Database

Limits in list length for IN clause

10-Dec-08 18:16

PIEBALDconsult wrote:
Probably more portable too?

I'd say so. Many DBMS support inline views.

The need to optimize rises from a bad design.My articles[^]

Jörgen Andersson9-Dec-08 22:54

9-Dec-08 22:54

In Oracle 9i the maximum length of a list used in an IN clause is 1000 records.
Does anyone know if it's still the same limit in version 10g or 11g?

A related question is what the limit is in SQLServer?

Wendelius10-Dec-08 4:27

10-Dec-08 4:27

Jörgen Andersson wrote:
In Oracle 9i the maximum length of a list used in an IN clause is 1000 records

In Oracle 1000 limitation is not for records, but only for comma separated values in the list. So you cannot pass more literal values for an IN-list than 1000.

Jörgen Andersson wrote:
Does anyone know if it's still the same limit in version 10g or 11g?

At least in 10g this applies.

Jörgen Andersson wrote:
A related question is what the limit is in SQLServer

In SQL Server I'm not aware of such limitation. The limitation you could hit is the maximum size of a sql statement which is 65,536 * network packet size.

Mika

The need to optimize rises from a bad design.My articles[^]

Jörgen Andersson10-Dec-08 5:04

10-Dec-08 5:04

Thanks!

Mika Wendelius wrote:
In Oracle 1000 limitation is not for records, but only for comma separated values in the list. So you cannot pass more literal values for an IN-list than 1000

I know, I'll try to express myself better in the future.

Mika Wendelius wrote:
At least in 10g this applies

Which means I'll have to keep on using temp tables. Hmmm | :|

Wendelius10-Dec-08 5:30

10-Dec-08 5:30

Jörgen Andersson wrote:
Thanks!

You're welcome Smile | :)

Jörgen Andersson wrote:
Which means I'll have to keep on using temp tables

That's one possibility, but since the the values for IN come from a subquery, you can use basically all the techniques what the database offers to create the result set (normal tables, temporary tables, xml tables, external tables etc etc).

The need to optimize rises from a bad design.My articles[^]

Jörgen Andersson10-Dec-08 7:39

10-Dec-08 7:39

Mika Wendelius wrote:
hat's one possibility, but since the the values for IN come from a subquery, you can use basically all the techniques what the database offers to create the result set (normal tables, temporary tables, xml tables, external tables etc etc).

The data for the IN clause (or maybe rather a join) is coming from a .net datatable.
Is there an easier way than inserting it into a temptable?

Wendelius10-Dec-08 9:22

10-Dec-08 9:22

Since I don't know the wholse situation, just throwing some ideas:

Can you put the data into an xml format and in the subquery use xquery to fetch the data again
if you're inserting data into the database using this IN operator, you could use arrays to pass the data to a procedure
if you're selecting data you can do the same and then return ref cursor
possibly you could use UDT's in the above scenarios
you could create a function as pipelined, pass the data to that with an array and then use the return value of the function as a table in your subquery etc.

The need to optimize rises from a bad design.My articles[^]

Jörgen Andersson10-Dec-08 10:08

Update trigger for multiple rows

10-Dec-08 10:08

Thanks again.
I'll have a deeper look into this tomorrow. Eventually with another question...

amit sahu209-Dec-08 18:18

amit sahu20

9-Dec-08 18:18

hi
i have a update trigger that get fired when any column updated of the table

create TRIGGER trigger_Update_ABC
ON ABC
FOR Update 
AS
declare @Id bigint 
set @Id = (select Id from Inserted)
update ABC set ABC.Post = getdate() where Id =@Id

when i execute such sql quarry

Update ABC set ABC.Active = 1 where  ABC.Id in(1,4,5,6,3,7)

then i got error

Re: Update trigger for multiple rows

Howard Richards9-Dec-08 22:54

Howard Richards

9-Dec-08 22:54

Triggers must *always* be written with the expectation that multiple rows will be affected. So SELECT ID from Inserted could return a series of INT not just one.

CREATETRIGGER trigger_Update_ABC ON ABC
FOR Update AS
  UPDATE ABC
  SET ABC.Post = GetDate()
  WHERE ABC.ID IN (SELECT ID from inserted)

You should check to see if recursive triggers are set on your server, as this is an update trigger on ABC that updates ABC. If recursive triggers are enabled it could cause an infinite loop and the statement will fail.

Do some research into recursive triggers if this is enabled.

'Howard

Re: Update trigger for multiple rows

amit sahu209-Dec-08 23:17

amit sahu20

9-Dec-08 23:17

than x Richards
my confusion is now much clear now

SQL Server 2005:Problem with full-text indexing

rahulae9-Dec-08 13:37

rahulae

9-Dec-08 13:37

Full-text index tab is disabled and

I can only see partition schemes and partition functions under storage.

Help me out in this regard.

Re: SQL Server 2005:Problem with full-text indexing

Howard Richards9-Dec-08 23:03

Howard Richards

9-Dec-08 23:03

http://www.indexserverfaq.com/tablenotenabled.htm[^]

'Howard

procedure in SQL

papy-boom9-Dec-08 9:40

9-Dec-08 9:40

i got a procedure in SQL Server at the end of the program i write this code
SELECT @@IDENTITY

COMMIT TRANSACTION INSERT_NOMENCLATURE
RETURN

the problem that i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name
thanks!

Wendelius9-Dec-08 10:03

9-Dec-08 10:03

papy-boom wrote:
i don't have the column name of SELECT @@IDENTITY how can i manage it to get the value with a column name

You don't know from which column @@IDENTITY returns the value. It just returns the last inserted identity value in your sesion. It can be any table and any identity column.

You can narrow the possibilities using SCOPE_IDENTITY, but you'd still face the same problem.

Normally you know where @@IDENTITY comes from in your procedure since it's the previous insert to identity columned table. What's the cause in your case that prevents you identifying the statement (and after that the table and the column)?

The need to optimize rises from a bad design.My articles[^]

papy-boom9-Dec-08 10:08

9-Dec-08 10:08

you mean if i replaced by select max from my table it will works?

Wendelius9-Dec-08 10:15

9-Dec-08 10:15

papy-boom wrote:
you mean if i replaced by select max from my table it will works?

No, that's not what I mean.

Since you didn't post any code or any explanation why do you want to know the column name, I can't say what's the answer for you.

Consider that I have a table:

CREATE TABLE Artist (
   [Artist#] int           NOT NULL IDENTITY(1,1) PRIMARY KEY,
   [Name]    nvarchar(100) NOT NULL
);

Then when I'm executing this piece of statement:

INSERT INTO Artist ([Name]) VALUES (@name);
SET @artistIdentity= @@IDENTITY;

Now on the 2'nd line I know that @@IDENTITY returns a value that is assigned to Artist# column in Artist table since that's the previous INSERT statement to a table that has an identity column.

The need to optimize rises from a bad design.My articles[^]

papy-boom9-Dec-08 10:25

9-Dec-08 10:25

the problem that is that in code part i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value so i tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work

Wendelius9-Dec-08 10:37

9-Dec-08 10:37

papy-boom wrote:
tried to get select @@IDENTITY AS returnedvalue to get the name of column but it doesn't work

No, because it only returns the value.

papy-boom wrote:
i have a datareader to read the value returned value and the argument to pass to datareader is the name of the column so if my procedure return me a value without the name of column it's not simple to get the value

Now I don't follow the logic. You cannot ask the last added value for a single column using @@IDENTITY. It doesn't work that way.

If you need to know what's the last added identity value to a specific table, you can select MAX from the identity column. But I don't see the point why you should do this since the value of an identity column is normally irrelevant. Refer to: http://en.wikipedia.org/wiki/Surrogate_key[^]

So the question is, why do you wan't to know the value at all?

The need to optimize rises from a bad design.My articles[^]

papy-boom9-Dec-08 12:18

9-Dec-08 12:18

when i excute my procedure In SQL i have the value but in the name of column i have (no column name)
i write a script in C# to get the value with data reader but the method used in datareder is GetValueParam(Command,columName) the procedure dosen't return a name for this column i tried to give a name to returned column so i modify it with
COMMIT TRANSACTION INSERT_Artist
RETURN SELECT max(Artist#) AS ArtistId from Artist

LBL_ERROR:
ROLLBACK TRANSACTION INSERT_Artist
but i have the same problem (no column name) for returned value i need the column name to read it with a datareader without using another transaction
excuse my ignorance but i'm really dump in SQL Server

Wendelius9-Dec-08 18:31