Code Corner

If you're writing an SQL upgrade script that updates a field in your table from NVARCHAR(n) data type to NVARCHAR(max) and the script is designed to run many times - an easy way to avoid multiple table alteration is to check for max character length for that field, i.e.:

IF (SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MY_TABLE' AND COLUMN_NAME = 'MY_COLUMN')<>-1
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ALTER COLUMN [MY_COLUMN] [nvarchar](max) NOT NULL
END

This code checks length of the field which is specific number for standard NVARCHAR and -1 for NVARCHAR(max), and alters column type only if it hasn't already been altered.

Let's say you need to add a column to an existing table that already contains some rows. And this new column needs to be prepopulated with a default value. For example integer nullable column NEW_COLUMN is added to table MY_TABLE. Common approach is to check for column existence, and if it doesn't exist - create and populate it:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL
	UPDATE [dbo].[MY_TABLE] SET [NEW_COLUMN] = 1
END

But this doesn't work - you get "Invalid column name" error. The trick is - you don't need a separate UPDATE statement. ALTER TABLE ... ADD statement has a DEFAULT parameter. You can modify previous code like this:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL DEFAULT 1 WITH VALUES
END

This ALTER statement assigns default value of 1 to the new column. WITH VALUES parameter is important, it populates existing rows with the default value (without it it will be populated by NULLs).

Sometimes I bring information to a couple of my other WordPress blogs via RSS feed. It's a nice feature, allowing you to create several posts at once without manual entry. Unfortunately if RSS feed is broken or improperly formatted it can result in blank posts imported into the blog.

I was looking for a WordPress plugin that would allow me to mass-delete empty posts, but apparently none exist. You can delete posts based on date, tags, category, but not the content. Fortunately if you have access to phpMyAdmin of your MySQL installation - there is a solution. Read more...

Imagine after running a query like this:

SELECT ContactName, Country FROM Customers ORDER BY Country

on Northwind database and getting following result:

ContactName           Country
Patricio Simpson      Argentina
Yvonne Moncada        Argentina
Sergio Gutiérrez      Argentina
Georg Pipps           Austria
Roland Mendel         Austria
Catherine Dewey       Belgium
Pascale Cartrain      Belgium
Anabela Domingues     Brazil
Paula Parente         Brazil
Bernardo Batista      Brazil
Lúcia Carvalho        Brazil
Janete Limeira        Brazil
Aria Cruz             Brazil
André Fonseca         Brazil
Mario Pontes          Brazil
Pedro Afonso          Brazil
Elizabeth Lincoln     Canada
Jean Fresnière        Canada
Yoshi Tannamuri       Canada
...

You're asked to retreive only the 2nd and the 5th group of contacts. How do you do it? Read more...

T-SQL dialect of SQL doesn't have aggregate functions for strings, but there is an easy workaround using magic of XML.

Consider Employees table of the Northwind database. When I run following query:

SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName

I get following result:

Country FirstName
UK      Anne
UK      Michael
UK      Robert
UK      Steven
USA     Andrew
USA     Janet
USA     Laura
USA     Margaret
USA     Nancy

Now I want to combine first names into comma separated strings grouped by country. Read more...

Often there is a need to perform group by operations on in-memory .NET data tables. You can’t run a standard SQL statement and LINQ is not available prior version 3.0 of .NET. To compensate for this shortcoming I put together a small function that works in .NET 2.0. As input parameters it accepts source DataTable, column name to group by and a column name to perform aggregate operation on. It returns a grouped data as data table:

Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable

        'getting distinct values for group column
        Dim dv As New DataView(i_dSourceTable)

        'adding column for the row count
        Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn}) 

        dtGroup.Columns.Add("Count", GetType(Integer)) 

        'looping thru distinct values for the group, counting
        For Each dr As DataRow In dtGroup.Rows
            dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
        Next        

        'returning grouped/counted result
        Return dtGroup
End Function

The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s "ToTable" method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s "Compute" method - Count in this case, but it can easily be replaced with other aggregates.

IsNumeric(data)  is a widely used function in SQL Server programming.  If passed in data is a number - it returns 1, otherwise 0. Supposedly. There're several cases when it returns 1 even though the data is not numeric. Some of those cases are: dollar sign "$", minus sign "-", space, tab. If you try to call Cast or Convert function on the data after that to convert data to number - they will throw an error

An excellent article at SQL Hacks suggest adding "e0" to the data when passing it to IsNumeric function. What it does essentualy is making the number to be in scientific format, e.g 12.34 becomes 12.34e0 (or 12.34 * 1). Which still remains a number. So IsNumeric(12.34e0) returns 1 while IsNumeric($e0) returns 0.

Which works great until actual number in scientific format is passed.  If you try to test for example 1.234e5, IsNumeric correctly returns 1, but by adding "e0" as article suggest IsNumeric(1.2345e5e0) returns 0.

To work around this limitation I simple test for an "e" within passed data. If it exist, I am calling IsNumeric as usual, if it does not - I am adding "e0" to the tested data:

CREATE FUNCTION IsRealNumber (@i_sValueToTest nvarchar(250))
/* This function tests if passed in value a real number */

RETURNS int
AS
BEGIN
       DECLARE @iResult int

      -- if there is 'E' in the value - do a standard IsNumeric test
     IF CHARINDEX('E', UPPER(@i_sValueToTest))<> 0
            SET @iResult = IsNumeric(@i_sValueToTest)
      -- Otherwise do a modified IsNumeric test by adding 'E0' at the end
    ELSE
          SET @iResult = IsNumeric(@i_sValueToTest + 'E0')

        RETURN @iResult
END