Answer Posted / rakesh kumar jaiswal

CREATE TABLE Names
(
NameID INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED,
FName VARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT Names(FName) VALUES('Aaron')
INSERT Names(FName) VALUES('Greg')
INSERT Names(FName) VALUES('Alex')
INSERT Names(FName) VALUES('Luan')
INSERT Names(FName) VALUES('John')
INSERT Names(FName) VALUES('Todd')
INSERT Names(FName) VALUES('Scott')
INSERT Names(FName) VALUES('Jess')
INSERT Names(FName) VALUES('Drew')
INSERT Names(FName) VALUES('Katherine')
INSERT Names(FName) VALUES('Paul')
GO

-- solution #1: nested top

SELECT TOP 1 FName
FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub
ORDER BY FName DESC

-- solution #2: NOT IN

SELECT TOP 1 FName
FROM Names WHERE FName NOT IN
(
SELECT TOP 9 FName
FROM Names
ORDER BY FName
)
ORDER BY FName

-- solution #3: derived count
-- this assumes FName is unique

SELECT FName
FROM Names
WHERE
(
SELECT COUNT(*)
FROM Names n2
WHERE n2.FName <= Names.FName
) = 10

-- solution #4: MAX

SELECT FName = MAX(FName) FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub

-- solution #5: relative fetch from cursor

-- yes, cursors are generally evil, but
-- sometimes you might be surprised

DECLARE FNames CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT FName
FROM Names
ORDER BY FName

DECLARE @FName VARCHAR(32)

OPEN FNames

FETCH RELATIVE 10 FROM FNames INTO @FName

CLOSE FNames
DEALLOCATE FNames

SELECT FName = @FName

DROP TABLE Names
GO

What part does database design plays a role in performance of an sql server-based application?

976

---

Explain trigger classes i.e. Instead of and after trigger?

875

---

What is co-related sub query?

1018

---

What stored procedure can you use to display the current processes?

884

---

What are the new features introduced in SQL Server 2000? What changed between the previous version of SQL Server and the current version?

2360

---

What are various aggregate functions that are available?

976

---

What is your recommendation for a query running very slow? : sql server database administration

1039

---

What is isnull() operator?

1032

---

What are approximate numeric data types in ms sql server?

1052

---

Differentiate between a having clause and a where clause.

962

---

Mention the differences between sql server and mysql.

1010

---

What is a full text index?

916

---

What is hierarchy, what are its types and difference between them? : sql server analysis services, ssas

1258

---

Which are ddl commands?

1038

---

How to start sql server browser service?

1128

---