MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter


















Trending SQL Server Tips






SQL Server 2016: ALTER TABLE WITH (ONLINE = ON | OFF) T-SQL Enhancement

Read Comments   |   Related Topic: TSQL Enhancements in SQL Server 2016 > Next Topic: MAXDOP for DBCC CHECKDB


Click here to read about new T-SQL enhancements in SQL Server 2016 from the beginning...

ALTER TABLE WITH (ONLINE = ON | OFF) T-SQL Enhancement in SQL Server 2016

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.

However, this feature is turned off by default in SQL Server 2016. In case, if you wish to utilize this feature then you would need to specify ALTER TABLE WITH (ONLINE = ON) option.

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				

Related Tips

MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016
COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016
STRING_SPLIT and STRING_ESCAPE T-SQL Enhancement in SQL Server 2016
SERVERPROPERTY T-SQL Enhancement in SQL Server 2016

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server

Click the Next Page button to continue reading about New T-SQL enhancements in SQL Server 2016 and click on the Previous Page button to revise the previouly read topic.

Previous Page.. Next Page..




Learn More...





Last Updated On: May 11, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: