When we add a column to a table, what happens to existing rows?
Will the new column be null? Or will have a value?

This depends on how we add the column.
We have four different options to add a new column to an existing table:

• Adding a nullable column with no default value – in this case the column will be added to the table, and will be null in all existing rows.
• Adding a “no null” column without default value – this option will return an error. Existing rows will have null value in the new column and this is not legal. In order to add this type of column is to add a nullable column, update all the rows to have values for this column, and then change it to “not null”.
• Adding a “nullable” column with default value – in this case, existing rows will have null for this column, but new rows will get the default value.
• Adding a “no null” column with default value-after adding this type of column, existing rows will get the default value for this column and it will be not null.

Adding a column as described in one of the first 3 options will complete quickly, as only the definition of the table changes.
We have to be careful when using the last option will. Adding a column as described in the last option will implicitly cause an update of all the existing rows in the table and may take a long time to complete.