question

CarlKrig-8898 avatar image
0 Votes"
CarlKrig-8898 asked AnuragSharma-MSFT edited

Querying external table varchar column with literal runs N'..' forcing full index scan

Hi,

 --db1
 CREATE TABLE dbo.BigTab (c VARCHAR(50) INDEX ix);
    
 --db2
 CREATE EXTERNAL TABLE dbo.ExtTab (c VARCHAR(50)) WITH (data_source = db1ds, schema_name='dbo', object_name='BigTab');
 SELECT * FROM dbo.ExtTable WHERE c = 'helu'; -- notice the plain literal
 --runs db1-side as
 SELECT * FROM dbo.ExtTable WHERE c = N'helu'; -- notice the unicode literal
 -- forcing full table/index scan (instead of index seek)

Needless to say performance plummets.

This is basically the same as below Entity Framework bug from 2009.

Anybody found a workaround with Azure SQL external tables?

https://social.msdn.microsoft.com/Forums/en-US/d8577454-ebca-4697-80ef-73b7620e87a4/querying-varchar-columns-with-string-constant-expressions-results-in-unwanted-unicode-literals-in?forum=adodotnetentityframework

Best,
--cb

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Tried and failed, attempting to lure backing technology with metadata hints, but no difference still N'..'

 -- explicitly specifying collation on external table
 CREATE EXTERNAL TABLE dbo.ExtTab (c VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS) WITH (data_source = db1ds, schema_name='dbo', object_name='BigTab');
    
 -- explicitly specifying collation on query literals
  SELECT * FROM dbo.ExtTable WHERE c = 'helu' COLLATE SQL_Latin1_General_CP1_CI_AS;


0 Votes 0 ·

0 Answers