Update since last try

I have another attempt at the exam coming up this Friday which fine tuning my studies to pass this time.  I need to spend more time on querie optimization and tighten up my knowledge of XML.

I know that I have covered every topic in the book because I have read the book cover to cover.  It makes me think that I have the best broad understanding someone can have about the subject matters but because I don't use SQL for eight hours every day I have a hard time applying the knowledge to specific situations that are mentioned during the test.  Anyways, not passing the second time is not a downer... it's an igniter!!!

625, not enough

So another shot at the test coming up next week.  Scored 625 on my previous attempt, which was close but no cigar.

I felt like I visualized what the test was asking much clearer in terms of the TSQL pieces that needed to be answered.  I need work on query optimization, and creating file objects according to my post test strength/weakness hand out.  When I saw this I knew exactly which questions that I had missed… because I was unsure when I answered them the first time.

That was last Friday morning, over the weekend I went to New Orleans for vacation, after catching up on work when I got back, I'm now working further on my studies.  My plan for studying this week is a lot of flash cards with definitions on the back.

Cheers!

Countdown

T minus two days until the test, lots of practice in sandbox today, and lots of XML tomorrow. Cheers all!

Chapter 16 written review

Chapter 16 dealt with cursors, and the explanation of cursors vs set based operations (kind of like in chapter 1).  It talked about loops a little more, and how a loop is more of a cursor then a set based operation, and how a set based operation can be more efficient because it can be tuned and optimized.  The things that I got from lesson 1 was that if you are going to use a cursor you must first declare it, open a statement (batch), fetch in a loop, close, then deallocate the cursor.

Lesson 2 was interesting because it talked about temp tables vs table variables.  Interesting because this was discussed at SQL Saturday a week or so ago.  Brent Ozark talked about this in his seminar "think like an engine" where he compared indexes, and brought up the idea of variable tables.  From what I learned about them from him is that they are virtually useless.  One of the lesson review questions gives choices as to when is a table variable useful (choose all), and the answers were: "when the tables are very small and the plan is trivial, when the tables are very small and the plan is nontrivial, when the tables are large and the plan is trivial"; which to me seems like, pretty much never.  Other things I learned that I thought were useful in this lesson were that if you are going to create a temp table that can be seen on a local level you use the "#" sign; such as:
CREATE TABLE #temp1
(
 col1 INT NOT NULL,
 col2 INT NOT NULL
)
But if you wanted to create one and have it be seen on a global level you use '##'; such as:
CREATE TABLE ##glob1
(
 col1 INT NOT NULL,
 col2 INT NOT NULL
)

An interesting chapter again, I will be reading the last chapter maybe tomorrow (maybe not, it's my birthday), then doing some sand box homework in the next few evenings.

Cheers!!!

Chapter 15 Written review

Chapter 15 has three sections about indexes and statistics.

Lesson 1: This lesson deals with indexes, but before it does that it talks about the different ways that data is stored.  A heap is one way that the data is stored, I think of this as a book, with a table of contents at the beginning known as a Index Allocation Map. Here is more info on heaps from the experts, I looked and stared at the diagrams (pictures) in the book for a long time as I read through this stuff.  Then there are balanced trees that have levels of roots, intermediates, and leafs(data).  Think of an upside down tree for this one, with the intermediates being the branches.  The main idea in this lesson after explaining heaps and balanced trees (b-tree?) was that you can create clustered indexes on B-Trees that can seek data (you can only have one per table), and non-clustered indexes that can point to data.  Here are a couple of important things to remember about clustered indexes vs non clustered: Rakesh Blog.  I think of them as a clustered index is great for finding and seeking data, non-clustered can help in finding data in a heap, but it can also be helpful if you need to input data since clustered indexes are not so good at that.  This lesson also dealt with indexed views, which is (as it sounds) a view with an index on it.  These can be useful in ETL transactions.

Lesson 2: Discussed SARG's (search arguments), and introduced the reader to the idea that you can use indexes when querying data and it will find your data faster.  No brainer right?  Well it also explains, when the optimizer decides to use a query or not, and where it is a good idea to put an index.  Simply put, it's best to have a clustered index on a column that is a unique identifier, or INT, make sure that it is short though so that it can easily seeked.  This lesson discusses the Where clause too, and how it can be a powerful tool in using an index during a query which makes sense if you remember the logical way that SQL will execute a query from, WHERE, group by, having, select, order by.  I will add, that of course it's not a good idea to have a function on your where clause because when a calculation is performed SQL can no longer seek to a value, it would need to perform the calculation then check each row whether or not that value matches, which is a scan by definition.  This lesson does not talk about query hints which was a little disappointing to me, I think that's in chapter 17.

Lesson 3:  Was interesting because it talked about statistics, which from what I understand is a HUGE topic.  From what I've gathered I don't think I would ever mess with the statistics generated by SQL.  The book tries to give examples of when you would want to manage or create your own statistics, but geez.. I find that VERY advanced at least for my level.  The basics of statistics is that SQL is always creating and updating statistics on the distribution of key values.

All in all this was a very informative chapters, the index stuff was good but I enjoyed learning about the heap & B-trees.

Cheers!

Chapter 14 Written Review

Chapter 14 dealt with query optimization which is a really cool topic!  I've listened to one particular podcast probably 5 times about query optimization (podcast is SQL Down Under, and the guest is Grant Fritchey).

Lesson 1: Query optimization is exactly as it sounds, it's trying to find the most optimal way of executing code.  The SQL optimizer estimates a cost on the hardware when it begins a transaction.  The cost part is only one part of about five that the SQL engine will execute before returning the result set of the query.  Here are the 5 pieces in order: T-SQL, Parsing, Binding, Optimization, Execution... google these or look at page 520 in the kit to understand these further.  SQL has a few tools to help analyze queries, Extended Events, SQL Trace, and SQL Profiler.  Note that SQL Trace, and Profiler will be deprecated in future versions of SQL, so it's better to use Extended Events.

Lesson 2: Talks about execution plans, and how to read graphical plans.  Make sure to read them from right to left, and the top down.  As I learned in the podcast it's important to remember that actual execution plans, and estimated execution plans, are still all only ESTIMATED.

Lesson 3: Was about dynamic management objects (DMOs).  DMO's are another tool for finding queries that don't run optimally.  There are more then 130 DMOs available in SS2012.  Important DMO's to remember at ones that deal with SQL Server operating system, execution-related DMOs, and Index-related DMOs.


Credit: Querying Microsoft SQL Server 2012; Itzik Ben-Gan, Dejan Sarka, Ron Talmage

Just a little recap Ch. 12 & 13 written review

So beginning with chapter 12;

Lesson 1 was about transaction commands, how SQL handles transactions, how the user is able to see where SQL is in a transaction, and locking for readers and writers.  It also talked about rollbacks, which is essentially not committing a transaction.  Lots of lingo in this chapter, hence the flash cards that I made for myself.

Lesson 2 dealt with errors (raising errors, and throwing errors), one interesting note that I found out was that Microsoft is suggesting to use the THROW statement instead of RAISERROR in new applications, and RAISERROR can't be used in SQL Server's 2014's natively compiled stored procedures.  It also talked about XACT_ABORT (note the XACT part means 'transaction'), as you might imagine this command aborts a transaction.  Lastly, I think of Try/Catch kind of like an IF statement, because if an error occurs during the try part of transaction control is passed to the catch part of the batch.

Lesson 3 Was about Dynamic SQL, and SQL injection.  I had a post on SSC a while back when I first started studying that someone commented that I needed to use dynnamic SQL, and this example from the book is kind of what I was getting at in my post:
USE TSQL2012;
GO
DECLARE @SQLString AS NVARCHAR(4000), @address AS NVARCHAR(60);
SET @SQLString = N'
SELECT custid, companyname, contactname, contacttitle, address
FROM [Sales].[Customers]
WHERE address = @address';
SET @address = N'5678 rue de l''Abbaye';
EXEC sp_executesql
@statement = @SQLString
, @params = N'@address NVARCHAR(60)'
, @address = @address;

In this example, we declare a parameter for sp_executesql command that is actually some SQL code... doing this is the best way to protect against SQL injection.  

Chapter 13

Lesson 1 is all about my fav, stored procedures!  This is simple, if you have a query like:

SELECT name

FROM customers

WHERE firstname = 'Bob';

You can create a procedure out of this, and interchange 'Bob' with a different parameter.  The code would look something like this:

USE TSQL

IF OBJECT_ID('name','P') IS NOT NULL DROP PROC findbyname

CREATE PROC findbyname

   @custname AS VARCHAR(30)

AS

BEGIN

SELECT name

FROM customers

WHERE firstname = @custname

RETURN

END;

--Now you can execute the procedure by using EXECUTE, or EXEC for short:

EXEC findbyname

   @custname = 'Bob' --Or steve, or whatever name you want

This lesson also talks about branching logics for SQL code, these branching logics are statements such as: IF/ELSE, WHILE, WAITFOR, GOTO, RETURN.  When I read through this part of the book I had to remind myself that these branching logics are great, but remember that SQL is not an object oriented language, so when you start running this while loops and what not, it's going to kill your performance.  I use a while loops a bunch for the development work that I do, but that's a totally different animal.  Just a thought on this.  Also remember that procs (stored procedures) can all other procs... again the code can get messy.

Lesson 2 dealt with Triggers.  Triggers are just like procs, but there needs to be something has to happen in order for them to be executed.  SQL supports triggers for two different kinds of events, a data manipulation event (insert, updates, and deletes), and a data definition event (creating objects).

Lesson 3 is about user defined functions.  I found this great page on stack over flow a while back that I bookmarked that talked about the differences between user defined functions, and procs... make sure to review this: difference between udf's and procs

Sources: sqlhints.com, stackoverflow, Querying Microsoft SQL Server 2012; Itzik Ben-Gan, Dejan Sarka, Ron Talmage