29 Dec 2017

What is filegroup in sql server



Database is physically collection of files. Grouped into file groups.
Database is logically collection of tables. Grouped into schemas.

To bridge the gap between these entities: we use “file groups"
File groups are used to group one or more data files and route table data.

Whenever we define a database one file group is auto created: "primary file group"

Syntax for table creation:

                Create table <schema name>.<table name>
                                (
                                Column name <type> <constraint>,
                                Column name <type> <constraint>,
                                Column name <type> <constraint>,
                                Column name <type> <constraint>,
                                ......
                                )
                                On file group name

Only one primary key is possible per table.
No limit on number of unique & foreign keys.
               
Purpose of file groups: easy data allocation + performance tuning +Easy recovery

Purpose of schemas:-      easy security management

Default schema is dob = database owner.


Default file group is primary.

No comments:
Write comments

Popular Posts

Popular Posts

Translate

Total Pageviews