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.

A Video Tutorial on How To Add Full-Text Search To SQL Server 2012

I'm on Chapter 6 of the Querying Microsoft SQL Server 2012 Training Kit. In order for me to learn this chapter I have to install/add Full-Text Search into my SQL Server 2012 instance. After looking around on the internet I found this nice video by DBA Presents. It shows you step by step on how to add Full-Text Search and then some.

CTEs

CTE stands for common table expression. It involves three parts:

1. Inner query
2. The name of the query and its columns
3. Outer query

Format

The structure of a CTE goes like this:

WITH <name>
AS (
  <inner query>
)
<outer query>

No Nesting

CTEs don't nest unlike derived tables. To handle multiple CTEs, you can separate them using commas:

WITH C1 AS
(
  SELECT...
  FROM T1
  WHERE...
),
C2 AS
(
  SELECT...
  FROM C1
  WHERE...
)
SELECT ...
FROM C2
WHERE ...;

Recursion support

You can do recursion with CTEs. The recursive query can have 2 or more queries usually separated by UNION ALL operator. The requirement is one of the queries in the CTE body needs to return a valid relational result. This is known as the anchor member. This gets executed once. Then one of the queries in the CTE body will need to reference the CTE name. This is the query that will be invoked repeatedly until it returns an empty set. The outer query will then represent the unified results of the anchor member along with the results of the recursive member.

WITH EmpsCTE AS
(-- This is the anchor member
  SELECT empid, mgrid, firstname, lastname, 0 AS distance
  FROM HR.Employees
  WHERE empid = 9

  UNION ALL -- separator

-- This is the recursive member
  SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
  FROM EmpsCTE AS S
    JOIN HR.Employees AS M
      ON S.mgrid = M.empid
)-- Outer query
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;

It returns the following result:

empid	mgrid	firstname	lastname	distance
9	5	Zoya	Dolgopyatova	0
5	2	Sven	Buck	1
2	1	Don	Funk	2
1	NULL	Sara	Davis	3

The EXISTS predicate

Today, I've been learning about correlated subqueries. One of the cool new things that I learned today was the EXISTS predicate. I haven't really used it before. But it is similar to the IN predicate. However, after doing some research, IN and EXISTS are quite different from each other especially when it comes from an optimization perspective.

Basically the way EXISTS works is that it accepts a subquery as input and returns true if the subquery returns at least one row, otherwise it returns false. For example:


SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.orderdate = '20070212');


The query returns customers and their company that had orders placed on February 12, 2007.

custid	companyname
5	Customer HGVLZ
66	Customer LHANT

If the subquery did not return anything, then the outer query will not return any customer.

Can be negated

The EXISTS predicate can also be negated. For example:

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.orderdate = '20070212');


The result will return customers who did not order on February 7, 2012.

custid	companyname
72	Customer AHPOP
58	Customer AHXHT
25	Customer AZJED
18	Customer BSVAR

EXISTS vs IN

As I mentioned earlier, the first question that came to mind when I was looking at the EXISTS, was how is it different from IN? Someone posted the same question at Stack Overflow which produced some interesting discussion http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql. EXISTS predicate is supposed to be faster than the IN. The main purpose of the EXISTS is to well, check if a row does exists. Thus, avoiding the counting stuff such as:

SELECT COUNT(*) FROM Table

I've actually seen a lot of the query above used at work in places where they could have used EXISTS instead. Maybe I can start introducing more EXISTS since it is supposed to be faster and simpler to write. Also the example above has to check the entire table wherein if we use EXISTS, the statement will return true as soon as a row is found.

SQL Server 2012 and SQL Server Management Studio (SSMS) after Windows 10 update

I just finished upgrading my machine to the new Windows 10 OS and I just wanted to share to all the others who are wondering if their SSMS or SQL Server 2012 install won't work, that it should work fine. One of the worries I had before deciding to upgrade was that I might have to re-install my SSMS or I might have to do some reconfiguration because I saw a post somewhere that they were having trouble connecting to their DB after they upgraded. The upgrade was pretty seamless and smooth. It took me about 30 mins. I upgraded from version 8.1 and I'm loving 10 so far. I'm rocking my SSMS and SQL Server 2012 that I installed on my Windows 8.1. So come on and jump. The water is just fine.

Notes on Sorting Data

When is order guaranteed?

If we run a simple query such as the one below, how do we know the order of the result when it comes back? 

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA';

Before, I always thought the results were ordered by the primary key by default. Boy, was I wrong. But after finding out that I wasn't the only one, I didn't feel as bad. The common misconception was the rows will always be returned in insertion order; for some, by primary key order (I belonged to that group); some assume by clustered index order. The correct assumption however is that a query has no guaranteed order unless we explicitly instruct the query by adding the ORDER BY clause.

The example query above returned the following results like this after I ran it in my machine:>

empid	firstname	lastname	city	birthmonth
1	Sara	Davis	Seattle	12
2	Don	Funk	Tacoma	2
3	Judy	Lew	Kirkland	8
4	Yael	Peled	Redmond	9
8	Maria	Cameron	Seattle	1

Although this result exactly matched the one listed in the Querying Microsoft SQL Server 2012 Training Kit, it does not mean it will be the same if we execute this in another machine. Actually, we are not guaranteed that the result will display like this if we execute this query again later at a different time in the same machine.

Factors that determine the default ordering

I tried executing the query again several times but it always returned the same result sorted like in the above table. However, according to the Training Kit, this is not guaranteed. The database engine can return the data in any order because there is no explicit instruction to return the data using a specific order. One of the factors might cause the query to return the result in this order is optimization. Perhaps, the SQL Server decided that it will return the query in this order for this period of time and there is a good chance that the result will be returned this way unless some circumstances change. Some examples of changes that may affect the database engine are:

•  changes in data distribution, 
• availability of physical structures such as indexes, 
• physical resources such as CPU and memory. 
• A change in the database version can also affect these like for example, if the database has been upgraded with service packs and patches.

The ORDER BY clause

The only way we can guarantee an ordering is by adding the ORDER BY clause to the query instruction.

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
ORDER BY city;

After ordering it by 'city', the output of the query becomes:

empid	firstname	lastname	city	birthmonth
3	Judy	Lew	Kirkland	8
4	Yael	Peled	Redmond	9
8	Maria	Cameron	Seattle	1
1	Sara	Davis	Seattle	12
2	Don	Funk	Tacoma	2

The default direction is always ASC (ascending order). We can add that to the end of the ORDER BY clause but it is optional. The DESC (descending) however needs to be explicitly specified we want the result to be sorted in descending order.

It is important to note that the result of a query with an ORDER BY clause is considered non-relational (from an ordering perspective).  This is result is also called a cursor.

Deterministic/Non-deterministic

The result above is sorted by city, but if we look closely, empid 8 and 1 have the same city (Seattle). So in this case, how did the database engine know that empid 8 will be displayed first before empid 1? In such as case, the order is considered to be non-deterministic. If the city is not unique, the query does not guarantee order among the rows with the same city. Just like before, when it did not have an ORDER BY clause, it has no guarantee on the ordering of the rows returned. 

To make the query above deterministic, we need to add more criteria to the order by clause. Here we are adding a tiebreaker by adding empid so that if the city is the same, the empid will determine the ordering.

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
ORDER BY city, empid;

Here's the output of the modified query: (deterministic)

empid	firstname	lastname	city	birthmonth
3	Judy	Lew	Kirkland	8
4	Yael	Peled	Redmond	9
1	Sara	Davis	Seattle	12
8	Maria	Cameron	Seattle	1
2	Don	Funk	Tacoma	2

Bad practice

In T-SQL the ORDER BY clause allows sorting by using the ordinal positions of the columns in the SELECT statement.

SELECT empid, firstname, lastname, city, MONTH(birthdate) AS birthmonth
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
ORDER BY 4, 1;

However, this is considered bad practice. This has been mentioned several times in the Training Kit so this practice is not recommended and probably an exam item. It's considered best practice to refer to column names and not the ordinal positions.

Another cool bit that I learned is that you can also order the resulting rows by using column elements that we're not returning in the SELECT clause. For example:

SELECT empid, city,
FROM HR.Employees
WHERE country = N'USA' AND region = N'WA'
ORDER BY birthdate;

empid	city
4	Redmond
1	Seattle
2	Tacoma
8	Seattle
3	Kirkland

However, the result would be more meaningful if we include the birthdate as part of the result.

The DISTINCT clause

If the DISTINCT clause is added to the SELECT clause, then the previous query in the above section becomes invalid. This is because the resulting rows will not be mapped to the source rows in a one-to-one relationship but rather in a one-to-many relationship. The DISTINCT eliminates  duplicates in the result set. In the example above, we were ordering the result by birthdate, but if there are employees that have distinct birthdates that belong to the same city (for example, Seattle), the query will not pick which employee to return and will fail. So if the DISTINCT case is used, we are limited in the ORDER BY list to only use elements that appear in the SELECT list.

Alias in the ORDER BY clause

Last week, I wrote a post about Logical Query Processing Order. In that post, we know that the ORDER BY gets conceptually evaluated after the SELECT statement. This means that aliases are allowed in the ORDER BY clause.

Treatment of NULLs

How are NULLs sorted? In standard SQL, NULLs are supposed sort together, however it does not dictate whether they should be sorted before non-NULLs or after. It lets the implementation decide. In SQL Server, NULL values are sorted before non-NULLs in the default ascending direction.

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE custid = 20
ORDER BY shippeddate;

After I executed the example query above from the Training Kit, I got the following result

orderid	shippeddate
11008	NULL
11072	NULL
10258	2006-07-23 00:00:00.000
10263	2006-07-31 00:00:00.000
10351	2006-11-20 00:00:00.000
10368	2006-12-02 00:00:00.000

If we want to make the NULLs sort after non-NULL values in T-SQL. We need to change the query to the one below:

SELECT orderid, shippeddate
FROM Sales.Orders
WHERE custid = 20
ORDER BY CASE
WHEN shippeddate IS NULL THEN 1
ELSE 0
END, shippeddate;

Since ORDER BY allows the use of expressions, we can use a CASE statement to assign to assign a value of "1" if the shippeddate IS NULL and "0" if it is non-NULL. This will put the non-NULLs before the NULL values and sort it using the shippedate (after the CASE statement). If I did not put the shippeddate after the END, it will still put the NULL values after, but the shippeddate will not be sorted.

In stadard SQL, there are options called NULL FIRST and NULLS LAST to conveniently control how NULLs are sorted. T-SQL does not support these functions however.

Summary

It's important to remember that a query that does not have an ORDER BY clause returns a relational result.  This does not guarantee any order and the only way to guarantee an order is to explicity  specify it using the ORDER BY clause. Therefore, a query with an ORDER BY clause returns a result set that is non-relational and it is called a cursor.

If ordering is not important in your result, it is recommended that you avoid using the ORDER BY clause as the sorting can be a very expensive process.

70-461 Exam Prep videos

I registered and scheduled my 70-461 exam today. I was able to cover a few topics today from the Querying SQL Server 2012 Training Kit . I spent most of my study time watching some exam prep videos from Youtube just to get an idea of what the exam will be like.  One of the videos was the one below by TechEd North America. This video was actually in the Microsoft 70-461 Exam page also.



It was very similar to the video below by Microsoft Learning (published in 2013).



They both give a nice overview of the topics that will be covered in the exam and gave me some tips on what areas I need to focus on for my review.

Logical Query Processing Order

One of the interesting things that I have learned during my 70-461 review is the Logical Query Processing order. I have been writing 'SELECT' statements for a while but it's only now that I really understood the order in how the statement is actually interpreted. So, a typical SELECT statement goes like this:

SELECT country, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees
FROM HR.Employees
WHERE hiredate >= '20030101'
GROUP BY country, YEAR(hiredate)
HAVING COUNT(*) > 1
ORDER BY country, yearhired DESC;

Before, I thought the main query clauses were processed in the order they were entered (also known as the "keyed-in order"):
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

No wonder I used to get the occasional Invalid column name '<insert your bad column name here>'. That's because I didn't understand the logical query processing. The conceptual interpretation order actually goes like this:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Whoa! Wait, so the SELECT statement is actually processed as the last (if no ORDER BY clause is present)?? Yes! I actually asked the same thing. Each of the steps above (phases) operates on one or more tables as inputs and the result is a virtual table that becomes that input of the next step. It's almost similar to method chaining. That explains why we get the Invalid column name error if for example, we used an alias defined in the SELECT clause in the WHERE clause. This is because the WHERE clause is actually evaluated before the SELECT clause, so it doesn't know anything about the alias yet.

Ready, Set...GO!

My name is Noel. I created this blog to document and blog my way to becoming a DBA. I'm currently a Web developer. I develop primarily in Java (GWT Framework) for my current job (Well currently, I'm assigned to work on a .NET project, so I'm currently developing in C#), and I also enjoy doing front-end development and web designing during my spare time (HTML/CSS/jQuery). Check out my past works in my portfolio page at nre4ma.com.

In my 8 years of development, I have experience in developing front-end, server-side, and database. In other words I've already seen and worked on the full-stack. So what has made me think about becoming a DBA? I have no straight answer, but I guess it started about 5-6 years ago when I first started writing SQL scripts (Oracle DB) as part of my job function. I didn't have a clue about SQL, so I basically learned it while doing the job. I was fascinated at how you can manipulate the data and the different ways of accessing it. Since then I've been kind of obsessed about data. I remember I used to write little programs/web apps that does inventory, or some kind of record keeping apps back then for myself using free DBs like MySQL.

Now, at this point in my career, I really feel like it's about the right time to really take the plunge and become a full pledge DBA. I have to be honest, but the salary that a DBA job demands is also a huge part of the reason why I want to become one. I still love being a front-end developer but I'm looking for some new challenges.

So hopefully, this blog will allow me to have a deeper understanding in the many topics that I will encounter along the way and also share my knowledge to the world and other people like me who have started their journey to become a DBA. And also, I will use this as a reference/reviewer for my certification tests.

I have decided to take the SQL Server (Microsoft) route. Basically, just because there is a higher demand for in the job market where I live. And also, their site and certification seems more organized compared to Oracle.

Right now, I'm reading the Querying Microsoft SQL Server 2012 Training Kit. I'm only in the early chapters but in my next few posts, Hopefully after a couple of months I will be ready to take the Exam 70-461. I will be sharing some topics and try to teach some of the stuff that I learned from the book.