Inconsistencies among system tables?

, , ,
1

Hi,

I get a SQL Server 2008 database called ‘BLANK’. And it contains several tables, one of them is ‘MyTable’. Its object ID is 480720765.

I find a very strange problem in the table.

When I login as DAC mode and using the following statements:

USE BLANK;

select * from sys.syscolpars where id = 480720765;

I will get all the columns for ‘MyTable’, and their colid is from 1 to 21, as below:

https://social.msdn.microsoft.com/Forums/getfile/977856

But when I use the following statements to find the partition id for the table:

USE BLANK;

select partition_id from sys.system_internals_partitions where (object_id = '480720765') AND (index_id <= 1);

I will get the partition id as 72057594062897152.

Then if I use the following statements to find the columns of the partition:

USE BLANK;

select * from sys.system_internals_partition_columns where partition_id = '72057594062897152';

I will get all the columns for the partition, but the partition_column_id is from 0 to 20, instead of from 1 to 21.

Below is the result:

https://social.msdn.microsoft.com/Forums/getfile/977858

Originally I think syscolpars.colid should equal to system_internals_partition_columns.partition_column_id, since all other tables follow this way. But ‘MyTable’ is not. Why?

Thanks

2

Post the table DDL

3

It's real simple. It's because MS designed or misdesigned it that way. And why on this good Green Earth would you be trundling about in the system database for this type of thing anyway?