Doubt regarding PFS

Does SQL Server use PFS to check whether a page is allocated or not if the
table has a clustered index.
Regards
BalajiPFS is used for determining if there is enough free space on the page for
the row but only on Heaps.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:C5E97806-A784-4B47-9746-EA01C5A3F84A@.microsoft.com...
> Does SQL Server use PFS to check whether a page is allocated or not if the
> table has a clustered index.
> Regards
> Balaji

Doubt on unique non clustered Index

Hi All,
I have a table with data:
CREATE TABLE [dbo].[Relation] (
[ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
[PersonID] [uniqueid] NOT NULL ,
[CurrentID] [uniqueid] NOT NULL ,
[RelativeName] [varchar] (50) NOT NULL ,
)
Here there are duplicate records for a combination of PersonID, CurrentID
and RelativeName.
I need to create a unique non clustered index on PersonID, CurrentID and
RelativeName.
But it throws an error saying duplicate values...
Why I want to create such an index is to stop inserting further duplicates
and the old data is important for me.
Is there any other way i can accomplish this?
Thanks,
PradYou ought to be able to get rid of the duplicates, otherwise your table
lacks integrity because of the redundant data. Why do you say that the old
data is important to you? In what way?
First, you'll have to fix any foreign key references:
UPDATE foo
SET id =
(SELECT MIN(R2.id)
FROM Relation AS R1
, Relation AS R2
WHERE R1.id = foo.id
AND R1.personid = R2.personid
AND R1.currentid = R2.currentid
AND R1.relativename = R2.relativename) ;
Then delete the duplicates:
DELETE FROM Relation
WHERE EXISTS
(SELECT *
FROM Relation AS R
WHERE R.personid = Relation.personid
AND R.currentid = Relation.currentid
AND R.relativename = Relation.relativename
AND R.id < Relation.id) ;
Now you can add the unique constraint.
Hope this helps.
David Portas
SQL Server MVP
--|||Hi
Maybe you should be changing/consolidating the data related that makes this
non-unique. This does not necessarily mean deleting it, for example having a
alternateids table would mean that only one id needs to exist in the "master
"
table.
John
"Pradeep Kutty" wrote:

> Hi All,
> I have a table with data:
> CREATE TABLE [dbo].[Relation] (
> [ID] [uniqueid] IDENTITY (1, 1) NOT NULL ,
> [PersonID] [uniqueid] NOT NULL ,
> [CurrentID] [uniqueid] NOT NULL ,
> [RelativeName] [varchar] (50) NOT NULL ,
> )
> Here there are duplicate records for a combination of PersonID, CurrentID
> and RelativeName.
> I need to create a unique non clustered index on PersonID, CurrentID and
> RelativeName.
> But it throws an error saying duplicate values...
> Why I want to create such an index is to stop inserting further duplicates
> and the old data is important for me.
> Is there any other way i can accomplish this?
> Thanks,
> Prad
>
>|||I prefer this one:
Delete from Relation
Where
[id] not in
(
SELECT MAX([id])
FROM Relation
GROUP BY [personid],[currentid],[relativename])
Regards,
"David Portas" wrote:

> You ought to be able to get rid of the duplicates, otherwise your table
> lacks integrity because of the redundant data. Why do you say that the old
> data is important to you? In what way?
> First, you'll have to fix any foreign key references:
> UPDATE foo
> SET id =
> (SELECT MIN(R2.id)
> FROM Relation AS R1
> , Relation AS R2
> WHERE R1.id = foo.id
> AND R1.personid = R2.personid
> AND R1.currentid = R2.currentid
> AND R1.relativename = R2.relativename) ;
> Then delete the duplicates:
> DELETE FROM Relation
> WHERE EXISTS
> (SELECT *
> FROM Relation AS R
> WHERE R.personid = Relation.personid
> AND R.currentid = Relation.currentid
> AND R.relativename = Relation.relativename
> AND R.id < Relation.id) ;
> Now you can add the unique constraint.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>

Dont use clustered indexes?

Hi,

The more I read, the more confused I'm getting ! (no wonder they say
ignorance is bliss)

I just got back from the bookstore and was flipping through some SQL Server
Administration books.

One says, that to get the best query performance, youi do two things:

1. Cover all the columns used in each SELECT (including the WHERE, ORDER
BY , etc.) with an index

2. Make sure it's a NON-CLUSTERED index.

In this way, the author says, you avoid ever going directly to the base
tables for data to resolve the query - i.e. it's resolved in the index.

So, for example, he argues if you have:

SELECT Lname,Fname, CompanyName
from Contacts
inner join Customers
on (contacts.custid = customers.custid)

that you use two non-clustered indexes:
1. Lname,Fname and custid from the Contacts table
2. CompanyName and custid from Customers

(as opposed to the standard approach of a clustered index on the PK's of
each table)

He says that clustered indexes don't speed up performance because they're
the same as a full table scan. Should I drop clustered indexes from my
large tables, given that there are multiple non-clustered indexes on them?
Is it better to just use multiple non-clustered indexes on a heap table?

SteveThe best indexing strategy depends on a number of factors. It is true that
a covering non-clustered index will be beneficial to some queries,
especially when only a few columns are selected and most of the rows in the
table are needed. However, you need to balance the cost of maintaining the
index with the benefits of using it. Too many indexes can slow down
insert/update performance and increase the likelihood of blocking and
deadlocks. It's overkill to create a lot of non-clustered indexes to cover
queries unless the database is read-only, the additional disk space
requirements aren't a concern and you can anticipate the queries beforehand.

Personally, I rarely create heap tables in SQL 7 and above. The clustered
index eliminates the I/O overhead of maintenance to non-clustered leaf nodes
due to page splits because the clustered key rather than physical location
is used to as the bookmark to data rows. See the Books Online
<architec.chm::/8_ar_da2_8sit.htm> for a discussion on clustered and
non-clustered indexes.

Covering non-clustered indexes are appropriate to address specific
performance issues but, IMHO, are the exception rather than the rule.
Exercising a little common sense in creating useful indexes goes a long way
in preventing performance problems.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Steve_CA" <steveee_ca@.yahoo.com> wrote in message
news:5cccd.67695$3C6.2513201@.news20.bellglobal.com ...
> Hi,
> The more I read, the more confused I'm getting ! (no wonder they say
> ignorance is bliss)
> I just got back from the bookstore and was flipping through some SQL
> Server Administration books.
> One says, that to get the best query performance, youi do two things:
> 1. Cover all the columns used in each SELECT (including the WHERE, ORDER
> BY , etc.) with an index
> 2. Make sure it's a NON-CLUSTERED index.
> In this way, the author says, you avoid ever going directly to the base
> tables for data to resolve the query - i.e. it's resolved in the index.
> So, for example, he argues if you have:
> SELECT Lname,Fname, CompanyName
> from Contacts
> inner join Customers
> on (contacts.custid = customers.custid)
> that you use two non-clustered indexes:
> 1. Lname,Fname and custid from the Contacts table
> 2. CompanyName and custid from Customers
> (as opposed to the standard approach of a clustered index on the PK's of
> each table)
> He says that clustered indexes don't speed up performance because they're
> the same as a full table scan. Should I drop clustered indexes from my
> large tables, given that there are multiple non-clustered indexes on them?
> Is it better to just use multiple non-clustered indexes on a heap table?
> Steve