If you are rebuilding your Indexes, MS-SQL requires a lock on that page, no matter what. Even using Enteprise edition, it will temporary acquire a shared lock, between processing.

I recommend...

Jeff Moden (10/3/2012)

---

sql-lover (10/3/2012)

---

sqlfriends (10/3/2012)

---

Thanks,

but I mean the starting seed number, I mean in order for it start again from 5000 in a table that already have records,...

sqlfriends (10/3/2012)

---

Thanks,

but I mean the starting seed number, I mean in order for it start again from 5000 in a table that already have records, it seems I...

I can be wrong, but if you want to start from where you left, different table, you must use SET IDENTITY INSERT ON ... insert, then turned it off. That...

Took a less elegant approach 🙂 ... and I may use bt's...

I've seen a few similar tables. I am in the process of fixing those issues.

Anyway ... tested your...

I wanna die ... :crying:

I just discovered there is no CI on that table, just a NCI. So bt ... your script is doing an ugly table scan (checked the...

bt,

That's a very nice piece of code, thank you so much. However, I wonder how many locks (if one) it will create. Like I said, the production server has serious...

Michael,

Good catch!

Here's the simple schema...

CREATE TABLE [dbo].[MyTargetTable](

[col1] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[col2] [int] NOT NULL,

[col3] [smallint] NOT NULL,

[col4] [smalldatetime] NOT NULL,

[col5] [real] NULL

) ON [FG2]

col1 is INT on...

Faced 1st challenge (dumb error)

I can't use "n" option with BCP or re-inserted from source table. Source has "int" on that column. Target table already has the new "bigint" data...

aaron.reese (10/3/2012)

---

another thing, if you are going to create a new table and manually insert the records into it, dont forget to turn identity insert on; otherwise you will potentially...

bteraberry (10/2/2012)

---

Depending on your timeline, you could do this with virtually zero downtime. Rather than doing a bulk copy, you could easily chunk this up since you're not looking...

bteraberry (10/2/2012)

---

He said these are very big tables. Altering the column means...

NO way! Because downtime! Why do you think that I am taking all...

Good point...

Ever increased ... it is my understanding, no updates or inserts in the middle ...