composite primary key...

I have two fields in a table,first name and last name.Both the fields contain duplicate values.Say, there are two people with the firstname mary.their last names are andrews and mathews.SQL Server wouldn't let me assign these fields a composite primary key.gives me an error saying that the first name field contains duplicate values (Duh...)....
Any help would be appreciated.No le creaste algun indice?, podrias poner el DDL para ver la tabla, ya sea desde el enterprise manager, seleccionando la tabla, click derecho, Todas las tareas, generar sql script.|||

Quote:

Originally Posted by Arkhels

No le creaste algun indice?, podrias poner el DDL para ver la tabla, ya sea desde el enterprise manager, seleccionando la tabla, click derecho, Todas las tareas, generar sql script.

???????:(|||

Quote:

Originally Posted by TheIntern

I have two fields in a table,first name and last name.Both the fields contain duplicate values.Say, there are two people with the firstname mary.their last names are andrews and mathews.SQL Server wouldn't let me assign these fields a composite primary key.gives me an error saying that the first name field contains duplicate values (Duh...)....
Any help would be appreciated.

making these fields a composite primary key is not too smart. Add an ID field - data type int identity (1,1) not null.

composite index and column order

Hi,

I created a composite index (lastname, firstname). I know the following
queries will use this index:

WHERE lastname = ...
WHERE lastname = ... AND firstname = ...

Also this won't use the index:
WHERE firstname = ...

But how about: WHERE firstname = .. AND lastname = ...

And why?

Thanks a lot,

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORGBaihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...
> But how about: WHERE firstname = .. AND lastname = ...

It will use the index.

> And why?

Because, as far as the query optimizer is concerned, these two are
exactly the same:

WHERE lastname = ... AND firstname = ...
WHERE firstname = ... AND lastname = ...|||Thanks for your help, I really appreciate it.

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG|||Baihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...

Not necessarily. Consider the following query:

select lastname, firstname from some_table where firstname = ...

It will use the index, and, more to the point, it will not touch the
table at all - the index already has all the information the query
needs. It is called "index covering".|||Alexander Kuznetsov wrote:
> Baihao Yuan wrote:
> > Hi,
> > I created a composite index (lastname, firstname). I know the following
> > queries will use this index:
> > WHERE lastname = ...
> > WHERE lastname = ... AND firstname = ...
> > Also this won't use the index:
> > WHERE firstname = ...
> Not necessarily. Consider the following query:
> select lastname, firstname from some_table where firstname = ...
> It will use the index, and, more to the point, it will not touch the
> table at all - the index already has all the information the query
> needs. It is called "index covering".

No, it won't. If you had a list of people on a piece of paper, sorted
by last name and then by first name, explain how you would use that
list to find everyone with a first name of "Joe" without looking
through the entire list.|||I created a table called tblNames with nonClustered index defined on
lastname,firstname (composite index).

select * from tblnames where lastname = 'smith'--Performed an Index
Seek

select * from tblnames where lastname = 'smith' and firstname =
'john'--Performed an Index Seek

select * from tblnames where firstname = 'john'--Performed a Table Scan

select * from tblnames where firstname = 'john' and lastname =
'smith'--Performed an Index Seek

ZeldorBlat wrote:
> Alexander Kuznetsov wrote:
> > Baihao Yuan wrote:
> > > Hi,
> > > > I created a composite index (lastname, firstname). I know the following
> > > queries will use this index:
> > > > WHERE lastname = ...
> > > WHERE lastname = ... AND firstname = ...
> > > > Also this won't use the index:
> > > WHERE firstname = ...
> > > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.|||> > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Why don't you try it out in practice? You might be in for some
surprise. If the index is smaller than the table, and contains all the
necessary information, it is likely to be used instead of the table.
Google up "index covering".|||Alexander Kuznetsov wrote:
> > > Not necessarily. Consider the following query:
> > > > select lastname, firstname from some_table where firstname = ...
> > > > It will use the index, and, more to the point, it will not touch the
> > > table at all - the index already has all the information the query
> > > needs. It is called "index covering".
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Why don't you try it out in practice? You might be in for some
> surprise. If the index is smaller than the table, and contains all the
> necessary information, it is likely to be used instead of the table.
> Google up "index covering".

I did try it in practice -- as did the OP who posted his results in
this thread.|||>
> select * from tblnames where firstname = 'john'--Performed a Table Scan

If you only select 2 columns, firstname, lastname

select columns, firstname from tblnames where firstname = 'john'

and the table has a lot of other columns, the index is likely to be
used even if firstname is not the first column in the index. The reason
is simple: the index contains all the information necessary to satisfy
the query and it is smaller than the table.|||> I did try it in practice -- as did the OP who posted his results in
> this thread.

the OP tried for

select * from ...

while I was speaking aobut

select lastName, firstname from ...

Big difference.|||correction:

If you only select 2 columns, firstname, lastname

select firstname, lastname from tblnames where firstname = 'john'

> and the table has a lot of other columns, the index is likely to be
> used even if firstname is not the first column in the index. The reason
> is simple: the index contains all the information necessary to satisfy
> the query and it is smaller than the table.|||ZeldorBlat (zeldorblat@.gmail.com) writes:
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Say further that with each list there is a page number to references
where the persons appear in the book.

If all you want to know is the name of the persons, you can scan
the index, you don't have to read the whole book.

It's important to keep in mind that an index can be used in two
ways: Seek (look up data through the index tree) and Scan (read
the entire index from left to right): While the latter is far more
expensive, it can still be useful at times.

--
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|||Erland Sommarskog wrote:
> ZeldorBlat (zeldorblat@.gmail.com) writes:
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Say further that with each list there is a page number to references
> where the persons appear in the book.
> If all you want to know is the name of the persons, you can scan
> the index, you don't have to read the whole book.
> It's important to keep in mind that an index can be used in two
> ways: Seek (look up data through the index tree) and Scan (read
> the entire index from left to right): While the latter is far more
> expensive, it can still be useful at times.
>
> --
> 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

Point taken. Thanks, Erland.|||I'd like to repeat the suggestion to do your own experimenting. The
technology evolves quite fast, so anything you might have read in any
book / article / whatever esle may be already obsolete. The optimizer
is way smarter now than it used to be 5 or 10 years ago. In this
particular case you did not need to read anything, you could just take
any table of, say, 100K rows, with, say, 20 columns, create an index on
it

create index i1 on t1(col1, col2)

and see the execution plan for the query

select col1, col2 from t1 where col2 =...

That's all it takes, it's that simple.

Good luck!

Complex Query question. Using Sum and CAST operation.

Hi

Can onayone tell me what is wrong with the below code. Thank you,

SELECT P.FirstName, P.LastName, P.PortfolioID, P.PortfolioDescription, SC.Description, SC.SecurityID, SC.SecurityTypeID, SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID WHEN 11 THEN CAST(SUM(T .Quantity)) WHEN 2 THEN CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) / 100 ELSE CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) END AS Amount, P.AccountNumber, P.AccountTypeDescription,
SC.CodeDescription, SC.CodeDescription2, SC.Symbol
FROM dbo.Transactions AS T WITH (Nolock) INNER JOIN
dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID INNER JOIN
dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID INNER JOIN
dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
WHERE (T.StatusTypeID = 100) AND (G.OwningPortfolioID = 270) AND (T.TradeDate <= '1/24/2007')
GROUP BY T.SymbolID, P.FirstName, P.LastName, P.PortfolioID, SC.Description, SC.SecurityID, SC.SecurityTypeID, P.PortfolioDescription, P.AccountNumber,
P.AccountTypeDescription, SC.CodeDescription, SC.CodeDescription2, SC.Symbol

The correct syntax for CAST is CAST( <expr> as <datatype>). So fix your query and try again. Also, it is good to post the actual error message that you are getting so that we don't have to guess what might be wrong.|||

The following query will work for you..

Note: You Cannot perform an aggregate function on an expression containing an aggregate or a subquery; So you have to use INNER JOIN Instead of Subquery. It is good practice to use join rather than the subquery.

SELECT
P.FirstName,
P.LastName,
P.PortfolioID,
P.PortfolioDescription,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID
WHEN 11 THEN
CAST(SUM(T .Quantity) As Int)
WHEN 2 THEN
CAST(SUM(T .Quantity * SP.Price) / 100 as INT)
ELSE
CAST(SUM(T .Quantity * SP.Price) as INT)
END AS Amount,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol
FROM
dbo.Transactions AS T WITH (Nolock)
INNER JOIN dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID
INNER JOIN dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID
INNER JOIN dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
INNER JOIN dbo.SecurityPrices As SP WITH (Nolock) ON T.SymbolID = SP.SecurityID AND SP.PriceDate = '1/24/2007'
WHERE
(T.StatusTypeID = 100)
AND (G.OwningPortfolioID = 270)
AND (T.TradeDate <= '1/24/2007')
GROUP BY
T.SymbolID,
P.FirstName,
P.LastName,
P.PortfolioID,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
P.PortfolioDescription,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol