SQLServerPedia

I have just finished the first of the Microsoft SQL Server 2012 Beta exams, Querying Microsoft SQL Server 2012 (71-461).  I took them for a couple of different reasons, I feel that certifications are important and it was an opportunity for me to upgrade my current certifications.  

My thoughts after finishing the exam, You don't want me to be your developer.  It was a good exam by I know my strengths are in database administration and not development.  There were some questions that were unclear, and some that I flat didn't know the answer of but they seemed positioned simlar to the tests from last year.  

If you are intersted in taking these exams you can register for them for free here.

I have mentioned before that use Evernote for a host of things.  Studying for these exams has been no different.  I have created a notebook that has my links to study notes.  I am going to post them here but I will share the notebook that will update as I update them.  You can see the notebook here.

The notes below and in my notebook are based off of the study guide posted in the Microsoft Training Catalog.

This exam is intended for SQL Server database administrators, implementers, system engineers, and developers with two or more years of experience who are seeking to prove their skills and knowledge in writing queries. Primary responsibilities may include but are not limited to:

Create database objects (24%)

• Create and alter tables using T-SQL syntax (simple statements)
  • Create and alter tables
    • Tables store data in the database
    • The CREATE TABLE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms174979.aspx
    • After a table is created you can change many of the options through the ALTER TABLE command.  
    • The ALTER TABLE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms190273.aspx
• Create and alter views (simple statements)
  • Vies are essentially virtual tables.  This virtual table is defined by a query.
  • Views have a max of 1024 columns
  • The CREATE VIEW TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187956.aspx
  • The ALTER VIEW TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms173846(v=sql.110).asp
• Design Views
• Create and modify contraints
  • Constraints define the way the Database Engine enforces the integrity of a database. 
  • Constraints are preferred to using Triggers, Rules, and Defaults
  • Keys are constraints
  • Constraint TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189862.aspx
• Create and alter DML Triggers
  • INSTEAD OF are executed in place of the usual triggering action. 
  • AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE
  • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms190267.aspx
  • CREATE TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189799.aspx
  • Triggers are a way to enforce business rules and data integrity.
  • It is a special type of stored procedure that automatically takes effect.
  • There are three types: DML, DDL and logon
  • DDL triggers are invoked when a data definition language event takes place.
  • DML triggers are invoked when when a data manipulation language event takes place in the database.  This includes INSERT, UPDATE or DELETE statements that modify data.    
  • Logon triggers fire stored procedures in response to a LOGON event.
  • They can cascade changes through related tables in a database.  Constraints can do this more efficiently.
  • The CREATE TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms189799.aspx
  • The ALTER TRIGGER TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms176072.aspx

Work with Data (27%)

• Query data by using SELECT statements
  • Ranking Functions
    • Ranking functions return a ranking value for each row in a partition
    • The Ranking Function syntax for RANK can be found here: http://msdn.microsoft.com/en-us/library/ms189798.aspx
  • Join Basics
    • A good primer can be found on Pinal Dave's blog: http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
  • Except and Intersect
    • Returns distinct values by comparing the results of two queries
    • EXCEPT retunes and distinct values from the left query that are not also found in the right query.
    • INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand
    • Rules
      • The number and order of the columns must be the same in all queries
      • The data types must be compatible
    • Two NULL values are considered equal
    • More information can be found here: http://msdn.microsoft.com/en-us/library/ms188055.aspx
  • Dynamic SQL
    • Dynamic SQL is the term used when the SQL code is generated by a program before it is executed.  
  • CASE
    • Evaluates the list of conditions and returns one of multiple possible result expressions
    • The CASE statement can be found here: http://msdn.microsoft.com/en-us/library/ms181765.aspx
  • ISNULL
    • Replaces NULL with the specified replacement value
    • ISNULL is not a replacement for IS NULL. 
    • ISNULL TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms184325.aspx
  • COALESCE
    • Returns the first non null expression among its arguments
    • Expressions involving COALESCE with non-null parameters is considered to be NULL
    • The COALESCE statement can be found here: http://msdn.microsoft.com/en-us/library/ms190349.aspx

• Implement sub-queries
  • A subquery can be used anywhere an expression is allowed
  • A subquery will return a single value
  • More information can be found here: http://msdn.microsoft.com/en-us/library/aa213252(v=sql.80).aspx

• Implement data types
  • A data type is an attribute that specifies the type of data that the object can hold.  
  • Data types can be created through the CREATE TYPE command or through .NET Framework
  • More information on Data Types can be found here: http://msdn.microsoft.com/en-us/library/ms187752.aspx
  • GUIDs can cause performance issues when they are used as the clustered index key.  More information can be found from Glenn Berry here: http://www.sqlservercentral.com/blogs/glennberry/2010/03/22/why-uniqueidentifier-is-a-bad-choice-for-a-clustered-index-in-sql-server/
  • GUIDs are also a larger data type than necessary.

• Implement aggregate queries
  • Aggregate functions return a single value summarizing a given data set.  All aggregate functions are deterministic.  
  • Examples: COUNT, MAX, MIN, AVG, SUM, etc
  • More information on aggregate functions can be found here: http://sqlserverpedia.com/wiki/Built-in_Functions_-_Aggregate_Functions

• Query and manage XML data
  • SQL Server 2012 has changed the Data Type Conversion by the XML value Method Has Changed.  More information regarding this change can be found here: http://technet.microsoft.com/en-us/library/ms143359(v=sql.110).aspx
  • TSQL supports a subset of the XQuery language that is used for querying the XML data type.

Modify Data (24%)

• Create and alter stored procedures
  • Stored procedures accept input parameters return values
  • They contain programming statements that perform operations in the database
  • The CREATE PROCEDURE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187926.aspx
• Modify data by using INSERT, UPDATE, and DELETE statement
• Combine Datasets
  • Datasets can be combined by UNION, EXCEPT, and INTERSECT
  • ORDER BY is only allowed at the end of the statement and cannot be used within the individual queries
  • GROUP BY and HAVING clauses can be used only within individual queries not on the final result set.
  • The FOR BROWSE clause cannot be used.
  • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms191523.aspx
• Work with functions
  • Apply built-in scalar functions
    • Provided by SQL Server.
    • Cannot be modified
    • Access system information without accessing the system tables
    • Used to perform common tasks like SUM, GETDATE, or IDENTITY
    • MSDN Library Reference:http://msdn.microsoft.com/en-us/library/ms177499.aspx
  • Apply ranking functions
    • Retuns a ranking value for each row in a partition
    • MSDN Library Reference: http://msdn.microsoft.com/en-us/library/ms189798.aspx

Troubleshoot and Optimize Queries (25%)

• Optimize queries
  • Statistics
    • The query optimizer uses statistics to create query plans that improve query performance. 
    • They contain statistical information about the distribution of the values in one or more columns of a table or indexed view
    • More information regarding statistics can be found here: http://msdn.microsoft.com/en-us/library/ms190397.aspx
  • Query Plans
    • SQL Server 2012 includes additional information over 2008R2.  
    • Additions to the SELECT Statement
      • MemoryGrantInfo is now included
      • OptimizedHardwareDependentProperties is now included
      • Warnings are now included to show things that will effect plans
      • Some more information can be found here: http://www.sqlservercentral.com/blogs/scarydba/2011/12/12/changes-to-sql-server-2012-execution-plans/
    • Plan Guide information can be found here: http://technet.microsoft.com/en-us/library/ms190417(v=sql.110).aspx
  • DMVs
    • DMV information can be found here: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/18/sql-server-2012-changes-to-system-objects-in-rc0.aspx
  • Hints 
    • options or strategies for enforcement by the query processor. 
    • Join Hints
      • LOOP, HASH, MERGE, REMOTE
      • More information on Join Hints can be found here: http://msdn.microsoft.com/en-us/library/ms173815.aspx
    • Query Hints
      • More Information on Query Hints can be found here: http://msdn.microsoft.com/en-us/library/ms181714.aspx
    • Table Hints
      • Table Hints override the default behavior of the query
      • More information on table hints can be found here: http://msdn.microsoft.com/en-us/library/ms187373.aspx
    • Statistics IO
      • Setting STATISTICS IO is ON the query statistics are displayed when it is run
      • Statistics will show information regarding physical and logical reads.
      • More information can be found here: http://msdn.microsoft.com/en-us/library/ms184361(v=sql.110).aspx
    • Parameterized Queries
      • TSQL that accepts parameters
• Manage transactions
• Evaluate the use of row-based operations vs. set-based operations
• Implement error handling

 

 

 

 

Create and alter indexes

• Index allows specific data to be located quicker.
• Indexes are on-disk structure associated with a table or view.
• There are two types of indexes: Clustered and Nonclustered
  • Clustered
    • Sort and store the data rows in the table. 
    • Like 'The Highlander', there can be only one
  • Nonclustered
    • Contains key values and a pointer to the data roy that contains the key
    • Can have more than one.
• Indexes, if properly designed, can reduce disk I/O and improve system performance by reducing the use of system resources. 
• CREATE INDEX TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms188783.aspx

Create and alter stored procedures

• Stored procedures accept input parameters return values
• They contain programming statements that perform operations in the database
• The CREATE PROCEDURE TSQL can be found here: http://msdn.microsoft.com/en-us/library/ms187926.aspx

Create and alter user-defined functions (UDFs)

• These functions are used to bundle code for reuse within SQL Server.  
• TSQL Errors cause the function to stop.
• Benefits of UDFs
  • Allow modular programming
  • Allow faster execution
  • Can reduce network traffic
• The CREATE FUNCTION TSQL can be found here:http://msdn.microsoft.com/en-us/library/ms191320(v=sql.110).aspx

 

Permalink | Leave a comment  »