Sql server tutorial 03 Creating and Altering Tables - XADAT.COM
15772
post-template-default,single,single-post,postid-15772,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 03 Creating and Altering Tables No ratings yet.

Sql server tutorial 03 Creating and Altering Tables

    Learning Goals

  • Create SQL Tables
  • Specify Column Data Types
  • Create User-Defined Types
  • Specify Column Default Values
  • Specify Identity Column and Generation Options
  • Alter SQL Tables
  • Drop SQL Tables
  • Truncate SQL Tables

 

In an SQL environment, tables are the basic unit of data management. Most SQL
programming you do is related either directly or indirectly to those tables. As a result,
before you can insert data into your database or modify that data, the appropriate tables
must have been created or you must create them. The SQL standard provides four
statements that allow you to define, change, and delete table definitions in an SQL
environment. You can use the CREATE TABLE statement to add a table, the ALTER
TABLE statement to modify that definition, the DROP TABLE statement to delete the
table and its data from your database, and the TRUNCATE TABLE statement to delete the
data in the table while leaving the table definition intact. Of these four statements, the
CREATE TABLE statement has the most complex syntax. Not only is this because of the
various types of tables supported by SQL, but also because a table definition can include
many elements. However, despite these complexities, table creation is a fairly
straightforward process once you understand the basic syntax.

Create SQL Tables

As you might recall from Chapter 2, SQL supports three types of tables: base tables,
derived tables, and transient tables. Most base tables are schema objects that hold SQL
data. Derived tables are the results you see when you request (query) data from the
database. Transient tables are named tables that are implicitly created during the
evaluation of a query expression or the execution of a trigger.
In this chapter, you’ll be working with base tables. In fact, most of what you’ll be
directly working with throughout this book (as well as throughout your programming
career) are base tables; however, not all base tables are the same. Some are persistent
(permanent) and some are temporary. Some are schema objects and some are contained in
modules. All module base tables are also temporary tables. SQL supports five types of
base tables:

 

  • Regular persistent base tables A named schema object defined by a table definitionin a CREATE TABLE statement that specifies neither the WITH SYSTEM
    VERSIONING nor TEMPORARY options. Regular persistent base tables hold the
    SQL data that is stored in your database. This is the most common type of base table
    and is often what is being referred to when people mention base tables or tables. A
    regular persistent base table always exists as long as the table definition exists, and can
    be called from within any SQL session.

 

  • System-versioned tables A named schema object defined by a table definition that
    specifies the WITH SYSTEM VERSIONING option. System-versioned tables, which
    are part of the temporal features added to the SQL:2011 version of the SQL standard,
    contain both current and historical versions of table rows. I describe the temporal
    features of the SQL standard in Chapter 13.
  • Global temporary tables A named schema object defined by a table definition in a
    CREATE GLOBAL TEMPORARY TABLE statement. Although the table definition is
    a part of the schema, the actual table exists only when referenced within the context of
    the SQL session in which it was created. When the session ends, the table no longer
    exists. A global temporary table created in one session cannot be accessed from
    another SQL session. The contents are distinct within each SQL session.
  • Created local temporary tables A named schema object defined by a table
    definition in a CREATE LOCAL TEMPORARY TABLE statement. Like a global
    temporary table, the created local temporary table can be referenced only within the
    context of the SQL session in which it was created and cannot be accessed from
    another SQL session. However, a global temporary table can be accessed from
    anywhere within the associated SQL session, whereas a created local temporary table
    can be accessed only within the associated module. The contents are distinct within
    that module.
    Declared local temporary tables A table declared as part of a procedure in a
    module. The table definition is not contained in the schema and does not exist until
    that procedure is executed. Like other temporary tables, the declared local temporary
    table can be referenced only within the context of the SQL session in which it was
    created.

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

No Comments

Sorry, the comment form is closed at this time.