SQL 2000 Maximum Capacity

[fafrazh99]

Microsoft claims SQL 2K only accept 8060 bytes per row. However, I noticed couple of my clients still design their tables way beyond the maximum capacity, sometime 3 to 4 times larger. This makes me pause and think twice. What if the data inserted larger than 8060, does it really broke the SQL 2K. Did anybody run into this kind of problem before?

Thanks for you feedback!

 

[swampBoogie]

If the total amount of data to be inserted exceeds 8060 character you will get an error. What did you expect?

 

[SQLBill]

You CAN enter more than 8060 characters. But ONLY if you are using TEXT, NTEXT, or IMAGE data types.

-SQLBill

 

[SQLSister]

The only case in which you can risk this type of overdesisn of a table is if the data structure is such that you know positively that all data fileds will never be filled to capacity. For instance if you had a customer table and 20 of the fields would only be filled in if the customer was a lowyer and 20 differnt fields would only be filled if the customer was a doctor, then going over the capacity is OK as long as the longest possible subset of data is less than 8060 bytes. Usually however, this is a mistake made by someone who usually isn't aware of the maximum and who has no idea how big the fields should be so makes them artificially wide to accomodate any possible data.

Choices to fix are as follows:
If this is production database, find the max characters stored in each field and see if you can reduce the size of the fields down to where the total is under the max. YOu might also want to test what happens if you deliberately input a record that is too long, has the user at least been given an error message or does he think heis data was entered?

If it just in the design phase, you will need to analyze the table and see where reductions in field length are possible and if they will get you under the limit.

If reducing the field sies to get under the max record size is not possible, the only safe method I know of to fix this is to split the table into two or more tables that have a one to one relationship.

Of course, in either case you'll also have to check and adjust, where needed, the code that references the tables when you do this, so it is a major effort to make this kind fo fix.

 

[fafrazh99]

Thanks SQLSister for your reponse!