What is "nvarchar" ?

Most of us are familiar with the use of the SQL datatype nvarchar.
Yes, nvarchar comes with unicode support & thus we use it when we want support for different languages.

But have you ever wondered, why it is called as nvarchar ? If yes then here is the answer..
As per ANSCI standards, nvarchar stands for "National character varying". so while declaring any column/variable in SQL of type nvarchar you can also specify it as follows.

create table tEmployeeMaster(
EmployeeName national character varying(64)
)


One more important thing to note about this database is, whenever you try to insert some string value into nvarchar column/variable, SQL server needs first cast it from varchar to nvarchar and then stores it.

Insert Into tEmployeeMaster values('Mirza Ateeq Baig')

Now here there is some casting overhead for SQL server to cast string to nvarchar.
There is one way to avoid this casting overhead. Above insert statement can also be written as follows.

Insert Into tEmployeeMaster values(N'Mirza Ateeq Baig')

The prefix N before string indicates that the string should be treated as nvarchar. So now SQL server knows that this is nvarchar, thus no casting is required in this case.

Hope that this is useful for some of you. cheers,

Numeric DataType & Storage, Choose wisely..!!!

SQL Server provides two datatypes decimal and numeric that have fixed precision and scale. numeric datatype is primitive datatype which is equivalent to decimal and it is kept merely for backward compatibility. Most of the time developer use these datatypes without keeping in mind the storage aspect, which i think is very important and affects performance of the reports.

Lets briefly discuss about this datatype and its storage aspect.

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Table below specifies storage structure that SQL Server uses based on the precision selected by user.

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

By default SQL Server uses 18,3 as precision and scale for these datatypes. Most of the time developers just these default precision and dont really think of what actual is the requirement for them. As a good practice always choose precision manually as per your need.

Hope you find this post useful. Feel free to provide your comments.