Paul Nielsen

SSMS + Vista Speech Recognition

Paul Nielsen — Thu, 09 Oct 2008 02:52:00 GMT

Ever wonder if Management Studio can be controlled by speech alone? Watch this ScreenCast (1:47)

Cringely says databases are dead

Paul Nielsen — Fri, 03 Oct 2008 22:57:00 GMT

pingback to Brent Ozar's blog post about Robert X. Cringely's (columnist for PBS) prediction that Oracle's and SQL Server's days are numbered.

http://www.brentozar.com/archive/2008/10/cringely-says-databases-are-dead/#comment-5871

Recursive CTEs in AdventureWorks 2008

Paul Nielsen — Fri, 03 Oct 2008 22:09:00 GMT

The advent of HierarchyID removed the adjaceny pairs pattern (MangerID) from the employee table in AdventureWorks 2008. The recursive CTE example in BOL still uses the old AdventureWorks structure, so the code won't run in AdventureWorks 2008. I know that recursive CTE's are yesterday's news, but they're still useful as far as I'm concerned. So, if you want to learn about them and play with them in AdventureWorks 2008, here's some code to make your life easier...

(note: The new AdventureWorks 2008 does not include the adjacency pairs ManagerID column, the contacts table is changed to Person table, and the ContactID is changed to BusinessEntityID.)

-- Recreate Adjacency Pairs Pattern

ALTER TABLE HumanResources.Employee

Add ManagerID INT;

-- Populate the new ManagerID Column using join condition to match

UPDATE E

SET ManagerID = M.BusinessEntityID

FROM HumanResources.Employee E

JOIN HumanResources.Employee M

ON M.OrganizationNode = E.OrganizationNode.GetAncestor(1);

-- This query demos the recursive CTE

WITH OrgPath (BusinessEntityID, ManagerID, lv)

AS (

-- Anchor

SELECT BusinessEntityID, ManagerID, 1

FROM HumanResources.Employee

WHERE ManagerID IS NULL -- should only be EmployeeID 1

-- WHERE EmployeeID = 1 -- the CEO

-- Recursive Call

UNION ALL

SELECT E.BusinessEntityID, E.ManagerID, lv + 1

FROM HumanResources.Employee E

JOIN OrgPath

ON E.ManagerID = OrgPath.BusinessEntityID

)

SELECT Emp.BusinessEntityID, Emp.JobTitle,

C.FirstName + ' ' + C.LastName AS [Name],

M.FirstName + ' ' + M.LastName AS [Manager], Lv

FROM HumanResources.Employee Emp

JOIN OrgPath

ON Emp.BusinessEntityID = OrgPath.BusinessEntityID

JOIN Person.Person AS C

ON C.BusinessEntityID = Emp.BusinessEntityID

Left Join Person.Person AS M

ON Emp.ManagerID = M.BusinessEntityID

ORDER BY Lv

Denormalize for Performance?

Paul Nielsen — Fri, 03 Oct 2008 12:00:00 GMT

Conventional wisdom says to “normalize to 3rd normal form then denormalize for performance.” Poppycock! I say for an OLTP operational database (not a reporting or BI database), a well normalized design will outperform a denormalized design for three good reasons:

1) If the denormalization duplicates data, then the DML operations have to write to multiple tables, this means extra code and extra work.

2) If the denormalization duplicates data, these will typically be keys or at least candidate keys, so they’ll be indexed which contributes to the index bloat problem.

3) If the denormalization repeats columns (item1, item2, item3, etc), then the data has to massaged before being inserted or updated, and when reading the data it typically needs to be unpivoted for set-based code, and these operations mean extra work.

Those who promote denormalization would say argue that denormalization is trading write performance for read performance, and this is true for reporting database, but in issue number 3 above, even read performance suffers.

Since denormalization tends to go with SQL code in the app layer (no database abstraction in the form of stored procedures), the denormalization also impacts extensibility, since it’s even more expensive now to modify the database schema.

