We can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table in SQL Server. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Before PIVOT

After PIVOT

A simple PIVOT sample in SQL Server.Here we are going to demonstrate a very simple PIVOT sample without any complexity. We are having a table named Accounts and it containing customer name and their deposit with denominations.

Table Structure For showing simple PIVOT sample

CREATE TABLE Accounts(Customer VARCHAR(25), Denomination VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','10 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','50 $',6)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('John','100 $',1)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','10 $',4)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','50 $',3)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Ram','100 $',11)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','10 $',20)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','50 $',12)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('KATE','100 $',2)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','10 $',0)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','50 $',5)
INSERT INTO Accounts(Customer, Denomination, QTY)
VALUES('Eby','100 $',5) 

In order to PIVOT above mentioned table we can use below script. The result should be as Customer name with all denomination will be coming as columns with qty as values for each column.

SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN ([10 $],[50 $],[100 $])) AS Total

Dynamic Query to PIVOT table for dynamic columns. In the above example we are using a simple structured table and then PIVOT with denomination values. This can be achieved only when we are having denomination values as static. Suppose this denomination values are dynamic (Each country having different denomination like $,EUR, IND etc..), we need to create a dynamic query to PIVOT above table. Suppose we are having different table for getting Denomination values and we are going to take Denomination values from this table at run time as dynamic.

CREATE TABLE Denomination(Value VARCHAR(25))
GO
INSERT INTO Denomination(Value)
VALUES('10 $')
INSERT INTO Denomination(Value)
VALUES('50 $')
INSERT INTO Denomination(Value)
VALUES('100 $')

First of all, we need to get dynamic columns names from the above table. After that we can create a dynamic query with these columns.

Declare @ColumnNames VARCHAR(100);
SELECT @ColumnNames = COALESCE(@ColumnNames+ ',','') +
'['+ Cast(Value AS VARCHAR(50)) +']' FROM Denomination cust
PRINT @ColumnNames
DECLARE @DynamicQuery Varchar(MAX);
SET @DynamicQuery = '
SELECT * FROM Accounts
PIVOT (SUM(QTY) For Denomination IN (' + @ColumnNames + ')) AS Total'
EXEC (@DynamicQuery);

Today post I will show you the difference between Stored Procedure and Function in SQL Server. Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called.

Basic Difference

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Functions can have only input parameters for it whereas Procedures can have input/output parameters .
• Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..
• Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
5. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
6. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
7. We can go for Transaction Management in Procedure whereas we can't go in Function.

Hope it will completely enough to explain the difference about stored procedure and Function.

Here, I have provided an article showing you how to utilize the two new logical functions Choose and IIF in SQL Server. The Choose function works like an array kind of thing and the IIF function is used to check a condition. In this article we will see both functions with examples. These functions are also called new logical functions in SQL Server 2012. So let's take a look at a practical example of how to use the Choose and IIF functions in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

These are the two logical functions:

1. IIF() Function
2. Choose() Function

IIF() Function

The IIF function is used to check a condition. Suppose X>Y. In this condition a is the first expression and b is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax

IIF ( boolean_expression, true_value, false_value )

Example

DECLARE @X INT;
SET @X=50;
DECLARE @Y INT;
SET @Y=60;
Select iif(@X>@Y, 50, 60) As IIFResult

In this example X=50 and Y=60; in other words the condition is false.  Select iif(@X>@Y, 50, 60) As IIFResult. It returns false value that is 60.

Output

Choose() Function

This function is used to return the value out of a list based on its index number. You can think of it as an array kind of thing. The Index number here starts from 1.

Syntax

CHOOSE ( index, value1, value2.... [, valueN ] )

CHOOSE() Function excepts two parameters,

Index: Index is an integer expression that represents an index into the list of the items. The list index always starts at 1. 

Value: List of values of any data type.

Now some facts related to the Choose Function

1. Item index starts from 1

DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H') As ChooseResult 

In the preceding example we take index=5. It will start at 1. Choose() returns T as output since T is present at @Index location 5.

Output

2.  When passed a set of types to the function it returns the data type with the highest precedence; see:

DECLARE @ShowIndex INT;
SET @ShowIndex =5;
Select Choose(@ShowIndex ,35,42,12.6,14,15,18.7)  As CooseResult

In this example we use index=5. It will start at 1. Choose() returns 15.0 as output since 15 is present at @ShowIndex location 5 because in the item list, fractional numbers have higher precedence than integers.

3. If an index value exceeds the bound of the array it returns NULL

DECLARE @ShowIndex INT;
SET @ShowIndex =9;
Select Choose(@ShowIndex , 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index=9. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last Index=8.

Output

4. If the index value is negative then that exceeds the bounds of the array therefore it returns NULL; see:

DECLARE @ShowIndex INT;
SET @ShowIndex =-1;
Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we take index= -1. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array.

Output

5. If the provided index value has a float data type other than int, then the value is implicitly converted to an integer; see:

DECLARE @ShowIndex  INT;
SET @ShowIndex =4.5;
Select Choose(@ShowIndex ,35,42,12.6,13,15,20) As CooseResult

In this example we take index= 4.5. It will start at 1.  If the specified index value has a float data type other than int, then the value is implicitly converted to an integer. It returns the 13.0 as output since 15 is present at @ShowIndex=4.5 which means index is 4.

Output

Best performance is the main concern to develop a successful application. Focus of some key points which keeping in mind we can improve the database performance and tune it accordingly. A good database design provides best performance during data manipulation which results into the best performance of an application. During database designing and data manipulation we should consider the following key points:

Don’t have to type out the columns
If you’re using SQL Server Management Studios (SSMS) 2008 or higher, you can tell SSMS to script out select statements for you. To do this, right-click the table, go to Script Table As – Select To – New Query Editor Window . You can alternatively script to the clipboard if you already have a script open and just want to paste in there . This will open up a new window with your select statement.

A bonus (or down side) is that SQL Server automatically wraps each column with brackets, so if your column names have odd characters (such as spaces) this will always work. Another bonus is consistency. Using this method you will always be sure to have all of the columns in the table, so if you’re forgetful this method is perfect for you.

Fine Tune SSMS Options
SQL Server Management Studios has a lot of options to play with. One option that I have disabled is the “Use [database]” statement that you get whenever you script out a table. To change this I went to Tools – Options. Then went to SQL Server Object Explorer – Scripting, and changed “Script USE [database]” to false.

Use EXISTS instead of IN
Does practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.

 -- Avoid
SELECT Name,Price FROM tblProduct
where ProductID IN (Select distinct ProductID from tblOrder)
--Best practice
SELECT Name,Price FROM tblProduct
where ProductID EXISTS (Select distinct ProductID from tblOrder)

Create Clustered and Non-Clustered Indexes
Does practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

Choose Appropriate Data Type
Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

Avoid NULL in Fixed-Length Field
Does practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

Avoid * in SELECT Statement
Does practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.

-- Avoid
SELECT * FROM tblName
--Best practice
SELECT col1,col2,col3 FROM tblName

Keep Clustered Index Small
Does practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly.

Use Schema name before SQL objects name
Does practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.
 
--Here dbo is schema name
SELECT col1,col2 from dbo.tblName
-- Avoid
SELECT col1,col2 from tblName

SET NOCOUNT ON
Does practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:

CREATE PROCEDURE dbo.MyTestProc
AS
SET NOCOUNT ON
BEGIN
.
.
END

Summary
Expose some key point to improve your SQL Server database performance. hope after reading this article you will be able to use these tips with in your Sql Server database designing and manipulation.