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.  

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.

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.

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.

Removing ports from an existing mapping - Do's and Dont's

Recently, a colleague called in for an issue that he was facing with an Informatica mapping. Let me recreate that situation here -

There is a mapping that get n ports from a source and loads m ports to a target. Standard stuff, nothing special or fishy around there. There comes a change request that says, such and such x number of columns are not required in the target, and since there would be a sure performance penalty for carrying through extra data (however small), the mapping should be changed to remove those ports from the pipeline.

What this gentleman did was to remove the connections for those ports from the source qualifier onwards. That saved him from changing and re-importing the source definition.

However, the mapping execution failed, complaining about a certain error situation. Thats when I got the call :)

whether I could discuss and fix it is something else, but what was the reason of the error - ?  

The very fact that if you are bringing in certain ports in the source qualifier, you HAVE to take them forward.  Thats a rule from informatica's side. Which means, if you are pulling up n ports from the source, you HAVE to expose all those n ports going away from the source qualifier. Or, putting it differently, in whichever way you create a set of ports in your source qualifier, all of them have to be consumed by some transformation object. Not a single one can be left unconnected, on the input side or the output side. Simple.

That was the reason, and the solution was fairly simple, to reduce his effort, either remove the ports from source qualifier as well, or just carry them forward to one transformation. and then drop them onwards.

Having said that, the very reason for which this whole change was initiated, the performance gain that would come through by not having the extra ports being carried forward, would come only when you remove the un-wanted ports from the source itself.

Moving an ecommerce site to Amazon Web Services

This is an interesting one.. There is an e-commerce startup, run by a dear friend.  One fine day we were chatting around and he mentioned challenges with his business. Challenges included performance, scaling and cost issues about his hosting services provider.

My instant reaction was, why dont you move to aws.. and it clicked just like that.. We decided that we'd start with the free tier, with his mysql server on amazon RDS and rest of app server functionality on the micro instance. All of which is within purview of the free tier.  Using Cloudfront we'd localize the static content, to add to the performance of content delivery. Not now, but eventually, we'd start using ELB - the load balancer for distributing load across the instances we would have.

With something like aws, planning all that is really really that straightforward.  You just have to be a bit more technically oriented to think in terms of these things and you are set for good.

We are already thinking about configuring autoscaling for the services, so that running on the micro instance doesnt become a bottleneck for the customers. With that we'd hope to achieve dynamic automatic scaling up and down the infrastructure.

We have already taken the first steps, and I am helping his venture migrate to AWS, one of my dream jobs, to be able to consult around migration / integration of Traditional systems to cloud based systems.

Teradata TPump vs MultiLoad

Was doing some research around Teradata's load utilities and found some useful info on Teradata forums 

MultiLoad
* Loads data to TeraData from a Mainframe or flat file
* Multiple tables can be loaded in the same MultiLoad.
* Up to 20 INSERTS, UPDATES, or DELETES on upt o 5 tables.
* UPSERT is supported
* There can be NO - Unique Secondary Indexes (USI), Triggers, Referential Integrity or Join Indexes.
* Duplicate rows are allowed
* Each Import task can do multiple INSERT, UPDATE and DELETE functions.
* Some Secondary Indexes (NUSI) and RI are allowed
* Locks at the table level
* Block Level transferring of Data.

TPump

* Loads data to TeraData from Mainframe or flat file
* Processes INSERTS, UPDATES or DELETES
* Tables are usually populated.
* Can have Secondary Indexes and RI on tables.
* Does not support MULTI-SET tables.
* Locks at the row hash level
* It uses time based checkpoints not count based.If you are using an OLTP the TPUMPs trickle or continuous loads to populated tables. It acts like a water faucet (tap), that is it can be turned up and load millions of rows or at peak periods tuned down to trickle ffed into the tables.Generally MultiLoad performs better for large bulk loads because of the 64k block loading of data and TPump works better on Low volume changes.

General Writeup 
Multiload performs better in almost all cases.

The only time TPump's performance approaches Multiload is when you are updating a very small percentage of the rows. Tpump could probably beat Multiload if you had a very small number of rows in that Multiload has to log on to one session per AMP, whereas Tpump sessions can be controlled. So, with a very few number of rows, the overhead of Multiload may make it slower than TPump.

Multiload performs better because it sends the data from the host to the DBMS more efficiently (in block mode; with no embedded commands).  Tpump sends the data as part of a statement (exec macro statement). Tpump allows you to pack statements together to gain more efficiency, but it's still doesn't send the data as efficiently as Multiload.

The second reason that Multiload is faster is that it then applies the updates in block mode. So, if you have multiple updates destined for the same data block, they will get applied with one physical write of the data block. Tpump will need to write the data block once for each update.

The advantage that Tpump has over Multiload is that it locks only the rows (actually row hashes) that it's updating whereas Multiload locks the entire table for write while it's updating the data.  Because of this, you can run multiple Tpumps against the same table at the same time, whereas you can only run one Multiload against a table at a time.

Since Multiload takes a write lock on the table it's updating during it's APPLY phase (the phase where it actually updates the table), you can only access the table with an access lock (i.e. dirty read). With Tpump, you could access individual rows with a regular read lock.  If you tried to do a query that required a read lock on the table during a Tpump, the read lock would end up blocking the Tpump updates until the query finished, so it's still not a great idea to try to run queries requiring a read lock on the table during a Tpump.

Another advantage that Tpump has over Multiload is that there is no Tpump code within the DBMS, so new features are automatically enabled with Tpump, whereas there are a number of features that you can't use with Multiload (USI's, referential integrity, join indices, etc.).

In summary, 

Multiload is better for bulk updating especially if done in traditional batch mode.
Tpump is usually better for continuous updating of a table.

Finding Informatica domain name

Recently I came across a situation where the customer people had provided us with informatica server hostname, but not the domain name, nor the port for domain.

In such a case, we lost quite some time figuring out how to go through the domain configuration. That's when I started thinking about the alternates for finding the domain name information from the system (assuming different access levels)

If the repository database access is available, i.e. you can access the informatica repository database, you can use the following query to get the domain name out.

select pos_name
from PO_DOMAINSERVICECONFIG

 This sql will need to be run in the schema where the domain repository has been created.

On another approach, if the database access is not there, and the informatica server access is available, another file, domains.infa in the $PM_HOME equivalent directory will be able to provide information on domain name/port etc..

About the port

Though the installations process allows customization of the ports for domain, many installations keep the default as is.  In any case, a simple telnet to the host on the suspicious port will confirm whether the port is open or not.

In my example situation, it turned out to be the default 6005.