Included Column Index with non clustered index in SQL Server 2005/2008/Denali

My previous article was based on “Non clustered Index” and this article focuses on “Included Column” feature of Index which was introduced in SQL Server 2005 and still there in newer version.
As long as architecture of “Included Column” concerns, whatever columns you have defined under “Include” clause under the index, those will be stored on the Leaf pages, it won’t get stored on the Root page or Intermediate page of the index.
Now, let us talk little bit about the benefit we are going to get out of this feature.
The main feature is that, the columns you have under “Include” clause of “Create Index” statement would not affect the size of the index. Index has limitation that you can have it on maximum of 16 column / 900 bytes. So no matter how big columns you are going to use in your “Include”, you will get benefit for sure.
You should keep those columns in “Include” clause which generally comes under “SELECT” clause and not being used much in “WHERE”, “GROUP BY” or “ON” clause of “JOIN”.
We are going to create one table with big column size and will try to create Non Clustered Index on that.
–create dummy table and see whether we are able to create index on that or not.
create table NonClustCheck
(
Col1 char(500),
col2 char(400),
col3 char(10)
)
GO
–if you will try following idex, you will greeted with error message as follows:
–Msg 1944, Level 16, State 1, Line 1
–Index ‘idx_NonClustCheck’ was not created. This index has a key length of at least 910 bytes.
–The maximum permissible key length is 900 bytes.
create nonclustered indexidx_NonClustCheck on NonClustCheck(col1,col2,col3)
go
–you can create following index on the same table
create nonclustered indexidx_NonClustCheck1 on NonClustCheck(col1)
INCLUDE(col2,col3)
go
we finally we have created non clustered index with one key column (Col1) and two included column (Col2 and Col3). Let us check whether this index gets scan or seek or optimizer decides not to use this.
–well there is no data in this table yet,
–even just wanted to see whether Non clustered index is having any effect or not.
–run following query with execution plan and you can see Index Seek
— Ctrl + M and than F5 to run query with execution plan.
select * from NonClustCheck where Col1=‘SQLHub.Com’
GO
–you can see Col2 is not as the Index Key,
–even you can see that our non clustered index is getting SCAN.
select * from NonClustCheck where Col2=‘hello’
GO
Hope this will be helpful to you. Do drop comments; it will encourage me for sure.
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

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

sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005

sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats in SQL Server 2005

Today I am happy to introduced one more article written by Mr. Mark Will regarding two very useful data management views  (DMV) in SQL Server 2005. I am sure every reader of my blog will be happy to read it as the information provided in the article could become very useful asset for everybody who are using SQL Server 2005.

Introducing SQL 2005 Two Data Management Views

by Mark Wills

In SQL 2005, new Data Management Views were introduced known as DMV’s.

I have recently been involved in some discussions as to what these views really do, and thought I might share some relatively light hearted discussion.

At first glance they give information which doesn’t reconcile. And that is why they are different, it is more the differences that become important rather than they don’t match.

They actually tell us different pieces of the index puzzle, and collectively, are very telling about your index designs (which will be a different post).

The views are :

sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted and updated each time the plan is executed.

sys.dm_db_index_operational_stats reports on all indexes regardless of whether they have been used and records how many times the storage engine executes a specific operation on the index.

The way I remember how to use them is by name (duh), as in “is my index useful” then usage, “is my index operating efficiently” then operational.

Let’s create a couple of test tables for this purpose :

— first a ‘heap’ table ie one with no PK or clustered index

IF object_id(‘tst_tbl_heap’,‘U’) is not null drop table tst_tbl_heap

CREATE TABLE tst_tbl_heap (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

go

— now a ‘clustered’ table

IF object_id(‘tst_tbl_indexes’,‘U’) is not null drop table tst_tbl_indexes

CREATE TABLE tst_tbl_indexes (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

ALTER TABLE tst_tbl_indexes ADD CONSTRAINT PK_tst_tbl_id PRIMARY KEY CLUSTERED(ID)

CREATE UNIQUE INDEX idx_tst_tbl_SN ON tst_tbl_indexes(SecurityNumber)

go

–OK, now lets see what we have :

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— we get nothing – and why not ? remember the name ? we haven’t used any indexes yet…

— but…

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s            — note : this has parameters

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

 

— does show us our two indexes, and even shows us a row for our table without an index.

— So, it is not just for indexes huh !

— now, remember our name ? operational – but are they working, no, the counts are zero.

— lets now add some data…

INSERT tst_tbl_heap(ID,FirstName,SecurityNumber) values (1,‘Mark’,‘1234567’)

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber) values (1,‘Mark’,‘1234567’)

— now lets look again at our DMV’s

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— Now we get something ! despite being called indexes, not just for indexes.

— Similarly for below we also return information

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— We wont bother anymore about the heap table, you can play with that

— the lesson was that even a table without indexes is getting in there.

— Now lets do an insert

 

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber)

SELECT 2 as id,‘Mark2’ as firstname,‘2234567’ as SecurityNumber union all

SELECT 3 as id,‘Mark3’ as firstname,‘3234567’ as SecurityNumber

 

— now lets look again at our DMV’s

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

 

— So, what happens with a select ?

SELECT * from tst_tbl_indexes where ID = 2

— now lets look again at our DMV’s

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— we get user_seeks=1

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— singleton_lookups=1

— so let’s now try another query

 

SELECT * from tst_tbl_indexes where ID in (1,2,3)

go

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— we get user_seeks=2

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— singleton_lookups=4 on our PK, nothing on the second (1 per selected rows above)

— and finally clean up those tables

IF object_id(‘tst_tbl_heap’,‘U’) is not null drop table tst_tbl_heap

IF object_id(‘tst_tbl_indexes’,‘U’) is not null drop table tst_tbl_indexes

 

Now I am not going to go into detail – there are plenty of other postings out there

but what I am going to do is to summarise how we can use these differences

sys.dm_db_index_usage_stats

Is cleared when service starts / reboots etc. So keep that firmly in mind

Good to help identify if an index is used – no entry unless it is

Good to help identify if a table is used – no entry unless it is

Has handy dates can help show when types of activity were last used

Has counts to help identify frequency of use

If machine has been up for the entire period that encapsulates all usage then can highlight unused indexes and consider their removal

 

sys.dm_db_index_operational_stats

Exists when table / indexes are created

Shows volumes of activity (plan, rows, pages), not just an instance

Far more detailed to help identify what type of activity

Shows row_lock_wait_count – indicating lock contention

Can measure the cost of having an index, or missing one

 

Lets look at the different types of activity that is going to be recorded:

 

SQL Statement   Read Write

Select          Yes  No

Insert          No   Yes on all indexes

Update          Yes  Yes if row affects the index

Delete          Yes  Yes

 

Armed with the above table, looking at DMV’s activity, you can soon work out where possible areas of further inspection are. Now go read books on line for a more detailed understanding of what each column is saying…

 

http://msdn.microsoft.com/en-us/library/ms188755.aspx

http://msdn.microsoft.com/en-us/library/ms174281.aspx

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

Where is my Open Package option of DTS in SSMS?

If you are working with SQL Server 7.0 or SQL Server 2000 since long and jump into SQL Server 2005 and/or 2008 suddenly you may be finding the option to open DTS package of SQL Server 2000 in SSMS. It was very easy from Enterprise manager of SQL Server2000. You can expand “Data Transformation Service” table under the “Database” tab, right click on that and click on “Open Package “ command. Have a look at screen capture.

But you will not be able to see “Data Transformation Service” command in SSMS directly. Isn’t it there? Answer is, it is there but at other location.

 Expand the Management object.

 Open the Legacy object.

 Right click on Data Transformation Services.

 Click on “Open Package File”

Have a look at screen capture:

However, you can open old .DTS packages in SSMS but it is better to upgrade it to .DTSX package. There is one nice article at simple-talk.com. Have a look.

http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssismigration/

 

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

Make all columns from all tables in database, NOT NULL and set Default value in SQL Server 2008

Today I helped one person in one forum, he wanted to generate script to make all columns in all tables NOT NULL and wanted to set DEFAULT value 0 for all numeric data types. I felt to share it with all my readers as it could be interesting to know and sometime could be very helpful and handy with some customize changes.
select
not_null = ‘alter table ‘ + table_name + ‘ alter column ‘
                  + column_name + ‘ ‘ + data_type
                  + case when data_type = ‘numeric’ then ‘(‘ else end
                  + case when data_type = ‘numeric’ then convert(varchar,numeric_precision_radix) else end
                  + case when data_type = ‘numeric’ then ‘,’ else end
                  + case when data_type = ‘numeric’ then convert(varchar,numeric_scale) else end
                  + case when data_type = ‘numeric’ then ‘)’ else end
                  + ‘ not null ‘
,default_0 = ‘alter table ‘ + table_name
                  + ‘ add default 0 for ‘ + column_name
from information_schema.columns
where COLUMN_DEFAULT is null
and   data_type not like ‘%char%’
and   data_type not like ‘%time%’
and   data_type not like ‘%image%’
and   data_type not like ‘%binary%’

 

Above query will list the T-SQL script which you can run in your query editor.
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

Find database principal and its member in SQL Server 2005

Today I was digging about principal and member of that principal from sys.database_principal. It might be handy and very useful sometime to list out all users’ list with its associated principal name. I have used sys.database_pricipal to get details about principal and its member’s details and sys.database_role_members for relationship of principal and its member.

Have a look at my T-SQL

SELECT
MemPri.Name as MemberPrincipal,     
RolePri.Name as RolePrincipal,
MemPri.Create_Date as DateCreated,
MemPri.Modify_date as DateModified,
MemPri.Type_Desc as Description
FROM
sys.database_role_members RoleMem
JOIN sys.database_principals RolePri ON RoleMem.role_principal_id = RolePri.principal_id
JOIN sys.database_principals MemPri ON RoleMem.member_principal_id = MemPri.principal_id
order by MemberPrincipal
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

Is MSSQLSERVER installed on server? Check from vb.net or c# with System.ServiceProcess

We often need to check whether SQL Server installed on the server before installing our application developed in VB.NET or in C#. To find out any service installed on your server, you have to use ServiceProcess class from System. You can’t directly call System.ServiceProcess in your VB.NET or in C#, you have to first ADD REFERENCE to your application from “.NET” tab of ADD REFERENCE dilog box. Once you are done with adding reference, you can call serviceProcess

VB.NET
Imports System.ServiceProcess

C#
using System.ServiceProcess

Basically, ServiceProcess will give an array of all processes running on the box, you have to identify your service from that array. Generally SQL Server installed with default instance, which is MSSQLSERVER, you can simply check that instance name from array, if client machine has not installed with SQL server with default instance than there one small ray of hope is, you can check array item with substring item “SQL”, this way is not 100% sure shot, if named instance is completely different and doesn’t contain “SQL” word at all, this method won’t work, you are out of luck in this case.
Let us have a look at code in VB.NET, however, you can simply modify your syntax and made it work in C# as well.

Imports System.ServiceProcess

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim services As ServiceController()
        services = ServiceController.GetServices()
        For i As Integer = 0 To services.Length
            If (services(i).ServiceName.ToLower() = “mssqlserver”) Then
                Label1.Text = “Service Found!!!”
                Exit For
            End If
        Next i
    End Sub
End Class

Create one windows application in VB.NET, call System.ServiceProcess namespace and have above code in your Form1’s page load event. Result will be printed on the label1 of the form1.

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

Find Primary key, foreign key in all columns and all table: SQL Server 2005

We generally used to find primary key or foreign key by SSMS or by using SP_help for one table. Sometime we need to list out all primary key and/or foreign key and/or CHECK constraint and/or UNIQUE key available in database for all table and columns. How can we do that? I had generated very small and handy script for my personal use which I am going to share with you now.
–not only primary key, you can find
–CHECK
–FOREIGN KEY
–PRIMARY KEY
–UNIQUE KEY
select p.table_name,c.column_name,p.constraint_name
from
information_schema.table_constraints p join
information_schema.key_column_usage c
ON
p.table_name=c.table_name
and
p.constraint_name=c.constraint_name
where p.constraint_type=‘Primary Key’
order by p.table_name,c.column_name
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