Alter UserDefined Datatype

How can I alter a userdefined datatype.

eg: A user defined datatype of Dt_Quantity numeric(16,0)

Want to change it to numeric(16,3).

Is it possible?

If it requires a system catelog updation please help.

This requirment is came after the implementation of the DataBase.

Please help me fast|||which version of sql server are you using ?|||Using the SQL Server 2005|||

you have to drop it and recreate...

From BOL :

Note: User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause. For more information, see ALTER ASSEMBLY (Transact-SQL).

Madhu

Alter table db size getting increased.

Hi,
I am doing alter table and updating Nvarchar datatype to Ntext. AS there are
almost
54,09,873 records installation is taking more than 3 hours and log/database
files are getting increased beyond limit. Now there is hardly space left and
still installation is not completed.
Tasks Restore Size After installation
Datafile Size 39000 46529 MB
Log Size 4550 14032 MB
ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
54,09,873 Rows affected.
As far as I know and understand Alter basically copy the contents to some
temp table, deletes the table, recreate the table with new definition and
again copy back to original table( Sorry if sequence is wrong ?) and I thi
nk
this is the reason why this script is taking more space and impacting log
size.
IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
SIZE OF DB INCREASE.
--
SanjayYes, you might try dumping the data into an external text file, and then bc
p
or bulk insert it back into the new table structure.
"Sanjay" wrote:

> Hi,
> I am doing alter table and updating Nvarchar datatype to Ntext. AS there a
re
> almost
> 54,09,873 records installation is taking more than 3 hours and log/databas
e
> files are getting increased beyond limit. Now there is hardly space left a
nd
> still installation is not completed.
> Tasks Restore Size After installation
> Datafile Size 39000 46529 MB
> Log Size 4550 14032 MB
> ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
> 54,09,873 Rows affected.
> As far as I know and understand Alter basically copy the contents to some
> temp table, deletes the table, recreate the table with new definition and
> again copy back to original table( Sorry if sequence is wrong ?) and I t
hink
> this is the reason why this script is taking more space and impacting log
> size.
> IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
> SIZE OF DB INCREASE.
> --
> Sanjay|||Thanks for your help,
can you help me in syntax and will this be logged in log file.
i never tried that but will this help in reducing time and DB size issues.
"CBretana" wrote:
> Yes, you might try dumping the data into an external text file, and then
bcp
> or bulk insert it back into the new table structure.
> "Sanjay" wrote:
>|||Look up BCP in BOL -- it's a utility that comes w/SQL server.
Setting the logging level to SIMPLE would help too -- look up ALTER
DATABASE; the setting is SET RECOVERY
"Sanjay" wrote:
> Thanks for your help,
> can you help me in syntax and will this be logged in log file.
> i never tried that but will this help in reducing time and DB size issues.
> "CBretana" wrote:
>|||I tried but log file was increased around 5 gb and data file also.
i am doing something like this.
alter database xyz
set recovery simple
alter table and modifying nvarchar to ntext ( around 54,09,873 rows)
set recovery full.
"KH" wrote:
> Look up BCP in BOL -- it's a utility that comes w/SQL server.
> Setting the logging level to SIMPLE would help too -- look up ALTER
> DATABASE; the setting is SET RECOVERY
>
> "Sanjay" wrote:
>

Alter Table Alter Column sometimes fails when changing to Not

Hi Martin,
I think that solves my problem -- perhaps slightly indirectly. In the Alter
statements I did not specify the datatype because I was not changing it.
Perhaps the statements defaulted to something other than the float(8) of the
original columns?
I'll try explicitly repeating the current datatype and see if that makes the
message disappear.
Follow-up question: If I drop the offending "DF__Temporary__..." constraints
do they get recreated automatically?
Thanks much and Best regards,
--
Doug MacLean
"Martin C K Poon" wrote:

> I think the error message raises when you change a column definition from
> float(8) to int, that is having a default value of data type "float(8)".
> Use "sp_help tblVendQuotePrice" to check the constraints on the table
> tblVendQuotePrice.
> Check the constraint_type and constraint_keys columns from the last result
> as returned from sp_help, and check the default values.
> Upon altering the column definition , you will need to drop the default
> constraint "DF__Temporary__VQuot__22B77893" when the data type of the colu
mn
> does not match the data type of the default value.
> ALTER TABLE tblVendQuotePrice DROP CONSTRAINT DF__Temporary__VQuot__22B778
93
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> |b?l￥ó
> news:4EBF1421-AB37-4101-859D-23D4B4D5ECB8@.microsoft.com ¤¤???g...
> tables:
> the
>
>> In the Alter
> statements I did not specify the datatype because I was not changing it.

I see datatype 'int' as specified in the ALTER TABLE statement from original
post. Perhaps the wrong datatype (int instead of float) was inadvertently
specified.
> Follow-up question: If I drop the offending "DF__Temporary__..."
> constraints
> do they get recreated automatically?
There is no automatic recreation of constraints using Transact-SQL scripts.
If you want to change the column datatype, you need to drop constraints
referencing the column, alter the column and then recreate the constraints.
Hope this helps.
Dan Guzman
SQL Server MVP
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> wrote in message
news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com...
> Hi Martin,
> I think that solves my problem -- perhaps slightly indirectly. In the
> Alter
> statements I did not specify the datatype because I was not changing it.
> Perhaps the statements defaulted to something other than the float(8) of
> the
> original columns?
> I'll try explicitly repeating the current datatype and see if that makes
> the
> message disappear.
> Follow-up question: If I drop the offending "DF__Temporary__..."
> constraints
> do they get recreated automatically?
> Thanks much and Best regards,
> --
> Doug MacLean
>
> "Martin C K Poon" wrote:
>|||Please refer to BOL for more information.
- CREATE DEFAULT
- sp_binddefault
- sp_unbinddefault
- DROP DEFAULT
When you create a column with a default value (either by using CREATE
TABLE... or ALTER TABLE ADD column), SQL Server creates an object called a
"default" automatically. This default object will then bound to a column.
To verify this, you can obtain the list of default objects from the
following query. You can find your "DF__Temporary__..." default objects from
the result.
SELECT name AS myDefaultObjects FROM sysobjects WHERE type = 'D' ORDER BY
name
You can create/drop default objects using CREATE DEFAULT, DROP DEFAULT.
After creating the default objects, you can use sp_binddefault and
sp_unbinddefault to bind/unbind the default objects to your columns.
For the current case, the "DF__Temporary__..." default objects will *not* be
binded to your columns automatically.
You will need to create a default object (using CREATE DEFAULT) and bind it
to the column (using sp_binddefault).
Martin C K Poon
Senior Analyst Programmer
====================================
"Doug MacLean" <DougMacLean@.discussions.microsoft.com> bl
news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com g...
> Hi Martin,
> I think that solves my problem -- perhaps slightly indirectly. In the
Alter
> statements I did not specify the datatype because I was not changing it.
> Perhaps the statements defaulted to something other than the float(8) of
the
> original columns?
> I'll try explicitly repeating the current datatype and see if that makes
the
> message disappear.
> Follow-up question: If I drop the offending "DF__Temporary__..."
constraints
> do they get recreated automatically?
> Thanks much and Best regards,
> --
> Doug MacLean
>
> "Martin C K Poon" wrote:
>
from
result
column
DF__Temporary__VQuot__22B77893[color=dar
kred]
fails
make
objects
explicitly
error.|||Thanks much, Martin.
Best Regards,
--
Doug MacLean
"Martin C K Poon" wrote:

> Please refer to BOL for more information.
> - CREATE DEFAULT
> - sp_binddefault
> - sp_unbinddefault
> - DROP DEFAULT
> When you create a column with a default value (either by using CREATE
> TABLE... or ALTER TABLE ADD column), SQL Server creates an object called a
> "default" automatically. This default object will then bound to a column.
> To verify this, you can obtain the list of default objects from the
> following query. You can find your "DF__Temporary__..." default objects fr
om
> the result.
> SELECT name AS myDefaultObjects FROM sysobjects WHERE type = 'D' ORDER BY
> name
> You can create/drop default objects using CREATE DEFAULT, DROP DEFAULT.
> After creating the default objects, you can use sp_binddefault and
> sp_unbinddefault to bind/unbind the default objects to your columns.
> For the current case, the "DF__Temporary__..." default objects will *not*
be
> binded to your columns automatically.
> You will need to create a default object (using CREATE DEFAULT) and bind i
t
> to the column (using sp_binddefault).
> --
> Martin C K Poon
> Senior Analyst Programmer
> ====================================
> "Doug MacLean" <DougMacLean@.discussions.microsoft.com> |b?l￥ó
> news:D2685A80-7C93-4657-9219-912C4423FC43@.microsoft.com ¤¤???g...
> Alter
> the
> the
> constraints
> from
> result
> column
> DF__Temporary__VQuot__22B77893
> fails
> make
> objects
> explicitly
> error.
>
>

alter table - change time evaluation

Hi,

I have a table with 70 million records. I want to change the datatype of one of the columns from int to decimal(18,3). What is the best way of doing it? and how can I evaluate the time change before I start and make the change?

thank you,

Tomer

..assuming you want to know how long the operation would take....this varies massively by table definition + indexes + hardware + load

you could use SELECT INTO to create a table of one million records and add indexes identical to those on the production table.

Then drop the index on the int column on that table,

execute an ALTER TABLE ALTER COLUMN to change the datatype

and rebuild the index.

Then multiply the result by 70 though that's only an estimate as the rebuild time may not be linear.

Alter Table - Change Column Datatype

Hi,

I want to change the datatype of an existing column from char to
varbinary. When I run the "Alter Table" statement, I get the
following error message -

Disallowed implicit conversion from data type char to data type
varbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERT
function to run this query.

Can the CONVERT function be used as part of an alter table/alter
column? Is there another way besides renaming the table and creating
a new one?

Thanks,
BruceOn 19 Apr 2004 11:29:46 -0700, Bruce wrote:

>Hi,
>I want to change the datatype of an existing column from char to
>varbinary. When I run the "Alter Table" statement, I get the
>following error message -
>Disallowed implicit conversion from data type char to data type
>varbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERT
>function to run this query.
>Can the CONVERT function be used as part of an alter table/alter
>column? Is there another way besides renaming the table and creating
>a new one?
>Thanks,
>Bruce

Yes, there is another way: rename not the whole table, but just the
column, then create a new one:

EXEC sp_rename 'test.dbo.testalter.col1' 'col1old', COLUMN
go
ALTER TABLE test.dbo.testalter
ADD col1 varbinary(321) NULL
-- If it has to be NOT NULL, change this to read
-- ADD col1 varbinary(321) NOT NULL DEFAULT 0
go
UPDATE test.dbo.testalter
SET col1 = CAST(col1old AS varbinary(321))
go
ALTER TABLE test.dbo.testalter
DROP COLUMN col1old
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Alter table

I have a table which has a field with datatype text , now i want to change this field to varchar.

How i can do that.

Thanks in advance...alter table mytable alter column mycol varchar(50)

Alter statement

Hi All,
Can I use one alter statement to change 2 columns' datatype.
Or Is there any command that I can use to change 2 columns' datatype.
For example : I have the table tableA as below
create table tablea (ca smallint not null, cb smallint not null)
and I want to change to int not null for both co
Hi Jodie,
You only get one ALTER COLUMN statement per ALTER TABLE statement:
ALTER TABLE tablea ALTER COLUMN ca int NOT NULL
ALTER TABLE tablea ALTER COLUMN cb int NOT NULL
Books-Online ALTER TABLE: http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Thanks,
Ken
On Feb 16, 6:49 pm, Jodie <J...@.discussions.microsoft.com> wrote:
> Hi All,
> Can I use one alter statement to change 2 columns' datatype.
> Or Is there any command that I can use to change 2 columns' datatype.
> For example : I have the table tableA as below
> create table tablea (ca smallint not null, cb smallint not null)
> and I want to change to int not null for both co

Alter statement

Hi All,
Can I use one alter statement to change 2 columns' datatype.
Or Is there any command that I can use to change 2 columns' datatype.
For example : I have the table tableA as below
create table tablea (ca smallint not null, cb smallint not null)
and I want to change to int not null for both coHi Jodie,
You only get one ALTER COLUMN statement per ALTER TABLE statement:
ALTER TABLE tablea ALTER COLUMN ca int NOT NULL
ALTER TABLE tablea ALTER COLUMN cb int NOT NULL
Books-Online ALTER TABLE: [url]http://msdn2.microsoft.com/en-us/library/ms190273.aspx[/
url]
Thanks,
Ken
On Feb 16, 6:49 pm, Jodie <J...@.discussions.microsoft.com> wrote:
> Hi All,
> Can I use one alter statement to change 2 columns' datatype.
> Or Is there any command that I can use to change 2 columns' datatype.
> For example : I have the table tableA as below
> create table tablea (ca smallint not null, cb smallint not null)
> and I want to change to int not null for both co

Alter statement

Hi All,
Can I use one alter statement to change 2 columns' datatype.
Or Is there any command that I can use to change 2 columns' datatype.
For example : I have the table tableA as below
create table tablea (ca smallint not null, cb smallint not null)
and I want to change to int not null for both coHi Jodie,
You only get one ALTER COLUMN statement per ALTER TABLE statement:
ALTER TABLE tablea ALTER COLUMN ca int NOT NULL
ALTER TABLE tablea ALTER COLUMN cb int NOT NULL
Books-Online ALTER TABLE: http://msdn2.microsoft.com/en-us/library/ms190273.aspx
Thanks,
Ken
On Feb 16, 6:49 pm, Jodie <J...@.discussions.microsoft.com> wrote:
> Hi All,
> Can I use one alter statement to change 2 columns' datatype.
> Or Is there any command that I can use to change 2 columns' datatype.
> For example : I have the table tableA as below
> create table tablea (ca smallint not null, cb smallint not null)
> and I want to change to int not null for both co

Alter of text type field

Can I change the datatype for a particular field which was previously set to text?If possible then how?Secondly can I change the datatype of a filed of int type to identity after inserting data?Can I remove identity property from a field?USE Northwind
GO

CREATE TABLE myTable98 (Col1 int, Col2 text)
GO

INSERT INTO myTable98 (Col1, Col2)
SELECT 1, REPLICATE('X',8001) UNION ALL
SELECT 2, 'Hi! How the hell are you' UNION ALL
SELECT 3, 'X'
GO

ALTER TABLE myTable98 ALTER Column Col2 varchar(8000)
GO
-- No Good
ALTER TABLE myTable98 ADD Col3 varchar(8000)
GO

UPDATE myTable98 SET Col3 = Col2

SELECT Col1, LEN(Col3), Col3 FROM myTable98

ALTER TABLE myTable98 DROP Column Col2
GO

SELECT * FROM myTable98
GO

DROP TABLE myTable98
GO

Look up ALTER in Books Online for more....

alter column statement doesn't work, trying to change datatype

I'm trying to do something like this:
ALTER TABLE [tablename] ALTER COLUMN [fieldID] int;
I've tried several versions of this, and the data type is never changed from
numeric. I have removed all indexes on the table and it still doesn't work,
no error messages or anything. I am trying to do this with script because I
have to change like 150 databases and it's part of a much bigger script that
I have to do the same thing on other tables and columns. Any ideas?
thanks,
CoryPerhaps you are altering some other table. Have you checked the owner of the
table? Or, better yet,
owner-qualify the ALTER statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:Ob$zDhUNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> I'm trying to do something like this:
> ALTER TABLE [tablename] ALTER COLUMN [fieldID] int;
> I've tried several versions of this, and the data type is never changed fr
om numeric. I have
> removed all indexes on the table and it still doesn't work, no error messa
ges or anything. I am
> trying to do this with script because I have to change like 150 databases
and it's part of a much
> bigger script that I have to do the same thing on other tables and columns
. Any ideas?
>
> thanks,
> Cory
>
>|||No error messages -- are you sure it's not working? What do you see when
you run sp_help 'tablename' ?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:Ob$zDhUNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> I'm trying to do something like this:
> ALTER TABLE [tablename] ALTER COLUMN [fieldID] int;
> I've tried several versions of this, and the data type is never changed
from
> numeric. I have removed all indexes on the table and it still doesn't
work,
> no error messages or anything. I am trying to do this with script because
I
> have to change like 150 databases and it's part of a much bigger script
that
> I have to do the same thing on other tables and columns. Any ideas?
>
> thanks,
> Cory
>
>|||Wow, you're right, running sp_help does show that it was converted from
numeric to int. I have been looking at it through Enterprise Manager. I
can run the script then look at the design view for the table and it is
unchanged even after closing everything out, refreshing everything, and
trying again. I am becoming more and more agitated with Enterprise Manager
by the day.
thank you,
Cory
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23j3gApUNFHA.2544@.TK2MSFTNGP10.phx.gbl...
> No error messages -- are you sure it's not working? What do you see when
> you run sp_help 'tablename' ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:Ob$zDhUNFHA.3192@.TK2MSFTNGP10.phx.gbl...
> from
> work,
> I
> that
>|||> trying again. I am becoming more and more agitated with Enterprise
Manager
> by the day.
And you haven't even read http://www.aspfaq.com/2455 yet...

