Altering Table Column: Oracle vs MS SQL

Today I needed to do some coding in MS SQL and discovered some differences between Oracle and MS SQL worth mentioning.

As you know, Oracle allows altering more than one column in a single command. For ex:

ALTER TABLE scott.emp MODIFY (
   hiredate NOT NULL,
   deptno NUMBER(4) NOT NULL
);

In TSQL, however, you will have to execute two commands:

ALTER TABLE emp ALTER COLUMN hiredate DATE NOT NULL;
ALTER TABLE emp ALTER COLUMN deptno INT NOT NULL;

A couple of more differences:

  • TSQL uses ALTER COLUMN, Oracle SQL – MODIFY
  • TSQL, unlike Oracle SQL, cannot alter column null-ability without knowing its data type

I hope this short post will help Oracle developers to be productive with MS SQL if the opportunity presents.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Further Reading:

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements