SQL Data Types and CREATE TABLE: Numbers, Text, Date/Time and the Turkish I Collation Trap

Version note: The SQL (Structured Query Language) queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10); the PostgreSQL comparisons were run on PostgreSQL 18.6.

Guess First: How Do You Uppercase "Saatleri Ayarlama Enstitüsü"?

The Bookstore catalog has four Turkish books. We uppercase the titles of two of them. Before running it, guess the output of the second row:

SELECT title,
       UPPER(title) AS upper_title
FROM dbo.books
WHERE book_id IN (75, 77);

And a classic query coming from a search box: the user types "ince" and title is a case-insensitive column. Does İnce Memed show up?

SELECT book_id, title
FROM dbo.books
WHERE title LIKE N'ince%';

The real output:

title                        upper_title
---------------------------  ---------------------------
İnce Memed                   İNCE MEMED
Saatleri Ayarlama Enstitüsü  SAATLERI AYARLAMA ENSTITÜSÜ

(2 rows affected)

book_id  title
-------  -----

(0 rows affected)

"SAATLERI ... ENSTITÜSÜ": the dotted i's turned into dotless I's. The search returned nothing at all. The reason is a single word: collation. The database's default collation is SQL_Latin1_General_CP1_CI_AS, and by its rules the uppercase of "i" is "I", while "İ" is not the same letter as "i". In this post we look at this trap, the data types that produce it, and how those types are chosen with CREATE TABLE.

Standard Types and Their T-SQL Equivalents

The ISO (International Organization for Standardization) SQL standard groups types into families: integers, exact numerics, approximate numerics, character strings, date and time, boolean and binary. Every product implements these families under its own names, and the names do not always line up:

The note "in T-SQL TIMESTAMP is not a date" is a trap. In the standard TIMESTAMP means date and time; in T-SQL it is the old name of rowversion, an 8-byte counter that changes on every update:

CREATE TABLE dbo.ts_demo (id INT, changed TIMESTAMP);
INSERT INTO dbo.ts_demo (id) VALUES (1);
SELECT id, changed FROM dbo.ts_demo;

id  changed
--  ------------------
1   0x00000000000007D1

A created TIMESTAMP column ported from PostgreSQL is created on SQL Server without an error and holds this binary value instead of a date. The counter is used for optimistic concurrency; the Entity Framework Core (EF Core) series covered it in "EF Core'da Concurrency: Token, rowversion ve DbUpdateConcurrencyException" (concurrency tokens and rowversion).

Numbers: INT, BIGINT, DECIMAL and FLOAT

INT is 4 bytes and ends at 2,147,483,647. When it overflows, SQL Server does not silently wrap around; it raises an error:

DECLARE @stock INT = 2147483647;
SELECT @stock + 1 AS next_value;

Msg 8115, Level 16, State 2, Server b8be943222fa, Line 2
Arithmetic overflow error converting expression to data type int.

Declare the same variable as BIGINT and the result is 2147483648. At 800 orders a day, orders.order_id would take thousands of years to fill an INT; but in log, event or click tables the identity value can hit that limit within a few years. The rule is simple: if the row count could ever pass billions, use BIGINT from the start. Changing a primary key's type later means changing every foreign key that points to it.

For money the only right choice is DECIMAL(p,s): p is the total number of digits (precision) and s the digits after the decimal point (scale). list_price DECIMAL(10,2) holds up to 99,999,999.99 and stores values exactly in decimal. FLOAT, on the other hand, is an approximate number in base two; 0.1 cannot be written exactly in binary:

SELECT CASE WHEN CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT) = CAST(0.3 AS FLOAT) THEN 'equal' ELSE 'not equal' END AS float_check,
       CASE WHEN CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) = 0.3 THEN 'equal' ELSE 'not equal' END AS decimal_check;

float_check  decimal_check
-----------  -------------
not equal    equal

(1 rows affected)

In sums the error accumulates. Imagine adding a 0.10 TRY (Turkish lira) fee to each of the 379 orders:

SELECT SUM(CAST(0.1 AS FLOAT)) AS float_sum, SUM(CAST(0.1 AS DECIMAL(10,2))) AS decimal_sum
FROM dbo.orders;

float_sum           decimal_sum
------------------  -----------
37.900000000000269  37.90

(1 rows affected)

In a report the difference disappears in rounding, but an equality check such as WHERE total = 37.90 or an accounting reconciliation will not match. FLOAT exists for scientific measurements, coordinates and statistics, not for money, quantities and stock. DECIMAL has its own discipline too: a value beyond the scale is silently rounded, a value beyond the precision raises an error.

SELECT CAST(19.999 AS DECIMAL(10,2)) AS rounded,
       CAST(10 AS DECIMAL(10,2)) / 3 AS divided;
SELECT CAST(123456789.50 AS DECIMAL(10,2)) AS too_big;

rounded  divided
-------  --------
20.00    3.333333

(1 rows affected)
Msg 8115, Level 16, State 8, Server b8be943222fa, Line 1
Arithmetic overflow error converting numeric to data type numeric.

The result of the division is not DECIMAL(10,2); SQL Server computes the result type's precision and scale from the operands, and here it came out with 6 decimal places. When you write the result into a column, know which type and which rounding it will land in.

Text: CHAR, VARCHAR, NVARCHAR and UTF-8

CHAR(n) is fixed-length and pads the remaining space with blanks; VARCHAR(n) is variable-length; the leading N stands for "national" and stores Unicode (UTF-16), at least 2 bytes per character:

SELECT DATALENGTH(CAST('Dune' AS CHAR(10))) AS char10,
       DATALENGTH(CAST('Dune' AS VARCHAR(10))) AS varchar10,
       DATALENGTH(CAST(N'Dune' AS NVARCHAR(10))) AS nvarchar10,
       '[' + CAST('Dune' AS CHAR(10)) + ']' AS char_padded;

char10  varchar10  nvarchar10  char_padded
------  ---------  ----------  ------------
10      4          8           [Dune      ]

(1 rows affected)

CHAR only makes sense for values whose length really is fixed: in Bookstore isbn CHAR(13), language_code CHAR(2), country_code CHAR(2). The real decision is between VARCHAR and NVARCHAR. Classic VARCHAR is bound to a code page, and the code page is set by the collation. Our database's collation uses code page 1252, which has "ü" but not "İ" or "ğ":

SELECT CAST(N'İnce Memed' AS VARCHAR(50)) AS as_varchar,
       CAST(N'Saatleri Ayarlama Enstitüsü' AS VARCHAR(50)) AS as_varchar2,
       CAST(N'Oğuz Atay' AS VARCHAR(50)) AS as_varchar3;

as_varchar  as_varchar2                  as_varchar3
----------  ---------------------------  -----------
Ince Memed  Saatleri Ayarlama Enstitüsü  Oguz Atay

(1 rows affected)

No error, no warning: "İ" silently became "I", "ğ" silently became "g", and that data never comes back. The same loss happens when you forget the N in front of a string literal, because a literal without N is VARCHAR too:

SELECT 'Oğuz' AS without_n, N'Oğuz' AS with_n;

without_n  with_n
---------  ------
Oguz       Oğuz

(1 rows affected)

That is why everything written in human language is NVARCHAR in Bookstore: title, first_name, city. email and phone are VARCHAR because their content is limited to ASCII (American Standard Code for Information Interchange) characters. Since SQL Server 2019 there is a third way: with a collation whose name ends in _UTF8, VARCHAR stores UTF-8 (Unicode Transformation Format, 8-bit):

SELECT CAST(N'Oğuz Atay' COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS VARCHAR(50)) AS utf8_varchar,
       DATALENGTH(CAST(N'Oğuz Atay' COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS VARCHAR(50))) AS utf8_bytes,
       DATALENGTH(N'Oğuz Atay') AS nvarchar_bytes;

utf8_varchar  utf8_bytes  nvarchar_bytes
------------  ----------  --------------
Oğuz Atay     10          18

(1 rows affected)

"ğ" takes 2 bytes in UTF-8, the other letters 1 byte; 10 bytes in total. The same text takes 18 bytes in NVARCHAR. For mostly Latin text UTF-8 saves space; but the 50 in VARCHAR(50) is now a byte count, not a character count, and text rich in Turkish characters can fill it sooner than you expect.

Then there is length. Making every column NVARCHAR(MAX) "so it always fits" is tempting, but MAX columns may be stored off-row for large values, cannot be an index key, and their length stops being a business rule. title NVARCHAR(200) is both documentation and validation: a book title longer than 200 characters is probably a mistake.

Date and Time: DATE, DATETIME2 and DATETIMEOFFSET

For a new column the options are: DATE (3 bytes) for a date only, DATETIME2(n) for date and time, and DATETIMEOFFSET(n) if you also need to keep the offset. n is the number of fractional-second digits, from 0 to 7:

SELECT CAST('2025-12-31 23:59:59.1234567' AS DATETIME2(7)) AS dt2_7,
       CAST('2025-12-31 23:59:59.1234567' AS DATETIME2(0)) AS dt2_0,
       CAST('2025-12-31 23:59:59.1234567 +03:00' AS DATETIMEOFFSET(0)) AS dto,
       CAST('2025-12-31' AS DATE) AS d;

dt2_7                        dt2_0                dto                         d
---------------------------  -------------------  --------------------------  ----------
2025-12-31 23:59:59.1234567  2025-12-31 23:59:59  2025-12-31 23:59:59 +03:00  2025-12-31

(1 rows affected)

Do not put the old DATETIME type into new code. Its accuracy is about 1/300 of a second and it rounds values to instants ending in .000, .003 or .007. That is why classic code that writes the end of the day as "23:59:59.999" lands on the wrong day:

SELECT CAST('2025-12-31 23:59:59.997' AS DATETIME) AS dt_997,
       CAST('2025-12-31 23:59:59.998' AS DATETIME) AS dt_998,
       CAST('2025-12-31 23:59:59.999' AS DATETIME) AS dt_999,
       CAST('2025-12-31 23:59:59.999' AS DATETIME2(3)) AS dt2_999;

dt_997                   dt_998                   dt_999                   dt2_999
-----------------------  -----------------------  -----------------------  -----------------------
2025-12-31 23:59:59.997  2025-12-31 23:59:59.997  2026-01-01 00:00:00.000  2025-12-31 23:59:59.999

(1 rows affected)

The value written as "the last moment of December 31" became midnight on January 1, 2026. The BETWEEN upper bound of a yearly report would also collect the first orders of the next year. The right fix is not changing the type but writing ranges half-open (>= start and < end); we will do that in post 6. Size favours DATETIME2 as well: DATETIME is 8 bytes, DATETIME2(0) 6 bytes, DATETIME2(7) 8 bytes.

In Bookstore every timestamp is DATETIME2(0) in UTC. The rule: store the instant in UTC and convert to local time when you show it to the user. Order 263, placed on New Year's Eve, is a good example:

SELECT o.order_id, o.ordered_at AS utc,
       o.ordered_at AT TIME ZONE 'UTC' AT TIME ZONE 'Turkey Standard Time' AS istanbul
FROM dbo.orders AS o
WHERE o.order_id = 263;

order_id  utc                  istanbul
--------  -------------------  --------------------------
263       2025-12-31 23:06:27  2026-01-01 02:06:27 +03:00

(1 rows affected)

The first AT TIME ZONE 'UTC' labels the offset-less value as "this is UTC"; the second converts it to Istanbul time. The order is in 2025 by UTC and in 2026 by Istanbul time. Which time zone the "2025 sales" report is cut by is a business decision and must be written explicitly in the query. Time zone names are Windows names, even on SQL Server on Linux. The design side of this topic is in the Solution Pattern series post "Zaman ve Time Zone: UTC Instant, Yerel İş Günü, DateOnly ve TimeProvider" (time and time zones).

BIT and UNIQUEIDENTIFIER

T-SQL has no boolean column; it has BIT, which takes 0, 1 or NULL. On conversion every non-zero number becomes 1, and the strings 'true' and 'false' are accepted too:

SELECT CAST('true' AS BIT) AS from_true, CAST(5 AS BIT) AS from_five,
       CAST('6F9619FF-8B86-D011-B42D-00C04FC964FF' AS UNIQUEIDENTIFIER) AS guid_value,
       DATALENGTH(NEWID()) AS guid_bytes;

from_true  from_five  guid_value                            guid_bytes
---------  ---------  ------------------------------------  ----------
1          1          6F9619FF-8B86-D011-B42D-00C04FC964FF  16

(1 rows affected)

UNIQUEIDENTIFIER is a 16-byte GUID (Globally Unique Identifier). It is handy for ids exposed to the outside and for records generated in several systems, but the effect of making a random GUID the clustered primary key on the index is a design topic. The short answer comes in the next post, the long one in the "SQL Server Derinlemesine" (SQL Server in Depth) series.

Collation and the Turkish I Trap

A collation is the set of rules that decides how text is compared and sorted: which letter is the uppercase of which, whether case matters, that is CI (case-insensitive) or CS (case-sensitive), whether accents matter, that is AI (accent-insensitive) or AS (accent-sensitive), and which code page VARCHAR uses. Turkish differs in four letters: i ↔ İ and ı ↔ I. Three comparisons make it clear:

SELECT CASE WHEN N'istanbul' = N'ISTANBUL' COLLATE Latin1_General_100_CI_AS THEN 'equal' ELSE 'different' END AS latin1,
       CASE WHEN N'istanbul' = N'ISTANBUL' COLLATE Turkish_100_CI_AS THEN 'equal' ELSE 'different' END AS turkish,
       CASE WHEN N'istanbul' = N'İSTANBUL' COLLATE Turkish_100_CI_AS THEN 'equal' ELSE 'different' END AS turkish_dotted;

latin1  turkish    turkish_dotted
------  ---------  --------------
equal   different  equal

(1 rows affected)

In the Turkish collation "istanbul" and "ISTANBUL" are different words, because the uppercase of "i" is "İ". Both are correct; which one is right depends on the language of the data. Let's rerun the two opening queries with Turkish rules. COLLATE applies another collation to an expression inside a query:

SELECT title,
       UPPER(title COLLATE Turkish_100_CI_AS) AS upper_title
FROM dbo.books
WHERE book_id IN (75, 77);

SELECT book_id, title
FROM dbo.books
WHERE title COLLATE Turkish_100_CI_AS LIKE N'ince%';

title                        upper_title
---------------------------  ---------------------------
İnce Memed                   İNCE MEMED
Saatleri Ayarlama Enstitüsü  SAATLERİ AYARLAMA ENSTİTÜSÜ

(2 rows affected)

book_id  title
-------  ----------
75       İnce Memed

(1 rows affected)

Correct results. But writing COLLATE inside a query has a price: when a comparison uses a rule other than the column's own collation, an index on that column cannot be used for seeking. The lasting fix is to create the column (or the database) with the right collation from the start. PostgreSQL is similar; its default collation does not know Turkish rules, and you have to ask for an ICU (International Components for Unicode) based Turkish collation explicitly:

SELECT upper('Saatleri Ayarlama Enstitüsü') AS default_collation,
       upper('Saatleri Ayarlama Enstitüsü' COLLATE "tr-TR-x-icu") AS turkish_icu;

      default_collation      |         turkish_icu
-----------------------------+-----------------------------
 SAATLERI AYARLAMA ENSTITÜSÜ | SAATLERİ AYARLAMA ENSTİTÜSÜ
(1 row)

When different collations meet in the same query, you also get an error. Let's create an import table with a Turkish collation and join it to the catalog:

CREATE TABLE dbo.import_titles (
    title NVARCHAR(200) COLLATE Turkish_100_CI_AS NOT NULL
);
INSERT INTO dbo.import_titles (title) VALUES (N'İnce Memed'), (N'Dune');

SELECT b.book_id, b.title
FROM dbo.books AS b
JOIN dbo.import_titles AS i
    ON i.title = b.title;

Msg 468, Level 16, State 9, Server b8be943222fa, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Turkish_100_CI_AS" in the equal to operation.

SQL Server cannot decide which of the two rules to use. Adding COLLATE Turkish_100_CI_AS to one side of the comparison makes the join work and it finds both books. You will meet this error most often with temporary tables in tempdb when the database collation differs from the server collation: the columns of a temporary table take the collation of tempdb, that is, of the server.

CREATE TABLE and Schemas

Now let's put these decisions together and add a table for book editions to Bookstore. First we open our own schema. A schema is a namespace that groups tables and a permission boundary; dbo is the default schema:

DROP TABLE IF EXISTS catalog.book_editions;
DROP SCHEMA IF EXISTS catalog;
GO
CREATE SCHEMA catalog;
GO
CREATE TABLE catalog.book_editions (
    edition_id      INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    book_id         INT               NOT NULL,
    edition_label   NVARCHAR(100)     NOT NULL,
    language_code   CHAR(2)           NOT NULL,
    isbn            CHAR(13)          NOT NULL,
    page_count      SMALLINT          NULL,
    list_price      DECIMAL(10,2)     NOT NULL,
    weight_grams    INT               NULL,
    is_hardcover    BIT               NOT NULL DEFAULT (0),
    published_on    DATE              NULL,
    created_at      DATETIME2(0)      NOT NULL DEFAULT (SYSUTCDATETIME())
);
INSERT INTO catalog.book_editions (book_id, edition_label, language_code, isbn, page_count, list_price, published_on)
VALUES (75, N'İnce Memed 1, pocket edition', 'tr', '9798900000751', 436, 289.90, '2024-03-01');
SELECT edition_id, edition_label, list_price, is_hardcover, created_at
FROM catalog.book_editions;

edition_id  edition_label                 list_price  is_hardcover  created_at
----------  ----------------------------  ----------  ------------  -------------------
1           İnce Memed 1, pocket edition  289.90      0             2026-11-08 12:00:27

(1 rows affected)

A few rules can be read from this. CREATE SCHEMA must be alone in its batch; GO is not a T-SQL command but a separator that sqlcmd, SSMS (SQL Server Management Studio) and Visual Studio Code recognise to split a script into batches. DROP TABLE IF EXISTS exists since SQL Server 2016 and makes scripts re-runnable. IDENTITY(1,1) lets the server generate the value, and DEFAULTs fill the columns you did not write: is_hardcover 0, created_at the current UTC time. Bookstore's naming rule is snake_case, plural table names and a primary key named <table>_id. Which rule you pick matters less than picking one and applying it everywhere. This table is missing constraints: book_id does not have to point to a book that exists, and list_price can be negative. We will add them in the next post.

In Production

Choose the collation when you create the database; changing it later does not change existing columns. ALTER DATABASE ... COLLATE only affects the database default and new columns; old columns keep the old collation and become exactly the source of the conflict error in this post. In a container, the server collation is set at install time with the MSSQL_COLLATION environment variable. For a mostly Turkish application Turkish_100_CI_AS is a sensible default; for a multilingual application it is more flexible to keep a Latin1-based default and give a column-level collation to the columns that need Turkish sorting and searching.

Changing types is expensive. Going from INT to BIGINT or from VARCHAR to NVARCHAR on a large table means rewriting every row and locking the table until it finishes. Choose the type by thinking about the row count and the language of the data three years from now. If you use EF Core, override the default mappings of .NET types (such as nvarchar(max) for string) deliberately; this is covered in the EF Core series post "EF Core'da Model ve Convention'lar: Key, Nullable, Kolon Tipleri ve Shadow Property" (model and conventions).

Common Pitfalls

• Using FLOAT or REAL for money. Even 379 small additions made the error visible.
• VARCHAR with code page 1252 for Turkish data: "İ" and "ğ" vanish without an error or warning. A string literal without N causes the same loss.
• Making every column NVARCHAR(MAX); length is a business rule, and columns that cannot be an index key cause trouble later.
• Storing dates as strings. '31.12.2025' and '2025-12-31' cannot be sorted, validated or used in date arithmetic.
• Writing "23:59:59.999" with the old DATETIME and getting rounded into the next day.
• Storing local time without an offset and losing which time zone it belongs to. Store UTC or use DATETIMEOFFSET.
• Searching Turkish text in a Latin1 collation and not finding "İnce Memed" with "ince".

A Small Experiment

★☆☆ List the column types of the customers table from INFORMATION_SCHEMA.COLUMNS and write one sentence for each explaining why the type was chosen. Why is email VARCHAR(254)?

★★☆ For Turkish_100_CI_AS and Turkish_100_CS_AS, first guess and then run the comparisons N'ırmak' = N'IRMAK' and N'ırmak' = N'irmak'.

★★★ Create a VARCHAR(27) column with the Latin1_General_100_CI_AS_SC_UTF8 collation and try to write the 27-character "Saatleri Ayarlama Enstitüsü" into it. If you get an error, why? Repeat with NVARCHAR(27) and compare the LEN and DATALENGTH values.

As it stands, catalog.book_editions accepts anything: a book that does not exist, a negative price, two editions with the same ISBN (International Standard Book Number). In the next post we make this table rule-bound with primary key, foreign key, UNIQUE, CHECK and DEFAULT constraints, and discuss the order for changing a schema safely in production with ALTER TABLE.