In production databases I’ve built, I’ve tested a pure normalized design vs. the best denormalized design I could come up with. Having a clearly defined database abstraction layer meant that I could run one script to modify the table structure, correct the data, and alter the stored procedure, and the app could continue to make the same stored procedure calls without breaking. In every case, the normalized design outperformed the denormalized design by about 15% due the normalized design’s more efficient code.

Dell hints at next gen Precision notebooks

Paul Nielsen — Fri, 15 Aug 2008 16:45:00 GMT

Quad Core, 16Gb RAM, 1TB RAID, 1Gb Graphics, run dual 30' monitors

see the video: http://www.dell.com/content/topics/topic.aspx/global/products/precn/topics/en/precision_concept_jumppage?c=us&l=en&s=gen

If SQL was an Olympic sport…

Paul Nielsen — Fri, 15 Aug 2008 04:47:00 GMT

we’d be judged by how well we nailed the disconnect.

Seriously, my Olympic pet peeve is that the gymnasts do these amazing twists and defy gravity, and then the major point of their score is whether they move a foot when they land. It makes no sense to me.

Stupid Index Seek Tricks - using Included Columns

Paul Nielsen — Fri, 15 Aug 2008 01:44:00 GMT

Usually, we think of index seeks (a possible Query Execution Plan operation) as using the b-tree index to pick out a row and then quickly pass that row to the next operation. But the index seek has special powers: when the conditions are right, it can examine non-key columns and filter based on those values inside the index seek operation.

When this happens the index seek properties will have two predicates – a seek predicate (which details the b-tree portion of the seek), and a predicate (with details about the additional filter using non-key columns).

You can see this behavior with the following code:

USE AdventureWorks2008

DROP INDEX Production.WorkOrder.IX_WorkOrder_ProductID

CREATE INDEX IX_WorkOrder_ProductID

ON Production.WorkOrder (ProductID)

INCLUDE (StartDate)

SELECT WorkOrderID, StartDate

FROM Production.WorkOrder

WHERE ProductID = 75

AND StartDate = '2002-01-04'

2008 SSMS Query Editing Gem

Paul Nielsen — Fri, 15 Aug 2008 01:36:00 GMT

Hidden in the Edit > IntelliSense menu is QuickInfo, keyboard shortcut Ctrl-K, Ctrl-I. It pops up information (e.g. column name, data type, nullability) about the current object (table, view, DMV, column) under the cursor. Very Cool.

Thank You to Buck and his team of SSMS superstars.

AutoAudit on CodePlex - vote for your features

Paul Nielsen — Thu, 14 Aug 2008 23:43:00 GMT

In an effort to be more "open", I've moved AutoAudit (code-gen utility that creates audit tirggers) from my swebsite to CodePlex. Soemthing I like about CodePlex is the open submission and voting on issues and feature requests. I've moved every feature request from emails and this blog to CodePlex. I invite you to download, submit requests, and vote at: http://www.codeplex.com/AutoAudit/WorkItem/List.aspx

Euro-style SQL sticker

Paul Nielsen — Tue, 05 Aug 2008 06:08:00 GMT

I love SQL and I wanted to put a oval Euro-style "SQL" sticker on the cover of my notebook. It's about the same cost to order a lot than to order one, so I have a few left over. They're nice vinyl top quality stickers suitable for a nice top quality notebook (see attachment).

If you'd like one, email me your name and address, and I'll snail mail you a sticker as a thanks for reading my blog (till they run out). No strings attached, I don't do anything with your address except mail you the sticker.

pauln@sqlserverbible.com please use the subject: Euro SQL Sticker

SSMS T-SQL Debugger ScreenCast

Paul Nielsen — Wed, 23 Jul 2008 06:22:00 GMT

just posted - SSMS T-SQL Debugger ScreenCast 5:58 - focuses on the call stack. If you haven't played with the new Debugger, this is an easy way to get a look-see.

The Romantic DBA

Paul Nielsen — Thu, 17 Jul 2008 23:30:00 GMT

Tomorrow is our Thousandths Day - Edie and I will have been married for exactly one thousand days. How do I know? SQL Server told me:

Select DateAdd(d, 1000, '10/22/2005')

When I first told Edie about Thousandths Day a few months ago, she thought I was making it up, but now she's wondering what I have planned. Since she never reads my blog… I booked the romance package at the Antler's Hotel where we had our wedding reception and a dinner at La Creperie, our favorite French restaurant downtown Colorado Springs.

Zoomix, Semantics, and the future of SQL Server

Paul Nielsen — Tue, 15 Jul 2008 20:55:00 GMT

Yesterday, Microsoft bought Zoomix, a very cool Israeli company. (<tangent> Israel is one of my favorite words - rich with layers of meaning: Isra meaning "one who struggles" and El, a Hebrew name for God </tangent>.)

Sunday I meet up with a friend who has a PhD in Computer Science and does research in a government lab. He said the most important database trend was semantics - identifying meta data about data. He also said the Oracle was all over semantics and Microsoft was behind the curve on this one.

Zoomix is a database semantics company researching data cleansing, linguistics and how these technologies become practical with databases. The Jerusalem based company will be integrated with Microsoft's Israeli research center and their work is going to integrated into future SQL Server versions.

Sounds cool to me.

OT: NY Times Tech Editorial on Windows 7

Paul Nielsen — Tue, 01 Jul 2008 03:42:00 GMT

I like the Vista interface, but I know that under the covers Vista is a bit bloated. I've blogged before that the one of the problems with the Vista vs. XP debate is that not every computer needs the power user UI of Vista. Many production machines are better off with the lighter weight XP.

Now we hear that Windows 7 won't be the redesigned microkernel MinWin that was talked about last year. This well written, thoughtful NY Times tech editorial calls for a new kernel for a new Windows. As a huge fan of Windows and one who wishes the very best for Microsoft and the Windows user base, I too would like to see the next Windows be a hit out of the park, not just Vista with multi-touch.

It's time to make a clean break from the past and architect a new kernel that's designed for multi-processors, SSD disks, and virtualization. Modern virtualization means that compatability can be accomplished with VPC running XP. It's time to shrug off the shackes of compatability. I have no doubt that Microsoft can do it. Come on Steve, build a Windows that will make everyone want to jump from XP!

Count of rows within five minutes of first instance

Paul Nielsen — Thu, 26 Jun 2008 18:52:00 GMT

In the SSWUG Virtual conference, Earl asks,

Paul, I am looking @ several million records. In each record is a nvarchar that IDs the record and a datetime for when the transaction took place for that nvarchar ID. I need to capture the nvarchar ID when a number of the records for that nvarchar ID was within a time period of the other records with the same nvarcharID. It seems that a cursor is the best way to go through each record to verify a count for the nvarchar ID each time the record was within (5) minutes of the original record. Would you have a set oriented method for extracting these sets of records?

Here's your script Earl,

USE TempDB
CREATE TABLE T1 (
        T1ID INT NOT NULL IDENTITY PRIMARY KEY,
        RecordID NVARCHAR(50) NOT NULL,
        TransTime DateTime NOT NULL
  )
GO
INSERT T1 (RecordID, TransTime)
    VALUES ('abc', '1/1/1980')
INSERT T1 (RecordID, TransTime)
    VALUES ('qwerty', '1/1/1980')
GO 3
INSERT T1 (RecordID, TransTime)
    VALUES ('abc', GETDATE())
INSERT T1 (RecordID, TransTime)
    VALUES ('qwerty', GETDATE())
INSERT T1 (RecordID, TransTime)
    VALUES ('qwerty', GETDATE())
INSERT T1 (RecordID, TransTime)
    VALUES ('xyz', GETDATE())
WAITFOR delay '000:000:000.050'
GO 25

WITH FirstRecord AS
  (
  SELECT RecordID, MIN(TransTime) AS FirstTime
    FROM T1
    GROUP BY RecordID)
SELECT T1.RecordID, COUNT(*) AS COUNT
  FROM T1
    JOIN FirstRecord
      ON T1.RecordID = FirstRecord.RecordID
      AND T1.TransTime <= DATEADD(n,1, FirstTime)
  GROUP BY T1.RecordID