Joes 2 Pros SQL Server Videos from May 2012: SQL in Sixty Seconds

These Joes 2 Pros SQL Server videos gather five lessons that I shared in May 2012.

Rick Morelan wrote the five-part Joes 2 Pros learning series for people building SQL Server programming and development skills. It starts with fundamentals, including opening SQL Server Management Studio, and moves into query tuning and SQL Server architecture. I appreciated how much care these books take to explain the concepts.

The books were written with Exam 70-433 in mind. That is the historical exam context, not current certification guidance. The same five videos came back on 1 August 2012 in the reverse order. That post is Joes 2 Pros exam prep videos from August 2012. This page keeps the May order and adds one small example per lesson. I ran every example on SQL Server 2025.

Introduction to XML Data Type Methods

SQL Server added the XML data type in 2005. Its methods let you query a document, extract a value, test for a node and change the content. The query() method returns XML, value() returns one scalar, exist() returns 1, 0 or NULL, and modify() edits the document. The nodes() method turns nodes into rows, and this video does not cover it.

DECLARE @Order xml = N'<order id="7"><item sku="TEA1" qty="2"/><item sku="BIS2" qty="5"/></order>';
SELECT @Order.value('(/order/@id)[1]', 'int') AS OrderId,
       @Order.exist('/order/item[@sku="BIS2"]') AS HasBiscuits,
       @Order.exist('/order/item[@sku="XYZ"]') AS HasMissingSku,
       @Order.query('/order/item[@qty > 3]') AS BigItems;
SET @Order.modify('replace value of (/order/item[@sku="TEA1"]/@qty)[1] with "3"');
SELECT @Order.value('(/order/item[@sku="TEA1"]/@qty)[1]', 'int') AS NewQty;

value() returned order 7. exist() returned 1 for the biscuit item and 0 for a SKU that is not there. query() returned only the item with a quantity above 3. After modify(), the tea quantity read 3. On a NULL document, exist() returns NULL and not 0, so handle NULL when the column allows it.

Detailed blog post

Introduction to SQL Error Actions

An error does not always stop the batch or undo the transaction. The result depends on the error, the scope and settings such as XACT_ABORT. I put a divide by zero between two inserts in one transaction. I ran it with the default setting, then with XACT_ABORT on.

CREATE TABLE dbo.Stock (ItemId int PRIMARY KEY);

SET XACT_ABORT OFF;
BEGIN TRANSACTION;
INSERT INTO dbo.Stock VALUES (1);
SELECT 1 / 0 AS Boom;
INSERT INTO dbo.Stock VALUES (2);
COMMIT TRANSACTION;
SELECT COUNT(*) AS RowsWithoutAbort FROM dbo.Stock;

DELETE FROM dbo.Stock;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO dbo.Stock VALUES (1);
SELECT 1 / 0 AS Boom;
INSERT INTO dbo.Stock VALUES (2);
COMMIT TRANSACTION;
GO
SET XACT_ABORT OFF;
SELECT COUNT(*) AS RowsWithAbort, @@TRANCOUNT AS OpenTransactions FROM dbo.Stock;

By default the error stopped only its own statement, so both inserts committed and the table held 2 rows. With XACT_ABORT on, the same error ended the batch and rolled the transaction back. The table held 0 rows and no transaction stayed open. A compile error is not affected by this setting. Do not assume every error with severity 11 or higher acts the same way. Check the exact error and the transaction state.

Detailed blog post

Introduction to Basics of a Query Hint

A hint in the OPTION clause overrides a choice the optimizer made. Measure before and after, because the answer stays the same while the cost can change. I joined 100,000 orders to 1,000 customers and compared the default plan with a forced loop join. Look at the Messages tab for the reads.

CREATE TABLE dbo.Customer (CustomerId int PRIMARY KEY, CustomerName nvarchar(40) NOT NULL);
CREATE TABLE dbo.SalesOrder (OrderId int IDENTITY(1,1) PRIMARY KEY, CustomerId int NOT NULL, Amount decimal(8,2) NOT NULL);
CREATE INDEX IX_SalesOrder_Customer ON dbo.SalesOrder (CustomerId);
INSERT INTO dbo.Customer
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT 1)), CONCAT(N'Customer ', ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
INSERT INTO dbo.SalesOrder (CustomerId, Amount)
SELECT TOP (100000) 1 + ABS(CHECKSUM(NEWID())) % 1000, 10
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;

SET STATISTICS IO ON;
SELECT COUNT(*) AS Matches, SUM(o.Amount) AS Total
FROM dbo.SalesOrder AS o JOIN dbo.Customer AS c ON c.CustomerId = o.CustomerId;
SELECT COUNT(*) AS Matches, SUM(o.Amount) AS Total
FROM dbo.SalesOrder AS o JOIN dbo.Customer AS c ON c.CustomerId = o.CustomerId OPTION (LOOP JOIN);
SET STATISTICS IO OFF;

Both queries returned 100,000 matches and the same total. The default plan read 8 pages of Customer. The forced loop join read 2,000, which is 250 times more. A hint that helps one workload can hurt when the data changes. I treat hints as a targeted tool for experienced developers and DBAs. I write down why each one is there.

Detailed blog post

Introduction to Hierarchical Query

A common table expression is a named result that one statement can use. It can refer to itself, which is how recursion works. It is not a stored temporary table. A recursive CTE has an anchor query that picks the starting rows. A recursive member finds each next level, and UNION ALL joins the two.

CREATE TABLE dbo.Staff (StaffId int PRIMARY KEY, StaffName nvarchar(30) NOT NULL, ManagerId int NULL);
INSERT INTO dbo.Staff VALUES (1, N'Asha', NULL), (2, N'Ravi', 1), (3, N'Meera', 1),
                             (4, N'Kabir', 2), (5, N'Lata', 2), (6, N'Dev', 4);

WITH Chain AS
(
    SELECT StaffId, StaffName, ManagerId, 0 AS Depth, CAST(StaffName AS nvarchar(400)) AS PathText
    FROM dbo.Staff WHERE ManagerId IS NULL
    UNION ALL
    SELECT s.StaffId, s.StaffName, s.ManagerId, c.Depth + 1, CAST(c.PathText + N' > ' + s.StaffName AS nvarchar(400))
    FROM dbo.Staff AS s JOIN Chain AS c ON s.ManagerId = c.StaffId
)
SELECT REPLICATE(N'    ', Depth) + StaffName AS Org, Depth, PathText FROM Chain ORDER BY PathText;

Asha has no manager, so Asha sits at depth 0. Dev sits at depth 3 under Kabir, Ravi and Asha. Ordering by the path text keeps every manager directly above their reports. An INNER JOIN suits this example, but it is not a requirement for every recursive CTE.

Detailed blog post | Quiz with answer

Introduction to SQL Server Security

Picture a workplace where Tom needs read access to a financial folder. The folder is the securable, Tom is the principal and read access is the permission. Those three ideas start every SQL Server security conversation. Here is the same story in a small database, with a user who has no login.

CREATE TABLE dbo.Payroll (PayrollId int PRIMARY KEY, Amount decimal(10,2) NOT NULL);
INSERT INTO dbo.Payroll VALUES (1, 5000), (2, 6200);
CREATE USER Tom WITHOUT LOGIN;

EXECUTE AS USER = 'Tom';
BEGIN TRY SELECT COUNT(*) AS RowsSeen FROM dbo.Payroll; END TRY
BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorBeforeGrant; END CATCH;
REVERT;
GRANT SELECT ON dbo.Payroll TO Tom;
EXECUTE AS USER = 'Tom';
SELECT COUNT(*) AS RowsSeenAfterGrant FROM dbo.Payroll;
BEGIN TRY UPDATE dbo.Payroll SET Amount = 1; END TRY
BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorOnUpdate; END CATCH;
REVERT;
SELECT USER_NAME() AS BackToMe;

Before the grant, Tom’s SELECT failed with error 229. After GRANT SELECT, Tom read both rows. An UPDATE by Tom still failed with 229, because nobody granted it. The last query returned dbo, so REVERT put me back as myself. Grant only what a principal needs.

Detailed blog post | Quiz with answer

A five-video collection is not a course, it is five clear lessons you can take one at a time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.