*.mdf file (noob question)
-
Ehrm... Hello!
Call me a newbie, but I don't get it. Isn't the .mdf file a database itself? If it is, how come when I store 20kb of data in it the file doesn't get a byte bigger? If it isn't the database... What is then??
/Tomi
Ps. Maybe this should't be in the TSQL category but it's a habit...
-
In SQL Server, a database can span across multiple files. These files can be grouped into filegroups.
One reason you'd do this is to increase performance. For instance, if I have two tables which were hit heavily and I had the option of two physical sets of drives, I could create a file on one set, I could do the following: create two filegroups. Put a file that belongs to filegroup 1 on drive set A. Put another file that belongs to filegroup 2 on drive set B. Create the 1st table on filegroup 1. Create the 2nd table on filegroup 2. This reduces Disk I/O, which tends to be the slowest operation with respect to handling data.
The recommended file extensions are:
.mdf - primary file
.ndf - additional files
.ldf - log files
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
The .mdf file is the database file however, the file size does not necessarily tell you how much data is in the file. So, when you insert 20 K into the database, the .mdf file size may not need to expand.
The premise behind this is that they size the data file, .mdf, first thereby allocating the disk space and then fill the file with data. The actual disk space allocation is the main impact to speed so, by sizing the file first that takes care of that issue (unless you have to grow the file).
A quick way to see the used space increase is by running sp_spaceused in the database that you are checking.
Check on that procedure in BOL for more information.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Thanks!
One question tho. If I copy the .mdf file and put it on another computer, it copies all the records, right? I have done this a few times. But does it always work?
Thanks again
/Tomi
-
Yes, it copies all aspects of that database.
Glad to help.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply