Creating and managing tables

After you have created the database, the next step is to create tables. Tables are objects that store and organize data within a database. SQL Server provides the following types of tables:

  • Temporary tables: These exist within the tempdb database. These tables do not exist permanently. Instead, temporary tables have a life and limited accessibility, which differ according to their type. You can create two types of temporary tables: local and global. The name of the local temporary table must begin with a single number sign (#) and the name of the global temporary table must begin with two number signs (##). Local temporary tables are only available in the user session that created the table. Therefore, SQL Server removes the local temporary table when the user session ends. On the other hand, global temporary tables are available for all user sessions after its creation. SQL Server removes the global temporary table once all user sessions that refer to it are disconnected.
  • System tables: These store data about SQL Server 2014 and its components. SQL Server does not allow you to directly update the data of system tables.
  • User-defined tables: These are standard tables that contain user data. You can create up to 2,147,483,647 tables per user database.
  • Partitioned tables: These are a type of user-defined tables whose data is horizontally divided into distinct units and spread across one or more filegroups in a database. Partitioned tables make the large tables and indexes more manageable because you can manage them separately. By default, SQL Server 2014 supports up to 15,000 partitions.
  • File tables: Since SQL 2012, the SQL Server database engine lets you save files and directories in a SQL Server database. The FileTable feature builds on top of SQL Server FILESTREAM technology. File table has a fixed schema, and every row in this table represents a file or directory. Files can be loaded in bulk and updated and managed in T-SQL like any other column. SQL Server also supports the backup and restore operations on file tables. File tables allow files and similar objects to be stored in the SQL Server database, but allow access to them as if they were stored in the filesystem. All this is possible without any changes to client applications.
  • Memory-optimized tables: SQL Server 2014 allows you to create memory-optimized tables within a database. It is one of the key new performance-related architectural enhancements to the SQL Server 2014 database engine. The benefit of memory-optimized tables is to improve the performance of OLTP applications, as all the data for memory-optimized tables resides in memory. All transactions on memory-optimized OLTP tables are fully atomic, consistent, isolated, and durable (ACID).

Creating and modifying tables

You can use either Transact-SQL DDL statements or SQL Server Management Studio to create and modify tables. In the following sections, we will discuss these options.

Creating and modifying tables with T-SQL DDL statements

In this section, you will learn how to create and manage tables using T-SQL DDL statements.

Creating a table with T-SQL DDL statements

We use the CREATE TABLE statement to create tables within a database. The following is the basic syntax for this command:

CREATE TABLE [[database_name.]schema.]table_name
(column_name data_type | [column_definition] |
[computed_column], [table_constraint])
[ON filegroup | partition_scheme | DEFAULT]
[TEXTIMAGE_ON filegroup|DEFAULT]
[FILESTREAM_ON partition_scheme_name | filegroup | default]
    [ WITH ( <table_option> [ ,...n ] ) ]
[;]

For example, enter and execute the following T-SQL code in the query editor to create a Book_Info table within a CH02_03 database:

USE [CH02_03];
GO
CREATE TABLE Book_Info
    (
      Book_ID SMALLINT ,
      Book_Name VARCHAR(20) ,
      Description VARCHAR(30) ,
      Price [SMALLMONEY] ,
      Author_ID [int]
    )
ON CH02_FG1;
GO

To create memory-optimized tables, we must first create a MEMORY_OPTIMIZED_DATA filegroup within a database. For example, to create a MEMORY_OPTIMIZED_DATA filegroup in the CH02_03 database, we enter and execute the following T-SQL code:

USE [master];
GO

ALTER DATABASE [CH02_03]
ADD FILEGROUP [CH02_FGMO]
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

Next, run the following code to add a database file to the CH02_03 database memory-optimized file group (CH02_FGMO):

USE [master];
GO

ALTER DATABASE [CH02_03]
ADD FILE (NAME = 'CH02_03_MemoryOptimized', FILENAME = 'C:SQLDataCH02_03_MO.ndf')
TO FILEGROUP CH02_FGMO
GO

Finally, enter and execute the following T-SQL code to create a memory-optimized version of the Book_Info_MO table within the CH02_03 database:

USE [CH02_03];
GO

CREATE TABLE Book_Info_MO
    (
      Book_ID SMALLINT NOT NULL,
      Book_Name VARCHAR(20),
      Description VARCHAR(30),
      Price [SMALLMONEY],
      Author_ID [int],
      CONSTRAINT [PK_Book_Info_ID] PRIMARY KEY
      NONCLUSTERED HASH (Book_ID) WITH (BUCKET_COUNT = 2000))
      WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

Modifying a table with T-SQL DDL statements

We use the ALTER TABLE statement to modify an existing table. The following is the basic syntax for this command:

ALTER TABLE [[database.]schema.]table_name
[ADD | ALTER | DROP column_information]
[ADD | DROP constraint_information [index_properties]]
[WITH CHECK | NOCHECK CONSTRAINT constraint_name | ALL]
[ENABLE | DISABLE TRIGGER trigger_name | ALL]
[SWITCH partition_information] [;]

For example, to add a Topic_ID column to the Book_Info table, we execute the following T-SQL code:

USE [CH02_03];
GO

ALTER TABLE [Book_Info]
ADD [Topic_ID] INT NOT NULL;
GO

Dropping a table with T-SQL DDL statements

We use the DROP TABLE statement to delete the table from the SQL Server database. The basic syntax for this is as follows:

DROP TABLE table_name

For example, enter and execute the following T-SQL code to drop the Book_Info_MO table from the CH02_03 database:

USE [CH02_03];
GO
DROP TABLE Book_Info_MO;
GO

Creating and modifying tables with SSMS 2014

You can use SQL Server 2014 Management Studio to create and modify tables. In this section, we will cover this GUI tool.

Creating a table with SSMS 2014

Here are the steps to create tables with SQL Server 2014 Management Studio:

  1. Launch SQL Server 2014 Management Studio.
  2. In Object Explorer, expand the Databases folder and then click on the Tables folder and select New Table from the menu. This launches the table designer in SSMS 2014.
  3. Use the Table, Properties, and Column Properties panes to define some basic information about the table, as shown in the following screenshot:
    Creating a table with SSMS 2014
  4. Click on the Save button.
  5. Type in the table name in the textbox on the Choose Name window and then click on OK to save the table.

Modifying a table with SSMS 2014

Here are the steps to modify an existing database table with SQL Server 2014 Management Studio:

  1. Launch SQL Server 2014 Management Studio.
  2. In Object Explorer, expand the Databases folder and then expand the Tables folder.
  3. Right-click on the table you want to modify and then select Design from the menu. This opens the table designer.
  4. After making changes to the table design, click on the Save button to save the changes.

Deleting a table with SSMS 2014

To delete a table, use the following steps:

  1. Launch SQL Server 2014 Management Studio.
  2. In Object Explorer, expand the Databases folder and then expand the Tables folder.
  3. Right-click on the table you want to delete and choose Delete from the menu. Click on OK to delete the table.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.225.95.245