Non-logged table ?

Question

Post reply

Non-logged table ?

SQL Guy 1

SSCoach

Points: 15981
More actions
June 29, 2010 at 3:10 pm

#234442

We need to create a non-logged table. That means that all inserts, deletes, updates to this table should not be logged. Is it possible to do ?
Thanks

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

nope

---------------------------------------------------------------------

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Michael Valentine Jones

SSC Guru

Points: 64818

June 29, 2010 at 3:13 pm

#1186730

Any operation that changes a database is logged.

Lowell SSC Guru Points: 323546 More actions · Answer 3

i thought table variables were exempt? i know if they are in a transaction, and the transaction is rolled back, the table variable is unaffected...does that mean it's not logged, or just that it doesn't get affected by transactional changes and rollbacks?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Table variables are not actually database objects, even in tempdb.

# or ## tables are actual tempdb objects, so they are logged.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

some quick googling highlights this:

http://support.microsoft.com/kb/305977/en-gb

and anyway I have read many times especially on this site that table variables ARE written to tempdb (if not enough memory available). They are also therefore logged in tempdb, but unlike temp tables are not affected by a rollback.

Its all a moot point though, table variables are not the same object type as tables.

---------------------------------------------------------------------

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

I was saying that it is not logged because it is not a database object in tempdb, not because it is not stored in tempdb. The following code shows that a declared table is not actually a db object.

declare @x table (id int not null primary key clustered)

insert into @x (id) select id from tempdb.dbo.sysobjects

declare @y table (xx int not null)

declare @y1 table (xx int not null)

declare @y2 table (xx int not null)

-- Show new objects

select * from tempdb.dbo.sysobjects where id not in (select id from @x)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

The actual insert/update/delete into a table variable is logged iirc, which makes sense should there be a problem with that transaction during processing you don't necessarily want partial data entered into the table variable.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Lowell (6/29/2010)
i thought table variables were exempt? i know if they are in a transaction, and the transaction is rolled back, the table variable is unaffected...does that mean it's not logged, or just that it doesn't get affected by transactional changes and rollbacks?

Just that they don't get affected by explicit rollbacks. (ROLLBACK TRANSACTION)

Changes to a table variable still rollback in the case of an error (eg. key violation), and that would not be possible if the table variable was not logged.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

Michael Valentine Jones (6/29/2010)
I was saying that it is not logged because it is not a database object in tempdb, not because it is not stored in tempdb. The following code shows that a declared table is not actually a db object.
declare @x table (id int not null primary key clustered)
insert into @x (id) select id from tempdb.dbo.sysobjects
declare @y table (xx int not null)
declare @y1 table (xx int not null)
declare @y2 table (xx int not null)
-- Show new objects
select * from tempdb.dbo.sysobjects where id not in (select id from @x)

They are in tempDb's system tables, the reason that your query isn't showing them is because, as far as I can tell, they're all created before any queries actually run. Try this alternate code, shows up 3 objects and, if you check sys.columns as well, the names match (the object names are mangled, not the column names)

CREATE TABLE #X (id int not null primary key clustered)

insert into #X (id) select id from tempdb.dbo.sysobjects

GO

declare @y table (xx int not null)

declare @y1 table (xx int not null)

declare @y2 table (xx int not null)

-- Show new objects

select * from tempdb.dbo.sysobjects where id not in (select id from #X)

Or:

DECLARE @someTable TABLE (

AReallyLongReallyOddColumnName int

)

SELECT o.*, c.NAME FROM tempdb.sys.objects o INNER JOIN tempdb.sys.columns c ON o.OBJECT_ID = c.object_id

WHERE o.name LIKE '#%'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass