---
title: "ALTER TABLE with a default: SQL Server vs Oracle"  
description: "ALTER TABLE with a default: SQL Server vs Oracle"  
author: "Anonymous User"  
published: 2013-05-04  
updated: 2013-05-04  
canonical: https://www.mindstick.com/forum/811/alter-table-with-a-default-sql-server-vs-oracle  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# ALTER TABLE with a default: SQL Server vs Oracle

Hi Mindstickians!\
We have some tables in an [Oracle database](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) with several [million rows](https://www.mindstick.com/forum/157166/what-is-the-best-and-fast-way-to-insert-2-million-rows-of-data-into-sql-server). When we alter one of these tables to add a new column, we specify a default. This is very slow to \
run as Oracle has to update all existing rows with the default. The solution is to ensure the column is defined as [NOT NULL](https://www.mindstick.com/interview/1933/what-is-not-null-constraint) because then Oracle (recent versions only) \
will not update all existing rows with the default - the subsequent presence of a null in one of these columns tells Oracle that it requires a default and it will \
provide the default on the fly.\
My question is regarding [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server): does it exhibit similar behaviour when adding a column and providing a default? If not, are there any [best practices](https://www.mindstick.com/articles/337564/building-a-microservices-architecture-with-laravel-best-practices) in \
efficiently adding new columns with [default values](https://www.mindstick.com/blog/10881/default-values), and are there any [advantages](https://www.mindstick.com/articles/12841/5-advantages-of-customer-portal-you-didn-t-know-about) in defining a [column as](https://www.mindstick.com/forum/159334/how-to-set-column-as-auto_increment-in-mysql) NOT NULL?\
Thanks in advance!

## Replies

### Reply by AVADHESH PATEL

Hi Goti!\
Prior to SQL Server 2012 adding a NULLable column w/o default was very vast, but adding a DEFAULT contraint would be slow, as every row has to be updated. Since SQL Server 2012 http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/ when possible.\


---

Original Source: https://www.mindstick.com/forum/811/alter-table-with-a-default-sql-server-vs-oracle

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.