Post reply

Combine Two Columns to create a unique key cluster

  • April 25, 2007 at 10:16 pm

    #69736

    I have to ask another key question for crying out loud..

    I have the a working understanding of indexes and clusters and non clusters.

    Example:

    I create table A and table B both are unrelated and both have a primary key cluster on an int data field. Now i create table C that has tables A and tables B primary keys that together they now create a unique cluster pk index in table C..

    How would two columns combined two create a unique index help in t-sql

    can some one give me any kind of comment on this or just point me to the correct wording so i can accurately do a Google.

    thanks

    erik

    Dam again!

  • April 26, 2007 at 11:01 am

    #702701

    CREATE UNIQUE CLUSTERED INDEX CI_TableC_Col1_Col2

    ON dbo.TableC (Col1 ASC, Col2 ASC) ...

    Look at books online - search for CREATE INDEX.

  • April 26, 2007 at 10:26 pm

    #702848

    I think after reading this about 20 times in three years i am getting an understand of real world sql now.

    I got the grips on the keys , alternate keys, composite keys and normalization. The only thing i can not seem to sink in my brain now is what sql does when a composit key is created.. I know that a composte key is made up of to columns so that they can create a unique primary key. And this makes since becuase of one of the examples that i seen was a three column table with schema like so: EmployeeID, ProjectID, Hours Worked. So EmployeeID and projectID because the composite key. I do not know why but i can not seem to figure out how sql composes this physically for index scanning.

    Does sql create a seperate table that i can not see to do it's index scanning against..

    How would this realte to Select , or inner joing or WHATEVER when in an conditional like the where clause? If where EmployeeID or where projectID =,,, but now they are composite keys..

    all help would be great!

    erik

    Dam again!

  • April 27, 2007 at 8:39 am

    #702945

    Do you understand how b-trees work?

    Look at the b-trees and SQL architecture in the following best-practices presentation:

    http://education.sqlfarms.com/ShowPost.aspx?PostID=797

    You can get it in other places on the web. This presentation is given to user groups all over the country. Basically, you need to understand how SQL server stores the tables (both logically and physically) in the database file and this will clarify and answer your question.

    I have not seen a single-page on the MS site that explains this. But you can look up explanations about B-trees anywhere online.

  • April 27, 2007 at 12:16 pm

    #703029

    And get a copy of Inside SQL Server 2005: The Storage Engine by Kalen Delaney

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply