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.

 

Advertisements

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…


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


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

Εύκολη CSV λίστα


Ένα μικρό κομματάκι T-SQL για γρήγορη δημιουργία CSV λίστας.

Με αυτόν τον τρόπο, γλυτώνουμε από το iteration μέσα στο resultset όπου κι αν το κάνουμε.

   DECLARE @authors varchar(400)
   SELECT @authors = ''

   SELECT @authors = @authors + ', ' + au_lname + ' ' + au_fname
   FROM authors

   SELECT @authors = SUBSTRING(@authors, 3, 400)

   PRINT @authors

 

Δεν θυμάμαι που το βρήκα την πρώτη φορά για να αποδώσω τα εύσημα!


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.


Ranking Functions


Πολλές φορές έχουμε να αντιμετωπίσουμε 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.


APPLY relational operator


Ένας από τους κυριότερους λόγους που πολλοί 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.