Are there any MS SQL operations that are not transactional ?

I pretty new to MS SQL.
Long ago I heard that some MS SQL data definition operations are not
transactional.
Executed in the transaction's boundaries their results persists even if
transaction rolls back.
Could anyone ellaborat on that subject ?
Thank you.Table variables are non-transaction, that is to say, they aren't affected by
transaction scope - there is still logging on the transaction log.
declare @.tb table ( mycol int )
insert @.tb values( 1 )
begin tran
update @.tb set mycol = mycol + 1
rollback tran
select * from @.tb
The above returns 2, if you use a temporary table or permanent table you
will get 1...
create table #tb ( mycol int )
insert #tb values( 1 )
begin tran
update #tb set mycol = mycol + 1
rollback tran
select * from #tb
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||TRUNCATE table.
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||That's incorrect. TRUNCATE participates in a transaction like any other
DML operation (table variables excepted).
David Portas
SQL Server MVP
--|||> TRUNCATE table.
?
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
ROLLBACK
SELECT Col1 FROM MyTable
DROP TABLE MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>|||You can have non transaction DDL inside a transaction if you use xp_cmdshell
and osql to execute the DDL. Extended stored procedures are not included in
a transaction. But if you are looking for a bug or "feature" where plain DDL
(table variables excepted as mentioned in the other posts) is not
transactional, there is none I am aware of in SQL Server 2000, although
there might have been in long ago versions like 6.0 or 6.5.
Jacco Schalkwijk
SQL Server MVP
"Marek" <nospam@.nowhere.com> wrote in message
news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>I pretty new to MS SQL.
> Long ago I heard that some MS SQL data definition operations are not
> transactional.
> Executed in the transaction's boundaries their results persists even if
> transaction rolls back.
> Could anyone ellaborat on that subject ?
> Thank you.
>|||I stand corrected.
"JT" <someone@.microsoft.com> wrote in message
news:%23LIQ8RM0FHA.1192@.TK2MSFTNGP10.phx.gbl...
> TRUNCATE table.
> "Marek" <nospam@.nowhere.com> wrote in message
> news:e2n$QpL0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>