Archive for 2012

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

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(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(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(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(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(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(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')
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






Posted in | Leave a comment

White papers on sql server

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




white paper on query plan in sql server

Posted in | Leave a comment

NOCOUNT Command in sql server

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.


Posted in | Leave a comment

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

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.

Posted in | Leave a comment

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

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.

Posted in | Leave a comment

What are the disadvantages of TRUNCATE in SQL Server?

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.  

Posted in | Leave a comment

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

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.

Posted in | Leave a comment

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

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.

Posted in | Leave a comment

Temporary Tables


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 1st 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:





















 

Posted in | Leave a comment

Examples in SQL SERVER

Posted in | Leave a comment

Example for Creating a stored procedure with input and output parameteres

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


Posted in | Leave a comment

Example for creating a stored procedure with an input parameter

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




Posted in | Leave a comment

Example for creating a simple stored procedure

Example:

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

Query:


CREATE PROC STUDENT_DATA
AS
BEGIN
      SELECT * FROM STUDENT
END


Posted in | Leave a comment

Example for Inserting Data into a table

Example:

Insert data into STUDENT table without explicitly providing column names.



Query:

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


Posted in | Leave a comment

Example for Truncate table

Example:

Truncate data from STUDENT table.

Query:

TRUNCATE TABLE STUDENT

Posted in | Leave a comment

Example for Deleting complete data from table

Example:

 Delete complete information from STUDENT table.

Query:

DELETE FROM STUDENT


Posted in | Leave a comment

Example for Deleting data from a table based on a condition

Example:

Delete data from STUDENT table where student id =1

Query:

DELETE FROM STUDENT
WHERE STUDENT_ID =1



Posted in | Leave a comment

Example for Update data in a table

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


Posted in | Leave a comment

Insert data Examples

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
(    
      1,
      'JAMES',
      'CAMERO',
      'AARON'     ,
      '19 Quaker Ridge Rd.,Bethel CT 06801',
      '6023458976'
)

Posted in | Leave a comment

Create Table Example

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
)

Posted in | Leave a comment

USE Clause Example


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.




Posted in | Leave a comment