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

It's not a bug... it's a feature

Just got this in the inbox, pretty funny

Screencast: Real World SQL Server Integration Services with Matthew Roche

Channel 9 has posted a SQL Server 2005 Integration Services screen cast. From the site:

This geekSpeak is all about SQL Server 2005 Integration Services. We are lucky to have a real expert from the industry, Matthew Roche, to share his experiences with us. Matthew shows us how SSIS is a real development studio, since it's an instance of Visual Studio 2005, with new designers. It's very familiar for folks who are moving from a traditional development environment. He gets us familiar with tools and techniques, solutions and projects, like the drag-and-drop approach to building packages, opportunities to include scripts, and develop your own components as .NET Assemblies.

Matthew answers some great questions around performance, programmability. He helps us understand the distinction between data flows and control flows, and how best to manage SSIS packages. He offers some useful guidance on what scenarios merit what choices for moving data from one place to another - be it .NET app, SSIS, TSQL, BCP and so on.But the most important reason to watch is to find out what Matthew would "chew off his own mouse finger" to avoid having to do. :D
Be sure to check the geekSpeak blog for upcoming geekSpeaks!


Watch the screencast(WMV)

Hotfix: Operation on device '' exceeded retry count

FIX: Error message when you use the UNLOAD and REWIND options to back up a database to a tape device in SQL Server 2005: "Operation on device '' exceeded retry count"

SYMPTOMS
In Microsoft SQL Server 2005 Service Pack 2 (SP2), you use the UNLOAD and REWIND options to back up a database to a tape device. However, you receive an error message that resembles the following:
Operation on device 'TAPE0(<\\.\Tape0>)' exceeded retry count.

RESOLUTION
The fix for this issue was first released in Cumulative Update 3. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
939537 (http://support.microsoft.com/kb/939537/LN/ ) Cumulative update package 3 for SQL Server 2005 Service Pack 2

Get more details here: http://support.microsoft.com/kb/940379/en-US

Internal Query Processor Error The query processor could not produce a query plan

FIX: Error message when you run a query that fires an INSTEAD OF trigger in SQL Server 2005 Service Pack 2: "Internal Query Processor Error The query processor could not produce a query plan"

SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005 Service Pack 2, you run a query that fires an INSTEAD OF trigger. The INSTEAD OF trigger references both the inserted table and the deleted table. The INSTEAD OF trigger updates the base table by using a cursor. In this scenario, you receive the following error message when you run the query:
Msg 8624, Level 16, State 1, Procedure TriggerName, Line LineNumber
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

RESOLUTION
Hotfix information
A supported hotfix is now available from Microsoft. However, it is intended to correct only the problem that is described in this article. Apply it only to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2005 service pack that contains this hotfix.

Get the hotfix here: http://support.microsoft.com/kb/939562/en-US

Screencast: Build UAC aware apps with Visual Studio 2008

Channel 9 has posted a great screencast about building UAC apps with Visual Studio 2008. From the site:

Introduction: User Account Control is the top compatibility hurdle for some applications moving to Windows Vista. It is relatively easy to comply with some elements of UAC (e.g. embedding a manifest in your app) and with Visual Studio 2008 it is even easier as I show in this video. Relevant blog posts of mine are here and here.

Video download: Click on the image to play the video (from a streaming file). If you'd prefer to download the wmv packaged in a zip file, you may do so here.

Watch the screencast(WMV)

Late summer cleaning

I have too many CDs and decided to throw out some 'old' software. I either have newer versions or no need for it anymore.

Microsoft Gains Momentum in Business Intelligence Market as It Prepares to Launch Major BI Offerings

Microsoft Corp. today announced key milestones achieved within the business intelligence (BI) marketplace, including IDC’s recognizing Microsoft as one of the fastest-growing BI vendors in 2006. In IDC’s report, “Worldwide Business Intelligence Tools 2006 Vendor Shares,”* analysts found that Microsoft had a growth rate of 28 percent, the highest among the top 10 industry vendors. In addition, Microsoft® SQL Server™ 2005 was acknowledged by The OLAP Report as the No. 1 online analytical processing (OLAP) server on the market.

Microsoft’s continued investment in delivering high-performing, low-cost BI solutions for all business users via the Microsoft Office experience and SQL Server 2005 has customers taking notice.

“Like most companies, we experience a constant flow of change. Having the ability to monitor my business, analyze key business data, act on it and see real-time results gives me the competitive edge my business needs to survive,” said Michael Saunders, corporate vice president and chief information officer for Kellwood Co. “Microsoft has given us the ability and confidence to allow our employees at all levels to make informed decisions.”

SQL Server has established itself as an enterprise-class data platform. A recent BZ Research study found that 74.7 percent of enterprises use SQL Server, compared with 54.5 percent for the nearest competitor.

The Rise in Demand for Pervasive Business Intelligence

In its analysis, IDC states that the BI market grew by more than 11 percent in 2006. The BI tools market continues to be driven by the need for improved performance management and, to a lesser extent, compliance. “Performance management can take on the form of various decision-support and reporting functions to improve revenue, profit and operational efficiency; decrease costs; uncover new opportunities; or mitigate risk,” said Dan Vesset, an analyst with IDC. “Microsoft had another strong year in this market.”

To give businesses a step in the right direction, Microsoft will launch Microsoft Office PerformancePoint™ Server 2007 on Sept. 19, and SQL Server 2008 is scheduled to ship in the second quarter of 2008. Customers are invited to attend an event on Sept. 20 to learn more about Office PerformancePoint Server 2007. More information about the event is available at http://www.windowsitpro.com/roadshows/performancepoint.

“We are focused on helping our customers achieve higher levels of business performance by allowing them to take advantage of the rich BI capabilities offered through Microsoft business intelligence,” said Chris Caren, general manager of Office Business Applications at Microsoft. “We are excited to help companies integrate efficiently with one another to ensure all employees are held accountable for their actions across the entire business.”

In preparation for the upcoming releases, Microsoft issued its fourth and final community technology preview (CTP) for Microsoft Office PerformancePoint Server 2007 as well as the July CTP for SQL Server 2008.

The CTP program has over 10,000 active members. The latest preview of PerformancePoint Server 2007 is available to the public at https://connect.microsoft.com/site/sitehome.aspx?SiteID=181&wa=wsignin1.0, and enables customers and partners to preview and provide feedback on the latest pre-release versions of both products. The SQL Server 2008 July CTP can be downloaded at http://connect.microsoft.com/sqlserver. Feedback received from customers and partners during previous CTP programs has led to significant improvements in both products and closely aligned them with the needs of customers.

Bridging the BI Divide

Both releases promise significant enhancements to the BI landscape. PerformancePoint Server 2007 helps organizations align their processes by streamlining into a single application the monitoring, analysis and planning activities needed to improve business performance. By deploying PerformancePoint Server 2007, businesses can achieve better results by allowing individuals across the organization to improve performance. SQL Server 2008 will help organizations deliver a more secure, reliable data platform for storing business-critical information and delivering the right information to all users, while reducing the time and cost of managing data.

PerformancePoint Server is tightly integrated with the familiar and easy-to-use Microsoft Office system, allowing organizations to better align employees across divisions and make them accountable for their actions. In addition, PerformancePoint Server 2007 takes advantage of the enterprise-grade reliability, high performance, security technology and scalability of SQL Server 2005, enabling more people at all levels of the organization to transform disparate enterprise data into shared information they can use to make decisions and take actions that improve business outcomes.

“TCS and Microsoft are working together to invest in developing industry-leading, world-class BI solutions that enable our customers to experience certainty,” said Santosh Mohanty, global director and head of the Business Intelligence and Performance Management practice at Tata Consultancy Services Ltd. “We are seeing significant market momentum and double-digit growth for BI solutions built on the industry-leading Microsoft technology stack. This growth, being fueled by demand from some of the largest corporations in the world, has enabled us to aggressively push our strategic objective of building a $100 million BI practice on the Microsoft technology stack.”


* Source: IDC, Doc #207422, June 2007

Video: WF and WCF integration in .NET 3.5

Channel 9 has posted their latest WCF Video, from the site:

The technical evangelist for Windows Workflow Foundation, Matt Winkler, took some time to interview the team that worked to create the Workflow Services functionality inside of .NET 3.5. Workflow Service allow a developer to implement a WCF service as a WF workflow, and to expose a WF workflow via a WCF service. Here we meet Pravin Indurkar, the PM of the feature, as well as a host of other team members from the WF-WCF team, including a rare on screen appearance by forum rockstar Tom Lake (who is wearing a legacy bright orange WinFX t-shirt from PDC’05). The interview concludes with a quick tour around building 42 meeting some of the other folks on the team.
For more information on the WF and WCF integration in .NET 3.5, check out Matt’s blog. Also, check out these screencasts:
Creating a Workflow Service in Approximately 60 secondsDynamically Creating Service Contracts using Workflow Services in .NET 3.5Building WCF Services with WF – a 80 minute talk by Pravin given at a training we did here in Redmond.

Listen to the podcast(MP3)
Listen to the podcast(WMA)
Download the Video
Watch the Video

Video: Programming JSON with WCF in .NET Framework 3.5

Channel 9 has posted another interesting video, from the site:

Technical Evangelist Matt Winkler meets up with Eugene Osovetsky and Ghenadie Plingau, a PM and developer, from the WCF team to talk about returning JSON from a WCF service. JSON (JavaScript Object Notation) is s text based serialization of a JavaScript object, allowing one to new up a variable right from the string of JSON. Eugene hows how a developer can use the WCF programming model to serve JSON to AJAX clients. We also talk about how this is implemented in WCF as well as cool applications of this, namely to call WCF services from AJAX. He then shows off a cool tank game sample that allows two individuals to compete via a JavaScript game that is communicating with WCF.

Listen to the podcast(MP3)
Listen to the podcast(WMA)
Download the Video
Watch the Video

SQL Server 2008 Live Meeting Event on August 21st on DateTime function

Don't forget, today is the day of the SQL Server 2008 Live Meeting Event. Here is what I got in my inbox:

We wanted to remind you that on August 21st at 11am Pacific, that we will holding our 3rd Live Meeting event of the month of July. Join Michael Wang as he explains all the interesting features the new function "DateTime" will include. We will some examples of how "DateTime" will work in SQL Server 2008, and will be fielding all your questions as well. So make sure you get there early for all the fun!


New Datetime Data Type
08/21/07 @ 11am Pacific

this would make a great error page

Got this in an email, wouldn't this make a greate error page on a website?

Do you know how NULLIF and non-deterministic functions work?

Run this first

CREATE TABLE #j (n varchar(15))

DECLARE @a int
SET @a = 1
WHILE @a <= 1000
BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go

After that is done run this query

SELECT * FROM #j WHERE n = ' '

You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out

run this

SET SHOWPLAN_TEXT ON

SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')

and we see the following
--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))

