0

I am trying to create a SQL View in SSMS. I am using Views because they are easier to invoke from Power BI than Stored Procedures (especially when no parameters are needed).

I start by writing and testing a SQL SELECT query with an ORDER BY clause.

When I copy and paste my query in the New View:

  1. SSMS adds a TOP (100) PERCENT to my SELECT statement.

  2. Tells me that my ORDER BY clause (which works perfectly well in the SQL SELECT) may not work.

  3. If you click the Help button on the dialog, you are taken to a Microsoft "Oops! No F1 help match was found" page.

    enter image description here

My questions are:

  1. Is TOP (100) PERCENT not implied when it is left out of a SQL Select?

  2. Why would a View based on a SQL Select statement not like ORDER BY clauses?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Doug Kimzey
  • 1,019
  • 2
  • 17
  • 32
  • If you copy and paste a query without a `TOP (100) PERCENT` SSMS won't add a `TOP` to it... It would literally take the SQL you had before; which means the `TOP (100) PERCENT` was already there. – Thom A Jan 24 '22 at 19:47
  • 1
    Don't use the visual view designers. Just paste `CREATE VIEW ... AS SELECT` into a new query window. But the `ORDER BY` needs to be on the `SELECT` from the view not inside the view anyway – Martin Smith Jan 24 '22 at 19:48
  • The SQL string that I pasted did not include a TOP (100) PERCENT. SSMS View Designer added a TOP (100) PERCENT to the SELECT. – Doug Kimzey Jan 24 '22 at 19:52
  • Don't use the designer, like Martin said; it's just not good. Write your queries properly. – Thom A Jan 24 '22 at 19:53
  • Could you clarify "ORDER BY needs to be on the SELECT from the view not inside the view"? – Doug Kimzey Jan 24 '22 at 19:53
  • You can't naturally use an `ORDER BY` clause in a view (and for most purposes you shouldn't), have the hack to allow you to do so is to include `TOP (100) PERCENT` and then you are allowed an `ORDER BY`. My guess is SSMS is doing that for you, but I suggest thats not the best way to do it. – Dale K Jan 24 '22 at 19:55
  • RE: "Could you clarify" Instead of `CREATE VIEW V AS SELECT * FROM T ORDER BY C GO SELECT * FROM V` - you need to do `CREATE VIEW V AS SELECT * FROM T GO SELECT * FROM V ORDER BY C` - the only place that an `ORDER BY` guarantees anything about the final order of the results is on the outer query. `SELECT TOP (100) PERCENT` is entirely optimised out and ignored anyway – Martin Smith Jan 24 '22 at 19:57
  • So you either need to invoke a SQL query or proc rather than just select a view - or do the sorting in PowerBi – Martin Smith Jan 24 '22 at 20:01
  • I did [try to reproduce](https://i.stack.imgur.com/nuJ6R.gif) the behaviour you speak of, but I couldn't. – Thom A Jan 24 '22 at 20:07
  • Hi Larnu - my steps to reproduce are: (1) create a SQL query without a TOP (100) PERCENT with an ORDER BY (2) Copy the SQL Select (3) Right-click 'Views' in the Object Explorer (4) Past the copied SQL Select into the View Designer (5) Click Save. – Doug Kimzey Jan 24 '22 at 20:10
  • It looks like I need to research calling a stored procedure from Power BI. – Doug Kimzey Jan 24 '22 at 20:12
  • When [I add a sorted column](https://i.stack.imgur.com/2p3fN.gif) it adds a `TOP` too, @DougKimzey . I can't reproduce a scenario where the `TOP` is there with no `ORDER BY` nor vice versa. As I stated, if you are copying SQL *without* an `ORDER BY` SSMS won't add it; it was already there (maybe you just didn't notice it). For a `VIEW` to have an `ORDER BY` it *must* have a `TOP` (or `OFFSET` clause) as others have stated, however, a `TOP (100) PERCENT` with an `ORDER BY` would be effectively ignored, as the ordering should be done *outside* of the `VIEW`. – Thom A Jan 24 '22 at 20:17
  • @Larnu https://i.stack.imgur.com/5WfxY.gif – Martin Smith Jan 24 '22 at 20:23
  • @MartinSmith and SSMS added the `TOP (100)` as soon as the `ORDER BY` was there, before you have a chance to copy it, so it further evidences that it *was* there when the OP copied it. – Thom A Jan 24 '22 at 20:25
  • They have never claimed anything different. "I start by writing and testing a SQL SELECT query **with an ORDER BY clause**. When I copy and paste my query in the New View:" – Martin Smith Jan 24 '22 at 20:25
  • [*"The SQL string that I pasted did not include a TOP (100) PERCENT"*](https://stackoverflow.com/questions/70839627/why-does-ssms-insert-a-top-100-percent-into-the-view-that-i-am-trying-to-wr?noredirect=1#comment125233543_70839627) @MartinSmith . – Thom A Jan 24 '22 at 20:26
  • yes it didn't include a **TOP 100 PERCENT** - SSMS view designer adds that itself if you paste in SQL text with an `ORDER BY` (or where the SQL text is edited to add an `ORDER BY`) – Martin Smith Jan 24 '22 at 20:38
  • I am going to see if I can call a stored procedure from Power BI (assuming a SQL Select in an sp can have an ORDER BY...). Views come in to Power BI - but are not reliably ordered. I can set the order of a view in Power BI (but this will cost performance..) I thought Views were a way to package complex SQL Selects. I will have to research when and why views are used. Thanks to all... – Doug Kimzey Jan 24 '22 at 20:45
  • Does this answer your question? [Create a view with ORDER BY clause](https://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause) – Charlieface Jan 24 '22 at 20:56

1 Answers1

1

SQL views to not support ORDER BY. For more detail on this, see these other posts:

Create a view with ORDER BY clause

Possible to have an OrderBy in a view?

Order BY is not supported in view in sql server

Why use Select Top 100 Percent?

As @Martin Smith said, your options are one of the following:

  1. Put the ORDER BY in a query that references the view.
SELECT * FROM ViewName ORDER BY [...]
  1. Do the ordering in the Power Query Editor. If you don't have any steps before this sort that break query folding, this should be translated into a native SQL query that gets evaluated on the SQL Server.

I recommend the latter since further steps can also potentially be folded in as well. Specifying your own query does not support query folding.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Hi Alexis - I tried the SELECT * FROM ViewName ORDER BY [...] in the Power Query Editor and got the same warning message in Power Query Editor as I get for a View in SSMS with an ORDER BY. I think the only thing to do is try a stored proc. – Doug Kimzey Jan 24 '22 at 21:37
  • Make sure that the view does not have the `TOP (100) PERCENT` or `ORDER BY` parts in it, just a plain `SELECT ... FROM` query. – Alexis Olson Jan 24 '22 at 21:39
  • It does not. It is a plain SELECT ... FROM. – Doug Kimzey Jan 24 '22 at 21:44
  • I cannot reproduce that behavior. I suggest using my second suggestion. – Alexis Olson Jan 24 '22 at 21:45
  • The view is a plain SELECT ... FROM. No `TOP (100) PERCENT` or `ORDER BY` parts. I added a new query in Power Query Editor that is a SELECT * FROM UnorderedView ORDER BY [...]. Power Query Editor returned an unordered bag of rows and displayed the same error that you get in MSSM when you try to create a View with an `ORDER BY`. – Doug Kimzey Jan 24 '22 at 21:47
  • I verified the View by having MSSM create a DROP...CREATE script for the view. The `SELECT` contains no `TOP (100) PERCENT` and no `ORDER BY`. – Doug Kimzey Jan 24 '22 at 21:50
  • It does not do that for me when I test it on my computer. Does Refresh Preview help at all? If not, do you get the same error if you just use `SELECT * FROM ViewName` as the query? – Alexis Olson Jan 24 '22 at 21:51