A Fine Slice Of SQL Server
Bert Wagner is on a quest to find out who moved his cheese:
Have you ever wondered who was the last person (or process) to modify a piece of data in your database?
SQL Server offers plenty of system views and functions that provide insight into how your server is running and how your queries are performing. However, it doesn’t offer much information about who last modified your data.
There are a few workarounds, though they aren’t great.
Comments closedItzik Ben-Gan wraps up an outstanding series:
When people want to pivot data using T-SQL, they either use a standard solution with a grouped query and CASE expressions, or the proprietary PIVOT table operator. The main benefit of the PIVOT operator is that it tends to result in shorter code. However, this operator has a few shortcomings, among them an inherent design trap that can result in bugs in your code. Here I’ll describe the trap, the potential bug, and a best practice that prevents the bug. I’ll also describe a suggestion to enhance the PIVOT operator’s syntax in a way that helps avoid the bug.
If you use the PIVOT operator, you definitely want to read this article.
Erik Darling has a card trick for us:
I always try to impart on people that SQL injection isn’t necessarily about vandalizing or trashing data in some way.
Often it’s about getting data. One great way to figure out how difficult it might be to get that data is to figure out who you’re logged in as.
There’s a somewhat easy way to figure out if you’re logged in as sa.
Wanna see it?
Of course you do.
Comments closedMax Vernon takes us through column dropping:
Dropping a column that is not referenced by any other object lets the storage engine simply mark the column definition as no longer present. Deleting the meta-data invalidates the procedure cache. Any query that subsequently references the affected table will result in the plan for that query be recompiled. The recompile operation can only return columns that currently exist in the table. As a result, the storage engine skips the bytes stored in each page for the dropped column, as if the column no longer exists.
This has some nice benefits in practice around minimizing deployment-releated downtime.
Comments closedTim Weigel continues a series on change tracking:
If you don’t provide a retention period, SQL Server’s default is 2 days. Auto-cleanup defaults to ON unless you tell it otherwise.
Easy!
The table level commands aren’t any more complicated. Before we get started, please note that change tracking requires a primary key on the table you want to track. This is reasonable – you need some kind of unique identifier to tell you which row has changed.
Read on for the scripts and further explanation.
Comments closedMax Vernon points out how transaction isolation levels work when combined with sp_executesql:
Imagine you have a piece of code where you don’t care about the downsides to the “read uncommitted” isolation level, and do your due diligence by adding
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;at the start of your code. The code following that statement will run under that isolation level, as expected. However, if you call dynamic T-SQL withsys.sp_executesql, and set the isolation level there, the dynamic code will run under theREAD UNCOMMITTED, however the isolation level will not be changed for the calling code. In other words, be careful about where you set the isolation level.
Click through for a demonstration of this.
Comments closedThomas Rushton has a script for us:
Why Script This? What’s Wrong With SSMS’s GUI?
Well, although SSMS does allow you to look at the error logs, it’s not very helpful for filtering – you can only filter for items that match, rather than exclude items. There are a few other filters as well – I guess the whole thing is just a wrapper around xp_readerrorlog below…
But Thomas has a better way for us.
Comments closedDave Mason doesn’t like rounding to intervals very much in T-SQL:
If I had to choose any of these options for production, I’d probably go with Query 1, just because I think it would make the most sense to any other developer that might encounter it (including future me, who probably wouldn’t remember writing the code). But I’m not really thrilled with any of the options. The one thing T-SQL has going for it though, is the relative ease for truncating the TIME off of a DATETIME by casting to DATE and back to DATETIME. I couldn’t find anything like this in DAX.
Dave also shows how to do this in DAX and Powershell.
Another alternative that Dave doesn’t mention is to invert the problem: if you have a fixed set of intervals you care about (e.g., 15-minute, 30-minute, hour, 4-hour, etc.), you can create a time table. This is like a date table but contains times of the day where you’ve precalculated the intervals. Then you join to the time table and have your results right there. If you do go this route, I’d try to keep the grain of the time table as shallow as possible, maybe using DATETIME2(0) instead of DATETIME2(7).
Itzik Ben-Gan takes us through two issues you might run into when using window functions:
There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. One pitfall has to do with calculations of running totals where you get a window frame with the implicit RANGE option. Another pitfall is somewhat related, but has more severe consequences, involving an implicit frame definition for the FIRST_VALUE and LAST_VALUE functions.
There’s a lot going on in these two examples, so read on.
Comments closedJohn McCormack wants to store a single space in a CHAR(1) column:
I was asked by a colleague why his where clause wasn’t being selective when filtering on a space value. The column was a char(1) data type. To understand the curious case of the space in char(1), we need to understand how the char data type works and also a bit more about the need for it in this scenario.
The ANSI standard makes sense, but it is something you have to keep in mind in cases like this.
Comments closed