if not exists (select 1 from information_schema.schemata where schema_name = ‘util’)begin exec sp_executesql N’create schema [util];’;end;go create table util.Numbers ( Num int not null ,constraint […]
Flatten in SQL Server
CREATE TYPE [dbo].[SingleColumnText] AS TABLE ( [TextValue] NVARCHAR (MAX) NULL); create function dbo.Flatten( @input dbo.SingleColumnText readonly ,@delimiter nvarchar(max))returns nvarchar(max)asbegin return ( select stuff(( select @delimiter […]
Convert UTC to local time in SQL Server
Thanks to this StackOverflow answer: convert( datetime, switchoffset( convert(datetimeoffset, MyTable.UtcColumn), datename(TzOffset, sysdatetimeoffset()) )) as ColumnInLocalTime
Catching SQL exceptions in .NET
Here’s one way of handling SQL errors from .NET: For expected errors, like data validation, raise an error with a specific error code and state. […]
SQL Dates Table
create table dbo.Dates ( TheDate date not null ,constraint PK_Dates primary key (TheDate));go declare @StartDate date = ‘1950-01-01’;declare @EndDate date = ‘2099-12-31’;;with cte as ( […]
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 […]
Is string an integer in SQL Server
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 […]
Insert with output in SQL Server
This can be an alternative to things like SCOPE_IDENTITY() or @@IDENTITY. declare @result table (ID int); — dbo.Records has identity column IDinsert dbo.Records (PersonName)output inserted.ID […]
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 […]
Drop Database in SQL Server
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 […]