The FreeTextTable function in SQL Server is very useful when you need to search a column for words, phrases, etc. FreeTextTable will return a table that you can join on.  It also returns the Rank of the matches so you can order the most relevant matches at the top.

In the example below I am searching the Keywords column of the MyTable table using the @Keywords passed into the stored procedure.  I am then ordering them by Rank so the highest matches show up first.

CREATE PROCEDURE [dbo].[FreeTextSample]
    @Keywords [varchar](1000) = ''
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    SELECT myTable.MyPrimaryKeyID,
        myTable.Title,
        myTable.Description
    FROM MyTable myTable(nolock)
    INNER JOIN FreeTextTable(dbo.MyTable, Keywords, @Keywords) AS searchTable
        ON myTable.MyPrimaryKeyID = searchTable.[KEY]
    ORDER By searchTable.[RANK] DESC

END
GO    

One thing to note is that LINQ does not contain an equivalent keyword for FreeTextTable.  So what I did was to create a new method on my DataContext that returns the results of the stored procedure.  See my post here for further details on how to do this.

I tend to look this up quite a bit, so here it is for quick reference.  This converts a datetime column into a MM/DD/YYYY format.

UPDATE MyTable
SET DateColumn2 = CONVERT(varchar(10), DateColumn1, 101)

When creating an advanced search you often need to dynamically create the Where statments in your SQL.  Here is an easy way to do this in LINQ.  In the example below orderID, lastName, firstName, shipped are all values retrieved from controls on the page.

var query = from orders in dataContext.MyOrdersTable
            select orders;
            
if (orderID.HasValue)
{
    query = query.Where(order => order.OrderID == orderID);
}
if (!string.IsNullOrEmpty(lastName))
{
    query = query.Where(order => order.LastName == lastName);
}
if (!string.IsNullOrEmpty(firstName))
{
    query = query.Where(order => order.FirstName == firstName);
}
if (shipped.HasValue)
{
    query = query.Where(order => order.Shipped == shipped);
}

myGridView.DataSouce = query;
myGridView.DataBind();

This trigger will write to an archive table when a row is updated or deleted.  Very useful when you want to know who did what and when.

USE [DatabaseName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trigTableName_Archive] 
   ON  [dbo].[TableName]
   FOR UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO TableName_ARCHIVE
        (Column1,
        Column2,
        Column3)
    SELECT
        Column1,
        Column2,
        Column3
    FROM deleted

END

I always seem to be looking this up.  So here it is:

USE DatabaseName
GO

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName
UNIQUE NONCLUSTERED (ColumnName)
GO

So the other day while attempting to drag a stored procedure onto the LINQ Designer in Visual Studio I received the following error:

The stored procedure was rather complex including a call to FREETEXT and for some reason the LINQ Designer would not accept it, so I manually mapped the stored procedure for the DataContext.  For future reference, here is some sample code:

public partial class MyDataContext
{
    [Function(Name = "dbo.MyStoredProc")]
    public ISingleResult<MyClass> MyProcName(
        [Parameter(Name = "Parameter1")] int? parameter1,
        [Parameter(Name = "Parameter2")] ProductCategory? parameter2,
        [Parameter(Name = "Parameter3")] bool? parameter3)
    {
        IExecuteResult result = this.ExecuteMethodCall(this,
            ((MethodInfo)(MethodInfo.GetCurrentMethod())),
            parameter1, parameter2, parameter3);

        return ((ISingleResult<MyClass>)(result.ReturnValue));
    }
}

Then you can call the stored procedure in code like so:

var results = myDC.MyProcName(parameter1, parameter2, parameter3);