T-SQL – Knowledgebase

Split string and get row number / index by using string_split in >= SQL Server 2016

7 January, 2019 Roel van Lisdonk 0 Comments 1 category

declare @name nvarchar(max) = ‘test.d.f.g.hf.ddddd’ declare @items table( id int identity(1,1) not null , part nvarchar(max) ); insert into @items (part) select [value] from string_split(@name, ‘.’) where rtrim([value]) <> ”; select * from @items This

Solving: Cannot insert explicit value for identity column in table ‘NewInventory’ when IDENTITY_INSERT is set to OFF.

29 September, 2015 Roel van Lisdonk 0 Comments 1 category

SQL Server SET IDENTITY_INSERT At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a

Using a recursive common table expression, to calculate totals and subtotals of children and grandchildren in a hierarchy

16 June, 2015 Roel van Lisdonk 0 Comments 1 category

In SQL Server a common table expression can be used to calculate totals / subtotals of a table containing hierarchical data: -- Create test data: if object_id('tempdb..#Hierarchy') is not null begin drop table #Hierarchy

How to add multiple constraints to one column, when creating a table with T-SQL

4 May, 2015 Roel van Lisdonk 0 Comments 1 category

If you want to add a “named” default and a “named” unique constraint to one column, when creating a table with T-SQL, you can use the “inline syntax”: Here we create a table

Change varchar column length, when column does not match given length in T-SQL.

31 March, 2014 Roel van Lisdonk 0 Comments 1 category

The following code will update a column to varchar(max) only, when it does not already have the varchar(max) length. -- Update column 'Name' length, it should be varchar(max). -- Note: varchar(max) has length '-1'. if

How to get the amount of elapsed minutes in current quarter in T-SQL

27 March, 2014 Roel van Lisdonk 0 Comments 1 category

If you want to know the amount of elapsed minutes in the current quarter, you can use the following T-SQL code: declare @CurrentDateTime datetime = '2014-03-27 10:56:10' declare @MinutesPassedInHour int = convert(int, datepart(minute, @CurrentDateTime)) declare

Tag: T-SQL

Split string and get row number / index by using string_split in >= SQL Server 2016

Solving: Cannot insert explicit value for identity column in table ‘NewInventory’ when IDENTITY_INSERT is set to OFF.

Using a recursive common table expression, to calculate totals and subtotals of children and grandchildren in a hierarchy

How to add multiple constraints to one column, when creating a table with T-SQL

Change varchar column length, when column does not match given length in T-SQL.

How to get the amount of elapsed minutes in current quarter in T-SQL

Convert date to "Dutch" string format "dd-mm-yyyy" in T-SQL

How to get the day of week of a all dates between start and end date in T-SQL

How to validate a value can be cast to an integer in T-SQL

Just a reminder: null = null, is not true in T-SQL