Thursday, May 22, 2014

Difference between Char, Varchar, and nVarchar data type in SQL Server



In real world everyone has different need and requirement, why we use the same data type to store different types of value. What is the actual difference between Char, Varchar and nVarchar data type.

Char DataType
Char datatype which is used to store the fixed length, non-unicode characters data. Suppose if we declared char (50) it will allocates memory for 50 characters. Once we declare char (50) and if we enter fewer than the number of characters defined (i.e. 6), the remaining length will be space filled to the right. it means other 44 characters of memory will be wasted.

Now we look in the query, How will it effect.

 

Output of this Select Statement:


Use this data type when the column data is to be of fixed length, which tends to be the case
for customer IDs and bank account IDs.

Nchar DataType
Nchar type is exactly similar to char. But hold character in Unicode format rather than ANSI. The Unicode format has a larger character set range than ANSI. Unicode datatype take exact double memory space in sql server. So use the nchar when you want to store the Unicode value.

varchar DataType

Varchar datatype, which is used to store the alphanumeric values, just like char datatype. If both are similar than what is the difference between char and Varchar? What is the need to create the new datatype? Later in this article we will discuss this. The maximum size of a Varchar column is 8,000 characters. However, if you define the column with no size—that is, Varchar () — then the length will default to 1.


Difference between the char and Varchar datatype

Char belongs to fixed length characters datatype, Varchar belongs to variable length characters datatype. If you define a column to be 20 characters long, but If you enter fewer than the number of characters defined like 10 characters then it will consume only 10 characters memory. It will not consume the defined memory.



Now we look in the query:





Output of the select statement:


We can see in above image the output of the select statement, the ZipCodelength value are varying according to data in zipCode field.


So we should use the Char for the fixed length data field and Varchar for the variable length data field.



Nvarchar DataType

Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

Difference between Varchar and Nvarchar DataType
Varchar(n)
Nvarchar(n)
1.    Non-Unicode Variable Length character data type.



Example: Declare @FirstName As Varchar(20) = ‘Developer’

Select @FirstName



2.    It takes 1 byte per character.



Example: Declare @FirstName As Varchar(20) = ‘Developer’

Select @FirstName AS FirstName,

DATALENGTH(@FirstName) As Length



Result:

FirstName Length

Developer 9


3.    Can store maximum 8000 Non-Unicode characters.

4.    If the value of n is not specified at the time of variable declaration or column definition then it is considered as 1.

Example: Declare @FirstName As Varchar =’Developer’

SELECT @firstName FirstName,

DATALENGTH(@firstName) Length



Result:

FirstName Length

D                1

5.     If n is not specified in while using Cast/Convert   functions, then it is considered as 30.
     Example:
     Declare @firstName Varchar(50)=
‘Unicode character sets hold up to 65,536’
   
Select CAST(@firstName As Varchar) FirstName,
DATALENGTH(CAST(@firstName As varchar)) Length 
      Result:
FirstName                                Length
Unicode character sets hold up  30
1.    Unicode Variable Length character data type. Nvarchar  can store both the Unicode and non-Unicode (i.e) Chinese, Japanese, Korean etc) character.



Example: Declare @FirstName As Nvarchar(20) = ‘Developer’

Select @FirstName

2.    It takes exactly double bytes per character as compare to Varchar. It takes 2 bytes per Unicode/Non-Unicode character.



Example: Declare @FirstName As Nvarchar(20)= ‘Developer

Select @FirstName AS FirstName,

DATALENGTH(@FirstName) AS Length



Result:

FirstName Length

Developer 18

3.    Can store maximum 4000 Unicode/Non-Unicode characters.

4.    If the value of n is not specified at the time of variable declaration or column definition then it is considered as 1.

Example: Declare @FirstName As Nvarchar =’Developer’

SELECT @firstName FirstName,

DATALENGTH(@firstName) Length



Result:

FirstName Length

D                2

5.    If n is not specified in while using Cast/Convert     functions, then it is considered as 30.
      Example:
       Declare @firstName Nvarchar(50)=
 ‘Unicode character sets hold up to 65,536’
   
 Select CAST(@firstName As Nvarchar) FirstName,
 DATALENGTH(CAST(@firstName As Nvarchar)) Length
  
 Result:
 FirstName                                 Length
 Unicode character sets hold up   60 

    

So if you are not using other languages then it’s better to use varchar datatype instead of nvarchar.  

Tuesday, May 20, 2014

Welcoming Microsoft world onto my blog...

For most of my IT life, I have been thoroughly disconnected with the Microsoft world of enterprise products. One of the reasons could be the fact that I was introduced into the IT through the corridors of Java & then Unix.. Subsequently more things came on the way, but I never got around to diving deep in the MS world.

Sometime back, I did try and explored SQL server 2008 R2 and associated ETL/Analytics pieces, but it didnt excite me as much.

Now, I have an opportunity to learn and share the MS wisdom through a dear friend Sachin Dubey.  He's going to be putting up content on to this blog. He would be contributing content geared towards Microsoft products, e.g. SQL Server, MS-BI, Dot-Net etc.  With significant experience working around MS BI space, Sachin is a self built knowledge repository. I hope we all learn with him.

Sunday, March 16, 2014

shuklaam vardhanam vishnu


I had to prepare my son for his shloka competition, but it was so hard to find it..
Finally, I managed to find the right sanskrit lyrics, hopefully, they are correct.

शुक्लां वर्धनम् विष्णुं  शशिवर्णं चतुर्भुजं ।
प्रसन्नवदनम् ध्यायेत् सर्वविघ्नोपशान्तये ॥ 
in roman,

Shuklaam Vardhanam Vishnum Shashivarnam chaturbhujam
prasannavadanam dhyaayet sarvavighnopashaantaye

Hope it helps

Saturday, February 8, 2014

Programmatic interaction with Facebook data

The last time I interacted with Facebook using a program (2011), it was fairly straightforward, and perhaps it was too simple, it was abused.  As a result, in my recent experiments, I come to find out that, they have made it so complicated (my feeling, no offence to Facebook here), that its quite hard, if not impossible to do genuine development work using Facebook API, let alone achieve something sinister.

I am a self-anointed tech junky, and therefore no one should be surprised that i took up this challenge couple of hours back to build a brand new routine to get data in/out from/to facebook using a popular and open source Data integration tool, called Pentaho 

There were two triggers, one, I had not done some core debugging based research using something new. Second, a friend of mine is building this ecommerce site, www.freemall.in for which the marketing efforts would need a simpler interface to push details to the site, instead of some human going up there and doing all.  So, this programming interface becomes the first step of the future plugin for his ecommerce venture.

As I already indicated, programming with facebook's current API is a relatively hard task, at least for couple of iterations, before you get the hang of it.  So, I started with the tutorial at pentaho's site - http://www.pentahoevalcenter.com/data-integration/advanced-data-integration/facebook/ which turns out to be based on the old Facebook API and prompted me to look elsewhere for the right way of working.

Then comes the big thing - the oAuth authentication protocol employed by Facebook, get a token of this type, then of this type. go to this site, achieve this, copy this and take it there.. Its all so much that I had to go back to google and see if there is something simpler.. and yes, god bless google, there was someone like me who had gone to all the trouble and then of documenting it... God bless his soul for this - 
http://stackoverflow.com/questions/12168452/long-lasting-fb-access-token-for-server-to-pull-fb-page-info

After this, I went ahead with the pentaho tutorial, which did work out nice and fine for me.

I was able to post couple of messages to my test page using the application that i built for this test case.

hope this collection of links at a single place helps others.



Tuesday, February 4, 2014

National Pension System (NPS) - India

The National Pension System (NPS) in India has been pushed for some time now. Government has been trying to increase the scope of the scheme, and hardly been successful at that.

One of the primary reasons has been the low cost structure for the Pension fund managers. While a regular mutual fund charges around 1.5% for the fund management effort, NPS fund managers were charging somewhere between 0.0009% to 0.25%.  At such low costs, it would have made little sense for anybody to be in business.

However, some of the fund managers persisted and are in relatively better standing today.  Some of them who did not continue (e.g. IDFC) continue to observe from a distance.

Over the last few years, returns from the NPS fund managers have been surpassing the general market returns, mostly in all categories.

Also, recently, govt regulations allowed hiking the fund management charges to 0.25%  in addition to allowing partial withdrawals from the NPS corpus.  Both of these measures have flamed the interest in NPS.  By and large, people are now taking notice of the possibility of considering NPS as a possible investment vehicle. 

Slowly, but surely, many private sector companies are also adopting the NPS route for their employees.  Beyond the long term investment option, NPS offers an instant benefit in terms of tax benefit above and beyond 80C.  10% of your Basic Salary + DA is the cap for Tax benefit under NPS. 

Therefore effectively, who falls in the 30% tax bracket, stands to shave of a substantial amount from his taxable income.  Furthermore, the returns on this additional investment are not as measly as the EPF (approx 8-9%), but much more controlled (you can chose ur fund options) and market linked.

All these measures are slowly resurrecting NPS as a possible investment vehicle.

Sunday, February 2, 2014

Proud - Cloudera Certified Developer for Apache Hadoop

I have not been a believer of certifications, that should be clear from the fact that even though I have been working with Informatica since 2002, I never tried their certification till 2011. And, same about Oracle, whose exam I have not yet attempted/planned/thought over. I used to think that the knowledge level will prevail anyway, whether or not an authority stamps on it.

However, I believe, I am changing, to a certain extent so to say.  It seems that I have come to accept the certifications' worth, and therefore, after the training provided by Cloudera, I picked up the opportunity and went through with the rigor of examination.

Fortunately, I came through.  And, as much as I try not to showcase it, its a great feeling.  Somehow, the knowledge is vindicated, that yes, this guy knows something about hadoop and you better listen to him, :) . Funny that one has to put a badge out there to be heard.

Well, all said and done, the certification is done, courtesy the employer, who sponsored the training and of course the examination coupon that came along.  I would like to thank our trainer from cloudera, Amandeep Khurana for his depth and breadth around all things hadoop.  As much as I knew about hadoop before going in the training, those 2.5 days added precious layers to my knowledge.  Thanks Amandeep.