John Liu Blog

Every drop counts

Docker Cheat Sheet

Docker command cheat sheet from twitter. Process Management Show all running docker containers docker ps Show all docker containers docker ps -a Run a container docker run <image>:<tag> Run a container and connect to it docker run -it <image>:<tabe> Run a container in the background docker run -d <image>:<tag> Stop a container docker stop <container> Kill a container docker kill <container> Volumes & Ports List volumes docker volume ls Create a volume

Posted by John Liu Sunday, July 16, 2023

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