John Liu Blog

Every drop counts

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

SQL Server Performance tool scripts

Buffer cache usage summary SELECT D.name ,MBUsed = COUNT(*) * 8 / 1024 ,MBEmpty = SUM(CAST([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) FROM sys.dm_os_buffer_descriptors B INNER JOIN sys.databases D ON B.database_id = D.database_id GROUP BY D.name Current memory status --https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/dbcc-memorystatus-monitor-memory-usage DBCC MEMORYSTATUS; Index usage SELECT DatabaseName = DB_NAME() ,TableName = o.name ,IndexName = i.name ,s.index_id ,i.type ,i.is_primary_key ,i.is_unique_constraint ,s.last_user_seek ,s.user_seeks ,SeekPercentage = CASE s.user_seeks WHEN 0 THEN 0 ELSE s.user_seeks*1.0 /(s.

Posted by John Liu Sunday, April 9, 2023

SQL Agent Job Name

At time, you might need to report the actual SQL Agent job name that took the action in your notification. For example, you have a SQL Agent job that send user an email notification and you want to include in the notification the actual job name that sends the notification. You might hardcode the job name in the notification. However, when the job renamed, you need to also remember to update the hardcoded job name in the code as well.

Posted by John Liu Thursday, March 23, 2023

SQL Server File Size

There are serveral ways to find out database size. Apart from using the SSMS GUI, you can using following scripts options. --this return size info for all databases --the size is in kilobytes exec sp_databases --this return size info for all databases SELECT DBName = DB_NAME(database_id) ,FileName = name ,physical_name ,type_desc ,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.0/1024/1024) --,max_size --,growth --,is_percent_growth FROM sys.master_files WHERE database_id > 6 AND type IN (0,1) --this return size info for current database with free space info SELECT DBName = DB_NAME() ,FileName = name ,physical_name ,type_desc ,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.

Posted by John Liu Sunday, March 19, 2023

SQL Server Python integration

SQL Server Machine Learning Services config files are located under C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXP_2019\MSSQL\Binn pythonlauncher.config rlauncher.config When receive following error If the service worked before but not now, check the LaunchPad service has been restarted following restart of SQL Instance. If restart SQL Instance followed by restart LaunchPad service didn’t help, check LaunchPad service account has FULL CONTROL permission on the working folder configured in the above config file From SQL2019, the working folder in the config file is a shortcut link to C:\Program Files\Microsoft SQL Server\MSSQL15.

Posted by John Liu Friday, December 23, 2022