A Fine Slice Of SQL Server
Chad Callihan gets the lead out:
Imagine we want to find gaps in post history for users. If a user is posting every day, we’re happy and can assume they’re happy. If they’re only posting once every few weeks, we want to investigate why that is.
Read on to see how you can do this with the LEAD() function.
Steve Jones cuts out the duplication:
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.
Read on to see how you can use WINDOW to replicate window frames, which can be quite helpful for larger queries. It doesn’t change the performance profile but does make the code more readable.
Deborah Melkin builds a report query:
I’ve been doing a lot of work recently where I have long running processes where I need to have visibility on where we are during the process as well as being able to provide a report we can use to find out if there were any errors, how long each step took, and other useful information that we can use later. My solution was to create an audit log table where I simply added a row before and after each step, recording the information I had available. When the processes are done, I create a query to run over the table returning the values I want to see.
Deb also throws in the only ordered set function in SQL Server, STRING_AGG().
When I first used window functions back in SQL Server 2005, I was in awe. I had always used inefficient self-joins to calculate things like running totals, and these really didn’t scale well with size-of-data. I quickly realized you could also use them for ranks and moving averages without those cumbersome self-joins, elaborate sub-queries, or #temp tables. Those all have their place, but window functions can make them feel old-school and dirty.
I’d also recommend learning more about the APPLY operator as well, as it can, depending on the circumstances, be even more effective than window functions (combined with common table expressions) for some of the use cases.
Rod Edwards shows off a great use case for LAG():
I often find myself using windows functions in order to group data in wierd and wonderful ways that a simple GROUP BY can’t do… however, the example below is one I came across quite recently on my travels, I was asked to have a look at some code for optimization opportunities. And in this case, thankfully there were some.
Read on for a lengthy cursor, followed by a much less lengthy (and much faster) window function.
Comments closedAaron Bertrand’s speaking my language:
I’ve written about
UNPIVOTbefore – see Use SQL Server’s UNPIVOT operator to help normalize output and Use SQL Server’s UNPIVOT operator to dynamically normalize output. It’s a powerful language feature that lets you flip results sideways, sort of like the opposite ofPIVOT. This is great if you have columns likePhone1andPhone2but want to collapse them into one column. One of the challenges is that if you have other columns you also want to collapse similarly, likeEmail1andEmail2, you need to add an additionalUNPIVOToperator or use a different approach. Is there a way to do this in a less complicated way?
Click through for the answer. And a free Curated SQL protip: if you ever want linked here, write an article about a good use case for the APPLY operator. I’m a sucker for those.
Joe Celko explains how NTILE() works:
The SQL
NTILE()is a window function that allows you to break a table into a specified number of approximately equal groups, or<bucket count>. For each row in a grouping, theNTILE()function assigns a bucket number representing the group to which the row belong starting at one.
Click through for the explanation, including what it is and isn’t, as well as several examples.
Comments closedChad Callihan finds the DDL for a view:
I recently faced a situation where I had to track down the definition of a particular SQL view for various databases. I didn’t want to click through each database in SSMS to gather the information. I thought I would write a query that I could use to save some clicks and gather what I needed for each database.
The first part of that task, querying for the view definition, may be a bit tricky. I would venture to guess it may not be in the first couple of places you would think to look. Let’s walk through how we can use a query to retrieve the definition of a view.
Just make sure that you have newline retention on or else your view definition is all going on one line.
Comments closedHugo Gruson draws some comparisons:
The debate regarding the use of
forloops versus theapply()function family (apply(),lapply(),vapply(), etc., along with their purrr counterparts:map(),map2(),map_lgl(),map_chr(), etc.), has been a longstanding one in the R community.While you may occasionally hear that
forloops are slower, this notion has already been debunked in other posts. When utilized correctly, aforloop can achieve performance on par withapply()functions.However, there are still lesser-known reasons to prefer
apply()functions overforloops, which we will explore in this post.
Read on for an important caveat, and then several reasons to prefer apply() (or purrr’s counterparts). H/T R-Bloggers.
Steve Jones points out one of the changes to T-SQL I really like:
This assumes I’ve added a table called dbo.Order with a PK of OrderID.
However, I can do this in the CREATE TABLE statement, like shown below. I add a new section after a column with the CONSTRAINT keyword. Then I name the constraint, which is always a good practice. I can then add the FK keyword, the column and the references that connects this child column to the parent column.
This came about in SQL Server 2014, along with In-Memory OLTP and the ability to create indexes inline with the table create script. It’s a minor quality of life thing but I do enjoy it.
Comments closed