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
  #11 (permalink)  
Old March 24th, 2009, 08:28 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

Originally Posted by Jeff Mason View Post
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.
All true and well stated... but unlike SQL Server 2005, VARCHAR's are included in the max length of a column and therefor, you must plan on the max length of the varchar.
--Jeff Moden

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.