The CASE Statement and Performance

SQL Server, T-SQL
In case you don't know, this query: UPDATE dbo.Test1 SET C2 = 2 WHERE C1 LIKE '%33%'; Will run quite a bit slower than this query: UPDATE dbo.Test1 SET C2 = 1 WHERE C1 LIKE '333%'; Or this one: UPDATE dbo.Test1 SET C2 = 1 WHERE C1 = '333'; That's because the second two queries have arguments in the filter criteria that allow SQL Server to use the statistics in an index to look for specific matching values and then use the balanced tree, B-Tree, of the index to retrieve specific rows. The argument in the first query requires a full scan against the index because there is no way to know what values might match or any path through the index to simply retrieve them. But, what if we…
Read More

Are Foreign Keys Better Than Indexes?

SQL Server, T-SQL
When I first saw this question I thought to myself, "Self. Don't you think that's comparing apples to hammers? Yes, Self, I'm pretty sure it is. Good, I thought so too, self. Yeah, me too." After rebooting because of the runaway iterations on that thought, I had another, "Well... hold on there self. Both types of objects, while pretty different, are taken into account by the query optimizer." I then had to admit to myself that I had a point. So the question remains, are foreign keys better than indexes? As my first self said, these are different objects and they fulfill different purposes within SQL Server. My second self wants to point out that when you're dealing with functional objects within SQL Server, it's a bad habit to start…
Read More

SQL University–Recommendations for a Clustered Index

SQL Server, T-SQL
Welcome, SQL University Students to another extension class here at Miskatonic University, home to the Fighting Cephalopods (GO PODS!). Never mind the stains on the floor, or the walls…or those really nasty ones on the ceiling. There was a… oh what did the dean call it… an incident last week when one of the students had a little accident after reading Die Vermiss Mysteriis one too many times. But we’re not here to talk about arcane tomes and unspeakable horrors today. No, today we’re here to talk about clustered indexes. SQL Server storage is really predicated around the idea of clustered indexes. Don’t believe me? Let’s list a few places that require a clustered index: Partitioning. A table in SQL Azure In order to create XML indexes What about the…
Read More

SQL University: Introduction to Indexes, Part the Second

SQL Server, T-SQL
Welcome once more to the Miskatonic branch of SQL University. Please try to concentrate. I realize the whipoorwills singing outside the window in a coordinated fashion that sounds almost like laboured breathing can be distracting, but we're talking about indexes here. We left last class with a general idea what an index is, now it's time for some specifics. There are several different kinds of indexes, as we talked about last class. But the two you're probably going to work with the most are clustered, non-clustered. Each of these indexes is stored in a structure called a B-Tree, a balanced tree, not a binary tree. That's a very important distinction. A B-Tree is a double-linked list that is defined by the keys of the indexes on the top and intermediate pages, and…
Read More

SQL University: Introduction to Indexes, Part the First

PowerShell, Spatial Data, SQL Server, T-SQL
Right, all eldritch tomes are to be closed and Elder Signs are to be put away during this course. Welcome to the History department here at the Miskatonic branch of SQL University. Why the History department? Well, first, because I like history and have frequently thought I would enjoy teaching it. Second, because I needed a hook upon which to hang part of the story I want to tell. What story is that you ask? Why, the story of the Dewey Decimal System. We are interested in studying history and historians must classify our subjects carefully. For advanced students we'll be covering the Library of Congress Classification System and the... Right, I give, this is the introductory class on indexes. If you thought we were covering something exciting and sexy like PowerShell,…
Read More

Spatial Indexes and a Lack of Data

Spatial Data, T-SQL
I was feeling quite confident about my new-found abilities with spatial indexes so I did a presentation for my team, to share what I had learned. I had also been sharing with one co-worker as I developed the knowledge of spatial indexes. While I was preparing my presentation, he was preparing his. I had focused on finding a set of data that showed it's proximity to a test location and then showing how retrieving that set of data was faster because of the spatial index. He took a different approach. He took the idea of saying, here's a list of different test locations, let's see which one of our internal locations meet the proximity test. At the same time, he tried three different spatial indexes, one with high granularity, one with medium and a final…
Read More

Spatial Data Hurts My Brain

Spatial Data, T-SQL
I'm still barely scratching the surface working with spatial data in SQL Server 2008. We've ported some of the data into a table where we built a geography spatial data column and we're begginning to work with point data. The requirements from the developers are, so far, very simple. They'll feed me a point and I find all the locations "close" to it. We had to go round & round on what defines "close" but finally settled on, I think, 15km. The query to answer a question like this is ridiculously simple (a few object names have been changed): SELECT ebe.[Location].STDistance(@Location) AS Distance, ebe.[InterestId], ebe.[Location].Lat AS Latitude, ebe.[Location].Long AS Longitude, ebe.[OrgId] FROM dbo.[ebe] AS ebe WHERE ebe[OrgId] = @OrgId AND ebe.[Location].STDistance(@Location) < @CloseDistance I'm not even hard-coding the "close" value…
Read More

Tim Ford’s Top 5 Indexing Best Practices

SQL Server, T-SQL
All I can really add to this is, yeah, me too. If you want some absolutely great advice on indexes, read this post. It's a must. And might I add, I've been the bad guy in Tim's example. Once, many, many years ago, I was reading from the SQL Server 7.0 documentation. It suggested that compound indexes were no longer needed since the optimizer could build them on the fly using index intersection. I had a performance problem and a consultant was telling me to use a compound index. I swore up and down it wouldn't work because Microsoft said so. He kept pushing and I kept pushing back. Finally, after a rather heated discussion in which I was convinced I had the upper hand, I got off the phone…
Read More

2008 Index Fragmentation

Uncategorized
I forgot all about this, but a script I wrote on using all the new functionality of dynamic management views & functions to do index defragmentation and rebuilds got published over at SQL Server Central. It could stand a bit of tweaking, but gets the job done on several of the systems I've tested it on so far.
Read More