Sql Server Interview Questions
Advance Difference
Difference between TRUNCATE and DELETE commands
difference
between a Composite Index and a Covering Index
Difference between CTE and Temp Table and Table Variable
Temp Table or Table variable or CTE are commonly used for
storing data temporarily in SQL Server. In this article, you will learn the
differences among these three.
CTE
CTE stands for Common Table expressions. It was introduced with
SQL Server 2005. It is a temporary result set and typically it may be a result
of complex sub-query. Unlike temporary table its life is limited to the current
query. It is defined by using WITH statement. CTE improves readability and ease
in maintenance of complex queries and sub-queries. Always begin CTE with
semicolon.
A sub query without CTE is given below :
1.
SELECT * FROM (
2.
SELECT Addr.Address, Emp.Name, Emp.Age From Address
Addr
3.
Inner join Employee Emp on Emp.EID = Addr.EID) Temp
4.
WHERE Temp.Age > 50
5.
ORDER BY Temp.NAME
By using CTE above query can be re-written as follows :
1.
;With
CTE1(Address, Name, Age)--Column
names for CTE, which are optional
2. AS
3. (
4.
SELECT Addr.Address, Emp.Name, Emp.Age from Address
Addr
5.
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
6. )
7.
SELECT * FROM CTE1 --Using
CTE
8.
WHERE CTE1.Age > 50
9.
ORDER BY CTE1.NAME
When to use CTE
1.
This is used to store
result of a complex sub query for further use.
2.
This is also used to
create a recursive query.
Temporary Tables
In SQL Server, temporary tables are created at run-time and you
can do all the operations which you can do on a normal table. These tables are
created inside Tempdb database. Based on the scope and behavior temporary
tables are of two types as given below-
1.
Local Temp Table
Local temp tables are only available to the SQL Server session
or connection (means single user) that created the tables. These are
automatically deleted when the session that created the tables has been closed.
Local temporary table name is stared with single hash ("#") sign.
1.
CREATE TABLE #LocalTemp
2.
(
3.
UserID int,
4.
Name varchar(50),
5.
Address varchar(150)
6.
)
7.
GO
8.
insert into #LocalTemp values ( 1, 'Shailendra','Noida');
9.
GO
10.
Select * from #LocalTemp
The scope of Local temp table exist to the current session of
current user means to the current query window. If you will close the current
query window or open a new query window and will try to find above created temp
table, it will give you the error.
2.
Global Temp Table
Global temp tables are available to all SQL Server sessions or
connections (means all the user). These can be created by any SQL Server
connection user and these are automatically deleted when all the SQL Server
connections have been closed. Global temporary table name is stared with double
hash ("##") sign.
1.
CREATE TABLE ##GlobalTemp
2.
(
3.
UserID int,
4.
Name varchar(50),
5.
Address varchar(150)
6.
)
7.
GO
8.
insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
9.
GO
10.
Select * from ##GlobalTemp
11.
Global temporary tables are visible to all SQL Server
connections while Local temporary tables are visible to only current SQL Server
connection.
Table Variable
This acts like a variable and exists for a particular batch of
query execution. It gets dropped once it comes out of batch. This is also
created in the Tempdb database but not the memory. This also allows you to
create primary key, identity at the time of Table variable declaration but not
non-clustered index.
1. GO
2.
DECLARE @TProduct TABLE
3. (
4.
SNo INT IDENTITY(1,1),
5. ProductID INT,
6. Qty INT
7. )
8.
--Insert
data to Table variable @Product
9.
INSERT INTO @TProduct(ProductID,Qty)
10. SELECT DISTINCT
ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC
11. --Select data
12. Select * from
@TProduct
13.
14. --Next batch
15. GO
16. Select * from
@TProduct --gives error in next
batch
17.
Note
1.
Temp Tables are
physically created in the Tempdb database. These tables act as the normal table
and also can have constraints, index like normal tables.
2.
CTE is a named
temporary result set which is used to manipulate the complex sub-queries data.
This exists for the scope of statement. This is created in memory rather than
Tempdb database. You cannot create any index on CTE.
3.
Table Variable acts
like a variable and exists for a particular batch of query execution. It gets
dropped once it comes out of batch. This is also created in the Tempdb database
but not the memory.
Dept wise highest salary:
Using inclause:
SELECT e.name e_name,d.name d_name,e.salary FROM employee e
INNER JOIN department d ON e.deptid=d.id WHERE e.salary IN(SELECT MAX(salary)
FROM employee GROUP BY deptid);
Sub Query:
SELECT e.name e_name,d.name d_name,e1.salary FROM employee e1
INNER JOIN (SELECT MAX(salary) salary,deptid FROM employee GROUP BY deptid) e2
ON e1.salary=e2.salary AND e1.deptid=e2.deptid INNER JOIN department d ON
e1.deptid=d.id
Highest Salary with Department Name:
SELECT e.id,e.name,d.dname,MAX(e.salary) as highest_salary FROM
emp as E LEFT JOIN department as D ON D.id=E.d_id GROUP BY E.d_id
This simple query give you all departments list and it’s
employee with max salary if exist or null otherwise:
SELECT department.name,employee.name,MAX(employee.salary) FROM
department LEFT OUTER JOIN employee ON(employee.department_ID=department.id)
GROUP BY department.id
Employee:
EmpNo
|
Ename
|
DeptNo
|
Salary
|
1
|
A
|
10
|
10
|
2
|
B
|
20
|
20
|
3
|
C
|
30
|
30
|
4
|
D
|
10
|
100
|
5
|
E
|
20
|
200
|
6
|
F
|
30
|
300
|
7
|
G
|
10
|
200
|
8
|
H
|
20
|
201
|
9
|
I
|
30
|
200
|
Dept:
DeptNo
|
10
|
20
|
30
|
One table joins_tb and second table joins1_tb
Joins_tb Joins1_tb
Inner Join:
select a.id,b.id from joins_tb a inner join Joins1_tb b on
a.id=b.id
Out Put:
id
|
id
|
1
|
1
|
2
|
2
|
3
|
3
|
4
|
4
|
4
|
4
|
Full Outer Join:
select a.id,b.id from
joins_tb a Full outer join Joins1_tb b on a.id=b.id
Out Put:
id
|
id
|
1
|
1
|
2
|
2
|
3
|
3
|
4
|
4
|
4
|
4
|
NULL
|
5
|
NULL
|
6
|
Left Outer Join:
select a.id,b.id from
joins_tb a Left outer join Joins1_tb b on a.id=b.id
Out Put:
1
|
1
|
2
|
2
|
3
|
3
|
4
|
4
|
4
|
4
|
Right outer Join:
select a.id,b.id from
joins_tb a right outer join Joins1_tb b on a.id=b.id
Out Put:
id
|
id
|
1
|
1
|
2
|
2
|
3
|
3
|
4
|
4
|
4
|
4
|
NULL
|
5
|
NULL
|
6
|
Equi Join:
select a.id,b.id from
joins_tb a join Joins1_tb b on a.id=b.id
Out Put:
id
|
id
|
1
|
1
|
2
|
2
|
3
|
3
|
4
|
4
|
4
|
4
|
Cross Join:
select
joins_tb.id,Joins1_tb.id from joins_tb
cross join Joins1_tb
Out Put:
Delete
Duplicate Records from the table
WITH TempUsers (EmpId,
EmpName, duplicateRecordCount)
AS
(
SELECT EmpId, EmpName,
ROW_NUMBER()OVER(PARTITION BY EmpId, EmpName ORDER BY EmpId) AS duplicateRecordCount
FROM dbo.tbl_Employeedata
)
DELETE
FROM TempUsers
WHERE duplicateRecordCount > 1
GO
AS
(
SELECT EmpId, EmpName,
ROW_NUMBER()OVER(PARTITION BY EmpId, EmpName ORDER BY EmpId) AS duplicateRecordCount
FROM dbo.tbl_Employeedata
)
DELETE
FROM TempUsers
WHERE duplicateRecordCount > 1
GO
-------------------------------------------------------------------------------------------------------
OR
Delete duplicate records:
select
* from ssistest
where datediff(yy, doj, convert (varchar(10), SYSDATETIME(), 101))>5
delete from SSISTest where EmpID
in(select EmpID from SSISTest group by empid having COUNT(*)>1) -- ALL duplicate delete
Delete duplicate records
with one record will show:
how to delete only one
------------------------------------------------------------------------------------------------------------------------------------------
WITH numbered AS (
SELECT ROW_NUMBER () OVER (PARTITION BY
name, age ORDER BY name, age) AS _dupe_num FROM deletetop3
WHERE 1=1
)
DELETE FROM numbered WHERE
_dupe_num > 1;-- one duplicate delete
---------------------------------------------------------------------------------------------------------------------------------
Second highest
salary Query:
-----------------------------------------------------------------------------
SELECT
EmpSal
FROM SSISTest
WHERE EmpSal = (SELECT MIN(EmpSal)
FROM (SELECT DISTINCT TOP (2) EmpSal
FROM SSISTest
ORDER BY EmpSal DESC) T);
OR
OR
-------------------------------------------------------------------------------------------------------------------
;WITH
Salaries AS
(
SELECT
empname, empsal,
DENSE_RANK() OVER(ORDER BY empsal DESC) AS 'SalaryRank'
FROM
SSISTest
)
SELECT
EmpName, EmpSal
FROM
Salaries
WHERE
SalaryRank = 2
----------------------------------------------------------------------------------------------------------------------------------------------------------
Write a query to display middle
record in emp table
WITH
Numbered AS
(
SELECT
*, COUNT(*) OVER () AS Cnt,
ROW_NUMBER() OVER (ORDER BY empid) AS RowNum
FROM
SSISTest
)
SELECT
empid,empname,empsal
FROM
Numbered
WHERE
RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
;
Difference between row_number(),rank(),dense_rank():
-- create table
CREATE
TABLE Salaries
(
Names
VARCHAR(1),
SalarY
INT
)
GO
-- insert data
INSERT
INTO Salaries SELECT
'A',5000
UNION ALL SELECT
'B',5000
UNION ALL SELECT
'C',3000
UNION ALL SELECT
'D',4000
UNION ALL SELECT
'E',6000
UNION ALL SELECT
'F',10000
GO
-- Test the data
SELECT
Names, Salary
FROM
Salaries
SELECT
names
,
salary
,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
,rank () OVER (ORDER BY salary DESC) as RANK
,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
FROM
salaries
Names
|
Salary
|
ROW_NUMBER
|
RANK
|
DENSE_RANK
|
F
|
10000
|
1
|
1
|
1
|
E
|
6000
|
2
|
2
|
2
|
A
|
5000
|
3
|
3
|
3
|
B
|
5000
|
4
|
3
|
3
|
D
|
4000
|
5
|
5
|
4
|
C
|
3000
|
6
|
6
|
5
|
Table:
Gender
|
Male
|
Male
|
Male
|
Female
|
Female
|
Change Male to Female and
Female to Male
UPDATE
shuffling_tb
SET
GENDER = CASE
WHEN
GENDER='Male' THEN 'Female'
WHEN
GENDER='Female' THEN 'Male'
ELSE
GENDER
END
WHERE
with CASE Statement:
select * From emp_master
where emp_last_name=
case emp_first_name
when 'test' then 'test'
when 'Mr name' then 'name'
ELSE
emp_first_name
end
Identity Off and
Insert:
create
table emp_tb(empid
int,
empname
varchar(50),
empsal
decimal(4,2))
select
* from emp_tb
insert
into emp_tb values(2,'bbb','100')
delete
from emp_tb where
empid=5
SET
IDENTITY_INSERT emp_tb ON
INSERT emp_tb(empid, empname,empsal) VALUES (5,'eee','100')
SET IDENTITY_INSERT emp_tb OFF
SELECT * FROM emp_tb
GO
insert into emp_tb values('GGG','200')
My table has data with a single column:
1
2
3
4
5
6
7
8
9
10
And I want output like this:
1 2 3 4 5-first row
6 7 8 9 10 - second row
WITH CTE AS
(
SELECT C,
(ROW_NUMBER() OVER (ORDER BY C) -1)%5 AS Col,
(ROW_NUMBER() OVER (ORDER BY C) -1)/5 AS Row
FROM YourTable
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX(C) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row
Table
a | b
------------
x | 1
x | 4
y | 6
y | 1
so that output is :
a | ListOfB
------------
x | 1, 4
y | 6, 1
select distinct a,
stuff(
(
select ','+ cast(b as varchar(10))
from table1 t2
where t1.a = t2.a for XML path('')
),1,1,'') ListOfB
from table1 t1
Table1
Col1 Col2 Col3
a1 1 4
a2 2 5
a3 3 6
a4 7 8
Table2
Col1 Col4
a1 10
a1 11
a1 22
a2 10
a2 11
a3 19
a3 22
a3 23
a4 15
Query output:
Col1 Col2 Col3 Col4
a1 1 4 10,11,22
a2 2 5 10,11
a3 3 6 19,22,23
a4 7 8 15
select t1.col1,
t1.col2,
t1.col3,
left(t2.col4, len(t2.col4)-1) col4
from table1 t1
cross apply
(
select cast(t2.Col4 as varchar(10)) + ', '
from Table2 t2
where t1.col1 = t2.col1
FOR XML PATH('')
) t2 (col4)
OR
select t1.col1,
t1.col2,
t1.col3,
STUFF(
(SELECT ', ' + cast(t2.Col4 as varchar(10))
FROM Table2 t2
where t1.col1 = t2.col1
FOR XML PATH (''))
, 1, 1, '') AS col4
from table1 t1
With Comma separated:
DECLARE @listStr VARCHAR(MAX)
SET @listStr = '' -- If you do not set
this as blank (' ') then it'll return NULL value. Try it yourself
SELECT @listStr = TableField + ', ' + @listStr FROM TableName
SELECT @listStr
How we will found count of the any sentence/string:
“This is the my first name” in this sentence is how many times there :
out put will be 2
SELECT (LEN(Sentence) - LEN(REPLACE(Sentence, 'is', ''))) / LEN('is') FROM String_t
*
Sentence – This is table column name.
Self Join eg..,
EmployeeID
|
Name
|
ManagerID
|
1
|
Sam
|
10
|
2
|
Harry
|
4
|
4
|
Manager
|
NULL
|
10
|
AnotherManager
|
NULL
|
SELECT
*
FROM
Employee e1
INNER
JOIN Employee e2
ON
e1.ManagerID = e2.EmployeeID
SELECT a.empid,a.empname,b.empname
[Managername],c.empname [HrName] FROM employee_tb a
INNER JOIN employee_tb b ON
a.managerid=b.employeeid
INNER JOIN employee_tb c ON
a.hrmanagerid=c.employeeid
1.
If there
are two tables emp1 and emp2, and both have common record. How can I fetch all
the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
(Select * from emp) Union (Select * from emp1)
2.
How to
fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
(Select * from emp) Intersect (Select * from emp1)
3.
How can
I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
(Select * from emp) Minus (Select * from emp1)
E1 table ID column name:1,2,3,4,5
E2 table ID column
name:11,12,13,14,15,16,5
(Select
* from e1) Union (Select * from e2) -- all unique records
output:1,2,3,4,5,11,12,13,14,15,16
(Select
* from e1) Union all (Select * from e2) -- all the records will
display
output:1,2,3,4,5,11,12,13,14,15,16,5
(Select
* from e1) Intersect (Select * from e2) -- matching with distinct
column
output:5
(Select
* from e1) except (Select * from e2) --First table not matching records
1,2,3,4
Merge Join:
MERGE
Production.ProductInventory AS [pi]
USING (
SELECT ProductID,
SUM(OrderQty) AS OrderQty
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.OrderDate = GETDATE()
GROUP BY ProductID
) AS src (ProductID, OrderQty) ON src.ProductID = [pi].ProductID
WHEN MATCHED AND src.OrderQty = 0
THEN DELETE;
WHEN MATCHED
THEN UPDATE SET [pi].Quantity = src.OrderQty
WHEN NOT MATCHED
THEN INSERT VALUES (src.ProductID, src.OrderQty)
USING (
SELECT ProductID,
SUM(OrderQty) AS OrderQty
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.OrderDate = GETDATE()
GROUP BY ProductID
) AS src (ProductID, OrderQty) ON src.ProductID = [pi].ProductID
WHEN MATCHED AND src.OrderQty = 0
THEN DELETE;
WHEN MATCHED
THEN UPDATE SET [pi].Quantity = src.OrderQty
WHEN NOT MATCHED
THEN INSERT VALUES (src.ProductID, src.OrderQty)
PIVOT:
USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3,
[257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
UNPIVOT:
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5
int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1,
Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3,
Emp4, Emp5)
)AS unpvt;
GO
VendorID Employee Orders
----------
---------- ------
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
Difference between cross join and Cartesian
product:
A cross join that does not have a 'where' clause gives the
Cartesian product. Cartesian product resultset contains no of rows in first
table multiplied by no of rows in second table.
Advantages of normalization
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
Disadvantages of normalization
1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for adhoc querying.
1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for adhoc querying.
Difference between Primary Key and Unique Key:
Primary Key:
i) Can be only one in a
table
ii) It never allows null
values
iii) Primary Key is
unique key identifier and can not be null and must be unique.
Unique Key:
i) Can be more than one
unique key in one table.
ii) Unique key can have
null values
iii) It can’t be
candidate key
iv) Unique key can be
null and may not be unique.
Candidate Key – A
Candidate Key can be any column or a combination of columns that can qualify as
unique key in database. There can be multiple Candidate Keys in one table. Each
Candidate Key can qualify as Primary Key.
Primary Key – A
Primary Key is a column or a combination of columns that uniquely identify a
record. Only one Candidate Key can be Primary Key.
One needs to be very
careful in selecting the Primary Key as an incorrect selection can adversely
impact the database architect and future normalization. For a Candidate Key to
qualify as a Primary Key, it should be Non-NULL and unique in any domain. I
have observed quite often that Primary Keys are seldom changed. I would like to
have your feedback on not changing a Primary Key.
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
1.
Function must return a value but in Stored Procedure it is
optional (Procedure can return zero or n values).
2.
Functions can have only input parameters for it whereas
Procedures can have input/output parameters.
3.
Function takes one input parameter it is mandatory but Stored
Procedure may take o to n input parameters..
4.
Functions can be called from Procedure whereas Procedures cannot
be called from Function.
Advance Difference
1.
Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE)
statement in it whereas Function allows only SELECT statement in it.
2.
Procedures cannot be utilized in a SELECT statement whereas
Function can be embedded in a SELECT statement.
3.
Stored Procedures cannot be used in the SQL statements anywhere
in the WHERE/HAVING/SELECT section whereas Function can be.
4.
Functions that return tables can be treated as another rowset.
This can be used in JOINs with other tables.
---------------------------------------------------------------------------------------------------------------
Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
SUBSTRING ( expression ,start , length )
expression
start
Is an integer or bigint expression that specifies where the
returned characters start. If start is
less than 1, the returned expression will begin at the first character that is
specified in expression. In this case, the number of
characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is
greater than the number of characters in the value expression, a zero-length
expression is returned.
length
Is a positive integer or bigint expression that specifies how many
characters of the expression will
be returned. If length is
negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of
characters in expression, the whole value expression beginning
at start
is returned.
USE AdventureWorks2012;
GO
SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial
FROM Person.Person
WHERE LastName like 'Barl%'
ORDER BY LastName;
SELECT
ROUND(column_name,decimals) FROM table_name;
Parameter
|
Description
|
column_name
|
Required. The field to
round.
|
decimals
|
Required. Specifies the
number of decimals to be returned.
|
DECLARE @myval decimal (5, 2);
SET @myval = 193.57;
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5));
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval));
The following example creates a table with an identity column
and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused
by a DELETE statement.
USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS
Title, CAST(e.SickLeaveHours AS char(1)) AS [Sick Leave]
FROM HumanResources.Employee e JOIN Person.Person p ON
e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID >5;
USE AdventureWorks2012;
GO
-- Create tool table.
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL
PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
GO
-- Create a gap in the identity values.
DELETE dbo.Tool
WHERE Name = 'Saw'
GO
SELECT *
FROM dbo.Tool
GO
-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO
-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
SELECT *
FROM dbo.Tool
GO
-- Drop products table.
DROP TABLE dbo.Tool
GO
Difference
between cross join and Full outer join
Cross Join :
No join conditions are specified.
Results in pairs of rows.
Results in Cartesian product of two tables.
Full Outer Join:
A combination of both left and right outer joins.
Results in every row from both of the tables , at least once.
Assigns NULL for unmatched fields.
Cross Join :
No join conditions are specified.
Results in pairs of rows.
Results in Cartesian product of two tables.
Full Outer Join:
A combination of both left and right outer joins.
Results in every row from both of the tables , at least once.
Assigns NULL for unmatched fields.
What is SQL Injection?
·
SQL Injection is an
attack in which attacker take the advantage of insecure application over
internet by running the SQL command against the database and to steal
information from it that too using GUI of the website.
·
This attack can happen
with the applications in which SQL queries are generated in the code.
·
The attacker tries to
inject their own SQL into the statement that the application will use to query
the database.
·
For example suppose the
below query string is used for a search feature in a website and a user may
have the inserted “Arpit” as the keyword to search. If in the code behind the
keyword is directly used into the SQL statement, it will be like.
String sql = “Select EmpName, City from EmployeeMaster where
EmpName like ‘%” + txtSearch.Text + “%’”;
But the attacker might enter the keyword like
‘ UNION SELECT name, type, id from sysobjects;--
But the attacker might enter the keyword like
‘ UNION SELECT name, type, id from sysobjects;--
This way attacker can get details of all the objects of the
application database and using that attacker can steal further information.
What is DBCC? Give few examples.
·
DBCC stands for Database
Consistency Checker.
·
These commands are used
to check the consistency of the database like validation tasks, maintenance and
status checks.
For example –
1. DBCC CHECKALLOC – It is used to check that all pages are
correctly allocated in database.
2. DBCC CHECKDB – It is used to check that
3. DBCC SQLPERF – It generates a report for the current usage of
4. DBCC SQLFILEGROUP – It used to check all the tables file group for any design.
2. DBCC CHECKDB – It is used to check that
3. DBCC SQLPERF – It generates a report for the current usage of
4. DBCC SQLFILEGROUP – It used to check all the tables file group for any design.
What is difference between View and Materialized view?
·
View result set doesn’t
save anywhere on disk and executes the query definition whenever they are
called, while materialized view are disk based and its result set table is
updated periodically.
·
Materialized view is
similar to regular views but the output of select query has been saved to a
table.
·
View shows the latest
data all the time while the materialized view only shows the fresh data after
its result table is updated either by setting a schedule or based on the change
in the underlying tables.
·
The performance of the
view depends on how good the selected statement the view has. If the select
statement has too many joins then it the view will perform poorly.
·
While in the case of
materialized view, we are querying a table, which may also be indexed, that
increase its performance.
SubQuery Example in SQL – Correlated vs Noncorrelated
SubQuery in SQL is a query inside another query. Some time to get
a particular information from database you may need to fire two separate sql
queries, subQuery is a way to combine or join them in single query. SQL query
which is on inner part of main query is called inner query while outer part of
main query is called outer query. for example in below sql query
SELECT name FROM City WHERE pincode IN (SELECT pincode FROM pin
WHERE zone='west')
section not highlighted is OUTER query while section highlighted
with grey is INNER query. In this SQL tutorial we will see both Correlated and
non-correlated sub-query and there examples, some differences between
correlated and noncorrelated subqueries and finally subquery vs join which is
classic debatable topic in SQL. By the way this SQL tutorial is next in series
of SQL and database articles in Javarevisited like truncate vs delete and 10
examples of SELECT queries. If you are
new here then you may find those examples interesting.
SubQuery Rules in SQL
Like any other concept in SQL, subquery also has some rules and
you can only embed one query inside another by following rules :
1. subquery can be used in insert statement.
2. subquery can be used in select statement as column.
3. subquery should always return either a scaler value if used
with where clause or value from a column if used with IN or NOT IN clause.
Before going to understand non-correlated and correlated subquery, let’s see the table
and data which we are going to use in this example. Until you have an
understanding of how table look like and what kind of data it stores its little
difficult to understand queries. In this subquery example we will use two table
Stock and Market. Stock holds different stocks and Market holds all stock exchanges
in the world.
mysql> select * from stock;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs
Group Inc | N |
| INDIGO | INDIGO
Airlines | NULL |
| INFY.BO | InfoSys
| BO |
| VOD.L | Vodafone Group
PLC | L |
+---------+-------------------------+--------------------+
6 rows in set (0.00 sec)
mysql> select from
Market;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock
Exchange | Japan |
| O | NASDAQ | United States |
| N | New York Stock
Exchange | United States |
| BO | Bombay Stock
Exchange | India |
+------+-------------------------+---------------+
4 rows in set (0.00 sec)
Noncorrelated subquery in SQL
There are two kind of subquery in SQL one is called non-correlated
and other is called correlated subquery. In non correlated subquery, inner
query doesn't depend on outer query and can run as stand alone query.Subquery
used along-with IN or NOT IN sql clause is good examples of Noncorrelated
subquery in SQL. Let's a noncorrelated subquery example to understand it
better.
NonCorrelated Subquery Example:
Difference between correlated and noncorrelated suqueryLet’s see
the query “Find all stocks from Japan”,
If we analyze this query we know that stock names are stored in Stock table
while Country name is stored in Market table, so we need to fire two query
first to get RIC for Japanese market and than all stocks which is listed on
that Market. we can combine these two queries into one sql query by using
subquery as shown in below example:
mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE =
(SELECT RIC FROM Market WHERE COUNTRY='Japan');
+---------+
| COMPANY |
+---------+
| Sony |
+---------+
1 row IN SET (0.02 sec)
Here part which is inside bracket is called inner query or
subquery. As you see in this example of subquery, inner query can run alone and
its not depended on outer query and that's why its called NonCorrelated query.
NonCorrelated Subquery Example with IN Clause SQL
NonCorrelated subquery are used along-with IN and NOT IN clause.
here is an example of subquery with IN clause in SQL.
SQL query: Find all stocks from United States and India
mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN
(SELECT RIC FROM Market WHERE COUNTRY='United States' OR COUNTRY= 'INDIA');
+-------------------------+
| COMPANY |
+-------------------------+
| Google Inc |
| Goldman Sachs GROUP Inc |
| InfoSys |
+-------------------------+
When Subquery is used along-with IN or NOT IN Clause it returns
result from one column instead of Scaler value.
Correlated SubQuery in SQL
Correlated subqueries are the one in which inner query or subquery
reference outer query. Outer query needs to be executed before inner query. One
of the most common example of correlated subquery is using keywords exits and
not exits. An important point to note is that correlated subqueries are slower
queries and one should avoid it as much as possible.
Example of Correlated Subquery in SQL
Here is an example of Correlated subquery “Return all markets
which has at least one stock listed on it.”
mysql> SELECT m.NAME FROM Market m WHERE m.RIC = (SELECT
s.LISTED_ON_EXCHANGE FROM Stock s WHERE s.LISTED_ON_EXCHANGE=m.RIC);
+-------------------------+
| NAME |
+-------------------------+
| Tokyo Stock Exchange |
| NASDAQ |
| New York Stock Exchange |
| Bombay Stock Exchange |
+-------------------------+
4 rows IN SET (0.00 sec)
Here inner query will execute for every Market as RIC will be
changed for every market.
Difference between Correlated and NonCorrelated
Subquery
Now we have seen correlated and noncorrelated subqueries and there
example its much easier to understand difference between correlated vs
noncorrelated queries. By the way this is also one of the popular sql interview
question and its good to know few differences:
1.In case of correlated subquery inner query depends on outer
query while in case of noncorrelated query inner query or subquery doesn't
depends on outer query and run by its own.
2.In case of correlated subquery, outer query executed before
inner query or subquery while in case of NonCorrelated subquery inner query
executes before outer query.
3.Correlated Sub-queries are slower than non correlated subquery
and should be avoided in favor of sql joins.
4.Common example of correlated subquery is using exits and not
exists keyword while non correlated query mostly use IN or NOT IN keywords.
SubQuery vs Join in SQL
Any information which you retrieve from database using subquery
can be retrieved by using different types os joins also. Since SQL is flexible
and it provides different way of doing same thing. Some people find SQL Joins
confusing and subquery specially noncorrelated more intuitive but in terms of
performance SQL Joins are more efficient than subqueries.
Important points about SubQuery in DBMS
1.Almost whatever you want to do with subquery can also be done
using join, it just matter of choice
subquery seems more intuitive to many user.
2.Subquery normally return an scaler value as result or result
from one column if used along with
IN Clause.
3.You can use subqueries in four places: subquery as a column in
select clause,
4.In case of correlated subquery outer query gets processed before
inner query.
Read more:
http://javarevisited.blogspot.com/2012/07/subquery-example-in-sql-correlated-vs.html#ixzz2qHuuglhU
What is CTE (Common Table Expression)?
·
When a complex SQL
statement has number of joins then it can be made easier by using Common Table
Expression.
·
Consider the following
SQL statement.
SELECT * FROM (
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID) E
WHERE E.Gender = ‘Male’
ORDER BY T.EmpName
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID) E
WHERE E.Gender = ‘Male’
ORDER BY T.EmpName
The syntax of CTE is as follow
- The CTE Name (followed by WITH keyword)
- The Column List (Optional)
- The Query (Appears within parentheses after the AS keyword)
- The CTE Name (followed by WITH keyword)
- The Column List (Optional)
- The Query (Appears within parentheses after the AS keyword)
If we write the above messy query using CTE it would be like
With E(EmpName, Department, Gender)
AS
(
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID
)
SELECT * FROM E
WHERE E.Gender = ‘Male’
ORDER BY E.EmpName
With E(EmpName, Department, Gender)
AS
(
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department dept on emp.DeptID = dept.DeptID
)
SELECT * FROM E
WHERE E.Gender = ‘Male’
ORDER BY E.EmpName
This way the query can be made more readable and easy to
understand.
What is difference between clustered and non clustered index?
·
A table can have only
one Clustered Index at a time which is generally created on primary key and can
have more than one non clustered indexes (maximum up to 999)
·
The leaf level of
clustered index is actual data pages of the table. Whereas in case of
non-clustered index the leaf level is a pointer to the data.
·
Non-clustered index is
faster than clustered index because when we use DML statement on clustered
index, performance issues may occurred since it has to update the index every
time a DML statement is executed.
·
Syntax of creative
clustered / non clustered index is as follow
CREATE [CLUSTERED | NON CLUSTERED] INDEX index_name
ON <object> (column [ASC | DESC] [,…n])
CREATE [CLUSTERED | NON CLUSTERED] INDEX index_name
ON <object> (column [ASC | DESC] [,…n])
What is use of EXCEPT clause? How it differs from NOT IN clause.
-When we combine two queries using EXCEPT clause, it will returns
distinct rows from the first SELECT statement that are not returned by the
second one.
-EXCEPT clause works the same way as the UNION operator of SQL and MINUS clause in Oracle.
-The syntax of EXCEPT clause is as follow
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
-EXCEPT clause works the same way as the UNION operator of SQL and MINUS clause in Oracle.
-The syntax of EXCEPT clause is as follow
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
EXCEPT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
-The difference between EXCEPT and NOT IN clause is EXCEPT operator returns all distinct rows from the rows returned by first select statement which does not exist in the rows returned by second select statement. On the other hand “NOT IN” will return all rows from returned by first select statement which does not exist in the rows returned by second select statement.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
-The difference between EXCEPT and NOT IN clause is EXCEPT operator returns all distinct rows from the rows returned by first select statement which does not exist in the rows returned by second select statement. On the other hand “NOT IN” will return all rows from returned by first select statement which does not exist in the rows returned by second select statement.
What is difference between Index Seek vs. Index Scan?
·
Index Seek and Index
Scan are operation for query tuning in execution plans.
·
Table Scan scans every
record of the table. So the cost of proportional is the number of rows of that
table.
·
The Index Scan is
preferred only when the table is small.
·
Index Seek only touches
the rows which qualify and the pages that contain that qualifying rows, so the
cost of proportional is the number of qualifying rows and pages instead of the
number of rows in the table.
·
Index seek is preferred
for highly sensitive queries.
What is ROW_NUMBER function?
·
RANK is one of the
Ranking functions which are used to give rank to each row in the result set of
a SELECT statement.
·
For using this function
first specify the function name, followed by the empty parentheses.
·
Then specify the OVER
function. For this function, you have to pass an ORDER BY clause as an
argument. The clause specifies the column(s) that you are going to rank.
·
For Example
SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary
SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary
·
In the result you will
see that the highest salary got the first rand and the lowest salary got the
last rank. Here the rows with equal salaries will not get same ranks.
What is Trigger?
-In SQL the Trigger is the procedural code that executed when you
INSERT, DELETE or UPDATE data in the table.
-Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database.
-For example, to prevent the user to delete the any Employee from EmpDetails table, following trigger can be created.
create trigger del_emp
on EmpDetails
for delete
as
begin
rollback transaction
print "You cannot delete any Employee!"
end
-When someone will delete a row from the EmpDetails table, the del_emp trigger cancels the deletion, rolls back the transaction, and prints a message "You cannot delete any Employee!"
-Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database.
-For example, to prevent the user to delete the any Employee from EmpDetails table, following trigger can be created.
create trigger del_emp
on EmpDetails
for delete
as
begin
rollback transaction
print "You cannot delete any Employee!"
end
-When someone will delete a row from the EmpDetails table, the del_emp trigger cancels the deletion, rolls back the transaction, and prints a message "You cannot delete any Employee!"
What is Scheduled job and how to create it?
-If we want to execute any procedural code automatically on specific
time either once or repeatedly then we can create a Scheduled job for that
code.
-Following are the steps to create a Scheduled Job.
1. Connect to your database of SQL server in SQL Server Management Studio.
2. On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New.
3. A New Job window will appear. Give a related name for the job.
4. Click next on the "Steps" in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call.
5. Click on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also.
-Using scheduled job you can also create alert and notifications.
-Following are the steps to create a Scheduled Job.
1. Connect to your database of SQL server in SQL Server Management Studio.
2. On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New.
3. A New Job window will appear. Give a related name for the job.
4. Click next on the "Steps" in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call.
5. Click on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also.
-Using scheduled job you can also create alert and notifications.
What is OPENXML in SQL Server?
-OPENXML provides an easy way to use an XML document as a
data-source for your procedures.
-OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows.
-By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database.
-Example:
DECLARE @count int
DECLARE @xml varchar(5000)
SET @xml ='<Employees>
<Employee id="1">
<Name>Arpit</Name>
<Employee >1234</ Employee >
</Employee >
<Employee id="2">
<Name>Rahul</Name>
<PhoneNo>2211</PhoneNo>
</Employee >
</Employees>'
EXEC sp_xml_preparedocument @count OUTPUT, @xml
SELECT *
FROM OPENXML (@count, Employees/Employee')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
It will give following result.
1 Arpit 1234
2 Rahul 2211
-OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows.
-By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database.
-Example:
DECLARE @count int
DECLARE @xml varchar(5000)
SET @xml ='<Employees>
<Employee id="1">
<Name>Arpit</Name>
<Employee >1234</ Employee >
</Employee >
<Employee id="2">
<Name>Rahul</Name>
<PhoneNo>2211</PhoneNo>
</Employee >
</Employees>'
EXEC sp_xml_preparedocument @count OUTPUT, @xml
SELECT *
FROM OPENXML (@count, Employees/Employee')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
It will give following result.
1 Arpit 1234
2 Rahul 2211
What are Sparse columns?
·
Sparse column is a type
of column which is used to optimize storage for null values.
·
When a column there is
big number of null then by defining that column as spars column we can save a
large amount of disk space.
·
The drawback of sparse
column is that it requires more space for the non null values. When we define a
column as sparse it requires additional 4 Byte for not null values.
·
For example, a DATETIME
column in a non-sparse column required 8 bytes of storage whereas if we define
that column as a sparse column then it will require 12 bytes.
·
It is not possible to
set ROWGUIDCOL and IDENTITY properties in sparse column.
What is RANK function?
-RANK is one of the Ranking functions which are used to give rank
to each row in the result set of a SELECT statement.
-For using this function first specify the function name, followed
by the empty parentheses.
-Then specify the OVER function. For this function, you have to
pass an ORDER BY clause as an argument. The clause specifies the column(s) that
you are going to rank.
For Example
SELECT RANK() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary
SELECT RANK() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary
-In the result you will see that the highest salary got the first
rand and the lowest salary got the last rank.
Here the rows with equal salaries will get same ranks.
-Remember that the rank depends on the row's position in the result set, not on the sequential number of the row.
Here the rows with equal salaries will get same ranks.
-Remember that the rank depends on the row's position in the result set, not on the sequential number of the row.
What are cursors and when they are useful?
-When we execute any SQL operations, SQL Server opens a work area
in memory which is called Cursor.
-When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used.
-There are two of cursors
-When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used.
-There are two of cursors
1. Implicate Cursor
SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors.
2. Explicit Cursor
When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.
They are managed by OPEN, FETCH and CLOSE.
SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors.
2. Explicit Cursor
When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.
They are managed by OPEN, FETCH and CLOSE.
%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in
both types of cursors.
What is log shipping?
-SQL has introduced Log Shipping feature to synchronize the
Distributed Database Server. Synchronize the database by copying Transaction
logs, Backing up, Restoring data. SQL Server Job Agents is used to make these
processes automatic.
-In the case of failure the Log Shipping will not transfer the server. That means it will not redirect your application to other server. This has to be done manually.
-Log shipping synchronizes the database only. The main functions of Log Shipping are as follows:
1. To Back up the transaction log of the primary database
2. To Copy the transaction log backup to every secondary server
3. To Restore the transaction log backup on the secondary database
-In the case of failure the Log Shipping will not transfer the server. That means it will not redirect your application to other server. This has to be done manually.
-Log shipping synchronizes the database only. The main functions of Log Shipping are as follows:
1. To Back up the transaction log of the primary database
2. To Copy the transaction log backup to every secondary server
3. To Restore the transaction log backup on the secondary database
What is SQL Profiler?
-SQL Server provides a graphical tool which helps system
administrators to monitor T-SQL statements of Database Engine.
-SQL Profiler can capture and store data related to every event to
a file or a table.
-SQL Server Profiler can be used
1. To create a trace
2. To store the trace results in a table.
3. To watch the trace results when the trace runs
4. To replay the trace results
5. To start, stop, pause, and modify the trace results
2. To store the trace results in a table.
3. To watch the trace results when the trace runs
4. To replay the trace results
5. To start, stop, pause, and modify the trace results
What is Similarity and Difference between Truncate and Delete in
SQL?
Similarity
-These both command will only delete data of the specified table,
they cannot remove the whole table data structure.
Difference
-TRUNCATE is a DDL (data definition language) command whereas
DELETE is a DML (data manipulation language) command.
-We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.
-TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.
-We can use any condition in WHERE clause using DELETE but you can't do it with TRUNCATE.
-If table is referenced by any foreign key constraints then TRUNCATE will not work.
-We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.
-TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.
-We can use any condition in WHERE clause using DELETE but you can't do it with TRUNCATE.
-If table is referenced by any foreign key constraints then TRUNCATE will not work.
Difference between TRUNCATE and DELETE commands
Submitted by Dipal havsar
(not verified) on Tue, 2006-09-19 07:39.
1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the
Rollback Tablespace first.then delete operation get performed.Thatswhy when you
type ROLLBACK after deleting a table ,you can get back the data(The system get
it for you from the Rollback Tablespace).All this process take time.But when
you type TRUNCATE,it removes data directly without copying it into the Rollback
Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back
the data.
3>You cann't rollback in TRUNCATE but in DELETE you can
rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML
commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in
DELETE you can write conditions using WHERE clause
What is Normalization of database? What are its benefits?
-Normalization is set of rules that are to be applied while
designing the database tables which are to be connected with each other by
relationships. This set of rules is called Normalization.
-Benefits of normalizing the database are
1. No need to restructure existing tables for new data.
2. Reducing repetitive entries.
3. Reducing required storage space
4. Increased speed and flexibility of queries.
1. No need to restructure existing tables for new data.
2. Reducing repetitive entries.
3. Reducing required storage space
4. Increased speed and flexibility of queries.
What is Fill factor?
·
The 'fill factor' option
indicate how full SQL Server will create each index page.
·
When the index page
doesn’t have free space for inserting a new row, SQL Server will create new
index page and transfer some rows from the previous index page to the new index
page. This process is called page split.
·
If we want to reduce the
number of page splits then we can use Fill factor option. Using Fill factor SQL
will reserve some space on each index page.
·
The fill factor is a
value from 1 through 100 that indicates the percentage of the index page to be
left empty. The default value for fill factor is 0.
·
If the table contains the
data which is not changed frequently then we can set the fill factor option to
100. When the table's data is modified frequently, we can set the fill factor
option to 80% or as we want.
What are different types of replication in SQL Server?
There are three types of replication in SQL SERVER
1. Snapshot Replication.
·
In Snapshot Replication
snapshot of one database is transferred to another database.
·
In this replication data
can be refreshed periodically and all data will be copied to another database every
time the table is refreshed.
2. Transactional Replication
·
In transactional
replication data will be same as in snapshot replication, but later only the
transactions are synchronized instead of replicating the whole database.
·
We can specify the
refresh of database either continuously or on periodic basis.
3. Merge Replication
·
Merge replication
replicate data from multiple sources into a single central database.
·
The initial load will be
same as in snapshot replication but later it allows change of data both on
subscriber and publisher, later when they come on-line it detects and combines
them and updates accordingly.
What is REPLACE and STUFF function in SQL Server?
STUFF: This function is used to replace the part of string with
some another string.
Syntax:
STUFF (String1, Position, Length, String2)
String1 - String to be overwritten
Position - Starting Position for overwriting
Length - Length of replacement string
String2- String to overwrite
String1 - String to be overwritten
Position - Starting Position for overwriting
Length - Length of replacement string
String2- String to overwrite
Example:
SELECT STUFF(‘Arpit',2,2,'mi')
Output: Amit
SELECT STUFF(‘Arpit',2,2,'mi')
Output: Amit
REPLACE: This function is used to replace all the occurrences of
particular string by another string.
Syntax:
REPLACE(String1, String2, String3)
REPLACE(String1, String2, String3)
Example:
SELECT REPLACE(‘Arpit Jain’,’i’,’m’)
Output: Arpmt Jamn
SELECT REPLACE(‘Arpit Jain’,’i’,’m’)
Output: Arpmt Jamn
If you want to replace the first occurrence of “I”, Replace
wouldn't work, because it always replaces ALL occurrences of the string.
Give an example to search for a string in all stored procedure
in SQL Server.
-Suppose we have an EmpDetails table in our database and there are
certain numbers of stored procedures in database. We want to know in which
stored procedure(s) table EmpDetails is used.
-We can use following query
-We can use following query
SELECT
sys.objects.name, sys.objects.type, sys.objects.type_desc,
sys.objects.schema_id, sys.syscomments.text
FROM sys.objects
INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id
where sys.syscomments.text like '%EmpDetails%'
And type ='P'
sys.objects.name, sys.objects.type, sys.objects.type_desc,
sys.objects.schema_id, sys.syscomments.text
FROM sys.objects
INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id
where sys.syscomments.text like '%EmpDetails%'
And type ='P'
What are Magic tables in SQL Server?
-In SQL Server there are two system tables “Inserted” and
“Deleted” called Magic tables.
-These are not the physical tables but the virtual tables
generally used with the triggers to retrieve the inserted, deleted or updated
rows.
-When a record is inserted in the table that record will be there
on INSERTED Magic table.
-When a record is updated in the table that existing record will
be there on DELETED Magic table and modified data will be there in INSERTED
Magic table.
-When a record is deleted from that table that record will be
there on DELETED Magic table.
What is difference between stored procedure and user defined
function?
·
It is not possible to
change the table data with the use of User defined functions but you can do it
by using stored procedure.
·
The execution of User
defined function will be stopped if any error occurred in it. But in the case
of Stored procedure when an error occurs the execution will ignore the error
and jumps to the next statement.
·
We can use User defined
function in XML FOR clause but we can use stored procedure in XML FOR clause.
·
It is not possible to
make permanent changes to server environment whereas stored procedure can
change some of the server environment.
·
User defined functions
do not return output parameters while stored procedure can return output
parameters.
What are ACID properties of Transaction?
Following are the ACID properties for Database.
Atomicity – Transactions may be set of SQL statements. If any of
statement fails then the entire transaction fails. The transaction follows all
or nothing rule.
Consistency – This property says that the transaction should be
always in consistent state. If any transaction is going to effect the
database’s consistent state then the transaction could be rolled back.
Isolation – This property says that one transaction cannot
retrieve the data that has been modified by any other transaction until it’s
completed.
Durability – When any transaction is committed then it must be
persisted. In the case of failure only committed transaction will be recovered
and uncommitted transaction will be rolled back.
What are COMMIT and ROLLBACK in SQL?
COMMIT statement is used to end the current transaction and once
the COMMIT statement is executed the transaction will be permanent and undone.
Syntax: COMMIT;
Example:
BEGIN
UPDATE EmpDetails SET EmpName = ‘Arpit’ where Dept = ‘Developer’
COMMIT;
END;
ROLLBACK statement is used to end the current transaction and undone the changes which was made by that transaction.
BEGIN
UPDATE EmpDetails SET EmpName = ‘Arpit’ where Dept = ‘Developer’
COMMIT;
END;
ROLLBACK statement is used to end the current transaction and undone the changes which was made by that transaction.
Syntax: ROLLBACK [TO] Savepoint_name;
Example
BEGIN
Statement1;
SAVEPOINT mysavepoint;
BEGIN
Statement2;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO mysavepoint;
Statement5;
END;
END;
BEGIN
Statement1;
SAVEPOINT mysavepoint;
BEGIN
Statement2;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO mysavepoint;
Statement5;
END;
END;
What is a Linked Server?
·
When we want to query on
remote database server along with the local database server then we can add the
remote SQL server to local SQL server in a same group using the concept called
Linked Server.
·
We can query on both
servers using T-SQL.
·
We can use stored
Procedure sp_addlinkedserver, sp_addlinkedsrvlogin to add new Linked Server.
·
By using Linked Server
we can SQL statement in clean and easy way to retrieve, join and combine remote
data with local data.
What is a WITH(NOLOCK)?
·
WITH(NOLOCK) is used to
unlock the data which is locked by the transaction that is not yet committed.
This command is used before SELECT statement.
·
When the transaction is
committed or rolled back then there is no need to use NOLOCK function because
the data is already released by the committed transaction.
·
Syntax: WITH(NOLOCK)
·
Example:
SELECT * FROM EmpDetails WITH(NOLOCK)
SELECT * FROM EmpDetails WITH(NOLOCK)
·
WITH(NOLCOK) is similar
as READ UNCOMMITTED
What are the basic functions for master, msdb, model, tempdb
databases?
·
The Master database
contains catalog and data for all databases of the SQL Server instance and it
holds the engine together. Because SQL Server cannot start if the master
database is not working.
·
The msdb database contains
data of database backups, SQL Agent, DTS packages, SQL Server jobs, and log
shipping.
·
The tempdb contains
temporary objects like global and local temporary tables and stored procedures.
·
The model is a template
database which is used for creating a new user database.
·
SQL Server includes
the following system databases.
System
database
|
Description
|
Records all
the system-level information for an instance of SQL Server.
|
|
Is used by
SQL Server Agent for scheduling alerts and jobs.
|
|
Is used as
the template for all databases created on the instance of SQL Server.
Modifications made to the model database, such as database
size, collation, recovery model, and other database options, are applied to
any databases created afterward.
|
|
Is a read-only database that contains system objects that are included
with SQL Server. System objects are physically persisted in the Resource database,
but they logically appear in the sys schema of every
database.
|
|
Is a
workspace for holding temporary objects or intermediate result sets?
|
List few advantages of Stored procedure.
·
By using stored
procedures we can reuse the code.
·
Stored procedure helps
in reducing network traffic and latency.
·
Stored procedures
provide better security to your data.
·
Stored procedure is cached
in SQL Server’s memory. So it helps to reduce the server overhead. It also
enhances application performance.
·
Stored procedures help
us in the encapsulation of the code. The code of the stored procedure can be
changed without affecting application.
Sql server interview questions and answers
|
|||||||||
|
|||||||||
Can we call SP inside a query?
|
||
No, we
can't call sp in query.
Eg:this query not working properly select empno,empname,exec spname from tablename. but function we can call with in a query. Eg: select empno,empname,upper(empaddress) from emptable |
||
difference
between a Composite Index and a Covering Index
Composite index: An index that contains more than one column. In both SQL
Server 2005 or higher, you can include up to 16 columns in an index, as long as
the index doesn’t exceed the 900 byte limit. Both clustered and nonclustered indexes
can be composite indexes.
Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.
Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.
A covered query is a query where all the columns in
the query's result set are pulled from non-clustered indexes.
A query is made into a
covered query by the judicious arrangement of indexes.
A covered query is often
more performance than a non-covered query in part because non-clustered
indexes have more rows per page than clustered indexes or heap indexes, so
fewer pages need to be brought into memory in order to satisfy the query.
They have more rows per page because only part of the table row is part of
the index row.
A covering index is an index which is used in a
covered query. There is no such thing as an index which, in and of itself, is
a covering index. An index may be a covering index with respect to query A,
while at the same time not being a covering index with respect to query B.
|
Composite
Index
An index that consists of more than one columns is referred as composite index. Both clustered and non-clustered indexes can be composite index. SQL 2005 and 2008 supports up to 16 columns in a
composite index.
One needs to be careful while defining
a composite index. The order of the columns are very important here. Lets see
an example.
IF EXISTS (SELECT name
FROM sys.indexes
WHERE name = N'IX_Employee_Composite') DROP INDEX IX_Employee_Composite ON [Employee]; GO CREATE INDEX IX_Employee_Composite ON [Employee] (Emp_Code,Name); GO
In the above example we have defined a
composite index 'IX_Employee_Composite' on Employee table. The columns that
have been used are Emp_code,Name. Here the Emp_Code field is the leftmost in
the index. The composite index plays the part if the where clause of the
query matches the column(s) that are leftmost in the index.
So in the following queries, the
composite index created above will be used.
SELECT * FROM EMPLOYEE
WHERE Emp_Code = "A001"
SELECT * FROM EMPLOYEE
WHERE Emp_Code = "A001" and Name = "ABC" and City =
"DEL"
But the index will fail in the
following queries
SELECT * FROM EMPLOYEE
WHERE Name = "ABC" and EMP_Code = "A001" and City =
"DEL"
SELECT * FROM EMPLOYEE
WHERE Name = "ABC"
So the thumb rule is to order the
columns in the key to enhance selectivity, with the most selective columns to
the leftmost of the key otherwise the query optimizer will ignore the index
at all.
Another approach is to split the
composite index into multiple single column index. SQL Server has the ability
to join two or more single indexes to make them use. However multiple single
indexes are not always better than single composite indexes.
|
Covering
Index
Covering index is also a type of composite index which covers a query. In other words, it includes all the columns that are needed to execute a query.
If you know a particular query is
being used often in the application, then we should define a covering index
on the table that will include all the columns referenced in the SELECT, JOIN
and WHERE clause of the query. As a result, the SQL Server doesn't have to
look up the actual data in the table, thus reducing the I/O and boosting
performance. One needs to be careful while defining a covering index, because
if it gets too big, then its overhad can outweigh its benefits.
In the composite index, the order of
the columns are very vital but incase of covering indexes, the order is not
important because even If the wHERE Clause in a query does not specify the
first column of an existing composite index, the query optimizer still use it
because it contains all of the columns referenced in the query.
If you want to create a covering
index, it is a good practise to modify the existing indexes that exists on
the table. For example if you want to define a covering index for columns Id
and Name and the index for column ID is already available then, instead of
creating a new covering index , modify the index on ID to be a composite
index on ID and Name.
|
Covered queries and covering indexes are different, yet
closely related. A query is covered if all the columns it uses come from one or
more indexes. These columns include the columns you want the query to return as
well as columns in any JOIN, WHERE, HAVING, and ORDER BY clause. A covered
query typically is considered advantageous because data access through indexes
can be more efficient. However, the high-speed access that this kind of query
facilitates can become costly when you update the table because you must
maintain the indexes.
A covering index—which is used in covered queries—can
provide some or all of the indexed columns that a covered query uses. If a
covering index is also a composite index (i.e., it indexes more than one column
in its base table or view), it might contain columns that aren't used in the
covered query but are used instead in other queries that have overlapping
columns.
Can we have primary key and clustered index on same table with
different field?
This is one of my favourite interview question. I thought I will
explain this in detail here.
Answer for the question ”Can
we have primary key and clustered index on same table with different field”
is YES we can create primary key on one field
and clustered index on another field.
The following summarizes the differences between COMPUTE and
GROUP BY:
·
GROUP BY produces a
single result set. There is one row for each group containing only the grouping
columns and aggregate functions that show the subaggregate for that group. The
select list can contain only the grouping columns and aggregate functions.
·
COMPUTE produces
multiple result sets. One kind of result set contains the detail rows for each
group containing the expressions from the select list. The other type of result
set contains the subaggregate for a group, or the total aggregate for the
SELECT statement. The select list can contain expressions other than the
grouping columns or aggregate functions. The aggregate functions are specified
in the COMPUTE clause, not in the select list.
The following query uses GROUP BY and aggregate functions. The query
returns one result set that has one row per group containing the aggregate
subtotals for that group:
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, SUM(UnitPrice), SUM(UnitPriceDiscount)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID;
 Note |
You cannot include ntext, text,
or image data types in a COMPUTE or COMPUTE BY clause.
|
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, UnitPrice, UnitPriceDiscount
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID
COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount) BY SalesOrderID;
Thank you, In this post you have covered 90% of topics. I found some more SQL Server Technical Interview Questions .
ReplyDeleteI Really appreciate your work