T-SQL

Bad Idea Jeans, T-SQL

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…

Scalar Functions In Views: Where’s The Overhead?

May 24, 2017

development, performance tuning, sql server

Short winded Quite often people will inherit and rely on views written back in the dark ages, before people were aware of the deleterious effects that scalar valued functions can have on performance. Of course, for some people, it’s still the dark ages. Sorry, you people. Questions and answers So what are the side effects…

Announcing Erik’s First Video Class: T-SQL Level Up, Level 2

April 25, 2017

Company News, T-SQL

You’re confident writing queries to get the data you want, and you’ve been doing it for a couple of years. You’ve taken our free T-SQL Level Up class, mastered tally tables and windowing functions, but what else is there? In this class with T-SQL maestro Erik Darling, you’ll learn tricks to take your queries to…

Replacing ISNULL In A WHERE Clause

April 14, 2017

Execution Plans, T-SQL

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…

The Surprising Behavior of Trailing Spaces

April 13, 2017

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…

Fun With Logging Dynamic SQL

April 11, 2017

dba, development, sql server

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…

sp_BlitzCache Tip: Cutting Through Heavy Dynamic SQL Use

March 17, 2017

First Responder Kit, sp_BlitzCache, T-SQL

dba, development, performance tuning, sp_BlitzCache, sql server

It keeps me up at night Out of the box, sp_BlitzCache will give you enough stuff to work on in your server’s plan cache to last a month of leap days. This is great for most people, but if you use a ton of dynamic SQL in stored procedures, it can make things difficult. What’s…

No seriously, don’t use SQL_VARIANT for that

March 15, 2017

Development, T-SQL

I’d been meaning to write this for a while I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn’t get into at the time is that it can give you incorrect results. When I see people using SQL_VARIANT, it’s often in dynamic SQL, when they don’t…

Inline Table Valued Functions: Parameter Snorting

February 8, 2017

You’ve probably heard about parameter sniffing But there’s an even more insidious menace out there: Parameter Snorting. It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at…

High Compiles and Multi-Statement Table Valued Functions

January 26, 2017

SQL Server, T-SQL, TempDB

Way back in 2016 I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions. Leaving aside…

Your Favorite Bugs & Enhancement Requests: #TSQL2sday 86 Roundup

January 17, 2017

I dunno about you, but I got a big stocking full of coal. Next year, I’m gonna be better, and I plan on asking Santa for a whole bunch of Connect requests. For T-SQL Tuesday, I asked you to name your favorite SQL Server bugs & enhancement requests, and here’s what you want in your…

#TSQL2sday: System-Maintained LastUpdatedDate, LastUpdatedBy Columns

January 10, 2017

#tsql2sday, t-sql tuesday

For this month’s T-SQL Tuesday, we’re talking about interesting Connect requests to fix or change SQL Server behavior. T-SQL Tuesday In our Senior DBA Class, one of the exercises involves figuring out changes to a table after an Always On Availability Group failover. In every class, several students always say the same thing: “I really…

Announcing T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

January 3, 2017

#tsql2sday, t-sql tuesday

I know this is going to come as a stunning shock to you, dear reader, so you may want to be sitting down. SQL Server isn’t perfect. It’s okay. Deep, calming breaths. We’re going to get through this together, and here’s how: Go to Connect.Microsoft.com, the place where we file bug reports and enhancement requests…

A Little Fun With Math

November 18, 2016

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…

What TRY/CATCH Doesn’t Handle

May 17, 2016

Tara Kizer

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…

Another reason why scalar functions in computed columns is a bad idea

January 26, 2016

dba, sql server

As if there weren’t enough reasons In my last blog post I talked about different things that cause plans, or zones of plans, to execute serially. One of the items I covered was computed columns that reference scalar functions. We know that they’ll make queries go serial, but what about other SQL stuff? Oh no my…

Give Your T-SQL a Semicolonoscopy

December 17, 2015

In theory, all of your T-SQL statements are supposed to end with a semicolon, like this: Transact-SQL 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…

Window Functions and Cruel Defaults

September 29, 2015

dba, development

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…

Indexing for Windowing Functions

June 17, 2015