October 26, 2017 at 8:27 am
I've created thousands upon thousands of stored procs, and I can probably count on one hand the number of times I've had to resort to using a CROSS JOIN in any of them. Just seems like a really, really ugly way to accomplish anything.
So, help me out here if possible! I just can't think of a better way of accomplishing the following. I know it's probably not a huge concern, since although I'm doing a cross join, I'm also explicitly filtering both tables down to one record apiece, but it still seems super ugly.
CREATE TABLE #TableToInsertValuesInto
(
ID INT IDENTITY PRIMARY KEY,
MasterID INT,
Val VARCHAR(200)
)
October 26, 2017 at 8:43 am
Why do you even need the cross join? Or are you asking how to do that without using a cartesian product, if so what is the problem with that?
SELECT
#TableThatHasTemplate.MasterID,
(
CASE TemplateName
WHEN 'SomeValue' THEN Field1
WHEN 'AnotherValue' THEN Field2
WHEN 'MoreValues' THEN Field3
WHEN 'YetMoreValues' THEN Field4
END
)
FROM #TableThatHasTemplate
, #TableThatHasValues
WHERE #TableThatHasValues.ID = @ValueID
AND #TableThatHasTemplate.MasterID = @MasterID
October 26, 2017 at 8:46 am
What you're doing is a CROSS JOIN, just without using the words CROSS JOIN.
By specifying two tables with a comma, SQL Server is implicitly applying a CROSS JOIN to form the query.
**edit
As for why I'm trying to avoid using a Cartesian product, well ... it just seems dirty to me, and I was sorta hoping there was a cleaner way of accomplishing my objective.
October 26, 2017 at 8:47 am
You could drop the CROSS and change the WHERE to ON. Same query, just avoiding the dreaded cross join syntax.
John
October 26, 2017 at 8:48 am
kramaswamy - Thursday, October 26, 2017 8:46 AMWhat you're doing is a CROSS JOIN, just without using the words CROSS JOIN.By specifying two tables with a comma, SQL Server is implicitly applying a CROSS JOIN to form the query.
Right and you are trying to get a cartesian product, why do you think that is a bad thing? You have two tables that aren't directly linked and you want all the results from one table matched with all the results from another table.
October 26, 2017 at 9:44 am
The real issue is that one of your tables isn't normalized. If you normalize your table, you don't need to use a CROSS JOIN.
SELECT t.ID, t.MasterID, v.Val
FROM #TableThatHasValues tv
CROSS APPLY -- unpivot
(
VALUES
('SomeValue', Field1)
, ('AnotherValue', Field2)
, ('MoreValues', Field3)
, ('YetMoreValues', Field4)
) v(TemplateName, Val)
INNER JOIN #TableThatHasTemplate t
ON v.TemplateName = t.TemplateName
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 26, 2017 at 9:58 am
Drew, can you explain that query a bit more? I've never seen that type of operation before. What exactly is the CROSS APPLY doing in this case? And how does that parameterized output for the CROSS APPLY work (the v(TemplateName, Val) part)?
October 26, 2017 at 11:35 am
The part in parentheses is just a Table Value Constructor and the v(TemplateName, Val) is just providing aliases for the fields returned by the Table Value Constructor. The CROSS APPLY allows the Table Value Constructor to access fields from the left table source.
This construct is just doing an UNPIVOT. I prefer this format, because it's much simpler syntax than using the UNPIVOT construct. I believe it's also faster, but I'm not positive on that.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 1, 2017 at 3:27 am
drew.allen - Thursday, October 26, 2017 11:35 AMThis construct is just doing an UNPIVOT. I prefer this format, because it's much simpler syntax than using the UNPIVOT construct. I believe it's also faster, but I'm not positive on that.Drew
From my experience it depends - on SQL 2008 R2 on a table with 9 million rows, unpivoting 20 fields was slightly faster - 4-5% faster.
Again as the method above is simpler I used the outer apply syntax.
But there might be cases where timing differences are significant and require any drop of improvement that may warrant the unpivot option.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply