SQL SERVER – MAX Column ID Used in Table

How to find out how many columns a table had in the history of the table? An interesting question isn’t it. Well, we discussed this during SQL YouTube Live – Performance Troubles – Execution Plans. Let us see MAX Column ID Used in Table.

Let me ask you a simple question – if you have a table with 3 columns, is there any way you can tell what was the maximum number of the column once you had for that table? Honestly, you can tell it easily.

Here is the script which tells the name of the table with MAX Column ID Used in Table.

SELECT name, max_column_id_used
FROM sys.tables
WHERE name = 'Test';

Let us take the script for the test ride.

First, create a table with five columns.

CREATE TABLE Test
(ID1 INT, ID2 INT, ID3 INT, ID4 INT, ID5 INT);

Now drop two of the columns. Now the remaining columns will be only 3.

ALTER TABLE Test
DROP COLUMN ID1;
ALTER TABLE Test
DROP COLUMN ID5;

Run the script again to check the max_column_id_used and you will find the number 5.

DROP TABLE Test;

Let me know what you think of this little trick. Many such tricks, I often discuss during Comprehensive Database Performance Health Check.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

Reference: Pinal Dave (http://blog.SQLAuthority.com)