Alter table script in SQL server 2005 Express

Question

Post reply

Alter table script in SQL server 2005 Express

manaprime

SSChasing Mays

Points: 650
More actions
June 15, 2008 at 10:26 pm

#124366

I want to generate alter table script in sql server 2005 Express.Changes i made are
add some columns in existing table and change their location of existing columns. I also want to keep data saved as it is on production.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 1

Hello,

The answer is simple with a script : no

You can only add the new columns after the last created column

you have a table with 3 columns

KeyColumn int primary key (identity)

value1 nvarchar(150)

value2 nvarchar(255)

value3 nvarchar(120)

with a script , you will add a new column value4 nvarchar(6) after value3, you can insert it between value1 and value2

see this link

http://msdn.microsoft.com/en-us/library/ms190273.aspx

but there is a solution and i think that the solution used by Sql Server Management Studio : to create a program using SMO

a table has a columnscollection in which you may insert at a given position

But a little warning : SMO is really capricious and the documentation really scarce.

Moreover, the SMO namespaces should not be the same between 2005 and 2008

Have a nice day

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

My question is why do you want the tables columns created in a specific order? When you write a select statement the columns are returned in the sequence that they are named in the select statement. When updating a row you specify the names of the columns to be updated, with the data in the values clause in the same sequence of the named columns.

For example when inserting data (From Books on Line BOL)

INSERT [INTO] table_or_view [(column_list)] data_values

The statement causes data_values to be inserted as one or more rows into the named table or view. column_list is a list of column names, separated by commas, that can be used to specify the columns for which data is supplied. If column_list is not specified, all the columns in the table or view receive data.

When a column_list does not name all the columns in a table or view, either the default value, if a default is defined for the column, or NULL is inserted into any column not named in the list. All columns not specified in the column list must either allow for null values or have a default assigned.

INSERT statements do not specify values for the following types of columns because the SQL Server 2005 Database Engine generates the values for columns of the following types:

Columns with an IDENTITY property that generates the values for the column.

Columns that have a default that uses the NEWID function to generate a unique GUID value.

Computed columns.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

nico van niekerk SSChasing Mays Points: 601 More actions · Answer 3

It IS important in which order columns are specified in a table. Having varchar columns preceding fixed-length columns place an undue burden on the server because of the overhead of having to deal with off-sets, which is a total waste when the server has to leap-frog to get to fixed-length colums.

So, have keys (varchars should never be keys, primary or foreign) at the front of the table, fixed-length columns following with variable length columns last in the table if you want to have a more efficient database.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 4

If I understand everyone else's responses correctly, then

1. You only want to control the column order to support smooth internal operation and enhance efficiency within SQL Server's internals.

2. There is no easy way to 'insert' a column in the middle using SQL Server Express.

Consequently, here is how you solve your problem.

. If Table 1 is your original table, then you create Table 2 with the additional fields and copy your Table 1 content into Table 2.

. Once you have Table 2 with all the data you need in it, then you DROP Table 1 and recreate Table 1 with all the fields you need in the correct order.

. Bring all your data back into Table 1 and your mission is accomplished. Crude but effective.

OR you can change all your table references to point to Table 2, defined the way you want, and you can just use Table 2.

nico van niekerk SSChasing Mays Points: 601 More actions · Answer 5

Correct. Even if you use Enterprise Manager or Management Studio, it will create a new table, import all the data and attributes, and then drop the old table. No magic there.

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 6

Hello,

There is a case where the new "field" is not added at the end : when it is a bit field and there are already bit fields in the table ( they are grouped by 8 to occupy a byte space )

I discovered that while reading the book Inside the Storage Engine (Author : K.Delaney)

Have a nice day

nico van niekerk SSChasing Mays Points: 601 More actions · Answer 7

Now that's a useful piece of information, although perfectly logical. Thanks for sharing.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 8

nico van niekerk (7/15/2008)
It IS important in which order columns are specified in a table. Having varchar columns preceding fixed-length columns place an undue burden on the server because of the overhead of having to deal with off-sets, which is a total waste when the server has to leap-frog to get to fixed-length colums.
So, have keys (varchars should never be keys, primary or foreign) at the front of the table, fixed-length columns following with variable length columns last in the table if you want to have a more efficient database.

I ran across your post doing some research into whether column-order can affect performance in SQL Server...do you have any book or website references supporting this post?

Here is some information from a trusted source that contradicts your claims: http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 9

As usual...it depends. I just found this little nugget and it reminded me of this thread. The article is also from the sqlskills.com tandem. It starts off by supporting the claims of the previous link I posted but then goes on to explain why within the set of fixed-width and variable-length set of columns which ones are declared as NULLable and NON-NULLable can affect row size, and therefore performance. This one presents itself as a corner-case to me but I could see it being relevant when designing work tables or archive tables where you would needs tons of NULLable varchar columns.

http://sqlskills.com/BLOGS/KIMBERLY/post/Column-order-doesnt-matter-generally-but-IT-DEPENDS!.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato