SQL Server: temporary tables and constraint names

| category: Programming | author: st
Tags:

Update. Since the version 2016 SQL Server supports anonymous constraint declaration:

CREATE TABLE #temp_table (
   id1 int NOT NULL,
   id2 int NOT NULL,
   name1 nvarchar(100),
   name2 nvarchar(100),
   PRIMARY KEY (id1, id2),
   UNIQUE (name1, name2)
);

For earlier versions the solution is below.

Local temporary tables are isolated in the scope …

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

| category: Programming | author: st
Tags: ,

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)

#include <iostream>
#include <cstdlib>

struct Node
{
    Node(Node* prev, int val)
        : prev(prev), next(NULL), value(val)
    { }
    Node *prev, *next;
    int value;
};

void clear_list(Node …

SQL Server: key-value store table and hash index

| category: Testing | author: st
Tags: ,

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 …

SQL Server: fractional part of number

| category: Programming | author: st
Tags:

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.

DECLARE @n1 float, @n2 float, @n3 float;
SELECT @n1 = 123.456, @n2 = 234.567, @n3 = 456.789;
SELECT @n1 - round(@n1, 0 …

SQL: generate random character string

| category: Programming | author: st
Tags:

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 …

SQL Server: sync two tables with MERGE statement

| category: Programming | author: st
Tags: ,

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 …

Count LOC with Notepad++

| category: Programming | author: st

Notepad++ can count LOC (lines of code) of source files and give you the estimation of code size.

Press Ctrl+Shift+F shortcut to start "Find in files" dialog. Select "regular expression" search mode and start it.

Notepad++ LOC count

Some regular expressions:

  • \S+\s*$ -- all non-empty lines
  • ^\s*$ -- only empty (white-space) lines …

Classic backgroud color in Windows 10

| category: My notes | author: st
Tags:

How to set classic background color in Windows 10?

  • Right click on desktop and select "Personalize" menu
  • Set "Background" option to "Solid color"
  • Click on "+ custom color" button
  • Click on "More"
  • Check that "RGB" is set
  • Enter "#336EA5" and click "Done" button

That's all.