 | |
NTEXT vs NVARCHAR for large number of columns
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
NTEXT vs NVARCHAR for large number of columns -
04-06-2005
, 02:58 PM
#2
| |||
| |||
|
|
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? |
#3
| |||
| |||
|
|
Are some or most of these columns NULLable? What information are you storing in these columns? "Oleg Ogurok" <oleg (AT) ogurok (DOT) ireallyhatespammers.com> wrote in message news:ebX4e.61035$NC6.41820 (AT) newsread1 (DOT) mlpsca01.us.to.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. |
#4
| |||
| |||
|
|
-----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? |
#5
| |||
| |||
|
#6
| |||
| |||
|
|
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. |
once. That's the "beauty" of varchar. :-)
Quote:
|
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? |
Quote:
| Thanks. -Oleg. |
#7
| |||
| |||
|
|
Cant you normalize this ? Greg Jackson PDX, Oregon |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.