Need Help With Converting Old Style Subselect To ANSI Joins?
Jul 20, 2005
Help!
I'm trying to understand the new ANSI join syntax (after many years of
coding using the old style). I am now working with an application that only
understands ANSI syntax so I am struggling.
My first (old style syntax) SQL statement below produces 60 rows:
SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1,
ADDRESS A6
WHERE A1.ADDRESSID=A6.ADDRESSID
AND A1.CONTACTID IN
(SELECT A4.CONTACTID
FROM CONTACT_LEADSOURCE A4,
LEADSOURCE A5
WHERE A4.LEADSOURCEID = A5.LEADSOURCEID
AND A5.DESCRIPTION = 'some_description' )
AND A1.CONTACTID IN
(SELECT A2.CONTACTID
FROM TICKET A2,
ENROLLHX A3,
EVENT A7
WHERE A3.STATUS IN ('R', 'Confirmed')
AND A2.TICKETID = A3.EVXEVTICKETID
AND A3.EVENTID = A7.EVENTID
AND A7.CODE IN
('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))
ORDER BY A1.LASTNAME ASC
I am trying to convert this to the newer ANSI sytax. My second SQL statement
below produces 67 rows (duplicates):
SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1
JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)
JOIN
( SELECT C.CONTACTID
FROM CONTACT C
JOIN CONTACT_LEADSOURCE A4 ON (C.CONTACTID
= A4.CONTACTID)
JOIN LEADSOURCE A5 ON (A4.LEADSOURCEID =
A5.LEADSOURCEID
AND A5.DESCRIPTION =
'some_description' )) AS C1 ON C1.CONTACTID = A1.CONTACTID
JOIN
(SELECT C2.CONTACTID
FROM CONTACT C2
JOIN TICKET A2 ON (C2.CONTACTID =
A2.CONTACTID)
JOIN ENROLLHX A3 ON (A2.TICKETID =
A3.TICKETID AND A3.STATUS in ('R', 'Confirmed'))
JOIN EVENT A7 ON (A3.EVENTID = A7.EVENTID
AND A7.CODE IN ('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')))
AS C3 ON C3.CONTACTID = A1.CONTACTID
Hi everyone.. can anyone help me on how to solve my problem regarding on Select.. im using PB6.5 and running on MSSLQ2005 database.. i attached an image for your reference.. thnks!
Help....I have a DB I'm working with that I know doesn't work with theANSI-92 JOIN SYNTAX....I'm not sure how much this limits my ability todeal with the following situation, so I'm soliciting the help of aguru....I apologize for the lack of scripted table structure, but thisdatabase is embedded in an application that I have no true schema for.I have a crude diagram of the tables and some of the relationships, butI've managed to have manually mapped some of the fields in the tablesI'm working with.What I have is a table(A) that I need to join with 10 othertables.....I'm joining on an identifier in the (A) that may exist manytimes in any of the other 10 tables...and may not be in ANY of thetables.When I run this query:SELECTSAMPLES.PK_SampleUID,UDFSAMPLEDATA02.AlphaData,UDF SAMPLEDATA01.AlphaData,UDFSAMPLEDATA03.AlphaData,UDFSAMPLEDATA05.AlphaData, UDFSAMPLEDATA06.AlphaData,UDFSAMPLEDATA07.AlphaData, UDFSAMPLEDATA08.AlphaData,UDFSAMPLEDATA09.AlphaData,UDFSAMPLEDATA10.AlphaDat aFROM SAMPLES, UDFSAMPLEDATA01,UDFSAMPLEDATA02,UDFSAMPLEDATA03,UDFSAMPLEDATA05,U DFSAMPLEDATA06,UDFSAMPLEDATA07 ,UDFSAMPLEDATA08, UDFSAMPLEDATA09, UDFSAMPLEDATA10WHERE UDFSAMPLEDATA02.AlphaData<>' ' ANDUDFSAMPLEDATA01.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA02.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA03.FK_SampleUID= SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA05.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA06.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA07.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA08.FK_SampleUID = SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA09.FK_SampleUID=SAMPLES.PK_SampleUID ANDUDFSAMPLEDATA10.FK_SampleUID = SAMPLES.PK_SampleUIDI return what appears to be the gazillion COMBINATIONS of all thefields in all the tables....they query doesn't even finish before theODBC driver I'm working with crashes my VBscript....Is there some way to take the multiple returned rows from a join andwork them all into ONE row per identifier?Any help I can garner would just make my week!TIA!J
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
My company wants me to research and flags or registry tricks that would allow non-ansi joins '=*' and '*=' in SQL Server 2005 with a compatiblity mode of 90 to be allowed.
The way I understand the situation is that in SQL Server 2005 with the database compatiblity set to 90, non-ansi join SQL such as the following would not work.
Select * from Customer, Sales Where Customer.CustomerID *= Sales.CustomerID
To work, the SQL above would have to be converted to ansi join SQL such as the following:
Select * from Customer LEFT OUTER JOIN Sales On Customer.CustomerID = Sales.CustomerID
Many hours would be spent browsing through millions of lines of code to find the non-ansi SQL and have changes made.
Does anyone know of any trace flaqs or registry entries that would allow SQL Server 2005 work in 90 compatiblity and still allow non-ansi =* and *= joins in SQL?
Hi,Just curious. Would you use ANSI style table joining or the 'oldfashion' table joining; especially if performance is the main concern?What I meant is illustrated below:ANSI Styleselect * from a join b on a.id = b.idOld Styleselect * from a, b where a.id = b.idI noticed that in some SQL, the ANSI is much faster but sometimes, theold style looks much better.It's ridiculous to try out both styles to see which is better wheneverwe want to write an SQL statement.Please comment.Thanks in advance.
A question for everyone: With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.
We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.
I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). This syntax returns four rows. Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join:
Code Block select t1.* from tblconfiguration t1 ,tblconfiguration t2 where t1.component = 'AdjProcessUtility' and t1.servername *= t2.servername and t1.component *= t2.component and t1.context *= t2.context and t1.property = 'proc' and t2.property = 'fff'
When the converted (using SQL enterprise Mgr) runs it returns no rows:
Code Block SELECT t1.* FROM dbo.tblConfiguration t1 LEFT OUTER JOIN dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')
I don't really see how to change this query to make it work. I've searched the web and I really don't see any examples of left joins which use more than one column.
Here's the table definition:
Code Block CREATE TABLE dbo.tblConfiguration ( ServerName VARCHAR(30) NOT NULL, Component VARCHAR(255) NOT NULL, Context VARCHAR(255) NOT NULL, Property VARCHAR(255) NOT NULL, CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ), Value VARCHAR(255) NOT NULL )
I use this table to define reports and there attribues. The rows repeat themselves except for the Property and Value columns Here is some of the data:
I have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.
SELECT c.juris_id, b.jrnl_mo_yr FROM a_trueup a, #t_mths b, r_rj c WHERE a.rlzd_mo_yr =* b.jrnl_mo_yr AND a.juris_id =* c.juris_id
[code]....
I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..
I want to make a CSS Style solution for my reports. I have few ideas related to this, changing the report properties on the fly, but I am not sure for which I should go for.
- We know that we can put expression on reports almost everywhere, and can also call custom code for setting up those expressions. i.e.
Public Shared Function GetValue(ByVal Key As String) As String
Dim myDataReader As SqlDataReader Dim mySqlConnection As SqlConnection Dim mySqlCommand As SqlCommand
mySqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;database=tempMIQB") mySqlCommand = New SqlCommand("SELECT * FROM [Properties] Where PropertyName='" & Key & "'", mySqlConnection) mySqlConnection.Open() myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
If myDataReader.Read() Then GetValue = myDataReader.Item("PropertyValue").ToString Else GetValue = "None" End If
End Function
and in textbox or any where I can say =Code.GetValue("BGColor") or from .NET dll.
For going further on with this idea I can create an .NET dll and get the list from database or XML file. (Please give suggestions for performance/scalability issue.)
- second idea is to write a custom application (script) which can go through all the reports and change the color and fonts and every thing.
- third is to use parameters and use Array.IndexOf method to search for the value, but in this case i need to add dataset to all reports and, I think we cannot access report properties i.e. Parameters from custom code, just to make a generic function to access parameter value.
I have two tables with the following relevant fields:
Apps appID appName
PBC pbcID appID appCT
These are joined on appID. appCT can be 1 of 2 values, either "PC" or "LA". So an example of a few records in PBC would be:
1 1 PC 2 1 LA 3 2 PC 4 2 LA 5 3 PC 6 4 LA ... ...
You can see that for each App, in PBC there can be two related records - PC and LA. But for example, record number 5 is App 3 PC, but there is no App 3 LA. I am trying to build a select to tell me which Apps are not in PBC at all, AND which Apps only have either LA or PC, not both.
I'm working on a purchasing website for a store. A request has many line items, and a line item can have many products. One of the characteristics of the line item data table is a total price, calculated from multiplying lineitems.quantity and product.price.
INSERT INTO lineitems (request_id, quantity, product_id, total_price)VALUES (@rid,@quant,@pid,@totalprice)WHERE @totalprice = (SELECT products.price * @quant FROM lineitems, products WHERE lineitems.product_id = products.id) Visual Studio isnt accepting this. Is there a way to do this better?
How do you combine the following 2 updates into one Update statement (1 SUBSELECT statement)
Update SPLL_Policy SET SPLL_Policy.Prog_Year = (Select TOP 1 Prog_Year From SPLL_WinsPolicy_Input Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
Update SPLL_Policy SET SPLL_Policy.Prog_NAME = (Select TOP 1 Prog_Name From SPLL_WinsPolicy_Input Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
I have the following table: CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1)) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)
-- Those works and gives me 11,12,21,22 SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3) SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2','3')
-- This doesn't works declare @Rule varchar(10) set @Rule='2,3' SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule) Any idea? I don't mind to change the data type if it works.
I have two tables in which I need to select data from and I don't know what construct to use. The two tables are SY and MV. SY contains stocks and MV contains a log of all price changes of these stocks. I need to produce a report of price changes between the latest price and the previous price and take the difference between the two. I'm using MSSQL. Here are the important fields in my tables:
SELECT sy.syid, sy.sycode, mv.price, (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) AS lastprice, (mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) AS diff, (mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) / (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) * 100 AS percentdiff, mv.date FROM mv, sy WHERE mv.syid = sy.syid AND mv.date = '8/27/2007' AND sy.ACTIVE = '1' ORDER BY sy.syid, mv.date DESC
I have a query which contains 2 subselects joined with a union all. The select for each is just a count, so I'm only returning 2 rows. I then want to be able to perform a calculation between these 2 results... ie divide one by the other to get the percentage.
The only way I could think of doing that was make the whole query a subselect of another query where I could then perform the calculation in the new select statement, however it doesn't like this. I just get incorrect syntax near the closing bracket of the from section.
Any ideas? Thanks!
SELECT * FROM
(SELECT count(t0.product) FROM (SELECT t0.packslip , t1.date_upld , t0.product AS product , t0.qty_topick as topick , t0.qty_picked as picked , t0.qty_topick - t0.qty_picked as shorted, (t0.qty_picked / t0.qty_topick) * 100 as linefill FROM rbeacon.dbo.shipline2 t0 INNER JOIN rbeacon.dbo.shiphist t1 ON t0.packslip = t1.packslip WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)) t0
UNION ALL
SELECT count(t1.product) FROM (SELECT t0.packslip , t1.date_upld , t0.product AS product , t0.qty_topick as topick , t0.qty_picked as picked , t0.qty_topick - t0.qty_picked as shorted, (t0.qty_picked / t0.qty_topick) * 100 as linefill FROM rbeacon.dbo.shipline2 t0 INNER JOIN rbeacon.dbo.shiphist t1 ON t0.packslip = t1.packslip WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101) AND t0.qty_picked <> t0.qty_topick) t1) t2
Hello,I have a problem with a subselect I use in a stored procedure:UPDATE #TEMP_TABLESET P_ID_1=(SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID),P_ID_2=PARENT_ID,P_ID_3=IDWHERE PARENT_ID IN (SELECT P_ID_2FROM #TEMP_TABLE b)So the subselect is (SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID), and it returns NULL. The cause of that is mostprobably the fact that I try to link ID from inner table b withPARENT_ID from the outer table. I thought it had to be done this way,but obviously not. Can somebody help me with this syntax problem?Thx,Bart
Not sure if this is the right group to post this to but.This is the current query that I have.select tableA.id,tableB.artist,tableB.image,from tableA,tableB wheretableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20order by tableB.price DESC'What I need is, for each row returned I need information from a thirdand fourth table. tableC, and tableD.tableC has information ( the tableA.id = tableC.eventId) that I need toobtain tableC.accountId = tableD.accountId in order do select thethe binding information in tableD between a Vendor(name,address..etc..)and tableB.imageAny help would be greatly appreciated.
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select ' + char (39) + @country + char (39) + ' as PAIS, A.ID, A.IDUSUARIO MSISDN, NULL AS MSISDN_COD, convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL, NULL AS FECHA_MO_LOCAL_D,
Hope someone could help me in revising a long running query. Here is the query
select * from table1 where classid is null and productid not in ( select productid from table1 where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
Hope someone could help me in revising a long running query. Here is the query
select * from table1 where classid is null and productid not in ( select productid from table1 where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
I have a stored procedure what produces N number of rows.The rows are ordered by a cataegoryType as followscatAcatBcatCWhat is needed to do on the C++ code side is break these out intotheir respective categories by iterating through the rows and checkingthe category type. Is there a way to let the DB do this via some sort ofsubselect on the rows returned via the stored procedure.Thanks in advance.
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
I have a sqldatasource, and on the selectcommand I'm trying to use a case statement with a subselect. The case statement works fine without the subselect, but I'm trouble getting it to work with the case statement. Could you help me with the syntax? ThanksSelectCommand=" SELECT DISTINCT RecipeID, Title FROM [Recipes] WHERE (CASE WHEN @Type='Appetizer' THEN Appetizer WHEN @Type='Pies' THEN (Select Distinct RecipeID, Title From Recipes WHERE Title like '%Pie%') WHEN @Type='Beverages' THEN Beverage WHEN @Type='Dessert' THEN Dessert WHEN @Type='Kids' THEN Kids WHEN @Type='Side' THEN Side WHEN @Type='Soup' THEN Salad WHEN @Type='Main' THEN Main WHEN @Type='Breakfast' THEN Breakfast END) = 1"
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp), CASE DATENAME(dw, sph.datestamp) WHEN 'Monday' then 'Monday' ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA') END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
1) What is the restriction on the max no. of tables that can be used in any one query in v7.0? In 6.5 it was a max of 16 :( 2) Are non-ANSI-style joins permitted in v7? Just that we're thinking of upgrading but 50% of our stored procs/views have the old *= syntax Thanks, Brad Carr
ok people, this is getting seriously frustrating! Please help!
As mentioned in a previous post, one of my batch jobs is printing fields with padding added, even when the table column is defined as varchar.
I've been to the knowledge base, read the article on ansi padding, ran the test scripts. But when I ran the select to display the columns, THE OUTPUT FOR BOTH TABLES WAS IDENTICAL!!! Apparently the SET ANSI_PADDING ON/OFF option had no effect!
What am i doing wrong? What is missing? Do i have to run the Set Ansi Padding option in the Master DB context? Have I unknowingly overridden the option somewhere else? Must I brush up on my COBOL for a career change?
My question here is whether or not MS SQL is processing joins according to the ANSI SQL standard. The situation is this, a friend of mine has the following query (The definition for the tables and the data in them are at the end of the message):
SELECT t.*, v.* FROM Table3 T LEFT OUTER JOIN TableView V ON T.tx = V.tx WHERE V.tx IS NULL and V.part IN (1,2)
My friend is implying that the OUTER join takes precedence over the WHERE clause and should return ALL rows from table3 that don't exist in the view. The view, as you will see below, is a partitioned view. My contention is that the proper join processing is to join the tables first and then apply the WHERE clause to that result set (regardless of the join type). In my friend's case, he expects this query to return four rows. My expectation is that it will return zero rows, because in the result set after the join is performed there are no rows that have a NULL in the v.tx column AND 1 or 2 in the v.part column. The actual ouput agrees with my premise and my friend is sure that this is not proper ANSI SQL join processing. Is this proper ANSI join processing or is MS SQL deviating from the standard?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO
CREATE TABLE [dbo].[Table1] ( [Part] [tinyint] NOT NULL , [id] [int] NOT NULL , [tx] [INT] NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [Part], [id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [CK_Table1] CHECK ([Part] = 1) GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table2] GO
CREATE TABLE [dbo].[Table2] ( [Part] [tinyint] NOT NULL , [id] [int] NOT NULL , [tx] [INT] NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( [Part], [id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [CK_Table2] CHECK ([Part] = 2) GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table3] GO
CREATE TABLE [dbo].[Table3] ( [tx] [int] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Table3] WITH CHECK ADD CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED ( [tx] ) ON [PRIMARY] GO --DROP VIEW TableView CREATE VIEW TableView AS SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 GO
