[SQL] 新增大量資料 - SqlBulkCopy

最近在用一物一碼,所以會需要產生大批編號

如果使用一筆一筆insert的話, 100萬筆資料不知道要存到哪一天了

這時候建議還是直接用sql bulk insert

ex.

using (SqlConnection conn = new SqlConnection(oneGoodsCS))
{
conn.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(conn))
{
copy.DestinationTableName = "DemoTable";
DataTable dt = new DataTable("DemoTable");
dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("TraceCodeID", typeof(int));

#region sub
for (var i = 0; i < request.Count; i++)
{
var code = Guid.NewGuid().ToString().ToLower();
dt.Rows.Add(code, "traceCodeID");
}
copy.WriteToServer(dt);
#endregion
}
}

[SQL] 學習 IN和EXISTS用法的差別

引用：https://www.jianshu.com/p/f212527d76ff


引用 :https://read01.com/zh-tw/ggKA5P.html#.Xhq8usgkuUk

exist會針對子查詢的表使用索引.
 not exist會對主子查詢都會使用索引.
 in與子查詢一起使用的時候,只能針對主查詢使用索引.
 not in則不會使用任何索引. 注意,一直以來認為exists比in效率高的說法是不準確的


紀錄一下

[sql] 將特定欄位當作 column

最近遇到要把 資料庫某個欄位當作 column 使用

所以就查詢了一下,目前有兩種方式


1.使用PIVOT, 轉置table, 但是原始column name 不能跟欄位裡面的值重複, 不然會出錯

說明: 先動態取得要轉置成column的欄位, ColumnGroup , 組成需要的字串, 再用PIVOT轉置TABLE

[sql] 撈取所有DB 的 所有TABLE

最近有個需求是直接給一張table, 但卻不知道在眾多DB的哪一個DB裡面

因此股哥到列出所有DB 及其 Table 並整理如下:

[sql] 切開同一欄位用逗點隔開的資訊

遇到一個狀況，資料庫顯示為

| CODE |   FK    |
-----------------------
|  A  |   1,2,3 |

我需要轉如下才能關聯....

| CODE |  FK   |
-----------------------
|  A  |     1 |
|  A  |     2 |
|  A  |     3 |

解法 :
參考 : https://stackoverflow.com/questions/13527537/sql-query-to-split-column-data-into-rows

先建立sql function

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

然後下以下sql 句即可
select t1.Code, s.items FK
from [table] t1
outer apply dbo.Split(t1.FK, ',') s

outer apply : 說明參考 https://ithelp.ithome.com.tw/articles/10195219

deadlock 相關蒐集

開始之前先補充一下,查詢專案sql使用狀況,可以用SSMS的活動監控器

接下來就可以觀察最近使用次數最多或是最耗時的sql, 進而觀察table的使用情況


---


最近上完非同步後,認識後deadlock, 多發生於呼叫非同步方法後,結果尚未回傳,, 就又用同步的方式去要結果導致 (ex. .Result)

這次在專案log發現deadlock, 檢查後發現,卻是sql 查詢時, datareader 查詢較久導致, 所以找一些相關文獻如下, 目前推測的狀況應該就是sql查詢使用到平行查詢,才導致deadlock情況

做個紀錄

目前看到幾個解決方式 :

1.修正SQL
2.不使用平行執行計畫。
3.maxdop=1。
4.用try catch(Error 1205 is a deadlock)預防處理死結。
等等


參考內容 :
[SQL Server][Deadlock]Intra-Query Parallel Thread Deadlocks初體驗
https://dotblogs.com.tw/stanley14/2017/07/22/intra-queryparallelthreaddeadlocks

[SQL Server][DeakLock]觀察死結的工具(四)擴充事件(Extended events)
https://dotblogs.com.tw/stanley14/2017/01/28/191015


限定資料表僅能用 Table Lock
https://byronhu.wordpress.com/2011/05/18/%e9%99%90%e5%ae%9a%e8%b3%87%e6%96%99%e8%a1%a8%e5%83%85%e8%83%bd%e7%94%a8-table-lock/

[SQL SERVER]內部平行查詢死結特性
https://dotblogs.com.tw/ricochen/2016/08/25/191618

[SQL] [Error] 資料表的識別欄位其外顯值只有當使用了資料行清單且 IDENTITY_INSERT 為 ON 時才能指定。

SQL 要更新trigger 內容時

出現以下內容 :

資料表的識別欄位其外顯值只有當使用了資料行清單且 IDENTITY_INSERT 為 ON 時才能指定。

這次的原因原來是 insert 時, 想要去改動 又自動增號的ID 才會這樣

解法:
1. 明確定義要insert 的欄位
2. 不明確定義欄位，就要把自動增號的值拿掉

[SQL] 新增insert data 時遇到內容有單引號

當遇到新增、更新資料時，資料內容的字串含有單引號時

ex. insert into data values('Client's info is incomplete',....)

請把字串內的 ' , 再多加一個引號變成 '' 就可以囉~

[SQL] SQL PASS 學習

201811/29 SQL Pass

學到 :

1.null vs 空字串 vs space

2.DB 的raw log content 0 資料流解讀：

  因此可用此還原data

3.利用db log追蹤驗證下述主題的原因

查詢db log 及寫入log:

dbcc traceon 3604  ---> 允許輸出db , server level系統資訊出來

4.會使用transaction三個特點,

5.delete vs truncate

6.table variable @ vs temp table #

7.cursor vs while

[SQL] 資料庫log過大問題

近期遇到備份儘管使用差異備份依舊非常龐大

檢查之下發現,原來是因為log table

難怪差異備份沒有幫助


接下來備份log之後, 再來把該table清空,

接著遇到資料庫交易記錄檔過大,

如果需要移除交易記錄(許可下當然事先備份出來再刪除)可參考下述

[SQL] 交易記錄檔 LDF 太大 (趴兔) - 使用 DBCC Shrinkfile


這裡使用DBCC SHRINKFILE來移除


USE TESTDB
GO
ALTER DATABASE TESTDB SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TESTDB_log, 1)
ALTER DATABASE TESTDB SET RECOVERY FULL WITH NO_WAIT
GO

這樣暫時清出空間,如果再不行就要來考慮壓縮資料表了~

[SQL] sql server 定期資料庫備份

定期因為容量不足,因此把原本的完整備份調整為差異備份,

因此簡單介紹一下設定備份流程

[SQL] 倒序搜尋

如果在sql中url欄位我需要撈出下述的1551066.html就好,該如何？

ex.http://you.ctrip.com//travels/taipeicity360/1551066.html

select right([Url], charindex('/', reverse([Url]) + '/') - 1)

[SQL] SELECT INTO跟INSERT INTO的差別

轉載 :  https://goo.gl/a6PvXF

SELECT INTO可以into進去暫存的table也可以into實體table，
INSERT INTO只能into實體的table。
實體：SELECT ～ INTO table2 FROM table1
虛擬：SELECT ～ INTO #table2 FROM table1

也就是說，如果使用SELECT INTO
後面的table2如果本來就有，他就直接塞資料；
如果本來就沒有table2，他會新建一個table2，再塞資料。

加了#的意義就是，into進去後，
把Query關掉，重新開啟一個Query，table2就不見了，等於是暫存性的。

當然假如不加#的話，table2就會一直保留著。

另外SELECT INTO如果是實體用法，可以等同於：
INSERT INTO table2 (.....)
SELECT ～ FROM table1

[SQL] 小數點去除多餘0

可以使用cast as float 先轉型～

結果

[SQL] 取得當前是第幾週? 跟下一週是

 

遇到一個狀況是2017/2/1  和 20172/3 是同一週就要歸在一起

所以我必須先轉換成同一週才能比較

SQL 上使用DATENAME 函式  參考網址

DATENAME(WK, ‘2017-02-01’) 其週數會和另一個同一週

 

另外如要join 本週跟上一週資料再搭配DATEADD (datepart , number, date )即可

ex.  SELECT DATEADD(week,1,GetDate()) --當前日期加一周後的日期

[Error] EF6 違反多重性條件約束

 

如下圖, 最近遇到資料更新時,因為資料表互相關連,造成無法更新的錯誤

 

 

原因 : 我的狀態在於automapping時, 會把子model 的父層屬性一起mapping,造成ef6更新時就報掉,

因此後來將父層屬性ignore 不要一起mapping即可

[SQL] sql分頁

分頁sql參考

1.sqlserver 2012 以上－使用offset fetch next  (只是之前在百萬筆資料時，讀取最後頁面此法慢慢的QQ)

                        WITH K AS                           

                       (
                            SELECT {0}
                         FROM Data t
                            where t.IsPublish = 1 
                         {4}
                         ORDER BY {0} {1}
                         OFFSET ({2} - 1) * {3} ROWS FETCH NEXT {3} ROWS ONLY 
                        )
                        SELECT c.[ID]
                        FROM dbo.Data AS c
                        INNER JOIN K ON c.{0} = K.{0}
                        ORDER BY c.{0} {1};

2. 之前使用的 ROW_NUMBER()

                       select *
                        from (
                         SELECT
                          t.ID,
                          ROW_NUMBER() OVER ( ORDER BY  t.{0} {1} ) RN
                         FROM Data t
                         where t.IsPublish = 1 
                         {4} 
                        ) data
                        WHERE RN BETWEEN {2} AND {3} ORDER BY RN";

[SQL] 使用者 'sa' 登入失敗。原因: 帳戶已停用。

 

 

解

請確認sa 這個帳號是否有啟用

[SQL] 遇到百萬筆資料搜尋太慢怎麼辦? 加入索引(Index)

 

最近遇到資料庫主Table有快兩百萬筆資料，然後join的資料表也是百萬級，我使用EF6來做資料查詢，結果一個查詢要5~8秒，實在是不符合需求阿！！

所以最好的改善方式就是加索引，先來看下面的圖

使用熱鍵ctrl + l 叫出執行計畫，SQLServer2014 很貼心會幫我們分析一句sql，我們可以看出哪邊的會花較多的成本，並建議我們加入索引(圖上綠色部分)，真的很貼心。

 

依照建議加入索引後，再執行一次如下圖

有沒有發現，他的查詢成本變0%，同理我們在把大部分需要查詢的欄位以及常join的FK加入索引，就能很明顯的改善的查詢效能囉。

 

現在我的查詢速度又回到500ms以下，真是太令人開心了。

 

整理一下重點 :

• 常用join table, 記得幫FK加入index
• 常搜尋的欄位也記得加入index
• 注意索引是需要維護的，如果常常會CUD，要記得定期維護！

[SQL] 重灌sql server 2014 遇到資料庫無法還原

 

用很久的sql server 2008r2 終於功成身退，換成sql2014 (其實是被逼的)

然後備份2008的db ,並刪除2008 安裝2014,再還原時發生錯誤！！

 

 

答案 :

其實最原始的錯誤好像不是上圖的錯誤，這是重製版QQ，內容比較像是說xxxx無法存取，

所以我就發現舊的DATA資料夾內還有原本DB檔案，必須把他移除才能重新還原成功

提供參考