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:
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.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.
In this section, you will learn how to create and manage tables using 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
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
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
You can use SQL Server 2014 Management Studio to create and modify tables. In this section, we will cover this GUI tool.
Here are the steps to create tables with SQL Server 2014 Management Studio:
Databases
folder and then click on the Tables
folder and select New Table from the menu. This launches the table designer in SSMS 2014.Here are the steps to modify an existing database table with SQL Server 2014 Management Studio:
Databases
folder and then expand the Tables
folder.18.225.95.245