locked
how to know the size of a view RRS feed

  • Question

  • Dear Forum Members,
    I have been given task to know the size of the views before they are ported to a different
    server to estimate the disk space required in the new server.
    I have around 10 views.Can you please advise me.


    Thanks

    • Moved by Tom Phillips Monday, November 16, 2009 6:04 PM TSQL Question (From:SQL Server Database Engine)
    Monday, November 16, 2009 5:44 PM

Answers

  • A "view" has no size, unless there are indexes on it.  It is simply a predefined SELECT statement.

    • Marked as answer by Kent Waldrop _ Thursday, September 8, 2011 7:53 PM
    Monday, November 16, 2009 6:04 PM
  • Unless it is an Indexed View, a view doesn't take up space in a database.  The data returned by the view exists in the underlying tables that the view queries.  Adding a view to a database is just adding a query that is presented as an object to the database, it doesn't add data or cost storage unless it is indexed, in which case you would use sp_spaceused to determine the index space requirements.



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Kent Waldrop _ Thursday, September 8, 2011 7:53 PM
    Monday, November 16, 2009 6:07 PM
  • Hello Forum Members,
    Thanks for the advise...
    will (rowcount of the view) * (sum of width of the columns) help me in giving necessary information to my manager??

    No.  The answer is a view unless indexed doesn't have a size value.  <<- This is the exact statement you give your manager.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by forsayi Monday, November 16, 2009 7:07 PM
    Monday, November 16, 2009 7:04 PM

All replies

  • I dont think the size of a view can be estimated using SP_SPACEUSED . The "content" of the view is calculated every time you select from the view which inturn takes data from table. In other words, every time you query the view its query is executed to retrieve the data. It's just like a running a query by itself.
    Thanks, Leks
    Monday, November 16, 2009 5:48 PM
  • Hi Thanks for the advise.I am interested in the current size....views are refreshed as and when required....for example if one view has 210000 rows and has 33 columns can you please advise me how to proceed further?? Based on the advise I will do the same thing for others.

    Thanks
    Monday, November 16, 2009 5:51 PM
  • A "view" has no size, unless there are indexes on it.  It is simply a predefined SELECT statement.

    • Marked as answer by Kent Waldrop _ Thursday, September 8, 2011 7:53 PM
    Monday, November 16, 2009 6:04 PM
  • Unless it is an Indexed View, a view doesn't take up space in a database.  The data returned by the view exists in the underlying tables that the view queries.  Adding a view to a database is just adding a query that is presented as an object to the database, it doesn't add data or cost storage unless it is indexed, in which case you would use sp_spaceused to determine the index space requirements.



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Kent Waldrop _ Thursday, September 8, 2011 7:53 PM
    Monday, November 16, 2009 6:07 PM
  • Hello Forum Members,
    Thanks for the advise...
    will (rowcount of the view) * (sum of width of the columns) help me in giving necessary information to my manager??
    Monday, November 16, 2009 6:42 PM
  • Hello Forum Members,
    Thanks for the advise...
    will (rowcount of the view) * (sum of width of the columns) help me in giving necessary information to my manager??

    No.  The answer is a view unless indexed doesn't have a size value.  <<- This is the exact statement you give your manager.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by forsayi Monday, November 16, 2009 7:07 PM
    Monday, November 16, 2009 7:04 PM
  • Supporting information from the Books Online topic View Resolution:

    Only the definition of a nonindexed view is stored, not the rows of the view. The query optimizer incorporates the logic from the view definition into the execution plan it builds for the SQL statement that references the nonindexed view.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, November 16, 2009 7:08 PM
  • Unless it is an Indexed View, a view doesn't take up space in a database.  The data returned by the view exists in the underlying tables that the view queries.  Adding a view to a database is just adding a query that is presented as an object to the database, it doesn't add data or cost storage unless it is indexed, in which case you would use sp_spaceused to determine the index space requirements.



    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!

    While a view that is not an indexed view add no additional space requirement to the database the view can in some cases add hidden costs to queries that use the view.  This problem can occur when a view is composed of multiple tables and a query is written that uses the view and data from one or more of the tables that compose the view is not used by the query.  This makes the data in the view from the unused tables something like "spectator data".

    Spectator data adds no value to the query but adds additional unnecessary overhead to the query.

    Beware of "monster views" that are created with the intention of "providing all of the data that the user will ever need".  They might eventually remind you of a vacuum cleaner.


    Kent Waldrop
    Monday, November 16, 2009 7:25 PM