2013-01-31

考試心得,Exam 70-461 : Querying Microsoft SQL Server 2012


考試科目:
Exam 70-461 : Querying Microsoft SQL Server 2012

-- 01_MCSA_SQL_Server_2012



請注意:

本文是筆者與朋友整理出來的資料,僅供參考。
Microsoft 可能會隨時變更考試的內容與方式。

更新日期:2013/01/31

各位考生在參與考試時,都會簽署一份 NDA(Non-Disclosure Agreement,保密協議),對於考試的內容必須要保密。



Microsoft 建議參考的教材:

1. 課程:Course 10774A : Querying Microsoft SQL Server 2012
http://www.microsoft.com/learning/en/us/Course.aspx?ID=10774A&Locale=en-us

2. Microsoft Press Books
Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012
http://www.microsoft.com/learning/en/us/book.aspx?id=16113&locale=en-us

3. 免費電子書:Free ebook: Introducing Microsoft SQL Server 2012
http://go.microsoft.com/FWLink/?Linkid=229829



考試各項主題的配分比重:

1. Create Database Objects (24%)
2. Work with Data (27%)

3. Modify Data (24%)
4. Troubleshoot & Optimize (25%)



考試題型之分享

考試時間約:200 min。

題數約:54 題。

及格分數:700 分。

滿分:1000 分。

題型:單選、複選、拖曳、撰寫T-SQL程式碼。

1. 單選

更多選項:不只是A、B、C、D等四個選項,筆者曾遇的是:由 A 到 I 的選項。

2. 複選

多選多,但題目內未說明要選幾個答案。

3. 拖曳

拖曳各部分 T-SQL 程式碼,組合成正確的 T-SQL 程式碼。

4. 撰寫 T-SQL 程式碼

依據題目需求,實際撰寫 T-SQL 程式碼。




參考資料:

考試:Exam 70-461 : Querying Microsoft SQL Server 2012
http://www.microsoft.com/learning/en/us/exam.aspx?id=70-461

課程:Course 10774A : Querying Microsoft SQL Server 2012
http://www.microsoft.com/learning/en/us/Course.aspx?ID=10774A&Locale=en-us

MCSA SQL Server 2012 認證
http://sharedderrick.blogspot.tw/2012/09/mcsa-sql-server-2012.html

2013-01-24

SQL Server 2012:DATEFROMPARTS 從各自部分取得日期和時間值的函數


在先前 SQL Server版本上,若要從各自部分取得的資料,轉型為日期和時間值,可以使用以下的方式:

EX1. CAST 或 CONVERT 轉換日期類型,SQL Server 2012 之前的作法

01_轉成為日期格式:2016-01-02

-- 01_轉成為日期格式:2016-01-02
-- 資料類型是 VARCHAR,可以轉型為:DATE 或 DATETIME。
DECLARE @Y VARCHAR(4) = '2016'
DECLARE @M VARCHAR(2) = '01'
DECLARE @D VARCHAR(2) = '02'

SELECT CONVERT(DATE, @Y+@M+@D) AS N'DATE' 
SELECT CONVERT(DATETIME, @Y+@M+@D) AS N'DATETIME' 
GO


-- 01_資料類型是 VARCHAR,可以轉型為:DATE 或 DATETIME


 02_假若資料類型是 INT,不允許從資料類型 int 明確轉換至 date



-- 02_假若資料類型是 INT,不允許從資料類型 int 明確轉換至 date

DECLARE @Y INT = 2016
DECLARE @M INT = 01
DECLARE @D INT = 02

SELECT CONVERT(DATE, @Y+@M+@D) AS N'DATE' 
SELECT CONVERT(DATETIME, @Y+@M+@D) AS N'DATETIME' 
GO

/*
訊息 529,層級 16,狀態 2,行 5
不允許從資料類型 int 明確轉換至 date。
*/


-- 02_不允許從資料類型 int 明確轉換至 date



03_錯誤的日期資料:20130229,轉換失敗


-- 03_錯誤的日期資料:20130229,轉換失敗

DECLARE @Y VARCHAR(4) = '2013'
DECLARE @M VARCHAR(2) = '02'
DECLARE @D VARCHAR(2) = '29'

SELECT CONVERT(DATE, @Y+@M+@D) AS N'DATE' 
SELECT CONVERT(DATETIME, @Y+@M+@D) AS N'DATETIME' 
GO

/*
訊息 241,層級 16,狀態 1,行 7
從字元字串轉換成日期及/或時間時,轉換失敗。
*/

-- 03_錯誤的日期資料,轉換失敗




DATEFROMPARTS日期函數


DATEFROMPARTS (Transact-SQL)

SQL Server 2012 版本新增加。
傳回指定之年、月、日的 date 值。

語法:


DATEFROMPARTS ( year, month, day )


引數:
(1) year:指定年份的整數運算式。

(2) month:指定月份的整數運算式,從 1 到 12。
(3) day:指定日期的整數運算式。

注意事項:
DATEFROMPARTS 傳回 date 值,其中日期部分會設為指定的年、月、日,而時間部分則會設為預設值。
如果引數無效,將會引發錯誤。

如果要求的引數為 null,即會傳回 null。

函數可以在 SQL Server 2012 伺服器和更新版伺服器上以遠端方式進行。
它在版本低於 SQL Server 2012 的伺服器上無法以遠端方式運作。



EX1. DATEFROMPARTS -- 有效的日期:2016-01-02



-- EX1. DATEFROMPARTS -- 有效的日期:2016-01-02
-- 輸入整數資料類型,回傳 DATE

DECLARE @Y INT = 2016
DECLARE @M INT = 1
DECLARE @D INT = 2

SELECT DATEFROMPARTS(@Y, @M, @D) AS N'DATE' 
GO

-- 04_DATEFROMPARTS -- 有效的日期:2016-01-02


EX2. DATEFROMPARTS -- 無效的日期:錯誤 2013-02-29


-- EX2. DATEFROMPARTS -- 無效的日期:錯誤 2013-02-29
-- 輸入整數資料類型,回傳 DATE

DECLARE @Y INT = 2013
DECLARE @M INT = 02
DECLARE @D INT = 29

SELECT DATEFROMPARTS(@Y, @M, @D) AS N'DATE' 
GO

/*
訊息 289,層級 16,狀態 1,行 7
無法建構資料類型 date,部分引數擁有無效的值。
*/

-- 05_DATEFROMPARTS -- 無效的日期:錯誤 2013-02-29

EX3. DATEFROMPARTS -- NULL 與日期


-- EX3. DATEFROMPARTS -- NULL 與日期
-- 輸入整數資料類型,回傳 DATE
-- 回傳:NULL
-- 若是使用 CAST、CONVERT 轉換函數,也是回傳:NULL

DECLARE @Y INT = 2016
DECLARE @M INT = NULL
DECLARE @D INT = 2

SELECT DATEFROMPARTS(@Y, @M, @D)  AS N'DATE' 
GO

-- 06_DATEFROMPARTS -- NULL 與日期

EX4. DATEFROMPARTS:缺少參數,錯誤


-- EX4. DATEFROMPARTS:缺少參數,錯誤
-- 輸入整數資料類型,回傳 DATE

DECLARE @Y INT = 2016
DECLARE @M INT = 1
DECLARE @D INT = 2

SELECT DATEFROMPARTS(@Y, @M) AS N'DATE' 
GO

/*
訊息 174,層級 15,狀態 1,行 5
datefromparts 函數需要 3 個引數。
*/

-- 07_DATEFROMPARTS:缺少參數,錯誤





參考資料

從各自部分取得日期和時間值的函數
http://technet.microsoft.com/zh-tw/library/ms186724.aspx

SQL SERVER 2012 -Date and Time Function – DATEFROMPARTS()
http://raresql.com/2012/08/31/sql-server-2012-date-and-time-function-datefromparts/

2013-01-22

SQL Server 2012:EOMONTH 日期函數,傳回指定日期的當月最後一天


Excel 上的 EOMONTH 日期函數,可以用來傳回在 start_date 之前或之後指定月數的這一個月最後一天的序列值。

使用 EOMONTH 來計算剛好落在這一個月最後一天的到期日。

如今,在 SQL Server 2012 版本也提供此函數。



EOMONTH (Transact-SQL)
以選擇性位移,傳回包含指定日期的當月最後一天。

SQL Server 2012 版本新增加

語法:
EOMONTH ( start_date [, month_to_add ] )


引數:
(1) start_date
日期運算式為當月最後一天該傳回者指定日期。

(2) month_to_add
指定要加入 start_date 之月數的選擇性整數運算式。

如果這個引數是指定的,則 EOMONTH 新增當月指定數字到 start_date,還有傳回當月最後一天做為結果日期。
如果這個加法溢位有效日期範圍,則會引起錯誤。

傳回類型:date

--
備註
此函數支援啟動遠端功能到 SQL Server 2012 伺服器和上方。
其無法從遠端處理到低於 SQL Server 2012 的伺服器版本。



請參考以下的範例程式碼:

EX1. 日期時間類型與 EOMONTH 函數

-- EX1. 日期時間類型與 EOMONTH 函數
-- 傳回包含指定日期的當月最後一天
DECLARE @date DATETIME2

SET @date = '20130201'
SELECT EOMONTH ( @date ) AS N'指定日期的當月最後一天'  -- 2013-02-28
GO


-- 01_日期時間類型與 EOMONTH 函數





EX2. 字串參數及明確轉換 與 EOMONTH 函數

-- EX2. 字串參數及明確轉換 與 EOMONTH 函數
-- 傳回包含指定日期的當月最後一天
DECLARE @date VARCHAR(255)

SET @date = '20140201'
SELECT EOMONTH ( @date ) AS N'指定日期的當月最後一天'  -- 2014-02-28
GO


-- 02_字串參數及明確轉換 與 EOMONTH 函數





EX3. 具有和不具有 month_to_add 參數的 EOMONTH 函數

-- EX3. 具有和不具有 month_to_add 參數的 EOMONTH 函數
-- 計算:以目前日期為基礎,其相差特定月數的最後一天

DECLARE @date DATETIME2
SET @date = SYSDATETIME()

SELECT EOMONTH ( @date ) AS N'本月最後一天' , @date N'今天'
SELECT EOMONTH ( @date, 1 ) AS N'下一個月最後一天' , @date N'今天'

SELECT EOMONTH ( @date, -1 ) AS N'前一個月最後一天' , @date N'今天'
SELECT EOMONTH ( @date, 2 ) AS N'下兩個月最後一天' , @date N'今天'

SELECT EOMONTH ( @date, -2 ) AS N'前兩個月最後一天' , @date N'今天'
GO


-- 03_計算:以目前日期為基礎,其相差特定月數的最後一天





在 SQL Server 2012 之前版本的作法

DATEADD (Transact-SQL)
傳回指定的 date,並將指定的 number 間隔 (帶正負號的整數) 加入至該 date 的指定 datepart。

語法:
DATEADD (datepart , number , date )


引數:
(1) datepart
這是 integernumber 要加入其中的 date 部分。

(2) number
這是可解析成 int (要加入至 date 的 datepart) 的運算式。
使用者自訂的變數有效。

如果您指定了含有十進位小數的值,該小數就會被截斷而且不會四捨五入。

(3) date
這是可解析成 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的運算式。
date 可以是運算式、資料行運算式、使用者自訂變數或字串常值。

如果此運算式為字串常值,它必須解析為 datetime。
若要避免模糊不清,請使用四位數年份。

--
傳回資料類型:
是 date 引數的資料類型,但字串常值除外。
字串常值的傳回資料類型是 datetime。

如果字串常值的秒數小數位數超過三個位置 (. nnn),或者包含時區時差部分,就會引發錯誤。

--
關於 datepart 引數

dayofyear 、day 和 weekday 都會傳回相同的值。

每個 datepart 及其縮寫都會傳回相同的值。

如果 datepart 是 month、date 月份的天數比傳回月份的天數多,而且 date 日期不存在傳回月份中,就會傳回傳回月份的最後一天。

例如,九月有 30 天。因此,下列陳述式會傳回 2006-09-30 00:00:00.000:

SELECT DATEADD(month, 1, '2006-08-30')
SELECT DATEADD(month, 1, '2006-08-31')


--
在 SQL Server 2012 之前版本的作法,例如:2000、2005、2008、2008 R2

需求:
計算與目前日期為基礎,其相差特定月數的最後一天

公式:
SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) + <相差特定月數>, 0) - 1 AS N'xxx月最後一天'


請參考以下的範例程式碼:

DECLARE @date DATETIME
SET @date = GETDATE()

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date), 0) - 1 AS N'本月最後一天' , @date N'今天'
SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) - 1, 0) - 1 AS N'前一個月最後一天' , @date N'今天'

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) + 1, 0) - 1 AS  N'下一個月最後一天' , @date N'今天'
SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) - 2, 0) - 1 AS N'前兩個月最後一天' , @date N'今天'

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) + 2, 0) - 1 AS  N'下兩個月最後一天' , @date N'今天'
GO


-- 04_舊版_計算與目前日期為基礎,其相差特定月數的最後一天






參考資料:

EOMONTH
http://office.microsoft.com/zh-tw/excel-help/HP005209076.aspx

EOMONTH (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/hh213020.aspx

2013-01-17

新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(2),以CUME_DIST、PERCENT_RANK、PERCENTILE_CONT和PERCENTILE_DISC視窗統計分佈函數為例


在前一篇文章中,介紹了認識「SQL視窗」、認識OVER子句、認識視窗資料分割、認識視窗框架與排序、認識SQL視窗函數、以及說明如何使用視窗彙總函數等主題。

在本次文章中,將繼續討論視窗統計分佈函數,包含有:CUME_DIST、PERCENT_RANK、PERCENTILE_CONT和PERCENTILE_DISC等主題。

認識視窗統計分佈函數

「視窗統計分佈函數(Window Distribution Functions)」用於在視窗或視窗資料分割的資料列上執行統計分析。可以不需要PARTITON BY來設定資料分割,但必須使用ORDER BY次子句來排序。而且,統計分布函數所處理的每一筆資料列皆會傳回排名值,其值域的範圍是介於0.0到1.0之間。

在SQL Server 2012版本中提供了統計分佈函數:PERCENT_RANK和CUME_DIST。也提供了「逆統計分佈函數(inverse distribution functions)」:PERCENTILE_CONT和PERCENTILE_DISC。



完整文章,請參考以下的網址:

新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(2),以CUME_DIST、PERCENT_RANK、PERCENTILE_CONT和PERCENTILE_DISC視窗統計分佈函數為例


2013-01-10

新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(1)


在先前的版本上,對於商業報表上常見的分析需求,例如:進一步的資料分群排名、「累加值(running totals)」等的統計運算,多半是利用SQL Server Reporting Services或Microsoft Excel等前台工具來完成。

前述的這些查詢需求,需以特定的資料區間為單位進行運算,而後進行逐筆的遞移來橫跨整個資料結果集,這種運算方式,因為具備逐筆滑動結果集視窗的特性,若要在後台的資料庫系統上,使用Transact-SQL來處理,這可能需要藉由數個複雜的資料操作,例如:使用資料表的「自我聯結(Self-joins) 」、暫存資料表、CTE或是「資料指標(Cursor)」等物件,進行組合出所需要的報表結果。

如今,在SQL Server 2012版本上,可以使用「SQL視窗(SQL Windows)」與「SQL視窗函數(SQL Windows Function)」的方式,來處理這類複雜的報表分析需求。

完整文章,請參考以下的網址:

新手學SQL Server 2012「SQL視窗函數(SQL Windows Function)」(1)