more of IEnumerable vs. List

IEnumerable<T> and Array allow for covariance where List<T> does not. A collection of children may be cast to a collection of parents. This is one example of a way of in which IEnumerable<T> is more robust than List<T> in conflict with what I wrote here.

using a case statement when casting to bit from a different type in SQL

Imagine a view crafted like so:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view dbo.EvilInsight
AS
SELECT perso.Name, expos.HasNotSeenEvil, expos.HasNotHeardEvil,
expos.HasNotSpokenEvil
FROM dbo.Person AS perso
INNER JOIN dbo.ExposureToEvil AS expos
ON perso.PersonId = expos.PersonId
GO

 
 

Now imagine the not nullable bit value for HasNotHeardEvil at the ExposureToEvil table is replaced with a nullable DateTime for DateOfFirstListeningToHeavyMetal. (The date would be null for people who had never heard heavy metal genre music or would otherwise record the moment when individuals had first heard evil.) Imagine also that you still need to represent this datapoint at the EvilInsight view as a true or false value dubbed HasNotHeardEvil. You could do so with a case statement like this:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view dbo.EvilInsight
AS
SELECT perso.Name, expos.HasNotSeenEvil,
CASE
WHEN expos.DateOfFirstListeningToHeavyMetal IS NULL then 1 else 0
END as HasNotHeardEvil,
expos.HasNotSpokenEvil
FROM dbo.Person AS perso
INNER JOIN dbo.ExposureToEvil AS expos
ON perso.PersonId = expos.PersonId
GO

 
 

Do not try to instead use a convert statement as given below. This following will return true for false and null for true. This came out in an experiment today.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER view dbo.EvilInsight
AS
SELECT perso.Name, expos.HasNotSeenEvil,
CONVERT(bit, (expos.DateOfFirstListeningToHeavyMetal)) as HasNotHeardEvil,
expos.HasNotSpokenEvil
FROM dbo.Person AS perso
INNER JOIN dbo.ExposureToEvil AS expos
ON perso.PersonId = expos.PersonId
GO

You can't repeat the use statement over and over again in a SQL script consolidated from other scripts.

USE [FOO]

SQL to create a view

This offers:

CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer

Generate Change Script

...is an icon that appears as a scroll with a pen upon it in the "Table Designer" menu of Microsoft SQL Server Management Studio Express. The first time you click it there should be a checkbox for "Automatically generate change script on every save"

running an update query at a view updates the tables that feed the view

I'm just realizing this for the first time. :P

Operations Manager

...is a tool that will crawl a LAN and report on what apps are running on your servers.