J.T. is not well liked amongst the developers at his organization. As a Database Administrator, it's J.T's job to make sure that database structures and queries maintain data integrity and do not put an unnecessarily load on the server. This often gets in the way of the developers, who prefer to think of the database as a giant dump site where data gets thrown and is rummaged through to be retrieved. Things like "indexes," "valid data," and "naming conventions" are merely obstacles put in place by J.T. to make their life harder.

Generally, the submission-review-rejection procedure happens once or twice with most of the developers. But one particular developer -- a newly hired ".NET Wizard" named Frank -- turns the procedure into a daily cycle that drags on for several weeks. Following is Frank's reply to the first in a chain of rejections on a project that Frank was leading up ... 

J.T.,

> I cannot find the "DtxSurveys" table you asked to import this
> into. Are you sure this is the right table?

This is a new table we need. Please just use DTS to import the 
CSV file I sent and SQL Server will generate the table and the 
columns. That's how we're doing it in dev.

Frank

J.T. replied, explaining explained that, per the database development guidelines, he must submit a CREATE TABLE script that explicitly defines the table. 

J.T.,

Fine, here is a script:

CREATE TABLE [DtxSurveys] (
[Status] varchar (8000) NULL, 
[Resp] varchar (8000) NULL, 
[Last] varchar (8000) NULL, 
... snip ...
[Supervisor_Name] varchar (8000) NULL, 
[Supervisor_CUID] varchar (8000) NULL, 
[Supervisor_UID] varchar (8000) NULL, 
[File_Data_Date] varchar (8000) NULL)

Frank

J.T. wasn't quite sure where to begin. The script was useful, as in a catch-all dumpster sort of way. He replied to Frank explaining that the table had no primary key, no typed data, and exceeded SQL Server's maximum row size of 8060 bytes. Frank wasn't too pleased and replied back: 

J.T.,

When we run the script in dev, all we get is a warning that 
the row is too long. It's not an error, but whatever. Here
is the revised script:

CREATE TABLE [DtxSurveys] (
[Status] [nvarchar](255) NULL,
[Resp] [nvarchar](255) NULL,
[Last] [nvarchar](255) NULL,
... snip ...
[Supervisor_Name] [nvarchar](255) NULL,
[Supervisor_CUID] [nvarchar](255) NULL,
[Supervisor_UID] [nvarchar](255) NULL,
[File_Data_Date] [smalldatetime] NULL)

Frank

It was a slight improvement, as in a catch-almost-all garbage can sort of way. The only thing Frank changed was VARCHAR(8000) to NVARCHAR(255) and the File_Data_Date field. I'll spare you the rest of the back-and-forth on the CREATE TABLE script, but suffice it to say that it took several more revisions before it represented the actual: "Status" was a single letter, "Supervisor_UID" was a globally unique identifier, "Universal_ID" was an eight-character numeric identifier, etc.

I'll leave you with one of the last things that Frank sent over for review. It was the following query: 

SELECT Product_ID, Product_name, 
case WHEN 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) is null 
then 0 
ELSE 
      (SELECT SUM(ProductRequest.ProductFaceValue) 
      FROM Requests WHERE (ProductRequest.ProductID = Product_ID) 
      AND (ProductRequest_createdByUserUID = @uid) 
      GROUP BY ProductRequest_productName) 
end 
as TotalDollarAmount FROM Requests WHERE (product_ID IN (@pid))

When J.T. mentioned that they will have to optimize the query because it ran for 2000 milliseconds, Frank explained that it's already optimized and can't run any faster. J.T. updated the query to use an ISNULL and increased the run time to 52 milliseconds. It was a small, 3800% decrease.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!