Create Procedure [dbo].[MoreEfficient_Paging_Student]
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS
DECLARE @MaxRows int
SET @MaxRows = @PageSize * @PageNumber
DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM student)
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM (
SELECT Top (@PageSize) ID, Name, Marks
FROM (
SELECT TOP (@MaxRows) ID, Name, Marks
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
)
AS Foo
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
)
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END
Improvements:
(1) The last page in previous post was showing as much records as the page size. For example if there are 10 rows in table and page size is 3 and we are requesting 4rth page (pages in all these three posts are 1-based indexed), then its returning 8th, 9th and 10th row. It should be returning only the 10th row. This is corrected in this post by putting following lines:
DECLARE @Count int
SET @Count = (SELECT COUNT(*) FROM student)
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
In scenario above, @Count is 10, @MaxRows is 12, @PageSize is 3, @PageNumber is 4. The above lines do this:
@PageSize = 10 - ( (4-1) * 3)
@PageSize = 10 - (3 * 3)
@PageSize = 10 - 9
@PageSize = 1
Therefore only one row is shown in the 4rth page.
(2) Another problem in previous post was that if we input a page number greater than the maximum number of pages in the table, it still return the last few rows (as much rows as the page size). This get corrected automatically in above lines:
For page 5, (row numbers 13th, 14th, 15th), no rows exist (since table has 10 rows), so there must be an error. Using above code:
IF( (@MaxRows - @Count) > 0)
SET @PageSize = @Count - ( (@PageNumber - 1) * @PageSize )
IF( (15 - 10) > 0) --condition is true
@PageSize = 10 - ( (5 - 1) * 3)
@PageSize = 10 - (4 * 3)
@PageSize = -12
That is, a negative number. When this is used in "top" statement in sql, it result in error because a "top" variable can't be negative.
(3) Efficiency (both in terms of memory and speed). The previous post was using two temporary tables, first of them might be as large as the entire table if page number is the last page number, second table was a only as large as the page size so no issue there. This post don't use any temporary table, instead the entire query run as a whole (though its quiet complex and long).
03 September, 2010
Efficient Method of Getting Middle Rows From Sql Server 2008, 2005 OR Efficient Method of Paging With Sort In Sql Server 2008, 2005
Step 1: Create a table with 3 fields: ID int, Name varchar(50), Marks float.
Step 2: Put some rows in this table.
Step 3:
Alter Procedure Efficient_Paging_Student
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS
DECLARE @MaxRows INT
SET @MaxRows = @PageSize * @PageNumber
SET ROWCOUNT @MaxRows
SELECT ID, Name, Marks
INTO #Temp
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
SET ROWCOUNT @PageSize
SELECT ID, Name, Marks
INTO #Foo
FROM #Temp
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
SET ROWCOUNT 0
IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END
Step 4: Test
Efficient_Paging_Student 'ID', 'DESC', 4, 2
Efficient_Paging_Student 'Name', 'ASC', 4, 2
Explanation:
Using this example we can get any page (set of rows) having any number and any page size. For example if page number is 2 and page size is 4 then we get row numbers 5, 6, 7, 8. Note that row number here do not means id. Its not a fixed field. Its row number 5th, 6th, 7th and 8th AFTER sorting is done according to desired column name.
To make it efficient, we have to limit the rows returned in each query. Using "top" keyword don't work here because top keyword don't take a variable. However, ROWNUMBER can be set to any variable:
SET ROWNUMBER @VAR
This works.
The whole procedure is divided into 3 select statements, first of them returned a different number of rows than the last two. We can't combine the last two because of this error:
Msg 1033, Level 15, State 1, Procedure Efficient_Paging_Student, Line 41
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
It means that we can't apply "order by" clause to derived tables. Therefore we have to store that table value (coming from the second select statement) in a separate hash table called #foo. Its not an issue because the size of this table is same as size of page at front end (web page, windows form etc) which is usually a very small size (5 to 100 rows).
The actual load of this method is in the first select statement, its because here the number of rows selected is equal to the product of page size and page number. It means that if number of rows can be evenly divided by page size (for example 100 rows and 5 rows each table) then for the last page the query select the entire table. I don't know anyway to avoid this because the sorting requires us to go to that level, that is each row have to be accessed once no matter what (unless we are using views but thats a different story).
Once the first select statement is executed, the rest of the procedure runs in flash because the second and third select statements deal with only as much rows as the size of page. Since size of page is always very little (discussed above) therefore its not an issue at all.
At last we have to do the final sorting. Remember that "order by" clause sort by default in ascending order so I didn't wrote "asc" keyword anywhere in any query. To find out whether to use ascending order or descending order I had to use an if statement.
An important part here is selecting of column name. See:
(SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END)
Look that each case statement returns a column name but not in quotes !!!. It is 100% valid sql code for sql 2005 and above. Whatever column name is returns is fit in above query. Following two statements parse to same query if @SortColumn is equal to 'Name':
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY Name DESC,
bar.ID
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END) DESC,
bar.ID
Its a very important thing to remember and greatly simplify the code. For prior versions of sql server, one have to use separate if/else or case statements to get this behavior, would be very messy. Note that the select case statement above have to be written in the stored procedure, there is no way to make a separate function for that. A function cannot return name of column.
Note that RowCount is specific to current thread. Value of RowCount set to some value in one thread don't affect its value in another thread. This can be tested by opening to "query windows" in management studio:
1st window:
type and execute: SET ROWCOUNT 1
keep this window open
2nd window:
type and execute: select * from student
RESULT: All rows of student table are returned. If there are 10 rows then all 10 rows are returned.
Go back to the first window (it should be open already). Type and execute "select * from student". Only 1 row should be returned. Its because in this thread (a query window is thread, so do a procedure, a function etc) the rowcount is set to 1. Now set rowcount to 5 and execute "select * from student". Now 5 rows should be returned.
Note that a thread is specific to a user as well as a function. It means two users simultaneously executing a stored procedure are still in two different threads.
Step 2: Put some rows in this table.
Step 3:
Alter Procedure Efficient_Paging_Student
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS
DECLARE @MaxRows INT
SET @MaxRows = @PageSize * @PageNumber
SET ROWCOUNT @MaxRows
SELECT ID, Name, Marks
INTO #Temp
FROM student
--WHERE --conditions
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END),
ID
SET ROWCOUNT @PageSize
SELECT ID, Name, Marks
INTO #Foo
FROM #Temp
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, ID)
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END) DESC,
ID DESC
SET ROWCOUNT 0
IF(@SortDirection = 'ASC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END),
bar.ID
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END) DESC,
bar.ID
END
Step 4: Test
Efficient_Paging_Student 'ID', 'DESC', 4, 2
Efficient_Paging_Student 'Name', 'ASC', 4, 2
Explanation:
Using this example we can get any page (set of rows) having any number and any page size. For example if page number is 2 and page size is 4 then we get row numbers 5, 6, 7, 8. Note that row number here do not means id. Its not a fixed field. Its row number 5th, 6th, 7th and 8th AFTER sorting is done according to desired column name.
To make it efficient, we have to limit the rows returned in each query. Using "top" keyword don't work here because top keyword don't take a variable. However, ROWNUMBER can be set to any variable:
SET ROWNUMBER @VAR
This works.
The whole procedure is divided into 3 select statements, first of them returned a different number of rows than the last two. We can't combine the last two because of this error:
Msg 1033, Level 15, State 1, Procedure Efficient_Paging_Student, Line 41
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
It means that we can't apply "order by" clause to derived tables. Therefore we have to store that table value (coming from the second select statement) in a separate hash table called #foo. Its not an issue because the size of this table is same as size of page at front end (web page, windows form etc) which is usually a very small size (5 to 100 rows).
The actual load of this method is in the first select statement, its because here the number of rows selected is equal to the product of page size and page number. It means that if number of rows can be evenly divided by page size (for example 100 rows and 5 rows each table) then for the last page the query select the entire table. I don't know anyway to avoid this because the sorting requires us to go to that level, that is each row have to be accessed once no matter what (unless we are using views but thats a different story).
Once the first select statement is executed, the rest of the procedure runs in flash because the second and third select statements deal with only as much rows as the size of page. Since size of page is always very little (discussed above) therefore its not an issue at all.
At last we have to do the final sorting. Remember that "order by" clause sort by default in ascending order so I didn't wrote "asc" keyword anywhere in any query. To find out whether to use ascending order or descending order I had to use an if statement.
An important part here is selecting of column name. See:
(SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END)
Look that each case statement returns a column name but not in quotes !!!. It is 100% valid sql code for sql 2005 and above. Whatever column name is returns is fit in above query. Following two statements parse to same query if @SortColumn is equal to 'Name':
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY Name DESC,
bar.ID
SELECT t.ID, t.Name, t.Marks
FROM #Foo
AS bar
INNER JOIN student AS t ON bar.ID = t.ID
ORDER BY (SELECT CASE @SortColumn
WHEN 'ID' THEN convert(sql_variant, bar.ID)
WHEN 'Name' THEN bar.Name
WHEN 'Marks' THEN bar.Marks
END) DESC,
bar.ID
Its a very important thing to remember and greatly simplify the code. For prior versions of sql server, one have to use separate if/else or case statements to get this behavior, would be very messy. Note that the select case statement above have to be written in the stored procedure, there is no way to make a separate function for that. A function cannot return name of column.
Note that RowCount is specific to current thread. Value of RowCount set to some value in one thread don't affect its value in another thread. This can be tested by opening to "query windows" in management studio:
1st window:
type and execute: SET ROWCOUNT 1
keep this window open
2nd window:
type and execute: select * from student
RESULT: All rows of student table are returned. If there are 10 rows then all 10 rows are returned.
Go back to the first window (it should be open already). Type and execute "select * from student". Only 1 row should be returned. Its because in this thread (a query window is thread, so do a procedure, a function etc) the rowcount is set to 1. Now set rowcount to 5 and execute "select * from student". Now 5 rows should be returned.
Note that a thread is specific to a user as well as a function. It means two users simultaneously executing a stored procedure are still in two different threads.
Paging And Sorting In Sql Server OR Getting Middle Rows In Sql Server
CREATE PROCEDURE Student_Paging
@SortColumn varchar(50),
@SortDirection varchar(4),
@PageSize int,
@PageNumber int
AS
BEGIN
SELECT
ROW_NUMBER() OVER (ORDER BY (
SELECT CASE @SortColumn
WHEN 'Id' THEN Id
WHEN 'Name' THEN Name
WHEN 'Marks' THEN Marks
END
)
) AS ROWNUMBER,
*
INTO
#Student_Page
FROM
student
IF(@SortDirection = 'ASC')
BEGIN
SELECT *
FROM #Student_Page
WHERE ROWNUMBER BETWEEN ((@PageNumber-1) * @PageSize) + 1 AND (@PageNumber * @PageSize)
ORDER BY ROWNUMBER
END
ELSE IF(@SortDirection = 'DESC')
BEGIN
SELECT *
FROM #Student_Page
WHERE ROWNUMBER BETWEEN ((@PageNumber-1) * @PageSize) + 1 AND (@PageNumber * @PageSize)
ORDER BY ROWNUMBER DESC
END
END
GO
24 August, 2010
Haveli
Following is my imagination of an ideal haveli. Consider this as fiction because there is no real structure like this.
Haveli is like a large house, town hall, manor house, residence of governor of village, fort, castle, meeting place. It have to be one in each village. A village is 1562.5 acres including surroundings and 1000 acres not including surroundings. Haveli is 1/25 in area of the village size, that is, 40 acres = 16 hectares = 160,000 sq meters = 193,600 sq yards = 1,742, 400 sq ft. This is the total plot of haveli.
Haveli consists of a central building and surrounding field all enclosed in a thick and large wall. The building is at the center and its size is 25% of the size of plot, which means 10 acres or 200 meters x 200 meters. To visualize this, think about a 4 x 4 square tiles block with the center 2 x 2 tiles being the real thing and rest being the surrounding. A 100 meters wide empty space is there at each side of building inside the plot.
The building consists of a central fort and surrounding slanted walls. The total area of fort plus slanted walls is 200 m x 200 m = 40,000 sq m, that is 25% of the plot. The fort is 100 m x 100 m at the center, therefore at each side of fort there is 50 m wide slanted wall. The slanted walls are at an angle of 60% making it very tough for anybody to climb up. It means the height of the slanted walls is 100 meters, twice its width. At two sides of this slanted wall, in middle of each of these two sides, stairs are made to climb up.
The fort consists of two large doors, at the sides of front and back stairs. On the other two sides there are large windows for ventilation. The fort consists of 5 floors: Ground floor, First floor, Top Floor, Basement and Deep Basement. Climbing up the stairs in the slanted walls one can reach the ground floor. The ground floor is nothing but a large halls, it has no rooms, no toilets, no stores, just a single large hall. Its purpose is to gather people for meeting, for addressing and for shelter. It has no furniture. A large number of people can be placed in it.
The first floor is strictly a residence of the governor of village. Some high ranking govt officials under the governor can also have residence there. This is only if village is large enough to have two layers of management of govt servants. No non-official villager is allowed to go to the first floor. The first floor has rooms, a large central hall etc.
The top floor is a semi sphere and its only purpose is to look the surrounding area and defend the haveli and the village. It has windows at critical locations with guns pointing out for both aerial and ground defense. Its dome shape is specially designed to efficiently and flexibility point guns in air and speedily move them with the moving bombers and missiles.
The basement is the working place of the haveli. Its where there is kitchen, store house, factory and toilet. The store house store food, fuel, clothings, gas masks, tools, spare parts etc. All the weapons and ammunition is stored at the top floor in the half sphere.
The deep basement, also called "deep", is where the prison of village is placed. It also has the well of the haveli. It also have long term storage place for excretions of people of haveli in case they have to live hidden in the haveli for multiple years. Its better to have a large water storage place instead of a well to completely isolate the haveli from its surroundings. Nobody is supposed to go in the "deep" because it should be working automatically. The deep can also have liquid nitrogen compound batteries for long term storage of electricity.
Each floor is 100 meters in height. The top and bottom 10 meters are used in making roof and base, the living place is the middle 80 meters. The height and width of each floor is also 100 meters with 10 meters used in making walls and 80 meters of middle being the living place. Therefore the living place at each floor is 80 meters x 80 meters x 80 meters = 512,000 cubic meters. The height is there for a lots of purposes including maintaining moral, ventilation and to provide a large storage place. The very center of the "deep" is water storage, 50 meters x 50 meters x 50 meters = 125,000 sq meters = 125,000 tons water. Living at a comfortable usage rate of 125 kg per person per day, this is enough for 1 million person-days or 2500 person-years. The air at ground floor which is free of any substructure contains enough air for 3600 person-years.
The basement, that is the first level of basement, which is directly below the ground floor is actually on the ground itself. Its because the ground floor of fort is 100 meters in air. The height of basement is 100 meters so it starts exactly at the ground level of empty place around haveli.
Having a height of 6 ft, a person can see approx 3 miles far in a flat field, this is called horizon. The horizon increases at the rate of square root of the increase in height. In standard units, at 1.7 meters height, horizon is 4.7 kms, at 6.8 meters height, horizon is 9.4 kms and so on. Standing at the floor of the top floor, 300 meters from ground level of surrounding of haveli, one can see as far as 62.61 km covering an area of 4000 sq kms or 1 million acres. Also note that 300 meters roughly means 1000 ft.
There is a reason I chose this size. The first man, prophet Adam had a height of 20 meters. Today, height of a well built and tall man is 2 meters (6 ft 8 inches). For such a man, a bedroom size of 4 meters x 4 meters is recommended (see post about Ideal Bedroom Size). For such a man the ideal hall size is 8 meters x 8 meters, 4 times the size of a bedroom. For such a man the ideal toilet, bathroom etc size is 2 meters x 2 meters. On that basis, I chose dimensions of haveli such that even prophet Adam can live in it. At a height of 20 meters, a 80 meters x 80 meters hall of ground floor of haveli is ideal for him.
The haveli is like 5 cubes, one over another, with each cube having its own covering in all six sides (roof, base, 4 walls). Each cube is a floor. Each floor has a total volume of 100 meters x 100 meters x 100 meters = 1,000,000 cubic meters. The living place in between is 80 meters x 80 meters x 80 meters = 512,000 cubic meters. Therefore, 488,000 cubic meters structure at each floor.
At each floor there are pillars too. Its because we can't expect a 20 meter thick roof can be hanged with a gap of 80 meters between walls. Infact, we have to make a pillar at every 10 meter, and that pillar have to be 1 meter wide and 1 meter long. There have to be 7 x 7 = 49 pillars at each floor, and each pillar would be 8 meter tall. This adds up 7 x 7 x 8 x 1 x 1 = 392 cubic meters structure at each floor, negligible in front of 488,000 cubic meters.
We have five floors so we have 488,000 cubic meters x 5 = 2,440,000 cubic meters structure. All this structure need to be of stone. The slanted walls have an area of 50 meters x 100 meters x 100 meters / 2 x 4 = 1,000,000 cubic meters. So altogether we need to make 2,440,000 + 1,000,000 = 3.44 million cubic meters structure.
Haveli is like a large house, town hall, manor house, residence of governor of village, fort, castle, meeting place. It have to be one in each village. A village is 1562.5 acres including surroundings and 1000 acres not including surroundings. Haveli is 1/25 in area of the village size, that is, 40 acres = 16 hectares = 160,000 sq meters = 193,600 sq yards = 1,742, 400 sq ft. This is the total plot of haveli.
Haveli consists of a central building and surrounding field all enclosed in a thick and large wall. The building is at the center and its size is 25% of the size of plot, which means 10 acres or 200 meters x 200 meters. To visualize this, think about a 4 x 4 square tiles block with the center 2 x 2 tiles being the real thing and rest being the surrounding. A 100 meters wide empty space is there at each side of building inside the plot.
The building consists of a central fort and surrounding slanted walls. The total area of fort plus slanted walls is 200 m x 200 m = 40,000 sq m, that is 25% of the plot. The fort is 100 m x 100 m at the center, therefore at each side of fort there is 50 m wide slanted wall. The slanted walls are at an angle of 60% making it very tough for anybody to climb up. It means the height of the slanted walls is 100 meters, twice its width. At two sides of this slanted wall, in middle of each of these two sides, stairs are made to climb up.
The fort consists of two large doors, at the sides of front and back stairs. On the other two sides there are large windows for ventilation. The fort consists of 5 floors: Ground floor, First floor, Top Floor, Basement and Deep Basement. Climbing up the stairs in the slanted walls one can reach the ground floor. The ground floor is nothing but a large halls, it has no rooms, no toilets, no stores, just a single large hall. Its purpose is to gather people for meeting, for addressing and for shelter. It has no furniture. A large number of people can be placed in it.
The first floor is strictly a residence of the governor of village. Some high ranking govt officials under the governor can also have residence there. This is only if village is large enough to have two layers of management of govt servants. No non-official villager is allowed to go to the first floor. The first floor has rooms, a large central hall etc.
The top floor is a semi sphere and its only purpose is to look the surrounding area and defend the haveli and the village. It has windows at critical locations with guns pointing out for both aerial and ground defense. Its dome shape is specially designed to efficiently and flexibility point guns in air and speedily move them with the moving bombers and missiles.
The basement is the working place of the haveli. Its where there is kitchen, store house, factory and toilet. The store house store food, fuel, clothings, gas masks, tools, spare parts etc. All the weapons and ammunition is stored at the top floor in the half sphere.
The deep basement, also called "deep", is where the prison of village is placed. It also has the well of the haveli. It also have long term storage place for excretions of people of haveli in case they have to live hidden in the haveli for multiple years. Its better to have a large water storage place instead of a well to completely isolate the haveli from its surroundings. Nobody is supposed to go in the "deep" because it should be working automatically. The deep can also have liquid nitrogen compound batteries for long term storage of electricity.
Each floor is 100 meters in height. The top and bottom 10 meters are used in making roof and base, the living place is the middle 80 meters. The height and width of each floor is also 100 meters with 10 meters used in making walls and 80 meters of middle being the living place. Therefore the living place at each floor is 80 meters x 80 meters x 80 meters = 512,000 cubic meters. The height is there for a lots of purposes including maintaining moral, ventilation and to provide a large storage place. The very center of the "deep" is water storage, 50 meters x 50 meters x 50 meters = 125,000 sq meters = 125,000 tons water. Living at a comfortable usage rate of 125 kg per person per day, this is enough for 1 million person-days or 2500 person-years. The air at ground floor which is free of any substructure contains enough air for 3600 person-years.
The basement, that is the first level of basement, which is directly below the ground floor is actually on the ground itself. Its because the ground floor of fort is 100 meters in air. The height of basement is 100 meters so it starts exactly at the ground level of empty place around haveli.
Having a height of 6 ft, a person can see approx 3 miles far in a flat field, this is called horizon. The horizon increases at the rate of square root of the increase in height. In standard units, at 1.7 meters height, horizon is 4.7 kms, at 6.8 meters height, horizon is 9.4 kms and so on. Standing at the floor of the top floor, 300 meters from ground level of surrounding of haveli, one can see as far as 62.61 km covering an area of 4000 sq kms or 1 million acres. Also note that 300 meters roughly means 1000 ft.
There is a reason I chose this size. The first man, prophet Adam had a height of 20 meters. Today, height of a well built and tall man is 2 meters (6 ft 8 inches). For such a man, a bedroom size of 4 meters x 4 meters is recommended (see post about Ideal Bedroom Size). For such a man the ideal hall size is 8 meters x 8 meters, 4 times the size of a bedroom. For such a man the ideal toilet, bathroom etc size is 2 meters x 2 meters. On that basis, I chose dimensions of haveli such that even prophet Adam can live in it. At a height of 20 meters, a 80 meters x 80 meters hall of ground floor of haveli is ideal for him.
The haveli is like 5 cubes, one over another, with each cube having its own covering in all six sides (roof, base, 4 walls). Each cube is a floor. Each floor has a total volume of 100 meters x 100 meters x 100 meters = 1,000,000 cubic meters. The living place in between is 80 meters x 80 meters x 80 meters = 512,000 cubic meters. Therefore, 488,000 cubic meters structure at each floor.
At each floor there are pillars too. Its because we can't expect a 20 meter thick roof can be hanged with a gap of 80 meters between walls. Infact, we have to make a pillar at every 10 meter, and that pillar have to be 1 meter wide and 1 meter long. There have to be 7 x 7 = 49 pillars at each floor, and each pillar would be 8 meter tall. This adds up 7 x 7 x 8 x 1 x 1 = 392 cubic meters structure at each floor, negligible in front of 488,000 cubic meters.
We have five floors so we have 488,000 cubic meters x 5 = 2,440,000 cubic meters structure. All this structure need to be of stone. The slanted walls have an area of 50 meters x 100 meters x 100 meters / 2 x 4 = 1,000,000 cubic meters. So altogether we need to make 2,440,000 + 1,000,000 = 3.44 million cubic meters structure.
Golden Code: Maintain Sorting While Moving to Next Page In Data Grid
default.aspx:
< asp : datagrid id="grd" runat="server" allowpaging="true" pagesize="4" allowsorting="true" autogeneratecolumns="false" onpageindexchanged="grd_PageIndexChanged" onsortcommand="grd_SortCommand">
< columns >
< asp : boundcolumn headertext="ID" datafield="ID" sortexpression="ID" />
< asp : boundcolumn headertext="Name" datafield="Name" sortexpression="Name" />
< asp : boundcolumn headertext="Marks" datafield="Marks" sortexpression="Marks" />
< / columns >
< pagerstyle horizontalalign="Center" mode="NextPrev" />
default.aspx.cs: (Code behind file)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace Data_Controls
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
FillGrid();
if (!Page.IsPostBack)
{
FillColumnsSortDirectionList();
}
SetPagingSorting();
}
private void FillGrid()
{
DataView view = LoadView();
this.grd.DataSource = view;
this.grd.DataBind();
}
private void FillColumnsSortDirectionList()
{
DataView view = (DataView)this.grd.DataSource;
ListColumnsSortDirectionList = new List ();
foreach (DataColumn column in view.ToTable().Columns)
ColumnsSortDirectionList.Add(new Pair(column.ColumnName, "asc"));
Session.Add("ColumnsSortDirectionList", ColumnsSortDirectionList);
Session.Add("SortColumn", "ID");
}
private void SetPagingSorting()
{
((DataView)this.grd.DataSource).Sort = Session["SortColumn"].ToString() + " " +
Utility.PairList__GetValue((List)Session["ColumnsSortDirectionList"], Session["SortColumn"].ToString());
}
private DataTable LoadTable()
{
DataTable tab = new DataTable();
tab.Columns.Add("ID", typeof(int));
tab.Columns.Add("Name", typeof(string));
tab.Columns.Add("Marks", typeof(double));
tab.Rows.Add(new object[] { 1, "Atif", 85.40d });
tab.Rows.Add(new object[] { 2, "Jamal", 70.00d });
tab.Rows.Add(new object[] { 3, "Nawaz", 60.00d });
tab.Rows.Add(new object[] { 4, "Salma", 75.00d });
tab.Rows.Add(new object[] { 5, "Yasir", 78.00d });
tab.Rows.Add(new object[] { 6, "Shabnam", 50.00d });
tab.Rows.Add(new object[] { 7, "Naseem", 74.32d });
tab.Rows.Add(new object[] { 8, "Tauseef", 58.00d });
tab.Rows.Add(new object[] { 9, "Nasreen", 18.00d });
tab.Rows.Add(new object[] { 10, "Sadiq", 90.00d });
return tab;
}
private DataView LoadView()
{
return LoadTable().DefaultView;
}
protected void grd_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
this.grd.CurrentPageIndex = e.NewPageIndex;
this.grd.DataBind();
}
protected void grd_SortCommand(object source, DataGridSortCommandEventArgs e)
{
Session["SortColumn"] = e.SortExpression;
ColumnsSortDirectionList__ToggleValue(e.SortExpression);
((DataView)this.grd.DataSource).Sort = e.SortExpression + " " +
Utility.PairList__GetValue((List)Session["ColumnsSortDirectionList"], e.SortExpression);
this.grd.DataBind();
}
private void ColumnsSortDirectionList__ToggleValue(string _Name)
{
ListColumnsSortDirectionList = (List )Session["ColumnsSortDirectionList"];
foreach (Pair p in ColumnsSortDirectionList)
{
if (p.Name == _Name)
{
if (p.Value == "asc")
p.Value = "desc";
else if (p.Value == "desc")
p.Value = "asc";
break;
}
}
}
}
}
Pair.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace Data_Controls
{
public class Pair
{
public string Name;
public string Value;
public Pair(string _Name, string _Value)
{
this.Name = _Name;
this.Value = _Value;
}
}
}
Utility.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace Data_Controls
{
public class Utility
{
public static string PairList__GetName(List_PairList, string _Value)
{
string returnValue = "";
foreach (Pair p in _PairList)
{
if (p.Value == _Value)
{
returnValue = p.Name;
break;
}
}
return returnValue;
}
public static string PairList__GetValue(List_PairList, string _Name)
{
string returnValue = "";
foreach (Pair p in _PairList)
{
if (p.Name == _Name)
{
returnValue = p.Value;
break;
}
}
return returnValue;
}
}
}
Subscribe to:
Posts (Atom)