Alter Column Question

Hello,
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
David Portas

Alter Column Question

Hello,
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
--
David Portas

Alter Column datatype with Default constraint

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallint Not Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2) Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thxyes thats right,

the constraint has a dependency on the column and hence the data type of the column.

If you change the data type then you change the column and then this affects the constraint which SQL Server will not allow.

drop the constriant, then do what you need to do to the column

Cheers

Alter Column datatype in SMO

I m trying to alter some columns' datatype in the existing database.

it seems in SMO, some datatype like Text, throw exceptions "Cos it's Text".

so how do I code it in SMO for this problem?

I read there are some TSQL solutions to create a temp table for this.

but can SMO has a way to impletment it?

best regards

Hi,

from which type to which type to you want to switch ? The common approach would be:

Server s = newServer(".");

Table t = s.Databases["Northwind"].Tables["SomeTable"];

t.Columns["ColA"].DataType = DataType.Int;

t.Alter();

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

the problem I have is to alter Text Field to NText.

the exception is alter column x failed cos it's Text field.

all other datatypes seem straightforward.

if you have SMO solutions for this issue, I will be really appreciated.

thanks for the reply

|||

Did you ever find a solution for this? I ran into the same problem yesterday. I can convert nearly all datatypes, except text <> ntext.

I could understand it, if I was trying to convert from ntext to text, but thats not the scenario - it is text to ntext.

Alter Column datatype in SMO

I m trying to alter some columns' datatype in the existing database.

it seems in SMO, some datatype like Text, throw exceptions "Cos it's Text".

so how do I code it in SMO for this problem?

I read there are some TSQL solutions to create a temp table for this.

but can SMO has a way to impletment it?

best regards

Hi,

from which type to which type to you want to switch ? The common approach would be:

Server s = new Server(".");

Table t = s.Databases["Northwind"].Tables["SomeTable"];

t.Columns["ColA"].DataType = DataType.Int;

t.Alter();

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

the problem I have is to alter Text Field to NText.

the exception is alter column x failed cos it's Text field.

all other datatypes seem straightforward.

if you have SMO solutions for this issue, I will be really appreciated.

thanks for the reply

|||

Did you ever find a solution for this? I ran into the same problem yesterday. I can convert nearly all datatypes, except text <> ntext.

I could understand it, if I was trying to convert from ntext to text, but thats not the scenario - it is text to ntext.

Alter column datatype and default value in a table

i have a table which has 3 columns, one of three is set to default value 0. Now i have to change data type of that particular column and its default value using sql query. am working with sql server 2005. am not getting any prob when i exec ALTER TABLE TABLE_NAME ADD COLUMN [COLUMN_NAME] DATATYPE SIZE CONSTRAINT [CONSTRAINT_NAME] VALUE. but getting probs when exec
ALTER TABLE TABLE_NAME ALTER COLUMN_NAME DATATYPE SIZE [CONSTRAINT NAME] VALUE. Help me, thanx in advance

Quote:

Originally Posted by vijaialphonse

i have a table which has 3 columns, one of three is set to default value 0. Now i have to change data type of that particular column and its default value using sql query. am working with sql server 2005. am not getting any prob when i exec ALTER TABLE TABLE_NAME ADD COLUMN [COLUMN_NAME] DATATYPE SIZE CONSTRAINT [CONSTRAINT_NAME] VALUE. but getting probs when exec
ALTER TABLE TABLE_NAME ALTER COLUMN_NAME DATATYPE SIZE [CONSTRAINT NAME] VALUE. Help me, thanx in advance

Are you sure your columns meet all the criteria described in the ALTER COLUMN notes at http://msdn2.microsoft.com/en-us/library/ms190273.aspx ?
Also not that if you modify the type and/or constraints of a column the data already in the column must meet the new settings.

Alter Add - Before Text Datatype

I am constantly updating tables in my database with new fields, a lot of tables have a field with text datatype as the last field in the table.

It's very time consuming to run a script that renames the table, creates a new table with the new field before the text field, and insert into new table using select from renamed table. (SQL BELOW)

execute sp_rename CUSTDEF, CUSTDEF_1030A
GO

CREATE TABLE [dbo].[CUSTDEF] (
[CustDef1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDefNEW] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE INDEX [CUSTDEF_ONE] ON [dbo].[CUSTDEF]([CUSTDEF1], [CUSTDEF2]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO CUSTDEF (CustDef1, CustDef2, CustDef3, CustDef4, CustDef5)
SELECT CustDef1, CustDef2, CustDef3, CustDef4, CustDef5
FROM CUSTDEF_1030A
GO

What I would like to do is to be able to have sql where I can use an ALTER ADD to add in CustDefNEW before the text field. Is there any way that I can do this, and save time more time than doing an insert/select against 50,000 records.

Thanks alot!The order of columns in a database has no bearing on the perforance. Is your background DB2? It used to be that way for varchars..

And why do you have text columns? How big is the data?

Bigger than 8000 bytes?

And no, ALter Add does manage the order of the columns (at least as far as I understand).

You can do it in EM...I think it'll do all that work for you behind the scenes...

I'm just not too keen about doing work there...see some weird things...

Good Luck

Another idea might be to use a view which looks like what you want...|||Brett,

I think the order can make a difference if you have, say, a long varchar field before the values on which you are searching. The server would have to determine the length of the data for the varchar in each row in order to calculate the offset of any data after it. If you know something that contradicts this, let me know.

In any case, MHawkins19, the TEXT datatype is not even stored in your rowset. All that is stored is a fixed length pointer to the location where the TEXT data is stored. Therefore, it make little or no difference what order your columns are in.

blindman

Allocations of LOB datatypes

All
We have a very large table (1M+ rows) in which we are
storing large xmls (datalength(column) of 10K) in a text
datatype field.
We are using up space at a much greater rate than
originally planned - and will stop saving the the xml
for certain events. There are about 25 events during the
lifetime of an order.
I am trying to understand how we can get back the space
allocated to this object if we decided to set the column = null. It does not seem to change the information returned
by sp_spaceused even afer using the updateusage flag.
Must I truncate and reload this table to reclaim the space.
(Doing a select into of the table to a new table does seem
to reclaim the space)
Thanks
LBHi Len
Take a look at DBCC CLEANTABLE and see if that helps.
It's documented in Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Len Bearse" <anonymous@.discussions.microsoft.com> wrote in message
news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> All
> We have a very large table (1M+ rows) in which we are
> storing large xmls (datalength(column) of 10K) in a text
> datatype field.
> We are using up space at a much greater rate than
> originally planned - and will stop saving the the xml
> for certain events. There are about 25 events during the
> lifetime of an order.
> I am trying to understand how we can get back the space
> allocated to this object if we decided to set the column => null. It does not seem to change the information returned
> by sp_spaceused even afer using the updateusage flag.
> Must I truncate and reload this table to reclaim the space.
> (Doing a select into of the table to a new table does seem
> to reclaim the space)
> Thanks
> LB
>|||It did not seem to help - please note in my tests
I am just setting the column = null.
>--Original Message--
>Hi Len
>Take a look at DBCC CLEANTABLE and see if that helps.
>It's documented in Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
in message
>news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> All
>> We have a very large table (1M+ rows) in which we are
>> storing large xmls (datalength(column) of 10K) in a text
>> datatype field.
>> We are using up space at a much greater rate than
>> originally planned - and will stop saving the the xml
>> for certain events. There are about 25 events during the
>> lifetime of an order.
>> I am trying to understand how we can get back the space
>> allocated to this object if we decided to set the
column =>> null. It does not seem to change the information
returned
>> by sp_spaceused even afer using the updateusage flag.
>> Must I truncate and reload this table to reclaim the
space.
>> (Doing a select into of the table to a new table does
seem
>> to reclaim the space)
>> Thanks
>> LB
>
>.
>|||Len
Ok, I see. You're not dropping the column, just setting it to null. But,
hey, that's a thought that might be a bit more efficient that a complete
recreate. Drop the column, run dbcc cleantable and then readd the column.
But of course, YMMV and you should test it well first.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:036801c3a577$66680450$a401280a@.phx.gbl...
> It did not seem to help - please note in my tests
> I am just setting the column = null.
>
> >--Original Message--
> >Hi Len
> >
> >Take a look at DBCC CLEANTABLE and see if that helps.
> >It's documented in Books Online.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> >> All
> >> We have a very large table (1M+ rows) in which we are
> >> storing large xmls (datalength(column) of 10K) in a text
> >> datatype field.
> >>
> >> We are using up space at a much greater rate than
> >> originally planned - and will stop saving the the xml
> >> for certain events. There are about 25 events during the
> >> lifetime of an order.
> >>
> >> I am trying to understand how we can get back the space
> >> allocated to this object if we decided to set the
> column => >> null. It does not seem to change the information
> returned
> >> by sp_spaceused even afer using the updateusage flag.
> >>
> >> Must I truncate and reload this table to reclaim the
> space.
> >> (Doing a select into of the table to a new table does
> seem
> >> to reclaim the space)
> >>
> >> Thanks
> >>
> >> LB
> >>
> >
> >
> >.
> >|||I guess the real underlying question is how is space
allocated to the structures the contain the lob data.
I had thought that the b-tree structure would allocate
data in a manner similar to other SQL server objects.
That as pages and extents were deallocated would be freed
and listed as available. It doesn't seem to be working
that way. In testing we don't seem to be freeing the data
to the same degree we are using it up.
I think what I am going to do one of the following
1) No check constraints - bcp table out - truncate table -
bcp table in - re-enable constraints
2) no check constraints - rename table to old_tbl - create
new_tab - insert into new empty table - re-enable
constraints
Len
>--Original Message--
>Len
>Ok, I see. You're not dropping the column, just setting
it to null. But,
>hey, that's a thought that might be a bit more efficient
that a complete
>recreate. Drop the column, run dbcc cleantable and then
readd the column.
>But of course, YMMV and you should test it well first.
>
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:036801c3a577$66680450$a401280a@.phx.gbl...
>> It did not seem to help - please note in my tests
>> I am just setting the column = null.
>>
>> >--Original Message--
>> >Hi Len
>> >
>> >Take a look at DBCC CLEANTABLE and see if that helps.
>> >It's documented in Books Online.
>> >
>> >--
>> >HTH
>> >--
>> >Kalen Delaney
>> >SQL Server MVP
>> >www.SolidQualityLearning.com
>> >
>> >
>> >"Len Bearse" <anonymous@.discussions.microsoft.com>
wrote
>> in message
>> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> >> All
>> >> We have a very large table (1M+ rows) in which we
are
>> >> storing large xmls (datalength(column) of 10K) in a
text
>> >> datatype field.
>> >>
>> >> We are using up space at a much greater rate than
>> >> originally planned - and will stop saving the the xml
>> >> for certain events. There are about 25 events during
the
>> >> lifetime of an order.
>> >>
>> >> I am trying to understand how we can get back the
space
>> >> allocated to this object if we decided to set the
>> column =>> >> null. It does not seem to change the information
>> returned
>> >> by sp_spaceused even afer using the updateusage flag.
>> >>
>> >> Must I truncate and reload this table to reclaim the
>> space.
>> >> (Doing a select into of the table to a new table does
>> seem
>> >> to reclaim the space)
>> >>
>> >> Thanks
>> >>
>> >> LB
>> >>
>> >
>> >
>> >.
>> >
>
>.
>