better method for DTS: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

is TOP 1 in JOIN possible

Doing a query with two Tables normaly is done by A.IDA=B.IDA
But also A.IDA>B.IDA is possible - but can give more than one join.

I have the following query:

SELECT * FROM TblA
LEFT JOIN TblB ON TblB.Begin > TblA.End

Now I want to get ONLY ONE joined record.

Is there an syntax like:
LEFT JOIN TOP 1 TblB ON TblB.Begin > TblA.End ?SQL Server 2000:

Select top N *
from table
order by column

Oracle 9i:

Select *
from
(select columns from table ORDER BY column)
where rownum = 1;|||Originally posted by r123456
SQL Server 2000:

Select top N *
from table
order by column

Oracle 9i:

Select *
from
(select columns from table ORDER BY column)
where rownum = 1;

Thank you. - But just selecting the top one of a table is not my problem.
I need the top 1 in the JOIN statement, because I want to join one table with onother by joining from the second table only the ONE next elder record.|||Select *
from
tableB tb
LEFT OUTER JOIN
(select top N * from tableA where condition) v1 on
v1.id = tb.id;

This query will join all records of tableB with the first record of the set V1.|||Originally posted by r123456
...
(select top N * from tableA where condition) v1 on
v1.id = tb.id;

This query will join all records of tableB with the first record of the set V1.

Sorry - but this doesn't help either
because if top 1 selects a record with another v1.id than tb.id I get no joined records although there IS one (but not on top of the list v1)

Or did I get something wrong...|||select *
from TblA
left outer
join TblB
on TblB.Begin > TblA.End
and TblB.Begin
= ( select max(Begin)
from TblB
where Begin > TblA.End )|||Originally posted by r937
select *
from TblA
left outer
join TblB
on TblB.Begin > TblA.End
and TblB.Begin
= ( select max(Begin)
from TblB
where Begin > TblA.End )

THAT WORKS !!!!!!

Thanks a lot !!!!!!!

Wednesday, March 28, 2012

Is this table design correct?

Hey Everyone,

Just had a quick question for you . I have these 3 tables that are related in some way - below are the structures. I know the structures are correct and they work fine. However, I am using Visio Enterprise Architect to design these tables and when I try to generate the code, I get an error saying that it does not like the table with 2 fields as the primary key. And I just wanted to get feedback to find out whether I am correct or Visio is.

Table: Orders
Columns:
OrderID int PK
Name varchar(100)
Phone varchar(20)

Table: Products
Columns:
ProductID int PK
Name varchar(100)

Table: OrderLineItems
Columns:
OrderID int PK
ProductID int PK
Price money

Thanks for any feedback.

Johnny DevA composite primary key is perfectly legitimate and is often useful if you have a link table, like in your case. The only thing that puzzles me is the Price column in the OrderLineItems table - shouldn't it be in the Products table?|||Hi Diplo,

Thanks for the response. This clears things up. Visio needs to be updated to support this.

The table structure that I posted in this message is not what I have. It was just a sample to demonstrate my case so I wont post my real tables (too complicated :). In fact, my tables are not even Order/Products related. But thanks anyway for catching that for others to see.sql

Is this query possible?

If so can someone help me out?

Ihave 2 tables. Both tables have pk's of (patId, visitDate). fk is patId. Sometime the 2 dates will match other timesthey don't so they aren't.

Table1example

patId visitDate1 labValue

1 1/1/06 5

1 1/5/06 <NULL>

1 2/1/06 <NULL>

2 2/2/06 6

2 3/12/06 3

3 1/24/06 2

3 3/1/06 <NULL>

Table2example

patId visitDate2 Col1 Col2

1 1/1/06 3 7

1 1/23/06 <NULL> 12

2 2/2/06 2 <NULL>

3 1/16/06 5 3

NoticeTable 1 has more/different patId's/visitDates

Nowwhat I want the query to output is everything from Table2 and "append" labValuefrom Table1. I want all records fromboth tables where the patId's match

Desiredoutput:

patId Date(from both tables) labValue Col1 Col2

1 1/1/06 5 3 7

1 1/5/06 <NULL> <NULL> <NULL>

1 1/23/06 <NULL> <NULL> 12

1 2/1/06 <NULL> <NULL> <NULL>

2 2/2/06 6 2 <NULL>

2 3/12/06 3 <NULL> <NULL>

3 1/16/06 <NULL> 5 3

3 1/24/06 2 <NULL> <NULL>

3 3/1/06 <NULL> <NULL> <NULL>

SELECT d1.PatID,d1.Date,t1.labValue,t2.Col1,t2.Col2
FROM (SELECT PatID,Date FROM Table1 UNION SELECT PatID,Date FROM Table2) d1
LEFT JOIN table1 t1 ON (d1.Patid=t1.patid AND d1.Date=t1.Date)
LEFT JOIN table2 t2 ON (d1.Patid=t2.patid AND d1.Date=t2.Date)|||

Thanks for your assistance with this.
It's almost working correctly, but not quite.
It is pulling all records from Table1 (which I want), but itis not pulling any records from Table2. It IS pulling data from Table2 when it matches a date in Table1, butthis is not always the case.

Table2 can have records with dates that do not match a datein Table1.

See my example in my first post of how records from bothtables are appended.

|||

The query I gave will return the exact results you have in your first post.

|||

USE [test]
GO
/****** Object: Table [dbo].[Table1] Script Date: 03/13/2006 12:13:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[patId] [int] NULL,
[visitDate] [datetime] NULL,
[labValue] [int] NULL
) ON [PRIMARY]
USE [test]
GO
/****** Object: Table [dbo].[Table2] Script Date: 03/13/2006 12:13:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table2](
[patID] [int] NULL,
[visitDate] [datetime] NULL,
[Col1] [int] NULL,
[Col2] [int] NULL
) ON [PRIMARY]
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (1,'1/1/2006',5)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (1,'1/5/2006',NULL)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (1,'2/1/2006',NULL)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (2,'2/2/2006',6)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (2,'3/12/2006',3)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (3,'1/24/2006',2)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (3,'3/1/2006',NULL)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (1,'1/1/2006',3,7)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (1,'1/23/2006',NULL,12)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (2,'2/2/2006',2,NULL)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (3,'1/16/2006',5,3)

SELECT d1.PatID,d1.visitDate,t1.labValue,t2.Col1,t2.Col2

FROM(SELECT PatID,visitDateFROM Table1UNIONSELECT PatID,visitDateFROM Table2) d1

LEFTJOIN table1 t1ON(d1.Patid=t1.patidAND d1.visitDate=t1.visitDate)

LEFTJOIN table2 t2ON(d1.Patid=t2.patidAND d1.visitDate=t2.visitDate)

Gives the results:

1 2006-01-01 00:00:00.000 5 3 7
1 2006-01-05 00:00:00.000 NULL NULL NULL
1 2006-01-23 00:00:00.000 NULL NULL 12
1 2006-02-01 00:00:00.000 NULL NULL NULL
2 2006-02-02 00:00:00.000 6 2 NULL
2 2006-03-12 00:00:00.000 3 NULL NULL
3 2006-01-16 00:00:00.000 NULL 5 3
3 2006-01-24 00:00:00.000 2 NULL NULL
3 2006-03-01 00:00:00.000 NULL NULL NULL

|||I think the last one will work
Thanks for your help|||

Ok, we're almost there. Going off of your last query I have this working. Here's my actual query:

SELECT d1.Date, t2.BxMarsh, t2.Diet, t2.Symptoms, t2.Bx,t2.BxLocation, t2.GI_MD,

t2.DateDietStarted,t2.DietNotes,t2.Comments

FROM (SELECT patnum,Date FROM labs UNION SELECTpatnum,labDate FROM labceliac) d1

LEFT JOIN labceliac t2 ON (d1.patnum=t2.patnum ANDd1.Date=t2.labDate)

where (d1.patnum = 3625)

Now, the last thing I need to do is add a column from Labsthat IS NOT in LabCeliac. Here's what Iadded:

SELECT d1.Date,d1.Transglut, t2.BxMarsh, t2.Diet,t2.Symptoms, t2.Bx, t2.BxLocation, t2.GI_MD,

t2.DateDietStarted,t2.DietNotes,t2.Comments

FROM (SELECT patnum,Date, Transglut FROM labs UNION SELECTpatnum,labDate FROM labceliac) d1

LEFT JOIN labceliac t2 ON (d1.patnum=t2.patnum ANDd1.Date=t2.labDate)

where (d1.patnum = 3625)

I added the Transglut field to both the first line and

Now I'm getting this error:

Server: Msg 8157, Level 16, State 1, Line 1

All the queries in a query expression containing a UNIONoperator must have the same number of expressions in their select lists.

I know that this is asking for the same field after theunion statement, but Transglut only exists in Labs, and not LabCeliac.

|||

SELECT d1.Date,labs.Transglut, t2.BxMarsh, t2.Diet, t2.Symptoms, t2.Bx, t2.BxLocation, t2.GI_MD,

t2.DateDietStarted, t2.DietNotes,t2.Comments

FROM (SELECT patnum,Date FROM labs UNION SELECT patnum,labDate FROM labceliac) d1

LEFT JOIN labceliac t2 ON (d1.patnum=t2.patnum AND d1.Date=t2.labDate)

LEFT JOIN labs ON (d1.patnum=labs.patnum AND d1.Date=labs.Date)

where (d1.patnum = 3625)

The trick is that our derived table d1 is a list of rows which represent every patient and every date for each of those patients that exist in either labs or labceliac. With that in hand, then we can and join that back to the labs and labceliac tables to get columns from those tables that are in one, but not the other by doing left (outer) joins.

Is this query possible?

Dear *,
I need help with a query!
I've set up a Database for managing employees and their contracts in
our institution.
I have two tables (stripped-down):
tbl_persons:
PersonID, smallint(2) Primary Key
Name, varchar(50)
tbl_contracts:
ContractID, smallint(2) Primary Key
Person_ID, smallint(2)
Begin, datetime(8)
End, datetime(8)
Now, for a number of reasons, it is possible, that Persons can have
consecutive entries in this database (changing of status
in the organisation, a number of fixed-term contracts etc.).
On our Intranet-Webpages, I would like present a list with people
leaving our institution. For this purpose i Wrote the following query:
CREATE VIEW dbo.qry_people_leave
AS
SELECT TOP 100 PERCENT dbo.tbl_contracts.Begin, dbo.tbl_contracts.End,
dbo.tbl_persons.Name
FROM dbo.tbl_persons INNER JOIN
dbo.tbl_contracts ON dbo.tbl_persons.PersonID =3D
dbo.tbl_contracts.PersonID
WHERE (dbo.tbl_contracts.End BETWEEN { fn NOW() } - 7 AND { fn NOW() }
+ 92) AND (dbo.tbl_contracts.Begin <=3D { fn NOW() })
ORDER BY dbo.tbl_contracts.End
But then people will appear on the list, whose contracts end during the
next three months, but who've got another contract subsequent to the on
shown in the list. This is irritating.
It's the same for a similar query to list "new" employees. People
appear as new employees, that have worked for years in our institute,
just because they've got a new contract.
Is it possible to have a query that display persons, whose contract
ends in the next three months, but only if there are
no later contracts for this person entered in the Database?
Any help/hint would be greatly appreciated!
Thanks in advance,
Manuel Sch=FCrenThere are some drawbacks to the approach I will describe (one of which is yo
u
do not account for gaps in contract periods), but maybe this might help.
Use a derived table t oget to the contract with the greatest expiration date
and join to your contract table to get all of the other data columns. For
instance:
SELECT c.Begin, c.End, p.Name
FROM dbo.tbl_persons p
INNER JOIN dbo.tbl_contracts c ON p.PersonID = c.PersonID
INNER JOIN (SELECT PersonID, MAX(End) AS End FROM tbl_contracts GROUP BY
Person_ID) m
ON c.PersonID = m.PersonID AND c.End = m.End
WHERE (dbo.tbl_contracts.End BETWEEN { fn NOW() } - 7 AND { fn NOW() }
+ 92) AND (dbo.tbl_contracts.Begin <= { fn NOW() })
ORDER BY dbo.tbl_contracts.End
So your derived table includes the MAX End Date for each PersonID. Now when
you link to it you will get the contract record for that person with that en
d
date. Note that if you have more than one contract with the same end date
for a given person, you will get back multiple rows.
HTH,
John Scragg
"manuel.schueren@.web.de" wrote:

> Dear *,
> I need help with a query!
> I've set up a Database for managing employees and their contracts in
> our institution.
> I have two tables (stripped-down):
> tbl_persons:
> PersonID, smallint(2) Primary Key
> Name, varchar(50)
> tbl_contracts:
> ContractID, smallint(2) Primary Key
> Person_ID, smallint(2)
> Begin, datetime(8)
> End, datetime(8)
>
> Now, for a number of reasons, it is possible, that Persons can have
> consecutive entries in this database (changing of status
> in the organisation, a number of fixed-term contracts etc.).
> On our Intranet-Webpages, I would like present a list with people
> leaving our institution. For this purpose i Wrote the following query:
> CREATE VIEW dbo.qry_people_leave
> AS
> SELECT TOP 100 PERCENT dbo.tbl_contracts.Begin, dbo.tbl_contracts.End,
> dbo.tbl_persons.Name
> FROM dbo.tbl_persons INNER JOIN
> dbo.tbl_contracts ON dbo.tbl_persons.PersonID =
> dbo.tbl_contracts.PersonID
> WHERE (dbo.tbl_contracts.End BETWEEN { fn NOW() } - 7 AND { fn NOW() }
> + 92) AND (dbo.tbl_contracts.Begin <= { fn NOW() })
> ORDER BY dbo.tbl_contracts.End
> But then people will appear on the list, whose contracts end during the
> next three months, but who've got another contract subsequent to the on
> shown in the list. This is irritating.
> It's the same for a similar query to list "new" employees. People
> appear as new employees, that have worked for years in our institute,
> just because they've got a new contract.
> Is it possible to have a query that display persons, whose contract
> ends in the next three months, but only if there are
> no later contracts for this person entered in the Database?
> Any help/hint would be greatly appreciated!
> Thanks in advance,
> Manuel Schüren
>|||Dear John,
thank you very much, this works like a charm!
Not accounting for gaps in contract periods is not a main problem, but
what about the other drawbacks for this solution, you've mentioned in
the beginning of your article?
Are there any serious ones?
Nevertheless, this helped a lot, great approach.
Thanks again.
Best regards,
Manuel

Is this query correct

I have two tables cd_customer and cd_customer_parent_hierarchy, I want to select all those customers from the parent table that are in the child table where the seed_flag for the child key is yes but the seed_flag for the parent is no

SELECT ccph.parent_customer_key
FROM cd_customer cc,
cd_customer_parent_hierarchy ccph,
cd_customer cc_p
WHERE
ccph.customer_key = cc.customer_key AND
ccph.parent_customer_key = cc_p.customer_key AND
CCPH.HIERARCHY_TYPE = '-' AND
CC.SEED_FLAG = 'Y' AND
CC_P.SEED_FLAG = 'N'

also what would be the difference in queries if I were to add a group by ccph.parent_customer_key

Thanksquery looks okay, given the fact that we can't see your table layouts nor sample data

if you add that GROUP BY, two things happen -- the query slows down, and only unique values will be returned

Monday, March 26, 2012

Is this possible without using cursors?

Hi!

I have 2 tables: Person and Address

Person
(
PersonID int PK
)

Adress
(
AddresID int PK,
PersonID int FK,
Default -- 1 if address is default for person
)

so when I join those table it yelds (for example):

p1 a1 1
p1 a2 0
p1 a3 0
p2 a4 1
p3 a5 0
p3 a6 0

Person may:
- have one default addres and some non-default;
- haven't default address;
- have only default adres

So proper result is:

p1 a1 p1 a1
p2 a4 OR p2 a4
p3 a5 p3 a6

I want to get list of persons and their adresses in following manner:

Get person and
- default adress ID if exists for this person
- any address ID else

Is this possible without using cursors?

Regards,
Walter

Walter:

This can be done with an outer join. The main thing to verify is what you want to do when you have NULL returned for OUTER TABLE results:

select p.personID,
a.addressID,
a.[default]
from Person p
left join address a
on p.personID = a.personID
and a.[default] = 1
-- Sample Output:
-- personID addressID default
-- -- -- -
-- 1 1 1
-- 2 4 1
-- 3 NULL NULL
Dave

|||

Hmmm...

I that statement, you have persons with defaults addresses, or info that there's no default address. But there is no addressID when it is non-default. We don't want NULLs, but any non-default addressID...

Walte

|||

Sorry about that. Is this closer to what you have in mind?

select personID,
addressID
from ( select p.personID,
a.addressID,
a.[default],
row_number () over
( partition by p.personID
order by a.[default] desc, a.addressID
) as seq
from Person p
inner join address a
on p.personID = a.personID
) a
where seq = 1
-- Sample Output:
-- personID addressID
-- -- --
-- 1 1
-- 2 4
-- 3 5

|||

Please mention the version of SQL Server so it is easy to suggest the correct solution that will work in that version or all versions. You can do below in SQL Server 2005:

select ...

from Person as p

cross apply (

select top 1 *

from Address as a

where a.PersonID = p.PersonID

order by a.Default desc

) as pa

And add an index on (Address.PersonID, Address.Default DESC) to get the best performance. If you need solution for older version of SQL Server then please reply back.

|||

I simulated 32767 rows of person data with about 75000 rows of address data with:

drop table dbo.Person
go
create table dbo.Person
(
PersonID int primary key
)
go
drop table dbo.address
go
create table dbo.Address
(
AddressID int primary key,
PersonID int,
[default] tinyint
)
go
insert into person
select iter
from small_iterator
insert into address
select personID,
personID,
0
from person
where personID % 11 > 1
insert into address
select 32768 + personID,
personID,
0
from person
where personID % 11 < 2
insert into address
select 2*32768 + personID,
personID,
0
from person
where personID %17 not in (3, 5, 13)
insert into address
select 3*32768 + personID,
personID,
0
from person
where personID %23 not in (7, 10, 13, 19)

First, I ran this query with these results:

declare @.begDt datetime
set @.begDt = getdate()
select personID,
addressID
from ( select p.personID,
a.addressID,
a.[default],
row_number () over
( partition by p.personID
order by a.[default] desc, a.addressID
) as seq
from Person p
inner join address a
on p.personID = a.personID
) a
where seq = 1
print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]
-- Sample Output:
-- personID addressID
-- -- --
-- 1 32769
-- 2 2
-- ...
-- 32766 32766
-- 32767 32767

-- |--Filter(WHERE:([Expr1004]=(1)))
-- |--Sequence Project(DEFINE:([Expr1004]=row_number))
-- |--Compute Scalar(DEFINE:([Expr1006]=(1)))
-- |--Segment
-- |--Sort(ORDER BY:([p].[PersonID] ASC, .[default] DESC, .[AddressID] ASC))
-- |--Hash Match(Inner Join, HASH:([p].[PersonID])=(.[PersonID]), RESIDUAL:([Mugambo].[dbo].[Address].[PersonID] as .[PersonID]=[Mugambo].[dbo].[Person].[PersonID] as [p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Person].[PK__Person__7D63964E] AS [p]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Address].[PK__Address__7F4BDEC0] AS ))
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Address'. Scan count 1, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Elapsed Time
--
-- 656

And then ran this query with these results:

declare @.begDt datetime
select @.begDt = getdate()
select p.personID,
pa.addressID
from person p
cross apply
( select top 1 *
from address as a
where a.personID = p.personID
order by a.[default] desc
) pa
print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]
-- |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Person].[PK__Person__7D63964E] AS [p]))
-- |--Sort(TOP 1, ORDER BY:(.[default] DESC))
-- |--Index Spool(SEEK:(.[PersonID]=[Mugambo].[dbo].[Person].[PersonID] as [p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Address].[PK__Address__7F4BDEC0] AS ))
-- Table 'Worktable'. Scan count 32767, logical reads 242508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Address'. Scan count 1, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2563

Dave

|||Please add an index on (Address.PersonID, Address.Default DESC) and you will get the best performance with the APPLY approach. Also, the results from both queries will not be identical because of the additional ordering on AddressID in your query.|||

Hi,

thanks for yours posts :) Unfortunately I use SQL SERVER 2000....

Regards,
Walter

|||

The following query might help you

Select Person.PersonID,Isnull(Adress.AddresID,Adress2.AddresID) from Person
left Outer Join Adress on Person.PersonID = Adress.PersonID And Adress.isDefault = 1
left Outer Join Adress Adress2 On Person.PersonID = Adress2.PersonID And Adress2.isDefault = 0
and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

|||

Select Person.PersonID,Isnull(Adress.AddresID,Adress2.AddresID) from Person
left Outer Join Adress on Person.PersonID = Adress.PersonID And Adress.isDefault = 1
left Outer Join Adress Adress2 On Person.PersonID = Adress2.PersonID And Adress2.isDefault = 0
and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

This query has me confused; I am not sure that I am getting the correct return data for my large test case.

|||

Here if there is a default value then it will pull that value..

If there is no default value instead of fetching Min/Max address id it will pull the random address from the DB.

May be your test case fail if you try to get Max/Min address id as expected.

Change your test case as "IN Non Default Address List instead of Particular Address"

|||Well, I am getting nulls for some address IDs and some rows that have duplicate personIDs. And I agree with you in that this might be a data problem. (I gotta get something to eat.)|||

Mani:

Ran the following three queries and got the results that follow:

select count(*) as [Person Records] from person
select count(distinct personID) as [Address Records] from address
select count(distinct a.personID) [Matched Person Records] from person a inner join address b on a.personID = b.personID

-- Person Records
-- --
-- 32767

-- Address Records
--
-- 32767

-- Matched Person Records
-- -
-- 32767

Therefore, I do not think that the problem is a data problem. I re-examined your select and I question this line:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

specifically, the "... Where Address2.PersonID ..." portion. I think this is the source of the NULL and duplicate PersonID records. When I change this line to:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

I get what I perceive to be "correct" results. Please verify whether or not you aggree.

Also, I completely agree with Umachandar's assessment that we need an additional index. I am therefore going to add a cover index based on (1) personID, (2) default and (3) addressID.

Running one more test.
Dave

|||

Mani:

Ran the following three queries and got the results that follow:

-- Person Records
-- --
-- 32767

-- Address Records
--
-- 32767

-- Matched Person Records
-- -
-- 32767

Therefore, I do not think that the problem is a data problem. I re-examined your select and I question this line:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

specifically, the "... Where Address2.PersonID ..." portion. I think this is the source of the NULL and duplicate PersonID records. When I change this line to:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

I get what I perceive to be "correct" results. Please verify whether or not you aggree.

Also, I completely agree with Umachandar's assessment that we need an additional index. I am therefore going to add a cover index based on (1) personID, (2) default and (3) addressID.

Running one more test.
Dave

|||

I ran the modified version of Mani's query against my mock tables and got the following results:

-- |--Compute Scalar(DEFINE:([Expr1010]=isnull([Address].[AddressID], [Address].[AddressID])))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Person].[PersonID]))
-- |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Address].[PersonID])=([Person].[PersonID]), RESIDUAL:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Sort(ORDER BY:([Address].[PersonID] ASC))
-- | | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]), WHERE:([Address].[default]=1))
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B]), ORDERED FORWARD)
-- |--Hash Match(Cache, HASH:([Person].[PersonID]), RESIDUAL:([Person].[PersonID]=[Person].[PersonID]))
-- |--Nested Loops(Inner Join, OUTER REFERENCES:([Address].[AddressID]))
-- |--Sort(TOP 1, ORDER BY:([Expr1009] ASC))
-- | |--Compute Scalar(DEFINE:([Address].[AddressID]=[Address].[AddressID], [Expr1009]=newid()))
-- | |--Index Spool(SEEK:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]))
-- |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]), SEEK:([Address].[AddressID]=[Address].[AddressID]), WHERE:([Address].[PersonID]=[Person].[PersonID] AND [Address].[default]=0) ORDERED FORWARD)
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Table 'Address'. Scan count 3, logical reads 585, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 85514, logical reads 347474, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 2186

I next added this index:

create index address_personID_ndx
on address (personID, [default] desc, addressID)

And then I re-tested the modified version of Mani's query and received these improved results:

-- |--Compute Scalar(DEFINE:([Expr1010]=isnull([Address].[AddressID], [Address].[AddressID])))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Person].[PersonID]))
-- |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Address].[PersonID])=([Person].[PersonID]), RESIDUAL:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Index Scan(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), WHERE:([Address].[default]=1) ORDERED FORWARD)
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B]), ORDERED FORWARD)
-- |--Hash Match(Cache, HASH:([Person].[PersonID]), RESIDUAL:([Person].[PersonID]=[Person].[PersonID]))
-- |--Nested Loops(Inner Join, OUTER REFERENCES:([Address].[AddressID]))
-- |--Sort(TOP 1, ORDER BY:([Expr1009] ASC))
-- | |--Compute Scalar(DEFINE:([Address].[AddressID]=[Address].[AddressID], [Expr1009]=newid()))
-- | |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), SEEK:([Address].[PersonID]=[Person].[PersonID]) ORDERED FORWARD)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), SEEK:([Address].[PersonID]=[Person].[PersonID] AND [Address].[default]=0 AND [Address].[AddressID]=[Address].[AddressID]) ORDERED FORWARD)
-- Table 'Address'. Scan count 65535, logical reads 131671, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 1953

I then tested a different query and obtained the results that follow:

select p.personID,
( select top 1 addressID from address b
where p.personID = b.personID
order by [default] desc
) as addressID
from Person p
-- |--Compute Scalar(DEFINE:(.[AddressID]=.[AddressID]))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B] AS [p]))
-- |--Compute Scalar(DEFINE:(.[AddressID]=.[AddressID]))
-- |--Top(1)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx] AS ), SEEK:(.[PersonID]=[p].[PersonID]) ORDERED FORWARD)
-- Table 'Address'. Scan count 32767, logical reads 65593, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 1890

There is not a lot of difference with respect to execution time between this query and the modified version of Mani's query. This version has one less join and therefore half the scans and half the logical reads. Also, if you neglect to add the index this query is liable to run slow because of the NESTED LOOP join and associated TABLE SCANS instead of INDEX SEEKS.

Either way, BE SURE TO ADD THE INDEX RECOMMENDED BY Umachandar!

Dave

sql

Is this possible with Full Text Indexing?

(SQL Server 2000, SP4)
Hello all!
I am wrestling with a problem that I hope someone can help me with. I have
a series of related tables (fairly normalized), each of which has a textual
column whose data will, for the most part, be unique from the other tables.
We have created a Full Text Catalog over these tables that will index these
text columns.
We are trying to write an interface to search over the entire "suite" of
tables, using an expression like "A and B" or "A or B". The problem arises
when we try and craft the appropriate query using the Full Text CONTAINS
predicate.
For example, lets say that we have Shape, Color, and Size tables. If
someone tries to search for "Circle or Red", I can pretty easily find the
appropriate records from each of the tables. However, if I try and search
for "Circle and Red", I'm having a more difficult time expressing the query
to restrict the results to only those records that match.
It's almost like I need a Full Text Catalog over the *entire* set of tables.
Working with the individual tables is giving me fits.
Any help/advice would be *much* appreciated. Thanks!
John Peterson
In SQL 2005 you can create an indexed view over all tables and the full text
index the view. In SQL 2000 I think you best bet would be to create a table
which would contain the rows from all tables, full-text index it, and then
search it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2000, SP4)
> Hello all!
> I am wrestling with a problem that I hope someone can help me with. I
> have a series of related tables (fairly normalized), each of which has a
> textual column whose data will, for the most part, be unique from the
> other tables. We have created a Full Text Catalog over these tables that
> will index these text columns.
> We are trying to write an interface to search over the entire "suite" of
> tables, using an expression like "A and B" or "A or B". The problem
> arises when we try and craft the appropriate query using the Full Text
> CONTAINS predicate.
> For example, lets say that we have Shape, Color, and Size tables. If
> someone tries to search for "Circle or Red", I can pretty easily find the
> appropriate records from each of the tables. However, if I try and search
> for "Circle and Red", I'm having a more difficult time expressing the
> query to restrict the results to only those records that match.
> It's almost like I need a Full Text Catalog over the *entire* set of
> tables. Working with the individual tables is giving me fits.
> Any help/advice would be *much* appreciated. Thanks!
> John Peterson
>
|||Dear Hilary,
Thank you for the information! I didn't realize that SQL 2005 had that
capability (to full-text an Indexed VIEW) -- that's really neat! I'll have
to look into that a bit more...
Yeah, if we stick with SQL 2000, our thought was to build a side table as
you suggest.
Thanks again!
John Peterson
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uhkSJm68GHA.3264@.TK2MSFTNGP04.phx.gbl...
> In SQL 2005 you can create an indexed view over all tables and the full
> text index the view. In SQL 2000 I think you best bet would be to create a
> table which would contain the rows from all tables, full-text index it,
> and then search it.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
>
sql

Is this possible with Full Text Indexing?

(SQL Server 2000, SP4)
Hello all!
I am wrestling with a problem that I hope someone can help me with. I have
a series of related tables (fairly normalized), each of which has a textual
column whose data will, for the most part, be unique from the other tables.
We have created a Full Text Catalog over these tables that will index these
text columns.
We are trying to write an interface to search over the entire "suite" of
tables, using an expression like "A and B" or "A or B". The problem arises
when we try and craft the appropriate query using the Full Text CONTAINS
predicate.
For example, lets say that we have Shape, Color, and Size tables. If
someone tries to search for "Circle or Red", I can pretty easily find the
appropriate records from each of the tables. However, if I try and search
for "Circle and Red", I'm having a more difficult time expressing the query
to restrict the results to only those records that match.
It's almost like I need a Full Text Catalog over the *entire* set of tables.
Working with the individual tables is giving me fits.
Any help/advice would be *much* appreciated. Thanks!
John PetersonIn SQL 2005 you can create an indexed view over all tables and the full text
index the view. In SQL 2000 I think you best bet would be to create a table
which would contain the rows from all tables, full-text index it, and then
search it.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2000, SP4)
> Hello all!
> I am wrestling with a problem that I hope someone can help me with. I
> have a series of related tables (fairly normalized), each of which has a
> textual column whose data will, for the most part, be unique from the
> other tables. We have created a Full Text Catalog over these tables that
> will index these text columns.
> We are trying to write an interface to search over the entire "suite" of
> tables, using an expression like "A and B" or "A or B". The problem
> arises when we try and craft the appropriate query using the Full Text
> CONTAINS predicate.
> For example, lets say that we have Shape, Color, and Size tables. If
> someone tries to search for "Circle or Red", I can pretty easily find the
> appropriate records from each of the tables. However, if I try and search
> for "Circle and Red", I'm having a more difficult time expressing the
> query to restrict the results to only those records that match.
> It's almost like I need a Full Text Catalog over the *entire* set of
> tables. Working with the individual tables is giving me fits.
> Any help/advice would be *much* appreciated. Thanks!
> John Peterson
>|||Dear Hilary,
Thank you for the information! I didn't realize that SQL 2005 had that
capability (to full-text an Indexed VIEW) -- that's really neat! I'll have
to look into that a bit more...
Yeah, if we stick with SQL 2000, our thought was to build a side table as
you suggest.
Thanks again!
John Peterson
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uhkSJm68GHA.3264@.TK2MSFTNGP04.phx.gbl...
> In SQL 2005 you can create an indexed view over all tables and the full
> text index the view. In SQL 2000 I think you best bet would be to create a
> table which would contain the rows from all tables, full-text index it,
> and then search it.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
>> (SQL Server 2000, SP4)
>> Hello all!
>> I am wrestling with a problem that I hope someone can help me with. I
>> have a series of related tables (fairly normalized), each of which has a
>> textual column whose data will, for the most part, be unique from the
>> other tables. We have created a Full Text Catalog over these tables that
>> will index these text columns.
>> We are trying to write an interface to search over the entire "suite" of
>> tables, using an expression like "A and B" or "A or B". The problem
>> arises when we try and craft the appropriate query using the Full Text
>> CONTAINS predicate.
>> For example, lets say that we have Shape, Color, and Size tables. If
>> someone tries to search for "Circle or Red", I can pretty easily find the
>> appropriate records from each of the tables. However, if I try and
>> search for "Circle and Red", I'm having a more difficult time expressing
>> the query to restrict the results to only those records that match.
>> It's almost like I need a Full Text Catalog over the *entire* set of
>> tables. Working with the individual tables is giving me fits.
>> Any help/advice would be *much* appreciated. Thanks!
>> John Peterson
>>
>

Is this possible with Full Text Indexing?

(SQL Server 2000, SP4)
Hello all!
I am wrestling with a problem that I hope someone can help me with. I have
a series of related tables (fairly normalized), each of which has a textual
column whose data will, for the most part, be unique from the other tables.
We have created a Full Text Catalog over these tables that will index these
text columns.
We are trying to write an interface to search over the entire "suite" of
tables, using an expression like "A and B" or "A or B". The problem arises
when we try and craft the appropriate query using the Full Text CONTAINS
predicate.
For example, lets say that we have Shape, Color, and Size tables. If
someone tries to search for "Circle or Red", I can pretty easily find the
appropriate records from each of the tables. However, if I try and search
for "Circle and Red", I'm having a more difficult time expressing the query
to restrict the results to only those records that match.
It's almost like I need a Full Text Catalog over the *entire* set of tables.
Working with the individual tables is giving me fits.
Any help/advice would be *much* appreciated. Thanks!
John PetersonIn SQL 2005 you can create an indexed view over all tables and the full text
index the view. In SQL 2000 I think you best bet would be to create a table
which would contain the rows from all tables, full-text index it, and then
search it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2000, SP4)
> Hello all!
> I am wrestling with a problem that I hope someone can help me with. I
> have a series of related tables (fairly normalized), each of which has a
> textual column whose data will, for the most part, be unique from the
> other tables. We have created a Full Text Catalog over these tables that
> will index these text columns.
> We are trying to write an interface to search over the entire "suite" of
> tables, using an expression like "A and B" or "A or B". The problem
> arises when we try and craft the appropriate query using the Full Text
> CONTAINS predicate.
> For example, lets say that we have Shape, Color, and Size tables. If
> someone tries to search for "Circle or Red", I can pretty easily find the
> appropriate records from each of the tables. However, if I try and search
> for "Circle and Red", I'm having a more difficult time expressing the
> query to restrict the results to only those records that match.
> It's almost like I need a Full Text Catalog over the *entire* set of
> tables. Working with the individual tables is giving me fits.
> Any help/advice would be *much* appreciated. Thanks!
> John Peterson
>|||Dear Hilary,
Thank you for the information! I didn't realize that SQL 2005 had that
capability (to full-text an Indexed VIEW) -- that's really neat! I'll have
to look into that a bit more...
Yeah, if we stick with SQL 2000, our thought was to build a side table as
you suggest.
Thanks again!
John Peterson
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uhkSJm68GHA.3264@.TK2MSFTNGP04.phx.gbl...
> In SQL 2005 you can create an indexed view over all tables and the full
> text index the view. In SQL 2000 I think you best bet would be to create a
> table which would contain the rows from all tables, full-text index it,
> and then search it.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u26x%23Sy8GHA.4084@.TK2MSFTNGP05.phx.gbl...
>

Friday, March 23, 2012

IS THIS GOOD DESIGN?

I have a 3 tables
tblAgent, tblClient, tblVendor.
Since each entity requires different info to be stored, it makes sense to
have 3 different tables.
Now each agent, client or vendor can write many notes, so I created the
table like this to store all info
ContactID, ContactTypeID, NoteEnterDate, Note
where
ContactID will contain the id of either agent, client, or vendor (since id
will be the same data type for all 3 tables)
ContactTypeID 1 = Agent
ContactTypeID 2 = Client
ContactTypeID 3 = Vendor
So based on the ContactTypeID passed in to stored procedure as parameter, I
would know which table to join and contactID will be selective to filter
data. Of course there would be no referential integrity with ContactID
since it contains ID from 3 different tables. I am planning to have a
clustered index on ContactID, ContactTypeID (table will have no primary key)
Is this good design? Should I have separate note table for each Agent,
Client, and Vendor?
Can you think of any better design with regard to indexes?
Can you think of any better design to accomodate aforementioned scenario?
ThanksLooks good enough except that
contacttypeid,contactid,notedate (in that order can) be the primary key
clustered, if your notedate is not smalldatetime, of course.
--
"Justin" wrote:

> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary ke
y)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>
>|||If each row in this notes table will specifically apply to only one of the
other three tables, I would say create 3 seperate tables for notes. This
lets you define the referential integrity constraints and you will know that
all client notes are in one place, vendors in another, etc. Other than the
table structures being similar, is there any other reason why you would want
these all in one table?
"Justin" <jus820@.hotmail.com> wrote in message
news:O3WqCxebGHA.1208@.TK2MSFTNGP04.phx.gbl...
> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary
key)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>sql

Is this feasible?

Hi
We have an access desktop app with front-end/back-end situation where all
tables are in the back end and everything else, forms/queries, are in the
front end. We would like to make a web app to use the same access database
but are worried about access being able to handle web app users. Is it
feasible to move the access backend tables to the SQL Server and link the
sql server tables in the access front end? It will not help access desktop
app as all processing will still be done by access but the web app can
presumably benefit from tables being on the SQL Server? Then over time we
can also re-write the access desktop app to be native sql.
Thanks
RegardsHiya John,
The LAST thing you want to do is put Access as the backend of anything with
more than one or two users. Put your data in SQL Server.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||Put all your data in SQL Server (MSDE) and use stored procedures and views
for extracting data and use Access strictly for the front end of the
application.
Jim
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||"William Morris" wrote
> The LAST thing you want to do
> is put Access as the backend of
> anything with more than one or two
> users. Put your data in SQL Server.
Where did you ever get that idea?
There are many factors that enter into multiuser access to Jet databasese,
including the requirements, design, and implementation of the application,
and the hardware, software, and network environments. If all those factors
are near-perfect, we have reliable reports of Access supporting 100+ happy,
concurrent users. Even if all are not near-perfect, we routinely see reports
of split Access-Jet databases supporting 30 to 70 users. We've had whines in
the past about "Access falling over with four users" and, any that we could
get details on turned out that all the factors were about as far from
perfect as possible, but the primary culprit was design by someone who
didn't know what they were doing.
Your user estimate is obviously nearly as low as it could possibly be,
unless some dunderhead claimed Access wouldn't support _any_ users.
And, by the way, "native SQL" does not have a desktop UI capability, just so
you won't make that mistake again.
Larry Linson
Microsoft Access MVP|||What you describe is certainly possible. Access makes a good client
application for server databases on the same LAN or WAN. If the person
needing the rich-client-interface is, however, accessing across the
Internet, you'll want to run Access on the server via some sort of Remote
Access Software (for one user, something like pcAnywhere or ReachOut would
be fine; for multiple users, take a look at Virtual Private Network and
Windows Terminal Server / Citrix).
However, Access itself would not be involved in supporting the web users in
such a situation. You have a Jet database, and you'd access it with either
DAO, or more likely, ADO code from .asp pages. If the database was on the
same machine, you can almost certainly support more concurrent users than
you could with an Access-Jet split database on a LAN. A Jet database can be
quite adequate for a web site with "modest" traffic. See my response to
William Morris for some numbers on concurrent users.
The advice you have received suggesting that you _need_ to convert to SQL
Server, without any indication of the requirements, or the expected number
of concurrent users, is "hasty", to say the least.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:%23VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>|||We encountered a similar scenario before. Yes, you can upsize (use the
upsizing utility) the MS-Access database tables to a SQL server and link
them back in the MS-Access database. You may want to check the following in
your front end. (1) AutoNumber (identity) (2) All the queries. There are
some flavors of MS-Access query may not work well with linked SQL server
tables. You may also have to tweak your front end to avoid any performance
issues. You may also want to consider the effort required to do the above
before making the final decision.
"John" <john@.nospam.infovis.co.uk> wrote in message
news:#VGkAIZyDHA.3888@.tk2msftngp13.phx.gbl...
> Hi
> We have an access desktop app with front-end/back-end situation where all
> tables are in the back end and everything else, forms/queries, are in the
> front end. We would like to make a web app to use the same access database
> but are worried about access being able to handle web app users. Is it
> feasible to move the access backend tables to the SQL Server and link the
> sql server tables in the access front end? It will not help access desktop
> app as all processing will still be done by access but the web app can
> presumably benefit from tables being on the SQL Server? Then over time we
> can also re-write the access desktop app to be native sql.
> Thanks
> Regards
>

Wednesday, March 21, 2012

Is this doable?

Hi,
Can you tell me if something like this is doable? Assume I have 2 tables as
follows:
Member(ID, Name, Gender)
Children(ID, FatherID, MotherID)
where ID, FatherID, MotherID are integers, Name is a character.
Assuming that I have the member's ID to start with, would it be possible to
display a row with the following:
ID, Name, [Father's Name], [Mother's Name] ?
Thank you
Please post onlySELECT M1.ID, M1.Name,
M2.Name as [Father's Name], M3.Name as [Mother's Name]
FROM Member M1
INNER JOIN Children C ON M1.ID=C.ID
INNER JOIN Member M2 ON C.FatherID=M2.ID
INNER JOIN Member M3 ON C.MotherID=M3.ID
Razvan|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:511725
No. I get the same name twice (the member's name is the same as the father)
Thank you
Please post only
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1110348053.094178.253040@.f14g2000cwb.googlegroups.com...
> SELECT M1.ID, M1.Name,
> M2.Name as [Father's Name], M3.Name as [Mother's Name]
> FROM Member M1
> INNER JOIN Children C ON M1.ID=C.ID
> INNER JOIN Member M2 ON C.FatherID=M2.ID
> INNER JOIN Member M3 ON C.MotherID=M3.ID
> Razvan
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:511727
Oops... sorry my mistake. Thank you very much.
Thank you
Please post only
"!!bogus" <hello@.microb.com> wrote in message
news:bxNXd.22362$fW4.668343@.news20.bellglobal.com...
> No. I get the same name twice (the member's name is the same as the
father)
>

Monday, March 19, 2012

Is this a "stored procedure" situation?

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:You'll want a few stored procedures for this probably. :) You don't want to bind the datagrid to the order-detail table. You'll need to populate it, then after they enter a part number, you will want to have an ON UPDATE action that:

1. Gets the part information and updates the ORDER-DETAIL table.
2. Refreshed the datagrid.|||[QUOTE=derrickleggett]You don't want to bind the datagrid to the order-detail table.QUOTE]

I think I understand what you mean by "bind" -- that the datagrid is like a *direct* window into the ORDER DETAIL table, right?

Okay, so I DON'T want to bind them. How can I tell if the datagrid that is currently in use is bound or not?

--William|||>> DerrickLeggett said:
>>You don't want to bind the datagrid to the order-detail table. You'll
>>need to populate it, then after they enter a part number, you will
>>want to have an ON UPDATE action that:
>> 1. Gets the part information and updates the ORDER-DETAIL table.
>> 2. Refreshed the datagrid.

The "datagrid" is a subform. Currently, I am using a View as the datasource for the subform, and the View is comprised of a join from the ORDER table and the PART table, and displays the PARTs that are already associated with the ORDER being viewed on the main form. There are two issues I'm trying to nail down:
1) To do what Derrick suggested (above), so that entering a PartNum value into a row of the subform causes the rest of the row to update with other data from the PART table (part description, etc.); and
2) To also allow a user to actually create a *new* entry in the PART table by entering a new PartNum into a row of the subform.

SO, I'd like the subform to recognize if a PartNum being entered into it is new or not. Is that possible? And, if so, how do I do that? PLEASE be explicit - this is all very new to me... :)

Is there transactional consistency across multiple publications?

Hi,

Currently, I have Server A which has Publication P1. Server B is subscribed to P1. Let's use 'T1' as the name for the set of tables/articles included in P1. Now I need to add serveral new tables to Server A. Let's call the new set of tables 'T2'. There is a Server C that needs to sync with the data in both T1 and T2. But Server B cannot have T2's data for privacy reasons.

One of the solutions I'm thinking about is to create another publication in Server A, called P2, which would publish the data in T2. Then have Server C subscribe to both P1 and P2. There would be no changes to Server B, who still subscribes to P1. My concern with this solution is: there are times where one transaction on Server A affects tables in both T1 and T2; since this type of transaction is split into 2 publications, will transactional consistency be maintained in the replication?

More specifically, suppose a statement (S1) in a transaction inserts a row in a T1 table, the next statement (S2) inserts a row in a T2 table using the result of S1. So S1 will be included in P1 and S2 in P2. Does SQL Server 2005 Replication guarantee that, by the time S2 is executed in Server C, S1 is already executed?

If anyone could explain what would happen in the above scenario, I'd really appreciated.

Thanks,
Dandan

Hi Dandan,

Transactional consistency for multiple subscriptions to the same subscriber database from multiple transactional publications of the same publisher database will be preserved if all publications involved are configured to share the same distribution agent (syspublications.independent_agent = 0). Otherwise, multiple distribution agents servicing the subscriptions can deliver parts of a transaction (spanning multiple tables) at different speed.

Hope that helps.

-Raymond

|||

Thank you very much for this information, Raymond.

Your rely mentioned "same subscriber database". In my scenario, different subscriber databases are subscribed to the same publisher. That is, Server B subscribes to P1, Server C subscribes to P1 and P2. P1 and P2 are publications from the same publisher database. In this case, will transactional consistency be preserved if the publications share the same distribution agent?

Thanks you very much,

Dandan

|||

I am not sure I understand the question correctly but different subscriber databases are serviced by different distribution agents at varying speeds so while transactional consistency is preserved at each subscriber database, there really is no guarantee that a replicated transaction will arrive at all subscribers at the same time.

-Raymond

|||

Sorry that I wasn't more clear with the question. But I think you have answered my question. Let me summarize: transactional consistency is preserved at each subscriber database, even if the subscriber database is subscribed to multiple publications, given that these publications are using the same distribution agent. In other words, if I have a stored procedure that performs many operations in one transaction, and this transaction affects multiple publications, then the order of execution for this transaction will stay the same when it is replicated to the subscriber database.

Did I get it right?

Thanks much,
Dandan

|||

I think I have probably over interpreted your situation and yes, the order is preserved in your scenario.

-Raymond

|||

Raymond,

I just need one more clarificaiton. For the subscriber that is subscribed to multiple publications, can the syncs from the 2 publications happen at different times? (We plan to have the subscriber sync with the publisher every hour) If so, could it break the intergrity between the tables in the 2 publications if the tables have dependencies on each other?

I would think the answer is no, because the above thread states "transactional consistency is preserved in the same subscriber syncing with multiple publications." I'd just like to confirm this with you.

Thanks much,
Dandan

|||

For a subscriber subscribing to multiple publications (from the same publisher database), transactional consistency across these multiple subscriptions\publications is preserved if and only if they are all serviced by the same distribution agent. That is, syspublications.independent_agent = 0 for all the publications involved. Note that this is *not* the default if you configure replication through SQL2005 Management Studio. You should be able to change this particular setting through the publication property page as long as you haven't created any subscriptions yet.

Hope that helps.

-Raymond

|||

Thank you very much for all of your help, Raymond. I will make sure to set publication's independent_agent property to false.

Dandan

Is there such a thing as too many relationship?

Hi,

I have a corporate database with about 60 different tables that spans
manufacturing, accounting, marketing, etc.

It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields like customer_id
or product_id.

But should I do that?

My question is: Is there such a thing as too many relationships? Can
I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected and simply use
JOINS in my business code?

Thanks,
HC>> I have a corporate database with about 60 different tables that
spans manufacturing, accounting, marketing, etc. <<

That is not that big for a corporate RDBMS ..

>> It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields [sic] customer_id
or product_id. But should I do that? <<

What do you mean by "establish a relationship"? Build a relationship
table among all the entities in the model?

>> My question is: Is there such a thing as too many relationships?
Can I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected [sic] and
simply use JOINS in my business code? <<

Conntected? You mean like in a network database with pointer chains?
You even talk about fields, not columns. Things in SQL are
referenced.

Yes, you need to get all of the business rules in your model. The
more you can enforce them with DRI actions and CHECK() constraints,
the better for you and the easier for the programmers that follow.
Otherwise, you data model is incomplete.|||jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0408131746.3edd63bc@.posting.google.com>...
> >> I have a corporate database with about 60 different tables that
> spans manufacturing, accounting, marketing, etc. <<
> That is not that big for a corporate RDBMS ..
> >> It is possible, but unwieldy, to establish a relationship for each
> table in the entire database through critical fields [sic] customer_id
> or product_id. But should I do that? <<
> What do you mean by "establish a relationship"? Build a relationship
> table among all the entities in the model?
> >> My question is: Is there such a thing as too many relationships?
> Can I establish referential integrity via relationships with critical
> tables like Accounting, but leave the rest unconnected [sic] and
> simply use JOINS in my business code? <<
> Conntected? You mean like in a network database with pointer chains?
> You even talk about fields, not columns. Things in SQL are
> referenced.
> Yes, you need to get all of the business rules in your model. The
> more you can enforce them with DRI actions and CHECK() constraints,
> the better for you and the easier for the programmers that follow.
> Otherwise, you data model is incomplete.

CELKO,

What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database?

Thanks.|||>> What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database? <<

Auxiliary tables, such as the calendar, would not be referenced by
another table.

Working tables used to scrub data before it goes into the schema would
not be referenced by another table. They might have few if any
constraints, so the raw data could be inspected.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||It is possible to have 'too many' relationships... though I doubt that
will be an issue for you. In other words, as everyone knows DRI exists
to help ensure data integrity. It also servers the purpose, as
intimated by Celko, of showing relations between entities...

However, each CHECK/FK creates a bit of overhead. That overhead can
end up being noticeable on VERY large tables (in the GBs and tens of
millions of rows). So there's a fine balance between optimizing
performance in some OLTP environments and keeping data clean/intact.
Good indexing can go a long way to keep all of this in check.

Case in point on the TOO MANY relationships (given all the stuff I
blabbed about above concerning costs) some over-zealous architects
will do something dumb like have an order items table. In that table
you'd normally have a FK for orderID... and for the itemID (for each
item)... but you probably wouldn't need a customerID in that table,
etc... )

Moral of the story. More is usually better. Just don't over do it.

--Mike

harris_cohen@.yahoo.com (H Cohen) wrote in message news:<1545331c.0408131453.477b3872@.posting.google.com>...
> Hi,
> I have a corporate database with about 60 different tables that spans
> manufacturing, accounting, marketing, etc.
> It is possible, but unwieldy, to establish a relationship for each
> table in the entire database through critical fields like customer_id
> or product_id.
> But should I do that?
> My question is: Is there such a thing as too many relationships? Can
> I establish referential integrity via relationships with critical
> tables like Accounting, but leave the rest unconnected and simply use
> JOINS in my business code?
> Thanks,
> HC

Monday, March 12, 2012

Is there such a thing as table name wildcards?

Hey all,

I have a datagrid with populated by this query: SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')

I have paging, sorting and selection enabled.

Now I am looking for a way to use a wild card as a placeholder for the table name in my select statements so I can use the valued selected from the datagrid.

Example : SELECT * FROM %TABLENAME%

TIA

WOOHOO! my first post.

You probably need to use dynamic SQL for this.

EXEC('SELECT * FROM ' + @.tablename)

|||

Thanks for pointing me in the right direction. I looked into dynamic SQL and ran with this stored procedure:

ALTER Procedure GenericTableSelect
@.TABLE_NAMEVarChar(100)
ASDeclare @.SQLVarChar(1000)
SELECT @.SQL ='SELECT * FROM '
SELECT @.SQL = @.SQL + @.TABLE_NAME
Exec ( @.SQL)

Some more info on dynamic sql : http://www.sqlteam.com/item.asp?ItemID=4599

Is there sp_helptext for tables

Hello Everybody, Please help me out:

Is there a system stored procedure for retrieving the sql statement that created a table.

I know i can use sp_helptext for views etc; i want the equivalent for tables.

sp_columns is not adequate either.

please help! thanks in advance;)sp_help will return all the columns of a table.|||sp_help will return all the columns of a table.
Hi Blindman,

I ran exec sp_help tblcustomers and i got:

Name: tblcustomers
Owner: dbo
Type: user table
Created_datetime: 4/18/2007 2:26:12 PM

Am i missing something??

Is there information_schema.view for count of rows in tables?

Hello,
Is there some kind of information_schema.something which lists tables and
the current count of rows in each table?
Thanks,
RichI tried something like this which did not work:
select table_name, (Select count(*) from table_name) as NumOfRows
from information_schema.tables
Any suggestions?
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||See
http://www.databasejournal.com/feat...cle.php/3441031
The above details two methods of getting the results that you want; one by
using a cursor, and another by using an undocumented stored procedure in SQL
Server 2000. Since I don't know what version you are using, I would recommen
d
looking at the cursor method.
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||Rich (Rich@.discussions.microsoft.com) writes:
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
Not in INFORMATION_SCHEMA, but in the system table sysindexes:
SELECT object_name(id), rows
FROM sysindexes
WHERE indid IN (0, 1)
I should add that the value you see here may not be exactly on the
mark, but most often it's close enough. This is a lot faster than
accessing each table.
The WHERE clause looks funny, but sysindexes is a bit special. For
each table there is always a row with indid = 0 *or* 1 - never both.
indid is one if the table has a clustered index, else it's zero.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As a follow up, you can use
SELECT object_name(i.[id]), rows
FROM sysindexes i INNER JOIN sysobjects o
ON i.[id] = o.[id]
WHERE i.indid IN (0, 1) AND o.xtype = 'U'
if you just want the row count of user-defined base tables. You can run the
following to get a comparison between counting the rows with COUNT(*) and
what appears in sysindexes. In a DB that I have, there was no difference for
any of the tables.
set nocount on
declare @.cnt int
declare @.table varchar(128)
declare @.cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables
fetch next from tables into @.table
while @.@.fetch_status = 0
begin
set @.cmd = 'select ''' + @.table + ''', count(*) from ' + @.table
insert into #rowcount exec (@.cmd)
fetch next from tables into @.table
end
CLOSE tables
DEALLOCATE tables
SELECT t1.tablename, t1.rowcnt AS "Counted with COUNT(*)", t2.[rows] AS
"Counted via sysindexes"
FROM #rowcount t1 INNER JOIN (
SELECT object_name(i.[id]) AS "tablename", i.[rows]
FROM sysindexes i INNER JOIN sysobjects o
ON i.[id] = o.[id]
WHERE i.indid IN (0, 1) AND o.xtype = 'U') t2
ON t1.tablename = t2.tablename
drop table #rowcount
--
"Erland Sommarskog" wrote:

> Rich (Rich@.discussions.microsoft.com) writes:
> Not in INFORMATION_SCHEMA, but in the system table sysindexes:
> SELECT object_name(id), rows
> FROM sysindexes
> WHERE indid IN (0, 1)
> I should add that the value you see here may not be exactly on the
> mark, but most often it's close enough. This is a lot faster than
> accessing each table.
> The WHERE clause looks funny, but sysindexes is a bit special. For
> each table there is always a row with indid = 0 *or* 1 - never both.
> indid is one if the table has a clustered index, else it's zero.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Thank you all for your replies. I did try both methods, the sysIndex table
method, and the cursor method (which included the sysindex table).
A few of the tables did have a slightly different count with count(*) than
the count in sysindex. I will guess that count(*) was probably a little bit
more current, as these are live tables getting data as we speak.
Does anyone know if peformance on data entry was affected while I ran the
cursor? The cursor took 25 seconds to run. Or is running this kind of
cursor pretty seamless against the data entry table?
Thanks again,
Rich
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||> I will guess that count(*) was probably a little bit more current
The count(*) rows (assuming you didn't use a nolock hint) will be almost
perfect as of the instant that the query finishes. The sysindexes value is
a reasonably recent value that is used for the optimizer to make "guesses"
as to how many rows are in the table much much faster than counting all of
the rows.

> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
Performance is affected no matter what the query :) Seriously, it is
affected in that some minor blocking will take place, and if you don't have
an index on the table, every row will have to be "touched" and locked during
the counting process. But the overhead on any other queries is likely
minimal.
The key here is to base which method you use based on your needs. If you
just want to know the number of rows in the table, then probably using
sysindexes is best, unless you need perfect results. Otherwise, locking is
fine. Note I said almost perfect in the first paragraph. Under the default
conditions in SQL Server, users can delete or insert rows that have already
been counted, so it is only as perfect as the users of the data allow.
I could keep going, but unless you really care about being perfect, it is
not really worth it :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:7C3F3BE6-296E-4F88-9C22-AB15DAA66050@.microsoft.com...
> Thank you all for your replies. I did try both methods, the sysIndex
> table
> method, and the cursor method (which included the sysindex table).
> A few of the tables did have a slightly different count with count(*) than
> the count in sysindex. I will guess that count(*) was probably a little
> bit
> more current, as these are live tables getting data as we speak.
> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
> Thanks again,
> Rich
> "Rich" wrote:
>|||Rich (Rich@.discussions.microsoft.com) writes:
> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
Unless there was a NOLOCK on the SELECT COUNT(*) queries, users could
experience blocking when you run the query. This can be particularly
noticeable on a large table that does not have any non-clustered indexes.
(If there is a non-clustered index, the COUNT(*) will run over that
index, which is cheaper than scanning the entire table.)
The query on sysindex is considerably leaner on resources, and it's
unlikely that it would cause blocking.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, March 9, 2012

Is there any way to make a VIEW automatically reflect changes to underlying tables?

(SQL Server 2000, SP3a)
Hello all!
I think I asked this question in a different way a long time ago, and wasn't sure if there
was any resolution.
Consider the following DDL/DML:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
select * from vTest
go
Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the VIEW
automatically reflect the changes to the underlying table? I had hoped a sp_recompile
might do the trick, but alas. :-(
Thanks for any help you can provide!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_005E_01C36B1C.27177190
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Check out sp_refreshview in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3a)
Hello all!
I think I asked this question in a different way a long time ago, and =wasn't sure if there
was any resolution.
Consider the following DDL/DML:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
select * from vTest
go
Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, =to have the VIEW
automatically reflect the changes to the underlying table? I had hoped =a sp_recompile
might do the trick, but alas. :-(
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_005E_01C36B1C.27177190
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Check out sp_refreshview in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:OmfK0zzaDHA.880@.T=K2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3a)Hello all!I think I asked this =question in a different way a long time ago, and wasn't sure if therewas any resolution.Consider the following DDL/DML:create =table tTest (Id int)gocreate view vTestas select * =from tTestgoselect * from vTestgoalter table tTest =add Name varchar(255)goselect * from vTestgoIs there =any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the VIEWautomatically reflect the changes to the underlying table? =I had hoped a sp_recompilemight do the trick, but alas. =:-(Thanks for any help you can provide!John Peterson

--=_NextPart_000_005E_01C36B1C.27177190--|||John,
I think you are searching fot sp_refreshview system SP. Check it in Books
OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and
wasn't sure if there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW,
to have the VIEW
> automatically reflect the changes to the underlying table? I had hoped a
sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>|||John,
Try this:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
sp_refreshview vTest
go
select * from vTest
go
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and
wasn't sure if there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW,
to have the VIEW
> automatically reflect the changes to the underlying table? I had hoped a
sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>|||Thanks, all! That was a speedy and accurate response! :-)
The sp_refreshview is exactly the type of thing I was looking for.
I think my other (somewhat related) thread was in reference to changing the
ansi_null/quoted_identifier settings of a VIEW/SP without having to DROP/CREATE or ALTER
that VIEW. It sounded as if there wasn't a convenient way to do that (though, someone had
come up with a clever solution to "dinking" the sysobjects table).
In any event, thanks again! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and wasn't sure if
there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the
VIEW
> automatically reflect the changes to the underlying table? I had hoped a sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>

Friday, March 30, 2012

is TOP 1 in JOIN possible

Wednesday, March 28, 2012

Is this table design correct?

Is this query possible?

Is this query possible?

Is this query correct

Monday, March 26, 2012

Is this possible without using cursors?

Is this possible with Full Text Indexing?

Is this possible with Full Text Indexing?

Is this possible with Full Text Indexing?

Is this possible with Full Text Indexing?

Friday, March 23, 2012

IS THIS GOOD DESIGN?

Is this feasible?

Wednesday, March 21, 2012

Is this doable?

Monday, March 19, 2012

Is this a "stored procedure" situation?

Is there transactional consistency across multiple publications?

Is there such a thing as too many relationship?

Monday, March 12, 2012

Is there such a thing as table name wildcards?

Is there sp_helptext for tables

Is there information_schema.view for count of rows in tables?

Friday, March 9, 2012

Is there any way to make a VIEW automatically reflect changes to underlying tables?

better method for DTS

Blog Archive

About Me