Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Indexes question.... Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 8:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 6:38 AM
Points: 513, Visits: 1,124
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...
Post #1389233
Posted Tuesday, November 27, 2012 8:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 41,570, Visits: 34,495
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 2008, MVP
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

Post #1389250
Posted Tuesday, November 27, 2012 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 06, 2012 2:49 PM
Points: 140, Visits: 310
Check the below link

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI406
Post #1389257
Posted Tuesday, November 27, 2012 8:51 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 6:38 AM
Points: 513, Visits: 1,124
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...
Post #1389259
Posted Tuesday, November 27, 2012 9:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 6:38 AM
Points: 513, Visits: 1,124
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...
Post #1389293
Posted Wednesday, November 28, 2012 4:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 6:38 AM
Points: 513, Visits: 1,124
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...
Post #1389635
Posted Wednesday, November 28, 2012 6:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:55 PM
Points: 41,570, Visits: 34,495
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 2008, MVP
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

Post #1389723
Posted Wednesday, November 28, 2012 6:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 6:38 AM
Points: 513, Visits: 1,124
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...
Post #1389740
Posted Wednesday, November 28, 2012 8:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, April 20, 2014 6:38 AM
Points: 513, Visits: 1,124
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...
Post #1389824
Posted Wednesday, November 28, 2012 9:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 2,820, Visits: 3,917
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
Post #1389892
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse