HC的攻城師小筆記: 11月 2017

2017年11月30日星期四

[SQL] MSSQL暫存資料表

當JOIN太多資料表或使用UNION時有時效能會變得很差，語法也相對變得複雜也難閱讀，這時暫存資料表就派上用場

1.使用DECLARE @TEMPTABLE TABLE
資料存放在記憶體，速度快但不太適合存太多筆資料，生命週期為一個批次

DECLARE @TEAMPTABLE TABLE(
book VARCHAR(30),
price int,
)

INSERT INTO @TEAMPTABLE (book, price) SELECT book, price FROM books WHERE (price = 100)

SELECT * FROM @TEAMPTABLE

2.使用CREATE TABLE #TEMPTABLE
資料存放在tempdb，生命週期為多個批次，session結束後自動刪除

CREATE TABLE #TEMPTABLE(
book VARCHAR(30),
price int,
)

INSERT INTO #TEMPTABLE (book, price) SELECT book, price FROM books WHERE (price = 100)

SELECT * FROM #TEMPTABLE

另外在語法前後加上以下語法可確保#TEMPTABLE有被DROP掉

IF Object_id('tempdb..#TEMPTABLE') IS NOT NULL
BEGIN
DROP TABLE #TEMPTABLE
END

小技巧1：若有需要從TEMPTABLE回寫原資料表，且要新的ID以免重複時可使用ROW_NUMBER()增加一個新ID欄位

ROW_NUMBER() OVER(ORDER BY RowID) + (SELECT MAX(RowID) FROM book))

小技巧2：快速複製資料表結構
SELECT TOP 0 * INTO #TEMPTABLE FROM book

顯示高亮度程式碼(SyntaxHighlighter教學)

建立了Blogger來當自己的程式小筆記，既然是程式小筆記當然希望程式的範例能夠看起來像是在程式編輯器上的樣式，一個方式可以自訂CSS，好處是速度快但變化較少，另外就是使用外掛，好處是樣式漂亮，缺點是既然是使用第三方的外掛如果服務停止樣式就會失效而且會影響網頁速度，各有利弊請看倌們自己選擇囉。
google了一下找了一個覺得好設定的外掛SyntaxHighlighter，只是設定完後沒多久心血來潮改個主題樣式，改完後馬上傻眼，原來會蓋掉原先加上去的code，不過還好剛剛說了外掛還蠻好設定，一下就復原了，不過怕忘記怎麼設定，還是筆記一下免得下次又做了蠢事還要再google一次，以下為設定方式

請注意，使用此外掛在連結Blogger不能使用https，外掛會失效，以及編輯時無法預覽，建議最後再加上程式碼
以下為加入外掛步驟：
1.進入Blogger後台->主題->編輯HTML
在<head></head>之間加入以下code


<script src="https://alexgorbatchev.com/pub/sh/current/scripts/shCore.js" type="text/javascript"></script>


<script src="https://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js" type="text/javascript"></script>


<link href="https://alexgorbatchev.com/pub/sh/current/styles/shCore.css" rel="stylesheet" type="text/css"></link>
<link href="https://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css" rel="stylesheet" type="text/css"></link>


<script type="text/javascript">
SyntaxHighlighter.all()
</script>

另外要顯示不同的script要加入對應的js
<script src="https://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js" type="text/javascript"></script>
把shBrushJScript.js改成自己需要的，如果你的Blogger需要多個script就新增多個上去
以下為網站上顯示的對照表
brushes清單

2.使用HTML編輯文章加入以下Code
<pre class="brush: script名稱">
code....
</pre>
加入就會出現以下結果了

if(a==1)
{
  b=a;
}

2017年11月29日星期三

[SQL] T-SQL使用CURSOR來跑迴圈

初期在公司主要是寫ASP，DB的部分涉入不深，單純是新增或修改資料表而已，之後才慢慢開始我的資料匯入人生，在一堆資料中打滾，既然要匯入資料當然開始常操作資料庫，覺得還是要多學學T-SQL的語法，有時會需要逐筆更新資料，能夠寫迴圈當然會方便很多啦，看完這些廢話再來就是主題了

使用CURSOR來跑迴圈，以下範例

DECLARE @RowID int

DECLARE @uid varchar(10)

DECLARE @sex varchar(4)

DECLARE temp_cursor CURSOR FOR

SELECT RowID, uid FROM tempTable

OPEN temp_cursor

FETCH NEXT FROM temp_cursor INTO @RowID, @uid

@@FETCH_STATUS

0 FETCH 陳述式順利執行

-1 FETCH 陳述式失敗，或資料列超出結果集

-2 擷取的資料列已遺漏

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sex=''

IF LEN(@uid)=8 SET @sex='公司'

IF LEN(@uid)=10 AND SUBSTRING(@uid,2,1)='1' SET @sex='男'

IF LEN(@uid)=10 AND SUBSTRING(@uid,2,1)='2' SET @sex='女'

IF @sex<>''

UPDATE tempTable SET sex=@sex WHERE RowID=@RowID

FETCH NEXT FROM temp_cursor INTO @RowID, @uid

END

CLOSE temp_cursor

DEALLOCATE temp_cursor

[SQL] DBCC CHECKDB修復資料庫、CHECKTABLE修復資料表

最近公司資料庫排程一直跳出錯誤，備份資料庫無法作完整驗證、重新組織索引及重建索引都失敗，上網查詢了一下可以使用DBCC CHECKDB檢查資料庫，CHECKDB依硬碟大小及IO能力決定檢查時間，資料庫大時可能要不少時間，平日可以改用CHECKTABLE來分批檢查，沒想到一檢查下去果真出現錯誤，使用REPAIR修復的話可針對資料庫或資料表進行修復，但有資料損失的風險

1. DBCC CHECKDB

重啟伺服器後，在沒有進行任何操作的情況下，在SQL查詢分析器中執行以下SQL進行資料庫的修復，修復資料庫存在的一致性錯誤與分配錯誤。

use master
declare @databasename Varchar(255)
set @databasename='需要修復的資料庫實體的名稱'
--將目標資料庫置為單使用者狀態
exec sp_dboption @databasename, N'single', N'true'
dbcc checkdb(@databasename,REPAIR_ALLOW_DATA_LOSS)
dbcc checkdb(@databasename,REPAIR_REBUILD)
--將目標資料庫置為多使用者狀態
exec sp_dboption @databasename, N'single', N'false'

然後執行 DBCC CHECKDB('需要修復的資料庫實體的名稱') 檢查資料庫是否仍舊存在錯誤。

2. DBCC CHECKTABLE
如果DBCC CHECKDB 檢查仍舊存在錯誤，可以使用DBCC CHECKTABLE來修復。

use 需要修復的資料庫實體的名稱

declare @dbname Varchar(255)
set @dbname='需要修復的資料庫實體的名稱'
--將目標資料庫置為單使用者狀態
exec sp_dboption @dbname,'single user','true'
dbcc checktable('需要修復的資料表的名稱',REPAIR_ALLOW_DATA_LOSS)
dbcc checktable('需要修復的資料表的名稱',REPAIR_REBUILD
--將目標資料庫置為多使用者狀態
exec sp_dboption @dbname,'single user','false'

3. 其他的一些常用的修復命令

DBCC DBREINDEX 重建指定資料庫中表的一個或多個索引

用法：DBCC DBREINDEX (表名,’’) 修復此表所有的索引

不過修復資料庫有損失資料的風險
1.如果是修復資料庫可以先卸離資料庫備份一份後再進行操作
2.如果是修復資料表的話可以先把資料表匯出可存取的資料至新的資料表，再用DBCC CHECKTABLE檢查是否還有錯誤，若沒有錯的話可以將舊資料表刪除後，把新資料表改回原資料表名稱

2017年11月30日 星期四