The strange case of the missing indexes....

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.

Then I checked 2 more times nothing. So I then contacted them and asked if they ran everything..yes they said and no errors.  Mmm, okay..what could be wrong. The only way I can access this server is through a linked server call. I decided to look at the query again.... bingo.. I see what the problem is.....

Let's take a look. First create the following database with 2 tables and 2 indexes on the Test table

CREATE DATABASE Test1
GO


USE Test1
GO

CREATE TABLE Foo(ID int)
GO

CREATE TABLE Test(ID int, ID2 int, ID3 int)
GO

CREATE INDEX ix_Test on Test(ID)
CREATE INDEX ix_Test2 on Test(ID2)

Now if you run the following query against the sys.indexes object catalog view

SELECT  FROM sys.indexes
WHERE object_id = OBJECT_ID('Test')

You will get back the following information

object_id	name	index_id	type	type_desc
901578250	NULL	0	0	HEAP
901578250	ix_Test	2	2	NONCLUSTERED
901578250	ix_Test2	3	2	NONCLUSTERED

As you can see we have information about our two indexes and the table itself, since there is no clustered index on this table, we get a row back that shows that the table is a heap

Now let us connect to another database on the same server, in this case we will use the tempdb database

Create a table with the same name

USE tempdb
GO

CREATE TABLE Test(foo int)
GO

Now run the same query again but point to the Test1 database

SELECT * FROM Test1.sys.indexes
WHERE object_id = OBJECT_ID('Test')

And you get nothing back.  What does the OBJECT_ID() function return?

SELECT OBJECT_ID('Test')

For me it returns the number 965578478. The problem is that that object_id is the id for that object in the tempdb database, not for the one in the Test1 database

So what can you do?  There are two ways to do this

One way is to join sys.indexes with the sys.tables object catalog view

Here is what the query looks like

SELECT i.* 
FROM Test1.sys.indexes i
JOIN Test1.sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'Test'

Running the query like that displays the correct information

object_id	name	index_id	type	type_desc
901578250	NULL	0	0	HEAP
901578250	ix_Test	2	2	NONCLUSTERED
901578250	ix_Test2	3	2	NONCLUSTERED

If you want to use this against a linked server, use 4 part notation, just prefix Test1.sys with the linked server name, I used LinkedServerName as an example

SELECT i.* 
FROM LinkedServerName.Test1.sys.indexes i
JOIN LinkedServerName.Test1.sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'Test'

That query with the join between sys.indexes and sys.tables can be used for a linked server as well as a different database on the same instance, if you just have to go to another database like we have done before, you can simplify it like this

SELECT  * FROM Test1.sys.indexes
WHERE object_id = OBJECT_ID('Test1.dbo.Test')

Basically, you pass in the database name, schema name and object name to the OBJECT_ID() function

That's all for today..

Using bigint with FORMATMESSAGE

SQL Server 2016 added the FORMATMESSAGE function.  According to Books On Line, FORMATMESSAGE constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

So let's take a look at this new function, run the following

SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50) 
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50); 

Here is the output if you run that

--------------------------------------------
Signed int 50, -50
Unsigned int 50, 4294967246

Here is what the type specifications that you can use are

Type specification		Represents
d or i		Signed integer
o		Unsigned octal
s		String
u		Unsigned integer
x or X		Unsigned hexadecimal

We used i to denote a signed integer, we also used u to denote a unsigned integer


Let's look at another example, this time we are using a variable. The variable will be an integer and we  are using i as the type specification

DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------
The value you supplied 1 is incorrect!


That worked without a problem. Now let's use a variable of the bigint data type, we are using the same type specification as before

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------------------------------------------
Error: 50000, Severity: -1, State: 1. (Params:).
The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.


As you can see that did not work, so what can we do?

One thing we can do is converting the value to a varchar and then use s as the type specification

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
   CONVERT(VARCHAR(100),@Val));

You will again get this as output

---------------------------------------
The value you supplied 1 is incorrect!

So converting to varchar worked, but what if we want to use a bigint data type without converting to a varchar?

Another way is to use I64d as the type specification

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);

You will get this

---------------------------------------
The value you supplied 1 is incorrect!

So there you have it, if you want to use bigint with FORMATMESSAGE use I64d as the type specification, or convert to varchar and use s as the type specification

Connecting to SQL Server when your default database is not available

One of our database on the development went in suspect mode today. This database was the default for a bunch of logins.  These people could not login now. Someone needed to use a different database but he couldn’t login because the database that was in suspect mode was the default database for the login he was using.

I told this person to click on the Options button in the connection dialog and specify another database. I guess there was an misunderstanding because this person couldn’t get it to work. This means it is time for a blog post.

Let's take a look how this all works

Here is a script that will create 2 databases

CREATE DATABASE Good
GO
 
CREATE DATABASE OopsBad
GO

Now create a new login named TestLogin with a password of Test. We are also adding the login we just created to the OopsBad database and we will make the login part of the db_owner role

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[OopsBad]
USE [OopsBad]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
USE [OopsBad]
GO
ALTER ROLE [db_owner] ADD MEMBER [TestLogin]
GO

Add the login we just created to the Good database as well and make the login part of the db_owner role

USE [Good]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
USE [Good]
GO
ALTER ROLE [db_owner] ADD MEMBER [TestLogin]
GO

Make sure that you can login with the TestLogin account

Now that you know that you can login with the TestLogin account, use another account and put the OopsBad database in offline mode

Take the database online

ALTER DATABASE OopsBad SET OFFLINE

Now if you try to login with the TestLogin account, you will see the following error

Here is what you need to do, on the connect to server window, click on the Options button

One the next screen, click on the Connection Properties tab, do not click on  Browse server... from the drop down.  If you click that you might have to wait for a long time

Instead just type in the database name, in our case the database name is Good

Click on Connect and you should be in....  That's it, pretty simple, you just have to know where to find it.

BULK INSERT with a file containing linefeeds gotcha

I blogged about this before but someone ran into this issue again at work so I thought it made sense to revisit this.

I prefer to use BULK INSERT or bcp as much as possible, this is especially true if all I need to do is dump the file into a table. If there is a need to do complex stuff,  I will use SQL Server Integration Services or Informatica. These days files are generated by all kinds of systems, these can be Windows, *nix, Mac, Raspberry Pis, IoT systems and many other systems.

If you get an error importing one of these files, your first course of action is to open one of these files in something like Notepad++ or Editplus.  Notepad++ and Editplus have more functionality and are many times faster than notepad. The nice thing about either of these is that you can see control characters.

See how you can see the linefeeds here? You cannot do that in notepad

So let's say you get a file where the row terminator is a linefeed, how would you specify that as a row terminator in BULK INSERT?

You can try n which stands for newline

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = 'n')

Nope, that doesn't work, you get 0 rows inserted


You can try r which stands for carriage return

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = 'r')

Nope, that doesn't work either, you get 0 rows inserted


What about l for linefeed?

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = 'l')

You get an error

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (SomeDate).



What about if you try a CHAR(10) which is a linefeed

BULK INSERT SomeTable
 FROM 'D:\JunkdrawImportMe.txt'
 WITH (FIELDTERMINATOR = 't',
 FIRSTROW =2,
 ROWTERMINATOR = CHAR(10) )

You get this error

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'CHAR'.



How about if you embed it by using Dynamic SQL

DECLARE @cmd varchar(1000)
SET @cmd = 'BULK INSERT SomeTable
FROM ''D:\JunkdrawImportMe.txt''
WITH ( FIELDTERMINATOR = ''t'',
 FIRSTROW =2,
 ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@cmd)

The dynamic SQL solution works, that was my original answer to this.

What about if we use the hex code for line feed 0x0a?

BULK INSERT SomeTable
FROM 'D:\JunkdrawImportMe.txt'
WITH (FIELDTERMINATOR = 't',
FIRSTROW =2,
ROWTERMINATOR = '0x0a') --CHAR(10) or ‘l’ does not work 
-- 0x0a is the hex code for linefeed CHAR(10)

Bingo, that works as well.  I actually prefer this, who wants to muck around with Dynamic SQL if there is an easier way....

That's all, keep this in mind next time you get a file with a linefeed and you are struggling importing that file

Sometimes, you can stare at something for 5 minutes before seeing it

The other day I was running some code and the variable that I created was not being populated. I looked at the code for a minute or so, ran it again (like that would change it) and it was still null. Before I show you the code, I want you to look at this image, what do you see?

 If you have never seen this image you will probably see Paris in the spring. But look closer, do you see the second the on the third line?

The way this works is that your brain eliminates the second the since it already processed.

Take a look at this text below, you probably have no problem "reading" it


"I cdn'uolt blveiee taht I cluod aulaclty uesdnatnrd waht I was rdanieg: the phaonmneel pweor of the hmuan mnid. Aoccdrnig to a rseearch taem at Cmabrigde Uinervtisy, it deosn't mttaer in waht oredr the ltteers in a wrod are, the olny iprmoatnt tihng is taht the frist and lsat ltteer be in the rghit pclae. The rset can be a taotl mses and you can sitll raed it wouthit a porbelm. Tihs is bcuseae the huamn mnid deos not raed ervey lteter by istlef, but the wrod as a wlohe. Scuh a cdonition is arppoiatrely cllaed Typoglycemia .

