SQL SERVER 视图 Garin Zhang

视图
SET　NOCOUNT ON;
SET Northwind;
GO
IF OBJECT_ID('dbo.ViewName') IS NOT NULL
    DROP VIEW dbo.ViewName;
GO
CREATE VIEW dbo.Viewname
AS
SELECT * from customer AS C
WHERE EXISTS
    (SELECT * from dbo.Orders AS O
     WHERE O.CustomerID = C.Customerid);
GO
**************************我是分割线**************************
IF OBJECT_ID('abo.VSign') IS NOT NULL
    DROP VIEW ado.VSign;
GO
    CREATE VIEW ado.VSign
AS
    SELECT mnth, qty, SIGN((S1.qty-(SELECT TOP 1 qty from ado.Sales AS S2
                                                         WHERE S2.mnth < S1.mnth ORDER BY S2.mnth DESC))) AS sgn
    from ado.Sales AS S1
GO
**************************
我是分割线**************************
刷新视图
EXEC sp_refreshview 'dbo.V1';
SELECT NEXEC sp_refreshview QUOTENAME(VIEW_NAME, '');
SELECT NEXEC sp_refreshview QUOTENAME(VIEW_NAME, '"') AS cmd
from(SELECT QUOTENAME(TABLE_SCHEMA) N, QUOTENAME(TABLE_NAME) AS VIEW_NAME
          from INFORMATION_SCHEMA, VIEWS) AS V
WHERE OBJECT PROPERTY(OBJECT_ID(VIEW_NAME), 'IsSchemaBound') = 0;
视图中的ORDER BY（必须与TOP和FORXML一起使用）
注：不要依赖于视图的ORDER BY（2000可用，2005不可用）
EXISTS中的*可以用，只起统计作用。
SQLSERVER2005支持CTE，使用模块化方法开发。
**************************
我是分割线**************************
ALTER VIEW dbo.Vtrends
AS
WITH CsalesRN AS
(SELECT mnth, qty, ROW_NUMBER() OVER(ORDER BY mnth) AS RN
 from dbo.sales),
CSign AS
(SELECT Cur.mnth, Cur.qty, SIGN(Cur.qty - Prv.qty) AS Sign
 from CsalesRN AS Cur
 LEFT OU