John Liu Blog

Every drop counts

SQL Server Change Data Capture (CDC)

CDC (Change Data Capture) was introduced in SQL2008 Enterprise only, only available to Standard edtions since SQL2016 SP1. It’s not available in Express edtion as Express edtion doesn’t have SQL Agent. CDC involves three-tier configuration: Server-level (agent job), Database-level and Table-level. It works in all recovery modes. When using CDC, we need to minotr log file growth and adjust retention period, especially if not using FULL recovery mode. CDC is like a video recording of every single change, where as Change Tracking (CT) is more like a “motion sensor”.

Posted by John Liu Thursday, December 25, 2025

SQL Server Change Tracking (CT)

Change Tracking (CT) introduced in SQL2008, all edtitions, includes Express. CT is more like a “motion sensor”. CT tells you which rows changed and what the latest values are, but id doesn’t keep a history of every intermediate step. CDC is like a video recording of every single change. CT is much more lightweight than CDC. CT can only be enabled on table with PK. It’s a sync operation (overhead on every transaction).

Posted by John Liu Thursday, December 25, 2025

SQL Server MCP

References: SQL AI Samples (Microsoft Azure Samples) MSSQL MCP (Aaronontheweb)

Posted by John Liu Sunday, November 2, 2025

Create embeddings in SQL2025

SQL2025 has the ability to use local ONNX model on the server to create embeddings. Following are steps for configuration. 1. If this feature is still developer preview feature, run following SQL scrit to enable preview features ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON; 2. Enable AI runtime on SQL2025 EXEC sp_execute 'external AI runtime enabled',1; RECONFIGURE WITH OVERRIDE; 3. Set up the ONNX runtime library Create a local folder, say C:\onnx_runtime, to store the runtime libarary files.

Posted by John Liu Sunday, October 26, 2025

SQL Server Scientific Notation

When we import numeric data into SQL server, sometime the source data might be in scientific notation format. SQL Server can’t automatically convert scientific notation value string to decimal or integer value and will result with implecite conversion error. To properly import the data, we need to convert the source string value to FLOAT or REAL data type first before convert to the target data type. SELECT CONVERT(DECIMAL(18,3),CONVERT(FLOAT,'1.2E-5'))

Posted by John Liu Sunday, September 7, 2025