How to RESET identity columns in SQL Server
One
way is...
truncate table [table_name]
-- for example
truncate table product
But the truncate command fails to delete the data if
there is a relationship given to the table and the identity column is not
reset.
The other way is...
In this case, first
you need to delete data from the child and the master table.
After deleting data,
fire this command and it will reset your identity column to 0.
DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
-- for example
DBCC CHECKIDENT('product', RESEED, 0)
insert values to identity column in SQL Server
CREATE TABLE Customer(ID int IDENTITY, Name varchar(100), Address varchar(200))
INSERT INTO Customer(ID,Name,Address)
VALUES(1,'Shakham','Delhi')
While will get error
SET IDENTITY_INSERT Customer ON (Allow)
SET IDENTITY_INSERT Customer OFF (DisAllow)
Usually, we use this trick
when we have deleted some rows from the table and we want the data in a
sequence.
What is transactions, how will be used in stored procedure
How will handle exceptions in stored procedure
Explain what is CTE in SQL Server
Read:Here ,Here
Difference between CTE and Temp Table and Table Variable
Delete from tables involved in a SQL Join
Deletes all the HR department employees.
Delete E From (Employees E Inner Join Departments D On E.DeptId = D.Id and D.Name = 'HR')
OR
Delete Employees From (Employees join Departments on Employees.DeptId = Departments.Id) where Departments.Name = 'HR'
Difference between inner join and left join
INNER JOIN returns only the matching rows between the tables involved in the JOIN. Notice that, Pam employee record which does not have a matching DepartmentId in departments table is eliminated from the result-set.
SELECT EmployeeName, DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
LEFT JOIN returns all rows from left table including non-matching rows. Notice that, Pam employee record which does not have a matching DepartmentId in departments table is also included in the result-set.
SELECT EmployeeName, DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
SQL Query to get the name of the Department that has got the maximum number of Employees.
SQL query that retrieves the department name with maximum number of employees
SELECT TOP 1 DepartmentName
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName
ORDER BY COUNT(*) DESC
SQL query to find rows that contain only numerical data
Select Value from TestTable Where ISNUMERIC(Value) = 1
ISNUMERIC function returns 1 when the input expression evaluates to a valid numeric data type, otherwise it returns 0. For the list of all valid numeric data types in SQL Server
SQL query to find employees hired in last n months
Select * FROM Employees Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N
-- Replace N with number of months
Delete duplicate rows in sql
WITH EmployeesCTE AS
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) ASRowNumber
FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1
How to get second-highest salary employee in a table?
select max(Salary) from Employee where Salary not in (Select Max(Salary) from Employee)
or select max(Salary) from Employee where Salary < (Select Max(Salary) from Employee)
If want to get nth-highest salary where n=1,2,3......n
SELECT TOP 1 salary FROM (
SELECT distinct TOP n salary FROM employee ORDER BY salary DESC ) AS emp ORDER BY salary ASC
To find nth highest salary using CTE
WITH RESULT AS
(
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM EMPLOYEES
)
SELECT TOP 1 SALARY FROM RESULT WHERE DENSERANK = N
To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.
Note:The below query will only work if there are no duplicates.
WITH RESULT AS
(
SELECT SALARY,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
FROM EMPLOYEES
)
SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3
What is SQL Injection?
SQL Injection is one of the many web attack mechanisms used by hackers to steal data from organizations.
It is the type of attack that takes advantage of improper coding of web applications that allows hacker to inject SQL commands into say a login form to allow them to gain access to the data held within your database.
An In-depth Explanation
SQL Injection is the hacking technique which attempts to pass SQL commands (statements) through a web application for execution by the backend database. If not sanitized properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.
Ex:- login pages, support and product request forms, feedback forms, search pages, shopping carts and the general delivery of dynamic content, shape modern websites and provide businesses with the means necessary to communicate with prospects and customers.
These website features are all susceptible to SQL Injection attacks which arise because the fields available for user input allow SQL statements to pass through and query the database directly.
Example of an SQL Injection Attack
SELECT id FROM logins WHERE username = 'username' AND password = 'password’
If the variables username and password are requested directly from the user’s input, this can easily be compromised.
Suppose that we gave Joe as a username and that the following string was provided as a password: anything' OR 'x'='x
SELECT id FROM logins WHERE username = 'Joe' AND password = 'anything' OR 'x'='x'
As the inputs of the web application are not properly sanitized, the use of the single quotes has turned the WHERE SQL command into a two-component clause.
The 'x'='x' part guarantees to be true regardless of what the first part contains.
This will allow the attacker to bypass the login form without actually knowing a valid username/password combination.
How do I prevent SQL Injection attacks?
Firewalls and similar intrusion detection mechanisms provide little defense against full-scale web attacks.
By passing data through parameters, stored procedures and Table adapters
EX:-
SELECT id FROM logins WHERE username = @username AND password =@password
