The difference in the handling of the spaces between Oracle and SQL Server(In the event of linking different DBMS)
Tags: sql, oracle, sqlserver
Published: 2022-04-11 12:38:47

This article is a continuation of [last time's article](https://dev.to/em8215/difference-of-the-handling-the-spaces-between-oracle-and-sql-server-4dp9).
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.
```sql:CreateTable(Oracle).sql
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
```
```sql:CreateTable(SQLServer).sql
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.
```sql
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](https://dev.to/em8215/difference-of-the-handling-the-spaces-between-oracle-and-sql-server-4dp9#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](https://dev.to/em8215/difference-of-the-handling-the-spaces-between-oracle-and-sql-server-4dp9#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".

```sql
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](https://dev.to/em8215/difference-of-the-handling-the-spaces-between-oracle-and-sql-server-4dp9#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.
```sql
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.
![Result of query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zdev2cg2lb89cc5ch76k.jpg)

## Conclustion
Including the previous experiment, the summary is following
 1. 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.
 2. With using Link Server to connect from SQL Server to tables in Oracle, the query is executed as search specification of SQL Server.
 3. 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](https://dev.to/em8215/difference-of-the-handling-the-spaces-between-oracle-and-sql-server-4dp9#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...)