Interview Questions → SQL SERVER → SQL Server Questionnaire II

SQL Server Questionnaire II

What are derived tables?

A derived table is a virtual table that's calculated on the fly from a select statement

Let us consider an example

I have made a table tbl_Test

Create table tbl_Test 
(
    PK_ID int identity(1,1),
    sName Varchar(200),
    sAddress Varchar(200)
)

I am inserting 4 values in the table

insert into tbl_Test values('Name1','Gurgaon')
insert into tbl_Test values('Name2','Gurgaon')
insert into tbl_Test values('Name3','Delhi')
insert into tbl_Test values('Name4','Delhi')

If I want all the names having address Gurgaon

then I can write it as

select * from tbl_Test where sAddress like 'Gurgaon'

or I can also use derived table

select * from 
--    derived table
(select * from tbl_Test where sAddress like 'Gurgaon') as DerTable

What are the maximum numbers of non clustered index on a table?

For SQL Server 2000 and 2005 -> 249 and for SQL Server 2008 -> 999

I have 2 tables

State

StateID	StateName
1	Arunachal Pradesh
2	Haryana
3	Punjab
4	Maharashtra

City

CityID	CityName	StateID
1	Itanagar	1
2	Gurgaon	2
3	Faridabad	2
4	Patiala	3
5	Ludhiana	3
6	Pune	4
7	Nasik	4

The required output should be

StateName	CityName
Arunachal Pradesh	Itanagar
Arunachal Pradesh	Other
Haryana	Faridabad
Haryana	Gurgaon
Haryana	Other
Punjab	Ludhiana
Punjab	Patiala
Punjab	Other
Maharashtra	Nasik
Maharashtra	Pune
Maharashtra	Other

What will be the query?

select statename,CityName from
(
Select a.stateid,statename,cityname,1 CityOrder
From state a inner join city b
On a.stateid=b.stateid
Union
Select stateid,statename,'Other',2 City
From state
) D
Order by stateid,CityOrder,cityName

What is the difference between function and stored procedure

      1. A stored procedure can return more than one values whereas a function will always return a single value.

      2. A stored procedure can have input as well as output parameters where function can only have input parameters.

      3. Procedure allows select statements as well as DML statements inside it whereas function only allows select statement within it.

      4.Functions can be called within the stored procedure but stored procedure can not be called from within the function.

      5. Function can be utilized in the select statement where as stored procedure can not be utilized within the select statement.

      6. You cannot use try-catch inside the function but you can use it in stored procedure.

      7. You cannot use transaction statements inside the function but you can use it in stored procedure.

What is the difference between left join and left outer join?

There is no difference between left join and left outer join, the keyword outer is optional.

Consider 2 tables person and department

Person

PersonID	PersonName	DepartmentID
1	Karan	1
2	Gaurav	1

Department

DepartmentID	DepartmentName
1	.Net
2	Testing

Now let us query using Left Join

select * from Person P Left Join Department D
On P.DepartmentID = D.DepartmentID

Output

PersonID	PersonName	DepartmentID	DepartmentID	DepartmentName
1	Karan	1	1	.Net
2	Gaurav	1	1	.Net

Now query the table using Outer

select * from Person P Left Outer Join Department D
On P.DepartmentID = D.DepartmentID

PersonID	PersonName	DepartmentID	DepartmentID	DepartmentName
1	Karan	1	1	.Net
2	Gaurav	1	1	.Net

What is the difference between nolock and readpast?

Nolock

Nolock also returns uncommitted data.

Consider a table contains 10 records

Now insert the record inside the transaction

      BEGIN TRANSACTION
      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)          
      VALUES            
      ('PoolTable', GETDATE(), 500)

Select query with nolock i.e.

      Select * from SalesHistory With(NoLock)

returns 11 records i.e. an uncommitted record also.

Repast

ReadPast ignores uncommitted records.

      BEGIN TRANSACTION
      UPDATE TOP(1) SalesHistory
      SET SalePrice = SalePrice + 1

With Select query with readpast only returns 9 records because 10th record is not committed.

If there are 5 columns in table A and 7 columns in table B how many columns will be there if we cross join table A and table B.?

Let us consider a table Employee

EmployeeID	EmployeeName	Salary	Address	State
1	Jason	20000	House no 11	State1
2	Robert	40000	House no 33	State2

And table Job

JobID	Title	AverageSalary	ProjectManager	Department	Address	State
1	Developer	10000	Smith	.Net	ABC Road	State4
2	Tester	12000	Smith	Testing	ABC Road	State4

Query For Cross Join

      SELECT *
      FROM Employee e CROSS JOIN job j
      ORDER BY j.JobID

So the output is

EmployeeID	EmployeeName	Salary	Address	State	JobID	Title	AverageSalary	ProjectManager	Department	Address	State
1	Jason	20000	House no 11	State1	1	Developer	10000	Smith	.Net	ABC Road	State4
2	Robert	40000	House no 33	State2	1	Developer	10000	Smith	.Net	ABC Road	State4
1	Jason	20000	House no 11	State1	2	Tester	12000	Smith	Testing	ABC Road	State4
2	Robert	40000	House no 33	State2	2	Tester	12000	Smith	Testing	ABC Road	State4

So there will be 12 columns in the resulting table

How do you delete duplicate records in a table

Consider a table

ID	Name	Age
2	Name2	34
1	Name1	34
1	Name1	34
2	Name2	34
3	Name3	34
3	Name3	34

To delete duplicate records

      WITH CTE (Id,Name,Age, DuplicateCount)
      AS
      (
        SELECT Id,Name,Age,
        ROW_NUMBER() OVER(PARTITION BY ID,Name,Age ORDER BY ID) AS DuplicateCount
        FROM #DuplicateRcordTable
      )
      delete from CTE where DuplicateCount >1

What is the difference between DateTime and DateTime2?

Datetime2 is the new datatype introduced in SQL Server 2008. It is considered as an extension of DateTime that has a larger date range, a larger default fractional precision.

DateTime Range - 1753-01-01 through 9999-12-31

DateTime2 Range - 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

What is the difference between null and blank?

Null means absence of value for a record and blank means value for the record with no data in it.

Posted By -

Karan Gupta

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**