asked
Is it preferable, to place the Nullable column in the start of the index ?
Hi Everyone, If Nullable column is placed in the start of the index, will it decrease the performance ? Kindly provide me some information about this. Regards, Satya
The fact that a column can have NULLs shouldn't make too much of a performance difference as part of an index in terms of structure. The main thing to consider is the index structure in terms of "selectiveness" and what queries you are dealing with. Remember, an index is used for SELECTing as well as DELETE and UPDATE commands. I would take an index as a whole into consideration rather than the single column being NULLable or not. You have asked quite a few index questions recently, may I suggest getting hold of a copy of [SQL Server Internals][1] by Kalen Delaney? It is an excellent book and could clear up a few of your questions. [1]:
http://www.amazon.com/Microsoft®-SQL-Server®-2008-Internals/dp/0735626243/ref=sr_1_1?ie=UTF8&qid=1331927070&sr=8-1
Your indexes should be based upon their usage patterns by queries, not by their data types. Bear in mind that depending upon the order of those columns the index may or may not get used. Take the following simplistic example. Based upon the query it will either use or not use the index because of the column order. CREATE TABLE T1 (c1 INT NOT NULL IDENTITY PRIMARY KEY, c2 INT NULL, c3 INT NULL, c4 INT NULL) CREATE NONCLUSTERED INDEX I1 ON t1(c2, c3); INSERT INTO t1 VALUES (1,1,1),(2,2,2), (3,3,3) go 500 SELECT c3 FROM t1 WHERE c3 = 3; --uses clust index scan SELECT c2 FROM t1 WHERE c2 = 3 --uses nonclust index seek
4 People are following this question.
