Big Tempdb Table

This won’t be the most exciting post.  But hey, it’s Monday!  What’s exciting about that?  Maybe someone will bring in donuts.  Or maybe I should since I am still on my couch and not headed to the office yet.

Anyways, every now and then I need a big table to test some code out on and usually I want it to at least appear to be somewhat “real world”.  Rather than recreate a script to create a table I use the script below.

USE tempdb


IF OBJECT_ID('tempdb..PurchaseOrders') IS NOT NULL
DROP TABLE PurchaseOrders

SELECT TOP (2500000)
ROW_NUMBER() OVER (ORDER BY poh.PurchaseOrderID, pod.PurchaseOrderDetailID) as RowOrderID
INTO PurchaseOrders
FROM AdventureWorks.Purchasing.PurchaseOrderDetail pod
CROSS JOIN AdventureWorks.Purchasing.PurchaseOrderHeader poh

CREATE CLUSTERED INDEX CIX_PurchaseOrders ON PurchaseOrders (RowOrderID)

CREATE INDEX IX_PurchaseOrders_1 ON PurchaseOrders (EmployeeID)

CREATE INDEX IX_PurchaseOrders_2 ON PurchaseOrders (VendorID)

CREATE INDEX IX_PurchaseOrders_3 ON PurchaseOrders (PurchaseOrderID, Status, RevisionNumber)

By modifying the TOP value it can return up to about 32 million rows.  And the width of the rows and indexes help make certain various performance tests try to act like I would expect on client tables.

Hope someone finds this useful.