Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #18 – My CTE

    It’s time for T-SQL Tuesday again, and it’s number 18. Hard to believe it’s been a year and a half since Adam Machanic (blog | @AdamMachanic) thought of the idea. I’ve participated in most and it’s something I look forward to each month. This month Bob Pusateri hosts the party with the theme of CTEs. No, it’s not thermal unit of expansion, and I hated chemistry.

    My CTE

    When CTEs were introduced, I thought they were a great idea. They made it much easier to write complicated queries that might need derived tables. In the past, writing something like this was hard to read.

    SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
     FROM Production.Product p
      INNER JOIN ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                     FROM Production.ProductCostHistory ph
                       INNER JOIN Production.ProductInventory pri
                         ON ph.ProductID = pri.ProductID
                    WHERE StandardCost > 10
                 ) b
        ON p.ProductID = b.ProductID
      INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
      WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

    A CTE can make this much easier to keep track of, especially in places where you don’t want to create a view instead.

    WITH ProductCTE
    AS ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                     FROM Production.ProductCostHistory ph
                       INNER JOIN Production.ProductInventory pri
                         ON ph.ProductID = pri.ProductID
                    WHERE StandardCost > 10
    ) SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
     FROM Production.Product p
      INNER JOIN ProductCTE b
        ON p.ProductID = b.ProductID
      INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
      WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

    I know this isn’t a great example, but by moving subqueries to a CTE structure, the end query is easier to debug and read.

    Top X of a Group

    Suppose you have a small result set of something like this.

    CREATE TABLE Books
    ( BookID INT IDENTITY(1,1) , BookName VARCHAR(200) , Genre VARCHAR(50) , reads INT ) go INSERT Books SELECT 'Old Man''s War', 'Sci-Fi', 200
    INSERT Books SELECT 'Ender''s Game', 'Sci-Fi', 345
    INSERT Books SELECT 'Red Thunder', 'Sci-Fi', 143
    INSERT Books SELECT 'Quarter Share', 'Sci-Fi', 25
    INSERT books SELECT 'The Enemy', 'Thriller', 67
    INSERT books SELECT 'The Hunt for Red October', 'Thriller', 678
    INSERT books SELECT 'Bad Luck and Trouble', 'Thriller', 545
    INSERT books SELECT 'Game of Lions', 'History', 644
    INSERT books SELECT 'The Rise of Theodore Roosevelt ', 'History', 67
    INSERT books SELECT 'An American Life: The Autobiography', 'History', 267

    Suppose I wanted to top two books from each genre, ranked by reads. A TOP 2 won’t work because that doesn’t allow you to specify groups. However using ROW_NUMBER and an OVER clause in a CTE, this becomes an easy query.

    WITH BookRanks AS ( SELECT b.BookID
          , b.BookName
          , b.Genre
          , b.reads
          , ROW_NUMBER() OVER (PARTITION BY b.genre ORDER BY reads DESC) AS Counter FROM Books b
     ) SELECT bookID
          , Genre
          , reads
          , bookname
      from BookRanks
       WHERE counter <= 2

    That gives me an easy to read result set:

    bookID  Genre    reads bookname

    ——- ——– —– ———————————————————-

    8       History  644   Game of Lions

    10      History  267   An American Life: The Autobiography

    2       Sci-Fi   345   Ender’s Game

    1       Sci-Fi   200   Old Man’s War

    6       Thriller 678   The Hunt for Red October

    7       Thriller 545   Bad Luck and Trouble

    Older T-SQL Tuesday Topics

    Just a quick list of the past topics and the roundups.

  • T-SQL Tuesday #18 Coming Next Week

    Other than the week I hosted it, I’ve been caught off guard by T-SQL Tuesday most months. This time I caught Bob’s Twitter announcement about #18. The topic this month is CTEs, so you have a week to get things ready.

  • T-SQL Tuesday #17 – APPLYing Yourself to T-SQL

    TSQL2sDay150x150It’s T-SQL Tuesday again, and this month Matt Velic is the host. His topic this month is the APPLY operator, after a challenge from Adam Machanic that you are not that proficient in T-SQL if you don’t know how to use this operator. I agree with Adam, and I think APPLY was an amazing addition to the T-SQL language.

    If you’re not sure what T-SQL Tuesday is all about, check out Adam’s initial T-SQL idea and post on the monthly blog party. T-SQL Tuesday is the second Tuesday of every month and the host rotates.

    You can also follow T-SQL Tuesday on Twitter with the #tsql2sday hashtag.

    APPLY

    The APPLY operator is one that I wished had been available in SQL 7/2000. There were many times when you were trying to apply a result set to a function and there was no easy way to do this. Most of the time this resulted in some type of cursor/temp table solution to make things work.

    One classic example was in trying to determine the SQL that someone had executed when they were blocking another user. The old sp_who2 gave limited information and often we were query a blocking tree and then start sending SPIDs through dbcc inputbuffer to get an idea of what SQL queries were being run.

    APPLY doesn’t help with DBCC, but it does help in other ways. In a modern twist to this problem, you can take a plan handle and run it through sys.dm_exec_sql_text to get the SQL that was executed

    If I did that for one of the connections I have locally, I could get something like this:

    SELECT *
 FROM sys.dm_exec_sql_text(0x010005003E60AD1C901E7D81000000000000000000000000)

    Which will give you this:

    tsqltues_code2

    Now, if you have a whole list of data, say perhaps a list of everyone connected from sys.dm_exec_connections, you can combine these two together.

    SELECT a.session_id
    , a.num_reads
    , a.num_writes
    , b.text
 FROM sys.dm_exec_connections a
   CROSS APPLY sys.dm_exec_sql_text(a.most_recent_sql_handle) b

    From this, you’ll get some result similar to this one:

    tsqltues_code1

    Note that you can’t join these two items together because this doesn’t work:

    SELECT *
 FROM sys.dm_exec_sql_text

    It returns an error:

    Msg 216, Level 16, State 1, Line 3

    Parameters were not supplied for the function ‘sys.dm_exec_sql_text’.

    You have to pass in a parameter, which means that either you create some cursor or loop to do this, or use the power of APPLY.

  • T-SQL Tuesday Reminder – Coming next week

    Matt Velic is hosting T-SQL Tuesday this month. You have a week to get a post ready on the apply operator.

    To help you out, here’s the BOL page on Using the Apply operator. It’s an operator that essentially lets you invoke a table valued function for each row of a result set. Not necessarily the most efficient way to get some things done, but it can be a powerful way of combining complex data that requires a function to interpret.