Indexing in sql server

ASP.NET MVC provides Action Filters

 Filters in ASP.NET MVC are a way to apply cross-cutting logic at the controller level. 
ASP.NET MVC provides Action Filters for executing filtering logic either before or after an action method is called. Action Filters are custom attributes that provide declarative means to add pre-action and post-action behavior to the controller's action methods.

public class CustomAuthenticationAttribute : FilterAttribute, IAuthenticationFilter
    {
        void IAuthenticationFilter.OnAuthentication(AuthenticationContext filterContext)
        {
            filterContext.Controller.ViewBag.OnAuthentication = "IAuthenticationFilter.OnAuthentication filter called";
        }

        void IAuthenticationFilter.OnAuthenticationChallenge(AuthenticationChallengeContext filterContext)
        {
            filterContext.Controller.ViewBag.OnAuthenticationChallenge = "IAuthenticationFilter.OnAuthenticationChallenge filter called";
        }
    }

******************************************************************
 public class CustomAuthorizationAttribute : FilterAttribute, IAuthorizationFilter
    {
        void IAuthorizationFilter.OnAuthorization(AuthorizationContext filterContext)
        {
            filterContext.Controller.ViewBag.OnAuthorization = "IAuthorizationFilter.OnAuthorization filter called";
        }
    }
*********************************************************************
   void IActionFilter.OnActionExecuted(ActionExecutedContext filterContext)
        {
            filterContext.Controller.ViewBag.OnActionExecuted = "IActionFilter.OnActionExecuted filter called";
        }

        void IActionFilter.OnActionExecuting(ActionExecutingContext filterContext)
        {
            filterContext.Controller.ViewBag.OnActionExecuting = "IActionFilter.OnActionExecuting filter called";
        }
***************************************************************************
 public class CustomResultAttribute : FilterAttribute, IResultFilter
    {
        void IResultFilter.OnResultExecuted(ResultExecutedContext filterContext)
        {
            filterContext.Controller.ViewBag.OnResultExecuted = "IResultFilter.OnResultExecuted filter called";
        }

        void IResultFilter.OnResultExecuting(ResultExecutingContext filterContext)
        {
            filterContext.Controller.ViewBag.OnResultExecuting = "IResultFilter.OnResultExecuting filter called";
        }
    }
********************************************************
 public class CustomExceptionAttribute : FilterAttribute, IExceptionFilter
    {      
        void IExceptionFilter.OnException(ExceptionContext filterContext)
        {
            filterContext.Controller.ViewBag.OnException = "IExceptionFilter.OnException filter called";
        }
    }
****************************************************************
Now create a controller
 public class HomeController : Controller
    {
        [CustomAuthenticationAttribute]
        [CustomAuthorization]
        [CustomAction]
        [CustomResultAttribute]
        [CustomExceptionAttribute]
        public ActionResult Index()
        {

          //  throw new Exception("Dummy Exception");
            ViewBag.Message = "Index Action of Home controller is being called.";
            return View();
        }
}
*********************************************
Below is the View
@{
    ViewBag.Title = "Index";
}
<h2>
    Index
</h2>
<ul>
    <li>@ViewBag.OnAuthentication</li>
    <li>@ViewBag.OnAuthorization</li>
    <li>@ViewBag.OnActionExecuting</li>
    <li>@ViewBag.OnActionExecuted</li>
    <li>@ViewBag.OnAuthenticationChallenge</li>
    <li>@ViewBag.OnResultExecuting</li>
    <li>@ViewBag.OnResultExecuted</li>
</ul>

*****************************

Now check the order

1. IAuthenticationFilter.OnAuthentication
2. IAuthorizationFilter.OnAuthorization
3. IActionFilter.OnActionExecuting
4.  public ActionResult Index() {}
5.  void IActionFilter.OnActionExecuted
6. IAuthenticationFilter.OnAuthenticationChallenge
7. IResultFilter.OnResultExecuting
8. IResultFilter.OnResultExecuted

************************************************

Index

• IAuthenticationFilter.OnAuthentication filter called
• IAuthorizationFilter.OnAuthorization filter called
• IActionFilter.OnActionExecuting filter called
• IActionFilter.OnActionExecuted filter called
• IAuthenticationFilter.OnAuthenticationChallenge filter called
• IResultFilter.OnResultExecuting filter called
• ************************************************************

Now throw exception

Now create a controller
 public class HomeController : Controller
    {
        [CustomAuthenticationAttribute]
        [CustomAuthorization]
        [CustomAction]
        [CustomResultAttribute]
        [CustomExceptionAttribute]
        public ActionResult Index()
        {

           throw new Exception("Dummy Exception");
            ViewBag.Message = "Index Action of Home controller is being called.";
            return View();
        }
}

1. IAuthenticationFilter.OnAuthentication
2. IAuthorizationFilter.OnAuthorization
3. IActionFilter.OnActionExecuting
4.  public ActionResult Index(){}
5. IActionFilter.OnActionExecuted
6. IExceptionFilter.OnException

Then Yellow Screen of Death

Can foreign key reference non primary key

1. A FOREIGN KEY constraint does not have to be linked only to aPRIMARY KEY constraint in another table; it can also be defined toreference the columns of a UNIQUE constraint in another table. So in your case if you make AnotherID unique, it will be allowed.

CREATE TABLE table3
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   SomeData VARCHAR(100) NOT NULL
)

CREATE TABLE table4
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   MoreData VARCHAR(30) NOT NULL,

   CONSTRAINT fk_table4_table3 FOREIGN KEY (AnotherID) REFERENCES table3 (AnotherID)
)

There are no primary or candidate keys in the referenced table 'table3' that match the referencing column list in the foreign key 'fk_table4_table3'.


/****** Object:  Table [dbo].[table1]    Script Date: 7/7/2015 11:28:36 AM ******/
CREATE TABLE table3
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT Unique NOT NULL,
   SomeData VARCHAR(100) NOT NULL
)

CREATE TABLE table4
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   MoreData VARCHAR(30) NOT NULL,

   CONSTRAINT fk_table4_table3 FOREIGN KEY (AnotherID) REFERENCES table3 (AnotherID)
)

1. 
   

2. 
   

Remove duplicate rows from table and Copy one table to another table

To Copy One table to another

create table #TT_Employee

(Emp_id int,

Emp_name varchar(20))

insert into #TT_Employee values(1,'Andy')

insert into #TT_Employee values (1,'Andy')

insert into #TT_Employee values(2,'Bill')

insert into #TT_Employee values(2,'Bill')

insert into #TT_Employee values (2,'Bill')

insert into #TT_Employee values (3,'Chris')




-- Method 1 : Select * into table1 from table2
 -- Method 2 : Insert into table2 select * from table1Insert the distinct rows from the duplicate rows table to new temporary table. Delete data from table which has duplicate rows then insert the distinct rows from the temporary table as shown below.
select distinct * into #tmp from #TT_Employee
truncate table #TT_Employee
insert into #TT_Employee select * from #tmp
drop table #tmp

If you want to consider only few columns in a table for duplication criteria to delete rows then Method 1 will not work(in our example, if EMDup table has more than 2 columns and delete rows if empid and name repeats more than one time).

In this case, Add an identity column as a serial number that acts as a row unique identifier(auto incremental ascending order).Then get the Rank against each empid,name. If Rank is greater than 1 means it is a duplicate row and delete the same. After deleting the duplicated rows, remove the identity column which is used for rank. See the below example.

 alter table EmpDup add  sno int identity(1,1)
    delete from EmpDup where sno in
    (
    select sno from (
    select *,
    RANK() OVER ( PARTITION BY empid,name ORDER BY sno DESC )rank
    From EmpDup
    )T
    where rank>1
    )

    alter table EmpDup 
    drop  column sno

Method 3:

Using "Delete Top( )" clause:

If you want to delete duplicate rows for a particular empid then use "Top()" command in delete query as shown below.

    delete top(2) From EmpDup where empid=2

OR

    delete top(select count(*)-1 From EmpDup x where x.empid=2) From EmpDup where empid=2
    

Method 4:

If you want to delete all the rows if the selected columns repeated more than 1 time then use below query.

Query to delete 3 duplicated rows (in our example table) or repeated more than 1 time.

    delete from EmpDup where EmpID in(select EmpID from EmpDup group by EmpId having
    count(*) >1)
    

http://www.c-sharpcorner.com/UploadFile/rohatash/using-case-with-select-and-update-statement-in-sql-server-20/

Update one row value with other value in second row.

create table #my
(EmpId int,
sex varchar(100))
insert into #my (EmpId ,sex)
select 1,'Male'
Union
select 2,'female'
Union
select 3, 'Male'
Union
select 4, 'Male'
Union
select 5, 'female'
select * from #my

update #my SET sex =case 
when sex='Male' then 'Female' 
when sex='female' then 'Male'
end

 

Difference between Temporary table and table variable

Table variables are alternative of temporary tables. As,for instance, temporary tables are table variables store a set of record. The syntax of table variable shown below: We can insert values using INSERT INTO clause in table variable. We can use SELECT to fetch data and UPDATE, DELETE for manipulate data in table variable. In table variable the constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY constraints are not allowed. Table variable Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. We can return a table variable from user-defined function.

It is just like temporary table (#EmpForm) but some differences are there. A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable we create with a DECLARE statement. Temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Table variable data will not be rolled back when a transaction rolled back. Temporary table data will be rolled back when a transaction rolled back. The variable will no longer exist after the procedure exits, there will be no table to clean up with a DROP statement. Temporary tables are automatically dropped when they go out of scope unless explicitly dropped using DROP TABLE. The restricted scope of a table variable gives SQL Server some liberty to perform optimizations. A table variable will generally use fewer resources than a temporary table. Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. We cannot use a table variable as an input or an output parameter. We cannot truncate a table variables whereas we can truncate a temporary table. We can not drop table variable as it automatically drop as it goes out of scope. We can not run separate command first create and insert and update it should execute in single shot. Whereas Temporary table we can do separate we can see it in temp db also.

Temp Table and Table Variable — both are created in TempDB and not in memory.

SELECT name,type_desc ,create_date  FROM tempdb.sys.objects 

WHERE type = 'U' 

order by create_date desc

Both present in the temp db

drop table #TTEmployee

drop table @TVEmployee

Temporary table should be explicitly drooped. but not table variable.

For more

http://www.c-sharpcorner.com/UploadFile/b31398/temp-table-vs-table-variable/

http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/