October 19, 2007 at 8:34 pm
Jack Corbett (10/19/2007)
I really enjoyed the thread. A lot of good information and no one got personal as I have seen happen when people have different ideas in a forum.
You didn't wait long enough (see Sergiy below). 😉
October 19, 2007 at 8:43 pm
Sergiy (10/19/2007)
David, for some reason I did not have any doubts about you and some other folks.😉
SQL 2k5 made for programming tools users having no math background, just like you.
As I said - if you have trouble with learning relational model and proper SQL - then SQL2k5 is right for you. It makes data access more procedural, as you like and understand.
But it does not make SQL2k5 useful for those who can build proper model and do proper querying.
Out of curiosity: what have you found in OVER() what did not exist in derived table?
I'm going to ignore all of your personal attacks, as it's now pretty common knowledge from past threads that that's just a sign of you losing the battle. I'm surprised you haven't started Godwinizing threads when you're down.
Of course you can replace most of the new functionality with old methods. I noted as much above. It's just much easier to do many things the new way. While I realize that actual evidence leaves you nonplussed, for the lurkers out there, here's an example of why I like 2005. Sergiy, feel free to show us the wonders of derived tables with much cleaner 2000 code than the 2005 version that I've provided below. Heck, I'll even happily compare times between the two methods, in case you want to argue that while you take longer to code than I do, your code runs faster.
SELECT
CustomerID
,SalesOrderID
,TotalDue
,PercentageOfTotal = TotalDue / Sum(TotalDue) Over(Partition By CustomerID) * 100.0
,DifferenceFromAverage = TotalDue - Avg(TotalDue) Over(Partition By CustomerID)
FROM
Sales.SalesOrderHeader
ORDER BY
CustomerID
It's certainly doable in 2000, but I'll write the code five times using Over() before you've finished your first attempt. Even better, if the end user comes back and decides they want the aggregates broken down further by year or just about any other desire they might have, that's an additional 10 seconds of coding. Useless indeed.
October 19, 2007 at 8:47 pm
Yeah - I call them - "not being in charge of your destiny":
- projects you inherited that aren't good;
- third-party apps that s**k data-wise,that you can't fix, can't get rid of, and can't get the users to use the correctly;
- underfunded stuff that gets overrun because users are no longer happy with the limited set that they have and start coming up with their own interpretation of "how to use the system".
- Some of it's bad design, some of it resource shortages, some of it - human nature.
- Some of it are projects that didn't grow well;
- some of it's just plain data that never should be stored in a DB that someone with a shiny title wants data out of....
I wish I had such a firm grasp as you seem to on your user base as to know and control where they're heading, but quite honestly - they keep surprising me every day.
"Every time I try to get out....They pull me back in!"
-----------------------------------------
As to cross apply - I have used it for occasionally pulling data out of HL7 transactions: since the format is entirely forced upon us, and (by hospital policy) also needed to be kept as is - those fields not needed for "our" purposes might not make it into separate fields - so cross apply sometimes is used. Not great, but it was the best we had at the time we needed it.
Right answer? wrong thinking? be the judge.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 20, 2007 at 12:11 am
Lowell (10/19/2007)
so...Your saying CLR's are for people who know sql, or people who don't know sql...I'm confused. 😉
By the way... this note cracked me up this morning... I almost blew morning coffee right out of my nose 😀 Thanks for making my day, Lowell... always good to hear from you... 🙂
--Jeff Moden
October 20, 2007 at 12:19 am
vadimt (10/18/2007)
the first solution didnt work. because of the Tally i didnt implement.i dont need it as a function because i need it only in one place in the whole code.
So... Vadimt... still think those are wise decisions? :blink:
--Jeff Moden
October 20, 2007 at 12:29 am
You didn't wait long enough
Couple of us didn't... I missed that... sorry. :ermm:
--Jeff Moden
October 20, 2007 at 5:38 am
Jeff Moden (10/20/2007)
vadimt (10/18/2007)
the first solution didnt work. because of the Tally i didnt implement.i dont need it as a function because i need it only in one place in the whole code.
So... Vadimt... still think those are wise decisions? :blink:
in general case i guess not... 😛
but for what i need its good enough.
its used once in one stored procedure, and there are no runtime limitations.
October 20, 2007 at 8:15 am
Heh... I'm thinking you missed a great "tool box" opportunity with the DBA... not a problem... thanks for the feedback.
--Jeff Moden
October 20, 2007 at 9:22 am
David McFarland (10/19/2007)
Jack Corbett (10/19/2007)
I really enjoyed the thread. A lot of good information and no one got personal as I have seen happen when people have different ideas in a forum.You didn't wait long enough (see Sergiy below). 😉
Well, I figured that after 50+ posts we would be safe. I am still thinking about a thread a few weeks ago where somebody had to be banned because all they did was call the writer and almost everybody else idiots. At least we haven't gotten that far...yet:D
Seriously though, if you can't post here without having something constructive to say about the thread, you probably shouldn't bother posting. We should all be professionals and be able to offer and accept constructive criticism and realize that there is always someone smarter OR more experienced than us.
Thanks again to those of you who offered solutions to the original problem and who were able to constructively discuss the different solutions. I had nothing but a passing interest in this thread and I saw 2 new ways to solve the problem that were better than anything I would have come up with. I have added both to my "tool box". I think both the tally table and the CLR solutions have valid arguments for their use and also think this was the best use of the CLR in SQL Server I have seen to this point. From a pure SQL Server DBA perspective I prefer any solution using T-SQL, but I want to know how and when to use the CLR.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2007 at 6:24 pm
[Code]
SELECT
H.CustomerID
,H.SalesOrderID
,H.TotalDue
,H.PercentageOfTotal = H.TotalDue / T.Sum_TotalDue * 100.0
,H.DifferenceFromAverage = H.TotalDue - T.Avg_TotalDue
FROM Sales.SalesOrderHeader H
INNER JOIN (select CustomerID, Sum(TotalDue) Sum_TotalDue, Avg(TotalDue) Avg_TotalDue
from Sales.SalesOrderHeader) T ON T.CustomerID = H.CustomerID
ORDER BY
H.CustomerID
[/Code]
So what?
_____________
Code for TallyGenerator
October 20, 2007 at 7:26 pm
David,
You know I'm dying to ask... and please don't take it personal...
Do you have a math background or not?
--Jeff Moden
October 20, 2007 at 8:14 pm
So what?
Heh... HOT DAMN! One performance test coming right up... somebody run the following in 2k5 'cause I still don't have it yet... I removed the ORDER BY from both pieces of code because we're not interested in the speed or ORDER BY (yet)...
--===== Create a variable to measure duration with
DECLARE @StartDate DATETIME
--===== Create a million row test table
IF OBJECT_ID('TempDB..#SalesOrderHeader','U') IS NOT NULL
DROP TABLE #SalesOrderHeader
PRINT 'Creating test table...'
SET @StartDate = GETDATE()
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
CustomerID = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),
SalesOrderID = CAST(RAND(CAST(NEWID() AS VARBINARY))*10000000+1 AS INT),
TotalDue = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY)
INTO #SalesOrderHeader
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE #SalesOrderHeader
ADD PRIMARY KEY CLUSTERED (RowNum) WITH FILLFACTOR = 100
--===== Add an index to the CustomerID/TotalDue columns just for grins
CREATE INDEX tmpSalesOrderHeader_CustomerID_TotalDue
ON #SalesOrderHeader (CustomerID,TotalDue)
PRINT CONVERT(VARCHAR(12),GETDATE()-@StartDate,114) + ' Duration (hh:mi:ss:mmm)'
PRINT REPLICATE('=',78)
--===== Run duration test on INNER JOIN method
PRINT 'INNER JOIN method...'
SET @StartDate = GETDATE()
SELECT H.CustomerID
,H.SalesOrderID
,H.TotalDue
,PercentageOfTotal = H.TotalDue / T.Sum_TotalDue * 100.0
,DifferenceFromAverage = H.TotalDue - T.Avg_TotalDue
FROM #SalesOrderHeader H
INNER JOIN (SELECT CustomerID, SUM(TotalDue) Sum_TotalDue, AVG(TotalDue) Avg_TotalDue
FROM #SalesOrderHeader
GROUP BY CustomerID) T
ON T.CustomerID = H.CustomerID
-- ORDER BY H.CustomerID
PRINT CONVERT(VARCHAR(12),GETDATE()-@StartDate,114) + ' Duration (hh:mi:ss:mmm)'
PRINT REPLICATE('=',78)
--===== Run duration test on OVER PARTITION method
PRINT 'OverParition method...'
SELECT CustomerID
,SalesOrderID
,TotalDue
,PercentageOfTotal = TotalDue / Sum(TotalDue) OVER(Partition BY CustomerID) * 100.0
,DifferenceFromAverage = TotalDue - Avg(TotalDue) OVER(Partition BY CustomerID)
FROM #SalesOrderHeader
-- ORDER BY CustomerID
PRINT CONVERT(VARCHAR(12),GETDATE()-@StartDate,114) + ' Duration (hh:mi:ss:mmm)'
PRINT REPLICATE('=',78)
Will OVER(PARTITION) be added to the list of things you can do if you don't really know T-SQL? "Only the shadow knows" 😛
--Jeff Moden
October 20, 2007 at 11:35 pm
Sergiy (10/20/2007)
So what?
I already told you what above. I'll repeat it again for you.
David McFarland
Even better, if the end user comes back and decides they want the aggregates broken down further by year or just about any other desire they might have, that's an additional 10 seconds of coding.
By the way, your code doesn't have the necessary GROUP BY (other syntax errors, but they're perfectly understandable when not testing something), although Jeff fixed that. My code was so straightforward that I didn't make any syntax error-causing typos at all, and that's pretty rare for me. Another reason I like Over().
October 20, 2007 at 11:41 pm
Jeff Moden (10/20/2007)
David,You know I'm dying to ask... and please don't take it personal...
Do you have a math background or not?
Won't take it personal at all. 🙂
It depends on what you mean by a math background. Am I a mathematics professional or academic? Not at all. Do I have formal math training? Absolutely, enought to be aware that 1.0 = .9~ . 🙂 Do I still broaden my math horizons at this late date in my career? Frequently. I'm moderately active in various forums, although much like here, my participation levels goes in waves, as I sometimes work a bit too much.
Now I'm dying to ask, and please don't take it personal. What does this have to do with this thread (or even that other thread, if that's what this is about)?
October 20, 2007 at 11:49 pm
Jeff Moden (10/20/2007)
Heh... HOT DAMN! One performance test coming right up... somebody run the following in 2k5 'cause I still don't have it yet... I removed the ORDER BY from both pieces of code because we're not interested in the speed or ORDER BY (yet)...[/QUOTE]I'll happily run it for you once marting is done, but I'd expect them to be in the same ballpark for that simple of a query.
Will OVER(PARTITION) be added to the list of things you can do if you don't really know T-SQL? "Only the shadow knows" 😛
Still don't know what this has to do with anything. Most people who use Over() are perfectly capable of using derived tables, they just don't have to in many situations now. Same as CTEs. Sure, you can do the same thing with temp tables, derived tables, table variables, and the like, and in some situations, those are actually better choices. Sometimes, CTEs are ideal. I can use a single hammer for all of my nailing needs as well, but sometimes, a tack hammer is a bit nicer, while other times, a nail gun might be ideal. Doesn't mean I can't use a hammer.
Viewing 15 posts - 61 through 75 (of 172 total)
You must be logged in to reply to this topic. Login to reply