Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes question....


Indexes question....

Author
Message
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
Hi,

If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?
But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!
Is it worth having both indexes?
The queries can have any column on the search criteria since the App UI allows it.

Thanks,
Pedro



If you need to work better, try working less...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47413 Visits: 44402
PiMané (11/27/2012)
Hi,

If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?


Yes.

But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!
Is it worth having both indexes?


Maybe.

http://www.sqlservercentral.com/articles/Indexing/68636/


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


dba_pkashyap
dba_pkashyap
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 317
Check the below link

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI406
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
In a first approach is it "wise" to just create one of the indexes (Col1, Col2, Col3) and not both (Col1, Col3, Col2) and after analyzing the workload for a few days/weeks see what SQL Server engine suggests?
Start from a simple approach and not the "big picture" since we can be creating indexes that won't be used?
If we have a 4 columns searchable table we can have loads of indexes or just create 4 (start with one different column and add the others by any order)...

Thanks,
Pedro



If you need to work better, try working less...
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
Just one more thing...
Is Col1, Col2, Col3 equal to having Col1, Col2 ?
Some tables have indexes like this an the wider indexes probably make the other obsolete..

Thanks,
Pedro



If you need to work better, try working less...
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
GilaMonster (11/27/2012)
PiMané (11/27/2012)
Hi,

If a table as an index with Col1, Col2, Col3 and includes Col5, Col4, Col7 (by this order) this index is obsolete if we have another index with Col1, Col2, Col3 and includes Col4, Col5, Col6, Col7, right?


Yes.

But what about an index with Col1, Col2, Col3 compared with Col1, Col3, Col2 ?!
Is it worth having both indexes?


Maybe.

http://www.sqlservercentral.com/articles/Indexing/68636/

Hi,

After reading your blog about indexes http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/ I made a query to get "duplicate" indexes considering that the first two columns are important and the other can be "joined", for example an index col1, col2, col3 and col1, col2, col5 can be "joined" in a col1, col2, col3, col5... I'm creating wider indexes and removing small ones.
I'm still not considering the include columns but joining two "duplicate" indexes include columns is the next step..

DECLARE @FirstColumnsKeys INT = 2
;WITH IndexInfo AS (
SELECT
SCHEMA_NAME(t.schema_id) SchemaName,
t.name TableName,
i.name IndexName,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id <= @FirstColumnsKeys ORDER BY ic.index_column_id FOR XML PATH('')) FirstKeyColumns,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 0 AND ic.index_column_id > @FirstColumnsKeys ORDER BY ic.column_id FOR XML PATH('')) OtherKeyColumns,
(SELECT ' ' + CAST(ic.column_id AS VARCHAR(10)) + ' ' FROM sys.index_columns ic WHERE i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.is_included_column = 1 ORDER BY ic.column_id FOR XML PATH('')) IncludeColumns
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id AND t.is_ms_shipped = 0
WHERE i.type != 0)
SELECT i1.* FROM
IndexInfo i1 INNER JOIN IndexInfo i2 ON i1.SchemaName = i2.SchemaName AND i1.TableName = i2.TableName AND i1.IndexName <> i2.IndexName AND i1.FirstKeyColumns = i2.FirstKeyColumns
ORDER BY i1.SchemaName, i1.TableName, i1.FirstKeyColumns, i1.OtherKeyColumns, i1.IndexName


Still working on the query but this is as it is so far...
Is this assumption, that the first two columns are what really matters and the other can be joined, valid for foreign keys without indexes? If I have a FK on col2, col3 and col4 can an index with col2, col3, col5, col4 be considered "good" for FK validation?

Thanks,
Pedro



If you need to work better, try working less...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47413 Visits: 44402
Pedantic nitpick. The ; is a statement terminator. It does not begin statements. ;WITH is an abomination that I wish would go away.

PiMané (11/28/2012)

DECLARE @FirstColumnsKeys INT = 2;
WITH IndexInfo AS (



Is this assumption, that the first two columns are what really matters and the other can be joined, valid for foreign keys without indexes?


Maybe. It depends how unique the combinations are (and that goes in general, not for foreign keys).


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


PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
GilaMonster (11/28/2012)
Pedantic nitpick. The ; is a statement terminator. It does not begin statements. ;WITH is an abomination that I wish would go away.

Agree... sorry :-)

Maybe. It depends how unique the combinations are (and that goes in general, not for foreign keys).

Do the indexes on FKs have to have the exact same columns by the same order to have impact?

Thanks,
Pedro



If you need to work better, try working less...
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
About FKs and indexes...
If a FK has Col1, Col2, Col3 by this order is a Col1, Col2 index any good?
It's probably better than nothing but should a Col1, Col2, Col3 index exist or a Col1, Col2, Col3, Col4, .... A Col1, Col2, Col4, Col3 probably is the same as just Col1, Col2 for a FK validation...

Thanks,
Pedro



If you need to work better, try working less...
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
PiMané (11/28/2012)
About FKs and indexes...
If a FK has Col1, Col2, Col3 by this order is a Col1, Col2 index any good?
yes but always (not there, where col3 is involve)


PiMané (11/28/2012)
About FKs and indexes...
It's probably better than nothing but should a Col1, Col2, Col3 index exist or a Col1, Col2, Col3, Col4, .... A Col1, Col2, Col4, Col3 probably is the same as just Col1, Col2 for a FK validation...
no .. i will suggest you to read article referred/written by Gail

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search