Justin Cooney

Web Development Tips and Examples

recent posts

about

I am a Senior Applications Programmer / Analyst with years of experience developing enterprise solutions using the Microsoft technology stack including C#, VB.NET, ASP.NET, AJAX, IIS and SQL Server. I specialize in Web application development with a focus on building secure systems, integrating applications, and designing robust database structures.

  • SQL Server: How to Query Version and Server Information

    There are three commands that can be used in SQL Server to query version information or information about the server hosting SQL Server. These commands can be run in SSMS directly, or can be used in code, for example to tailor queries for a particular database version.

    The commands are xp_msver, @@Version, and sp_server_info. I have successfully used all of these commands in SQL Server 2000, SQL Server 2005, and SQL Server 2008.

    Here is an example of their syntax:

    EXEC xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory",
"ProcessorCount" ,"FileDescription"

SELECT @@Version 'SQL Version'

EXEC sp_server_info

    The output of each of these commands is different: (more…)

  • SQL Server: Global Temporary Tables

    Global Temporary tables are defined on creation by adding the double pound sign (##) as a prefix. Unlike temporary tables they are available to all SQL Server sessions until the table is destroyed/the creating session goes out of scope.

    Global Temporary tables are similar to regular temporary tables in that they exist in tempdb and cause less locking and logging than permanent tables. To boost query performance when working with small data sets (under 1000 rows) it is advisable to use table variables rather than temporary tables since  table variables run within server memory and are not created in tempdb. See my article covering this for further details.

    Temporary tables in general are useful when needing to work with data on a temporary basis. This is especially useful when analyzing data before writing a production level query. Although using temporary tables for short term storage may seem convenient, it is usually best practice to convert these into a single query using joins or sub-tables for performance reasons.

    (more…)

  • SQL Server: When to Use the TSQL WITH NOLOCK Hint

    MS SQL Server allows using the TSQL hint ‘WITH (NOLOCK)’ to the joins of SELECT statements. At times the WITH (NOLOCK) hint can be justified and useful, but it comes with inherent dangers that are often ignored or not understood.

    The WITH (NOLOCK) hint is often overused or is used when it should not be. In many cases adding the hint is done to circumvent problems associated with poor database design and/or poor query syntax.

    The typical reason for using the WITH (NOLOCK) SQL hint is trying to avoid having a query hang if there are database deadlocks. In theory if a table is deadlocked, then a SELECT statement can hang indefinitely, which is certainly not desired behavior.

    (more…)