Views or functions cannot reference themselves directly or indirectly

Today I received the following SQL Server error which I had never encountered before.

Msg 4429, Level 16, State 1, Line 1
View or function ‘Table_1’ contains a self-reference. Views or functions cannot reference themselves directly or indirectly.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function ‘Test.Table_1’ because of binding errors.

Here’s a quick walk-through of the issue.

CREATE TABLE [dbo].[Table_1]
(
	[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[test] [nchar](10) NOT NULL
);

Insert some test data

INSERT INTO dbo.Table_1
(
	test
)
SELECT 'One'
UNION ALL
SELECT 'Two'
UNION ALL
SELECT 'Three'
UNION ALL
SELECT 'Four'
UNION ALL
SELECT 'Five';

Add a schema called ‘Test’ to the database by running the TSQL below. We will then create a view in this schema.

CREATE SCHEMA Test;

Now create this view.

CREATE VIEW Test.Table_1
AS
	SELECT Id, Test
	FROM Table_1;

Now try to select from this view.

 

I’d been creating lots of views for a third party application we’ve started to deploy at work. I’d simply created a new schema and named the views after the corresponding table in the database. This one was my fault for being a copy and paste monkey! I’d forgotten to specify the schema in one view. The fix?

ALTER VIEW Test.Table_1
AS
	SELECT Id, Test
	FROM dbo.Table_1;

Even though my user default schema was set to dbo SQL Server got its pants in a twist over this. So there you have it; it’s definitely good practice to specify schemas in your TSQL!