Many times, I’ve started with a company as one of their first, if not the first DBA. They’ve acquired enough servers now, with enough data moving around, that they just can’t continue as they are. There’s no shortage of developers to can write stored procedures or SSIS packages, or to write a new page for…
Category: T-SQL
Do what I say, not what I do
You’ll hear a lot of rules and best practices from senior IT people (not just data folks, all of them). You’ll also then see those same people do what they told you not to. I told you not to use NOLOCK even though I use it sometimes. I told you not to shrink databases, even…
Dynamic SQL and the joys of QUOTENAME
I’m a big fan of dynamic SQL. In the past I’ve written a How to, a Best Practices and even a Generic Dynamic SP although that last one was a bit so so in my opinion. Depending on what type of dynamic code you are writing QUOTENAME will be your best friend. Let’s start with…
Too Many Indexes?
Indexes are great. They speed up our queries. In fact, without them relational database systems wouldn’t work. Different indexes work best for different queries. In a system with a lot of queries that means we could need a lot of indexes. Fortunately we can have up to 999 non-clustered indexes per table and one clustered…
RBAR vs Batch
Many years ago Jeff Moden (of SQL Server Central fame) came up with the concept of RBAR. Row-By-Agonizing-Row. At it’s most basic it means you are inserting one row at a time. A more broad interpretation says it’s any type of loop even the type caused by a recursive CTE. And the point? Loops are…
ISNULL & Data Length Handling
[unable to retrieve full-text content]Some folks are not fans of having NULL values in your data. I am not much of a fan myself, however sometimes (all the time) they are a reality in which we have to deal. When working with NULL there are lots of strange behaviors you have to be sure to…
Improving Performance When Querying Multiple DMVs
A couple of days ago I posted a stored procedure (sp_GetAGInformation) which queried multiple DMVs to pull together a bunch of Availability Group information. If you took a look at the code you would see that it used a couple of CTEs (Common Table Expressions). CTEs are a great way to do recursive work, and…
Best practice recommendations for writing Dynamic SQL
Note this is not “Best Practices when USING Dynamic SQL”. These are just good habits I’ve come up with over the years to make sure that when writing my dynamic SQL it breaks less often. Note I don’t say “doesn’t break ever”, dynamic SQL is tricky stuff. The best you are likely to get is…
Extended Properties Are Your Friend
It’s nice to have friends, why aren’t you a friend of extended properties? They can make your like so much easier by helping to document your databases and objects. Take a basic table create statement Transact-SQL CREATE TABLE [dbo].[EatSomeBoogers]( [ID] [int] NOT NULL, [BgType] [varchar](20) NOT NULL, [Size] [smallint] NULL, [Viscosity] [tinyint] NULL ) ON…
Passing SQL Data To Command Line Processes Within SQL Jobs
Wow, that title is a bit of a mouthful, let me give you a scenario to help understand what the business problem was that I ran in to today. We have a production database running on SQL 2008 which contains a CLR procedure that accepts a reportID value, queries some data and writes out to…