Exam 70-461: Querying Microsoft SQL Server 2012

Resources to help when sitting the 70-461: Querying Microsoft SQL Server 2012 Certification Exam.

This is the overview page of the Exam. I strongly suggest going over the Skills Measured section. Ensure you have a good understanding of them. Including all the different options for Triggers, XML, Indexed Views etc.
Exam 70-461 Querying Microsoft SQL Server 2012

The Training Kit book is a great reference resource. In the front it highlights the skills measured and which chapters focus on those. One word of warning, the practice test that comes with the book is far easier than the actual exam. Use it as a guide on how the questions will be asked.
Amazon - Training-Kit-Exam-70-461-Microsoft

Microsoft 70-461 Test Preparation - This is a playlist of 10 videos which will give you a good overview. The videos dont cover everything but its a great start. I suggest even if you are not planning on taking the Exam its a good set of videos to watch to improve your knowledge. 
Playlist of 10 Videos on You Tube

MCSA Certification Prep | Exam 461: Querying Microsoft SQL Server 2012 - This is from Microsoft to give you an overview and tips on what to study on
Microsoft Exam Preperation Guide

What's New in SQL Server 2012 (Part 2 of 13) - New Transact-SQL Enhancements - New Features of SQL 2012 which will of course be asked about during the exam
New Features of SQL 2012 - Querying

The Microsoft Virtual Academy has some good videos as well. 
Querying Microsoft SQL Server 2012 Databases Jump Start

Microsoft Certification Offers - there can be some great discounts or double shot offers that you can take advantage of

Microsoft Certification Offers

My tip would be if you are unsure of an answer rule out what is not right. I found I could easily rule out at least half of the answers by looking for obvious wrong answers. For example if it asks for columns A, B & C make sure all options give this columns, it could be something as simple as that to narrow down the options and if you still don't know at least you will have a higher chance when guessing.

SQLCLR

I found this a useful article on SQL Server Central.
SQLCLRs are a great way of integrating SQL Server with other applications and/or to create better ways of achieving your results.
The Level 2 page is very in depth, I wish I had access to such information years ago when I first started playing with them.
My favorite SQLCLR I created was to trigger the SSRS Web Service so we can schedule and run Reports with variables set in a table. Very handy when you need hundreds/thousands of reports scheduled to run only after certain events complete etc.

http://www.sqlservercentral.com/stairway/105855/

SSMS Tools Pack

If you haven't already got SSMS Tools Pack I highly recommend it.

I use it a lot for the SQL Snippets which allows short cuts for example stf<ENTER> will return Select Top 100 FROM in the query window. All the Snippets are customisable and you create your own.

Running Custom Scripts from the Object Explorer is great too, I have scripts I want to run at a server level so I just click on the Server in Object Explorer and then just choose the script I want to run.

A lot of other really useful tools too, just makes life that little bit easier.

Sourcing Data from Active Directory

Something I've found useful in the past is being able to source user details from Active Directory.
Its nice being able to link the User Names from your systems up to actual Names of the users when providing data.
It is also handy when you want to find which users have certain security access. For example who has access to Database X.

/*
--Check if Ad Hoc Distributed Queries is Visible/Turned on
sp_configure

--Make Visible if it isnt
sp_configure 'show advanced options', 1
reconfigure

--Turn it on
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
*/

--Return All Active Directory Users

--Replace ABC.DEF with your active directory server name

select
*
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT Title, Department, Mail, DisplayName, Sn, GivenName, Cn
FROM ''LDAP://ABC.DEF'' where objectClass = ''User'' AND objectClass<>''computer'' '
)

--Find All Active Directory Groups

--Replace ABC.DEF with your active directory server name

select
*, substring(AdsPath, charindex('CN=', adspath), 300) as GroupName
,
substring(AdsPath, 0, charindex('CN=', adspath) - 1) as Domain
FROM
OPENROWSET('ADSDSOObject', 'adsdatasource;',
'SELECT AdsPath, name FROM ''LDAP://ABC.DEF'' WHERE objectCategory=''Group'' '
)

--Find Users from X Active Directory Group

--Replace 'YourADGroup' with the name of the Group you are after

--Replace ABC.DEF with your active directory server name
--Also again in the DC=ABC,DC=DEF section

select
*
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT Cn
FROM ''LDAP://ABC.DEF'' WHERE memberOf=''CN=YourADGroup,OU=Security,OU=Groups,DC=ABC,DC=DEF'' '
)

Extended Soundex

I think Soundex is great to be able to compare two names and find possible matches. One of the limitations though that I have found is that it will only return 4 characters which can mean it only checks the start of the name. This can be good when you are comparing two names and one has been shortened but what about longer names?

Anyway so I created the below one day to help out someone when they wanted to compare names and the traditional soundex wasnt enough.

It uses the same principles as Soundex but it searches through all of the name.

Wikipedia has a good explanation of how the traditional Soundex works.

Of course this is just a basic example, Id suggest to use it for larger pieces you place it into a User Defined Table Function perhaps Or a Stored Procedure which accepts tables - I believe you can do that now but haven't tried it myself yet.

--Create my Name List
IF OBJECT_ID('tempdb..#NameList') IS NOT NULL 
DROP TABLE #NameList
CREATE TABLE #NameList (ID INT IDENTITY, Surname varchar(120), GivenNames varchar(120), GivenNameLength int)
INSERT INTO #NameList (Surname, GivenNames, GivenNameLength)
Select 'Dummy 'Surname, 'Robert William Francis' as GivenNames, 22 as GivenNameLength
Declare @NameID as int = 1
Declare @Letter as int = 1

Declare @GivenNameLength as int
Declare @LetterToCheck as varchar(1)
Declare @SoundexValue as varchar(50)
Declare @Names as bigint = 0
DECLARE @ReturnList TABLE (Surname varchar(120), GivenNames varchar(120), SoundexValue varchar(50))
--Determine the number of Names to check
Select @Names = COUNT(*) from #NameList
while @NameID <= @Names
 begin 
 --The first letter of the Name is put straight in, grab the length to know how many loops to perform
 select @GivenNameLength = GivenNameLength, @SoundexValue = LEFT(GivenNames, 1)  
 from #NameList
 where ID = @NameID
 Select @Letter = 2
 --For each letter in the Name assign a Soundex Value
 while @Letter <= @GivenNameLength 
  begin
  select @LetterToCheck = UPPER(SUBSTRING(GivenNames, @Letter, 1)) from #NameList
  where ID = @NameID
 
  --Assign Soundex Values
  select @SoundexValue = @SoundexValue + case when @LetterToCheck IN ('b', 'f', 'p', 'v') then '1'
     when @LetterToCheck IN ('c', 'g', 'j', 'k', 'q', 's', 'x', 'z') then '2'
     when @LetterToCheck IN ('d', 't') then '3'
     when @LetterToCheck IN ('l') then '4'
     when @LetterToCheck IN ('m', 'n') then '5'
     when @LetterToCheck IN ('r') then '6'
     --when @LetterToCheck IN ('h', 'w') then 'hw'
     when @LetterToCheck IN ('a', 'e', 'i', 'o', 'u') then 'v'
     else '' end
  Select @Letter = @Letter + 1
  end
  --Return a record
  INSERT INTO @ReturnList
  select Surname, GivenNames, 
  --If there are two letters with the same number next to each other return only 1
  --Unless there is a vowel in which case they remain
  REPLACE( 
  REPLACE(
  REPLACE(
  REPLACE(
  REPLACE(
  REPLACE(
  Replace(@SoundexValue
  , '11', '1') 
  , '22', '2') 
  , '33', '3') 
  , '44', '4') 
  , '55', '5') 
  , '66', '6') 
  , 'v', '') as NewSoundexValue
  from #NameList
  where ID = @NameID
 select @NameID = @NameID + 1
 end
SELECT * FROM @ReturnList

Creating a Calendar View

I like to put commonly used scripts into Views/Stored Procedures etc. It means I dont have to re-write out the logic each time, I know that there has been a consistent use of the logic and if there is a change needed to the logic I make it once rather than many times across all of my scripts/reports etc. Yeah seems obvious to do but Ive seen a lot of developers who dont bother.

Now this is view is related of course to my previous post Creating A Calendar Table but I find that having an independent view that you can use in Reports or reused SQL Scripts very helpful.

What better way to set default date values than referring to a consistent view AND if for some reason there is a change in regards to the definition of say the last day of the Previous Month - maybe it is now the last Monday for example instead of the last actual day you can change it within the view once and all of your reports/scripts are instantly updated. No more having to go through them all updating the logic.

Depending on where you are located you may want to change around a few of these values for example the Financial Years which wont apply to all.

-- =============================================

-- Create View template

-- =============================================

USE

ABC

-- Adjust accordingly

GO

IF

object_id(N'dbo.vDateDetails', 'V') IS NOT NULL

DROP VIEW dbo.vDateDetails

GO

CREATE

VIEW dbo.vDateDetails AS

SELECT

getdate

() AS [Now]

,

DATEADD(d, DATEDIFF(d,0,getdate()), 0) as Today

,

DATEADD(d, DATEDIFF(d,0,getdate()), -1) as Yesterday

,

CONVERT (varchar, getdate(), 112) As IntDate

,

DAY(getdate()) As [DayOfMonth]

,

DATEPART(dayofyear, getdate()) as [DayOfYear]

,

Month(getdate()) as MonthOfYear

,

DATENAME(month, getdate()) AS [MonthName]

,

YEAR(getdate()) as [Year]

,

case when Month(getdate()) > 6 then

right(cast(YEAR(getdate()) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)

else

right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)

end as CurrentFinancialYear

,

case when Month(getdate()) > 6 then

right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)

else

right(cast(YEAR(dateadd(yy, -2, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2)

end as PreviousFinancialYear

,

case when Month(getdate()) > 6 then

right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 2, getdate())) as varchar(4)), 2)

else

right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)

end as NextFinancialYear

,

DATEPART(quarter, getdate()) as CalQuarter

,

DATEPART(week, getdate()) as WeekOfYear

,

DATEPART(weekday, getdate()) as [Weekday]

,

DATENAME(weekday, getdate()) AS [WeekDayName]

,

DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) as MondayOfWeek

,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) as LastDayOfPreviousMonth

,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) as LastDayOfCurrentMonth

,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) as LastDayOfNextMonth

,

DATEADD(mm, DATEDIFF(m,0,getdate()),0) as FirstDayOfCurrentMonth

,

DATEADD(mm, DATEDIFF(m,0,getdate())+1,0) as FirstDayOfNextMonth

,

DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) as FirstDayOfPreviousMonth

,

DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as FirstDayOfCurrentQuarter

,

DATEADD(s,-1,DATEADD(qq, DATEDIFF(q,0,getdate())+1,0)) as LastDayOfCurrentQuarter