Best data type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Best data type

Author

Topic

guaro555
Starting Member

3 Posts

Posted - 2011-02-26 : 23:49:07

Hi gurus

I am in the process of creating a table, I would like to no a few things
1) what is the best data type for Alphanumeric,text and numbers, I keep reading I should work with nchar or nvarchar but don't know for sure
2) when creating a field there is a length choice, i work with fixed text file, is it in here where I enter the length of the fix field I am importing?

thank a bunch for all your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-27 : 00:08:18

1. Only use the n data types if you need to store unicode data. Unicode data is for those characters that require double bytes such as Chinese.
2. Use whatever the max size of the field in the your file is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

guaro555
Starting Member

3 Posts

Posted - 2011-02-27 : 01:10:54

T.

Thanks a bunch for your help, so just to make sure I have this
if I have a field that is 8 spaces in length and is numeric, the field I have to create in sql table has to be numeric with length of 8?, if this is the case I most be doing something wrong because when I create the field the default is 16 and I cant change it.how about text when I create the field in sql with data type text it gives me 255.

Thanks fro battling with me here

koto
Starting Member

6 Posts

Posted - 2011-03-08 : 16:49:18

Hi

For the 8 digit numeric filed the optimal solution would be int type.
If you need to enforce a 8 digit length rule you have a few ways e.g.constraint (int_t is a column of int type):
ALTER TABLE [dbo].[TransactionDemo] WITH CHECK ADD CONSTRAINT [CK_TransactionDemo] CHECK ((len([int_t])=(8)))

As for the text use varchar or nvarchar. nchar and text are for backward compatibility and will be removed in feature versions of server.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-08 : 17:37:25

quote:
nchar and text are for backward compatibility and will be removed in feature versions of server

I think you meant "image and text".

koto
Starting Member

6 Posts

Posted - 2011-03-09 : 02:57:59

Yes, robvolk, you are right.

nchar has a feature of padding with nulls thus always use declared size. So it a good choice only if the column is likely to have the values close to the declared size.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-09 : 08:14:21

quote:
nchar has a feature of padding with nulls thus always use declared size

I think you meant "padding with spaces".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources