Oracle – MSSQL Convert Tips (1)

These days I participate a project  to convert sql statements between Oracle and MSSQL and learn a lot.

1.Auto increament column Issue
sql server : when create table define this column property  identity(0,1)
oracle       : must use a sequence and a trigger as the following:

CREATE SEQUENCE SEQUENCE_NAME
  START WITH 0
  NOMAXVALUE
  MINVALUE 0
  NOCYCLE
  NOCACHE
  NOORDER;

CREATE OR REPLACE TRIGGER TRIGGER_NAME
BEFORE INSERT
ON TABLENAME
FOR EACH ROW
BEGIN
  SELECT SEQUENCE_NAME.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;

2. Trigger Auto set System Date
when insert an entry, set a certain column to sysdate. 
This makes only one clock ,and reduce one column operation in programming .
both use trigger like the following:

sql server case:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[TRIGGER_NAME]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [TRIGGER_NAME]
GO
CREATE TRIGGER [TRIGGER_NAME] ON [TABLE_NAME]
FOR INSERT
AS
    DECLARE @id int;
    SELECT @id = inserted.id FROM inserted;
    UPDATE TABLE_NAME SET COLUMN_NAME = getdate() WHERE id = @id;
GO

Oracle case :
CREATE OR REPLACE TRIGGER TRIGGER_NAME
BEFORE INSERT
ON TABLE_NAME
FOR EACH ROW
BEGIN
  SELECT sysdate
  INTO :NEW.COLUMN_NAME
  FROM DUAL;
END;

3. Sytax difference and similiar
modify column ,alter column
Integrated constraint like Primary Key, Foreign Key, Check, Unique ,Index
    
4. Oracle connect by, SQL Server should use User Defined Function to do so.
 
5 .Misc
sqlserver: IsNull(a,b)
oracle: nvl(a,b)