How to determine maximum record length

[georgedwilkinson]

I'm using MS SQL Server 2000. The previous developer thought nothing of creating humongous tables with no thought of normalization and I've inherited that. To make matters worse, my user base has learned to create their own updatable queries in Access and they don't want to lose that ability. Thus, every time they need a new field, guess where they want the data stored...that's right, the huge table that's already almost 20k bytes.

SQL Server 2000 has a maximum row size of 8060 bytes. With tables which have the capability of surpassing that if 1/3 of the fields are utilized, I cringe every time my boss asks for more fields.

He is aware that we will be running out of usable "space" in these tables and I am in the process of determining how long we have to go before it all explodes. However, I haven't found any useful information on how to calculate the maximum record length in SQL Server. The closest I came was: http://west-wind.com/weblog/posts/207.aspx near the bottom. Notice this advice is for 2005 and I am using 2000.

Has anyone mastered this problem before? Do you have any advice for how to get this information?

 

[WayneRyan]

George,

"SQL Server 2000 has a maximum row size of 8060 bytes."

Individual fields can hold more than that! I don't normally deal in text data,
but I'm sure that 8,060 can't be the ceiling. Also 20K bytes for a table isn't
that large.

I don't think I understand what the problem is. At least, other than the
fact that they can extend your tables ... that's not nice.

Can't they add the fields to another table?

In the interim, I'll go read some specs.

Wayne

 

[WayneRyan]

George,

Wow, 8,060 is the max!

But, they don't all have to be varchars. You can make some of them text
fields. They'll only cost you 16 bytes. And SQL Server 2005 removes this
limitation.

I wonder where 8,060 came from? 8,192 is a nice power of 2.

More importantly, when one of your users makes a new field, where do they
get its data from?

Wayne

 

[WayneRyan]

George,

Also, if you had them add the new fields to another table, a nice view could
consolidate them and it'd be transparent when they used the view.

Wayne

 

[georgedwilkinson]

Thanks for the response, Wayne.

I found the exact answer to the question I posed to the forum (DATALENGTH), but since you bring it up, I'm not very happy with the way I feel like I'm being forced to implement this particular request.

The users are surprisingly savvy here and many of them write their own updatable queries (I have mixed emotions about this). I was thinking I would normalize this little piece of the system and make an updatable view for them. This could be a pretty big system change and now that summer is over and I can devote myself to this, it's probably time to bite the bullet.

I really am averse to adding ANY more fields to these tables because it breaks every rule of normalization I can think of. The problem is getting real live requirements in anything but tiny chunks that don't tell me how they think they use the system. When I suggested a normalized fix the last time this problem came up (they want to add fields--really just add work-order processing capability to the system) I was told it was a one-off thing and they needed it pretty quick. I had plenty to do then so I let it slide. Now that we come to the new request, the need can be resolved by the exact same normalized structure I recommended the last time I added "ad-hoc" fields (another few fields to basically do work-order tracking).

Just FYI: the fields are currently mostly nvarchar with the occasional date, number, and yes/no field. I could use ntext but I just don't want to add any fields and definitely don't want to change anything unless I'm gonna do it right. Plus I'd have to refresh the links within and re-distribute multiple front ends internationally right in the middle of a troublesome Citrix implementation.

I think that 8060 bytes is too large for any table and am probably gonna just go ahead and normalize this little piece of the system, flatten it on the form, and give them an updatable view (which I've never implemented before-I guess it's about time).

Thanks for the advice, Wayne. I'm still taking advice on how to smooth out this process (i.e. normalize and give updatable view) or any other "magic" you or anyone else knows.

 

[georgedwilkinson]

BTW, 8060 is the size of a database block. SQL 2005 removed the 8060 limitation.

Used to be the same in Oracle 7.2. Oracle gave the DBA the opportunity to set the block size in 8 one time per instance. I think Oracle now allows flexible block sizes within the database.

Since I personally never build OLTP tables that "wide" I've never particularly cared before now.

 

[MagicMan]

Hi George,
SQL 2000 t-sql reference to creating a partioned view (to allow updating) can be found here: http://technet.microsoft.com/en-us/library/aa258253(SQL.80).aspx
I fully agree, you can break the massive table into logical pieces, but you may have to compromise some normalization due to the current knowledge base of the users who create there own queries.
Smiles
Bob
PS: A view has a max of 1024 columns....not sure if that is in the reference.