MSSQL DATALENGTH 尾端空白字串

select '0022000114693',len('0022000114693'),len('0022000114693 '),DATALENGTH('0022000114693'),DATALENGTH('0022000114693 ')

值變化:

MSSQL 使用 FOR JSON 將查詢結果格式化為 JSON

 使用 FOR JSON 將查詢結果格式化為 JSON

---

TRY CATCH

 在較早的 SQL 版本，要檢測 TSQL 是否有發生執行錯誤，都只能透過判斷 @@ERROR 全域變數，直到 SQL2005 才新增了 TRY...CATCH 這個結構化的例外處理功能。 它採用和程式語言中的 TRY...CATCH 類似的語法，在 TRY 區塊內放的是一般陳述式，CATCH 區塊內放的是錯誤處理的陳述式。

使用 TRY...CATCH 結構，若執行的 TSQL 發生了錯誤，錯誤訊息將不會傳到呼叫端，除非透過 RAISERROR 再送出錯誤訊息。

取得錯誤訊息的函式：

當使用 TRY...CATCH 時，你可以在 CATCH 區塊中使用以下函式以取得與錯誤訊息相關的資訊：

ERROR_NUMBER ：發生錯誤的錯誤代碼

ERROR_MESSAGE ：發生錯誤的錯誤訊息

ERROR_SEVERITY ：發生錯誤的錯誤層級

ERROR_STATE ：發生錯誤的錯誤狀態

ERROR_PROCEDURE ：發生錯誤的程序名稱

ERROR_LINE ：發生錯誤的行數

BEGIN TRY

INSERT Emp(EmpName, DepNo) Values (@EmpName, 1) 

INSERT Emp(EmpName, DepNo) Values (@EmpName, 1) 

END TRY

BEGIN CATCH

    PRINT '錯誤代碼：' + cast(ERROR_NUMBER() as varchar(5)) + char(13) +

  '錯誤訊息：' + ERROR_MESSAGE()

    RAISERROR('資料寫入錯誤',16,10)

END CATCH; 

以上函式，若不是在 CATCH 區塊中使用，都將回傳 NULL 。

mssql 日期 dateadd 應用

取60天前的日期

 date>=cast(dateadd(day,-60,getdate()) as date)

MSSQL stored procedure TRY...CATCH

參考來源:TRY...CATCH (Transact-SQL)

 BEGIN TRY

SELECT  aa/0 FROM DEMO

END TRY

BEGIN CATCH

    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

使用FOR XML PATH將多筆資料組合成一個字串

 參考:使用FOR XML PATH將多筆資料組合成一個字串

--

在SQL 結尾加入  FOR XML PATH('')

SELECT  ',' + NAME  FROM TEAMLIST WHERE TEAMID = 1

FOR XML PATH('')

MS-SQL while

   declare @go_qty int

  set @go_qty=1

  while (@go_qty<=2)

  begin

     print @go_qty

set @go_qty+=1

 

  end

SQL 判斷小數點

 select 3.2%1

SQL 延遲指令

 select getdate();

waitfor delay '00:00:30';

select getdate();

SqlConnection.ConnectionTimeout Property

 參考引用:SqlConnection.ConnectionTimeout

---

private static void OpenSqlConnection()

{

    string connectionString = GetConnectionString();

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

        connection.Open();

        Console.WriteLine("State: {0}", connection.State);

        Console.WriteLine("ConnectionTimeout: {0}",

            connection.ConnectionTimeout);

    }

}

static private string GetConnectionString()

{

    // To avoid storing the connection string in your code,

    // you can retrieve it from a configuration file, using the

    // System.Configuration.ConfigurationSettings.AppSettings property

    return "Data Source=(local);Initial Catalog=AdventureWorks;"

        + "Integrated Security=SSPI;Connection Timeout=30";

}

使用 BULK INSERT 或 OPENROWSET(BULK...) 將資料匯入 SQL Server

 參考:使用 BULK INSERT 或 OPENROWSET(BULK...) 將資料匯入 SQL Server

撰寫Stored Procedure小細節

 參考引用:[SQL SERVER]撰寫Stored Procedure小細節

---

撰寫Stored Procedure小細節

EXEC AssetLibrary_AssetDeleteAttributes @AssetID

declare @myquery nvarchar(4000);

set @myquery = 'SELECT * from dbo.test2 where c1 = @id';

--第一次

execute sp_executesql @myquery,N'@id int', @id = 1;

--第二次

execute sp_executesql @myquery,N'@id int', @id = 2;

--查看執行計畫

SELECT cap.usecounts as '使用次數',objtype as '快取類型',st.text

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%dbo.test2%'

DECLARE @myquery VARCHAR(8000)

,@myid int;

set @myid = 5;

set @myquery = 'select * from dbo.test2 where c1 = '+ CAST( @myid as varchar);

--第一次

exec (@myquery) 

set @myid = 6; 

set @myquery = 'select * from dbo.test2 where c1 = '+ CAST(@myid as varchar );

--第二次

exec (@myquery) 

SELECT cap.usecounts as '使用次數',objtype as '快取類型',st.text            

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%dbo.test2%'

MSSQL 自動排版工具 -Poor Man's T-SQL Formatter

 Poor Man's T-SQL Formatter

---

資源集區'internal' 中的系統記憶體不足，無法執行此查詢

 sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'max server memory', 2147483647;

GO

RECONFIGURE;

GO

---------------------

EXEC sp_configure 'show advanced options', '1' RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'max server memory', 2147483647 RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced options', '0' RECONFIGURE WITH OVERRIDE;

---------------------

 DBCC FREESYSTEMCACHE ('ALL'); --釋放所以在快取內，未被使用的項目

 DBCC FREEPROCCACHE; --從計畫快取移除所有元素

 DBCC DROPCLEANBUFFERS; --清除緩衝區裡面的所有暫存項目

 DBCC FREESESSIONCACHE; --清除所有查詢所使用的分散式連線快取

WAITFOR (Transact-SQL)

 引用來源:WAITFOR (Transact-SQL)

----

範例

A. 使用 WAITFOR TIME

下列範例會在下午 10:20 (22:20) 執行 msdb 資料庫中的預存程序 sp_update_job。

SQL

複製

EXECUTE sp_add_job @job_name = 'TestJob';  

BEGIN  

    WAITFOR TIME '22:20';  

    EXECUTE sp_update_job @job_name = 'TestJob',  

        @new_name = 'UpdatedJob';  

END;  

GO  

B. 使用 WAITFOR DELAY

下列範例會在延遲兩小時之後執行預存程序。

SQL

複製

BEGIN  

    WAITFOR DELAY '02:00';  

    EXECUTE sp_helpdb;  

END;  

GO  

C. 搭配本機變數來使用 WAITFOR DELAY

下列範例顯示如何搭配 WAITFOR DELAY 選項來使用本機變數。 這預存程序會等待一陣可變的時段，再將經歷的時、分、秒數資訊傳回給使用者。

SQL

複製

IF OBJECT_ID('dbo.TimeDelay_hh_mm_ss','P') IS NOT NULL  

    DROP PROCEDURE dbo.TimeDelay_hh_mm_ss;  

GO  

CREATE PROCEDURE dbo.TimeDelay_hh_mm_ss   

    (  

    @DelayLength char(8)= '00:00:00'  

    )  

AS  

DECLARE @ReturnInfo VARCHAR(255)  

IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0  

    BEGIN  

        SELECT @ReturnInfo = 'Invalid time ' + @DelayLength   

        + ',hh:mm:ss, submitted.';  

        -- This PRINT statement is for testing, not use in production.  

        PRINT @ReturnInfo   

        RETURN(1)  

    END  

BEGIN  

    WAITFOR DELAY @DelayLength  

    SELECT @ReturnInfo = 'A total time of ' + @DelayLength + ',   

        hh:mm:ss, has elapsed! Your time is up.'  

    -- This PRINT statement is for testing, not use in production.  

    PRINT @ReturnInfo;  

END;  

GO  

/* This statement executes the dbo.TimeDelay_hh_mm_ss procedure. */  

EXEC TimeDelay_hh_mm_ss '00:00:10';  

GO  

以下為結果集。

A total time of 00:00:10, in hh:mm:ss, has elapsed. Your time is up.

bcp 公用程式

 bcp 公用程式

--

SQL Server 中的 JSON 資料

要在 SQL Server 中應用 JSON 資料

必須 SQL Server 2016 (13.x) 以上版本

請參考:SQL Server 中的 JSON 資料

SQL Server的最新更新和版本歷程記錄

 SQL Server的最新更新和版本歷程記錄

---

非常詳細的所有 MS-SQL 2008 到 MS-SQL 2022 核心版本記錄表

不知道電腦到底安裝了哪一版本,可以到該網址內查看記錄表

緩衝集區裡沒有足夠的可用記憶體

DBCC MEMORYSTATUS

釋放MSSQL Server Cache (緩衝集區裡沒有足夠的可用記憶體)

當出現"緩衝集區裡沒有足夠的可用記憶體"此項錯誤時，可嘗試使用以下指令釋放快取記憶體；或是於程式中固定一段時間執行此三個Query

操作指令(適用2005,2008):

DBCC FREESYSTEMCACHE('all')

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE WITH NO_INFOMSGS

***********************

DBCC FREEPROCCACHE --清除存儲過程相關的緩存 

DBCC FREESESSIONCACHE --會話緩存 

DBCC FREESYSTEMCACHE('All') --系統緩存 

DBCC DROPCLEANBUFFERS --所有緩存

MS-SQL刪除帳號

 SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

將帳號佔用的權限轉移到dbo

alter authorization on schema::[db_securityadmin] To [dbo]