SQL Server: kill all database connections

| category: Programming | author: st
Tags:

How to kill all database connections before dropping it?

USE master;
GO
DECLARE @sql nvarchar(max);
WHILE 1=1 BEGIN
    SELECT TOP 1 @sql = N'KILL ' + convert(nvarchar(10), spid)
    FROM sysprocesses WHERE dbid = DB_ID('my_database');
    IF @@rowcount = 0  BREAK;
    EXEC sp_executesql @sql;
END;
DROP DATABASE IF EXISTS my_database;

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 …

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 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 …

Fast inserting a file into a table

| category: Programming | author: st
Tags:

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 …

SQL: delete duplicated rows

| category: Programming | author: st
Tags:

How to delete the rows having the duplicated values of one or more columns and considered as duplicates? The example in Transact SQL is below.

SET NOCOUNT ON;
CREATE TABLE #t (
  product_name nvarchar(20),
  vendor_name nvarchar(20)
)
GO
INSERT INTO #t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2 …