|
|
Posted by Tony Rogerson on 11/29/07 08:10
> This should be a no-op, but it scrambles everything
>
> BEGIN
> DELETE FROM Foo
> WHERE server_name = 'server_bob';
> INSERT INTO Foo VALUES ('server_bob');
> END;
>
Why are you looking at the surrogate key - that's for the plumbing - you
should not care about it's value - it's not important - remember Codds
rules?
Because of the foriegn keys set up you'll have also deleted all the data
from all the tables that referenecd this so basically your database would be
empty - is this really an example?
And how on earth does it scramble 'server_name'?
> And you added the cost of an extra index with the UNIQUE, too!
A bit of maths; your natural key is 50 bytes long, you have this
situation....
create table Users (
user_name varchar(50) not null primary key
)
-- max row length 50
create table Tickets (
ticket_code varchar(10) not null primary key
)
-- max row length 10
create table Permissions (
ticket_code varchar(10) not null references Tickets( ticket_code ),
user_name varchar(50) not null references Users( user_name )
)
-- max row length 60
OR the surrogate way....
create table Users (
users_id int not null IDENTITY unique clustered,
user_name varchar(50) not null primary key
)
-- max row length 54
create table Tickets (
tickets_id int not null IDENTITY unique clustered,
ticket_code varchar(10) not null primary key
)
-- max row length 14
create table Permissions (
tickets_id int not null references Tickets( tickets_id ),
users_id not null references Users( users_id )
)
-- max row length 8
Is the maths clearer now?
Which table has the most rows? The base table or the tables that reference
it? The talbes that reference it usually have a many to 1 relationship
between the foreign table and the base table.
From the application perspective, what is better along your 3g or GPRS
56Kbit connection? Passing 100 rows that each are 4 bytes (the users_id for
the plumbing) or passing 100 rows that are 50 bytes each for the plumbing?
And I would really like an answer as to why you think a UNIQUE index on a 4
byte column on the base table gives a bigger overhead than using the bloated
natural keys in the reference tables.
Will you please now actually take the time and digest what is going on here
instead of burying your head in the sand as per usual because this surrogate
key method in SQL Server seems beyond you.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:7bca372f-f117-4ce9-aae2-615a9a4959ef@v4g2000hsf.googlegroups.com...
>>> Then all is well, well it would have been if they are started out
>>> properly in the first place [NOT NULL UNIQUE on natural key]. <<
>
> This should be a no-op, but it scrambles everything
>
> BEGIN
> DELETE FROM Foo
> WHERE server_name = 'server_bob';
> INSERT INTO Foo VALUES ('server_bob');
> END;
>
> And you added the cost of an extra index with the UNIQUE, too!
Navigation:
[Reply to this message]
|