Voice of the DBA

Tag: syndicated

A New Word: Vaucasy

vaucasy – n. the feat that you’re little more than a product of your circumstances, that for all the thought you put into shaping your believes and behaviors and relationships, you’re essentially a dog being trained by whatever stimuli you happen to encounter, reflexively drawn to whoever gives you reliable hits of pleasure, skeptical of ideas that make you feel powerless.

I think a lot about how my life has gone, and how I react to things. My thoughts, my behaviors, and how my past shapes me. I do try to learn more, and I think I do a good job.

I’m also proud of watching my kids grow and change as adults. When I look at them, I think they have a really interesting mix of things they inherited from my wife or I, and also from their experiences. They are all very different, but similar.

Vaucasy is certainly something that I think affects all of us. There is a very human need to react to simuli, looking for pleasure. I also think we can change that and put our thoughts into shaping behaviors and thoughts, but it takes effort.

It’s worth it.

From the Dictionary of Obscure Sorrows

November 17, 2023
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.

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.

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

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.
November 15, 2023
T-SQL Tuesday #168–Using Window Functions
I 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.
November 14, 2023
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

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

November 13, 2023