SQL Union Operator | SQL Union ALL Operator | Difference between Union and Union ALL Operators in SQL |

Introduction:

Here I will explain about SQL UNION and UNION ALL operators and differences between UNION and UNION ALL in SQL Server

Description

Union Operators are used to combine the result of two or more select queries into single result set.

SQL UNION Operator:

SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.

SQL Union Operator Syntax:

SELECT column1,column2 FROM table1 UNION SELECT column1,column2 FROM table2

Here one more thing we need to remember that is we can use Union Operator for the tables which is having same column names and same data types otherwise it will throw error like this

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Now I will explain with one example first design two tables in your tables like this

UserInfo

UserID	UserName	Location
1	Suresh	Hyderabad
2	Prasanthi	Hyderabad
3	Mahesh	Vizag

After that create another table and give name as UserDetails

UserID	UserName	Location
1	Suresh	Hyderabad
2	Nagaraju	Bangalore
3	Madhav	Nagpur

Now write the Union Operator Query to get all the user details from two tables like this

SELECT UserName,Location FROM UserInfo UNION SELECT UserName,Location FROM UserDetails

Resultant table will be like this

 

UserName	Location
Suresh	Hyderabad
Prasanthi	Hyderabad
Mahesh	Vizag
Nagaraju	Bangalore
Madhav	Nagpur

If you observe above resultant table it contains UserDetails with distinct records because Union Operator will return only distinct records. If we want all the records then we need to use UNION ALL Operator.

SQL UNION ALL Operator:

This operator is used in a situation like return all the records from the tables including duplicate values also.  

SQL UNION ALL Operator Syntax:

SELECT column1,column2 FROM table1 UNION ALL SELECT column1,column2 FROM table2

Result table

UserName	Location
Suresh	Hyderabad
Suresh	Hyderabad
Prasanthi	Hyderabad
Mahesh	Vizag
Nagaraju	Bangalore
Madhav	Nagpur

The main difference between Union and Union ALL operator is

Union operator will return distinct values but Union ALL returns all the values including duplicate values.

what is stored procedure in Sql server | what are the advantages of using stored procedures in sql server

Introduction:

Here I will explain about what is stored procedure is and advantages and disadvantages of stored procedures in sql server

Description:

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

Sample of creating Stored Procedure

USE AdventureWorks2008R2; GO CREATE PROCEDURE dbo.sp_who AS     SELECT FirstName, LastName FROM Person.Person; GO EXEC sp_who; EXEC dbo.sp_who; GO DROP PROCEDURE dbo.sp_who; GO

Advantages of using stored procedures

a)    a) Stored procedure allows modular programming. 

You can create the procedure once, store it in the database, and call it any number of times in your program. 

b)    b) Stored Procedure allows faster execution. 

If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times. 

c)     c) Stored Procedure can reduce network traffic. 

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

d)    d) Stored procedures provide better security to your data

Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

In SQL we are having different types of stored procedures are there

a)    System Stored Procedures

b)    User Defined Stored procedures

c)    Extended Stored Procedures

System Stored Procedures:

System stored procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be used to perform variety of tasks to support sql server functions for external application calls in the system tables 

Ex: sp_helptext [StoredProcedure_Name]

User Defined Stored Procedures:

User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database

Extended Stored Procedures:

Extended stored procedures are the procedures that call functions from DLL files. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.

SQL Server Indexes Tutorial | Different Types of Indexes in SQL Server | Difference between Clustered Indexes and Non-Clustered Indexes in SQL Server

Introduction:

Here I will explain about SQL Indexes and different types of indexes and advantages of indexes in SQL Server.

Description:

An index can be created in a table to increase the performance of application and we can get the data more quickly and efficiently. Let’s see an example to illustrate this point suppose now we are reading book in that I need to check the information for dbmanagement to get this information I need to search each page of the book because I don’t know in which page that word information exists it’s time taken process. Instead of reading the each page of book to get that particular word information if I check the index of book (Glossary) it is much quicker for us to get the pages which contains the information with dbmanagement word. By using second method we can save lot of time and we can get information in efficient way.

This same principle applies for retrieving data from a database table. Without an SQL Index, the database system reads through the entire table to locate the desired information. If we set the proper index in place, the database system first go through the index to find out where to retrieve the data, and then go to that location directly to get the needed data. This is much faster due to the SQL Index. Creating and removing indexes on table will not show any effect on application because indexes operate behind the scenes.

Syntax to Create SQL Index in table:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)

Example to create Index on table

CREATE INDEX SampleIndex ON UserInformation (UserName)

The above statement is used to create an index named “SampleIndex” on the “UserName” column in the “UserInformation” table

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

Example of creating SQL Index on multiple columns

CREATE INDEX SampleIndex ON UserInformation (UserName,FirstName)

To Drop Index on table use the below statement

DROP INDEX TABLE_NAME.INDEX_NAME

In SQL we are having two types of indexes are there

1)    Clustered Index

2)    Non-Clustered Index

 

Clustered Index

Only 1 allowed per table physically rearranges the data in the table to confirm to the index constraints for use on columns that are frequently searched for ranges of data for use on columns with low selectivity.

Non-Clustered Index

Up to 249 allowed per table creates a separate list of key values with pointers to the location of the data in the data pages For use on columns that are searched for single values For use on columns with high selectivity

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Delete Duplicate Records In Sql Server

DELETE
FROM emp
WHERE empid NOT IN
(
SELECT  MAX(empid)
FROM emp
GROUP BY empname,sal,address)

Which attribute is used in order that the method can be used as WebService ?

WebMethod attribute has to be specified in order that the method and property can be

treated as WebService.

What the different phase/steps of acquiring a proxy object in Webservice ?

Following are the different steps needed to get a proxy object of a webservice at

the client side :-

√ Client communicates to UDI node for WebService either through browser or

UDDI's public web service.

√ UDII responds with a list of webservice.

√ Every service listed by webservice has a URI pointing to DISCO or WSDL

document.

√ After parsing the DISCO document, we follow the URI for the WSDL document

related to the webservice which we need.

√ Client then parses the WSDL document and builds a proxy object which can

communicate with Webservice.

What is WSDL?

Web Service Description Language (WSDL)is a W3C specification which defines XML

grammar for describing Web Services.XML grammar describes details such as:-

√ Where we can find the Web Service (its URI)?

√ What are the methods and properties that service supports?

√ Data type support.

√ Supported protocols

In short its a bible of what the webservice can do.Clients can consume this WSDL and

build proxy objects that clients use to communicate with the Web Services. Full WSDL

specification is available at http://www.w3.org/TR/wsdl.