Forum Discussion

yachaoh001's avatar
yachaoh001
Copper Contributor
May 04, 2023

The primary key field of the SQLSERVER2016 table is UUID. Variable parameter passing causes a full t

For example, table1 column id is the primary key, varchar(22), ID is prefixed by N by default during parameter passing, resulting in full table scanning;

select  *from table1 where id = '346xPbiLLviw4mxAzoMQT9'
select * from table1 where id = N'346xPbiLLviw4mxAzoMQT9'

 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    May 05, 2023

    yachaoh001 , the literal N indicate Unicode = NVarChar and your column has the different data type varchar = ASCII. Mixing different data types in a query can cause full table/index scans.

    • yachaoh001's avatar
      yachaoh001
      Copper Contributor
      May 05, 2023

      olafhelper 

      If the primary key field is set to varchar type, how can I avoid index scanning caused by type conversion during value transfer

  • Raksha112's avatar
    Raksha112
    Copper Contributor
    May 16, 2023
    You need to check as the N prefix is used for Unicode character data. As UUID is not Unicode, so you can't use the N prefix while passing the UUID parameter.

    As you want to do minimal index scanning, you must avoid using the prefix N as a parameter will be a proper UUID and will avoid performance issues by unnecessary conversions.

Resources