| Author |
Topic |
|
under2811
Constraint Violating Yak Guru
366 Posts |
 Posted - 2006-02-23 : 13:02:51
|
| Hi..I have basic level question..Why would we use SELECT TOP 100 PERCENT in a SQL Server 2000 query?T.I.APapillon |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-23 : 13:12:23
|
| The only time that you need it is if you have an ORDER BY in a view.Tara Kizeraka tduggan |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-23 : 13:12:43
|
| good question. Why would you do that? |
|
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-02-23 : 13:25:23
|
| Hi..Tara..It is not sufficient using only select statement in view with order by clause??T.I.APapillon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-23 : 13:36:25
|
| Why not try creating one in Query Analyzer and seeing what you get? You'll learn more by trying things out. Here is an example of what to try:CREATE VIEW TestViewASSELECT nameFROM sysobjectsORDER BY nameGOTara Kizeraka tduggan |
|
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-02-23 : 13:49:23
|
| thanks tara..i got it..... :) :)RegardsPapillon |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2006-02-23 : 14:11:47
|
| I always wondered why view can't have an order by clause within its defintion?, may be a view is a "virtual table" and in a table there is no ordering of the rows... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-23 : 15:15:45
|
| why not select from the view and then use ORDER BY. -ec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-23 : 15:17:39
|
quote:
Originally posted by eyechart
why not select from the view and then use ORDER BY. -ec
I've never understood why people don't do that. I just hate seeing SELECT TOP 100 PERCENT in views. It seems so pointless.Tara Kizeraka tduggan |
|
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-02-23 : 15:36:04
|
| Hi..Tara..If i created view and if i want to insert huge recds from that view into table n my view contains select top 100 percent with order by clause..is there more stress to server cause first it sort then select n then insert recds into my table...and will my table contains the same order that view has????T.I.APapillon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-02-23 : 15:43:14
|
| You'll have to test that. I don't use views to insert data.Tara Kizeraka tduggan |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-23 : 16:20:11
|
| >>and will my table contains the same order that view has????If your table has a clustered index (on something other than an identity column) then the order of your select statement will not effect the order of the inserted rows. However, I imagine that if you order the selected rows by the clustered index columns then it could help the speed of your insert.Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-02-23 : 21:08:45
|
quote:
Originally posted by tkizer
quote:
Originally posted by eyechart
why not select from the view and then use ORDER BY. -ec
I've never understood why people don't do that. I just hate seeing SELECT TOP 100 PERCENT in views. It seems so pointless.Tara Kizeraka tduggan
We use some views that are created for the specific purpose of BCPing data out in a specific order. I know we could do this with a query, but it is convenient to BCP using a view with the order by built into it.I also use SELECT TOP 100 PERCENT when loading a temp table with an identity column when I want to use the identity column as a row number for a set of ordered data. This makes sure that the identity sequence corresponds to the sequence of the ordered data. This is very convenient for ranking large sets of data.I sometimes use a SELECT TOP 100 PERCENT in a derived table in a query to prevent SQL Server from "stepping into" the query in it's query plan. I used it in the function, F_TABLE_NUMBER_RANGE, in the link below, because I found that since it was an inline table function, SQL Server would merge the query plan with the calling query, and sometimes have a very slow query. I could have prevented this by using a multi-statement table function, but that would slow it down because it required the additional processing of inserting it into a table.The F_TABLE_NUMBER_RANGE function has 5 cross joins in 2 levels of derived tables created from UNION ALLs, so when this gets included in an outer query plan, results could be strange. Also, SELECT TOP 100 PERCENT seems to prevent SQL Server from spending a lot of time sorting through all the possible query plans.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685CODO ERGO SUM |
|
|
|
|
Why would we use SELECT TOP 100 PERCENT??