Well, just to show we're not out of the woods yet, the chest infection triggered an asthma attack tonight. Fortunately my fingers and toes are all nice and pink again.
@JSapkota I commented that. My reading of the question is that when they say "duplicate data" they mean duplicated at parent and child table, not in the same table. But I might be wrong. Removed my comment.
@JSapkota I noticed that at the time - I was on that page when the question asker accepted your answer. The comment in question has been self-deleted, so I would say don't worry about it. Appreciate you asking though :)
I want to find classical Greatest N per Group. I have two methods to solve this problem
Dense_rank Over() method
Min Over() method
Both work flawlessly. Now I want to find which one is better and why.
Sample data:
CREATE TABLE #test
(
id INT,
NAME VARCHAR(50),
...
@ypercubeᵀᴹ The point is that the answers would be the same, so yes that's absolutely right to be closed as a duplicate. If the author was intending to ask a question with different answers, they should post that as a new question. As the banner says.
@Learner No. There are Community Technology Previews (CTPs) available. These are basically beta / pre-release versions of the software which you can try out for free.
@dezso Like I hinted? Where? I didn't mean to, if I did. Wouldn't it be ok if he edited it into the current question? I might be missing something, so please be direct.
We can certainly close the current question as unclear until he adds something specific?
@dezso Well, as far as I call tell, he did what he was asked to do: ask a new question instead of asking in an answer. Closing that as a duplicate would seem .... harsh. We either want to answer his specific question about the implementation of the technique in your answer, or we don't.
@dezso I can check in detail a bit later when I haven't got so many plates in the air, but my recollection is that he is after a specific implementation of your general solution.
If we can achieve that via a small edit to his current question, that seems better to me than closing as a dupe and waiting for a second question.
The problem is that we don't know the user's chat reputation (except on SO and Meta.SO, because there it's identical to the main site rep).
So for low-rep users, we'd have to ask the chat site via HTTP -- either when the warning is displayed for the first time, or at least remember that it faile...
I made the following case when statement in my sql:
SELECT
*,
CASE
WHEN lead_time < 14 THEN 0
WHEN (14 <= lead_time < 21 AND days_passed = 8) THEN 1
WHEN
(21 <= lead_time < 28
AND days_passed = 15)
THEN
1
WHEN
(28 <= lead_time < 42
...
I answered but then noticed it had been cross posted at SO, 40 minutes earlier. It already has an answer there, too: stackoverflow.com/questions/35698710/…
/rant on this restructuredtext + sphinx is such an overengineered piece of shit why is it a good idea to have such a confusing markup and no easy way to give style /rant off
We refresh our Development environment from Production (for up to date data), then try to reapply the development differences from before v after. But it doesn't always notice object dependencies
Got it, I've requested a bit more info; happy to keep going if you are. Perf. tuning can be an iterative exercise (and then we'll just ask @Paul White ; ) — wBob14 mins ago
@wBob Ha!
If you would find it helpful to have a chat room for discussion on that q & a just ask.
@MarkSinkinson I've had better luck using dbdeploy or DACPACs for deploying changes, provided the release is tested beforehand to make sure the DDL generated is sane.
I'm kind of surprised it's totally missing object dependencies though.
@JamesLupolt We snapshot the DB state before, then generate a diff after restoring Production into Dev. It was trying to create views before creating tables, which was weird.
@Phil Cheers. I'll get back to you on that. The person who wrote this is not in for the next couple of days and I want to make sure I'm not wasting anyone's time
@dezso I suspect it's just his old show room (as he was a shop owner) and since he didn't get permission to do construction work he just left them as-is
Just got a nasty gram that the following query was using 6.3GB of tempdb space ~ SELECT * FROM dbo.FactSnapshot WHERE StudentId = 1. How do I prove them wrong
Query generate 2300 rows, not particularly wide table, should be just ints and decimals
@ypercubeᵀᴹ Apart from liking your following answer content, I like the fact that you made the distinction (via comments) between the table structure and the index structure (physical). Would you mind if I incorporate the question that the OP made in his comment and your response to such question to your answer body? I think it's worth having those points in there.
The table may have a million or a billion or a trillion rows. The answer is the same:
Add an appropriate index on the columns used in the WHERE (and other clauses).
In this case, a simple index on (name) would be enough. Assuming that the number of rows that match the condition name = 'John' is...
VMWare VMotion isn't going to reboot your server, restart any services or drop caches. The VM stays live during the VMotion, so you shouldn't lose cache or plans, unless the host you are moving to is under severe memory pressure and ballooning is active.
What does happen during VMotion is increa...
Performance improvement is not a guarantee of partitioning. It's really not even the main reason to do it ... read here: asktom.oracle.com/pls/asktom/…. Tom explains it better than I ever could :) But in short: it's a tool, to help with administration. — Ditto2 hours ago
My view on partitioning is generally that it is primarily a decent tool for archival and data lifecycle management - properly indexed data, unless you're dealing with very very large datasets, is normally sufficient enough to avoid the need for partitioning and the incredible licensing costs that are associated with it. Don't think you need to be using it just because it exists as a feature.... — Phil11 mins ago
It's amazing that partitioning is not a tool for performance, in most databases (as far as I know). Yet, we get a few questions every month of people convinced that partitioning will solve their performance problems. In SQL Server, in MySQL, in Oracle, ...
@ypercubeᵀᴹ Thanks. I preferred to ask before, because you could have considered those points were unnecessary, so I would have avoided editing your post.
@TomV I guess I wasn't around much when rickrolling was most popular. :) Certainly haven't been forced to listen to it often enough to stop enjoying the song when I do chance to be listening to it.
@PaulWhite Well, it's not cowardice, I prefer to ask before because I respect the opinion of the author of a post about a determined edition. But I get your point
Making a good, thoughtful edit is one of the most altruistic things you can do on dba (short of a bounty). You improve their question/answer, bump it for more views, and they get all the rep.
@MDCCL It's honestly the way I see it. I sometimes see a quite good answer, and think I could do better. So I add to it. Easier than starting from scratch, and it feels good.
Re editing, I was thinking about promoting the comment here to an answer, being as it is the answer to part 1 at least, and when adding comments it says "try and avoid answering questions in comments". Thoughts?
Or to put it another way, what would a good answer to that question look like?
@AndriyM Good point of view, as well. Just a matter of doing it a few times (and gathering the opinions of The Heap regulars, of course) to get a better impression of the implications.
This system provides information about video games, gaming equipment and accessories (controllers, headsets, etc.) The end-user of this system is a company that sells/rents video games and gaming equipment to its customers.
Suppliers supply the shop with games and equipment. The system keeps info...
@wBob That would certainly answer question 1, though it would need a few words as well as the links to be sufficiently standalone. Question 2 would require more work.
@MDCCL The suggested edit? Nothing I can see to indicate it's the same person.
You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application.
The proce...
Can someone fix @MikeFal's misspelling off command
It's complaining that it's not 5 characters so I can't edit.
I want to find classical Greatest N per Group. I have two methods to solve this problem
Dense_rank Over() method
Min Over() method
Both work flawlessly. Now I want to find which one is better and why.
Sample data:
CREATE TABLE #test
(
id INT,
NAME VARCHAR(50),
...
I made the following case when statement in my sql:
SELECT
*,
CASE
WHEN lead_time < 14 THEN 0
WHEN (14 <= lead_time < 21 AND days_passed = 8) THEN 1
WHEN
(21 <= lead_time < 28
AND days_passed = 15)
THEN
1
WHEN
(28 <= lead_time < 42
...
