第25個冬天

 在 SQL Server 中，Index Location（索引的位置） 通常是指「索引資料實際儲存的檔案位置」或「索引是放在哪個資料表分割區（partition）、資料檔案（filegroup）或頁面（page）上」。 CREATE INDEX ... ON <filegroup|partition_scheme> 若將非叢集索引放到與資料表不同的 filegroup 且位於不同磁碟，可能提升查詢效能，因為可以平衡 I/O 載荷；亦可利用 partition scheme 將 Clustered / Nonclustered index 分散到多個 filegroup 之上 https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver17 官方 T‑SQL 語法說明 CREATE INDEX 對比 ON filegroup_name、ON partition_scheme(column) 與 ON [default] 若不指定，非分割資料表的 index 將放在與 base table 相同檔案群組。 [default] 並非資料庫預設組，而是指基表使用的 filegroup 或 partition scheme https://learn.microsoft.com/zh-tw/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver17 建立索引時指定特定檔案 or filegroup https://stackoverflow.com/questions/7823882/how-to-create-indexs-on-a-different-file-of-filegroup?utm_source=chatgpt.com 將現有的索引移至不同的檔案群組 https://learn.microsoft.com/zh-tw/sql/relational-databases/indexes/move-an-existing-index-to-a-different-filegroup?view=sql-server...

 1. 檢查目前資料庫中的使用者是否存在 dbo 確認 dbo 是否真的是一個 "使用者"（user），而不是只有 schema。 -- 查詢目前資料庫內所有的使用者 SELECT name, type_desc FROM sys.database_principals WHERE name = 'dbo'; 若查不到 dbo 或其 type_desc 不是 SQL_USER 或 WINDOWS_USER，表示它不是一個 user。 正確情況下 dbo 應該是 DATABASE_ROLE，不是使用者。 2. 檢查哪一行語法錯誤地參照了 dbo 作為 user 搜尋 SP 或 SQL 檔中使用 GRANT, REVOKE, DENY 等授權語句對象是否錯誤指向 dbo： -- 搜尋資料庫內的物件含有 'GRANT ... TO dbo' SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE '%GRANT%TO dbo%'; 哪些 Stored Procedure 或 View 中誤用 GRANT TO dbo。 3. 在 SSMS 的 Object Explorer 中檢查目前登入帳號是否有權限存取目前資料庫 如果你懷疑是登入帳號無權限，也可能會報這錯： -- 查看目前帳號是否有 mapping 到目前資料庫 SELECT dp.name AS UserName, dp.type_desc, dp.authentication_type_desc FROM sys.database_principals dp WHERE dp.sid = SUSER_SID();  -- 目前登入帳號的 SID 或 -- 顯示目前登入帳號所擁有的權限 SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); 若查不到權限，表示登入帳號無權操作目前資料庫，可能造成 GRANT TO dbo 這類語法被拒絕執行。 4. 檢查是否有混淆 schema 與 user 的概念 有時候開發人員誤把 dbo 當作使用者來授權，其實它是 schema： --...

(1)建立專用的低權限SQL帳號 - 可以建立一個只有讀取Job Agent記錄權限的專用SQL帳號，而非使用高權限的sa帳號。 建立SQL登入帳號 ： USE [master] GO CREATE LOGIN [JobLogReader] WITH PASSWORD=N'複雜密碼', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO 在msdb資料庫中建立使用者 USE [msdb] GO CREATE USER [JobLogReader] FOR LOGIN [JobLogReader] GO 授予最小權限 USE [msdb] GO -- 授予讀取sysjobs和sysjobhistory表的權限 GRANT SELECT ON sysjobs TO [JobLogReader] GRANT SELECT ON sysjobhistory TO [JobLogReader] GRANT SELECT ON sysjobsteps TO [JobLogReader] GO (2) <# .SYNOPSIS 撈取SQL Server 2008 Job Agent執行狀況的LOG並匯出為.log檔案 .DESCRIPTION 此腳本用於從SQL Server 2008撈取Job Agent的執行記錄，並使用BCP命令將結果匯出為.log檔案 匯出的檔案格式與SQL Server Agent圖形介面匯出的格式相容，可再次匯入 腳本使用安全的方式處理SQL Server連線憑證，避免明碼存儲 .PARAMETER ServerInstance SQL Server的實例名稱，例如："SERVERNAME\INSTANCENAME" .PARAMETER Database 包含Job Agent記錄的資料庫名稱，通常為"msdb" .PARAMETER Days 要撈取的天數範圍，例如：7表示撈取最近7天的記錄 .PARAMETER OutputPath 匯出的.log檔案路徑 .EXAMPLE .\Expo...

  使用 PowerShell 腳本來執行 T-SQL  觸發查詢並將結果使用 bcp (Bulk Copy Program) 命令導出到 .log 文件 # 定義 SQL Server 的連線資訊 $serverName = "YOUR_SERVER_NAME" $databaseName = "YOUR_DATABASE_NAME" $username = "sa" $password = "YOUR_PASSWORD" $query = "SELECT * FROM YOUR_TABLE WHERE CONDITION = 'VALUE'" # 替換為您的 T-SQL 查詢 # 設定 bcp 命令所需的查詢和導出文件路徑 $queryFile = "C:\path\to\yourQuery.sql" $outputFile = "C:\path\to\output.log" # 將 SQL 查詢寫入一個文件中 Set-Content -Path $queryFile -Value $query # 建立 bcp 命令 $bcpCommand = "bcp `"$query`" queryout $outputFile -c -t, -S $serverName -d $databaseName -U $username -P $password" # 執行 bcp 命令 Invoke-Expression $bcpCommand # 檢查輸出文件 if ( Test-Path $outputFile ) { Write-Host "文件已成功導出到: $outputFile" } else { Write-Host "導出失敗" } 定義連線參數，包括伺服器名稱、資料庫名稱、用戶名和密碼。 設定要執行的查詢和結果輸出文件的路徑。 使用 bcp 命令從 SQL Server 導出數據到指定的 .log 文件。 $query 如果很複...

  ROW_NUMBER()函數 ROW_NUMBER()要配合 OVER()一起使用，生成的序號可以是升序的，也可以是降序的。 首先使用 SQL 語句創建一個表，然後添加一些數據，SQL 語句如下 /* CREATE TABLE Student ( Id int primary key not null, SName nvarchar(5), SAge int ) INSERT INTO Student(Id,SName,SAge) VALUES(1,'Ted',23),(2,'Sam',18),(3,'Jack',29),(4,'Mike',21) GO */ select * from Student --用ROW_NUMBER來針對年齡由小到大編列序號 SELECT (ROW_NUMBER() OVER( ORDER BY S.SAge ASC )) AS 序號 ,Id,SName AS 姓名 ,SAge AS 年齡 FROM Student AS S 分組排序 在 ROW_NUMBER()函數的 OVER()中，可以使用 PARTITION BY 關鍵字進行分組排序。 首先是根據第一個欄位分組，分組後再在組內根據第二個欄位再排序並加上序號。 PARTITION BY 關鍵字的基本語法如下： ROW_NUMBER() OVER(PARTITION BY 欄位1 ORDER BY 欄位2) 將上面的 Student 表中再添加一個城市的字段 City，新建一個Student2的表。 ROW_NUMBER,RANK,DENSE_RANK各自比較 /* drop table Student2 CREATE TABLE Student2 ( Id int primary key not null, SName nvarchar(50), SAge int, City nvarchar(10) ); INSERT INTO Student2(Id, SName, SAge, City) VALUES (1, 'Ted', 23, '台北'), (2, 'Sam...

 在使用 SQL Server 的 BCP (Bulk Copy Program) 工具匯出數據時，如果遇到中文別名變成亂碼的問題，通常是由於編碼設置不正確所導致。這裡有幾個步驟可以嘗試解決這個問題： 指定正確的字符集：在 BCP 命令中使用 -C 參數來指定正確的字符集。例如，如果你的數據庫使用的是中文字符集，可能需要設置 -C ACP 來使用活動代碼頁，或者使用 -C 936 指定簡體中文的代碼頁。使用 -C ACP 可以幫助確保 BCP 輸出或輸入的數據正確處理中文字符，避免出現亂碼，特別是當資料庫存儲的數據使用的是與 Windows 系統相同的字符編碼時。 bcp "SELECT * FROM your_table" queryout output_file.txt -c -C ACP -T -S your_server 在 SQL Server 的 BCP 工具中使用 -C 參數是來指定字符集的。-C ACP 這個參數指的是讓 BCP 使用活動代碼頁（Active Code Page）進行資料的匯出或匯入。代碼頁（Code Page）是一種用於提供特定語言字符集支援的編碼系統。 活動代碼頁 ACP 通常指的是在 Windows 系統上設定為預設的系統本地字符集，它會根據操作系統的地區設置自動調整。例如，在中文版 Windows 上，活動代碼頁通常設定為 936（即 CP936，對應於簡體中文 GBK 編碼）。 -T 參數用於指定使用 Windows 驗證。 -c 參數用於指定以字符模式進行數據輸出，適合文本數據。 使用 Unicode 格式：如果 -C 參數無法解決問題，嘗試將輸出格式設為 Unicode 格式。使用 -w 參數來將輸出設為 Unicode。 bcp "SELECT * FROM your_table" queryout output_file.txt -w -T -S your_server 啟用xp_cmdshell後就可以用它來間接執行bcp EXEC sp_configure 'show advanced options' , 1 ;  RECONFIGURE;  EXEC sp_configure 'xp_cmdshell' , 1 ;  RECONFIG...

  在朝狀交易特別是層層包覆的SP十分容易遇到.... 建議先不要特別為transaction 取名 一律都統一用 begin tran commit tran rollback tran https://stackoverflow.com/questions/19260055/cannot-roll-back-subtransaction-no-transaction-or-savepoint-of-that-name-was-fo https://www.sqlservercentral.com/forums/topic/transaction-no-transaction-or-savepoint-of-that-name-was-found https://blog.sqlauthority.com/2015/03/11/sql-server-error-msg-6401-level-16-cannot-roll-back-transaction/

  SELECT request_session_id AS spid,     resource_type AS rt,     resource_databASe_id AS rdb,     (CASE resource_type      WHEN 'OBJECT' then object_name(resource_ASsociated_entity_id)      WHEN 'DATABASE' then 'sombas'      ELSE      (SELECT object_name(object_id) FROM sys.partitions          WHERE hobt_id = resource_ASsociated_entity_id) END) AS objname,     resource_description AS rd,     request_mode AS rm,     request_status AS rs FROM sys.dm_tran_locks where request_mode IN ('X','IX') --X 表示 dead lock --IX 表示 wait lock ，這種是因為前一個Request已經dead lock，而被pending的狀態。 kill {上一個查詢結果集的 spid} SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID()  AND resource_associated_entity_id = OBJECT_ID(N'dbo.Table名'); select cmd,* from sys.sysprocesses where blocked > 0         SELECT r.session_id,   ...

列出一或多個郵件設定檔的相關資訊。 EXECUTE msdb.dbo.sysmail_help_profile_sp 列出一或多個Database Mail 設定檔的相關帳戶。 EXECUTE msdb.dbo.sysmail_help_profileaccount_sp 列出Database Mail 帳戶的相關資訊(密碼除外)。 EXECUTE msdb.dbo.sysmail_help_account_sp 列出Database Mail設定檔 SELECT * FROM msdb.dbo.sysmail_profile 列出Database Mail帳戶 SELECT * FROM msdb.dbo.sysmail_account 列出設定檔對應的帳戶 select * from msdb.dbo.sysmail_profileaccount

條列角色與對應成員清單 WITH RoleMembers (member_principal_id, role_principal_id) AS ( SELECT rm1.member_principal_id, rm1.role_principal_id FROM sys.database_role_members rm1 (NOLOCK) UNION ALL SELECT d.member_principal_id, rm.role_principal_id FROM sys.database_role_members rm (NOLOCK) INNER JOIN RoleMembers AS d ON rm.member_principal_id = d.role_principal_id ) select distinct rp.name as database_role, mp.name as database_userl from RoleMembers drm join sys.database_principals rp on (drm.role_principal_id = rp.principal_id) join sys.database_principals mp on (drm.member_principal_id = mp.principal_id) order by rp.name 檢查某個角色下授權的存取清單(以USER_TABLE來篩) SELECT DB_NAME() AS 'DBName' ,p.[name] AS 'PrincipalName' ,p.[type_desc] AS 'PrincipalType' ,p2.[name] AS 'GrantedBy' ,dbp.[permission_name] ,dbp.[state_desc] ,so.name AS 'ObjectName' ,so.[type_de...

  接續上一篇 T-SQL筆記57_MERGE...USING語句使用_part1.When Matched/When Not Matched 對於有些資料記錄，在來源表中不存在，而在目標表中存在，則需要執行 刪除操作，將不存在的記錄刪除掉。 還是使用原來的來源表和目標表，並將數據恢覆原狀： TargetTable中 Id=11 和22 的記錄在SourceTable中不存在，需要刪除掉。 T-SQL程式碼 merge into TargetTable As T Using SourceTable as S on T.Id = S.Id WHEN Not Matched By Source THEN DELETE ; 在這邊SQL 語句中使用到“NOT MATCHED BY Source”這個語句 指定在來源表中不匹配的記錄，需要於目標表中刪除。

最近又回鍋到Porting母公司系統的職務了。 近期在閱讀公司上千行的預存程序既有的程式碼邏輯時候，又發現一個有點陌生的語句。 Merge .... Using... When Matched/When Not Matched 真的有點複雜 https://csharp-video-tutorials.blogspot.com/2014/09/part-69-merge-in-sql-server.html 微軟在 SQL Server 2008 及以後的版本中添加了 Merge 語句，使用 Merge 語句可以多個 SQL 的 DML 語句 組合成一個整體操作，從而 解決實現同樣的功能簡化 SQL 語句和事務。 DML 操作語句指的是對表執行 插入（INSERT）、更新（UPDATE）和刪除（DELETE）數據的操作。 與 Merge 語句搭配使用到的 USING 語句，可根據條件執行 DML 操作。 如此一來，就可以根據相關的條件對目標表執行插入、更新和刪除操作。 Merge 語句執行的操作中，會涉及到兩張表，一張表稱為來源表，另一張表稱為目標表。 語法如下： MERGE [TOP (表達式)] INTO <目標表> AS 別名 USING <來源表或一個來源查詢結果> [AS 別名] ON <條件表達式> [WHEN MATCHED THEN DML 操作] [WHEN NOT MATCHED THEN DML 操作] [WHEN NOT MATCHED BY SOURCE] (1). 使用 MERGE 指定目標表，目標表名放在 INTO 關鍵字的後面。 (2). AS 關鍵字後面是給目標表起一個別名。 (3). TOP 表示只對指定的前幾條執行 DML 操作。 (4). USING 指定源表，並使用 AS 指定別名。 (5). 使用 ON 將源表和目標表建立連接。 (6). 使用 WHEN…THEN 指定匹配條件。 (7). MATCHED 表示如果 ON 條件匹配上了，則執行的 DML 操作。 (8). NOT MATCHED 表示如果 ON 條件未匹配上，則執行的 DML 操作。 SQL測試資料案例建立 CREATE TABLE SourceTable ( Id int primary key not nu...