On which column to create index if no where clause is present ?
-
Hi Experts,
On which column should one create index if no WHERE clause is specified.
example
Select * from table_nameNo identity column is present on the table(because none is needed 🙂 ). I search this on major forum but i guess i may have got my keywords incorrect.
-
None, there's no index useful for that query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/18/2013)
None, there's no index useful for that query.I may be wrong over here but if none index is created on the table it gets stored on heap and retrival is slow. Hence i think one index should be created it can be on a random column.
-
I disagree with you completely.
Every table should have a clustered index, but certainly not on 'a random column'. The column has to be carefully chosen. That's irrelevant to this question though, there is no index useful for the query you posted, therefore it's useless to create any index for this particular query.
Oh, and heap means no clustered index, not no indexes at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Shadab Shah (11/18/2013)
GilaMonster (11/18/2013)
None, there's no index useful for that query.I may be wrong over here but if none index is created on the table it gets stored on heap and retrival is slow. Hence i think one index should be created it can be on a random column.
With or without index, you're doing a full table scan, so indexes are worthless.
Maybe - and just maybe - you might benefit from a (clustered) columnstore index - if you're using SQL Server 2012 or 2014 - because those indexes compress the data really efficient and you might get less IO.
But this depends on a lot of factors, such as for example the number of unique values in your columns. It might even not help at all.
If you were not using * - shame on you - but just selecting a few columns, the columnstore index would give you some real benefit, as the reduced number of columns has a great impact on IO.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (11/18/2013)
Maybe - and just maybe - you might benefit from a (clustered) columnstore index - if you're using SQL Server 2012 or 2014 - because those indexes compress the data really efficient and you might get less IO.and whether the table is read only or not (SQL 2012).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/18/2013)
Koen Verbeeck (11/18/2013)
Maybe - and just maybe - you might benefit from a (clustered) columnstore index - if you're using SQL Server 2012 or 2014 - because those indexes compress the data really efficient and you might get less IO.and whether the table is read only or not (SQL 2012).
Indeed. A pretty important limitation in SQL 2012.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Shadab Shah (11/18/2013)
Hi Experts,On which column should one create index if no WHERE clause is specified.
example
Select * from table_nameNo identity column is present on the table(because none is needed 🙂 ). I search this on major forum but i guess i may have got my keywords incorrect.
Your example query is asking for ALL the columns and ALL the rows in your table. The only possible way to satisfy this query is to read ALL the pages. An index would not help you here.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply