September 29, 2011 at 2:28 am
September 29, 2011 at 4:51 am
Yes. You're right. The cluster key is stored as part of non-clustered indexes anyway.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 29, 2011 at 5:07 am
September 29, 2011 at 5:11 am
MadTester (9/29/2011)
My guess is yes, and the RID column adds size to index2 since I guess it is stored twice, once as an explicit column, and once as part of the pointer to the record on the table.
Depends if that index is defined as unique or not. The clustering key is added at the leaf level for a unique index and at all levels for a non-unique.
The clustering key is most definitely not stored twice. SQL is not that stupid. It needs the clustering key in the index. If you explicitly put it there, it doesn't need to be added a second time
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
September 29, 2011 at 5:13 am
MadTester (9/29/2011)
great, then I can log a defect against all those indexes which explicitly include a clustered column as part of the non clustered index.
No!!!
Don't depend on SQL's behaviour. If you need the column in the index, put it in the index. If you don't need it in the index, leave it out.
If you depend on SQL's behaviour when you really do need that column in the index and someone changes the clustered index or adds another column to the nonclustered index then the index definition changes and performance changes. Don't intentionally write code/design indexes susceptible to unintended side effects.
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
September 29, 2011 at 5:56 am
Ooops. Misread that question. Gail is right. It's not stored twice. I just meant it's stored anyway... sorry about that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 29, 2011 at 8:07 am
Thanks guys.
I did a test and checked the index size when only [col2] and also when index = [col2,RID].
They are almost the same size where RID is clustered. difference was 8KB where rid and col2 are both int and number of rows = 223000.
Which now means that even though they won't be true duplicates by their definition, these 2 are still duplicated indexes.
I am guessing that where index1 = [col2] and index2 = [col2,RID] I should then remove index1 since index 2 will still behave the same way when RID is changed to nonclustered.
September 29, 2011 at 8:21 am
Do you have any queries that need RID in the index definition? Do you have any queries that filter on both, join on both, aggregate on both? If not, remove the second because it's not necessary.
Indexes should only contain the columns that they need to and no more. If you do need the RID column, have it there. If you don't need it, take it out. The decision should be based on the queries that use the 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
September 30, 2011 at 2:48 am
Hello all,
in an OLTP system, when you create an index to show data on the screen in an ordered fashion, you will need an unique index to properly scroll up and down. That's my main reason to dislike non-unique indexes and think about adding the primary key to them.
September 30, 2011 at 3:57 am
Now I executed a small test against a table:
CREATE TABLE [dbo].[TASKS](
[TaskCode] [int] NOT NULL,
[TypeCode] [smallint] NOT NULL,
[Description] [varchar](max) NULL,
CONSTRAINT [TASKS_PK] PRIMARY KEY CLUSTERED
(
[TaskCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ixType] ON [dbo].[TASKS]
(
[TypeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
That's a simplified version of my table. There are two indexes:
- TaskCode, the primary key.
- TypeCode, a non-unique index.
Now I run this instruction,
SELECT TaskCode, TypeCode FROM Tasks
and this SELECT is solved against the non-unique index. So my conclusion is that this index contains the primary key, even when it's not explicitly defined.
I tested another situation; I created a non-unique index on the Types table over the field [Procedure] and executed this Join:
SELECT TaskCode, TypeCode FROM Tasks TA
LEFT JOIN Types TI ON TI.TypeCode = TA.TypeCode
WHERE Procedure = 'OG49'
the plan shows that this new non-unique index contains the column [TypeCode].
These tests convinced me that a non-unique index contains the primary key - or maybe the cluster key -, I need more tests to distinguish if the case is the first or the second.
September 30, 2011 at 7:24 am
frfernan (9/30/2011)
in an OLTP system, when you create an index to show data on the screen in an ordered fashion, you will need an unique index to properly scroll up and down. That's my main reason to dislike non-unique indexes and think about adding the primary key to them.
Errr, indexes have nothing to do with order, they're for searching. There's no requirement to have a unique index to facilitate paging, I could do a paging solution with no indexes at all.
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
September 30, 2011 at 7:27 am
frfernan (9/30/2011)
So my conclusion is that this index contains the primary key, even when it's not explicitly defined.
Not the primary key. Nothing to do with the primary key. The clustered index key is present in all nonclustered indexes (as had been stated earlier in this thread).
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
September 30, 2011 at 8:22 am
Hello,
GilaMonster (9/30/2011)
frfernan (9/30/2011)
in an OLTP system, when you create an index to show data on the screen in an ordered fashion, you will need an unique index to properly scroll up and down. That's my main reason to dislike non-unique indexes and think about adding the primary key to them.Errr, indexes have nothing to do with order, they're for searching. There's no requirement to have a unique index to facilitate paging
Thanks Gail for your corrections. But I don't understand what are you stating here. I will try to be more concrete, imagine this scenario:
- Your app must show in the client terminal some data list, e.g. the list of his phone calls. And he wants to receive this info sorted by called number.
- So you code one screen, and that screen retrieve phone calls ordered by called number; you show only 20 rows.
- Your client, sure, want to see more than 20 rows, and you code something to page up/down. You must identify the first/last rows shown, and to do this you need an unique index; suppose that the calling date is this unique key.
In this case I will order data by called number and calling date, and I will create an unique index using these two columns. I use this index for order and for facilitate paging, as opposite to your comment "There's no requirement to have a unique index to facilitate paging", so please clarify me what are you saying.
I could do a paging solution with no indexes at all.
Me too, and with empty tables it will run fast 😀
Regards and good weekend,
Francesc
September 30, 2011 at 8:33 am
frfernan (9/30/2011)
In this case I will order data by called number and calling date, and I will create an unique index using these two columns. I use this index for order and for facilitate paging, as opposite to your comment
Indexes aren't for ordering. If you need an order, you use an Order By, not an index. Indexes are for seeks, for finding data. They can sometimes help speed up an order by, but that's all.
"There's no requirement to have a unique index to facilitate paging", so please clarify me what are you saying.
Exactly what I said. There is no need at all for any form of index to do paging. It may be helped with an index (performance-wise), you don't need one though.
Here's some options (you'll note, no defined indexes) http://qa.sqlservercentral.com/articles/t-sql/66030/
Here's 3 more options, 2 use a unique index (in this case same column as the primary key), one does not. http://qa.sqlservercentral.com/articles/paging/69892/
I could do a paging solution with no indexes at all.
Me too, and with empty tables it will run fast 😀
I'm wondering whether to be insulted by that.....
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
October 3, 2011 at 3:14 am
Hello,
Exactly what I said. There is no need at all for any form of index to do paging. It may be helped with an index (performance-wise), you don't need one though.
Yes, but... strictly speaking I don't need any index, only some uniqueness constraints for database design and my app would still work, the next task will be designing indexes for improve performance, that's the indexes meaning, right?. That's what I don't understand from your assertion, I accept that indexes aren't strictly needed - apps can work without indexes - but I need define some of them for an application practical use. In the example I posted the index I suggested is my first guess for a performance improvement.
Here's some options (you'll note, no defined indexes) http://qa.sqlservercentral.com/articles/t-sql/66030/
Here's 3 more options, 2 use a unique index (in this case same column as the primary key), one does not. http://qa.sqlservercentral.com/articles/paging/69892/
Thanks for these links. They use ROW_NUMBER to do paging, this technique is new for me.
I'm wondering whether to be insulted by that.....
Not at all, insulting you is absolutely far of my aim. Anyway I apologize if you consider my comment is irrespective.
Regards,
Francesc
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply