I added a PRIMARY KEY to my previously created SQL table how do I make that column show "first"?

Question

I previously created a SQL server table, recently I went back and added a primary key to the table but the primary key (contact_id) shows like below:
-----------------------------------------------------------------
| last_name | first_name | contact_id |
-----------------------------------------------------------------

BUT I want it to show up like this:

-----------------------------------------------------------------
| contact_id | first_name | last_name |
-----------------------------------------------------------------

How do you do this using SQL? I'm using SQL Server Management Studio if you are wondering.

Answer 1

In addition to all the earlier answers ... SQL Server Management Studio allows us to re-order the column position easily ( this position in DBMS parlance is called as "Ordinal Position" ).



To do this - Right-Click your table in Object Explorer > Design > drag the column to the desired position> Ctrl + S/ Save



The above procedure would reset the Ordinal position of the column.



One warning is - this is an abstracted phenomena and actually, the SQL Server Engine drops & re-creates the table ( of course, in a controlled manner and within transactions ensuring that table doesn't lose the data ). Hence, if table is huge, avoid doing this.



This whole information is meant to add to the available list of options and I do not endorse it.



The best way would be to generate the script of the original table and then modifying it per your need, renaming it to a temporary name. The data insert from the original table then follows and subsequent step should be dropping the original table and then renaming this temporary table with the original name.





-- In 'thoughts'...

Lonely Rogue.

Answer 2

You can not.



The columns will always display in the order that you created them.



If it is critical for some reason to display the columns in some particular order, there are few ways to do that.



1. Place the columns of interest on the "select" in the order you want them. This is a good idea anyway, because it's rarely a good idea to use "select * "



You can always tell a newbie in this field by their discouraged use of "select * ".



So, your query should be:



SELECT contact_id, first_name, last_name FROM your_table WHERE {whatever}



2. Create a new table, with the columns in the order you want, and whichever is the primary key that you want. Then copy the data from the old table to the new. Then drop the old table and rename the new table.



Be warned of course, if you have gazillions of rows, this can be very time-consuming.



3. Ignore the issue altogether, and recognize that whatever application you're using to access the data can access the columns in whatever order it wants, even to ignore columns it's not interested in.

Answer 3

The order of columns as defined in the data dictionary can't be rearranged. Of course, you can order them any way you like in a resultset. (Unless you always do SELECT * - which is both lazy and inefficient - the order of columns should be irrelevent in 99.999999% of the cases.)



If it REALLY bothers you, create a new table with the columns in the order you prefer, copy over the data and drop the old one, but that seems a lot of trouble for something that really doesnt matter.

Answer 4

With ease use this SQL query ALTER table YOUR_TABLE_NAME ADD predominant KEY (PRIMARY_KEY_COLUMN_NAME); if you want to add a brand new column for your fundamental key, run this query first ALTER table YOUR_TABLE_NAME ADD PRIMARY_KEY_COLUMN_NAME integer; make sure to replace the YOUR_TABLE_NAME and PRIMARY_KEY_COLUMN_NAME with the appropiate names

Answer 5

This will help

http://sql-ex.ru/help/select15.php