Where are views stored in SQL Server

Question

I'm a SQL Server newbie. I've tried foraging around on the web for a while but could not get my question answered. Can anyone please tell me where exactly is a view stored in SQL server 2008 database?

Answer 1

If you mean the tables the view produces then the answer is that they aren't stored at all. A view is just a query, and that is all it stores. When you query a view the db engine just fetches your view query's results and then queries those.

DB engines can store 'materialized' views, but that's a different topic.

Answer 2

The pedantic answer to your question is... only Microsoft knows exactly where view metadata is physically stored. In the move from SQL 2000 to SQL 2005 (on which 2008 is based) MS got rid of direct access to system tables where views used to be literally stored (dbo.sysviews and dbo.syscomments) and added a layer of abstraction (via the hidden resources database) which means you can only access meta data about views via catalog views. INFORMATION_SCHEMA is an ANSI compliant set of catalog views. While marginally useful for their relative portability between versions, often more information is available from the sql 2008 catalog views - in this case sys.views and sys.sql_modules

Be aware that views can be created with the ENCRYPTION option set which encrypts the sys.comments record(s) that contain the SQL definition of the view. But if not encrypted, then sp_helptext [MyView] will give you a quick look at the definition.

edited as per 1st comment below, to replace "sys.comments" with "sys.sql_modules"

Answer 3

Note: Based on this post http://improve.dk/archive/2012/08/27/where-does-sql-server-store-the-source-for-stored-procedures.aspx, is very likely that the definition of views are stored (also) in sys.sysobjvalues system table.

The list of all user T-SQL modules (within SQL Server 2008) can be queried using sys.sql_modules system view (link). Here, you can find the definitions of user views (column definition):

SELECT  QUOTENAME(s.name)+'.'+QUOTENAME(o.name) AS full_object_name, 
        m.*
FROM    sys.sql_modules m
JOIN    sys.objects o ON m.object_id=o.object_id
JOIN    sys.schemas s ON o.schema_id=s.schema_id
WHERE   o.type='V' -- only view objects
ORDER BY full_object_name

If you run EXEC sp_helptext 'sys.sql_modules' you will get the source code of this system view:

CREATE VIEW sys.sql_modules AS
    SELECT object_id = o.id,
        definition = object_definition(o.id),
        uses_ansi_nulls = sysconv(bit, o.status & 0x40000),             -- OBJMOD_ANSINULLS
        uses_quoted_identifier = sysconv(bit, o.status & 0x80000),      -- OBJMOD_QUOTEDIDENT
        is_schema_bound = sysconv(bit, o.status & 0x20000),             -- OBJMOD_SCHEMABOUND
        uses_database_collation = sysconv(bit, o.status & 0x100000),    -- OBJMOD_USESDBCOLL
        is_recompiled = sysconv(bit, o.status & 0x400000),              -- OBJMOD_NOCACHE
        null_on_null_input = sysconv(bit, o.status & 0x200000),         -- OBJMOD_NULLONNULL
        execute_as_principal_id = x.indepid
    FROM sys.sysschobjs o
    LEFT JOIN sys.syssingleobjrefs x ON x.depid = o.id AND x.class = 22 AND x.depsubid = 0 -- SRC_OBJEXECASOWNER
    WHERE o.pclass <> 100
        AND ((o.type = 'TR' AND has_access('TR', o.id, o.pid, o.nsclass) = 1)
            OR (type IN ('P','V','FN','IF','TF','RF','IS') AND has_access('CO', o.id) = 1)
            OR (type IN ('R','D') AND o.pid = 0))

You can see that this view queries another system object sys.sysschobjs that, I think, is the system table used to store definition of views.

Note 1: Using INFORMATION_SCHEMA.VIEWS to find definition of a view is not a reliable method because INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION column definition is convert(nvarchar(4000), object_definition(object_id)) (max. 4000 chars).

Note 2: Instead, you should use sys.sql_modules.definition column: definition = object_definition(o.id). If you look at object_definition function (link) you will see that return type is nvarchar(max).

Answer 4

In a system table.

The following query will retrieve them...

SELECT TABLE_NAME as ViewName, VIEW_DEFINITION as ViewDefinition FROM INFORMATION_SCHEMA.Views

To view edit them normally you would look in the view folder under tables in studio manager.

You can create/edit them from this folder using the designer or write scripts.

Answer 5

View is a simple SQL statement that is stored in database schema (INFORMATION_SCHEMA.Views). So when ever we call the view the SQL statement gets executed and return the rows from main physical table.

You can also tell the view as a Logical table that store the defination (the sql statement) but not the result.

You can see the defination using below statement, as said by Dan above, Only if the view defination is Not encrypted: SELECT TABLE_NAME as ViewName, VIEW_DEFINITION as ViewDefinition FROM INFORMATION_SCHEMA.Views

More details on View @ MSDN.