What is the maximum allowable size of a row in SQL SERVER?

  • If a table does not contain any sparse column, then the maximum allowable row size is 8060 bytes.
  • However, if a table contains sparse columns, then the maximum allowable row size is 8018 bytes.
No comments:

How is a column changed from sparse to nonsparse or nonsparse to sparse

  • When a column changed from sparse to nonsparse or nonsparse to sparse, the storage format of the column is changed. This is accomplished by the SQL SERVER Database Engine using the following procedure:
  • The SQL SERVER Database Engine adds a new column to the table in the new storage size and format.
  • For each row in the table, the SQL SERVER Database Engine updates and copies the value stored in the old column to the new column.
  • The SQL SERVER Database Engine then removes the old column from the table schema.
  • It then rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.
No comments:

List down the limitations for using SPARSE Columns

  • A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
  • A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.
  • A sparse column cannot have a default value or bound to a rule.
  • A computed column cannot be marked as SPARSE although it can contain a sparse column.
  • A sparse column cannot be part of a clustered index or a unique primary key index.
  • A sparse column cannot be used as a partition key of a clustered index or heap, but it can be used as the partition key of a nonclustered index.
  • A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.
  • Sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.
  • When a non-sparse column is changed to a sparse column, the sparse column will consume more space for non-null values. When a row is close to the maximum row size limit, the operation can fail.
  • If a table contains a sparse column, the maximum size of a row in a table will be decreased from 8060 bytes to 8012 bytes.
No comments:

What are the datatypes which cannot be specified as SPARSE?

Given below are the data types which cannot be specifies as SPARSE:
  • geography
  • geometry
  • image
  • ntext
  • text
  • timestamp
  • user-defined data types
No comments:

Explain the COLUMNS_UPDATED function and its relation to Sparse Column

The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. The bits that are returned by the COLUMNS_UPDATED function are as follows:
  • When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.
  • When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.
  • For insert operations, all bits are set to 1.
No comments:

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.
No comments:

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.

No comments:

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.
No comments:

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.
No comments:

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);
  1. 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");
  1. 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");
  1. 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();
2 comments:

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();
}
}
No comments:

How is a C# program compiled to a processor specific native code?

When C# programs are compiled, they are physically packaged into assemblies. Assemblies are self-describing unit of functionality containing both code and metadata. Assemblies typically have the file extension .exe or .dll, depending on whether they implement applications or libraries.

Assemblies contain executable code in the form of Intermediate Language (IL) instructions, and symbolic information in the form of metadata. Before it is executed, the IL code in an assembly is automatically converted to processor-specific code by the Just-In-Time (JIT) compiler of .NET Common Language Runtime.
No comments:

BETWEEN logical operator (SQL) is Inclusive

SQL logical operator BETWEEN is inclusive. It uses a closed interval ([a,b]).

Syntax:

 <#test_expression#> [ NOT ] BETWEEN <#begin_expression#> AND <#end_expression#>

Result Type: Boolean

BETWEEN returns TRUE if the value of the <#test_expression#> is greater than or equal to the value of <#begin_expression#> and less than or equal to the value of <#end_expression#>.

NOT BETWEEN returns TRUE if the value of <#test_expression#> is less than the value of <#begin_expression#> or greater than the value of <#end_expression#>.

Note: You can use the greater than (>) and less than operators (<) to specify an exclusive range. If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.

Example: The example below retrieves a list of Opportunities which has been implemented last 60 days:

SELECT a.Id, a.Name, a.StageName
FROM dbo.sf_opportunity a
WHERE a.Consultation_Date_Time_test__c BETWEEN DATEADD(DAY, -60, GETDATE()) AND GETDATE() AND a.IsDeleted = 0
No comments:

operator keyword (C# Conversion Keywords)

The operator keyword is used to overload a built-in operator or to provide a user-defined conversion in a class or struct declaration.

Example: The following example overloads the + and * operators to perform fractional addition and multiplication, and also provides a conversion operator that converts a Fraction type to a double type.

class Fraction
{
int iNum, iDen;
public Fraction(int iNum, int iDen)
{
this.iNum = iNum;
this.iDen = iDen;
}

// overload operator +
public static Fraction operator +(Fraction x, Fraction y)
{
return new Fraction(x.iNum * y.iDen + y.iNum * x.iDen, x.iDen * y.iDen);
}

// overload operator *
public static Fraction operator *(Fraction x, Fraction y)
{
return new Fraction(x.iNum * y.iNum, x.iDen * y.iDen);
}

// user-defined conversion from Fraction to double
public static implicit operator double(Fraction oFraction)
{
return (double)oFraction.iNum / oFraction.iDen;
}
}

class Test
{
static void Main()
{
Fraction x = new Fraction(1, 2);
Fraction y = new Fraction(3, 7);
Fraction z = new Fraction(2, 3);
Console.WriteLine((double)(x * y + z));
}
}

/*Output: 0.880952380952381*/
1 comment:

explicit keyword (C# Conversion Keywords)

The explicit keyword declares a user-defined type conversion operator that must be invoked with a cast. The conversion operator converts from a source type to a target type. The source type provides the conversion operator. Unlike implicit conversion, explicit conversion operators must be invoked by means of a cast.

Omitting the cast results in compile-time error CS0266.

Example: The following example provides a Fahrenheit and a Celsius class, each of which provides an explicit conversion operator to the other class.

class Celsius
{
public Celsius(float fTemp)
{
fDegrees = fTemp;
}
// Must be defined inside a class called Celsius:
public static explicit operator Fahrenheit(Celsius c)
{
return new Fahrenheit((9.0f / 5.0f) * c.fDegrees + 32);
}
public float Degrees
{
get { return fDegrees; }
}
private float fDegrees;
}

class Fahrenheit
{
public Fahrenheit(float fTemp)
{
fDegrees = fTemp;
}
// Must be defined inside a class called Fahrenheit:
public static explicit operator Celsius(Fahrenheit fahr)
{
return new Celsius((5.0f / 9.0f) * (fahr.fDegrees - 32));
}
public float Degrees
{
get { return fDegrees; }
}
private float fDegrees;
}

class MainClass
{
static void Main()
{
Fahrenheit oFahrenheit = new Fahrenheit(100.0f);
Console.Write("{0} Fahrenheit", oFahrenheit.Degrees);
Celsius oCelsius = (Celsius)oFahrenheit; // Calls explicit conversion operator in Fahrenheit class

Console.Write(" = {0} Celsius", oCelsius.Degrees);
Fahrenheit oFahrenheit2 = (Fahrenheit)oCelsius; // Calls explicit conversion operator in Celsius class
Console.WriteLine(" = {0} Fahrenheit", oFahrenheit2.Degrees);
}
}
No comments:

implicit keyword (C# Conversion Keywords)

An implicit keyword is used to declare an implicit user-defined type conversion operator. It is used to enable implicit (automatic) conversions between a user-defined type and another type, if the conversion is guaranteed not to cause a loss of data.

Implicit conversions can improve source code readability by eliminating unnecessary casts. Care must be taken to prevent unexpected results because implicit conversions do not require programmers to explicitly cast from one type to the other. In general, implicit conversion operators should never throw exceptions and never lose information so that they can be used safely without the programmer's awareness. If a conversion operator cannot meet those criteria, it should be marked explicit.

Example:

class Digit
{
public Digit(double dValue) { dVal = dValue; }
public double dVal;

// User-defined conversion from Digit to double
public static implicit operator double(Digit oDigit)
{
return oDigit.dVal;
}

// User-defined conversion from double to Digit
public static implicit operator Digit(double dDoubleVal)
{
return new Digit(dDoubleVal);
}
}

class Program
{
static void Main(string[] args)
{
Digit oDigit = new Digit(7);

double dNum = oDigit; //This call invokes the implicit "double" operator

Digit oDigit2 = 12; //This call invokes the implicit "Digit" operator

Console.WriteLine("dNum = {0} oDigit2 = {1}", dNum, oDigit2.dVal);
Console.ReadLine();
}
}

No comments:
Subscribe to: Posts (Atom)