SQL statement length limit?

SQL statement length limit?

Post by Brad Clement » Sat, 06 Apr 1996 04:00:00



Is there a limit to the length of a SQL statement that can be handled by
SQL Server 6.0?  And would there be any serious performance problems
with a statement of the form "select a, b, c from x where id = 123 or id
= 456 or id = 789 or id = 234 or id = 234 ..." assuming that id is a
unique, indexed column?
 
 
 

SQL statement length limit?

Post by Asif Kha » Mon, 08 Apr 1996 05:00:00




Quote:> Is there a limit to the length of a SQL statement that can be handled by
> SQL Server 6.0?  And would there be any serious performance problems
> with a statement of the form "select a, b, c from x where id = 123 or id
> = 456 or id = 789 or id = 234 or id = 234 ..." assuming that id is a
> unique, indexed column?

ermm - dont know if this is any help but it's the normal
case to have the id's you want in their own table eg. z,
then to join the tables x & z on the id column.

select x.a, x.b, x.c, z.id
from x,z
where x.id=z.id

this way you'd have a sql statement of the same length
regardless of how many id's you're selecting.

but you probably knew all that anyway.
--

PGP Fingerprint: 1E 3F F0 0B 3B A1 B2 83  81 C1 FD 1C FD A7 DB CE

 
 
 

SQL statement length limit?

Post by Michael Moor » Mon, 08 Apr 1996 05:00:00



> Is there a limit to the length of a SQL statement that can be handled by
> SQL Server 6.0?  And would there be any serious performance problems
> with a statement of the form "select a, b, c from x where id = 123 or id
> = 456 or id = 789 or id = 234 or id = 234 ..." assuming that id is a
> unique, indexed column?

-- No problem - there is some sort of limit such the the execution plan
(ie the internally generated sequence of instructions) cannot exceed 64K.

A better way of writing the query would be:
   select a, b, c from x where id in (123, 456, 789, 234)

Share and Enjoy...

 ====================================================
 ______  ________        ______                ______
 ___   |/  /___(_)__________  /_ ______ ______ ___  /  
 __  /|_/ / __  / _  ___/__  __ \_  __ `/_  _ \__  /  
 _  /  / /  _  /  / /__  _  / / // /_/ / /  __/_  /    
 /_/  /_/   /_/   \___/  /_/ /_/ \__,_/  \___/ /_/

 
 
 

SQL statement length limit?

Post by Brad Clement » Tue, 09 Apr 1996 04:00:00



> -- No problem - there is some sort of limit such the the execution plan
> (ie the internally generated sequence of instructions) cannot exceed 64K.

> A better way of writing the query would be:
>    select a, b, c from x where id in (123, 456, 789, 234)

Thanks for the information, Michael.  I don't suppose there's any easy
way to compute the execution plan length from the SQL statement, is
there?  Or is there a way to see the execution plan length for queries
in isql/w or something like that?

Brad Clements
NetVoyage

 
 
 

SQL statement length limit?

Post by Brad Clement » Wed, 10 Apr 1996 04:00:00



> ermm - dont know if this is any help but it's the normal
> case to have the id's you want in their own table eg. z,
> then to join the tables x & z on the id column.

> select x.a, x.b, x.c, z.id
> from x,z
> where x.id=z.id

> this way you'd have a sql statement of the same length
> regardless of how many id's you're selecting.

> but you probably knew all that anyway.

I realize that this method will work and may be better because it won't
run into any sql statement length limits.  But I was trying to avoid the
overhead of creating a temporary table and writing all the ID's of
interest into it.  

If there was some way to determine how long a SQL statement can be
before running into the limit then I could use both methods; the former
method on shorter lists of IDs, and the join method on longer lists.

Thanks,
Brad Clements

 
 
 

1. ODBC SQL Statement length limit of 64k???

I have written a VB5 application that inserts data into a SQL Server table.
I am using an ODBCDirect workspace, and the EXECUTE method to run the sql
insert statement. The table has four fields, one of which is TEXT, allowing
very large text data to be inserted. When one of my users tried to insert a
row that resulted in a sql statement that exceeded 64k, he got an ODBC call
failed msg. When we shortened the data slightly, it worked. What are the
rules here?

Steve

2. ms access as a publisher

3. SQL statement length limits

4. Problem creating dimension

5. Limit to the Length of the SQL statement

6. US-PA: Horsham-POWERBUILDER / ORACLE DEVELOPER

7. String length limits hit in SQL statement

8. Access 2000 form

9. limits of SQL statement length

10. Limit to length of string displayed in SELECT statement

11. Row length limit in SQL Server 2000?

12. 128 character SQL String length limit??

13. Some problem due to the length limit of SQL sentence