T-SQL

Give Your T-SQL a Semicolonoscopy

December 17, 2015

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

Window Functions and Cruel Defaults

September 29, 2015

Erik Darling

SQL Server, T-SQL

dba, development

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

June 17, 2015

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

March 3, 2015

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?

October 14, 2014

Jes Schultz Borland

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

March 31, 2014

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

December 26, 2013

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

July 14, 2011

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

May 10, 2011

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

June 9, 2010

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…