Indexers

Indexers allow your class to be used just like an array. On the inside of a class, you manage a collection of values any way you want.

using System;

///

///

     A simple indexer example.
///

class IntIndexer
{
    private string[] myData;

    public IntIndexer(int size)
    {
        myData = new string[size];

        for (int i=0; i < size; i++)
        {
            myData[i] = "empty";
        }
    }

    public string this[int pos]
    {
        get
       {
            return myData[pos];
        }
        set
       {
            myData[pos] = value;
        }
    }

    static void Main(string[] args)
    {
        int size = 10;

        IntIndexer myInd = new IntIndexer(size);

        myInd[9] = "Some Value";
        myInd[3] = "Another Value";
        myInd[5] = "Any Value";

        Console.WriteLine("\nIndexer Output\n");

        for (int i=0; i < size; i++)
        {
            Console.WriteLine("myInd[{0}]: {1}", i, myInd[i]);
        }
    }
}

Partial Class

Partial type definitions allow the definition of a class, struct or interface to be split into multiple files.

In File1.cs:

namespace PC
{
    partial class A { }
}

In File2.cs:

namespace PC
{
    partial class A { }
}

Remarks

---

Splitting a class, struct or interface type over several files can be useful when working with large projects, or with automatically generated code such as that provided by the Windows Forms Designer. For more information, see Partial Classes.

New features in SQl Server 2008.

1.Compressed Storage of Tables and Indexes

 SQL Server 2008 supports both row and page compression for both tables and indexes. Data compression can be configured for the following database objects:

•     A whole table that is stored as a heap.
•     A whole table that is stored as a clustered index.
•     A whole nonclustered index.
•     A whole indexed view.

2. FILESTREAM Storage
FILESTREAM in SQL Server 2008 enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. VARBINARY(MAX)
Standard VARBINARY(MAX) data is limited to 2 GB.

3. Sparse Columns
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.

CREATE TABLE dbo.T1

(

  keycol INT                   NOT NULL PRIMARY KEY,

  col1   VARCHAR(20)           NOT NULL,

  col2   INT            SPARSE     NULL,

  col3   CHAR(10)       SPARSE     NULL,

  col4   NUMERIC(12, 2) SPARSE     NULL

);

4. IntelliSense for Query Editing

5.Query Editor Regions

6.Filtered Indexes 
SQL Server 2008 introduces filtered indexes and statistics. You can now create a nonclustered index based on a predicate, and only the subset of rows for which the predicate holds true are stored in the index B-Tree. Similarly, you can manually create statistics based on a predicate.

CREATE NONCLUSTERED INDEX idx_currate_notnull

  ON Sales.SalesOrderHeader(CurrencyRateID)

  WHERE CurrencyRateID IS NOT NULL;

7. DDL Trigger Enhancements 

In SQL Server 2008, the type of events on which you can now create DDL triggers is enhanced to include stored procedures that perform DDL-like operations. This gives you more complete coverage of DDL events that you can capture with triggers.

Many stored procedures perform DDL-like operations. Before SQL Server 2008, you could not capture their invocation with a trigger. Now you can capture many new events that fire as a result of calls to such procedures. For example, the stored procedure sp_rename now fires a trigger created on the new RENAME event. 

 8.Grouping Sets

SQL Server 2008 introduces several extensions to the GROUP BY clause that enable you to define multiple groupings in the same query. 

SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty

FROM dbo.Orders

GROUP BY GROUPING SETS (

  ( custid, empid, YEAR(orderdate) ),

The following statements are equivalent:

SELECT customer, year, SUM(sales) FROM T GROUP BY GROUPING SETS ((customer), (year))

SELECT customer, NULL as year, SUM(sales) FROM T GROUP BY customer UNION ALL SELECT NULL as customer, year, SUM(sales) FROM T GROUP BY year

 9. MERGE Statement

The new MERGE statement is a standard statement that combines INSERT, UPDATE, and DELETE actions as a single atomic operation based on conditional logic.

 MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
  ON TGT.custid = SRC.custid
WHEN MATCHED THEN
  UPDATE SET
    TGT.companyname = SRC.companyname,
    TGT.phone = SRC.phone,
    TGT.address = SRC.address
WHEN NOT MATCHED THEN
  INSERT (custid, companyname, phone, address)
  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)

10. Table Types 

SQL Server 2008 introduces table types and table-valued parameters that help abbreviate your code and improve its performance. 

 You use the CREATE TYPE statement to create a new table type. For example, the following code defines a table type called OrderIDs in database:

CREATE TYPE dbo.OrderIDs AS TABLE ( pos INT NOT NULL PRIMARY KEY,
  orderid INT NOT NULL UNIQUE );

DECLARE @T AS dbo.OrderIDs;
INSERT INTO @T(pos, orderid) VALUES(1, 51480),(2, 51973),(3, 51819);

SELECT pos, orderid FROM @T ORDER BY pos;

11. Large UDTs

In SQL Server 2005, user-defined types (UDTs) in the CLR were limited to 8,000 bytes. SQL Server 2008 lifts this limitation and now supports large UDTs. Similar to the built-in large object types that SQL Server supports, large UDTs can now reach up to 2 GB in size.

12. Date and Time Data Types

 Before SQL Server 2008, date and time improvements were probably at the top of the list of the most requested improvements for SQL Server—especially the request for separate date and time data types, but also for general enhanced support for temporal data. SQL Server 2008 introduces four new date and time data types—including DATE, TIME, DATETIME2, and DATETIMEOFFSET—as well as new functions that operate on the new types and enhancements to existing functions. 

13. CONVERT Function

The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values.

14. Spatial:  Two new spatial data types have been added--GEOMETRY and GEOGRAPHY--which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.

15. HIERARCHYID

While hierarchical tree structures are commonly used in many applications, SQL Server has not made it easy to represent and store them in relational tables. In SQL Server 2008, the HIERARCHYID data type has been added to help resolve this problem. It is designed to store values that represent the position of nodes of a hierarchal tree structure.

For example, the HIERARCHYID data type makes it easier to express these types of relationships without requiring multiple parent/child tables and complex joins.

•     Organizational structures
•     A set of tasks that make up a larger projects (like a GANTT chart)
•     File systems (folders and their sub-folders)
•     A classification of language terms
•     A bill of materials to assemble or build a product
•     A graphical representation of links between web pages 

16. Roll UP

It will do get the sum of group by columns in another row.

SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City WITH ROLLUP

SQL Server Log Files

1. SQL Server Setup Log
You might already be familiar with the SQL Server 2005 Setup log, which is located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. If the summary.txt log file shows a component failure, you can investigate the root cause by looking at the component’s log, which you’ll find in the %Program-Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files directory.

2. SQL Server Profiler Log
SQL Server Profiler, the primary application-tracing tool in SQL Server 2005, captures the system’s current database activity and writes it to a file for later analysis. You can find the Profiler logs in the log .trc file in the %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory.

3. SQL Server Agent Log
SQL Server 2005’s job scheduling subsystem, SQL Server Agent, maintains a set of log files with warning and error messages about the jobs it has run, written to the %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory. SQL Server will maintain up to nine SQL Server Agent error log files. The current log file is named SQLAGENT .OUT, whereas archived files are numbered sequentially. You can view SQL Server Agent logs by using SQL Server Management Studio (SSMS). Expand a server node, expand Management, click SQL Server Logs, and select the check box for SQL Server Agent.

4. Windows Event Log
An important source of information for troubleshooting SQL Server errors, the Windows Event log contains three useful logs. The application log records events in SQL Server and SQL Server Agent and can be used by SQL Server IntegrationServices (SSIS) packages. The security log records authentication information, and the system log records service startup and shutdown information. To view the Windows Event log, go to Administrative Tools, Event Viewer.

5. SQL Server Error Log
The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially. The current error log file is named ERRORLOG. To view the error log, which is located in the %Program-Files%\Microsoft SQL Server\MSSQL.1MSSQL\LOG\ERRORLOG directory, open SSMS, expand a server node, expand Management, and click SQL Server Logs.

Anonymous Methods.

An anonymous method is a method without a name - which is why it is called anonymous. You don't declare anonymous methods like regular methods. Instead they get hooked up directly to events.

>> Because you can hook an anonymous method up to an event directly, a couple of the steps of working with delegates can be removed.

>> An anonymous method uses the keyword, delegate, instead of a method name. This is followed by the body of the method. Typical usage of an anonymous method is to assign it to an event.

By using anonymous methods, you reduce the coding overhead in instantiating delegates because you do not have to create a separate method.

Example:

       Button btnHello = new Button();
        btnHello.Text = "Hello";

        btnHello.Click +=
            delegate
            {
                MessageBox.Show("Hello");
            };

        Controls.Add(btnHello);

Using Parameters with Anonymous Methods:

        Button btnHello = new Button();
        btnHello.Text = "Hello";

        btnHello.Click +=
            delegate
            {
                MessageBox.Show("Hello");
            };

        Button btnGoodBye = new Button();
        btnGoodBye.Text = "Goodbye";
        btnGoodBye.Left = btnHello.Width + 5;
        btnGoodBye.Click +=
            delegate(object sender, EventArgs e)
            {
                string message = (sender as Button).Text;
                MessageBox.Show(message);
            };

        Controls.Add(btnHello);
        Controls.Add(btnGoodBye);

What is impersonation?

Impersonation is commonly used in applications that rely on Microsoft Internet Information Services (IIS) to authenticate the user.   


you must include an tag in the Web.config file of this application and set the impersonate attribute to true. For example:

configuration
  system.web
    identity impersonate="true"
  system.web
configuration

When impersonation is enabled, only your application code runs under the context of the impersonated user. Applications are compiled and configuration information is loaded using the identity of the ASP.NET process.

You can also add support for specific names to run an application as a configurable identity, as shown in the following example:

  
identity impersonate="true"   userName="contoso\Jane"   password="********" 

What is Jagged Arrays?

A jagged array is an array whose elements are arrays. The elements of a jagged array can be of different dimensions and sizes. A jagged array is sometimes called an "array of arrays." The following examples show how to declare, initialize, and access jagged arrays.

int[][] jaggedArray = new int[3][];

Before you can use jaggedArray, its elements must be initialized.

You can initialize the elements like this: 

jaggedArray[0] = new int[5];
jaggedArray[1] = new int[4];
jaggedArray[2] = new int[2];

Each of the elements is a single-dimensional array of integers. The first element is an array of 5 integers, the second is an array of 4 integers, and the third is an array of 2 integers.
It is also possible to use initializers to fill the array elements with values, in which case you do not need the array size. For example:

jaggedArray[0] = new int[] { 1, 3, 5, 7, 9 };
jaggedArray[1] = new int[] { 0, 2, 4, 6 };
jaggedArray[2] = new int[] { 11, 22 };

What is difference between constants and read-only?

Constant variables are declared and initialized at compile time. The value can’t be changed after wards.

Read-only variables will be initialized only from the Static constructor of the class. Read only is used only when we want to assign the value at run time.

What are generics in C#.NET?

1. Generics are used to make reusable code classes to decrease the code redundancy,

2. Increase type safety and performance.

3. Using generics, we can create collection classes.

4. To create generic collection, System.Collections.Generic namespace should be used instead of classes such as ArrayList in the System.Collections namespace. Generics promotes the usage of parameterized types.

Difference between Throw and Throw ex in c#?

Exception bubbling means that even though you are catching the exception and doing something with it, you want that exception to "bubble" up from your code to the calling code so it has a chance to do something with that exception. This is a fairly common scenario, but it has the potential to cause some major problems when you are debugging.


Throw Ex: (Stack trace information lost)

 The original stack trace info gets overwritten. So you loose original exceptions's stack trace as only the last point from which the exception was thrown is available.

   1: try

   2: {

   3:     // do some operation that can fail

   4: }

   5: catch (Exception ex)

   6: {

   7:     // do some local cleanup

   8:     throw ex;

   9: }

In this case, the stack trace is truncated below the method that failed. What this means is that when you look at the stack trace, it will look as if the exception originated in your code. This isn't always the case, particularly if you are bubbling up a CLR generated exception (like a SqlException). This is a problem known as "breaking the stack", because you no longer have the full stack trace information. 
  
Throw: (Stack trace information preserved)

   1: try

   2: {

   3:     // do some operation that can fail

   4: }

   5: catch (Exception ex)

   6: {

   7:     // do some local cleanup

   8:     throw;

   9: }

 Throw, on the other hand retains the stack trace with the original point of exception available.


It is always advised to use “throw” because it provides more accurate error information.

Recommendations for Abstract Classes vs. Interfaces

1. If you anticipate creating multiple versions of your component, create an abstract class. Abstract classes provide a simple and easy way to version your components. By updating the base class, all inheriting classes are automatically updated with the change. 
2. Interfaces, on the other hand, cannot be changed once created. If a new version of an interface is required, you must create a whole new interface.
3. If you are designing small, concise bits of functionality, use interfaces.
4. If you are designing large functional units, use an abstract class.

When to prefer an interface ( Interfaces are more used to set standards.)

Back when I wrote about the importance of composition, I mentioned that it is extremely useful when you don’t want a massive hierarchical type framework. The same applies to interfaces. This isn’t my example, but its the best one Ive come across. Lets say you have an interface for a Director and another interface for a Actor.

public interface Actor{
   Performance say(Line l);
}

public interface Director{
   Movie direct(boolean goodmovie);
}

In reality, there are Actors who are also Directors. If we are using interfaces rather than abstract classes, we can implement both Actor and Director. We could even define an ActorDirector interface that extends both like this:

public interface ActorDirector extends Actor, Director{
...
}

We could achieve the same thing using abstract classes. Unfortunately the alternative would require up to 2^n (where n is the number of attributes) possible combinations in order to support all possibilities.

When to prefer an Abstract class

Abstract classes allow you to provide default functionality for the subclasses. Common knowledge at this point. Why is this extremely important though? If you plan on updating this base class throughout the life of your program, it is best to allow that base class to be an abstract class. Why? Because you can make a change to it and all of the inheriting classes will now have this new functionality.

If the base class will be changing often and an interface was used instead of an abstract class, we are going to run into problems. Once an interface is changed, any class that implements that will be broken.

INDEXS in SQL SERVER

A SQL table explanation is not good enough for getting the desired data very quickly or sorting the data in a specific order.

What we actually need for doing this is some sort of cross reference facilities where for certain columns of information within a table, it should be possible to get whole records of information quickly. But if we consider a huge amount of data in a table, we need some sort of cross reference to get to the data very quickly. This is where an index within SQL Server comes in.

So an index can be defined as:

• “An index is an on-disk structure associated with a table or views that speed retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view”. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.”

Index Structures

For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom, as shown in Figure below.

 
Figure : B-tree structure of a SQL Server index

When a query is issued against an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes. The query engine continues down through the index nodes until it reaches the leaf node.

Clustered Indexes

A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node.

An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order.  As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.

Indexes are first sorted on the first column in the index, then any duplicates of the first column and sorted by the second column, etc.

Nonclustered Indexes

In non-clustered index the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row locator’s structure depends on whether it points to a clustered table or to a heap.
1. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row.
2. If referencing a heap, the row locator points to the actual data row. 

(Note: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.)

 --> Nonclustered indexes cannot be sorted like clustered indexes.

Max number of clustered index in table: 1

Max number of non-clustered index in table: 

1. In sql server 2005- 249 
2. in sql server 2008- 999

 Composite index:
 An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index.

 Consider the following guidelines when planning your indexing strategy:

1. For tables that are heavily updated, use as few columns as possible in the index, and don’t over-index the tables.



2. If a table contains a lot of data but data modifications are low, use as many indexes as necessary to improve query performance.

3. For clustered indexes, try to keep the length of the indexed columns as short as possible.

4. The uniqueness of values in a column affects index performance. In general, the more duplicate values you have in a column, the more poorly the index performs.

5. In multi-column indexes, list the most selective (nearest to unique) first in the column list. For example, when indexing an employee table for a query on social security number (SSN) and last name (lastName), your index declaration should be:

CREATE NONCLUSTERED INDEX ix_Employee_SSN
ON dbo.Employee (SSN, lastName);

Syntax:

Create Clustered Index index_name on table_name (Columns_name)

CREATE NONCLUSTERED INDEX ix_Employee_SSN
ON dbo.Employee (SSN, lastName);

 Disadvantages:

1. Both clustered indexes, and nonclustered indexes take up additional disk space. The amount of space that they require will depend on the columns in the index, and the number of rows in the table.

2. Indexes will increase the amount of time that your INSERT, UPDATE and DELETE statement take, as the data has to be updated in the table as well as in each index.

3. Columns of the TEXT, NTEXT and IMAGE data types can not be indexed using normal indexes. Columns of these data types can only be indexed with Full Text indexes.

Disadvantage for Clustured Index:

If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. A table's clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.

Disadvantage for NonClustured Index :

The disadvantage of a non-clustered index is that it is slightly slower than a clustered index and they can take up quite a bit of space on the disk.

Another disadvantage is using too many indexes can actually slow your database down. Thinking of a book again, imagine if every "the", "and" or "at" was included in the index. That would stop the index being useful - the index becomes as big as the text! On top of that, each time a page or database row is updated or removed, the reference or index also has to be updated.

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.
The basic syntax is as follows:

DROP INDEX index_name;