Extended challenge for everyone

Question

Post reply

Extended challenge for everyone

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Rob Farley SSCrazy Points: 2739 More actions · Answer 1

Let me see... (please excuse the obvious bits)

-- Insert into Test (id, name, Colid) Select Distinct id, name, Colid from dbo.SysColumns

Well, [id] is the table name, so this just populates the column name and order, per objectid.

-- Declare @Int as int

-- set @Int = 0

-- declare @LastId as int

-- set @LastId = -1

-- update dbo.Test set @Int = Colid2 = case When @LastId id THEN 0 ELSE @Int END + 1, LastId = @LastId, @LastId = id

To me it looks like a ranking function on the 'id' field, thanks to the unique index on the table. My guess is that @LastId being set last should mean that the lastid field should be the previous id on "colid=1" rows, but I have a feeling it will actually set it first and put it into the lastid column... so effectively, 'lastid = id' would have done. But I'm not really sure on this - I'll have to run it to be sure. I don't think you need the 'lastid' field... you could've done the same without it, right?

RobF

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

I don't think you need the 'lastid' field... you could've done the same without it, right?

Yes, it should always contain the same value as the id column. This UPDATE thingy is a nice T-SQL feature. Unfortunately it fails, when you require a certain order. http://www.sqlteam.com/item.asp?ItemID=765

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rob Farley SSCrazy Points: 2739 More actions · Answer 3

But that's why he put the index on the table, so that it would order it for him without changing the 'order by', right?

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

But that's why he put the index on the table, so that it would order it for him without changing the 'order by', right?

Rob, you should know better. While this works in most case, there is NO GUARANTEE to.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rob Farley SSCrazy Points: 2739 More actions · Answer 5

Oh, I know there's no guarantee... but that's the thing with this shortcut. Personally, I hate it and would rather use a CURSOR! But that's just because I prefer correctness to performance.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

But that's just because I prefer correctness to performance.

Now, good to hear that.

I almost thought that everybody's more crazy about performance and speed and tweak that and tune this as integrity and correctness.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rob Farley SSCrazy Points: 2739 More actions · Answer 7

Not everyone.

Very pleased that 2005 has user-defined aggregate functions (which reminds me, I need to play with that some more), so that people stop using that nasty trick of using variables inside multi-row select/updates.

RobF (not an MVP... although I keep thinking that Microsoft must be missing out )

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

RobF (not an MVP... although I keep thinking that Microsoft must be missing out )

Don't worry about that. After all, it's just another three letters. Nothing more and nothing less.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 9

"Looks like you have the same object with different owners."

After rebooting the system my problem went away. Or at least is hidding until I am sleep typing one day when he (or she as the case maybe) will wake up and wreck my code. I am trying to recreate the problem but so far have had no luck.

Mike

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 10

From BOL

The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.

syscolumns

Contains one row for every column in every table and view, and a row for each parameter in a stored procedure. This table is in each database.

SO..You can have more than one person who is mapped to the dbo inside each database BUT there is only one syscolumns per DB. Even when there is more than one sysadmin user you should not be able to insert a duplicate row into syscolumns.

I know that I am missing something here and once someone gives me the answer I will bang my head aganist the wall and say "I knew that". But so far nary a ding in the wall. Help

Mike

[edited for clarity. changed Reading the syscolumns even when there was more than one sysadmin user you should not be able to insert a duplicate row into syscolumns. to say what I meant not what I said.]

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 11

"Unfortunately it fails, when you require a certain order."

Requiring a certain order just requires careful coding. A where statement will fail (not return the expected results) if you do not follow a certain order.

Where a = something

And b = something

Or c = something

If different from

Where a =something

Or c = something

And b = something

Mike

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

Blame it on the language barrier. I meant that you cannot specify an ORDER BY along with the UPDATE. Sorting might be a better word.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Frank, Rob. I'm not sure you got exactly how the update is working (unless you're keeping it to yourself). You're right about incrementing a value, but you're still missing something to make it a complete answer.

I also totally agree about the order by not being certain without specifying the order by explicitly. But this code is not something I would put in production because I can't garantee the result. However I would gladly use it to populate a column that's just been added the the table for a "one time run" where I can test that it's working just before running it.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

...then let us finally participate on thy wisdom!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

Here's the kicker...

Compare colid to colid2 .

I know it's not perfect but the syscolumns handle the colid in strange way in functions and I didn't wanna waste time on that...