Types of Authentication Modes in Microsoft SQL Server

There are two types of Authentication modes in Microsoft SQL Server. These are following :

• Windows Authentication
• Mixed mode

Windows authentication mode

Windows authentication mode allows users to connect using Windows domain like NT/2000 account. The access to the Microsoft SQL server is controlled by Windows NT/2000 account or group used when logged in, means that Windows username and password are used to access the database on Microsoft SQL Server server.

Mixed mode authentication

Mixed mode authentication allows use of Windows credentials and the local Microsoft SQL server account to logged into Database.

DDL DCL and DML in Microsoft SQL Server

Data definition language

Data definition language (DDL) is used to define and manage all attributes and properties of a database in Microsoft SQL Server. DDL statements include following commands :

• CREATE
• ALTER
• DROP

Data Control Language

Data control language (DCL) is used to control permissions on database objects. Permissions are controlled by using following commands :

• GRANT
• REVOKE
• DENY

Data Manipulation Language

Data manipulation language (DML) is used to select, insert, update, and delete data in the objects defined with Data Definition Language.

Why Avoid Permitting NULL values in Microsoft SQL Server

NULL value in column in Microsoft SQL Server means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is also different from zero or "". 

We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Log Shipping in Microsoft SQL Server

Log shipping in Microsoft SQL Server is process used to automatically taking backup of the database and transaction files from one Microsoft SQL Server and then restoring them on a backup server. 

Log shipping keeps the two Microsoft SQL Server instances in sync with each other. If production server fails, then users simply need to be pointed to the backup server. 

Log shipping process consists of following three steps :

1. Backup transaction logs of the Production server.
2. Copy these logs on the backup server.
3. Restore the log on backup server.

Types of Temporary Table in Microsoft SQL Server

There are two types of temporary tables in Microsoft SQL Server. These are: 

1. Local temporary table
2. Global temporary table. 

Local Temporary Table

Local temporary table is available only to the current Db connection for current user and are cleared when connection is closed. Local temporary table can not be shared among multiple users.

Global Temporary Table

Global temporary table is available to any connection once created. They are cleared when the last connection is closed. Global temporary table can be shared by multiple user sessions.

Temporary tables are used to allow short term use of data in Microsoft SQL Server.