Displaying the order of the columns of a table

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'