SQL Merge Insert/Update with output

if object_id('tempdb..#joe') is not null
    drop table #joe;

go

declare @results table (id int);

create table #joe (id int not null identity, name varchar(100));

insert #joe
    select 'John' [name]
    union select 'Jane'
    union select 'Mary'
    union select 'Pat';

select * from #joe order by id;

declare @id int, @name varchar(100);

-- Update
--set @id = 2; set @name = 'Phil';

-- Insert
--set @id = null; set @name = 'Phil';

merge #joe tgt
using (select @id [id]) src
on tgt.id = src.id
when matched then
    update set name = @name
when not matched then
    insert (name)
    values (@name)
output inserted.id into @results;

select * from #joe order by id;
select * from @results;

Is string an integer

SQL Server 2008 doesn’t yet have the TRY_PARSE function, so safely converting text to an integer is not as simple as you’d think.

There’s the ISNUMERIC function, but as people have pointed out, there are major issues with this function, and it doesn’t work for regular people like you and me. The real solution seems to be to add .e0 to the end of the string before checking, which handles these funky scenarios.

select cast(Val as int) [TheIntVal]
from dbo.MyTable
where isnumeric(Val + '.e0') = 1;

Insert with output

This can be an alternative to things like SCOPE_IDENTITY() or @@IDENTITY.

declare @result table (ID int);

-- dbo.Records has identity column ID
insert dbo.Records (PersonName)
output inserted.ID into @result
values (@PersonName);

select ID from @result; -- The newly created identity

GZip CLR Functions

Looks like SQL Server 2016 finally introduced a COMPRESS function. If you’re not quite there yet, then you can build a CLR function to compress/decompress data using GZip.

using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlBytes GZipString(SqlString value)
    {
        using (var inputStream = new MemoryStream(Encoding.UTF8.GetBytes(value.Value)))
        {
            using (var outputStream = new MemoryStream())
            {
                Compress(inputStream, outputStream);
                return new SqlBytes(outputStream.ToArray());
            }
        }
    }

    [SqlFunction]
    public static SqlBytes GZipBinary(SqlBytes value)
    {
        using (var inputStream = new MemoryStream(value.Value))
        {
            using (var outputStream = new MemoryStream())
            {
                Compress(inputStream, outputStream);
                return new SqlBytes(outputStream.ToArray());
            }
        }
    }

    [SqlFunction]
    public static SqlString GUnZipToString(SqlBytes value)
    {
        using (var inputStream = new MemoryStream(value.Value))
        {
            using (var outputStream = new MemoryStream())
            {
                Decompress(inputStream, outputStream);
                return new SqlString(Encoding.UTF8.GetString(outputStream.ToArray()));
            }
        }
    }

    [SqlFunction]
    public static SqlBytes GUnZipToBinary(SqlBytes value)
    {
        using (var inputStream = new MemoryStream(value.Value))
        {
            using (var outputStream = new MemoryStream())
            {
                Decompress(inputStream, outputStream);
                return new SqlBytes(outputStream.ToArray());
            }
        }
    }

    private static void Compress(Stream inputStream, Stream outputStream)
    {
        using (var gzip = new GZipStream(outputStream, CompressionMode.Compress))
        {
            byte[] buffer = new byte[8192];
            int count;
            while ((count = inputStream.Read(buffer, 0, buffer.Length)) > 0)
            {
                gzip.Write(buffer, 0, count);
            }
        }
    }

    private static void Decompress(Stream inputStream, Stream outputStream)
    {
        using (var gzip = new GZipStream(inputStream, CompressionMode.Decompress))
        {
            byte[] buffer = new byte[8192];
            int count;
            while ((count = gzip.Read(buffer, 0, buffer.Length)) > 0)
            {
                outputStream.Write(buffer, 0, count);
            }
        }
    }
}

Drop Database

If you’re working locally, building a new app from scratch, you might be in a position where you want to wipe the database and start clean – but SQL Server may block you if you’ve got any open connections or any other issues blocking you.

This should set you as the only user of the database, which will clear the way for dropping it without ever getting an error:

use master;
go
if exists (select 1 from sys.databases where name = 'SomeDatabase')
begin
    alter database SomeDatabase set single_user with rollback immediate;
    drop database SomeDatabase;
end;
go

Disable all constraints

Sometimes you just need to disable all constraints on a database. Maybe you’re doing some massive data inserts or updates, and the constraints slow it down. Or maybe you just like living dangerously.

Don’t try this at home…

-- Disable all
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Enable all
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Cursors

Here’s my preferred syntax for a simple one-way readonly cursor:

It uses the @variable syntax just because I find it weird in SQL Server to use a variable without the @ symbol. I don’t know if there are any meaningful differences in doing it this way, but this just feels more natural to me.

Many people will fetch upfront, then do a while @@fetch_status <> 0, and then fetch again at the end of the loop body. This example avoids that by fetching inside an infinite loop, and breaking when it stops fetching records.

declare @cur cursor;
set @cur = cursor local fast_forward for
    select * from
    (
        select 1 [a]
        union select 2
        union select 3
    ) x
    where x.a > 0;

declare @a int;
open @cur;
while 1 = 1
begin
    fetch next from @cur into @a;
    if @@fetch_status <> 0 break;

    print @a;
    -- do stuff
end;
close @cur;
deallocate @cur;

Cursor for update

When you’re working through a cursor, if you need to update each record one at a time, here’s a simple example:

if object_id('tempdb..#joe') is not null
    drop table #joe;

select a, b
into #joe
from
(
    select 1 a, null b
    union select 11 a, null b
    union select 111 a, null b
) x;

alter table #joe add primary key (a);

select * from #joe;

declare @cur cursor;

set @cur = cursor local forward_only for
    select a from #joe 
    for update of b;
open @cur;

declare @a int;
while 1=1
begin
    fetch next from @cur into @a;
    if @@fetch_status <> 0 break;

    update #joe set b = a * 2 where current of @cur;
end;

close @cur;
deallocate @cur;

select * from #joe;

Case-sensitive column

To make a column case-sensitive instead of the default case-insensitive, just add a COLLATE to the column definition, and it’ll use that instead of the database default.

Default (case-insensitive)

if object_id('tempdb..#tmp') is not null
  drop table #tmp;

create table #tmp (i int not null identity, val varchar(10));

insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');

select * from #tmp;

Case-sensitive

if object_id('tempdb..#tmp') is not null
 drop table #tmp;

create table #tmp (i int not null identity, val varchar(10) collate Latin1_General_CS_AS);

insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'a' where not exists (select 1 from #tmp where val = 'a');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');
insert #tmp (val) select 'A' where not exists (select 1 from #tmp where val = 'A');

select * from #tmp;

List collations:

select [name], [description] from fn_helpcollations() order by [name];

Basic XML Examples

Here are some samples of various techniques for selecting from and into XML.

Select values from an XML column

declare @tbl table (id int, xval xml);
insert @tbl values
    (1, '<root><items><foo id="34">some text</foo></items></root>')
    ,(2, '<root><items><foo id="56">more text</foo></items></root>');

select
    t.id
    ,x.value('@id', 'int') [FooID]
    ,x.value('.', 'varchar(max)') [FooText]
from @tbl t
outer apply t.xval.nodes('/root/items/foo') x(x);
go
id  FooID   FooText
1    34    some text
2    56    more text

Select from XML using a variable

-- Pick the third item from each record
declare @idx int = 3;
declare @tbl table (id int, xval xml);
insert @tbl values
    (1, '<root><item>34</item><item>42</item><item>22</item></root>')
    ,(2, '<root><item>87</item><item>93</item><item>14</item></root>');

select
tbl.id
,x.value('.', 'int') [ValueByVariable]
from @tbl tbl
outer apply tbl.xval.nodes('root/item[sql:variable("@idx")]/text()') x(x)
go
id  ValueByVariable
1         22
2         14

Use value from query results as part of query

-- Pull the "nth" item value, where "n" is the id column
declare @tbl table (id int, xval xml);
insert @tbl values
    (1, '<root><item>34</item><item>42</item></root>')
    ,(2, '<root><item>87</item><item>93</item></root>');

select
tbl.id
,x.value('.', 'int') [ValueByColumn]
from @tbl tbl
outer apply tbl.xval.nodes('root/item[sql:column("tbl.id")]/text()') x(x)
go
id  ValueByColumn
1       34
2       93

Select from a table into XML

if object_id('dbo.tbl') is not null
drop table dbo.tbl;
go
create table dbo.tbl (id int, name varchar(100));
insert dbo.tbl values (1, 'John'), (2, 'Jane');

-- Element is the table name, and columns become attributes
select * from dbo.tbl for xml auto;
/*
<dbo.tbl id="1" name="John" />
<dbo.tbl id="2" name="Jane" />
*/

-- Each element gets named "row"
select * from dbo.tbl for xml raw;
/*
<row id="1" name="John" />
<row id="2" name="Jane" />
*/

-- Define the root name and row name, then define the XML structure by the query column names
select id [@the-id], [name] [the-name] from dbo.tbl for xml path ('the-item'), root ('the-root');
/*
<the-root>
    <the-item the-id="1">
    <the-name>John</the-name>
    </the-item>
    <the-item the-id="2">
    <the-name>Jane</the-name>
    </the-item>
</the-root>
*/

go

if object_id('dbo.tbl') is not null
    drop table dbo.tbl;
go