SQL Server Interview Questions -- Part 8

TABLEA

ID
1
2
3
4
5

TABLEB

ID
1
3
5
7

In the above tables, what is the result of
71.
select * from tableA
union
select * from tableB
Ans:

1
2
3
4
5
7

72.
select * from tableA
union all
select * from tableB
Ans:

1
2
3
3
4
5
5
7

73.
Select * from tableA
intersect
select * from tableB
Ans:

1
3
5

74.
Select * from tableA
except
select * from tableB
Ans:

2
4

75. What is a Common Table Expression (CTE) ?
Ans:
Common Table Expression can be thought of as temporary result set. But, it has lot of powers including recursive usage.

Example:
WITH MYCTE (ID, NumberOfOrders)
AS
(
SELECT ID, COUNT(*)
FROM Order
GROUP BY ID
)
select * from MYCTE


76. What is row_number() in SQL Server ?
Ans: row_number () returns the sequential number of a row in a table for all the rows. It also has a partition clause that can return sequential number starting fresh with every partition.

77. What in newid() in SQL Server ?
Ans: NewID() creates a unique value. The type of value returned by newid is a uniqueidentifier.

78. What is a temporary table in SQL Server ?
Ans: Temporary tables as the name suggests is very similar to regular table but are temporary and are stored in tempdb database.

79. What are the two types of temporary tables in SQL Server ?
Ans: Local and Global.

80. What are the difference between local and global temporary tables ?
Ans: Local tables are represented as #temp and global ones are represented as ##temp. Local temporary tables can been seen/accessed by only the user who creates it and are deleted when the user disconnects from the current instance of SQL Server. Global temporary tables can be seen/accessed by any user and are deleted only when all users referencing the table disconnect from the instance of SQL Server.