Basic T-SQL Commands

Hi All,

Hope you all are doing good.

In this article, I will explain you about T-SQL commands.

How to check SQL Server Version?

Command:	select @@version;
Output:	Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) (Hypervisor)

How to create a Database?

Syntax:	CREATE DATABASE {your-database-name}
Example:	CREATE DATABASE Stores

How to drop a Database?

Syntax:	DROP DATABASE  IF EXISTS [database_name-1,database_name-2,....,database_name-n];
Example:	DROP DATABASE IF EXISTS Stores;

NOTE − Be careful while doing this operation because by deleting an existing database would result in loss of complete information stored in the database.

Make sure you have the admin rights before dropping any database.


Dropping a database, deletes the LDF and MDF files.

You cannot drop a database when it is currently in use. You get an error stating - Cannot drop database "Stores" because it is currently in use. So, if other users are connected, you need to put the database in single user mode and then drop the database.

Alter Database Stores Set SINGLE_USER With Rollback Immediate

With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.

How to create a Schema?

Syntax:	CREATE SCHEMA {your-schema-name}
Example:	CREATE SCHEMA Production

How to drop a Schema?

Syntax:	DROP SCHEMA IF EXISTS [schema_name];
Example:	DROP SCHEMA IF EXISTS Production;

How to create a Table?

Syntax:	CREATE TABLE {Table-Name} (    Column-1 Type,    Column-2 Type,    .    .    .    Column-n Type );
Example:	CREATE TABLE Production.categories (    category_id INT,    category_name VARCHAR (255) );

How to create a Table with Primary Key and Identity?

Syntax:	CREATE TABLE {Table-Name} (    Column-1 Type IDENTITY (1, 1) PRIMARY KEY,    Column-2 Type,    .    .    .    Column-n Type );
Example:	CREATE TABLE Production.categories  (     category_id INT IDENTITY (1, 1) PRIMARY KEY,    category_name VARCHAR (255) NOT NULL );

How to delete a table?

Syntax:	DELETE FROM table_name WHERE [condition];
Delete all records from a table
Example:	DELETE FROM Production.categories;
Delete all records from a table which matches to given condition
Example:	DELETE FROM Production.categories WHERE ID = 6;

How to drop a table?

Syntax:	DROP TABLE [database_name.][schema_name.]table_name_1,                         [database_name.][schema_name.]table_name_2,                        ...                        [database_name.][schema_name.]table_name_n;
Example:	DROP TABLE IF EXISTS Production.categories;

Thanks for reading my article.