sql - How to calculate the Num_Key_Cols for a Non-Clustered Index with included columns in it? -

i'm trying estimate index size using information provided @ msdn website.

let consider table "table1" 3 columns in it. columns listed below,

1. id int, not null
2. marks int, not null
3. submitdate date, not null

initially, have created clustered primary key on id column , planned create non-clustered index "id" "index key column" as, "marks" , "submitdate" columns used "included columns" in index.

based on above plan, trying estimate non-clustered index key size before creating it. while going through msdn site , have lot of confusions clarified. there 4 steps estimate nonclustered index key size , at first step, 1.2 , 1.3 explains about, how calculate num_key_cols, fixed_key_size, num_variable_key_cols , max_var_key_size. @ 1.2 , 1.3, should calculate based on index key type, have clustered index key or not , have included columns or not; seems confusing. can me based on info have provided sample table (table1) , non-clustered index key structure create.

in case, have included columns, index key column primary key, , columns not null fields. how calculate index size it?

thanks in advance.

initially, have created clustered primary key on id column , planned create non-clustered index "id" "index key column" as, "marks" , "submitdate" columns used "included columns" in index.

i wouldn't this. making id both primary key (i.e. unique) , clustering key of table (with 3 columns), there little point adding further nc index on id - clustered index adequate.

if had large number of (on page) columns in table there reason add nc index on id included columns on (marks, submitdate) nc index density higher. not case here.

some clarification of msdn nc sizing link:

• direct index columns need considered in levels of tree.
• include columns present in leaf nodes of tree
• num_key_cols = num_key_cols + 1 needed when sql server adds 4 byte uniquifier if clustering key isn't unique. you've clustered primary key, unique, not +1.

remember empirical measurements on real data

not tables , indexes have fixed column widths - many have variable length index columns (n)varchar, in case actual storage consumed table , indices highly dependent on average lengths of such fields.

in case, suggest creating table , populating approximate data , measure actual data storage tools sp_spaceused , sys.dm_db_index_physical_stats, e.g.:

select * sys.dm_db_index_physical_stats (db_id(),             object_id(n'dbo.table1'), null, null , 'detailed'); 

(page size 8192)

edit

just illustrate, if have in place:

create table table1 (     id int identity(1,1),     marks int not null,     submitdate date not null );  alter table table1 add constraint pk_table1 primary key clustered (id); 

then there no point in doing well:

create nonclustered index ix_table1 on table1(id) include (marks, submitdate) 

since clustered index seek / scan on id performantly nc index - double storage requirement.

as aside, note in general the clustered index key included in non-clustered indexes automatically, , not need explicitly added non clustered indexes.