Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Unanswered: (Almost) always use NVARCHAR?

    If you're designing a database that is not likely to run into space issues because of the massive volume of data, would it just make sense to always use nvarchar instead of varchar? Every time I use varchar I feel like a pre-Y2k programmer saying I'll just use 2 digits for the year.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    no. use nvarchar when appropriate. not blindly.

    unicode makes your tables wider, which means you fit fewer rows on a page, which means more frequent reads, which means perf suffers.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If the conditions you set forth will ALWAYS be true, - I don't see a reason why you shouldn't make it NVARCHAR for every string column (or NTEXT for a large string column). But there is a question, - are you sure that it will ALWAYS be the case? However, in my experience, making room for localization always paid off, so even in general I believe that to account for extended character set is always a good idea. And if you're lucky, and your app happens to run for more than 5 years, - you will see (or hear about) the benefits of doing so. Just use the benefits that came with 2K5 (data partitioning in particular), and the obvious overhead associated with unicode data may not end up being the culprit of the problems
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    defintely not ntext. use nvarchar(max) where you are thinking of ntext.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    very good point! missed that
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Mar 2008
    Posts
    52
    What about for stored procedure formal parameters? Can you always use NVARCHAR there? Is there some drawback?

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you compare a nvarchar param with char/varchar column, you'll be forcing a conversion to take place. so here again, use the appropriate datatype.

    so don't use unicode blindly for params. unless of course you are using unicode blindly for columns!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah what the heck - into the fray again. If you will NEVER need to store unicode data why use nvarchar? You guys don't use bigints for boolean fields do you, just in case one day there are several billion possible values? If you might one day need to store unicode then of course nvarchar but I would base it on the business case and not on how much disk you have. Jesse's point is totally valid - nvarchar lowers the density of information on pages so IMHO you need a justification to use it, not a justification not to.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I use the FLOAT datatype for my boolean fields, so that I can represent different degrees of "truthiness".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    booleans only? you should use float for your surrogate keys too.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    I use the FLOAT datatype for my boolean fields, so that I can represent different degrees of "truthiness".
    Do you have an algorithm for that (for example based on the log in) or do you allow the users to declare the degree of "truthiness"?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jezemine
    booleans only? you should use float for your surrogate keys too.
    That would be funny were it not for the fact that one of my clients has a database that uses surrogate keys with decimal values.

    Quote Originally Posted by pootle flump
    Do you have an algorithm for that (for example based on the log in) or do you allow the users to declare the degree of "truthiness"?
    I go with my gut feeling, of course.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Sep 2005
    Posts
    161
    I agree with the varchar camp. The most important thing is to be consistent. Try joining a varchar column on an nvarchar column and you'll end up with an index scan.

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by blindman
    That would be funny were it not for the fact that one of my clients has a database that uses surrogate keys with decimal values.
    I maintain it's still funny despite that fact. It killed at Chortles last night...

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Hmmmm... some useful stuff in this thread. But since it's sometimes hard to read sarcasm, I hope I don't end up using the wrong stuff.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •