maximum row size exceeds the maximum number of bytes per row (8060

Hi,
I have created a table (say Table1) with few columns, with one of the
columns (say column1) having the data type as Varchar(8000).
Now I run the below query -
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
if not exists (select * from dbo.syscolumns
where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = 'Column1')
ALTER TABLE dbo.Table1 ADD
Column1 varchar(500) NULL
GO
COMMIT
Now that the column Column1 already exists, the add column statement won't
be executed. But still I get the warning -
"Warning: The table 'Table1' has been created but its maximum row size
(8579) 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."
My question is, now that the column already exists, the statement itself
won't be executed. Then why do I still get this warning?That has to do with the sequence of query processing and when the warning is generated. Obviously
the warning (the fact that this table will have a row size for which you can exceed the limit in
your data) is generated in a stage which is earlier than when the statements are actually executed.
In other words, the If statement haven't been executed at this stage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"swap@.@.@." <swap@.discussions.microsoft.com> wrote in message
news:73AC8FFC-4041-4B22-AB25-4CCDB94598FC@.microsoft.com...
> Hi,
> I have created a table (say Table1) with few columns, with one of the
> columns (say column1) having the data type as Varchar(8000).
> Now I run the below query -
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> if not exists (select * from dbo.syscolumns
> where id = object_id(N'[dbo].[Table1]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and name = 'Column1')
> ALTER TABLE dbo.Table1 ADD
> Column1 varchar(500) NULL
> GO
> COMMIT
> Now that the column Column1 already exists, the add column statement won't
> be executed. But still I get the warning -
> "Warning: The table 'Table1' has been created but its maximum row size
> (8579) 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."
> My question is, now that the column already exists, the statement itself
> won't be executed. Then why do I still get this warning?
>