T-SQL

Using the Stack Overflow public export, take these two queries looking for a particular user by Id (the clustering key): Transact-SQL SELECT * FROM dbo.Users WHERE Id = 26837; SELECT * FROM dbo.Users WHERE Id LIKE 26837; 12 SELECT * FROM dbo.Users WHERE Id = 26837;SELECT * FROM dbo.Users WHERE Id LIKE 26837; The first…

Two Code Patterns That Don’t Virtualize Well

January 25, 2018

Virtualization used to be a really Big Deal™ for database admins: we had to do a lot of careful planning to get a virtualization project done right. These days, virtualization is more and more of a no-brainer: most apps make the transition just fine. Every now and then, though, an exception pops up – usually…

#TSQL2sday: Brad McGehee Made a Difference in My Career.

November 14, 2017

For this month’s T-SQL Tuesday, Ewald asked who’s made a difference in our careers. When I first got started out in SQL Server, all I had was books and Books Online. Back then, neither of them were particularly well-indexed, nor were they up to date. Then I found a web site that turned things around.…

SQL Server Workarounds

October 19, 2017

Development, SQL Server, T-SQL

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…

Bad Idea Jeans: Multiple Index Hints

May 26, 2017

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

Parameter Sniffing, SQL Server, T-SQL

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

SQL Server, T-SQL

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