0

I have created a view in SQL Server which is working properly and sorting exactly as I require. Here is the view ...

CREATE VIEW [vwInventoryList]
AS

SELECT TOP 100 PERCENT x.[Cols_I_Need]

FROM dbo.vwInventoryListSummary x

GROUP BY x.[columns]
ORDER BY dbo.x.ID,dbo.x.[Description],[with_more_4_cols]
GO

The issue is when I execute the below sql from C# or even in SQL Server Management Studio, the result is not sorted as I have specified in my view and I don't understand why.

Select * from vwInventoryList

I would like the view to control the sorting of the data and my code (select statement) should simply select from the view without specifying the order by in the C# code.

Any ideas why this is happening ?

Ronjon
  • 1,789
  • 4
  • 16
  • 26
  • 3
    Views cannot be sorted. Use `SELECT * FROM [vwInventoryList] ORDER BY..` – Lukasz Szozda Aug 24 '17 at 16:46
  • 2
    [Why does ORDER BY not belong in a View? - dba.se answer by Aaron Bertrand](https://dba.stackexchange.com/a/21437/43889) – SqlZim Aug 24 '17 at 16:47
  • 2
    If you want to play ugly use `SELECT TOP 1000000 ... ORDER BY` instead of `SELECT TOP 100 PERCENT..` to enforce intermediate materialization – Lukasz Szozda Aug 24 '17 at 16:48
  • 1
    And one more thing, consider using some sort of SQL formatter, analysing this code is nightmare. – Lukasz Szozda Aug 24 '17 at 16:53
  • @lad2025 The query is too complex `SELECT * FROM dbo.vwInventoryListSummary ORDER BY vwInventoryListSummary.ProductCategoryFID` will be enough to get the same answers. – Juan Carlos Oropeza Aug 24 '17 at 16:56

1 Answers1

1

See Create a view with ORDER BY clause to understand the order by clause in a view. It is very helpful. One way that you don't have to use an order by in the C# code is to create a stored procedure that calls this view. In the procedure you can put the sql below

SELECT * FROM [vwInventoryList] ORDER BY {Column}

It keeps the C# code from becoming speckled with select statements and allows you to better keep track of procedures. Ultimately it is up to you. Also see What are the pros and cons to keeping SQL in Stored Procs versus Code for more information on when to use sql inline vs stored procs.

Max Pringle
  • 621
  • 6
  • 18