In this article I am portraying my experience about Querying Microsoft SQL Server 2012 paper.

A year ago I have chosen to give this test for demonstrate my insight also enhance specialized aptitude.

 Truth be told, (April,29th 2017) I have passed this test. In the event that some intending to give this paper in future. Today I will control the best book which I have utilized for clear this paper. The book is

1. Querying Microsoft SQL Server 2012 (MCSA Exam 70-461)

2. Microsoft SQL Server 2012 T-SQL Fundamentals

You can likewise check Study materials on Microsoft site: Microsoft Exam Learning

Additionally, I am sharing sort of inquiry segment and my test Performance on it which I have in Exam.

In here, I will feature about Exam question design In here, I will feature about Exam question design 
• Total Questions: 54 
• Total time to endeavor the paper was 3 Hrs. 
• Total Marks: 1000 
• Passing Marks: 700 

The Questions in the Exam were:

– Mostly Objective Type.
– Some question can Subjective: Like you’ve to compose code, as SELECT explanation as well as View Definition, and so on.
–  And not very many were Multiple Choice.
– A few Question you’ve to get the fitting code piece and mastermind them in right arrangement.

So as to Exam is distanced in to four modules. So as to Exam is distanced in to four modules.

1. work with information
2. Troubleshoot and Optimize
3. Create Database Objects
4. Modify Data

 

1. Work with information:

it is secured it is secured

• New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT).
• FETCH-OFFSET, SEQUENCE.
• Ranking Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE).
• OVER () Window Function.
• JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS.
• APPLY Operators (CROSS APPLY versus OUTER APPLY).
• CTE and Sub-Queries.
• PIVOT, ROLLUP, CUBE and GROUPING SETS.
• Dynamic SQL.
• CASE versus ISNULL versus COALESCE.
• FOR XML RAW/AUTO/PATH [ELEMENTS].
• Implementing XML Schemas and Handling of XML information

 

2. Troubleshoot and Optimize

• Using Statistics
• Query Hints (OPTION
• Table Hints (UPDLOCK, ROWLOCK, TABLOCK, … and so on.)
• TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT).
• SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted.
• ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)
• TRY/CATCH, RAISE versus THROW.
• CURSORS (Row-Based) versus SET Based Approach.

3. Create Database Objects

• CREATE/ALTER/DROP Database/Table.
• ADD/DROP/Rename table’s Columns.
• Data Types (XML, DATETIME, SPATIAL, VARCHAR).
• Constraints (Primary Key, Unique Key, Foreign Key, NOT NULL, Check, Default).
• Computed Columns.
• Views, Indexed Views (SCHEMABINDING, COUNT_BIG (*), CLUSTERED INDEX).
• Stored Procedures.
• DML Triggers (INSERTED, UPDATED, UPDATE work) versus CHECK Constraint.
• TRIGGER for VIEWS (INSTEAD OF).
• UDF (Functions).

4. Modify Data

• Stored Procedure (with EXECUTE AS, RECOMPILE).
• MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT).
• EXCEPT versus INTERSECT.
• UNION versus UNION ALL.
• SCALAR versus TABLE Valued Functions.
• Use of APPLY with UDFs.
• VARCHAR(MAX) and. Compose ().

 

Summary

I trust this article will supportive to comprehend about the Microsoft certification. I might want to have input from my blog perusers. Your profitable criticism, question, or remarks about this article are constantly welcome.