Difference Between Union vs. Union All in SQL Server
Posted inSQL

Difference Between Union vs. Union All in SQL Server

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected. UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
SQL Server DATEADD Function to Add or Subtract Dates
Posted inSQL

SQL Server DATEADD Function to Add or Subtract Dates

This function is used to add or subtract specified time interval from dates in SQL Server. Generally DATEADD function will take 3 arguments. Declaration of DATEADD function: DATEADD (datepart, number, date) In this function 1st Argument "datepart" is the interval type we need to add or subtract for example day, month, year, hour, minute, second. Datepart can be one of the following:
ISNULL function in SQL Server
Posted inSQL

ISNULL function in SQL Server

ISNULL ( check_expression , replacement_value ) Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. Example: create table Tab(id int,name char(10)) insert into Tab values(1,'Vikas') insert into Tab values(2,null) insert into Tab values(3,null) insert into Tab values(4,'Arun') select * from Tab select id,isnull(name,'No data') as name from Tab
SQL SERVER – CASE Statement Examples and Explanation
Posted inSQL

SQL SERVER – CASE Statement Examples and Explanation

CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions. Two basic formulations for CASE expression 1) Simple CASE expressions A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.
Different Types of SQL Joins
Posted inSQL

Different Types of SQL Joins

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table. I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows: INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN CROSS JOIN SELF JOIN LEFT JOIN RIGHT JOIN Each type is explained below with suitable examples: Let’s consider two tables as Table1 & Table2 for our example. – CREATE TEST TABLES CREATE TABLE Table1(ID [int], Code [varchar](10)); GO CREATE TABLE Table2(ID [int], Amount [int]); GO
Implementing Referential Integrity and Cascading Actions in SQL
Posted inSQL

Implementing Referential Integrity and Cascading Actions in SQL

Many a times you need to delete the record but at the same time you need to make sure all the related or referenced records are also deleted. This is because you can not delete a record if it is referenced in another table. In this case you can either delete the referenced record or you can set the “Cascade Delete On”. CREATE TABLE [dbo].[Tracks]( [TrackID] [int] NOT NULL PRIMARY KEY, [Title] [varchar](50) NULL, [AlbumID] [int] NULL, [Duration] [time](0) NULL)
SQL Server Basics and Types of Cursors
Posted inSQL

SQL Server Basics and Types of Cursors

Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row. Life Cycle of Cursor Declare Cursor A cursor is declared by defining the SQL statement that returns a result set. Open A Cursor is opened and populated by executing the SQL statement defined by the cursor. Fetch
Different Types of SQL Server Functions
Posted inSQL

Different Types of SQL Server Functions

Types of Function System Defined Function These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server Scalar Function Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions. abs(-10.67)- This returns absolute number of the given number means 10.67. rand(10)-This will generate random number of 10 characters. round(17.56719,3)-This will round off the given number to 3 places of decimal means 17.567