Parameterize Sorting Stored Procedure

In Data-driven application or website, dynamic sorting is common need. Ideally we should write stored procedure for dynamic sorting.

We found nice solution for above problem. Bellow stored procedure fetch ordered data with passed parameter. There two parameters for dynamic sorting stored procedure. Parameter @sortDirection pass for order direction (asc or desc). Second parameter @sortCol for pass sorting field name.

Code

---

=====================================================-
-- Create date:   18-Nov-2011
-- Description:   Example of Fetch Data With Sorting Parameter
=====================================================
Create PROCEDURE Product_Sorting_Parameter
    -- Add the parameters for the stored procedure here
    @sortDirection as varchar(5),
    @sortCol as varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;
    -- Select Query
    SELECT
        [ProductID] ,[Name],[ProductNumber],[Color],[ListPrice],[Size]     
     FROM         [AdventureWorks2008R2].[Production].[Product]
     ORDER BY
     -- Name
     Case WHEN @sortCol = 'Name' and @sortDirection = 'asc' THEN Name end,
     Case WHEN @sortCol = 'Name' and @sortDirection = 'desc' THEN Name end desc,
     -- Size
     Case WHEN @sortCol = 'Size' and @sortDirection = 'asc' THEN Size end,
     Case WHEN @sortCol = 'Size' and @sortDirection = 'desc' THEN Size end desc,
     -- Color
     Case WHEN @sortCol = 'Color' and @sortDirection = 'asc' THEN Color end,
     Case WHEN @sortCol = 'Color' and @sortDirection = 'desc' THEN Color end desc,
     -- Price
     Case WHEN @sortCol = 'Price' and @sortDirection = 'asc' THEN ListPrice end,
     Case WHEN @sortCol = 'Price' and @sortDirection = 'desc' THEN ListPrice end desc
END
GO

Juneau : Microsoft SQL Server Developer Tools

Microsoft announce new SQL Server tools, code named "Janeau" CTP 3 on 13th June 2011. For install "Janeau" is required Visual Studio 2010 SP 1. The operating system is required vista sp2 or above, Windows 7, Sql Server R2 SP 2. From bellow link you can download "Janeau" from microsoft.

Janeau Download Link

Microsoft provide nice video series for get starting with "Janeau". So here I share one of them video regarding "Janeau" integrate with visual studio via entity framework.

More Video refer bellow link

Get Started with SQL Server Developer Services Codename "Juneau"

NEWID() - Generate Randomly Sort Records - TSQL

In our company, we have project for "Quiz management". On quiz page, we have to fetch 10 random question from large table. To get a random question, you might be tempted to select the top n rows from the table. However, this question is not random. The first n rows are not necessarily representative of the whole table. After few searches on MSDN, we found wonderfull solution. The solution here is the NEWID function, which generates a globally unique identifier (GUID) in memory for each row. By definition, the GUID is unique and fairly random; so, when you sort by that GUID with the ORDER BY clause, you get a random ordering of the rows in the table.

For that we use bellow query, which generate 10 random question each time. For testing we run them 3 times and code as per bellow.

Code :

SELECT TOP 10  *   FROM questions ORDER BY NEWID();

SQL Server CE 3.5 Overview

Topics

• What's New in SQL Server Compact Edition
• Feature Improvements to Enhance Reliability and Performance
• Synchronization
• Storage Engine
• Query Processor
• Integration with SQL Server 2005
• Integration with Microsoft Visual Studio 2005
• Advantage of SQL SERVER CE
• Disadvantage of SQL SERVER CE
• Connection strings for SQL Server Compact Edition
• Difference Between SQL Server Compact Edition (CE) and SQL Server Express Edition

Monthwise report With Pivot Query

Yesterday One of my friend give me to solve one interesting query. There are some specific forms list in one table. There is one more table for month. The status of form as per month recorded in another table. Base on status table, we have to find for which month status record not fill up for forms.

Table Name         Field Name
Forms                   form_id
                              form_name

Months                 Month_id
                              Month_Name

Status                   form_id
                              month_id
                              status (whether filled or not)
                              comment (Comment for each entry)

Sample Data As per Bellow: 

Required Result

I found one solution for this. The code of query as per bellow.

Server Side Pagination Query

The paging of a large database resultset in Web applications is a well known problem. In short, you don't want all the results from your query to be displayed on a single Web page, so some sort of paged display is more appropriate. While it was not an easy task in the old ASP, the DataGrid control in the ASP.NET simplifies this to a few lines of code. So, the paging is easy in ASP.NET, but the default behavior of the DataGrid is that all resulting records from your query will be fetched from SQL server to the ASP.NET application. If your query returns a million records this will cause some serious performance issues (if you need convincing, try executing such a query in your web application and see the memory consumption of the aspnet_wp.exe in the task manager). That's why a custom paging solution is required where desired behavior is to fetch only the rows from the current page. 

Here I provide some query which provide solution for custom pagination. It helps to improve your search result for your filter data.

Code

TSQL - Decimal to time

 Yesterday I got one query to change numeric value to decimal value. Suddenly I thought "Are datetime and numeric value convertible?" like It convert in excel. Is it possible SQL Server. After 1 hours searching and trial and error ,  I found result.  

The answer is yes. 

Here are the main rules on DATATIME and NUMERIC value conversions:

• During the conversion a DATETIME value will be treated as a NUMERIC value with the number of days relative the base date, Jan 1, 1900 being the integer part, and the time of the day being the decimal part.
• DATETIME values can not be converted to NUMERIC values implicitly using assignment operations.
• NUMERIC values can be converted to DATETIME values implicitly using assignment operations.
• DATETIME values and DATETIME values can be converted to each other explicitly using CAST() or CONVERT() functions.

The tutorial exercise below shows you some good examples:

• Implicit conversion Numeric to Datetime
  
  

  Implicit Conversion Numeric To Datetime

NULLIF (Transact-SQL)

Yesterday I found one more function of sql server. NULLIF which returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF function is available from Sql Server 2005.

Syntax :

NULLIF ( expression , expression )

Example :

The following example creates a budgets table to show a department (dept) its current budget (current_year) and its previous budget (previous_year). For the current year, NULL is used for departments with budgets that have not changed from the previous year, and 0 is used for budgets that have not yet been determined. To find out the average of only those departments that receive a budget and to include the budget value from the previous year (use the previous_year value, where the current_year is NULL), combine the NULLIF and COALESCE functions. 

ISNULLIF Example

Important Query For DBA

Which protocol is being used for client-server connectivity in SQL Server?

SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Above  query return our server  connection protocol. There are 4 protocol there for connect database server with client.

• Shared Memory
• TCP/IP
• Named Pipes
• VIA (Virtual Interface Adapter)

Delete VS Truncate

"What is deference between Delete & Truncate ? " 

We all face this question in interview at least once in our career. Actually it is very tricky question. We have many misunderstanding regarding this two terms. After lots of searching today I found some good comparison between them. It is as per bellow.

Delete	Truncate
Removes rows from a table or view. DELETE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information.	Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
You may use DELETE statement against a view (with some limitations).	You can’t use TRUNCATE statement against a view.