{"id":533,"date":"2018-05-19T11:48:35","date_gmt":"2018-05-19T04:48:35","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=533"},"modified":"2020-04-11T20:13:28","modified_gmt":"2020-04-11T13:13:28","slug":"sql-server-create-table","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/","title":{"rendered":"SQL Server CREATE TABLE"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server <code>CREATE TABLE<\/code> statement to create a new table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='introduction-to-the-sql-server-create-table-statement'>Introduction to the SQL Server CREATE TABLE statement <a href=\"#introduction-to-the-sql-server-create-table-statement\" class=\"anchor\" id=\"introduction-to-the-sql-server-create-table-statement\" title=\"Anchor for Introduction to the SQL Server &lt;code&gt;CREATE TABLE&lt;\/code&gt; statement\">#<\/a><\/h2>\n\n\n\n<p>Tables are used to store data in the database. Tables are uniquely named within a <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-database\/\">database<\/a> and <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-schema\/\">schema<\/a>. Each table contains one or more columns. And each column has an associated <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-data-types\/\">data type<\/a> that defines the kind of data it can store e.g., numbers, strings, or temporal data.<\/p>\n\n\n\n<p>To create a new table, you use the <code>CREATE TABLE<\/code> statement as follows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> &#91;database_name.]&#91;schema_name.]table_name (\n    pk_column data_type PRIMARY <span class=\"hljs-keyword\">KEY<\/span>,\n    column_1 data_type <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    column_2 data_type,\n    ...,\n    table_constraints\n);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>First, specify the name of the database in which the table is created. The <code>database_name<\/code> must be the name of an existing database. If you don&#8217;t specify it, the <code>database_name<\/code> defaults to the current database.<\/li><li>Second, specify the schema to which the new table belongs.<\/li><li>Third, specify the name of the new table.<\/li><li>Fourth, each table should have a <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-primary-key\/\">primary key<\/a> which consists of one or more columns. Typically, you list the primary key columns first and then other columns. If the primary key contains only one column, you can use the <code>PRIMARY KEY<\/code>&nbsp;keywords after the column name. If the primary key consists of two or more columns, you need to specify the <code>PRIMARY KEY<\/code> constraint as a table constraint. Each column has an associated data type specified after its name in the statement. A column may have one or more column constraints such as <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-not-null-constraint\/\">NOT NULL<\/a><\/code> and <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-unique-constraint\/\">UNIQUE<\/a><\/code>.<\/li><li>Fifth, a table may have some constraints specified in the table constraints section such as <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-foreign-key\/\">FOREIGN KEY<\/a><\/code>, <code>PRIMARY KEY<\/code>, <code>UNIQUE<\/code> and <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-check-constraint\/\">CHECK<\/a><\/code>.<\/li><\/ul>\n\n\n\n<p>Note that <code>CREATE TABLE<\/code> is complex and has more options than the syntax above. We will gradually introduce you to each individual options in the subsequent tutorials.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='sql-server-create-table-example'>SQL Server CREATE TABLE example <a href=\"#sql-server-create-table-example\" class=\"anchor\" id=\"sql-server-create-table-example\" title=\"Anchor for SQL Server &lt;code&gt;CREATE TABLE&lt;\/code&gt; example\">#<\/a><\/h2>\n\n\n\n<p>The following statement creates a new table named <code>sales.visits<\/code> to track the customer in-store visits:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> sales.visits (\n    visit_id <span class=\"hljs-built_in\">INT<\/span> PRIMARY <span class=\"hljs-keyword\">KEY<\/span> <span class=\"hljs-keyword\">IDENTITY<\/span> (<span class=\"hljs-number\">1<\/span>, <span class=\"hljs-number\">1<\/span>),\n    first_name <span class=\"hljs-built_in\">VARCHAR<\/span> (<span class=\"hljs-number\">50<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    last_name <span class=\"hljs-built_in\">VARCHAR<\/span> (<span class=\"hljs-number\">50<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    visited_at DATETIME,\n    phone <span class=\"hljs-built_in\">VARCHAR<\/span>(<span class=\"hljs-number\">20<\/span>),\n    store_id <span class=\"hljs-built_in\">INT<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-literal\">NULL<\/span>,\n    <span class=\"hljs-keyword\">FOREIGN<\/span> <span class=\"hljs-keyword\">KEY<\/span> (store_id) <span class=\"hljs-keyword\">REFERENCES<\/span> sales.stores (store_id)\n);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this example:<\/p>\n\n\n\n<p>Because we do not specify the name of the database explicitly in which the table is created, the visits table is created in the <code>BikeStores<\/code> database. For the schema, we specify it explicitly, therefore, the visits table is created in the sales schema.<\/p>\n\n\n\n<p>The <code>visits<\/code> table contains six columns:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The <code>visit_id<\/code> column is the primary key column of the table. The <code>IDENTITY(1,1)<\/code> instructs SQL Server to automatically generate integer numbers for the column starting from one and increasing by one for each new row.<\/li><li>The <code>first_name<\/code> and <code>last_name<\/code> columns are character string columns with <code>VARCHAR<\/code> type. These columns can store up to 50 characters.<\/li><li>The <code>visited_at<\/code> is a <code>DATETIME<\/code> column that records the date and time at which the customer visits the store.<\/li><li>The <code>phone<\/code> column is a varying character string column which accepts <code>NULL<\/code>.<\/li><li>The <code>store_id<\/code> column stores the identification numbers which identify the store where the customer visited.<\/li><li>At the end of the table&#8217;s definition is a <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-foreign-key\/\">FOREIGN KEY<\/a><\/code> constraint. This foreign key ensures that the values in the <code>store_id<\/code> column of the <code>visits<\/code> table must be available in the <code>store_id<\/code> column in the &nbsp;<code>stores<\/code> table. You will learn more about the <code><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-foreign-key\/\">FOREIGN KEY<\/a><\/code> constraint in the next tutorial.<\/li><\/ul>\n\n\n\n<p>In this tutorial, you have learned how to use the SQL Server <code>CREATE TABLE<\/code> statement to create a new table in a database.<\/p>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"533\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/\"\n\t\t\t\tdata-post-title=\"SQL Server CREATE TABLE\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"533\"\n\t\t\t\tdata-post-url=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/\"\n\t\t\t\tdata-post-title=\"SQL Server CREATE TABLE\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial shows you how to use the SQL Server CREATE TABLE statement to create a new table in a specific schema of a database.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":100,"menu_order":51,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-533","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Server CREATE TABLE: Creating a New Table in the Database<\/title>\n<meta name=\"description\" content=\"This tutorial shows you how to use the SQL Server CREATE TABLE statement to create a new table in a specific schema of a database.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server CREATE TABLE: Creating a New Table in the Database\" \/>\n<meta property=\"og:description\" content=\"This tutorial shows you how to use the SQL Server CREATE TABLE statement to create a new table in a specific schema of a database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Server Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-11T13:13:28+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-basics\\\/sql-server-create-table\\\/\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-basics\\\/sql-server-create-table\\\/\",\"name\":\"SQL Server CREATE TABLE: Creating a New Table in the Database\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\"},\"datePublished\":\"2018-05-19T04:48:35+00:00\",\"dateModified\":\"2020-04-11T13:13:28+00:00\",\"description\":\"This tutorial shows you how to use the SQL Server CREATE TABLE statement to create a new table in a specific schema of a database.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-basics\\\/sql-server-create-table\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-basics\\\/sql-server-create-table\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-basics\\\/sql-server-create-table\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Basics\",\"item\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/sql-server-basics\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Server CREATE TABLE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/\",\"name\":\"SQL Server Tutorial\",\"description\":\"The Practical SQL Server Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlservertutorial.net\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server CREATE TABLE: Creating a New Table in the Database","description":"This tutorial shows you how to use the SQL Server CREATE TABLE statement to create a new table in a specific schema of a database.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server CREATE TABLE: Creating a New Table in the Database","og_description":"This tutorial shows you how to use the SQL Server CREATE TABLE statement to create a new table in a specific schema of a database.","og_url":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/","og_site_name":"SQL Server Tutorial","article_modified_time":"2020-04-11T13:13:28+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/","url":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/","name":"SQL Server CREATE TABLE: Creating a New Table in the Database","isPartOf":{"@id":"https:\/\/www.sqlservertutorial.net\/#website"},"datePublished":"2018-05-19T04:48:35+00:00","dateModified":"2020-04-11T13:13:28+00:00","description":"This tutorial shows you how to use the SQL Server CREATE TABLE statement to create a new table in a specific schema of a database.","breadcrumb":{"@id":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-create-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlservertutorial.net\/"},{"@type":"ListItem","position":2,"name":"SQL Server Basics","item":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/"},{"@type":"ListItem","position":3,"name":"SQL Server CREATE TABLE"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlservertutorial.net\/#website","url":"https:\/\/www.sqlservertutorial.net\/","name":"SQL Server Tutorial","description":"The Practical SQL Server Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlservertutorial.net\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/comments?post=533"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/533\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/pages\/100"}],"wp:attachment":[{"href":"https:\/\/www.sqlservertutorial.net\/wp-json\/wp\/v2\/media?parent=533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}