Here is a T-SQL scripting exercise in case you have a few minutes to spare or are bored with whatever else you are doing.
The task is to write a simple T-SQL script to generate and load 4,000,000 rows into a test table. The objective is to make the following simple test query to have the worst performance in terms of elapsed time:
SELECT COUNT(*) FROM dbo.test;
The longer the elapsed time, the worst it is.
Of course, there must be a number of constraints, and they are as follows:
· The test table can have any number of columns of any fixed-length data types. However, the first column should be an integer column with values from 1 through 4,000,000, inclusive.
· The sum of the all the column widths are between 1000 bytes plus or minus 20 (i.e. between 980 bytes and 1020 bytes).
· No NULL is allowed for any column.
· No undocumented features are allowed in the script.
· When the test query is run, the test table must meet the following condition:
§ The test table has 4,000,000 rows.
§ The total size of data and index must be less than 7.5GB, as measured by sp_spaceused.
§ Avg. page density is greater than 50% as reported by DBCC SHOWCONTIG().
· The test query shown above must be run as is without any change.
Well, it’ll reveal a lot of about how data is stored and how the simple test query is processed.
If you would like to share your solution, and I hope you do, please post it here in the comment. Or if you want it better formatted, I can append it to the main text of this post.