Truncate/Delete

  • We need the DDL for the tables and some sample data for the tables (in a readily consumable format). The sample data should have data that should be deleted and not deleted from both tables. Be sure that the DDL for the tables includes the foreign key as well.

    And, yes, it is possible to write a procedure to delete data from both tables.

  • Here is one table:

    [dbo].[package_lists](

    [packagelist_id] [int] NOT NULL,

    [total_packages] [int] NOT NULL,

    [shipper_symbol] [nvarchar](50) NOT NULL,

    [shipper_friendlyname] [nvarchar](100) NOT NULL,

    [shipdate] [smalldatetime] NOT NULL,

    [service_symbol] [nvarchar](50) NOT NULL,

    [service_friendlyname] [nvarchar](100) NOT NULL,

    [processed_date] [datetime] NULL CONSTRAINT [processed_date_default] DEFAULT (getdate()),

    CONSTRAINT [PK_package_lists] PRIMARY KEY CLUSTERED

    (

    [packagelist_id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    Here is 2nd table:

    [dbo].[packages](

    [msn] [int] NOT NULL,

    [nvarchar](50) NOT NULL,

    [computer] [nvarchar](50) NOT NULL,

    [packagelist_id] [int] NOT NULL,

    [nofn_sequence] [int] NOT NULL,

    [nofn_total] [int] NOT NULL,

    [bundle_id] [int] NOT NULL,

    [consignee_code] [nvarchar](50) NOT NULL,

    [consignee_company] [nvarchar](50) NOT NULL,

    [consignee_contact] [nvarchar](50) NOT NULL,

    [consignee_address_line_1] [nvarchar](50) NOT NULL,

    [consignee_address_line_2] [nvarchar](50) NOT NULL,

    [consignee_city] [nvarchar](50) NOT NULL,

    [consignee_stateprovince] [nvarchar](20) NOT NULL,

    [consignee_postalcode] [nvarchar](35) NOT NULL,

    [consignee_country_iso] [nvarchar](3) NOT NULL,

    [consignee_country_friendlyname] [nvarchar](50) NOT NULL,

    [consignee_phone] [nvarchar](20) NOT NULL,

    [consignee_residential_flag] [bit] NOT NULL,

    [consignee_tax_id] [nvarchar](20) NOT NULL,

    [payment_type_symbol] [nvarchar](50) NOT NULL,

    [payment_type_friendlyname] [nvarchar](50) NOT NULL,

    [consignee_account] [nvarchar](50) NOT NULL,

    [third_party_billing_flag] [bit] NOT NULL,

    [third_party_billing_account] [nvarchar](50) NOT NULL,

    [third_party_billing_code] [nvarchar](50) NOT NULL,

    [third_party_billing_company] [nvarchar](50) NOT NULL,

    [third_party_billing_contact] [nvarchar](50) NOT NULL,

    [third_party_billing_address_line_1] [nvarchar](50) NOT NULL,

    [third_party_billing_address_line_2] [nvarchar](50) NOT NULL,

    [third_party_billing_city] [nvarchar](50) NOT NULL,

    [third_party_billing_stateprovince] [nvarchar](50) NOT NULL,

    [third_party_billing_postalcode] [nvarchar](50) NOT NULL,

    [third_party_billing_country_iso] [nvarchar](3) NOT NULL,

    [third_party_billing_country_friendlyname] [nvarchar](50) NOT NULL,

    [third_party_billing_phone] [nvarchar](50) NOT NULL,

    [third_party_billing_residential_flag] [bit] NOT NULL,

    [package_type_symbol] [nvarchar](20) NOT NULL,

    [package_type_friendlyname] [nvarchar](50) NOT NULL,

    [weight] [float] NOT NULL,

    [weight_units] [smallint] NOT NULL,

    [dimensions] [nvarchar](50) NOT NULL,

    [dimension_units] [smallint] NOT NULL,

    [declared_value] [money] NOT NULL,

    [declared_value_currency] [nvarchar](3) NOT NULL,

    [cod_amount_goods] [money] NOT NULL,

    [cod_amount_goods_currency] [nvarchar](3) NOT NULL,

    [cod_amount_collect] [money] NOT NULL,

    [cod_amount_collect_currency] [nvarchar](3) NOT NULL,

    [cod_payment_method] [smallint] NOT NULL,

    [user_handling_charge] [money] NOT NULL,

    [user_handling_charge_currency] [nvarchar](3) NOT NULL,

    [oversize_flag] [bit] NOT NULL,

    [additional_handling_flag] [bit] NOT NULL,

    [hazmat_flag] [bit] NOT NULL,

    [hazmat_id] [nvarchar](50) NOT NULL,

    [hazmat_description] [nvarchar](50) NOT NULL,

    [hazmat_class] [nvarchar](50) NOT NULL,

    [hazmat_quantity] [float] NOT NULL,

    [hazmat_quantity_type] [smallint] NOT NULL,

    [hazmat_quantity_units] [smallint] NOT NULL,

    [hazmat_packing] [nvarchar](50) NOT NULL,

    [hazmat_packing_group] [smallint] NOT NULL,

    [hazmat_label] [int] NOT NULL,

    [hazmat_cargo_flag] [bit] NOT NULL,

    [hazmat_accessible_flag] [bit] NOT NULL,

    [proof_flag] [bit] NOT NULL,

    [proof_require_signature_flag] [bit] NOT NULL,

    [proof_require_signature_adult_flag] [bit] NOT NULL,

    [saturday_delivery_flag] [bit] NOT NULL,

    [void_flag] [bit] NOT NULL,

    [commitment_symbol] [nvarchar](50) NOT NULL,

    [commitment_friendlyname] [nvarchar](50) NOT NULL,

    [arrival_date] [smalldatetime] NULL,

    [results_currency] [nvarchar](3) NOT NULL,

    [base] [money] NOT NULL,

    [special] [money] NOT NULL,

    [discount] [money] NOT NULL,

    [vat] [money] NOT NULL,

    [total] [money] NOT NULL,

    [dual_service_symbol] [nvarchar](50) NOT NULL,

    [dual_service_friendlyname] [nvarchar](100) NOT NULL,

    [dual_results_currency] [nvarchar](3) NOT NULL,

    [dual_base] [money] NOT NULL,

    [dual_special] [money] NOT NULL,

    [dual_discount] [money] NOT NULL,

    [dual_vat] [money] NOT NULL,

    [dual_total] [money] NOT NULL,

    [hundredweight_rated_flag] [bit] NOT NULL,

    [declared_value_fee] [money] NOT NULL,

    [return_delivery_fee] [money] NOT NULL,

    [saturday_delivery_fee] [money] NOT NULL,

    [saturday_pickup_fee] [money] NOT NULL,

    [proof_fee] [money] NOT NULL,

    [proof_require_signature_fee] [money] NOT NULL,

    [additional_handling_fee] [money] NOT NULL,

    [oversize_fee] [money] NOT NULL,

    [hazmat_fee] [money] NOT NULL,

    [cod_fee] [money] NOT NULL,

    [cod_number] [nvarchar](15) NOT NULL,

    [description] [nvarchar](50) NOT NULL,

    [carrier_instructions] [nvarchar](100) NOT NULL,

    [tracking_number] [nvarchar](35) NOT NULL,

    [bar_code] [nvarchar](100) NOT NULL,

    [zone] [nvarchar](10) NOT NULL,

    [item_number] [nvarchar](50) NOT NULL,

    [sub_number] [nvarchar](50) NOT NULL,

    [commodity_class_symbol] [nvarchar](50) NOT NULL,

    [commodity_class_friendlyname] [nvarchar](50) NOT NULL,

    [waybill_bol_number] [nvarchar](50) NOT NULL,

    [bol_comment] [nvarchar](50) NOT NULL,

    [sed_method] [int] NOT NULL,

    [sed_exemption_number] [nvarchar](20) NOT NULL,

    [cert_of_origin_method] [int] NOT NULL,

    [parties_related_flag] [bit] NOT NULL,

    [documents_only_flag] [bit] NOT NULL,

    [declared_value_customs] [money] NOT NULL,

    [declared_value_customs_currency] [nvarchar](3) NOT NULL,

    [consignee_customs_id] [nvarchar](20) NOT NULL,

    [export_reason] [nvarchar](100) NOT NULL,

    [ultimate_dest_country_iso] [nvarchar](3) NOT NULL,

    [ultimate_dest_country] [nvarchar](50) NOT NULL,

    [closed_manifest_flag] [bit] NOT NULL,

    [closed_manifest_symbol] [nvarchar](30) NOT NULL,

    [closed_manifest_friendlyname] [nvarchar](50) NOT NULL,

    [reference_1] [nvarchar](50) NOT NULL,

    [reference_2] [nvarchar](50) NOT NULL,

    [reference_3] [nvarchar](50) NOT NULL,

    [reference_4] [nvarchar](50) NOT NULL,

    [reference_5] [nvarchar](50) NOT NULL,

    [reference_6] [nvarchar](50) NOT NULL,

    [reference_7] [nvarchar](50) NOT NULL,

    [reference_8] [nvarchar](50) NOT NULL,

    [reference_9] [nvarchar](50) NOT NULL,

    [reference_10] [nvarchar](50) NOT NULL,

    [reference_11] [nvarchar](50) NOT NULL,

    [reference_12] [nvarchar](50) NOT NULL,

    [reference_13] [nvarchar](50) NOT NULL,

    [reference_14] [nvarchar](50) NOT NULL,

    [reference_15] [nvarchar](50) NOT NULL,

    [reference_16] [nvarchar](50) NOT NULL,

    [reference_17] [nvarchar](50) NOT NULL,

    [reference_18] [nvarchar](50) NOT NULL,

    [reference_19] [nvarchar](50) NOT NULL,

    [reference_20] [nvarchar](50) NOT NULL,

    [terms_of_sale_symbol] [nvarchar](50) NOT NULL,

    [terms_of_sale_friendlyname] [nvarchar](50) NOT NULL,

    [return_address_flag] [bit] NOT NULL,

    [return_address_code] [nvarchar](50) NOT NULL,

    [return_company] [nvarchar](50) NOT NULL,

    [return_contact] [nvarchar](50) NOT NULL,

    [return_address_line_1] [nvarchar](50) NOT NULL,

    [return_address_line_2] [nvarchar](50) NOT NULL,

    [return_city] [nvarchar](50) NOT NULL,

    [return_stateprovince] [nvarchar](20) NOT NULL,

    [return_postalcode] [nvarchar](35) NOT NULL,

    [return_country_iso] [nvarchar](3) NOT NULL,

    [return_country_friendlyname] [nvarchar](50) NOT NULL,

    [return_phone] [nvarchar](20) NOT NULL,

    [return_residential_flag] [bit] NOT NULL,

    [special_delivery_flag] [bit] NOT NULL,

    [special_delivery_fee] [money] NOT NULL,

    [export_information_code] [nvarchar](20) NOT NULL,

    [comments] [nvarchar](255) NOT NULL,

    [importer_of_record_account] [nvarchar](50) NOT NULL,

    [importer_of_record_code] [nvarchar](50) NOT NULL,

    [importer_of_record_company] [nvarchar](50) NOT NULL,

    [importer_of_record_contact] [nvarchar](50) NOT NULL,

    [importer_of_record_address_line_1] [nvarchar](50) NOT NULL,

    [importer_of_record_address_line_2] [nvarchar](50) NOT NULL,

    [importer_of_record_city] [nvarchar](50) NOT NULL,

    [importer_of_record_stateprovince] [nvarchar](20) NOT NULL,

    [importer_of_record_postalcode] [nvarchar](35) NOT NULL,

    [importer_of_record_country_iso] [nvarchar](3) NOT NULL,

    [importer_of_record_country_friendlyname] [nvarchar](50) NOT NULL,

    [importer_of_record_phone] [nvarchar](20) NOT NULL,

    [importer_of_record_residential_flag] [bit] NOT NULL,

    [importer_of_record_tax_id] [nvarchar](50) NOT NULL,

    [world_ease_flag] [bit] NOT NULL,

    [world_ease_code] [nvarchar](50) NOT NULL,

    CONSTRAINT [PK_packages] PRIMARY KEY CLUSTERED

    (

    [msn] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[packages] WITH CHECK ADD CONSTRAINT [FK_packages_package_lists] FOREIGN KEY([packagelist_id])

    REFERENCES [dbo].[package_lists] ([packagelist_id])

    GO

    ALTER TABLE [dbo].[packages] CHECK CONSTRAINT [FK_packages_package_lists]

  • GilaMonster (4/13/2009)


    There's a lot more to consider than performance.

    Truncate requires ALTER TABLE permissions, delete requires DELETE permissions

    This is called CONTROL permission, and yes the truncate will fail without it.

    Truncate will also reset the identity column back to 1 (or whatever the initial value declared was) DELETE will not.

    The probability of survival is inversely proportional to the angle of arrival.

  • also, if i remember correctly truncate will reset any seed\incremental value on ID columns,etc

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • SA (4/14/2009)


    You can delete the data from the table which has the foreign key or you can disable constraint checks and then delete.

    So it really depends on your database design.

    Or the "child" table can have the ON DELETE CASCADE clause as part of the FK constraint (to the parent table). So when the "parent" row is deleted, the associated entries are deleted from their respecitive tables. However, before you change the FK constraint to perform this, make sure that it is OK from a business rules standpoint!


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • /*

    After you have run the TRUNCATE TABLE statement, it is important

    then to manually update the statistics on this table using

    UPDATE STATISTICS. This is because running TRUNCATE TABLE will

    not reset the statistics for the table, which means that as you

    add data to the table, the statistics for that table will be

    incorrect for a time period. Of course, 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 Optimizer is using out-of-date statistics.

    *****

    If you use TRUNCATE TABLE instead of DELETE to remove all

    of the rows of a table, TRUNCATE TABLE will not work when

    there are Foreign Key references present for that table.

    A workaround is to DROP the constraints before firing the

    TRUNCATE. Here's a generic script that will drop all existing

    Foreign Key constraints on a specific table:

    */

    CREATE TABLE dropping_constraints

    (

    cmd VARCHAR(8000)

    )

    INSERT INTO dropping_constraints

    SELECT

    'ALTER TABLE [' +

    t2.Table_Name +

    '] DROP CONSTRAINT ' +

    t1.Constraint_Name

    FROM

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t1

    INNER JOIN

    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2

    ON

    t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME

    WHERE t2.TABLE_NAME='your_tablename_goes_here'

    DECLARE @stmt VARCHAR(8000)

    DECLARE @rowcnt INT

    SELECT TOP 1 @stmt=cmd FROM dropping_constraints

    SET @rowcnt=@@ROWCOUNT

    WHILE @rowcnt0

    BEGIN

    EXEC (@stmt)

    SET @stmt = 'DELETE FROM dropping_constraints WHERE cmd ='+ QUOTENAME(@stmt,'''')

    EXEC (@stmt)

    SELECT TOP 1 @stmt=cmd FROM dropping_constraints

    SET @rowcnt=@@ROWCOUNT

    END

    DROP TABLE dropping_constraints

  • All I am trying to do delete data from the table where date >=30

    Correct me if I'm wrong, but as far as I'm aware, you can't perform a conditional TRUNCATE, so your only option would be to use the DELETE command.

  • GilaMonster (4/13/2009)


    There's a lot more to consider than performance.

    Truncate deletes all the data in the table. Delete can have a where clause allowing conditional deletes.

    Truncate does not fire triggers, delete does.

    Truncate is not allowed on a replicated table or one with foreign key constraints, delete is.

    Truncate requires ALTER TABLE permissions, delete requires DELETE permissions

    Truncate also will RESEED a table.

  • I guess I should have read all pages in the thread before the last two posts to reduce post redundancy:-)

  • rgillings (4/16/2009)


    Truncate also will RESEED a table.

    that is correct

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply