Administration

Administration & Schema Management

SQLServerNIO provides specialized "Client" classes to safely generate and execute Data Definition Language (DDL) and management instructions.

---

General Administration

Use the SQLServerAdministrationClient to list and modify database infrastructure.

let admin = SQLServerAdministrationClient(client: client)

// Fetching database properties
let dbProps = try await admin.fetchDatabaseProperties(name: "MyDatabase")

// Creating tables programmatically
let columns = [
    SQLServerColumnDefinition(name: "id", definition: .standard(.init(dataType: .int, isPrimaryKey: true))),
    SQLServerColumnDefinition(name: "email", definition: .standard(.init(dataType: .nvarchar(length: .length(150)))))
]
try await admin.createTable(name: "Users", columns: columns)

Indexes and Constraints

Use the SQLServerIndexClient and SQLServerConstraintClient.

let indexClient = SQLServerIndexClient(client: client)

// Creating highly tuned indexes
try await indexClient.createIndex(
    name: "IX_Users_LastName_Incl",
    table: "Users",
    columns: [
        IndexColumn(name: "last_name"),
        IndexColumn(name: "first_name", isIncluded: true), // INCLUDE column
        IndexColumn(name: "email", isIncluded: true)
    ]
)

// Constraints
let constraintClient = SQLServerConstraintClient(client: client)
try await constraintClient.addCheckConstraint(
    name: "CK_Users_Age",
    table: "Users",
    expression: "age >= 18"
)

Bulk Copy

For ingesting vast amounts of tabular data efficiently, TDS exposes Bulk Copy operations. Do not use standard INSERT loops when SQLServerBulkCopyClient exists.

let bulkCopy = SQLServerBulkCopyClient(client: client)

let options = SQLServerBulkCopyOptions(
    table: "TelemetryLogs",
    columns: ["device_id", "payload", "created_at"],
    batchSize: 5000 // Send to server 5000 rows at a time
)

// Generate massive row payload
let rows = [
    SQLServerBulkCopyRow(values: [.nString("DEV-01"), .nString("{}"), .date(Date())]),
    // ... 10,000 more rows
]

// Execute Bulk ingestion
let summary = try await bulkCopy.copy(rows: rows, options: options)
print("Finished. Inserted \(summary.totalRows) in \(summary.duration) seconds.")

Table-Valued Parameters

For sending collections of objects into a Stored Procedure or complex script, SQLServerTableValuedParameter securely streams data types matching a predefined User-Defined Table Type in SQL Server.

let parameter = SQLServerTableValuedParameter(
    name: "TvpUsers", // Corresponds to the @TvpUsers parameter
    columns: [
        .init(name: "id", dataType: .int),
        .init(name: "name", dataType: .nvarchar(length: .length(100)))
    ],
    rows: [
        .init(values: [.int(1), .nString("Alice")]),
        .init(values: [.int(2), .nString("Bob")])
    ]
)

try await client.execute("""
INSERT INTO dbo.Users (id, name)
SELECT id, name FROM @TvpUsers;
""", tableParameters: [parameter])