If you don't do it then you can't restore your production databases to a consistent state.

As it is impossible to backup multiple databases...

Another solution to my original problem (unit testing t-sql - continuing after expected error)

I am surprised this works really - a more reliable check for sql server to make would...

something like

if (xact_state() = 0) begin

begin tran

set @towner = 1

end--if

if (xact_state() <> 0 and @towner = 1) begin

rollback tran

end--if

why should SELECT be immune from deadlocks?

even a SELECT from a single table can get deadlocked due to multiple indexes involved

(if index access is in different order)

two connections issuing "select...

ok

I understand now

any pain would be felt by a rebuild

but as my archiving is mostly in sequence anyway - even rebuilds won't be badly affected

The question that remains is whether...

I am positive that a clustered index defines the physical order of rows

from BOL:

Creates an index in which the logical order of the key values determines the physical order of...

I am ashamed to admit this database had "autoshrink" enabled...

oh yes

exec sp_executesql N'select getdate() option (recompile)'

that works nicely 🙂

but not this:

exec sp_executesql N'select...

I'd like to know how the server handles large quantities of distinct SQL statements with SP_EXECUTESQL

seems to me that all those cached plans will end up pushing out plans that...

2. The difference between GRANT SELECT and GRANT EXECUTE is huge:

While first permission allows only select from table/view, GRANT EXECUTE allows exeuting procedure which can perform SELECT and INSERT/UPDATE/DELETE if...

create some helper functions:

public static string SqlName(string name) {

return "[" + name.Replace("]", "]]") + "]";

}//method

public static string SqlString(string name) {

return "'" + name.Replace("'", "''") + "'";

}//method

if you're building...

cached query plans are not always good

if they are not reused they waste resources

and if they are dependent on parameters they are worse than compiling fresh

but if you are going...

[edit]

ok I see where I am going wrong now...

giving users SELECT access opens a big can of worms

related to read locks interfering with normal OLTP operations

I either give them access via stored procedure (that releases locks asap by...

I had a look at that ebook on defensive programming and the first thing I read was shocking:

ALTER PROCEDURE dbo.SetEmployeeManager

@EmployeeID INT ,

@ManagerID INT

AS

SET NOCOUNT ON ;

UPDATE dbo.Employee

SET ...