jwcolby
jwcolby at colbyconsulting.com
Fri Jun 26 11:47:50 CDT 2009
I think it would be helpful to copy the SQL Server list on this discussion since they are the ones
who know SQL Server. You are discussing this on the Access list where none of your arguments
applies (at least to pure Access).
John W. Colby
www.ColbyConsulting.com
Shamil Salakhetdinov wrote:
> Hi Gustav,
>
> Yes, SQL Server table can have no more than one clustered index (see links
> below).
> And "if the table has a clustered index, or the index is on an indexed view,
> the row locator is the clustered index key for the row."
> The latter means (AFAIU feel free to correct me if you suppose I'm wrong)
> that if we introduce surrogate key additionally to clustered index created
> on natural (primary key) columns and if we also create an index on surrogate
> key column (to e.g. speed-up joins execution one may suppose) then clustered
> index will anyway be involved in sql selection operations to lookup actual
> data rows - and that will result in joins of large data tables using
> surrogate keys would run (considerably) slower than joins on tables without
> surrogate keys. And not even joins: if you have a surrogate
> Autonumber/Identity clustered primary key/index on a table (that's what we
> usually have most of the time currenlty?) plus a natural (compound) (unique)
> key/index then using where expression on the columns from the natural
> key/index will involve two indexes on sql execution time, and if result set
> is large then execution time will be bad...
>
> Well, the above doesn't yet sound clear for me therefore I'd propose to have
> this discussion kept here for some time to find all "pros" and contra" for
> this or that approach. As e.g. it's done here on "the GUIDs as primary key"
> subject:
>
> "Is it a bad idea to use GUIDs as primary keys in MS SQL?"
> http://stackoverflow.com/questions/537145/is-it-a-bad-idea-to-use-guids-as-p
> rimary-keys-in-ms-sql
>
> <<<<<<<<<<<<<<<<<<<<
> GUID Pros
> * Unique across every table, every database, every server
> * Allows easy merging of records from different databases
> * Allows easy distribution of databases across multiple servers
> * You can generate IDs anywhere, instead of having to roundtrip to the
> database
> * Most replication scenarios require GUID columns anyway
>
> GUID Cons
>
> * It is a whopping 4 times larger than the traditional 4-byte index value;
> this can have serious performance and storage implications if you're not
> careful
> * Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
> * The generated GUIDs should be partially sequential for best performance...
>
> I mean I expect this thread will finally come to similar "Pros" and "Cons"
> list - in fact that would be two lists:
>
> 1)
> Natural Primary Keys with Clustered Index and No Surrogate Keys - Pros
>
> Natural Primary Keys with Clustered Index and No Surrogate Keys - Contra
>
> 2)
> Surrogate Primary Keys with Clustered index - Pros
>
> Surrogate Primary Keys with Clustered index - Contra
>
> As usual the "truth should be somewhere in between" - what I wanted to
> achieve is to stop using surrogate keys almost "religiously" in all and
> every table as I do now - I think I can stop doing that now if I will use
> ADO.NET Entity Framework as my main data access/manipulation technology
> because ADO.NET Entity Framework will take care for me to "write/generate
> lengthy join expressions on-the-fly (under the hood)" as well as it (ADO.NET
> Entity Framework) will let me to not rewrite my join expressions in the case
> of the data model changes/reorganization.
>
> Thank you.
>
> --
> Shamil
>
> P.S. Here are some links related to the subject of this thread:
>
> ---
> The ADO.NET Entity Framework Overview
> http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
>
> ---
> Table and Index Organization
> http://msdn.microsoft.com/en-us/library/ms189051.aspx
>
> Tables and Index Data Structures Architecture
> http://msdn.microsoft.com/en-us/library/ms180978.aspx
>
> Logical and Physical Operators Reference
> http://msdn.microsoft.com/en-us/library/ms191158.aspx
>
> --
> Clustered Index Structures
> http://msdn.microsoft.com/en-us/library/ms177443.aspx
>
> "If the table has a clustered index, or the index is on an indexed view, the
> row locator is the clustered index key for the row. "
>
>
> Nonclustered Index Structures
> http://msdn.microsoft.com/en-us/library/ms177484.aspx
>
> Heap Structures
> http://msdn.microsoft.com/en-us/library/ms188270.aspx
>
> SQL Statement Processing
> http://msdn.microsoft.com/en-us/library/ms190623.aspx
>
> ---
> Is it a bad idea to use GUIDs as primary keys in MS SQL?
> http://stackoverflow.com/questions/537145/is-it-a-bad-idea-to-use-guids-as-p
> rimary-keys-in-ms-sql
> ---
> Should a Composite Primary Key be clustered in SQL Server?
> http://stackoverflow.com/questions/389348/should-a-composite-primary-key-be-
> clustered-in-sql-server
>
> What happens when I drop a clustered primary key in SQL 2005
> http://stackoverflow.com/questions/705504/what-happens-when-i-drop-a-cluster
> ed-primary-key-in-sql-2005
>
> Where to place a primary key
> http://stackoverflow.com/questions/575450/where-to-place-a-primary-key
>
> What is the optimal indexing strategy for a relation table?
> http://stackoverflow.com/questions/851908/what-is-the-optimal-indexing-strat
> egy-for-a-relation-table
> ----
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Friday, June 26, 2009 12:40 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Clustered Index Debate - Was: RE: Primary keys and
> entry points
>
> Hi Shamil and John
>
> Reading these chatty fellows is a bit difficult as they refer to quite a lot
> before and outside the article and many versions of SQL Server including
> 6.5, pack it in "if"s and different scenarios, and - as I'm not a DBA -
> expressions unknown by me, but to me it looks more like a discussion on
> indexes rather than keys with zero conclusion.
>
> However - if that is true - if only one clustered index can be applied to a
> table then, of course, it has to be designed with great care considering the
> expected purpose and usage of the table and, of course, that may very well
> include a natural (compound) key. But I can't see why that should indicate a
> farewell to the surrogate key.
>
> How the ADO.NET Entity Framework handles this is really out of our hands but
> I guess the old rule still applies: the groundwork sets the quality of the
> house.
>
> /gustav
>
>
> <<< snip >>>
>