Old post but very helpful!!
I proved it does move in SQL Server 2017 by truncating partition 2 after the update and the record is still there in partition 1
TRUNCATE TABLE IDRanges WITH (PARTITIONS(2))
select * from sys.partitions where object_id = object_id('IDRanges')
partition_id object_id index_id partition_number hobt_id rows
Possible? Yes. A good idea? Perhaps not.
Here is one possible way to use a calculated column to facilitate placing rows onto individual partitions based on a composite value.
Do this in tempdb, so we don't inadvertently clobber your work:
DROP TABLE dbo.Units;
DROP PARTITION SCHEME units_ps;
DROP PARTITION FUNCTION units_pf;
Create the ...
Partitioning adds the partition number as an implied leading key to partitioned indexes.
Your clustered index on:
[MANDT] ASC, [MBLNR] ASC, [MJAHR] ASC, [ZEILE] ASC
[<partition_id>], [MANDT] ASC, [MBLNR] ASC, [MJAHR] ASC, [ZEILE] ASC
That index cannot deliver the order needed for:
SELECT TOP (4000) *
ORDER BY MANDT, MBLNR, MJAHR, ...
You could consider a Full File Backup strategy. This is primarily of benefit in your scenario if the database is using the full recovery model.
This alternative can be more challenging to set up and maintain, but offers advantages that seem relevant to your needs. Quoting from the documentation link above:
Benefits of File Backups
File backups offer the ...
You could just do a full backup less frequently, then regular differential and/or transaction log backups. These would be very small if little has changed.
The size of the diffs will grow larger and larger as more and more data changes. They are not incremental, they include all changes since the last full backup.
Restoring would take a little longer, as you ...
I am not going to mark any file or file group as read only.
Will I still be able to do a partial backup on just the files
No. A minimal restore sequence includes all read/write filegroups. And so "a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files"
if an old ...
I doubt the wisdom of two levels of hash partitioning — if I were you, I would not partition the individual shards. But perhaps you have a compelling reason to do that.
You could use this for the fist level of partitioning:
PARTITION BY HASH (id)
and for the second level, you could use
PARTITION BY HASH (reverse(id::text))
Unlike other DBs, MySQL does the binding in the client. Hence, whether bound or not, the server has the same amount of work, and goes through the same decisions about how to optimize the query.
Binding in the client is desirable as a way to combat "SQL injection" and to get proper escaping of strings.
I have seen as many as ...
All three approaches should produce the same query plan so there shouldn't be a difference from that standpoint. You'd obviously need to verify that in your environment when the queries get a little more complicated to ensure that partition pruning is actually happening when you expect it to happen.
Using just conditions in the where clause is vastly easier ...
What negative effects will it have if I add partitions for a couple of years ahead
None. A partition is basically an invisible table, so you'll have a handful of extra rows in various catalog tables and four preallocated extents per empty partition.
will it be ok to use a timestamp column as a partitioning key, or is there any advantage in using a ...
Did the primary index become nonclustered in order to spread the data
between the partitions?
Why was the ClusteredIndex_on_PartitionByVejes_637678578300644842
index created and then deleted?
No. A requirement for table partitioning is the unique clustered index key must explicitly include the partitioning column. Since fecha is not part of the existing ...
dba.SE is a question-and-answer format site. It works best if you ask a discrete question. Since you have multiple, complicated issues and need significant performance, you are better served hiring a consultant to advise you.