oracle SYS_EXTRACT_UTC(SYSTIMESTAMP), mssql GETUTCDATE(), mysql CURRENT_TIMESTAMP

inORACLE

DROP TABLE "CMS_TEMP";

CREATE TABLE "CMS_TEMP" (  
"ID" VARCHAR2(36) DEFAULT SYS_GUID() NOT NULL,
"NAME" NVARCHAR2(150),
"RCDATE" TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
);

insert into "BOROO"."CMS_TEMP" (NAME)
values ('new name 1');

select TO_CHAR(RCDATE, 'YYYY-MM-DD HH24:MI:SS.FF') from CMS_TEMP;


inMSSQL


DROP TABLE [dbo].[table1]
GO
/****** Object:  Table [dbo].[table1]    Script Date: 3/21/2017 5:50:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_table1_ID]  DEFAULT (newid()),
    [Name] [nvarchar](50) NOT NULL,
    [rcdate] datetime NOT NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO
insert into table1(name)
values ('name 1');
GO
select rcdate from table1;


inMySQL

CREATE TABLE `table1` (
  `id` char(36) DEFAULT NULL,
  `name` varchar(250) NOT NULL,
  `rcdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

oracle sys_guid(), mssql newid(), mysql trigger all for default uniqueidentifier ID

inORACLE

DROP TABLE "CMS_TEMP";

CREATE TABLE "CMS_TEMP" (   
"ID" VARCHAR2(36) NOT NULL,
"NAME" NVARCHAR2(150),
"RCTIME" TIMESTAMP (6) DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP) NOT NULL
);

create or replace FUNCTION NEWID RETURN VARCHAR2 IS guid VARCHAR2(36);
BEGIN
    SELECT SYS_GUID() INTO guid FROM DUAL;
guid := regexp_replace(rawtohex(sys_guid())
       , '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
       , '\1-\2-\3-\4-\5');
--OR
guid := SUBSTR(guid,  1, 8) ||
        '-' || SUBSTR(guid,  9, 4) ||
        '-' || SUBSTR(guid, 13, 4) ||
        '-' || SUBSTR(guid, 17, 4) ||
        '-' || SUBSTR(guid, 21);
    RETURN guid;
END NEWID;

create or replace TRIGGER SetGUIDforCMS_TEMP BEFORE INSERT ON CMS_TEMP
FOR EACH ROW
BEGIN
    :new.ID := NEWID();
END;

insert into "CMS_TEMP" (NAME)
values ('new name 1');

select * from cms_temp;

DECLARE
  v_Return VARCHAR2(36);
BEGIN
  v_Return := NEWID;
  DBMS_OUTPUT.PUT_LINE(v_Return);
END;

select NEWID from dual;

inMSSQL

Data Type: uniqueidentifier
Default Value or Binding: (newid())

CREATE TABLE [dbo].[table1](
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_table1_ID]  DEFAULT (newid()),
    [Name] [nvarchar](50) NOT NULL,
    [rcdate] datetime NOT NULL DEFAULT GETUTCDATE()
) ON [PRIMARY]
GO

inMYSQL

CREATE TRIGGER `before_insert_table1` BEFORE INSERT ON `table1`
FOR EACH ROW BEGIN
    SET new.id = UPPER(uuid());
END

maybe backup entity framework migration history manual

SELECT * into [backup_db].[dbo].[migrations]
  FROM original_db.dbo.__MigrationHistory

after error

  insert into original.dbo.__MigrationHistory
  select * from [backup_db].[dbo].[migrations]

mssql 2012 feature is Sequence objects

create sequence MySeq as int
start with 1  -- Start with value 1
increment by 1-- Increment with value 1
minvalue 0 -- Minimum value to start is zero
maxvalue 100 -- Maximum it can go to 100
no cycle -- Do not go above 100
cache 50 -- Increment 50 values in memory rather than incrementing from IO

SELECT NEXT VALUE FOR dbo.MySeq AS seq_no;

SELECT current_value FROM sys.sequences WHERE name = 'MySeq';

SQL SERVER – NULLIF() vs ISNULL()

SQL SERVER – NULLIF() vs ISNULL()

NULLIF() Returns a null value if the two specified expressions are equal. If the Two expressions are not equal then it will return the first expression's value. Whether the returned value is NULL or NOT NULL, NULLIF() will return the same data type as the first expression 

Syntax  : NULLIF(expression1,expression2)

Example 1 :

Select NULLIF(100,50*2)  'NULLIF Value'

OutPut :

Example 2 :

Select NULLIF(2*2,2*7)  'NULLIF Value'

OutPut :

Example 3 :

Select NULLIF(20-2,19)  'NULLIF Value'

OutPut :

ISNULL() Replaces the NULL value with the specified expression value.

Syntax : ISNULL(check expression,replacement value)

Example 1 :

Select ISNULL(null,12) 'ISNULL VALUE'

OutPut :

Example 2:

Select ISNULL(marks,0)  'Marks'  from Student

OutPut :

pagenite by rownumber, different of subquery vs with clause in mssql

This is one way (SQL2000)

SELECT * FROM
(
    SELECT TOP (@pageSize) * FROM
    (
        SELECT TOP (@pageNumber * @pageSize) *
        FROM tableName 
        ORDER BY columnName ASC
    ) AS t1 
    ORDER BY columnName DESC
) AS t2 
ORDER BY columnName ASC

and this is another way (SQL 2005)

;WITH results AS (
    SELECT 
        rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
        , *
    FROM tableName 
) 
SELECT * 
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

and this is another way SQL2012

SELECT * FROM Table_1
ORDER BY id
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY

example

SELECT * from (
SELECT *, ROW_NUMBER() OVER(ORDER BY id asc) as rownum FROM [test].[dbo].[Table_1]
) as a
where a.rownum > 1 and a.rownum < 4
GO
with b as (
SELECT *, ROW_NUMBER() OVER(ORDER BY (select 1)) as rownum FROM [test].[dbo].[Table_1]
)
select top 10 * from b where b.rownum > 1
GO