↓
 

The Intransigent Data Blog

Getting data to toe the line

The Intransigent Data Blog
The Intransigent Data Blog
  • Home
  • About this Blog
  • Links
  • Toolset
Home→Categories T-SQL 1 2 3 >>

Category Archives: T-SQL

Post navigation

← Older posts

Alternatives to the (slow) FORMAT function

The Intransigent Data Blog Posted on 16/01/2022 by Andrew Ing26/02/2022
stopwatch in a hand

At first sight the FORMAT function introduced in SQL Server 2016 looks like a useful addition. It allows freedom in formatting dates and numbers using C#-style format strings  along with locale awareness.  Although formatting is strictly the preserve of the … Continue reading →

Posted in T-SQL

Disable non-clustered indexes during data load

The Intransigent Data Blog Posted on 24/06/2021 by Andrew Ing14/07/2021

Indexes slow down INSERTS into SQL Server tables, especially in Azure SQL where bulk logged inserts are not available. This procedure can disable and then rebuild all non-clustered indexes on one specified table.  Disabling non-clustered indexes is a zero cost … Continue reading →

Posted in Data Warehouse, T-SQL

Referential integrity checks

The Intransigent Data Blog Posted on 18/08/2020 by Andrew Ing18/08/2020

This script is aimed at data warehouse relational databases which don’t have foreign key constraints.  Using column names it matches key column values between fact and dimension tables to identify orphans.

Continue reading →
Posted in Data Warehouse, T-SQL

Maintenance – Record Deletion

The Intransigent Data Blog Posted on 11/02/2019 by Andrew Ing14/07/2019

A simple framework for implementing a time-framed record deletion policy: deleting records that are older than a certain threshold. Consists of a logging table and a stored procedure which deletes the records.   The procedure deletes rows in batches and provides … Continue reading →

Posted in T-SQL

Populating Date Dimension with Fiscal Periods

The Intransigent Data Blog Posted on 23/04/2017 by adminUsr23/04/2017

A standard method for populating a date table with fiscal periods will always come to grief over exceptions.  This post includes a procedure that should be customisable enough to cater for most variations on fiscal periods – provided all periods … Continue reading →

Posted in Data Warehouse, T-SQL

A Procedure for generating Data Warehouse Update code

The Intransigent Data Blog Posted on 20/01/2017 by adminUsr23/04/2017

Background Transactional tables exist in Database A.   These tables have partner archive tables in the same or another database.  For each insert or change of a value in the transactional database a timestamped new row is entered into the … Continue reading →

Posted in Data Warehouse, T-SQL

Duration Dimension

The Intransigent Data Blog Posted on 21/02/2016 by adminUsr22/02/2016

For measuring short durations, such as telephone calls or handling times. Q1 Linear Grain? The question I haven’t answered is whether the dimension should be at the finest grain for all durations.  The grain I am using is seconds but … Continue reading →

Posted in Data Warehouse, T-SQL

Data Obfuscation

The Intransigent Data Blog Posted on 30/07/2015 by Andrew Ing30/07/2015

Some functions for obfuscating data to protect personally identifiable data.  Aims: fast enough to process a large table resulting data is “sufficiently” obfuscated resulting data retains enough characteristics that it makes some sense in context. transformations are repeatable so that … Continue reading →

Posted in T-SQL

Literal Values in Scripts (VALUES() )

The Intransigent Data Blog Posted on 22/04/2015 by Andrew Ing04/09/2017

Options for including literal values in scripts – syntax that I find particularly hard to remember. The feature is named Table Value Constructor. It was introduced in 2008 – see this article. Source of Merge statement MERGE INTO dbo.TargetTable AS Target … Continue reading →

Posted in T-SQL

Generate Random Test Data

The Intransigent Data Blog Posted on 03/02/2015 by Andrew Ing25/01/2019

Steps to follow to create a randomised dataset for testing. An online data generator:  www.generatedata.com. Prepare the Database We will be loading a lot of data into the tables. So Set Instant File Initialisation Set the data file size sufficient to contain … Continue reading →

Posted in T-SQL

Post navigation

← Older posts

Recent Posts

  • Alternatives to the (slow) FORMAT function
  • Disable non-clustered indexes during data load
  • Script the creation of ODBC DSNs
  • DAX: partitioned conditional aggregations
  • Referential integrity checks
  • Understanding errors from Tabular Analysis Services
  • Error flows in Azure Data Factory
  • The effect of RAID cache backup battery failure

Pages

  • About this Blog
  • Links
  • Toolset

Categories

  • ASP.Net
  • Automation
  • Azure Data Factory
  • Data Warehouse
  • DAX
  • DBA
  • IIS
  • MDS
  • MDX
  • Permissions
  • SSAS
  • SSIS
  • SSRS
  • T-SQL
  • Uncategorized
  • VBA

Archives

  • January 2022 (1)
  • June 2021 (1)
  • May 2021 (1)
  • August 2020 (2)
  • June 2020 (1)
  • December 2019 (1)
  • March 2019 (1)
  • February 2019 (1)
  • December 2018 (1)
  • November 2018 (1)
  • August 2018 (1)
  • April 2018 (1)
  • October 2017 (1)
  • April 2017 (1)
  • January 2017 (2)
  • November 2016 (1)
  • September 2016 (1)
  • May 2016 (2)
  • April 2016 (1)
  • March 2016 (1)
  • February 2016 (2)
  • December 2015 (3)
  • October 2015 (1)
  • July 2015 (1)
  • May 2015 (1)
  • April 2015 (2)
  • February 2015 (1)
  • January 2015 (1)
  • December 2014 (1)
  • October 2014 (2)
  • September 2014 (2)
  • August 2014 (2)
  • July 2014 (3)
  • June 2014 (2)
  • May 2014 (2)
  • April 2014 (1)
  • March 2014 (1)
  • January 2014 (1)
  • December 2013 (5)

Tags

Blog Convention SQL-Pattern SSAS SSIS SSRS T-SQL Validation

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©2022 - The Intransigent Data Blog - Weaver Xtreme Theme
↑