Indexes

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 22, 2017 at 3:50 pm

  #401457

  If I have a column where the leading column is defined in an Index do I need a separate index with that column alone?

  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/

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  January 22, 2017 at 4:46 pm

  #1924278

  Welsh Corgi - Sunday, January 22, 2017 3:50 PM

  If I have a column where the leading column is defined in an Index do I need a separate index with that column alone?

  It depends.  When you say above that you "have a column where the leading column," do you mean the leading column in the table is the leading table in an index or the column is the leading column in an index?  What kind of index are you referring to?  What is it that you want to accomplish?

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 22, 2017 at 4:54 pm

  #1924279

  Ed Wagner - Sunday, January 22, 2017 4:46 PM

  Welsh Corgi - Sunday, January 22, 2017 3:50 PM

  If I have a column where the leading column is defined in an Index do I need a separate index with that column alone?

  It depends.  When you say above that you "have a column where the leading column," do you mean the leading column in the table is the leading table in an index or the column is the leading column in an index?  What kind of index are you referring to?  What is it that you want to accomplish?

  I mean the column is the leading column in the index which is a non-clustered index.

  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/

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  January 22, 2017 at 5:05 pm

  #1924280

  Welsh Corgi - Sunday, January 22, 2017 4:54 PM

  Ed Wagner - Sunday, January 22, 2017 4:46 PM

  Welsh Corgi - Sunday, January 22, 2017 3:50 PM

  If I have a column where the leading column is defined in an Index do I need a separate index with that column alone?

  It depends.  When you say above that you "have a column where the leading column," do you mean the leading column in the table is the leading table in an index or the column is the leading column in an index?  What kind of index are you referring to?  What is it that you want to accomplish?

  I mean the column is the leading column in the index which is a non-clustered index.

  Thanks.

  Okay.  What is it that you want to accomplish?

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 22, 2017 at 5:08 pm

  #1924281

  Ed Wagner - Sunday, January 22, 2017 5:05 PM

  Welsh Corgi - Sunday, January 22, 2017 4:54 PM

  Ed Wagner - Sunday, January 22, 2017 4:46 PM

  Welsh Corgi - Sunday, January 22, 2017 3:50 PM

  If I have a column where the leading column is defined in an Index do I need a separate index with that column alone?

  It depends.  When you say above that you "have a column where the leading column," do you mean the leading column in the table is the leading table in an index or the column is the leading column in an index?  What kind of index are you referring to?  What is it that you want to accomplish?

  I mean the column is the leading column in the index which is a non-clustered index.

  Thanks.

  Okay.  What is it that you want to accomplish?

  I want to eliminate unneeded indexes.

  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/

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  January 22, 2017 at 5:25 pm

  #1924282

  Welsh Corgi - Sunday, January 22, 2017 5:08 PM

  Ed Wagner - Sunday, January 22, 2017 5:05 PM

  Welsh Corgi - Sunday, January 22, 2017 4:54 PM

  Ed Wagner - Sunday, January 22, 2017 4:46 PM

  Welsh Corgi - Sunday, January 22, 2017 3:50 PM

  If I have a column where the leading column is defined in an Index do I need a separate index with that column alone?

  It depends.  When you say above that you "have a column where the leading column," do you mean the leading column in the table is the leading table in an index or the column is the leading column in an index?  What kind of index are you referring to?  What is it that you want to accomplish?

  I mean the column is the leading column in the index which is a non-clustered index.

  Thanks.

  Okay.  What is it that you want to accomplish?

  I want to eliminate unneeded indexes.

  But you're asking about adding another one, not dropping one.

  If you have an NCI where your column is the leading one, then it can be used in an index seek when queried in the correct way.  Now, if the NCI is wide and your queries don't need the extra columns in your NCI, then the query may be able to benefit from an NCI on that column alone.  If, on the other hand, your NCI isn't very wide or your queries also use the other columns it contains, then it won't help to create a new NCI.  It all depends on your queries and how they use the indexes.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 22, 2017 at 5:31 pm

  #1924283

  Ed Wagner - Sunday, January 22, 2017 5:25 PM

  Welsh Corgi - Sunday, January 22, 2017 5:08 PM

  Ed Wagner - Sunday, January 22, 2017 5:05 PM

  Welsh Corgi - Sunday, January 22, 2017 4:54 PM

  Ed Wagner - Sunday, January 22, 2017 4:46 PM

  Welsh Corgi - Sunday, January 22, 2017 3:50 PM

  If I have a column where the leading column is defined in an Index do I need a separate index with that column alone?

  It depends.  When you say above that you "have a column where the leading column," do you mean the leading column in the table is the leading table in an index or the column is the leading column in an index?  What kind of index are you referring to?  What is it that you want to accomplish?

  I mean the column is the leading column in the index which is a non-clustered index.

  Thanks.

  Okay.  What is it that you want to accomplish?

  I want to eliminate unneeded indexes.

  But you're asking about adding another one, not dropping one.

  If you have an NCI where your column is the leading one, then it can be used in an index seek when queried in the correct way.  Now, if the NCI is wide and your queries don't need the extra columns in your NCI, then the query may be able to benefit from an NCI on that column alone.  If, on the other hand, your NCI isn't very wide or your queries also use the other columns it contains, then it won't help to create a new NCI.  It all depends on your queries and how they use the indexes.

  No I'm looking at dropping indexes.

  There are 33 indexes on one of the tables. lol

  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/

