Sql server Index Drawbacks

Index Drawbacks

There are tradeoffs to almost any feature in computer programming, and indexes are no exception. While indexes provide a substantial performance benefit to searches, there is also a downside to indexing. Let's talk about some of those drawbacks now.
Indexes and Disk SpaceIndexes are stored on the disk, and the amount of space required will depend on the size of the table, and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users. To see the space required for a table, use the sp_spaceused system stored procedure in a query window.

EXEC sp_spaceused Orders

Given a table name (Orders), the procedure will return the amount of space used by the data and all indexes associated with the table, like so:

Name rows reserved data index_size unused
------- -------- ----------- ------ ---------- -------
Orders 830 504 KB 160 KB 320 KB 24 KB

According to the output above, the table data uses 160 kilobytes, while the table indexes use twice as much, or 320 kilobytes. The ratio of index size to table size can vary greatly, depending on the columns, data types, and number of indexes on a table.

Indexes and Data Modification

Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed. As we discussed earlier, indexing can help the database during data modification statements by allowing the database to quickly locate the records to modify, however, we now caveat the discussion with the understanding that providing too many indexes to update can actually hurt the performance of data modifications. This leads to a delicate balancing act when tuning the database for performance.

In decision support systems and data warehouses, where information is stored for reporting purposes, data remains relatively static and report generating queries outnumber data modification queries. In these types of environments, heavy indexing is commonplace in order to optimize the reports generated. In contrast, a database used for transaction processing will see many records added and updated. These types of databases will use fewer indexes to allow for higher throughput on inserts and updates.

Every application is unique, and finding the best indexes to use for a specific application usually requires some help from the optimization tools offered by many database vendors. SQL Server 2000 and Access include the Profiler and Index Tuning Wizard tools to help tweak performance.
Now we have enough information to understand why indexes are useful and where indexes are best applied. So start thinking about it ......

Mutable or Immutable? (String and StringBuilder)

Mutable or Immutable? (String and StringBuilder)

An object qualifies as being called immutable if its value cannot be modified once it has been created. For example, methods that appear to modify a String actually return a new String containing the modification. Developers are modifying strings all the time in their code. This may appear to the developer as mutable - but it is not. What actually happens is your string variable/object has been changed to reference a new string value containing the results of your new string value. For this very reason .NET has the System.Text.StringBuilder class. If you find it necessary to modify the actual contents of a string-like object heavily, such as in a for or foreach loop, use the System.Text.StringBuilder class.

Why string? Can't use StringBinder everywhere?

No. You can't. When initializing a StringBuilder you are going down in performance. Also many actions that you do with string can't be done with StringBinder. Actually it is used mostly for situations as explained above. Last week I show a person, who used StringBuilder to just add two strings together! its really nonsense. We must really think about the overhead of initialization. In my personal experience a StringBuilder can be used where more than four or more string concatenation take place. Also if you try to do some other manipulation (Like removing a part from the string, replacing a part in the string, etc, etc) then better not to use StringBuilder at those places. Because anyway we are creating new strings. Another important issue. We must be careful to guess the size of StringBuilder . If the size which we are going to get is more than what assigned, it must increase the size. Which will reduce the performance of it.
History

Assigning and Copying Arrays

Arrays are Reference Types

This means that the data in the object is stored somewhere in the computer's memory and the variable holds a pointer to that area of memory. If one object variable is assigned to another, only the pointer to the memory location is duplicated; both variables actually point to the same data. A change in one object's values is therefore reflected in the other object.

This can be demonstrated by assigning one array to another. A change to any of the elements of either array is visible in both:

int[] primary = new int[] {2,4,8};

// Point a second array at the same memory range using assignment
int[] secondary = primary;

// Retrieve a value from the primary array
int value = primary[1]; // value = 4

// Alter the value in the secondary array and re-read the primary
secondary[1] = 99;
value = primary[1]; // value = 99

It is important to understand the effects of assigning reference types as mistakenly modifying one instance can unexpectedly cause problems elsewhere.

Cloning Arrays

It is often necessary to create a copy of an array that may be manipulated without damaging the contents of the original. To achieve this, the array class includes a Clone method. Rather than assigning a simple reference, this creates a completely new copy of the array. The Clone method returns an object but the specific array type is not specified so this object must be cast to the correct type for assignment.

int[] primary = new int[] {2,4,8};

// Clone the array into a secondary array, casting appropriately
int[] secondary = (int[])primary.Clone();

// Retrieve a value from the primary array
int value = primary[1]; // value = 4

// Alter the value in the secondary array and re-read the primary
secondary[1] = 99;
value = primary[1]; // value = 4
value = secondary[1]; // value = 99

Why doesn't C# support multiple inheritance?

Why doesn't C# support multiple inheritance?
This answer is from Chris Brumme.I've copied the text in here in case the post disappears.

***

There are a number of reasons we don't implement Multiple Implementation Inheritance directly. (As you know, we support Multiple Interface Inheritance).

However, I should point out that it's possible for compilers to create MI for their types inside the CLR. There are a few rough edges if you go down this path: the result is unverifiable, there is no interop with other languages via the CLS, and in V1 and V1.1 you may run into deadlocks with the OS loader lock. (We're fixing that last problem, but the first two problems remain). The technique is to generate some VTables in RVA-based static fields. In order to deposit the addresses of managed methods (which probably haven't been JITted yet), you use the VTFixup construct. This construct is a table of triplets. The triplets consist of a token to a managed method, an address in your image that should be fixed up (in this case, a slot of the VTable you are creating in the RVA-based static), and some flags. The possible flags are described in corhdr.h and they allow you to specify 32- vs. 64-bit pointer sizes, control over virtual behavior, and whether some reverse-PInvoke behavior should be applied in the form of a thunk that eventually dispatches to the managed method. If we are performing an unmanaged->managed transition, you also have some control over which AppDomain should be selected for us to dispatch the call. However, one of these options (COR_VTABLE_FROM_UNMANAGED_RETAIN_APPDOMAIN) doesn't exist in V1. We added it in V1.1.

There are several reasons we haven't provided a baked-in, verifiable, CLS-compliant version of multiple implementation inheritance:

1. Different languages actually have different expectations for how MI works. For example, how conflicts are resolved and whether duplicate bases are merged or redundant. Before we can implement MI in the CLR, we have to do a survey of all the languages, figure out the common concepts, and decide how to express them in a language-neutral manner. We would also have to decide whether MI belongs in the CLS and what this would mean for languages that don't want this concept (presumably VB.NET, for example). Of course, that's the business we are in as a common language runtime, but we haven't got around to doing it for MI yet.

2. The number of places where MI is truly appropriate is actually quite small. In many cases, multiple interface inheritance can get the job done instead. In other cases, you may be able to use encapsulation and delegation. If we were to add a slightly different construct, like mixins, would that actually be more powerful?

3. Multiple implementation inheritance injects a lot of complexity into the implementation. This complexity impacts casting, layout, dispatch, field access, serialization, identity comparisons, verifiability, reflection, generics, and probably lots of other places.

It's not at all clear that this feature would pay for itself. It's something we are often asked about. It's something we haven't done due diligence on. But my gut tells me that, after we've done a deep examination, we'll still decide to leave the feature unimplemented.