Actually, this is a great article. Life would be a way easier if every developer would ask similar questions. Nowadays almost nobody cares about that but to tell the truth the correct answer is: It depends on.
If you have a relatively small software with several million of records, you will be fine with that. Maybe I could recommend you to create a weekly job that does an online rebuild on each table every Sunday to take care of your tables and indexes (of course, please use to Online=On hint or you will have some surprise :D)
If you have a big software with several million of users and plenty of rows, you must ask yourself similar questions.
If you store a row in 400 bytes instead of 100, it does not affect your performance directly. But indirectly, let imagine: you have larger rows, the pages are filled sooner, you will have more page split that takes time and IO. And, of course, your server has to read more pages to get the same amount of rows because each row is larger. So indirectly, it affects your performance.
I think if you keep some rules in your mind, you will avoid the major problems:
- every fixed length column takes a place. If you have a nullable int column, it will take 4 bytes. If you have a char(10) column, it will take 10 bytes and so on.
- variable length columns take a same amount of bytes as their length multiple by the storage size of one unit. A varchar(10) that stores 'apple' takes 5 bytes (plus some additional info). An nvarchar(10) that stores 'apple' takes 10 bytes.
- Very big rows are stored out of the row data (check the Inside SQL Server books).
So my advice is simple:
- plan your columns well
- avoid to delete or alter a column
- maintain your tables