In a previous job we'd do hardware sizing and tell them what to buy. Everything contractual, because we benchmarked and knew exactly what they needed given volumes
Client was told RAID 01, did RAID5. Hilarity ensued
Got flown out to fix the slowness after go-live, all slow IO
If you're going to spend 10 million on software, consultancy and support, at least listen to what's said!
Well, the pavements are terrible in the village. Walked home on the road. Closed my pub at 8:30 because it was dead. 4 out of the 6 pubs were closed as I walked to the lock-in pub.
A friend that lives near Sheffield showed me pictures of the hilarity that was his drive into work today
Drive down a country road - end up reversing a mile because there's a 8 foot wall of snow
Last night there were 4 cars of people that came down Winnats Pass, which is basically a hard drive in the middle of summer. All ended up getting rooms in the pubs because they abandoned their cars. Why they tried to go down one of the steepest inclines is beyond me.
One of my best mates is a farmer and I really feel for him because he's having to help these idiots to get their cars out of the way so he can get to his livestock! They've started lambing a few weeks early
Lambs born outside are as good as dead. Need to be chucked in a barn L(
(In these temperatures)
I'd never have known this crap if i didn't live in a village. it's cool :)
To be honest, it's satnav. If any sane person looked on a map they would realise instantly that they'd be better off taking a big detour. Or just not travelling in this weather
I'm also having this problem, the other database(s) is/are unable to store anything in cache, meaning each request takes the full length of time, running the query on the other databases builds the initial response three times faster and caches results (and related results for similar queries) fo...
Yes. Guiding comments and downvotes do not require that
Let it sit for a bit so the poster can see the response to their post
Mods don't like to delete non-spam in the first 12 hours life of the post
IF you feel that strongly about it, tell me when it's been around for 10 days, so that the poster (if they came back) would've seen the comment replies.
After 10 days I'll delete it, but not in the first 12 hours
So the review queue puts it in front of us to flag but mods won't touch it. By the time you would touch it, its no longer in the review queue. What a waste of everyones time.
If I leave a comment, I have to remember to come back later flag it when inevitably the post hasn't changed. I standby by my original point, waste of time.
I'll stick to the spam. The broken windows can accumulate all on their own.
@MarkStorey-Smith Seems to me moderator action did take place based on your flag, so must be some new sense of 'declined' we are encountering here. Helpful but declined, perhaps. LOL.
@PaulWhite I get helpful but declined on some of my SO flags
Of course I am working on cleaning up old questions and a significant number of them I think need to be removed (some I can edit, some I can answer, a few are good questions I can't answer)
Speaking of old ones I am answering, this one may have significant re-use value:
You have two options. The first is to use a polymorphic type which would be cast on call, the second would be to use a refcursor.
Polymorphic type
In this case, you'd do something like:
CREATE FUNCTION foo (bar varchar, baz ANYELEMENT) returns ANYELEMENT AS
$$
SELECT 'test'::TEXT;
$$ la...
@MarkStorey-Smith for a while we had a sticky here from Uncle Sam jcolebrand that newcomers should be treated a bit more softly (like instead of flagging leaving comments and so)
@jcolebrand I find both question and answer hard to parse but I think if I'm being honest I would have commented and deleted that one: the OP can still see the answer and comment when it is deleted I think? If it was a lot shorter I might have converted to a comment as it does genuinely seem tangential and not an answer.
@JNK This is more like dirt under the carpet than a broken window because there are good answers on the same question and the DVs clearly mark it out as 'what not to do'. If it was just a poor answer rather than not an answer at all, I think that would sufficient (and it leaves the door open for improvement).
The obvious solution is to let answers that are "plain wrong" be downvoted into oblivion - where they will serve a useful purpose advertising their wrongness :)
The problem is that there are two kinds of votes:
informed votes from people who could have answered the question themselves or who h...
I'd think quite differently if there weren't contrasting good answers or a bad answer wasn't downvoted
Couple of comments - (a) a low rep user can't see their own deleted posts, and may not be active when any comment comes through (b) the problem with the bad answers is that they'll often get a couple of up-votes before anyone points out they're wrong. So unless subsequent down-votes more than compensate, there are two effects...
(1) the user earns net positive rep (even a +2 -9 is net positive) and so isn't discouraged from posting more bad crap in the future, because it works!
(2) if the user gets +3 -2 it still looks like a "good" answer (e.g. at least one person thought so). And low rep users can't click on the score to see whether that's really +1 or some combination of +-.
And finally, I used to be all for leaving comments to accompany my down-vote. Now I still leave comments here and there, but I'm very careful to not down-vote at the same time, due to certain assholes that will always exist.
@JNK I'm not sure they can see their own deleted posts in any context. Do you have any evidence of this? Also they may still see a comment in the little drop-down, but unless the comment is very brief, they won't see that in their profile either, and still won't be able to read the entire comment. I'm also not sure how long it will stay in the little global notifier thingy.
I guess if they self-deleted they would need to have some way to edit and undelete. I'd have to create a sock puppet account to test.
Other points still stand. When a bad answer gets upvoted, even once, nothing good will ever come of it. The best case is a dog-pile where enough people down-vote to make the guy delete the answer. Sadly with the current reputation scheme that ratio needs to be 5:1.
@AaronBertrand that is true enough in the cases where rep is all that the poster cares about. Take a look at this answer though: I'm glad it is still there despite being wrong. The votes and comments tell a useful story.
Well to be honest there just isn't enough volume of evidence on dba.SE to really pick out trends and behaviors. I posted a really bad answer the other day and got 4 up-votes before I was corrected. I promptly deleted it because they were right.
So when dba.SE gets to the volume of SO, I fully expect similar trends to emerge (perhaps - and hopefully - at a lower rate).
there are a few folks that will get upvoted just for posting. Part of that is if someone disagrees or thinks you are wrong they will be inclined to believe your post regardless
@AaronBertrand no but if you or Paul White or Martin Smith post something that I disagree with or think may be wrong, my first instinct is normally to say "Oh, I didn't know that!"
I was quite embarrassed actually. I have had this misconception about COPY_ONLY databases my entire career - namely that they couldn't be used to restore to a point in time with subsequent logs.
Let's say I have a table called EmployeeSales
EmployeeId | Profit
1 | 10
2 | 15
2 | 5
3 | 20
I could write a query such as
select EmployeeId, SUM(Profit)
from EmployeeSales
group by EmployeeId
to get each employee's total profit.
Now let's cha...
There may be a more elegant way of doing it inside the CTE, but here's my solution.
WITH cte (rowNo, itemNo, dueDate, amountDue) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY [Due Date]) rowNo,
[Item No], [Due Date], [Amount Due]
FROM loans
)
SELECT a.itemNo, a.dueDate,
CASE WHEN c.dueDate ...
On 2012 you can use lag. Also hard-coding the dates looks kind of suspicious to me. I didn't read the question (it's overly long and they didn't bother to specify the version), but are those special case dates that should be hard-coded?
@PolishPrince That's definitely cleaner, but the problem he's having is that he's hitting the recursion limit, which defaults to 100. You can alter it with the MAXRECURSION hint.
Yeah I'd be surprised if it doesn't, given the number of others things that can have that result.
This is about the only one that is actually a logical by-product. It certainly isn't intuitive that it happens due to RECONFIGURE after changing xp_cmdshell or CLR settings or even show advanced options for sp_configure.
But I will confess I have not tested it either.
When you turn the setting on, it should have an immediate impact, e.g. next query knows to compile a stub. In which case the system would have to know to not match an existing plan for the same hash.
I have the following code to create a stored procedure.
The SP should check if a date exists in a table and if it does then pull a value from that date. If it doesnt then pull a value from 2 days ago.
It errors when I try to create it; I get incorrect syntax on the second BEGIN.
USE [Gas...
This answer is getting a lot of upvotes. When upvoting, consider the OP's evident level of skill and knowledge! If she were to implement this solution, would she really know what she was doing, and in six months when another beginner looks at the code, would they? — Jason Swett58 secs ago
@Zane the guy doesn't like that he isn't getting as many up-votes. Against my typical ethics but in this case I gave a -1 for telling other people how to vote. If he didn't have an answer in the race (and it was a more warranted situation in the first place) he might be taken more seriously.
@JNK It looks like SO requires some sort of login but DBA.SE allows guest
The question I found is on SO.
I guess the OP doesn't like my answer either
it makes sense to create a function for this, @jason - thank you. I suppose where I'm struggling is that my results are pulled in from a query, split into something like $row['number_id'] along with other tabular data (and while I despise using tables, if it's tabular data I have limited options). I will work on this one and thank you for helping me think this through farther than just an answer that will work, to get to an answer that will work well. — Melanie Sumner Smith5 mins ago
I have the following stored procedure:
CREATE PROCEDURE [cafgAddCoreID]
@HoldingName nvarchar (50) = null
@CountStart int = 0,
AS
DECLARE @sql nvarchar (50)
DECLARE @bit nvarchar (200)
BEGIN
SET @bit = 'MTR1- + CAST ((' + @CountStart + ' + ROW_NUMBER()
OVER (ORDER BY [FindNo])) AS NV...
I have a script that has to look throught over 2.5 million records to find if a member that has an unread email. I want to know what can be done to improve its speed. Currently it can take up to 8 seconds to run the script:
SELECT TOP(1) MemberMailID
FROM MemberMail
WHERE ToReadFlag = 0
AND To...
Seems like he's basically asking for an indexing tutorial. And I don't find that any of the answers will be satisfactory - sure you can make this one query faster with a good index, but at what cost? No free lunch.
@JNK I just tested it: an even an unregistered user gets the notification of the comment after the answer has been deleted (and can see both answer and comment)
Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automat...
@PaulWhite Just been to your blog. That conference reads like a who's who of SQL Server heavyweights. Anyhow, bundling a $300 cash back or Surface device into the registration?!
how should I write this statement using a subquery?
SELECT customerName, ROUND(SUM(od.quantityOrdered*od.priceEach),2) AS mxm
FROM customers AS c
JOIN orders AS o ON c.customerNumber = o.customerNumber
JOIN orderDetails AS od ON o.orderNumber = od.orderNumber
GROUP BY customerName
ORDER BY mxm ...
Where does google for example, get the url of a page, which then crawls and processes?
I mean the index page, the home page if you prefer.
Is there a database with all the available web pages in the world wide web?
@PolishPrince Out of curiousity, why are you posting in The Heap a request to VtC a StackOverflow question (that's not entirely about DBMS)?? It's valid IMO and is something I might have asked years ago. Even now, I would love to read the answer to the question.
@RichardTheKiwi There are times when we run across flat out bad questions on SO that should be closed, so we will ask in here for some help in closing the questions.
Just IMO, the question may not be brilliantly worded, but I was genuinely interested in what methods Google uses to scour new web sites. Domain registries? Blindly hitting IP addresses?
@RichardTheKiwi If you don't agree with the VtC request, then feel free to explain why. Or don't add a vote, there are plenty of times that I don't VtC when asked. Same with others
@RichardTheKiwi Then the question has multiple issues, including being not constructive and being off topic
@RichardTheKiwi Which just brings us back to, why am I being singled out? For some reason it feels like this isn't the first time I recall you asking me specifically.
@PolishPrince I am not sure you are being singled out. @RichardTheKiwi hasn't been around The Heap for a little while on a regular basis, so he might not be aware that this happens
@PolishPrince and that question does not look like DBA.SE related. You'll have to agree that most VtC requests here are about questions that could appear here.
As Gordon stated in his answer, you will have to unpivot the data in the points table.
An unpivot takes the data from the multiple columns into rows. Once the data is in the rows, it will be much easier to join and filter the data and total the points for each account. The code to unpivot the d...
@AaronBertrand Btw, initial tests on that optimize for ad hoc question I had show that the cache is not cleared when you RECONFIGURE, though it does look like it creates new query plans regardless. I'm going to poke at it further.
@Marian The problem with Gordon's answer is he did not include all of the logic, past 12 months for both active and inactive users. He only included active users
@MikeFal than those plans are quietly going to the bin.. They should be evicted in short time, as long as they are not accessed anymore, right?
@bluefeet He mentioned something about " In fact, you don't even have to worry about when a customer left, since presumably they have not collected points since then."
isn't that an answer to your question? He's basically saying that non active users don't matter
@Marian But that is not what was requested Now, what I want to do is create a query that calculates the amount of points that each customer has. (Current 12 months for active customers, last 12 months they were active for customers who are no longer active.)
Gordon is skipping inactive users all together and it doesn't sound like that is correct
@bluefeet Well, it's more of a matter of logic. Don't give a man fish, teach him how to fish. You both did that..so now the OP goes further as needed. I wouldn't go as far as do 100% for him, but I'm lazy.
Using Standard SQL, how do I make a report with a top title?
Last year I participated in a Database competition. The description said I could use MySQL, Oracle, or MS SQL Server. I choose MySQL because that is what I am familiar with. It said that I must write an sql statement using Standard SQL...
@Marian Should be, though not for certain. I'm investigating. The way I understood it, making an instance config change that could affect the buffer pool (i.e., changing min/max memory) causes the cache to be dumped. I was wondering if the ad hoc optimization would as well, but it looks like not.
From an administrative standpoint, I'd want the cache to be cleared as a 'better safe than sorry' thing, just to make sure old ad hoc plans were not being reused.
@MikeFal Well, yeah, but how do you make sure you're evicting only real ad-hoc plans? What if it's the first execution of a report that ran at that exact point in time? I'd say the LRU algorithm would be a safer choice.
@bluefeet see? that's why you have as much points as Aaron and we don't :-).
@Marian I'm not not all that concerned about evicting only ad hoc plans. I'm cool with a clean slate for everything. Most of this is a curiosity thing, though, establishing the different events that trigger a cache dump.
@Marian Yup, totally cool with doing that in production if I have to. Usually I try to only recompile what I have to, but in the case of this sort of configuration change, I really don't have an issue with the system recompiling everything. It's not like I'm doing a lot of it. :)
@MichaelFredrickson wow, I remember my Oracle 8 days, where I used command line for everything, because I hated the default UI tool. That's more of an output competition than db competition.
@AaronBertrand :O Don't do that.. Stupid questions might just come from me! I'm still downloading that SSMS 2012. Dontcha want some silly questions on it? You certainly need a baseline for a future "Yet another ..." ;))
@MikeFal Mike, I'm not sure what you're intimating with this and the next comment about "trolls", but from my perspective, if I filter out my comments and Aaron's for the last two hours, I don't see how you drew your conclusions. Btw, I only posted twice because the chatroom was telling me that it failed to post
@Marian I'm not sure he's started the new job itself yet. Plus, hard to judge a new job as being a good option until you've been there for 6 months. Always need to wait for the honeymoon to wear off.
@Marian That's the thing, though. Many skills that make a good programmer/dba do not translate to management. Kevin Kline talks about that a lot. It's a hard transition, hopefully I don't ever have to make it. :)
@MikeFal Well I remember something about a tipping point where a man was put in a position higher than his skill. And from there nothing would work well.
@mikefal I did. I'll definitely return. You ran up to talk to Tim and that's fine. I just looked around and thought that I wanted a dr pepper and went to find one and they were gone
@MarkStorey-Smith I am, it's a matter of finding the right contractor situation. I've been shopping around for a firm that fits, but haven't had a whole lot of luck.
Not quite brave enough to go completely independent. :)
You have two options. The first is to use a polymorphic type which would be cast on call, the second would be to use a refcursor.
Polymorphic type
In this case, you'd do something like:
CREATE FUNCTION foo (bar varchar, baz ANYELEMENT) returns ANYELEMENT AS
$$
SELECT 'test'::TEXT;
$$ la...
The obvious solution is to let answers that are "plain wrong" be downvoted into oblivion - where they will serve a useful purpose advertising their wrongness :)
The problem is that there are two kinds of votes:
informed votes from people who could have answered the question themselves or who h...
Let's say I have a table called EmployeeSales
EmployeeId | Profit
1 | 10
2 | 15
2 | 5
3 | 20
I could write a query such as
select EmployeeId, SUM(Profit)
from EmployeeSales
group by EmployeeId
to get each employee's total profit.
Now let's cha...
There may be a more elegant way of doing it inside the CTE, but here's my solution.
WITH cte (rowNo, itemNo, dueDate, amountDue) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY [Due Date]) rowNo,
[Item No], [Due Date], [Amount Due]
FROM loans
)
SELECT a.itemNo, a.dueDate,
CASE WHEN c.dueDate ...
I have the following code to create a stored procedure.
The SP should check if a date exists in a table and if it does then pull a value from that date. If it doesnt then pull a value from 2 days ago.
It errors when I try to create it; I get incorrect syntax on the second BEGIN.
USE [Gas...
I have the following stored procedure:
CREATE PROCEDURE [cafgAddCoreID]
@HoldingName nvarchar (50) = null
@CountStart int = 0,
AS
DECLARE @sql nvarchar (50)
DECLARE @bit nvarchar (200)
BEGIN
SET @bit = 'MTR1- + CAST ((' + @CountStart + ' + ROW_NUMBER()
OVER (ORDER BY [FindNo])) AS NV...
I have a script that has to look throught over 2.5 million records to find if a member that has an unread email. I want to know what can be done to improve its speed. Currently it can take up to 8 seconds to run the script:
SELECT TOP(1) MemberMailID
FROM MemberMail
WHERE ToReadFlag = 0
AND To...
Where does google for example, get the url of a page, which then crawls and processes?
I mean the index page, the home page if you prefer.
Is there a database with all the available web pages in the world wide web?
As Gordon stated in his answer, you will have to unpivot the data in the points table.
An unpivot takes the data from the multiple columns into rows. Once the data is in the rows, it will be much easier to join and filter the data and total the points for each account. The code to unpivot the d...
