Solved

primary key in sql view

Posted on 2007-03-28
16
261 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
  • 10
  • 6
16 Comments
 

Author Comment

by:khuz01
Comment Utility
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 300 total points
Comment Utility
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
Comment Utility
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
 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
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
Comment Utility
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
Comment Utility
do i need to add a constraint at the bottom?
0
 

Author Comment

by:khuz01
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 23

Expert Comment

by:Christopher Kile
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now