SQL Server Performance Forum – Threads Archive

DDL Triggers

Hi, I have created a DDL Trigger on the Northwind db. I created a table in the northwind db from the New Table wizard and the log table recorded an entry as shown below which is fine . I also created a non clustered index on the column fk_user_id CREATE TABLE dbo.test
(
pk_primary_key int NULL,
fk_user_id int NULL
) ON [PRIMARY]
But when I altered the column fk_user_id and made the datatype bigint through the modify table wizard then the log table recorded three entries one for the creation of a temp table as shown below CREATE TABLE dbo.Tmp_test
(
pk_primary_key int NULL,
fk_user_id bigint NULL
) ON [PRIMARY] Second is DROP TABLE dbo.test and the third is CREATE NONCLUSTERED INDEX ix_fk_user_id ON dbo.test
(
fk_user_id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] My Questions are : 1) Is a temp table always created when we change the datatype of a cloumn from the design view of a table
2) Secondly why the log table is making an entry for DROP TABLE test when no command for droping the table has been issued.
3) Are all the non clustered index recreated everytime you change the datatype of a column (from the design view) involved in non clustered index. This works fine when you use the ALTER TABLE ALTER COLUMN command. Thanks,
Arijit

Yes DDL operations will use TEMPDB.
every acion is logged to the table
No you have to reindex the non Yes DDL operations will use TEMPDB. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

2) Secondly why the log table is making an entry for DROP TABLE test when no command for droping the table has been issued.
If you GUI to change the datatype of a column, SQL server creates a new temp table and copies the data and drops the original table and renames the old new temp table to original.
It is not advisable to use GUI to change the datatype and add/remove columns from a table on large table… YOu can run a sql trace to see this info… MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi, Thanks for the quick response. Can you please clarify further on question 3 (regarding the non clustered index). Thanks

]]>