Basics of SQL server

This article will be helpful for newbies to know and learn the basics of SQL Server.

What is SQL?

SQL stands for Structured Query Language (pronounced as SEQUEL). Essentially, it's a language that allows communication with databases in order to manage all the data they contain.

For example, an application like Facebook holds a user’s profile information, including data about their friends and posts. To hold this data, a database system is used. SQL is a programming language that enables programmers to work with that data.

What is SQL Server?

SQL Server is a relational database management system, or RDBMS, developed and marketed by Microsoft. SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact-SQL).

T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capabilities of declaring variable, exception handling, stored procedure, etc.

SQL Server Management Studio (SSMS) is the main interface tool for SQL Server, and it supports both 32-bit and 64-bit environments.

Most of the people have to know SQL server query has designed for set based (declarative) .

SQL Commands

SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data. SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.

There are five types of SQL commands:

DQL (Select)

DML (Insert, Update, Delete)

DDL (Create, Alter, Drop, Truncate)

DCL (Grant, Revoke, Deny)

TCL (Commit, Rollback)

Database

A Database stores all the data information (like objects) and has the default data file and log file. The data file is called mdf, ndf…. (One-mdf,more than one ndf). The log file is called ldf (More than one ldf)

Databases in SQL server can be categorized into two types:

1. System databases

A system database will create by default when the SQL server installed.

2. User databases

A user database will create by the user.

SQL Server System Databases are :

1. Master

2. Model

3. Msdb

4. Tempdb

5. Resource (2005 onwards)

Master

• The Master database manages the SQL Server instance. It stores system configuration, logins details etc..

• In SQL Server 2005 onwards Master database stores only the logical information in the system objects residing in the sys schema.

• In the previous editions of SQL Server, the Master database physically stores all the system objects, (tables, Sprocs etc.) information.

• One of the main things is master and resource DB must be located in the same directory. Otherwise the server goes down

MSDB

• MSDB is the main database to manage the SQL Server Agent configurations.

• It provides some of the configurations for the SQL Server Agent service (such as jobs alerts etc..) and it stores database backup details.

Model

• Model is a template database for all user defined databases.

• So we could use the model as a template for other user databases (such as the recovery model, DB size, SPs etc.…) i.e. Whenever you create a new database the template will taken from the model database.

Resource

• Introduced in SQL Server 2005 to help in managing the upgrade and rollback of the system objects (service pack).

• Prior to SQL Server 2005 the system related data were stored in the master database.

• It’s a hidden and read-only database that is not accessible via SQL server management studio.

TempDB

• TempDB is a temporary database to store temporary tables like #local, ##global, table variables, cursors, work tables sorted in TempDB, etc…

• When the SQL Server instance is rebooted, the TempDB database is recreated every time.