Brand New Database & Storage Options

Brand New Database & Storage Options

Post by Fred Nelso » Thu, 12 Dec 2002 01:23:16



Hi:

I'm fortunate to be designing my first SQL 2000 database.  Many of the files
I will need will contain variable length character fields such as name,
address and descriptions.

It appears to me that I should use nvarchar(size) rather than char(size) for
all such fields since a great deal of space will be saved by this process.
(And I have found that they work fine in all programming I have done during
my training.)

I would appreciate and and all comments as to whether this - or another
approach - is the way to go!

Thanks very much!

Fred

 
 
 

Brand New Database & Storage Options

Post by oj » Thu, 12 Dec 2002 01:37:50


Fred,

Take a look at Northwind or Pubs database. Those could be used as a starting
point.

--
-oj
http://www.rac4sql.net


Quote:> Hi:

> I'm fortunate to be designing my first SQL 2000 database.  Many of the
files
> I will need will contain variable length character fields such as name,
> address and descriptions.

> It appears to me that I should use nvarchar(size) rather than char(size)
for
> all such fields since a great deal of space will be saved by this process.
> (And I have found that they work fine in all programming I have done
during
> my training.)

> I would appreciate and and all comments as to whether this - or another
> approach - is the way to go!

> Thanks very much!

> Fred


 
 
 

Brand New Database & Storage Options

Post by cary » Thu, 12 Dec 2002 01:50:13


There are two sets of character datatypes:

char and varchar
nchar and nvarchar

Both the char and nchar should be used for columns where
the length of the actual data will be very consistent;
varchar and nvarchar should be used for character fields
in which the actual data length with vary greatly (like
you have below).

The difference between the two sets of character data
types is the 'n' datatypes store the data in unicode data
format.  According to BOL, "Using Unicode data types, a
column can store any character defined by the Unicode
Standard, which includes all of the characters defined in
the various character sets. Unicode data types take twice
as much storage space as non-Unicode data types."  Non-
unicode fields can only store characters associated with
the character set chosen at SQL Server setup.

Unless you need to store multiple character sets, I would
use the varchar data type.

HTH-
Cary

Quote:>-----Original Message-----
>Hi:

>I'm fortunate to be designing my first SQL 2000

database.  Many of the files
Quote:>I will need will contain variable length character fields
such as name,
>address and descriptions.

>It appears to me that I should use nvarchar(size) rather
than char(size) for
>all such fields since a great deal of space will be saved
by this process.
>(And I have found that they work fine in all programming
I have done during
>my training.)

>I would appreciate and and all comments as to whether
this - or another
>approach - is the way to go!

>Thanks very much!

>Fred

>.

 
 
 

Brand New Database & Storage Options

Post by Gary Hampso » Thu, 12 Dec 2002 01:51:55


From BOL:

Character data consists of any combination of letters, symbols, and numeric
characters. For example, valid character data includes "928", "Johnson", and
"(0*&(%B99nh  jkJ". In Microsoft? SQL ServerT 2000, character data is stored
using the char, varchar, and text data types. Use varchar when the entries
in a column vary in the number of characters they contain, but the length of
any entry does not exceeds 8 kilobytes (KB). Use char when every entry for a
column has the same fixed length (up to 8 KB). Columns of text data can be
used to store ASCII characters longer than 8 KB. For example, because HTML
documents are all ASCII characters and usually longer than 8 KB, they can be
stored in text columns in SQL Server prior to being viewed in a browser.

It is recommended that the defined length of a character column be no larger
than the maximum expected length of the character data to be stored.

To store international character data in SQL Server, use the nchar,
nvarchar, and ntext data types.

Hope that helps..

Peace,

Gary Hampson


> Fred,

> Take a look at Northwind or Pubs database. Those could be used as a
starting
> point.

> --
> -oj
> http://www.rac4sql.net



> > Hi:

> > I'm fortunate to be designing my first SQL 2000 database.  Many of the
> files
> > I will need will contain variable length character fields such as name,
> > address and descriptions.

> > It appears to me that I should use nvarchar(size) rather than char(size)
> for
> > all such fields since a great deal of space will be saved by this
process.
> > (And I have found that they work fine in all programming I have done
> during
> > my training.)

> > I would appreciate and and all comments as to whether this - or another
> > approach - is the way to go!

> > Thanks very much!

> > Fred

 
 
 

Brand New Database & Storage Options

Post by Roy Harve » Thu, 12 Dec 2002 01:54:04


Fred,

Quote:>I'm fortunate to be designing my first SQL 2000 database.  Many of the files
>I will need will contain variable length character fields such as name,
>address and descriptions.

>It appears to me that I should use nvarchar(size) rather than char(size) for
>all such fields since a great deal of space will be saved by this process.

The choices are char vs. varchar for ascii data, or nchar vs. nvarchar
for unicode (two bytes per character) data.  Yes, for such things as
names, addresses, and descriptions varchar (or nvarchar, as
appropriate) is the way to go.

Roy

 
 
 

1. restore all database to a brand new server

NT4serverSP6a, SQL7SP3
I am restoring all databases(master, msdb, PJC databases and other user
databases) from a server to a brand new server. The two servers have
different names. After all databases have been restored, only PJC(Microsoft
Project Central Database) can not work. What causes this problem? Does
different computer name affect the new sql server?

thanks

2. Crystal Report 16bit

3. error msg 3251 on AddNew method for ADO/Brand New Jet database

4. Entering passwords when opening a form

5. Brand new database crashes apps

6. Win2000 ODBC Problem

7. Brand new Web Based Intro to SQL Tutorial w/ live practice Database

8. Brand new to SQL Server (simple questions)

9. move sql server to brand new server

10. Performance Counters missing on brand new install of SQL 7

11. fs: Brand New 3com PCI 10/100 3c905B Net Card