Archive for 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

Difference between primary key and unique key

Hi Friends, in this in this session we are going to learn the differences between primary key and unique key.

This is one of the most frequently asked sql server interview questions.
Before we discuss the difference between primary key and unique key, let’s see what a primary key is and what a unique key is.

Primary Key:
Primary key is a key which uniquely identifies each row/record in the table.

This implies if you have a primary key on a table, you can identify each and every row in that table uniquely using primary key.


Unique Key:
Unique key constraint enforces the uniqueness on the columns in which it is defined.

That is Unique key will make sure only unique values are allowed in the columns in which it is defined.

Similarities:
Both Primary key and unique Key enforces uniqueness on the columns on which they are defined.
  •          Primary key and unique Key columns allow only unique values.
  •          Primary key and unique Key columns will avoid duplicates
  •          Both Primary key and unique Key columns uniquely identifies each row/record in the table


Differences:
PRIMARY KEY
UNIQUE KEY
Primary Key Does not allow duplicates
Unique key allows only one NULL
A table can have only 1 Primary Key
A table can have multiple Unique Keys
By default Primary Key creates a Unique Clustered Index on the table
By default Unique Key creates a Unique Non-Clustered Index on the table


EXAMPLES:
CREATETABLE #CUSTOMER
(
ID          INT               PRIMARYKEY  ,
NAME        VARCHAR(25)                   ,
PHONE       INT               UNIQUE      ,
SSN         INT               UNIQUE           

)

Posted in | Leave a comment

What is the short cut for opening sql server from command prompt?

Command for opening Sql server management studio from run is:

Go to Start --> Run --> Type " SQLWB " --> Click "OK"


Posted in | 2 Comments