Creating Complex Test Databases - Request for Ideas
Over the past week, I've written a few entries regarding complex databases with interesting characteristics for testing purposes.
- Creating Complex Test Databases - Intro
- Creating Complex Test Databases - Creating a Database with 1 Billion Random Rows
- Creating Complex Test Databases - Generating One Table for each of the 2,397 supported Collations
- Creating Complex Test Databases - Creating a Table with all Supported SQL Server Data Types
- Creating Complex Test Databases - Creating a Table with more than 1,024 Columns
- Creating Complex Test Databases - Using Excel to convert a raw list of text into viable T-SQL syntax
I selected these first topics loosely on the fact that I was going through the exercise of creating these specific databases for testing purposes as part of my "real" job. Now I would like to turn the question to everyone else to drum up some more interesting ideas.
Are there any complex/interesting databases that you'd like to see highlighted in a blog entry?
A few thoughts come to mind surrounding:
- Complex relationships of PK/FKs, including composite keys
- Interesting user/login/schema relationships
- Using column sets to exceed the 1,024 column limit in tables
- Sql_variant data of all supported types (Ex: Sql_Variant data type can store data in the form of 23 other system data types. If your application contains any sql_variant columns, testing that the app works with data of all supported types is a good practice.)
- Exploring UDTs and UDDTs
- Spatial types and data
- Circular object dependencies
- Stored procedures / Functions that refer to objects that no longer exist
I will eventually compile all of the scripts and documentation from this blog series to publish in the MSDN Code Gallery as a download. Leave me a comment with any ideas that you would like to see included in the sample code.
Hope you enjoy,
Sam Lester (MSFT)