February 14, 2008 at 8:30 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 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
February 14, 2008 at 8:39 am
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, 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
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2012 at 4:00 pm
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
)
October 29, 2012 at 4:13 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2012 at 4:22 pm
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?
October 29, 2012 at 4:43 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply