{"id":3457,"date":"2012-12-22T22:17:27","date_gmt":"2012-12-22T22:17:27","guid":{"rendered":"http:\/\/craftydba.com\/?p=3457"},"modified":"2012-12-24T03:05:42","modified_gmt":"2012-12-24T03:05:42","slug":"basic-training-tables","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3457","title":{"rendered":"Basic Training – Tables"},"content":{"rendered":"

\"\"<\/a>Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR<\/a>) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING<\/a>.<\/p>\n

The most basic part of a database is a TABLE which consists of COLUMNS. A important decision during the initial design is to choose the data types that will capture the information you want in the least amount of space. I did a good job covering the various data types supported by SQL Server.<\/p>\n

Now, I am going to talk about the two ways in which a table can be created via TSQL.<\/p>\n

To prepare for some up coming conversations, I am going to create a sample database named [TRAINING] that contains a sample schema named [PUBS]. I will be tearing apart the pubs sample database<\/a>, that shipped with Microsoft SQL Server 2000, for teaching purposes. The snippet below accomplishes these actions.<\/p>\n

--\r\n-- Create training database\r\n--\r\n\r\n-- Which database to use.\r\nUSE [master]\r\nGO\r\n\r\n-- Delete existing database\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'TRAINING')\r\n    DROP DATABASE [TRAINING]\r\nGO\r\n\r\n-- Add new database\r\nCREATE DATABASE [TRAINING] ON\r\nPRIMARY\r\n    ( NAME = N'TRAINING_DAT', FILENAME = N'C:\\MSSQL\\DATA\\TRAINING.MDF' ,\r\n    SIZE = 32MB , MAXSIZE = UNLIMITED, FILEGROWTH = 4MB)\r\nLOG ON\r\n    ( NAME = N'TRAINING_LOG', FILENAME = N'C:\\MSSQL\\LOG\\TRAINING.LDF' ,\r\n    SIZE = 8MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB );\r\nGO \r\n\r\n\r\n--\r\n-- Create pubs schema\r\n--\r\n\r\n-- Which database to use.\r\nUSE [TRAINING]\r\nGO\r\n\r\n-- Delete existing schema.\r\nIF EXISTS (SELECT * FROM sys.schemas WHERE name = N'PUBS')\r\nDROP SCHEMA [PUBS]\r\nGO\r\n\r\n-- Add new schema.\r\nCREATE SCHEMA [PUBS] AUTHORIZATION [dbo]\r\nGO<\/span><\/pre>\n<\/p>\n
First way to create a table is to use the CREATE TABLE<\/a>, ALTER TABLE<\/a> and DROP TABLE<\/a> statements are part of ANSI SQL<\/a>.  These statements allow a database developer make, change, and remove tables from the database.  We are going to create the [AUTHORS] table inside the [PUBS] schema.  If this table exists before hand, it will be dropped.  Also, I am going to alter the table to add a primary key constraint.  I will talk more about declarative data integrity in future articles.<\/p>\n
--\r\n-- Create authors table \r\n--\r\n\r\n-- Delete existing table\r\nIF  EXISTS (\r\n    SELECT * FROM sys.objects\r\n    WHERE object_id = OBJECT_ID(N'[PUBS].[AUTHORS]') AND\r\n    type in (N'U'))\r\nDROP TABLE [PUBS].[AUTHORS]\r\nGO\r\n\r\n-- Create new table\r\nCREATE TABLE [PUBS].[AUTHORS]\r\n(\r\n   au_id varchar(11) not null,\r\n   au_lname varchar(40),\r\n   au_fname varchar(20),\r\n   phone char(12),\r\n   address varchar(40),\r\n   city varchar(20),\r\n   state char(2),\r\n   zip char(5),\r\n   contract bit\r\n);\r\n\r\n-- Alter the authors table\r\nALTER TABLE [PUBS].[AUTHORS] \r\n   ADD CONSTRAINT PK_AUTHORS_ID PRIMARY KEY CLUSTERED (au_id);\r\n<\/span><\/pre>\n<\/p>\n
What use is a table without some data.  The code below inserts 5 rows into our newly built table.<\/p>\n
--\r\n-- Add five records\r\n--\r\n\r\nINSERT INTO  [PUBS].[AUTHORS]\r\n   VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',\r\n   '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1);\r\n\r\nINSERT INTO  [PUBS].[AUTHORS]\r\n   VALUES('213-46-8915', 'Green', 'Marjorie', '415 986-7020',\r\n   '309 63rd St. #411', 'Oakland', 'CA', '94618', 1);\r\n\r\nINSERT INTO  [PUBS].[AUTHORS]\r\n   VALUES('238-95-7766', 'Carson', 'Cheryl', '415 548-7723',\r\n   '589 Darwin Ln.', 'Berkeley', 'CA', '94705', 1);\r\n\r\nINSERT INTO  [PUBS].[AUTHORS]\r\n   VALUES('998-72-3567', 'Ringer', 'Albert', '801 826-0752',\r\n   '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1);\r\n\r\nINSERT INTO  [PUBS].[AUTHORS]\r\n   VALUES('899-46-2035', 'Ringer', 'Anne', '801 826-0752',\r\n   '67 Seventh Av.', 'Salt Lake City', 'UT', '84152', 1);\r\n<\/span><\/pre>\n<\/p>\n
Second way to create a table is to use the INTO<\/a> clause of the SELECT statement.  The table is created with the output results from the SELECT statement.  A column alias can be used to change the names of the destination columns in the new table.  All other characteristics such as indexes are not transferred during this operation.<\/p>\n
The following TSQL snippet creates a table [NAMES] which has three columns.<\/p>\n
--\r\n-- Create new table (SELECT INTO)\r\n--\r\n\r\nSELECT    \r\n   au_id as author_id,\r\n   au_lname AS last_name,\r\n   au_fname AS first_name\r\nINTO [PUBS].[NAMES] \r\nFROM [PUBS].[AUTHORS];\r\n<\/span><\/pre>\n<\/p>\n
One way to look at the details of these tables is to view them through the SQL Server Management Studio (SSMS).<\/p>\n
\"\"<\/a><\/p>\n
Another way to look at the details of the [AUTHORS] table is to use the sp_help<\/a> system stored procedure.<\/p>\n
\"\"<\/a><\/p>\n
Regardless of the way in which you create a table via TSQL, you must have been granted<\/a> the CREATE TABLE rights for the database otherwise a permission denied error will occur.  <\/p>\n
Next time, I will be talking about the nullability of COLUMNS in a table.  This constraint can be used to enforce data entry (NOT NULL) or allow empty values (NULL).  <\/p>\n
Table Examples<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic part of a database is a TABLE which consists of COLUMNS. A important decision during the initial design is to choose the data types that will capture the information you want in the least amount of space. I did a good job covering the various…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[64,50,53,63,31,52,54,62,12,15,678,522,29],"class_list":["post-3457","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-table","tag-create-database","tag-create-schema","tag-create-table","tag-database-developer","tag-drop-database","tag-drop-schema","tag-drop-table","tag-free-code","tag-john-f-miner-iii","tag-select-into","tag-sp_help","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3457","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3457"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3457\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3457"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3457"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}