title: How does SQL Server handle large physical database file
body: <p>Coming from MySQL and PostgreSQL, i would very much like to know how SQL Server stores and handles large physical database file.</p>
  
  <p>According to this article here</p>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/aa174545%28SQL.80%29.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/aa174545%28SQL.80%29.aspx</a></p>
  
  <p>SQL Server has 3 types of file, <code>.mdf</code>, <code>.ndf</code> and <code>.ldf</code></p>
  
  <p>Due to the nature of how data grows, a database can contain hundreds of thousand of files. This would eventually affect the size of these <code>.mdf</code>.</p>
  
  <p>So the question is, how does SQL Server handle large physical database files?</p>
  
  <p>I might seem to ask a lot of question, but i would like to have an answer also covers the sub-question below:</p>
  
  <ol>
  <li>Theoretically, <code>.mdf</code> filesize could grow to GB or perhaps TB. Is this common in real world scenario?</li>
  <li>Since SQL Server deals with a single file, it would have a considerably large read/write operation performed on the same file. How would this impact the performance?</li>
  <li>Is it possible (has there been any case) to split <code>.mdf</code> into parts. Instead of having 1 uber large <code>.mdf</code> file, would it be better to split it into chunks?</li>
  </ol>
  
  <p>note: I am new to SQL Server, basic query in SQL Server appears to be similar to MySQL, I would like to know a bit about what is going on "under the hood".</p>