Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, February 5, 2016 2:07 PM
Points: 15,016, Visits: 12,988
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 SQLKover.

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


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 1,469, Visits: 5,284
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based spitter? Try PatternSplitCM
Need to remove of replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:47 PM
Points: 15,266, Visits: 15,254
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