John Liu Blog

Every drop counts

SQL Table Expression

Itzik Ben-Gan has a great series of articals about table expressions, focus on four types of named table expressions: derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). Fundamentals of table expressions, Part 1 Fundamentals of table expressions, Part 2 – Derived tables, logical considerations Fundamentals of table expressions, Part 3 – Derived tables, optimization considerations Fundamentals of table expressions, Part 4 – Derived tables, optimization considerations, continued

Posted by John Liu Thursday, November 11, 2021

Software working with Azure SQL Database

When develop software working with Azure SQL database, some special considerations needs to be taken into account. Connection string With standard SQL Server, if the connection didn’t specify a database name in your connection string, the connection default to master database. Unless specifically denyed access, a valid login with connection permission can connect to master database and be able to see the full list of database names on the connected instance, even if they don’t have permission to access those databases.

Posted by John Liu Tuesday, November 9, 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

Availability % and downtime mapping

When people talking about system availability, it reflects to an agreed downtime. The mapping is as following: Availability % Downtime per year 99% 3.65 days 99.5% 1.83 days 99.9% 8.77 hours 99.95% 4.38 hours 99.99% 52.60 minutes 99.995% 26.30 minutes 99.999% 5.26 minutes (Table source: https://docs.microsoft.com/en-us/learn/modules/deploy-sql-server-virtual-machine/2-explain-iaas-options-deploying-azure) For solution involves mulitple system with different availabilities, the maximum availability of the overall solution is the smallest availability. However, you can utilise Availability Zones to increase overall availability.

Posted by John Liu Sunday, November 7, 2021

SQL Server on Azure config best practice

Each Azure VM has a OS disk (C drive) and a temporary disk (D drive). Only store on the temporary disk temp data that doesn’t need to be retained as all data on this disk will be lost after server reboot. The best practice for SQL Server on Azure VM is to use Premium Disks pooled for increased IOPs and storage capacity. Data file should be on its own pool with read-caching on the Azure disk.

Posted by John Liu Sunday, November 7, 2021