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 is the New in SQL server 2008?

982

---

Can we run Reporting Services with SQL Server express edition, which is a free version of SQL Server?

121

---

Explain primary key and foreign key constraints?

1040

---

What is difference between cte and view?

924

---

Is the order of columns in the set clause important in ms sql server?

975

---

what kind of lan types do you know? : Sql server database administration

988

---

What does truncate do?

946

---

Why is there a performance difference between two similar queries where one uses union and the other uses union all?

1041

---

What is openxml in sql server?

1183

---

Explain magic tables in sql server?

1065

---

how to create a scrollable cursor with the scroll option? : Sql server database administration

1100

---

What is merge statement?

1086

---

Where is localdb stored?

951

---

Explain what are the restrictions while creating batches in sql server?

1082

---

What is the difference between stored procedure and functions?

1142

---