[NXP-10862] SQLServer: fix varchar conversion to enable index usage - Nuxeo Issue Tracker

XML

Word

Printable

Tags:
- mssql
- performances
Impact type:

Data Persistence Change
Upgrade notes:

Hide

The attached script converts VARCHAR columns to NVARCHAR in the database. In addition, it also adds the _oid columns for ~~NXP-10934~~.

The latest version of this script is available at
https://github.com/nuxeo/nuxeo-core/blob/master/nuxeo-core-storage-sql/nuxeo-core-storage-sql/doc/sqlserver-upgrade-script.sql.txt

Show
The attached script converts VARCHAR columns to NVARCHAR in the database. In addition, it also adds the _oid columns for NXP-10934 . The latest version of this script is available at https://github.com/nuxeo/nuxeo-core/blob/master/nuxeo-core-storage-sql/nuxeo-core-storage-sql/doc/sqlserver-upgrade-script.sql.txt

The MS SQLServer jdbc driver by default send string as unicode (sendStringParametersAsUnicode=true).

The database can’t convert nvarchar() to varchar() and therefore don't use existing varchar index.

All our uid ar varchar(36) which make direct access by id slow.

is required by

NXP-10934 Use better clustered indexes on SQL Server