今天發生一個SQL奇怪的現象，記錄一下
就是訂單列表(使用分頁的SQL 找出 top 20)跑很久才出來，大約10多秒 
SQL指令條件僅有訂單日期(預設是1年內訂單，訂單總數有130萬筆)，而訂單日期也有做index
之前都大約1~2秒就出來，這一兩天卻要10秒才跑出來

用執行計畫評估功能查也看不出哪裡有問題，把一些子查詢拿掉也沒有改善
所以就改一下搜尋條件，改搜尋半年內訂單，大約 5秒
改搜尋兩年內訂單，1秒!!! 真是神奇，把條件區間拉大反而很快???????
直覺上覺得索引table是不是太大太亂了，剛好看到CreateDate索引有個重建按鈕
就勇敢把他按下去，想說會不會跑很久，結果重建只要不到1秒就好了
然後神奇的事發生了，訂單列表不須一秒就顯示出來啦!

SQL 指令大概長這樣

Select * from (

Select Top 20 * from (

select Top 20 a.*

--, CompelteOrderId= (select top 1 K.Id from packing_list_main k With(NoLock) join Order_Main p With(NoLock) on k.Order_Id=p.Id where k.En_Packing_List_Status=300 and P.MEmber_Id=a.Member_Id )

--, [MemoCount_前台問題] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 1)

--, [MemoCount_業務備註] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 3)

--, [MemoCount_系統檢查] = (Select COUNT(*) from Order_Memo With(NoLock) where Order_Memo.Order_Id = a.Id and Memo_Type = 4)

--, QnACount = (select count(*) from Order_QnA With(NoLock) where Order_Id = a.Id)

from V_Order_Main_For_Admin_List a With(NoLock) left join Member b with(NoLock) on a.Member_Id=b.Id where (1=1) and En_Order_Status <> -300 and a.Create_Date >='2018-06-20' order by Id Desc

) AS T1 order by Id

) AS T2 order by Id Desc

參考 will will web 的舊文章，原來定期把索引整理整理，也是要記得做的。
不過週期上我想 1季或半年跑一次應該就可以了
-------------------------------------------------------------------
後記: 2020/6/22 -  一年後又發生一樣的狀況，真是神奇
這次 OrderMain CreateDate 沒有很分散。只有 2.X %
想說其他 index 是不是該重建，但是都沒有效果
最後只好重建 CreateDate --> 結果居然解決了

看 2021年會在發生嗎?

 

刪除使用者, 出現 "無法卸除目前使用者已登入的登入" 時, 可以用以下的指令查該 user 的執行緒

sp_who 'sa2'

再用 kill 指令刪除執行緒..

查現有 SQL Server 中登入帳號的 SID 語法:

select sid, loginname, dbname from dbo.syslogins;

建立使用者語法

CREATE LOGIN [loginname] WITH PASSWORD=N'password',

DEFAULT_DATABASE=[dbname ], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,

SID = sid

組合如下:

select 'CREATE LOGIN [' + loginname + '] WITH PASSWORD=N''XXX'', DEFAULT_DATABASE=[' + dbname + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,SID =' +  master.dbo.fn_varbintohexstr(sid) from dbo.syslogins Where loginname = 'username';

記得要改密碼 (XXX) 和 'username' ..

 
 
 

如下, 要記得修改 database_name, userOLD, userNEW 三個參數

CREATE TABLE #Command

    (

    Id int NOT NULL IDENTITY (1, 1),

    command nvarchar(MAX) NOT NULL

    )  ON [PRIMARY]

     TEXTIMAGE_ON [PRIMARY]

GO



USE database_name  -- Use the database from which you want to extract the permissions

GO



SET NOCOUNT ON



DECLARE @OldUser sysname, @NewUser sysname



SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from

SET @NewUser = 'userNEW' --The user or role to which to copy the permissions to



insert into #Command(command)

Select convert(nvarchar(max), '--Database Context')



insert into #Command(command)

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME())



insert into #Command(command)

SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)



insert into #Command(command)

SELECT 'EXEC sp_addrolemember @rolename ='

    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'

FROM    sys.database_role_members AS rm

WHERE USER_NAME(rm.member_principal_id) = @OldUser

ORDER BY rm.role_principal_id ASC



insert into #Command(command)

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)

    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM    sys.database_permissions AS perm

    INNER JOIN

    sys.objects AS obj

    ON perm.major_id = obj.[object_id]

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

    LEFT JOIN

    sys.columns AS cl

    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE usr.name = @OldUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC



insert into #Command(command)

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

    + SPACE(1) + perm.permission_name + SPACE(1)

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'

FROM    sys.database_permissions AS perm

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

WHERE usr.name = @OldUser

AND perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC



Select command from #Command order by Id

drop table #Command

SQL 字串比對, varchar 或 char 和 N'xxxxxx'  的比對會非常慢, 要注意.

使用範例如下:

    void getList()

    {

        var Q = TN.Admin.TCatOrderDeliveryRecord.Select().OrderBy("DeliveryCompletion_Date, Id Desc");



        if (U2.WU.IsNonEmptyFromQueryStringOrForm("OD00"))

        {

            Q.And("OD00 = ", U2.WU.GetValue("OD00"));

        }



        if (U2.WU.V.StartDate_IsOK)

        {

            Q.And("DeliveryCompletion_Date >= ", U2.WU.V.StartDate);

        }



        if (U2.WU.V.EndDate_IsOK)

        {

            Q.And("DeliveryCompletion_Date < ", U2.WU.V.EndDate);

        }



        U2.JSON.WriteSuccessData(Q.GetPageDT2(U2.WU.V.CurrentPage, U2.WU.V.PageSize));

    }

轉換文章內容成為excel 檔案的時候
發生的錯誤
網路上查都好像是跟圖片有關的錯誤
後來才確認是 emoji 的問題
文章裡有出現

🙋‍♀️🙋‍♀️🙋‍♀️

這種東西 

SQL:
SQL 好像也沒辦法完美的取代 ( 如果有辦法對準是哪個字元就可以 但如果是一個字串好像會無法取代)
只有剛好對到那個字元開頭時才能換
NCHAR(65039)  NCHAR(8205)

Select TOP 10 
Replace(SUBSTRING(content,10,15),NCHAR(65039) ,'XX') ,                                    ---HongKong‍️‍️‍️🙋‍♀️怎
REPLACE(SUBSTRING(content,17,8),NCHAR(65039),'OO')                                        ---OO怎 
FROM [Table] 


C#
最後回到C#來處理
首先用 把string  .ToArray() 變成char
找出字元後 轉成 int 來確認要怎麼表達這個 char
最後結果就變成>>
Convert.ToInt32(Table.Rows[0]["Content"].ToString().ToArray());
之後直接變成 char去取代
row["mycolumn"].ToString().Replace((char)65039, ' ').Replace((char)8205, ' ');
然後就暫時 沒錯誤了
只是這網站一直再更新
https://emojipedia.org/unicode-12.0/

所以未來可能還會有新的問題
目前還不知道甚麼快速的解法







 

修改欄位設定時，出現需要刪除資料表(drop table)再建立資料表(create table)才能修改時，
於 [工具] > [選項] > [設計工具]
清除防止儲存需要重新建立資料表的變更] 核取方塊，即可。
如下圖


參考網址：https://support.microsoft.com/zh-tw/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-change
 

<範例>
Table: [員工資料表]
欄位: [主管], [員工]

需求是: 當輸入一個員工姓名, 要把該員工下面所有階層的員工都Show出來

SQL語法:
 

WITH [員工資料表new] AS (

--找出 [主管] 為 某人的資料當作 依據

SELECT [主管], [員工]

FROM [員工資料表]

WHERE [主管] = 'XXXX'



UNION ALL



--之後以 上面查出的結果 為依據遞迴查詢

SELECT e.[主管], e.[員工]

FROM [員工資料表] e

INNER JOIN [員工資料表new] ecte ON ecte.[員工] = e.[主管]

)

SELECT *

FROM [員工資料表new]

參考網站: [SQL] 遞迴的寫法

想到的先列出來.

1. 幫 String 擴充屬性(javascript, .NET)
2. SQL Injection.
3. Cross Site Script Attack.
4. 避免排程未完全執行以及重覆執行.
5. 更新 Cache 與 Lock.
6. SQL Transaction.