MSSQL #

Microsoft SQL Server (MSSQL) is Microsoft’s enterprise relational database management system, widely used in corporate environments — especially in the Windows/.NET ecosystem, but also in JVM/Kotlin applications. MSSQL has several important differences from MySQL: different T-SQL syntax, integrated Windows Authentication, enterprise features like Always On, and strong stored procedure support. In Kotlin, you interact with MSSQL through Microsoft’s JDBC driver, HikariCP for connection pooling, and ORMs like Exposed. This article covers all aspects from basic connections, CRUD, distinctive T-SQL features, to stored procedure handling and the differences to watch out for when migrating from MySQL.

Setup and Dependencies #

// build.gradle.kts
dependencies {
    // Microsoft JDBC Driver for SQL Server
    implementation("com.microsoft.sqlserver:mssql-jdbc:12.6.1.jre11")

    // HikariCP connection pool
    implementation("com.zaxxer:HikariCP:5.1.0")

    // Exposed ORM (optional)
    implementation("org.jetbrains.exposed:exposed-core:0.49.0")
    implementation("org.jetbrains.exposed:exposed-dao:0.49.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.49.0")

    // Flyway for migrations (SQL Server edition)
    implementation("org.flywaydb:flyway-core:10.10.0")
    implementation("org.flywaydb:flyway-sqlserver:10.10.0")
}

Connection String Format #

MSSQL has a connection string format different from MySQL:

// Basic format with SQL Server Authentication
val basicUrl = "jdbc:sqlserver://localhost:1433;databaseName=myapp;encrypt=false"

// Full format with all options
val fullUrl = buildString {
    append("jdbc:sqlserver://")
    append("localhost:1433")                    // host:port (default 1433)
    append(";databaseName=myapp")              // database name
    append(";encrypt=true")                    // connection encryption
    append(";trustServerCertificate=true")     // trust self-signed certificates (dev)
    append(";loginTimeout=15")                 // login timeout in seconds
    append(";sendStringParametersAsUnicode=true") // UTF-8 support
    append(";applicationName=MyKotlinApp")     // app name for monitoring
}

// Windows Authentication (without username/password)
val windowsAuthUrl = "jdbc:sqlserver://localhost:1433;databaseName=myapp;integratedSecurity=true;encrypt=false"

// Azure SQL Database
val azureUrl = buildString {
    append("jdbc:sqlserver://myserver.database.windows.net:1433")
    append(";databaseName=myapp")
    append(";encrypt=true")
    append(";trustServerCertificate=false")
    append(";hostNameInCertificate=*.database.windows.net")
    append(";loginTimeout=30")
    append(";authentication=ActiveDirectoryPassword")  // or SqlPassword
}

Connecting with HikariCP #

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

object DatabaseMssql {
    private val dataSource: HikariDataSource by lazy {
        val config = HikariConfig().apply {
            jdbcUrl = buildString {
                append("jdbc:sqlserver://")
                append(System.getenv("DB_HOST") ?: "localhost:1433")
                append(";databaseName=${System.getenv("DB_NAME") ?: "myapp"}")
                append(";encrypt=${System.getenv("DB_ENCRYPT") ?: "false"}")
                append(";trustServerCertificate=true")
                append(";sendStringParametersAsUnicode=true")
            }
            driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            username = System.getenv("DB_USER") ?: "sa"
            password = System.getenv("DB_PASSWORD") ?: "Password123!"

            // Pool settings
            minimumIdle = 2
            maximumPoolSize = 10
            idleTimeout = 300_000         // 5 minutes
            connectionTimeout = 30_000    // 30 seconds
            maxLifetime = 1_800_000       // 30 minutes
            poolName = "MSSQL-Pool"

            // Test query for connection validation
            connectionTestQuery = "SELECT 1"
        }
        HikariDataSource(config)
    }

    fun <T> use(block: (java.sql.Connection) -> T): T {
        return dataSource.connection.use(block)
    }

    fun close() {
        if (!dataSource.isClosed) dataSource.close()
    }
}

fun main() {
    DatabaseMssql.use { connection ->
        connection.createStatement().use { stmt ->
            stmt.executeQuery("SELECT @@VERSION").use { rs ->
                if (rs.next()) println("SQL Server: ${rs.getString(1).lines().first()}")
            }
        }
    }
}

T-SQL vs MySQL SQL Differences #

MSSQL uses the T-SQL dialect, which differs from MySQL. These are the differences to watch out for when writing queries:

-- AUTO INCREMENT
-- MySQL:
CREATE TABLE pengguna (id INT AUTO_INCREMENT PRIMARY KEY, ...);

-- MSSQL (T-SQL):
CREATE TABLE pengguna (id INT IDENTITY(1,1) PRIMARY KEY, ...);

-- LIMIT / PAGING
-- MySQL:
SELECT * FROM produk ORDER BY nama LIMIT 10 OFFSET 20;

-- MSSQL:
SELECT * FROM produk ORDER BY nama OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- String functions
-- MySQL: IFNULL(), GROUP_CONCAT()
-- MSSQL: ISNULL(), STRING_AGG()

-- Date functions
-- MySQL: NOW(), DATE_FORMAT()
-- MSSQL: GETDATE(), FORMAT()

-- Boolean
-- MySQL: BOOLEAN (alias for TINYINT(1)), TRUE/FALSE values
-- MSSQL: BIT (0 or 1, no TRUE/FALSE literals)

-- Backtick vs square bracket
-- MySQL: SELECT `nama` FROM `produk`
-- MSSQL: SELECT [nama] FROM [produk]  or  SELECT "nama" FROM "produk"

MSSQL Table Schema #

CREATE TABLE dbo.produk (
    id              INT IDENTITY(1,1) PRIMARY KEY,
    nama            NVARCHAR(255)   NOT NULL,        -- N prefix for Unicode
    deskripsi       NVARCHAR(MAX),                   -- equivalent to TEXT in MySQL
    harga           DECIMAL(15,2)   NOT NULL,
    stok            INT             NOT NULL DEFAULT 0,
    kategori        NVARCHAR(100),
    aktif           BIT             NOT NULL DEFAULT 1,  -- BIT not BOOLEAN
    dibuat_pada     DATETIME2       DEFAULT GETDATE(),
    diperbarui_pada DATETIME2       DEFAULT GETDATE()
);

-- Index
CREATE INDEX IX_produk_kategori ON dbo.produk(kategori);
CREATE INDEX IX_produk_aktif ON dbo.produk(aktif);

Use NVARCHAR (not VARCHAR) for text columns storing non-ASCII characters like Indonesian letters, Arabic, or other Unicode characters. NVARCHAR stores in UTF-16 format, so character length is counted differently — NVARCHAR(100) stores 100 Unicode characters.

CRUD with JDBC #

Model and Repository #

import java.math.BigDecimal
import java.sql.ResultSet
import java.sql.Types

data class Product(
    val id: Int = 0,
    val name: String,
    val description: String? = null,
    val price: BigDecimal,
    val stock: Int = 0,
    val category: String? = null,
    val active: Boolean = true
)

class MssqlProductRepository {

    private fun ResultSet.toProduct() = Product(
        id          = getInt("id"),
        name        = getString("nama"),
        description = getString("deskripsi"),
        price       = getBigDecimal("harga"),
        stock       = getInt("stok"),
        category    = getString("kategori"),
        active      = getBoolean("aktif")  // BIT is automatically converted to Boolean
    )

    // INSERT — MSSQL uses OUTPUT INSERTED.id to get the generated ID
    fun save(product: Product): Product {
        val sql = """
            INSERT INTO dbo.produk (nama, deskripsi, harga, stok, kategori, aktif)
            OUTPUT INSERTED.id
            VALUES (?, ?, ?, ?, ?, ?)
        """.trimIndent()

        return DatabaseMssql.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setString(1, product.name)
                // Null handling — setNull for nullable columns
                if (product.description != null) stmt.setString(2, product.description)
                else stmt.setNull(2, Types.NVARCHAR)

                stmt.setBigDecimal(3, product.price)
                stmt.setInt(4, product.stock)

                if (product.category != null) stmt.setString(5, product.category)
                else stmt.setNull(5, Types.NVARCHAR)

                stmt.setBoolean(6, product.active)

                stmt.executeQuery().use { rs ->
                    if (rs.next()) product.copy(id = rs.getInt(1))
                    else throw RuntimeException("Failed to get the generated ID")
                }
            }
        }
    }

    // SELECT with MSSQL pagination (OFFSET...FETCH)
    fun findAll(
        category: String? = null,
        activeOnly: Boolean = true,
        page: Int = 1,
        size: Int = 20
    ): List<Product> {
        val offset = (page - 1) * size
        val conditions = mutableListOf<String>()
        val params = mutableListOf<Any?>()

        if (activeOnly) conditions.add("aktif = 1")
        if (category != null) {
            conditions.add("kategori = ?")
            params.add(category)
        }

        val whereClause = if (conditions.isNotEmpty()) "WHERE ${conditions.joinToString(" AND ")}" else ""

        val sql = """
            SELECT *
            FROM dbo.produk
            $whereClause
            ORDER BY nama
            OFFSET ? ROWS
            FETCH NEXT ? ROWS ONLY
        """.trimIndent()

        return DatabaseMssql.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                var idx = 1
                params.forEach { p ->
                    when (p) {
                        is String -> stmt.setString(idx++, p)
                        else -> stmt.setObject(idx++, p)
                    }
                }
                stmt.setInt(idx++, offset)
                stmt.setInt(idx, size)

                stmt.executeQuery().use { rs ->
                    buildList { while (rs.next()) add(rs.toProduct()) }
                }
            }
        }
    }

    // SELECT with MSSQL TOP (pagination alternative)
    fun getTop(n: Int, activeOnly: Boolean = true): List<Product> {
        val sql = """
            SELECT TOP (?) *
            FROM dbo.produk
            ${if (activeOnly) "WHERE aktif = 1" else ""}
            ORDER BY harga DESC
        """.trimIndent()

        return DatabaseMssql.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setInt(1, n)
                stmt.executeQuery().use { rs ->
                    buildList { while (rs.next()) add(rs.toProduct()) }
                }
            }
        }
    }

    // UPDATE
    fun update(product: Product): Boolean {
        val sql = """
            UPDATE dbo.produk
            SET nama = ?, deskripsi = ?, harga = ?, stok = ?, kategori = ?
            WHERE id = ?
        """.trimIndent()

        return DatabaseMssql.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setString(1, product.name)
                if (product.description != null) stmt.setString(2, product.description)
                else stmt.setNull(2, Types.NVARCHAR)
                stmt.setBigDecimal(3, product.price)
                stmt.setInt(4, product.stock)
                if (product.category != null) stmt.setString(5, product.category)
                else stmt.setNull(5, Types.NVARCHAR)
                stmt.setInt(6, product.id)
                stmt.executeUpdate() > 0
            }
        }
    }

    // MERGE (UPSERT) — a distinctive T-SQL feature
    fun upsert(product: Product): Product {
        val sql = """
            MERGE dbo.produk AS target
            USING (SELECT ? AS nama, ? AS harga, ? AS stok, ? AS kategori) AS source
                ON target.nama = source.nama
            WHEN MATCHED THEN
                UPDATE SET harga = source.harga, stok = source.stok
            WHEN NOT MATCHED THEN
                INSERT (nama, harga, stok, kategori)
                VALUES (source.nama, source.harga, source.stok, source.kategori)
            OUTPUT INSERTED.id;
        """.trimIndent()

        return DatabaseMssql.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setString(1, product.name)
                stmt.setBigDecimal(2, product.price)
                stmt.setInt(3, product.stock)
                if (product.category != null) stmt.setString(4, product.category)
                else stmt.setNull(4, Types.NVARCHAR)

                stmt.executeQuery().use { rs ->
                    if (rs.next()) product.copy(id = rs.getInt(1))
                    else product
                }
            }
        }
    }
}

Transactions in MSSQL #

fun transferStockMssql(fromId: Int, toId: Int, quantity: Int) {
    DatabaseMssql.use { connection ->
        connection.autoCommit = false

        try {
            // Use WITH (UPDLOCK, ROWLOCK) to prevent deadlocks
            val checkStockSql = """
                SELECT stok FROM dbo.produk WITH (UPDLOCK, ROWLOCK)
                WHERE id = ?
            """.trimIndent()

            val sourceStock = connection.prepareStatement(checkStockSql).use { stmt ->
                stmt.setInt(1, fromId)
                stmt.executeQuery().use { rs ->
                    if (!rs.next()) throw NoSuchElementException("Product $fromId not found")
                    rs.getInt("stok")
                }
            }

            if (sourceStock < quantity) {
                throw IllegalStateException("Insufficient stock: available $sourceStock, requested $quantity")
            }

            connection.prepareStatement("UPDATE dbo.produk SET stok = stok - ? WHERE id = ?").use { stmt ->
                stmt.setInt(1, quantity)
                stmt.setInt(2, fromId)
                stmt.executeUpdate()
            }

            connection.prepareStatement("UPDATE dbo.produk SET stok = stok + ? WHERE id = ?").use { stmt ->
                stmt.setInt(1, quantity)
                stmt.setInt(2, toId)
                stmt.executeUpdate()
            }

            connection.commit()
            println("Transfer successful: $quantity units from product $fromId to $toId")

        } catch (e: Exception) {
            connection.rollback()
            println("Transfer failed, rolled back: ${e.message}")
            throw e
        } finally {
            connection.autoCommit = true
        }
    }
}

Stored Procedures #

Stored procedures are a powerful MSSQL feature often used in enterprise environments. Kotlin can call stored procedures through CallableStatement:

-- Stored procedure definition in SQL Server
CREATE PROCEDURE dbo.sp_CariProduk
    @Kategori NVARCHAR(100) = NULL,
    @HargaMaks DECIMAL(15,2) = NULL,
    @Limit INT = 20
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@Limit) id, nama, harga, stok, kategori
    FROM dbo.produk
    WHERE aktif = 1
      AND (@Kategori IS NULL OR kategori = @Kategori)
      AND (@HargaMaks IS NULL OR harga <= @HargaMaks)
    ORDER BY nama;
END;
GO

-- Stored procedure with output parameters
CREATE PROCEDURE dbo.sp_HitungStokTotal
    @Kategori NVARCHAR(100),
    @TotalStok INT OUTPUT,
    @JumlahProduk INT OUTPUT
AS
BEGIN
    SELECT
        @TotalStok = SUM(stok),
        @JumlahProduk = COUNT(*)
    FROM dbo.produk
    WHERE aktif = 1 AND kategori = @Kategori;
END;
GO

// Calling a stored procedure
fun searchProductsViaStoredProcedure(
    category: String? = null,
    maxPrice: BigDecimal? = null,
    limit: Int = 20
): List<Product> {
    return DatabaseMssql.use { connection ->
        // {call StoredProcedureName(?, ?, ?)}
        connection.prepareCall("{call dbo.sp_CariProduk(?, ?, ?)}").use { stmt ->
            // Named or positional parameters
            if (category != null) stmt.setString(1, category)
            else stmt.setNull(1, Types.NVARCHAR)

            if (maxPrice != null) stmt.setBigDecimal(2, maxPrice)
            else stmt.setNull(2, Types.DECIMAL)

            stmt.setInt(3, limit)

            stmt.executeQuery().use { rs ->
                buildList {
                    while (rs.next()) {
                        add(Product(
                            id       = rs.getInt("id"),
                            name     = rs.getString("nama"),
                            price    = rs.getBigDecimal("harga"),
                            stock    = rs.getInt("stok"),
                            category = rs.getString("kategori")
                        ))
                    }
                }
            }
        }
    }
}

// Stored procedure with output parameters
fun countStockByCategory(category: String): Pair<Int, Int> {
    return DatabaseMssql.use { connection ->
        connection.prepareCall("{call dbo.sp_HitungStokTotal(?, ?, ?)}").use { stmt ->
            stmt.setString(1, category)

            // Register the output parameters
            stmt.registerOutParameter(2, Types.INTEGER)  // @TotalStok
            stmt.registerOutParameter(3, Types.INTEGER)  // @JumlahProduk

            stmt.execute()

            val totalStock = stmt.getInt(2)
            val productCount = stmt.getInt(3)

            Pair(totalStock, productCount)
        }
    }
}

// Usage
fun main() {
    val products = searchProductsViaStoredProcedure(category = "Elektronik", limit = 5)
    products.forEach { println("${it.name}: Rp${it.price}") }

    val (totalStock, productCount) = countStockByCategory("Elektronik")
    println("Elektronik: $productCount products, total stock $totalStock units")
}

Exposed with MSSQL #

Exposed can be used with MSSQL with a dialect adjustment:

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

object MssqlProductTable : Table("dbo.produk") {
    val id        = integer("id").autoIncrement()
    val name      = varchar("nama", 255)
    val description = text("deskripsi").nullable()
    val price     = decimal("harga", 15, 2)
    val stock     = integer("stok").default(0)
    val category  = varchar("kategori", 100).nullable()
    val active    = bool("aktif").default(true)

    override val primaryKey = PrimaryKey(id)
}

fun initializeExposedMssql() {
    // Use the DataSource from HikariCP
    Database.connect(DatabaseMssql.dataSource)
}

// Queries with Exposed — the same as MySQL
fun searchProductsExposed(category: String? = null) = transaction {
    MssqlProductTable
        .select { MssqlProductTable.active eq true }
        .apply {
            if (category != null) {
                andWhere { MssqlProductTable.category eq category }
            }
        }
        .orderBy(MssqlProductTable.name)
        .map { row ->
            Product(
                id       = row[MssqlProductTable.id],
                name     = row[MssqlProductTable.name],
                price    = row[MssqlProductTable.price],
                stock    = row[MssqlProductTable.stock],
                category = row[MssqlProductTable.category],
                active   = row[MssqlProductTable.active]
            )
        }
}

Migrations with Flyway for MSSQL #

import org.flywaydb.core.Flyway

fun runMssqlMigrations() {
    val flyway = Flyway.configure()
        .dataSource(DatabaseMssql.dataSource)
        .locations("classpath:db/migration/mssql")
        .defaultSchema("dbo")
        .baselineOnMigrate(true)
        .validateOnMigrate(true)
        .load()

    val result = flyway.migrate()
    println("MSSQL Migration: ${result.migrationsExecuted} migrations executed")
}

Migration files for MSSQL must use T-SQL syntax:

-- src/main/resources/db/migration/mssql/V1__create_produk.sql
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'produk' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.produk (
        id              INT IDENTITY(1,1) PRIMARY KEY,
        nama            NVARCHAR(255)   NOT NULL,
        deskripsi       NVARCHAR(MAX),
        harga           DECIMAL(15,2)   NOT NULL,
        stok            INT             NOT NULL DEFAULT 0,
        kategori        NVARCHAR(100),
        aktif           BIT             NOT NULL DEFAULT 1,
        dibuat_pada     DATETIME2       DEFAULT GETDATE()
    );

    CREATE INDEX IX_produk_kategori ON dbo.produk(kategori);
    CREATE INDEX IX_produk_aktif ON dbo.produk(aktif);
END;
GO

MSSQL-Specific Tips #

Windows Authentication #

// If the application runs on Windows and needs Windows Authentication
val windowsAuthUrl = buildString {
    append("jdbc:sqlserver://localhost:1433")
    append(";databaseName=myapp")
    append(";integratedSecurity=true")
    append(";encrypt=false")
    // No username/password needed — uses the current user's Windows credentials
}

// For Windows Auth, add the DLL to PATH or as a dependency
// implementation("com.microsoft.sqlserver:mssql-jdbc_auth:12.6.1.x64") // Windows only

Handling Deadlocks #

MSSQL detects deadlocks more aggressively than MySQL. Handle SQLServerException with error code 1205:

import com.microsoft.sqlserver.jdbc.SQLServerException

fun executeWithRetry(block: () -> Unit, maxAttempts: Int = 3) {
    var attempts = 0
    while (attempts < maxAttempts) {
        try {
            block()
            return
        } catch (e: SQLServerException) {
            if (e.errorCode == 1205 && attempts < maxAttempts - 1) {
                // Error 1205 = deadlock victim
                attempts++
                println("Deadlock detected, attempt $attempts...")
                Thread.sleep(100L * attempts)  // simple exponential backoff
            } else {
                throw e
            }
        }
    }
}

Summary #

Official Microsoft driver — use com.microsoft.sqlserver:mssql-jdbc (not the outdated jtds). The Microsoft driver supports all modern SQL Server features including encryption and AAD.
NVARCHAR for Unicode text — always use NVARCHAR (not VARCHAR) for columns storing text that may contain non-ASCII characters. In JDBC, both setNString() and setString() work with NVARCHAR.
OUTPUT INSERTED to get IDs — in MSSQL, INSERT ... OUTPUT INSERTED.id is more reliable than getGeneratedKeys() for getting a newly generated IDENTITY value. It can also return many columns at once.
OFFSET…FETCH for pagination — replace MySQL’s LIMIT...OFFSET with OFFSET n ROWS FETCH NEXT m ROWS ONLY in T-SQL. An ORDER BY clause is mandatory when using OFFSET.
MERGE for upserts — T-SQL has a powerful MERGE syntax for insert-or-update operations based on conditions. More explicit and flexible than MySQL’s INSERT ... ON DUPLICATE KEY UPDATE.
Stored procedures with CallableStatement — in MSSQL enterprise environments, stored procedures are often used for database-side business logic. CallableStatement with the {call sp_name(?, ?)} syntax handles input and output parameters.
WITH (UPDLOCK, ROWLOCK) to prevent deadlocks — when reading data that will soon be updated within one transaction, use these hints to acquire locks earlier and avoid deadlocks.
Handle deadlocks with retry — MSSQL picks one transaction as the “deadlock victim” (error 1205) and rolls it back. Handle this with a retry mechanism using exponential backoff.

← Previous: MySQL Next: Oracle →