Using Tables and Data Types in MSSQL Server

When it comes to working with MSSQL Server, tables and data types form the backbone of your database design and operations. They are essential elements that allow you to store and manipulate data effectively. In this article, we will take a detailed look at how to create and manage tables in MSSQL Server, along with an overview of the various data types available.

Creating Tables in MSSQL Server

Tables in MSSQL Server hold the structured data in rows and columns. To create a table, you use the CREATE TABLE statement, specifying the name of the table and defining its columns, along with their respective data types. Here’s a simple example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    HireDate DATETIME NOT NULL,
    Salary DECIMAL(10, 2) CHECK (Salary >= 0)
);

Explaining the Table Structure

EmployeeID INT PRIMARY KEY: Here, we’re defining the EmployeeID column as an integer that uniquely identifies each employee. Adding PRIMARY KEY indicates that this column cannot contain NULL values, and it must have unique values.
FirstName NVARCHAR(50) NOT NULL: The FirstName column is defined with a data type of NVARCHAR, which allows for variable-length Unicode string data (good for names). The (50) indicates it can hold up to 50 characters. The NOT NULL constraint signifies that this column cannot be empty.
HireDate DATETIME NOT NULL: The HireDate column stores the date and time an employee was hired, and it also cannot be NULL.
Salary DECIMAL(10, 2) CHECK (Salary >= 0): Salary is a decimal number that can hold up to 10 digits, with 2 digits after the decimal point. The CHECK constraint ensures that a negative salary is not allowed.

Altering Tables

As your application evolves, you may need to modify your tables. You can use the ALTER TABLE statement for this purpose. For example, if you want to add a new column to the Employees table, say Department, you would do it like this:

ALTER TABLE Employees
ADD Department NVARCHAR(50);

If you decide later to change the data type of the Salary column, you can execute:

ALTER TABLE Employees
ALTER COLUMN Salary FLOAT;

Dropping a Column

In some cases, it might be necessary to remove a column. This can be done using the following command:

ALTER TABLE Employees
DROP COLUMN Department;

Understanding Data Types

Choosing the right data type is crucial because it affects storage, performance, and data integrity. MSSQL Server provides a plethora of data types. Here’s a breakdown of the most commonly used categories:

1. Numeric Data Types

INT: A 4-byte integer.
SMALLINT: A 2-byte integer.
TINYINT: A 1-byte integer.
BIGINT: An 8-byte integer.
FLOAT: A floating-point number.
DECIMAL(p, s): A decimal number where p is the precision (total number of digits) and s is the scale (number of digits after the decimal point).

2. Date and Time Data Types

DATETIME: Stores date and time; useful for timestamps.
DATE: Stores the date only.
TIME: Stores the time only.
DATETIME2: Offers higher precision than DATETIME.

3. String Data Types

CHAR(n): Fixed-length character string. If you store a string shorter than n, it will be padded with spaces.
VARCHAR(n): Variable-length character string. Efficient for strings that vary substantially in length.
NCHAR(n): Fixed-length Unicode character string.
NVARCHAR(n): Variable-length Unicode character string. Ideal for storing text that may include characters from multiple languages.

4. Binary Data Types

BINARY(n): Fixed-length binary data.
VARBINARY(n): Variable-length binary data.
IMAGE: Used for large binary objects, such as images and other files.

5. Others

BIT: A type that can hold a value of 0, 1, or NULL.
XML: For storing XML data.
JSON: Although not a separate data type, you can store JSON in NVARCHAR or VARCHAR types and leverage built-in functions.

Best Practices for Table Design

Normalize Your Data: Ensure that your tables are well-structured to reduce data redundancy. Avoid storing the same information in multiple places.
Use Appropriate Data Types: Choosing the right data type not only saves storage space but also enhances performance.
Define Primary and Foreign Keys: Use primary keys for uniqueness and foreign keys to maintain referential integrity between tables.
Indexing: Consider indexing frequently queried columns to speed up data retrieval, but also be mindful of the trade-offs with write performance.
Use Constraints Wisely: Implement constraints like UNIQUE, CHECK, and NOT NULL to enforce data integrity.

Managing Tables with T-SQL

Using Transact-SQL (T-SQL), MSSQL Server’s iteration of SQL, you can manage tables through various commands effectively:

Inserting Data:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Salary)
VALUES (1, 'John', 'Doe', '2023-01-01', 50000.00);

Updating Data:

UPDATE Employees
SET Salary = Salary * 1.05  -- Giving a raise of 5%
WHERE LastName = 'Doe';

Deleting Data:

DELETE FROM Employees
WHERE EmployeeID = 1;

Conclusion

Understanding how to create and manage tables, along with selecting the appropriate data types in MSSQL Server, is essential for effective database design and operations. By leveraging the capabilities of MSSQL and adhering to best practices, you can ensure a well-structured and efficient database that serves your application’s needs. Happy querying!

Databases - MSSQL Server