John Magnabosco

The Legend of the Filtered Index

2011-10-06T10:52:07Z

Once upon a time there was a big and bulky twenty-nine million row table. He tempestuously hoarded data like a maddened shopper amid a clearance sale. Despite his leviathan nature and eager appetite he loved to share his treasures. Multitudes from all around would embark upon an epiphanous journey to sample contents of his mythical purse of knowledge.

After a long day of performing countless table scans the table was overcome with fatigue. After a short period of unavailability, he decided that he needed to consider a new way to share his prized possessions in a more efficient manner. Thus, a non-clustered index was born. She dutifully directed the pilgrims that sought the table's data - no longer would those despicable table scans darken the doorsteps of this quaint village. and yet, the table's veracious appetite did not wane.

Any bit or byte that wondered near him was consumed with vigor. His columns and rows continued to expand beyond the expectations of even the most liberal estimation. As his rows grew grander they became more difficult to organize and maintain. The once bright and cheerful disposition of the non-clustered index began to dim. The wait time for those who sought the table's treasures began to increase. Some of those who came to nibble upon the banquet of knowledge even timed-out and never realized their aspired enlightenment. After a period of heart-wrenching introspection, the table decided to drop the index and attempt another solution.

At the darkest hour of the table's desperation came a grand flash of light. As his eyes regained their vision there stood several creatures who looked very similar to his former, beloved, non-clustered index. They all spoke in unison as they introduced themselves: "Fear not, for we come to organize your data and direct those who seek to partake in it. We are the filtered index." Immediately, the filtered indexes began to scurry about. One took control of the past quarter's data. Another took control of the previous quarter's data. All of the remaining filtered indexes followed suit. As the nearly gluttonous habits of the table scaled forward more filtered indexes appeared. Regardless of the table's size, all of the eagerly awaiting data seekers were delivered data as quickly as a Jimmy John's sandwich. The table was moved to tears. All in the land of data rejoiced and all lived happily ever after, at least until the next data challenge crept from the fearsome cave of the unknown.

The End.

SQL Server MVP Deep Dives Vol. 2

2011-09-30T02:36:24Z

The confetti and silly hats, which are quintessential standards of the celebration of a new year, had just been freshly packed away when an email popped up in my inbox. It was Kalen Delaney announcing the call for submissions for upcoming the SQL Server Deep Dives Volume 2 book. Potential authors were challenged to write about their area of passion in regard to SQL Server. I jumped at the chance to offer my contribution. To my delight, my chapter made the cut!

The topic of passion that I contributed was on the topic of personally identifiable data and the super powers that the DBA holds in its protection. I titled it Will the real Mr. Smith please stand up?, in honor of the tag line of old To Tell The Truth game show. It can be found as the eleventh chapter of the book within the Database Administration section. It was quite an honor to participate in the authoring of this book and to share these pages with such an esteemed list of SQL Server Gurus.

All royalties from SQL Server Deep Dives Volume 2 will be donated to Operation Smile which is an international children's medial charity. So when you purchase this book, you not only help yourself you also will help a child who suffers from facial deformities start a new life.

For those who will be attending the 2011 PASS Summit on October 11 - 14 in Seattle, Washington, there will be an opportunity to get your hands on a fresh off-the-press copy of this life changing book. There will also be the opportunity to have your copy signed by many of the contributing authors. Others, like myself - who will not make it to the biggest SQL Server event of the year, a copy of this book can be ordered directly from Manning Publishing, Amazon.com and Barnes and Noble shortly after the Summit.

Enjoy!

SQL Saturday and Exploring Data Privacy

2011-06-29T01:28:06Z

I have been highly impressed with the growth of the SQL Saturday phenomenon. It seems that an announcement for a new wonderful event finds its way to my inbox on a daily basis. I have had the opportunity to attend the first of the SQL Saturday's for Tampa, Chicago, Louisville and recently my home town of Indianapolis. It is my hope that there will be many more in my future. This past weekend I had the honor of being selected to speak amid a great line up of speakers at SQL Saturday #82 in Indianapolis. My session topic/title was "Exploring Data Privacy".

Below is a brief synopsis of my session:

Data Privacy in a Nutshell
       - Definition of data privacy
       - Examples of personally identifiable data
       - Examples of Sensitive data

Laws and Stuff
- Various examples of laws, regulations and policies that influence the definition of data privacy
- General rules of thumb that encompasses most laws

Your Data Footprint
       - Who has personal information about you?
       - What are you exchanging data privacy for?
       - The amazing resilience of data
       - The cost of data loss

Weapons of Mass Protection
      - Data classification
      - Extended properties
      - Database Object Schemas
      - An extraordinarily brief introduction of encryption
      - The amazing data professional <-the most important point of the entire session!

The subject of data privacy is one that is quickly making its way to the forefront of the mind of many data professionals. Somewhere out there someone is storing personally identifiable and other sensitive data about you. In some cases it is kept reasonably secure. In other cases it is kept in total exposure without the consideration of its potential of damage to you. Who has access to it and how is it being used? Are we being unnecessarily required to supply sensitive data in exchange for products and services? These are just a few questions on everyone's mind. As data loss events of grand scale hit the headlines in a more frequent succession, the level of frustration and urgency for a solution increases.

I assembled this session with the intent to raise awareness of sensitive data and remind us all that we, data professionals, are the ones who have the greatest impact and influence on how sensitive data is regarded and protected. Mahatma Gandhi once said "Be the change you want to see in the world." This is guidance that I keep near to my heart as I approached this topic of data privacy.

Moving Dates

2011-04-23T23:32:00Z

At the loading dock, hard working individuals load a semi trailer with your new television, stereo system and personal computer. There is a dependency in this process: it is that your favorite electronics store has a loading dock that will be able to accommodate the size of the semi. If the semi were to arrive to the store and the location of receiving deliveries is only the size of a one-car garage it may either result in a refused delivery or a lot of manual work to perform the task of unloading the semi.

This experience is a shared one between semi drivers and database administrators. We pack up data from one database and deliver it to another. Not always does the proverbial loading dock meet the needs of the data that is being delivered. Consider the datetime data type for an example.

The datetime data type is one in which we are all familiar. It has been part of the data type options for quite sometime. The range of dates that can be stored in this data type is from January 1, 1753 through December 31, 9999. The release of SQL Server 2008 a collection of date data types were introduced: date, datetime2, and datetimeoffset; all of which provide an accepted date range from January 1, 0001 through December 31, 9999. The expansion of date range for these new date data types provides compliance with ANSI 92 SQL Standard for the Gregorian calendar. This also presents a challenge when shipping data from a SQL Server 2008 database, which is using the date data type, to a SQL Server 2005 database, which is using the datetime data type.

This situation can be illustrated through the execution of the following code:

DECLARE @Date DATE;
DECLARE @Datetime DATETIME;

SET @Date = '10/24/1492';
SET @Datetime = @Date

The resulting error that occurs is:

The conversion of date data type to a datetime type resulted in an out-of-range value.

The quick and easy reply might be to upgrade the SQL Server 2005 database to SQL Server 2008 and change the data type of the column in question. While this is certainly a valid option, not always is making such changes to the receiving database within our realm of control or the desire of the business.

Data type modifications, such as varchar or numeric could provide a means of resolving this issue, but this conversion does loose some date characteristics that are valuable, such as ordering of dates and the use of date methods like DATEDIFF.

In essence, the resolution of this issue resides in an application of some minor business logic and compromise with the data that is being sent. It could be determined that since the receiving database does not allow October 24, 1492 that it has no functional use for dates preceding January 1, 1753. Therefore, these rows could be filtered out of the data that is being delivered. Another option could be to return NULL or another date in the date column to substitute out of range dates; thus, delivering the row but with a compatible, although less than accurate, data value. An illustration of such a statement would be:

DECLARE @Date DATE;
DECLARE @Datetime DATETIME;

SET @Date = '10/24/1492';
SET @Datetime = (CASE WHEN @Date '1753-01-01' THEN '1753-01-01' ELSE @Date END);

SELECT @Datetime

The latter solution noted above certainly does not settle well for those of us who are concerned with data integrity. It certainly should not be the option executed as your default response, nor should it be implemented without the exclusion of the options noted prior. However, it does offer a solution that could be mutually acceptable depending on the data's use. The key factor here is to work closely with those who are requesting the delivery of data. Before packing up your semi gain an understanding of the dock to which you are delivering. The time taken here could save your driver a load of sweat.

Expanding Influence and Community

2011-01-25T02:29:40Z

When I was just nine years of age my father introduced me to the computer. It was a Radio Shack TRS-80 Color Computer (aka: CoCo). He shared with me the nuances of writing BASIC and it wasn't long before I was in the back seat of the school bus scribbling, on a pad of paper, the code I would later type. My father demonstrated that while my friends were playing their Atari 2600 consoles, I had the unique opportunity to create my own games on the Coco. One of which provided a great friend of mine hours and hours of hilarity and entertainment.

It wasn't long before my father was inviting me to tag along as he drove to the local high school where a gathering of fellow Coco enthusiasts assembled. In these meetings all in attendance would chat about their latest challenges and solutions. They would swap the labors of their sleepless nights eagerly gazing into their green and black screens. Friendships were built and business partners were developed. While these experiences at the time in my pre-teen mind were chalked up to simply sharing time with my father, it had a tremendous impact on me later in life.

This past weekend I attended the Louisville SQL Saturday (#45). It was great to see that there were some who brought along their children. It is encouraging to see fresh faces in the crowd at our monthly IndyPASS meetings. Each time I see the youthful eyes peering from the audience while the finer details of SQL Server is presented, I cannot help but to be transported back to the experiences that I enjoyed in those Coco days. It is exciting to think of how these experiences are impacting their lives and stimulating their minds. Some of these children have actually approached me asking questions about what was presented or simply bragging about their latest discovery in programming.

One of the topics that arose in the "Women in Technology" session in Louisville, which was masterfully facilitated by Kathi Kellenberger, was exploring how we could ignite the spark of interest in databases among the youth. It was awesome to hear that there were some that volunteer their time to share their experiences with students. It made me wonder what user groups could achieve if we were to consider expanding our influence and community beyond our immediate peers to include those who are simply enjoying their time with their father or mother.

SQL Prompt Easter Egg

2011-01-14T03:49:00Z

Having Red Gate's SQL Prompt installed with SQL Server Management Studio has saved me many headaches over the years of its use. It is extremely nice to type in a table name and see not only the column names, but also their data types and identification of primary keys. Another cool feature is the built-in short cut scripts that are included toward the bottom of the suggestion box. An example of these short cut scripts would be to type in the letters cv and then hit enter and the following template for CREATE VIEW will appear:

CREATE VIEW
--WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA
AS
SELECT /* query specification */
-- WITH CHECK OPTION
GO

These scripts are great, and on occasion rather humorous. Recently, I was writing an UPDATE statement that would update a derived and aliased set of data in . An example of such a statement is as follows:

UPDATE y
SET a.[FieldA] = b.[FieldB]
FROM
    (
        SELECT
            a.[FieldA]
            ,b.[FieldB]
        FROM
            [MyTableA] a
            INNER JOIN [MyTableB] b
                ON a.[PKA] = b.[PKB]
    ) y;

Upon typing the UPDATE y portion I hit enter and the expression "A A A A R G H !" appeared resulting in an unexpected burst of laughter. With a dash of curiosity and a pinch of research I discovered that at the bottom of the SQL Prompt suggestion box resides a short cut script called "yell", which is described as "Vent your frustration". Another humorous short cut script is "neo", which is described as "-- I know Kung-Fu". All is required for these to activate is to type the first letter and hit enter. I wonder if there are any undocumented ones?

Embracing Imperfection

2010-12-04T21:55:41Z

The pursuit of perfection is a road on which we often find ourselves traveling. It is an unpaved road filed with pot-holes and ruts that often destroy our stride. The shoulders of this road are lined with the bones and rotting carcasses of well planned projects, solutions and dreams of others who have dared the journey.

Often the choice to engage in this travel is a compulsive one. We can't help but to pack our bags and make the trip. We justify it by equating it to the delivery of a quality product or service. We use our past travels as validation of our worthiness and value. Our shared experience, as tortured pilgrims of perfection, reveals that each odyssey that bewitched us resulted in a stark reminder of the very weaknesses and fears that we were attempting to mollify. The voice of the critic that berated us for the lack of craftsmanship was our own. Although, at the end of the journey our own critical voice was joined by the gnashing of teeth of those who could not reap the fruit of your labor due to its lack of timely delivery.

There is another road in which to travel. It is the pursuit of embracing imperfection. The cost of traveling this route is your contribution to its eternal construction. Each segment is designed uniquely. At times it has the appearance of a patchwork quilt; while other times it is well organized and highly measured. In all cases, its construction has continually advanced and been utilized as each segment was delivered by its architect.

Those who choose to select this spindle of these crossroads crack open the shells of their fears to reveal the vapor that is within. They construct their houses upon these shells. Through their hunger for mastery they wring every drop of nectar from failure and discard its husks to the ditches of this road. Through their efforts the thoroughfare begins to develop a personality of its own, a beautifully human one, rich with the strengths and weaknesses of all of its contributors.

Like many of us, the pursuit of perfection has not served me well. In fact, I would say that it has been more damaging than it has been helpful. While the perfectionist in me occasionally makes its presence known, I consider myself a "recovering perfectionist". It is evident to me that there is immense beauty found in imperfection. I choose to embrace it. It is grounding. It is constructive. It is honest.

Converting Encrypted Values

2010-11-28T03:43:21Z

Your database has been protecting sensitive data at rest using the cell-level encryption features of SQL Server for quite sometime. The employees in the auditing department have been inviting you to their after-work gatherings and buying you drinks. Thousands of customers implicitly include you in their prayers of thanks giving as their identities remain safe in your company's database.

The cipher text resting snuggly in a column of the varbinary data type is great for security; but it can create some interesting challenges when interacting with other data types such as the XML data type. The XML data type is one that is often used as a message type for the Service Broker feature of SQL Server. It also can be an interesting data type to capture for auditing or integrating with external systems. The challenge that cipher text presents is that the need for decryption remains even after it has experienced its XML metamorphosis. Quite an interesting challenge nonetheless; but fear not. There is a solution.

To simulate this scenario, we first will want to create a plain text value for us to encrypt. We will do this by creating a variable to store our plain text value:

-- set plain text value
DECLARE @PlainText NVARCHAR(255);
SET @PlainText = 'This is plain text to encrypt';

The next step will be to create a variable that will store the cipher text that is generated from the encryption process. We will populate this variable by using a pre-defined symmetric key and certificate combination:

-- encrypt plain text value
DECLARE @CipherText VARBINARY(MAX);
OPEN SYMMETRIC KEY SymKey
    DECRYPTION BY CERTIFICATE SymCert
    WITH PASSWORD='mypassword2010';
    SET @CipherText = EncryptByKey
                         (
                          Key_GUID('SymKey'),
                          @PlainText
                         );
CLOSE ALL SYMMETRIC KEYS;

The value of our newly generated cipher text is 0x006E12933CBFB0469F79ABCC79A583--. This will be important as we reference our cipher text later in this post. Our final step in preparing our scenario is to create a table variable to simulate the existence of a table that contains a column used to hold encrypted values. Once this table variable has been created, populate the table variable with the newly generated cipher text:

-- capture value in table variable
DECLARE @tbl TABLE (EncVal varbinary(MAX));
INSERT INTO @tbl (EncVal) VALUES (@CipherText);

We are now ready to experience the challenge of capturing our encrypted column in an XML data type using the FOR XML clause:

-- capture set in xml
DECLARE @xml XML;
SET @xml = (SELECT
            EncVal
            FROM @tbl AS MYTABLE
            FOR XML AUTO, BINARY BASE64, ROOT('root'));

If you add the SELECT @XML statement at the end of this portion of the code you will see the contents of the XML data in its raw format:

<root>
<MYTABLE EncVal="AG4Skzy/sEafeavMeaWDBwEAAACE--" />
</root>

Strangely, the value that is captured appears nothing like the value that was created through the encryption process. The result being that when this XML is converted into a readable data set the encrypted value will not be able to be decrypted, even with access to the symmetric key and certificate used to perform the decryption. An immediate thought might be to convert the varbinary data type to either a varchar or nvarchar before creating the XML data. This approach makes good sense. The code for this might look something like the following:

-- capture set in xml
DECLARE @xml XML;
SET @xml = (SELECT
            CONVERT(NVARCHAR(MAX),EncVal) AS EncVal
            FROM @tbl AS MYTABLE
            FOR XML AUTO, BINARY BASE64, ROOT('root'));

However, this results in the following error:

Msg 9420, Level 16, State 1, Line 26
XML parsing: line 1, character 37, illegal xml character

A quick query that returns CONVERT(NVARCHAR(MAX),EncVal) reveals that the value that is causing the error looks like something off of a genuine Chinese menu. While this situation does present us with one of those spine-tingling, expletive-generating challenges, rest assured that this approach is on the right track. With the addition of the "style" argument to the CONVERT method, our solution is at hand. When dealing with converting varbinary data types we have three styles available to us:

- The first is to not include the style parameter, or use the value of "0". As we see, this style will not work for us.

- The second option is to use the value of "1" will keep our varbinary value including the "0x" prefix. In our case, the value will be 0x006E12933CBFB0469F79ABCC79A583--

- The third option is to use the value of "2" which will chop the "0x" prefix off of our varbinary value. In our case, the value will be 006E12933CBFB0469F79ABCC79A583--

Since we will want to convert this back to varbinary when reading this value from the XML data we will want the "0x" prefix, so we will want to change our code as follows:

-- capture set in xml
DECLARE @xml XML;
SET @xml = (SELECT
            CONVERT(NVARCHAR(MAX),EncVal,1) AS EncVal
            FROM @tbl AS MYTABLE
            FOR XML AUTO, BINARY BASE64, ROOT('root'));

Once again, with the inclusion of the SELECT @XML statement at the end of this portion of the code you will see the contents of the XML data in its raw format:

<root>
<MYTABLE EncVal="0x006E12933CBFB0469F79ABCC79A583--" />
</root>

Nice! We are now cooking with gas. To continue our scenario, we will want to parse the XML data into a data set so that we can glean our freshly captured cipher text. Once we have our cipher text snagged we will capture it into a variable so that it can be used during decryption:

-- read back xml
DECLARE @hdoc INT;
DECLARE @EncVal NVARCHAR(MAX);
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml;

SELECT @EncVal = EncVal
FROM OPENXML (@hdoc, '/root/MYTABLE')
WITH ([EncVal] VARBINARY(MAX) '@EncVal');

EXEC sp_xml_removedocument @hDoc;

Finally, the decryption of our cipher text using the DECRYPTBYKEYAUTOCERT method and the certificate utilized to perform the encryption earlier in our exercise:

SELECT
    CONVERT(NVARCHAR(MAX),
                    DecryptByKeyAutoCert
                         (
                          CERT_ID('AuditLogCert'),
                          N'mypassword2010',
                          @EncVal
                         )
                    ) EncVal;

Ah yes, another hurdle presents itself! The decryption produced the value of NULL which in cryptography means that either you don't have permissions to decrypt the cipher text or something went wrong during the decryption process (ok, sometimes the value is actually NULL; but not in this case). As we see, the @EncVal variable is an nvarchar data type. The third parameter of the DECRYPTBYKEYAUTOCERT method requires a varbinary value. Therefore we will need to utilize our handy-dandy CONVERT method:

SELECT
    CONVERT(NVARCHAR(MAX),
                    DecryptByKeyAutoCert
                         (
                           CERT_ID('AuditLogCert'),
                           N'mypassword2010',
                           CONVERT(VARBINARY(MAX),@EncVal)
                         )
                    ) EncVal;

Oh, almost. The result remains NULL despite our conversion to the varbinary data type. This is due to the creation of an varbinary value that does not reflect the actual value of our @EncVal variable; but rather a varbinary conversion of the variable itself. In this case, something like 0x3000780030003000360045003--. Considering the "style" parameter got us past XML challenge, we will want to consider its power for this challenge as well. Knowing that the value of "1" will provide us with the actual value including the "0x", we will opt to utilize that value in this case:

SELECT
    CONVERT(NVARCHAR(MAX),
                    DecryptByKeyAutoCert
                         (
                          CERT_ID('SymCert'),
                          N'mypassword2010',
                          CONVERT(VARBINARY(MAX),@EncVal,1)
                         )
                    ) EncVal;

Bingo, we have success! We have discovered what happens with varbinary data when captured as XML data. We have figured out how to make this data useful post-XML-ification. Best of all we now have a choice in after-work parties now that our very happy client who depends on our XML based interface invites us for dinner in celebration. All thanks to the effective use of the style parameter.

Databases and Beer

2010-11-13T18:42:00Z

It is a bit of a no-brainer: Include the word "beer" in a subject line of an e-mail or blog post title and you can be certain that it will be read. While there are times this practice might be a ploy to increase readership, it is not the case for this blog post. There is inspiration that can be drawn from other industries to which we, as database professionals, can apply in our industry. In this post I will highlight one of my favorite participants of the brewing industry.

The Boston Beer Company started in the 1970s in Boston, Massachusetts. Others may be more familiar with this company through their Samuel Adams Boston Lager and other various seasonal beers. I am continually inspired by their commitment to mastery of the brewing process to which they evangelize frequently in their commercials. They also are continually in pursuit of pushing the boundaries of beer as we know it while working within traditional constraints.

A recent example of this is their collaboration with Weihenstephan Brewery of Munich, Germany to produce the soon to be released Infinium beer. This beer, while brewed as an ale, is touted as something closer to something like Champaign - all while complying with the Reinheitsgebot. The Reinheitsgebot is also known as the "German Beer Purity Law" which was originated in 1516. This law states that beer is to consist of water, barley, hops and yeast. That's it. Quite a limiting constraint indeed. and yet, The Boston Beer Company pushed forward.

Much like the process of brewing, the discipline of database design and architecture is one that is continually in process and driven by the pursuit of mastery. While we do not have purity laws to constrain us, we have many other types: best practices, company policies, government regulations, security and budgets. Through our fellow comrades, we discuss the challenges and constraints in which we operate. We boil down the principles and theories that define our profession. We reassemble these into something that is complementary to the business needs that we must fulfill. As a result, it is not uncommon to see something amazingly innovative in a small business who is pushing the boundaries of their database well beyond its intended state. It is equally common to see innovation in the use of features available in the more advanced features of databases that are found in large businesses.

The tag line for The Boston Beer Company is: "Take Pride In Your Beer.", I would like to offer an alternative and say "Take Pride In Your Database." So, As you pour your next Boston Lager into a frosted glass, consider those who spend their lives mastering the craft of brewing and strive to interject their spirit into everything that you do as a database professional.

Cheers!

Return to Sender: Identity Values and Triggers

2010-10-02T23:03:00Z

In my previous post I wrote about using the OUTPUT clause of an INSERT statement to obtain the auto-generated identity value of a row. Quickly and easily obtaining the identity value will allow the application to immediately reference the new row or add rows to another table that use the identity value as a foreign key reference.

A common, and simple, example of this use would be a stored procedure that issues an INSERT statement and returns a result set that contains the identity value of the new row. Below is sample code that creates a table and an stored procedure to illustrate this process:

-- creates the table
CREATE TABLE [dbo].[MyTable]
(
[MyID] [int] IDENTITY(1,1) NOT NULL,
[MyColumn] [varchar](50) NOT NULL
) ON [PRIMARY];
GO

-- creates the stored procedure
CREATE PROCEDURE [dbo].[InsertMyTable]
(
    @MyColumn varchar(50)
)
AS
BEGIN
    -- declare table varaible to capture the identity value
    DECLARE @MyTableVar TABLE (MyID INT);
    -- insert the record
    INSERT INTO [dbo].[MyTable]
    ([MyColumn])
    OUTPUT INSERTED.MyID INTO @MyTableVar
    VALUES (@MyColumn);
    -- return the identity value
    SELECT MyID FROM @MyTableVar;
END
GO

-- execute the stored procedure adding a row and returning the new identity value
EXEC [dbo].[InsertMyTable] 'This is the first test row';

All is well and good in regard to this sample; but imagine a more complicated scenario: There are two databases. The first database contained stored procedure that formerly wrote to a table; but as a second database was created there was a need to seamlessly integrate them. The simple use of the OUTPUT clause alone just wasn't enough to meet the need. To accomplish the seamlessness of this integration the table was moved to the second database and a view was created in the first database to simulate the former table's existence. In order to accomplish some other integration functionality, such as creating a Service Broker message, "instead of triggers" were added to the view. To simulate this environment, I have provided the following sample code:

-- rename the original table to simulate the move to another database
EXEC sp_RENAME [MyTable] , 'MyTable2';

-- create a view to represent the prior table
CREATE VIEW [dbo].[MyTable]
AS
    SELECT
        MyID, MyColumn
    FROM
        dbo.MyTable2;
GO

-- create the instead of trigger to write to new table
-- Please note that the "other integration functionality" noted above is not included for simplicity
CREATE TRIGGER [dbo].[MyTable_InsteadOfInsert]
ON [dbo].[MyTable]
INSTEAD OF INSERT
AS
BEGIN
    -- nocount was added so that this insert is not reflected in the records affected
    SET NOCOUNT ON;
    -- declare table varaible to capture the identity value
    DECLARE @MyTableVar TABLE (MyID INT);
    -- insert the record
    INSERT INTO [dbo].[MyTable2]
    ([MyColumn])
    OUTPUT INSERTED.MyID INTO @MyTableVar
    SELECT
        [MyColumn]
    FROM inserted;
    -- return the identity value
    SELECT MyID FROM @MyTableVar;
END
GO

-- modify the InsertMyTable stored procedure so that two sets are not returned
ALTER PROCEDURE [dbo].[InsertMyTable]
(
    @MyColumn varchar(50)
)
AS
BEGIN
    -- insert the record
    INSERT INTO [dbo].[MyTable]
    ([MyColumn])
    VALUES (@MyColumn);
END
GO

-- execute the stored procedure adding a row and returning the new identity value
EXEC [dbo].[InsertMyTable] 'This is the second test row';

After the execution of the InsertMyTable stored procedure, the identity value that was generated in the new table is returned. You can also run the following SELECT statements and see that the view reflects the same records as the new table:

SELECT * FROM dbo.MyTable;
SELECT * FROM dbo.MyTable2;

Recently, I encountered a bit of a "fly in the ointment" when I read the updated MSDN article for "Create Trigger" which states:

"The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. To prevent triggers from returning result sets, set the disallow results from triggers option to 1."

Yowza! In the aforementioned scenario we are depending on the "Instead of trigger" to return the result set from the OUTPUT clause. If the "disallow results from triggers option" is already set to the value of 1 on your instance; you certainly had a head scratching moment with the sample code above and experienced this challenge first-hand.

One solution that I would like to offer would be the following: Create a column in the table that would store a globally unique identifier (GUID) value that is generated by the stored procedure and passed as part of the INSERT statement. Once the new record is inserted, it could be recalled by the GUID to obtain the identity value that is generated. To demonstrate this make the following modifications to our simulated environment:

-- add a new column to the table to capture a GUID value
ALTER TABLE [dbo].[MyTable2]
ADD [IntegrationID] UNIQUEIDENTIFIER NULL;

-- a reference to the new column is added to the view
ALTER VIEW [dbo].[MyTable]
AS
    SELECT
        MyID, MyColumn, IntegrationID
    FROM
        dbo.MyTable2;
GO

-- add the new column to the trigger and remove the use of the OUTPUT clause
ALTER TRIGGER [dbo].[MyTable_InsteadOfInsert]
ON [dbo].[MyTable]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- insert the record
    INSERT INTO [dbo].[MyTable2]
    ([MyColumn],[IntegrationID])
    SELECT
        [MyColumn], [IntegrationID]
    FROM inserted;
END
GO

-- add code to generate the GUID to recall the new record
ALTER PROCEDURE [dbo].[InsertMyTable]
    (
        @MyColumn varchar(50)
    )
    AS
    BEGIN
        -- used to capture the identity value that is returned
        DECLARE @GUID UNIQUEIDENTIFIER;
        SET @GUID = NEWID();
        -- insert the record
        INSERT INTO [dbo].[MyTable]
        ([MyColumn],[IntegrationID])
        VALUES
        (@MyColumn,@GUID);
        -- return identity value
        SELECT [MyID]
        FROM [dbo].[MyTable] with (nolock)
        WHERE [IntegrationID] = @GUID;
    END

-- execute the stored procedure adding a row and returning the new identity value
EXEC [dbo].[InsertMyTable] 'This is the third test row';

After the execution of this modified version of the InsertMyTable stored procedure, the newly generated identity value is returned without the need for the trigger to provide a return set; thus overcoming the reliance on a deprecated trigger feature.

This scenario was one that I recently experienced first hand when integrating an older database that had many dependencies attached to the original version of a specific table. The goal in this integration was to keep the original database as close to untouched as it could be while achieving a near real-time synchronization with the new database. This specific aspect was only a small part of the overall integration solution; but it proved to be an interesting challenge to overcome.

OUTPUT Clause Saves The Day

2010-08-28T18:58:09Z

Frank, the DBA, pecked at his keyboard with feverish rapidity. His tongue pointed from his lips caused his tense breathing to be audible to the entire department. His eyes were glazed with that "don't bother me I am deep in code" look that staged off all but the bravest of co-workers. Amid the array of stored procedures that were spawned in Frank's day of zoning was one that was used to insert a row into a table and return the identity value that was just created so that other calls can utilize the value.

In this stored procedure, Frank utilized the @@IDENTITY system function to derive the new identity value:

CREATE PROCEDURE [dbo].[InsertMyTable]
    (
        @ColumnValue [varchar] (50)
    )
AS
BEGIN

-- insert new record

    INSERT INTO [Demo].[dbo].[MyTable]
           (
            [MyColumn]
            , [MyDate]
           )
     VALUES
           (
            @ColumnValue
            , GETDATE()
            );

-- declare variable to return
DECLARE @id BIGINT;

-- set the value of the variable to @@IDENTITY
SET @id = @@IDENTITY;

-- return the new identity value
SELECT @id;

END
GO

In his unit testing he executed the following command and was satisfied that it was functioning as expected:

EXEC [dbo].[InsertMyTable] 'MyTestValue';

Although, later he was confounded by the fact that the stored procedure did not consistently return the identity value when utilized in conjunction with the application development environment. Frank scanned the Internet like a whale hunting for plankton. He read blogs, forums, technical articles and flipped through his mountain of books and magazines that somehow supported a rattling cage filled with hamsters at its peak. He could not identify any great explanation of why this inconsistency exists.

He experimented with the alternative system functions of SCOPE_IDENTITY and IDENT_CURRENT to no avail. Frustrated, he wandered out of the office in a daze, scratching his head and having a fierce conversation to himself - which subsequently frightened and cut short the smoke breaks of all that were hanging around the exit door of the office building.

It wasn't long before Frank found himself at the neighborhood pub sitting with his good friend and fellow data-geek, Kyle. While everyone else at the pub were ruing their latest blunder with their significant others, Frank and Kyle were commiserating about their latest coding challenges. With a significant air of resignation, Frank described his conundrum with @@IDENTITY - complete with dramatic use of waving hands and sound effects. Kyle, with cool confidence, took a sip of his tall glass of Sam Adams and said "Did you try the output clause of the INSERT statement?" - All drew quiet.

Frank's eyes lit up like a neon sign in the dark. It was as if the weight of the world was lifted from his shoulders. Frank swigged down the remainder of his beverage, patted Kyle on the shoulder and said "Order me another, I will be right back!". Frank raced back to the office so quickly he was not sure that he actually opened the door. He quickly revised his stored procedure:

CREATE PROCEDURE [dbo].[InsertMyTable]
    (
        @ColumnValue [varchar] (50)
    )
AS
BEGIN

    -- declaration of table variable
    DECLARE @MyTableVar TABLE (IdentityColumn BIGINT);

    -- performs the insert
    INSERT INTO [Demo].[dbo].[MyTable]
           (
            [MyColumn]
            , [MyDate]
           )
     OUTPUT INSERTED.IdentityColumn INTO @MyTableVar
     VALUES
           (
            @ColumnValue
            , GETDATE()
            );

-- returns the new identity value as a dataset
SELECT IdentityColumn FROM @MyTableVar;

END
GO

Further testing proved that Frank's modification was effective and reliable. This moment of enlightenment was so inspiring that the cleaning lady of the office was later heard describing something that looked like a halo was emitting from Frank's head. With a hop and a kick of his heels, Frank sprinted back to the pub and, in his great appreciation, bought his friend Kyle a steak dinner. At that moment, Frank was deeply grateful for his network of friends and a deeper appreciation for the subtleties of Transact-SQL. Although, the behavior of @@IDENTITY still baffles him, he hasn't lost any sleep over it.

The Database Artist

2010-07-31T21:04:53Z

This past Friday I journeyed to the local bookstore and picked up a copy of Seth Godin's latest book titled "Linchpin". This book explores the concept of work and how we can become truly valuable. In this book Seth encourages us to become artists; to recognize the difference between doing a job and creating art.

The book offers a simple definition of art: "Art is a personal gift that changes the recipient." He further explains that the essence of art is not a measurement of a person's skills or the quality of the end result; but in the gift and change aspect. This prompted me to think about how I and others approach our roles as database professionals.

Majority of us were hired as DBAs (or the many variants of that role) with specific tasks and responsibilities in mind. If asked what we are paid for many of us would have no problem identifying these items. This would be what we define as our "job". We were hired to protect the confidentiality, integrity and accessibility of the data that is stored within the many databases throughout our companies. We were hired to backup these same databases and restore them when needed. We were hired to maximize and preserve the performance of these databases. We were hired to architect and build data storage systems to which data is efficiently maintained.

In general, the grand majority of us fulfill our job with a high level of skill, effectiveness and consistently. It is what is expected when our employers cut that paycheck with our name on the "pay to the order of" line. It is when we exceed our obligatory fulfillment of duty in a fashion that changes those around us to which we truly become valuable. It is when these activities are sprung from our passion rather than through expectation of financial or positional reciprocation that we discover the artist in ourselves.

Imagine Frank, the DBA, being invited to a Marketing project meeting. It is clear that he has been invited specifically for their understanding of the existing databases and how the data that they need to complete the project can be gleaned. Frank confidently and competently answers their questions. Upon the meeting's conclusion the Marketing department is satisfied. Frank met their expectations and the project was completed as it was originally planned.

Now Imagine Chuck, the DBA, being invited to the same Marketing project meeting. He too was invited for his understanding of the databases and how the data can be gleaned to complete the project. Chuck confidently and competently answered their questions. In addition, Chuck identifies an overlooked aspect of the project and presents the issue in non-technical terms. The Marketing department is elated. The project, and the business, was changed for the better due to Chuck's contribution.

What differentiated Frank from Chuck? Chuck regularly takes the time to visit the Marketing department. He gets to know the employees of the department, the tasks that they perform and the challenges that they face. Through his semi-casual conversations with the Marketing Director he learns of the department's visions and constraints. Not only is Chuck a more knowledgeable DBA because of these practices, he has established a trusting collaborative relationship. While Frank performs his job very well, Chuck masterfully performs his art.

Albert Einstein once said "Try not to become a man of success, rather try to become a man of value." I believe that this quote expresses the gist of Seth Godin's message through his excellent book. Are we satisfied with the letters DBA meaning "Database Administrator" or should it be "Database Artist"? In the age of the expendable employee I contend that it is a critical change in title.

EncryptByKey in Large Quantities

2010-07-04T23:56:14Z

Most publications that discuss encryption, including my own book, demonstrate the use of the EncryptByKey function as follows:

-- Opens the symmetric key with certificate
OPEN SYMMETRIC KEY SampleSymKey
DECRYPTION BY CERTIFICATE SampleCertificate
WITH PASSWORD = 'MyStr0ngP@ssw0rD';

-- Perform encryption on plain text value and capture in a variable
DECLARE @EncVal varbinary(max);
SET @EncVal = EncryptByKey(Key_GUID('SampleSymKey'), 'TestValue');

-- Use the encrypted value that is captured in the variable
INSERT INTO dbo.SampleEncTable (PlainVal, EncVal)
VALUES ('TestValue', @EncVal);

-- Close the symmetric key
CLOSE SYMMETRIC KEY SampleSymKey;
GO

Execute a query that utilizes the DecryptByKeyAutoCert and you can see that the EncryptByKey function performs very well:

SELECT
    CONVERT(varchar,DECRYPTBYKEYAUTOCERT(
                Cert_ID('SampleCertificate'),
                N'MyStr0ngP@ssw0rD',
                EncVal)
     ) as DecryptedVal
FROM
    dbo.SampleEncTable;
GO

The challenge with these examples are that, while they effectively demonstrate the functionality of these cryptographic functions, they represent only the encryption and decryption of a singular value.

If you were faced with a mass encryption effort, such as the conversion of a legacy database containing plain text sensitive data to a new database in which the converted sensitive data is encrypted, the utilization of a cursor to perform the above sample for each record is very tempting and excruciatingly expensive. Below is a sample of such an attempt:

-- Opens the symmetric key with certificate
OPEN SYMMETRIC KEY SampleSymKey
   DECRYPTION BY CERTIFICATE SampleCertificate
   WITH PASSWORD = 'MyStr0ngP@ssw0rD';

-- Creating a cursor (oooh, you desperate coder, you. ;D)
DECLARE @LegVal varchar(9);
DECLARE EncCur CURSOR
    FOR SELECT
            LegacyVal
        FROM
            dbo.SampleLegacyTable;

-- Open Cursor (screeeeeeech)
OPEN EncCur;
FETCH NEXT FROM EncCur INTO @LegVal;

-- Loop through cursor (mommy, I want off this ride!)
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Perform encryption on plain text value and capture in a variable
    DECLARE @EncVal varbinary(max);
    SET @EncVal = EncryptByKey(Key_GUID('SampleSymKey'), @LegVal);

    -- Use the encrypted value that is captured in the variable
    INSERT INTO dbo.SampleEncTable (PlainVal, EncVal)
    VALUES ('TestValue', @EncVal);

    FETCH NEXT FROM EncCur INTO @LegVal;
END

-- Close Cursor (bang!)
CLOSE EncCur;
DEALLOCATE EncCur;

-- Close the symmetric key
CLOSE SYMMETRIC KEY SampleSymKey;
GO

Cursors are a procedural programming concept which deals with data in a row-by-row method. One row is retrieved and processed at a time. T-SQL is a set based, or declarative, programming language and works best when set based commands are used to process data. An example of using the EncryptByKey function in a set based fashion, and without the dreaded cursor, the statement would appear something like the following:

-- Opens the symmetric key with certificate
OPEN SYMMETRIC KEY SampleSymKey
   DECRYPTION BY CERTIFICATE SampleCertificate
   WITH PASSWORD = 'MyStr0ngP@ssw0rD';

-- Encrypt legacy data as a set (zing!)
INSERT INTO dbo.SampleEncTable
    (
        PlainVal,
        EncVal
    )
    SELECT
        LegacyVal,
        EncryptByKey(Key_GUID('SampleSymKey'), LegacyVal)
    FROM
        dbo.SampleLegacyTable;

-- Close the symmetric key
CLOSE SYMMETRIC KEY SampleSymKey;
GO

With the use of the above example, your legacy data has found its new home in a highly efficient manner. The sensitive data has been encrypted and is ready to be disclosed is a safe manner.

What If TDE Was Available In Standard Edition?

2010-06-21T04:30:26Z

Imagine a database that contained information about a company's customers. Many employees depend upon this information to perform their daily duties. This information flows into this database like a waterfall of data. Reports are pulled to gain insight on the latest product sales. Automated processes kick off on regular intervals to feed other databases or send the information to a third party.

Getting Sensitive
Among this information resides pieces of data that is considered sensitive. A customer's credit card number that was used to order the latest widget, a customer's federal identification number used for obtaining their credit rating to qualify for reoccurring payments, a customer's bank account and routing number to process a check payment for their order. This is data that you can find just about anywhere in the data landscape around the world.

There Are No Small Businesses, Just Small Budgets
Now imagine that this database contains less than 2 million records. The company itself is rather small; but keeping itself in the black, financially, on a regular basis. The data mining activities at this company primarily consist of basic queries and reports. The size of the data file is hardly worth the consideration of data compression. Performance is average and acceptable; but it's primary performance bottleneck resides in the processing power of the glorified desktop that resides in a closet. Hardly the candidate for purchasing a license of SQL Server 2008 Enterprise Edition.

The Incredible Disappearing Backup Tape
The day ends and everyone packs away their day's work to begin their travels toward their loved ones, be it family, friends or beer. One employee who is in arm's reach of the latest backup tape throws it into their bag and leaves the building. At that point the sensitive data contained on that tape can easily be restored into another instance and gleaned for dastardly purposes. This threat is not unique to large businesses with enterprise applications.

TDE To The Rescue
Transparent Data Encryption (TDE) is a security feature that was designed to aid in the protection of sensitive data by encrypting the physical files of a database. This level of protection makes a database restoration on another instance extremely difficult without the appropriate key hierarchy in place. Unfortunately, this feature is not available in SQL Server 2008 Standard Edition.

Imagine If You Will
What if TDE was available in Standard Edition? The soon to be ex-employee who has stolen the backup tape will make the attempt to restore the database. Instead of getting a slew of credit card numbers they receive an error. The company is protected, the customers are protected. Our global data environment is a much safer place.

You shouldn't have to be a large company with a large budget to create a secure environment. TDE should be considered a basic security feature and therefore included in Standard Edition. Not to mention that it would also offer a slight advantage over the competition. Enterprise Edition features have trickled down to Standard Edition as recently as SQL Server 2008 R2 with the backup compression feature. It is my hope that TDE will soon follow suit.

Database Owner Conundrum

2010-06-05T13:30:14Z

Have you ever restored a database from a production environment on Server A into a development environment on Server B and had some items, such as Service Broker, mysteriously cease functioning? You might want to consider reviewing the database owner property of the database.

The Scenario
Recently, I was developing some messaging functionality that utilized the Service Broker feature of SQL Server in a development environment. Within the instance of the development environment resided two databases: One was a restored version of a production database that we will call "RestoreDB". The second database was a brand new database that has yet to exist in the production environment that we will call "DevDB". The goal is to setup a communication path between RestoreDB and DevDB that will later be implemented into the production database.

After implementing all of the Service Broker objects that are required to communicate within a database as well as between two databases on the same instance I found myself a bit confounded. My testing was showing that the communication was successful when it was occurring internally within DevDB; but the communication between RestoreDB and DevDB did not appear to be working.

Profiler to the rescue
After carefully reviewing my code for any misspellings, missing commas or any other minor items that might be a syntactical cause of failure, I decided to launch Profiler to aid in the troubleshooting. After simulating the cross database messaging, I noticed the following error appearing in Profiler:

An exception occurred while enqueueing a message in the target queue. Error: 33009, State: 2. The database owner SID recorded in the master database differs from the database owner SID recorded in database '[Database Name Here]'. You should correct this situation by resetting the owner of database '[Database Name Here]' using the ALTER AUTHORIZATION statement.

Now, this error message is a helpful one. Not only does it identify the issue in plain language, it also provides a potential solution. An execution of the following query that utilizes the catalog view sys.transmission_queue revealed the same error message for each communication attempt:

SELECT
    *
FROM
    sys.transmission_queue;

Seeing the situation as a learning opportunity I dove a bit deeper.

Reviewing the database properties
The owner of a specific database can be easily viewed by right-clicking the database in SQL Server Management Studio and selecting the "properties" option. The owner is listed on the "General" page of the properties screen. In my scenario, the database in the production server was created by Frank the DBA; therefore his server login appeared as the owner: "ServerName\Frank". While this is interesting information, it certainly doesn't tell me much in regard to the SID (security identifier) and its existence, or lack thereof, in the master database as the error suggested.

I pulled together the following query to gather more interesting information:

SELECT
    a.name
    , a.owner_sid
    , b.sid
    , b.name
    , b.type_desc
FROM
    master.sys.databases a
    LEFT OUTER JOIN master.sys.server_principals b
        ON a.owner_sid = b.sid
WHERE
    a.name not in ('master','tempdb','model','msdb');

This query also helped identify how many other user databases in the instance were experiencing the same issue. In this scenario, I saw that there were no matching SIDs in server_principals to the owner SID for my database. What login should be used as the database owner instead of Frank's? The system stored procedure sp_helplogins will provide a list of the valid logins that can be used. Here is an example of its use, revealing all available logins:

EXEC sp_helplogins;

Fixing a hole
The error message stated that the recommended solution was to execute the ALTER AUTHORIZATION statement. The full statement for this scenario would appear as follows:

ALTER AUTHORIZATION ON DATABASE:: [Database Name Here] TO [Login Name];

Another option is to execute the following statement using the sp_changedbowner system stored procedure; but please keep in mind that this stored procedure has been deprecated and will likely disappear in future versions of SQL Server:

EXEC dbo.sp_changedbowner @loginname = [Login Name];

.And They Lived Happily Ever After
Upon changing the database owner to an existing login and simulating the inner and cross database messaging the errors have ceased. More importantly, all messages sent through this feature now successfully complete their journey. I have added the ownership change to my restoration script for the development environment.