clustered index and nonclustered index

I have a table with 2 indexes:
1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
2 - nonclustered, unique, unique key located on PRIMARY
the second index it's over three columns (all int).
If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
hope than a "select * from table" returns the following:
1, 3, 7, 7
2, 3, 5, 6
but instead i get the following:
2, 3, 5, 6
1, 3, 7, 7
why this happen ? should'nt the PK clustered index order the results by the
first column? how can i make that results can be like the first case?
thanks in advance"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
>
The table's physical design does not guarantee any logical ordering to
results. The only way to get ordered results is to use an ORDER BY clause
in the query.
David|||With SQL, there is NO guarantee about the order of data retrieval UNLESS you
explicitly request the order by using the ORDER BY statement.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||I have found that doing a reindex seems to order the rows correctly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||byteman,
As mentioned by others, the only way to guarantee any sorting is to use
the ORDER BY clause.
If the four columns you describe are the entire table, then the
optimizer could use the clustered index to satisfy the query, or use
index 2. Both cover the query. Index 1 covers the query because the leaf
level includes all table column. Index 2 covers the query because the
clustered index key is always included in a nonclustered index.
HTH,
Gert-Jan
byteman wrote:
> I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by th
e
> first column? how can i make that results can be like the first case?
> thanks in advance