NTEXT vs NVARCHAR for large number of columns

[Oleg Ogurok]

Hi all,

I need to store data into about 104 columns. This is problematic with MS
SQL, since it doesn't support rows over 8kb in total size.

Most of the columns are of type NVARCHAR(255), which means we can't have
more than 8092/(255*2) = 15 columns of this type.

With a row length of more than 8kb, SQL gives a warning that any rows over
that amount will be truncated.

So far I'm seeing two possible solutions to this problem:
1. Split data into multiple tables with the same ID column accross all
tables, and then join them on SELECT statements.

2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because it
contains a pointer to the actual value stored somewhere else. However, NTEXT
doesn't support regular indexing, only through a Full-Text Index catalog. In
this case I'll need to user "WHERE CONTAINS(column Name, 'sometext')" to
perform searches, which is bearable.

I'm inclined toward #2. However I haven't used Full-Text indices before and
don't know their limitations. Will I run into problems with NTEXT? Is there
a better solution?
Thanks.
-Oleg.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

NVarchar is a Unicode data type; do you need to use Unicode? If not
change the data type to Varchar. Does each column have to be 255 chars
wide? If not change the Varchar width to whatever is needed for each
column.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlRDaIechKq OuFEgEQLbewCgxM bvyyacTTgytXK8v zNSUaL/n5YAn19E
W7I2V1XUf2izxY5 DhSPmAqn5
=t8Ui
-----END PGP SIGNATURE-----
Oleg Ogurok wrote:

Hi all,

I need to store data into about 104 columns. This is problematic with MS
SQL, since it doesn't support rows over 8kb in total size.

Most of the columns are of type NVARCHAR(255), which means we can't have
more than 8092/(255*2) = 15 columns of this type.

With a row length of more than 8kb, SQL gives a warning that any rows over
that amount will be truncated.

So far I'm seeing two possible solutions to this problem:
1. Split data into multiple tables with the same ID column accross all
tables, and then join them on SELECT statements.

2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because it
contains a pointer to the actual value stored somewhere else. However, NTEXT
doesn't support regular indexing, only through a Full-Text Index catalog. In
this case I'll need to user "WHERE CONTAINS(column Name, 'sometext')" to
perform searches, which is bearable.

I'm inclined toward #2. However I haven't used Full-Text indices before and
don't know their limitations. Will I run into problems with NTEXT? Is there
a better solution?

[Raymond D'Anjou]

Are some or most of these columns NULLable?
What information are you storing in these columns?

"Oleg Ogurok" <ol**@ogurok.ir eallyhatespamme rs.com> wrote in message
news:eb******** ***********@new sread1.mlpsca01 .us.to.verio.ne t...

Hi all,

I need to store data into about 104 columns. This is problematic with MS
SQL, since it doesn't support rows over 8kb in total size.

Most of the columns are of type NVARCHAR(255), which means we can't have
more than 8092/(255*2) = 15 columns of this type.

With a row length of more than 8kb, SQL gives a warning that any rows over
that amount will be truncated.

So far I'm seeing two possible solutions to this problem:
1. Split data into multiple tables with the same ID column accross all
tables, and then join them on SELECT statements.

2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because it
contains a pointer to the actual value stored somewhere else. However,
NTEXT
doesn't support regular indexing, only through a Full-Text Index catalog.
In
this case I'll need to user "WHERE CONTAINS(column Name, 'sometext')" to
perform searches, which is bearable.

I'm inclined toward #2. However I haven't used Full-Text indices before
and
don't know their limitations. Will I run into problems with NTEXT? Is
there
a better solution?
Thanks.
-Oleg.

[Oleg Ogurok]

About 50 (half) of them are nullable. I'm storing mostly various contact
info as well as some business-related data that needs to be searchable. Most
data is 100-200 characters long.

-Oleg.

"Raymond D'Anjou" <rd*****@savant softNOSPAM.net> wrote in message
news:uR******** ******@TK2MSFTN GP15.phx.gbl...

Are some or most of these columns NULLable?
What information are you storing in these columns?

"Oleg Ogurok" <ol**@ogurok.ir eallyhatespamme rs.com> wrote in message
news:eb******** ***********@new sread1.mlpsca01 .us.to.verio.ne t...

Hi all,

I need to store data into about 104 columns. This is problematic with MS
SQL, since it doesn't support rows over 8kb in total size.

Most of the columns are of type NVARCHAR(255), which means we can't have
more than 8092/(255*2) = 15 columns of this type.

With a row length of more than 8kb, SQL gives a warning that any rows over that amount will be truncated.

So far I'm seeing two possible solutions to this problem:
1. Split data into multiple tables with the same ID column accross all
tables, and then join them on SELECT statements.

2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because it
contains a pointer to the actual value stored somewhere else. However,
NTEXT
doesn't support regular indexing, only through a Full-Text Index catalog. In
this case I'll need to user "WHERE CONTAINS(column Name, 'sometext')" to
perform searches, which is bearable.

I'm inclined toward #2. However I haven't used Full-Text indices before
and
don't know their limitations. Will I run into problems with NTEXT? Is
there
a better solution?
Thanks.
-Oleg.

[Oleg Ogurok]

Yep, I need unicode and the data can be between 100 and 255 chars long in
most cases.

-Oleg.

"MGFoster" <[e-mail] > wrote in message
news:Jr******** *******@newsrea d3.news.pas.ear thlink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

NVarchar is a Unicode data type; do you need to use Unicode? If not
change the data type to Varchar. Does each column have to be 255 chars
wide? If not change the Varchar width to whatever is needed for each
column.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlRDaIechKq OuFEgEQLbewCgxM bvyyacTTgytXK8v zNSUaL/n5YAn19E
W7I2V1XUf2izxY5 DhSPmAqn5
=t8Ui
-----END PGP SIGNATURE-----
Oleg Ogurok wrote:

Hi all,

I need to store data into about 104 columns. This is problematic with MS
SQL, since it doesn't support rows over 8kb in total size.

Most of the columns are of type NVARCHAR(255), which means we can't have
more than 8092/(255*2) = 15 columns of this type.

With a row length of more than 8kb, SQL gives a warning that any rows over that amount will be truncated.

So far I'm seeing two possible solutions to this problem:
1. Split data into multiple tables with the same ID column accross all
tables, and then join them on SELECT statements.

2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because it
contains a pointer to the actual value stored somewhere else. However, NTEXT doesn't support regular indexing, only through a Full-Text Index catalog. In this case I'll need to user "WHERE CONTAINS(column Name, 'sometext')" to
perform searches, which is bearable.

I'm inclined toward #2. However I haven't used Full-Text indices before and don't know their limitations. Will I run into problems with NTEXT? Is there a better solution?

[pdxJaxon]

Cant you normalize this ?
Greg Jackson
PDX, Oregon

[Greg D. Moore \(Strider\)]

"Oleg Ogurok" <ol**@ogurok.ir eallyhatespamme rs.com> wrote in message
news:eb******** ***********@new sread1.mlpsca01 .us.to.verio.ne t...

Hi all,

I need to store data into about 104 columns. This is problematic with MS
SQL, since it doesn't support rows over 8kb in total size.

Most of the columns are of type NVARCHAR(255), which means we can't have
more than 8092/(255*2) = 15 columns of this type.
Actually you CAN have more columns than 15, you just can't fill them all at
once. That's the "beauty" of varchar. :-)


With a row length of more than 8kb, SQL gives a warning that any rows over
that amount will be truncated.

So far I'm seeing two possible solutions to this problem:
1. Split data into multiple tables with the same ID column accross all
tables, and then join them on SELECT statements.

2. Use NTEXT instead of NVARCHAR. NTEXT's length is 16 bytes because it
contains a pointer to the actual value stored somewhere else. However, NTEXT doesn't support regular indexing, only through a Full-Text Index catalog. In this case I'll need to user "WHERE CONTAINS(column Name, 'sometext')" to
perform searches, which is bearable.

I'm inclined toward #2. However I haven't used Full-Text indices before and don't know their limitations. Will I run into problems with NTEXT? Is there a better solution?
I'd go with 1.



Thanks.
-Oleg.

[Louis Davidson]

This would be my feeling as well. Normalizing these structures should be
your first step. 50 columns of 255 chars each is nuts, and 104 is
incredibly nuts. Compound that by 104 text columns and your system will be
a real pain.

Can you give us more information about what you want to do.

--
----------------------------------------------------------------------------
Louis Davidson - dr***@hotmail.c om
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"pdxJaxon" <Gr************ *@Hotmail.com> wrote in message
news:e7******** ******@TK2MSFTN GP09.phx.gbl...

Cant you normalize this ?
Greg Jackson
PDX, Oregon