Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Whether order of rows will be changed when its selected? Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 10, 2014 4:43 AM
Points: 15, Visits: 44

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
1 101
7 202
4 302
6 788
9 67
3 9080

Thanks in advance.

Sharmi
Post #1506145
Posted Friday, October 18, 2013 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 13,263, Visits: 11,053
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1506150
Posted Friday, October 18, 2013 7:50 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:06 AM
Points: 612, Visits: 2,853
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1506172
Posted Friday, October 18, 2013 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1506177
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse