自定义行功能：访问与SQL Server [英] Custom Row Functions: Access vs. SQL Server
问题描述
我有一个Access数据库应用程序，其中包含许多用VBA编写的自定义行
函数。换句话说，很多查询包含
计算字段，这些字段使用模块中定义的函数。
我现在正考虑将数据库从Access升级到SQL
服务器。如果我这样做，我该如何实现自定义行功能？
Visual Basic是否与SQL Server集成，就像使用Access一样？或者
SQL Server中的T-SQL提供VBA给我的功能
Access？或者也许没有办法在
后端做自定义行功能，而且它们必须在前端实现？
可以有人向我解释它是如何工作的吗？
-TC
I have an Access database application with a lot of custom row
functions written in VBA. In other words, a lot of queries contain
calculated fields which use functions defined in the modules.
I am now thinking about upgrading the database from Access to SQL
Server. If I do, how can I implement the custom row functions? Is
Visual Basic integrated with SQL Server just as it is with Access? Or
does T-SQL in SQL Server offer the functionality VBA gives me in
Access? Or perhaps there is no way to do custom row functions in the
back-end, and they have to be implemented in the front-end?
Can anyone explain to me how it works?
-TC
推荐答案
2005年11月16日08:05 ：23-0800，TC <去********* @ yahoo.com>写道：
On 16 Nov 2005 08:05:23 -0800, "TC" <go*********@yahoo.com> wrote:
我有一个Access数据库应用程序，其中包含许多用VBA编写的自定义行
函数。换句话说，很多查询包含使用模块中定义的函数的计算字段。
我现在正考虑将数据库从Access升级到SQL服务器。如果我这样做，我该如何实现自定义行功能？
Visual Basic是否与Access一起集成了SQL Server？或者说SQL Server中的T-SQL是否提供了VBA给我带来的功能？或者也许没有办法在
后端进行自定义行功能，而且必须在前端实现？
任何人都可以向我解释它是如何工作的吗？
-TC
I have an Access database application with a lot of custom row
functions written in VBA. In other words, a lot of queries contain
calculated fields which use functions defined in the modules.
I am now thinking about upgrading the database from Access to SQL
Server. If I do, how can I implement the custom row functions? Is
Visual Basic integrated with SQL Server just as it is with Access? Or
does T-SQL in SQL Server offer the functionality VBA gives me in
Access? Or perhaps there is no way to do custom row functions in the
back-end, and they have to be implemented in the front-end?
Can anyone explain to me how it works?
-TC
自定义函数可用于SQL Server 2000及以上查询，但它们必须是
必须用T-SQL编写并包含在服务器上。此外，这些功能的性能绝对令人沮丧。您可以通过在其中放入一些简单的函数调用来减慢查询数量
（注意
表示函数没有这个性能问题） 。
幸运的是，这对于大多数Access / SQL Server
应用程序来说都不是问题，因为事实证明这些函数通常用于某一点。
查询，其中Access / JET可以将其应用于从
服务器返回的结果。如果我查询链接表，并使用Format（）将字段格式化为字符串
，则Access首先从后端查询所需的字段，
然后在接收行时在本地应用Format函数。
当你在某个地方使用Access函数时会遇到麻烦
Access / JET从能够将处理委托给后端，例如
，当函数用于WHERE子句或内部和聚合函数时
如SUM（Round（ [美孚]））。您只需要知道这些情况，并且
避免它们。
Custom functions can be used in SQL Server 2000 and above queries, but they
must be written in T-SQL and contained on the server. Furthermore, the
performance of these functions is absolutely dismal. You can slow a query
down by a factor of 10,000 by putting a few simple function calls in it (note
that tabluar functions don''t have this performance problem).
Fortunately, this just isn''t a problem with most Access/SQL Server
applications because it turns out the functions are usually used at a point in
the query where Access/JET can apply it to the results coming back from the
server. If I do a query of a linked table, and I format a field as a string
using Format(), Access first queries the fields it needs from the back-end,
then applies the Format function locally as the rows are received.
The trouble comes in when you use Access functions somewhere that they prevent
Access/JET from being able to delegate processing to the back-end, such as
when the function is used in a WHERE clause or inside and aggregate function
such as SUM(Round([Foo])). You just need to be aware of these cases, and
avoid them.
SQL Server中的IMO T-SQL提供VBA提供的功能访问。
但是，如果我们确定我们正在谈论相同的
的话，它可能会有所帮助。如果您可以发布其中一个调用VBA函数或函数代码中的两个的查询，那么我们可能会看到如何完成这样的操作。仅限T-SQL。
（我一直想知道当使用MS-SQL时VBA是否冗余
[这是一个没有模块的应用程序可能是高效的]但那是
另一个故事。）
IMO T-SQL in SQL Server offers the functionality VBA gives in Access.
But, it might be helpful if we are sure we are talking about the same
thing. If you could post one of the queries that calls perhaps two of
the VBA functions, and the code of the functions, then we might see how
this could be done as T-SQL only.
(I have been wondering whether VBA is redundant when one uses MS-SQL
[that is a application without modules might be efficient] but that is
another story.)
史蒂夫，
谢谢你的答复。
事实上，我表达了我的问题 - 我真的很感兴趣
将功能转移到后端，所以你对T-SQL的评论与我最相关的是
。 T-SQL真的那么慢吗？假设我选择Oracle
代替; PL-SQL是否具有所需的功能，是否比T-SQL更快？
？
此外，什么是表格函数？
-TC
Steve,
Thank you for the reply.
In fact, I expressed my question badly -- I''m really interested in
moving the functions to the back-end, so your comments on T-SQL are
most relevant to me. Is T-SQL really that slow? Suppose I choose Oracle
instead; does PL-SQL have the required capabilities, and is it faster
than T-SQL?
Also, what is a "tabular function"?
-TC
这篇关于自定义行功能：访问与SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
