SQL SERVER Tips and Tricks

SQL SERVER Tutorial for the Beginners

2013-12-31T09:00:00.000-08:00

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

Differences between ISNULL and NULLIF

2013-12-11T21:43:00.000-08:00

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!!

Difference between primary key and unique key

2013-11-21T21:18:00.003-08:00

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

)

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

2013-02-05T05:36:00.003-08:00

Command for opening Sql server management studio from run is:

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

How to delete duplicate records from a table in sql server?

2012-12-04T02:44:00.000-08:00

There are lot of ways to delete duplicate records from table in sql server.

Here in this article , I will tell you 2 methods through which you can delete duplicate records from the table.

Example:

1st create a sample table.

CREATE TABLE STUDENT

(

STUDENT_ID INT,

FIRST_NAME VARCHAR(25),

LAST_NAME VARCHAR(25),

FATHE_NAME VARCHAR(25),

ADDR VARCHAR(400),

PHONE BIGINT

)

Insert data into STUDENT table :

INSERT INTO STUDENT VALUES(1,'JOE','CAMERO','AARON','19 Quaker Ridge Rd.','6023458976')

INSERT INTO STUDENT VALUES(2,'Kathleen','Kelly','Devin','7100 Athens Place Washington','4083458976')

INSERT INTO STUDENT VALUES(3,'Jeff','Willams','Jean','8400 London Place Washington','4083128400')

INSERT INTO STUDENT VALUES(4,'Dennis','Charle','Demarcus','1000 Coney Island Ave. Brooklyn NY 11230','3022151541')

INSERT INTO STUDENT VALUES(5,'Adan','Fred','Abraham','5520 Quebec Place ; Washington','3022151541')

INSERT INTO STUDENT VALUES(6,'Omarion','Balla','Oliver','3290 Hermosillo Place ;Washington','3022178541')

INSERT INTO STUDENT VALUES(7,'Omari','Richard','Deshaun','1500 Vance Ave','3022178231')

INSERT INTO STUDENT VALUES(8,'Mill','Mike','Carol','4150 Sydney Place ; Washington','3022178231')

INSERT INTO STUDENT VALUES(9,'Joseph','Fredric','George','915 E 7th St. Apt 6L;Brooklyn NY 11230','3029875634')

INSERT INTO STUDENT VALUES(10,'Joaquin','Norman','Deonte','7100 Athens Place;Washington','4158907654')

INSERT INTO STUDENT VALUES(11,'Denise','Carter ','Williams','6170 Peshwar Place','4123567654')

Method 1: Delete duplicate records from a table using Temp Table.

By using the temporary table we can delete the duplicate records from the table.

1. select distinct records from the main table into a temporary table.

2. Delete complete data from main table.

3. Insert the records from temp table into main table.

SELECT DISTINCT * INTO #TEMP

FROM STUDENT

DELETE FROM STUDENT

INSERT INTO STUDENT SELECT * FROM #TEMP

Method 2: Delete duplicate records from a table using CTE (Common Table Expression):

CTE(Common Table Expression) can be used to delete the duplicate records from the table.

Here is the sample query to delete duplicate records from STUDENT table:

WITH STUDENT_CTE AS

(

SELECT ROW_NUMBER() OVER(PARTITION BY STUDENT_ID

ORDER BY FIRST_NAME, LAST_NAME,

FATHE_NAME, ADDR,PHONE)

AS ROW_NUM

FROM STUDENT

)

DELETE FROM STUDENT_CTE WHERE ROW_NUM > 1

White papers on sql server

2012-11-27T04:39:00.002-08:00

Here is the link to my white papers on sql server:

white paper on query plan in sql server

NOCOUNT Command in sql server

2012-09-06T06:58:00.000-07:00

NOCOUNT command returns number of rows affected by T-SQL statements after the command execution.

Syntax for NOCOUNT is :

SET NOCOUNT {ON/OFF}

SET NOCOUNT OFF : By default NOCOUNT option will be set to OFF in sql server. When ever a T-SQL statement is executed in sql server, then it will return the count of number of rows affected after the statement completion.

SET NOCOUNT ON : This command will not return the count of the number of records affected after command execution.

This option will reduce the extra overhead to the network , which will improve the performance of the sql code.

SET NOCOUNT ON option is especially useful in case of stored procedures.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function @@ROWCOUNT still works and can still be used in stored procedures to fetch information about how many rows were affected by the statement.

What are the uses of SET NOCOUNT ON in sql server?

2012-09-06T06:13:00.002-07:00

By deafault NOCOUNT will be set to OFF in sql server. This will return number of rows affected at the end of each T-SQL statement exceution. In a stored procedure which comprises of many T-SQL statements , its not wise to return the number of rows at the end of each statement execution.

For printing the message with number of rows affected each time, sql server uses some part of system resources; Which is unneccesarily wasted for displaying unuseful informatio.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For a stored procedures containing many T-SQL statements that do not return much actual data, SET NO COUNT ON can provide a significant performance boost because network traffic is greatly reduced.

