Systems Engineering and RDBMS

Archive for February 15th, 2007

ANSI_PADDING and trailing spaces in SQL Server

Posted by decipherinfosys on February 15, 2007

In SQL Server, ANSI_PADDING setting controls 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 ‘xyz’ and ‘xyz ‘ 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 DEMO1_TEST (COL1 VARCHAR(10))
GO
–W/O SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1’);
–WITH SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1 ‘);
GO
SELECT COL1, DATALENGTH (COL1) AS LENGTH
FROM DEMO1_TEST
GO
COL1       LENGTH
———- ———–
DEMO1     5
DEMO1     6

SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1 ‘
GO
SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1’
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:

DROP TABLE DEMO1_TEST
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE DEMO1_TEST (COL1 VARCHAR(10))
GO
–W/O SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1’);
–WITH SPACE
INSERT INTO DEMO1_TEST VALUES (‘DEMO1 ‘);
GO
SELECT COL1, DATALENGTH (COL1) AS LENGTH
FROM DEMO1_TEST
GO

COL1       LENGTH
———- ———–
DEMO1     5
DEMO1     5                => NOTE: It removed the trailing space because you told it to do so.

SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1 ‘
GO
SELECT COUNT(1) CNT FROM DEMO1_TEST WHERE COL1 = ‘DEMO1’
GO

CNT
———–
2

CNT
———–
2

Trailing spaces should never be allowed through the application.  All the RDBMS do not behave the same way and the onus thus lies upon the application to never allow trailing spaces.  It has other implications as well at the application tier.  Spaces should only be allowed if they are part of the data itself like ‘John Doe’s Wonderful Show’ where the spaces mean something.

Posted in SQL Server | 2 Comments »

Thumb rule for Export/Import in Oracle

Posted by decipherinfosys on February 15, 2007

Just a little tidbit.  Here is a thumb rule for doing export import between different Oracle Versions:

There are scenarios where one needs to export from a lower version and import to a higher version of Oracle or vice-versa.  The thumb rule is:

Take the export using the export utility of the lower version and do the import using the import utitlity of the target i.e. the destination version.  So, for example: If you have to export/import from 9.2.0.6 to 10.1.0.2, you should take an export using 9.2.0.6 and then do an import using 10.1.0.2

There are of course other caveats if there are other differences like the character sets are largely different between the two databases, the char versus the byte semantics etc.  We will cover those in one of our future blog posts.

Posted in Oracle | Leave a Comment »