Sql Server Data Definition with DDL Statements






query

Image credit:en.wikimedia.org

Data Definition using Create Statement

The Data Definition using Create Statement is used to create a database, table, view, function, stored procedure, cursor etc. in MS Sql Server 2012 Environment

Creating a Database with Create Statement

Open up Sql Server MMC and type the following query in the Query Editor Window by selecting the New Query option on the top left corner of the Console.

Create Database myDB;
The database named as myDB will be created by executing the query by clicking the “!” button with red color. Refresh the current server instance and you can see myDB in the left pane.

Creating a Table with Create Statement

*Select the database myDB first.
* Open up Sql Server MMC and type the following query in the Query Editor Window by selecting the New Query option on the top left corner of the Console.

CREATE TABLE employeeTable
(
employeeID int,
firstName varchar(15),
lastName varchar(15),
address nvarchar(MAX),
zipCode int(15),

);

Execute the query and see the table employeeTable inside the left pane after refreshing.From the findings in the site http://www.razorsql.com/docs/sqlserver_create_table.html
I am showing you how well they have explained the syntax of writing Create Table Statements . Here is the excerpt from the above link:

The MS SQL Server Create Table Tool allows users to visually create tables. After entering in the table name and the number of columns, the tool allows the user to enter the following information for each column of the table:
Column Name
Column Type (for example, Integer, Char, Varchar, etc.)
Length or Precision
Scale (for decimal columns)
Nullability (whether or not the column accepts null)
Primary Key (whether or not the column is a primary key)
Unique (whether to add a unique constraint to the column)
Default Value (the default value that should be inserted when a null is attempted to be inserted into the column)
Identity (whether the column auto-increments)
Identity Start
Identity Increment
After entering the column information, the Create Table Tool can generate and/or execute the SQL needed to create the table.
Listed below is an example of the SQL the Create Table Tool generates for a sample MS SQL Server table.

CREATE TABLE sample.dbo.test_table
(col1 int NOT NULL,
col2 char(25),
col3 decimal(10,2),
col4 varchar(25),
col5 datetime,
PRIMARY KEY (col1),
UNIQUE (col2))

The above statement specifies col1 as Primary Key and col2 as Unique key.

The Unique number is often assigned with some value and it may consist of auto incrementing numbers as shown below:

CREATE TABLE employeeTable
(
employeeID int NOT NULL identity(1,1) Primary Key,
firstName varchar(15),
lastName varchar(15),
address nvarchar(MAX),
zipCode int(15),
);

“employeeID” column will start with 1 with first record and gets incremented by 1 on each new record insertion.

Create a View with Create Statement.

A View can be easily created by executing the statements like this:

CREATE VIEW employeeView AS
SELECT * FROM employeeTable ;


View is a result-set of the selected records from a particular table.

Create a Stored Procedure with Create Statement

A Stored Procedure can be defined as a pre-compiled set of sql statements, which when invoked, , gives the user, the desired result(s).
Stored Procedures are extensively used in application development such as Asp.Net or Form based
applications .

Syntax of Stored Procedure

Use DATABASE_NAME;
CREATE OR REPLACE PROCEDURE Procedure_Name (parameters and their types)
GO
AS
SET NOCOUNT ON;
STATEMENT;
GO



Working Example:

USE myDB;
GO
CREATE PROCEDURE proc_GetAllEmployees(@firstName varchar(15),@lastName varchar(15))
AS

SET NOCOUNT ON;
SELECT * FROM employeeTable WHERE firstName = @firstName AND lastName = @lastName;
GO

Click the “!” sign to compile and save.

{Triggers and Cursors will be discussed later }

ALTER AND DROP Working Examples
ALTER TABLE employeeTable
(
employeeID int NOT NULL identity(1,1) Primary Key,
firstName varchar(10), //length is changed from 15 to 10
lastName varchar(15),
address nvarchar(MAX),
zipCode int(15),
);

DROP TABLE employeeTable;


Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.
Syed Adeel Ahmed on FacebookSyed Adeel Ahmed on GoogleSyed Adeel Ahmed on InstagramSyed Adeel Ahmed on Twitter

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.