3 tables on one report

Dear all
I want to display three tables on one report. This works fine as long I
place them on the same horizontal line (3 columns). If I use 2 columns (first
column table 1, second column tables 2 and 3) I have the problem, that table
3 starts where table 1 ends. Because table 1 is longer as table 2 I have a
lot of empty space between table 2 and 3.
How can I change this?
Thanks for your answer,
MarcWell, I have a workaround: I use a subreport.
Regards, Marc|||Marc,
I am not sure what you are trying to do, but I have several reports that
need multiple tables on one report. I have had to put them within nested
list boxes at times to get a particular order of the tables to appear (since
otherwise then tend to follow one after the other and I wanted them
interlaced) - In other cases I have used rectangles to enforce placement
within a page. There are other ways to handle without a subreport. If you
give a better idea of what you want then maybe I can suggest something.
Thanks
"Marc" wrote:
> Well, I have a workaround: I use a subreport.
> Regards, Marc|||Hi MJT
Thanks for your answer. Well I'm quite new to SQL Server 2005 Reporting
Services so I struggled with placing tables on a report and arranging them as
the requirement says. Like you proposed I useed the rectangle - this works
very well! What I did not understand is the List component. When I place
several tables inside a List, this component requires a dataset. So I guess
the List is used for other purposes.
Best regards,
Marc|||Hi Marc,
A list data region repeats with each group or row in the dataset. A list
can be used for free-form reports or in conjunction with other data regions.
You could refer the following article in the SQL Books Online:
Working with List Data Regions
http://msdn2.microsoft.com/en-us/library/ms159121(d=ide).aspx
Hope this will be helpful to you to understand the List Control. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

3 table query help

i have 3 tables member_info, subscription_info, exclude
member_info has 3 columns (login, fname, lname)
subscription_info has 5 columns
(login, subid, monthlypayment, startdate, enddate)
exclude has only one column (login)
I want to write a query that would return:
login, fname, lname, subid, monthlypayment
where login is not on of the logins from exclude table..
I tried this query:
select login, fname, lname, subid, monthlypayment
from member_info, subscription_info, exclude
where member_info.login=subscription_info.login and
member_info.login <> exclude.login
but that yeilds duplicate records, please helptry:
select m.login, m.fname, m.lname, s.subid, s.monthlypayment
from member_info m
inner join subscription_info s ON m.login = s.login
left outer join exclude e ON e.login = m.login
where e.login IS NULL
>--Original Message--
>i have 3 tables member_info, subscription_info, exclude
>member_info has 3 columns (login, fname, lname)
>subscription_info has 5 columns
>(login, subid, monthlypayment, startdate, enddate)
>exclude has only one column (login)
>I want to write a query that would return:
>login, fname, lname, subid, monthlypayment
>where login is not on of the logins from exclude table..
>I tried this query:
>select login, fname, lname, subid, monthlypayment
>from member_info, subscription_info, exclude
>where member_info.login=subscription_info.login and
>member_info.login <> exclude.login
>but that yeilds duplicate records, please help
>.
>

3 table joins - 3rd table join main not exist (return null columns) - please help!

Hello SQL Guru's,

This has had me stumped for about 8 hours already and I think I've gotten to a point where I'm trying the same things over and over again and they are just not working. Any help would be greatly appreciated!

My Table Structure:

Table 1) 'Modules'

ModuleID | ModuleName | isVisible
--
1 Test 1 True
2 Test 2 True
3 Test 3 False
4 Test 4 True

Table 2) 'ModuleUserTypes'

ID | ModuleID | UserType

1 1 1
2 1 2
3 2 1
4 3 1
5 4 1
6 4 2

Table 3) 'ModuleUserSettings'

ID | ModuleID | UserID | CustomTitle | BGColor
--
1 2 1 New Title2 Black
2 2 2 New Title2 White
3 3 1 New Title3 Orange
4 4 1 NewTitle4 Yellow

My Goal:
To be able to join the 3 tables 'Modules', 'ModuleUserTypes', and 'ModuleUserSettings' together and return 'ModuleID, ModuleName, CustomTitle, BGColor' for ALL Modules with UserType = 1 along with associated ModuleUserSettings IF the UserSetting exists, otherwise NULL for the columns.

My desired result set:

UserID = 1
UserType = 1
isVisible = True

ModuleID | ModuleName | CustomTitle | BGColor
--
1 Test1 NULL NULL
2 Test2 New Title2 White
4 Test4 New Title4 Yellow

I'm sure this type of query will be easy for someone out there, but rather hard for me!

Thanks for your efforts!

Execute the following query, to get your results :

select m.ModuleID,m.ModuleName,CustomTitle,BGColor

from Modules m left join ModuleUserTypes mut on m.ModuleID = mut.ModuleID

left join ModuleUserSettings mus on m.ModuleID = mus.ModuleID

where (UserID is null or UserID = 1)

and UserType=1

and IsVisible = 1

Assumption - IsVisible column is bit data type

otherwise - use -

and IsVisible = 'True'

Thanks

Naras.

|||you need to include your User table or you table that define the usertype of a user and you need to use left join

hope this helps

SELECT *
INTO #Modules
FROM (
SELECT 1 AS ModuleID
,'Test 1' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 2 AS ModuleID
,'Test 2' AS ModuleName
, 'True' AS isVisible
UNION ALL
SELECT 3 AS ModuleID
,'Test 3' AS ModuleName
, 'False' AS isVisible
UNION ALL
SELECT 4 AS ModuleID
,'Test 4' AS ModuleName
, 'True' AS isVisible
) Modules

SELECT *
INTO #ModuleUserTypes
FROM ( SELECT 1 AS [ID]
, 1 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 2 AS [ID]
, 1 AS ModuleID
, 2 AS UserType
UNION ALL
SELECT 3 AS [ID]
, 2 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 4 AS [ID]
, 3 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 5 AS [ID]
, 4 AS ModuleID
, 1 AS UserType
UNION ALL
SELECT 6 AS [ID]
, 4 AS ModuleID
, 2 AS UserType

) ModuleUserTypes

SELECT *
INTO #ModuleUserSettings
FROM (

SELECT 1 AS [ID]
, 2 AS ModuleID
, 1 AS UserID
, 'New Title2' AS CustomTitle
, 'White' AS BGColor
UNION ALL
SELECT 2 AS [ID]
, 2 AS ModuleID
, 2 AS UserID
, 'New Title2' AS CustomTitle
, 'Black' AS BGColor
UNION ALL
SELECT 3 AS [ID]
, 3 AS ModuleID
, 1 AS UserID
, 'New Title3' AS CustomTitle
, 'Orange' AS BGColor
UNION ALL
SELECT 4 AS [ID]
, 4 AS ModuleID
, 1 AS UserID
, 'New Title4' AS CustomTitle
, 'Yellow' AS BGColor
) ModuleUserSettings

SELECT *
INTO #Users
FROM (
SELECT 1 AS UserID
, 1 AS UserType
UNION ALL
SELECT 2 AS UserID
, 2 AS UserType

) Users

DECLARE @.UserType int
DECLARE @.UserID int
DECLARE @.isVisible varchar(5)

SET @.UserType = 1
SET @.UserID = 1
SET @.isVisible = 'True'

SELECT DISTINCT
m.ModuleID
, m.ModuleName
, mus.CustomTitle
, mus.BGColor
FROM #Modules m LEFT OUTER JOIN
#ModuleUserTypes mut ON m.ModuleID = mut.ModuleID LEFT OUTER JOIN
#ModuleUserSettings mus ON m.ModuleID = mus.ModuleID
AND mut.ModuleID = mus.ModuleID LEFT OUTER JOIN
#Users ut ON mut.UserType = ut.UserType
AND mus.UserID = ut.UserID
WHERE ISNULL(mut.UserType,@.UserType) = @.UserType
AND ISNULL(mus.UserID,@.UserID) = @.UserID
AND ISNULL(m.isVisible,@.isVisible) = @.isVisible

DROP TABLE #Modules
DROP TABLE #ModuleUserTypes
DROP TABLE #ModuleUserSettings
DROP TABLE #Users|||

select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolour

from Modules m

join moduleusertypes mut

on m.moduleid = mut.moduleid

and mut.usertype = 1

left join ModuleUserSettings mus

on mus.moduleid = mut.moduleid

and mus.userid = mut.usertype

where IsVisible = 1

Assuming userid in 'ModuleUserSettings' is equal to UserType in 'ModuleUserTypes'

Regards,

kwareol

|||Thanks Nara's for your reply. I tried a similar statement but it was not filtering correctly. It would work until I added the UserType=1 and isVisible=1 to the where clause.
|||Kwareol,

Your statement took me in the right direction!

All I needed to add was the UserID filter.

This is the final statement that works exactly as I needed:

select m.ModuleId,m.ModuleName ,mus.Customtitle,mus.bgcolor

from Modules m

join moduleusertypes mut

on m.moduleid = mut.moduleid

and mut.usertype = 1

left join ModuleUserSettings mus

on mus.moduleid = mut.moduleid

and (mus.userid = 1 or mus.userid is null)

where IsVisible = 1

Thank you and everybody so much for your time and efforts!! It's much much appreciated!

(I marked this post as the final answer. I'm not exactly sure how this forums works and if users get some sort of point ranking for posting correct answers. If so, I will change it to Kwareol for him leading me in the direction I needed to go)

3 columns as 1

I have three columns which I would like to have return as one column BUT have each column add its collection result to the end of the previous one.

Example

Col1 col2 col3

Happy Fun Land

Wally World Here

Result set would be as below

ColAlis

Happy

Wally

Fun

World

Land

Here

I can do this need at a code level on my application but i would rather the SQL statement do it.

Thanks

Here it is

Code Snippet

Create Table #data (

[Col1] Varchar(100) ,

[col2] Varchar(100) ,

[col3] Varchar(100)

);

Insert Into #data Values('Happy','Fun','Land');

Insert Into #data Values('Wally','World','Here');

For SQL Server 2000

Code Snippet

Select Col1 as [Values] From #Data

Union All

Select Col2 From #Data

Union All

Select Col3 From #Data

For SQL Server 2005

Code Snippet

Select [Values] from #Data

Unpivot

(

[Values] for Cols in (Col1,Col2,Col3)

) as Upvt Order By Cols

|||

Code Snippet

create table #t (Col1 varchar(10), col2 varchar(10), col3 varchar(10))

insert into #t

select 'Happy', 'Fun', 'Land'

union all select 'Wally', 'World', 'Here'

select Items

from

(select * from #t) p

unpivot

( items for data in (col1, col2, col3) ) as x

OR

Code Snippet

select col1 from #t

union all select col2 from #t

union all select col3 from #t

|||Try this:

SELECT col1+' '+col2+' '+col3 FROM yourtable

Have a nice day!

Edited:
I'm sorry, didn't read the desired result hehe.
This is not an option...|||

Try:

Code Snippet

declare @.t table (

col1 varchar(25),

col2 varchar(25),

col3 varchar(25)

)

insert into @.t values('Happy', 'Fun', 'Land')

insert into @.t values('Wally', 'World', 'Here')

select

t2.c1 as col,

case

when t2.c1 = 1 then col1

when t2.c1 = 2 then col2

when t2.c1 = 3 then col3

end as [value]

from

@.t as t1

cross join

(select 1 as c1 union all select 2 union all select 3) as t2

order by

col,

[value]

-- 2005

select

col,

[value]

from

(select col1 as [1], col2 as [2], col3 as [3] from @.t) as p

unpivot

([value] for col in ([1], [2], [3])) as unpvt

order by

col,

[value]

go

AMB

|||

Excellent suggestions Mani, Dale, Alejandro!

However, both solutions 'almost' produce the same resultset -not quite what the OP asked.

While two solutions (SQL 2000 / SQL 2005) was not specifically requested, it seems that if two solutions are provided, that they should both provide the same resultset to address the OP's problem. It's just a matter of quality control.

It seems that the 'closest' solution may perhaps be the simplest solution -adding an artificial grouping indicator. Since with the OP's data, there is no way to precisely determine the order of a column, either we have to conclude that we cannot derive an accurate solution for the problem, or we take corrective actions.

Here is a suggestion that 'should' alleviate the ordering problem:

Code Snippet

DECLARE @.Data table
( Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);

Insert Into @.Data Values ( 'Happy', 'Fun', 'Land' );
Insert Into @.Data Values ( 'Wally', 'World', 'Here' );

DECLARE @.Data2 table
( RowOrder int IDENTITY,
Col1 varchar(20) ,
Col2 varchar(20) ,
Col3 varchar(20)
);

INSERT INTO @.Data2
SELECT *
FROM @.Data;

--Works in both SQL 2000/2005

SELECT
1 AS Collection,
RowOrder,
Col1 AS [Values]
FROM @.Data2

Union All

SELECT
2 AS Collection,
RowOrder,
Col2 AS [Values]
FROM @.Data2

Union All

SELECT
3 AS Collection,
RowOrder,
Col3 AS [Values]
FROM @.Data2

ORDER BY
Collection,
RowOrder

Collection RowOrder Values
-- -- --
1 1 Happy
1 2 Wally
2 1 Fun
2 2 World
3 1 Land
3 2 Here

Of course, there 'could' be a slight reorganization of the data order in the process of inserting from the original table into the 'identified' table (@.Data2) -but under most circumstances, that should be minimal.

(Dale, Alejandro -please send me an email -Arnie)

|||

Hi Arnie,

I am using "order by" clause to be consistent, but the result is lightly different from what the OP expected (values from col3 - 'Here', 'Land'). There is not an easy way to use a row number, to know from which row is every value coming from.

Thanks,

Alejandro Mesa

|||

You're absolutely correct Arnie.

Thanks!

I think I'm in need of a vacation

|||

Hi Arnie,

I agree with you, but I wonder how are you assuring that the data will be inserted into the second table in the same order you are inserting it into the first one.

> INSERT INTO @.Data2

> SELECT * FROM @.Data;

this statement does not guarantee that the row ( 'Happy', 'Fun', 'Land' ) will be the first one. Just add a clustered index (we need a table and not a table variable in order to do this) to table [Data], by [col1] DESC and that solution will fail. Check also the attached link.

Code Snippet

create table dbo.Data

( Col1 varchar(20) ,

Col2 varchar(20) ,

Col3 varchar(20)

);

Insert Into dbo.Data Values ( 'Happy', 'Fun', 'Land' );

Insert Into dbo.Data Values ( 'Wally', 'World', 'Here' );

go

create clustered index Data_col1_nu_c_ix

on dbo.Data(col1 DESC)

go

create table dbo.Data2

( RowOrder int IDENTITY,

Col1 varchar(20) ,

Col2 varchar(20) ,

Col3 varchar(20)

);

INSERT INTO dbo.Data2

SELECT *

FROM dbo.Data;

--Works in both SQL 2000/2005

SELECT

1 AS Collection,

RowOrder,

Col1 AS [Values]

FROM dbo.Data2

Union All

SELECT

2 AS Collection,

RowOrder,

Col2 AS [Values]

FROM dbo.Data2

Union All

SELECT

3 AS Collection,

RowOrder,

Col3 AS [Values]

FROM dbo.Data2

ORDER BY

Collection,

RowOrder

go

drop table dbo.Data, dbo.Data2

go

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

http://support.microsoft.com/kb/273586

AMB

|||

Alejandro,

I agree totally, since the OP didn't provide any indication of sequecing, positioning is totally happenstance.

Without a definitive sequencing indicator on the primary table, and without the option to alter the primary table to add such (PK, IDENTITY, Index, etc.), there is little that can be done to absolutely ensure sequence. The only option is to temporarily assert sequencing -as my example of moving into a temp table with IDENTITY. Whether or not it the sequence matches the primary table is totally by accident -BUT sequence can then be maintained for additional activities using the temp table. It just may be a different sequence the 'next time'.

|||

I haven’t dug deep into these solutions so I apologize in advance if I ask for clarification of what it does vs. what I need.

What I don’t want

Record 1 = 'HappyFunLand'

Record 2 = 'WallyWorldHere'

I want one column returned that says

Happy

Wally

Fun

World

Land

Here

The order I have listed above if look closely is actually contents of col1 then append contents of col2 then append contents of col3 all returned as one column.

So in the example below

insert into @.t values('Happy', 'Fun', 'Land')

insert into @.t values('Wally', 'World', 'Here')

I would be inserting only the first two rows of the original table from my understanding

Here is a more specific example of the real table

Column 1 = EnduserEmail

Column 2 = TechEmail

Column3 = managerEmail

I want to populate one dropdown with all emails from all columns without doing any coding which I could do but I would rather keep it on SQL if I can.

If your SQL statement or someone else’s does this I thank you all greatly, just point me to the correct post number.

Reason I am clarifying myself is only because I don’t want to learn the statement only to find it doesn’t do what I am looking for.

Thanks again!

|||

Sorry everyone I think I figured it out.

Example below

select Email1 from myTable

union all
Select Email2 from myTable

union all

Select Email3 from myTable

Orderby would simply be alpha numeric in my case which i assume is standard orderby.

Sorry I completely forgot about UNION statement

|||

Sean,

There is NO 'standard' sorting or ordering UNLESS you specifically use an ORDER BY.

Without the ORDER BY, it is just by chance.

|||

Right, what I ment to say was as long as a standard ORDER BY statement would work the same in the context of UNION statement as it would with a simple SELECT statement.

And it did perfectly.

Thanks again I have my sproc now.

2q - Indexs and Optimize...

Dont know if that is the right group ... but here goes
2 questions:
1. What are the diffenrence on adding 1 column per Index, or adding more
columns per index... i'm a little confused here. Adn waht ORDER BY? is
that just the default ordering rule when doing "ORDER BY column"?
2. Are there any easy why to examine sql queries comming to the server
so its easier to debug what quries are slow ... ? so its possible to
make some optimazations on them ...
kind regards
Mikael SyskaAs far as 1) goes theres a world of difference. I would suggest reading
about indexes, if you have the time 'Inside Sql server 2000' would probably
give you all the answers you need.
2) you can use Profiler tool. Trace queries and possibly filter them by
duration value. If you put over 3000 you'll see only queries that last over
3 seconds...
MC
"Mikael Syska" <news01@.syska.dk> wrote in message
news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Dont know if that is the right group ... but here goes
> 2 questions:
> 1. What are the diffenrence on adding 1 column per Index, or adding more
> columns per index... i'm a little confused here. Adn waht ORDER BY? is
> that just the default ordering rule when doing "ORDER BY column"?
> 2. Are there any easy why to examine sql queries comming to the server so
> its easier to debug what quries are slow ... ? so its possible to make
> some optimazations on them ...
> kind regards
> Mikael Syska|||Mikael:
When people ask me this question (#1), I always say, look at the phone
book. You can think of a phone book as being like a 2-way composite
index on Lastname (Ascending) and Firstname (Ascending), in that order:
Adams, John
Bryant, Frank
Coolidge, Adam
Coolidge, Calvin
Coolidge, Zach
If you ordered it as Lastname (Ascending) and Firstname (Descending):
Adams, John
Bryant, Frank
Coolidge, Zach
Coolidge, Calvin
Coolidge, Adam
If you ordered it as First name (Ascending) ...
Coolidge, Adam
Coolidge, Calvin
Bryant, Frank
Adams, John
Coolidge, Zach
Get the idea?
MC wrote:
> As far as 1) goes theres a world of difference. I would suggest reading
> about indexes, if you have the time 'Inside Sql server 2000' would probably
> give you all the answers you need.
> 2) you can use Profiler tool. Trace queries and possibly filter them by
> duration value. If you put over 3000 you'll see only queries that last over
> 3 seconds...
>
> MC
> "Mikael Syska" <news01@.syska.dk> wrote in message
> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding more
>> columns per index... i'm a little confused here. Adn waht ORDER BY? is
>> that just the default ordering rule when doing "ORDER BY column"?
>> 2. Are there any easy why to examine sql queries comming to the server so
>> its easier to debug what quries are slow ... ? so its possible to make
>> some optimazations on them ...
>> kind regards
>> Mikael Syska
>|||Yes, I think so ...
So an index on FirstName DESC, LastName ASC
and the query ...
SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
would are the list after that index ... ASC on FirstName and DESC on
LastName ?
and if I had a other index on FirstName ASC and did a
SELECT Firname, LastName FROM t1 ORDER BY FirstName
it would sort DESC ... right ? if i guess they are grouped ...
But are there any performance gain by doing it that way (then some
columns would be added more than 1 time) ?
best regrads
Mikael Syska
David Markle wrote:
> Mikael:
> When people ask me this question (#1), I always say, look at the phone
> book. You can think of a phone book as being like a 2-way composite
> index on Lastname (Ascending) and Firstname (Ascending), in that order:
> Adams, John
> Bryant, Frank
> Coolidge, Adam
> Coolidge, Calvin
> Coolidge, Zach
> If you ordered it as Lastname (Ascending) and Firstname (Descending):
> Adams, John
> Bryant, Frank
> Coolidge, Zach
> Coolidge, Calvin
> Coolidge, Adam
> If you ordered it as First name (Ascending) ...
> Coolidge, Adam
> Coolidge, Calvin
> Bryant, Frank
> Adams, John
> Coolidge, Zach
> Get the idea?
> MC wrote:
>> As far as 1) goes theres a world of difference. I would suggest
>> reading about indexes, if you have the time 'Inside Sql server 2000'
>> would probably give you all the answers you need.
>> 2) you can use Profiler tool. Trace queries and possibly filter them
>> by duration value. If you put over 3000 you'll see only queries that
>> last over 3 seconds...
>>
>> MC
>> "Mikael Syska" <news01@.syska.dk> wrote in message
>> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding
>> more columns per index... i'm a little confused here. Adn waht ORDER
>> BY? is that just the default ordering rule when doing "ORDER BY column"?
>> 2. Are there any easy why to examine sql queries comming to the
>> server so its easier to debug what quries are slow ... ? so its
>> possible to make some optimazations on them ...
>> kind regards
>> Mikael Syska
>>|||"Mikael Syska" <news01@.syska.dk> wrote in message
news:eS7AKXsXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Yes, I think so ...
> So an index on FirstName DESC, LastName ASC
> and the query ...
> SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
> would are the list after that index ... ASC on FirstName and DESC on
> LastName ?
No, ASC on both. An ORDER BY is ASCending unless otherwise specified.
However, the query plans will be different.
Given this script:
create table order_test
( fname varchar(10),
lname varchar(10))
insert into order_test values ('albert', 'zilch')
insert into order_test values ('albert', 'allen')
insert into order_test values ('albert', 'Brend')
insert into order_test values ('Bill', 'zilch')
insert into order_test values ('Bill', 'Cramden')
insert into order_test values ('Wendy', 'Jillson')
create index fname_idx on order_test (fname asc, lname desc)
go
set showplan_all on
go
select * from order_test order by fname, lname
select * from order_test order by fname, lname desc
drop table order_test
Since we're ordering the results in "reverse" of the index, we have an extra
scan in there.
creating an INDEX with DESC is useful when you will generally return the
results in DESC order. It's just an optimization step.
select * from order_test order by fname, lname
|--Sort(ORDER BY:([testing].[dbo].[order_test].[fname] ASC,
[testing].[dbo].[order_test].[lname] ASC))
|--Index Scan(OBJECT:([testing].[dbo].[order_test].[fname_idx]))
select * from order_test order by fname, lname desc
|--Index Scan(OBJECT:([testing].[dbo].[order_test].[fname_idx]), ORDERED
FORWARD)
> and if I had a other index on FirstName ASC and did a
> SELECT Firname, LastName FROM t1 ORDER BY FirstName
> it would sort DESC ... right ? if i guess they are grouped ...
> But are there any performance gain by doing it that way (then some columns
> would be added more than 1 time) ?
> best regrads
> Mikael Syska
> David Markle wrote:
>> Mikael:
>> When people ask me this question (#1), I always say, look at the phone
>> book. You can think of a phone book as being like a 2-way composite
>> index on Lastname (Ascending) and Firstname (Ascending), in that order:
>> Adams, John
>> Bryant, Frank
>> Coolidge, Adam
>> Coolidge, Calvin
>> Coolidge, Zach
>> If you ordered it as Lastname (Ascending) and Firstname (Descending):
>> Adams, John
>> Bryant, Frank
>> Coolidge, Zach
>> Coolidge, Calvin
>> Coolidge, Adam
>> If you ordered it as First name (Ascending) ...
>> Coolidge, Adam
>> Coolidge, Calvin
>> Bryant, Frank
>> Adams, John
>> Coolidge, Zach
>> Get the idea?
>> MC wrote:
>> As far as 1) goes theres a world of difference. I would suggest reading
>> about indexes, if you have the time 'Inside Sql server 2000' would
>> probably give you all the answers you need.
>> 2) you can use Profiler tool. Trace queries and possibly filter them by
>> duration value. If you put over 3000 you'll see only queries that last
>> over 3 seconds...
>>
>> MC
>> "Mikael Syska" <news01@.syska.dk> wrote in message
>> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>> Dont know if that is the right group ... but here goes
>> 2 questions:
>> 1. What are the diffenrence on adding 1 column per Index, or adding
>> more columns per index... i'm a little confused here. Adn waht ORDER
>> BY? is that just the default ordering rule when doing "ORDER BY
>> column"?
>> 2. Are there any easy why to examine sql queries comming to the server
>> so its easier to debug what quries are slow ... ? so its possible to
>> make some optimazations on them ...
>> kind regards
>> Mikael Syska
>>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

2ND POST: Is there a way to insert a record using another as a tem

Hi
If you specify the column list then you insert values into those columns.
Missing columns will be set to NULL (if it is allowed) or a default value.
To update existing data use the UPDATE statement, you will only change the
values for columns specified in the SET clause. More information on the
INSERT and UPDATE statements can be found in Books Online.
Using stored procedure may help to remove any issues with different versions
as you will ship the correct procedure with the changes to the schema.
You can look at the INFORMATION_SCHEMA.columns view to find out what columns
a table has, although a different approach would be to only provide a
solution for the latest version and check they are on that version otherwise
force an upgrade.
John
"SA Development" wrote:

> Hi,
> I am currently inserting a record using:
> insert into TABLE(FIELD1,FIELD2) values ('%s',%d)
> But, the problem I have is that the table I am connecting to can either ha
ve
> 46 fields or 47 fields depending on its version. Is there a command that
> would allow me to use an existing record as a template and only specified
> the fields I want to change and then insert the new record?
> Something like:
> insert into TABLE(FIELD1) value ('%s') using template record where
> FIELD1='9999999' ?
> Thanks,
> SA Dev
>
>Thanks for the good ideas everyone, sorry my first post wasn't entirely
clear.
Lets say I have a table like this:
field1(key) field2 field3 field4 field5
9999 a b c d
What I was hoping to do is add a new record (field1=123 for example), but
use the 9999 as a template record so that its field values are inserted into
my new record EXCEPT for any that I override.
It sounds like the update command will let me update just the fields I want,
is there a way to tell SQL to copy record 9999 to record 123 and use all the
field values in record 9999 ?
Thanks,
Alan|||Hi David,

> INSERT INTO YourTable (col1, col2, col3, col4, col5)
> SELECT 123, col2, col3, col4, col5
> FROM YourTable
> WHERE col1 = 9999
Thank you -- that is exactly what I needed!
Thanks to everyone else who posted as well.
Have a great day,
Alan

2005-Problem restoring database with encrypted columns to diff ser

I need to start encrypting several fields in a database and have been doing
some testing with a test database first. I've run into problems when
attempting to restore the database on either the same server (but different
database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the
original database:
create master key
encryption by password = 'testAppleA3';
I also saw a posting here where it referenced running the below first:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
However when i then try to open the key it stil gives an error:
An error occurred during decryption.
create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane
Doe',encryptbykey(key_guid('sk_Test'),'$
200000'));
insert into employees values(102,'Bob
Jones',encryptbykey(key_guid('sk_Test'),
'$500000'));
select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from
employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a
different server (same issue if restore to different database but on same
server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first
in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and
then symmetric key:
drop symmetric key sk_test
drop certificate test
drop master key
Then recreate the master key:
create master key
encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test
FROM FILE = 'c:\storedcerts\encryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it
has no private key or because the password provided for the private key is
incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost
all access to decrypt the data in the database despite restoring from a
backup which restored the symmetric key and certificate and i obviously know
the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
again but this does not resolve the issue.So now I'm confused.
Where this did not work is when i created the intial database using SQL
Server Express edition on a Win XP workstation and then was restoring to a
database located on a SQL Server Standard edition on a Windows 2003 Server.
I just tried creating everything from scratch on one WIndows 2003 Server
with SQL Server Standard edition and then restoring that database to a
different Windows 2003 Server also with SQL Server Standard and this time it
worked as long as i did the below first:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
My question (comes to reliabilty concerns) is why did it not work when i did
:
Orig database SQLSvr Express on Win XP
Restore to SQL Svr Std on Win2003Svr
But did work when I did
Orig database SQL Svr Std on Win 2003Svr
Restore to different SQL Svr Std on different Win 2003 Svr
What would the reason for this be?
Thx.
"Robert" wrote:

> I need to start encrypting several fields in a database and have been doin
g
> some testing with a test database first. I've run into problems when
> attempting to restore the database on either the same server (but differen
t
> database) or to a separate server.
> First, here's how i created the symmetric key and encrypted data in the
> original database:
> create master key
> encryption by password = 'testAppleA3';
> I also saw a posting here where it referenced running the below first:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
> CLOSE MASTER KEY;
> However when i then try to open the key it stil gives an error:
> An error occurred during decryption.
>
> create certificate test
> with subject = 'test certificate',
> EXPIRY_DATE = '1/1/2010';
> create symmetric key sk_Test
> with algorithm = triple_des
> encryption by certificate test;
> open symmetric key sk_Test decryption by certificate test;
> insert into employees values (101,'Jane
> Doe',encryptbykey(key_guid('sk_Test'),'$
200000'));
> insert into employees values(102,'Bob
> Jones',encryptbykey(key_guid('sk_Test'),
'$500000'));
> select * from employees
> --delete from employees
> select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from
> employees
> close all symmetric keys
> Next I backup up this test database and restore it to a new database on a
> different server (same issue if restore to different database but on same
> server).
> Then if i attempt to open the key in the new database and decrypt:
> open symmetric key sk_Test decryption by certificate test;
> I get the error: An error occurred during decryption.
> Ok, well not unexpected, so reading the forums, i try doing the below firs
t
> in the new database:
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
> Then I try opening the key again and get the error again:
> An error occurred during decryption.
> So then it occurs to me, maybe i need to drop and recreate it so i do
> drop symmetric key sk_test
> then
> create symmetric key sk_Test
> with algorithm = triple_des
> encryption by certificate test;
> and then try to open it.
> Same error!
> So then i decide, let's drop everything, the master key, the certificate a
nd
> then symmetric key:
> drop symmetric key sk_test
> drop certificate test
> drop master key
> Then recreate the master key:
> create master key
> encryption by password = 'testAppleA3';
> Restore the certificate from a backup i had made to a file:
> CREATE CERTIFICATE test
> FROM FILE = 'c:\storedcerts\encryptiontestcert'
> Recreate the symmetric key again:
> create symmetric key sk_Test
> with algorithm = triple_des
> encryption by certificate test;
> And now open the key only to get the error:
> Cannot decrypt or encrypt using the specified certificate, either because
it
> has no private key or because the password provided for the private key is
> incorrect.
> So what am I doing wrong here? In this scenario I would appear to have lo
st
> all access to decrypt the data in the database despite restoring from a
> backup which restored the symmetric key and certificate and i obviously kn
ow
> the password for the master key.
> I also tried running the command
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
> again but this does not resolve the issue.
>|||Hello Robert,
I was not able to reproduce the issue on my side.
On a SQL Express instance, create a database/table, Master key,
Certificate, symmetric key and insert the record with encrypted column.
Backup/restore to the SQL Enterprise edition instance. I could run the
following query successfully on the restored database:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
open symmetric key sk_Test decryption by certificate test;
It seems the issue is related to SQL service account. Is the service
account the same on both SQL Express and SQL Enterprise instances?
Master key protected by old service account credentials may need to be
protected by the current one. You can use the alter service master key
statement to enter the old credentials and have it be protected by the
current account.
Also, you may want to use same Windows account to start SQL instances to
test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: 2005-Problem restoring database with encrypted columns to
diff ser
>thread-index: AcYgUZjYietJilQUTrOvZ82WtyHCWw==
>X-WBNR-Posting-Host: 159.37.7.48
>From: examnotes <robertv@.noemail.nospam>
>References: <4CB8E9AA-99DF-44A1-8375-BDAB8B69A586@.microsoft.com>
>Subject: RE: 2005-Problem restoring database with encrypted columns to
diff ser
>Date: Mon, 23 Jan 2006 11:17:05 -0800
>Lines: 156
>Message-ID: <E0B161AC-6911-45DE-8100-C42FA028BB1F@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.security
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26209
>X-Tomcat-NG: microsoft.public.sqlserver.security
>So now I'm confused.
>Where this did not work is when i created the intial database using SQL
>Server Express edition on a Win XP workstation and then was restoring to a
>database located on a SQL Server Standard edition on a Windows 2003 Server.
>I just tried creating everything from scratch on one WIndows 2003 Server
>with SQL Server Standard edition and then restoring that database to a
>different Windows 2003 Server also with SQL Server Standard and this time
it
>worked as long as i did the below first:
>OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';
>ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
>CLOSE MASTER KEY;
>My question (comes to reliabilty concerns) is why did it not work when i
did:
>Orig database SQLSvr Express on Win XP
>Restore to SQL Svr Std on Win2003Svr
>But did work when I did
>Orig database SQL Svr Std on Win 2003Svr
>Restore to different SQL Svr Std on different Win 2003 Svr
>What would the reason for this be?
>Thx.
>
>"Robert" wrote:
>
doing[vbcol=seagreen]
different[vbcol=seagreen]
a[vbcol=seagreen]
same[vbcol=seagreen]
first[vbcol=seagreen]
and[vbcol=seagreen]
because it[vbcol=seagreen]
is[vbcol=seagreen]
lost[vbcol=seagreen]
know[vbcol=seagreen]
>

2005 Query help

Please help.

On sql 2000 i have a query like this where the columns are primary keys.

Select count(*) from db.dbo.table1

where convert(varchar(3), col1) + convert(varchar(10), col2) not in

(select convert(varchar(3), col1) + convert(varchar(10), col2) from db.dbo.table2)

It completes in 1 second with sql 2000. I have restored the db to sql 2005 and run the same query. The processors peg and it goes to la la land. I have updated statistics and installed SP1. Anyone have ideas? The sql 2005 is even way better than the sql 2000 box. If you have a better way to perform the same task, please let me know.

Thanks!

can you compare execution plans on both servers? That should give a head start.|||

Hey,

I did compare the execution plans. For some reason, the 2005 execution plan has more to it and mentions parallelism. It just does not run the query. The box just maxes out and stays maxed out. Very strange.

I've rebooted the box for the heck of it and it doesn't matter. It doesn't want to run. Thanks for the response.

|||Moving to T-SQL forum. Maybe there's a way to rewrite the query.|||

Try this using EXISTS:

select *
from db.dbo.table1 as table1
where not exists (select *
from db.dbo.table2 as table2
where table1.col1 = table2.col1
and table1.col2 = table2.co2)

This should perform better and give the same results (actually more correct, because the varchar conversions could in some rare cases given invalid values).

As for the long run times, How much data is involved? One of the problems I have run into is a lot of waits during parallel operations when some larger operations go parallel. I had to tune some of my data warehouse queries by setting MAXDOP to 1.

I found this by executing this query:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

And checking the wait type. Lots of huge CXPACKET waits. Once you get into the wait, watch the results here and post them. You can see where the execution is at by watching the currentExecutingCommand column (it is really cool to watch when you aren't stuck :)

|||

Hey Louis. Thanks for the response!

The exists does work very well. I use that most of the time. I also will put a hyphen between the converts to help avoid getting errors when I do use the "in" style.

I tried running the query you posted on the 2005 box and it complains about '.' near the end of the query saying incorrect syntax. I don't see why though.

It's very strange though how they will be handled so differently between 2000 and 2005.

Thanks again!

|||

The other concern with the IN style is indexing. If you put values in functions or expressions it invalidates use of indexes. But putting seperators that cannot exist in the data will make it "technically" safe.

I took that query verbatim and ran it on my express instance and it worked fine. It is version:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I will try it on my 2005 SP1 box and make sure it works there, but that is interesting.

|||

Hey Louis. Thanks for the info.

Thanks too for trying it on your boxes!

One of my tables has 1.4 million records and the other has about 40K.

Thanks

2005 perf much worse than 2000... suggestions please..

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas?

Here is the procedure...

declare @.srch_field varchar(8000)

declare @.row int, @.productid varchar(25)

DECLARE @.title varchar(150), @.actors_keyname varchar(1200), @.directors_name varchar(400)

Declare @.genres varchar(700), @.theme varchar(1500), @.type varchar(1500), @.studio_desc varchar(100)

DECLARE @.media_format varchar(50), @.artist_name varchar(100), @.dev_name varchar(100)

DECLARE @.flags varchar(256), @.starring varchar(256), @.esrb varchar(100), @.esrb_desc varchar(500)

DECLARE @.ptrval varbinary(16), @.text varchar(max)

declare @.productlist table(product_id varchar(25), IDNUM int identity)

insert into @.productlist (product_id)

select product_id

from music_load..globalsearch

select @.row = @.@.rowcount

while @.row > 0

begin

select @.productid = product_id

from @.productlist

where idnum = @.row

SELECT @.title = rtrim(title) ,

@.actors_keyname = actors_keyname ,

@.directors_name = directors_name,

@.genres = genres ,

@.theme = theme ,

@.type = type ,

@.studio_desc = studio_desc,

@.media_format = media_format ,

@.artist_name = artist_name,

@.dev_name = dev_name,

@.flags = flags ,

@.starring =starring ,

@.esrb = esrb ,

@.esrb_desc = esrb_desc

FROM globalsearch

where product_id = @.productid

Set @.srch_field = isnull(@.title,'')

if @.actors_keyname is not null and @.actors_keyname <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.actors_keyname)

if @.directors_name is not null and @.directors_name <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.directors_name)

