Aggregation issue with parent child dimension when not using primary key

When creating a parent child dimension I am not using the primary key of the underlying table. I define the key when I create the dimension. The parent/child relationship works fine but my measure aggregation does not work.

The dimension has a regular relation type to the measure and the primary key from the underlying dimension table is used in the relationship to join in the measure. Since I have not used the primary key in the dimension and have defined my own key the define relationship page warns me that I have selected a non-key granularity attribute and I must directly or indirectly relate all other attributes to it. When I attempt to relate the attributes on the dimension structure tab I receive errors that I have created an attribute loop.

Any ideas?

CLG3,

I too have been trying to solve this issue. It seems that using a non-key granularity attribute to link to the measure group was never intended to work with Parent-Child dimensions. Like you say, doing this causes conflicts with the restrictions on the dimension's attribute relationships.

What I have done solves the issue but it is a really nasty hack:

Alter your Fact table to store the dimension's Parent-Child key rather than the dimension's primary key. In the dimension usage tab, link to the measure group via the Parent-Child key rather than the dimension's key.|||Is there a reason preventing you from using the dimension primary key as the member key, even if it means having to add a new parent key onto the dimension?|||

Hi John,

I describe the reasons I need the member key to be different to the primary key in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1635466&SiteID=1

My latest approach is:

Use the dimension's primary key to reference the dimension from the fact. Create a view on the fact that joins to the dimension and get's the dimension's "Business Key" (which is what I use as the member key for the dimension) Build the Measure Group on the fact view rather than the actual fact.

Aggregation issue with parent child dimension when not using primary key

When creating a parent child dimension I am not using the primary key of the underlying table. I define the key when I create the dimension. The parent/child relationship works fine but my measure aggregation does not work.

The dimension has a regular relation type to the measure and the primary key from the underlying dimension table is used in the relationship to join in the measure. Since I have not used the primary key in the dimension and have defined my own key the define relationship page warns me that I have selected a non-key granularity attribute and I must directly or indirectly relate all other attributes to it. When I attempt to relate the attributes on the dimension structure tab I receive errors that I have created an attribute loop.

Any ideas?

CLG3,

I too have been trying to solve this issue. It seems that using a non-key granularity attribute to link to the measure group was never intended to work with Parent-Child dimensions. Like you say, doing this causes conflicts with the restrictions on the dimension's attribute relationships.

What I have done solves the issue but it is a really nasty hack:

Alter your Fact table to store the dimension's Parent-Child key rather than the dimension's primary key. In the dimension usage tab, link to the measure group via the Parent-Child key rather than the dimension's key.|||Is there a reason preventing you from using the dimension primary key as the member key, even if it means having to add a new parent key onto the dimension?|||

Hi John,

I describe the reasons I need the member key to be different to the primary key in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1635466&SiteID=1

My latest approach is:

Use the dimension's primary key to reference the dimension from the fact. Create a view on the fact that joins to the dimension and get's the dimension's "Business Key" (which is what I use as the member key for the dimension) Build the Measure Group on the fact view rather than the actual fact.

aggregate query help

--drop table bldg
--drop table room
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
bldg org Area
-- -- ---
B1 Org1 100
B1 Org2 300
B2 Org1 400
B2 Org2 600
that works fine. however, i need two additional columns in the output.
one column is the total area of the building.
the other is the percentage of usage by the org of each building.
the output i need would look like this.
bldg org
Area Bldg Area Org Pct
-- -- ---
B1 Org1
100 400 25.00
B1 Org2
300 400 75.00
B2 Org1
400 1000 40.00
B2 Org2
600 1000 60.00
any way to do this in a select statement, i.e. no stored procedures or
cursors?Try something like this:
set nocount on
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm
go
select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
100.00 PCT
from
(select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm, t1.bldg_id) t3
join
(select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org, t1.bldg_id) t4
on t3.bldg_nm = t4.bldg_nm
order by t3.bldg_nm, t4.org
drop table bldg
drop table room
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ch" <ch@.dontemailme.com> wrote in message
news:4152E589.90B4DB41@.dontemailme.com...
> --drop table bldg
> --drop table room
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
>
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> bldg org Area
> -- -- ---
> B1 Org1 100
> B1 Org2 300
> B2 Org1 400
> B2 Org2 600
>
> that works fine. however, i need two additional columns in the output.
> one column is the total area of the building.
> the other is the percentage of usage by the org of each building.
> the output i need would look like this.
> bldg org
> Area Bldg Area Org Pct
> -- -- ---
> B1 Org1
> 100 400 25.00
> B1 Org2
> 300 400 75.00
> B2 Org1
> 400 1000 40.00
> B2 Org2
> 600 1000 60.00
> any way to do this in a select statement, i.e. no stored procedures or
> cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||> because i don't care for the inner join syntax ;)
That is a mistake IMO. The whole database world is moving away from the old join syntax. Of course
it is up to you, but perhaps time will come when you work in a project/organization that don't like
the old join syntax... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41530D1C.953CF4F1@.dontemailme.com...
> thanks, although i had to change you inner join syntax to where clauses
> because i don't care for the inner join syntax ;)
>
> "Gregory A. Larsen" wrote:
> >
> > Try something like this:
> >
> > set nocount on
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm
> > go
> >
> > select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> > 100.00 PCT
> > from
> >
> > (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm, t1.bldg_id) t3
> >
> > join
> >
> > (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> >
> > on t3.bldg_nm = t4.bldg_nm
> >
> > order by t3.bldg_nm, t4.org
> >
> > drop table bldg
> > drop table room
> >
> > --
> >
> > ----
> > ----
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:4152E589.90B4DB41@.dontemailme.com...
> > >
> > > --drop table bldg
> > > --drop table room
> > >
> > > create table bldg
> > > (
> > > bldg_id int not null primary key,
> > > bldg_nm varchar(20)
> > > )
> > > go
> > >
> > > create table room
> > > (
> > > bldg_id int not null,
> > > room_num varchar(10) not null,
> > > area numeric,
> > > org varchar(10)
> > > )
> > > go
> > > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > > go
> > >
> > > insert into bldg values (1, 'B1')
> > > insert into bldg values (2, 'B2')
> > > insert into room values (1, '1RM1', 100.00, 'Org1')
> > > insert into room values (1, '1RM2', 50.00, 'Org2')
> > > insert into room values (1, '1RM3', 250.00, 'Org2')
> > > insert into room values (2, '2RM1', 400.00, 'Org1')
> > > insert into room values (2, '2RM2', 600.00, 'Org2')
> > > go
> > >
> > >
> > > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > > group by t1.bldg_nm, t2.org
> > > order by t1.bldg_nm, t2.org
> > > go
> > >
> > > bldg org Area
> > > -- -- ---
> > > B1 Org1 100
> > > B1 Org2 300
> > > B2 Org1 400
> > > B2 Org2 600
> > >
> > >
> > > that works fine. however, i need two additional columns in the output.
> > > one column is the total area of the building.
> > > the other is the percentage of usage by the org of each building.
> > > the output i need would look like this.
> > >
> > > bldg org
> > > Area Bldg Area Org Pct
> > > -- -- ---
> > > B1 Org1
> > > 100 400 25.00
> > > B1 Org2
> > > 300 400 75.00
> > > B2 Org1
> > > 400 1000 40.00
> > > B2 Org2
> > > 600 1000 60.00
> > >
> > > any way to do this in a select statement, i.e. no stored procedures or
> > > cursors?

Age Old Question about GROUP BY clause (i think) - Probably easy answer

How does one get the primary key of the row that is joined in via a
group by aggregate clause when the aggregate is not performed on the
primary key?

For example,

Person table
(
PersonID int,
FirstName varchar(50)
LastName varchar(50)
)

Visit table
(
VisitID int,
PersonID int,
VisitDate datetime
)

These are simplified versions of my tables. I'm trying to create a
view that gets the first time each person Visited:

selectp.PersonID,
min(v.VisitDate)
fromVisit v
joinPerson p on p.PersonID = v.PersonID
group byp.PersonID

The problem is that I would like to return the VisitID in the
resultset, but when I do it expands the query since I have to also put
it in the group by clause.

What are the different ways to achieve this?
Subqueries?
Only return the date and then join off of date on the outside?

Neither of these seem too entising...

Thanks in advance for any help.

-DaveHow about:

select person.*, visit.*
from person left join visit
on person.personid = visit.visitid
and visit.visitdate =
(select min(visitdate) from visit where personid = person.personid)

FN

malcolm wrote:
> How does one get the primary key of the row that is joined in via a
> group by aggregate clause when the aggregate is not performed on the
> primary key?
> For example,
> Person table
> (
> PersonID int,
> FirstName varchar(50)
> LastName varchar(50)
> )
>
> Visit table
> (
> VisitID int,
> PersonID int,
> VisitDate datetime
> )
> These are simplified versions of my tables. I'm trying to create a
> view that gets the first time each person Visited:
> selectp.PersonID,
> min(v.VisitDate)
> fromVisit v
> joinPerson p on p.PersonID = v.PersonID
> group byp.PersonID
> The problem is that I would like to return the VisitID in the
> resultset, but when I do it expands the query since I have to also put
> it in the group by clause.
> What are the different ways to achieve this?
> Subqueries?
> Only return the date and then join off of date on the outside?
> Neither of these seem too entising...
> Thanks in advance for any help.
> -Dave|||Oops, make that third line:

on person.personid = visit.personid

But I'm sure you got the gist.

FN

fn wrote:

> How about:
> select person.*, visit.*
> from person left join visit
> on person.personid = visit.visitid
> and visit.visitdate =
> (select min(visitdate) from visit where personid = person.personid)
> FN
> malcolm wrote:
>> How does one get the primary key of the row that is joined in via a
>> group by aggregate clause when the aggregate is not performed on the
>> primary key?
>>
>> For example,
>>
>> Person table
>> (
>> PersonID int,
>> FirstName varchar(50)
>> LastName varchar(50)
>> )
>>
>>
>> Visit table
>> (
>> VisitID int,
>> PersonID int,
>> VisitDate datetime
>> )
>>
>> These are simplified versions of my tables. I'm trying to create a
>> view that gets the first time each person Visited:
>>
>> select p.PersonID,
>> min(v.VisitDate)
>> from Visit v
>> join Person p on p.PersonID = v.PersonID
>> group by p.PersonID
>>
>> The problem is that I would like to return the VisitID in the
>> resultset, but when I do it expands the query since I have to also put
>> it in the group by clause.
>>
>> What are the different ways to achieve this? Subqueries? Only return
>> the date and then join off of date on the outside?
>>
>> Neither of these seem too entising...
>>
>> Thanks in advance for any help.
>>
>> -Dave|||"malcolm" <chakachimp@.yahoo.com> wrote in message
news:4fe7c9e8.0406241624.18ca60ed@.posting.google.c om...
> How does one get the primary key of the row that is joined in via a
> group by aggregate clause when the aggregate is not performed on the
> primary key?
> For example,
> Person table
> (
> PersonID int,
> FirstName varchar(50)
> LastName varchar(50)
> )
>
> Visit table
> (
> VisitID int,
> PersonID int,
> VisitDate datetime
> )
> These are simplified versions of my tables. I'm trying to create a
> view that gets the first time each person Visited:
> select p.PersonID,
> min(v.VisitDate)
> from Visit v
> join Person p on p.PersonID = v.PersonID
> group by p.PersonID
> The problem is that I would like to return the VisitID in the
> resultset, but when I do it expands the query since I have to also put
> it in the group by clause.
> What are the different ways to achieve this?
> Subqueries?
> Only return the date and then join off of date on the outside?
> Neither of these seem too entising...
> Thanks in advance for any help.
> -Dave

SELECT P.PersonID, V1.VisitID, V1.VisitDate
FROM Persons AS P
LEFT OUTER JOIN
Visits AS V1
ON P.PersonID = V1.PersonID
LEFT OUTER JOIN
Visits AS V2
ON P.PersonID = V2.PersonID AND
V2.VisitDate < V1.VisitDate
WHERE V2.VisitDate IS NULL

--
JAG|||Please post DDL in the future. What you did post had no keys, too
many NULLs, the wrong datatypes (ever meet anyone with a fifty letter
first name? Only if they were named for a full Bible verse) and
singular table names. Is this what you meant?

CREATE TABLE Persons
(person_id INTEGER NOT NULL PRIMARY KEY, --assumption
first_name VARCHAR(15) NOT NULL, --USPS size name
last_name VARCHAR(15) NOT NULL); --USPS size name

Now I have to make assumptions about not having visits from unknown
people in my DRI.

CREATE TABLE Visits
(visit_nbr INTEGER NOT NULL PRIMARY KEY, --assumption
person_id INTEGER NOT NULL -- DRI assumption
REFERENCES Persons (person_id),
visit_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL -- default
assumption
);

>> I'm trying to create a view that gets the first time each person
visited .. The problem is that I would like to return the visit_id in
the resultset, <<

CREATE VIEW FirstVisits (person_id, visit_nbr, visit_date)
AS
SELECT V1.person_id, V1.visit_nbr, V1.visit_date
FROM Visits AS V1
WHERE V1.visit_date
= (SELECT MIN(v2.visit_date)
FROM Visits AS V2
WHERE V1.person_id = V2.person_id);

Yeah, yeah, I know it was quicky posting, but get in the habit of
doing it right all the time. Most DML problems come from bad DDL.|||You're right I did quickly slop together the question. What I posted
doesn't even come close to my example so I simply took too many
shortcuts in my post; I was simply trying to get to the point of my
question. Consider it DDL-UML ;)

The reason I didn't post working DDL is because I assumed someone
would have the answer off the top of thier head. Thanks for the
detailed response though.

-dave

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0406251250.13026daa@.posting.google.com>...
> Please post DDL in the future. What you did post had no keys, too
> many NULLs, the wrong datatypes (ever meet anyone with a fifty letter
> first name? Only if they were named for a full Bible verse) and
> singular table names. Is this what you meant?
> CREATE TABLE Persons
> (person_id INTEGER NOT NULL PRIMARY KEY, --assumption
> first_name VARCHAR(15) NOT NULL, --USPS size name
> last_name VARCHAR(15) NOT NULL); --USPS size name
> Now I have to make assumptions about not having visits from unknown
> people in my DRI.
> CREATE TABLE Visits
> (visit_nbr INTEGER NOT NULL PRIMARY KEY, --assumption
> person_id INTEGER NOT NULL -- DRI assumption
> REFERENCES Persons (person_id),
> visit_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL -- default
> assumption
> );
> >> I'm trying to create a view that gets the first time each person
> visited .. The problem is that I would like to return the visit_id in
> the resultset, <<
> CREATE VIEW FirstVisits (person_id, visit_nbr, visit_date)
> AS
> SELECT V1.person_id, V1.visit_nbr, V1.visit_date
> FROM Visits AS V1
> WHERE V1.visit_date
> = (SELECT MIN(v2.visit_date)
> FROM Visits AS V2
> WHERE V1.person_id = V2.person_id);
> Yeah, yeah, I know it was quicky posting, but get in the habit of
> doing it right all the time. Most DML problems come from bad DDL.|||Don't use a group by clause, use a subquery instead.

SELECT p.personID,v.VisitDate
FROM Person p,Visit v
WHERE p.PersonID = v.PersonID
and p.VisitDate = (SELECT MIN(v2.VisitDate)
FROM Visit v2
WHERE v2.PersonID = p.PersonID)

After Full Population, ItemCount = 0

After full population on a table with 1 million rows:
Item Count: 0
Catalog size: 1 MB
Unique Key Count: 1
I used SQL Enterprise Wizard for Full-Text Indexing on a table with 1
million rows.
Obviously, Item Count should be greater than zero.
SQL Service is running on Local System account. I have SysAdmin privileges.
the 1 unique count refers to the table.
Please post any messages you may see in your event log from MSSearch or
MSSCi.
Can you follow the advise in these kb articles?
http://support.microsoft.com/default...b;en-us;317746
http://support.microsoft.com/default...b;en-us;277549
http://support.microsoft.com/default...b;en-us;814035
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:B777D507-E501-44BA-AE29-91B3F1407675@.microsoft.com...
> After full population on a table with 1 million rows:
> Item Count: 0
> Catalog size: 1 MB
> Unique Key Count: 1
> I used SQL Enterprise Wizard for Full-Text Indexing on a table with 1
> million rows.
> Obviously, Item Count should be greater than zero.
> SQL Service is running on Local System account. I have SysAdmin
> privileges.
>
|||In this knowledge base article, Q317746
"PRB: SQL Server Full-Text Search Does Not Populate Catalogs"
It's says to make sure that:
1) Make sure that the BUILTIN\Administrators login exists in SQL Server.
-and-
2) Make sure that the Microsoft Search service is running under the Local
System account.
As a resolution, it says:
1) Grant the [NT Authority\System] user a logon to SQL Server.
For example: EXEC sp_grantlogin [NT Authority\System]
2) Add that account to the sysadmins role:
EXEC sp_addsrvrolemember @.loginame = [NT Authority\System], @.rolename =
'sysadmin'
I'm logged in using the sa account so this isn't the problem.
|||<b>http://support.microsoft.com/default...b;en-us;317746</b>
There are no errors when doing Full Population or Incremental Population.
<b>http://support.microsoft.com/default...b;en-us;277549</b>
There are no errors when doing Full Population or Incremental Population.
<b>http://support.microsoft.com/default...b;en-us;814035</b>
select fulltextcatalogproperty('northwind','itemcount')
Result: No rows returned
More insights:
1) Running SQL Server 2000. Fresh install. Not upgraded from SQL Server 7.0
2) There are no errors in the Event Application Log.
3) BUILTIN\Administrator is removed.
4) Connected using sa account.
5) Set up full-text indexing using the SQL Enterprise Full-Text Indexing...
wizard.
Other queries that I've tried:
<b>exec sp_help_fulltext_catalogs</b>
ftcatid NAME PATH
-- -- ----
5 Northwind D:\Program Files\Microsoft SQL Server\MSSQL\FTData
<b>exec sp_help_fulltext_tables</b>
TABLE_OWNER TABLE_NAME FULLTEXT_KEY_INDEX_NAME and so on...
-- -- --
dbo Categories PK_Categories
dbo Customers PK_Customers
dbo Employees PK_Employees
<b>exec sp_help_fulltext_columns</b>
Result: Several rows of columns that are indexed.
|||Can you examine the informational messages from MSSearch and MSSCi in the
application log - the messages will likely not be errors.
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:EDF955BC-86F2-44F2-A033-9FBE8F4BA065@.microsoft.com...
> <b>http://support.microsoft.com/default...b;en-us;317746</b>
> There are no errors when doing Full Population or Incremental Population.
> <b>http://support.microsoft.com/default...b;en-us;277549</b>
> There are no errors when doing Full Population or Incremental Population.
> <b>http://support.microsoft.com/default...b;en-us;814035</b>
> select fulltextcatalogproperty('northwind','itemcount')
> Result: No rows returned
> More insights:
> 1) Running SQL Server 2000. Fresh install. Not upgraded from SQL Server
> 7.0
> 2) There are no errors in the Event Application Log.
> 3) BUILTIN\Administrator is removed.
> 4) Connected using sa account.
> 5) Set up full-text indexing using the SQL Enterprise Full-Text
> Indexing...
> wizard.
> Other queries that I've tried:
> <b>exec sp_help_fulltext_catalogs</b>
> ftcatid NAME PATH
> -- -- ----
> 5 Northwind D:\Program Files\Microsoft SQL Server\MSSQL\FTData
> <b>exec sp_help_fulltext_tables</b>
> TABLE_OWNER TABLE_NAME FULLTEXT_KEY_INDEX_NAME and so on...
> -- -- --
> dbo Categories PK_Categories
> dbo Customers PK_Customers
> dbo Employees PK_Employees
> <b>exec sp_help_fulltext_columns</b>
> Result: Several rows of columns that are indexed.
>
|||A) Cleared the Event Application Log.
B) Ran the Full Population command.
C) Checked the Event Application Log.
Result:
1) No Information messages
2) No Error messages
3) No Warning messages
|||can you open a command prompt and navigate to C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA\SQLServer\GatherLogs>
or
C:\Program Files\Microsoft SQL
Server\MSSQL$InstanceName\FTDATA\SQLServer\GatherL ogs>
zip up the contents you find there and either post them here or send them to
me offline.
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:C30E9BE9-25B8-4238-A3F1-A491A5C2C29C@.microsoft.com...
> A) Cleared the Event Application Log.
> B) Ran the Full Population command.
> C) Checked the Event Application Log.
> Result:
> 1) No Information messages
> 2) No Error messages
> 3) No Warning messages
>
|||the sa account has no bearing on whether the builtin\administrators role is
part of the system administrators role.
Please add it back in or follow the instructions in this kb article.
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:31616064-05D9-475A-8A76-6C63F5CF1CF9@.microsoft.com...
> In this knowledge base article, Q317746
> "PRB: SQL Server Full-Text Search Does Not Populate Catalogs"
> It's says to make sure that:
> 1) Make sure that the BUILTIN\Administrators login exists in SQL Server.
> -and-
> 2) Make sure that the Microsoft Search service is running under the Local
> System account.
> As a resolution, it says:
> 1) Grant the [NT Authority\System] user a logon to SQL Server.
> For example: EXEC sp_grantlogin [NT Authority\System]
> 2) Add that account to the sysadmins role:
> EXEC sp_addsrvrolemember @.loginame = [NT Authority\System], @.rolename =
> 'sysadmin'
> I'm logged in using the sa account so this isn't the problem.
>
|||I looked at the list of Server Roles. This is what we have.
Bulk Insert Administrators
Database Creators
Disk Administrators
Process Administrators
Security Administrators
Server Administrators
Setup Administrators
System Administrators
So, which role is the "BUILTIN\Administrator"? If it's System Administrator,
then it's already in there.
|||double click on the system administrator role. Is the BuiltIn\Administrators
group there?
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:49E74919-224A-4314-93B4-77EBA2395C18@.microsoft.com...
> I looked at the list of Server Roles. This is what we have.
> Bulk Insert Administrators
> Database Creators
> Disk Administrators
> Process Administrators
> Security Administrators
> Server Administrators
> Setup Administrators
> System Administrators
> So, which role is the "BUILTIN\Administrator"? If it's System
Administrator,
> then it's already in there.
>

After an insert, how do I get the primary key of the new row?

I am using C# and ADO.NET

After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @.@.IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @.@.IDENTITY'."}. does anyone know what to do?Hi,

have a look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

If you are on SQL2k5 you can use the new OUPUT parameter and put the information back via this technology.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi

You can use DataTables and DataAdapter to make insertion in the database after the insertion into the database the DataAdapter retrive the values from database for automatically.

You have to use CommandBuilder for this purpose.
Make changes to the DataTable and then run UpdateDataBase method on that table of the DataAdaper..check MSDN for details.

Or you can create Stord procedure to Insert in DB after inseration it will get the PK and will return it for you.

OR

Just Select Max(pk_id) From Table1

|||Akbar,

Select Max(pk_id) seems like a simple way to do this. In fact, I feel stupid for not thinking of it. One followup question to this method: Does this method fail if there are multiple writers writing to this database? This fails if someone else writes to the database before you send your second query, correct?|||The recommended ways of doing that is by using scope_identity()|||The aggregate MAX is a function that was used in the old days to get the highest / recent value. Its not practical / preferable / suggested / recommended / whatever in days of writing with multiple users to a database. Like Joyei said, I would rather use the SCOPE_IDENTITY() approach.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Someone asked me the same thing today and after a couple of hours I've came up with this (the code is in VB but I will try to explain the concept as well as I can):

I'll use a sample table from a SQL Server (I work with an SQL 2000 right now). The table is called _FluxModels and has two fields FluxModelID (an identity field) and FluxModel (a varchar (50) field).

In order to update the table I use a SqlConnection, a SqlDataAdapter, a DataTable and a DataRow.

Instead of generating the InsertCommand with a CommandBuilder, I declare a separate SqlCommand and use this for getting the CommandText and Parameters.

Then I pass the CommandText and the Parameters to the InsertCommand of the DataAdapter.

I added a new parameter "@.ID" (you can use any name you like) of type SqlDbType.Int.

I added this text to the CommandText of the InsertCommand : select @.ID=SCOPE_IDENTITY()

I set the UpdatedRowSource property of the InsertCommand to UpdateRowSource.OutputParameters (actually it works without this setting).

Now, whenever I add a new record, I have the FluxModelID returned in the @.ID parameter of the InsertCommand of the SqlDataAdapter.

Maybe this is not the best way for doing this but it works.

Here is the code I used:

Dim cn As New SqlConnection("Data Source=[YOUR SQL SERVER];Initial Catalog=[YOUR DATABASE NAME];Integrated Security=True")

Dim da As New SqlDataAdapter("Select * from _FluxModels", cn)

Dim db As New SqlCommandBuilder(da)

Dim dt As New DataTable

Dim drr() As DataRow 'array of DataRows used for updating

Dim dr As DataRow

Dim NewCmd As SqlCommand

Dim i As Integer

Dim p As SqlParameter

cn.Open()

'Use the NewCmd instead of da.InsertCommand

NewCmd = db.GetInsertCommand

'Initialize the da.InserCommand as a new SqlCommand

da.InsertCommand = New SqlCommand

'Pass the parameters from the generated command

For i = 0 To NewCmd.Parameters.Count - 1

p = New SqlParameter

p.ParameterName = NewCmd.Parameters(i).ParameterName

p.SourceColumn = NewCmd.Parameters(i).SourceColumn

p.Direction = NewCmd.Parameters(i).Direction

p.DbType = NewCmd.Parameters(i).DbType

p.Value = NewCmd.Parameters(i).Value

da.InsertCommand.Parameters.Add(p)

Next

'Pass the connection to the InsertCommand

da.InsertCommand.Connection = da.SelectCommand.Connection

'and the Commandtext

da.InsertCommand.CommandText = NewCmd.CommandText

'modify the CommandText

da.InsertCommand.CommandText = da.InsertCommand.CommandText & " select @.ID=SCOPE_IDENTITY()"

'add the parameter for the identity

da.InsertCommand.Parameters.Add("@.ID", SqlDbType.Int, 4, "")

'set the parameter direction

da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Direction = ParameterDirection.Output

'works without the next line

'da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

'Fill the DataTable

da.Fill(dt)

'Add the new record

dr = dt.NewRow

dr("FluxModel") = "BBB"

dt.Rows.Add(dr)

'Use an array of 1 DataRow to update the database

ReDim drr(0)

drr(0) = dr

da.Update(drr)

'Test if the parameter contains a valid value

If Not da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value Is System.DBNull.Value Then

'Display the identity field of the new record

MsgBox(da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value)

End If

'Close the connection to the database

cn.Close()

I hope this will help you.

After an insert, how do I get the primary key of the new row?

I am using C# and ADO.NET

After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @.@.IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @.@.IDENTITY'."}. does anyone know what to do?Hi,

have a look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

If you are on SQL2k5 you can use the new OUPUT parameter and put the information back via this technology.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi

You can use DataTables and DataAdapter to make insertion in the database after the insertion into the database the DataAdapter retrive the values from database for automatically.

You have to use CommandBuilder for this purpose.
Make changes to the DataTable and then run UpdateDataBase method on that table of the DataAdaper..check MSDN for details.

Or you can create Stord procedure to Insert in DB after inseration it will get the PK and will return it for you.

OR

Just Select Max(pk_id) From Table1

|||Akbar,

Select Max(pk_id) seems like a simple way to do this. In fact, I feel stupid for not thinking of it. One followup question to this method: Does this method fail if there are multiple writers writing to this database? This fails if someone else writes to the database before you send your second query, correct?|||The recommended ways of doing that is by using scope_identity()|||The aggregate MAX is a function that was used in the old days to get the highest / recent value. Its not practical / preferable / suggested / recommended / whatever in days of writing with multiple users to a database. Like Joyei said, I would rather use the SCOPE_IDENTITY() approach.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Someone asked me the same thing today and after a couple of hours I've came up with this (the code is in VB but I will try to explain the concept as well as I can):

I'll use a sample table from a SQL Server (I work with an SQL 2000 right now). The table is called _FluxModels and has two fields FluxModelID (an identity field) and FluxModel (a varchar (50) field).

In order to update the table I use a SqlConnection, a SqlDataAdapter, a DataTable and a DataRow.

Instead of generating the InsertCommand with a CommandBuilder, I declare a separate SqlCommand and use this for getting the CommandText and Parameters.

Then I pass the CommandText and the Parameters to the InsertCommand of the DataAdapter.

I added a new parameter "@.ID" (you can use any name you like) of type SqlDbType.Int.

I added this text to the CommandText of the InsertCommand : select @.ID=SCOPE_IDENTITY()

I set the UpdatedRowSource property of the InsertCommand to UpdateRowSource.OutputParameters (actually it works without this setting).

Now, whenever I add a new record, I have the FluxModelID returned in the @.ID parameter of the InsertCommand of the SqlDataAdapter.

Maybe this is not the best way for doing this but it works.

Here is the code I used:

Dim cn As New SqlConnection("Data Source=[YOUR SQL SERVER];Initial Catalog=[YOUR DATABASE NAME];Integrated Security=True")

Dim da As New SqlDataAdapter("Select * from _FluxModels", cn)

Dim db As New SqlCommandBuilder(da)

Dim dt As New DataTable

Dim drr() As DataRow 'array of DataRows used for updating

Dim dr As DataRow

Dim NewCmd As SqlCommand

Dim i As Integer

Dim p As SqlParameter

cn.Open()

'Use the NewCmd instead of da.InsertCommand

NewCmd = db.GetInsertCommand

'Initialize the da.InserCommand as a new SqlCommand

da.InsertCommand = New SqlCommand

'Pass the parameters from the generated command

For i = 0 To NewCmd.Parameters.Count - 1

p = New SqlParameter

p.ParameterName = NewCmd.Parameters(i).ParameterName

p.SourceColumn = NewCmd.Parameters(i).SourceColumn

p.Direction = NewCmd.Parameters(i).Direction

p.DbType = NewCmd.Parameters(i).DbType

p.Value = NewCmd.Parameters(i).Value

da.InsertCommand.Parameters.Add(p)

Next

'Pass the connection to the InsertCommand

da.InsertCommand.Connection = da.SelectCommand.Connection

'and the Commandtext

da.InsertCommand.CommandText = NewCmd.CommandText

'modify the CommandText

da.InsertCommand.CommandText = da.InsertCommand.CommandText & " select @.ID=SCOPE_IDENTITY()"

