The awesome Erin Stellato tweeted this morning about discovering a problem with sp_estimate_data_compression_savings on Azure SQL DB. Although compression is supported on Azure, you cannot run this procedure to get an idea of the potential space savings. She even requested that people up-vote a request to add this procedure to Azure.
I needed to verify this for myself and at first I thought Erin was wrong, because my Azure SQL DB was not complaining about an unknown procedure when I tried to execute sp_estimate_data_compression_savings. It just complained about my parameters. Only after I had all my parameters entered correctly, did I get an error message about the procedure not being supported in this version.
So I looked at the source code and saw there was a simple test for edition, that I could just comment out. But then I got another error about an unknown function called sys.generate_index_ddl. I could not find that function anywhere! I even connected to a onprem SQL Server 2017 and still couldn't find it. So I finally started my onprem instance in single user mode and went to the resource database and found the function. It took a while to get it in a format that was workable, but I was then able to create a similar function, called fn_generate_index_ddl in my Azure SQL DB. But then that function called another unknown function and I had to repeat the process. All told, there were four 'hidden' functions I had to create, after stealing their definitions from the mssqlsystemresource database in an onprem instance.
But I got it to work!
And now I have a procedure called usp_estimate_data_compression_savings that runs on Azure. It took me most of the afternoon to get all the pieces to work together, but it was a very satisfying feeling to finally see results.
Oh, and if you'd like to create this usp_estimate_data_compress_savings for yourself, you can download my code from here.