Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add WITH (ONLINE=ON) to MS SQL indexes creation #554

Open
MNF opened this issue Jun 18, 2022 · 1 comment
Open

Add WITH (ONLINE=ON) to MS SQL indexes creation #554

MNF opened this issue Jun 18, 2022 · 1 comment

Comments

@MNF
Copy link
Contributor

MNF commented Jun 18, 2022

If we upgrade V2 to V3, we have existing database with millions of record and creating index may be time-consuming.
To anticipate it, Indexes IX_Streams_IdOriginal and IX_Streams_IdOriginalReversed (and other indexes as well) better to specify WITH (ONLINE=ON)

CREATE NONCLUSTERED INDEX IX_Streams_IdOriginal

Unfortunately it is required to add conditional logic as not all sql server editions support WITH (ONLINE) https://stackoverflow.com/questions/50407490/how-to-create-nonclustered-index-with-online-if-available

Btw, even WITH (ONLINE=ON) may cause blocks on the final stage
https://dba.stackexchange.com/questions/41181/why-does-create-index-with-online-on-block-access-to-the-table-over-a-period/50139#50139

@michael-freidgeim-webjet

When I created the index, I got

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

Does it mean, that MS SQL in reality support not more than 848 characters, not 1000 as declared in schema?
(it's not a problem for my IdOriginal values)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants