Alter table alter column text 
Author Message
 Alter table alter column text
create table FRtext
(textf text)

Alter table FRtext
Alter column textf varchar(8000)

Error
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'textf' because it is 'text'.

Now how can i do so and keep my data intact assuming i dont have more than
8000 chars

Thanks



Sat, 14 Aug 2004 04:27:20 GMT
 Alter table alter column text

FR, did you try adding a new column of varchar(8000) type, then update that
column to the value of your text column. Then drop the text column and
rename the new column as the text column?
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


create table FRtext
(textf text)

Alter table FRtext
Alter column textf varchar(8000)

Error
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'textf' because it is 'text'.

Now how can i do so and keep my data intact assuming i dont have more than
8000 chars

Thanks



Sat, 14 Aug 2004 04:38:22 GMT
 Alter table alter column text
floyd,

you cannot alter text/ntext/image column. this is well documented in bol.
remember that text/ntext/image is stored as blob in the database.

in sql2k, there is an option to stored 'text in row' - see bol for more
info. however, it still holds true that you cannot use 'alter column' for
blob.

--
-oj
http://rac4sql.home.attbi.com


Quote:
> create table FRtext
> (textf text)

> Alter table FRtext
> Alter column textf varchar(8000)

> Error
> Server: Msg 4928, Level 16, State 1, Line 1
> Cannot alter column 'textf' because it is 'text'.

> Now how can i do so and keep my data intact assuming i dont have more than
> 8000 chars

> Thanks



Sat, 14 Aug 2004 04:36:27 GMT
 Alter table alter column text
If i use 'text in row' , are there any advantages to doing so rather than
having a varchar datatype set ?

What i want to get to is why use text in row for text datatype if we can
specify a varchar

Thanks


Quote:
> floyd,

> you cannot alter text/ntext/image column. this is well documented in bol.
> remember that text/ntext/image is stored as blob in the database.

> in sql2k, there is an option to stored 'text in row' - see bol for more
> info. however, it still holds true that you cannot use 'alter column' for
> blob.

> --
> -oj
> http://rac4sql.home.attbi.com



> > create table FRtext
> > (textf text)

> > Alter table FRtext
> > Alter column textf varchar(8000)

> > Error
> > Server: Msg 4928, Level 16, State 1, Line 1
> > Cannot alter column 'textf' because it is 'text'.

> > Now how can i do so and keep my data intact assuming i dont have more
than
> > 8000 chars

> > Thanks



Sat, 14 Aug 2004 05:10:02 GMT
 Alter table alter column text
with text in row option, if the text is stored within the data page if it,
indeed, fits within the page. this allows textdata access as fast as regular
data (char/binary). if the text is larger or doesn't fit within the data
page, a pointer is stored there.

the bottom line, with text in row option you benefit from faster access to
your blob. but, if you do not require blob, please do stay with char/binary
as blob is notorious for db fragmentation (eventhough, it's better in sql2k,
it's still bad).

--
-oj
http://rac4sql.home.attbi.com


Quote:
> If i use 'text in row' , are there any advantages to doing so rather than
> having a varchar datatype set ?

> What i want to get to is why use text in row for text datatype if we can
> specify a varchar

> Thanks



Sat, 14 Aug 2004 05:16:07 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. multiple ALTER COLUMNs in one ALTER TABLE statement?

2. Help with Alter Table Alter Column

3. Alter Table Alter Column Syntax

4. Alter table alter column

5. alter table alter column

6. Alter table alter column syntax

7. Alter table Alter column problem.

8. ALTER TABLE ... ALTER COLUMN IDENTITY

9. ALTER TABLE ALTER COLUMN

10. ALTER TABLE ALTER COLUMN to set Default

11. syntax for ALTER TABLE ALTER COLUMN DEFAULT

12. Errors With ALTER TABLE/ALTER COLUMN


 
Powered by phpBB® Forum Software