學海無邊，書囊無底

 
SQL 2012 新增的日期或是時間有關的函數

T-SQL的程式碼是依序執行的 假設，一個預存程序C 裡面需要依序執行預存程序A、B 簡單的寫法如下   CREATE PROC sp_C
AS
 
EXEC sp_A
EXEC sp_B

OPENQUERY ( linked_server ,'query' )
linked_server

  使用T-SQL查詢excel檔案中的資料是最省時省力的，執行前須確定SQL Server是否允許使用 OPENROWSET 和 OPENDATASOURCE 進行特定分散式查詢(參考註1)。由於需投過SQL Server讀取excel檔案中的資料，因此SQL Server執行帳號需要擁有存取該檔案的權限，可參考(註2)說明調整SQL Server啟動帳號。
建立excel檔案，在檔案內容建立以下資料。接著使用不同的

我的執行環境如下
作業系統：Windows 7 (64位元)
執行環境 ：SQL Server 2008 R2 (64位元)+Office (64位元)
以下使用 ACE 引擎查詢 Excel 97-2003 檔案內容(註1)，由於執行語法的那台SQL Server環境中已安裝了OFFICE 64位元，因此在下圖可看到Microsoft.ACE.OLEDB.12.0連接。ACE 引擎是随 Office 2007 一起發布的資料庫連接组件，可同時查詢舊有的Office 97-2003與Office 2007。值得一提的是，Microsoft.ACE.OLEDB.12.0 可以查詢正在開啟的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 則不行。

當 SET ANSI_NULLS 是 OFF 時，等於 (=) 和不等於 (<>) 比較運算子並不遵照 ISO 標準。當 SET ANSI_NULLS 是 ON 時，使用 WHERE column_name = NULL 的 SELECT 陳述式會傳回零個資料列，即使 column_name 含有 Null 值，也是如此。使用 WHERE column_name <> NULL 的 SELECT 陳述式也會傳回零個資料列，即使 column_name 含有非 Null 值，也是如此。在將來的 SQL Server 版本中，ANSI_NULLS 一定會是 ON，而且明確將此選項設定為 OFF 的應用程式將會產生錯誤。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 CREATE TABLE [dbo].[myEmployees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
Sales INT NULL,
)
insert into myEmployees values
('Nancy','Sales Representative',100),
('Andrew','Sales Manager',200),
('Janet','Sales Representative',300),
('Margaret','Sales Representative',400),
('Sandy',NULL,500)

declare @numerics as table
( row_id tinyint identity(1,1) primary key, numeric_val varchar(100))
insert into @numerics(numeric_val)values ('35.2'),(' 100.7(received tylenol per RN')
,(' 120'),('100.6(@4pm)'),('92.4 orally'),('37.2(99.0)'),('35.3 C'),('-97.6')
select n.numeric_val,
try_convert(float, n.numeric_val) as nv_try_convert,
isnumeric(n.numeric_val) as nv_isnumeric, case
when try_convert(float, n.numeric_val) is not null
then abs(try_convert(float, n.numeric_val))
when left(ltrim(n.numeric_val),4) like '[0-9][0-9].[0-9]'
then abs(try_convert(float, left(ltrim(n.numeric_val),4)))
when left(ltrim(n.numeric_val),5) like '[0-9][0-9][0-9].[0-9]'
then abs(try_convert(float, left(ltrim(n.numeric_val),5))) end as nv_try_convert_case
from @numerics n

＞＞WITH RESULT SETS   為預存程序提供一個很好的功能，可明確定義資料集中各欄位型態。 避免預存程序執行過程中被變更欄位屬性。 In earlier versions of SQL server when we wished to change a column name or datatype in the resultset of a stored procedure, all the references needed to be changed. There was no simple way to dump the output of a stored procedure without worrying about the column names and data types.  

當查詢的筆數過多時，不建議將所有的資料往用戶端送。因為傳送過多的資料，會讓有限的網路頻寬影響傳送的效能。此外，SQL Server需等待使用者端程式處理完一批資料後，才能傳送下一批，導致前端程式效能不佳，也拖累SQL Server。且取得過多的資料也會讓人無法分析，大量細節資料下傳意義不大。 基於上述原因，會視需要而將結果集分頁。舉例來說，假設符合條件的資料有1000筆，而網頁上每頁所呈現的資料為25筆資料。只要查詢當下各頁時，由資料庫中取得符合的25筆資料傳至用戶端即可。不需將1000 筆資料整批傳送至前端，再由前端程式挑出25筆記錄呈現。 以下使用不同版本的SQL Server撰寫預存程序提供的分頁功能示範如何將取得的資料集給與適當的編號，並透過參數指定每頁要呈現的資料筆數 。 適用版本：SQL 2012 透過SQL Server 2012提供的新功能(OFFSET與FETCH)達到分頁效果。
OFFSET：決定從第幾行開始顯示資料
FETCH NEXT：針對資料指標的第一項提取，它會傳回結果集中的第一個資料列。用於決定你要列出幾筆 。

資料欄位中有NULL值時，需要特別小心處理
否則查詢時，通常會被遺漏此筆資料 以下簡單透過下列T-SQL語法建立一個測試資料說明
CREATE TABLE [dbo].[myEmployees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](10) NOT NULL,
[Title] [nvarchar](30) NULL,
Sales INT NULL,
)
insert into myEmployees values
('Nancy','Sales Representative',100),
('Andrew','Sales Manager',200),
('Janet','Sales Representative',300),
('Margaret','Sales Representative',400),
('Sandy',NULL,500)

同事的需求，他區紐分析的欄位在每次查詢個過程中都會異動。上網Google了依些資訊之後，發現只能透過組字串的方式達到此功能。以下簡單的使用Northwind資料庫測試結果

CREATE PROC usp_PIVO @Country NVARCHAR(15)
AS
declare @pvtheaders nvarchar(max) ,@sql nvarchar(max) --@sumsql 動態組出樞紐資料行相加

SELECT @pvtheaders=ISNULL(@pvtheaders,'')+'['+CustomerID+'],'
FROM Customers WHERE Country ='UK'
GROUP BY CustomerID
--SELECT left(@pvtheaders, len(@pvtheaders)-1)
--移除 @pvtheaders 字串 最後一位字元 ","
set @pvtheaders= left(@pvtheaders, len(@pvtheaders) - 1)

--動態PIVOT
set @sql=' SELECT * FROM (
select CustomerID,FirstName,Freight
from Orders o JOIN Employees e on o.EmployeeID=e.EmployeeID )p
pivot
(SUM(Freight)
for CustomerID in ('+ @pvtheaders +')) AS pvt'

--執行
exec sp_executesql @sql