John Liu Blog

Every drop counts

SQL database roles

One way SQL server to easily manage access is using database roles. SQL server has several built-in database roles. db_owner role member by default can perform any action within the database. However, except the actual database owner (user name dbo), other members in the db_owner role can be deny access by placing them in other database roles with deny access to objects, such as db_denydatareader. Other roles functions: db_backupoperator: perform database back db_datareader: can read data db_datawriter: can INSERT, UPDATE and DELETE data db_ddladmin: can create/modify any objects but can read/write data db_denydatareader: can’t read data db_denydatawriter: can’t write data db_securityadmin: can grant access to user db_accessadmin: can create user but can’t grant access to objects and data

Posted by John Liu Sunday, November 14, 2021

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

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