dba

  1. Home
  2. dba
  3. (Page 7)

Window Functions and Cruel Defaults

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

Performance Benefits of Unique Indexes

SQL Server
27 Comments
SQL server loves unique indexes Why? Because it’s lazy. Just like you. If you had to spend all day flipping pages around, you’d probably be even lazier. Thank Codd someone figured out how to make a computer do it. There’s some code below, along with some screen shots, but… TL;DR SQL is generally pretty happy…
Read More

Logical Query Processing Follow-up

SQL Server
0
i like questions! Because I don’t always like talking to myself, and robots are sometimes murderous and insane. So when this one came up in the comments of my previous post, I thought it would make a good follow-up blog. It’s a really good question, and definitely one I found myself asking quite a bit…
Read More

Finding Tables with Nonclustered Primary Keys and no Clustered Index

Indexing, SQL Server
30 Comments
i’ve seen this happen Especially if you’ve just inherited a database, or started using a vendor application. This can also be the result of inexperienced developers having free reign over index design. Unless you’re running regular health checks on your indexes with something like our sp_BlitzIndex® tool, you might not catch immediately that you have…
Read More

Logical Query Processing

You can’t do that on management studio Recently, while working with a client, I did something in a query that they were mystified by. I didn’t think much of it, but I thought it might be useful to you, dear readers, as well. Along with an explanation. Here’s a sample query that takes advantage of…
Read More

The sp_rename follies

SQL Server
28 Comments
Before we get started… I know. I know. BOL. It’s documented. They even show you how to rename a table. Thanks, sp_rename! But sometimes… You just forget. And as with most simple mistakes, fixing them is… Weird. Here’s what happened to me recently, when I was working on a table swapping demo. Transact-SQL CREATE TABLE…
Read More

Indexing for GROUP BY

Indexing, SQL Server
9 Comments
It’s not glamorous And on your list of things that aren’t going fast enough, it’s probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you’re performing aggregations on. We’ll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! Transact-SQL…
Read More

And Party and Alt Shift

SQL Server
50 Comments
This is a cool SSMS trick I picked up a while back Though not nearly as far back as I wish I had. It’s so cool I made a .gif of it in action. When you’re done putting your socks back on, I’ll tell you how it works. This .gif was brought to you by…
Read More

SELECT INTO and non-nullable columns

SQL Server
21 Comments
SELECT…INTO  is one of my favorite SQL Server features. It’s great for creating table skeletons with false WHERE clauses (1=2), moving a real table to a staged/temp location for testing, etc. In SQL Server 2014 It acquired the ability to go parallel, which is pretty neat, but that’s not what we’re talking about here. It…
Read More
Menu