Preparing for 70-761- Sharing my findings

DatabaseHead

Starting off working through JSON for export and import. I already passed 461 and most of the material is review, so I am going to start with the new items, temporal tables and JSON.

Listed below is a pretty nice tutorial on JSON for MS SQL.


Part 1 through 3.

https://www.youtube.com/watch?v=FwcuI05hnM4

https://www.youtube.com/watch?v=5Zc2oct2JVo

https://www.youtube.com/watch?v=j2Uxo9sMw0A

Just a side items, to parse out individual lines using JSON, I found this script which works for me. Instead of having it come out as one row.

Note using Adventureworks 2017 for testing......

USE [AdventureWorks2017]GO
SELECT (SELECT [AddressID] ,[AddressLine1] ,[AddressLine2] ,[City] ,[StateProvinceID] ,[PostalCode] ,[SpatialLocation].ToString() as location ,[rowguid] ,[ModifiedDate] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) FROM [Person].[Address]

DatabaseHead

https://www.edx.org/course/querying-data-transact-sql

Here is an entry level course to get you going for this exam. It doesn't cover everything, but I believe it covers the first two section of the certification. I've found it pretty basic, but some of the later modules look interesting such as APPLY and some of the error trapping.

Just got done running through ~ 1.5 hours of this and WOW, Allix isn't much, Graeme seems to be a lot more knowledgable. If you develop in SQL, you'll understand what I mean.....

Either way I am going to power through, they do present some decent information, but you can tell they are 100% academia.

DatabaseHead

Just sprinkling notes throughout this thread.....

Some academic definitions you may or may not know if you are working through this.......

Deterministic functions always returns the same output result all the time it is executed for same input values. i.e. ABS, DATEDIFF, ISNULL etc.

@CPU_BUSY etc. Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.

https://blog.sqlauthority.com/2007/10/26/sql-server-deterministic-functions-and-nondeterministic-functions/

I found this to be very helpful.

DatabaseHead

Very helpful tutorial on JSON.

https://www.youtube.com/watch?v=0d26vfWWrWE

Make sure to alter your database if you don't have permissions, it's been a while.......

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 130

*** You may want to create a copy of your database if you have other projects running off of it. I don't want to be responsible for recommending a change that breaks your other projects......

For the last year or so I have been working with Teradata and Netezza.

DatabaseHead

Querying Data with Transact-SQL - Exam 70-761 Certification Training by Mark Long. This is aligned with 70-761. I would highly recommend anyone who is interested in picking up on the core exam concepts to give this a watch.

Note, you'll have to study temporal tables and JSON/XML on your own....


Here is decent site I found to help structure your studying....

https://simonlearningsqlserver.wordpress.com/2018/03/28/exam-prep-for-70-761/

It also has data in the word press site you can use to fill tables and query.

DatabaseHead

Finished up watching the JSON video again, very interesting if you are looking to get into the document db space. (It touches on some of the principals working with JSON and XML files).

With semi unstructured data coming in at an alarming rate, it's wise to shift efforts from relational databases to document / columnar databases.... (just my insights and findings).

Anyway back to it.... Hope this helps someone if they plan on getting this certification.

DatabaseHead

https://www.youtube.com/watch?v=WIg64plWH80

Solid tutorial on XML. According to the exam objectives there could be XML questions so I thought it would be worth while to refresh my knowledge.....

It doesn't cover all of the FOR XML statements, but the more important ones, RAW and AUTO, with ELEMENTS and ROOT.....

I'm going to give this a once over take detail notes and move on..... No more tonight XML kills me, which is sooooo odd, I love working with JSON.

Another good XML video for parsing and loading XML data into SQL server. Would could embed this code into another stored proc and schedule it to run out of SSMS or in my case (i've had to do this a few times), create a script task in SSIS and call these procs to ingest XML data into tabular format.....

Note, there are other enterprise tools that can handle this easier. In fact SSIS has an XML task that can handle XML data as well... I just prefer to use the script task.....

https://www.youtube.com/watch?v=vy2Nv26UzAU

mzx380

Hey DatabaseHead

I'd love to pick your brain on this cert as I'm planning to make this one of my last ones for this year! Your tips are helpful (as usual)

DatabaseHead

mzx380 wrote: »

Hey DatabaseHead

I'd love to pick your brain on this cert as I'm planning to make this one of my last ones for this year! Your tips are helpful (as usual)

This is a great course to start with.

https://www.edx.org/course/querying-data-transact-sql (my initial opinion faded once I got into the material, they improved their deliver as the course went on).

It covers quite a bit..... You can set up an Azure lab account and that way you don't have to install SSMS on your machine.

I'm looking at this as a learning experience and take my sweet ole time...... No rush at all and I may never even get to 762 lol....

This is more about self improvement than anything else.....

mzx380

hi DBH
I'm going to just go ahead and enroll. I bought a book from Microsoft Press to start right after I'm done with my next PMI cert. I've had SQL exposure for years but I never acted on getting this cert.

Thanks for the recommendation, I'm sure it will be helpful

Curious, why would you consider it a big deal to not have SQL studio locally installed?

DatabaseHead

I'll be back on track very soon, just pivoted over to a Teradata certification and passed. This is more in my immediate space, but have not lost sight of the 761 exam.....

The end game is a full remote position in BI that pays well. lol

mzx380

DatabaseHead wrote: »

I'll be back on track very soon, just pivoted over to a Teradata certification and passed. This is more in my immediate space, but have not lost sight of the 761 exam.....

The end game is a full remote position in BI that pays well. lol

Good luck man, I know you can do it.
I'm looking to start studtying for this exam in Q4 2018

Any advice you give on it will as usual be helpful

DatabaseHead

You got it... I'll continue to post out any findings that may help. Note, it might not be the most structured but I will try to organize and post anything that I feel will help with this particular certification.

DatabaseHead

mzx380 wrote: »

Curious, why would you consider it a big deal to not have SQL studio locally installed?

Just saw this.......

Swear I wasn't dodging your your question. For me it's a big deal, because I have processes set up through windows scheduler to execute each night. I have SSIS packages set to execute weekly and I can't get that from the express version of Azure I don't think. I also have weekly backups set up to save to external storage. The data I brought in a normalized took HOURS and don't want to lose it.

If all you want to do is learn some SQL and pass the exam it's fine. But I have a website that I brought up ~6 months ago that I am showing my visualizations and some other people in regards to baseball. In fact I set up some models to run through SSAS for time series forecasting (like some players averages, OPS, baseball stats) this is not automated but to get it in synch with each other took hours. I don't want to mess with that.......

Some of the features in 2017 SSMS are only available in the local version. That will change eventually and everything will be going to the cloud version.... Still fighting that off as long as possible.

mzx380

DatabaseHead wrote: »

Just saw this.......

Swear I wasn't dodging your your question. For me it's a big deal, because I have processes set up through windows scheduler to execute each night. I have SSIS packages set to execute weekly and I can't get that from the express version of Azure I don't think. I also have weekly backups set up to save to external storage. The data I brought in a normalized took HOURS and don't want to lose it.

If all you want to do is learn some SQL and pass the exam it's fine. But I have a website that I brought up ~6 months ago that I am showing my visualizations and some other people in regards to baseball. In fact I set up some models to run through SSAS for time series forecasting (like some players averages, OPS, baseball stats) this is not automated but to get it in synch with each other took hours. I don't want to mess with that.......

Some of the features in 2017 SSMS are only available in the local version. That will change eventually, and everything will be going to the cloud version... Still fighting that off as long as possible.

Wow, that's awesome. Baseball is my fav sport, and I can see how the data analysis that's such a big part of it today would bleed into your visualization project. Yes, I'm likely going to install studio on my machine and learn since that's how I did it when I was working on SQL server 2008, but I wanted to see if there were any other merits to it

Thanks for the heads up

DatabaseHead

The exam objectives are broken out in 3 groups.

Manage Data
Query Data with advanced
Programming Databases

My suggestion is to break up your study efforts into 3rd and focus on the exam objectives in the 1st one which is 45%. AND IMO the easiest of the 3 to understand.

The next one Query Data is fairly easy as well, if you can get those two down extremely well (and in relatively short order) you can then focus mainly on the last section Programming Databases. (This is where most people will get tripped up). Thankfully in my role I am coding and reviewing a ton of stored procs which gives me a huge advantage in this exam. Even though the RDBMS is Teradata and not MS SQL......

DatabaseHead

Scalar Function Link.....

https://www.youtube.com/watch?v=6BslHItOTjU

Multi Value Table Functions

https://www.youtube.com/watch?v=nCAEgNxC7nU

Deterministic vs Non Deterministic

https://www.youtube.com/watch?v=WNoTgfg3mGc

Recursive CTE's
https://www.essentialsql.com/recursive-ctes-explained/

Persisted

https://www.youtube.com/watch?v=5mH2W9myW_Q

Date Functions

https://www.itprotoday.com/software-development/t-sql-classic-date-functions

Cross and Outer Apply

https://www.youtube.com/watch?v=3Cw2fuO5TG0


Recursive CTE's

https://www.essentialsql.com/recursive-ctes-explained/

Temporal Tables

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017

DatabaseHead

Working on Stored Procedures and begin try, begin catch etc...... Working through the different ways to work with stored procedures. Calling table variables etc....

I've worked in several IT and BI departments in my career and you usually adopt their methodology when working in those environments. For instance the environment I am in every transformed structure is done via stored procedure, views are rarely used and function of any type are prohibited due to their horrible performance.

Triggers are from 1985.... I've seen them used in the form of check constraints, that's it.....

Tail end of the stretch run. Next pay cycle setting up my test date, I believe MS has buy one get 2 free takes going on now so might as well. If I fail the first I'll have an idea of what to study for more specifically..... If not and I pass bonus.

I will be rolling into 762 (I think thats the exam) I've seriously been so focused I have no idea what's next. I've been telling myself this could be the only exam I do in this track..... So we shall see....

mzx380

Hey DBH

Started my journey towards this cert last week.  In the middle of reading Itzik Ben-Gan's book and was wondering if you know of any exam simulators that can help me hone any item that I might not be so clear on.

Thanks