MDF, NDF, LDF, and Filegroups

🔹 1. Filegroup kya hota hai?

🔸 Definition:

Filegroup SQL Server में logical storage unit hota hai, jisme ek ya zyada data files hoti hain. Ye filegroups aapko data ko alag-alag physical disks pe divide karne ki flexibility dete hain, taaki performance optimize ho sake.

🔸 Default Filegroup:

• Jab bhi aap ek database create karte ho, ek primary filegroup automatically create hota hai.
• Isme by default primary data file (MDF) store hoti hai.

🔸 Types of Filegroups:

1. Primary Filegroup:
   • Isme MDF file hoti hai (main data file).
   • Database objects (tables, indexes) by default yahin store hote hain.
2. User-defined Filegroups:
   • Aap custom filegroups create kar sakte ho.
   • Useful for partitioning large tables or indexes across multiple disks.
3. Read-Only Filegroup (optional):
   • Isme stored data sirf read ho sakta hai, write nahi.
   • Archival ya historical data ke liye use hota hai.

🔹 2. MDF, NDF, LDF kya hote hain?

🔸 1. MDF (Master Data File):

• Primary data file hoti hai.
• Isme schema (tables, views, stored procedures) aur actual data store hota hai.
• Har database me ek hi MDF file hoti hai.

📁 Example:

MyDatabase.mdf

🔸 2. NDF (Secondary Data Files):

• Optional files hain.
• Jab data bahut bada ho jata hai ya performance ke liye data multiple disks pe rakhna ho, tab use karte hain.
• Ek database me 0 se lekar multiple NDF files ho sakti hain.

📁 Example:

MyDatabase_Data2.ndf
MyDatabase_Data3.ndf

➡️ Aap NDF files ko user-defined filegroups me assign kar sakte ho.

🔸 3. LDF (Log Data File):

• Transaction log file hoti hai.
• Isme sabhi transactions (insert, update, delete) ka record hota hai.
• LDF file crash recovery ke liye essential hoti hai.
• Ek database me ek ya zyada LDF files ho sakti hain, but usually ek hi rakhte hain.

📁 Example:

MyDatabase_log.ldf

Filegroup-Based Data Placement (USE CASE):

-- New filegroup create karna
ALTER DATABASE MyDatabase
ADD FILEGROUP Sales_FG;

-- NDF file add karna
ALTER DATABASE MyDatabase
ADD FILE (
   NAME = 'Sales_Data',
   FILENAME = 'D:\SQLData\Sales_Data.ndf',
   SIZE = 100MB,
   MAXSIZE = 1GB,
   FILEGROWTH = 10MB
) TO FILEGROUP Sales_FG;

-- Table ko specific filegroup me banana
CREATE TABLE Sales (
   OrderID INT,
   OrderDate DATE
) ON Sales_FG;

🔹 4. Advantage of Filegroups

🔹 5. Commands Reference for MDF/NDF/LDF and Filegroups

✅ Database Files Check Karna

-- Data files and log files details
SELECT 
   name AS FileName,
   physical_name AS PhysicalFile,
   type_desc AS FileType,
   size*8/1024 AS SizeMB,
   file_id
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabaseName');

✅ Filegroups List

SELECT 
   name AS FilegroupName, 
   type_desc, 
   is_default 
FROM sys.filegroups;

✅ Assign Default Filegroup

ALTER DATABASE MyDatabase
MODIFY FILEGROUP Sales_FG DEFAULT;

🔹 6. Real-World Use Case

• MDF stores your main application data.
• NDF used when:
  • App grows and needs storage split across multiple drives.
  • Table partitioning required.
• LDF is critical for recovery and should always be on a separate disk for performance.
• Filegroups help you logically group and manage files. Backup/Restore can be performed at filegroup level.

🔹 7. Best Practices