Warning or no warning?
-
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
-
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) -
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.
-
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) -
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 -
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
-
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) -
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).
-
Thanks for the replies; some food for thought.
Ken
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply