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

Doubt in Index

Hi EverB,

I have created a table say T1 with 5 columns (col1.....col5).
I have created an index on col4 and col5.

iam executing a select query say.

select col1,col2,col3 from t1 where col1 =0;

In the above query i hav not used the columns for which i hav created the index.My ques is-

Does the index really works when u execute the above query?

Any answer please.......
It wuld be better if u culd suggest some sites for this.

Thanks and Regds,
Arunno, for that query, the index is ignored

a good tutorial is Optimizing your MySQL Application (http://www.sitepoint.com/print/optimizing-mysql-application) even if you're using something other than mysql

double unique index

Hello.

I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example:

Row 1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 2

this shouldn't be possible.

Row1:

ColumnA = 1, ColumnB = 2

Row2:

ColumnA = 1, ColumnB = 3

this should be possible

is there any way I can do this?

thanks in advance

You can create a UNIQUE constraint or a unique INDEX.

ALTER TABLE YourTable ADD CONSTRAINT UK_YourTable_ColumnA_ColumnB UNIQUE (ColumnA,ColumnB)

Or

CREATE UNIQUE INDEX IX_YourTable_ColumnA_ColumnB ON YourTable (ColumnA,ColumnB)

The constraint or index can also be clustered if you do not have a clustered PK/index/constraint yet.

doing a FREETEXTTABLE on more than one columns -OR- creating a SECOND FT INDEX for a tab

Is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.
Alternatively, is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?
SQL 2005 allows you to select which column you want returned in a full-text
search - this is for both Contains and FreeText. SQL FTS also allows you to
full-test index indexed views, which might also help you.
You can only create a single Full-Text index per table. I think what you
might have to do in SQL 2000 is to partition your table into multiple child
tables containing the columns you wish and then full-text index these
tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||SQL2000 also lets you select a column, but only one! Does 2005 allow
multiple columns?
Partition the table to smaller ones? How do you mean that exactly?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
> SQL 2005 allows you to select which column you want returned in a
> full-text
> search - this is for both Contains and FreeText. SQL FTS also allows you
> to
> full-test index indexed views, which might also help you.
> You can only create a single Full-Text index per table. I think what you
> might have to do in SQL 2000 is to partition your table into multiple
> child
> tables containing the columns you wish and then full-text index these
> tables.
|||Denis,
Yes, this can be done, see "SQL Server FTS across multiple tables or
columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
and substitute freetexttable for containstable in the examples.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included in the index. Any ideas?
Or perhaps you could show me how CONTAINSTABLE could be applied in this
case?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uKI3DMLKFHA.2628@.tk2msftngp13.phx.gbl...
> Denis,
> Yes, this can be done, see "SQL Server FTS across multiple tables or
> columns" at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> and substitute freetexttable for containstable in the examples.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> statement,
>
|||No SQL 2000 allows you to search one column or all columns. SQL 2005 lets
you search one column, all columns, or any combination of columns.
To partition a table you have to make one or more tables with the same PK.
So if you have a table like this:
Create Table parent
( pk int not null identity constraint primarykey primary key,
charcol1 char(20),
charcol2 char(20),
charcol3 char(20),
charcol4 char(20))
You could create the following child tables
Create Table child1
( pk int not null identity constraint primarykey1 primary key,
charcol1 char(20))
Create Table child2
( pk int not null identity constraint primarykey2 primary key,
charcol2 char(20))
Create Table child3
( pk int not null identity constraint primarykey3 primary key,
charcol3 char(20))
And then use triggers or replication to keep them in sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis" <denis@.pharmiweb.com> wrote in message
news:eIOgqtKKFHA.1500@.TK2MSFTNGP09.phx.gbl...
> SQL2000 also lets you select a column, but only one! Does 2005 allow
> multiple columns?
> Partition the table to smaller ones? How do you mean that exactly?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included And if EITHER of the fields
contain the search string i want them displayed.
|||SQL 2000 CAN indeed look either in one or all fields. Look at the
definition of FREETEXTTABLE.
|||Create Table tblJobsDataWareHouse
(uid int not null identity constraint primarykey4 primary key,
fldRequirementshtm char(20),
fldJobTitle char(20),
fldCompanyName char(20))
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values ('test',null,null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL,'test',null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'test')
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'rest')
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'test','create'
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
GO
sp_fulltext_catalog 'test','start_full'
go
select * from tblJobsDataWareHouse where contains(*,'test')
go
select * from tblJobsDataWareHouse as tbl join (
select * from containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
union
select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
union
select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
)as ft
on ft.[key]=tbl.uid
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis" <denis@.pharmiweb.com> wrote in message
news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
> I looked at what you said but I am not sure how that would work. Let me
> give you an idea of the type of how i am trying to do the search.
>
> -- Old Search
> --SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
> FT_TBL.uID = KW.[KEY]'
> --SET @.RankField = 'KW.RANK'
>
> -- NEW SEARCH
> SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
> ''') as KW ON FT_TBL.uID = KW.[KEY] '
> SET @.RankField = 'KW.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
> KWw ON FT_TBL.uID = KWw.[KEY]'
> SET @.RankField = 'KWw.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
> as KWww ON FT_TBL.uID = KWww.[KEY]'
> SET @.RankField = 'KWww.RANK'
>
> I want my new search to look at Title, Description and Company name only,
> rather than every field that was included And if EITHER of the fields
> contain the search string i want them displayed.
>
|||many thanks for your code example.
After some thinking i decided that due to the structure of the database is
best not to break-down the table into smaller ones, so i guess i will have
to find another way of doing it.
But thank ytou very much for the time you took to answer my question.
again, many thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e%23msOZZKFHA.656@.TK2MSFTNGP14.phx.gbl...
> Create Table tblJobsDataWareHouse
> (uid int not null identity constraint primarykey4 primary key,
> fldRequirementshtm char(20),
> fldJobTitle char(20),
> fldCompanyName char(20))
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values ('test',null,null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL,'test',null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'test')
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'rest')
> GO
> sp_fulltext_database 'enable'
> GO
> sp_fulltext_catalog 'test','create'
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add',
> 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
> GO
> sp_fulltext_catalog 'test','start_full'
> go
> select * from tblJobsDataWareHouse where contains(*,'test')
> go
> select * from tblJobsDataWareHouse as tbl join (
> select * from
> containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
> union
> select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
> union
> select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
> )as ft
> on ft.[key]=tbl.uid
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
>