January 29, 2010 at 9:27 am
We currently have two high traffic tables that utilize CHAR columns to store data. The size of the tables aren't that great since there are only a few columns and the length of the columns are fairly short. These tables also have a composite key.
Due to an internal change these column values may become larger in the future and to prepare, I'm adjusting the length of the columns to allow the max length of the spec. Since the values will often vary from a length of 3 to a length of 12 with a lax length of 20, I was thinking converting these to VARCHAR will be the way to go.
What I'm looking for is to see if what I suspect is correct and for performance, what's the best way to go. In my example below, the three columns changing are Data1, Data2 and Data3. These will now have a maximum length of 20 characters.
What is a good way to tune this to expect this future length? Do I need to rebuild the indices?
Any help is apreciated.
TableA - Contains a list of current items actually located here and their actual location. Around 10,000 combined updates/inserts/deletes per day. This will only hold around 500 records at a given time.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TableA](
[EntranceDateTime] [datetime] NOT NULL,
[Data1] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Data2] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Data3] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Data4] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
[EntranceDateTime] ASC,
[Data1] ASC,
[Data2] ASC,
[Data3] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
TableA_History - Same as TableA, except it holds a history of all items located here and when it left a specific location. Everytime an item changes locations, it is recorded here for future reporting. This holds around a million records and a job will clear out older data to keep is aroundd that size.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TableA_History](
[EntranceDateTime] [datetime] NOT NULL,
[Data1] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Data2] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExitDateTime] [datetime] NULL,
[Data3] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Data4] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
[EntranceDateTime] ASC,
[Data1] ASC,
[Data2] ASC,
[Data3] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
January 29, 2010 at 10:21 am
You don't necessarily need to tune the design. I would move to varchar as it can save some space, which might end up making your rows denser on the tables.
If there is potentially some changing of key size often, you might want to adjust the fill factor to minimize page splits. Leave more space and rebuild indexes regularly to ensure that you have the space to handle inserts/updates.
February 1, 2010 at 8:11 am
Thank you for your time with this.
February 2, 2010 at 12:05 am
with the limited knowledge of your system, data and data access patters I have I would leave the active table with char and make the history table variable.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply