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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s