April 23, 2012 at 10:52 pm
How do write a query to figure how much more disk space i will need when i change all my column from varchar to nvarchar.
As part of new change in my application, we require to change all varchar to nvarchar and before we go ahead we need to know abt disk space.
We have abt 400 tables in the db and almost all the tables have either one or many varchar columns.
April 24, 2012 at 3:19 am
I don't have a query for this but in normal circumstances nvarchar takes double the size as varchar.
Storage Size
Varchar - Actual Length (in bytes)
NVarchar - 2 times Actual Length (in bytes)
According to this you can have an assumption of the increase in Storage space.
Hope this helps.
April 24, 2012 at 3:39 am
Yeah, but you can simply multiply varchar column sizes by two, as it will give you much larger number than it will really take.
Saying that current column varchar(100), changing it to nvarchar(100) will not guarantee that you will need extra 100 bytes straight away, it will depend on the size of data you have in it.
But, for simplicity you can do just that:
select sum(sc.max_length) as current_max_length_in_varchar
,sum(sc.max_length)*2 as required_max_length_in_nvarchar
from sys.tables st
join sys.columns sc
on sc.[object_id] = st.[object_id]
where st.type = 'U'
and sc.system_type_id = 167 -- varchar
April 24, 2012 at 3:44 am
April 24, 2012 at 3:55 am
Yea.. i know
nvarchar = 2(varchar)+2
But i was mainly looking for query.. since I have abt 800 tables and each table has 3/4 varchar columns, with each row count abt 10000.
So to traverse thru them is a big task
Was thinking of picking varchar column from each table and then applying formula (but too tedious and lengthy)
eg. table has 3 varchar column of length 3,4,6 each and row count is 20
so total space needed is = 2(3)+ 2(20); 2(4) + 2(20) ; 2(6)+20 ..
Hope i'm right
April 24, 2012 at 4:03 am
khushbu (4/24/2012)
Yea.. i knownvarchar = 2(varchar)+2
where +2 coming from?
April 24, 2012 at 4:21 am
Eugene Elutin (4/24/2012)
khushbu (4/24/2012)
Yea.. i knownvarchar = 2(varchar)+2
where +2 coming from?
From BOL: http://msdn.microsoft.com/en-us/library/ms186939.aspx
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.
April 24, 2012 at 4:28 am
khushbu (4/24/2012)
Eugene Elutin (4/24/2012)
khushbu (4/24/2012)
Yea.. i knownvarchar = 2(varchar)+2
where +2 coming from?
From BOL: http://msdn.microsoft.com/en-us/library/ms186939.aspx
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.
That's right: the actual length of data entered + 2 bytes
It's not 2(varchar)+2!
As varchar also takes: "the actual length of data entered + 2 bytes" :
April 24, 2012 at 4:34 am
Eugene Elutin (4/24/2012)
khushbu (4/24/2012)
Eugene Elutin (4/24/2012)
khushbu (4/24/2012)
Yea.. i knownvarchar = 2(varchar)+2
where +2 coming from?
From BOL: http://msdn.microsoft.com/en-us/library/ms186939.aspx
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.
That's right: the actual length of data entered + 2 bytes
It's not 2(varchar)+2!
As varchar also takes: "the actual length of data entered + 2 bytes" :
Okies got it, so its always 2 times the stored data.
However my earlier query still holds true , when removing +2 (no of rows) part?
April 24, 2012 at 4:44 am
Eugene Elutin (4/24/2012)
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.
That's right: the actual length of data entered + 2 bytes
It's not 2(varchar)+2!
As varchar also takes: "the actual length of data entered + 2 bytes" :
The quoted BoL extract states "The storage size, in bytes, is two times the actual length of data entered + 2 bytes."
Varchar storage size = 2 bytes + length of data stored
NVarchar storage size = 2 bytes + 2*(length of data stored) because with nvarchar each character takes 2 bytes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 24, 2012 at 4:52 am
A quick way to do this might be to calculate the average row length (via sys.dm_db_index_physical_stats) and subtract the length of any fixed lenght columns within the tables (like int, datetime, decimal, etc). That will give you the average length of varchar columns per table. You can roughly double that to get the average length of nvarchar columns. That'll tell you at a minimum how much extra space you need for the tables.
It's not completely accurate, you will need to take indexes into account as well, but it's a fairly easy rough figure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2012 at 3:49 am
How about using DATALENGTH?
Do you think it should be more good than stats DMV?
declare @v1 varchar(10),@v2 nvarchar (10)
set @v1= '885'
set @v2= '885'
select DATALENGTH (@v1) as varchar1
select DATALENGTH (@v2) as nvarchar1
select len (@v1) as varchar1
select len (@v2) as nvarchar1
April 25, 2012 at 8:11 am
All great information ...
Just do not forget about any indexes that have varchar columns as well !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 25, 2012 at 11:06 pm
Yea.. Indexes is something my next problem is now..
There are few columns that are part of index, how can i calculate (even approx) space for them?
April 26, 2012 at 3:31 am
If you use this method, you can calculate nonclustered indexes as well, much the same way.
GilaMonster (4/24/2012)
A quick way to do this might be to calculate the average row length (via sys.dm_db_index_physical_stats) and subtract the length of any fixed lenght columns within the tables (like int, datetime, decimal, etc). That will give you the average length of varchar columns per table. You can roughly double that to get the average length of nvarchar columns. That'll tell you at a minimum how much extra space you need for the tables.It's not completely accurate, you will need to take indexes into account as well, but it's a fairly easy rough figure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply