February 27, 2011 at 7:49 pm
Craig - you missed one key thing.
Each table had the required constraint
This probably should have specified that each table had the required CHECK constraint to ensure that data within only a certain time period was in it. THEN when running a select against the view, the QO will see that it doesn't need to hit those other tables.
Edit: and I see that Paul and Jeff both answered this while I was composing it - Thanks guys!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 27, 2011 at 7:56 pm
Actually, after all these years, I finally found the correct rules for the partitioning column including the fact that the partitioning column must be a part of the PK. I found it under "partitioning schemes" in BOL... not under "partitioned views". Here's the list straight out of BOL...
[font="Arial Black"]Partitioning Column Rules[/font]
Only one column can be used for partitioning, and it must exist on each member table. CHECK constraints identify the data available in each member table. The following additional rules apply:
The key ranges of the CHECK constraints in each table cannot overlap with the ranges of any other table. Any specific value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, IN, AND, OR, <, <=, >, >=, =.
The partitioning column cannot be an identity, default or timestamp column.
The partitioning column must be in the same ordinal location in the select list of each SELECT statement in the view. For example, the partitioning column is always the first column in each select list or the second column in each select list, and so on.
The partitioning column cannot allow for nulls.
The partitioning column must be a part of the primary key of the table.
The partitioning column cannot be a computed column.
There must be only one constraint on the partitioning column. If there is more than one constraint, SQL Server ignores all the constraints and will not consider them when determining whether the view is a partitioned view.
There are no restrictions on the updatability of the partitioning column.
--Jeff Moden
February 27, 2011 at 8:00 pm
pietlinden (2/27/2011)
For someone who "needs to solve [this] on his own", you sure got the newsgroup to do a lot of your homework. The problem with that is who is going to sit next to you and take the exam? Just about every database exam I have ever had has had a substantial section on paper... mappings, ERDs, and all that jazz. Hope you find your book soon, and have the nerve to actually open it. The problem with cheating on homework is that it's where you learn, and having someone else give you the answer completely defeats the purpose.
This is just plain unhelpful, and more than a little rude.
If you don't want to help, or don't approve of the questioner's motives, move on to another thread without comment - there are plenty of others out there. I have no problem with assisting people with homework or anything else, so long as they make some sort of effort (which daveriya has) and are learning from the experience (again, true). We all had to start somewhere, and you just cannot assume that someone only posts a question here to 'cheat' - it is just as likely they are looking for some help and pointers from more experienced people. None of us know enough about the exact circumstances of the poster to make sweeping assumptions about their motives.
Forums are supposed to be a place for people to help others. Far too many threads are ending up as conversations on an unrelated topic by regulars who ought to know better - start a new thread for a new topic! In addition, far too many people are posting with comments in the same vein as the one posted above. I get that some people have moral objections to some questions - that is fine, but please just move on. Thank you.
daveriya - it would help us to help you if you would please read http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 8:10 pm
Jeff Moden (2/27/2011)
I found it under "partitioning schemes" in BOL... not under "partitioned views".
The is a good deal of coverage of this topic in BOL, in separate entries. The main index entry is 'Partitioned Views [SQL Server]' - which links to a number of useful pages including a complete list of requirements for the partitioning column under 'Creating Distributed Partitioned Views'.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 8:30 pm
SQLkiwi (2/27/2011)
pietlinden (2/27/2011)
For someone who "needs to solve [this] on his own", you sure got the newsgroup to do a lot of your homework. The problem with that is who is going to sit next to you and take the exam? Just about every database exam I have ever had has had a substantial section on paper... mappings, ERDs, and all that jazz. Hope you find your book soon, and have the nerve to actually open it. The problem with cheating on homework is that it's where you learn, and having someone else give you the answer completely defeats the purpose.This is just plain unhelpful, and more than a little rude.
If you don't want to help, or don't approve of the questioner's motives, move on to another thread without comment - there are plenty of others out there. I have no problem with assisting people with homework or anything else, so long as they make some sort of effort (which daveriya has) and are learning from the experience (again, true). We all had to start somewhere, and you just cannot assume that someone only posts a question here to 'cheat' - it is just as likely they are looking for some help and pointers from more experienced people. None of us know enough about the exact circumstances of the poster to make sweeping assumptions about their motives.
Forums are supposed to be a place for people to help others. Far too many threads are ending up as conversations on an unrelated topic by regulars who ought to know better - start a new thread for a new topic! In addition, far too many people are posting with comments in the same vein as the one posted above. I get that some people have moral objections to some questions - that is fine, but please just move on. Thank you.
daveriya - it would help us to help you if you would please read http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Paul
Heh... you should practice what you preach in this case. 😉
--Jeff Moden
February 27, 2011 at 8:32 pm
SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
I found it under "partitioning schemes" in BOL... not under "partitioned views".The is a good deal of coverage of this topic in BOL, in separate entries. The main index entry is 'Partitioned Views [SQL Server]' - which links to a number of useful pages including a complete list of requirements for the partitioning column under 'Creating Distributed Partitioned Views'.
Yep... but those are for distributed partitioned views... not local partitioned views. 😉 The fact that the partitioning column must be a part of the PK is not included in the section called "creating" nor in the sub-selection that leads to called "Using Partitioned Views".
If you never have the intention of building "distributed" partitioned views, you may never find that nuance. They really need to include it in the "creating" and "using" sections of BOL.
--Jeff Moden
February 27, 2011 at 8:50 pm
Jeff Moden (2/27/2011)
Heh... you should practice what you preach in this case. 😉
Heh. No. My comments were as much for daveriya's benefit as anyone else's - so quite on-topic.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 8:52 pm
Jeff Moden (2/27/2011)
Yep... but those are for distributed partitioned views... not local partitioned views. 😉 The fact that the partitioning column must be a part of the PK is not included in the section called "creating" nor in the sub-selection that leads to called "Using Partitioned Views". If you never have the intention of building "distributed" partitioned views, you may never find that nuance. They really need to include it in the "creating" and "using" sections of BOL.
Jeff, if you have a suggestion to improve Books Online, submit it. My suggestion to Craig was to look up Partitioned Views in BOL, that's all. The information is there, so stop quibbling.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 8:54 pm
SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
Heh... you should practice what you preach in this case. 😉Heh. No. My comments were as much for daveriya's benefit as anyone else's - so quite on-topic.
Perhaps... perhaps not. They seemed quite well directed at pietlinden and I believe that people have the same right to object (as you have just done) as people do to post. 😉
--Jeff Moden
February 27, 2011 at 8:56 pm
SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
Yep... but those are for distributed partitioned views... not local partitioned views. 😉 The fact that the partitioning column must be a part of the PK is not included in the section called "creating" nor in the sub-selection that leads to called "Using Partitioned Views". If you never have the intention of building "distributed" partitioned views, you may never find that nuance. They really need to include it in the "creating" and "using" sections of BOL.Jeff, if you have a suggestion to improve Books Online, submit it. My suggestion to Craig was to look up Partitioned Views in BOL, that's all. The information is there, so stop quibbling.
Jeez, Paul. Lighten up! You were saying that the information was in a particular spot and I was saying it is not where it is expected.
--Jeff Moden
February 27, 2011 at 9:28 pm
Sorry for the delay, I was fiddling with the code you provided Jeff, trying to see what I'd not been paying attention to. I've known about a partitioned view, but sadly never really applied it, always finding them to be more annoying then anything else to work with when I investigated them via help files.
One of these days I'm going to learn to put a sock in my mouth before I use strong language towards any modeling technique again. I must learn to question, not react. I've been lone wolfing too much lately, it's starting to show.
Jeff Moden (2/27/2011)
It's not necessary to touch all of the tables BUT... you do have to build the underlying tables and the partitioned view correctly. You MUST have a non-overlapping check constraint on the "partitioning column" and the partitioned column MUST (I've never been able to get it to work any other way) be a part of the PK (something they don't come right out and tell you in BOL).Here's the code that demos it all including inserts that automatically partition themselves to the correct table(s).
Thank you for this succinct code in showing exactly what I wasn't seeing. The tables should be called BoardToHead1... BoardToHead2... :blush:
Alright, I not only retract my abusive statement of the book's technique, I'm going to go paddle myself with wet noodles for a while. I've always approached problems like this via this method, which may have a lot to do with being self-trained on the job. I would hazard that it leaves a few gaping holes in my edumacation:
(Note, I do not recommend using this technique, it's why I avoid it.)
--DROP PROC FindRightTable
GO
CREATE PROC FindRightTable
@StartDate DATETIME,
@EndDate DATETIME
AS
DECLARE @C INT --DayCounter
, @sql nVARCHAR(4000)
, @tableName nVARCHAR(100)
, @StartMonth DATETIME
SET @C = 0
SET @startMonth = DATEADD( mm, DATEDIFF( mm, 0, @startdate), 0)
print @startMonth
WHILE @C <= DATEDIFF( mm, @StartDate, @EndDate)
BEGIN
IF @C > 0
BEGIN
SET @sql = @sql + ' UNION ALL ' + CHAR(13) + ' SELECT * FROM '
END
ELSE
BEGIN
SET @sql = 'SELECT * FROM '
END
SET @tableName = N'dbo.UnionTest_'
+ CONVERT( nVARCHAR(50), YEAR( DATEADD( mm, @C, @startMonth)))
+ CONVERT( nVARCHAR(50), RIGHT( 100 + MONTH( DATEADD( mm, @C, @startMonth)), 2)) --+100 trick courtesy of LutzM
+ ' '
SET @sql = @sql + @tableName
SET @sql = @sql + 'WHERE SomeDate >= ''' + CONVERT( VARCHAR(25), @StartDate, 121) + ''' AND SomeDate <= ''' + CONVERT( VARCHAR(25), @EndDate, 121) + ''''
--PRINT @sql
END
PRINT @sql
EXEC sp_executesql @sql
GO
EXEC FindRightTable @StartDate = '20110315', @EndDate = '20110316'
EXEC FindRightTable @StartDate = '20110215', @EndDate = '20110316'
EXEC FindRightTable @StartDate = '20110115', @EndDate = '20110316'
EXEC FindRightTable @StartDate = '20110115', @EndDate = '20110216'
@SQLKiwi: Sorry if you felt this or I was derailing the thread. I felt it appropriate still due to the context of the initial requests dealing with unioning tables designed this way.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 27, 2011 at 11:30 pm
(Moved to a later post)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 28, 2011 at 6:48 am
Craig Farrell (2/27/2011)
Sorry for the delay, I was fiddling with the code you provided Jeff, trying to see what I'd not been paying attention to. I've known about a partitioned view, but sadly never really applied it, always finding them to be more annoying then anything else to work with when I investigated them via help files.One of these days I'm going to learn to put a sock in my mouth before I use strong language towards any modeling technique again. I must learn to question, not react. I've been lone wolfing too much lately, it's starting to show.
No, no... you were fine. I didn't mean to come across personally when I said you have to do it right. Heh... guess you're in good company when it comes to needing to learn about socks. 😛 I've mostly been working in the "lone wolf" mode throughout my career and my email tone really sucks. I could have just left the "do it right" part out of it and it would have been much nicer. My most sincere apologies. I'll get it right one of these days.
Now, where'd I put that sock? 😀
--Jeff Moden
February 28, 2011 at 7:02 am
daveriya (2/27/2011)
One way to show its registered or not...{code removed} group by clause doesn fit anywhere here
Have a look at something like this:
DECLARE @Bank1_201001
TABLE (
num_trans INTEGER NOT NULL,
spend MONEY NOT NULL,
registered BIT NOT NULL
)
;
DECLARE @Bank2_201001
TABLE (
num_trans INTEGER NOT NULL,
spend MONEY NOT NULL,
registered BIT NOT NULL
)
;
INSERT @Bank1_201001 (num_trans, spend, registered)
VALUES (123, $5764.38, 0),
(342, $9638.47, 1)
;
INSERT @Bank1_201001 (num_trans, spend, registered)
VALUES (309, $7511.06, 0),
(211, $5009.83, 1)
;
SELECT SubQuery.registered,
[Number of Transactions] = SUM(SubQuery.num_trans),
[Total Spend] = SUM(SubQuery.spend)
FROM (
SELECT B1.registered,
B1.num_trans,
B1.spend
FROM @Bank1_201001 AS B1
UNION ALL
SELECT B2.registered,
B2.num_trans,
B2.spend
FROM @Bank2_201001 AS B2
) AS SubQuery
GROUP BY
SubQuery.registered
ORDER BY
SubQuery.registered;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 28, 2011 at 8:37 am
Thanks a lot SQLkiwi.
You solve my prob.
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply