Column order

Other SQL Server Topics
1

Hello. I work with an SAP system and SQL Server 2008. I inserted a new field to be part of the key, as the third field on a table. When I select data from the table using the SQL client, my new column shows as the last column on the right. Any thoughts? Thanks.

2

This is the expected behavior in SQL Server. When you add a new column it gets the last ordinal position. If you want to get the data in a specific order, you can always rearrange the columns in any order you want in your SELECT statement. For example:

SELECT
	Col1,
	Col2,
	MyNewColumn,
	Col3,
	Col4
FROM
	YourTable;

If you do want to control the ordinal position, the only way is to drop and recreate the table. However, that would mean that you have to take care of permissions, foreign keys, constraints etc., in addition to making sure that you preserve the data that may already be in the table.

Also, the recommendation is that when selecting from a table, always explicitly list the columns rather than using SELECT *. This way, the ordinal position would not matter.

1 Like
3

thanks so much for your prompt response. what is really weird, is, that, because my test system got refreshed and i lost my change, i did this twice. the first time the column showed up in third place, and, of course, i broke some processes. the second time the column showed up as the last field. so i am trying to figure that out. thanks