DateTime Today = DateTime.Now;
DateTime PreviusDay = Today.AddDays(-1);
DateTime LastWeekDay = Today.AddDays(-(int)Today.DayOfWeek);
DateTime LastMonth = DateTime.Now.AddMonths(-1);
MERGE Statement in SQL
The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE and DELETE. In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handle the large running databases. But unlike INSERT, UPDATE and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called as target table.
Now we know that the MERGE in SQL requires two tables : one the target table on which we want to perform INSERT, UPDATE and DELETE operations, and the other one is source table which contains the new modified and correct data for target table and is actually compared with the actual target table in order to modify it.
MERGE statement in SQL basically merges data from a source result set to a target table based on a condition that is specified. The syntax of MERGE statement can be complex to understand at first but its very easy once you know what it means.So,not to get confused first let’s discuss some basics. Suppose you have two tables: source and target, now think if you want to make changes in the required target table with the help of provided source table which consists of latest details.
Example -
MERGE INTO target_table_name or target_table_query
USING source_table_name or source_table_query
ON (list_of_conditions)
WHEN MATCHED THEN
UPDATE target_table_name SET target_table_name.column_1 = source_table_name.expr_1, target_table_name.column_2 = source_table_name.expr_2,...target_table_name.column_n = source_table_name.expr_n
WHEN NOT MATCHED THEN
INSERT (column_1,column_2...column_n)
VALUES(source_table_name.expr_1, source_table_name.expr_2,...source_table_name.expr_n);
What is the difference between UNION and UNION ALL
Difference between UNION and UNION ALL is that:
- UNION: only keeps unique records
- UNION ALL: keeps all records, including duplicates
UNION ALL keeps all of the records from each of the original data sets, UNION removes any duplicate records. UNION first performs a sorting operation and eliminates of the records that are duplicated across all columns before finally returning the combined data set.
Example for UNION and UNION ALL :
UNION =>
UNION ALL =>
UNION or UNION ALL have the same basic requirements of the data being combined:
1. There must be the same number of columns retrieved in each SELECT statement to be combined.
2. The columns retrieved must be in the same order in each SELECT statement.
3. The columns retrieved must be of similar data types.
Because the UNION ALL operator does not remove duplicate rows, it runs faster than the UNION operator.
Differences between CHAR, VARCHAR data types in SQL Server
CHAR:
CHAR stands for “Character”.
It is used for the fixed length of character data.
The maximum length of character data should be 254 characters.
CHAR is a fixed length string data type, so any remaining space in the field is padded with blanks.
The default length for a CHAR is 1, and the maximum size of length is 254.
Storage size of CHAR datatypes is equal to n bytes i.e. set length
CHAR take 1 byte for each character
Better performance than VARCHAR
VARCHAR:
VARCHAR stands for “Variable Character”.
It’s used for the variable length of character data.
It is valid for a maximum of 4046 characters.
Since it is variable length it takes less memory spaces.
Decreases the performance of some SQL queries.
Storage size of VARCHAR datatype is equal to the actual length of the entered string in bytes.
VARCHAR take 1 byte for each character and some extra bytes for holding length information.
Performance is not good as compared to CHAR
It can hold numbers, letters and special characters.
Subscribe to:
Posts (Atom)