Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 table datatype
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mcp111
Starting Member

44 Posts

Posted - 07/01/2002 :  15:50:11  Show Profile  Reply with Quote
Can you give an example of a query using the table datatype to eliminate left joins?

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/01/2002 :  16:30:31  Show Profile  Reply with Quote
What does a variable's type have to do with joins?

<O>
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 07/01/2002 :  16:33:25  Show Profile  Visit MichaelP's Homepage  Reply with Quote
I don't think that's the purpose of a Table Variable.
Table Variables are used as a replacement for Temp Tables because Table Variables are stored in memory, but Temp Table are stored on disk in the tempdb, thus making Temp Tables slower.

Can you give an example of your query and tables (DDL and DML)? I think that you could possibly use table variable to eliminate LEFT JOINS, but I think that by the time you populate your table variables etc, the LEFT JOIN is going to eb just as quick.

Michael

<Yoda>Use the Search page you must. Find answer you will.
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 07/01/2002 :  16:36:43  Show Profile  Visit MichaelP's Homepage  Reply with Quote
Page, here's what I think this guy is trying to do.
He's got a query with LEFT JOINS, but he wants to change them to INNER JOINS (for performance?). So, I think what he wants to do is populate a table variable with the data in such a way as to eliminte the LEFT JOIN. He'd only put data into the Table Variable that will JOIN to his other table.

Interesting idea actually. I think with some testing he'll find that going around the world to avoid the LEFT JOIN is going to be as slow if not slower than a straight-up LEFT JOIN.

....but I could be wrong.

Michael

<Yoda>Use the Search page you must. Find answer you will.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 07/01/2002 :  19:05:30  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
This doesn't make sense to me. A LEFT JOIN shows all records in the left-hand table and values for matching records in the Right, nulls for non-matched records. If he populates a Table variable to replace the Left table, with only the values that match in the right table, then he will get the same results as if he just did an INNER JOIN on the two original tables anyway. If he populates the Table Variable with all values from the Left table, then he still has to do a LEFT JOIN to get all the values.

If it's the Right table that he's trying to replace with a table variable, then in order to do an INNER JOIN he has to populate the table variable with every key value from the Left table, then fill in the appropriate values from the right table, but to do this will still have to do a LEFT JOIN somewhere to run the UPDATE or INSERT statement, unless he initially populates from the Right-hand table, and then does an INSERT where NOT EXISTS, or something like that.

How can else you get around not doing a LEFT JOIN somewhere in this process?

Go to Top of Page

furryfish
Starting Member

2 Posts

Posted - 01/26/2005 :  21:34:07  Show Profile  Reply with Quote
Perhaps he's going to use an IN in the where clause based on a table variable's data? This removes the need for a join and behaves like an inner.

IE

select * from TABLE
where ID in (select I from @tableVariable)


Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 01/26/2005 :  21:57:52  Show Profile  Visit robvolk's Homepage  Reply with Quote
For one thing, at best, the optimizer would generate the same plan for that as it would for a JOIN. More likely, it would be LESS efficient than using a JOIN. For a large set of data, a temp table could be a better solution as it can be indexed; a table variable cannot. The query plan would show how the optimizer handles it.

And in any case, neither method changes or effects the requirements of the query.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 01/27/2005 :  02:55:40  Show Profile  Reply with Quote
mcp111,

What problem are you trying to solve? Shed some light and we'll see what we can do to be of assistance.

HTH

=================================================================

Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971)
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 01/27/2005 :  06:40:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
I doubt he's still around, this was posted over 2-1/2 years ago. Probably figured something out by now.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/27/2005 :  13:42:46  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

Probably figured something out by now.



Yeah...how to make doughnuts....





Brett

8-)
Go to Top of Page

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 02/17/2010 :  10:30:11  Show Profile  Reply with Quote
quote:
Originally posted by mcp111

Can you give an example of a query using the table datatype to eliminate left joins?






CREATE TABLE #MainTable ( 
        CustomerID INT  PRIMARY KEY, 
        FirstName VARCHAR(100) 
) 


DECLARE @Count INT
SET @Count = 0

DECLARE @Iterator INT
SET @Iterator = 0

WHILE @Count <8000
BEGIN

	INSERT INTO #MainTable SELECT @Count, 'Cust'+CONVERT(VARCHAR(10),@Count)
	SET @Count = @Count+1

END
  
CREATE TABLE  #RightTable 
( 
        OrderID INT  PRIMARY KEY, 
        CustomerID INT, 
        Product VARCHAR(100) 
) 
 
CREATE INDEX  [IDX_CustomerID] ON #RightTable (CustomerID)

WHILE @Iterator <400000
BEGIN

		IF @Iterator % 2 = 0
		BEGIN

			INSERT INTO #RightTable  SELECT @Iterator,2, 'Prod'+CONVERT(VARCHAR(10),@Iterator)
		END
		ELSE
		BEGIN
			INSERT INTO #RightTable  SELECT @Iterator,1, 'Prod'+CONVERT(VARCHAR(10),@Iterator)
		END

SET @Iterator = @Iterator+1

END


-- Approach 1: Using LEFT JOIN
SELECT  mt.CustomerID,mt.FirstName,COUNT(rt.Product) [CountResult]
FROM    #MainTable mt 
		LEFT JOIN #RightTable rt ON mt.CustomerID = rt.CustomerID 
GROUP BY mt.CustomerID,mt.FirstName


---------------------------



-- Approach 2: Using Table variable Update
DECLARE @WorkingTableVariable TABLE
( 
        CustomerID INT, 
        FirstName VARCHAR(100),
		ProductCount INT  
) 
 
INSERT INTO @WorkingTableVariable (CustomerID,FirstName)
SELECT  CustomerID, FirstName FROM #MainTable 
 
UPDATE  @WorkingTableVariable 
SET     ProductCount = [Count]
FROM    @WorkingTableVariable wt 
		INNER JOIN
			(SELECT CustomerID,COUNT(rt.Product) AS [Count]
			 FROM #RightTable rt
			 GROUP BY  CustomerID) IV ON wt.CustomerID = IV.CustomerID 
 
SELECT CustomerID,FirstName, ISNULL(ProductCount,0) [CountResult] FROM    @WorkingTableVariable 
ORDER BY CustomerID

--------

DROP TABLE  #MainTable
DROP TABLE  #RightTable


Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000