sql server programming: [quoted text, click to view]

I would expect the INSERT to be the faster of these 2 methods. However, for
a large table, SELECT INTO is the fastest method if your database is in the
SIMPLE or BULK_LOGGED recovery model.

You can coerce new columns to NOT NULL using ISNULL like the example below.
Be sure to test this method to ensure the resultant schema is as expected.
Constraints may be added afterward. You can also specify the NOCHECK option
when adding foreign key and check constraints if you are certain existing
data are valid.

CREATE TABLE OLD
(
a1 char(5) NOT NULL,
a2 char(5) NOT NULL,
a3 char(5) NOT NULL
)
GO

INSERT INTO OLD
VALUES('11111', '22222', '33333')
GO

SELECT
a1,
a2,
a3,
ISNULL(CAST('44444' AS char(5)), '') AS c1,
ISNULL(CAST('55555' AS char(5)), '') AS c2,
ISNULL(CAST('66666' AS char(5)), '') AS c3
INTO NEW
FROM OLD

DROP TABLE OLD
EXEC sp_rename 'NEW', 'OLD'

ALTER TABLE OLD
ADD CONSTRAINT DF_c1 DEFAULT '44444' FOR c1
ALTER TABLE OLD
ADD CONSTRAINT DF_c2 DEFAULT '55555' FOR c2
ALTER TABLE OLD
ADD CONSTRAINT DF_c3 DEFAULT '66666' FOR c3

ALTER TABLE OLD WITH NOCHECK
ADD CONSTRAINT FK_OLD_SomeOtherTable
FOREIGN KEY (a1) REFERENCES SomeOtherTable(a1)
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Q1.

I have to create a table with new schema which has 10 billion rows.
Which is faster?
method 1:
select ...... into

alter table NEW alter column c1 char(5) NOT NULL
alter table NEW alter column c2 char(5) NOT NULL
alter table NEW alter column c3 char(5) NOT NULL
...
alter table NEW alter column c5 char(5) NOT NULL

or
method 2:
create table NEW
(
c1 char(5) .... NOT NULL
, c2 char(5) .... NOT NULL
...
, c5 char(5) .... NOT NULL
...
, c10 varchar(5) NULL default ''
)
insert NEW select ...from OLD


Q2.
is there any way that i put NOT NULL / DEFAULT / FK and etc.
while i am doing "SELECT ... INTO"?

thanks in advance,
William Chung