This question arose because the only extremely fast solution I could find to a particular problem only works without an ORDER BY clause. Below is the full T-SQL needed to produce the problem, along with my proposed solution (I am using SQL Server 2008 R2, if that matters.)
--Create Orders table
...
Oh my, attempted concatenation of two incompatible types, old-style joins, nvarchar without length - there is a whole mess of problems going on here. What exactly are you attempting to do? What does "filter out" mean? What will the trigger do with the rows it identifies? Is this trigger supposed to do different things with an update than a delete? Is it configured as a trigger for both update and delete? For a delete operation, inserted will be empty, so I'm having a real hard time understanding what is really going on here and what purpose this trigger could possibly serve. — Aaron Bertrand ♦17 hours ago
More amazing is the reply of the OP.
Sorry for being so unclear, I have replaced "filter out" with "retreive". There are multiple paths for updated, deleted and inserted records. This is a monster of a trigger with multiple nested cursors, temp tables, on-the-fly string-concatinated xml generation and so on. I'm in the process of getting rid of the temp tables as a first step. It's a mind-boggling puzzle I'm trying to solve, without a full understanding of what it is actually supposed to technically. Luckily I have regression tests at my disposal to check if it "still works" functionally after I try applying improvements. — Louis Somers11 hours ago
Cursors, nested cursors, xml and temp tables inside a trigger ...
@JackDouglas I suppose it isn't. They say ""It's a mind-boggling puzzle I'm trying to solve, without a full understanding of what it is actually supposed to technically.
If you want to return the ID that a multiple first characters in the shortname, then first look at getting a distinct count of the rows:
select id
from yourtable
group by id
having count(distinct left(shortname, 1)) > 1;
This should return to you the rows that have both a 2 and a 1 as the firs...
Already up-voted I'm afraid, @bluefeet. Good luck with the +7 for the "handegg" hat.
I've got a customer that is on RTM of SQL Server 2008 R2. It looks like there have been 4 service packs since then. Do we need to take any special precautions to get them up to date? I figure we want a machine backups as well as a backup of all the databases including system ones.
Please be cognizant of the differences between 2008 and 2008 R2. Their names are similar but they are completely separate, full releases of SQL Server. 2008 R2 only has three service packs. SP3 is the last service pack that will be released and 2008 R2 will no longer receive any fixes under mainstream support. Yes, you absolutely should get them up to date.
Database backups are never a bad idea when applying any update (but don't you already have those running?), but I don't think a machine backup is justified for a SQL Server service pack.
I'll have to talk to them about a test system. They use the same server for test and prod. Testing restores should be easy, since right now they only focus on full backups. (I'm hoping to fix that part)
@AaronBertrand I left a document covering my findings fairly briefly, but I'm going to clarify things in a followup call. It sounds like they want me to fix their backup strategy. 5/6 of their biggest wait stats were backup related. They were backing up 8 gigs of data every 30 minutes.
@AaronBertrand So while the badness is self-explanatory, anything concrete? I'm sure I'd need to sell them on setting up a test server
@AaronBertrand Yeah, it was taking a minute every time. And then they ran a transaction log backup every night. Which was failing because report server db was in offline mode. There is a reason they paid me to come in for 8 hours, that's for sure.
@AaronBertrand Curiosity. We are integrating the other office's data into ours and their developers will become ours. Thing is, they're an Oracle shop, we are not
So, much of their TSQL smells like Oracle and is riddled with cursors
Ah yeah I'm not touching Oracle cursors and there are some vast differences (however the "Oracle cursors are better" thing you hear all the time actually has little to do with explicit cursors).
And if they don't intend to profit from it, then what right do they have to prevent other assholes from doing so? They didn't invent the phrase "I can't breathe."
Anyways, I suspect the other office is not happy about having "lost" the battle for what home grown product is used for policy management. Thus, we have a turf war and if they can prove that SQL Server just can't scale, then maybe they'll get to keep using their product (idle speculation). Which ties in nicely with my observation of them picking the fatest data types, procedural logic, etc
I know, malice versus ignorance
We burn 143 GB of space in one table because they used datetime2 instead of date to hold date only values
Values never exceed 100? Let's use bigint just to be safe
@ConcernedOfTunbridgeWells if you have two different versions of a query that are getting different plans, I suspect that the order of that comparison is not the only difference.
Anyone ever see SCOM alerts for this counter: Stolen Server Memory (KB)
Description in BOL: "Specifies the amount of memory the server is using for purposes other than database pages" I am assuming this is still memory used by SQL Server, just not for data?
after about an hour of arguing with folks about why it's stupid to shrink a log file as small as possible i just have to stop caring so much and collect a frickin' paycheck
@PaulWhite There are three major sins in this code and the related tables: 1) Storing numbers as strings, 2) Run-on sentence SQL, and 3), the cause of today's production outage due to a missed alert: Forgetting about NULL.
Shocking. It's almost as if big corporates only have twitter accounts for advertising and spam, rather than helping people or using information to fix their own problems.
@James TBH that's what i use twitter the most to do ... customer service queue calls are stupidlong now and the people just dont care. i like to escalate via twitter until i receive a nice PM with a direct phone number
Ha, I complained about how I bought 2 pieces of furniture and they both had problems. They responded to me in a few minutes. I wasn't even messaging them.
I don't believe this is entirely accurate - first, you can't say GROUP BY 1 in SQL Server. And where it is supported (e.g. MySQL) it is the first column in the query, not the table. Your description might work for SELECT * but even then - if it's a join - there is no "the table." — Aaron Bertrand ♦4 mins ago
A lot of people waste time on bad questions. A bad answer is easily set to ignore by various metrics (down-votes, comments), especially when there are better up-voted answers. The exception is when a shitty answer has the highest number of votes and/or is accepted.
I don't really have a serious point to make here. I should go to sleep, I'm tired.
@swasheck Yes, that drives me nuts too. I tried to comment, but it said I didn't have enough rep, so I'm going to comment anyway, here in this big Answer box, and wait for the mods to sort it out.
Of course he does. Because it worked for him in one test.
@PaulWhite He didn't delete (or only did so temporarily).
By "index" did you mean "ordinal position"? Index is a very specific thing in an RDBMS, and I don't think that's what you meant. You're still suggesting that it has something to do with the underlying table, when it doesn't necessarily - only in the case where the query uses SELECT * or the select list happens to match the physical order of column structure in the underlying table. — Aaron Bertrand ♦58 secs ago
It's weird that delete (1) sticks around after it's been deleted and undeleted.
Ultimately I don't think the answer should stay because the guy is flailing around, but sometimes a down-voted answer is as good as the assumptions people might make in its absence
Also, it's amazing how a simple email inquiry about odd behaviour w.r.t. GO has ranged to sqlcmd enhancements to a pissing match about how to better manage a server, TSQL or PowerShell
@swasheck I went to your facebook page and made the grim discovery that we were not friends. After crying for several minutes and blowing my nose a dozen times, I looked for the "Add Friend" button, but did not find it. The only offer was to send you a message. When I clicked on that, that image is what I saw.
@AaronBertrand fascinating. i think it has to do with an option that was set to use @facebook.com as my primary email address
the nerve of some people
@AaronBertrand rectified. heh. and incoming
odd. general account settings has a bunch of the standard configurations and then a random "temperature" setting. i'm disappointed that they didnt include Kelvin
I marked this as community wiki for a reason. Please feel free to edit it with more information about a hat (or anything else).
Chameleon
Chameleon is tricky. It is currently unknown exactly what the trigger for it is, but it includes editing your profile.
The current theory is that you ar...
@billinkc Need a little help. Trying to run a SSISDB internal sproc, it's squawking at me about active operations. Is there an easy way to find and kill active operations?
@MikeFal Don't hate me for using the GUI... if you right click on the SSISDB, there is an option for active operations. you can at least see them there.
I don't know the TSQL for that or I would tell you
To view the list of packages that are running on the server, query the view, catalog.executions (SSISDB Database) for packages that have a status of 2.
@royi dense_rank() is when 4 people tied for first, the next-best is in second. rank() is when 4 people tied for first, the next-best if fifth. There isn't really any technical reason you would use one over the other, it's how you want the numbers displayed and consumed. In some sports, for example, they want a nice clean 1st, 2nd, 3rd. In others, they want your rank to reflect how many people finished ahead of you (well, minus one, depending on your perspective).
@Aaron Thank you for the links i'm surely going to read them . ( it would be great if (kindly) you can continue the competitors analogy , because your example explains dense_rank. and it will be great if I can see the difference as opposed to rank about the same scenario
@MikeFal Have to say watching walkthrough on YouTube of the game for MS2 and picking the outcome or next step actually sounds cool
@MikeFal Cool. I am taking on new job in January and will have to go 3 week trip to corporate office so looking for something to fill downtime (if I get any). Seems like a game that could fill plenty of time
@ShawnMelton So it's fascinating how they managed to hook each game story line into each of the previous episodes. Yes, the overall end pretty much is the same overall, but it's the differences in how the internal events playout due to your choices that's really cool.
If you want to return the ID that a multiple first characters in the shortname, then first look at getting a distinct count of the rows:
select id
from yourtable
group by id
having count(distinct left(shortname, 1)) > 1;
This should return to you the rows that have both a 2 and a 1 as the firs...
I marked this as community wiki for a reason. Please feel free to edit it with more information about a hat (or anything else).
Chameleon
Chameleon is tricky. It is currently unknown exactly what the trigger for it is, but it includes editing your profile.
The current theory is that you ar...
