Sample Dimension Table

Dimensions that are not junk dimensions and implemented as physical tables can be implemented by this script.

Note that the primary key is of data type BIGINT. The INT data type maxes out at 2,147,483,647. It turns out, in extensive load testing, you can blow clean through that number. There are remedies for that. You can reset the value using DBCC CHECKIDENT, but why take chances?

Nuke it from orbit. It's the only way to be sure. And by that I mean use BIGINT for contrived keys. It taps out at 9,223,372,036,854,775,807 at the cost of four more bytes of hard drive space.

DROP TABLE IF EXISTS [dbo].[DimYourDimensionName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DimYourDimensionName](
	[YourDimensionNameCK] [bigint] IDENTITY(1,1) NOT NULL,
	[CreatedBy] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[UpdatedBy] [nvarchar](50) NULL,
	[UpdatedOn] [datetime] NULL,
	[SourceSystem] [nvarchar](100) NULL,
	[SourceSystemKey] [nvarchar](100) NULL,
	[EffectiveFrom] [datetime] NULL,
	[EffectiveTo] [datetime] NULL,
	[IsMostRecentRecord] [bit] NULL
 CONSTRAINT [PK_YourDimensionName] PRIMARY KEY CLUSTERED 
(
	[YourDimensionNameCK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Last updated