Difference between NULLIF and ISNULL IN SQL SERVER 2005

NULLIF –

NULLIF returns NULL if both of the strings are equal else returns first string.

Syntax :
NULLIF(expression1, expression2)

e.g.
SELECT NULLIF(‘MakeFlag’, ‘MakeFlag’)AS ‘Null if Equal’
Output : NULL

SELECT NULLIF(‘FinishedGoodsFlag’, ‘MakeFlag’)AS ‘Null if Equal’ — FinishedGoodsFlag

NULLIF can be implemented using CASE statement. It will compare two parameters and return NULL if both expressions are same.
e.g.
SELECT ‘Null if Equal’ =
CASE
WHEN ‘MakeFlag’ = ‘FinishedGoodsFlag’ THEN NULL
ELSE ‘FinishedGoodsFlag’
END
Output : FinishedGoodsFlag

ISNULL() –

ISNULL is used to replace value of expression, if it comes to NULL.
Syntax :
SELECT ISNULL(NULL, expression)

e.g.
DECLARE @var varchar(50)
SELECT ISNULL(@var, ‘No value has been assigned to variable.’)
Output : No value has been assigned to variable.

ISNULL and NULLIF are exactly opposite to each other.
ISNULL returns NULL if comparison becomes successful.
On the other hand, NULLIF returns not null if comparison becomes successful.

Pawan Kumar / Gauri D
Pawankkmr@hotmail.com

CASE statement in SQL SERVER 2005

CASE statement in MS SQL Server is used to return different values in SELECT clause based on various Boolean conditions.

It is like Select – Case statement in C#.Net.

It has WHEN, THEN, ELSE clauses with End to terminate statement.

Syntax:

CASE [expression]
WHEN [Boolean expression/value] THEN [return value]
[ELSE [return value]]
END

e.g.
SELECT vchr_FirstName + ‘ ‘ + vchr_LastName AS ‘Name’,
CASE chr_IsEmployeeActive
WHEN ‘A’ THEN ‘Active’
WHEN ‘R’ THEN ‘Resigned’
END AS ‘Active’
FROM AE_EMPLOYEE

CASE statement in Order By-
e.g.
SELECT vchr_FirstName + ‘ ‘ + vchr_LastName AS ‘Name’, chr_IsEmployeeActive AS ‘Active’
FROM AE_EMPLOYEE
ORDER BY
CASE chr_IsEmployeeActive
WHEN ‘A’ THEN 2
WHEN ‘R’ THEN 1
END

We can write ‘Else’ statement if none of boolean expression is true.

e.g.
SELECT vchr_FirstName + ‘ ‘ + vchr_LastName AS ‘Name’,
CASE chr_IsEmployeeActive
WHEN ‘A’ THEN ‘Active’
WHEN ‘R’ THEN ‘Resigned’
ELSE ‘Unknown Flag’
END AS ‘Active’
FROM AE_EMPLOYEE

Pawan Kumar / Gauri D
Pawankkmr@hotmail.com

USE Statement in SQL SERVER

1.It is used to sets the current database.

2.Eg. USE databaseName

3.If we have not provided what database the tables in action queries like insert and select statements are from then the system will throw you an error.There are two options to resolve this problem either select the database from the drop down box from the SQL Server Management studio.The second option is to include a USE statement just before to your select or insert statement then the system will not throw any error and will use that database.

4.If you do not use the use statement whoever executes the script has to make sure that it is using correct database.

Pawan Kumar

Pawankkmr@hotmail.com

Transaction Isolation Levels in SQL Server

Syntax : SET TRANSACTION ISOLATION LEVEL [ READ COMMITTED / READ UNCOMMITTED / REPEATABLE READ / SERIALIZABLE ]

Note : The isolation level will affect only transactions for current connection.

1.Read Committed ( Default )

a. It is a default isolation level.
b. Problems can be prevented by this isolation level is dirty reads.
c. It cannot problems like Non repeatable reads , phantoms and lost update.
d. The locks associated with the select statement are freed as soon as statement is complete.SQL Server does not wait for the end of the transaction.
e. In case of insert , update and delete queries , locks will be there till the end of transaction just in case rollback is required.

2.Read UnCommitted

a. It will give you highest performance in terms of speed.
b. This means do not set any locks.
c. With this we can possibly recieve all types of concurrency issues.
d. This means we do not block any parallel transactions.

3.Repeatable Read

a. It prevents dirty reads.
b. It prevents non repeatable reads.
c. It holds shared lock until end of the transaction. Due to this transactions can block users access to objects and hence productivity can be reduced.

3.Serializable

1. It prevents you from all forms of concurreny issues but lost update.
2. When you set the level to Serializable action queries like insert , update and delete to the table ot tables used by your transaction must not meet the where clause of the any statement in that transaction.
3. If any other user is going to so do something that you transaction is doing that it must wait till your transaction has been completed.
4. Update process works the same for multiple users as it would if all your user did one transaction at a time.Process things one after the other in a serial manner.

Note : My personal advice is do not change isolation level unless it is required for specific purpose.

Pawan Kumar
Pawankkmr@hotmail.com

System Databases in SQL Server 2005

1.Master

a) Every SQL SERVER has a master database.
b) It contains special set of tables which keeps track of the system as a whole. It means it keep information about the new databases as well as all the objects in all databases.
c) This database is very important our system.
d) This database cannot be deleted.

2.Model

a) It is a template for creating a new database.
b) We can alter this template.
c) We need to keep many things in mind in order to alter the template Db eg.Size of the model DB cannot be smaller than 100MB.

3.MSDB

a) It the place where SQL SERVER agent stores there system tasks.
b) Eg of tasks could be schedule backups , schedule sps etc. One you create a schedule an entry is made in the msdb database.

4.TempDB

a) This DB is used to help temp data like temp variables.

b) It is also used to hold things temporaily eg. consider you have a complex query and system needs to build in between a table so it saves the same at the tempDB.

Pawan Kumar

Pawankkmr@hotmail.com