Questions / comments

1) How big is your DB? We are running 40DBs or so on an RAID 5 server, total about 15GB (not big). The server has *never* been defragmented....

Robert,

Another good day, I learned something new (and useful).

Thanks!!

P

Multiple statements in IF / ELSE need to be wrapped in BEGIN...END

How many users? How educated, as in knowledge of SQL and DBs, are your users? How big is the DB? (Think about it: forget one join clause, and the query...

Couldn't you then run into the problem of 2 trans beginning at the same time, each getting the same new id number?

I just want to know how the app knows which of the two tables to insert.

How about 1 parent table A with identity, 2 child tables with foreign key and check constraints: table B, all the ids must be divisible by 2 with...

Here's the correct version (single column in select, ordering not by ordinal position - is that still to be deprecated?)

SELECTcVal

FROM #tmp_holding

ORDER BY CONVERT(INTEGER, STUFF(cVal, 1, PATINDEX('%[0-9]%', cVal) -1, '')), cVal

P

Try this:

SELECTcVal, CONVERT(INTEGER, STUFF(cVal, 1, PATINDEX('%[0-9]%', cVal) -1, ''))

FROM #tmp_holding

ORDER BY 2, 1

HTH,

P

Jeff,

VERY COOL!!!!

Never seen that UPDATE...SET construct before.

Not so fast here, tho, took 1m11s.

Thanks,

Paul

You say this is called from an update trigger, and then give an example involving one 1000 row insert (or 1000 single row inserts). Either way, unless the trigger is...

Save all your changes in a script file, then cursor through your DBs calling OSQL using xp_cmdshell.

Michael,

You're too funny!!! You say we're not here to preach, yet you're preaching at me!!!!

I just read your solution to the original question: update syscolumns????? IMHO, that's preaching suicide....

Michael,

In that case, it's not the ordinal position that breaks things, it's the poor code.

The ordinal position cannot matter. If you want to see columns in a specific order, select them in that order.