Sparse Columns in SQL Server 2008

Sparse Columns are a new feature introduced in SQL Server 2008. Sparse columns are ordinary columns that have an optimized storage for NULL values. Sparse columns require more storage space for nonnull values than the space required for identical data that is not marked SPARSE. Thus, they reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

Sparse columns should be used when the space saved is at least 20 percent to 40 percent. Sparse columns can be used with column sets (column sets are defined by using the CREATE TABLE or ALTER TABLE statements) and filtered indexes.

Example

CREATE TABLE DocumentStore

(in_doc_id int PRIMARY KEY,

vc_title varchar(200) NOT NULL,

vc_production_specification varchar(20) SPARSE NULL,

in_production_location smallint SPARSE NULL,

vc_marketing_survey_group varchar(20) SPARSE NULL);

GO

Characteristics of Sparse Columns

• The Sparse keyword is used by the SQL Server Database Engine to optimize the storage of values in that column. When the column value is NULL for any row, the values require no storage.
• A table having sparse columns has the same catalog views as for a typical table. The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.
• Since sparse columns are a property of the storage layer, a SELECT…INTO statement does not copy over the sparse column property into a new table.

How to keep a local variable in scope across a try and catch block

The following code is erroneous, and won't work, because oSqlConnection goes out of scope before you enter the catch block.

try

{

SqlConnection oSqlConnection = new SqlConnection();

oSqlConnection.Open();

}

catch

{

if (oSqlConnection != null) oSqlConnection.Close();

}

Fix for the Issue – The fix is simple - just declare oSqlConnection before entering the try block.

SqlConnection oSqlConnection = null; // Note the assignment to null to avoid error CS0165 - Use of possibly unassigned local variable 'oSqlConnection'.

try

{

oSqlConnection = new SqlConnection();

oSqlConnection.Open();

}

catch

{

if (oSqlConnection != null) oSqlConnection.Close();

}

For this particular example, you could wrap the SqlConnection class in one that implements IDisposable (if it does not already), so that you could then use a using statement instead of extending the scope of the local.

What is a Stored Procedure

A stored procedure (SP) is a precompiled set of T-SQL statements, the basic purpose of which is to group T-SQL statements together to perform a task or set of multiple tasks. It is stored in the data dictionary, and can be executed either from the SQL Server Management Studio or from within an application as required.

Stored procedures can execute batches of T-SQL statements, and return multiple result sets as well. It can accept input parameters and can return one or more output parameters to the calling procedure or batch. A stored procedure can call other stored procedures and can also return status information to the calling procedure to indicate whether they succeeded or failed.

Benefits of a Stored Procedure

• Modular Programming – After a Stored Procedure is created, it can be invoked multiple times from multiple places from any application. If any modification is needed in the SP, it needs to be done only at one place. It increases code reusability.
• Improved Performance – Stored Procedures executes the code faster and reduces the network traffic.
  Faster execution – Stored procedures are precompiled i.e; parsed and optimized as soon as they are created and are stored in memory. Thus it executes a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimize your SQL code every time it runs.
  Reduced network traffic – Sending many lines of SQL code from an application over the network to SQL Server, impacts network performance. This is especially true if the SQL code is lengthy and is accessed frequently in the application. Running the code on the SQL Server (as a Stored Procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied ti invoke the SP and the results of any query. Stored Procedures limit data with WHERE clauses, ensuring that your application sends just the necessary data over the network wire.
• Security – Users can execute a stored procedure without needing to execute any of the SQL statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way. You can grant rights to the stored procedure without granting rights to the underlying objects.
  Stored Procedures can be used to make SQL injection attacks more difficult to execute successfully.
  Stored Procedures enable you to perform error-handling at the server.

Syntax of a Stored Procedure

CREATE PROCEDURE procedure_name
[ { @parameter data_type }[ OUTPUT ]
] [ ,...n ]
[ WITH

{ RECOMPILE | ENCRYPTION } ]
AS sql_statement [ ...n ] >

Types of Stored Procedures

There are three types of Stored Procedures:

• User Defined Stored Procedures – User Defined stored procedures are created by normal users like us.
• SQL Server System Stored Procedures – System stored procedures usually begin with sp_ and most of them live in the Master database. They handle much of what is considered to be the nuts-and-bolts of SQL Server administration, and are written in T-SQL just like user defined SPs. Many vital functions of SQL Server rely on these SPs to remain intact!
• Extended Stored Procedures – Extended stored procedures usually begins with xp_ or sp_ and also live in the Master database. They are not written in T-SQL. They are in fact compiled DLLs which open up a world of functionality to the SQL Server environment.

Static Constructors

A static constructor is invoked automatically before the first instance of the class is created or any static members are referenced. It initializes static data members when the class is referenced first time.

Why is a static constructor used?

• A static constructor is used to initialize any static data, or to perform a particular action that needs to be performed once only.
• Typical use of static constructors – When the class is using a log file, the static constructor can be used to write entries to the log file.
• Static constructors are also useful when creating wrapper classes for unmanaged code, when the constructor can call the LoadLibrary method.

Properties of static constructors

• A static constructor does not take access modifiers or have parameters.
• The execution of a static constructor is triggered automatically when the first instance of the class is created or any static members are referenced.
• A static constructor cannot be called directly.
• The user has no control on the execution of static constructor.

Example: In this example, the class Bus has a static constructor and one static member, Drive(). When Drive() is called, the static constructor is invoked to initialize the class.

public class Bus
{
    // Static constructor:
    static Bus()
    {
        System.Console.WriteLine("The static constructor invoked.");
    }

    public static void Drive()
    {
        System.Console.WriteLine("The Drive method invoked.");
    }
}

class TestBus
{
    static void Main()
    {
        Bus.Drive();
    }
}

Output:  

The static constructor invoked.

The Drive method invoked.

What are Special (Specialized) Collections in .Net?

The System.Collections.Specialized namespace contains specialized and strongly-typed collections. Specialized Collections are the customized .Net collection classes which are used for specific purposes.

Four most important special collection classes which are commonly used are as follows:

1. CollectionsUtil – CollectionsUtil class helps to Creates collections that ignore the case in strings. The methods of the CollectionsUtil class generate a case-insensitive instance of the collection using case-insensitive implementations of the hash code provider and the comparer. The resulting instance can be used like any other instances of that class, although it may behave differently.
   Example:

Hashtable ObjHash = CollectionsUtil.CreateCaseInsensitiveHashtable();
ObjHash.Add("mohan","he is a software developer");
string str = (string) ObjHash["MOHAN"];
MessageBox.Show(str);

2. ListDictionary – ListDictionary implements IDictionary by using a singly linked list. It is smaller and faster than a Hashtable if the number of elements is 10 or less. So, it is recommended for collections that typically contain 10 items or less.
   Example:

ListDictionary ObjDic = new ListDictionary();
ObjDic.Add("manoj", "he is a software developer");
ObjDic.Add("ramesh", "he is a software developer");

3. HybridDictionary – HybridDictionary implements IDictionary by using a ListDictionary while the collection is small, and then switching to a Hashtable when the collection gets large. It should be used in cases where the number of elements in a dictionary is unknown.
   If the initial size of the collection is greater than the optimal size for a ListDictionary, the collection is stored in a Hashtable right away to avoid the overhead of copying elements from the ListDictionary to a Hashtable.
   Example:

HybridDictionary ObjHybrid = new HybridDictionary();
ObjHybrid.Add("mohan", "he is a software developer");
ObjDic.Add("Raja", "he is a network administrator");
ObjDic.Add("ramesh", "he is a hardware engineer");

4. StringCollection – StringCollection is a very special collection, which represents a collection of strings. StringCollection accepts a null reference (Nothing in Visual Basic) as a valid value and allows duplicate elements. String comparisons are case-sensitive. Indexes in this collection are zero-based.
   

   Example:
   
   // Creates and initializes a new StringCollection.

   StringCollection myCol = new StringCollection();
   
   // Adds a range of elements from an array to the end of the StringCollection.
   String[] myArr = new String[] { "RED", "orange", "yellow", "RED", "green", "blue", "RED", "indigo", "violet", "RED" };
   myCol.AddRange( myArr );
   
   // Adds one element to the end of the StringCollection and inserts another at index 3.
   myCol.Add( "* white" );
   myCol.Insert( 3, "* gray" );
   
   // Removes one element from the StringCollection.
   myCol.Remove( "yellow" );
   
   // Clears the entire collection.
   myCol.Clear();

Value Types Vs. Reference Types

There are two kinds of types in C#: Value types and Reference types.

Value Types

• Variables of value types directly contain their data in memory allocated on the stack.
• Storage of contents of a variable in a stack increases efficiency, but the limited lifetime of value types makes them in-efficient for sharing data between different classes.
• With value types, the variables each have their own copy of the data, and it is not possible for operations on one to affect the other (except in the case of ref and out parameter variables).
• When a value type variable goes out of scope, the value is discarded from the stack.
• C#’s value types are further divided into simple types, enum types, struct types (even if their members are reference types), and nullable types.
• Value types are derived from System.ValueType.
• When value types are passed By Value, the actual value is copied and there are no references involved. When value types are passed By Reference, the method argument is initialized with the address pointing back to the original value in the calling routine.

Example

class Program

{

static void Main(string[] args)

{

int iMarksOfRam = 65;

int iMarksOfTom = iMarksOfRam;

iMarksOfRam++; //

Console.WriteLine(iMarksOfTom); // it will display 65, iMarksOfRam++ won’t affect on iMarksOfTom

Console.WriteLine(iMarksOfRam); // it will display 66

Console.Read();

}

}

Reference Types

• Variables of reference types store references to their data (known as objects) on the heap.
• Declaration of reference types has a greater overhead, but they are accessible from other classes, which is a clear advantage.
• With reference types, it is possible for two variables to reference the same object and thus possible for operations on one variable to affect the object referenced by the other variable.
• When a reference type variable goes out of scope, the memory is not returned to the heap. Instead, it is reclaimed by the garbage collector when it determines that the variable is no longer needed.
• C#’s reference types are further divided into class types, interface types, array types (even if their elements are value types), and delegate types.
• Reference types are derived from System.Object.
• 90% of the time, when reference types are passed By Value or By Reference, it is functionally the same. However, in horns of dilemma, pass reference types By Value and not By Reference!

Note: When a value type is passed by reference, it simply points back to the original value type. However, when a reference type is passed by value, it creates a copy of the reference (address) inside the method being invoked.

Example

class Program

{

static void Main(string[] args)

{

StringBuilder sReferenceTypeX = new StringBuilder("Hello ");

StringBuilder sReferenceTypeY = sReferenceTypeX;

sReferenceTypeX.Append("World");

Console.WriteLine(sReferenceTypeY); //it will display Hello World

Console.WriteLine(sReferenceTypeX); //it will display Hello World

Console.Read();

}

}