Musing of a SQL Server User: Crazy column ordering

Jaymer

We all know that AIM shows us fields sorted by field name, and up til now, I hadn't really cared about what was going on in the back end, although I'd seen people write some things about it.
With Derby, I wasn't using any backend tool so how AIM took care of things was its business.

But now I'm working with a big client with big data in MS SQL Server... and other people down the road are going to possibly be hitting this data with various reporting tools.
One of my tables is a total mess - its like a jigsaw puzzle of field names.

Frankly, someone down the road is going to be asking "what the hell was this designer thinking when he laid out these fields in this table".
All my life I've kinda laid out table structures with key fields "in the front" trailing off to less important fields at the end.

I'm contemplating manually creating an 85-field table just to order the fields in some semblance of logical order/structure, so that when viewing them (even in SMSS) it makes some kinda sense.

How have you guys handled this?
thx

CalD

Hi Jamer,

Not sure if you are aware but within SQL Management studio you can edit the table and drag/dropthe fields into the order you want them in... I haven't tested doing this and if it would have any impact on aware but I doubt it would...

Like you we have always setup tables with most important fields first tapering off to least important, I did notice awares seemingly random ordering on the back end but hadn't been too fused about it so far.

Jaymer

Sure enough, it DOES let you reorder fields. Then I got this msg when saving:
Screen Shot 2017-01-19 at 11.15.58 AM.png

I'm sure that cause of some constraints or PK or something from aware... it gave me the option to save a text file, which i though would be a create table statement, so I saved it and its empty, so I'm probably not going to drag 85 fields around again... 🙁

aware_support

But now I'm working with a big client with big data in MS SQL Server...

You are working without a license?

johntalbott

Jaymer wrote
Sure enough, it DOES let you reorder fields. Then I got this msg when saving:
[attachment=1]Screen Shot 2017-01-19 at 11.15.58 AM.png[/attachment]

I'm sure that cause of some constraints or PK or something from aware... it gave me the option to save a text file, which i though would be a create table statement, so I saved it and its empty, so I'm probably not going to drag 85 fields around again... 🙁

This a SQL Server Mgmt Studio option that you can turn off to allow making table design changes that require dropping and recreating tables. It's under Tools > Options >

Note: Of course you'll want to test this on a non-critical database and for the real deal have a plan b to in the event something gets hosed in the process.

Jaymer

aware_support wrote
But now I'm working with a big client with big data in MS SQL Server...
You are working without a license?

thanks for pointing that out.
obviously, its in the 30-day eval mode.
finishing proof of concept, but it doesn't change the validity of my concerns.

Jaymer

Are you saying that if I'm on a paid license I won't see this weird behavior with field ordering?

Will convert this to a developer license and then supply this in SAAS mode... using SQL SERVER Express.

aware_support

No, paid license doesn't change anything. Frankly, I don't understand what your concerns are. That Aware IM names the fields the way it does? Or that your SQL Server Studio doesn't order them? The latter is not the Aware IM issue, the former is what it is and you cannot change Aware IM field naming convention. I cannot see how this can even remotely be a problem...

johntalbott

I believe that he just wants the SQL table columns ordered in a way that they are more developer friendly when viewing from SSMS or another tool.

Example:
Unfriendly = FirstName, Zip, Address, State, Zip, LastName
Friendly = FirstName, LastName, Address, City, State, Zip

So if he reorders the table columns using SSMS ... will it break anything in AwareIM?

I would not expect that AwareIM cares about the order of the table columns, but one never knows.

aware_support

No, it would not break anything. Feel free to reorder as you see fit.

BLOMASKY

Jaymer, before you put any real data in the tables, just use SQL Mgmt Studio, reorder the fields the way you want, drop and recreate the table
and also, creating multi-segment indexes can help performance a LOT

Also, use your fav. ER diagraming tool, import the database and use that to change the view to order the fields how you like.
Bruce