they have phone numbers like 011.44.1345.729268

i want to ignore 011.44. if found at the beginning of numbers using regex

Morning

A chairde - Morning all!

@fido9dido Hi - as I've said before here, this is not the place to post direct questions - that's for the main site and it looks like a good question (i.e. upvotable) to me. Here, we are a meta-meta-discussion/chat site with lots of J-sEAGL-s and plenty of off-topic [ crap | rubbish | sh*t | whatever_you_want_to_call_it ] (to use a regex metaphor)! :-)

@Vérace +1

@JohnK.N. - thanks John... This is just the sort of question we should be getting more of on the main site. I mean admin issues are perfectly on-topic (hint... d.b.A <---- .se), but I really like a meaty regex or SQL problem to get my teeth into from time to time! :-)

Morning

good morning

@bbaird - Morning - not much happening in the neighbourhood today! In the neighbourhood, in the neighbourhood... Everybody...

okay sorry about that

@fido9dido Please check what I wrote as a comment to your question. This line "they have phone numbers like 011.44.1345.729268" which you used above to explain your problem and which I "grokked" (sorry about the Heinlein American neologism) immediately, isn't present in the question. Finally, another reason to post on the main site as opposed to here is that many more people will see than if you post amongst us [ poor sad bastards | deadly cool dudes ]...

So my ETL is more optimized, but now I've got this weird deadlock issue that occurs due to some parallel query optimization I suspect is due to a trigger but I don't know for certain.

IT NEVER ENDS

@bbaird - that's why we get paid the big bucks! Bwaaaahhhh...

God help me, I might need to try... ugh... memory optimized tables

Well, just one, and for a very specific use case.

@Vérace I did get a salary bump yesterday so

news.itsfoss.com/firefox-continuous-decline

@bbaird realize that if you enable In Memory OLTP it's extremely difficult to remove. I would be careful to not do that in prod unless you're certain it will help.

@HannahVernon It's a monthly ETL process so I've got some time to test - it's a single table that just gets blasted with updates so I figured that is the use case. But I'm thinking that's last resort.

Morning

@JohnK.N. Eerily reminiscent of a certain Q&A website I've heard people talking about!?

Complaining to the extent that they manage to get themselves suspended for a week for doing it too loudly/forcefully...

Hmmm.... wonder where that could be?...

@bbaird Congrats... I wish I could say the same! :-(

does it makes sense now i tried to use fiddle for the first time but it doesnt recognize oracle regex function

@fido9dido It just does what oracle would do! Put in a few examples of your input, your regex and your desired result! Get a simple one to work correctly first!

dbfiddle.uk/…

I showed exampled on desired result, and i used fiddle to to create table in prev link REGEX_REPLACE doesnt work in fiddle

dbfiddle.uk/…

this is the expected result

as you can see fiddle doesnt work

@fido9dido dbfiddle.uk/…

as you can see it does, if you select the correct database

oh sorry

this is the first time to use it, I updated my question and the sample thanks

Anyone have any tips or tricks about large updates on tables? Basically concerning space, time, log usage, and online vs some kind of service window? Specifically, the OLTP group have decided to change some columns in their system from datetime to datetimeoffset and after they ALTER COLUMN, they want to UPDATE the columns using SWITCHOFFSET.

Things we've discussed are creating a new table and inserting all the rows, then dropping the old table and renaming, updating the rows in place in a batch, backing up the log in between batches, setting the DB to simple recovery to minimize the logging.

And of course this is not a single system, this is a turnkey system, so this is an upgrade that has to happen on each customer's system, not just once on our test and production systems and we're done.

So there are like a dozen or so tables of various cardinalities and row sizes, so some are more problematic than others - there might be a table with 10m narrow rows and a row with 1m rows but contains some XML blobs.

@CadeRoux I think instead of ALTER COLUMN and update, it might be a tad faster to add new column, update it using the old column, drop the old and then rename.

OK, will add that to things to benchmark.

also indexes: if there are any on the column, consider dropping them before the operation and adding them back at the end.

Indexes and FKs I think need to be considered with the switch table method (do you plan to use sp_rename or switch?)

Not sure what we are going to do. Want to benchmark some other ways. Currently it is just ALTER COLUMN, with UPDATE TOP (@BatchSize) tbl SET colname = SWITCHOFFSET(colname, timezone) WHERE updated = 0 or something close to that. He says he is dropping and recreating the indexes already.

oh, I think switch is not an an alternative to sp_rename in your case. If I am reading right, it requires the tables ot have same definition

Yeah, I don't think we could use table partition switch method

On log usage, when you update a small column in a large row, does it use as much log as updating a large column in the same row - changing a datetimeoffset versus changing an XML? I guess I need to see if these XML/nvarchar(max) are stored in row or not. I'm not sure he really knows right now which of the table upgrades contributed to teh log growing to 40GB.

@CadeRoux did they change recovery model for those tests? or they were done with full?

@ypercubeᵀᴹ I think still full at that time. With simple and updates in batches, the batches should reuse the log.

@CadeRoux If speed and efficiency are goals, and you're on a fairly modern SQL Server 2016+ I would generally advocate for parallel SELECT INTO incorporating the conversion and switchoffset, then build indexes in parallel with minimal logging.

ALTER COLUMN is single-threaded, fully-logged, and slow due to expanding rows having to split pages and move around. Adding a new column then updating can be even worse for similar reasons. UPDATE is always fully-logged.

Good, yes, I think for version 8.0 of their product, they are requiring 2016+

@PaulWhite So, SELECT blah with datatype/switchoffset done inline INTO newtable OPTION (maxdop whatever); and then DROP oldtable, sp_rename newtable.

SELECT
    B.Id,
    B.[Name],
    B.UserId,
    DateTimeO =
        SWITCHOFFSET(
            CONVERT(datetimeoffset, B.[Date]),
            '+12:00')
INTO dbo.NewBadges
FROM dbo.Badges AS B;

DROP TABLE dbo.Badges;

ALTER TABLE dbo.NewBadges
    ADD CONSTRAINT [PK dbo.NewBadges Id]
        PRIMARY KEY CLUSTERED (Id);

EXECUTE sys.sp_rename
    @objname = N'dbo.NewBadges',
    @newname = N'Badges',
    @objtype = 'OBJECT';

EXECUTE sys.sp_rename
    @objname = N'[PK dbo.NewBadges Id]',
    @newname = N'PK dbo.Badges Id',

Example on the Stack Overflow database

SIMPLE recovery model will help a lot ofc

And that's automatically parallel on the INTO assuming database settings etc? If there is an identity, it will be preserved normally as long as none of the other identity-breaking conditions like joins etc are present, and that doesn't stop the parallelism, right?

@CadeRoux Right. Id is a column with the IDENTITY property in dbo.Badges.

Excellent

For clarity, the new table has the same identity specification.

An estimated plan is good enough to see if you will get parallelism.

Cool

All that said, I think it's weird to use DATETIMEOFFSET.

@PaulWhite Instead of datetime and a separate column for timezone or just convert to UTC?

@CadeRoux Well it doesn't really give you anything. It's a big topic, but storing in UTC is more usual. Where TZ info is important, you usually care about the actual TZ rules and DST not just an offset from UTC at a point in time, which tells you nothing else.

So if TZ needs to be recorded, it is often best to record the standard TZ name (using whichever 'standard' works best for the application).

Why they decided that, I'm not 100% sure, but I know when it comes to my side, if everything was in UTC, that would cause me analytical problems and needs to each be converted to an appropriate local time, since if I want to see procedures done at 9AM local time in different time zones to be included in the same bucket for analyzing across facilities.

It all depends on where the boundary is for TZ rule enforcement. Database, middle tier, app.

As I say, it's a big topic.

If you only ever need to know about the point in time, and will never to any date/time adjustments, I guess DTO might be ok.

Dates and times and worse than strings.

Local times are the worst.

There's typically one hour per year that doesn't exist, and another hour that happens twice.

Almost no software handles that.

It is incredibly awkward. For their problem, I would have moved everything to UTC, even though that would cause a lot more work for me to re-present the data as useful for analysts.

On my side, I kind of have overkill. I use DTO for my own columns just in case I need to distinguish the timezone it was recorded in the future, but run all the servers and processes in UTC and use GETUTCDATE() - all my own datetimes are operational for process logs and ETL. But none of the dates I have to present and use analytics for are really mine, they are these ones from other systems.

So for my own columns, I just use DTO as explicit documentation that the datetime is at offset 0 in the data, it shouldn't be ambiguous.

And that's specifically because my processes do run at night, during time zone changes etc, and UTC is the only way to do that.

Windows scheduler on these local time servers still bites me, though. Scheduled task won't run because that time doesn't exist. Also, on UTC servers, the use case of "get this report every morning at 8AM" is very difficult to implement.

AT TIME ZONE is very useful for small amounts of data

Yes, I'm hoping that with this jump of the OLTP systems to requiring SQL 2016, we will make a break too and start being able to say some future release of the analytics products will only be compatible with this version so we can get SQL 2016 requirement effectively.

Ok, I have what might be a silly question. So I see examples for OPENJSON(@variable, '$') all over the place. Never do I see an example where you use OPENJSON(table_column, '$'). Is that because it is so simple, nobody needs to explain it?

JSON is entirely intuitive, just like an iPhone

said in true deadpan fashion

no sarcasm detected

@D-Klotz I recently asked a question about that. It wasn't obvious to me until I saw it in action 😀

@HannahVernon thank you ! So a CROSS APPLY. I confess I have not used it. Time to experiment. Thank you.

@PaulWhite reading the discussion and wondering about IDENTITY columns. Is there a way to preserve the values using the SELECT INTO?

oh, identity_insert. silly me

@ypercubeᵀᴹ What? SELECT INTO copies the values as-is. The new column also has the identity property. What have I misunderstood about your question?

as I said

@HannahVernon joke's on me, it made the problem worse. Back to the drawing board.

@bbaird worse, wow.

@PaulWhite ah, you used select into, not insert ... select.

double silly me

@HannahVernon Yep, error went from Msg 1205, Level 13, State 51, Line 10, Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. to --Msg 41302, Level 16, State 110, Line 10, The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.

fun.

So it's updating too fast now, where it was only occasionally conflicting before. This has to do with an AFTER UPDATE trigger and a big insert that goes parallel

So I'll just need to do the same thing slightly differently.

@ypercubeᵀᴹ doublesillycube™

The INSERT...SELECT method is still worth testing, but you won't get parallelism on the insert, and minimal logging can be a bit tricksy.

interesting

Seems like an old software problem