SQL Server Tables – BTree or Linked List?

Is a clustered table in SQL Server a BTree or Linked List? When you first learn about the structure behind clustered indexes in SQL Server, you find out that the clustered index is structured as a type of B+Tree where queries that

Posted in Corruption, TSQL Tagged with: , ,

Week 4 – Building the Corrupt Database

The following are the steps that I went through to create a more difficult Database Corruption Challenge for Week 4. Here is how I built the database, nothing fancy, however I did add an extra FILEGROUP called [UserObjects]. Notice, that

Posted in Corruption Tagged with: , , , , , , ,

Corruption Challenge 1 – An alternative solution

After posting the winning solution for Corruption Challenge 1 from Brent Ozar, I realized that he and I both solved the corruption by using the REPAIR_ALLOW_DATA_LOSS option on CheckDb. A very nasty move, however it did repair the corruption.  

Posted in Corruption Tagged with: , , , , ,

Corruption Challenge 1 – how I corrupted the database

Since the corruption challenge completed yesterday, I have had several request asking how I created the corrupt database. So here is the script that I used to create the Database Corruption Challenge 1. First the initial setup. Most of this

Posted in Corruption Tagged with: , , , , , , ,


After learning the INNER JOIN when querying SQL Server, the LEFT OUTER JOIN and RIGHT OUTER JOIN are often what people attempt to learn next. The reason that I say attempt is that most people don’t get OUTER JOINs right the

Posted in 70-461 Training Tagged with: , , , , , , ,

Using the TSQL IIF Function

Here is a quick video tutorial on how to use the IIF function on SQL Server 2012, SQL Server 2014 0r newer. This was originally part of my free SQL query training for the 70-461 certification exam. Here is the sample

Posted in 70-461 Training Tagged with: , , , , , , ,

Today’s Training: SQL Queries Section 1 Review

As part of our weekly free SQL Query Training, we have split the 70-461 curriculum into 4 sections to prepare everyone to take the certification test later in the year. Today’s session is to review the first 25% of the

Posted in Classes, Query Certification Tagged with: , , , , ,

TSQL OUTPUT Clause With UPDATE Statements

Earlier in the week I added a blog entry on using the OUTPUT Clause on INSERT Statements, today I will expand it by exploring the use of the OUTPUT clause with UPDATE statements. Generally when you do an UPDATE statement you only

Posted in 70-461 Training, TSQL Tagged with: , , , , ,

Sample Database for Common Table Expressions

As I have presented my Common Table Expressions presentation many times, and as part of writing the Common Table Expressions Book I have created some sample database scripts to use. Once you load up the script into SSMS and refresh the databases

Posted in CTE Tagged with: , , , , , , ,

DBCC CleanTable

Being day eight of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CLEANTABLE. Many times I have worked on a database that has evolved over 10 or more years of changes from different developers and DBAs.

Posted in DBCC Commands Tagged with: , , , ,