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 CONSTRAINTConst_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.
PRIMARY KEY and INDEX
This article is related to PRIMARY KEY and INDEX.
A common misconnects is that the PRIMARY KEY is always CUSTERED index.
Before going to the depth of this article I want to ask some common questions.
My questions are mentioned bellow.
1. Is PRIMARY KEY is always CLUSTERED INDEX?
2. Can Primary KEY be CREATED without CLUSTERED INDEX (NONCLUSTERD)?
3. Can Primary KEY Exist WITHOUT Any INDEX?
Now I am explaining it one by one.
Is PRIMARY KEY is always CLUSTERED INDEX?
By default when we create the PRIMARY KEY it takes UNIQUE CUSTERED INDEX. Here I am providing a simple example to illustrate my points.
CREATE TABLE my_Emp
(EmpID INT NOT NULL PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL)
GO
sp_helpindex my_Emp
GO
-- Result
index_name
|
index_description
|
index_keys
|
PK__my_Emp__AF2DBA79525B36FA
|
clustered, unique, primary key located on PRIMARY
|
EmpID
|
Here in this example I am Just create a table where "EmpID" is the primary key. Please look at the result It showing UNIQUE CLUSTERED INDEX.
Can Primary KEY be CREATED without CLUSTERED INDEX (NONCLUSTERD)?
Yes, the Primary key can be created without CLUSTERED INDEX. Here I am providing a simple example to illustrate my points.
DROP TABLE my_Emp
GO
CREATE TABLE my_Emp
(EmpID INT NOT NULL PRIMARY KEY NONCLUSTERED,
EmpName VARCHAR(50) NOT NULL)
GO
sp_helpindex my_Emp
GO
-- Result
index_name
|
index_description
|
index_keys
|
PK__my_Emp__AF2DBA78562BC7DE
|
nonclustered, unique, primary key located on PRIMARY
|
EmpID
|
Here in this Example I am using NONCLUSTERED keywords with PRIMARY KEY. It gives me UNIQUE NONCLUSTERED PRIMARY KEY. So the PRIMARY KEY by default is UNIQUE CLUSTERED but we can create UNIQUE NONCLUSTERED primary key.
Can Primary KEY Exist WITHOUT Any INDEX?
NO. The PRIMARY KEY can not be exists without any Index. Here I am providing a simple example to illustrate my points.
DROP TABLE my_Emp
GO
CREATE TABLE my_Emp
(EmpID INT NOT NULL,
EmpName VARCHAR(50) NOT NULL)
GO
CREATE CLUSTERED INDEX indx_my_Emp ON my_Emp (EmpName)
GO
ALTER TABLE my_Emp
ADD PRIMARY KEY(EmpID)
GO
sp_helpindex my_Emp
GO
-- Result
index_name
|
index_description
|
index_keys
|
indx_my_Emp
|
clustered located on PRIMARY
|
EmpName
|
PK__my_Emp__AF2DBA7859FC58C2
|
nonclustered, unique, primary key located on PRIMARY
|
EmpID
|
Here I am just craete a table without any index or parimay key (heap). Then create a clustered index on columns named "EmpName". As only one primary key can exists in a table. Then I am createing a primary key on columns named :EmpID". Please look at the output. Here the Primary key takes UNIQUE NONCLUSTERED INDEX.
Conclutions
The PRIMARY KEY by defaults take the CLUSTERED INDEX. That's not means that we can craete the PRIMARY KEY with NONCLUSTERED INDEX.
It is necesasary to understand that the PRIMARY KEY columns is suitable for CLUSTERED INDEX or NOT. If not suitable for CLUSTERED INDEX, please don't create CLUSTERED INDEX on primary key.
