Non-keyed columns in indexes in SQL 2005
I am confused about all the hype about including non-keyed columns in indexes in SQL Server 2005. I clearly understand the advantage of including a column in an index to avoid needing a bookmark lookup to retrieve the data. But I could do that in SQL 2000 - just add the column as a keyed column on the index. So what is the difference with having the column not part of the key? Non-keyed indexes make WHERE clauses slower if I have to do a table scan when I could otherwise do an index scan. If that is the only difference, then why would anyone ever want to use a non-keyed column in an index?
According to books online:
"They can be data types not allowed as index key columns."
"They are not considered by the Database Engine when calculating the number of index key columns or index key size."
"While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level""Computed columns that are deterministic and either precise or imprecise can be included columns"
In my 7 years of SQL Server, I have never run into a situation where I wanted an index on a column and could not add it because of the data type. I am not even sure which types are allowed (I know text and image aren't - but they aren't allowed in the new non-keyed columns feature either). I suppose there might be some special situation needing this.
The total index key size is 900 bytes. I have run into situations where this was stopped me from using an index I wanted, but it is rare, and never caused much of a problem. 900 bytes is usually more than enough.
Most of the size of the index is the leaf nodes, so this won't save significant disk space. It probably can make a small but significant performance difference doing index scans/seeks.
Including computed columns might save someone from denormalizing for speed, which saves maintenance, but again, I don't see this happening often enough to be signficant.
So, in summary, If a having a column as part of an index key clearly isn't useful for WHERE clauses, then by default it should be included as a non-key column. And there are some other situations where this is useful. But this is a small optimization, not a revelution.
According to books online:
"They can be data types not allowed as index key columns."
"They are not considered by the Database Engine when calculating the number of index key columns or index key size."
"While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level""Computed columns that are deterministic and either precise or imprecise can be included columns"
In my 7 years of SQL Server, I have never run into a situation where I wanted an index on a column and could not add it because of the data type. I am not even sure which types are allowed (I know text and image aren't - but they aren't allowed in the new non-keyed columns feature either). I suppose there might be some special situation needing this.
The total index key size is 900 bytes. I have run into situations where this was stopped me from using an index I wanted, but it is rare, and never caused much of a problem. 900 bytes is usually more than enough.
Most of the size of the index is the leaf nodes, so this won't save significant disk space. It probably can make a small but significant performance difference doing index scans/seeks.
Including computed columns might save someone from denormalizing for speed, which saves maintenance, but again, I don't see this happening often enough to be signficant.
So, in summary, If a having a column as part of an index key clearly isn't useful for WHERE clauses, then by default it should be included as a non-key column. And there are some other situations where this is useful. But this is a small optimization, not a revelution.
posted by Brad at 8:17 AM
0 Comments:
Post a Comment
<< Home