Maria's journey in the data fields

It appears that Registered servers are not being stored in the registry now. There is a file here C:\Documents and Settings\{you}\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml that can be moved from server to server.

Today I have played with partition merging. The target of this was to see how long it can take, which locks are being held etc. I see that even if partions are empty, the minimum time that it takes is 30 sec ( 2x2.66 CPU 7Gb RAM ). Most of the merge time the folowing system tables are locked exclusive which means that no new query compilations, no schema changes are not allowed: sysrowsetrefs,sysrowsets,sysallocunits,syssingleobjrefs, sysobjvalues,syshobtcolumns,sysrowsetcolumns,sysserefs,syshobts,sysschobjs,sysclsobjs. If there are any schema modification processes running in the database or any data modification processes merge of course waits till those are finished and it can aquire the required locks. If there are other tables that reside on the same partition function, Sch-M locks are placed on them. Regardless of partition sizes, the merged partition will reside on the filegroup that did not hold the merged boundary. This means that is you need to merge many partitions, start

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.