Creating Objects – SQL Server

Although the CREATE, ALTER, and DROP (DDL) commands appear to be extremely straightforward, their use is high and will be used frequently since they deal with all other database objects, including tables, views, stored procedures, functions, constraints, rules, synonyms, triggers, and more. In this post, we’ll talk about utilizing the CREATE command to CREATE OBJECTS in SQL Server.

CREATE DATABASE:

CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON
      [ PRIMARY ] <filespec> [ ,...n ]
      [ , <filegroup> [ ,...n ] ]
      [ LOG ON <filespec> [ ,...n ] ]
]
[ COLLATE collation_name ]
[ WITH <option> [,...n ] ]
[;]

<option> ::=
{
      FILESTREAM ( <filestream_option> [,...n ] )
    | DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias }
    | DEFAULT_LANGUAGE = { lcid | language_name | language_alias }
    | NESTED_TRIGGERS = { OFF | ON }
    | TRANSFORM_NOISE_WORDS = { OFF | ON}
    | TWO_DIGIT_YEAR_CUTOFF = <two_digit_year_cutoff>
    | DB_CHAINING { OFF | ON }
    | TRUSTWORTHY { OFF | ON }
    | PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='<Filepath to folder on DAX formatted volume>' )
    | LEDGER = {ON | OFF}
}

<filestream_option> ::=
{
      NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
    | DIRECTORY_NAME = 'directory_name'
}

<filespec> ::=
{
(
    NAME = logical_file_name ,
    FILENAME = { 'os_file_name' | 'filestream_path' }
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
)
}

<filegroup> ::=
{
FILEGROUP filegroup name [ [ CONTAINS FILESTREAM ] [ DEFAULT ] | CONTAINS MEMORY_OPTIMIZED_DATA ]
    <filespec> [ ,...n ]
}

The entire syntax for creating a database is listed above. In actuality, none of those parameters are mandatory. And some of the parameters are introduced in SQL Server 2012 onwards. If you wish to set the file name, disk location, file size, auto growth ratio, etc., you can do so.

The default settings for several of these parameters are used by SQL Server when you run CREATE DATABASE without the optional options.

Some commonly used parameters are listed below.

Example1:
USE master
GO
CREATE DATABASE dbSales
ON
   ( NAME = Sales_dat,
     FILENAME = ‘c:\program files\microsoft sql server\mssql\data\saledat.mdf’,
     SIZE = 10,
     MAXSIZE = 50,
     FILEGROWTH = 5 )
LOG ON
   ( NAME = ‘Sales_log’,  
     FILENAME = ‘c:\program files\microsoft sql server\mssql\data\salelog.ldf’,
     SIZE = 5MB,
     MAXSIZE = 25MB,
     FILEGROWTH = 5MB )
GO

If you do not want to utilize the parameters you can simply create the database as shown below.

Example2:
CREATE DATABASE dbSales

CREATE TABLE:
A table is more than simply columns and rows; it also needs to be described as to what kind of data it should contain, how much data it should hold, and whether it should permit or disallow data based on the given rules. Depending on the project you are managing, they are generally suggested. These aren’t necessary in order to make the tables, though.

Please take a look at the sample below.

Example1:
CREATE TABLE tbStudentDetails(
—-iStudentID INT,
—-vcStudentName VARCHAR(40),
—-tiGroupID TINYINT,
—-sdtApplicationDate SMALLDATETIME,
—-sdtJoinDate SMALLDATETIME,
—-vcFatherName VARCHAR(40),
—-vcAddress1 VARCHAR(20),
—-vcAddress2 VARCHAR(20),
—-vcCity VARCHAR(20),
—-bActive BIT)
GO

Example2:
CREATE TABLE tbStudentDetails(
—-iStudentID INT PRIMARY KEY,
—-vcStudentName VARCHAR(40) NOT NULL,
—-tiGroupID TINYINT REFERENCES stbGroups(tiGroupID),
—-sdtApplicationDate SMALLDATETIME DEFAULT ‘2016-06-13’,
—-sdtJoinDate SMALLDATETIME,
—-vcFatherName VARCHAR(40) NULL, vcAddress1 VARCHAR(20) NULL,
—-vcAddress2 VARCHAR(20) NULL, vcCity VARCHAR(20) NOT NULL,
—-bActive BIT NOT NULL DEFAULT 1)
GO

While we mentioned the data type and size in the first example, we only specified the constraints in the second. In the example, restrictions meant that there could be no null values, that a default value would be entered in the event that a column’s value was left blank, or that NULL would be used in its place.

CREATE VIEW:

A query determines the columns and rows of a virtual table called VIEW. The syntax to build a view of the data in one or more database tables is as follows.

Example:
CREATE VIEW [vwStudentPayInfo] AS
SELECT a.iStudentID, StudentName, PaidAmount, PaidDate
FROM tbStudents a
JOIN tbStudentPayments b ON a.iStudentID = b.iStudentID
WHERE YEAR(HireDate) = 2021;

CREATE INDEX:

The below is to create a simple or normal or nonclustered index. It can be created on a single column or more than one column. Please note that this index allows duplicate values.

Syntax:
CREATE INDEX index_name ON table_name (column1, column2, …);

Example:
CREATE INDEX ndx_AdmissionDate ON tbStudent(dtAdmissionDate);

The below is to create a clustered index. A clustered index is an index that specifies the physical arrangement of a database’s table records. There can only be one clustered index per table since there can only be one method that records are physically stored in a database table.

Syntax:
CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);

Example:
CREATE CLUSTERED INDEX ndx_StudentID_tbStudent ON tbStudent (StudentID);

The below is to create a unique index which means it doesn’t allow duplicate indexes. This can also be on single or multiple columns.

Syntax:
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, …);

Example:
CREATE UNIQUE INDEX unq_ndx_mobile ON tbStudent(countryCode, areacode, phone);

CREATE LOGIN:

This is to create a login to connect to the database server. This doesn’t mean any privileges on user databases. It is an identity that is used to connect to a SQL Server instance. Once created the Login is then mapped to a database user.

Example:
CREATE LOGIN DXB\S202202 WITH PASSWORD = ‘StrongPassw0rd1!’
MUST_CHANGE, CHECK_EXPIRATION = ON;

CREATE USER:

This is to create the users to access the user databases. These users are mapped to logins.

Example:
CREATE USER DXB\S202202 FOR LOGIN DXB\S202202
Once the user is created you can provide the necessary permissions to it.

Using CREATE statements, many more objects can be created such as CREATE ROLE, etc, and these will be covered in the next topics.

Please subscribe to receive notifications of new articles.