Gerunds

The way to implement a "many-to-many" relationship requires an additional table be created consisting of the primary keys from each of the entities. This additional table goes by many different names depending on who you talk to.

Association table
Bridge table
Cross-reference (xref) table
Intersection tables
Join table (perhaps most common)
Junction table
Link table, link entity
Many-to-many relationship tables
Map table
Reference table
Relationship table
Swing table


Probably the most proper (being a mathematical model) but least used is "Gerund" -- so named by E.F. Codd, creator of the relational model. This term applies because an entity is functioning as a relationship.

Don't Criticize Code

Editorial - Don't Criticize Code
By Phil Factor
SQLServerCentral.com

After many years spent supervising development teams, I've come to recognize warning signs of problems ahead. There are many such signs, but the following is one that always makes me cringe: a developer looks at code that he or she is assigned to maintain, and says something like "Who one earth wrote such stupid and xxxx (unstructured/ mindless/ unintelligible/etc.) code?"

There is a developer showing his inexperience. Only adolescents writing their first application in some exciting new computer language in their bedroom ever believe that it is possible to write perfect code, or even that they are capable of doing it. Intensive commercial experience as a developer normally grinds off the idealism and naivety that inspires this attitude towards legacy code.

Never is it a good idea to criticize legacy code, let alone the poor developer responsible for it, particularly if you don't know the history of the application and the circumstances of the code being written. If you don't show the code due respect, you are in danger of refactoring it into something worse. You probably aren't aware of the circumstances under which the code was written, or the real brief given to the programmer. The pressures of coding in a commercial development project are difficult to describe, but it is perfectly natural under such conditions to take decisions that, in retrospect look daft, but which actually saved the project.

Legacy code is something I pore through with eagerness, as if it were a rich historical archive; even if it merely illustrates the coder's drift into lunacy. Many times, when doing so, I have been jolted out of a growing sense of contempt by coming across a technique or insight that is brilliant. This real sense of humility, when faced with the results of human endeavor under pressure, always served me well in my work as a consultant. When you've been called in to fix a project that is about to hit the wall, making any suggestion of criticism is probably the least helpful thing you could possibly do. Beyond the easy job of fixing things, comes the more difficult trick of tactfully attributing their original predicament to a cruel act of Fate.

For anyone eager for a long-term career in IT Development, humility and tact are probably more important habits to acquire than demon coding-skills.

SQLServerCentral.com

Title Case / Proper Case

CREATE FUNCTION [dbo].[fnTitleCase]( @text AS varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @Reset bit;
   DECLARE @Ret varchar(8000);
   DECLARE @i int;
   DECLARE @c char(1);

   SELECT @Reset = 1, @i=1, @Ret = '';

   WHILE (@i <= len(@Text))
    BEGIN
       SELECT @c = substring(@text,@i,1),
                   @Ret = @Ret + case when @Reset=1 then upper(@c) else lower(@c) end,
                   @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
                   @i = @i +1
    END
   RETURN @Ret
END

3rd Party References

In Visual Studio I usually get errors when using 3rd party controls due to the assemblies not being deployed along with the application.

To resolve this, I put copies of the control assemblies in a sub-folder of the project called "References" and then add those files to the references in the project references in the solution explorer using browse.

You can then go to the properties page of the reference and set "Copy Local" to true. This will ensure that the module gets deployed to the "bin" folder of the app.

Changing Collation on Columns

Had a problem with this after importing data from one database to another using "SELECT INTO" over a link. A co-worker developed this, so kudos go to him...

declare @table_name varchar(50)
declare @column varchar(50)
declare @datatype varchar(50)
declare @nullornot varchar(15)
declare @length varchar(3)
declare @getprops cursor

SET @getprops = CURSOR for

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CASE WHEN IS_NULLABLE = 'YES' 
   THEN 'NULL' 
   ELSE 'NOT NULL' 
   END AS NLL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'Metrics'
 AND COLLATION_NAME = 'SQL_Latin1_General_CP1_CI_AS'
 
OPEN @getprops
FETCH NEXT
FROM @getprops into @table_name, @column, @datatype, @length, @nullornot
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'ALTER TABLE ' + @table_name + N' ALTER COLUMN ' + @column
PRINT @datatype + N'(' + @length + N') COLLATE SQL_Latin1_General_CP850_CI_AS ' + @nullornot
PRINT N'GO'
PRINT N''

FETCH NEXT
FROM @getprops into @table_name, @column, @datatype, @length, @nullornot
END
CLOSE @getprops
DEALLOCATE @getprops
GO