Category: TSQL

Determining how much of your data file has been modified

SQL Server 2017 introduces a new column in the sys.dm_db_file_space_usage system table. The column is modified_extent_page_count, which tells how many pages have been modified in your data file since the last full backup. What is really interesting about this is that with

Posted in SQL 2017, TSQL Tagged with: , , , , , , ,

Finding Blocking Queries

One of the common problems with when diagnosing what appears to be a slow scenario where there may be blocking involved is determining what is blocking on SQL Server. Here is a query that I like to use. If there

Posted in TSQL Tagged with: , , , , ,

Temporary Stored Procedures #sproc

Yes, you can create temporary stored procedures by prefixing the name of the sproc with a #. The temporary stored procedure is sort of a “leave no trace” stored procedure that is only good for your session. If you use

Posted in TSQL Tagged with: , , , , , , ,

Drop a trigger after a specific date

  The question of how to delete a trigger after a specific date came up.  Here is what could be done. If you want to trigger to disappear after a specific date or time, you can just drop the trigger

Posted in TSQL Tagged with: , , ,

DBCC ShrinkDatabase – I want to shrink my database.

TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it. This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different

Posted in DBCC Commands Tagged with: , , , , , , ,

SQL Server Script to Display Job History

Its not always quick and easy in SQL Server to get a full list of the jobs that have been run, when they were run and how long they took. I created the following script to quickly check on the

Posted in SQL Server, TSQL Tagged with: , , , ,

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: , ,

How To Move TempDB Files

I was asked recently how to move TempDB on SQL Server. The question specifically was how to move the TempDB files to different drives. At that point I realized that I don’t have that posted to my blog, so here we go.

Posted in TSQL Tagged with: , , ,

TSQL JOIN Types Poster (Version 4)

So many times I have been asked for help with a query, where the question really comes down to the understanding of the difference between INNER and LEFT or RIGHT JOINs. I created this poster a few years ago and

Posted in TSQL Tagged with: , , , , , , ,

Using The TSQL EOMONTH Function

Here is a quick video training on how to use the T-SQL EOMONTH function on SQL Server. This was originally part of my free SQL query training for the 70-461 certification exam. The EOMONTH function returns the last day of a given

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