Atif-ullah Sheikh Sharing SQL knowledge

System versioned Tables in SQL Server 2016

2017-05-15T06:27:00.003-07:00

In SQL Server 2016, System versioned tables are the tables whose data is maintained in the history table. This history is maintained by SQL Server itself. All you need is to specify 2 additional datetime2 columns (SysStartTime and SysEndTime in the example below) and a clause with these two columns as : PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

To create a system versioned table;

------ System versioned table

CREATE TABLE Department

(

DeptID int NOT NULL PRIMARY KEY CLUSTERED

, DeptName varchar(50) NOT NULL

, ManagerID INT NULL

, ParentDeptID int NULL

, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

)

WITH (SYSTEM_VERSIONING = ON)

;

The system versioned table creates a temporal table which maintains the history of the data. By default, the name is as MSSQL_TemporalHistoryFor_1541580530. But you can specify the name of this table in the CREATE TABLE statement above;

------ System versioned table with name the temporal table

CREATE TABLE Department

(

DeptID int NOT NULL PRIMARY KEY CLUSTERED

, DeptName varchar(50) NOT NULL

, ManagerID INT NULL

, ParentDeptID int NULL

, SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

)

WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.DepartmentHistory ) )

;

In above example, dbo.DepartmentHistory is created as temporal table.

In the management studio, the system versioned tables are shown as below;

The above diagram shows the default name of the temporal table. If you have specified the name of your temporal table (dbo.DepartmentHistory in our example), then it will be shown;

You can also rename the temporal table. In sys.objects, these tables are listed as a user_table. To check if any table have a temporal table or not you can query the “OBJECTPROPERTY” for property “TableTemporalType”;

Select OBJECTPROPERTY(OBJECT_ID(N'dbo.Department', N'U'), N'TableTemporalType')

INSERT / UPDATE / DELETE

When we insert any data in the main table (dbo.Department), no data is inserted in the history / temporal table. In case of UPDATE you will see the latest data in main table and the history data in the temporal. The SysStartTime and SysEndTime are updated as per the time of UPDATE statement;

Update [dbo].[Department]

Set DeptName = 'A'

where DeptID = 3

Select * from [dbo].[Department]

Select * from [dbo].[DepartmentHistory]

You can see above that DeptName for DeptID=3 is changed to ‘A’ and the old value ‘D3’ is available in the temporal table. Please note that the copy of the complete record is maintained in the temporal table.

You cannot delete the data directly from Temporal table. When you delete data from main table, the data is stored in temporal table and deleted from the main table.

Delete from [dbo].[Department] where DeptID = 3

Select * from [dbo].[Department]

As you can see the data is deleted from main table. But the record is available in temporal table;

Select * from [dbo].[DepartmentHistory]

When you try to delete from the DepartmentHistory temporal table, it will generate error;

Delete from [dbo].[DepartmentHistory] where DeptID = 3

So, you cannot delete data from temporal table.

WHAT ELSE YOU CANNOT DO

TRUNCATE TABLE

1. You also cannot TRUNCATE the system versioned and the temporal table. You will get the following error;

Msg 13545, Level 16, State 1, Line 35

Truncate failed on table 'Test16.dbo.Department' because it is not supported operation on system-versioned tables.

2. You cannot change the schema of the temporal table.

3. You cannot add trigger to temporal table

YOU CAN ADD INDEX

You can index the temporal table and the main table as per your requirement.

Update Table Joined with other on the basis of CASE statement

2017-05-02T06:24:00.001-07:00

Please check the below example;

Declare @vTableA Table (CINAME varchar(100),  SerialNo varchar(100),   Status varchar(100),    UniquekeyCiname_serialno varchar(100))
Declare @vTableB Table (CINAME varchar(100),  SerialNo varchar(100),   Status varchar(100),    UniquekeyCiname_serialno varchar(100))

Insert into @vTableA
Select 'ASDAS','23SSD',NULL,'ASDAS23SSD'
Union All
Select 'WER34','',NULL,'WER34'
Union All
Select 'DFDFS','3239',NULL,'DFDFS3239'
Union All
Select 'ERTER',NULL,NULL,'ERTER'


Insert into @vTableB
Select 'ASDAS','23SSD','DEPLOYED','ASDAS23SSD'
Union All
Select 'WER34','5335','IN TRANSIT','WER345335'
Union All
Select 'DFDFS','3239','LOST','DFDFS3239'
Union All
Select 'ERTER','678W','DEAD','ERTER678W'


Select * from @vTableA
Select * from @vTableB

Update a
Set a.[Status] = b.[Status] 
From @vTableA a
Inner Join @vTableB b on Case when IsNull(a.SerialNo,'')<>'' then a.UniquekeyCiname_serialno else a.CINAME end = Case when IsNull(a.SerialNo,'')<> '' then b.UniquekeyCiname_serialno else b.CINAME end

Select * from @vTableA
Select * from @vTableB

Implement Log Shipping with AlwaysOn Availability Group

2017-04-12T04:37:00.002-07:00

Audience

This article is for those who understands bot AlwaysOn Availability Group and Log Shipping. I will not go into the details explaining the both technologies. The article will only cover the tricky part that is involved to implement Log Shipping with AlwaysOn Availability Group.

Scenario

Let’s suppose you have AlwaysOn Availability Group implemented. Assume you have more than one databases part of the availability group but due to some technical requirements, you need to provide a read only access of only one database. Here, Log Shipping can help you to replicate data on the another machine to give access to the data.

Environment

Let’s say we have one node as SQL1 and one node as SQL2. You need to apply Log Shipping on node SQL3 which is not the part of the availability group replicas. You need to first apply Availability group with Listener (IP and port). Make sure you can connect with the Primary node (say, its SQL1 is Primary and SQL2 is secondary at this time) using Listener IP and port that you specified at the time of the creation of Availability Group on Listener Tab.

Implement Log Shipping

Connect the primary Replica using Listener IP and port and implement Log Shipping using SQL3 as your Log Shipping node. Make sure that the shared backup path is accessible by all nodes SQL1, SQL2 and SQL3.

Tricky Part

Right click the availability group and go to Backup Preferences page. Here select, “Primary” as you backup preference.

Since the log shipping is applied to the Primary replica SQL1 and not on secondary replica SQL2, so when the failover will occur from SQL1 to SQL2, the log shipping will break. It is because the log shipping jobs are not applied on the secondary SQL2 node and in case of any log backup on SQL1, the LSN chain will break even when you will go back to SQL1 from SQL2. So the trick here is to create the script of the Log Shipping that you implemented on Primary node SQL1. The script is divided into 2 parts. Copy the “Primary” part of the script. Failover to the SQL2. Connect to SQL Server using the same Listener IP and port. This will connect you to SQL2. Open the query window and paste the copied script. Run the script. The script will create and configure the Log Shipping on SQL2 for you. Now you have log shipping is configured on both nodes SQL1 an SQL2.

Result

Now, in case of failover, your log shipping will never break as the backups will be taken on Primary node only and the LSN will be maintained.

SQL Server 2016 T-SQL Enhancements

2017-02-09T04:10:00.000-08:00

Few enhancements are worth discussing.

DROP IF EXISTS

In old versions of SQL Server we had to check if the table or column exists before dropping it. In SQL Server 2016, this is made simple by making IF EXISTS optional part of DROP TABLE, DROP INDEX, etc.

DROP TABLE IF EXISTS dbo.Product

DROP TRIGGER IF EXISTS trProductInsert

DROP INDEX IF EXISTS Department.DeptID.trProductInsert

It works with the following objects;

AGGREGATE	PROCEDURE	TABLE
ASSEMBLY	ROLE	TRIGGER
VIEW	RULE	TYPE
DATABASE	SCHEMA	USER
DEFAULT	SECURITY POLICY	VIEW
FUNCTION	SEQUENCE
INDEX	SYNONYM

For column use the following syntax;

ALTER TABLE Department DROP COLUMN IF EXISTS abc

STRING_SPLIT

Prior to SQL Server, we had to create a Table value function to get the list of character separated values. MS have now made it the part of SQL Server built-in functions.

Select * from STRING_SPLIT('a,b,c',',')

Also works with the variable;

DECLARE @tags NVARCHAR(400) = a,b,,c,d'

SELECT value

FROM STRING_SPLIT(@tags, ',')

WHERE RTRIM(value) <> ''

Returns values are nvarchar if the input in nvarchar or nchar. Otherwise returns varchar. It accepts a single character as separator.

SYSTEM_TIME

For system versioned tables, we can check the history values in the query;

SELECT * FROM dbo.Department

FOR SYSTEM_TIME BETWEEN '2017-02-08' And '2017-02-10';

The above query will give you all the updates made on the tblDepartment (with system versioning enabled) between the two given dates. You can also specify the date and time;

SELECT * FROM dbo.Department

FOR SYSTEM_TIME BETWEEN '2017-02-08 09:00:00.000' And '2017-02-10 10:00:00.000';

We will discuss the system version tables in another article.

JSON

Create JSON string on the fly;

Select *

from Department

FOR JSON Path

Added functions are;

OPENJSON:

DECLARE @JSON NVARCHAR(4000)

SET @json = N'[{"DeptID":1,"DeptName":"Dept 1","ManagerID":1,"SysStartTime":"2017-02-09T08:54:49.6660045","SysEndTime":"9999-12-31T23:59:59.9999999"},{"DeptID":2,"DeptName":"D2","ManagerID":1,"SysStartTime":"2017-02-09T08:54:18.6513263","SysEndTime":"9999-12-31T23:59:59.9999999"},{"DeptID":3,"DeptName":"D3","ManagerID":1,"SysStartTime":"2017-02-09T09:03:01.9862457","SysEndTime":"9999-12-31T23:59:59.9999999"}]';

SELECT *

FROM OPENJSON( @json) a

We can also work with multiple records in a json string. In order to link the data in Jason strings to its respective Jason string;

SELECT *

FROM OPENJSON( @json) a

Cross Apply OPENJSON( a.value)

As you can see in the upper query, the cross apply is used to parse the complete data in the JSON string with multiple records.

Cycle Clipboard Ring SQL Server 2016

2017-02-08T04:36:00.000-08:00

We have a new feature “Cycle Clipboard ring” in SQL Server management studio SSMS 13.0.16100.1. This new feature of SSMS allows you to toggle between what you have copied on the clipboard.

For example, if you copy something from one query window;

Use DBNAME

select * from Table1

Then copy another command from another query window;

Use DBNAME2

select * from TableName2

Now open a third query window and press CTRL+SHIFT+V; you will see one of the items from above. Keep holding CTRL+SHIFT and re-press “V” and you will see other item from the clipboard. This happens as the anything copied on the clipboard, it goes into the ring and it keeps rotating with the key combination on CTRL+SHIFT+V.

PS: Anything copied outside from SSMS before opening SSMS is also the part of this ring.

LEN and DATALENGTH

2017-02-02T23:26:00.003-08:00

LEN function is very commonly used function in t-sql. One thing to note in this function that is ignores the trailing blanks;

Declare @v VarChar(5)

Set @v = 'ati '

Select Len(@v)

Same goes for CHAR and NVARCHAR.

To overcome this, use and character at the end and minus 1 from the length J

Declare @v VarChar(5)

Set @v = 'ati '

Set @v = @v + '?'

Select Len(@v)-1

Some people might use DATALENGTH function;

Declare @v VarChar(5)

Set @v = 'ati '

Select DATALENGTH(@v)

But be alert as DATALENGTH function counts the bytes, not the length. If you change the data type of @v from varchar to nvarchar, the result will change;

Declare @v NVarChar(5)

Set @v = 'ati '

Select DATALENGTH(@v)

Thanks for reading.

XACT_ABORT V/S TRY .. CATCH

2017-02-02T04:26:00.001-08:00

Few days back we witnessed an issue that the SQL Server was running slow. We investigated and soon found out that there were uncommitted transactions on the server. We re-confirmed those transactions from the developers and resolved the issue. Someone tossed the idea to apply SET XACT_ABORT ON in all SPs so that this situation may not occur again. That made me to write this article so that I can explain the pros and cons of SET XACT_ABORT ON option.

SET XACT_ABORT ON

As many of you already know that this options just takes the whole batch a one transaction. If any of the statement in the batch fails, the transaction of the whole batch is rolled back. Commits in case of success. Simple. But not that simple. You cannot control the flow of transaction with if XACT_ABORT is ON. TRY .. CATCH will not work. Even if you have the error log is implemented in CATCH block, it will not be saved as it will also get rolled backed. So, DB end error log is gone. In some cases you may need to continue with the rest of your code in case of an error, for example return a dataset. You cannot do this as well. The plus point you will never have an open transaction on your server. All transactions will either commit or rollback.

Another way to handle an open transaction issue is to apply TRY .. CATCH in all SPs. Ok, I know. There are few limitations of TRY .. CATCH. TRY .. CATCH do not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction in case of a schema error (table does not exists, etc ). Check the code below;

BEGIN TRY

-- Table does not exist; object name resolution

-- error not caught.

begin Tran

SELECT * FROM NonexistingTable;

commit

END TRY

BEGIN CATCH

rollback

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

Select @@TRANCOUNT

Execute the above statements. You will end up with an opened transaction.

You can also try this in a stored procedure. You will still end up in an open transaction.

Bow add SET XACT_ABORT ON;

SET XACT_ABORT ON

BEGIN TRY

-- Table does not exist; object name resolution

-- error not caught.

begin Tran

SELECT * FROM NonexistingTable;

commit

END TRY

BEGIN CATCH

rollback

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

Select @@TRANCOUNT

Your transaction will be rolled back and you will find no open transaction.

CONCLUSION

XACT_ABORT ON solves the open transaction issue but at the cost of logging implemented in CATCH block (if you have any).

But “NonexistingTable” type of issues should not be in the production environment. Like why will I deploy a SP using a table that does not exists? Strange thing for me. You should have QA in place and your deployment scripts should be verified before running in the production environment.

You decide.

SET DATEFIRST and SET LANGUAGE

2017-02-01T05:57:00.002-08:00

This is a tricky part. SQL Server have language settings at the following levels;

1. Server

2. User Login

3. Session

You need to be very clear for the language settings of the user / login as it may impact the date functions;

DECLARE @Today DATETIME;

SET @Today = getdate();

SET LANGUAGE Italian;

SELECT DATENAME(month, @Today) AS 'Month Name';

Select @@DATEFIRST

SET LANGUAGE us_English;

SELECT DATENAME(month, @Today) AS 'Month Name' ;

Select @@DATEFIRST

SET LANGUAGE British;

SELECT DATENAME(month, @Today) AS 'Month Name' ;

Select @@DATEFIRST

SET LANGUAGE us_English;

As you can see from the above code, I have set the language back to US English (English in SQL Server language drop downs) as I like to have it that way. You also see that @@DATEFIRST function returns different value for different languages. This value is set on the basis of;

1. Language selected

2. SET DATEFIRST option

You can change the language and still keep the First date of week same as in US English;

SET LANGUAGE us_English;

Select @@DATEFIRST

SET LANGUAGE British;

Select @@DATEFIRST

SET DATEFIRST 7

Select @@DATEFIRST

As you can see, US_English sets the @@DateFirst to 7 and British sets it to 1. But we have used SET DATEFIRST to change the language to British but kept @@DATEFIRST to 7 using SET DATEFIRST option.

This is very important when you are working multilingual databases. Where ever you are using day of week values (in DatePart, etc), you should keep this change in mind as well. Either use one language throughout your database for Server, users and session. But if there might be any change to get the benefit of SQL Server multilingual support, you should use SET DATEFIRST into account when programming to keep all the calculations and checks aligned.

xp_fileexist And its Alternate

2017-01-30T05:53:00.000-08:00

xp_fileexists is a very useful undocumented stored procedure of SQL Server.

The usage of xp_fileexist is as follow;

Exec xp_fileexist “E:\abc.txt”

The values returned are

You can also use OUTPUT parameter to get the value of “File Exists” column as below;

Declare @vFileExists int

exec master.dbo.xp_fileexist 'E:\abc.txt', @vFileExists OUTPUT

Select @vFileExists

If you want to save all three returned values, then you will have to go throu Temp Table approach;

Declare @vFileExists Table (FileExists int, FileDir int, ParentDirExists int)

insert into @vFileExists

exec master.dbo.xp_fileexist 'E:\abc.txt'

Select * from @vFileExists

Sometimes xp_fileexist behaves abnormally. Your file is there and it does not check the file and returns 0 in FileExists column. This is an extended SP so its behavior may also change with different versions of SQL Server.

If you are facing the same problem, change you code with xp_cmdshell “dir” approach;

Declare @vExistsPath nvarchar(100)

Declare @files Table ([FileName] nvarchar(100))

Set @vExistsPath = ''

Set @vExistsPath = 'E:\abc.txt'

Set @vExistsPath = 'dir ' + @vExistsPath + ' /b'

Insert into @files EXEC xp_cmdshell @vExistsPath

Select * from @files

if Exists(Select 1 from @files where [FileName] = 'abc.txt' And [FileName] is Not Null)

begin

Select 1

end

else

begin

Select 0

end

The “/b” switch returns only filename with extension as a result of “dir” command

Handeling comma while creating CSV

2017-01-30T05:52:00.000-08:00

I would recommend you should NOT use comma ',' in your csv as I think you are facing issues because of ',' in the data of your text column. Instead you can use '|' sign.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT Column1, Column2, Column3 FROM YourDB.[dbo].[YourTable] ORDER BY 1" queryout E:\Objects3.txt -t"|" -c -T '

if you still want to use comma as separator due to any limitation, you can use char(34) before and after the column that you think may have comma in the text. Adding char(34) i.e. double quote, will make the text with comma as a single value.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT Column1, char(34) + Column2 + char(34) , Column3 FROM YourDB.[dbo].[YourTable] ORDER BY 1" queryout E:\Objects3.txt -t"," -c -T '

The above code assumes that you have comma in Column 2 and file created is comma separated CSV.

I hope it will help you.

Microsoft.ACE.OLEDB.12.0 Issues and their solution

2017-01-23T02:58:00.004-08:00

Few OLEDB issues that I would like to share;

1. Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

If you see this message with your OLEDB query, it means you do not have sufficient rights on the OLEDB source. Please check your user have enough rights to access the OLEDB provider. The provider must show in the list of providers as below;

Secondly, right click the provider and check “Disallow adhoc Access”. It should be unchecked.

2. Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Check the above issues stated in point 1. If above settings are good, and still getting this error, then it your syntax. Please check the correct syntax to query using the Provider. For example, to query excel file, the correct syntax is;

SELECT *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=F:\FleName.xlsx',

'select * from [sheet1$]')

For csv, the syntax is;

SELECT *

FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',

'Text;Database=E:\',

'SELECT * from FileName.csv')

Please note that in case of excel, File name is give in the “Database” property and sheet name is given in the “Select * from [sheet1$]” statement.

In case of csv, only path is given for “Database” property and file name is given in the “Select * from Filaname.csv” part.

3. OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

You need to install 64 bit or 32 bit Access driver depending upon the operating system that you have on the machine where you are running the query to access the excel or csv file.

4. Few columns returning NULL in the dataset

You might see this issue where your values are turning into NULL. If you look deeply into your data, you will notice that the values in the column are numbers (not alpha numeric charachters) and the values that are alpha numeric in the parent file are turning into NULL. This is because the data type of the column is determined based up on the first few data values in the column. If these are numbers, the datatype will be set to “int” and all character values will be saved as NULL.

To solve this issue, you need to do some changes in the registry as shown in the screen below;

Open registry of the server in which you are processing the csv / Excel file. Go to the following path;

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Text

We have selected “Text” as we faced this issue while processing CSV files.

Change “ImportMixedTypes” property value to “Text”. Save the registry. Execute the query again and you will now see all your data returning as Text. You can no move your data as per your table schema.

Testing, Adding Nodes to Clusters and Adding Replicas to Availability Groups - Part 3

2014-11-03T07:32:00.000-08:00

You can read this article here.

Enabling AlwaysOn and Creating Availability Groups - Part 2

2014-11-03T07:31:00.002-08:00

You can read this article here.

Introduction and Creating Windows Cluster - Part 1

2014-11-03T07:30:00.001-08:00

You can read this article here.

Creating a Power Pivot Data Model in Microsoft Excel 2013

2014-06-17T11:28:00.000-07:00

I was featured on SQL Server Pro for writing an article on creating a Power Pivot Data Model in Microsoft Excel 2013. In the article, I am providing step-by-step instructions for generating complex reports in Excel using a Power Pivot Table, an add-in that allows users to gather, store, model and analyze large amounts of data.

Read the full article here.

SQL Server 2014 – Index Enhancements

2014-05-15T07:11:00.004-07:00

Inline Specification of Clustered and NON-Clustered Indexes

SQL Server 2014 is launched and there are many new things added in the product. DBAs were waiting for a very long time for some of those. Creating Inline Clustered and NON-Clustered Indexes is one of them.

If you are familiar with T-SQL coding, you should know that you cannot add NON clustered index while creating a table in CREATE TABLE statement. The NON-Clustered indexes were to be added after the creation of the table. Now you can add Non-Clustered indexes in the CREATE TABLE statement.

Some of you say “What’s the PLUS point in this?” Well, I have myself witnessed that when you are creating the database tables, you are more involved in the design. Generally, you have all the information regarding the data that the table is going to hold and the major user queries that are going to be executed on that table. Point is, that is the best time to decide to add indexes. You generally do this. But at times, you forget to exclusively add and run the “CREATE INDEX” statement. This results in an un-wanted delays and, sometimes, results in the crash, especially when you are reading data for reports from the tables on which you forgot to add an index. With the ability of creating Inline Indexes, the chances of such mistakes is even lower now.

If you execute the following in SQL Server 2012, it will generate an error;

Use AdventureWorks2012

CREATE TABLE tblTest

(

ID int IDENTITY (1,1) PRIMARY KEY,

NAME nvarchar(100),

FATHERNAME nvarchar(100) index idx_FATHERNAME

)

The error will be;

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

But if you execute the same code in SQL Server 2014 (any edition), the command will be executed successfully.

You can also create composite indexes in the same manner. Included columns and filtered conditions are not yet supported with Inline specification of indexes.

Use AdventureWorks2012

CREATE TABLE tblTest

(

ID int IDENTITY (1,1) PRIMARY KEY,

NAME nvarchar(100),

FATHERNAME nvarchar(100) index idx_FATHERNAME (FATHERNAME,NAME)

)

Table variable can have an index

Yes, now we can have an index on a table variable defined in the T-SQL code. We all know the importance of the table variable. Here is the example code;

Declare @vTabletest TABLE

(

ID int IDENTITY (1,1) PRIMARY KEY,

NAME nvarchar(100),

FATHERNAME nvarchar(100) index idx_FATHERNAME

)

Insert into @vTabletest values ('A','B')

Select * from @vTabletest

Again, if you try to execute the above code in SQL Server 2012, it will generate errors.

You can also create composite indexes in the same manner. Included columns and filtered conditions are not yet supported with Inline specification of indexes.

Clustered Columnstore Indexes

SQL Server 2014 enables you to create a clustered columnstore index. In the prior versions, only non-clustered columnstore indexes were offered.

In order to create a clustered columnstore index on a table, first you will have to create a table without any index on it i.e. a heap;

CREATE TABLE tblTest

(

ID int ,

NAME nvarchar(100),

FATHERNAME nvarchar(100)

)

Now, execute the command to create a column stored Index;

CREATE CLUSTERED COLUMNSTORE INDEX idx_CS_Test on tbltest

The above command converts the table into the clustered columns store index named idx_CS_test. As you can see in the above statement, you cannot specify the key list in the index specification as we used to do in the non-clustered columnstore indexes. This is because the whole table is converted into the columnstore index.

Optimizing SQL Azure Database

2014-03-18T11:30:00.001-07:00

1. Dynamic Management Views

SQL Azure provides a few handy system views called dynamic management views (DMVs) that are also available in SQL Server. SQL Azure exposes a subset of the DMVs, but all those related to query execution are available.

sys.dmexecconnections

sys.dmexecqueryplan

sys.dmexecquerystats

sys.dmexecrequests

sys.dmexecsessions

sys.dmexecsqltext

sys.dmexectextquery_plan

2. Connection pooling

Connection pooling is an important consideration for performance. Although this statement is generally accurate in the world of database programming, it becomes critical for SQL Azure. A poorly designed application may create too many connection requests, which can end up flooding SQL Azure. If too many connection requests are established, your connections will be throttled, meaning that you can no longer connect for a period of time.

3. Indexes

Creating the right indexes can be complex; it can take a long time to fully understand indexing and fine-tune database queries. One of the most important things to remember with indexing is that its primary purpose is to help SQL Azure find the data it needs quickly.

Indexes are like smaller tables that contain a subset of the primary table. The tradeoff is that indexes consume space and must be maintained by the SQL Azure engine as the primary data changes, which can impact performance under certain scenarios.

Remember, you must have a clustered index on each table in SQL Azure Database. Windows Azure SQL Database does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

4. Indexed views

Indexed views are an excellent alternative when you absolutely need to JOIN data, and traditional indexing doesn't yield the performance you're looking for. Indexed views behave like tables; the data covered is materialized to disk so it can be retrieved quickly. Before jumping on indexed views, understand that they have certain limitations and that due to their nature, you may incur a performance hit through the usual Insert, Delete, and Update statements.

5. Stored procedures

You've seen various ways to tune your statements and improve execution plans. However, keep in mind that you also have stored procedures at your disposal. Stored procedures can give you an edge if you need to execute logic that requires a large volume of data. Because you know that returning lots of data turns into a performance problem in SQL Azure, you can place the business logic that needs the data in a stored procedure, and have the procedure return a status code. Because you aren't charged for CPU time, this becomes an affordable option.

Stored procedures can also be an interesting security tool, allowing you proxy the calls to underlying tables through a procedure and never allowing direct access to the tables.

There are a few restrictions in SQL Azure Stored Procedures. You cannot use the following in CREATE PROCEDURE statement;

FOR REPLICATION option
EXTERNAL NAME ( external methods)
ENCRYPTION option

Also, following XML commands doe not work in SQL Azure;

sp_xml_preparedocument
sp_xml_removedocument
OpenXML

We have the workaround to tackle with these commands but that is not in the scope of this article.

NOTE: Reference taken from http://programming4.us/

Introducing Tabular Model - Self-Service Business Intelligence

2014-03-18T07:03:00.003-07:00

Tabular Model is a new feature in SQL Server 2012. Tabular models are in-memory databases in Analysis Services. They use state-of-the-art compression algorithm and multi-threaded query processors named as VertpPaq (xVelocity in-memory analytics engine). This enable fast access to tabular objects and data by reporting applications such as MS Excel.

Tabular Models are created in SQL Server Data Tools (SSDT). It is the upgraded version of BI developer. SSDT is also used to develop Reports, Integration Services and Multidimensional projects. Tabular Model project is deployed as Analysis Services database just like Multidimensional projects. In Tabular Model, we can add partitions for optimization purposes. Row-level security can also be implemented in Tabular Model. We can also import external data to make it a part of the model. We can add relationships, measures and calculated columns on the tabular data in the model. Hierarchies are also supported in Tabular model as in multidimensional model. This makes it more do-able and easy to go with project type in SSAS. As Tabular Model is deployed as Analysis Services database, client applications can connect to the deployed model for reporting purposes.

Analysis services provides three approaches to develop business intelligence semantic models;

1. Tabular

2. Multidimensional

3. PowerPivot

Tabular Model is based on relational structure such as tables and relationships for modeling of data. Multidimensional and data mining are based on OLAP (cubes and dimensions). PowerPivot is a self-service BI tool.

Tabular Model support data access through two modes;

Cached Mode In cached mode, you can integrate data from multiple sources including relational databases, data feeds, and flat text files.

Direct-Query Mode In this mode, you can by-pass the In-Memory model and allow cklient applications to query directly from the relational database. In short, Tabular model is designed to query huge data with much lesser response time in different client application, especially in MS Excel.

Comparing With Multidimensional Model
With the release of Tabular Model, there are few decisions by Managers that has to be made. Some common questions are;

Question 1: When to use Tabular Model and Multidimensional Model?
Answer: Both Tabular and Multidimensional Models are the part of Analysis Services. Bot are developed in SSDT. The answer lies within the data to be used in the model. If you have huge data, like in TBs, to process in the model, then Multidimensional Model is the best suite for you. Tabular Model are more compressed than Multidimensional but it also requires more memory to load and process the data in memory. PowerPivot Models are for even lesser data size.

Question 2: What do we mean by 'External Data' or 'External Source'?
Answer: We can import data in Tabular Model and PowerPivot from External sources. These can be text files, excel files, data feeds and even some document formats. In multidimensional, you can only load data from relational sources using OLEDB and managed providers.

Question 3: What is the core benefit of Tabular Model?
Answer: The core benefit is that they are easy to implement as PowerPivot. You can develop more efficient and effective solution. The data processing is very fast and it is easy to understand, It uses DAX as programming language. DAX is quite similar to Excel formula language.

Question 4: Should we convert the current Multidimensional Models to Tabular?
Answer: NO. The project types are not interchangeable. And it will not be an effective decission to convert the multidimensional model into Tabular model. It is best to use the model for the new requirements.

Analysis Services 2012 Two in One Product
Most important aspect of Analysis Services 2012 is that it is two products in one. In SQL Server 2008 R2 and before, we only had Multidimensional Model option in Analysis services. But in SQL Server 2012, you can choose between Multidimensional and Tabular Model while installing SSAS instance. You need to have separate instance for both if you want to use both, multidimensional and tabular model. In the figure below, you can see that on Feature selection page, there is only one selection for Analysis Services;

But when you pass on to the Analysis configuration page, there you will see a radio button for Server Mode selection. The options are; a. Multidimensional and Data Mining Mode b. Tabular Mode

Licensing
Analysis services 2012 is available in the following editions of SQL Server;

SQL Server Standard Edition
SQL Server BI Edition
SQL Server Enterprise Edition

In SQL Server Standard edition, you can only have Multidimensional Mode. SQL Server BI edition and SQL Server Enterprise editions have both Multidimensional and Tabular Mode. In terms of Analysis Services functionality, these two editions are the same; the only difference between them is that SQL Server Business Intelligence edition licensing is based on buying a server license plus Client Access Licenses (CALs), whereas SQL Server Enterprise edition is licensed on a per-CPU core basis. (You can no longer license SQL Server Enterprise edition on a server-plus-CALs basis as was possible in the past.) In SQL Server Business Intelligence and SQL Server Enterprise editions, both Tabular and Multidimensional models contain all available features and can use as many cores as the operating system makes available. The upshot of this is that it could be more expensive in some situations to use Tabular than Multidimensional because Multidimensional is available in SQL Server Standard edition and Tabular is not. If you have a limited budget, already have existing Multidimensional skills, or are willing to learn them, and your data volumes mean that you do not need to use Multidimensional features such as partitioning, it might make sense to use Multidimensional and SQL Server Standard edition to save money. If you are willing to pay slightly more for SQL Server Business Intelligence edition or SQL Server Enterprise edition, however, then licensing costs should not be a consideration in your choice of model.

A hint to Self-Service BI and Corporate BI
Over the last few years, trends and demand of BI have significantly changed. Now, users are more interested in having the hold on their data and analyze it according to their own run-time requirement on the spot. Prior to that, they had to involve an IT professional to develop complex reports for them. This is Corporate BI. This scenario still exists but for analysis work, it becomes more cost and time consuming for engaging IT professional for the analysis work. This resulted in a new Self-Service BI. There are tools available in the market like QlikView and Tableu that enables users to query their own data in a user friendly interface. Analysis Services is a corporate-BI tool by MS as you need an IT professional to design and build an Analysis Services database. There are disadvantages of self-service BI like poor-quality data, lack of integration between multiple source systems, etc. Still, self-service BI is high on demand and the reason is that most of the data users think that in most cases, the time consuming step of creating a data warehouse is un-necessary in their initial scenario. MS, as a software company could not ignore this market demand, so in 2010, it released its own serf service BI tool, PowerPivot. Tabular Model resembles a lot to PowerPivot and a refined version of PowerPivot as you get the scalability and manageability in the Tabular Model. PowerPivot is more Agile and Self-Service BI tool.

Comparing File Names in a directory with the list in Excel File

2013-12-10T09:18:00.001-08:00

Here is the Script...

---- Get File Names rom you directory

Declare @vOSFiles Table (MyFileName nvarchar(1000),MyDepth int, IsFile bit)

Insert into @vOSFiles

EXEC xp_dirtree 'G:\Atif\', 1, 1

--Select * from @vOSFiles where IsFile = 1

---- Get List of Files from Excel

---- Insert Excel File list to another Temp Table.

---- The structure o fthis table depends upon you excel file and requirement

Declare @vExcelFileList Table (MyFileName nvarchar(1000))

---- This is for xlsx files

Insert into @vExcelFileList

SELECT [MyFileName] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;Database=G:\Budjet.xlsx;HDR=Yes;IMEX=1',

'SELECT * FROM [Sheet2$]')

--Select *

--from @vOSFiles

---- Join the two tables on the File Names

Select a.*, case when b.MyFileName is not Null then 'Exists' Else 'Not Exists' end as FileExistance

from @vOSFiles a

Left Outer Join @vExcelFileList b on b.MyFileName = a.MyFileName

Where a.IsFile = 1

You might want to enable ad hoc query options;

---- Enable AdHoc Queries

--USE MSDB

--GO

--sp_configure 'show advanced options', 1;

--GO

--RECONFIGURE;

--GO

--sp_configure 'Ad Hoc Distributed Queries', 1;

--GO

--RECONFIGURE;

--GO

---- Add OLEDB referance for xlsx

--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

-- , N'AllowInProcess', 1

--GO

--EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

-- , N'DynamicParameters', 1

--GO

For XLS files (earlier 2007 versions of Excel), you can run the OpenRowset query as;

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'

,'Excel 4.0;Database=G:\Budjet.xls','SELECT * FROM [sheet2$]')

5% OFF All Lenovo ThinkPad's. Coupon Code:CJDEC5UK Lenovo UK Homepage

Error Handling in SQL Server 2012

2013-11-25T09:16:00.000-08:00

As we all know that from SQL Server 2005 and onward, we had TRY CATCH to handle the exceptions / errors in t-sql code along with @@ERROR methodology.

@@ERROR

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Simple Example;

CREATE TABLE [dbo].[tblTest](

[NAME] [varchar](100) NOT NULL,

CONSTRAINT [PK_tblTest_1] PRIMARY KEY CLUSTERED

(

[NAME] ASC

)

INSERT INTO tblTest

Select 'Atif'

Union

Select 'Sheikh'

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

if @@ERROR <> 0

print 'Error Generated'

else

Print 'No Error.'

TRY CATCH

We can also make use of TRY CATCH block in order to catch the error;

BEGIN TRY

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

END TRY

BEGIN CATCH

print 'Error Generated.'

END CATCH

We can make use of transactions and roll back transactipon in the CATCH block. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error.

BEGIN TRY

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

END TRY

BEGIN CATCH

print 'Error Generated.'

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

THROW

Now, in SQL Server 2012, we can also use THROW to throw the exception to the application. You can say it works like RAISERROR;

BEGIN TRY

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

END TRY

BEGIN CATCH

print 'Error Generated.';

THROW;

END CATCH

You can use THROW statement like RAISERROR as well;

BEGIN TRY

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

END TRY

BEGIN CATCH

print 'Error Generated.';

THROW 51000, 'Primary Key Violates.', 1;

END CATCH

Limitatoin of Sequence Objects in SQL Server 2012

2013-11-21T04:14:00.000-08:00

Sequence Object introduced in SQL Server 2012 is a good features added by MS SQL Server team. I have already discussed this in my post "Sequence Object in SQL Server 2012" but there are many limitations. I will try to explain the prominent limitations.

Let us create a sequence object in our test database.

CREATE SEQUENCE LimitSequence AS INT
START WITH 1
INCREMENT BY 1
GO

Also, create a test table with ID generated by using the sequence object;

CREATE TABLE dbo.TestLimit(ID INT,Name VARCHAR(100))
GO
INSERT INTO dbo.TestLimit VALUES
(NEXT VALUE FOR LimitSequence,'Atif'),
(NEXT VALUE FOR LimitSequence,'Sheikh'),
(NEXT VALUE FOR LimitSequence,'Asif')
GO

Cannot Use DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT

Now, if you try to run the query using sequence object with any of these clauses, you will get an error message;

Select Distinct NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

On executing the above query, you get an eror message as ;

Msg 11721, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

This applies to all operators.

Using simple ORDER BY clause

If you try the query using the sequence object with simple ORDER by clause, it will generate an error;

Select NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

Order by ID

Error message is;

Msg 11723, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

Good part for this is to user OVER (ORDER BY ).

Select NEXT VALUE FOR LimitSequence,*, ROW_NUMBER() Over (Order by ID) as RNO from dbo.TestLimit

The above query will execute without any error.

TOP

Cannot use with TOP.

Select Top(10) NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

This query will generate error message as;

Msg 11739, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.

As stated in the error message, if ROWCOUNT is set or TOP and OFFSET is used, the query will generate an error message.

CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF

All these are not allowed. I will give the example if ISNULL function;

Select Isnull( NEXT VALUE FOR LimitSequence,0),* from dbo.TestLimit

Erro rmessage is;

Msg 11741, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

You can try the rest of the functions yourself.

WHERE Clause

You cannot use it in WHERE clasuse;

Select * from dbo.TestLimit

where NEXT VALUE FOR LimitSequence = 6

Error message will be;

Msg 11720, Level 15, State 1, Line 2

NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

So, these are few prominent limitation for the sequence objects.

Contained Database

2013-11-20T04:46:00.000-08:00

What is it?

A contained database is a database within a sql server instance that is not dependent upon the instance itself in terms of users and metadata. All users and metadata is stored within the contained database.

What is the purpose?

These database are easy to port from instance / server to instance / server. You don't have to worry about the user logins associated with the database. This is very handy when you are dealing with large number of database users.

Any Important Note / drawback?

Important note is that in SQLServer 2012, only Partial containment type is supported. It means that your database can be a None contained database or partially contained database. A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed. Fully contained user entities (those that never cross the database boundary), for example sys.indexes. Any code that uses these features or any object that references only these entities is also fully contained.

As stated above, SQL Server 2012 is supported with only partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

According to BOL, use the sys.dm_db_uncontained_entities and sys.sql_modules view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

The behavior of partially contained databases differs most distinctly from that of non-contained databases with regard to collation.

How to create?

Ok, enough theory. there are three steps to create a contained database;

1. First, we need to enable the "contained database authentication". We can do this using T-SQL;

sp_configure 'contained database authentication', 1;

RECONFIGURE

2. Then we create a new database as contained database;

USE master

CREATE DATABASE TestDB

CONTAINMENT=PARTIAL
GO

--Create table with records

USE TestDB
GO

CREATE TABLE tblTest(

id int ,
Name varchar (250)
)

INSERT INTO tblTest

VALUES

(10,'Atif'),
(20,'Imran'),
(30,'Asif')
GO

3. Then, at last, we will be creating the user(s) for the our partially contained database;

USE TestDB

CREATE USER TestUser WITH PASSWORD=N'testCUser1$',DEFAULT_SCHEMA=dbo

EXEC sp_addrolemember'db_owner', 'TestUser'

In order to test our exercise, we have to log into a new session using the above created user. Check you server Server authentication settings. As I am using sql server user and not a Domain user, the instance should be configured as "SQL Server and Windows authentication mode". In the figure I have specified the user name and password of the user that we have created in the contained database;

Next, we have to specify the name of the contained database. You will have to type it as the user you have specified does not exists in the sql server instance.

Once the login name is authenticated, you will have you object explorer listing only the contained database;

That's it. We have successfully created a contained database.
Ok, what about the old databases that i need to convert to partial contained database?

We can convert an uncontained database to partial contained database by using the following 3 steps;

1. Enable the "contained database authentication" as above.
2. Alter the database as;

ALTER DATABASE [MyDatabase]

SET CONTAINMENT=PARTIAL

3. In the last step, we move the user(s) to the database;

sp_migrate_user_to_contained

@username = N'MyDBLogin',

@rename = N'keep_name',

@disablelogin = N'disable_login'

In the above statement, we have un-authorized the 'MyDBLogin' to log in the instance. We did not changed the login name here by specifying the value 'keep_name' for @rename.

Now, if you log in using this user as in our last example, you will only see the 'MyDatabase' in teh object explorer.

Hope this will help you.

Sequence Object in SQL Server 2012

2013-11-19T04:34:00.002-08:00

MS SQL Server has introduced a new feature of Sequence Objects in SQL Server 2012. In the prior versions, IDENTITY was used to generate sequence numbers. The limitation of IDENTITY was that it was related to one table. The scope of Sequence Objects is database. So, if you want to add a same value to two tables, you can use this feature. Also, you can generate the next sequence number before going into the transaction. The IDENTITY is incremented in the transaction at the time of INSERT. Another plus point of Sequence Object is that you can define the maximum value for the object and re-initialize the counter of the object without any extra programming effort.

Here is the code of creating teh new sequence object in SQL Server 2012 Management Studio;

CREATE SEQUENCE dbo.NextCustomerID
    as BIGINT
    START WITH 1
    INCREMENT BY 1;
GO

This will add a new sequence object. You can see this object in Object Explorer under your database in Programming --> Sequence folder.

Well, it not over yet. The query used to create this object creates the object with the default values. You can set many parameters. If you right click the newly created object and click properties, this will open the property window for this object. Here you can set Minimum value, Maximum Value, Cycle and Cache.

Minimum and Maximum are the minimum and maximum values for the object. Cycle check box states that the value will re-initialize when the maximum value is reached. You can also set the re-start value (From where it will start after reaching the maximum value). Cache option tells the object how many values it will have remaining to avoid extra Disk I/Os during the increment process. For example, if cache size is set to 25, it will not generate 25 numbers in it memory. It will only keep the current value and the remaining values. If 3 IDs are generated for the object with cache of 25, it will have 4 and 22 in its memory. For more information on cache mechanism you can Click Here and check the Cache Management section.

Ok, here is the code for generating the sequence number from the object;

-- Get the next available ID
DECLARE @CustID BIGINT;
SELECT @CustID = NEXT VALUE FOR dbo.NextCustomerID;
SELECT @CustID as 'Customer ID';

Please remember that the sequence object is not transaction specific. Once you generate the sequence with the NEXT VALUE, it is consumed weather the transaction is committed and rolled back. This is similar to the IDENTITY. Hence, the sequence objects also may have gaps in between as we have in IDENTITY columns.

In case you want to restart the sequence, you can use;

ALTER SEQUENCE dbo.NextCustomerID

RESTART

You can use the OVER ORDER BY clause with NEXT VALUE FOR for generating the next sequence values;

SELECT NEXT VALUE FOR dbo.NextCustomerID OVER (ORDER BY RNO) AS ListNumber,

Name

FROM tblTest ;

You can check books online for more information.

There are also the limitations of the Sequence Objects which I will cover in the separate topic and try to explain with examples.

Columnstore Indexes

2013-11-18T04:30:00.001-08:00

This is the new feature added in SQL Server 2012 to overcome query performance and index data compression issues. The data stored in the columnstore index is pretty much compressed and very optimized. In SQL Srever 2012, if you have a columnstore index, you cannot perform the DML operations on the table i.e. you cannot insert / update or delete any record. The purpose of this index is for Data warehousing. You have to devise the logic in order to populate the data, for example table partitioning. But this issue is resolved in SQL Server 2014. But there, you will have to create a CLUSTERED COLUMNSTORE INDEX.

You can add this index simply by right clicking the Index folder under the table on which you want to add the column store index;

On clicking the option, a screen will open asking to add the column(s) on which you want to create a column store index (FirstName, LastName).

Leave rest of the properties to default. Click OK button to add the index.

Now, question is how the data will be stored? We have two types of data storge in sql server, RowStore and Column Store. Assume you have a tabel as

RNO NAME FNAME
1 Atif Sheikh
2 Asif Ahmed
3 Imran Khan

In RowStore index, the data will be saved as;

Atif,Sheikh
Asif,Ahmed
Imran,Khan

The above data will be saved as a row in the 8k page.

And this will go on until the 8k page is complete.

In case of Columnstore index, the data will be stored as a segment of its respective column. Each segment have only one column. This goes for all the columns in the index. A Column can span on multiple segments and a segment may have multiple data pages. As for above example the data will be stored as;

Col1 Col2

Atif Sheikh
Asif Ahmed
Imran Khan

These columns are just like pointers in the 8k page of the index. One page can have multiple columns

Now if you will query with NAME or FNAME in your WHERE condition, the data search and retrieval will be very fast as each condition will have to check one single column. You can check this with Statistics and Execution plans on you tables.

Paging in SQL Server 2012

2013-11-13T06:37:00.002-08:00

As we all know that MS has given us the FETCH NEXT clause in the query to apply paging. Let me explain this feature by adding a temp table of 1 million records.

with wcte as (

Select Row_number()over (order by a.name) as RNO

from sys.columns a, sys.columns b, sys.columns c

) Select rno , 'Name: ' + CAST(rno as varchar(10)) as Name

into tblTest

from wcte where RNO <= 1000000

Add primary key on RNO column through the designer. This will add a clustered index on this column.

Now, lets write the query for the SQL Server 2005 / 2008 / 2008 R2 using CTE;

;With wcte as (

Select Row_Number() over (Order by Rno) Rno2,* from tblTest WHERE Name is Not Null

) Select * from wcte where rno2 > 899979 and rno2 < 900000

order by rno

If you look at the execution plan of this query, it will be like this;

Now lets write the query for SQL Server 2012 paging;

Declare @vPageNo int

Declare @vOffset int

Declare @vPageSize int

Set @vPageNo = 45000

Set @vPageSize = 20

Set @vOffset = (@vPageNo-1) * @vPageSize

SELECT *

FROM tblTest

WHERE Name is Not Null

ORDER BY rno

OFFSET @vOffset ROWS

FETCH NEXT @vPageSize ROWS ONLY

I have added the variables for passing OFFSET and PAGESIZE. This gives an additional hint for passing these from the front end as Input parameters of stored procedure.

The execution plan for this query is;

Now, it is very clear that the paging query of SQL Server 2012 performed very limited functions and performed 100% index scan. The SQL Server 2008 query performed many additional functions which makes it a bit smaller with my 2 column table.

Results might be different if you apply the same scenario directly on the clustered indexed column (RNO of tbltest in my case) instead of applying Row_Number() function. This is not the real time case as you never have IDs in sequence in you db tables as sequences break when you delete or migrate. More importantly, you apply paging on queries with WHERE clause. And you cannot use ID columns for paging in any case with WHERE Clause.