Common Table Expressions

Introduced in SQL 2005 was the Common Table Expression (CTE). These can act like subqueries or views within a SQL statement.

Good definition on 4GuysFromRolla and MSDN.

To use them you use the 'WITH' keyword, for example if you were using the following Subquery:

SELECT
 *
FROM
    (
    SELECT
        p.Forename
    ,   p.Surname
    ,   COUNT(m.MessageId) AS MessageCount
    FROM
        [Pupil] p
        INNER JOIN [Message] m ON (p.PupilId = m.PupilId)
    GROUP BY
       p.Forename
   ,   p.Surname
   ) AS PupilsWithMessages

to get all pupils with a message you could now use a CTE,

WITH PupilsWithMessages (Forename, Surname, MessageCount) AS (
    SELECT
        p.Forename
    ,   p.Surname
    ,   COUNT(m.MessageId) AS MessageCount
    FROM
        [Pupil] p
        INNER JOIN [Message] m ON (p.PupilId = m.PupilId)
    GROUP BY
        p.Forename
    ,   p.Surname
)

SELECT
    *
FROM
    PupilsWithMessages

This is a simple example but it looks a lot cleaner than subqueries and you can use them in the same way as you would a view. Better examples on the above links.

Recursive CTE's

You can also use them for recursing, a simple example I came up with was to loop through a comma delimited list of weekdays and return a seperate row for each one. Whereas previously you would need to use a Cursor you can now use a CTE.

DECLARE @String varchar(100)
SET @String = 'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,';

WITH List (Val, Rest, HierarchyLevel) AS (
    SELECT
        SUBSTRING(@String, 1, CHARINDEX(',', @String)-1) AS Val
    ,   SUBSTRING(@String, CHARINDEX(',', @String)+1,200) AS Rest
    ,   1 AS HierarchyLevel
    UNION ALL
    SELECT
        SUBSTRING(l.Rest, 1, CHARINDEX(',',l.Rest)-1) AS Val
    ,   SUBSTRING(l.Rest, CHARINDEX(',', l.Rest)+1,200) AS Rest
    ,   l.HierarchyLevel + 1 AS HierarchyLevel
    FROM
       List l
    WHERE
       CHARINDEX(',', l.Rest) <> 0
       --AND HierarchyLevel < 5 /*You can limit how 'deep' the recursion goes*/
)

SELECT
    *
FROM
    List

In the 2nd query the CTE references itself (List) to continue splitting up the string until the conditions specified in the WHERE statement are true.

New property syntax in C#3.0

Prior to C#3.0 we would use the following syntax for creating properties:

private string _GetAndSet;
public string GetAndSet { get { return _GetAndSet; } set { _GetAndSet = value; } }


private string _SetOnly;
public string SetOnly{ get { return _SetOnly; } set { _SetOnly = value; } }


C#3.0 allows us to use the following syntax which is the equivalent of above.

public string GetAndSet { get; set; }

public string SetOnly{ get; private set; }


Create report in Reporting Services 2008

This is a great link to show you how to create reports and deploy them:

http://www.accelebrate.com/sql_training/ssrs_2008_tutorial.htm

Add a parameter to report once report is created:

Right click parameter in the report data window - add parameter and fill out required fields.
Right click the grid - tablix properties - filters and add in the filter using the parameter as the value.