Jason SQL Server

[轉貼]T-SQL — Common Table Expression (CTE) 教學重點筆記

http://kkbruce.blogspot.com/2011/01/t-sql-common-table-expression-cte.html

 

重點筆記：

  • 可以把CTE當成”Temp View”
  • 如果在Store Procedure使用CTE，會造成每次執行都會re-compiler，效能低落
  • 資料集未以Table存放於Database時，適合用CTE
  • View通常用來分解大型查詢，如果只用一次，那View不是好辦法
  • 子查詢只能從所在的”陳述式”存取，如果有多個相同的子查詢，必須含重覆程式碼，難閱讀及維護
  • CTE能在同批次下，重覆使用(像View一樣)
  • CTE只能用於其後第一個陳述式(Select, Insert, Delete, Update)，此陳述式可多次取用CTE
  • CTE後面可接另一個CTE，使用逗號(,)

 

Jason SQL Server

[轉貼]關於CTE

SQL – 使用 一般資料表運算式 CTE (Common Table Expression)

早期可以透過暫存資料 表(# 或 ##)或者 table 來存放資料，現在也可以使用 一般資料表運算式 CTE (Common Table Expression) 來暫存資料。雖然 CTE 的查詢是使用 Select 查詢，但是依照 MSDN 的說明，仍有一些限制，那就是在 CTE 中不可以使用以下的子句：

1.COMPUTE 或 COMPUTE BY
2.ORDER BY (除非指定了 TOP 子句)
3.INTO
4.含有查詢提示的 OPTION 子句
5.FOR XML
6.FOR BROWSE

以下就來實作一些 CTE 的範例…

範例一：單一的 CTE 用法

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
)

Select *
From OrdersTable

範例二：兩個 CTE 作 Join

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
),
OrderDetailsTable as
(
Select OrderID as ‘訂單ID’, ProductID as ‘產品ID’, UnitPrice as ‘價格’
from dbo.[Order Details]
)

Select *
From OrdersTable A inner join OrderDetailsTable B
on A.訂單ID = B.訂單ID

範例三：CTE 遞迴 (參考：黑暗大的部落格文章)

資料清單

Code：

–如果CTE的WITH不在第一列, 前方要加上;
;WITH 公司組織結果(部門名稱, 直屬部門, 層級, 排序欄位)
AS
(
–Recursive CTE分為兩個部分, 第一部分為Anchor Member
–指不會被遞迴呼叫到的部分
SELECT 部門名稱, 直屬部門, 0, CONVERT(nvarchar(128), 部門ID)
FROM dbo.公司組織 WHERE 直屬部門=N’ROOT’

UNION ALL

–UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
–直到無任何查詢結果為止
SELECT P.部門名稱, P.直屬部門, B.層級+1,
CONVERT(nvarchar(128), B.排序欄位 + ‘-‘ + CONVERT(nvarchar(128), P.部門ID))
FROM dbo.公司組織 P, 公司組織結果 B
WHERE P.直屬部門=B.部門名稱
)
SELECT (REPLICATE(‘       ‘, 層級) + 部門名稱) as ‘部門名稱’, 層級, 排序欄位
FROM 公司組織結果
ORDER BY 排序欄位

另一個 CTE 遞迴範例 (參考：Journey to SQL Authority with Pinal Dave)

Code：

USE AdventureWorks
GO
WITH Emp_CTE AS (
–找出 ManagerID 為 NULL 的資料當作 Anchor Member
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL

UNION ALL

–之後以 Anchor Member 為依據遞迴查詢
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

 

Jason SQL Server

[轉貼]SQL建立DB LINK的方法

原文網址


--Add DB Link Server
EXEC sp_addlinkedserver
@server = 'F6', --Server Name
@srvproduct = 'MS SQL',
@datasrc = '192.168.1.1' , --Server IP
@provider = 'SQLNCLI'

--Remove DB Link Server
EXEC sp_dropserver
@server = 'F6' --Server Name

--Add Login User and Password
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'F6' , --Server Name
@useself = 'false' ,
@locallogin = NULL ,
@rmtuser = 'sa' , --User
@rmtpassword = 'sasa' --Password
--Add DB Link Server
EXEC sp_addlinkedserver
@server = 'F6', --Server Name
@srvproduct = 'MS SQL',
@datasrc = '192.168.1.1' , --Server IP
@provider = 'SQLNCLI'

--Remove DB Link Server
EXEC sp_dropserver
@server = 'F6' --Server Name

--Add Login User and Password
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'F6' , --Server Name
@useself = 'false' ,
@locallogin = NULL ,
@rmtuser = 'sa' , --User
@rmtpassword = 'sasa' --Password

--Query DB Link Server
select * from sys.servers
--Query DB Link Server
select * from sys.servers

--Query Login User
select * from sys.linked_logins
--Query Login User
select * from sys.linked_logins

--Query DB Link Data
select * from [F6].[database].[dbo].[table]
--select * from [server name].[database name].[owner name].[table name]

--Query DB Link Data
select * from [F6].[database].[dbo].[table]
--select * from [server name].[database name].[owner name].[table name]
Jason SQL Server

暫存表(Temporary Tables)的使用簡介

http://cbw0731.pixnet.net/blog/post/24993864

暫存表(Temporary Tables)

CREATE TABLE #Yaks (
YakID int,
YakName char(30) )
  • table name 前加入”#”, 表示這是一個暫存表(temporary table)
  • 當session 關閉時, 這個table 將會自動drop
  • 好的寫作習慣, 應在暫存表使用完畢後, 下指令去 drop, 而不是讓系統自動回收
  • 暫存表是存在主機記憶體中, 因此存取速度較快
  • 暫存 table 的限制:
  • 暫存表存在於”tempdb”這個database 裡
  • 如果有兩個使用者建立同一個名字的暫存表, 則他們會各自擁有獨立的一份, 互相不會干擾.
  • 若stored procedure A 建立了一個暫存表, 並呼叫 stored procedure B, 則在 B 中可以存取這個暫存表
  • 如果在SQL Server Management Studio or Query Analyzer 中建立的暫存表, 會等到我們手動drop 去關閉session 才會消失

表格變數(Table Variables)

當我們使用 SQL Server 2000 或以後的版本, 則可以考慮使用 “Table Variables” (表格變數); 使用方式如下例:

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakName
FROM dbo.Yaks
WHERE YakType = ‘Tibetan’

— Do some stuff with the table

 

 

  • 它和暫存表類似, 但它更加彈性, 且不會存在於tempdb 中(完全存在於記憶體).
  • 使用完畢後, 不須手動去 drop它

兩者的使用時機

 

  • 當暫存的資料筆數小於100筆時, 使用表格變數, 否則, 可使用暫存表, 因為針對表格變數, SQL Server 不會去解析/最佳化它的效能.
  • 當我們須要對表格建立索引(Index)時, 則必須使用暫存表.
  • 在使用暫存表時, 最好能在建立後一併建立索引, 這能增加效能 (SQL Server 2005後, 這方面已改善, 所以可以不建索引; 但建立它仍是一個好習慣)

全域暫存表(Global Temporary Tables)

在表格名字前面, 加入兩個”#”, 比如”##YakHerders”, 則表示這是個全域暫存表, 也就是說, 這個表格和一般表格一樣, 可以被所有連線(connections/sessions)使用; 在SQL Server 中, 這樣的應用並不多見.

Jason SQL Server

[轉貼]SQL SERVER–cursor 簡單範例

http://pgtalk.blogspot.com/2007/11/sql-server-cursor.html


USE [testDB]
GO
/****** 物件: StoredProcedure [dbo].[pavo_CalculateRecordSum] 指令碼日期: 11/15/2007 11:55:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_cursor_Test]
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET NOCOUNT ON;
DECLARE Cur Cursor FOR
SELECT a,b,c
FROM Table_A
–開啟cursor
Open Cur

–定義cursor裡要取出的物件
DECLARE @a int,@b nvarchar(50), @c DATETIME

–進入cursor
Fetch NEXT FROM Cur INTO @a, @b,@c
print @@FETCH_STATUS;
While (@@FETCH_STATUS <> -1)
BEGIN
–在此加入LOOP中的內容

————————

–再到下個指標去
Fetch NEXT FROM Cur INTO @a, @b,@c
END

–關閉cursor
CLOSE Cur
–釋放cursor
DEALLOCATE Cur

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
–印出錯誤訊息
PRINT ERROR_NUMBER();
PRINT ERROR_MESSAGE();
ROLLBACK TRANSACTION;
–關閉cursor
CLOSE Cur
–釋放cursor
DEALLOCATE Cur
END CATCH;
END