Passing 70-461...How I did it

I finally passed the 70-461 today. This is actually my 2nd attempt. I just realized how unprepared I was when I first took the exam back in November 2015. I remember feeling a little down for a few days after I got the "FAIL" that first time. But I think that first attempt helped me a lot because it gave me an idea of what the styles of questions were like. It also allowed me to really identify my problem areas. For me, it was writing the actual T-SQL codes. I knew the concepts back then but since I was not using SQL Server everyday, I sucked when it came to writing code.

So this is what I did to prepare for this exam:

- I looked at the skills measured and the feedback from my first exam, I highlighted all the topics that I needed to work on and made sure those were my top priorities every study session.

- Write T-SQL and write it everyday. (Since I was not using SQL Server everyday, I made it a habit of writing a T-SQL everyday. Especially those involving Creating UDF objects, Views, Stored Procs, and yeah...even that dreadful XML part!). I made sure I write the code without looking at the manual. I also did try to copy some of the code from the Microsoft Press book 70-461 Training Kit. I made sure I understood what the code is doing, then eventually write it down without looking at the code. The important thing is I understood each part and what was the purpose of it.

- I got an extra book. I already had the 70-461 Training Kit book but this was not enough. I think this other book was a great supporting reference: Murach's SQL Server 2012 for developers. (And yes...I also made sure I did the coding examples in that book.)

- Katie & Emil website. (www.katieandemil.com) - they got some nice sample questions that's by far almost similar to the styles of the questions in the exam. Drag and drop sql code around, hot spots. and fill in code..etc. You should check it out.

- And yes...did I forget to mention that I had to write some T-SQL code?

- Read MSDN documentation. Make sure you know some of the basic syntax for Creating the different objects. Don't ignore those optional parts of the syntax!!!

- Watch 70-461 videos on YouTube. There are some nice videos out there by Christopher Harrison.

- I blogged about some topics that I didn't understand. (datashizzle.BlogSpot.com) Even though I know no one reads it, but I was able to use them as notes for my study anywhere. I plan on continuing to add to the blog and keep learning.

So there ya go. I wish I passed this on my first take, but I guess I just was not prepared back then. So make sure you take advantage of that Free retake! And don't worry if you failed also, just learn from it and eventually you'll get it! Don't give up! Good luck.

IIF and CHOOSE Functions

The IFF and CHOOSE functions are non-standard T-SQL functions introduced in SQL Server 2012. These functions were added to help simplify migration from Microsoft Access.

IFF Function
Think of this as an If-Else function.

Example:

DECLARE
@a SMALLINT = 7,
@b SMALLINT = 19;
SELECT IIF(@a < @b, 'true', 'false'); -- Returns 'true'
GO

This is similar to CASE statement like below:
DECLARE
@a SMALLINT = 7,
@b SMALLINT = 19;
SELECT CASE WHEN @a < @b
THEN 'true'
ELSE 'false'
END -- Returns 'true'

Although IIF may seem like less code to write in most scenarios. It's still recommended to use standard functions unless you are migrating from Microsoft Access.
Link to Microsoft Documentation: https://msdn.microsoft.com/en-us/library/hh213574.aspx

CHOOSE Function
The CHOOSE function is straightforward. It will choose from a list of options based on the index position that you provided in the first parameter.

Example:

SELECT CHOOSE(2, 'eric', 'kyle', 'stan', 'kenny'); -- Returns 'kyle'
SELECT CHOOSE(4, 'eric', 'kyle', 'stan', 'kenny'); -- Returns 'kenny'
SELECT CHOOSE(0, 'eric', 'kyle', 'stan', 'kenny'); -- Returns NULL 

Link to Microsoft Documentation: https://msdn.microsoft.com/en-us/library/hh213019.aspx

Performing Date Searches in SQL Server 2012

Searching for data that contains both a date and time component can sometimes be tricky especially if you are looking for specific dates and times. I used to always have to open my sql book or google it when I am querying for something that involves dates. But with practice and a little bit of studying, here are some of the key points that I always try to remember whenever I am in this situation:

1. Always check if there is time component in the column. If your search does not require specific time, then proceed to #2 below.
2. Eliminate the time component in your query (I will show in the examples below).
3. If you do not specify a time component in your search parameter, for example: SELECT * FROM Sales.Orders WHERE orderdate < '2015-12-05', then a zero time component is added to it when converted to a datetime value. This conversion is implicit that is why it is very easy to miss or forget. Therefore, if the date columns you are searching have non-zero time components (for example, '2015-12-05 13:58:32.833), your query will not return that row(s).

How to ignore/remove time values:

I use some of the following techniques to do this:

- Use date type:

SELECT * FROM Sales.Orders WHERE CONVERT(date, orderdate) = '2015-12-05';

This technique is probably the easiest and the one I always use. However, it is important to also remember that this only works for SQL Server 2008 and later.

- Use range of dates:

SELECT * FROM Sales.Orders WHERE orderdate >= '2015-12-03' AND orderdate <= '2015-12-05';

This technique does not really involve any functions like CONVERT, but you need to be careful with this one. If your date has a non-zero component, it will not include it. For example, you have 2015-12-05 13:58:32.833, then the query will not include that record because orderdate is <= '2015-12-05' (which gets converted implicitly to '2015-12-05 00:00:00.000'). You can either do ...AND orderdate < '2015-12-06' if you want to include all the orders for 2015-12-05.

- Search using individual date components (i.e., MONTH, DAY, YEAR)

SELECT * FROM Sales.Orders WHERE MONTH(orderdate) = 12 

AND DAY(orderdate) = 05 AND YEAR(orderdate) 2015;

This gives you some flexibility and makes your query very specific. 

- Use CAST

SELECT * FROM Sales.Orders WHERE CAST(CAST(orderdate AS char(11)) as datetime) = '2015-12-05';

The inner CAST in the WHERE clause, first converts the datetime to a char type that's only 11 characters long. Doing this will truncate the time component. Then the outer CAST() converts the result of inner cast back to datetime. Again, since it is converted back to a datetime, there will be a zero time component added to it.

- Use CONVERT function

SELECT * FROM Sales.Orders WHERE CONVERT(datetime, CONVERT(char(10), orderdate, 110)) = '2015-12-05';

Almost similar to the CAST technique. The inner CONVERT function converts the datetime to a char type that's only 10 chars long, truncating the time component, and then the outer CONVERT function converts the result back to a datetime value with the zero time component. The '110' in the inner CONVERT indicates the date and time styles. You can find more details about this in the following Microsoft documentation: https://msdn.microsoft.com/en-us/library/ms187928.aspx#Anchor_3

COALESCE vs ISNULL

The COALESCE and ISNULL are functions that can be considered as abbreviates of the CASE expression. These two functions are primarily used to address certain situations when there is a NULL value expected in the input.
The COALESCE function accepts a list of expressions and returns the first that is not NULL. If all input expressions are NULL, then it returns NULL.
The ISNULL function accepts only two expression as inputs and returns the first one that is not NULL.
To see how these 2 functions work, see the demo below:

DECLARE
@x AS VARCHAR(3) = NULL,
@y AS VARCHAR(10) = '1234567890';

SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL];


Here's the output of this code:

COALESCE	ISNULL
------------	--------
1234567890	123

Differences:
- As you can see, the COALESCE returns the value of @y variable as expected. However, you may be wondering why the ISNULL only returns the first 3 numbers of the input. The reason for this is because the ISNULL function uses the first input's type. The first input variable @x (in this case VARCHAR(3)) type will be used. The COALESCE function however uses the data type of the value that has the highest precedence (in this case, VARCHAR(10)).

- Another difference between the COALESCE and ISNULL is that COALESCE is the standard while ISNULL is not. So, aside from having limited functionality (since it only accepts 2 inputs), it is recommended to always use the standard function for code flexibility/portability.

- The result expression for ISNULL and COALESCE are also different in terms of NULLability. Therefore, it makes a difference when these functions are used for example, in computed columns. For example, when you want to copy a table using SELECT INTO and you want to have the target column(s) to be defined as NOT NULL. If the source column is defined as NOT NULL, then both expressions in the SELECT INTO ISNULL(srcCol1, 0) AS targetCol1 and/or COALESCE(srcCol1, 0) AS targetCol1 will define the target column attribute as NOT NULL. However, if the source column is defined to allow NULL. The target column will be also defined as allowing NULL if the COALESCE is used while ISNULL will define the target attribute as NOT NULL.
See example below:


SELECT
ISNULL(custid, -1) AS custid,
COALESCE(shipregion, 'NA') AS shipregion,
freight
INTO MyOrdersTest
FROM Sales.Orders
WHERE shipcountry = N'Switzerland';


After running the code above. Note that the column definition for the shipregion is defined as NULL.

Now let's try running the code where we use ISNULL for the shipregion column...


SELECT
ISNULL(custid, -1) AS custid,
ISNULL(shipregion, 'NA') AS shipregion,
freight
INTO MyOrdersTest2
FROM Sales.Orders
WHERE shipcountry = N'Switzerland';


Note the column definition for the shipregion this time is defined as NOT NULL

For more details about these functions. Check out the Microsoft documentation here: https://msdn.microsoft.com/en-us/library/ms190349.aspx

OFFSET-FETCH Notes

The OFFSET-FETCH clause gives the ability to selectively fetch only a fixed window subset of the entire result set. We can also think about it as being able to paginate through the result set. This feature is very useful for UI developers who are developing a grid type of widget or page that wants to be able to paginate through the records in their grid that are being returned from the database.

The OFFSET clause does not really require the FETCH clause in order to work. However, in T-SQL a FETCH clause requires the OFFSET to be present. Also, an ORDER BY clause is mandatory if you want to use the OFFSET-FETCH clause in your query.

Here are some examples:

USE TSQL2012;
GO


-- This will skip the first 10 rows and return the remaining rows.
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET 10 ROWS;
GO

-- This will skip the first 10 rows and return only the next 10 rows
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

-- Example of dynamic OFFSET-FETCH filtering by passing input parameters.
-- This might be similar to a UI passing in the pagesize and pagenumber so that
-- it can paginate thru the result set. (Server-side pagination).
DECLARE
@pagesize AS BIGINT = 10, @pagenum AS BIGINT = 3;

SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY orderid DESC
OFFSET (@pagenum - 1) * @pagesize ROWS
FETCH NEXT @pagesize ROWS ONLY;

-- Example if you want to filter a number of rows in arbitrary order
SELECT orderid, productid, unitprice, qty, discount
FROM Sales.OrderDetails
ORDER BY (SELECT NULL)
OFFSET 10 ROWS;
GO


THROW vs RAISERROR in TRY/CATCH Block

During some of my practice tests, I encountered questions about error handling more than once. Here are some notes about the THROW and RAISERROR when used inside the TRY/CATCH Block:

•  RAISERROR must have severity levels from 11-19 to transfer control to the CATCH block. (See: Detailed List of Severity levels). 
•  RAISERROR and THROW in the TRY block will not cause SQL Server to send back an error message back to the client. 
•  THROW with or without parameters in the CATCH block causes the batch to terminate so this needs to be the last statement to execute because any remaining commands following the THROW will not execute.
• RAISERROR in the CATCH block can return error message back to the client but cannot re-raise the original error number. A custom error message number (50000) will need to be used.
• RAISERROR statement does not terminate the batch.
• THROW with parameteres always raises errors with custom error number and severity level 16.
• THROW with parameters can also re-raise the original error message and send it back to the client.

Syntax of RAISERROR:

RAISERROR ({msg_id | msg_str | @local_variable }
{, severity ,state}
[, argument [,....n]])
[WITH option [,....n]]

Syntax of THROW:

THROW [ {error_number | @local_variable },
{message | @local_variable },
{state | @local_variable}
] [;]


Heap Allocation Check Script

Just wanted to do a quick post of a useful query for checking heap allocation:

NOTE: You can replace the dbo.TestStructure with the real table name you're using.

SELECT index_type_desc, page_count,
record_count, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID(N'tempdb'), OBJECT_ID(N'dbo.TestStructure', N'U'), NULL, NULL, 'DETAILED');
EXEC dbo.sp_spaceused @objname = N'dbo.TestStructure', @updateusage = true;

I'm currently on Chapter 15 of the Querying Microsoft SQL Server 2012 Training Kit. The first lesson talks about implementing indexes. It's definitely one of the topics that I'm still trying to get a warm-fuzzy-feeling about. Clustered and Non-clustered indexes made me dust off my old data structures book from way back in college. I plan on coming back and do a separate post about it. However, here is a nice short video presentation by Voluntary DBA from Youtube that explains it pretty well:

EXAM UPDATE:
It's been a while since my last full post. I've just been busy with family and work. I also wanted to be able to cover all the chapters from the Training Kit early so that I will have some time to go back and review. My exam is scheduled for November 2nd. So I have some time. I plan on coming back and doing more posts as I go back and review the different topics.