how to create table in sql server

Create table

The create table key word is used to crate a table in sql server database .
Sql server  create table syntax

CREATE TABLE table name
( column name1  data type(size),  
column name2  data type(size),  
column name3  data type(size),
)

Example

create table person
(
      p_id  int,
      firstname varchar(50),
      lastname varchar(50),
      address varchar(70),
      city  char(20)
)

Drop table

The drop  table key word is used to delete a table in sql server database .
Sql server  drop  table syntax

Drop table table name
Example
Drop table employee

Sql server create database

create database student

on primary

(

   name='st_data',--àit is use for logical name.

   filename='f:\mydata\st_data.mdf',--à it is used actual store data in the hardisk

   size=10mb,

   maxsize=20mb,

   filegrowth=2mb

),

(

   name='st_data1',

   filename='f:\mydata\st_data1.ndf',

   size=10mb,

   maxsize=12mb,

   filegrowth=3mb

)

log on

(

   name='st_data2',

   filename='f:\mydata\st_data2.ldf',

   size=12mb,

   maxsize=20mb,

   filegrowth=4mb

)

The create database process is allocating 10.00 MB on disk st_data for .mdf file.10 MB on disk st_data1 for .NDF file

And 12 mb on disk st_data2 for .LDF file .

• We can see this result

• After running this query . we can see this result

•  Go to database properties and select files .

• We can see this result 

  Step-1

  

  
  

  Step-2

   

  

Sql server database files

Sql server database have three type of file 

1) Primary data files
2) Secondary data files
3) log files

What are primary data files?

The primary data file is the starting point of the database
 primary data file also called .MDF.

What are secondary data files?

Secondary data files is optional data file. IT can be used for spreading data on the different storage media .
Secondary data files also called .NDF

What is log files?

The log files contain log information of the database  that is used to recover the data.

• Log file also called .ldf file
• Every database have at least one  log file .

• .MDF – primary database file
• .NDF- secondary data files
• .LDF- log file.  

 

what is database ?

Database a usually large collection of data organized specially for rapid search and retrieval as by a computer.

 Sql server supported two type of database 

•  system database 

Like 

Master database

The  master database maintain all the system level information for a sql server system. Such as logon accounts, link server, and system configuration settings.

Model database act as a template for creating user define database .

Msdb database

MSDB database is used  by sql server agent for auto backup, jobs and scheduling alerts .

  Temp db database

TEMDB database used for temporary user objects that are explicitly created, such as sql query handling .

• sample database

sql server sample database use for testing purpose.
Such as
Pubs , northwind --- It is use in sql server 2000
Adventure work --- it is use in sql server 2005,2008

•  user defined database 

   

 

T-SQL tutorial

Welcome to beginner to advance T-SQL tutorial. In this tutorial I have explained you about basic to advance T-SQL statement .This tutorial also help those person going to DBA(Database Administrator) in future .

What is  T-SQL language ?

T-SQL (Transact Structured Query Language ) is a non-procedural language for changing data in the database . T-SQL encompassed several components , such as

1. DML-(Data Manipulation Language)
   
2. DDL-(Data definition language)
3. DCL –(Data control language ). 
4. TCL (Transactional control Language).

DML-(Data Manipulation Language):-

DML is used to insert, update, delete store  and modify data from database table .

Example :-select ,insert, update ,delete,

DDL-(Data definition language):

DDL  is used to create and modify the structure of database objects from database.
Example : create , alter and drop .

DCL –(Data control language ):

DCL  is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Example : grant, revoke and  deny

TCL (Transactional Control language)

It is used for manage different transactions occurring within  a database .
Example : rollback, commit

 

create database

Transact-SQL using for SQL Server

create a table simple tableperson
 create table person
(
    p_id  int,
    firstname varchar(50),
     lastname varchar(50),
    address varchar(70),
    city  char(20)
)
select * from person
insert into person values (1,'saikat','ghosh','india','kolkata')
insert into person values (2,'ayon','dash','india','kolkata')
insert into person values (3,'anupom','saha','india','dunlop')
insert into person values (4,'saikat','dash','india','belghoria')
insert into person values (5,'saikat','ghosh','india','kolkata')