go backarticles

Articles of SQLschool.gr Team

The WITH SCHEMABINDING option: What you are missing by not using it

Antonios Chatzipavlis

Overview

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

Δάσκαλε τα φώτα σου θέλω, γιατί θα κλάψω σε λίγο.
Αφού έκατσα και έγραψα ένα κατεβατό να κάνω ελέγχους από εδώ και από εκεί στα references που λείπουν και από αυτά που δε λείπουν έχω κολλήσει στο εξής πετυχημένο:
Missing objects OK;
Ελα όμως που μερικές views (και πιθανό και SPs) κάνουν reference missing columns.
Αυτό πως το βρίσκω γιατί έχω κοντά 10Κ objects και δεν μπορώ να κάνω handle το error όταν κάνω sp_refreshview και από πίσω σκάει η sp_refreshsqlmodule.
Έχεις κάποια μαγική συνταγή ή ιδέα;

-- SQLschool.gr member
Δυστυχώς μαγική συνταγή ή ιδέα δεν έχω για να λύσω το πρόβλημα αλλά το WITH SCHEMABINDING έχει την δυνατότητα να αποτρέψει φαινόμενα σαν αυτό, αρκεί να χρησιμοποιηθεί.

-- Antonios Chatzipavlis

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 γλαφυρά περιγράφει.

NOTE
Δεν θα υποστηρίξω ότι η χρήση της είναι πανάκεια αλλά ακράδαντα πιστεύω στην χρήση της σε κάθε database.

Interesting points

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

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

Sample initialization

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.

Sample script

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

Returned Error
Msg 1054, Level 15, State 6, Procedure vT_withschemabinding, Line 4
Syntax '*' is not allowed in schema-bound objects.

Names must be in two-part format

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

Sample script

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

Returned Error
Msg 4512, Level 16, State 3, Procedure vT_withschemabinding, Line 5
Cannot schema bind view 'vT_withschemabinding' because name 'T' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Correct Syntax

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 δεν είναι οπότε κάνουμε ότι θέλουμε με αυτή. Επίσης σε παρακάτω πράδειγμα θα δείτε ότι μπορώ να προσθέσω και νέα κολώνα στον πίνακα αυτό.

Drop a used column in object

ALTER TABLE dbo.T
DROP COLUMN col3;
GO

Returned Error
Msg 5074, Level 16, State 1, Line 1
The object 'vT_withschemabinding' is dependent on column 'col3'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN col3 failed because one or more objects access this column.

Drop a NOT used column in object

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).

SQL Script

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.

SQL Script

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.


Execution Plan without schemabinding
image


Execution Plan with schemabinding
image


//Antonios Chatzipavlis


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.



Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
sql school greece logo
© 2010-2021 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.