Monthly Archives: March 2019

Report on Self harm

Recently we were given access to some data for self-harm statistics collected by WHO. Here are the important points

Highest risk category are millennial males nearing the age of 25 in the Americas. Below is a screen grab of the basic report. Further analysis can be found below:-

Data for regions like Africa and Middle East were too sparse for any meaningful analysis so will not be discussed further.

Some obvious points were identified first

  • Men are far more prone to self-harm than women.
  • The peak risk age group for almost all generations appears to 35-45 years of age.

Suicide Rates for Europe and Asia shown below are on the decline




Suicide rates for north and South America are trending upwards

North America

South America


There is a direct inverse link between per capita GDP and Self harms, as was witnessed for most countries for the time periods during periods of recession. Increase in population also contributes to increase in self-harm rates if not accompanied by greater growth in per capita GDP.

With every subsequent generation we see that peaks in self-harm rates are reached at younger and younger ages


There is a worrisome trend of economic pressure resulting in increased self-harm rates for all generations expect millennials. In the case of millennials we see that rates spike far ahead of the age group acquiring working age likely a result of online challenges such as Blue Whale Game etc. We also noted an anomaly with regard to much older generations like the Silent generation who are currently over 75 years of age having significantly higher rates for the age group 75+ compared to other generations. It is uncertain if the spike is due to not having sufficient data or an actual trend.

SQL 2017 – Minor features

There are a tons of minor improvements of features in SQL 2017 that nobody talks about so I figured before we all get swept up with SQL 2019. It might be good to quickly post something about them for posterity.

  1. sys.dm_os_host_info


A new DMV that gives OS level info as shown below, as you can imagine it’s very basic and doesn’t really provide any real information other than the OS name and version.

Only practical use is for audits and migrations which are fairly rare and could be managed via other ways. It doesn’t really give any information on if the Database Instance is running on a Docker container which reduces its usefulness in my opinion.


Essentially it a unpivot function for string values. From a pure database design perspective it’s the exact opposite of normalization. Because you taking data from columns and merging them into a single cell.


varchar(max)),‘ | ‘)
GROUP (Order
by CourseName)

from [survey].Course


The most common place I see this being used is in a column like skills which has a row for each skill the developer has. The combined skills list is then presented as a delimited value to some presentation layer. The ability to sort values within the list is a nice touch.

  1. Creating Clustered Columnstore index on LOB columns like varchar(max) and nvarchar(max)

In SQL 2016 and below you would encounter the below message when creating a CLUSTERED COLUMNSSTORE INDEX on a LOB datatype column.

Msg 35343, Level 16, State 1, Line 1

The statement failed. Column ‘CourseDetails’ has a data type that cannot participate in a columnstore index. Omit column ‘CourseDetails’.


However in SQL 2017 this is no longer the case. While there are still the other usual limitations such as no other indexes are supported etc.

The Create statement will work in SQL 2017. SSMS obviously depends on which version you’re using. Why I have added this as a minor feature is because until the limitations of a CSCI are addressed there isnt that many real world use cases where using the Clustered Columnstore index is practical let alone one that support LOB datatypes.


The sister function to String_agg where it concatenate data from multiple columns with a delimiter unlike string STRING_AGG which concats multiple rows with a delimiter.

  1. TRIM

Hmm I wonder what Trim does



A useful function even though it’s a minor one. At its core it’s a simpler version of replace that works with multiple escape characters.

  1. sys.dm_db_log_stats



While technically not a minor features it is still just a DMV so I figured I’ll explain it here. The DMV basically provides a health check of the T log and the most useful columns in the DMV include the number of VLFs, the log reuse wait desc (log_tuncation_holdup_reason) and the lsn and time since last backup. The best part of this DMV for me is that it helps better visualize the log growth and truncation / shrink mechanisms for my trainings.