SQL Server Table FAQ

A table is a logical structure to store data related to a single entity such as customer, product, order, etc.  Each table has one or more columns.  Each column should be configured with the appropriate data type and null setting. Review the CREATE TABLE logic.

A schema is a logical container within a database to grant permissions to particular objects.  By default, each SQL Server database has a dbo and sys schema.  Although user defined objects can be created in the dbo schema, it is generally recommended to create a separate schema for each logical application such as sales, inventory, accounting, etc.

A single record in a table that consists of multiple columns.

Data types are defined for columns in tables and parameters for stored procedures, functions and queries.  The data type determines the acceptable data for the column or parameters.  It is prudent to select the appropriate data type for your columns and parameters to minimize the amount of storage that is needed at a table level and avoid implicit or explicit conversion issues which could be a performance problem in the long term.  Common data types are INT, DATE, DATETIME, VARCHAR(), VARCHAR(max), CHAR(), etc. User defined data types can also be created.

Null is an unknown value.  A column in a table can be configured to allow a null value or not.

Additional Information:
Some Tricky Situations When Working with SQL Server NULLs
Dealing with a No NULL Requirement for Data Modeling in SQL Server

Referential integrity maintains consistent data from a primary key on one table to a foreign key on a related table.  For example, with an Orders table the primary key could be OrderID.  A related table could be OrderDetails, which should include the OrderID column from the Orders table.  On the OrderDetails table, the OrderID column would be the foreign key related back to the OrderID column from the Orders table.

A primary key is generally a single column, but could be two or more columns, that uniquely identifies a row in a table.  A table should have a primary key to uniquely identify each row.

A foreign key is generally a single column, but could be two or more columns, that relates to another table to ensure data is consistent.

SQL Server indexes are physical structures that are built to improve data access performance.  Without an index, a table scan will occur, which means the SQL Server Query Processor is scanning the table from the first to last row to complete the query.  This is an expensive operation with a significant amount of data and a significant user base.

SQL Server indexes should be chosen wisely based on the data access patterns.  Having too many or duplicate indexes is counterproductive and will impact the performance of INSERT, UPDATE and DELETE operations as well as excessive storage.  Indexes also need to be maintained (rebuilt or reorganized) on a regular basis, either weekly, monthly or quarterly.

SQL Server ships with numerous types of SQL Server indexes that all provide specific value based on the need.  The SQL Server indexes include:
Clustered Index
Non Clustered Index
Composite Index
Included Columns
Filtered Index
ColumnStore Index
Full Text Index
XML Index
Spatial Index
Heap Table

A SQL Server Clustered Index brings physical and logical order to a table.  There is a single SQL Server clustered index created per table.  Typical columns are a unique ID such as the Primary Key for the table or date column such as OrderDate.  It is a best practice to create a clustered index for most tables to improve data access performance when querying the table

One or more SQL Server Non Clustered Indexes can be created per table.  Non Clustered Indexes are generally used for range queries, Foreign Keys or on columns used in WHERE, ORDER BY and GROUP BY clauses to fulfill specific queries.  The more selective the data is in the column, the greater benefit the index will provide to the query performance.

A SQL Server View is a virtual table.  The code that comprises the View is a SELECT statement that accesses one or more tables via a JOIN statement.  A View can be used to simplify a complex query or to simplify reporting for power users.  Permissions for users or groups should be granted to Views. Check out these limitations.