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
GO
SET STATISTICS IO ON
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
,poh.PurchaseOrderID
,poh.RevisionNumber
,poh.Status
,poh.EmployeeID
,poh.VendorID
,poh.ShipMethodID
,poh.OrderDate
,poh.ShipDate
,poh.SubTotal
,poh.TaxAmt
,poh.Freight
,poh.TotalDue
,pod.PurchaseOrderDetailID
,pod.DueDate
,pod.OrderQty
,pod.ProductID
,pod.UnitPrice
,pod.LineTotal
,pod.ReceivedQty
,pod.RejectedQty
,pod.StockedQty
,pod.ModifiedDate
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.
Strate SQL 