id BIGINT -- PRIMARY KEY,
imei_number BIGINT NOT NULL,
gps_datetime datetime NOT NULL,
event_id INT,
speed INT,
latitude decimal(10,6),
longitude decimal(10,6),
raw_data varchar NULL
Where imei_number = 1234
And gps_datetime between 'date1' and 'date'
and event_id in 1,5,14 --this is sometimes not used
imei_number
gps_datetime
id
imei_number
event_id
id
Id
ASKER
ASKER
ASKER
Yes, typically it improves performance, since I/O is usually the bottleneck, not CPU. If, somehow, you have relatively a CPU shortage and lots of I/O excess capacity (extraordinarily rare), then compression would hurt more than help.
Indexes 1 and 14 should be page compressed; index 16 only row compressed (row compression is basically free unless you are severely CPU constrained).
ASKER
Yep, that makes sense. Usually ROW saves a little bit, but not always.
Btw, for any pages where page compression ends up not helping, SQL will then not compress that page. That is, the SQL algorithm is sophisticated enough to only actually implement the compression on a given page if it saves a certain amount of space.
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
btw, here the tables primary key should be: (gps_datetime, imei_number, id).