查询中的最大UNION语句 [英] Maximum UNION statements in a query

问题描述

想知道我可以在查询中创建的UNION语句的数量是否存在物理上或现实上的限制？我有一个客户用了大约250个表来获得
- 数据需要保存在单独的表中，但是我需要过滤它们以创建单个结果集。每张表

持有35,000到150,000行。我现在应该自己开枪吗？


lq

Wondering if there is a physical or realistic limitation to the number
of UNION statements I can create in a query? I have a client with
approx 250 tables - the data needs to be kept in seperate tables, but I
need to be filtering them to create single results sets. Each table
holds between 35,000 - 150,000 rows. Should I shoot myself now?

lq

推荐答案

你好，lq


每个SELECT语句限制为256个表。在使用UNION语句的查询中，您可以使用更多的

而不是256个表，但是这样的查询

不能用作视图或SELECT语句的子查询。如果

您直接使用这样的查询，您可能会在大约1300-1500 SELECT-s处达到

查询优化器的堆栈空间限制。


如果您实现分区视图，请确保您保持在

256表限制之下。最好是分区列有一组固定的可能值（例如，使用

国家名称的第一个字母，而不是国家名称本身） 。


您可能还想查看分区表。在SQL Server中

2005.它们在某种程度上比

分区视图更容易实现和更灵活。此外，它们受限于每个分区表的1000

分区。 （而不是

分区视图的256表限制）。


有关更多信息，请参阅：
http://groups.google.com/group/comp .. ..e778a5f3affd5b
http://groups.google.com/group/micro...5402088c4da967
http://msdn2.microsoft.com/en-us/library/ms190199.aspx
http://msdn2.microsoft.com/en-us/library/ms143432.aspx


Razvan

Hello, lq

There is a limit of "256 tables per SELECT statement". You can use more
than 256 tables in a query with UNION statements, but such a query
cannot be used as a view or as a subquery for a SELECT statement. If
you use such a query directly, you may hit a stack space limit of the
query optimizer at around 1300-1500 SELECT-s.

If you implement partitioned views, make sure that you stay under the
256-tables limit. It would be best if the partitioning column has a
fixed set of possible values (for example, use the first letter of the
country name, not the country name itself).

You may also want to take a look at "partitioned tables" in SQL Server
2005. They are somehow easier to implement and more flexible than
partitioned views. Also, they are subject of a limit of "1000
partitions per partitioned table" (instead of the 256-table limit for
partitioned views).

For more informations, see:
http://groups.google.com/group/comp....e778a5f3affd5b
http://groups.google.com/group/micro...5402088c4da967
http://msdn2.microsoft.com/en-us/library/ms190199.aspx
http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Razvan

嗨


拍摄自己。


SQL Server 7.0,2000和2005 limi ts如下：

每个SELECT语句的表：256

因此，您的查询不仅会表现不佳，而且还会进入256表

限制。无论你是否有意见等，你都可以获得256个基本表格。


如果设计正确，你可以将所有数据放在一个表格中并访问

数据通过视图来强制执行安全。


问候

------------------- -------------

Mike Epprecht，Microsoft SQL Server MVP

瑞士苏黎世


IM： mi**@epprecht.net

MVP计划： http://www.microsoft.com/mvp


博客： http://www.msmvps.com/epprecht/


" laurenq uantrell" < LA ************* @ hotmail.com>在消息中写道

news：11 ********************** @ g44g2000cwa.googlegr oups.com ...

Hi

Shoot yourself.

SQL Server 7.0, 2000 and 2005 limits are as follows:
Tables per SELECT statement: 256

So, not only will your queries perform badly, but you walk into a 256 table
limit. No matter if you have views etc, but 256 base tables is all you get.

With correct design, you could have all the data in one table and access the
data through views to enforce security.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

想知道我可以在查询中创建的UNION语句的数量是否存在物理上或现实上的限制？我有一个大约250个表的客户端 - 数据需要保存在单独的表中，但是我需要过滤它们来创建单个结果集。每张桌子都有35,000到150,000行。我现在应该开枪吗？

lq

Wondering if there is a physical or realistic limitation to the number
of UNION statements I can create in a query? I have a client with
approx 250 tables - the data needs to be kept in seperate tables, but I
need to be filtering them to create single results sets. Each table
holds between 35,000 - 150,000 rows. Should I shoot myself now?

lq

迈克，

谢谢你。不幸的是，客户端架构规则要求

数据驻留在大约250个不同的表中。


所以，我在考虑之前，我自己拍摄之前，正在玩

这样的事情（虽然我怀疑性能会完全打击

，每张表平均有50K记录。）：

（foo代码）


DECLARE @counter smallint，@ tablename varchar（20），@ sql nvarchar（4000）

SELECT @tablename =''tblBaseName''


SELECT @sql =''SELECT * FROM tblBaseName1''


SET @counter = 2
WHILE @counter< 251 / *最大赌桌数* /

BEGIN

设定NOCOUNT ON

@tablename = @tablename + CAST（@counter as varchar（3 ））

SELECT @sql =


@sql +''UNION ALL SELECT * FROM''+ @tablename


SET @counter = @counter + 1

SET NOCOUNT OFF

END

GO


EXEC sp_executesql @sql

Mike,
Thanks for that. Unfortunately, the client architecture rules require
the data to reside in approx 250 different tables.

So, what I''m considering, before I shoot myself, is playing with
something like this (though I suspect the performance will totally blow
with each table holding average of 50K records.):

(foo code)

DECLARE @counter smallint, @tablename varchar(20), @sql nvarchar(4000)
SELECT @tablename = ''tblBaseName''

SELECT @sql = ''SELECT * FROM tblBaseName1''

SET @counter = 2
WHILE @counter < 251 /*max table count*/
BEGIN
SET NOCOUNT ON
@tablename = @tablename + CAST(@counter as varchar(3))
SELECT @sql =

@sql + '' UNION ALL SELECT * FROM '' + @tablename

SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO

EXEC sp_executesql @sql

这篇关于查询中的最大UNION语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！