exploring SQL and file systems

Question 1

What is SQL Physical Architecture?

Answer 1

Tells about how the data is actually stored in file system of an operating system.
Page, extent, database files, transaction log etc. are core components of physical architecture

Question 2

What is logical architecture?

Answer 2

Tells how data is logically grouped and presented to the user.
eg: Tables, constraints, views, stored procedures, triggers etc. are core components of logical architecture

Question 3

What are examples of Physical data structure components?

Answer 3

Page
extent
table
index
database files
database file group
transaction log file

Question 4

What are File groups?

Answer 4

collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.

Question 5

SQL Server maps a _______over a set of operating-system files.

Answer 5

database

Question 6

Data and log information are _____ mixed in the same file, and individual files are used only by _____ database(s).

Answer 6

never, one

Question 7

What do read only files help to reduce?

Answer 7

reduce the number of backups

by not backing up read only files due to not modifying

Question 8

what is the typical log file extension?

Answer 8

.ldf

these are not apart of file groups

Question 9

What are the three types of SQL file types?

Answer 9

Primary database files
Secondary database files
Transaction log files

Question 10

Databases have two different file names. They are:

Answer 10

Logical file names

OS file names

Question 11

What are logical file names?

Answer 11

Used in T-SQL to refer to a physical file

Question 12

What are OS file names?

Answer 12

full name; folder path and physical file

Question 13

What are primary database files?

Answer 13

-contains startup info for the database
- every database has one primary data file
file name extension : .mdf
-user data and objects stored here or in secondary

Question 14

what are secondary database files?

Answer 14

files that are optional, user-defined, and store user data

• can be used to spread data across multiple disks, by putting each file on a different drive
• file name extension- .ndf

Question 15

What are transaction log files?

Answer 15

holds log info that is used to recover the database

• must be at least one log file for each database
• file name extension .ldf

Question 16

4 steps for modifying a file in a database

Answer 16

1. runs a query to modify row in table
2. change is recorded in transaction log
3. query returns
4. change is recorded in data file

Question 17

What are pages in a database

Answer 17

fundamental unit of storage
8kb per page
96 byte header
contains header and body (body=data rows)

Question 18

What is included in a page header?

Answer 18

Stores system info

- page number, page type, amount of free space, allocation unit ID pf the object that owns the page

Question 19

What are slot arrays?

Answer 19

Slot arrays indicate the logical order of the data row on the page

Question 20

What are different page types?

Answer 20

Data
index
text/image
page free space
index allocation map
globally allocated map
etc.

Question 21

What are extents?

Answer 21

Basic unit in which space is managed

• 8 physically contiguous pages
• 16 extents per megabyte
• New tables are usually mixed extents until allocated space is full.

Question 22

Uniform extents are?

Answer 22

owned by a single objects

-all 8 pages in the extent can only be owned by one object

Question 23

mixed extents

Answer 23

• share by up to 8 objects

each page can be owned by a different object

Question 24

What is the difference between log and database files

Answer 24

-database system is comprised of both
- all transactions write to log file immediately
pages are read from database memory
modified when needed.