Wednesday, July 14, 2010

Views in SQL server

Views in SQL server:

View in DBMS system can be considered as a virtual table. Just like table view is made up of rows & columns. The term virtual specifies that the view doesn't actually store information itself, but just pulls it out of one or more underlying tables.
E.g. we create view “USER_CONTACT_DETAILS” as follows

CREATE VIEW USER_CONTACT_DETAILS AS
SELECT
FNAME,
LNAME,
ADDRESS1,
ADDRESS2,
PHONE1 AS LANDLINE,
PHONE2 AS CELLPHONE,
CITY,
STATE,
COUNTRY,
ZIP_CODE AS PIN
FROM
USER_DETAILS
WHERE
UPPER(CITY)=”MUMBAI”


When you issue statement
SELECT * FROM USER_CONTACT_DETAILS
Each time actual records are pulled from USER_DETAILS table but using logic that we specified while creating that view like we just wanted only specific columns of USER_DETAILS table which should be filtered using condition specified in where clause. The only thing that is stored in the database is the SELECT statement that forms the virtual table (with one exception, indexed views)













WHY TO USE VIEW IF I CAN QUERY DATABASE TABLES?

Advantages of view:
Views can provide advantages over tables:
1) Limiting the Exposure of Table: By creating view on table(s) and mentioning only columns specific to requirement of user and exposing view name to him & access to it but restrict him from seeing corresponding table or modifying view. This let us to actual hide what is schema of table(s) used to create this view, on which server it resides. This way we can mask other sensitive information from exposing to un-necessary exposure.
2) Merging Data: A Partition View let us to merge data from multiple tables in multiple databases, so it appears as one table only, but it is in fact several tables. This can be accomplished by using the UNION operator. For example if we had customers in India and USA, we could have one server for India, and one for USA, with tables that are identical, and then merge all data in a partitioned view. More on this later.
3) Import and export data:
A view can also be used to export and/or import data to/from another application. the bcp utility and BULK INSERT works with a view.
4) Emulate Backward Compatibility: To provide a backward compatible interface to emulate a table whose schema has changed.
5) Convenience: Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents but it saves us from creating those complex queries (if any) again and again.
Syntax of creating view:
Syntax bellow copied from MSDN online

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]
<view_attribute> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ]
}







Restriction on underlying Select Statement of view:
The SELECT clauses in a view definition cannot include the following:
• COMPUTE or COMPUTE BY clauses
• An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
Note: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.
• The INTO keyword
• The OPTION clause
• A reference to a temporary table or a table variable.
Who has Permission to create View:One must be a member of the fixed database roles db_owner ,db_ddladmin or sysadmin server role, though member of db_owner database role can give others permission to do so
If you are the db_owner you want to give such permission to your colleague say
“Sangram2681” need to run following command
GRANT CREATE VIEW TO [sangram2681]

Where the schema is stored?
When we create view the actual data is not stored but the query that wil generate the data this query is stored in Syscomments system table.

Let’s Learn By Doing :
Supposing you have a pubs database (if not you can get it’s .ldf & .mdf file by googling out!)With respect to this database

Run following queries








1) Use pubs;

2) create view store_sale
as
select
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip,
count(*)total_order
from
stores st
inner join
sales sl
on st.stor_id=sl.stor_id
group by
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip

Trying out order by in underlying select:

When we try to run following query we get an error.
alter view store_sale
as
select
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip,
count(*)total_order
from
stores st
inner join
sales sl
on st.stor_id=sl.stor_id
group by
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
order by st.stor_name
What is that error ?: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Then when can we use that holy phrase order by in create view?
See following query here we used order by but that was with TOP n clause so that passed compilation.

alter view store_sale
as
select
top 5
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip,
count(*)total_order
from
stores st
inner join
sales sl
on st.stor_id=sl.stor_id
group by
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
order by st.stor_name

Suppose someone has created a view, you want to know actual schema behind it we need to run a inbuilt stored procedure bellow (if you have the permission)

EXEC sp_helptext store_sale


On which object our view depends ?
sp_depends stored procedure gives us information about what other objects our view depends on.
EXEC sp_depends store_sale
Encrypting View: when we create view the query used to create it is stored in Syscomments table, bydefault that query is not encrypted but saved as plane text
But you can ask SQL server to do so by using with ENCRYPTION clause in create view query.
The query to do so is bellow

