Tuesday 4 December 2012

Copying Table with INDEX and CONSTRAINTS

Introduction
When we are trying to copy a table (Structure only or with Data) we are unable to copy the Indexes, constraint with it.
Can we copy Indexes and Constraint with Table structure in the same database?
This article is related to it.
General Method of Copying Table Structure
To understand it properly I am trying to make an example.
Step-1 [ Base Table Its Indexes and Constraints ]


IF OBJECT_ID('my_EMPTABLE') IS NOT NULL
   BEGIN
     DROP TABLE my_EMPTABLE;
   END
GO
-- Creating Base Table  
CREATE TABLE my_EMPTABLE
(
      EMPID     int IDENTITY(1,1) NOT NULL,
      EMPNAME   varchar(50) NOT NULL,
      EMPGRADE  varchar(1)  NOT NULL,
      EMPDEPT   varchar(50) NOT NULL,
      EMPCITY   varchar(50) NOT NULL,
      EMPSTATE  varchar(50) NOT NULL,
      CONSTRAINT PK__my_EMPTABLE_EMPID PRIMARY KEY CLUSTERED
                  (
                        EMPID ASC
                  )
)
GO
-- Creating NonClustered Index
CREATE NONCLUSTERED INDEX IX_NonCLUS_my_EMPTABLE_EMPCITY
ON my_EMPTABLE(EMPCITY);
GO

-- Creating Default constraint
ALTER TABLE my_EMPTABLE ADD  CONSTRAINT Const_DFLT_my_EMPTABLE_EMPSTATE 
DEFAULT ('TRIPURA') FOR [EMPSTATE];
GO

Step-2 [ Insert Some Records ]
 
INSERT INTO my_EMPTABLE 
      (EMPNAME, EMPGRADE, EMPDEPT, EMPCITY, EMPSTATE)
VALUES('JOYDEEP DAS', 'B', 'DEV', 'AGARTALA', 'TRIPURA'),
      ('TUHIN SHINAH', 'B', 'DEV', 'KOLKATA', 'WEST BENGAL');
Step-3 [ The Simple way to Copy Table ]
To copy table with records
SELECT EMPID, EMPNAME, EMPGRADE, EMPDEPT, EMPCITY, EMPSTATE
INTO   my_EMPTABLE_CPY  
FROM   my_EMPTABLE;
To copy table structure only
SELECT EMPID, EMPNAME, EMPGRADE, EMPDEPT, EMPCITY, EMPSTATE
INTO   my_EMPTABLE_CPY  
FROM   my_EMPTABLE
WHERE  1 = 2;
Step-4 [ So how we copy Table with All of it Index and Constraint ]
In the above example the Index and constraint are not copied.
You can not directly copy the constraint from one table to another table; first you should copy the table Structure with indexes and constraint, to do so
References: (Hardi Patel)
Please follow the instructions below:
 1. Select the DATABASE from which you want to copy the table, then right Click on that and Select the TASK then Select Generate Script.
Database -> Task -> Generate Scripts...
You will get the Wizard
follow it.
Step 1. Press Next button
Step 2. Select Database -> Select the database from list and Press Next button
Step 3. Choose Script Options -> Table/View Options -> Select two indexes option and change both to True.
Script Full-Text Indexes -> True and Script Indexes -> True. Press NEXT
Step 4. Choose Object Type -> Tables
Step 5. Choose Tables -> select tables you want to generate script. Press NEXT.
Step 6. Output Option -> select Script to New Query Window options. Press Finish.
Step 7. Press Finish

Step-5 [ Alternate Way to copy Table with All of it Index and Constraint ]
We can use this stored procedure to perform this work. This Stored Procedure is ready to use.
/*

Testing
--------
EXEC my_TBLCOPY
     @p_STRUCONLY      = 1,
     @p_SOURCETBL      = 'sale_tfa_it',
     @p_DESTINATIONTBL = 'my_it'
    


IMPORTANT: Will not work with full-text, spatial, or XML indexes.
*/    
IF OBJECT_ID('my_TBLCOPY') IS NOT NULL
   BEGIN
      DROP PROCEDURE my_TBLCOPY;
   END
GO       
CREATE PROCEDURE my_TBLCOPY
       (
         @p_STRUCONLY      INT          = 1,
         @p_SOURCETBL      VARCHAR(255) = NULL,
         @p_DESTINATIONTBL VARCHAR(255) = NULL
       )
AS
              DECLARE @my_Table   nVARCHAR(255) = '',
                      @intRow_Cnt INT = 1,
                      @execStr    nVARCHAR(MAX),
                      @curPk      nVARCHAR(255),
                      @MaxRows    INT,
                      @execPK     nVARCHAR(255) = NULL;
              CREATE TABLE #tmp_Indxs
                           (
                             rnum INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
                             execStr NVARCHAR(MAX)
                           );
BEGIN
     SET NOCOUNT ON;
     BEGIN TRY
     SET @my_Table = @p_SOURCETBL;
     WITH qry (NAME, TYPE_DESC, IS_UNIQUE, IGNORE_DUP_KEY,
               FILL_FACTOR, PAD_INDEX, ROW_LOCKS, PAGE_LOCKS,
               COLUMN_NNAME, KEY_ORD, KEY_DESC)
           AS (SELECT ind.NAME, ind.type_desc, ind.is_unique, ind.ignore_dup_key,
                      ind.fill_factor, ind.is_padded, ind.allow_row_locks,
                      ind.allow_page_locks, col.NAME AS column_nName,
                      ind_col.key_ordinal, ind_col.is_descending_key
                  FROM   sys.indexes ind
                         LEFT OUTER JOIN sys.stats sta ON sta.object_id = ind.object_id
                                                       AND sta.stats_id = ind.index_id
                         LEFT OUTER JOIN (sys.index_columns ind_col
                         INNER JOIN sys.columns col ON col.object_id = ind_col.object_id
                                                  AND col.column_id = ind_col.column_id
                            ) ON ind_col.object_id = ind.object_id
                                 AND ind_col.index_id = ind.index_id
                         LEFT OUTER JOIN sys.data_spaces dsp 
                                 ON dsp.data_space_id = ind.data_space_id
                         INNER JOIN sys.tables st ON ind.object_id = st.object_id
                  WHERE  st.NAME = @my_Table
                            AND ind.index_id >= 0
                            AND ind.is_disabled = 0
                            AND ind.is_primary_key = 0
                            AND ind.type <> 3
                            AND ind.type <> 4
                            AND ind.is_hypothetical = 0),
                            bigQ (indName, cols, isUnique, type, options)
             AS (
                SELECT DISTINCT NAME COLLATE DATABASE_DEFAULT,(
                               SELECT column_nName + CASE key_desc
                                           WHEN 1 THEN ' DESC' ELSE '' END + ','
                               FROM   qry q2
                               WHERE  q2.NAME = q1.NAME
                       ORDER BY NAME, key_ord
                       FOR XML PATH('')), is_unique, type_desc,  
                                       'DROP_EXISTING=ON,IGNORE_DUP_KEY=' +
                    CASE ignore_dup_key
                       WHEN 1 THEN 'ON' ELSE 'OFF'
                       END + ',FILLFACTOR=' + STR(CASE WHEN fill_factor>0
                                                       THEN fill_factor
                                                       ELSE 79 END, 3, 0) +
                             ',PAD_INDEX=' + CASE pad_index
                       WHEN 1 THEN 'ON' ELSE 'OFF'
                       END + ',ALLOW_ROW_LOCKS=' + CASE row_locks
                       WHEN 1 THEN 'ON' ELSE 'OFF'
                       END + ',ALLOW_PAGE_LOCKS=' + CASE page_locks
                       WHEN 1 THEN 'ON' ELSE 'OFF'END
       FROM qry q1
       )
       INSERT INTO #tmp_Indxs (execStr)
    SELECT 'CREATE ' + CASE isUnique
              WHEN 1
                     THEN 'UNIQUE '
              ELSE ''
              END + CASE type
              WHEN 'HEAP'
                     THEN NULL
              ELSE type
              END + ' INDEX [' + indName + '] ON [' + @my_Table + '] (' +
                     LEFT(cols, LEN(cols) - 1) + ') WITH (' + options + ')'
      FROM bigQ;


    SET @MaxRows = (SELECT COUNT(*)
              FROM #tmp_Indxs
              );

    WHILE @intRow_Cnt <= @MaxRows
              BEGIN
                     SELECT @execStr = execStr
                     FROM #tmp_Indxs
                     WHERE rnum = @intRow_Cnt;

                     IF @execStr IS NOT NULL
                           EXEC (@execStr);

                     SET @intRow_Cnt = @intRow_Cnt + 1;
              END


       SELECT TOP 1 @curPk = NAME
       FROM sys.indexes
       WHERE object_id = object_id(@my_Table)
              AND is_primary_key = 1;
             
      
    EXEC('IF OBJECT_ID('''+ @p_DESTINATIONTBL+''') IS NOT NULL BEGIN DROP TABLE '+ @p_DESTINATIONTBL +' END');
   
       IF @p_STRUCONLY = 1
          BEGIN
              EXEC('SELECT * INTO '+ @p_DESTINATIONTBL + ' FROM '+ @my_Table + ' WHERE 1=2');
          END
       Else
          BEGIN
              EXEC('SELECT * INTO '+ @p_DESTINATIONTBL + ' FROM '+ @my_Table); 
       END
       SELECT @execPK = coalesce(@execPK + ',', 'ALTER TABLE [' + @p_DESTINATIONTBL + ']
        ADD CONSTRAINT [my_EMP1'  +ind.NAME + '] PRIMARY KEY (') + '[' + col.NAME + ']'
       FROM sys.indexes ind
       LEFT OUTER JOIN (
       sys.index_columns ind_col INNER JOIN sys.columns col 
                         ON col.object_id = ind_col.object_id
                     AND col.column_id = ind_col.column_id
              ) ON ind_col.object_id = ind.object_id
              AND ind_col.index_id = ind.index_id
       WHERE ind.object_id = object_id(@my_Table)
              AND is_primary_key = 1
       ORDER BY ind.index_id
              ,ind_col.key_ordinal;

       SET @execPK = @execPK + ');';
      
       IF @execPK IS NOT NULL
              EXEC (@execPK);
             
    PRINT 'Table Copied';
    END TRY
    BEGIN CATCH
      PRINT 'Error Found';
    END CATCH       
END      
To execute this stored procedure
EXEC my_TBLCOPY
     @p_STRUCONLY      = 1,
     @p_SOURCETBL      = 'my_EMPTABLE',
     @p_DESTINATIONTBL = 'my_EMPTABLE_CPY'

Here    
@p_STRUCONLY: When the Value of this parameter is 1 it only copy the structure not the data. If other then 1 it copies structure with data.
@p_SOURCETBL: The name of the source table.
@p_DESTINATIONTBL: The name of the Destination Table.

Please try this stored procedure. I hope you like it.



Posted by: MR. JOYDEEP DAS

Tuesday 27 November 2012

Improve the performance by Execution Plan

Introduction
Developers ask a common quest "How to improve the performance of a SQL Query". It is not so easy to answer as lot of factors is related to it. There are some general guidelines that we can follow to improve the overall performance of a query.
But I recommended the execution plan to understand the performance of the query. I preferred execution plan when I am building the query block step by step.
In this article I am trying to show a basic strategy, how to improve a query by observing the query plan.

Prerequisite
To understand this article, we have a very good knowledge of Index, Index Scan, Index Seek, Table scan etc. Please follow the related tropics of this article, to complete this.

Improving Query
To understand it properly, I am taken an example.
Step-1 [ Creating The Base Table ]
-- Creating the Base Table
IF OBJECT_ID('Emp_Dtls') IS NOT NULL
   BEGIN
      DROP TABLE Emp_Dtls;
   END
GO  
CREATE TABLE Emp_Dtls
       (EMPID    INT          NOT NULL IDENTITY,
        EMPNAME  VARCHAR(50)  NOT NULL,
        EMPGRADE VARCHAR(1)   NOT NULL,
        EMPDEPT  VARCHAR(30)  NOT NULL);
GO
Step-2 [ Inserting the Records ]
DECLARE @i INT=1;
BEGIN TRY
BEGIN TRAN
WHILE (@i <= 50000)
BEGIN
      INSERT INTO Emp_Dtls
             (EMPNAME, EMPGRADE, EMPDEPT) 
      VALUES('Developer-'+CONVERT(VARCHAR, @i),'C','DEV');
      SET @i=@i+1;
END

SET @i=1;  
WHILE (@i <= 50000)
BEGIN
      INSERT INTO Emp_Dtls
             (EMPNAME, EMPGRADE, EMPDEPT) 
      VALUES('Devlivery Mgr-'+CONVERT(VARCHAR, @i),'B','DM');
      SET @i=@i+1;
END  

SET @i=1;  
WHILE (@i <= 50000)
BEGIN
      INSERT INTO Emp_Dtls
             (EMPNAME, EMPGRADE, EMPDEPT) 
      VALUES('Manager-'+CONVERT(VARCHAR, @i),'A','MGR');
      SET @i=@i+1;
END  
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Step-3 [ See the Actual Execution Plan ]
-- Execution Plan-1 [ Table Scan ]
SELECT * FROM Emp_Dtls; 



As there is NO INDEX defined it going to TABLE SCAN. So the performance of the SQL Query is worst. We have to improve the performance of the Query.
Step-4 [ Create Clustered Index ]
As there is no index over here and the attribute "EMPID" of Table objects "EMP_DTLS" has INTEGER data type, so it is a good candidate key for CLUSTERED INDEX. Now we are going to create the CLUSTERED INDEX on it.
-- Create Custered Index 
IF EXISTS(SELECT *
          FROM   sys.sysindexes
          WHERE  id = OBJECT_ID('Emp_Dtls')
                 AND name ='IX_CLUS_Emp_Dtls')
   BEGIN
      DROP INDEX Emp_Dtls.IX_CLUS_Emp_Dtls;
   END      
GO
CREATE CLUSTERED INDEX IX_CLUS_Emp_Dtls
ON  Emp_Dtls(EMPID);
Aster creating the CLUSTERED INDEX we are going to see the EXECUTION plan again that it Improves or NOT.
-- Execution Plan-2 [ Clustered Index Scan ]
SELECT * FROM Emp_Dtls;



Now we can see that there is Clustered Index Scan. So the performance is little bit improve. At least it uses the CLSUTERED INDEX.
 Step-5[ Putting WHERE conditions in Query ]
-- Execution Plan-3 [ Using WHERE Conditions ]
SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A'; 



 As the "EMPGRADE" is used in the WHERE conditions we are going to make a NON CLUSTERED Index on it.
-- Now Create Non Clustered Index on EMPGRADE
IF EXISTS(SELECT *
          FROM   sys.sysindexes
          WHERE  id = OBJECT_ID('Emp_Dtls')
                 AND name ='IX_NONCLUS_EMPGRADE')
   BEGIN
      DROP INDEX Emp_Dtls.IX_NONCLUS_EMPGRADE;
   END      
GO
CREATE NONCLUSTERED INDEX IX_NONCLUS_EMPGRADE
ON  Emp_Dtls(EMPGRADE); 
GO

Now again see the execution plan.
-- Execution Plan-4
SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A'; 



Here again the clustered Index is used. The non clustered index that we created is not used here. Why?
You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries.
In this Example the "EMPNAME" is a NONKEY Columns.
Step-6[ Solve the Problem ]
-- Non clustered Index with Incluse
IF EXISTS(SELECT *
          FROM   sys.sysindexes
          WHERE  id = OBJECT_ID('Emp_Dtls')
                 AND name ='IX_NONCLUS_EMPGRADE_EMPNAME')
   BEGIN
      DROP INDEX Emp_Dtls.IX_NONCLUS_EMPGRADE_EMPNAME;
   END      
GO
CREATE NONCLUSTERED INDEX IX_NONCLUS_EMPGRADE_EMPNAME
ON Emp_Dtls(EMPGRADE) INCLUDE(EMPNAME);
Now again see the Execution Plan.
-- Execution Plan-5
SELECT EMPID, EMPNAME FROM Emp_Dtls WHERE EMPGRADE='A';



Now the desired output came and it is INDEX SEEK.

Related Tropics

Hope you like it.


Posted by: MR. JOYDEEP DAS