Index on View

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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
    (
    ID INT NOT NULL IDENTITY
    CONSTRAINT PK_t PRIMARY KEY,
    Col1 SMALLINT NOT NULL,
    Col2 SMALLINT NULL,
    Col3 INT NOT 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
    SELECT ID,
    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 Col4 VARCHAR(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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply