Dear Group:
I was going to create an index on a view we have, but I am getting the following error: "Cannot create index on view '' because the view is not schema bound."
I Googled this and found that the issue is that we need to create the view using SchemaBinding like the following: "CREATE VIEW [dbo].[..] WITH SCHEMABINDING".
My question is this: If I alter the view to allow for SchemaBinding, does it change anything in the way we call or use this view? I won't need to change any code that touches this, correct? Nothing will start breaking by doing this? Just a little nervous as it is a very heavily used view.
It won't impact anything that references the view (except when you have to drop & recreate it).
You have to drop & recreate schemabound view if altering source table(s): https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15
Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.... Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.
Also take note of the other requirements for indexed views: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15
March 9, 2021 at 1:36 pm
Piling on just a little. It could break things. It completely depends on how you do deployments of changes to the structures in your database. A schema bound view means you must drop the view in order to make underlying table changes. This might affect the behavior of your existing system quite negatively.
I'm not saying don't use schema binding. I am saying it does have an impact, just not directly in the code that's accessing the view.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2021 at 10:02 am
To clarify. Only columns used in view are affected by schemabound. You can still add, delete or modify the other columns.
USE master;
DROP DATABASE IF EXISTS TestDB;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE dbo.t
(
IDINTNOT NULLIDENTITY
CONSTRAINT PK_t PRIMARY KEY,
Col1SMALLINTNOT NULL,
Col2SMALLINTNULL,
Col3INTNOT NULL
);
GO
SET NOCOUNT ON;
GO
INSERT INTO dbo.t (Col1, Col2, Col3) VALUES
(19, 20, 30);
GO 100
CREATE VIEW dbo.vt
WITH SCHEMABINDING
AS
SELECTID,
Col1,
Col2
FROM dbo.t;
GO
CREATE UNIQUE CLUSTERED INDEX cl_vt ON dbo.vt (ID);
CREATE NONCLUSTERED INDEX nc_vt_Col1_Col2 ON dbo.vt (Col1, Col2);
GO
-- OK
ALTER TABLE dbo.t
ADD Col4VARCHAR(10) NOT NULL
CONSTRAINT DF_t_Col4 DEFAULT('A');
GO
-- Error - used in view
ALTER TABLE dbo.t
ALTER COLUMN Col1 INT NOT NULL;
GO
-- OK
ALTER TABLE dbo.t
DROP COLUMN Col3;
GO
SELECT*
FROM dbo.t;
SELECT *
FROM dbo.vt;
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply