The Heap™ – Consultancy ©®

Morning

hot2use

Morning

Andy K

7:41 AM

Morning and Evening

Morning

McNets

7:55 AM

it should be deleted

dezso

9:02 AM

morning

9:55 AM

morning

evening

Q: How long this alter table sql may take?

10:30 AM

0

Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL Suppose there are 45 GB data space and 2GB Index space about 3 million records in this table. column XXX is varchar(8000) now

sql-server

poorly worded question, but actually more interesting than it looks. I seem to remember changing the varchar data type when not (max) was metadata only, and depending on his "text in row" setting the data shouldn't move out-of-row since his existing data fits already. I'm not sure what would happen

Max Vernon

Thanks for that bounty, @TomV

Q: How long this alter table sql may take?

11:21 AM

0

Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL Suppose there are 45 GB data space and 2GB Index space about 3 million records in this table. column XXX is varchar(8000) now Some other information: 99.99% records has NULL value in this varchar(8000) the web application may hit t...

sql-server ddl

Is this an online operation? ^^^

I can't find it from the docs

This blog post by Michael Swart says that it can't be online but it's from 2013: michaeljswart.com/2013/04/…

@ypercubeᵀᴹ It can be, if ONLINE = ON is specified and the version is recent enough.

It does require Enterprise Edition

Needs testing in any case to be sure

@TomV text in row is only for the old LOB types. New LOB types respect large value types out of row.

>Msg 1712 Level 16 State 1 Line 1
Online index operations can only be performed in Enterprise edition of SQL Server.

@PaulWhite Seems like - if it is online - it's from 2016 and above?

@ypercubeᵀᴹ That sounds right

11:36 AM

> Are you sure the alter column can be done online?

> Are you sure the alter column cannot be done online?

Really @ypercubeᵀᴹ, make up your mind already! :)

@AndriyM hehe ;)

@ypercubeᵀᴹ I just tested it in 2016 and 2017 and it can be done online in enterprise edition

dbfiddle.uk/…

sadly dbfiddle is express but it does confirm that the syntax is valid for 2016 and 2017 but not 2014

@PaulWhite the syntax is valid. But there's a chance it will give a different error in Enterprise?

Like "online alter column not allowed for varchar(max)"?

@ypercubeᵀᴹ No I ran that test on my local developer instances

ah, great

11:39 AM

@ypercubeᵀᴹ It worked successfully on 2016 and 2017

I will admit I was surprised

I expected it not to be allowed

Will you post an answer?

Wasn't intending to

Pity. Apparently the OP needs this to be done in minutes

It's 2014 Enterprise so

You could probably do something with batched updates and triggers

gbn

11:56 AM

@AndriyM Create a new column, run a batched update, delete old, rename

@gbn there are concurrent updates

Which is where the triggers come in, I guess

gbn

I didn't read the details. with 99%+ NULL I would use a single update

Perhaps a filter like WHERE YYY IS NOT NULL would make it even less I/O-intensive

12:22 PM

Oy, we got an answer now, even if from a different mod than expected ;)

@AndriyM Yes exactly. There are various ways to achieve an online-like experience but the details can be tricky. 2016+ makes this so much easier.

I fear this will be tested in production

@AaronBertrand I would test it if I can. I do not have enough permission to minapulate on this table. I can only execute some query and I am really new to ddl. This operation is suggested by my colleages, I am just kind of worried about it takes too long. Thanks you again! — user6690200 6 mins ago

@PaulWhite Ah OK you're right, I was typing from memory, but still the data should fit in row

I'm not sure the current up-voted answer is correct in stating that it will be moved out of row

@TomV Yes indeed. There would be a bunch more I/O if there are existing LOB types and the option is set to true.

@TomV It's also not correct in saying the column can't be indexed, but what can you do :)

(it can be an included column)

@PaulWhite or a part of it indexed (as a computed column)

Quite

@TomV Pages will still be rewritten since the structure of the row changes even if the data can stay in row. There will also be lots of logging. To some extent, the data always 'moves'.

I think that's right anyway.

In any case, I'd be doing some pretty comprehensive testing before running it for real.

12:40 PM

@PaulWhite Ah OK that essentially clears it up for me then. So the nvarchar(max) is stored differently than a nvarchar(8000) even if it stays in row?

@TomV Well there's no such thing as an nvarchar(8000) but I know what you mean :)

The details depend on circumstances a bit, but for a traditional offline operation (which this would be) the row is fully rewritten even if it stays on the same page and yes the format changes.

@PaulWhite Shall we assume that when this is done online, the existing rows are not altered but the table is somehow marked that a part is using the old format while new rows, after the change, will use the new?

@ypercubeᵀᴹ My recollection of the implementation is that pages are fully rewritten into a new structure and the engine takes care that any changes to the data while the operation is in progress are made to both old and new structures.

So trading space for concurrency

12:56 PM

Ah thnx. I see the docs in ALTER TABLE link to "online index rebuild": docs.microsoft.com/en-us/sql/relational-databases/indexes/… (so you are right ;)

(as if there was any doubt)

@ypercubeᵀᴹ Yes that describes online index build and online alter column reuses much of the same infrastructure

> Online alter column has similar requirements, restrictions, and functionality as online index rebuild. This includes: ...

ha

right

1:33 PM

Some of the documentation is actually pretty good

@PaulWhite Microsoft documentation? :o

@jadarnel27 Yes!

gbn

@jadarnel27 Try getting the same level for Oracle...

I have met a couple of MS technical writers. They are good, talented people, and work hard

Just kidding. That's actually where I tend to go first, it has the vast majority of the information I need.

1:36 PM

You can even edit it yourself these days

I'm not being sniffy, just adding some personal experience

@TomV Yeah, I have mixed feeling about that :)

@gbn Eek, I know.

Also it requires signing up for something called HubGits

@PaulWhite I didn't read it that way, and I appreciate the context =)

That was the first time the word "sniffy" has ever been used in this room

I should get a prize

1:37 PM

I thought you were referring to allergies

ha!

> separated by a common language

For SQL Server, the examples in particular are extremely helpful for me. The .NET / C# docs are quite good too, and I've never understand why developers are so confused / intimidated by them.

Well I there is some variation in quality for sure, and don't ask me to comment on KB articles :)

But given the pace of change, overall I reckon the docs are pretty good

Lamak

@PaulWhite can you comment on KB articles?

aw c'mon

1:40 PM

LOL

@PaulWhite Pull requests are reviewed and merged by Senior Technical writers or content managers from microsoft

At least the ones I proposed were

@TomV Have you had any approved? I'm hoping the level of oversight does not decline over time.

@PaulWhite All three or four of them, though it took anywhere from a few days to over a week

Impressive. Most impressive.

(said in my best Vader voice for some reason)

Mostly typos or things that were blatantly wrong

I'm not suggesting I know more about SQL Server than the folks writing books online

1:46 PM

They need AndriyM.

3

I had a whitespace / formatting change PR approved =P

yay!

Like this or this

Perhaps someone can explain "pull request" to me (without any obvious low-brow jokes)

@PaulWhite You have your own branch of the main repository where you do changes, and you ask them to "pull" a change from your repository into theirs

1:48 PM

The name is not intuitive to me

@TomV Oh. Seems backward?

I would think I'd be making a push or update request. idk.

@PaulWhite It's in the distributed spirit of things, you work on your own copy of the project, not the actual project

But yes I thought it was weird too when I started working with the git thing

@TomV yes I understand, it's just the intuition around the direction I suppose

My massive contribution to SQL Server docs: github.com/MicrosoftDocs/sql-docs/commit/…

Maybe I'd be expecting commit or merge request.

@jadarnel27 mate that's awesome

@PaulWhite Linus created git and you expect it to be intuitive?

1:51 PM

(but actually a little bit impressive if I'm honest)

@jadarnel27 you should put 'contributor' on your resume now

@TomV Was that the guy with the blanket in Peanuts?

@PaulWhite lol thanks =)

Oh, Torvalds

@jadarnel27 How does one get from there to the actual doc page?

1:52 PM

@Taryn Hahaha, yes.

@jadarnel27 Well I have contributed exactly nothing so

@AndriyM I was actually just wondering the same thing.

Doesn't seem like they are providing those links.

Also the name GitHub - the central focus point of all gits? Seems unkind.

2

@AndriyM I took the dashes out of the file name and Googled it to get back to the actual docs page.

Windows Server Failover Clustering with SQL Server

1:57 PM

Thanks. Managed to find it without removing anything

2:22 PM

@jadarnel27 Even that had to be signed off by the original author

@TomV Ha, I didn't notice that he was the original author (I didn't read the PR response carefully, just saw it was merged).

2:59 PM

Is there a common way / place that you document what trace flags you have turned on for the different SQL Server instances you support?

@jadarnel27 use OpServer - github.com/opserver/Opserver

@Taryn I didn't know that it would show you globally enabled trace flags. I don't see that in the screenshots (but I know they are kind of out of date).

@jadarnel27 let me show you a screenshot of one of our servers

When looking at a specific server you'll see the trace flags turned on

@Taryn Nifty!

Thanks for sharing, I was looking at this screenshot from the GitHub readme: i.imgur.com/PwiVuIK.png

yeah I think that's an old image

Zane

3:44 PM

@Taryn Cool trace flags yo

4:46 PM

@Taryn any idea what 9023 does?

5:02 PM

@TomV "there is a trace flag (9023) to control the size of the SQLLOGPOOL cache, and place a 512MB upper limit on its size." (source)

@jadarnel27 beat me to it

5:38 PM

My Google-fu is strong. Sometimes.