Function Vs. Performance

SQL Server
Recently I was looking through DBA.StackExchange when I saw a pretty simple question that I decided to answer. I went off, set up a test database and some test tables and quickly wrote a query to answer the question. I got it all formatted pretty and was on my way to post it when I saw that another answer was already there. Yeah. Identical to mine. Almost line for line. Well, nuts. I know. I'll write a blog post. The Setup The original poster had two tables that, frankly, are badly designed. However, they share enough data that they are "related" if not relational. Here's the code: CREATE DATABASE Testing; GO USE Testing; GO CREATE TABLE Table_A ( ID INT IDENTITY(1, 1), Score INT ); CREATE TABLE Table_B ( FromPoint…
Read More

Left or Right?

SQL Server, T-SQL
No, this is not about politics. It's about your WHERE clause... and your JOIN criteria... and your HAVING clause. It's about a canard that still makes the rounds occasionally. Please, help me put this statement to sleep for once and all: A function on the left side of the equals sign can lead to performance problems Well, you know, it's sort of true. But then, a function on the right side of the equals sign can also lead to performance problems. In short, it's not the placement of the function that causes issues, it's the function that causes issues. Let's take a look at a really simple example: SELECT a.AddressID, a.AddressLine1, AddressLine2 FROM Person.Address AS a WHERE a.AddressLine1 = 'Downshire Way'; This simple query results in an equally simple execution plan: Now,…
Read More

Is Dynamic T-SQL a Good Design Pattern?

T-SQL
In a recent discussion it was suggested to me that not only is dynamic T-SQL useful for things like catch-all queries or some really hard to solve problems involving variable table lists, but is, in fact, a perfectly acceptable design pattern for all queries against a database. Note, in this case, we’re not talking about an ORM tool which takes control of the system through parameterized queries, but rather an intentional choice to build nothing but dynamic T-SQL directly on the system. To me, this was immediately problematic. I absolutely agree, you’re going to have dynamic T-SQL for some of those odd-ball catch-all search queries. But to simply expand that out to include all your queries is nuts. There really is a reason that stored procedures exist, and it’s not…
Read More

SQL University: Index Usage

SQL Server, T-SQL
Greetings. Welcome once more to the Miskatonic University branch of SQL University. Does anyone know where to buy some camping equipment, cheap? I’ve been tagged to go an expedition to Antarctica and I need some cold weather gear a bit more substantial than my LL Bean boots. Evidently the last expedition found some caves in some mountains down there. Sounds like the perfect place to get away from all the crazy stuff that goes on here at Miskatonic. I mean, what could happen? Anyway, our last several talks have all been about indexes and indexing. One of the things that we haven’t talked about is how to tell if, how or when your indexes are being used. Starting with SQL Server 2005, and continuing to 2008 and R2, there has…
Read More

Review: Idera SQL Doctor

SQL Server, T-SQL, Tools
Recently, a co-worker practically slammed me up against the wall, exclaiming “You have to check out this new tool, right now!” The piece of software he was so excited about was Idera’s SQL Doctor. Based on this assaultrecommendation, I decided to take a little time & look the software over. SQL Doctor, as the name implies, is a diagnostic tool. It runs a set of best practice rules against your server, your databases and your code. As the rules are executed, your system’s compliance with these best practices is evaluated and an interactive report is generated. With the report you can drill down on various aspects of your system to see where you may have gone wrong. All that sounds very clinical, just laid out like that. But the fact…
Read More

SQL University: Introduction to Indexes, Part the Third

SQL Server, T-SQL
Nice to see most of you have managed to fight your way through the shoggoths outside to attend another lecture at the Miskatonic branch of SQL University. This will be the third and final part of the introduction to indexes lecture. Please, if you're going mad, step out into the hall. Our previous two lectures introduced the concept of indexes and then talked about two types of indexes, clustered and nonclustered. This lecture will cover the concept of statistics as they relate to indexes. If you followed the previous lecture then you know that indexes are stored in a Balanced Tree or B-Tree structure. You know that this storage mechanism is intended to provide fast retrieval of data. But, how can the query engine inside SQL Server know which index…
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