Mark White's BI Blog

I spotted a question over at Stack Overflow asking whether it is possible to hide specific SQL Server user-tables from end users. Strictly speaking it is not, but there is a trick to get superfluous tables out of the way of the "Tables" node of the object explorer. While, on first glance, this may seem like poor practise, it turns out that there's a bona-fide case for it: Garbage tables used in table partitions. A data warehouse that I recently delivered makes extensive use of SQL's partitioned tables feature - which enabled a data-load design which does incremental fast-loads... something which simply isn't possible without partitions. The down-side of using partitions is that, in order to keep a fact table on-line whilst loading in new data, you effectively need three tables: The fact table A staging table, into which new data is loaded and prepared A garbage table into which the old partition of the fact table can be switched The garbage table is of absolutely...