SQLite 查詢忽略字母大小寫 COLLATE NOCASE

在MS SQL Server上，預設查詢時會自動忽略了英文大小寫差異，
但SQLite預設將大小寫視為不同結果，
但可以利用在來欄位名稱後加上COLLATE NOCASE以便忽略大小寫差異...

select *
from TableName
where AccNo='Dean' COLLATE NOCASE

參考自
sqlite查询忽略大小写，你踩坑了吗？

SQLite 多資料庫查詢

一個sqlite檔案唯一個database，如果需要讀取多個資料庫，
可以使用ATTACH DATABASE的指令，掛載其他資料庫...

--附加資料庫
ATTACH DATABASE 'C:/123.db' As 'Tmp_db';

--表格寫入附加資料庫
insert into Tmp_db.Company
select *  
from Company

--查詢附加資料庫表格
select *  
from Tmp_db.Company

--解除附加資料庫
DETACH DATABASE 'Tmp_db';

參考自
SQLite 附加数据库
[Sqlite3] 多個Sqlite資料庫結合使用

[MS SQL Server] 還原資料庫備份到同一台主機

目前工作環境的MS SQL Server有做
每小時 交易記錄備份
每天 差異備份
每週 完整備份

先前有測試過將資料庫直接恢復到特定時間是沒有問題的，
不過今天卻小小卡關一下，因為常見的是原資料庫利用備份檔還原，
或者在其它資料庫主機做同樣資料庫名稱的備份還原動作！

但是如果是在同一台主機，要將原本備份的資料還原到另外一個資料庫呢？
假設原本資料庫叫DB_AA，想要在同主機上新建一個DB_BB，
並將之前DB_AA備份的檔案回存到DB_BB上

今天遇到的錯誤訊息是
"備份組包含現有的 'XXX' 資料庫以外的資料庫備份。"

[T-SQL] 表格欄位別名

我們常在抓取資料時，會針對欄位作別名的動作，
特別是有計算欄位的部分，常見的用法如下

select CorpNo as FieldNa
from Company

select FieldNa=CorpNo
from Company

今天看到一篇分享文，覺得這方法也不錯，順便筆記一下

select *
from (select CorpNo
           from Company) T (FieldNa)

參考自
[SQL] 衍伸資料表欄位別名

[T-SQL] 月份第一天、月份最後一天

--月份第一天
select N'月份第一天'=DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) 

--月份最後一天
select N'月份最後一天'=DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0))


--RESULT
月份第一天
-----------------------
2015-01-01 00:00:00.000


月份最後一天
-----------------------
2015-01-31 00:00:00.000

[MS SQL] MERGE 同步處理兩個資料表

今天研究了一下MS SQL SERVER 2008才新增的MERGE功能，
主要簡化了比對兩個表格差異處時的新增、修改、刪除動作，
以前需要分開撰寫INSERT、UPDATE、DELETE語法，
透過MERGE比對資料，根據設定更新資料！

[MS SQL] 在VIEW使用ORDER BY

透過VIEW查詢Table可以節省很多重複語法編寫的問題，
不過如果要在VIEW中使用ORDER BY，
建立VIEW會出現 下列錯問訊息
除非同時指定了 TOP 或 FOR XML，否則 ORDER BY 子句在檢視表、內嵌函數、衍生資料表、子查詢及通用資料表運算式中均為無效。

這時只要在SELECT後面加上TOP (100) PERCENT，即可排除錯誤！

SELECT TOP (100) PERCENT *
FROM TABLENAME
ORDER BY FIELDNAME

MSDN-CREATE VIEW (Transact-SQL)

[MS SQL] XML資料型態

之前寫過一篇 MS SQL 利用Trigger紀錄Table異動資料，
雖然可以記錄每次異動的資料，但缺點就是每個table欄位不同，
就要將每個要記錄的table分開新建tablex來記錄，感覺似乎也不太適合大量使用，
後來有用到XML STUFF，發現XML格式也可適用此問題，來達到記錄異動資料的需求，
好處就是不用考慮每個表格欄位數的多寡，一個欄位就可以達到記錄目的！

[MS SQL] 查詢包含特定字串之Proc、Function、View

--查詢包含特定字串之Proc、Function
DECLARE @TmpStr NVARCHAR(MAX)='Factory=@CorpNo'

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@TmpStr+'%'
AND ROUTINE_TYPE ROUTINE_TYPE IN ('PROCEDURE','FUNCTION')

--查詢包含特定字串之View
SELECT TABLE_NAME,VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%'+@TmpStr+'%'

參考自-青年廣論研討班-如何在SQL Server 資料庫中 找出符合你的關鍵字的Store Procedure

[Delphi][MS SQL] HOST_NAME()抓取主機名稱錯誤

前幾天寫了一個當登入失敗時，紀錄登入者的IP、主機名稱、時間...等，
今天去看LOG檔才發現，怎麼主機名稱都一樣 ，
明明在MS SQL Server Management Studio執行時都可以正常抓取，
後來才發現在Delphi內的ADO Connection設定連線時，
會將連線主機的名稱寫在Connection內的Workstation中，
而當我們使用T-SQL執行HOST_NAME()時，
反而直接抓取此參數資料，而非透過Server查詢，

[MS SQL] 查詢TABLE相關資訊

MS SQL Server內建sp_name可查詢表格的結構，
但是如果表格內有欄位描述時，使用sp_name是無法顯示出來，

[MS SQL] 修改使用中資料庫的定序

--修改前將資料庫設定為單一使用者模式(SINGLE_USER)
ALTER DATABASE DBName 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE

--修改資料庫定序
ALTER DATABASE DBName COLLATE Chinese_PRC_Stroke_CI_AS

--修改後將資料庫改回允許有權限之帳號存取模式(MULTI_USER) 
ALTER DATABASE DBName 
SET MULTI_USER

參考自
MSDN-ALTER DATABASE SET 選項 (Transact-SQL)
MSDN-將資料庫設定為單一使用者模式

[MS SQL] 字串(前中後)含有空白字元問題 LEN()、DATALENGTH()

今天在做字串資料處理時，跑處來的結果一直有問題，後來逐步測試才發現，
寫了那麼久的程式，我竟然沒有注意到，如果使用LEN()函數，
空白字元如果位於字串的後方，計算字串長度時，是會省略不計算的，

select '前面空白'=CONVERT(VARCHAR,LEN(' 123ab')),
  '中間空白'=CONVERT(VARCHAR,LEN('12 3ab')),
  '後面空白'=CONVERT(VARCHAR,LEN('123ab '))

/*----------------------------------結果----------------------------------*/
前面空白                           中間空白                           後面空白
------------------------------ ------------------------------ ------------------------------
6                              6                              5

這次需要改用DATALENGTH()計算，才可以正確抓取字串長度！

select '前面空白'=CONVERT(VARCHAR,DATALENGTH(' 123ab')),
  '中間空白'=CONVERT(VARCHAR,DATALENGTH('12 3ab')),
  '後面空白'=CONVERT(VARCHAR,DATALENGTH('123ab '))

/*----------------------------------結果----------------------------------*/
前面空白                           中間空白                           後面空白
------------------------------ ------------------------------ ------------------------------
6                              6                              6

但是有一點需要注意，DATALENGTH()是計算位元組，
當資料型態是NCHAR()、NVARCHAR()時，取得的長度會不一樣！

select 'VARCHAR'=CONVERT(VARCHAR,DATALENGTH(CONVERT(VARCHAR,'2238# '))),
  'NVARCHAR'=CONVERT(VARCHAR,DATALENGTH(CONVERT(NVARCHAR,'2238# '))),
  'NCHAR'=CONVERT(VARCHAR,DATALENGTH(CONVERT(NCHAR,'2238# ')))

/*----------------------------------結果----------------------------------*/
VARCHAR                        NVARCHAR                       NCHAR
------------------------------ ------------------------------ ------------------------------
6                              12                             60

[MS SQL] 快速刪除資料庫中所有表格內資料

最近公司SQL Server在做移轉主機動作，因為牽涉到降版(2008 R2 Express -> 2008)的問題，
無法直接備份還原資料庫到新主機，所以一直在測試複製數據的方式，
一開始很笨的每次都將資料庫整個刪掉再透過語法重新建立表格，
因為懶的慢慢測試，後來還是google到了方法～

MS SQL 使用sp_send_dbmail發送-HTML格式郵件

透過MS SQL的sp_send_dbmail可直接將查詢出的資料以HTML郵件格式發出
範例如下

--建立測試表格/資料
IF (select OBJECT_ID('tempdb..#Mail_Tmp')) IS NOT NULL DROP TABLE #Mail_Tmp
CREATE TABLE #Mail_Tmp (AccNo VARCHAR(10),
      EMail VARCHAR(MAX))
 
INSERT INTO #Mail_Tmp VALUES ('dean','dean@gmail.com')
INSERT INTO #Mail_Tmp VALUES ('phoebe','phoebe@gmail.com')

--抓取資料
IF EXISTS (select * from #Mail_Tmp)
BEGIN
 DECLARE @tableHTML  NVARCHAR(MAX) ;
 SET @tableHTML =
  N'<H1>用戶郵件帳號明細</H1>' +
  N'<table border="1">' +
  N'<th>使用者</th>'+
  N'<th>email</th>'+  
  N'</tr>' +
  CAST ( (select td=AccNo,'',
      td=EMail,''
    from #Mail_Tmp
      FOR XML PATH('tr'), TYPE
  ) AS NVARCHAR(MAX) ) +
  N'</table>'+
  N'此郵件由系統發出'
 
 EXEC msdb.dbo.sp_send_dbmail
  @profile_name='ERP_DB',--Database Mail設定檔名稱
  @recipients='dean@gmail.com',--收件者
  @copy_recipients='',--CC
  @subject='用戶郵件帳號明細',--主旨
  @body=@tableHTML,
  @body_format=HTML--使用HTML格式
END

收到郵件結果如下

參考自 MSDN-sp_send_dbmail

stored procedure IF ELSE SELECT INTO 出現 [訊息2714 層級16] 錯誤訊息

--範例
--建立暫存表
IF UPPER(@ServerNa) = 'SERVAER_A'
BEGIN
 IF (SELECT OBJECT_ID('tempdb..#STUL')) IS NOT NULL DROP TABLE #STUL
 select DISTINCT CorpNo,IONo into #STUL
 from [SERVAER_A].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END
ELSE IF UPPER(@ServerNa) = 'SERVAER_B'
BEGIN
 IF (SELECT OBJECT_ID('tempdb..#STUL')) IS NOT NULL DROP TABLE #STUL
 select DISTINCT CorpNo,IONo into #STUL
 from [SERVAER_B].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END

--錯誤訊息
訊息 2714，層級 16，狀態 1，程序 SP_XXX，行 24
資料庫中已經有一個名為 '#STUL' 的物件。

通常在不同的IF ELSE判斷子句中，應該可以視為獨立的事件，
不知為什麼MS SQL在stored procedure中，
使用IF ELSE針對不同的條件SELECT INTO到同樣的表格，會有這樣的錯誤訊息，
只好改寫語法，於判斷式前先將暫存表建立，再於子句中INSERT INTO即可

--建立暫存表
IF (SELECT OBJECT_ID('tempdb..#STUL')) IS NOT NULL DROP TABLE #STUL
SELECT TOP 1 CorpNo,IONo
  INTO #STUL
FROM SysTableUpdateLog

IF UPPER(@ServerNa) = 'SERVAER_A'
BEGIN
 INSERT INTO #STUL
 select DISTINCT CorpNo,IONo
 from [SERVAER_A].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END
ELSE IF UPPER(@ServerNa) = 'SERVAER_B'
BEGIN
 INSERT INTO #STUL 
 select DISTINCT CorpNo,IONo
 from [SERVAER_B].InnovaERP.dbo.SysTableUpdateLog
 where PrgType=@PrgType
END

T-SQL 比較 ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE() 次序函數差異

MS SQL中提供ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()四個次序函數供資料排序使用
簡單說明如下：
ROW_NUMBER()：資料列以流水編排序
RANK()：資料列依ORDER BY欄位排名
DENSE_RANK()：同RANK()，但不跳號
NTILE()：資料列依傳入參數作為分組組數分組排名

查詢MS SQL Server連線狀況

--列出目前連線的資訊
use master--use master系統資料庫
select c.session_id, c.connect_time,s.login_time, c.client_net_address,
s.login_name,s.status
from sys.dm_exec_connections c left join sys.dm_exec_sessions s
on c.session_id = s.session_id

參考自：
Jeff's Memo-查詢SQL Server目前連線(Connection)狀況
MSDN-sys.dm_exec_connections
MSDN-sys.dm_exec_sessions

MS SQL Server備份計畫

一直以來公司的資料庫都使用T-SQL做完整備份，一天一次，非常耗用空間，
當然這作法是不正確，但是因為一直沒有出事，
所以也讓我一直拖到現在才找時間研究該如何正確備份，
在MS SQL Server中可分為完整備份、差異備份、交易檔備份，
這次規劃將公司的備份變更為
1.每週日凌晨作一次完整備份
2.每日早上6點作一次差異備份
3.每日上班時間每小時作一次交易檔備份

判斷資料表中特定欄位是否存在

於MS SQL資料庫中可使用sys.sysobjects查詢資料表是否存在，
而sys.columns則可查詢資料表中欄位資訊，

--檢查特定資料表是否存在
select OBJECT_ID('Prod')
--或
select * from sys.sysobjects where id=OBJECT_ID('Prod')
--檢查特定資料表中特定欄位是否存在
select * from sys.syscolumns where id=OBJECT_ID('Prod') and name='ProdNo'

如果是要查詢暫存表中的相關資訊，作法如下

--檢查特定暫存資料表是否存在
select OBJECT_ID('tempdb..#Prod')
--或
select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#Prod')
--檢查特定暫存資料表中特定欄位是否存在
select * from tempdb..syscolumns where id=OBJECT_ID('tempdb..#Prod') and name='ProdNo'