SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change

Question

I read through Microsoft's "Editions and supported features of SQL Server 2017" doc to compare the feature differences between Enterprise and Standard edition.

Two things not available in Standard that caught my eye were:

1. Online indexing

2. Online schema change

Does this literally mean you can't create and modify indexes or tables (and other objects?) without taking the database offline first, or does Microsoft mean there are only specific operations you can't do and if so where can I learn more about which operations require taking the database offline?

Answer 1

The confusion seems to be with the verbiage - online/offline is sort of confusing in this context.

It refers to whether the operations being performed (a reindex or a table alter) are performed online or not, and not whether the state of the object is online or offline. Online means that the object does not have an exclusive lock on it, and is possibly accessible to other queries/operations as a result. Offline means it is locked by the operation and can't be touched by other queries no matter what.

The benefit for Enterprise is that I can do my schema changes or index maintenance and not (potentially) adversely impact other queries running. That doesn't mean that other, less restrictive, locks won't be taken (row level, page level), but it does mean that I'm not totally boxed out of the object. It does not guarantee things will keep running the same, though, as many other factors play into this. Check out the individual commands you're interested in running to see the myriad options that may effect locking behaviors.

For example, I've seen an online index reorg end up effectively locking the table from all other SELECT statements until I tuned the MAXDOP it was using, even though it wasn't actually taking a lock on the whole table.

An object can't be taken online or offline, although a database can (not relevant to this topic though).

Resources:

• How Online Indexing Works
• Online ALTER COLUMN Operation

Answer 2

No, you don't need to take your database offline, or even take some object offline (how?) manually before altering a table or creating an index.

On the Standard version, SQL Server will lock your object (i.e. table) until the create index or alter table is complete.

If you have the Enterprise Edition, then you could specify the WITH (ONLINE=ON) option to alter tables or create indexes, so you could still access (SELECT) your object even while the create index operation is running. This is really useful if you have large objects or if you don't have a maintenance window.