A trick with SQL

Hi people - need help with a little thing on SQL...

I have a function that returns a table of values where one row is called 'UserID' - lets call this table 'x'

I have a another table with a row called 'UserID' in it - lets call this table 'y'

I want an SQL statement that achieves:

Select everything from table x (the table that was generated by the function) where the UserID is not in any row of table y.

Anyone think they can help?

Regards,

Will

SELECT * FROM table_x WHERE UserID NOT IN (SELECT UserID FROM table_y)

or

SELECT * FROM table_x WHERE UserID NOT EXISTS (SELECT 1 FROM table_y WHERE table_y.UserID=table_x.UserID)

|||

This example:

 SELECT t1.* FROM t1 WHERENOT EXISTS (SELECT *FROM t2WHERE t1.id = t2.t1id)

Came from this page:http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

|||

Thanks Guys

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A QuotedStr function

Hi;
is there a function to add quotes to a string sentence?; like this
exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
if @.NAME is only William, i need to add quotes to it to get a sentence like:
Select * from Customers where Name='William' and AGECUST > 25
is there a function to do that?There is no special function for that. you can do in 2 ways.
1. use double quotes and set quote identifier off so that you can use both
double and single quotes.
2. for using single quote in your string you need to put one more single
quote.
ie taking your e.g
exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST > 25')
please note it looks like double quote it is not, it is 2 single quotes.
Try this.
Amarnath, MCTS
"Willo" wrote:
> Hi;
> is there a function to add quotes to a string sentence?; like this
> exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
> if @.NAME is only William, i need to add quotes to it to get a sentence like:
> Select * from Customers where Name='William' and AGECUST > 25
> is there a function to do that?
>
>
>|||"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> There is no special function for that. you can do in 2 ways.
> 1. use double quotes and set quote identifier off so that you can use both
> double and single quotes.
where can i set that?
> 2. for using single quote in your string you need to put one more single
> quote.
> ie taking your e.g
> exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> 25')
> please note it looks like double quote it is not, it is 2 single quotes.
>
i got a syntax error here|||You need to set in the data tab itself. e.g
SET QUOTED_IDENTIFIER OFF
exec
("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
AGECUST > 25")
Just paste this in your data tab it will work. ps: To make it clear I have
left space in between the double quotes. once you get the idea you can remove
the space. Just to check whether the sql query is correct just replace "exec"
with "select"
you will get the full query itself for you to check.
Amarnath, MCTS
"Willo" wrote:
> "Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
> news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> > There is no special function for that. you can do in 2 ways.
> > 1. use double quotes and set quote identifier off so that you can use both
> > double and single quotes.
> where can i set that?
> > 2. for using single quote in your string you need to put one more single
> > quote.
> > ie taking your e.g
> >
> > exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> > 25')
> > please note it looks like double quote it is not, it is 2 single quotes.
> >
> i got a syntax error here
>
>
>|||Thank! Amarnath, works great.
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:41C16D7C-954B-43D6-A165-1AC2D887AD3F@.microsoft.com...
> You need to set in the data tab itself. e.g
> SET QUOTED_IDENTIFIER OFF
> exec
> ("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
> AGECUST > 25")
> Just paste this in your data tab it will work. ps: To make it clear I have
> left space in between the double quotes. once you get the idea you can
> remove
> the space. Just to check whether the sql query is correct just replace
> "exec"
> with "select"
> you will get the full query itself for you to check.
> Amarnath, MCTS

A function to return number of records

Hello everyone,
I am trying to write a function that returns the number of records present in a table. But in this case i want to pass 'TABLENAME' as a parameter to this function and then retrieve numrecords henceforth.
This is how I'm currently trying to retrieve it -->

FUNCTION GetNumRecords(@.tablename as nvarchar(20))
return int as
begin
Declare @.numrecs as int
Set @.numrecs = (Select Count(*) from @.tablename)
return @.numrecs
end

I'm getting the following error:

--
Must declare the variable '@.tablename'.
--

But i am already passing @.tablename as a parameter to the function and I dont exactly understand where the problem is!! Why can't I use a local variable in a SELECT statement like this.

Any input is highly appreciated,
Thank you.use execute|||Or use:

SELECT @.numrecs = rowcnt FROM sysindexes
WHERE id = object_id(@.TableName)
AND indid in (0,1)

Each table must have at least 1 entry in sysindexes.
If the table has the clustered index, its indid will be 1.
If the table has no clustered, it will have a record with indid = 0.|||I'm afraid you're confusing SQL Server and Oracle syntax

Never use: DECLARE variable_name AS datatype
but instead: DECLARE variable_name datatype|||Refer to this link (http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=280) to get the result.

A function that returns comma delimited results

Hi all,

I need to create a Function that returns a table that coverts the repeated results of its original (grouped on a certain column) and comma delimit a field.

I'll give an example, as I cant explain to well what I want.

I have this table:

ANSWER
ID TITLE QUESTIONID
0 y 1
1 n 1
2 y 2
3 n 3
4 m 3
5 y 3
6 n 4

and I want to create a function that will allow me to return the following, grouped by the question ID.

ANSWERS
TITLE QUESTIONID
y,n 1
y 2
n,m,y 3
n 4

Thanks,
Let me know if there is a more appropriate place to post this type of question.This should get you started.
create function dbo.udf(@.i int)
returns varchar(8000)
as
begin
declare @.s varchar(8000)
select @.s=isnull(@.s+',','')+Title
from Answers
where QuestionID=@.i
order by ID
return @.s
end
go
select dbo.udf(QuestionID) [Title],QuestionID
from (select distinct QuestionID
from Answers)Derived|||Thats fantastic!

I got as far as creating the function myself, but had no idea about the select you wrote.

Can I ask, what is Derived?
After integrating with my structure, I created a view which contains that SELECT, and then used that view as part of my query, and solving my issues. Thanks!!

Andre|||Derived is an alias for the derived table - virtual table formed by the inner select.
e.g.
select *
from (select OrderID from Northwind..Orders) as [derived table]

A function Smilar to DECODE Function in Oracle

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
aliYou Use a "Select case When " statment in SQL server which i same work as Decode

a function inside function in a query

The problem is the sum functions inside the effectivemass function, what should i do to fix it

Code Snippet

create view missEeCuts
AS
select e.idevent
from events e, isolatedLeptons as l, isolatedLeptons as l2
where dbo.module(e.PxMiss,e.PyMiss)>=40
and dbo.effectiveMass(e.PxMiss,e.PyMiss,sum(l.px)
,sum(l2.py))<= 150.0 and l2.eventid=e.idevent and l.eventid = e.idevent and l.idap <> l2.idap;

We need you to describe the problem or error in more detail please. I imagine you are getting a syntax or Group By -type error, but need to know more info.

Thanks,
Bryan

|||now i modified to this

Code Snippet

create view missEeCuts
AS
select e.idevent
from events as e, isolatedLeptons as l, isolatedLeptons as l2
where dbo.module(e.PxMiss,e.PyMiss)>=40
and l2.eventid=e.idevent and l.eventid = e.idevent and l.idap <> l2.idap
group by e.idevent
having dbo.effectiveMass(e.PxMiss,e.PyMiss,sum(l.px)
,sum(l2.py))<= 150.0;

and its trowing me this error

Msg 8121, Level 16, State 1, Procedure missEeCuts, Line 3
Column 'events.PxMiss' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Procedure missEeCuts, Line 3
Column 'events.PyMiss' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

the thing is that i dont want to group by it by PxMiss, PyMiss
|||

I broke the code down a bit. Looking at hte logic, you might end up with a list of repeated idevent values so you may want to use a SELECT DISTINCT at the top. Still, this should take care of the GROUP BY issue

Code Snippet

create view missEeCuts
as
select
e.idevent
from events e
inner join (
select
eventid,
idap,
sum(px) as sum_px
from isolatedLeptons
group by
eventid,
idap
) l
on e.idevent=l.eventid
inner join (
select
eventid,
idap,
sum(py) as sum_py
from isolatedLeptsons
group by
eventid,
idap
) l2
on e.idevent=l2.eventid AND
l.idap <> l2.idap
where
dbo.module(e.PxMiss,e.PyMiss)>=40 AND
dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px),l2.sum_py))<= 150.0

|||now that one return me this error

Msg 3623, Level 16, State 1, Line 1
A domain error occurred.

the original one was this function and I need to change it to a view, returning a table with all the events that fullfil the condition

Code Snippet

create function missEeCuts
(@.idevent INT)
Returns bit
AS
BEGIN
declare @.pt31x Real;
declare @.pt31y Real;
set @.pt31x = (select sum(l.px)
from isolatedLeptons(@.idevent) as l);
set @.pt31y = (select sum(l2.py)
from isolatedLeptons(@.idevent) as l2);
return ( select distinct 1
from events e
where @.idevent= e.idevent and dbo.module(e.PxMiss,e.PyMiss)>=40
and dbo.effectiveMass(e.PxMiss,e.PyMiss,@.pt31x,@.pt31y)<= 150.0
)
END

GO

|||

There is most likely a mathematical error in there. Try running each of the subqueries independently to verify they work without errors. Also, verify the data type of the px and py fields are inline with the data types expected by the effectivemass function.

B.

|||youre right, the problem is in effectivemass

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisRI got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);

> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
>
the[vbcol=seagreen]
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
(PF)[vbcol=seagreen]
and[vbcol=seagreen]
know[vbcol=seagreen]
>

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisR
I got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>
|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);

> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>
|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> You can test the partition function using the $PARTITION and sample
values:[vbcol=seagreen]
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
the[vbcol=seagreen]
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
(PF)[vbcol=seagreen]
and[vbcol=seagreen]
know
>

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisRI got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
>> Howdy all. I've got a couple questions regarding Partition Function (PF)
> and
>> Partition Scheme (PS) and I was wondering if someone could assist?
>> 1. How do I assign a table to a Partition Scheme? I created a test PF and
>> PS, but how could they really partition my table if the table doesnt know
> to
>> use them? I know I can assign the table to a filegroup that has a PS on
> it,
>> but didn't know if that alone would cut it?
>> 2. Is there some place I can verify all my settings once I think I have
> them
>> right?
>> 3. Would it ever make sense to partition an index without partitioning
>> the
>> table it resides on?
>> TIA, ChrisR
>>
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> > 2. Is there some place I can verify all my settings once I think I have
> > them right?
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
> > 3. Would it ever make sense to partition an index without partitioning
the
> > table it resides on?
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
> >I got number 1, but could still use help with 2 and 3. Here is number 1.
> >
> > CREATE PARTITION FUNCTION myRangePF2 (int)
> >
> > AS RANGE LEFT FOR VALUES (1, 100, 1000);
> >
> > GO
> >
> >
> >
> > CREATE PARTITION SCHEME myRangePS2
> >
> > AS PARTITION myRangePF2
> >
> > TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> >
> > [TransactionNumber] [int] NOT NULL)
> >
> > ON myRangePS2 (TransactionNumber)
> >
> >
> >
> >
> >
> >
> >
> > "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> > news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> >> Howdy all. I've got a couple questions regarding Partition Function
(PF)
> > and
> >> Partition Scheme (PS) and I was wondering if someone could assist?
> >>
> >> 1. How do I assign a table to a Partition Scheme? I created a test PF
and
> >> PS, but how could they really partition my table if the table doesnt
know
> > to
> >> use them? I know I can assign the table to a filegroup that has a PS on
> > it,
> >> but didn't know if that alone would cut it?
> >>
> >> 2. Is there some place I can verify all my settings once I think I have
> > them
> >> right?
> >>
> >> 3. Would it ever make sense to partition an index without partitioning
> >> the
> >> table it resides on?
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>