Hi Harshana,
There is no function available with SQL Server similar to "LIMIT" but you can get the result set as you want.
Here is one the ways of limiting records with SQL Server 2000.
SELECT T.*
FROM
(
SELECT TOP 100 PERCENT (SELECT COUNT(*) FROM Person.Contact WHERE ContactID <= c.ContactID) AS RowNumbr, c.*
FROM Person.Contact c
ORDER BY c.ContactID
) T
WHERE T.RowNumbr BETWEEN 30 AND 60
You get the same easily with SQL Server 2005 by using a ranking function.
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ContactID) AS RowNumber, *
FROM Person.Contact) T
WHERE T.RowNumber BETWEEN 30 AND 60
Hope these solutions work for you. Let us know if you have more queries.