Tables and Stored Procedures

[Barry Young]

SQL Server 2000:

Question 1

If you drop / rename a table and then recreate the table with the SAME
NAME, what impact does it have on stored procedures that use these tables?
From a system perspective, do you have to rebuild / recompile ALL the stored
procedures that use this table?

I had a discussion with someone that said that this is a good idea, since
the IDs of the tables change in sysobjects and from a SQL SERVER query plan
perspective, this needs to be done...

Question 2

If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if an
error occurs? Will it Rollback? The theory is that it won't because
Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?

Thanks!

[Dan Guzman]

If you drop and recreate a table in SQL 2000, existing query plans are
invalidated. Procedures that reference the table are automatically
recompiled the next time they are executed so you don't need to take any
special action.

TRUNCATE TABLE is minimally logged so the operation can be rolled back just
like other SQL statements. To illustrate:

CREATE TABLE MyTable(Col1 int)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT * FROM MyTable
ROLLBACK
SELECT * FROM MyTable

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Barry Young" <yo******@insightbb.com> wrote in message
news:sP_ad.235588$D%.109359@attbi_s51...

SQL Server 2000:

Question 1

If you drop / rename a table and then recreate the table with the SAME
NAME, what impact does it have on stored procedures that use these tables?
From a system perspective, do you have to rebuild / recompile ALL the
stored procedures that use this table?

I had a discussion with someone that said that this is a good idea, since
the IDs of the tables change in sysobjects and from a SQL SERVER query
plan perspective, this needs to be done...

Question 2

If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if
an error occurs? Will it Rollback? The theory is that it won't because
Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?

Thanks!

[Barry Young]

Thanks Dan!

That is great info!

I assume that all related stored procs to that table are just recompiled
once, correct?

Barry

"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message
news:r7*****************@newssvr11.news.prodigy.co m...

If you drop and recreate a table in SQL 2000, existing query plans are
invalidated. Procedures that reference the table are automatically
recompiled the next time they are executed so you don't need to take any
special action.

TRUNCATE TABLE is minimally logged so the operation can be rolled back
just like other SQL statements. To illustrate:

CREATE TABLE MyTable(Col1 int)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT * FROM MyTable
ROLLBACK
SELECT * FROM MyTable

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Barry Young" <yo******@insightbb.com> wrote in message
news:sP_ad.235588$D%.109359@attbi_s51...

SQL Server 2000:

Question 1

If you drop / rename a table and then recreate the table with the SAME
NAME, what impact does it have on stored procedures that use these
tables? From a system perspective, do you have to rebuild / recompile ALL
the stored procedures that use this table?

I had a discussion with someone that said that this is a good idea, since
the IDs of the tables change in sysobjects and from a SQL SERVER query
plan perspective, this needs to be done...

Question 2

If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if
an error occurs? Will it Rollback? The theory is that it won't because
Truncate doesn't utilize the logs where as Delete From uses the SQL
Logs?

Thanks!

[Dan Guzman]

Each proc will recompile the next time it is executed. Normal caching and
recompilation rules apply for subsequent executions.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Barry Young" <yo******@insightbb.com> wrote in message
news:Lh%ad.228342$MQ5.135764@attbi_s52...

Thanks Dan!

That is great info!

I assume that all related stored procs to that table are just recompiled
once, correct?

Barry

"Dan Guzman" <gu******@nospam-online.sbcglobal.net> wrote in message
news:r7*****************@newssvr11.news.prodigy.co m...

If you drop and recreate a table in SQL 2000, existing query plans are
invalidated. Procedures that reference the table are automatically
recompiled the next time they are executed so you don't need to take any
special action.

TRUNCATE TABLE is minimally logged so the operation can be rolled back
just like other SQL statements. To illustrate:

CREATE TABLE MyTable(Col1 int)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT * FROM MyTable
ROLLBACK
SELECT * FROM MyTable

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Barry Young" <yo******@insightbb.com> wrote in message
news:sP_ad.235588$D%.109359@attbi_s51...

SQL Server 2000:

Question 1

If you drop / rename a table and then recreate the table with the SAME
NAME, what impact does it have on stored procedures that use these
tables? From a system perspective, do you have to rebuild / recompile
ALL the stored procedures that use this table?

I had a discussion with someone that said that this is a good idea,
since the IDs of the tables change in sysobjects and from a SQL SERVER
query plan perspective, this needs to be done...

Question 2

If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if
an error occurs? Will it Rollback? The theory is that it won't because
Truncate doesn't utilize the logs where as Delete From uses the SQL
Logs?

Thanks!