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,

How to make Read-only VIEWS in SQL-Server

We all know that Views in SQL Server are editable. Unlike Oracle, there is no direct option available to make a view read-only (as per my knowledge, correct me if I am wrong). So i was just looking into some options to make Views read-only and users should not be able to update the views.

One solution to this is, only assign SELECT permission on view to the specified users/role. With that said user/role will only be able to SELECT from the view.

But if you dont want to mess around with security and dont want anyone to update views at all then there is one "cheat" solution. While creating the View use Derived fields even if you can directly get the fields from table.

Following is one VIEW that I created where I have two derived columns and one normal column mapped from table.

CREATE VIEW [dbo].[TestReadOnlyView]
AS
SELECT case when PriorityTypeID IS NULL THEN NULL
ELSE PriorityTypeID end as PriorityTypeID
,case when PriorityTypeDesc IS NULL THEN NULL
ELSE PriorityTypeDesc end as PriorityTypeDesc
,SortOrder
FROM dbo.PriorityTypes


Now with this VIEW, you can update SortOrder column but you wont be able to update other two columns.
Try out yourself.

I am still looking into this, so if anyone is having any other solution or opinion then do share with me.

UserDefined Function to Trim Zeros from decimal value

Most of the times for reporting purpose we want dont want to show extra zeros that comes along with decimal values. To my surprise SQL Server doesnt provide any function which will help trimming extra useless zeros from decimal. After searching a lot about this I found that if you want you have to write your own function which will serve this purpose for you.
And I found one scalar function which you can call in your procedures to trim extra zeros from a decimal value. Pass the decimal value, variable or column you want to remove the extra zeros from to the function, and it returns a trimmed string:


CREATE FUNCTION [dbo].[fnTrimZeros]
(
@decValue decimal(7,5)
)
RETURNS varchar(9)
AS
BEGIN
DECLARE @txtTrimmed varchar(9), @chrTest varchar(1)

SELECT @txtTrimmed = Convert(varchar(8),@decValue)
SELECT @chrTest = Substring(@txtTrimmed,Len(@txtTrimmed),1)
WHILE @chrTest = '0'
BEGIN
SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))
SELECT @chrTest = substring(@txtTrimmed,len(@txtTrimmed),1)
END
IF @chrTest = '.' -- remove unnecessary decimal point
SELECT @txtTrimmed = substring(@txtTrimmed,0,len(@txtTrimmed))

RETURN @txtTrimmed + '%' -- optional % sign formatting

END



Call the function by:

SELECT dbo.fnTrimZeros(@DecimalVariable) AS TrimmedDecimal

-or-

SELECT dbo.fnTrimZeros(DecimalColumn) AS TrimmedDecimal FROM dbo.DecimalTable

You can adjust the precision of the decimals, or length of the varchars to suit your needs. Also, if you convert the trimmed string back to a decimal, you're probably just going to add extra zeros back on since you can't predict the precision of the decimal on the fly...
Ideally these kind of string manipulation should be done on the presentation layer (when possible, 'cause sometimes it's not!) instead of doing this in back end.

Rarely Used but Useful SQL Function "NULLIF"

We all are very well aware and familiar with the function IFNULL, which is used to check if the value is NULL and provide replacement value for NULL.

Recently I came across one more useful SQL function "NULLIF". This works exactly opposite to IFNULL. In here you can find for some specific value and can replace it with NULL.
Now you will wonder why we may need to replace some useful value with NULL. Well I have one situation for you. Suppose you are doing some mathematical operations and you have to account for division by zero. Instead of writing multiple checks and IF clauses, NULLIF function comes in handy.

One of the simplest solutions is to do something like this:

SELECT @v1 / NULLIF( @v2, 0)

This will return NULL if division was invalid (Division by zero) or else
it will give the proper result.

If you prefer the result of an invalid division to be zero, you can add
a COALESCE:

SELECT COALESCE( @v1 / NULLIF(@v2,0), 0)


Hope that you will also find this function useful. Feel free to put your comments.

Potential of 'SixthSense' Technology by Pranav Mistry

Guys, I was amazed to see an Indian who came up with such an important technology that will actually change our physical world. The guy I am walking about is Pranav Mistry from MIT India and I must say that he has got amazing vision.
This video is a must watch for every Techy who want to make a difference.

At TEDIndia, Pranav Mistry demos several tools that help the physical world interact with the world of data -- including a deep look at his SixthSense device and a new, paradigm-shifting paper "laptop".
In an onstage Q&A, Mistry says he'll open-source the software behind SixthSense, to open its possibilities to all.

http://economictimes.indiatimes.com/tv/TED-India-Pranav-Mistry/videoshow_ted/5231080.cms


Hope you like it..!!!

Using PRINT within a function (SQL Server)

Have you ever tried using PRINT function inside another user defined function in SQL Server?
Well i recently tried doing this and to my surprise i found that we can not use PRINT function inside another user defined function.
I tried a lot to find the reason for this, but no luck. I am not sure why SQL Server team always comes with such shortcomings.

We often use PRINT function as a mean for debugging our Database scripts or procedures. so now question is how can we debug user defined functions without PRINT function.
Well guys if you are expecting any other alternate solution from me then i wont disappoint you. The only option to make sure that your user defined function is working properly is to test the script before making it a function. Meaning you test your script first with all possible scenarios and inputs, then you can make it as function.

Thats it guys, this is the only solution to this for now.
If any of you have any other better solution then do let everyone know here. Also don't forget to write in your valuable comments.

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.