Peter DeBetta's SQL Programming Blog

OT: Getting Fit in February

Peter DeBetta — Mon, 02 Feb 2009 16:34:15 GMT

A friend and colleague of mine, Caleb Jenkins, has started a fitness challenge (http://calebjenkins.wordpress.com/2009/01/29/official-rules/) and I decided that it was time for me to accelerate my fitness program that I had started in January. As of 9:00 AM on February 1, I weighed in at 201 pounds. My overall and ambitious goal is to lose at least 20 pounds. I hope to lose at least 10 pounds during the month of February. Even if I don’t win the fitness challenge, I will be much better off and will hopefully get to my ultimate goal of staying under 180 pounds.

I worked out on Saturday and Sunday. On Sunday I also helped my 5 year old son attempt to ride his bicycle without training wheels for the first time. My backs hurts from leaning over and catching Chris from falling. I am sore. I am achy. But I am determined. Wish me luck on my road to a healthier lifestyle.

Tweet! SQLblog is now on Twitter!

Peter DeBetta — Fri, 19 Sep 2008 00:42:54 GMT

Are you a twitter tweeter who loves SQL Server? You are?! Great, because SQLblog is now posting (AKA tweeting) on Twitter at http://twitter.com/sqlblog. Enjoy!

Introducing SQL Server 2008 - The Book

Peter DeBetta — Tue, 16 Sep 2008 19:23:51 GMT

It took a while, but a new e-book authored by yours truly, Greg Low, and Mark Whitehorn is finally available. And it's free!

To get your free copy, browse to http://www.microsoft.com/learning/sql/2008/default.mspx. From there, look in the Special Offers section for the "Free e-book offer". Although the site states you can get excerpts, the whole book is available for reading.

Many, many thanks to Greg and Mark for their quality contributions to the book.

PSA: Juvenile Diabetes Research Foundation - Walk to Cure Diabetes

Peter DeBetta — Tue, 16 Sep 2008 15:36:13 GMT

Last year, my 4 year-old son and I walked (well, I carried him for 2 miles) in support of Juvenile Diabetes Research Foundation (JDRF). We raised a modest amount in support of this wonderful effort. This year, we want to help more than ever, and I thought I'd ask the SQLblog community for some additional support.

We are walking in support of our friend Marissa (Marissa's Mermaids team). Marissa was diagnosed in Sept. 2005 with Type 1 Diabetes, since then she has had 4620 finger pricks to draw blood and 1095 injections. Now, she is attached to an insulin pump 24 hours a day. Type 1 (or Juvenile) Diabetes is a chronic, debilitating disease affecting every organ system - it is an autoimmune disease where the body attacks its pancreas and permanently destroys the cells that make insulin. Once Type 1 Diabetes arrives, it becomes a life-long condition.

Like nearly 3 million Americans with Type 1 Diabetes, Marissa has lower projected lifespan by 14 years and is more likely to suffer blindness, amputation, heart disease, kidney disease, nerve damage, stroke, and heart attack. Every year, 13,000 children are diagnosed. INSULIN IS NOT A CURE - IT IS MERELY LIFE SUPPORT. At times when Marissa is most upset, with tears in her eyes, she says, "I wish I could take my diabetes and throw it in the trash can."

To manage Marissa's diabetes, her parents have the impossible struggle of balancing carbohydrates, insulin, and physical activity with uncontrollable variables such as illness, growth, exercise-level, excitement, and stress. Every day offers Marissa the risk of a drastic low blood sugar causing seizure or possibly death. It could only take one mistake on one morning or one afternoon or one night. Or it could simply involve a random event from her body.

Marissa is brave. Her parents poke her finger to draw blood 8-12 times each day including routine checks at midnight and 3am. Every carbohydrate at every meal or snack must be counted. The carbs must be perfectly balanced immediately through injections or an insulin pump to attempt to imitate the way yours and my pancreas works. There are no exceptions and there is never a day off!

JDRF is amazingly efficient - 85% of donations go directly to research.

Major research advances have occurred in the past few years! Diabetes has been cured in mice in at least 4 ways and various human trials are beginning. Scientists legitimately believe a cure is possible before Marissa loses this life-long battle. We ask you to share Marissa's story with your organization so that your entire company would consider sponsoring Marissa. Your contribution might make the difference in the one research laboratory where the cure will be achieved!

So if you are able to donate, please visit http://walk.jdrf.org/index.cfm?fuseaction=extranet.personalpage&confirmid=87065878. Together we can help find a cure!

Policy Based Management Evaluation Modes

Peter DeBetta — Mon, 16 Jun 2008 20:36:00 GMT

Dan Jones wrote a great post about Facets from the new Policy-Based Management feature of SQL Server 2008. At one point in the post, he listed all of the available facets and their supported evaluation modes. Since SQL Server 2008 is not RTM, and since facets can be added in the future, I thought I'd write a query that would list the facets and supported evaluation modes.

Note that the On Demand mode is always supported and has therefore been left out of the query.

;WITH EM (EvalModeID, EvalModeName)
AS
( SELECT *
   FROM
       (VALUES
           (1, 'Check on Change: Prevent'),
           (2, 'Check on Change: Log'),
           (4, 'Check on Schedule')) AS EvalModes (EvalModeID, EvalModeName)
)
, FEM (FacetID, FacetName, EvaluationMode, IsSupported)
AS
( SELECT
       pmf.management_facet_id
       , pmf.name
       , EM.EvalModeName
       , 1
   FROM msdb.dbo.syspolicy_management_facets AS pmf
       INNER JOIN EM ON pmf.execution_mode & EM.EvalModeID = EM.EvalModeID
)
SELECT FacetID
   , FacetName
   , [Check on Change: Prevent]
   , [Check on Change: Log]
   , [Check on Schedule]
FROM FEM
PIVOT
( COUNT(IsSupported)
   FOR EvaluationMode IN ([Check on Change: Prevent], [Check on Change: Log], [Check on Schedule])
)AS FEMP
ORDER BY FacetName

The results for the existing 72 facets are as follows:

FacetID	FacetName	Check on Change: Prevent	Check on Change: Log	Check on Schedule
1	ApplicationRole	1	1	1
2	AsymmetricKey	1	1	1
3	Audit	0	0	1
4	BackupDevice	0	0	1
5	BrokerPriority	0	0	1
6	BrokerService	0	0	1
7	Certificate	0	0	1
8	Credential	0	0	1
9	CryptographicProvider	0	0	1
10	Database	0	0	1
11	DatabaseAuditSpecification	0	0	1
12	DatabaseDdlTrigger	0	0	1
13	DatabaseRole	1	1	1
14	DataFile	0	0	1
15	Default	0	0	1
16	Endpoint	1	1	1
17	FileGroup	0	0	1
18	FullTextCatalog	0	0	1
19	FullTextIndex	0	0	1
20	FullTextStopList	0	0	1
21	IDatabaseMaintenanceFacet	0	0	1
22	IDatabaseOptions	0	1	1
23	IDatabasePerformanceFacet	0	0	1
24	IDatabaseSecurityFacet	0	0	1
25	ILoginOptions	1	1	1
26	IMultipartNameFacet	1	1	1
27	INameFacet	0	0	1
31	Index	0	0	1
32	IServerAuditFacet	0	0	1
33	IServerConfigurationFacet	0	1	1
34	IServerInformation	0	0	1
35	IServerPerformanceFacet	0	0	1
36	IServerSecurityFacet	0	0	1
38	IServerSettings	0	0	1
37	IServerSetupFacet	0	0	1
41	ISurfaceAreaFacet	0	1	1
28	ITableOptions	1	1	1
29	IUserOptions	1	1	1
30	IViewOptions	1	1	1
42	LinkedServer	0	0	1
43	LogFile	0	0	1
44	Login	0	0	1
45	MessageType	0	0	1
46	PartitionFunction	0	0	1
47	PartitionScheme	0	0	1
48	PlanGuide	0	0	1
49	RemoteServiceBinding	0	0	1
50	ResourceGovernor	0	0	1
51	ResourcePool	1	1	1
52	Rule	0	0	1
53	Schema	1	1	1
54	Server	0	0	1
55	ServerAuditSpecification	0	0	1
56	ServerDdlTrigger	0	0	1
57	ServiceContract	0	0	1
58	ServiceQueue	0	0	1
59	ServiceRoute	0	0	1
60	Statistic	0	0	1
61	StoredProcedure	1	1	1
62	SymmetricKey	0	0	1
63	Synonym	0	0	1
64	Table	0	0	1
65	Trigger	0	0	1
66	User	0	0	1
67	UserDefinedAggregate	0	0	1
68	UserDefinedDataType	0	0	1
69	UserDefinedFunction	1	1	1
70	UserDefinedTableType	0	0	1
71	UserDefinedType	0	0	1
72	View	0	0	1
73	WorkloadGroup	1	1	1
74	XmlSchemaCollection	0	0	1

TechEd 2008 Samples

Peter DeBetta — Tue, 10 Jun 2008 18:44:00 GMT

As promised, I am posting my samples from the North America TechEd 2008 Developer Week.

Note: All the samples are designed to run in SQL Server 2008. The safe dynamic sql samples can be modified, however, to work in SQL Server 2005.

SQLblog: New SQL Server Forum is now available

Peter DeBetta — Thu, 08 May 2008 07:01:41 GMT

After numerous questions and requests, we decided to create a forum for all SQL Server related questions. You can view the new forum at http://sqlblog.com/forums/57/ShowForum.aspx or use the Forums menu item on the site to browse there. We hope this new addition to the SQLblog community provides the members a valuable benefit and look forward to seeing your forum posts.

OT: Introducing SQL Server 2008 Book is Content Complete

Peter DeBetta — Wed, 09 Apr 2008 09:54:00 GMT

With some help from two esteemed colleagues, Greg Low and Mark Whitehorn, the Introducing SQL Server 2008 book is now content complete. Sure, there is still some tech and copy editing that needs to be done, but the core writing is complete. The book, based on the Feburary CTP6 release, will be available free from Microsoft and covers a slew of new features including:

Policy-Based Management and Auditing
Transparent Data Encryption and Data and Backup Compression
Resource Governor
Performance Data Collection
New data types including HierarchyID, Filestream, Spatial, Date and Time, and Enhancements to the XML data type
Table Types (and Table valued parameters)
T-SQL Enhancements including Merge, Single statement declaration/assignment, Increment Operators, and GROUPING SETS
Sparse Columns and Filtered Indexes
High Availability Enhancements for Database Mirroring and Failover Clustering
Business Intelligence Enhancements for Integration Services, Reporting Services, and Analysis Services

I'll post again when the book is available for download.

Yet Another Stored Procedure vs. Ad-hoc Query Discussion?

Peter DeBetta — Thu, 03 Apr 2008 04:44:00 GMT

Earlier today, Will Sullivan posted a blog entry, My Statement on Stored Procedures, in which he emphatically states his official opinion of stored procedures as:

"I prefer not to use them."

He then goes about dismissing most of the misinformation about why stored procedures are better than ad-hoc (parameterized) queries.

The first bit of misinformation he dispels is the now defunct argument that "Stored Procedures are faster than ad-hoc queries". He states that "Unless your ad-hoc queries are always significantly different from each other, their execution plans are cached right along side those of the SP's." I completely agree. We'll call that one a tie, so the score so far: SP 0, Ad-hoc 0.

Another myth he tries to debunk is that "Editing SP's is a breeze with Query Analyzer". Query Analyzer - that's so SQL Server 2000. Seriously, though, there are a number of fine code editors that allow you to edit SPs with ease. Query Analyzer is not at the top of that list, however. I will say that when you write T-SQL you should use a code editor that is meant for T-SQL, for the same reasons that when you write C#, you want to use a code editor meant for C#. Again, no winner here, so the score remains: SP 0, Ad-hoc 0.

He addresses another statement that is supposedly made in defense of SPs: "Ad-hoc queries are a nightmare to maintain, as they are spread all over your code". Again, either one is easy to maintain, with the right tools. We are still scoreless: SP 0, Ad-hoc 0.

It just so happens that I agree with many of his points. And there are other objective and subjective points on topics such as organization, maintenance, design, and so on, which one could argue for either SPs or ad-hoc queries equally so. Don't get me wrong, however, as I believe that using ad-hoc queries when you could have used stored procedures is simply wrong.

And so I will address Will's last point (actually, it was his second point) that is repeatedly misrepresented: "Stored Procedures are safe against SQL injection attacks; ad-hoc queries are not".

Ad-hoc queries prevent SQL Injection attacks as well as SPs do. Any claim otherwise would be wrong. But that's not the issue. The problem is that ad-hoc queries require that you expose the underlying objects of the database. In order to use ad-hoc queries, you must allow direct access for select, insert, update, and delete operations to the tables in the database. Although I know most experienced developers would only write ad-hoc/parameterized queries against the underlying data, at a later date, some disgruntled or inexperienced developer may write dynamic SQL instead (I have seen it happen), and expose the database to SQL injection attacks (which I have also seen in production systems), including exposure to such awful actions as...

-- Can you say Identity Theft?
SELECT FirstName, LastName, ZIP, CreditCardNumber, CreditCardType, CreditCardExpoiration, CVV
FROM Customers

...or worse...

-- Do we really need all those customers?
DELETE Customers

...or even worse...

-- NEVER EVER DO THIS, PLEASE
-- This will execute a DELETE against all tables
EXEC sp_MSforeachtable 'DELETE ?'

...or even, even worse (assuming the SQL login has elevated permissions - which many apps do)...

-- NEVER EVER DO THIS, PLEASE
-- This will drop all tables from the database
EXEC sp_MSforeachtable 'DROP TABLE ?'

...and so although your ad-hoc query code won't allow SQL injection, some other programmer's dynamic SQL will. Assuming you've correctly secured your database, this doesn't happen with stored procedures since you do not have to expose any of the underlying tables (because of a little something known as chain of ownership).

Of course, you could completely self-destruct any security benefits by creating a SP such as this one:

-- NEVER EVER DO THIS, PLEASE, I beg of you...
CREATE PROC prExecuteSql (@sql VARCHAR(MAX))
WITH EXECUTE AS dbo
AS
EXEC (@sql)
GO

As you can see, SPs aren't fool proof, but you can mitigate your risk by having an employee or a consultant who knows what they are doing in the database.

Yes, there are some applications will not require the extra security, or other factors may simply prevent you from using stored procedures, and so using ad-hoc SQL is a viable option in those cases. But I believe that security should be at the top of your important-things-for-your-application list, and alas, ad-hoc queries require you to unnecessarily expose your database objects, which will more than likely lead to problems down the road. You can argue any other point and there are no clear winners, but when it comes to security, ad-hoc loses. If you want to a more secure database, you need to be using SPs.

And so, the final score (well, for now anyways): SP 1, Ad-hoc 0.

SQL Server 2008 Leap Year Issue

Peter DeBetta — Fri, 29 Feb 2008 16:42:00 GMT

Microsoft reported:

"We have recently discovered an issue with SQL Server 2008 CTPs that result in SQL Server 2008 not starting or installing on Feb 29 GMT only. We recommend that you do not run, install or upgrade this CTP on Feb 29 GMT to minimize any impact in your environment. You can install starting on March 1 GMT...."

SQL Bracketed Identifiers and Community Server

Peter DeBetta — Thu, 07 Feb 2008 18:20:00 GMT

Bloggers at SQLblog.com were occasionally having issues posting. We had narrowed down the cause to the use of bracketed identifiers with certain text, such as [name] or [date], so that when a post contained a query such as this:

SELECT Description as [name],
, OrderDate as [date]
FROM SomeTable

Attempting to publish it to the blog would simply cause an unhandled exception. The problem has to do with the Community Server TextPart component, which uses bracketed identifiers to replace snippets and for certain post settings when posting. I found a work around, however, which involves making a change to the ScottWater.CS.Modules source code and redeploying. Here is the code change, found in ScottWater.CS.Modules.MetaBlogExtender class, csa_PreProcessPost method:

...
foreach(TextPart tp in e.TextParts)
{
    string name = tp.Name.ToLower();
    /* Start of added code */
    try
    {
        String testValue = tp.Value;
    }
    catch (ArgumentOutOfRangeException)
    {
        break;
    }
    /* End of added code */
    switch (name)
        {
...

Virus Alert: Windows Live Messenger

Peter DeBetta — Tue, 29 Jan 2008 21:44:00 GMT

I received a IM message in Windows Live Messenger from a techie friend earlier today. The message reads (myname replaces my actual email name)

"Hey isnt this YOU?? http://msngallery.ms.funpic.de/viewimage.php?=myname@hotmail.com"

I thought it strange that my friend was offline when this message was sent, but I clicked the link anyways to see what he could be talking about (my ego/curiosity made a bad choice). Next thing I know, I am being prompted to Run or Save a MS-DOS application. Fortunately, I read the dialog and canceled the request.

According to news on the net, some AV programs didn't catch it and many people now have a trojan running on their system.

OT: What will Bill Gates do after he leaves Microsoft?

Peter DeBetta — Mon, 07 Jan 2008 22:06:00 GMT

I laughed, I cried...

http://www.youtube.com/watch?v=v5uw07iEkjU

Bill Gates presents a farewell video at the keynote of the Consumer Electronics Show. (video URL has been changed to YouTube since the MSN video and image are no longer "available".)

TechEd 2008 Abstract Deadline Extension

Peter DeBetta — Thu, 03 Jan 2008 16:28:00 GMT

Just over a month ago, Simon Guest had posted a blog entry about the call for sessions for TechEd 2008. The original deadline was 31 December 2007, but I've just gotten confirmation that this deadline has been extended to 31 January 2008, so if you were thinking of submitting any abstracts, you still have some time to do it.

This year, the format for TechEd is different: It occurs over two weeks, the first being a developer-centric conference (June 3 - 6), and the latter being IT-centric (June 10 - 13). SQL Server topics can obviously be in both, so be sure to choose the correct conference before submitting your abstracts.

To get started, you need to first go to https://2008.msteched.com/cft/. Use TEUS-Speakers as the access code, create a profile, and begin submitting topics.

And good luck!

2008: Converting XML to HIERARCHYID

Peter DeBetta — Sun, 09 Dec 2007 18:39:00 GMT

While working on a new book, I have been experimenting with the new HIERARCHYID data type. And since I've seen comparisons drawn between using XML versus using HIERARCHYID to manage a hierarchy of data, I decided that it would likely be very useful if I could convert an XML structure into an equivalent relational structure using HIERARCHYID. Here is what I came up with:

DECLARE @x XML =
'<A id="1">
<B id="2">
    <C id="3"/>
     <D id="4"/>
</B>
  <E id="5"/>
   <F id="6"/>
</A>';

WITH Folders (ID, ParentID, Description, RowNum) AS
(
    SELECT
        t.c.value('@id', 'int')
        , NULLIF(t.c.value('../@id', 'nvarchar(50)'), '')
        , t.c.value('local-name(.)', 'nvarchar(50)')
        , t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int')
    FROM @x.nodes('//*') AS t(c)
)
, FolderTree AS
(
    SELECT ID, ParentID, Description, RowNum,
        HIERARCHYID::GetRoot() AS FolderNode
    FROM Folders
    WHERE ParentID IS NULL

    UNION ALL

    SELECT F.ID, F.ParentID, F.Description, F.RowNum,
        CAST(FT.FolderNode.ToString() + CAST(F.RowNum AS varchar(50)) + '/'
            AS HIERARCHYID)
    FROM Folders AS F
        INNER JOIN FolderTree AS FT ON F.ParentID = FT.ID
)
SELECT
    ID,
    ParentID,
    Description,
    FolderNode,
    FolderNode.ToString() AS Path
FROM FolderTree
ORDER BY FolderNode;

Which results in:

ID	ParentID	Description	FolderNode	Path
1	NULL	A	0x	/
2	1	B	0x58	/1/
3	2	C	0x5AC0	/1/1/
4	2	D	0x5B40	/1/2/
5	1	E	0x68	/2/
6	1	F	0x78	/3/

The basis of this is the same as if converting an existing adjacency model to use HIERARCHYID – use a combination of a recursive CTE and row numbers partitioned by parent to generate the new path of the node and then cast that path to the HIERARCHYID data type. The parent path is retrieved using the ToString method of the parent node, and this is concatenated with the RowNum value and a forward slash (/) to result in the path for the current node. And finally, the newly generated path is then cast to HIERARCHYID, which uses its static Parse method behind the scenes to create a new instance of HIERARCHYID. This table shows the additional detail and should reveal how the new path is created.

ID	ParentID	Description	FolderNode	Path	Parent Path	RowNum
1	NULL	A	0x	/	*N/A*	1
2	1	B	0x58	/1/	/	1
3	2	C	0x5AC0	/1/1/	/1/	1
4	2	D	0x5B40	/1/2/	/1/	2
5	1	E	0x68	/2/	/	2
6	1	F	0x78	/3/	/	3

On the XML side of things, I generated a partitioned row number by using the nodes method to return all nodes in the XML structure, as shown here: FROM @x.nodes('//*') AS t(c). The double forward slash (//) translates to "relative path" and the asterisk means any node, so this returns all nodes at all levels. Then, to return the row number, I use one of my favorite XQuery examples:

t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int') AS RowNum

This translates to "count the number of immediate child nodes of my parent node that are positioned before me." Now that I have all the nodes, and a partitioned row number for these nodes, I can construct the new path of the node.

I will post more as I continue writing about and exploring SQL Server 2008.

Enjoy!