SQL Server Aggregate Functions

Aggregate Functions : Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

Aggregate functions can be used as expressions only in the following:

·         The select list of a SELECT statement (either a subquery or an outer query).

·         A HAVING clause.

AVG : Returns the average of the values in a group. Null values are ignored.

Examples of AVG function :

Using avg function in SELECT clause:-

SELECT AVG(UnitsInStock) FROM   Products

Using avg function with DISTINCT:-

SELECT AVG ( DISTINCT UnitsInStock FROM Products

Using avg function in SELECT clause with GROUP BY clause:-

SELECT ProductName,AVG(Quantity)FROM   Invoices

GROUP  BY ProductName

MIN : Returns the minimum value in the expression.

Examples of MIN function :

For example, the following returns the least amount from the FactFinance table:

SELECT MIN(amount) FROM FactFinance

Results:

-1121918

Note that the parameter of MIN can be any valid expression, including string columns, as in the following:

SELECT MIN(EnglishProductName)FROM DimProduct

Results:

Adjustable Race

SUM : Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

Examples of SUM function :

The following query returns the sum of amounts from FactFinance table:

SELECT SUM(Amount)FROM FactFinance

Results:

1358640412.7

The next example uses DISTINCT keyword to return the sum of distinct values:

SELECT SUM(DISTINCT Amount)FROM FactFinance

Results:

1251597458.19

COUNT : Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.

Examples of COUNT function :

If you specify a "*" as the criterion, COUNT returns the total number of rows in a table; for example, the following query counts rows in the FactFinance table of Adventure Works DW database:

SELECT COUNT(*)FROM FactFinance

Results:

39409

If you join multiple tables then COUNT(*) returns the number of rows satisfying the join criterion, as in the following:

SELECT COUNT(*)FROM FactFinance a INNER JOIN DimOrganization b

ON a.OrganizationKey = b.OrganizationKey

COUNT(*) cannot be used with DISTINCT; nor can you specify any other parameter - this variation of the function automatically counts every single row in a single or multiple joined tables.

Unlike all other aggregate functions, COUNT does not ignore NULL values.

If you need to find the count of unique items within a column in a table use COUNT (DISTINCT column_name) syntax. For example, the following query counts unique organization keys within the FactFinance table:

SELECT COUNT(DISTINCT OrganizationKey)FROM FactFinance

Results:

9

MAX : Returns the maximum value in the expression.

Examples of MAX function :

For example, the following returns the greatest amount from the FactFinance table:

SELECT MAX(amount)FROM FactFinance

Results:

4820988

SQL Server Union and Union All

UNION : UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

Syntax:

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

UNION

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

EXAMPLE :

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

UNION

    

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

UNION ALL: UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.The same rules that apply to UNION apply to the UNION ALL operator.

Syntax:

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

UNION ALL

SELECT column1 [, column2 ] FROM table1 [, table2 ][WHERE condition]

EXAMPLE :

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

UNION ALL

    

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

SQL Server TRUNCATE and DELETE

TRUNCATE : TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement. TRUNCATE is a DDL command.

Syntax : TRUNCATE Table Table_name

EXAMPLE :

TRUNCATE TABLE CUSTOMERS;

Restrictions : You cannot use TRUNCATE TABLE on tables that

·         Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

·         Participate in an indexed view.

·         Are published by using transnational replication or merge replication.

DELETE : Removes one or more rows from a table or view in SQL Server.

Syntax : DELETE FROM table WHERE conditions;

EXAMPLE :

USING ONE CONDITION

DELETE FROM employees WHERE first_name = 'Sunil';

USING TWO CONDITIONS

DELETE FROM employees WHERE last_name = 'Johnson'

AND employee_id >= 80;

USING TOP KEYWORD

DELETE TOP(3)FROM employees WHERE last_name = 'Sunil'

Difference between TRUNCATE and DELETE :

TRUNCATE	DELETE
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.	DELETE is executed using a row lock, each row in the table is locked for deletion.
We cannot use Where clause with TRUNCATE.	We can use where clause with DELETE to filter & delete specific records.
TRUNCATE removes all rows from a table.	The DELETE command is used to remove rows from a table based on WHERE condition.
Minimal logging in transaction log, so it is performance wise faster.	It maintain the log, so it slower than TRUNCATE.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
To use Truncate on a table you need at least ALTER permission on the table.	To use Delete you need DELETE permission on the table.
Truncate uses the less transaction space than Delete statement.	Delete uses the more transaction space than Truncate statement.
Truncate cannot be used with indexed views	Delete can be used with indexed views

SQL Server Mathematical Functions

SQL Server Mathematical Functions :

ABS : A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Syntax : ABS ( numeric_expression )

Examples : It shows the results of using the ABS function on three different numbers.

SELECT ABS(-1.0), ABS(0.0), ABS(1.0);

Output : 1.0        0.0          1.0

The ABS function can produce an overflow error when the absolute value of a number is greater than the largest number that can be represented by the specified data type. For example, the int data type can hold only values that range from -2,147,483,648 to 2,147,483,647. Computing the absolute value for the signed integer -2,147,483,648 causes an overflow error because its absolute value is greater than the positive range for the int data type.

DECLARE @i int;

SET @i = -2147483648;

SELECT ABS(@i);

GO

Output :

Msg 8115, Level 16, State 2, Line 3

Arithmetic overflow error converting expression to data type int

DEGREES : Returns the corresponding angle in degrees for an angle specified in radians.

Syntax : DEGREES ( numeric_expression )

Examples : It returns the number of degrees in an angle of PI/2 radians.

SELECT 'The number of degrees in PI/2 radians is: ' +

CONVERT(varchar, DEGREES((PI()/2)));

GO

Or

select DEGREES((PI()/2))

Output : The number of degrees in PI/2 radians is: 90

CEILING : Returns the smallest integer greater than, or equal to, the specified numeric expression.

Syntax : CEILING ( numeric_expression )

Examples : It shows positive numeric, negative, and zero values with the CEILING function.

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0);

GO

Output:

124.00   -123.00 0.00

PI : Returns the constant value of PI.

Syntax : PI ( )

Return Type: float

Examples : It returns the value of PI.

SELECT PI();

Output: 3.14159265358979

SQUARE : Returns the square of the specified float value.

Syntax : SQUARE ( float_expression )

Return Type: float

Examples :

SELECT SQUARE(5)

Output: 25

SQRT: Returns the square root of the specified float value.

Syntax : SQRT ( float_expression )

Return Type: float

Examples : It returns the square root of numbers between 1.00 and 10.00.

DECLARE @myvalue float;

SET @myvalue = 1.00;

WHILE @myvalue < 10.00

BEGIN

SELECT SQRT(@myvalue);

SET @myvalue = @myvalue + 1

END;

GO

Output:

1.0

------------------------

1.4142135623731         

------------------------

1.73205080756888        

------------------------

2.0                     

------------------------

2.23606797749979        

------------------------

2.44948974278318        

------------------------

2.64575131106459        

------------------------

2.82842712474619        

------------------------

3.0

FLOOR : Returns the largest integer less than or equal to the specified numeric expression.

Syntax : FLOOR ( numeric_expression )

Examples : It shows positive numeric, negative numeric, and currency values with the FLOOR function.

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45);

Output:

123         -124       123.00

SQL Server Commands

SQL Server Commands- DDL, DML, TCL, DCL :

DML Commands : DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples : SELECT, UPDATE, INSERT statements

Select : It is used to extract the data from one or combination of tables

Update : It is used to update the data in a database table.

Delete : It is used to delete data from the database table.

Insert Into : It is used to insert data into a database(table)

DDL Commands : DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

Create Table: It is used to create a Table

Alter Table: It is used to Alter the table definition like adding any columns or deleting any table column.

Drop table: It is used to Drop the table.

Create Index: It is used to Create a Index on a table

Drop Index: It is used to drop a table from the table

DCL Commands : DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

Grant : It is used to give access rights to the user for the database

Revoke: It is used to revoke or delete the access rights of some of the users for a given database.

TCL Commands : TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

Commit: This command is used to save the work done by the user.

Rollback:  This command is used to delete the data till the last committed state of the database.

Tips to improve SQL Server database design and performane

Tips to improve SQL Server database design and performance

Choose Appropriate Data Type : Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance.

Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

Avoid nchar and nvarchar : Practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.

Avoid NULL in fixed-length field : Practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

Avoid * in SELECT statement : Practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.

-- Avoid

SELECT * FROM tblName

--Best practice

SELECT col1,col2,col3 FROM tblName

Use EXISTS instead of IN : Practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.

-- Avoid

SELECT Name,Price FROM tblProduct

where ProductID IN (Select distinct ProductID from tblOrder)

--Best practice

SELECT Name,Price FROM tblProduct

where ProductID EXISTS (Select distinct ProductID from tblOrder)

Avoid Having Clause : Practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.

Create Clustered and Non-Clustered Indexes : Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

Keep clustered index small : Practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly.

Avoid Cursors : You should avoid using SQL cursor since it has adverse effect on SQL server’s performance. It fetches the records row by row which results in repeated network round trips.

Use Table variable inplace of Temp table : Practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.

Use UNION ALL inplace of UNION : Practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

Use Schema name before SQL objects name: Practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.

--Here dbo is schema name

SELECT col1,col2 from dbo.tblName

-- Avoid

SELECT col1,col2 from tblName

Keep Transaction small : Practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks.

SET NOCOUNT ON: Practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement.This prevents stored procedure to send messages indicating number of rows affected thus saves network traffic.

CREATE PROCEDURE dbo.MyTestProc

AS

SET NOCOUNT ON

BEGIN

.

.

END

Use TRY-Catch : Practice to use TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH.

Use Stored Procedure for frequently used data and more complex queries : Practice to create stored procedure for query that is required to access data frequently.

Avoid prefix "sp_" with user defined stored procedure name : Practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Because SQL server first search the system defined stored procedure. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.

SQL Server Joins

Joins : SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables.

Types of Joins :In SQL Server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition.

1. Inner Join

2. Outer Join

3. Cross Join

4. Self Join

Inner Join : Inner join returns only those records/rows that match in both the tables.

Syntax for Inner Join is as

Select * from table_1 as t1

inner join table_2 as t2

on t1.IDcol=t2.IDcol

Examples : Consider the below three tables



Inner Join 

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price

FROM tblProduct AS t0 INNER JOIN tblOrder AS t1 ON t0.ProductID =t1.ProductID

ORDER BY t1.OrderID

Output :

Inner Join among more than two tables:

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer

FROM tblProduct AS t0

INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID

ORDER BY t1.OrderID

Output :

Inner Join on multiple conditions:

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer

FROM tblProduct AS t0

INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID AND t1.ContactNo = t2.ContactNo

ORDER BY t1.OrderID

Output :

Outer Join :

We have three types of Outer Join.

1.Left Outer Join : Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

Syntax for Left outer Join is as :

Select * from table_1 as t1

left outer join table_2 as t2

on t1.IDcol=t2.IDcol

Examples :

SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price

FROM tblProduct AS t0

LEFT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

ORDER BY t0.ProductID

Output :

2.Right Outer Join :Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values.

Syntax for right outer Join is as :

Select * from table_1 as t1

right outer join table_2 as t2

on t1.IDcol=t2.IDcol

Examples :

SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price

FROM tblProduct AS t0

RIGHT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

ORDER BY t0.ProductID 

Output :

3.Full Outer Join :Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables. If there are no columns matching in the both tables, it returns NULL values.

Syntax for full outer Join is as :

Select * from table_1 as t1

full outer join table_2 as t2

on t1.IDcol=t2.IDcol

Examples :

SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price

FROM tblProduct AS t0

FULL OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID

ORDER BY t0.ProductID

Output :

Cross Join : Cross join is a Cartesian join means Cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

Syntax for right outer Join is as :

Select * from table_1

cross join table_2

Examples : 

SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price

FROM tblProduct AS t0, tblOrder AS t1

ORDER BY t0.ProductID

Output :

Self Join : SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of SQL join.

Syntax for right outer Join is as :

SELECT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

Examples :

To understand Self Join,

Here I will explain self join with one example for that first design one table and give name as EmployeeDetails in your database as shown below.

Column Name	Data Type	Allow Nulls
EmpId	Int (set Identity=true)	No
EmpName	varchar(50)	Yes
ManagerId	Int	Yes

Once table designed please enter the data in your table that as shown below.

EmpId	EmpName	ManagerId
1	Suresh	0
2	Prasanthi	1
3	Mahesh	1
4	Sai	2
5	Madhav	2
6	Honey	5

Now if I want get the details of Empolyees who are in Manager Position for that we need to write query like this

SELECT DISTINCT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

Output:

EmpId	EmpName
1	Suresh
2	Prasanthi
5	Madhav

Suppose if I want get the details of Empolyees who are having Managers then we need to write query like

SELECT e2.EmpId, e2.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

Output:

EmpId	EmpName
2	Prasanthi
3	Mahesh
4	Sai
5	Madhav
6	Honey

Basic guide to help you decide which type of join to use:

• Use an inner join when you want matching rows from both tables.
• Use a left or right outer join when you want to preserve all rows from one table and return only matching rows from the other.
• Use a left or right outer join with exclusions when you're looking for something in one table that doesn't exist in another table.
• Use a full outer join when you want all the rows from both sides, with nulls where they don't match.
• Use a cross join when you want to create a Cartesian product from two tables.