SQL SERVER DATA TYPES

What's the difference between CHAR and VARCHAR data types and when do I use them?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters.  The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length.  If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size.  On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

What's the difference between NCHAR and NVARCHAR data types and when do I use them?

NCHAR and NVARCHAR data types are both Unicode character data types with a maximum length of 4,000 characters.  The main difference between these 2 data types is that an NCHAR data type is fixed-length while an NVARCHAR is variable-length.  If the number of characters entered in an NCHAR data type column is less than the specified column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for NCHAR is two times n bytes while for NVARCHAR is two times the number of characters entered (in bytes).

You should use NCHAR data type when the data values in a column are expected to be consistently close to the same size.  On the other hand, you should use NVARCHAR when the data values in a column are expected to vary considerably in size.

What's the difference between CHAR and NCHAR data types and when do I use them?

CHAR and NCHAR data types are both character data types that are fixed-length.  Below is the summary of the differences between these 2 data types:

	CHAR(n)	NCHAR(n)
Character Data Type	Non-Unicode Data	Unicode Data
Maximum length	8,000	4,000
Character Size	1 byte	2 byte
Storage Size	n bytes	2 x n bytes

You would use NCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters

What's the difference between VARCHAR and NVARCHAR data types and when do I use them?

VARCHAR and NVARCHAR data types are both character data types that are variable-length.  Below is the summary of the differences between these 2 data types:

	VARCHAR(n)	NVARCHAR(n)
Character Data Type	Non-Unicode Data	Unicode Data
Maximum length	8,000	4,000
Character Size	1 byte	2 byte
Storage Size	actual length (in bytes)	2 x actual length (in bytes)

You would use NVARCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.

What's the difference between TINYINT, SMALLINT, INT and BIGINT data types and when do I use them?

TINYINT, SMALLINT, INT and BIGINT are all the same in the sense that they are all exact number data types that use integer data.  The difference between these data types are in the minimum and maximum values that each can contain as well as the storage size required by each data type, as shown in the following table:

Data Type	Min Value	Max Value	Storage Size
tinyint	0	255	1 byte
smallint	-2^15 (-32,768)	2^15 - 1 (32,767)	2 byte
int	-2^31 (-2,147,483,648)	2^31 - 1 (2,147,483,647)	4 byte
bigint	-2^63 (-9,223,372,036,854,775,808)	2^63 - 1 (9,223,372,036,854,775,807)	8 byte

Choosing which of these data types to use depends on the value you want to store for the column or variable.  The rule of thumb is to always use the data type that will require the least storage size.  Don't always use INT as your data type for whole numbers if you don't need to.  If you simply need to store a value between 0 and 255 then you should define your column as TINYINT.

What's the difference between NUMERIC and DECIMAL data types and when do I use them?

There is no difference between NUMERIC and DECIMAL data types.  They are synonymous to each other and either one can be used.  DECIMAL/NUMERIC data types are numeric data types with fixed precision and scale.

In declaring a DECIMAL or NUMERIC data type, p, which is the precision, specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.  The precision must be a value from 1 through the maximum precision of 38.  The s is the scale and it specifies the maximum number of decimal digits that can be stored to the right of the decimal point.  Scale, which defaults to 0 if not specified, must be a value from 0 to the precision value.

The following table specifies the storage size required based on the precision specified for the NUMERIC or DECIMAL data type:

Precision	Storage Size
1 - 9	5 bytes
10 - 19	9 bytes
20 - 28	13 bytes
29 - 38	17 bytes

What's the difference between FLOAT and REAL data types and when do I use them?

FLOAT and REAL data types are both approximate number data types for use with floating point numeric data.  Floating point data is approximate; not all values in the data type range can be precisely represented.  The differences between these 2 data types are in the minimum and maximum values each can hold as well as the storage size required, as specified in the following table:

Data Type	n	Min Value	Max Value	Precision	Storage Size
float [(n)]	1-24	-1.79E + 308	1.79E + 308	7 digits	4 bytes
	25-53	-1.79E + 308	1.79E + 308	15 digits	8 bytes
real		-3.40E + 38	3.40E + 38	7 digits	4 bytes

For FLOAT data type, the n is the number of bits used to store the mantissa in scientific notation and thus dictates the precision and storage size and it must be a value from 1 through 53.  If not specified, this defaults to 53.  In SQL Server, the synonym for REAL data type is FLOAT(24).  If your data requires only a maximum of 7 digits precision, you can either use the REAL data type or FLOAT data type with 24 as the parameter (FLOAT(24)).

What's the difference between SMALLDATETIME and DATETIME data types and when do I use them?

A datetime data type is date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).  Values are rounded to increments of .000, .003, or .007 seconds.

On the other hand, a smalldatetime data type is a date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute.  smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers.  The first 4 bytes store the number of days before or after the base date, January 1, 1900.  The base date is the system reference date.  Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime.  SQL Server stores smalldatetime values as two 2-byte integers.  The first 2 bytes store the number of days after January 1, 1900.  The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Data Type	Min Value	Max Value	Accuracy	Storage Size
smalldatetime	January 1, 1900	June 6, 2079	up to a minute	4 bytes
datetime	January 1, 1750	December 31, 9999	one three-hundredth of a second	8 bytes

