Presents your 
SQL SERVER E-NEWSLETTER for July 16, 2002

<------------------------------------------->

ALWAYS CHECK STRING LENGTH DIFFERENCES

The user connection option, SET ANSI_WARNINGS ON, is an ANSI SQL-92
standard behavior. This user connection option is moving to a strong 
typing
of database data types. Strong typing is simply a stringent adherence 
to a
data type. Strong typing doesn't allow assignments or comparisons of
variables of different types.

Similar to strong typing is the programming language C's evolution to
C++. SQL Server 6.5 and db-library's default setting for this option is
OFF, but SQL Server 7.0 and 2000 use ODBC and OLEDB with the default 
setting
ON.

When set to on, this user connection will affect string truncation when
inserting into tables but not when inserting into local variables. It's
good practice to always check the length of strings as input into table
columns. When encountering string length differences, you may want to 
raise
an error to alert the end user to truncate the string he or she is
inserting into the table.

Also, when set to on, the ANSI_WARNING connection option will affect
Unicode or binary column data types. Additionally, users will receive
arithmetic overflow errors for division by zero computations.

The following is a sample script, which demonstrates the error 
generated
when a larger string is inserted into a smaller character string column
in a table. Notice that no error is generated when assigning the larger
string to a smaller character local variable.

USE pubs
GO
SET ANSI_WARNINGS OFF
GO
CREATE TABLE test (char10  CHAR(10) NOT NULL)
GO
INSERT test (char10) VALUES ('01234567890123456789')
GO
DROP TABLE test
GO
DECLARE @char CHAR(10)
SET @char = '012345678910'
SELECT @char
GO
SET ANSI_WARNINGS ON
GO
CREATE TABLE test (char10  CHAR(10) NOT NULL)
GO
-- Statement below generate an error with AANSI_WARNINGS SET ON
INSERT test (char10) VALUES ('01234567890123456789')
GO
DROP TABLE test
GO
-- Statement below has no affect with ANSI__WARNINGS SET ON
DECLARE @char CHAR(10)
SET @char = '012345678910'
SELECT @char
GO

----------------------------------------