November 30, 2015 at 2:11 pm
If you have on Index with Column A and another Index with Column A and Column B can't you just have the Index with Both Columns as long as Column A precedes column B of the composite index?
With respect to Include if an Index use the same Colum(s) in the Index but you but a different combination in the Includes part do you need two indexes or will one index with the columns suffice?
You knowledge would be greatly appreciated.
Thanks. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 30, 2015 at 2:17 pm
Welsh Corgi (11/30/2015)
If you have on Index with Column A and another Index with Column A and Column B can't you just have the Index with Both Columns as long as Column A precedes column B of the composite index?With respect to Include if an Index use the same Colum(s) in the Index but you but a different combination in the Includes part do you need two indexes or will one index with the columns suffice?
You knowledge would be greatly appreciated.
Thanks. 🙂
Confused by your words. How about posting a couple example indexes for each question (there is more than one question being asked) that help illustrate what you are asking?
November 30, 2015 at 2:28 pm
Welsh Corgi (11/30/2015)
If you have on Index with Column A and another Index with Column A and Column B can't you just have the Index with Both Columns as long as Column A precedes column B of the composite index?
Yes
With respect to Include if an Index use the same Colum(s) in the Index but you but a different combination in the Includes part do you need two indexes or will one index with the columns suffice?
Probably one with the combined set in the include.
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
November 30, 2015 at 2:52 pm
GilaMonster (11/30/2015)
Welsh Corgi (11/30/2015)
If you have on Index with Column A and another Index with Column A and Column B can't you just have the Index with Both Columns as long as Column A precedes column B of the composite index?Yes
With respect to Include if an Index use the same Colum(s) in the Index but you but a different combination in the Includes part do you need two indexes or will one index with the columns suffice?
Probably one with the combined set in the include.
Thank you very much.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2015 at 1:37 am
GilaMonster (11/30/2015)
Welsh Corgi (11/30/2015)
If you have on Index with Column A and another Index with Column A and Column B can't you just have the Index with Both Columns as long as Column A precedes column B of the composite index?Yes
The vast majority of the time. There may be instances where you're seeing scans that the smaller index would be more useful.
With respect to Include if an Index use the same Colum(s) in the Index but you but a different combination in the Includes part do you need two indexes or will one index with the columns suffice?
Probably one with the combined set in the include.
Agreed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2015 at 1:49 am
Grant Fritchey (12/1/2015)
GilaMonster (11/30/2015)
Welsh Corgi (11/30/2015)
If you have on Index with Column A and another Index with Column A and Column B can't you just have the Index with Both Columns as long as Column A precedes column B of the composite index?Yes
The vast majority of the time. There may be instances where you're seeing scans that the smaller index would be more useful.
True. More common when dealing with the clustered and nonclustered on the same column. I suspect a scan over a 1 or 2 column index would be near identical unless dealing with large column and huge table (billions of rows)
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
December 1, 2015 at 2:23 am
GilaMonster (12/1/2015)
Grant Fritchey (12/1/2015)
GilaMonster (11/30/2015)
Welsh Corgi (11/30/2015)
If you have on Index with Column A and another Index with Column A and Column B can't you just have the Index with Both Columns as long as Column A precedes column B of the composite index?Yes
The vast majority of the time. There may be instances where you're seeing scans that the smaller index would be more useful.
True. More common when dealing with the clustered and nonclustered on the same column. I suspect a scan over a 1 or 2 column index would be near identical unless dealing with large column and huge table (billions of rows)
No arguments. This is an edge case to a degree. Couldn't help mentioning it though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply