SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 nvarchar - numeric vs alphanumeric
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wondering_sa
Starting Member

8 Posts

Posted - 10/11/2013 :  13:26:27  Show Profile  Reply with Quote

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

India
52309 Posts

Posted - 10/11/2013 :  13:52:04  Show Profile  Reply with Quote
numeric may have slight upper hand over alphanumeric data. What will data used for? is it some kind of unique identifier?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 10/11/2013 :  15:07:48  Show Profile  Reply with Quote
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
Go to Top of Page

wondering_sa
Starting Member

8 Posts

Posted - 10/12/2013 :  23:45:48  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/03/2013 :  12:30:02  Show Profile  Reply with Quote
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 ...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000