SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, September 20, 2006

SORT THE PHYSICAL ORDER ON A TABLE'S COLUMNS

SQL Apprentice Question
I'm working in SQL2000 . how can I move the physical position of a column
in a table by script?
for example:


select * from table1


f1 f2 f3
---- ----- -----


desired result:
f3 f2 f1
---- ----- -----


any help would be greatly appreciated.


Celko Answers
>> how can I move the physical position of a column in a table by script? <<


You have the wrong mental model of how RDBMS and SQL work. Think
logical and not physical models.

One of the many reasons that columns are not like fields is that they
have no physical location and are not required to be contigous. You
use a column name to get their data. Likewise, the rows in a table have
no ordering, unlike the records in a file. You locate them by a key.


In a file system, 3GL commands like "READ (a,b,c) FROM FileA" give you
different results than "READ (c,a,b) FROM FileA" while "SELECT a,b,c
FROM TableA" has the same data as "SELECT c,a,b FROM TableA".

No comments: