# Column

> Source: Courseiva IT Certification Glossary — https://courseiva.com/glossary/column

## Quick definition

A column is a vertical collection of cells in a database table, all holding the same kind of data. For example, in a table of employees, a column called "LastName" would contain only last names. Each row in the table has one value for that column. Columns define the structure of the data and tell you what kind of information is stored.

## Simple meaning

Think of a spreadsheet where you have a list of people. You might have a column for "First Name," another for "Last Name," and another for "Email Address." Every person in the list has a spot in each of those columns. A column in a database works the same way. It is a vertical stack of information where every entry is the same type, all dates, all text, all numbers. 

 In a database table, columns are sometimes called "fields." They are the building blocks of your data structure. When you create a table, you decide what columns you need. For example, if you build a table for a library, you might have columns for "BookTitle," "Author," "ISBN," and "PublishYear." Each column has a data type, like text or number, which controls what kind of values you can put in it. 

 Imagine a filing cabinet. Each drawer is a table, and each folder inside the drawer is a row. The labels on the folder tabs are the columns. They tell you what information should be in that folder. If the label says "CustomerID," every folder must have a customer ID written there. This consistency is what makes databases powerful. You can quickly find everything with a certain value in a column, like all books published in 2020, because every row has that column filled in a predictable way.

## Technical definition

In relational database management systems (RDBMS), a column, also formally referred to as an attribute or field, is a structural component of a table. It represents a single data element that is consistent across all rows (also known as tuples) in the table. Each column is defined by a name and a data type, which constrains the values that can be stored in it. Common data types include INT, VARCHAR, DATE, DECIMAL, and BOOLEAN, among others. 

 When creating a table with SQL, columns are defined in the CREATE TABLE statement. For example:

 CREATE TABLE Customers (
 CustomerID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 Email VARCHAR(100) UNIQUE,
 SignUpDate DATE
 );

 In this statement, CustomerID, FirstName, LastName, Email, and SignUpDate are columns. The data type after each column name enforces the kind of data that can be entered. VARCHAR(50) means the column can hold up to 50 characters of text. INT holds whole numbers. DATE holds calendar dates. 

 Columns also have constraints that enforce data integrity. Common constraints include NOT NULL (the column cannot be empty), UNIQUE (all values in the column must be different), PRIMARY KEY (uniquely identifies each row), and FOREIGN KEY (links to a column in another table). These constraints are critical for maintaining accurate and reliable data across related tables. 

 In a physical sense, columns are stored in data pages on disk, with values stored in a specific order depending on the row layout. When an index is created on a column, the database engine builds a separate structure (like a B-tree) to speed up searches on that column. Composite indexes involve multiple columns, and their order matters for query performance. 

 In the context of the DP-900 exam (Microsoft Azure Data Fundamentals), candidates need to understand how columns relate to tables in Azure SQL Database, Azure Cosmos DB, and Azure Synapse Analytics. For example, in a star schema data model, columns in fact tables typically contain numeric measures, while columns in dimension tables contain descriptive attributes. Understanding column-level data types and constraints is foundational for querying and designing databases on Azure.

## Real-life example

Imagine you are organizing a large family reunion. You create a signup sheet on a clipboard. The sheet has a table drawn on it. At the top of the sheet, you write column headers: "First Name," "Last Name," "Phone Number," "Number of Guests," and "Potluck Dish." Each person who arrives fills in one row under those column headers. 

 Now, every row has a value for "First Name", John, Maria, Ahmed, etc. If you later want to call everyone whose "Potluck Dish" is "lasagna," you can easily scan down that column. The column makes it simple to find specific information because all entries in that column are the same type of data. 

 In this analogy, the clipboard is the database table. The column headers are the column names. The data types are implicit, you know "Number of Guests" should be a number, not text. If someone wrote "five" instead of "5," it would be inconsistent and hard to sort. That’s why databases enforce data types. 

 The real-life sheet works fine for a small group, but for a database with millions of customers, the column structure is essential. It allows the database engine to index the "Phone Number" column and find a specific number in milliseconds. It also prevents someone from accidentally putting a date in the "Potluck Dish" column because the data type would reject it. So a column is not just a label, it is a rule that keeps data clean and usable.

