Distributed Partitions (Across multiple servers)

I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.

I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.

Any help would be great.

Thanks!

Eric Elliston

SWFLParent.com

http://www.swflparent.com

Hi Eric,

I think you're thinking of distributed partitioned views. Have a look at http://msdn2.microsoft.com/en-us/library/aa479364.aspx which actually presents a few options available to you.

Cheers,

Rob

Distributed FULL OUTER JOIN results in INNER join

Hi,
I am trying to perform a full outer join across two linked
servers - LINK1 and LINK2. If I put the following query in:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
- the query plan gives me a MERGE JOIN/FULL OUTER JOIN
which is what I expect. If, however, I add a WHERE clause:
SELECT
*
FROM
LINK1.mydb.dbo.test AS C1 FULL OUTER JOIN
LINK2.mydb.dbo.test AS C2
ON
C1.n = C2.n AND C1.s = C2.s
WHERE C1.r = 0 and C2.r = 0
- I get a MERGE JOIN/INNER JOIN, which gives me the wrong
result. The table definition is:
create table test (n char(8) not null, s int not null, r
tinyint not null)
go
alter table test add primary key (n,s)
go
I have obviously simplified the problem as much as
possible. Am I misunderstanding something about FULL OUTER
JOINS or is this a feature? I can obviously circumvent by
selecting into two temporary tables on the local server
and doing the join there.
regards
KenMany thanks
Ken
>--Original Message--
>The problem is nothing to do with the query being a
distributed one. Because
>your WHERE clause references both sides of the join, the
result set will
>include only the INNER joined rows - WHERE C1.r = 0 AND
C2.r = 0 means that
>both r columns must be non-NULL in the result. Perhaps
what you intended
>was:
>...
>WHERE COALESCE(C1.r, C2.r)=0 AND COALESCE(C2.r, C1.r)=0
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Displaying Top N subtotal as well as grand total

I am currently migrating Crystal Report XI reports over to the
Reporting Services and came across a problem.
The Crystal Report displays Top N records, subtotal of these Top N
records, and the grand total of all the records. SQL stored procedure
underneath returns all the records.
>From what I have been reading in online groups and helps, these types
of features are not directly supported in the Reporting Services. The
workaround I have found so far involves returning two sets of results
("Top N" results and "All" results) and combining the two result
sets.
Since some query takes up significant amount of time, I would like to
avoid calling the stored procedures twice if necessary. Does anyone
know how I can avoid this and work with just one result set?
Thanks in advance.
ShoheiBring all the records and use filter "Top N" to filter for Top N records only.
Amarnath [MVP]
"Shohei.Yamauchi@.gmail.com" wrote:
> I am currently migrating Crystal Report XI reports over to the
> Reporting Services and came across a problem.
> The Crystal Report displays Top N records, subtotal of these Top N
> records, and the grand total of all the records. SQL stored procedure
> underneath returns all the records.
> >From what I have been reading in online groups and helps, these types
> of features are not directly supported in the Reporting Services. The
> workaround I have found so far involves returning two sets of results
> ("Top N" results and "All" results) and combining the two result
> sets.
> Since some query takes up significant amount of time, I would like to
> avoid calling the stored procedures twice if necessary. Does anyone
> know how I can avoid this and work with just one result set?
> Thanks in advance.
> Shohei
>|||If you are using SQL Server 2005, you can use ROW_NUMBER () in a Data
set so that you can get top N

Displaying Data Across when data goes down! Has to be easy!

I have a table with:

Name, Qtr, Amount
Tom, 1, 100
Bob, 1, 123
Tom, 2, 234
Bob, 2, 456
Steve, 1, 565
Steve, 2, 898

I want the query to return:

Name, Qtr 1 Amount, Qtr 2 Amount
Bob 123 456
Steve 565 898
Tom 100 234

I can't seem to figure this out! Any help would be appreciated!!
SheilaOn May 2, 8:04 pm, gwhi...@.kc.rr.com wrote:

Quote:

Originally Posted by

I have a table with:
>
Name, Qtr, Amount
Tom, 1, 100
Bob, 1, 123
Tom, 2, 234
Bob, 2, 456
Steve, 1, 565
Steve, 2, 898
>
I want the query to return:
>
Name, Qtr 1 Amount, Qtr 2 Amount
Bob 123 456
Steve 565 898
Tom 100 234
>
I can't seem to figure this out! Any help would be appreciated!!
Sheila

Search this newsgroup for the word "crosstab."|||Here is one way to do this:

SELECT Name,
SUM(CASE WHEN Qtr = 1
THEN Amount
ELSE 0 END) AS 'Qtr 1 Amount',
SUM(CASE WHEN Qtr = 2
THEN Amount
ELSE 0 END) AS 'Qtr 2 Amount'
FROM Foo
GROUP BY Name;

In SQL Server 2005 it can be done with the PIVOT operator:

SELECT Name,
[1] AS 'Qtr 1 Amount',
[2] AS 'Qtr 2 Amount'
FROM Foo
PIVOT
(SUM(Amount) FOR Qtr IN ([1], [2])) AS P;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Displaying data across the page rather than down

Hi All

I am currently working on a report where the data needs to be displayed across the width of the page rather than repeating as you go down the page. Is this possible using a table or would I need to go about it another way?

Thanks

Look at the Horizontal Tables example in Chris Hays' blog http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx