T-SQL

Don’t Use Scalar User-Defined Functions in Computed Columns.

Last Updated May 6, 2020

Scalar functions in computed columns cause all queries that hit that table to execute serially. But it gets worse! Scalar functions in computed columns cause index maintenance to go single-threaded. If you’re running Expensive Edition, index rebuilds can be both online and parallel. That’s pretty cool, because it keeps all your gadgets and gizmos mostly…

Give Your T-SQL a Semicolonoscopy

Last Updated February 13, 2017

Brent Ozar

SQL Server, T-SQL

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

Window Functions and Cruel Defaults

Last Updated March 12, 2018

Erik Darling

SQL Server, T-SQL

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

Last Updated March 12, 2018

Erik Darling

Indexing, SQL Server, T-SQL

Hooray Windowing Functions They do stuff that used to be hard to do, or took weird self-joins or correlated sub-queries with triangular joins to accomplish. That’s when there’s a standalone inequality predicate, usually for getting a running total. With Windowing Functions, a lot of the code complexity and inefficiency is taken out of the picture,…

The Top 3 Mistakes T-SQL Developers Make

Last Updated February 13, 2017

Doug Lane

Development, SQL Server, T-SQL

Over the years, I’ve done all kinds of awful things with T-SQL and made countless mistakes. Some were harmless; others were borderline catastrophic (exciting times!). I was curious what kind of horrible mistakes other people make, so I threw the question out to Twitter. Every answer I got was unique, which was both telling (so…

Are SQL Server Functions Dragging Your Query Down?

Last Updated February 14, 2017

Jes Schultz

Execution Plans, SQL Server, T-SQL

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you…

Refactoring T-SQL with Windowing Functions

Last Updated February 14, 2017

Doug Lane

SQL Server, T-SQL, Videos

You’ve been querying comparative numbers like Year To Date and Same Period Last Year by using tedious CTEs and subqueries. Beginning with SQL Server 2012, getting these numbers is easier than ever! Join Doug for a 30-minute T-SQL tune-up using window functions that will cut down dramatically on the amount of code you need to…

How to Cache Stored Procedure Results

Last Updated February 13, 2017

Brent Ozar

SQL Server, T-SQL

Say you run an online store, and on each item’s page, you need to show related items that people purchased. Let’s take an Amazon page for my favorite rechargeable AA batteries: Frequently Bought Together In a perfect world, we would cache this data in the web/app tier – but back here in the real world,…

Windowing Function Examples for SQL Server

Last Updated November 26, 2020

Jeremiah Peschka

SQL Server, T-SQL

Aggregations and grouping can be a pain in the rear for the novice SQL developer. Way back in 2003, the ANSI/ISO standards people figured this out and added windows and ranking functions to the standard. In 2005, Microsoft added a few of these functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as the OVER() clause…

Well, it was good, but I dunno about amazing.

Seeks, Scans, and Statistics in the Grocery Store

Last Updated July 14, 2019

Brent Ozar

Indexing, T-SQL

Building an execution plan is a lot like going shopping. Before I leave the house, I need to think about a few things: How many items do I need? I take a glance at my grocery list to see roughly how many things I’m shopping for. What stores am I going to visit? I can’t…

Sargability: Why %string% Is Slow

Last Updated December 29, 2017

Brent Ozar

T-SQL

People love to search. Google has us addicted to fast, easy search functions. Users expect every application to have a built-in blazing-fast search functionality. To pull that off, developers build search queries that let users enter a string, and we ask SQL Server to find matches. For example, say our users need to find some…

T-SQL

Save your cart?