July 18, 2011 at 11:04 am
Dear Experts!
I am maintaining the Large scale Product metadata, Unfortunately the metadata tables not supports for the Varchar Data types like Unicode characters sets involved in metadata, so i have changed the Data type varchar into nvarchar, now supported
but My doubts is
1) Is it correct way to altering the column Data type to Nvarchar? after changes any memory issues there?
2) When the automated updated happens the string length issues and data type issues in update and Insert Query occurred simultaneously, so i have hanged the Nvarchar(MAX) for all columns , Is it correct way, in future any problem will occurs in tables?
3) What about the table datatype size (ie) 8086 bytes rules
Please help me to overcome this problem,
July 18, 2011 at 11:12 am
N-types (NChar, NVarchar) take twice as much storage as ASCII for the same number of characters. Thus, you are likely to have a lot of page fragmentation if you convert from one to the other in a live table. Also, Unicode (N) can't go above 4000 characters unless you use NVarchar(max), so if you have Varchar(8000), or anything over 4000, you'll have to convert to the Nvarchar(max) data type. That can cause some performance issues in the I/O layer, but it's not noticeable in most systems.
You'll need to test in a Dev and QA environment before you take something like this live. Test thoroughly!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 18, 2011 at 11:29 am
many thanks!
But i need to clarify with 2 and 3 points, please help, if altering the all columns as MAX mean?
July 19, 2011 at 4:11 am
Saravanan_tvr (7/18/2011)
2) When the automated updated happens the string length issues and data type issues in update and Insert Query occurred simultaneously, so i have hanged the Nvarchar(MAX) for all columns , Is it correct way, in future any problem will occurs in tables?
Sigh...if you must, but I would not recommend it. There aren't many certainties, if any, in the relational database world in terms of things you should always do but I would nominate choosing the proper data-type for your columns as a candidate. Using a MAX data type for all of your variable-length character columns is lazy (sorry). There is a cost associated with using the MAX data types over using columns with a declared maximum length. Here is a good article on that topic with some metrics to back it up: http://richardlees.blogspot.com/2010/07/varcharmax-performance-in-sql-server.html
3) What about the table datatype size (ie) 8086 bytes rules
The limit on the max row length was mostly lifted when SQL Server 2005 was introduced. There are some restrictions but in your scenario you shouldn't be affected by them. You can read about it here: http://technet.microsoft.com/en-us/library/ms186981.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply