These appear to be "exam" questions and not a current real-world problem.

I'll offer my thoughts on a CTE vs. derived table:

1. I'll continue to code CTEs as they are simplier and easier to maintain, along with being a bit "self...

Note: SQL Server 2005 SP2.

It just so happens that I'm coding a query for an application that was a prime candidate for a CTE as it had a complicated sub-query...

I believe that the 1st article by Tony showed that the result set of...

It depends upon what you are doing. Performance can vary and in certain cases be worse. See the following postings by Tony Rogerson related to performance:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-2-row-number-is-executed-number-of-cte-references-x-number-of-rows-from-the-anchor.aspx

Edit: Spelling correction.

G² (11/6/2008)

---

Now you know why just performing an "ALTER TABLE" to change the data type could not be...

G² (11/6/2008)

---

It was my understanding that varchar(max) was meant to be a replacement to the text data type because of the issue that this question raises as well as other...

Carla Wilson (11/6/2008)

---

While I agree that a datatype change to an existing production table may not be a work-around,...

I agree with "J"!

Although I had selected "change the data type to varchar(max)" I removed it as the scenario had stated that no duplicates could be assumed. Therefore, the...

The sample provided was a syntax example. It would not work against your table. Refer to the Books Online (BOL) and adjust to your table name, column name,...

Read the syntax of the CONTAINS clause in the Books Online (BOL): http://technet.microsoft.com/en-us/library/ms187787(SQL.90).aspx

If you use a variable all terms, including the "OR", in the query specification must be placed in...

The best thing to do is to drop and recreate. This way, you'll be sure that everything is correct under SQL Server 2005.

You'll also get the opportunity to adjust/change...

Short answer: NO

Long answer: The SQL engine uses various words...

I had completely forgotten about this because I never declare more than one trigger for the same action. Frankly, I never really understood why people want to have multiple triggers...

Hugo Kornelis (9/5/2008)

---

That's why many developers make it a habit to start each trigger with this line:

IF @@ROWCOUNT = 0 RETURN;

Do make sure that it's the very first line of...