primary key in sql view

i need to set a primary/unique key to my view which i will later need to return rows according to this unique key. the view is as:
CREATE VIEW AllEntrantSummary (ArticleIDFK, EntryMethod, Answer, FirstName, LastName, DayPhone, Email, MobileNo) AS
SELECT     gmtv_registration.gmtvreg.compentry.ArticleIDFK, 'Web', CAST(CompAns AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer, CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
                      CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName, CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS
                       AS DayPhone, CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
                      CAST(Mobile AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM         gmtv_registration.gmtvreg.userprofile, gmtv_registration.gmtvreg.compentry
WHERE     gmtv_registration.gmtvreg.userprofile.userpk = gmtv_registration.gmtvreg.compentry.userfk
UNION ALL
SELECT     ArticleIDFK, 'Wap', CAST(Answer AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer, CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
                      CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName, CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS
                       AS DayPhone, CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
                      CAST(MobileNo AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM         gmtv_wap.dbo.compentry
UNION ALL
SELECT     ArticleID AS ArticleIDFK, 'imode', CAST(CompAnswer AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer, CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
                      CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName, CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS
                       AS DayPhone, CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
                      CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM         gmtv_imode.dbo.compentry


Please can some one tell me how i can add another coloumn so that it displays the row number?
khuz01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

khuz01Author Commented:
i have found this example:
   CREATE VIEW v_pub_rank
   AS
   select rank=count(*), s1.title_id, qty=sum(s1.qty)
   from (select title_id, qty=sum(qty) from sales group by title_id) s1,
        (select title_id, qty=sum(qty) from sales group by title_id) s2
   where s1.qty >= s2.qty
   group by s1.title_id

but how would i integrate this with my query above?
0
Christopher KileCommented:
You are not creating a primary key, though what you want can be used as such.  What you are creating is an identity field. You can not create an identity field in a view.  HOWEVER, you can create an identity field in a constructed table returned by a user-defined function:

CREATE FUNCTION AllEntrantSummary
RETURNS @aes TABLE
(
ident INT IDENTITY(1, 1),
ArticleIDFK AS INT,
EntryMethod AS VARCHAR,
Answer AS VARCHAR,
FirstName AS VARCHAR,
LastName AS VARCHAR,
DayPhone AS VARCHAR,
Email AS VARCHAR,
MobileNo AS VARCHAR)
AS
BEGIN
INSERT INTO @aes
(
ArticleIDFK,
EntryMethod,
Answer,
FirstName,
LastName,
DayPhone,
Email,
MobileNo)
SELECT    
gmtv_registration.gmtvreg.compentry.ArticleIDFK,
'Web',
CAST(CompAns AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer,
CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName,
CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS DayPhone,
CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
CAST(Mobile AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM        
gmtv_registration.gmtvreg.userprofile, gmtv_registration.gmtvreg.compentry
WHERE    
gmtv_registration.gmtvreg.userprofile.userpk = gmtv_registration.gmtvreg.compentry.userfk
UNION ALL
SELECT    
ArticleIDFK,
'Wap',
CAST(Answer AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer,
CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName,
CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS DayPhone,
CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
CAST(MobileNo AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM        
gmtv_wap.dbo.compentry
UNION ALL
SELECT    
ArticleID AS ArticleIDFK,
'imode',
CAST(CompAnswer AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer,
CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName,
CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS DayPhone,
CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
 CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM        
gmtv_imode.dbo.compentry
RETURN
END
GO

The usage would be:

SELECT * FROM AllEntrantSummary()

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
khuz01Author Commented:
thanks but there are a couple of problems:

1) i get an error saying @aes needs to be defined.

2) i need to delete the table afterwards, how would i do this on the fly?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Christopher KileCommented:
Change

CREATE FUNCTION AllEntrantSummary

to

CREATE FUNCTION AllEntrantSummary ()

and the table is generated new every time you call the function and is never stored permanently, so there is no need to drop the table.
0
khuz01Author Commented:
syntax check is now ok but when i run it, it says error on this line:
 ident INT IDENTITY(1, 1)

Invalid column 'INT' is specified in a constraint or computed-column definition.
0
khuz01Author Commented:
do i need to add a constraint at the bottom?
0
khuz01Author Commented:
this is the full error message:

Server: Msg 1759, Level 16, State 1, Procedure AllEntrantSummary, Line 2
Invalid column 'INT' is specified in a constraint or computed-column definition.
0
khuz01Author Commented:
ITS OK, I'VE SOLVED IT!

I GOT RID OF THE AS IN THE ORIGINAL DECLARATION.

ie:(
ident INT IDENTITY(1, 1),
ArticleIDFK AS INT,
EntryMethod AS VARCHAR,
Answer AS VARCHAR,
FirstName AS VARCHAR,
LastName AS VARCHAR,
DayPhone AS VARCHAR,
Email AS VARCHAR,
MobileNo AS VARCHAR)

got changed to:
(
ident INT IDENTITY(1, 1) NOT NULL,
ArticleIDFK VARCHAR,
EntryMethod VARCHAR,
Answer VARCHAR,
FirstName VARCHAR,
LastName VARCHAR,
DayPhone VARCHAR,
Email VARCHAR,
MobileNo VARCHAR)
0
Christopher KileCommented:
OUCH....sorry about the AS...the QA person for pro bono work was busy yesterday...glad it finally works, though :)  I've found user-defined functions very useful in providing views with clustered indices without having to conform to the rather LONG rule list for constructing such a view.
0
khuz01Author Commented:
its ok, thanks for your help anyway.

the only problem is that the query takes a very long time to return the results, is there a way i can speed it up?
 
what is that about clustering u mentioned, will this speed up the query?
0
Christopher KileCommented:
No, because the clustering is occuring as the data is being inserted into the return table.  How does the standalone query perform?
0
khuz01Author Commented:
well before i realised the need for an identity column, it was fine. but the creation of this table maxes out the tempdb with 3gb of data!

the error i get is:
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

is this happening because i'm doing a union all? will it change if i just do a join?

0
Christopher KileCommented:
Hmmmmm...I don't think you can do this with a join, unless I'm misunderstanding your query.

Also, I've gone back over what you've told me, and I realized that though I gave you what you asked for, I may not have given you what you wanted.  

I don't think either a view or a table function is actually what you need.  If you generate this table on the fly several times within one procedure, especially when all such generations are occurring in the context of a single transaction, then you are potentially generating a very LARGE number of transaction steps (hence the overflow of your transaction log).  

If, as I suspect, you are joining to the results of this query more than once during a transaction, I think you should seriously consider writing this query to a permanent table.  Multiple users can access the table at once if you identify each query request with its own identifier; an insert trigger can be used to update the "identity" field you need (this will now be a sub-identifier within the query request) with the next number in sequence.  

If this sounds of interest, please let me know.
0
khuz01Author Commented:
i have just realised that i can reduce the number of records returned by specifying the 'articleid' in the function.
can you please check the following syntax for me please?

CREATE FUNCTION AllEntrantSummary(INCOMINGARTICLEID)
RETURNS @aes TABLE
(
ident INT IDENTITY(1, 1),
ArticleIDFK AS INT,
EntryMethod AS VARCHAR,
Answer AS VARCHAR,
FirstName AS VARCHAR,
LastName AS VARCHAR,
DayPhone AS VARCHAR,
Email AS VARCHAR,
MobileNo AS VARCHAR)
AS
BEGIN
INSERT INTO @aes
(
ArticleIDFK,
EntryMethod,
Answer,
FirstName,
LastName,
DayPhone,
Email,
MobileNo)
SELECT    
gmtv_registration.gmtvreg.compentry.ArticleIDFK,
'Web',
CAST(CompAns AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer,
CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName,
CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS DayPhone,
CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
CAST(Mobile AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM        
gmtv_registration.gmtvreg.userprofile, gmtv_registration.gmtvreg.compentry
WHERE    
gmtv_registration.gmtvreg.userprofile.userpk = gmtv_registration.gmtvreg.compentry.userfk

AND gmtv_registration.gmtvreg.compentry.ArticleIDFK = INCOMINGARTICLEID

UNION ALL
SELECT    
ArticleIDFK,
'Wap',
CAST(Answer AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer,
CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName,
CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS DayPhone,
CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
CAST(MobileNo AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM        
gmtv_wap.dbo.compentry

WHERE ARTICLEIDFK = INCOMINGARTICLEID

UNION ALL
SELECT    
ArticleID AS ArticleIDFK,
'imode',
CAST(CompAnswer AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Answer,
CAST(FirstName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS FirstName,
CAST(LastName AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS LastName,
CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS DayPhone,
CAST(Email AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS Email,
 CAST(DayPhone AS VARCHAR) COLLATE SQL_Latin1_General_CP1_CI_AS AS MobileNo
FROM        
gmtv_imode.dbo.compentry

WHERE ARTICLEIDFK = INCOMINGARTICLEID

RETURN
END
GO
0
khuz01Author Commented:
i've been able to get it working reasonably quickly tweeking the code in my last post. Your method sound like it'd work too but as i've got this working now, i dont really wanna break it!

thank you for all your help.
0
Christopher KileCommented:
CREATE FUNCTION AllEntrantSummary(INCOMINGARTICLEID)

should be

CREATE FUNCTION AllEntrantSummary(INCOMINGARTICLEID INT)

Otherwise, your syntax seems correct.  And yes, this should boost your performance considerably.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.