Creating databases, data types, and constraints

CREATE DATABASE, MDF/LDF, autogrowth, CHAR/VARCHAR/NCHAR/NVARCHAR, uniqueidentifier, and CHECK constraints.

Beginner 42 min read SQLSQL ServerCREATE DATABASE

Lesson 5 / 7 0%

Creating databases, data types, and constraints

Level: beginner · Reading time: about 35 to 45 minutes

Topics: CREATE DATABASE, SSMS, T-SQL, MDF, LDF, autogrowth, data types, CHAR, VARCHAR, NCHAR, NVARCHAR, uniqueidentifier, CHECK

What will we learn?

We build our first real database — graphically and with SQL.

database via SSMS and T-SQL
naming and owner
MDF, LDF, and autogrowth
important data types
CHAR, VARCHAR, NCHAR, NVARCHAR
GUID and uniqueidentifier
CHECK constraints

1. Two ways to create a database

In SQL Server you usually create a database in two ways:

Graphical — SSMS and Object Explorer
Command — T-SQL with CREATE DATABASE

2. Method 1 — Object Explorer

After connecting in SSMS, in Object Explorer:

text

Databases
↓
Right Click
↓
New Database

3. The New Database window

The key field is Database name — e.g. TestDB, UniversityDB, or StoreDB.

4. Naming databases

SQL Server does not require PascalCase; that is a team convention. In this course we may use UniversityDB, StoreDB, LibraryDB.

5. What is PascalCase?

Capitalize the first letter of each word — e.g. StudentManagementSystem, OnlineStoreDB.

6. No spaces in database names

Prefer UniversityDB over University Database so queries need fewer brackets.

7. Avoid special characters

ShopDB is fine; Shop@DB or Test#Database are poor professional choices.

8. Avoid reserved SQL words

SELECT, TABLE, DATABASE, ORDER, USER are reserved. Use Orders or CustomerOrders instead of Order alone.

9. Suggested naming rules for this course

Meaningful English names
No spaces or odd characters
Avoid reserved words
PascalCase

10. Database owner

Owner sets which login owns the database. In learning environments we usually leave it unchanged for now.

11. Database files

Each database has at least a data file and a transaction log file, typically:

text

TestDB.mdf
TestDB_log.ldf

12. MDF file

.mdf is the primary data file holding tables, indexes, and other database objects.

MDF = primary database data file

13. LDF file

.ldf is the transaction log file for transactional operations and recovery.

14. Transaction log in simple terms

If a money transfer stops halfway, SQL Server must know whether the transaction completed. The log supports recovery — it is not just an error file.

15. MDF vs LDF

MDF → data and objects · LDF → transaction log

16. NDF secondary data files

Large databases may add secondary data files with .ndf.

Summary: MDF (primary) · NDF (secondary) · LDF (log)

17. Initial size

Starting file size — e.g. data file 64 MB and log file 64 MB.

18. Autogrowth

When the database fills up, SQL Server can grow files automatically — autogrowth.

19. Configuring autogrowth

You can set growth increments, for example 64 MB at a time.

20. Maximum file size

Unlimited does not mean infinite disk space; growth still depends on disk, edition, and system limits.

21. CREATE DATABASE command

In SSMS → New Query:

sql

CREATE DATABASE TestDB;

22. Running the query

Click Execute. If the database does not appear: Databases → Right Click → Refresh.

sql

CREATE DATABASE UniversityDB;

23. Graphical or command?

Graphical is easy to start with. Commands are repeatable and scriptable — we will use T-SQL more over time.

24. What is a data type?

Each column has a type: Age is numeric, FirstName is text, BirthDate is a date. That is the data type.

25. Numeric data types

tinyint, smallint, int, bigint, decimal, numeric, float, real — age often uses int.

26. INT

Age INT — whole numbers like 18, 25, 40.

27. SMALLINT

Integer with a smaller range than INT — useful when values stay small.

28. FLOAT

Approximate floating-point numbers — decimal is usually better for money.

29. Money types

money and smallmoney exist; many systems prefer decimal(18,2) for precise amounts.

30. Text data types

CHAR, VARCHAR, NCHAR, NVARCHAR — fixed length vs variable length.

31. CHAR

Code CHAR(10) — fixed length; shorter values are padded with spaces.

32. CHAR example

Name CHAR(10) storing Ali uses all 10 characters. Good for M/F flags or fixed codes.

33. VARCHAR

Name VARCHAR(100) — variable length; Ali does not consume all 100 characters.

34. NCHAR

Unicode text with fixed length.

35. Why Unicode matters

For Persian text (Ali, Mohammad, university) NCHAR and NVARCHAR are common choices.

36. NVARCHAR

Unicode and variable length — names, addresses, titles.

FirstName NVARCHAR(50)

37. NVARCHAR example

FirstName NVARCHAR(10) with «Ali» — unlike nchar, it is not space-padded to fixed length.

38. Text type comparison

Type	Length	Unicode
CHAR	Fixed	Usually non-Unicode
VARCHAR	Variable	Usually non-Unicode
NCHAR	Fixed	Unicode
NVARCHAR	Variable	Unicode

For Persian in this course, NVARCHAR is the simple default.

39. Practical choice for Persian names

FirstName NVARCHAR(50) and LastName NVARCHAR(50)

40. GUID

Globally Unique Identifier — in SQL Server: UNIQUEIDENTIFIER

41. GUID example

Example: 6F9619FF-8B86-D011-B42D-00C04FC964FF

UserId UNIQUEIDENTIFIER

42. NEWID()

Function to generate a GUID:

sql

SELECT NEWID();

43. Sample Student table design

StudentId INT, FirstName NVARCHAR(50), LastName NVARCHAR(50), Age INT

44. Data type alone is not enough

INT age allows -150 or 500000 — logically invalid. We need a constraint.

45. What is a constraint?

A rule on data — no negative age, no duplicate codes, values within a range.

46. CHECK constraint

CHECK (Age BETWEEN 1 AND 80)

47. CHECK on age

sql

CREATE TABLE Students
(
    StudentId INT,
    FirstName NVARCHAR(50),
    Age INT CHECK (Age BETWEEN 1 AND 80)
);

Age = 25 accepted · Age = 100 rejected

48. BETWEEN

Age BETWEEN 1 AND 80 means Age >= 1 AND Age <= 80 — both ends inclusive.

49. Why CHECK matters

If only the front-end enforces rules, another app can insert Age = -20. CHECK protects at the database too.

50. CHECK on score

CHECK (Score BETWEEN 0 AND 20)

51. CHECK on salary

CHECK (Salary >= 0)

52. CHECK on quantity

CHECK (Quantity >= 1)

53. A fuller first table

sql

CREATE TABLE Students
(
    StudentId INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT CHECK (Age BETWEEN 1 AND 80)
);

Later we will cover PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and DEFAULT in full.

54. Workflow so far

text

Problem analysis
↓
ER diagram
↓
Entity → Attribute
↓
Database → Table → Column
↓
Data type → Constraint

Session summary

Database: graphical SSMS or CREATE DATABASE. MDF = data, LDF = log. Types: INT, CHAR/VARCHAR, NCHAR/NVARCHAR, UNIQUEIDENTIFIER. CHECK e.g. CHECK (Age BETWEEN 1 AND 80).

Session 5 exercises

Work these after the reading.