John Liu Blog

Every drop counts

SQL CLR

Start from SQL2017, CLR strict security is enabled by default in SQL, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. One can disable CLR stric security option but that’s not recommended. Microsoft recommends assembily be signed by a certificate or asymmetric key with corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database. DBA can aslo add assembliy to a trusted list by using sys.

Posted by John Liu Friday, July 7, 2023

SQL Count DISTINCT with partition

There are times where you want to count distinct value over a partition window. However, COUNT(DISTINCT column) OVER(PARTITION BY …) is not supported by SQL. One alternative way to achieve the same results without using GROUP BY is to use the DENSE_RANK() function. --if column does not allow null SELECT * ,CountDistinct = DENSE_RANK() OVER(PARTITION BY columnList ORDER BY column ASC) + DENSE_RANK() OVER(PARTITION BY columnList ORDER BY column DESC) - 1 FROM table Please note, if your data might have null values, the above method might not work correctly as COUNT() ignore null value whereas DENSE_RANK() doesn’t.

Posted by John Liu Friday, July 7, 2023

PowerShell export data to file

This example PowerShell script exports data from SQL table into files and then zip them together. Clear-Host function ExportDataToFile { param( [string]$instancename, [string]$dbname, [string]$flowname, [string]$clientname, [string]$filepath ) $guid = @() $guid_replace = @() if ([string]::IsNullOrEmpty($filepath)) { throw "filepath is rquired but not specified!" } if ($filepath -match "\\$") { $filepath = $filepath.Substring(0,$filepath.Length-1) } #connection to SQL $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=" + $instancename + ";Database=" + $dbname + ";Integrated Security=True" $SqlCmd = New-Object System.

Posted by John Liu Saturday, July 1, 2023

SQL Server Relocate Tempdb file location

There might be time you would like to relocate tempdb file to a new location. Make sure the SQL Server service account has required permission to the destination folder. Run the following command to move each database file to the new location: ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'E:\Temp\tempdb.mdf') Restart SQL Server service and tempdb should be relocated to the new location. If you encounter any issue that caused the SQL Server service fail to start, you can try to start the service in emergency mode and then connect to it make any required adjustment.

Posted by John Liu Sunday, June 18, 2023

Docker notes

Get help docker --help List all existing containers (without truncating result string) docker ps -a --no-trunc Start a container (containner name is case sensitive) docker start <container name> Show current downloaded images docker images To download an image from a registry (use :latest or :1.2.3 tag for specific version) docker pull <docker_image>:latest To create and run a containner (use create instead of run to just create a containner) docker run -it --name DataApiBuilder -v "c:\DataAPIBuilder\Samples:/App/Samples" -p 5004:5000 mcr.

Posted by John Liu Friday, June 16, 2023