CREATE TABLE XXXX_XX(
[DM_CV_AGGR_FACT_POLICY_COVERAGE_ID] [int] IDENTITY(1,1) NOT NULL,
[POL_TYPE_ID] [int] NULL,
[GENDER] [varchar](1) NULL,
[DRIVER_AGE_RANGE_ID] [int] NULL,
[CARRIER_ID] [varchar](11) NULL,
[C_ST_ALPH] [nvarchar](10) NULL,
[LIMIT_ID] [int] NULL,
[BIZ_MONTHYEAR] [int] NOT NULL,
[BIZ_YEAR] [varchar](4) NULL,
[BIZ_MONTH] [varchar](2) NULL,
[TOT_POLICY_COUNT] [int] NULL,
[TOT_VEHICLE_COUNT] [int] NULL,
[TOT_DRIVER_COUNT] [int] NULL,
[START_DT] [int] NULL,
[END_DT] [int] NULL,
[LOAD_DT] [datetime] NOT NULL,
CONSTRAINT [PK_DM_CV_AGGR_Fact_Policy_Coverage] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SCH_DM_CV_AGGR_Fact_Policy_Coverage]([BIZ_MONTHYEAR])
) ON [SCH_DM_CV_AGGR_Fact_Policy_Coverage]([BIZ_MONTHYEAR])
Wow - hard to know where to start with all the issues I see here.
1) I will assume you have biz_monthyear as leading column for some particular reason (report query performance probably). With it first you will get splits as you insert new data and really bad splitting if you load them out of order.
2) Your PK allows you to insert the EXACT SAME RECORD multiple times. Hopefully you guarantee that cannot happen via some other control.
3) Do you need 4.2Billion values for POL_TYPE_ID? Doubtful. If not you have wasted space over small or tiny int.
4) Gender being varchar(1) was already covered as bad practice.
5) DRIVER_AGE_RANGE_ID DEFINITELY doesn't need 4.2B values. tinyint gives 255 values for 1 byte vs 4 for int.
6) BIZ_MONTHYEAR doesn't need 4.2B values. A smallint gives you sufficient years for 2 bytes.
7) BIZ_MONTH as varchar(2) is like Gender.
8) I wonder how many of your (n)varchars are actually stuffed with valid values sufficient to make their average length at or very close to the variable length allowed. If they are then variable length fields are quite inefficient from storage and processing perspective.
9) Why do you need 2 YEAR fields?
10) START_DT/END_DT again have the int/smallint problem.
Bytes REALLY do add up and are REALLY important!! You could easily have 20 bytes PER ROW in this table that are useless.
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service