2

My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am I able to query for greater than 1024 columns but I can not save this very same query as a view?

I am using SQL Server 2012 Enterprise

  • 1
    Maybe your boss doesn't want a view after all. You can accomplish this with a stored procedure, for example, that builds the SELECT list based on his choices. – Aaron Bertrand Mar 25 '16 at 0:38
  • @AaronBertrand Oh definately, I played with that idea but ran into the issue of actually presenting the result set of the stored procedure in a table format so that it can be operated on :/ – Tyler Benzing Mar 25 '16 at 0:43
  • We write CRUD stuff like this into user interfaces all the time, not sure what the issue might be, but ok. – Aaron Bertrand Mar 25 '16 at 0:45
  • 1
    @AaronBertrand Management. Non DBA people wanting to look at databases is the issue -_- appreciate the help though – Tyler Benzing Mar 25 '16 at 0:47
2

A View is not a Result Set. A View is an object that needs a clear definition. A Result Set, on the other hand, has a bit more freedom. For example, you can have both missing column names and/or duplicate column names in a Result Set. But Tables and Views cannot allow for either since you would not be able to SELECT such columns (there would be no way to clearly specify which columns you were referring to).

Run the following, for example:

SELECT 1, 2 AS [t], 3 AS [t];
5

From CREATE VIEW (Transact-SQL):

A view can have a maximum of 1,024 columns.

From Maximum Capacity Specifications for SQL Server:

Extract

Each table can have a maximum of 1,024 columns (30,000 for wide tables).

So, the simple answer is that the (somewhat arbitrary) limits are different.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.