About Views in SQL Server
Views are virtual tables that represent the result set of a select statement from one or more tables or other views. In most cases, that is unless a view is indexed, a view is basically a predefined query that is stored and executed whenever the view is referenced in a query.
Views are of 3 types:
Distributed partitioned views are those that reference data across multiple servers and combine the data to the user as a single result set.
Syntax:
CREATE VIEW vDistributedSample
AS
SELECT col1, col2, col3 FROM Server1.DBName.dbo.TableName
UNION ALL
SELECT col1, col2, col3 FROM Server2. DBName.dbo.TableName
UNION ALL
SELECT col1, col2, col3 FROM Server3.DBName.dbo.TableName
You can use a view to insert, update, and delete data in the underlying tables as long as certain conditions are met:
All the columns being modified must be in the same base table.
The columns must also directly reference the base table; you cannot modify computed columns or columns that are derived from or affected by aggregate functions.
If the WITH CHECK option is specified, as shown in Listing 5, the view cannot be updated in any way that would cause the updated record to disappear from the result set.
Syntax:
USE AdventureWorks2008
GO
--Drop the view if it currently exists
IF OBJECT_ID('dbo.vMarketingEmployees', 'V') IS NOT NULL
DROP VIEW dbo.vMarketingEmployees;
GO
--Create a view using the WITH CHECK option
CREATE VIEW vMarketingEmployees
AS
SELECT dbo.Employee.EmpFirstName AS FirstName,
dbo.Employee.EmpLastName AS LastName,
dbo.Department.DepartmentID,
dbo.Department.DepartmentDscr AS Department
FROM dbo.Department INNER JOIN
dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
WHERE dbo.Department.DepartmentID = 1
WITH CHECK OPTION
3. Indexed Views
If you have a process-intensive view that is run often, you can improve performance and reduce the processing time needed to execute the view by creating an index on the view.
The first index you create on a view must be a unique clustered index, which causes the result set of the view to be stored in the database. Thereafter, you can create nonclustered indexes.
Syntax:
USE AdventureWorks2008
GO
CREATE VIEW vEmployees
WITH SCHEMABINDING
AS
SELECT dbo.Employee.EmpFirstName AS FirstName,
dbo.Employee.EmpLastName AS LastName,
dbo.Department.DepartmentID,
dbo.Department.DepartmentDscr AS Department
FROM dbo.Department INNER JOIN
dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID
GO
--Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_vEmployee_Dept
ON vEmployees (DepartmentID);
GO
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials