Skip to main content

SET ROWCOUNT

Stop the processing after the number of rows has been affected.

Example:

Step-1: Create the Table and insert some sample data.

CREATE TABLE STUDENT
(
ID INT IDENTITY(1,1) PRIMARY KEY,
STUDENTNAME VARCHAR(100) NOT NULL ,
LIVINGLOCATION VARCHAR(100) NULL
)

INSERT INTO STUDENT(STUDENTNAME,LIVINGLOCATION)
SELECT 'Rakesh','Hyderabad'
UNION ALL
SELECT 'Raju','Delhi'
UNION ALL
SELECT 'Madhu','Hyderabad'
UNION ALL
SELECT 'Naresh',NULL
UNION ALL
SELECT 'Venaktesh','Chennai'

In above Insert Statement we inserted 5 sample records.

Step-2: Using SET ROWCOUT

SET ROWCOUNT 3;
SELECT * FROM STUDENT
ORDER BY ID DESC







It will affected 3 rows after that processing has been stopped.

Step-2: Using SET ROWCOUT 0

SET ROWCOUNT 0;
SELECT * FROM STUDENT
ORDER BY ID DESC









It will return all rows if ROWCOUNT IS 0.

Labels:

Comments

  1. UnknownFebruary 3, 2015 at 10:55 PM

    good

    ReplyDelete
  2. UnknownFebruary 4, 2015 at 6:05 AM

    hi sir, i have one doubt, how to retrive the particular second row using top clause method???

    ReplyDelete
    Replies
    1. kallurirakeshFebruary 4, 2015 at 6:39 AM

      This comment has been removed by the author.

      Delete
    2. kallurirakeshFebruary 4, 2015 at 6:48 AM

      This Query may helpfull

      CREATE TABLE EMPLOYEE
      (
      EMPID INT IDENTITY(1,1) PRIMARY KEY,
      EMPNAME VARCHAR(100),
      SALARY INT NOT NULL
      )

      INSERT INTO EMPLOYEE
      SELECT 'RAKESH',8000
      UNION ALL
      SELECT 'RAJU',1000
      UNION ALL
      SELECT 'NARESH',5000
      UNION ALL
      SELECT 'VENKATESH',5800

      SELECT * FROM (select *, ROW_NUMBER() OVER(ORDER BY Salary DESC) ROWNUM FROM Employee) a WHERE a.ROWNUM=2

      Delete
  3. kallurirakeshFebruary 4, 2015 at 6:47 AM

    CREATE TABLE EMPLOYEE
    (
    EMPID INT IDENTITY(1,1) PRIMARY KEY,
    EMPNAME VARCHAR(100),
    SALARY INT NOT NULL
    )

    INSERT INTO EMPLOYEE
    SELECT 'RAKESH',8000
    UNION ALL
    SELECT 'RAJU',1000
    UNION ALL
    SELECT 'NARESH',5000
    UNION ALL
    SELECT 'VENKATESH',5800


    SELECT TOP 1 * FROM (SELECT TOP 2 * FROM EMPLOYEE ORDER BY SALARY DESC) A ORDER BY SALARY

    ReplyDelete
  4. UnknownFebruary 4, 2015 at 11:56 PM

    Splendid !! I have learnt new concept

    ReplyDelete

Post a Comment

Popular posts from this blog

Rank Functions in SQL SERVER

1 . ROW_NUMBER () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition. 2. RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set. 3. DENSE_RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking. 4. NTILE ( INTEGER_EXPRESSION ) OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups. Examples: --create Employee table create table Employee (                 EmpId int identity ( 1 , 1 ) primary key ,              ...
1 comment
Read more

Difference between LEN and DATALENGTH

LEN: LEN function returns the number of characters in a variable .it also removes the trailing spaces and then then return the length. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT LEN ( @Name ) as [len] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= 'rakesh ' SELECT LEN ( @Name ) as [len] Output: When we observe above variable assigned 'rakesh ' string after that added 3 spaces . Len function removes trailing spaces not leading spaces. DATALENGTH : DATALENG function returns the number of bytes occupy in a variable .it also considered the spaces also. Example-1: DECLARE @Name VARCHAR ( 20 )= 'rakesh' SELECT DATALENGTH ( @Name ) as [DataLength] Output: Example-2: DECLARE @Name VARCHAR ( 20 )= ' rakesh ' SELECT DATALENGTH ( @Name ) as [DataLength] Output: In above example before ' r ' and after ...
Post a Comment
Read more