Static Class

1. Static Class cannot be instantiated unlike the unstatic class. You should directly access its Method via the ClassName.MethodName
   
   
2. A Program can't tell when it is going to load static class but its definitely loaded before the call.
   
   
3. A Static class will always have the static constructor and its called only once since after that its in the memory for its lifetime.
   
   
4. A Static class can contain only static members. So all the members and functions have to be static.
   
   
5. A Static class is always sealed since it cannot be inherited further. Further they cannot inherit form any other class (except Object)

Static Constructor

This is a special constructor and gets called before the first object is created of the class. The time of execution cannot be determined, but it is definitely before the first object creation - could be at the time of loading the assembly. Notes for Static Constructors:

1. It is used to initialize static data members.
   
2. Can't access anything but static members.
   
3. The static constructor should be without parameters.
4. Can't have access modifiers like Public, Private or Protected.
5. There can be only one static constructor in the class.
6. A class can have static as well as non static constructor without arg at same time.

The syntax of writing the static constructors is also damn simple. Here it is:

public class Sample{

    static Sample()

    {

        // Only static members are accessible.

        // Initialization code can be here.

    }

    // Other class methods goes here

}

Let us understand features step by step here.

Firstly, the call to the static method is made by the CLR and not by the object, so we do not need to have the access modifier to it.

Secondly, it is going to be called by CLR, who can pass the parameters to it, if required. So we cannot have parameterized static constructor.

Thirdly, non-static members in the class are specific to the object instance. So static constructor, if allowed to work on non-static members, will reflect the changes in all the object instances, which is impractical. So static constructor can access only static members of the class.

Fourthly, overloading needs the two methods to be different in terms of methods definition, which you cannot do with Static Constructors, so you can have at the most one static constructor in the class.

Now, one question raises here, can we have two constructors as:

public class Sample

{

    static Sample()

    {

        // Only static members are accessible.

        // Initialization code can be here.

    }

    public Sample()

    {

        // Code for the First Constructor.

    }

    // Other class methods goes here

}

This is perfectly valid, though doesn't seem to be in accordance with overloading concepts. But why? Because the time of execution of the two methods are different. One is at the time of loading the assembly and one is at the time of object creation.

SQL Server Q & A -1

1. How to optimize stored procedures in SQL Server?
Ans: I. Use where clause
II. Select only required fields
III. Do join on indexed key fields

2. What is the difference between Stored procedure and User defined functions?

3. Why should we go for Stored Procedures? Why not direct queries?
Ans: SP are precompiled and contain an execution plan with it. Hence, they are faster.

4. How many NULL values we can have in a Unique key field in SQL Server?
Ans: Only one. In case of Oracle, we can have multiple NULL values in a Unique key field.

5. What is correlated subquery?
6. What is an index. What are the types?
Indexes in databases are very much similar to Indexes in Books. Indexes help in searching data faster.

Types: Clustered Index
Non-Clustered Index

7. What is the difference between a clustered index and a non-clustered index?
Clustered Index:
1 Only one clustered index allowed per table
2 Physically rearranges the data
3 For use on columns that are frequently searched for range of data

Non-clustered Index:
1 Upto 249 non-clustered index allowed per table
2 Doesn’t rearrange the data. Keeps a pointer to the data.
3 For use on columns that are searched for single value.

8. What is a join? What are the types of joins?
Joins are used to retrieve data from multiple tables.
Following are the types of joins:
Inner join
Left outer join
Right outer join
Full outer join
Cross join


9. What is a transaction?
A SQL transaction is a sequence of operations performed as a single unit of work. If all the tasks are completed successfully, then the transaction is committed (saved). If a single task fails, the transaction is rolled back (discarded).

10. What is ACID property of transaction?
A SQL transaction must exhibit ACID property, i.e Atomicity, Consistency, Isolation, and Durability.

Atomicity: A transaction is always treated as a single unit of work, i.e. either all the tasks are performed or none of them, no intermediate stage.
Consistency: When a transaction is completed, it must leave all data in a consistent state.
Isolation: Modifications made by a transaction must be isolated from the modifications made by other transactions.
Durability: After a transaction is completed, it’s effects are permanently in place in the system.

11. What is SET NOCOUNT ON?
When we perform a SELECT, INSERT, UPDATE or DELETE query, it returns a COUNT (number of rows affected) when SET NOCOUNT OFF. If SET NOCOUNT ON, it doesn’t return the COUNT.

12. How to delete exactly duplicate records from a table?
There are many ways. Simplest answer is:
i. Let the table tab1 contains duplicate records.
ii. Insert distinct records from tab1 in a temporary table #temp
INSERT INTO #temp
SELECT DISTINCT * FROM tab1
iii. Delete all rows from original table tab1
DELETE FROM tab1
iv. Insert from temporary table
INSERT INTO tab1
SELECT * FROM #temp
Try other solutions yourself.

13. How to get nth highest salary from employee table.
The query below demonstrates how to find the 5th highest salary. Replace 5 with any integer to get nth salary.

SELECT TOP 1 SALARY
FROM (SELECT DISTINCT TOP 5 SALARY
FROM EMPLOYEE ORDER BY SALARY DESC) a
ORDER BY SALARY ASC

Enabling SQL Cache Dependency in ASP.NET

1• Enable notifications for the database.
2• Enable notifications for individual tables.
3• Enable ASP.NET polling using “web.config” file
4• Finally use the Cache dependency object in your ASP.NET code

1. Enabling notifications for the database.

a. Enable notifications for the database, that can be done by using aspnet_regsql.exe, a command line utility.

CD c:\[WinDir]\Microsoft.NET\Framework\[Version] directory

b. execute the command at the same location as

c:>[WinDir]\Microsoft.NET\Framework\[Version] directory> aspnet_regsql -ed -E -d MyDBName

Switch	Description
-ed	command-line switch
-E	Use trusted connection
-S	Specify server name it other than the current computer you are working on
-d	Database Name

The above command will cause to create one new table and new stored procedures as specified below in the database.

Essentially, when a change takes place, a record is written in this table. The SQL Server
polling queries this table for changes.

Procedure Name	Description
AspNet_Sql CacheRegisterTable StoredProcedure	This stored procedure sets a table to support notifications. This process works by adding a notification trigger to the table, which will fire when any row is inserted, deleted, or updated.
AspNet_SqlCache UnRegisterTable StoredProcedure	This stored procedure takes a registered table and removes the notification trigger so that notifications won't be generated.
AspNet_Sql CacheUpdateChange IdStoredProcedure	The notification trigger calls this stored procedure to update the AspNet_SqlCacheTablesFor ChangeNotification table, thereby indicating that the table has changed.
AspNet_SqlCache QueryRegistered TablesStored Procedure	This extracts just the table names from the AspNet_SqlCacheTablesForChangeNotification table. It’s used to get a quick look at all the registered tables.
AspNet_SqlCache PollingStored Procedure	This will get the list of changes from the AspNet_SqlCacheTablesForChangeNotification table. It is used to perform polling.

Table Name	Description
AspNet_SqlCacheTablesForChangeNotification	Has three columns: tableName, notificationCreated, and changeId. This table is used to track changes. Essentially, when a change takes place, a record is written into this table. The SQL Server polling queries this table. Also a set of stored procedures is added to the database as well. See the following table.

2. Enabling notification for individual tables

After enabling notification for database, need to enable notification support for each individual table.

a. This also can be done manually using AspNet_SqlCacheRegisterTableStoredProcedure in query analyzer, for example, as given below:

exec AspNet_SqlCacheRegisterTableStoredProcedure 'TableName'

b. This also can be done with the use of "aspnet_regsql" with parameters as discussed

Parameter	Description
-et	to enable a able for sql cache dependency notifications
-t	to name the table

e.g.

aspnet_regsql -et -E -d Northwind -t 'TableName'

 Registering tables for notification internally creates trigger for the tables. For instance for
a "TableName" table the following trigger is created. So any modifications done to the
"TableName" table will update the "AspNet_SqlCacheNotification" table.

CREATE TRIGGER
dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON
[TableName]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure
N'Products‘
END

When you make a change in the table (such as inserting, deleting or
updating a record), the change Id column is incremented by 1.ASP.NET queries this table
repeatedly keeps track of the most recent changed values for every table. When this
value changes in a subsequent read, ASP.NET knows that the table has changed.

Continue .......

What are the various ways to maintain state in web application?

√ session variables
√ Hidden fields
√ View state
√ Hidden frames
√ Cookies
√ Query strings

What is scavenging ?

The feature of caching is one of the useful technologies that exists in web application to improve the performance of the application in many scenarios but also have drawback that it uses numerous resources of the server that includes a great amount of memory which sometimes causes the complete memory gets exhausted.  

 So when server running your ASP.NET application runs low on memory resources, items are removed from cache depending on cache item priority. Cache item priority is set when you add item to cache. By setting the cache item priority controls the items scavenging are removed first

Call Back method creation for a cached item

Ensures cache to be updated based on changes after removal of corresponding cached item.
 
CacheItemRemovedCallback delegate: An instance of this delegate is passed to the insert method when we insert a cache item.


Signature of the delegate:

public delegate void CacheItemRemovedCallback( string key, 
    object value, CacheItemRemovedReason reason );

Parameter	Description
key	The index location for the item removed from the cache.
value	The object item removed from the cache.
reason	The reason the item was removed from the cache, as specified by the CacheItemRemovedReason enumeration.

CacheItemRemovedReason enumeration: a very useful enumeration used for getting reason of expiration.

Parameter	Description
DependencyChanged	The item is removed from the cache because a file or key dependency changed.
Expired	The item is removed from the cache because it expired.
Removed	The item is removed from the cache by a Remove method call, or by an Insert method call that specified the same key.
Underused	The item is removed from the cache because the system removed it to free memory.

e.g.

public void RemovedCallback(String k,
Object v,CacheItemRemovedReason r) {
// Code to check the reason of expiration.

// Logic to repopulate cache item using new values

}