sql server index

sql index

An index is an internal table structure that sql server uses to provide quick access to rows of a table based on the values of one or more column.

Advantage of using indexes :

1. Improve the speed of the execute of queries.
2. Inforce uniqueness of data.
3. Speed up joins between table

Disadvantage of using indexes

1. Take time to c create an index
2. Take large amount of disk space to store data along with the original data source on the table
3. Gets update each time the data is modified.

Type of index in sql server

Sql server support two type of index.

1. Clustered index
2. None clustered index

Clustered index

• the data is physically sorted.
• Only one clustered index can be created per table

Non-clustered index

• The  physically order of the rows is not the same as the index order
• No-clustered indexes are typically created on columns used in join and where clause and whose value by modified frequently.
• SQL server creates non-clustered indexes by defaults when the create index command is given .
• There can be as many as 249 non-clustered indexes per table
• A clustered index should be created before a none-clustered index.
• A clustered index would need to be rebuilt it is built before a clustered index .
• Clustered index is create on primary key
• Non-clustered index create on foreign key

 Syntax for clustered index

Create [UNIQUE][CLUSTERED]
INDEX INDEX-NAME,
ON TABLE-NAME(COLUMN-NAME1)

Syntax for none clustered index
Create [UNIQUE][NONECLUSTERED]
INDEX INDEX-NAME,
ON TABLE-NAME(COLUMN-NAME1)

More example

At first create a table for testing a index
create table test
(
     id int primary key not null,
     name varchar(50),
     address varchar(70),
     descrip nvarchar(90)
)

Than after creating this table insert value
insert into test values(1,'ram','hgd','cat')
insert into test values(2,'dfs','aaa','cat dog')
insert into test values(3,'sdf','bbb','cat dog cow')
insert into test values(4,'dsf','ccc','cat dog cow')
insert into test values(5,'sdf','zzz','cat dog camale cow')
insert into test values(6,'eew','lll','cat dog camale cow')
insert into test values(7,'asd','ppp','ss')

then show the result

select * from test

by default clustered index create on primary key
You can check  run this command

sp_helpindex test

 

run this command for catering a clustered index

 create clustered index in_test

on test(id)

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