Advertisements

Archive

Posts Tagged ‘SQL Server 2016’

Preparing for Exam 70-761, Querying Data with Transact-SQL | Certification


 
This exam (70-761) will earn you MCP in SQL Server 2016 Querying Data with Transact-SQL. It is 1 out of the 2 exams to earn the “MCSA: SQL 2016 Database Development” certification. I will discuss about the other “Exam 70-762, Developing SQL Databases” in my next post.
 

So, let’s go and understand this exam, links to study material and how to prepare for this.
 

–> This exam is targeted for students or professionals who want to learn about Transact-SQL or simply T-SQL, which includes:
1. Know about SQL Server and its components
2. Write single/multi table SELECT statements, with SET and Predicate logic
3. Apply Filtering, Sorting, JOIN, etc with SQL Queries
4. Write DDL, DML SQL Statements to Store and Retrieve data to & from Tables
5. Create Views, Stored Procedures, Functions, etc
6. Use inbuilt Functions and Transform data
7. Work with SQL Datatypes, Variables, Conditions, Loops and T-SQL code/scripts
 

–> The exam is divided into multiple modules:

1. Introduction to Microsoft SQL Server 2016
    – The Basic Architecture of SQL Server
    – SQL Server on-premise and cloud-based Editions and Versions
    – Getting Started with SSMS

2. Introduction to T-SQL Querying
    – Introducing T-SQL with basic SELECT Statements
    – Understanding Sets and SET based Queries in relational DBs
    – Understanding Predicate Logic to Filter Data
    – Understanding the Logical Order of Operations in SELECT statements

3. Writing SELECT Queries
    – Writing Simple SELECT Statements, its structure and format
    – Eliminating Duplicates with DISTINCT clause
    – Using Column and Table Aliases
    – Writing Simple CASE Expressions

4. Querying Multiple Tables
    – Understanding Joins
    – Querying with Inner Joins, Outer Joins, Cross Joins and Self Joins

5. Sorting and Filtering Data
    – Sorting Data with ORDER BY clause
    – Filtering Data with WHERE clause Predicates
    – Filtering Data with TOP and OFFSET-FETCH
    – Working with Unknown/Missing Values or three-valued logic (NULL)

6. Working with SQL Server 2016 Data Types
    – Introducing SQL Server 2016 Data Types
    – Working with Character Data
    – Working with Date and Time Data
    – Working with data types conversion

7. Using DML to Modify Data
    – Inserting Data with INSERT and SELECT INTO statements
    – Modifying and Deleting Data with UPDATE, MERGE, DELETE, and TRUNCATE statements

8. Using Built-In Functions
    – Writing Queries with Built-In Functions
    – Using Conversion Functions (CAST & CONVERT)
    – Using Logical Functions
    – Using Functions to Work with NULL

9. Grouping and Aggregating Data
    – Using built-in Aggregate Functions
    – Using the GROUP BY Clause
    – Filtering Groups with HAVING

10. Using Subqueries
    – Writing Self-Contained Subqueries
    – Writing Correlated Subqueries
    – Using the EXISTS Predicate with Subqueries

11. Using Table Expressions
    – Using Views
    – Using Inline Table-Valued Functions (TVFs)
    – Using Derived Tables
    – Using Common Table Expressions (CTEs)

12. Using Set Operators
    – Writing Queries with the UNION operator (and UNION ALL)
    – Using EXCEPT and INTERSECT operators
    – Using APPLY operators (CROSS APPLY and OUTER APPLY)

13. Using Windows Ranking, Offset, and Aggregate Functions
    – Creating Windows with OVER clause with partitioning, ordering, and framing
    – Exploring Window Aggregate and Ranking Functions

14. Pivoting and Grouping Sets
    – Writing Queries with PIVOT and UNPIVOT
    – Working with Grouping Sets, CUBE and ROLLUP Subclauses
    – Using the GROUPING_ID function

15. Executing Stored Procedures
    – Querying Data with Stored Procedures
    – Creating Simple Stored Procedures
    – Passing Parameters to Stored procedures
    – Stored Procedures with output parameters
    – Working with Dynamic SQL

16. Programming with T-SQL
    – T-SQL Programming Elements (Variables, Batches, )
    – Using variables in a Dynamic SQL Statement
    – Controlling Program Flow (IF-ELSE condition, WHILE loop)
    – Working with Synonyms

17. Implementing Error Handling
    – Implementing T-SQL error handling with TRY/CATCH block
    – Implementing structured exception handling with THROW keyword

18. Implementing Transactions
    – Transactions and the database engines
    – Differences between batches and transactions
    – Controlling transactions with BEGIN, COMMIT, and ROLLBACK
    – Error handling with the CATCH block
    – Use of SET XACT_ABORT while handling Transactions


 
Microsoft official link to this exam: https://www.microsoft.com/en-in/learning/course.aspx?cid=20761

Book available as a Training Kit on Amazon, buy it.

SQL Server 2016 articles on my blog: https://sqlwithmanoj.com/sql-server-2016-articles/


Advertisements

SQL Server 2016 Certification Path

May 21, 2017 1 comment

 
Microsoft has made few changes to its Certification path for the new version of SQL Server i.e. SQL Server 2016.

And with this post I’m trying to collate and put all exams and certifications in concise and clear manner. Would be happy to accept any comments, changes and suggestions !!!
 

–> The MCSA i.e. “Microsoft Certified Solution Associate” level now contains the new “MCSA: SQL Server 2016” certification, and this replaces the older “MCSA: SQL Server 2012/14” one.
 

–> And the top MCSE i.e. “Microsoft Certified Solutions Expert” level now contains the new “MCSE: Data Management and Analytics” certification. This also replaces the following 2 existing certifications (but retains the underlying exams to earn this MCSE level, which I’ve explained below):
– MCSE: Data Platform
– MCSE: Business Intelligence


 

–> Here are the details of exams at both the levels.

Level 1. “MCSA: SQL Server 2016”: Now to earn this level you need to give either of the following certifications:

   1. MCSA: SQL 2016 Database Development certification
       – Exam [70-761], Querying Data with Transact-SQL
       – Exam [70-762], Developing SQL Databases

   2. MCSA: SQL 2016 Database Administration certification
       – Exam [70-764], Administering a SQL Database Infrastructure
       – Exam [70-765], Provisioning SQL Databases

   3. MCSA: SQL 2016 Business Intelligence Development certification
       – Exam [70-767], Implementing an SQL Data Warehouse
       – Exam [70-768], Developing SQL Data Models

 

Level 2. “MCSE: Data Management and Analytics”: Now to earn this level you need to first get an MCSA on either of the above 3 certifications, or “MCSA: Data Science”. Then need to pass anyone of the below exam:

   – Exam [70-473], Designing and Implementing Cloud Data Platform Solutions
   – Exam [70-475], Designing and Implementing Big Data Analytics Solutions
   – Exam [70-464], Developing Microsoft SQL Server Databases
   – Exam [70-465], Designing Database Solutions for Microsoft SQL Server
   – Exam [70-466], Implementing Data Models and Reports with Microsoft SQL Server
   – Exam [70-467], Designing Business Intelligence Solutions with Microsoft SQL Server


Now “CREATE OR ALTER” Stored Procedure, Function, View, Trigger with SQL Server 2016

November 19, 2016 2 comments

 
SQL Server 2016 release was packed with lot of new features, and I tried to cover most of them, [check here]. This includes some of the major new features like, Polybase, Temporal Tables, JSON support, Stretch DB, Row Level Security, Dynamic data Masking, etc. are very unique to the other Database systems in competition.

But Microsoft’s SQL Server team also keeps on adding few features in every release which were already there in other Database systems, so that developers could use those and make their life easier, like the new IF EXISTS option with DROP & ALTER statements I already discussed in my [previous post].

 
Now, with the recent Service Pack 1, one more feature has been added, which developers (mainly from the Oracle background) were missing from long time, and that is CREATE OR ALTER option while creating programming modules, like:

1. Stored Procedures (SP)

2. Functions (UDFs)

3. Views

4. Triggers

 
–> Now you can create a new Stored Procedure without checking its existence, simply by using the new CREATE OR ALTER option, like below:

CREATE OR ALTER PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO

… you can execute the above code multiple times and it won’t fail. First time this CREATEs the SP, next time it will ALTER it.

 
–> Previously you need to add an IF EXISTS() condition to check if the SP already exists or not. If exists then drop and then create a new SP, like:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spgetEmployeeDetails')
	DROP PROCEDURE dbo.spgetEmployeeDetails
GO

CREATE PROCEDURE dbo.spgetEmployeeDetails
	@EmpID INT
AS
BEGIN
	SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName 
	FROM Person.Person
	WHERE BusinessEntityID = @EmpID
END
GO

SQL Server 2016 1st Service Pack (SP1) is out (download, new features & enhancements)

November 17, 2016 4 comments

 
Almost 6 months back i.e. on 1st June 2016 Microsoft released SQL Server 2016 RTM, i.e. full and final version, which you can [check and download here].
 

Yesterday (i.e. 16-Nov-2016) Microsoft released the 1st Service Pack (SP1) of SQL Server 2016.
 

–> Download:

To download the SQL Server 2016 SP1 you can Register and Download the Full version or Free evaluation version (180 days).

… or you can Download the new Setup utility here, which provides you option to do a Basic or Custom installation, or download the ISO or CAB file (~2.5 GB).

… or you can also just download the Service Pack (SP1) (~550 MB), instead of the whole Setup (~2.5 GB).
 

–> What’s new SP1:

1. Features which were only available in Enterprise edition are now enabled in Standard, Web, Express, and LocalDB editions, link.

2. List of Bugs and issues fixed, link.

3. CREATE OR ALTER syntax for Stored Procedures, Views, Functions, and Triggers.

4. DBCC CLONEDATABASE (source_database_name, target_database_name), with optional WITH NO_STATISTICS, NO_QUERYSTORE. Creates a duplicate database by cloning Schema, metadata and statistics, without the data.

5. OPTION (USE HINT(‘hint1’, ‘hint2’)), support for a more generic query hinting is added, link.

6. Post this Service Pack (SP1) Parallel INSERTs in INSERT..SELECT to local temporary tables is disabled by default and will require TABLOCK hint for parallel insert to be enabled.

7. New DMVs are added, and some enhanced:
   – sys.dm_exec_valid_use_hints to list hints
   – sys.dm_exec_query_statistics_xml to return showplan XML transient statistics
   – sys.dm_db_incremental_stats_properties to check incremental statistics for the specified table
   – New column instant_file_initialization_enabled is added to sys.dm_server_services, to allow DBAs to programmatically identify if Instant File initialization (IFI) is in effect at the SQL Server service startup.
   – New column estimated_read_row_count is added to sys.dm_exec_query_profiles
   – New columns sql_memory_model and sql_memory_model_desc are added to sys.dm_os_sys_info, to provide information about the locking model for memory pages, and to allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.
 

sql-server-2016-install
 

Do check & Like my FB Page.


New Sample database “WideWorldImporters” for SQL Server 2016 and Azure SQL Database


 
So now as SQL Server 2016 is released (on 1-June-2016) and is in market for few days, so Microsoft team has released a new Sample Database “WideWorldImporters” specially for learning and working with new features of SQL Server 2016 and Azure SQL Database.
 

Till now AdventureWorks was quiet popular Sample Database since SQL Server 2005 to SQL Server 2014, and still will be. And prior to this we had Northwind and Pubs sample databases to work with SQL Server 2000 version.
 

–> WideWorldImporters:

You can download both the OLTP and OLAP (DW/BI) databases from this GitHub link.

1. WideWorldImporters (OLTP): contains sample tables for OnLine Transaction Processing (OLTP) workloads, as well as Real-time Operation Analytics.

2. WideWorldImportersDW (OLAP, DW/BI): contains sample tables for OnLine Analytical Processing (OLAP) workloads, in Dimensional Model, like Fact and Dimension tables.

3. For Azure SQL Database: you can download the bacpac for both the editions OLTP/OLAP.
 

–> These Sample Databases are designed in such a way that these can be used to check and evaluate the new Features of SQL Server 2016, like:

1. Temporal Database and Tables

2. Native JSON support

3. ColumnStore Index

4. In-Memory OLTP

5. Row Level Security, Dynamic Data Masking and Always Encrypted

6. Partitioning

7. Query Store

8. Polybase
 

–> The MSDN Documentation of these sample databases provides you more information on:

1. Installation and Configuration

2. The Database Catalog

3. Use of SQL Server features and capabilities (mentioned in above points)

4. Some Sample Queries (zip file)
 

–> After Downloading and Restoring the sample tables looks like this in Object Explorer:

WideWorldImporters