1

Topic: Type of compression for an index (data_compression): ROW or PAGE.

Compression of numerical types is produced for type ROW in the core, and varchar, etc. is not compressed. Compression is produced in line.
Compression at first is produced for type PAGE in line, then then prefix compression, then dictionary compression. In this case line types are well compressed. So often repeating substrings are located in special area of page, and already links to these substrings are stored in columns in .
Question in the following.
There will be an index on the big table of type nonclustered index on dbo. Table (type_id, object_id, dt) where columns have following types: type_id int, object_id int, dt datatime.
What type of compression to select for such index? If sense in compression PAGE? After all all columns of an index of integral types. It seems to me that for them compression of a prefix and compression of the dictionary or at all will not be is produced, or links to a prefix and the dictionary take more places, than the data.
Can be for such index  not to produce compression?

2

Re: Type of compression for an index (data_compression): ROW or PAGE.

https://docs.microsoft.com/en-us/sql/re … ansact-sql

3

Re: Type of compression for an index (data_compression): ROW or PAGE.

Gavrilenko Sergey Alekseevich, thanks! I read about this procedure. But for it at first it is necessary to create an index, and then by means of this procedure it will be already possible to check up what scoring from compression we receive at different types. And if it is other type an index it will be necessary .

4

Re: Type of compression for an index (data_compression): ROW or PAGE.

Business not only in compression as that. Repeatedly encountered a situation when the clustered index is compressed, you build one more covering not cluster for what that of frequent request and you forget to add compression. The optimizer all the same goes on the oblate cluster. And all the same asks that index which like as soon as that created. And if you create its compressed - everything is all right, both works faster and runs on the necessary index.

5

Re: Type of compression for an index (data_compression): ROW or PAGE.

Prolog;
You can receive the adequate answer only making the test for your system.
And if  as a rule page proves to be on  is better.

6

Re: Type of compression for an index (data_compression): ROW or PAGE.

LogrusAS wrote:

Business not only in compression as that. Repeatedly encountered a situation when the clustered index is compressed, you build one more covering not cluster for what that of frequent request and you forget to add compression. The optimizer all the same goes on the oblate cluster. And all the same asks that index which like as soon as that created. And if you create its compressed - everything is all right, both works faster and runs on the necessary index.

Probably  "covering" contained so much fields;
That the oblate cluster weighed less smile))

7

Re: Type of compression for an index (data_compression): ROW or PAGE.

There is a table (7M row), here its sizes depending on compression type:
NONE ROW PAGE COLUMNSTORE COLUMNSTORE_ARCHIVE
size (MB) 1672 656 495 173 87
(data+index) 1507+165 513+142 404+91 173+0 87+0
CREATE TABLE [dbo]. [123] (
[1] [int] NOT NULL;
[2] [smallint] NULL;
[3] [smallint] NULL;
[4] [int] NULL;
[5] [bigint] NULL;
[6] [bigint] NULL;
[7] [bigint] NULL;
[8] [bigint] NULL;
[9] [bigint] NULL;
[10] [bigint] NULL;
[11] [bigint] NULL;
[12] [bigint] NULL;
[13] [bigint] NULL;
[14] [bigint] NULL;
[15] [bigint] NULL;
[16] [bigint] NULL;
[17] [bigint] NULL;
[18] [bigint] NULL;
[19] [bigint] NULL;
[20] [bigint] NULL;
[21] [bigint] NULL;
[22] [bigint] NULL;
[23] [smallint] NULL;
[24] [smallint] NULL;
[25] [smallint] NULL;
[26] [smallint] NULL;
[27] [char] (2) NOT NULL;
[28] [char] (2) NOT NULL;
[29] [bigint] NULL;
[30] [bigint] NULL;
[31] [datetime] NULL
) ON [PRIMARY]
GO

8

Re: Type of compression for an index (data_compression): ROW or PAGE.

Found average on the size the table (15,955,742 lines). Created three indexes identical on column collection (fillfactor=100, pad_index=off) c types int, datetime, bigint, but with different types of compression. The size of indexes the following:
Compession, Size (KB)
NONE, 416288
ROW, 254376
PAGE, 141616
All thanks!

9

Re: Type of compression for an index (data_compression): ROW or PAGE.

Prolog wrote:

All thanks!

for you the size is important?? Carry out the test on inserty\selekty, and that as though then was not surprises.