Do you know what data type is used when running ad-hoc queries 1

[SQLDenis]

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 (

http://sqlblog.com/blogs/louis_davidson/default.aspx).

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.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nice95gle]

Nice to know, thanks

Well Done is better than well said
- Ben Franklin

 

[ESquared]

CREATE FUNCTION VarType(@Variable sql_variant)
RETURNS varchar(256)
AS
BEGIN
   DECLARE @VarType varchar(256)
   SET @VarType = Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType'))
   IF @VarType LIKE '%char%' OR @VarType LIKE '%binary%' BEGIN
      SET @VarType = @VarType + '(' + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'MaxLength')) + ')'
   END
   ELSE IF @VarType IN ('decimal', 'money', 'numeric') BEGIN
      SET @VarType = @VarType + '('
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Precision'))
         + ','
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Scale'))
         + ')'
   END
   RETURN @VarType
END

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[Jasen]

Yet another reason to avoid implicit conversions.