February 14, 2008 at 8:45 am
Hi, I need to create 2 indexes on a large table
(~200 million records). I wanted to lock the entire table
during these 2 transactions to prevent any update or insert
to the table by the users, but the user should be able to do select. I'm on SQL Server 2005. My example of code below. Could you please let me know:
1. should i use the set transaction isolation level serializable, or do i use the tablelockx as I used below.
2. if the tablockx is correct, then is the code below correct
and would hold the lock for the entire transaction?
use testdb
go
begin tran
select top 1 * table1 with(tablockx)
create nonclustered index (IX_ID_Type_Date)
on table1(id,type, date)
if @@error <> 0
rollback tran
else
commit tran
go
begin tran
select top 1 * table1 with(tablockx)
create nonclustered index (IX_ID_Company)
on table1(id, Company)
if @@error <> 0
rollback tran
else
commit tran
go
February 14, 2008 at 8:50 am
Please don't cross post. It just wastes people's time.
Answered on the following thread - http://www.sqlservercentral.com/Forums/Topic455740-169-1.aspx
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
February 14, 2008 at 8:52 am
sorry, i made a mistake to post in the wrong
forum, so this is why I repost it here because
i thought i have to be in the right forum.
February 14, 2008 at 9:02 am
We're not that strict here. The same post in 2 different places means that people will waste time answering something that has been answered already.
If you realise the post is in the wrong place, and post a new one in the right place, stick a link in the one referring to the other, so that people answering can see what's been said.
Just post in the right place next time 😉
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply