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

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.

How To Delete a null record

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())

Delete Duplicate rows from the table.

Delete Duplicate rows from the table.
Suppose there is a table called "EmployeeTable" which have some duplicate records.
There is a three way to delete the duplicate rows.First way to delete duplicate rows :

How To Copy Data and Structure of One Table To Another New Tables

select * into newtable from originaltable where 1=1

Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

Write a query to convert all the letters in a word to upper case

SELECT UPPER('test')

Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

Find duplicate rows in a table?

Find duplicate rows in a table? OR I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.

How to know how many tables contains empno as a column in a database?

Hi Friends ,i am writing this article  to know how many tables contains empno as a column in a database?

How To Find age from date of birth

Hi Friends i am writing this article to find the age from date of birth

Returning String Instead of NULL:

Consider a situation in which your database table contains NULL and you don't want to return NULL but some message. Like suppose you have a Person table and a Phone Table and a person does not have a phone number so we can easily return a message saying "No Phone Number Found" instead of returning a NULL.

How can I find the last day of the month?

Hi friends i am writing this article to find the last day of the month.I think it may useful to you.

What is a deadlock “GRANT” and “REVOKE’ statements in SQL Server

What is a deadlock in SQL Server?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.

Add default value to existing column (SQL)

In SQL Server there are two ways to add a column with a default value.



Add Default Value to Existing Column

Using SQL Data Definition Language (DDL) to Create Data Tables and Other Database Objects

Using the CREATE TABLE Statement to Create Tables

Tables are the primary structures used to hold data in a relational database. In a typical multi-user environment, the database administrator (dba) creates the tables that serve as the data stores for the organization's data. Users normally create their own temporary tables used to store data extracted from the main organizational tables.

What is meant cluster and Non cluster index?

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.

Difference between HAVING and WHERE Clause

Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

Find and Delete duplicate records in a table

Many times you can face problem of duplicate records in table.So How would you identify and delete duplicate records in a table?

Difference between Truncate and Delete in SQL

Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

Difference between stored procedure and function

Fundamental difference between Stored procedure vs User Functions:

• Procedure may return none or more values.Function must always return one value either a scalar value or a table.
• Procedure have input,output parameters.Functions have only input parameters.
• Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
• Functions can be called from procedure.Procedures cannot be called from function.
• Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
• Transaction management possible in procedure but not in function.

 Below are few differences between Stored Procedure and Function

1) Stored procedure are compiled for first time and compiled format is saved and executes compiled code when ever it is called. But function is compiled and executed every time it is called.

2) Function must return a value but in stored procedure it is optional.

3) Function takes one input parameter it is mandatory but stored procedure may take o to n input parameters.

4) Functions can be called from select statement, but stored procedures can not be called from select statement.

5) We can build logic in functions and we can even break complex logic in to methods.

6) We can use try catch statements in stored procedures but in functions we can not use.

7) We can not use insert,delete,update and create statements in functions but in stored procedures we can use those statements.

8 ) Functions can have only input parameters but stored procedure can have input and out put parameters

A stored procedure is a program (or procedure) which is 
physically stored within a database. They are usually written in 
a proprietary database language like PL/SQL for Oracle database 
or PL/PgSQL for PostgreSQL. The advantage of a stored procedure 
is that when it is run, in response to a user request, it is run 
directly by the database engine, which usually runs on a separate
database server. As such, it has direct access to the data it 
needs to manipulate and only needs to send its results back to 
the user, doing away with the overhead of communicating large 
amounts of data back and forth.


User-defined function
=====================

A user-defined function is a routine that encapsulates useful 
logic for use in other queries. While views are limited to a 
single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

1>Procedure can return zero or n values whereas function can return one value which is mandatory.

2>Procedures can have input,output parameters for it whereas functions can have only input parameters.

3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.

4>Functions can be called from procedure whereas procedures cannot be called from function.

5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6>We can go for transaction management in procedure whereas we can't go in function.

7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.