Which type should i use for saving a string like this: 000000-0000?
-
Our customers are all id'ed by their social securitynumber (CPR) which is in the format 000000-0000 here in Denmark. How would you save this in the DB? The easiest way is ofcourse to save it as a string, but i'm also considering an integer-field. Does anyone know of the performance in searching when dealing with strings vs. integer fields (in a db), and would it be worth the effort to write code that converts between userinputted CPR's to integer? We are using MSSQL, and i'm programming in C# if it matters.
Why you don't use varchar column datatype in you DB? Why field in DB have to be integer datatype? I suggest to use varchar column in DB.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
-
Why you don't use varchar column datatype in you DB? Why field in DB have to be integer datatype? I suggest to use varchar column in DB.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
That is why i'm asking the question - i don't know. Won't the performance fall enough to take the cpr-fields datatype into consideration?
-
Our customers are all id'ed by their social securitynumber (CPR) which is in the format 000000-0000 here in Denmark. How would you save this in the DB? The easiest way is ofcourse to save it as a string, but i'm also considering an integer-field. Does anyone know of the performance in searching when dealing with strings vs. integer fields (in a db), and would it be worth the effort to write code that converts between userinputted CPR's to integer? We are using MSSQL, and i'm programming in C# if it matters.
Store it as a string. We have a very similar format for SSN and it's proven to be best in string. If you want to search the whole SSN (using equality operator) or by describing the start of it (using LIKE '...%'), it will be efficient with string as long as you have an index on it. If you want to search efficiently using subparts of the SSN (for example Year BETWEEN XX and YY), you can break it down to other calculated fields such as year, month etc and index them. Create a trigger which fills the calculated fields and never let the user to modify them. Also if you can, enforce a uniqueness on the index on SSN, the search will be faster. Typically SSN should be unique but I don't know if you have exceptions and even if you do, do you encounter them in your application. Also use triggers to verify the correct format of SSN so you won't have accidental mistakes/duplicates. Hope this helps, Mika
The need to optimize rises from a bad design.My articles[^]
-
That is why i'm asking the question - i don't know. Won't the performance fall enough to take the cpr-fields datatype into consideration?
As you know if you store value (example)
000012as integer in DB then you will get only12value and zeros before12value will be eliminated automatically.So in your case I would use varchar datatype in DB.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
-
Store it as a string. We have a very similar format for SSN and it's proven to be best in string. If you want to search the whole SSN (using equality operator) or by describing the start of it (using LIKE '...%'), it will be efficient with string as long as you have an index on it. If you want to search efficiently using subparts of the SSN (for example Year BETWEEN XX and YY), you can break it down to other calculated fields such as year, month etc and index them. Create a trigger which fills the calculated fields and never let the user to modify them. Also if you can, enforce a uniqueness on the index on SSN, the search will be faster. Typically SSN should be unique but I don't know if you have exceptions and even if you do, do you encounter them in your application. Also use triggers to verify the correct format of SSN so you won't have accidental mistakes/duplicates. Hope this helps, Mika
The need to optimize rises from a bad design.My articles[^]
Mika > Thanks a lot! That answer was very helpful. Blue_boy > You could format the integers when you show it to the user and easily insert the missing 0. But i'm convinced - i shall use varchar for SSN :)
-
Mika > Thanks a lot! That answer was very helpful. Blue_boy > You could format the integers when you show it to the user and easily insert the missing 0. But i'm convinced - i shall use varchar for SSN :)
-
Mika > Thanks a lot! That answer was very helpful. Blue_boy > You could format the integers when you show it to the user and easily insert the missing 0. But i'm convinced - i shall use varchar for SSN :)
Jan Sommer wrote:
You could format the integers when you show it to the user and easily insert the missing 0
Yes,but your question was how to store value in DB like string or integer,by the way the SSN format
000000-0000you can not store in DB as integer so you must use varchar.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
-
As you know if you store value (example)
000012as integer in DB then you will get only12value and zeros before12value will be eliminated automatically.So in your case I would use varchar datatype in DB.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
That's an easily corrected formatting issue, and not a reason to prefer integer over varchar.
-
Jan Sommer wrote:
You could format the integers when you show it to the user and easily insert the missing 0
Yes,but your question was how to store value in DB like string or integer,by the way the SSN format
000000-0000you can not store in DB as integer so you must use varchar.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
You have no idea what you are talking about. You are confusing data storage representation and UI visualization. They are not related.
-
That's an easily corrected formatting issue, and not a reason to prefer integer over varchar.
-
You have no idea what you are talking about. You are confusing data storage representation and UI visualization. They are not related.