Creating Indexes using T-SQL

In my previous post, we discussed creating indexes using SQL Management Studio. You can achieve the same results using SQL Scripts.

Using T-SQL

1.  Create a Non-Clustered, Non-Unique Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:56:50 ******/
CREATE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

2. Create a Unique Non-Clustered Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:59:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

HINTS / CLAUSE DEFINITIONS

PAD_INDEX
This option specifies whether you want to leave some space in each node (also referred to as page) for future inserts/updates. This is only useful when you specify a fill factor, because it uses the % specified in fill factor. Default is OFF.

STATISTICS_NORECOMPUTE
This flag determines whether index statistics are automatically recomputed. If you set it to ON, your SQL Query Optmizer may not be able to pick the optimal execution plan for any queries using this table.

SORT_IN_TEMPDB
Specifies that during index build/rebuild, intermediate sort results will be stored in tempDB. If your tempDB is on a different disk(s) than your production DB, it may reduce the time needed to create an index.

IGNORE_DUP_KEY
As I discussed in one of my previous post, when this option is ON and an attempt is made to insert a duplicate key, server issues a warning and ignores the duplicate row. If this option is OFF, server issues an error message and rolls back the entire INSERT. This clause can only be turned on if you have specified UNIQUE clause in your index.

DROP_EXISTING
This clause signals the SQL Server to drop and rebuild the pre-existing index with the same name. When you drop a clustered index, all non-clustered indexes must be rebuilt because they contain pointers to clustered index keys. This clause is extremely useful when dropping a clustered index on the table that also has non-clustered indexes. The non-clustered indexes are rebuilt only once and only if the keys are different.

ONLINE
This clause needs some explanation. When this clause is ON, it means database can be online i.e. being used for other processes while Index is being built or rebuilt. In other words, Index operations do not need exclusive lock. Default is OFF meaning indexing operation requires exclusive lock on the table. This was a nice enhancement in SQL 2005. Prior versions required exclusive lock for index operations. Some of columns such as VARCHAR(MAX) cannot be indexed while online.

ALLOW_ROW_LOCKS
This clause specifies whether data row is locked when performing operations on the indexed keys. When performing OLTP operations, it is a good practice to leave turn this ON.

ALLOW_PAGE_LOCKS
This clause determines whether the entire data page will be locked during index operations.

If both clauses are OFF, SQL Engine will not lock data page or data rows instead entire table will be locked during the operation.

Generally, it is a good idea to leave the defaults alone unless you have a very good reason to change them.

Thank you and as always, your comments are welcome!