What is SCHEMABINDING?

SCHEMABINDING with a Query...

***Table***

CREATE TABLE Sampletbl ( col1 DATETIME ) 

***Data***

INSERT  INTO Sampletbl
VALUES  ( GETDATE() + 1 )

***Function***

CREATE FUNCTION SampleFunction ( )
RETURNS DATETIME
    WITH SCHEMABINDING
AS 
    BEGIN 

        DECLARE @t DATETIME

        SELECT  @t = col1
        FROM    dbo.Sampletbl

        RETURN @t

    END

***Testing***

SELECT  dbo.SampleFunction()

DROP TABLE Sampletbl

ALTER TABLE Sampletbl ADD col2 VARCHAR(100)

When you run the SQL provided above, the DROP TABLE Sampletbl will fail with a SCHEMABINDING error. In order to drop the table, the function needs to be dropped first.The beauty of schemabinding in a large environment with lots of views, functions, and stored procs, is that it helps ensure that when you make changes to an object, you are forced to consider all dependent objects at the same time, rather than miss something and get burned later.