Limitations in a query !!

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • harsha.21aug@gmail.com
    #1

    Limitations in a query !!

    Hi all there,
    I'm a newbee to this forum. I've a question, is there any limitation
    on "IN" clause in select query.
    for example :
    "SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
    ('EMP1001','EMP 1002','EMP1003' , etc, etc, so on)".

    I've read at some documentation that there is a limitation for Columns
    in a table and i.e. we can have only 1024 columns per table, is this
    true?
    Plz help me !!

    Thanx in advance.

    Kind Regards,
    Harry

    Tags: None
    • Roy Harvey
      #2
      Re: Limitations in a query !!

      On 2 Aug 2006 05:31:03 -0700, harsha.21aug@gm ail.com wrote:
      >Hi all there,
      I'm a newbee to this forum. I've a question, is there any limitation
      >on "IN" clause in select query.
      >for example :
      >"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
      >('EMP1001','EM P1002','EMP1003 ', etc, etc, so on)".
      There doesn't seem to be a documented limit, but if the list gets too
      long it should be in a table, not in-line code.
      >I've read at some documentation that there is a limitation for Columns
      >in a table and i.e. we can have only 1024 columns per table, is this
      >true?
      Yes, the limit is 1024 columns in a table. Any database design that
      comes close to this, much less needs to exceed it, is probably
      questionable.
      >Plz help me !!
      >
      >Thanx in advance.
      >
      >Kind Regards,
      >Harry
      Roy Harvey
      Beacon Falls, CT

        Comment

        • harsha.21aug@gmail.com
          #3
          Re: Limitations in a query !!

          Thanx alot Roy. :)

          Roy Harvey wrote:
          On 2 Aug 2006 05:31:03 -0700, harsha.21aug@gm ail.com wrote:
          >
          Hi all there,
          I'm a newbee to this forum. I've a question, is there any limitation
          on "IN" clause in select query.
          for example :
          "SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
          ('EMP1001','EMP 1002','EMP1003' , etc, etc, so on)".
          >
          There doesn't seem to be a documented limit, but if the list gets too
          long it should be in a table, not in-line code.
          >
          I've read at some documentation that there is a limitation for Columns
          in a table and i.e. we can have only 1024 columns per table, is this
          true?
          >
          Yes, the limit is 1024 columns in a table. Any database design that
          comes close to this, much less needs to exceed it, is probably
          questionable.
          >
          Plz help me !!

          Thanx in advance.

          Kind Regards,
          Harry
          >
          Roy Harvey
          Beacon Falls, CT

            Comment

            • Karthik
              #4
              Re: Limitations in a query !!

              Harsha, See this post

              http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/8605a2b10bf4faaa/f2da17b97723fe15?hl=en#f2da17b97723fe15


              harsha.21aug@gm ail.com wrote:
              Thanx alot Roy. :)
              >
              Roy Harvey wrote:
              On 2 Aug 2006 05:31:03 -0700, harsha.21aug@gm ail.com wrote:
              >Hi all there,
              I'm a newbee to this forum. I've a question, is there any limitation
              >on "IN" clause in select query.
              >for example :
              >"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
              >('EMP1001','EM P1002','EMP1003 ', etc, etc, so on)".
              There doesn't seem to be a documented limit, but if the list gets too
              long it should be in a table, not in-line code.
              >I've read at some documentation that there is a limitation for Columns
              >in a table and i.e. we can have only 1024 columns per table, is this
              >true?
              Yes, the limit is 1024 columns in a table. Any database design that
              comes close to this, much less needs to exceed it, is probably
              questionable.
              >Plz help me !!
              >
              >Thanx in advance.
              >
              >Kind Regards,
              >Harry
              Roy Harvey
              Beacon Falls, CT

                Comment

                • Erland Sommarskog
                  #5
                  Re: Limitations in a query !!

                  (harsha.21aug@g mail.com) writes:
                  I'm a newbee to this forum. I've a question, is there any limitation
                  on "IN" clause in select query.
                  for example :
                  "SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
                  ('EMP1001','EMP 1002','EMP1003' , etc, etc, so on)".

                  To add to Roy's response, be aware of that when the list grows very
                  long (1000 elements) compile times on SQL 2000 can be absymal. SQL 2005
                  appears to do better in this regard.


                  --
                  Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                  Books Online for SQL Server 2005 at
                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                  Books Online for SQL Server 2000 at
                  http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                    Comment

                    Previous template Next
                    Working...