SQL Server *= Operator?

Question

Today while inside a client's production system, I found a SQL Server query that contained an unfamiliar syntax. In the below example, what does the *= operator do? I could not find any mention of it on MSDN. The query does execute and return data. As far as anyone knows, this has been in the system since they were using SQL Server 2000, but they are now running 2005.

declare @nProduct int declare @iPricingType int declare @nMCC int  set @nProduct = 4 set @iPricingType = 2 set @nMCC = 230  --Build SQL for factor matrix  Select distinct base.uiBase_Price_ID, base.nNoteRate,  base.sDeliveryOpt,  IsNull(base.nPrice,0) as nPrice,  IsNull(base.nPrice,0) + Isnull(fact.nFactor,0) as nAdjPrice,  base.iProduct_ID, fact.iPosition as fiPosition,  base.iPosition,  CONVERT(varchar(20), base.dtDate_Updated, 101) + ' ' + CONVERT(varchar(20), base.dtDate_Updated, 108) as 'dtDate_Updated',  fact.nFactor,  fact.nTreasFactor,  product.sProduct_txt ,   pfi.sPFI_Name,   mccprod.nServicing_Fee,   fact.nNoteRate as fNoteRate,   mcc.nLRA_Charge as nLRA   From  tbl_Base_Prices base, tbl_Factors fact, tbl_Product product, tbl_PFI pfi, tbl_MCC mcc, tbl_MCC_Product mccprod  Where base.iProduct_ID = @nProduct   And base.iProduct_ID *= fact.iProduct_ID  And base.iPosition *= fact.iPosition  And base.nNoteRate *= fact.nNoteRate  And base.iPricing_Type = @iPricingType And fact.iMCC_ID =  @nMCC And fact.iProduct_ID = @nProduct And mcc.iMCC_ID =  @nMCC  And mcc.iPFI_ID = pfi.iPFI_ID  And mccprod.iMCC_ID =  @nMCC And mccprod.iProduct_ID =  @nProduct And base.iProduct_ID = product.iProduct_ID  and fact.iPricing_Type= @iPricingType Order By base.nNoteRate, base.iPosition  

Answer 1

Remove this code immediately and replace with a left join. This code does not always interpret correctly (Sometimes SQL Server decides it is a cross join) even in SQL Server 2000 and thus can give incorrect results! Also it is deprecated for the future (Using Outer Joins, SQL Server 2000 documentation archived from the original).

I'm going to add that in adjusting to left joins you should remove all of those other implicit joins as well. The implicit join syntax has been obsolete since 1992, there is no excuse for it still being in production code. And mixing implicit and explicit joins can give unexpected results.

---