operation sysytem file type: mdf&ldf

database file type: mdf&ndf

 

===primary data file

include startup infomation for the database catalog and points to the outher database files

can also contain obj&data, that means can not contain obj&data

当用户自己创建用户自定义文件组，并制定该文件组为默认文件组之后，所有的数据库对象将存在该文件组中

主数据文件保存在主文件组中。主文件组不一定是默认文件组，默认文件组也不一定是主文件组

 

===secondary data file[Max:32776]

obj&user data

***put each sdf on a different disk drive to boost performance

 

===log file

records all database modifications that each transaction make

create multiple log files per database to facilitate faster recovery 

===file group: a logical structure = pfg&ufg

splitting database objects across several filegroups, you can

#take advantage of the differrentdisk subsystems

#allow SQL Server to perform paralled disk operations

#back up and restore files individually

***default filegroup

***read-only filegroup

 

===how to design table

one access-intensive table:create multiple second data file,

store the files on different disk drives,

and group these files in a filegroup or multiple filegroups

 

===how to design filegroup

create at least one user-defined filegroup to hold seconday data files and database objects.

Configure this filegroup as the default filegroup so that SQL Server will store all objects

you create in this filegroup

 

==how to design database files with RAID system

RAID10------Log Files

RAID5-------Data Files

 

 1CREATE DATABASE [70431] 

 2ON  

 3--primary data file

 4PRIMARY 

 5    ( NAME = N'70431_pdf', FILENAME = N'D:\DBF\70431_pdf.mdf' , 

 6    SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 20480KB ), 

 7

 8--seconday data file 

 9FILEGROUP [USEDEFINE1] 

10    ( NAME = N'70431_data1', FILENAME = N'E:\Projects\SQL\DbFiles\70431_data1.ndf' , 

11    SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 20480KB ), 

12    ( NAME = N'70431_data3', FILENAME = N'E:\Projects\SQL\DbFiles\70431_data3.ndf' , 

13    SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 10240KB ), 

14FILEGROUP [USERDEFINE2] 

15    ( NAME = N'70431_data2', FILENAME = N'E:\Projects\SQL\DbFiles\70431_data2.ndf' , 

16    SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 10240KB ), 

17    ( NAME = N'70431_data4', FILENAME = N'E:\Projects\SQL\DbFiles\70431_data4.ndf' , 

18    SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 10240KB )

19

20-- log file 

21LOG ON 

22    ( NAME = N'70431_log', FILENAME = N'F:\Projects\SQL\LgFIles\70431_log.ldf' , 

23    SIZE = 20480KB , MAXSIZE = 204800KB , FILEGROWTH = 20480KB ), 

24    ( NAME = N'70431-log1', FILENAME = N'F:\Projects\SQL\LgFIles\70431-log1.ldf' , 

25    SIZE = 20480KB , MAXSIZE = 102400KB , FILEGROWTH = 10240KB )

26

27

28GO

29

30ALTER DATABASE [70431]

31MODIFY FILEGROUP [USEDEFINE1] DEFAULT