Truncate table DOES rollback

The post on table variables not being affected by transactions reminds me of another DBA interview question that I very rarely hear people answer correctly.

What is the difference between DELETE and TRUNCATE?

The standard answer trotted out is that DELETE is logged and can be rolled back, whereas TRUNCATE can't.

Really?

OK, try this:

CREATE TABLE #Trunc (MyId INT)
INSERT #Trunc SELECT 1 UNION SELECT 2


SELECT * FROM #Trunc 
-- returns 2 rows


BEGIN TRAN
  TRUNCATE TABLE #Trunc
  SELECT * FROM #Trunc 
-- returns 0 rows - we have truncated the table
ROLLBACK TRAN


SELECT * FROM #Trunc

Now if TRUNCATE was indeed non-logged and cannot be rolled back, you would expect the final SELECT statement to also return 0 records, and then laugh at you as you realised you had lost all your data ...

But it doesn't!  It returns the original 2 rows that were there before you issued the TRUNCATE statement ... if you don't believe me, give it a try ...

Table variables don't roll back

A standard question asked at DBA interviews is what is the difference between a temp table and a table variable.

Aside from the usual incorrect answer of one being a physical table in tempdb and the other being just held in memory, and the less often heard but correct fact of one taking the collation of tempdb and the other taking the collation of the current database, a little known aspect is that one of them is totally unaffected by transactions.

So what I hear you say ...

Combine this with TRY and CATCH error logic, and you can capture the data causing transactions to roll back and write it somewhere else after the transaction has failed.

Don't believe me??  OK, have a look at this:

DECLARE @Data TABLE (MyID INT)


BEGIN TRANSACTION 
INSERT @Data VALUES (1)
ROLLBACK TRANSACATION

Now in a normal, or even temp, table you would get no records back from a subsequent SELECT statement, but run SELECT * FROM @Data after the ROLLBACK statement in this situation, and you will see it returns the data added within the transaction.

Obviously this is a very basic example, but using this within complicated transactions, and the possibilities for auditing and error handling are endless!  Well ... nearly endless ... well ...

Remote access - requires Network Level Authentication

I recently had the following error while trying to connect to a Windows 2008 server while installing SQL 2008, by remote desktop.

The remote computer requires Network Level Authentication, which your computer does not support. 
For assistance, contact your system administrator or technical support.

This seems to be applicable to Windows XP SP3, and the fix is a couple of registry updates.

• Open regedit, and find subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
• Right click "SecurityPackages" and select modify
• Add the value "tspkg" (don't delete any of the existing ones)
• find subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders
• Right click "SecurityProviders" and select modify
• Add the value "credssp.dll"
• Reboot

This should now allow you to RDP onto the Windows 2008 Server.

UPDATE - Microsoft have released an automated FIX IT link for this: Microsoft Support 951608