Truncate Microsoft SQL Database without manually dropping Key constraints

We all have faced times when we want to truncate all the tables of a database, but to truncate a table we need to drop the all the constarints. It is painful to drop all constraints, than truncate and recreate the constarints.
If it is a small database this trick might work but for a big database it is next to impossible, specially when your database structure is changing over time.
Although there is no out of the box way to do it, but with a few line of code we could surely do it.

Following peice of code would do the trick. We can also create a procedure for it if needed regularly.

We will need 3 tables to store the data temporarily. Let's first check if they already exists:
IF OBJECT_ID('dbo.DropFkey', 'U') IS NOT NULL
    DROP TABLE dbo.DropFkey;
IF OBJECT_ID('dbo.DROPTruncate', 'U') IS NOT NULL
    DROP TABLE dbo.DROPTruncate;
    IF OBJECT_ID('dbo.CreateConstraints', 'U') IS NOT NULL
    DROP TABLE dbo.CreateConstraints;

Next step is to create a list of all the primary keys and store it into a table.
;WITH cte(consColumn,
         foreignKeyName,
         parentSchema,
         parentTableName,
         parentColName,
         refSchema,
         refTableName,
         refColName)
     AS (SELECT fkc.constraint_column_id AS consColumn,
                fk.NAME AS foreignKeyName,
                parentSchema.name AS parentSchema,
                parentTable.NAME AS parentTableName,
                parent_col.NAME AS parentColName,
                refSchema.name AS refSchema,
                refTable.NAME AS refTableName,
                ref_col.NAME AS refColName
         FROM sys.foreign_keys fk
              INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
              INNER JOIN sys.tables parentTable ON parentTable.object_id = fkc.parent_object_id
              INNER JOIN sys.schemas parentSchema ON parentSchema.schema_id = parentTable.schema_id
              INNER JOIN sys.columns parent_col ON fkc.parent_column_id = parent_col.column_id
                                                   AND parent_col.object_id = parentTable.object_id
              INNER JOIN sys.tables refTable ON refTable.object_id = fkc.referenced_object_id
              INNER JOIN sys.schemas refSchema ON refSchema.schema_id = refTable.schema_id
              INNER JOIN sys.columns ref_col ON fkc.referenced_column_id = ref_col.column_id
                                                AND ref_col.object_id = refTable.object_id
                                                AND parentTable.type = 'U'
                                                AND refTable.type = 'U')
     SELECT DISTINCT
            foreignKeyName,
            parentSchema,
            parentTableName,
            SUBSTRING(
                     (
                         SELECT ','+a.parentColName+''
                         FROM cte a
                         WHERE a.foreignKeyName = c.foreignKeyName
                         ORDER BY a.consColumn
                         FOR XML PATH('')
                     ), 2, 200000) AS parentColName,
            refSchema,
            refTableName,
            SUBSTRING(
                     (
                         SELECT ','+b.refColName+''
                         FROM cte b
                         WHERE b.foreignKeyName = c.foreignKeyName
                         ORDER BY b.consColumn
                         FOR XML PATH('')
                     ), 2, 200000) AS refColName
    INTO #FkeyDesc
     FROM cte c;

Now that we have the list of all the foreign keys, we can drop the keys in the table. We would use the list of keys stored above to re-create the keys post truncation.
SELECT DISTINCT Row_Number() OVER ( ORDER BY foreignKeyName ASC ) AS RN,
       'IF  EXISTS 
    (SELECT * FROM sys.foreign_keys 
WHERE 
object_id = OBJECT_ID(N''['+parentSchema+'].['+foreignKeyName+']'') 
AND 
parent_object_id 
= OBJECT_ID(N''['+parentSchema+'].['+ParentTableName+']''))
ALTER TABLE ['+parentSchema+'].['+ParentTableName+'] 
DROP CONSTRAINT ['+foreignKeyName+']' AS foreignKey_drop_script
INTO DropFkey
FROM #FkeyDesc;
DECLARE @Job NVARCHAR(MAX);
DECLARE @RN INT;
DECLARE @RN1 INT;
SELECT TOP 1 @Job = foreignKey_drop_script,
             @RN = RN
