Lesson 9	Creating SQL-Server Tables
Objective	Identify key considerations for creating a table

Creating Tables (Identify key considerations)

What are the primary considerations to take into account when creating a table?
Before you create a table, you must think about the data that it will store. Then you must consider what datatype should be used for each column of data. For example, suppose you want to store an employee's salary that never exceeds $200,000 per year.

When designing a database table, it's crucial to select the appropriate "data types" for each column to ensure 1) efficiency, 2)accuracy, and 3) proper constraints.
Example: Storing an Employee's Salary
If we need to store an employee's salary that never exceeds $200,000 per year, we must consider:

Precision and Scale: We need to handle decimal values (e.g., $75,500.50).
Range of Values: The highest possible salary is $200,000.
Performance Optimization: Use a data type that is sufficient without being excessive.

Suitable Data Type Choices:

Database	Suggested Data Type	Explanation
MySQL / PostgreSQL	`DECIMAL(9,2)`	Allows values up to 999,999.99, ensuring precision for salary calculations.
SQL Server	`MONEY` or `DECIMAL(9,2)`	`MONEY` is designed for currency storage but may have rounding issues, `DECIMAL(9,2)` ensures accuracy.
Oracle	`NUMBER(9,2)`	Handles up to 999,999.99, sufficient for the salary range.
SQLite	`REAL` or `NUMERIC(9,2)`	`REAL` stores floating-point numbers; `NUMERIC(9,2)` ensures fixed precision.

Example Table Definition:
For MySQL/PostgreSQL

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Salary DECIMAL(9,2) CHECK (Salary <= 200000)
);

For Oracle

CREATE TABLE Employees (
    EmpID NUMBER PRIMARY KEY,
    Name VARCHAR2(100) NOT NULL,
    Salary NUMBER(9,2) CHECK (Salary <= 200000)
);

Why Use `DECIMAL(9,2)` Instead of `FLOAT`?

DECIMAL ensures accurate calculations for monetary values.
FLOAT and REAL introduce rounding errors, which are problematic for financial data.

At first glance, you'd probably think that you should use the money datatype. However, as we saw in a previous module, the money datatype takes 8 bytes of storage, and it has a range of

-922,337,203,685,477.5808 to 922,337,203,685,477.5807.

Unless you are storing national deficit numbers, you really don't need such a large range, nor will you want to consume that much storage space.The smallmoney datatype, on the other hand, takes only 4 bytes of storage. It has a range of

-214,748.3648 to 214,748.3647,

which is well within the range of values that you expect to store. That, in combination with the smaller storage requirement, makes it a much more sensible choice.

Composite primary keys

A primary key comprising more than one column is called a composite primary key. If you want to create a table that uses a composite primary key, you must use the CONSTRAINT clause to specify all the columns that it will comprise. To use the CONSTRAINT clause, use the CONSTRAINT keyword after the last column definition. You must give it a unique name within the table, like this:

CONSTRAINT pkey PRIMARY KEY (Column1, Column2, Column3)

You will see exactly how in the next lesson. In the next lesson, we'll dissect the Transact-SQL statement used to create a table.