Concatenate int & var char - SQL

Hi,

I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar.

But i am receiving the error:

'Conversion failed when converting the varchar value ',' to data type int.

The SQL I am trying to use is:

select game_no + ',' + team_name as match
from result

Thanks

As the error message says you can concatenate similar datatype values and use CONVERT or CAST functions otherwise to make them simialr.

selectConvert(Varchar,game_no)+','+ team_nameas match

from result

|||

Yep. Why the parser is so stupid that it assumes the presence of one number means all other exprssions must evaluate to a number, rather than the other way around, is a mystery to me.

concatenate a string within a loop from a temp table

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

An easy way (but slow if you have a big table) to do this is to use a cursor. Bellow is a fully functional example.

Hope it helps!

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

DECLARE @.MyResult TABLE (PK Int, SumOfPockets Varchar(4000))

DECLARE @.PK Int

DECLARE @.MyString Varchar(4000)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR

SELECT PK FROM @.MyTable GROUP BY PK

OPEN cMyTable

FETCH NEXT FROM cMyTable INTO @.PK

WHILE @.@.FETCH_STATUS=0

BEGIN

SET @.MyString = ''

SELECT @.MyString = @.MyString + pocket FROM @.MyTable WHERE PK = @.PK

INSERT INTO @.MyResult(PK, SumOfPockets) VALUES (@.PK, @.MyString)

FETCH NEXT FROM cMyTable INTO @.PK

END

CLOSE cMyTable

DEALLOCATE cMyTable

SELECT * FROM @.MyResult

|||

Doru,

This looks like a great solution although i have a very huge table and i ran out of resources when i tried to implement your solution. I was hopping i can do it with some loops and temp tables. do you have any other ideas.

Emad

|||i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.|||

Emadkb wrote:

i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.

If you're running on SQL Server 2005, you can use VARCHAR(MAX) and NVARCHAR(MAX) variables in your string concatenation.

|||we are still on SQL 2000|||

Hi Emad,

If Varchar(8000) is not big enough then you'll have to use Text datatype in your destination table. This will slow down the whole thing even more :-(.

A solution that should work, but which is very slow, is:

---

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

CREATE TABLE #MyResult (PK Int, SumOfPockets Text)
DECLARE @.PK Int, @.pocket Varchar(10), @.prev_PK Int
DECLARE @.ptrColText Varbinary(16)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR
SELECT PK, pocket FROM @.MyTable

OPEN cMyTable
FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.PK = @.prev_PK
UPDATETEXT #MyResult.SumOfPockets @.ptrColText NULL 0 @.pocket
ELSE
BEGIN
INSERT INTO #MyResult(PK, SumOfPockets)
VALUES (@.PK, @.pocket)

SELECT @.ptrColText = TEXTPTR(SumOfPockets)
FROM #MyResult WHERE PK = @.PK

SET @.prev_PK = @.PK
END

FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

END
CLOSE cMyTable
DEALLOCATE cMyTable

SELECT * FROM #MyResult

---

|||

This looks exactly like i want. Excellent work. I will try it and let you know how it goes.

Emad

Concat int with string

Hi,

I am trying to concat an int with an nvarchar in my select:

select distinct(id + name)
from load
where name not like '%test%'

but I am getting an error saying:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '灪愭楮敭' to a column of data type int.

My question is: how do you concat int and string in MS SQL Server. The equivalent in Oracle would be ||, I think.

Thanks for any help.

Mongodid you try this --

select distinct(cast(id as nvarchar) + name) ...

Computed columns

Hi,

Consider the following example

create table sample

(col1 int,

col2 int ,

col3 AS col1 + col2) PERSISTED NOT NULL)

basically col3 is a computed column. Now when ever a row in col1 or col2 is updated the computed column will reflect the new value. how does this happen in the background. does this use row level triggers or what other mechanism is used to maintain col3 - computed column

the value does not exist by default

when you select a record and you included the calculated column

the server reevaluates everything which

is one of the great disadvantage of the computed column.

computation is being done all over again when you select from this column

|||I have marked it PERSISTED ...meaning that the column is saved in the database. I think what you are talking about is the computed column without using the PERSISTED key word.|||

sorry i wasn't aware of that new feature

any way

Their values are updated when any columns that are part of their calculation change

|||I would say magic :) Seriously, it would be done at a physical implementation level below what we have access to, much like values in an index get maintained. I think if you thought of it sort of like a row-level trigger it wouldn't be "wrong," but it is not through any mechanism that we have direct access to for sure.|||the reason why i posted the question is : If i use a lot of computed columns in my database, will it cause any kind of performance problems. so far what ever i have read about computed columns, no where it is mentioned that using computed columns may cause performance problems. Please let me know if you have any information.|||I think the question is more about how you need the data. There will be a performance hit, whether you persist them, or not. The difference will be based on whether you modify data more, or read it more. If it is a frequently used column (or might be) try persisting it. If it doesn't slow you down too much then that would be the best idea. Unless your formula is tremendously complex, I doubt you will even notice.|||

one good thing in answering in this forum is that you

got to learn new things. anyway here's what BOL has to say

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The SQL Server 2005 Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.

In terms of performance therefore persisted columns, performs better than non-persisted. Except of course when we hard talking of harddisk consumption. persisted column may even outrun a column-trigger solution and the first is easier to maintain over the later.

|||Thanks for all your replies.

Compute sum of count(*) with group by

Hi,
Given the following table and test data:
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (5,1,'2005-01-01 15:15','2005-01-01 15:20')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
the following query lists only the spid's with non-unique spid's and
their respective counts:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
I'm new to SQL and am having difficulty with a couple of things:
1. Modify the above query to compute the grand total for the Count, or
indeed a separate SQL statement to return just the grand total (= 6 in
this example).
2. This is the big challenge :). Taking the grouping returned by the
above query, write a query/stored procedure which looks for records
with identical spId's and the endtime of one spid equal to the
startTime of another. With the above test data, recordIds 3, 5, and 2,
4, 6 match this criteria.
Thanks very much for any help with this.1. Use a derived table construct:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total ) ;
2. Not sure if your requirements are clear since . Something like:
SELECT recordId, spId, ...
( SELECT MIN( t2.startTime )
FROM tbl t2 WHERE t2.spId = t1.spId
AND t2.startTime >= t1.endtime )
FROM tbl t1
ORDER BY t1.spid, startTime ;
If, not please post the sample resultset for the dataset you posted.
Anith|||Anith Sen wrote:
> 1. Use a derived table construct:
> SELECT SUM( total )
> FROM ( SELECT spid, COUNT(*)
> FROM tbl
> GROUP BY spid
> HAVING COUNT(*) > 1 ) D ( spid, total ) ;
>
Thanks. What does the 'D' mean above?

> 2. Not sure if your requirements are clear since . Something like:
> SELECT recordId, spId, ...
> ( SELECT MIN( t2.startTime )
> FROM tbl t2 WHERE t2.spId = t1.spId
> AND t2.startTime >= t1.endtime )
> FROM tbl t1
> ORDER BY t1.spid, startTime ;
> If, not please post the sample resultset for the dataset you posted.
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (5,1,'2005-01-01 14:33','2005-01-01 15:20')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
(Sorry, no wonder it wasn't clear as there was mistake in my original
test data. I've corrected the data above and put records with the same
spId together to make the grouping more obvious.)
So, from the above test data the expected results contain 2 sets of
matching data:
1. recordIds 3 and 5 because they have the same spId (1) and the
endTime of recordId 3 is the same as the startTime of recordId 5.
2. recordIds 2, 4 and 6 because they have the same spId (2) and the
endTime of recordId 2 is the same as the startTime of recordId 4; the
endTime of 4 is the same as the startTime of 6.
I hope that makes sense now. cheers,|||On 11 Nov 2005 09:30:25 -0800, "J Williams"
<johnwilliams_esquire@.hotmail.com> wrote:
>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
If that does the job, great, otherwise you can always store the
results of the first query in a table an do further summations against
it.
J.|||>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
Thanks, but that doesn't give the expected result. The basic SELECT:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
returns:
spid Count
1 3
2 3
The grand total of Count in the above resultset is 6 and the SQL posted
earlier by Anith Sen gives this result:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total )|||>> What does the 'D' mean above?
D in the query stands for an alias for the derived table ( some folks
explicitly use AS keyword before the alias as well. )
Can you post the sample resultset here ( as you'd want to see on the QA
results pane ).
Anith|||Anith Sen wrote:

> Can you post the sample resultset here ( as you'd want to see on the QA
> results pane ).
First recordId, Second recordId, spId, endTime, startTime
3 5 1 2005-01-01 14:33 2005-01-01 14:33
2 4 2 2005-01-01 14:00 2005-01-01 14:00
4 6 2 2005-01-01 15:15 2005-01-01 15:15
The resultset shows pairs of 'matching' records, which is slightly
different (and better) to how I first envisioned it.
Thanks.|||This is one way of getting it:
SELECT MAX( t1.recordid ),
t2.recordid, t1.spid, t1.endtime
FROM test t1
INNER JOIN test t2
ON t1.spId = t2.spId
AND t1.endTime = t2.starttime
GROUP BY t1.spid, t2.recordid, t1.endtime ;
Anith|||That's excellent, thanks.

Compound Primary Key - order not as expected

Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?John Rivers wrote:
> Hello,
> if you create this table:
> create table hello (
> int a
> , int b
> constraint pk_hello primary key clustered ( a, b )
> )
> and then insert the following records
> a,b
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
> and then do
> select a,b from hello
> the output seems to be:
> a,b
> 1,1
> 2,1
> 3,1
> 1,2
> 2,2
> 3,2
> 1,3
> 2,3
> 3,3
> which is wrong and (i think) is reflecting the actual index order
> and physical order on disk

This is not wrong at all. As long as you do not have an "ORDER BY"
clause the RDBMS is free to return records in *any* order.

> it should be:
> a,b
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
> i have tested this on a table with 500,000 records
> and sure enough if you declare the clustered primary key fields in
> reverse order:
> constraint pk_hello primary key clustered ( b, a )
> two things happen:
> - the select with no order by returns the records in the expected order

Again: you have to adjust your expectations.

> - queries relying on that order run MUCH FASTER
> has anyone else seen / noticed this?

Yes.

Cheers

robert|||Order is not guaranteed unless you include an ORDER BY. This is by design.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"John Rivers" <first10@.btinternet.com> wrote in message
news:1146048739.469710.138210@.e56g2000cwe.googlegr oups.com...
Hello,

if you create this table:

create table hello (
int a
, int b
constraint pk_hello primary key clustered ( a, b )
)

and then insert the following records

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

and then do

select a,b from hello

the output seems to be:

a,b
1,1
2,1
3,1
1,2
2,2
3,2
1,3
2,3
3,3

which is wrong and (i think) is reflecting the actual index order
and physical order on disk

it should be:

a,b
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

i have tested this on a table with 500,000 records

and sure enough if you declare the clustered primary key fields in
reverse order:

constraint pk_hello primary key clustered ( b, a )

two things happen:

- the select with no order by returns the records in the expected order
- queries relying on that order run MUCH FASTER

has anyone else seen / noticed this?|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

You need to read a book on RDBMS; you are still locked into a file
system mind set.|||Hello,

when a clustered index is present the records *are* physically ordered
on disk to match the index

that is the whole point of a clustered index

and by default a select statement with no ORDER BY will always return
data in the order of the clustered index (when present)

this can easily be proved by watching the Execution Plan

the issue i am trying to highlight concerns the order of the records on
disk when a *compound* clustered index is present

i have seen cases when it is not as expected

maybe you can enjoy reading that RDBMS book :-)

best wishes,

john|||John Rivers wrote:
> Hello,
> when a clustered index is present the records *are* physically ordered
> on disk to match the index
> that is the whole point of a clustered index
> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)
Um. No. I've seen it return them out of order with only a few hundred
rows. As soon as the table is occupying more than one page, the query
optimizer *can* decide to produce a parallel plan. You'll see the
result as chunks of output which are in clustered index order, but no
deterministic ordering between the chunks. e.g. it'll look like:

1
2
3
4
5
11
12
13
14
15
6
7
8
9
10

The *only* way to guarantee the order of output is to put an order by
clause on your select statement.

Damien|||John Rivers wrote:
> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)

Not true at all. As Joe says, tables are not logically ordered. There
is no guarantee that any queries will match the physical order on disk
or in a clustered index.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On 28 Apr 2006 05:41:26 -0700, John Rivers wrote:

>Hello,
>when a clustered index is present the records *are* physically ordered
>on disk to match the index
>that is the whole point of a clustered index

Hi John,

Correct.

>and by default a select statement with no ORDER BY will always return
>data in the order of the clustered index (when present)

Incorrect. Damien already pointed out the risk of parallellism.

Another potential issue is an optimization technique MS employs called
"piggybacking" - if a query on another connection is in the middle of a
tbale scan on the table you need, the DB will use the values coming in
for your query as well, then (when the first query's table scan is
finished) restart the scan from start up to where it started to
piggyback. The results would be like 6 - 7 - 8 - 9 - 10 - 1 - 2 - 3 - 4
- 5

This is almost impossible to reproduce in a test environment, but it
MIGHT happen intermittently in a heavily used production DB. Tough lluck
if your app expects the rows to be in order, even without ORDER BY.

>the issue i am trying to highlight concerns the order of the records on
>disk when a *compound* clustered index is present
>i have seen cases when it is not as expected

How did you "see" those cases? Using a query reallly doesn't prove
anything. Did you issue DBCC PAGE commands to inspect the actual
contents of the index and data pages?

--
Hugo Kornelis, SQL Server MVP|||John Rivers (first10@.btinternet.com) writes:
> when a clustered index is present the records *are* physically ordered
> on disk to match the index
> that is the whole point of a clustered index

Actually, they are ordered if you follow the page links. But if pages
are in disorder, the physical order on disk may be yet another one.

> and by default a select statement with no ORDER BY will always return
> data in the order of the clustered index (when present)

No. This may have been true by chance for SQL Server up version 6.5. It is
definitely not correct for SQL 7 and later.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your knowledgable answers

I will check out DBCC PAGE

composite indexes - a subtle question

I have a table like so
CREATE TABLE [dbo].[account] (
[pty_id] [int] NOT NULL ,
[sort_code] [int] NOT NULL ,
[account_no] [int] NOT NULL ,
[account_open_dt] [datetime] NULL ,
[account_close_dt] [datetime] NULL ,
[account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[category_code] [smallint] NULL ,
[prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
) ON [PRIMARY]
as you can see there's a primary key clustered index on account_no and
sort_code
I can link it up to any other table efficiently which also has sort_code and
account_no as part of a single index (primary or otherwise)
ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
etc...
and that will work fine...
Here's my question
if account_no is part or the sort_code/Account_no composite key (and
therefore index)
could I link another table to only the account_no column and still get some
indexing benefit
i.e can you join on a single column of a multiple column index and still get
some help from that composite index or does that composite index only work a
s
a single entity and in fact you need to create an extra nonclustered index
for that column on top of the other index it's participating in to get the
benefit of indexing on that column...
phew
I hope this makes sense
any help would be great appreciated
Regards and thanks in advance,
CharlesACharles,
When joining to the first column of a multi-column index, some
of the same optimizations are available--for example, a merge
join (if the other table also has a supporting index) or nested loop
joins with index ss. Depending on the exact query, the two-column
index may be just as useful as a separate one-column index or a
little less useful (typically because the two-column index takes up
more data pages because of duplications in the first column and the
presence of the second column even when duplicates are few), but it
should still help in most cases. The only real way to be sure is
to create the additional index and measure the performance.
Steve Kass
Drew University
CharlesA wrote:

>I have a table like so
>CREATE TABLE [dbo].[account] (
> [pty_id] [int] NOT NULL ,
> [sort_code] [int] NOT NULL ,
> [account_no] [int] NOT NULL ,
> [account_open_dt] [datetime] NULL ,
> [account_close_dt] [datetime] NULL ,
> [account_nm] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [market_sector] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [market_segment] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [category_code] [smallint] NULL ,
> [prime] [char] (1) COLLATE Latin1_General_CI_AS NULL
> CONSTRAINT PK_ACCOUNT PRIMARY KEY CLUSTERED (sort_code, account_no)
> ) ON [PRIMARY]
>
>
>as you can see there's a primary key clustered index on account_no and
>sort_code
>I can link it up to any other table efficiently which also has sort_code an
d
>account_no as part of a single index (primary or otherwise)
>ON (a.sort_code= b.sortCode) ANd (a.account_no = b.account_no)
>etc...
>and that will work fine...
>Here's my question
>if account_no is part or the sort_code/Account_no composite key (and
>therefore index)
>could I link another table to only the account_no column and still get some
>indexing benefit
>i.e can you join on a single column of a multiple column index and still ge
t
>some help from that composite index or does that composite index only work
as
>a single entity and in fact you need to create an extra nonclustered index
>for that column on top of the other index it's participating in to get the
>benefit of indexing on that column...
>phew
>I hope this makes sense
>any help would be great appreciated
>Regards and thanks in advance,
>CharlesA
>|||Charles,
Can you recreate your PK as (account_no, sort_code)? What are the
cardinalities of these 2 columns?|||Charles,
I know you'd like an immediate answer, but it might really help to
watch Kimberly Tripp's (SQL Server MVP) presentation at the following
website. I'm in the process of designing indexes and it was incredibly
helpful to watch the presentation. She keeps it simple but I finally
got it...
http://www.microsoft.com/uk/technet...aspx?videoid=29
Other links:
http://msevents.microsoft.com/CUI/E...e=en-U
S
http://www.sqlskills.com/blogs/kimb...
3-a58ba7b1265e
(The presentation took me a while to download and it is an hour long
but totally worth it.)|||Hi,
Whether you get performance benifit or not depends on the location of the
column in the order of the index key.
You want to join Account_no from tbl1 with Account no in tbl2 with
sort_code/Account_no as an index.
If the left most column in the index is sort_code i.e
Index(sort_code,Account_no) , you don't find any use of the index, it will
still go for an idex scan.
But if your index had been Index(Account_no,sort_code)
then this index is sufficient for joining Account_no alone or Account_no and
sort_code.
Hope I made sense :)|||Thanks everyone for your answers, much appreciated...
I will download that presentation when I'm at home, I've been dying to
understand indexing properly for ages now
as I say, thanks All
Regards,
CharlesA

Composite index design question

I have converted a database to support multiple clients by adding a ClientID
(int) to most of the 200 tables. This is to enable an application to use a
single database for multiple clients, separating the data via the ClientID
Where the primary key was OrderID, or ProductID, it is now ( ClientID,
OrderID) and (ClientID, ProductID) respectively.
The majority of clustered indexes are composite and have ClientID as the
first column. This of course means most of the foreign key indexes have
ClientID as the first column in the index. I even added ClientID to most
other nonclustered indexes since I know that ClientID will be in every join
and specified in the WHERE clause of every query.
It occurred to me recently that this database may have hundreds of thousands
of Orders and Products, but will likely only have 30-50 unique values of
ClientID.
This being the case, I would assume I will get better performance if the
indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
ClientID) ? The database tables are not sufficiently large at this point to
make a difference, but with 30 clients they will hit the 100K mark in a shor
t
period of time.Hi
When you create a composite index on columns SQL Server keeps statistics on
only one (first) column. So make sure that this column is selective enough.
http://www.sql-server-performance.c...ite_indexes.asp
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>|||Consider seperate non-composite indexes for ClientID and ProductID, since
this will provide more flexibility when generating an execution plan. Also,
consider not clustering on ClientID. With 50 unique clients using the
database, clustering will result in additional I/O and page fragmentation.
I generally avoid using composite columns except on clustered indexes, and I
generally avoid clustered indexes except on data warehouse tables where
there is a large amount of fairly static data that is frequently grouped or
sorted in mass for reporting purposes. There is little advantage to
clustering indexes in an OLTP database, unless it is on a bookmarked id
column like Product.ProductID.
"mikenz" <mikewnz@.newsgroups.nospam> wrote in message
news:BEA8FDD5-CD9C-4EA6-9942-DD6D4D2A6331@.microsoft.com...
>I have converted a database to support multiple clients by adding a
>ClientID
> (int) to most of the 200 tables. This is to enable an application to use
> a
> single database for multiple clients, separating the data via the ClientID
> Where the primary key was OrderID, or ProductID, it is now ( ClientID,
> OrderID) and (ClientID, ProductID) respectively.
> The majority of clustered indexes are composite and have ClientID as the
> first column. This of course means most of the foreign key indexes have
> ClientID as the first column in the index. I even added ClientID to most
> other nonclustered indexes since I know that ClientID will be in every
> join
> and specified in the WHERE clause of every query.
> It occurred to me recently that this database may have hundreds of
> thousands
> of Orders and Products, but will likely only have 30-50 unique values of
> ClientID.
> This being the case, I would assume I will get better performance if the
> indexes were rearranged to be ( ProductID, ClientID) and ( OrderID,
> ClientID) ? The database tables are not sufficiently large at this point
> to
> make a difference, but with 30 clients they will hit the 100K mark in a
> short
> period of time.
>

composite index

Hi

I have a table with a 3 column composite cluster index in sql 2005

table1 ( a uniqueidentifier, b uniqueidentifier, c int, d varchar(1000))

the composite index is on a,b,c

when I write a query

select * from table1 where a = 'asd' and b = 'afasddf'

the query analyzer uses cluster index seek

but if I use the table in a join

select * from table1 inner join table2 on table1.a = table2.a

and table1.b = 'sdsfds'

the query optimizer is using a cluster index scan on b, I can't limit the query on table1.b in a join?

thanks

Pauli

create an index on t1.b should do it.

e.g.

create table #t1(i uniqueidentifier, j uniqueidentifier, k int, primary key(i,j,k))
create table #t2(i uniqueidentifier, j uniqueidentifier, k int, primary key(i))
create index ix_ on #t1(j)
go
set showplan_text on
go

declare @.j uniqueidentifier
set @.j=newid()

select * from #t1 t1 join #t2 t2 on t1.i=t2.i and t1.j = @.j
go
set showplan_text off
go
drop table #t2,#t1

|||

paulixml wrote:

the query optimizer is using a cluster index scan on b, I can't limit the query on table1.b in a join?

Apparently in your particular case the optimizer has concluded that doing the join and then filtering the output is more expensive than first performing a table scan and filtering the intermediate output and then doing a join on a much smaller (hopefully) set.

It all really depends the existing indexes in BOTH your tables table1 and table2 (and not just the availability of the index on [a,b,c] in table1), AND, of course, on the actual data - hence, on the statistics available to the optimizer. One could easily cook up various column value distributions for your existing tables/indexes where the very same query will produce very different execution plans.

compond index and key faster/better?

In a situation where you have two tables in a hierarchy like this:

create table authors (authorid int identity (1,1))

create table books (
authorid int,
bookid int identity (1,1)
)

Is there any disadvantage to having the primary key and the clustered
index as a compound key, like this:

alter table books add constraint PK_books primary key clustered
(authored, bookid)

Normally, I would make bookid the key, but then I got to thinking, most
of the queries are going to be "select * from books where authorid =
@.@.some_authorID"

So, wouldn't a compound key and index make this a little faster?(eric.nave@.gmail.com) writes:
> create table books (
> authorid int,
> bookid int identity (1,1)
> )
> Is there any disadvantage to having the primary key and the clustered
> index as a compound key, like this:
> alter table books add constraint PK_books primary key clustered
> (authored, bookid)

That looks a little funny. Since bookid is unique, why add authorid
to the PK?

Then again, PK of a books table should probably be the ISBN, as that
is a natural key.

And there should probably be a relation table, as a book can more than
one author.

> Normally, I would make bookid the key, but then I got to thinking, most
> of the queries are going to be "select * from books where authorid =
> @.@.some_authorID"
> So, wouldn't a compound key and index make this a little faster?

Having the clustered index on authorid is probably better than clustering
on ISBN, yes. But there not really any reason to add it to the PK of
books. (In an bookauthors table that covers the many-to-many relation
between books and authors, the key would make sense.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> That looks a little funny. Since bookid is unique, why add authorid
> to the PK?

Only for the purpose of having the clustered index that way. I think
my example would have been better if I'd made it a case of, "should I
have a simple or a compond index" and left the key out of it. My
mistake.

> Then again, PK of a books table should probably be the ISBN, as that
> is a natural key.

Well, this is just an example off the top of my head. Books and
authors is just the first think I came up with.

> And there should probably be a relation table, as a book can more than
> one author.

Same response as above.

> Having the clustered index on authorid is probably better than clustering
> on ISBN, yes. But there not really any reason to add it to the PK of
> books.

I concede that there's no reason to have it in the PK. But, having a
compond clustered index seems like a good idea if you constantly want
to find all books by a given author. The books will then be returned
in bookid order.|||> But, having a
> compond clustered index seems like a good idea if you constantly want
> to find all books by a given author. The books will then be returned
> in bookid order.

Although it is likely that data will be returned in sequence by bookid, the
order is guaranteed only when bookid is specified in an ORDER BY clause. A
clustered index including bookid will facilitate efficient ordering in this
case.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<eric.nave@.gmail.com> wrote in message
news:1125527844.570677.118750@.z14g2000cwz.googlegr oups.com...
> Erland Sommarskog wrote:
>> That looks a little funny. Since bookid is unique, why add authorid
>> to the PK?
> Only for the purpose of having the clustered index that way. I think
> my example would have been better if I'd made it a case of, "should I
> have a simple or a compond index" and left the key out of it. My
> mistake.
>> Then again, PK of a books table should probably be the ISBN, as that
>> is a natural key.
> Well, this is just an example off the top of my head. Books and
> authors is just the first think I came up with.
>> And there should probably be a relation table, as a book can more than
>> one author.
> Same response as above.
>> Having the clustered index on authorid is probably better than clustering
>> on ISBN, yes. But there not really any reason to add it to the PK of
>> books.
> I concede that there's no reason to have it in the PK. But, having a
> compond clustered index seems like a good idea if you constantly want
> to find all books by a given author. The books will then be returned
> in bookid order.

Complex UPDATE

Given the 2 tables Sponsor and SponsorEvent with SponsorID on SponsorEvent
as a FK
CREATE TABLE [dbo].[Sponsor] (
[SponsorID] [int] NOT NULL ,
[SponsorCode] [char] (15) NOT NULL ,
[BrandID] [int] NOT NULL ,
[FirstDate] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sponsor] WITH NOCHECK ADD
CONSTRAINT [PK_Sponsor] PRIMARY KEY CLUSTERED
(
[SponsorID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[SponsorEvent] (
[Date] [int] NOT NULL ,
[BarbChanID] [int] NOT NULL ,
[StartTime] [int] NOT NULL ,
[AreaFlags] [smallint] NOT NULL ,
[PlatformFlags] [tinyint] NOT NULL ,
[EndTime] [int] NOT NULL ,
[SponsorID] [int] NOT NULL ,
[SponsorICodeID] [int] NOT NULL ,
[SponsorINameID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[SponsorEvent] WITH NOCHECK ADD
CONSTRAINT [PK_SponsorEvent] PRIMARY KEY CLUSTERED
(
[Date],
[BarbChanID],
[StartTime],
[AreaFlags],
[PlatformFlags]
) WITH FILLFACTOR = 90 ON [PRIMARY]
what is wrong with this syntax
update Sponsor set FirstDate = att.[Date]
from (
SELECT SponsorID,MIN([DATE]) From SponsorEvent GROUP by SponsorID)
att,Sponsor s
where s.SponsorID = att.SponsorID
Query Analyser complains of
"No column was specified for column 2 of 'att'."
I am trying to group by SponsorID on SponsorEvent, work out what the minimum
date is on SponsorEvent and update [Date] on Sponsor with this where
SponsorID is in common.
Thanks
Stephen Howe> Query Analyser complains of
> "No column was specified for column 2 of 'att'."
Isn't it "No column NAME was specified...?"
This happens when you apply a calculation on a column and don't include an
alias. The outer query doesn't see a column named "Date", it only sees the
expression MIN([Date]), which is not the same thing.
How about :
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor
INNER JOIN
(
SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att
ON s.SponsorID = att.SponsorID
Or, better yet, instead of having to run this update every single time the
SponsorEvent changes, drop the column firstdate from the sponsor table.
There is no reason to store this redundant information when you can always
get it directly from SponsorEvent.
As an aside, I recommend renaming the column [Date]. It is never a good
idea to use reserved words as column names.
A|||Sorry, forgot the s:

> FROM Sponsor
Should be
FROM Sponsor s|||Thanks for response

> Isn't it "No column NAME was specified...?"
No. Definitely not. What I wrote was copy and pasted out of QA.
There is a message in colour red just before. The full message is
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'att'.

> How about :
> UPDATE Sponsor
> SET FirstDate = att.FirstDate
> FROM Sponsor
> INNER JOIN
> (
> SELECT SponsorID, [Date] = MIN([DATE])
> FROM SponsorEvent
> GROUP by SponsorID
> ) Att
> ON s.SponsorID = att.SponsorID
Does not work. I changed text to
SET FirstDate = att.[Date]
FROM Sponsor s
and now I get
"Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'att' does not match with a table name or alias name used
in the query."

> Or, better yet, instead of having to run this update every single time the
> SponsorEvent changes, drop the column firstdate from the sponsor table.
> There is no reason to store this redundant information when you can always
> get it directly from SponsorEvent.
Yes you are right and I would like to.
But the reason why is that we have a analogous situation for tables where
Sponsor and SponsorEvent which have SponsorID in common
is reproduced with
SpotFilm and Spot which have SpotFilmID in common
SpotFilm and Sponsor have identical columns apart from the ID fields. It is
also such that if you concatenated them, the ID's are unique, no row in
common.
I say "somewhat symmetrical" because the difference is that data in
SponsorEvent is split between Spot and Slot.
Spot as a table has over 100 million rows (and gets larger per w).
Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDate
just happens to be a compromise. I know what you are saying, and I agree in
principal. At some point this year the server will be upgraded from SQL
Server 7 to 2000 and maybe the optimisation might be better.

> As an aside, I recommend renaming the column [Date]. It is never a good
> idea to use reserved words as column names.
I know. Noted. We have a table called [Break] which I am loathe to change.
Granted it is a keyword, but in the industry I am in "Break" is the most
descriptive word there is for the data it contains. They really are called
"Commercial Breaks". But I will change [Date] :-)
Stephen Howe|||What, do you have a case sensitive collation? Try using att everywhere and
no Att vs. att?
This works fine for me. You might want to provide similar DDL in the future
to make it easier for others to reproduce your scenario and test their
results. (See http://www.aspfaq.com/5006)
USE Tempdb
GO
CREATE TABLE Sponsor
(
SponsorID INT,
FirstDate SMALLDATETIME
)
GO
CREATE TABLE SponsorEvent
(
SponsorID INT,
[Date] SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT Sponsor SELECT 1, NULL
INSERT Sponsor SELECT 2, NULL
INSERT Sponsor SELECT 3, NULL
INSERT Sponsor SELECT 4, NULL
INSERT SponsorEvent SELECT 1, '20010501'
INSERT SponsorEvent SELECT 1, '20010601'
INSERT SponsorEvent SELECT 1, '20040801'
INSERT SponsorEvent SELECT 3, '20040701'
INSERT SponsorEvent SELECT 3, '20010601'
INSERT SponsorEvent SELECT 4, '20030801'
GO
UPDATE Sponsor
SET FirstDate = att.FirstDate
FROM Sponsor s
INNER JOIN
(
SELECT SponsorID, FirstDate = MIN([Date])
FROM SponsorEvent
GROUP BY SponsorID
) att
ON s.SponsorID = att.SponsorID
GO
SELECT * FROM Sponsor
GO
DROP TABLE SponsorEvent, Sponsor
GO
On 3/12/05 12:57 PM, in article ORy87zyJFHA.2716@.TK2MSFTNGP15.phx.gbl,
"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote:

> Thanks for response
>
> No. Definitely not. What I wrote was copy and pasted out of QA.
> There is a message in colour red just before. The full message is
> Server: Msg 8155, Level 16, State 2, Line 1
> No column was specified for column 2 of 'att'.
>
> Does not work. I changed text to
> SET FirstDate = att.[Date]
> FROM Sponsor s
> and now I get
> "Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'att' does not match with a table name or alias name use
d
> in the query."
>
> Yes you are right and I would like to.
> But the reason why is that we have a analogous situation for tables where
> Sponsor and SponsorEvent which have SponsorID in common
> is reproduced with
> SpotFilm and Spot which have SpotFilmID in common
> SpotFilm and Sponsor have identical columns apart from the ID fields. It i
s
> also such that if you concatenated them, the ID's are unique, no row in
> common.
> I say "somewhat symmetrical" because the difference is that data in
> SponsorEvent is split between Spot and Slot.
> Spot as a table has over 100 million rows (and gets larger per w).
> Working out MIN([Date]) for each SpotFilmID on Spot is a triple-join between
> Spot,Slot and SpotFilm, and we need to export that every Thursday. FirstDa
te
> just happens to be a compromise. I know what you are saying, and I agree i
n
> principal. At some point this year the server will be upgraded from SQL
> Server 7 to 2000 and maybe the optimisation might be better.
>
> I know. Noted. We have a table called [Break] which I am loathe to change.
> Granted it is a keyword, but in the industry I am in "Break" is the most
> descriptive word there is for the data it contains. They really are called
> "Commercial Breaks". But I will change [Date] :-)
> Stephen Howe
>
>
>|||> Sorry, forgot the s:
>
> Should be
> FROM Sponsor s
Thanks Aaaron, done it
UPDATE Sponsor
SET FirstDate = att.[Date]
FROM
(SELECT SponsorID, [Date] = MIN([DATE])
FROM SponsorEvent
GROUP by SponsorID
) Att INNER JOIN Sponsor s ON Att.SponsorID=s.SPonsorID|||> What, do you have a case sensitive collation? Try using att everywhere
and
> no Att vs. att?
I don't think we do. Anything to do with SQL Server 7 not recognising the
syntax?
Perhaps SQL Server 2000 is "improved" in that order does not matter
I changed the order putting Sponsor s last, (after the SELECT .. GROUP BY)
and at that point it recognised it.
Strange
But thanks
Stephen|||Date is not just a reserved word, it is too vague to be a valid data
element name -- date of what' Is there only one sponsor, as you
showed with a singular name? If you were writing SQL instead of a
strange dialect, would this be what you meant? It is a bad practice to
name relationship tables by concatenating names together -- always ask
if the relationship has its own name. Endorsements, sponsorships, etc.
UPDATE Sponsors
SET firstdate -- of what'
= (SELECT MIN(foobar_date)
FROM Endorsements AS E
WHERE E.sponsor_id = Sponsors.sponsor_id)
You might want to read a book on SQL and see what the standard UPDATE
syntax is.
And get a book on data modeling. Names like "SponsorICodeID" make
absolutley no sense. A code is not an identifier; it is a scalar value
on a nominal scale. Most of the rest of your DDL looks like you are
writing SQL with flags, etc. -- all the classic mistakes of someone who
does not know how to make a schema.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110772877.481289.236930@.l41g2000cwc.googlegroups.com...
> It is a bad practice to name relationship tables by concatenating
names together
Mr. Celko,
Really? I've been doing a lot of that lately (sometimes M-to-M
relationships are stumpers). Is that a part of a standard I can take
a look at? Perhaps you could provide a weblink to an article or other
work with an extensive description of why it is bad, and the process
of doing better.
Sincerely,
Chris O.|||>> [name relationship tables by concatenating names together ] I've
been doing a lot of that lately (sometimes M-to-M relationships are
stumpers). <<
Not if you start from a data model. Relationships important enough to
be modeled tend to have names -- "Marriages" instead "ManWoman' or even
worse "CivilUnions" instead of "ManMan_WomanWoman_ManWomen". A
relationship name invites the attributes that go with the relationship;
Marriages implies a wedding date, license number, etc.
Google up ISO-11179; the principle is to name a thing for what it *is*,
not for what it *does* in a particular situation, not for hopw you
build it-- i.e. this is an "automobile", not a "TiresFrameMotor"; the
whole not the parts.
an extensive description of why it is bad, and the process of doing
better. <<
Look for an entire book on SQL style about the middle of this year from
me.

Complex SQL Query.

Ok,

Table Structure :

FileProduct
===========

ID int -> Unique Key (Long Integer)
FileProduct Text -> Description of product.

FileDetails
===========

ID int -> Unique Key (Long Integer)
ProductID Text -> Relational Link into the FileProduct Table above.
Filename Text -> Name of the file.
Version Text -> Version Details of File.

PCDetails
=========

ID int -> Unique Key (Long Integer)
PCName Text -> Name of PC
FileName Text -> FileName found on PC.
Version Text -> Version Details.

Table Data (what is in each table):

FileProduct
===============
ID FileProduct
-- --
1 P1

File Details
===============
ID ProductID Filename Version
-- -- -
1 1 F1.DAT 1
2 1 F1.DAT 2
3 1 F3.DAT 2
4 1 F4.DAT 2

PCDetails
=========
ID PCName FileName Version
-- -- -
1 PC1 F1.DAT 2
2 PC1 F3.DAT 2
3 PC1 F4.DAT 2
4 PC2 F1.DAT 1
5 PC2 F3.DAT 2
6 PC2 F6.DAT 3

Ok now here is the problem. What I am trying to do is
how to make a SQL statment that will return every PCName
that has has the items in the FileProduct.

Ok here is how I would like it processed

Any file with the same name would be joined by an OR condition.

So the logic would be.

If the PC record has (F1.DAT - Version 1 OR F1.DAT - Version 2) AND (F3.DAT - Version 2)
and (F4.DAT - Version 2) then it would be a succesfull match and return PC1.
So as you can see files with the same name are ORed together and files with different
names are ANDed together.

In the PC Details this would match rows 1,2 and 3. However, PC2 would not be matched
because it does not have a match for F4.DAT.

Now a Product could have mulltiple files in it and there would be multiple products.

I figure this is possible with some magic SQL - but I can't figure it out..
My instincts say this is possible with just a SQL statement.

Any help greatly appreciated !!! :)

Thanks,

Ward.

Ok, a little tricky, but if you infer from the requirement that you want to match PCs that have matching files and versions (or some version of a file) you could do something like this

Code Snippet

select * from pcdetails a

where ([filename] in (select [filename] from filedetails) and version in (select version from filedetails where [filename] = a.filename))

and (PCName not in (select PCName from pcdetails where version not in (select version from filedetails)))

The above, given the table structures and test data you provided returns this result:

Code Snippet

ID PCName FileName Version

1 PC1 F1.DAT 2

2 PC1 F3.DAT 2

3 PC1 F4.DAT 2

Maybe this will get you kick-started. I know it doesn't solve this issue of if a PC doesn't have all the files on it, but you should be able to figure that out with a little time.

Hope this at least helps.

|||

Code Snippet

select PCName
from (
select distinct PCName
from PCDetails
) as PCDetails
where not exists ( -- where there is no...
select * from [File Details] -- ...Filename/version in File Details...
where not exists ( -- ...that does not appear (note ANDs below) ...
select *
from PCDetails as P2 -- ...in PCDetails...
where P2.PCName = PCDetails.PCName -- ...for that particular PCName
and P2.FileName = [File Details].Filename
and P2.Version = [File Details].Version
)
)

This is my guess as to what you want.

Strategy:
Select PCNames for which there is no "missing" file/version.
More specifically:
Select from among the distinct PCNames in PCDetails
those PCNames for which not one of the Filename/versions
present in File Details fails to appear for that
particular PCName in PCDetails.

Steve Kass
Drew University
http://www.stevekass.com

Complex SQL Query.

Ok,

Table Structure :

FileProduct
===========

ID int -> Unique Key (Long Integer)
FileProduct Text -> Description of product.

FileDetails
===========

ID int -> Unique Key (Long Integer)
ProductID Text -> Relational Link into the FileProduct Table above.
Filename Text -> Name of the file.
Version Text -> Version Details of File.

PCDetails
=========

ID int -> Unique Key (Long Integer)
PCName Text -> Name of PC
FileName Text -> FileName found on PC.
Version Text -> Version Details.

Table Data (what is in each table):

FileProduct
===============
ID FileProduct
-- --
1 P1

File Details
===============
ID ProductID Filename Version
-- -- -
1 1 F1.DAT 1
2 1 F1.DAT 2
3 1 F3.DAT 2
4 1 F4.DAT 2

PCDetails
=========
ID PCName FileName Version
-- -- -
1 PC1 F1.DAT 2
2 PC1 F3.DAT 2
3 PC1 F4.DAT 2
4 PC2 F1.DAT 1
5 PC2 F3.DAT 2
6 PC2 F6.DAT 3

Ok now here is the problem. What I am trying to do is
how to make a SQL statment that will return every PCName
that has has the items in the FileProduct.

Ok here is how I would like it processed

Any file with the same name would be joined by an OR condition.

So the logic would be.

If the PC record has (F1.DAT - Version 1 OR F1.DAT - Version 2) AND (F3.DAT - Version 2)
and (F4.DAT - Version 2) then it would be a succesfull match and return PC1.
So as you can see files with the same name are ORed together and files with different
names are ANDed together.

In the PC Details this would match rows 1,2 and 3. However, PC2 would not be matched
because it does not have a match for F4.DAT.

Now a Product could have mulltiple files in it and there would be multiple products.

I figure this is possible with some magic SQL - but I can't figure it out..
My instincts say this is possible with just a SQL statement.

Any help greatly appreciated !!! :)

Thanks,

Ward.

Ok, a little tricky, but if you infer from the requirement that you want to match PCs that have matching files and versions (or some version of a file) you could do something like this

Code Snippet

select * from pcdetails a

where ([filename] in (select [filename] from filedetails) and version in (select version from filedetails where [filename] = a.filename))

and (PCName not in (select PCName from pcdetails where version not in (select version from filedetails)))

The above, given the table structures and test data you provided returns this result:

Code Snippet

ID PCName FileName Version

1 PC1 F1.DAT 2

2 PC1 F3.DAT 2

3 PC1 F4.DAT 2

Maybe this will get you kick-started. I know it doesn't solve this issue of if a PC doesn't have all the files on it, but you should be able to figure that out with a little time.

Hope this at least helps.

|||

Code Snippet

select PCName
from (
select distinct PCName
from PCDetails
) as PCDetails
where not exists ( -- where there is no...
select * from [File Details] -- ...Filename/version in File Details...
where not exists ( -- ...that does not appear (note ANDs below) ...
select *
from PCDetails as P2 -- ...in PCDetails...
where P2.PCName = PCDetails.PCName -- ...for that particular PCName
and P2.FileName = [File Details].Filename
and P2.Version = [File Details].Version
)
)

This is my guess as to what you want.

Strategy:
Select PCNames for which there is no "missing" file/version.
More specifically:
Select from among the distinct PCNames in PCDetails
those PCNames for which not one of the Filename/versions
present in File Details fails to appear for that
particular PCName in PCDetails.

Steve Kass
Drew University
http://www.stevekass.com

complex SQL (for me)

Hello,

Lets look at this table :

CREATE TABLE [dbo].[TableHisto](
[Id] [int] NOT NULL,
[Week] [nvarchar](50) COLLATE French_CI_AS NULL,
[Project] [int] NOT NULL
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Identifiant d''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Id'

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Date de l''enregistrement' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Week'

GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Projet de rfrence' ,@.level0type=N'SCHEMA', @.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'TableHisto', @.level2type=N'COLUMN', @.level2name=N'Project'

It is a table where i store projects week reports.

I want to make a request to display a table with project ID in Row, Weeks in columns and either TableHisto.id or Null value in cell.

I use SQL 2005. Thanks for any help

You can use a Matrix Report type that supported by many report products

like Crystal report ,

it is easy to use by a power full wizard

|||

I cant use it becouse i need to use it as a navigation tool on web site

|||

See if this set of posts helps you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326847&SiteID=1

We did a rotation of a set like this (more generic in nature) in these posts.

|||

Your sample is very nice but i don't know how to use it.

Lets me show what I want :

set nocount on
create table Histo
(
Id varchar(10) primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-12','Internet'
union all
select '49','2006-13','Intranet'
go

select *
from Histo
go

it make this table :

Id | Week | Project
47 | 2006-12 | Internet
48 | 2006-12 | Internet
49 | 2006-13 | Intranet

And I Want to get something like that :

Project | 2006-12 | 2006-13
Internet | 47 | 49
Intranet | 48 | null

I work on it for days and dont find the solution.

Thanks a lot for any help

|||Well, this is the perfect example of the use of the PIVOT keyword in SQL Server 2005. If you're using SQL 2005 then use PIVOT. If you're using SQL 2000 then you'll have to use a case statement to accomplish this and it can be a bit cumbersome.|||Please demonstrate. I don't quite know how you will achieve what is being asked.|||nobody can help me ?|||

I found the solution :

set nocount on
create table Histo
(
Id varchar(10)primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-13','Internet'
union all
select '49','2006-12','Intranet'

go

select*
from Histo

go

SELECT Project,
[2006-12],
[2006-13]
FROM
(SELECT Project, week, id
FROM histo) s
PIVOT
(
max(id)
FOR Week IN ([2006-12],[2006-13])
) p
ORDER BY [Project]
go

drop table histo

Complex query problem, help needed

Hi All,
We have a table in our database (MS SQL) where in no primary key defined.
Table name is NAMELINK.
Structure is as follows :
NameId1 int 4 not null
Category char 5 nullable
Relation12 char 20 nullable
NameId2 int 4 not null
Relation21 char 21 nullable
Remarks char 30 nullable
Dependent smallint 2 not null

While conversion we are assuming that NameId1, NameId2 is a
composite primary key (looking at the data majorly it is unique). But
still there are some cases where it can not be unique.
Now we want a select query to fetch only those records where the
combination of NameId1, NameId2 is unique. We tried self join but
somehow it's not working.
Please help about this.

Regards,
PrashantTry this query

select * from NAMELINK inner join ( select distinct NameId1, NameId2 from NAMELINK) NL on NAMELINK.NameId1 = NL.NameId1 and NAMELINK.NameId2 = NL.NameId2

I have written the query on the fly without testing it. So test it and post your reply.

Originally posted by dahalkar_p
Hi All,
We have a table in our database (MS SQL) where in no primary key defined.
Table name is NAMELINK.
Structure is as follows :
NameId1 int 4 not null
Category char 5 nullable
Relation12 char 20 nullable
NameId2 int 4 not null
Relation21 char 21 nullable
Remarks char 30 nullable
Dependent smallint 2 not null

While conversion we are assuming that NameId1, NameId2 is a
composite primary key (looking at the data majorly it is unique). But
still there are some cases where it can not be unique.
Now we want a select query to fetch only those records where the
combination of NameId1, NameId2 is unique. We tried self join but
somehow it's not working.
Please help about this.

Regards,
Prashant|||Hi Mohamed,
Sorry to say that this is not going to work for me..

I need to remove the records which are duplicating this way:

Nameid1 Nameid2
1 2
2 1

I need just one of the following record not both..

Prashant|||Hmm... Seems to be pretty interesting problem. I had been struggling like hell to find a solution to it for about 4 hours continously. My objectives were to avoid temporary tables and avoid cursors; To solve by a single query. Unfortunately I could not avoid correlated query (in the second part of UNION keyword) which I hate because of performance reasons. If number the duplicates as u said were low then the performance should be good. Performance decreases as the number of duplicates increases. Anyway here goes the solution.

SELECT NAMELINK.* FROM NAMELINK LEFT JOIN
(
SELECT nl1.* FROM NAMELINK nl1
INNER JOIN NAMELINK nl2 on nl1.Nameid2 = nl2.Nameid1 and nl1.Nameid1= nl2.Nameid2
) MyNameLink ON Namelink.Nameid1 = MyNameLink.Nameid1 and Namelink.Nameid2 = MyNameLink.Nameid2
WHERE MyNameLink.Nameid1 IS NULL AND MyNameLink.Nameid2 IS NULL

UNION ALL

SELECT nl2.* FROM NAMELINK nl1
INNER JOIN NAMELINK nl2 on nl1.Nameid2 = nl2.Nameid1 and nl1.Nameid1= nl2.Nameid2
WHERE
nl2.Nameid1 =
( SELECT TOP 1 nl3.Nameid1 FROM NAMELINK nl3 INNER JOIN NAMELINK nl4 ON nl3.Nameid2 = nl4.nameid1 and nl3.Nameid1 = nl4.Nameid2 WHERE (nl3.Nameid1 + nl3.Nameid2) = (nl1.Nameid1 + nl1.Nameid2) ) and
nl2.Nameid2 =
( SELECT TOP 1 nl3.Nameid2 FROM NAMELINK nl3 INNER JOIN NAMELINK nl4 ON nl3.Nameid2 = nl4.nameid1 and nl3.Nameid1 = nl4.Nameid2 WHERE (nl3.Nameid1 + nl3.Nameid2) = (nl1.Nameid1 + nl1.Nameid2) )

I tested it thoroughly and validated the solution. Just copy, paste it for execution and tell me the result.

Good Luck.

Originally posted by dahalkar_p
Hi Mohamed,
Sorry to say that this is not going to work for me..

I need to remove the records which are duplicating this way:

Nameid1 Nameid2
1 2
2 1

I need just one of the following record not both..

Prashant|||Cheers!!!!
You have done it..
Thank's a lot man...

Prashant|||I would be glad if you could tell me the number of rows in the namelink table and your comments on the performance of the solution. ( I am much worried about performance in all my solutions).

Originally posted by dahalkar_p
Cheers!!!!
You have done it..
Thank's a lot man...

Prashant|||Right now i have a database with small number of records in the table.
They are around 250. The performance test can not be done on such small db.
I will let you know when it is done on a live database with lot's of records.

-Prashant|||select NameId1, NameId2 from NAMELINK group by NameId1, NameId2
having count(*) = 1

run this query - it should bring back some positive information!!!

Enjoy,

Neil de Later,
Johannesburg - South Africa

e-mail : neil@.bex.co.za

Originally posted by dahalkar_p
Hi All,
We have a table in our database (MS SQL) where in no primary key defined.
Table name is NAMELINK.
Structure is as follows :
NameId1 int 4 not null
Category char 5 nullable
Relation12 char 20 nullable
NameId2 int 4 not null
Relation21 char 21 nullable
Remarks char 30 nullable
Dependent smallint 2 not null

While conversion we are assuming that NameId1, NameId2 is a
composite primary key (looking at the data majorly it is unique). But
still there are some cases where it can not be unique.
Now we want a select query to fetch only those records where the
combination of NameId1, NameId2 is unique. We tried self join but
somehow it's not working.
Please help about this.

Regards,
Prashant|||Hi bex,

This will return all the rows in the table..
I think you have a confusion on the way the question is asked.
It's not just duplicate in both the tables.
It's combination should not repeat even vice versa.

Nameid1 Nameid2
1 2
2 1

Here i need any one record.

-Prashant|||--assume invalid PK NameId1, NameId2 <-> NameId2, NameId1

--without duplicities
select n1.*
from NAMELINK n1
where 1=(
select count(*) from NAMELINK n2 where
(n1.NameId1=n2.NameId1 and n1.NameId2=n2.NameId2)
or
(n1.NameId1=n2.NameId2 and n1.NameId2=n2.NameId1)
)
order by NameId1, NameId2

--only duplicities, correctly ordered
select n1.*
from NAMELINK n1
inner join NAMELINK n2
on n1.NameId1=n2.NameId1 and n1.NameId2=n2.NameId2
where 1<(
select count(*) from NAMELINK n2 where
(n1.NameId1=n2.NameId1 and n1.NameId2=n2.NameId2)
or
(n1.NameId1=n2.NameId2 and n1.NameId2=n2.NameId1)
)
order by
case when n1.NameId1>n1.NameId2
then n1.NameId2
else n1.NameId1
end
,case when n1.NameId1>n1.NameId2
then n1.NameId1
else n1.NameId2
end|||Hi,

How can we delete the duplicates in the similar scenario?

Regards,|||--Try this:

-- Coping source table, adding PK "Id"
select "Id"=IDENTITY(int,1,1),*
into dbo.TempNameLink
from dbo.NameLink
order by
case when NameId1>NameId2
then NameId2
else NameId1
end
,case when NameId1>NameId2
then NameId1
else NameId2
end
GO
-- accelerating by indexes on computed columns
alter table dbo.TempNameLink
add "compNameId1" as
case when n2.NameId1>n2.NameId2
then n2.NameId2
else n2.NameId1
end
,"compNameId2" as
case when n2.NameId1>n2.NameId2
then n2.NameId1
else n2.NameId2
end
GO
create unique clustered index IC_TempNameLink on TempNameLink ("compNameId1","compNameId2","Id")
GO
alter table dbo.TempNameLink
add constraint PK_TempNameLink
primary key nonclustered ("Id")
GO

--inserting unique values
--Information from Category,Relation12,Relation21,Remarks,Dependent columns in duplicities is lost.
create table dbo.NewNameLink (
NameId1 int not null
,NameId2 int not null
,Category char(5) null
,Relation12 char(20) null
,Relation21 char(21) null
,Remarks char(30) null
,Dependent smallint not null
,constraint PK_NewNameLink
primary key ( NameId1,NameId2 )
,constraint CK_NewNameLink
check ( NameId1<NameId2 )
)
GO
insert dbo.NewNameLink(NameId1,NameId2,Category,Relation1 2,Relation21,Remarks,Dependent)
select n1.NameId1,n1.NameId2,n1.Category,n1.Relation12,n1 .Relation21
,n1.Remarks,n1.Dependent
from dbo.TempNameLink n1
join (
select "Id"=min("Id")
from dbo.TempNameLink n2
group by n2."compNameId1",n2."compNameId2"
) XXX on n1."Id"=XXX."Id"|||Thank's for your help but Sorry to say , this will eliminate all the duplicates.

We want to keep one of the duplicate and eliminate the rest of the values.

I think i was not clear in my question.

Regards,|||All,

Here's the fix for the complex situation:

select "Id"=IDENTITY(int,1,1),*
into dbo.TempNameLink
from dbo.NameLink
order by
case when NameId1>NameId2
then NameId2
else NameId1
end
,case when NameId1>NameId2
then NameId1
else NameId2
end
GO

CREATE TABLE [NameLink1] (
[NameID1] [int] NOT NULL DEFAULT (0),
[Category] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT (''),
[Relation12] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT (''),
[NameID2] [int] NOT NULL DEFAULT (0),
[Relation21] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT (''),
[Remarks] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT (''),
[Dependent] [smallint] NOT NULL DEFAULT (0),
[UpdateDate] [datetime] NULL ,
[UpdatedByID] [int] NOT NULL DEFAULT (0),
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
) ON [PRIMARY]
GO

CREATE procedure CLEANLINK(@.tempID integer output) as
begin
declare @.Nameid1 integer,
@.Nameid2 integer,
@.ID integer,
@.COUNTER integer,
@.TOTAL integer
declare TEMPCURSOR cursor dynamic scroll for select N.ID, N.NameID1, N.NameID2 from TEMPNAMELINK as N
select @.TOTAL = COUNT(*) from TEMPNAMELINK
select @.COUNTER=1
open TEMPCURSOR
while @.COUNTER <= @.TOTAL
begin
fetch next from TEMPCURSOR into @.ID, @.Nameid1, @.Nameid2
Begin
IF not exists (Select 1 from namelink1 where Nameid1 = @.Nameid1 and Nameid2 = @.Nameid2)
Begin
INSERT INTO dbo.NameLink1(NameID1, Category, Relation12, NameID2, Relation21, Remarks, Dependent, UpdateDate, UpdatedByID, rowguid) SELECT NameID1, Category, Relation12, NameID2, Relation21, Remarks, Dependent, UpdateDate, UpdatedByID, rowguid from TEMPNAMELINK where nameid1 = @.Nameid1 and nameid2 = @.Nameid2 and ID = @.ID
end
end
select @.COUNTER=@.COUNTER+1
end
DEALLOCATE TEMPCURSOR
end
GO

execute cleanlink 1
go

delete from NAMELINK
go

INSERT INTO dbo.NameLink(NameID1, Category, Relation12, NameID2, Relation21, Remarks, Dependent, UpdateDate, UpdatedByID, rowguid)
SELECT NameID1, Category, Relation12, NameID2, Relation21, Remarks, Dependent, UpdateDate, UpdatedByID, rowguid from NAMELINK1
go

drop table NAMELINK1
go

DROP TABLE TEMPNAMELINK
go

DROP PROCEDURE CLEANLINK
go

This script does it all.

Thanks to all,|||--So simply

select "Id"=IDENTITY(int,1,1),NameID1, Category, Relation12, NameID2, Relation21, Remarks, Dependent, UpdateDate, UpdatedByID, rowguid
into dbo.TempNameLink
from dbo.NameLink
order by NameId1,NameId2
GO
create clustered index IC_TempNameLink on dbo.TempNameLink (NameId1,NameId2)
GO
alter table dbo.TempNameLink
add constraint PK_TempNameLink primary key ("Id")
GO
delete dbo.NameLink
insert dbo.NameLink(NameID1, Category, Relation12, NameID2, Relation21, Remarks, Dependent, UpdateDate, UpdatedByID, rowguid )
select NameID1, Category, Relation12, NameID2, Relation21, Remarks, Dependent, UpdateDate, UpdatedByID, rowguid
from dbo.TempNameLink t
join (
select "Id"=min("Id")
from dbo.TempNameLink
group by NameID1, NameID2
) XXX on t."Id"=XXX."Id"
drop table dbo.TempNameLink

complex merging query in sql server 2000

Hello,

I have a Database in a SQL Server 2000 where I have different users
tables with equal fields like this:

id (int)
email (varchar)
name (varchar)
address (varchar)
joinedon (datetime)

I want to merge all the tables in one taking rows with the same email
but taking always the more recent fields based on the 'joinedon' field.
So if I have this four different rows:

Tbl email name address joinedon
----------------------------
T1 j@.smith.com johnathan NULL 01/01/95
T2 j@.smith.com NULL barcelona street 01/01/98
T3 j@.smith.com john valencia street 01/01/97
T4 j@.smith.com john Q NULL 01/01/99

And the final row entered in the new table would be

Tbl email name address joinedon
---------------------------
new j@.smith.com john Q barcelona street 01/01/99

I am trying doing his with union statements, but i am not getting the
real merging of data. Any clue?

Thanks for your help.Considering the following DDL and sample data:

CREATE TABLE TheTable (
id int PRIMARY KEY,
email varchar(50) NOT NULL,
name varchar(50) NULL,
address varchar(50) NULL,
joinedon datetime NOT NULL,
UNIQUE (email, joinedon)
)

INSERT INTO TheTable VALUES (1,
'j@.smith.com','johnathan',NULL,'19950101')
INSERT INTO TheTable VALUES (2, 'j@.smith.com',NULL,'barcelona
street','19980101')
INSERT INTO TheTable VALUES (3, 'j@.smith.com','john','valencia
street','19970101')
INSERT INTO TheTable VALUES (4, 'j@.smith.com','john Q',NULL,'19990101')

The following query provides the expected result:

SELECT x.email, (
SELECT a.name FROM TheTable a
WHERE a.email=x.email
AND a.joinedon=(
SELECT MAX(b.joinedon) FROM TheTable b
WHERE b.email=a.email AND b.name IS NOT NULL
)
) as name, (
SELECT c.address FROM TheTable c
WHERE c.email=x.email
AND c.joinedon=(
SELECT MAX(d.joinedon) FROM TheTable d
WHERE d.email=c.email AND d.address IS NOT NULL
)
) as address,
x.joinedon
FROM (
SELECT email, MAX(joinedon) as joinedon
FROM TheTable
GROUP BY email
) x

Razvan

mrclash wrote:

Quote:

Originally Posted by

Hello,
>
I have a Database in a SQL Server 2000 where I have different users
tables with equal fields like this:
>
id (int)
email (varchar)
name (varchar)
address (varchar)
joinedon (datetime)
>
I want to merge all the tables in one taking rows with the same email
but taking always the more recent fields based on the 'joinedon' field.
So if I have this four different rows:
>
Tbl email name address joinedon
----------------------------
T1 j@.smith.com johnathan NULL 01/01/95
T2 j@.smith.com NULL barcelona street 01/01/98
T3 j@.smith.com john valencia street 01/01/97
T4 j@.smith.com john Q NULL 01/01/99
>
And the final row entered in the new table would be
>
Tbl email name address joinedon
---------------------------
new j@.smith.com john Q barcelona street 01/01/99
>
I am trying doing his with union statements, but i am not getting the
real merging of data. Any clue?
>
Thanks for your help.

Complex Delet Query .. Help me!

I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.have you tried ON DELETE CASCADE in the foreign key relationship?

i've never done this myself (cascading delete in the adjacency model hierarchy), so i'd be very interested in finding out that it works|||I have not had much SQL experience.. Im more of a C#/C++/asp.net developer! Moreover Iam deleting the records in the same table.. so there is no foriegn key or anything here.. did I miss something!?|||create table yourtable
( SECID integer
, PARENTID integer
, NAME varchar(50)
, primary key (SECID)
, constraint validparent
foreign key (PARENTID)
references yourtable (SECID)
on delete cascade
)

-- load table

delete from yourtable where SECID=8001

Edit: nope, that don't workError: Introducing FOREIGN KEY constraint 'validparent' on table 'yourtable' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (State:37000, Native Code: 6F9)
Error: Could not create constraint. See previous errors. (State:37000, Native Code: 6D6)|||you'll have to do a recursive user define function...

(working on this RUDF..)|||Originally posted by netjkus
I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.

This is a complex query. I never heard or came accross like this. The parent-->child is nesting many times. I am not sure you can do it in one SQL. Might have to use a store procedure and keep the nodes in a temp table. Delete from the main table until no records found in temp table|||Check this one - with triggers (they have to be recursive - database option)...

drop table test
create table test (id int primary key,pid int)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
insert test values(4,2)
go
select * from test
go
create trigger tr_test on test
for delete
as
if not exists(select * from deleted)
return
delete test where pid in(select id from deleted)
go
delete test where id=2-- or 1|||smasanam

I'm almost there with my RUDF...
just a few minutes more|||GOT IT GOOD

coming up !|||Create these functions

CREATE FUNCTION Trim (@.Mot Varchar(230))
RETURNS Varchar(230) AS
BEGIN
return(Rtrim(Ltrim(@.Mot)))
END

CREATE Function ListOfChildren (@.Parents Varchar(100))
RETURNS Varchar(100)
As
Begin
Declare @.List Varchar(100),
@.NewList VarChar(100),
@.TotalList VarChar(100)

set @.List=@.Parents
set @.NewList='abc'
set @.TotalList='|' + @.Parents

while @.NewList<>''
begin
set @.NewList=''
Select @.NewList = @.NewList + '|' + dbo.Trim(SectID) From Family Where PatIndex('%' +dbo.Trim(ParentID)+ '%', @.List )<>0
set @.List= @.NewList
set @.TotalList=@.List+@.TotalList
end

Return(@.TotalList)
End|||then you can do :

delete family
where patindex('%' + dbo.Trim(sectid) +'%' , dbo.listofchildren('8007'))<>0 or
patindex('%' + dbo.Trim(parentid) +'%' , dbo.listofchildren('8007'))<>0

in fact the function ListOfChildren returns for 8007 the list of :
all children (8009,8010)
all children of children (8011)
the named parent (8007)|||http://www.sqlteam.com/item.asp?ItemID=8866

Or

http://archives.postgresql.org/pgsql-sql/2002-11/msg00358.php|||my solution seems simpler than Brett's...

wooooooooo
risky saying this|||Karolyn

Thanks a ton.. Iam trying this right now...|||Originally posted by Karolyn
my solution seems simpler than Brett's...

wooooooooo
risky saying this

troublemaker, heh?

Anyway isn't

while @.NewList<>''
begin
set @.NewList=''

False right away?|||nope 'cause it's set to 'abc'

need gllasssseesss ?|||(ding ding ding)
ROUND 1

Karolyn : right strong punch
Brett : KO|||and when you do the total

Brett : 1289
Karolyn : 1

catching up !!!|||How right you are...the evaluation doesn't come until the END...|||I can't answer lots of question
so when I do
I make a big fuss out of it
(a Frenchy-of-France-Attitude that I learned here in France)|||BUT STILL...
your

code:------------------------
while @.NewList<>''
begin
set @.NewList=''
------------------------
False right away?


was a VERY Low one|||Very nice...

USE Northwind
GO

CREATE TABLE family(SECtID varchar(10), PARENTID varchar(10))
GO

INSERT INTO family(SECtID, PARENTID)
SELECT '8000', NULL UNION ALL
SELECT '8001', '8000' UNION ALL
SELECT '8002', '8000' UNION ALL
SELECT '8003', '8002' UNION ALL
SELECT '8004', '8002' UNION ALL
SELECT '8005', '8003' UNION ALL
SELECT '8006', '8003' UNION ALL
SELECT '8007', '8001' UNION ALL
SELECT '8008', '8001' UNION ALL
SELECT '8009', '8007' UNION ALL
SELECT '8010', '8007' UNION ALL
SELECT '8011', '8009'
GO

SELECT dbo.ListOfChildren(8007)
GO

DROP FUNCTION TRIM
DROP FUNCTION ListOfChildren
DROP TABLE family
GO

And I like the fact that is comes out in asceding hierarchy, with the grandfather of it all last...|||Originally posted by Karolyn
BUT STILL...
your



was a VERY Low one

Well I apologize if I offended you.

Listen, that's why I'm a scrub...I missed it...

(had nothing to do with trying to put you down...a very silly game I don't play)|||Karolyn

Thanks a ton.. It works!! You made my day!! Iam implementing it !

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Thats not working in SQL 2000. You will have to create it in compat. mode 2k.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Would you please show me a sample code?

Thanks

|||Hi,

the equivalent would be:

CREATE TABLE [dbo].[City](

[CityID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED

(

[CityID] ASC

) ON [PRIMARY]

)

GO

CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])

WITH IGNORE_DUP_KEY ON [PRIMARY]

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)

) ON [PRIMARY]

Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.

|||

Thanks for the reply.

I generated the script using SMO in the following steps:

1. In SMO, right-click on the table City, choose Modify.

2. Highlight CityID, click the "Set Primary Key" button and save the change

3. Right-click on table City, choose "Script table as", choose "Create to"....

Thanks.

|||

Hi

u can't use ignore_dup_key with constraint. u need to create index key.

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

|||

I upgraded my SQL Server 2005 to SP1.

Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.

Problem solved!!

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Thats not working in SQL 2000. You will have to create it in compat. mode 2k.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Would you please show me a sample code?

Thanks

|||Hi,

the equivalent would be:

CREATE TABLE [dbo].[City](

[CityID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED

(

[CityID] ASC

) ON [PRIMARY]

)

GO

CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])

WITH IGNORE_DUP_KEY ON [PRIMARY]

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)

) ON [PRIMARY]

Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.

|||

Thanks for the reply.

I generated the script using SMO in the following steps:

1. In SMO, right-click on the table City, choose Modify.

2. Highlight CityID, click the "Set Primary Key" button and save the change

3. Right-click on table City, choose "Script table as", choose "Create to"....

Thanks.

|||

Hi

u can't use ignore_dup_key with constraint. u need to create index key.

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

|||

I upgraded my SQL Server 2005 to SP1.

Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.

Problem solved!!