Archive for December 2013

SQL SERVER Tutorial for the Beginners

Chapter 1:  Introduction

                        Introduction to RDBMS
                        Introduction to SQL Server
                        SQL Server Installation
                        Getting Started with SQL Server
                        System Databases in SQL Server

Chapter 2:  SQL Server Concepts
                       
                        Database Creation
                        Data types in SQL Server
                        Table Creation using SQL Commands
                        Table Creation using UI
                        Important points about Tables in SQL Server
                        Storing and Retrieving data in SQL Server 

Chapter 3: Introduction to SQL Server Commands

                        Introduction to SQL Server Commands
                        DDL Commands in SQL Server
                                        1.   CREATE
                                        2.   ALTER
                                        3.   DROP
                                        4.   TRUNCATE
                        DML Commands in SQL Server
                                        1.    INSERT  ( Part 1  , Part 2)
                                        2.    UPDATE
                                        3.    SELECT
                                        4.    DELETE
                        DCL Commands in SQL Server
                        TCL Commands in SQL Server
                        Difference Between DELETE and TRUNCATE

 Chapter 4: Simple Commands in SQL SERVER
                       
                        SELECT
                        DISTINCT
                        TOP
                        WHERE
                        GROUP BY
                        HAVING
                        ORDER BY
                        IN
                        BETWEEN
                        Difference Between Having and Where
                        Group By Errors

 Chapter 5: Aggregate Functions in SQL SERVER

                        COUNT
                        SUM
                        MAX
                        MIN
                        AVG

Chapter 6: Joins in SQL SERVER

                        INNER JOIN
                        LEFT OUTER JOIN
                        RIGHT OUTER JOIN
                        FULL OUTER JOIN
                        CROSS JOIN (or) CARTESIAN PRODUCT
                        SELF JOIN

Chapter 7: Stored Procedures in SQL SERVER


                        Introduction to stored procedures
                        Advantages and Disadvantages of Stored Procedures
                        Why stored procedures are faster than independent queries?
                        SP uses in Cross applications
                        SP without input and output parameters
                        SP with input parameters and without output parameters
                        Stored procedures with input and output parameters
                        Checks performed while compiling stored procedures
                        System Stored Procedures

Miscellaneous :

                        Templates for Creating Objects
                        Temporary Tables
                        @@FUNCTIONS
                        System Stored Procedures
                        Examples
                        White Papers on SQL SERVER




Posted in | Comments Off

Differences between ISNULL and NULLIF

Hi friends, in this article I am gonna discuss the differences between ISNULL and NULLI. Both ISNULL and NULLIF are built in system functions in sql server. Lot of people get confused between these 2 system functions.

Lets discuss about each of these system functions separately so that you will get better idea.

Here is a video which clearly explains  Differences between ISNULL and NULLIF
ISNULL:

ISNUL is a system function which replaces NULL with the specified replacement value.

The sytax for ISNULL function is

ISNULL (<check_expression> , <replacement_value> ).

Which implies isnull function accepts 2 input parameters.

1st one is an expression and 2nd paramter is a replacement value.

The functionalityof ISNULL is :

1. 1st it will Evaluate the expression which is in parameter 1.
2. it will compare the evaluated expression with null. if the paramter 1 = NULL ?
3. If parameter1 is equal to null i.e., if parameter 1 is null, then isnull function returns replacement value. if parameter1 <> NULL, then ISNULL function returns parameter1

Lets see this with an example so that it will be more clear.

CODE
       DECLARE @PARAM1 INT, @PARAM2 INT
       SELECT @PARAM1 =10 , @PARAM2= 20
       SELECT ISNULL(@PARAM1 ,@PARAM2)

OUTPUT
       10

In the above example Parameter1 IS NOT  NULL , so ISNULL Function returned Parameter1 



CODE
       DECLARE @PARAM1 INT, @PARAM2 INT
       SELECT @PARAM1 =NULL, @PARAM2= 20
       SELECT ISNULL(@PARAM1 ,@PARAM2)

OUTPUT
       20

In the above example Parameter1 IS NULL , so ISNULL Function returned Parameter2 

To summarize is null functionality.

  • Parameter1 = NULL , retuns  Parameter2
  • Parameter1 <> NULL , retuns  Parameter1


NULLIF:

NULLIF is a system function which returns a null value if the two specified expressions are equal.

The sytax for NULLIF function is

NULLIF ( <Expression1> , <Expression2> )

Which implies NULLIF function also accepts 2 input parameters.

The functionality of NULLIF is :

1. 1st it will Evaluate both the expression which are in parameter 1 and parameter 2.
2. next it will compare both the evaluated expressions with null.
3. If both the expressions are equal, the nullif functions returns NULL. 
4. If both the expressions are not equal, the nullif functions returns 1st expression.

Lets see this with an example so that it will be more clear.


CODE
       DECLARE @PARAM1 INT, @PARAM2 INT
       SELECT @PARAM1 =10, @PARAM2= 20
       SELECT NULLIF(@PARAM1 ,@PARAM2)

OUTPUT
       10

In the above example Paramter1 is not equal to  Paramter2, so ISNULL Function returned Paramter1

CODE
       DECLARE @PARAM1 INT, @PARAM2 INT
       SELECT @PARAM1 =10, @PARAM2= 10
       SELECT NULLIF(@PARAM1 ,@PARAM2)

OUTPUT
       NULL

In the above example Parameter1 is equal to  Parameter2, so ISNULL Function returned NULL.

To summarize nullif functionality...


  • Parameter1 = Parameter2 , retuns  NULL
  • Parameter1 <> Parameter2 , retuns  Parameter1

Lets conclude the discussion with the similarities and differences between ISNULL and NULLIF

Similarities:


  • Both ISNULL and NULLIF are system functions.
  • Syntax for Both the functions are similar
  • Both  ISNULL and NULLIF functions accepts 2 input parameters.


Differences:

  • isnull function compares 1st parameter with null.where as nullif function compares 1st parameter with 2nd parameter 
  • ISNULL function returns 2nd paramter if the 1st parameter isnull. nullif function returns null is 1st parameter = 2nd paramter.
  • ISNULL function returns 1st paramter if the 1st parameter is not null. nullif function returns 1st paramter if 1st parameter <> 2nd paramter.

I hope this session is useful. Please me know your comments and valuable suggestion.

Bye , Have a good Day!!


Posted in | 1 Comment