adding primary key to an existing table

Hi all,

I there a way to check if the column is set to NOT NULL is equal to true, if not set to true i have to set it to true before adding my primary key or else i'll get an error message.

in my SP i have a ALTER TABLE table1 ADD PRIMARY KEY (colum1)

My question is, how can i check if the column is set to not null is false and how can i update to set it true?

Thanks.

Teo,

You can use the DMVs sys.tables and sys.columns to determine if a column is nullable.

select t.name, c.name, c.is_nullable
from sys.tables t join sys.columns c
on t.object_id = c.object_id
where t.name = 'fact'

Secondly, to change a column to NOT NULL if it is, use ALTER TABLE ... ALTER COLUMN.

ALTER TABLE dbo.fact ALTER COLUMN dim1_id INT NOT NULL

Regards,

Galex

|||

Why you want to check for NULL. without checking it just set it to not null

ALTER TABLE table1 alter column col1 int not null

ALTER TABLE table1 ADD PRIMARY KEY (col1)

it will not retun any errors even it is already set to not null

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
>> I am running an upgrade to an existing system which I have found is using
> the
>> Alter Table statement to add a Not Null with Default column to huge table
>> (100 million records). Not surprisingly it is taking rather a long time!
>> Can anyone give me any other options to perform this operation ?
>> I'm currently thinking about
>> 1) Adding the column Nullable
>> 2) Running multiple updates to the table
>> 3) Altering the column to make it Not-Nullable.
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using the
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.
I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>
|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>
|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')
|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?
|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using th
e
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.

adding new column

ALTER TABLE BFTITLE ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL ;
syntax is not working so can any one tell the right syntaxalter Table Bftitle Add [ratetype] [numeric] (10) Not Null|||Read BOL (SQL Server Books On Line) carefully. If a table already has rows in it, it isn't possible to add a NOT NULL column unless you also give the column a default value too (if you think about it, this only makes sense). I'm guessing on this, but I'd suggest trying:ALTER TABLE BFTITLE
ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0-PatP|||ALTER TABLE BFTITLE
ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0
-PatP

Close. This worked for me:

ALTER TABLE BFTITLE
ADD [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0

I just removed "COLUMN" from your statement. Funny, I did not think it would be possible to add a non-nullable column, but you're right it can be done as long as you define a default value.

Following is proof:

create table foo (bar int)
go

insert foo (bar) values (1)
go
insert foo (bar) values (2)
go
insert foo (bar) values (3)
go
insert foo (bar) values (4)
go

ALTER TABLE foo
ADD [RATETYPE] [NUMERIC] (10) NOT NULL
DEFAULT 0

select * from foo

sp_columns foo

Adding all non-null fields

I have 50 fields that I would like to sum together in an sql statement. However, some of them may be null causing the statement to fail. Can someone send me a statement that would add up only fields that are not null.

My current sql stmt is "select x1 + x2 + x3 + ... x50 from xtable"

Thanksselect ISNULL(x1,0)
+ ISNULL(x2,0)
+ ISNULL(x3,0)
+ ...
+ ISNULL(x50,0) from xtable|||Thanks a lot.