April 27, 2021 at 5:11 pm
I have a base table with 4 varbinary(max) columns (COMPRESSed columns that were originally nvarchar(max)). As full background, there is an INSTEAD OF INSERT trigger to transparently handle converting the incoming nvarchar(max) to varbinary(max). And a view that is used for all standard SELECTs that transparently converts the varbinary(max) back to nvarchar(max).
I wanted to get the max lengths of the compressed columns for the first, say, 200 rows. But when I try to get the Estimated Query Plan below, SSMS just spins endlessly. However, TOP (1) does run ... but anything else, even TOP (2), just hangs.
/* just spins endlessly, waited over 15 min and still no estimated exec plan */
SELECT MAX(DATALENGTH(varbinmax_col1)), MAX(DATALENGTH(varbinmax_col2)),
MAX(DATALENGTH(varbinmax_col3)), MAX(DATALENGTH(varbinmax_col4))
FROM (
SELECT TOP (200) *
/* SELECT TOP (1) * --this works completely: plan gets created and run */
FROM dbo.base_table_with_compressed_varbinmax_columns
) AS x
Hmm, this hangs too, interesting:
SELECT MAX(DATALENGTH(varbinmax_col1))
FROM dbo.base_table
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 27, 2021 at 5:25 pm
How long does
SELECT DATALENGTH(varbinmax_col1)
FROM dbo.base_table
take to run?
April 27, 2021 at 6:05 pm
How long does
SELECT DATALENGTH(varbinmax_col1)
FROM dbo.base_tabletake to run?
Here's the TIME statistics for:
SELECT TOP (200) DATALENGTH(varbinmax_col1)
(200 rows affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1 ms.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply