There is nothing wrong with the logic of using TEXT instead of varchar (or nvarchar), except in the situation in which logging is required using TRIGGERS. In this case you are unable to log the changes to the TEXT fields and varchar is more suitable. What you do no need is to ensure that you check the ADO error codes and that you react on size overruns. However, from my experience there are many cases in which the logic is not for the pure approach of total normalisation (all text fields into a single common TEXT table), but rather the pragmatic in keeping like information together, and knowing that it will be very rare to have the size limitation exceeded. This does not imply that all fields are varchar(8000), but that a combination of varchars of different sizes, suited to the data needs, is likely to exceed the 8060 limit in some situations, and yet it is rare that they all are at their max length at the same time. I full agree that the sidepread usage of varchar(8000) for all character fields is worst practise.
Roger Layton (email@example.com
Managing Director, Roger Layton Associates (Pty) Ltd (www.rl.co.za)