Re: ORDER BY and IDENTITY

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Questar (Questar_at_discussions.microsoft.com)
Date: 12/20/04 

Date: Mon, 20 Dec 2004 10:07:15 -0800

I'm afraid I may have jumped in inappropriately. Your post was probably
directed toward a response to the original question, while my reply to your
post was in the context of the original question.

"Kevin Munro" wrote:

> The first query (select * from t1) can't be guaranteed to come out in the
> correct order (but most probably does.)
>
> The second query (select * from t1 order by 1 desc) must come out in the
> correct order because of the order by clause?
>
> Have I misread you?
>
> The order by clause must work, is it the sequence of the identity values
> that can't be guaranteed?
>
> Thanks, Kevin.
>
>
> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message
> news:25586F01-E9D9-4916-B76A-77EA7109E3D8@microsoft.com...
> > SQL Server does guarantee the order of the result set based on the ORDER
> > BY
> > list, or in case you are not using ORDER BY clause and the table has a
> > clustered index then the sort will be based on the columns and order
> > direction used to create the clustered index. Everything else about
> > expected
> > order is an illusion.
> >
> > Example:
> >
> > use northwind
> > go
> >
> > create table t1 (colA int not null identity primary key clustered)
> >
> > insert into t1 default values
> > insert into t1 default values
> > insert into t1 default values
> > insert into t1 default values
> > insert into t1 default values
> >
> > select * from t1
> > select * from t1 order by 1 desc
> >
> > drop table t1
> >
> >
> >
> > AMB
> >
> >
> > "Questar" wrote:
> >
> >> It has come to our attention that the expected (and observed) behavior of
> >> a
> >> specific T-SQL statement is not guaranteed. You have probably
> >> encountered
> >> the issue before, but I will provide two examples here.
> >>
> >> Syntax Example 1:
> >>
> >> CREATE TABLE #T1 (T1 int, Z1 varchar(20))
> >>
> >> INSERT #T1 VALUES (1,'X')
> >> INSERT #T1 VALUES (2,'Y')
> >> INSERT #T1 VALUES (3,'Z')
> >>
> >> SELECT T1 AS T2, Z1 AS Z2, IDENTITY(int,1,1) AS I2 INTO #T2 FROM #T1
> >> ORDER
> >> BY T1 DESC
> >>
> >> DROP TABLE #T1
> >> DROP TABLE #T2
> >>
> >> Syntax Example 2:
> >>
> >> CREATE TABLE #T1 (T1 int, Z1 varchar(20))
> >> CREATE TABLE #T2 (T2 int, Z2 varchar(20), I2 int IDENTITY(1,1))
> >>
> >> INSERT #T1 VALUES (1,'X')
> >> INSERT #T1 VALUES (2,'Y')
> >> INSERT #T1 VALUES (3,'Z')
> >>
> >> INSERT #T2 (T2,Z2) SELECT T1,Z1 FROM #T1 ORDER BY T1 DESC
> >>
> >> DROP TABLE #T1
> >> DROP TABLE #T2
> >>
> >> According to a SQL Server MVP, the SQL Server 2000 execution plans for
> >> these
> >> two examples are not guaranteed to have the sort occur before the
> >> assignment
> >> of IDENTITY values. The MVP said the expected behavior was guaranteed in
> >> SQL
> >> Server 2005. These two examples produce very similar, but not identical,
> >> execution plans. Looking at the statements logically I could understand
> >> if
> >> the syntax of Example 1 resulted in the Compute Scalar (IDENTITY)
> >> operation
> >> happening before the Sort operation, but that behavior would seem very
> >> wrong
> >> for Example 2. I believe there's a significant amount of T-SQL code
> >> being
> >> used (including some of ours) that depends upon the syntax of Example 2
> >> resulting in the Sort operation happening before the Compute Scalar
> >> (IDENTITY) operation.
> >>
> >> Does anybody have any additional thoughts or information on this matter?
> >>
>
>
>

Relevant Pages

  • Re: CLUSTERD INDEXES
    ... > The clustered index is about the organization of the rows in storage. ... > The ORDER BY clause is about the organization of the rows in a result set. ... > The organization of the rows in storage does not always guarantee ... > and if they can be meet the query planner will consider taking advantage ...
    (microsoft.public.sqlserver.programming)
  • Re: CLUSTERD INDEXES
    ... The clustered index is about the organization of the rows in storage. ... The ORDER BY clause is about the organization of the rows in a result set. ... The organization of the rows in storage does not always guarantee ... the query planner, checks to see if some qualifications can be meet ...
    (microsoft.public.sqlserver.programming)
  • Re: Which row inserted first
    ... Without specifying ORDER BY clause you cannot guarantee the order ... Assuming your clustered index is an identity, ...
    (microsoft.public.sqlserver.server)
  • Re: ORDER BY and IDENTITY
    ... Just if SQL Server decide to use the clustered index to ... > The order by clause must work, is it the sequence of the identity values ... >> SQL Server does guarantee the order of the result set based on the ORDER ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL query produces result randomly!!
    ... I can't find the original question. ... order is by using an ORDER BY clause. ... > "raj" wrote: ... >> obtain userNames and passes as a parameter to a query which populates ...
    (microsoft.public.sqlserver.programming)