How do I cast <T> to varbinary and be still be able to perform a CONVERT on the sql side? Implications?

Question

Hello,

I'm writing this application that will allow a user to define custom quizzes and then allow another user to respond to the questions. Each question of the quiz has a corresponding datatype. All the responses to all the questions are stored vertically in my [Response] table. I currently use 2 fields to store the response.

//Response schema
ResponseID int
QuizPersonID int
QuestionID int
ChoiceID int //maps to Choice table, used for drop down lists
ChoiceValue varbinary(MAX) //used to store a user entered value

I'm using .net 3.5 C# SQL Server 2008.

I'm thinking that I would want to store different datatypes in the same field and then in my SQL report proc I would CONVERT to the proper datatype. I'm thinking this is ideal because I only have to check one field. I'm also thinking it might be more trouble than it is worth.

I think my other options are to; store the data as strings in the db (yuck), or to have a column for each datatype I might use.

So what I would like to know is, how would I format my datatypes in C# so that they can be converted properly in SQL? What is the performance hit for converting in SQL? Should I just make a whole wack of columns for each datatype?

Answer 1

Still not a perfect solution but you might like to take a look at the sql_variant data type which allows you to store most SQL Server types in the same column. But note that "long" data is not supported.

An alternative that I've also used in similar situations in the past is to have a separate table for each type of response along with the "parent" response table. This aso allows you to properly relate to your choice table. For example, you would have the following tables:

• Response
• StringResponse
• DateTimeResponse
• BitResponse
• ChoiceResponse
• XyzResponse, etc

All the typed response tables would be in an optional 1-1 relationship with Response and all other relating tables would relate to the parent Response table only.