June 4, 2009 at 7:56 am
Hi,
I have a table that is in the process of being reviewed (its too large). However I have removed a primary key and now can't add one back. Command I'm using is:
ALTER TABLE [DB].DBO.[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED (Field1, Field2)
Due to the large size (change of text fields back to varchar for speed but need to move them out to bring the size under 8060 bytes) I always get the "warning" message about row size, however I would have expected it to still add the primary key?
All help, hints and answers appreciated!
Jamie
June 4, 2009 at 1:30 pm
If its any help, I can create an identical table, copy the data in using insert statements but its only adding the primary key which is causing the issue.
The largest volume of data per row is 1800 bytes, well under the 8060 limit.
The server is SQL Server 2000, works fine under 2005 and up, but we can't upgrade to this.
Answers on a postcard?
June 4, 2009 at 3:55 pm
I haven't got an SQL2k machine I can test this on, so this is off the top of my head.
The only thing I can think of is to do an "Alter Table ... Alter Column ..." to shrink the unused varchar fields down so the largest possible row length is less than the 8060 byte limit. For example, if you've got a varchar of 8000 bytes but only 1000 maximum in it then alter it to be 1000 bytes, add the PK then alter the field back to the original size again.
Of course, this is potentially going to be quite slow if the table has a lot of rows, and it may blow your log file out too.
I'm assuming you want to do it in place eg. because of a lack of disk space: the move of the data to a new table with the PK already in place is almost certainly going to be quicker than the above.
June 4, 2009 at 4:25 pm
Thanks Glenn, good couple of points. I'm restricted and can't redefine the varchar fields unfortunately - but I take your point about the primary key before the insert.
The really odd thing is that there are other tables that blow the size limit on a full row, but only this one is generating the "warning" on the primary key.
Also why would a warning stop the actual Alter statement?
June 4, 2009 at 4:39 pm
What is the total size of the primary key you're trying to create?
If the index you're trying to create would have entries that are longer than 900 bytes then it will fail. You will be able to create that index on an empty table (although you'll get a warning), but when you try to insert a record for which the index entry would be > 900 bytes then that insert will fail.
The above will apply for primary keys too (although a primary key of that size is going to be of questionable benefit in most cases, but that's a separate discussion).
June 5, 2009 at 2:21 am
Its a combination of two integer fields, so well under the 900, although it is clustered.
June 5, 2009 at 3:00 am
Jamie Batiste (6/4/2009)
ALTER TABLE [DB].DBO.[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED (Field1, Field2)
why give "CLUSTERED" in your alter command .
when you add a primary key it always creates clustered index.
To figure out if the PRIMARY KEY is clustered or not, issue:
EXEC sp_help 'mytable'
and look in the 6th result set returned.
Tanx 😀
June 12, 2009 at 2:31 am
Sorry for the long post delay.
I should explain the full scenario. We have created a full database update tool. It takes an original database, generates meta data for all objects on the master. At a client site it takes the metadata and allows you to update from any version to the current master, ensuring that no data is lost. This has been running for the best part of 5 years.
The issue here occurs on one table which is due for review and because of 4 large varchar fields blows the 8064 limit.
For small changes the tool adds/removes columns as required. For larger changes, it creates a table of the same name but with tmp_ in the name. Data is then copied across from the original table to the new corrected version. The original table is then removed and the primary key applied (it already existed prior to the deletion of the original table). The clustered keyword is used because it appears in the master primary key.
In this instance and only this instance the key cannot be generated because the warning occurs and stops execution of the index generation command. This is the first and only time I've seen a warning (not an error) cause SQL statements to stop execution - and only on SQL2000 due to the limit rules changing in 2005.
Hope that helps some.
June 12, 2009 at 6:48 am
You must hve a row (rows) where those 2 fields indeed exceed the limit.
That's why index creation fails.
_____________
Code for TallyGenerator
June 12, 2009 at 7:04 am
the largest data size of any row is 1800 bytes.
June 12, 2009 at 7:10 am
Jamie Batiste (6/12/2009)
the largest data size of any row is 1800 bytes.
Change column definitions then.
_____________
Code for TallyGenerator
June 12, 2009 at 7:33 am
Possibly the warning you're seeing is not the real problem
Is there another clustered index on the table?
June 12, 2009 at 8:26 am
Sadly no. Its a freshly built table containing all data but with no indexes on at all.
Sergiy, I can't change the varchar fields just yet unfortunately. The really odd thing is that the table defiinition has been oversize for a long time yet this is the first time that any issue has occured.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply