Primary Key - Result Set Order
-
Here's a qualification for what you are about to read. It may not make sense why I am doing what I am doing, but business requirements called for it. I had to create a temporary solution for a guy in our company to create a table that has a single-field primary key. The table he needed had two fields that comprised the primary key.
I have the following table:
CREATE TABLE dbo.My_Table(
Unique_ID varchar(20),
Unit_ID int,
Year_Month varchar(7))
Here is some sample data:
Unique_ID Unit_ID Year_Month
9935200506 9935 2005/06
9935200505 9935 2005/05
9935200504 9935 2005/04
9935200503 9935 2005/03
9935200502 9935 2005/02
9935200501 9935 2005/01
9935200412 9935 2004/12
9935200411 9935 2004/11
5145200510 5145 2005/10
5145200509 5145 2005/09
5145200508 5145 2005/08
5145200507 5145 2005/07
2387200401 2387 2004/01
2387200312 2387 2003/12
2387200308 2387 2003/08
I have built a primary key on this table with the following:
ALTER TABLE dbo.My_Table ADD
CONSTRAINT PK_My_Table PRIMARY KEY CLUSTERED
(
Unique_ID DESC
 
ON PRIMARYI repopulate this table twice a day, once at 6:00 AM and once at 12:00 PM. When I repopulate it, I truncate the table then load it using the following SELECT statement:
INSERT INTO dbo.My_Table
SELECT CONVERT(varchar(20), unit_id) + LEFT(year_month, 4) + RIGHT(year_month, 2)
FROM dbo.My_Original_Table
WHERE LEN(year_month) = 7
(I have some error handling and notifications to let me know if any year_month is not 7 long. If it is not, we need to fix it anyway.)
When I first built the primary key, it worked fine. Today I got a call from the person I built this table for and they said it was not sorted in the proper manner. (The proper way is the way I show at top.)
When I queried it I also didn't get it in the right order. If I did a SELECT TOP ... I got data in the right order.
(I used this query:
SELECT *
FROM dbo.My_Table)
(As I am typing this I seem to remember seeing something about this problem in the past but please bear with me while I continue.)
I dropped the Primary Key constraint and then added it back to the table and the data comes in the right order.
Does this have to do with the way SQL Server stores data? Even though my primary key is specified the way it is, does this not mean that my data will come to me in this order unless I specifically specify the order?
If so, is that a function of TOP, that, unless another order is specified, it will return the TOP X records in Primary Key order?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
If you want a specific sort order you MUST use order by. If you also need to use top use this syntax :
Select top 100 percent Cols from dbo.YourTable order by PK Desc
-
Yes, you are retrieving the data exactly as SQL Server has stored it physically on disk.
You can eliminate the problem by either:
a) When inserting the data, order it. So
INSERT INTO dbo.My_Table
SELECT CONVERT(varchar(20), unit_id) + LEFT(year_month, 4) + RIGHT(year_month, 2)
FROM dbo.My_Original_Table
WHERE LEN(year_month) = 7
ORDER BY Unique_ID DESC
b) Reindex the table immediately after the insert. This is a good standard practice anyway.
-
No, the 'problem' is not solved by any of those methods. As Remi said above, the only way to guarantee a specific order of the results is to use ORDER BY when fetching them.
-
One other thing that can screw this up :
2+ cpus (multiple threads reading)
or
another process already reading the same data >> the 2nd query joins the fetching of the first query and THEN goes back to the "beginning" of the table the fetch the rest.
So even assuming that you use the correct execution plan everytime and that nobody is ever allowed near the indexes of the table or the select itself, it can (will) still FAIL.
-
Thanks guys. As my post said, about half-way through, I thought this was the case but I just needed clarification.
Thanks again,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply