Use Row_number() in SQL Server

SQL Scenario -I

Table A Table B
Id Id
1 1
2 -
- 3
4 4
Expected Output:
Id Description
1 1 Has both in tables
2 2 Has in A table Only
3 3 Has in B table Only

4 4 Has both in tables.

create view UV_Result
as
select a.id,a.id + '  ' + 'Has both in tables' as [DESCRIPTION] from table1 a,table2 b where a.id=b.id and a.id<>'-'

Create view UV_TestA
as
select Distinct id as [TableAID],
      RowNum = row_number() OVER (ORDER BY (SELECT 0))
from  table1 a
   

Create view UV_TestB
as
 select   id,
     RowNum = row_number() OVER (ORDER BY (SELECT 0))
from     table2

create view uv_Final
as
select id,DESCRIPTION from UV_Result
union ALL
select rownum,CONVERT(varchar(50),CONVERT(bigint,rownum)) +' ' + 'HAS TABLE B'  from UV_TestA where TableAId='-'
UNION ALL
select rownum,CONVERT(varchar(50),CONVERT(bigint,rownum)) +' ' + 'HAS TABLE A'  from UV_TestB where Id='-'

select * from uv_Final order by id

Define MVC Action Filters

Define MVC Action Filters

Action Filters are used in cases where we need to perform some logic either before an action method is called or after an action method runs.

Use of System.XML.XLINQ

It contains classes to provide functionality to LINQToXML

Use of System.Data.DLINQ

Provide functionality to Work with LINQToSQL.

Qualifiers in LINQ

They are LINQ Extension Methods return Boolean Value.

1. All
2. Any
3. Contains
4. SquenceEqual

Main Components in LINQ

There are three Main Components in LINQ

1.  Standard Query Operator
2.  Language Extension
3.  LINQ Provider

add Minutes to DateTime in Sql Server

We can use DATEADD() function like below to add minutes to DateTime in Sql Server. DATEADD() functions first parameter value can be minute or mi or n all will return the same result. Below example shows how we can add two minutes to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(minute,2,GETDATE()) 'Now + 2 Minutes'
SELECT GETDATE() 'Now',
           DATEADD(mi,2,GETDATE()) 'Now + 2 Minutes'
SELECT GETDATE() 'Now',
           DATEADD(n,2,GETDATE()) 'Now + 2 Minutes'

add Hours to DateTime in Sql Server

                We can use DATEADD() function like below to add hours to DateTime in Sql Server.                
                DATEADD() functions first parameter value can be hour or hh all will return the same result. Below example shows how we can add two hours to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(hour,2,GETDATE()) 'Now + 2 Hours'
SELECT GETDATE() 'Now',
           DATEADD(hh,2,GETDATE()) 'Now + 2 Hours'

add Seconds to DateTime in Sql Server

               We can use DATEADD() function like below to add seconds to DateTime in Sql Server. DATEADD() functions first parameter value can be second or ss or s all will return the same result. Below example shows how we can add two seconds to Current DateTime in Sql Server:

SELECT GETDATE() 'Now',
           DATEADD(second,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
           DATEADD(ss,2,GETDATE()) 'Now + 2 Seconds'
SELECT GETDATE() 'Now',
           DATEADD(s,2,GETDATE()) 'Now + 2 Seconds'

IIF funciton in SQL Server 2012 and give an Example

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.

Create a Table

CREATE TABLE SimpleTable (ID INT, NAME VARCHAR(10))
GO
Insert some value into table
INSERT INTO SimpleTable (ID, NAME)
SELECT 1, 'LAKSHMI'
UNION ALL
SELECT 2, 'NARAYANAN'
UNION ALL
SELECT 3, 'NARAYANAN'
GO

UPDATE SimpleTable
SET Gender = IIF(NAME = 'NARAYANAN', 'LAKSHMI', 'NARAYANAN')
GO
SELECT *
FROM SimpleTable
GO