Post reply

Truncate

  • September 28, 2013 at 2:32 pm

    #280303

    Comments posted to this topic are about the item Truncate

  • September 29, 2013 at 1:39 pm

    #1654351

    Interesting, very interesting. Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 29, 2013 at 3:25 pm

    #1654363

    Nice and smart - thanks!

  • September 29, 2013 at 6:28 pm

    #1654387

    Awesome.

    Learnt something today 🙂

    M&M

  • September 29, 2013 at 11:13 pm

    #1654409

    Koen Verbeeck (9/29/2013)

    Interesting, very interesting. Thanks for the question.

    +1

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • September 29, 2013 at 11:29 pm

    #1654414

    a learning for today...!!!

    very good question 🙂

  • September 30, 2013 at 12:28 am

    #1654423

    Excellent question to get the lazy Monday going .. 😉

  • September 30, 2013 at 1:47 am

    #1654448

    Learned something new. Thanks for the question.

  • September 30, 2013 at 2:22 am

    #1654456

    thanks for the question....

    and for anyone else asking the question "why would you?":

    🙂

  • September 30, 2013 at 2:45 am

    #1654461

    While DELETE is a pure DML statement which has no side effects on an IDENTITY, TRUNCATE resets the IDENTITY and needs ALTER TABLE permission, which appearently means that the TRUNCATE command counts to the DDL statements.

    Interesting!

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • September 30, 2013 at 3:05 am

    #1654463

    balde (9/30/2013)

    thanks for the question....

    and for anyone else asking the question "why would you?":

    🙂

    Thanks balde for posting the link. Definitely there might be some rare cases where such implementation is required. 🙂

  • September 30, 2013 at 3:26 am

    This was removed by the editor as SPAM

  • September 30, 2013 at 7:22 am

    #1654547

    Thanks for the question!


    Everything is awesome!

  • September 30, 2013 at 9:01 am

    #1654598

    Good question, but the answer isn't quite right.

    You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint UNLESS that table has a foreign key that references itself.

    implies that it's okay to truncate a table with foreign keys as long as there's a self-referential foreign key involved. A more accurate explanation would be

    You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint UNLESS all foreign key references on the table are also from the table (i.e, self-referential.)

    Try

    create table PKFK (

    PK int not null primary key,

    FK int null foreign key references PKFK(PK)

    );

    insert PKFK values (1,null), (2,1), (3,2);

    truncate table PKFK;

    create table FK (

    FK int null foreign key references PKFK(PK)

    );

    insert PKFK values (1,null), (2,1), (3,2);

    insert FK values (1), (2);

    truncate table PKFK;

    drop table FK;

    drop table PKFK;

  • September 30, 2013 at 9:59 am

    #1654626

    nice question.. Thanks.

Viewing 15 posts - 1 through 15 (of 21 total)

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