You could read a book, like Grant Fritchey's free eBook on SQL Server Execution Plans. This isn't really a good format for questions here, since someone could literally paste the entirety of that book as an answer. What would be the point of that, when you could just go read the book directly? Tons of other resources out there too. Try a search engine. This isn't a "do my research for me" site... — Aaron Bertrand3 mins ago
I have pretty good gear and yesterday applying a CU to an Express instance took about 20 minutes. I don't know what kind of shit Windows does under the covers but I don't like it.
I feel bad now because I gave the same guy crap the other day for blogging about how cool it is to use Powershell to call sp_who2 (using 20+ lines of code and comments).
@AaronBertrand Eh. If someone's being stupid, call them on it. Concatenation by recursive CTE seems like a remarkably bass-ackwards way of doing it
Also people self-answering questions that are leading people down bad paths doesn't really sit well with me. i.e. the question should be more "how to do something in SQL Server", not "how to do something (using specific-technology-that's-not-really-well-suited-to-the-task) in SQL Server"
@BlueBerry-vignesh4303 That would be a good question for ServerFault. If you create a question there with some more details about what you're trying to do, I can answer there.
You could avoid the WithMax step by just adding a COUNT(*) OVER (PARTITION BY C.State) to NumberedCounties. But really, if you feel this can be useful despite the fact there are better concatenation methods, why don't you elaborate on why (as in "give an example where using this particular method is preferable"). — Andriy M1 min ago
do TCP keep-alive packets reset the ASYNC_NETWORK_IO waits for a session?
I have this query (running on a SSIS package) that only returned a single row, and has the thread on node 0 waiting for ASYNC_NETWORK_IO, but it never seems to exceed 2s...
OK, that doesn't make any sense, because there's examples of ASYNC_NETWORK_IO having much higher wait times that a typical TCP keep-alive interval. Now I'm puzzled on why this is happening...
another thing that I would expect, is the session for the insert waiting for a lock on the table or something... it's a sleeping session with lastwaittype on sysprocesses "MISCELLANEOUS"
Is it possible the lookups are still trying to load their reference data? If one were blocked that might explain what you're seeing. Awaiting command basically means the insert hasn't started yet.
Besides, the source session indicates it has sent 1 row, which seems like a suspiciously low number e.g. might be the result of some single row SELECT the source component issues before the full read gets underway. What does DBCC INPUTBUFFER show for the source connection?
@PaulWhite it's using a lot of conditional splits and union all tasks due to this inferred member pattern they're using, and the rows are pretty wide (WSTR all over).
but yes, I agree it's taking more time than it should
@PaulWhite Can you imagine the disruption to the world economy if that ever gets invented? No more cars, no more trucks, no more trains, planes, gasoline, ships, and all the resources necessary to build those things?
I kind of liken it to the invention of radio communications. No one ever thought you'd be able to instantly communicate around the world, however it clearly was possible and only took around 100 years until we are able to communicate massive amounts of data over very large distances. I imagine something like a tesseract that can fold space or something very bizarre that doesn't need a huge amount of power. One can only hope I suppose.
I can sort of imagine how time travel to the future would work without gross paradox, but travel to the past seems to require multiple universes, which might well be physically consistent, but hardly useful.
Maybe if we travelled far enough forward there would be a version of replication that ever caused anything aside from pain.
@PaulWhite I can only imagine traveling forward. Backward appears much more difficult to comprehend. Also, if travel backwards were possible, surely we'd see travelers from the future?
Even if you could only view the past, it might still be more harm than help. Was it Isaac Asimov who likened the idea with that of living in aquarium? (in one of his stories)
@AaronBertrand thanks for that tip about CONVERT. I hate using CONVERT for date time conversion since the possible output formats SUCK SO MUCH. Hence I shy away from that sucker. Glad to know I can use to convert binary values for comparison purposes.
Convert is great because for example if your strings are dd/mm/yyyy you can just use style 103. With cast you have to use things like SET DATEFORMAT, ugh
I use cast because I don't have to think about it. Most of the time. And typically I don't actually cast datetimes to varchars, shudder
I really like the Format() function so much better. It just works the way I want it to. Having said that - I almost always do the conversion in the front-end code, where it should be done
@MaxVernon so just along the lines of my consistency argument, when you don't need the style parameters, you don't have to use them. When you do need them, I find it much easier to add ,103 than to re-write a cast as a convert. Not a big deal either way, but I don't see any advantage to using CAST only in the cases where you don't need convert.
@MaxVernon FORMAT() is pretty but it is extremely expensive.
At one time you could down-vote your own item (say, this is a bug but I don't think it's worth fixing, just document it), or abstain, but now it automatically votes for you on creation.
And you can't undo the up-vote - you can only change it to a down-vote.
@MikaelEriksson I've cleaned out that 0x001A (except in plain text) and that validator thing still doesn't like it. Do I need to entitize 0x001A even in plain text? That must wreak havoc on RSS feeds everywhere?
@AaronBertrand I use Thunderbird at work and it has been awfully silent for a while from SQLPerformance.com. I think it is OK now but I will check the feed in Thunderbird tomorrow. I will let you know how it goes.
@mmarie The issue isn't pay it's debt. Even when I get my raise it will just increase the debt payback rate. I will still be equally poor. Just out of the hideous shackles of debt faster.
@AaronBertrand And this description "This feed is valid, but interoperability with the widest range of feed readers could be improved by implementing the following recommendations."
So valid but could be better.
And that is in an old post so it should work now since it worked before.
Remind me to go with my gut on whether I should put the effort into providing an answer.
Thanks a lot @billinkc. I will go over it. I did sthg like this to handle and it seems like its working it. ([Drv DOB] == "0") || ([Drv DOB] == "101") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2)) . It handles 0 and 101 effectively and as well as output the right format for DOB. — apariyar10 mins ago
@MaxVernon Thanks. It's going to be a blog post because I've never had to try and implement the 2 to 4 year boundary logic before in SSIS. Seemed like a fun challenge
@billinkc Thank you for taking the time to produce a comprehensive answer that gives me the benefit of your years of experience for free. I might read it later. Meantime, I hacked something fragile together that future maintainers will hate me for.
We have a database which is set up in Merge Replication.
We accidentally updated hundreds of thousands of records in a large published table on the publisher database.
We did is something like this:-
update LargeTable set Field=Field
So basically, every record got updated to be the same - no...
We have a database which is set up in Merge Replication.
We accidentally updated hundreds of thousands of records in a large published table on the publisher database.
We did is something like this:-
update LargeTable set Field=Field
So basically, every record got updated to be the same - no ...
We have a database which is set up in Merge Replication.
We accidentally updated hundreds of thousands of records in a large published table on the publisher database.
We did is something like this:-
update LargeTable set Field=Field
So basically, every record got updated to be the same - no...
wholesale sexy corsets from online store.many diffirent quility shoes.so visit the website.
http://www.tidestore.com/Wholesale-Sexy-Corsets-100496/
I have done something like this and it works.
([Drv DOB] == "0") || ([Drv DOB] == "101") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2))
We have a database which is set up in Merge Replication.
We accidentally updated hundreds of thousands of records in a large published table on the publisher database.
We did is something like this:-
update LargeTable set Field=Field
So basically, every record got updated to be the same - no...
