Yhyjyk

Multi tool use

Why "be dealt cards" rather than "be dealing cards"?

quarter to five p.m

Trouble understanding overseas colleagues

What is the opposite of 'gravitas'?

The Riley Riddle Mine

There is only s̶i̶x̶t̶y one place he can be

Hide Select Output from T-SQL

How does residential electricity work?

How can I replace every global instance of "x[2]" with "x_2"

Should my PhD thesis be submitted under my legal name?

Teaching indefinite integrals that require special-casing

when is out of tune ok?

Greatest common substring

How do I rename a LINUX host without needing to reboot for the rename to take effect?

How will losing mobility of one hand affect my career as a programmer?

Why does John Bercow say “unlock” after reading out the results of a vote?

The baby cries all morning

Increase performance creating Mandelbrot set in python

Efficiently merge handle parallel feature branches in SFDX

Was the picture area of a CRT a parallelogram (instead of a true rectangle)?

What's the purpose of "true" in bash "if sudo true; then"

How to be diplomatic in refusing to write code that breaches the privacy of our users

I'm in charge of equipment buying but no one's ever happy with what I choose. How to fix this?

Why are on-board computers allowed to change controls without notifying the pilots?

Displaying the order of the columns of a table

Why should I create an ID column when I can use others as key fields?How do you create a relationship to a non-primary key in SQL Server?Oracle GoldenGate add trandata errorsError while executing SSIS package which contains Script component through SQL Server Agent JobShould I mark a composite index as unique if it contains the primary key?Can I rely on reading SQL Server Identity values in order?Order by custom filter without certain dataSQL Server query problem when selecting data from child table based on column in parent tableQuery runs slowly when a non-indexed column is added to the WHERE clauseProper table design for sparse primary key

3

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.

sql-server sql-server-2016

share|improve this question

edited 49 mins ago

MDCCL

6,85331745

asked 1 hour ago

John ThomasJohn Thomas

211

New contributor

John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

3

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.

sql-server sql-server-2016

share|improve this question

edited 49 mins ago

MDCCL

6,85331745

asked 1 hour ago

John ThomasJohn Thomas

211

New contributor

John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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.

sql-server sql-server-2016

share|improve this question

edited 49 mins ago

MDCCL

6,85331745

asked 1 hour ago

John ThomasJohn Thomas

211

New contributor

John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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
)

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

share|improve this question

edited 49 mins ago

MDCCL

6,85331745

asked 1 hour ago

John ThomasJohn Thomas

211

New contributor

John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 49 mins ago

MDCCL

6,85331745

asked 1 hour ago

John ThomasJohn Thomas

211

New contributor

John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 49 mins ago

MDCCL

6,85331745

edited 49 mins ago

MDCCL

6,85331745

asked 1 hour ago

John ThomasJohn Thomas

211

New contributor

John Thomas is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 hour ago

John ThomasJohn Thomas

211

2 Answers
2

active

oldest

votes

3

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

share

edited 1 hour ago

answered 1 hour ago

Max VernonMax Vernon

51.9k13114230

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?)
  
  – John Thomas
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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/…
  
  – John Thomas
  1 hour ago
  

  
  
  
  

add a comment | 

1

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'

share|improve this answer

answered 1 hour ago

PadwanPadwan

1916

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

John Thomas is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233179%2fdisplaying-the-order-of-the-columns-of-a-table%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

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

share

edited 1 hour ago

answered 1 hour ago

Max VernonMax Vernon

51.9k13114230

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?)
  
  – John Thomas
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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/…
  
  – John Thomas
  1 hour ago
  

  
  
  
  

add a comment | 

3

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

share

edited 1 hour ago

answered 1 hour ago

Max VernonMax Vernon

51.9k13114230

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?)
  
  – John Thomas
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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/…
  
  – John Thomas
  1 hour ago
  

  
  
  
  

add a comment | 

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

share

edited 1 hour ago

answered 1 hour ago

Max VernonMax Vernon

51.9k13114230

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'

╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t ║ c2 ║ 2 ║ 1 ║
║ t ║ c3 ║ 3 ║ 2 ║
║ t ║ c4 ║ 4 ║ 3 ║
║ t ║ c5 ║ 5 ║ 4 ║
╚══════╩══════╩═══════════╩═════════╝

share

edited 1 hour ago

answered 1 hour ago

Max VernonMax Vernon

51.9k13114230

answered 1 hour ago

Max VernonMax Vernon

51.9k13114230

answered 1 hour ago

Max VernonMax Vernon

51.9k13114230

1

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'

share|improve this answer

answered 1 hour ago

PadwanPadwan

1916

add a comment | 

1

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'

share|improve this answer

answered 1 hour ago

PadwanPadwan

1916

add a comment | 

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'

share|improve this answer

answered 1 hour ago

PadwanPadwan

1916

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

share|improve this answer

answered 1 hour ago

PadwanPadwan

1916

answered 1 hour ago

PadwanPadwan

1916

answered 1 hour ago

PadwanPadwan

1916