469,898 Members | 1,720 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

NTEXT vs NVARCHAR for large number of columns

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(columnName, '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.
Jul 23 '05 #1
7 11925
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQlRDaIechKqOuFEgEQLbewCgxMbvyyacTTgytXK8vzNSUa L/n5YAn19E
W7I2V1XUf2izxY5DhSPmAqn5
=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(columnName, '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?

Jul 23 '05 #2
Are some or most of these columns NULLable?
What information are you storing in these columns?

"Oleg Ogurok" <ol**@ogurok.ireallyhatespammers.com> wrote in message
news:eb*******************@newsread1.mlpsca01.us.t o.verio.net...
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(columnName, '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.

Jul 23 '05 #3
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*****@savantsoftNOSPAM.net> wrote in message
news:uR**************@TK2MSFTNGP15.phx.gbl...
Are some or most of these columns NULLable?
What information are you storing in these columns?

"Oleg Ogurok" <ol**@ogurok.ireallyhatespammers.com> wrote in message
news:eb*******************@newsread1.mlpsca01.us.t o.verio.net...
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(columnName, '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.


Jul 23 '05 #4
Yep, I need unicode and the data can be between 100 and 255 chars long in
most cases.

-Oleg.

"MGFoster" <me@privacy.com> wrote in message
news:Jr***************@newsread3.news.pas.earthlin k.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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQlRDaIechKqOuFEgEQLbewCgxMbvyyacTTgytXK8vzNSUa L/n5YAn19E
W7I2V1XUf2izxY5DhSPmAqn5
=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(columnName, '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?

Jul 23 '05 #5
Cant you normalize this ?
Greg Jackson
PDX, Oregon
Jul 23 '05 #6

"Oleg Ogurok" <ol**@ogurok.ireallyhatespammers.com> wrote in message
news:eb*******************@newsread1.mlpsca01.us.t o.verio.net...
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(columnName, '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.

Jul 23 '05 #7
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.com
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**************@TK2MSFTNGP09.phx.gbl...
Cant you normalize this ?
Greg Jackson
PDX, Oregon

Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Anand | last post: by
1 post views Thread by Derek Erb | last post: by
1 post views Thread by Rippo | last post: by
10 posts views Thread by Jerry | last post: by
4 posts views Thread by Cylix | last post: by
3 posts views Thread by =?Utf-8?B?ZGF2aWQ=?= | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.