Birthday Query in VFP

It's a common request to get a list of people whose birthday (or an anniversary date) falls into specified date range w/o regard to the year.

Birthday Query in MS SQL Server

It's a common request to get a list of people whose birthday (or an anniversary date) falls in the specified date range w/o regard to the year.

AT and RAT UDFs - Search a character expression for the occurrence of another character expression


T-SQL has CHARINDEX and PATINDEX functions that return the position of one character expression in another. The ufn_AT and ufn_RAT functions add the ability to search for specified occurrences and to search from right to left.

Queries and functions which accept alias as parameter


The functions such as DELETED(), RECNO(), etc., which accept an optional workarea alias, could yield unexpected results when used in queries incorrectly.

Queries and Filtered Cursors


clause can create a filtered cursor under following conditions:

  • Only one table is referenced by the query
  • WHERE condition is fully optimizable and can be represented by SET FILTER TO command
  • In the Select list either '*' specified (all fields) or subset of the fields from the table
  • No additional clauses, like DISTINCT, ORDER BY, GROUP BY, etc., are present
  • SET EXACT and SET ANSI settings match

Week number in a Month


Mline UDF - Return specific line


The ufn_Mlines() UDF for SQL Server is similar to the VFP Mline() function but w/o third parameter.
It's more an example than finished UDF. I didn't do much testing on it.

IsNumericEx UDF - Data type aware


The built-in SQL Server ISNUMERIC() function determines if character expression can be converted to one of the numeric types. The ufn_IsNumericEx() function accepts the second parameter - the data type to convert to and checks also for characters illegal for that data type. It doesn't check if the numeric value is in the range for specified data type though.

How does SQL Server Management Studio move columns?


SSMS (SQL Server Management Studio) does not use any kind of wizardry as one might think. It creates a new table, moves the data, drops the original table and renames the new table to the original name.

View log usage in SQL2005 using Dynamic Management objects


SQL Server 2005 introduces a number of Dynamic Management objects. One of these is dm_os_performance_counters which gives you access to SQL Server Performance Counters that were only available through Performance Monitor. Several of the counters are related to the log so we can see the log usage for databases from within SQL Server.