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


 CREATE TABLE dbo.BigTab (c VARCHAR(50) INDEX ix);
 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?


· 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