Skip to main content

​
Overview

PruebaETL uses specific SQL Server data types to ensure data integrity, proper storage, and optimal performance. This reference documents all data types used in the database schema.

​
SQL Server Data Types

​
NVARCHAR

Description: Variable-length Unicode string data type that supports international characters. Usage in PruebaETL:
  • NVARCHAR(255) - Cliente names
  • NVARCHAR(100) - City names
  • NVARCHAR(50) - Segments, channels, date strings in raw tables
  • NVARCHAR(10) - Currency codes
Why Unicode? PruebaETL handles Spanish text with special characters (á, é, í, ó, ú, ñ, ü) and requires full Unicode support for data integrity. 
CREATE TABLE clientes (
    cliente_id INT PRIMARY KEY,
    nombre NVARCHAR(255) NOT NULL,  -- Supports "Raúl Pérez"
    ...
);
NVARCHAR uses 2 bytes per character. Use it for all text fields that may contain special characters.

​
DECIMAL

Description: Exact numeric data type with fixed precision and scale. Usage in PruebaETL:
  • DECIMAL(18,2) - Monetary amounts (total sales)
Format:
  • 18 digits total precision
  • 2 digits after decimal point
  • Range: -999,999,999,999,999.99 to 999,999,999,999,999.99
Why DECIMAL? Financial calculations require exact precision. FLOAT/REAL can introduce rounding errors. 
CREATE TABLE ventas (
    venta_id INT PRIMARY KEY,
    total DECIMAL(18,2) NOT NULL,  -- Exact monetary values
    ...
);
Never use FLOAT or REAL for monetary values. Always use DECIMAL for financial data.

​
DATE

Description: Stores only the date (year, month, day) without time component. Usage in PruebaETL:
  • Cliente registration dates (fecha_registro)
  • Sale transaction dates (fecha)
Format: YYYY-MM-DD Storage: 3 bytes Range: 0001-01-01 to 9999-12-31 
CREATE TABLE clientes (
    cliente_id INT PRIMARY KEY,
    fecha_registro DATE,  -- Example: 2024-11-23
    ...
);
Use DATE instead of DATETIME when you don’t need time information. It saves storage space (3 bytes vs 8 bytes).

​
DATETIME

Description: Stores both date and time with precision to milliseconds. Usage in PruebaETL:
  • Audit timestamps (fecha_creacion, fecha_actualizacion)
  • Raw data load timestamps (fecha_carga)
Format: YYYY-MM-DD HH:MM:SS.mmm Storage: 8 bytes Range: 1753-01-01 to 9999-12-31 Precision: 3.33 milliseconds 
CREATE TABLE clientes (
    cliente_id INT PRIMARY KEY,
    fecha_creacion DATETIME DEFAULT GETDATE(),
    fecha_actualizacion DATETIME DEFAULT GETDATE(),
    ...
);
GETDATE() is used as default value to automatically capture creation and update timestamps.

​
INT

Description: 4-byte integer data type. Usage in PruebaETL:
  • Primary keys (with IDENTITY for auto-increment)
  • Foreign keys
  • All ID fields
Range: -2,147,483,648 to 2,147,483,647 Storage: 4 bytes 
CREATE TABLE ciudades (
    ciudad_id INT PRIMARY KEY IDENTITY(1,1),  -- Auto-incrementing
    ...
);

CREATE TABLE clientes (
    cliente_id INT PRIMARY KEY,  -- Preserved from CSV
    ciudad_id INT NOT NULL,      -- Foreign key
    ...
);

​
Data Type Selection Guidelines

​
Text Data

ScenarioData TypeExample
Names, descriptionsNVARCHAR(255)Cliente names
Short codesNVARCHAR(10)Currency codes (USD, DOP)
Medium textNVARCHAR(100)City names
CategoriesNVARCHAR(50)Segments, channels

​
Numeric Data

ScenarioData TypeExample
Monetary amountsDECIMAL(18,2)Sale totals
IDs and countersINTPrimary/foreign keys

​
Date/Time Data

ScenarioData TypeExample
Business datesDATERegistration date, sale date
Audit timestampsDATETIMECreated at, updated at

​
Special Considerations

​
NULL vs NOT NULL

NOT NULL fields:
  • Primary keys
  • Foreign keys
  • Required business fields (nombre, total)
Nullable fields:
  • Optional dates (fecha_registro)
  • Historical audit fields
-- Required field
nombre NVARCHAR(255) NOT NULL

-- Optional field
fecha_registro DATE  -- Can be NULL

​
IDENTITY Property

Used for: Auto-incrementing primary keys in master tables. Syntax: IDENTITY(seed, increment) 
CREATE TABLE ciudades (
    ciudad_id INT PRIMARY KEY IDENTITY(1,1),  -- Starts at 1, increments by 1
    ...
);
Do not use IDENTITY for business tables (clientes, ventas) where IDs come from source data.

​
DEFAULT Values

Automatic timestamps: 
fecha_creacion DATETIME DEFAULT GETDATE()
fecha_carga DATETIME DEFAULT GETDATE()

​
Complete Schema Example

-- Master table with IDENTITY
CREATE TABLE ciudades (
    ciudad_id INT PRIMARY KEY IDENTITY(1,1),
    nombre NVARCHAR(100) NOT NULL UNIQUE,
    fecha_creacion DATETIME DEFAULT GETDATE()
);

-- Business table with foreign keys
CREATE TABLE clientes (
    cliente_id INT PRIMARY KEY,
    nombre NVARCHAR(255) NOT NULL,
    ciudad_id INT NOT NULL,
    segmento_id INT NOT NULL,
    fecha_registro DATE,
    fecha_creacion DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_clientes_ciudad FOREIGN KEY (ciudad_id) 
        REFERENCES ciudades(ciudad_id)
);

-- Transaction table with DECIMAL for money
CREATE TABLE ventas (
    venta_id INT PRIMARY KEY,
    cliente_id INT NOT NULL,
    fecha DATE,
    total DECIMAL(18,2) NOT NULL,
    moneda_id INT NOT NULL,
    fecha_creacion DATETIME DEFAULT GETDATE(),
    CONSTRAINT FK_ventas_cliente FOREIGN KEY (cliente_id) 
        REFERENCES clientes(cliente_id)
);

Functions Reference

Learn about data normalization functions

Troubleshooting

Common data type issues and solutions

Build docs developers (and LLMs) love

Get started for freeTalk to us