May 30, 2013 at 7:35 am
OK. I have the most basic of queries and SSMS is barking at me. I created this as a View in SSMS.
SELECT TOP (100) PERCENT AddressTypeID, AddressTypeDesc
FROM dbo.AddressTypes
WHERE (Active = 1)
ORDER BY AddressTypeDesc
Runs just fine. I can save it as a saved View but when I do, SSMS says:
Warning: The ORDER BY clause is used only to dertermine the rows that are returned by the TOP Clause in the View Definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
OK. I've read this several times and I'm still failing to understand what it's trying to tell me. Can someone please specify?
I can ofcourse simply create a Query and it works just fine.
PS - does anyone know how to disable the default "TOP PERCENT" SSMS insists on inserting into the queries/views?
May 30, 2013 at 7:38 am
RedBirdOBX (5/30/2013)
OK. I have the most basic of queries and SSMS is barking at me. I created this as a View in SSMS.SELECT TOP (100) PERCENT AddressTypeID, AddressTypeDesc
FROM dbo.AddressTypes
WHERE (Active = 1)
ORDER BY AddressTypeDesc
Runs just fine. I can save it as a saved View but when I do, SSMS says:
Warning: The ORDER BY clause is used only to dertermine the rows that are returned by the TOP Clause in the View Definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
OK. I've read this several times and I'm still failing to understand what it's trying to tell me. Can someone please specify?
I can ofcourse simply create a Query and it works just fine.
PS - does anyone know how to disable the default "TOP PERCENT" SSMS insists on inserting into the queries/views?
In a view the only thing an order by does is to determine which rows to return. It does NOT mean the view will always be in that order. Since you have top 100 percent just drop the top and the order by.
SELECT AddressTypeID, AddressTypeDesc
FROM dbo.AddressTypes
WHERE Active = 1
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 7:43 am
The discussion on this link http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e0e7fb1c-0327-4833-b9fb-8a660b5389ca explains it.
May 30, 2013 at 7:52 am
I don't suppose anyone knows how to remove the TOP 100 PERCENT from SSMS default settings?
May 30, 2013 at 7:59 am
RedBirdOBX (5/30/2013)
I don't suppose anyone knows how to remove the TOP 100 PERCENT from SSMS default settings?
Not sure what you mean. Are you using a wizard or something to create your view?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 1:11 pm
SSMS has an option where you can define how many rows are selected by default when you right-click a table. Go to Tools...Options...SQL Server Object Explorer...Commands. The key name is "Value for Select Top <n> Rows command".
If you're using Access or a third-party wizard or SSMS template or something, that's different, but I don't know of anything that will give you a default 100 PERCENT in a view definition. The bottom line is what are you using to create a view?
I just type the CREATE VIEW statement (and all the other DDL statements) myself, so I admit I'm not too familiar with the wizards that may be available. Call me old-fashioned, but I still type all my CREATE TABLE statements complete with indexes, constraints, etc.
May 30, 2013 at 1:20 pm
Ed Wagner (5/30/2013)
I just type the CREATE VIEW statement (and all the other DDL statements) myself, so I admit I'm not too familiar with the wizards that may be available. Call me old-fashioned, but I still type all my CREATE TABLE statements complete with indexes, constraints, etc.
I wouldn't call that old fashioned. I would call that efficient. I can type in the definition WAY faster than pointing and clicking with sporadic typing. It also produces far cleaner code than the designer too. 😀
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 1:24 pm
Thanks. I feel the same way...that's why I still don't like the GUI table or view designers. I completely agree on speed and cleanliness of the tables, too. There are some who laugh at me for doing so, but I find I create much cleaner tables much more quickly. I also think that it reinforces the SQL language and how things work, which I believe some people don't really want to know.
May 30, 2013 at 1:28 pm
I use a few different methods of creating a view. I can Right Click on the Views folder in SSMS and simply select New View. I can also simply click New Query and do it all by hand. But in any event, the SSMS by default always puts in the TOP 100 PERCENT in the Select statement which is what I'd like to remove if possible. Who would have thought using Order By would have been so much trouble?
May 30, 2013 at 1:37 pm
RedBirdOBX (5/30/2013)
I use a few different methods of creating a view. I can Right Click on the Views folder in SSMS and simply select New View. I can also simply click New Query and do it all by hand. But in any event, the SSMS by default always puts in the TOP 100 PERCENT in the Select statement which is what I'd like to remove if possible. Who would have thought using Order By would have been so much trouble?
I just right clicked view and select new view. It did not put top 100 percent. It did bring up some sort of query designer though.
I still don't quite understand what you mean that SSMS put in the top 100 percent. The bottom line really is that unless you trying to only return certain rows there is no point in using an order by in a view. A view returns a table which by definition is an unordered set so thinking that a view will have the results ordered is a misunderstanding of how views work.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 1:53 pm
RedBirdOBX (5/30/2013)
I use a few different methods of creating a view. I can Right Click on the Views folder in SSMS and simply select New View. I can also simply click New Query and do it all by hand. But in any event, the SSMS by default always puts in the TOP 100 PERCENT in the Select statement which is what I'd like to remove if possible. Who would have thought using Order By would have been so much trouble?
I'm wondering if you have an SSMS plug-in such as SSMS Toolpack that's generating the default snippet.
May 30, 2013 at 2:02 pm
May 30, 2013 at 2:11 pm
May 30, 2013 at 2:14 pm
kl25 (5/30/2013)
RedBirdOBX (5/30/2013)
I use a few different methods of creating a view. I can Right Click on the Views folder in SSMS and simply select New View. I can also simply click New Query and do it all by hand. But in any event, the SSMS by default always puts in the TOP 100 PERCENT in the Select statement which is what I'd like to remove if possible. Who would have thought using Order By would have been so much trouble?I'm wondering if you have an SSMS plug-in such as SSMS Toolpack that's generating the default snippet.
Ahh that is because in the designer you are adding a sort type. It will not add the top 100 percent until you specify a sorttype for a given column. Here is a classic example of why just learning to write your queries is far more efficient that using the designers. You have been banging your head against the wall trying to figure out why it works. Nobody around here has been able to offer much help because most of the folks around here just type in the query. Especially with intellisense the designers just don't offer much.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 30, 2013 at 2:19 pm
Thanks Sean. You're right. I never use the designer. Only type. It sounded like a snippet--didn't realize the designer did something like that. 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply