Tuesday, October 13, 2009

How to make database offline or online



Database is made offline to move its physical files. There can be many ways to make a database offline. But there are three main methods which are used frequently to make the database offline. These methods are given below:-


1)With the help of Alter database Command:-

We can make the database offline or online with the help of the Alter database command. The Alter Database command to make the database offline is :-

    ALTER DATABASE database name SET Offline

If we want to make the database online we can use the following Alter Database command:-

  ALTER DATABASE database name SET Online

2)With the help of the Db_options:-

 We can also use the db_options command to make a database offline or online.To make a database offline we can use the following command:-

sp_dboption databasename ,'offline',true

To make the database online we can use the following command:-

sp_dboption databasename ,'offline',false

3)With the help of Sql server management studio:-

We can also use the Sql server management studio to make a database offline as shown in the given figure.
To make database offline we have to follow steps show in fig 1 and to bring back the database online we needs to follows the step shown in fig 2:-


Fig1



Fig2





DMCA.com

Monday, October 12, 2009

Different ways to create a table and insert data into the table

Tables can be defined as the structure which contains the data in the database. There can be many ways to create table and to insert data into the table. Some of the ways to create a table is given below:-

1)Creation of a table with the help of a create statement

For example, suppose we want to create a table called tbl_student then the syntax of creating this table is given below:-

CREATE TABLE tbl_Students( [Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) ,
[Lastname] [nvarchar](200) ,
[Email] [nvarchar](100)
)

Let us suppose we want to insert data into the table tbl_students, then we can use the insert statements to insert data into the table.

The insert statement used for inserting the data is given below:-

Insert into tbl_students(Firstname,lastname,email)
select 'Vivek','Johari','Vivek@abc.com' union all
select 'Pankaj','Kumar','Pankaj@abc.com' union all
Select 'Amit','Singh','amit@abc.com'

Here we can insert multiple rows into the table with the help of a single query mentioned above.

Another way to insert the values into the table tbl_students is :-

insert into tbl_students(Firstname,lastname,email)
Values('Manish','Kumar','manish@abc.com')

Insert into tbl_students(Firstname,lastname,email)
Values('Abhishek','Singh','abhishek@abc.com')

Insert into tbl_students(Firstname,lastname,email)
Values ('Uma','Sharma','Uma@abc.com')

Here, whenever we need to insert a row into the table ,we have to use a seperate insert statement.

The result of the above Sql queries can be verified with the help of the following query:-

SELECT * FROM tbl_students

Result:-









Note:- We don't need to insert data into the column Studentid since it is defined as the Identity column and whenever a record is inserted into the table, sql server automatically insert value into this column.

2)Creation of table with the help of another table.

2.1) Suppose we want to create a table tbl_studentinfo which contains a subset of the columns (studentid, Firstname, Lastname) of the table tbl_student then we can use the following query.

Select studentid, Firstname, Lastname into tbl_studentinfo from tbl_students

This query will create a table tbl_studentinfo having columns studentid, Firstname, Lastname and it contains data of these columns from the table tbl_students. This can be verify with the help of the given below query.

SELECT * FROM tbl_studentinfo

Result:-









2.2)Suppose we want to create a table which is exactly the copy of a given table then we can also use the following Sql query

SELECT * INTO tbl_studentscopy FROM tbl_students

The table tbl_studentscopy created by the above Sql query will contains exactly the same data as tbl_students. This can be verified with the help of the query given below:-

SELECT * FROM tbl_studentscopy

Result:-










There is another interesting way to insert the values into a table with the help of another table. For example, suppose we have a table named as tbl_Studentsdemo whose structure is given below:-

CREATE TABLE tbl_Studentsdemo( [Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) ,
[Lastname] [nvarchar](200) ,
[Email] [nvarchar](100)
)
Now if we want to insert values into the table from the table tbl_students, then we can use the following sql query to insert the data into the table.

Insert into tbl_Studentsdemo(Firstname,lastname,email)
SELECT Firstname,lastname,email FROM tbl_students

The above query will insert all the data from the table tbl_students into the table tbl_Studentsdemo. Again we can verified it with the help of the given query:-

SELECT * FROM tbl_Studentsdemo

Result:-

DMCA.com

Monday, October 5, 2009

Difference between Primary key and Unique key



Both Primary key and Unique key enforce uniqueness of the column on which they are defined but still there are some differences between them. The differences are defined below:-

1)There can be only one Primary key possible in a table but there can be many unique keys possible in a table.

Suppose there is a table named tbl_primarykey whose structure is given below:-

CREATE TABLE tbl_primarykey (studentid INT NOT NULL PRIMARY KEY, Studentname nvarchar(255), email nvarchar(50), PhoneNo nvarchar(10))

Here studentid is defined as the primary key. Now since primary key is already defined on the column studentid, no other primary key can be defined on the table tbl_primarykey

Again, suppose there is another table named tbl_UniqueIndex whose structure is given below:-

CREATE TABLE tbl_UniqueIndex(studentid INT NOT NULL UNIQUE , Studentname nvarchar(255), email nvarchar(50), PhoneNo nvarchar(10))

Here Unique constraint is defined on the column studentid. We can also add another unique constraint on another column studentname of the table tbl_UniqueIndex.

ALTER TABLE tbl_UniqueIndex ADD CONSTRAINT Const_Unique UNIQUE(studentname)

The above query shows that there can be multiple unique constraints can be defined on a single table but on a single table only one primary key can be defined.

2)Primary key do not allow NULL values but a Unique key allow one NULL value.If we try to insert NULL value into the primary key, we get an error message. 

For example, if we try to insert NULL value into the column studentid of the table tbl_primarykey, we get the following error:-

INSERT INTO tbl_primarykey (studentid,Studentname,email)
VALUES(NULL,'Vivek' ,'vivek@mail.com')

Error:-

 


On the other hand if we want to insert NULL values in the column on which Unique constraint is defined we can do it.

For example, we can insert the null value in the column studentname of the table tbl_UniqueIndex.

INSERT INTO tbl_UniqueIndex (studentid,Studentname,email)
VALUES(1,NULL,'abc@mail.com')

Note:- We can only insert one NULL value inside the column on which a Unique Constraint is defined.

For example, if we try to insert more than one NULL value in the column Studentname of the table tbl_UniqueIndex, we get the following error:-

INSERT INTO tbl_UniqueIndex (studentid,Studentname,email)
VALUES(2,NULL,'virender@mail.com')

Error:-




3)When a Primary key is created, a clustered index is made by default but if an Unique key is created, a non-clustered index is created by default.

If we check all the indexes made on the table tbl_primarykey and tbl_UniqueIndex, then we can found out that a clustered index is created by default when a Primary key is created and a Non_Clustered Index is created by default when a Unique Constraint is created on the table.

To check the indexes made on the table tbl_primarykey, we can use the following query:-

sp_helpindex tbl_primarykey




To check the indexes made on the table tbl_UniqueIndex, we can use the following query:-

sp_helpindex tbl_UniqueIndex





You can also get more information about Primary Key and Unique Constraint from the link given below:-



DMCA.com