Shared Flashcard Set

Details

Storage Requirements lesson 4 part 3
pages, rows etc, and estimating their storage amounts-Microsoft SQL Server 2008 Exam 70-432
22
Computer Science
Undergraduate 4
09/21/2013

Additional Computer Science Flashcards

 


 

Cards

Term
CREATE ASSEMBLY
Definition

this is a transact-SQL command

It creates the catalog assembly

Term
CREATE TYPE
Definition

This is the command to create

an alias data type

or user-defined data type

 

also

sp_addtype syntax

 

Term
Parts of a row in table
Definition

row header

fix-length data

null block

variable block

variable-length data

Term
row header
Definition

row header is 4 bytes

 

data follows said header

Term
Estimating Table Storage Requirements
Definition

single row space calculation

 

rows in a page calculation

 

rows in a table calculation

 

pages to be used in total

Term
Page
Definition

8KB of space

 

remember that the row's header is 4 bytes

and entire page of rows is 8KB or about ~8000 bytes

 

ALSO each page has a header of 2 bytes

Term
Rows per Page
Definition

8096 is the number of free space in a page for data

 

8096 / (row size in byte +2)

Term
NULL BITMAP Calculation
Definition

2+ ((number of columns +7)/8)

 

or

 

((number of nullable columns + 7)/8))

Term
VARIABLE_LENGTH COLUMNS Calculation
Definition
2 + (number of variable columns x 2) + maximum variable size
Term
Row Size
Definition
Fixed data size + variable data size + null_bitmap + row_header which is 4 bytes
Term
Rows that a Table Will Hold
Definition

Estimate the number of rows the table will hold. No formula exists to calculate this; you just need to have a good understanding of your data and user community.

Term

Total Number of Pages

Required

Definition
rows in a table / rows per page
Term
FILE types
Definition

These are NOT data types

 

.mdb

.mdf

.ndf

.ldf

Term
.mdb
Definition

this is master data base

 

not be confused with model data base which is what the data base uses as a template

 

its extension is .mdf

 

a catalog is a database

 

.mdf is a mini file system

Term
.ndf
Definition

This is NOT the default database

 

this are subsequent secondary database files

 

default primary is the .mdf

Term
.ldf
Definition
this the extension for log files
Term

Given Table Creation:

Char (10)

Varchar(20)

Varchar(20)

Varchar(10)

Int

Money

 

**the above can all be null  (money isn't specified as null)

Definition

remember that you add 2 bytes to each variable column

 

So the varchar columns are 22, 22, and 12 respectively

 

int is 4 bytes

 money is 8 bytes

Term

Estimate the null bitmap

 

Char (10)  can be null

Varchar(20)  can be null

Varchar(20)  can be null

Varchar(10)  can be null

Int

Money

Definition

there are 4 nullable columns

 

(4+7)/8

11/7

1.375

 

add 2

 

3.375

 

or 3

Term

Variable length column size

 

Char (10) can be null

Varchar(20) can be null

Varchar(20) can be null

Varchar(10) can be null

Int

Money

Definition

2+(number of variable columns * 2) + max of varchar size

 

2+(3*2)+20+20+10

 

2+6+20+20+10

8+40+10

48+10

58

Term

total row size

 

Char (10) can be null

Varchar(20) can be null

Varchar(20) can be null

Varchar(10) can be null

Int

Money

Definition

fixed data size + variable data size + null bitmap + row header

 

Fixed is 10 +4 for int + 8 for money=22

variable is 58

null bitmap is 3

header is ALWAYS 4

 

22+58+3+4=

87

Term
Number of Pages Needed
Definition

Rows to page is 8096 / (bytes in a row+2)

 

records you expect / rows to a page

 

Number of pages needed * 8192

 

then divide by 1024 twice

Term

formulas for

row amount

rows to a page

rows to a table

pages needed

Definition

First, you calculate a row size:

 

A=2+ ((nullable columns you have +7)/8)

 

B=2+ (variable columns you have x 2) + varchars added up

 

C=fixed data added

 

Final Step=A+B+C+4

 

Second, you calculate rows to a page:

 

 

Supporting users have an ad free experience!