有在使用 MS SQL server 的朋友,如果有需要找到 identity 跳號的資料,可以參考一下新的寫法,查詢成本省很大。
#長知識
【SQL Tips】之【了解為何identity會發生不連續號碼與快速找出那些號碼是跳號】20171224
**identity保證唯一,不保證連續**
**使用SARG規則與SET BASED方式找出不連續號碼**
**最後那三種找出不連續的技巧,成本比如下
50%(SELF JOIN搭配NOT IN) : 32%(SELF JOIN搭配 OFFSET 函數) : 18%(LAG 函數) **
許多SQL Server開發人員經常會使用identity自動產生連續編號,然而該identity卻有一個特質是發生交易退回(rollback)則會產生跳號的狀況,以下的狀況就是identity碰到交易退回後所產生的跳號情況。
***
use tempdb
go
if object_id('tblNum') is not null
drop table tblNum
go
create table tblNum
(c1 int identity ,
c2 nvarchar(30) unique --防止重複
)
go
insert into tblNum(c2) values ('lewis1')
insert into tblNum(c2) values ('lewis2')
insert into tblNum(c2) values ('lewis3')
insert into tblNum(c2) values ('lewis3') --重複姓名就發生跳號
insert into tblNum(c2) values ('lewis5')
insert into tblNum(c2) values ('lewis5') --重複姓名就發生跳號
insert into tblNum(c2) values ('lewis5') --重複姓名就發生跳號
insert into tblNum(c2) values ('lewis8')
insert into tblNum(c2) values ('lewis9')
insert into tblNum(c2) values ('lewis10')
go
select * from tblNum
GO
--結果
c1 c2
1 lewis1
2 lewis2
3 lewis3
5 lewis5 <--發生跳號
8 lewis8 <--發生跳號
9 lewis9
10 lewis10
***
基本上單一執行INSERT UPDATE DELETE 就是一種隱性交易,上述的範例就是因為條件約束(constraint)的unique緣故,當輸入的文字發生重複的時候,就會自動退回交易,緊接著identity也隨之發生跳號的狀況。
當瞭解identity會有發生跳號的特質之後,接下來分享三種從SQL Server 2005開始使用的抓取跳號的範例,以及從SQL Server 2012開始支援的Windows Offset函數抓取跳號的技巧。這三種技巧都是使用set-based的方式,不使用WHILE迴圈去比對資料的連續性,值得一試。
**從SQL Server 2005支援的抓取跳號的技巧
--使用自我查詢產生搭配CTE
--使用SET BASED技巧取代迴圈處理
WITH DS AS (
SELECT DS1.* FROM tblNum as DS1 JOIN
tblNum as DS2
on DS1.c1 = DS2.c1+1 )
SELECT * FROM tblNum
WHERE c1 NOT IN (SELECT TOP(1) c1 FROM tblNum ORDER BY c1)
EXCEPT
SELECT * FROM DS
GO
**從SQL Server 2012支援的抓取跳號的技巧
--使用自我查詢產生搭配OFFSET
--使用SET BASED技巧取代迴圈處理
WITH DS AS (
SELECT DS1.* FROM tblNum as DS1 JOIN
tblNum as DS2
on DS1.c1 = DS2.c1+1 ),
DS2 AS (SELECT * FROM tblNum ORDER BY 1 OFFSET 1 ROW )
SELECT * FROM DS2
EXCEPT
SELECT * FROM DS
GO
**從SQL Server 2012支援的抓取跳號的技巧
--使用自我查詢產生搭配LAG的Windows Offset技巧
--使用SET BASED技巧取代迴圈處理
WITH DIFF AS (
select *,LAG(c1, 1,0) OVER ( ORDER BY c1) as previous
,c1 - LAG(c1, 1,0) OVER ( ORDER BY c1) as diff
from tblNum
)
SELECT c1,c2 FROM DIFF WHERE diff>1
GO
--結果就是 5/8 之前就發生跳號
c1 c2
5 lewis5
8 lewis8
最後那三種找出不連續的技巧,成本比如下
50%(SELF JOIN搭配NOT IN) : 32%(SELF JOIN搭配 OFFSET) : 18%(LAG)
「drop table in sql」的推薦目錄:
- 關於drop table in sql 在 91 敏捷開發之路 Facebook 的精選貼文
- 關於drop table in sql 在 91 敏捷開發之路 Facebook 的精選貼文
- 關於drop table in sql 在 How to drop all tables from a database with one SQL query? 的評價
- 關於drop table in sql 在 sql-docs/drop-table-transact-sql.md at live - GitHub 的評價
- 關於drop table in sql 在 17.17. DROP TABLE — Teradata Distribution of Presto 0.167-t ... 的評價
- 關於drop table in sql 在 How to drop multiple tables with common prefix in one query? 的評價
drop table in sql 在 91 敏捷開發之路 Facebook 的精選貼文
NOT IN/NOT EXISTS/EXCEPT 使用注意事項
#SQL
【SQL Tips】之【 NULL處理技巧,使用NOT IN /NOT EXISTS/EXCEPT】
許多時候,兩個資料表要找出差異值的時候,許多人第一直覺就是使用【NOT IN】,當然大部分時候都不會有狀況,但是碰到NOT IN的子查詢資料值,如果有NULL,就全盤皆輸,意思就是找不出任何差異。這樣在小量資料可以藉由眼力觀察的狀況下,還可以找出這樣寫法NOT IN的危險地方,但是碰到背景程式,或是資料量多的時候,幾乎無法觀察到這樣危險。所以,告訴自己不要再用【NOT IN】去找出兩邊資料差異。
反倒是要使用【NOT EXISTS】寫法,雖然是複雜一點,但是跨越SQL Server與Oracle兩種資料庫,都是可以正常找出兩邊資料差異值,不擔心NOT EXISTS的基礎資料表有NULL值狀況。另外值得一提就是,需多人會直接使用SQL Server的【EXCEPT】與Oracle的【MINUS】方式,要留意再留意,這樣的方式雖然可以找出差異值,但是針對回傳值,會自動進行重複資料列移除。
【SQL Server Code】
if object_id('x') is not null
drop table x
go
--建立比對基礎資料
create table x(a int)
go
insert into x values(1)
insert into x values(1) --注意重複
insert into x values(NULL) --注意NULL
go
if object_id('y') is not null
drop table y
go
--建立簡單比對來源資料
create table y(b int not null)
go
insert into y values(1)
insert into y values(1)
insert into y values(2)
insert into y values(2)
insert into y values(2) --注意三個2
go
--三種找出y資料表中(1,1,2,2,2) 然後不存在於 x資料表中的(1,1,null)
--【預期要回傳三個2】
--第一種 使用NOT IN 注意(子查詢有NULL值)
--無法處理對比資料表有NULL狀況
SELECT b
FROM y
WHERE b NOT IN(SELECT a from x)
GO
--第二種 使用NOT EXISTS 注意(SELECT 需要 JOIN)
--忠實回傳三個2
SELECT b
FROM y
WHERE NOT EXISTS (SELECT * FROM x WHERE x.a = y.b)
GO
--第三種 使用EXCEPT (僅回傳一個2)
SELECT b FROM y
EXCEPT
SELECT a FROM x
GO
【Oracle Code】
drop table x purge;
--建立比對基礎資料
create table x(a int);
insert into x values(1);
insert into x values(1); --注意重複
insert into x values(NULL); --注意NULL
drop table y purge;
--建立簡單比對來源資料
create table y(b int not null);
insert into y values(1);
insert into y values(1);
insert into y values(2);
insert into y values(2);
insert into y values(2); --注意三個2
--三種找出y資料表中(1,1,2,2,2) 然後不存在於 x資料表中的(1,1,null)
--第一種 使用NOT IN 注意(子查詢有NULL值)
--無法處理對比資料表有NULL狀況
SELECT b
FROM y
WHERE b NOT IN(SELECT a from x);
--第二種 使用NOT EXISTS 注意(SELECT 需要 JOIN)
--忠實回傳三個2
SELECT b
FROM y
WHERE NOT EXISTS (SELECT * FROM x WHERE x.a = y.b);
--第三種 使用EXCEPT (僅回傳一個2)
SELECT b FROM y
MINUS
SELECT a FROM x;
drop table in sql 在 sql-docs/drop-table-transact-sql.md at live - GitHub 的推薦與評價
... for Microsoft SQL Server, tools such as SQL Server Management Studio (SSMS) , SQL Server Data Tools (SSDT) etc. - sql-docs/drop-table-transact-sql.md at ... ... <看更多>
drop table in sql 在 17.17. DROP TABLE — Teradata Distribution of Presto 0.167-t ... 的推薦與評價
SQL Statement Syntax »; 17.17. DROP TABLE; View page ... Synopsis. DROP TABLE [ IF EXISTS ] table_name ... Drop the table orders_by_date if it exists:. ... <看更多>
drop table in sql 在 How to drop all tables from a database with one SQL query? 的推薦與評價
... <看更多>