Query Regarding Indexing and Field Length Adjustment for LocalizationResources Table

[andreeatatar23]

Hello,
In our project we use this Localization Resorce package, and recently we tried to add an index to the LocalizationResource table, for performance improvement, but we received a warning related to this:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IDX_LocalizationResources' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

This is the script we tried to run:

CREATE NONCLUSTERED INDEX [IDX_LocalizationResources]
ON [dbo].[LocalizationResources] ([ResourceKey])

After some investigations, we saw that ResourceKey has Data Type = nvarchar(1000), and we believe this is why we received that warning.

We would appreciate your expert opinion regarding the issue at hand. Specifically, we are wondering whether the field lengths can be adjusted. Given your familiarity with this package, we would value your advice on how best to proceed with this matter. Could you kindly provide your insights and recommendations?

Thank you!

[valdisiljuconoks]

do you have resources with keys longer that that?

[andreeatatar23]

Hello, no, as far as I saw the max length is 75, after applying this:

SELECT MAX(LEN([ResourceKey])) AS MaxLength
FROM [LocalizationResources]

[valdisiljuconoks]

hey, sorry for delay. by default there already should be unique non-clustered index on resource key column. can you verify that?

[andreeatatar23]

Hello, sorry for the delayed repose, most of my team was OOO in December. I checked the LocalozationResources table and this is all that I could see, a clustered index:

[valdisiljuconoks]

interesting, sounds like schema updater was not successfully executed.

check this low-level code: https://github.com/valdisiljuconoks/LocalizationProvider/blob/master/common/src/DbLocalizationProvider.Storage.SqlServer/SchemaUpdater.cs#L141

it does look for index existence and if it's missing in the database, schema updater should be creating one..

[valdisiljuconoks]

one option would be to run profiler (if even possible) against db before app starts and see what statements are executed while app is starting.