August 16, 2011 at 4:07 am
How about using the intersect operator ?
DECLARE @a int = 123,
@b-2 int = 123 ,
@C int = 123
;
select case when v IS not null then 1 else 0 end from(
select * from(select @b-2
intersect
select @a)t(v)
intersect
select @C
)t
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
August 16, 2011 at 7:13 am
R.P.Rozema (8/16/2011)
Sean Lange, I hadn't yet seen the additional requirement of skipping 0's when I wrote my reply. That is why yours looked over complicated to me. Sorry about that.Of course this additional requirement can also be rather easily implemented using = all. But as Jeff demonstrated, = all performs slightly worse than conventional logic, so I won't even bother putting an example up. Still if you have a need for testing a set of scalars for which you do not always know the exact number of entries in it, the all, any and some operators may still be of use. Nice to see that even the BIG people on this forum still encounter some "new" things. SQL server is cool :cool:.
No worries. It seems that I too had missed some of the requirements. The target was kind of moving on this for the first couple posts. You are right about SQL server. It is such a huge topic that everybody learns new stuff all the time. And this community is awesome that (most people at least) can put aside their egos and be professional and learning all the time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2011 at 12:06 pm
SQLSandwiches (8/16/2011)
I was just going for 3 variables but I do appreciate the explanation.So if ALL isn't useful. Why still keep it around? MSSQL is released every couple years. You'd think they be watching these sites. (not trying to open up a can of worms or anything)
ALL is useful just as PIVOT is useful. It's another way of doing the same thing and some people understand one way easier than another. I just haven't used ALL for anything except demonstration purposes because, as in my example code, I've found it to be slower than traditional methods. PIVOT falls into the same category... it's useful for people who don't know how to do Cross-Tabs (especially very high performance pre-aggregated Cross-Tabs). I don't use PIVOT because I've also found PIVOT to be slower and (sometimes) much more difficult to code than traditional code.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 12:17 pm
R.P.Rozema (8/16/2011)
However, in situations where readability is more important than raw execution speed
You're absolutely barking up the wrong tree with me on that subject. 😉 For me, performance both in duration and resource usage, is secondary only to accuracy. If "trick" code is necessary to get the extra speed I need, then I'll simply explain it with a terribly underused feature of T-SQL... Comments. 😉 I have also found that "trick" code usually isn't necessary.
In that same vein, my personal opinion is that ALL is not more readable than the standard code that can replace it. I also find the ALL code to be closer to RBAR than the standard code because it frequently requires "row matching" through the use of correlation. All that, notwithstanding, if ALL turns out to be the fastest for a given task, then I'd use it regardless of my personal opinions on readability. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 12:52 pm
Jeff Moden (8/16/2011)
R.P.Rozema (8/16/2011)
However, in situations where readability is more important than raw execution speedYou're absolutely barking up the wrong tree with me on that subject. 😉 For me, performance both in duration and resource usage, is secondary only to accuracy. If "trick" code is necessary to get the extra speed I need, then I'll simply explain it with a terribly underused feature of T-SQL... Comments. 😉 I have also found that "trick" code usually isn't necessary.
I have to agree with Jeff here. If you have a piece of code that might not be easy to understand, use a comment. Sacrificing performance, when the database is a single bottleneck that is extremely hard to scale out, is not worth it. Developers should build better T-SQL skills rather than asking for easier to read and worse performing code.
August 16, 2011 at 1:34 pm
SQLSandwiches (8/15/2011)
Thanks Sean, but I'm not sure that would work.
DECLARE @a int = 123,@b-2 int = 0 ,
@C int =102733019
select case when
or @a = 0
or @b-2 = 0
or @C = 0
then 1
else 0
end
If A and C are different and B is zero, it still returns 1.
What about:
DECLARE @a int = 123,
@b-2 int = 0 ,
@C int =102733019
set @a = isnull(@a , 0)
set @b-2 = isnull(@b , 0)
set @C = isnull(@c , 0)
select case when
and ( @a = @b-2 or @b-2 = @C or @a = @C )
)
then 1
else 0
end
?
Regards,
Iulian
August 16, 2011 at 2:57 pm
R.P.Rozema (8/16/2011)
Jeff Moden (8/16/2011)
what else could you use ALL for? I ask because I've never found the need to make sure that multiple column values in the same row were all identical.Jeff, the answer to that question is in your message footer 🙂
"First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
"All", "some" and "any" are most useful when working with values in a column in a set of rows that must all have a particular value, be over/under a particular value, or not any having a particular value. There are alternative methods to using these operators (as there are always alternatives for everything in T-SQL), f.e. using sub queries. However, in situations where readability is more important than raw execution speed, these operators can help for nicely readable and compacter T-SQL code. If only more people knew about these operators.
I'm not presenting this as the way to do it, there are (better) alternatives. This is just an example to show that these operators may make T-SQL more readable for people less familiar with the language. This represents a small job system where multiple steps may need to be run in parallel for particular jobs. Each job's status depends on whether or not any or all of the job's steps are done and if all are done, their outcome.
declare @jobs table (jobID int not null
,primary key( jobID)
);
declare @steps table (
jobID int not null,
action varchar(10) not null,
done bit not null default 0,
result int null
);
insert @jobs(jobID)
select 1
union all select 2
union all select 3
union all select 4;
insert @steps(jobID, action, done, result)
select 1, 'process 1', 1, 0
union all select 1, 'process 2', 1, 0
union all select 1, 'process 3', 1, -5
union all select 1, 'process 4', 1, 0
union all select 2, 'process 1', 1, 0
union all select 2, 'process 2', 1, 0
union all select 3, 'process 3', 0, null
union all select 4, 'process 2', 1, 0
union all select 4, 'process 3', 0, 0;
select j.jobID as [job ID]
,case
when 1 = all (select done from @steps stp where stp.jobID = j.jobID)
then
case when 0 = all (select result from @steps stp where stp.jobID = j.jobID)
then 'Done'
else 'Failed'
end
when 1 = any (select done from @steps stp where stp.jobID = j.jobID)
then 'Busy'
else 'Waiting'
end as [job status]
from @jobs j;
The output of the final query:
job ID job status----------- ----------
1 Failed
2 Done
3 Waiting
4 Busy
(4 row(s) affected)
Good code. Let's cover the full life-cycle of the code. Let's say the boss changes the requirements, now. In addition to all that is currently displayed, the new requirements are to display the count of steps still running (Done = 0) for each job and to display an extra status of "Failed, still running" (at least one step for the given job had Done = 0 with at least one failure in the Result).
How would you modify your code to do that? By adding another nested case?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 4:55 pm
I know, the example was far fetched. And as I said: there are far better ways to implement the example. Still I don't share your idea that readability is always less important: Not all people reading T-SQL code are T-SQL programmers. Sometimes you need to explain/give (parts of) the functionality to people not familiar with reading T-SQL: application developers, (junior) report builders, support people, even end-users or heaven forbid: managers ;-). It is not that they can not understand the tricks. T-SQL simply isn't their core job, so they don't know them. Would I put f.e. your cross tab in such a script, I would find myself having to explain the workings of the script to them instead of them picking up on the functionality. These scripts usually don't tend to be run often nor are they too heavy on the server (or I will not give it to them). This kind of code I want to be clear and simple, even when that means it is not the optimal solution. So the real answer to your question on my example is: "yes, in such a script, when my boss tells me to add the extra status and I need him to understand what is going on, i will add another nested case even though I know ways of better implementing it."
For pretty much all other code I fully agree with you: resource usage and speed prevail over readability. Luckily we've got you to find out and show us the best options: I already decided I will not use all, any or some in production code after I had seen your measurements, even though the difference is really small: there is a better alternative so I will use that. Can we agree to disagree -a little- on the readability?
August 16, 2011 at 9:32 pm
R.P.Rozema (8/16/2011)
I know, the example was far fetched. And as I said: there are far better ways to implement the example. Still I don't share your idea that readability is always less important: Not all people reading T-SQL code are T-SQL programmers. Sometimes you need to explain/give (parts of) the functionality to people not familiar with reading T-SQL: application developers, (junior) report builders, support people, even end-users or heaven forbid: managers ;-). It is not that they can not understand the tricks. T-SQL simply isn't their core job, so they don't know them. Would I put f.e. your cross tab in such a script, I would find myself having to explain the workings of the script to them instead of them picking up on the functionality. These scripts usually don't tend to be run often nor are they too heavy on the server (or I will not give it to them). This kind of code I want to be clear and simple, even when that means it is not the optimal solution. So the real answer to your question on my example is: "yes, in such a script, when my boss tells me to add the extra status and I need him to understand what is going on, i will add another nested case even though I know ways of better implementing it."For pretty much all other code I fully agree with you: resource usage and speed prevail over readability. Luckily we've got you to find out and show us the best options: I already decided I will not use all, any or some in production code after I had seen your measurements, even though the difference is really small: there is a better alternative so I will use that. Can we agree to disagree -a little- on the readability?
Nah... not far fetched at all. It's actually a great example, simple to understand, and much more common than many people would think. The folks in my shop frequently write code of the same basis. Thank you for taking the time to post it.
I can't agree to disagree with you about readability because I DO agree that, usually, there is no need to sacrifice readability for performance because the two usually walk hand-in-hand. 😉 Using your fine example problem, you'll see that the following code is no less readable than what has already been presented. It's just not THAT "tricky". 😀 I will state, however, that I write code with the idea that I'm writing for T-SQL programmers to understand and not Managers. They can read the commments. 😉 That, notwithstanding, I usually don't write code that I can't explain to a Manager even if it's some seriously "trick" code.
WITH
ctePreAgg AS
(
SELECT JobID,
ActiveSteps = COUNT(Done) - SUM(CAST(Done AS SMALLINT)),
ResultSum = SUM(Result) --Only "0" is a "Pass"
FROM #Steps
GROUP BY jobID
)
SELECT JobID,
JobStatus = CASE
WHEN ActiveSteps = 0 AND ResultSum = 0 THEN 'Done'
WHEN ResultSum IS NULL THEN 'Waiting'
WHEN ActiveSteps = 0 AND ResultSum <> 0 THEN 'Failed'
WHEN ActiveSteps > 0 AND ResultSum = 0 THEN 'Busy'
ELSE 'UNKNOWN'
END,
ActiveSteps
FROM ctePreAgg
;
Just to drive a point home (and yes, I absolutely agree that's not necessary with you. It's for others who may be reading this good thread), it's also incredibly easy to modify to meet the new requirements I spoke of ...
WITH
ctePreAgg AS
(
SELECT JobID,
ActiveSteps = COUNT(Done) - SUM(CAST(Done AS SMALLINT)),
ResultSum = SUM(Result) --Only "0" is a "Pass"
FROM #Steps
GROUP BY jobID
)
SELECT JobID,
JobStatus = CASE
WHEN ActiveSteps = 0 AND ResultSum = 0 THEN 'Done'
WHEN ResultSum IS NULL THEN 'Waiting'
WHEN ActiveSteps = 0 AND ResultSum <> 0 THEN 'Failed[font="Arial Black"], Done[/font]'
[font="Arial Black"]WHEN ActiveSteps > 0 AND ResultSum <> 0 THEN 'Failed, still Busy'[/font]
WHEN ActiveSteps > 0 AND ResultSum = 0 THEN 'Busy'
ELSE 'Unknown'
END[font="Arial Black"],
ActiveSteps[/font]
FROM ctePreAgg
;
Thank you very much for the intelligent and courteous dialog, R.P. I've sincerely enjoyed this thread and our conversation. 🙂
p.s. As a side bar, no, I didn't include the comments that I'd normally include in such code... I wanted to show how readable the code was even without comments.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 9:36 pm
@Yin Halen,
My apologies... I didn't mean to hi-jack your thread. Are you all set now?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2011 at 10:06 pm
Highly offended. :angry:
Just kidding. I was fine back on the second or third post. You guys kind of blew it up (like that rbar picture).
It's been an enjoyable read though.
August 17, 2011 at 12:49 am
@Yin Halen, thanks for having us in your thread. My day wouldn't have been as much fun without it.
@jeff, your audience must be on a more advanced level than mine here. I usually have to explain even the concept of a common table expression: "It's almost like a temporary view". But at least on one point we are in full compliance: in-code comments should (and will) explain all that is beyond the most obvious. So I always add them wherever needed, AND keep them up-to-date when changing the code. Thank you for this conversation (and of course your performance comparison of = ALL).
August 17, 2011 at 3:17 am
@Jeff, your audience must be on a more advanced level than mine here. I usually have to explain even the concept of a common table expression: "It's almost like a temporary view". But at least on one point we are in full compliance: in-code comments should (and will) explain all that is beyond the most obvious. So I always add them wherever needed, AND keep them up-to-date when changing the code. Thank you for this conversation (and of course your performance comparison of = ALL).
Since we are on the topic, why aren't CTE's taught more? Once I started using them I found them useful.
As for commented code, do you guys enforce it in your workplace? I was able to use policy management for object naming but didn't find a way to enforce commented code.
It's great to say in theory - "code should always be commented" but how do you practice it?
August 17, 2011 at 4:32 am
SQLSandwiches (8/17/2011)
...As for commented code, do you guys enforce it in your workplace? I was able to use policy management for object naming but didn't find a way to enforce commented code.
It's great to say in theory - "code should always be commented" but how do you practice it?
You can introduce "code review" phase in your development cycle. It can help and not only with enforcing code standard policies...
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply