I need to list columns from a table in the table definition order:
select * from syscolumns where id = object_id('MyTable') --order by colid
syscolumns tables two columns look relevant:
colorder. The MSDN article on syscolumns says:
colid | smallint | Column or parameter ID. colorder | smallint | Identified for informational purposes only. | Not supported. Future compatibility is not guaranteed.
I tried to run
select * from syscolumns where colorder <> colid
which yielded no rows, and that makes me think that these columns has the same values most of the time.
It does look that the safest bet is to use colid. However I would be curious to know: is there a difference between these two columns, and if there is, what is this difference?
Also the MSDN article, does not confirm, that colid reflects the order of the table definition. While this is reasonable to assume that this is the case, could you please let me know, if you are sure that it's the case, how you know that this is?