SQL Server 2016 introduces an interesting T-SQL enhancement to improve performance and reduce downtime ALTER TABLE WITH (ONLINE = ON | OFF). This statement will help you alter data types, change column/table collation values, change nullability settings, change column length, and many other options while the table remains online for user connectivity.
This is Part 4 of 10 Part T-SQL Enhancements in SQL Server 2016 for Developers and DBAs. Click here to read it from the beginning….
However, this feature is turned off by default in SQL Server 2016. In case, if you wish to utilise this feature then you would need to specify ALTER TABLE WITH (ONLINE = ON) option.
Trending SQL Server Articles and Tips
- How to Change Select Top 1000 Rows and Edit Top 200 Rows Default Value in SQL Server Management Studio
- How to Recycle SQL Server Error Log file without restarting SQL Server Service
- Different SQL Server Recovery Models Step by Step Tutorial with Examples
- 2 Signs you need to migrate to SharePoint 2016 and how to nail the upgrade
- How to Configure a Contained Database Feature in SQL Server
Example ALTER TABLE WITH (ONLINE = ON | OFF)
DROP TABLE IF EXISTS ProductPrice GO CREATE TABLE ProductPrice (Price INT) GO INSERT INTO ProductPrice (Price) VALUES (1250); INSERT INTO ProductPrice (Price) VALUES (1252); INSERT INTO ProductPrice (Price) VALUES (1254); INSERT INTO ProductPrice (Price) VALUES (1256); INSERT INTO ProductPrice (Price) VALUES (1258); ALTER TABLE ProductPrice ALTER COLUMN Price DECIMAL (5, 2) WITH (ONLINE = ON) GO SELECT Price FROM ProductPrice GO
Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.
- MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
- ALTER DATABASE SET AUTOGROW_SINGLE_FILE
- ALTER DATABASE SET AUTOGROW_ALL_FILES
- COMPRESS and DECOMPRESS Functions
- STRING_SPLIT and STRING_ESCAPE Functions
- FORMATMESSAGE Statement
- SERVERPROPERTY Function
- TRUNCATE TABLE WITH PARTITIONS
- DROP IF EXISTS
- ALTER TABLE WITH (ONLINE = ON | OFF)