Category: T-SQL

Jana Sattainathan argues against hard-coding values in queries:

I have heard arguments for doing this type source code

• This is a one-time thing. We do not have the need to do it anywhere else

• We are on a deadline

• We do not have the ability to test if this was not done this way

• My program is going away in a week

• We do not have the time to correct this

• I am just following the existing pattern

• Unofficially (not) said – “This is my job security”

I’m with Jana in principle, but there are performance costs at the margin, making this less of a hard-and-fast rule than I’d like.

Kenneth Fisher one method of creating lists of random numbers:

I was working on a blog post this weekend that required a list of random numbers. Now, this isn’t exactly an uncommon task, but it’s not as easy as it would seem. The random number function RAND() has a few inherent flaws. And from what I can remember the random functions from most languages have the same issue.

First a few quotes from BOL about RAND()

Returns a pseudo-random float value from 0 through 1, exclusive.

Note: pseudo-random

If a seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

If you don’t specify the seed it gets selected at random. But that’s only once per query run, not once per row of the output.

Read on for Kenneth’s solution.

Vladimir Oselsky hits on something I dislike:

My first gut reaction was that this code is broken and would not run. To my amazement code ran just fine. Now came the hard part, which was to figure out what the code was doing because I have never seen this syntax before. Since I did not understand what I was looking at I could not BING “weird join syntax” to get an answer. As a developer, I learned long time ago to break down code into smallest possible chunks to get the answer.

After I have figured out the relationship between tables, I was able to understand what query was doing. To be able to read query better it can be rewritten in the following way.

Do read Vlad’s post.  I’ve seen terrible misuse of this plus right outer joins, and I don’t know if I’ve ever seen a case where using this syntax made the code easier to understand.

Denis Gobo ran into a problem with a linked server query he ran:

This past week I needed to run some queries on production to verify there were indexes added on a table. There were several scripts that needed to be run and the last one was the addition of the indexes.  The query given to me was something like the following

SELECT *
FROM LinkedServerName.DatabaseName.sys.indexes
WHERE object_id =(OBJECT_ID('TableName'))

So I ran the query..nothing. Aha maybe they are still running the scripts before that, setting up replication, snapshotting the table etc etc. I will check again in a bit I thought.

Click through for the full reason and how to fix your code in this situation.

Kenneth Fisher looks at a few methods for dynamic searches in T-SQL:

Multiple Queries

Pro: We get a separate query plan for each combination of parameters so performance is great. (Well, as good as can be anyway.)
Con: Maintance stinks. We need 16 different queries when we have 4 parameters and the numbers increase dramatically as we add additional parameters. So any change we make to the base query will have to be changed 16 times, and/or adding a new parameter means careful logic and adding a bunch of new queries.

I’d consider this a gateway for Erland Sommarskog’s article on the topic.