After doing the calculation I was tempted to answer the queston but to be sure I ran the script and got the error:

The statement has been terminated.

Msg 1101, Level 17, State 12, Line 2

Could not allocate a new page for database 'QOTD' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Now the script I ran is

Creating the database:

CREATE DATABASE [QOTD] ON PRIMARY

(

NAME = N'QOTD'

, FILENAME = N'...\QOTD.mdf'

, SIZE = 2097152KB

, MAXSIZE = UNLIMITED

, FILEGROWTH = 0

)

LOG ON

(

NAME = N'QOTD_log'

, FILENAME = N'...\QOTD_log.ldf'

, SIZE = 1024KB

, MAXSIZE = 2048GB

, FILEGROWTH = 10%

)

GO

ALTER DATABASE [QOTD] SET COMPATIBILITY_LEVEL = 100

GO

Then creating the table:

USE [QOTD]

GO

CREATE TABLE dbo.DemoTable

(DemoTableKey char(10) NOT NULL,

LargeNumber bigint NOT NULL,

ShortDescription char(80) NOT NULL,

LongDescription nchar(600) NULL,

Price money NOT NULL,

Counter1 int NOT NULL,

Counter2 bigint NOT NULL,

Counter3 smallint NOT NULL,

Counter4 int NOT NULL,

AddDate datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP),

ChgDate datetime NULL,

CONSTRAINT PK_DemoTable PRIMARY KEY (DemoTableKey),

CONSTRAINT CK_LargeNumber CHECK (LargeNumber > 0)

);

GO

Now filling the 1 mil records:

; WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 lines

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 lines

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 lines

E5(N) AS (SELECT 1 FROM E4 a, E2 b) --10E+6 or 1,000,000

INSERT INTO [dbo].[DemoTable]

([DemoTableKey]

,[LargeNumber]

,[ShortDescription]

,[LongDescription]

,[Price]

,[Counter1]

,[Counter2]

,[Counter3]

,[Counter4]

-- ,[AddDate]

,[ChgDate])

SELECTROW_NUMBER() OVER (ORDER BY N)

, 1

, 'NONE'

, NULL

, 1

, 1

, 1

, 1

, 1

-- , -- DEFAULT

, NULL

FROM E5

GO

Everyting is OK so far, all ran successfuly.

Finaly rebuilding the index:

ALTER INDEX ALL ON dbo.DemoTable

REBUILD WITH (FILLFACTOR = 100,

DATA_COMPRESSION = NONE,

MAXDOP = 1);

-- TRUNCATE TABLE dbo.DemoTable

I am not worried about the lost point, but is there anything I have missed?

One mention I did not get the error after the INSERT statement but after running the ALTER INDEX statement.

The question is great, a strong question, I would like to see more like this.

Thank you,

Iulian