Mirroring table changes through DDL triggers

Let’s say that we have an application that tracks and reports customer bills. Customers are able to view reports based on their bill. As time goes on, the customers have a need to create adjustments to their bill. It should be simple enough to add some identifying data to the bill to determine which bill is an adjustment, right? Well, not quite. It turns out that the Bill table is being loaded automatically on a regular (hourly) basis by an SSIS package.

Links For The Week

SQL SERVER Scripting DBA Actions – Dan Jones (Microsoft SQL Server team member) has put together a great overview of his tips and techniques for automating DBA tasks. This comes as a direct result of sitting in on the panel discussion on automation from PASS. Summer Camp for DBA’s (#SQLPASS) is over for 2008 – TJay Belt summarizes what he got out of PASS and how we were all able to use twitter to keep up to date on conference happenings.

Custom Sorting ArrayLists

A co-worker approached me with the problem of sorting an ArrayList of ArrayLists. Normally you might handle this in a DataTable or entity collection in your particular ORM. In this case, this was the data structure that was available to sort:\[\[sortorder\]\[type\]\[message\] \[sortorder\]\[type\]\[message\] \[sortorder\]\[type\]\[message\]\]Knowing a little bit about how ORM tools work, I made the guess that they implement a custom comparer to perform this functionality, so I took a look at what it would take for this instance.

Creating Dynamic Where Clauses

Sometimes you just have to be able to dynamic search criteria in a stored procedure. What’s going to be the best way to accomplish this? You could use dynamic SQL and run the risk that you won’t have compiled execution plans, syntax highlighting, or readable SQL code. That’s not really an elegant solution. Let’s get started with a User table: CREATE TABLE [dbo].[User]( [UserId] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, [EmailAddress] [varchar](255) NOT NULL, [Username] [varchar](50) NOT NULL, [Password] [varchar](256) NOT NULL, [PasswordHint1] [int] NOT NULL, [PasswordHint1Question] [varchar](255) NOT NULL, [PasswordHint1Answer] [varchar](50) NOT NULL, [PasswordHint2] [int] NOT NULL, [PasswordHint2Question] [varchar](255) NOT NULL, [PasswordHint2Answer] [varchar](50) NOT NULL, [FailedLoginAttempts] [smallint] NOT NULL CONSTRAINT[DF_User_FailedLoginAttempts] DEFAULT ((0)), [AccessLevelId] [int] NOT NULL, [ClientID] [int] NOT NULL, [SiteID] [int] NOT NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [UserId] ASC ) ); The goal here is to be able to SELECT a user by username for a combination of ClientId and SiteId, but at least one must be present.

Links for the Week

Brent Ozar provided the inspiration for this blog post and the others that will follow. Essentially, the goal is to put together a list of the most interesting/entertaining links that I’ve found in the last week. If you’re easily offended by SQL Server, I suggest you turn away now. 5 Quick Tips for the Query Using the ‘Wrong’ Index – Quick tips from Jason Massie about making sure the ‘correct’ index is being used by the query engine.

Avoiding Cross Database Ownership Chaining

Cross database ownership chaining is bad. Very bad. How bad? Despite being included in SQL Server 2005, this feature is disabled by default. SQL Server 2005 Books Online goes on to say “Setting cross db ownership chaining to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting.

Cursing at cursors

Cursors. Many database developers have to suppress a shudder of horror when they open up a stored procedure only to find a cursor lurking inside. They certainly have their place in the database developer’s arsenal of tools, but they can usually be rewritten as an easily understood set operation. I’ve spent the first three days at my new client learning the database schema and migrating four load routines from the old to the new database schema.

Get dates quickly with SQL

That’s right, now you too can use T-SQL to get dates quickly! Today at the client I came across the following chunk of SQL in a where clause: dateadd(m,-24,(cast(datepart(m,getdate()) as varchar(2))+'/1/'+cast(datepart(yyyy,getdate()) as varchar(4)))) It’s used to return the first of the month two years ago. So, for today, this would return 2006-09-01 What’s wrong with this? Well, in this query there are two string concatenations, two explicit conversions from an INTEGER to a VARCHAR, and an implicit conversion from VARCHAR to DATETIME.

CodeThinked | What is your strategy for becoming a better developer?

My amazing co-worker pointed out a great post from Justin Etheredge onstrategies for becoming a better developer. Self-improvement is a huge thing for me, both personally and professionally. I like both Justin and Rick’s ideas – Emulate, Get Involved, Read, and Stretch. My strategies tie these all together and are pretty simple, really: Learn and Teach. Teaching I’ve gained the greatest understanding of programming by sharing my knowledge with other developers.

Shingling – it’s not just for roofers!

I was catching up on the Information Architecture Institute mailing list and some feed reader backlog when I came across the concept of shingling. Seeing as how I have never heard of this term in the 8+ years I’ve been working, I decided it was high time that I learned about it. In essence, shingling seeks to solve the problem of indexing large quantities of data: How can you tell if two pieces of content are the same?