Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Choosing the best data type...

Posted on 2006-04-13
Medium Priority
Last Modified: 2012-06-27
I am making a few key decisions and would like your input on the data types i should use...

1.  A field that will be used for currency values, i am debating between decimal(7, 2) or money.  I dont need to store any data after the penny decimal place.

2.  A field that can store html.  Should I use varchar(4000) or nvarchar(4000) is there a difference?

2a. Also, a field that can store url values.  Preferably without any data formatting on the input and output without problems like the " ' " for example
Question by:rodmjay
LVL 33

Expert Comment

ID: 16451507
1. Decimal values does not use approximations when storing numbers. But then this means it will require more space. Money values are a compromise of decimal numbers. In my opinion, I think money is good enough.

2. Use nvarchar only if you are storing special encoding values like chinese, etc. Do you think varchar(4000) is enough for html? I think perhaps you need text. You have no problems storing ' with any of the string datatypes. Just remember to use 2 ' instead of 1.
LVL 97

Expert Comment

by:Lee W, MVP
ID: 16451515
Hi rodmjay,
> 1.  A field that will be used for currency values, i am debating between
> decimal(7, 2) or money.  I dont need to store any data after the penny
> decimal place.

Personally, I'd use money... but I don't think there's going to be any difference.

> 2.  A field that can store html.  Should I use varchar(4000) or
> nvarchar(4000) is there a difference?

nvarchar is unicode and data will take 2x the raw space.  Meaning for every character stored in unicode format, 2 bytes of data are used.  This I would say depends on if you have any need for storing text that might be in non-english languages.  (While technically Spanish and several romance languages are included in the ASCII character set, Russian, greek, and many asian languages are not, but ARE included in Unicode.

> 2a. Also, a field that can store url values.  Preferably without any
> data formatting on the input and output without problems like the " '
> " for example

' is used to indicate text.  You will ALWAYS have problems with this and must escape any instance of it with a double single quote mark.  How are you storing the data?  If in VB, VBA, or ASP, you would just use REPLACE(fieldvalue, '"'", "''"), for example.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16451528
1) use decimal. money contains the currency symbol, which is not really a good option for prod tables.

2) varchar should be fine, as pure html should not contain special characters (they will all be encoded with html tags).

2a) ' is not a problem as such, you have to code correctly.

Accepted Solution

knucklesbamm earned 1000 total points
ID: 16451559
Hey there,

A money type is functionally equivalent to a decimal(19, 4) in terms of digits and decimal spaces... however, the money type only consumes 8 bytes of space. You also have the option of using a smalmoney type that is functionally equivalent to decimal(10, 4) in terms of digits and decimal spaces, but it only uses 4 bytes of storage. A decimal(7, 2) will consume up to 5 bytes of storage...

In terms of which storage type to use between the three, it really comes down to how large of money values you wish to store. For example, if you want store monetary values over $214,748, you should not use the smallmoney datatype. A decimal 7, 2 affords you up to 5 digits before the decimal (i.e. up to 99,999)... this is a smaller number than the smallmoney datatype and uses more space, so this is probably no a good choice at all (not good use of space). My general rule is that if you need less than a decimal(8, 2), use smallmoney. If you need more than a decimal(9, 2), use money. If you need a decimal(8, 2) or decimal(9, 2), use the decimal. To summarize, given your above example, the choice for me would be the smallmoney datatype.

For your second question, the difference between nvarchar and varchar is that nvarchar can store unicode characters... as a result, the storage space required for an nvarchar data type is two times the size of the field in bytes. If you have unicode characters in your HTML, then use nvarchar, if not, use varchar because you'll have a better use of space. If your HTML ends up being exceptionally large in size, you may even want to look at using a text or ntext datatype.

Author Comment

ID: 16451626
Wow, thanks for the input....

So I nvarchar is out of the question, and decimal(7, 2) is a bad use of space.  And based on  your responses, smallmoney seems like the best alternative for me, since we will only be selling cds and magazines.

Thanks a lot, folks

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

575 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question