Tuesday, November 17, 2015

SQL Server Queries are spilling out to tempdb

-- How to rid yourself of tempDB spilage if SORT causing issues Create a Temp Table with a CLUSTERED INDEX

IF OBJECT_ID('tempdb..#tempSizes') IS NOT NULL DROP TABLE #tempTable

SELECT DISTINCT
IDENTITY(INT,1,1) AS ID,
Products.Name,
Product_Families.Parent_Family_Name,

INTO #tempTable
FROM Products
LEFT JOIN Product_Families ON Products.Product_Family_ID = Product_Families.Product_Family_ID
WHERE Products.Discontinued = 0
AND Quantity > 0

CREATE CLUSTERED INDEX IX_ID ON #tempSizes(ID)

SELECT * FROM #tempTable
Post a Comment