4

Please check the below table structure and records to be inserted in that table.

CREATE TABLE [tabGeneraltable](
   id int identity,
   [codGenLedger] [uniqueidentifier] NOT NULL,
   [codInvoice] [numeric](18, 0) NULL,
   [accountValue] [numeric](18, 2) NULL,
   [articleValue] [nvarchar](50) NULL,
   [codFinAccount] [int] NULL,
   [documentNbr] [nvarchar](50) NULL,
   [valueDate] [datetime] NULL,
   [insertDate] [datetime] NULL,
   CONSTRAINT [PK_tabGeneralLedger] PRIMARY KEY CLUSTERED([codGenLedger] ASC)
)


insert into [tabGeneraltable]([codGenLedger],[codInvoice], [codFinAccount],
                              [accountValue],[insertDate])
select NEWID(),1,11,232,getdate()
union all
select NEWID(),10,45,214,getdate()
union all
select NEWID(),9,425,410,getdate()
union all
select NEWID(),14,475,356,getdate()

After inserting all records, when executing a simple select statement on this table like

select * from tabGeneraltable

identity column ID is not coming in proper order or records are inserted randomly. (Check the insertDate column value if you insert all records separately.)

Why this is happening?

12

If you don't specify an order by clause your RDBMS is free to return the results as it chooses. Usually it will return the results in the order it can retrieve the records the fastest.

Since you have the column codGenLedger as the clustered key the records will be stored ordered by codGenLedger physically on disk (unless your indexes are fragmented, but they are still read in that order).

Your RDBMS will most likely do a clustered index scan in your example and return your results ordered by the clustered index.

See this sqlfiddle for a sql server example.

That is actually sorted by your uniqueidentifier, it doesn't look like it when you look at it alphabetically but it is sorted by the hex representation of the binary data.

See here for a complete explanation.

In fact, the data is sorted; it's just not sorted the way you expect. SQL Server displays globally unique identifier (GUID) data as a series of alphanumeric strings, but the uniqueidentifier data type is equivalent to the binary(16) data type. The GUID value you see is simply the hexadecimal representation of the binary value. So the data is sorted by this hex representation, not alphabetically.

2

It is sorting by the "conGenLedger" uniqueidentifier field because you created a CLUSTERED primary key on that field. A clustered index will store the data in the database alphabetically. If you want the data to be "clustered" (sorted by default) by the ID field, you need to create a CLUSTERED index on the ID field. Since a table can only have 1 clustered index, you need to create the primary key conGenLedger field as nonclustered.

You script would look like this ...

CREATE TABLE [tabGeneraltable](
   id int identity,
   [codGenLedger] [uniqueidentifier] NOT NULL,
   [codInvoice] [numeric](18, 0) NULL,
   [accountValue] [numeric](18, 2) NULL,
   [articleValue] [nvarchar](50) NULL,
   [codFinAccount] [int] NULL,
   [documentNbr] [nvarchar](50) NULL,
   [valueDate] [datetime] NULL,
   [insertDate] [datetime] NULL,
   CONSTRAINT [PK_tabGeneralLedger] PRIMARY KEY NONCLUSTERED ([codGenLedger] ASC)
)

CREATE CLUSTERED INDEX IX_tabGeneraltable_id 
    ON dbo.tabGeneraltable (id); 
GO

insert into [tabGeneraltable]([codGenLedger],[codInvoice], [codFinAccount],
                              [accountValue],[insertDate])
select NEWID(),1,11,232,getdate()
union all
select NEWID(),10,45,214,getdate()
union all
select NEWID(),9,425,410,getdate()
union all
select NEWID(),14,475,356,getdate()

select * from tabGeneraltable
  • 2
    There is no "Sorted by default". A DBMS will frequently return the values in that order but there is no guarantee. It is also very likely that you will make that assumption and in say 6 months your data will come back in a different order and your application will run into problems. – Kenneth Fisher Jun 25 '15 at 16:06
  • 2
    Understood. I meant that the data is stored physically in the order of the clustered index and when there is no order by clause, the data will frequently be returned in that order. But yes, it can not be guaranteed as the query plan can change from time to time based o a myriad of factors. – K Dawg Jun 25 '15 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.