Partitioning Database Table

Introduction


Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection.

The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on the data. All partitions of a single index or table must reside in the same database.


Partitioned tables and indexes support all the properties and features associated with designing and querying standard tables and indexes, including constraints, defaults, identity and timestamp values, and triggers. Therefore, if you want to implement a partitioned view that is local to one server, you might want to implement a partitioned table instead.


Deciding whether to implement partitioning depends primarily on how large your table is or how large it will become, how it is being used, and how well it is performing against user queries and maintenance operations.

• Generally, a large table might be appropriate for partitioning if both of the following are true:
• The table contains, or is expected to contain, lots of data that are used in different ways.

Queries or updates against the table are not performing as intended, or maintenance costs exceed predefined maintenance periods.


For example, if a current month of data is primarily used for INSERT, UPDATE, DELETE, and MERGE operations while previous months are used primarily for SELECT queries, managing this table may be easier if it is partitioned by month. This benefit can be especially true if regular maintenance operations on the table only have to target a subset of the data. If the table is not partitioned, these operations can consume lots of resources on an entire data set. With partitioning, maintenance operations, such as index rebuilds and defragmentations, can be performed on a single month of write-only data, for example, while the read-only data is still available for online access.


When to use:


You should partition your tables that has accumulated a lot of data over the time and you find that the performance is slowing down. It would not be a very good idea to partition a table it has  just a couple of hundred rows for example : Product Category table. You could partition your tables that contains a lot of transactions like Purchase Order tables, Sales Order tables, General Ledger Tables etc.


Steps to create Partitioning:
Step 1:
Open the SQL Server 2008 management studio and select the appropriate table that needs to be partitioned. Right click on the table and in the context menu select Storage >> Create Partition.

Step 2:

I am using the [Purchasing.WorkOrder] table in the 'AdventureWorks' sample database. When you select the above option a wizard dialog box appears, press the next button once.

Service Broker Solution

Introduction

Service Broker was introduced in SQL Server 2005 to provide a reliable, scalable, and asynchronous message queuing system for local or distributed applications. Service Broker solutions can range from a simple application existing ion a single database to a complex application reaching across remote SQL Server instance.

The Service Broker is a new platform for building asynchronous – distributed database applications. In this article I will give a brief overview of the definitions of the most important aspects in the Service Broker platform, and give a sample application that leverages some of its features.

Service Broker Provides

With the new Service Broker platform, internal or external SQL Server instances can send and receive guaranteed, asynchronous messages by using Transact T-SQL. Messages can be sent from within the same database, different database, or even remotely located SQL Server instances.

Service Broker Working

The Service Broker communicates with a newly developed protocol called the Dialog, which allows for bi-directional communication between two endpoints. The Dialog protocol specifies the logical steps required for a reliable conversation, and makes sure that messages are received in the order they were sent. The following diagram below from Microsoft SQL Server 2005 books online, illustrates how the Dialog protocol is being utilized in the Service Broker platform:

The following is another overview diagram of the Service Broker, it shows the main entities of the Service Broker such as a Message, Contract and Service which will be explained further in the next section. However let's look at the dialog conversation part which is a high level protocol used by the Service Broker to make sure messages are received in order and with no loss. The dialog conversation protocol as we see is not a transportation protocol as it is an application protocol to maintain the message Exactly-Once-In-Order (EOID). The EOID, is the concept of ordering messages no matter what order they were received in from the transportation channel.

The Service End Points as we see below are the communication end points between two Service Brokers. They are the ones who support the transportation protocols such as TCP/IP, HTTP, SOAP etc…

The end points are created on each Service Broker, to allow for remote communication between different SQL Server instances. There will be further explanation for each of the following in the next section.

Enabling Service Broker

Check if service broker is enabled :

SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDatabaseName'

Enable service broker :

ALTER DATABASE <> SET ENABLE_BROKER
GO

Date & Time Format Conversion

Some date and time conversion functions are:

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

                                        – Oct  2 2008 11:01AM          

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008                  

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        – Oct  2 2008 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)           – 01/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)           – 03/01/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)            – 1/03/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)             – 1/3/2012

SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)               – 1/3/12

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)             – 01/03/12

SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)         – JAN 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)         – Jan 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)       – January 03, 2012

SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)           – 2012/01/03

SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)             – 20120103

SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)           – 2012-01-03

SELECT CURRENT_TIMESTAMP                        – 2012-01-05 07:02:10.577

– SQL Server DATEADD function

SELECT DATEADD(month,2,’2012-12-09′)            – 2013-02-09 00:00:00.000

– SQL Server DATEDIFF function

SELECT DATEDIFF(day,’2012-12-09′,’2013-02-09′)  – 62

– SQL Server DATENAME function

SELECT DATENAME(month,   ’2012-12-09′)          – December

SELECT DATENAME(weekday, ’2012-12-09′)          – Sunday

– SQL Server DATEPART function

SELECT DATEPART(month, ’2012-12-09′)            – 12

– SQL Server DAY function

SELECT DAY(’2012-12-09′)                        – 9

– SQL Server GETDATE function

– local NYC – EST – Eastern Standard Time zone

SELECT GETDATE()                                – 2012-01-05 07:02:10.577

– SQL Server GETUTCDATE function

– London – Greenwich Mean Time

SELECT GETUTCDATE()                             – 2012-01-05 12:02:10.577

– SQL Server MONTH function

SELECT MONTH(’2012-12-09′)                      – 12

– SQL Server YEAR function

SELECT YEAR(’2012-12-09′)                       – 2012

Download complete file