A Fine Slice Of SQL Server
Steve Jones does a bit of aggregation:
I looked at row_number() in a previous post. Now I want to build on that and do some counting of rows with COUNT() and the OVER clause. I’ll show how this differs a bit from a normal aggregate.
Read on to see one example of how aggregation window functions (available in SQL Server since 2012) are so powerful.
Comments closedDennes Torres tries out T-SQL notebooks:
T-SQL Notebooks is one of the new features announced during FabCon Europe.
The most distracted could miss the fact this is a new feature at all. Yes, it is. Notebooks were capable to support Spark SQL, but T-SQL is something new.
The main examples being announced are built with data warehouses, but let me confirm and highlight this:
T-SQL Notebooks support lakehouses as well.
There is at least one limitation: DML is not supported with lakehouses.
Saving my rant about lakehouses vs warehouses in Fabric, do read what Dennes has to say about T-SQL notebooks as they exist today.
Comments closedDavid Fowler explains an error:
Recently I received a cry for help over Teams. The issue was that an application was throwing up the following SQL error,
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
I’ll be honest, that’s not one that I had seen before but it seemed pretty self explanatory. the query was just too complex for SQL to cope with. I asked what the query was, the answer was something similar to the snippet below,
Read on to learn what the problem was, as well as David’s answer. David had a simple rewrite retaining the IN clause, though you could also rewrite this with an INNER JOIN or even an EXISTS. One of those two alternative approaches might have a better performance profile, though there are no guarantees.
In this video, I show how to perform a variety of database backup operations via T-SQL, as well as how (and why) to back up to NUL and how to back up a database to a network share.
This one is not quite as lengthy as the prior video in the series: just 20 minutes instead of 30. That said, I do cover quite a bit of content around taking backups, something that every infrastructure DBA should be familiar doing.
Comments closedI started using Common Table Expressions (CTEs) in Oracle before Microsoft SQL Server and found them easier to read than a derived table. Something about reading from the top down just clicked in my brain. As the years have passed, I’m less of a fan for one reason: troubleshooting a chained CTE with several links is hard—especially when it’s someone else’s code. Even the ones I wrote years ago fill me with unease when something breaks. Is there an easier way to break down each link in the chain when things go wrong?
This is, I would argue, the biggest inconvenience around using common table expressions. The fact that SQL Server cannot materialize the contents of CTEs is likely a more prevalent problem, but challenges in how you can troubleshoot the individual parts of common table expressions are real.
Comments closedBrent Ozar thinks about consolidation:
I’ve got a BEGIN TRAN in there before the updates just so I can test the same queries repeatedly, and roll them back each time. The execution plan for the updates is quite nice: SQL Server divebombs into the supporting indexes:
Relatively few rows match, so our query does less than 1,000 logical reads – way less than there are pages in the table. In this case, separate UPDATE statements make sense.
Brent then continues with, but what do we do when we need to perform multiple independent scans of the same table? Read on for Brent’s answer, but definitely check out Thomas Franz’s comments, which indicate a potential complicating factor.
Comments closedSteve Stedman lays out a warning:
When writing SQL queries, it’s easy to focus on getting the right results without thinking too much about performance. One common mistake that can lead to significant slowdowns is using functions in the
JOIN ONclause of aSELECTstatement.While SQL Server supports a wide range of built-in functions, using them incorrectly—especially in the join conditions—can severely impact performance. Let’s break down why this happens, how it can affect your query speed, and what you can do to avoid it.
Read on for the answer.
Comments closedSteve Jones enumerates a result set:
I’m going to use some fun data for me. I’ve been tracking my travels, since I’m on the road a lot. I’m a data person and part of tracking is trying to ensure I’m not doing too much. Just looking at the data helps me keep perspective and sometimes cancel (or decline) a trip.
In any case, you don’t care, but I essentially have this data in a table. As you can see, I have the date of travel, the city, area, etc. I also have a few flags as to whether I was traveling that day, if I spent a night away from home, and how far I was.
Read on for a few trials with ROW_NUMBER().
Olivier Van Steenlant runs a test:
In the previous data recipe, Create a Test Class & First Unit Test with tSQLt, we created our very first T-SQL Unit Test to test the database collation. In this data recipe, we will test the execution of a Stored Procedure. Specifically, we will validate what happens when a new User is added to the user dimension.
Click through to see how it all works.
Comments closedChad Callihan checks out a pair of functions new to SQL Server 2022:
If you haven’t experimented much with SQL Server 2022, you may not be familiar with the new DATE_BUCKET and DATETRUNC functions. Both of these functions are useful when it comes to aggregating data. Let’s take a look at each function and walk through a few examples.
The DATE_BUCKET() function takes a bit of time getting used to, especially when you don’t use clean intervals like 1 hour or 1 minute. DATETRUNC(), meanwhile, is something I’ve wanted for quite some time.