What is filegroup in sql server

The database is a physical collection of files. Grouped into file groups.

The database is logically a collection of tables. Grouped into schema.

To bridge the gap between these entities: we use “file groups"

File-groups is 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-groups"

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 a number of unique & foreign keys.

               

Purpose of file groups: easy data allocation + performance tuning +Easy recovery

Purpose of schema:-      easy security management

Default schema is dob = database owner.

Default file group is primary.