T-SQL

[Video] You shouldn’t dynamically create/alter/drop triggers.

January 7, 2020

Let’s get one thing out of the way first: this isn’t about the relative good or evil of triggers. I kinda like triggers, in fact – they’re good for enforcing business logic in a hurry when you can’t redo code across an entire application. Sure, in a perfect world, we’d always have the code do…

What’s Better, CTEs or Temp Tables?

June 6, 2019

Brent Ozar

T-SQL

I get this question a lot, so let’s set up an example with the Stack Overflow database. My query’s goal is to: Find the top 5 Locations List the users who live in those top 5 Locations, alphabetized by their DisplayName There are a LOT of ways I could write this query, but for the…

Scalar function execution plans, SQL Server 2017

User-defined scalar functions suck – even when they don’t access data.

May 28, 2019

Brent Ozar

T-SQL

The performance of scalar functions sucks hard. Let’s see it in action using the Stack Overflow database – any size will work. I’ll set things up first in case you want to follow along: Transact-SQL USE StackOverflow2013; GO ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; GO EXEC DropIndexes; GO EXEC sp_configure 'cost threshold', 5; EXEC…

The Silent Bug I Find in Most Triggers

May 6, 2019

Brent Ozar

T-SQL

Get Notebook – FAQ I don’t have a problem with triggers. They get the job done when you need to implement business logic in a hurry, and you’re not allowed to change the application. As long as you keep the number of statements to a minimum (say, 2-3), and don’t try to do something really…

Finding Froid’s Limits: Testing Inlined User-Defined Functions

April 11, 2019

Brent Ozar

Execution Plans, SQL Server 2019, T-SQL

This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…

Should we use stored procedures or queries built in the app?

March 27, 2019

Brent Ozar

T-SQL

A client asked a great architecture question that deserved its own blog post: Should we use more “stored procedures” for select, insert, update, delete or should that just be sent as queries from the application? If you ask a junior database administrator where to put something, she’ll probably say “in the database” because that’s the…

Watch Brent Tune Queries: Fixing Nested Functions

January 24, 2019

Brent Ozar

T-SQL

When I do performance tuning for clients, I really pride myself on making as few changes as possible in order to make a tremendous difference. I consider it a failure when I have to tell someone to rewrite a bunch of queries from scratch. However, there are some cases where I just can’t work around…

Tuning Dynamic SQL by Hand with Short Circuits

January 23, 2019

Brent Ozar

T-SQL

When we think about building dynamic SQL, we usually think about a stored procedure like this that takes input parameters, builds a string, and then executes that string. Here’s a simple example: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsers @SearchDisplayName NVARCHAR(40) = NULL, @SearchLocation NVARCHAR(100) = NULL, @SearchReputation INT = NULL AS BEGIN DECLARE @StringToExecute NVARCHAR(4000);…

Do Functions Stop You From Using Indexes?

December 20, 2018

Brent Ozar

Indexing, T-SQL

Say I’ve got a function in my WHERE clause: Transact-SQL SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; 123 SELECT DisplayName FROM dbo.Users WHERE LTRIM(RTRIM(DisplayName)) = 'Brent Ozar'; If I have an index on DisplayName, will SQL Server use it? Sure: Function in the WHERE clause Even though SQL Server can’t seek to “Brent Ozar,” it will…

What Is SQL Injection?

December 1, 2018

Brent Ozar

T-SQL

Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both. For performance reasons, we decide to build dynamic SQL: Transact-SQL CREATE OR ALTER PROC dbo.UserSearch @DisplayName NVARCHAR(40) = NULL, @Location NVARCHAR(100) = NULL AS…

What’s New in SQL Server 2019: Faster Functions

November 7, 2018

Brent Ozar

SQL Server 2019, T-SQL

A while back, we talked you through a public whitepaper about how Microsoft was working on making user-defined functions go faster. Now that the preview of SQL Server 2019 is out, you can start getting your hands on Froid, the performance-boosting feature. Here’s the documentation on it – let’s see how it works. Using the…

SUM, AVG, and arithmetic overflow

October 30, 2018

Erik Darling

T-SQL

You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error. Transact-SQL SELECT u.Id, u.DisplayName, SUM(p.Score) AS SumPostScore, AVG(c.Score) AS SumCommentScore FROM dbo.Users AS u JOIN dbo.Posts…

What’s New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

September 24, 2018

Brent Ozar

SQL Server 2019, T-SQL

For over a decade, SQL Server’s handling of table variables has been legendarily bad. I’ve long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation: Transact-SQL declare @VoteStats table (PostId int, up int, down int) insert @VoteStats select PostId, up = sum(case when VoteTypeId = 2 then 1 else 0 end), down…

How to Check for Non-Existence of Rows

August 9, 2018

Brent Ozar

Execution Plans, T-SQL

You’re writing a query, and you wanna check to see if rows exist in a table. I’m using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are: In dbo.Users, the Id field uniquely identifies a user. In dbo.Comments, there’s a UserId field…

Stupid T-SQL Tricks

June 6, 2018

Brent Ozar

Bad Idea Jeans, T-SQL

Presented without comment: Transact-SQL CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT); GO SELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE]; GO 1234 CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT);GOSELECT [SELECT] FROM [FROM] WHERE [WHERE] LIKE [LIKE];GO Next up, can you break up a query with spaces? Yep: Transact-SQL SELECT * FROM…

How to Reduce the CPU Overhead of Dynamic SQL

May 23, 2018

Tara Kizer

Development, T-SQL

Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string. For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues. For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you…

Quick Tips For Debugging Large Stored Procedures

May 16, 2018

Erik Darling

T-SQL

Hell Here! Get Your Fresh Hell, Here! Let’s face it — as far as developer tools go, SSMS is pretty bad. Intellisense? The jokes write themselves. Don’t get me wrong, SSMS is a good management tool, but it’s not a good development tool (and what kind of maniac sticks debug — F6 — next to execute —…

How to Get a Random Row from a Large Table

March 5, 2018

Brent Ozar

T-SQL

Method 1, Bad: ORDER BY NEWID() Easy to write, but it performs like hot, hot garbage because it scans the entire clustered index, calculating NEWID() on every row: The plan with the scan That took 6 seconds on my machine, going parallel across multiple threads, using tens of seconds of CPU for all that computing…

Fifteen Things I Hate About ISNUMERIC

February 26, 2018

Erik Darling

T-SQL

Yello! Transact-SQL SELECT ISNUMERIC('$') AS [What] UNION ALL SELECT ISNUMERIC('£') AS [What] UNION ALL SELECT ISNUMERIC(',') AS [What] UNION ALL SELECT ISNUMERIC('.') AS [What] UNION ALL SELECT ISNUMERIC('0e+99') AS [What] UNION ALL SELECT ISNUMERIC('2e2') AS [What] UNION ALL SELECT ISNUMERIC('12D4') AS [What] UNION ALL SELECT ISNUMERIC(',1,1,1,1,1,1,1') AS [What] UNION ALL SELECT ISNUMERIC('-') AS [What] UNION…

Using WITH (NOEXPAND) to Get Parallelism with Scalar UDFs in Indexed Views

February 22, 2018

Brent Ozar

T-SQL

scalar functions

Scalar functions are the butt of everybody’s jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they’re in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, I could go on and on. Recently, we ran across yet another scenario where…