SQL

All expressions of 123456789 and signs +/- which value is 100

Submitted by st on

Suppose a string of ordered cyphers 123456789. You can insert signs "+" and "-" between any cyphers to make a correct arithmetical expression. The problem is to find all expressions which sum is 100.

This problem may be interesting for dynamic script languages having the function of a string expression evaluation.

E.g. solving in bash

SQL Server: key-value store table and hash index

Submitted by st 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 :

SQL: generate random character string

Submitted by st on

Collection of methods to generate random string in a single SQL statement

M1: Recursive string concatenation

On SQL Server the method is limited by 100 characters because of CTE recursion level limitation. The method has the performance issues when using in loops/joins because the table always have 100 rows generated every time.

Types of joins in SQL (cheatsheet for beginners)

Submitted by st on

This article is used in my book "Programming with databases".

Better to see once than to hear a hundred times. This small article explains what are the different kinds of joins in SQL.

At the first time you should take into account that SQL is a declarative 4th generation language. So the best way to use it (especially for beginners) is to write the query and let SQL optimizer do its job. That means thinking in set-oriented terms and avoiding sub-queries.

Let's take two example tables: Companies and Contacts.

Pages

Subscribe to RSS - SQL