10

Possible Duplicate:
Maximum size for a SQL Server Query? IN clause? Is there a Better Approach

I Googled for a while and can't find whether there's some hard limit on how huge query strings can be in SQL Server. I mean what if I build a 100 million character query - will it run or will the server just drop it as too long?

Is there a limit to how long SQL query can be (in terms of characters or tokens or whatever like that) in SQL Server?

Community
  • 1
  • 1
sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • 2
    Looks like the answer may be in [this question](http://stackoverflow.com/questions/1869753/maximum-size-for-a-sql-server-query-in-clause-is-there-a-better-approach). – Kasaku Sep 08 '11 at 08:26

1 Answers1

17

I never encountered a problem of SQL query being too long in terms of number of characters, but there is a maximum number of tables a query can reference (256) and I hit this limitation a few times.

Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?

UPDATE
In recent versions of SQL Server, the number of tables per SELECT statement is limited only by available resources.

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
  • 5
    The downvote here feels a little harsh to me. Not the exact answer required, but certainly related. – John N Sep 08 '11 at 08:34
  • 1
    How long (an estimation will be helpful too) was the longest query you produced? – sharptooth Sep 08 '11 at 08:41
  • @sharptooth: About 250 KB, but it was a view referencing other views. I'm not 100% sure about that, but I read somewhere that, when executing such queries, SQL Server replaces the referenced views with actual SELECT statements they contain, so the size of original query increases. – Marek Grzenkowicz Sep 08 '11 at 08:59
  • 1
    View substitution doesn't happen at a source code / textual level. It stores algebrized trees for the views then chucks them in later in the process. – Martin Smith Sep 08 '11 at 09:27
  • @sharptooth: I remembered wrong - it's 72 KB (not 250 KB). – Marek Grzenkowicz Sep 09 '11 at 14:47
  • @MarekGrzenkowicz How did you reference that many tables? – Martin Thoma Jun 07 '15 at 17:22
  • It was a reporting system built directly on top of an OLTP database (very bad practice, I know). It was normalized, so getting a dataset for a report required tens of joins. As soon as a report touched a few different business processes, the number of joins grew two- or threefold. – Marek Grzenkowicz Jun 07 '15 at 17:57