Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Using nvarchar(max) for all data types in a survey system

    I am working on a survey system with another DB Engineer.

    Our current design includes 1 table per Question Type (Numeric, Boolean, Text), Response values are tightly coupled based on data being entered, to keep things simple assume there are only 6 tables in this this system (1 for each Question Type to define the "rules" of the data entered, and 1 for each Response collection).

    He is a former .Net programmer and has been talking with the .Net guys on the project and he is proposing that we abstract our Question Types to use one table and store the Input Values all as nvarchar(max).

    This obviously simplifies the DB design and reduces the amount of work I will need to do so I should be all for it. I guess I am a little concerned that we are no longer tightly coupling the data types and essentially leaving all the validation up to the application. Also should I be worried that a bunch of bit data will be stored as 1/0 in an nvarchar(max) column?

    Thanks,
    -John

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as you've got infinite disk, don't need to worry about data quality, and will never have a processing deadline, I can't see any problems.

    On a more serious note, you are quite correct to be concerned about this idea. NVARCHAR(MAX) columns tend to use large amounts of disk. They have no type checking, so nearly anything can be wedged into them. Due to the conversions needed to do anything with them (such as arithmetic or comparisons) and even the way that they are transmitted in a result set, they tend to take more time to process.

    -PatP

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    I pressed him on this, and he said we could create an equivalent OLAP structure when we work on the Reporting phase of this project.

    It may be shortsighted but management has decided the deliverable for this phase is ONLY taking and storing surveys (as well as all of the administration associated with that like adding participants to take the survey, etc...).

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its not "shortsighted". Its "agile"!
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    another issue with nvarchar(max) cols is they can't be in an index key. probably not important for this application though.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I always love database projects whose purpose is to store data, and never have it extracted. I usually suggest using a remarkably fast storage engine called '/dev/null' for these projects.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am starting to think that if your resume says .Net Developer on it, you should be forever denied access to the CREATE and ALTER table commands.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The INSERT and UPDATE times for \dev\nul are fantastic, but the retrieval time is dismal.

    -PatP

  9. #9
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by blindman
    Its not "shortsighted". Its "agile"!
    Funny you should mention that, it is our first Agile project.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Seriously? Do I win a prize for that?
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Sep 2005
    Posts
    161
    If you don't want to rely on the data being verified from the application, you can create a stored procedure to insert responses into the database. The SP will verify the data and raise an error is it doesn't meet your criteria. You can do the same thing with a trigger, but I wouldn't suggest a lengthy trigger on a table.

    Unless some of the responses are essays or novels, you don't have to use varchar(max).

  12. #12
    Join Date
    Jun 2005
    Posts
    319
    We can certainly use nvarchar(1000) or something similar, which is still > 900 bytes which means covering index, which means poorer index performance.

  13. #13
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by Gagnon
    We can certainly use nvarchar(1000) or something similar, which is still > 900 bytes which means covering index, which means poorer index performance.
    Why does this mean covering index? 900 is the max length of an index unless you use included columns.

    The number of bytes used for a particular row is
    2+(number of characters)*2.
    varchar is
    2+(number of characters).
    Last edited by cascred; 04-24-08 at 15:20.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought we had already agreed that you shouldn't store all your data in a single character column..?

    Boolean answers in a bit field, sentences in variable length character fields... I don't know why this idea is still festering...
    George
    Home | Blog

Posting Permissions

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