Study Notes for 70-461 Exam

Here are some notes taken, whilst doing last minute cram for this exam.

You can find our full list of study resources here.

Designing views

  • you cannot use COMPUTE
  • You cannot create on a temporary table
  • You can use an instead of trigger
  • you cannot use an AFTER trigger

If you want to rename a view you can use sp_rename, or (and this is the recommended option) you can delete and recreate.

You cannot use ALTER VIEW to rename a VIEW.

Well I doubt this is in the exam, but just in case the reason to not use sp_rename is because it doesn’t do a very good job of renaming. Example below

CREATE PROC test_sp_name
AS
SELECT 1

GO

sp_rename 'test_sp_name' , 'newtest_sp_name' 

GO

sp_helptext newtest_sp_name

The results of sp_helptext will show the old name still. That is why it is recommended, if given the option, to delete and recreate objects rather than using sp_rename.

Query Hints

  • You can use the MERGE UNION query hint to specify that all UNION opertaions will pe performed by merging UNION sets.
  • The CONCAT UNION query hint option specifies that all UNION operations will be performed by concatenating UNION sets.
  • The HASH UNION query hint option specifies that all UNION operations will be performed by hashing UNION sets.
  • The HASH JOIN query hint option specifies that all JOIN operations in the query use HASH JOINs.

Transactions

You can use COMMIT WORK instead to COMMIT a transaction. Not sure why you would want to, however you can.

The command is essentially identical to COMMIT TRANSACTION, except you cannot provide a transaction name.

  • You use the COMMIT TRANSACTION statement to mark the end of a successful implicit or explicit transaction.
  • You use the BEGIN TRANSACTION statement to mark the start point of an explicit local transaction.
  • You use the SAVE TRANSACTION statement to create a savepoint within a transaction.
  • You use the ROLLBACK TRANSACTION statement to roll back an implicit or explicit transaction. You can use this statement to roll back to a savepoint or to the beginning of the transaction.

ROWLEVEL Locks

You can use ROWLOCK to lock just a row, i.e not issue a page or table lock.

Altering Schemas

You need to use the ALTER SCHEMA TRANSFER statement to transfer a schema from one schema to another.

Transaction Isolation Levels

Make sure you memorize what all these do!

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE If you want to make sure that the statement cannot read data that has been altered (but not yet committed), and other transactions are unable to modify the data being read until commits. Also other transactions are not able to insert rows, if they have key values within the range being read by the current transaction.(until it commits)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED this is for when you want to allow statements to read rows that have been altered by other transactions, but not yet committed.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED – Normally the default isolation level, ensures that statements cannot read data that is being altered by other (as yet uncommitted) transactions. Data can however be altered by other transactions in between the statements that occur within the current transaction (this can result in nonrepeatable reads or phantom data).

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ – this will ensure that other statements cannot read data that has been modified, but not yet committed, also additionally it will stop other transactions from modifying data that has been read by the current transaction, until it is completed. It is however possible for other transactions to do inserts of rows, that meet the search conditions of the current transactions statements.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT – You can use this statement when you wish to make sure that data read by any statement in a transaction will be transactionally consistent with how that data existed at the beginning of the transaction.

SNAPSHOT transactions do not block separate transactions from being able to write data.

RANK and DENSE_RANK

The difference between these two is mainly to do with ties.

Out of the box, if you just use RANK, ties will be assigned the same rank. E.g 2 number 3’s. And number 4 will be skipped.

If you use DENSE_RANK there will still be 2 number 3’s, but there will be a number 4 as well.

Think of DENSE_RANK as being the same as RANK, but going into denser detail.

TRY_PARSE and TRY_CONVERT

TRY_PARSE is used for converting one cultural format to another. E.g a date in a certain countries format, to a datetime2 data type.

TRY_CONVERT is for attempting to convert one data type to another. They both return NULL values so a tricky one, look for the cultural/country date time question, to be sure it is TRY_PARSE.

CURSOR Questions

For questions about cursors its important to know the differences around these:

CLOSE – use this to release the current result set (cursors locks on rows) from a cursor. (its easy to get this mixed up with DEALLOCATE so be wary)

DEALLOCATE – this to remove the reference to a cursor.

OPEN – pretty self explanatory. Use it to open a Cursor.

FETCH – to get a row from a CURSOR.

More on cursors – make sure you also learn the different types.

Querying XML

To query XML-type variables or columns you must set the following: (or ensure they are already set)

ANSI_PADDING ON
ANSI_NULLS ON
ANSI_WARNINGS ON
QUOTED_IDENTIFIER ON

Other options that should be configured are as follows:

ARITHABORT ON
CONTACT_NULL_YIELDS_NULL ON
NUMERIC_ROUNDABORT OFF

Without these settings, queries using XML Data type methods will fail.

HASH, MERGE, CONCAT, UNIONS

CONCAT UNION = all UNION operations will be performed by concatenating UNION sets.

MERGE UNION – all UNION operations will be performed by merging UNION sets.

HASH UNION – all UNION operations will be performed by hashing UNION sets.

HASH JOIN – all JOIN operations in the query use HASH JOINs.

Learn your Int values

  • TINYINT 0 to 255
  • SMALLINT -32768 to + 32678
  • INT -2,147,483,648 to + 2,147,483,647
  • BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Moving Object From One Schema To Another

Remember to Alter the destination schema and transfer the source object to it.

E.g table sales.product wants to be transfered to the inventory schema

ALTER SCHEMA inventory TRANSFER sales.product

Error System Functions

ERROR_STATE() system function to determine the error state number. An error state number differs from an error number.

ERROR_LINE() system function to determine the line number at which the error occurred.

ERROR_MESSAGE() system function to determine the text of the message that is returned to the application.

ERROR_NUMBER() system function to determine the error number.

ERROR_PROCEDURE() system function to determine the name of the trigger of stored procedure in which the error occurred.

ERROR_SEVERITY() system function to determine the severity of the error.

Helpful things to remember when sitting 70-461 exam

Read the questions carefully more than once, even if the answer seems obvious.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.