MY.NET Tutorials: Microsoft Sql Server

How to Perform Some DML Commands with Single Stored Procedure in SQL Server

On 01:20:00 By RAMASHANKER VERMA // No comments:

Introduction:- There are two types of statements used in SQL Server
1.) DML(Data Manipulation Language):- Some Commands of DML are:

SELECT :-Retrieve information from the database.
INSERT:-Insert data into a table.
UPDATE:-Update Existing Records in a table.
DELETE:- Delete Records in a table.

2.) DDL (Data Definition Language) :- There are some Commands of DDL are:-

CREATE:-Create table in database.
ALTER:-Modify the structure of database.
DROP:- Delete the table in database.
TRUNCATE:-Remove all records from the table including all allotted space also.
COMMENT:- It is used for comment.
RENAME:- Rename the objects.

Here we will perform select, insert,Update and Delete commands in a single Stored Procedure in sql server.

How to implement Group By Clause concepts in SQL Statement

On 12:19:00 By RAMASHANKER VERMA // No comments:

Group By clause:-
When we run aggregated function in select query, we get the output on the basis of all the records but if we want that aggregated function should give the output on the basis of group and column then we can use Group By clause.

Before grouping the the records if we want to keep some conditions then we can specify WHERE Clause.
After Grouping the records if we want to some conditions then we can use 'having ' Clause.

There are some steps to implement this concepts which are as given below:-

How to Use After or For Triggers Concepts Using DML Statements in sql server 2008-2012

On 13:04:00 By RAMASHANKER VERMA // No comments:

Hi Friends ! Today , i will show you 'How to use For/After Triggers Using DML Statements in sql server'. Triggers concepts are more important for us because it is helpful to informed any changes (insert,delete,update or others) in database.For this concepts we will have to need two tables.Here i have created two tables such as student_details and student_count with different columns.When any user changes in student_details table's records then it (changes) will automatically update in student_count table with the help of trigger concepts.
I will explain three concepts of After /For triggers on sql database as given below:-

Insert Statement
Delete Statement
Update statement

1.) Insert statement:- In this ,when any user insert some records in student_details table then all changes will updated in student_count table using trigger trg_insert .There are some steps to implement this concepts as given below:-

Introduction of Triggers in SQL Server

On 23:14:00 By RAMASHANKER VERMA // No comments:

In sql sever,we can create four types of triggers.Which are given below:-

DML (Data Manipulation Language) Triggers
DDL(Data Definition Language ) Triggers
Logon Triggers
CLR Triggers

a.) DML(Data Manipulation Language) Triggers:- We can perform DML Triggers on the FollowingDML statements in sql server such as:

Insert statement
Update Statement
Delete statement
Stored Procedures

How to Attach .mdf or .sdf database file in SQL Server Management Studio easily

On 18:43:00 By RAMASHANKER VERMA // No comments:

Hi Friends ! Today, i am going to attached .mdf or .sdf database file in sql server.It is very process. You can easily attached your .mdf or .sdf database file in sql server .In previous tutorial,i have explained "how to add .mdf database in asp.net website".If you are facing any problem to add .mdf or .sdf file in website read here. Now I will add this .mdf database in my sql server.When you will attached your .mdf or .sdf database in sql server then you can perform all query operations (inset,delete,update etc) easily.You can easily take backup of your .mdf or .sdf database also. There are some basics steps to perform this task which are given below:-
Step 1:- First open your Computer Icon -->C:\Users\Ramashanker\Documents\Visual Studio 2010\WebSites\WebSite37\App_Data --> You will see .mdf file as shown below:-

Views in Microsoft SQL Server 2008

On 22:36:00 By RAMASHANKER VERMA // No comments:

A View is a logical or virtual table,which does not exists physically in the database.View is an object which contains 'select' statement.View is consider like as Virtual table.
We create a view for two purpose.

Security region --> If we don't want to show all the column's data of the table to an user then we generally create a view.
To make complex query simple.

I have already created the tables studentdetails and scourse in joins in sql server. I will explain view with the help of these tables.

Create view with two tables:- I am going to create a view with the help of joining of two tables.

Joins in SQL Server 2008

On 21:59:00 By RAMASHANKER VERMA // No comments:

Joins:- When we want to get the related data from the table or more than one table then we do joining of the tables.There can be different type of join.

Inner Join
Outer join
Self join
Cross join

Example:-

First I have created two table for implementing the all joins operations, which are given below:-

Studentdetails
scourse

Transaction in SQL Server

On 20:52:00 By RAMASHANKER VERMA // No comments:

If There are set of statements and we want either all statements should be executed or nor of them should be executed.We can keep those statements within Transaction. A Transaction insure that changes will be made or none of changes will be done.

Properties of Transactions:-

There are four properties of transactions.Which is known as Name of ACID.
1.) Atomicity:- In Atomicity each transaction should be a unique number,so that if transaction is failed or abort at any point ,we can easily Rollback.
2.) Consistency:- In Consistency.If our transaction is successfully committed then database changes should be done .
3. ) Isolation:- In Isolation,Each transactions should transparent and independent to each other.
4. ) Durability:- In Durability,result of each transaction should be persisted ,if in case system failure.

How to handle date in sql server 2008

On 03:57:00 By RAMASHANKER VERMA // 1 comment:

When we insert a date in a table,by default SQL Server accepts date in 'mdy' (Month-date -year) format. If we want to change 'mdy' Format to 'dmy' or other format then we can easily change .Which is as shown below:

Stored procedure and Function in MS Sql Server

On 13:58:00 By RAMASHANKER VERMA // No comments:

In this tutorial i will tell you"what is difference between stored procedure and function".But first i will tell you what is function and why we use function.
Function:-Function may contain a set of statement as stored procedure but generally we create function if there is some calculations which we can do frequently.
Example:

Stored Procedure in MS Sql Server 2008

On 20:42:00 By RAMASHANKER VERMA // No comments:

Stored Procedure is set of sql statement or pl/sql programming also.which is perform a specific task. when we compile the stored procedure then one object of the stored procedure will be created in the database.There is no need to compile stored procedure again and again.There are some differences between stored procedure and function .

Create a simple procedure:


create procedure prcselect
     as
select *from student

Delete and Truncate statement in Sql Server

On 18:38:00 By RAMASHANKER VERMA // 1 comment:

Hi friends Today i am going to explain,what is Delete and Truncate statement in Sql Server . Sql is the main element of any Database.If you want to understand full concepts (step by step) of Ms Sql Server then here .If you want o learn somethings in your life then patience is more necessary.If you understand all concept of Ms Sql Server then ado.net will very easy for you.
Delete and Truncate statement can be used to remove the data from the table.

How to copy data from one table to another table in SQL SERVER

On 23:27:00 By RAMASHANKER VERMA // 1 comment:

1. If we want to create new table containing same column and records as specify within existing table then following SQL command will be used:-

SYNTAX:-

select *into[new table] from [existing table]

Example:-
Here I have a student table (already build). we want to copy this table data to another new table(student4) by creating new table(student4) at run time .


select *into student4 from student

See the student4 records.

select*from student4

How to Add an Identity on Column in SQL SERVER 2008

On 02:43:00 By RAMASHANKER VERMA // No comments:

If we want to have a column where value should inserted automatically in incremented order, we can apply Identity on that column.By default we can not insert value manually in Identity column.

SYNTAX:-

Identity (<seed> < Incremented >)

There are some steps to know about identity column. which are given below:-

How to Create DEFAULT Constraints on column in SQL SERVER 2008

On 21:50:00 By RAMASHANKER VERMA // No comments:

The DEFAULT CONSTRAINT is used for default value, when the insert statement does not provide any specific value. DEFAULT CONSTRAINT are special case of column default.It is also used when we use any scripting code for the SQL Database.

There are some steps to Know about DEFAULT CONSTRAINT Which are given below:-

How to Create Check Constraints on Column in SQL SERVER 2008

On 18:22:00 By RAMASHANKER VERMA // No comments:

The check constraint is used for Domain integrity.It is used to insert the limited value in particular column.If we define a check constraint on a single column then it allow values for that column.

We can also apply check constraint on multiple column in the table.

There are some steps to know about check constraint on the table column.Which is given below

Create the Table column and Insert the Data without Sql Commands

On 19:08:00 By RAMASHANKER VERMA // No comments:

There are some steps please follow it:-
Step1:- Open your sql database->click Databases->System Databases->click master->Right mouse click on Table->click New Table.
see it:->

Data Integrity in Microsoft Sql Server

On 01:09:00 By RAMASHANKER VERMA // No comments:

These are more important concept of Microsoft Sql Server. Please Read it very Carefully.

Data Integrity:- The Consistency of data is known as data integrity. When we have large amount of data then we could not easily find the data and table from database. Because data were not consistence form.To remove this type of problem we use three integrity constraints. which are given below.

Microsoft Sql Server

On 23:55:00 By RAMASHANKER VERMA // No comments:

Sql Server is a relational database management system (RDBMS) developed by Microsoft that's designed for the enterprise environment.It is a software product whose primary function is to store and retrieve data as requested by other software application.The original sql server code was developed by sybase in the late 1980.
The first version of sql server was sql server 4.2.The latest version of sql server is sql server 2012.
Create Database Through Sql command:-
SYNTAX:-