"WHERE" condition in MSSQL2005 diff. with Oracle DB

Hi,

I got one question about MSSQL2005.

I am using "Microsoft SQL Server Migration Assistant for Oracle" to migrate Oracle Database to Microsoft SQL server 20005.

[Oracle DB]

In Oracle DB, there are two records in table

CREDITOR_ID CREDIT_ID CREDIT_LIMIT CREDIT_BALANCE CREDIT_ONHAND ACCTSTATUSID CREDITTERMSID EDIT_TIME Record 1 YT4996 XX 1 0 0 A 15 1/1/1999 14:10 Record 2 YT4996 XX 2 0 0 B 30 1/1/1998 17:21

In record 1, CREDITOR_ID='YT4996 ', there is a space after string 'YT4996'.

In record 2, CREDITOR_ID='YT4996', there is no space after string 'YT4996'.

SQL(1)

select xxx from CUSTOMER_ID='YT4996'

--> output 1 rows.

SQL(2)

select xxx from CUSTOMER_ID='YT4996 '

--> output 1 rows.

[MSSQL2005]

After migrate data to MSSQL, there are two identical records as Oracle DB.

CREDITOR_ID CREDIT_ID CREDIT_LIMIT CREDIT_BALANCE CREDIT_ONHAND ACCTSTATUSID CREDITTERMSID EDIT_TIME Record 1 YT4996 XX 1 0 0 A 15 1/1/1999 14:10 Record 2 YT4996 XX 2 0 0 B 30 1/1/1998 17:21

In record 1, CREDITOR_ID='YT4996 ', there is a space after string 'YT4996'.

In record 2, CREDITOR_ID='YT4996', there is no space after string 'YT4996'.

SQL(1)

select xxx from CUSTOMER_ID='YT4996'

--> output 2 rows.

SQL(2)

select xxx from CUSTOMER_ID='YT4996 '

--> output 2 rows.

Question:

==> In Oracle DB, 'YT4996 ' <> 'YT4996'

==> In MSSQL2005, 'YT4996 ' = 'YT4996'

Do any setting, affect MSSQL2005, make 'YT4996 ' = 'YT4996' ?

I want MSSQL2005 behaviour as Oracle....

Anyone have hint?

You must have defined the datatype for the column as varchar. This will force sqlserver to strip the trailing blank spaces. If you change the datatype to char, the trailing spaces will be preserved.|||

Thanks for info.

The column is defined as varchar(12).

but I cannot use char(12), since this column will contain variable width data, e.g. 'WT123', 'WT123456'.

If using char(12), all records in this column will become

'WT123 '

'WT123456 '

which will waste space when using char(12).

|||

"Set Ansi_Padding ON" (non-default) before creating the column will force the trailing blanks not to be truncated. This setting needs to be set also during your execution if you decide to create varchar variables and preserve the trailing blanks.

Here is a good example (from bol):

Code Snippet

SET ANSI_PADDING ON
GO
PRINT 'Testing with ANSI_PADDING ON'
GO

CREATE TABLE t1
(charcol char(16) NULL,
varcharcol varchar(16) NULL,
varbinarycol varbinary(8))
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee)
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00)

SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
GO

SET ANSI_PADDING OFF
GO
PRINT 'Testing with ANSI_PADDING OFF'
GO

CREATE TABLE t2
(charcol char(16) NULL,
varcharcol varchar(16) NULL,
varbinarycol varbinary(8))
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee)
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00)

SELECT 'CHAR'='>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
GO

DROP TABLE t1
DROP TABLE t2
GO

|||

Thanks for your info.

I had test your script, it work.

<<<BUT>>>.......

I find this "SET ANSI_NULLS ON" is not work if the COULMN defined as NOT NULL.

Here is my original script for table [CUSTOMER_CREDIT]

===============================================

USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NOT NULL,
[CREDIT_ID] varchar(6) NOT NULL,
[CREDIT_LIMIT] numeric(12, 2) NULL,
[CREDIT_BALANCE] numeric(12, 2) NULL,
[CREDIT_ONHAND] numeric(12, 2) NULL,
[ACCTSTATUSID] varchar(3) NULL,
[CREDITTERMSID] varchar(3) NULL,
[EDIT_TIME] datetime NULL
)
GO
ALTER TABLE [MAIN].[CUSTOMER_CREDIT]
ADD CONSTRAINT [PK_CUSTOMER_CREDIT]
PRIMARY KEY
CLUSTERED ([CREDITOR_ID] ASC, [CREDIT_ID] ASC)

GO

If I had create the table like this:

CREATE TABLE [MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NULL,
[CREDIT_ID] varchar(6) NOT NULL,
.....

If the COULMN [CREDITOR_ID] defined as NULL, "SET ANSI_PADDING ON" is work, such that I have two records:

[CREDITOR_ID] = 'ABCD ' , ie space after string "ABCD".

[CREDITOR_ID] = 'ABCD' , ie no space after string "ABCD".

But if the COULMN [CREDITOR_ID] defined as "NOT NULL", "SET ANSI_PADDING ON" is NOT work.

Any ideal?

|||

Here is the script to create table and testing:

USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MAIN].[CUSTOMER_CREDIT]') AND type in (N'U'))
BEGIN

DECLARE @.drop_statement varchar(500)

DECLARE drop_cursor CURSOR FOR
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
'.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name)
FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
WHERE fk.referenced_object_id = OBJECT_ID(N'[MAIN].[CUSTOMER_CREDIT]')

OPEN drop_cursor

FETCH NEXT FROM drop_cursor
INTO @.drop_statement

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC (@.drop_statement)

FETCH NEXT FROM drop_cursor
INTO @.drop_statement
END

CLOSE drop_cursor
DEALLOCATE drop_cursor

DROP TABLE [MAIN].[CUSTOMER_CREDIT]
END
GO

USE DBSYSTEM
GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE
[MAIN].[CUSTOMER_CREDIT]
(
[CREDITOR_ID] varchar(10) NOT NULL,
[CREDIT_ID] varchar(6) NOT NULL,
[CREDIT_LIMIT] numeric(12, 2) NULL,
[CREDIT_BALANCE] numeric(12, 2) NULL,
[CREDIT_ONHAND] numeric(12, 2) NULL,
[ACCTSTATUSID] varchar(3) NULL,
[CREDITTERMSID] varchar(3) NULL,
[EDIT_TIME] datetime NULL
)
GO
ALTER TABLE [MAIN].[CUSTOMER_CREDIT]
ADD CONSTRAINT [PK_CUSTOMER_CREDIT]
PRIMARY KEY
CLUSTERED ([CREDITOR_ID] ASC, [CREDIT_ID] ASC)

GO
===============================================================

SET ANSI_PADDING ON

GO

INSERT INTO [MAIN].[CUSTOMER_CREDIT] VALUES ('YT4996', 'XX','2400','0','0','C','30',CURRENT_TIMESTAMP)

GO

SET ANSI_PADDING ON

GO

INSERT INTO [MAIN].[CUSTOMER_CREDIT] VALUES ('YT4996 ','XX','0' ,'0','0','S','15',CURRENT_TIMESTAMP)

GO

SELECT 'VARCHAR'='>' + CREDITOR_ID + '<' FROM [MAIN].[CUSTOMER_CREDIT]
GO