70-461 Tracking Thread

N2IT

This is the initial post

So far I have covered CBT nugget chapter 1 and 2

MSSMSE has been installed along wtih AdventureWorks2012

Going through some basic DDL functions Create and Drop etc. Even though this part isn't to terribly difficult I am not so hot on Views, so that will be a very nice piece to cover.

I'll keep updating as my progress continues.

Currently I am on a 3 month data project with a potential to hire so I am thinking if I can land this certification with 3 weeks left in the contract that could help assist me for the position. (That's the theory) However if that isn't the case, 3 month of T-SQL experience with the 461 certification should help me find another database project to roll into.

N2IT

Ripped out some views this evening, faded a bit before I could hit the index piece. I will follow up with that tomorrow. I'll get some experience at work as well so that's a nice perk to the position.

NotHackingYou

Hoping to start on this one soon. PM me if you want to exchange notes. I have a few years SQL Server experience and might be able to answer your questions about views and indexes (if you have any).

Zorodzai

How are you finding the CBT ? Are you using any additional study guide(s) ?

N2IT

@Carl Sounds good - I am going to make one pass through the CBT and then come back through with questions if they aren't answered. Most of the material (SO FAR) is straight forward. Thanks again and I think trading notes would be good down the road.

Zoro - CBT Nuggets (This is all so far) I may purchase an MS book but last time I did that for 433 I failed. (I didn't study as much as I should have though).

Zorodzai

N2IT - Cool, did you ever get round to completing the 70433 ? I have a copy of the MS 70433 study guide and find it rather shallow, I prefer the Joes2Pros books though that's spread over 5 volumes and can get a bit boring at times.

N2IT

Thanks for the suggestions on the Joes2Pros book. I'll give it a review.

NotHackingYou

Passed a CIW test for WGU this morning and started on this book during some downtime at work. Finished chapter one, hoping to be through chapter 3 and maybe 4 tonight.

N2IT

Went back through capture 3 again (CBT) and ran some indexes and views. I have experience running joins so the syntax isn't to bad. I like intellisense like in Excel. It makes coding so much faster.

I plan on wrapping up chapter 3 tomorrow (x2) and then hitting chapter 4. I suspect once I hit the querying piece I will start to gain some traction since I do that piece already.

***Quick question essentially I do data analyst work, do you think this cert will add value? I really don't want to be a DBA or developer, but I obviously have to reconcile data a lot with my job which requires a lot of the same development tools.

I just don't want to sell myself as a DBA, but I really do like SQL and think this could help get me on at this company. I am on a 3 month contract and besides delivering on my task I want to show I am going above and beyond the call of duty.

NotHackingYou

If you spend any time at all writing SQL code at work then this will definitely help you.

N2IT

Carl

Absolutely, mostly DML, I am usually fetching from read only tables. Universes and warehouses, not so much off of production tables. I am never creating objects, unless I am taking data from SQL server and working with it in Access and soon Visual Studio.

Thanks for the heads up!

NotHackingYou

There's still a ton you can learn about improving your skills. Stuff like CTEs CROSS APPLY ect.

Zorodzai

Learning about CTEs and MERGE has really helped me a lot with my work.....

UniqueAgEnT

^^ CTEs can be awesome,but be careful with CTEs. From my experience, most people that learn about CTEs end up over using them in the wrong situations. I have seen many situations where each CTE has a high amount of records, and then the user is joining 1 CTE to another performing aggregate functions and the performance falls apart. Simply changing the CTEs to #Temp tables improved performance 10 fold.

N2IT

Great insight thanks again gang for posting all this good information

BTW I lied. We are getting a PROD SQL server spun up for SCCM historical. We were going to go with a Shadow copy, but decided it would impact the PROD server hosting SCCM, so we are going with another PROD server with the weekly back from SCCM.

Fun times.

My teammate has started scripting powershell as well to find other asset data. It's pretty cool stuff. (So glad I left management)

N2IT

Just curious does joe2pros have practice scripts to install in MS SQL?

Also an update Created my first unique clustered index. It's pretty neat using the execution plan feature to see the optimization of your index view compared to a full blown join. I created a view including 4 joins and the performance was so much better that just running the join out of a select across the tables. I have to say database optimization is a lot of fun.

O well figured I would share

NotHackingYou

It's a lot of fun to tune a query and watch the performance gains!

Zorodzai

Thanks for the warning about the CTEs.

Yes, Joes2Pros does have a sample database (JProCo) and scripts you can download from their site. The labs are available at Companion Labs | | Joes 2 Pros | SQL Books | SQL Videos Joes 2 Pros | SQL Books | SQL Videos

N2IT

Thanks for the link!

N2IT

Made it through lesson 4 again and jumped to 7 to start to review queries. Joins with aggregates etc. It's coming back slowly, but it's not new to me. I've taken several SQL courses at the community college so it's not to bad.

I really want to get into the XML piece.

Zorodzai

How are you finding the CBT Nuggets ? Do you think that will be sufficient material to pass the exam or are you also using the MS study guide ? I have a copy of the MS study guide and, so far (am only on the third chapter), have found it to share a lot of similarities to Ben Gan's T-SQl Fundamentals book.

N2IT

Nuggets are okay because he has the visual element with his teaching. He moves quickly which is okay, because I can pause if the concept is new or he does something differently than I normally do.

I'm going to go with Joe2Pro books if they have one for 461. I'm not sure if this will be enough to be honest. I've only taken 2 MS exams. Project 2010 and T-SQL 2008 passed the first failed the second. (The MOS ones are completely differently, strictly lab)

So we will see. I am still giving myself at least another month. Due to my position a lot of what I do is in MS Access which requires some VB so I have to split time. I am also finishing up my MBA so my time is scarces. Once the MBA is finished up I should gain a lot of traction. Either way I am spending atleast 2 - 5 hours a week labbing T-SQL.

NotHackingYou

How is your progress? I am nearly done with chapter 7

Zorodzai

@Carl - That's good progress. Which materials are you using to study ?

N2IT

So far I have completed chapter 1 - 4 and 7 - 8 of the CBT nuggets. Works has been extremely busy and a lot of my attention has gone to MS Access. I'm in the middle of compiling requirements for a software management database to track licensing and versioning. This is an mid term solution until the enterprise tool set is up and going.

NotHackingYou

That is good progress! @Zorodzai, I am using the MS Press boko by Itzik.

Zorodzai

Cool, I've also got a copy of the book and am going through it. Initially I wanted to do SQL 2008 certification but have decided it might make more sense to just go for 2012

Zorodzai

Just about done chapter 5 and starting on 6. Got a bit bogged down on ROLLUP and GROUPING, the book's way of explaining it had me slightly confused. Can't wait to start on the XML stuff......

N2IT

Agreed the XML information looks very helpful in my role.

Keep it up. I plan on hitting it hard this week. I have a few resumes to write as well so I'll have to have good time management.

knownhero

N2IT wrote: »

Agreed the XML information looks very helpful in my role.

Keep it up. I plan on hitting it hard this week. I have a few resumes to write as well so I'll have to have good time management.

The XML was a good piece to learn. I had fun doing it as I was able to actually use it in the work place while I was studying. I do have some notes down on my PC about this subject, if you like I will hunt them down and copy them here.

Zorodzai

@knownhero - would most definitely love to check your notes if you manage to locate them.