• BrainDonor

  SSCoach

  Points: 19254

  More actions

  January 23, 2017 at 2:48 am

  #1924338

  I believe what you're asking is - should the leading column on a wide non-clustered index have it's own index, so you can seek on that column alone?
  It's easy enough to test:

  CREATE TABLE IndexTest(
  ID INT IDENTITY(1,1),
  COLA VARCHAR(5),
  COLB VARCHAR(5),
  COLC VARCHAR(5)
  );

  ALTER TABLE [dbo].[IndexTest] ADD CONSTRAINT [PK_IndexTest] PRIMARY KEY CLUSTERED
  (
  [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO

  CREATE NONCLUSTERED INDEX [NC_Indextest] ON [dbo].[IndexTest]
  (
  [COLA], [COLB],[COLC]
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO
  [/code]
  
  Having added 20,00 rows of test data with Redgate Data Generator:

  SELECT TOP 1000 [ID]
    ,[COLA]
    ,[COLB]
    ,[COLC]
  FROM [TESTDB_INST1].[dbo].[IndexTest]
  WHERE cola = 'a' [/code]
  
  The execution plan shows a non-clustered Index Seek against the table, so it doesn't need the other columns of the index specified:

  
  So you don't need an additional index just for COLA.

  Steve Hall
  Linkedin
  Blog Site

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 23, 2017 at 2:58 am

  #1924340

  BrainDonor - Monday, January 23, 2017 2:48 AM

  I believe what you're asking is - should the leading column on a wide non-clustered index have it's own index, so you can seek on that column alone?
  It's easy enough to test:

  CREATE TABLE IndexTest(
  ID INT IDENTITY(1,1),
  COLA VARCHAR(5),
  COLB VARCHAR(5),
  COLC VARCHAR(5)
  );

  ALTER TABLE [dbo].[IndexTest] ADD CONSTRAINT [PK_IndexTest] PRIMARY KEY CLUSTERED
  (
  [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO

  CREATE NONCLUSTERED INDEX [NC_Indextest] ON [dbo].[IndexTest]
  (
  [COLA], [COLB],[COLC]
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO
  [/code]
  
  Having added 20,00 rows of test data with Redgate Data Generator:

  SELECT TOP 1000 [ID]
    ,[COLA]
    ,[COLB]
    ,[COLC]
  FROM [TESTDB_INST1].[dbo].[IndexTest]
  WHERE cola = 'a' [/code]
  
  The execution plan shows a non-clustered Index Seek against the table, so it doesn't need the other columns of the index specified:

  
  So you don't need an additional index just for COLA.

  Steve,

  So are you telling me if a have for example an index CallID as a single column index and I have another Index that starts off with CallID and several other columns I do not need the index with CallID as the single column Index?

  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/

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 23, 2017 at 3:20 am

  #1924345

  Welsh Corgi - Monday, January 23, 2017 2:58 AM

  Steve,

  So are you telling me if a have for example an index CallID as a single column index and I have another Index that starts off with CallID and several other columns I do not need the index with CallID as the single column Index?

  Thanks.

  Only you can answer that, because only you have access to your database.  Have you looked in sys.dm_db_index_usage_stats?

  John

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 23, 2017 at 3:24 am

  #1924346

  Welsh Corgi - Monday, January 23, 2017 2:58 AM

  So are you telling me if a have for example an index CallID as a single column index and I have another Index that starts off with CallID and several other columns I do not need the index with CallID as the single column Index?

  In general, yes. The narrower index is redundant.

  John Mitchell-245523 - Monday, January 23, 2017 3:20 AM

  Have you looked in sys.dm_db_index_usage_stats?

  Index usage stats will usually show both indexes being used, because the optimiser will happily use them both (also happens when there are multiple identical indexes)

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 23, 2017 at 3:33 am

  #1924348

  GilaMonster - Monday, January 23, 2017 3:24 AM

  Welsh Corgi - Monday, January 23, 2017 2:58 AM

  So are you telling me if a have for example an index CallID as a single column index and I have another Index that starts off with CallID and several other columns I do not need the index with CallID as the single column Index?

  In general, yes. The narrower index is redundant.

  John Mitchell-245523 - Monday, January 23, 2017 3:20 AM

  Have you looked in sys.dm_db_index_usage_stats?

  Index usage stats will usually show both indexes being used, because the optimiser will happily use them both (also happens when there are multiple identical indexes)

  Yes, precisely.  What I didn't mention is that once we've established that it's being used, we then have to decide whether the marginal gain from having the narrower index is worth the cost of maintaining it.  If the wider index is much wider, then it's possible the narrower index provides a significant performance advantage, or maybe a performance advantage on an important query.  These are all questions whose answers can only be known from an understanding of the data, usage patterns and business requirements.

  John