Trjtdtk

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.

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 ║
╚══════╩══════╩═══════════╩═════════╝

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'