[DB]How to make CREATE OR REPLACE VIEW work in SQL Server?

在Oracle的環境下，如果要修改View的內容可以使用CREATE OR REPLACE VIEW來處理

CREATE OR REPLACE VIEW data_VVVV AS 
SELECT 
    VCV.xxxx,
        VCV.yyyy AS yyyy,
        VCV.zzzz AS zzzz
FROM 
TABLE_A
;

但是MS SQL下無法這樣做
要處理以下的問題可使用
1.

IF OBJECT_ID('dbo.data_VVVV') IS NULL
BEGIN
    CREATE VIEW dbo.data_VVVV
    AS
    SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
END
ELSE
    ALTER VIEW dbo.data_VVVV
    AS
    SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
BEGIN
END

or
2.

IF OBJECT_ID('dbo.test_abc_def', 'V') IS NOT NULL
    DROP VIEW dbo.test_abc_def
GO

CREATE VIEW dbo.test_abc_def AS
SELECT 
    VCV.xxxx
    ,VCV.yyyy AS yyyy
    ,VCV.zzzz AS zzzz
FROM TABLE_A

如果要判斷這個View是否存在
還有別的用法，如：

IF (NOT EXISTS (SELECT 1 FROM sys.views WHERE name = 'data_VVV'))

if not exists (select * from sysobjects where name='data_VVV' and xtype='V')

剩下就看你要Drop重建或是Alter View

[DB]Oracle與MS SQL自動編號的比較

MS SQL在自動編號上比較方便,只要將欄位設定為IDENTITY(1,1)就可以了
但在Oracle就要先增加一個Sequence,然後利用這個Sequence來產生編號

MS SQL
--建立資料表
CREATE TABLE [Table]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL
) ON [PRIMARY]


--新增一筆資料
INSERT INTO [TABLE] VALUES('ABC')


ORACLE
--建立資料表
CREATE TABLE "TEST"
(
"ID" NUMBER,
"NAME" NVARCHAR2(50)
) ;

--建立Sequence
CREATE SEQUENCE "SEQ_TEST"
MINVALUE 1
MAXVALUE 999999999
INCREMENT BY 1
START WITH 1 ;

--新增一筆資料並利用Sequence自動產生編號
INSERT INTO TEST VALUES(SEQ_TEST.NEXTVAL,'ABC')

[DB]取得DB裡所有Table Name

Oracle
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'

MS SQL
SELECT * FROM INFORMATION_SCHEMA.TABLES

ACCESS
SELECT * FROM MSYSOBJECTS

[SQL]MS SQL與Oracle判斷欄位是否為NULL的方法比較,COALESCE()、ISNULL()、NVL()

COALESCE(),這個屬於ANSI標準,可以用在MS SQL或Oracle
語法：
SELECT COALESCE(FIELD,'*') FROM TABLE

Oracle專用語法
SELECT NVL(FIELD,'*') FROM TABLE

MS SQL專用語法
SELECT ISNULL(FIELD,'*') FROM TABLE
PS:ISNULL()的效能會比COALESCE()佳