SQL Server: the most commonly used data types?

Question

I'm a bit confused as there are many variable types in the sql server (ntext, varchar, nvarchar, etc.), so maybe if you give me what data types you use for the following fields, I will understand this a little better. If I do not have a generic field type, please let me know.

ID
Phone number
Email
Description (text paragraph)
Name
PLA
Price
departure date
Sex (m / f)
Discontinued (Yes / No)
number
Postcode

Answer 1

Brief recommendation:

• TEXT, NTEXT, IMAGE : all of these types are outdated and are planned to be removed in a future version of SQL Server - do not use them!

• CHAR vs. VARCHAR : CHAR is a fixed length, and it will pad entries with spaces to a specific length. Best suited for short lines (<5 characters), for example. codes such as currency (almost always 3 characters), US status (2 characters), etc. VARCHAR , on the other hand, is best suited for longer strings and only retains as many characters as inserted / updated. If you define a VARCHAR(200) and just insert Christmas into this field, your field takes 9 characters (and a small amount of overhead)

• NCHAR/NVARCHAR : Unicode version higher; always stores 2 bytes per character, so your Christmas field in it will store 9 characters and use 18 bytes for this. This is necessary if you have non-Western European characters such as Cyrillic, Arabic, Hebrew, Asian or other alphabets.

• VARCHAR(MAX) / NVARCHAR(MAX) are replacements for TEXT and NTEXT - data storage up to 2 GB (2 billion bytes) - which is 300 times the contents of Tolstoi War and Peace - should be sufficient for the vast majority of cases :-)

So your decision tree could be like this:

• Do I need non-Western European characters? If yes -> use NCHAR/NVARCHAR , otherwise CHAR/VARCHAR

• Is my line very short (<5 characters) and usually always the same length? If yes: use CHAR, otherwise VARCHAR

• Do I really need really huge amounts of text? If so, use VARCHAR (MAX), otherwise its size will suit your needs.

Answer 2

 Field -> Data Type ----- --------- Id int Phone # varchar Email varchar Desc varchar Name varchar Ssn varchar Price decimal, money, smallmoney ShipDate datetime Sex bit Discont bit Quantity int ZipCode varchar 

Answer 3

 ID - int Telephone - varchar(12) email - varchar(size) descripion varchar(max) name -varchar(size) ssn - varchar(11) price - smallmoney (or money if needed) shipdate - date (for sql server 2008, or smalldatetime for pre-2008) discontinued - bit quanity - int zipcode - varchar(10) 

Many people are going to recommend nvarchar in all cases instead of varchar, but knowing my sites / audience, I don’t need to allow international character sets and don’t want to waste space / speed / resources (as far as I know). If you need, replace nvarchar if necessary.

Answer 4

Here is what I used in the past

 ID = bigint Telephone = varchar(12) Email = varchar(100) Description = nvarchar(max) (sql Server 2005 and 2008 only) Name = nvarchar(100) SSN = varchar(11) Price = money ShipDate = datetime (date if using SQL Server 2008) Sex = char(1) (i have also used bit before 0 = female 1 =male) Discontinued (true false field) = bit Quantity = int if not fractional decimal if it is fractional ZipCode = varchar(10) 

Answer 5

 ID int or bigint Telephone Number varchar Email varchar Description varchar Name varchar SSN varchar Price money Ship Date datetime or date Sex (m/f) char(1) Discontinued (yes/no) bit Quantity int Zip Code varchar