Wednesday, January 15, 2014

Don't Depend on SQL Sever Sorting

Resonantly i have found a bug in production system. Where i have to uploaded data to temporary data base and import that data to another data  structure.
Data import part will happen in pre-define time schedule but not in same time. In my case data will be imported by using order of data upload . I have read data from data base without ordering the data. It work for some time more than one year. But suddenly system has generated errors due to some reason. I have done some digging. In addition i have found out that data has imported but not in intended order.
I have put order by clause in to select statement.That has resolved the my production issue. Unfortunately this error was not able to generate in local environment.
Therefore its always good to retrieve data with order by clause

Sample Code to test this scenario

 USE tempdb;
 -- Create Table

 CREATE TABLE dbo.TempData
 (ID INT IDENTITY(1,1) PRIMARY KEY
  ,Col1 INT)
 -- Insert Random data
  DECLARE @Count INT =0

  WHILE @Count < 1000
  BEGIN
 INSERT INTO dbo.TempData(Col1) VALUES(rand()*100000)
 SET @Count= @Count+1
  END
 --Select Top 10 records

  SELECT top 10* FROM dbo.TempData

















--Create Nonclusterd index on Col1

CREATE  NONCLUSTERED INDEX [TempData_Col1] ON dbo.TempData (Col1 ASC)


 --Select Top 10 records


SELECT top 10* FROM dbo.TempData













No comments:

Post a Comment