Skip to content

NVARCHAR(n) Vrs NVARCHAR(max)

These datatypes particularly NVARCHAR(n), have been with us for some time now but having seen some of the confusion around when to use any of these, I feel oblige to share and infact to also learn more from anyone out there.

 

NVARCHAR(n)

This data is used for storing a variable-length unicode string. The variable, n, – for the mathematically inclined – denotes the length of the string data and this be anything between 1 and 4,000.

 

NVARCHAR(max) (Large value type.)

The “max” in this datatype denotes a maximum storage size of 2 GB. Values stored in a nvarchar(N) are physically stored in the same way. However for the nvarchar(max) datatype , the values are treated as a TEXT value thus some additional processing is required particularly when the size exceeds 8000.

 

So when do we use any of these

The choice of which of these data types to use, firstly lies with what the business requirements are. In my own experience, the nvarchar(n) data type has been the preferred option as per requirements and more importantly, because of performance issues.

Unless there is a compelling reason to use nvarchar(max), I will strongly recommend the use of nvarcha(n) as this will meet most business requirement and also the optimum performance one stands to gain from this. For instance, creating a field just to store any amount of data only for it hold 100 or even 200 characters is not elegant.

Hope you find this article useful, please feel free to add comments and let me know what you think.

cheers

Slowing Changing Dimension (SCD) demystified – Part 3

I am wrapping up this series by finally considering the rarely used type 3 SCD.

( i.e.  in most business scenarios)

Unlike the Type 2 SCD, the type 3 SCD tracks only the current value and the original value of a dimension member.
Two attributes are normally used in designing a type 3 SCD, these are the start date and the initial value attributes.

The Start Date refers to the date the current value of the dimension member became active.
The initial value refers to the original value of the attribute.

Let examine a fictitious small company, ABC (see the data table below) that has changed locations on a number of
occasion over a period of time due to say favourable tax regimes.

From the data table, it is clear that the initial location (value) of the company is Leeds while the current location is now London and this change took place on the 15/05/2012. Thus we know what the initial value is and when the current location became active. Again we are unable to track historical changes particularly when the company changes location again.

NB: IsCurrentLocation is a boolean column that indicates which location is the current one; 0 = False, 1 = True

pkID is the primary key for the data table

Hope you found this useful and thanks for reading.

Slowing Changing Dimension (SCD) demystified – Part 2

In Part 1 of this series, we looked at SCD Type I. We would now look at the SCD Type II.  Let’s assume the table below holds historical info of call plans of a customer in the Telecom industry.

Historical information storage (As a row)

From the table above it is easy to note that customer with ID = 5 had a previous call plan/service with an ID of 3  and he’s currently on a new call plan with an ID of 5.
Even though there has been a change in the call plan/service subscribed by the customer, these changes are still kept thus making it easy to track historical changes.  Thus in SCD Type II, the old values are kept and a new row is created for each change.
Type II is the most elegant way of storing historical information of dimension attributes because it is possible to store many old records without altering the underlying table structure.

Slowing Changing Dimension (SCD) demystified – Part 1

SCD is a basically a concept that explains how historical data/values are stored within a data in a database.
There are three main scenarios namely Type I, Type II and Type III.

In this post, I will be discussing the Type I scenario and I promise to do the part 2 pretty soon. In Type I SCD, the old values in one or more attributes of the table are overwritten with new values thus the old not kept. To explain this further, let’s consider a simple employee data structure as shown below;

Figure I

BEFORE

AFTER

 

Figure I above shows the before state and the after state of the employee’s (John Lee) department information
It shows John’s department was changed from HR to IT. (NB: Click both Images to enlarge)

This change could be as a result of data being erroneously entered and from figure 1 it is clear that there is no way of tracking historical data once the data changes have been implemented. In this case, there is no way of telling what John’s former department was.

This is part 1 in my post on demystifying SCD, please stay tune for part 2 of this post.

Thank you