p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   table or row properties (http://p2p.wrox.com/showthread.php?t=73506)

juanita March 23rd, 2009 08:07 AM

table or row properties
 
Hi,

I have a table with columns nvarchar 4000 characters each, and gives me error if you attempt to enter more than 8060 byte in a row. How can I extend the size of the row? Any way to do this?

Jeff Moden March 23rd, 2009 08:20 AM

Quote:

Originally Posted by juanita (Post 237717)
Hi,



I have a table with columns nvarchar 4000 characters each, and gives me error if you attempt to enter more than 8060 byte in a row. How can I extend the size of the row? Any way to do this?

No... not in the same table in SQL Server 2000. You have to make some "sister" tables with the same PK.

Why do you need so many columns to contain NVarchar 4000?

juanita March 23rd, 2009 08:30 AM

Thanks, but ..
 
Quote:

Originally Posted by Jeff Moden (Post 237722)
No... not in the same table in SQL Server 2000. You have to make some "sister" tables with the same PK.

Why do you need so many columns to contain NVarchar 4000?


Because fields are free text and all belong to the same subject or matter, and divide the table seems wrong, because I am interested in having all that information in a table ..
Is the only solution is to split the table?

thanks

philip_cole March 23rd, 2009 09:49 AM

The only way you can really store more than 8060 bytes in a row in a single table is to make the columns TEXT, NTEXT or IMAGE. SQL 2000 stores these separate to the main row data, and can be up to 2GB in length. See http://msdn.microsoft.com/en-us/libr...4(SQL.80).aspx and http://msdn.microsoft.com/en-us/libr...3(SQL.80).aspx
TEXT datatypes are comparatively inefficient tho, so if you could find a way to reduce the field lengths or splitting it up somehow, that may be better.

HTH
Phil

Jeff Moden March 23rd, 2009 04:19 PM

... and before you even think of using a TEXT datatype column, look it up in Books Online and see what a pain in the rear it is to actually do anything with. Most of the string functions will not work on it and concatenation will not work on it. And, ya just gotta love those bloody pointers it uses.

Old Pedant March 23rd, 2009 07:58 PM

Gee, Jeff, I didn't know TEXT fields were so useful. <grin/>

Jeff Moden March 24th, 2009 12:29 AM

Heh... yeah... I've never ever understood why they even bothered. And what took them so long to figure out VARCHAR(MAX) [:p]

juanita March 24th, 2009 05:11 AM

Short text field string
 
Hi!
I watched the online help, and I've tested with the type of text data (with their corresponding functions for inserting and reading) and I cut the chain to 256, the only data type that I am not a short string is 3256 characters varchar. How many bytes is 12 character varchar?

Jeff Moden March 24th, 2009 06:20 PM

Quote:

Originally Posted by juanita (Post 237826)
Hi!


I watched the online help, and I've tested with the type of text data (with their corresponding functions for inserting and reading) and I cut the chain to 256, the only data type that I am not a short string is 3256 characters varchar. How many bytes is 12 character varchar?

Um.... 15 I believe. 12 for the characters, 2 for the character count, and 1 for nullability.

Jeff Mason March 24th, 2009 07:34 PM

Quote:

Originally Posted by Jeff Moden (Post 237900)
Um.... 15 I believe. 12 for the characters, 2 for the character count, and 1 for nullability.

Not to be picky here (OK, maybe a little [:D]), but this isn't quite right.

Varchar columns are stored in a data row after all the fixed length columns. The length of a given varchar column's data is not stored, but rather an array of 2 byte offsets to each column of variable data is stored. Of course, the offsets can be used to easily compute the length of any variable column, but to be precise the length is not explicit.

Whether a column contains NULL is stored in a bitmap, one bit for each column of data (variable or fixed). If the bit is set, the column contains NULL, and no data is stored if the data is a variable type. If the bit is zero, then offsets can be used to locate the data.

Curiously, this technique of not storing the data if a variable column is NULL does NOT apply to fixed length columns. So, if you define a CHAR column of length 50, say, then 50 bytes will be allocated whether or not the column has the value NULL. Zeroes are stored in the data bytes if the fixed length column is NULL, otherwise the actual data is there.

I confess I got this from my 'Inside SQL Server 2000' book, but I'm pretty sure these details have not changed in later releases of SQL Server.


All times are GMT -4. The time now is 06:42 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.