COALESCE Function In Sql Server

During Development we have use more time Colesce method If someone have home address or office address suppose if you dispaly available first record means you can use coalesce method or concatinate columns we use this coding

I have Below Table :

Insert Some Data in it :

Now the basic use of Colesce:
You can see the Id 2 hasnot OfficePhoneNo and id 5 has only MobileNo.
Colesce function can display available first record from one of the column.

It's Query Like :

select name ,coalesce(OfficePhoneNo,HomePhoneNo,MobileNo)
PhoneNo from EmpDetails

and Result will be :

Now I can show you another example of it.

We can also use coalesce function for Combining all column into single column.

CREATE TABLE Emp(FName VARCHAR(25))

INSERT INTO Emp VALUES('PalPatel')
INSERT INTO Emp VALUES('DishaAgola')
INSERT INTO Emp VALUES('Bansi')
INSERT INTO Emp VALUES('Nirav')


DECLARE @nam NVARCHAR(1024)

SELECT @nam=COALESCE(@nam+',', '')+ FName FROM Emp

SELECT Emp= @nam

Output :

PalPatel,DishaAgola,Bansi,Nirav

Thats it .....

varchar Vs. nvarchar

char, nchar, varchar, and nvarchar each take a length parameter which the database engine can use to optimize storage.

UTF-16 (when sticking to the BMP) has a simple two-to-one relationship between bytes of storage and characters, which the database can take advantage of. 

The amount of storage required for a UTF-8 string of N characters is not as clear, and could result in wasted space, or unexpectedly truncated strings.

varchar does not store ASCII, it stores an 8-bit encoding.

nvarchar and varchar do not "function identically" since varchar does not function at all in lots of scenarios. 

varchar contain max size 8000 while nvarchar size is 4000. 

nvarchar may take up twice as much space, but varchar is twice as slow, since it requires string conversions for every read and write (on those occasions when it actually works).