Clustered Index

Question

Which type of index(clustered/non clustrered) should be used for Insert/Update/Delete statement in SQL Server. I know it creates an additional overhead but is it better in performance as comparison to non clustered index? Also which index should be use for Select statements in SQL Server?

Answer 1

I am not quite sure what you mean by "should be used for Insert/Update/Delete statement" but in my opinion every table should have a clustered index. The clustered index specifies the order in which the data is actually stored. If a clustered index is not defined the data will simply be stored in a heap. If you don't have a natural column to serve as you clustered index you could always just create an identity column as an int or bigint like this.

CREATE TABLE [dbo].[demo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nchar](10) NULL,
[LastName] [nchar](10) NULL,
[Job] [nchar](10) NULL,
 CONSTRAINT [PK_demo] PRIMARY KEY CLUSTERED 
(
[ID] ASC
))

Answer 2

Not 100% sure what you're expecting to hear - you can only ever have a single clustering index on a table, and by default, every table (with very few edge case exceptions) should have one. All indices typically help your SELECTs the most and some tend to hurt the INSERTs, DELETEs and possibly UPDATEs a bit (or a lot, if chosen poorly).

A clustered index makes a table faster, for every operation. YES! It does. See Kim Tripp's excellent The Clustered Index Debate continues for background info. She also mentions her main criteria for a clustered index:

• narrow
• static (never changes)
• unique
• if ever possible: ever increasing

INT IDENTITY fulfills this perfectly - GUID's do not. See GUID's as Primary Key for extensive background info.

Why narrow? Because the clustering key is added to each and every index page of each and every non-clustered index on the same table (in order to be able to actually look up the data row, if needed). You don't want to have VARCHAR(200) in your clustering key....

Why unique?? See above - the clustering key is the item and mechanism that SQL Server uses to uniquely find a data row. It has to be unique. If you pick a non-unique clustering key, SQL Server itself will add a 4-byte uniqueifier to your keys. Be careful of that!

Next: non-clustered indices. Basically there's one rule: any foreign key in a child table referencing another table should be indexed, it'll speed up JOINs and other operations.

Furthermore, any queries that have WHERE clauses are a good candidate - pick those first which are executed a lot. Put indices on columns that show up in WHERE clauses, in ORDER BY statements.

Next: measure your system, check the DMV's (dynamic management views) for hints about unused or missing indices, and tweak your system over and over again. It's an ongoing process, you'll never be done!

Another word of warning: with a truckload of indices, you can make any SELECT query go really really fast. But at the same time, INSERTs, UPDATEs and DELETEs which have to update all the indices involved might suffer. If you only ever SELECT - go nuts! Otherwise, it's a fine and delicate balancing act. You can always tweak a single query beyond belief - but the rest of your system might suffer in doing so. Don't over-index your database! Put a few good indices in place, check and observe how the system behaves, and then maybe add another one or two, and again: observe how the total system performance is affected by that.