Warning or no warning?

Question

Warning or no warning?

ken.trock

SSCertifiable

Points: 5147
More actions
May 25, 2010 at 9:14 am

#224950

declare @test-2 char(10)
set @test-2 = '1,2,3,4,5,6,7,8,9,10,11,12'
select @test-2
Most people realize this returns '1,2,3,4,5,' without any warning or error. Does anyone think it should generate a warning? I know it's set me back in debugging a couple times because our strings got longer and we forgot to make the Varchar column bigger :unsure: Or maybe we should make all our strings varchar(8000) when we're not sure. Seems inefficient even though SQL handles it properly.
Ken

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 1

I'd prefer that it operate like it does... no warning. I'll sometimes (many times, actually) use it as a cheater (LEFT).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 2

But...

wouldn't it be nice to have a SQL Server setting (like IDENTITY_INSERT), something like STRING_TRUNC with ON as a default (truncate strings, like it currently does...)? That would allow us to check if there is some code causing a truncation...

It's not on my top 10 list but I wouldn't wipe it off my all-wishes-list either.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 3

lmu92 (5/25/2010)
But...
wouldn't it be nice to have a SQL Server setting (like IDENTITY_INSERT), something like STRING_TRUNC with ON as a default (truncate strings, like it currently does...)? That would allow us to check if there is some code causing a truncation...
It's not on my top 10 list but I wouldn't wipe it off my all-wishes-list either.

I wonder what the CPU cost would be for such a thing when used on a table column instead of a single variable if some paranoid individual decided the default setting for his server would be OFF?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90262 More actions · Answer 4

lmu92 (5/25/2010)
But...
wouldn't it be nice to have a SQL Server setting (like IDENTITY_INSERT), something like STRING_TRUNC with ON as a default (truncate strings, like it currently does...)? That would allow us to check if there is some code causing a truncation...
It's not on my top 10 list but I wouldn't wipe it off my all-wishes-list either.

There is a setting available - but it is not honored:

From BOL:

Note:
ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

LutzM SSC Guru Points: 107049 More actions · Answer 5

Jeff Moden (5/25/2010)
lmu92 (5/25/2010)
But...
wouldn't it be nice to have a SQL Server setting (like IDENTITY_INSERT), something like STRING_TRUNC with ON as a default (truncate strings, like it currently does...)? That would allow us to check if there is some code causing a truncation...
It's not on my top 10 list but I wouldn't wipe it off my all-wishes-list either.
I wonder what the CPU cost would be for such a thing when used on a table column instead of a single variable if some paranoid individual decided the default setting for his server would be OFF?

As Jeffrey mentioned above it probably would have the same effect like SET ANSI_WARNINGS ON. Maybe the STRING_TRUNC could be used to close the gap ANSI_WARNINGS left open (so it would be limited to [stored procedure, user-defined function, or when declaring and setting variables in a batch statement]).... I'd rather see it treated this way than being faced with an extended version of ANSI_WARNINGS...

Coming back to your original question/concern: I guess there are people around being able to tell us the CPU cost for using ANSI_WARNINGS ON 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1003925 More actions · Answer 6

With what Jeffrey posted from BOL, I'd be content to not have any additional warnings mostly because I'm a defensive programmer to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 7

I don't see why there should be a warning - SQL Server is doing exactly what it was told to do and setting the string to CHAR(10).

ken.trock SSCertifiable Points: 5147 More actions · Answer 8

ken.trock

SSCertifiable

Points: 5147

May 26, 2010 at 8:12 am

#1172750

Thanks for the replies; some food for thought.

Ken