Interesting question about NCI's when converting HEAPs to CI's
-
I've not run into it before because I don't create permanent tables as HEAPs to begin with. I've also not been able to find the answer in Yabingooglehoo or BOL.
I know that when you convert a HEAP to a Clustered Index, that all of the Non-Clustered Indexes will be rebuilt because the physical HEAP RIDs must be converted to CI references. What I don't know is, if I include the ONLINE=ON option in the creation of the CI, will that option be applied to all of the NCIs that get rebuilt? The 3rd party table I'm working with is quite large and hit tens of thousands of times per hour and I need to keep it online during the conversion.
Yes, I know I could drop the NCIs and rebuild them but that would only keep the table online. I also need to keep the performance up during all of this and, of course, the NCIs play a critical role there.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Warning
You will need the space this large table takes up and downtime.
CREATE a table with the schema you want copy the data over rename and rename. Make sure all is well then drop the old renamed table and recover file space unused space. - Joe Torre - Thursday, August 30, 2018 3:55 PM
Thanks for the reply, Joe. Yep... I'm well aware that the HEAP will persist until the CI is committed and I have the space for that. But there's no need for "downtime" that I can see. I have the "Expensive Edition" and so creating the CI with ONLINE=ON should do the trick for the CI itself. What I want to know is if the NCIs will also be rebuilt (and they will all be rebuilt because of the conversion of HEAP to CI) with an implied ONLINE=ON.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I believe they will. Try it on a smaller heap table.
-
Heh.... I was trying to avoid that because 1) I don't have any other HEAPs, 2) I didn't want to take the time to make one, and 3) the reason I didn't want to take the time to make one is that the machine I'm working on is bloody fast and so it's going to have to be a big one to find out for sure. 😀
It would appear, though, that's the only way I can be sure because I'm not finding any documentation that's the way it will work.
When I get'er done, I'll post back. Thanks for your replies, Joe.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heh... Crud. It turns out this is a moot point. They don't want "just" a Not Null column with a Unique Clustered Index... they actually want a Primary Key CONSTRAINT on the table. Since that's a "Table Create" option, we can't use WITH(ONLINE = ON).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Friday, August 31, 2018 11:52 AM
Don't understand why you say it is a "table create" option - surely if the columns that should be the PK are unique and not null you can just do a alter .. add pk with online...
[
if object_id('dbo.testaddpk') is not null
drop table dbo.testaddpk;create table testaddpk
(id int identity (1,1) not null
,blabla varchar(200)
);insert into testaddpk (blabla) select replicate('abc',30);
go 200alter table testaddpk add constraint pk_1 primary key clustered
(id)
with (online = on); - frederico_fonseca - Friday, August 31, 2018 12:22 PM
I'll try running your code and see what happens.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Interesting... earlier today, I ran the following code and it said "Error near ONLINE" and would not execute.
ALTER TABLE dbo.Trans
ADD CONSTRAINT PK_Trans PRIMARY KEY CLUSTERED ([Iterator]) WITH (ONLINE = ON)
‌Heh... sidebar... I hope you laugh as hard at the table and column name as I did on this 3rd party table. That's not the worst of it, either.
Anyway, that error caused me to finally see the Red squiggly, which is STILL present. The little popup in the following is when you hover the mouse over the squiggly.
As I said, checking in BOL (and I certainly may have read it incorrectly), the ONLINE option doesn't seem to be an option for this type of constraint.
So, failure to run this morning (it produced an error), contains a Red Squiggly, and guess what happened when I just tried to run it again without making any changes to the code (it was still onscreen from this morning)? You've probably guess that it ran just fine. <headdesk><headdesk><headdesk><howl at the moon><headdesk><headdesk><headdesk>
Your code also executes just fine but also contains the same Red Squiggly with the same tool tip when you hover the mouse over it.
Heh... I'm getting too old for this kind of silly stuff. The <headdesk> thing still doesn't bother me but the neighbors think I'm too old to howl at the moon anymore. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Friday, August 31, 2018 8:05 PM
Interesting... earlier today, I ran the following code and it said "Error near ONLINE" and would not execute.
ALTER TABLE dbo.Trans
ADD CONSTRAINT PK_Trans PRIMARY KEY CLUSTERED ([Iterator]) WITH (ONLINE = ON)
‌Heh... sidebar... I hope you laugh as hard at the table and column name as I did on this 3rd party table. That's not the worst of it, either.
Anyway, that error caused me to finally see the Red squiggly, which is STILL present. The little popup in the following is when you hover the mouse over the squiggly.
As I said, checking in BOL (and I certainly may have read it incorrectly), the ONLINE option doesn't seem to be an option for this type of constraint.
So, failure to run this morning (it produced an error), contains a Red Squiggly, and guess what happened when I just tried to run it again without making any changes to the code (it was still onscreen from this morning)? You've probably guess that it ran just fine. <headdesk><headdesk><headdesk><howl at the moon><headdesk><headdesk><headdesk>
Your code also executes just fine but also contains the same Red Squiggly with the same tool tip when you hover the mouse over it.
Heh... I'm getting too old for this kind of silly stuff. The <headdesk> thing still doesn't bother me but the neighbors think I'm too old to howl at the moon anymore. 😀
That's the sort of thing that makes me glad I'm no longer responsible for making things consistently reliable when working with SQL Server.
Last time I looked at the documentation, WITH ONLINE wasn't available when adding a table constraint using ALTER TABLE; but I didn't believe that, as (for example) a even adding a CHECK constraint doesn't allow WITH ONLINE according to al least one BOL page for SQL Server 2017. And adding a CLUSTERED PRIMARY KEY using ALTER TABLE was not supported on SQL SERVER 2017, only NONCLUSTERED PRIMARY KEY could be added by that route. And even an unclustered one could not be added on the two DATA WAREHOUSE versions of the 2017 product. And whether in the case where it could be done it could be done ONLINE was not documented (I would have expected ONLINE not to be not allowed when adding a CLUSTERED primary key - if that were ever allowed - if any other indexes already existed on the table, and would have spent a lot of time looking for non-existent documentation about whether ONLINE was allowed even if there were no other indexes on the table, or if the PRIMARY KEY being added was NONCLUSTERED). I also found that this was contradicted by other BOL pages which claim to cover sql SERVER 2017. I would probably have gone off and drunk too much if I'd tested it and got mutually inconsistent results as you did, but instead I just decided I didnt care and didn't test - I could rebuild the table if necessary, I don't no longer responsibility for anything that has to run all the time.Tom
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply