Indexes

Question

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/

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 · Answer 5

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 · Answer 6

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: 19252 More actions · Answer 7

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 · Answer 8

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 · Answer 9

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 · Answer 10

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 · Answer 11

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