The difference in the handling of the spaces between Oracle and SQL Server(In the event of linking different DBMS)

em8215 — Mon, 11 Apr 2022 12:38:47 +0000

This article is a continuation of last time's article.
I experimented with handling the spaces when linking Oracle and SQL Server this time.

Experiment

Table and data query

The query and DBMS's version are the same as the previous one.

CREATE TABLE CompareTestTable
(
     Seq         NUMBER
    ,ColCHAR     CHAR(10)
    ,ColVARCHAR  VARCHAR2(10)
);

INSERT INTO CompareTestTable VALUES( 1, 'aaa',  'bbb'  );       -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' );       -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' );       -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' );     -- Spaces after and before a string

CREATE TABLE CompareTestTable
(
     Seq         INT
    ,ColCHAR     CHAR(10)
    ,ColVARCHAR  VARCHAR(10)
)
INSERT INTO CompareTestTable VALUES( 1, 'aaa',  'bbb'  );       -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' );       -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' );       -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' );     -- Spaces after and before a string

SQL Server => Oracle (With using Link Server of SQL server, tables in oracle are accessed.)

The name of Link Server is created as "Oracle".
Tables of Oracle are in "TEST_USER" schema.

select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = 'aaa';       -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = 'aaa ';      -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = ' aaa';      -- Hit SEQ=3,4 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColCHAR = ' aaa ';     -- Hit SEQ=3,4 records

select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = 'bbb';    -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = 'bbb ';   -- Hit SEQ=1,2 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = ' bbb';   -- Hit SEQ=3,4 records
select * from [ORACLE]..[TEST_USER].[COMPARETESTTABLE] where ColVARCHAR = ' bbb ';  -- Hit SEQ=3,4 records

This result is the same as the previous result of SQL Server.
It is indicated that once the data was retrieved from Oracle to SQL Server, then the condition was evaluated at SQL Server.

As a side note, with using OPENQUERY of SQL Server which is to execute query at Oracle, the result is the same as the previous result of Oracle.

Oracle => SQL Server (With using Database Link of Oracle, tables in SQL Server are accessed.)

The name of Database Link is created as "SQL_SERVER".

select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = 'aaa';       -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = 'aaa ';      -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = ' aaa';      -- Hit SEQ=3,4 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColCHAR" = ' aaa ';     -- Hit SEQ=3,4 records

select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = 'bbb';    -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = 'bbb ';   -- Hit SEQ=1,2 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = ' bbb';   -- Hit SEQ=3,4 records
select * from COMPARETESTTABLE@SQL_SERVER where "ColVARCHAR" = ' bbb ';  -- Hit SEQ=3,4 records

This result is unexpected.
I think the result is the same as the previous result of Oracle, but actually is the same as the above result of SQL Server.
I wonder if the query was executed at SQL Server.

I investigated the history of the execution query at SQL Server with the following query.

SELECT TOP 1000
QS.creation_time,
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
ST.text,
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
ORDER BY
QS.creation_time DESC

Just as expected, the query was executed at SQL Server.

Conclustion

Including the previous experiment, the summary is following

In the event of column type is CHAR, the result of a search query is the same while a different result will have occurred in the case of VARCHAR.
With using Link Server to connect from SQL Server to tables in Oracle, the query is executed as search specification of SQL Server.
With using Database Link to connect from Oracle to tables in SQL Server, the query is executed as search specification of SQL Server.

We think you sometimes change the access by using the Oracle Database link to using Oracle Materialized View to improve query performance.
But Oracle Materialized View is simply a table so access with it will be the same as previous result of Oracle.
Therefore, the result is possible to differ from the previous result if you change it.
(Actually, I faced this problem in my work. It is the motivation I wrote these articles...)

Difference of the handling the spaces between Oracle and SQL Server

em8215 — Tue, 05 Apr 2022 13:04:18 +0000

I conducted an experiment about the handling the spaces between Oracle and SQL Server because noticed the difference when using Oracle for my work.
(Oracle's version is Oracle 19c and SQL Server's verstion is SQL Server 2019 as these had already been installed in my PC.)

Experiment

Oracle

I conducted an experiment under following table and data.

CREATE TABLE CompareTestTable
(
     Seq         NUMBER
    ,ColCHAR     CHAR(10)
    ,ColVARCHAR  VARCHAR2(10)
);

INSERT INTO CompareTestTable VALUES( 1, 'aaa',  'bbb'  );       -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' );       -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' );       -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' );     -- Spaces after and before a string

select * from CompareTestTable where ColCHAR = 'aaa';       -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = 'aaa ';      -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = ' aaa';      -- Hit SEQ=3,4 records
select * from CompareTestTable where ColCHAR = ' aaa ';     -- Hit SEQ=3,4 records

select * from CompareTestTable where ColVARCHAR = 'bbb';    -- Hit SEQ=1 record
select * from CompareTestTable where ColVARCHAR = 'bbb ';   -- Hit SEQ=2 record
select * from CompareTestTable where ColVARCHAR = ' bbb';   -- Hit SEQ=3 record
select * from CompareTestTable where ColVARCHAR = ' bbb ';  -- Hit SEQ=4 record

The space after string is seemed to be ignored when colomn type is Char.
However, in the event of Varchar2, the space after or before string isn't seemed to be ignored.
The detail is following
https://docs.oracle.com/cd/B13789_01/server.101/b10759/sql_elements002.htm#:~:text=Nonpadded%20Comparison%20Semantics,-Oracle%20compares%20two&text=If%20two%20values%20of%20different,the%20values%20are%20considered%20equal.

SQL Server

As the same case for Oracle, I conducted an experiment under following table and data on SQL Server.

CREATE TABLE CompareTestTable
(
     Seq         INT
    ,ColCHAR     CHAR(10)
    ,ColVARCHAR  VARCHAR(10)
)
INSERT INTO CompareTestTable VALUES( 1, 'aaa',  'bbb'  );       -- No space
INSERT INTO CompareTestTable VALUES( 2, 'aaa ', 'bbb ' );       -- A space after a string
INSERT INTO CompareTestTable VALUES( 3, ' aaa', ' bbb' );       -- A space before a string
INSERT INTO CompareTestTable VALUES( 4, ' aaa ', ' bbb ' );     -- Spaces after and before a string

select * from CompareTestTable where ColCHAR = 'aaa';       -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = 'aaa ';      -- Hit SEQ=1,2 records
select * from CompareTestTable where ColCHAR = ' aaa';      -- Hit SEQ=3,4 records
select * from CompareTestTable where ColCHAR = ' aaa ';     -- Hit SEQ=3,4 records

select * from CompareTestTable where ColVARCHAR = 'bbb';    -- Hit SEQ=1,2 records
select * from CompareTestTable where ColVARCHAR = 'bbb ';   -- Hit SEQ=1,2 records
select * from CompareTestTable where ColVARCHAR = ' bbb';   -- Hit SEQ=3,4 records
select * from CompareTestTable where ColVARCHAR = ' bbb ';  -- Hit SEQ=3,4 records

The result of Char is the same as Oracle.
However, in the event of Varchar, the result is different between Oracle and SQL Server, the spaces after or before string is seems to be ignored.

Therefore, it should be note when using varchar because of the difference between Oracle and SQL Server as such above.

DEV Community: em8215

The difference in the handling of the spaces between Oracle and SQL Server(In the event of linking different DBMS)

Experiment

Table and data query

SQL Server => Oracle (With using Link Server of SQL server, tables in oracle are accessed.)

Oracle => SQL Server (With using Database Link of Oracle, tables in SQL Server are accessed.)

Conclustion

Difference of the handling the spaces between Oracle and SQL Server

Experiment

Oracle

SQL Server