January 25, 2006 at 4:02 am
Hi
I wonder if you change order of fields in your database if it cause problems on different aims of use.
Doest it cause problems on DTS or replication or syncronization or comaparing of two identical schema with different fields order ?
any experience ?
January 25, 2006 at 7:10 am
First, why would you want to do that? In your select, you can get the columns/fields in any order you want.
Second, it's a real hassle to change the order. Enterprise Manager makes it seem easy, but it is really copying the whole table (SELECT INTO) into a new table with the fields in your new order, dropping the original table and then copying the new table back to the original table's name.
Is it really worth the effort?
-SQLBill
January 26, 2006 at 3:12 am
If your coding practice is "SELECT * FROM X" then the columns won't come back in the order you may expect....which is 1 good reason either to avoid moving columns around or not to use this coding style.
January 26, 2006 at 5:15 am
The ordering of columns in tables (or sets in general) is a flaw in SQL and one of the reasons it is not truly relational. Don't reinforce the problem by actually relying on a specific order.
January 26, 2006 at 8:49 am
In the first place, I have never really had any problems moving columns around, but I usually do it in the pliminary stages and there is not much data in them.
One possible issue you may have, is developers sometimes reference the column position, rather than the column name (bad programmer!) If you move things around, you just blew up his or her program.
Michael Lee
January 26, 2006 at 10:52 am
If they are using open SQL in their applications rather than stored procedure calls, then they really are bad programmers.
January 27, 2006 at 12:50 am
Thank you all try to find some of the problems.
As i see First problem is Bad programmers using SELECT * statement
I think one should get big problem when using BULK INSERT am i correct?
third one is schema presentation should give a nice logical view
to the third parts.
January 27, 2006 at 1:36 am
Why? Sure, I agree that stored procedures have many benefits and I usually use them myself, but there is nothing inherently wrong with not using them. On the contrary, overuse of stored procedures (e.g. writing much to complicated procs) is a common mistake.
January 27, 2006 at 6:04 am
On the contrary, overuse of stored procedures (e.g. writing much to complicated procs) is a common mistake.
May i know the reason for this, as we are heavily dependant on stored procedures for any almost any database operation.
January 27, 2006 at 8:14 am
What I meant is that some people do not want to write any logic outside of a stored procedure and instead create huge stored procedures that they call from an application. There is nothing inherently wrong with this either, it is just that these procedures tend to be badly written and often do not perform well because of this. A typical example is complicating procs so much that they need to be recompiled each time they are run (or even worse they should be recompiled but is not and end up using bad exec plans).
January 31, 2006 at 12:50 am
Discussion here is
Changing of Field order in a database schema
and nor storedprocedures please pay attention!
thnks
February 1, 2006 at 2:52 pm
Actually, I am in the same situation right now. We had to add GUIDs to about 200 tables and of course SQL Server added them at the end. I would like this GUID to appear 2nd (after Primary Key). I would like to know how to do this programmatically (without having to open each table in Enterprise Manager and drag the field to the 2nd position).
February 2, 2006 at 12:43 am
I am almost sure that your schema had that logical order which caused
that all ID came to the last column. You must have correct schema
on logical design. if you run something in a later structure then it comes later.
February 2, 2006 at 12:53 am
You can do it programmatically, but it will require to replace all of the tables with new tables. All the data needs to be moved to new tables, the old ones dropped and then the new ones renamed back to the old names. This is a huge operation that will not be nice for you server to handle.
Why is it such a problem that the column names are shown in a specific order in Enterprise Manager?? In any client that you write that use data in the table you should specify exactly which columns you want. There you can specify the order in which you want the columns. Never simply use SELECT *. And make sure that your clients are not relying on whichever order you specify the columns in. Clients should access columns by name, not by ordinal position.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply