0
0
SQL Query, Data Storage, Binary Trees, and Indexing: A Complete Guide
1. SQL Queries and Data Storage
| Aspect | Details |
|---|---|
| Tables | Data in SQL is stored in tables, consisting of rows (records) and columns (attributes). |
| Data Types | Each column has a defined data type (e.g., INT, VARCHAR, DATE) to store specific data efficiently. |
| Storage Mechanism | – Data is stored in pages (typically 8 KB). – Tables and indexes are grouped into extents. – Rows are stored within these pages. |
2. Binary Trees in SQL
| Aspect | Details |
|---|---|
| Definition | A binary tree is a hierarchical data structure where each node has at most two children. |
| SQL Usage | SQL databases use B-Trees or B+Trees for indexing to ensure balanced, efficient operations. |
| Why B+Trees? | – Provides balanced height for faster search. – Leaf nodes contain data pointers for efficient sequential access. |
3. SQL Indexing
| Type | Description | Example Query |
|---|---|---|
| Clustered Index | Organizes table data physically by indexed column. Only one clustered index per table. | CREATE CLUSTERED INDEX idx_order ON Orders(OrderID); |
| Non-Clustered Index | Creates a separate structure pointing to rows in the table. Multiple non-clustered indexes allowed. | CREATE NONCLUSTERED INDEX idx_customer ON Orders(CustomerID); |
| Unique Index | Ensures all values in the column(s) are unique. | CREATE UNIQUE INDEX idx_email ON Users(Email); |
| Full-Text Index | Supports text-based searching in large text columns. | CREATE FULLTEXT INDEX ON Articles(Content); |
| Filtered Index | Applies to a subset of rows based on conditions. | CREATE NONCLUSTERED INDEX idx_active_users ON Users(Status) WHERE Status = 'Active'; |
4. Index Design Considerations
| Aspect | Details |
|---|---|
| Composite Indexes | Multi-column indexes. Place the most selective column first. |
| Covering Indexes | Include all columns needed by a query to avoid additional table lookups. |
| Maintenance | Rebuild or reorganize fragmented indexes regularly to ensure performance. |
5. Tricky Interview Questions and Answers
| Question | Answer |
|---|---|
| What happens if a column in the index contains NULL values? | NULL values are included in the index but are excluded from unique constraints unless explicitly allowed. |
| How does SQL Server decide to use an index? | The query optimizer evaluates statistics to determine the cost-effectiveness of using an index. |
| What is index fragmentation, and how do you resolve it? | Fragmentation occurs when index pages are non-contiguous. Fix by rebuilding or reorganizing indexes. |
| Can an index slow down queries? | Yes, indexes can slow down `INSERT`, `UPDATE`, or `DELETE` operations due to maintenance overhead. |
| What is a covering index? | An index containing all columns required for a query, avoiding the need for additional table lookups. |
6. Real-World Example
Consider a Library Management System with the following table:
CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title NVARCHAR(255),
Author NVARCHAR(255),
Genre NVARCHAR(50),
PublishedDate DATE,
CopiesAvailable INT
);Adding an index on the `Author` column:
CREATE NONCLUSTERED INDEX idx_author ON Books(Author);Query using the index:
SELECT * FROM Books WHERE Author = 'J.K. Rowling';