Matthew Pavey | Faith, Family, Code

Before SQL Server 2008, if you needed to store a date you used DATETIME, whether or not you cared about the time. If you needed only a time of day, you still used DATETIME and ignored the date half. It worked, but it wasted space and it made intent unclear.

SQL Server 2008 added types that say what they mean, and they are still the right choice today:

• DATE stores a date with no time component, in 3 bytes instead of the 8 that DATETIME takes.
• TIME stores a time of day with no date, with configurable precision.
• DATETIME2 is the replacement for DATETIME: a wider date range, better precision, and configurable storage.
• DATETIMEOFFSET stores a datetime along with a time zone offset, which matters more than it used to now that so much runs across regions.

The practical advice is simple. For new work, use DATE when you only need a date, TIME when you only need a time, and DATETIME2 rather than DATETIME everywhere else. Microsoft has recommended DATETIME2 over DATETIME for new development for a long time now.

The one thing to watch when moving an existing column from DATETIME to DATETIME2 is rounding. DATETIME rounds to increments of .000, .003, or .007 seconds, and DATETIME2 does not, so values that were being quietly rounded before will stop being rounded after. That is usually what you want, but it can surprise a comparison or a test that was written against the old behavior.

SQL Server has a handy system stored procedure called sp_spaceused that reports the space used by a database or by an individual table. To see it for the whole database:

EXEC sp_spaceused

That returns two result sets. The first has the database name, its size, and unallocated space. The second breaks the size down into how much is reserved, how much of that is data, how much is indexes, and how much is unused.

To look at a single table, pass the table name as the first parameter:

EXEC sp_spaceused 'Orders'

That gives you one result set containing:

• Name, the name of the table
• Rows, the number of rows in the table
• Reserved, total reserved space for the table
• Data, space used by the data
• Index_Size, space used by the table's indexes
• Unused, unused space in the table

Usually what you actually want is this for every table at once, which means running sp_spaceused once per table. You could query the system catalog for a list of tables and iterate with a cursor. The easier route is sp_MSforeachtable, an undocumented stored procedure that takes a command and runs it against every user table in the database. Put a question mark where you want the table name substituted:

EXEC sp_MSforeachtable @command1 = "EXEC sp_spaceused '?'"

That runs EXEC sp_spaceused 'TableName' for each user table, which works but gives you one result set per table. To get it all back as a single result set, create a temp table, let sp_MSforeachtable insert into it, and select from it at the end:

CREATE TABLE #spaceused
(
    name       VARCHAR(128),
    rows       BIGINT,
    reserved   VARCHAR(25),
    data       VARCHAR(25),
    index_size VARCHAR(25),
    unused     VARCHAR(25)
)

EXEC sp_MSforeachtable @command1 = "INSERT INTO #spaceused EXEC sp_spaceused '?'"

SELECT * FROM #spaceused

DROP TABLE #spaceused

One caution worth stating: sp_MSforeachtable is undocumented, which means Microsoft has never committed to keeping it or to its behavior staying the same. It has been there a long time and it is widely used, but it is not something to build production code around. For an ad hoc look at where your space is going, it is hard to beat.

ASP.NET protects forms authentication tickets and ViewState from tampering by signing them. Any modification made on the client or over the network is detected when the server processes the data.

That protection causes a specific problem in a web farm. ViewState generated on server A and posted back to server B will fail validation unless the <machineKey> is the same on every server in the farm or cluster, because the ViewState is signed with a key that is autogenerated per machine.

The exception looks like this:

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or
cluster, ensure that <machineKey> configuration specifies the same validationKey and
validation algorithm. AutoGenerate cannot be used in a cluster.

The solution is to give every server in the farm the same key. Generate a hex encoded 64-bit or 128-bit <machineKey> and put the same one in each server's machine.config, or in the application's Web.config if you do not have machine-level access.

<system.web>
  <machineKey validationKey="..." decryptionKey="..."
              validation="SHA1" decryption="AES" />
</system.web>

You may find older advice, including an earlier version of this post, suggesting enableViewStateMac="false" as a workaround when you cannot change machine.config. Do not do that. Turning off MAC validation means ViewState can be tampered with, and that turned out to be exploitable for remote code execution. Microsoft addressed it in security update MS14-059, and as of .NET Framework 4.5.2 the setting is ignored entirely: ASP.NET always validates ViewState regardless of what the config says.

Matching the machine key across the farm was always the correct answer. Now it is the only one.

I ran into a LINQ problem around building queries dynamically. I could easily write something that was not type safe and not checked at compile time, but that bothered me. The whole point of using LINQ was to keep everything type safe and verified at compile time.

My case involved querying an entity rather than SQL directly, but the same rules applied.

The answer was lambda expressions. Because each Where call returns a new query rather than executing anything, you can build the query up conditionally and only run it at the end:

Public Function Search(ByVal Name As String, ByVal City As String) As IEnumerable(Of MyTable)

    Dim MyEntities As New DbEntities()
    Dim MyQuery    As ObjectQuery(Of MyTable) = MyEntities.MyTableSet
    Dim MyResults  = From x In MyQuery Select x

    If Not IsBlank(Name) Then
        MyResults = MyResults.Where(Function(e) e.Name.Equals(Name))
    End If

    If Not IsBlank(City) Then
        MyResults = MyResults.Where(Function(e) e.City.Equals(City))
    End If

    Return MyResults.AsEnumerable

End Function

Each conditional adds a filter to the query without running it. Nothing hits the database until AsEnumerable is called at the end, so you get one query with exactly the filters the caller asked for, and all of it still type safe.

When programming in SQL you sometimes need to create a temporary table inside a stored procedure. That part is straightforward:

SELECT TrackingGroupID,
       Tag
INTO   #temp
FROM   MyTable

You can then use #temp through the rest of the procedure and drop it when you are done:

-- testing
SELECT COUNT(*) FROM #temp

-- drop temp table
DROP TABLE #temp

But if you also need dynamic SQL, you will run into scope issues. In my case I needed to apply a dynamic filter to a query and store the results in a temporary table so I could do further manipulation, analysis, and grouping on the filtered data.

Normally I prefer table variables for this:

DECLARE @Temp TABLE
(
    TrackingGroupID INT,
    Tag             VARCHAR(50) NOT NULL DEFAULT ''
)

In this case I could not get a table variable to work, so I used a #temp table instead. My first attempt looked like this:

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'SELECT TrackingGroupID,
                   Tag
            INTO   #temp
            FROM   MyTable
            WHERE  1=1 ' + @Where + '
            GROUP BY TrackingGroupID, Tag'

EXEC(@SQL)

-- do further manipulation or analysis on #temp
-- ...

DROP TABLE #temp

Which gave me this:

Msg 208, Level 16, State 0, Line 18
Invalid object name '#temp'.

I knew it was a scoping problem but was not sure how to work around it. The explanation is that EXEC and sp_executesql run dynamic SQL in a new child scope, and any objects created inside that scope are dropped as soon as it closes. The temp table was being created and destroyed inside the dynamic statement, so by the time the outer procedure went looking for it, it was gone.

The fix is to create the table in the outer scope first, then have the dynamic SQL insert into it rather than create it:

-- create temp table in the outer scope
CREATE TABLE #temp
(
    TrackingGroupID INT,
    Tag             VARCHAR(50) NOT NULL DEFAULT ''
)

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'INSERT INTO #temp
            (TrackingGroupID, Tag)
            SELECT TrackingGroupID,
                   Tag
            FROM   MyTable
            WHERE  1=1 ' + @Where + '
            GROUP BY TrackingGroupID, Tag'

EXEC(@SQL)

-- do further manipulation or analysis on #temp
-- ...

DROP TABLE #temp

Now the table exists for the whole scope of the stored procedure, and the dynamic statement is only writing to it.

A global temp table, ##temp, would likely work here as well, though I did not go down that road.

I do not get to dabble in classic ASP much anymore, which is fine with me, but for the first time in years I had to debug some ASP code to sort out an error for someone consuming one of our web services.

The code was simple:

Set MyXmlHttp = Server.CreateObject("MSXML2.ServerXMLHTTP")
MyXmlHttp.open "get", "https://www.example.com/webservice.asmx/GetSites?StudyID=35", False
MyXmlHttp.setRequestHeader "Content-Type", "text/xml"
MyXmlHttp.send()
XmlData = MyXmlHttp.responseText

Response.Write(Server.HtmlEncode(XmlData))

And the error was:

HTTP Error 403.1 - Forbidden: Execute access is denied

Can you spot the problem?

MyXmlHttp.open "GET", "https://www.example.com/webservice.asmx/GetSites?StudyID=35", False

It might not be obvious what changed. Switching the method from "get" to "GET" fixed it.

I wondered whether this was a bug, but Microsoft's documentation confirms it is by design: for ServerXMLHTTP the method parameter is case sensitive and must be entered in all upper case letters. A lowercase verb is not recognized, and the 403.1 you get back gives no hint that the casing is the problem.

Building a genuine data access layer on top of LINQ to SQL raises a question that is harder than it looks: where do you draw the line between business logic and the DAL, and where does the query actually get executed?

The core concern is what the data access layer should return. The argument I find convincing is that it should hand back arrays of entities, or IEnumerable(Of T), and specifically not IQueryable(Of T). Returning IQueryable gives you more rope to hang yourself with. The caller can redefine the query that ultimately goes to the database, the caller ends up executing the query because of deferred execution, and the distinction between the business layer and the DAL blurs until it is gone.

That is the practical problem with deferred execution at a layer boundary. If you return IQueryable(Of T) from the data access layer, nothing has run yet. The query executes later, when something in the business layer or the UI enumerates it, which means your data access layer is no longer the thing deciding what hits the database. Returning List(Of T), IEnumerable(Of T), or an array of entities draws a clear line: the DAL fetches, everything above it works with what it got.

I have experimented with returning all four, and each has advantages and caveats. My research so far leads me to prefer IEnumerable(Of T), partly for the reason above and partly because LINQ itself operates on IEnumerable(Of T). Design your application around it and you find plenty of places where a LINQ query turns out to be an elegant solution.

There is a broader point here about standards, and it is the one I keep coming back to. When developers have to make a judgment call like this during routine development, choosing takes time, and different developers make different choices. The same developer may choose differently from one day to the next. That leads to inconsistency, which costs everyone reading the code later, and in the worst case developers start rewriting each other's work just to match their own preference.

Agreeing on a standard before integrating LINQ into a project seems like a smart move, especially on a team.