Does SELECT retrieve rows in the order they are inserted ( Back to Basics -1 ) ?: SQL server 2005, SQL, SELECT, Order of output

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 
GO
CREATE 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Select all Open in new window

We are ready for the test. Let us select data from the table.

USE TempDB 
GO
SELECT * FROM OrderTest 
GO

1:
2:
3:
4:

Select all Open in new window

Fig1.1.bmp

64 KB
TestOutput1

The results are indeed retrieved in the order in which it is inserted. Convinced?

Now I am going to add a primary key to the table.

USE TempDB 
GO
ALTER TABLE OrderTest ADD PRIMARY KEY(N1)
GO

1:
2:
3:
4:

Select all Open in new window

With the primary key in place, let us once again select data from the table using the previous select statement.

USE TempDB 
GO
SELECT * FROM OrderTest 
GO

1:
2:
3:
4:

Select all Open in new window

Fig1.2.bmp

61 KB
TestOutput2

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.

Fig1.3.bmp

223 KB
ExecutionPlan1

Fig1.4.bmp

258 KB
ExecutionPlan2

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.

Gain Access to all our Tech Resources

Get personalized answers

Ask unlimited questions

Access Proven Solutions

Search 3.2 million solutions

Read In-Depth How-To Guides

1000+ articles, demos, & tips

Watch Step by Step Tutorials

Learn direct from top tech pros

And Much More!

Your complete tech resource

30-day free trial. Register in 60 seconds.

Does SELECT retrieve rows in the order they are inserted ( Back to Basics -1 ) ?

SQL server 2005

SQL

SELECT

Order of output

SQL Server 2005

About The Author

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server 2005 Experts