alter view store_sale
with ENCRYPTION
as
select
top 5
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip,
count(*)total_order
from
stores st
inner join
sales sl
on st.stor_id=sl.stor_id
group by
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
order by st.stor_name

View with check option:
Consider a view created by following query

create view store_with_check
as
select
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
from
stores st
where
state='CA'

This view only displays records in which state is ‘CA’.now if we try to insert into this view a record in which state is ‘WA’ does it give an error

Lets try out following query.

insert into store_with_check values(6381,'The crossword','676 osama mansion','Seattle','WA','98056')



this query actually insert record into table can be verified using
following query.
select
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
from
stores st


if this isn’t desirable we have to modify our original create view state
as follows here we use with check option that enforces that doesn’t
violate the condition in where clause of create view during insert into view.

Following query illustrate it

alter view store_with_check
as
select
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
from
stores st
where
state='CA'
with check option

now try to insert the record into view which has state not ‘CA’

insert into store_with_check values(6382,'The crossword','676 osama mansion','Seattle','WA','98056')

this query doesn’t run gives that we are violating with check option.

Need to Specify Each column name:
Consider following query

alter view store_sale
as
select
top 5
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip,
count(*)
from
stores st
inner join
sales sl
on st.stor_id=sl.stor_id
group by
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
order by st.stor_name

The query is all right the only thing is that we are using a aggregate function count () the column generated column due to it has name so our query gives an error

Now just add that it will work fine.

alter view store_sale
as
select
top 5
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip,
count(*) total_orders
from
stores st
inner join
sales sl
on st.stor_id=sl.stor_id
group by
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
order by st.stor_name

Thinking of adversity:
Now lets find out if we deleted one column say zip( code) from stores table but
We are using the zip column in our view store_sale so what will happen to our view store_sale.
No it isn’t work as it’s defination assume that stores table has zip column but now it isn’t there.

How to tackle the problem ?
To tackle this problem sql server deviced a way where you can’t modify a table if modification breaks your view(s) builds on it.
If you still want to go ahead just alter/drop those view then modify your table. Here we need to specify with schemabinding option
The following query illustrate


alter view store_sale
with SCHEMABINDING
as
select
top 5
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip,
count(*) total_orders
from
dbo.stores st
inner join
dbo.sales sl
on st.stor_id=sl.stor_id
group by
st.stor_id,
st.stor_name,
st.stor_address,
st.city,
st.state,
st.zip
order by st.stor_name

note here when we put schemabinding clause we have to make sure that table_name are like dbo.table_name else we get un-desirable error

what was that error? Names must be in two-part format and an object cannot reference itself.

With View_Metadata what is this key word for ?
If WITH VIEW_METADATA is specified, SQL Server will return the metadata information about the view, instead of the underlying tables to the DBLIB, ODBC and OLE DB APIs, when metadata is being requested. Metadata is information about the view’s properties, for example column names or type. If WITH VIEW_METADATA is used, the client application is able to create an updateable client side cursor, based on the view.
What are Partitioned views?
A partitioned view uses the UNION ALL operator to merge all member tables (which must be structured in the same way). The tables can be stored in the same SQL Server, or in multiple SQL Servers. It must however be clear what data belongs to each partition, by using CHECK constraints and data cannot overlap (so you cannot have one table with customers ID from 1 to 50, and another table with customer ID from 25 to 75).
For example, say that we have three different kinds of customers, those from Sweden, those from UK, and those from USA. The number of rows in the customers table is increasing very fast, so we would like to split the existing table into three other tables, on three different servers (in my example, we will do this on one server only, so as many of you as possible can try it). We then create a view on each server, to access information about all customers. But, this is very smart, if we later use the view to access data from two servers (because the WHERE clause limits it), it will not transfer data from the third server.
Before creating the tables, it is a good idea to mention the rules for a so called partitioning column (the column(s) that makes it impossible to overlap data).
• It is not a calculated, identity, default or timestamp column
• It cannot have the value NULL
• It is part of the primary key
• It should be validated by a CHECK constraint, but cannot be validated using these operators <> and !
• Only one CHECK constraint exists on the partitioning column.
There are also some rules the tables need to follow:
• The primary key should be defined on the same columns
• The table cannot have indexes created on computed columns
• All tables should have the same ANSI padding setting.
-- creating a new table from existing using into

SELECT * INTO CustomersSweden
FROM Customers
WHERE Country = 'Sweden'
GO

-- modifying country column datatype
ALTER TABLE CustomersSweden ALTER COLUMN
Country NVARCHAR(15) NOT NULL
GO
-- adding composite primary key on CustomerID, Country
ALTER TABLE CustomersSweden ADD
CONSTRAINT PK_CustomersSweden PRIMARY KEY (CustomerID, Country),
CONSTRAINT CK_CustSweden_Country CHECK (Country IN ('Sweden'))
GO

SELECT * INTO CustomersUK
FROM Customers
WHERE Country = 'UK'
GO
ALTER TABLE CustomersUK ALTER COLUMN
Country NVARCHAR(15) NOT NULL
GO
ALTER TABLE CustomersUK ADD
CONSTRAINT PK_CustomersUK PRIMARY KEY (CustomerID, Country),
CONSTRAINT CK_CustUK_Country CHECK (Country IN ('UK'))
GO

SELECT * INTO CustomersUSA
FROM Customers
WHERE Country = 'USA'
GO
ALTER TABLE CustomersUSA ALTER COLUMN
Country NVARCHAR(15) NOT NULL
GO
ALTER TABLE CustomersUSA ADD
CONSTRAINT PK_CustomersUSA PRIMARY KEY (CustomerID, Country),
CONSTRAINT CK_CustUSA_Country CHECK (Country IN ('USA'))
GO
This script creates three different tables (CustomersSweden, CustomersUK and CustomersUSA), all based on the Customers table in the Northwind database. But as you remember, a partitioning column cannot be NULL, so we have to alter the column Country so it does not accept NULL values. We also have to add a PRIMARY KEY constraint, and a check constraint.
After we have created the tables, we can create the partitioned view:
CREATE VIEW vwCustomers
AS
SELECT * FROM CustomersSweden
UNION ALL
SELECT * FROM CustomersUK
UNION ALL
SELECT * FROM CustomersUSA
GO
If we now select all data from this view, it looks like it’s one table, with customers from USA, Sweden and UK.
SELECT * FROM vwCustomers
Output:
CustomerID CompanyName . . . Country . . .
BERGS Berglunds snabbköp . . . Sweden . . .
FOLKO Folk och fä HB . . . Sweden . . .
AROUT Around the Horn . . . UK . . .
. . . . . . . . . . . . . . .
But, what happens if we insert data using this view?
INSERT INTO vwCustomers
VALUES
(
'SHOPS',
'Shop Shop Shop',
'John Doe',
'Owner',
'111 East 12th',
'New York',
'NY',
'94117',
'USA',
'(415) 555-1234',
NULL
)
Now try this, and see where you find John Doe.
SELECT * FROM CustomersSweden
SELECT * FROM CustomersUK
SELECT * FROM CustomersUSA
You also found him in the table CustomersUSA? And we do not even have to know that this table exists! He ends up there because that is the only table he will not violate the CHECK constraint in.
Say that John Doe moves to UK, but everything else is the same! (because we are lazy :-)
UPDATE vwCustomers
SET Country = 'UK'
WHERE CustomerID = 'SHOPS'
Note: Didn’t work? Only the Developer and Enterprise Edition of SQL Server 2000 allow INSERT, UPDATE and DELETE operations on partitioned views.
In which table do you think it’s stored this time? Yep, that is correct, in the CustomersUK table.
But, as always, there are some rules about updating data through a partitioned view.
• The INSERT statement must supply values for all columns in the view. It doesn’t matter if the underlying tables have a DEFAULT constraint, or allow NULLs.
• It must satisfy at least one of the underlying constraints (we cannot insert a customer from France in the example above).
• The DEFAULT keyword does not work in INSERT and UPDATE statements.
• Columns that are IDENTITY columns in one or more member tables cannot be modified.
• Data cannot be modified if one of the tables contains a timestamp column.
• If there is a self-join with the same view or with any of the member tables in the statement, INSERT, UPDATE and DELETE do not work.
Indexed views
What are SQL Server 2000 Indexed views?
Views in SQL Server 2000 are very similar to those in previous versions with a few major exceptions when using Indexed views. When a clustered index is created on the view, SQL Server immediately allocates storage space to store the results of the view. You can then treat the view like any other table by adding additional nonclustered indexes.

What are the requirements for Indexed views?
There are several requirements that you must take into consideration when using Indexed views.
1. View definition must always return the same results from the same underlying data.
2. Views cannot use non-deterministic functions.
3. The first index on a View must be a clustered, UNIQUE index.
4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.
5. View definition cannot contain the following
a. TOP
b. Text, ntext or image columns
c. DISTINCT
d. MIN, MAX, COUNT, STDEV, VARIANCE, AVG
e. SUM on a nullable expression
f. A derived table
g. Rowset function
h. Another view
i. UNION
j. Subqueries, outer joins, self joins
k. Full-text predicates like CONTAIN or FREETEXT
l. COMPUTE or COMPUTE BY
m. Cannot include order by in view definition

Notice that Indexed Views change the very essence of what a view was before this version of Sql Server. First, the data represented by the view is actually stored in the database. Secondly, the view definition must always return the same results for the same underlying data and all functions and expressions must be deterministic no matter what the current session settings.

To make sure that you can meet this requirement, the following session options must be set when you create an index view, when you modify any of the tables included in an indexed view or when the optimizer decides to use the indexed view as part of a query plan. Session Options that must be on
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YEILDS_NULL
QUOTED_IDENTIFIERS
Session options that must be off
NUMERIC_ROUNDABORT
Functions like GetDate(), rand() are non-deterministic because of different session settings can return different values and the settings for one user may not be the same as for another. The list of deterministic and non-deterministic functions will be included in the final version of Books Online for SQL Server 2000. (Look for topic Deterministic and Nondeterministic Functions in the Books Online)

Besides these restrictions, the underlying tables that make up the view must be protected from schema changes. Part of the syntax of the create view command is the “with SCHEMABINDING” phrase. This is required to create a View Index and this will prevent the dropping or altering of tables participating in an Indexed View. Note that dropping the clustered index of an Indexed View will return it to the standard SQL view as it was as described above in the section Before SQL Server 2000, what was a View?

How do I create an Indexed View?
1. Make sure that session properties are properly set.
2. Create a deterministic view with new SCHEMABINDING syntax.
3. Create unique Clustered Index.
4. Optionally, create additional nonclustered indexes.
Below you will find the code that you can paste into the Sql Server Query Analyzer to test this yourself. This example is based on the Northwind sample database.

-- Use the northwind database
USE NORTHWIND
GO

-- Make sure that all of the session settings are set properly
IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
go

-- Create the view, it must comply with the rules (deterministic)
CREATE VIEW PRODUCTS_BY_CUSTOMER WITH SCHEMABINDING AS
select customers.companyname,
products.productname,
sum(odetail.unitprice*odetail.quantity) as TotalPurchase,
count_big(*) as cnt
from dbo."order details" as odetail
inner join dbo.orders as omain
on omain.orderid = odetail.orderid
INNER join dbo.customers as customers
on customers.customerid = omain.customerid
INNER join dbo.products as products
on products.productid = odetail.productid
group by
customers.companyname,
products.productname
go

-- the following statement will cause an error if the view has not been
-- indexed
--EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'
--Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
--Views do not have space allocated.

-- Check to see if the indexes can be created
if ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1
BEGIN
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX PRODUCTS_BY_CUSTOMER_UNIQUE ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME, PRODUCTNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_1 ON
PRODUCTS_BY_CUSTOMER(COMPANYNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

-- Create NonClustered Indexes
CREATE INDEX PRODUCTS_BY_CUSTOMER_2 ON
PRODUCTS_BY_CUSTOMER(PRODUCTNAME)

EXEC SP_SPACEUSED 'PRODUCTS_BY_CUSTOMER'

END

Please note the ObjectProperty(object_id('products_by_customer'),'IsIndexable') = 1 in the above code listing. This command will tell you if all of the requirements for indexing a view have been met so that you can programmatically determine if a view can be indexed or not.

Also note that no space is allocated in the database for this view until the clustered index is created. If you try to use the SP_SPACEUSED stored procedure on a view that is not indexed, you get an error. The results of the SP_SPACEUSED commands that are sprinkled throughout the above code listing gives the following results on my test machine.

# of Rows Data Index Total Used
After Clustered Index Created 1685 168 kb 16 kb 184 kb
After NonClustered Index 1 1685 168 kb 168 kb 336 kb
After NonClustered Index 2 1685 168 kb 320 kb 488 kb

How do I use the Indexed View?
You can use the view like you would any other view. Also, the SQL Server query optimizer will attempt to use a View Index even if the view is not referenced in the from clause, although you can override this behavior with the Expand Views hint.

From the sample created in the above code example, you could use the view as follows

Example 1: select * from products_by_customer

Example 1 above lets the query optimizer determine whether or not to use the view and its indexes or to use the base tables. Surprising on my test machine, this example uses the base tables, not the Indexed View. The query optimizer is a complex piece of technology but it isn’t always perfect. Based on my testing with this sample data in the Northwind database, I had to use the (noexpand) hint seen in the next example to force the optimizer to use the View Index. The speed of this on my test machine was about 3 times faster with 1685 records. By increasing the number of records in the base tables (orders 3000 records and order details 224,696 records), I found that the query optimizer did use the View Index without specifying the hint and the resulting query speeds where approximately 50 times faster. The # of records in the view, after adding all of these records in the base tables, was 1880 records. I conclude that the query optimizer with a small number of records in the base table (Orders had about 830 and order details had about 2155 records when I started) lean towards using the base tables instead of the View index. More testing would be needed to nail down the break even point but this just points out why the hints are still around and how much faster performance can be when the View Indexes are used.

Example 2: select * from products_by_customer with (noexpand)

Example 2 uses a hint to force the query optimizer to consider only the view and its indexes in the execution plan.

Example 3: select * from products_by_customer option (Expand Views)

Example 3 uses a hint to force the query optimizer to expand all indexed views into their underlying Select statements so the optimizer won’t consider any View Indexes in the execution plan.
When would I want to use a View Index?
If you have an application that is a Data-Mart, Data-Mining or decision-support type application, you can improve performance with View Indexes. Applications that do any of the following may benefit as well:
• Joins and aggregations of big tables
• Repeated patterns of queries
• Repeated aggregations on the same or overlapping sets of columns
• Repeated joins of the same tables on the same keys
Also, situations where you might consider de-normalizing a set of tables by storing aggregate information in the parent table may be good situations to consider creating an aggregate view on the child table and creating the appropriate View Index. In essence, the Indexed View replaces your de-normalized fields and all the work of keeping this de-normalized aggregate field up to date is taken care of by the database engine.
When would I NOT want to use a View Index?
You obviously cannot use a View Index if you need to include syntax in the view definition that is not allowed. It seems to me that Top, Min, Max, Count, using another view, union, subqueiries and outer joins are serious restrictions that would disqualify a large number of views that I might want to optimize using the View Index.

Storage may also be a major consideration as the data in the view is physically and permanently stored not only in its base table, but in the clustered index of the View Index. Effectively, the data is stored twice, once in its base table and once in the clustered index of the Indexed View.

Also, On-Line Transaction Processing systems (OLTP) will actually suffer performance loss if you try to use View Indexes. Databases with frequent inserts, updates, deletes suffer as these commands must not only process the table, but any associated Indexed Views that the base table is participating in. Also, views that are simply subsets of rows or columns with no aggregation or computation provide no benefit over a standard SQL view or a T-SQL command directly against the base tables. This additional overhead to update the data in the clustered index of the view I believe is the reason that the clustered index must be unique for an Indexed view. It uses this unique value to quickly update the appropriate record in the Indexed View. Without a unique index, the processing for updating the Indexed View records could be unacceptably long.
What are the performance benefits?
As I indicated earlier, I experienced query times 3 times quicker using the Indexed Views over the same query not using the Indexed Views on the sample data in the NorthWind database. With a much bigger data set and with the same database objects defined, I got query times as much as 50 times faster. Microsoft has reported performance improvements of 10 to 100 times with applications that access indexed views instead of base tables. I also experimented with using other queries that did not directly reference the Indexed View and got similar performance gains when the optimizer selected the Indexed View over other possible execution plans.

References:
1) “Indexed Views in SQL Server 2000” By Doug Carpenter at http://www.sqlteam.com/article/indexed-views-in-sql-server-2000
2) MSDN online
3) Other useful material on WIKI & Internet

No comments:

Post a Comment