Fluent SQL

CREATE TABLE

In Fluent SQL, table objects can be used to represent fully-qualified table names in SELECT statements. They can also be used to generate CREATE TABLE SQL statements. How the SQL is rendered depends on the context.

We saw earlier how to create a Table for use in a SELECT statement’s FROM clause.

var customer = Table.Create("Customer");

var customer = Table.Create("MACHINE123", "dbo", "Customer");

Generate the SQL directly from the table object and write it to the console we get an error.

Console.WriteLine(customer.ToSQLOrError());

-- SQL Error: Create table statement must have column definitions.

Inside a SELECT statement it makes sense, but by itself there isn’t enough information to generate meaningful SQL. We need to add column definitions. These can be supplied as a parameter array.

Column definitions

The easiest way to create column definition is to start with the column name as a string and chain data typing extension methods onto that name. Thirty extension methods, some with multiple overloads, give access to all of SQL Server’s various binary, character, date, and numeric data types. Try "Name".NVarChar(100), "Description".NVarCharMax(), "Weight".Decimal(), "Weight".Decimal(7), or "Weight".Decimal(7, 3).

All column defintion types have further extension methods like Null() and NotNull() that allow you to add additional detail. The integer types also have an Identity() method to set up auto-incrementing identity columns.

var customer = Table.Create("Customer",
    "ID".Int().Identity(1, 1).NotNull(),
    "Name".NVarChar(100));

CREATE TABLE [Customer] (
    [ID] INT IDENTITY(1, 1) NOT NULL,
    [Name] NVARCHAR(100)
);

Some combinations are illegal. For example, you can’t make a column both nullable and non-nullable. The type system isn’t strong-enough to prevent this at compile-time, but it will generate an error rather than illegal SQL.

Calling the PrimaryKey() method automatically makes a column non-nullable, so trying to make a primary key field nullable will generate a run-time error.

var customer = Table.Create("Customer",
    "ID".Int().Identity(1, 1).PrimaryKey().Null(),
    "Name".NVarChar(100));

-- SQL Error: Column cannot be both nullable and non-nullable.

Compression Options

To create a table with a data compression option, chain the With() method onto the table definition and pass in the compression option. Options are None(), Row(), and Page().

var customer = Table.Create("Customer",
    "ID".Int().Identity(1, 1).PrimaryKey(),
    "Name".NVarChar(100))
    .With(TableDataCompression.Page());

CREATE TABLE [Customer] (
    [ID] INT IDENTITY(1, 1) NOT NULL,
    [Name] NVARCHAR(100)
)
WITH (DATA_COMPRESSION = PAGE);