Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

primary key in sql view

Posted on 2007-03-28
16
Medium Priority
?
273 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:khuz01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
16 Comments
 

Author Comment

by:khuz01
ID: 18809290
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
 
LVL 23

Accepted Solution

by:
Christopher Kile earned 1200 total points
ID: 18809842
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
 

Author Comment

by:khuz01
ID: 18814973
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18815028
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
 

Author Comment

by:khuz01
ID: 18815083
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
 

Author Comment

by:khuz01
ID: 18815162
do i need to add a constraint at the bottom?
0
 

Author Comment

by:khuz01
ID: 18815239
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
 

Author Comment

by:khuz01
ID: 18815357
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
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18815681
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
 

Author Comment

by:khuz01
ID: 18816175
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
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18816399
No, because the clustering is occuring as the data is being inserted into the return table.  How does the standalone query perform?
0
 

Author Comment

by:khuz01
ID: 18816483
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
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18816820
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
 

Author Comment

by:khuz01
ID: 18817034
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
 

Author Comment

by:khuz01
ID: 18817404
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
 
LVL 23

Expert Comment

by:Christopher Kile
ID: 18817418
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question