July 15, 2003 at 1:01 am
First of all, I knew the correct answer because is has become a classical one since SQL 7.
But I want to make one remark: it is not ansi and is a bad idea to put an order by in a view. The order by should always be done in the select by the application.
This is the same issue as depending on the order of records in an table. If your application depends on this, you may run in to trouble. The order will change if (e.g. for performance) you have to change the clustered index.
So:
1) don't depend on the order of the columns (don't use select *) in your applications.
2) don't depend on the physical order of the rows in the table.
Both may change!!
July 15, 2003 at 4:27 am
Just a comment...
With an ORDER BY statement it will generally produce the same results. The only difference may be when two or more records have the same value for the column ordered on which is usually decided by either the clustered indexes sort of these or the row identifier.
SELECT * is a bigger nuisance since SQL records the actually columns and their position from the table when the view is built. If you alter the table you have to force the view to recompile dropping it's listing and rebuild them. However the columns will be in order of their colorder in the table unless the table is altered.
July 15, 2003 at 12:51 pm
One may validly argue that the correct answer should be:
CREATE VIEW
vArticles as
SELECT TOP 100 PERCENT With Ties * FROM Articles
ORDER BY CreateDt
Rather than: (not considering ties)
CREATE VIEW
vArticles as
SELECT TOP 100 PERCENT * FROM Articles
ORDER BY CreateDt
July 15, 2003 at 3:25 pm
OK I'll bite;
I've never seen anything like WITH TIES;
I looked to see if it was a query hint, but it looks like its a named set, right? i found something in the BOL about "Using WITH to Create Named Sets", but it didn't specify what the advantage would be.
Lowell
OK digging deeper into the BOL, i found WITH Ties:
SELECT [ ALL | DISTINCT ]
[ TOP n [ PERCENT ] [ WITH TIES ] ]
< select_list >
WITH TIES
Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can only be specified if an ORDER BY clause is specified.
< select_list >
The columns to be selected for the result set. The select list is a series of expressions separated by commas.
*
Specifies that all columns from all tables and views in the FROM clause should be returned. The columns are returned by table or view, as specified in the FROM clause, and in the order in which they exist in the table or view.
Edited by - lowell on 07/15/2003 3:30:38 PM
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply