database indexes

Create index on Orders table with key CustomerID and fill factor 75

create nonclustered index Orders_Customers_link on Orders(CustomerID) with (fillfactor=75) go

r

number of records

R

record size/length (for a table record)

R­_i

index record size/length

b_x

number of blocks needed to store index records

where x = the level of the index (1 = first, 2 = second, etc.)

P

block pointer size

P_R

record pointer size

b

number of blocks needed to store table records

e_x

number of index entries for an index level,

where x = the level of the index (1 = first, 2 = second, etc.)

bfr

blocking factor

bfr_i

index blocking factor

May see bfr_is and bfr_id – These help differentiate between sparse and dense bfrs

V

size of the ordering key field

t

number of levels needed on multi-level index

t_b

total number of blocks needed to support multi-level index

bfr =

floor(B/R)

b =

ceil(r/bfr)

R_i = (primary, sparse)

V + P

R_i = (secondary, dense)

V + P_R

bfr_i =

floor(B/R_i)

e_x =

b_x-1

e₁ = (sparse)

b

e₁ = (dense)

r

b₁ = (sparse)

ceil(e₁/brf_is)

b₁ = (dense)

ceil(e₁/brf_id)

b_x =

ceil(e_x/bfr_is) (for x > 1)