smalldatetime is usually used when you don't need to store the time of the day such as in cases of effectivity dates and expiration dates.  datetime  is used if the time of the day is needed and up to the second accuracy is required.

What's the difference between SMALLMONEY and MONEY data types and when do I use them?

MONEY and SMALLMONEY are both monetary data types for representing monetary or currency values.  The differences between these 2 data types are in the minimum and maximum values each can hold as well as in the storage size required by each data type, as shown in the following table:

Data Type	Min Value	Max Value	Storage Size
smallmoney	-214,748.3648	214,748.3648	4 bytes
money	-2^63 (-922,337,203,685,477.5808)	2^63 (-922,337,203,685,477.5808)	8 bytes

How do I store a boolean value in SQL Server?

In SQL Server, there's no boolean data type.  The nearest data type that can be used in place of boolean data is the BIT data type, which is an integer data type that can accept a value of 1, 0 or NULL value only.

WCF OperationContractAttribute with example

The OperationContractAttribute, also defined in the System.ServiceModel namespace, can be applied only to methods. It should declare to the method which belongs to a Service contract. Operation contract can be declared with below named parameters that provide control over the service description and message formats.

Name Specifies a different name for the operation instead of using the default, which is the method name.Action Controls the action header for messages to this operation.

ReplyAction Controls the action header for response messages from this operation.

IsOneWay Indicates whether the operation is one-way and has no reply. When operations are one-way, ReplyAction is not supported.

ProtectionLevel Enables the Service contract to specify constraints on how messages to
all operations in the contract are protected on the wire, that is, whether they are signed and encrypted.

IsInitiating Indicates whether invoking the operation initiates a new session between the caller and the service.

IsTerminating Indicates whether invoking the operation terminates an existing session between the caller and the service

Here is Sample WCF Class for reference.

// C#
[ServiceContract(Namespace = "")]
public class CrudContract
{
    [OperationContract(IsOneWay = true, Action = "urn:crud:insert")]
    void ProcessInsertMessage(Message message);
    [OperationContract(IsOneWay = true, Action = "urn:crud:update")]
    void ProcessUpdateMessage(Message message);
    [OperationContract(IsOneWay = true, Action = "urn:crud:delete")]
    void ProcessDeleteMessage(Message message);
    // The catch-all operation:
    [OperationContract(IsOneWay = true, Action = "*")]
    void ProcessUnrecognizedMessage(Message message);
}

Hope this is useful

“CleanRiaClientFilesTask" task failed unexpectedly

Recently few of our developers and me got this error while building my Silverlight application. I got this error while checking in the code and merge my code changes.

Error Details.

Error    38    The "CleanRiaClientFilesTask" task failed unexpectedly.
System.ArgumentException: Illegal characters in path.

To resolve this we need to do two simple steps.

Step 1: Go to this location in your system or your Framework install directory, for me its C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files. Clean all the temporary files under this location and leave the root folder empty.

Step 2: Go to your project location and Delete all the files in \obj folder. Basically all the files are stored in the \obj folder created by ria services.

Now clean the project and try building again. It worked for me. Hope this will help you. Good luck

Iterating through Generic List in C#

Recently I need to loop through a Generic list and bind the result to a Combobox. Here is how you can do this in C#

List<CCDSDateResult> list = new List<CCDSDateResult>();
for (int i = 0; i < e.Result.Count; i++)
{
    if (e.Result.ToList()[i].ccds_date.ToString().Length > 0)
    {
        // Write your logic..to add item in the list
        list.Add(new CCDSDateResult(DateTime.Parse(e.Result.ToList()[i].ccds_date.ToString())));
    }
}
// adding a empty record to the list
list.Insert(0, new CCDSDateResult());
this.OrderComboBox.ItemsSource = list.ToList();

Hope this is useful.

Exporting to Excel from Telerik Gridview in Silverlight

Here is the sample code for exporting Telerik Gridview in silverlight.

private void btnExportOrders_Click(object sender, RoutedEventArgs e)
{
   // Checking whether grid has rows
    if (OrderGrid.Items.Count > 0)
    {
          string extension = "xls";
          string selectedItem = "Excel";
          ExportFormat format = ExportFormat.ExcelML;
          SaveFileDialog dialog = new SaveFileDialog();
          dialog.DefaultExt = extension;
          dialog.Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, selectedItem);
          dialog.FilterIndex = 1;
          if (dialog.ShowDialog() == true)
          {
              using (Stream stream = dialog.OpenFile())
              {
                GridViewExportOptions options = new GridViewExportOptions();
                options.Format = format;
                options.ShowColumnHeaders = true;
                options.Encoding = Encoding.UTF8;
                options.ShowColumnHeaders = true;
               // name of the grid which you want export      
                OrderGrid.Export(stream, options);
               }
           }
     }
     else
      {
         // User friendly Message
          MessageBox.Show("There are no records to export.", "Export Warning!", MessageBoxButton.OK);
      }
 }

This export code will also support different formats like CSV,Text and HTML. All you need to change is the enum type for ExportFormat to above formats and the extension. Its easy and simple. Good luck