Nonclustered Index in SQL Server

After finishing two articles on Index “Some basics about Index in SQL Server” and “Clustered Index in SQL Server”, this is now time to explore nonclustered index in SQL Server. There are only two major difference between Clustered and Nonclustered Index are as follows:
1.)    You can have only one Clustered index per table and 249 (till SQL Server 2005) and 999 (after SQL Server 2005) Nonclustered Index per table
2.)    Clustered Index stores actual row data in the leaf level and nonclustered index would stores only those columns which are included in nonclustered index and pointer to the actual row which may be in clustered index or in heap (know more about leaf, heap, RID etc. from here).
Like clustered index, you can have one or multiple columns defined in Nonclustered Index too. Order of the column defined in Nonclustered plays an important role to meet Index seek, as I told you in previous article too that Index seek is good than Index Scan, so one should try to meet up Index seek as long as possible.
We are going to use same database and “Orders” table defined in previous article “Clustered Index in SQL Server”.
–creating nonclustered index,
CREATE NONCLUSTERED INDEXidx_orderdate on Orders(orderdate,orderid)
–run following query with execution plan and see the results in execution plan
–you can see execution plan with the following steps
–first select below given query
–Press Ctrl+M
–press F5
SELECT OrderDate,orderid from orders where OrderDate = ‘2010-02-01 00:04:00.000’
You will see your nonClustered Index Seek in your execution plan.
 
There are few more indexes under the same category like INCLUDE columns, Filtered Index, Covering Index which we will be exploring very soon.
if you want to refer all other articles related to index, click here.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Clustered Index in SQL Server

As per the promise I have made in “Some basics about Index in SQL Server” article, I am coming up with each Indexes of SQL Server and first and most important is “Clustered Index”. 
Understanding of Clustered Index:
As I told in previous article too that Index is the key of performance, good and managed Index could boost up your speed of retrieving of data from table.
Clustered Index contains actual data of the table in leaf level pages in logically sorted order (to understand root and leaf level page, click here). Since it is logically sorted, it doesn’t need to go for all data present there in Index. 
For example if you are looking for the phone number of “Ritesh Shah” in telephone directory, you can move to a page which has phone number of the person whose first name starts with “RI” and once all instance of “RI” over in directory, you don’t need to move on as there is NO CHANCE, you will get “Ritesh” anywhere in directory except those particular pages.
BTW, if you don’t have clustered index on your table, than your table would be called “HEAP”, which wouldn’t not have logically sorted data so if you are searching for “Ritesh Shah”, you can to check complete table as you never know, where you will find “Ritesh Shah”, just one method, go each and every row of table and check for matching criteria.
Like any other columns, you can define clustered index in more than one field too and all the columns covered up under the index, called key column.
While choosing a prime candidate for Clustered Index column in your table, you have to select the columns which meet few of the general criteria defined below. (you can say following criteria as a best practice while choosing index candidate)
–> You key column or combination of key columns should be unique and not null. If your You key column or combination of key columns are not unique than SQL Server has to add one more hidden column of 4-byte INT to make it unique. However, you can’t see that hidden column neither can query it directly; it would be purely for SQL Server’s internal use.
–> It should be short as wide key value would increase the depth of Clustered Index and will reduce the performance a bit and also increase the size of non-clustered index as it is being there as a reference in all non-clustered index.
–> Select less changing or no changing fields for you clustered index as Key value indicates the location of page where actual data resides, if you change this key value, row has to be deleted from that page and has to move to another appropriate page which reduces the performance and increase unnecessary overhead to IO.
Generally whenever you make Primary Key in any of your table, SQL Server itself create clustered index on it but if you want to keep clustered index on any other column(s) due to high selectivity on those column(s), you can do it.
Have you got bored of so long theory? Let us do some practical and check it out?
–create one database which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLEorders
END
GO
–creating table
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big table)
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
–creating clustered index, however it is not meeting the criteria I have
–given in article, but this is ok for example
CREATE CLUSTERED INDEXidx_refno ON orders(refno)
GO
–run both of the following query with execution plan and see the results in execution plan
–you can see execution plan with the following steps
–first select both of the below given query
–Press Ctrl+M
–press F5
SELECT TOP 10 * from orders whererefno=4
SELECT TOP 10 * from orders whereOrderDate = ‘2010-02-01 00:04:00.000’
GO
–if you wish, you can uncomment below code and delete SQLHub database
–use master
–go
–drop database sqlhub
If you execute both the query with “Actual Execution Plan”, you will see first query is having Index Seek and second query is having Index Scan. 
Seek and Scan is really interesting topic which I will cover later but just keep in mind that, Seek is good, Scan is bad as it will check all records of the index.
if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Some basics about Index in SQL Server

I wrote an article about index “Be selective while creating Index” on my blog few days back based on the questionI have received in my “ASK Profile” in BeyondRelational.Com.
After writing above article I felt to write few more stuff regarding Index as I still see so many queries, concerns regarding index in developer’s mind, especially who are .NET developer and have very less touch with SQL Server. I know this is not the task of .NET developer but there are so many companies in which .NET developer used to undergo the task of creating database table, SPs etc.
Since most of the people knew that Index is the key of good performance over SELECT statements, very few of them really know how to utilize this sword which has blade on both the side, if you don’t use it wisely, it will harm your performance too.
BTW, all discussion about index in this article is generic for SQL Server; it is not related to specific SQL Server version.
Before jumping more into the Index concept, let me tell you that all data in SQL Server being stored in page, one page consume 8KB. So whenever you create any table and insert data, it goes to one page, suppose you have 4KB of data in one row than two row will comes in one SQL Server page.
Understanding of Index:
Index is nothing but just a kind of data structure which helps optimizer to find data row easily and fast. You can compare it with the “Index” you get in almost all books. If you know the topic you wanted to find, look for it in Book’s Index and you will get page number where that topic is explained.
Generally you can define index on one or more than one column, if you define your index on more than one column, it is called “Composite Index”. All the columns you have created an Index on are called Key Columns.
Have you ever studied “B-Tree” in any of the OOPs languages like C++, C# etc.? Index is kind of “B-Tree” and store data in “B-Tree” structure. It suppose to have one Root Page, it may or may not have Intermediate page (based on the size of data) and one or more than one leaf pages. In sort, Root page would be the top of index structure and leaf page would be lower part of the index structure.
You will have entry of each row of your data table in leaf level pages sorted in logical order.  Mainly there are two type of indexes you can create on SQL Server table (I am not considering all new type of index comes up with each new version of SQL Server, I may cover those up in future article).
1.)    Clustered Index
2.)    Non-Clustered Index
You can have only one clustered Index on each table as Clustered Index stores data in logical order leaf pages of your clustered index has actual sorted data within and this is the reason why you can’t have more than one clustered index on your table.
As long as non clustered index concern, you can have up to 249 Non-Clustered Index per table up to SQL Server 2005 and in later version, you can have limit of 999. Non-Clustered Index don’t store data sorted physically in its leaf level pages but it stores the pointer of the each row of the included column. Pointer may points to clustered index key and if you don’t have clustered index in your table than pointer points to “Row Identifier”.
 “Row-Identifier” is nothing but the unique combination of File ID and Page Number and Slot Index crated by SQL Server to identify each row uniquely in absence of Clustered Index.
So this is it, for the basics of Index, I will come up with some more detailed article regarding each type of index very soon.
if you want to refer all other articles related to index, click here
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Cannot create index on view Msg 1940, Level 16, State 1, Line 1

If you are using View, you might have come across error message something like this:
Msg 1940, Level 16, State 1, Line 1
Cannot create index on view ‘ViewName’. It does not have a unique clustered index.
I have seen this question many times in different SQL Server forums so finally decided to write something about this error. If you have view and you want to create Index on that view, that is fine but there is one prerequisite, you have to have one Unique Clustered Index in that view then and then you will be able to create NonClustered Index on that view. You may face this error in any SQL Server version after SQL Server 2000.
Let us see it by example.
–create emp table
create table emps
(
      ID int,
      name varchar(50),
      dept varchar(50),
      company varchar(50)
)
GO
 
–create view on that table
create view dbo.Vemps
as
select name,dept,company from dbo.emps

 
–check the view whether it is working
select * from vemps


–create index on view
CREATE nonCLUSTERED INDEX id_View_Vemps
ON dbo.vemps(name,dept)


–as soon as you will try to create above index,
–you will be greeted by following error.


–Msg 1939, Level 16, State 1, Line 1
–Cannot create index on view ‘Vemps’ because the view is not schema bound.


–reason of above error is, View should be with schemabindings.
–let us drop view and recreate it.


drop view dbo.vemps
go


create view dbo.Vemps
WITH SCHEMABINDING
as
select name,dept,company from dbo.emps
GO


–now let us again create index.
CREATE nonCLUSTERED INDEX id_View_Vemps
ON dbo.vemps(name,dept)
go


–this time you will be greeted with following error.


–Msg 1940, Level 16, State 1, Line 2
–Cannot create index on view ‘dbo.vemps’. It does not have a unique clustered index.


–if you create unique clustered index first and then you will be able to create
–nonclustered index on view
CREATE unique CLUSTERED INDEX id_View_Vemps
ON dbo.vemps(name,dept)
GO


–now create nonclustered index
CREATE nonCLUSTERED INDEX id_View_Vemps_non_clust
ON dbo.vemps(company)


–select your view
select * from vemps



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005

It is often useful to keep an eye on the size of your database, indexes always plays an important role in SQL Server databases so it is really good to keep an eye on that as well. We used to do rebuild, defrag indexes sooner or later but have you ever think about how much space those indexes consumes? Let us try to find out with “SysIndexes”.
If you observe SysIndexes table than you came to know that it has lots of crucial information about clustered and non-clustered indexes. How can you differentiate clustered and non-clustered indexes from “SysIndexes” ? well, it is really very easy, Clustered index always have “1” in IndID field and non-clustered index always have >1 in IndID field.
You might even know that data used to get stored in page of 8KB in SQL Server so we can calculate those pages of 8KB (8192 byte) to get values in MB.
Now let us have a look at simple yet useful T-SQL statement.
–for non-clustered index
select sum(cast(reserved as bigint))*8192/(1024*1024) AS TotalMegabytes_Non_Clust
from sysindexes
where indid > 1;
go
–for clustered index
select sum(cast(reserved as bigint))*8192/(1024*1024) AS TotalMegabytes_Clust
from sysindexes
where indid = 1;
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

DBCC SHOWCONTIG and DBCC INDEXDEFRAG in SQL Server 2005

Index is most important part of the SQL Server though it is not a concept of RDBMS. Index is really important to boost up the speed of data retrieval but just creating index will not do, there must be a policy of maintaining an index also, because whenever you perform any insert, update or delete operation on the table data-distribution states will get out of date.

DBCC INDEXDEFRAG command is used to maintain the index and its stat whereas DBCC SHOWCONTIG used to see fragmentation details and density for a given index and/or table.

Let us see it practically.

–create one table for test

create table DBCCTest

(

ID uniqueidentifier not null CONSTRAINT pkID PRIMARY KEY CLUSTERED DEFAULT NEWID(),

name varchar(20),

DateEntered datetime DEFAULT GETDATE()

)

–to check the index density and performance, you have to have couple of hundred thousand rows.

–let us create one loop to enter data

–I will run below batch five times to enter

–quarter milion of data row

SET NOCOUNT ON

DECLARE @I INT

SET @I =0

WHILE @I<50000

BEGIN

–we won’t insert ID and DateEntered as we have default value there

INSERT INTO DBCCTest (Name) VALUES(‘Ritesh’)

SET @I=@I+1

END

–now let us check states of indexes by DBCC ShowCOntig

DBCC SHOWCONTIG(DBCCTest) WITH ALL_INDEXES

–DBCC SHOWCONTIG scanning ‘DBCCTest’ table…

–Table: ‘DBCCTest’ (1028198713); index ID: 1, database ID: 7

–TABLE level scan performed.

— Pages Scanned…………………………..: 1974

— Extents Scanned…………………………: 249

— Extent Switches…………………………: 1973

— Avg. Pages per Extent……………………: 7.9

— Scan Density [Best Count:Actual Count]…….: 12.51% [247:1974]

— Logical Scan Fragmentation ………………: 99.19%

— Extent Scan Fragmentation ……………….: 0.40%

— Avg. Bytes Free per Page…………………: 2650.2

— Avg. Page Density (full)…………………: 67.26%

–DBCC execution completed. If DBCC printed error messages, contact your system administrator.

–above is the results of SHOWCONTIG command.

–now let us use INDEXDEFRAG

DBCC INDEXDEFRAG (‘ADVENTUREWORKS’,‘DBCCTest’,‘pkID’)

–here is the results of index defragmentation

–pages scanned pages moved pages removed

–1949 1332 638

–now again check SHOWCONFIG

DBCC SHOWCONTIG(DBCCTest) WITH ALL_INDEXES

–SEE the result difference in both SHOWCONTIG run.

–DBCC SHOWCONTIG scanning ‘DBCCTest’ table…

–Table: ‘DBCCTest’ (1028198713); index ID: 1, database ID: 7

–TABLE level scan performed.

— Pages Scanned…………………………..: 1336

— Extents Scanned…………………………: 171

— Extent Switches…………………………: 176

— Avg. Pages per Extent……………………: 7.8

— Scan Density [Best Count:Actual Count]…….: 94.35% [167:177]

— Logical Scan Fragmentation ………………: 1.12%

— Extent Scan Fragmentation ……………….: 7.02%

— Avg. Bytes Free per Page…………………: 49.6

— Avg. Page Density (full)…………………: 99.39%

–DBCC execution completed. If DBCC printed error messages, contact your system administrator.

–let us not keep big garbage after checking

DROP TABLE DBCCTest

Happy SQLing with SQLHub!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah