SQL Server Tables

In SQL Server, a table within a database is a structured set of data organized into rows and columns, designed to hold information in a relational way. While SQL Server adheres to many aspects of the ANSI SQL standard, it also includes proprietary extensions and functionalities that differentiate its tables from those strictly defined by the ANSI SQL Standard, including the latest 2023 version. One key differentiator is the use of Transact-SQL (T-SQL), SQL Server's proprietary extension of SQL. T-SQL adds unique features and syntax to table definitions and manipulations that may not be present in ANSI SQL. For example, T-SQL introduces special data types, such as `SQL_VARIANT` or `HIERARCHYID`, and table properties like `TEXTIMAGE_ON` for specifying the location of large text and binary data, which are specific to SQL Server.
SQL Server also offers advanced indexing options such as filtered indexes, columnstore indexes, and spatial indexes, which are designed to enhance performance and storage efficiency in specific scenarios. These indexing strategies might not be fully covered or standardized in the ANSI SQL 2023 specification, thus providing SQL Server tables with potentially more optimized data access paths. Furthermore, SQL Server integrates closely with other Microsoft technologies, offering features like table partitioning and data compression in ways that are optimized for the Windows operating system and the broader Microsoft ecosystem. These integrations can affect how tables are stored, accessed, and managed, diverging from the ANSI standard which aims to maintain database vendor neutrality.
SQL Server also extends beyond the standard in areas such as security and data recovery. Features like Transparent Data Encryption (TDE) and Always Encrypted are specific implementations for securing data at rest and in use, which may not be fully prescribed by ANSI SQL standards. Additionally, SQL Server's implementation of point-in-time recovery, using transaction logs, can influence how tables are managed and recovered after failure, offering capabilities that might not be explicitly defined in the ANSI standard. In summary, while a SQL Server table shares the fundamental concept of a relational table as defined by the ANSI SQL Standard, it differentiates itself through proprietary extensions, performance optimizations, integration with Microsoft technologies, and advanced security and recovery features. These differences are driven by the specific needs and capabilities of the SQL Server ecosystem and the applications it serves, beyond what is specified in the ANSI SQL Standard 2023.

Tables are the fundamental building blocks of a SQL Server database. You can think of a table as a spreadsheet containing rows and columns. Columns are sometimes referred to as fields; rows are sometimes referred to as records.
A column defines a specific attribute; a row supplies the data for that attribute.
For example, a table devoted to employees would have columns defining the specific attributes of an employee, such as employee number, name, and address. However, this does not necessarily mean that the company has employees. The company might employ only contractors, whose data is stored in another table. A table, then, will always have at least one column, but will not always have rows. The diagram below illustrates the relationship between rows and columns in a table.

When creating a table, there are a series of elements that can be customized, depending on the attributes of the table you are creating. These elements include:

1. Column definitions, up to 1024 columns
2. Indexes, both clustered and non-clustered
3. The file group where the table is stored
4. Attributes such as primary key, foreign key, and check constraints
5. Default values

You will lean how and why to define these elements as you proceed through this course. In the next lesson, you will learn about data integrity.
To create, modify, or delete a table, you must have the appropriate permissions:

Think of the word schema as meaning a complete description of a database in a formal language supported by the database management system. A schema will include a formal description of all tables included in the database, their relationships and constraints, and other information needed for creating or reconstructing the database. A schema should be a complete description of the structure of the database, but will say nothing about the actual data contained in it. I introduce the term here because, in the context of SQL Server Modeling, it can often be used more or less synonymously with the word model. Schema is more appropriately used in the database context, whereas model is used in the modeling context. But the two terms are closely linked in the context of the SQL Server Modeling framework.
In the next lesson, we will dive into the elements of a table.