這邊跟大家介紹一套做 DB migration 的好東西 pt-online-schema-change
,這是可以讓 DBA 省時省心力的工具,他可以用簡單的指令讓修改資料表結構時不鎖表(可寫入)與接近零 downtime 的好物。
SQL Server
[C#][MSSQL] 連線結束後 Temp Table 沒有自動釋放 !?
上次 [C#] SQL 資料庫 Connection Pool 連線池觀念釐清 有提到可以開啟 Connection Pool 來減少開啟連線的效能耗損,但最近發現有 Stored Procedure 執行後沒有手動 Drop Temp Table,而 .NET 又將連線丟回 Pool 裡面造成 Temp Table 沒有被正常釋放的問題,下面來測試看看是不是真的會有這種情況發生。
[MSSQL] SQL Server 的一些眉眉角角 part.1
目前工作 Database 方面主要都在使用 SQL Server,寫了近兩年的 Stored Procedure 也遇到了許多的問題,在這裡簡單筆記一下。
- 暫存資料表小知識
*#
或##
建立的暫存資料表,可從tempdb.暫存資料表
找到。
*#
建立的暫存資料表只有該連線可以取用;##
則是全部連線均可使用。
* 連線結束時皆會自動刪除,若程式中有 Connection Pool 來管理連線則不會立刻刪除。
* 父層 Stored Procedure 建立的暫存資料表子層也可以使用;子層 Stored Procedure 建立的暫存資料表父層也可以使用 - 若開啟交易 (Transaction) 但無提交異動 (Commit) 會發生資料表鎖死 (Table Lock) 的問題,切記要提交異動 (Commit)。
- 使用主鍵來
UPDATE
為資料鎖 (Row Lock),若使用其他條件則有可能會升級成資料頁鎖(Page Lock) 或資料表鎖 (Table Lock)。 - 交易 (Transaction) 具有復原機制 (RollBack),但實際上資料表中的資料已經被異動且鎖定的關係所以無法取得,可利用
WITH(NOLOCK)
來取得被鎖定的髒資料。 - 未開啟交易時使用
CURSOR
或UPDATE FROM
若超過五千筆時可能會造成鎖定與死結,可使用ROW_NUMBER()
排序資料塞入暫存資料表並使用WHILE
來一筆一筆更新資料避開鎖定,速度雖慢但能減少鎖死。
123456789101112131415161718192021222324252627282930313233343536373839404142SET NOCOUNT ONDECLARE@RowNum INT,@RowCount INT,@Temp_Id INTSELECTROW_NUMBER() OVER(ORDER BY [Id]) AS RowNum,[Id],[Name]INTO#TempFROM[exfast].[dbo].[TableA] WITH(NOLOCK)SELECT@RowNum = 1,@RowCount = (SELECT SUM(1) FROM #Temp)WHILE(@RowNum <= @RowCount)BEGINSELECT@Temp_Id = [Id]FROM#TempWHERERowNum = @RowNumUPDATE[exfast].[dbo].[TableA]SET[Name] = 'dddd'WHERE[Id] = @Temp_IdSET @RowNum = @RowNum + 1ENDDROP TABLE #Temp - 高負載情況下使用
UPDATE OUTPUT
的方法來取得資料更新前後的狀態,可減少SELECT
次數提高效率。
123456789101112131415161718192021DECLARE @TempA TABLE([INSERTED_Id] INT,[INSERTED_Name] NVARCHAR(32),[DELETED_Id] INT,[DELETED_Name] NVARCHAR(32))UPDATE[exfast.Helper].[dbo].[TableA]SET[Name] = 'qqqq'OUTPUTINSERTED.[Id],INSERTED.[Name],DELETED.[Id],DELETED.[Name]INTO@TempASELECT * FROM @TempA - 高負載情況下可能會將隔離層級拉高到 Serializable,這時使用交易會發生相同資料表
SELECT
UPDATE
中的 Shared Lock 與 Exclusive Lock 撞車造成 DeadLock,可在SELECT
加上WITH(UPDLOCK)
來減少此問題。
1234567891011121314151617-- 故意指定隔離層級模擬撞車的情況SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT * FROM [exfast.Helper].[dbo].[TableA]WAITFOR DELAY '00:00:10'UPDATE[exfast.Helper].[dbo].[TableA]SET[Name] = 'cccc'WHERE[Id] = 2ROLLBACK
[Docker][Linux] SQL Server 安裝筆記
環境:Window 7
- 安裝 Docker Toolbox
- 新增 Volume
1 |
docker volume create vol-mssql |
- 建立容器
1 2 3 4 5 6 7 8 9 10 |
docker run \ --restart=always \ --name mssql \ --mount "source=vol-mssql,target=/var/opt/mssql" \ -e "ACCEPT_EULA=Y" \ -e "SA_PASSWORD=1OCHWiY9O#RF" \ -e "MSSQL_PID=Express" \ -e "MSSQL_COLLATION=Chinese_Taiwan_Stroke_CI_AS" \ -p 1433:1433 \ -d microsoft/mssql-server-linux:latest |
- 更新時區
1 2 3 4 |
docker exec -ti mssql bash apt-get update apt-get install tzdata -y dpkg-reconfigure tzdata |
參考資料:
[MSSQL] 開啟對外 TCP/IP Port 連線
- 電腦管理
- 服務與應用程式
- SQL Server 網路組態
- MSSQLSERVER
- TCP/IP
- 通訊協定
- 啟用:是
這樣就可以用預設 port 1433 連線囉
[MSSQL] 當自動識別欄位需要Insert資料時應該怎麼辦 ?
今天在手動將MySQL資料匯入MSSQL時遇到了這個問題,但又不想將自動識別欄位取消掉,於是乎找了一下資料來解決 …
語法:
1 |
SET IDENTITY_INSERT TABLE_NAME OFF; |
1 |
SET IDENTITY_INSERT TABLE_NAME ON; |
範例:
1 2 3 4 5 6 7 |
SET IDENTITY_INSERT [dbo].[Demo] ON; INSERT INTO [dbo].[Demo] ([ID], [Name]) VALUES (N'1', N'王小名'); INSERT INTO [dbo].[Demo] ([ID], [Name]) VALUES (N'2', N'王大名'); SET IDENTITY_INSERT [dbo].[Demo] OFF; |
參考:
[MSSQL] 使用 T-SQL 實作分頁
很多表格會遇上資料有上萬筆資料或者上千萬時的大數據,這時如果還是把所有資料撈出來在分頁的話,會造成使用者頁面讀取時間過久,更會有伺服器壓力倍增的問題,這裡提供一個SQL解決方案。
SQL如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @PageNum AS BIGINT; DECLARE @PageSize AS BIGINT; DECLARE @PageCount AS BIGINT; SET @PageNum = 3; SET @PageSize = 10; SET @PageCount = ( SELECT COUNT(1) FROM dbo.TBase_Product WHERE Product_State != 9) / @PageSize; IF @PageNum < 1 SET @PageNum = 1; WITH Temp AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY Product_ID) AS RowNum FROM dbo.TBase_Product ) SELECT RowNum, @PageCount AS PageCount FROM Temp WHERE RowNum BETWEEN ((@PageNum - 1) * @PageSize + 1) AND (@PageNum * @PageSize) ORDER BY Product_ID; |
執行結果:
參考:
[MSSQL] SQL Server Management Studio 連線非預設 1433 port 埠號
參考:SQL Server 透過 TCP/IP 遠端連線時如何使用非 1433 埠號
自從把SQL Server放到公用網路上的時候,就常常被人暴力破解Try我的密碼,雖然不至於被猜到,但是看了總是不太開心,於是乎就把預設的Port修改掉了,卻發生使用SQL Server Management Studio無法連線的問題,之前用MySQL時格式不外乎就是在後面加上:port
但轉來MSSQL卻發生無法連線的問題…
爬了一下文才發現,使用ip,port(127.0.0.1,4321)
這種格式就可以正常連線囉!
[MSSQL] SQL Server Management Studio 連線工具
來源:使用 SQL Server Management Studio 連接到 SQL Database 並執行範例 T-SQL 查詢
SQL Server Management Studio (SSMS) 是一個整合式環境,您可以加以利用來存取、設定、管理及開發 SQL Server 的所有元件。 SSMS 利用許多豐富的指令碼編輯器來合併一群非常廣泛的圖形工具,使所有技術層級的開發人員及管理員都能夠存取。 此版除了提升與舊版 SQL Server 之間的相容性之外,也改進了獨立 Web 安裝程式,以及 SSMS 中,當有新版本可用時的快顯通知。
下載 SQL Server Management Studio (SSMS)
筆記一下…
- 1
- 2