THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

  • Ping Back: Checklists, Recipes and Algorithms

  • The <NDA> cloud is lifted, (for SDS anyway)

    I received confirmation moments ago from an SDS product manager that this blog post is indeed public knowledge,



    The hurdle for cloud db isn't security, bandwidth, or location, it's ease of transition. And the transition is now "change your connection string."  



  • Nordic: Case Study

    As soon as my book is complete, I'm going to focus full-time on and an ISV app based on Nordic. In the meantime here's an email I recently received from an ISV that's testing Nordic as the database design for thier next version:

    I have implemented a variation of Nordic which relies on (ID int, ServerID tinyint) keys for PK’s and FK’s, replacing the GUID’s.  I’ve added one table, which I mentioned to you a while back, a home for the AssociationID where the associationID (actually it is the composite ID, ServerID) is unique.


    We ran into problems with the Nordic stored procedures since Entity Framework does not have a concept of table variables in SQL.  So I’ve taken to writing triggers on the tables and views over the associations.  The triggers use table variables inside so they are hidden from the Entity Framework.  Using triggers, as a side benefit, lets us enforce, very simply, a “never delete” rule globally, which has been a design goal for us for a long while.


    Our database is a  bit of a stripped down Nordic now but I’ve populated the database over the last week and I have to say it kicks ass!!!  Using the “instead of” triggers on the views, I’ve pushed the Object table to 870,000 rows.  I made our association table be extension of Object as well, since we want the Object attributes to apply to associations just as much as they apply to entities.  46% of the Objects are 7 different classes of entity data and 54% of the Objects are class/role associations spread over 15 class/role association types.


    The insert triggers are capable of creating 500,000 rows spread across 4 tables (includes object rows, class entities and class/role associations) in 2 minutes (on a machine that is 6 years old!!) starting out with no data in the model.  Our tables are tall and narrow and I did strip down the object table a little bit from what you have.  I’m not sure yet what the performance will be when we get up into the 10 million row range and add bunches of indexes.


    Writing the triggers kind of sucks but the bang for the buck is good so far and I’m getting faster, better at it.  Someday, maybe Marc will incorporate some of the logic into his framework, Interacx.


    And I’ve tested this model in a P2P (Peer to Peer) replicated configuration.  (The ServerID’s purpose in life is to make PK’s unique in a replicated environment.)  In all  my tests so far the data model maintains integrity when we make changes to two different database instances at the same time.


    Looks like we will continue to extend this model, adding features to it, and maybe someday use it in a production environment instead of just as a research tool.


    When your book reviews end we should talk on the phone some day.



  • woot! Brad's a PC!

    Our good friend Brad McGehee of the Big Island is on the big screen in the "I'm a PC" campaign!  Aloha Brad!
  • SQL in the Cloud

    I spend my life thinking strategically about data. I'm going out on a limb and making a prediction:

    In two years time, a major question for every new deployment will be "Do we buy our own server, or host our data in the cloud?" and the momentum will be heavily for the cloud.

    In five years time, hosting your own SQL data on your own servers will seem as obsolete as running your own dial-up BBS.

    (so plan your career wisely)

    - The SQL Whisperer


  • Rocky Mountain Tech Tri-Fecta / Saturday Feb 21 / Denver

    If you’re in the neighborhood, I’d like to invite you to the Rocky Mountain Tech Tri-Fecta. I’m giving the SQL keynote: Lessons learned while writing SQL Server 2008 Bible. I’ll talk about choosing a great tech editor and the importance of style guides and Word templates – no just kidding.  


    Lessons learned while writing SQL Server 2008 Bible

    March 3rd the SQL Server 2008 Bible will go to press. I’ve spent most of a year digesting SQL Server 2008 and writing the book. Here’s my take on the strategic positioning of SQL Server 2008, my top 10 favorite new features, and where SQL Server 2008 belongs in your IT plans.

    My other two sessions…

    Nordic - Object/Relational

    Objects and databases don’t mix well. But, Nordic is a CodePlex open source T-SQL code-gen project that fully emulates object orientation within SQL Server, supporting class/attribute inheritance, workflow state w/inheritance, and associations with workflow state dependencies and inheritance. Associations with inheritance is very significant feature – it takes the 2D relational model and makes it 3D.  Adding workflow state to associations is like adding business logic to foreign keys without any code. Using a generic .Net UI to display the data, Nordic is makes it easy to model and build an object/relational database with a clean database abstraction layer. And it’s fast.

    In this session Paul demos the Nordic UI, walks though the stored procedure calls, and discusses how to design an object database using SQL Server.

    Pure T-SQL Code

    Fresh, never frozen, not from concentrate, extra pulp pure T-SQL Code. This session starts with about 12,000 lines of T-SQL sample code scripts from the SQL Server 2008 Bible ranging from simple queries to audit trail, to data compression and change tracking. The attendees will select the scripts for the detailed walkthroughs.

  • Book Update

    SQL Server 2008 Bible is draft complete, finally, and I'm cramming all day and half the night every day to answer every one of Hugo's most excellent tech edit comments. This edition will come in at about 1550 pages, I think. Several of the chapters are meatier than the last edition and this reflects my enthusiasm for SQL Server 2008. I'm much more pleased with 2008 than I was with 2005.  

    I can see a light at the end, the "final drop" deadline is looming ever closer, With any luck, I'll be completely done with the book about the day before the MVP Summit.

  • AutoAudit 1.09 posted to CodePlex

    AutoAudit is a code-gen utility that creates audit trail triggers, views to reconstruct deleted rows, and a table-valued UDF to reconstruct row history. 

    AutoAudiot 1.09 adds:
    _RowHistory table-valued UDF
    SchemaAudit triggers that keep the triggers in synch with ALTER TABLES, and audits database changes
    compatability with SQL Server 2008 data types
    several nit bug fixes

    AutoAudit CodePlex site 

    ScreenCast demoing AutoAudit 1.09 (6:45)

     please post issues and feature requests to CodePlex, Thanks, -Paul

  • SSMS + Vista Speech Recognition

    Ever wonder if Management Studio can be controlled by speech alone? Watch this ScreenCast (1:47)
  • Cringely says databases are dead

    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.



  • Recursive CTEs in AdventureWorks 2008

    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



      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?

    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

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

    see the video:

  • If SQL was an Olympic sport…

    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

    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'


This Blog



news item test
Privacy Statement