Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

lock table during index creation Expand / Collapse
Author
Message
Posted Thursday, February 14, 2008 8:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
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
Posted Thursday, February 14, 2008 8:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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
Posted Thursday, February 14, 2008 8:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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
Posted Monday, October 29, 2012 4:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 184, Visits: 1,080
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
Posted Monday, October 29, 2012 4:13 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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
Posted Monday, October 29, 2012 4:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 184, Visits: 1,080
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
Posted Monday, October 29, 2012 4:43 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 »

Add to briefcase

Permissions Expand / Collapse