Creating a table with a dual primary key

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!Does it matter that the destination needs to start at 1 and be sequential? If not, just use the Identity value. Probably too simple.

If you do it in a cursor, you could use a counter and move each record one at a time and use your counter to create the line number.

You could create the line number field in your source and pre-populate it with a process to loop through and assign the line numbers just prior to dumping it to the destination.

Doing it within one SQL statement to move it from one to the other might be impossible.|||Yes, you can create primary key that consists of two columns, or composite primary key.
It is not possible to create dual promary key in SQL server. You may create multiple uniqe index/constraints. But for one talbe, there is only one primary key.|||It is not quite clear what you exactly want to do.

Do you want to add a new column for an existing table as a primary key or is it a whole new table?|||I think we are on the right track (sort of).

I my example, the Order ID's are already assigned (they basically use an Identity Key).

What I need is some sort of query (or activeX script) that will loop through each record, and assign the Order_Line_ID (By the way, I don't care how many SQL statements this takes, as long as it works).

I am querying the Order_Line table, and sorting it by the Order#, so the table will look like this at first:

LineID-Identity | Order#
1 | 12345
2 | 12345
3 | 12345
4 | 12346
5 | 12347
6 | 12347
7 | 12348

The idea is, an order may have multiple lines (you've probably ordered more than one item at a time from some site online)

The counter needs to start over at 1 when it comes to a new order. For example, if the first three records in the Order_Line table are from Order# 12345, the first three rows would be numbered 1, 2 and 3, respectively (that should make sense). Now the tricky part: If the next Order # is 12346, the counter would start at 1 again, like this.

LineID-Identity | Order# | OrderLine#
1 | 12345 | 1
2 | 12345 | 2
3 | 12345 | 3
4 | 12346 | 1
5 | 12347 | 1
6 | 12347 | 2
7 | 12348 | 1

As you can see, this is a new column, so no data is being removed. How do you do this?|||Within SQL, use a cursor and loop through a sorted list of the line items. Use a "current" and "previous" variable to hold the order ID and a counter to hold the new line number. Create the field (null at first), loop through the cursor and using the logic of comparing the last order ID to the current one, assign the line number from the counter, increment the counter, etc. Look up the cursor options you'll need to use to make the cursor editable.

Creating a Publication

Hi,
I am trying to create a publication and when i select all tables then there
are certain tables which cannot be published. I get a key with a cross sign
on it.
Whats the reason and how to overcome this? coz I want to publish all tables.
Any help is highly appreciated.
Thanks
pmud
It seems you're using transactional replication and the key with a cross
sign means that this table can't be replicated because it doesn't have a
primary key. This script will list all these tables:
select * from information_schema.tables
where table_type = 'base table'
and objectproperty(object_id(table_name),'IsMsShipped' ) = 0
and objectproperty(object_id(table_name),'TableHasPrim aryKey') = 0
Using this script you can find them, add the necessary PKs and then add them
to your publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the answer. I have a doubt. I have to write this script in query
analyzer after creating the publication? Also , some tables will not have a
primary key. is there no way these tables can be replicated?
Thanks for any help.
pmud
"Paul Ibison" wrote:

> It seems you're using transactional replication and the key with a cross
> sign means that this table can't be replicated because it doesn't have a
> primary key. This script will list all these tables:
> select * from information_schema.tables
> where table_type = 'base table'
> and objectproperty(object_id(table_name),'IsMsShipped' ) = 0
> and objectproperty(object_id(table_name),'TableHasPrim aryKey') = 0
> Using this script you can find them, add the necessary PKs and then add them
> to your publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||This script is to identify those tables requiring a PK and which don't have
one. The PK is mandatory for transactional replication. Otherwise you could
use merge or snapshot for those extra tables. Have a look in books on line
(BOL) for more details of the differences between these types of
replication - this is a good starting point.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul...That was helpful
pmud
"Paul Ibison" wrote:

> This script is to identify those tables requiring a PK and which don't have
> one. The PK is mandatory for transactional replication. Otherwise you could
> use merge or snapshot for those extra tables. Have a look in books on line
> (BOL) for more details of the differences between these types of
> replication - this is a good starting point.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Creating a primary key in a trigger

I'd like to create a primary key value (incremental) within a trigger and set it in a primary key column.

Any idea anyone? Do I define my trigger as a On INSERT, Instead of INSERT? I tried both but it doesn't seem I'm doing things right.

You can use the identity column. Is there any different logic you are using for incremnetal primary key?

|||

Not quite sure what you're trying to do here- are you not able to use IDENTITY as your auto-incrementing primary key field?

I think if you *really* want to do this you may do something like this:

Code Snippet

create trigger bbbb on bb
instead of insert
as

declare @.int int

select @.int = MAX(col1) from bb

insert into bb (col1, col2, col3)
select @.int+1, col2, col3

from inserted

HTH!

Creating a primary key as a non clustered index

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

It appears to me from the code above that you actually created the clustered index on the Id field.|||

As rottengeek noticed, you are creating the clustered index on column [Id], but even if you create it on column [code], does not expect any specific order if you are not using the "order by" clause in your "select" statement. That is the only way to assure a specific order.

Quaere Verum - Clustered Index Scans - Part I

http://www.sqlmag.com/articles/index.cfm?articleid=92886&

Quaere Verum - Clustered Index Scans - Part II

http://www.sqlmag.com/articles/index.cfm?articleid=92887&

Quaere Verum - Clustered Index Scans - Part III

http://www.sqlmag.com/articles/index.cfm?articleid=92888&

AMB

|||

you are correct, so i have modified it to have the clustered index on the Code field.

Hi,

I have created a very simple table. Here is the script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]

GO

CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Code]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO

The records that i added are:

Id Code

1 a
2 b
3 aa
4 bb

Now when i query like

Select * from IndexTable

I expect the results as:

Id Code

1 a
3 aa
2 b
4 bb

as i have the clustered index on column Code.

But i m getting the results as:

Id Code

1 a
2 b
3 aa
4 bb

as per the primary key order that is a non clustered index.

Can anyone explain why it is happening?

Thanks

Nitin

creating a FK on existing tables

hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?

product :

product_id
report_id
name

report :

report_id
dateR

i want to create a FK on product.report_id, and ON DELETE CASCADE

thank you--Creating table with same structure (Primary key)]
CREATE TABLE [A] (
[report_id] [varchar] (10) ,
[dateR] [Datetime],
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[report_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
--Inserting data from report table
INSERT INTO A
SELECT * FROM report

--Dropping table report
DROP TABLE report
GO
--Renaming A table as report table
EXEC sp_rename 'A','report'
GO
--Caution: Changing any part of an object name
--could break scripts and stored procedures.

--This will create a FK in product table

ALTER TABLE products WITH NOCHECK
ADD CONSTRAINT exd_check FOREIGN KEY
(
[report_id]
) REFERENCES [report] (
[report_id]
) ON DELETE CASCADE|||genial !

thanks a lot

Creating a Dimension Table from a 3-key table

Hi All,

I have a situation with a table that was created for a transactional

system with a 3 columns key. The table is similar to the following:

country state city description
1 12 21 City A from country 1 and state 12
1 13 21 City A from country 1 and state 13
2 14 22 City B from country 2 and state 14
2 15 22 City B from country 2 and state 15

Now I'm trying to create a dts package that would allow me to build a

city dimension table with unique codes (keys) for each city. What kind of

transformation should I use to translate the old codes (based on the

country-state-city key) into the new ones and preserving the data

integrity?

Thanks,

Ignaciodoesn't that defeat the purpose of building a cube?

What's it going to be for?

How are you going to go after the data?|||This cube is going to show sales history since 2002. The table where data is being pulled could contain data as the following:

cust_id year country state city amount_cash amount_credit
525 2002 1 12 21 8500 3200
714 2002 1 13 21 3250 775

Let's say I create a fact table with fk and measures only. If I would like to know city totals, it looks like cities sharing the same id will be aggregated, when in fact they shouldn't. And in the other hand, cities that are geographically shared by different states will add up correctly. Maybe I need to further analyze this, but what first puzzled me was the city table with no unique id.

Thanks for your thoughts!

Ignacio

Creating a copy of a record

What is the simplest way to create a copy of a row?
The table in question has a primary key that is an identity field, which
will obviously need to have a different value, but otherwise I want to be
able to create a row that is identical to another one.
AFAIK Select Into only copies rows into a new table.
I could obviously retrieve each field from the original row (bar the primary
key) and INSERT a new record with this information, but since there are a
fair few columns, I wondered if there might be a simpler one-line SQL
instruction that will do the job...
Thanks in advance
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Think again. Why would you ever want to duplicate a row in a table?
(even with a different IDENTITY key).
In principle:
INSERT INTO YourTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE /* some row */ ;
However, this ought to fail on a key violation because IDENTITY should
never be the only key of a table. Rethink your requirement and your
table design.
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
pk INT NOT NULL PRIMARY KEY,
col CHAR(1),
col1 INT
)
INSERT INTO #Test VALUES (1,'a',20)
--Copy Row
INSERT INTO #Test
SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
col,col1 FROM #Test WHERE pk=1 --change to variable
SELECT * FROM #test
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23cT1aMdvFHA.3688@.tk2msftngp13.phx.gbl...
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the
> primary key) and INSERT a new record with this information, but since
> there are a fair few columns, I wondered if there might be a simpler
> one-line SQL instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127214710.180280.324430@.g43g2000cwa.googlegroups.com...
> Think again. Why would you ever want to duplicate a row in a table?
> (even with a different IDENTITY key).
> In principle:
> INSERT INTO YourTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE /* some row */ ;
> However, this ought to fail on a key violation because IDENTITY should
> never be the only key of a table. Rethink your requirement and your
> table design.
>
I can see where you are coming from, but I'm afraid my requirement is
genuine & valid.
The table in question hold orderlines. In this example an orderline is being
closed off, but a new copy of the line is to be added to the order.
Therefore, I hoped to copy original orderline (with a different PK
obviously) and then Update the original to close it off. [Obviously this is
a simplistic explanation).
As you can see, in this case the table design is fine. And the requirement,
IS the requirement. But how we satisfy the requirement is the question...
and hence my post.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> CREATE TABLE #Test
> (
> pk INT NOT NULL PRIMARY KEY,
> col CHAR(1),
> col1 INT
> )
> INSERT INTO #Test VALUES (1,'a',20)
> --Copy Row
> INSERT INTO #Test
> SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
> col,col1 FROM #Test WHERE pk=1 --change to variable
> SELECT * FROM #test
Uri,
Thanks for this.
Q. Will this still work if my PK is an Identity field?
Chris|||> As you can see, in this case the table design is fine.
I don't see that. There are serious problems with tables that have only
an IDENTITY key. The consequences of storing duplicate data make it
very difficult (perhaps impossible in some conditions) to validate and
integrate data without logical keys. Also, there are practical
programming issues in TSQL because of the way IDENTITY values are
assigned in multiple row INSERTs.
Putting that aside, why store redundant data? If the row is going to be
modified anyway as you have said then why not insert the modified row
instead of duplicating an existing one and incurring the overhead of an
extra update? Seems like you are paying a high price in integrity and
performance for no discernable benefit.
David Portas
SQL Server MVP
--|||CJM
I think David has
already answered this question , however you are free to try it.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:u4WSrhdvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Uri,
> Thanks for this.
> Q. Will this still work if my PK is an Identity field?
> Chris
>|||> I can see where you are coming from, but I'm afraid my requirement is
> genuine & valid.
> The table in question hold orderlines. In this example an orderline is
> being closed off, but a new copy of the line is to be added to the order.
WHY? What is different about the row? Why does it need to be deleted and
re-inserted?
A|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127217457.000463.24290@.g44g2000cwa.googlegroups.com...
> I don't see that. There are serious problems with tables that have only
> an IDENTITY key. The consequences of storing duplicate data make it
> very difficult (perhaps impossible in some conditions) to validate and
> integrate data without logical keys. Also, there are practical
> programming issues in TSQL because of the way IDENTITY values are
> assigned in multiple row INSERTs.
>
Well I'll have to reserve judgement until I have digested all this... (which
I will).
What alternative would you offer instead of using an identity field?
I've not noticed any significant issues with Identity fields, but I'm always
open to improving my knowledge and my techniques...

> Putting that aside, why store redundant data? If the row is going to be
> modified anyway as you have said then why not insert the modified row
> instead of duplicating an existing one and incurring the overhead of an
> extra update? Seems like you are paying a high price in integrity and
> performance for no discernable benefit.
>
What data is redundant? The original record? No, not so. It remains an
essential part of the order. It would take too long to truly put this into
the right context, but suffice to say that the new row is NOT replacing the
old row. Both will co-exist and both are essential.
I've adapted the INSERT INTO statement that you suggested, and I now have
one command that achieves what I need. I don't see how it could get any more
efficient.|||-- if ur table has huge no of columns, u can try
select * into #t from tbl_name where ...
insert into tbl_name
select * from #t
drop table #t
Rakesh
"CJM" wrote:

> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the prima
ry
> key) and INSERT a new record with this information, but since there are a
> fair few columns, I wondered if there might be a simpler one-line SQL
> instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
Ivan
Ivan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>
|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>
|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>
|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
IvanIvan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>

Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
IvanIvan
Take a look at this example posted by Steve Kass long time ago
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> CREATE TABLE dupNulls (
> pk int identity(1,1) primary key,
> X int NULL,
> nullbuster as (case when X is null then pk else 0 end),
> CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
> )
> INSERT INTO dupNulls(X) VALUES (1)
> INSERT INTO dupNulls(X) VALUES (NULL)
> INSERT INTO dupNulls(X) VALUES (NULL)
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 1 WHERE pk = 2
> GO
> SELECT pk, X, nullbuster FROM dupNulls
> UPDATE dupNulls SET X = 2 WHERE pk = 2
> SELECT pk, X, nullbuster FROM dupNulls
> DROP TABLE dupNulls
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> My table on the SQL 2000 Server has a filed Id which is a (clustered)
>> primary key.
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
>> How can I manage this constraint with help of SQL Server Enterprise
>> manager direct on the table or with help of Quer Analyzer?
>> Thanks
>> Ivan
>|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> My table on the SQL 2000 Server has a filed Id which is a (clustered)
>> primary key.
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
>> How can I manage this constraint with help of SQL Server Enterprise
>> manager direct on the table or with help of Quer Analyzer?
>> Thanks
>> Ivan
>

Creating a common table expression--temporary table--using TSQL??

Using SQL against a DB2 table the 'with' key word is used to
dynamically create a temporary table with an SQL statement that is
retained for the duration of that SQL statement.
What is the equivalent to the SQL 'with' using TSQL? If there is not
one, what is the TSQL solution to creating a temporary table that is
associated with an SQL statement? Examples would be appreciated.
Thank you!!On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

I believe there is such a thing in SQL Server 2005, but not in any earlier
versions.|||You did not say what version of MSSQL you are on so I will assume 2000.
This is straight from the TSQL books.

Temporary Tables
SQL Server supports temporary tables. These tables have names that
start with a number sign (#). If a temporary table is not dropped when
a user disconnects, SQL Server automatically drops the temporary table.
Temporary tables are not stored in the current database; they are
stored in the tempdb system database.
There are two types of temporary tables:
Local temporary tables
The names of these tables begin with one number sign (#). These tables
are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These
tables are visible to all connections. If the tables are not dropped
explicitly before the connection that created them disconnects, they
are dropped as soon as all other tasks stop referencing them. No new
tasks can reference a global temporary table after the connection that
created it disconnects. The association between a task and a table is
always dropped when the current statement completes executing;
therefore, global temporary tables are usually dropped soon after the
connection that created them disconnects.
Many traditional uses of temporary tables can now be replaced with
variables that have the table data type.

Example
create table #TempTable (col1 varchar(10), col2 bit)
insert into #TempTable values('asdf', 1)
select * from #TempTable
select * into #TempTable2 from #TempTable
select * from #TempTable2
drop table #TempTable
drop table #TempTable2

You can just about anything with a temp table that you can with a
normal table, including indexes.

HTH
Paul|||Yes, but I think he wanted to associate the statement with the name, a
physical table - like a temporary view.

On 28 Dec 2004 07:19:13 -0800, "Paul" <stpaul_71@.yahoo.com> wrote:

>You did not say what version of MSSQL you are on so I will assume 2000.
>This is straight from the TSQL books.
>Temporary Tables
>SQL Server supports temporary tables. These tables have names that
>start with a number sign (#). If a temporary table is not dropped when
>a user disconnects, SQL Server automatically drops the temporary table.
>Temporary tables are not stored in the current database; they are
>stored in the tempdb system database.
>There are two types of temporary tables:
>Local temporary tables
>The names of these tables begin with one number sign (#). These tables
>are visible only to the connection that created them.
>Global temporary tables
>The names of these tables begin with two number signs (##). These
>tables are visible to all connections. If the tables are not dropped
>explicitly before the connection that created them disconnects, they
>are dropped as soon as all other tasks stop referencing them. No new
>tasks can reference a global temporary table after the connection that
>created it disconnects. The association between a task and a table is
>always dropped when the current statement completes executing;
>therefore, global temporary tables are usually dropped soon after the
>connection that created them disconnects.
>Many traditional uses of temporary tables can now be replaced with
>variables that have the table data type.
>
>
>Example
>create table #TempTable (col1 varchar(10), col2 bit)
>insert into #TempTable values('asdf', 1)
>select * from #TempTable
>select * into #TempTable2 from #TempTable
>select * from #TempTable2
>drop table #TempTable
>drop table #TempTable2
>
>You can just about anything with a temp table that you can with a
>normal table, including indexes.
>
>HTH
>Paul|||On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:

>Using SQL against a DB2 table the 'with' key word is used to
>dynamically create a temporary table with an SQL statement that is
>retained for the duration of that SQL statement.
>What is the equivalent to the SQL 'with' using TSQL? If there is not
>one, what is the TSQL solution to creating a temporary table that is
>associated with an SQL statement? Examples would be appreciated.
>Thank you!!

Hi Randi,

I don't know if it's exactly the same as the DB2 version (probably not),
but SQL Server supports derived table expressions. Example (from BOL):

USE pubs
GO
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >Using SQL against a DB2 table the 'with' key word is used to
> >dynamically create a temporary table with an SQL statement that is
> >retained for the duration of that SQL statement.
> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >one, what is the TSQL solution to creating a temporary table that is
> >associated with an SQL statement? Examples would be appreciated.
> >Thank you!!
> Hi Randi,
> I don't know if it's exactly the same as the DB2 version (probably not),
> but SQL Server supports derived table expressions. Example (from BOL):
> USE pubs
> GO
> SELECT ST.stor_id, ST.stor_name
> FROM stores AS ST,
> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> FROM sales
> GROUP BY stor_id
> ) AS SA
> WHERE ST.stor_id = SA.stor_id
> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Hugo, a common table expression, provided by the WITH
clause, is defined in Standard SQL (beginning with SQL:1999)
and is implemented in SQL Server 2005. Semantically, the WITH
clause is similar to defining one or more views whose scope and
extent is the enclosed query. Factoring out and naming these
common subexpressions in a query is meant to aid readability,
conciseness, maintainability, and even efficiency. There are cases
when a derived table is a perfectly good alternative, however,
when that derived table is used multiple times in the query a
common table expression becomes handy.

Taking the BOL example from above, imagine you wanted to
rank stores in decreasing order by number of distinct titles. Using
WITH, one could write (admittedly, in this case a view is a reasonable
choice too):

WITH DistinctTitles (stor_id, title_count) AS
(SELECT stor_id, COUNT(DISTINCT title_id)
FROM sales
GROUP BY stor_id)
SELECT T1.stor_id, T1.title_count,
COUNT(DISTINCT T2.title_count) AS stor_rank
FROM DistinctTitles AS T1
INNER JOIN
DistinctTitles AS T2
ON T2.title_count >= T1.title_count
GROUP BY T1.stor_id, T1.title_count;

It's also through the WITH clause that we can define recursive queries. This
is where WITH truly shines.

--
JAG|||Hi John,

Thanks for your explanation. I had heard that WITH would be introduced in
SQL Server 2005; unfortunately, I'll have to wait a little longer before
I'll get a chance to actually play with it. (I don't have a spare system
lying around that I can use to safely toy with beta software).

It does look promising, though. I'm sure I'll really get to like this
feature once I have it available!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
>> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
>>
>> >Using SQL against a DB2 table the 'with' key word is used to
>> >dynamically create a temporary table with an SQL statement that is
>> >retained for the duration of that SQL statement.
>> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >one, what is the TSQL solution to creating a temporary table that is
>> >associated with an SQL statement? Examples would be appreciated.
>> >Thank you!!
>>
>> Hi Randi,
>>
>> I don't know if it's exactly the same as the DB2 version (probably not),
>> but SQL Server supports derived table expressions. Example (from BOL):
>>
>> USE pubs
>> GO
>> SELECT ST.stor_id, ST.stor_name
>> FROM stores AS ST,
>> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
>> FROM sales
>> GROUP BY stor_id
>> ) AS SA
>> WHERE ST.stor_id = SA.stor_id
>> AND SA.title_count = (SELECT COUNT(*) FROM titles)
>>
>> Best, Hugo
>> --
>>
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>Hi Hugo, a common table expression, provided by the WITH
>clause, is defined in Standard SQL (beginning with SQL:1999)
>and is implemented in SQL Server 2005. Semantically, the WITH
>clause is similar to defining one or more views whose scope and
>extent is the enclosed query. Factoring out and naming these
>common subexpressions in a query is meant to aid readability,
>conciseness, maintainability, and even efficiency. There are cases
>when a derived table is a perfectly good alternative, however,
>when that derived table is used multiple times in the query a
>common table expression becomes handy.
>Taking the BOL example from above, imagine you wanted to
>rank stores in decreasing order by number of distinct titles. Using
>WITH, one could write (admittedly, in this case a view is a reasonable
>choice too):
>WITH DistinctTitles (stor_id, title_count) AS
> (SELECT stor_id, COUNT(DISTINCT title_id)
> FROM sales
> GROUP BY stor_id)
>SELECT T1.stor_id, T1.title_count,
> COUNT(DISTINCT T2.title_count) AS stor_rank
>FROM DistinctTitles AS T1
> INNER JOIN
> DistinctTitles AS T2
> ON T2.title_count >= T1.title_count
>GROUP BY T1.stor_id, T1.title_count;
>It's also through the WITH clause that we can define recursive queries. This
>is where WITH truly shines.

Just curious - can the scope of a WITH be more than one query? An entire
stored procedure, for instance?|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >>
> >> >Using SQL against a DB2 table the 'with' key word is used to
> >> >dynamically create a temporary table with an SQL statement that is
> >> >retained for the duration of that SQL statement.
> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >> >one, what is the TSQL solution to creating a temporary table that is
> >> >associated with an SQL statement? Examples would be appreciated.
> >> >Thank you!!
> >>
> >> Hi Randi,
> >>
> >> I don't know if it's exactly the same as the DB2 version (probably not),
> >> but SQL Server supports derived table expressions. Example (from BOL):
> >>
> >> USE pubs
> >> GO
> >> SELECT ST.stor_id, ST.stor_name
> >> FROM stores AS ST,
> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> >> FROM sales
> >> GROUP BY stor_id
> >> ) AS SA
> >> WHERE ST.stor_id = SA.stor_id
> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> >>
> >> Best, Hugo
> >> --
> >>
> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
> >Hi Hugo, a common table expression, provided by the WITH
> >clause, is defined in Standard SQL (beginning with SQL:1999)
> >and is implemented in SQL Server 2005. Semantically, the WITH
> >clause is similar to defining one or more views whose scope and
> >extent is the enclosed query. Factoring out and naming these
> >common subexpressions in a query is meant to aid readability,
> >conciseness, maintainability, and even efficiency. There are cases
> >when a derived table is a perfectly good alternative, however,
> >when that derived table is used multiple times in the query a
> >common table expression becomes handy.
> >Taking the BOL example from above, imagine you wanted to
> >rank stores in decreasing order by number of distinct titles. Using
> >WITH, one could write (admittedly, in this case a view is a reasonable
> >choice too):
> >WITH DistinctTitles (stor_id, title_count) AS
> > (SELECT stor_id, COUNT(DISTINCT title_id)
> > FROM sales
> > GROUP BY stor_id)
> >SELECT T1.stor_id, T1.title_count,
> > COUNT(DISTINCT T2.title_count) AS stor_rank
> >FROM DistinctTitles AS T1
> > INNER JOIN
> > DistinctTitles AS T2
> > ON T2.title_count >= T1.title_count
> >GROUP BY T1.stor_id, T1.title_count;
> >It's also through the WITH clause that we can define recursive queries. This
> >is where WITH truly shines.
> Just curious - can the scope of a WITH be more than one query? An entire
> stored procedure, for instance?

No, a WITH clause encloses a single query expression and can be used
anywhere a query is used, e.g., in defining a view.

--
JAG|||On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <jag@.acm.org> wrote:

>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
>> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
>>
>> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
>> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
>> >>
>> >> >Using SQL against a DB2 table the 'with' key word is used to
>> >> >dynamically create a temporary table with an SQL statement that is
>> >> >retained for the duration of that SQL statement.
>> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
>> >> >one, what is the TSQL solution to creating a temporary table that is
>> >> >associated with an SQL statement? Examples would be appreciated.
>> >> >Thank you!!
>> >>
>> >> Hi Randi,
>> >>
>> >> I don't know if it's exactly the same as the DB2 version (probably not),
>> >> but SQL Server supports derived table expressions. Example (from BOL):
>> >>
>> >> USE pubs
>> >> GO
>> >> SELECT ST.stor_id, ST.stor_name
>> >> FROM stores AS ST,
>> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
>> >> FROM sales
>> >> GROUP BY stor_id
>> >> ) AS SA
>> >> WHERE ST.stor_id = SA.stor_id
>> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
>> >>
>> >> Best, Hugo
>> >> --
>> >>
>> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
>>> >Hi Hugo, a common table expression, provided by the WITH
>> >clause, is defined in Standard SQL (beginning with SQL:1999)
>> >and is implemented in SQL Server 2005. Semantically, the WITH
>> >clause is similar to defining one or more views whose scope and
>> >extent is the enclosed query. Factoring out and naming these
>> >common subexpressions in a query is meant to aid readability,
>> >conciseness, maintainability, and even efficiency. There are cases
>> >when a derived table is a perfectly good alternative, however,
>> >when that derived table is used multiple times in the query a
>> >common table expression becomes handy.
>>> >Taking the BOL example from above, imagine you wanted to
>> >rank stores in decreasing order by number of distinct titles. Using
>> >WITH, one could write (admittedly, in this case a view is a reasonable
>> >choice too):
>>> >WITH DistinctTitles (stor_id, title_count) AS
>> > (SELECT stor_id, COUNT(DISTINCT title_id)
>> > FROM sales
>> > GROUP BY stor_id)
>> >SELECT T1.stor_id, T1.title_count,
>> > COUNT(DISTINCT T2.title_count) AS stor_rank
>> >FROM DistinctTitles AS T1
>> > INNER JOIN
>> > DistinctTitles AS T2
>> > ON T2.title_count >= T1.title_count
>> >GROUP BY T1.stor_id, T1.title_count;
>>> >It's also through the WITH clause that we can define recursive queries. This
>> >is where WITH truly shines.
>>
>> Just curious - can the scope of a WITH be more than one query? An entire
>> stored procedure, for instance?
>No, a WITH clause encloses a single query expression and can be used
>anywhere a query is used, e.g., in defining a view.

Darn - I thought this would finally be a tool for removing SQL code
duplication within stored procedures. Does SQL Server 2005 offer some other
new feature to do this?|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:5kr4t0l92t2pcit2rbgpa7c9naiutb7853@.4ax.com...
> On Wed, 29 Dec 2004 08:40:27 GMT, "John Gilson" <jag@.acm.org> wrote:
> >"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> >news:h8q4t09uig7tn0lcro8ocjf6od0f6om854@.4ax.com...
> >> On Tue, 28 Dec 2004 16:44:04 GMT, "John Gilson" <jag@.acm.org> wrote:
> >>
> >> >"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> >> >news:euv2t01l92752odj1ikd51a0v7shq3en6s@.4ax.com...
> >> >> On 28 Dec 2004 07:07:49 -0800, randi_clausen@.ins.state.il.us wrote:
> >> >>
> >> >> >Using SQL against a DB2 table the 'with' key word is used to
> >> >> >dynamically create a temporary table with an SQL statement that is
> >> >> >retained for the duration of that SQL statement.
> >> >> >What is the equivalent to the SQL 'with' using TSQL? If there is not
> >> >> >one, what is the TSQL solution to creating a temporary table that is
> >> >> >associated with an SQL statement? Examples would be appreciated.
> >> >> >Thank you!!
> >> >>
> >> >> Hi Randi,
> >> >>
> >> >> I don't know if it's exactly the same as the DB2 version (probably not),
> >> >> but SQL Server supports derived table expressions. Example (from BOL):
> >> >>
> >> >> USE pubs
> >> >> GO
> >> >> SELECT ST.stor_id, ST.stor_name
> >> >> FROM stores AS ST,
> >> >> (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
> >> >> FROM sales
> >> >> GROUP BY stor_id
> >> >> ) AS SA
> >> >> WHERE ST.stor_id = SA.stor_id
> >> >> AND SA.title_count = (SELECT COUNT(*) FROM titles)
> >> >>
> >> >> Best, Hugo
> >> >> --
> >> >>
> >> >> (Remove _NO_ and _SPAM_ to get my e-mail address)
> >> >> >Hi Hugo, a common table expression, provided by the WITH
> >> >clause, is defined in Standard SQL (beginning with SQL:1999)
> >> >and is implemented in SQL Server 2005. Semantically, the WITH
> >> >clause is similar to defining one or more views whose scope and
> >> >extent is the enclosed query. Factoring out and naming these
> >> >common subexpressions in a query is meant to aid readability,
> >> >conciseness, maintainability, and even efficiency. There are cases
> >> >when a derived table is a perfectly good alternative, however,
> >> >when that derived table is used multiple times in the query a
> >> >common table expression becomes handy.
> >> >> >Taking the BOL example from above, imagine you wanted to
> >> >rank stores in decreasing order by number of distinct titles. Using
> >> >WITH, one could write (admittedly, in this case a view is a reasonable
> >> >choice too):
> >> >> >WITH DistinctTitles (stor_id, title_count) AS
> >> > (SELECT stor_id, COUNT(DISTINCT title_id)
> >> > FROM sales
> >> > GROUP BY stor_id)
> >> >SELECT T1.stor_id, T1.title_count,
> >> > COUNT(DISTINCT T2.title_count) AS stor_rank
> >> >FROM DistinctTitles AS T1
> >> > INNER JOIN
> >> > DistinctTitles AS T2
> >> > ON T2.title_count >= T1.title_count
> >> >GROUP BY T1.stor_id, T1.title_count;
> >> >> >It's also through the WITH clause that we can define recursive queries. This
> >> >is where WITH truly shines.
> >>
> >> Just curious - can the scope of a WITH be more than one query? An entire
> >> stored procedure, for instance?
> >No, a WITH clause encloses a single query expression and can be used
> >anywhere a query is used, e.g., in defining a view.
> Darn - I thought this would finally be a tool for removing SQL code
> duplication within stored procedures. Does SQL Server 2005 offer some other
> new feature to do this?

The idea behind the common table expression in a WITH clause is that
it doesn't act like a macro but is instead evaluated to a virtual table
that is used in each place where it's referenced in the enclosed query.
So in a stored procedure one might use a temp table or table variable
to store an intermediate result in lieu of such an animal. Nothing exciting
I'm afraid.

--
JAG|||John Gilson (jag@.acm.org) writes:
> The idea behind the common table expression in a WITH clause is that it
> doesn't act like a macro but is instead evaluated to a virtual table
> that is used in each place where it's referenced in the enclosed query.
> So in a stored procedure one might use a temp table or table variable
> to store an intermediate result in lieu of such an animal. Nothing
> exciting I'm afraid.

Nah, the current implementation appears to be quite macro-like, at least
for non-recursive queries.

When I look at the query plan for the query below, the CTE is computed
many times. For this query a temp table or a table variable would be
a much better alternative.

CREATE TABLE prodreport (id int NOT NULL,
product1 int NOT NULL,
product2 int NULL,
product3 int NULL,
CONSTRAINT pk_report PRIMARY KEY(id))
go
INSERT prodreport (id, product1)
SELECT PurchaseOrderID, MIN(ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail
GROUP BY PurchaseOrderID
go
-- This is the query of the show.
WITH temp (id, productid, rowno) AS
(SELECT PurchaseOrderID, ProductID,
rowno = (SELECT COUNT(*)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p2
WHERE p1.PurchaseOrderID = p2.PurchaseOrderID
AND p1.ProductID >= p2.ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1)
UPDATE prodreport
SET product1 = t1.productid,
product2 = t2.productid,
product3 = t3.productid
FROM prodreport r
JOIN temp t1 ON t1.id = r.id
AND t1.rowno = 1
LEFT JOIN temp t2 ON t2.id = r.id
AND t2.rowno = 2
LEFT JOIN temp t3 ON t3.id = r.id
AND t3.rowno = 3

SELECT * FROM prodreport
go
DROP TABLE prodreport
go

I should that Umachandar Jaychandran, a former SQL Server MVP, rewrote
the query in this way:

WITH top_3_prods(id, productid, rowno) AS
(
SELECT PurchaseOrderID, ProductID,
ROW_NUMBER() OVER(PARTITION BY PurchaseOrderId
ORDER BY ProductID)
FROM AdventureWorks.Purchasing.PurchaseOrderDetail p1
) ,
pvt_top_3_prods (id, product1, product2, product3) AS
(
SELECT id, [1], [2], [3]
FROM top_3_prods
PIVOT (min(ProductId) for rowno in ( [1], [2], [3] )) as pv
)
UPDATE prodreport
SET product1 = t1.product1,
product2 = t1.product2,
product3 = t1.product3
FROM prodreport r
JOIN pvt_top_3_prods t1 ON t1.id = r.id

There's a whole fireworks of new T-SQL features in that one!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a Auto Increment which contains Numbers & Text

Hi,
Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-
ABC000001
ABC000002
ABC000003
Etc, Etc
I am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?
Regards,
BrettI would just create a view that exposes the alpha you want plus the IDENTITY column, formatted as you wish.|||

Douglas' solution is a horrible hack. It is not scalable (what happens when you want "BCD" and "ABC") and requires having two keys -- the "real" key and the "identity" key.
You were on the right track with a stored procedure. I don't know what you're trying to model ... so I'm going with Accounts.
Your proc should look like:
PROCEDURE Create_New_Account ( @.Account_Number CHAR(8) OUT, @.Salesperson_Number CHAR(5), (... other required fields) )
I'd recommend pre-creating your identifiers, and doing a SELECT / DELETE out of the Account_Numbers table. If that won't fly, just SELECT MAX(Account_Number) from your Accounts table, parse out the string and increment the number part.

|||

I would disagree with the "Horrible hack" characterization. If there was a column [Prefix] and a column with the identity, the [Prefix] could be whatever you want. I saw a reference to Auto Increment and was thinking about Access rather than SQL Server (yes, of course I realize the post was on the SQL Server forum).

If you can ensure all access to inserting data is through the stored procedures, then of course use that. method.

What is being attempted (adding meaning of some sort to an identifier) is not often a wonderful thing.

|||You're right; it's ideal to have to have both parts of the key in the table instead of combining it into one.
But I won't concede to "Horrible Hack" being a mischaraterization. Everytime I've seen this done ... it's a horrible hack ... here's my favorite example ...
Order_Numbers were defined something as : (Order_Date + Order_Seq) + XOR Parity Shift. This was to ensure order_nums were not predictable (from end users) and were verifiable.
The programmer who implemented this requirement used this schema:
Orders_Base = TABLE (Order_ID, Order_Date, Order_Seq, ...)
Orders = VIEW( SELECT complex_generator_fn( Order_Date, Order_Seq) as Order_Num, Order_Date, ... )
Guess how fun that got when they actually used the system to place orders. And tried to query on the non-indexed Order_Num.
They first implemented decode logic in the Middle tier to get Seq + Date. Surprise surprise, that didn't quite work out so well either.
Final iteration (and still as it exists today):
Orders_Base = TABLE( Order_ID, Order_Date, Order_Seq )
Order_Numbers = TABLE( Order_ID, Order_Number )
Oh, and warehousing and analytics is a COMPLETE nightmare.

Create View

how to create view in sql server 2005 that recognize Identity And Primary key of base tableI don't understand what the requirement is, over any normal view. See CREATE VIEW in Books Online perhaps? Why is this related to SQL Server Integration Services, the title of this forum?|||

SELECT COLS.table_schema
,COLS.Table_name
,COLS.COLUMN_NAME
,cols.CONSTRAINT_NAME
,ac.IS_identity
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
JOIN sys.all_columns ac
ON OBJECT_NAME(ac.object_id) = COLS.table_name
AND ac.name=COLS.COLUMN_NAME
WHERE CONS.CONSTRAINT_TYPE LIKE 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

as i wrote this pretty quick and didnt test it fully.. using it is on your own risk ;)

Guldmann, platon.dk