Give Your T-SQL a Semicolonoscopy

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…
Read More

Indexing for Windowing Functions

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,…
Read More

How to Cache Stored Procedure Results

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,…
Read More
Query Execution Plan

Sargability: Why %string% Is Slow

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…
Read More