SQL temp table issue

Question

SQL temp table issue

aasree

SSC-Addicted

Points: 483
More actions
April 20, 2017 at 7:20 am

#320560

Is there any patch made in SQL about handling temporary table?
Previously the SQL (2012) is seemingly forgiving that when you create a temp table like this posed no problem:
create table dbo.#tmp_bcp (
dataid int primary key identity(1,1),
data varchar(995)
)

--and if we insert a value with about 1000 characters:
insert into dbo.#tmp_bcp
select @TextWith1000Characters
The above existing code is running without a problem for years(recent values before this incident has more than 995 characters) until last week when it starts to
be complaining and reporting a truncation error when value has more than 995 characters.
Any ideas? We just don't change production script right away by immediately changing the declaration above from data varchar(995) to about data varchar(2000)
Any configuration need to be changed?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

aasree - Thursday, April 20, 2017 7:20 AM
Is there any patch made in SQL about handling temporary table?
Previously the SQL (2012) is seemingly forgiving that when you create a temp table like this posed no problem:
create table dbo.#tmp_bcp (
dataid int primary key identity(1,1),
data varchar(995)
)

--and if we insert a value with about 1000 characters:
insert into dbo.#tmp_bcp
select @TextWith1000Characters
The above existing code is running without a problem for years(recent values before this incident has more than 995 characters) until last week when it starts to
be complaining and reporting a truncation error when value has more than 995 characters.
Any ideas? We just don't change production script right away by immediately changing the declaration above from data varchar(995) to about data varchar(2000)
Any configuration need to be changed?

Was the ANSI_WARNINGS turned OFF on that server?
😎

Phil Parkin SSC Guru Points: 246965 More actions · Answer 2

'Forgiving' is not the word I would use, more like 'silently truncating data'. Here is some code to demonstrate the validity of Eirikur's suggestion.

SET ANSI_WARNINGS OFF;

IF OBJECT_ID('tempdb..#tmp_bcp', 'U') IS NOT NULL DROP TABLE #tmp_bcp;

CREATE TABLE #tmp_bcp ( dataid INT PRIMARY KEY IDENTITY(1, 1) , data VARCHAR(995) );

DECLARE @TextWith1000Characters VARCHAR(1000) = REPLICATE('x', 1000);

INSERT #tmp_bcp ( data ) SELECT @TextWith1000Characters;

SELECT tb.dataid , tb.data , LEN(tb.data) FROM #tmp_bcp tb;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

In addition to other statements, I ALWAYS question this type of structure in temp objects in client code:

dataid int primary key identity(1,1),

In my experience (~45000 hours with SQL Server, most as a consultant) that is almost always a WASTE OF EFFORT for NO BENEFIT! If you do not have an explicit need for that, remove it. And if you think you do, try to figure out a way to not.

Never forget that the fastest thing you can do in SQL Server is ... wait for it ... NOTHING! 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

aasree SSC-Addicted Points: 483 More actions · Answer 4

TheSQLGuru - Thursday, April 20, 2017 9:30 AM
In addition to other statements, I ALWAYS question this type of structure in temp objects in client code:
dataid int primary key identity(1,1),
In my experience (~45000 hours with SQL Server, most as a consultant) that is almost always a WASTE OF EFFORT for NO BENEFIT! If you do not have an explicit need for that, remove it. And if you think you do, try to figure out a way to not.
Never forget that the fastest thing you can do in SQL Server is ... wait for it ... NOTHING! 😎

I appreciate your advise. Many developers nowadays are dealing with legacy codes 🙂

aasree SSC-Addicted Points: 483 More actions · Answer 5

Thanks Eirikur and Phil.

In some institutions sql devs and dba's are geographically dispersed.
Devs may not be aware of the sudden changes by DBA's in Server configuration manager settings that can impact the operation of stored procedures.