Will It Compress?

Question

Post reply

Will It Compress?

Andy Warren

SSC Guru

Points: 119902
More actions
September 1, 2016 at 11:46 pm

#329714

Comments posted to this topic are about the item Will It Compress?
Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
September 2, 2016 at 2:38 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 1

SPARSE is not a datatype but a property.

So, the correct answer is wrong!

SELECT * FROM sys.types

WHERE is_user_defined='0'

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 2

"we attempt to compress the table using this statement: sp_estimate_data_compression_savings ..." is wrong because the sp "sp_estimate_data_compression_savings" just is an "estimate" and it doesn't compress anything!

Neil Burton SSC-Insane Points: 22999 More actions · Answer 3

Carlo Romagnano (9/2/2016)
SPARSE is not a datatype but a property.....

+1

There's nothing in the question that suggests that there are SPARSE columns. It's not something that would jump out from 'all possible datatypes' either. As far as I could see, there are no datatypes that are incompatible with compression.

The answer might as well have been 'fooled you, the server was unplugged.'

At least Andy's back on form. I was getting worried because I've got a couple of his recent ones right 🙂

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

sipas Hall of Fame Points: 3828 More actions · Answer 4

BWFC (9/2/2016)
Carlo Romagnano (9/2/2016)
SPARSE is not a datatype but a property.....
+1
There's nothing in the question that suggests that there are SPARSE columns. It's not something that would jump out from 'all possible datatypes' either. As far as I could see, there are no datatypes that are incompatible with compression.
The answer might as well have been 'fooled you, the server was unplugged.'
At least Andy's back on form. I was getting worried because I've got a couple of his recent ones right 🙂

+1

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 5

excellent question...

But sparse column is not a data type its a property on a column, it was very hard to think that way, got the answer wrong but it was definitely a good learning though specifying sparse column would have made easier to answer..!!

carl.eaves Mr or Mrs. 500 Points: 512 More actions · Answer 6

carl.eaves

Mr or Mrs. 500

Points: 512

September 2, 2016 at 5:32 am

#1898964

+1 Sparse is not a datatype.

sknox SSChampion Points: 12388 More actions · Answer 7

Carlo Romagnano (9/2/2016)
SPARSE is not a datatype but a property.
So, the correct answer is wrong!
SELECT * FROM sys.types
WHERE is_user_defined='0'

THIS.

I understand why Andy didn't specify SPARSE, as that would be a giveaway. I also understand why he didn't open it up to all properties/ attributes, as then it would get way too complex (do we count a sampling of the different possible lengths of CHAR() and NCHAR() for example?)

So I think this is one of those interesting questions that doesn't work as a QotD.

Andy Warren SSC Guru Points: 119902 More actions · Answer 8

Carlo, agree that "we attempt to compress the table using" should have been said differently.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Andy Warren SSC Guru Points: 119902 More actions · Answer 9

Good morning all and happy Friday! I agree that sparse isn't a data type - funny how you can think it and write it and still miss the obvious. Clearly I could have done better. If it was "all datatypes and attributes" it would have been closer, but still tough to figure out. My goal is never to write a "gotcha", just to challenge you to see the whole board.

Thanks for trying the question and all the comments, always appreciated.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Bobby Russell SSCrazy Points: 2801 More actions · Answer 10

I missed it for a different reason. I was guessing he intended sparse column so I knew it wouldn't compress but the statement isn't the cause of the error. The cause of the error is the column in the table so my thoughts were answer one and the last as correct. I had switch from answer two to one. Shoots self in foot for second guessing...

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 11

I do not regret that I have got it wrong. I learned something new, thanks Andy. 🙂

The question has been imprecise specified, because only SQL Server Enterprise Edition

supports Data Compression and DB Engine SPROC sp_estimate_data_compression_savings.

So e.g. in the MS SQL 2014 Standard command ends with errors Msg 534 and Msg 7738.

At last, I did not consider Sparse column as the data type.

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Carlo Romagnano (9/2/2016)
SPARSE is not a datatype but a property.
So, the correct answer is wrong!
SELECT * FROM sys.types
WHERE is_user_defined='0'

I agree - SPARSE has nothing to do with type.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 13

sipas (9/2/2016)
BWFC (9/2/2016)
Carlo Romagnano (9/2/2016)
SPARSE is not a datatype but a property.....
+1
There's nothing in the question that suggests that there are SPARSE columns. It's not something that would jump out from 'all possible datatypes' either. As far as I could see, there are no datatypes that are incompatible with compression.
The answer might as well have been 'fooled you, the server was unplugged.'
At least Andy's back on form. I was getting worried because I've got a couple of his recent ones right 🙂
+1

And + another 1

Tom