SQL查詢所有Table筆數

CREATE TABLE #TableInfo
(
TableName VarChar(max),
Cnt INT
);

EXEC sp_MSforeachtable 'INSERT #TableInfo select ''?'' as TableName, Count(*) as Cnt from ?';

UPDATE #TableInfo
SET TableName=REPLACE(REPLACE(REPLACE(TableName,'[dbo].',''),'[',''),']','')

SELECT * FROM #TableInfo Order By Cnt DESC

DROP TABLE #TableInfo

在 SQL View 建立 Index

--CREATE VIEW
CREATE VIEW V_Table1
WITH SCHEMABINDING
AS
SELECT ID, FLD1
FROM dbo.Table1

--CREATE PK Index
CREATE UNIQUE CLUSTERED INDEX PK_Table1
ON V_Table1 (ID)

--CREATE Combine/Lookup INDEX
CREATE NONCLUSTERED INDEX NCI_Table1
ON V_Table1 (FLD1)
INCLUDE (ID)

CTE迴圈年曆

DECLARE @S_DATE AS DATE
DECLARE @E_DATE AS DATE
SET @S_DATE='2014/01/01'
SET @E_DATE='2014/12/31'
;
WITH CTE_DATE AS
(
 SELECT @S_DATE AS S_DATE
 UNION ALL
 SELECT DATEADD(d,1, S_DATE) AS S_DATE
 FROM CTE_DATE
 WHERE S_DATE<@E_DATE
)
SELECT S_DATE
FROM CTE_DATE
OPTION (MAXRECURSION 0)

CTE 遞迴

WITH CTE AS ( SELECT PostTitle , Keyword , 1 AS StartPos , -- 錨點起點　　　　　　　　 CHARINDEX(',',Keyword) AS EndPos -- 錨點終點 FROM @Temp UNION ALL SELECT PostTitle , Keyword , EndPos + 1 , -- 遞迴起點 CHARINDEX(',',Keyword,EndPos + 1) -- 遞迴終點 FROM CTE WHERE EndPos > 0 -- CHARINDEX() 大於 0 表示還有逗號存在，遞迴繼續跑，等於 0 則遞迴停止 ) SELECT PostTitle , SUBSTRING ( Keyword , StartPos , ISNULL -- 第一個參數假如為 NULL 就抓取第二參數來表示終點 ( NULLIF(EndPos,0) , -- 第一參數；利用 NULLIF() 把終點為 0 的值轉換成 NULL LEN(Keyword) + 1 -- 第二參數 ) - StartPos ) AS String FROM CTE ORDER BY PostTitle , StartPos -- 對起點進行排序，能確保關鍵字呈現順序

SQL避免在Where條件使用日期函數

--索引掃描Scan: Bad
SELECT COUNT(*) From Orders WHERE YEAR(OrderDate)=1997

--索引搜尋Seek: Good
SELECT COUNT(*) FROM Orders
WHERE OrderDate >= '1997/01/01' AND OrderDate < '1998/01/01';