SQL Basic - Constraints

Heap Tables
1. Heap and clustered indexes directly affect how data in their underlying tables are stored. Nonclustered indexes are independent of row storage.
2. When a table is first created, the initial storage structure is called a heap. Rows are inserted into the table in the order in which they are added. A table will use a heap until a clustered index is created on the table.

XML Index
1. Based on XML column.
2. For every node in an XML document an entry is made in the XML index. This information is persisted in internal tables.
3. Creating and maintaining XML indexes can be quite costly. Every time the index is updated, it needs to shred all of the nodes of the XML document into the XML index.
4. For example, in a library system, system list out all the books based on there author / title and now want to list out based on chapters exists in the books, then it should be based on chapter per book like wise in xml index sql server maintain node level info based on there clustered index on the table at internal tables

Primary Key Constraint
1. A Primary Key is a unique value that identify a record within a table.
2. No Null value is allowed in primary key column.
3. There can only be one primary key within a table.
4. Typically a primary key created on a single column, but it can be a composit primary key on multiple columns. include up to 16 columns for a composit primary key and also can not exceed 900 bytes

Unique Key Constraint
1. A Unique Key is as similar as primary key, except one exception, it can contain one null value.
2. There can be more than one unique key in a table.

Foreign Key Constraint: Define relational integrity between 2 tables, 2 tables can be logically connect with Primary Key (Parent Table) and foreign Key (Child Table)

Check Constraint: Check constraint is used to check a particular expression during insertation and updation data, During bulk insert or Heavy insertation or updation check constraint should be disable.

On Delete Cascade / On Update Cascade: that is inhancement of triggers, child table is automatically updated / deleted


System Tables for Indexes:

sys.Indexes: sys.Indexes provide information for all tables / index / table valued functions. That return information about type of index (Clustered . Non-Clustered, Heap)
sys.Index_Columns: sys.Index_Columns provides list of all key columns as well as included columns with in a index.
sys.xml_indexes: Return rows
sys.Column_Store_Dictionaries
sys.Column_Store_Segements