Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple column
s
that I wanted to change data types for, this is the only way I have been abl
e
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!well! u dont have that flexibility
u can add multiple columns using ALTER
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:

> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebui
ld
> the whole table when its changing the data type, so if I had multiple colu
mns
> that I wanted to change data types for, this is the only way I have been a
ble
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all m
y
> columns with one rebuild? Thanks guys!
>|||Okay, how about doing it in Enterprise Manager's Design Table section? I ca
n
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
[vbcol=seagreen]
> well! u dont have that flexibility
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Shishir Viriyala" wrote:
>|||it rebuilds for each column.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Check and see :-). Press the "save change script" button and you will see th
e SQL that EM is about
to execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in mess
age
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:

> Check and see :-). Press the "save change script" button and you will see
the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in me
ssage
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
>|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:

>Okay, how about doing it in Enterprise Manager's Design Table section? I c
an
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or doe
s
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)