"Amzanig huh? Yaeh and you awlyas thguoht slpeling was ipmorantt."

So back to my code..here is a simple example

DECLARE @name varchar(100)

SELECT @name = @name FROM sys.databases
WHERE database_id = 6


SELECT @name

Do you see the problem?  The table has a column named name, the variable is @name, these look very similar


Let's take a look at another example, one of my co-workers was inserting data into a temp table from another table.

INSERT #Temp

SELECT lastrun, cpu_busy, io_busy, idle, pack_received,  connections, pack_errors, 

total_read, total_write, total_errors
FROM master.dbo.spt_monitor

He then needed to add a column, he modified the temp table to add this column, then he added this column to his insert query, the column name was pack_sent

INSERT #Temp
SELECT lastrun, cpu_busy, io_busy, idle, pack_received pack_sent, connections, 
pack_errors, total_read, total_write, total_errors
FROM master.dbo.spt_monitor

Running that gave him this error

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.


Do you see what happened?  He added the column but forgot a comma, so pack_sent became an alias for the pack_received column. Sometimes I really wished as would be required to alias a column, at least you could eliminate errors like these,


So how many times have you done stuff like this..also do you have any other examples of similar stuff?

Gotcha with the LEAD analytical function and date datatype

The other day some code that used the LEAD analytical function stopped working suddenly. It started to throw an error.

Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

The code wasn't touched in a long time... so what could be the issue. Let's reproduce it here

First create the following table

CREATE TABLE testRunningTotal(Id tinyint,SomeDate datetime, Charge decimal(20,2))

INSERT testRunningTotal
SELECT 1,'20120101',1000
UNION ALL
SELECT 1,'20120401',200
UNION ALL
SELECT 1,'20120501',300
UNION ALL
SELECT 1,'20120601',600
UNION ALL
SELECT 2,'20120101',100
UNION ALL
SELECT 2,'20130101',500
UNION ALL
SELECT 2,'20140101',-800
UNION ALL
SELECT 3,'20120101',100

Running this query to see what is in this table

SELECT * FROM testRunningTotal

Id SomeDate Charge

1 2012-01-01 00:00:00.000 1000.00

1 2012-04-01 00:00:00.000 200.00

1 2012-05-01 00:00:00.000 300.00

1 2012-06-01 00:00:00.000 600.00

2 2012-01-01 00:00:00.000 100.00

2 2013-01-01 00:00:00.000 500.00

2 2014-01-01 00:00:00.000 -800.00

3 2012-01-01 00:00:00.000 100.00

Now run our fancy query...

SELECT id, someDate as StartDate,
LEAD(SomeDate -1,1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

Here are our results

id StartDate Enddate Charge RunningTotal

1 2012-01-01 00:00:00.000 2012-03-31 00:00:00.000 1000.00 1000.00

1 2012-04-01 00:00:00.000 2012-04-30 00:00:00.000 200.00 1200.00

1 2012-05-01 00:00:00.000 2012-05-31 00:00:00.000 300.00 1500.00

1 2012-06-01 00:00:00.000 9999-12-31 00:00:00.000 600.00 2100.00

2 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 100.00 100.00

2 2013-01-01 00:00:00.000 2013-12-31 00:00:00.000 500.00 600.00

2 2014-01-01 00:00:00.000 9999-12-31 00:00:00.000 -800.00 -200.00

3 2012-01-01 00:00:00.000 9999-12-31 00:00:00.000 100.00 100.00

To see what the query actually does, visit this post Easy running totals with windowing functions

Let's take a closer look at the LEAD analytical function, this is what Books On Line has on its page

  LEAD ( scalar_expression [ ,offset ] , [ default ] ) 

    OVER ( [ partition_by_clause ] order_by_clause )

offset

The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

Looks all cool to me..nothing that stands out that could cause a problem...

What will happen if someone changes the column from a datetime to a date? Maybe they looked at the data and saw that all the values had only dates and no time components... why use 8 bytes of storage when you can use 3 instead?

To find out exactly what will happen, run the following DDL statement....

ALTER TABLE testRunningTotal ALTER COLUMN SomeDate date

Now go run your query again

SELECT id, someDate as StartDate,
LEAD(SomeDate -1,1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

Here is the error you get

  Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

The error is the same as if you would run the following

SELECT CONVERT(DATE,GETDATE() )-1

Instead what you have to do is use the DATEADD function to do arithmetic with dates

SELECT DATEADD(dd,-1,CONVERT(DATE,GETDATE()))

So to fix our query from before, we want to change the -1 to be DATEADD(dd,-1 instead, here is what our query looks like

 SELECT id, someDate as StartDate,
LEAD(dateadd(dd,-1,SomeDate),1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

And here are our results, it's all good now

id StartDate Enddate Charge RunningTotal

1 2012-01-01 2012-03-31 1000.00 1000.00

1 2012-04-01 2012-04-30 200.00 1200.00

1 2012-05-01 2012-05-31 300.00 1500.00

1 2012-06-01 9999-12-31 600.00 2100.00

2 2012-01-01 2012-12-31 100.00 100.00

2 2013-01-01 2013-12-31 500.00 600.00

2 2014-01-01 9999-12-31 -800.00 -200.00

3 2012-01-01 9999-12-31 100.00 100.00

I still think that maybe the LEAD function should do this conversion internally since all we are doing is specifying the OFFSET...what is your opinion?

Whenever you use the designer in SSMS, click on the Generate Change Script icon to verify what it will do

SQL Server Management Studio is pretty good tool to manage and develop against SQL Server instances. You have to be aware of some things that can be dangerous. Just the other day I had to help someone on a development environment who had a problem when he made a column bigger


The table looked like this

CREATE TABLE [TestTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[itemdate] [datetime] NOT NULL ,
[title] [varchar] (30) NOT NULL ,
) ON [PRIMARY]

So what the person wanted to do was make the title column 50 characters instead of 30. He right clicked on the table, clicked on Design, made the change and hit the save button. He then tried to run a query against the table and it was stuck, nothing was being returned. After a couple of minutes all seemed fine.
So what happened? Behind the scenes SSMS actually created a new table, dumped all that data into that table, dropped the original table and then renamed this new table to match the name of the original table



Here is what the script looks like that SSMS generates

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_TestTable
(
id int NOT NULL IDENTITY (1, 1),
itemdate datetime NOT NULL,
title varchar(50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_TestTable SET (LOCK_ESCALATION = TABLE)
GO

SET IDENTITY_INSERT dbo.Tmp_TestTable ON
GO

IF EXISTS(SELECT * FROM dbo.TestTable)
EXEC('INSERT INTO dbo.Tmp_TestTable (id, itemdate, title)

SELECT id, itemdate, title FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO

SET IDENTITY_INSERT dbo.Tmp_TestTable OFF
GO

DROP TABLE dbo.TestTable
GO

EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT

All that can be replaced with this simple one line command, it runs much faster and it doesn't recreate anything, no data is being moved around either.

ALTER TABLE [TestTable] ALTER COLUMN [title] [varchar] (50)

The only time you should be using the designer is if the table is empty or if you want to see what kind of T-SQL the designer generates. You can see the T-SQL that SSMS generates by clicking on the Generate Change Script icon, you can then copy the T-SQL into a SSMS query window to look at it. You can see the Generate Change Script icon in the image below

 

By the way, SSMS will by default disable the recreation of tables when clicking on the save button, this can be disabled or enabled by unchecking or checking the highlighted option in the image below. You can get to this by clicking Tools-->Options-->Table and Database Designers

Defaults with parameters in SQL Server stored procedures gotcha

I was talking with someone today and this person told me that the stored procedure doesn't work as expected



Here is what the simplified code looked like

CREATE PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int = 1,
@SomeThirdParam int
AS
SELECT @SomeOtherParam
GO

The person told me that when calling the proc and passing in NULL, the value would be NULL, he expected the value to be 1 since that is the default. Running the code like this will return the value NULL

EXEC prTest 1,null,3

In order to omit this parameter you can't be passing it in. If your parameter is the last one then you can just omit the value and pass in the first 2 parameters. If your parameter is not last,  then use named parameters instead of positional parameters. if you run the query like this, you will get back the value 1

EXEC prTest @SomeParam = 1,@SomeThirdParam = 3

Here is an example where the last parameter has a default

ALTER PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int ,
@SomeThirdParam int =3
AS
SELECT @SomeThirdParam
GO

Running that proc with just the first 2 values will return the default

EXEC prTest 1,2

So to recap..... a default will only be applied when the parameter is not passed in, if you pass in a NULL, which is technically the absence of a value, NULL will be used

Do you depend on sp_depends (no pun intended)

HTML Source EditorWord wrap I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this

First create this proc

CREATE PROC SomeTestProc
AS
SELECT dbo.somefuction(1)
GO


now create this function

CREATE FUNCTION somefuction(@id int)
RETURNS int
AS
BEGIN
SELECT @id = 1
RETURN @id
END
Go


now run this


sp_depends 'somefuction'

result: Object does not reference any object, and no objects reference it.



Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT

specific_name,*

FROM information_schema.routines

WHERE

object_definition(object_id(specific_name)) LIKE '%somefuction%'

AND

routine_type = 'procedure'

BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character

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

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 this 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.