Sql Server Indexing With Two or More Columns
in [Server Configuration]
|
From: mark on 10 Dec 2009 02:16 I got a question with indexing. If I create an index and select 2 or more columns, what is the difference with that and creating 2 (or more separate ) indexes for them? Thanks --- http://msgroups.net/microsoft.public.sqlserver.server/
From: Uri Dimant on 10 Dec 2009 03:10 mark It depends on what you are doing If you have WHERE Last=(a)p1 AND First=(a)p2 there no need to have two indexes , however having WHERE First=(a)p2 the first index on Last,First may or may not be useful, so in that case having two NCI may be a good idea I mean you need testing it, and make a decision.Also there is no need to create NCI on every column, it is especially true in SQL Server 2005 and onwards where you have INCLUDE clause to cover ther SELECTs "mark" <user(a)msgroups.net/> wrote in message news:O93vDjWeKHA.4880(a)TK2MSFTNGP05.phx.gbl... >I got a question with indexing. If I create an index and select 2 or more >columns, what is the difference with that and creating 2 (or more >separate ) indexes for them? > > Thanks > > --- > http://msgroups.net/microsoft.public.sqlserver.server/
From: Dan Guzman on 10 Dec 2009 08:09 To add on to what Uri said, the composite index will be most efficient when both columns are specified in the WHERE/JOIN clause. However, when only one column is specified, the first key column needs to be specified in order to perform a seek; the composite index can't be used efficiently with only the second key column. The composite index is the best choice when queries include both key columns. With separate indexes, more data than needed will be touched when both columns are specified. The advantage, though, is that you'll will have a potentially useful index when either column is specified. So if your queries often specify only one of the columns, the separate indexes are probably the better choice. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "mark" <user(a)msgroups.net/> wrote in message news:O93vDjWeKHA.4880(a)TK2MSFTNGP05.phx.gbl... > I got a question with indexing. If I create an index and select 2 or more > columns, what is the difference with that and creating 2 (or more > separate ) indexes for them? > > Thanks > > --- > http://msgroups.net/microsoft.public.sqlserver.server/
From: mark on 10 Dec 2009 08:41 Thanks for your replies, So I take it that if both columns are specified in the where, the composite index is faster(maybe not by much) than the separate indexes? And if only the first column is specified in the where, the separate indexes is faster than the composite? --- frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.server/Sql-Server-Indexing-With-Two-or-More-Columns
From: Jay on 10 Dec 2009 10:35
The Query Optimizer will choose the best index for a query, not the best group of indexes. So, if you need to filter by two columns, then put both in the index. To get performance from an index, you want to get an index seek and the Query Optimizer can only do a seek for columns that head the index, or are listed in order (ColA, ColB, etc). > So I take it that if both columns are specified in the where, the > composite index is faster(maybe not by much) than the separate indexes? > Yes, though by how much depends on the size of the table, the bigger the table is, the better this will perform. > And if only the first column is specified in the where, the separate > indexes is faster than the composite? > Assuming the same data, then this would return more rows and therefore would take longer. The problem is that you are seeking to a larger dataset. Oh, and the rules change a bit if you have a clustered index. But now I'm causing trouble :) |