Introduction to SQL Server Data Types and SELECT Queries
SQL Server is a powerful relational database management system (RDBMS) used in ERP systems to manage data across various business functions. Understanding SQL Server data types and basic SELECT queries is foundational for students aiming to work with databases in business contexts. Data types define how data is stored, while SELECT queries retrieve data for reporting, analysis, and decision-making in ERP modules like Sales, HR, Production, SCM, and Accounts.
This material assumes familiarity with SQL Server installation (refer to the previous guide on installing SQL Server 2022 Developer Edition) and focuses on practical applications in ERP systems. Each section includes real-life examples, code, and best practices to ensure students grasp both theoretical and practical aspects.
SQL Server Data Types
SQL Server supports a variety of data types to store different kinds of data. Choosing the appropriate data type is critical for performance, storage efficiency, and data integrity in ERP systems. Below is a detailed explanation of key data types, categorized by their use, with examples from ERP modules.
1. Numeric Data Types
Numeric data types store numbers, which are common in ERP systems for identifiers, quantities, and financial calculations.
INT:
Description: Stores whole numbers from -2,147,483,648 to 2,147,483,647.
Size: 4 bytes.
Use Case: Primary keys, quantities, or counts.
ERP Example:
Sales: OrderID in an Orders table to uniquely identify sales orders.
HR: EmployeeID in an Employees table to identify employees.
Production: BatchID in a ProductionBatches table to track production runs.
SCM: ShipmentID in a Shipments table for tracking deliveries.
Accounts: TransactionID in a GeneralLedger table for financial transactions.
Code Example:
CREATE TABLE Sales.Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATETIME, TotalAmount DECIMAL(10, 2) ); INSERT INTO Sales.Orders (OrderID, CustomerID, OrderDate, TotalAmount) VALUES (1, 101, '2025-08-27', 1500.75);
DECIMAL(p, s):
Description: Stores fixed-point numbers with precision p (total digits) and scale s (decimal places).
Size: 5–17 bytes, depending on precision.
Use Case: Financial calculations, percentages, or measurements requiring precision.
ERP Example:
Sales: UnitPrice in a OrderDetails table (e.g., 999.99).
HR: Salary in an Employees table (e.g., 75000.00).
Production: MaterialCost in a ProductionOrders table.
SCM: FreightCost in a Shipments table.
Accounts: InvoiceAmount in an Invoices table.
Code Example:
CREATE TABLE Accounts.Invoices ( InvoiceID INT PRIMARY KEY, CustomerID INT, InvoiceDate DATE, InvoiceAmount DECIMAL(10, 2) ); INSERT INTO Accounts.Invoices (InvoiceID, CustomerID, InvoiceDate, InvoiceAmount) VALUES (1001, 101, '2025-08-27', 2500.50);
FLOAT:
Description: Stores floating-point numbers for approximate values, suitable for scientific or large-range calculations.
Size: 4 or 8 bytes, depending on precision.
Use Case: Scientific calculations or metrics requiring a wide range.
ERP Example:
Production: MachineEfficiency in a ProductionMetrics table (e.g., 98.7654321).
SCM: DistanceTraveled in a ShipmentTracking table for logistics calculations.
Code Example:
CREATE TABLE Production.ProductionMetrics ( MetricID INT PRIMARY KEY, MachineID INT, Efficiency FLOAT ); INSERT INTO Production.ProductionMetrics (MetricID, MachineID, Efficiency) VALUES (1, 501, 98.765);
Best Practices for Numeric Data Types:
Use INT for whole numbers like IDs or counts to save space and improve performance.
Use DECIMAL for financial data to ensure precision in calculations.
Avoid FLOAT for monetary values, as it may lead to rounding errors.
Dos:
Choose the smallest numeric type that meets the range requirement (e.g., TINYINT for values 0–255).
Use DECIMAL for currency to avoid precision loss.
Don’ts:
Don’t use FLOAT for financial calculations, as it may cause inaccuracies.
Don’t use INT for values requiring decimal places.
Pros:
INT: Fast and efficient for indexing and joins.
DECIMAL: Precise for financial calculations.
FLOAT: Supports large ranges for scientific data.
Cons:
INT: Limited to whole numbers.
DECIMAL: Larger storage footprint than INT.
FLOAT: Potential for rounding errors in precise calculations.
Alternatives:
BIGINT: For larger whole numbers (8 bytes).
SMALLINT: For smaller ranges (-32,768 to 32,767, 2 bytes).
TINYINT: For small ranges (0–255, 1 byte).
MONEY: For currency (8 bytes, but less flexible than DECIMAL).
2. String Data Types
String data types store text, which is common for names, descriptions, and codes in ERP systems.
VARCHAR(n):
Description: Stores variable-length strings up to n characters (1 byte per character).
Size: n bytes + 2 bytes overhead.
Use Case: Variable-length text like names or descriptions.
ERP Example:
Sales: ProductName in a Products table (e.g., 'Laptop Pro').
HR: JobTitle in an Employees table (e.g., 'Software Engineer').
Production: MaterialDescription in a Materials table.
SCM: SupplierName in a Suppliers table.
Accounts: AccountDescription in a ChartOfAccounts table.
Code Example:
CREATE TABLE Sales.Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), CategoryID INT ); INSERT INTO Sales.Products (ProductID, ProductName, CategoryID) VALUES (1, 'Laptop Pro', 10);
CHAR(n):
Description: Stores fixed-length strings of n characters.
Size: n bytes.
Use Case: Fixed-length codes or identifiers.
ERP Example:
Sales: ProductCode in a Products table (e.g., 'LAP123').
HR: DepartmentCode in a Departments table (e.g., 'HR001').
Accounts: AccountCode in a ChartOfAccounts table (e.g., 'GL100').
Code Example:
CREATE TABLE HR.Departments ( DepartmentID INT PRIMARY KEY, DepartmentCode CHAR(5), DepartmentName VARCHAR(50) ); INSERT INTO HR.Departments (DepartmentID, DepartmentCode, DepartmentName) VALUES (1, 'HR001', 'Human Resources');
NVARCHAR(n):
Description: Stores variable-length Unicode strings (2 bytes per character).
Size: 2n bytes + 2 bytes overhead.
Use Case: Multilingual text or global applications.
ERP Example:
Sales: CustomerName in a Customers table to support international names.
HR: EmployeeName in an Employees table for multilingual support.
SCM: SupplierName in a Suppliers table for global vendors.
Code Example:
CREATE TABLE Sales.Customers ( CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(100), Country NVARCHAR(50) ); INSERT INTO Sales.Customers (CustomerID, CustomerName, Country) VALUES (101, N'José GarcÃa', N'Spain');
Best Practices for String Data Types:
Use VARCHAR for variable-length text to save space.
Use NVARCHAR for applications requiring Unicode support (e.g., international customers).
Avoid VARCHAR(MAX) unless large text (e.g., notes or comments) is needed, as it impacts performance.
Dos:
Specify the length (n) for VARCHAR and NVARCHAR to prevent truncation.
Use NVARCHAR for global ERP systems to support multiple languages.
Don’ts:
Don’t use CHAR for variable-length data, as it wastes space.
Don’t overuse VARCHAR(MAX) or NVARCHAR(MAX), as they are less efficient for indexing.
Pros:
VARCHAR: Efficient for variable-length text.
CHAR: Fast for fixed-length codes in queries.
NVARCHAR: Essential for multilingual support.
Cons:
VARCHAR: Limited to 8,000 characters unless MAX is used.
CHAR: Wastes space for variable-length data.
NVARCHAR: Double storage compared to VARCHAR.
Alternatives:
TEXT: Legacy type for large text (deprecated; use VARCHAR(MAX) instead).
NTEXT: Legacy Unicode text (deprecated; use NVARCHAR(MAX) instead).
NCHAR: Fixed-length Unicode strings (less common).
3. Date and Time Data Types
Date and time data types store temporal data, critical for tracking events in ERP systems.
DATETIME:
Description: Stores date and time (January 1, 1753, to December 31, 9999, with 3.33ms accuracy).
Size: 8 bytes.
Use Case: Timestamps for events or transactions.
ERP Example:
Sales: OrderDate in an Orders table (e.g., '2025-08-27 14:30:00').
HR: HireDate in an Employees table.
Production: ProductionStartTime in a ProductionOrders table.
SCM: ShipmentDate in a Shipments table.
Accounts: TransactionDate in a GeneralLedger table.
Code Example:
CREATE TABLE Sales.Orders ( OrderID INT PRIMARY KEY, OrderDate DATETIME, CustomerID INT ); INSERT INTO Sales.Orders (OrderID, OrderDate, CustomerID) VALUES (1, '2025-08-27 14:30:00', 101);
DATE:
Description: Stores date only (January 1, 0001, to December 31, 9999).
Size: 3 bytes.
Use Case: Dates without time components.
ERP Example:
HR: BirthDate in an Employees table.
Accounts: InvoiceDueDate in an Invoices table.
Code Example:
CREATE TABLE HR.Employees ( EmployeeID INT PRIMARY KEY, FirstName NVARCHAR(50), BirthDate DATE ); INSERT INTO HR.Employees (EmployeeID, FirstName, BirthDate) VALUES (1, N'Alice', '1990-05-15');
TIME:
Description: Stores time only (00:00:00.0000000 to 23:59:59.9999999).
Size: 3–5 bytes, depending on precision.
Use Case: Time-specific data like shift hours.
ERP Example:
Production: ShiftStartTime in a ProductionSchedules table.
HR: PunchInTime in an Attendance table.
Code Example:
CREATE TABLE HR.Attendance ( AttendanceID INT PRIMARY KEY, EmployeeID INT, PunchInTime TIME ); INSERT INTO HR.Attendance (AttendanceID, EmployeeID, PunchInTime) VALUES (1, 1, '08:00:00');
Best Practices for Date and Time Data Types:
Use DATE for date-only data to save space.
Use DATETIME2 (more precise than DATETIME) for modern applications requiring high accuracy.
Store dates in UTC for global ERP systems to handle time zones consistently.
Dos:
Use DATE or DATETIME2 for new applications to leverage modern features.
Validate date inputs to prevent invalid data (e.g., '2025-13-01').
Don’ts:
Don’t use DATETIME for new applications; prefer DATETIME2 for better precision.
Don’t store dates as strings, as it complicates queries and sorting.
Pros:
DATETIME: Widely supported in legacy systems.
DATE: Compact for date-only storage.
TIME: Precise for time-specific data.
Cons:
DATETIME: Limited precision and range compared to DATETIME2.
DATE/TIME: Not suitable for combined date-time data.
Alternatives:
DATETIME2: More precise and flexible than DATETIME (3–8 bytes).
SMALLDATETIME: Smaller range (1900–2079, 4 bytes) for legacy systems.
DATETIMEOFFSET: Includes time zone information for global applications.
4. Binary Data Types
Binary data types store non-text data like images or files.
VARBINARY(n):
Description: Stores variable-length binary data up to n bytes.
Size: n bytes + 2 bytes overhead.
Use Case: Images, documents, or encrypted data.
ERP Example:
Sales: ProductImage in a Products table for product photos.
HR: EmployeePhoto in an Employees table.
Production: BlueprintFile in a ProductionDesigns table.
Code Example:
CREATE TABLE Sales.Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), ProductImage VARBINARY(MAX) ); -- Note: Inserting binary data typically requires application code (e.g., C#).
Best Practices for Binary Data Types:
Use VARBINARY(MAX) for large binary data like images or documents.
Store large binary data in file systems with references in the database for better performance.
Dos:
Use VARBINARY(MAX) for flexibility with large files.
Validate binary data inputs to prevent corruption.
Don’ts:
Don’t store large binary data in the database unless necessary, as it impacts performance.
Don’t use fixed-length BINARY for variable-length data.
Pros:
VARBINARY: Flexible for various binary data types.
VARBINARY(MAX): Supports large files up to 2 GB.
Cons:
Large binary data can degrade database performance.
Requires application code for insertion and retrieval.
Alternatives:
File system storage with NVARCHAR paths for large files.
FILESTREAM: Integrates file system storage with SQL Server.
5. Other Data Types
Additional data types are used for specific purposes in ERP systems.
BIT:
Description: Stores Boolean values (0, 1, or NULL).
Size: 1 byte (up to 8 BIT columns share 1 byte).
Use Case: Flags or status indicators.
ERP Example:
Sales: IsActive in a Customers table.
HR: IsFullTime in an Employees table.
Production: IsComplete in a ProductionOrders table.
Code Example:
CREATE TABLE HR.Employees ( EmployeeID INT PRIMARY KEY, FirstName NVARCHAR(50), IsFullTime BIT ); INSERT INTO HR.Employees (EmployeeID, FirstName, IsFullTime) VALUES (1, N'Alice', 1);
UNIQUEIDENTIFIER:
Description: Stores globally unique identifiers (GUIDs).
Size: 16 bytes.
Use Case: Unique keys across distributed systems.
ERP Example:
Sales: OrderGUID in an Orders table for integration with external systems.
SCM: ShipmentGUID in a Shipments table for global tracking.
Code Example:
CREATE TABLE Sales.Orders ( OrderGUID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), OrderDate DATETIME, CustomerID INT ); INSERT INTO Sales.Orders (OrderDate, CustomerID) VALUES ('2025-08-27', 101);
Best Practices for Other Data Types:
Use BIT for simple flags to save space.
Use UNIQUEIDENTIFIER for distributed systems where integer IDs may conflict.
Dos:
Use NEWID() or NEWSEQUENTIALID() for generating UNIQUEIDENTIFIER values.
Combine multiple BIT columns to optimize storage.
Don’ts:
Don’t use UNIQUEIDENTIFIER for primary keys unless necessary, as it’s larger than INT.
Don’t use BIT for non-Boolean data.
Pros:
BIT: Compact for Boolean data.
UNIQUEIDENTIFIER: Ensures uniqueness in distributed systems.
Cons:
UNIQUEIDENTIFIER: Larger storage and slower indexing compared to INT.
BIT: Limited to Boolean values.
Alternatives:
INT or BIGINT for primary keys in non-distributed systems.
TINYINT for small enumerated values instead of BIT.
Basic SELECT Queries
SELECT queries retrieve data from tables, a fundamental skill for ERP systems. Below are explanations, examples, and best practices for writing SELECT queries in the context of ERP modules.
Basic SELECT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC];Real-Life ERP Examples
Sales: Retrieve Active Orders:
Scenario: A sales manager needs a list of all orders placed in 2025 with a total amount greater than $1000.
Code:
SELECT OrderID, CustomerID, OrderDate, TotalAmount FROM Sales.Orders WHERE YEAR(OrderDate) = 2025 AND TotalAmount > 1000 ORDER BY OrderDate DESC;Explanation: Retrieves orders with specific criteria, sorted by date in descending order.
HR: List Full-Time Employees:
Scenario: HR needs a list of full-time employees hired after January 1, 2023.
Code:
SELECT EmployeeID, FirstName, LastName, HireDate FROM HR.Employees WHERE IsFullTime = 1 AND HireDate > '2023-01-01' ORDER BY LastName ASC;
Production: Track Completed Orders:
Scenario: Production manager wants to see completed production orders for a specific product.
Code:
SELECT ProductionOrderID, ProductID, ProductionStartTime, IsComplete FROM Production.ProductionOrders WHERE IsComplete = 1 AND ProductID = 1 ORDER BY ProductionStartTime;
SCM: Monitor Shipments:
Scenario: SCM team needs details of shipments scheduled for August 2025.
Code:
SELECT ShipmentID, SupplierID, ShipmentDate, FreightCost FROM SCM.Shipments WHERE ShipmentDate BETWEEN '2025-08-01' AND '2025-08-31' ORDER BY FreightCost DESC;
Accounts: Retrieve Overdue Invoices:
Scenario: Accounts department needs a list of unpaid invoices due before August 27, 2025.
Code:
SELECT InvoiceID, CustomerID, InvoiceDate, InvoiceAmount FROM Accounts.Invoices WHERE InvoiceDueDate < '2025-08-27' AND IsPaid = 0 ORDER BY InvoiceAmount DESC;
Advanced SELECT Features
Aggregations:
Scenario: Calculate total sales by customer in 2025.
Code:
SELECT CustomerID, SUM(TotalAmount) AS TotalSales FROM Sales.Orders WHERE YEAR(OrderDate) = 2025 GROUP BY CustomerID ORDER BY TotalSales DESC;
Joins:
Scenario: Join Customers and Orders to get customer names with their orders.
Code:
SELECT c.CustomerID, c.CustomerName, o.OrderID, o.OrderDate FROM Sales.Customers c INNER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID WHERE YEAR(o.OrderDate) = 2025;
Exception Handling with TRY_CAST:
Scenario: Convert a string column to a numeric value, handling errors.
Code:
SELECT ProductID, ProductName, TRY_CAST(PriceString AS DECIMAL(10, 2)) AS Price FROM Sales.Products WHERE TRY_CAST(PriceString AS DECIMAL(10, 2)) IS NOT NULL;
Best Practices for SELECT Queries:
Select only the columns needed to reduce resource usage.
Use meaningful aliases (e.g., c for Customers) for readability.
Index columns used in WHERE, JOIN, and ORDER BY clauses for performance.
Use TRY_CAST or TRY_CONVERT to handle data type mismatches gracefully.
Dos:
Write clear, formatted queries for maintainability.
Use table aliases in joins to simplify syntax.
Validate query results to ensure correctness.
Don’ts:
Don’t use SELECT * in production, as it retrieves unnecessary data.
Don’t ignore indexing, as it impacts query performance.
Don’t write complex queries without testing on small datasets.
Pros:
SELECT queries are versatile for reporting and analysis.
Joins enable combining data from multiple ERP modules.
Aggregations support business intelligence tasks.
Cons:
Poorly written queries can degrade performance.
Complex joins require careful indexing to avoid slowdowns.
Alternatives:
Views: Create reusable queries for common reports (e.g., CREATE VIEW Sales.ActiveOrders AS ...).
Stored Procedures: Encapsulate complex logic for reuse.
Power BI: For advanced reporting outside SQL Server.
Business Usage in ERP Systems
SQL Server data types and SELECT queries are critical in ERP systems for managing data across modules:
Sales:
Data Types: INT for OrderID, DECIMAL for TotalAmount, NVARCHAR for CustomerName.
SELECT Queries: Retrieve sales reports, customer orders, or top-selling products.
Example: Identify top customers by sales volume for marketing campaigns.
HR:
Data Types: INT for EmployeeID, NVARCHAR for FirstName, DATE for HireDate, BIT for IsFullTime.
SELECT Queries: List employees by department, calculate payroll, or track attendance.
Example: Generate a report of employees eligible for promotions based on hire date.
Production:
Data Types: INT for ProductionOrderID, FLOAT for Efficiency, DATETIME for ProductionStartTime.
SELECT Queries: Monitor production status, track material usage, or analyze machine performance.
Example: Identify bottlenecks in production by querying completion times.
SCM:
Data Types: INT for ShipmentID, DECIMAL for FreightCost, UNIQUEIDENTIFIER for global tracking.
SELECT Queries: Track shipments, calculate logistics costs, or monitor supplier performance.
Example: Optimize delivery routes by analyzing shipment data.
Accounts:
Data Types: DECIMAL for InvoiceAmount, DATE for InvoiceDueDate, CHAR for AccountCode.
SELECT Queries: Generate financial reports, track overdue invoices, or reconcile accounts.
Example: Produce a balance sheet by aggregating transactions.
Assignment for Practical Lab
Objective: Apply SQL Server data types and SELECT queries in an ERP context.
Tasks:
Database Setup:
Create a database named ERPDemo.
Create tables for each ERP module:
Sales: Orders (OrderID INT, CustomerID INT, OrderDate DATETIME, TotalAmount DECIMAL(10, 2)).
HR: Employees (EmployeeID INT, FirstName NVARCHAR(50), LastName NVARCHAR(50), HireDate DATE, IsFullTime BIT).
Production: ProductionOrders (ProductionOrderID INT, ProductID INT, ProductionStartTime DATETIME, IsComplete BIT).
SCM: Shipments (ShipmentID INT, SupplierID INT, ShipmentDate DATE, FreightCost DECIMAL(10, 2)).
Accounts: Invoices (InvoiceID INT, CustomerID INT, InvoiceDate DATE, InvoiceAmount DECIMAL(10, 2), IsPaid BIT).
Insert at least five records into each table.
SELECT Queries:
Write a query to retrieve all orders from 2025 with a total amount greater than $500.
Write a query to list full-time employees hired after January 1, 2024.
Write a query to find completed production orders for a specific product.
Write a query to calculate total freight costs for shipments in August 2025.
Write a query to identify unpaid invoices due before August 27, 2025.
Advanced Queries:
Write an aggregation query to calculate total sales by customer.
Write a join query to combine Customers and Orders to show customer names with their orders.
Use TRY_CAST to handle a string-to-numeric conversion in a query.
Documentation:
Document the database schema, including data types and their rationale.
Save all T-SQL scripts in separate .sql files.
Write a report (500–1000 words) explaining your queries, data types chosen, and any challenges faced.
No comments:
Post a Comment
Thanks for your valuable comment...........
Md. Mominul Islam