FAQ : What is Partition Scheme in SQL Server?

Once you have created the Partition Function then the next step is to create Partition Scheme. As already mentioned, One partition function may have one or more Partition Scheme. . The partition scheme defines how any resulting partitions will be stored on filegroups. You list the filegroups for a partition scheme in the same order that you want to have them map to the partitions defined by the partition function. Creating a partition scheme assumes that you have already created the filegroups. You can mention ALL TO keyword to create all the partition to a single filegroup.

Eg. From BOL
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);

Edit : Should Filegroup single file or multiple files considered as out of scope in this context. But selecting single filegroup VS multiple filegroup and Single files per filegroup VS multiple files also a significant decision to be made in terms of performance.

FAQ : What is Partition Function in SQL Server?

Partition functions are special kind of objects (of course created by user) created in a database which can be used only by partition scheme. Partition function contains a data type , that must be the same data type as the partition column chosen in the table. Partition Function as such not directly related to any table.

NOTE: Even though Partition Function to Partition scheme is One to Many, in sliding window scenario moving data between in and out of partition is a common requirement, which require Partition function modification. So practically it makes each Partition table should have exclusive partition function and scheme.
Partition functions are not user defined functions.

Here are few of facts to be known about partition functions• Partition functions are not listed as database objects in the sys.all_objects or sys.objects system tables; instead, you can find them listed in sys.partition_functions.
• Partition functions are not contained by a database schema.
• Special commands must be used for creating, altering, and dropping a partition function:
o CREATE PARTITION FUNCTION
o ALTER PARTITION FUNCTION
o DROP PARTITION FUNCTION
• Partition functions can be invoked interactively in Transact-SQL by using the $PARTITION function.

Partition Functions System Objects

• sys.partition_functions : Contains a row for each partition function.
• sys.partition_range_values : Contains a row for each range boundary value of a partition function of type R.
• sys.partition_parameters : Contains a row for each parameter of a partition function.
• $PARTITION. :Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
Eg. SELECT $PARTITION.RangePF1 (10) ;

FAQ : What is the advantages and disadvantages of Table Partitioning of 2005 over Partition View of earlier versions?

Advantage of Table Partitioning
• No need to create separate physical tables for each ranges manually.
• There are few limitations when you insert data into Partition View like you cannot use BULK INSERT/BCP etc.
• Less complex to manage and administrate
• SQL Server automatically manages the placement of data in the proper partitions.


Disadvantage of Table Partitioning
• Major disadvantage is, we cannot have different index model for different range. Ie. In general, we may need to have more number of indexes when the data is READ ONLY and less or different index model for the data which is READWRITE. That is not possible with partition table.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

Partition is not recommended for a table in following condition

1. If you want to have different indexing structure for READ ONLY and READ WRITE data then partition is not recommended.
2. If the table is not accessed much or do not required much index maintenance
3. If the query on the tables are filtered(WHERE condition) by some column which cannot be used for partitioned.
4. If you planning to use ONLINE INDEX REBUILD option you cannot use partition. You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

FAQ: How to determine partition suits for a table ?

There are few general guidelines for determining whether the partition feature is suited for a table or not. One point to be noted here is, if a table is not partitioned properly (ie. Not selected the most suitable partition column), though it will not adversely affect the performance but it will not give any performance benefit either. Remember that the query optimizer can limit the partitions that are scanned to resolve a query only if the query filters on the partitioning column. Precisely, just because the table is partitioned, we will not have performance benefit.

1. First of all, just because of a table is big, it should not be partitioned. Ie. Only size of the table cannot be "THE" criteria for partition.
2. If the index maintenance on a table is time consuming or make the table offline for the users, you may opt partition because re-indexing on only required partition is better than re-indexing the whole table. Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table.
3. You can go for partition if the data must be aged out of the table (archived) periodically and the current delete process is taking time or blocking other users.
4. If the table is loaded with new data periodically and the loading process takes time and the table data lends itself to a partition column based on ascending date or time we may go for partition.
5. Table backup of huge table can be managed smartly by using new backup features of sql server 2005 and partitioning.
6. To summarize, the best way to put this in single statement is, if you have any table which fits into SLIDING WINDOW scenario you must partition it.

FAQ: What all are the types of table partitions in SQL Server

There are two types of partitions. Horizontal and vertical.

Horizontal partition
Horizontal partition divides the data on subset of rows. Ie. Divide the data in such a way that all the rows of single year /month /week(Range) may be in one partition kind. In earlier versions of SQL Server this method was achieved by creating physical tables for each subsets and UNION the tables using a View(Partition View) and application access the data using Views.

Vertical partition
In Vertical Partition , the columns of a very wide table are spread across multiple physical tables containing distinct subsets of the columns with the same number of rows. The relation would be ONE TO ONE among the tables. The result is multiple tables containing the same number of rows but different columns, usually with the same primary key column in each table. Often a view is defined across the multiple tables and queries directed against the view.

FAQ : What is Table Partitioning in SQL Server?

Partitioning is the process of dividing data into small , more manageable chunks. We can divide the data horizontally (on rows ) or vertically (on columns) . Table partitioning can make very large tables and indexes easier to manage, and improve the performance of APPROPRIATELY FILTERED queries. Horizontal Partitioning is one of the new feature added in SQL Server 2005 (in earlier versions we used to create separate physical table and create a wrapper view which union all the tables which is also known as Partition View). When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes. You can then perform maintenance operations on individual partitions, and properly filtered queries will access only the correct partitions. But it is still one table as far as SQL Server is concerned

Error : CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.

One of the common reason for this error is, when we try to attach a MDF file which is already attached (to the same instance or any other instance) we get this error. So the solution is this, if you get this error when you try to attach a MDF file, then ensure that the file is not accessed by any other process or attached to any SQL Server instance already.

FAQ : How to disable AUTO UPATE STATISTICS for a specific table or index in SQL Server

Using sp_autostats system stored procedure we can switch off AUTO UPDATE STATISTICS on a specific table or index. Please note that, if AUTO UPDATE STAT is OFF in Database level, you can not switch ON object level. In otherwords, if the DB level Auto update stat is ON then you can override the DB level setting and switch off AUTO UPDATE STAT object level using this stored procedure.

Syntax
sp_autostats [ @tblname = ] 'table_name'
[ , [ @flagc = ] 'stats_flag' ]
[ , [ @indname = ] 'index_name' ]


Enabling automatic statistics for all indexes of a table
The following example enables the automatic statistics setting for all indexes of the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product', 'ON'

Community Technology Preview of Microsoft SQL 2008 R2 is available for download

The latest Community Technology Preview (CTP) for SQL Server 2008 R2 available to the general public today. The new capabilities in the latest CTP include support for Windows Server 2008 R2 – including Hyper-V with Live Migration – as well as enhanced data compression with support for Unicode UCS-2. Also included are new visualization features and a Report Part Gallery to Report Builder 3.0.



SQL Server 2008 R2 includes enhancements designed to help administrators centrally monitor and manage multiple database applications, instances or servers. In addition, it has enabled high-scale complex event-stream processing through SQL Server StreamInsight, and expanded business intelligence capabilities with SQL Server PowerPivot for Excel.



At the PASS Summit last week in Seattle, Microsoft announced two new premium editions – SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse – these will be available once SQL Server 2008 R2 is released. SQL Server 2008 R2 is on track for general availability in the first half of 2010.



The Community Technology Preview of Microsoft SQL 2008 R2 is available for download Here

FAQ : Why we need to have different service account for each service in SQL Server?

In SQL Server, as per the components we select, different services gets installed like SQL SERVER Database engine, Analysis Service, Integration Services, Reporting Services and SQL Server Agent Services. But each service have its own specific tasks to do which requires different privilege and permissions. If we are configuring all the services startup account with a single user, this user will have to have a sum of all the permission required by all the services, which is very against security policy recommended by Microsoft. For eg. DB Engine needs permission to the folder where the data and log files are kept why because it has to write the data into these files. Whereas SQL Server Agent does not need this permission. So, if we are using same user for both the services, if the SQL Server Agent service got hacked or compromised , even the database engine is at risk. This is the very basic reason why we need different windows user for each service.

SQL Server BI Webcast series by Amit Bansal

There is a webcast series starting soon on SQL Server BI by Amit Bansal.

Webcast 1: BI for you -November 6, Friday, 3 pm to 4.30 pm

Webcast 2: Create your first cube -November 9, Monday, 3 pm to 4.30 pm

Webcast 3: Extract data from the cube -November 11, Wednesday, 3 pm to 4.30 pm

Webcast 4: Learn MDX scripting -November 13, Friday, 3 pm to 4.30 pm



To register & browse more details about each webcast, visit