Wednesday, November 6, 2013

SQL Server : Context_Info

 

While working one of my project, need for connection specific setting required to enable and disable functionality. Project has very large database in terms of entities and many of entities have triggers that affect other entity and also done task for sending notification.

Sometime there is need to disable certain trigger required during data mining work without affecting application that use data.

While searching for solution to that issue I came across Context_Info functionality available in SQL Server.It is varbinary field and its value available only for particular connection session.

You can use it following way.

Step 1. SET CONTEXT_INFO 0x1

Step 2. If 0x01 = Context_Info()
    Print ‘ContextInfo Set’
else
    print ‘ContextInfo Not Set’

If you have SQL Server Management Studio then open two new query window.

In first window put code specified in Step1 and Step2 and In Second window only put code specified in Step 2.

image

You can also use above functionality during coding as well. You just have to call that function with value. Following is the sample with C#. ( Test application that I created for this)

 class SqlContextInfoSample
{
public void ExecuteSample()
{
using (var conn = GetConnection())
{
SetContextInfomationToConnection(conn);
Console.WriteLine(GetContextInformationFromConnection(conn));
}

using (var conn = GetConnection())
{
Console.WriteLine(GetContextInformationFromConnection(conn));
}
}

SqlConnection GetConnection()
{
var conn = new SqlConnection();
conn.ConnectionString = @"<>";
conn.Open();
return conn;
}

void SetContextInfomationToConnection(SqlConnection conn)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SET CONTEXT_INFO 0x1";
cmd.ExecuteNonQuery();
}
}

string GetContextInformationFromConnection(SqlConnection conn)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "Select CONTEXT_INFO()";
object result = cmd.ExecuteScalar();
return result.ToString();
}
}
}

Hope this will help.

Tuesday, November 5, 2013

SQL Server 2012 : OFFSET FETCH VS CTE WITH ROW_NUMBER()

 

Since SQL Server 2005 and 2008 ROW_NUMBER() Function available and many of us will us that function with CTE to get data by custom page size.

In SQL Server 2012 there is new functionality to get record by page size and it is known as OFFSET FETCH.

While Learning SQL Server 2012 Syntax for OFFSET and FETCH clause I found that this will not work if you are specify order by clause in Select query. So I decided to check ROW_NUMBER() with CTE to new functionality in terms of performance.

For this example AdventureWorks_2012 database in used.

Old Way Of Query ( Version 2005 , 2008)

WITH CTE
AS
(
Select AccountNumber , P.FirstName , P.LastName , P.MiddleName , ROW_NUMBER() OVER (ORDER BY P.BusinessEntityId) RowNum from Sales.Customer SC
LEFT JOIN Person.Person P ON SC.PersonID = P.BusinessEntityID
WHERE AccountNumber Like 'A%'
)
Select AccountNumber , FirstName , LastName , MiddleName from CTE WHERE RowNum > 1000 and RowNum <= 2000

SQL Server 2012

Select AccountNumber , P.FirstName , P.LastName , P.MiddleName   from Sales.Customer SC
LEFT JOIN Person.Person P ON SC.PersonID = P.BusinessEntityID
WHERE AccountNumber Like 'A%'
ORDER BY P.BusinessEntityID
OFFSET 1000 ROWS
FETCH NEXT 100 Rows ONLY

At this point I thought there must be a performance improvement in SQL Server 2012. To check that I used execution plan and statics IO.


Execution Plan ( Both Query Took 50%)


image


STATISTICS IO ( Both are same)


image


As per my thinking there is no performance improvement and it is just syntax get change ( Internally In SQL Engine both query are same as they generated same execution plan)


Please give me your opinion regarding this and correct me If I am wrong somewhere to understand functionality.