Custom Paging in SQL Server

Brfore discussing custom paging, lets discusse how default paging works. Data controls like gridview are used to display data. When data is large you want to show it in parts that is possible using pagination feature of gridview. Actually gridview merely shows data, its the datasource attached to the gridview that fetchs data from the database.Now with the default paging, datasource of the gridview retrives whole data from the database and then gridview shows only the selective data from whole set of data based on the page index that user has choosen.
So, in the default paging, although you see subset of data but actually whole set of data is retrived from the DB that makes this approach preformance regressive.

Now, what is custom paging?

Through custom paging only selective set of data is retirved from the database unlike default paging. To achieve this, SQl Server 2005 has introduce a feature called ROW_NUMBER() that can be used in a query to retrive selective data from the database.

Following code example will show how to use ROW_NUMBER() in an SP that will get selective records from DB based on page index to be shown and no. of record per page parameters passed to it.

-- Create table
create table [dbo].[NUM]
(
[n] [int] NOT NULL,
[s] [varchar](128) NULL
)
GO
--Now insert 1000 records in the table.
set nocount on
declare @n int,@v int
set @n=0
while @n<1000
begin
set @v=(cast((rand()*100)as int)%100) + @n
insert into NUM
select @v,'Value: '+convert(varchar,@v)
set @n=@n+1
end
GO

-- Create stored procedure for retrieving records using sql server custom paging feature

create proc GetDataByCustomPaging
@pageindex int,
@pagesize int
as
begin
select n,s from (select ROW_NUMBER() over (order by n) as RowNum ,n,s from NUM) as tblNUM
where RowNum between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
end

-- Execute Sp by passing page index and the size of page

exec GetDataByCustomPaging 2,10

Gridview control also supports custom paging but you need to do some coding to implement it in gridview.

Pointers in c#

Sometimes people get confused when they are asked "Can we use pointers in C#?", they retorthow it can be? C# is a managed language and we can only write managed code in C# whereas pointers are unmanaged. But they are wrong... you can use pointers in C# with some extra care.

Let me first clear what is managed and unmanaged code?
Managed code is executed under the control of Common Language Runtime (CRL) and takes advantage of services provided by CLR like garbage collection(Automatic memory allocation and de-allocation), code level security,CLS etc. Whereas unmanaged code does not execute under full control of CLR so it can create some problem therefore its also called unsafe.

But CLR allows programmer to write unmanaged code along with managed code using a keyword "unsafe".you can use this keyword for any class, method or code block that contains unmanaged code.Like...

unsafe class Class1 {}

static unsafe void Method1( int* ptr, int len{...}

unsafe
{
int* ptr;
int i = 1;
ptr = &i;
System.Console.WriteLine("Value of i is: " + *ptr);
}

To compile unmanaged code along with managed code you have to set "allow unsafe code" option in the build tab of the project properties window as true.

One more thing to remember when using pointers...

Because GC cleans up memory automatically, it can change the location of an object in the memory during cleaning. If that happens it may be the chance that your pointer will point to wrong location in the memory. This scenario is very difficult to debug as your program will compile sucessfully but will not give desired result.

So, to avoid such situation C# provide a keyword "Fixed" that informs CLR not to change the location of an object referenced by the pointer. Like...

// col is a managed variable.
Colour col = new Colour();
// use fixed to get address of col.R in the pointer
fixed ( int* ptr = &col.R)
{
*ptr = 255;
}

Web Page Life Cycle

Here I am not going to explain web request process but will just give you the sequence of occurance of a web page events.

Below is the sequence of web page events:

Page Initialization
Load ViewState
Load Post Data
Page Load
Raised Postback Changed Event
Raised Postback
Page PreRender
Save ViewState
Page Render
Page Unload

Now, lets take a scenario, there is a web page built upon a master page and contains a web usercontrol that has child web usercontrol in it.

Here is the sequence of events post execution of HttpModules:

Initialization

Page- PreInit

Init - ChildUserControl
Init - UserControl
Init - MasterPage
Init - Page

Page- InitComplete

Page- PreLoad

Load- Page
Load- MasterPage
Load- UserControl
Load- ChildUserControl

PostBack Event

DataBinding- Page
DataBinding- MasterPage
DataBinding- UserControl
DataBinding- ChildUserControl

Page- LoadComplete

PreRender- Page
PreRender- MasterPage
PreRender- UserControl
PreRender- ChildUserControl

Page- PreRenderComplete

Page- SaveViewStateComplete

Unload- ChildUserControl
Unload- - UserControl
Unload- - MasterPage
Unload- - Page

Difference between Temp tables and Table variables in Sql Server

Following are the difference between Temp tables and Table variables:

1. Transaction log are not recorded for table variables so they are transaction netrual or you can say they are out of scope of transaction mechanism. Whereas temp tables participate in transactions just like normal tables.

2. Table variables can not be altered it means no DDL action is allowed on them. Whereas temp tables can be altered.

3. Stored Procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

4. Unlike temp tables, table variables memory resident but not always. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.

5. There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. Although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

Table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable.

No statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable. Queries involving table variables don't generate parallel plans.

My Suggestions:

1. Use table variable where you want to pass table to the Sp as parameter b'coz there is no other choice.
2. Its found that table variable are slow in sql server 2005 than 2000 on similar data and circumstances, so if you have used table variables extensively in you BD and planning to migrate from sql server 2000 to 2005, make your choice carefully.
3. Table variables are OK if used in small queries and for processing small amount of data otherwise go for temp tables.
4. If your are using very complex business logic in your SP, its better using temp table than table variables.

Add an Icon to URL

How about giving personal feel to the url of your website by changing icon of your site url.

Wanna try it...Follow below given steps:

1. Create an icon file with the name "favicon.ico" or find and ico file in you system and rename its copy for your use. Make sure the size should be 16 X 16.
2. Now add this file in the root of your website.
3. And add following 2 lines in the head section of your website's home page but if you are using master page in your website then add these line in the master page's head section and it will be displayed for every web page of your site.

< link rel="icon" href="favicon.ico" type="image/ico" / >
< link rel="SHORTCUT ICON" href="favicon.ico" / >

Thats it...

Now open your site in new browser window and see the effect.