4

I created a table, and want to find the display the order of its columns. Should I use the following query to display the info ordered by column_id?

select * from sys.columns c
where c.object_id = object_id('Customer')
order by column_id


create table dbo.Customer
(
    CustomerId int primary key,
    CustomerName varchar(255),
    CustomerAddress varchar(255),
    EnrollmentDate date
)

Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:

Column name  Data type  Description
-----------  ---------  ---------------------------------------------- 
column_id:   int        ID of the column. Is unique within the object.
                        Column IDs might not be sequential.
8

column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.

As the documentation states, column_id values may not be sequential if you drop a column from a table.

You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.

Consider a quick example:

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
    c1 int
    , c2 int
    , c3 int
    , c4 int
);

ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);

SELECT o.name
    , c.name
    , c.column_id
    , ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
    INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'

The output looks like:

╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t    ║ c2   ║         2 ║       1 ║
║ t    ║ c3   ║         3 ║       2 ║
║ t    ║ c4   ║         4 ║       3 ║
║ t    ║ c5   ║         5 ║       4 ║
╚══════╩══════╩═══════════╩═════════╝
  • thanks, can we assume column order will always go ascending order, so I if I delete columns, we get them sequenced in order, that's all I really care about, example : 1,3,4,7,8, (if we delete some columns?) – user175410 Mar 26 at 20:58
  • another interesting note, Ordinal is not in Microsoft documentation, wondering if they are trying to deprecate docs.microsoft.com/en-us/sql/t-sql/functions/… , mssqltips.com/sqlservertip/1298/… – user175410 Mar 26 at 21:03
  • If they were trying to deprecate it, they'd add it to the deprecated features list. Most likely, it is undocumented by mere oversight. Either that, or they don't want to promote the idea of ordinals in a relational database where order only matters if you specify one explicitly. – Max Vernon Mar 27 at 13:30
2

Just to propose an additional answer that will tell you the actual column position instead of column_id

select column_name, ORDINAL_POSITION 
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'your_table'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy