The frequently asked problem on interviews.

You have a log of some user activities represented as a table. Every activity record has at least users ID and activity date/time values.

The session is a sequence of activities having less than N minutes between two log records. When the elapsed …

Very old subject I remember to discuss in the middle of 1990s... But some people still says that a clean architecture should remove all business logic from the database.

Let's start from referential and domain integrity rules (constraints). Are they business ones? Yes, of course. The e-mail column should be …

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 …

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 …

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 …

This small example shows how to synchronize the data of two tables using the MERGE statement

CREATE TABLE table1 (id int NOT NULL PRIMARY KEY, str_value nvarchar(50));
CREATE TABLE table2 (id int NOT NULL PRIMARY KEY, str_value nvarchar(50));
GO
INSERT INTO table1 (id, str_value) VALUES (1, 'Value 1 …

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 …

Here are some examples of SQL to generate a sequence of numeric (integer) values from 0 to 99.

ANSI-compatible

SELECT ones.n1 + tens.n2 * 10
FROM
(
  SELECT 0 AS n1
  UNION SELECT 1 AS n1
  UNION SELECT 2 AS n1
  UNION SELECT 3 AS n1
  UNION SELECT 4 AS n1 …

There are many examples of user defined table functions generating the series of dates(times) between two specified values. Usually, in OLAP you can see a table filled by the date/time series, too. Such table avoid to generate the values "on the fly".

However, in some cases you may …