Well, I have a solution, but I still think the requirements are ridiculous. This assumes that the source table (in my case, @t) has at least one row. If you have zero rows, what exactly are you demoing?
DECLARE @count INT = 17; -- here is whatever your 'X' is
DECLARE @t TABLE(i INT);
INSERT @...
@AaronBertrand i have a quick question regarding filtering profiler traces. do they pull everything and then filter at the client? meaning, if i filter by database name in a profiler trace, does the trace collect all events for all databases but only present the ones that i've requested?
@swasheck do you mean server-side trace or do you really mean using profiler to run a server-side trace? If it's the latter, and you're asking the question... :-)
@AaronBertrand sorry. i'm really pretty muddy today (wisdom teeth extraction yesterday = hydrocodone today) so i'm probably missing something. also, i know less about traces than i know about XEs so my brain only thinks in terms of XEs. i suppose i should go read about profiler traces, i guess.
@BobbyScon You're going to have to provide better sample data, period. I can't troubleshoot why the query somehow starts at 11/26/2012 when the only mention of that data point is in the word problem. — Aaron Bertrand20 secs ago
Sure. Don't think of it as rendering rows in a table. Think of it as showing a bunch of tiles in a Metro UI. The specific use case would be demos of the software. The salesperson sets up a brand new instance and there are blocks in the UI that show random items that meet a certain criteria. If there isn't enough data in the system to fill out the UI, because no content very little content has been created, they don't want big empty holes in the UI. It doesn't look "pretty". — Seattle Leonard9 mins ago
So basically, if there is one Big Bird icon in the table, and they want to show 40 tiles on the screen, they want 40 Big Birds instead of 1 Big Bird and 39 empty tiles.
I still think it's kind of silly, even after he's explained it, but at least now that he's explained it, it's not the absolute silliest thing I've seen today
@swasheck Sorry, I'm not trying to be a jerk, but I think it depends on the type of filter (event/column) and what event/column you're filtering on. Like you, I don't really do much server-side trace these days, much less profiler. I don't know off-hand when database_id is filtered, but I do know that if you trace to a file or table, the filtered rows don't end up in the file or table. Just not sure where they're dropped.
@swasheck in all honesty I'd spend more time getting your colleagues up to speed on XEvents than trying to figure out just how bad their profiler traces are.
@AaronBertrand i know you're not trying to be a jerk - i didnt think you were. i just didnt want to misrepresent the benefit of an XE session over a trace. i omitted that line from the email and will carry on.
Both constructs suffer from the same issue where you may get a plan that works well for one value of blah but not for another. What benefit do you perceive from the CASE expression? The only benefit I could see is forcing order of evaluation if you know, for a fact, that column1 is always more likely to be equal to blah than column2. And even that isn't guaranteed.
@AaronBertrand I don't see any benefit that's the problem. An item we are trying to push out uses that OR logic and I don't see why that should be the case.
@JackDouglas well, on the CASE, you reversed the logic (added NOT) and changed the condition (=0 became =1). On the OR you only changed the logic (added NOT). To be equivalent you should have changed OR to AND in that one.
I have a task and I don't know how solve it :)
So, we have a lot of customers who should use SQL Server for their applications.
Design is standard and simply:
Customer -> Remote Desktop -> AppServer -> SQL Server
So, now we plan create a SQL Server cluster ('active/passive' or 'active/activ...
@Zane well that's what I was getting at. They'll both yield the same plan, and they'll both re-use that plan even for values that don't make sense. Which path you choose is kind of like choosing whether to fall on a steak knife or a cleaver.
@ypercube hey I've been to Greece before. If and when I come back I can find you
So if they want to enforce this rule, why don't you push back the onus on them to prove that there are scenarios where it matters?
That is going to be easier to prove than proving there are NO cases where it matters. But it might also flick on a light bulb somewhere that maybe the rule should be relaxed and limited only to the cases where they've proven it matters.
Think of the difference between proving there are bears in the nearby forest, and proving there aren't.
I am going to push back and already have those test cases to back it up. I just wanted to make sure no one on here had found a reason that one option would be superior.
Just the one that I mentioned - SQL Server may evaluate a set of ORs in any order, whereas CASE - in most situations - will order left-to-right. Whether you gain anything from such short circuiting is a different story. Personally I've found it more stable to just build the query with dynamic SQL instead of using OR or CASE
@FreshPrinceOfSO it doesn't always work out noticeably better, and sometimes it's negligibly worse, but I'd rather have the ad hoc workloads setting on and know that every plan I'm using makes sense for the chosen parameters, than to have a plan that makes sense for some but makes absolutely no sense for others.
I'm also not afraid to pay recompile costs, but generally I work with queries that don't have high compile costs in the first place. This isn't for everyone, obviously.
I don't mind other people giving answers on the post at all, I was hoping others would add what is necessary. But this isn't what I had in mind. It's a repeat, some odd advice like "an offline database", etc. Maybe I'm being unfair :)
If you don't mind I can give you the code what i am trying to do right now. Please help me to write the best efficient store proc. I know I am asking too much but, this will help me for my complete project. — user18827051 min ago
@FreshPrinceOfSO in which case we will launch a full-out Heap assault. you've taken the frontal assault, the rest of us will flank, surround, and annihilate.
@AaronBertrand probably my crappy browser but I'm seeing this on your web page.
Warning: Invalid argument supplied for foreach() in /home/sqlperf1/sqlperformance.com/app/wp-content/plugins/ultimate-category-excluder/ultimate-category-excluder.php on line 133
> Texas Rangers, Sooners, Stars, Blackhawks, Islanders, Red Sox, and Liverpool FC fan. Friendly desktop support guy. Not really a calico cat or black lab.
Desktop Support - where you have to be friendly to everyone.
@Phil it appears as though you've referred to the same woman by two different names
@MikeWalsh yes. i remember the right bottom tooth and the feeling of them digging in my jaw. everything else i've completely forgotten. i may have farted in the room many times and i never knew it.
@swasheck I remember the middle of my extraction. This little moment of clarity. The oral surgeon said something like "give him a little more, he's waking up" and then they pushed some more versed and I forget the rest of the procedure until my wife was in the recovery room
well maybe not waking up.. but "he looks uncomfortable" or something like that. :)
@swasheck Yeah IV starts don't always feel that great. Especially if you have someone not good at it. That part of EMT-I and Paramedic class sucked.. Letting your classmates stick you..
@swasheck Really? That's standard here in NH (NREMT state) for classes at the Intermediate (well AEMT now, I guess, let my ticket expire when life got busy and family grew) and Medic (though thankfully most students in medic classes are I's first)... Always had to practice IV starts on one another.
@swasheck yeah. Though it may just be a way that NH likes to have their educators do the training. Actually makes sense to have the first starts be on suspecting classmates instead of unsuspecting patients having to experience a student.. It's been awhile since I've seen or smelled the back of an ambulance, but I still get annoyed when I am a party to a bad IV starter..
You know right away when they start slapping your arm like crazy and asking you to pump a bunch of times.. Especially when I'm giving blood and you can see my AC from across the room ;-)
@swasheck :-) I miss it.. Once the mortgage is paid off, I'm going to start from scratch and get my licenses again and do it part time.. Miss it a lot. (Well.. not all of the calls.. I miss the fun traumas, the interesting medicals, cutting people out of cars, doing interior attack at a fire, etc... I don't miss the stubbed toe wanting a free ride to city)
or the "I've been constipated for two days and I want an ambulance ride, and I don't care about getting back because the hospital pays for a taxi for me and I don't pay for the ride, and you can't refuse me so take me now" kind of calls. I don't miss those :)
@MikeWalsh i had a lot of fun experiences. and a lot of bad ones. the "lizard farm" was an awful place to go. i got to witness a really nasty crime scene (slashed throats with the body still reacting to a PCP OD). i got to tube someone in a five-alarm fire.
@swasheck That'll do it. My town is an all Volly town, safe for a day time crew of two. Priorities changed when the kids started coming out and getting older and I realized I was missing too much time with them.. Will go back later to it, though. :)
@swasheck Yeah never saw a PCP OD, wouldn't like that.. Pushed the Protocol Narcan once to a guy out of it on Heroin.. That was the last time I gave that much Narcan to someone ;-) Still nothing compared to a PCP OD I imagine
@MikeWalsh looked like he was still alive. it was actually 100% head to convince me he was dead. the gut reaction was "holy crap he's still alive!!!! and moving for the knife!!!" in my hospital rounds i got to do the Narcan. that was fun. and then there was a guy saying that he was vomiting blood so he couldnt go to work. boss told him to go to hospital and provide the bill. so we tubed him. it was "payback" because NG tubes are AWFUL
@MikeWalsh i wrestled with that for about 3 years
@Kin no problem. i am just interested in hearing the resolution because it could go a long way in resolving some of my issues
@swasheck yeah never played with NG.. Actually left Medic school in the middle. Realized that family was way too far down on my list of priorities.. And had a bad call with a local idiot on the whacker squad.. Almost broke a Pts neck.. Had to write it up..
@swasheck and realized that "If I get my medic.. I'll be the highest license in this region.. and if I stay volly and work IT, I'm responsible for these people... And if I quit IT and do this full time, we'll be moving out of our house or I'll never see home from the OT"
@MikeWalsh nah. academia is a facade of intellect - at least in the US. academia is more about how much $$ you can bring to the institution by playing the game. i envision more of an eastern european/turkey sort of academia
UOC (my alma mater) had a large endowment called the Erskine Fund. It was one of the most academic of the universities in New Zealand, precisely because they could stay in their ivory tower to some extent.
They got away with some nice techniques to separate the men from the boys. My favourite was teaching functional programming at Stage 1 and requiring a pass in that course as a pre-requisite to get into Stage 2 computer science.
@swasheck Eventually they sold out and taught Java in Stage 1 but they they went to Python for COSC121. I still think they should go back to Haskell for COSC122.
@swasheck Eventually they sold out and taught Java in Stage 1 but then they went to Python for COSC121. I still think they should go back to Haskell for COSC122.
@Zane and are you seeing this on the page or in the javascript console or elsewhere? Do you have any add-ins/plug-ins enabled? Unfortunately I don't have a single Windows VM where I can install IE7 to test.
@SimonRigharts You see it a bit in the city. Algorithmic trading types use it because you can do correctness proofs before you deploy it. Helps not to have bugs in your algo trading systems lest they place buy orders for the entire LSE.
@swasheck I like it too. It's one of my go-to languages for hacking up utilities to do this and that.
@swasheck You can do application development in it - there are quite a few web frameworks (Django etc.) for it, and there are mature bindings for most GUI toolkits like QT.
@ConcernedOfTunbridgeWells oh sure ... i'm just saying that my personal affinity for it is how easy it is to pick up and just get it to do something useful
@swasheck It's very good at that. Another thing it's quite good for is frigging with COM or .Net APIs. You can run up an interactive shell and use it to query the API.
Setup: SQL Server 2012 with data being used in a line graph in SSRS using Report Builder 3.0
I have a table with sales data with the first transaction being on 10/10/2012. This table gets written to regularly updating the sales information and will continue to be written to into the future (curr...
Thank you Aaron Bertrand for giving a real answer. I ended up creating my own.
DECLARE @count INT = 10;
DECLARE @ids TABLE(id INT);
WHILE ((SELECT COUNT(*) FROM @ids) < @count)
BEGIN
INSERT INTO @ids
SELECT TOP(@count) ID
FROM Things
ORDER BY NEWID()
END
SELECT T...
@Phil for the record I thought cuntsultancy was hilarious, but I don't know what the context was, so if it was demeaning that wasn't right, but if it was just taking the piss I would've laughed my ass off.
Well, I have a solution, but I still think the requirements are ridiculous. This assumes that the source table (in my case, @t) has at least one row. If you have zero rows, what exactly are you demoing?
DECLARE @count INT = 17; -- here is whatever your 'X' is
DECLARE @t TABLE(i INT);
INSERT @...
I have a task and I don't know how solve it :)
So, we have a lot of customers who should use SQL Server for their applications.
Design is standard and simply:
Customer -> Remote Desktop -> AppServer -> SQL Server
So, now we plan create a SQL Server cluster ('active/passive' or 'active/activ...
Thank you Aaron Bertrand for giving a real answer. I ended up creating my own.
DECLARE @count INT = 10;
DECLARE @ids TABLE(id INT);
WHILE ((SELECT COUNT(*) FROM @ids) < @count)
BEGIN
INSERT INTO @ids
SELECT TOP(@count) ID
FROM Things
ORDER BY NEWID()
END
SELECT T...
