Basic of Data Manipulation Lanaguage (DML).

 The Data manipulation Lanaguage (DML) basically is the manipulation of the data. In the DML there are the following operation we can perform.

• INSERT
• DELETE
• UPDATE
• SELECT

The INSERT statement basically used to insert the data row into data table. The syntax of the INSERT is:-

    INSERT into table_name(column_name) values('value which you want to store')

The DELETE statement is used to delete the data from data table. The syntax of the DELETE is:-

    DELETE from table_name

 

The above query delete all records from the table. if we want to delete the particular record from the table then we use the following query. 

     DELETE from table_name where <condtiion>

The UPDATE statement is used to update the record in the table. The syntax of the UPDATE is:- 

     UPDATE table_name set column_Name=value  

The above query update all the column records in the table. if want to update the particular row then we use the following query.

    UPDATE table_name set column_Name=value UPDATE table_name set column_Name=value

The SELECT statement is used to select and retrive the records from table. The syntax of the SELECT is:-

    SELECT * from table_name 

The above query retrive all the information from the table. if we want to retrive the particular column with some condition then we use the following query.

    SELECT column_name1,column_name2 from table_name where <condtiion>

What are triggers in sql server?

 A trigger is a special type of stored procedure that automatically run when an event occurs in the database server. 

Types of Triggers:-

There are three types of triggers in sql server.

• DML Triggers

• DDL Triggers

• Logon Triggers

DML triggers run when a user tries to modify data through the Data Manipulation Language(DML) event. DML event are INSERT, DELETE or UPDATE statement on a table or view. These triggers fire when any valid event fires, whether table row affected or not.

DDL triggers run in response to a variety of Data Definition Language(DDL) events. DDL event are CREATE , ALTER or DROP Statements.

Logon triggers fires in response to the LOGON event that’s raised when session is being established. 
 

Different DATEDIFF Functions in sql server

 SELECT DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Update query in sql server using join

 

What is output of the following query?

SELECT case when null=null then 'Hello' else 'World' end as Result

Output : World

Note: NULL means unknown. hence tow unknown null can't be same or equal.

Difference between Delete, Drop and Truncate in SQL Server

Delete is used to delete the one row or more than one rows or all rows from data table in database.

Syntax:
DELETE FROM TABLE table_Name;

After execute this query all rows are removed from table.

DELETE FROM TABLE table_Name WHERE column_Name=?

After execute this query particular row removed from table.

Drop is used to delete whole database or just a table.
The Drop statement destroys the objects like an existing database,table,index,or view.

Syntax:
DROP object object_Name

Examples:
DROP TABLE table_name;
table_name: Name of the table to be deleted.

DROP DATABASE database_name;
database_name: Name of the database to be deleted.

Truncate statement is a Data Definition Language(DDL) operation that is used to mark the extents of a table for deallocation (empty for reuse).

Truncate basically used to remove the whole data from table but remain the structure of table for future use.

Syntax:

TRUNCATE TABLE table_Name;

TRUNCATE Vs DROP

Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.
Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure.
Table or Database deletion using DROP statement cannot be rolled back, so it must be used wisely.

How to create stored procedure for insert data into table in sql server?

create procedure sp_InsertUserType(
@type_Name nvarchar(200),
@isActive bit )
as 
begin
INSERT INTO tblUserType(type_Name,isActive) VALUES(@type_Name,@isActive)
end
go


exec sp_InsertUserType 'Admin','true'

What is stored procedure in sql server

A stored procedure in SQL Server is a group of one or more Transact-SQL statements.

Stored procedure syntax:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute Stored procedure :

EXEC procedure_name

A network related or instance specific error occurred while establishing a connection to sql server. the server was not found or was not accessible.

A network related or instance specific error occurred while establishing a connection to sql server. the server was not found or was not accessible.

Go to start menu and search the service and open it.

Search MS sql server and click on start

Now Your error has been removed .

Get the identity of last inserted record in sql server using c#?

There are different ways to do this.

SELECT @@IDENTITY

It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()

It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

C# code for getting last inserted Id

public int getLastInsertedAdId()

        {

            try

            {

                conn.Open();

  string sql = "SELECT IDENT_CURRENT('tblAdTestBriefDetail')";

//SELECT SCOPE_IDENTITY()  

//SELECT @@IDENTITY

                cmd = new SqlCommand(sql, conn);

                return  System.Convert.ToInt32(cmd.ExecuteScalar());

        

            } catch(Exception ex)

            {

            }

            finally

            {

                conn.Close();

            }

            return 0;

        }

sql server important query

1. Reset the auto generated filed in sql server .

  DBCC CHECKIDENT (table_name, RESEED, 0)


2. Add New column in sql server database by query.


ALTER TABLE  table_Name ADD column_Name datatype NULL ; 


3. Rename Column name in sql server.

sp_rename 'tableName.old_column_Name', 'New_Column_Name', 'COLUMN';

4. Change datatype of particular column in sql server

ALTER TABLE table_Name  ALTER COLUMN Column_Name New_datatype(like bit,datetime);

5. delete particular column from table in sql server

ALTER TABLE table_Name DROP COLUMN column_Name(which you want to delete or remove from table)

6. Remove/Delete more then one column from table in sql server


ALTER TABLE table_Name DROP COLUMN Column1,Column2,...

7. How to active current window for write query

USE Database_Name

8. How to get list of tables in particular database.

SELECT
  *
FROM
  kls.INFORMATION_SCHEMA.TABLES;
GO

9. How to convert date time into string.


select convert(varchar, GETDATE(), 0) as date_In_String

10. How to local server in desktop

(LocalDb)\MSSQLLocalDB  ( Use this as server name)

11. How to change data type of column in sql server.

alter table tbl_Name alter column column_Name new_Datatype

12.  How to copy data from one table to Another table in sql server

insert into tbl_Name1(column_1,column_2) select column_1,column_2 from tbl_Name2


13. How to copy structure of table from one table to another table

select * into tblNewMailingAddres from tblMailingAddress

14. How to get structure of table

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = 'mytabel'