July 1, 2011 at 8:21 am
The question I have is this: Is there any reason to have INDEX_2 in the following situation?
Table and Primary Key:
CREATE TABLE [dbo].[Table_1](
[Col_1] [int] IDENTITY(1,1) NOT NULL,
[Col_2] [datetime] NOT NULL,
[Col_3] [datetime] NOT NULL,
[Col_4] [int] NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[Col_1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Index_2:
CREATE NONCLUSTERED INDEX [Index_2] ON [dbo].[Table_1]
(
[Col_1] ASC,
[Col_4] ASC
)WITH (IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF) ON [PRIMARY]
My assumption: Col_1 has to be unique, so Index_2 (with Col_1 as the first column in the index) has to be unique also, even though it is defined as non-unique. Any lookup using Index_2 could be found using PK_Table_1 also.
July 1, 2011 at 9:53 am
The classic answer: "It depends" 🙂
INDEX_2 might be chosen by a query since it's more narrow than the original table. But since there are only two more columns (8 byte each) I would most probably not add this index since the cost of updating the index might not be worth the benefit.
For a table with much more columns including large VARCHAR() columns the decision might differ if there are just a few inserts/updates and a lot of selects using only those two columns.
July 1, 2011 at 10:02 am
Agreed with Lutz. It depends. He gave good reasons. I would look at execution plans involving the table and indexes in question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 1, 2011 at 10:40 am
Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 1, 2011 at 10:43 am
Syed Jahanzaib Bin hassan (7/1/2011)
Dont use Clustered index column in the NonClustered index this is overhead
No it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not.
and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2
How are these two indexes the same?
Nonclustered on Col1, Col4
Nonclustered on Col2
??
If you meant Col4, the two indexes still would not be the same as the leading column would be different.
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
July 1, 2011 at 10:45 am
Syed Jahanzaib Bin hassan (7/1/2011)
Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2
I don't agree with that. These are not the same. It depends on the query.
And it looks like Gail has also posted while I was posting. She explains it quite well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 1, 2011 at 10:46 am
Syed Jahanzaib Bin hassan (7/1/2011)
Dont use Clustered index column in the NonClustered index this is overhead and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2
That is what I was thinking, but I don't know enough of the internals to know if there is an advantage of the nonclustered index in this case. The execution plan shows that the nonclustered index is selected in the query I'm checking. If I disable the nonclustered index, then the clustered one is used. A profiler trace shows the same I/O and CPU in either case.
Chris
July 1, 2011 at 10:48 am
GilaMonster (7/1/2011)
Syed Jahanzaib Bin hassan (7/1/2011)
Dont use Clustered index column in the NonClustered index this is overheadNo it's not. SQL includes the clustering key in all nonclustered indexes anyway whether you specify it or not.
and leaf level of this nonclustered index will be same as your index when you will create the single column index on col2
How are these two indexes the same?
Nonclustered on Col1, Col4
Nonclustered on Col2
??
If you meant Col4, the two indexes still would not be the same as the leading column would be different.
Clustered is Col_1
NonClustered is Clo_1, Col_4
July 1, 2011 at 10:50 am
In this particular case there are very few advantages to having that exact index, seeing as the table is so small. Swapping the columns around (col4, col1) however could be exceeding useful, depending on the queries that run against the table.
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
July 1, 2011 at 10:53 am
GilaMonster (7/1/2011)
In this particular case there are very few advantages to having that exact index, seeing as the table is so small. Swapping the columns around (col4, col1) however could be exceeding useful, depending on the queries that run against the table.
Can you think of any performance DISADVANTAGES from dropping the nonclustered (Col_1, Col_4) index?
July 1, 2011 at 10:57 am
croberts 36762 (7/1/2011)
GilaMonster (7/1/2011)
In this particular case there are very few advantages to having that exact index, seeing as the table is so small. Swapping the columns around (col4, col1) however could be exceeding useful, depending on the queries that run against the table.Can you think of any performance DISADVANTAGES from dropping the nonclustered (Col_1, Col_4) index?
Yes - if an index hint is in use - you may break code. You should evaluate code to determine if you should drop it first. Maybe somebody created that index due to it creating a better execution plan and improving performance. Not likely but possible. I would evaluate your queries involved in that table first before deciding to drop it. And of course, do that in a dev environment first.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 1, 2011 at 10:58 am
No. Not with a table this narrow. If I was doing indexing work, that one would be on my list to drop without serious concern.
Just do a check (query sys.sql_modules) and make sure that the index name doesn't appear in any stored procs (as index hints)
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
July 1, 2011 at 10:58 am
The main advantage I see to the non-clustered index in this case, is that it will have more rows per page than the clustered index (since it's narrower), and if it's got everything you need for a query (covering index), then you're using less I/O, and less RAM, because of less pages.
This will give the same I/O stats, in terms of logical and physical reads, if I'm not mistaken, but it will take less bandwidth on your I/O channel, et al. So it would be more efficient in that case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 11:00 am
GSquared (7/1/2011)
The main advantage I see to the non-clustered index in this case, is that it will have more rows per page than the clustered index (since it's narrower), and if it's got everything you need for a query (covering index), then you're using less I/O, and less RAM, because of less pages.
If the table was wider I'd agree with you, but 4 byte row (+ header & other bits) vs 12 byte row (+ header & other bits), not likely to make that much difference (unless we're talking 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
July 1, 2011 at 11:01 am
GilaMonster (7/1/2011)
No. Not with a table this narrow. If I was doing indexing work, that one would be on my list to drop without serious concern.Just do a check (query sys.sql_modules) and make sure that the index name doesn't appear in any stored procs (as index hints)
Assuming the table is truly this narrow and that this is not an overly simplified example. I would hesitate little as well. But as Gail and I said - still perform the due diligence.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply