SQL Server: how to simulate and catch deadlock

Submitted by st on Fri, 16/11/2018 - 12:42

SQL Server Profiler has the template "locks" that is intended especially to trace deadlock. Let's try to simulate and catch a deadlock situation.

Suppose the test database is named test. Create two tables and feed them some data.

Testing

SQL Server

SQL Server: temporary tables and constraint names

Submitted by st on Wed, 14/11/2018 - 10:19

Local temporary tables are isolated in the scope of current session regardless its name. It means that #temp_table in the session 1 is not the same that #temp_table in the session 2. However, the names of constraints are stored in the metadata of tempdb and can produce duplicate name errors.

Programming

SQL Server

C/C++: how to clear a linked circular list

Submitted by st on Mon, 12/11/2018 - 15:25

In the case of linked circular list you need to add some checks to avoid memory corruption.

Full example (compiled OK with GCC 8.x)

SQL Server: key-value store table and hash index

Submitted by st on Tue, 06/11/2018 - 14:55

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 :

Testing

SQL Server |
SQL

SQL Server: fractional part of number

Submitted by st on Tue, 30/10/2018 - 11:05

Unfortunately, SQL Server doesn't have a function like frac to extract the fractional part of number. The method is to subtract the integer part from the origin value.

Programming

SQL Server

SQL: generate random character string

Submitted by st on Fri, 26/10/2018 - 10:49

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.