MSSQL TABLES

Tables: Combination of Rows and Columns. SQL Server 2008R2 can have up to two billion tables per database and 1,024 columns per table.

The maximum number of bytes per row is 8,060. A table can contain a maximum of 999 non-clustered indexes and 1 clustered index.

-Table: Can be Create, Alter, Drop, and Truncate. [Difference Delete and Truncate Statement]

-Columns: Column Name, Data Type, Default Value, Identity [Seed, Increment, NOT FOR REPLICATION, ROWGUIDCOL], COLLATE, NULL | NOT

NULL, CONSTRAINT, TEXTIMAGE_ON.

-System Table: System tables contain information about all the objects, data types, constraints, configuration options, and resources available to the SQL Server. One subset of tables exists only in the Master database and contains system wide information; another subset of tables exists in every database (including Master) and contains information about the objects and resources belonging to that database. All system tables start with the three characters sys and all system tables have an ID number less than 100.

-Database Constraints: A constraint is a restriction; It ensures that your data meets certain data integrity rules. Four types of Data Integrity:

• Entity integrity: For individual rows and interested in a particular row, dealing with PRIMARY KEY and UNIQUE constraints.

• Domain integrity: For one or more columns. A column or set of columns meets particular criteria. The domain is the column.

• Referential integrity: When a value in one column match the value in another column — in either the same table or a different table.

• User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

-Constraints Types: PRIMARY KEY Constraints, FOREIGN KEY, UNIQUE Constraints, DEFAULT Definitions, Allowing Null Values, NOT Null, Rules.

-Table Types: Partitioned Table (data is horizontally divided), Temporary (Local & Global) and System Tables (configuration of the server).

-Table statistics: Statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. Statistics can be creates/updates automatically or manually. Statistics can be enable/disable (On/Off) on database level. Can be creating for Single Column, Multiple Column or Computed Column.

Leave a Reply

Your email address will not be published. Required fields are marked *