SQL Server 2014数据访问层

介绍 ( Introduction )

As a person who has always enjoyed finding new and innovative ways to perform tasks more efficiently and effectively with SQL Server, I have endeavored to document some of the more ‘innovative’ ways and means of doing these things in our SQLShack ‘get-togethers’.

作为一个一直乐于寻找新的和创新的方法来使用SQL Server更有效地执行任务的人，我努力在SQLShack的“聚会”中记录一些更“创新”的方式和方法来做这些事情。 。

Today, we shall be looking at one of my favourites, the ‘Data Access Layer’, not to be confused with ‘Data Access Layers’ from the Visual Studio world.

今天，我们将关注我的最爱之一，即“数据访问层”，不要与Visual Studio世界中的“数据访问层”相混淆。

A Data Access Layer (hence forward referred to as a ‘DAL’) for all intents and purposes is a user defined table function. A DAL is capable of accepting arguments to its parameters and in doing so is able to process data for the end user, whether or not any arguments have been passed to its varied parameters. More on this in a few minutes.

出于所有意图和目的的数据访问层（以下简称为“ DAL”）是用户定义的表函数 。 DAL能够接受其参数的参数，并且这样做能够为最终用户处理数据，无论是否已将任何参数传递给它的各种参数。 几分钟后将对此进行更多介绍。

一点历史 ( A bit of history )

When working at a client site around 5 years ago, I was working on a financial project where many of the business folks wrote their own queries. The client had offices around the world and oft times upon arriving at work in the morning (eastern USA), I would find that overnight there were queries from say the ‘Timbuktu’ office that were still running from the night before. In fact one query ran for twenty six hours and yielded four rows. Enough said!

大约5年前，当在客户站点工作时，我正在从事一个金融项目，许多商务人士在其中编写自己的查询。 该客户在世界各地都有办事处，并且通常在早上到达美国东部时间上班（美国东部），我会发现一夜之间，有人问起“ Timbuktu”办事处，但该询问仍从前一天晚上开始运作。 实际上，一个查询运行了26个小时并产生了四行。 说够了！

Under these circumstances we had to find a way to continue permitting the business folks to create their own queries yet not to bring the system to its knees in doing so. Hence the birth of the DAL concept, a well-tuned suite of user defined table functions (tuned by the DBA’s with the end clients in mind).

在这种情况下，我们必须找到一种方法来继续允许业务人员创建自己的查询，而又不会使系统瘫痪。 因此，DAL概念诞生了，它是用户定义的表功能经过良好调整的套件（由DBA在考虑最终客户的情况下进行了调整）。

为什么不使用调整好的视图？ ( Why not use a well-tuned view? )

A DAL is capable of accepting arguments and passing these arguments to the DAL parameters.

DAL能够接受参数并将这些参数传递给DAL参数。

入门 ( Getting started )

Whilst the client had hundreds of millions of rows of data in most tables, with between 100 and 300 fields in each, the sample that we shall be dealing with is considerably smaller. The important point being that what we are now going to look at, is truly scalable. This in my opinion is most important.

尽管客户端在大多数表中都有数亿行数据，每个表中都有100至300个字段，但我们要处理的样本要小得多。 重要的一点是我们现在要看的是真正可扩展的。 我认为这是最重要的。

The table shown above is a great example. It has 149 fields (see the code above).

上表是一个很好的例子。 它有149个字段（请参见上面的代码）。

Having done the proper ‘due diligence’ with the user community and after having a myriad of meetings with the power users, we found that there was much commonality in the queries that they wrote.

与用户社区进行了适当的“尽职调查”，并且与高级用户进行了无数次会议之后，我们发现他们编写的查询存在很多共性。

The simple Venn diagram (above) shows that one or more folks used column B and one (person/group) used only A and another (person/group) used only C.

简单的维恩图（上图）显示一个或多个人使用B列，一个（人/组）仅使用A，另一个（人/组）仅使用C。

However there were commonalties in their processes thus it made sense to combine fields A,B, and C into one DAL function.

但是，它们的过程存在共同点，因此将字段A，B和C组合为一个DAL函数是有意义的。

让我们构造第一个DAL ( Let’s construct our first DAL )

We start by opening SQL Server Management Studio and by opening a new query.

我们首先打开SQL Server Management Studio，然后打开一个新查询。

As the reader will note above we shall be using the DAL database. Note that we first check for the existence of an object with the name ‘SQLShack’ and TYPE = ‘TF’. Should there be such an object then we know that we are really going to re-create the object.

正如读者在上面指出的那样，我们将使用DAL数据库。 请注意，我们首先检查名称为'SQLShack'和TYPE ='TF'的对象是否存在。 如果存在这样的对象，那么我们知道我们将真正重新创建该对象。

The astute reader will note the four (4) parameters that are passed through to our DAL:

精明的读者会注意到传递给DAL的四（4）个参数：

1. A comma delimited list of funds (none, one or more may be passed)
   以逗号分隔的资金清单（无，可以通过一个或多个）
2. A list of assets (the children of a ‘fund’). Once again (none, one or more may be passed)
   资产清单（“基金”的子项）。 再一次（无，可能会通过一个或多个）
3. A start date parameter
   开始日期参数
4. An end date parameter
   结束日期参数

We also indicate that the function will return a table in the form of a table variable (@resultset).

我们还指示该函数将以表变量（@resultset）的形式返回一个表。

We are now in the position to define the output of our table function (see below). In other words the data that will be returned to our end users.

现在，我们可以定义表函数的输出了（见下文）。 换句话说，数据将返回给我们的最终用户。

Whilst the actual SQLShack DAL has 100 + fields, we see a few of them in the screen shot above. The one piece that is still missing is the SQL code to pull the data.

尽管实际SQLShack DAL具有100多个字段，但我们在上面的屏幕快照中看到了其中的一些字段。 仍然缺少的一部分是用于提取数据SQL代码。

Our completed starter DAL (renamed “SQLShackStartedDAL”) may be seen below and once created will be found amongst the “Table-valued functions” (see below).

我们完整的 d 入门DAL （重命名为“ SQLShackStartedDAL”）可以在下面看到，一旦创建，就可以在“表值函数”中找到（请参见下文）。

Now that we have created a very simple DAL, it is time to give it a try.

现在我们已经创建了一个非常简单的DAL，现在该尝试一下了。

As we shall note above, I have create a simple query to pull data from the DAL. I have given the query my fund list, my asset list and a start and an end date. Running the query I obtain ‘tonnes’ of data, HOWEVER we forgot one important point and that was to insert a predicate into the function. Let us do that now.

如上文所述，我已经创建了一个简单的查询来从DAL中提取数据。 我已经给查询提供了我的资金清单，资产清单以及开始和结束日期。 运行查询时，我获得了“大量”的数据，但是我们忘记了一个重要的观点，那就是在函数中插入一个谓词。 现在让我们这样做。

That looks better (see the predicate above).

看起来更好（请参见上面的谓词）。

Our result set, may be seen above.

我们的结果集可以在上面看到。

在“米老鼠”查询之外。 ( On beyond ‘Mickey Mouse’ queries. )

At this point, we may want to see more than one fund, how do we achieve this?

在这一点上，我们可能希望看到多个基金 ，我们如何实现这一目标？

Note that in the screen shot above, I have added another fund to the fund list. We are now looking for ‘M1TE’ and ‘PAT1’. Note that the two funds are separated by a comma.

请注意，在上面的屏幕截图中，我将另一个基金添加到了基金列表中。 我们现在正在寻找“ M1TE”和“ PAT1”。 请注意，这两个基金之间用逗号隔开。

It is obvious that as the query now stands, nothing will be returned as SQL Server will interpret the fund as being “M1TE,PAT1” and this is not the case. Let us fix this now.

显然，按照现在的查询，将不会返回任何内容，因为SQL Server会将资金解释为“ M1TE，PAT1 ” ，事实并非如此。 现在让我们解决此问题。

In the screen dump above, note the replacement for “and FUND_ID = @Fundlist” with

在上面的屏幕转储，注意更换为“和FUND_ID = @Fundlist” 与

 
“and ((1 = CASE WHEN @fundlist ='' THEN 1 ELSE 2 END) or
CHARINDEX(psd.fund_id,@fundlist,1) >0 ) “
 

This requires an explanation. SQL Server parses from left to right. This said, should @fundlist be blank, and with the case statement (being evaluated first) 1 = 1, which is TRUE. No filter is applied and no further parsing is done. Should there be a list of funds being passed through, then @fundlist is NOT empty therefore 1 = 2 which is FALSE and therefore the second part or the OR clause is parsed.

这需要一个解释。 SQL Server从左到右进行解析。 也就是说，@ fundlist应该为空，并且带有case语句（首先被评估） 1 = 1 ， 这是TRUE 。 没有应用过滤器，也没有进一步的解析。 如果有通过的资金清单，则@fundlist不为空，因此1 = 2为FALSE 因此，将解析第二部分或OR子句 。

When we now run the query we find the following:

现在，当我们运行查询时，我们发现以下内容：

Note that we have returned data for fund M1TE but none for fund PAT1 (which is to be expected) as an asset belongs to a fund and one fund only (a business rule).

请注意，我们返回的是基金M1TE的数据，但没有返回基金PAT1的数据（这是预期的），因为资产属于一个基金，仅属于一个基金（业务规则）。

这就是“踢球者”！ ( So here is the “Kicker”! )

How do we alter our query to permit two or more funds to be returned? As mentioned above an asset belongs to one and only one fund. Our predicate (with in the DAL function) is defined via “and’s” (see below).

我们如何更改查询以允许退回两个或更多资金？ 如上所述，资产属于一个且只有一个基金。 我们的谓词（在DAL函数中带有）是通过“和”定义的 （见下文）。

 
where CAL_DT between convert(date,@startdate) and convert(date,@enddate)
and ((1 = CASE WHEN @fundlist ='' THEN 1 ELSE 2 END) or 
CHARINDEX(psd.fund_id,@fundlist,1) >0 ) 
and ASSET_ID = @assetlist
 

Changing the asset list in our query from

从中更改查询中的资产列表

 
select * from SQLShackStarterDAL('M1TE,PAT1','477155956','2006-01-01','2008-12-31')
 

to

至

 
select * from SQLShackStarterDAL('M1TE,PAT1','','2006-01-01','2008-12-31') 
 

will not work as can be seen on the screen dump below:

在以下屏幕转储中无法看到：

The reason being that we have no fund with a blank on NULL name.

原因是我们没有空名称为空的基金。

By taking the parameter out altogether will not work either, as may be seen below:

通过完全删除该参数也不起作用，如下所示：

Even by removing the comma, we generate a run time error (see below).

即使删除逗号，我们也会产生运行时错误（请参见下文）。

So what do we do?

那么我们该怎么办？

Let us back up a tad and return the query to its original form, yet remove the ‘asset ID’ value. At this point in time we do nothing further (see below).

让我们备份tad并将查询返回其原始形式，但删除“资产ID”值。 目前，我们无法采取任何进一步措施（请参见下文）。

NOW!! The astute reader will now say “Why not set a default?” Remember that your predicate is constructed with “and” therefore what value do you put in for the default. A blank or NULL will NOT do the job as there are no assets called ‘‘ nor ‘NULL’.

现在！！ 精明的读者现在会说“为什么不设置默认值？” 请记住，您的谓词是用“和”构造的，因此您为默认值输入了什么值。 空白或NULL不会执行此作业，因为没有称为“”或“ NULL”的资产。

Going back to the DAL function, let us make a small change and add the following piece of code for the asset_ID

回到DAL函数，让我们进行一些小的更改，并为asset_ID添加以下代码

 
and ((1 = CASE WHEN @assetlist =''  THEN 1 ELSE 2 END) or 
CHARINDEX(psd.asset_id,@assetlist,1) >0 )   
 

Our DAL now is structured as follows:

现在，我们的DAL的结构如下：

Let us see how this looks back in our test query. Note that going forward I shall be using fund PAT2 as one of our funds.

让我们看看我们的测试查询中的情况。 请注意，今后我将使用PAT2基金作为我们的基金之一。

Note that both funds are now showing regardless of the asset ID.

请注意，无论资产ID如何，这两个资金现在都显示。

Similarly we could set the funds to ‘ ‘ and place two asset ID’s within the query (see below).

同样，我们可以将资金设置为“”，并在查询中放置两个资产ID（请参见下文）。

那么我们要去哪里呢？ ( So where are we going with this? )

When we look at a larger version of this DAL function (i.e. add more fields to the DAL) such as the one seen below, we shall note that with the list of fields that is contained within the DAL, we are in a position to customize what each user requires.

当我们查看此DAL函数的较大版本（即，向DAL添加更多字段）时，如下所示，我们将注意到，随着DAL中包含的字段列表，我们可以自定义每个用户的需求。

Above is a list of +/- 20 fields that we are now going to insert into our starter DAL table function.

上面是+/- 20个字段的列表，我们现在将这些字段插入到入门DAL表函数中。

The fields definitions have been inserted into our DAL function (see above) and the same fields added to our select statement (see below).

字段定义已插入到我们的DAL函数中（请参见上文），并将相同的字段添加到我们的select语句中（请参见下文）。

We now recreate the DAL function and re-run our query.

现在，我们重新创建DAL函数并重新运行查询。

What John wishes to see:

约翰希望看到的：

..and now what the fields that Mary wishes to view:

..现在，玛丽希望查看哪些字段：

Thus we can see how flexible final data extraction may be; remembering that the query encased within the DAL has been well tuned by the DBA’s. This said we are being both efficient and effective!

因此，我们可以看到最终数据提取可能有多灵活。 请记住，DAL中包含的查询已由DBA进行了很好的调整。 这说明我们既高效又有效！

We should also realize that if allocated the necessary rights, we are STILL ABLE to effect ‘joins’ to other tables and view (see below).

我们还应该意识到，如果分配了必要的权限，我们仍然可以实现与其他表和视图的“联接”（请参见下文）。

Note that in the screen above we check to see if the fund is still active or closed. This data comes from the “ActiveOrClosed” table.

请注意，在上面的屏幕中，我们检查了基金是否仍处于活动状态或关闭状态。 此数据来自“ ActiveOrClosed”表。

来自DAL的报告 ( Reporting from a DAL )

Our final exercise is to show you that the same DAL may be utilized for reporting. Immediately below, I show a report that I have created in SQL Server Reporting Services. Note that in this case I have used T-SQL as the query type. Should you be unfamiliar with SQL Server Reporting Services, please do yourself a favour and have a look at some of the earlier articles that I have posted on this website.

我们的最后一个练习是向您显示可以使用相同的DAL进行报告。 在下面，我将显示我在SQL Server Reporting Services中创建的报告。 请注意，在这种情况下，我已使用T-SQL作为查询类型。 如果您不熟悉SQL Server Reporting Services，请帮自己一个忙，看看我在此网站上发布的一些较早的文章。

Running the report we find the following:

运行该报告，我们发现以下内容：

结论 ( Conclusions )

Power users and business analysts (with SQL experience) can at time issue queries that can bring the system to its knees. As developers and DBA’s, it is our duty to ensure that these folks are able to obtain the necessary data in a timely manner as opposed to a query that runs in excess of twenty four hours and returns four rows.

高级用户和业务分析人员（具有SQL经验）可以及时发出查询，使系统崩溃。 作为开发人员和DBA，我们的责任是确保这些人员能够及时获取必要的数据，而不是运行超过24小时并返回四行的查询。

Well-tuned generic queries encased within a user defined table function can yield a myriad of combinations of required data (to the end user) PLUS ensure that rendering of this data is done in the most efficient and effective manner.

包含在用户定义的表函数中的经过良好调整的通用查询可以产生（向最终用户）所需数据的无数组合，并且确保以最有效的方式呈现此数据。

We have discussed how parameters ensure that the queries and DAL’s are flexible and we have also seen that with a properly constructed DAL it is not necessary to pass an argument.

我们已经讨论了参数如何确保查询和DAL的灵活性，并且还看到使用正确构造的DAL不必传递参数。

Finally, I do hope that you will give it a try.

最后，我希望您能尝试一下。

Until the next time, happy programming!

直到下一次，编程愉快！

翻译自: https://www.sqlshack.com/sql-server-2014-data-access-layers/