Estimating Table Space...greenspun.com : LUSENET : SQL Server Database Administration : One Thread
Size of the table to be estimated:
/****** Object: Table [dbo].[ADDRESS] Script Date: 2/14/00 11:44:55 AM ******/ CREATE TABLE [dbo].[ADDRESS] ( [RespondentID] [decimal](19, 0) NOT NULL , [Address1] [varchar] (100) NULL , [Address2] [varchar] (100) NULL , [AptNbr] [varchar] (20) NULL , [City] [char] (20) NULL , [State] [char] (2) NULL , [ZipCode] [char] (10) NULL , [CountryTypeCode] [int] NOT NULL , [ModifiedBy] [varchar] (30) NOT NULL , [ModifyDate] [datetime] NOT NULL ) GO
ALTER TABLE [dbo].[ADDRESS] WITH NOCHECK ADD CONSTRAINT [PK_ADDRESS] PRIMARY KEY CLUSTERED ( [RespondentID] ) WITH FILLFACTOR = 90 ON [PRIMARY] GO
CREATE INDEX [idx_state_address] ON [dbo].[ADDRESS]([State]) ON [PRIMARY] GO
CREATE INDEX [idx_zip_address] ON [dbo].[ADDRESS]([ZipCode]) ON [PRIMARY] GO
CREATE INDEX [idx_mods_address] ON [dbo].[ADDRESS]([ModifiedBy], [ModifyDate]) ON [PRIMARY] GO
I segregated fixed and variable column like this:
Fixed col: Respondent ID - length = 9 CountryTypeCode - length = 4 ModifyDate - length = 8
Variable col: Address1 - length = 100 Address2 - length = 100 AptNbr - length = 20 City - length = 20 State - length = 2 ZipCode - length = 10 ModifiedBy - length = 30
I am following books online for estimating the table size. I am not too sure segregating the column shown above is correct as I am not getting the correct size. Can you correct me if I am wrong?
Thanks in advance,
-- Anonymous, February 14, 2000
The way I read the documentation, the nullable columns are still considered fixed. Also, with variable columns, your estimate will vary with how much of the variable columns you actually use. And finally, of course, you must not expect too much of an estimate.
Hope this helps,
-- Anonymous, February 23, 2000