SQL Server Page Types

There are lots of different page types and Paul Randall list them in his article. However as I tend to have a rather poor memory for remembering them first off here are the ones that can be anywhere within the database.

m_type
1  DATA_PAGE
2  INDEX_PAGE
3 TEXT_MIX_PAGE
4 TEXT_TREE_PAGE
7 SORT_PAGE
10 IAM_PAGE

Now the other ones should be in predictable locations assuming I have the script coded correctly. I have added in a few example page locations, as well as looking in to the suspect pages table as in a restoration scenario only certain pages can be restored using page restore.

SELECT *,
CASE
WHEN [page_id] = 0 THEN 'File Header Page m_type 15'
WHEN [page_id] = 1 OR [page_id] % 8088 = 0 THEN 'PFS m_type 11'
WHEN [page_id] = 2 OR [page_id] % 511232 = 0 THEN 'GAM m_type 8'
WHEN [page_id] = 3 OR ([page_id] - 1) % 511232 = 0 THEN 'SGAM m_type 9'
WHEN [page_id] = 6 OR ([page_id] - 6) % 511232 = 0 THEN 'DCM m_type 16'
WHEN [page_id] = 7 OR ([page_id] - 7) % 511232 = 0 THEN 'BCM m_type 17'
WHEN [page_id] = 9 AND [file_id] = 1 THEN 'Boot Page m_type 13' --DBCC DBINFO WITH TABLERESULTS
WHEN [page_id] = 10 AND DB_ID() = 1 THEN 'config page -> sp_configure settings only present in master m_type 14'
ELSE 'Other'
END [Description],
'DBCC PAGE('''+DB_NAME()+''','+LTRIM(STR(x.[file_id]))+','+LTRIM(STR(x.[page_id]))+',3) WITH TABLERESULTS' [Page]
FROM
(
SELECT 0 [page_id],1 [file_id] UNION ALL -- A File Header Page
SELECT 1 [page_id],1 [file_id] UNION ALL -- A PFS Page
 --SELECT 8088 [page_id],1 [file_id] UNION ALL -- A PFS Page
 --SELECT 16176 [page_id],1 [file_id] UNION ALL -- A PFS Page
SELECT 2 [page_id],1 [file_id] UNION ALL -- A GAM Page
 --SELECT 511232 [page_id],1 [file_id] UNION ALL -- A GAM Page
SELECT 3 [page_id],1 [file_id] UNION ALL -- A SGAM page
 --SELECT 511233 [page_id],1 [file_id] UNION ALL -- A SGAM page
SELECT 6 [page_id],1 [file_id] UNION ALL -- A DCM page
 --SELECT 511238 [page_id],1 [file_id] UNION ALL -- A DCM page
SELECT 7 [page_id],1 [file_id] UNION ALL -- A BCM page
 --SELECT 511239 [page_id],1 [file_id] UNION ALL -- A BCM page
SELECT 9 [page_id],1 [file_id] UNION ALL -- The Boot Page
SELECT [page_id],[file_id] FROM msdb.dbo.suspect_pages
) x

Acknowledgement

This m_type values were gathered from Paul Randall’s blog, and the script works along the same lines as Robert L Davis blog post on tempdb contention.

http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontentionpart2

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/

SQL Server Row Internals

Row Storage

A Row is stored on a Page which is 8192 Bytes, and this is broken down in two key areas when considering physical storage. Firstly on all pages there is a 96 Byte Header. In addition to this we also need to store the Row structure as well as its location in the remainder of the Page.

The location of a Row is stored at the end of the Page working backwards using 2 Bytes per Row. These 2 Byte pairs are generally called the Slot Array. I have seen a few articles/explanations of the Slot Array listing this as 36 Bytes which is incorrect, unless of course you are storing 18 Rows.

Now as the Page Header is always 96 Bytes, if you were to create a table and insert a Row into it; then the earliest point that it could be placed is immediately after the Page Header.

This location immediately after the Page Header would mean that the final 2 Bytes of the Page (Byte 8190 and Byte 8191) would be 60 00 which when reversed becomes 0x60 or 96 as an integer.

If another Row is added then Byte 8188 and Byte 8189 would be used to store the location of this second Row. The order of these 2 Byte pairs within the Slot Array is determined by how they are indexed, not necessarily how they are stored on the Page. That is to say a Row stored towards the front of the Page may or may not appear towards the beginning of the Slot Array which grows from the end of the Page towards the start.

Here is a representation of how a row is stored internally within SQL server using the standard structure.

Desc Bytes
Page Header 96
Status Byte A 1
Status Byte B 1
Null Bitmap Offset 2
Fixed Length Columns X
Column Count 2
Null Bitmap(At least 1 Byte) 1(+1 per 8 Columns)
Variable Length Column Count 2
Variable Length Offset Array 2 per column*
Uniqueifier 4 (if needed)
Variable Length Columns X
Row Version Contains timestamp + pointer to original record 14 bytes
Slot Array 2 bytes per Record/Row/Slot

Now you will see that the fixed length columns are stored towards the start of the structure and then the variable length columns. *I have highlighted that the variable length column array is 2 Bytes per column, however if a column is null then the entry in the Slot Array is not needed unless it has a non null column to its right.

[Location A][Location B][Location C] AAA,BBB,CCC
[Location A][Location B][Location C] AAA,NULL,CCC
[Location A][Location B][Location C] AAA,BBB,NULL

In the first 2 examples all 3 location pointers are needed, however the 3rd example does not require the 3rd location pointer. This raises the interesting point that column order does matter, and that you should keep the variable length columns that have a high probability to  remain NULL after variable lengths columns that are not.

Smallest Row Size

The smallest row size using the above mentioned format would be for a CHAR(1)
+1 Status Byte A
+1 Status Byte B
+2 Null Bitmap Offset
+1 Char(1)
+2 Column Count
+1 Null Bitmap (This is always stored regardless)

This would work out as 8 Bytes for the Row plus 2 Bytes per the Row location in the Slot Array. Now it is worth noting however that this is not the smallest Row size as internally this would be padded by 1 additional Byte. If you store a CHAR(2) then no padding will take place. The reason this happens is because of an optimization which is best explained as follows:

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

Marcel van der Holst – MSFT

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/4e35ba31-748b-4a3b-947d-b56006b47ab5

So although the Row is 8 Bytes it is stored using 9 Bytes. The smallest optimally stored Row therefore is a fixed data type of 2 Bytes.

Most Rows per Page

On that note then if we say that we have a 9 Byte Row Structure + 2 Bytes for the Slot Array to store in the 8096 Bytes (8192-96 Page Header) we can work out the most Rows per Page as follows:

8096/11 = 736 Rows

Acknowledgement

This post builds on information gathered from research, but the factual knowledge was gathered from Paul Randall blog posts.

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/

MCM 2008 Success

I got an email from Microsoft! The lab I took on the 11th April. (Yesterday). I passed!!!

So…. a long time ago in a place….no wait…

I passed the prerequisite and then spent many days with my books and study material to pass the knowledge exam which as I said before was hard. The lab exam itself is hard. No wait it is very hard. The problem I found about the process was not really knowing what to expect with the content. Failing the lab on my first attempt gave me some pointers and areas to improve.  I guess every failure should really teach you something. If you keep doing the same thing and expecting different results… you are very much like my cat.

Anyway…I took the lab yesterday on the 11th (From my home) and on the 12th I have the result. I have passed and am very proud to count myself among fellow MCM holders. I can relax now and absorb 2012 knowledge at SQLBits.

 

SQL Server Partitioning Data Movement

Introduction

This is a continuation to the partitioning overview post to help explain how data movement is performed using Alter Partition with Merge and Split Commands.

Getting Started

In order to move data in or out of a partitioned object the part of the object that needs to be moved must reside on the same file group. In the first post all the partitions were placed on the primary file group. Now if the middle partition P2 was removed using the MERGE syntax this would result in P3 renumbering itself to P2. So to make things easier to follow the next example will create file groups called One,Two,Three and Four which will correspond to the initial partition locations.

The code below is for a RANGE RIGHT partition, but included is the code for a RANGE LEFT.

CREATE DATABASE [MyTest]
GO
USE [master]
GO
ALTER DATABASE [MyTest] ADD FILEGROUP [One]
ALTER DATABASE [MyTest] ADD FILEGROUP [Two]
ALTER DATABASE [MyTest] ADD FILEGROUP [Three]
ALTER DATABASE [MyTest] ADD FILEGROUP [Four]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'One_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\One_File01.ndf')
TO FILEGROUP [One]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'Two_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Two_File01.ndf')
TO FILEGROUP [Two]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'Three_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Three_File01.ndf')
TO FILEGROUP [Three]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'Four_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Four_File01.ndf')
TO FILEGROUP [Four]
GO
USE [MyTest]
CREATE PARTITION FUNCTION pfR(INT)
AS RANGE RIGHT FOR VALUES (100,200,300);
CREATE PARTITION SCHEME psR AS PARTITION pfR TO
([One],[Two],[Three],[Four]);
CREATE TABLE R ([ID] INT) ON psR([ID]);
INSERT INTO R VALUES (NULL)
INSERT INTO R VALUES (-1)
INSERT INTO R VALUES (0)
INSERT INTO R VALUES (1)
INSERT INTO R VALUES (100)
INSERT INTO R VALUES (101)
INSERT INTO R VALUES (200)
INSERT INTO R VALUES (201)
INSERT INTO R VALUES (300)
INSERT INTO R VALUES (301)
INSERT INTO R VALUES (1001)

/*
CREATE PARTITION FUNCTION pfL(INT) AS RANGE LEFT FOR VALUES (100,200,300);
CREATE PARTITION SCHEME psL AS PARTITION pfL TO
([One],[Two],[Three],[Four]);
CREATE TABLE L ([ID] INT) ON psL([ID]);
INSERT INTO L VALUES (NULL)
INSERT INTO L VALUES (-1)
INSERT INTO L VALUES (0)
INSERT INTO L VALUES (1)
INSERT INTO L VALUES (100)
INSERT INTO L VALUES (101)
INSERT INTO L VALUES (200)
INSERT INTO L VALUES (201)
INSERT INTO L VALUES (300)
INSERT INTO L VALUES (301)
INSERT INTO L VALUES (1001)

--DROP TABLE L
--DROP PARTITION SCHEME psL
--DROP PARTITION FUNCTION pfL
--DROP TABLE R
--DROP PARTITION SCHEME psR
--DROP PARTITION FUNCTION pfR
*/

You can check where each row is using the Detailed Table and Index Breakdown script

Merge

Using the example below of a RANGE RIGHT partition what would happen if the secondary boundary was removed.

RightPartition

In order to remove the secondary boundary this command is needed:

ALTER PARTITION FUNCTION pfR() MERGE RANGE(200)

The resultant change is that the partition to the right of the boundary shown below is removed and the data is moved to partition 2. The example has rows in both partitions, but this would also be true if both P2 and P3 had been empty, except no data would have been moved.

RightPartitionMergeBoundary200

But what if instead the values 100 and 101 had not been inserted and P2 was empty?

I need to add a note here to say that the following is true from SQL 2012 with regards to empty partitions, as was highlighted this is not true for versions prior. For previous versions see the following post: http://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/

 

RightPartitionEmptyP2

In this case data is not moved from P3 but instead P2 is removed.

RightPartitionEmptyP2MergeBoundary200

If the merge was on a RANGE LEFT example then it will remove the left partition. The easiest way to think about this is that the partition that satisfies the boundary condition will be removed, unless there are no rows in the other partition that does not satisfy the boundary condition. Hopefully that is clear if you imagine the two sides of your boundary.

If nulls are allowed on the partition column then they will be stored in P1, which is important when considering range left or right. A partition merge on a range right that has nulls in P1 will remove P2, where as a range left will remove P1.

Split

If you want to add partitions you must first specify the next filegroup to use. In order to put back the partition removed from the split above the following code is needed:

Right-

ALTER PARTITION SCHEME psR NEXT USED [Three]
GO
ALTER PARTITION FUNCTION pfR() SPLIT RANGE(200)

Left-

ALTER PARTITION SCHEME psL NEXT USED [Two]
GO
ALTER PARTITION FUNCTION pfL() SPLIT RANGE(200)

Switch

Switching is perhaps the most useful of the movement commands in that you can move data incredibly easily.

If the contents of P2 needed to be moved out we would first need to create a destination table before switching P2. The destination table does not need to be partitioned if we are moving only one source partition. In some cases it maybe easier to create an exact copy of your source structure as then you can move partitions specifying each partition number as needed.

CREATE TABLE R2 ([ID] INT) ON [Two];
GO
ALTER TABLE [dbo].[R] SWITCH PARTITION 2 TO R2

The two rows from P2 of the source table are now present in the destination table and P2 of source is showing as zero rows.

Now to put the rows back…

ALTER TABLE [dbo].[R2] SWITCH TO [dbo].[R] PARTITION 2

ALTER TABLE SWITCH statement failed. Check constraints of source table ‘dbo.R2’ allow values that are not allowed by range defined by partition 2 on target table ‘dbo.R’.

In order for this to work we need to confirm that the data in the R2 table will fit in to P2, which means a constraint. Now P2 should hold values that are greater than or equal to 100, but less than 200. (Nulls are held in P1)

ALTER TABLE [dbo].[R2] ADD CONSTRAINT [PleaseBelieveMe]
CHECK ([ID]>=N'100' AND [ID]<N'200' AND [ID] IS NOT NULL)

The switch will now work correctly and the rows are returned. This process becomes slightly more complicated with indexes but the theory is the same and is explained well in Special Guidelines for Partitioned Indexes.

The only issue that I have had with SWITCH is when conflict detection is enabled on a peer to peer replication, which is logged here on connect.

SQL Server Partitioning Overview

Introduction

This is a guide to help explain partitioning setup; which although introduced way back in SQL 2005 is still an area which throws up a lot of questions.

Requirements

First off you will need Enterprise SQL Server in your production environment, or alternatively you can explore this feature in SQL Developer edition.

Getting Started

In order to partition there are two objects that you will first need to create these are a PARTITION FUNCTION and a PARTITION SCHEME.

Function

The partition function is the first building block that you need and requires that you specify boundaries.

CREATE PARTITION FUNCTION pfL(INT)
AS RANGE LEFT
FOR VALUES (100,200,300);

The syntax is simple and shows that in the above case the function expects an integer and will create boundaries based on a RANGE LEFT strategy. The example shows three boundaries which will mean that there are four partitions. Number of boundaries + 1.

RANGE LEFT means that the boundary points are <= less than or equal. In the example below the first boundary is 100 which means all values less than or equal to 100 will go in to the first partition, those above 100 and less than the next boundary will fit in to partition two (P2) and so on.

The below image shows this and with example values sorted.

LeftPartition

Now obviously having shown RANGE LEFT, here is a RANGE RIGHT example. Note that now the boundaries points are => greater than or equal to. So the value 100 for example is now in the second partition.

RightPartition

Scheme

Before we can apply the partition, a partition scheme needs to be deployed which will identify the file groups to use.

CREATE PARTITION SCHEME psL
AS PARTITION pfL TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

The table below specifies that it will use the partition scheme and will be partitioned on the ID column and to make this example simple all the data is stored on the Primary file group.

CREATE TABLE L ([ID] INT) ON psL([ID])
INSERT INTO L VALUES (NULL)
INSERT INTO L VALUES (-1)
INSERT INTO L VALUES (0)
INSERT INTO L VALUES (1)
INSERT INTO L VALUES (100)
INSERT INTO L VALUES (101)
INSERT INTO L VALUES (200)
INSERT INTO L VALUES (201)
INSERT INTO L VALUES (300)
INSERT INTO L VALUES (301)
INSERT INTO L VALUES (1001)

You can check where each row is using the Detailed Table and Index Breakdown script

Find Uptime / Last Boot Up Time Commands

There are numerous ways to find how long a machine has been powered on for, and below I will list a few of them:

CMD

systeminfo | find "System Boot Time"
net statistics server | find "Statistics since"
net statistics workstation | find "Statistics since"

Powershell

$LastBootUpTime = [System.Management.ManagementDateTimeConverter]::ToDateTime($(gwmi Win32_OperatingSystem).LastBootUpTime)
$d = New-Timespan $LastBootUpTime (Get-Date)
"{0} days {1} hours {2} minutes" -f $d.days,$d.hours,$d.minutes

SQL Events 2013

2013 is shaping up to be a good year for me getting to events, so far penciled in are SQLSaturday in Exeter and SQLBits XI in Nottingham. Hope to see lots of you there… In fact you can even register for SQLSaturday right now. So go do that.

SQLSaturday #194 8th and 9th March 2013 Exeter
http://www.sqlsaturday.com/194/eventhome.aspx

SQLBits XI 2nd to 4th May 2013 Nottingham
http://sqlbits.com/

SQL Object Decryption

Background

The ability to “decrypt” an object in SQL Server be it stored procedure, view, function or trigger is something that I have run into a few times. In this post I will show you how you can “decrypt” any sql object using a simple script and highlight the things to be aware of.

Firstly my annoying use of “air quotes” is something that you would have noticed and that is because when you use “WITH ENCRYPTION” you are actually only obfuscating the object definition. The process to “decrypt” is very straight forward and requires only one pre-requiste the Dedicated Admin Connection (DAC) which I will explain later, but first I want to talk about how this solution came to be needed.

Solution History

When I first needed to decrypt objects for SQL Server 2000 I had always used a solution posted by Joseph Gama which is based on work by shoeboy. Here is a link to that SQL 2000 solution. However when I went to use this on SQL 2005 it no longer worked…

The reason why the SQL 2000 solution no longer functioned was because the encrypted object values were no longer present in the ctext column of the syscomments table. They had been moved to the imageval column of sys.sysobjvalues, which can only be accessed when using the Dedicated Admin Connection (DAC).

The Dedicated Admin Connection (DAC) is a single connection for the administrator of SQL Server to access the instance. This is important because it gives access to objects that are otherwise hidden.

So that is the basic background to this updated solution. I needed to decrypt objects in later version of SQL Server and so I rewrote a solution using the same decryption methods of the original, but using the new location for the encrypted objects defintions. I also wanted to avoid dropping the encrypted object(s) and so changed this to an alter with rollback, as well as adding in the new object types.

I had kept my rewrite to myself for sometime until it came up on the forum in 2010  http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330/ .

I have since come to realize that perhaps this is not as visible as it could be and so I have given it a new home here where hopefully I can get feedback and address any bugs (undocumented features) without resurrecting that post.

How it works

The decryption method works as it has done in all similar versions of this code.

Encrypted Object ^ Template Object ^ Dummy Encrypted Object

For those interested in exactly what is going on please see the XOR_cipher wikipedia entry.

Getting it to work

In order to decrypt objects as I said you will need to access an object that is not available via a standard connection so for this you will need to enable Dedicated Admin Connection (DAC) if not already enabled.

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

Additionally if you are using SQL Express you will need to enable trace flag 7806.

Then with this done open SSMS, choose File, New, Database Engine Query and set your servername to be admin:ServerName

dac

The last step is to change the code lines to reference the object you want the underlying text for and then check the messages tab.

Below is the code:

--http://jongurgul.com/blog/sql-object-decryption/
DECLARE @EncObj VARBINARY(MAX),@DummyEncObj VARBINARY(MAX),@ObjectNameStmTemplate NVARCHAR(MAX)
SET NOCOUNT ON
/*
--You must be using a DAC.
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e
on (ec.[endpoint_id]=e.[endpoint_id])
WHERE e.[name]='Dedicated Admin Connection'
AND ec.[session_id] = @@SPID
*/
USE [master] --change to where your encrypted object resides
DECLARE @object_id INT,@name sysname
SELECT @object_id = [object_id],@name = [name]
FROM sys.all_objects
WHERE name = N'jjj' --<=Either put your object name here or make sure @object_id is set, and that the object it relates to is encrypted.

SELECT TOP 1
 @ObjectNameStmTemplate = [ObjectStmTemplate]
,@EncObj = [imageval]
FROM
(
SELECT
SPACE(1)+
(
CASE WHEN [type] = 'P' THEN N'PROCEDURE'
WHEN [type] = 'V' THEN 'VIEW'
WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION'
WHEN [type] IN ('TR') THEN N'TRIGGER'
ELSE [type]
END
)
+SPACE(1)+QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME(ao.[name])+SPACE(1)+
(
CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS'
WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC'
WHEN [type] IN ('FN') THEN N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END'
WHEN [type] IN ('TF') THEN N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END'
WHEN [type] IN ('IF') THEN N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N'
WHEN [type] IN ('TR') THEN N' ON ' + OBJECT_NAME(ao.[parent_object_id]) + ' WITH ENCRYPTION FOR DELETE AS SELECT 1 N'
ELSE [type]
END
) +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval]
FROM sys.all_objects ao
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND ao.[Object_id] = sov.[objid]
WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF','TR') AND ao.[object_id] = @object_id
UNION ALL
--Server Triggers
SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(st.[name])+SPACE(1)+N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1'
 +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval]
FROM sys.server_triggers st
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND st.[object_id] = sov.[objid] WHERE st.[object_id] = @object_id
--Database Triggers
UNION ALL
SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(dt.[name])+SPACE(1)+N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1'
 +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval]
FROM sys.triggers dt
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND dt.[object_id] = sov.[objid] AND dt.[parent_class_desc] = 'DATABASE' WHERE dt.[object_id] = @object_id
) x([ObjectStmTemplate],[imageval])

--Alter the existing object, then revert so that we have the dummy object encrypted value
BEGIN TRANSACTION
	DECLARE @sql NVARCHAR(MAX)
	SET @sql = N'ALTER'+@ObjectNameStmTemplate
	EXEC sp_executesql @sql
	SELECT @DummyEncObj = sov.[imageval]
	FROM sys.all_objects ao
	INNER JOIN sys.sysobjvalues sov ON sov.[valclass]=1 AND ao.[Object_id]=sov.[objid]
	WHERE ao.[object_id] = @object_id
ROLLBACK TRANSACTION

DECLARE @Final NVARCHAR(MAX)
SET @Final = N''
DECLARE @Pos INT
SET @Pos = 1
WHILE @Pos <= DATALENGTH(@EncObj)/2
BEGIN
	SET @Final = @Final + NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)),@Pos,1))^(UNICODE(SUBSTRING(N'CREATE'+@ObjectNameStmTemplate COLLATE DATABASE_DEFAULT,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1))))
	SET @Pos = @Pos + 1
END

--If the object is small then just print, else print in chunks
IF DATALENGTH(@Final) <= 8000
BEGIN
	PRINT '--SMALL--'
	PRINT @Final
END
ELSE
BEGIN
	PRINT '--BIG--'
	DECLARE @c INT
	SET @c = 0
	WHILE @c <= (DATALENGTH(@Final)/8000)
	BEGIN
		PRINT SUBSTRING(@Final,1+(@c*4000),4000)
		SET @c = @c + 1
	END
END

Get-StringHash and Get-FileHash

Hashing

Here are two of my powershell scripts that provide a quick and easy way to hash either a string or a file using any of the cryptography hash algorithms.

Get-StringHash

#http://jongurgul.com/blog/get-stringhash-get-filehash/
Function Get-StringHash([String] $String,$HashName = "MD5")
{
$StringBuilder = New-Object System.Text.StringBuilder
[System.Security.Cryptography.HashAlgorithm]::Create($HashName).ComputeHash([System.Text.Encoding]::UTF8.GetBytes($String))|%{
[Void]$StringBuilder.Append($_.ToString("x2"))
}
$StringBuilder.ToString()
}

Usage Examples:

Get-StringHash “My String to hash” “MD5”
Get-StringHash “My String to hash” “RIPEMD160”
Get-StringHash “My String to hash” “SHA1”
Get-StringHash “My String to hash” “SHA256”
Get-StringHash “My String to hash” “SHA384”
Get-StringHash “My String to hash” “SHA512”

http://gallery.technet.microsoft.com/scriptcenter/Get-StringHash-aa843f71

Get-FileHash

#http://jongurgul.com/blog/get-stringhash-get-filehash/
Function Get-FileHash([String] $FileName,$HashName = "MD5")
{
$FileStream = New-Object System.IO.FileStream($FileName,[System.IO.FileMode]::Open)
$StringBuilder = New-Object System.Text.StringBuilder
[System.Security.Cryptography.HashAlgorithm]::Create($HashName).ComputeHash($FileStream)|%{[Void]$StringBuilder.Append($_.ToString("x2"))}
$FileStream.Close()
$FileStream.Dispose()
$StringBuilder.ToString()
}

Usage Examples:

Get-FileHash “C:\MyFile.txt” “MD5”
Get-FileHash “C:\MyFile.txt” “RIPEMD160”
Get-FileHash “C:\MyFile.txt” “SHA1”
Get-FileHash “C:\MyFile.txt” “SHA256”
Get-FileHash “C:\MyFile.txt” “SHA384”
Get-FileHash “C:\MyFile.txt” “SHA512”

http://gallery.technet.microsoft.com/scriptcenter/Get-FileHash-83ab0189