T-SQL

Half Of You Don’t Understand Variables and Transactions.

Last Updated August 20, 2020

Do table variables and variables respect transactions? If I set the value of a variable during a transaction, and I roll it back, what happens? DECLARE @MySalary INT = 100000; BEGIN TRAN SET @MySalary = @MySalary * 2; ROLLBACK; SELECT @MySalary; 1234567 DECLARE @MySalary INT = 100000; BEGIN TRANSET @MySalary = @MySalary * 2;ROLLBACK; SELECT @MySalary; I…

Your Views Aren’t The Problem. Your Code Is.

Last Updated May 6, 2020

Brent Ozar

T-SQL

“I hate views,” the DBA said. “They kill performance. And nested views are even worse.” Wrong. I’ll prove it. I’m going to use the 50GB StackOverflow2013 database, and I’ll start by creating a couple of indexes on the Users table to help our queries, then create a view on the Users table: Transact-SQL USE StackOverflow2013;…

Free Webcast on Dynamic SQL Pro Tips

Last Updated May 1, 2020

Brent Ozar

GroupBy Conference, T-SQL

Dynamic SQL is one of the most powerful tools in the database developer’s arsenal. When you need a complex search stored procedure that takes all kinds of parameters (price, category, location, color), dynamic SQL can run extremely quickly by leveraging better indexes. However, when done wrong, it’s extremely painful to troubleshoot. I’ve been working with…

Can You Use NVARCHAR As a Universal Parameter? Almost.

Last Updated May 27, 2020

Brent Ozar

T-SQL

A perfect storm of unusual problems caused a client to ask, “What if we just used NVARCHAR(4000) as the default datatype parameter for any query, regardless of what datatype the table has in it – like numbers or dates?” It actually works pretty well in most situations, believe it or not. The Users table of the…

If You Have Foreign Keys, Don’t Update Fields That Aren’t Changing.

Last Updated December 29, 2021

Brent Ozar

Foreign Keys, T-SQL

If you update a row without actually changing its contents, does it still hurt? Paul White wrote in detail about the impact of non-updating updates, proving that SQL Server works hard to avoid doing extra work where it can. That’s a great post, and you should read it. But foreign keys add another level of…

How to Get Better Estimates for Modification Queries

Last Updated April 27, 2020

Brent Ozar

Execution Plans, T-SQL

When you’re doing DUI operations against tables with millions of rows, you have to be really careful about SQL Server’s estimates. Ideally, you want your delete/update/insert (what – what’d you think I meant?) queries to be as simple as possible – even a simple join can cause SQL Server to do wildly, wildly incorrect estimates,…

Why Ordering Isn’t Guaranteed Without an ORDER BY

Last Updated April 11, 2020

Brent Ozar

Indexing, T-SQL

If your query doesn’t have an ORDER BY clause, you can’t reliably predict the order of your results over time. Sure, it’s going to look predictable at first, but down the road, as things change – the indexes, the table, the server’s configuration, the size of your data – you can end up with some ugly…

Making TRY_CAST and TRY_CONVERT Queries Faster with Indexed Computed Columns

Last Updated March 1, 2020

Brent Ozar

Computed columns, Execution Plans, T-SQL

I know this is gonna sound crazy, but let’s say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let’s say your application frequently ran a query looking for dates in that column, like this: Transact-SQL SELECT *…

How to Pass a List of Values Into a Stored Procedure

Last Updated February 7, 2020

Brent Ozar

T-SQL

Say we have a stored procedure that queries the Stack Overflow Users table to find people in a given location. Here’s what the table looks like: And here’s what my starting stored procedure looks like: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsersByLocation @SearchLocation NVARCHAR(40) AS SELECT * FROM dbo.Users WHERE Location = @SearchLocation ORDER BY DisplayName;…

How to Remove Times from Dates in SQL Server

Last Updated January 27, 2020

Brent Ozar

T-SQL

Say that you’ve got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table: And let’s say you wanna find the users who last accessed the site on September 9, 2018 – regardless of the time. Here are a few ways to do it: Transact-SQL SELECT *…

Cool Query Hints

Last Updated January 22, 2020

Brent Ozar

T-SQL

The SQL Server documentation has a pretty cool list of query hints: Yeah, I surf in dark mode. I’m a dark kinda guy. But wait – what’s that colored box? ENHANCE! I could make jokes here, but … every single thing in that caution is true. SQL Server really does typically select the best execution…

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

Last Updated January 6, 2020

Brent Ozar

T-SQL

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?

Last Updated August 1, 2020

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.

Last Updated May 27, 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

Last Updated 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

Last Updated June 25, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, 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?

Last Updated 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

Last Updated 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

Last Updated January 18, 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?

Last Updated December 5, 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…

T-SQL

Save your cart?