November 1, 2011 at 5:04 am
Do a simple test : 'Create table test ( A int null, B int null, C int null );'
On sql server GUI , modify your table :
Insert a column X between A and B column.
Now execute this query :
'SELECT * from sys.objects obj inner join sys.columns col on obj.object_Id=col.object_Id WHERE obj.Name='test' ORDER BY obj.create_date'
Normally X column must be the last , and this column is in the seconde position. Is it a bugg ?This obj.create_date is not correct , why ?
November 1, 2011 at 5:15 am
You're ordering your results by the create date of the table. Since that will be the same for each column in the table, you can't guarantee that your results will be returned in any particular order.
John
November 1, 2011 at 5:23 am
Thanks John ,
Is it possible to sort by creation date of the column ?
November 1, 2011 at 5:32 am
Not as far as I know - there is no create_date column in sys.columns. I would advise against using the GUI to insert a new column between two existing columns. I think that it creates a new table with the new column and then copies the data over from the old table - potentially an expensive operation for a large table. I would advise using the syntax here http://msdn.microsoft.com/en-us/library/ms190273(v=SQL.90).aspx to add your column - you can then order your results by column_id.
John
November 1, 2011 at 5:33 am
There's no date column in that table. The best you can do afaik is to order by column_id which is like an identity column for each object in that table.
This assume you don't use the GUI to edit your tables. Which, possibly, would drop and rebuild the table making that info useless.
November 1, 2011 at 5:34 am
John Mitchell-245523 (11/1/2011)
I would advise using the GUI to insert a new column between two existing columns.
Did I miss something in the conversation?
Edit YUP.
There's no point in putting the columns in the correct order. You do that in the select statements.
SQL will order those columns on the pages as it pleases so you have no control over that either.
November 1, 2011 at 5:41 am
Ninja's_RGR'us (11/1/2011)
Did I miss something in the conversation?Edit YUP.
There's no point in putting the columns in the correct order. You do that in the select statements.
SQL will order those columns on the pages as it pleases so you have no control over that either.
Yes, sorry about that - the firewall software (or whatever it is here) bans certain phrases, so I had to add my post bit by bit to work out what was being objected to. Turns out it was "?LTER ?ABLE".
Edit - just realised that I missed the word "against" from my original post, just to add to the confusion even further!
John
November 1, 2011 at 5:42 am
No in the OP, I had misread that phrase => Insert a column X between A and B column.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy