Archive for the ‘Code’ Category

Fun with NEWSEQUENTIALID()

We’re thinking about converting the constrain on an existing column in a table with billions of rows of data.  The column is a uniqueidentifier serving as a nonclustered primary key; we’re planning on replacing the NEWID() constraint to a NEWSEQUENTIALID().  What is challenging me is my concern over primary key collisions; will the new default [...]

July 31, 2009 · stuart · One Comment
Posted in: SQL, SQL Server, SQLServerPedia Syndication

You CAN teach an old dog….

This is more of a sticky note kind of post, to remind me to use a better syntax in the future.  Maybe it’ll help some of you as well.  A long time ago, when I was starting to write T-SQL, and I needed to extract only the date from a DATETIME column, I used to [...]

July 24, 2009 · stuart · One Comment
Posted in: SQL, SQLServerPedia Syndication

Partitioning computed columns

For a project at work, I’ve been asked to help optimize our ETL process.  Part of that involves taking an existing table, and partitioning it along two existing columns.  Since the partitioning process only supports partitioning on one column, the obvious choice is to use a computed column.  I ran into some issues along the [...]

July 22, 2009 · stuart · No Comments
Posted in: SQL, SQL Server, SQLServerPedia Syndication

SCRUM, Source Control, and the SQL Server Developer (Part 2)

So my last post focused on the modifications my shop has made to our implementation of Scrum without a lot of details about how we manage our code changes.  This post is intended to explain how we set up source control to support the scrum process. Source control is one of those poorly-defined practices; it’s [...]

May 20, 2009 · stuart · No Comments
Posted in: SQL, SQL Server, SQLServerPedia Syndication, VSTS:DB

SQLSaturday #7: Birmingham, AL May 30, 2009

I’ve been so busy with all of my other projects at work that I’ve forgotten to really tout this.  On May 30, I’ll be presenting at the Birmingham SQLSaturday (hosted by the Steel City SQL User Group).  My talk is titled “Confessions of a Data integrator”, and will focus on dealing with other people’s database [...]

May 17, 2009 · stuart · No Comments
Posted in: Conferences, SQL

SQL Server & XML: links of interest

I’ve been working on a project recently that requires using XML in SQL Server, so I’ve been searching the web to find references. Books Online is unusually obtuse on this subject (the samples are sub-par for this complex subject), so I’ve been trying to find good examples of how it works. Here’s some of the [...]

May 14, 2009 · stuart · No Comments
Posted in: SQL Server, SQLServerPedia Syndication, XML

Death by a thousand cuts…

This is has been an awful week; things have just not gone as planned from the get-go.  I’m gonna run through several issues in this post, and perhaps someone will find some value in them; lessons learned may help others avoid the pain of my ways.   VSTS: DB Pro This week, I’ve spent way [...]

May 14, 2009 · stuart · No Comments
Posted in: SQL, SQLServerPedia Syndication, VSTS:DB

Call me a believer: Microsoft SSAS

This week, I’ve spent most of my days working with Microsoft’s Premier Support; they’re on site at our office, showing us the basics of SQL Server Reporting Services and SQL Server Analysis Services. I must admit that I was NOT looking forward to this week, because I felt like we were going to spend a [...]

April 1, 2009 · stuart · No Comments
Posted in: Code, Education, SQLServerPedia Syndication

Building ranges using a dynamically generated numbers table

We use a queueing metaphor for dataloads for the majority of our imports, and it’s been a challenge to figure out how to generate dynamic ranges to add to the queue. In other words, we want to tell our system to go and pcik data from a source that has an id value >x and [...]

January 25, 2009 · stuart · 2 Comments
Posted in: SQL

Auto generated SQL Server keys – uniqueidentifier or IDENTITY – a rebuttal

I found this article by Armando Prato from facility9′ssite; for the most part, the article explains the differences between using a uniqueidentifier and integer-based identity values to auto-generate (surrogate) primary key values. However, the conclusion to the article has a logical flaw; can you spot it?: It’s evident that using IDENTITY to auto-generate key values [...]

December 30, 2008 · stuart · 11 Comments
Posted in: SQL