|
| Featured Contents |
| Question of the Day |
|
| The Voice of the DBA |
Two Important ‘ONLINE’ Column-Level Operations in SQL Server Today we have a guest editorial from Kendra Little as Steve is away on his sabbatical. Recently, I wrote a blog post summing up the different ONLINE operations in SQL Server– we’ve moved a long way past simple online index creation and rebuilds. I wrote the post because I found there was a major ONLINE feature which I didn’t learn about when it became available in SQL Server. I suspect I was distracted by other shiny features in SQL Server 2016 when this first appeared, and it simply didn’t register. The feature is: ALTER TABLE ALTER COLUMN ONLINE. The ‘ALTER COLUMN ONLINE’ feature is important because it reduces blocking while making schema changes such as changing data types, precision, nullability, and other properties. The ability to ADD COLUMNs online is separate. It works in some cases for non-nullable columns with default values in SQL Server 2012 and higher. The ADD COLUMN feature is unusual because it doesn’t require (or allow) the use of the ONLINE keyword – it simply kicks in automatically whenever it is available. It’s now normal for code deployments to occur while databases are online and serving requests – initial data from the 2020 state of DevOps report shows that the majority of respondents report that most or all of their deployments take place while systems are available and serving user requests. This means that ONLINE operations remain a compelling selling point for SQL Server’s Enterprise Edition, which provides these features. Knowing about these features is useful for a few reasons: When making licensing decisions, understanding ONLINE operations is needed to help understand if and how Enterprise Edition will help, and if it’s worth the cost You need to use the ONLINE keyword to take advantage the feature for ALTER COLUMN – are you already paying for Enterprise Edition but forgetting to take advantage of all the ONLINE features? Very few of them kick in without using special syntax. Because the ADD COLUMN online feature does not require the ONLINE keyword and only works in Enterprise Edition, be cautious when using Developer Edition to estimate the performance impacts of adding a column: Developer Edition will do this online, but if you deploy the change to a Standard Edition instance it will be done offline! (Please consider voting up this User Voice suggestion to change this behavior.) On the one hand, it’s a shame that it’s tough to keep up with critical features for SQL Server. But then again, that’s not necessarily a bad problem to have. Kendra Little Join the debate, and respond to today's editorial on the forums |
|
| Featured Contents |
