January 14, 2010 at 12:14 pm
Will a nonclustered index creation help if a column has only one value. That is, this column has the value 1 only and the rowcount of this table is around half a million.
M&M
January 14, 2010 at 12:23 pm
Depends on how the data is being retrieved from the table. Check the execution plan of any of your queries and see if it makes any difference by adding an index on a test database.
In general, avoiding book mark lookups by using covering indexes will be good for query performance, that means it is good to index this column.
HTH...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 14, 2010 at 12:26 pm
From BOL on Column Considerations for non-clustered indexes:
Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.
If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient.
January 14, 2010 at 12:29 pm
This column is used in a where condition.
select * from tablea where columna = '1'
But I am not sure how the optimizer will use this non-clustered index on columna if the value of this column is always 1. Would it really matter?
M&M
January 14, 2010 at 12:29 pm
If a column has only one value, then there's no use in filtering by that column, hence what's the point of indexing? Even so, a single-column index on a column with one value is as close to useless as you can get. If it's part of a composite index, maybe, but it shouldn't be the leading column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply