SQL Table size

SQL Table size

• vivi_0606

  in SQL Server 2005 you can use CTE and ROW_NUMBER

  then the query would be this

  with ReturnFifthRow as(

  select row_number() over(order by code) as RowNumber,* from table1)

  select * from ReturnFifthRow

  where RowNumber =5

  Run the complete script to see how this works

  create table table1 (code int,description varchar(10))

  insert into table1 values(1,'abc1')

  insert into table1 values(2,'abc2')

  insert into table1 values(3,'abc3')

  insert into table1 values(4,'abc4')

  insert into table1 values(5,'abc5')

  insert into table1 values(6,'abc6')

  insert into table1 values(7,'abc7')

  insert into table1 values(8,'abc8')

  insert into table1 values(9,'abc9')

  insert into table1 values(10,'abc10')

  insert into table1 values(11,'abc11')

  insert into table1 values(12,'abc12')

  insert into table1 values(13,'abc13')

  insert into table1 values(14,'abc14')

  insert into table1 values(15,'abc15')

  insert into table1 values(16,'abc16')

  insert into table1 values(17,'abc17')

  insert into table1 values(18,'abc18')

  insert into table1 values(19,'abc19')

  insert into table1 values(20,'abc20')

  GO

  with ReturnFifthRow as(

  select row_number() over(order by code) as RowNumber,* from table1)

  select * from ReturnFifthRow

  where RowNumber =5

  Denis the SQL Menace

  http://sqlservercode.blogspot.com/

  
  

• NewDBA

  Hello Denis You Answer is very good and Thank You Very Much for this.

  I have one more problem ,

  Suppose If i have a table named table1 and have 20 records in that table with two column Code and Description,

  There is in this table no column have uniquness in data. No primary key's No unique constraints not any other thing.

  I want to select 5th row from the table .

  wht's the querry for this

  
  
  

• raindrops123

  Hello Denis

  The data u have inserted in the table is

  insert into table1 values(1,'abc1')

  insert into table1 values(2,'abc2')

  insert into table1 values(3,'abc3')

  insert into table1 values(4,'abc4')

  insert into table1 values(5,'abc5')

  insert into table1 values(6,'abc6')

  insert into table1 values(7,'abc7')

  insert into table1 values(8,'abc8')

  insert into table1 values(9,'abc9')

  insert into table1 values(10,'abc10')

  insert into table1 values(11,'abc11')

  insert into table1 values(12,'abc12')

  insert into table1 values(13,'abc13')

  insert into table1 values(14,'abc14')

  insert into table1 values(15,'abc15')

  insert into table1 values(16,'abc16')

  insert into table1 values(17,'abc17')

  insert into table1 values(18,'abc18')

  insert into table1 values(19,'abc19')

  insert into table1 values(20,'abc20')

  But suppose if the data like this

  insert into table1 values(1,'abc1')

  insert into table1 values(2,'abc2')

  insert into table1 values(1,'abc3')

  insert into table1 values(4,'abc4')

  insert into table1 values(5,'abc5')

  insert into table1 values(5,'abc6')

  insert into table1 values(7,'abc9')

  insert into table1 values(5,'abc8')

  insert into table1 values(9,'abc9')

  insert into table1 values(15,'abc15')

  insert into table1 values(11,'abc11')

  insert into table1 values(12,'abc12')

  insert into table1 values(20,'abc1')

  insert into table1 values(14,'abc14')

  insert into table1 values(15,'abc5')

  insert into table1 values(16,'abc16')

  insert into table1 values(17,'abc17')

  insert into table1 values(19,'abc19')

  insert into table1 values(19,'abc19')

  insert into table1 values(20,'abc20')

  Then in this case the code that u have given will work with sql 2005 or not.

  and one more thing can't we do the same in SQL 200.

  Byeeeeeee and Take care

  
  
  

• Kerry_Dixon

  To get the size of the authors table in the pubs database use this

  USE pubs
  EXEC sp_spaceused 'authors'

  >>-- how much amount of data can a table store in sql.

  Unlimited AFAIK you can store Billions of rows (you will probably never reach this, performance comes into play in this case)

  -- On which thing the size of table depends.

  Mostly disksize also a row in a SQl server table can not exceed 8060 bytes (this does not include image,text and other BLOB's)

  here are the Maximum Capacity Specifications for SQL Server 2005

  http://msdn2.microsoft.com/en-us/library/ms143432.aspx

  Denis the SQL Menace

  http://sqlservercode.blogspot.com/

  
  
• SQL Table size