About ordering of columns

i add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns.........
how to do so ..........:)The only way to do that is dropping the table and recreating it with the columns in the position you want.

But why do you want to do this? Column order has no significant meaning in the database and can be arbitrarily changed in the the statements you use.|||I think if you use EM or SSMS to add a column you can add a column to the middle as well, but this likely does just what lexiflex suggests under the covers.

I agree with lexiflex: why do you care? The only reason I can think of is you are using "select *" in your app code, which is a no-no.|||You CAN move columns around in EM when in table design view. Just drag
the column to the position you want, and save the design.

ummmm... I would NEVER do this, I just saw somebody else do it (heh heh)|||You CAN move columns around in EM when in table design view. Just drag the column to the position you want, and save the design.but under the covers, EM creates a new table, copies the data from your original table, and then drops it|||EM creates a new table, copies the data from your original table, and then drops it
Which brings me to another reason for not using EM and making scripts yourself...

Try adding a NOT NULL-column without a default somewhere in between the other columns. Running the EM-script will cause an error about NULL-values in NOT NULL-columns just before it drops your original table and renames the temp-table. Thus making all the data from that table magically disappear... :S|||You CAN move columns around in EM when in table design view. Just drag
the column to the position you want, and save the design.

ummmm... I would NEVER do this, I just saw somebody else do it (heh heh)
I freely admit to doing this all the time.
Why? Because I like my data columns in logical groups, thats why.
But why do you want to do this? Column order has no significant meaning in the database and can be arbitrarily changed in the the statements you use.Absolutely correct. The database does not care about column order. But PEOPLE do...

About Members and Ordinal function

When I use MDX sample application,
I write a code like this:

SELECT

{[Time].[All Time].[2005]} ON COLUMNS,

{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS

FROM francis.

Here Equipment is a huge dimension table,actually I want to do it like

Filter([Equipment].[All types].[mechanism].Members to narrow the range as prevent duplicate.
But it failed.

One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.

Can someone tell me the reasons?
This is probably the same issue as I mentioned on the other thread - you probably need to use the .Children function instead of the .Members function. The .Children function returns all the members 'underneath' a member in a hierarchy, the .Members function returns all the members on a dimension or level. So:
[Equipment].[All types].[mechanism].Children
should work for you.

I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?

Chris|||Hi, Chris, give you more details as example. I assume[Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.

|||No, your last example won't do what you want, I think - it will return all the members on the same level as[Equipment].[All types].[mechanism].[machine001].[abc]. Does[Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.

You might want to read some of the articles in the 'MDX Essentials' series here:
http://www.databasejournal.com/article.php/1459531/
Especially the ones on 'family' functions such as this one:
http://www.databasejournal.com/features/mssql/article.php/2168911

HTH,

Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.

About Members and Ordinal function

When I use MDX sample application,
I write a code like this:

SELECT

{[Time].[All Time].[2005]} ON COLUMNS,

{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS

FROM francis.

Here Equipment is a huge dimension table,actually I want to do it like

Filter([Equipment].[All types].[mechanism].Members to narrow the range as prevent duplicate.
But it failed.

One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.

Can someone tell me the reasons?
This is probably the same issue as I mentioned on the other thread - you probably need to use the .Children function instead of the .Members function. The .Children function returns all the members 'underneath' a member in a hierarchy, the .Members function returns all the members on a dimension or level. So:
[Equipment].[All types].[mechanism].Children
should work for you.

I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?

Chris|||Hi, Chris, give you more details as example. I assume [Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.

|||No, your last example won't do what you want, I think - it will return all the members on the same level as [Equipment].[All types].[mechanism].[machine001].[abc]. Does [Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.

You might want to read some of the articles in the 'MDX Essentials' series here:
http://www.databasejournal.com/article.php/1459531/
Especially the ones on 'family' functions such as this one:
http://www.databasejournal.com/features/mssql/article.php/2168911

HTH,

Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.

About loop and cells

How do i do a loop to insert excel records to new excel worksheet as i onli want a certain of the columns from the existince excel. For the existince excel mi got record of column A - M then i just need to copy data of A - L then copy from the same existince column H data to the new excel worksheet. For those who know pls put up codes for me so tat i can have a better understanding over it . THankzAre you sure you have the right forum?|||oh so sorry coz i need to do this first then i can have a standard excel to push it into mssql database. SO i need to solve this situation before i can proceed. If i really post wrong so sorry as i am new .

about bcp

Hi all,

I would like to know about bcp. The input file (in parameter) must to has \t to separate the columns? Is there a way to choice the separator simbol?

for example the file:

1;Test1;active;
2;Test2;active;
3;Test3;inactive;

Yes, there is a command-line parameter "-t" which can be used to specify a field terminator. See Books Online for more details on the command-line options of BCP.

http://msdn2.microsoft.com/en-us/ms162802(SQL.90).aspx

About adding a new column...

Suppose I have a table with the following columns: Year, SalesInEurope,
SalesInAmerica, TotalSales. I want to add a new column called
SalesInAsia, say, but I want it to appear before TotalSales. How can
this be achieved?
Thanks,

BrunoWell first of all I would strongly recommend you change the design of
your table. It's a mistake to represent data in column names because it
makes your data much harder to manipulate and maintain. Basic design
sense would indicate is that the "region" is an attribute and
"America", "Asia", etc are values. In other words one would normally
expect to see ONE column for Region and one column for Sales Value.
Presenting different regions as columns is something you should do in a
report, not in a table.

To answer your specific question, there is no single command to add a
column at a particular ordinal position. The only way to do that in a
table is to re-create the table, or maybe in this case to rename the
columns and repopulate the data. In a production application table
column order is mostly unimportant - the order is determined by the
column order in a SELECT statement, not in a table. If you feel you
have a good reason to change the column order in the table on a
non-production system then you could try using the table designer in
Enterprise Manager. EM will allow you to re-order the columns and
re-create the table for you. EM will also generate the script for you
if you want to see how it's done.

Hope this helps.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> In a production application table column order is mostly unimportant -

Yes, if no humans ever look at the database - which is highly unlikely
if the system is in production.

I just get so tired of this. Everytime someone asks about placing a column
in a certain order in a table, there is always someone knowledgable who
needs to tell that person he does not need to do that.

Column order *does* matter, because it makes it so easy to work with.
The fact that SQL Server does not provide any simple command to change
order is no reason to tell people they want the wrong thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I agree with most of what you said. What I mean by "mostly unimportant"
is that for most people most of the time the column order returned by
SELECT * is not important enough to justify the effort and impact of
recreating a table. That's my experience anyway.

> The fact that SQL Server does not provide any simple command to change
> order is no reason to tell people they want the wrong thing.

I don't agree with this. There is good reason to tell people they are
wrong. Implied column ordering is a serious failing of SQL and has lead
to much bad code and incorrect results. I want to discourage anyone
from building column order dependencies into their systems. I would do
so even if SQL Server made it a trivial task to re-order columns - in
fact I'd be even more vocal about it if there were a trivial solution!
I agree that there is a case for physically ordering columns for
support and development purposes but I don't want people to confuse
that service delivery issue with their business's presentational
requirements.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> I agree with most of what you said. What I mean by "mostly unimportant"
> is that for most people most of the time the column order returned by
> SELECT * is not important enough to justify the effort and impact of
> recreating a table. That's my experience anyway.

I would agree that with the current state of the art, that rearranging
columns is a bit too much hassle. Then again, if I were to take over
maintenance were column order were accidental, and particularly different
in different in different instances of the same schema, I might consider
it. (After all, I have the tools to do this rather easily. It is just
the time to run it that could be expensive.)

> I don't agree with this. There is good reason to tell people they are
> wrong. Implied column ordering is a serious failing of SQL and has lead
> to much bad code and incorrect results. I want to discourage anyone
> from building column order dependencies into their systems. I would do
> so even if SQL Server made it a trivial task to re-order columns - in
> fact I'd be even more vocal about it if there were a trivial solution!
> I agree that there is a case for physically ordering columns for
> support and development purposes but I don't want people to confuse
> that service delivery issue with their business's presentational
> requirements.

One should not forget that there actually users out there whose only
application is Open Table in Enterprise Manager. I don't like it myself,
but there was a huge outcry when Microsoft tried to leave it out from
Management Studio in SQL 2005.

Of course, using SELECT * in combination with references to columns by
number in client code is extremely bad practice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am asked to do this on a very regular basis.

The logic I normally go through is as follows:

-delete trigger for the original table (if it exists);
-delete all views for the original table, if they exist (they will keep
referencing the original table even after it is renamed)
-rename the original table to table_name_old (executing the native
sp_rename stored procedure)
-create the new table in the correct structure
-create new default value constraints unless you're okay with system
generated names for default value constraints
-insert into new_table (all columns except for new one) as select (all
columns) from original table
-recreate view(s) if applicable
-recreate trigger if applicable
*then confirm all is well before dropping the old table.

I would recommend making a SQL script for this process, and keeping it
as a template for future use.

I also would recommend SQL scripts over EM or any other GUI: All that
GUIs will ultimately do is provide a nice pretty set of screens and
buttons, and then generate a SQL script which you may or may not see.
Getting used to coding the SQL script will eliminate the middle-man,
and allow you to comfortably replicate the same process again and
again.

I can fire you a sample script with the logic that I use if you like.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D057FB844Yazorman@.127.0.0.1...
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> > In a production application table column order is mostly unimportant -
> Yes, if no humans ever look at the database - which is highly unlikely
> if the system is in production.
> I just get so tired of this. Everytime someone asks about placing a column
> in a certain order in a table, there is always someone knowledgable who
> needs to tell that person he does not need to do that.
> Column order *does* matter, because it makes it so easy to work with.
> The fact that SQL Server does not provide any simple command to change
> order is no reason to tell people they want the wrong thing.

I can see your point of view (and often wish in EM I could see columns in
the order I want) but to a point have to disagree with it.

Let me pull a Celko here, but I think getting into the mindshift of thinking
of a table and realizing that order of columns should not be a physical
attribute of the table.

The minute folks assume they are, they start accepting code like select *
from bar and assuming that * will return columns in a specific manner. This
burned us on a code change a year or so ago where due to the way the schema
was changing (partly due to replication) the order of the columns DID in
fact change and of course the programmers who wrote the code in the height
of the dotcom era saved 30 seconds (or about 2 days "internet time" :-) by
typing * instead of a proper column list.

This was fine for 3-4 years until suddenly production code on a high volume
website broke.

Ideally Enterprise Manager could access some sort of metadata that would
store the desired view of columns, independent of the physical layout of the
table.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||BD (bobby_dread@.hotmail.com) writes:
> I also would recommend SQL scripts over EM or any other GUI: All that
> GUIs will ultimately do is provide a nice pretty set of screens and
> buttons, and then generate a SQL script which you may or may not see.
> Getting used to coding the SQL script will eliminate the middle-man,
> and allow you to comfortably replicate the same process again and
> again.

You can use EM to get a script, that you can work from. But beware
that the script that EM generates has several flaws, that you need to
fix:

o Remove all BEGIN TRANSACTION and COMMIT TRANSACTION, except the first
BEGIN and the last COMMIT.

o Remove all GO. Instead wrap all the ALTER and CREATE TABLE statements
in EXEC.

o Add SET XACT_ABORT ON first in the script.

o Replace all WITH NOCHECK in the script WITH CHECK.

o Review the script carefully, so that it does not include changes to
do not intend to make. Yes, EM, may add such changes.

The first three points has to do with the transaction scope. You may
not always want a transaction, if you have large tables. But in such
case, you must be prepared to restore a backup if the script fails.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> I can see your point of view (and often wish in EM I could see columns in
> the order I want) but to a point have to disagree with it.
> Let me pull a Celko here, but I think getting into the mindshift of
> thinking of a table and realizing that order of columns should not be a
> physical attribute of the table.
> The minute folks assume they are, they start accepting code like select
> * from bar and assuming that * will return columns in a specific manner.
> This burned us on a code change a year or so ago where due to the way
> the schema was changing (partly due to replication) the order of the
> columns DID in fact change and of course the programmers who wrote the
> code in the height of the dotcom era saved 30 seconds (or about 2 days
> "internet time" :-) by typing * instead of a proper column list.
> This was fine for 3-4 years until suddenly production code on a high
> volume website broke.

Yes, SELECT * does not belong in application code. (Unless it's some
simple throw-away thing like keep track of the local sports club.)

Just like, it is not good practice to call a routine (be that a C++
function of stored procedure) with 20 parameters with positional actual
parameters. Yet, few would argue that it's a good idea to have the
parameters in random order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 13 Sep 2005 17:06:25 -0700, BD wrote:

>I am asked to do this on a very regular basis.
>The logic I normally go through is as follows:
(snip)
>I would recommend making a SQL script for this process, and keeping it
>as a template for future use.

Hi BD,

If you make a generic template, then you might wish to include dropping
and recreation of foreign key constraints that reference the table to be
changed. You omitted that from your shortlist.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes, that is true. I was working from memory, and neglected to mention
the FKs.

Which is another good reason for using scripts - memory ain't always
all it could be. ;)|||And, recreating any PK constraints, depending on the nature of the
column change.

I'm pretty sure PK constraints will also follow the SID of the table,
and refer to the 'old' table even after it is renamed, so they should
be dropped and recreated. I could be mistaken on that point, though.|||On 14 Sep 2005 15:59:22 -0700, BD wrote:

>And, recreating any PK constraints, depending on the nature of the
>column change.
>I'm pretty sure PK constraints will also follow the SID of the table,
>and refer to the 'old' table even after it is renamed, so they should
>be dropped and recreated. I could be mistaken on that point, though.

Hi BD,

PK constraints (and all other constraints that are defined on the table)
will be dropped when the table is dropped. They should of course be
included in the CREATE TABLE statement for the new table, or added with
ALTER TABLE statements.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Right; so if you want to preserve the names of your PK constraints, it
wouldn't be enough to just recreate the new table, drop the old one,
and assume that the constraints will be intact and point to where you
expect them to... ;)