Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT *
- INTO date_CI_TransactionHistory
- FROM Production.TransactionHistory;
- ALTER TABLE date_CI_TransactionHistory ADD CONSTRAINT
- PK_date_CI_TransactionHistory PRIMARY KEY /*NONCLUSTERED*/ (TransactionDate, TransactionID);
- --CREATE CLUSTERED INDEX IX_date_CI_TransactionHistory_TransactionDate
- -- ON date_CI_TransactionHistory(TransactionDate, ProductID);
- CREATE NONCLUSTERED INDEX IX_date_CI_TransactionHistory_ProductID
- ON date_CI_TransactionHistory(ProductID);
- CREATE NONCLUSTERED INDEX IX_date_CI_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
- ON date_CI_TransactionHistory(ReferenceOrderID, ReferenceOrderLineID);
- GO
- --DROP TABLE date_CI_TransactionHistory
- SELECT *
- INTO int_CI_TransactionHistory
- FROM Production.TransactionHistory;
- ALTER TABLE int_CI_TransactionHistory ADD CONSTRAINT
- PK_int_CI_TransactionHistory PRIMARY KEY (TransactionID, TransactionDate);
- CREATE NONCLUSTERED INDEX IX_int_CI_TransactionHistory_TransactionDate
- ON int_CI_TransactionHistory(TransactionDate);
- CREATE NONCLUSTERED INDEX IX_int_CI_TransactionHistory_ProductID
- ON int_CI_TransactionHistory(ProductID);
- CREATE NONCLUSTERED INDEX IX_int_CI_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
- ON int_CI_TransactionHistory(ReferenceOrderID, ReferenceOrderLineID);
- GO
- --DROP TABLE int_CI_TransactionHistory
- EXEC sp_spaceused date_CI_TransactionHistory
- EXEC sp_spaceused int_CI_TransactionHistory
- SET STATISTICS IO ON
- SET STATISTICS TIME ON
- SELECT *
- FROM date_CI_TransactionHistory
- WHERE TransactionDate BETWEEN '2014-05-01' AND '2014-05-31'
- /*
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 93, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 0 ms, elapsed time = 144 ms.
- */
- SELECT *
- FROM int_CI_TransactionHistory
- WHERE TransactionDate BETWEEN '2014-05-01' AND '2014-05-31'
- /*
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 16 ms, elapsed time = 160 ms.
- */
- SELECT *
- FROM date_CI_TransactionHistory
- WHERE TransactionDate = '2014-05-05 00:00:00.000'
- /*
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 0 ms, elapsed time = 75 ms.
- */
- SELECT *
- FROM int_CI_TransactionHistory
- WHERE TransactionDate = '2014-05-05 00:00:00.000'
- /*
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 0 ms, elapsed time = 75 ms.
- */
- SELECT *
- FROM date_CI_TransactionHistory
- WHERE TransactionID = 166032
- /*
- SQL Server parse and compile time:
- CPU time = 1 ms, elapsed time = 1 ms.
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 314, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 0 ms, elapsed time = 4 ms.
- */
- SELECT *
- FROM int_CI_TransactionHistory
- WHERE TransactionID = 166032
- /*
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 0 ms, elapsed time = 4 ms.
- */
- SELECT *
- FROM date_CI_TransactionHistory CI_Test
- JOIN production.TransactionHistory Orig
- ON CI_Test.TransactionID = Orig.TransactionID
- WHERE Orig.Quantity > 30
- /*
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- Table 'date_CI_TransactionHistory'. Scan count 1, logical reads 793, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- Table 'TransactionHistory'. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 47 ms, elapsed time = 186 ms.
- */
- SELECT *
- FROM int_CI_TransactionHistory CI_Test
- JOIN production.TransactionHistory Orig
- ON CI_Test.TransactionID = Orig.TransactionID
- WHERE Orig.Quantity > 30
- /*
- SQL Server parse and compile time:
- CPU time = 0 ms, elapsed time = 0 ms.
- Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- Table 'int_CI_TransactionHistory'. Scan count 1, logical reads 788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- Table 'TransactionHistory'. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
- SQL Server Execution Times:
- CPU time = 47 ms, elapsed time = 186 ms.
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement