1"use strict";(self.webpackChunk_N_E=self.webpackChunk_N_E||[]).push([[9576],{9576:function(e,s,t){t.r(s),t.d(s,{default:function(){return o}});var n=t(7437),a=t(7648),r=t(8054),i=t(4513);function o(){return(0,n.jsxs)("div",{className:"prose prose-lg max-w-none",children:[(0,n.jsxs)("div",{className:"not-prose my-8 p-6 bg-gray-50 rounded-xl border border-gray-200",children:[(0,n.jsx)("h4",{className:"font-bold text-gray-900 mb-3",children:"Quick Navigation"}),(0,n.jsx)("div",{className:"flex flex-wrap gap-2",children:[{text:"Quick Answer",id:"quick-answer"},{text:"Clustered Index",id:"clustered-index"},{text:"Nonclustered Index",id:"nonclustered-index"},{text:"Visual Comparison",id:"visual-comparison"},{text:"When to Use Clustered",id:"when-clustered"},{text:"When to Use Nonclustered",id:"when-nonclustered"},{text:"Decision Guide",id:"decision-guide"},{text:"Common Mistakes",id:"common-mistakes"},{text:"Interview Questions",id:"interview-questions"},{text:"FAQ",id:"faq"}].map(e=>(0,n.jsx)("a",{href:"#".concat(e.id),className:"px-3 py-1 bg-amber-100 hover:bg-amber-200 text-amber-800 rounded-full text-sm transition-colors",children:e.text},e.id))})]}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Indexes are the secret weapon for fast SQL queries. But choosing the wrong type can make your database slower, not faster. Let's break down clustered vs nonclustered indexes with visual examples so you'll know exactly when to use each."}),(0,n.jsx)("h2",{id:"quick-answer",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"Quick Answer: Clustered vs Nonclustered Index"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Here's the TL;DR:"}),(0,n.jsxs)("ul",{className:"list-disc pl-6 mb-6 space-y-2 text-gray-700",children:[(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Clustered index:"})," Data is physically sorted by the index column (like a phone book sorted by name)"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Nonclustered index:"})," A separate structure pointing to data (like a library card catalog)"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Key difference:"})," One clustered index per table vs up to 999 nonclustered indexes"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"When to use which:"})," Clustered for range queries, nonclustered for selective lookups"]})]}),(0,n.jsx)(r.Rb,{headers:["Feature","Clustered Index","Nonclustered Index"],rows:[["Storage","Data stored in index order","Separate structure with pointers"],["Quantity","One per table","Up to 999 per table"],["Best for","Range queries, full scans","Selective point lookups"],["Speed","Fastest for primary key lookups","Faster for filtered queries on non-key columns"]],caption:"Quick reference comparison"}),(0,n.jsx)("h2",{id:"clustered-index",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"How Clustered Indexes Work"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"A clustered index determines the physical order of data in a table. The rows ARE stored in the order of the clustered index key. Think of a phone book: the entries are physically sorted by last name, so finding âSmithâ means jumping to the S section."}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:(0,n.jsx)("strong",{children:"Key characteristics of clustered indexes:"})}),(0,n.jsxs)("ul",{className:"list-disc pl-6 mb-6 space-y-2 text-gray-700",children:[(0,n.jsx)("li",{children:"Data rows are physically stored in the order of the clustered index"}),(0,n.jsx)("li",{children:"Primary key is automatically clustered in SQL Server (unless specified otherwise)"}),(0,n.jsx)("li",{children:"Only ONE clustered index allowed per table (you can't sort data two ways)"}),(0,n.jsx)("li",{children:"Uses a B-tree structure: root node, intermediate nodes, and leaf nodes"}),(0,n.jsx)("li",{children:"Leaf nodes contain the ACTUAL DATA ROWS"}),(0,n.jsx)("li",{children:"Fast for range scans because data is sequential on disk"})]}),(0,n.jsx)(r.wq,{nodes:[{label:"Root Node (starting point)",icon:"\uD83C\uDF33",type:"start"},{label:"Intermediate Nodes (navigation)",icon:"\uD83D\uDCCD",type:"process"},{label:"Leaf Nodes = ACTUAL DATA ROWS",icon:"\uD83D\uDCC4",type:"end"}],caption:"In a clustered index, leaf nodes contain the actual table data, sorted by the index key."}),(0,n.jsx)(i.M_,{variant:"tip",title:"The Filing Cabinet Analogy",children:"Think of a clustered index like case files in a filing cabinet, sorted by case number. The files ARE in that order. There's no separate lookup needed. When you need Case #500, you go straight to that section."}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Here's how y
1ou create a clustered index:"}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg mb-6",children:[(0,n.jsx)("h4",{className:"font-bold text-gray-900 mb-3",children:"Creating a Clustered Index:"}),(0,n.jsx)("pre",{className:"bg-gray-800 text-green-400 p-4 rounded text-sm overflow-x-auto",children:"-- Primary key automatically creates clustered index in SQL Server\nCREATE TABLE suspects (\n suspect_id INT PRIMARY KEY, -- Clustered by default\n name VARCHAR(100),\n last_seen DATE\n);\n\n-- Or explicitly create a clustered index\nCREATE CLUSTERED INDEX idx_suspects_id ON suspects(suspect_id);"}),(0,n.jsx)("p",{className:"text-gray-600 text-sm mt-2",children:"In SQL Server, PRIMARY KEY creates a clustered index unless you specify NONCLUSTERED."})]}),(0,n.jsx)("h2",{id:"nonclustered-index",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"How Nonclustered Indexes Work"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"A nonclustered index is a separate data structure that lives outside the table. It contains the indexed column values plus a pointer back to the actual row. This pointer is called a âbookmarkâ or ârow locator.â"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:(0,n.jsx)("strong",{children:"Key characteristics of nonclustered indexes:"})}),(0,n.jsxs)("ul",{className:"list-disc pl-6 mb-6 space-y-2 text-gray-700",children:[(0,n.jsx)("li",{children:"Separate data structure outside the table"}),(0,n.jsx)("li",{children:"Contains indexed columns + pointers (RID or clustered key)"}),(0,n.jsx)("li",{children:"Multiple nonclustered indexes allowed (up to 999 in SQL Server)"}),(0,n.jsx)("li",{children:"Requires a âbookmark lookupâ to get full row data"}),(0,n.jsx)("li",{children:"Can INCLUDE extra columns for covering indexes"})]}),(0,n.jsx)(r.wq,{nodes:[{label:"Root Node (starting point)",icon:"\uD83C\uDF33",type:"start"},{label:"Intermediate Nodes (navigation)",icon:"\uD83D\uDCCD",type:"process"},{label:"Leaf Nodes = INDEX KEY + POINTER",icon:"\uD83D\uDD17",type:"process"},{label:"Pointer â Table Data (bookmark lookup)",icon:"\uD83D\uDCC4",type:"end"}],caption:"Nonclustered leaf nodes contain the index key plus a pointer back to the actual row."}),(0,n.jsx)(i.M_,{variant:"tip",title:"The Library Card Catalog Analogy",children:"A nonclustered index is like a library card catalog. The cards are sorted alphabetically by title, but they contain a call number (pointer) telling you where to find the actual book on the shelves."}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Here's how you create a nonclustered index:"}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg mb-6",children:[(0,n.jsx)("h4",{className:"font-bold text-gray-900 mb-3",children:"Creating Nonclustered Indexes:"}),(0,n.jsx)("pre",{className:"bg-gray-800 text-green-400 p-4 rounded text-sm overflow-x-auto",children:"-- Simple nonclustered index\nCREATE NONCLUSTERED INDEX idx_suspects_name \nON suspects(name);\n\n-- Covering index with INCLUDE clause\nCREATE NONCLUSTERED INDEX idx_suspects_name_covering \nON suspects(name) \nINCLUDE (last_seen, age);\n-- Now queries selecting only name, last_seen, age \n-- don't need a bookmark lookup!"}),(0,n.jsx)("p",{className:"text-gray-600 text-sm mt-2",children:"The INCLUDE clause adds columns to the leaf level without sorting by them."})]}),(0,n.jsxs)("p",{className:"text-gray-700 leading-relaxed mb-6",children:["Understanding indexes is crucial for writing efficient SQL. If you want to practice querying databases where every millisecond counts,"," ",(0,n.jsx)(a.default,{href:"/cases",className:"text-amber-700 hover:text-amber-900 underline font-medium",children:"SQLNoir's detective cases"})," ","challenge you to solve mysteries with real SQL queries."]}),(0,n.jsx)("h2",{id:"visual-comparison",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"Visual Comparison: The Key Difference"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"The fundamental difference comes down to what's stored in the leaf nodes:"}),(0,n.jsxs)("ul",{className:"list-disc pl-6 mb-6 space-y-2 text-gray-700",children:[(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Clustered:"})," Leaf node = the actual data row"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Nonclustered:"})," Leaf node = index key + pointer to data row"]})]}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"This single difference explains ALL performance characteristics of each index type."}),(0,n.jsx)(r.Rb,{headers:["Aspect","Clustered Index","Nonclustered Index"],rows:[["Leaf nodes contain","Actual data rows","Index key + pointer"],["Physical data order","Determined by index","Not affected"],["Number per table","Maximum 1","Up to 999"],["Storage overhead","Lower (
1data is the index)","Higher (separate structure)"],["Range scan performance","Excellent (sequential)","Requires bookmark lookups"],["Point lookup performance","Excellent (direct path)","Good (with covering index: excellent)"],["Insert/Update cost","Higher if reordering needed","Additional index maintenance"],["When to use","Primary key, range-scanned columns","Frequently filtered non-key columns"],["Auto-created by","PRIMARY KEY (SQL Server)","UNIQUE constraint (as option)"],["PostgreSQL behavior","CLUSTER command (one-time reorder)","Default for CREATE INDEX"]],caption:"Complete 10-point comparison of clustered vs nonclustered indexes"}),(0,n.jsx)("h2",{id:"when-clustered",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"When to Use Clustered Indexes"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Clustered indexes shine in specific scenarios:"}),(0,n.jsxs)("ul",{className:"list-disc pl-6 mb-6 space-y-2 text-gray-700",children:[(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Primary keys:"})," Auto-clustered in SQL Server, makes sense for most lookups"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Range queries:"})," Columns used with BETWEEN, greater than, less than"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"ORDER BY columns:"})," When most queries sort by the same column"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Read-heavy tables:"})," Few writes, many reads benefit from sequential data"]})]}),(0,n.jsx)(i._N,{clauses:[{keyword:"SELECT",code:"SELECT *",annotation:"Retrieve all columns"},{keyword:"FROM",code:"FROM cases",annotation:"The cases table"},{keyword:"WHERE",code:"WHERE case_date BETWEEN '2024-01-01' AND '2024-12-31'",annotation:"Range scan benefits from clustered index. Data is sequential!"},{keyword:"ORDER BY",code:"ORDER BY case_date;",annotation:"No sorting needed. Data already in order!"}],caption:"This query benefits massively from a clustered index on case_date"}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg mb-6",children:[(0,n.jsx)("h4",{className:"font-bold text-gray-900 mb-3",children:"Example: Date-Based Clustered Index"}),(0,n.jsx)("pre",{className:"bg-gray-800 text-green-400 p-4 rounded text-sm overflow-x-auto",children:"-- If most queries filter/sort by date, cluster on date\nCREATE TABLE cases (\n case_id INT,\n case_date DATE,\n title VARCHAR(200),\n status VARCHAR(50)\n);\n\nCREATE CLUSTERED INDEX idx_cases_date ON cases(case_date);\n\n-- Now range queries are lightning fast:\nSELECT * FROM cases \nWHERE case_date BETWEEN '2024-01-01' AND '2024-06-30';\n-- Data is physically ordered by date!"})]}),(0,n.jsx)("h2",{id:"when-nonclustered",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"When to Use Nonclustered Indexes"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Nonclustered indexes are your go-to for:"}),(0,n.jsxs)("ul",{className:"list-disc pl-6 mb-6 space-y-2 text-gray-700",children:[(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Frequently filtered columns:"})," Columns in WHERE clauses (not the primary key)"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"JOIN columns:"})," Foreign keys benefit from nonclustered indexes"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Selective queries:"})," When returning a small percentage of rows"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Multiple access paths:"})," When queries filter by different columns"]}),(0,n.jsxs)("li",{children:[(0,n.jsx)("strong",{children:"Covering indexes:"})," When you can satisfy queries entirely from the index"]})]}),(0,n.jsx)(i.Nw,{before:{code:"SELECT * FROM suspects WHERE last_name = 'Martinez';\n-- Without index: Full Table Scan\n-- Scans ALL 1,000,000 rows\n-- 5,000+ page reads\n-- ~2.5 seconds",label:"Without Index: Full Table Scan",issues:["Examines every row in the table","Slow even for one matching record"]},after:{code:"CREATE NONCLUSTERED INDEX idx_lastname \nON suspects(last_name);\n\nSELECT * FROM suspects WHERE last_name = 'Martinez';\n-- With index: Index Seek + Bookmark Lookup\n-- ~10 page reads\n-- ~0.01 seconds",label:"With Nonclustered Index: Index Seek",improvements:["Jumps directly to matching entr
1ies","100x+ faster for selective queries"]},caption:"Nonclustered index dramatically improves selective lookup performance"}),(0,n.jsx)(i.L2,{caseNumber:3,caseTitle:"The Miami Marina Murder",challenge:"Think you understand when to use each index type? Test your skills by solving a case where choosing the right query approach is the difference between cracking the case and hitting a dead end.",difficulty:"intermediate",href:"/cases"}),(0,n.jsx)("h2",{id:"decision-guide",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"Decision Guide: Which Index Type to Use"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Follow this decision tree when choosing your index type:"}),(0,n.jsx)(r.wq,{nodes:[{label:"Is this your PRIMARY KEY?",icon:"\uD83D\uDD11",type:"start"},{label:"Will you do RANGE SCANS (BETWEEN, >, <)?",icon:"\uD83D\uDCCA",type:"process"},{label:"Do you already have a clustered index?",icon:"â",type:"process"},{label:"Is this column frequently in WHERE clauses?",icon:"\uD83D\uDD0D",type:"process"},{label:"Choose: Clustered, Nonclustered, or Covering",icon:"â
",type:"end"}],caption:"PRIMARY KEY â Clustered (auto). Range scans + no existing clustered â Consider clustered. Already have clustered + frequent WHERE â Nonclustered. Need full row without bookmark lookup â Covering nonclustered."}),(0,n.jsx)(i.Ri,{title:"\uD83D\uDD0D Test Your Index Knowledge",questions:[{question:"You have an 'orders' table and frequently query orders by customer_id (not the primary key). Which index type?",options:["Clustered index on customer_id","Nonclustered index on customer_id","No index needed","Both types"],correctIndex:1,explanation:"Since the table already has a clustered index (likely on order_id), you can't add another. Nonclustered is perfect for frequently filtered columns."},{question:"A query retrieves only customer_id and order_date from a million-row table. What's the fastest approach?",options:["Clustered index on customer_id","Nonclustered index on customer_id","Nonclustered covering index with INCLUDE (order_date)","Full table scan"],correctIndex:2,explanation:"A covering index includes all needed columns, eliminating bookmark lookups entirely. The query is satisfied from the index alone."},{question:"In SQL Server, what happens when you define a PRIMARY KEY on a table?",options:["A nonclustered index is created","A clustered index is created by default","No index is created","Both types are created"],correctIndex:1,explanation:"SQL Server automatically creates a clustered index on the PRIMARY KEY unless you explicitly specify NONCLUSTERED."},{question:"Why can a table have only ONE clustered index?",options:["SQL Server limitation that could be changed","Because clustered index determines physical data order","To save storage space","No technical reason, just convention"],correctIndex:1,explanation:"Data can only be physically sorted ONE way. You can't alphabetize a phone book by both name AND address simultaneously."}]}),(0,n.jsx)("h2",{id:"common-mistakes",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"Common Mistakes (And How to Avoid Them)"}),(0,n.jsx)("h3",{className:"text-xl font-bold text-gray-900 mt-8 mb-4",children:"Mistake 1: Clustering on a Frequently Updated Column"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"When you cluster on a column that changes often, every update may require physically moving the row to maintain sort order. This causes âpage splitsâ and degrades write performance."}),(0,n.jsx)(i.Nw,{before:{code:"-- Status changes frequently: Open â In Progress â Closed\nCREATE CLUSTERED INDEX idx_status ON cases(status);\n\n-- Every status update may require row movement\nUPDATE cases SET status = 'Closed' WHERE case_id = 123;\n-- Row physically moves from 'In Progress' section to 'Closed' section!",label:"Bad: Clustered Index on Frequently Updated Column",issues:["Every status change may require physical row movement","Causes page splits and fragmentation","Write performance degrades over time"]},after:{code:"-- case_id is auto-increment, never changes\n-- PRIMARY KEY creates clustered index automatically\nCREATE TABLE cases (\n case_id INT PRIMARY KEY,\n status VARCHAR(20)\n);\n\n-- Status queries use nonclustered index\nCREATE NONCLUSTERED INDEX idx_status ON cases(status);\n\n-- Status updates don't move rows!",label:"Better: Clustered on Sequential ID, Nonclustered on Status",improvements:["New rows always go to the end (no reordering)","Status updates don't move physical rows","Both query patterns are fast"]},caption:"Avoid clustering on columns that change frequently"}),(0,n.jsx)("h3",{className:"text-xl font-bold text-gray-900 mt-8 mb-4",children:"Mistake 2: Too Many Nonclustered Indexe
1s"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"Every nonclustered index must be updated on INSERT, UPDATE, and DELETE operations. Having too many indexes slows down writes."}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg mb-6",children:[(0,n.jsx)("h4",{className:"font-bold text-gray-900 mb-3",children:"The Trade-off:"}),(0,n.jsx)("pre",{className:"bg-gray-800 text-green-400 p-4 rounded text-sm overflow-x-auto",children:"-- Each index speeds up reads but slows down writes\nCREATE NONCLUSTERED INDEX idx_1 ON suspects(name);\nCREATE NONCLUSTERED INDEX idx_2 ON suspects(age);\nCREATE NONCLUSTERED INDEX idx_3 ON suspects(last_seen);\nCREATE NONCLUSTERED INDEX idx_4 ON suspects(city);\nCREATE NONCLUSTERED INDEX idx_5 ON suspects(status);\n\n-- Every INSERT now updates 5 indexes + the table!\nINSERT INTO suspects VALUES (...);\n-- Every UPDATE potentially updates multiple indexes\nUPDATE suspects SET status = 'Cleared' WHERE suspect_id = 123;"}),(0,n.jsx)("p",{className:"text-gray-600 text-sm mt-2",children:"Rule of thumb: Index columns that are frequently in WHERE clauses, not every column."})]}),(0,n.jsx)("h3",{className:"text-xl font-bold text-gray-900 mt-8 mb-4",children:"Mistake 3: Not Using Covering Indexes"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"If your queries consistently need specific columns, a covering index eliminates bookmark lookups entirely:"}),(0,n.jsx)("div",{className:"bg-gray-50 p-6 rounded-lg mb-6",children:(0,n.jsx)("pre",{className:"bg-gray-800 text-green-400 p-4 rounded text-sm overflow-x-auto",children:"-- Without covering index: Index Seek + Bookmark Lookup\nCREATE NONCLUSTERED INDEX idx_name ON suspects(last_name);\n\nSELECT last_name, first_name, phone \nFROM suspects WHERE last_name = 'Smith';\n-- Has to look up the actual row for first_name and phone\n\n-- With covering index: Index Seek only!\nCREATE NONCLUSTERED INDEX idx_name_covering \nON suspects(last_name) INCLUDE (first_name, phone);\n\nSELECT last_name, first_name, phone \nFROM suspects WHERE last_name = 'Smith';\n-- All columns in the index, no bookmark lookup needed!"})}),(0,n.jsx)(i.M_,{variant:"tip",title:"Pro Tip",children:"Check your execution plans. If you see âKey Lookupâ or âRID Lookup,â that's a bookmark lookup. Consider a covering index if this query runs frequently."}),(0,n.jsx)("h2",{id:"interview-questions",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"Interview Questions: Clustered vs Nonclustered"}),(0,n.jsx)("p",{className:"text-gray-700 leading-relaxed mb-6",children:"These are the index questions that come up in technical interviews:"}),(0,n.jsxs)("div",{className:"space-y-6",children:[(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Q: What's the key difference between clustered and nonclustered indexes?"}),(0,n.jsxs)("p",{className:"text-gray-700",children:[(0,n.jsx)("strong",{children:"A:"})," Clustered indexes determine the PHYSICAL order of data on disk. The leaf nodes contain the actual data rows. Nonclustered indexes are separate structures where leaf nodes contain index keys + pointers back to the actual rows. This is why you can have only one clustered index (data can only be sorted one way) but many nonclustered indexes."]})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Q: Why can a table have only one clustered index?"}),(0,n.jsxs)("p",{className:"text-gray-700",children:[(0,n.jsx)("strong",{children:"A:"})," Because data can only be physically sorted one way. You can't alphabetize a phone book by both name AND address simultaneously. The clustered index key determines row order on disk."]})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Q: What is a covering index?"}),(0,n.jsxs)("p",{className:"text-gray-700",children:[(0,n.jsx)("strong",{children:"A:"})," A covering index includes all columns needed by a query, either in the key columns or via INCLUDE clause. This eliminates the need for a bookmark lookup because the query can be satisfied entirely from the index."]})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Q: What are page splits and why do they matter?"}),(0,n.jsxs)("p",{className:"text-gray-700",children:[(0,n.jsx)("strong",{children:"A:"})," Page splits occur when a new row needs to be inserted into a full data page in a clustered index. The database must split the page in two and redistribute rows. This causes fragmentation and slows writes. Choose clustered index keys that grow sequentially (like auto-increment IDs) to minimize page splits."]})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Q: When would a nonclustered index be faster than a clustered index?"}),(0,n.jsxs)("p",{className:"text-gray-700",children:[(0,n.jsx)("strong",{children:"A:"})," When you have a covering index that includes all columns needed by the query. The nonclustered index is smaller than the full table, so scanning it is faster. Also, for highly selective queries that return few rows, a nonclustered index seek + bookmark lookup can be faster than a clustered range scan that returns more data than needed."]})]})]}),(0,n.jsx)(i.M_,{variant:"clue",title:"Interview Tip",children:"When explaining indexes in interviews, always mention the PHYSICAL storage aspect. The clustered index determines how data is PHYSICALLY stored on disk. That's why there can only be one. Use the phone book (clustered) vs. library card catalog (nonclustered) analogy."}),(0,n.jsxs)("div",{className:"not-prose my-10 p-8 bg-gradient-to-br from-amber-50 to-amber-100/80 border border-amber-200 rounded-xl text-center",children:[(0,n.jsx)("p",{className:"text-amber-900 font-detective text-xl mb-2",children:"Ready to prove your SQL knowledge?"}),(0,n.jsx)("p",{className:"text-amber-700 mb-5 max-w-lg mx-auto",children:"SQLNoir challenges you to use efficient queries to solve real detective cases. No index? Good luck scanning a million rows to find your suspect."}),(0,n.jsx)(a.default,{href:"/cases",className:"inline-flex items-center gap-2 px-6 py-3 bg-amber-800/90 hover:bg-amber-700/90 text-amber-100 rounded-lg font-detective text-lg transition-colors",children:"Start Your Investigation â"})]}),(0,n.jsx)("h2",{id:"faq",className:"text-3xl font-detective text-amber-900 mt-12 mb-6",children:"FAQ"}),(0,n.jsxs)("div",{className:"space-y-6",children:[(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Can a table have both clustered and nonclustered indexes?"}),(0,n.jsx)("p",{className:"text-gray-700",children:"Yes! A table can have ONE clustered index (typically on primary key) and up to 999 nonclustered indexes on other columns. This is the most common setup."})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"What happens if I don't create a clustered index?"}),(0,n.jsx)("p",{className:"text-gray-700",children:"The table becomes a âheap.â Rows are stored in no particular order. Heaps can work for certain write-heavy scenarios but generally perform worse for reads. Most tables benefit from having a clustered index."})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Does PostgreSQL support clustered indexes?"}),(0,n.jsx)("p",{className:"text-gray-700",children:"PostgreSQL handles it differently. You can CLUSTER a table to physically reorder it by an index, but unlike SQL Server, this is a one-time operation. New inserts don't maintain the order. PostgreSQL's default CREATE INDEX creates nonclustered indexes."})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"How do I know if my query is using an index?"}),(0,n.jsx)("p",{className:"text-gray-700",children:"Check the execution plan. In PostgreSQL, use EXPLAIN or EXPLAIN ANALYZE. In SQL Server, view the execution plan. Look for âIndex Seekâ (good) vs âTable Scanâ or âClustered Index Scanâ (no index used effectively)."})]}),(0,n.jsxs)("div",{className:"bg-gray-50 p-6 rounded-lg",children:[(0,n.jsx)("h3",{className:"font-bold text-gray-900 mb-2",children:"Should I index every column I query?"}),(0,n.jsx)("p",{className:"text-gray-700",children:"No! Each index slows down writes (INSERT, UPDATE, DELETE). Index columns that are frequently filtered or sorted, but balance read performance gains against write overhead. Monitor query patterns and index strategically."})]})]})]})}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.