• Repost - originally posted 16-08-2012

Finding redundant columns


The task is to refactor a large SQL Server database design with over 1100 tables, 3500 stored procedures, 600 views and 500 functions. One of the areas of focus is reducing the size of the tables, by getting rid of columns that have been created but never used. A lot of columns had been added for the future or "just in case", but this has caused unnecessary bloat. By using the INFORMATION_SCHEMA.columns we have been able to find columns that are all just NULL values. The next step is to then find columns that only have one distinct value in them and then review these to see if they are required. Thinner tables makes the database design easier to handle and will help better performance, especially as some of these redundant columns were indexed as well. This needs co-ordination with the application developers to make sure that these columns are not used in any applications and then once the columns identified, drop them in a non-production environment and do end to end testing.


3 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t