Denis Gobo : Tip, sqL Server 2000

Return Null If A Value Is A Certain Value

Denis Gobo — Tue, 06 Nov 2007 18:28:00 GMT

You need to return NULL only if the value of your data is a certain value. How do you do this? There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT NULLIF(@1,'D')

REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)

CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

Increase Your Productivity With Query Analyzer/SQL Server Management Studio

Denis Gobo — Mon, 24 Sep 2007 15:52:00 GMT

Drag And Drop Column Names
In Query Analyzer you can save a lot of time by using this trick instead of typing all the column names of a table
Hit F8, this will open Object Browser
Navigate to DatabaseName/TableName/Columns
Click on the column folder and drag the column folder into the Code Window
Upon release you will see that all the column names are in the Code Window

Use Shortcuts
Did you know that you can have CTRL + Number key combinations/shortcuts in Query Analyzer? You can set it up so that CTRL + 5 executes sp_who2 for example. I thought everyone already knew this but apparently not; I showed this to 3 coworkers yesterday and they didn't know about it. So how do you set this up? It is pretty easy, select Tools -->Customize from the toolbar. In the Customize tab you will see 3 shortcuts already:

ALT + F1 sp_help

CTRL + 1 sp_who

CTRL + 2 sp_lock

To add your own queries/procedures type or paste the query or stored procedure that you want to execute into one of the empty ones (see picture below)

If you have 4 Query Analyzers applications open it will be available in all 4 of them. I usually have 4 Query Analyzer applications open, one for staging, one for development and one each for one of the production boxes. so if I have to quickly check that a table is the same on all 4 machines I just hit CTRL + 6 in all 4 Query Analyzer applications and I am done.

I created a Word document that looks like this
3 - errorlog
4 - product table
5 - country table
6 - vendor codes
7 - sp_who2
etc etc etc

The font-size is 32, I printed this out and put it next to my monitor so that I can quickly see which key combination I have to hit. Once you set this up you will see how much time it will save you.

In SSMS 2005. Click Tools -> Customize -> "Commands" tab -> "Keyboard" button (Lower left corner of screen).On the options window, click the Keyboard option (under Environment). See pictures below:

SQL Gotcha: Do you know what data type is used when running ad-hoc queries?

Denis Gobo — Mon, 10 Sep 2007 14:31:00 GMT

This is for SQL Server 2000 only, SQL Server 2005 is a lot smarter which is another reason to upgrade.
When running the following query you probably already know that 2 is converted to an int datatype.

SELECT *
FROM Table
WHERE ID =2

What about the value 2222222222? Do you think since it can't fit into an int that it will be a bigint? Let's test that out.
First create this table.
CREATE TABLE TestAdHoc (id bigint primary key)

INSERT INTO TestAdHoc
SELECT 1 UNION
SELECT 2433253453453466666 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6

Now let's run these 2 queries which return the same data

SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666

SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)

Now run the following SET statement and run the 2 queries again

SET SHOWPLAN_TEXT ON

SELECT *
FROM TestAdHoc
WHERE ID =2433253453453466666

SELECT *
FROM TestAdHoc
WHERE ID =CONVERT(bigint,2433253453453466666)

And what do we see?

First Query
--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004]))
--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1,
[Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL)
then 0 else 6If (Convert([@1])+1=NULL) then 0 else 10))
--Constant Scan
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id] > [Expr1002] AND [TestAdHoc].[id] < [Expr1003]), WHERE:(Convert([TestAdHoc].[id])=[@1]) ORDERED FORWARD)

Second Query
--Clustered Index Seek(OBJECT:([Blog].[dbo].[TestAdHoc].[PK__TestAdHoc__2818EA29]),
SEEK:([TestAdHoc].[id]=2433253453453466666) ORDERED FORWARD)

The first query has a much different execution plan than the second query. The first execution plan has a lot more than the second execution plan and will be a little slower.

So how do you know what dataype the value is converted to? Here is a simple SQL query which I first saw on Louis Davidson's blog. Just run this query.

SELECT CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'BaseType') AS varchar(20)) + '(' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Precision') AS varchar(10)) + ',' +
CAST(SQL_VARIANT_PROPERTY(2433253453453466666,'Scale') AS varchar(10)) + ')'

So the output is numeric(19,0). So instead of a bigint SQL Server converts the value to a numeric data type.
Here is another query which demonstrates the different datatypes used.

SELECT CAST(SQL_VARIANT_PROPERTY(2,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(222222222,'BaseType') AS varchar(20))
UNION ALL
SELECT CAST(SQL_VARIANT_PROPERTY(2222222222,'BaseType') AS varchar(20))

So when running ad-hoc queries it is always a good practice to use parameters or inline convert statements.