Press "Enter" to skip to content

Category: T-SQL

SQL Injection without Dynamic SQL

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 closed

Dropping a Column as a Metadata Operation

Max 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 closed

Enabling Database-Level Change Tracking

Tim 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 closed

Isolation Levels and Dynamic SQL

Max 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 with sys.sp_executesql, and set the isolation level there, the dynamic code will run under the READ 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 closed

Reading SQL Server Error Logs

Thomas 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 closed

Rounding to Intervals in Various Languages

Dave 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).

Comments closed

Pitfalls with Window Functions

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 closed

Spaces in CHAR Columns

John 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

Procedure Parameters: Optional and Required

Kenneth Fisher takes us through procedure parameters:

If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it’s pretty easy to make some parameters required and others optional. You simply give them a default value.

Kenneth also points out that functions don’t behave this way, and shows how to handle parameters where you don’t want to accept NULL under any circumstances. This is useful when NULL is just a placeholder for “I don’t really want to use this parameter” but the application doesn’t know how to avoid sending the parameter in the first place.

Comments closed