Adding a new column in a certain position....

Hi,
I am writing some updates scripts for a database and need to add a new
column at a specific position in the table. I am using the 'ALTER TABLE'
command but this just tags it on the end. Is there any way of doing (via
TSQL) this without rebuilding the table?
Thanks in advance,
Stuno, there isn't. but there isn't a good reason for doing this in the first
place - at least not the one i know of. why is the order of columns in base
table important to you?
dean
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||Stu skrev:

> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
This is a common question, and the anwer is 'no' :)
Search for it on Google for example, then you'll learn that you should
not really care about in which order the columns are in a table.
/impslayer, aka Birger Johansson|||Stu,
There is no good reason (that I have come across) to need the columns in any
particular order (except, perhaps, for clarity at some stage), but
operationally, they should make no difference whatsoever.
As a side point, they may make a difference should you use the SELECT * FROM
tblMyTable, but then again, that is not good practise to do, I always state
my Selected column names, for both clarity and to ensure that should a schem
a
change occur in the underlaying table (e.g. a column added) then the same,
correct, columns are returned each time.
Hope this assists,
Tony
"Stu" wrote:

> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>|||http://www.aspfaq.com/2528
"Stu" <s.lock@.cergis.com> wrote in message
news:uL%230ep9KGHA.3836@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing some updates scripts for a database and need to add a new
> column at a specific position in the table. I am using the 'ALTER TABLE'
> command but this just tags it on the end. Is there any way of doing (via
> TSQL) this without rebuilding the table?
> Thanks in advance,
> Stu
>
>

Adding a Column in an Ordinal Position

I have a situation where I am using SQL Server 2005 and I need to be able to
add a column in a specific ordinal position. I have looked at BOL and I
wasn't able to find any mention of being able to specify Ordinal position.
Is there a way?
Thanks!
RonLogically there should be no reason to put a column in a particular slot but
you can do this via SSMS. Open the table in the table designer and insert a
column in between the two that you want. Just be aware that this will
create a new table, copy all the existing rows to it, drop the old one and
rename this one. Not something you usually care to do in a production env
but that is up to you. There is no way to do this via an alter table.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:u0TTgYYBGHA.636@.TK2MSFTNGP10.phx.gbl...
> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
> Thanks!
> Ron
>|||> I have a situation where I am using SQL Server 2005 and I need to be able
> to add a column in a specific ordinal position. I have looked at BOL and
> I wasn't able to find any mention of being able to specify Ordinal
> position.
> Is there a way?
ALTER TABLE does not support this syntax because there is really no reason
to do this. Your applications and queries do not care what order the
columns were specified / created, unless you are lazy and expect ordinal
position from "SELECT * " or "INSERT without column definition" to work.
Of course, there are kludge workarounds. But I'd be more interested in
hearing the justification for this...
Please read the following:
http://www.aspfaq.com/2528|||Without recreating the entire table with the column in appropriate position
in the CREATE TABLE statement, this is not possible.
There could be some specific exceptions however, in most cases, adding a
column in a specific position is almost meaningless since well written SQL
code generally relies on column names rather than column positions.
Anith|||> ... But I'd be more interested in
> hearing the justification for this...
>
Well, I sometimes insert a column into the middle of a table out of
consideration for the y who maintains the system - who may be me or
may be some other guy. IMO, it is nice to have attributes that are
logically connected (begin and end dates, for example) display together
in the schema. It just makes for easier reading.
My 2 cents.
Payson|||> may be some other guy. IMO, it is nice to have attributes that are
> logically connected (begin and end dates, for example) display together
> in the schema.
Why would you have a BeginDate column, and then later on decide you need an
EndDate column?
If it's important, drop and re-create the table. If you're expecting an
ALTER TABLE exception, don't hold your breath. "Logically connected" does
not have to mean "visually adjacent." Another way to simulate this is to
create a view with the columns in the order you'd like them to be, if
disrupting the existing table is out of the question.
A|||Are you saying that a table only makes sense if the columns are properly
adjacent to each other? Or are you saying that no documentation is needed if
the columns are "well aligned"?
IMHO understanding databases and tables begins with understandig the
business logic first, and not the other way around.
ML
http://milambda.blogspot.com/|||ML wrote:
> Are you saying that a table only makes sense if the columns are properly
> adjacent to each other? Or are you saying that no documentation is needed
if
> the columns are "well aligned"?
>
No. Instead, I am saying it is easier to read and understand the
schema if columns that belong together display together in the schema.
And I almost never trust the documentation for details. I have been
burned too many times by inaccurate or out of date documentation and
comments. When learning a system, I always look at the schema and the
code because that's what's running. Maybe I do that because I have
been an independent contractor for so long. I seem to always be
cleaning up someone else's mess. Oh, well, it's a living...
Payson|||Yeah. Cleaning up someone else's mess is a drag. That's why I write my own
documentation before I actually start making changes. Sometimes the schema
and the code don't reflect the actual business logic as much as they reflect
the mind-set of the author. Sadly.
ML
http://milambda.blogspot.com/

add coll in specific order

Hi All,
I use this sql to add new column in my DB, but it put this column in last
position of table, have any way to specific this position?
ALTER TABLE clientes_dados_economicos ADD matricula INT NULL
CONSTRAINT matricula__clientes_dados_economicos__u UNIQUE;
renda_mensal money no 8 19 4 no (n/a) (n/a) NULL
ddd int no 4 10 0 yes (n/a) (n/a) NULL
telefone int no 4 10 0 yes (n/a) (n/a) NULL
ramal int no 4 10 0 yes (n/a) (n/a) NULL
matricula int no 4 10 0 yes (n/a) (n/a) NULL <-- put here
----
matricula int no 4 10 0 yes (n/a) (n/a) NULL <--I want here
renda_mensal money no 8 19 4 no (n/a) (n/a) NULL
ddd int no 4 10 0 yes (n/a) (n/a) NULL
telefone int no 4 10 0 yes (n/a) (n/a) NULL
ramal int no 4 10 0 yes (n/a) (n/a) NULL
Thanks> I use this sql to add new column in my DB, but it put this column in last
> position of table, have any way to specific this position?
You need to drop the table and re-create it. Why does column position
matter?|||Retf,

> I use this sql to add new column in my DB, but it put this column in last
> position of table, have any way to specific this position?
No, there is not a straight way to accomplish this. SQL Server will put the
new column to the end when using "alter table".
You can do this from "Enterprise Manager". See the script used, before
saving the changes.
AMB
"Retf" wrote:

> Hi All,
> I use this sql to add new column in my DB, but it put this column in last
> position of table, have any way to specific this position?
> ALTER TABLE clientes_dados_economicos ADD matricula INT NULL
> CONSTRAINT matricula__clientes_dados_economicos__u UNIQUE;
> renda_mensal money no 8 19 4 no (n/a) (n/a) NULL
> ddd int no 4 10 0 yes (n/a) (n/a) NULL
> telefone int no 4 10 0 yes (n/a) (n/a) NULL
> ramal int no 4 10 0 yes (n/a) (n/a) NULL
> matricula int no 4 10 0 yes (n/a) (n/a) NULL <-- put here
>
> ----
>
> matricula int no 4 10 0 yes (n/a) (n/a) NULL <--I want here
> renda_mensal money no 8 19 4 no (n/a) (n/a) NULL
> ddd int no 4 10 0 yes (n/a) (n/a) NULL
> telefone int no 4 10 0 yes (n/a) (n/a) NULL
> ramal int no 4 10 0 yes (n/a) (n/a) NULL
>
> Thanks
>
>

Add a coulm in a table, after a specific column

I don't think there is a quick shortcut. The way I have normally done this
is to create a new table, move the data, drop the original table and rename
the new table to the original name.

You can use SSMS to generate a script for this (that is if you make the
change via the graphical interface in SSMS). This option is available
through Generate Change Script in the Table Designer menu or if you
right-click the table editor.

HTH,

Plamen Ratchev
http://www.SQLStudio.comYou can use SSMS to generate a script for this (that is if you make the

Quote:

Originally Posted by

change via the graphical interface in SSMS). This option is available
through Generate Change Script in the Table Designer menu or if you
right-click the table editor.
>

Thanks!!!|||On Wed, 31 Oct 2007 16:56:29 +0100, D. wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>You can use SSMS to generate a script for this (that is if you make the
>change via the graphical interface in SSMS). This option is available
>through Generate Change Script in the Table Designer menu or if you
>right-click the table editor.
>>

>
>Thanks!!!
>

But please do check, double-check, and triple-check the script before
executing it on a live server. There have historically been some serious
flaws in the scripted code used, that might result in losing all your
data if you are unlucky. Maybe things have changed since the last time I
looked, but I'd never run a script generated by Enterprise Manager or
SQL Server Management Studio without prior minute inspection!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||>

Quote:

Originally Posted by

But please do check, double-check, and triple-check the script before
executing it on a live server. There have historically been some serious
flaws in the scripted code used, that might result in losing all your
data if you are unlucky. Maybe things have changed since the last time I
looked, but I'd never run a script generated by Enterprise Manager or
SQL Server Management Studio without prior minute inspection!
>

Ok,
could you please me tell me which part do I have to check?

I though it is the same script that Enterprise Manager launch when I save
the modification in the data structure...

D.|||D. (d@.d.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>But please do check, double-check, and triple-check the script before
>executing it on a live server. There have historically been some serious
>flaws in the scripted code used, that might result in losing all your
>data if you are unlucky. Maybe things have changed since the last time I
>looked, but I'd never run a script generated by Enterprise Manager or
>SQL Server Management Studio without prior minute inspection!
>>

>
Ok,
could you please me tell me which part do I have to check?
>
I though it is the same script that Enterprise Manager launch when I save
the modification in the data structure...

EM and Mgmt Studio generate the same crap.

There are many things to watch out for:

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @.@.transcount 0 BEGIN END.

o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>

Quote:

Originally Posted by

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

Ok, I have to execute the DDL statements via jdbc so Ill put them inside a
unique transaction.

Quote:

Originally Posted by

>
o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

Ok

Quote:

Originally Posted by

>
o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @.@.transcount 0 BEGIN END.

I must remove all "go" statements because I'll execute the code via jdbc
driver.
I've not understood what do you mean with the phrase "wrap most statements
in EXEC" (sorry but I'm fairly new to SQLServer)

Quote:

Originally Posted by

>
o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.

Ok, I'll do.

I' also noticed that SSMS, before the ALTER TABLE statement put always some
instructions (in the automated generated SQL) that I don't unserstand
well...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE xxxxxxx

Do you think that I have to execute them from my java program or can I start
directly with ALTER TABLE instruction?

Thanks,
D.|||D. (d@.d.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>o Remove all "go" in the script, and wrap most statements in EXEC.
> The way script lookas as generated, if there is a batch-aborting
> error, the transaction is rolled back, and the rest of the statements
> will be committed. An alternative is to wrap all batches in
> IF @.@.transcount 0 BEGIN END.

>
I must remove all "go" statements because I'll execute the code via jdbc
driver.
I've not understood what do you mean with the phrase "wrap most statements
in EXEC" (sorry but I'm fairly new to SQLServer)

If you from a client, it's actually easier. Don't remove the "go" in
the sense that you send the entire script at once, but execute each
batch separately, and abort the whole affair if there is an error.
Don't forget to add a IF @.@.trancount 0 ROLLBACK TRANSACTION in this case.

The full story is this: when there is an error in an SQL command, several
things can happen, depending on the error, and many of there errors
when you work with DDL abort the batch and rollback the transaction.
Batches is separated by the "go". This means that if one batch fails
and aborts the transaction, and you run the script from a query window,
the remaining statements will still be executed - but without a transaction.

The stuff about EXEC is that instead of "ALTER TABLE ...", you need

EXEC('ALTER TABLE ...')

This is because if you put all in one batch, the script may not compile,
because it may refer to columns that has not been created yet etc.

But since you run from JDBC, you should not need this.

Quote:

Originally Posted by

I' also noticed that SSMS, before the ALTER TABLE statement put always
some instructions (in the automated generated SQL) that I don't
unserstand well...
>
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE xxxxxxx
>
Do you think that I have to execute them from my java program or can I
start directly with ALTER TABLE instruction?

Include them. Some of these settings are save with the table and per
columns. Many of these settings are already in effect, but they do
not make any harm.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Add a calculated measure to a Measure Group ?

I have created a calculated measure which is logically very much part of a specific measure group but I can't seem to work out how to add it to the measure group.. it just sits there as part of the general Measures.

Can I specifiy in which Measures group it should reside? This would also make more sense for managing Perspectives..

In the calculations tab, when you are defining the calculation, look on the toolbar for a button named "Calculation Properties". Click on that and it will open a dialog box that you can use to add either display folders or associated measure groups for each of your calculated members. Just add an associated measure group for the calculated member in question and it will then show up visually in that measure group's list of items.

HTH,
Dave Fackler

|||Thanks.. not exactly obvious is it!|||

Hi Dave,

how another property of Calculated member "Description" can be set ?

Only with notepad?

|||

Hi,

Here is how to relate the calculated memeber and the measure group

1. Open the calculations tab in the BI studio

2. Select the calculation (you want to set) from the left 'script organizer' window

3. Click on the 'Calculation properties' toolbar button (It is on the calculation tab, above the script organizer)

or from the top BI studio menu, select Cube>Calculation Properties

4. This opens a dialog box, with all your calculations on the left drop down and all the measureGroups on the right drop down. (ignore the display folder)

5. select the calculation from the left drop down and the associated MeasureGroup from the the right drop down.

Its pretty easy, you should be able to do it in a snap

Regards

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific record is in a table that has multiple varchar fields? I'm trying to do some statistics on bytes/record and can't seem to find an easy way to see what the actual storage space is.
Thanks!
SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific recor
d is in a table that has multiple varchar fields? I'm trying to do some sta
tistics on bytes/record and can't seem to find an easy way to see what the a
ctual storage space is.
Thanks!SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!

Actual number of bytes used

Is there a way in SQL to ask what the actual record size of a specific record is in a table that has multiple varchar fields? I'm trying to do some statistics on bytes/record and can't seem to find an easy way to see what the actual storage space is
Thanks!SELECT DATALENGTH(col1) + DATALENGTH(col2) ...
FROM ...
WHERE ...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Sue D" <anonymous@.discussions.microsoft.com> wrote in message
news:14EBA0E1-C638-4B17-8DF3-E84FE206BBC2@.microsoft.com...
> Is there a way in SQL to ask what the actual record size of a specific
> record is in a table that has multiple varchar fields? I'm trying to do
> some statistics on bytes/record and can't seem to find an easy way to see
> what the actual storage space is.
> Thanks!