Indexing question
-
Say I have this TSQL table
create table Table (
TableId int identity(1, 1) not null,
value nvarchar(max) null,
id0 int null,
id1 int null,
id2 int null
)and say I make a query like (getting the biggest
id2for a givenid0andid1)declare @arg0 int = 2
declare @arg1 int = 3select top 1 id2
from Table
where id0 = @arg0 and id1 = @arg1
order by id2 descWhat would be the better index
Index1, orIndex2?create index Index1 on Table (id2) include (id0, id1)
create index index2 on Table (id0, id1, id2)A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
Say I have this TSQL table
create table Table (
TableId int identity(1, 1) not null,
value nvarchar(max) null,
id0 int null,
id1 int null,
id2 int null
)and say I make a query like (getting the biggest
id2for a givenid0andid1)declare @arg0 int = 2
declare @arg1 int = 3select top 1 id2
from Table
where id0 = @arg0 and id1 = @arg1
order by id2 descWhat would be the better index
Index1, orIndex2?create index Index1 on Table (id2) include (id0, id1)
create index index2 on Table (id0, id1, id2)A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
Index3. Didn't actually try it, just based it on documentation.
Super Lloyd wrote:
create index index2 on Table (id0, id1, id2)
Creates a composite index, based on those three columns, sorted first by id0, and within that group on id1, and within that group on id2.
Super Lloyd wrote:
create index Index1 on Table (id2) include (id0, id1)
Creates a index on id2, but adds the (values of) columns id0 and id1 to the index for quick access.
Super Lloyd wrote:
What would be the better index
Index1, orIndex2?You mean "faster"? Do you mean faster read, or faster write? From what I see, they'd both need to write three fields when writing, since the index needs be updated. They'd both supply the three fields from the index (without accessing the table) when reading. The bigger difference is in the first field of the index, id2 in one, id0 in the other. Both queries seem to cover the query, but the docs point to making a composite of all three fields;
Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.
Which field comes first in that list, is also on MSDN;
List the columns to be included i
-
Index3. Didn't actually try it, just based it on documentation.
Super Lloyd wrote:
create index index2 on Table (id0, id1, id2)
Creates a composite index, based on those three columns, sorted first by id0, and within that group on id1, and within that group on id2.
Super Lloyd wrote:
create index Index1 on Table (id2) include (id0, id1)
Creates a index on id2, but adds the (values of) columns id0 and id1 to the index for quick access.
Super Lloyd wrote:
What would be the better index
Index1, orIndex2?You mean "faster"? Do you mean faster read, or faster write? From what I see, they'd both need to write three fields when writing, since the index needs be updated. They'd both supply the three fields from the index (without accessing the table) when reading. The bigger difference is in the first field of the index, id2 in one, id0 in the other. Both queries seem to cover the query, but the docs point to making a composite of all three fields;
Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.
Which field comes first in that list, is also on MSDN;
List the columns to be included i
Mm.. you seem to recommend
Index2so I am confused by your "Index3" on the first line... basically this is a table that aggregate a summary from multiple data sources.id0andid1identify the datasource and are small number (between 1 to 9 each) andid2is the original record id in the foreign datasource. I am interested to speed up read query... But.. mmm... I realize this particular query is not too important.. and querying byid2will be used elsewhere... mmmmm..... Thanks for your feedback and MSDN quotes though! :)A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
Say I have this TSQL table
create table Table (
TableId int identity(1, 1) not null,
value nvarchar(max) null,
id0 int null,
id1 int null,
id2 int null
)and say I make a query like (getting the biggest
id2for a givenid0andid1)declare @arg0 int = 2
declare @arg1 int = 3select top 1 id2
from Table
where id0 = @arg0 and id1 = @arg1
order by id2 descWhat would be the better index
Index1, orIndex2?create index Index1 on Table (id2) include (id0, id1)
create index index2 on Table (id0, id1, id2)A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
Index2 is the faster one because index1 will not be used at all except maybe for a full scan. You might want to create it as;
create index index2 on Table (id0, id1, id2 DESC)
But the difference is academical at best. SQL Server can scan in reverse direction after all.
Wrong is evil and must be defeated. - Jeff Ello
-
Say I have this TSQL table
create table Table (
TableId int identity(1, 1) not null,
value nvarchar(max) null,
id0 int null,
id1 int null,
id2 int null
)and say I make a query like (getting the biggest
id2for a givenid0andid1)declare @arg0 int = 2
declare @arg1 int = 3select top 1 id2
from Table
where id0 = @arg0 and id1 = @arg1
order by id2 descWhat would be the better index
Index1, orIndex2?create index Index1 on Table (id2) include (id0, id1)
create index index2 on Table (id0, id1, id2)A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
For that query, definitely index 2. Think of it like searching a massive address book. You're trying to find the highest house number for anyone called "John Smith".
- Index 1 sorts the addresses by house number, and includes the first and last name. You have to start at the end and scan backwards until you find an entry for "John Smith".
- Index 2 sorts the addresses by first name, then last name, then house number. You can jump straight to the end of the "John Smith" records, and see the highest house number.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Mm.. you seem to recommend
Index2so I am confused by your "Index3" on the first line... basically this is a table that aggregate a summary from multiple data sources.id0andid1identify the datasource and are small number (between 1 to 9 each) andid2is the original record id in the foreign datasource. I am interested to speed up read query... But.. mmm... I realize this particular query is not too important.. and querying byid2will be used elsewhere... mmmmm..... Thanks for your feedback and MSDN quotes though! :)A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
Super Lloyd wrote:
Mm.. you seem to recommend
Index2so I am confused by your "Index3" on the first line...Same as Index2, but with another order of the fields.
Super Lloyd wrote:
basically this is a table that aggregate a summary from multiple data sources.
id0andid1identify the datasource and are small number (between 1 to 9 each) andid2is the original record id in the foreign datasource.That makes those three fields a compound primary key.
Super Lloyd wrote:
Thanks for your feedback and MSDN quotes though! :)
My pleasure, and seems they know most about Sql Server :thumbsup:
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
