Term
|
Definition
this is a transact-SQL command
It creates the catalog assembly |
|
|
Term
|
Definition
This is the command to create
an alias data type
or user-defined data type
also
sp_addtype syntax
|
|
|
Term
|
Definition
row header
fix-length data
null block
variable block
variable-length data |
|
|
Term
|
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
|
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
|
Definition
8096 is the number of free space in a page for data
8096 / (row size in byte +2) |
|
|
Term
|
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
|
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
|
Definition
These are NOT data types
.mdb
.mdf
.ndf
.ldf |
|
|
Term
|
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
|
Definition
This is NOT the default database
this are subsequent secondary database files
default primary is the .mdf |
|
|
Term
|
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
|
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:
|
|
|
