Joes 2 Pros SQL Server Learning series is indeed fun. Joes 2 Pros series is written for beginners and who wants to build expertise for SQL Server programming and development from fundamental. In the beginning of the series author Rick Morelan is not shy to explain the simplest concept of how to open SQL Server Management Studio. Honestly the book starts with that much basic but as it progresses further Rick discussing about various advanced concepts from query tuning to Core Architecture.
This five part series is written with keeping SQL Server Exam 70-433. Instead of just focusing on what will be there in exam, this series is focusing on learning the important concepts thoroughly. This book no way take short cut to explain any concepts and at times, will go beyond the topic at length. The best part is that all the books has many companion videos explaining the concepts and videos. Every Wednesday I like to post a video which explains something in quick few seconds. Today we will go over five videos which I posted in my earlier posts related to Joes 2 Pros series.
Introduction to XML Data Type Methods – SQL in Sixty Seconds #015
The XML data type was first introduced with SQL Server 2005. This data type continues with SQL Server 2008 where expanded XML features are available, most notably is the power of the XQuery language to analyze and query the values contained in your XML instance.
There are five XML data type methods available in SQL Server 2008:
query() – Used to extract XML fragments from an XML data type.
value() – Used to extract a single value from an XML document.
exist() – Used to determine if a specified node exists. Returns 1 if yes and 0 if no.
modify() – Updates XML data in an XML data type.
node() – Shreds XML data into multiple rows (not covered in this blog post).
[Detailed Blog Post] | [Quiz with Answer]
Introduction to SQL Error Actions – SQL in Sixty Seconds #014
Most people believe that when SQL Server encounters an error severity level 11 or higher the remaining SQL statements will not get executed. In addition, people also believe that if any error severity level of 11 or higher is hit inside an explicit transaction, then the whole statement will fail as a unit. While both of these beliefs are true 99% of the time, they are not true in all cases. It is these outlying cases that frequently cause unexpected results in your SQL code.
To understand how to achieve consistent results you need to know the four ways SQL Error Actions can react to error severity levels 11-16:
Statement Termination – The statement with the procedure fails but the code keeps on running to the next statement. Transactions are not affected.
Scope Abortion – The current procedure, function or batch is aborted and the next calling scope keeps running. That is, if Stored Procedure A calls B and C, and B fails, then nothing in B runs but A continues to call C. @@Error is set but the procedure does not have a return value.
Batch Termination – The entire client call is terminated.
XACT_ABORT – (ON = The entire client call is terminated.) or (OFF = SQL Server will choose how to handle all errors.)
[Detailed Blog Post] | [Quiz with Answer]
Introduction to Basics of a Query Hint – SQL in Sixty Seconds #013
Query hints specify that the indicated hints should be used throughout the query. Query hints affect all operators in the statement and are implemented using the OPTION clause. Cautionary Note: Because the SQL Server Query Optimizer typically selects the best execution plan for a query, it is highly recommended that hints be used as a last resort for experienced developers and database administrators to achieve the desired results.
[Detailed Blog Post] | [Quiz with Answer]
Introduction to Hierarchical Query – SQL in Sixty Seconds #012
A CTE can be thought of as a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table while providing the same benefits to the user. However; a CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query. A recursive CTE requires four elements in order to work properly:
Anchor query (runs once and the results ‘seed’ the Recursive query)
Recursive query (runs multiple times and is the criteria for the remaining results)
UNION ALL statement to bind the Anchor and Recursive queries together.
INNER JOIN statement to bind the Recursive query to the results of the CTE.
[Detailed Blog Post] | [Quiz with Answer]
Introduction to SQL Server Security – SQL in Sixty Seconds #011
Let’s get some basic definitions down first. Take the workplace example where “Tom” needs “Read” access to the “Financial Folder”. What are the Securable, Principal, and Permissions from that last sentence?
A Securable is a resource that someone might want to access (like the Financial Folder).
A Principal is anything that might want to gain access to the securable (like Tom).
A Permission is the level of access a principal has to a securable (like Read).
[Detailed Blog Post] | [Quiz with Answer]
Please leave a comment explain which one was your favorite video as that will help me understand what works and what needs improvement.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology, Video