if @.genres is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.genres, 0,''))))

if @.theme is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.theme, 0,''))))

if @.type is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.type, 0,''))))

if @.studio_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.studio_desc)

if @.media_format is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.media_format)

if @.artist_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.artist_name)

if @.dev_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.dev_name)

if @.flags is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.flags)

if @.starring is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.starring)

if @.esrb is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb)

if @.esrb_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb_desc)

update globalsearch

set srch_field = @.srch_field

where product_id = @.productid

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = credits

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = track

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

set @.row = @.row - 1

end

Text fields are going away in 2005. The first thing to do is change srch_field to a varchar(max) type.

The second thing is to make the update one UPDATE command instead of steping thru them one at a time. You don't need a cursor or a temp table or a loop at all. Do something like this:

UPDATE globalsearch
SET srch_field = isnull(rtrim(title),'') +
'~' + isnull(CASE WHEN actors_keyname <> 'unknown' THEN actors_keyname ELSE NULL END,'') +
'~' + isnull(CASE WHEN directors_name <> 'unknown' THEN directors_name ELSE NULL END,'') +
'~' + isnull((ltrim(rtrim(replace(@.genres, 0,'')))),'') +
......|||

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

|||

William Lowers wrote:

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

I don't quite understand your question.

You should change all the TEXT datatypes to VARCHAR(MAX). Then they are just strings and you can use string concatination on them directly just like any other string.|||

You are correct... for some reason I remember trying the string concatination and it didn't work. no idea when or where that was but I changed to all varchar(max) and made a single update statement. now the procedure runs in under 2 minutes...

Thanks a lot.

2005 fti query performance issue

I have a 10 million row table with two columns(name, address) full text
indexed. I want to search the table for
the first 200 occurances of "Bob jones" in the name field. I have ran
another query to find out that there are actully 15,291 rows with "Bob Jones"
in the name field. I want just the first 200.
When I run the FT search, it takes 2 minutes for the query to finish. When I
run a simple query
(select * from tst where name = 'Bob jones'),
the query comes back in under two seconds. It appears that the query is
searching the whole catalog before going to the data table. Is this expected?
How can I get the query to search the catalog and stop after the first 200
occurances are found?
Here is the query I am using:
Select name, address from tst
where contains(Name, '"Bob Jones"')
Table schema is as follows:
create table tst(
uid int identity(1,1),
Name varchar(255),
address varchar(255)
)
use containstable. It allows you to limit your results set to the first 200
or so rows.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:51042F5E-9BCF-43AE-A232-73670F90EEEB@.microsoft.com...
>I have a 10 million row table with two columns(name, address) full text
> indexed. I want to search the table for
> the first 200 occurances of "Bob jones" in the name field. I have ran
> another query to find out that there are actully 15,291 rows with "Bob
> Jones"
> in the name field. I want just the first 200.
> When I run the FT search, it takes 2 minutes for the query to finish. When
> I
> run a simple query
> (select * from tst where name = 'Bob jones'),
> the query comes back in under two seconds. It appears that the query is
> searching the whole catalog before going to the data table. Is this
> expected?
> How can I get the query to search the catalog and stop after the first 200
> occurances are found?
> Here is the query I am using:
> Select name, address from tst
> where contains(Name, '"Bob Jones"')
> Table schema is as follows:
> create table tst(
> uid int identity(1,1),
> Name varchar(255),
> address varchar(255)
> )
|||Carl,
You should be able to use TOP x in your select list, where x is 200 in your
SQL 2005 FTS query, for example:
Select TOP 200 name, address from tst
where contains(Name, '"Bob Jones"')
You may think that using CONTAINSTABLE (or FREETEXTTABLE) with Top_N_Rank
would also meet your requirement of getting the "first 200 occurrences of
"Bob jones" in the name field" (emphasis on first), but this would be
incorrect. The Top_N_Rank parameter limits the top number of rows by RANK
and not the *first* rows that contain your search phrase.
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:51042F5E-9BCF-43AE-A232-73670F90EEEB@.microsoft.com...
> I have a 10 million row table with two columns(name, address) full text
> indexed. I want to search the table for
> the first 200 occurances of "Bob jones" in the name field. I have ran
> another query to find out that there are actully 15,291 rows with "Bob
Jones"
> in the name field. I want just the first 200.
> When I run the FT search, it takes 2 minutes for the query to finish. When
I
> run a simple query
> (select * from tst where name = 'Bob jones'),
> the query comes back in under two seconds. It appears that the query is
> searching the whole catalog before going to the data table. Is this
expected?
> How can I get the query to search the catalog and stop after the first 200
> occurances are found?
> Here is the query I am using:
> Select name, address from tst
> where contains(Name, '"Bob Jones"')
> Table schema is as follows:
> create table tst(
> uid int identity(1,1),
> Name varchar(255),
> address varchar(255)
> )

2005 ft index help

I have numerous vldb's that my mgmt want to have full text indexed. Usually
there are betwen two ad four columns per table that would need the FTI put on
them. FTI on sql2000 failed miserably as it was slow and my tables were
probably too large (smallest is 10 million rows and largest is 800 million
rows).
I would like to know if 2005 FTI, since its been incorporated into the sql
engine, has had its upper limits increased in regards to the size of db can
be indexed.
also, can you replicate a FTI to another server along with its table?
thanks!!!
I believe it was tested to 20 million rows, if not more.
Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
Hilary
"Carl Henthorn" wrote:

> I have numerous vldb's that my mgmt want to have full text indexed. Usually
> there are betwen two ad four columns per table that would need the FTI put on
> them. FTI on sql2000 failed miserably as it was slow and my tables were
> probably too large (smallest is 10 million rows and largest is 800 million
> rows).
> I would like to know if 2005 FTI, since its been incorporated into the sql
> engine, has had its upper limits increased in regards to the size of db can
> be indexed.
> also, can you replicate a FTI to another server along with its table?
> thanks!!!
|||that is not very comforting. Why is FT not tested against larger databases?
Is there a max size to the catalog? is there a peformance issue?
"Hilary Cotter" wrote:
[vbcol=seagreen]
> I believe it was tested to 20 million rows, if not more.
> Yes, you can replicate SQL Server 2005 Full Text Indexes and their tables.
> Hilary
> "Carl Henthorn" wrote:
|||I am not sure what the limits that Microsoft has tested it against.
My statistic comes from here:
http://msdn.microsoft.com/library/de...05ftsearch.asp
In this sentence:
.. For example, on the same hardware, with the same data set, building a
full-text index on 20 million rows of character-based text data took roughly
14 days in SQL Server 2000, while in SQL Server 2005, the same index
required less than 10 hours.
But reading on I see this:
SQL Server 2005 full-text catalogs, by contrast, have been tested with and
can support up to 2,000,000,000 rows of data (based on the 4-byte internal
DocId). Further, the indexing process also scales up to that amount of data
on a larger number of CPUs. Scalability of the text engine at indexing time
over multiple CPUs has also improved significantly over previous releases
(the full-text engine scales well up to roughly 16 CPUs on a 32-bit
platform).
I take it that is not a typo and Andrew means 2 billion.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0EF93B7D-A916-4BB3-B171-3DF9A29A48A7@.microsoft.com...[vbcol=seagreen]
> that is not very comforting. Why is FT not tested against larger
> databases?
> Is there a max size to the catalog? is there a peformance issue?
> "Hilary Cotter" wrote: