When does the space get allocated

Question

If I declare a column as nvarchar(max), I understand that this will allocate 2Gb of space, but does it actually take the 2Gb, of disk space, straight away once I save the changes to the table? Or, is it that it makes note that this column will allow 2Gb of data to be populated in the column?

Answer 1

As I understand it, space isn't allocated until it is needed.

Try the following queries:

CREATE TABLE SizeTest (

    MyID int primary key
)

INSERT INTO SizeTest SELECT 1
UNION SELECT 2 
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5

EXEC sp_spaceused 'SizeTest'

ALTER TABLE SizeTest ADD MyBigText nvarchar(max)

EXEC sp_spaceused 'SizeTest'

UPDATE SizeTest SET MyBigText = 'This is big text' WHERE MyID = 1

EXEC sp_spaceused 'SizeTest'

DROP TABLE SizeTest

By executing this statement, you should get the following for all three sp_spaceused calls:

name        rows      reserved  data  index_size unused
SizeTest    5           16 KB   8 KB        8 KB    0 KB

At no point is the 2GB allocated.