Today's question (by Steve Jones - SSC Editor):

Hyperscale Replicas III

Think you know the answer? Click here, and find out if you are right.

Adding new column with DEFAULT II

Which number did the two COUNT(*) return:

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp (id INT NOT NULL)

INSERT INTO #tmp (id) 
SELECT gs.value
  FROM GENERATE_SERIES(1, 5) AS gs

ALTER TABLE #tmp ADD my_value INT NOT NULL CONSTRAINT df_tmp_my_value DEFAULT 1

SELECT COUNT(*)  FROM #tmp AS t WHERE my_value = 1

ALTER TABLE #tmp DROP CONSTRAINT df_tmp_my_value

ALTER TABLE #tmp ADD CONSTRAINT df_tmp_my_value DEFAULT 2 FOR my_value

SELECT COUNT(*)  FROM #tmp AS t WHERE my_value = 1

Answer: both 5

Explanation: When you are adding a new column that does not allow NULL values you have to specify a DEFAULT constraint - otherwise you receive the error 4901 "ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'my_value' cannot be added to non-empty table '#tmp' because it does not satisfy these conditions." The value of the specified DEFAULT will be saved in the columns meta data (sys.system_internals_partition_columns), so no rewrite of the possible very large table is necessary. It will not be changed, when you are dropping the CONSTRAINT later or even creating another one. You can use the following query to verify check the saved initial value for the column:

SELECT
    c.name,
    i.has_default,
    i.default_value
FROM tempdb.sys.partitions p
JOIN tempdb.sys.system_internals_partition_columns i
    ON p.partition_id = i.partition_id
JOIN tempdb.sys.columns c
    ON c.object_id = p.object_id
   AND c.column_id = i.partition_column_id
WHERE p.object_id = OBJECT_ID('tempdb..#tmp')
  AND p.index_id IN (0,1);

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.