This can also be written like this

SELECT CASE WHEN REPLICATE('1', RAND()*2) =' '
THEN NULL ELSE REPLICATE('1', RAND()*2) END

See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.

Here is an example, keep hitting F5 and you will see it will never be blank

DECLARE @val float
SET @val = RAND()

SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')

Article: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

This is the 6th article that Scott Guthrie has written about LINQ. The first 5 can be found below.

Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control

What is covered in the 6th article?

To SPROC or not to SPROC? That is the question....
The Steps to Map and Call a SPROC using LINQ to SQL
How to Map a SPROC to a LINQ to SQL DataContext
How to Call our Newly Mapped SPROC
Mapping the Return Type of SPROC Methods to Data Model Classes
Handling SPROC Output Parameters
Handling Multiple Result Shapes from SPROCs
Supporting User Defined Functions (UDFs)
Summary


Read the article here: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

Screencast: Amanda Silver on Visual Basic LINQ Syntax in Visual Studio 2008

Channel 9 has posted their latest screen cast. From the site:

In this interview, Amanda Silver, a Lead Program Manager on the Visual Basic Team, demonstrates new LINQ syntax around Joins and Aggregates that is now available in Visual Studio 2008 Beta 2. Amanda is a guru of LINQ in Visual Basic and gives insightful explanations of how to use this new syntax. Also check out new How Do I videos on LINQ to help get you started with LINQ in Visual Basic. More are added weekly!

Watch the screencast(WMV)

Screencast: Cider Beta 2 : Styling a WPF application with Blend and Cider

Brief walkthrough on using Blend to style a WPF application originally created with VS 2008 Cider designer; see how you can seamlessly go back and forth between the two tools to get the best out of a developer (intellisense, code wiring, etc.) and designer ( styling, templating) tools for creating WPF applications. This is part 2 in a 2 part series, part 1 is available here: Cider Beta 2 : Feature walkthrough

Watch the screencast(WMV)

Video: Understanding the “offline database development” paradigm

Channel 9 posted another great video. From the site:

Visual Studio Team Edition for Database Professionals (or “DBPro”) introduced a new way of working with your database in an “offline” or “sandbox” mode. Understanding how this works exactly requires a new way of thinking about how you build, deploy, and manage changes to your database. We asked Sachin Rekhi of the DBPro team to explain the paradigm and talk about the updated MSF guidance which adds roles and activities for database professionals.

Download the Video
Watch the Video

Video: Sonu Arora and C. Venkatesh: WCF Line of Business Adapter SDK

Channel 9 has posted their latest video. From the site:


I recently got a chance to sit down with some of the folks behind the WCF LOB Adapter SDK. Meet Sonu Arora, Program Manager, and C. Venkatesh, Principal Group Manager. Here, we talk about, well, the WCF LOB Adapter SDK. What is it, exactly? What's it for? Why did we make it? How did we make it? Can unmanaged developers take advantage of it? Tune in to find out the answers. For those of you who have no idea what WCF LOB Adapter SDK means:


WCF Line of Business Adapter Team says:

Developers face the challenge of building services that interoperate across organizational and platform boundaries, and connect to existing line-of- business (LOB) systems. At the same time, these services and LOB systems evolve independently of each other, making connecting applications and services a constant challenge.

To simplify this problem for partners and customers, Microsoft has created a common framework based on Windows Communication Foundation (WCF) – the WCF Line-of-Business Adapter Software Development Kit ( WCF LOB Adapter SDK). With the WCF LOB Adapter SDK, developers now have a consistent and repeatable way to build LOB Adapters for the .NET platform.

For developers and partners, this means a common adapter that can be used across the entire .NET platform, not just a single endpoint like SharePoint or BizTalk Server.

The WCF LOB Adapter SDK ships with a rich set of development tools to automate and simplify adapter development in a consistent and repeatable manner:

For Adapter Developers

• Adapter Development Code Generation Wizard

• Visual Studio 2005 Integration (IntelliSense, context-sensitive help)

• Runtime Components (LOB System Connection Pooling, Metadata Cache Lookup)



For Adapter Consumers

• Add Adapter Service Reference Visual Studio Plug-In

• Consumer Adapter Service BizTalk Project Add-In



Download the WCF LOB Adapter SDK here.

Listen to the podcast(MP3)
Listen to the podcast(WMA)
Download the Video
Watch the Video

Silverlight Virtual Earth Viewer

Virtual Earth now has a viewer in Silverlight, you can download it here:
http://silverlight.net/themes/silverlight/community/gallerydetail.aspx?cat=4

Video: The .NET Show: Silverlight

On this final episode of the .NET Show, Scott Guthrie, general manager for .NET Platform technologies, and Jason Zander, general manager for the .NET Framework, join host Robert Hess to discuss and demonstrate Silverlight, Microsoft's new cross-browser, cross-platform plug-in for delivering the next generation of .NET-based media experiences and rich interactive applications for the Web.

Watch it here: http://msdn.microsoft.com/theshow/episode.aspx?xml=theshow/en/episode063/manifest.xml

The World In Seven Photos

Only in China



Only in Hawaii


Only in India


Only in Mexico


Only in Texas


Only in Thailand


And last, but not least . . .

Only in America

New Service Release and Power Tools for Visual Studio Team Edition for Database Professionals

Channel 9 has published their latest video:

The team that builds Visual Studio Team Edition for Database Professionals (or “DBPro” for short!) has been hard at work on a service release and a set of power tools that add new functionality to DBPro. For this interview we asked Gert Drapers, the software architect for the DBPro team, to show us the new features of these releases. Gert has been working at Microsoft since 1991, having spent most of his career on the SQL Server team. If you like the SQL Query Analyzer, Gert is the guy to thank!
You can download the DBPro service release here and the power tools here.

Download the Video
Watch the Video

An 86 year old man walked into a crowded waiting room

An 86 year old man walked into a crowded waiting room and
approached the desk....

The Receptionist said, "Yes sir, what are you seeing the Doctor
for today?"

"There's something wrong with my dick", he replied.
The receptionist became irritated and said, "You shouldn't come
Into a crowded waiting room and say things like that."

"Why not, you asked me what was wrong and I told you," he said.

The Receptionist replied; "Now you've caused some embarrassment in
This room full of people. You should have said there is something
wrong with your ear or something and discussed the problem further with
the Doctor in private."


The man replied, "You shouldn't ask people questions in a room
full strangers, if the answer could embarrass anyone. The man
walked out, waited several minutes and then re-entered.


The Receptionist smiled smugly and asked, "Yes??"

"There's something wrong with my ear", he stated loudly.

The Receptionist nodded approvingly and smiled, knowing he had
taken her advice. "And what is wrong with your ear, Sir??"

"I can't piss out of it," he replied.

Screencast: Multitargeting in Visual Studio 2008

Channel 9 has published their latest screen cast. From the site:

Introduction: The coolest IMO feature of the Visual Studio 2008 IDE is that it supports the ability to target multiple versions of the .NET Framework, including using the new language features and also taking advantage of all project templates out of the box. In this video you can see what I mean by that. My relevant blog posts are here, here, here and here.

Video download: Click on the link to play the video (from a streaming file). If you'd prefer to download the wmv packaged in a zip file, you may do so here.

Watch the screencast(WMV)

Screencast: VB Intellisense improvements in Visual Studio 2008

Channel 9 has published their latest screen cast. From the site

Author: Hi, I am Daniel Moth :)
Introduction: In this video I demonstrate the improved cool VB intellisense everywhere and show how it not only increases productivity, but also gives you hints for learning new language features (that are also usable in .NET 2.0 projects). My relevant blog posts to this video in order of appearance are here, here, here, here, here, here and here.

Watch the screencast(WMV)

SQL Server 2008 Webchat and Live Meeting Series

SQL Server 2008 July CTP Web Chat
July CTP Public Web Chat
08/13/07
Add to my Calendar


SQL Server 2008 July CTP Live Meeting Schedule
Enterprise Scale Reporting Engine
08/15/07

AS Time Series Stability
08/17/07

New Datetime Data Type
08/21/07

Ordpath For SQL Server
08/28/07

You put the long, hard shaft...

You put the long, hard shaft...

into your hot, waiting mouth...

you play with it around your tongue....

in and out...in and out...

you feel it building up inside you....

you go faster and faster...

until the white creamy liquid fills your mouth...

you spit... and it's all over......






TAKE CARE OF YOUR TEETH!!!!


;-)

And now with that in mind watch this

Pownce bite experiment: out of 55 people how many will bite on Pownce?

I get requests that people want to add me as a friend on Pownce, I accept anyone's request. I decided to do a little experiment. If I was to ask 55 people to befriend me how many would bite? I don't know any of these people, I just clicked add friends and started to click on the add friend button
Below is the list of all the people, how many do you think will bite? I think between 10 and 20%

http://pownce.com/Biscuitsmlp/
http://pownce.com/littlecharva/
http://pownce.com/thomashawk/
http://pownce.com/Cafferine/
http://pownce.com/cooliojones/
http://pownce.com/CathleenRitt/
http://pownce.com/tealkra/
http://pownce.com/Phuser/
http://pownce.com/davidohalloran/
http://pownce.com/veronica/
http://pownce.com/Zwilliams/
http://pownce.com/TheFemGeek/
http://pownce.com/loydschutte/
http://pownce.com/Techchickblogger/
http://pownce.com/nibbler/
http://pownce.com/nickhodge/
http://pownce.com/MirMia/
http://pownce.com/the2nddeal/
http://pownce.com/AndiG/
http://pownce.com/Ericwandell/
http://pownce.com/kowax/
http://pownce.com/rjdavid/
http://pownce.com/Bootman/
http://pownce.com/vikk/
http://pownce.com/Tiil/
http://pownce.com/varianallen/
http://pownce.com/pmorle/
http://pownce.com/Tabatha/
http://pownce.com/weskandel/
http://pownce.com/Swanny14/
http://pownce.com/brianheys/
http://pownce.com/jairtrejo/
http://pownce.com/Harlequin/
http://pownce.com/mbookspan/
http://pownce.com/ezu/
http://pownce.com/zakfrazier/
http://pownce.com/florentp/
http://pownce.com/xMinusx/
http://pownce.com/Fogview/
http://pownce.com/dank/
http://pownce.com/Claws/
http://pownce.com/jonbu/
http://pownce.com/CWal37/
http://pownce.com/thewayofthespoon/
http://pownce.com/yonigre/
http://pownce.com/petel/
http://pownce.com/Homescape3d/
http://pownce.com/Ringmaster69/
http://pownce.com/mbazzoni/
http://pownce.com/j3one/
http://pownce.com/Karoll/
http://pownce.com/meateater/
http://pownce.com/ambience/
http://pownce.com/owenthomas/
http://pownce.com/mincedmedia/


BTW if for some strange reason you want to add me and you are not on this list (why would you be?) then here is my Pownce profile: http://pownce.com/DenisGobo/

I will update this and make the ones that accepted bold and red

IIS and .NET 2.0 interview questions

A bunch of good IIS and .NET 2.0 interview questions

How do you isolate and troubleshoot an ASP and / or ASP.NET application?
What is your idea of locking down a server?
How do you feel about having developers or anyone else making changes directly in production? Aka how do you normally handle change management.


Can you run the 1.1 and 2.0 on the same box? What are the limitations.
Can you explain what the '/bin' folder is used for.
Do you know what user instancing is.

What makes the IUSR account unique in IIS7 vs. previous versions.
What are some of the new ways of troubleshooting, Logging in IIS7

read them all here: http://weblogs.asp.net/steveschofield/archive/2007/08/09/iis-and-net-2-0-interview-questions.aspx

Redneck vasectomy

A redneck wants to get a vasectomy and goes to the doctor. The doctor says "ah, it's really easy, put a lit cherry bomb in a beer can and count to ten".

The redneck is confused, but since the doctor told him, he decides to try it. He takes the cherry bomb, lights it, puts it in the can and counts to five, at which point he puts the can between his legs and resumes counting with the other hand.

Video: Peter Spiro: Building great databases. Making great teams. Leadership. WinFS. The power of having fun

Channel 9 has posted their latest video. From the site:

Don't get a real job until you're 30. This is Technical Fellow Peter Spiro's advice, and something he fell just short of doing. Discover how this forestry student got a job at Digital Equipment Corporation (DEC) and then came to Microsoft to help build the team that drove SQL Server to the huge success it is today.

How do you learn the necessary skills for leading huge projects?

Try restaurant work or being in the Peace Corps. Also, never underestimate the power of knowing how to live it up. In this episode, learn how Peter uses his passion and energy to influence technology across Microsoft while empowering talent and growing teams to produce first-rate software.
Listen to the podcast(MP3)
Listen to the podcast(WMA)
Download the Video
Watch the Video

Does Reddit Need New Subreddits?

I think reddit needs a couple of new subreddits. One would be a joke/humor subreddit. The reason I am saying this is because the blonde joke that I posted on this blog yesterday made it to the reddit home page and got 118 votes with 68 comments. The link to that is here: http://reddit.com/info/2dqjc/comments
Some more useful subreddits would be pictures and videos, right now when someone posts a picture the subject is either
Picture of…..
Or
Subject..[Pic]
If you had a picture subreddit you would know immediately that all these links were pictures. So what do you think should we have more subreddits or would that be overkill?

New Hands-on Labs and Samples Available for Visual Studio 2008 Beta 2

Original source: http://blogs.msdn.com/charlie/archive/2007/07/26/visual-studio-2008-beta-2-released.aspx

The C# Language Specification Version 3.0 is now available for review.

There are samples and hands on labs available to help you explore this release:
LINQ and language samples for Visual Studio 2008 Beta 2
LINQ Hands On Labs
C# 3.0 Language Enhancements Hands On Lab

Podcast: Subversion Quickstart for .NET Developers

Listen to the Subversion Quickstart for .NET Developers show here: http://polymorphicpodcast.com/shows/subversion/

A blind man wanders into an all girls biker bar

Funny joke someone emailed me

A blind man wanders into an all girls biker bar by mistake. He
finds his way to a bar stool and orders some coffee. After sitting
there for a while, he yells to the waiter, "Hey, you wanna hear a
blonde joke?"

The bar immediately falls absolutely silent. In a very deep, husky
voice, the woman next to him says, "Before you tell that joke, sir,
I think it is only fair -- given that you are blind -- that you
should know five things:

1. The bartender is a blonde girl with a baseball bat.
2. The bouncer is a blonde girl.
3. I'm a 6 foot tall, 175 lb. blonde woman with a black belt in karate.
4. The woman sitting next to me is blonde and a professional
weightlifter.
5. The lady to your right is blonde and a professional wrestler.

Now, think about it seriously, Mister. Do you still wanna tell that
joke?"

The blind man thinks for a second, shakes his head, and mutters,
"No, .not if I'm gonna have to explain it five times."

sys.dm_exec_sessions

What does the sys.dm_exec_sessions view return? It looks like it is a combination of DBCC USEROPTION and sp_who. Let's for example find out how many sessions are running, sleeping or dormant

Just a quick count of all the sessions
SELECT COUNT(*) as StatusCount,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
GROUP BY status

Let's list all SPID's
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions


Let's just grab our own SPID
SELECT session_id,CASE status
WHEN 'Running' THEN 'Running - Currently running one or more requests'
WHEN 'Sleeping ' THEN 'Sleeping - Currently running no requests'
ELSE 'Dormant – Session is in prelogin state' END status
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID


Now let's query all user sessions
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =0

Then for the server you would change 0 to 1
SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process =1

Just a quick count of all the transaction isolation levels
SELECT COUNT(*),CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
GROUP BY transaction_isolation_level



Let's look at some of these ANSI settings and while we are at it let's use UNPIVOT to return the results vertically
SELECT SPID,Value,ANSI_SETTING
FROM (
SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID ) P
UNPIVOT (Value for ANSI_SETTING IN(
QUOTED_IDENTIFIER,ARITHABORT,ANSI_NULL_DFLT_ON,
ANSI_DEFAULTS,ANSI_WARNINGS,
ANSI_PADDING,ANSI_NULLS,CONCAT_NULL_YIELDS_NULL
)
) AS unpvt



Now let's see if we can duplicate DBCC USEROPTIONS
First run DBCC USEROPTIONS
And then run the following:

SELECT @@SPID as SPID,
CASE quoted_identifier
WHEN 1 THEN 'SET' ELSE 'OFF' END QUOTED_IDENTIFIER,
CASE arithabort
WHEN 1 THEN 'SET' ELSE 'OFF' END ARITHABORT,
CASE ansi_null_dflt_on
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULL_DFLT_ON,
CASE ansi_defaults
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_DEFAULTS ,
CASE ansi_warnings
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_WARNINGS,
CASE ansi_padding
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_PADDING,
CASE ansi_nulls
WHEN 1 THEN 'SET' ELSE 'OFF' END ANSI_NULLS,
CASE concat_null_yields_null
WHEN 1 THEN 'SET' ELSE 'OFF' END CONCAT_NULL_YIELDS_NULL,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL,lock_timeout,date_first,date_format
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

It's probably not exactly the same and I don't know if I missed a column but below is the whole definition of the view and you can experiment with this yourself

Below is what the sys.dm_exec_sessions view returns
On the first line are the column name and the data type, below that is the description

sys.dm_exec_sessions

session_id smallint
Identifies the session associated with each active primary connection.

login_time datetime
Time when session was established.

host_name nvarchar(128)
Host associated with the session.

program_name nvarchar(128)
Program associated with the session.

host_process_id int
Process ID associated with the session.

client_version int
Version of the interface used by the client to connect to the Server.

client_interface_name nvarchar(32)
Name of the interface used by the client to connect to the Server.

security_id varbinary(85)
Microsoft Windows security ID associated with the login.

login_name nvarchar(128)
SQL Login Name associated with the session.

nt_domain nvarchar(128)
Domain from which session connection was made.

nt_user_name nvarchar(128)
Name of the user associated with the session.

status nvarchar(30)
Status of the session. Possible values:
-- Running - Currently running one or more requests
-- Sleeping - Currently running no requests
-- Dormant – Session is in prelogin state



context_info varbinary(128)
CONTEXT_INFO value for the session.

cpu_time int
CPU time, in milliseconds, that was used by this session.

memory_usage int
Number of 8-KB pages of memory used by this session.

total_scheduled_time int
Total time, in milliseconds, for which the session (requests within) were scheduled for execution.

total_elapsed_time int
Time, in milliseconds, since the session was established.

endpoint_id int
ID of the Endpoint associated with the session.

last_request_start_time datetime
Time at which the last request on the session began. This includes the currently executing request.

last_request_end_time datetime
Time of the last completion of a request on the session.

reads bigint
Number of reads performed, by requests in this session, during this session.

writes bigint
Number of writes performed, by requests in this session, during this session.

logical_reads bigint
Number of logical reads that have been performed on the session.

is_user_process bit
0 if the session is a system session. Otherwise, it is 1.

text_size int
TEXTSIZE setting for the session.

language nvarchar(128)
LANGUAGE setting for the session.

date_format nvarchar(3)
DATEFORMAT setting for the session.

date_first smallint
DATEFIRST setting for the session.

quoted_identifier bit
QUOTED_IDENTIFIER setting for the session.

arithabort bit
ARITHABORT setting for the session.

ansi_null_dflt_on bit
ANSI_NULL_DFLT_ON setting for the session.

ansi_defaults bit
ANSI_DEFAULTS setting for the session.

ansi_warnings bit
ANSI_WARNINGS setting for the session.

ansi_padding bit
ANSI_PADDING setting for the session.

ansi_nulls bit
ANSI_NULLS setting for the session.

concat_null_yields_null bit
CONCAT_NULL_YIELDS_NULL setting for the session.

transaction_isolation_level smallint
Transaction isolation level of the session.
-- 0 = Unspecified
-- 1 = ReadUncomitted
-- 2 = ReadCommitted
-- 3 = Repeatable
-- 4 = Serializable
-- 5 = Snapshot

lock_timeout int
LOCK_TIMEOUT setting for the session. The value is in milliseconds.

deadlock_priority int
DEADLOCK_PRIORITY setting for the session.

row_count bigint
number of rows returned on the session up to this point.

prev_error int
ID of the last error returned on the session.



So that's it for today, the view contains a lot more than I covered.

Podcast: David Hayden on the Enterprise Library

Dotnetrocks has posted their latest podcast. From the site:

Carl and Richard talk to David Hayden about the new features of the Microsoft Enterprise Library 3

David Hayden is an independent consultant in Sarasota, Florida offering consulting, mentoring, and development services on architecture and best practices in .NET. David is a Microsoft MVP in C#, speaker, MSDN Forum Moderator, Enterprise Library Community Leader, and advisor to Microsoft Patterns & Practices. He spends much of his time helping companies and development teams leverage the proven practices and productivity benefits of Enterprise Library and Software Factories. In addition to his personal blogs at DavidHayden.com and CodeBetter.com, David also runs PnPGuidance.net, which offers numerous articles, screencasts, sample downloads and other resources covering best practices in .NET.

Download it here: http://www.dotnetrocks.com/default.aspx?showNum=262

Video: New Features in WPF 3.5

Channel 9 has posted their latest video. From the site:



Although WPF is less than a year old, we’re already putting the finishing touches to an update that will add some neat features and improvements, improve internationalization support and increase performance. In this video, Charles Torre (and Dr Sneath, when he finally turns up) sit down with Kevin “Bag’o’Tricks” Moore as he provides a whirlwind tour of the improvements and changes you can expect to see.



Download the Video

Watch the Video

Acropolis August CTP (VS 2008 Beta2 Refresh) Release available now

Acropolis August CTP (VS 2008 Beta2 Refresh) Release available now
From the site:

This release is mainly an update so that our customers can continue to evaluate “Acropolis” with the latest VS 2008 Beta. In addition to VS 2008 Beta2 support, we have also added one small feature named “FloatingLayoutPane”. We have gotten feedback from our customers and in our forum that basic functionality like showing a floating window is too hard in the July CTP. Please let us know what you think.

Please note, the Acropolis August CTP depends on VS 2008 Beta2, it will not work with VS 2008 Beta1.

Download the bits:
- Acropolis CTP & Documentation
- Acropolis Samples for August CTP

Video: Acropolis Meet (some of) the Team

Channel 9 has published their latest video, from the site:

The Microsoft code name “Acropolis” Community Technology Preview is a set of components and tools that make it easier for developers to build and manage modular, business focused, client .NET applications. Acropolis is part of the “.NET Client Futures” wave of releases, our preview of upcoming technologies for Windows client development.
Acropolis builds on the rich capabilities of Microsoft Windows and the .NET Framework, including Windows Presentation Foundation (WPF), by providing tools and pre-built components that help developers quickly assemble applications from loosely-coupled parts and services. Here, Kathy Kam, "Acropolis" PM, takes us around the halls of building 42 to meet some of the people who create "Acropolis". We also run into an old friend in the hallway who's thinking deeply about big software componentization problems...Enjoy!
Check out the "Acropolis" Team Blog.

Listen to the podcast(MP3)
Listen to the podcast(WMA)
Download the Video
Watch the Video

Agile: Planning Poker and Scrum

Who said you can’t gamble at work and what is Scrum anyway? Scrum is an agile process for developing software. With Scrum, projects progress via a series of iterations called sprints. A sprint can be 2 weeks, 3 weeks or a month. The sprint is a deliverable, after the completion of the sprint you are supposed to have delivered working piece of code. This might be a subset of a product. The reason to deliver frequently is so that the client can comment on the product. Instead of waiting 3 months before the client sees the product only to say that this is not what the envisioned they will see it earlier and give you feedback. This will save you a lot of time, trust me on this one! If you want to learn more about scrum then download this excellent 90 page book in pdf format by Henrik Kniberg (Scrum and XP from the Trenches)

Planning Poker
Have you ever been in a meeting where the question was asked how long it would take to do a certain task? What usually happens is this: the first person will say 16 hours and the next 3 people will pick something close or even the same value. With planning poker you don’t know what the other people said until every person decided. This is how it works: everyone has a bunch of cards which are numbered between 0.5 and 48 (with gaps), a need more info card and a need coffee break card. These cards can be used for days or hours, it depends how big the task is. So it the same question is asked then everyone puts a card down with the number facing down. Then all the people turn the cards and the group looks at the numbers. At this point you will see strange things every now and then, some people have 2 hours some people have 32 hours for the same task. The reason for this is because some of the people didn’t completely understand what is involved and might need more information. You go around the room and everyone explains how they picked their number. This is where you will find out that some people didn’t understand the task and need more info. Sometimes you will find out that a task needs to be split up, a task should not take longer than 1 ideal day to complete. After you have completed the whole process a couple of times you will find out that your team is much better at estimating the time it will take to complete a task.

There is a website where you can do planning poker online, the URL is http://www.planningpoker.com/

We made our own, we all picked a different picture for the back of the cards and everyone has the same numbers for the front.


To learn more about Scrum visit these URLs
http://agilemanifesto.org/
http://www.mountaingoatsoftware.com/scrum
http://www.xprogramming.com/xpmag/whatisxp.htm

If you want to read books about scrum then I recommend these two
Agile Project Management with Scrum (Microsoft Professional)
Agile Software Development with SCRUM

And never ever call a certified Scrum Master Scrumbag ;-)

Showtime You Tricked Me, Shame On You

A couple of days ago I was flipping channels (like any man ought to do without interference from the wife/girlfriend). I noticed The Hills Have Eyes 2 just started, it had 2007 as the year made. I found that a little bit strange but for all you know it was one of those straight to video releases. I checked it out on IMDB and it only got 5 stars. This is the plot outline according to IMDB: “A team of trainees of the National Guard brings supply to the New Mexico Desert for a group of soldiers and scientists that are installing a monitoring system in Sector 16. They do not find anybody in the camp, and they receive a blurred distress signal from the hills. Their sergeant gathers a rescue team, and they are attacked and trapped by deformed cannibals, having to fight to survive.”

I am thinking to myself “Okay I’ll bite and watch for 30 minutes, if it sucks I will turn it off”. I am watching this movie and the first thing I noticed is that it looks like it is taking place in the late 70s. One of these deformed freaks shows up and he looks like a puppet. How is the makeup in this movie so much worse than in the first movie? I watch for another 10 minutes and I turned it off. Next day I check IMDB again and what do I find? Yes there is a sequel of the original: The Hills Have Eyes Part II (1985). It has a 3.2 star rating. This movie doesn’t deserve more than 1 star. If you hate someone then buy this movie for that person because the person WILL suffer watching this garbage.

IronPython Integration Sample and the WPF Designer

If you've had the opportunity to work with WPF/.NET 3.0 yet, you've likely run into the x:Class attribute in XAML. If you specify a x:Class on a XAML page, during compilation the XAML compiler creates a class in code using the registered CodeDomProvider for the given language that you're compiling. This presented some challenges for getting the IronPython sample to work with the new designer.
There were two major problems:

1) The IronPython CodeDomProvider is not registered globally on the machine.
2)IronPython 1.1 does not support compiling to .NET-consumable types.

Read the rest here: http://blogs.msdn.com/aaronmar/archive/2007/08/01/ironpython-integration-sample-and-the-wpf-designer.aspx

A Developers Toolkit of free tools for C# and .Net

All the tools are freely available for download online (with the exception of the full Visual Studio 2005 suite).

Categories
IDE
Profiling Tools
Debugging
Code Analysis Tools
Unit Testing Tools
Documentation Tools
Compliance Tools
Build Tools
Contiuous Integration Tools
Source Control Tools

Get the full detailed list here: http://blog.lowesoftware.com/software-development/a-developers-toolkit-for-c-and-net

SQL Server 2008 Has Nanosecond Precision?

It looks like SQL Server 2008 has nanosecond precision for the time datatype
If you run the following

[edit]I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd[/edit]

DECLARE @t time
SELECT @t ='0:0'
SELECT @t AS Time1,DATEADD(ms,1,@t) AS TimeMilli,
DATEADD(ns,10000,@t) AS TimeNano1,DATEADD(ns,100,@t) AS TimeNano2


Time1 00:00:00.0000000
TimeMilli 00:00:00.0010000
TimeNano1 00:00:00.0000100
TimeNano2 00:00:00.0000001

Another interesting thing is that you can not use 0,'0' or ' ' to assign a value

These 3 will all fail

DECLARE @t time
SELECT @t =' '

DECLARE @t time
SELECT @t ='0'


DECLARE @t time
SELECT @t =0

But this will succeed

DECLARE @t time
SELECT @ =''

SQL Server Notification Services Removed from SQL Server 2008

From the last section (5.0 Deprecated Features) in the read me file

5.0 Deprecated Features
This section covers SQL Server 2005 features that are no longer included with SQL Server 2008.

5.1 SQL Server Notification Services Removed from SQL Server 2008
SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.

There you have it, no more Notification Services

SQL Server 2008 July CTP Has Been Released, So What Is New?

Download it here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

What is new?

Enterprise Reporting Engine
Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.

Analysis Services Time Series
This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.

T-SQL Improvements
Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.


Performance Data Collection
Collect data from various sources in SQL Server and OS to help with performance troubleshooting and server maintenance. With this improvement, organizations improve their analysis of common performance issues:
· Define what data is collected and organize the collection into collection sets
· Start/stop/manipulate collection sets programmatically (T-SQL and .NET API)
· Define where data is stored (relational database)
· View data through reports in SQL Server Management Studio.
· Provide platform to plug in more data collectors in the future.


Extended Events
SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.


Database Mirroring Enhancements
SQL Server 2008 builds upon the momentum of SQL Server 2005 by providing a more reliable platform with enhanced database mirroring:

Automatic bad page repair – allows the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the corrupted page from the mirroring partner.

Log stream compression – compression of the outgoing log stream in order to minimize the network bandwidth used by database mirroring.

Miscellaneous performance enhancements:
using asynchronous log write requests on the mirror in order to shorten the log write time and thus speed-up the commit acknowledgement.

better utilization of the mirroring log send buffers in order to pack multiple smaller log blocks into a single network send.

Supportability and diagnosability improvements:
additional performance counters to allow for more granular accounting of the time spent across the different stages of the DBM log processing.

new DMVs and extensions of existing views in order to expose additional information about the mirroring sessions.


ORDPATH Improvement
ORDPATH improvement provides an important new functionality to our customers who use hierarchical data. It provides a superior way of modeling hierarchies in SQL Server by introducing the HierarchyID system data type and corresponding built-in methods which are designed to make it easier to store, query and operate hierarchical data. HierarchyID is also optimized for representing trees, the most common type of hierarchical data.


Large User-Defined Types Improvement
Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.

DATE/TIME Data Types
SQL Server 2008 introduces new date and time data types. The new data types enable applications to have separate date and time types, larger year ranges for date value, larger fractional seconds precision for time value, time-zone offset aware datetime type that containing date, time and time zone offset portion, user defined option on fractional seconds precision of time related types and datetime2 and datetimeoffset provide standards conformant semantics. Along with the T-SQL support on the new types, both native (ODBC, OLEDB) and managed (SqlClient) providers also provide the full support through the client driver APIs.


Improved XML Support
To leverages the new date and time types, SQL Server’s XML Schema collection now provides full support for the xs:date, xs:time and xs:dateTime data types. Support for union types is also enhanced by returning correct results for “instance of” queries when union types are involved, and adding support for lists of unions and unions of lists constructs in XML Schemas.