Tag Info

Hot answers tagged

14

It's not a matter of putting compression on a primary key, but instead a matter of putting the compression on the clustered index. For SQL Server, a clustered index is organizing the table's physical structure on that index. Or, in shorter form, the clustered index is the table. This means that compressing the clustered index and compressing the table are ...


11

Both page and row compression do not compress BLOBs. Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately. If you want to compress BLOBs you need to store them as VARBINARY(MAX) and apply your stream ...


5

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda's dynamic. Remember that Barracuda also supports old redundant and compact row formats. The article you ...


4

There is no equivalent proc so what I have been doing recently is creating a copy of the table in tempdb using SELECT...INTO, eg SELECT TOP 10 PERCENT * INTO tempdb.dbo.yourTable FROM dbo.yourTable then apply the clustered columnstore. If your table is too large to fit in your tempdb (as I might expect for a datawarehouse table), transfer a percentage ...


3

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use: ALTER TABLE table_name MOVE ...


3

Here are some thoughts on this: Do you know that you should get better compression on the strings that you have tested with? Have you tested those same strings by gzipping outside of .NET? Such as on Linux / CygWin -- DOS port of UNIX utilities / PHP / etc? If you have updated your system with .NET 4.5, then you are using the updated GZipStream. This is ...


2

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file? No this cannot damage your Log file. Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it? Yes database compression affects I/O and as far as my experience ...


2

Have you tested the lz6 compression level? lz6 provides less overall compression but it has a index on values which you can pull out without going through the overhead and cost of uncompressing the whole file. I If you cannot give up any compression at all, have you tried a simple json document store DB such as Mongo? I feel using filestream in SQL ...


2

COMPACT is format supported by Antilope. It stores first 768 bytes of BLOB in case its value doesn't fit in page. DYNAMIC is almost the same as COMPACT except only 20 bytes for each BLOB field is used. Benefits - more BLOB fields are possible in a record. COMPRESSED is used for compressed tables. Hence its benefits.


1

Below is a complete script that works as expected without the error. :setvar IsArchive "" :setvar Suffix _Partitioned CREATE SCHEMA Orders; GO CREATE SCHEMA ArchivedOrders; GO CREATE SEQUENCE Orders.NextOrderId; GO CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS RANGE RIGHT FOR VALUES(); GO CREATE PARTITION SCHEME ...


1

I'd suggest you split this large column off into its own table. While you're there take out any other columns which are only used once in a blue moon. (The smaller your row the faster your queries will run.) I'll take your current table as MyData. Rename it to, say, MyData_Crucial. Let's call the new table MyData_Sometimes. Create this view: create ...



Only top voted, non community-wiki answers of a minimum length are eligible