October 5, 2005 at 3:48 pm
I am wondering whether Trimming Varchar data is good for the space issue in database or overload (bad) for the I/O?
Any suggestion?
October 5, 2005 at 7:01 pm
ALWAYS trim your data prior to an Insert or Update. Some day, for some reason, Someone WILL absolutely write a procedure that does the following:
SELECT * from MyTable WHERE LEN(Column1) > 1
You can enforce this by UI processing standards, or if you are the DBA.. You can insist that all Inserts and Updates process varchar parameters with LTrim(RTrim(@param)).
-Mike Gercevich
October 6, 2005 at 5:51 am
There are other factors that govern this as well. In SQL Server, ANSI settings control the "storage" part of the trailing space and that is done at the time of the creation of the tables:
SQL Server follows the ANSI standard on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.
The SET ANSI_PADDING setting does not affect whether SQL Server pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons. And the setting is always on by default
Example:
CREATE TABLE MANISH_TEST (COL1 VARCHAR(10))
GO
--W/O SPACE
INSERT INTO MANISH_TEST VALUES ('MANISH');
--WITH SPACE
INSERT INTO MANISH_TEST VALUES ('MANISH ');
GO
SELECT COL1, DATALENGTH (COL1) AS LENGTH
FROM MANISH_TEST
GO
COL1 LENGTH
---------- -----------
MANISH 6
MANISH 7
SELECT COUNT(1) CNT FROM MANISH_TEST WHERE COL1 = 'MANISH '
GO
SELECT COUNT(1) CNT FROM MANISH_TEST WHERE COL1 = 'MANISH'
GO
CNT
-----------
2
CNT
-----------
2
NOTE: If the table was created with ANSI_PADDING set to OFF, then SQL Server will automatically remove the space...however, as I mentioned before, it follows the ANSI behavior and that does not effect comparisons:
SET ANSI_PADDING OFF
GO
CREATE TABLE MANISH_TEST (COL1 VARCHAR(10))
GO
--W/O SPACE
INSERT INTO MANISH_TEST VALUES ('MANISH');
--WITH SPACE
INSERT INTO MANISH_TEST VALUES ('MANISH ');
GO
SELECT COL1, DATALENGTH (COL1) AS LENGTH
FROM MANISH_TEST
GO
COL1 LENGTH
---------- -----------
MANISH 6
MANISH 6 => NOTE: It removed the trailing space because you told it to do so.
SELECT COUNT(1) CNT FROM MANISH_TEST WHERE COL1 = 'MANISH '
GO
SELECT COUNT(1) CNT FROM MANISH_TEST WHERE COL1 = 'MANISH'
GO
CNT
-----------
2
CNT
-----------
2
Hth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply