Sunday, April 12, 2015

Created Indexed View in T-SQL

Wrong Example:

create view Employees_View as select * from [dbo].[Employees]
GO
create index Employees_View_Idx on Employees_View(LastName,First_name);
GO

What’s are possible errors for above 2 T-SQL Statements


Msg 1939, Level 16, State 1, Line 5
Cannot create index on view 'Employees_View' because the view is not schema bound.


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


Msg 1940, Level 16, State 1, Line 36
Cannot create index on view 'Employees_View'. It does not have a unique clustered index.


Msg 1942, Level 16, State 1, Line 38
Cannot create index on view 'NORTHWND2.dbo.Employees_View'. It contains text, ntext, image, FILESTREAM or xml columns.

Correct Example:


drop view Employees_View
GO
create view Employees_View with schemabinding
as select [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[TitleOfCourtesy]
      ,[BirthDate]
      ,[HireDate]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[HomePhone]
      ,[Extension]
      ,[ReportsTo]
      ,[PhotoPath] from [dbo].[Employees]
GO

create unique clustered index Employees_View_Idx on Employees_View(LastName,Firstname);
GO