Friday, December 2, 2011

0 Something about View

View

View is the virtual table which is defined by the query.

  1. CREATE VIEW vHRdepartment
  2. AS
  3. SELECT  DepartmentID,Name,GroupName,ModifiedDate
  4. FROM HumanResources.Department
  5. WHERE DepartmentID >10

image

The select statement can not include ODER BY clause, unless we specify the TOP or FOR XML. otherwise the CREATE VIEW will fail with

Msg 1033, Level 15, State 1, Procedure vHRdepartment, Line 6
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.

check view definition

  1. sp_helptext 'dbo.vHRdepartment'

image

  1. select * from sys.syscomments where id=OBJECT_ID('dbo.vHRdepartment')

image

  1. SELECT * FROM sys.views
  2.  
  3. SELECT * FROM sys.columns sc   join sys.views sv on sc.object_id = Object_id( sv.name)

image

  1. select * from sys.sql_dependencies

image

  1. select * from sys.sql_modules

image

with check option

  1. USE Tempdb
  2. GO
  3. CREATE TABLE t(c1 int)
  4. GO
  5. CREATE VIEW v
  6. AS
  7. SELECT * FROM t WHERE c1 > 10
  8. GO

When we insert the row does not fit in the view definition. It wont show up in the result set.

  1. INSERT V VALUES(6)
  1. SELECT * FROM v

image

To avoid such row being insert, we can create use with check option on the view.

  1. ALTER VIEW v
  2. AS
  3. SELECT * FROM t
  4. WHERE c1 > 10
  5. WITH CHECK OPTION
  6. GO

Lets try to insert the row

  1. INSERT V VALUES(9)

And we will get the below error

Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

We still can insert the row to the base table. the CHECK OPTION only affect on the view level.

with encryption

  1. USE Tempdb
  2. GO
  3. CREATE TABLE t2(c1 int)
  4. GO
  5. CREATE VIEW v2
  6. with ENCRYPTION
  7. AS
  8. SELECT * FROM t2 WHERE c1 > 10
  9. GO

Once with encryption is enable, we no longer access the view definition.

  1. sp_helptext 'v2'

image

  1. select * from sys.syscomments where id=OBJECT_ID('v2')

image

with SCHEMABINDING

With schemabinding is to prevent modification of base table could cause view become invalid.

  1. USE Tempdb
  2. GO
  3. CREATE TABLE t3(c1 int)
  4. GO
  5. CREATE VIEW v3
  6. AS
  7. SELECT * FROM t3 WHERE c1 > 10
  8. GO

Lets update the base table.

  1. ALTER TABLE t3 ADD C2 INT

The add column will works fine. Lets update the view with schemabinding.

  1. ALTER VIEW v3
  2. WITH SCHEMABINDING
  3. AS
  4. SELECT * FROM t3 WHERE c1 > 10
  5. GO

Above alter will fail because the schemabinding will require we explicit defined what column we want the view to see. can not use *. and the table and view name must use 2 part name. EX: dbo.v3

Msg 1054, Level 15, State 6, Procedure v3, Line 4
Syntax '*' is not allowed in schema-bound objects.

  1. ALTER VIEW dbo.v3
  2. WITH SCHEMABINDING
  3. AS
  4. SELECT c1,c2 FROM dbo.t3 WHERE c1 > 10
  5. GO

Lets try to alter the base table

If the alter does not affect the view. then it is ok to alter.

  1. ALTER TABLE t3 ADD c3 INT

If the alter affect the view, then the alter will fail

  1. Alter table t3 drop c2

Msg 3728, Level 16, State 1, Line 1
'c2' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates