quote:

---

Originally posted by MichaelP

I know that TRUNCATE TABLE is a non-logged operation. That's why it's so fast. I think that BULK INSERT (or is ti BCP?) is non-logged as well.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

---

USE Northwind
GO

DECLARE   @ServerName sysname
	, @User varchar(25)
	, @Password varchar(25)
	, @cmd varchar(8000)

SELECT	  @ServerName = ''
	, @User = ''
	, @Password = ''

SELECT @cmd = 'bcp Northwind.dbo.Orders out c:\Orders_20040204.dat -n -S'
      +@ServerName+' -U'+@User+' -P'+@Password  
SELECT @cmd = 'master..xp_cmdshell ' + '''' + @cmd + ''''
SELECT @cmd
EXEC(@cmd)

EXEC master..xp_cmdshell 'Dir C:\*.*'

BEGIN TRAN
SELECT * INTO NewOrders FROM Orders WHERE 1=0
COMMIT TRAN

SELECT COUNT(*) As ComittedCount FROM NewOrders

BEGIN TRAN
SELECT @cmd = 'bcp Northwind.dbo.NewOrders  in c:\Orders_20040204.dat -n -S'
      +@ServerName+' -U'+@User+' -P'+@Password  
SELECT @cmd = 'master..xp_cmdshell ' + '''' + @cmd + ''''
SELECT @cmd
EXEC(@cmd)
-- oops
-- COMMIT TRAN
ROLLBACK TRAN

SELECT COUNT(*) AS Rollback_Count FROM NewOrders
GO

DROP TABLE NewOrders
EXEC master..xp_cmdshell 'DEL c:\Orders_20040204.dat'
EXEC master..xp_cmdshell 'Dir C:\*.*'
GO

USE Northwind
GO

BEGIN TRAN
SELECT * INTO NewOrders FROM Orders
COMMIT TRAN
SELECT COUNT(*) AS Created_Count FROM NewOrders
BEGIN TRAN
TRUNCATE TABLE NewOrders
ROLLBACK TRAN
SELECT COUNT(*) AS RollBack_Count FROM NewOrders
GO

DROP TABLE NewOrders
GO

quote:

---

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

---

quote:

---

Originally posted by MichaelP

I know that TRUNCATE TABLE is a non-logged operation. That's why it's so fast. I think that BULK INSERT (or is ti BCP?) is non-logged as well.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

---

use northwind 
go
exec SP_HELPINDEX EMPLOYEES
go
begin tran
	CREATE nonclustered INDEX IDX_Employee_Title on Employees(Title)
	exec SP_HELPINDEX EMPLOYEES
rollback tran
go
exec SP_HELPINDEX EMPLOYEES
go
USE pubs
GO
begin tran
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
   FROM pub_info pr, publishers p
      WHERE p.pub_id = pr.pub_id 
      AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 0 0 'This One ' 
select pr.* FROM pub_info pr, publishers p
      WHERE p.pub_id = pr.pub_id 
      AND p.pub_name = 'New Moon Books'

rollback tran
GO
select pr.* FROM pub_info pr, publishers p
      WHERE p.pub_id = pr.pub_id 
      AND p.pub_name = 'New Moon Books'
go


CREATE TABLE [authors2] (
	[au_id] [id] NOT NULL ,
	[au_lname] [varchar] (40)  NOT NULL ,
	[au_fname] [varchar] (20)  NOT NULL ,
	[phone] [char] (12)  NOT NULL CONSTRAINT [DF__authors__phone__1923BF5C] DEFAULT ('UNKNOWN'),
	[address] [varchar] (40)  NULL ,
	[city] [varchar] (20)  NULL ,
	[state] [char] (2)  NULL ,
	[zip] [char] (5)  NULL ,
	[contract] [bit] NOT NULL ,
) ON [PRIMARY]
GO

exec master..xp_cmdshell 'bcp pubs.dbo.authors out c:\new_auth.dat -c -T'

go
begin tran
	select count(*) from authors2
	BULK INSERT pubs.dbo.authors2 FROM 'c:\new_auth.dat'
	select count(*) from authors2
rollback tran

select count(*) from authors2

quote:

---

Originally posted by The Enigma

I would argue that somehow "bcp in" is in a seperate session and it commits before returning back the control to SQL Server

---

quote:

---

In this light, I believe that "Non-Logged operation" is actually a minimally logged operation in reality, which means that its affects are viewed by the server on the page/segment level, rather than on the record level (I am not sure if it makes sense.)

So I guess I DON'T stay corrected in regards to Steve Duncan's comment that "TRUNCATE is a logged operation" simply because it's...minimally logged operation! Yes, it can be rolled back, but not in a row-by-row fashion. It can also be seen by Log Explorer, where you would not see individual DELETEs when trying to reverse this statement.

This is just to "set the record straight!"

---

BEGIN TRAN
SELECT * INTO NewOrders FROM Orders
ROLLBACK TRAN
SELECT * FROM NewOrders

quote:

---

Originally posted by X002548

So do you think the following is rolling back the CREATE TABLE for the statement only since it doesn't "see" the rows...

BEGIN TRAN
SELECT * INTO NewOrders FROM Orders
ROLLBACK TRAN
SELECT * FROM NewOrders

Brett

8-)

---

quote:

---

Originally posted by The Enigma

There is a difference between no-logged and minimally logged ... and i did not find the term non-logged in the Holy Book

---

quote:

---

A TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger. Because permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.

---

quote:

---

A TRUNCATE TABLE statement is not caught by a DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE without a WHERE clause (it removes all rows), it is not logged and thus cannot execute a trigger. Because permission for the TRUNCATE TABLE statement defaults to the table owner and is not transferable, only the table owner should be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement.

---