Solved

primary key in sql view

Posted on 2007-03-28
16
269 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
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 300 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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