Maria's journey in the data fields

When switching partition in or out, partition_ids are changing for both source and destination, even if destination is a regular table. At the beginning I thought that new id's are being issured but it appears that SQL Server simply exchanging ids of source and destination. This can help to get alert that partition has been changed or used in applications that track data movements. Here is a sample script and result screenshot: -------------------------------------- CREATE PARTITION FUNCTION [f](datetime) AS RANGE RIGHT FOR VALUES ('20091014','20091015') GO CREATE PARTITION SCHEME [s] AS PARTITION [f] ALL TO ([primary]) GO create table t ( i int, d datetime) on s(d) insert into t select 1,'20091015 01:01' union all select 2,'20091015 04:01' union all select 3,'20091015 05:01' drop table t1 create table t1 ( i int, d datetime) on [primary] select * from sys.partitions where object_name(object_id) IN ( 't' ,'t1') order by object...

When there is a requirement to declare several local variables inside TSQL script, it appears that table variables must have it's own DECLARE clause. Surprisingly, Books Online do not mention that. Here you go : DECLARE @str1 varchar(8), @TblList table(TableName varchar(250)) Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'table'. DECLARE @TblList table(TableName varchar(250)), @str1 varchar(8) Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','. DECLARE @str1 varchar(8) DECLARE @TblList table(TableName varchar(250)) Command(s) completed successfully.

Currently table statistics and histograms are saved per table and not per table partition. Many environments that i see experience real performance degradation when migrating from view partitions to native partitions because, thanks to partition elimination, we never work on table level and always on partition level so statistics that exist only on table level give wrong estimations when data spread not equally between partitions, for instance, when some value appears a lot in one partition and only once in another, estimations for partition where the value appears once are bad. I see this behavior in both SQL 2005 and I was sure that this will be changed in SQL 2008 but the behavior stayed the same.

In the Books Online, in the Table and Index Organization chapter there is such schema: I think, this is not correct , because, there there is only one set of indexes and only one heap per table, and each b-tree or heap separated into partitions. Even in table sys.partitions, partitions are stored as object_id ->index_id->partition_id It should look like this

Yes. Even if constraint checks partitioning column and "bad" data can exist in one partition only. See for yourself: I create table natively partitioned on integer field with another char field to fill more pages (full script at the end of message), I fill the table with 1000000 rows. I also have a nonclustered index in partitioned column. For a design of our application i need to add check constraint (i >2) to the table. create partition function f (int) as range for values (5,50,100,500); create partition scheme s as partition f all to ([primary]) create table t ( i int not null,c char(2000)) on s(i) declare @i int set @i = 1 while @i begin insert into t select @i,replicate('a',2000) set @i = @i + 1 end create index ind1 on t(i) Checking the view dm_db_index_operational_stats I see that sql server scanned index on ALL partitions to check if data ok for the constraint : field range_scan_count changed from 0 to 1 in all partitions, page_lock_count also changed for...