Ο 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, κλπ.

Επιτέλους! Το 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.

Πολλές φορές έχουμε να αντιμετωπίσουμε requests του τύπου «θέλω όλες τις σημερινές παραγγελίες ταξινομημένες κατά ώρα αλλά με μία πρώτη στήλη extra που να έχει αύξοντα αρίθμηση». Εφόσον εκείνη την ώρα δεν χτυπήσει ο κεραυνός του Codd τον βάρβαρο που τόλμησε να ξεστομίσει τέτοιο πράγμα, εμείς θα πρέπει να το ικανοποιήσουμε. Για να πούμε την αλήθεια, η πρακτική αξία της δυνατότητας να μπορεί να παράγει κανείς row numbers είναι μεγάλη. Φανταστείτε για παράδειγμα πόσο πιο απλός θα ήταν ο κώδικας για την υλοποίηση paging tables σε web-pages αν είχαμε αυτή τη δυνατότητα.

Μέχρι σήμερα, για να φτιάξουμε κάτι τέτοιο, θα έπρεπε να καταφύγουμε σε διάφορες τεχνικές. Κάποιος θα μπορούσε να σκαρώσει γρήγορα-γρήγορα ένα query του τύπου:

SELECT (SELECT COUNT(*)
  FROM Orders AS O2
  WHERE O2.orderid <= O1.orderid) AS rownum,
  orderid,
  orderdate
FROM Orders AS O1
ORDER BY orderid

το οποίο είναι τρομερά inefficient καθώς για κάθε εγγραφή, κάνει ένα ξεχωριστό query προκειμένου να βρει τον αριθμό της (περισσότερα τέτοια εδώ). Άλλοι, θα επιλέξουν να γίνει αυτή η στήλη αρίθμησης client-side, να την παράγουν server side με cursors ή με temporary tables.

Στον SQL Server 2005 υπάρχει μια σειρά από functions που μας βοηθάνε ακριβώς σε αυτόν τον τομέα. Ονομάζονται “Ranking Functions” και όπως θα διαπιστώσετε έχουν πολύ μεγαλύτερη ευελιξία από οποιαδήποτε λύση του παρελθόντος.

Ας πούμε ότι θέλουμε το query να μας επιστρέφει το όνομα του υπαλλήλου που έχει θέσει την παραγγελία, την ημερομηνία και αρίθμηση των παραγγελιών ανά υπάλληλο σε φθίνουσα σειρά ημερομηνίας. Το query μας είναι το παρακάτω:

SELECT  Employees.LastName,
  RANK() OVER(PARTITION BY Employees.LastName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM    Orders INNER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID

Εδώ, το ranking function είναι το RANK() το οποίο συνοδεύεται από ένα OVER clause το οποίο περιέχει το PARTITION BY clause που καθορίζει ένα column με επαναλαμβανόμενες τιμές – το «ανά υπάλληλο» στο ζητούμενο query μας – και ένα ORDER BY clause που καθορίζει τη σειρά ταξινόμησης.

Και ιδού το αποτέλεσμα:

Buchanan 1 1998-04-22 00:00:00.000
Buchanan 2 1998-03-17 00:00:00.000
Buchanan 3 1998-03-03 00:00:00.000
...
Callahan 1 1998-05-06 00:00:00.000
Callahan 2 1998-05-04 00:00:00.000
Callahan 3 1998-05-01 00:00:00.000
...
Davolio 1 1998-05-06 00:00:00.000
Davolio 2 1998-05-05 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 5 1998-05-01 00:00:00.000
Davolio 6 1998-04-21 00:00:00.000
Davolio 6 1998-04-21 00:00:00.000
...
Dodsworth 1 1998-04-29 00:00:00.000
Dodsworth 2 1998-04-14 00:00:00.000
Dodsworth 3 1998-04-13 00:00:00.000
Dodsworth 4 1998-04-10 00:00:00.000
Dodsworth 5 1998-03-26 00:00:00.000
...

Παρατηρήστε οι εγγραφές της αγαπημένης μας Davolio έχουν rank numbers που επαναλαμβάνονται. Αυτό συμβαίνει γιατί υπάρχουν πολλαπλές παραγγελίες την ίδια μέρα (και ώρα).  Παρατηρήστε επίσης ότι οι τιμές σε αυτή τη στήλη είναι 3,3,5,6,6,8 κλπ. Δηλαδή όταν δύο ή παραπάνω rows έχουν ίδιο rank, τότε το επόμενο rank «πηδάει» ανάλογες θέσεις. Αν δεν το θέλουμε αυτό, τότε αλλάζουμε το ranking function από RANK σε DENSE_RANK, δηλαδή:

SELECT  Employees.LastName,
  DENSE_RANK() OVER(PARTITION BY Employees.LastName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM    Orders INNER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID

Το αποτέλεσμα:

…
Davolio 1 1998-05-06 00:00:00.000
Davolio 2 1998-05-05 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 4 1998-05-01 00:00:00.000
Davolio 5 1998-04-21 00:00:00.000
Davolio 5 1998-04-21 00:00:00.000
Davolio 6 1998-04-16 00:00:00.000
Davolio 7 1998-04-14 00:00:00.000
Davolio 8 1998-04-09 00:00:00.000
Davolio 9 1998-04-02 00:00:00.000
Davolio 10 1998-04-01 00:00:00.000
Davolio 10 1998-04-01 00:00:00.000
…

Και πάλι όμως μπορεί να πει κάποιος «Εγώ θέλω οπωσδήποτε να έχω αύξουσα αρίθμηση για κάθε row». Γι αυτόν τον περίεργο που θα πει τέτοιο πράγμα, έχουμε το function ROW_NUMBER. Οπότε, το νέο query είναι ως εξής:

SELECT  Employees.LastName,
  ROW_NUMBER() OVER(PARTITION BY Employees.LastName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM    Orders INNER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID

Με αποτέλεσμα:

…
Davolio 1 1998-05-06 00:00:00.000
Davolio 2 1998-05-05 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 4 1998-05-04 00:00:00.000
Davolio 5 1998-05-01 00:00:00.000
Davolio 6 1998-04-21 00:00:00.000
Davolio 7 1998-04-21 00:00:00.000
Davolio 8 1998-04-16 00:00:00.000
Davolio 9 1998-04-14 00:00:00.000
Davolio 10 1998-04-09 00:00:00.000
Davolio 11 1998-04-02 00:00:00.000
Davolio 12 1998-04-01 00:00:00.000
Davolio 13 1998-04-01 00:00:00.000
…

Φυσικά, αν θέλουμε να μην εφαρμόσουμε partitions στα δεδομένα μας, μπορούμε να πούμε κάτι σαν:

SELECT ROW_NUMBER() OVER (ORDER BY OrderID), * FROM Orders

Πάντοτε το ORDER BY clause είναι υποχρεωτικό, ακόμη κι αν έχουμε κάποιο clustered index που μας επιστρέφει ταξινομημένα τα δεδομένα.

Τέλος, υπάρχει ένα τέταρτο ranking function που ονομάζεται NTILE και χρησιμεύει προκειμένου να «σπάσουμε» ένα σύνολο από εγγραφές σε ομάδες. Δηλαδή, για παράδειγμα, αν θέλουμε μια λίστα για τη μεταφορική εταιρία «United Package», τις παραγγελίες που έχει διακινήσει, την ημερομηνία παραγγελίας και όλα αυτά σε 5 groups, τότε μπορούμε να γράψουμε το εξής query:

SELECT Shippers.CompanyName,
  Orders.OrderID,
  NTILE(5) OVER(PARTITION BY Shippers.CompanyName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM Orders INNER JOIN Shippers
   ON Orders.ShipVia = Shippers.ShipperID
WHERE Shippers.CompanyName='United Package'

Το function NTILE έχει ένα όρισμα που δείχνει πόσα groups θα φτιαχτούν. Παράλληλα, προσπαθεί να κάνει populate τα groups με ίσο αριθμό εγγραφών και αν αυτό δεν γίνει, τοποθετεί τα groups με μεγαλύτερο αριθμό εγγραφών, πρώτα.

United Package 11074 1 1998-05-06 00:00:00.000
United Package 11075 1 1998-05-06 00:00:00.000
United Package 11076 1 1998-05-06 00:00:00.000
...
United Package 10938 2 1998-03-10 00:00:00.000
United Package 10939 2 1998-03-10 00:00:00.000
United Package 10936 2 1998-03-09 00:00:00.000
...
United Package 10768 3 1997-12-08 00:00:00.000
United Package 10761 3 1997-12-02 00:00:00.000
United Package 10756 3 1997-11-27 00:00:00.000
...
United Package 10579 4 1997-06-25 00:00:00.000
United Package 10577 4 1997-06-23 00:00:00.000
United Package 10574 4 1997-06-19 00:00:00.000
...
United Package 10429 5 1997-01-29 00:00:00.000
United Package 10427 5 1997-01-27 00:00:00.000
United Package 10425 5 1997-01-24 00:00:00.000
...

Κλείνοντας, θα πρέπει να πούμε ότι τα ranking functions είναι non-deterministic, που χοντρικά σημαίνει ότι το ίδιο query σε δύο διαφορετικές χρονικές στιγμές μπορεί να δώσει διαφορετικά αποτελέσματα. Αν θα γίνει αυτό ή όχι εξαρτάται από τη σειρά ταξινόμησης. Όταν βασίζεται σε κάποιο κλειδί (και δεν αλλάζει το πλήθος των εγγραφών) τότε είναι deterministic.  Δεν μπορούμε δηλαδή να είμαστε ποτέ σίγουροι ότι η παραγγελία 10936 θα έχει rank 2.

Ένας από τους κυριότερους λόγους που πολλοί developers καταφεύγουν στους cursors όταν γράφουν T-SQL κώδικα είναι η αδυναμία να σκεφτούν τη λύση του προβλήματος με set-oriented τρόπο. Μια κλασική περίπτωση που συμβαίνει αυτό είναι το «θέλω για κάθε εγγραφή από τον πίνακα Χ να συμβαίνει κάτι στο πίνακα Υ».

Ο APPLY operator είναι ένας νέος relational operator που έρχεται να βοηθήσει όταν αντιμετωπίζουμε τέτοια προβλήματα, ώστε να μην χρειαστεί να καταφύγουμε σε cursors. Χρησιμοποιείται στο FROM clause και μας επιτρέπει να εφαρμόσουμε ένα table expression για κάθε εγγραφή του εξωτερικού πίνακα, όπου table expression μπορεί να είναι ένα view, ένας πίνακας ή ένα table function.

Ας δούμε ένα παράδειγμα:

Θέλουμε ένα report όπου για κάθε κατηγορία προϊόντων θα εμφανίζονται τα 3 πιο ακριβά προϊόντα.

Αρχικά, ορίζουμε το table function το οποίο έχει ως παράμετρο το CategoryID και βάσει αυτού φέρνει με TOP(3) τα τρία ακριβότερα προϊόντα.

CREATE FUNCTION MostExpensiveProducts(@CatID int) 
RETURNS TABLE AS
RETURN
  SELECT TOP (3) ProductID, ProductName, UnitPrice
  FROM dbo.Products
  WHERE CategoryID = @CatID
  ORDER BY UnitPrice DESC

Κατόπιν είμαστε έτοιμοι να γράψουμε το query μας:

SELECT CategoryName, MEP.ProductName, MEP.UnitPrice
FROM Categories 
CROSS APPLY MostExpensiveProducts(CategoryID) AS MEP

Και το output για μερικούς-μερικούς που δεν έχουν εγκαταστήσει SQL Server 2005 ακόμη!

CategoryName    ProductName                              UnitPrice
--------------- ---------------------------------------- ---------------------
Beverages       Côte de Blaye                            263.50
Beverages       Ipoh Coffee                              46.00
Beverages       Chang                                    19.00
Condiments      Vegie-spread                             43.90
Condiments      Northwoods Cranberry Sauce               40.00
Condiments      Sirop d'érable                           28.50
Confections     Sir Rodney's Marmalade                   81.00
Confections     Tarte au sucre                           49.30
Confections     Schoggi Schokolade                       43.90
Dairy Products  Raclette Courdavault                     55.00
Dairy Products  Queso Manchego La Pastora                38.00
Dairy Products  Gudbrandsdalsost                         36.00
Grains/Cereals  Gnocchi di nonna Alice                   38.00
Grains/Cereals  Wimmers gute Semmelknödel                33.25
Grains/Cereals  Gustaf's Knäckebröd                      21.00
Meat/Poultry    Thüringer Rostbratwurst                  123.79
Meat/Poultry    Mishi Kobe Niku                          97.00
Meat/Poultry    Alice Mutton                             39.00
Produce         Manjimup Dried Apples                    53.00
Produce         Rössle Sauerkraut                        45.60
Produce         Uncle Bob's Organic Dried Pears          30.00
Seafood         Carnarvon Tigers                         62.50
Seafood         Ikura                                    31.00
Seafood         Gravad lax                               26.00

Το σημαντικό εδώ είναι ότι έχουμε τη δυνατότητα να περνάμε τα πεδία από την εγγραφή του αριστερού πίνακα προς τον δεξιό πίνακα, δηλαδή για κάθε εγγραφή του πίνακα Categories περνάμε το CategoryID ως παράμετρο στο table-function. Επίσης, αν υποθέσουμε ότι είχαμε κάποια κατηγορία προϊόντων, χωρίς όμως προϊόντα, θα μπορούσαμε να χρησιμοποιήσουμε το OUTER APPLY ώστε να εμφανιστεί και αυτή η κατηγορία, με nulls όμως στα πεδία ProductName και UnitPrice.

Common Table Expressions

Τα Common Table Expressions (CTEs) είναι ένα νέο χαρακτηριστικό της T-SQL το οποίο καθορίζεται στο SQL-99 πρότυπο. Τι είναι αυτά; Είναι ο τρόπος με τον οποίο καθορίζουμε προσωρινά result sets μέσα σε ένα statement. Φέρτε στο μυαλό σας τα derived και temporary tables… Ε, κάτι παρόμοιο αλλά όχι ακριβώς το ίδιο όπως θα δούμε παρακάτω. Συνήθως χρησιμοποιούνται για δύο λόγους. Προκειμένου να απλουστεύσουμε τη δομή περίπλοκων queries (εντάξει, αυτό δεν μας ενδιαφέρει γιατί δεν έχουμε πρόβλημα με τα περίπλοκα queries) αλλά και προκειμένου να μπορούμε να υλοποιήσουμε εύκολα αναδρομικά (recursive) queries! 

Η σύνταξη ενός CTE έχει ως εξής:

[WITH <common_table_expression> [,...n] ]
<common_table_expression>::=
   expression_name
   [(column_name [,...n])]
   AS
   (<CTE_query_definition>)

Βέβαια, είναι κομματάκι δύσκολο να αντιληφθεί κανείς αμέσως περί τίνος πρόκειται, γι αυτό ας δούμε ένα παράδειγμα:

WITH TopOrders (ProductID, TotQty) AS
( SELECT ProductID, Sum(Quantity) 
  FROM [Order Details] GROUP BY ProductID)
SELECT * FROM TopOrders
WHERE TotQty>1000
ORDER BY TotQty DESC

Δεν είναι τρομερό ως παράδειγμα με την έννοια του ότι θα μπορούσαμε να κάνουμε το ίδιο πράγμα χωρίς CTE, ωστόσο ας δούμε πως σχετίζεται με τον ορισμό. Καταρχήν, ορίζουμε ένα CTE που ονομάζεται TopOrders, αποτελείται από δύο πεδία, το ProductID και το TotQty. To CTE_query_definition είναι το SELECT που μας επιστρέφει το result set του οποίου τα πεδία γίνονται match με αυτά που έχω καθορίσει στο CTE.

Αν χρησιμοποιούσαμε temporary table θα έπρεπε να το δηλώναμε και να το κάναμε populate. Εδώ τα γλυτώνουμε αυτά, ωστόσο ο CTE μπορεί να χρησιμοποιηθεί μόνο μια φορά, στο query που ακολουθεί, ενώ το temporary table μπορεί να επαναχρησιμοποιηθεί.

Φυσικά, δεν μας εμποδίζει τίποτα να κάνουμε παιχνίδι όπως εδώ:

WITH TopOrders (ProductID, TotQty) AS
( SELECT ProductID, Sum(Quantity) 
  FROM [Order Details] GROUP BY ProductID)
SELECT P.ProductName, TPO.TotQty FROM TopOrders TPO
INNER JOIN Products P ON P.ProductID=TPO.ProductID
WHERE TotQty>1000
ORDER BY TotQty DESC

Εκεί που φαίνεται η αξία των CTE είναι στα λεγόμενα recursive queries, δηλαδή queries σε πίνακες με self-join. Για παράδειγμα, στη Northwind database, υπάρχει ο πίνακας Employees, με το πεδίο EmployeeID ως κλειδί και για κάθε employee, το πεδίο ReportsTo δείχνει στον manager που αναφέρεται αυτός ο υπάλληλος. Έτσι λοιπόν, αν θέλουμε να πάρουμε ένα report με τους managers και τους υφισταμένους τους, τότε μπορούμε να γράψουμε το εξής:

WITH Managers (ManagerID, EmployeeID) AS 
( SELECT ReportsTo, EmployeeID
  FROM Employees
  WHERE ReportsTo IS NULL
 UNION ALL
 SELECT E.ReportsTo, E.EmployeeID
  FROM Employees E  
  INNER JOIN Managers M ON 
  E.ReportsTo = M.EmployeeID)
SELECT * FROM Managers

Αυτό το CTE είναι ελαφρώς διαφορετικό σε σχέση με το προηγούμενο. Κατ’ αρχήν θυμηθείτε ας πούμε τα recursive functions σε οποιαδήποτε γλώσσα προγραμματισμού. Θα πρέπει να υπάρχει ένας τρόπος να σταματούν. Εδώ, δεν έχουμε IF και η λογική δεν είναι procedural και για αυτόν λόγο, έχουμε ένα πρώτο SELECT που ονομάζεται «anchor member query» - αποτελεί δε την κορυφή της ιεραρχίας.

Κατόπιν, υποχρεωτικά έχουμε το UNION ALL (το μοναδικό operator που επιτρέπεται) το οποίο συνδέει τα δύο result set. Τέλος έχουμε το «recursive member query» το οποίο συνδέει τον πίνακα με το CTE και εκτελείται συνεχώς μέχρι  να μην επιστρέφει result set.

Τέλος, μπορούμε να καθορίσουμε το μέγιστο αριθμό από recursions με το MAXRECURSION option (τι άλλο περιμένατε, ε;), ο οποίος αν ξεπεραστεί, τότε το query σταματά με error message.

Έγραψα προηγουμένως ότι η αξία των CTE φαίνεται στα recursive queries. Ορίστε λοιπόν μια άσκηση για γερούς αναγνώστες… Δοκιμάστε να γράψετε το παραπάνω χωρίς CTE για να δούμε αν αξίζει ή όχι η χρήση τους. Αν δεν έχω απάντηση (μιας και δεν βλέπω ποτέ κανένα σχόλιο στα posts μου) θα σας δείξω έναν τρόπο σε προσεχές post.

Αν έχετε γράψει κώδικα για triggers θα ξέρετε τους ψευδοπίνακες Inserted και Deleted μέσω των οποίων μπορεί κανείς να αποκτήσει πρόσβαση στις τιμές των rows που γίνονται INSERT, UPDATE ή DELETE, ακριβώς πριν να ολοκληρωθεί η διαδικασία.

Για παράδειγμα, ένα τυπικό πρόβλημα που θα έπρεπε να χρησιμοποιήσουμε triggers, είναι όταν αλλάζουμε την τιμή όλων των προϊόντων που ανήκουν σε μια κατηγορία και το ζητούμενο είναι να καταγράφουμε σε έναν πίνακα την προηγούμενη τιμή, τη νέα τιμή, την ημέρα και ώρα της αλλαγής και τον χρήστη που κάνει την αλλαγή. Εναλλακτικά, αν δεν θέλουμε να χρησιμοποιήσουμε triggers, θα πρέπει να γράψουμε μια μακροσκελή stored procedure η οποία θα κάνει ένα iteration σε όλες τις εγγραφές, θα κάνει ένα-ένα τα UPDATE που θα ακολουθούνται από ένα INSERT στο logging πίνακα (Χωρίς cursors ε! Με τον άλλον τρόπο, τον καλό, με while loop).

Στον SQL Server 2005 μπορούμε να χρησιμοποιήσουμε το keyword OUTPUT το οποίο μπαίνει στα INSERT, UPDATE και DELETE statements και μας δίνει πρόσβαση στους πίνακες Inserted και Deleted αλλά χωρίς να βρισκόμαστε στο context ενός trigger!

Δείτε εδώ:

DECLARE @PriceChangeDetails TABLE
(ProductID int, OldPrice money, 
 NewPrice money, UpdatedBy sysname, UpdateDate datetime)
 
UPDATE Products
SET UnitPrice = UnitPrice * 1.05
OUTPUT INSERTED.ProductID, DELETED.UnitPrice, INSERTED.UnitPrice,
suser_name(), getdate() INTO @PriceChangeDetails
WHERE CategoryID = 1
 
SELECT * FROM @PriceChangeDetails

Αντίστοιχα, όπως και στους triggers, στα INSERT statements έχουμε πρόσβαση μόνο στον πίνακα Inserted και στα DELETE statements μόνο στον πίνακα Deleted.

Οι περιπτώσεις που δεν μπορούμε να χρησιμοποιήσουμε το OUTPUT keyword είναι όταν έχουμε INSERT πάνω σε view και όταν το DML γίνεται σε local/distributed partitioned tables/views ή σε remote tables/views

Άλλη μια αλλαγή στο νέο SQL Server είναι ο τρόπος με τον οποίο μπορούμε να διαχειριστούμε τα indexes. Πλέον έχει προστεθεί functionality στην εντολή ALTER INDEX και δεν είναι υποχρεωτικό να χρησιμοποιούμε τα DBCC statements. Έτσι, μπορούμε να κάνουμε τα παρακάτω:

• Disable ένα index
  ALTER INDEX <index_name> ΟΝ <table_name> DISABLE
• Rebuild ένα index
  ALTER INDEX <index_name> ΟΝ <table_name> REBUILD
  Αυτό αντιστοιχεί με το DBCC DBREINDEX και πέραν του προφανή λόγου που χρησιμοποιείται κάνει επίσης enable ένα index που έχουμε κάνει disable με το προηγούμενο statement (Λογικό αυτό γιατί το disabled index έχει «μείνει» στα παλιά data).
• Reorganize index
  ALTER INDEX <index_name> ΟΝ <table_name> REORGANIZE
  Αυτό αντιστοιχεί με το DBCC INDEXDEFRAG δηλαδή είναι on-line operation (όπερ σημαίνει ότι δεν γίνονται locks που να επηρεάζουν την ομαλή λειτουργία του πίνακα) όμως παίζει μόνο στο leaf-level του index.

Επίσης, κατά το ALTER INDEX και CREATE INDEX υπάρχουν και κάποια index options όπως ONLINE = {ON | OFF}, ALLOW_ROW_LOCKS
= {ON | OFF}, ALLOW_PAGE_LOCKS = {ON | OFF} και MAXDOP = number_of_processors

Σε ότι αφορά τα indexes, μια αλλαγή που έχει να κάνει με το performance είναι ότι πλέον μπορούμε να συμπεριλάβουμε σε ένα nonclustered index και πεδία που δεν είναι κλειδιά. Μάλιστα, αυτά τα πεδία δεν προσμετράνε στον περιορισμό των 16 πεδίων ανά index. Ουσιαστικά, αυτό που γίνεται είναι να «ανεβαίνουν» τα πεδία από τα data pages του πίνακα, στα leaf-pages του index και έτσι, μπορούμε να δημιουργήσουμε ευκολότερα covering indexes. Ο τρόπος για να κάνουμε τα προηγούμενα είναι στο CREATE INDEX statement να προσθέσουμε στο τέλος ένα INCLUDE με τα πεδία που μας ενδιαφέρουν.

Τέλος, μπορούμε να δημιουργήσουμε και partioned indexes ακολουθώντας σχεδόν την ίδια διαδικασία με τα partitioned tables.

Το partitioning γίνεται οριζόντια δηλαδή ένα διαφορετικό set από εγγραφές υπάρχει σε κάθε partition και κάθε ένα από αυτούς κατοικοεδρεύει σε διαφορετικό filegroup.

To πλεονέκτημα είναι ότι είναι ευκολότερη η διαχείριση πινάκων μεγάλου όγκου δεδομένων αφού οι ενέργειες που κάνουμε περιορίζονται μόνο στους πίνακες που αφορούν.

Επίσης, ο query optimizer όταν τρέχει σε συστήματα με 8 επεξεργαστές και άνω, μπορεί να προσαρμόζει το execution plan ώστε να εκτελούνται παράλληλα queries στα partitions.

Πως γίνεται η δουλειά

Αρχικά χρειαζόμαστε ένα PARTITION FUNCTION. Αυτό καθορίζει ποια data πάνε σε ποιό partition.

[code language="T-SQL"]
CREATE PARTITION FUNCTION OrderDatePF (datetime)
AS RANGE LEFT FOR VALUES ('1/1/1997', '1/1/1998')
[/code]

Δηλαδή θα σπάσουμε τον πίνακα Orders σε τρεις ομάδες μία ως (προσοχή, όχι «ως και» - RANGE RIGHT) 1/1/1997, μία  από 1/1/1997 ως 1/1/1998 και μία από 1/1/1998 ως σήμερα

Κατόπιν, χρειαζόμαστε ένα PARTITION SCHEME το οποίο θα αντιστοιχήσει τα table partitions σε ανάλογα filegroups

[code language="T-SQL"]
CREATE PARTITION SCHEME OrderDatePS
AS PARTITION OrderDatePF TO (filegroup1, filegroup2, filegroup3)
[/code]

Και τέλος φτιάχνουμε τον πίνακα που θα γίνει partitioned

[code language="T-SQL"]
CREATE TABLE OrdersHistory(
                OrderID int PRIMARY KEY,
                CustomerID nchar(5),
                EmployeeID int ,
                OrderDate datetime ,
                RequiredDate datetime ,
                ShippedDate datetime ,
                ShipVia int ,
                Freight money ,
                ShipName nvarchar(40) ,
                ShipAddress nvarchar(60) ,
                ShipCity nvarchar(15) ,
                ShipRegion nvarchar(15),
                ShipPostalCode nvarchar(10) ,
                ShipCountry nvarchar(15))
ON OrderDatePS(OrderDate)
[/code]

Αν θα τρέξετε το παραπάνω όμως δεν θα παίξει… Θα πρέπει να το αλλάξετε να γίνει έτσι:

[code language="T-SQL"]
CREATE TABLE OrdersHistory(
                OrderID int ,
                CustomerID nchar(5),
                EmployeeID int ,
                OrderDate datetime ,
                RequiredDate datetime ,
                ShippedDate datetime ,
                ShipVia int ,
                Freight money ,
                ShipName nvarchar(40) ,
                ShipAddress nvarchar(60) ,
                ShipCity nvarchar(15) ,
                ShipRegion nvarchar(15),
                ShipPostalCode nvarchar(10) ,
                ShipCountry nvarchar(15),
CONSTRAINT PK_OrdersHist PRIMARY KEY CLUSTERED
(
                OrderID ASC,
                OrderDate
))
ON OrderDatePS(OrderDate)
[/code]

Το πιάσατε το κόλπο;

Εδώ η δουλειά έχει τελειώσει. Μένει να τον γεμίσουμε:

[code language="T-SQL"]
INSERT INTO OrdersHistory
SELECT * FROM Orders
[/code]

Και πλέον μπορούμε να τρέξουμε τα query μας.

[code language="T-SQL"]
SELECT OrderID, $partition.OrderDatePF(OrderDate) Partition
FROM OrdersHistory
[/code]

Αυτό το [code language="T-SQL"]$partition.OrderDatePF(OrderDate)[/code] τι είναι; Ένα function που μας επιστρέφει τον αριθμό του partition που βρίσκεται το row…

Το ωραίο της υπόθεσης το άφησα για το τέλος… Η λύση αυτή διαφέρει πολύ σε σχέση με το να κάνει κάποιος το ίδιο σενάριο χειροκίνητα και να συντηρεί τους πίνακες με triggers ή με INSERT INTO SELECT FROM statements. Με το νέο τρόπο, είναι ταχύτατη η μεταφορά δεδομένων από partition σε partition γιατί τα data δεν μεταφέρονται «φυσικά» αλλά απλώς αλλάζουν τα metadata που έχουν να κάνουν με την τοποθεσία αποθήκευσης…

Πόσο καιρό λέω τώρα να αρχίσω να γράφω στο blog μου, μία το ένα, μία το άλλο, τελικά τώρα ξεκινάω.

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