Author Archives: Stanislav_Panteleev

NULL in SQL means: Missing, unknown, or not applicable value. It’s not zero 0, it’s not an empty string ”. It literally means “I don’t know” or “value does not exist.” NULL ≠ 0NULL ≠ ”NULL = unknown Key Characteristics … Continue reading →

What is a UNIQUE Constraint? A UNIQUE constraint ensures that all values in a column (or a combination of columns) are different — no duplicates are allowed. Similar to a PRIMARY KEY, but: A table can have only one primary … Continue reading →

Difference Between WHERE and HAVING WHERE HAVING Filters rows before grouping or aggregation. Filters groups after aggregation. Used with SELECT, UPDATE, DELETE Used only with GROUP BY (aggregated queries). Cannot use aggregate functions (SUM(), COUNT(), etc.) in WHERE. Can use … Continue reading →

There are two main strategies depending on how bad the fragmentation is: 1. REORGANIZE the Index (Light Defragmentation) What it does: Defragment leaf pages online, without locking the table. Moves rows between pages to compact them. Does NOT rebuild the … Continue reading →

1. More Pages to Read Fragmentation = each page holds fewer rows (wasted space). To find or scan the same number of rows, the database needs to read more pages. More pages = more I/O operations. 👉 Disk I/O is … Continue reading →

Fragmentation happens when the physical storage of data (or indexes) on disk becomes disorganized. In an ideal world: Data is stored contiguously — nice and neat. In reality (especially after lots of INSERTs, UPDATEs, DELETEs): Data gets scattered — small … Continue reading →

Big Picture: What is an Index Structure? Most database indexes (like MySQL InnoDB, PostgreSQL B-tree indexes, SQL Server) are stored as a B-tree or B+ tree structure. Think of it as a tree — like an upside-down real tree: The … Continue reading →

An index page is a block of memory/disk storage where part of the index data is stored. More precisely: A page is a fixed-size block of data — typically 8 KB (in systems like SQL Server, PostgreSQL, MySQL with InnoDB). … Continue reading →

1. Slower Write Operations INSERT, UPDATE, DELETE operations become slower. Why? Every time you modify the table, the database has to: Update the table and Update all related indexes. Example: If there’s an index on customer_id, the database must: Insert … Continue reading →

What is an Index in SQL? An index in a database is like an index in a book: Instead of scanning the whole book (all pages), you look at the index to find the page number directly. In a database, … Continue reading →