lock table during index creation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » lock table during index creation

lock table during index creation

Rate Topic

Display Mode

Topic Options

Author

Message

sqlblue

Posted Thursday, February 14, 2008 8:30 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, March 02, 2013 4:14 PM
Points: 69, Visits: 218

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 if I have it correctly.

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

Post #455740

GilaMonster

Posted Thursday, February 14, 2008 8:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 37,640, Visits: 29,895

Why do you want to lock the tables?

iirc, in SQL 2000, creating a nonclustered index makes the table readonly for the duration of the index creation

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #455750

GilaMonster

Posted Thursday, February 14, 2008 8:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 37,640, Visits: 29,895

Not at all. SQL's quite capable of handling index creation while the DB's in use.

Since you're on SQL 2005, you can create the index either online or offline. Offline is the behaviour from 2000, the table is readonly while the index is been created.
Online, the table can be read and written while the index is been created and after creation, SQL merges in any changes that happened during the build.

Either way, you don't have to force locks or transactions or anything like that. The engine handles all that internally

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #455768

jshahan

Posted Monday, October 29, 2012 4:00 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:49 PM
Points: 134, Visits: 716

Hi Gail (I hope you see this)

I have databases using SQL 2005, 2008 and 2008 R2.

I want to drop a non clustered primary key and immediately add a clustered version of the same index.

Will SQL Server still “protect me” in this circumstance if this is done while the database is in use? I won't get dupes or anything in the PK column?

Script would be something like this:

ALTER TAble Table1
drop PK_Table1

ALTER TABLE Table1
ADD CONSTRAINT [PK_Table1]
PRIMARY KEY CLUSTERED
(
[ID] ASC
)

Post #1378497

GilaMonster

Posted Monday, October 29, 2012 4:13 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 37,640, Visits: 29,895

BEGIN TRANSACTION

ALTER TAble Table1
drop PK_Table1

ALTER TABLE Table1
ADD CONSTRAINT [PK_Table1]
PRIMARY KEY CLUSTERED
(
[ID] ASC
)

COMMIT TRANSACTION

Please in future post new questions in a new thread. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1378502

jshahan

Posted Monday, October 29, 2012 4:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:49 PM
Points: 134, Visits: 716

Sorry for the improper posting. I thought my question was virtually identical and using the same thread seemed to make sense if someone could find all the answers in one spot. Again my apologies. Won't do it agani.

I take it from your response that wrapping this in a transaction will prevent a problem with duplicate keys, but would it make sense to do this when a database is in use and the table has a couple of million rows?

Post #1378506

GilaMonster

Posted Monday, October 29, 2012 4:43 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 37,640, Visits: 29,895

Couple million's small, but no, modifying indexes is not a good idea when the server is in use.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1378515

« Prev Topic | Next Topic »

Permissions