Sort Order and Indexes

[Aaron37]

Hello,
I'm new to Sql Server and have a question about my primary key. I've set up my primary key clustered in ascending order. When I opened the table and returned all rows, the order of the id column was 1,10,11,110 instead of 1,2,3,4,... Even when I add order by Id to the SQL statement the order didn't change. I created an unclustered index for the same column and when I opened the table the order was the way I intended, 1,2,3,4, ...
Why would the sort order change after adding the other index? I'm sure it's propbably not a good thing to have 2 indexes for the same column but it's all I've been able to come up with. Is there a better way to do it? This is probably a silly question but like I said I'm new to SQL Server. Any help is greatly appreciated.

Thanks,

A.Davis

 

[Jonax]

Is your PK an INT? It sounds like it's sorting alphabetically and not numerically!? This is not a bug - it's an undocumented feature...
;-)

 

[Aaron37]

Thanks for the reply. I set the PK as an integer. I don't understand why it would sort that way. Any ideas?

Thanks for any help,

A.Davis

 

[iowtech]

What collation method do you have set? It sounds like it could be binary.

 

[Aaron37]

How do you check that? I didn't set up the server.

Thanks for any help,

A.Davis

 

[TheBugSlayer]

Aaron37,
Check the sp_helpsort and sp_dboption stored procedure. One of the two- not too sure which one- will not render all the options unless you change one of the properties.

You can also change collation when installing SQL Srv. You would have to choose custom install and choose locales and all related features. Careful though, two DBs with different locale will not exchange data...