Going for the MCSE: Data Management and Analytics

adam9870

Hello All,

Currently I have passed the exams: Microsoft 70-620, 70-642, 70-640, 70-462. I want to hold MCSE: Data Management and Analytics for a year.

I'm starting to prepare for the exam 70-461: Querying Microsoft SQL Server 2012/2014 or 70-761 Querying Data with Transact-SQL and 70-762 Developing SQL Databases.

I use offical traing kit for 70-461 exam. I was reading chapter 1, 2, 3, 4, 5, 6, 7 8. I need to go back to XML and Full Text Search, especially Semantic Search Functions. So far, I was install SQL Server 2014 SP2 on Windows 8.1 64-bit and I create test database from the script attach to the 70-461 Traing Kit.

I was starting to prepare two month ago, but I broke off because of private issues and workload. I went back to study this week. I feel the topic is demanding. The biggest progress I make during weekends. In this weekend I read full "Chapter 8 Creating Tables and Enforcing Data Integrity".

I try to test the solutions presented in the book in a practical way. For example, I processing data from XML file using OPENXML for import data about blocked prescriptions numbers from an XML file into a database and then display the content of the selected node of XML file.

I make a notes into file at Google Docs. This is my first topic of this type. I will try to update it 2-3 times a month. If I understand correctly, for achieve MCSE: Data Management and Analytics just pass the exams:
- First way: MCSA: SQL Server 2012/2014 (70-461, 70-462, 70-463) and 70-473, 70-475, 70-464, 70-465, 70-466 or 70-467 exams.
- Second way: MCSA: SQL 2016 Database Development (70-761/70-762) and 70-473, 70-475, 70-464, 70-465, 70-466 or 70-467 exams.

Sombody currently preparing for 70-461, 70-761 or 70-762 exams? What materials do you use?

EagerDinosaur

I'm aiming for MCSE: Data Management and Analytics as well.

I already have MCSA: SQL Server 2012/2014, which involved passing 70-461. I thought it was one of the easier MS exams, the MS "Training Kit" book covered it quite well. I probably benefitted from having about 20 years general SQL Server experience, which helped guide my guesses when I hadn't studied a topic enough. I found it easier (and more job-relevant) than 462 or 463.

I've just ordered the MS book for 70-762. That exam looks like a real bargain for me. If I pass it, I will get both MCSA: SQL Server 2016 and MCSE: Data Management and Analytics.

Also, I think MS have just expanded the list of electives for the MCSE, which is nice:
https://buildazure.com/2017/02/14/mcse-data-management-and-analytics-certification/

adam9870

From end of June, I was read over 200 pages of 70-461 training kit. There are 130 pages left, I will probably read them within 2 weeks. Sometimes it is difficult to find time to study in the day after work. Sometimes they are topics over which I spend more time than I would like.
I make notes in the form of short (1-2 minutes) video recordings and blog notes. Also I make notes in google docs. In the coming months I will probably publish these notes in an orderly form.
Recently I was read and I labbing topics such as:

• Chapter 9 Designing and Creating Views, Inline Functions, and Synonyms
• Chapter 10 Inserting, Updating, and Deleting Data
• Chapter 11 Other Data Modification Aspects
• Chapter 12 Implementing Transactions, Error Handling, and Dynamic SQL

Today I have day off and I read about stored procedures and triggers.
After finishing the book we will all practice again. I have no memory for the code. I have heard that on the exam It is important knowledge of syntax. I have an idea for a database for exercises and some tasks. If you have any ideas for exercises - let me know.

@EagerDinosaur: thans for you answer. When you plan to pass 70-762?

DatabaseHead

Glad those days are over. 461 was rough.

aaronchristenson

70-464 Developing Microsoft SQL Server Databases is next on my list of exams to take. I am taking the 5 day course from New Horizons in a few weeks. We had some training vouchers at work that were about to expire so I signed up to use them. This will complete the MCSE Data Management & Analytics.

EagerDinosaur

adam9870 wrote: »

@EagerDinosaur: thans for you answer. When you plan to pass 70-762?

I passed it about a week ago, MS confirmed the MCSE about 24 hours later. 762 seemed difficult while I was taking it, but I think the scoring criteria must have been fairly generous. It was quite interesting, lots of questions about what kind of index to choose for a particular business scenario. It covers some of the same data-warehouse design issues that 70-463 covers.

adam9870

I just finished reading chapter 15 of 70-461 trainig kit (about topics such as indexes, statistics etc.)
Currently I do not feel the indexes are my strength. As for me, this is poorly explained in the book.
I watched a webcast about indexes, I have to sit down to him again after when I finish the book.

@aaronchristenson: Please let me know how it went after the exam. It may interest not only me.

@EagerDinosaur: What materials to use to prepare for 70-463? The training kit does not have good ratings on the internet. Or on what topics to focus on? On the forum someone recommended this book
https://www.amazon.com/Microsoft-Server-2008-Integration-Services/dp/0470525762
what do You think about it?
What plans after passing the MCSE?

adam9870

Today I'm done 70-461 Training Kit.
Now it's time to repeat the material, sample tasks etc.
I plan to take the exam 17 October. (Orientation)

nachodba

Good luck with your studying!

DatabaseHead

Adam good luck on your goals. Stay calm and cool and let the information that you have learned come to the fore front of your mind. You'll do fine....

adam9870

@nachodba / @ DatabaseHead - Thanks.

I spent the weekend reading Microsoft Docs about XML in SQL Server, including FOR XML AUTO, FOR XML RAW, FOR XML PATH.

I heard that in the book (Training Kit 70-461) this topic is very modest compared to what is on the exam.

I like the most FOR XML PATH because it gives you the greatest control of XML structure. In work I prepare XML fileaccording to the guidelines of another system to which my XML file will be imported.

In my plans I have to try to generate XML according to what I see everyday in the work to exchange data with national health fund.

I also found a record of a video session about XML in SQL Server from the TechEd conference.

adam9870

Interesing artice

Data type precedence (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql

adam9870

Example: how store binary data in the database (for example: .DOC file)

CREATE TABLE [dbo].[doc]
(
id int identity(1,1) not null,
"file" varbinary(max),
fileextension nvarchar (10),
filename nvarchar(255),
title nvarchar(255)
)

INSERT INTO [dbo].[doc]
(fileextension,
filename,
title,
"file"
)
SELECT 
N'doc',
N'test1.doc',
N'Lewis Hamilton wins in Singapore after Ferrari crash',
bulkcolumn
FROM OPENROWSET(BULK 'C:\test\test1.doc',
SINGLE_BLOB) AS doc;

adam9870

I am playing with Full-Text-Search.
It is very important to write queries in practice, create tables, keys, consider what columns and data types to create, how to insert long text into columns, how to insert text with an apostrophe in the middle etc.
Recently I use FTS to see if the comments are positive or negative.
I continue to work...

adam9870

I need some time before I pass the exam.
I did not sign up for exam in October.
I am devoting time to working and learning SQL Server - lately I am practicing various topics, for example: create own functions and procedures, views, choosing the right data types, 'partition by', options etc. If you have hints on what to practice - I will be happy to listen.
The amount of material is high and the exam seems difficult. I prefer to prepare well than to take the exam several times.
Current - orientation date - my 70-461 exam is: December 2017.
Greetings to the readers.

adam9870

I plan to pass the exam on January 11, 2018.

Usually, after work, I was tired and now I changed the plan of the day:
- 3.00 am - wake up call
- learning until 7 am
- 7am - preparation for work
- work from 8 am to 4pm
- 4 pm - 8 / 9pm - time for chillout

My recent notes:

-- step 1
@TRANCOUNT AS number_of_transactions_step1
-- step 2
BEGIN TRANSACTION
-- step 3
@TRANCOUNT AS number_of_transactions_step3
-- step 4
ROLLBACK TRANSACTION
-- step 5
@TRANCOUNT AS number_of_transactions_step5

XACT_ABORT is using for rollback transaction transaction when SQL statement raises a run-time error. The adoption that I see in practice: data import and rollback if any problem occurs

SET XACT_ABORT ON
SET XACT_ABORT OFF

It is possible to combine XACT_ABORT with XACT_STATE () in TRY / CATCH. XACT_STATE allows you to specify the transaction status.

I will try to post notes of topics that I work on every few days.

Links:
SET XACT_ABORT (Transact-SQL)
XACT_STATE (Transact-SQL)
BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
@@TRANCOUNT (Transact-SQL)

adam9870

Below is an example of how works SOME / ANY, ALL, IN, EXISTS

use tempdb
go

create table test_city
(CityId int identity(1,1) not null primary key,
CityName varchar(255) not null);

create table test_person
(PersonId int identity(1,1) not null primary key,
PersonName varchar(255) not null,
CityId int not null
constraint FK_test_person_CityId foreign key
references test_city(CityId));

insert into test_city
(cityName)
values ('New York'),
('Bangkok'),
('Dubai'),
('Barcelona'),
('Shanghai'),
('Hong Kong'),
('Tokyo'),
('Seoul'),
('Istanbul'),
('Kuala Lumpur');

select * from test_city

insert into test_person
(PersonName, CityId)
values
('Harold Crane', '1'),
('James Crow', '2'),
('William Gull', '3'),
('Logan Quail', '3');

select * from test_person

select cityid, cityName from test_city as tc
where not exists (select * from test_person as tp where tc.CityId = tp.CityId)

select cityid, cityName from test_city as tc
where exists (select * from test_person as tp where tc.CityId = tp.CityId)

select cityid from test_city
where cityid in (select cityid from test_person)

select cityid from test_city
where cityid = any(select cityid from test_person)

select cityid from test_city
where cityid = some(select cityid from test_person)

select cityid from test_city
where cityid = all(select cityid from test_person)

adam9870

Today I kept repeating how they work NEWSEQUENTIALID, NEWID functions and sequence objects. I wrote post on my blog about this topic. I provide an address for those interested


https://adam9870.blogspot.com/2017/12/en-sql-server-2014-sp2-surrogate-keys.html


Cheers!

aaronchristenson

I have 70-464 Developing Microsoft SQL Server Databases scheduled for Tuesday morning.

adam9870

@aaronchristenson: Great, good luck! let me know how it went.


My next post on blog.
[EN] SQL Server 2014 SP2 - how works TOP and OFFSET?
https://adam9870.blogspot.com/2017/12/en-sql-server-2014-sp2-how-works-top.html

aaronchristenson

Did not go well, I was not ready and after a holiday I should have known better. I might take it again but it will be awhile.

adam9870

@aaronchristenson: It will be fine, I'll keep my fingers crossed!
Any suggestions about topics from exams (with respecting NDA) for future candidat?
Recently, I'am sitting on the .WRITE function and RAISERROR.

15 days until the my 70-461 exam.

adam9870

I finded good artice about MERGE.
https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

In addition to MERGE, today I check how doe EOMONTH function, DATEADD, DATENAME, DATEDIFF.

14 days until the my 70-461 exam.

adam9870

I signed up for the exam officially. I chose the date 16 January 2018 because others date were unavailable.

[EN] SQL Server 2014 SP2 - how works IIF?
https://adam9870.blogspot.com/2018/01/en-sql-server-2014-sp2-how-works-iif-pl.html

So 15 days until the my 70-461 exam.

adam9870

Today I reading about cursors and optimalization, for example:
T-SQL: A Simple Example Using a Cursor - Steve Stedman
https://www.sqlcopilot.com/optimize-for-ad-hoc-workloads.html

13 days until the my 70-461 exam.

Zorodzai

All the best. Am following your journey as my team and I work with SQL and this is a track my team could benefit from.

adam9870

Something from today: foreign key must be created on a column which refers to a column that is the primary key in another table.

9 days until the my 70-461 exam.

@Zorodzai: thank you for response. I'm very pleased, but it seems to me that what I am writing about is basics of T-SQL - I have to learn a lot myself. Greetings to You and Your Team from Poland.

adam9870

I found a video about XML in SQL Server:

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

My 70-461 exam will be day after tomorrow.

adam9870

I passed 70-461 with 757/1000.
The exam was demanding. I'm confused.
There were many tasks which consisted in the fact that you had to write the code. It's good to remember the syntax of the different commands. In normal life, fortunately, there is Microsoft Docs.
Someone can recommend books to preparation for 70-463 for a beginner in the data warehousing topic?

Zorodzai

Congrats, well done on the pass

nachodba

Congrats on the pass!