Choosing the best data type...

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
LVL 8
rodmjayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hongjunCommented:
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.
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
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.


Cheers!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
knucklesbammCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rodmjayAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.