C# provides us with value types and Reference Types. Value Types are stored on the stack and Reference types are stored on the heap. The conversion of value type to reference type is known as boxing and converting reference type back to the value type is known as unboxing.

Value Type

Value types are primitive types that are mapped directly to the FCL. Like Int32 maps to System.Int32,double maps to System.double. All value types are stored on stack and all the value types are derived from System.ValueType. All structures and enumerated types that are derived from System.ValueType are created on stack, hence known as ValueType.

Reference Types

Reference Types are different from value types in such a way that memory is allocated to them from the heap. All the classes are of reference type. C# new operator returns the memory address of the object.

Boxing Example

int Val = 11; object Obj = Val; // boxing 

The first line we created a Value Type Val and assigned a value to Val. The second line , we created an instance of Object Obj and assign the value of Val to Obj. The above operation (object Obj = i ) we saw converting a value of a Value Type into a value of a corresponding Reference Type . These types of operation is called Boxing 

UnBoxing Example

 int ValUnBox = Obj; // unboxing 

 The first line (int ValUnBox = (int) Obj) shows extracts the Value Type from the Object . That is converting a value of a Reference Type into a value of a Value Type. This operation is called UnBoxing

• Use views and stored procedures instead of heavy-duty queries. This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.


• Use table variables instead of temporary tables.Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available from SQL Server 2000 only.


• Try to use UNION ALL statement instead of UNION, whenever possible. The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.


• Try to avoid using the DISTINCT clause, whenever possible. Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.


• Try to avoid using SQL Server cursors, whenever possible. SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated sub-query or derived tables, if you need to perform row-by-row operations.


• If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement. Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times


• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement. This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.


• Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows. This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients


• Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns. This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

         1. Indexes
         2. avoid more number of triggers on the table
         3. unnecessary complicated joins
         4. correct use of Group by clause with the select list
         5. In worst cases De-normalization

Index Optimization tips

• Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.


• Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.


• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.


• If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.


• Create surrogate integer primary key (identity for example) if your table will not have many insert operations.


• Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.


• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.


• You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.


• You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods. sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

If you are working with VS 2010 (any Edition) and cannot open your 2010 solution on VS 2008 then just follow these 3 Steps:

For .sln:

1. Open the solution file in your favorite text editor (ex: notepad++).

2. Find the Following:Microsoft Visual Studio Solution File, Format Version 11.00.# Visual Studio 2010.

Replace with: Microsoft Visual Studio Solution File, Format Version 10.00. (I)# Visual Studio 2008. (II, optinal)

For .csproj/vbproj:

1. Open project file in your favorite text editor (ex: notepad++).

2. Find the Following:.

Replace with: .

(III) Thats it..Now you can open your solution file on VS2008.Reverse of this will enable VS2008 solution to open in VS2010.

Whenever you want to execute a sql statement that shouldn't return a value or a record set the ExecuteNonQuery should be used. So if you want to run an update, delete, or insert statement, you should use the ExecuteNonQuery. ExecuteNonQuery returns the number of rows affected by the statement.

This sounds very nice, but whenever you use the SQL Server 2005 IDE or Visual Studio to create a Stored Procedure it adds a small line that ruins everything. That line is: SET NOCOUNT ON; This line turns on the NOCOUNT feature of SQL Server, which "Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results" and therefore it makes the SP always to return -1 when called from the application (in my case a web app).

In conclusion, remove that line from your SP and you will now get a value indicating the number of rows affected by the statement.

Select the word and hit CTRL+SHIFT+U the SSMS immediately changes the selected word to UPPER CASE. Similar to convert cases to lower case hit CTRL+SHIFT+L.

Displaying Current Date and Time

SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO

CAST and Convert

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression/ColumnName [ , style ] )

Shrinking Truncate Log File

USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO

Restore Database Backup using T-Sql

Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.

----Make Database to single user Mode

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database

RESTORE DATABASE YourDB
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser mode.

If error occurs please execute following command it will convert database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER
GO

DECLARE @UserRoleName Table (UserID varchar(50), UserRole varchar(100))
INSERT INTO @UserRoleName
SELECT 'Vasanth','DotNet Developer' UNION ALL
SELECT 'Vasanth','Sql Developer' UNION ALL
SELECT 'Jayaseelan','DotNet Developer' UNION ALL
SELECT 'Jayaseelan','Appl Deployer' UNION ALL
SELECT 'Vinoth','DotNet Developer'

SELECT * FROM @UserRoleName



SELECT UserID, CAST(UserRole AS VARCHAR(100)) AS RoleName FROM @UserRoleName FOR XML PATH('')



SELECT UserID, STUFF((SELECT CAST(t2.UserRole AS VARCHAR(100)) AS RoleName FROM @UserRoleName t2 WHERE t1.Userid = t2.userID
FOR XML PATH('')),1,0,'') AS RoleDescription
FROM @UserRoleName t1 GROUP BY UserID



SELECT UserID, STUFF((SELECT ', ' + CAST(t2.UserRole as varchar(100)) FROM @UserRoleName t2 WHERE t1.Userid = t2.userID
FOR XML PATH('')),1,1,'') Role
FROM @UserRoleName t1 GROUP BY UserID

STUFF - Delete a specified length of characters and insert another set of characters at a specified starting point. eg.

SELECT STUFF('VASANTH', 3, 2, 'XYZ')

3 - Starting Position from where the Replace Starts
1 - upto Which position it needs to be replaced from '3'
'XYZ' - Replacing characters in that position

Result is : VAXYZNTH

REPLACE - Replace all occurrences of the second given string expression in the first string expression with a third expression. eg.

SELECT REPLACE('VASANTH', 'A', 'KZT')

Result is : VKZTSKZTNTH

1. Intellisence Enhancements (IDE, Lookup will come automatically)
2. Declaration and Initialization in Same Line (Instead of DECLARE and SET in seperate Statement).
3. Compound Operators : (+=, -+, *=, /=....)
4. Multiple Value Insert in single Insert Statement
5. Grouping Sets (Grouping in Different Select stament is merged with one).
6. User-Defined Table Type and Table-Value Parameter (TVP)
7. New Date and Time Data Types
8. New Datatype DateTime2, DateTimeOffSet
9. HirerachyID

Ref : http://www.sqlservercentral.com/articles/SQL+Server+2008/65539/

Declare @Sql nvarchar(max)
Declare @cnt varchar(10)
Declare @TableName as varchar(50)
SET @TableName = 'temp'

SET @Sql = 'Select @Cnt = count(*) From ' + @TableName
--PRINT @Sql
EXEC sp_executeSql @Sql, N'@Cnt varchar(10) output', @Cnt output
Select @Cnt

Compound Operators



Row Constructor (or Table-Valued Constructor) as Derived Table

SELECT *
FROM
(VALUES ('USD', 'U.S. Dollar'),
('EUR', 'Euro'),
('CAD', 'Canadian Dollar'),
('JPY', 'Japanese Yen')) AS [Currency] ( [CurrencyCode], [CurrencyName] )

Multiple Value Inserts Within a Single INSERT Statement

INSERT INTO [dbo].[USState]
VALUES ('AK', 'Alaska'),
('AL', 'Alabama'),
('AR', 'Arkansas'),
('AZ', 'Arizona'),
('CA', 'California')

Table-Valued Parameters

CREATE TYPE [ContactTemplate] AS TABLE (
[Email] VARCHAR(100),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO

CREATE PROCEDURE [dbo].[usp_ProcessContact]
@Contact ContactTemplate READONLY
AS

-- Update First Name and Last Name for Existing Emails
UPDATE A
SET [FirstName] = B.[FirstName], [LastName] = B.[LastName]
FROM [dbo].[Contact] A INNER JOIN @Contact B ON A.[Email] = B.[Email]

-- Add New Email Addresses
INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
SELECT [Email], [FirstName], [LastName]
FROM @Contact A
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Contact] B
WHERE A.[Email] = B.[Email])
GO

MERGE Statement

CREATE PROCEDURE [dbo].[usp_MergeEmployee]
@EmployeeNumber VARCHAR(10),
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Position VARCHAR(50)
AS

MERGE [dbo].[Employee] AS [Target]

USING (SELECT @EmployeeNumber, @FirstName, @LastName, @Position)
AS [Source] ( [EmployeeNumber], [FirstName], [LastName], [Position] )

ON [Target].[EmployeeNumber] = [Source].[EmployeeNumber]

WHEN MATCHED THEN

UPDATE SET [FirstName] = [Source][FirstName],
[LastName] = [Source].[LastName],
[Position] = [Source].[Position]

WHEN NOT MATCHED THEN

INSERT ( [EmployeeNumber], [FirstName], [LastName], [Position] )
VALUES ( [Source].[EmployeeNumber], [Source].[FirstName],
[Source].[LastName], [Source].[Position] );

GO

Ref : http://www.sql-server-helper.com/sql-server-2008/index.aspx

The following references need to be added

Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.ConnectionInfo

ServerConnection oServerCon = new ServerConnection(txtServer.Text, txtUserName.Text, txtPassword.Text);
Server oServer = new Server(oServerCon);

lvDbOptions.Items.Clear();
String Value = String.Empty;

if (ddlOption.SelectedItem.ToString().ToLower() == "tables")
{                        
     foreach (Table oTable in oServer.Databases[txtDatabase.Text].Tables)
     {
          if (oTable.IsSystemObject == false)
          {
               Value = oTable.ToString().Substring(oTable.ToString().IndexOf(".") + 2, (oTable.ToString().Length - 1) - (oTable.ToString().IndexOf(".") + 2));

               lvDbOptions.Items.Add(new ListViewItem(Value));
          }
     }
}
else if (ddlOption.SelectedItem.ToString().ToLower() == "stored procedures")
{
     foreach (StoredProcedure oProc in oServer.Databases[txtDatabase.Text].StoredProcedures)
     {
          if(oProc.IsSystemObject == false)
          {
               Value = oProc.ToString().Substring(oProc.ToString().IndexOf(".") + 2, (oProc.ToString().Length - 1) - (oProc.ToString().IndexOf(".") + 2));

               lvDbOptions.Items.Add(new ListViewItem(Value));
          }
     }
}
else if (ddlOption.SelectedItem.ToString().ToLower() == "functions")
{
     foreach (UserDefinedFunction oFunc in oServer.Databases[txtDatabase.Text].UserDefinedFunctions)
     {
          if (oFunc.IsSystemObject == false) 
          {
               Value = oFunc.ToString().Substring(oFunc.ToString().IndexOf(".") + 2, (oFunc.ToString().Length - 1) - (oFunc.ToString().IndexOf(".") + 2));

               lvDbOptions.Items.Add(new ListViewItem(Value));
          }
     }
}

The following references need to be added

Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.ConnectionInfo

ServerConnection oCon = new ServerConnection(server, DBUserID, DBPassword);

Server oServer = new Server(oCon);
Restore oRestore = new Restore();

//sFileName - File Physical Location

if (File.Exists(sFileName))
{           
    oRestore.Database = DbName;
    oRestore.Action = RestoreActionType.Database;
    oRestore.Devices.AddDevice(sFileName, DeviceType.File);
    oRestore.NoRecovery = false;
    oRestore.ReplaceDatabase = true;
    oRestore.PercentCompleteNotification = 10;
    oRestore.PercentComplete +=
    new PercentCompleteEventHandler(bkp_percentComplete);
    oRestore.SqlRestore(oServer);
}

protected void bkp_percentComplete(object sender, PercentCompleteEventArgs e)
{
     lblStatus.Text = e.Percent.ToString() + "% restored";
}

The following references need to be added

Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.ConnectionInfo

ServerConnection oCon = new ServerConnection(Server, DBUserID, DBPassword); 

Server oServer = new Server(oCon);
Backup oBackUp = new Backup();

sFileName = BackupLocation + "\\" + DBName 
+ "_" + System.DateTime.Now.ToString("dd_MM_yyyy hh_mm") + ".bak";
                
BackupDeviceItem oBackupDevice = 
new BackupDeviceItem(sFileName, DeviceType.File);

oBackUp.Devices.Add(oBackupDevice);

//oBackUp.Devices.AddDevice(@"C:\DBbackup.bak", DeviceType.File);
//oBackUp.Devices.AddDevice(sFileName, DeviceType.File);

oBackUp.Database = DBName;
oBackUp.Action = BackupActionType.Database;
oBackUp.NoRecovery = false;
oBackUp.Initialize = true;        
oBackUp.PercentCompleteNotification = 10;

oBackUp.PercentComplete += new PercentCompleteEventHandler(bkp_percentComplete);
oBackUp.SqlBackup(oServer);        

protected void bkp_percentComplete(object sender, PercentCompleteEventArgs e)
{
    lblStatus.Text = e.Percent.ToString() + "% backed up";        
}

TRUNCATE TABLE statement was used to remove all data, the IDENTITY column resets the numbering to start with the original value. DELETE statement was used to remove all data, the IDENTITY values are still incremented according to the last value used.

A TRUNCATE TABLE statement tends to use fewer locks than a DELETE statement. When a TRUNCATE TABLE statement is used, SQL Server applies table and page locks but not row locks, as a DELETE statement does.

A TRUNCATE TABLE statement uses less transaction log space than a DELETE statement. When a TRUNCATE TABLE statement is used, SQL Server de-allocates the data pages and records only the de-allocations in the transaction log. When a DELETE statement is used, SQL Server makes an entry into the transaction log for each deleted row.

A TRUNCATE TABLE statement leaves no pages in a table, whereas a DELETE statement can leave empty pages.

While a TRUNCATE TABLE statement is more efficient than a DELETE statement, there are restrictions that govern the use of TRUNCATE TABLE. For example, you should not use a TRUNCATE TABLE statement against a table under the following conditions:

An indexed view specifies the table in its definition.

Transaction or merge replication is used to publish the table.

The table is referenced by a foreign key constraint (unless it is a self-referencing foreign key).

The table's IDENTITY values must be preserved and consistently incremented.

Only specific rows are to be deleted from the table, and not the entire dataset.

Regardless of these differences, both the TRUNCATE TABLE and DELETE statements remove only data and do not impact the table structure. Indexes, constraints, and columns are left untouched.

The Date object has methods for manipulating dates and times. JavaScript stores dates as the number of milliseconds since January 1, 1970. The sample below shows the different methods of creating date objects, all of which involve passing arguments to the Date() constructor.

A few things to note:

To create a Date object containing the current date and time, the Date() constructor takes no arguments.

When passing the date as a string to the Date() constructor, the time portion is optional. If it is not included, it defaults to 00:00:00. Also, other date formats are acceptable (e.g, "11/06/2009" and "11-06-2009").

When passing date parts to the Date() constructor, dd, hh, mm, ss, and ms are all optional. The default of each is 0.

Months are numbered from 0 (January) to 11 (December).

Some common date methods are shown below. In all the examples, the variable RIGHT_NOW contains "Fri Nov 06 00:23:54:650 EDT 2009".

The Math object is a built-in static object. The Math object's properties and methods are accessed directly (e.g, Math.PI) and are used for performing complex math operations. Some common math properties and methods are shown below.

Relational Databases :

A relational database at its simplest is a set of tables used for storing data. Each table has a unique name and may relate to one or more other tables in the database through common values.

Tables :

A table in a database is a collection of rows and columns. Tables are also known as entities or relations.

Rows :

A row contains data pertaining to a single item or record in a table. Rows are also known as records or tuples.

Columns :

A column contains data representing a specific characteristic of the records in the table. Columns are also known as fields or attributes.

Relationships :

A relationship is a link between two tables (i.e, relations). Relationships make it possible to find data in one table that pertains to a specific record in another table.

Datatypes :

Each of a table's columns has a defined datatype that specifies the type of data that can exist in that column. For example, the FirstName column might be defined as varchar(20), indicating that it can contain a string of up to 20 characters. Unfortunately, datatypes vary widely between databases.

Primary Keys :

Most tables have a column or group of columns that can be used to identify records. For example, an Employees table might have a column called EmployeeID that is unique for every row. This makes it easy to keep track of a record over time and to associate a record with records in other tables.

Foreign Keys :

Foreign key columns are columns that link to primary key columns in other tables, thereby creating a relationship. For example, the Customers table might have a foreign key column called SalesRep that links to EmployeeID, the primary key in the Employees table.

Relational Database Management System :

A Relational Database Management System (RDBMS), commonly (but incorrectly) called a database, is software for creating, manipulating, and administering a database. For simplicity, we will often refer to RDBMSs as databases.

Popular Databases :

ORACLE
SQL SERVER
DB2

Popular Open Source Databases :

MySQL
PostgreSQL

SQL Statements :

Database Manipulation Language (DML)

DML statements are used to work with data in an existing database. The most common DML statements are:

SELECT
INSERT
UPDATE
DELETE

Database Definition Language (DDL)

DDL statements are used to structure objects in a database. The most common DDL statements are:
CREATE
ALTER
DROP

Database Control Language (DCL)

DCL statements are used for database administration. The most common DCL statements are:

GRANT
DENY (SQL Server Only)
REVOKE

A regular expression is a pattern that specifies a list of characters. In this section, we will look at how those characters are specified :

Start and End ( ^ $ )

A caret (^) at the beginning of a regular expression indicates that the string being searched must start with this pattern.

The pattern ^foo can be found in "food", but not in "barfood".

A dollar sign ($) at the end of a regular expression indicates that the string being searched must end with this pattern.

The pattern foo$ can be found in "curfoo", but not in "food".

Number of Occurrences ( ? + * {} )

The following symbols affect the number of occurrences of the preceding character: ?, +, *, and {}.

A questionmark (?) indicates that the preceding character should appear zero or one times in the pattern.

The pattern foo? can be found in "food" and "fod", but not "faod".

A plus sign (+) indicates that the preceding character should appear one or more times in the pattern.

The pattern fo+ can be found in "fod", "food" and "foood", but not "fd".

A asterisk (*) indicates that the preceding character should appear zero or more times in the pattern.

The pattern fo*d can be found in "fd", "fod" and "food".

Curly brackets with one parameter ( {n} ) indicate that the preceding character should appear exactly n times in the pattern.

The pattern fo{3}d can be found in "foood" , but not "food" or "fooood".

Curly brackets with two parameters ( {n1,n2} ) indicate that the preceding character should appear between n1 and n2 times in the pattern.

The pattern fo{2,4}d can be found in "food","foood" and "fooood", but not "fod" or "foooood".

Curly brackets with one parameter and an empty second paramenter ( {n,} ) indicate that the preceding character should appear at least n times in the pattern.

The pattern fo{2,}d can be found in "food" and "foooood", but not "fod".

Common Characters ( . \d \D \w \W \s \S )

A period ( . ) represents any character except a newline.

The pattern fo.d can be found in "food", "foad", "fo9d", and "fo*d".

Backslash-d ( \d ) represents any digit. It is the equivalent of [0-9].

The pattern fo\dd can be found in "fo1d", "fo4d" and "fo0d", but not in "food" or "fodd".

Backslash-D ( \D ) represents any character except a digit. It is the equivalent of [^0-9].

The pattern fo\Dd can be found in "food" and "foad", but not in "fo4d".

Backslash-w ( \w ) represents any word character (letters, digits, and the underscore (_) ).

The pattern fo\wd can be found in "food", "fo_d" and "fo4d", but not in "fo*d".

Backslash-W ( \W ) represents any character except a word character.

The pattern fo\Wd can be found in "fo*d", "fo@d" and "fo.d", but not in "food".

Backslash-s ( \s) represents any whitespace character (e.g, space, tab, newline, etc.).

The pattern fo\sd can be found in "fo d", but not in "food".

Backslash-S ( \S ) represents any character except a whitespace character.

The pattern fo\Sd can be found in "fo*d", "food" and "fo4d", but not in "fo d".

Grouping ( [] )

Square brackets ( [] ) are used to group options.

The pattern f[aeiou]d can be found in "fad" and "fed", but not in "food", "faed" or "fd".

The pattern f[aeiou]{2}d can be found in "faed" and "feod", but not in "fod", "fed" or "fd".

Negation ( ^ )

When used after the first character of the regular expression, the caret ( ^ ) is used for negation.

The pattern f[^aeiou]d can be found in "fqd" and "f4d", but not in "fad" or "fed".

Subpatterns ( () )

Parentheses ( () ) are used to capture subpatterns.

The pattern f(oo)?d can be found in "food" and "fd", but not in "fod".

Alternatives ( )

The pipe ( ) is used to create optional patterns.

The pattern foo$^bar can be found in "foo" and "bar", but not "foobar".

Escape Character ( \ )

The backslash ( \ ) is used to escape special characters.

The pattern fo\.d can be found in "fo.d", but not in "food" or "fo4d".

Backreferences

Backreferences are special wildcards that refer back to a subpattern within a pattern. They can be used to make sure that two subpatterns match. The first subpattern in a pattern is referenced as \1, the second is referenced as \2, and so on.

A more practical example has to do matching the delimiter in social security numbers. Examine the following regular expression.

^\d{3}([\- ]?)\d{2}([\- ]?)\d{4}$

Within the caret (^) and dollar sign ($), which are used to specify the beginning and end of the pattern, there are three sequences of digits, optionally separated by a hyphen or a space. This pattern will be matched in all of following strings (and more).

•123-45-6789
•123 45 6789
•123456789
•123-45 6789
•123 45-6789
•123-456789

Trim function, trims all leading and trailing spaces:

String.prototype.trim = function(){return (this.replace(/^[\s\xA0]+/, "").replace(/[\s\xA0]+$/, ""))}

StartsWith to check if a string starts with a particular character sequecnce:

String.prototype.startsWith = function(str) {return (this.match("^"+str)==str)}

EndsWith to check if a string ends with a particular character sequecnce:

String.prototype.endsWith = function(str) {return (this.match(str+"$")==str)}

All these functions once loaded will behave as built-in JavaScript functions. Here are few examples:

var myStr = “ Earth is a beautiful planet ”;
var myStr2 = myStr.trim(); //==“Earth is a beautiful planet”;
if (myStr2.startsWith(“Earth”)) // returns TRUE
if (myStr2.endsWith(“planet”)) // returns TRUE
if (myStr.startsWith(“Earth”)) // returns FALSE due to the leading spaces…
if (myStr.endsWith(“planet”)) // returns FALSE due to trailing spaces…

Temp Tables vs. Table Variables

1. SQL Server does not place locks on table variables when the table variables are used.
2. Temp tables allow for multiple indexes to be created
3. Table variables allow a single index the Primary Key to be created when the table variable is declared only.
4. Temp tables can be created locally (#TableName) or globally (##TableName)
5. Table variables are destroyed as the batch is completed.
6. Temp tables can be used throughout multiple batches.
7. Temp tables can be used to hold the output of a stored procedure (temp tables will get this functionality in SQL Server 2008).

Table variables and Temp Tables vs. CTEs

1. CTEs are used after the command which creates them.
2. CTEs can be recursive within a single command (be careful because they can cause an infinite loop).
3. Table variables and Temp Tables can be used throughout the batch.
4. The command before the CTE must end with a semi-colon (;).
5. As Temp tables and table variables are tables you can insert, update and delete the data within the table.
6. CTEs can not have any indexes created on them, source tables much have indexes created on them.