John Liu Blog

Every drop counts

SQL string split

There are different ways to split string in SQL query. One way I came across is using openjson function. DECLARE @String AS VARCHAR(100) = '1,2,3,4' SELECT value FROM OPENJSON('[' + @String + ']') This method assumed the string using comma as the delimitor. If your string using other character as the delimitor, consider using REPLACE() function to change it to comma first. Another simple method would be using STRING_SPLIT() function in SQL2016+

Posted by John Liu Monday, November 15, 2021

SQL Server Geometry data conversion

In SQL Server, when need to convert geometry data for export to text file or operat with DISTINCT, be mindful the method you choose to do conversion to minimise inaccurate data introduced by conversion. When run following query using four different conversion methods: convert the geometry data to string using CONVERT then back to geometry and compare with orginal geometry convert the geometry data to string using ToString then back to geometry and compare with orginal geometry convert the geometry data to binary and then back to geometry again and compare with the original geometry convert the geometry data to binary and then to varchar and then back to binary and geometry and compare with the original geometry SELECT Location ,GeometryData --method 1 ,GeometryData.

Posted by John Liu Monday, November 8, 2021

Small table big performance impact

I recently came across an intersting case with a new stored procedure performance. The SP appeared to be run fast for one parameter value but very slow for another. My initial thought was parameter sniffing. However, a quick WITH RECOMPILE didn’t help. After turn on Live Query Statistics and look at the live data movements, I noticed that the process is doing a index scan on a small table with only 8 rows and then doing nested loop against a large table with out of wack estimates.

Posted by John Liu Tuesday, November 2, 2021

Effectively mapping primary key – foreign key relations

To properly locating the primary key - foreign key relations in a database sounds like a tough task. Mohit Nayyar posted a technique to find out primary key and foreign key that have the same column names. The limitation of that is that, in reality, column names are not always the same in a database. For example, in the AdvantureWorks sample database in SQL2005, the SalesPersonID in Sales.SalesPerson table has a foreign key reference to the EmployeeID column in the HumanResources.

Posted by John Liu Wednesday, November 7, 2007