I've found SSAS a nightmare. To deploy our SSAS database it I've resorted to copying all the source code files to the server it's to be deployed on and editing...

WHERE us.UserId = @userid

The query smells pretty bad.

1. It has a LEFT JOIN that is really an INNER JOIN
2. It is using IN on the concatenation of two columns, this makes that part...

Hi mharbuz,

Thanks, I'm glad you got it to work eventually.

Jonathan

No worries, Jeff. I also use the first one most primarily (99+%) with only a few use cases for the offset one. You got me to thinking about the...

Some things are much better on the new site. The ability to paste in SQL code without it reformatting it is really good.

I'm wondering if some optimisation is needed to...

Have you tried setting the Compatability level of the database to "SQL Server 2008"?

Suppose I have to merge data from two systems which happen to have conflicting surrogate primary key values even though they...

Using integer identity primary keys with a consistent naming convention (e.g. TableNameId) has a lot of advantages in developing and supporting a database. You will know the column name and...

Are you using compression?

I only managed about 30 seconds before I had to stop it.

I'm surprised Joe Celko hasn't piled into this thread as he always seems to bring up the advantages of natural primary keys and the disadvantages of using numbers as PKs...

A different method that doesn't use PIVOT

;WITH x AS
(
    SELECT ItemID,
           SUM(IIF(MonthNo=1,  SalesQty,...

A tvf is a function that returns a table. So you use it in the same way you would use a table. From how you describe the table I think...

When you insert the rows you can OUTPUT the Id's into a temporary table. Then delete the Ids from the source table that are in the temporary table.

Also, you should...