Index Problem (maybe) ?

Hi,

I am having problems (I think) with the indexing of my primary keys in my tables. I am fairly new to developing with MSSQL but understand database design methods.

I am developing with ASP + MYSQL 2000 and have recently noticed that when adding new records to a table, the new items are appearing midway through the data.

Eg. the primary keys column is listed 1,2,3,5,6,4 etc

This is causing me a right ball ache when it comes to retreiving the new id of the added record.

Is this due to the indexing of the table? If I change the index property of the primary key to be a CLUSTERED index, then all is well, but surely MSSQL should have done this by default when I set up my primary key?

I upgraded the db from Access - will this have made a difference?

Thanks for any help

Chris

do you care where on the disk the new rows are placed?

CLUSTERING really only matters when you have a bazillion-row table, and you want to minimize disk seek contention (or something like that)

in almost all applications, it does not matter where the new rows are placed

note that, by definition, a relational database does not have any concept like "position" of a row

even when there is a CLUSTERING index, you are not guaranteed to place the rows in an exact sequence

the only way to guarantee the sequence of rows is to use ORDER BY in your SELECT statement