What is the RIGHT technology?

One of things I strongly believe in is using the right technology for solving the right problem. You can often find (understandably) that IT professionals look for solutions using technologies they understand best which can lead to inappropriate decsions being taken. "When all you have is a hammer....".

However, just how do you define the right technology?

An analogy. Whats the best way of buying music? Is it online or in HMV (other retail outlets are available)? Now, for price and convenience then online is pretty tough to beat. But what if you don't have a computer or don't know how to use one? There may be a premium associated with buying in the high street but its still the best understood method there is - everyone knows how to buy from a shop.

I've been involved with a particular application written entirely on SQL Server technologies and its an impressive implementation. Its fast and reliable and for someone familiar with the technology entirely supportable. However, its not me that has to use it. The "customer" has limited technology skills and yet is tasked with driving the application with recurring difficulty and as a consequence, there is a feeling that the application is not robust. The fact is, that the application is entirely robust, but is not built for a technology-light person to use.

Previous to the SQL implementation, there was an Excel equivalent which was less robust and performant. However, the users fully understood how to work it and were much more comfortable with it even at the cost of longer runtime. Asked to use its Excel predecessor, I would have probably claimed it not robust as a result of not being comfortable using that technology. Same issue, different technology.

We need to make the right technology choices and this doesn't just mean what is the best technology for the job. First and foremost, applications need to be fit for purpose and usable by the customer even if thats at the expense of the most elegant technology solution. If customers have no experience or understanding of a particular technology, then we shouldn't be building solutions that require that skillset. Simple.

T-SQL: The complete(?) guide to XML

As this blog is as much of a resource for me as it is for anyone else, I thought i'd share a link to a superb article written by Jacob Sebastian on using XML in SQL Server.

http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

Over the past few years, I've found myself using XML in SQL Server applications more and more frequently both in ETL and in database tables. I'm not sure if this is a result of me becoming more lazy, my database design skills degrading or whether I'm just embracing the technology that MS has provided but whatever the reason, i find it incredibly useful.

T-SQL: Nonclustered Primary Keys

In the majority of SQL database applications I have worked with, surrogate keys are prevalent. Essentially, IDENTITY fields are used to define unique rows rather than working out which fields define the PRIMARY KEY for a table. I have no problem with this in principal and use this technique often myself, for example in Logging/Audit tables where I follow design principals that say every table should have a PK.

By default, when you define a primary key it is created as a CLUSTERED index. However with surrogate keys, the field is rarely used in queries and so essentially you have "wasted" your clustered index - in my view, your most prized index possession due the fact there can be only one per table. Therefore, I strongly recommend to create any primary keys as NONCLUSTERED which will allow you to make judicial use of your clustered index on columns which may benefit more. Of course, there are caveats to this, one example being if you are intending on creating XML indexes which requires that the table has a clustered primary key.

Futher to this, when creating tables a good habit I have got into is to create your table without defining constraints in the DDL and then adding the constraints as separate DDL statements. This reminds you to be a lot more explicit in your naming conventions and constraint/index definitions rather than relying on SQL Servers defaults.

An example below:

CREATE TABLE [dbo].[tblAudit](
[Audit_ID] [int] IDENTITY(1,1) NOT NULL,
[Audit_Type_ID] [int] NOT NULL,
[Date_Created] [datetime] NOT NULL,
[Created_By] [nvarchar](255) NULL,
[Audit_Description] [nvarchar](255) NULL
)
GO
ALTER TABLE [dbo].[tblAudit]
ADD CONSTRAINT [PK_tblAudit_AuditID] PRIMARY KEY ([Audit_ID])
GO
ALTER TABLE [dbo].[tblAudit] WITH CHECK ADD CONSTRAINT [FK_tblAudit_AuditTypeID] FOREIGN KEY([Audit_Type_ID])
REFERENCES [dbo].[tblAuditType] ([Audit_Type_ID])
GO
ALTER TABLE [dbo].[tblAudit] ADD CONSTRAINT [DF_tblAudit_DateCreated] DEFAULT (GETDATE()) FOR [Date_Created]
GO