<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
11,419 Points
4,519 Views
4 Endorsements
Last Modified:
Approved
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 

Open in new window


We are ready for the test.  Let us select data from the table.
USE TempDB 
GO
SELECT * FROM OrderTest 
GO

Open in new window

TestOutput1The 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 

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

Open in new window

TestOutput2The 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.
 ExecutionPlan1 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.
4
Comment
0 Comments

Featured Post

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month