Voice of the DBA

Author: way0utwest

  • Deploying Indexes with SQL Compare

    I suspect many people assume this is the case, but a customer recently asked if SQL Compare handles indexes. It does, and this post shows the basics of index comparisons with no filters.

    This is a part of a series of posts on SQL Compare on my blog. You can read other posts I’ve written by clicking the link.

    The Setup

    I have two databases that are completely synced from a schema perspective.

    2023-10-30 13_56_53-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

    I’ll now create an index in Compare1. This is a simple index on a single table. I use this code:

    CREATE INDEX IDX_mychar ON dbo.MyTable (Mychar)

    Once I refresh the compare, I see this. Note that I’ve selected the table that has a difference and it shows the new index. This bottom left shows the scripted version of the code I ran above.

    2023-10-30 13_58_13-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

    If I create the deployment script, I see the index in it, as shown here:

    2023-10-30 13_59_03-Deployment

    By default, SQL Compare includes almost all objects and that includes indexes. There are options to change the behavior with indexes, and I’ll cover those in future posts. You can also set a filter that might exclude indexes (or include those), but those are also future posts.

    SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an evaluation today.

  • Republish: Can Data Save the World

    I still in Seattle attending the PASS Data Community Summit 2023. I decided to republish Can Data Save the World? because I do think data is important for improving all aspects of the world. It’s not all we need, but it does matter. The more we measure and learn, the better decisions we can potentially make.

  • T-SQL Tuesday #168–Using Window Functions

    tsqltuesdayI am the host for T-SQL Tuesday this month, and I hope that a lot of people like the topic. This idea actually came to me earlier this year when I happened to see someone ask about a T-SQL problem and get an answer using a Window function. This person mentioned they hadn’t used the window function before, and I wondered how many people haven’t even tried using the OVER() clause with a window function.

    I also saved the idea of window functions just in case I didn’t have a host, and I realized a few months ago November was blank. So I created an invitation for technical solutions using window functions, hopefully mature solutions you’ve use many times.

    If you want to host, contact me, and send me your blog link and I’ll get you scheduled. FYI, I’m looking for people in the second half of 2024, so it’s not an immediate need.

    Cleaning Up Window Functions

    I don’t write a ton of code, and I don’t have any really cool solutions, but I did want to highlight one thing from SQL Server 2022: the WINDOW clause.

    In the past we’ve often had code like this:

    WITH    HRCTE
           AS ( SELECT   hrorder = ROW_NUMBER() OVER ( PARTITION BY p.franchName ORDER BY HR DESC )
                       , p.nameFirst
                       , p.nameLast
                       , p.franchName
                       , p.HR
                FROM     dbo.Players p
              )
     SELECT  hrdenserank = DENSE_RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , hrrank = RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , playercound = COUNT(p.nameLast) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
           , hrsum = sum(p.HR) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
     FROM    HRCTE
     WHERE   HRCTE.hrorder <= 5;

    That’s not bad, but I’ve written a bunch of repeating code in the OVER() clauses. In SQL Server 2022, I can do something more like this (just the outer query):

        SELECT  hrdenserank = DENSE_RANK() OVER frname
           , hrrank = RANK() OVER frname
           , playercound = COUNT(p.nameLast) OVER frname
           , hrsum = sum(p.HR) OVER frname
     FROM    HRCTE
     WHERE   HRCTE.hrorder <= 5
     WINDOW frname AS ( PARTITION BY HRCTE.franchName ORDER BY HR DESC );

    That, to me, is a cool maturity of the Windowing function capability in T-SQL. I can alias a window and reuse it in my code. This also makes I can make a few different windows and easily see which one is used with which aggregate.

  • Data Community Summit Week

    I’m up in Seattle this week for the PASS Data Community Summit 2023. This is almost an annual event for me. I’ve missed a few since 1999, but not many.

    This is my last trip of the year. It’s been quite a year of travel, and I’m glad not to have anything booked in the foreseeable future. I have one conference in late January, but I haven’t submitted for others or planned anything else for now.

    In any case, I have a DevOps in a Day tomorrow and then a few panels at the Summit, but mostly I’m free to enjoy the time with people. I am hoping to do some livestreaming at the event, but we’ll see if that works. If they give me access to the Summit channels Winking smile

    We’ll see if I can get some time to post pictures and thoughts here as well.