SQL Server

Cursor SQL Server

DECLARE @iterator as CURSOR;
 
SET @iterator = CURSOR FOR
SELECT TOP (10000) [UserId]
      ,[ParentTagId]
  FROM [UserSubscriptions]
  where ParentTagId is not null
  group by UserId, ParentTagId having count(*)>=2;
 
OPEN @iterator;
FETCH NEXT FROM @iterator INTO @UserId, @ParentTagId;
 
WHILE @@FETCH_STATUS = 0
BEGIN
 -- Your mumbo jumbo goes here
 
 FETCH NEXT FROM @iterator INTO @UserId, @ParentTagId;
END
 
CLOSE @iterator;
DEALLOCATE @iterator;

Last day of the month

In T-SQL 2012+:

EOMONTH(datetime)

in general case:

dateadd(day,-day(dateadd(month,1,orderdate)),dateadd(month,1,orderdate))

Generate numbers table in-place (SQL Enumerable.Range from .NET)

DECLARE @startnum INT=1
DECLARE @endnum INT=50
;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)

Generate sequence of dates

1. Create table with integer numbers:

create table integers (n int);
declare @i int = 1;
while @i <= 100 begin
insert into integers values(@i)
set @i = @i + 1;
end;

1. Use this query to create in-line table:

with dates as (
  select dateadd(day,n-1,cast('2006-01-01' as datetime)) as d from Nums
  where dateadd(day,n-1,cast('2006-01-01' as datetime)) <= cast('2008-12-31' as datetime)
)

Working with IP addresses

Convert integer IP to decimal in T-SQL

CREATE FUNCTION [dbo].[ToIpv4]
(
    @ip bigint
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN CONCAT(CAST((@ip/ POWER(256,3)) & 255 as VARCHAR(MAX)) , '.' ,
  CAST((@ip / POWER(256,2) ) & 255 as VARCHAR(MAX)) , '.' , 
  CAST((@ip / POWER(256,1) ) & 255 as VARCHAR(MAX)) , '.' , 
  CAST((@ip / POWER(256,0) ) & 255 as VARCHAR(MAX)))

END
GO

Convert decimal (dot notation) IP to integer

CREATE   FUNCTION [dbo].[parse_ip](@input BIGINT)

RETURNS VARCHAR(30)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result VARCHAR(30) = ''

    DECLARE @i int = 3;
    DECLARE @tmp bigint;
    DECLARE @ip bigint = @input;
    while @i >= 0
    BEGIN
        set @tmp = FLOOR(@ip / POWER(256, @i));
        set @ip = @ip - @tmp * POWER(256, @i);
        set @result = @result + CAST(@tmp as varchar(max));
        if @i != 0
            begin
                set @result = @result + '.'
            end;
        set @i = @i - 1;
    END;
    return @result
END

GO

Query hints SQLServer

select p.nazwisko as pracownik, (select nazwa from zespoly z where z.ID_ZESP = p.ID_ZESP) as zespol
from pracownicy p
option(merge join)

Split string into rows (T-SQL cross apply)

(select Country, University, LTRIM(value) as lang
from so_survey_2017
cross apply string_split(HaveWorkedLanguage,';')) t

Buld import from CSV into SQLServer

bulk insert [dbo].awesome_table
from 'C:\path_to_csv_file.csv'
with
(
  firstrow = 2,
  FIELDTERMINATOR=',',
  ROWTERMINATOR='0x0A'
)

Select first row for each group

with countries as (
  select ip_start, ip_end, country_code, ROW_NUMBER() OVER (PARTITION BY country_code ORDER BY country_code asc) as row_num from geoip
  where country_code in ('YE', 'CO')
)
select * from countries where row_num = 1

Check size of databases in SQLServer

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
-- WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id
order by total_size_mb desc

MySQL

LAG function for MySQL

SET @lastName='';
select f.* from(
  SELECT @lastName as previous, @lastName:=name as name
  FROM files
  order by name) f
where f.name != f.previous

PostgreSQL

Empty blob (bytea)

(SELECT '0'::bytea)