'add the parameter for the identity

da.InsertCommand.Parameters.Add("@.ID", SqlDbType.Int, 4, "")

'set the parameter direction

da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Direction = ParameterDirection.Output

'works without the next line

'da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

'Fill the DataTable

da.Fill(dt)

'Add the new record

dr = dt.NewRow

dr("FluxModel") = "BBB"

dt.Rows.Add(dr)

'Use an array of 1 DataRow to update the database

ReDim drr(0)

drr(0) = dr

da.Update(drr)

'Test if the parameter contains a valid value

If Not da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value Is System.DBNull.Value Then

'Display the identity field of the new record

MsgBox(da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value)

End If

'Close the connection to the database

cn.Close()

I hope this will help you.

|||this is very nice solution

THANK YOU!

Affect of FillFactor on Index

I am trying to understand how the fillfactor might be affecting the clusered
index.
The primary key (nonclustered) on the table is a composite index of two (int
data type) fields, where one field is an identity seed incrementing by one,
and the other field being more constant.
The non-unique clustered index (that seems to fragment quite often) is a
composite index of four columns:
lname varchar(35)
fname varchar(35)
mname varchar(35)
rptype char(12)
This index has a fillfactor of zero.
The row size on the table is about 900 bytes.
The number of reads versus writes is about 50:1 under normal activity, but
sometimes (once every two weeks or so) this table is loaded with
approximately 200-500 new records at one time.
With a row size of 900 bytes, I am getting about 8 rows a page.
I am trying to wrap by brain around what affect updates and inserts have.
Please help explain.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1cbrichards via SQLMonster.com wrote:
> I am trying to understand how the fillfactor might be affecting the clusered
> index.
> The primary key (nonclustered) on the table is a composite index of two (int
> data type) fields, where one field is an identity seed incrementing by one,
> and the other field being more constant.
> The non-unique clustered index (that seems to fragment quite often) is a
> composite index of four columns:
> lname varchar(35)
> fname varchar(35)
> mname varchar(35)
> rptype char(12)
> This index has a fillfactor of zero.
> The row size on the table is about 900 bytes.
> The number of reads versus writes is about 50:1 under normal activity, but
> sometimes (once every two weeks or so) this table is loaded with
> approximately 200-500 new records at one time.
> With a row size of 900 bytes, I am getting about 8 rows a page.
> I am trying to wrap by brain around what affect updates and inserts have.
> Please help explain.
>
With a fill rate of 100%, clustered on lname (assuming this is "last
name"), you're going to see page splits (i.e. fragmentation) any time
you insert a new last name between two existing ones. Example:
LNAME: Jenkins
LNAME: Jones
These are stored on a data page, that page is 100% full. You insert a
new record:
LNAME: Johnson
Due to the clustered index, this new record belongs between the two
existing ones. However, since the data page containing those records is
full, it has to be split to make room for the new record.
Does that help?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy...that makes sense.
When it comes to updates, at 900 bytes per row (and let's say 8 rows per page)
, that would leave approximately 800 bytes free per page (before any page
splits factor in) for updates, correct?
Tracy McKibben wrote:
>> I am trying to understand how the fillfactor might be affecting the clusered
>> index.
>[quoted text clipped - 20 lines]
>> I am trying to wrap by brain around what affect updates and inserts have.
>> Please help explain.
>With a fill rate of 100%, clustered on lname (assuming this is "last
>name"), you're going to see page splits (i.e. fragmentation) any time
>you insert a new last name between two existing ones. Example:
>LNAME: Jenkins
>LNAME: Jones
>These are stored on a data page, that page is 100% full. You insert a
>new record:
>LNAME: Johnson
>Due to the clustered index, this new record belongs between the two
>existing ones. However, since the data page containing those records is
>full, it has to be split to make room for the new record.
>Does that help?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> Thanks Tracy...that makes sense.
> When it comes to updates, at 900 bytes per row (and let's say 8 rows per page)
> , that would leave approximately 800 bytes free per page (before any page
> splits factor in) for updates, correct?
Sounds right, I think...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, if I have 800 bytes free per page (based upon 900 and some bytes per row),
reducing the fillfactor to say, 80%, would not be that beneficial in reducing
page splits due to updates, correct?
Tracy McKibben wrote:
>> Thanks Tracy...that makes sense.
>> When it comes to updates, at 900 bytes per row (and let's say 8 rows per page)
>> , that would leave approximately 800 bytes free per page (before any page
>> splits factor in) for updates, correct?
>Sounds right, I think...
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> So, if I have 800 bytes free per page (based upon 900 and some bytes per row),
> reducing the fillfactor to say, 80%, would not be that beneficial in reducing
> page splits due to updates, correct?
>
Probably not. You'll reduce the frequency of the page splits, but you
will still eventually encounter them. Your options are to monitor
fragmentation, reindexing when necessary, or consider clustering on
different keys that aren't so prone to splitting.
I have a script that you might find useful for handling the
fragmentation. I'm currently rebuilding my web site, and don't have the
script online, but you can pull it from Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded".
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Affect of FillFactor on Index

I am trying to understand how the fillfactor might be affecting the clusered
index.
The primary key (nonclustered) on the table is a composite index of two (int
data type) fields, where one field is an identity seed incrementing by one,
and the other field being more constant.
The non-unique clustered index (that seems to fragment quite often) is a
composite index of four columns:
lname varchar(35)
fname varchar(35)
mname varchar(35)
rptype char(12)
This index has a fillfactor of zero.
The row size on the table is about 900 bytes.
The number of reads versus writes is about 50:1 under normal activity, but
sometimes (once every two weeks or so) this table is loaded with
approximately 200-500 new records at one time.
With a row size of 900 bytes, I am getting about 8 rows a page.
I am trying to wrap by brain around what affect updates and inserts have.
Please help explain.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1cbrichards via droptable.com wrote:
> I am trying to understand how the fillfactor might be affecting the cluser
ed
> index.
> The primary key (nonclustered) on the table is a composite index of two (i
nt
> data type) fields, where one field is an identity seed incrementing by one
,
> and the other field being more constant.
> The non-unique clustered index (that seems to fragment quite often) is a
> composite index of four columns:
> lname varchar(35)
> fname varchar(35)
> mname varchar(35)
> rptype char(12)
> This index has a fillfactor of zero.
> The row size on the table is about 900 bytes.
> The number of reads versus writes is about 50:1 under normal activity, but
> sometimes (once every two weeks or so) this table is loaded with
> approximately 200-500 new records at one time.
> With a row size of 900 bytes, I am getting about 8 rows a page.
> I am trying to wrap by brain around what affect updates and inserts have.
> Please help explain.
>
With a fill rate of 100%, clustered on lname (assuming this is "last
name"), you're going to see page splits (i.e. fragmentation) any time
you insert a new last name between two existing ones. Example:
LNAME: Jenkins
LNAME: Jones
These are stored on a data page, that page is 100% full. You insert a
new record:
LNAME: Johnson
Due to the clustered index, this new record belongs between the two
existing ones. However, since the data page containing those records is
full, it has to be split to make room for the new record.
Does that help?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy...that makes sense.
When it comes to updates, at 900 bytes per row (and let's say 8 rows per pag
e)
, that would leave approximately 800 bytes free per page (before any page
splits factor in) for updates, correct?
Tracy McKibben wrote:
>[quoted text clipped - 20 lines]
>With a fill rate of 100%, clustered on lname (assuming this is "last
>name"), you're going to see page splits (i.e. fragmentation) any time
>you insert a new last name between two existing ones. Example:
>LNAME: Jenkins
>LNAME: Jones
>These are stored on a data page, that page is 100% full. You insert a
>new record:
>LNAME: Johnson
>Due to the clustered index, this new record belongs between the two
>existing ones. However, since the data page containing those records is
>full, it has to be split to make room for the new record.
>Does that help?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> Thanks Tracy...that makes sense.
> When it comes to updates, at 900 bytes per row (and let's say 8 rows per p
age)
> , that would leave approximately 800 bytes free per page (before any page
> splits factor in) for updates, correct?
Sounds right, I think...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, if I have 800 bytes free per page (based upon 900 and some bytes per row
),
reducing the fillfactor to say, 80%, would not be that beneficial in reducin
g
page splits due to updates, correct?
Tracy McKibben wrote:
>Sounds right, I think...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> So, if I have 800 bytes free per page (based upon 900 and some bytes per r
ow),
> reducing the fillfactor to say, 80%, would not be that beneficial in reduc
ing
> page splits due to updates, correct?
>
Probably not. You'll reduce the frequency of the page splits, but you
will still eventually encounter them. Your options are to monitor
fragmentation, reindexing when necessary, or consider clustering on
different keys that aren't so prone to splitting.
I have a script that you might find useful for handling the
fragmentation. I'm currently rebuilding my web site, and don't have the
script online, but you can pull it from Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded".
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Advise Please

hello
I have two related table

table1
cId
cDesc

table2
Id
Name
phone
cId

These two tables are related
where cId in table 1 is primary key and cId in table2 is foreign key

If I want to delete a record in table 1 which is related to table 2
Which is faster and more accurate

should I write my stored procedure like this
if exists(select * from table2 where cID = @.CID)
delete from table1 where cID = @.CID
else
return 0
-------------
or
-------------
delete from table1 where cID = @.CID

if @.@.error<>0
return 0

Neither of the above will work if you have a primary-foriegn key relationship as it would break the relationship.

Are you wanting to delete the record from table1 and any related records from table2, or do you want to delete from table1 only if there are no references to it in table2?

|||

wel i gues option 2 is faster but first u need to delete it from table 2 otherwise it gives u error

|||

Hi,

I recently ran into such a case and used the following code:
DELETE FROM TABLE2 WHERE id = @.ID
DELETE FROM TABLE1 WHERE id = @.ID
this worked perfectly for me.
But there is other way to achieve your goal:
just define the following foreign key on table2:
FOREIGN KEY (Id) REFERENCES table1 (cID) ON DELETE CASCADE

I hope this heps

|||

The second one

|||

I dont want to delete child if it exists...

Which the better way referenced to my first post

|||

according to my knowledge u need yo delete the refrence from child table i.e. foreign key relation ......

may be im wrong do let me know

|||

It seems that there is a big misunderstanding,

wht I am trying to do is :

check if the parent table has a child, if so message the user u cannot delete (The parent Record)

if the parent does not have a child delete the record..(The parent Record)

So I mentioned in my first post 2 options to do that and I was wondering which is the better and the faster

I hope I made my idea clear now
Thank you

|||

The 2nd option is indeed faster and better depending upon how many rows are in your 2nd table.

|||But is it apropriate to coz sql an error??|||

As long as your trapping the error and resetting the error object I dont see any problems with it. Just be sure you have the FK constraints on your second table otherwise this method will not work.

|||

well now v get the xact n accurate situation dat wht u need n wht u r doing...wel i gues u r on the rite track n again the 2nd option is beter wen eva u get error from sql u can catch it in try catch block n after verifying that its a FK constraint error u can simply display that child record exists.....

|||

Are there any disadvantages using catch block statments??

|||

Are there any disadvantages using "try catch" block statments??

|||

Using Try Catch block statements are highly advisable when you know that there is a possablity of an exception being thrown. Good error handling is always a good idea in any application.

Advice Requested on Primary Key: Is char(20) better than binary(20)

I am not posting the DDL because it is not relevant to my question.
So far I have been unable to find a decent "natural key" for a table I am
designing. The true "natural key" is varbinary(MAX), which is unusable, and
so I have to consider a surrogate key, which is using SHA1 agorithm to
generate a 20 byte value of the natural key.
My Question:
Which would be the best choice for storing
20 bytes from SHA1 algorithm?
char(20)
binary(20)
I would like to choose the best datatype for Joins/Performance, etc. I am
using SQL Server 2005.
Thanks
Russell Mangel
Las Vegas, NVBINARY. The HashBytes function returns a VARBINARY value.
"Russell Mangel" <russell@.tymer.net> wrote in message
news:eUnwdJAkGHA.2004@.TK2MSFTNGP04.phx.gbl...
>I am not posting the DDL because it is not relevant to my question.
> So far I have been unable to find a decent "natural key" for a table I am
> designing. The true "natural key" is varbinary(MAX), which is unusable,
> and so I have to consider a surrogate key, which is using SHA1 agorithm to
> generate a 20 byte value of the natural key.
> My Question:
> Which would be the best choice for storing
> 20 bytes from SHA1 algorithm?
> char(20)
> binary(20)
> I would like to choose the best datatype for Joins/Performance, etc. I am
> using SQL Server 2005.
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>|||Russell Mangel wrote:
> I am not posting the DDL because it is not relevant to my question.
> So far I have been unable to find a decent "natural key" for a table I am
> designing. The true "natural key" is varbinary(MAX), which is unusable, an
d
> so I have to consider a surrogate key, which is using SHA1 agorithm to
> generate a 20 byte value of the natural key.
> My Question:
> Which would be the best choice for storing
> 20 bytes from SHA1 algorithm?
> char(20)
> binary(20)
> I would like to choose the best datatype for Joins/Performance, etc. I am
> using SQL Server 2005.
> Thanks
> Russell Mangel
> Las Vegas, NV
Your hash wouldn't usually be called a surrogate key because it's
derived from real, meaningful data. I'd prefer to call it a logical key
or business key but I think it's fair enough to call it a natural key
if you like.
Even without the hash, your problem wasn't that you couldn't find the
key but that you had no easy way to enforce it. It's an annoyance that
SQL Server won't permit unique constraints on "large" keys. I suspect
the reason is due to the fact that only B-tree indexes are supported
and that makes large keys impractical in Microsoft's way of thinking.

> Which would be the best choice for storing
> 20 bytes from SHA1 algorithm?
Use BINARY to avoid the overhead and complications of collations.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> My Question: Which would be the best choice for storing 20 bytes from SHA
1 algorithm?
char(20)
binary(20) <<
If you use CHAR(20), then you can display it easier, but that is the
only advantage I can think of. It would be nice to have a Teradata
style hashing routine that would give you a true surrogate key, hidden
from the users and with hash clash managed by the system. Sigh!|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150329186.339767.74520@.g10g2000cwb.googlegroups.com...
> char(20)
> binary(20) <<
> If you use CHAR(20), then you can display it easier, but that is the
> only advantage I can think of. It would be nice to have a Teradata
> style hashing routine that would give you a true surrogate key, hidden
> from the users and with hash clash managed by the system. Sigh!
Unless you convert the SHA1 hash to some character format, like Base64,
you'll run into problems displaying some hash codes that contain a 0x00 and
other special control characters in them. Base64, ASCII hexadecimal
representation, etc., of a 20-byte hash will require more than a CHAR(20) to
store.
-- Demonstration of CHAR(10) with an embedded 0x00 char
DECLARE @.x CHAR(10)
SELECT @.x = 'ABC' + CHAR(0) + 'DEF'
SELECT @.x
PRINT @.x

Advice requested : Choosing the best Key for a table

I would appreciate good advice about the following Schema for a Table called
Messages. The table will hold archived messages from Microsoft Outlook. The
SQL 2005 schema that I have posted, is simply what the data looks like in
it's *raw* form. I intend to modify the schema in the most efficient way
possible. This table was created using MS SQL server 2005 and uses two if
the new (MAX) data types.
You can help me the most by focusing on the best Key for the Messages table.
The "natural key" for the table (in raw form), would be MessageID, so lets
discuss it's problems. The first problem is that varbinary(MAX) (new in
SQL2005), does not allow Primary Key constraints, and so it should be!
Obviously we will need to use a smaller DataType than varbinary(MAX), once
you pick something reasonable in size you are allowed to create PrimaryKey
constraint. MessageID's do not have a documented or published limit, they
can be *any* length. From my observations MessageID will typically be 70-340
bytes. So I suppose we could set varbinary(500). This would allow us to
handle most situations. But this is still way too long for a Primary Key in
my opinion. The other columns that are set as xxx(MAX) have similar
problems, ignore these columns for now until we find a decent Key for the
table.
In my opinion this Table does not have a "Natural Key" we can use it's
simply to long, and this table will have "hundreds of millions of rows", so
I am worried about JOIN performance. Once a message is archived (then
deleted), from it's original location and placed in SQL server, the
MessageID has no value and is never displayed to the user. There are more
complications because this table is replicated using (Merge replication).
The database is a distributed database, so using auto identity columns
complicate replication issues, not too mention irritating --Celko--. So I
really want to find a suitable surrogate key.
So what to do?
1. Use RSA to reduce MessageID to 20 bytes, and deal with the collisions?
2. Use a rowGuid (Outch!)
At this point I like option #1 the best because it works nicely with
"replication" as the original MessageID is a Globally Unique Identifier, so
no two MessageID's (PR_ENTRY_ID long term) will ever exist. But I think
there is are issues with generating these shorter keys using RSA, collisions
will happen. Additionally it is quite simple to encode the MessageID using
RSA. The MessageID would now be 20 bytes or [varbinary](20).
Does anyone have any good suggestions?
Thanks for your time.
Russell Mangel
Las Vegas, NV
-- Note: I commented out the Constraint, as varbinary(MAX) is not allowed.
-- This schema is just what the data looks like in the real world.
CREATE TABLE
[dbo].[Messages]
(
[MessageID] [varbinary](MAX) NOT NULL,
[ParentID] [varbinary](MAX) NOT NULL, -- FK for Folders Table
[SenderID] [int] NOT NULL, -- FK for Sender of message
[MessageType] [int] NOT NULL, -- FK MessageType
[Length] [bigint] NOT NULL,
[To] [nvarchar](MAX) NOT NULL,
[CC] [nvarchar](MAX) NOT NULL,
[Subject] [nvarchar](MAX) NOT NULL,
[Body] [ntext] NOT NULL,
[HasAttachment] [bit] NOT NULL,
-- CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
-- (
-- [MessageID]
-- )
)ON [PRIMARY]
-- End SchemaHi, Russel
Well, why do you want the MessageId column to be varbinary datatype? Any
Reason?
Sure, I don't know your business requirements so my guess is (if you do not
want to use an IDENTITYT)
BTW an IDENTITY in most cases is a good candidate for surrogate keys
CREATE TABLE Messages
(
MessageId INT NOT NULL PRIMARY KEY
)
Then you will need to generate a next MessageId by your self like
DECLARE @.MaxId INT
BEGIN TRAN
SELECT @.MaxId =COALESCE(MAX(MessageId),0) FROM Messages WITH
(UPDLOCK,HOLDLOCK)
INSERT INTO Messages SELECT @.MaxId ,....... FROM Table
COMMIT TRAN
"Russell Mangel" <russell@.tymer.net> wrote in message
news:%23JD8adcaGHA.3304@.TK2MSFTNGP04.phx.gbl...
>I would appreciate good advice about the following Schema for a Table
>called Messages. The table will hold archived messages from Microsoft
>Outlook. The SQL 2005 schema that I have posted, is simply what the data
>looks like in it's *raw* form. I intend to modify the schema in the most
>efficient way possible. This table was created using MS SQL server 2005 and
>uses two if the new (MAX) data types.
> You can help me the most by focusing on the best Key for the Messages
> table.
> The "natural key" for the table (in raw form), would be MessageID, so lets
> discuss it's problems. The first problem is that varbinary(MAX) (new in
> SQL2005), does not allow Primary Key constraints, and so it should be!
> Obviously we will need to use a smaller DataType than varbinary(MAX), once
> you pick something reasonable in size you are allowed to create PrimaryKey
> constraint. MessageID's do not have a documented or published limit, they
> can be *any* length. From my observations MessageID will typically be
> 70-340 bytes. So I suppose we could set varbinary(500). This would allow
> us to handle most situations. But this is still way too long for a Primary
> Key in my opinion. The other columns that are set as xxx(MAX) have similar
> problems, ignore these columns for now until we find a decent Key for the
> table.
> In my opinion this Table does not have a "Natural Key" we can use it's
> simply to long, and this table will have "hundreds of millions of rows",
> so I am worried about JOIN performance. Once a message is archived (then
> deleted), from it's original location and placed in SQL server, the
> MessageID has no value and is never displayed to the user. There are more
> complications because this table is replicated using (Merge replication).
> The database is a distributed database, so using auto identity columns
> complicate replication issues, not too mention irritating --Celko--. So I
> really want to find a suitable surrogate key.
> So what to do?
> 1. Use RSA to reduce MessageID to 20 bytes, and deal with the collisions?
> 2. Use a rowGuid (Outch!)
> At this point I like option #1 the best because it works nicely with
> "replication" as the original MessageID is a Globally Unique Identifier,
> so no two MessageID's (PR_ENTRY_ID long term) will ever exist. But I think
> there is are issues with generating these shorter keys using RSA,
> collisions will happen. Additionally it is quite simple to encode the
> MessageID using RSA. The MessageID would now be 20 bytes or
> [varbinary](20).
> Does anyone have any good suggestions?
> Thanks for your time.
> Russell Mangel
> Las Vegas, NV
>
> -- Note: I commented out the Constraint, as varbinary(MAX) is not
> allowed.
> -- This schema is just what the data looks like in the real world.
> CREATE TABLE
> [dbo].[Messages]
> (
> [MessageID] [varbinary](MAX) NOT NULL,
> [ParentID] [varbinary](MAX) NOT NULL, -- FK for Folders Table
> [SenderID] [int] NOT NULL, -- FK for Sender of message
> [MessageType] [int] NOT NULL, -- FK MessageType
> [Length] [bigint] NOT NULL,
> [To] [nvarchar](MAX) NOT NULL,
> [CC] [nvarchar](MAX) NOT NULL,
> [Subject] [nvarchar](MAX) NOT NULL,
> [Body] [ntext] NOT NULL,
> [HasAttachment] [bit] NOT NULL,
> -- CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
> -- (
> -- [MessageID]
> -- )
> )ON [PRIMARY]
> -- End Schema
>|||Can MessageID ever change for a given message? If not, then use it as the
natural key (as silly as this name sounds in this case). Put a unique
constraint on it, but create a surrogate key for joins.
Although everybody else will say that the GUID is a poor choice for a
primary key, I'd say use it but NEVER as a clustered key. Perhaps you'd have
more use for a clustered index on a datetime column (although I can't see on
e
in your proposed schema). Maybe you could also use an IDENTITY column - it
makes a great
candidate for a clustered index.
Of course a better option still would be to do more research and find a
better natural key (maybe even composite) candidate, then use a single colum
n
surrogate key for joins and references.
ML
http://milambda.blogspot.com/|||There is 1 other Column in the Messages table "Created" it's a DateTime,
that might help us find a better key.
Not sure what to call this Key as it has two columns, must be
Composite/Natural?
Is it possible for a person (SenderID), to send a message at the exact same
time?
Hmmmmmmm. Got to think about this for a bit, and maybe I can find another
column that I can use for Composite key.
-- Here is the new Schema,
CREATE TABLE
[dbo].[Messages]
(
[MessageID] [varbinary](max) NOT NULL,
[ParentID] [varbinary](max) NOT NULL,
[SenderID] [int] NOT NULL,
[MessageType] [int] NOT NULL,
[Length] [bigint] NOT NULL,
[To] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CC] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Subject] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HasAttachment] [bit] NOT NULL,
[Created] [datetime] NOT NULL, -- New Column
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[SenderID] ASC,
[Created] ASC
)
)ON [PRIMARY]|||Russell Mangel wrote:
> I would appreciate good advice about the following Schema for a Table call
ed
> Messages. The table will hold archived messages from Microsoft Outlook. Th
e
> SQL 2005 schema that I have posted, is simply what the data looks like in
> it's *raw* form. I intend to modify the schema in the most efficient way
> possible. This table was created using MS SQL server 2005 and uses two if
> the new (MAX) data types.
> You can help me the most by focusing on the best Key for the Messages tabl
e.
> The "natural key" for the table (in raw form), would be MessageID, so lets
> discuss it's problems. The first problem is that varbinary(MAX) (new in
> SQL2005), does not allow Primary Key constraints, and so it should be!
> Obviously we will need to use a smaller DataType than varbinary(MAX), once
> you pick something reasonable in size you are allowed to create PrimaryKey
> constraint. MessageID's do not have a documented or published limit, they
> can be *any* length. From my observations MessageID will typically be 70-3
40
> bytes. So I suppose we could set varbinary(500). This would allow us to
> handle most situations. But this is still way too long for a Primary Key i
n
> my opinion. The other columns that are set as xxx(MAX) have similar
> problems, ignore these columns for now until we find a decent Key for the
> table.
> In my opinion this Table does not have a "Natural Key" we can use it's
> simply to long, and this table will have "hundreds of millions of rows", s
o
> I am worried about JOIN performance. Once a message is archived (then
> deleted), from it's original location and placed in SQL server, the
> MessageID has no value and is never displayed to the user. There are more
> complications because this table is replicated using (Merge replication).
> The database is a distributed database, so using auto identity columns
> complicate replication issues, not too mention irritating --Celko--. So I
> really want to find a suitable surrogate key.
> So what to do?
> 1. Use RSA to reduce MessageID to 20 bytes, and deal with the collisions?
> 2. Use a rowGuid (Outch!)
> At this point I like option #1 the best because it works nicely with
> "replication" as the original MessageID is a Globally Unique Identifier, s
o
> no two MessageID's (PR_ENTRY_ID long term) will ever exist. But I think
> there is are issues with generating these shorter keys using RSA, collisio
ns
> will happen. Additionally it is quite simple to encode the MessageID using
> RSA. The MessageID would now be 20 bytes or [varbinary](20).
> Does anyone have any good suggestions?
> Thanks for your time.
> Russell Mangel
> Las Vegas, NV
>
> -- Note: I commented out the Constraint, as varbinary(MAX) is not allowed
.
> -- This schema is just what the data looks like in the real world.
> CREATE TABLE
> [dbo].[Messages]
> (
> [MessageID] [varbinary](MAX) NOT NULL,
> [ParentID] [varbinary](MAX) NOT NULL, -- FK for Folders Table
> [SenderID] [int] NOT NULL, -- FK for Sender of message
> [MessageType] [int] NOT NULL, -- FK MessageType
> [Length] [bigint] NOT NULL,
> [To] [nvarchar](MAX) NOT NULL,
> [CC] [nvarchar](MAX) NOT NULL,
> [Subject] [nvarchar](MAX) NOT NULL,
> [Body] [ntext] NOT NULL,
> [HasAttachment] [bit] NOT NULL,
> -- CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
> -- (
> -- [MessageID]
> -- )
> )ON [PRIMARY]
> -- End Schema
You can add a constraint on MessageID like this:
CREATE FUNCTION dbo.ufn_sha_hash
(@.msg VARBINARY(8000))
RETURNS VARBINARY(20)
AS
BEGIN
RETURN
ISNULL(HASHBYTES('SHA1',@.msg),0x7C7CDB42
7449446E8EA812C5981C5D6A) ;
END
GO
CREATE TABLE dbo.Messages
(
MessageID varbinary(8000) NOT NULL,
HashID varbinary(20) NOT NULL PRIMARY KEY,
CHECK (HashID = dbo.ufn_sha_hash(MessageID)),
);
GO
Whether you should also use a surrogate key is a different question. It
would probably make sense to do so. Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> CREATE FUNCTION dbo.ufn_sha_hash
> (@.msg VARBINARY(8000))
> RETURNS VARBINARY(20)
> AS
> BEGIN
> RETURN
> ISNULL(HASHBYTES('SHA1',@.msg),0x7C7CDB42
7449446E8EA812C5981C5D6A) ;
> END
> GO
> CREATE TABLE dbo.Messages
> (
> MessageID varbinary(8000) NOT NULL,
> HashID varbinary(20) NOT NULL PRIMARY KEY,
> CHECK (HashID = dbo.ufn_sha_hash(MessageID)),
> );
> GO
> Whether you should also use a surrogate key is a different question. It
> would probably make sense to do so. Hope this helps.
> --
> David Portas, SQL Server MVP
I didn't know that SQL could do SHA1 hashes, thanks. This table is a
replicated table so once you add surrogate keys, you have to deal with that.
Personally I would rather have a binary(20) column which replaces MessageID
column completely than deal with the surrogate key problems with Replicated
(Merge) databases.
The Messages table also has a FK (FolderID) for Folders table, so this
column could also be removed and replaced by a binary(20) column. Folders
Table PK (FolderID)would be replaced with SHA1 binary(20) as well.
Which brings a couple questions:
1. If we are going to store an SHA1 hash, wouldn't it be best to use:
binary(20) instead of varbinary(20)?
2. Will I have any collisions (PK violations) with SHA1 hash (more on this
in next sentence)?
The MessageID is a variable length Binary byte array, and is a Globally
UniqueIdentifier (it's just an older form of GUID before GUID was born) this
MessageID is from MAPI (Exchange Server). So if you had 50 million
varbinary(MAX) MessageIDs, and you ran SHA1 hash against all of them would
you find duplicated SHA1 hash values?
Thanks
Russell Mangel|||Russell Mangel wrote:
> I didn't know that SQL could do SHA1 hashes, thanks. This table is a
> replicated table so once you add surrogate keys, you have to deal with tha
t.
> Personally I would rather have a binary(20) column which replaces Message
ID
> column completely than deal with the surrogate key problems with Replicate
d
> (Merge) databases.
> The Messages table also has a FK (FolderID) for Folders table, so this
> column could also be removed and replaced by a binary(20) column. Folders
> Table PK (FolderID)would be replaced with SHA1 binary(20) as well.
> Which brings a couple questions:
> 1. If we are going to store an SHA1 hash, wouldn't it be best to use:
> binary(20) instead of varbinary(20)?
> 2. Will I have any collisions (PK violations) with SHA1 hash (more on this
> in next sentence)?
> The MessageID is a variable length Binary byte array, and is a Globally
> UniqueIdentifier (it's just an older form of GUID before GUID was born) th
is
> MessageID is from MAPI (Exchange Server). So if you had 50 million
> varbinary(MAX) MessageIDs, and you ran SHA1 hash against all of them would
> you find duplicated SHA1 hash values?
> Thanks
> Russell Mangel
1. True. Although VARBINARY is functionaly equivalent because the
return value is always 20 bytes.
2. Not unless the MessageID exceeds 8000 bytes, which is the maximum
supported length for HashBytes. Although there is a theoretical risk of
a hash collision, you need 2^80 hashed messages on average before you
hit a duplicate. You are hashing GUIDs though. I don't know the GUIDs
used by Exchange but if they have similar qualities to the ones
generated by Windows then theoretically you are much, much more likely
to hit a duplicate GUID than a duplicate hash.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--