Category Archives: SQL-SERVER

Create New Database in SQL-Server Management Studio 2008.

Create New Database in SQL-Server

In this post i will explain you how to create new database in sql server and how to manages database in server. before we going to start create database, let’s understand basic concept of database.

What is Database?

Database : The Database is a collection of information which is well  organized, so we can easily manipulate the information. Manipulation means we can insert, retrieve, update and  delete information easily.

In computer language a database is a collection of information in structured / tabled organized. The place where we can manage our database like create, update and delete, the place known as Database Server.

The Database server is a collection of database. We  can say database server provide place to store database. There are many database server available in market for store data in tabled manner.

Database is a place where we can store our data for permanent, and we can use and manipulate our data as per our need. For create and manage database we need database server, the database server known as database management system.

Here, we will learn how to operate and run microsoft sql server (mssql). we are using sql server management studio for create and manipulate database. we use sql server for stored data in tabled manner and use that data in future. For create and manage database, these are the basic step to manage database.

Create New Database in SQL Server Management Studio 2008

Step 1 – Open SQL Server Management Studio 2008

Step 2 – Connect Server with Server Name

Step 3 – Explore Server Name tab and select database option

Step 4 – Right click on database and Select New Database option

Step 5 – Write New database name at database name field

Step 6 – Click  OK to create database successfully.

Step 7 – Now we can manage database to create table and stored procedure in created database.

Check video tutorials of create new database in sql server management studio 2008

let’s understand how the all above step actually  work in sql server management studio in detail.

Step 1 – Open and Connect the SQL Server Management Studio 2008.

Open SQL Server Management Studio and connect the server using server name.

If Authentication mode set to Windows Authentication then we do  not need username and password for connect server, bu if the authentication mode set to SQL Server Authentication then we need username and password for  connect the sql server.

Here, we set Authentication mode to Windows authentication so we do not need username and password to connect server.

sql server management studio 2008

Open and Connect SQL Server Management Studio 2008.

 Step 2 – Create New Database in SQL Server Management Studio 2008.

After connecting server we are logged in the server, Now Select the server name and expand it  to  see more option. Select Database folder option to create new database.

The Database folder store the many database in it. Now, Right click on Database folder Select New Database option.

Connect Server –> Database –> Right Click  on Database –> New Database

Create New database in sql server management studio 2008.

Create New database in sql server management studio 2008.

In below screen, write new database name, here we create new database name = “TestExample” and click OK button to create new database in sql server.

Create New database in sql server management studio 2008.

Create New database in sql server management studio 2008.

Step 3 – Create New Table in Database

After creating new database, for managing our data or records we need to create a table in created database.

Click on the newly created database “TestExample”, there is a Table option in database. For creating new table right click on Table and select New Table option.

Database Name –> Table –> Right Click on Table Folder –> New Table

Create New Table in SQL Server Management Studio 2008.

Create New Table in SQL Server Management Studio 2008.

For creating table we assign column name with datatype and size. The table contain many columns with appropriate datatype and size. Here, we create three columns in Table, The column ID, Name and City. The ID column must be integer datatype and rest of two name and city columns  are varchar() or nvarchar() datatype.

Create New Table in SQL Server Management Studio 2008.

Create New Table in SQL Server Management Studio 2008.

Step 4 – Assign Primary Key on ID (unique column)

The Primary Key is a most important concept of sql server. We can set primary key on particular column of table.

The Primary Key is a not null and unique column of table. In our example the ID column  is a unique  and not null column, so we can assign primary key on ID column like below.

If you assign a primary key just right click on ID column select Set Primary Key option. The primary key column is unique and not null.

Assign Primary Key on column in sql table.

Assign Primary Key on column in sql table.

After assign primary key on ID column, now our ID column values unique and it must have some value we can not leave id column value as null. There is a other facility provided by sql server is Auto increment, if the ID column is Primary key then we set the ID column to Auto increment means the value of ID column is taken automatically in sequence manner.

For set auto increment property go to Identity Specification option of ID column and set Is Identity = yes.

Select Primary Key Column –> Identity Specification –> Is Identity = Yes

Set Auto Increment Primary key in sql server 2008

Set Auto Increment Primary key in sql server 2008

Step 5 – Save Table to sql server

After creating column in table save table with any text name as your like for table name. We save below table with name UserMst. The UserMst is out sql table name which is created in TestExample database.

Create New Table in SQL Server Management Studio 2008.

Create New Table in SQL Server Management Studio 2008.

Step 6 – Modify Columns in existing table

Now, after creating table in sql, some time we need to add some new columns in existing table, for adding or deleting column in existing table select Design option of table.

Right Click on Table –> Design

Modify columns in existing table in sql server 2008

Modify columns in existing table in sql server 2008

For see the records of table or open table select the Edit Top 200 Rows option.

Right Click on Table –> Edit Top 200 Rows — > View Table Data See the records of table right click on table and select Edit Top 200 Rows option to open table or see the records of table.

Create New database in sql server management studio 2008.

Create New database in sql server management studio 2008.

Here, we can see the table with some data information.

Create New Table in SQL Server Management Studio 2008.

Create New Table in SQL Server Management Studio 2008.

 

Create Stored Procedure in SQL-Server.

Stored Procedure in SQL-Server

Stored procedure is nothing but the simple compiled sql statement.

Stored Procedure is a simple SQL Query in Specific format in SQL Server and we can call them from in our program or application instead of writing queries inside our application code.
Stored Procedure are pre-compiled sql statements which is stored in a SQL Server database.
Stored procedure is a simple SQL Statement, which is pre-compiled in sql.

Check video tutorial of create table and stored procedure in sql server 2008

Here, are  some sql step create stored procedure in sql server  management studio.

Step 1 –Open SQL Server Management Studio 2008

Step 2 –  Connect Server with Server Name

 Step 3 – Explore Server Name tab and select database option

Step 4 – Right click on database and Select New Database option

Step 5 – Write New database name at database name field

Step 6 – Click  OK to create database successfully.

Step 7 – Create New Table in Database

Step 7 – Go to Programmability –> Stored Procedure –> Right Click –> New Stored Procedure.

For create stored procedure, first create new database and create new table in database.

we will learn how to create stored procedure in sql server management studio with an example.

First Create Database –> Create Table — > Programmability — > Stored Procedures.

Here we have Database name = Example.

After creating new database create new table in database. We have here created new table with name “USERMST”.

For Create New Table Database –> Right Click on Tables –> New Table

Here we create table USERMST with three columns ID , Name and Surname. The first column ID is integer and we assign it Primary key and auto increment. The other two columns name and surname are nvarchar() datatype.

Create New Table in sql server

Create New Table in sql server

Now, Select Programmability — > Stored Procedure — > New Stored Procedure

create stored procedure in sql server

create stored procedure in sql server

Select the programmability option and right click on and select New Stored procedure.

create stored procedure in sql server

create stored procedure in sql server

 

give a name for new Select stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for SELECT statement.

create stored procedure in sql server

create stored procedure in sql server

 

give a name for new Insert stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for INSERT statement.

create stored procedure in sql server

create insert stored procedure in sql server

 

give a name for new Delete stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for DELETE statement.

create stored procedure in sql server

 

give a name for new Update stored procedure CREATE PROCEDURE [Name of the procedure]

here is the new stored procedure for UPDATE statement.

create stored procedure in sql server

create stored procedure in sql server

 

list of all stored procedure which we have already created above.

create stored procedure in sql server

create stored procedure in sql server

 

Copy one Table with Data to another Table in SQL-Server

Copy Table in SQL – Server Management Studio

Here we will learn how to create and copy new table from an existing table in sql server management studio.

Here is a syntax for copy data from one table to other table.

Here, OldTableName is our old created table  with some data.

and the NewTableName which is after created run the sql query.

Run above query in your sql server we can create new table with copy all the data.

In below screen we write Select * into Tbll from AddressBook, here Tbll is our new table which will be created after run this query and Addressbook is our old table which is created first in sql server. Here all the records are copies from Addressbook table to Tbll Table.

 

Copy Data from one table to other table in sql server

Copy Data from one table to other table in sql server

 

Copy Data from one table to other table in sql server

Copy Data from one table to other table in sql server

 

Copy Data from one table to other table in sql server

Copy Data from one table to other table in sql server

Video tutorial of sql server copy table.

CHAR, VARCHAR and NVARCHAR Data Type Difference.

CHAR, VARCHAR and NVARCHAR Data Type Difference

In this sql post i will example difference between three data types Char, Varchar and Nvarchar. Before understand differences data type , first we should know what is data type.

What is Data Type in SQL?

Data type is a classification of data, which variable may contain. We have to assign proper data type to variable at the time of declaration of any variable.

The data type depend on which type of data variable can store with it.

The Char Data Type:

We use Char Datatype for store fixed length of Characters.
We can declare Char datatype like:

@Name as Char(50);

@City as Char(10)

Here, We Declare @Name variable of Char(50), means here it will allocates memory for 50 characters  and for city variable we allocate memory up to 10 character. means in name variable we can store maximum 50 character and in city variable we can store maximum 10 character.

In above statement we have declared Name variable with size 50, it is not compulsory to insert 50 character, its define the maximum capacity of variable.

We declare here Char(50) and insert only 30 character of word then only 20 characters of memory will be used and other 20 characters of memory will be wasted.

The Varchar DataType:

Varchar means variable characters and it is used to store non-unicode characters.
It will allocate the memory based on number characters inserted.
Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.
It takes 1 bytes per Non-Unicode.
Example
DECLARE @Name AS VARCHAR(50) = ‘MEERA’
SELECT @Name AS Name, DATALENGTH(@Name) AS LengthResult:
Name        Length
MEERA      5

 

The Nvarchar DataType:

Nvarchar is used to store Unicode characters and it allows you to store multiple languages in database.
Nvarcahr datatype will take twice as much space to store extended set of characters as required by other languages.
It takes 2 bytes per Unicode/Non-Unicode
Example
DECLARE @Name AS NVARCHAR(50)= ‘MEERA’
SELECT @Name AS Name, DATALENGTH(@Name) AS LengthResult:
Name        Length
MEERA     10

How to Create Stored Procedure in SQL-Server?

How to Create Stored Procedure in SQL-Server?

Stored Procedure is write SQL Query in Specific format in SQL Server and call them from your application, instead of writing queries inside your program code.
Stored Procedures are precompiled Transact-SQL statements which is stored in a SQL Server database.
Stored procedure is a simple SQL Statement, which is precompiled.

STEP 1 – Open the SQL Server management studi0

STEP 2 – Create a New Database

STEP 3 – Expand the DataBase name, Select the Programmability folder —> Stored Procedure.

Stored Procedure in SQL - SERVER and ASP.Net

Stored Procedure in SQL – SERVER and ASP.Net

STEP 4 – Right Click on the Stored Procedure folder Select “New Stored Procedure” Option.

Stored Procedure in SQL - SERVER and ASP.Net

Stored Procedure in SQL – SERVER and ASP.Net

After Writing Stored Procedure Press F5 key to Execute / Save Stored Procedure.

Then Refresh the Stored Procedure Folder and you will see the new created stored procedure in  SQL Server.

Stored Procedure in SQL - SERVER and ASP.Net

Stored Procedure in SQL – SERVER and ASP.Net

If you want to changes in Existing Stored Procedure,
Just Right Click the Created Stored Procedure and Select Modify Option to change the Stored Procedure…

Create XML using SQL Query in SQL Server 2008

Here, we will learn how to create XML using Query:

SELECTTOP1000 [Code]
      ,[Name]
      ,[Price]
  FROM[Temp].[dbo].[Product]
  FORXML RAW ('Product'),
  ROOT ('Products');
Here SQL statement “FOR XML RAW” creates one XML node for every corresponding row in database and columns will be created as attributes of that XML node.
Results:
<Products>
<ProductCode="1"Name="Computer"Price="100.0000"/>
<ProductCode="2"Name="Mobile"Price="250.0000"/>
</Products>
SQL ELEMENTS create Elements instead of Attributes in xml
SELECTTOP1000 [Code]
      ,[Name]
      ,[Price]
  FROM[Temp].[dbo].[Product]
  FORXML RAW ('Product'),
ROOT ('Products'),
ELEMENTS;
RESULTS :
<Products>
<Product>
  <Code>1</Code>
  <Name>Computer</Name>
  <Price>100.0000</Price>
</Product>
<Product>
  <Code>2</Code>
  <Name>Mobile</Name>
  <Price>250.0000</Price>
</Product>
</Products>

 

What is Key in SQl-Server?

Unique Key
  • The field must be unique within it’s Column.
    Primary Key

  • A Combination of unique & not null.

  • A table has Only one Primary Key.

    Foreign Key

  • Define relationship between two Table.

  • Either one to one, one to many, many to many

    Candidate Key


  • A table may have more than one combination of columns that could uniquely identify the rows in a Table, each combination is a Candidate Key.
    OR
  • A candidate Key is one that can identify each rows of a table uniquely.

What is Stored Procedure in SQL-Server?

What is Stored Procedure in SQL-Server?

  • Stored Procedure is write SQL Query in Specific format in SQL Server and call them from your application, instead of writing queries inside your program code. 
  • Stored Procedures are precompiled Transact-SQL statements which is stored in a SQL Server database. 
  • Stored procedure is a simple SQL Statement, which is precompiled. 
  • SP Increase the performance of your application.
    Create a Stored Procedure in SQL-Server

    CREATE PROCEDURE [STUDENT_SELECT]
      (


        //here is your parameter for insert/update/delete purpose.
      )
      AS


     BEGIN


         SELECT * FROM STUDENTMST


     END

What is SQL?

SQL – Structured Query Language.

  • SQL stands for Structured Query Language.

  • SQL is a globally accepted standard language that is used to handle database related activities for creating database schemas, updating/inserting/deleting of data, retrievieng data, and managing user roles and database activities.

     

  • SQL is used to communicate with a database.

     

  • it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

How to Create a Table in SQL?

 Create a Table in SQL

 

  • Below is the code / syntax to create a table in SQL,

The SQL syntax for CREATE TABLE is

CREATE TABLE “Table_Name”(“column 1” “datatype_for_column_1”,
“column 2” “datatype_for_column_2”,
… )

  • Create Table for Student, the Table name StudentMst with below Column.

CREATE TABLE StudentMst
(
First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
)

  • If you want to specify the Default values for some column then,
CREATE TABLE StudentMst
(
First_Name char(50),
Last_Name char(50),
Address char(50) default ‘Meera Academy’,
City char(50) default ‘Modasa’,
Country char(25),
)

What is DDL Command in SQL-Server?

Data Definition Language (DDL)

    • DDL stands for Data Definition Language. 
    • DDL statements are used to define the database structure or schema. 
    • It is used to create and modify the structure of database objects in database.
    •  Examples

      CREATE, ALTER, DROP statements

      1. CREATE
        – For creating databases & database objects.

      Syntax for creating database:

      CREATE DATABASE DataBaseName


Create DataBase Students

               Syntax for creating Table:

  CREATE TABLE StudentsMst
(
StudentId int,|
StudentName varchar(50),
StudentCity varchar(50),
)

 2. ALTER
   – For altering/modifying the exsisting tables.

     Syntax for Altering/Adding columns:

  Alter Table TableName Add Column1 datatype,Column2 datatype,…..

     Example

Alter Table StudentsMst Add DateOfBirth datetime,SchoolName varchar(300)

   Syntax for changing column datatype:

   Alter Table TableName Alter Column ColumnName NewDataType

Example

Alter Table StudentsMst Alter Column StudentPincode BigInt

3. DROP
For delete objects from the database

Drop Table table1,table2,…
Example

Drop DataBase Students

What is DML Command in SQL-Server?

Data Manipulation Language(DML)

 

  • DML Stands for Data Manipulation Language. 
  • DML statements are used for managing data within schema objects. Some examples: 
  • SELECT – Retrieve/Select data from the database
  • This command is used to fetch/get a result set of records from a table.

    Syntax :- Select * from TableName

     

  • INSERT – insert data into a table
  • This command is used to add records to a table.

    Syntax :- Insert into TableName (Colmn1,Colmn2,…..) values (Value1,Value2,…..)

    Insert into StudemtMst (Name,Pincode) values (‘Meera’, ‘383315’)

     

  • UPDATE – updates existing data within the table 
  • This command is used to edit/Update the records of a table.

    Syntax :- Update Table TableName set Colmn1=’Values’
    Update Table StudentMst set Name=’Meera’ and Pincode=’383315′

     

  • DELETE – deletes records from the table. 
  • This command is used to remove/delete records from a table.

    Syntax :- Delete from TableName;
    Delete from StudentMst where ID=’5′