T-SQL

SQL Server Workarounds

Development, SQL Server, T-SQL
14 Comments
360 Questions We’ve been asking you folks a lot about what you’d add to SQL Server, and we’ve gotten some great answers. There are even some that have been answered in the last couple versions of SQL Server. For instance, 2016 brought us STRING_SPLIT! SQL Server 2017 has a whole bunch of neat stuff: CONCAT_WS…
Read More

Bad Idea Jeans: Multiple Index Hints

I hate that you can do this Here’s the thing: I’m mostly writing this because I didn’t know you could do it. But it’s cool, because it’ll reinforce some other concepts, and I’ll show you why you shouldn’t do it. I’m talking, of course, about index hints. To be more specific, hinting multiple indexes on…
Read More

Don’t Use Scalar Functions in Views.

T-SQL
3 Comments
The short story: if your view has a scalar user-defined function it it, any query that calls the view will go single-threaded, even if the query doesn’t reference the scalar function. Now for the long story. Quite often people will inherit and rely on views written back in the dark ages, before people were aware…
Read More

Replacing ISNULL In A WHERE Clause

Execution Plans, T-SQL
4 Comments
I like blogging Really, I do. But sometimes I like a change of pace. Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky…
Read More

The Surprising Behavior of Trailing Spaces

T-SQL
26 Comments
In every training class we do, I learn stuff from students. Here’s last week’s surprise. Create a temp table, and put in a value with trailing spaces. Then query for it using the = and <> operators, using different numbers of trailing spaces: Transact-SQL CREATE TABLE #Wines (Winery VARCHAR(50)); INSERT INTO #Wines VALUES ('Cliff Lede…
Read More

Fun With Logging Dynamic SQL

T-SQL
9 Comments
While working on a demo I thought this might make a fun aside to share on the blog, because working with dynamic SQL can be challenging. Especially if you don’t have a monitoring tool or application profiling to figure out who ran what and when, most of the time you’ll have no idea what went…
Read More

A Little Fun With Math

SQL Server, T-SQL
5 Comments
I’ve never been much at math But I’ve always liked reading about it. It’s super interesting, and it always blows my mind. Like, guaranteed. Once in a while I’ll even try my hand at solving problems I read about in SQL. Not because SQL is a particularly good language for it; but just because sometimes…
Read More
Tara Kizer

What TRY/CATCH Doesn’t Handle

SQL Server, T-SQL
25 Comments
We were once asked in class what TRY/CATCH doesn’t handle besides object existence errors. It’s well documented in Books Online (BOL). If you’re like me, then tl;dr. Are we even calling it Books Online these days? I still say “bookmark lookup” instead of “key lookup”. I suppose I’ll be saying Books Online for quite some…
Read More

Give Your T-SQL a Semicolonoscopy

SQL Server, T-SQL
38 Comments
In theory, all of your T-SQL statements are supposed to end with a semicolon, like this: SELECT * FROM dbo.Posts; SELECT * FROM dbo.Comments; GO 123 SELECT * FROM dbo.Posts;SELECT * FROM dbo.Comments;GO Why? Well, SQL Server 2005’s Books Online says: Many code examples use a semicolon (;) as a Transact-SQL statement terminator. Although the semicolon…
Read More

Window Functions and Cruel Defaults

SQL Server, T-SQL
5 Comments
My First Post Here… Well, my first technical post, was about how the default index creation method is OFFLINE. If you want that sweet, sweet Enterpri$e Edition ONLINE goodness, you need to specify it. It’s been a while since that one; almost six months to the day. So here’s another one! But Window Functions Are…
Read More