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.

SQL SERVER - MAX Column ID Used in Table ColumnID-800x332

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.

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

, ,
Previous Post
SQL SERVER – Correcting Space Allocation with DBCC UPDATEUSAGE
Next Post
[Video] SQL Performance Troubles – Execution Plans – Live 2

Related Posts

1 Comment. Leave new

  • Giancarlo Gomez
    July 31, 2021 7:47 pm

    Always love playing with these little hints you give us Pinal. I decided to run it on one of my databases and see the total number of columns along with the current amount of columns with the following:

    SELECT [name], [max_column_id_used],
    (
    SELECT COUNT([column_name])
    FROM [information_schema].[columns]
    WHERE [table_name] = [sys].[tables].[name]
    ) AS [current_column_count]
    FROM [sys].[tables]
    ORDER BY [max_column_id_used] DESC

    Reply

Leave a Reply

Menu