|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
**SORT THE PHYSICAL ORDER ON A TABLE'S COLUMNS**Hi
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. M
You cannot unless to re-create a table How about running 1) select f1 f2 f3 from table1 2)select f3 f2 f1 from table1 Show quote "M" <rez1***@yahoo.co.uk> wrote in message news:op.tfs46dkun9ig5y@system109.parskhazar.net... > Hi > > 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. no, I mean physical order
in other word, I want to change the columns' orders by script in order to have different order by "select * from table1". On Wed, 13 Sep 2006 12:13:54 +0330, Uri Dimant <u***@iscar.co.il> wrote: Show quote > M > You cannot unless to re-create a table > > How about running > > 1) select f1 f2 f3 from table1 > > 2)select f3 f2 f1 from table1 > > > > "M" <rez1***@yahoo.co.uk> wrote in message > news:op.tfs46dkun9ig5y@system109.parskhazar.net... >> Hi >> >> 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. > > M (rez1***@yahoo.co.uk) writes:
> no, I mean physical order First of all, keep in mind that SELECT * has no place in production code.> > in other word, I want to change the columns' orders by script in order to > have different order by "select * from table1". (Except in EXISTS subqueries, where it does not mean anything.) You should always list the columns you need explicitly. (And I mean the columns you really need. Not only because just in case.) But SELECT * is very nice for ad hoc queries when you test and debug. Changing column order of a table is a complex operation, as it reqiures creating a new table with the new order moving the data over, drop the old table and rename the new version. You need to take care to reload indexes, triggers and constraints. You also need to move foriegn keys of referencing table. Enterprise Mananger/Management Studio appears to permit you do this through a GUI operation, but they are a good example of that this is a complex operation - the scripts they generate have several serious flaws, and are unacceptable to run in a production enviroment. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Also keep in mind that the order that the columns come back when you say
SELECT * is NOT the physical order. The physical order in the data rows on the pages may be completely different that the order the come back in your query. In the physical row, the columns are stored based on whether they are fixed length or variable length. So SELECT * is somewhat arbitrary in the order the columns will come back. If YOU want a particular order, you should list that order, or create a view that lists the columns in the order you want. -- HTH Kalen Delaney, SQL Server MVP Show quote "M" <rez1***@yahoo.co.uk> wrote in message news:op.tfs615yjn9ig5y@system109.parskhazar.net... > no, I mean physical order > > in other word, I want to change the columns' orders by script in order to > have different order by "select * from table1". > > On Wed, 13 Sep 2006 12:13:54 +0330, Uri Dimant <u***@iscar.co.il> wrote: > >> M >> You cannot unless to re-create a table >> >> How about running >> >> 1) select f1 f2 f3 from table1 >> >> 2)select f3 f2 f1 from table1 >> >> >> >> "M" <rez1***@yahoo.co.uk> wrote in message >> news:op.tfs46dkun9ig5y@system109.parskhazar.net... >>> Hi >>> >>> 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. >> >> > > > > -- > Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ On Wed, 13 Sep 2006 04:50:20 -0700, "Kalen Delaney"
<replies@public_newsgroups.com> wrote: >Also keep in mind that the order that the columns come back when you say I would disagree with the statement that the order of columns when>SELECT * is NOT the physical order. The physical order in the data rows on >the pages may be completely different that the order the come back in your >query. In the physical row, the columns are stored based on whether they are >fixed length or variable length. > >So SELECT * is somewhat arbitrary in the order the columns will come back. >If YOU want a particular order, you should list that order, or create a view >that lists the columns in the order you want. using SELECT * is arbitrary in any way. The order in which columns appear when using the SELECT * syntax has, for me at least, always been 100% reliable and deterministic. It is the same order of columns assumed by an INSERT that does not specify the target columns explicitly. Since there is nothing arbitrary about INSERT, I do not believe there is anything arbitrary about SELECT *. Roy Harvey Beacon Falls, CT Roy Harvey wrote:
> I would disagree with the statement that the order of columns when In some cases it can be unfeasibly hard to guarantee column order> using SELECT * is arbitrary in any way. The order in which columns > appear when using the SELECT * syntax has, for me at least, always > been 100% reliable and deterministic. It is the same order of columns > assumed by an INSERT that does not specify the target columns > explicitly. Since there is nothing arbitrary about INSERT, I do not > believe there is anything arbitrary about SELECT *. within tables. For example in a development environment where incremental changes may be applied in something other than the ideal order. Or in a production environment where it may not be possible to justify the downtime required to re-order columns. For those reasons it may be prudent to assume that column order within tables is NOT reliable because it is somewhat beyond your control. Unfortunately there are places where logical column order becomes significant (INSERT is just one example). So developers need to make an effort to avoid the trap of building column order dependencies into code. It would be better for all concerned if those flaws were fixed but unfortunately Microsoft has added new examples of such errors rather than fixing the legacy SQL ones. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message You are not being politically correct, but you do know. But 'fixing'?news:1158151256.587872.89260@d34g2000cwd.googlegroups.com... > . > It would be better for all concerned if those flaws were fixed > but unfortunately Microsoft has added new examples of such errors > rather than fixing the legacy SQL ones. >. How in the world are they going to fix it? The thing I think you are wishing for is on your bookshelf -:) Roy Harvey wrote:
> I would disagree with the statement that the order of columns when Roy,> using SELECT * is arbitrary in any way. The order in which columns > appear when using the SELECT * syntax has, for me at least, always > been 100% reliable and deterministic. It is the same order of columns > assumed by an INSERT that does not specify the target columns > explicitly. Since there is nothing arbitrary about INSERT, I do not > believe there is anything arbitrary about SELECT *. I would disagree with your "100% reliable and deterministic" estimate. How about this simple example: CREATE TABLE a(i INT, j INT) INSERT a VALUES(1, 2) go CREATE VIEW a_v AS SELECT * FROM a go DROP TABLE a go CREATE TABLE a(j INT, i INT) INSERT a(i,j) VALUES(1, 2) go SELECT * FROM a go (1 row(s) affected) j i ----------- ----------- 2 1 SELECT * FROM a_v go (1 row(s) affected) i j ----------- ----------- 2 1 (1 row(s) affected) DROP TABLE a go DROP VIEW a_v What do you think? I think it is deterministic in the sense that a given input (i.e. table
definition) will always return the same results (i.e. column order). If you change the table definition, the results will change. The results will, however, be consistent for a given table definition. None of this changes the fact that select * is terrible practice in production code. Show quote "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1158153733.607690.56400@i42g2000cwa.googlegroups.com... > > Roy Harvey wrote: > > I would disagree with the statement that the order of columns when > > using SELECT * is arbitrary in any way. The order in which columns > > appear when using the SELECT * syntax has, for me at least, always > > been 100% reliable and deterministic. It is the same order of columns > > assumed by an INSERT that does not specify the target columns > > explicitly. Since there is nothing arbitrary about INSERT, I do not > > believe there is anything arbitrary about SELECT *. > > Roy, > > I would disagree with your "100% reliable and deterministic" estimate. > How about this simple example: > > CREATE TABLE a(i INT, j INT) > INSERT a VALUES(1, 2) > go > CREATE VIEW a_v > AS > SELECT * FROM a > go > DROP TABLE a > go > CREATE TABLE a(j INT, i INT) > INSERT a(i,j) VALUES(1, 2) > go > SELECT * FROM a > go > > (1 row(s) affected) > > j i > ----------- ----------- > 2 1 > > SELECT * FROM a_v > go > > (1 row(s) affected) > > i j > ----------- ----------- > 2 1 > > (1 row(s) affected) > > > > DROP TABLE a > go > DROP VIEW a_v > > > What do you think? > Yes, SELECT * in a view is quite vulnerable to changes in the
underlying tables, and stored procedures that reference those views are in turn vulnerable as well. If the second generation of the table had named the columns [banana] and [apple] the view would still return (i,j). So it illustrates not a sequencing problem, but that the mapping is positional when the view is generated and remains positional. At all times the order of the columns, or perhaps it is clearer to say column headings, of a SELECT * from the view matches the order from sp_help against the view. So I think it is interesting, but not particularly relevant. Roy Harvey Beacon Falls, CT On 13 Sep 2006 06:22:13 -0700, "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote: Show quote > >Roy Harvey wrote: >> I would disagree with the statement that the order of columns when >> using SELECT * is arbitrary in any way. The order in which columns >> appear when using the SELECT * syntax has, for me at least, always >> been 100% reliable and deterministic. It is the same order of columns >> assumed by an INSERT that does not specify the target columns >> explicitly. Since there is nothing arbitrary about INSERT, I do not >> believe there is anything arbitrary about SELECT *. > >Roy, > >I would disagree with your "100% reliable and deterministic" estimate. >How about this simple example: > >CREATE TABLE a(i INT, j INT) >INSERT a VALUES(1, 2) >go >CREATE VIEW a_v >AS >SELECT * FROM a >go >DROP TABLE a >go >CREATE TABLE a(j INT, i INT) >INSERT a(i,j) VALUES(1, 2) >go >SELECT * FROM a >go > >(1 row(s) affected) > >j i >----------- ----------- >2 1 > >SELECT * FROM a_v >go > >(1 row(s) affected) > >i j >----------- ----------- >2 1 > >(1 row(s) affected) > > > >DROP TABLE a >go >DROP VIEW a_v > > >What do you think? Maybe I'm missing something, but I wasn't able to find where in BOL or
in MSDN they guarantee that SELECT * lists columns in the order of ORDINAL_POSITION, left to right. I guess if it is not guarantteed, it could be changed, right? Just like views with SELECT TOP 100 PERCENT. Makes sense? On 13 Sep 2006 12:02:26 -0700, "Alexander Kuznetsov"
<AK_TIREDOFSPAM@hotmail.COM> wrote: >Maybe I'm missing something, but I wasn't able to find where in BOL or I don't have a copy of the SQL standard - I can never make sense of>in MSDN they guarantee that SELECT * lists columns in the order of >ORDINAL_POSITION, left to right. I guess if it is not guarantteed, it >could be changed, right? Just like views with SELECT TOP 100 PERCENT. >Makes sense? the way they write that sort of thing anyway - but I always assumed, and continue to assume, that the standard covers the issue of the order of columns within a table as part of the core requirements for compliance. The fact that it is probably not documented that way in BOL would be of no particular consequence in that case. But perhaps I am wrong about the standard. Roy M wrote:
Show quote > Hi The column order is irrelevant, because you shouldn't be using SELECT * > > 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. for anything but testing. If you want the fields returned in the order f3/f2/f1, then your SELECT statement needs to be: SELECT f3, f2, f1 FROM table >> 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. Thinklogical 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".  |
|||||||||||||||||||||||
Other interesting topics