1

Situation is this...our data warehouse doesn't need nvarchar and is built using all non-unicode datatypes. But all new source systems are all coming in with unicode (utf8 Oracle or nvarchar in sqlserver).

If we leave the source nvarchar to our staging then we get implicit datatype conversions if we have to join or compare to our EDW during loads. Plus it just takes up excess storage that is unnecessary. Sounds great to stay true the source and not risk losing characters in conversion but the reality is we need to optimize storage and our load processes and that means not using unicode anytime we can avoid it.

Thoughts?

  • 1
    Why not embrace the unicode? Storage is cheap. – Erik Darling Dec 16 '17 at 4:30
  • Data integrity versus the size of Unicode. I will take data integrity every time. – paparazzo Dec 16 '17 at 14:51
  • First - if the data coming in from other sources is in Unicode, then you can't be certain you don't need Unicode. That said: If the problem is the data coming from new data sources, can the problem be fixed by changing the export process from those sources, to export non-Unicode data? – RDFozz Dec 18 '17 at 21:31
  • Actually storage is not cheap - its incredibly expensive in large quantities especially with SSDs. Doubling the cost of multimillion $ with of storage is only cheap if you're Trump. And every byte of extra storage means more memory and more I/O and more CPU = worse performance. – Saxman Jan 11 '18 at 20:41
  • Yes the data can be changed from unicode and that is in fact what I am considering. My approach was that like sp_BlitzErik but now I'm rethinking that due to the incredible negative consequences I'm now facing with implicit datatype conversions since some sources are unicode and some not and the excess storage is killing us not in storage cost but in performance pulling out of monster unicode tables with no added benefit other than the concept of keeping perfect value integrity. Btw...converting results in "?" for unicode characters in the data if there are any. – Saxman Jan 12 '18 at 22:34
2

In SQL Server with Table and Index Compression (Row, Page or Columnstore) NVarchar columns don't use two bytes per character. They use Unicode Compression.

Here's an example:

use master 
go
drop database TestUnicodeCompression
go
create database TestUnicodeCompression

go
use TestUnicodeCompression
drop table if exists A
drop table if exists B
go
create table A(id int identity, a nvarchar(200) default cast(newid() as nvarchar(200) ))

create table B(id int identity, a varchar(200) default cast(newid() as varchar(200)))

go

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.objects o, sys.columns c, sys.columns c2
)
insert into A(A) select cast(newid() as varchar(200))
from q;

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.objects o, sys.columns c, sys.columns c2
)
insert into B(A) select cast(newid() as varchar(200))
from q;

go
alter table A rebuild with (data_compression=none)
alter table B rebuild with (data_compression=none)
go

select 'before compression' state,  
       cast( object_name(object_id) as varchar(10)) table_name, 
       used_page_count 
from sys.dm_db_partition_stats
where object_id in (object_id('A'), object_id('B'))

go

alter table A rebuild with (data_compression=page)
alter table B rebuild with (data_compression=page)

go

select 'after compression' state, 
        cast( object_name(object_id) as varchar(10)) table_name, 
        used_page_count 
from sys.dm_db_partition_stats
where object_id in (object_id('A'), object_id('B'))

outputs

(1000000 rows affected)

(1000000 rows affected)
state              table_name used_page_count
------------------ ---------- --------------------
before compression A          11114
before compression B          6581

(2 rows affected)

state             table_name used_page_count
----------------- ---------- --------------------
after compression A          6210
after compression B          6062

(2 rows affected)
  • Sounds like its saying it just does compression but still takes 2 bytes regardless instead of just 1 byte right? – Saxman Jan 11 '18 at 20:39
  • Nope. One byte per character for several commonly-used languages, per the doc link. – David Browne - Microsoft Jan 11 '18 at 20:48
  • I tested this with 2 duplcicate tables with only difference using varchar verses nvarchar. I compressed both using page compression. Result after compression was still the same ratio...in other words, compression did not eliminate the extra space taken up by using nvarchar. – Saxman Feb 8 '18 at 20:22
  • @Saxman added an example demonstrating the higher compression ratio for NVarchar data. – David Browne - Microsoft Feb 8 '18 at 20:51
  • Thanks David, I ran above and tried throwing in some random characters and same result as you. I am re-doing my previous test which uses the Dynamics CRM Contact entity with over 8 million contacts and will provide results. – Saxman Feb 8 '18 at 21:54
1

My solution was 1) as RDFozz suggested, convert from unicode to non-unicode in the extract, 2) use Task Factory Advanced Derived Column component to change the datatype (since a simple datatype translation works in that component but does not with the SSIS provided data conversion or the derived column components - they both blow when finding non-ascii characters in data). This is all then generated using BIML that throws all nchar/nvarchar, etc through the TF component.

The end result is significant savings in both storage and prevents current performance problems experienced with sources where we didn't do this.

My advice...it's all well and good in theory to keep unicode datatypes. But the reality is it comes at a significant cost and you should be able to justify it. Be sure the data integrity purity is needed or you are incurring significant unnecessary overhead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.