July 18, 2009 at 2:14 am
I have the following table in my DB, to which I want to add another 10 columns; aLL the new columns
are the following data type: [char] (5). I am just concerned whether this will affect the performance as
the table already have 23 columns in it. Current DB size is around 600 MB and this table grows by 15000 records each year.
Is it better to create another table or I can simply add the new columns in the existing table; how big a
table can be? I know there is some restriction in sql2000 (8000 bytes), but that is no the case in sql2005..
Further more, please advice me on the timestamp column in the table, this table is designed by my Predecessor
and I am just wondering whether this is really needed, as we also have an unique Id in this table.
This DB was migrated from SQL 2000 to SQL2005, due to this col 8 and col 9 are of ntext data types; In SQL 2005
ntext is replaced by varchar(max) data type and I heard this is much better when performance is concerned, please
advice me on the cons and pros if I convert ntext to varchar(max), and how can I safely do this conversion.
Any advice will be greatly appreciated.
[dbo].[tbl_report1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[dat_repDate] [datetime] NULL ,
[personID] [nvarchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[str_col_1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_2] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_3] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_4] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_5] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_6] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_7] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[txt_col_8] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[str_col_9] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[str_col_10] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[str_col_12] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[str_col_13] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_14] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[int_col_15] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[str_col_16] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[str_col_17] [nvarchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[str_col_18] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[str_col_19] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_20] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_21] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_22] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[str_col_23] [char] (3) COLLATE Latin1_General_CI_AS NULL ,
[upsize_ts] [timestamp] NULL <-----------
)
July 18, 2009 at 9:56 am
Please don't cross post.
Closed thread. Answer here: http://www.sqlservercentral.com/Forums/Topic755298-149-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply