有在使用 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)

Entity Framework Performance and What You Can Do About It

傳送門：https://www.simple-talk.com/…/entity-framework-performance…/

很多時候 ORM 慢，不是本身慢，而是使用 ORM 的人犯了幾個不該犯的錯誤，下列列出該注意的事項：

① model還沒給條件，就觸發執行 SQL 的問題

② "N+1" Select 的問題（沒有一次把要用到的關聯資料撈出來）

③ Select * 欄位全撈的問題

④ DataType 不相符的問題

⑤ Miss Index 的問題

⑥ 如何讓查詢計畫 reuse 的問題，例如分頁查詢影響查詢參數的方式

⑦ 批次作業使用 BulkInsert 相關的 API 可以提升 10 倍以上的效能

⑧ 用 AddRange() 取代迴圈裡面的 Add()，搭配AutoDetectChangesEnabled 開關。

※ 範例的效能提升：reduced from 34 seconds down to 85 ms – a 400x speed boost!

⑨ 如果只是要讀資料，沒有要異動，透過 AsNoTracking 關掉 Change tracking。

其他的注意事項，請自行參考文章後半部細節。

#EntityFramework #程式不是會動就好

如何將單次查詢改為批次查詢(EXCEL VBA自動化)
01_單次查詢InputBox介面
02_單次查詢程式碼
03_用移除重複建立欄位清單
04_單次轉批次查詢程式(加入迴圈)
05_單次轉批次查詢程式(完成)
以往要查詢資料時通常需要將資料匯出到資料庫中，
再利用ADO與SQL的SELECT語法來查詢資料，
但這樣的方法技術難度很高，並不適合一般性的辦公室應用，
因此如何不用資料庫，也能在EXCEL中查詢變得很重要。

為什麼以前在EXCEL中查詢很困難，
主要是EXCEL2003以前的版本一個工作表最多只能容納65536筆資料，
加上如果資料倆一多就很容易白畫面，接下來就是EXCEL程式當掉。
EXCEL2007筆數提高到1048576筆，
此外，現在電腦記憶體也從早期的64MB，提高到4GB以上，增加了六十幾倍，
因此大大提高EXCEL的可用性，把它拿來當資料庫應該也沒什麼不可，
加上EXCEL本身提供VBA開發環境，對EXCEL的自動化有如虎添翼的功效，
事實上，VBA無須安裝，所以很容易取得，又跨WINDOWS與MAC電腦，
加上只要有EXCEL電腦就有VBA，
讓VBA的執行更容易，不向其他開發工具，如VB.NET，光安裝就是大問題，
更別說執行與設計的複雜度，非一般人可及。

以下用很簡單的方式教大家快速建立EXCEL版的查詢系統，會先錄製巨集，
再來修改，其後就可以當成範本，快速修改成自己所需，
這樣彈性也大，也省去大筆軟體開發的經費。

01_單次查詢InputBox介面

02_單次查詢程式碼

03_用移除重複建立欄位清單

04_單次轉批次查詢程式(加入迴圈)

05_單次轉批次查詢程式(完成)


教學影音：

VBA到Python程式開發202101第7次上課

01_重點回顧與判斷檔案是否存在
02_判斷all存在與刪除與刪除all以外檔案
03_合併細節說明與檔案分割與取得檔名
04_新增SQLite資料庫資料表與欄位
05_SQL資料庫與建立資料表與欄位並新增資料
06_用Select語法查詢結果

完整教學
http://goo.gl/aQTMFS

吳老師教學論壇
http://www.tqc.idv.tw/

教學論壇(之後課程會放論壇上課學員請自行加入)：
https://groups.google.com/forum/#!forum/tcfst_python_2021_1

課程簡介：入門
建置Python開發環境
基本語法與結構控制
迴圈、資料結構及函式
VBA重要函數到Python
檔案處理
資料庫處理
課程簡介：進階
網頁資料擷取與分析、Python網頁測試自動化、YouTube影片下載器
處理 Excel 試算表、處理 PDF 與 Word 文件、處理 CSV 檔和 JSON 資料
實戰：PM2.5即時監測顯示器、Email 和文字簡訊、處理影像圖片、以 GUI 自動化來控制鍵盤和滑鼠

上課用書：
參考書目
Python初學特訓班(附250分鐘影音教學/範例程式)
作者： 鄧文淵/總監製, 文淵閣工作室/編著?
出版社：碁峰? 出版日期：2016/11/29

Python程式設計入門
作者：葉難
ISBN：9789864340057
出版社：博碩文化
出版日期：2015/04/02

吳老師 110/4/6

EXCEL,VBA,Python,自強工業基金會,EXCEL,VBA,函數,程式設計,線上教學,PYTHON安裝環境

中央從Python程式到網路爬蟲應用第7次SQLite建立資料庫與新增資料&匯入會員資料的50個會員到資料庫中&改用format產生SQL語法與寫入資料&用executemany&&用select查詢資料表&刪除資料與自訂函數&Update修改資料&會員編號與文字關鍵字與日期查詢

上課內容：
01_重點回顧與SQLite建立資料庫與新增資料
02_修改為自動增號與迴圈自動輸入
03_匯入會員資料的50個會員到資料庫中
04_改用format產生SQL語法與寫入資料
05_改用executemany大量輸入資料
06_修改list2的第五欄資料與大量新增資料
07_用select查詢資料表
08_SQL刪除資料與自訂函數
09_Update修改資料與查詢結果
10_會員編號與文字關鍵字與日期查詢
11_全省郵局地址轉入資料庫與查詢作業說明

完整影音
http://goo.gl/aQTMFS

教學論壇(之後課程會放論壇上課學員請自行加入)：
https://groups.google.com/forum/#!forum/ncu_python_2019

懶人包：
EXCEL函數與VBA http://terry28853669.pixnet.net/blog/category/list/1384521
EXCEL VBA自動化教學 http://terry28853669.pixnet.net/blog/category/list/1384524

TQC+Python證照目錄：
Python 第1類：基本程式設計
技能內容：變數與常數、指定敘述、標準輸入輸出、運算式、算術運算子、數學函式的應用、格式化的輸出Python 第2類：選擇敘述
技能內容：if、if...else、if…elif
Python 第3類：迴圈敘述
技能內容：while、for…in
Python 第4類：進階控制流程
技能內容：常用的控制結構、條件判斷、迴圈
Python 第5類：函式(Function)
技能內容：函式使用、傳遞參數、回傳資料、內建函式、區域變數與全域變數
Python 第6類：串列(List)的運作(一維、二維以及多維)
技能內容：串列的建立、串列的函式、串列參數傳遞、串列應用
Python 第7類：數組（Tuple）、集合（Set）以及詞典（Dictionary）
技能內容：數組、集合、詞典的建立、運作及應用
Python 第8類：字串(String)的運作
技能內容：字串的建立、字串的庫存函式、字串的應用
Python 第9類：檔案與異常處理
技能內容：文字I/O、檔案的建立、寫入資料與讀取資料、二進位I/O、編碼（Encoding）、異常處理

課程簡介：入門
建置Python開發環境
基本語法與結構控制
迴圈、資料結構及函式
VBA重要函數到Python
檔案處理
資料庫處理
課程簡介：進階
網頁資料擷取與分析、Python網頁測試自動化、YouTube影片下載器
處理 Excel 試算表、處理 PDF 與 Word 文件、處理 CSV 檔和 JSON 資料
實戰：PM2.5即時監測顯示器、Email 和文字簡訊、處理影像圖片、以 GUI 自動化來控制鍵盤和滑鼠

上課用書：
參考書目
Python初學特訓班(附250分鐘影音教學/範例程式)
作者： 鄧文淵/總監製, 文淵閣工作室/編著
出版社：碁峰? 出版日期：2016/11/29

吳老師 108/3/9

Python,中央大學資工系,福建師範大學,EXCEL,VBA,函數,程式設計,線上教學,PYTHON安裝環境,網路爬蟲

中央從Python程式到網路爬蟲應用第7次SQLite建立資料庫與新增資料&匯入會員資料的50個會員到資料庫中&改用format產生SQL語法與寫入資料&用executemany&&用select查詢資料表&刪除資料與自訂函數&Update修改資料&會員編號與文字關鍵字與日期查詢

上課內容：
01_重點回顧與SQLite建立資料庫與新增資料
02_修改為自動增號與迴圈自動輸入
03_匯入會員資料的50個會員到資料庫中
04_改用format產生SQL語法與寫入資料
05_改用executemany大量輸入資料
06_修改list2的第五欄資料與大量新增資料
07_用select查詢資料表
08_SQL刪除資料與自訂函數
09_Update修改資料與查詢結果
10_會員編號與文字關鍵字與日期查詢
11_全省郵局地址轉入資料庫與查詢作業說明

完整影音
http://goo.gl/aQTMFS

教學論壇(之後課程會放論壇上課學員請自行加入)：
https://groups.google.com/forum/#!forum/ncu_python_2019

懶人包：
EXCEL函數與VBA http://terry28853669.pixnet.net/blog/category/list/1384521
EXCEL VBA自動化教學 http://terry28853669.pixnet.net/blog/category/list/1384524

TQC+Python證照目錄：
Python 第1類：基本程式設計
技能內容：變數與常數、指定敘述、標準輸入輸出、運算式、算術運算子、數學函式的應用、格式化的輸出Python 第2類：選擇敘述
技能內容：if、if...else、if…elif
Python 第3類：迴圈敘述
技能內容：while、for…in
Python 第4類：進階控制流程
技能內容：常用的控制結構、條件判斷、迴圈
Python 第5類：函式(Function)
技能內容：函式使用、傳遞參數、回傳資料、內建函式、區域變數與全域變數
Python 第6類：串列(List)的運作(一維、二維以及多維)
技能內容：串列的建立、串列的函式、串列參數傳遞、串列應用
Python 第7類：數組（Tuple）、集合（Set）以及詞典（Dictionary）
技能內容：數組、集合、詞典的建立、運作及應用
Python 第8類：字串(String)的運作
技能內容：字串的建立、字串的庫存函式、字串的應用
Python 第9類：檔案與異常處理
技能內容：文字I/O、檔案的建立、寫入資料與讀取資料、二進位I/O、編碼（Encoding）、異常處理

課程簡介：入門
建置Python開發環境
基本語法與結構控制
迴圈、資料結構及函式
VBA重要函數到Python
檔案處理
資料庫處理
課程簡介：進階
網頁資料擷取與分析、Python網頁測試自動化、YouTube影片下載器
處理 Excel 試算表、處理 PDF 與 Word 文件、處理 CSV 檔和 JSON 資料
實戰：PM2.5即時監測顯示器、Email 和文字簡訊、處理影像圖片、以 GUI 自動化來控制鍵盤和滑鼠

上課用書：
參考書目
Python初學特訓班(附250分鐘影音教學/範例程式)
作者： 鄧文淵/總監製, 文淵閣工作室/編著
出版社：碁峰? 出版日期：2016/11/29

吳老師 108/3/9

Python,中央大學資工系,福建師範大學,EXCEL,VBA,函數,程式設計,線上教學,PYTHON安裝環境,網路爬蟲