InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 20:46:05 GMT20http://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Grant Fritchey (10/11/2012)[/b][hr][quote][b]ScottPletcher (10/11/2012)[/b][hr][quote][b]Grant Fritchey (10/11/2012)[/b][hr][quote][b]ScottPletcher (10/11/2012)[/b][hr][quote][b]Grant Fritchey (10/11/2012)[/b]As an aside, the CHAR(2) would be a really poor choice for the first column in a compound key. You'd want to use either the INT or the NVARCHAR(20) because they will generate more efficient statistics.[/quote]Why? I wasn't aware, and don't see way, stats on a char(2) would be inherently worse than on an int. [/quote]It depends on the data. If you have a perfectly random distribution of ANSI characters, then you're looking at about 65,000 combinations, which could be reasonably well distributed for an index, depending on the number of rows it was supporting. But since an INT can hold 4,000,000,000 distinct values, the chances are, a better distribution. But you can set up anything so that it's the opposite. I was just playing off the standard uses of such numbers. An int is more likely to be a distributed value while a varchar(2) is more likely to be something like a state abbreviation. In this case, as more and more data gets added, the statistics for the varchar(2) get more and more useless as only 50 possible buckets (out of the 200 available) would be filled with hundreds and thousands of rows reflecting the same data over & over.In short, I made a generalization.[/quote]But since it's the leading column in a clustered index, it's [i]still[/i] valuable at reducing the number of rows that have to be processed.Seems to me that what you're talking about applies far more to a nonclus index.[/quote]Working off the generalization, not really. The first column is the only one with detailed statistics. The compound key adds to the overall selectivity of the index, part of how the optimizer picks it, but it's the distribution of the data within the stats that most drives the optimizer towards choosing an index. Again, distribute the data in just the right way and this point changes, but then we're discussing data distribution, not the idea of useful statistics.[/quote]I still think that applies only to nonclus indexes. The selectivity can be 95% and a clus index seek will still be used if it is applicable.Thu, 11 Oct 2012 12:40:20 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]ScottPletcher (10/11/2012)[/b][hr][quote][b]Grant Fritchey (10/11/2012)[/b][hr][quote][b]ScottPletcher (10/11/2012)[/b][hr][quote][b]Grant Fritchey (10/11/2012)[/b]As an aside, the CHAR(2) would be a really poor choice for the first column in a compound key. You'd want to use either the INT or the NVARCHAR(20) because they will generate more efficient statistics.[/quote]Why? I wasn't aware, and don't see way, stats on a char(2) would be inherently worse than on an int. [/quote]It depends on the data. If you have a perfectly random distribution of ANSI characters, then you're looking at about 65,000 combinations, which could be reasonably well distributed for an index, depending on the number of rows it was supporting. But since an INT can hold 4,000,000,000 distinct values, the chances are, a better distribution. But you can set up anything so that it's the opposite. I was just playing off the standard uses of such numbers. An int is more likely to be a distributed value while a varchar(2) is more likely to be something like a state abbreviation. In this case, as more and more data gets added, the statistics for the varchar(2) get more and more useless as only 50 possible buckets (out of the 200 available) would be filled with hundreds and thousands of rows reflecting the same data over & over.In short, I made a generalization.[/quote]But since it's the leading column in a clustered index, it's [i]still[/i] valuable at reducing the number of rows that have to be processed.Seems to me that what you're talking about applies far more to a nonclus index.[/quote]Working off the generalization, not really. The first column is the only one with detailed statistics. The compound key adds to the overall selectivity of the index, part of how the optimizer picks it, but it's the distribution of the data within the stats that most drives the optimizer towards choosing an index. Again, distribute the data in just the right way and this point changes, but then we're discussing data distribution, not the idea of useful statistics.Thu, 11 Oct 2012 12:34:14 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Grant Fritchey (10/11/2012)[/b][hr][quote][b]ScottPletcher (10/11/2012)[/b][hr][quote][b]Grant Fritchey (10/11/2012)[/b]As an aside, the CHAR(2) would be a really poor choice for the first column in a compound key. You'd want to use either the INT or the NVARCHAR(20) because they will generate more efficient statistics.[/quote]Why? I wasn't aware, and don't see way, stats on a char(2) would be inherently worse than on an int. [/quote]It depends on the data. If you have a perfectly random distribution of ANSI characters, then you're looking at about 65,000 combinations, which could be reasonably well distributed for an index, depending on the number of rows it was supporting. But since an INT can hold 4,000,000,000 distinct values, the chances are, a better distribution. But you can set up anything so that it's the opposite. I was just playing off the standard uses of such numbers. An int is more likely to be a distributed value while a varchar(2) is more likely to be something like a state abbreviation. In this case, as more and more data gets added, the statistics for the varchar(2) get more and more useless as only 50 possible buckets (out of the 200 available) would be filled with hundreds and thousands of rows reflecting the same data over & over.In short, I made a generalization.[/quote]But since it's the leading column in a clustered index, it's [i]still[/i] valuable at reducing the number of rows that have to be processed.Seems to me that what you're talking about applies far more to a nonclus index.Thu, 11 Oct 2012 12:25:40 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]ScottPletcher (10/11/2012)[/b][hr][quote][b]Grant Fritchey (10/11/2012)[/b]As an aside, the CHAR(2) would be a really poor choice for the first column in a compound key. You'd want to use either the INT or the NVARCHAR(20) because they will generate more efficient statistics.[/quote]Why? I wasn't aware, and don't see way, stats on a char(2) would be inherently worse than on an int. [/quote]It depends on the data. If you have a perfectly random distribution of ANSI characters, then you're looking at about 65,000 combinations, which could be reasonably well distributed for an index, depending on the number of rows it was supporting. But since an INT can hold 4,000,000,000 distinct values, the chances are, a better distribution. But you can set up anything so that it's the opposite. I was just playing off the standard uses of such numbers. An int is more likely to be a distributed value while a varchar(2) is more likely to be something like a state abbreviation. In this case, as more and more data gets added, the statistics for the varchar(2) get more and more useless as only 50 possible buckets (out of the 200 available) would be filled with hundreds and thousands of rows reflecting the same data over & over.In short, I made a generalization.Thu, 11 Oct 2012 12:07:04 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Grant Fritchey (10/11/2012)[/b]As an aside, the CHAR(2) would be a really poor choice for the first column in a compound key. You'd want to use either the INT or the NVARCHAR(20) because they will generate more efficient statistics.[/quote]Why? I wasn't aware, and don't see why, stats on a char(2) would be inherently worse than on an int.Thu, 11 Oct 2012 10:40:21 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxI know you want some specifics that you can apply to creating all indexes on all tables, but there really aren't any.There are some general rules for creating proper indexes, but specifics -- exactly what columns, and in what order -- will still depend on each specific table's individual requirements.Thu, 11 Oct 2012 09:11:19 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxIf you're going to search by Col1 and Col4, then, in general, put them first in the index, to narrow the row range immediately as much as possible.I didn't say to automatically exclude the nvarchar(20) column from the index. You need to re-read my comments. I said "it depends", although [i]in general[/i], yes, you would only want a 40 char column in the clus index if it was absolutely needed.I wasn't aware, and don't see way, stats on a char(2) would be inherently worse than on an int. I would think SQL would be able to create a map of value ranges for a char(2) as well as for an int.Thu, 11 Oct 2012 08:47:21 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Grant Fritchey (10/11/2012)[/b][hr]I'd be nervous about creating that many indexes, especially if you're only saving a few pages read from memory. If the query was called thousands of times a minute, heck yes, it's worth it. If it's only called a thousand times a day... probably not.[/quote]Thanks for all the help Grant :-)Just begging to read your book "SQL Server 2012 Query Performance Tuning" and hope to get more pointers..Thanks,PedroThu, 11 Oct 2012 06:37:37 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxI'd be nervous about creating that many indexes, especially if you're only saving a few pages read from memory. If the query was called thousands of times a minute, heck yes, it's worth it. If it's only called a thousand times a day... probably not.Thu, 11 Oct 2012 06:01:40 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxI've created the index [code="sql"]CREATE INDEX idxTest_03 ON idxTest (Col1, Col4, Col3, Col2)[/code] and the execution plan behavior is exactly the same as idxTest_02. But again the reads are the same as using idxTest_01.So idxTest_03 and idxTest_01 have different column orders, generate different execution plans but have the same number of reads, take the same time to execute and both are Seeks.I can almost assume the only column order that matters is the 1st one... In our app I don't know exactly how the data will be filtered so I create 4 indexes: Col1, Col2, Col3, Col4; Col2, Col1, Col3, Col4; Col3, Col1, Col2, Col4 and Col4, Col1, Col2, Col3. The only thing I know is that it can only be filtered over those 4 coluns. Or is this just over engineering!?! Just create an index and monitor possible indexes that could be created?!The index maintenance whenever a column was updated can be bad... Thanks,PedroThu, 11 Oct 2012 05:26:00 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxFirst things first, estimated costs are not measures that you can compare between queries. They are too subject to odd variability, so don't look at cost estimates within execution plans as a measure of performance. You clear the cache, but then only measure reloading it for the first query, not for the second. That's going to affect the behavior of the queries too. Instead, to get a clean measure, you need to execute them separately.On my machine, the both executed in 0 ms. The difference is in the reads. The first query did 5, the second did 2. That might be the difference between them. But, because you're forcing the index on the optimizer, it's hard to know if you're seeing the same behavior that would be evident if that query was actually adequate for the optimizer to select it naturally.You can see how the information is retrieved. First you have a seek predicate:[quote]Seek Keys[1]: Start: [Testing].[dbo].[idxTest].Col1 >= Scalar Operator((10)), End: [Testing].[dbo].[idxTest].Col1 <= Scalar Operator((200))[/quote]Then you have a Predicate:[quote][Testing].[dbo].[idxTest].[Col4]>(60)[/quote]And that's for both seek operations. But the main difference is that the second query doesn't need to go through as many pages to find the values since it's only dealing with the two columns.If you modify the scripts to test each naturally, letting the optimizer pick the results, you get basically the same behavior. The number of reads varies, and that's the sticking point. So, you've proven that the key order matters. What if you rearrange the compound key to put col4 in the second position in the first index?Thu, 11 Oct 2012 05:08:43 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxI created the following script for testing...[code="sql"]CREATE TABLE idxTest (Id INT NOT NULL IDENTITY, Col1 INT, Col2 INT, Col3 INT, Col4 INT, CONSTRAINT pk_idxTest PRIMARY KEY CLUSTERED (id))SET NOCOUNT ON;DECLARE @i INT = 1WHILE @i < 100000BEGIN INSERT INTO idxTest (Col1, Col2, Col3, Col4) VALUES (@i/2, @i/3, @i/4, @i/5) SET @i = @i +1ENDCREATE INDEX idxTest_01 ON idxTest (Col1, Col2, Col3, Col4)CREATE INDEX idxTest_02 ON idxTest (Col1, Col4)DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSET STATISTICS IO ONSET STATISTICS TIME ONPRINT '-------------------------------------'SELECT id FROM idxTest WITH(INDEX(idxTest_01)) WHERE Col1 BETWEEN 10 AND 200 AND Col4 > 60 PRINT '-------------------------------------'SELECT id FROM idxTest WITH(INDEX(idxTest_02)) WHERE Col1 BETWEEN 10 AND 200 AND Col4 > 60 PRINT '-------------------------------------'[/code]Execution plan says the 1st one takes 55% and 2nd 45%.Statistics IO on the 1st has:Table 'idxTest'. Scan count 1, logical reads 5, physical reads 3, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.The 2nd:Table 'idxTest'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Perfectly normal since the idxTest_01 is wider and more reads are needed.After executing the 2nd time without DBCC commands the physical and read-ahead reads were gone.Statistics time was almost the same, the 1st toke 1ms and the 2nd 0ms.Imagining the possible combinations of all the 4 columns, 16 indexes, is it worth the performance gain comparing to only having 4 indexes with all 4 columns? Also the number of records is never to big since we have data paging with 20 or 40 records...Thanks,PedroThu, 11 Oct 2012 04:35:45 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]PiMané (10/11/2012)[/b][hr]One more if I may Grant... I've already discussed this point but if you can enlighten me a little more I'd be grateful :-)Suppose I have an index I1 with C1, C2, C3 and C4. I want to search for C1 and C4.Execution plan says an index seek is made over I1... it seeks C1 data but then it has to get ALL C2 and C3 on the C1 and then seach C4 again... is this equivalent to making a Seek, Scan, Seek inside the same index or just a simple Seek is made, which would mean that I1 is a substitue for all the index combinations starting with C1 column for those 4 columns...Thanks,Pedro[/quote]A seek is a targeted scan anyway, so it's finding the starting point using the first column, then scanning through the values to find the rest. If you have a plan that you want to compare this on, take a look at the properties to see what it did.Thu, 11 Oct 2012 04:09:52 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxOne more if I may Grant... I've already discussed this point but if you can enlighten me a little more I'd be grateful :-)Suppose I have an index I1 with C1, C2, C3 and C4. I want to search for C1 and C4.Execution plan says an index seek is made over I1... it seeks C1 data but then it has to get ALL C2 and C3 on the C1 and then seach C4 again... is this equivalent to making a Seek, Scan, Seek inside the same index or just a simple Seek is made, which would mean that I1 is a substitue for all the index combinations starting with C1 column for those 4 columns...Thanks,PedroThu, 11 Oct 2012 04:04:30 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]PiMané (10/11/2012)[/b][hr][quote][b]ScottPletcher (10/10/2012)[/b][hr]The nvarchar(20) does make that a problematic clustering key, but it still depends.A three-column is not slower to do lookup on if that's what you (almost) always specify in the WHERE clause.But, to avoid forty extra bytes, you should consider making the clus key just ( invoiceType, invoiceNumber ), assuming that is still reasonably selective. Typically narrowing it down to a few hundred rows (typical worst case) is good enough. You can create a nonclus index with all three columns if needed.[/quote]We can have the PK with IDENTITY or GUID (in case replication or synchronization is needed across other databases) to use in FKs and use the most selective columns for CI (as long as they aren't very wide, if they are toss the biggest ones out...).Basically it's this? (in this case since there's no narrow natural key to be PK, if there was it would be PK and CI).Just one more thing I just remembered... CI doesn't have to be unique, since it doesn't have to be the PK, so if other NCI indexes have the CI data to go to the page won't that make a RID Lookup since CI isn't unique, instead of Key Lookup?Thanks,Pedro[/quote]If the clustered index does not have a unique key value (and more importantly, you don't use UNIQUE when making that index), then SQL Server will add a value internally. This is called a uniquifier and is a 4 byte int value. This makes non-unique clustered indexes a little more problematic to juggle. Hence, a natural bias is built in to make the clustered index a primary key. But it's just a bias, not a requirement or even a best practice.Thu, 11 Oct 2012 04:03:41 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]PiMané (10/10/2012)[/b][hr][quote][b]Grant Fritchey (10/10/2012)[/b][hr]On a table that is stored as a clustered index, the non-clustered indexes use the key values of the clustered index as lookup values for the columns that are not some part of the non-clustered index. If a table is stored as a heap, an artificial identifier, called a row identifier, or RID, is used to track down those additional columns.[/quote]So if we have a CI on a natural key, say for example 3 columns (invoiceType CHAR(2), invoiceNumber INT, productCode NVARCHAR(20)) all the NCI would have the CI key... those same values... big "waste" of space considering that even a GUID is smaller and faster to lookup than 3 column key, right?!Thanks,Pedro[/quote]It can be, but, again, I can't throw out the concept because I've got a slightly fat key. It really depends on the situation. And, primarily, the results of testing. As an aside, the CHAR(2) would be a really poor choice for the first column in a compound key. You'd want to use either the INT or the NVARCHAR(20) because they will generate more efficient statistics.Thu, 11 Oct 2012 03:57:29 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]ScottPletcher (10/10/2012)[/b][hr]The nvarchar(20) does make that a problematic clustering key, but it still depends.A three-column is not slower to do lookup on if that's what you (almost) always specify in the WHERE clause.But, to avoid forty extra bytes, you should consider making the clus key just ( invoiceType, invoiceNumber ), assuming that is still reasonably selective. Typically narrowing it down to a few hundred rows (typical worst case) is good enough. You can create a nonclus index with all three columns if needed.[/quote]We can have the PK with IDENTITY or GUID (in case replication or synchronization is needed across other databases) to use in FKs and use the most selective columns for CI (as long as they aren't very wide, if they are toss the biggest ones out...).Basically it's this? (in this case since there's no narrow natural key to be PK, if there was it would be PK and CI).Just one more thing I just remembered... CI doesn't have to be unique, since it doesn't have to be the PK, so if other NCI indexes have the CI data to go to the page won't that make a RID Lookup since CI isn't unique, instead of Key Lookup?Thanks,PedroThu, 11 Oct 2012 00:36:12 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxThe nvarchar(20) does make that a problematic clustering key, but it still depends.A three-column is not slower to do lookup on if that's what you (almost) always specify in the WHERE clause.But, to avoid forty extra bytes, you should consider making the clus key just ( invoiceType, invoiceNumber ), assuming that is still reasonably selective. Typically narrowing it down to a few hundred rows (typical worst case) is good enough. You can create a nonclus index with all three columns if needed.Wed, 10 Oct 2012 15:54:07 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Grant Fritchey (10/10/2012)[/b][hr]On a table that is stored as a clustered index, the non-clustered indexes use the key values of the clustered index as lookup values for the columns that are not some part of the non-clustered index. If a table is stored as a heap, an artificial identifier, called a row identifier, or RID, is used to track down those additional columns.[/quote]So if we have a CI on a natural key, say for example 3 columns (invoiceType CHAR(2), invoiceNumber INT, productCode NVARCHAR(20)) all the NCI would have the CI key... those same values... big "waste" of space considering that even a GUID is smaller and faster to lookup than 3 column key, right?!Thanks,PedroWed, 10 Oct 2012 13:59:52 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]PiMané (10/10/2012)[/b][hr]Hi,I was reading about PK and Clustered Indexes over at sqlskill.com and after reading too much I guess I burned a fuse...So pardon if this is a stupid question...The PK has to be UNIQUE and NOT NULL, the CL index doesn't necessarily have to be on the PK (example: PK a IDENTITY column for FK and joins, CL over a natural key like customerCode).If the CL columns can be updated or aren't sequential is it a good choise for CL or should we use the PK INDENTITY column for CL? [/quote]There's no hard and fast rule for this. If you're strictly talking about the physical storage and retrieval of the clustered index (CI, not CL, would be a better abbreviation, then NCI comes from non-clustered index), then yes, a monotonically increasing value makes for a more efficient clustered index. But no, I wouldn't recommend just using an identity value as the primary key or as the clustered index. It really depends on how data is going to be stored and retrieved from within the table. I'm not a purist in regards to artificial or natural keys. I tend to bend my designs around what works best in a situation (and I know that cranks up both parties, but what can I say). You can get magnificent performance out of compound key values (as an example) or datetime or even strings, if those values are the best way to store and retrieve the data and your queries are written to use them.[quote]The non CL indexes "point" to what value? The PK or the CL index?Thanks,Pedro[/quote]On a table that is stored as a clustered index, the non-clustered indexes use the key values of the clustered index as lookup values for the columns that are not some part of the non-clustered index. If a table is stored as a heap, an artificial identifier, called a row identifier, or RID, is used to track down those additional columns.Wed, 10 Oct 2012 11:31:40 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxHi,I was reading about PK and Clustered Indexes over at sqlskill.com and after reading too much I guess I burned a fuse...So pardon if this is a stupid question...The PK has to be UNIQUE and NOT NULL, the CL index doesn't necessarily have to be on the PK (example: PK a IDENTITY column for FK and joins, CL over a natural key like customerCode).If the CL columns can be updated or aren't sequential is it a good choise for CL or should we use the PK INDENTITY column for CL?The non CL indexes "point" to what value? The PK or the CL index?Thanks,PedroWed, 10 Oct 2012 10:41:50 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Eugene Elutin (10/4/2012)[/b][hr]Yes, you are right! There is no one-size-fits-all answer to that.However...I guess it was some long discussions between SQL Server (and back to old days Sybase) design engineers/architects about making PK clustered by default. They decided to do so, and not only for the reason "not to create heaps". PK is not always the best choice for clustered, but quite often it is. Use of Surrogate keys for PK is quite popular in modern RDBMS's (at least in SQL Server and Oracle), so may be that is another statistical manifestation example of 80-20 rule... [/quote]I think that in more than 20% of cases a large enough proportion of the work required of the dbms involves searches for a range of values in some initial columns of the natural key. A surrogate key may of course still be useful for reducing the storage required in other tables for references to this table (and consequently reducing the number of in those other tables pages handled by joins with this table), but while it does no harm to make the surrogate the primary key it is not always sensible in these cases to make it the cluster key - the natural key should have a unique constraint on it and non-null constraints on each of its columns (ie the table should be normalised at least to 1NF, to keep the code manipulating tha table simple - actually I generally can't see any point in normalisation to lower levels than EKNF, but only 1NF is relevant to this discussion) and often either that index (if it's narrow enough) or an index on the relevant intial part (perhaps just one column) of the natural key should be used for clustering.Every case needs careful analysis, and I don't believe there's an 80 to 20 rule in favour of clustering on a surrogate.Thu, 04 Oct 2012 13:51:18 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Eugene Elutin (10/4/2012)[/b][hr]Yes, you are right! There is no one-size-fits-all answer to that.However...I guess it was some long discussions between SQL Server (and back to old days Sybase) design engineers/architects about making PK clustered by default. They decided to do so, and not only for the reason "not to create heaps". PK is not always the best choice for clustered, but quite often it is. Use of Surrogate keys for PK is quite popular in modern RDBMS's (at least in SQL Server and Oracle), so may be that is another statistical manifestation example of 80-20 rule... [/quote]I think that in more than 20% of cases a large enough proportion of the work required of the dbms involves searches for a range of values in some initial columns of the natural key. A surrogate key may of course still be useful for reducing the storage required in other tables for references to this table (and consequently reducing the number of in those other tables pages handled by joins with this table), but while it does no harm to make the surrogate the primary key it is not at all sensible in these cases to make it the cluster key - the natural key should have a unique constraint on it and non-null constraints on each of its columns (ie the table should be normalised at least to 1NF, to keep the code manipulating tha table simple - actually I generally can't see any point in normalisation to lower levels than EKNF, but only 1NF is relevant to this discussion) and either that index (if it's narrow enough) or an index on the relevant intial part (perhaps just one column) of the natural key should be used for clustering.Thu, 04 Oct 2012 13:45:18 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Eugene Elutin (10/4/2012)[/b][hr][quote][b]ScottPletcher (10/4/2012)[/b][hr][quote][b]I don't think such extremely simplistic rules always work well in tables, large or small.Choosing the best indexes takes a lot of careful consideration of your data distribution and your query usage patterns, and there is no one-size-fits-all answer.If a table is declared with a pk (which is advisable), by default a clustered index will be built on the primary key column(s). However, this is not always the best choice. The primary key is, by definition, unique, and nonclustered indexes are very efficient at finding a single qualifying row and can still enforce the primary key's uniqueness. So save your clustered index for something that will benefit more from it.[/quote]Yes, you are right! There is no one-size-fits-all answer to that.However...I guess it was some long discussions between SQL Server (and back to old days Sybase) design engineers/architects about making PK clustered by default. They decided to do so, and not only for the reason "not to create heaps". PK is not always the best choice for clustered, but quite often it is. Use of Surrogate keys for PK is quite popular in modern RDBMS's (at least in SQL Server and Oracle), so may be that is another statistical manifestation example of 80-20 rule... [/quote]The problem is, that's nothing close to the real ratio in production. ~60+% of legacy tuning for me is just changing the dopey identity clustered index to the proper clustered index, with an easy 50+% gain in performance.A clustering key does NOT have to be unique to aid performance. That is the worst secondary myth that gets propogated by the main myth about always making the clus key an identity. It's just a thoughtless approach.Thu, 04 Oct 2012 12:50:58 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]ScottPletcher (10/4/2012)[/b][hr][quote][b]I don't think such extremely simplistic rules always work well in tables, large or small.Choosing the best indexes takes a lot of careful consideration of your data distribution and your query usage patterns, and there is no one-size-fits-all answer.If a table is declared with a pk (which is advisable), by default a clustered index will be built on the primary key column(s). However, this is not always the best choice. The primary key is, by definition, unique, and nonclustered indexes are very efficient at finding a single qualifying row and can still enforce the primary key's uniqueness. So save your clustered index for something that will benefit more from it.[/quote]Yes, you are right! There is no one-size-fits-all answer to that.However...I guess it was some long discussions between SQL Server (and back to old days Sybase) design engineers/architects about making PK clustered by default. They decided to do so, and not only for the reason "not to create heaps". PK is not always the best choice for clustered, but quite often it is. Use of Surrogate keys for PK is quite popular in modern RDBMS's (at least in SQL Server and Oracle), so may be that is another statistical manifestation example of 80-20 rule... Thu, 04 Oct 2012 09:02:32 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Jeff Moden (10/3/2012)[/b][hr][quote][b]ScottPletcher (10/3/2012)[/b][hr]Determing the appropriate column actually takes analysis; again, there is no one-rule-fits-all-tables for the best clustered key column.Customer ids are usually unique and ever-increasing; narrowness is sometimes not as good because char is used even though when the values are purely numeric. Even so, often a somewhat larger clustered key is best overall for performance.[/quote]But you didn't say Customer "ID"... you said Customer "Code". Perhaps it's a difference in terminology but I've found that the two are usually drastically different.So far as the no one-rule-fits-all-tables, I agree. That notwithstanding, the rule I stated is very good for larger tables because of the implications on non-clustered indexes. About the only real analysis needed on such tables is trying to determine if the first column of the clustered index should be some sort of date column or not. A narrow and unique secondary column, such as an IDENTITY column would be necessary or an 8 byte internal row ID would be added to the clustered index. Narrowness is always a good thing in an index simply because you can fit more rows per page on indexes and the clustered index is automatically appended to every non-clustered index that doesn't explicitly use the clustered index column(s).[/quote]I don't think such extremely simplistic rules always work well in tables, large or small.Choosing the best indexes takes a lot of careful consideration of your data distribution and your query usage patterns, and there is no one-size-fits-all answer.If a table is declared with a pk (which is advisable), by default a clustered index will be built on the primary key column(s). However, this is not always the best choice. The primary key is, by definition, unique, and nonclustered indexes are very efficient at finding a single qualifying row and can still enforce the primary key's uniqueness. So save your clustered index for something that will benefit more from it.Thu, 04 Oct 2012 08:36:09 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]L' Eomot Inversé (10/4/2012)[/b][hr][quote][b]ScottPletcher (10/3/2012)[/b][hr]Typically customer codes are sequential. Since they're not here, that would definitely make the clustering key choice much more difficult.[/quote]Typically customer codes occur in order records more often than in customer records, and while they may be sequential in order of creation of customers they are certainly not sequential in terms of creation of orders. So the ideathat a customer code is typically sequential in relation to whatever table we are talking about here is not reasonable. [/quote]just like a products table... every product has a product code which is used in searches but the inserts are never sequential and usually they are strings.. so a surrogate key would apply very well also in this case..natural keys are "beautiful" but very hard to determine what columns should be... can be customer code but also the ITIN since every person has one but usually you don't give your number before you make a purchase, when you initially register the customer...One question though...Suppose I have a Products, a Warehouses, a WarehouseProducts, Orders and OrderProducts and the OrderProducts is "linked" to WarehouseProducts, since I can make an order to my suppliers for the same product but to different warehouses (on the same order).Products has ProductCode, Warehouses has WarehouseCode, WarehouseProducts has ProductCode and WarehouseCode and OrderProducts as ProductCode and WarehouseCode.. these are all strings... Isn't it better to add surrogate keys IDENTITY to all tables and make the "link" by it? Pro: fast in joins, less columns on "linked" tables...Cons: If I want to get the product description from an order I have to use the WarehouseProducts to get to the Products table.. (one more join...). - or could simply add the ProductId along with the WarehouseProductId on the OrderProducts table but that seems "redundant"...Thanks,PedroThu, 04 Oct 2012 03:42:43 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxThis discussion is bringing us back to the similar, quite heated one, we had not long ago. Does a "Customer" have a "Natural Key"? Customer Code? May be, but to make it unique, you will need to introduce the special infrastructure which will need to generate one and ensure it is unique within organisation. So, for this organisation, you can make it "Natural", but that's about it.Now, why to bother with the cost of infrastructure to generate and maintain such unique "Customer Codes"? Just to make it look nicer? Kind of alpha-numeric?Identity, could easily play the role of "Customer Code", to make it more appearing to customer, you can format it as you wish when it's shown to them. For example, to please Chinese customers, make Customer Code/Id BIGINT and seed it from 77700000000000. Format it as 777-000-0000-0000 for better readability on output documents and it will make Asian people happy :hehe:Actually, I would not even use such name as "Customer Code" for a key. For me it means more kind of "Customer Type". Customer Number or Account Number at least, or simply Customer ID is sounds better to me.Thu, 04 Oct 2012 03:41:56 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]ScottPletcher (10/3/2012)[/b][hr]Typically customer codes are sequential. Since they're not here, that would definitely make the clustering key choice much more difficult.[/quote]Typically customer codes occur in order records more often than in customer records, and while they may be sequential in order of creation of customers they are certainly not sequential in terms of creation of orders. So the ideathat a customer code is typically sequential in relation to whatever table we are talking about here is not reasonable. Thu, 04 Oct 2012 03:04:20 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]ScottPletcher (10/3/2012)[/b][hr]Determing the appropriate column actually takes analysis; again, there is no one-rule-fits-all-tables for the best clustered key column.Customer ids are usually unique and ever-increasing; narrowness is sometimes not as good because char is used even though when the values are purely numeric. Even so, often a somewhat larger clustered key is best overall for performance.[/quote]But you didn't say Customer "ID"... you said Customer "Code". Perhaps it's a difference in terminology but I've found that the two are usually drastically different.So far as the no one-rule-fits-all-tables, I agree. That notwithstanding, the rule I stated is very good for larger tables because of the implications on non-clustered indexes. About the only real analysis needed on such tables is trying to determine if the first column of the clustered index should be some sort of date column or not. A narrow and unique secondary column, such as an IDENTITY column would be necessary or an 8 byte internal row ID would be added to the clustered index. Narrowness is always a good thing in an index simply because you can fit more rows per page on indexes and the clustered index is automatically appended to every non-clustered index that doesn't explicitly use the clustered index column(s).Wed, 03 Oct 2012 18:05:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Jeff Moden (10/3/2012)[/b][hr][quote]Use the clustered index for a more appropriate column[/quote]For large tables, the "appropriate column" is usually unique, narrow, and ever-increasing. "Customer Code" columns almost never meet all 3 of that criteria.[/quote]Determing the appropriate column actually takes analysis; again, there is no one-rule-fits-all-tables for the best clustered key column.Customer ids are usually unique and ever-increasing; narrowness is sometimes not as good because char is used even though when the values are purely numeric. Even so, often a somewhat larger clustered key is best overall for performance.Wed, 03 Oct 2012 16:57:42 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote]Use the clustered index for a more appropriate column[/quote]For large tables, the "appropriate column" is usually unique, narrow, and ever-increasing. "Customer Code" columns almost never meet all 3 of that criteria.Wed, 03 Oct 2012 16:43:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxTypically customer codes are sequential. Since they're not here, that would definitely make the clustering key choice much more difficult.If customer code is (almost) always used for lookups/processing, you still save a huge amount of overhead by not having to go thru a nonclus index, which at minimum roughly doubles the reads to get to a specific row.As I noted before, if the customer code is actually a customer number, it can also be stored as an int rather than as character. If it's alphanum, naturally that would also have to be taken into consideration.I don't object to surrogate keys in general, just the idea that [i]by default[/i] tables should be clustered by identity -- that's just laziness. There is no one-size-fits-all rules when it comes to determing the best clustering key for a given table.Wed, 03 Oct 2012 08:43:35 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]ScottPletcher (10/2/2012)[/b][hr][quote][b]Eugene Elutin (10/2/2012)[/b][hr][quote][...Is this statement true? If so is it better to use Guid or BIGINT?[/quote]It depends, but usually BIGINT is better.[quote]Another question, related to keys... If a table uses Id as PK should it be clustered if BIGINT identity? Or if, say a customers table, has a CustomerCode that's used for searches should that be the column for a clustered index (even if when inserting the codes aren't sequential generating page moves in the database files)?...[/quote]It depends, but in this case, most likely your PK should be clustered and you will create non-clustered index on CustomerCode. [/quote]Almost certainly the CustomerCode will make a vastly better clustering key. In fact, since you already have a unique code per customer, you almost certainly don't need an identity column on that table.Often customer codes are stored as character, but if they are entirely numeric, you could store them as int to save space and reduce the overhead of storage and joins.In general, a nonclustered index is perfect for identity values. Use the clustered index for a more appropriate column. Of course specific joins and other things can change this.[b]An identity column should NOT be the [u]default[/u] choice for a clustering key, period.[/b] You should always put some thought into the proper clustering key, not just base it on an overly-simplistic rule.[/quote]1. I haven't seen OP stating that CustomerCode is unique and cannot be reused. 2. Sinlge searching pattern alone doesn't mean that this key should be made to clustered index, may be but not always. If CustomerCode is of alpha-numeric nature, making this clustered will lead to higher fragmentation. 3. Until there is a good reason, identity column is a good choice for a clustered index, as it is narrow, unique, static and always increasing (which helps to avoid fragmentation)If CustomerCode is really used for searching one single customer record, the performance wise, having it as clustered or non-clustered index will not be much different.I do usually use a bit more than this when determine candidate for clustered index:1. Range searches2. Use in joins3. Use in ORDER BY and GROUP BY 4. Returning large resultsets.Also clustered index narrowness plays some role in the effectiveness of index as well.I guess there are two camps here: Natural Keys vs Surrogate Keys :hehe:Tue, 02 Oct 2012 18:49:01 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Eugene Elutin (10/2/2012)[/b][hr][quote][...Is this statement true? If so is it better to use Guid or BIGINT?[/quote]It depends, but usually BIGINT is better.[quote]Another question, related to keys... If a table uses Id as PK should it be clustered if BIGINT identity? Or if, say a customers table, has a CustomerCode that's used for searches should that be the column for a clustered index (even if when inserting the codes aren't sequential generating page moves in the database files)?...[/quote]It depends, but in this case, most likely your PK should be clustered and you will create non-clustered index on CustomerCode. [/quote]Almost certainly the CustomerCode will make a vastly better clustering key. In fact, since you already have a unique code per customer, you almost certainly don't need an identity column on that table.Often customer codes are stored as character, but if they are entirely numeric, you could store them as int to save space and reduce the overhead of storage and joins.In general, a nonclustered index is perfect for identity values. Use the clustered index for a more appropriate column. Of course specific joins and other things can change this.[b]An identity column should NOT be the [u]default[/u] choice for a clustering key, period.[/b] You should always put some thought into the proper clustering key, not just base it on an overly-simplistic rule.Tue, 02 Oct 2012 15:51:21 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxGUID vs BigInt vs Int for a surrogate primary key, or the leading edge of any index, is all about what you want the index/key to do. There is no "one is better" answer that's universally true.GUIDs have a number of advantages and disadvantages. Storage space, index fragmentation, possibly bandwidth and I/O issues (because of size), and being extremely human-unfriendly, are some of the disadvantages. Close-to-guaranteed-uniqueness, the ability to generate them in the application layer without a database round-trip, lack of real limits on the number you can generate, are some of the advantages.BigInt has one advantage over Int, in that it can store 18-pentillion values instead of 4-billion for Int. Rare that you need more than 4-billion, but if you do, BigInt will work and Int won't.Int and BigInt have advantages over GUID in terms of size, sequentiality (partially handled by NewSequentialID for GUIDs), and human-readability. They have disadvantages in terms of range, uniqueness, and resource contention in heavy-insert databases (slight but real).You need to determine what you need the column to actually do, what your expectations are on data volume in the table, and make an informed decision based on that, not just decide "BigInt is better".On the question of composite clustered indexes/keys, it's rare that you'd get a significant performance increase by adding a surrogate key just to bypass a natural key. It can happen, but the reason to use surrogate keys is usually that you can't come up with a valid natural key (people, for example), not that you need to speed up table queries. Surrogate keys, like ID or GUID, are convenient for developers, but they have very little, if anything, to do with performance of queries.Tue, 02 Oct 2012 11:59:13 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxI don't agree with the initial construct. I've seen plenty of indexes with compound keys work perfectly well for performance without the need to add another column and another index to the table in order to get performance to work. This would be especially true for two columns. You're talking about an index with a key that is 8 bytes wide. You're going to add a bigint column to the table, which is also 8 bytes wide, which saves what exactly? Adding a GUID, which is 16 bytes wide really doesn't save any space or make for a smaller index since it's twice the size.Now, if both those columns are not very selective, so that the statistics for them is weak or horribly skewed, yeah, maybe adding another column is a possible solution, but I think we're talking about edge cases.Also, be careful with GUIDs. They can lead to severe index fragmentation (which is not a big deal, at all, but is a possible issue) if you're using the GUID as a clustered index.Tue, 02 Oct 2012 10:41:30 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspxWait for JC to state that "We don't use IDENTITY, Cobol-like monster from 50's" :hehe:The only place where I would agree on legitimate use of GUID's for PK, would be systems where PK must be generated by application before inserting data to database for one or another reasons.In the rest - I would use surrogate IDENTITY PK's.Decision on clustering is a bit separate, but mostly you would want this one clustered as well.With new Oracle-like SEQUENCEs in SQL2012, use of GUID's is even less prominent, as you will be able to get PK values from SEQUENCE independent of inserting process.Tue, 02 Oct 2012 10:01:39 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][b]Eugene Elutin (10/2/2012)[/b][hr]It depends, but in this case, most likely your PK should be clustered and you will create non-clustered index on CustomerCode. [/quote]That's what I already do, on every table have an Id column BIGINT IDENTITY and the PK on that column is clustered so the data write is sequential.GUID have the NEWSEQUENTIALID() but I've read people having problems with that (not working properly) and performance issues.[url]http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x-NewId-x-NewSeque[/url].So I'll stick with what I'm doing :-)Thanks,PedroTue, 02 Oct 2012 09:35:47 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1367112-391-1.aspx[quote][...Is this statement true? If so is it better to use Guid or BIGINT?[/quote]It depends, but usually BIGINT is better.[quote]Another question, related to keys... If a table uses Id as PK should it be clustered if BIGINT identity? Or if, say a customers table, has a CustomerCode that's used for searches should that be the column for a clustered index (even if when inserting the codes aren't sequential generating page moves in the database files)?...[/quote]It depends, but in this case, most likely your PK should be clustered and you will create non-clustered index on CustomerCode. Tue, 02 Oct 2012 09:17:50 GMTEugene Elutin