October 1, 2019 at 8:34 pm
Why is this INDEX setting not persistent ? I'm testing this on SQL2019, but I've seen the same thing on SQL2016.
I run this
CREATE NONCLUSTERED INDEX [ImageActID] ON [dbo].[Image](
[ImgID] ASC
)WITH (SORT_IN_TEMPDB=OFF, DROP_EXISTING=ON, ONLINE=ON, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
GO
Then I refresh and right click on the index and select "Script index as CREATE to ..." And this is the results
CREATE NONCLUSTERED INDEX [ImageActID] ON [dbo].[Image](
[ImgID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
October 1, 2019 at 10:45 pm
If I had to guess, my guess would be that they're using the default settings instead of actual settings. That's terrible, of course, but I see little that can be done about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2019 at 7:57 am
I would say that particular keywords of the create/alter index statement are part of that command definition, not part of the object that is object of the command
object static properties - e.g. properties that are part of the object definition and therefore part of its metadata - see sys.indexes
create/alter index only options e.g. only valid for the current statement
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy