Microsoft 70-464 Developing Microsoft SQL Server 2012 Da...

Introduce SQL Server 2012 by defining an instance and exploring the different editions available within SQL Server.

Discover how SQL Server evolved from a relational database to a full suite with integration services, analysis services, reporting services, and full-text indexing, with .NET integration.

Define SQL server instance as a relational database management system with default and named instances; the browser directs connections to ports 1433 or 1434, enabling security boundaries and resource use.

Explore SQL Server 2012 editions—Enterprise, Standard, Business Intelligence, Developer, Express—and core licensing changes; understand the server to database hierarchy and key system databases like Master, Model, MSDB, TempDB, resource database.

Explore tools to work with the server, including SQL Server Management Studio, SQL Server Data Tools (formerly BI Development Studio), the command line interface to SQL Server, and PowerShell.

Unify SQL Server Management Studio, merging Enterprise Manager and Query Analyzer into one interface. Discover IntelliSense, native debugging, scripting, templates, and project-based workflows for database development.

Install sql server data tools (ssdt) as visual studio 2010 with business intelligence templates, including ssis, ssrs, and ssas; previously known as bids, select ssdt from the installation client components.

Use the sequel command, a command line interface for sequel server, with an ole db connection to automate object creation via script and access the dedicated administrative connection for troubleshooting.

Explore sql server 2012 tools in sql server management studio, script objects and databases, and generate scripts for empty shells. Use new query windows and debugging to inspect code.

Explore SQL Server data tools in Visual Studio, use template explorer to create databases and business intelligence templates for integration, analysis, and reporting services.

Gain hands-on experience with sequel server data tools, management studio, power shall, and sequel command. Explore options and begin working with samples and labs to prepare for the exam.

Learn how to switch query results from grid to text in SQL Server Management Studio using the results to text icon, and the change applies to the selected tab.

Explore SQL Server 2012 basics, including what an instance is. Use SQL Server Management Studio, SQL Server Data Tools with BI templates, SQL command line, and PowerShell to manage instances.

Explore data types supported by SQL Server and understand collations, Unicode characters, and null values. Practice converting data types within table contexts.

Define data types to specify permitted data in columns, variables, expressions, and parameters, ensuring data integrity and query optimization.

Explore SQL Server data types, including numeric, binary, and character types, and learn how to choose between char and varchar using a customer name and state example to optimize storage.

Discover how collations govern data comparison, storage, and sorting in SQL Server 2012 from server instance to database column and query levels, including language, ANSI/Unicode, and case and accent sensitivity.

Unicode uses 16 bits (two bytes) to store characters such as Japanese, Chinese, and Cyrillic, and SQL supports double-byte UTF for up to 4 bytes per character.

Null values follow three-valued logic, are not equal to any value, and are handled by is null and coalesce under ANSI standards; they affect queries and aggregates.

Explains implicit and explicit data type conversions in SQL Server using cast or convert, and introduces parse and try convert with culture-specific options and null-on-failure handling, noting .NET CLR reliance.

Explore the fundamentals of tables in SQL Server, including normalizing tables for OLTP databases, working with tables, using temporary tables, and understanding schemas.

Explore how tables store data with user-defined and system-defined types, and how catalog views replace system tables in SQL Server 2005 and later, including columns, rows, and computed columns.

Normalize OLTP databases to reduce redundancy and storage, boosting transactional performance. Denormalize OLAP data for reporting and batch processing, using primary keys and foreign keys across 1NF, 2NF, and 3NF.

Explore temporary tables in SQL Server, including session and global scopes, explicit drop practices, and how they maintain statistics to boost large set-based operations compared to table variables.

Create, alter, and drop tables using SQL Server Management Studio or SQL; learn temporary table prefixes and how foreign keys affect drop operations.

Discover how ANSI schemas define namespaces in SQL Server and how objects belong to a schema. Learn how default schemas and dbo ownership affect object resolution in queries.

Explore primary keys and foreign keys, applying cascade actions and unique constraints to ensure data integrity in SQL Server 2012.

Identify and differentiate primary keys and foreign keys in SQL Server, including naturally occurring and surrogate keys, referential integrity, cascade rules, and logical versus physical deletes.

Explore how a unique constraint and a unique index enforce column uniqueness, with a constraint automatically creating an index, and note that a unique constraint allows a single null value.

Explore unicode vs non-unicode data types, compare length and data length, and show string literals with capital N. Examine cast, convert, and try parse and convert behavior for date/time errors.

Explore how collations affect case sensitivity and sorting in SQL Server. Learn to handle null values with is null and coalesce for robust string concatenation.

Create a sales database with employee and sales tables, enforcing data integrity through primary keys, unique constraints, foreign keys, identity seeds, and composite keys.

Explore SQL Server 2012 data modeling with sales detail and product tables, focusing on foreign keys, primary keys, calculated columns (quantity × unit price), constraints, and table alterations.

Identify the most efficient data types for various scenarios, create tables in SQL Server Management Studio, and enforce referential integrity with primary and foreign keys, plus default and unique constraints.

Explore SQL Server data types, user defined types and aliases, and tables with columns and rows. Apply constraints to ensure data integrity, including primary keys and foreign keys.

Explore data storage basics and indexing, and learn about heaps, clustered indexes, and non-clustered indexes.

Explore how SQL Server stores data on 8 kilobyte pages, placing each row on a page unless it has blob data, and replace image or text with varbinary(max) and varchar(max).

Explore how SQL Server uses clustered and non-clustered indexes, unique constraints, and primary keys to boost performance, while heaps and page maintenance affect tradeoffs.

Explore how a clustered index orders table rows by the index key using a B-tree, with leaf data containing full row, and why only one clustered index per table matters.

Discover how non-clustered indexes differ from clustered indexes, sit above a clustered index or heap, and use leaf pages that point to data pages to speed queries.

Develop an indexing strategy based on average workload rather than a single query, balancing performance, maintenance, ascending or descending order, and selectivity and depth for clustered and non-clustered indexes.

Learn how data type choices affect non-clustered index performance, from exact numerics and date types to character data, and how filtered indexes improve selectivity and reduce maintenance.

Explore how non-clustered indexes can be covering, letting queries return results from the index without reading clustered data pages, and learn how included columns extend coverage beyond the 900-byte limit.

Explore maintaining indexes, address index fragmentation, and apply index tuning techniques to optimize SQL Server 2012 performance.

Maintain and monitor indexes to manage fragmentation and inevitable page splits in OLTP databases. Use fill factor and pad index to reserve empty space and reduce splits as pages grow.

Identify internal and external index fragmentation; decide between rebuild or reorganize with online options. Understand non-clustered index lookups and include columns; profiler is deprecated in favor of extended events.

Use the database engine tuning advisor to analyze a workload from server profiler or server side trace, generate index and statistics recommendations, and implement or script those changes.

Create a person index table and build a clustered index on last name, first name, and middle name to enable efficient seeks; add a non-clustered covering index on modified date.

Explore creating and altering non-clustered and covering indexes, including columns to optimize queries, compare index seek versus index scan, and review index maintenance and fragmentation.

Evaluate index fragmentation in SQL Server using dynamic management functions and DBCC SHOWCONTIG, compare clustered and nonclustered indexes, and fix fragmentation with rebuild or reorganize online or offline.

Understand clustered and non-clustered indexes, filtered indexes, and included columns. Compare rebuilding and reorganizing indexes, and assess index fragmentation with profiler, extended events, and the database engine tuning advisor.

Explore exam scenarios on clustered and non-clustered indexes, including leaf-level data in clustered indexes, included columns, and filtered indexes defined by predicates.

Explore indexing concepts, including clustered and non-clustered indexes, including properties such as included columns and filtered indexes, and learn how to assess and address index fragmentation through rebuilds or reorganizations.

Learn query performance basics by examining statistics I/O and statistics time, reading an execution plan, and understanding plan reuse.

Understand how automatic statistics on indexed columns affect query performance and the difference between estimated and actual statistics; update statistics; enable set statistics io and time to monitor reads.

Learn how query execution begins with parsing and resolution, then optimization based on statistics, with execution plans cached and viewable as estimated or actual in Management Studio.

Learn to read execution plans from right to left and top to bottom, identify expensive operators by cost percentages that sum to 100 percent, and compare estimated versus actual execution.

Explore common execution plan operators from table and index scans to seek operations, joins, aggregates, filters, and sorts, and learn to troubleshoot and compare operator costs.

Store and reuse execution plans from the cache to avoid costly optimization. Recompile plans for parameter sniffing when statistics change, but use sparingly and ensure recompilation is triggered.

Analyze query performance by examining estimated and actual execution plans in management studio, noting clustered and non-clustered index scans, hash matches, and nested loops, with XML details.

Explore statistics, histograms, and high keys to understand the execution plan, then use the database engine tuning adviser and profiler for index and trace recommendations.

Learn how the database engine tuning advisor analyzes workloads, provides recommendations, and how extended events replace profiler, using query and execution plans to optimize performance with indexes and statistics.

Examine exam scenarios for SQL Server 2012 development, covering statistics, reading and interpreting execution plans and their operators, plan reuse and recompilation, and why not all plans can be cached.

Explore how statistics automatically created for indexed and key columns influence execution plans, how the optimizer uses them, and how cached plan reuse enhances query performance.

Explore the introduction to the Common Language Runtime, discuss assemblies and permissions, and examine the objects available through the CLR.

Explore how the common language runtime enables managed code in SQL Server, allowing stored procedures, functions, and user-defined types such as geospatial, with access to external resources for advanced calculations.

Create and import CLR assemblies into SQL Server databases, written in Visual Basic or C#, containing stored procedures and user-defined functions; imports rely on well-formed binaries and permission checks.

Explore the default safe permission set and when to enable external access or sign the assembly. Recognize that unsafe permissions grant unrestricted access and risk SQL Server process stability.

Demonstrates integrating the common language runtime (clr) with SQL Server by building an assembly stored procedure that accepts xml data and a file name, then saves the result to disk.

Identify when the common language runtime is preferred over T-SQL, and understand CLR objects, assemblies, with stored procedures, table-valued and scalar-valued functions, DDL, DML, triggers, and user-defined types or aggregates.

Clarify when to enable the CLR on the SQL Server instance for using spatial data types, and identify the Common Language Runtime equivalent of a print statement, the sequel pipe.

Explore the common language runtime integration with SQL Server, including assemblies and CLR objects like stored procedures, functions, triggers, and user defined aggregates and types, and when to use CLR.

Explore error handling in T-SQL, including errors and severity, and learn to handle them with try catch, try parse, try convert, and alerts.

Learn how to handle Transact-SQL errors in sequel server 2012, including error properties, raise error, ad hoc raises, and throw, plus viewing system messages in catalog views.

Understand how error severity levels in sequel server guide troubleshooting, from informational 0–10 to admin-required 17–25, including corruption indicators and steps like restart and dbcc checks.

Learn structured error handling in SQL Server with try catch, introduced in SQL Server 2005. Nest blocks, access error info in the catch, and use throw to rethrow.

Explore try parse and try convert for handling conversion errors, returning null when conversions fail on the .NET CLR, while try convert may throw errors when not permitted.

Discover how the @@ERROR variable indicates the last statement's result in a batch; when an error occurs, @@ERROR holds the error number, otherwise zero.

Configure alerts in SQL Server to proactively handle errors by error number or severity, notify operators, or trigger jobs, with examples like truncating a full transaction log after a backup.

Explore T-SQL error handling with begin try and catch, inspecting error properties like message, number, line, and severity, and learn when to rethrow or treat information-only messages as non-errors.

Master proactive error handling in T-SQL with begin try and begin catch, including nested blocks, inner and outer catches, and divide by zero error handling with logging or email alerts.

Mastering exam scenarios by understanding error severity levels, implementing try catch, try parse, and try convert, and configuring alerts to notify operators and trigger jobs that proactively handle errors.

Explore how try catch blocks handle errors in SQL, clarify that they do not catch all errors, and learn how to notify administrators with alerts during error handling.

Explore sql server error handling, including system and user defined errors, error messages, and severity. Implement raiserror and throw, and use try catch, try parse, and try convert, plus alerts.

Explore an introduction to views, the different types of views in SQL Server, how to work with views, and key considerations when using and creating them.

Views act as tables by a select, returning a single result set; they support insert, update, and delete through the view and enable permissions on view instead of underlying objects.

Explore the two primary view types in SQL Server: system views and user defined views, including catalog, compatibility, information_schema, dynamic management views, and programmability views.

Create and manage views with data definition language, covering limitations such as a single result set and no insert, update, or delete, plus security and 32-level nesting.

Explore how indexed views use schema binding and deterministic definitions to build a unique clustered index, and how partition views enable cross-server results via linked servers and unions.

Explore an introduction to functions, identify the different types of functions, and learn how to work with functions. Examine the considerations to take when creating or altering functions.

Explore scalar-valued and table-valued functions, including inline and multi-statement variants created via SQL or CLR, and distinguish system functions from user-defined ones that cannot modify data.

Learn to create scalar and table-valued functions in SQL Server, including inline and multi-statement forms, returning a single value or a table, with deterministic and non-deterministic options and DDL operations.

Explore creating and using views and functions with the Adventure Works 2012 dataset, including an updatable view joining people and credit to form a full name.

Recreates views and enforces uniqueness on person_id to prevent duplicates, analyzes execution plans for indexed views, and builds a scalar function full name with first, middle, and last name parameters.

Explore advanced SQL Server techniques by building and using views and scalar and table-valued functions, examining performance, from-clause usage, and limitations of indexed and updatable views.

Explore when to use views, their limitations and nesting, security chains, and permissions, including indexed and updatable views; contrast scalar and table valued functions and their performance.

Assess how views can modify data under table- and permission-based limits, contrast with functions that cannot modify data, and identify scalar-valued, table-valued multi-statement, and table-valued inline functions.

Explore views and their types, when to use them to hide query complexity, and how they enable data manipulation on base tables. Review table-valued and scalar functions and their use.

Explore stored procedures and their types, learn how to work with them, and identify key considerations when implementing stored procedures.

Explore how stored procedures perform insert, update, and delete operations with input and output parameters, using temporary tables, and distinguish system from user defined procedures, noting security and performance benefits.

Understand stored procedures, their limitations, and what cannot be contained. Learn to use input and output parameters, single-batch compilation, and return codes (0 for success, negative for failure).

Explore the concept of triggers in SQL Server, learn the different types of triggers, and practice working with triggers.

Triggers are stored procedures tied to a table that run on insert, update, or delete transactions, including after and instead of triggers, and must be bulletproof to avoid rollback.

Explore how triggers use inserted and deleted pseudo-tables to track insert, update, and delete; manage nesting up to 32 levels, recursion risks, and fire order with set trigger order.

Create and alter a stored procedure that selects into a temporary table from person schema, showing parameter binding by ordinal position or name, default values, and error handling.

Create a credit limit table and an audit table, and implement insert, update, and don't delete triggers to log date, user, and before/after values.

Demonstrates stored procedures with input parameters and how to execute them. Explains triggers and instead of triggers to audit changes, show before and after values, and enforce business rules.

Learn to create stored procedures with syntax and parameters, including input, output, and return values; compare triggers with constraints for referential integrity, noting where to implement in code or SQL.

Stored procedures accept input and output parameters, return values, and support insert, update, and delete with multiple or no result sets; use instead of trigger for efficient error handling.

Explore stored procedures and triggers in SQL Server, including input/output parameters, returning result sets, late binding, and the use of after and instead of triggers for referential integrity.

Explore a primer on sql concepts and xml concepts, then examine schema collections, as this lesson explains how these elements organize data within sql databases.

Explore the XML data type in SQL Server 2005 for storing XML in columns, variables, and parameters, and query it with built-in methods to return native or XML results.

Explore xml concepts, including unicode markup, case sensitive tag names, and start-to-end elements with quoted attributes. Distinguish well-formed documents from fragments and note top-level elements in sql server.

Describe XML namespaces as collections of unique names for elements or attributes, explain how URIs (URLs, URNs) are used, and show aliasing to simplify schemas.

Explain why SML is popular for data exchange, detailing SQL Server schema collections, validation of well-formed SML against constraints, and storage as documents or fragments with indexing.

Explore schemas and schema collections in SQL Server, covering SML storage as blob up to 2 GB and creating primary and secondary indexes for path, value, and property queries.

Explore the for XML clause and related queries. Learn how these elements are used to work with XML data.

Explore the FOR XML clause in SQL Server, comparing options like auto, raw, explicit, and path to generate well-formed XML from query results, with roots, nesting, and attributes.

Explore flowr queries and the x query language for identifying sml nodes, with methods like query, exists, and modify in sequel server for typed and untyped sml data.

Create and store xml in SQL Server, ensure well-formed xml with a single root, and use for xml raw or auto to return elements under orders.

Explore xml in sql server with auto, explicit, and path modes to build well-formed data with a root orders node, showing customer id and sales order id as elements.

Prepare for the exam by mastering storing values in sql server tables as parameters and variables, and applying schema collections with SML indexing (primary and secondary) and SML path.

Create a primary X-amount index first, then secondary indexes such as path or value, based on the existing primary and the X queries used to query the SML structure.

Explore the XML data type in SQL Server, examine XML elements, well-formed documents and fragments, and learn to return XML result sets using for xml explicit and for xml path.