Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wondering_sa
Starting Member
8 Posts |
 Posted - 2013-10-11 : 13:26:27
|
Hi Everybody!  This is a design/performance question. There is an important field in our database everything else revolves around. The data type of the field is (nvarchar(50), null), and changing it is not an option.We do have a choice of the data input though. We can store numeric or alphanumeric values in the filed. Would picking one over another help with overall database/application performance?Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-11 : 13:52:04
|
| numeric may have slight upper hand over alphanumeric data. What will data used for? is it some kind of unique identifier?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-11 : 15:07:48
|
Couple of things to keep in mind regarding primary key/key candidate columns:1. It helps if you have an ever increasing primary key. That will avoid page splits, defragmentation and similar maladies.2. It helps if you have a narrow primary key. The primary keys goes along for the ride in every index and foreign keys if you have any.But in your case neither of these apply because it is a nullable column - which means it is not primary key or foreign key. So whether you will gain or lose by keeping it as numeric or not largely depends on how you will be using it.For example, if you will need to cast it to an integer freqently and in doing so will need to eliminate non-numeric characters then it is worthwhile to restrict it to numeric digits via a constraint.On the other hand, if it is numeric digits, and if people make the assumption that it is numeric and depend on that, for example, for sorting, they would be surprised by the string sort which might have been unexpected.So... I don't know, I don't have any opinion one way or the other... I just wanted to ramble on, that's all  |
|
|
|
wondering_sa
Starting Member
8 Posts |
Posted - 2013-10-12 : 23:45:48
|
| Thank you for your replies!This is a part number field in a large manufacturing database. Our customer makes lots of unique items, and has a funny habit of assigning distinct part number to every piece. As a result, the number of items is likely to hover in hundreds of thousands.If you think that keeping values numeric could help with overall system performance, can you explain why?Thank you. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-03 : 12:30:02
|
| If the field is nvarchar(50), null and you cannot change that then it doesn't make much difference what you put in there.If it happens that you can use ascending-only numeric values then you will avoid index page splits (assuming that field is indexed), but if you start at 1000 and get up to 9999 and then add 10000 then after that point all record inserts will involve page splits (periodically). (That will be true after the first time you have increased to one higher order of magnitude, so unless you start at 1000000 and never exceed 9999999 (or a start number with an even larger order of magniture) then you will have this problem. However, if you CAN start with an order of magnitude that you never exceed then all your sorts (which will be alphanumeric for NVarchar) will have the side effect of actually sorting numerically).The part that worries me more is that nvarchar is a grossly inefficient way of storing a whole number. I expect that the application is a package, and the field is allowed to store "anything" to suit different users, but personally in our packages we set such client-independent key fields to be Varchar, rather than NVarchar, so as not to require twice the storage space. We've never had a situation where a client wanted to have such a Product Code type keyfield store wide-characters.Only other issue that occurs to me is to recommend that you do not us leading zeros for your product codes - when you cut & paste to Excel it will assume the column is numeric and chop off and throw away all the leading zeros ... |
|
|
|
|
|
|
|
|
nvarchar - numeric vs alphanumeric