Extended challenge for everyone
-
[SCRIPT EDITED to take into consideration multiple owners]
We were having a little discussion Here about the best way to create documentation after the program was created. I then made the point that documentation was the most important part of the code (besides that it has to work).
I then challenged anyone to tell me what this code is doing without executing it... and also without documentation
.Since I got no answer on that thread I'm extending the challenge to anyone that wishes to take it on.
So what does this do?
if object_id('Test') > 0
DROP TABLE Test
GO
CREATE TABLE [Test] (
[id] [int] NOT NULL ,
[Name] [sysname] NOT NULL ,
[Colid] [smallint] NOT NULL ,
[Colid2] [int] NULL ,
[LastId] [int] NULL ,
CONSTRAINT [PK_Test] UNIQUE CLUSTERED
(
[id],
[Colid],
[Name]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert into Test (id, name, Colid) Select Distinct id, name, Colid from dbo.SysColumns
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
Select * from test
GO
DROP Table Test
GO
Interesting fact about this thread... it's only the 3rd one I start. I can't understand how the other 1720+ post got credited to my account. I guess I'm sleeptyping or something
. -
Amazingly no one wants to pick up on the challege Remi - not even Ron!
As for "it's only the 3rd one I start. I can't understand how the other 1720+ post got credited to my account...."
-that's because Frank donated them to you from his account because he wants you to catch up to his 5000+ posts and become "an MVP" (note..."an MVP" - just like "an SQL") - God knows you've earned it! <;-)
**ASCII stupid question, get a stupid ANSI !!!** -
Not even close to MVP... Don't have a single certif, nor 25% of the knowledge of dts, backup/restore, DR, Security and under the hood workings of sql server that Frank has. Not too mention that I don't have my own website. However I must admit that I begin to know my way around tsql and basic trouble-shooting
. -
Drop table test if it exist
Create table test
Create unique clustered index (id,colid,name)
Load test with id, name, colid from syscolumns
Id = object id of table
Name = name of the column
Colid = column id
Set colid2 to 1 through count(*) increment by 1
Set lastid to id
I think.
Mike
-
Well ?? after running the code I get an error.
Server: Msg 2627, Level 14, State 2, Line 2
Violation of UNIQUE KEY constraint 'PK_Test'. Cannot insert duplicate key in object 'Test'.
The statement has been terminated.
Which I will have to think some more about
Mike
-
How odd, remove the colid from the PK and it'll run... I guess????
I just added the third column so you have a more evident view of what's going on when you run the final select. I'll try re-running the query from pubs or Northwind tomorrow to see if there's the error there.
However I don't see how you could have a duplicate in the id/name index... let alone with the colid as a third column in the index.
Can you trace which function/proc has that duplicate value??
I've worked for a few months on the system tables and I've never seen anything like this...
-
Is is possible that you have a table function that returns a column that has the same name as an input parameter??
It's the only thing I can think of at the moment...
If you still want to see what the final select looks like, you can create the index as clustered only (not unique/not primary key). You should get the same results.
-
Thanks I will try that. I agree that I should not be getting this error.
Mike
-
FWIW, the code ran just fine for me against one of my development databases.
-
Ran fine on mine too... I'm anxious to see how I can make it fail now
.So what did you think it did before running it?
-
checking for duplicates I found: Any help here would be good. I thought that the ID had to be unique within each db.
Mike
SELECT [id],[name],[colid] FROM dbo.syscolumns
GROUP BY [id],[name],[colid]
HAVING COUNT(*) > 1
/*
ID Name colid
654625375 @ccopt 3
654625375 @ccopt 4
654625375 @handle 1
654625375 @ODBCVer 11
654625375 @p1 5
654625375 @p1 6
654625375 @p2 6
654625375 @p2 7
654625375 @p3 7
654625375 @p3 8
654625375 @p4 8
654625375 @p4 9
654625375 @p5 9
654625375 @p5 10
654625375 @p6 10
654625375 @procname 2
654625375 @rows 4
654625375 @rows 5
654625375 @scrollopt 2
654625375 @scrollopt 3
1022626686 @table_name 1
1022626686 @table_schema 2
*/
-
I had an idea you were enumerating something but I wasn't quite sure what was going on. Also I was confused by this syntax set @Int = Colid2 = ... as I had not seen it before.
It's one of those very subtle sql statements that does a lot more than you would first expect and I was impressed when I saw the output.
That's what I love about this site - always learning new things.
-
Took me about 3 minutes the first time to write it. Then I lost the code and retried to write it. Guess what, it took over 30 minutes. The hard thing to figure out is in what order, the variables are really set... But I won't say too much here
. -
God I'm slow tonight.
Just remembered that one of the unique indexes in SysObjects is :
Name, Owner (always the simple little things that trip you up).
Michael Du Bois : Looks like you have the same object with different owners.
I'm gonna modify the insert statement to make sure it ignores the duplicates now
. -
Time to go to bed... and still no correct answer.
Maybe tomorrow morning
.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply