Programming4us
         
 
 
SQL Server

SQL Server 2008: Administering Database Objects - Working with Tables (part 7) - Partitions

2/16/2011 5:50:28 PM

9.4.8. Partitions

You can use partitioning to increase query performance and decrease maintenance time on large tables by working with subsets of data without altering the presentation layer. You must be running the Enterprise or Developer Edition of SQL Server in order to take advantage of partitioned tables and indexes. In order to implement table partitioning, you must first create a partition function, then create a partition scheme, and finally create a table that uses the partition scheme.

Listing 21 shows the syntax needed in order to create a partitioned table.

Example 21. Syntax to Create a Partitioned Table
USE AdventureWorks2008
GO


--Create partition function
CREATE PARTITION FUNCTION SamplePartitionFunction (Datetime)
AS RANGE RIGHT FOR VALUES ('1/1/2000');
GO

--Create partition scheme
CREATE PARTITION SCHEME SamplePartitionScheme
AS PARTITION SamplePartitionFunction
TO ([PRIMARY], [PRIMARY]);
GO

--Create partition table
CREATE TABLE SamplePartitionTable
(ID INT NOT NULL,
SomeDateColumn DATETIME)
ON SamplePartitionScheme (SomeDateColumn);
GO

--Insert sample data
INSERT INTO SamplePartitionTable
VALUES (1,'1/1/1999'), (2,'1/15/1999'), (3,'1/21/1999'),
(4,'1/1/2000'), (5,'1/20/2006')

--Query the sys.partitions table to show the inserted rows for each partition
SELECT partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SamplePartitionTable')


Figure 5 shows the results returned from querying the sys.partitions table. Notice that the second partition has two rows. You can use the LEFT or RIGHT keyword when creating the function to determine which filegroup will contain the value specified as the divisor. If no value is specified, the value will reside in the left filegroup. Since we specified RANGE RIGHT for the divisor value 1/1/2000, it was inserted into the second partition.

Figure 5. Results of Listing 21

You create a partition function to specify how the rows in the table will be divided or partitioned based upon the values in the partitioning columns. The scheme actually assigns the partitions defined using the partition function to a specific filegroup. You can assign each partition to the same filegroup, but using multiple filegroups gives you the added ability to create a backup of each partition separately by performing a filegroup backup. At the very minimum, you must define enough filegroups in the partition scheme to hold the partitions defined by the partition function, but you can also add extra filegroups that will be marked next for use if the function is altered to create extra partitions. You must have one more filegroup defined in the partition scheme than the number of ranges defined in the partition function. For example, if you define a single range in the partition function, the rows will be divided into two filegroups.

9. Temporary Tables

Temporary tables are like regular tables, except they are stored in the tempdb and automatically dropped after they have been used. There are two types of temporary tables, local and global. Local temporary tables are defined using a pound sign (#) in front of the table name and is visible only to the session that created it. As soon as the session is disconnected, the temporary table is deleted. Global temporary tables are defined using double pound signs (##) in front of the table name and are visible to all the users in the database. Global temporary tables will be deleted as soon as your session is disconnected and all other sessions using the table have disconnected as well.

You will often find DBAs using temporary tables in administrative scripts. For example, you can run the following script to insert the contents of the error log into a temporary table, and then filter your results to entries that contain an error. If you were just using the sp_read-errorlog stored procedure, you would have to scroll through all the results and manually look for entries containing an error.

--Create the temp table
CREATE TABLE #Temp
(LogDate DateTime,
ProcessInfo varchar(255),
Txt varchar(Max))

--Insert the data from the error log
INSERT INTO #Temp
EXEC sp_readerrorlog

--Select only txt containing err
SELECT * FROM #Temp
WHERE Txt LIKE '%err%'

--Drop temp table
DROP TABLE #Temp

Global temporary tables can cause major application issues. For example, if you define a global temporary table in a stored procedure that is called multiple times before the global temporary table has been deleted, it will cause the next CREATE statement to fail.


Other -----------------
- SQL Server 2008: Administering Database Objects - Working with Tables (part 2) - Primary Key Constraints & Unique Constraints
- SQL Server 2008: Administering Database Objects - Working with Database Snapshots
- Programming with SQL Azure : WCF Data Services (part 3)
- Programming with SQL Azure : WCF Data Services (part 2) - Creating the Client Application
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 4) - EXPLICIT Mode
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 3) - AUTO Mode
- Programming with SQL Azure : WCF Data Services (part 1)
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 2) - Working with Binary Columns
- Using XML in SQL Server 2008: Relational Data As XML - The FOR XML Modes (part 1) - RAW Mode
- Programming with SQL Azure : Connecting to SQL Azure (part 4) - Sqlcmd
- Programming with SQL Azure : Connecting to SQL Azure (part 3) - ODBC
- Programming with SQL Azure : Connecting to SQL Azure (part 2)
- Programming with SQL Azure : Connecting to SQL Azure (part 1) - ADO.NET
- Programming with SQL Azure : Application Deployment Factors
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 3)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 2)
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 1)
- SQL Server 2008: SQL Server Web Services
- SQL Server 2008: SQL Server Service Broker - Related System Catalogs
- SQL Azure Backup Strategies (part 2)
 
 
Most View
- Windows 7 : Controlling and Customizing Your Website (part 5) - Viewing the Server Logs
- Managing SQL Server Permissions (part 4) - Using T-SQL to Manage Permissions
- Windows Server 2008 : Recovering Role Services and Features (part 2) - System State Recovery for Domain Controllers
- jQuery 1.3 : How to use a plugin
- Making Your Site Accessible to Search Engines
- SharePoint 2010 : Add a Column to a List or Document Library
- Managing Windows Server 2012 Storage and File Systems : Storage Management (part 14) - Managing volumes on dynamic disks - Configuring RAID 5
- Undoing the AutoComplete Nightmare, Images of Filth and Perversion
- Performing Administrative Tasks Using Central Administration (part 7)
- SQL Server 2008 : Managing Security - Security and SQL Agent
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS