@ErikE I'm a big fan of consistency. Because I don't like to use MERGE in cases where I am at least skeptical of its safety, I'd rather not use it anywhere. I mean, we were able to solve that particular problem before MERGE came along.
eww why, Evan? Did you actually read the article? MERGE is largely just syntactic sugar around separate statements anyway (except they left a few, err, holes when stitching it together).
I also wonder how many just bat merge statements around thinking they don't need to deal with isolation semantics because it's "one statement"... then they wonder why they get race conditions etc.
@AaronBertrand What do you recommend for performing an INSERT and returning the generated identity values in a way that can be correlated to the original rows?
I can't speak for SQL Server and that terrifying list of drawbacks which has convinced their experienced users to avoid the functionality. I use ON CONFLICT all the time. Never gotten in a bind with it.
@EvanCarroll Evan, I think it's time for you to start using the "reply" functionality by clicking on the little arrow to the right of each post. It's hard to follow you sometimes because it's not always clear what message you're responding to.
@AaronBertrand Hmmm I only have one of these. There is a partial single-column scoping key, but the system that generates the row only does so through very complicated logic, and there's no particular second value to give any key-based identity to the row besides that it's the next one in the series. (This is a system producing delta messages to send to another system.)
@ErikE I'd have to see it in action to understand, don't do well with word problems especially after the kids are finally in bed.
@ErikE I can't tell you to not use MERGE - if that works for you, go nuts. I can only tell you what I would do, and it's not that. I'd do whatever I would have done in 2005.
@AaronBertrand The identity values are used to update the Id value in C# code so that the newly-DB-saved objects can be presented to the user, and if sending is desired, the Ids can be used to re-fetch them from the database and work with them.
@AaronBertrand Oh, and more than one message can be generated at once for the same scoping key and other column values. Truly, the only guaranteed difference between 2 rows could be in the JSON payload and the identity value.
Think of a set of related tables. A parent table, a child table, a grandchild table. You need to import those tables preserving the relationships contained in them. What we are doing currently is using a cursor on the parent and for each parent row inserted we get the generated value and open another cursor on the child to use the generated value to substitute the source reference, and for every child row there's another cursor to insert grandchild rows in the same manner.
CREATE TABLE #morth(a char(1), b char(1));
INSERT #morth(a,b) VALUES('x','y'),('p','q');
CREATE TABLE #floob(id int IDENTITY(1,1), a char(1), b char(2));
INSERT #floob (a,b)
OUTPUT inserted.id, inserted.a, inserted.b
SELECT a,b FROM #morth;
GO
DROP TABLE #floob, #morth;
MERGE dbo.ReportingMessage
USING #NewReportingMessage input ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (ItemId, SerializedMessage)
VALUES (input.ItemId, input.SerializedMessage)
OUTPUT input.InputId, inserted.ReportingMessageId;
CREATE TABLE foo AS SELECT x, chr(x+64) FROM generate_series(1,10) AS t(x);
CREATE UNIQUE INDEX ON foo(x);
INSERT INTO foo (
SELECT y+5, chr(y+64+5)
FROM generate_series(1,10) AS t(y)
) ON CONFLICT (x) DO UPDATE
SET chr = EXCLUDED.chr
RETURNING x;
@AndriyM I don't know that we're talking about the same use case. Erik says he takes values from a temp table and inserts into a single table which generates an identity column. Where am I inserting a source ID? There is no source ID.
@EvanCarroll I know you like flexing your PG prowess but we're not talking about PG
@EvanCarroll It's not really helping at all. And I don't see how your example does anything like return 2 values, the correlating ID and the newly generated ID.
@EvanCarroll I don't need the ID. I need two values, the ID AND a value from the original row that wasn't inserted. You're not even answering the right question, here.
@EvanCarroll Look at the OUTPUT clause of my MERGE statement above.
So if you run that query, the table gets populated with (1,A)..(10,J). That updated extends the ASCII table with (6,F)..(15,O) and returns the col (x) for the inserted and updated rows which in this case is (6,15) the rows affected.
Then you would do SELECT FOR UPDATE in a CTE before hand and return that (which should work).
this isn't necessary, as Aaron said, I'm just out to tackle the tunnel vision in a friendly way. If it's not appreciated, I'll stfu. And you're welcome to tell me directly whenever. =) friendly competition isn't always welcomed on the proprietary side of the aisle. Not like inspiration is going to overwhelm Microsoft with patches.
@EvanCarroll ON CONFLICT may be a PostgreSQL equivalent of MERGE but the point here is not about resolving conflicts on INSERTs. It's about getting mapping between the source ID and the generated target ID. The source rows are being inserted into another table where each gets a generated ID. The problem is to get mapping between the source ID (which doesn't take part in the inserts) and the target ID. It's easy for a single row but not so much for a row set if done in a single statement.
@AndriyM I've built a system that can handle a year of retrospective data and hundreds of thousands of message comparisons within a few minutes, and I take seriously the need to do things in sets and as efficiently as possible so the system stays fast.
@EvanCarroll I have a 10-year old who is in this phase where he is constantly showing off his knowledge. And admittedly, he is very smart and has a lot of knowledge for a 10-year-old. But he is frequently making himself look like a fool because being 10, he doesn't actually have much experience in the world and over-rates his own ability, experience, and knowledge. I keep trying to guide him gently to be less impressed with himself.
@EvanCarroll I'm sure it's just a phase my son will grow out of, but in the meantime, when he acts like he knows something and ends up actually arguing with me on a topic he doesn't truly understand, or gives a confident but completely misplaced answer, it becomes really frustrating. My son has the excuse of being 10. What's your excuse?
@AndriyM love to show you. Give me a second I'll whip it up and we'll see how it looks in SQL Server. I'm also mildly curios, and we can all be friends right?
I've got an elephant and you've got a window with four panes or something? no hostility...
@EvanCarroll I don't think we can all be friends. The more you talk the more ticked I get at you and your actions. I feel quite hostile right now. You're going to show us how it's done, eh? In SQL Server? Let's see it.
I'm human, though, and my emotional side can (and does from time to time) get the better of me. I'm consciously against hostility, but sometimes I can't help feeling it. (Not feeling towards anyone any at the moment, for the record.)
@EvanCarroll And what do you mean by competition? How is this a competition? I'm trying to solve work problems. How are you fighting against Microsoft by posting queries irrelevant to the discussion that interfere with me getting my work done? Trying to do some postgres evangelism? All you're doing is associating in my mind assery with PG.
@ErikE I meant to fix my earlier message, missed the grace period for editing, so it became a new (basically duplicate) message, which is why I deleted it.
Doesn't matter. The point I made is clear enough, it's just clumsy because of the wrong word order. :) Tickles my OCD
Using ltree
If IPV6 works, great. It doesn't support "ACC". ltree does.
A label path is a sequence of zero or more labels separated by dots, for example L1.L2.L3, representing a path from the root of a hierarchical tree to a particular node. The length of a label path must be less than 65kB,...
@ErikE - I don't mind your "too many words" - I love the English language and love the tiny minutiae of subtle meaning. An anecdote is "a short account of a particular incident or event, especially of an interesting or amusing nature". I wrote "This is an anectode, not an answer" (10k+ users only) in response to an answer that started with "My experience ...", continued with "there have been many times" and ended with "I'm not sure".
This clearly is an anecdote. If the OP had edited his answer to show proof of any of the claims made in his answer, I would have acknowledged that in a comment; and depending on the results, may have even upvoted his answer.
Formatting is meta-data. If you include formatting in a column, you are mixing meta-data with data, which is clearly not a great idea. I'm not a theory-guru, but I'd bet that is listed somewhere as a no-no.
For your specific example, which I know is only an example, you may actually want to st...
@EvanCarroll - I suppose there is a killer way to do that in PostgreSQL ?
@JoeObbish Just out of curiosity, when you say "string aggregation" do you mean string concatenation (e.g. for xml path) or min/max(string) aggregation? And are we talking about worse performance with columnstore, or just no better than with rowstore?
@AaronBertrand @ErikE For that particular example, yes I would simply use MERGE. It is one of the few (only?) truly legitimate uses as far as I am concerned.
Simple MERGE statements are often not problematic on small sets. The problem is that simple statements on small sets have a habit of morphing into something more complex over time, which is where the MERGE edge cases creep in.
@ypercubeᵀᴹ I am tempted to get banned from meta with a comment 'You are clearly an idiot'. Also, adding a very professional no-you-name-the-person to the room. Instead, I'll just keep ignoring here and now elsewhere, too. Slightly complicated because we both are involved in ogresql @TomV.
Seen on the interwebs today ... *And if you ever feel like your job is useless, remember there is someone whose job it is to install turn signals in BMWs.*
@EvanCarroll Thanks. It's still not the same problem, I'm afraid. A picture is worth a thousand words, so when I have time I'll try and come up with an example to illustrate what I and Erik meant.
@ypercubeᵀᴹ Yes. It was a joke trying to conflate the fact that "upsert" was a made-up word that might look/sound awkward (=absurd) to those unfamiliar with the DBA jargon, with the similarity of its pronunciation to the word "absurd". The joke didn't really work well, which I think was because I failed to get the grammar right to convey both meanings as intended. (The grammar worked for one meaning but not exactly for the other.)
SQLFiddle has been an essential tool and a great complement to dba.se for a very long time, but it has gone downhill a bit in the last year or two.
I have also wondered for some time what a 'fiddle' designed specifically with dba.se and markdown in mind might look like, and over the last few day...
You might prefer something like this:
select *
from foobar
where (subject,term) in ( ('STAT','111')
,('STAT','222')
,('ENGLISH','555')
,('COMM','444')
,('COMM','333')
,('S...
I never understood why SQLFiddle didn't cache results tbh — the large majority of visits probably didn't ever need to hit the database back-ends
Am somewhat concerned about security issues though — using XE for Oracle is inherently dangerous. My only mitigation at moment is that the VM DB backends are stateless and can be rolled back in case of problems.
Hola, adding an index on a 100k row join table is going to take seconds, right? ~50mb in size, MySQL 5.5.54, so I'm not sure if that version supports adding indexes without locking.
@TomV $0. Donations would be nice, but I think I have to be committed to a certain level of 'making it work' even if I don't get any — but if I do get any, it'll make a difference to how much time I spend on the project. I've asked a contact at SE to contribute and have reason to think they might, but we will see.
"A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions." docs.oracle.com/database/122/SQLRF/…
@Grimaldi you may be interested to read the discussion in here from chat.stackexchange.com/transcript/message/35497672#35497672 onwards - my reject message on your edit was not correct: I was right that the 1000 limit doesn't apply, but my reason was wrong.
@dezso thanks!
@ypercubeᵀᴹ that's right it doesn't. The VALUES limit reference is a typo I think - Grimaldi was always referring to an in-list limit
On the plus side, I think brexit might be quite good for New Zealand. Good old blighty will be wanting to buy lots of agricultural produce from New Zealand, just like it did back in the glory days of the empire.
@ypercubeᵀᴹ I think that's what used to be the old department of lands and survey. I guess they're using it as the arse end of some sort of GIS application.
Quite interesting. Quite a few of the PG jobs I've seen have been on GIS-type applications.
@PaulWhite Although SQL Server has been keeping me busy for the past 15 years or so (and a certain other DBMS at other times but we don't talk about that) I've been keeping an eye on Postgres and it's getting to the last few major features it needs to be a credible general purpose EDW platform.
Once the PG team get parallel query working well then it's got the last real barrier to working with large volumes.
There are also clustering options like Postgres-XC, and I'd guess it's only a matter of time before someone builds a usable column-store backend for it.
Now that Microsoft are starting to shake down their customer base for pricing, I could see it becoming quite an attractive option. There's no intrinsic reason that it couldn't become the dominant player in the bottom end of the market.
@ConcernedOfTunbridgeWells I don't think about PG much, but yes, it seems to me that parallel query and columnstore (ideally with something like batch mode parallel execution) would be needed.
@PaulWhite They're just putting in the basics of parallel execution now. I suspect it will be a few more generations before it's really mature and perfor^H^H^H^H^Hquick.
@TomV Can you put the irritating project manager in a cage with the arrogant system engineer and see who wins?
@PaulWhite I meant FOR XML PATH. As far as I can tell it's only worse if you use a CCI as an excuse to get rid of all of your nonclustered indexes, which is the path that we appear to be going down (for better or worse). I have a completely unsubstantiated theory that Microsoft is finally giving us STRING_AGG for the CCI with no indexes use case, so at least it won't be an issue in vNext.
@JoeObbish I like to think that someone at Microsoft recently discovered that they could actually implement suggestions we've all been asking for for a decade or so, rather than just putting them on a shelf somewhere. Clearly there is a backlog :)
That said. Replacing all existing indexes with a CCI is one possible approach, and it may even work well in very many cases, but the ability to add NCIs over CCI exists for good reasons.
In my organization, when we take over or create new sqlserver instances we have this little procedure that we do which consists of creating maintenance plans
I've been thinking that using only sqlserver agent jobs with some SQL pre-made scripts would be faster but other DBAs says it's a bad idea
Anyone here care to share what they do when you take over a new sqlserver instance that was poorly maintained, in terms of backup policies and other scheduled jobs ?
Should i make a thread about this ? it's not really a technical question
Generally when I start in a new environment, I tend to check where are the backups, when the last full was taken, when was the last restore applied and I check the security too.
The way I do this is via T-SQL.
Check the backups
;with Radhe as (
SELECT @@Servername as [...
@JackDouglas Heh, I might just need to :P We're taking a big look at our DBs over here soon, and trying to work through O'Reilly's high performance SQL is like chewing tar at the moment
@MaxVernon I have an html report built by a batch script emailed to me every day that contains the status of the backups last execution, failure, success, etc
the script is quite old and buggy though, any online free scripts that does the same kind of thing you ever heard of ? Definitely gonna use Ola stuff from now on
Using ltree
If IPV6 works, great. It doesn't support "ACC". ltree does.
A label path is a sequence of zero or more labels separated by dots, for example L1.L2.L3, representing a path from the root of a hierarchical tree to a particular node. The length of a label path must be less than 65kB,...
Formatting is meta-data. If you include formatting in a column, you are mixing meta-data with data, which is clearly not a great idea. I'm not a theory-guru, but I'd bet that is listed somewhere as a no-no.
For your specific example, which I know is only an example, you may actually want to st...
Generally when I start in a new environment, I tend to check where are the backups, when the last full was taken, when was the last restore applied and I check the security too.
The way I do this is via T-SQL.
Check the backups
;with Radhe as (
SELECT @@Servername as [...
