indexes for compound primary keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

indexes for compound primary keys

Author

Topic

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-09 : 12:30:55

Greetings all,
I have a table in my database,it's primary key contains tree columns,now I want when user executes the queries like SELECT,INSERT,UPDATE,...it could be sorted according to one of these tree columns not all of them
now,is it a good idea to change the primary key's index?
Any help appreciated.

---------------------------------------
Mehdi.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 12:59:24

You would need to add a clustered index on that one column. You don't have to change the primary key.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-09 : 13:18:18

quote:
Originally posted by Mehdi
I have a table in my database,it's primary key contains tree columns,now I want when user executes the queries like SELECT,INSERT,UPDATE,...it could be sorted according to one of these tree columns not all of them
now,is it a good idea to change the primary key's index?
Any help appreciated.

put an index on the column you want ordered, and use the ORDER BY clause in your select statment.

Why are you concerned with INSERTS and UPDATES?

-ec

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-09 : 13:19:21

I didnt want to change the primary key(I wanted to change it's index)
If I make a new clustered index for that column,then what is the role of primary key's index?
with lots of thanks.

---------------------------------------
Mehdi.

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-09 : 13:29:01

let me tell my problem,
I am developing a Library managemant system,I have here tree tables for Books,Users,LibrarayManager....
in my LibrarayManager table I have tree primary columns(UserID,BookID,DateBookLent) now I want when the manager uses my system and sends the queries(SELECT,INSERT,UPDATE) all the time mytable is sorted according to DateBookLent column.I dont want to use ORDER BY for sorting my table outside the datasource.

---------------------------------------
Mehdi.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 13:32:47

Then just put a clustered index on DateBookLent. Your primary key index would be non-clustered as you are only allowed one clustered index per table.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-09 : 13:34:41

quote:
Originally posted by Mehdi
I am developing a Library managemant system,I have here tree tables for Books,Users,LibrarayManager....
in my LibrarayManager table I have tree primary columns(UserID,BookID,DateBookLent) now I want when the manager uses my system and sends the queries(SELECT,INSERT,UPDATE) all the time mytable is sorted according to DateBookLent column.I dont want to use ORDER BY for sorting my table outside the datasource.

You cannot guarantee the order of a resultset unless you use the ORDER BY clause. Sometimes you will get a resultset ordered by an index, but you should not count on that.

the order of INSERTS and UPDATES is unimportant. If we are missing something here, fill us in a little more.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 13:40:21

What does this mean?:

quote:

I dont want to use ORDER BY for sorting my table outside the datasource.

Tara

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-09 : 14:31:12

quote:

You cannot guarantee the order of a resultset unless you use the ORDER BY clause

quote:

What does this mean?:
I dont want to use ORDER BY for sorting my table outside the datasource.

I want to guarantee the orders of a my rows in my table not in the resultset of the queries,
i.e when the manager adds(INSERT) a new row to mytable(LibrarayManager table),it should be located in proper location according to it's DateBookLent field in my table in datasource(not in resultsets of queries).

---------------------------------------
Mehdi.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-09 : 14:38:35

quote:
Originally posted by Mehdi
I want to guarantee the orders of a my rows in my table not in the resultset of the queries,
i.e when the manager adds(INSERT) a new row to mytable(LibrarayManager table),it should be located in proper location according to it's DateBookLent field in my table in datasource(not in resultsets of queries).

you can't.

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 14:42:26

quote:

you can't.

I'm confused. That's what a clustered index does.

Tara

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2004-08-09 : 14:51:17

>>I'm confused.That's what a clustered index does
This is my answer.(sorry for confusion)
with thanks to Tara and eyechart.
Hope you again help me next time.

---------------------------------------
Mehdi.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-09 : 15:29:08

quote:
Originally posted by tduggan
I'm confused. That's what a clustered index does.

Doesn't that only happen if you cluster on a unique incrmementing column, like an identity field?

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-09 : 16:05:13

No. A clustered index orders the table according to the column(s) in the index. So let's take an example. Table1 with columns Column1 and Column2. Column1 is an identity column and is the primary key and we specify non-clustered. Column2 is varchar(50). We place a clustered index on Column2. The table is physically ordered by Column2. So if you did this:

SELECT *
FROM Table1

you would get the data ordered by Column2. It would be the same as:

SELECT *
FROM Table1
ORDER BY Column2

But to be safe, I always specify ORDER BY if I want the results ordered.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources