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!!!
Wednesday, May 28, 2014
Friday, May 23, 2014
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!
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!
Wednesday, May 14, 2014
Countdown
T minus two days until the test, lots of practice in sandbox today, and lots of XML tomorrow. Cheers all!
Tuesday, May 6, 2014
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!!!
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!!!
Monday, May 5, 2014
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!
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!
Saturday, May 3, 2014
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
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
Friday, May 2, 2014
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;
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
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
Thursday, May 1, 2014
May 1st, Date Set!
After a long while, I'm making another post to update myself where I am currently. A while back I posted a picture of some flash cards that I was using for chapter 12 I believe (the heart of SQL transactions). Since then I've read through chapter 13, and am almost done with 14. Stored procedures, UDF's were some of the things that were covered during these chapters, and as I alluded too before, what I feel like is my bread and butter as far as writing TSQL goes.
As far as the test taking goes, I'm so happy that a lot of the folks on born to learn have taken and completed the test, it totally inspires me to do the same! I know that the training kit allows for "self paced" learning, but I feel like I've been studying for years! Partially it's me, not thinking that I understand the material well enough, and want to have an even firmer handle on it then I do, and part of it is the fact that in my daily job (which is somewhat seasonal, spring being the busiest season) I've been massively slammed. I'm learning all of the SQL material on my own which forces me to prioritize my daily routine. Things as simple as getting hair cut have been put off recently (which I will be doing today)!
None the less I have set a date to take the test May 16th! My plan of action is to finish reading the book by next Friday, and make flash cards for the conceptual pieces of it. Then work hard on sand box work over the course of the following week until I take the test Friday the 16th. I honestly think that I could pass the test today, but ensure that I WILL pass it the 16th I'm going to do a lot of reviewing topics, and scripting TSQL.
I think it would be healthy for me to spend time writing down what I've learned in the last few chapters since I last posted, so, that would be 11, 12, 13, and now 14 so that I have them for my own reference as I review in the next two weeks.
Cheers!
As far as the test taking goes, I'm so happy that a lot of the folks on born to learn have taken and completed the test, it totally inspires me to do the same! I know that the training kit allows for "self paced" learning, but I feel like I've been studying for years! Partially it's me, not thinking that I understand the material well enough, and want to have an even firmer handle on it then I do, and part of it is the fact that in my daily job (which is somewhat seasonal, spring being the busiest season) I've been massively slammed. I'm learning all of the SQL material on my own which forces me to prioritize my daily routine. Things as simple as getting hair cut have been put off recently (which I will be doing today)!
None the less I have set a date to take the test May 16th! My plan of action is to finish reading the book by next Friday, and make flash cards for the conceptual pieces of it. Then work hard on sand box work over the course of the following week until I take the test Friday the 16th. I honestly think that I could pass the test today, but ensure that I WILL pass it the 16th I'm going to do a lot of reviewing topics, and scripting TSQL.
I think it would be healthy for me to spend time writing down what I've learned in the last few chapters since I last posted, so, that would be 11, 12, 13, and now 14 so that I have them for my own reference as I review in the next two weeks.
Cheers!
Subscribe to:
Posts (Atom)