Dot Net Tricks | Dot Net Tips

Types of SQL Keys We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views. Super Key Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys. Candidate Key A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Primary Key Constraints Primary key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table. It can not accept null, duplicate values. Primary key constraint at column level CREATE TABLE table_name ( col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY, col2 datatype ); Primary key constraint at table level ALTER TABLE table_name ADD[CONSTRAINT constraint_name] PRIMARY KEY (col1,col2)

We can create temporary table(s) using stored procedure for intermediate data manipulation. Below code is used to remove duplicate records from tables using procedure. CREATE TABLE Employee_Test ( Emp_ID int identity, Emp_Name varchar (55) NULL, Emp_Sal decimal (10, 2) NULL, Emp_Designation varchar (20) NULL ) Go INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Mohan',15000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Mohan',15000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Riyaz',16000,'SE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Monu',27000,'SSE') INSERT INTO Employee_Test(Emp_Name,Emp_Sal,Emp_Designation)VALUES('Amit',12000,'SE') GO --Now see table SELECT * FROM dbo.Employee_Test

Sometimes we required to remove duplicate records from a table although table has a UniqueID Column with identity. In this article, I would like to share a best way to delete duplicate records from a table in SQL Server. Suppose we have below Employee table in SQL Server. CREATE TABLE dbo.Employee ( EmpID int IDENTITY(1,1) NOT NULL, Name varchar(55) NULL, Salary decimal(10, 2) NULL, Designation varchar(20) NULL ) The da

SQL Injection arises since the fields available for user input allow SQL statements to pass through and query the database directly. SQL Injection: A Simple Example For explaining this issue, Let's create a table "tbluser" for describing the SQL Injection Attack. Create table tbluser ( userName varchar(50) primary key, userpwd varchar(50), address varchar(100) ) insert into tbluser(userName,userpwd,address)values('mohan@gmail.com','123456','Delhi'); insert into tbluser(userName,userpwd,address)values('shailendra@gmail.com','123456','Noida'); insert into tbluser(userName,userpwd,address)values('jitendra@gmail.com','123456','Gurgaon'); insert into tbluser(userName,userpwd,address)values('bipul@gmail.com','123456','Delhi'); select * from tbluser

Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers. Types of DML Triggers After Trigger (using FOR/AFTER CLAUSE) This trigger fires after SQL Server completes the execution of the action successfully that fired it. Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Group By Clause Group By clause is used for grouping the records of the database table(s).This clause creates a single row for each group and this process is called aggregation. To use group by clause we have to use at least one aggregate function in Select statement. We can use group by clause without where clause. Syntax for Group By Clause SELECT Col1, Col2, Aggreate_function FROM Table_Name WHERE Condition GROUP BY Col1, Col2 Let's see how the Group By clause works. Suppose we have a table StudentMarks that contains marks in each subject of the student. Create table StudentMarks

Suppose we have employee name and salary as shown in below fig. Query to get…

Remove all Tables -- drop all user defined tables EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" Remove all User-defined Stored Procedures -- drop all user defined stored procedures Declare @procName varchar(500) Declare cur Cursor For Select [name] From sys.objects where type = 'p' Open cur Fetch Next From cur Into @procName While @@fetch_status = 0 Begin Exec('drop procedure ' + @procName) Fetch Next From cur Into @procName End

CREATE FUNCTION ConvertListToTable ( @Delimiter    nvarchar(5), @List        nvarchar(max) ) RETURNS @TableOfValues TABLE ( RowNumber  …