Jump to content

antonch

Administrators
 View Profile  See their activity

  • Posts

    1030

  • Joined

  • Last visited

  • Days Won

    7

 Content Type 

Blog Entries posted by antonch

  1. antonch
    Η λατρεία μου για τα poster είναι γνωστή. Ειδικά για αυτά που αφορούν τον SQL Server. Αν μάλιστα αυτά είναι χαριτωμένα, έξυπνα και λένε πολλά μου αρέσουν περισσότερο. Γενικά είμαι poster hunter.
    Χθες τυχαία ανακάλυψα αυτό που ακολουθεί το οποίο το έχει φτιάξει η Kendra Little (blog|twitter) το οποίο εξηγεί αρκετά όμορφα τα SQL Server Isolations Levels. Μπορείτε να το κατεβάσετε από το original link ή και από εδώ.

  4. antonch
    Σε τακτά χρονικά διαστήματα ή σε περιπτώσεις που υπάρχει στέρηση σε μνήμη, ένα thread αναλαμβάνει να κάνει τον καθαρισμό της buffer cache από τις σελίδες που δεν έχουν πρόσφατα χρησιμοποιηθεί.
    Το thread αυτό ονομάζεται Lazywriter και ο τρόπος με τον οποίο αποφασίζει ποιες είναι αυτές που θα φύγουν από την buffer cache είναι και ονομάζεται Least Recently Used (LRU) policy.
    Least Recently Used policy
    Στο header της κάθε σελίδας όταν αυτή είναι στη μνήμη αποθηκεύονται πληροφορίες που κρατάνε τις τελευταίες δύο φορές (time) που αυτή έχει χρησιμοποιηθεί. Επίσης υπάρχει ένας μετρητής ο οποίος κάθε φορά που γίνεται ο περιοδικός έλεγχος (Lazywriter) αυτός μειώνεται. Έτσι στην περίπτωση που χρειάζεται να ελευθερωθεί χώρος στη buffer cache οι σελίδες με τη μικρότερη τιμή στο μετρητή είναι αυτές που φεύγουν από αυτή.
    Αν θέλουμε να δούμε πόσο χρόνο (μέση τιμη) οι σελίδες μένουν στην buffer cache μπορούμε να το δούμε με την χρήση του Page Life Expectancy performance counter.
    Lazywriter operation
    Είναι ένα thread το οποίο τακτικά ελέγχει το μέγεθος του free buffer list.

    O SQL Server προσπαθεί να συντηρήσει ένα αριθμό από ελεύθερες σελίδες στην buffer cache με σκοπό να μπορεί να ανεβάζει σε αυτή γρηγορότερα σελίδες, αυτός ο αριθμός ελέγχεται από το free buffer list.

    Όποτε γίνεται ο συγκεκριμένος έλεγχος (ή γίνεται checkpoint ή read operation που χρειάζεται να διαβάσει σελίδες από το δίσκο και να τις ανεβάσει στην buffer cache) και δει ότι ο αριθμός αυτός είναι μικρότερος από αυτό που έχει το free buffer list τότε με βάση το LRU policy οι σελίδες αυτές φεύγουν από την buffer cache.

    Στην περίπτωση που μια σελίδα είναι dirty
    και εμπίπτει στο LRU policy τότε αυτή γράφεται στο δίσκο (data file) πριν χαρακτηριστεί σαν σελίδα που μπορεί να φύγει από την buffer cache.
  5. antonch
    Η διαδικασία αυτή όταν 
    γίνεται
    μεταφέρει τις χαρακτηρισμένες ως dirty
    pages από την μνήμη (buffer cache) στο δίσκο (data file(s)) και αλλάζει στο
    header τους το status από dirty σε clean. Δεν
    αδείαζει-καθαρίζει την buffer cache από αυτές.








  6. antonch
    Όταν μια σελίδα διαβάζεται από τον δίσκο και μπαίνει στην buffer cache θεωρείται ότι είναι μια Clean Page. Όταν όμως κάνω ένα transaction το οποίο την επηρεάζει (πάντα στην μνήμη) αυτή είναι μια Dirty Page (μπαίνει ένα mark στον header της) καθώς η εικόνα στην μνήμη είναι διαφορετική από αυτή στο δίσκο.
    Για να δω πόσες dirty pages έχω ανά βάση αρκεί να εκτελέσω το παρακάτω query που χρησιμοποιεί την sys.dm_os_buffer_descriptors
    SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
    FROM sys.dm_os_buffer_descriptors
    WHERE is_modified =1
    GROUP BY db_name(database_id)
    ORDER BY count(page_id) DESC
  7. antonch
    Η DMV αυτή δείχνει μια γράμμη για κάθε data page η οποία είναι στην buffer cache.
    Με το παρακάτω script μπορείς να δεις το ποσό σε MB που έχει δεσμευτεί από τις σελίδες που υπάρχουν για κάθε βάση μέσα στην buffer cache
    SELECT
    CASE database_id
    WHEN 32767 THEN 'ResourceDB'
    ELSE DB_NAME(database_id)
    END AS 'Database',
    COUNT(*)*8/1024 AS 'Cached Size (MB)'
    FROM sys.dm_os_buffer_descriptors
    GROUP BY DB_NAME(database_id),database_id
    ORDER BY 2 DESC
  8. antonch
    Το PAGEIOLATCH wait type δείχνει το χρόνο που χρειάστηκε για να διαβαστεί μια σελίδα από το δίσκο και να μπει στην μνήμη (buffer cache)
  9. antonch
    By
    Abhishek Sinha


    The Microsoft SQL
    Server Sustained Engineering team is proud to announce the release of SQL Server
    2008 R2 Cumulative Update 7.
    Cumulative Update 7 contains a roll-up of hotfixes released since the initial
    release of SQL Server 2008 R2.
    CU#7 KB Article:
    http://support.microsoft.com/kb/2489376 Understanding
    Incremental Servicing
    Model for SQL Server
    SQL Server Support Information:
    http://support.microsoft.com/ph/2855
    Previous
    Cumulative Update KB
    Articles:
    CU#6 KB Article:
    http://support.microsoft.com/kb/2489376 CU#5 KB Article:
    http://support.microsoft.com/kb/2438347 CU#4 KB Article:
    http://support.microsoft.com/kb/2345451 CU#3 KB Article:
    http://support.microsoft.com/kb/2261464 CU#2 KB Article:
    http://support.microsoft.com/kb/2072493 CU#1 KB Article:
    http://support.microsoft.com/kb/981355

    Abhishek
    Sinha
    Program Manager
    SQL Server
    Sustained Engineering
  11. antonch
    Λίγο πριν το Πάσχα λέω να κάνουμε ένα SQL Saturday Night το οποίο θα γίνει σύμφωνα με μια νέα φιλοσοφία όσον αφορά τον τρόπο διεξαγωγής του και συμμετοχής.

    Όσοι πραγματικά θέλουν να συμμετάσχουν στην ζωντανή παρουσίαση θα πρέπει να κάνουν registration ώστε να τους αποσταλθεί το link του Live Meeting.

    Η διαδικασία είναι απλή, και θα βελτιωθεί στο μέλλον. Με την ευκαιρία θα έρθετε σε επαφή με το SqlSchool.gr το οποίο είναι ακόμα στα πρώτα του βήματα και προδευτικά θα γεμίζει με υλικό. Φυσικά τα σχόλια και οι παρατηρήσεις σας είναι ευπρόσδεκτα.

    Όσοι θέλουν να κάνουν registration ας κάνουν κλικ στο δίπλα link

     

    Registration
  12. antonch
    Πριν από λίγο ήμουν με ένα φίλο και συνάδελφο και κάναμε διάφορα πραγματάκια σε μία βάση. Κάποια στιγμή θέλαμε να αλλάξουμε την τιμή από ένα πεδίο σε ένα record σε null και βαριόμουν να κάτσω να γράψω ένα update statement. Έτσι άνοιξα τον SSMS και με την γνωστή διαδικασία δεξι κλικ Edit πάνω στο πίνακα που θέλω στην βάση μου πήγα στο πεδίο και πάτησα Ctrl+0 όπου αμέσα γίνεται null η τιμή του και το μόνο που έχεις να κάνει είναι να πας στην επόμενη εγγραφή για να γινει commit η αλλαγή σου. Η κίνηση αυτή ξάφνιασε τον φίλο και συνάδελφο μιας και είναι από τους δυνατούς παίχτες έτσι σκέφτηκα να τα μοιραστώ με περισσότερους που ίσως δεν το γνώριζαν.
  14. antonch
    Με αφορμή το τελευταίο μου SQL Server Saturday Night που σαν θέμα είχε τον SQL Server Profiler και μπορείτε να το δείτε εδώ και στο οποίο αναφέρθηκα στο συγκεκριμένο θέμα αποφάσισα να γράψω το συγκεκριμένο ώστε να δώσω περισσότερες λεπτομέρειες.
    Όπως ανέφερα και στη παρουσίαση μου το ποια events θα διαλέξει κάνεις έχει περισσότερο να κάνει με την εμπειρία που έχει αλλά και την ικανότητα με αυτά να εντοπίσει το εν λόγω πρόβλημα.
    Προσωπικά επιλέγω αυτά που θα σας παραθέσω παρακάτω αλλά αυτό φυσικά δεν είναι το απόλυτο. Ο καθένας από εσάς μπορεί να προσθέσει ή να αφαιρέσει events με τα οποία πιστεύει ότι θα κάνει καλύτερα την αναζήτηση του στο θέμα μας.
    Για να διαβάσετε την συνέχεια του άρθρου κάνετε click εδώ
    Enjoy it!
    antonch
  15. antonch

    Margaritari #6

    By antonch,

    Τις τελευταίες ημέρες είχα την ευτυχία να ανακαλύψω μερικά ωραία ασχολούμενος με μια βάση
    1. Δήλωση πεδίου σε πίνακα σαν varchar(1) not null
    2. Δήλωση πεδίου σε πίνακα που κρατάει το τηλέφωνο σαν varchar(max) not null (μεγάλε που θα πάρεις τηλέφωνο?)
  18. antonch
    Το πρόβλημα

    Πριν από μερικές μέρες μια συνάδελφος ήρθε με το εξής πρόβλημα, ήθελε να δείξει κάποια δεδομένα σε ένα treeview control σε ένα web page. Στην ουσία ήταν μια ιεραρχία που από την δομή του πίνακα έβγαινε αρκετά εύκολα με ένα order by clause. Όμως δεν ήταν τόσο απλά τα πράγματα, ήθελε να υπάρχει ταξινόμηση ανά επίπεδο ιεραρχίας το οποίο ορίζονταν από ένα άλλο πεδίο.

    Για να εξηγήσουμε καλύτερα την πρόκληση αυτή ας έρθουμε να δούμε το πώς ήταν τα δεδομένα της

    Ολόκληρο το άρθρο θα το βρείτε εδώ καθώς χρειάστηκε να το αναθεωρήσω
  21. antonch
    Νέα CUs ανακοινώθηκαν πριν 2 ημέρες από την Microsoft για τον SQL Server 2005 & 2008 R2.
    Αυτά είναι τα εξής
    KB #2489375 - SQL Server 2005 SP3 CU #14 (9.00.4317) KB #2489409 - SQL Server 2005 SP4 CU #2 (9.00.5259) KB #2489376 - SQL Server 2008 R2 CU #6 (10.50.1765) Τα περισσότερα fixes υπάρχουν σε αυτό για τον SQL Server 2008 R2.
  22. antonch
    Posted @ 2/20/2011 9:31 PM By Kimberly L. Tripp
    Posted in SQL Server Magazine
    Πριν μερικές μέρες είχα δημοσιεύσει ένα άρθρο “Dynamic SQL ή μήπως μπορώ και αλλιώς;”. Σήμερα διάβασα το παρακάτω άρθρο της Kimberly Trip το οποίο καλύπτει μια άλλη πτυχή την οποία δεν είχα καλύψει στο δικό μου. Και τα δύο άρθρα είναι σωστά και έρχονται να επιβεβαιώσουν ότι τίποτα μα τίποτα δεν είναι defacto στον SQL Server. Στην ουσία για μένα είναι ακόμα μια επιβεβαίωση της γνωστής απάντησης που σε αρκετές περιπτώσεις δίνω και δεν είναι άλλη από το ΕΞΑΡΤΑΤΑΙ. Τα πάντα είναι θέμα ποιότητας και ποσότητας δεδομένων.!!!
     
    Question: This is a follow-up question to the Q&A post: Statistics, literals, recompilation… confusion! posted last week.
    Would it actually be better to use OPTION(RECOMPILE), rather than dynamic SQL (DSE), as in reality, both options are likely to re-compile each time, and forcing re-compilation (with OPTION(RECOMPILE)) does not have the security implications of dynamic string execution?
    Answer: Yes and no. Unfortunately, using only OPTION (RECOMPILE) as a single/uniform solution can be even more problematic. While recompilation can be helpful for some statements, it’s a waste when it’s unnecessary, it may eat up too much CPU and there are some statements that it just cannot help. In fact, if you have a system with a lot of dynamic statements and/or recompilations – you might make things worse. As a general practice I try not to use it and I tend to use it sparingly – only if I can’t come up with a better solution. For example, the optimal execution plan for some statements can vary between table scans and nonclustered index with [bookmark] lookups because of the volatility of the parameters *AND* the fact that a better index doesn’t exist. Sometimes I can create an index (usually a covering index) and make the plan more stable/consistent without requiring recompilation.
    And, there are other cases where OPTION (RECOMPILE) doesn’t help the execution plan because it’s the statement itself that is the problem. I see this often. As a generalization, I see this when the supplied parameters vary and a single procedure has been created to try and satisfy all of the possible parameter combinations (some of which might not be supplied at all). Usually the WHERE clause looks something like the following:
    WHERE (ColumnX = @variableX OR @variableX IS NULL)
    AND (ColumnY = @variableY OR @variableY IS NULL)
    AND (ColumnZ = @variableZ OR @variableZ IS NULL)
    …
    When the procedure ends up looking like this, I call it a multi-purpose procedure. The simple fact is that when the Transact-SQL statement itself is poorly written then often even recompilation can’t help. There are some cases where building the statement dynamically yields better results - especially in cases where the number of supplied parameters (vs. those which are null) changes from execution to execution.
    It’s in these cases where I BUILD the exact statement (with only the non-null parameters) instead of one that includes numerous variables that are set to NULL. To execute the constructed statement I’ll use either: dynamic string execution or sp_executesql. Choosing which one to use correctly requires knowledge of your data, some plan analysis and thorough testing.
    As a simple rule of thumb, use sp_executesql if the plan chosen by each specific set of parameters is consistent (you’ll only know this through thorough testing). When the distribution of the data for specific parameters causes the plan to change, then use dynamic string execution (EXEC(‘string’)) rather than sp_executesql to execute the statement. And, you’ll need to protect your code from SQL Injection. To do this effectively often requires other features such as: string protection (w/REPLACE and/or QUOTENAME()), EXECUTE AS, and login-less users.
    For more information on protecting from SQL Injection, check out this post: Little Bobby Tables, SQL Injection and EXECUTE AS
    For more information on multi-purpose parameters, check out this post: Stored Procedure Parameters giving you grief in a multi-purpose procedure?
    This is definitely a complicated subject and it requires three key things:
    Knowing your data Knowing your workload Knowing how SQL Server works It’s that last one that people aren’t always looking out for. I hope this posts helps you get closer!
    Enjoy,
    kt
  24. antonch
    Today I’m pleased to announce the availability of the SQL Server 2008 R2 Update For Developers January 2011 Update. This is a great resource for developers, trainers, consultants and evangelists who need to understand the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 from a developer perspective. It contains a rich set of presentations, demos, hands-on labs and videos that are perfect for self-paced learning or for conducting your own training. The January 2011 update includes lots of great new content and several usability improvements.
    The easiest way to get started with the training kit is to download it, install it, open default.htm and browse the kit for the content that you are most interested in. Many of the presentations and demos in the training kit include a video that you can watch to familiarize yourself with the content. When you are ready for some hands-on experience, try installing one of the demos or hands-on labs. Each of them includes a configuration wizard that simplifies installation. Check out the following sections for more on what’s new in this update.
    Ø New Content: Build Your First Microsoft BI Solution with SQL Server 2008 R2
    Ø Other Content in the Training Kit
    Ø Usability Improvements
    Ø Virtual Machine Information
    Ø What’s Next
    You can download the training kit now at the following URL:
    http://go.microsoft.com/?linkid=9710868
    This training kit is brought to you by DPE’s Data Platform Evangelism team. Please feel free to email us at [email protected] with your feedback and questions. We are particularly interested in hearing about use of this content to drive SQL Server developer adoption.
    Regards,
    Roger
    Roger Doherty | Sr. Technical Evangelist | Microsoft Corp.
  25. antonch
    Σαν developers αρκετές φορές ερχόμαστε αντιμέτωποι με προβλήματα που πρέπει να λύσουμε μέσα σε σύντομο χρονικό διάστημα. Τις περισσότερες φορές η λύση που επιλέγουμε για να το λύσουμε είναι αυτή που ξέρουμε καλύτερα, την έχουν χρησιμοποιήσει αρκετές φορές, την εμπιστευόμαστε περισσότερο ή αυτή ξέρουμε μόνο.
    Κάποιες φορές αυτή είναι ιδανική, καλή, μέτρια ή άστα να πάνε…
    Ας έρθουμε όμως στο προκείμενο…
    Πριν μερικές μέρες είχα να αντιμετωπίσω ένα θέμα το οποίο πάντα όταν ανακύπτει προκαλεί πολλά σχόλια, αρνητικά κυρίως, από συναδέλφους που βλέπουν το SQL Server σαν ένα μέσω αποθήκευσης και μόνο των δεδομένων, αυτό που εγώ λέω κουβά.
    Το πρόβλημα είναι το εξής «Έχω ένα ή περισσότερους πίνακες που θέλω να τους ρωτήσω με πολλαπλά φίλτρα τα οποία άλλες φορές θα έχουν τιμή και άλλες όχι. Με ποιο απλά λόγια αν το φίλτρο έχει τιμή τότε θέλω να είναι στο WHERE αλλιώς όχι».
    Αυτό όταν το ακούς ή το αντιμετωπίζεις, άμεσα το μυαλό σου πηγαίνει στο «θέλω δυναμικά να κτίζω το WHERE clause στο SELECT που θα κάνω».
    Αυτό σημαίνει αρκετά πράγματα τα οποία θα ήταν κουτό από μέρος μου να κάτσω να τα γράψω μιας και ο Erland Sommarskog (blog), MVP και αυτός στον SQL Server, έχει ήδη γράψει ένα καταπληκτικό post με τίτλο «The Curse and Blessings of Dynamic SQL», το οποίο και εγώ πριν αρχίσω την συγγραφή αυτού του post δεν γνώριζα την ύπαρξη του. Αλλά ευτυχώς είχα την προνοητικότητα να ρωτήσω τους άλλους MVPs αν κάποιος έχει γράψει κάτι για αυτό ώστε να εστιάσω την προσοχή μου σε αυτό που θέλω να αναλύσω χωρίς να γράψω όλη την ιστορία από την αρχή.Thanks Er!.
    Ας πάρουμε για παράδειγμα το εξής:
    Θέλω να έχω μια stored procedure που να παίρνει σαν παραμέτρους πεδία του πίνακα Customers και του πίνακα Orders από την Northwind και ανάλογα να κάνει αναζήτηση με το τι τιμές ή όχι έχω δώσει σε αυτές και να μου επιστρέφει το επιθυμητό αποτέλεσμα που στην περίπτωση του παραδείγματος μας θα είναι οι παραγγελίες ανά πελάτη.
    Αυτό σημαίνει ότι η συγκεκριμένη stored pocedure θα πρέπει να έχει την δυνατότητα να μην πάρει τιμές σε καμία από αυτές άρα θα πρέπει αυτές να είναι αρχικοποιημένες με μια τιμή που στην περίπτωση μας η τιμή αυτή θα είναι null. Έτσι ένα πιθανό stored procedure signature θα μπορούσε να είναι το παρακάτω
    CREATE PROC spGetCustomerOrders
    @CompanyName NVARCHAR(40)=NULL,
    @Country NVARCHAR(15)=NULL,
    @City NVARCHAR(15)=NULL,
    @Region NVARCHAR(15)=NULL,
    @OrderDate_Min DATETIME=NULL,
    @OrderDate_Max DATETIME=NULL,
    @Employees NVARCHAR(100)=NULL




    Το αποτέλεσμα που θέλω να επιστρέφει είναι το εξής query


    SELECT o.OrderID,
    o.EmployeeID,
    c.CustomerID,
    c.CompanyName,
    o.OrderDate,
    c.Country,
    c.City,
    c.Region
    FROM Customers c
    INNER JOIN Orders o ON c.CustomerID=o.CustomerID

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


    WHERE
    c.CompanyName LIKE @CompanyName + '%'
    AND
    c.Country = @Country
    AND
    c.City = @City
    AND
    c.Region = @Region
    AND
    o.OrderDate BETWEEN @OrderDate_Min AND @OrderDate_Max
    AND
    o.EmployeeID IN ()

    Αν σαν πρώτη σκέψη είχα το dynamic sql τότε μια πιθανή υλοποίηση θα ήταν η παρακάτω


    CREATE PROC spGetCustomerOrders @CompanyName NVARCHAR(40)=NULL,
    @Country NVARCHAR(15)=NULL,
    @City NVARCHAR(15)=NULL,
    @Region NVARCHAR(15)=NULL,
    @OrderDate_Min DATETIME=NULL,
    @OrderDate_Max DATETIME=NULL,
    @Employees NVARCHAR(100)=null
    AS

    SET NOCOUNT ON;

    DECLARE @select nvarchar(2000)
    DECLARE @where nvarchar(1000)

    SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101')
    SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231')

    IF ( @OrderDate_Min > @OrderDate_Max )
    BEGIN
    RAISERROR ('@OrderDate_Min is bigger than @OrderDate_Max',16,1)
    RETURN
    END

    SET @select = 'SELECT o.OrderID, o.EmployeeID, c.CustomerID, c.CompanyName, o.OrderDate, c.Country, c.City, c.Region FROM Customers c INNER JOIN Orders o ON c.CustomerID=o.CustomerID '

    -- ΓΙΑ ΝΑ ΕΙΜΑΙ ΑΣΦΑΛΕΙΣ ΕΔΩ ΘΑ ΠΡΈΠΕΙ ΝΑ ΕΛΕΞΩ ΓΙΑ SQL INJECTION

    SET @where = 'WHERE (o.OrderDate BETWEEN ''' + CONVERT(char(10),@OrderDate_Min,102) + ''' AND ''' + CONVERT(char(10),@OrderDate_Max,102) + ''')'
    if not @CompanyName is null SET @where += ' AND (c.CompanyName LIKE ''' + @CompanyName + '%' +''')'
    if not @Country is null SET @where += ' AND (c.Country = ''' + @Country + ''')'
    if not @City is null SET @where += ' AND (c.City = ''' + @City + ''')'
    if not @Region is null SET @where += ' AND (c.Region = ''' + @Region + ''')'
    if not @Employees is null SET @where += ' AND (o.EmployeeID IN ('+ @Employees + '))'
    SET @select += @where
    EXEC (@select)
    GO

    Και ο τρόπος εκτέλεσης της θα ήταν ο παρακάτω


    exec spGetCustomerOrders @CompanyName = 'c',
    @Country ='UK',
    @City =null,
    @Region =null,
    @OrderDate_Min =null,
    @OrderDate_Max =null,
    @Employees ='1,2,3,4'

    Ουφ κουράστηκα να γράφω και να προσέχω τα μονά quotes που ανοίγουν που κλείνουν να βάζω ακόμα ένα σε αυτά ώστε να συμπεριληφθούν στο string, α και να προσέξω για sql injection.


    Το τελευταίο θα μπορούσα εύκολα να το αποφύγω αν άλλαζα τον κτίσιμο του sql statement όπως παρακάτω


    CREATE PROC spGetCustomerOrders @CompanyName NVARCHAR(40)=NULL,
    @Country NVARCHAR(15)=NULL,
    @City NVARCHAR(15)=NULL,
    @Region NVARCHAR(15)=NULL,
    @OrderDate_Min DATETIME=NULL,
    @OrderDate_Max DATETIME=NULL,
    @Employees NVARCHAR(100)=null
    AS

    SET NOCOUNT ON;

    DECLARE @select nvarchar(2000)
    DECLARE @where nvarchar(1000)

    SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101')
    SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231')

    IF ( @OrderDate_Min > @OrderDate_Max )
    BEGIN
    RAISERROR ('@OrderDate_Min is bigger than @OrderDate_Max',16,1)
    RETURN
    END

    SET @select = 'SELECT o.OrderID, o.EmployeeID, c.CustomerID, c.CompanyName, o.OrderDate, c.Country, c.City, c.Region FROM Customers c INNER JOIN Orders o ON c.CustomerID=o.CustomerID '
    SET @where = 'WHERE (o.OrderDate BETWEEN @OrderDate_Min AND @OrderDate_Max)'

    if not @CompanyName is null SET @where += ' AND (c.CompanyName LIKE @CompanyName)'
    if not @Country is null SET @where += ' AND (c.Country = @Country)'
    if not @City is null SET @where += ' AND (c.City = @City )'
    if not @Region is null SET @where += ' AND (c.Region = @Region )'
    if not @Employees is null SET @where += ' AND (o.EmployeeID IN (@Employees))'
    SET @select += @where

    EXEC sp_executesql @select,N'@CompanyName NVARCHAR(40), @Country NVARCHAR(15), @City NVARCHAR(15), @Region NVARCHAR(15), @OrderDate_Min DATETIME, @OrderDate_Max DATETIME, @Employees NVARCHAR(100)', @CompanyName, @Country, @City, @Region, @OrderDate_Min, @OrderDate_Max, @Employees

    GO

    Και ο τρόπος εκτέλεσης της θα ήταν ο παρακάτω


    exec spGetCustomerOrders @CompanyName = 'c%',
    @Country ='UK',
    @City =null,
    @Region =null,
    @OrderDate_Min =null,
    @OrderDate_Max =null,
    @Employees =null

    Μήπως όμως θα μπορούσα να την γράψω αλλιώς; Χωρίς dynamic sql; Ας το δοκιμάσουμε.


    Αρχικά με ενοχλεί η παράμετρος @Employees που είναι nvarchar. Θα ήθελα να είναι array. Μα καλά θα μου πεις array σε T-SQL, τι πίνεις;


    Όντως η έννοια αυτή δεν υπάρχει σε T-SQL, όμως υπάρχουν οι πίνακες και τα table-value parameters. Έτσι φτιάχνω ένα δικό μου data type που θα είναι table data type και θα παίζει το ρόλο του array


    CREATE TYPE OrderEmployees AS TABLE (EmployeeID INT);
    GO

    Και θα αλλάξω την παράμετρο @Employees σε αυτό το table data type. Έτσι πλέον το stored procedure signature θα γίνει


    CREATE PROC spGetCustomerOrders @CompanyName NVARCHAR(40)=NULL,
    @Country NVARCHAR(15)=NULL,
    @City NVARCHAR(15)=NULL,
    @Region NVARCHAR(15)=NULL,
    @OrderDate_Min DATETIME=NULL,
    @OrderDate_Max DATETIME=NULL,
    @Employees OrderEmployees READONLY

    Για να ξέρω αν έχω rows στο @Employees table-value parameter δηλώνω την μεταβλητή @ HasEmployees σαν bit (Boolean) και την γεμίζω ανάλογα 0 δεν έχω, 1 έχω rows


    DECLARE @HasEmployees BIT
    SET @HasEmployees = CASE WHEN (SELECT COUNT(*) FROM @Employees)>0 THEN 1 ELSE 0 END

    Αρχικοποιώ και τις @OrderDate_Min, @OrderDate_Max ώστε να μπορώ να παίρνω όλα τα rows σε περίπτωση που δεν μου δώσουν τιμές


    SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101')
    SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231')




    Η υλοποίηση της θα μπορούσε να είναι η παρακάτω


    CREATE PROC spGetCustomerOrders @CompanyName NVARCHAR(40)=NULL,
    @Country NVARCHAR(15)=NULL,
    @City NVARCHAR(15)=NULL,
    @Region NVARCHAR(15)=NULL,
    @OrderDate_Min DATETIME=NULL,
    @OrderDate_Max DATETIME=NULL,
    @Employees OrderEmployees READONLY
    AS

    SET NOCOUNT ON;

    DECLARE @HasEmployees BIT
    SET @OrderDate_Min = ISNULL(@OrderDate_Min,'19000101')
    SET @OrderDate_Max = ISNULL(@OrderDate_Max,'99991231')
    SET @HasEmployees = CASE WHEN (SELECT COUNT(*) FROM @Employees)>0 THEN 1 ELSE 0 END

    SELECT o.OrderID, o.EmployeeID, c.CustomerID,c.CompanyName,o.OrderDate,c.Country,c.City,c.Region
    FROM Customers c
    INNER JOIN Orders o ON c.CustomerID=o.CustomerID
    WHERE
    ((c.CompanyName LIKE @CompanyName + '%') OR (@CompanyName IS NULL))
    AND
    ((c.Country = @Country) OR (@Country IS NULL))
    AND
    ((c.City = @City) OR (@City IS NULL))
    AND
    ((c.Region = @Region) OR (@Region IS NULL))
    AND
    (o.OrderDate BETWEEN @OrderDate_Min AND @OrderDate_Max )
    AND
    (CASE @HasEmployees
    WHEN 0 THEN 1
    ELSE
    CASE WHEN o.EmployeeID IN (SELECT EmployeeID FROM @Employees) THEN 1
    ELSE 0
    END
    END = 1 )
    GO

    Και η εκτέλεση αυτής


    DECLARE @e OrderEmployees
    -- Κάνω remark το insert αυτό αν θέλω να προσομοιάσω το γεγονός ότι δεν μου δίνουν τιμές στους υπαλλήλους
    INSERT INTO @e VALUES (8),(4)

    EXEC spGetCustomerOrders @CompanyName='c',
    @Country='UK',
    @City=NULL,
    @Region=NULL,
    @OrderDate_Min=NULL,
    @OrderDate_Max=NULL,
    @Employees=@e

    Το σημαντικό όμως είναι η διαφορά σε χρόνο εκτέλεσης καθώς η λύση που δεν περιέχει το dynamic sql είναι γρηγορότερη τουλάχιστον κατά 60%
×
×
  • Create New...