order of key columns

order of key columns

Post by Andy Mey » Wed, 25 Jun 2003 19:19:15



Hi all,

I have a table with this two columns:

PlaceID    VarChar(10)
ArticleID  VarChar(10)

Are there guidelines in which order a primary key/index should be
created, in other words: which one is faster?

PlaceID, ArticleID
or
ArticleID, PlaceID

There are ten different PlaceIDs and approximate 10.000 different
ArticleIDs

Perhaps one of you can answer this question?
Thanks in advance,
Andy

 
 
 

order of key columns

Post by Mark » Wed, 25 Jun 2003 20:25:28



Quote:> Hi all,

> I have a table with this two columns:

> PlaceID    VarChar(10)
> ArticleID  VarChar(10)

> Are there guidelines in which order a primary key/index should be
> created, in other words: which one is faster?

> PlaceID, ArticleID
> or
> ArticleID, PlaceID

> There are ten different PlaceIDs and approximate 10.000 different
> ArticleIDs

> Perhaps one of you can answer this question?
> Thanks in advance,
> Andy

In theory, it would be faster to have the column with the highest
cardinality first (ArticleID). However, if both columns are always specified
in every WHERE clause that accesses the table, then it won't make any
difference.

If only one of these two columns is specified in a WHERE clause of any SQL
statement against the table, then that column should be first.

 
 
 

1. Selecting the first 6 columns of a table by column order/ column id

Hello,

I would like a generic way of selecting the first 6 columns of many
tables.  How would you do that?  I know syscolumns has all the column
names and column ID's and table ID's

It cannot be by name since I am doing this with n number of table with
m number of columns.  I want to list the first 6 (if there are that
many) of each table.

Thanks,
Joel

2. Error 624: Could not retrieve row from page by RID

3. Columns created in alphabetical order, not defined order

4. Scripting: (Database Role)

5. ORDER BY column names vs column numbers

6. Problems using BeginTransaction

7. INSERT INTO TABLE KEYED ONLY ON IDENTITY COLUMN RESULTS IN KEY VIOLATION (6.5)

8. IA-Cedar Rapids-73189--Year 2000-Client/Server-Relational Database-ORACLE-SYBASE

9. Foreign key references to non-primary key columns

10. Composite Primary key or 1 primary key column with all values

11. DB Design: identity column as primary key and join key