January 5, 2021 at 11:22 pm
I work on sql server 2012 I face issue when i select top 10 from table parts
it different part id when select top 10 * from same table
what I do as below :
select top 10 * from parts.nop_part
select top 10 partid from parts.nop_part
result first statement
PartID PartNumber PartNumberNon CompanyID
10 THS6002CDWP THS6002CDWP 1005070
12 THS6002IDWP THS6002IDWP 1005070
13 THS6002IDWPR THS6002IDWPR 1005070
14 TLV6004IPWR TLV6004IPWR 1005070
15 TLV6002IDGKR TLV6002IDGKR 1005070
16 TLV6002IDGKT TLV6002IDGKT 1005070
17 TLV2369IDGKR TLV2369IDGKR 1005070
18 TLV6002IDR TLV6002IDR 1005070
19 TLV2369IDGKT TLV2369IDGKT 1005070
20 TLV2369IDR TLV2369IDR 1005070
result of second statement :
partid
15
16
18
92692
10
12
13
1323975
18573944
18575053
why part id changed from first statement to second statement
although this is top 10
this is my question
January 6, 2021 at 2:19 am
Because order is not guaranteed without using an ORDER BY clause? This is SQL 101!
January 6, 2021 at 2:27 pm
Just reinforcing the correct answer. A TOP query without an ORDER by doesn't guarantee any particular order. Further, if you look at the execution plans for the two queries, you may find that they are pulling the data from different sources. It's possible you have an index on the PartID column that can be used by the second query. Whereas, the first query must go to the clustered index, or heap, to pull the data. Information in two different indexes, with two different key values, is certainly going to result in different orders to the data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply