I've answered this question twice today so I thought it made good sense to blog it.  I am going to give three examples that will quickly get you to the text of a stored procedure or other object type.  I am assuming you are looking to skip the [Database Name] --> Programmability --> Stored Procedures path of Object Browser, although the new "DROP and CREATE to" option in SQL 2008 is nice.  We will be using the following methods today:

• OBJECT_DEFINITION()
• sys.sql_modules
• sp_helptext

First up is OBJECT_DEFINITION().  This system function takes an object_id and returns a varchar(max) output of the object source text.  There are multiple types that can be used with OBJECT_DEFINITION, check the BOL reference for a complete list.  The syntax for this function is:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.GetCustomer', 'P')) AS objectText;

Next up is the object catalog view sys.sql_modules.  This view returns a row for each object that is a SQL language-defined module.  Again, check the BOL reference for your complete list of types.  This view returns many useful pieces of information but today we are only after the definition column, which contains the SQL text of the module.  Here's the simple query:

SELECT [definition] AS objectText
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID(N'dbo.GetCustomer', 'P');

Last is the old standby sp_helptext.  This system stored procedure takes and object name (and potentially a column name) and returns the definition of multiple object types with a 255 character row output.  The source for this stored procedure is sys.sql_modules, referenced above.  Here is a sample call to this stored procedure:

EXECUTE sp_helptext @objName = N'dbo.GetCustomer';

The one additional option provided by sp_helptext is the ability to return computed column definitions.  To get that detail we simply add the columnname parameter:

EXECUTE sp_helptext @objName = N'dbo.Customer', @columnname = 'CustomerComputedColumn';

So the next time you have that "what does this object do?" moment give one of these options a try.

Monday July 28th I will be sharing the podium with my colleague and friend Sergey Barskiy to give a talk about SQL Server 2008 functionality for our .NET developer brethren.  It should be a great time, details of the event can be found on the ADNUG site.

I got the email tonight from my next speaking engagement and got quite a laugh...one missing letter has left me as "Whiney".  Maybe Doug is trying to tell me something?

If you like reading whitepapers have I got the find for you.  The Data Platform Insider has seven articles across the 2008 product for your reading pleasure.  You can feel the buzz cranking up as we get closer to RTM.  These are always such fun times...

We're back talking SQL Server 2008, hopefully with announcements of RTM around the corner.  Today I want to cover one of the great new features in the upcoming release of SQL Server - data compression. 

Data compression allows you to store your data more effectively and reduce the storage resources needed.  The biggest winners of this feature will be instances that have large I/O bound workloads, such as data warehouses.

To give some perspective, a 4-byte integer in SQL Server 2005 would take up 1 byte + 4 bits per value with data compression enabled in SQL Server 2008.  Apply this across millions of rows and you have some significant savings.

Compression can be applied at either row or page level for the following object types:

• Tables
• Nonclustered indexes
• Indexed views

Partitioned and non-partitioned tables may be compressed, with partitioned tables allowing further specification of row, page, or none at the individual partition level.

Indexes of compressed tables do not inherit the settings of their parent object.  You must set each index individually.

Setting up compression can be done in a couple of ways.  Like so many things SQL Server related, there is a wizard.  For an existing item, right click the object in Object Explorer and choose "Storage" --> "Manage Compression..." as seen below.

Upon selecting "Manage" the wizard pops up to take you through the process.

The first step in the wizard displays the partitions associated to our object and allows us to set the compression type for each.  Had this example table been partitioned we would have seen details for partition function boundary and a row count.  We also have a handy "Calculate" button to show the compressed space necessary.

For this simple example I want to apply compression to all partitions (all one of them) so I select the smartly named "Use same compression type for all partitions" checkbox and choose from the drop-down list to the right.

The next step of the wizard presents us with options to either script the compression changes, run them immediately, or schedule them (presumably for an off-peak hour when you won't run your instance into a ditch).  I really appreciate the choice given to me here as my needs may change drastically depending on the objects or environment where I happen to be working.

At this point we choose "Next" or "Finish" and receive the output of all our clicking:

USE [ExampleCode]
ALTER TABLE [dbo].[Invoice] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE
)

Executing the query applies compression to the object, with no outward signs of compression to anyone viewing via Object Explorer or Intellisense. 

To find the compression style of an object you can run the following query against the sys.partitions compatibility view:

SELECT * 
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.Invoice', 'U');

The data_compression_desc column will list the compression style.  To get statistics on specific partitions you can query the Dynamic Management Object function sys.dm_index_operational_stats:

SELECT page_compression_attempt_count, page_compression_success_count 
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.Invoice', 'U'), NULL, NULL);

Like all things SQL Server, there are considerations before going out and running wild with compression:

• Compression is available only in Enterprise and Developer editions.
• Compression does not change the maximum row size of a table or index.
• A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes.  This check is done at initial compression and again as rows are inserted or modified.
• You can enable or disable ROW or PAGE compression online or offline. Enabling compression on a heap is single threaded for an online operation.
• The disk space requirements for enabling or disabling row or page compression are the same as for creating or rebuilding an index. For partitioned data, you can reduce the space that is required by enabling or disabling compression for one partition at a time.

Next time...encryption and announcement of another place you can come hear me speak, if you can bear it.  ;)

Mary-Jo Foley is reporting an announcement of August as the RTM date for the hotly awaited release.  Let the online chatter begin...