Partitoned views
-
I was testing on queries against my partition views and I found some behaviour that doesnt make sense. As far as I have read the partition key should be part of the primary key of the undelying tables.
Sample tables:
create table [a] (a INT check (a between 1 and 5), b varchar(1) primary key (a,b))
create table (a INT check (a between 6 and 10), b varchar(1) primary key (a,b))
create view ab as
select * from a
UNION ALL
select * from b
Problems
1. These two inserts makes the optimizers do a nested loop check against both tables a and b before inserting.
insert into ab values (1,'a')
insert into ab values (1,'b')
Shouldnt be inserting against just table a? (thus making a check on a only)?
2. This select makes a filter check in both tables a and b. Should it just check in table a only?
select * from ab where a = 1
3.- This ones makes a correct decision on where to select from, but look the query.
select * from ab where a = 1 and 1=1
Should 1=1 NOT be there?
4.- This ones makes a correct decision on where to select from, but look the query.
select * from ab where a = 1 and b is not null
Should b is not null NOT be there?
Thanks in advance for your thoughts.
-
The answer is yes it should, but no it does not.
Looking back at the early days of when partitions were implemented in mainframe DB2, very similar problems occurred. It took many years for the DB2 developers to produce true partition independance, where you could simultaneously bulk insert, update, delete and select into separate partitions without each operation blocking the other.
Partitioning in SQL Server is simply not at the same stage of maturity. In the meantime, which could be a few years, we will have to learn a number of tricks to fool the optimiser into giving greater partition independance than it normally gives.
The problem is that although the extra syntax you use may be necessary and beneficial today, Microsoft could release a patch tomorrow that might render it redundant or harmful. Each site will have to assess the trade-off between getting the usability enhancemnts given by adding extra syntax, and the additional maintenance that may be needed if a MS patch makes the extra syntax harmful. This situation is sadly familiar to DBAs skilled in many other products, so Microsoft are just following an industry trend...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
At SQLPASS, I was told that Yukon is supposed to have "real" partitioning, in which the partitions can be managed individually, taken on/offline, etc.
Larry
Larry
-
That will be awesome. So I can implement Oracle Solutions into Sql Server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply