When uncompressing data, make sure to use the correct function

SQL Server has offered data compression for a while now, you could either use PAGE compression or ROW compression, you had no ability so specify which columns.  That changed in SQL Server 2016

SQL Server 2016 added the ability to compress data yourself by using the COMPRESS function. The COMPRESS function compresses the input expression using the GZIP algorithm. The result of the compression is byte array of type varbinary(max).


Let's take a quick look at how this function works

We will create a table, insert one row and then update the SomeOtherColumn value by using the COMPRESS function on the SomeColumn column

CREATE TABLE test (Somecolumn varchar(200), SomeOtherColumn varbinary(max))
go

INSERT test VALUES ('aaaaaaaaaaaaa', null)

UPDATE test
SET SomeOtherColumn = COMPRESS(Somecolumn)
go

SELECT Somecolumn,SomeOtherColumn 
FROM test

Here is what the output looks like

What if if told you now to create a new table by uncompressing the data via a SELECT INTO query followed by dropping the old table

No problem, if there is a COMPRESS function then surely there must be an UNCOMPRESS function... right?  Let's find out......

SELECT Somecolumn,CAST( UNCOMPRESS(SomeOtherColumn) AS varchar(max))  AS SomeOtherColumn
INTO SomeNewTable
FROM test

DROP TABLE test

SELECT *
FROM SomeNewTable

Wrong, while the UNCOMPRESS function exists, it is not the correct function

Hopefully you ran a test before just dropping a table after doing select into

The correct function is DECOMPRESS

Here is what it all looks like

SELECT Somecolumn,SomeOtherColumn,
 CAST( DECOMPRESS(SomeOtherColumn) AS varchar(max)) AS CastedDecompressed,
 DECOMPRESS(SomeOtherColumn) as Decompressed FROM test

 SELECT Somecolumn,SomeOtherColumn, 
CAST( UNCOMPRESS(SomeOtherColumn) AS varchar(max)) AS CastedUncompressed,
 UNCOMPRESS(SomeOtherColumn) as Uncompressed FROM test

This is the output

As you can see the UNCOMPRESS function returns a bunch of nonsense while the DECOMPRESS function works as expected

So the question is really wth is the uncompress function?

The 15 most popular posts in 2017

Another year is almost over. As the last post of this year I decided to share with you the 15 most popular posts in 2017. I just used Google Analytics to grab this info, I excluded the home page from the top 15.  Some of these post are so old, we didn't even have windowing functions in SQL Server when these were written.,....

Here they are in order of popularity

01. Ten SQL Server Functions That You Hardly Use But Should
A post from 2007 showing some hardly used functions like NULLIF, PARSENAME and STUFF

02. Five Ways To Return Values From Stored Procedures
A very old post that shows you five ways to return values from a stored proc

03. Your lack of constraints is disturbing
A post showing the type of constraints available in SQL Server with examples

04.  Use T-SQL to create caveman graphs
One of the shortest post on this site, show you how you can make visually appealing output with a pipe symbol

05. Convert Millisecond To "hh:mm:ss" Format
A very old post showing you how to convert from milliseconds to "hh:mm:ss" format

06. T-SQL Tuesday #92, Lessons learned the hard way
Some of my mistakes put together in 1 post

07. SQL Server 2017: SQL Graph
Me messing around with the Graph functionality in SQL Server 2017

08. Query Analyzer Trick
A very old post explaining how you can drag the columns into the query window from object explorer. I think by now everyone knows this one right?

09. ISO-11179 Naming Conventions
A very old post linking to the ISO-11179 Naming Conventions document

10. Not sure that I like the message from Online Resumable Index Rebuild in SQL Server 2017
After playing qround with resumable index rebuilds I think the kill state and severe error occurred is a little over the top

11. Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection
This is my recap of the chalkboard session with the SQL Server team at the SQL Server PASS summit in Seattle.

12.  Five great SQL Server GitHub repos that every SQL Server person should check out
What the titles says, 5 GitHub repos you need to use

13. Chaos Isolation Level In SQL Server
This is linked from  dba.stackexchange.com, it is kind of interesting because it was a silly post I made

14. T-SQL Tuesday #86: String or binary data would be truncated
A pet peeve of many people

15. Microsoft releases SQL Server Horizontica, a Vertica killer
Probably the best version of SQL Server ever created, was only available for download for 24 hours.


That is all for this year... see you in 2018...  the year of Linux on the desktop, DevOps, Ethereum, AR/VR, bendable phones and much more......

,

Use T-SQL to create caveman graphs

I found this technique on Rich Benner's SQL Server Blog: Visualising the Marvel Cinematic Universe in T-SQL and decided to play around with it after someone asked me to give him the sizes of all databases on a development instance of SQL Server

The way it works is that you take the size of the database and then divide that number against the total size of all databases. You then use the replicate function with the | (pipe) character to generate the 'graph'  so 8% will look like this ||||||||

You can use this for tables with most rows, a count per state etc etc. By looking at the output the graph column adds a nice visual effect to it IMHO


Here is what the final query looks like

SELECT 
       database_name = DB_NAME(database_id) 
     , total_size_GB = CAST(SUM(size) * 8. / 1024/1024 AS DECIMAL(30,2))
  , percent_size = (CONVERT(decimal(30,4),(SUM(size) /
     (SELECT SUM(CONVERT(decimal(30,4),size))  
     FROM sys.master_files WITH(NOWAIT)))) *100.00)
  , graph = replicate('|',((convert(decimal(30,2),(SUM(size) / 
    (SELECT SUM(CONVERT(decimal(30,2),size))  
     FROM sys.master_files WITH(NOWAIT)))) *100)))
FROM sys.master_files WITH(NOWAIT)
GROUP BY database_id
ORDER BY 3 DESC

And here is the output (I blanked out the DB name in the output below), there are 48 databases, 15 of them show a bar, the rest don't because they use less than 0.5% of space.

Do you see how you can quickly tell visually that the top DB is about twice as large as the next DB? 

Those guys in Lascaux would have been so proud, only if they could see this  :-)

How to update 2 tables with 1 statement in SQL Server..the hard way....

Every now and then you will get someone asking how to update two tables with one statement in SQL Server. The answer is usually, no that is not possible... the person then walks away muttering something about how MySQL allows it.

So I decided to try to see if I could update two tables with one statement. I decided to try a couple of different things

• view
• common table expression
• indexed view
• instead of trigger

In order to begin we need two tables, each table will have one row of data so we can update those rows

CREATE TABLE test1(id int primary key, someVal char(1) not null)
CREATE TABLE test2(id int primary key, someVal char(1) not null)

INSERT test1 VALUES(1,'a')
INSERT test2 VALUES(1,'a')
Go

Now we can start with plan A... the mighty view

CREATE VIEW Testview1
AS
SELECT t1.*,t2.SomeVal as SomeVal2
FROM test1 t1
JOIN test2 t2 on t1.id = t2.id

Running a simple select against the view

SELECT * FROM testview1

id someVal SomeVal2
1 a         a

Time to update the view

UPDATE testview1 SET SomeVal = 'b', SomeVal2 = 'b'
WHERE id = 1

Msg 4405, Level 16, State 1, Line 1
View or function 'testview1' is not updatable because the modification affects multiple base tables.

As you can see that didn't work since even though you are updating one view, you are still trying to update two tables.

Time to implement plan B... the versatile common table expression

Since you can update a common table expression, can you update a common table expression if it updates more than one table?  Let's try it out

;WITH cte AS (SELECT t1.*,t2.SomeVal as SomeVal2
FROM test1 t1
JOIN test2 t2 on t1.id = t2.id)

UPDATE cte SET SomeVal = 'b', SomeVal2 = 'b'
WHERE id = 1

Msg 4405, Level 16, State 1, Line 1
View or function 'cte' is not updatable because the modification affects multiple base tables.

So plan B ended like plan A... also pretty much the same error message


Plan C.... the mighty indexed view.

If you ever tried working with indexed views you are probably busy cursing at the moment.
Let's create this view and add an index

CREATE VIEW testviewIndexed 
WITH SCHEMABINDING  
AS  
SELECT t1.id, t1.someVal, t2.SomeVal as SomeVal2
FROM dbo.test1 t1
JOIN dbo.test2 t2 on t1.id = t2.id
GO


CREATE UNIQUE CLUSTERED INDEX IDX_V1   
    ON testviewIndexed (id);  

Fingers crossed....but if the regular view didn't work..why would this?

UPDATE testviewIndexed SET SomeVal = 'b', SomeVal2 = 'b'
WHERE id = 1

Msg 4405, Level 16, State 1, Line 2
View or function 'testviewIndexed' is not updatable because the modification affects multiple base tables.

That is right... plan C is also bad, very bad.

So we are left with one more thing... plan D (as in Denis)....

We will now use an instead of trigger on the regular view from before

CREATE TRIGGER InsteadTrigger on testview1
INSTEAD OF UPDATE
AS
BEGIN

 UPDATE t  SET t.SomeVal = i.SomeVal
 FROM INSERTED i JOIN test1 t on i.id = t.id

 UPDATE t  SET t.SomeVal = i.SomeVal2
 FROM INSERTED i JOIN test2 t on i.id = t.id
END

GO

Let's see what happens now.. fingers crossed

UPDATE testview1 SET SomeVal = 'b', SomeVal2 = 'b'
WHERE id = 1

No error, let's see what is in the table

SELECT * FROM test1
SELECT * FROM test2

id someVal SomeVal2
1 b         b

And as you can see, you can update two tables with one statement.

Should you really go through all this trouble because you don't want to do something like this?

BEGIN TRAN

 UPDATE test1 SET SomeVal = 'c'
 WHERE id = 1

 UPDATE test2 SET SomeVal = 'c'
 WHERE id = 1

COMMIT-- hmm where is the error checking/begin try xact_state()?

Nope I would not go this route,  if the table changes you now need to also update the trigger. What if someone drops the trigger? There are too many ways this can go wrong