How to create molecular queries from atoms in SQL Server

"Guide" This article describes several different strategies for creating molecular queries from atoms in SQL Server.

Each SQL Server developer has its own preferred method of action. My method is called molecular inquiry. These are queries that are grouped together by atomic queries, through which I can process a table. By combining atoms together, you can build molecules. Of course there will be limits (what chemists call the valence), but in general this principle still applies.

In this article, I'll explore several variations of this strategy. I start with the basics (that is, the most detailed content) and then step deeper. To give you an idea of the flexibility of this approach, I will use several techniques at different levels. (Warning: This is not the only solution, I'm just talking about some viable options.) ）

I started with the commonly used database Northwind (although I copied it to northwind_new in order to preserve the original, actually this is the database I used.) In my copy, I make these important changes.

I removed the composite primary key, added a new column called PK, and set it to the identity column.

I added a computed column called Extendedamount.

USE [Northwind_New]
GO
/****** Object: Table [dbo].[OrderDetails_New]
Script Date: 08/23/2006 16:15:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATETABLE [dbo].[OrderDetails_New](
[OrderDetailID] [int] IDENTITY(1,1)NOTNULL,
[OrderID] [int] NOTNULL,
[ProductID] [int] NOTNULL,
[UnitPrice] [money] NOTNULL,
[Quantity] [smallint] NOTNULL,
[Discount] [real] NOTNULL,
[ExtendedAmount] AS([Quantity] * [UnitPrice] *(1 - [Discount])),
CONSTRAINT [PK_OrderDetails_New] PRIMARYKEYCLUSTERED
(
[OrderDetailID] ASC
)ON [PRIMARY]
)ON [PRIMARY]