Strings Used In Query Always Sent With Nvarchar Syntax, Even If The Underlying Column Is Not Unicode

I'm noticing some odd behavior in the SQL generated for queries against string fields in MS SQL. Server version: SQL Server 2014 12.0.5000.0 Collation: SQL_Latin1_General_CP1_CI_AS

Solution 1:

I was able to reproduce the issue. Your MCVE was very helpful.

It was interesting to see that, for your ORM example, SQL Profiler showed no evidence that SQLAlchemy was retrieving the column metadata before running the SELECT query against the table. Apparently it believes that it knows enough about the columns to construct a working query, even though (as it turns out) it is not necessarily the most efficient one.

I knew that SQLAlchemy's SQL Expression Language would retrieve the table metadata, so I tried a similar SELECT using

metadata = MetaData()
my_table = Table('test', metadata, autoload=True, autoload_with=engine)
stmt = select([my_table.c.id, my_table.c.key])\
    .select_from(my_table)\
    .where(my_table.c.key == value)
cnxn = engine.connect()
items = cnxn.execute(stmt).fetchall()

and although SQLAlchemy did indeed retrieve the metadata using

SELECT [INFORMATION_SCHEMA].[columns].[table_schema],
       [INFORMATION_SCHEMA].[columns].[table_name],
       [INFORMATION_SCHEMA].[columns].[column_name],
       [INFORMATION_SCHEMA].[columns].[is_nullable],
       [INFORMATION_SCHEMA].[columns].[data_type],
       [INFORMATION_SCHEMA].[columns].[ordinal_position],
       [INFORMATION_SCHEMA].[columns].[character_maximum_length],
       [INFORMATION_SCHEMA].[columns].[numeric_precision],
       [INFORMATION_SCHEMA].[columns].[numeric_scale],
       [INFORMATION_SCHEMA].[columns].[column_default],
       [INFORMATION_SCHEMA].[columns].[collation_name]
FROM   [INFORMATION_SCHEMA].[columns]
WHERE  [INFORMATION_SCHEMA].[columns].[table_name] =Cast(
       N'test'AS NVARCHAR(max))
       AND [INFORMATION_SCHEMA].[columns].[table_schema] =Cast(
           N'dbo'AS NVARCHAR(max))
ORDERBY [INFORMATION_SCHEMA].[columns].[ordinal_position]

a portion of whose output is

TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  IS_NULLABLE  DATA_TYPE  ORDINAL_POSITION  CHARACTER_MAXIMUM_LENGTH
------------  ----------  -----------  -----------  ---------  ----------------  ------------------------
dbo           test        id           NO           int        1                 NULL
dbo           test        key          NO           varchar    2                 50

the resulting SELECT query still used an nvarchar literal

SELECT test.id, test.[key] 
FROM test 
WHERE test.[key] = N'record123456'

Finally, I did the same tests using pyodbc instead of pymssql and the results were essentially the same. I was curious if SQLAlchemy's dialect for pyodbc might take advantage of setinputsizes to specify the parameter types (i.e., pyodbc.SQL_VARCHAR instead of pyodbc.SQL_WVARCHAR), but apparently it does not.

So, I'd say that for the time being your best bet is to continue encoding your string values into bytes that correspond to the character set of the varchar column you are querying (not utf-8). Of course, you can also dive into source code for the SQLAlchemy dialect(s) and submit a PR to make SQLAlchemy better.