Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 23rd, 2009, 08:07 AM
Registered User
 
Join Date: Mar 2009
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?
  #2 (permalink)  
Old March 23rd, 2009, 08:20 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
Originally Posted by juanita View Post
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?
__________________
--Jeff Moden
  #3 (permalink)  
Old March 23rd, 2009, 08:30 AM
Registered User
 
Join Date: Mar 2009
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Thanks, but ..

Quote:
Originally Posted by Jeff Moden View Post
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
  #4 (permalink)  
Old March 23rd, 2009, 09:49 AM
Friend of Wrox
Points: 894, Level: 11
Points: 894, Level: 11 Points: 894, Level: 11 Points: 894, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: London, , United Kingdom.
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

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
  #5 (permalink)  
Old March 23rd, 2009, 04:19 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

... 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.
__________________
--Jeff Moden
  #6 (permalink)  
Old March 23rd, 2009, 07:58 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Gee, Jeff, I didn't know TEXT fields were so useful. <grin/>
  #7 (permalink)  
Old March 24th, 2009, 12:29 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Heh... yeah... I've never ever understood why they even bothered. And what took them so long to figure out VARCHAR(MAX)
__________________
--Jeff Moden
  #8 (permalink)  
Old March 24th, 2009, 05:11 AM
Registered User
 
Join Date: Mar 2009
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?
  #9 (permalink)  
Old March 24th, 2009, 06:20 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
Originally Posted by juanita View Post
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 Moden
  #10 (permalink)  
Old March 24th, 2009, 07:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
Originally Posted by Jeff Moden View Post
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 ), 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.
__________________
-- Jeff




Similar Threads
Thread Thread Starter Forum Replies Last Post
Modify field properties in linked table RickD Access 1 September 8th, 2006 06:58 AM
table retains borderTop and borderLeft properties crmpicco Javascript How-To 6 February 22nd, 2006 06:21 AM
UPDATING 1 row with another row in same table rit01 SQL Server 2000 3 February 19th, 2006 08:55 AM
first row of a table Adam H-W SQL Language 2 January 11th, 2005 10:15 AM
How to know the acual row in my table drachx SQL Server 2000 7 October 18th, 2004 12:31 AM





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