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 strange case of the missing indexes....

This past week I needed to run some queries on production to verify there were indexes added on a table. There were several scripts that needed to be run and the last one was the addition of the indexes.  The query given to me was something like the following

SELECT *
FROM LinkedServerName.DatabaseName.sys.indexes
WHERE object_id =(OBJECT_ID('TableName'))

So I ran the query..nothing. Aha maybe they are still running the scripts before that, setting up replication, snapshotting the table etc etc. I will check again in a bit I thought.

Then I checked 2 more times nothing. So I then contacted them and asked if they ran everything..yes they said and no errors.  Mmm, okay..what could be wrong. The only way I can access this server is through a linked server call. I decided to look at the query again.... bingo.. I see what the problem is.....

Let's take a look. First create the following database with 2 tables and 2 indexes on the Test table

CREATE DATABASE Test1
GO


USE Test1
GO

CREATE TABLE Foo(ID int)
GO

CREATE TABLE Test(ID int, ID2 int, ID3 int)
GO

CREATE INDEX ix_Test on Test(ID)
CREATE INDEX ix_Test2 on Test(ID2)

Now if you run the following query against the sys.indexes object catalog view

SELECT  FROM sys.indexes
WHERE object_id = OBJECT_ID('Test')

You will get back the following information

object_id	name	index_id	type	type_desc
901578250	NULL	0	0	HEAP
901578250	ix_Test	2	2	NONCLUSTERED
901578250	ix_Test2	3	2	NONCLUSTERED

As you can see we have information about our two indexes and the table itself, since there is no clustered index on this table, we get a row back that shows that the table is a heap

Now let us connect to another database on the same server, in this case we will use the tempdb database

Create a table with the same name

USE tempdb
GO

CREATE TABLE Test(foo int)
GO

Now run the same query again but point to the Test1 database

SELECT * FROM Test1.sys.indexes
WHERE object_id = OBJECT_ID('Test')

And you get nothing back.  What does the OBJECT_ID() function return?

SELECT OBJECT_ID('Test')

For me it returns the number 965578478. The problem is that that object_id is the id for that object in the tempdb database, not for the one in the Test1 database

So what can you do?  There are two ways to do this

One way is to join sys.indexes with the sys.tables object catalog view

Here is what the query looks like

SELECT i.* 
FROM Test1.sys.indexes i
JOIN Test1.sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'Test'

Running the query like that displays the correct information

object_id	name	index_id	type	type_desc
901578250	NULL	0	0	HEAP
901578250	ix_Test	2	2	NONCLUSTERED
901578250	ix_Test2	3	2	NONCLUSTERED

If you want to use this against a linked server, use 4 part notation, just prefix Test1.sys with the linked server name, I used LinkedServerName as an example

SELECT i.* 
FROM LinkedServerName.Test1.sys.indexes i
JOIN LinkedServerName.Test1.sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'Test'

That query with the join between sys.indexes and sys.tables can be used for a linked server as well as a different database on the same instance, if you just have to go to another database like we have done before, you can simplify it like this

SELECT  * FROM Test1.sys.indexes
WHERE object_id = OBJECT_ID('Test1.dbo.Test')

Basically, you pass in the database name, schema name and object name to the OBJECT_ID() function

That's all for today..

How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008

How do you find out the recovery model for all the databases on your SQL Server box?
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)


--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases


--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases