I sometimes forget my umbrella at work on the day it rained, and have to bring it back on one of the following days, when, of course it's already sunny and there's not even a hint of an impending rain.
But I'm sure I never look like a dork in such cases. If anything, I probably look eccentric, haha
a request came in last week for a failing sql agent job, by now it has escalated to 'no ldf for tempdb', 'The log for database 'msdb' is not available', 'fcb::close-flush: Operating system error (null) encountered.' and the likes
Using coalesce to handle NULL should not have any performance impact.
Try this once.
SELECT ...
FROM ...
WHERE a.Blah = @Blah AND
coalesce(a.VersionId,-1) = coalesce(@VersionId,-1)
You can add NO COMMIT to your COPY and run ANALYZE_CONSTRAINTS before a COMMIT:
dbadmin=> CREATE TABLE tbl (a int PRIMARY KEY);
CREATE TABLE
dbadmin=> COPY tbl FROM STDIN NO COMMIT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 2
I need to load files in vertica using copy but i do not want duplicate entries. As unique constraint will not be helpful as it will load data and then i may have to delete duplicate entries from ananlyse_constraints().Other is using insert with bit of logic to check wether entry exist in table. I...
@Colin'tHart Funny thing is that the commenter made another answer - where he claims that using isnull instead of coalesce will be ok and the query will use an index.
@ypercube Hmm, not sure. Maybe SQL Server has some special logic to process isnull?
I can't find anything to indicate that. It's just an ordinary function. So using it will prevent the optimizer from seeing an index, unless the index is on isnull(<column>, <value>)
@ypercube That was a useful discussion for me as well.
I've certainly heard it before that ISNULL behaved differently from COALESCE in certain situations. I may even have heard about this particular property as well (that it still allows you to have an index seek if you use it on a non-nullable column), but I wasn't sure and had to verify.
And verifying things yourself is a sure way of cementing your knowledge.
@ypercube Something like that. In the XML plan I can only see that with a nullable column I have a <Predicate> branch and with a non-nullable one a <SeekPredicate> instead.
So it kind of ignores ISNULL (because it knows that that column is not nullable)
Select
(select count(column) From dbo.HugeTable where predicate = 'A'),
(select count(column) From dbo.HugeTable where predicate = 'B'),
(select count(column) From dbo.HugeTable where predicate = 'C')
from dbo.ThisHappens112Times
@AndriyM good answer. @PaulWhite has of course blogged about it. http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx
If I remember correctly there is an issue in the XML version of the query plan when you do it with variables. Something to do with EQ showing where it should be IS. Perhaps a bug generating the XML version of the plan.
@swasheck yes, understood. although she has some good code examples of how to work out what columns are actually in the intermediate and leaf level pages of an index. personally i'd probably take that, and then work out the same for 'missing' indexes, and you can assess how much overlap there is.
@Zane Yep. I thought I would like it more because I am part business monkey and am quite good with Excel. But even 8 or more projects in, it's still just a necessary evil to me.
please guide me how to read this graph to figure out what is cause for this deadlock . I looked for resources online but didn't find clear information. this is my first time to see this graph
deadlock-list
deadlock victim=process49b0a0558
process-list
process id=process49b0a0558 taskpriority=0 ...
@ypercube yes that's it. Holliday and summer activities keeps me from regularly visiting here. About your comment, I don't see the deleted answers but from what is shown here in chat the answer was wrong and you worked it out.
but st pieters is still a tram ride away from the historic center
otoh st pieters & dampoort should be on the same line on most trajectories
just let me know where you want to be & I'll look into that, if you have time for lunch i'll drive you there if i'm at home otherwise I'll fix your train itinary
For SQL Server Days I fly into Amsterdam then I need to get to Utrecht for SQL Saturday, I have a ride from Utrecht to Antwerp, then I need to get from Antwerp to Brussels to fly out, what are my best modes of transport from AMS -> Utrecht and Antwerp -> Brussels?
Time/rush isn't a factor, just wondering what the locals would do
Oh if people would understand that using duct tape to hold the hood down on your car when for an additional nickel I could just buy the bolts needed ...