Disclaimer: This is work in progress based on notes gathered over the years, intended to consolidate information from the various sources. The content needs yet to be reviewed against the current documentation.
Last updated: 19-Mar-2024
[SQL Server 2005] View (aka virtual table)
- {def} a database object that encapsulates a SQL statement and that can be used as a virtual table in further SQL statements
- cannot be executed by itself
- ⇐ it must be used within a query [15]
- doesn't store any data
- except index views
- data is dynamically produced from the underlying table when the view is used [32]
- views depend on the underlying tables and act like a filter on the underlying tables [32]
- used just like regular tables without incurring additional cost
- unless the view is indexed [25]
- turning a query into a view
- remove the ORDER BY clause
- assure there are no name duplicates
- assure that each column has a name
- projected columns
- columns included in the view
- view’s column list
- renames every output column just as if every column had those alias names in the SELECT statement
- a view is more self-documenting if the column names of the view are specified in the SELECT statement and not listed separately in the view [27]
- {restriction} sorting is not allowed in a view
- unless the view includes a TOP predicate
- ORDER BY clause serves only to define which rows qualify for the TOP predicate [15]
- the only way to logically guarantee sorted results is to define the ORDER BY clause in the executing query [15]
- [SQL Server 2005] had a bug in the Query Optimizer that would enable an ORDER BY in a view using a top 100 percent predicate [15]
- the behavior was never documented or officially supported [15]
- OFFSET FETCH clause
- {restriction} parameters can’t be passed to a view [100]
- {alternative} use an inline table-valued function
- {restriction} cannot reference a variable inside the SELECT statement [100]
- {restriction} cannot create a table, whether permanent or temporary
- ⇒ cannot use the SELECT/INTO syntax in a view
- {restriction} can reference only permanent tables
- ⇒ cannot reference a temporary table [100]
- {benefit} present the correct fields to the user
- {benefit} enforce security
- by specifying
- only needed columns
- projects a predefined set of columns [15]
- hides sensitive, irrelevant, or confusing columns [15]
- should be used in parallel with SQL Server–enforced security [15]
- only needed records
- by allowing users access to the view without the need to give access to the used tables
- grant users read permission from only the views, and restrict access to the physical tables [15]
- {benefit} maintainability
- {benefit} provides a level of abstraction
- hides the complexity of the underlying data structures
- encapsulates (business)logic
- denormalize or flatten complex joins
- can consolidate data across databases/servers
- can be used as single version of truth
- {benefit} allow changing data in the base tables
- {downside} layers of nested views require needless overhead for views’ understanding
- {downside} single-purpose views quickly become obsolete and clutter the database [15]
- {downside} complex views are perceived as having poor performance [15]
- {best practice} use generic/standard naming conventions
- {best practice} use aliases for cryptic/recurring column names
- {best practice} consider only the requested columns
- {best practice} group specific purpose view under own schema
- {best practice} avoid hardcoding values
- {best practice} use views for column-level security together with SQL Server–enforced security
- {best practice} limit views to ad-hoc queries and reports
- for extensibility and control [15]
- ⇐ performance isn’t the reason [15]
- {poor practices} create views for single-purpose queries (aka one time requests)
- {operation} create a view
- {operation} drop a view
- {operation} alter a view
- {operation} select data
- {operation} update data
- unless the view is a simple single table view, it’s difficult to update the underlying data through the view [15]
- {type} inline views
- exist only during the execution of a query [32]
- simplify the development of a one-time query [32]
- allows creating queries in steps
- enables troubleshooting
- can replace inline UDFs
- alternatives
- inline UDFs
- temporary tables
- {type} indexed views
- materialize the data, storing the results of the view in a clustered index on disk [15]
- similar to a covering index
- but with greater control
- can include data from multiple data sources [15]
- no need to include the clustered index keys [15]
- designing an indexed view is more like designing an indexing structure than creating a view [15]
- can cause deadlock when two or more of the participating tables is updated/inserted/deleted from two or more sessions in parallel such that they block each other causing a deadlock scenario [29]
- {type} compatibility views
- allow accessing a subset of the SQL Server 2000 system tables
- don’t contain any metadata related to features added after
- views have the same names as many of the system tables in previous version, as well as the same column names
- ⇒ any code that uses the SQL Server 2000 system tables won’t break [16]
- there’s no guarantee that will be returned exactly the same results as the ones from the corresponding tables in SQL Server 2000 [16]
- accessible from any database
- hidden in the resource database
- e.g. sysobjects, sysindexes, sysusers, sysdatabases
- {type} [SQL Server 2015] catalog views
- general interface to the persisted system metadata
- built on an inheritance model
- ⇒ no need to redefine internally sets of attributes common to many objects
- available over sys schema
- must be included in object’s reference
- some of the names are easy to remember because they are similar to the SQL Server 2000 system table names [16]
- the columns displayed are very different from the columns in the compatibility views
- some metadata appears only in the master database
- keeps track of system-wide data (e.g. databases and logins)
- other metadata is available in every database (e.g. objects and permissions)
- metadata appearing only in the msdb database isn’t available through catalog views but is still available in system tables, in the schema dbo (e.g. backup and restore, replication, Database Maintenance Plans, Integration Services, log shipping, and SQL Server Agent)
- {type} partitioned views
- allow the data in a large table to be split into smaller member tables
- the data is partitioned between the member tables based on ranges of data values in one of the columns [4]
- the data ranges for each member table are defined in a CHECK constraint specified on the partitioning column [4]
- a view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined [4]
- when SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which member table contains the rows [4]
- {type} distributed partition views (DPV)
- local partitioned views
- a single table is horizontally split into multiple tables, usually all have the same structure [30]
- cross database partitioned views
- tables are split among different databases on the same server instance
- distributed (across server or instance) partitioned views
- tables participating in the view reside in different databases which reside ondifferent servers or different instances
- {type} nested views
- views referred by other views [15]
- can lead to an abstraction layer with nested views several layers deep
- too difficult to diagnose and maintain [15]
- {type} updatable view
- view that allows updating the underlying tables
- only one table may be updated
- if the view includes joins, then the UPDATE statement that references the view must change columns in only one table [15]
- typically not a recommended solution for application design
- WITH CHECK OPTION causes the WHERE clause of the view to check the data being inserted or updated through the view in addition to the data being retrieved [15]
- it makes the WHERE clause a two-way restriction [15]
- ⇒ can protect the data from undesired inserts and updates [15]
- ⇒ useful when the view should limit inserts and updates with the same restrictions applied to the WHERE clause [15]
- when CHECK OPTION isn’t use, records inserted in the view that don’t match the WHERE constraints will disappear (aka disappearing rows) [15]
- {type} non-updatable views
- views that don’t allow updating the underlying tables
- {workaround} build an INSTEAD OF trigger that inspects the modified data and then performs a legal UPDATE operation based on that data [15]
- {type} horizontally positioned views.
- used s to enforce row-level security with the help of a WITH CHECK option
- {downside} has a high maintenance cost [15]
- {alternative} row-level security can be designed using user-access tables and stored procedures [15]
- {type} schema-bound views
- the SELECT statement must include the schema name for any referenced objects [15]
- SELECT * (all columns) is not permitted [15]
- {type} subscription views
- a view used to export Master Data Services data to subscribing systems
[4] Microsoft (2013) SQL Server 2000 Documentation
[25] Basit A Masood-Al-Farooq et al (2014) SQL Server 2014 Development Essentials: Design, implement, and deliver a successful database solution with Microsoft SQL Server 2014
DPV - Distributed Partition Views
UDF - User-Defined Function