Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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

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..

Demo:
use [AdventureWorks]
go
create table dbo.CustomersTest
(
customer_id int,
report_id int,
rundate datetime null
)
go

-- 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'

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 8

    <=-=Jul 7 2008 12:07PM=-=>

    Hi,
    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

    <=-=Jul 8 2008 9:10AM=-=>

    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.

    <=-=Mar 18 2011 4:29AM=-=>

    Please explain why this makes it to wont fix?

    <=-=Mar 18 2011 6:17PM=-=>

    Hello,

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

    http://connect.microsoft.com/SQLServer/feedback/details/124781

    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

    <=-=Nov 27 2012 4:14PM=-=>

    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…

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base