Our community of experts have been thoroughly vetted for their expertise and industry experience.
Published:
Browse All Articles > Does SELECT retrieve rows in the order they are inserted ( Back to Basics -1 ) ?
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted? Many believe this is the case.
Let us try to examine for ourselves with an example.
To get started, use the following script, which I ran on my sever to create a table OrderTest in database TempDB and, additionally, to insert 4 rows into the table.
USE TempDB GOCREATE TABLE OrderTest(N1 INT NOT NULL, C1 VARCHAR(100) NOT NULL)GO INSERT INTO OrderTest(N1, C1) VALUES(1, 'First Insert') GO INSERT INTO OrderTest(N1, C1) VALUES(10, 'Second Insert') GO INSERT INTO OrderTest(N1, C1) VALUES(2, 'Third Insert') GO INSERT INTO OrderTest(N1, C1) VALUES(4, 'Fourth Insert') GO
The rows are NOT returned in the order in which it is inserted!! Surprising??
What made the difference?
In the first case, the SQL Server query optimizer decided to do a table scan while it opted for a Clustered Index Scan in the latter case. See the following execution plans for an illustration. The verdict.
SQL server does not guarantee any order of output (unless you explicitly append an ORDER BY clause to the query to order your output in a desired way added). Further, it is the SQL Server query optimizer that decides how the data should be retrieved. Hence never assume that data will be returned in the order in which it is inserted.
Comments (0)