SQL SERVER - How To Use ‘Order By’ Clause In a View

In general, we cannot use 'Order By' clause in a view Definition. If we try to use the 'Order By' clause in a view definition, it will give the following error message

'The ORDER BY clause is invalid in views, inline functions, derived tables, sub queries, and common table expressions, unless TOP or FOR XML is also specified'

In order to solve this, there are two alternatives.

First     One is Using 'Top' keyword in the view definition Second One is Using the 'Order By' Clause in the View Retrieval.

Let us see the First case through an example.

Method 1 : Creating a view using DimEmployee Dimension table with Top keyword

Syntax:

Create View vw_EmpDetails AS
Select Top 100 Percent EmployeeKey,FirstName,LastName,Title,DepartmentName From dbo.DimEmployee
Order By EmployeeKey

Method 2 : Using the 'Order By' Clause directly in the view

As a first step we will create a view with out using the 'TOP' keyword.

Syntax:

Create View vw_EmpDetails AS
Select EmployeeKey,FirstName,LastName,Title,DepartmentName From dbo.DimEmployee

Now, we will use the 'Order By' clause in the Select statement.

Syntax: 

Select * From vw_EmpDetails
Order By EmployeeKey

SQL SERVER - Types Of Indexes

Index is that which can be used for uniqueness and Fast Data Retrieval

There are two types of indexes. They are :

1.Clustered index
2. Non Clustered index

Clustered index is that in which all the records are arranged in sorted order on the index key where as, in case Of Non Clustered index no records will be in sorted order.

In case of Clustered index as the data is in the Sorted Order and there will be minimal effort to retrieve the data. But in case of Non clustered index maximum error must be kept to retrieve data with Range Search.

In Clustered index, the Actual data is available at the end of the leaf node. Hence there will be only one Clustered index. In Non Clustered index the address will be stored in the leaf nodes. Hence there may be many number of Non Clustered indexes.

The number of Non Clustered indexes in a Table are 249 while the number of Clustered index is only One.    

SQL SERVER - Types of Constraints

Constraints are the Restrictions Placed either at a Column level or Table level. A Constraint ensures that the data meets certain Data integrity rules.

There are 3 types of Constraints. They are :
1. Domain Constraints
2. Entity Constraints                                                              3. Referential Integrity Constraints

Domain Constraints Deal with One or more Columns.

Entity Constraints requires every row to have a unique value for a column or combination of columns.

Referential Integrity Constraints are created when a value in one column must match the value in another column, in the same/other table.

SQL SERVER - Different types of Databases

There are 4 types of databases :

They are :
1.Master
2.Model
3.Msdb
4.Tempdb

SQL SERVER - Types of User Defined Functions

User Defined Functions in SQL Server are nothing but the extension to the Microsoft  Defined System Functions, Where user can include his own Business logic and his own calculations.

There are 3 types of user defined functions:
They are :
1. Scalar Function
2. Table or Inline function
3. Multi statement Function

Scalar Function : If the user defined function returns any scalar value like integer or character etc then it is called Scalar Function

Example:

Create Function dbo.fn_Returns_Number                      Returns INT as
Begin
  Declare @Num int
  Set @Num = 27
  Return @Num
End

Table valued or Inline Function : If the user defined function returns a table then it is called Table valued or Inline function. Here we will use single Select query in the body of the user defined function to return a set of rows from any specific table.

Example:

Create Function dbo.fn_Returns_Table(@City Varchar(10))
Returns Table as
Return(Select Emp_Name From Employee where City = @City)

Multi Statement Function : If the definition of a Table Valued Function contains the design of the table, then it is called Multi statement function. That means this function allows to specify the design of the table being returned from User defined function.

Example:

Create Function dbo.fn_TableWithDesign(@Date Varchar(10))
Returns @Temp Table(Id Int, Desc varchar(10),Qty Int) as
Begin
    Insert Into @Temp
    Select ProdId,ProdName, ProdQty
    From Product
    where ProdDate > @Date                                   
Return
End

SQL SERVER - Types Of Joins

Getting the data from more than one table is called a Join.

There are five types of Joins.
1.Cross Join
2.Natural Join
3.Equi Join
4.Self Join
5.Outer Join

Cross Join : Cross Join is nothing but a Cartesian Product where getting the data from more than one table with out specifying any condition in the ON Clause. 

Example:

Select  EmpName                                                     From Employee CROSS JOIN Department

The result set contains                                              (No. of Records in Employee) * (No. of Records in Department) of Records

Natural Join : A Join that restricts  the redundant column data from the result set.

Example:

Select Emp.EmpId,Emp.EmpName,Dept.DeptName,Emp.Salary
From Employee Emp  JOIN Department Dept
ON  Emp.DeptId = Dept.DeptId

Here we will specify all the columns from the both tables, that we want to display, in the Select statement. Hence the Redundant Columns can be avoided.

If we did not specified the columns and simply used the ‘*’ in the select statement, then we can see the DeptId column twice, which can be avoided in this Natural Join.

Equi Join : A Join which displays the redundant Column data from the result set.Here we will use the ‘*’ symbol in the Select Statement.

Example:

Select  *
From Employee Emp  JOIN Department Dept
ON  Emp.DeptId = Dept.DeptId

As Specified above, Here we won’t specify the columns names in the Select statement instead we will use ‘*’ symbol. Hence the Redundant Columns will be shown.

In the above example, we can see the DeptId column twice one is from Employee table and another one is from Department table.

Self Join : A table can be joined to itself.That means, A Join in which One record of a table correlates with other records of the same table.

Example:

Select Mgr.LogID Manager,Emp.MgrID,Emp.LogID, Emp.EmpID
From Employee Emp JOIN Employee Mgr
ON  Emp.MgrID = Mgr.EmpID

The above example returns the list of all the managers and their employees that report to them. Here one record of the table  Employee correlates with other records of the same table

Outer Join : The result set contains all the rows from one table and matching rows from another.

Again the Outer Join can be of 3 types:

1. LEFT OUTER JOIN or LEFT JOIN                                        2. RIGHT OUTER JOIN or RIGHT JOIN                                    3. FULL OUTER JOIN or FULL JOIN

Example:

Let us consider two tables Product and Orders. Every Product May or May not be Ordered with in a period of time.

Now we want to get all the Product details, Whether it may be Ordered or not. In this case we will go for LEFT OUTER JOIN.

Select P.ProdId, ProdName, OrderNum
From Product P LEFT OUTER JOIN Orders O
ON  Product.PRODID = Orders.PRODID

In this case, In the Result set, for all the Products those are not Ordered, the OrderNum column will be Null.That means, the result set contains all matched records from the Orders  table and all unmatched records from the Product Table.

Similarly, In case of RIGHT OUTER JOIN, it will return all the matched records from the First table and all the unmatched records from the Second table.Null value will be placed for all the missing values at the

While In case Of FULL OUTER JOIN, the result set contains all the records from both tables. It will return NULLs for any values missing on either side.

Example:

Select CustID, CompName, O.OrderID                          From Customer C FULL OUTER JOIN Orders O                  ON  c.CustomerID = o.CustomerID

SQL SERVER - Types Of Triggers

There are two types of triggers are there in sql server
1.After Trigger
2. Instead Of Trigger

SQL SERVER – Ranking Functions

Ranking functions return a ranking value for each row. Depending on the function that is used, some rows might receive the same value as other rows while some receive one rank plus the number of distinct ranks that come before the current row.

Let us first create the Student Marks table.

Create Table StudentMarks(StudentId int identity(1,1),Name varchar(25),Paper1 int,Paper2 int,Paper3 int,Batch_no int)

Insert Into StudentMarks
Select 'AAA',90,95,99,1
Union All
Select 'BBB',90,94,98,1
Union All
Select 'CCC',90,93,99,1
Union All
Select 'DDD',91,92,97,2
Union All
Select 'EEE',97,99,99,2
Union All
Select 'FFF',97,92,98,2
Union All
Select 'GGG',91,92,93,2
Union All
Select 'HHH',95,95,95,2
Union All
Select 'III',91,93,95,3
Union All
Select 'JJJ',90,95,99,3
Union All
Select 'KKK',91,93,99,3
Union All
Select 'LLL',96,91,99,3

Now let us first retrieve the records from the table.

Select * From StudentMarks

Now let us see the Ranking Functions one by one, using the above table. 

1. RANK() : Returns the rank of each row.If two or more rows tie for a rank, each tied rows receives the same rank.

Example:                                                                  Select Studentid,Name,Paper1,
Rank() over(Order by paper1 desc) 'Rank'
From StudentMarks

Output :

Here the first two Rows i.e. Paper1 marks for the first two students are same. Hence they both will receive the same Rank i.e.  Rank 1. Since the first two ranks were given, the third student will receive the Rank 3 instead of 2. The ranking will follows  in the same manner for the remaining rows.

2. DENSE_RANK() : Returns the Rank of each row, without any gaps in the ranking. If two or more rows tie for a rank, each tied rows receives the same Rank. The next rank is one plus the number of distinct ranks that come before the current row.

Example:                                                              Select Studentid,Name,Paper1,
Dense_Rank() over(Order by paper1 desc) 'Dense_Rank'
From StudentMarks

Output :

Here the first two Rows i.e. Paper1 marks for the first two students are same. Hence they both will receive the same rank i.e  Rank 1. Here Unlike RANK(), the next student will  receive Rank 2 instead of 3. That means the next rank will be, 1  +  1 (the Number of distinct ranks that come before the current row) i.e. 2.

3. NTILE(n) : Divides all Rows in an ordered partitions into ‘n’ number of groups. The groups are numbered,starting at 1.

Example:                                                                                  Select Studentid,Name,Paper1,
NTILE(4) over(Order by Paper1 desc) 'Group'
From StudentMarks

Output :

As shown in the above example, all the 12 rows were divided into 4 groups in an order partition.

4. ROW_Number()  : Displays Sequential Row Number for each of the row with in the partition starting at 1. If we are not using the ‘Partition By’ clause, then it will display the sequential row number continuously for each of the row. 

Example 1 (Using Order By clause):                               Select 
ROW_NUMBER() over(Order by Paper1 desc) 'Record_No',
Studentid,Name,Paper1
From StudentMarks

Output :

As shown in the above example, as we are not partitioned the rows, the row number is displaying sequentially from 1 to 12.

Example 2 (Using Partition By clause):                         Select 
ROW_NUMBER() over(Partition By Batch_no Order by Paper1 desc) 'Record_No',
Studentid,Name,Paper1,Batch_no
From StudentMarks

Output :

As shown in the above example, For each partition, Here  Batch_no, a new record or row number will be displayed. As three distinct batches are available in the table, On applying the Partition on Batch_no, for each partition a new set of row numbers will  be displayed,  each starting at 1.

SQL SERVER - Date And Time Functions

Following are the types of Date and Time functions that are available in SQL Server.

DateAdd()
DateDiff()
DatePart()
DateName()                                                                   GetDate()                                                                             Year()                                                                                         Month()                                                                                  Day()

Let us see Where and How these Functions can be used.

1. GetDate()  : Returns the Current System Date and Time

     Syntax : GetDate()

     Example : Select GetDate() [Current Date]

     Result :

      

2. DateAdd() : Returns the Date after Adding the specific interval (it may be either days or months or years) to the given Date.

     Syntax : DateAdd(Datepart,Integer,Date)

     Example : Select DateAdd(dd,27,GetDate()) [New Date]

     Result :

           

3. DateDiff() : Returns the  Difference  between  two Specified Dates.

     Syntax : DateDiff(Datepart,Integer,Date)

     Example : Select DateDiff(dd,'01/01/2009',GetDate()) [Date Difference]

     Result :

    

4. DatePart() : Returns an integer which represents the  specified Date Part of the Specified Date.

     Syntax : DatePart(Datepart,Date)

     Example : Select DatePart(dw,'01/26/2009') [Week Day]

     Result :

   

5. DateName() : Returns the Character string which represents the specified Date Part of the Specified Date.

     Syntax : DateName(Datepart,Date)

     Example : Select DateName(month,'05/27/2009') [Month]

     Result :

    

6. Year() :  Returns the Year of the Specified date

     Syntax :  Year(Date)

     Example : Select Year('05/27/2009') [Year]

     Result :

   

7. Month() : Returns the Month of the Specified date

     Syntax :  Month(Date)

     Example : Select Month('05/27/2009') [Month]

     Result :

   

8. Day() :  Returns the Day of the Specified date

     Syntax :  Day(Date)

     Example : Select Day('05/27/2009') [Day]

     Result :

   

SQL SERVER – Aggregate Functions

Oftentimes, with in an Organization or with in the Business, the higher authorities are much interested to summarize the data for data analysis and decision support.

SQL Server Provides the Aggregate functions to assist the summarization of large volumes of data. These Aggregate Functions will perform a calculation on a set of values and return a single value.

All Of the Queries in this article will use the below SalesOrder table from the Adventure Works database.Let us first have a look at the data that is available in this table.

Select * From SalesOrder

Now, We will see different types of Aggregate functions that are available in SQL Server.

1. AVG() : Returns the average of the values in a group.

Example:                                                             Select Customerkey,AVG(SalesAmount) 'Average Sales'         From SalesOrder
Group By Customerkey

Output :

2. COUNT()  & COUNT_BIG(): Both Returns the number of items in a group.The main Difference between the two is Count_big is always returns the BigInt value.

Example:                                                             Select Customerkey,                                                Count(SalesOrderNumber) 'Sales Count',                   Count_BIG(SalesOrderNumber) 'Sales Count'                  From SalesOrder
Group By Customerkey

Output :

3. SUM() : Returns the sum of all or Distinct values.This can be applied only on Numeric columns. 

Example:                                                             Select Customerkey, SUM(SalesAmount) 'Total Sales'         From SalesOrder
Group By Customerkey

Output :

4. MAX() & MIN() : Returns the maximum  and minimum values of the column.

Example:                                                             Select Customerkey, MAX(SalesAmount) 'Max Sales',        MIN(SalesAmount) 'Min Sales'                                     From SalesOrder                                                       Where Customerkey =14324
Group By Customerkey

Output :

5. VAR() & VARP() : Returns the Statistical Variance and Variance for the Population of all values of the column.

Example:                                                             Select Customerkey, VAR(SalesAmount) 'Sales Variance', VARP(SalesAmount) 'Sales Variance Population'              From SalesOrder                                                       Group By Customerkey

Output :

6. STDEV() & STDDEVP() : Returns the Standard Deviation and Standard Deviation for the population of all values of the column.

Example:                                                             Select Customerkey,                                               STDEVP(SalesAmount) 'Sales Std Deviation',              STDEVP(SalesAmount) 'Sales Std Deviation Population'    From SalesOrder                                                       Group By Customerkey

Output :

SQL SERVER – Trigonometric and Logarithmic Functions

Today we will Discuss various Trigonometric  and Logarithmic Functions, which can be consider as Mathematical Functions.  These Trigonometric functions will take the number of radians as an argument. Arguments for ASIN(), ACOS() ranges from –1 and +1.

In order to convert the radians to degrees, we can use the below  formula.

1 Radian = (1 Degree * pi) / 180     where pi=3.14

1. SIN() : Returns the Sine of a Radian expression.

Example : SELECT ROUND(SIN((0*3.14)/180),2) 'SIN 0', ROUND(SIN((90*3.14)/180),2) 'SIN 90'

Output :

2. COS() : Returns the Cosine of a Radian expression.

Example : SELECT ROUND(COS((0*3.14)/180),2) 'COS 0', ROUND(COS((90*3.14)/180),2) 'COS 90'

Output :

3. TAN() : Returns the Tangent of a Radian expression.

Example : SELECT ROUND(TAN((0*3.14)/180),2) 'TAN 0', ROUND(TAN((45*3.14)/180),2) 'TAN 45'

Output :

4. COT() : Returns the CoTangent of a Radian expression.

Example : SELECT ROUND(COT((90*3.14)/180),2) 'COT 90', ROUND(COT((45*3.14)/180),2) 'COT 45'

Output :

5. ASIN() : Returns the ArcSine of a Radian expression.

Example : SELECT ASIN(-.54) 'ASIN -.54'

Output :

6. ACOS() : Returns  ArcCoSine of a Radian expression.

Example : SELECT ACOS(-.54) 'ACOS -.54'

Output :

7. ATAN() : Returns  ArcTangent of a Radian expression.

Example : SELECT ATAN(4) 'ATAN 4'

Output :

8. ATN2() : Returns  the angular component of the polar coordinates (r, q) associated with (x, y)

Example : SELECT ATN2(35.17,150.2) 'ATN2 Value'

Output :

9. PI() :  Returns the Constant value of the PI i.e 22/7(3.14)

Example : SELECT PI() 'PI Value'

Output :

10. DEGREES() & RADIANS() : Used to convert among themselves i.e. From Degrees to Radians and From Radians to Degrees.

Example : SELECT DEGREES(PI() /2) 'Radians' ,        RADIANS(180/PI()) 'Degrees'

Output :

11. EXP() : Returns the Exponent value of specified float expression or Natural Logarithm.

Example : SELECT EXP(1) 'Exponent Value', EXP(LOG (20)) 'Exponent Of Log'

Output :

12. LOG() : Returns the Natural Logarithm value of specified float expression or Exponent value.

Example : SELECT LOG (10) 'Log Value', LOG(EXP (20)) 'Log of Exponent'

Output :

13. LOG10() : Returns the Logarithm value of Base 10.

Example : SELECT LOG10 (100) 'Log 100 Base 10'

Output :

SQL SERVER – Arithmetic Functions

In general Arithmetic will come under Mathematical functions of SQL Server. All these functions will return the same data type as that of the input value.

Different Arithmetic Functions that are available are:

1. ABS() : Returns the absolute or positive value of the specified numeric expression.

Example: Select ABS(5) AbsoluteValue, ABS(0) AbsoluteValue,ABS(-14) AbsoluteValue

Output :

2. CEILING() : Returns the immediate smallest integer that is greater than or equal to the number specified.

Example : Select CEILING(-5.6), CEILING(-0.89), CEILING(+5.6)

Output :

3. FLOOR() :  Returns the immediate largest integer that is smaller than or equal to the number specified.

Example : Select FLOOR(-5.6), FLOOR(-0.89), FLOOR(+5.6)

Output :

4. POWER() :  Returns the Power of the specified expression.

Example : Select POWER(2,5) AS '2^5', POWER(4,3) AS   '4^3'

Output :

5. ROUND() : Returns a numeric value, rounded to the specified length or precision.

Example : SELECT ROUND(14.994, 2) Rounded_Twovalues, ROUND(15.9995, 3) Rounded_Threevalues

Output :

6. SIGN() : Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression

Example : SELECT SIGN(14) Positive , SIGN(-15) Negative,SIGN(0) Zero

Output :

7. SQRT() : Returns the Square Root of the specified float value.

Example : SELECT SQRT(3) 'SQRT(3)',SQRT(4) 'SQRT(4)'

Output :

8. SQUARE() : Returns the square of the specified value.

Example : SELECT SQUARE(3) 'SQUARE(3)', SQUARE(4) 'SQUARE(4)'

Output :

9. RAND() : Returns Random float value between 0 and 1. NO random value will match with other value.

Example : SELECT RAND() 'RANDOM Value1',RAND() 'RANDOM Value2', RAND() 'RANDOM Value3'

Output :

SQL SERVER - String Functions

String Functions have wide usage in SQL Server. We will see the frequently used String Functions in SQL Server 2005.

1. SUBSTRING(): Returns part of the string or a column

   Syntax : SUBSTRING(expression, Start, Length)

   Expression : Can be either character string or binary string or text or image or a column.

   Start : an integer from where the substring starts

   Length : an integer that represents the length of the Substring

   Example:  Select SUBSTRING('SQL Server 2008',12,4) as Version

    Result :

2. LEFT() &  RIGHT(): Returns certain number of characters either from Left Or Right side of the character expression.

Syntax :        LEFT (Character expression, integer)      

                  RIGHT(Character expression, integer)

   Character expression : Can be either Char  or varchar or nvarchar or Binary data types

   Integer  : The number of characters that will be returned

   Example:  Select Left('SQL Server 2008',3) as RDBMS 
                   Select Right('SQL Server 2008',4) as Version

    Result :

3. CHARINDEX() & PATINDEX(): Returns the starting position or First Occurence of the specified character expression in the given string. In the case of CHARINDEX() we can not use the Text data type where as in PATINDEX() we can use.Also we can use Wild card characters in PATINDEX() where as in CHARINDEX() we cannot.

Syntax : CHARINDEX(Character Expr1, Character  Expr2,[Start])                                                  

             PATINDEX('%Expression%',Character Expr2)

   Character Expr1 : Character(s) that needs to be find out.

   Character Expr2 : String or a column where we need to search the Character Expr1.

   Start :  An integer from where the search starts. This Parameter is Optional.

Expression :  Character(s) that needs to be find out.  Here Wild card characters can be used

Example:                                                                Select PatIndex('%is%','I wish towish') [First Occurrence]
Select CharIndex('to','I wish to wish') [First Occurrence]
Select CharIndex('is','I wish to wis',10) [Second Occurrence]

    Result :   

 

4. LTRIM() & RTRIM(): Trims all the leading and trailing  blanks in a character string .

Syntax :        LTRIM(Character expression)      

                  RTRIM(Character expression)

    Character Expression : Can be a character string or column or Binary data

   Example:    Select LTRIM ( '    XYZ') 
                    Select RTRIM( 'XYZ   ')

    Result :

   

5. LOWER() & UPPER(): Converts the character string’s Case to either Lower or Upper.

Syntax :        LOWER(Character expression)      

                  UPPER(Character expression)

    Character Expression : Can be a character string or column or Binary data

   Example:    Select LOWER ( 'ABC') LowerCase 
                      Select UPPER( 'abc') UpperCase

    Result :

    

6. REPLACE() : Replaces all occurrences of a specified string with another string

    Syntax : REPLACE(expression1,expression2,expression3)

   Expression1 : String to be searched.

   Expression2 : String to be Replaced.

   Expression3 : String with which Expression2 has to be replaced.

   Example:  Select REPLACE('SQL Server 2005','2005','2008') NEW_VERSION

    Result :

7. STUFF() :  Deletes a specified length of characters and inserts another set of characters at the starting point specified.

    Syntax:   STUFF(Expression1,start,length,Expression2)

   Expression1 : It can be either Character string or Column or Binary data in which Deletions\Insertions will be made.

   Start : Starting Position from where the Deletion/Insertion takes place

   Length : Number of characters to be Deleted

   Expression2 : Character String to be Replaced.

   Example:  Select STUFF('SQL Server 2005',12,4,'2008') NEW_VERSION

    Result :

8. LEN() : Returns the number of characters of the specified string

    Syntax:   LEN(Expression)

   Expression : It can be either Character string or Column, of which the length has to be calculated

   Example:  Select LEN('SQL Server 2008') Length

    Result :

9. REVERSE() : Returns the Reverse of the string expression.

    Syntax:   REVERSE(Expression)

   Expression : It can be either Character string or Column or Binary Data.

   Example:  Select REVERSE('1234567890') 
                       GO
                       Select REVERSE('REVERSE') 

    Result :

10. REPLICATE() :  Repeats the character expression a specified number of times

    Syntax:   REPLICATE(Expression,Integer)

   Expression : It can be either Character string or Column or Binary Data.      

   Example:  Select REPLICATE('$',10)  Repetition

    Result :