White Chicks And Gang Signs

What's New in Windows Vista SP1

Security
Provides security software vendors a more secure way to communicate with Windows Security Center.

Includes application programming interfaces (APIs) by which third-party security and malicious software detection applications can work with kernel patch protection on x64 versions of Windows Vista. These APIs help ISVs develop software that extends the functionality of the Windows kernel on x64 computers without disabling or weakening the protection offered by kernel patch protection.

Improves the security of running RemoteApp programs and desktops by allowing Remote Desktop Protocol (RDP) files to be signed. Customers can differentiate user experiences based on publisher identity.

Adds an Elliptical Curve Cryptography (ECC) pseudo-random number generator (PRNG) to the list of available PRNGs in Windows Vista.

Enhances BitLocker Drive Encryption (BDE) to offer an additional multifactor authentication method that combines a key protected by the Trusted Platform Module (TPM) with a Startup key stored on a USB storage device and a user-generated personal identification number (PIN).


Read the rest here: http://windowsconnected.com/blogs/joshs_blog/archive/2007/08/29/what-s-new-in-windows-vista-sp1.aspx

Download LINQPad

Can't wait for C# 3.0 and LINQ? Well you don't have to! Dynamically query SQL databases today in LINQ: no more struggling with antiquated SQL. Download LINQPad and kiss goodbye to SQL Management Studio: LINQPad supports LINQ to objects, LINQ to SQL and LINQ to XML—in fact, everything in C# 3.0 and .NET Framework 3.5. LINQPad is also a terrific learning tool for experimenting with this exciting new technology.

No installation required: LINQPad ships as a simple "click and run" self-updating 300KB executable. Instant LINQ!


Download it here: http://www.albahari.com/linqpad.html

Five Ways To Return Values From Stored Procedures

I have answered a bunch of questions over the last couple of days and some of them had to do with returning values from stored procedures
Everyone knows that you can return a value by using return inside a stored procedure. What everyone doesn't know is that return can only be an int data type
So how do you return something that is not an int (bigint, smallint etc etc) datatype
Let's take a look
We will start with a regular return statement, everything works as expected

--#1 return
CREATE PROCEDURE TestReturn
AS
SET NOCOUNT ON

DECLARE @i int
SELECT @i = DATEPART(hh,GETDATE())
RETURN @i
SET NOCOUNT OFF
GO

DECLARE @SomeValue int
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO


Now let's try returning a varchar

ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON

DECLARE @i VARCHAR(50)
SELECT @i = DATENAME(mm,GETDATE())
RETURN @i
SET NOCOUNT OFF
GO

DECLARE @SomeValue VARCHAR(50)
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO

Oops, it doesn't work the following message is returned (if you run it in September)
Server: Msg 245, Level 16, State 1, Procedure TestReturn, Line 7
Syntax error converting the varchar value 'September' to a column of data type int.

Let's try hard coding a character value
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
RETURN 'ab'
SET NOCOUNT OFF
GO


DECLARE @SomeValue VARCHAR(50)
EXEC @SomeValue = TestReturn
SELECT @SomeValue
GO

It is interesting that the procedure compiles without a problem. But when we try to run it the following message is displayed


Server: Msg 245, Level 16, State 1, Procedure TestReturn, Line 7
Syntax error converting the varchar value 'ab' to a column of data type int.


So what can we do? well we can use an OUTPUT parameter. By the way the following 4 ways to return a varchar values are in the order from best to worst

--#2 OUTPUT
ALTER PROCEDURE TestReturn @SomeParm VARCHAR(50) OUTPUT
AS
SET NOCOUNT ON
SELECT @SomeParm = 'ab'
SET NOCOUNT OFF
GO


DECLARE @SomeValue VARCHAR(50)
EXEC TestReturn @SomeParm = @SomeValue OUTPUT
SELECT @SomeValue
GO


Another way is to create a temp table and call the proc with insert..exec

--#3 Insert Into TEMP Table outside the proc
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
SELECT 'ab'
SET NOCOUNT OFF
GO

DECLARE @SomeValue VARCHAR(50)
CREATE TABLE #Test(SomeValue VARCHAR(50))
INSERT INTO #Test
EXEC TestReturn
SELECT @SomeValue = SomeValue
FROM #Test

SELECT @SomeValue
DROP TABLE #Test
GO


This one is almost the same as the previous example, the only difference is that ther insert happens inside the proc
And of course if you call the proc without creating the table you will get a nice error message

--#4 Insert Into TEMP Table inside the proc
ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
INSERT INTO #Test
SELECT 'ab'
SET NOCOUNT OFF
GO


DECLARE @SomeValue VARCHAR(50)
CREATE TABLE #Test(SomeValue VARCHAR(50))
EXEC TestReturn
SELECT @SomeValue = SomeValue
FROM #Test

SELECT @SomeValue
DROP TABLE #Test


And last you create a permanent table with an identity, in the proc you insert into that table and you return the identity value. You can then use that identity value to get the varchar value

--#5 Insert Into A Table And Return The Identity value
CREATE TABLE HoldingTable(ID INT IDENTITY,SomeValue VARCHAR(50))
GO

ALTER PROCEDURE TestReturn
AS
SET NOCOUNT ON
DECLARE @i INT

INSERT INTO HoldingTable
SELECT 'ab'
SELECT @I = SCOPE_IDENTITY()

RETURN @i
SET NOCOUNT OFF
GO

DECLARE @SomeValue VARCHAR(50), @i INT
EXEC @i = TestReturn
SELECT @SomeValue = SomeValue
FROM HoldingTable
WHERE ID = @i

SELECT @SomeValue



DROP PROCEDURE TestReturn

ASP.NET AJAX in Action

Title: ASP.NET AJAX in Action
Authors: Alessandro Gallo, David Barkol, and Rama Krishna Vavilala
Published: August, 2007
Pages: 576 pages
ISBN: 1-933988-14-2


Ajax revolutionized how users interact with web pages. Gone are frustrating page refreshes, lost scroll positions, intermittent interactions, and flat, boring pages. Instead we have a new generation of fast, rich, and intuitive web applications. The ASP.NET AJAX framework puts the power of Ajax into the hands of Microsoft ASP.NET developers. ASP.NET AJAX, formerly called Atlas, is a new free framework from Microsoft designed to easily add Ajax features to ASP.NET applications. With this technology, ASP.NET developers can easily build more interactive and highly-personalized web applications that work across all most popular browsers.

ASP.NET AJAX in Action is a fast-paced, example-rich tutorial designed for ASP.NET web developers and written by ASP.NET AJAX experts Alessandro “Garbin” Gallo, David Barkol, and Rama Krishna Vavilala. This book introduces you to Ajax applications and to the ASP.NET AJAX technology. Beginners will appreciate the clear explanations of key ideas and terminology. Intermediate and advanced ASP.NET developers will find a no-nonsense learning source and well-organized reference.

ASP.NET AJAX in Action offers a rich set of examples and meticulous explanations. The extensive code samples are accompanied by accurate and rigorous explanations of the concepts behind development with ASP.NET AJAX. In this book, you will discover how to use

Microsoft Ajax Library
Partial rendering with UpdatePanels
Advanced client and server techniques
Ajax Control Toolkit

If you are a web developer looking to bring your web pages to life and to enhance the user experience, this book is for you.

ASP.NET AJAX in Action will give you with the knowledge and tools you need to more easily craft the next generation of Ajax applications. With the help of the Microsoft ASP.NET AJAX framework, Ajax development has never been easier and more instinctive for both client-script developers and ASP.NET developers alike.

WHAT'S INSIDE:
Lots of code examples
Deep coverage of ASP.NET AJAX Extensions
Covers the ASP.NET AJAX Futures CTP
Partial page rendering with UpdatePanels
Advanced client and server techniques

About the Authors
Alessandro “Garbin” Gallo is a Microsoft MVP in the Visual ASP/ASP.NET category and has been an active contributor for the Ajax Control Toolkit project. As a .NET developer/consultant with a primary focus on ASP.NET application design and development, Alessandro has been developing with ASP.NET AJAX since the very first CTP. Notably, he won the Grand Prize at the Mash-it-up with ASP.NET AJAX contest held by Microsoft in 2006.

David Barkol is a Principal Consultant for Neudesic, one of Microsoft's leading .NET professional services firms and a Gold Certified Partner. At Neudesic David specializes in providing custom .NET solutions that leverage technologies such as ASP.NET, Web Services, Windows Forms, SQL Server, and C#. He is an MCSD in .NET and a member of the Microsoft ASP.NET Advisory Council. David resides in tropical La Palma, CA.

Rama Krishna Vavilala is a senior software developer/architect at 3C Software, a leading supplier of Cost Management Solutions. He has designed and developed three different versions of Impact:ECS™ (3C Softwares product suite). Currently, he is designing an Ajax-based web application using ASP.NET AJAX. This application will be a part of the Impact:ECS™ suite. He is a regular contributor at The Code Project and has contributed around 20 articles on wide ranging subjects


Table Of Contents

Part 1 ASP.NET AJAX basics
1 Introducing ASP.NET AJAX
1.1 What is Ajax?
1.2 ASP.NET AJAX architecture
1.3 ASP.NET AJAX in action
1.4 Summary

2 First steps with the Microsoft Ajax Library
2.1 A quick overview of the library
2.2 The Application model
2.3 Working with the DOM
2.4 Making development with JavaScript easier
2.5 Summary

3 JavaScript for Ajax developers
3.1 Working with objects
3.2 Working with JSON
3.3 Classes in JavaScript
3.4 Understanding inheritance
3.5 Understanding interfaces and enumerations
3.6 Using type reflection
3.7 Working with events
3.8 Summary

4 Exploring the Ajax server extensions
4.1 Ajax for ASP.NET developers
4.2 Enhancing an existing ASP.NET site
4.3 ScriptManager: the brains of an Ajax page
4.4 Partial-page updates
4.5 Summary

5 Making asynchronous network calls
5.1 Working with ASP.NET Web Services
5.2 The asynchronous communication layer
5.3 Consuming external Web Services
5.4 Using ASP.NET application services
5.5 Summary

6 Partial-page rendering with UpdatePanels
6.1 With great power comes great responsibility
6.2 Getting to know the UpdatePanel
6.3 Triggers
6.4 Advanced techniques
6.5 Live GridView filter
6.6 Summary

Part 2 Advanced techniques
7 Under the hood of the UpdatePanel
7.1 The PageRequestManager: the unsung hero
7.2 A client-side event viewer
7.3 UpdatePanel cookbook
7.4 Caveats and limitations
7.5 Summary

8 ASP.NET AJAX client components
8.1 The client component model
8.2 Working with client components
8.3 Behaviors
8.4 Controls
8.5 Summary

9 Building Ajax-enabled controls
9.1 Script descriptors
9.2 Introduction to Ajax-enabled controls
9.3 Extenders
9.4 Script controls
9.5 Summary

10 Developing with the Ajax Control Toolkit
10.1 A world of extenders
10.2 The Ajax Control Toolkit API
10.3 Animations
10.4 Summary

Part 3 ASP.NET AJAX Futures
11 XML Script
11.1 XML Script basics
11.2 Actions
11.3 Bindings
11.4 Summary

12 Dragging and dropping
12.1 The drag-and-drop engine
12.2 A drag-and-drop shopping cart
12.3 Summary

Part 4 Mastering ASP.NET AJAX
13 Implementing common Ajax patterns
13.1 Script versioning
13.2 Helpers, help me help you!
13.3 Logical navigation and unique URLs
13.4 Declarative data binding
13.5 Declarative widgets
13.6 Summary

Source code (12MB)
Sample Chapter 2
Sample Chapter 4
Amazon Link: ASP.NET AJAX in Action

Amy Winehouse is proof drugs are a dieters best friend

Have to go now to listen to this song Rehab by...oh wait....

Reflector updated for Orcas Beta 2

Reflector for .NET
Reflector is the class browser, explorer, analyzer and documentation viewer for .NET. Reflector allows to easily view, navigate, search, decompile and analyze .NET assemblies in C#, Visual Basic and IL.

Download it here: http://www.aisto.com/Roeder/DotNet/

Interview With Itzik Ben-Gan Author Of Inside Microsoft SQL Server 2005: T-SQL Querying

If you are a SQL developer then you know who Itzik Ben-Gan is. If for some strange reason you don’t know then listen up. Itzik is a SQL Server MVP, he writes a monthly column for SQL Server Magazine and he is also a principal mentor and founder of Solid Quality Learning. You can also find him in the SQL Server programming public newsgroups. If you browse the MSDN site you will find several articles written by Itzik.

I purchased Inside Microsoft SQL Server 2005:T-SQL Querying and must say it is my favorite SQL Server 2005 book so far. Chapters 3, 6 and 9 alone are worth the money for the book. I have posted a link to a sample chapter (6) at the end of this post if you are interested to learn more about the style of writing and just to see how awesome this book really is.

Let’s get started with the interview

Q. What is the target audience for this book?
A. T-SQL developers with intermediate to advanced background.
By the way, the book is actually split to two volumes: Inside Microsoft SQL Server 2005: T-SQL Querying and Inside Microsoft SQL Server 2005: T-SQL Programming. Originally I intended to write one book, but I ended up with over 1200 pages; so I had to split it to two. But you should consider them as volumes 1 and 2 of one book. T-SQL Querying should be read before T-SQL Programming.

Also, I’d like to mention that several people contributed to the books, and I had great pleasure working with them. Lubor Kollar wrote Chapter 2 - Physical Query Processing of the T-SQL Querying volume and also provided great help and advice. Dejan Sarka wrote the CLR and XML content and explained what they have to do with the relational model. Roger Wolter wrote Chapter 11 - Service Broker of the T-SQL Programming volume. Steve Kass was the technical editor of the books, but contributed way more than what you would normally expect from technical editors. You can read about the contributors here:

http://www.insidetsql.com/


Q. What new technologies in SQL Server 2005 do you think are the most beneficial for developers?

A. I don’t think that it would be right of me to name two technologies in general, rather, depending on need. For example, for applications that involve XML manipulation, developers will find XML enhancements beneficial (and there are major ones in the product). For applications that need to implement at the database computationally intensive calculations, complex calculations, iterative logic, string manipulations, parsing, and so on, developers will find CLR integration beneficial. For applications that need queuing support, developers will find the new queuing infrastructure and the service broker platform beneficial. And then there’s row versioning embedded in the engine supporting the new snapshot isolations and other aspects of SQL Server; programmers will find this technology beneficial for certain types of systems that suffer from concurrency problems.

As for things that are closer to my heart; in regards to T-SQL, the two new features that I find most beneficial for developers are the OVER clause (e.g., with ranking calculations) and Common Table Expressions (CTE).

The OVER clause is really profound; I feel that it helps bridging the gap (or maybe I should say abyss) between cursors and sets. The OVER clause wasn’t implemented fully in SQL Server 2005, but even the existing implementation (especially with ranking calculations) allows simplifying and optimizing many pre-2005 solutions.

As for CTEs, they have both nonrecursive and recursive forms. The nonrecursive form has several important advantages over derived tables, allowing less code and better code maintenance. The recursive form allows for the first time in SQL Server’s history to write recursive queries, and very elegant ones. These are especially useful in manipulation of graphs (e.g., employee organizational chart, bill of materials, etc.).

Q. What will a person who reads this book gain in terms of understanding how the query engine works?

A. The approach I took in the books was first to understand logical query processing, then physical query processing (the engine), and then tackle practical problems where you need to combine your knowledge in both. It’s a problem based approach; I didn’t attempt to explain the engine for the sake of understanding how the engine works; rather to look at common practical problems developers face, write several solutions to each problem, analyze how the engine processes the solutions, optimize their performance, and simplify them. Ideal solutions are those that are both simple and perform well. A complex solution may have the side-effect of making the author proud of the ability to write such a solution, but is more prone to bugs and obviously involves maintenance overhead. The real beauty is in simple solutions that also perform well.

Regarding querying logic, the books put a lot of emphasis on correct understanding of SQL and thinking in terms of sets; this is a very though phase that developers have to go through since for most of us mere mortals it’s not a natural way of thinking.

Q. What are the most important things a person can do to master Transact-SQL?

A. The quickest way is to issue the following code:

USE master ;-)

On a more serious note, I believe that there’s no quick way—there’s a way; it’s an ongoing thing. I can give recommendations based on what I try to follow. But some aspects of the way are personal; different things may work differently for different people. Anyway, my two cents worth…

Most importantly, you need to master the basics; or maybe I should use the term fundamentals. Ego and vanity can be big obstacles here. People try to jump directly to what they consider “advanced” and don’t get the fundamentals right. Advanced techniques are a matter of combining fundamental techniques. So be diligent and invest a lot of effort in understanding the fundamentals well. In terms of querying logic this means logical query processing, sets, NULLs, three-valued-logic, all-at-once operations, and so on. In terms of physical processing, this means getting to know internal structures, access methods, analyzing execution plans, and so on.

When facing new problems, try to solve them yourself before looking at others’ solutions. When you try something yourself you understand it best. This will also help you become more creative and develop your own techniques. Also, try different approaches, not just one, and compare the solutions.

Never consider yourself as being finished. Strive for perfection but never consider your solution perfect. Constantly work on problem solving; look for new problems and keep going back to old problems and try to improve the solutions. This way you polish and perfect your techniques and enhance your vocabulary.

I also feel that you get a much better understanding of a subject when you need to explain it to others.

I truly believe in all of the above, but ideally, you don’t need me to tell you these things. In fact I feel awkward and uncomfortable giving such recommendations; so if at this point you think that I’m a fart I won’t hold it against you. I feel more comfortable writing queries and talking about them. ;-)

As I said earlier, this simply feels natural to me. Though some things are common in the way to master any profession, some things are and should be personal; namely, you have to find your own way and not necessarily follow what someone else recommends to the letter. :-)


Q. Itzik , the first time I came across your name was in the Microsoft public SQL Server programming group. Can you explain to us why you participate in newsgroups and forums?

A. In the past I used to be much more active in the newsgroups; today less, but whenever I participate I feel revived—especially when visiting the public programming newsgroup. If I had to name one thing that had the greatest impact on my knowledge in T-SQL it would doubtless be my participation in the newsgroups. It’s a very fertile soil to grow. So many practical problems are posted there daily, and by trying to solve other people’s problems, you end up learning more and more. Also, many practical problems are repeated there frequently, and as time goes by, you keep perfecting your techniques, and also sometimes get to see very unique and interesting solutions posted by other people. People pay so much money to study in schools, colleges and universities; here’s a great place to learn for free, and while you’re at it, you end up helping other people. :-) It’s the perfect Dojo for T-SQL practitioners.


Q. How did the massive changes between SQL Server 2000 and SQL Server 2005 affect the research for your book?

A. I think I went through a different experience than some other authors. I’m so immersed in the T-SQL language that I feel that it’s my mother tongue. Seriously, in many cases I feel that I can express myself better with T-SQL than with English or even Hebrew which is supposed to be my native tongue. I was deeply involved in writing T-SQL code using new language elements from the very early builds of Yukon. Also, our company, Solid Quality Learning, works closely with the SQL Server development team and customer advisory team (CAT). We worked on whitepapers for beta builds and got access to specs in very early stages of the product. I also developed and delivered courses on Advanced T-SQL before writing the books. So writing the books did not involve research, rather the material was essentially part of me, and all I had to do was to convert my thoughts to words.

I have similar experience with writing in general. I have a monthly T-SQL column in SQL Server Magazine, and often people ask me where do I get ideas, and what’s the process involved. I work on so many ideas constantly not for the sake of writing, rather, it’s a natural part of me—the way I was talking about earlier. So when it’s time to write a column, it’s basically pulling something from one of the drawers in my mind and putting it into words.


Q. Name three things that you wish would be in Katmai (the next version of SQL Server)?

A. These are easy to name, but I doubt that my wishes will come true in Katmai:

1. Support the ORDER BY sub-clause of the OVER clause for aggregate functions; BTW, this has nothing to do with ordered aggregates, rather it’s a logical way to define to a window function which window to work with. I’ll provide a link to a paper which I wrote recently for people looking for more details.

2. Support the ROWS and RANGE windowing sub-clauses of the OVER clause for aggregate functions.

3. Add support for row value constructors/vector expressions.
In general I wish to see more focus on T-SQL enhancements, and specifically, a more complete implementation of the profound OVER clause. It bears great benefits within it that I believe many people are not aware of yet. You can find more details in the following paper:

http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc


Q. Do you think that every developer should have a numbers table in their database?

A. That’s a good question that makes me smile. :-) Yes! If not a table, a table function. I’ve used an auxiliary table of numbers in so many solutions; I find it a great helper tool to solve many querying problems. Just as an example, here’s a query using an auxiliary table of numbers (called Nums with a column called n) that splits strings containing comma separated lists of values (stored in a table called Arrays, with columns arrid and string):

SELECT

  arrid,

  n - LEN(REPLACE(LEFT(string, n), ',', '')) + 1 AS pos,

  SUBSTRING(string, n,

    CHARINDEX(',', string + ',', n) - n) AS val

FROM Arrays JOIN Nums

  ON n <= LEN(string)   
AND SUBSTRING(',' + string, n, 1) = ','  
ORDER BY arrid, pos;  

And if you need to operate on a single array (e.g., one that you get as an input to a stored procedure), you can encapsulate the logic in an inline table function:

CREATE FUNCTION dbo.fn_split(@arr AS VARCHAR(MAX)) RETURNS TABLE

AS

RETURN

  SELECT

    n - LEN(REPLACE(LEFT(@arr, n), ',', '')) + 1 AS pos,

    SUBSTRING(@arr, n,

      CHARINDEX(',', @arr + ',', n) - n) AS element

  FROM dbo.Nums

  WHERE n <= LEN(@arr) AND SUBSTRING(',' + @arr, n, 1) = ',';  
GO    
SELECT * FROM dbo.fn_split('10248,10249,10250');   

Output:
pos element
---- --------
1 10248
2 10249
3 10250

This implementation has major advantages (both in terms of security and performance) over the commonly practiced solution utilizing dynamic SQL.

Erland Sommarskog, a fellow MVP, wrote a great paper on the subject. You can find details here:
http://www.sommarskog.se/arrays-in-sql.html

By the way, you can vote for adding the table of numbers as part of the product here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=258733


As a tip, here’s a very fast inline table function that will return a table of numbers for you:

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE

AS

RETURN

  WITH

  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),

  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),

  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),

  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),

  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),

  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),

  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)

  SELECT n FROM Nums WHERE n <= @n;  
GO     

-- Test function 
SELECT * FROM dbo.fn_nums(10) AS F;  
GO   

Output:

n
---
1
2
3
4
5
6
7
8
9
10

It’s even faster than querying a real table of numbers. ;-)


Q. Why is it important to think in sets instead of procedural code?

A. SQL is based on the relational model, which in turn is based on set-theory and predicate logic. The relational model is a proven model for handling data—for several decades. It’s important to adopt set-based thinking in order to write simple and efficient solutions, otherwise, you defeat the whole premise on which the model is based. SQL queries (set-based) have several advantages over procedural/iterative code (e.g., using cursors) as far as data manipulation is concerned. Set-based solutions focus on the “what” part of the solution; namely their logic. Iterative solutions spend a lot of code on the “how” part, clouding the logic behind all the code; lengthy code naturally has maintenance overhead. Set-based solutions allow the optimizer to choose an execution plan among several possible plans based on cost estimations, while iterative solutions typically force a very rigid plan. In short, set-based solutions allow you to focus on logic, and speak with the database in its native language. Also, cursors have a lot of overhead associated with the record-by-record manipulation.

I’m not saying that ALL problems are best solved with set-based solutions, but the majority of data manipulation related problems are. Tasks that involve less data manipulation, and more computations, string manipulation, parsing, inherently iterative logic, these can benefit from procedural solutions. But T-SQL (and SQL in general) was not designed to handle such tasks efficiently. That’s an area where .NET is much stronger, richer and more efficient. These are the areas where I see the benefit in the CLR integration in SQL Server 2005.


Q. Which chapter was the hardest to write and can you explain why?

A. Chapter 3 – Query Tuning in the T-SQL Querying volume. It was the hardest for several reasons.

In this chapter I cover internal structures and access methods. It was very important for me to depict internal structures and diagrams of access methods graphically as accurately and as detailed as possible. I created many figures and worked on each for many hours at the pixel level. Several figures took a day of work each. I actually took snapshots of real internal structures like pages of B-Trees with DBCC PAGE and DBCC IND commands, and incorporated real bits of data in the diagrams to make them as reliable as possible.

Also, the chapter appears early in the first volume. I wanted it early in the books to provide important foundations and background that is required for the rest of the books. But I had to include queries used for performance analysis that use advanced techniques that I explain later in the book.

There were also other obstacles.

This was the chapter I wrote last, and it took me several very draining weeks to complete. It ended up with over 100 pages. There’s a lot of query tuning coverage throughout the books, but it’s not the tuning part that was hard, rather the aforementioned reasons.


Q. Why do you write technical books?

A. I’m a teacher in heart and sole. I think that writing is an integral part of teaching. It’s a flowing river; you acquire knowledge and you pass on your knowledge to others. It’s very hard and requires a lot of discipline, but ultimately it’s a process from which you also gain a lot.
Also, there’s a more selfish reason. As I said, I’m a teacher and I want students in my classes. Books give you exposure and I hoped the books would get more students in my courses.


Q. Who are your favorite authors?

A. JRR Tolkien, William Goldman, Douglas Adams, Edgar Allan Poe, Niccolo Machiavelli, Yamamoto Tsunetomo, Miyamoto Musashi, Sun Tzu, J.K. Rowling

By the way, some of my favorite authors did not write any books, yet. :)

Here are a few examples:

David Campbell, who is the General Manager of Strategy, Infrastructure and Architecture of Microsoft SQL Server used to participate in the past in some private SQL newsgroups back in the days when he owned SQL Server’s Storage Engine. His depth of knowledge, teaching and explanatory skills, passion, humility, great English, all were evident in his posts. I asked him to write the foreword to the T-SQL Querying book; you have to read it to understand what I’m talking about. It’s pure joy to read his writings, and I sorely miss his participation in the newsgroups. I wish he had written books back in the days when he was very deeply involved in the technicalities and small details of SQL Server’s engine.

Roy Harvey, a fellow SQL Server MVP. He’s active in the SQL Server community especially in the SQL Server Programming newsgroup. When you read his posts you feel the experience, wisdom and humility flowing from them. I would have very much loved to see work published by him, but you know, some very wise and knowledgeable people shy away from publicity and exposure—unfortunately for us.

Steve Kass is another good example. Steve was the technical editor of my books and there are no words that I can use to describe his capabilities. He has a very unique combination of knowledge, logic, mathematics, SQL, genius, perfect English, and a way of explaining things in a simple manner. Oh, how much I’d love to see his work published in books some day!

I also like reading posts by David Portas; another fellow MVP. I’d love to see a book on modeling published by him some day.


Q. What SQL Server books are on your bookshelf?

A. With your permission, I’d like to broaden my reply to books covering topics that I believe are important for SQL practitioners. On my bookshelf you will find The Art of Computer Programming by Donald E. Knuth, books about logic puzzles, The Thirteen Books of The Elements by Euclid, Elements of Set Theory, Graph Theory, Regular Expressions Pocket Reference by Tony Stubblebine, Mastering Regular Expressions by Jeffrey E. F. Friedl, several SQL books by Joe Celko, SQL-99 Complete, Really by Gultzan and Peltzer, Inside Microsoft SQL Server (various versions; early by Ron Soukup, later by Kalen Delaney), The Art of SQL by Stephane Faroult with Peter Robson, and others…

You can find a more extensive list here:

http://www.sql.co.il/books/insidetsql2005/resources.htm#Books

I have to say though that I probably spend more time writing stuff than reading…


Q. I know you like math and logic puzzles, do you think it is beneficial for a SQL developer to be somewhat good in math?

A. Yes, but I would say it is beneficial to be good in logic and not necessarily generally in math. That’s why I included a whole appendix with pure logic puzzles in the T-SQL Querying volume. Some people find it odd, while people that truly follow the SQL way find it natural (at least that’s what I believe). Almost every SQL querying problem in its hart is a logic puzzle. It’s very common to see that people who are deeply immersed in SQL are also very fond of logic puzzles. It’s not by chance. My belief is that if you practice logic puzzles you can improve your SQL. Also, logic puzzles are fun and can be practiced by the whole family.

And there’s another reason to dwell on logic—one coined by Sir Arthur Conan Doyle:

"Crime is common. Logic is rare. Therefore it is upon the logic rather than upon the crime that you should dwell."

Amazon Link: Inside Microsoft SQL Server 2005: T-SQL Querying
Sample Chapter: http://www.sql.co.il/books/insidetsql2005/sample_chapters.htm#T-SQL_Querying_Sample_Chapters

Interview With SQL Server MVP Louis Davidson: Author Of Pro SQL Server 2005 Database Design and Optimization

Louis is the author of Pro SQL Server 2005 Database Design and Optimization and a SQL Server MVP. I have a copy of his book and I highly recommend it to anyone who wants to learn about designing and optimizing databases. The question-and-answer session with Louis that follows was conducted via email.

Louis, the first time I came across your name was in the Microsoft public SQL Server programming group. Can you explain to us why you participate in newsgroups and forums?

I have been involved with newsgroups since back in college when I was an avid reader of rec.music.beatles. And when I found the SQL Server groups (back then, before Microsoft’s groups it was comp.databases.ms-sqlserver), I started reading them, and finally giving some feedback. I then spent time in the newsgroups trying to answer people’s questions to help them, and help me extend my knowledge past school and technical books; because the real situations that people get into are more interesting than anything you can get in a textbook.

I quit hanging around for quite a while because the public newsgroups (just got too doggone noisy. I mean come on, there is only so often that one can have certain, “personal” products sold to them.

But about three years ago, I went to a session with Kalen Delaney (who was then, and is now one of the people who I look up to greatly,) who was talking about things to do to promote your books. I started then trying to answer three questions a day (or more) on the Microsoft newsgroups (instead of the public ones) and now on the MSDN Forums (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1).

I don’t know that it helped my book sales in any overwhelmingly large way, but it certainly has helped me in my career. I find that so many things I have learned while helping others end up being useful at my day job. Plus, my philosophy in regards to SQL Server is simple: the more people who like SQL Server the more likely that the product will be around when I retire in 30-40 years.


I have noticed that you have explained the 12 rules of Codd, do you think that it is important to know these rules as a database developer?

Absolutely, if you don’t know why you do something, then it is just too easy to think that it isn’t worth it. And you can’t really bend the rules until you know them. Codd’s rules are the foundation that the RDBMS was built upon, which the begat normalization. Once people get that SQL Server is essentially a tool largely created to manipulate and serve up normalized data (a concept that was built upon the original 12 rules) it actually becomes essential to normalize, not just something that some square geezer is telling you that to do.


Why did you decide to write technical books?

Two reasons:
1. So I would have a reference to refer to when I need to recall something.
2. Because I hate having free time.

Seriously, I had really done quite badly in my senior level database design class in college, but good enough (I think I made a C.) So when I was thrust into the database programmer role, I wanted to learn how to do it right (something my father had not so subtlety driven into my brain years before.) What made things even better was that I had several mentors who believed in doing things right during my first six or seven years of programming, so I learned essentially only good practices, but I still wanted more. I couldn’t find a book on database design that was not written as a text book, so I suggested a book on database design to the editors at WROX, whom I was working for as a tech editor on a Visual Basic/SQL Server book. I expected to tech edit this design book, or possibly write a chapter or two, but before I knew it they had contracted me to write the entire book (Chuck Hawkins wrote a chapter for me, but I wrote the rest) and paid me what seemed like a ton of money (of course, if I average it out to an hourly wage, I averaged far less than minimum wage when all was said and done.)

The process of writing the book was an amazing journey, one because I had never written a paper greater than like 15 pages (my degree is in CS, so all of our papers were really quite small in comparison) and I had to learn a good deal of stuff along the way because I was not really an “expert” at the time (and the more I know, the less I feel like an expert now!) The thing about writing is that you have to be more correct than in almost any medium. When you speak on a subject, if you say something, the people in the audience go, “whu?” and if you talk fast enough and right enough, they will likely forgive you (as long as you don’t do it again.) But if you mess up in writing in a book, it just hangs there over your head forever.

When it was over I had something I was proud of and I said what every technical book writer I have talked with says: “NEVER AGAIN! Too much work, not enough gain, I could make more money cleaning out stalls at the local pet store!!!” I have now said more or less the same thing three times now, and I hope to say it again around the time Katmai ships.


Which chapter was the hardest to write and can you explain why?

Well, the ones that were the hardest were actually the easiest, since I got the Kevin Kline and Kurt Windisch to write them. These guys were excellent co-authors, and did great work. Kurt’s section on CLR programming is great, with some awesome example code that I often use when I need to build a CLR function. Kevin wrote a chapter about cross-platform development that rounded out the book.

Of the chapters I wrote, the most troublesome was probably the security chapter. Security is a very complex topic and there are many, many ways to enforce security. Even worse, there are too many ways to get around it once you have it set up. It is the one chapter that I am not completely happy with, and the one that I plan to do some writing on again in the next few months, most likely after the holidays, during which I hope to do very very little that isn’t concerned with eating, sleeping, or going to Disney World.


What is the audience for this book?

Well, with this being my third book I was kind of hoping that my audience had grown to Dogbert
-like proportions, but I have to be reasonable. I would simply be satisfied if I could get every SQL Server programmer to read it (and buy it!) The overall goal was to make a practical guide going through the boring bits (theory, modeling), the stuff you hear about but rarely really understand (normalization) and to include the more practical topics like constraints, triggers, security, etc that even people who have years of experience would like.

I think that most people will get something from the book, from the newbie to the pro. Of course the payoff will be far less for someone who already knows the theory, but there is something in there for everyone that will at least be worth your 60 bucks (which is the full retail price. Don’t pay full retail price!)

I also try to keep it as light as possible, considering the very dry topic. There are several little humorous bits thrown in, and for fun I added a hidden message for a contest to run later (actually going on right now in the end of 2006.) All of the clues are leading to a message that I “encrypted” into a bit of code in the book (and yes, that is bonus clue for reading through this really long interview!) for a bit of fun.

Ultimately, I want the book to be something that I would purchase in each version at the very least for the practical aspects of the book. You might think that since I wrote the book I wouldn’t refer to it, but I often use the templates for triggers, procedures, error handling, etc from the book whenever I need to write one without using some tool.


Which SQL Server books are on your bookshelf?

Well, to tell the truth, I have quite a few on my desk and no time to read them. I have thumbed through Itzik’s book, “Inside Microsoft SQL Server 2005: T-SQL Programming”, and it is pretty darn good. Dejan Sarka, who was one of my technical editors worked on that book too. Beauchemin and Sullivan’s “A Developer’s Guide to SQL Server 2005” seems pretty good, and I have used Tachev’s book, “Applied Microsoft Analysis Services 2005” several times to try to figure out how different Analysis Services was in the 2005 version.

And I would be remiss to not mention Kalen’s previous “Inside SQL Server 2005” book. What a great reference that book was. I can’t wait to get my hands on her new books (not that I would have the time to read them either!)


What are/could be some consequences if your data model is not in Third Normal Form or even First Normal Form?

The earth could stop spinning on its axis, at least that is what I try to tell people. The more reasonable answer is that you will have nagging, annoying issues with your data that will require more and more management just to keep straight.

Normalization pushes data consistency issues to the foreground and forces you to deal with them. For example, take first normal form. One of the rules of first normal form is that every row is unique. A constant question on newsgroups/forums is: “I have duplicate rows, how do I get rid of them?” Well, go through this following messy set of steps, and then PROTECT YOURSELF USING KEYS!
Another concern of first normal form is scalar attributes. I like to define this as keeping your data broken down to the lowest form you will manipulate using SQL. So if you have to use substring for any common operation, they you likely have an issue. For example take an address, if you use addresses just as the user types them in, then just have a text column, put the address in it. But if you have a mailing system and you have to cleanse the data, and then break the address down into all of its constituent parts instead of going through the process every time you need an address. SQL is great for taking bits and pieces and constructing what you need, but lousy at breaking things down.

For third normal form, the problems are more ominous. If the data in the table isn’t really describing the thing that the table is modeling, you will end up with redundant data. Redundant data doesn’t sound so bad, until you have need to update the data. Consider the case where you have a table modeling a person, and in this table you have the person’s manager, the manager’s name and address. From this sentence, it almost sounds plausible and correct. It certainly is something you might want to see on a screen. But is the manager just the manager for the one person? If not you might have ten copies of the managers information. Now you have to make sure and deal with all ten copies, which is a real pain, to say the least.


How did the massive changes between SQL Server 2000 and SQL Server 2005 affect the research for your book?

The beauty for me was that the truly massive changes made little difference. Since the book I have written is about database design, things haven’t changed so incredibly much from that angle in any version of SQL Server. SQL evolves and gets better and there are optimization changes, but the basics remain the same. Fortunately as machines get more powerful we need to tweak performance less and less and we can just focus on data integrity and usability through normalization.

The biggest differences that I am interested in from an authoring standpoint have been in the ability to do more with T-SQL (I wrote the T-SQL for Developer’s chapter in the Pro SQL Server 2005 book for Apress), and now using the CLR functions. I had Kurt Windisch (a friend who was a board member for PASS at the time) write a very large CLR section, and I am so glad. Now I have a nice reference and excellent code samples for CLR objects. It is one of the great things about a book like this in that it is not overview book where you simply give the new features and come up with some reason you might want to use the features. If the feature isn’t tremendously useful, we can say that it isn’t useful. I think we got it pretty much right by saying that functions are really the most useful part of the new CLR objects, as well as to a lesser extent aggregations.

I admit that I don’t cover XML, something that I lament occasionally. I had waffled on including it for months because I just don’t feel that it is that important to OLTP systems, but on the other hand it could certainly be useful to implement a set of sparse properties (a topic I do touch on) and had arranged for another coauthor set up to write me a chapter about XML, but they dropped out too late to get another one. Even today I am considering commissioning an “add-on” chapter about XML to send out to readers (if you want it, vote with your email to drsql@hotmail.com and your wallets by purchasing the book so I can afford to pay someone. )


Name three things that are new in SQL Server 2005 that you find are the most valuable?

1. Dynamic Management Objects. Wow, I can’t even begin to start on just how great these are. The best one to me is outlined in this blog entry: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1443.entry . Automatic index tuning is awesome. I have used the basis of this system to optimize a third party system in excellent ways using the number two feature.
   
2. Included columns on indexes. These have really made it easier for dba’s to optimize queries by building covering indexes for queries without bloating indexes to death. What’s more, because you aren’t limited to 900 bytes, you can cover some queries in larger, more unwieldy systems that you just couldn’t do before. They aren’t the end all as they still have to be maintained, but they are certainly excellent in many situations. We have used them extensively to optimize a third party system that was very heavy on reads to cut down tremendously on full table scans.
   
3. The new BI Paradigm. The attribute oriented nature of Analysis Services is just plain cool. It gives users this extremely rich set of possibilities for viewing data using their cubes that is just so much better than they had in 2000. It was a tremendous change in gears though, and I am very glad that the fundamentals of dimensional modeling didn’t change, so the Kimball paradigm data warehouse we had was still quite viable using either platform, just easier to use.
   

Name three things that you wish would be in Katmai (the next version of SQL Server)?

I should have finished this interview a few weeks earlier, and I could have spoken a bit more freely about this subject, but at this point I am under NDA for what I have heard about Katmai. What I will say is that I hope to see as much work done in the relational arena (T-SQL and the engine) as they do for the other areas like XML, or tools. The heart and soul of SQL Server should always be right there, lurking in the name: SQL. All of the other new stuff they have added over the years has made things better for us, but I would have traded many, many non relational features to have had the ROW_NUMBER() and RANK() functions in an earlier version.

Of course, this is just my very selfish view on things as a Data Architect/Relational Programmer. In this particular case, even more than normal, I particularly speak only for me.


How critical is a proper data model for a good performing database?

Freaking essential! This is like asking how important is it to know the race route to win the race. The SQL Server engine is amazingly good at serving up data, but it is up to the implementer to know what data is needed and to put the data into a format that works the way that SQL Server works.

I would also be remiss if I didn’t take this one step further. How can you build anything without design? Not one programmer would call up a contractor and say “build me a house, three bedrooms, two baths” and then just let the contractor sketch out a rough drawing and then start working would they? And while I realize that it seems that software is a lot more pliable than building a house, once you have built the foundation classes, the database, and started a UI, rework can be just as costly as running a bulldozer though a regular house, and on a particularly a large software project, far more expensive.


What are some of the biggest mistakes that people make when designing a database?

I did something this for Greg Low’s really cool SQL Down Under podcast (where you get a good idea that I am terrified of speaking publicly) but lets just boil it down to two here:

1. Treating database design like application design - The reason why the concept of a distinct business layer is so intriguing to me is that we can transform the user’s views/needs from the GUI through this layer to the data layer. Each layer of the application has it’s own needs (UI, Object Layer Representation, Data Access Layer, Stored Procedure Layer, Database Structures) each very important in it’s own right. More importantly, each has very different design goals.
   
2. Forgetting about the future – During the design phase of any project, changes are very, very cheap. So if you realize you want to have a table with a domain of values instead of a simple varchar column, it is as simple as adding a rectangle to your model and connecting rectangles. Same goes with the discovery that the cardinality between two of your tables was not what you originally thought (“oh, a person is allowed to have more than one phone number in this system.”) However, as time passes, and more code is written, and more data is added to the system, the costs go up almost exponentially. Of course when the project manager has saddled you up and is giving you the crop, demanding results (that is their job after all) it can be all too easy to simply ignore the future and start coding. Always think about Future-You and how annoyed he is going to be when he realizes that had you just spent another hour three months ago, two weeks of work (of course corresponding to Future-You’s vacation to Disney World) could have been avoided.

There are so many others, bad naming, poor constraint usage, under-using stored procedures, ignoring security, weak testing, and so on and so on. But I think I can safely say that if you start with these two concepts, a great number of these bad habits will correct themselves.


Can you list any third party tools that you find useful to have as a SQL Server developer/admin?

I have become quite enamored with the Red-Gate tools, especially the database compare tool. I use it quite often to compare databases to find changes that have been made, especially for indexes and stuff done for tuning a database. The Apex tools are pretty good too.

The one tool I have used a lot lately has been the Automatic Indexing stuff from the query optimization team: (http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx). I am working right now on optimizing a homegrown/third party “object oriented”-esque tool that has a great deal of object abstraction built in, and have found tremendous performance gains from some really esoteric indexes this thing added to some of the queries that I would have never thought of in a month of Sundays. I wrote about it in more detail here: (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1443.entry and yes, I know this is the second time I have mentioned it, it is that important!)

In general, I am not so much a tool guy as I am a script guy. I use the UI to script out objects quite often, but then I try to edit everything with T-SQL so I know how it is done. The people who get too comfortable in the wizards or GUI often forget or don’t learn all of the tricks that you can do with the raw SQL commands, because it would just be too hard to build the perfect UI. So they give you 50% in the wizards and 90% in the GUI, and the rest is “advanced” stuff. If you don’t know about the nuts and bolts, you might be tempted to say “SQL Server doesn’t…”.

Lastly I have always been a big fan of ERwin for modeling. It does a really good job of representing my structures, and I like their macro language for building very customized scripts to build tables/views/procedures/etc.


What is the biggest Database in GB/PB that you have ever worked with, how many rows was your biggest table ever?

It was about ten feet across, six feet high. I had just started my new job as a LAN administrator and occasionally I had to fill in for the night operator. We had these 14 inch across reels of something that looked like a reel to reel tape, but they….wait, do you mean gigabytes and petabytes? I was thinking Great Big, or Pretty Big.

Well definitely no petabytes, and only recently have we started thinking in terms of terabytes, some of which is due to the fact that we have started storing everything as Unicode, essentially doubling the size of the database. We probably have around a half of a terabyte in our OLTP system and Data warehouse combined. I am never too excited about the size of a database as I am of the number of rows in a key table. You could have 10 billion rows and a very small database because it was designed nicely, or to the contrary, a million rows and have a terabyte if you just stored all of your data in text blobs (which is a favorite kind of storage for too many people.)

The largest database I designed I never got to see come to fruition because our company went through a couple of downsizing “events”, which I was part of the next to last one. I only know it was large because a prior coworker of mine called me up one day and told me we had gone past the 2 billion row mark and they were to changing to use bigint rather than basic int types for the surrogate keys. Tons of rows, but really they were quite small rows. We were keeping information on Internet equipment that the company had deployed. It was a pretty cool system that I wish I could see in action today (if it is still being used. The concept was neat, but as the implementers we were never 100% sure if the data would be useful later in the process.)


When did you start blogging and why?

When is easy, almost two years ago when I started writing my last book. The answer to why is pretty much the same as the answer to the technical books question, but increased an order of magnitude (as a reminder, that answer was: Two reasons: so I would have a reference to look into when I need it; and because I hate having free time.)

Blogging is possibly the biggest time sink on earth really. When I am working a “normal” 40 or so hour week in my day (read: paying) job, it is easy to spend an hour or so a day blogging, but when my job squeezes my time, it gets really hard.

But the most exciting part of blogging is the incredible active reference it gives me, in a format that really works for me. I can also use more of a shotgun approach and just write about what I want to write about on any topic, SQL or otherwise, rather than just writing about database design exclusively.

Over the years, I have tried creating libraries of code on my laptop, but that never has seemed to work for me. As I have aged (a little) I have started to realize that commenting, blogging, writing, etc is not for you, it is for Future Me and other people. I have so much code on my laptop from the past 10 years that I don’t know what the heck to do with it. I have VB6, SQL from past ages, failed article attempts, diagrams, etc all over my projects directory. So Past Me (that jerk!) is punishing me with his laziness, something I now vow not to do to Future Me. Confused? Good.

I started to realize that my blog had gotten out of control with so much information that I could no longer search and find when I found OfficeLive.com, a Microsoft site that allows you to create a free website for your small business. So I created drsql.org as a reference for my books, presentations, and my blog. On there you will find a decent enough index to my blog entries, usually within a month of being up to date, as well as a list of about ten articles that I can get to from the front page just about instantly.


About the book:
By Louis Davidson, Kevin Kline, Kurt Windisch
ISBN: 1-59059-529-7
672 pp.
Published: May 2006

Table Of Contents
CHAPTER 1 Introduction to Database Concepts
CHAPTER 2 Data Modeling
CHAPTER 3 Conceptual Data Modeling
CHAPTER 4 The Normalization Process
CHAPTER 5 Implementing the Base Table Structures
CHAPTER 6 Protecting the Integrity of Your Data
CHAPTER 7 Securing Access to the Data
CHAPTER 8 Table Structures and Indexing
CHAPTER 9 Coding for Concurrency
CHAPTER 10 Code-Level Architectural Decisions
CHAPTER 11 Database Interoperability
APPENDIX A Codd’s 12 Rules for an RDBMS
APPENDIX B Datatype Reference
INDEX

Sample Chapter:
Ch. 02 - Data Modeling


Amazon Links:

Interview With Ken Henderson About The Book SQL Server 2005 Practical Troubleshooting: The Database Engine

I am a big fan of Ken Henderson’s books, I believe that every SQL Server developer should have a copy of his books. When I noticed on Amazon.com that the book SQL Server 2005 Practical Troubleshooting: The Database Engine which listed Ken Henderson as its editor would be coming out soon I got very excited. I decided to send Ken an email to see if he would be willing to answer some questions I had about the book. To my surprise Ken was more than willing to accommodate my request.
The question-and-answer session with Ken that follows was conducted via email.




Denis: What is the audience for this book, is it the enterprise user or can a small department benefit from the tips in this book?

Ken: Both types of users would benefit. Anyone who’s ever had a problem with SQL Server has probably noticed how few resources there are out there for troubleshooting SQL Server issues. There are plenty of resources that talk about how it works. There are many that discuss how to write code for it. But there are scant few that talk about what to do when something goes wrong. This book is intended for that audience. It is also intended for those who want to better understand how the product works and to be prepared in the event that something does go wrong with it. SQL Server doesn’t break often, but, when it does, this book will help you deal with it.

Denis: For a customer who has a performance problem that is not hardware related, what would you say are the most important chapters in this book (in order of importance)?

Ken: The Query Processor Issues chapter and the Procedure Cache Issues chapter are the two best for this type of problem.

Denis: Seven developers from the SQL Server development team and three support professionals from Microsoft Customer Support Services wrote this book. What took so long to write a book like this, and why wasn’t there a SQL Server 2000 version? Is it because SQL Server has truly grown to be a major player in the enterprise market and there is a definitive need for a book like this now?

Ken: The book took so long because all of the authors are first-time authors, and they are very busy people. There was no SQL Server 2000 version because I was too busy with my own projects to begin this project back then. SQL Server is indeed a major player in the enterprise, but I believe it has been since SQL Server 7.0. That particular aspect had nothing to do with the timing of this book.

Denis: I noticed you are listed as editor of this book. Have you written any chapters in this book?

Ken: No, I did not write any of the chapters of the book. I also tried to preserve each author’s writing style. Each chapter includes its own byline, and I have edited them as little as possible. Because some of the authors were more capable than others, I necessarily had to be involved to varying degrees with each chapter. Some chapters needed lots of editing; some very little. I think each individual perspective represented in the book is a valuable one, but I also think they speak in unison on the important points of practical troubleshooting with SQL Server.

Denis: What new technologies in SQL Server 2005 do you think are the most beneficial for performance?

Ken: There are really too many to list. A few that come to mind at the moment: instant file growth, improved wildcard support, more sophisticated cache management, improved scaling on big hardware, the XML data type and richer XML support in general, CLR integration, etc.

Denis: This book as I understand has a lot of internals information from the people who either wrote the product or have supported it that currently is not available anywhere else--is that right?

Ken: Yes, the majority of the authors were actually developers on the product. A few were support engineers who supported the product. All have had full access to the SQL Server source code for many years.

Denis: What will a person who reads this book gain in terms of understanding how to performance tune a server?

Ken: They will better understand both how the server works and also how to recognize and troubleshoot common performance problems.

Denis: Is the book geared towards a beginner/intermediate level user or do you have to be an advanced user to really utilize the information in this book?

Ken: There is something in this book for everyone. I’d like to think that beginners, intermediates, and advanced users alike would benefit from reading it.

Denis: What are the most important things a person can do to master SQL Server?

Ken: Naturally, the best thing a person could do would be to do what the authors of this book did: study the SQL Server source code. Studying the SQL Server source gives you insight into how the product works that is impossible to gain through any other means. But, given that that excludes pretty much everyone outside of Microsoft, here are some general thoughts:

#1, understand how Windows works at a very low level and how SQL Server utilizes the many facilities it offers

#2, understand how the product was designed and how it was intended to be used

#3, explore it not only as a user, but as a developer. Fire up a debugger and see how it works under the hood

#4, build real apps with it, using its various components as they were intended to be used

Denis: What are the most important things a person can do to master Transact-SQL?

Ken: My initial thought is that, again, studying the SQL Server source code is the shortest path to the deepest understanding of the language. That said, here are some general thoughts in no particular order:

#1, understand how SQL Server works. Understand the intricacies of performance tuning on SQL Server. Know how data is stored. Understand memory management and scheduling at a very low level. Understand logging and tempdb semantics. Remember that SQL Server is just an application. It’s not magical and can be misused and abused just like any other app

#2, learn the syntax and semantics of the language inside-out. Get a feel for its strengths and weaknesses, and code to those strengths. Always lean toward writing set-oriented code when you can

#3, study solutions to hard problems that are available in various forms, and apply the techniques you learn to solve your own problems

#4, learn other SQL dialects so that you can become familiar with their unique attributes and understand how T-SQL compares with them. Gain an understanding of where T-SQL fits in the general taxonomy of SQL dialects

#5, learn other languages besides SQL. If your favorite programming language is T-SQL, you probably don’t know many languages. Learn C#, VB, Perl, Ruby, C++, or any others you can work into your research so that you can better understand software engineering as a discipline and so that you can more clearly see T-SQL’s strengths and weaknesses when compared with those other languages. Try to see where you might apply techniques from those other languages to solve problems you encounter in T-SQL. Familiarize yourself with what a design pattern is, what an idiom is, what refactoring is, and apply these concepts in T-SQL just as you would in any other “real” language

#6, understand the various SQL Server components and other technologies so that you can accurately ascertain when it’s appropriate to use T-SQL. It’s not the solution for every problem. As the old saying goes, “When all you have is a hammer, everything starts to look like a nail.” By broadening your knowledge of the tools and problem solutions available to you, you’ll be more likely to choose the best one when deciding how to design a piece of software or solve a particular problem. T-SQL may turn out not to be the best way to go in a given situation


And I will end with 9 questions for Ken not related to this book

Denis: What SQL Server books are on your bookshelf?

Ken: I have Celko’s books, Darren Green’s book, and a few others. Unfortunately, I don’t have time to read as much as I’d like. I spend most of my time either writing code for the product or studying code written by others on the SQL Server development team. The majority of my research into how SQL Server works happens via studying its source code directly.

Denis: Why do you write technical books?

Ken: I write technical books because I enjoy passing on what I’ve learned as a developer. That’s different from enjoying teaching people. I do enjoy teaching people, but that’s not why I write books. Some of the things I’ve learned about SQL Server took me years to master. I enjoy passing that on to people so that they don’t have to travel the same arduous roads that I did. I enjoy helping people. That’s different from teaching for the sake of teaching. I could never train people for a living. I am a programmer by trade, and everything else is an offshoot of that.

If I didn’t think I had something unique to bring to the discussion, I don’t think I’d write books. I don’t ever want to do what has already been done. I want to bring a fresh perspective to technical books, and I want to explore things in ways most other authors wouldn’t. If my work was exactly like everyone else’s, there’d be no reason for it to exist, and I wouldn’t bother. Given that I’ve never written fulltime but have always held down a regular day job while writing my books, the work itself is simply too hard to do just to be a clone of someone else. When people pick up one of my books, I hope they know right away that it’s one of mine, that it speaks with a distinctive voice, and I hope they think they might learn something from it simply because they trust me as an author.

Denis: Why did you join Microsoft?

Ken: I joined Microsoft to get inside SQL Server. I felt that the only way to go beyond the books and whitepapers currently out there on SQL Server was to see the source code for myself, and the only way that was going to happen is if I joined the company. I wanted to approach the exploration of SQL Server from a career developer’s standpoint, something that I had not seen done before. Most SQL Server books were written by professional trainers and former DBAs. As a career developer, I thought I could bring a fresh perspective to the coverage of SQL Server, and I felt the only way to really do that was to “go live with the natives” for a few years.

Denis: Who are your favorite authors?

Ken: Mark Twain, Kurt Vonnegut, Bart D. Erhman, Robert Price, Dean Koontz, Stephen King, Joe Celko, Sam Harris, Richard Carrier, Don Box, David Solomon, Charles Petzold, Kent Beck, Martin Fowler, Bruce Eckel, and many others.

Denis: Who do you consider your rival authors?

Ken: I don’t really think of anyone else out there as a rival. When I write a book, I mainly measure my work against my concept of the perfect book. I write for me. There’s a great book out there titled On Writing Well where the author, William Zinsser, repeats the old truism that quality is its own reward. It really is. I love the fact that people enjoy my books, but, really, the day I finish the final draft of a book and can say that I’m really done with it (at least for the moment :-) ), I’ve accomplished my goal. If it never sold a copy, I’d still feel fulfilled. I do care how it sells against other books, but I don’t really focus on it and don’t get caught up in any type of rivalries with other authors or other books.

Because I always want to write a better book than I wrote last time, I necessarily compete with my previous work and I compete against what I think the ideal technical book is. I think there’s enough room out there for lots of technical authors (it’s not as though people only buy one technical book and no others), and I have special empathy for my comrades out there who have to slog along in the middle of the night to crank their books out.

Denis: Where did the “Guru’s Guide” concept come from?

Ken: Wayne Snyder, one of the MVPs reviewing the manuscript for the first Guru’s Guide (which was at that time unnamed), wrote in the margin, “Hey, Ken, this is really a guru’s guide to solutions to hard T-SQL problems!” at which point the marketing folk at Addison-Wesley saw this and seized upon it. We had kicked around several titles, but hadn’t settled on any of them. As soon as they saw this, they pushed me hard to use it, and I reluctantly agreed. I didn’t like it initially because I thought the title of a technical book should focus on either the subject material or its intended audience, not its author. There was an understanding that we’d revisit the title when we did the second book (I was originally under contract to do three SQL Server books for Addison-Wesley), but then sales of the first book exploded, and there was no way we could change it at that point.

Denis: What do you think of all the accolades the Guru’s Guide books have received?

Ken: I am appreciative of them, but continue to be surprised by the longevity of the books and the reception they’ve garnered. I thought I was writing a niche book when I wrote that first Guru’s Guide book. I just wanted to get down everything I knew about T-SQL before I forgot it ;-). I will continue to write the kinds of books I like to read as long as people will buy them, so I hope that people continue to enjoy my work.

Denis: Will you be updating your Guru’s Guide books for SQL Server 2005? If so, when will they be out?

Ken: Yes. The second editions of the Guru’s Guide books should be out in 2007.

Denis: Describe your most unpleasant experience as an author.

Ken: I had a particularly unpleasant experience during the work on my architecture book when I had to send one of the technical reviewers packing. He was someone who’d provided useful feedback on my work in the past and someone I’d handpicked to review the book for technical issues. I usually appreciate negative feedback during the technical review process and generally consider it the most useful type of feedback, but this reviewer focused more on arguing with me about what should and shouldn’t be in the book than reviewing what was there for technical accuracy. He had a problem with the fact that I spent the first 300 pages of the book (the book ended up being over 1000 pages long) covering fundamental concepts (e.g., Windows internals) that I thought people needed to understand in order to understand the rest of the book.

I had seen people within Microsoft struggle to understand SQL Server internals because they did not have a good grasp of how Windows worked or how XML worked or how COM worked, or whatever, and, assuming readers would likely face the same types of challenges, I set out to remedy that in my book. I also wanted to go deeper than any SQL Server book ever had, and that necessitated being able to assume a certain amount of fundamental knowledge going in. I wrote him back after his first objection to the section and told him that, while I respected his opinion, I had my reasons for including it, and I explained those reasons as best I could.

He suggested I just refer people to authors like Richter and Solomon and those guys, and I told him I’d considered that, but that ultimately I felt that would be cutting corners and would be a huge inconvenience since readers would have to purchase several other books just to understand mine. No single other book had all the technical fundamentals I felt were essential, nor did any of them cover the material the way that I wanted it covered--in a manner that was designed especially for DBAs and database people. At the same time, most readers wouldn’t be able to skip the fundamentals coverage in some form or fashion because they wouldn’t be able to understand my SQL Server internals coverage without it. While it was certainly a huge amount of work for me to include this section (it was much like writing a whole separate book), I felt it was the right thing to do.

He persisted with his objections and continued to voice them not only to me but also to the editing team at Addison-Wesley. I told him on several occasions that I respected his opinion, but that, as the author, the call was mine to make and that I’d made it. This seemed to irritate him, and he continued to consume a certain amount of my time with correspondence related to the subject. At one point, I counted 7 separate threads from him on that one subject in my Inbox, and the folks at Addison-Wesley had begun to complain about him. The fundamentals section, and his negative remarks regarding it, came to dominate all the feedback we got from him. While other reviewers were either indifferent to the coverage of Windows internals in a SQL Server book (it was certainly a novel approach) or embraced it outright, he became increasingly more negative as we went along. We got useful feedback on the entirety of the manuscript from all the other reviewers, but he seemed unable to move on from the fundamentals issue. Eventually, I had my fill of dealing with him and cut him loose from the project. I’m a fairly patient person, but I just didn’t have time to deal with him anymore.

Technical reviewers sometimes get on crusades and attempt to usurp the role of the author to some extent. Until this happened, I’d never personally experienced it, but I’d heard of it. At the end of the day, the decision as to what is and isn’t in a book is the author’s to make, and the role of the technical reviewer is to identify technical issues with whatever it is that will be in the book. Decisions about content belong to the author, and, to a lesser extent, the publisher and the publisher’s editing team. I guess the lesson I learned here was to be more careful with whom I select for involvement with my projects. I always want honest feedback, and, fortunately, I know a lot of people who will happily point out every technical issue they find with my work without trying to become a de facto coauthor.


About the book:
Paperback: 456 pages
Publisher: Addison-Wesley; 1ST edition
Language: English
ISBN: 0321447743

Contents
Preface
Chapter 1 Waiting and Blocking Issues
Chapter 2 Data Corruption and Recovery Issues
Chapter 3 Memory Issues
Chapter 4 Procedure Cache Issues
Chapter 5 Query Processor Issues
Chapter 6 Server Crashes and Other Critical Failures
Chapter 7 Service Broker Issues
Chapter 8 SQLOS and Scheduling Issues
Chapter 9 Tempdb Issues
Chapter 10 Clustering Issues
Index



Thanks to Ken for answering all these questions and if there is one reason this year to buy your own holiday gift then SQL Server 2005 Practical Troubleshooting: The Database Engine is it



Amazon Links:

Fun With SQL Server Update Triggers

Below is some code that will show how to test for updated field values in an update trigger. As you can see the IF UPDATE (field) is true even when the values don’t change. Another thing to keep in mind is that if a value changes from NULL to something else and vice-versa, and you are comparing deleted and inserted tables without using COALESCE or ISNULL it won’t return those rows. Run the code below to see what I mean


CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )


INSERT INTO TestTrigger
SELECT 'SQL',500.23


CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS

IF @@ROWCOUNT =0
RETURN

IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO

--Let's update the value to 100
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back all 3 rows


--Let's run the same statement
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back the first row


--Let's really update
UPDATE TestTrigger SET value = 200 WHERE testid =1
--we get back all 3 rows

--Let's update with NULL
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it

--Let's update with NULL again
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back row 1

--Let's update with 300
UPDATE TestTrigger SET value =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300

--Let's update with 500
UPDATE TestTrigger SET value =500 WHERE testid =1
--we get back all 3 rows