Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…
Previously, I wrote an article about SQL SERVER – The Self Join – Inner Join and Outer Join, and that blog post seems very popular because of its interesting points. It is quite common to think that Self Join is also only Inner Join, but the reality is that it can be anything. The concept of Self Join is very useful that we use it quite often in our coding. However, this is not allowed in the Index View. I will be using the same example that I have created earlier for the said article.
Let us first create the same table for an employee. One of the columns in this table contains the ID of the manger, who is an employee of that company, at the same time. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need to use Self Join.
USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO
We will now utilize Inner Join to find the employees and their managers’ details.
-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
Now let us try to create View on the table. This will allow well construction of the View without any issues associated with it.
-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
Now let us try to create a Clustered Index on the View.
-- Attempt to Create Index on View will thrown an error
CREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]
(
[EmployeeName] ASC
)
GO
Unfortunately, the above attempt will not allow you to create the Clustered Index, as evidenced by an error message. It will throw following error suggesting that SELF JOIN is now allowed in the table.
Msg 1947, Level 16, State 1, Line 2
Cannot create index on view “tempdb.dbo.myJoinView”. The view contains a self join on “tempdb.dbo.Employee”.
The generic reason provided is that it is very expensive to manage the view for SQL Server when SELF JOIN is implemented in the query.
If any of you has a better explanation of this subject, please post it here through your comments, and I will publish it with due credit.
The complete script for the example is given below:
USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO
-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
-- Attempt to Create Index on View will thrown an error
CREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]
(
[EmployeeName] ASC
)
GO
/*
Msg 1947, Level 16, State 1, Line 2
Cannot create index on view "tempdb.dbo.myJoinView". The view contains a self join on "tempdb.dbo.Employee".
*/
-- Clean up
DROP VIEW myJoinView
DROP TABLE Employee
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Sir,
Please don’t take it otherwise but we are bored of these limitations. Please change the topic.
Regards,
Amit
Hi, Pinal
I belive – this limitation will be eliminated in next version of SQL Server, probably – with usage of special join hint (self join executed exactly as inner join).
From architectural point – that happens when attempt to implement generic solution for complex case with far functional dependencies.
From logical point it might be solved as following:
– specify join hint to eliminate the cause of limitation
– give query analyzer more smart to use only INNER join if the view is indexed
Imagine you have a view with this syntax:
SELECT InitDateTable.date_id AS init_date_id
,EndDateTable.date_id AS end_date_id
FROM myschema.mytable MyTable
INNER
JOIN myschema.mydatestable InitDateTable
ON InitDateTable.date = MyTable.init_date
INNER
JOIN myschema.mydatestable EndDateTable
ON EndDateTable.date = MyTable.end_date
You can’t create index because it tells you this view contains a self join, but this isn’t true because it is accesing the same table two times for two separate fields. This is not a SELF JOIN but it tells you it is.
Is it any way to do this?
Imagine you have a view with this syntax:
SELECT InitDateTable.date_id AS init_date_id
,EndDateTable.date_id AS end_date_id
FROM myschema.mytable MyTable
INNER
JOIN myschema.mydatestable InitDateTable
ON InitDateTable.date = MyTable.init_date
INNER
JOIN myschema.mydatestable EndDateTable
ON EndDateTable.date = MyTable.end_date
You can’t create index because it tells you this view contains a self join, but this isn’t true because it is accesing the same table two times for two separate fields. This is not a SELF JOIN but it tells you it is.
Is it any way to do this?
Because the joined table column names are same. It cannot create index for duplicate column names. Is my understanding is correct?