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;