April 14, 2011 at 8:09 am
Is it possible to create a new field in between two existing fields by using T-SQL?
For eg:
CREATE TABLE Employee
(
Id INT,
Name VARCHAR(300),
AddressId INT,
DateOfBirth DATETIME
)
I have created this table. Now, I want to create a new CommunicationAddressId field in between AddressId and DateOfBirth by using ALTER script.
ALTER TABLE Employee ADD CommunicationAddressId INT
The above script will create the CommunicationAddressId field after DateOfBirth field.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 14, 2011 at 8:27 am
The ordering of the columns as they appear in a table definition script is cosmetic, they are organized internally in a way that is best for SQL.
The probability of survival is inversely proportional to the angle of arrival.
April 14, 2011 at 8:39 am
sturner (4/14/2011)
The ordering of the columns as they appear in a table definition script is cosmetic, they are organized internally in a way that is best for SQL.
Your statement is usually correct, but as usual it depends. Check out this blog post from Kim Tripp which explains why column order can matter: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Column-order-doesnt-matter-generally-but-IT-DEPENDS!.aspx
Regarding the original post:
C.K.Shaiju (4/14/2011)
Is it possible to create a new field in between two existing fields by using T-SQL?
No, not as far as I know. To my knowledge you will need to drop and recreate the table. MySQL has syntax like ALTER TABLE ADD new_column_name AFTER existing_column_name. It would be nice to have in SQL Server, however the implications of doing that in terms of altering a large table may be why they have left it as a manual process to drop and recreate the table...to force the user to think about the data movement...or maybe they have better things to do with their development dollars 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 14, 2011 at 9:00 am
You can do this in SSMS.
Right click on your table and select Design, you can then drag your column to the position you want it.
In order to do this though, SSMS has to create a temporary table with the new definition, copies all of your data into it, then drops the original table and renames the temporary table back to the original name. If you want to see the T-SQL statements that SSMS generates so that you can put them in your own scripts, put a SQL Server Profiler Trace on whist you do it.
You may get a message that changes that require tables to be recreated are not allowed. If this happens, select Tools>Options>Designers>Table and database Designers and uncheck the option that says "Prevent saving changes that require table re-creation"
On last thought on this. you should consider carefully why you want to do this and if it is really necessary. your queries, and the applications that rely on them ought to agnostic to the ordinal position of the columns.
April 14, 2011 at 9:00 am
As sturner said, this is only for cosmetic. For that, recreating table is unnecessary. Thanks to you both :-).
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 14, 2011 at 9:21 am
Ben, While adding a new field in the designing tool, we can add it in a position wherever we want. But later when we create the field, I need the ALTER script as it is required to send to the production. So I will run the script here in my place and create the field. That time field will be created in the bottom of the actual table. When we look the table in the designing tool and actual table fields, it will be in different positions. It is little (VERY little:smooooth:) uncomfortable for me. But when I came to know the solution from you both, I don’t feel that much uncomfortable:-D. Thank you.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 14, 2011 at 10:01 am
While adding a new field in the designing tool, we can add it in a position wherever we want
This is what it looks like, but behind the scenes the designer has to create a new table and copy your data into it to acheive this. You will need to a similar copy, drop and rename operation in your scripts if you want your production databases to flect your dev environment in this way.
Why is the column order so important? (other than for tidiness which I fully understand )
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply