Posted by Tony Rogerson on 12/16/05 17:15

> Why do you allow users to define tables on the fly in a production
> database? That means you have no data model and users control the
> schema, so you do not even know the names of your entities.

Think Knowledge Management, where you define your knowledge model on the fly
and build the table structure to support it; better that then creating
1,000's of tables containing generic stuff.

> TRUNCATE is both proprietrary and dangerous (see what it does with
> logging, its limitations, etc.)

What does it do with logging? Why is it dangerous?

What rubbish.

TRUNCATE TABLE is logged, SQL Server logs extent deallocations so is fully
recoverable if in the middle of the truncate a problem occurs.

Yes there are limitations, specifically and for good reason, not being able
to use it when you have foriegn key constraints - you should know that.

Say you have a 10GB table that you want to clear down and empty - would you
really issue a DELETE so that ALL the rows and index pages are logged
causing a 10GB+ log file, it would also take an exceedingly long time to
run. Its one of the first things you learn as a DBA, TRUNCATE is better than
DELETE when clearing down a table.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1134739926.655709.309060@z14g2000cwz.googlegroups.com...
>>> Is there something wrong in the query? Or if someone can tell me a
>>> better approach. <<
>
> The correct terms are "referenced" and "referencing" tables; "child"
> and "parent" are terms from IDMS, IMS and other network DBMS systems.
>
> It is always a dangerous thing to do queries and execture statements on
> the schema information tables. It says that you do not know what you
> are doing until run time.
>
> Why not put DRI actions for ON DELETE CASCADE on the referencing tables
> and let the system do the work properly so you do not have to do this
> kind of manual housecleaning?
>
> Why do you allow users to define tables on the fly in a production
> database? That means you have no data model and users control the
> schema, so you do not even know the names of your entities.
>
> TRUNCATE is both proprietrary and dangerous (see what it does with
> logging, its limitations, etc.)
>
> The kludge to fix the bad design is to create DELETE FROM statements on
> the fly. The real answer is to get a workable schema and to get rid of
> this code.
>

[Back to original message]