Saturday, January 26, 2013

Creating a Database and Tables in SQL Server

This blog will guide you how to create a database and tables in SQL Server. The audience of this blog are those who don't have enough experience in SQL Server yet. For those who are very keen in using SQL Server, please skip this blog.

If you have your SQL Server installed in your PC then we are ready to go. Otherwise, please visit the How to Install SQL Server 2008 blog to guide you how to install it.

Creating a Database

Now we are expecting that you already installed SQL Server 2008 Express Edition in your PC. First, you have to open the Microsoft SQL Server Management Studio by clicking the icon from your Desktop. If it has not placed in the Desktop, then please click the Start button All Programs->Microsoft SQL Server 2008->SQL Server Management Studio.

The SQL Server Management Studio looks like below.



By default, it will automatically display the Object Explorer. If you haven't seen the Object Explorer then please go to menu View->Object Explorer or press F8.

After that, click the Connect label located in the Object Explorer to connect in the available SQL Server instance you had created during the installation. See the image below the Connect to Server window.


There you see the Server type, Server name and Authentication type of connection. Usually if you're working locally you have to place the (local) or dot in the Server name. The server type usually Database Engine. If you're working locally or if you're working with windows authenticated environment, then leave the Authentication value to Windows Authentication. However, if you know you're connecting in the secure database you have to provide the authentication process credentials. In the Authentication field select the SQL Server Authentication and enter the Username and Password credential. Click Connect button and wait until the Object Explorer is filled with a new database connection instance.

Select the new database instance and expand it. Select the Databases and select New Database. See below the image.


After clicking the New Database menu, you will see the create database dialog. See below.


In the database name, please type the name of the database you wish to create. The most common name for the database is the actual name of you application (but it is up to you). If you wish to create an advance database, you can modify the Options (type of connection and its properties) and Filegroups to define what schema this database are part of. For your convenient let's just start in the basic functionality.

If you're done with your database name, click OK button. You will notice that the Object Explorer databases node has been filled with your newly created database. See below the image.


And that's all. You just learn how to create a SQL Database.

Creating a Table

If you follow our tutorial above weI created our own database named CodesDirectory. From there, we will start our process by expanding the CodesDirectory database and right-click from the Tables folder. Select New Table menu and it will show you the table designer. From that designer please follow our image below.


Once you're done with your designing, please save the table by pressing the Ctrl+S keys. It will show you a new dialog that prompts the table name. In our sample, we created it as User. See image below.


Now you will notice again that the CodesDirectory database Tables node will have a new entry named User. That is just the table we just created named User. In SQL Server, user has an option to create a database table by user interface or by SQL Text. If you wish to create a table by SQL Text, then you have to learn more in DDL (Data Definition Language) stuff.

Sample below is the way how to retrieve the corresponding SQL Text when creating a table.

Scripting table.


You can choose other option in the Script Table as menu if you wish. But in our case, we just displayed how the User table has been created via SQL Text. Below is the actual script how to create a User table.

USE [CodesDirectory]
GO

/****** Object:  Table [dbo].[User]    Script Date: 01/27/2013 13:49:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
      [UserID] [uniqueidentifier] NOT NULL,
      [Username] [nvarchar](32) NOT NULL,
      [Firstname] [nvarchar](32) NOT NULL,
      [Lastname] [nvarchar](32) NOT NULL,
      [Birthdate] [datetime] NOT NULL,
      [SerialNumber] [nvarchar](24) NULL,
      [CreatedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_UserID]  DEFAULT (newid()) FOR [UserID]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_CreatedDate]  DEFAULT (getutcdate()) FOR [CreatedDate]
GO

If you wish to create a database by SQL Text, you need to study more the DML vs DDL. The DML or Data Manipulation Language is an SQL Text in SQL Server that is being used for data manipulation operation. Basic keyword includes SELECT, UPDATE and DELETE. The DDL or Data Definition Language is the administrator task that manipulates around database schema. Basic keywords are CREATE, ALTER and DROP.

1 comment:

Place your comments and ideas