FROM DropFkey
ORDER BY RN;

WHILE @RN IS NOT NULL
    BEGIN
        EXEC sp_executesql
             @Job;
        SET @RN1 = @RN;
        SELECT TOP 1 @Job = foreignKey_drop_script,
                     @RN = RN
        FROM DropFkey
        WHERE RN > @Rn1
        ORDER BY RN;
   IF @@ROWCOUNT = 0
        BREAK
    END;


As the keys are dropped we could simply truncate all the tables, with the exception of tables which stores the list of keys to be regenerated
SELECT DISTINCT ROW_NUMBER () OVER  (ORDER BY Name) AS RN,
' TRUNCATE TABLE '+ Name + '' AS TruncateScript
INTO DROPTruncate
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND NAME NOT IN
(
'DropFkey',
'DropTruncate',
'CreateConstraints'
--If neede we can add more tables here and these tables would not be truncated.
) 
DECLARE @RNTruncate INT;
DECLARE @RNTruncate1 INT;
DECLARE @JobTruncate NVARCHAR(MAX);
SELECT TOP 1 @JobTruncate = TruncateScript,
             @RNTruncate = RN
FROM DROPTruncate
ORDER BY RN;
WHILE @RNTruncate IS NOT NULL
    BEGIN
        EXEC sp_executesql
             @Jobtruncate;
        SET @RNTruncate1 = @RNTruncate;
        SELECT TOP 1 @JobTruncate = TruncateScript,
                     @RNTruncate = RN
        FROM DROPTruncate
        WHERE RN > @RNTruncate1
        ORDER BY RN;      
        IF @@ROWCOUNT = 0 BREAK;
    END;


Now that all the tables are truncate we could simply re-create the keys.
SELECT DISTINCT
        ROW_NUMBER () OVER  (ORDER BY foreignKeyName) AS RN,
       'ALTER TABLE ['+parentSchema+'].['+ParentTableName+']  WITH CHECK 
ADD  CONSTRAINT ['+foreignKeyName+'] FOREIGN KEY('+parentColName+')
REFERENCES ['+refSchema+'].['+refTableName+']('+refcolname+')' AS Add_constraints_script
INTO CreateConstraints
FROM #FkeyDesc; 
DECLARE @RNCREATE INT;
DECLARE @RNCREATE1 INT;
DECLARE @JobCREATE NVARCHAR(MAX);
SELECT TOP 1 @JobCREATE = Add_constraints_script,
             @RNCREATE = RN
FROM CreateConstraints
ORDER BY RN;
WHILE @RNCREATE IS NOT NULL
    BEGIN
        EXEC sp_executesql
             @JobCREATE;
        SET @RNCREATE1 = @RNCREATE;
        SELECT TOP 1 @JobCREATE = Add_constraints_script,
                     @RNCREATE = RN
        FROM CreateConstraints
        WHERE RN > @RNCREATE1
        ORDER BY RN;
        IF @@ROWCOUNT = 0 BREAK;
 END

Performance: It seems like a gigantic piece of code, but i have tested it on database with more than 100 tables and a few million records. Execution time was approx 1 minute.

Feel free to post your queries in comments and follow this blog if you liked the post.

How to import large SQL files in database

I recently faced an situation, where i was supplied with a .SQL file which was exported from a large table with some million records. The file was exported from the generate script options of SSMS and  had the insert statements. The size of the file was more than 2GB.

SSMS(SQL Server Management Studio) is not capable enough of handling such big files. It would through one following error:

• Insufficient memory to continue the execution of the program.
• or SSMS would simply reject the request to open the file.

SQLCMD is the life saver here. We could use SQL command line utility to execute the T-SQL commands. It is pretty simple to use as detailed below:

1. Open the Command prompt with the administrator privileges.
2. Execute the following command.

 sqlcmd -S SQLSERVER\InstanceName -i "F:\File.sql"

The difference: SSMS uses the .Net framework for execution where as the command line uses ODBC driver.

ETL or ESB : What is better fit for your design

I have often seen people getting confused about, what would be a better fit for movement of data in their system. Their is a lot of content out their about these two technologies. Today i am trying to elaborate these in a very basic example.

Domino's(The pizza giant) logistics is a very good example to understand.
So how domino's works:
They prepare their dough, veggies, spices etc. in their factories than big giant trucks supply these materials to their outlets once or twice a day.

This is what ETL is. Scheduled movement of large quantity data from source to destination. Obviously in real scenario we do some data transformations in between.

The outlets usually have their delivery bikes to deliver the order in smaller chunks as they receive the order. This is ESB. Delivery of small chunks of data to the needy system in near real-time manner.

Would it make sense to use a large truck to deliver the pizzas only once or twice day or vice versa. This will not work at all. This is why it is important plugin in the right component in the system.

I hope i explained it well. Let me know if you have any questions?

Checksum - How to find incremental data when there are no dates column in the source, using Talend DI

I recently came across a situation where we needed to perform incremental on some data, but the problem we cam across is that there was no date column in the source feed which could help us find the incremental data.
When you have an insert date and update in the source you could simply filer the data of source using the date column. We could surely find out the the new inserts by comparing the business keys but to find out the updates one need to compare each column value. This would make the performance very bad if there many columns in the table.

Scenario:
Suppose we have an employee table in the both source and target table with some 20 columns. Now comparing each column of the table in both source and target db for each row could not be a solution. We decided to generate the checksum for each row in the data and compare the checksum values for each row.
What is Checksum: Checksum is an error detecting algorithm and it generates a unique number for a string provided. There are multiple algorithms to generate checksum and we used CRC32.

I am demonstrating this in Talend, but one could use almost any ETL tool to replicate the same.
For the purpose of demonstration , let us take 3 columns:

• EmpId(PK)
• Name(Changing Attr)
• Salary(Changing Attr)

Table Employee source has these 3 columns. The table Employee Target would have same structure with one extra column name checksum. Consider the snapshot below to understand the data.

Below is how the talend job looks like.

It reads the data from source employee table and the java code in the component "tJavaRow" adds another column named checksum to the flow. Below is how the java code looks like.
output_row.EmpID = input_row.EmpID;
output_row.Name = input_row.Name;
output_row.Salary = input_row.Salary;
String rowConcat= input_row.EmpID+input_row.Name+input_row.Salary;

CRC32 crc = new CRC32();
crc.update(rowConcat.getBytes());
output_row.Checksum=Long.toString(crc.getValue());

Here we have concatenated all the 3 columns and calculated the checksum on it. Please note that the sequence of column is important.

After that tMap is comparing the checksum value generated in the process and checksum value already available in the target table.

As seen in the out put below, one row went to the insert branch and 1 row went to the update branch.The log window below shows the checksum values generated.

Talend Introductions for MSBI developers

In this post i would cover the intorduction to talend about product details for the people who already have experience in MSBI and are new to talend.
Talend offers multiple products including an ETL tool call Data Integration. It is a great tool and is built on java so if you have no idea about java at all you might face some challenges during debugging or enhancements.
Apart from this Talend offers other tools for Big Data, Cloud integration and Master Data Management tool.

The open source confusion:
Most of people would tell you Talend is an open source tool but that is not entirely true. Talend offer 2 ETL options:

1. Open Studio for Data integration: It is a free tool but with limited functionality. It does not offer all the connectors, no Management and Monitoring tool and No Data quality or cloud support. So in short it is just a tool to learn Talend but not business.
2.  Data Management Platform: It is a licenced product but one could surely download the one month trial.

I will cover more about Talend in my further posts.