Microsoft SQL Server - Transact-SQL Keywords: CREATE

	Transact-SQL Keywords: CREATE

Introduction

The CREATE keyword is used to create an object.

Creating a Database

Here is an example of creatinge a database:

CREATE DATABASE NationalCensus;

The formula to specify a file when creating a database is:

CREATE DATABASE DatabaseName
ON PRIMARY
( NAME = LogicalName, FILENAME = Path )

Here is an example:

CREATE DATABASE NationalCensus
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf')
GO

The formula to specify a log file is:

CREATE DATABASE DatabaseName
ON PRIMARY
( NAME = LogicalName, FILENAME = Path.mdf )
LOG ON
( NAME = LogicalName, FILENAME = Path.ldf )

Here is an example:

CREATE DATABASE NationalCensus
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:\Exercises\NationalCensus.ldf')
GO

Creating a Login

The formula to programmatically create a login is:

CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }

<option_list1> ::= 
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=  
    SID = sid
    | DEFAULT_DATABASE =database    
    | DEFAULT_LANGUAGE =language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL =credential_name <sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name<windows_options> ::=      
    DEFAULT_DATABASE =database
    | DEFAULT_LANGUAGE =language

tand ead in order to access the database or resource is called a login.

To let Microsoft SQL Server generate skeleton code for you, on the Standard toolbar, click New Query to get a text editor. In the Template Explorer, expand the Login node, drag Create SQL Login Must Change Password and drop it in the empty text editor:

-- =================================================
-- Create SQL Login Must Change Password template
-- =================================================

CREATE LOGIN <SQL_login_name, sysname, login_name> 
	WITH PASSWORD = N'<password, sysname, Change_Password>' 
	MUST_CHANGE,
	CHECK_POLICY = <check_policy,ON or OFF, ON>;
GO

If you are creating the login with code, add the FROM WINDOWS flag:

CREATE LOGIN See Below
FROM WINDOWS

When Windows Authentication is used or if you add the FROM WINDOWS flag, when the Connect to Server dialog box comes up, the user can select Windows authentication and click Connect.

If the use

Creating a User

The formula to programmatically create a user is:

CREATE USER user_name 
    [ { { FOR | FROM }
      { 
        LOGIN login_name 
        | CERTIFICATE cert_name 
        | ASYMMETRIC KEY asym_key_name
      } 
      | WITHOUT LOGIN
    ] 
    [ WITH DEFAULT_SCHEMA =schema_name ]

Here is an example:

CREATE USER JohnYamo
FOR LOGIN rkouma;
GO

If the name is in more than one word, include it in square brackets. Here is an example:

CREATE USER [Paul Martin Souffrance]
FOR LOGIN rkouma;
GO

Here is another example that also grants a permission:

CREATE USER [Raymond Kouma]
FOR LOGIN rkouma;
GO
USE Exercise1;
GO

GRANT CREATE FUNCTION
TO rkouma;
GO

Creating a Function

The basic formula to create a function is:

CREATE FUNCTION FuntionName()
RETURNS DataType
AS
BEGIN
    RETURN Expression
END

Here is an example

CREATE FUNCTION GetFullName()
RETURNS nvarchar(100)
AS
BEGIN
	RETURN N'Doe, John'
END