Composite Key?

I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tables

Table #1 - DanceTable
Columns: DanceID, Name, Description, Tags

Table #2 - StepsTable
Columns DanceID, StepID, longLocation, latLocation, Action, Description

Within my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step5, Step 6, Step7

What I really want is (or I think what I want is) is a composite primary key.

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step1, Step2, Step3

That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10).

I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.

A composite key is just a key made from multiple fields. In your case, it would be DanceID,StepID.

Your tables look fine, although if you make the StepID an identity field, you really don't need to store the "StepNumber". It's redundant. You can derive the "StepNumber" by the number of records that have the same DanceID and a lower StepID.

This would get you the @.StepNumber-th step:

SELECT TOP 1 *

FROM ( SELECT TOP (@.StepNumber) *

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID ASC) t1

ORDER BY t1.StepID DESC

Or get them all in order with "StepNumber":

SELECT *,ROW_NUMBER() OVER (ORDER BY StepID) As StepNumber

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID

But of course there is nothing prohibiting you from including a StepNumber field (Like in case you don't always want the steps to be renumbered, or they aren't contiguious, like step 1, 3, 5 with no step 2 or 4, etc). In this case, your primary key would be StepID, and I would create a unique index/constraint on DanceID,StepNumber.

composite key structure

I'm just looking to get suggestions as to what the best way to handle this
key structure is, in terms of performance. At present, the current pk is on
an identity value. I 'inherited' this and am sing to change post haste
for many reasons. These three columns combined equate to the primary key
from a business-perspective: tradetime,endpoint,ordernumber
Because of this, I have created a clustered compound primary key with the
columns in this order: tradetime,endpoint,ordernumber
Tradetime has the most selectivity, as it nearly always montonically
increases.
Endpoint is used first in most of the ad hoc where clauses.
All my procedures, however, are very date-specific. So this, too, puts
tradetime in the where clauses a lot.
tradetime is datetime, endpoint is varchar(8) and ordernumber is varchar(32)
hence, the thing is kind of wide. but, as i said before, it is the only
uniqueID from a business perspective. the db structure is both current and
historical. current is about 300-600K and nearly all continuous inserts,
historical averages around 38M and it's where the reporting and analysis is
done. the data is rarely ever updated in either.
So, should it be clustered or non? Are the columns ordered correctly in the
compound key? And any thoughts as to how this will impact insertion
performance?
thank you in advance,
-- LynnLynn,

Base on the this, you should already have a unique index by these three
columns. I think the key is to wide to be used as a clustered index. Remembe
r
that the key of a clustered index is used by the rest of nonclustered indexe
s.
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Lynn" wrote:
> I'm just looking to get suggestions as to what the best way to handle this
> key structure is, in terms of performance. At present, the current pk is
on
> an identity value. I 'inherited' this and am sing to change post haste
> for many reasons. These three columns combined equate to the primary key
> from a business-perspective: tradetime,endpoint,ordernumber
> Because of this, I have created a clustered compound primary key with the
> columns in this order: tradetime,endpoint,ordernumber
> Tradetime has the most selectivity, as it nearly always montonically
> increases.
> Endpoint is used first in most of the ad hoc where clauses.
> All my procedures, however, are very date-specific. So this, too, puts
> tradetime in the where clauses a lot.
> tradetime is datetime, endpoint is varchar(8) and ordernumber is varchar(3
2)
> hence, the thing is kind of wide. but, as i said before, it is the only
> uniqueID from a business perspective. the db structure is both current an
d
> historical. current is about 300-600K and nearly all continuous inserts,
> historical averages around 38M and it's where the reporting and analysis i
s
> done. the data is rarely ever updated in either.
> So, should it be clustered or non? Are the columns ordered correctly in t
he
> compound key? And any thoughts as to how this will impact insertion
> performance?
> thank you in advance,
> -- Lynn|||ok, amb, but are you suggesting i do not create a compound pk on these three
columns and that a unique index or unique constraint will suffice, or are yo
u
suggesting i should do the compound pk but that it should be non-clustered?
Lynn
"Alejandro Mesa" wrote:
> Lynn,
>
>
> Base on the this, you should already have a unique index by these three
> columns. I think the key is to wide to be used as a clustered index. Remem
ber
> that the key of a clustered index is used by the rest of nonclustered inde
xes.
> Tips on Optimizing SQL Server Clustered Indexes
> http://www.sql-server-performance.c...red_indexes.asp
>
> AMB
> "Lynn" wrote:
>|||> ok, amb, but are you suggesting i do not create a compound pk on these
> three
> columns and that a unique index or unique constraint will suffice, or are
> you
> suggesting i should do the compound pk but that it should be
> non-clustered?
Do you have related tables (like OrderDetails) that need referential
integrity back to this table? Do you want to store OrderNumber there, or do
you want to repeat the tradetime,endpoint,ordernumber combo in every related
table?|||Actually, no, there are very few related tables. It's a trading repository
that we continually load with trades. The other tables are specific to
accounts and bi, but not to the ordernumbers themselves. There is a need to
go back in a report on/query the data regularly, but it's done against the
historical table, which is structured identically.
--
Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Do you have related tables (like OrderDetails) that need referential
> integrity back to this table? Do you want to store OrderNumber there, or
do
> you want to repeat the tradetime,endpoint,ordernumber combo in every relat
ed
> table?
>
>|||Lynn,
What I meant was that an identity column as pk, does not asure that there
will not be duplicated rows by (tradetime,endpoint,ordernumber) and because
these columns, as you said, equate to the primary key from a
business-perspective then it should exists already a constraint to force the
uniqueness by these columns. The question is if this unique index should be
clustered or not?. I will also consider what Aaron is asking you for (is
there any other table that is referencing this table?). Well, there are othe
r
things to consider like if there are other columns that you use for range
queries or are used in the "group by" clause, what is more important for you
"select" or "insert" performance, etc. Take a look to the article in the
link, it can help you to understand what are the columns in your table prope
r
for a clustered index.
AMB
"Lynn" wrote:
> ok, amb, but are you suggesting i do not create a compound pk on these thr
ee
> columns and that a unique index or unique constraint will suffice, or are
you
> suggesting i should do the compound pk but that it should be non-clustered
?
> --
> Lynn
>
> "Alejandro Mesa" wrote:
>|||Yes, I understand that the identity column pk didn't prevent dupes, nor is i
t
portable at all. All it does is sequentially number the records and it has
no pertinence whatsoever to the actual data value. Except for very few
maintenace scripts, the data is never queried looking on identity value.
Hence, I'm changing it. But I'm not done yet. That's the reason for my
inquiry. I'm trying to find the best way to get us where we need to be. I
understand the three columns already imply a constraint to force the
uniqueness. But that constraint does not yet exist. I intend to drop the
existing pk and replace with one that is actually meaningful, and I feel it
should be based upon these three fields, which uniquely identify our data.
I've already done this in my dev bed as clustered, and I've actually read
that article before several times - it is what prompted me to cluster the pk
in the first place because of the remark near the bottom about wide indices.
But today i've been researching and have seen the nonclustered composite pk
referenced several times, so now I'm debating whether I've done it properly
and I thought I'd s a little advice.
--
Lynn
"Alejandro Mesa" wrote:
> Lynn,
> What I meant was that an identity column as pk, does not asure that there
> will not be duplicated rows by (tradetime,endpoint,ordernumber) and becaus
e
> these columns, as you said, equate to the primary key from a
> business-perspective then it should exists already a constraint to force t
he
> uniqueness by these columns. The question is if this unique index should b
e
> clustered or not?. I will also consider what Aaron is asking you for (is
> there any other table that is referencing this table?). Well, there are ot
her
> things to consider like if there are other columns that you use for range
> queries or are used in the "group by" clause, what is more important for y
ou
> "select" or "insert" performance, etc. Take a look to the article in the
> link, it can help you to understand what are the columns in your table pro
per
> for a clustered index.
>
> AMB
> "Lynn" wrote:
>|||>> Except for very few
maintenace scripts, the data is never queried looking on identity
value.
Hence, I'm changing it.<<
usually we need a stronger reason for changing a working system. What
are your other reasons?|||We have no unique ID. And many, many holes because of this.
--
Lynn
"AK" wrote:

