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.

This article talk about what SQL injection is, how can that effect the security of our websites and what steps should be taken to create an ASP.NET application SQL injection proof. SQL injection is the attack in which the user of the website will input some SQL code as input which would result in creating a SQL statement that developers didn't intend to write. These SQL statements could result in unauthorized access, revealing secret user information and sometimes it could even wipe out the entire data lying on the server.

Getting to know SQL Injection

Let us take this discussion a little further by looking into the bad coding practices that will make the application prone to the SQL injection attacks. Let us create a simple table that contains username and password of the user for authentication.

Now I will create a small page that lets the user to enter his login credentials and get them validated against the Users table.

Note: Password should never be stored in plain text. This table contains password in plain text just for the sake of simplicity of this article.

The actual code that I will use to authenticate the user contains dynamic SQL that is being created by concatenating strings. This code will return true if the userid and password are found in the database otherwise false.  

public bool IsUserAuthenticated_Bad(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select userID from Users where userID = '" + username + "' and password = '" + password + "'";               

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return false;
}

For all the normal users this code will work fine. I can even test it using userid as sampleuser and password as samplepwd and this will work fine. For any other data except this it should say that authentication failed(since this is the only record in the table). The query that will get generated to test this input will be:

select userID from Users where userID = 'sampleuser' and password = 'samplepwd'

Now let us try to inject some SQL into this page. Let me give hacker' or 1=1-- as username and anything in the password(even leave it empty). Now the resultant SQL for this will become:

select userID from Users where userID = 'hacker' or 1=1--' and password = ''

Now when we execute this query the 1=1 clause will always return true(and the password check is commented out. Now irrespective of whatever data user has entered this will SQL return a row making this function return true and in turn authenticating the user. So What I have done now is that I gained access to the website even when I didn't knew the valid user credentials.

How can I curb this problem is something we will look into details in some time. But before that let us also look at one more example of SQL injection just to get little more understanding.

In this second example we will assume that the malicious user somehow got hold of the database schema and then he is trying to manipulate the application to find some confidential information. Lets say we have a page that is supposed to show all the products that are assigned to a user in the organization.

Let us start by looking at the Product table.

Let us now look at the code that is retrieving this data:

public DataTable GetProductsAssigner_Bad(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = '" + userID + "'";

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return result;
}

Now if I call this function with the proper data(as normal users would do) then this will show me the results. i.e. If I call this page for sampleuser the resulting query would be:

select * from Products where AssignedTo = 'sampleuser'

Now let me use this query string with this page: userID=' UNION SELECT 0 AS Expr1, password, userID FROM Users -- . Once this data is used with the current code this will show me all the username and passwords from the database. The reason will be quiet clear once we look into the resulting query of this input.

select * from Products where AssignedTo = '' UNION SELECT 0 AS Expr1, password, userID FROM Users --

Now we saw that how string concatenated dynamic SQL is prone to SQL injection. There are many other problems that could be created by injecting SQL. Imagine a scenario where the injected SQL is dropping tables or truncating all the tables. The problem in such cases would be catastrophic.

How to Prevent SQL Injection

ASP.NET provides us beautiful mechanism for prevention against the SQL injection. There are some thumb rules that should be followed in order to prevent injection attacks on our websites.

• User input should never be trusted. It should always be validated
• Dynamic SQL should never be created using string concatenations.
• Always prefer using Stored Procedures. 
• If dynamic SQL is needed it should be used with parametrized commands.
• All sensitive and confidential information should be stored in encrypted.
• The application should never use/access the DB with Administrator privileges. 

User input should never be trusted. It should always be validated

The basic thumb rule here is that the user input should never be trusted. First of all we should apply filters on all the input fields. If any field is supposed to take numbers then we should never accept alphabets in that. Secondly, All the inputs should be validated against a regular expression so that no SQL characters and SQL command keywords are passed to the database.

Both this filtration and validation should be done at client side using JavaScript. It would suffice for the normal user. Malicious users cans till bypass the client side validations. So to curb that all the validations should be done at server side too.

Dynamic SQL should never be created using string concatenations.

If we have dynamic SQL being created using string concatenations then we are always at the risk of getting some SQL that we are not supposed to use with the application. It is advisable to avoid the string concatenations altogether.

Always prefer using Stored Procedures.

Stored procedures are the best way of performing the DB operations. We can always be sure of that no bad SQL is being generated if we are using stored procedures. Let us create a Stored procedure for the database access required for our login page and see what is the right way of doing the database operation using stored procedure.

CREATE PROCEDURE dbo.CheckUser     
      (
      @userID varchar(20),
      @password varchar(16)
      )
AS
      select userID from Users where userID = @userID and password = @password
      RETURN

And now lets have a good version in our code using this stored procedure.

public bool IsUserAuthenticated_Good(string username, string password)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "CheckUser";
                cmd.Parameters.Add(new SqlParameter("@userID", username));
                cmd.Parameters.Add(new SqlParameter("@password", password));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);

                    //check if any match is found
                    if (result.Rows.Count == 1)
                    {
                        // return true to indicate that userID and password are matched.
                        return true;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return false;
}

If dynamic SQL is needed it should be used with parametrized commands.

If we still find our self needing the dynamic SQL in code then parametrized commands are the best way of performing such dynamic SQL business. This way we can always be sure of that no bad SQL is being generated. Let us create a parametrized command for the database access required for our Product page and see what is the right way of doing the database operation.

public DataTable GetProductsAssigner_Good(string userID)
{
    DataTable result = null;
    try
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDbConnectionString1"].ConnectionString))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from Products where AssignedTo = @userID";
                cmd.Parameters.Add(new SqlParameter("@userID", userID));

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    result = new DataTable();
                    da.Fill(result);
                }
            }
        }
    }
    catch (Exception ex)
    {
        //Pokemon exception handling
    }

    //user id not found, lets treat him as a guest       
    return result;
}

All sensitive and confidential information should be stored in encrypted.

All the sensitive information should be stored encrypted in the database. The benefit of having this is that even if somehow the user get hold of the data he will only be able to see the encrypted values which are not easy to use for someone who doesn't know the encryption technique used by the application.

The application should never use/access the DB with Administrator privileges.

This will make sure that even if the bad SQL is being passed to the Database by some injections, the database will not allow any catastrophic actions like dropping table.

Note: Refer the sample application attached to see the working examples SQL injection and how to curb them using parametrized commands and stored procedures.