@bluefeet Concur. IMHO, at best the comment should probably stay for a few hours and get eventually fixed up. SO/SE does allow us to improve answers after all.
Have you ever wanted to view a deleted question even if you don't have the required 10k rep for it?
All you need is the link:
Take for instance a question from the deleted meta questions archive like
Envelope lighting up arbitrarily and unpredictably.
Take the link:
http://meta.stackoverflo...
@RichardTheKiwi yes, he is quite knowledgeable about sql, but I think that that makes it kinda worse that he some times doesn't take the time to polish his answers
@bluefeet And, if you get past the somehow abbrasive language, I have the impression that he is quite reasonable. Might be a language barrier. I do remember some confrontation between you and him, and I think that you were in the right
but, - oh dear lord I'm playing devil's advocate- he may have wanted to say that comment as a compliment to the user, because he acknowledges @bluefeet's skills
@GordonLinoff Not so hard to do when posting a code only answer. Not that this answer isn't upvote worthy, but the comparison is just not fair — LamakMar 21 at 20:19
That was my response to him at the time
@JNK and he said this afterwards:
@Lamak . . . when I wrote that comment, both answers had just the code. The other answer has been modified and much improved with explanation (as is usually the case for bluefeet). — Gordon LinoffMar 21 at 20:21
You can still use an aggregate function to pivot the data. There are several ways that this can be done.
Aggregate with CASE:
select name,
max(case when category = 'A' then category else '' end) CategoryA,
max(case when category = 'B' then category else '' end) CategoryB,
max(case when c...
Greetings,
My name is Rahul and I am recruiter at eTeam Inc. eTeam Inc is a global contingency staffing firm servicing fortune 1000 clients globally.We have an excellent job opportunity with one of our client.
eTeam is a professional services firm with competencies in Data Management, Business Process Automation & Professional Contingency Staffing Services. eTeam works for fortune 1000 companies and large system integrators.
Job Title: Desktop support
Duration: 6 Months
Location: Denver, CO & Louisville, KY
Perhaps something like this? This will compare the current row's datetime to the next row's datetime.
WITH cte AS (
SELECT ROW_NUMBER() OVER(ORDER BY dd ASC) rn,
dd
FROM sample
)
SELECT a.dd, DATEDIFF(hour, a.dd, b.dd) hourDiff
FROM cte a
LEFT JOIN cte b ON b.rn = a.rn + 1
Result
| ...
Yesterday (April 1st, April Fools Day notwithstanding) we had some high-ranking members of DBA.SE banned from chat by moderators/10k users from other sites that did not take an appropriate amount of time to evaluate the situation. The Heap is a place where many 10k+ users hang out and talk about...
I have a SQL Server 2005 table that records each step of a process as shown below
ResourceID EventType Time
ABC123 Job Activated 2013-01-08 10:01:31.000
ABC123 Download Complete 2013-01-08 10:03:32.000
ABC123 Job Complete 2013-01-08 10:07:42.000
XYZ789 ...
@swasheck yep, same here. Some really are warmhearted and try to do their jobs well. Others just read some HR book and start shooting stupid questions :).
@MichaelFredrickson well I'm sure some row estimations are changed, as the same query that Mike or Max tried (not sure who made that query to AdventureWorks) returned different no of estimated rows.
@MichaelFredrickson well, as long as it doesn't bleed, it's a fix.
Still your answer just points at a link that shows what CLR/RegEx is. It is probably better suited as a comment than an answer. IMHO. — Aaron Bertrand34 secs ago
@AaronBertrand IMHO You're right, and your answer has been accepted. I think the other answer is right too, since the asker said "For bonus points.." The guy got an upvote for his contribution. (He should elaborate - you won't get an argument - that's more of a quality issue)
@RichardTheKiwi while his contribution is helpful, it is not really an answer.
If I asked "and for bonus points, how do i do this using pivot" and you linked me to the PIVOT documentation, do you think that's technically an answer?
I think the answer is: when you review a plan in SQL Sentry, you are reviewing the cached plan at the time of execution. When you generate an actual plan in Plan Explorer, you are generating a new plan.
I'm not an old time user of the Heap, but an oldie of DBA.SE. I, as Max said it, think that you shouldn't really shut the cowboys, even if they don't wear a tie daily.. Talking to naughty servers daily isn't really a poetry lesson. So please don't ban anyone for just dropping the F bomb or some other gentlemen's locker chat. Thank you! — Marian1 min ago
@Marian One last pointless factoid then I'll stop bothering everyone with this weirdness... it doesn't scan if I only select a few columns... but when I select *, then it scans with the parameterized like.
Ok, can you send me the plan and tell me what is showing differently? Also can you tell me what build of Plan Explorer and what build of SQL Sentry? The codebase is the same, so if we make a fix in Plan Explorer, and you have the latest build of that, but your SQL Sentry client is a year old, you won't have all the fixes.
@AaronBertrand Ah... OK, plan reading fail on my part... when the like uses a parameter there is a seek, and then there is a separate scan on a different index than the one that is seeked that covers the few columns that are returned...
Good point... let me make this a real question...
Let me see if I can recreate it in a bastardized AdventureWorks...
@Zane ok. You know my e-mail address right? Plans and issues are always welcome there. Hopefully I can explain it. Estimated rows where? Coming out of a specific operator? Which type of operator? We do some corrections for certain operators (since SSMS totally underestimates certain scenarios by not multiplying by number of executions), but we didn't get them all perfect at the beginning.
@Zane So I suspect you have a fairly newish build of Plan Explorer but an old version of the SQL Sentry client.
@Marian SELECT * used a seek for what part of the query? Didn't that also add a lookup?
Eep replied to the wrong post because the chat window shifted
@AaronBertrand from what I gathered by the performance of this query Plan explorer is correct and SSMS is underestimating. And yes it is one specific operator. I'll send you the plan tomorrow. Thanks in advance.
@AaronBertrand didn't see his plans, we only chit chatted here in the room. But textual differences yes, one was declaring a variable and using it, one was not.
@Michael Isn't it a case of plan stability? The first one is stored for the text which ends with "like @criteria", knowing that the value for that param may change in the next invocation of the plan from the cache. The second one stores a plan for a SQL Statement that ends with 'like 1234%' which can use an index on serialNumber optimized to search for the prefix '1234'
For example, say SQL Server puts a plan of "seek on index against serialNumber, RID lookup" (or bookmark if you have a clustered index) for the query `select * from dbo.device where serialNumber like @criteria` Then someone fires the same query (same SET settings etc) and uses `@criteria = '%A'` - you'd end up with the wrong plan
I hear you @Michael about that formatting in chat...
@RichardTheKiwi So what you're saying makes sense... but other people are reporting that using a parameter with like uses seeks as long as the value of the parameter is SARGable...
@MichaelFredrickson but it also depends on what plan(s) have been cached before it. Same behavior when you add OPTION (RECOMPILE)?
I think I'll need a full repro to even start to comment. However I am not installing SQL Server 2005 for this. And I hope you have a very good reason for leaving this table as a heap.
@AaronBertrand I was going to suggest OPTION (RECOMPILE) too but it seems 2005 doesn't do anything with it. I have a varchar index on table clustered on an int column, and the parameterized query with OPTION (RECOMPILE) still generates a clustered index scan.
Ah yes, 2005, duh. So glad I don't have to deal with that on a regular basis.
If the code is in a stored procedure you could play with WITH RECOMPILE at the module level rather than statement level. Might not help but would be interesting to try.
I take it back. SQL Server is lying. Ctrl-M actual plan shows Index SEEK when OPTION (RECOMPILE) is in use. Ctrl-L estimated shows Index SCAN. Both after DBCC FREEPROCCACHE.
@RichardTheKiwi weird I thought OPTION (RECOMPILE) was added in 2008. Shows how long it's been since I've dealt with that...
I thought that statement-level recompile wasn't really available in 2005. Maybe it's statement-level recompile inside of a module that isn't possible in 2005 - all or nothing.
Egads I love modern versions of SQL Server. Don't have to think about all this stuff.
at any rate ... i tried doing just a STDEVP(wordcount) but because it's an aggregate and came back as NULL because of my grouping sets ... i was just going to ask if i was calculating on the correct data point.
I have query like this:
`SELECT [/BIC/IORSVPTX] as Region,
COUNTRY_ID,
[/BIC/IOWCNTRY] as Country,
[/BIC/IOC_TRLNO] as Trial,
[/BIC/IOWQUAL] as ResourceType,
case
when [/BIC/IOWQUAL] like '%Supporter%'
then 1
when [/BIC/IOWQUAL] like '%Monitor%'
then 3
when [/BIC/IOWQU...
I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek occurs instead.
The issue only happens given this specific scenario... so I'm not confused as t...
Have you ever wanted to view a deleted question even if you don't have the required 10k rep for it?
All you need is the link:
Take for instance a question from the deleted meta questions archive like
Envelope lighting up arbitrarily and unpredictably.
Take the link:
http://meta.stackoverflo...
You can still use an aggregate function to pivot the data. There are several ways that this can be done.
Aggregate with CASE:
select name,
max(case when category = 'A' then category else '' end) CategoryA,
max(case when category = 'B' then category else '' end) CategoryB,
max(case when c...
Perhaps something like this? This will compare the current row's datetime to the next row's datetime.
WITH cte AS (
SELECT ROW_NUMBER() OVER(ORDER BY dd ASC) rn,
dd
FROM sample
)
SELECT a.dd, DATEDIFF(hour, a.dd, b.dd) hourDiff
FROM cte a
LEFT JOIN cte b ON b.rn = a.rn + 1
Result
| ...
I have a SQL Server 2005 table that records each step of a process as shown below
ResourceID EventType Time
ABC123 Job Activated 2013-01-08 10:01:31.000
ABC123 Download Complete 2013-01-08 10:03:32.000
ABC123 Job Complete 2013-01-08 10:07:42.000
XYZ789 ...
I have query like this:
`SELECT [/BIC/IORSVPTX] as Region,
COUNTRY_ID,
[/BIC/IOWCNTRY] as Country,
[/BIC/IOC_TRLNO] as Trial,
[/BIC/IOWQUAL] as ResourceType,
case
when [/BIC/IOWQUAL] like '%Supporter%'
then 1
when [/BIC/IOWQUAL] like '%Monitor%'
then 3
when [/BIC/IOWQU...