> maintenace scripts, the data is never queried looking on identity
> value.
> Hence, I'm changing it.<<
> usually we need a stronger reason for changing a working system. What
> are your other reasons?
>|||> We have no unique ID. And many, many holes because of this.
AK is merely suggesting that you could easily add a UNIQUE CONSTRAINT or
INDEX to your three columns, and leave the rest as is. The IDENTITY doesn't
have to be the PK but you don't have to remove it completely if the system
will continue to work while it is still there. And you never know, you may
later want to use a skinnier pk than you three wide columns for related
tables (given that such things don't exist now, think about trying to undo
this change later).

complex sql statement, need help

i have a complex sql statement and i think that my structure looks good but apparently not because i keep getting the same error, i was wondering if anyone knew how to correct this problem.

SELECT A.*, B.Name, C.SIName, D.IID,

(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID , A
WHERE F.Emp='Service' AND E.Lead=1 AND E.ID=[A].[D])
AS Service,

(Select [LastName] , [FirstName]
FROM E INNER JOIN F ON E.SID =
F.SID ,A
WHERE F.Emp='Industry' AND E.Lead=1 AND E.ID=[A].[D])
AS Industry

FROM A , B, C
WHERE (1=1) AND B.SID = C.SID AND A.ID = B.ID

i always get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

does anyone know how to fix this query?Whats this syntax?

FROM E INNER JOIN F ON E.SID =
F.SID , A|||im not entirely sure, im in the process of converting this from access to sql server. i would assume that they are going ahead and assigning the table from which they will be calling on later. i usually dont use the inner join method myself, i will just type what fields i want, then the tables and then the criteria, so im guessing its along the same lines by calling table A and then specifying criteria on the next line
AND E.ID=[A].[D])|||You are getting the error because the subqueries in your select clause return more than one value (LastName and FirstName), and you are trying to assign the results to a single field in your result set (Service).

You can probably avoid the error by concatenating the two values into one:
[LastName] + ' ' + [FirstName]

but this is not a good way to write a sql statement. There are a lot of problems with the code you posted. We can probably give you some help cleaning it up, but you will need to tell us a bit more about what you want to do, and post a description of the tables and their columns.|||Are the tables really called A, B, C?

Can you post the DDL for these tables?

Do you know what the expected result is suppose to be?

I'm guessing they're trying to coorelate to table A for the names...I think I'd make it just part of the join...with a derived table

Alos you should start using the JOIN syntax...it'll be easier for you in the long run

(My Own Opinion)

MOO|||thanks for the info guys, i think i got it fixed, i used blindmans suggestion and it worked. also ill keep your advice in mind brett|||only problem i have now, is it returns each record 15 times|||This is just a guess, and it would really help if we had the DDL for tables A,B,C,E and F (are the tables really called this?) along with some sample data...

But here's a shot

SELECT A.*
, B.Name
, C.SIName
, D.IID
, N1.*
, N2.*
FROM B
INNER JOIN C ON B.ID = C.ID
INNER JOIN A ON B.ID = A.ID
INNER JOIN ( SELECT [LastName] , [FirstName] FROM E
INNER JOIN F ON E.SID = F.SID
WHERE F.Emp='Service' AND E.Lead=1) AS N1
ON N1.ID=A.D
INNER JOIN ( SELECT [LastName] , [FirstName] FROM E
INNER JOIN F ON E.SID = F.SID
WHERE F.Emp='Industry' AND E.Lead=1) AS N2
ON N2.ID=A.D|||You can avoid the duplicated records by using the SELECT DISTINCT syntax, but this and the other suggestion I gave you are really just band-aid solutions for more serious problems.
For instance, your subqueries appear to use cartesian joins to tables that are not reference in the select list or the criteria, and your subqueries in SELECT clauses are not a good idea to begin with. They should be dropped in favor of direct joins or moved to the FROM clause.|||yea i know it would help, but i got it figured out now. i dont have a lot of experience with converting from access to sql and i assumed that a lot of complex changes were neccessary when come to find out, there was really only a small change to convert it. the code i showed you guys obviously included my f'ed up alterations. but the only problem was the &'s from access had to be switched to +'s in sql. so sorry for taking up your time guys.

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 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 Query

Structure of DB
Fields
--
FieldID
Description
Details
--
DetailsID
FieldID
RangeDayID
RangeHourMonID
RangeHourTueID
RangeHourWedID
RangeHourThuID
RangeHourFriID
RangeHourSatID
RangeHourSunID
RangeDays
--
RangeDayID
>From (DateTime)
To (DateTime)
Open (Bool)
RangeHours
--
RangeHourID
>From (DateTime)
To (DateTime)
Open (Bool)
Reservations
--
ReservationID
UserID
DetailID
From
To
Users
--
UserID
Name
Example of RangeDays
From To Open
01/06/06 31/06/06 Yes
28/06/06 28/06/06 No
Example of RangeHours
From To Open
09:00 18:00 Yes
12:00 13:00 No
how should be the query for this result:
Mon-26 Tue-27 Wed-28 ...
08.00 Close Free Close
08.30 Close Free Close
09.00 Libero Luca Close
09.30 Marco Luca Close
10.00 Marco Free Close
...
Thanks a lot.Something to use to make test.
Thanks
use tempdb
set xact_abort on
begin transaction
create table Fields(FieldID int identity,Description varchar(20))
create table Details(DetailsID int identity,FieldID int,DayRange
int,HourLunRange int,HourMarRange int,HourMerRange int,HourGioRange
int,HourVenRange int,HourSabRange int,HourDomRange int)
create table RangeDays(DayID int identity,DayRange int,Da DateTime,A
DateTime,IsOpen bit)
create table RangeHours(HourID int identity,HourRange int,Da DateTime,A
DateTime,IsOpen bit)
create table Resevations(ResevationID int identity,UserID int,DetailsID
int,Da datetime,A datetime)
create table Users(UserID int identity,Nome varchar(20))
ALTER TABLE [dbo].[Fields] ADD
CONSTRAINT [PK_Fields] PRIMARY KEY CLUSTERED
(
[FieldID]
) ON [PRIMARY]
ALTER TABLE [dbo].[RangeDays] ADD
CONSTRAINT [PK_RangeDays] PRIMARY KEY CLUSTERED
(
[DayID]
) ON [PRIMARY]
ALTER TABLE [dbo].[RangeHours] ADD
CONSTRAINT [PK_RangeHours] PRIMARY KEY CLUSTERED
(
[HourID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Details] ADD
CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED
(
[DetailsID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Resevations] ADD
CONSTRAINT [PK_Resevations] PRIMARY KEY CLUSTERED
(
[ResevationID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Details] ADD
CONSTRAINT [FK_Details_Fields] FOREIGN KEY
(
[FieldID]
) REFERENCES [dbo].[Fields] (
[FieldID]
)
ALTER TABLE [dbo].[Resevations] ADD
CONSTRAINT [FK_Resevations_Details] FOREIGN KEY
(
[DetailsID]
) REFERENCES [dbo].[Details] (
[DetailsID]
),
CONSTRAINT [FK_Resevations_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [dbo].[Users] (
[UserID]
)
Insert into Users values('Marco')
Insert into Users values('Luca')
insert into Fields values('Soccer')
insert into RangeHours values(1,'09:00','18:00',1)
insert into RangeHours values(1,'12:00','13:00',0)
insert into RangeDays values(1,'20060601', '20060630', 1)
insert into RangeDays values(1,'20060611', '20060611', 0)
insert into Details values(1, 1, 1, 1, 1, 1, 1, 1, 1)
insert into Resevations values( 1, 1, '20060626 09:00' ,'20060626
10:00')
insert into Resevations values( 2, 1, '20060627 10:00' ,'20060627
12:00')
commit transaction|||Marco
First of all thanks fro posting DDL , it is realy helpful , however , can
you explain about your final result ?
It is not clear what's "Free","Close" and why do the users appear there?
Thanks
"Marco Montagnani" <marco.montagnani@.gmail.com> wrote in message
news:1151492024.943216.278430@.i40g2000cwc.googlegroups.com...
> Something to use to make test.
> Thanks
> use tempdb
> set xact_abort on
> begin transaction
> create table Fields(FieldID int identity,Description varchar(20))
> create table Details(DetailsID int identity,FieldID int,DayRange
> int,HourLunRange int,HourMarRange int,HourMerRange int,HourGioRange
> int,HourVenRange int,HourSabRange int,HourDomRange int)
> create table RangeDays(DayID int identity,DayRange int,Da DateTime,A
> DateTime,IsOpen bit)
> create table RangeHours(HourID int identity,HourRange int,Da DateTime,A
> DateTime,IsOpen bit)
> create table Resevations(ResevationID int identity,UserID int,DetailsID
> int,Da datetime,A datetime)
> create table Users(UserID int identity,Nome varchar(20))
> ALTER TABLE [dbo].[Fields] ADD
> CONSTRAINT [PK_Fields] PRIMARY KEY CLUSTERED
> (
> [FieldID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[RangeDays] ADD
> CONSTRAINT [PK_RangeDays] PRIMARY KEY CLUSTERED
> (
> [DayID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[RangeHours] ADD
> CONSTRAINT [PK_RangeHours] PRIMARY KEY CLUSTERED
> (
> [HourID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Users] ADD
> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
> (
> [UserID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Details] ADD
> CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED
> (
> [DetailsID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Resevations] ADD
> CONSTRAINT [PK_Resevations] PRIMARY KEY CLUSTERED
> (
> [ResevationID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Details] ADD
> CONSTRAINT [FK_Details_Fields] FOREIGN KEY
> (
> [FieldID]
> ) REFERENCES [dbo].[Fields] (
> [FieldID]
> )
> ALTER TABLE [dbo].[Resevations] ADD
> CONSTRAINT [FK_Resevations_Details] FOREIGN KEY
> (
> [DetailsID]
> ) REFERENCES [dbo].[Details] (
> [DetailsID]
> ),
> CONSTRAINT [FK_Resevations_Users] FOREIGN KEY
> (
> [UserID]
> ) REFERENCES [dbo].[Users] (
> [UserID]
> )
>
> Insert into Users values('Marco')
> Insert into Users values('Luca')
> insert into Fields values('Soccer')
> insert into RangeHours values(1,'09:00','18:00',1)
> insert into RangeHours values(1,'12:00','13:00',0)
> insert into RangeDays values(1,'20060601', '20060630', 1)
> insert into RangeDays values(1,'20060611', '20060611', 0)
> insert into Details values(1, 1, 1, 1, 1, 1, 1, 1, 1)
> insert into Resevations values( 1, 1, '20060626 09:00' ,'20060626
> 10:00')
> insert into Resevations values( 2, 1, '20060627 10:00' ,'20060627
> 12:00')
> commit transaction
>|||Uri Dimant wrote:
> Marco
> First of all thanks fro posting DDL , it is realy helpful , however , can
> you explain about your final result ?
The result may be a wly report of availability of fields

> It is not clear what's "Free","Close" and why do the users appear there?
Free means that there is'nt a resevation to this fiels at that time.
Close means that the field is close (not reservable) at that time.|||Marco
create trigger tr_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0 return
insert MyAuditTable
select i.ID, d.MyColumn, i.MyColumn from inserted i join deleted d on
d.ID = o.Id
"Marco Montagnani" <marco.montagnani@.gmail.com> wrote in message
news:1151495588.559261.303250@.75g2000cwc.googlegroups.com...
> Uri Dimant wrote:
> The result may be a wly report of availability of fields
>
> Free means that there is'nt a resevation to this fiels at that time.
> Close means that the field is close (not reservable) at that time.
>|||Marco,sorry
Mon-26 Tue-27 Wed-28 ...
>08.00 Close Free Close
>08.30 Close Free Close
>09.00 Libero Luca Close
>09.30 Marco Luca Close
>10.00 Marco Free Close
Do you mean that your report will start on 8AM and till what?

> The result may be a wly report of availability of fields
Yep, I got it , but can you explain a little bit your business logic , what
are you trying to achive?
"Marco Montagnani" <marco.montagnani@.gmail.com> wrote in message
news:1151495588.559261.303250@.75g2000cwc.googlegroups.com...
> Uri Dimant wrote:
> The result may be a wly report of availability of fields
>
> Free means that there is'nt a resevation to this fiels at that time.
> Close means that the field is close (not reservable) at that time.
>|||> Yep, I got it , but can you explain a little bit your business logic , what
> are you trying to achive?
Sorry My english explanation.
I'am trying to archive a structure of fields reservations.
The result of the query may be a wly report of these reservations.

Complete Neophyte Question(s)

I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
familiar with databases from a data structure/manipulation standpoint, the
permissions/security model in SQL Server 2005 has baffled me thus far. I
digress...
As far as I can tell, our previous DBA create a role which is called
SP_Exec. I know that this role has permissions defined somehow, but I'll be
damned if I can figure it out. One of our users can modify a particular
view, and another can't. The only difference I can see is that one is a
member of this role and the other isn't. If I remove the role from the user
who CAN modify the view, he no longer can. Alas, I have no idea where this
is defined.
Schemas are also slightly confusing, but I imagine that's just a way of
logically grouping sets of objects. At first glance it seems everything is
utilizing the a schema called dbo. One of our users was having trouble
until I went into the schema and added "View Definition" as a permission.
I'm not even certain what this effectively did, but it solved the problem
while I try to figure out how this whole thing works.
Can anyone point me in the direction of a document that explains this in
English? Perhaps the majority of my hang-ups are with Management Studio and
not the actual structure of the overall permissions model. ANY help would
be appreciated.I'd recommend that you go for a training course ;-)
"James" <minorkeys@.gmail.com> wrote in message
news:eVtdKXmuHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
>familiar with databases from a data structure/manipulation standpoint, the
>permissions/security model in SQL Server 2005 has baffled me thus far. I
>digress...
> As far as I can tell, our previous DBA create a role which is called
> SP_Exec. I know that this role has permissions defined somehow, but I'll
> be damned if I can figure it out. One of our users can modify a
> particular view, and another can't. The only difference I can see is that
> one is a member of this role and the other isn't. If I remove the role
> from the user who CAN modify the view, he no longer can. Alas, I have no
> idea where this is defined.
> Schemas are also slightly confusing, but I imagine that's just a way of
> logically grouping sets of objects. At first glance it seems everything
> is utilizing the a schema called dbo. One of our users was having trouble
> until I went into the schema and added "View Definition" as a permission.
> I'm not even certain what this effectively did, but it solved the problem
> while I try to figure out how this whole thing works.
> Can anyone point me in the direction of a document that explains this in
> English? Perhaps the majority of my hang-ups are with Management Studio
> and not the actual structure of the overall permissions model. ANY help
> would be appreciated.
>|||James (minorkeys@.gmail.com) writes:
> I have inherited a SQL Server (2005) from an outgoing DBA and while I'm
> familiar with databases from a data structure/manipulation standpoint, the
> permissions/security model in SQL Server 2005 has baffled me thus far.
From having been very simple-minded in SQL 4.x, it is now quite sophisticate
d.

> As far as I can tell, our previous DBA create a role which is called
> SP_Exec. I know that this role has permissions defined somehow, but I'll > be dam
ned if I can figure it out.
In Object Explorer, Databases->yourdb->Security->Roles->Database Roles.
Find the role of interest, and click Properties in the context menu.
Go to the Seucrables tab.
Normally, you grant permissions to roles, and then add users to the
roles. If you were to grant rights to users directly, it would be more
difficult to managed.

> Schemas are also slightly confusing, but I imagine that's just a way of
> logically grouping sets of objects.
Right. There are some security features related to schemas. If you add
an object to a schema owned by someone else, the schema owner becomes
the object that you created. It is also possible to grant permissions
on a schmea, which implies that you get permissions to all existing
and future objects in the schema to which the permissions apply.

> At first glance it seems everything is utilizing the a schema called
> dbo. One of our users was having trouble until I went into the schema
> and added "View Definition" as a permission.
In SQL 2005, users are only permitted to see objects they have permission
to. This is a change from SQL 2000 where the metadata was visible to
all users.

> Can anyone point me in the direction of a document that explains this in
> English? Perhaps the majority of my hang-ups are with Management Studio
> and not the actual structure of the overall permissions model. ANY help
> would be appreciated.
The normal starting point would be
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-7808
659449c5.htm
in Books Online, but admittedly Books Online is surprisingly thin on
some of the permission topics.
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|||Thank you very much. I believe I have my head wrapped around this better.
When I go to the Securables tab for this role, there are no objects in the
listbox. Simply an Add button. Yet, if I view the properties of certain
stored procedures it will have that role listed with execute permissions.
This seems like a simple request, but all I want is to see the objects that
a certain role has permissions on, and what those permissions are?
Rhetorical: Why is this so difficult/counter-intuitive?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns995FC3C3DACYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> From having been very simple-minded in SQL 4.x, it is now quite
> sophisticated.
>
> In Object Explorer, Databases->yourdb->Security->Roles->Database Roles.
> Find the role of interest, and click Properties in the context menu.
> Go to the Seucrables tab.
> Normally, you grant permissions to roles, and then add users to the
> roles. If you were to grant rights to users directly, it would be more
> difficult to managed.
>
> Right. There are some security features related to schemas. If you add
> an object to a schema owned by someone else, the schema owner becomes
> the object that you created. It is also possible to grant permissions
> on a schmea, which implies that you get permissions to all existing
> and future objects in the schema to which the permissions apply.
>
> In SQL 2005, users are only permitted to see objects they have permission
> to. This is a change from SQL 2000 where the metadata was visible to
> all users.
>
> The normal starting point would be
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5d43fefc-5aa4-43d7-aedb-78
08659449c5.htm
> in Books Online, but admittedly Books Online is surprisingly thin on
> some of the permission topics.
>
> --
> 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|||James (minorkeys@.gmail.com) writes:
> Thank you very much. I believe I have my head wrapped around this better.
> When I go to the Securables tab for this role, there are no objects in the
> listbox. Simply an Add button. Yet, if I view the properties of certain
> stored procedures it will have that role listed with execute permissions.
> This seems like a simple request, but all I want is to see the objects
> that a certain role has permissions on, and what those permissions are?
> Rhetorical: Why is this so difficult/counter-intuitive?
I did some research, and I think I have the answer. If you do Help->About
what version do you get for Managment Studio? My guess is that you will
see something 9.00.1399 or 9.00.2047, that is either RTM or SP1. To wit,
when I try this on SP1 of SSMS, I don't see the securables, but SP2 gives
me the list of objects.
You can find the latest service pack for SQL Server on
http://support.microsoft.com/kb/913089/.
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|||I don't use the word hero very often, but you sir, are the greatest hero of
all time. Thank you for confirming I'm not insane. SP2 has caused the
Securables tab to populate properly and now it actually makes sense.
The only other issue that I'm trying to wrap my head around and can't seem
to google well is the difference between "GRANT" and "WITH GRANT". Can you
enlighten me?
Thank you a million times over!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996577CE42B7BYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> I did some research, and I think I have the answer. If you do Help->About
> what version do you get for Managment Studio? My guess is that you will
> see something 9.00.1399 or 9.00.2047, that is either RTM or SP1. To wit,
> when I try this on SP1 of SSMS, I don't see the securables, but SP2 gives
> me the list of objects.
> You can find the latest service pack for SQL Server on
> http://support.microsoft.com/kb/913089/.
>
> --
> 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|||Disregard, seems my google-ing skills were lacking.
More generic question as I'm messing with the Northwind database to get a
thorough understanding of this. If a user isn't a member of any roles and
has no permissions explicitly defined, does it err on the side of deny or
grant? Or does that depend on the permission? Right now I have a user who
has Connect as the only database level permission, no roles, no secureables
but can still view definition, it seems. I'm able to connect and view all
of the tables, although everything else seems locked down. I have refreshed
and can still see them.
Either way, thanks again. I'm much much further than I was yesterday at
this time.
"James" <minorkeys@.gmail.com> wrote in message
news:uFdBmN%23vHHA.2304@.TK2MSFTNGP06.phx.gbl...
>I don't use the word hero very often, but you sir, are the greatest hero of
>all time. Thank you for confirming I'm not insane. SP2 has caused the
>Securables tab to populate properly and now it actually makes sense.
> The only other issue that I'm trying to wrap my head around and can't seem
> to google well is the difference between "GRANT" and "WITH GRANT". Can
> you enlighten me?
> Thank you a million times over!
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns996577CE42B7BYazorman@.127.0.0.1...
>|||James (minorkeys@.gmail.com) writes:
> Disregard, seems my google-ing skills were lacking.
You should not have to go Google to find out what WITH GRANT means. SQL
Server comes with online documentation on you hard disk.
WITH GRANT is one of the more esotheric features in SQL Server in my
opinion, but maybe that says more about the simplistic security of the
system I work with.

> More generic question as I'm messing with the Northwind database to get
> a thorough understanding of this. If a user isn't a member of any roles
> and has no permissions explicitly defined, does it err on the side of
> deny or grant? Or does that depend on the permission? Right now I have
> a user who has Connect as the only database level permission, no roles,
> no secureables but can still view definition, it seems. I'm able to
> connect and view all of the tables, although everything else seems
> locked down. I have refreshed and can still see them.
If no permissions have been granted, then you have no permissions. That is,
if run the below in a database, the SELECT should not return anything:
CREATE LOGIN erik WITH PASSWORD='rtsoppa'
go
CREATE USER erik
go
EXECUTE AS LOGIN = 'erik'
go
SELECT name FROM sys.objects
go
REVERT
go
DROP USER erik
go
DROP LOGIN erik
...unless rights have been granted to the public role.
In SQL 2005 a user only has permission to see the definition of objects
to which he been granted some access. More exactly he needs VIEW DEFINITION,
but this permission is implied if he already has SELECT permission.
Where DENY comes in is that it overrides GRANT. Say that a user is a member
of a role that has SELECT permission to a table X, but that himself he
has been denied access to the table. Then he cannot access that table.
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|||That makes complete sense to me, but somehow I'm overlooking something.
I have a user named james on a database. If I go into properties for that
user they have no owned schemas. They have no role membership (including
public, which isn't listed here for some reason). If I right-click the
database and go to properties -> Permissions, the only permission they have
is Connect, not view definition.
The only thing I can see is that there's a login of the same name at the
server level which is a member of the Server Role public, but my
understanding is that it's unrelated.
So the long and the short of it, is that this user can view definition on
this database, and I can't figure out why.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns996668C8847CBYazorman@.127.0.0.1...
> James (minorkeys@.gmail.com) writes:
> You should not have to go Google to find out what WITH GRANT means. SQL
> Server comes with online documentation on you hard disk.
> WITH GRANT is one of the more esotheric features in SQL Server in my
> opinion, but maybe that says more about the simplistic security of the
> system I work with.
>
> If no permissions have been granted, then you have no permissions. That
> is,
> if run the below in a database, the SELECT should not return anything:
> CREATE LOGIN erik WITH PASSWORD='rtsoppa'
> go
> CREATE USER erik
> go
> EXECUTE AS LOGIN = 'erik'
> go
> SELECT name FROM sys.objects
> go
> REVERT
> go
> DROP USER erik
> go
> DROP LOGIN erik
> ...unless rights have been granted to the public role.
> In SQL 2005 a user only has permission to see the definition of objects
> to which he been granted some access. More exactly he needs VIEW
> DEFINITION,
> but this permission is implied if he already has SELECT permission.
> Where DENY comes in is that it overrides GRANT. Say that a user is a
> member
> of a role that has SELECT permission to a table X, but that himself he
> has been denied access to the table. Then he cannot access that table.
>
>
> --
> 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|||James (minorkeys@.gmail.com) writes:
> That makes complete sense to me, but somehow I'm overlooking something.
> I have a user named james on a database. If I go into properties for
> that user they have no owned schemas. They have no role membership
> (including public, which isn't listed here for some reason). If I
> right-click the database and go to properties -> Permissions, the only
> permission they have is Connect, not view definition.
> The only thing I can see is that there's a login of the same name at the
> server level which is a member of the Server Role public, but my
> understanding is that it's unrelated.
> So the long and the short of it, is that this user can view definition on
> this database, and I can't figure out why.
If you under database roles look at the public role, does it have any
permissions on anything?
If you run:
execute as login = 'james'
go
select * from sys.fn_my_permissions('dbo.Orders', 'object')
go
revert
What do you see?
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

Compatibility with SQL 2000

I have moved a database from SQL 2000 to SQL 2005 Express. I have modified the structure in 2005 Management Studio Express.

Now I cannot attach to the modified dataabse in SQL 2000 Enterprise Manager. I get "Error 602: Could not find row in sysindexes for database ID.... Run DBCC CHECKTABLE on sysindexes".

This occurs despite the fact that I have kept the database at Compatibiluty Level SQL Server 2000, as reported in 2005 Management Studio Express.

Are 2005 and 2000 databases not compatible?

Many thanks.

As you discovered, once you attach a database to SQL 2005, you cannot move it back to SQL 2000.

Moving a database is a one-way operation, and cannot be reversed.

|||

Compatability Mode has nothing to do with the database format, it has to do with how commands are run by the database engine. Databases are automatically converted to the 2005 file format when they are attached, there is no way to convert the database format back. You should be able to manage SQL 2000 database from SSMS if that is the tool you would prefer to use, but you can not bounce databases back and forth between SQL 2000 and SQL 2005.

Mike

|||Thanks.