Error running CREATE table script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error running CREATE table script

Author

Topic

dignam
Starting Member

4 Posts

Posted - 2004-06-28 : 12:06:39

greetings:

I am trying to learn how to generate scripts for my tables so that when I need to add a new table to our application I can run the script instead of manually creating or importing the new objects.

When I generate a script for my table and then run it in a new database I receive the following warning repeated three times:

Warning: The table 'submission' has been created but its maximum row size (12229) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

I am enclosing the script below. I do not understand how this number (12229) is being calculated. Do I have problems with my initial table design?

thanks a lot,
Jeffrey

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[submission]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[submission]
GO

CREATE TABLE [dbo].[submission] (
[submission_id] [int] NOT NULL ,
[journal_id] [smallint] NOT NULL ,
[manus_id] [int] NULL ,
[title] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[running_hd] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[author_lst] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[revis_id] [tinyint] NULL ,
[people_id] [int] NULL ,
[notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[keywords] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[abstract] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[submission_status_id] [tinyint] NULL ,
[ed_asstnt] [smallint] NULL ,
[super_ed] [int] NULL ,
[editor] [int] NULL ,
[type_id] [tinyint] NULL ,
[section_id] [smallint] NULL ,
[nbr_files] [int] NULL ,
[ed_as_auth] [bit] NULL ,
[submission_complete_dte] [datetime] NULL ,
[chg_date_time] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[submission] WITH NOCHECK ADD
CONSTRAINT [PK_submission] PRIMARY KEY CLUSTERED
(
[submission_id],
[journal_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[submission] ADD
CONSTRAINT [DF_submission_editor] DEFAULT (0) FOR [editor],
CONSTRAINT [DF_submission_nbr_files] DEFAULT (0) FOR [nbr_files],
CONSTRAINT [DF_submission_ed_as_auth] DEFAULT (0) FOR [ed_as_auth]
GO

chadmat
The Chadinator

1974 Posts

Posted - 2004-06-28 : 12:31:28

NVarchars are Double Byte, so you have to Double the size. Nvarchar(4000) takes up 8000 Bytes.

So you can see that you have several NVarchar fields that will bring your total over 8k.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

dignam
Starting Member

4 Posts

Posted - 2004-06-28 : 13:08:16

Thanks, Chad.

So am I better off using ntext for fields this sive (nvarchar 4000)? What are the design and performance concernes when deciding between these datatypes?

thanks again for your great help,
Jeffrey

chadmat
The Chadinator

1974 Posts

Posted - 2004-06-28 : 13:35:43

It depends on alot of factors. If you often use up the entire 4000 characters then you probably want to consider ntext.

In SQL 2000 using Text and NText is not that big a deal, in earler versions there were some more things you had to take into consideration.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources