O SQL Server 2000 μας τελειώνει σιγά-σιγά…


Ο ερχομός του SQL Server 2008 συνεπάγεται το τέλος της υποστήριξης του SQL server 2000. To Mainstream Support (που ουσιαστικά είναι τα Security Updates και τα Non Security Hotfixes) θα λήξει στις 4 Αυγούστου 2008 για τις εκδόσεις SQL Server 2000 64-bit Edition, SQL Server 2000 Developer, SQL Server 2000 Enterprise, SQL Server 2000 Standard, SQL Server 2000 CE, και SQL Server 2000 Workgroup Edition. Το Extended Support θα συνεχιστεί ως το 2013 ωστόσο ο SQL Server 2000 θα πάψει να πωλείται από τους περισσότερους vendors από τον Δεκέμβριο του 2007. Εξάλλου, καμία έκδοση του SQL Server 2000 δεν υποστηρίζεται στα Windows Vista.

Ήδη τις προάλλες μου έλεγε κάποιος γνωστός ότι αισθάνεται ότι ο SQL Server 2008 βγαίνει πολύ νωρίς και ότι ακόμη δεν έχει περάσει σε SQL Server 2005 καθώς οι εφαρμογές του δουλεύουν μια χαρά με 2000, δεν "απαιτούν" κανένα feature του 2005 και δεν υπάρχει λόγος να περάσει από τη διαδικασία του upgrade.

Δεν έχει μεγάλο άδικο… Ουσιαστικά αυτοί που θα τραβήξουν τις υπάρχουσες εγκαταστάσεις είναι οι vendors που θα παρουσιάσουν προϊόντα που απαιτούν 2005. Διαφορετικά, θα πρέπει το προϊόν να έχει τουλάχιστον ένα killer feature που θα μπορεί να χρησιμοποιηθεί χωρίς να χρειάζεται να αλλάξουν οι εφαρμογές. Τέτοια features είναι αυτά που συνήθως ανήκουν στην κατηγορία του administration (ή όπως λέει η Microsoft, στο key-area "Mission-critical platform"). Όχι ότι ο SQL Server 2005 δεν έχει τέτοια features. Απλά δεν είναι killer για την πλειοψηφία των εγκαταστάσεων που απλά τρέχουν ένα ERP ή CRM.

Advertisements

Persistent Computed Columns


Πολλές φορές χρειάζεται να σπάσουμε την κανονικοποίηση ενός πίνακα και να συμπεριλάβουμε computed columns. H θεωρία λέει ότι η τιμή κάθε computed column υπολογίζεται onthefly κάθε φορά που το χρησιμοποιεί κάποιο query και στη βάση δεν αποθηκεύεται τίποτα, έχουμε δηλαδή ένα virtual πεδίο. Όταν όμως η φόρμουλα του computed column είναι περίπλοκη (πχ μπορεί να περιέχει CASE) τότε ενδεχομένως να θέλαμε να αφαιρέσουμε λίγο φορτίο από τη CPU σε βάρος λίγου (ή πολύ) χώρου στο δίσκο ο οποίος θα μπορούσε να φιλοξενεί τις υπολογισμένες τιμές του πεδίου.

 

Mια τεχνική που χρησιμοποιούσαμε μέχρι σήμερα για να κάνουμε persist τις computed τιμές, ήταν να βάζουμε index πάνω στο computed πεδίο ώστε να έχουμε έτοιμες τις τιμές που θέλουμε να διαβάσουμε. Το πρόβλημα είναι ότι η συντήρηση του index έχει overhead γιατί το index δεν είναι και τόσο κατάλληλο γι αυτή τη δουλειά. Ένα UPDATE στα πεδία που απαρτίζουν το computed column ενδέχεται να έχει ως αποτέλεσμα πολύ περισσότερα write ops απ’ όσα χρειάζονται για ένα αντίστοιχο απλό update ενός τυπικού πεδίου (όταν αυτό δεν έχει index), καθώς ενδεχομένως να χρειαστεί να γίνει και restructure του index.

 

Στον SQL Server 2005 υπάρχει η δυνατότητα να δηλώσουμε ένα computed column ως persistent πράγμα που σημαίνει ότι ο server θα διαχειρίζεται διαφανώς τις τιμές του computed πεδίου. Όταν τις διαβάζουμε, θα τις διαβάζουμε από τον δίσκο και όταν αλλάζουν τα πεδία που απαρτίζουν το computed column (στα Inserts και Updates) θα τo ενημερώνει αυτόματα. Ας δούμε ένα παράδειγμα:

 

Αρχικά, θα φτιάξουμε δύο πίνακες, έναν με persistent και ένα με μη-persistent computed column.

CREATE TABLE dbo.Product (

    ProductID INT,

    ProductName NVARCHAR(50),

    SellStartDate DATETIME NOT NULL,

    SellEndDate DATETIME NOT NULL,

    SellDuration AS DATEDIFF(dd, SellStartDate, SellEndDate) )

GO

CREATE TABLE dbo.ProductPCC (

    ProductID INT,

    ProductName NVARCHAR(50),

    SellStartDate DATETIME NOT NULL,

    SellEndDate DATETIME NOT NULL,

    SellDuration AS DATEDIFF(dd, SellStartDate, SellEndDate) PERSISTED )

GO

Το computed column είναι πολύ απλό, ίσα-ίσα για το proof-of-point. H μόνη διαφορά στους δύο πίνακες είναι το PERSISTED keyword στο πεδίο SellDuration. Κατόπιν θα γεμίζουμε τους δύο αυτούς πίνακες με δοκιμαστικά data.

DECLARE @i INT

DECLARE @SellStartDate DATETIME

DECLARE @SellEndDate DATETIME

SET @i = 1

WHILE @i < 1000

    BEGIN

        SET @SellStartDate = CAST(( 365.2422 * 105 ) * RAND() AS DATETIME)

        SET @SellEndDate = DATEADD(d, RAND() * 10000, @SellStartDate)

        INSERT  INTO Product

        VALUES  ( @i,

                  ‘test’ + CAST(@i AS VARCHAR),

                  @SellStartDate,

                  @SellEndDate )

        INSERT  INTO ProductPCC

        VALUES  ( @i,

                  ‘test’ + CAST(@i AS VARCHAR),

                  @SellStartDate,

                  @SellEndDate )

        SET @i = @i + 1

    END

GO

1000 εγγραφές με τυχαία SellStartDate και SellEndDate. Τώρα είμαστε έτοιμοι να δώσουμε τα query μας και να δούμε πως συμπεριφέρονται. Προσοχή! Επειδή μετά από κάθε SELECT θα χρησιμοποιούμε δύο Data Management Views για να μετρήσουμε την απόδοσή του, θα πρέπει να καθαρίζουμε την cache του Query Engine ώστε να έχουμε αξιόπιστα αποτελέσματα. Δίνουμε λοιπόν

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

Και τρέχουμε το πρώτο query. Αυτό δεν χρησιμοποιεί καθόλου computed columns και δουλεύει με την παραδοσιακή τεχνική.

SELECT  Product.ProductID,

        Product.ProductName,

        Product.SellStartDate,

        Product.SellEndDate,

        Product.SellDuration,

        DATEDIFF(dd, SellStartDate, SellEndDate) SellDuration

FROM    Product

Τώρα θα χρησιμοποιήσουμε τα δύο Data Management Views. Τα Data Management Views είναι κάποια system views που παρέχουν διάφορες πληροφορίες και είναι εξαιρετικά χρήσιμα. Αποτελούν το νέο τυποποιημένο τρόπο να κάνουμε διάφορες δουλίτσες, εκεί που χρησιμοποιούσαμε μέχρι σήμερα DBCCs, εξωτερικά εργαλεία, κλπ.

Το Sys.Dm_Exec_Query_Stats μας παρέχει στατιστικά για τα queries που έχουν εκτελεστεί. Είναι πολύ χρήσιμο καθώς μας γλυτώνει από την ανάγκη να χρησιμοποιήσουμε τον Profiler. Το sys.dm_exec_sql_text είναι βοηθητικό, για να πάρουμε το κείμενο του κάθε query. Τρέχουμε το παρακάτω λοιπόν:

SELECT  s2.text,

        total_worker_time,

        total_physical_reads,

        total_logical_reads,

        total_elapsed_time

FROM    Sys.Dm_Exec_Query_Stats

        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

 

και παίρνουμε

 

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

112742

3

9

126750

 

Ξανακάνουμε το ίδιο (μην ξεχάσετε τα DBCC), για το επόμενο query που χρησιμοποιεί το computed column:

GO

SELECT  Product.ProductID,

        Product.ProductName,

        Product.SellStartDate,

        Product.SellEndDate,

        Product.SellDuration

FROM    Product

GO

To query αυτό μας δίνει

 

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

73125

3

9

74515

 

Και τέλος, άλλη μια από τα ίδια για το persisted computed column

SELECT  ProductPCC.ProductID,

        ProductPCC.ProductName,

        ProductPCC.SellStartDate,

        ProductPCC.SellEndDate,

        ProductPCC.SellDuration

FROM    ProductPCC

GO

To DMV query μας δίνει τα παρακάτω αποτελέσματα:

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

58632

6

9

61130

Συγκεντρωτικά, έχουμε τα παρακάτω αποτελέσματα (τα δικά σας θα διαφέρουν):

 

 

total_worker_time

total_physical_reads

total_logical_reads

total_elapsed_time

Query 1

112742

3

9

126750

Query 2

73125

3

9

74515

Query 3

58632

6

9

61130

 

Γενικά, φαίνεται ότι τα persistent computed columns έχουν πολύ καλύτερο performance, φυσικά με tradeoff τον αποθηκευτικό χώρο.

 

Μπορείτε να συνεχίσετε τα πειράματα. Δοκιμάστε να βάλετε ένα index πάνω στο computed πεδίο και στους δύο πίνακες για να δείτε πως θα επηρεαστούν τα αποτελέσματα. Επιπρόσθετα, πριν αποφασίσετε να τα χρησιμοποιήσετε, θα πρέπει να εξετάσετε τι γίνεται με τα write ops από πλευράς performance. Γενικά, τα write ops δημιουργούν overhead οπότε ένας πίνακας με πολλά write ops ενδέχεται να μην είναι κατάλληλος για persistent computed columns.

 


Efficient Schema Changes II


Σε προηγούμενο post Efficient schema changes είχα αναφέρει το πόσο χρονοβόρα μπορεί να γίνει μια διαδικασία αλλαγής του schema ενός πίνακα, όταν αυτός είναι γεμάτος data. Ας δούμε μια περίπτωση. Το σενάριο είναι το εξής: Έχουμε έναν πίνακα με ένα πεδίο σταθερού μεγέθους το οποίο θέλουμε να μειώσουμε ώστε να χωρέσουν περισσότερες εγγραφές στα data και index pages.

Εκ πρώτης όψεως δεν φαίνεται δύσκολο κάτι τέτοιο. Μπορούμε να γράψουμε ένα ALTER TABLE … ALTER COLUMN … statement και να ξεμπερδέψουμε. Σπάνια όμως θα παίξει κάτι τέτοιο (δείτε στα Books On Line τους περιορισμούς). Και αν παίξει, αυτό που θα γίνει θα είναι να κλειδώσει ολόκληρο τον πίνακα όσο γίνεται η διαδικασία, όπερ σημαίνει ότι ως λύση δεν είναι και τόσο ελκυστική.

Εναλλακτικά, μπορεί να γίνει αυτό που κάνει το Management Studio/Enterprise Manager όταν κάνουμε αυτή τη δουλειά μέσω του UI. Δημιουργία ενός νέου temp πίνακα με το νέο schema, μετά INSERT INTO από τον παλιό στο νέο, drop του παλιού, rename του νέου με το παλιό όνομα. Ρίξτε μια ματιά στο INSERT INTO που παράγεται όταν ζητήσουμε αλλαγή του πεδίου Country σε nvarchar(5) από nvarchar(15) (δεν είναι σταθερού μήκους και ούτε indexed αλλά δεν πειράζει, ως παράδειγμα κάνει)

IF EXISTS(SELECT * FROM dbo.Customers)
  EXEC('INSERT INTO dbo.Tmp_Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax, ModifiedDate) SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode,
CONVERT(nvarchar(5), Country), Phone, Fax, ModifiedDate
FROM dbo.Customers WITH (HOLDLOCK TABLOCKX)'
) GO

Χμμμμ… WITH (HOLDLOCK TABLOCKX) (!!!) Μαζί με τη διαχείριση των constraints (drop τα παλιά, create νέα στον temp πίνακα, κλπ) όλα αυτά σε ένα transaction. Όχι πολύ efficient αλλά ευτυχώς που παράγεται αυτό το script αυτόματα!

Ας δούμε μια άλλη προσέγγιση στο πρόβλημα:

Προσθέτουμε στον υπάρχον πίνακα ένα νέο column με την αλλαγή που θέλουμε, κάνουμε UPDATE (αντί INSERT INTO) με  τις τιμές από το παλιό πεδίο, κάνουμε drop το παλιό πεδίο και rename το νέο με το παλιό όνομα. Το πλεονέκτημα είναι ότι δεν χρειάζεται table lock πράγμα που σημαίνει ότι αυξάνεται το availability του πίνακα ενώ με το κολπάκι που θα σας δείξω για το update, μπορεί να τρέξει η διαδικασία σε ένα σύνολο από πολλά μικρά transactions.

USE Northwind

ALTER TABLE Customers 
ADD tmpCountry NVARCHAR(5) NULL

SET NOCOUNT ON 
SET ROWCOUNT 500

DECLARE @rows INT 
SELECT  @rows = 1 
WHILE @rows > 0 
    BEGIN 
        BEGIN TRAN 
        UPDATE  Customers 
        SET     tmpCountry = CONVERT(NVARCHAR(5), Country) 
        WHERE   tmpCountry IS NULL 
        SET @rows = @@ROWCOUNT 
        COMMIT 
    END

ALTER TABLE Customers DROP COLUMN Country

EXEC sp_rename 'Customers.tmpCountry', 'Country', 'COLUMN'

To κόλπο είναι ότι όταν γίνεται ADD το πεδίο tmpCountry, αυτόματα μπαίνει η τιμή NULL σε όλες τις εγγραφές. Κατόπιν, το loop τρέχει όσο βρίσκει εγγραφές με null τιμές για να τους περάσει μέσω του UPDATE τη νέα τιμή. Αυτή η δουλειά γίνεται κάθε φορά σε batches των 500 εγγραφών, δηλαδή δεν χρειάζεται να κλειδώνεται ολόκληρος ο πίνακας. Αν θέλουμε, μπορούμε να προστατέψουμε τις εγγραφές που έχουν γίνει ήδη update από τυχόν updates που έρχονται από τις εφαρμογές που χρησιμοποιούν τη βάση βάζοντας προσωρινά ένα update trigger για το πεδίο Country. Αυτό το update trigger θα κάνει propagate τις αλλαγές στο νέο πεδίο του πίνακα. Το μόνο που χρειάζεται πλέον είναι να κάνουμε το switch των constraints…


Υ.Γ. Τα παραπάνω, βασίζονται σε υλικό που άντλησα από εδώ, πηγή μεγάλης βοήθειας όταν παιδευόμουν… 


Query Plan Guides: Η εκδίκηση του DBA


Το βασικότερο βήμα κατά την εκτέλεση ενός query στον SQL Server είναι η δημιουργία του execution plan, δηλαδή ο καθορισμός του τρόπου που θα γίνουν access τα δεδομένα (χονδρικά, τι indexes θα χρησιμοποιηθούν και πως). Η δημιουργία του execution plan είναι μια περίπλοκη διαδικασία που υλοποιείται από το query engine λαμβάνοντας υπόψη διάφορους παράγοντες με έναν μηχανισμό που ονομάζεται cost-based query optimization. Ο query optimizer βελτιώνεται διαρκώς όχι μόνο από έκδοση σε έκδοση στον SQL Server αλλά ακόμη και από SP σε SP καθώς το development team του SQL Server έχει ειδικές συμφωνίες με μεγάλους πελάτες που παρέχουν πραγματικά δεδομένα και σχετικά traces για το πώς αυτά χρησιμοποιούνται.

Ήδη από την έκδοση 2000 του SQL Server, ο query optimizer είναι πολύ αποτελεσματικός και σχεδόν πάντοτε προτείνει το βέλτιστο execution plan. Εντούτοις, πολλοί developers, για διάφορους λόγους, θεωρούν ότι πρέπει να υποχρεώσουν τον SQL Server να εκτελέσει κάποιο query με συγκεκριμένο τρόπο, επιβάλλοντας δηλαδή συγκεκριμένα indexes ή συγκεκριμένα είδη joins – τα λεγόμενα «HINTS». Όπως αναφέρουν και τα Books On Line, τα query hints θα πρέπει να χρησιμοποιούνται μόνο εφόσον υπάρχει πολύ καλή και δικαιολογημένη αιτία και έχοντας υπόψη ότι ανά πάσα στιγμή ενδέχεται να χρειαστεί να σταματήσει η χρήση τους καθώς η αλλαγές στη φύση των δεδομένων μπορεί να έχει καταστήσει το query hint τελείως ακατάλληλο. Το πρόβλημα είναι ότι πολλές φορές, οι developers γράφουν εφαρμογές που χρησιμοποιούν query hints και πλέον είναι εξαιρετικά δύσκολο να αλλάξει το query.

Στον SQL Server 2005 υπάρχει τρόπος να αλλάξει ένα query (ως προς τα hints) χωρίς να πειραχθεί η εφαρμογή που το στέλνει, δηλαδή να γίνει override κατά κάποιο τρόπο. Αυτό μπορεί να γίνει με τα Plan Guides. Κάθε query που θέλουμε να κάνουμε override, αποθηκεύεται σε έναν εσωτερικό system table μαζί με το αντίστοιχο υποκατάστατό του. Η διαδικασία αυτή γίνεται μέσω δύο stored procedures, της sp_create_plan_guide και sp_control_plan_guide ενώ τα αποθηκευμένα Plan Guides φαίνονται σε ένα system catalog view που ονομάζεται sys.plan_guides.

Για παράδειγμα, δημιουργούμε ένα Plan Guide:

sp_create_plan_guide
@name = N'PlanGuide1',
@stmt = N'SELECT COUNT(*) AS Total FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID and h.OrderDate BETWEEN ''1/1/2000'' AND ''1/1/2005'' ',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MERGE JOIN)'
GO

Χρειάζεται προσοχή ώστε το SQL statement να είναι ακριβώς όπως το λαμβάνει ο server. Γι αυτό χρησιμοποιούμε τον Profiler τόσο για το statement (@stmt) όσο και για τις παραμέτρους (@params). Κατόπιν, μπορούμε να το ενεργοποιήσουμε, να το απενεργοποιήσουμε, να το κάνουμε drop, κλπ με την sp_control_plan_guide:

sp_control_plan_guide N'DROP', N'PlanGuide1'

Τα Plan Guides έχουν πολλές εφαρμογές. Πέρα από το παράδειγμα που ανέφερα, μπορούν να χρησιμοποιηθούν πολύ έξυπνα μαζί με τα OPTIMIZE FOR ή RECOMPILE query hints καθώς και για να αλλάξει συμπεριφορά σε queries που παίζουν με parallel execution plans. Πρακτικά, μπορούν να χρησιμοποιηθούν σε όλες τις εκδόσεις του SQL Server 2005 εκτός από την Express και τη Workgroup.

Πολλά περισσότερα για τα παραπάνω μπορείτε να βρείτε στο http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx όπου υπάρχει και ένα πολύ καλό technical article σε word format για να κάνετε download.


SQL Server 2005 Schemas


Ένα νέο χαρακτηριστικό στον SQL Server 2005 είναι τα Schemas. To Schema είναι κάτι σαν το namespace στο .ΝΕΤ Framework. Κάθε database object δημιουργείται κάτω από ένα schema και το πλήρες reference στο object (το «fully qualified name» που λέμε) γίνεται χρησιμοποιώντας όνομα του τύπου server.database.schema.object (πχ SERVER01.AdventureWorks.Production.Product)

