Sql-server Database Learning notes-table

1. Tables and their properties

Table: Also known as an entity, is a collection of data that is stored in the same type.

Column (field): Also called a field, domain, or attribute, which forms the schema of a table, expressed as a property in one piece of information.

Row: Also known as a tuple (tuple) that stores specific bar data.

Code (KEY): Also known as the primary key, like a person's identity card number, is a unique character, representing the current identity of this data.

Foreign KEY (FK): This is the relationship that represents the association between a piece of information and other information.

2. Features of the table

The table is represented in the database as a two-dimensional table consisting of rows and columns. Rows are also called records in many cases, and columns become fields or domains.

Each field in the table corresponds to the description of an entity. There is no extra field to go in. Of course, this sentence is not absolute, sometimes considering the system's two development or system requirements change, may also deliberately leave one or two alternate fields.

In a table, the order of each row of data can be arbitrarily transformed, usually in the order in which the data is inserted. You can also sort the data by index, in short, the data row arrangement here does not affect the subsequent sequencing of the programming process.

But the data between rows is also fine and do not repeat, this is controlled by the primary key, because the primary key specifies that no repetition is allowed in a table.

As with the order of the peers, the order of the columns can be arranged arbitrarily, and the user can define up to 1024 columns for a table. In the same table, column names must be unique, and there are no restrictions between multiple tables. Each column must define the data type at the same time as defined. These are all controlled by the SQL Server system.

Table names are not allowed to be duplicated in the same database. This is the default, if you must repeat it is not no way, create a new schema, and then let the two data tables with the same name under different schemas. The default tables are in the DBO schema.

3. Types of tables

In SQL Server2008, you can divide a table into the following 4 types, as the table does.

1) Normal table

Ordinary tables, also known as standard tables, are the tables that are commonly used to store data in a database, the most commonly used objects, and the most important tables. So most of the tables we talk about are regular tables, and other tables have their own special uses.

2) partition Table

Partitioned tables divide data horizontally into tables of multiple cells that can be distributed across multiple filegroups in a database, enabling parallel access to the data in a cell. If the data volume of the table is very large, and the data is often accessed in different ways, you might consider establishing a partitioned table. In short, partitioned tables are primarily used to conveniently manage large tables, increasing the usage of data in these tables.

3) Temporary table

Temporary tables, as the name implies, are temporary tables that are created temporarily and cannot be persisted. Temporary tables are also divided into local temporary tables and global temporary tables. If the temporary table is created, it will persist until the instance of SQL Server is disconnected if it is not actively deleted. Another difference is that the local temporary table detachment creator is visible, while the global staging table is visible to all users and connections.

4) system table

The role of the system table is obvious, mainly used to store information about the SQL Server server configuration, database settings, users, schemas, and so on. Typically, it can only be used by a database administrator (DBA).

4. Creating and Modifying tables

1) Create a normal table

The simplest syntax for creating tables is as follows:

CREATE tabl table name (    column    data type    constraint or default value,    column    data type    constraint or default value,    ...)

2) Create a temporary table

Creating a temporary table is simple. It is said that the temporary table is divided into local temporary tables and global temporary tables, and the only difference between the creation of ordinary tables is the number of "#", the local temporary table added a "#" number, such as #student. The Global temp table is added with two "#" numbers, such as # #Student.

Demonstrates how to create a temporary table-create a table with a query:

Select  into #order from ordersgoSelect* from #  OrderGo

Note: Try not to use temporary tables unless you have to. You can generally use subqueries instead of temporary tables. Using temporary tables can be costly, and if you are programming with COM +, it can also cause a lot of trouble because COM + uses database connection pooling and temporary tables exist all the time, as mentioned earlier, and temporary tables persist until they are disconnected unless manually deleted. SQL Server provides alternatives, such as the table data type.

3) Create a partitioned table

What is a partition? To improve the scalability and manageability of large tables and tables with various access patterns.

Why use partitioning? Typically, tables are created to store information about an entity, such as customers or sales, and each table has only attribute information that describes the entity. A table that corresponds to an entity is easiest to design and understand, so you don't need to optimize the performance, scalability, and manageability of this table, especially if the table is not particularly large. So what exactly does it call a big table? The size of a very large database (VLDB) is calculated at hundreds of GB, even in terabytes, but the term does not necessarily reflect the size of the tables in the database. A large database is a database that cannot be run as expected, or a database that runs a cost or maintenance cost that exceeds a predetermined maintenance or budget requirement. These requirements also apply to tables, which can be considered very large if the activity or maintenance actions of other users limit the availability of the data. For example, a table can be considered very large if performance is severely degraded, or if the data is inaccessible for two hours per day, weekly, or even monthly maintenance. In these cases, periodic downtime is acceptable, but through better design and partitioning, it is often possible to avoid or minimize the occurrence of this situation.

In addition to size, tables with different access patterns can affect performance and availability when different sets of rows in a table have different usage patterns. Although usage patterns are not always changing (and this is not a requirement for partitioning), partitioning can further improve wall calendars, performance, and availability when usage patterns change. Also, for example, the data for the current month may be readable and writable, but data from previous months (which usually account for most of the table data) are institutional. The ability of the table to respond to user requests may be affected if the data usage changes similarly, or if the maintenance costs become unusually large as the number of read and write data in the table increases. Accordingly, this also limits the availability and scalability of the server.

In addition, if you use a large number of datasets in different ways, you often need to perform maintenance operations on static data. This can result in costly results, such as performance issues, blocking issues, backups (space, time, and operational costs), and may negatively impact the overall scalability of the server.

What can be helped by partitioning? When tables and indexes become very large, partitioning can divide the data into smaller, easier-to-manage parts, providing some help.

In addition, if a large table exists in a system with multiple CPUs, partitioning the table allows for better performance through parallel operations. By performing multiple operations on each parallel subset, you can improve the performance of large-scale operations in very large datasets, such as millions of of rows. Examples of performance improvements through partitioning can be seen from the aggregation in previous releases. For example, in addition to aggregating into a large table, SQL Server can process each partition separately and then aggregate the results of each partition. In SQL Server2008, queries that connect large datasets can benefit directly from partitioning, and in SQL Server2008, related tables (such as order and OrderDetails tables) that are partitioned into the same partition key and the same partition function are called aligned. When the optimizer detects that two partitions are joined together by cutting aligned tables, SQL Server2008 can connect the data in the unified partition before merging the results. This allows SQL Server2008 to use computers with multiple CPUs more efficiently.

3) Adding and removing columns

ALTER  table name    -- add column to table  Add column name 1 data type nullable, column name 2 data type nullable

ALTER TABLE Table name DROP COLUMN column name 1, column name 2

EXEC sp_help table name   -- view table information

4) Modify Columns

' table name. Column Name ',' new column name '    -- Modify column name

4) constraints

The constraint is to ensure the integrity of the data.

Database three major constraints: Entity integrity (primary key), referential integrity (foreign key), user-defined integrity

 alter  table  table name --  add one of the primary key methods (remember to delete the primary key first)  add  primary  key  (L column name) 

 create  table   references  table name (primary key name) 
    
     --
      Specify the foreign key association (Mode 1)  --  mode 2 using the CONSTRAINT keyword to create a foreign key constraint  constraint  foreign KEY constraint name foreign  key  (Foreign key column name) references   Main table name (ID))  

 alter  table  table name --  default constraint  add  constraint  default_name   "   column name 

 -- unique constraint specifies that a column or columns in a table cannot have the same two rows or more than two rows of data exist  create  table   table name (column name data type  unique  --  mode 1  constraint  constraint name unique  (column 1, Column 2) --  mode 2 ) 

-- Turn off constraint detection NOCHECK CONSTRAINT Constraint name NOCHECK CONSTRAINT  All -- turn on constraint detection CHECK CONSTRAINT Constraint name CHECK CONSTRAINT  All

Sql-server Database Learning notes-table