Concatenate field based on unique id. (Follow up)

thanks for your earlier reply.

If i want to order the IDs by a Timestamp column in descending order how do i do it. I couldn't do it in Inner query.
right now it gives in random order. Is there any other way to get it?

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, createTS, count(*) as seq
from your_table as t1 join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment, createTS
order by createTS desc -> gives error
) as t3
group by t3.id;

Got it working. I used 'top 100 percent" and used the ORDER BY in inner query.
Thanks.|||Note that using TOP 100 PERCENT is still not guaranteed to work. It depends on the query plan and even more so in SQL Server 2005. The use of ORDER BY clause is specific to a scope only and in your example to the derived table. Generally, you should not rely on the order in which the rows are processed in a SELECT statement. You should basically consider a SELECT statement source as an unordered set of rows. In your example, you can achieve the results by changing the condition:

t2.Comment <= t1.Comment

to

t2.CreateTs <= t1.CreateTs

This assumes that time stamp value is unique per id and this would guarantee that the sequence number is based on sorting the values in ascending order. You can incorporate additional conditions to handle matching time stamps and different comments. As I said before, doing these type of operations in SQL is not the right approach. These can be done very easily on the client side and with less work / assumptions.

composite primary keys versus composite unique indexes

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

One item that is not always immediately apparent for this type of scenario has to do with clustering. Is your primary key also presently your clustered index? If the answer is yes and you change to a primary key based on an identity column and leave the CLUSTERED / NOT CLUSTERED aspect of this primary key to be defaulted then any query that still accesses data based on your current primary key will experience a new performance problem -- these accesses will now also require a bookmark lookup.

The good news is that this is might not grieve you for most OLTP activity, but it is likely to impact some reports and perhaps some record lookups; however, this is rarely enough of a drawback to preclude the change your are considering. Just file keep this in mind and if some reports or lookups become slower there is a chance that this modification might be the reason.

The bigger problem usually is modifying tables that reference your table with foreign key constraints. You have two alternatives: First, you can replicate the identity column into the foreign table and now use this value as the foreign key reference; however, if you do this you should also drop the columns that references the old foreign key columns of the table that you are changing. The second alternative is to continue to reference your modified table by what has now become the UNIQUE constraint rather than the primary key. This works and I have used this a number of times but it can be unsavory. Also, if you choose this alternative you need to know the that REFERENCES clause of the foreign key constraint will now need to explicitly list the columns of the UNIQUE constraint or your FK constraint will not compile.

Also, you might have some reports, functions, or stored procedures that filter based on one of these columns in one of your foreign tables. Changing the foreign key reference might be an impact here also.

The moral of this second problem is that you want to know all tables that have foreign key constraints to the table you are considering modifying. There might be a lot of hidden additional work if your table is referenced from a couple of dozen other tables.

Composite key "NOT IN" query?

I have a table with a composite key formed by the unique combination of columns w, x, y, z

I'm trying to write an INSERT statement along the following lines

INSERT INTO myTable

(SELECT w, x, y, z FROM someTable) t1

WHERE (this is the part I'm stumped on - where the unique combination of w, x, y, z is NOT in myTable already)

Help would be appreciated. Can you use the NOT IN keyword on composite values?

Can you use the NOT IN keyword on composite values?

Yes,but that is expensive.

FromQuery SQL Server Performance Tuning Tips

But If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

Use EXISTS or NOT EXISTS|||

INSERT INTO MyTable

SELECT t1.w,t1.x,t1.y,t1.z

FROM someTable t1

LEFT JOIN MyTable t2 ON t1.w=t2.w and t1.x=t2.x and t1.y=t2.y and t1.z=t2.z

WHERE t2.w IS NULL

This should also work:

INSERT INTO MyTable

SELECT w,x,y,z

FROM someTable

EXCEPT

SELECT w,x,y,z

FROM MyTable

Composite clustered index - column order

Want to check my thinking with you folks...

I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.

Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000

C3 is effectively number of seconds since 01/01/1970.

The usage of the table is typically, insert a row, do something else, then update it.

Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.

My thinking is that this composite index is better ordered C1,C2,C3.

My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.

Welcome your thoughts.

What are the data types of these columns? If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits. That usually happens with varchars that are updated to a larger value for example. You are usually better off to have a narrow clustered index.

What are you trying to accomplish here? Are you worried about SELECT performance, INSERT/UPDATE performance, or about index size and maintenance?

If C3 is being updated a lot, you might be better off to have the clustered index on C1, C2, and then have a non-clustered index on C3.

|||

"What are the data types of these columns"

char(4),Char(4) and int

"If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits"

Together the 3 columns provide unique key, and none of the columns are updated. The page splitting aspect I'm considering is, if the first column in the clustered is effectively an identity (so the next value inserted can only ever be bigger than the last), does this bias the inserts to one side of the tree - page splits being necessary there, because a fill factor spreads the free space throughout the tree?

"You are usually better off to have a narrow clustered index"

Yes. I appreciate that, because it gets tagged onto all non-clustered indexes. Let's assume that space isn't an issue.

Looking for best pewrformance for select \ insert & update. Index size & maint not an issue.

Thanks

Complicated join query - how is this done?

At least this is complicated for me. I hope it's not trivial for you
experts!
Accounts table has Acct_Number (unique), Client_ID, and Rep_Num. The
rep num is the number that identifies the registered representative(s)
on the account. (The client ID, of course, points to the client's name,
address, and other stuff in another table.)
The Rep_Nums table has Rep_Num (not unique) and Rep_ID. Each rep number
can map to many rep IDs. Many acccounts are assigned to a "split rep"
number where two (or more) individual "rep persons" share the work of
servicing the account and share the commissions.
The Rep_Names table has Rep_ID (unique), Rep_FName, and Rep_LName with
the rep-person's first and last name.
So far, straightforward.
Now, when we display account information, we usually don't need to see
the names of all reps assigned to the rep number on the account -- any
one rep-person's first and last name is enough information for internal
display purposes (in case we need to call one of the reps on an account
for some reason).
Now I figured out one way to do this -- I created a Rep_View that groups
the rep_nums table by rep number, and picks the smallest rep_id for each
one, and returns that rep's first and last name.
So when I want to display account information along with the name of one
of the reps on the account, I use the rep view instead of the underlying
tables.
But::: The rep name information comes from external data sources that
we have no control over, and the rep's first and last names are null for
many of the rep_IDs in the rep_names table. We're trying to fill in the
missing information, but it's a slow process, and in the meantime, we
need to show reports.
Of course, sometimes a rep number points to two rep IDs, and one of
those rep IDs has a first and last name in the table and the other does
not.
You might see where this is leading -- I need to display account
numbers, client IDs (I'll get the client names from another table), rep
number, and ONE of the first name/last name pairs from the rep names
table, picking any rep first name/last name pair that's not null for the
rep number.
How is this done? The only way I can think of involves creating an on-
the-fly computed column that holds rep first name concatenated with rep
last name, and pick the min or the max of that (for each rep number) to
try to get a non-null rep name pair. Then I might want to un-
concatenate that rep's name to get back to separate rep first and last
name, and that's messy.
And I don't like creating on-the-fly computed columns that concatenate
varchar fields.
Of course, all sets of rep first name/rep last names for any rep number
might be null too, and if so, we need to show null or "Unknown" for the
rep's name.
Comments are appreciated!
David WalkerSelect Acct_Number, Client_ID,
Min(Rep_FName + ' ' + Rep_LName) RepName
From Accounts A
Left Join (Rep_Nums N Join Rep_Names R
On R.Rep_ID = N.Rep_ID)
On N.Rep_Num = A.Rep_Num
And R.Rep_FName Is Not Null
And R.Rep_LName Is Not Null
"DWalker" wrote:

> At least this is complicated for me. I hope it's not trivial for you
> experts!
> Accounts table has Acct_Number (unique), Client_ID, and Rep_Num. The
> rep num is the number that identifies the registered representative(s)
> on the account. (The client ID, of course, points to the client's name,
> address, and other stuff in another table.)
> The Rep_Nums table has Rep_Num (not unique) and Rep_ID. Each rep number
> can map to many rep IDs. Many acccounts are assigned to a "split rep"
> number where two (or more) individual "rep persons" share the work of
> servicing the account and share the commissions.
> The Rep_Names table has Rep_ID (unique), Rep_FName, and Rep_LName with
> the rep-person's first and last name.
> So far, straightforward.
> Now, when we display account information, we usually don't need to see
> the names of all reps assigned to the rep number on the account -- any
> one rep-person's first and last name is enough information for internal
> display purposes (in case we need to call one of the reps on an account
> for some reason).
> Now I figured out one way to do this -- I created a Rep_View that groups
> the rep_nums table by rep number, and picks the smallest rep_id for each
> one, and returns that rep's first and last name.
> So when I want to display account information along with the name of one
> of the reps on the account, I use the rep view instead of the underlying
> tables.
> But::: The rep name information comes from external data sources that
> we have no control over, and the rep's first and last names are null for
> many of the rep_IDs in the rep_names table. We're trying to fill in the
> missing information, but it's a slow process, and in the meantime, we
> need to show reports.
> Of course, sometimes a rep number points to two rep IDs, and one of
> those rep IDs has a first and last name in the table and the other does
> not.
> You might see where this is leading -- I need to display account
> numbers, client IDs (I'll get the client names from another table), rep
> number, and ONE of the first name/last name pairs from the rep names
> table, picking any rep first name/last name pair that's not null for the
> rep number.
> How is this done? The only way I can think of involves creating an on-
> the-fly computed column that holds rep first name concatenated with rep
> last name, and pick the min or the max of that (for each rep number) to
> try to get a non-null rep name pair. Then I might want to un-
> concatenate that rep's name to get back to separate rep first and last
> name, and that's messy.
> And I don't like creating on-the-fly computed columns that concatenate
> varchar fields.
> Of course, all sets of rep first name/rep last names for any rep number
> might be null too, and if so, we need to show null or "Unknown" for the
> rep's name.
> Comments are appreciated!
>
> David Walker
>|||David,
You have posted here several times, right? How about following
www.aspfaq.com/5006?
Anith|||"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:D58B52E9-D083-4510-86CF-986F3981F7B5@.microsoft.com:

> Select Acct_Number, Client_ID,
> Min(Rep_FName + ' ' + Rep_LName) RepName
> From Accounts A
> Left Join (Rep_Nums N Join Rep_Names R
> On R.Rep_ID = N.Rep_ID)
> On N.Rep_Num = A.Rep_Num
> And R.Rep_FName Is Not Null
> And R.Rep_LName Is Not Null
>
That helps, thanks. I still need to get the rep names out in separate
First and Last name fields; I can put a delimiter other than space in
there and split them again, I suppose.
Thanks.
David Walker
> "DWalker" wrote:
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in news:OT91gVpJFHA.2980
@.TK2MSFTNGP10.phx.gbl:

> David,
> You have posted here several times, right? How about following
> www.aspfaq.com/5006?
>
Sorry, I meant to add the DDL at the bottom, but I simplified some of the
fields (there are lots of fields that don't contribute to the problem) and
I was looking for techniques. I'll post the DDL next time.
David Walker|||"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:D58B52E9-D083-4510-86CF-986F3981F7B5@.microsoft.com:

> Select Acct_Number, Client_ID,
> Min(Rep_FName + ' ' + Rep_LName) RepName
> From Accounts A
> Left Join (Rep_Nums N Join Rep_Names R
> On R.Rep_ID = N.Rep_ID)
> On N.Rep_Num = A.Rep_Num
> And R.Rep_FName Is Not Null
> And R.Rep_LName Is Not Null
That doesn't work.
Column 'Accounts.ACCT_NUMBER' is invalid in the select list because it is
not contained in an aggregate function and there is no GROUP BY clause.
And the same message for Client_ID.
I see that you're trying to pick one of the advisor names where they are
not null, but without grouping, it doesn't work.
David|||On Fri, 18 Mar 2005 09:41:32 -0800, DWalker wrote:

>"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
>news:D58B52E9-D083-4510-86CF-986F3981F7B5@.microsoft.com:
>
>That doesn't work.
>Column 'Accounts.ACCT_NUMBER' is invalid in the select list because it is
>not contained in an aggregate function and there is no GROUP BY clause.
>And the same message for Client_ID.
>I see that you're trying to pick one of the advisor names where they are
>not null, but without grouping, it doesn't work.
>David
Hi David,
I think CBretana intended to add
GROUP BY Acct_Number, Client_ID
at the end of the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo.
David
Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
news:87cm31dchmap2uano4sab114ba0fu36thq@.
4ax.com:

> On Fri, 18 Mar 2005 09:41:32 -0800, DWalker wrote:
>
> Hi David,
> I think CBretana intended to add
> GROUP BY Acct_Number, Client_ID
> at the end of the query.
> Best, Hugo

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