Anthony Bloesch's Web Log

A WebLog about SQL Server development.

  • Anthony Bloesch's Web Log

    SQL Server 2008 error handling best practice

    • 9 Comments
    Error handling in SQL Server 2008 needs careful implementation. Here is the pattern the Microsoft "Oslo" Repository uses. It is based on experimentation and best practice guidance from the SQL Server team and other SQL Server experts. ...
  • Anthony Bloesch's Web Log

    Hierarchies (trees) in SQL Server 2005

    • 18 Comments
    There is much debate about how to implement hierarchies (trees) relationally in SQL Server. I decided to test the main techniques out and see which performed best for my application....
  • Anthony Bloesch's Web Log

    Adding intellisense and refactoring to the T-SQL editor

    • 18 Comments
    One of the features I would like to see in our tools for SQL Server is a T-SQL language service. This would give customers features like intellisense, text completion, refactoring (e.g. renaming a table everywhere at once), error highlighting (as in Word’s red squiggly underlining), and host of other features that C# and Visual Basic have in Visual Studio 2005. Unfortunately, implementing a language service for T-SQL requires solving some difficult problems....
  • Anthony Bloesch's Web Log

    Uppercase or lowercase T-SQL keywords

    • 10 Comments
    I have been rethinking using uppercase letters for T-SQL keywords. Readability research shows that uppercase keywords are harder to read and experience shows they are harder to type. Is it just tradition that keeps T-SQL developers uppercasing keywords?...
  • Anthony Bloesch's Web Log

    Surrogate keys

    • 67 Comments
    After you have been building databases for a while, surrogate keys become second nature but many people do not know about them so I thought I should devote some time to them. In addition, a constant challenge is choosing between using an int or a uniqueidentifier (i.e. GUID) as a surrogate key and I thought this would be a great forum to get some feedback on when you should use one or the other....
  • Anthony Bloesch's Web Log

    SQL Server fragmentation

    • 4 Comments
    We have recently been looking at database fragmentation for real usage of the "Oslo" repository. However, since database fragmentation is a major cause of poor performance I thought a discussion of how to minimize and deal with database fragmentation in SQL Server might be generally useful....
  • Anthony Bloesch's Web Log

    When to use SQL Server 2005's XML data columns

    • 0 Comments
    SQL Server 2005’s support for XML has improved substantially. The new internal representation of XML and XML indexes supports much faster queries and updates. SQL Server 2005 supports querying and updating XML with XQuery, a powerful second-generation DML language for XML. Finally, SQL Server provides good support for converting between and relating relational and XML based data. Now that XML data has such good support in, the choice of when to use an XML column and when to use standard relation...
  • Anthony Bloesch's Web Log

    Instead of triggers over views (part 1)

    • 1 Comments
    Views are useful for creating a business entity based view data while allowing for an efficient logical schema. SQL Server’s instead of triggers allow many of these views to be updatable. Here are some experiments that show how to write them and some of their properties. ...
  • Anthony Bloesch's Web Log

    Bulk loading data with IDataReader and SqlBulkCopy

    • 0 Comments
    Introduction Often large amounts of data need to be quickly loaded into a database. A common approach is to fill a DataTable and use the SqlBulkCopy class to load the data. The problem with this approach is that the data set must be materialized in...
  • Anthony Bloesch's Web Log

    Sqlcmd's cool new facilities

    • 1 Comments
    Sqlcmd is the SQL Server 2005 replacement for osql which in turn is being deprecated in SQL Server 2005. An irritating aspect of osql is that it lacks basic macro facilities. A macro processor like M4 could be used to preprocess the script but that required deploying M4. So I was excited when I learnt that sqlcmd had macro like features built-in....
  • Anthony Bloesch's Web Log

    Paging SQL Server result sets

    • 0 Comments
    Paging through SQL Server result sets is an approach for reducing the network and client resources used to display large result sets. Here is a discussion of the merits of three approaches including their relative performance....
  • Anthony Bloesch's Web Log

    Testing strings for equality counting trailing spaces

    • 1 Comments
    The SQL standard requires that string comparisons, effectively, pad the shorter string with space characters. This leads to the surprising result that N'' ≠ N' ' (the empty string equals a string of one or more space characters) and more generally any string equals another string if they differ only by trailing spaces. Here is a discussion of various work arounds....
  • Anthony Bloesch's Web Log

    Finding and stopping rogue SQL traces

    • 1 Comments
    Introduction A common cause of mysterious performance issues are traces that have been left running. Naïve use of traces can leave many traces running—traces that are slowing down the application by consuming critical resources. This happens...
  • Anthony Bloesch's Web Log

    SQL Server performance investigation

    • 4 Comments
    Introduction I frequently help teams inside and outside of Microsoft investigate SQL Server database performance issues. As well, I monitor some large internal SharePoint databases to understand their performance characteristics and to make sure we...
  • Anthony Bloesch's Web Log

    Database design made easier

    • 80 Comments
    Compared with the development experience for, say, C# and Visual Basic the T-SQL development experience is terrible—no intellisense, refactoring, unit testing, code coverage or even good error reporting. Yet many developers spend large amounts of time building T-SQL code. SQL Server comes with a great set of tools for DBAs and for operational management but the T-SQL development experience is not even close to what I get with C#. ...
  • Anthony Bloesch's Web Log

    How should database models be persisted?

    • 3 Comments
    The conventional way to persist the semantic data in a graphical database design/modeling tool is as a single binary file. This is easy to implement but does not mesh well with the text oriented tools developers typically use (e.g. editors, source code control systems, file diff and file searching utilities). In addition, if something goes wrong recovering your data is difficult. I think a better way to persist semantic information is as a small number of T-SQL creation scripts....
  • Anthony Bloesch's Web Log

    Paul Flessner talk

    • 7 Comments
    Paul Flessner is one of my favorite VPs. Here is a recent high level talk that Paul and others gave on SQL Server 2005 http://www.microsoft.com/seminar/shared/asp/view.asp?url=/seminar/en/20050906_isvconnect/manifest.xml&rate=2. The talk is for ISVs that sell database based applications but it is nice high level look at SQL Server 2005 and the thinking behind its development....
  • Anthony Bloesch's Web Log

    T-SQL casing conventions feedback

    • 1 Comments
    Thanks everyone for the great blog and email comments on how we should deal with T-SQL casing conventions. Here is a summary of that feedback....
  • Anthony Bloesch's Web Log

    Building software for a global mass market

    • 1 Comments
    In the comments to my intellisense blog entry people asked a reasonable question: why not just ship some of the imperfect T-SQL intellisense Microsoft developers have built internally and thus help our customers be more productive. I thought an explanation might give you some interesting insights into how Microsoft works and what it takes to build software for a global mass-market....
  • Anthony Bloesch's Web Log

    Large tuple uniqueness constraints in SQL Server

    • 1 Comments
    SQL Server 2008 limits unique constraints to 900 bytes of data per tuple. Here is a technique for enforcing uniqueness over larger tuple sizes....
  • Anthony Bloesch's Web Log

    Oslo repository

    • 2 Comments
    The central challenge of repository implementations is how to provide support for an extensible list of metadata schema. The most common solution is to use a universal schema (e.g. an entity table, a property-value table and a relationship table). This makes writing tools like repository browsers easier but at a significant cost. Here I discuss an alternative approach used int the "Oslo" repostory....
  • Anthony Bloesch's Web Log

    An insiders guide to giving feedback to Microsoft

    • 1 Comments
    I have worked on both sides—giving feedback to Microsoft and receiving it from customers. Here are the best ways I know of to have your voice heard....
  • Anthony Bloesch's Web Log

    Favorite keyboard shortcuts

    • 1 Comments
    Here are a list of my favorite shortcuts in US Letter docx, A4 docx, US Letter XPS and A4 XPS formats. I have included Windows 7, Internet Explorer 8 and SQL Server Management Studio 2008 shortcuts. ...
  • Anthony Bloesch's Web Log

    The n habits of highly effective developers

    • 1 Comments
    There is a famous book “The Seven Habits of Highly Effective People” by Stephen R. Covey, I have been thinking about what are the n habits of a great developer. Here is my list....
  • Anthony Bloesch's Web Log

    Top 25 most dangerous programming errors

    • 1 Comments
    The paper “Top 25 Most Dangerous Programming Errors”is an interesting study of the families of programming errors that lead to security issues. From a T-SQL perspective, however, the paper seems off mark so here is a list of Common SQL specific errors I was able to extract from their database. ...
Page 1 of 2 (30 items) 12