SQL Server Cafe

sql server index

2013-11-25T19:15:00.002-08:00

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 :

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

Disadvantage of using indexes

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

Type of index in sql server

Sql server support two type of index.

Clustered index
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

2013-11-24T17:32:00.003-08:00

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

2013-11-24T09:32:00.002-08:00

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

2013-11-23T20:09:00.000-08:00

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.

2013-11-22T15:04:00.000-08:00

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

2013-10-29T18:53:00.000-07:00

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

DML-(Data Manipulation Language)
DDL-(Data definition language)
DCL –(Data control language ).
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

2011-07-06T08:20:00.001-07:00

Transact-SQL using for SQL Server

2011-06-29T08:50:00.000-07:00

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')

creating identity table & insert values

2010-12-14T19:37:00.000-08:00

create table cricketers
(
    playerno int identity(100,2),
    playername varchar(50),
    country char(15),
    point int,
    postion varchar(50)
)

insert into cricketers values('sachin tendulcar','india','876','batsman')
insert into cricketers values('jaques kallis','south africa',798,'all rounder')

creating a table & insert values

2010-12-14T19:29:00.000-08:00

create table employee

(
   ecode char(10),
   ename varchar(30),
   city char(20),
   salary money,
   age int
)

insert into employee values ('e001','manash','kolkata',26000,26)
insert into employee values ('e002','saikat','london',26000,26)
insert into employee values ('e003','plash','bengalore',26000,26)
insert into employee values ('e004','sundip','mumbai',26000,26)
insert into employee values ('e005','sudip','santipur',26000,26)
insert into employee values ('e006','babon','kolkata',26000,26)
insert into employee values ('e007','aniban','london',26000,26)
insert into employee values ('e008','souvik','kolkata',26000,26)

creating a database

2010-12-14T19:19:00.000-08:00

create database student
on primary
(
    name='p_data',
    filename='d:\sql\p_data.mdf',
    size=10mb,
    maxsize=20mb,
    filegrowth=2mb
)

log on
(
    name='p2_log',
    filename='d:\sql\p2_log.ldf',
    size=12mb,
    maxsize=20mb,
    filegrowth=3mb
)

alter database ss
add file
(
    name='ndf7_data',
    filename='d:\sql\s\ndf7_data.ldf',
    size=11mb,
    maxsize=21mb,
    filegrowth=3mb
),
(
    name='ndf6_data',
    filename='d:\sql\s\ndf6_data.ldf',
    size=11mb,
    maxsize=21mb,
    filegrowth=3mb
)
go

SQL Server improving

2010-12-14T02:49:00.000-08:00

In the ten years since release of Microsoft's previous SQL Server product (SQL Server 2000), advancements have been made in performance, the client IDE tools, and several complementary systems that are packaged with SQL Server 2005. These include: an ETL tool (SQL Server Integration Services or SSIS), a Reporting Server, an OLAP and data mining server , and several messaging technologies, specifically Service Broker and Notification Services.