July 13, 2007 at 2:12 pm
I've been using an identity column on a sorted table for a while and just recently heard this concept from someone I work with. Can anyone explain where/why/when the sort WON'T work properly, how could I tell if my optimization choices are setup to confound my process?
The dataset I am using has 15 million records and I am sorting on a 5 column combination to resequence. (See below) I create the table, insert the records sorted, and then multiply the identity value x 1 in the next step to make it static so I can perform calculations on it later.
CREATE TABLE [dbo].[TableA] (
[Field1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field2] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field3] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Field4] [int] NULL ,
[Field5] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AUTOGEN] [int] IDENTITY (0, 1) NOT FOR REPLICATION NOT NULL,
[AUTOGENVALU] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TableA (Field1, Field2, Field3, Field4, Field5)
SELECT DISTINCT Field1, Field2, Field3, Field4, Field5
FROM TableB
WHERE Field3 in ('AB', 'CD') and Field1 <> 'UNKNOWN' and Field1 like '2009%'
ORDER BY Field1 asc, Field3 desc, Field4 asc, Field5 desc, Field2 desc
GO
UPDATE TableA
SET AUTOGENVALU = (AUTOGEN*1)
GO
July 13, 2007 at 2:58 pm
Remove the concept of 'sorted table' from your thinking. Inserting data into a table in a particular order does not guarantee the data will come back in the same order.
The only way to guarantee order of retrieved data is with an 'order by' clause in the query, otherwise the DBMS has the option of giving you back your results in the order the optimzer believes is most efficient.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply