In general this homework series is meant to give you ideas of things to play with that most if not all data professionals are going to end up touching eventually. Personally, I’ve found it nice to have a little bit of practice ahead of time whenever possible. This month I want you to take a look at table sizes. This will work best if you have a few tables with at least a reasonable amount of data in them. If you don’t already have some from previous testing you can either create a few or use a sample database.
How big is my table?
Before anything else, it helps to know how big your tables are. Off the top of my head, I can think of several different ways to get the size of one or more tables. See what you can come up with. Get at least one method under each of these categories. Think about what uses you might have for each. For example “What is the biggest table in this database?”
- System views
- System stored procedure
- Built in report
Changing table size
Table size may not seem that important at first glance, but it plays into both minimizing storage requirements and query performance which are both highly important. That said, I want you to try playing with the size of at least one table. Obviously in some cases archiving off some data is a great option to reduce the table size. Here, however, we are going to talk about changing table sizes without getting rid of any data. By preference, it should have at least 10k rows so you can really see a difference. Make sure you check the table size after each test.
- Data Types: Try changing the data types a few times. This should give you an idea of just how important datatyping really is.
- Change an int column to bigint.
- Change a varchar column to char.
- Change a char or varchar column to nchar or nvarchar.
- Change a datetime column to date or smalldatetime.
- Play with FILLFACTOR: Check your table size at 20, 50, 80 and 100. (Note that 0 and 100 are the same so there’s no need to check both.) Do this on a clustered index.
- Compression: Compress your table using first row and then page compression.
For extra credit try playing with combinations of the above. How does fillfactor and various data types affect the two types of compression? How small can you get the table? (No, truncating or deleting every row doesn’t count.)