Update dotnet: Difference Between UNION And UNION ALL

<script> alert("mohammed ")</script>

C# Code To check Date Format

NOTE :

Name Space for CultureInfor is  using System.Globalization;

Using syste.

********************************* START *****************************

//Example 1 - Validate Date for the format DD/MM/YYYY 
private bool ValidateDate(string stringDateValue)
{
    try
    {
        CultureInfo CultureInfoDateCulture = new CultureInfo("fr-FR");
        DateTime d = DateTime.ParseExact(stringDateValue, "dd/MM/yyyy", CultureInfoDateCulture);
        return true;
    }
    catch
    {
        return false;
    }
}

//Example 2 - Validate Date for the format MM/DD/YYYY 
private bool ValidateDate(string stringDateValue)
{
    try
    {
        CultureInfo CultureInfoDateCulture = new CultureInfo("en-US");
        DateTime d = DateTime.ParseExact(stringDateValue, "MM/dd/yyyy", CultureInfoDateCulture);
        return true;
    }
    catch
    {
        return false;
    }
}

//Example 3 - Validate Date for the format YYYY/MM/DD 
private bool ValidateDate(string stringDateValue)
{
    try
    {
        CultureInfo CultureInfoDateCulture = new CultureInfo("ja-JP");
        DateTime d = DateTime.ParseExact(stringDateValue, "yyyy/MM/dd", CultureInfoDateCulture);
        return true;
    }
    catch
    {
        return false;
    }
}

//Example 4 - Validate Date for the format DDMMYYYY 
private bool ValidateDate(string stringDateValue)
{
    try
    {
        CultureInfo CultureInfoDateCulture = new CultureInfo("fr-FR");
        DateTime d = DateTime.ParseExact(stringDateValue, "ddMMyyyy", CultureInfoDateCulture);
        return true;
    }
    catch
    {
        return false;
    }
}

//Example 5 - Validate Date for the format MMDDYYYY 
private bool ValidateDate(string stringDateValue)
{
    try
    {
        CultureInfo CultureInfoDateCulture = new CultureInfo("en-US");
        DateTime d = DateTime.ParseExact(stringDateValue, "MMddyyyy", CultureInfoDateCulture);
        return true;
    }
    catch
    {
        return false;
    }
}

//Example 6 - Validate Date for the format MMDDYYYYHHMMSS 
private bool ValidateDate(string stringDateValue)
{
    try
    {
        CultureInfo CultureInfoDateCulture = new CultureInfo("en-US");
        DateTime d = DateTime.ParseExact(stringDateValue, "MMddyyyyHHmmss", CultureInfoDateCulture);
        return true;
    }
    catch
    {
        return false;
    }
}

Stored Procedures Advantages

There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question. I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)

Not to mentioned I received the second question following my answer : Why?Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines

SQL Views - Advantages and Disadvantages

Advantages

Views are used for several different reasons:

1. To hide data complexity. Instead of forcing your users to learn the T-SQL JOIN syntax you might wish to provide a view that runs a commonly requested SQL statement.
   
2. To protect the data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For instance, customer names, addresses and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the underlying tables. There are a couple of ways you might want to secure your data:
   
   1. Create a view to allow reading of only certain columns from a table. A common example of this would be the salary column in the employee table. You might not want all personnel to be able to read manager's or each other's salary. This is referred to as partitioning a table vertically and is accomplished by specifying only the appropriate columns in the CREATE VIEW statement.
      
   2. Create a view to allow reading only certain rows from a table. For instance, you might have a view for department managers. This way, each manager can provide raises only to the employees of his or her department. This is referred to as horizontal partitioning and is accomplished by providing a WHERE clause in the SELECT statement that creates a view.
      
3. Enforcing some simple business rules. For example, if you wish to generate a list of customers that need to receive the fall catalog, you can create a view of customers that have previously bought your shirts during the fall.
   
4. Data exports with BCP. If you are using BCP to export your SQL Server data into text files, you can format the data through views since BCP's formatting ability is quite limited.
   
5. Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.

Disadvantages

Even though views can be a great tool for securing and customizing data, they can be slow. Indeed, they are not any faster than the query that defines them. With SQL Server 2000, indexed views (also referred to as "materialized" views) are supported to overcome this limitation. Views can especially degrade the performance if they are based on other views. Therefore, it is recommended NOT to create views based on other views. All views should be created against base tables.

SQL HAVING

The SQL HAVING clause is used in conjunction with the SELECT clause to specify a search condition for a group or aggregate. The HAVING clause behaves like the WHERE clause, but is applicable to groups - the rows in the result set representing groups. In contrast the WHERE clause is applied to individual rows, not to groups. To clarify how exactly HAVING works, we’ll use the Sales table:

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	08/10/2005	190	2	Johnson
3	07/13/2005	500	5	Baldwin
4	07/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/03/2005	2000	2	Baldwin

In the previous chapter we retrieved a list with all customers along with the total amount each customer has spent respectively and we use the following statement:

SELECT CustomerName, SUM(OrderPrice) FROM Sales
GROUP BY CustomerName

This time we want to select all unique customers, who have spent more than 1200 in our store. To accomplish that we’ll modify the SQL statement above adding the HAVING clause at the end of it:

SELECT CustomerName, SUM(OrderPrice) FROM Sales
GROUP BY CustomerName
HAVING SUM(OrderPrice) > 1200

The result of the SELECT query after we added the HAVING search condition is below: CustomerName OrderPrice Baldwin 2500 Smith 1400

CustomerName	OrderPrice
Baldwin	2500
Smith	1400

Another useful example of the HAVING clause, will be if we want to select all customers that have ordered more than 5 items in total from all their orders. OUR HAVING statement will look like this:

SELECT CustomerName, SUM(OrderQuantity) FROM Sales
GROUP BY CustomerName
HAVING SUM(OrderQuantity) > 5

You can have both WHERE and HAVING in one SELECT statement. For example you want to select all customers who have spent more than 1000, after 10/01/2005. The SQL statement including both HAVING and WHERE clauses will look like this:

SELECT CustomerName, SUM(OrderPrice) FROM Sales
WHERE OrderDate > ‘10/01/2005’
GROUP BY CustomerName
HAVING SUM(OrderPrice) > 1000

Here is something very important to keep in mind. The WHERE clause search condition is applied to each individual row in the Sales table. After that the HAVING clause is applied on the rows in the final result, which are a product of the grouping. The important thing to remember is that the grouping is done only on the rows that satisfied the WHERE clause condition.

SQL GROUP

The SQL GROUP BY statement is used together with the SQL aggregate functions to group the retrieved data by one or more columns. The GROUP BY concept is one of the most complicated concepts for people new to the SQL language and the easiest way to understand it, is by example. We want to retrieve a list with unique customers from our Sales table, and at the same time to get the total amount each customer has spent in our store.

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	08/10/2005	190	2	Johnson
3	07/13/2005	500	5	Baldwin
4	07/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/03/2005	2000	2	Baldwin

You already know how to retrieve a list with unique customer using the DISTINCT keyword:

SELECT DISTINCT CustomerName FROM Sales

The SQL statement above works just fine, but it doesn’t return the total amount of money spent for each of the customers. In order to accomplish that we will use both SUM SQL function and the GROUP BY clause:

SELECT CustomerName, SUM(OrderPrice) FROM Sales
GROUP BY CustomerName

We have 2 columns specified in our SELECT list – CustomerName and SUM(OrderPrice). The problem is that SUM(OrderPrice), returns a single value, while we have many customers in our Sales table. The GROUP BY clause comes to the rescue, specifying that the SUM function has to be executed for each unique CustomerName value. In this case the GROUP BY clause acts similar to DISTINCT statement, but for the purpose of using it along with SQL aggregate functions. The result set retrieved from the statement above will look like this

CustomerName	OrderPrice
Baldwin	2500
Johnson	190
Smith	1400
Wood	1000

You do grouping using GROUP BY by more than one column, for example:

SELECT CustomerName, OrderDate, SUM(OrderPrice) FROM Sales
GROUP BY CustomerName, OrderDate

When grouping, keep in mind that all columns that appear in your SELECT column list, that are not aggregated (used along with one of the SQL aggregate functions), have to appear in the GROUP BY clause too.

Difference Between UNION And UNION ALL

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.