Product has been added to your cart.

T-SQL

  1. Home
  3. Development
  5. T-SQL
    6. (Page 2)

How to Pass a List of Values Into a Stored Procedure

Brent Ozar
T-SQL
59 Comments
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;…
Read More

How to Remove Times from Dates in SQL Server

Brent Ozar
T-SQL
34 Comments
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 *…
Read More

Cool Query Hints

Brent Ozar
T-SQL
12 Comments
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…
Read More

What’s Better, CTEs or Temp Tables?

Brent Ozar
T-SQL
36 Comments
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…
Read More

The Silent Bug I Find in Most Triggers

Brent Ozar
T-SQL
34 Comments
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…
Read More
Froid

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

Brent Ozar
Execution Plans, Parameter Sniffing, SQL Server 2019, T-SQL
16 Comments
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…
Read More

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

Brent Ozar
T-SQL
66 Comments
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…
Read More

Tuning Dynamic SQL by Hand with Short Circuits

Brent Ozar
T-SQL
7 Comments
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);…
Read More
No longer uses the index

Do Functions Stop You From Using Indexes?

Brent Ozar
Indexing, T-SQL
5 Comments
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…
Read More

What Is SQL Injection?

Brent Ozar
T-SQL
4 Comments
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…
Read More

SUM, AVG, and arithmetic overflow

Erik Darling
T-SQL
14 Comments
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…
Read More

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

Brent Ozar
Parameter Sniffing, SQL Server 2019, T-SQL
16 Comments
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…
Read More

Stupid T-SQL Tricks

Brent Ozar
Bad Idea Jeans, T-SQL
31 Comments
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…
Read More

How to Reduce the CPU Overhead of Dynamic SQL

Tara Kizer
Development, T-SQL
14 Comments
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…
Read More

Quick Tips For Debugging Large Stored Procedures

Erik Darling
T-SQL
55 Comments
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 —…
Read More
Menu