Combine Two Columns to create a unique key cluster
-
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!
-
CREATE UNIQUE CLUSTERED INDEX CI_TableC_Col1_Col2
ON dbo.TableC (Col1 ASC, Col2 ASC) ...
Look at books online - search for CREATE INDEX.
-
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!
-
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.
-
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 RooseveltAuthor 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