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.

  • MS SQL Server: Temporary Table and Table Variable Performance

    Temporary tables and table variables in SQL Server actually perform quite differently under different circumstances.

    Temporary tables are decelared as: CREATE Table #TableName ( id INT )

    Table variables are declared as: DECLARE @TableName TABLE ( id INT )

    Temporary tables are transaction bound whereas table variables are not. This means that if a transaction in which a temporary table or a table variable are instantiated and populated is rolled back, the temporary table will still exist while the table variable will no longer exist.

    Along the lines of rolling back transactions, if a temporary table and table variable are declared outside of the transaction, but populated within the transaction that is later rolled back, the temporary table would not have the data it was populated with. On the other hand the table variable would retain the data that was added during the rolled back transaction.

    The bottom line is that transaction logs are not kept for table variables but they are for temporary tables, so table variables are separate from the transaction mechanism. Since table variables are not part of the transactions mechanism, or logging, or locking they tend to be lighter weight components that can perform better than temporary tables.

    (more…)

  • SQL Server 2008 – Using TRY – CATCH Blocks

    With SQL Server 2008 Microsoft has added .NET like error handling to SQL Server by adding support for TRY – CATCH blocks. I like this new feature since the old @@ERROR syntax was quite unwieldy and the TRY – CATCH just seems more intuitive to use.

    Here are some of the features of the new TRY – CATCH blocks in  SQL Server 2008:

    (more…)

  • tSQL to Show Key Word or Phrase Frequency

    Sometimes it is useful to be able to search for the number of occurrences of a particular word or phrase within a table column. Although one can use a cursor and calculate the word count in a loop it much more efficient to do the calculations within a single statement.

    Here is the Transact-SQL query that will show the number of times that the word WebControls shows up in the ErrorMessage column of an error logging table:

    SELECT
(LEN(errormessage) - LEN(REPLACE(errormessage, 'webcontrols', '')) ) / LEN('webcontrols')
FROM dbo.ErrorLogs