Alter table - Nullability of column [Remove requirement to specify datatype] - by DB007

Status : 


Sign in
to vote
ID 354991 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 7/4/2008 2:29:14 AM
Access Restriction Public
Primary Feedback Item 124781


Hi All,

If I want to change the nullability of a column from null -> not null, I have to specify the columns existing data-type.  This should not be necessary in future..

use [AdventureWorks]
create table dbo.CustomersTest
customer_id int, 
report_id int, 
rundate datetime null 

-- How we have to currently specify the nullability change:
alter table customerstest
alter column rundate datetime not null

-- If run as the following, we get an error back:
alter table customerstest
alter column rundate  not null
--Error: Incorrect syntax near the keyword 'not'
Sign in to post a comment.
Posted by SAinCA on 11/27/2012 at 4:14 PM
It is VERY odd that one can vote on this "duplicate" issue, but Microsoft has barred ALL voting on the "Primary Feedback Item". Do you not want our input any more, Microsoft? Or does this (drum roll please) actually mean you are working on implementing the "Primary" request? Do say "Yes!" to this...
Posted by Microsoft on 3/18/2011 at 6:17 PM

Thank you for submitting this suggestion. I have resolved yours as duplicate of one below:

Although that suggestion item is about ALTER TABLE for changing column order, I have added your request also as part of that. Moreover ANSI SQL:2008 added support to set or drop NOT NULL attribute using ALTER COLUMN, set or drop IDENTITY property and other enhancements. We will take a look at all of these together as a single improvement.

Umachandar, SQL Programmability Team
Posted by DB007 on 3/18/2011 at 4:29 AM
Please explain why this makes it to wont fix?
Posted by DB007 on 7/8/2008 at 9:10 AM
Had a look at this, gets even more interesting..

IF the column whose nullability status changes - is part of an index(s):
Need to drop all associated indexes with the column
change nullability of column status
Rebuild indexes onto the column.

It would be nice to not have to actually drop the indexes also, have the option of rebuild any indexes in the background automatically.

Posted by Microsoft on 7/7/2008 at 12:07 PM
Thanks for your suggestion. We will consider your request for a future version of SQL Server. The current ALTER TABLE ALTER COLUMN syntax conforms to the ANSI SQL specification. Since ANSI SQL doesn't support adding or removing NULLability via ALTER COLUMN we will have to come up with our own syntax.

Umachandar, SQL Programmability Team