Το παραπάνω μοιάζει με το fully qualified name που έχουμε στον SQL Server 7 & 2000 ωστόσο η ομοιότητα είναι μόνο οπτική. Στον 7 & 2000, το schema προσδιορίζεται από τον δημιουργό του object, υπάρχει δηλαδή άμεση συνάφεια μεταξύ user και schema. Στον 2005, τα schemas είναι ανεξάρτητα από τους users. Αυτή η οργάνωση έχει διάφορα πλεονεκτήματα όπως:

  • Λιγότερα προβλήματα με τα object ownerships
  • Απλουστευμένο μοντέλο security καθώς μπορούν να εκχωρηθούν δικαιώματα είτε σε schema level, είτε σε object level
  • Ευκολότερη διαχείριση καθώς αν διαγραφεί ένας χρήστης δεν αφήνει ορφανά objects

Διαδικασία Name Resolution

Προκειμένου να καταλάβει ο SQL Server σε ποιο object αναφερόμαστε σε κάθε statement, χρησιμοποιεί μια διαδικασία που ονομάζεται name resolution. Για παράδειγμα, ας υποθέσουμε ότι η βάση περιέχει δύο objects Sales.Product και Production.Product. Αν δεν προσδιορίσουμε το fully qualified name και πούμε «SELECT * FROM Products» τότε ο Server θα εξετάσει αν ο χρήστης που έδωσε το query έχει default schema και αν υπάρχει πίνακας Products σε αυτό. Αν δεν υπάρχει default schema θα ψάξει το object στο dbo schema. Μπορούμε να κατασκευάσουμε ένα schema με την εντολή CREATE SCHEMA:

CREATE SCHEMA Production

Ενώ μπορούμε να δώσουμε default schema σε κάποιον χρήστη με την εντολή ALTER USER:

ALTER USER Manos WITH DEFAULT_SCHEMA = Production

SQL Server 2005 και CLR, μέρος #1: Managed User-Defined Functions


Ο SQL Server 2005 έχει πολλά νέα χαρακτηριστικά. Πολλά από αυτά είναι εμφανή, πολλά άλλα είναι κάτω από το καπό. Μερικά από αυτά είναι τόσο σημαντικά, που ακόμη δεν έχουμε συλλάβει τις αλλαγές που σηματοδοτούν στον τρόπο που γράφουμε εφαρμογές σήμερα.

Ένα τέτοιο χαρακτηριστικό είναι η δυνατότητα να γράφουμε κώδικα σε οποιαδήποτε γλώσσα προγραμματισμού δουλεύει στο .NET CLR, εκεί που μέχρι σήμερα δεσμευόμασταν με την T-SQL. Κατά την ταπεινή μου γνώμη μου είναι το σημαντικότερο χαρακτηριστικό του SQL Server 2005. Το θέμα έχει πολύ background και πολλά θεωρητικά για να συζητήσουμε, ωστόσο θα ξεκινήσουμε με ένα απλό παράδειγμα και πάνω σε αυτό μπορούμε να επεκταθούμε.

Θα φτιάξουμε ένα UDF χρησιμοποιώντας VB.NET. Μπορούμε να ξεκινήσουμε το Visual Studio 2005 και με New Project, επιλέγουμε τη γλώσσα προτίμησης και στην ομάδα Database, επιλέγουμε SQL Server Project. Δίνουμε το κατάλληλο όνομα στο project και κατόπιν εμφανίζεται ένα παράθυρο με το οποίο επιλέγουμε ένα Database Reference (αν δεν έχουμε κανένα, τότε εμφανίζεται το αντίστοιχο dialog για να δημιουργήσουμε) που υποδηλώνει σε ποια database θα δουλέψουμε. Το Visual Studio θα δημιουργήσει το Solution με το Project και κάποια βασικά αρχεία μέσα σε αυτό που θα δούμε τη χρησιμότητά τους παρακάτω.

Πάνω στο όνομα του project κάνουμε δεξί κλικ και επιλέγουμε Add User Defined Function. Αφήνουμε ως όνομα το Function1.vb και ανοίγει ο κώδικας της UDF που προσθέσαμε. Θα παρατηρήσετε ότι είναι έτοιμος κώδικας από ένα UDF που επιστρέφει το “Hello”. Για αρχή μας αρκεί. Η μοναδική αλλαγή που θα κάνουμε είναι να αλλάξουμε το attribute “<Microsoft.SqlServer.Server.SqlFunction()> _” σε “<Microsoft.SqlServer.Server.SqlFunction(“SayHello”)> _”. Αυτό ήταν! Μόλις φτιάξαμε ένα managed UDF.

Για να δοκιμάσουμε το δημιούργημά μας έχουμε δύο επιλογές. Η πρώτη είναι να κάνουμε Deploy το Project/Solution. Πράγματι, αν το κάνουμε αυτό και κατόπιν ανοίξουμε το Management Studio, θα εμφανιστεί το UDF ως scalar-valued Function στο group Programmability. Θα παρατηρήσετε ότι ο default owner του UDF είναι ο dbo γιατί ό,τι γίνεται deploy από το VS μπαίνει σε αυτό το schema. Για να τρέξουμε αυτό το UDF θα πρέπει πρώτα να ενεργοποιήσουμε τη δυνατότητα για εκτέλεση managed κώδικα, που για λόγους security είναι απενεργοποιημένη. Για να γίνει αυτό δίνουμε:

sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO

οπότε πλέον μπορούμε να πούμε

USE Northwind 
SELECT dbo.SayHello()

Το όνομα του UDF είναι αυτό που καθορίσαμε στο SqlFunction attribute. Αν δεν είχαμε καθορίσει τίποτα εκεί, το όνομα θα ήταν Function1.

Η δεύτερη επιλογή για να δοκιμάσουμε το UDF είναι να γράψουμε το SELECT (ή ό,τι άλλο κώδικα T-SQL χρειάζεται) στο αρχείο Test.sql που υπάρχει στο project μας και εξυπηρετεί ακριβώς αυτόν το σκοπό. Αυτό το αρχείο τρέχει αν ξεκινήσουμε το debugging. Μπορούμε να έχουμε πολλαπλά test scripts και να επιλέγουμε με δεξί κλικ και “Set as Default Debug Script” ποιο θα τρέχει με την έναρξη του Debugging.

Διάφορα αξιοσημείωτα

  • Όταν φτιάχνουμε ένα managed object μέσω template τότε δημιουργείται ένα partial class ώστε αν φτιάξουμε πολλαπλά objects να έχουμε τον κώδικα σε ξεχωριστά αρχεία, άλλα όλα μαζί σε μία κλάση.
  • Κάθε managed object που δημιουργούμε είναι και ένα Shared Function/Sub (Static στη C#) έτσι ώστε να μην χρειάζεται instance της κλάσης για να χρησιμοποιηθεί. Αυτό συνεπάγεται τον εξής περιορισμό: Μέσα σε ένα Shared Function/Sub μπορούμε να χρησιμοποιήσουμε μόνο shared objects. Δηλαδή ο παρακάτω κώδικας θα χτυπήσει κατά το compilation
    Public Class UserDefinedFunctions1 
        Public Class test 
            Public Val As Integer 
        End Class
    
        Dim t As New test
    
        <Microsoft.SqlServer.Server.SqlFunction()> _ 
        Public Shared Function Function1() As SqlString 
            ' Add your code here 
            t.Val = 5 
            Return New SqlString("Hello") 
        End Function 
    End Class

Σε προσεχές post θα δούμε κάτι πιο χρήσιμο πέρα από το Hello κι επίσης θα εξετάσουμε τη δημιουργία κι άλλων τύπων managed objects όπως stored procedures, triggers, κλπ.


Try…Catch


Επιτέλους! Το error handling πάντα ήταν ένα από τα αδύνατα σημεία της T-SQL. Πλέον στη νέα έκδοση του SQL Server υποστηρίζεται η δομή Try…Catch! Ας δούμε ένα παράδειγμα:

SET tempdb 
GO 
  
CREATE TABLE DemoTable (ColumnA int PRIMARY KEY, ColumnB int) 
CREATE TABLE LogTable (ColumnA int, ColumnB int, error int,  date datetime default GETDATE()) 
GO 
  
CREATE PROCEDURE DoSomething @a int, @b int AS 
SET XACT_ABORT ON 
BEGIN TRY 
BEGIN TRAN 
    INSERT INTO DemoTable VALUES (@a, @b) 
COMMIT TRAN 
END TRY 
BEGIN CATCH  
  DECLARE @err int 
  SET @err = @@error 
ROLLBACK TRAN 
  INSERT INTO LogTable VALUES(@a, @b, @err, default) 
END CATCH 
GO 

EXEC DoSomething 1,1 
EXEC DoSomething 2,2 
EXEC DoSomething 3,3 
EXEC DoSomething 1,1

SELECT * FROM LogTable

Μέσα στην procedure, θα παρατηρήσετε ότι η δομή Try…Catch σπάει δε δύο τμήματα. Το πρώτο είναι το BEGIN TRY…END TRY block και το δεύτερο είναι το BEGIN CATCH…END CATCH block. Εντούτοις, αυτά τα δύο αποτελούν μία οντότητα και δεν μπορούν να διαχωριστούν (πχ το πρώτο να είναι σε μια stored procedure και το δεύτερο σε ένα trigger). Ωστόσο, υπάρχει η δυνατότητα για nested Try…Catch structures οπότε αυτό από μόνο του αποτελεί τον πρώτο και καλύτερο λόγο για να τα χρησιμοποιήσει κάποιος στον κώδικά του.

Ένα σημείο που θέλει προσοχή είναι το ποια λάθη πιάνει το Catch block. Υπάρχουν διάφορα λάθη που δεν περνάνε στο Catch block:

  • Συντακτικά λάθη
  • Λάθη από recompilation (π.χ. έχουμε κάνει drop έναν πίνακα που υπήρχε όταν φτιάξαμε το stored procedure με το Try…Catch)
  • Όσα προκαλούν την διακοπή του connection
  • Όσα έχουν severity level έως και 10

Για τα δύο πρώτα, μπορούμε να καταφύγουμε στο κόλπο του σπασίματος του κώδικα σε πολλαπλές procedures. Ειδικά για τα τελευταία, αν θέλουμε οπωσδήποτε να περνάει ο έλεγχος στο Catch block όταν συμβούν, τότε έχουμε δύο επιλογές. Μπορούμε να ενεργοποιήσουμε το SET XACT_ABORT ON (όπως στο παράδειγμα) ή να ελέγχουμε το global variable @@error μετά από κάθε “επικίνδυνο” statement και κατόπιν να εκτελούμε ένα RAISERROR…WITH TRAN_ABORT ώστε να περάσουμε explicitly τον έλεγχο στο Catch κομμάτι.

Επίσης (αυτό είναι μία από τις παλιότερες αρχές του error handling σε T-SQL), στο Catch block, αν σκοπεύουμε να χρησιμοποιήσουμε το @@error θα πρέπει αμέσως να το αποθηκέψουμε σε μία μεταβλητή γιατί θα χαθεί η τιμή του μετά από οποιοδήποτε statement που θα εκτελεστεί επιτυχώς. Δηλαδή ο παρακάτω κώδικας, αν και τυπικό για περιβάλλον VB.NET, εδώ είναι συνταγή αποτυχίας:

BEGIN CATCH  
  SELECT 'Unexpected error occurred: ', @@Error 
  INSERT INTO LogTable VALUES(@a, @b, @err, default) 
END CATCH

Τέλος, καλό θα είναι όταν έχουμε περίπλοκα CATCH blocks να κάνουμε ABORT ΤRAN όσο το δυνατόν συντομότερο για να επελευθερώνουμε resources. Αυτό συμβαίνει γιατί όταν έχει συμβεί κάποιο critical error (ενώ έχουμε κάνει BEGIN TRAN), τότε το transaction μπαίνει σε ένα doomed state από το οποίο δεν μπορεί να βγεί παρά μόνο όταν κάνουμε εμείς ROLLBACK. Μέχρι τότε, μπορούμε να κάνουμε οτιδήποτε αρκεί να μην έχει ως αποτέλεσμα να γραφτεί κάτι στο transaction log.