UNION VIEWS And CONSTRAINTS
Aug 29, 2007
I have a union view that unions 36 tables which have a date constraint set on the datetime field, the data for which is is same for all records within the respective tables. The purpose of this is to allow users to go straight to the month that they require. This works fine with raw sql queries using hardcoded dates , the query plan shows that only the required tables are accessed. However, if i create a lookup table (this is for business objects), join the union view date field to date field and use a between criteria, it ignores the constraint and scans all 36 tables in the view. Changing the between to = shows that the constaint is used. Does anyone have any ideas here , i seem to have tried everything
Mark
View 4 Replies
ADVERTISEMENT
Oct 31, 2007
For a Union All of Views where both views look at the same openrowset file I get
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
This probably occurs because the first view keeps the file open.
Any workarounds on this?
View 4 Replies
View Related
Sep 17, 2007
Hey
I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:
I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.
so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.
What shall a do?
Thanks for all help
View 10 Replies
View Related
Feb 5, 2007
hi frends.i have such a great problem.i want to use union in indexedview and i must have to use it. because i cant make a one tablebecause in those two tables there are 2,000,000 records entereddaily.So please give me suggestion. can also give me alternate ofusing UNION in indexed view.and i want to use idexed view only becausei will do searching afterwards.Regards,Mr.MirzaSoftware Engineer.
View 1 Replies
View Related
Mar 21, 2007
Hi,
I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.
I have two partitioned tables and a view on top of both tables as below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO
Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.
I then ran the query execution plan for the following query on both SQL server 2000 and 2005.
select * from TableView where TableID = 10
On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.
Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO
Now run the query execution plan for the same query again.
select * from TableView where TableID = 10
This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.
I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?
Is it a bug in SQL server?
Or am I missing any thing?
Any help appreciated.
Thanks
View 2 Replies
View Related
Jan 31, 2008
Hi folks,I'm having issues trying to create a union using two Views I created.View 1:SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, ExDescription.Surname + '. ' + ExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMemberFROM MONDAS.Reaseheath.dbo.ExDescription AS ExDescription INNER JOIN dbo.Development INNER JOIN dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id ON ExDescription.AccountCode = dbo.Development.Related_Staff_Id COLLATE Latin1_General_CI_AI View 2:SELECT dbo.Development.Object_Id, dbo.Development.Related_Event_Id, dbo.Development.Related_Staff_Id, dbo.NonStaffExDescription.Surname + ', ' + dbo.NonStaffExDescription.PreferredName AS StaffMember, dbo.Events.Event_type, dbo.Events.Academic_Year, dbo.Events.Start_date, dbo.Events.End_date, dbo.Development.IsStaffMemberFROM dbo.Development INNER JOIN dbo.Events ON dbo.Development.Related_Event_Id = dbo.Events.Object_Id INNER JOIN dbo.NonStaffExDescription ON dbo.Development.Related_Staff_Id = dbo.NonStaffExDescription.AccountCode In the first view I am linking to another server, which is why I have had to change the collation, but the second view all the tables are in the same database hence the collation is the same.But when I'm trying to do a UNION between the two I get another collation error, but can't figure out how to change the collation like I did in the first view.Heres the UNION query:SELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMemberFROM dbo.DevView1UNIONSELECT Object_Id, Related_Event_Id, Related_Staff_Id, StaffMember, Event_type, Academic_Year, Start_date, End_date, IsStaffMemberFROM dbo.DevView2 Can anyone help?Thanks
View 3 Replies
View Related
Jan 9, 2007
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
View 3 Replies
View Related
Jan 17, 2008
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
View 7 Replies
View Related
Jun 12, 2014
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
View 4 Replies
View Related
Apr 29, 2008
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
View 3 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Nov 6, 2006
Hi all,
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
Any idea why this could happen ?
View 18 Replies
View Related
Jun 28, 2007
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
View 3 Replies
View Related
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
Mar 14, 2006
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies
View Related
Oct 15, 2006
I'm using MS SQL 2000 with enterprise manager I have a customers table called high_customer, it has a primary key called IDI have an invoices table called high_invoices, it has no primary keys but it does have a column for customer ID.I want to set it so if a customer is deleted from the customers table, any invoices with that customerID are deleted from the invoices table.Do I need to specify a constraint? If so how do I do it in enterprise manager.Thanks
View 1 Replies
View Related
Apr 27, 2005
Hi,
I have the following problem:
I have a table called Jobs with the fields:
JobNumber, Name, Customer, ...
And a table called Customers with the fields:
ID, Name, Address, ...
Obviously Jobs is linked to Customers with the Customer<->ID fields. I want to set it up so that if a Customer is deleted, then any Jobs that had that customer listed now have the Customer field set to NULL. Can I do this with a constraint, or will I need to use a trigger?
Cheers,Little'un
View 10 Replies
View Related
Feb 27, 2001
Hello,
I am not a DBA and I need to do the following.
I have 2 tables A and C and both have a common fileds say emp.Emp is pkey in C .
There can be a record in C but not in A.If a record is inserted in A then it checks for the same employee info in C .If not found it do not allow to insert a record in A.
How to go for it?
Thanks!
View 1 Replies
View Related
Feb 5, 2004
Hiya peops!!
Listen i was wondering if anyone could tell me whether they know the correct syntax for creating constraints or where i could find it on the net???
Thanks
View 3 Replies
View Related
Feb 5, 2004
Hiya peops
im creating a constraint using enterprise manager for 1 of my tables n was wondering how u constructively create a constraint expression, there is a specific style,right??
Thanks
View 1 Replies
View Related
Nov 20, 2007
hi there,
i have added my tables and defined foreig keys and primary keys for my table, but when i try to put the data it gives me some error reagards to parent key..and it wont let me drop the table... can anybody tell me how to drop the foregin key..i have tried (
ALTER TABLE module DROP CONSTRAINT ATTENDANCEID_FK;)
this formula and it wont work,
heres my tables and relationships:
CREATE TABLE ATTENDANCE
(ATTENDANCEID char(4) NOT NULL,
StudID char(4) NOT NULL,
ModuleID char(4) NOT NULL,
Week1 VARCHAR(10),
Week2 VARCHAR(10),
Week3 VARCHAR(10),
Week4 VARCHAR(10),
Week5 VARCHAR(10),
Week6 VARCHAR(10),
PRIMARY KEY (ATTENDANCEID));
ALTER TABLE ATTENDANCE
ADD FOREIGN KEY (studID) REFERENCES student(studID);
ALTER TABLE ATTENDANCE
FOREIGN KEY (moduleID) REFERENCES module(moduleID));
insert into ATTENDANCE values
('F1','A1','E1','Yes','Yes','Yes','Yes','Yes','Yes');
insert into ATTENDANCE values
('F2','A2','E2','Yes','No','Yes','Yes','No','Yes');
insert into ATTENDANCE values
('F3','A3','E2','Yes','Yes','Yes','No','Yes','Yes');
insert into ATTENDANCE values
('F4','A4','E2','Yes','Yes','Yes','Yes','No','Yes');
insert into ATTENDANCE values
('F5','A5','E1''Yes','Yes','No','Yes','Yes','Yes');
insert into ATTENDANCE values
('F6','A6','E2','Yes','Yes','Yes','Yes','Yes','No');
insert into ATTENDANCE values
('F7','A7','E1','Yes','Yes','Yes','Yes','No','No');
insert into ATTENDANCE values
('F8','A8','E2','Yes','Yes','Yes','No','Yes','Yes');
insert into ATTENDANCE values
('F9','A9','E3','Yes','Yes','No','Yes','Yes','Yes');
CREATE TABLE MODULE
(ModuleID char(4) NOT NULL,
ATTENDANCEID char(4) NOT NULL,
ModuleName varchar(45),
ModuleCode varchar(6),
PRIMARY KEY (ModuleID));
ALTER TABLE MODULE
ADD FOREIGN KEY (attendanceID) REFERENCES attendance(attendanceID);
insert into MODULE values
('E1','F1','Workshop Research and Project Implementation','Im3023');
insert into MODULE values
('E2','F2','Advanced Database Development','SD3042');
insert into MODULE values
('E3','F3','Advacned Information Systems Develpment','SD3043');
charles
View 2 Replies
View Related
Dec 11, 2007
I am fairly new at SQL and also new to this forum. So I don't know if this topic belongs here or not.
But anyways, I found this tutorial online. However, I am having trouble completing it. Can someone please help me?
Given the following table definitions:
CREATE TABLE USERS
(
USERID CHAR(5) PRIMARY KEY,
NAME VARCHAR(30),
RATING CHAR(1) CHECK (RATING IN (A, B,C,D)
)
CREATE TABLE ITEMS
(
ITEM_NO NUMBER PRIMARY KEY,
DESCRIPTION VARCHAR(50),
OFFERED_BY CHAR(5) REFERENCES USERS(USERID) ON UPDATE CASCADE ON DELETE CASCADE,
START_DATE DATETIME,
END_DATE DATETIME,
RESERVE_PRICE NUMBER
)
CREATE TABLE BIDS
(
USERID CHAR(5) REFERENCES USERS ON UPDATE CASCADE ON DELETE SET NULL,
ITEMNO NUMBER REFERENCES ITEMS(ITEM_NO) ON UPDATE CASCADE ON DELETE CASCADE,
BID NUMBER,
BID_DATE DATETIME,
PRIMARY KEY(USERID,ITEMNO,BID_DATE)
)
Directions: Indicate whether the statement is accepted or rejected; and if accepted indicate its effects. Evaluate each statement INDEPENDENTLY.
1) INSERT INTO ITEMS VALUES(1009, Red Bicycle,U01,1999-01-08, 1999-02-10, 30)
2) UPDATE ITEMS SET ITEMNO=1008 where ITEMNO=1001
3) DELETE FROM ITEMS WHERE ITEMNO=1005
4) INSERT INTO ITEMS VALUES(1010, Green Bicycle,U07,1999-01-08, 1999-02-10, 30)
5) UPDATE BIDS SET ITEMNO=1011 WHERE ITEMNO = 1001
6) DELETE FROM USERS WHERE USERID=U01
7) UPDATE ITEMS SET ITEMNO=1020 WHERE ITEMNO=1001
8) DELETE FROM BIDS WHERE BID > 1001
Thanks. I would greatly appreciate your help.
View 4 Replies
View Related
Dec 7, 2007
My table structure is something like this :
ORDER_ITEM (OrderSK, ItemNumber, Qty, ServiceSK, UnitPrice)
SERVICE (ServiceSK, Description, UnitPrice)
How could I modify the database structure to enforce the data constraint that ORDER_ITEM.UnitPrice must be equal to SERVICE.UnitPrice for the corresponding service.
View 6 Replies
View Related
Jan 23, 2004
i have a question about constraints in SQL server.
i have three tables:
Client
---------
clientID
name
etc...
Bank
-------
BankID (a virtual number, identity)
ClientID (FK: Client)
BankAccountNumber
BankName
Order
--------
OrderID
ClientID
BankID
a Client an have one or more bankAccounts.. if he place an order, he can select wich bankaccount will be used to pay.
how do i have to set the constraints so that the bankAccount in Order, is allways of THAT client in order.???
Thanx...
View 3 Replies
View Related
Jul 17, 2004
I have to change someones username in my database in a couple of tables. When I try to make any changes, I get an error that there is a constraint. Is there a way to make the changes without removing all the constraints then replacing them?
Thanks
View 1 Replies
View Related
Nov 30, 2001
Hi,
Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.
ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag
ALTER TABLE users DROP CONSTRAINT dev1
How do I remove any existing default values using script?
TIA,
Seoras.
View 2 Replies
View Related
May 4, 2001
How does one know what a particular trigger or constraint is supposed to be doing on a table. I am just two days old on this position, and no form of documentation, at the same time I am expected to trouble-shoot some error messages. Thanks for your help.
View 3 Replies
View Related
Sep 18, 2001
When I first set up my database, I set it up with many unique constraints, but not much in the way if indexes. After reading more about the benefits of indexes, I'm thinking of deleting many of the unique constraints, and replacing them with unique indexes where appropriate.
It seems that indexes would be better than unique constraints in many cases, because you have the double benefit of enforcing uniqueness, as well as improving sorting capability. Does this make sense?
View 1 Replies
View Related
Apr 30, 2000
Hi folks,
Wonder if I'm onto something....everything I've read says that to drop foreign key constraints, you have to drop/recreate the table.
I've been able to dynamically create the ALTER TABLE statements for all FK constraints in a database. The way I did was a little convoluted (Access 97 linked tables and pass-through queries, cut/paste from the debug window to SQL7 QA), but it does work. More importantly, it saves the hassle of dropping/recreating tables....Someone with more expertise than I in using nested cursors could probably convert it to pure T-SQL pretty easily.
Just curious...opinions? Be glad to post the code here if that's appropriate.
Dan
View 1 Replies
View Related
Dec 1, 2000
How do i create a check constraints on column a so it dose not accept $ character? syntax pls.
Thanks
View 2 Replies
View Related
Sep 25, 2002
Can anyone recommend a good article/book on the subject? I am trying to find out more about compound primary keys. How many is “too many”? Does the number of primary key columns affect your performance? If so, how does it affect it? Should some of the constraints be handled from stored procedures?
Any response is greatly appreciated.
:cool:
View 2 Replies
View Related
Dec 29, 2006
Executed as user: companyadmin user. Violation of PRIMARY KEY constraint 'PK_tblRMAProduct'. Cannot insert duplicate key in object 'tblRMAProduct'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
View 1 Replies
View Related
