April 5, 2016 at 6:17 am
I created a table using a query, but the table needs an ID column as primary key. First question is: Is there a make table query command that will generate the ID column? Next question is: How long (best guess of course) should it take to run the query below on a million records?:
ALTER TABLE myTable
ADD ID INT IDENTITY(1,1) NOT NULL
ALTER TABLE myTable
ADD CONSTRAINT PK_Grouped PRIMARY KEY(ID)
I started this query two days ago on an Azure database and it finally terminated with error:
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
April 5, 2016 at 6:27 am
doasidont (4/5/2016)
I created a table using a query, but the table needs an ID column as primary key. First question is: Is there a make table query command that will generate the ID column? Next question is: How long (best guess of course) should it take to run the query below on a million records?:ALTER TABLE myTable
ADD ID INT IDENTITY(1,1) NOT NULL
ALTER TABLE myTable
ADD CONSTRAINT PK_Grouped PRIMARY KEY(ID)
I started this query two days ago on an Azure database and it finally terminated with error:
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
I suggest that you create the table first, with IDENTITY column in place, and then run an INSERT query.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 5, 2016 at 6:47 am
I would say your Azure database is under powered (as is almost ALWAYS the case). But as someone else said, fastest will be to make a new table (with NO indexes, or perhaps just the clustered PK on identity if that is your intent) and then do an INSERT. Not sure about Azure DB, but this could get you minimally logged transaction, which will be MUCH quicker than anything fully logged! Note there are some requirements for this, such as TABLOCK.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2016 at 7:15 am
Thanks. The table I'm dealing with has already been created and populated with data. I'd rather not rebuild it again. Should I use the 'Alter Table' command or use some other query to create the identity column? Does the 'Tablock' command go with the 'Alter'? Should I set 'Identity-Insert'?
April 6, 2016 at 7:22 am
IF OBJECT_ID('tempdb..#a','U') IS NOT NULL
DROP TABLE #a;
CREATE TABLE #a (col1 sysname);
INSERT #a
(
col1
)
SELECT TOP 1000000
c1.name
FROM sys.columns c1
CROSS JOIN sys.columns c2
CROSS JOIN sys.columns c3;
ALTER TABLE #a
ADD id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED;
SELECT *
FROM #a;
Creates a temp table, populates it with 1 million rows, then adds a NC IDENTITY PK, all completes in 30 seconds on my desktop PC.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 6, 2016 at 5:01 pm
Thank you. The 'Alter' method worked, for 50 million rows it took several hours.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply