What is the difference between these two T-SQL statements?

In a SSIS package at work there are some SQL tasks that create staging tables for holding import data. All the statements take this form:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tbNewTable') AND type in (N'U'))
BEGIN
    TRUNCATE TABLE dbo.tbNewTable
END
ELSE
BEGIN
    CREATE TABLE dbo.tbNewTable (
        ColumnA VARCHAR(10) NULL,
        ColumnB VARCHAR(10) NULL,
        ColumnC INT NULL
    ) ON PRIMARY
END

In Itzik Ben-Gan's T-SQL Fundamentals I see a different form of statement for creating a table:

IF OBJECT_ID('dbo.tbNewTable', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbNewTable
END

CREATE TABLE dbo.tbNewTable (
    ColumnA VARCHAR(10) NULL,
    ColumnB VARCHA开发者_运维百科R(10) NULL,
    ColumnC INT NULL
) ON PRIMARY

Each of these appears to do the same thing. After execution, there will be a empty table called tbNewTable in the dbo schema.

Are there any practical or theoretical differences between the two? What implications might they have?

The first one assumes that if the table exists, it has the same columns as those it would create. The second one does not make that assumption. So if a table with that name happened to exist and had a different set of columns, the two would have very different results.

The first will not actually DROP the table -- it merely TRUNCATES all the data in said table. Hence why the CREATE is guarded.

Thus the form with the DROP will allow the subsequent CREATE to change the schema (when the new table is created) even if tbNewTable previously existed.

Because the DROP/CREATE alters the database schema it may not also be allowed in all cases. For instance, a view created with a SCHEMABINDING will prevent the table from being dropped. (This also hold true for more general FK relationships, should any exist.)

...when SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

The TRUNCATE should be marginally faster in one of those constant "don't care" ways: there should be no performance consideration given to one over the other.

There are also permission differences. TRUNCATE only requires the ALTER permission.

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner...

Happy coding.

These are very different..

The first does an equality check on the sys.objects system table and looks to see if there is a matching table name. If so, it truncates the table. Basically removing all rows but maintaining the table structure itself - i.e. the actual table is never dropped.

In the second, the check to make sure that the table exists is implicitly done using the OBJECT_ID() method. If so, the table is dropped completely - rows and structure.

If you have a primary and foreign key constraint on the table, you'll certainly have issues dropping it completely... and if you have other tables that are linked to the table you are trying to 'truncate' you'll have issues there too, unless you have cascade deletion turned on.

I tend to dislike either construction in an SSIS package. I create the tables in a deployment script and I want the package to fail if one of the tables I use is missing later on because then something drastically wrong has happened and I want to investigate what before I try putting data anywhere.