This kind of question appears on SQL forums quite often...

There are few possible reasons for this to happen, parameter sniffing is one of them.

...

I like the idea of having a sid (a surrogate id) on my tables, even if I do have a natural key that can serve as a primary key. ...

In your case you can use:

;WITH SUM_SALES_SUMMARY

AS

(

SELECT CSCODE AS CSCODE

...

Some people will tell you that you should always use natural keys (JC the master of them :-)) and others will tell you that you should always use surrogate keys....

Use this:

'%[^a-z ''^-]%'

Sector7G (8/10/2012)

---

Hello,

I’d like to confirm if what I am attempting is possible. I suspect it’s not, but wanted to ask. I am trying to re-write a query that...

You should learn about aggregate functions.

In your case you will need to use SUM()

I guess to answer OP given question any of T-SQL solution would do, I also think that interviewer will be looking for answer in SQL. I wouldn't go with...

I don't have VS to test...

try:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class UserDefinedFunctions

{

static readonly Regex _regex = new Regex(@"[^0][0-9]*[^0]", RegexOptions.Compiled);

...

try to return

(SqlString) s.Value.TrimStart('0').TrimEnd('0');

instead of

new SqlString(s.Value.TrimStart('0').TrimEnd('0'));

what about Regex?

Don't forget to create static Regex object instead of instance in the function.

To remove leading and trailing...

You cannot even remotely compare performance of PATINDEX and .NET Regex...

PATINDEX can only find the pattern in the string, but to...

Yeah,

you better not

debug my code

without note

it can explode!

:hehe:

You can find a most of details about this one in BoL.

Usually this one is used for aggregation-like string concatenation...

dwain.c (8/10/2012)

---

Hah! I was waiting to hear someone suggest that. Not surprised it was you.

Where have you been lately Eugene? Haven't...

You should try CLR too...