TableCardinality

Question

What does it means TableCardinality in execution plan? I am looking at data base tuning performances

Thanks

Answer 1

Cardinality is the number of UNIQUE values for a given table / column (so it's not surprising that is equal to the number of entries in the primary key index as that index is most likely clustered). The cardinality of the index or table is usefull to sql server as it allows the query optimiser to make educated guesses about the possible best plans to use when referencing that table.

When the optimiser has to take your sql code and work out what to do with it, it will consider alternative plans before settling on the one that it will use to actually retrieve the data. In most real world cases the number of possible plans is too large for sqlserver to calculate the absolute best plan via sampling all possibles so the optimiser will use statistical analysis to determin a "good enough" plan.

Cardinality is one of the metrics it uses to work out such a plan as it may use this to derermine which physical joins to use (hashmaps or loops for example)

Answer 2

Table Cardinality, in the context of the Execution Plan, is normally the same as the number of rows in the table per the latest statistics on that table.

Source: see http://support.microsoft.com/kb/195565 and search for "table cardinality" - you will see this note: "NOTE: In this strictest sense, SQL Server counts cardinality as the number of rows in the table."

Also, I checked a number of tables in various queries, and in each case, TableCardinality was equal to the number of rows in the table, or in some cases, the rowcnt value in sys.sysindexes for the primary key for that table.