|
|
Posted by Erland Sommarskog on 09/13/06 22:01
Tzanko (tzanko.tzanev@strategicthought.com) writes:
> The question:
> What is the best way to implement this in SQL Server 2000. In
> particular I am looking at two options: Setting each of the 6 columns
> to be of type 'text'. Looking at the documentation, it appears that
> this would behave for as long as each string is not longer than 4000
> characters and I am happy to have this limit. It however is unpleasant
> to use the text type for longer than 4000 char strings, as in this case
> I understand there are some specific ways of handling the data. Option
> two is to create a new LongStrings table with 2 columns - long unique
> number and varchar(4000). Each string is stored in this LongStrings
> table and is crosreferenced (by using the unique ID) with its original
> cell in its original table. Now I'd preffer option 1 (provided I do not
> have to do anything special to handle the strings) and would like to
> avoid option 2 because it is not easy to write queries to get the data.
The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.
If the key of the table is (cola, colb) the new tables should also have
the keys (cola, colb). Simply, what you do is that you split the columns
over several tables.
You should consider text or varchar(max) if you really need to fit more
than 8000 characters.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|