SQL Server Performance tuning tips : SET NOCOUNT ON at the top of stored procedure

2012-09-06T06:12:00.000-07:00

TIP: Always SET NOCOUNT ON at the top of stored procedure for better performance.

By deafault NOCOUNT will be set to OFF in sql server. This will return number of rows affected at the end of each T-SQL statement exceution. In a stored procedure which comprises of many T-SQL statements , its not wise to return the number of rows at the end of each statement execution.

For printing the message with number of rows affected each time, sql server uses some part of system resources; Which is unneccesarily wasted for displaying unuseful informatio.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For a stored procedures containing many T-SQL statements that do not return much actual data, SET NO COUNT ON can provide a significant performance boost because network traffic is greatly reduced.

What are the disadvantages of TRUNCATE in SQL Server?

2012-09-05T07:52:00.000-07:00

TRUNCATE command consumes less time compared to DELETE. Also, Truncate operation will not consume space in transaction log.

Despite of these advantages, TRUNCATE will have its own disadvantages.

TRUNCATE will reset the seed of an identity column to its initial value. If we don't want seed of identity column to reset after data deletion from table, then truncate is not an apt choice.

Statistics on table will not be updated automatically after TRUNCATE operation unless AUTO UPDATE STATISTICS is ON . So, After every TRUNCATE Statement it's required to update the statistics on table manually. If this operation is not done, then wrong statistics will up used for sometime, which may hit the performance.If you wait long enough, and if you have Auto Update Statistics turned on for the database, then the statistics will eventually catch up with themselves. But this may not happen quickly, resulting in slowly performing queries because the Query Optimiser is using out-of-date statistics.

SQL Server Performance tuning Tips -- Use TRUNCATE Instead of DELETE

2012-09-05T07:00:00.001-07:00

Tip: AVOID DELETE operation if the same task can be performed using TRUNCATE.

If you want to delete complete data from a table, then go for TRUNCATE instead of DELETE.

DELETE operation will maintain transaction log for each and every record it deletes from the table. This will unnecessarily consumes space in transaction log.

Where as TRUNCATE operation will not maintain the log and it will delete all the records from table at one shot.

Also, DELETE operation will consume significantly huge amount of time if the table is of big size.

In order to have better performance and to run the query with in shorter time use TRUNCATE instead of DELETE.

Sql server performance tuning tips -- Don't Run Screen Saver

2012-09-05T06:38:00.001-07:00

TIP: Don't Run Screen Saver

Running Screen saver in a machine consumes significant amount of CPU time.
If we are running the screen saver in the same machine as that of SQL Server running, then some part of CPU time is unnecessarily wasted for running screen saver.

So, When ever a query is running in sql server, always go for a blank screen saver instead of flashy and animated screen savers.

Temporary Tables

2012-08-14T04:11:00.001-07:00

If we can create permanent (persistent) tables in database, why to go for temporary tables. Many people use temporary tables in database without knowing what the purpose and how it will add to performance tuning of queries.

This article will brief you about various types of temporary tables and will show you the examples for each type of temporary table.

"Divide'n'Conquer" a large "problem".

You can get absolutely mind boggling blinding speed out of some queries. This is the rule based on which temporary table concept came into picture. Many people think that "good set based code" has to be a single query to accomplish a task and nothing could be further from the truth. I've used Temp Tables to divide up queries that have couple of joins and that previous took anywhere from 30 minutes to 1 hour to run and have gotten them to run in seconds.

Temporary can be created at runtime and can do the all kinds of operations that one normal table can do. Scope of the temporary tables is limited to the session or instance. These tables are created in tempdb.

Temporary tables are used in the following scenarios:

When we are doing large number of row manipulation in stored procedures. In complex joins, instead of joining the main table directly we can use temporary tables. This will reduce the data load on the table which in turn reduces the execution time of the query.
This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.

Some important information about temp tables:

Temporary table can't have foreign key constraints on it.
The best way to use a temporary table is to create it and then fill it with data. i.e., instead of using Select into temp table, create temp table 1^st and populate with data later

Different Types of Temporary Tables

In SQL SERVER we can create the following types of temporary tables.

Local Temp Table
Global Temp Table
Table Variable

Local Temp Table:

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign

Here is an example for local temp table.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Here is an example for global temp table.

scope of Local Temporary table is only bounded with the current connection of current user. Where as, the scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables

We can create a table a global table in one instance of database and we can use it in another instance of the same database until we disconnect from database.

Table Variable

We have a data type called TABLE in sql server. We can make use of this data type to create temporary tables in database. Here is the syntax for temporary table variable.

If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.

Temporary tables are stored in tempdb as shown below:

Examples in SQL SERVER

2012-08-13T08:00:00.000-07:00

Examples and sample queries for various scenarios are provided below:

Example for Creating a stored procedure with input and output parameteres

2012-08-13T07:59:00.001-07:00

Example 1:

Create a stored procedure to find out the class of a student based on the student id.

Here, Student ID is an input parameter and Class ID is a output parameter.

Query:

CREATE PROC USP_CLASS_OF_STUD

@STUDENT_ID INT,

@CLASS_ID INT OUTPUT

BEGIN

SELECT @CLASS_ID = CLASS_ID

FROM STUDENT_CLASS

WHERE STUDENT_ID =@STUDENT_ID

RETURN @CLASS_ID

END

Example 2:

Execute the above stored procedure to find of the class of a student whose id is 5

Query:

DECLARE @CLASS INT

EXEC USP_CLASS_OF_STUD 5, @CLASS OUTPUT

SELECT @CLASS

Example for creating a stored procedure with an input parameter

2012-08-13T06:10:00.000-07:00

Example 1:

Create a stored procedure Student id, firstname and lastname from STUDENT table by joining the STUDENT_CLASS table Where class = @Class_Id_parameter

Query:

CREATE PROC USP_STUDENTS_IN_CLASS

@CLASSS_ID INT

BEGIN

--Select the student deatils whose class id is @CLASSS_ID

SELECT

S.STUDENT_ID,

S.FIRST_NAME,

S.LAST_NAME

FROM STUDENT S

INNER JOIN STUDENT_CLASS SC

ON S.STUDENT_ID =SC.STUDENT_ID

AND SC.CLASS_ID= @CLASSS_ID

END

Example 2: Execute the above stored procedure to fetch 10th class student's information/

Query:

EXEC USP_STUDENTS_IN_CLASS 10

Example for creating a simple stored procedure

2012-08-13T06:05:00.000-07:00

Example:

Create a simple stored procedure to fetch complete data from STUDENT table.

Query:

CREATE PROC STUDENT_DATA

BEGIN

SELECT * FROM STUDENT

END

Example for Inserting Data into a table

2012-08-13T06:02:00.000-07:00

Example:

Insert data into STUDENT table without explicitly providing column names.

Query:

INSERT INTO STUDENT

VALUES

(

'Dennis',

'Charle',

'Demarcus' ,

'1000 Coney Island Ave. Brooklyn NY 11230',

'3022151541'

)

Example for Truncate table

2012-08-13T06:00:00.002-07:00

Example:

Truncate data from STUDENT table.

Query:

TRUNCATE TABLE STUDENT

Example for Deleting complete data from table

2012-08-13T05:59:00.002-07:00

Example:

Delete complete information from STUDENT table.

Query:

DELETE FROM STUDENT

Example for Deleting data from a table based on a condition

2012-08-13T05:05:00.002-07:00

Example:

Delete data from STUDENT table where student id =1

Query:

DELETE FROM STUDENT

WHERE STUDENT_ID =1

Example for Update data in a table

2012-08-13T05:03:00.000-07:00

Example:

Update FIRST_NAME to 'JOE' in STUDENT table for the student whose Id is 1 .

Query:

UPDATE STUDENT

SET FIRST_NAME = 'JOE'

WHERE STUDENT_ID =1

Insert data Examples

2012-08-13T05:01:00.000-07:00

Example: Insert data into STUDENT table .

Structure for Student table can be found in the below path:
STUDNET Table

Query:

INSERT INTO STUDENT

(

STUDENT_ID,

FIRST_NAME,

LAST_NAME,

FATHE_NAME,

ADDR ,

PHONE

)

VALUES

(

'JAMES',

'CAMERO',

'AARON' ,

'19 Quaker Ridge Rd.,Bethel CT 06801',

'6023458976'

)

Create Table Example

2012-08-13T04:58:00.000-07:00

Example:

Create sample tables like Student, Class etc.,

Queries:

STUNDENT Table:

CREATE TABLE STUDENT

(

STUDENT_ID INT,

FIRST_NAME VARCHAR(25),

LAST_NAME VARCHAR(25),

FATHE_NAME VARCHAR(25),

ADDR VARCHAR(400),

PHONE BIGINT

)

CLASS Table:

CREATE TABLE CLASS

(

CLASS_ID INT,

CLASS_TEACH_ID INT

)

TEACHERTable:

CREATE TABLE TEACHER

(

TEACHER_ID INT,

FIRST_NAME VARCHAR(25),

LAST_NAME VARCHAR(25),

ADDR VARCHAR(400),

PHONE BIGINT

)

STUNDET_CLASS relation Table:

CREATE TABLE STUDENT_CLASS

(

STUDENT_ID INT,

CLASS_ID INT

)

USE Clause Example

2012-08-13T04:52:00.000-07:00

Example:

Using USE clause in SQL Server , database can be changed to point to the required database instead of default database.

Change the database from default database to EXAMPLE database

Query:

USE EXAMPLE

Before running the USE EXAMPLE command database was pointing to 'master' database.

After running the USE EXAMPLE command database was pointing to EXAMPLE database.