SQL Server

SQL Server: key-value store table and hash index

Submitted by Serguei_Tarassov on

Hash indexes are used as entry points for memory-optimized tables; both features were introduced in SQL Server 2014. Let's look at index performance in scenario "key-value store".

Scenario and environment

The table stores a significant volume (about 10 millions rows) of pairs key-value. The key is of integer type and the value is a small random string.

The table store is used :

  • random read of a value from a key
  • random update of a value by a key

We will tests the scenario for the following types of table :

Fast inserting a file into a table

Submitted by Serguei_Tarassov on

How to insert fast a file (i.e. binary one) from disk to a SQL Server table?

Certainly the SQL Server account should have a corresponding permissions to access the file on the disk. For example I take the file C:\WINDOWS\system32\oembios.bin having the size about 12 MB. For really big files you'd better switch to the FILESTREAM approach.

Indexing columns of bit type

Submitted by Serguei_Tarassov on

Clearly, an index build on only one column of the bit type is useless because the selectivity is poor (50% of a table in average). However, when a table has several columns of the bit type, the composite index may be efficient.

For the test example, we'll create a table and fill it with random data.

Pages

Subscribe to RSS - SQL Server