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 1234»»»

Out of order Expand / Collapse
Author
Message
Posted Thursday, October 20, 2011 8:57 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 2,084, Visits: 2,081
Comments posted to this topic are about the item Out of order
Post #1194110
Posted Thursday, October 20, 2011 9:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
Nice question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1194115
Posted Friday, October 21, 2011 1:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,542, Visits: 2,410
CREATE VIEW vw_clients AS
SELECT TOP 100 PERCENT Code, Name FROM Clients
ORDER BY Code

I am curious to know if the VIEW above always returns records in sorted order.
Post #1194139
Posted Friday, October 21, 2011 1:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:41 AM
Points: 1,593, Visits: 5,631
Carlo Romagnano (10/21/2011)
CREATE VIEW vw_clients AS
SELECT TOP 100 PERCENT Code, Name FROM Clients
ORDER BY Code

I am curious to know if the VIEW above always returns records in sorted order.


No, it doesn't, unless you're still running SQL 2000. On more recent SQL versions the optimiser will see that the TOP 100 PERCENT and the ORDER BY are both redundant and will remove them. If you want the results of a view to be sorted, you have to include the ORDER BY in the SELECT statement you use to query the view.
Post #1194144
Posted Friday, October 21, 2011 1:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 1,415, Visits: 801
Sneaky question.
Post #1194158
Posted Friday, October 21, 2011 1:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 3,982, Visits: 5,228
Good question...
even then, the sort order of any select from the view is not guaranteed....


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1194160
Posted Friday, October 21, 2011 2:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,542, Visits: 2,410
paul.knibbs (10/21/2011)
Carlo Romagnano (10/21/2011)
CREATE VIEW vw_clients AS
SELECT TOP 100 PERCENT Code, Name FROM Clients
ORDER BY Code

I am curious to know if the VIEW above always returns records in sorted order.


No, it doesn't, unless you're still running SQL 2000. On more recent SQL versions the optimiser will see that the TOP 100 PERCENT and the ORDER BY are both redundant and will remove them. If you want the results of a view to be sorted, you have to include the ORDER BY in the SELECT statement you use to query the view.

Thanks. In fact, in sql2000 the optimizer use an index scan (create index idx_client_code on Clients(Code)).
Post #1194167
Posted Friday, October 21, 2011 2:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 2,152, Visits: 1,744
Well, I'm glad I looked at these posts. I knew you could order a view if you did the SELECT TOP (100) PERCENT thing but I hadn't realised that it didn't work!
So what looked like an easy question has become an opportunity to learn something new.
Post #1194171
Posted Friday, October 21, 2011 2:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,542, Visits: 2,410
Stewart "Arturius" Campbell (10/21/2011)
Good question...
even then, the sort order of any select from the view is not guaranteed....

In fact, the TOP clause with the "ORDER BY", in this case, is just a filter and not a command to sort rows.
Post #1194172
Posted Friday, October 21, 2011 2:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:40 AM
Points: 1,347, Visits: 487
Easy point! :)
Post #1194199
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse