Category Archives: T-SQL

The T-SQL flavor of SQL

SQL CASE Statement

T-SQL

Maybe knew of it, but hadn’t had reason until now to use a CASE statement in SQL:

UPDATE Devices

SET InUse = (CASE UserId WHEN 3

THEN 1

ELSE 0 END)

WHERE DevGuid = '94F74150-26B0-4396-982C-675A14A66FAD';

In this situation, there are multiple rows with the same device GUID because more than one user may use the same app on the device (but not simultaneously…unless they’re extraordinarily close). What this query does is get all the rows that share the same device GUID, then sets the InUse bit to true (1) if the UserId matches (3). Any row within that set with a different user ID gets its InUse bit set to false (0). This avoids having to make more than one call to the server.

Computed, int, not null…what does that mean?

SQL Server, T-SQL

I didn’t even know this was possible, but noticed a column in Microsoft’s AdventureWorks sample DB Workorders table with the attributes of “Computed, int, not null” while other similar columns just had “int, not null”. A peek at the CREATE TABLE code:


CREATE TABLE [Production].[WorkOrder](

[WorkOrderID] [int] IDENTITY(1,1) NOT NULL,

[ProductID] [int] NOT NULL,

[OrderQty] [int] NOT NULL,

[StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),

[ScrappedQty] [smallint] NOT NULL,

[StartDate] [datetime] NOT NULL,

[EndDate] [datetime] NULL,

[DueDate] [datetime] NOT NULL,

[ScrapReasonID] [smallint] NULL,

[ModifiedDate] [datetime] NOT NULL,

CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED

(

[WorkOrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO