One of the enhancements being introduced in SQL Server 2016 involves handling large record definitions and large object types, VARCHAR(MAX), NVCARCHAR(MAX) and VARBINARY(MAX). While BINARY(MAX) was not specified in the MSDN article, it may be worth reviewing. One of the first things I noticed was the extension of built-in string functions to handle LOB types. Len, LTrim, RTrim and Substring […]

If you’re like me, you probably get a few emails daily telling you that you need to get on the Converged Infrastructure wagon. The trend popped out overnight, and now it seems everyone is getting into the act. Here’s a quote from Wikipedia describing the concept of Converged Infrastructure. Converged infrastructure operates by grouping multiple information technology (IT) components into […]

In a recent editorial I made the statement that a developer should write SQL Code to the best of their ability. If the performance was acceptable, then they should simply go nuts with their implementation. Tommy Petersen reminded me of the folly this statement can generate. He says… “If you are getting the performance you want then go nuts” (Tommy […]

Random record selection is a rather involved topic. You can use random number generators when creating data, and use the randomly created values for selection. You can select records at runtime, randomly assign a value, and select a few meeting your criteria. You can also purchase third party tools that optimize and automate the process for you. And much more. […]

What is a correlated Sub Select? Historically, when it comes to performance, it was a dirty word. Let me give you an example as it will more easily explain what a correlated sub select is. SELECT Customer.Id ,Customer.Name ,Customer.Region — Here Comes the Sub Select ,(SELECT SUM(TotalAmount) FROM Sales WHERE Sales.CustomerId = Customer.Id ) AS TotalSales — Back to the […]

Often when we use tools like Entity Framework we use it as it is designed out of the box, forgetting that you can optimize certain aspects of the underlying SQL engine through the use of our own SQL. You can interact with the database directly through SQL Queries, Queries with Parameters, Stored procedures…basically any valid SQL. What I find even […]

Today I am going into territory where I find I still have much to learn. That is the topic of actually capturing and handling errors. Previously we talked about tracking and notification of errors. Today, we are transitioning to when and how to capture exceptions and errors. I’d say different kinds of programs have different needs. A Service, or long […]

Continuing on the topic of error handling, I thought I would change the direction today, and talk more about error and exception handling as a philosophy, instead of focusing on syntax, etc. There are many purposes for error handling. Two stand out to me as very significant. If something bad happens, disrupting the user’s experience, error handling should tell me, […]

Today I want to start a short series of editorials on the topic of Exception, or error handling. I thought I would start out with SQL Server errors, and then move on later into the topic at large. SQL Server 2005 introduced a Try/Catch error handling syntax allowing us to easily capture errors are perform appropriate actions such as COMMIT […]

If you work with SQL Server for very long, you will probably find yourself looking into how it locks data. Data locks are one of the biggest causes of slow performing queries. There are things you can do to optimize your database performance if you first understand how longing is performed. Here are some high level features of locking to […]