Welcome back to another quick blog, about something I just learned about.

Am playing around with Always Encrypted for the first time. I was just following along the basic tutorial and encrypted some columns in my AutoDealershipDemo database. But then I decided to go crack open the data page using my friend DBCC PAGE.

WITH TABLERESULTS

Because I only ever use DBCC PAGE() once every so often, I did a quick search to refresh on the syntax. And what I found was someone who used something I’d never seen before…

DBCC PAGE(aa, bb, cc, dd) WITH TABLERESULTS

“WITH TABLERESULTS”? What is this…?

WOW – for me, this makes the output SO much easier to consume!!!

And what of that encrypted data?

And if you’re curious, here’s what I was really after.

See how I had encrypted the VIN, InvoicePrice, MSRP, and DateReceived columns in my test table. Pretty cool to see it scrambled up and interesting to see how it consumed a heck of a lot more space in-row. 

DBCC Commands

Another quick search tells me that you can also use “WITH TABLERESULTS” with other DBCC commands too. I didn’t look much more into it since I got what I wanted, but thought I’d share this neat tidbit.

Thanks for reading!

Found an interesting tidbit while working on a compression diagnostic script, and thought I’d write a quick blog.

Was refreshing myself on all of the output of sys.dm_db_index_physical_stats and the nuances, when I found an interesting tidbit about “compressed_page_count.”

For heaps, newly allocated pages aren’t PAGE compressed.

A heap is PAGE compressed under two special conditions: when data is bulk imported or when a heap is rebuilt. Typical DML operations that cause page allocations aren’t PAGE compressed.

Rebuild a heap when the compressed_page_count value grows larger than the threshold you want.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver16

Well then… moral of the story folks, is that if you have heaps that are PAGE compressed, you may not be getting the benefits you think you’re getting if you’re not executing rebuilds regularly!

Thanks for reading.

Recently someone asked my wife Deborah if she knew if my sp_helpExpandView utility procedure could also work with triggers. Great question – I rarely work with triggers, especially nowadays, so I had no idea… and finally found some time and motivation to dig into it deeper.

TL;DR – How Does sp_helpExpandView Work with Triggers?

1. If you run sp_helpExpandView whose parameter IS a trigger, you’ll get information about the objects that the trigger references. However, you will not get information about the table that the trigger is associated with (which could be derived from another query).
   
2. If you run sp_helpExpandView whose parameter is a table that HAS a trigger, you will NOT get any information about that trigger being present. You’ll have to write a different query for that.
   
3. If you run sp_helpExpandView whose parameter is a table that is UTILIZED BY a trigger, you WILL get information about that trigger.

Digging Deeper

To dig deeper, I set up a quick prototype with two tables and a basic INSERT trigger.

USE TempDB;

GO

CREATE TABLE dbo.TableWithADMLTrigger (

	RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

	MyValue VARCHAR(50),

	MyTimestamp DATETIME DEFAULT(GETDATE())

);



CREATE TABLE dbo.TriggerAuditTable (

	RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

	InsertedRecID INT,

	MyValue VARCHAR(50),

	MyTimestamp DATETIME 

);

GO



CREATE OR ALTER TRIGGER dbo.tr_InsertTriggerForAuditing 

	ON dbo.TableWithADMLTrigger

AFTER INSERT

AS

BEGIN

	INSERT INTO dbo.TriggerAuditTable (

		InsertedRecID, MyValue, MyTimeStamp

	)

	SELECT inserted.RecID, inserted.MyValue, inserted.MyTimestamp

	FROM inserted

END

GO



INSERT INTO dbo.TableWithADMLTrigger (MyValue) VALUES ('alpha');

GO 3



SELECT *

FROM dbo.TableWithADMLTrigger;



SELECT *

FROM dbo.TriggerAuditTable;

GO

Now, let’s try running sp_helpExpandView against each entity.

EXEC sp_helpExpandView 'dbo.TableWithADMLTrigger';
GO

EXEC sp_helpExpandView 'dbo.TriggerAuditTable';
GO

EXEC sp_helpExpandView 'dbo.tr_InsertTriggerForAuditing';
GO

The reason for this has to do with the underlying code’s use of sys.dm_sql_referenced_entities(). There are actually two of these DMFs in SQL Server, sys.dm_sql_referenced_entities() and sys.dm_sql_referencing_entities(). Note the critical difference, referencED and referencING.

Confused? Me too – which is why I have to re-explain the difference to myself every time I revisit this topic. Hopefully this summary helps you.

• Referenced Entities: Return all objects that are referenced by the object parameter passed in.
  
  ex: dbo.MyStoredProcedure that has a SELECT * FROM dbo.MyTable, dbo.MyTable is referenced by the stored procedure and would appear in the resultset from a query against sys.dm_sql_referenced_entities()
  
• Referencing Entities: Returns all objects that are referencing the object parameter passed in.
  
  ex: If dbo.MyTable is passed, then dbo.MyStoredProcedure would appear in the resultset from a query against sys.dm_sql_referencing_entities()

Interestingly enough, not all objects are supported by both DMFs. See the Remarks section of the documentation of either DMF for a full table. But the consequence for sp_helpExpandView is that if you use sp_helpExpandView against tables that are both referenced by or referencing a trigger, it will not yield any output.

Partial Solution via sys.dm_sql_referencing_entities()

You can use sys.dm_sql_referencing_entities() to see triggers that reference a table. Here’s more example code.

DECLARE @TableName NVARCHAR(256) = 'dbo.TriggerAuditTable';



SELECT-- DISTINCT

	DB_NAME() AS database_name,

	@TableName AS table_name,

	COALESCE(schemas.name, dm_sql_referencing_entities.referencing_schema_name) + '.' + dm_sql_referencing_entities.referencing_entity_name AS object_referencing_this_table, 

	COALESCE(schemas.type, NULL) AS referencing_object_type,

	dm_sql_referencing_entities.referencing_id AS referencing_object_id

FROM sys.dm_sql_referencing_entities(@TableName, 'OBJECT')

LEFT OUTER JOIN (

	SELECT objects.object_id, schemas.name, objects.type

	FROM sys.objects

	INNER JOIN sys.schemas

		ON objects.schema_id = schemas.schema_id

) schemas

	ON dm_sql_referencing_entities.referencing_id = schemas.object_id;

You can combine the above plus code that returns all triggers defined on a given table, to give you a bigger picture of trigger impact and relationships. Someday I may try to integrate this into sp_helpExpandView. Or I would absolutely welcome and credit anyone who wants to modify the code and issue a Pull Request on Github.

Thanks for reading.