SQL Anywhere database - Rearrange order of columns

Author

Rearrange order of columns

Steven J. Serenska

2005-05-24, 7:23 am

Hello:

I am getting ready to document a database I am working on and am
wondering if there is an easy way to rearrange the order of the columns
in the various tables.

As it stands now, columns are included in a table in the order they were
added by the designer, which may or may not be the most intuitive order.
An example might be:

LastName
MiddleName
Address1
City
State
FirstName
PostalCode

In this fake case, I'm trying to represent that the designer remembered
he had to include a first name column after entering the other address
fields.

Because I use a tool that dumps the DB/Table structure and column
comments to a text file, the overall readability of the docs can be
improved by being able to arrange the columns on the db in a sensible
order before running the tool.

I have noticed that there is no "drag & drop" column-ordering facility
in the Sybase Central designer (e.g., like Access or SQL Server). The
only way I can see to do this would be to unload the db, manually alter
the column order in the CREATE TABLE statements in the reload script,
and then alter all the corresponding LOAD TABLE statements so that the
columns still lined up.

Is there an easier way to do this that I am overlooking?

Thanks.

Steven J. Serenska

Greg Fenton

2005-05-24, 9:23 am

Steven J. Serenska wrote:
>
> The
> only way I can see to do this would be to unload the db, manually alter
> the column order in the CREATE TABLE statements in the reload script,
> and then alter all the corresponding LOAD TABLE statements so that the
> columns still lined up.
>
> Is there an easier way to do this that I am overlooking?
>

This is the only way to ensure that the columns get the order you want
them to have. You might be able to do it by dropping and re-adding
individual columns, but that is a hit-and-miss approach (at best).

BTW: depending on the version of ASA that you are using you might want
to consider the column order not for documentation purposes, but for
physical layout reasons. Having frequently accessed columns listed
first and lesser access columns later means the database can perform
less work for most queries.

Have you considered using a tool such as PowerDesigner to design your
conceptual as well as your physical layouts? The conceptual will map
onto your "documentation" view of the world, whereas the physical will
map onto "DBA" view of the world where decisions are made independent of
the specific data being stored.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Nick Elson

2005-05-24, 11:23 am

No, not physically anyway.

If your tool can access a view instead, you can reorder the
column list in a view definition set up just for that purpose.
That would at least address your (implied) 'preferred order.

But to modify the entities in the system tables, that requires a
UNLOAD/OUTPUT, DROP and CREATE TABLE sequence..

As to documenting the columns (or to assist in the recreation of
the table), the LOAD TABLE statement accepts a column
list. Further OUTPUT can be used to create exports
in any column order (via the preceding select statement).

In recreating the table an OUTPUT and the preceding select
can order the exported data in any column order desired. Alternatively
you can just UNLOAD the data and reorder the columns (after
the drop+create table) in LOAD TABLE statement using the
column list.

Alternatively you could even consider replacing your tool with
a combination of SELECT+OUTPUT plus LOAD TABLE
(with the column-list).

About the only ***CAVEAT***, I can think of, concerning
changing the column order applies to primary keys and (table)
uniqueness constraints in general. The significance of the column
order is maintained in any uniqueness constraints (including primary
keys). If your primary key (or any uniqueness constraint) includes
multiple columns then it may be changed by a reordering.

As a hypothetical example, if your primary key was defined
on these columns

LastName, MiddleName, FirstName

changing the column order to

FirstName, MiddleName, LastName

would result in a very different primary key index. The original
index could be used for queries search for LastName order
or searches on just LastName. The new col. order would
require an additional index for that, unless every query you have
also searches or orders on FirstName and MiddleName as
well.

If this is your case, then some queries may start to perform
sub-optimally.

"Steven J. Serenska" < sjs@RemoveTheseWords
Banksurance.com> wrote in message
news:42930f9b$1@foru
ms-2-dub...
> Hello:
>
> I am getting ready to document a database I am working on and am wondering
> if there is an easy way to rearrange the order of the columns in the
> various tables.
>
> As it stands now, columns are included in a table in the order they were
> added by the designer, which may or may not be the most intuitive order.
> An example might be:
>
> LastName
> MiddleName
> Address1
> City
> State
> FirstName
> PostalCode
>
> In this fake case, I'm trying to represent that the designer remembered he
> had to include a first name column after entering the other address
> fields.
>
> Because I use a tool that dumps the DB/Table structure and column comments
> to a text file, the overall readability of the docs can be improved by
> being able to arrange the columns on the db in a sensible order before
> running the tool.
>
> I have noticed that there is no "drag & drop" column-ordering facility in
> the Sybase Central designer (e.g., like Access or SQL Server). The only
> way I can see to do this would be to unload the db, manually alter the
> column order in the CREATE TABLE statements in the reload script, and then
> alter all the corresponding LOAD TABLE statements so that the columns
> still lined up.
>
> Is there an easier way to do this that I am overlooking?
>
> Thanks.
>
> Steven J. Serenska

Steven J. Serenska

2005-05-24, 8:23 pm

Greg, Nick:

Thanks for the responses. It's pretty clear that there's only one way
to do it.

Greg, I was interested to read about the frequently accessed columns
issue. I'll bear this in mind when I establish the final column orders.

Nick, thanks for the caveats. All of my tables have an autoincrement
primary key which always comes first, so there should be no issue in my
case.

Thanks again.

Steven J. Serenska

Greg Fenton

2005-05-24, 8:23 pm

Steven J. Serenska wrote:
>
> Greg, I was interested to read about the frequently accessed columns
> issue. I'll bear this in mind when I establish the final column orders.
>

In the ASA 9.x online docs, see this "Top Performance Tips" page:

ASA SQL User's Guide
Monitoring and Improving Performance
Top performance tips
- Pay attention to the order of columns

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links