select range of data rows in MSSQL2005

We all know if we have to find range of data then we use sub queries but sql2005 provide row_number to find range of data

for ex.
if we have to select data from 21 row to 30 the n

SELECT * FROM
(
SELECT Row_Number() OVER (ORDER BY last_name, first_name) as rowid,
user_id,
last_name,
first_name
FROM user
) as a
where rowid >20 and rowid <31


same if i want 28 record the n

SELECT * FROM
(
SELECT Row_Number() OVER (ORDER BY last_name, first_name) as rowid,
user_id,
last_name,
first_name
FROM user
) as a
where rowid = 28

@@rowcount with MSSQL

@@ROWCOUNT returns the number of rows affected or read depending on the operation.

terms as follows

• @@ROWCOUNT returns the number of rows returned to the user for a SELECT.
• @@ROWCOUNT returns the number of rows affected after issuing a INSERT, DELETE, or UPDATE.

e.g



SELECT emp_firstname,emp_lastname from emp_details ;


out put is


emp_firstname emp_lastname

yogesh k

Jhon Bush


out put returns two rows

if we execute following after this the n

SELECT @@ROWCOUNT AS 'Rows previously affected';

then out put will be

Rows previously affected
------------------------
2

(1 row(s) affected

But if we execute query like


SELECT Count(*) as rowcount from emp_details
then

out put is

rowcount
-----------
2

(1 row(s) affected)

and we execute following after this
SELECT @@ROWCOUNT AS 'Rows previously affected';


then

out put is

Rows previously affected
------------------------
1

(1 row(s) affected)

• Notice it tells you that it sent back only 1 row to the user.because only one row is return to user
  

dealy in sql or wait in sql query

In many application or process we required dealy

there is dealy in sql server
call WAITFOR

select '1'
WAITFOR DELAY '00:00:30'

above query run and wait 30 sec for output

select '2'
WAITFOR Time '15:00'


above query run and wait for 3:00pm for output


WAITFOR gives out put after end of process if you want out put after each wait then use code as follows

select getdate()
WAITFOR DELAY '00:00:30'
Go
select getdate()
WAITFOR DELAY '00:00:30'
Go
select getdate()
Go

alternate logic for delay is while loop but its not accurate
it gives differnt interval on different configuration server.

text file as data source

In many application we need Text file as datasource or as table

many ways to do this one of that as follows

create one folder on c drive name textfiles
and execute following code


EXEC sp_addlinkedserver txtdatasrc, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', 'c:\textfiles', NULL, 'Text'

now your link server with name " txtdatasrc" is created

execute following code

EXEC sp_tables_ex txtdatasrc

see there is no record because folder does not contain any text file .

add new text file name table1 to folder

and again run command


EXEC sp_tables_ex txtdatasrc

see there is table name "table1#txt" in out put

now you can select rows from table1#txt by using select statment as follows


select * from txtsrv...table1#txt

this link server is very useful to access text as datasource

there are many othere methods and tricks in this stuff we will discuss soon...