Αφορμή για το συγκεκριμένο άρθρο ήταν μια πρόσφατη επικοινωνία με ένα μέλος του SQLschool.gr που έγραφε σε αυτή:

The WITH SCHEMABINDING option

Στον SQL Server κάθε φορά που χρησιμοποιούμε το WITH SCHEMABINDING option κατά την δημιουργία (CREATE/ALTER) ενός view ή function αυτό που κάνουμε είναι να δένουμε (bind) το object που δημιουργούμε με όλα τα objects(tables/views) που χρησιμοποιούνται σε αυτό.

Αυτό σημαίνει ότι τα χρησιμοποιούμενα objects πλέον δεν μπορούν να υποστούν αλλαγές στο schema τους, δεν μπορούν να γίνουν drop και δεν μπορεί να αλλάξει το database collation στη database που έχει schema bound objects.

Από την άλλη δεν έχω φαινόμενα που φορούν code breaking σε views/functions που επηρεάζουν το business continuity και σε αρκετές περιπτώσεις έχω και performance gain.

Αν και το WITH SCHEMABINDING είναι διαθέσιμο από το SQL Server 2000 η χρήση του είναι ελάχιστη με αποτέλεσμα να δημιουργούνται προβλήματα, τις περισσότερες φορές καταστροφικά.

Αν έχεις μια database που είναι μικρή και μόνο ένας την αλλάζει και αυτός ο ένας είναι 101% τυπικός τότε ενδεχομένως να μην χρειάζεται η χρήση του WITH SCHEMABINGING. Σε οποιαδήποτε άλλη περίπτωση η χρήση της είναι επιβεβλημένη για να μην φτάσουμε σε αυτές τις καταστάσεις που το μέλος του SQLschool.gr γλαφυρά περιγράφει.

Interesting points

Φυσικά στο documentation για view και functions υπάρχουν όλες οι λεπτομέρειες για το option αυτό, όμως θέλω να εστιάσω με παραδείγματα σε κάποια από αυτά.

Για αυτό το σκοπό ας ξεκινήσουμε με μια απλή database και ένα απλό table με τρία rows.

USE master;
GO
CREATE DATABASE learndb;
GO
USE learndb;
GO
CREATE TABLE dbo.T
(
    col1 int IDENTITY PRIMARY KEY,
    col2 int,
    col3 DATETIME2(7) DEFAULT (SYSDATETIME()),
    col4 NVARCHAR(10)
);
GO
INSERT INTO dbo.T(col2,col4)
VALUES (1,'row 1'),(2,'row 2'),(3,'row 3');
GO
SELECT * FROM dbo.T;
GO

Syntax '*' is not allowed in schema-bound objects.

Όταν δημιουργούμε ένα view/function με schemabinding δεν επιτρέπεται η χρήση SELECT * στα objects που δημιουργούνται σε αυτό.
Στο παράδειγμα που ακολουθεί βλέπουμε και επιστρεφόμενο error message.

CREATE VIEW dbo.vT_withschemabinding
WITH SCHEMABINDING
AS
SELECT * FROM dbo.T;
GO

Names must be in two-part format

Όταν δημιουργούμε ένα view/function με schemabinding πρέπει τα objects που εμπεριέχονται σε αυτό να είναι με two part name δηλαδή schema_name.object_name.
Αν δεν ακολουθηθεί αυτό το επιστρεφόμενο error message είναι το παρακάτω.

CREATE VIEW dbo.vT_withschemabinding
WITH SCHEMABINDING
AS
SELECT col1,col3,col4 FROM T;
GO

CREATE VIEW dbo.vT_withschemabinding
WITH SCHEMABINDING
AS
SELECT col1,col3,col4 FROM dbo.T;
GO

Alter Table

Όπως γράφτηκε και παραπάνω όταν χρησιμοποιώ το WITH SCHEMABINDING option τα χρησιμοποιούμενα objects σε αυτό δεν μπορούν να αλλάξουν ή να διαγραφτούν. Αυτό που θέλω να τονίσω είναι ότι στην πραγματικότητα αυτό αφορά τις κολώνες που αναφέρονται στο object που δημιουργούμε, για παράδειγμα.

Στο προηγούμενο παράδειγμα δημιουργήθηκε ένα view το οποίο περιέχει τις κολώνες col1, col3, col4 αυτές είναι που δεν αλλάζουν οι άλλες κολώνες του πίνακα που δεν αναφέρονται σε ένα σε αυτό ή σε κάποιο άλλο object with schemabinding μπορούν να μεταβληθούν.

Στο παράδειγμα που ακολουθεί η col3 είναι μια χρησιμοποιούμενη κολώνα και δεν μπορεί να αλλάξει αλλά η col2 δεν είναι οπότε κάνουμε ότι θέλουμε με αυτή. Επίσης σε παρακάτω πράδειγμα θα δείτε ότι μπορώ να προσθέσω και νέα κολώνα στον πίνακα αυτό.

ALTER TABLE dbo.T
DROP COLUMN col3;
GO

ALTER TABLE dbo.T
DROP COLUMN col2;
GO

Specify the SCHEMABINDING option when you are creating a UDF that does not access data

Είναι πολύ καλή πρακτική κάθε φορά που δημιουργούμε ένα function που δεν χρειάζεται να διαβάσει δεδομένα αλλά απλά κάνει υπολογισμούς να χρησιμοποιούμε σε αυτό το schemabinding option καθώς έτσι ο query optimizer δεν χρειάζεται να εκτελέσει μη απαραίτητους spool operators που θα έκανε για την αποτροπή του Halloween problem που περιγράφει στο άρθρο αυτή η Φήβη (The Halloween Problem - SQLschool.gr).

CREATE FUNCTION dbo.fn_noschemabinding(@p1 INT) RETURNS INT
AS
BEGIN
    DECLARE @rv int = 0;
    SET @rv = YEAR(SYSDATETIME()) + MONTH(SYSDATETIME()) + @p1;
    RETURN @rv;
END
GO

CREATE OR ALTER FUNCTION dbo.fn_withschemabinding(@p1 INT) RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @rv int = 0;
    SET @rv = YEAR(SYSDATETIME()) + MONTH(SYSDATETIME()) + @p1;
    RETURN @rv;
END
GO

ALTER TABLE dbo.T
ADD col5 INT;
GO

Στο παράδειγμα που ακολουθεί δημιουργούμε την ίδια function σε λογική απλά στην δεύτερη γίνεται χρήση του schemabinding option. Επίσης γίνεται προσθήκη μιας ακόμα κολώνας την οποία κάνουμε update χρησιμοποιώντας και τις δύο functions.

UPDATE dbo.T
SET col5 = dbo.fn_noschemabinding(col1);
GO

UPDATE dbo.T
SET col5 = dbo.fn_withschemabinding(col1);
GO

Παρατηρούμε ότι στα execution plans των updates όταν γίνεται χρήση του function with schemabinding option δεν υπάρχει ο eager spool operator που χρησιμοποιεί η πρώτη για την αποφυγή του Halloween.

//Antonios Chatzipavlis