Home Technical Books Religion Books Authors

The Special Web Price of $39.99 saves you over 10%! Click Buy Now!


The "SQL Server 2012 Prep and Reference" Helps You Code Better

The Special Web Price of $39.99 saves you over 10%! Click Buy Now!


The Part, Chapter and Topic Map

Part 1: Creating Database Objects

Chapter 1: Tables

Pages 190- 32

What are the rules for creating, altering tables, partitions, columns, indexes? What constrains using exotic data types?

Chapter 2: Views

Pages 33 - 37

Creating views has rules; so does creating indexes on views; If a view is going to have an index, how is that made possible?

Chapter 3: Constraints

Pages 38 - 41

How many types of constraints are there, what are their descriptions and how does SQL Server treat them differently?

Chapter 4: DML Triggers

Pages 42 - 45

There are server triggers, triggers for the system and then there are DML triggers. What are the types, what sets them apart from one another? What are the dangers of triggers?

When you finish Chapter 4 it marks the end of section 1 of Microsoft's description of test, "Creating Database Objects" 24%. If you need to make notes, return and review each chapter after reading through once and make notes on the second time through.

Part 2: Work with Data

Chapter 5: Query Data by Using SELECT Statements

Pages 47 - 56

What is the difference between INTERSECT and EXCEPT; RANK() and DENSE_RANK()? Why on earth would you combine PIVOT with NTILE()? What is RBAR and why should we hate it?

Chapter 6: Dynamic T-SQL and System Metadata Queries

Pages 57 - 76

When is code "good" or "bad"? What is the difference between data and metadata? Why when you are trying to get a report out on an impossible deadline do you care about metadata anyway?

Chapter 7: Implement Sub-Queries

Pages 77 - 83

What's the difference between a derived table, correlated sub-query and a pivot? What are the differences between ADO/MDAC and SSNC? (And why is connectivity with sub-queries!?)

Chapter 8: Implement Data Type

Pages 84 - 99

What is the effect of ANSI_PADDING on VARCHAR or CHAR? How is a FLOAT data type different from a DECIMAL?

Chapter 9: Implement Aggregate Queries

Pages 100 - 117

What is the difference between a GROUP BY HAVING clause and a WHERE clause? What is the effect on AVG of ALL or DISTINCT?

Chapter 10: Query and Manage XML Data

Pages 118 - 136

How many different ways are there to query XML data and how do you use them? Which one of these is RBAR and which is not? How is well-formed XML different from poorly formed XML?

Section 2 of Microsoft's description of the test " 27% is complete. Use the same method as used at the end of Section 1 in re-reading, review and taking notes. Part 3 worth 24% is next.

Part 3: Modify Data

Chapter 11: Create and Alter Stored Procedures (Simple Statements)

Pages 138 - 178

What causes a stored procedure to recompile and why is that a bad idea? What is required to execute a CLR procedure? How does BEGIN END differ from TRY CATCH? What is wrong with using a cursor or in other words, when is it a good idea to use them? What can stored procedures NOT do?

Chapter 12: Modify Data by Using INSERT, UPDATE and DELETE Statements

Pages 179 - 186

What can happen when you try to delete a record from a table that is in a relationship and what can you do about it? What kinds of errors do you see when you violate a default constraint; a check constraint? How do output statements differ between UPDATE and MERGE statements?

Chapter 13: Combine Datasets

Pages 187 - 196

How do UNION and UNION ALL differ in result; CASE, ISNULL and COALESCE? What about IF and CASE?

Chapter 14: Work with Functions

Pages 197 - 216

What are the differences between the different types of User Defined Functions? How do scalar functions differ from table functions both in output and in structure? When should you use SCHEMABINDING?

Part 4: Troubleshoot & Optimize

Chapter 15: Optimize Queries

Pages 217 - 257

Why are statistics more important that indexes, if indeed they are? What is the difference between a Clustered Scan and a Clustered Seek and why is that important? How does Showplan, plan guides and employing DMVs help you write better performing queries?

Chapter 16: Manage Transactions

Pages 259 - 267

Does SQL Server provide transactions on its own? What happens when a group of commit and rollback statements occur within the the same scope?

Chapter 17: Row-Based Operations vs. Set-Based Operations

Pages 268 - 270


Chapter 18: Implement Error Handling

Pages 268 - 274

Why do you use TRY CATCH? what is the new replacement for RAISERROR?

Section 4 is complete. Upon finishing your notes, schedule the test and make your first attempt to pass it. If you fail, revisit each subject area that the test report says is incomplete and rework your notes. If you need to get a deeper discussion as found in an in-depth book and dive deeply into the topic of weakness. Redo your notes and take it again.

Chapter 19: Notes

Some final thoughts.

As they said on Galaxy Quest,

"Never Give Up. Never Surrender! "

The Special Web Price of $39.99 saves you over 10%! Click Buy Now!