Whether order of rows will be changed when its selected?

Question

Post reply

Whether order of rows will be changed when its selected?

sharmili.net

SSC-Addicted

Points: 451
More actions
October 18, 2013 at 7:16 am

#278190

Hi Everyone,
This is my table which have no primary key,My question is If I select this whether the order of rows will be changing?Or the same order will be displayed when we select?And what is the reason behind for this?
no sid
1101
7202
4302
6788
967
39080
Thanks in advance.
Sharmi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259135 More actions · Answer 1

Most of the time you will get the order in which the rows were inserted, unless you use indexes.

It is however not 100% garantueed that you will always get the same order.

Conclusion: order does not matter. If it does, use ORDER BY.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Just to add to what Koen said...

The only way to guarantee that rows are returned in a specific order is when ORDER BY is used.

It's also worth noting that ORDER BY is used more often than it is needed. Sorting is an expensive operation for SQL server, especially when dealing with millions of rows. I let the application that uses the data do the sorting whenever possible.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Take a look at this article. http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/