Class 7 - Views in SQL Server

VIEW IN SQL SERVER 2000
Views are nothing but saved SQL queries. Suppose you have written a query by using multiple SQL statements to view the data. But it may require that watching the data from these tables whenever required. For this you don’t need to write all these queries every time, instead you can save it as a view. View doesn’t contain any data, it is just a query.

CREATE A VIEW
This example creates a view with a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently.
CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
GO

JOINS :
Views can be written by using multiple tables with help of joins. There are different joins available in SQL Server. As per the requirement we can choose join to use in the queries for view.

INNER
LEFT OUTER
RIGHT OUTER
FULL
CROSS
SELF
CARTISAN

Eg : Create view ABCD_View
AS
Select a.Col1, a.col2, b.col1, b.col2
from Table_A inner join Table_B
on a.Col1 = b.Col2


Some Arguments about Views in Sql Server 2000 : •
You can create views only in the current database. However, the tables and views referenced by the new view can exist in other databases or even other servers if the view is defined using distributed queries.
• A view can reference a maximum of 1,024 columns.
• A View can be used as a security mechanism like we can given permissions for the users also.
• View names must follow the rules for identifiers and must be unique for each user. Additionally, the name must not be the same as any tables owned by that user.
• You can build views on other views and on procedures that reference views. Microsoft® SQL Server™ 2000 allows views to be nested up to 32 levels.
• You cannot associate rules or DEFAULT definitions with views.
• You cannot associate AFTER triggers with views, only INSTEAD OF triggers.
• The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
• You cannot define full-text index definitions on views.
• You cannot create temporary views, and you cannot create views on temporary tables.
• Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
• When a view is created, the name of the view is stored in the sysobjects table.
• If the new table (or view) structure changes, then the view must be dropped and recreated.

View can be createD by using following options for security and performance purposes.

WITH CHECK OPTIONForces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION ensures the data remains visible through the view after the modification is committed.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTIONGO

WITH ENCRYPTIONIndicates that SQL Server encrypts the system table columns containing the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH ENCRYPTION
GO

SCHEMABINDINGBinds the view to the schema. When SCHEMABINDING is specified, the select_statement must include the two-part names (owner.object) of tables, views, or user-defined functions referenced.
Views or tables participating in a view created with the schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, SQL Server raises an error. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.

Use built-in functions within a view : This example shows a view definition that includes a built-in function. When you use functions, the derived column must include a column name in the CREATE VIEW statement.
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO

SQL Server 2005 New Features for improving Performance

TABLE OF CONTENTS

1. SCHEMA BINDING VIEW (Materialized View) :
2. CTE (Common Table Expression) :
3. TABLE PARTITION :
4. OUTPUT Keyword :
5. PIVOT / UNPIVOT :
6. APPLY Operator :
7. RANKING FUNCTIONS :
8. SQL SERVER 2005 INDEX FEATURES :
9. Sp_Executesql



1. SCHEMA BINDING VIEW (Materialized View) :

Schema Binding View is nothing but a view but it will bind the schema like a table. It means the view will hold the data. So we can able to create indexes on that. When index is created on a table automatically it improves the performance.

Benefits of Using Indexed Views :

• It is possible to create a unique clustered index on a view, as well as nonclustered indexes, to improve data access performance on the most complex queries.
• Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
• Tables can be prejoined and the resulting data set stored.
• Combinations of joins or aggregations can be stored.

Considerations : The following considerations should follow while creating SB views ..
• The view, and all tables referenced in the view, must be in the same database and have the same owner.
• The indexed view does not need to contain all the tables referenced in the query to be used by the optimizer.
• A unique clustered index must be created before any other indexes can be created on the view.
• The view must be created using schema binding and any user-defined functions referenced in the view must also be created with the SCHEMABINDING option.
• Additional disk space will be required to hold the data defined by the indexed view.
• DTA (Database Tuning Advisor) tool can be used to findout indexed view requirement. It is a new tool available in SQL 2005 only.

Creating Indexed Views:

The steps required to create an indexed view are critical to the successful implementation of the view.
1. Verify the setting of ANSI_NULLS is correct for all existing tables that will be referenced in the view.
2. Verify ANSI_NULLS is set correctly for the current session as shown in the table below before creating any new tables.
3. Verify ANSI_NULLS and QUOTED_IDENTIFIER are set correctly for the current session as shown in the table below before creating the view.
4. Verify the view definition is deterministic.
5. Create the view using the WITH SCHEMABINDING option.
6. Verify your session's SET options are set correctly as shown in the table below before creating the unique clustered index on the view.
7. Create the unique clustered index on the view.
8. The OBJECTPROPERTY function can be used to check the value of ANSI_NULLS and QUOTED_IDENTIFIER on an existing table or view.

Note The indexed view may contain float and real columns; however, such columns cannot be included in the clustered index key if they are non-persisted computed columns.
GROUP BY Restrictions
If GROUP BY is present, the VIEW definition:
• Must contain COUNT_BIG(*).
• Must not contain HAVING, CUBE, ROLLUP, or GROUPING()
These restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions


Example :

CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty) AS SumPrice,
SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,
COUNT_BIG(*) AS Count, ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID

GO

CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

Go

Can create many non clustered indexes

2. CTE (Common Table Expression) :
A named temporary result set based on a SELECT query. By mentioning WITH CTE Name we can write the query and the result set can be used again in the same query. The main purpose of CTE is we can avoid temporary table.

Advantages :
• Result set can be used in SELECT, INSERT, UPDATE, or DELETE
• Queries with derived tables become more readable
• Provide traversal of recursive hierarchies

Example :

WITH TopSales (SalesPersonID, NumSales) AS
(SELECT SalesPersonID, Count(*)
FROM Sales.SalesOrderHeader GROUP BY SalesPersonId)

SELECT LoginID, NumSales
FROM HumanResources.Employee e INNER JOIN TopSales
ON TopSales.SalesPersonID = e.EmployeeID
ORDER BY NumSales DESC



3. TABLE PARTITION :
Partition Tables
Vertical Table Partitioning :
You can use vertical table partitioning to move infrequently used columns into another table. Moving the infrequently used columns makes the main table narrower and allows more rows to fit on a page.

Horizontal Table Partitioning :
Horizontal table partitioning is a bit more complicated. But when tables that use horizontal table partitioning are designed correctly, you may obtain huge scalability gains. One of the most common scenarios for horizontal table partitioning is to support history or archive databases where partitions can be easily delineated by date. A simple method that you can use to view the data is to use partitioned views in conjunction with check constraints.

How to partition a table using Horizontal Partitioning :
Step 1: Create the partition function
CREATE PARTITION FUNCTION emailPF (nvarchar(30))
AS RANGE RIGHT FOR VALUES ('G', 'N')

Step 2: Create the partition scheme
CREATE PARTITION SCHEME emailPS
AS PARTITION emailPF TO (fg1, fg2, fg3)

Step 3 : Create the partitioned table
CREATE TABLE Sales.CustomerEmail
(CustID int, email nvarchar(30))
ON EMailPS (email)

* fg1,fg2,fg3 are filegroups located in different drives.



4. OUTPUT Keyword :

With help of Output keyword we can avoid multiple declarations and assigning the values. While performing INSERT/Update we can take the value into variable and it can be used in further queries.

Example :

DECLARE @InsertDetails TABLE
(ProductModelID int,
InsertedBy sysname)

INSERT INTO Production.ProductModel(Name, ModifiedDate)
OUTPUT inserted.ProductModel ID, suser_name()
INTO @InsertDetails
VALUES
('Racing Bike', getdate())


SELECT * FROM @InsertDetails


5. PIVOT / UNPIVOT :

PIVOT – converts values to columns :

Cust Prod Qty
Mike Bike 3
Mike Chain 2
Mike Bike 5
Lisa Bike 3
Lisa Chain 3
Lisa Chain 4

SELECT * FROM Sales.Order
PIVOT (SUM(Qty) FOR Prod IN ([Bike],[Chain])) PVT

Cust Bike Chain
Mike 8 2
Lisa 3 7


UNPIVOT – converts columns to values :

Cust Bike Chain
Mike 8 2
Lisa 3 7

SELECT Cust, Prod, Qty
FROM Sales.PivotedOrder
UNPIVOT (Qty FOR Prod IN ([Bike],[Chain])) UnPVT

Cust Prod Qty
Mike Bike 8
Mike Chain 2
Lisa Bike 3
Lisa Chain 7


6. APPLY Operator :

With help of APPLY operator we can join a function with table and get the result. In SQL 2000 then function result need to be inserted in a table, then only we can make a join from that. But using APPLY operator directly joining with table we can get the output.

Advantages : Invokes a table-valued function once per row
 CROSS APPLY – only rows with matching function results
 OUTER APPLY – all rows, regardless of matching function results


Example :

CREATE FUNCTION Sales.MostRecentOrders
(@CustID AS int) RETURNS TABLE AS
RETURN
SELECT TOP(3) SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustID
ORDER BY OrderDate DESC


SELECT Name AS Customer, MR.*
FROM Sales.Store
CROSS APPLY Sales.MostRecentOrders(CustomerID) AS MR

7. RANKING FUNCTIONS :

Function Description
RANK Returns a rank for each row within a specified partition in a result set
DENSE_RANK Returns a consecutive rank for each row within a specified partition in a result set
ROW_NUMBER Returns the ordinal row position of each row in a grouping within a result set
NTILE Divides the rows in each partition of a result set into a specified number of ranks based on a given value.

With help of ranking functions we can find the data sequentially, like we can avoid top and other keywords.
8. SQL SERVER 2005 INDEX FEATURES :

ONLINE INDEXING :
The online index option allows concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language (DDL) execution. For example, while a clustered index is being rebuilt, you can continue to make updates to the underlying data and perform queries against the data.
Eg :
CREATE CLUSTERED INDEX [PK_Employee_EmployeeID] ON [HumanResources].[Employee] ([EmployeeID] ASC)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON) ON [PRIMARY]

ALTER INDEX ALL on HumanResources.Employee REBUILD WITH (ONLINE=ON)

INCLUDE CLAUSE :
With help of include clause, by including non-key columns we can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size. Non-key columns can be included in non-clustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. INCLUDE clause to CREATE INDEX for nonkey columns – stored in leaf nodes of the index

Eg :

create unique index XUpersonInterest_firstName_inclInterest
on personInterest(firstName) include (interest)

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)


ALTER INDEX :
Disabling
ALTER INDEX IX_Customer_TerritoryID ON Sales.Customer DISABLE

Rebuilding
ALTER INDEX PK_Customer_CustomerID ON Sales.Customer REBUILD

Reorganizing
ALTER INDEX PK_Customer_CustomerID ON Sales.Customer REORGANIZE

Setting of options
ALTER INDEX PK_Customer_CustomerId ON Sales.Customer SET(...)



Index related considerations for improving performance :
• Create indexes based on use :
Do not create indexes if a table is rarely queried, or if a table does not ever seem to be used by the optimizer. Avoid indexes on bit, text, ntext, or image data types because they are rarely used. Avoid very wide indexes and indexes that are not selective.
• Keep clustered index keys as small as possible.
• Consider range data for clustered indexes.
• Create an index on all foreign keys.
• Create highly selective indexes.
Create indexes that exhibit high selectivity. In other words, create indexes that have many distinct values. For example, an index on a region column may have a small number of distinct values. Therefore, there may not be enough distinct values for the optimizer to use. Another example of an item that may not have enough distinct values is a bit column. Since there are only two values, an index cannot be very selective and as a result, the index may not be used.
• Consider a covering index for often-used, high-impact queries.
• Use multiple narrow indexes rather than a few wide indexes.
• Create composite indexes with the most restrictive column first.
• Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
• Remove unused indexes.
• Use the Index Tuning Wizard to tune the indexes.
• Keep statistics up to date

9. sp_executesql :

This system procedure can be used to execute dynamic sql queries instead of using EXEC keyword. Compare with EXEC keyword it will give the much performance.

Example :

DECLARE @SQLString NVARCHAR(500);

--Set column list. CHAR(13) is a carriage return, line feed
SET @SQLString = N'SELECT FirstName, LastName, JobTitle' + CHAR(13);

-- Set FROM clause with carriage return, line feed.
SET @SQLString = @SQLString + N'FROM HumanResources.vEmployee' + CHAR(13);

--Set WHERE clause.
SET @SQLString = @SQLString + N'WHERE LastName LIKE ''D%''';


EXEC sp_executesql @SQLString;


* The declared data type should be ntext/nchar/nvarchar