## Why it matters

In IT, data is the most valuable asset. Columns are the fundamental units that give data its meaning and structure. Without columns, data would be a chaotic pile of values with no way to know what each value represents. When you query a database, you almost always reference columns by name. For example, SELECT FirstName, LastName FROM Employees tells the database exactly which columns to retrieve. 

 Columns matter because they enforce data quality. A well-designed column with the correct data type prevents errors like storing letters in a numeric field. Constraints like NOT NULL ensure that critical information is never missing. Indexing on columns makes queries fast. If you frequently search for customers by email, adding an index on the Email column can turn a slow scan into an instant lookup. 

 In real-world IT operations, database administrators and developers spend a lot of time designing columns. They choose data types carefully to balance storage size and performance. For example, using INT instead of BIGINT for a column that will never need huge numbers saves disk space and speeds up joins. They also consider whether to allow NULLs, which can affect query logic and indexing. 

 For cloud databases like Azure SQL Database, understanding columns is also important for cost and performance. Columnstore indexes, for example, store data column-wise rather than row-wise, which can greatly improve analytic query performance by scanning only the columns needed. This is a key concept for the DP-900 exam, especially when discussing data warehousing in Azure Synapse Analytics.

## Why it matters in exams

For the DP-900 exam, columns are a core concept. The exam objectives explicitly cover describing how data is structured in relational databases, which includes defining tables, rows, and columns. You will encounter questions that ask you to identify the correct data type for a given column based on a business scenario. For example, if a column stores a person’s age, the correct data type is INT, not VARCHAR. 

 You will also see questions about constraints like PRIMARY KEY and FOREIGN KEY. These constraints are applied at the column level. A PRIMARY KEY constraint on a column ensures that every value in that column is unique and not null. The exam may present a scenario where you need to choose which column is best suited to be a primary key, such as CustomerID rather than FirstName, because FirstName is not unique. 

 Another common exam topic is the impact of column data types on storage and performance. For example, choosing CHAR(10) vs VARCHAR(10), CHAR uses fixed storage, while VARCHAR uses variable storage. The exam may test your understanding of when each is appropriate. There may also be questions about nullability and how it affects queries, such as using IS NULL or IS NOT NULL in WHERE clauses. 

 Data modeling questions on the DP-900 often involve designing tables with appropriate columns. You might be given a list of attributes (like product name, price, category) and asked to identify which columns belong in a product table. You may need to distinguish between columns that store measures (numeric values, often in fact tables) and columns that store attributes (descriptive data, often in dimension tables). 

 The exam also touches on Azure-specific column features, such as columnstore indexes in Azure Synapse Analytics and the ability to add columns dynamically in Azure Cosmos DB (which is schema-agnostic). Understanding the difference between a fixed schema (SQL) and a flexible schema (NoSQL) is important. Even in NoSQL, the concept of a column exists, though it is often used differently. 

 Expect questions that require reading a CREATE TABLE statement and identifying errors, such as missing data types or invalid constraints. For instance, you might see a statement that tries to add a PRIMARY KEY to a column that allows NULLs, which will fail. Being comfortable with SQL syntax for column definitions is a strong advantage for the DP-900 exam.

## How it appears in exam questions

In the DP-900 exam, column-related questions appear in several formats. One common type is scenario-based, where you are given a business requirement and asked to choose the correct column definition. For example: "A table will store employee salaries. Which data type should be used for the Salary column?" The correct answer is DECIMAL or MONEY, depending on the options. 

 Another pattern involves identifying constraints. You might see: "You need to ensure that the Email column contains no duplicate values. Which constraint should you add?" The answer is UNIQUE. Or: "You need to ensure that every row has a value in the CustomerID column. Which constraint should you use?" The answer is NOT NULL, often combined with PRIMARY KEY. 

 Some questions ask about indexing strategies. For example: "A query frequently filters on the LastName column. What should you create to improve performance?" The answer is a nonclustered index on that column. You might also be asked about the difference between clustered and nonclustered indexes, and how they relate to the physical ordering of column data. 

 There are also true/false or multiple-select questions about column properties. For instance: "Which of the following are valid column data types in SQL Server?" Options might include INT, VARCHAR, BOOLEAN, and BLOB. You need to know that BOOLEAN is not a native SQL Server data type (BIT is used instead). 

 Data modeling questions often show a partial table design. You might be asked to add a column that stores a foreign key to another table. For example: "You have an Orders table and a Customers table. Which column should you add to the Orders table to link them?" The answer is CustomerID with a FOREIGN KEY constraint referencing the Customers table. 

 Finally, there are conceptual questions about column-oriented storage. The exam may ask: "In Azure Synapse Analytics, which type of index stores data column by column rather than row by row?" The answer is columnstore index. Understanding when to use columnstore vs rowstore is part of the exam.

## Example scenario

You are a database designer for a small online bookstore. You need to create a table called "Books" that stores information about each book for sale. You want to include the title, author, price, publication year, and ISBN number. 

 First, you plan the columns. The Title column will hold text, so you choose VARCHAR(200), assuming no book title is longer than 200 characters. The Author column also needs text, so VARCHAR(100). The Price column needs to store decimal numbers like 19.99, so you choose DECIMAL(5,2), five digits total with two after the decimal point. The PublicationYear column could be stored as SMALLINT or DATE. Since you only need the year, SMALLINT is more efficient. The ISBN column is a fixed-length 13-character string, so you use CHAR(13). 

 Now you consider constraints. The ISBN should be unique and not null because every book must have a different ISBN. So you add a UNIQUE constraint and a NOT NULL constraint. You also decide that the Title and Author columns must have values (NOT NULL), but the price is optional for books not yet priced (allow NULL). 

 When you create the table, you write SQL like this: 

 CREATE TABLE Books (
 BookID INT PRIMARY KEY IDENTITY(1,1),
 Title VARCHAR(200) NOT NULL,
 Author VARCHAR(100) NOT NULL,
 Price DECIMAL(5,2) NULL,
 PublicationYear SMALLINT NULL,
 ISBN CHAR(13) NOT NULL UNIQUE
 );

 In this scenario, the columns define exactly what data can be stored. If someone tries to insert a row with a price of "cheap," the database will reject it because DECIMAL expects a number. This column structure ensures that the bookstore’s data remains clean and reliable for queries like "Find all books published after 2020 that cost less than $30."

## Common mistakes

- **Mistake:** Using VARCHAR for a column that stores whole numbers, like Age or ZipCode.
  - Why it is wrong: VARCHAR stores text, so numeric operations like sorting by age or comparing zip codes as numbers won't work correctly. For example, '100' would come before '2' in alphabetical order.
  - Fix: Use INT for whole numbers and CHAR(5) for fixed-length codes like US zip codes.
- **Mistake:** Making a PRIMARY KEY column nullable or allowing duplicate values.
  - Why it is wrong: A primary key must uniquely identify each row. If it allows NULLs or duplicates, the database cannot ensure uniqueness, and the constraint will fail upon table creation.
  - Fix: Always define a PRIMARY KEY column as NOT NULL and with a UNIQUE constraint (which is automatically enforced by PRIMARY KEY).
- **Mistake:** Choosing CHAR instead of VARCHAR for variable-length text, wasting storage.
  - Why it is wrong: CHAR(100) always reserves 100 characters of storage, even if you only store 'yes'. VARCHAR(100) uses only the space needed plus a small overhead.
  - Fix: Use CHAR only when the data is always the same length, like a state abbreviation of exactly 2 characters. Otherwise, use VARCHAR.
- **Mistake:** Not considering the order of columns in a composite index.
  - Why it is wrong: If you create an index on (LastName, FirstName), queries filtering only by FirstName will not be able to use the index efficiently, leading to slower performance.
  - Fix: Put the most selective or most frequently filtered column first in a composite index.
- **Mistake:** Adding a FOREIGN KEY column but forgetting to make it the same data type as the referenced column.
  - Why it is wrong: The foreign key and the primary key it references must have exactly the same data type. If one is INT and the other is BIGINT, the constraint will fail.
  - Fix: Always match the data types exactly when defining foreign key relationships.

## Exam trap

{"trap":"A question shows a table with a column defined as VARCHAR(10) and asks if it can store 'Hello World' (11 characters).","why_learners_choose_it":"They might think VARCHAR means it can hold any length, forgetting that the number in parentheses specifies the maximum.","how_to_avoid_it":"Always read the number in parentheses. VARCHAR(n) can hold at most n characters. 'Hello World' has 11 characters, so it would be truncated or cause an error depending on the database settings."}

## Commonly confused with

- **Column vs Row:** A row is a complete record that contains one value for every column in the table. While a column is a vertical set of values of the same type, a row is a horizontal set of values that together describe a single entity, like one customer or one product. (Example: In a table of students, the column "Grade" holds all the grade values. A row holds one student's ID, name, and grade together.)
- **Column vs Field:** In many contexts, "column" and "field" are used interchangeably. However, "field" sometimes refers to a single cell at the intersection of a row and a column, while "column" refers to the entire vertical set. In database design, "column" is the standard term. (Example: In a form, a field is a blank where you enter your name. In the database, the "Name" column contains all the names entered.)
- **Column vs Attribute:** Attribute is a more formal term often used in database theory and entity-relationship modeling. It means the same as column but is used when talking about entities and their properties. In practice, the terms are synonymous. (Example: In a data model, a Customer entity has the attributes CustomerID, Name, and Email. In the physical table, these become columns.)
- **Column vs Cell:** A cell is the single value at the intersection of a specific row and a specific column. A column contains many cells, one for each row. A cell is the smallest unit of data in a table. (Example: In a table, the column "Price" has a cell with value 19.99 in the row for a specific product.)

## Step-by-step breakdown

1. **Define the column name and data type** — You choose a descriptive name, like 'EmailAddress', and a data type such as VARCHAR(255). The data type restricts what values are allowed. This step is critical because it determines storage, performance, and data integrity.
2. **Specify constraints** — Add constraints like NOT NULL, UNIQUE, or PRIMARY KEY. These rules enforce business logic at the database level. For example, setting an Email column to UNIQUE prevents two customers from registering the same email address.
3. **Create the table in SQL** — Use the CREATE TABLE statement with your column definitions. The database engine parses this and creates the internal structure. Any syntax errors will be reported at this stage, so careful planning is needed.
4. **Insert data into the column** — When you insert a row, you provide a value for the column (or rely on a default). The database checks the value against the data type and constraints. If the value is invalid, the insert is rejected.
5. **Query the column** — You can retrieve data from a column using SELECT statements. For example, SELECT Email FROM Customers returns all values in the Email column. You can filter, sort, and join using columns.
6. **Optimize with indexes** — If a column is frequently used in WHERE clauses, you can create an index on it. The index helps the database find rows faster by creating an ordered copy of the column values with pointers to the full rows.
7. **Modify the column later** — Using ALTER TABLE, you can change a column's data type, add a constraint, drop a constraint, or rename it. However, changes can be expensive on large tables and might fail if existing data violates new rules.

## Practical mini-lesson

Understanding columns is the foundation of working with relational databases. In practice, you will not just create columns, you will design them with careful thought about storage, performance, and integrity. For example, if you are building a table for an e-commerce site, you need to decide how to store prices. Using DECIMAL(10,2) is typical, but for very high-precision financial calculations, you might use a different data type like MONEY in SQL Server. 

 When you add columns to an existing table, you need to consider whether the column can be NULL. If you add a NOT NULL column to a table that already has rows, you must provide a default value or the operation will fail. This is a common pitfall for developers who are altering production tables. 

 Indexing is another area where columns are critical. A clustered index determines the physical order of rows in a table. By default, the primary key column is the clustered index. But in some cases, you might want a different column, like a date column used in range queries, to be the clustered index for better performance. 

 In data warehousing, columnstore indexes revolutionize query speed. Instead of storing all columns for a row together (rowstore), they store each column separately. This allows compression and fast scanning. For example, if your query only sums sales amounts for a year, the columnstore index reads only the SalesAmount column, not the entire row. This is a key concept for Azure Synapse Analytics and is tested on DP-900. 

 What can go wrong? Poor column design leads to data anomalies. For instance, storing multiple values in a single column (like listing multiple phone numbers separated by commas) violates first normal form and makes queries messy. Also, choosing a data type that is too small can cause truncation. A classic example is storing a large text in a VARCHAR(50) column, the extra characters are silently cut off in some databases. 

 Professionals also need to understand collation, which affects how text columns are sorted and compared. For case-sensitive searches, you may need a binary collation. This is relevant when designing columns that store usernames or codes. Getting collation wrong can cause duplicate entries that your UNIQUE constraint misses, confusing users.

## Memory tip

Think of a column as a "category" of information, all the values in a column are the same type, like a single vertical column of jelly beans, each bean being the same flavor.

## FAQ

**What is the difference between a column and a field?**

In database terminology, 'column' and 'field' are often used interchangeably. However, strictly speaking, a column is the entire vertical set of values in a table, while a field is the single value at the intersection of a specific row and column.

**Can a column have multiple values in one cell?**

No, a column in a relational database should hold only one value per row (atomic value). Storing multiple values violates database normalization principles and makes queries difficult.

**What does NULL mean in a column?**

NULL means the value is unknown or missing. It is not the same as zero, an empty string, or false. NULL requires special handling in SQL, like using IS NULL instead of = NULL.

**How do I choose between CHAR and VARCHAR for a column?**

Use CHAR when all values have the same fixed length, like state abbreviations ('CA', 'TX'). Use VARCHAR when the length varies, like names. VARCHAR saves space for variable-length data.

**What happens if I try to store a string in an INT column?**

The database will throw an error and reject the insert or update. The data type constraint enforces that only valid numbers can be stored in an INT column.

**Can I change the data type of a column after the table is created?**

Yes, using ALTER TABLE ALTER COLUMN. But it can fail if existing data cannot be converted (e.g., trying to change a VARCHAR column with text to INT). It can also be slow on large tables.

## Summary

A column is one of the most basic yet vital components of a relational database. It defines what kind of data can be stored and ensures consistency across all rows. Every column has a name, a data type, and often constraints that enforce business rules. 

 Understanding columns is essential for anyone working with data, from database administrators to data analysts. It influences how you design tables, write queries, and optimize performance. In the DP-900 exam, expect questions that test your knowledge of data types, constraints, indexing, and column-related SQL syntax. 

 The key takeaway for exam preparation is to think of columns as rule-givers. They tell the database what is allowed and what is not. A well-designed column saves time, prevents errors, and makes queries fast. Pay attention to details like data type selection, primary keys, foreign keys, and index columns. These are not just exam topics, they are daily tasks for IT professionals. Mastering columns is a fundamental step toward becoming proficient in data management and cloud databases like Azure SQL Database.

---

Practice questions and the full interactive page: https://courseiva.com/glossary/column