MSSQL Mistake: column

Showing posts with label column. Show all posts

Monday, March 26, 2012

multi lingual where clause

I have a table with nvarchar column. In query analyzer when I run query with foreign language words that are already there in database it dose not return any rows

To test it I returned rows with Select * table. Then from result window I copy chinese characters and put it in SQL where clause in SQL analyzer .When I run query it dose not return results.

What could be the cause..its SQL 2k

found it add N infront of characters|||

As kyus94 indicated, to use UNICODE characters, including Chinese, you must preface the string with the character [ N ].

For example,

SELECT

Column1,

Column2,

etc

FROM MyTable

WHERE Column3 = N'ThisCouldBeChinese'

(Note the character immediately before (no space) the string.)

sql

I ran accross something I had not seen before on a MSSQL 2000 db today. All
of the tables had a PK and an index for the PK. In addition there was also
created another Clustered Index on the same PK column. As I have never seen
this before I dropped all existing indexes and put a single Clustered Index
in place on the PK columns. Currently I am wondering if this double index is
something that may have caused some performance issues? All is working great
with the single clustered index.
Thanks, KimHi Kim
The Northwind database has some similar duplicate indexes. Usually, it is
not a good thing. Sometimes it doesn't hurt anything, but if you do a lot of
updates, all those indexes need to be updated along with the data, so it can
actually hurt performance. (Inserts and deletes could have similar
performance issues.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kman" <kman@.toocoolwebs.com> wrote in message
news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> I ran accross something I had not seen before on a MSSQL 2000 db today.
All
> of the tables had a PK and an index for the PK. In addition there was also
> created another Clustered Index on the same PK column. As I have never
seen
> this before I dropped all existing indexes and put a single Clustered
Index
> in place on the PK columns. Currently I am wondering if this double index
is
> something that may have caused some performance issues? All is working
great
> with the single clustered index.
> Thanks, Kim
>||||
| I ran accross something I had not seen before on a MSSQL 2000 db today.
All
| of the tables had a PK and an index for the PK. In addition there was also
| created another Clustered Index on the same PK column. As I have never
seen
| this before I dropped all existing indexes and put a single Clustered
Index
| in place on the PK columns. Currently I am wondering if this double index
is
| something that may have caused some performance issues? All is working
great
| with the single clustered index.
--
Generally you should avoid duplication of indexes to avoid performance
degradation during updates.
--
Eric Cárdenas
SQL Server support|||Thank you Kalen and Eric
Regards,
Kim (Kman)|||If you join by the PK a lot in your queries it would be faster for the
queries to use a non-clustered index for the joining instead of the
clustered index as simply more entries could be stored in the pages of the
NCI. As the others have pointed out this would slow down your
updates/inserts but if you do join to this table a lot and on that column,
it might behoove you to keep a seemingly-redundant index.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Kman" <kman@.toocoolwebs.com> wrote in message
news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> I ran accross something I had not seen before on a MSSQL 2000 db today.
All
> of the tables had a PK and an index for the PK. In addition there was also
> created another Clustered Index on the same PK column. As I have never
seen
> this before I dropped all existing indexes and put a single Clustered
Index
> in place on the PK columns. Currently I am wondering if this double index
is
> something that may have caused some performance issues? All is working
great
> with the single clustered index.
> Thanks, Kim
>|||"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:eg%23KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> If you join by the PK a lot in your queries it would be faster for the
> queries to use a non-clustered index for the joining instead of the
> clustered index as simply more entries could be stored in the pages of the
> NCI. As the others have pointed out this would slow down your
> updates/inserts but if you do join to this table a lot and on that column,
> it might behoove you to keep a seemingly-redundant index.
>
Interesting - so for a table that does a lot of joins, you're saying that
having a NCI on the PK would actually produce faster results?
How much faster (or is that a how long is a piece of string type question) ?|||Best way to test is use the command "set statistics IO on"
Run the query with only the clustered index, look at the logical IO (which
is not the number of pages hit but the number of times a hit occurred to a
page)
Place a non-clustered index on the PK column, re-run the query and look at
the new logical IO. If you are only needing the PK in your query it would be
faster to hit the NCI data pages as they are more compact. If your query
looks for multiple columns from the PK table, it may be more sensible for
SQL to only hit the clustered index and ignore your NCI...so, it all depends
on how your queries are.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Boylett" <parc_erom@.crossdata.co.uk> wrote in message
news:ufeOGGF1DHA.484@.TK2MSFTNGP10.phx.gbl...
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:eg%23KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> > If you join by the PK a lot in your queries it would be faster for the
> > queries to use a non-clustered index for the joining instead of the
> > clustered index as simply more entries could be stored in the pages of
the
> > NCI. As the others have pointed out this would slow down your
> > updates/inserts but if you do join to this table a lot and on that
column,
> > it might behoove you to keep a seemingly-redundant index.
> >
> Interesting - so for a table that does a lot of joins, you're saying that
> having a NCI on the PK would actually produce faster results?
> How much faster (or is that a how long is a piece of string type question)
?
>
>|||Hi Ray
This would only be true if the join was a SEMI-join, checking for existence
in of matching rows in the inner table. If you were doing a true inner join,
and you needed data from the matching rows, a clustered index would be a
better choice.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:eg#KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> If you join by the PK a lot in your queries it would be faster for the
> queries to use a non-clustered index for the joining instead of the
> clustered index as simply more entries could be stored in the pages of the
> NCI. As the others have pointed out this would slow down your
> updates/inserts but if you do join to this table a lot and on that column,
> it might behoove you to keep a seemingly-redundant index.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Kman" <kman@.toocoolwebs.com> wrote in message
> news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> > I ran accross something I had not seen before on a MSSQL 2000 db today.
> All
> > of the tables had a PK and an index for the PK. In addition there was
also
> > created another Clustered Index on the same PK column. As I have never
> seen
> > this before I dropped all existing indexes and put a single Clustered
> Index
> > in place on the PK columns. Currently I am wondering if this double
index
> is
> > something that may have caused some performance issues? All is working
> great
> > with the single clustered index.
> >
> > Thanks, Kim
> >
> >
>|||Kalen, I agree with you, not too many cases where you would really use that
scenario of duplicate indexes, with a clustered index already on the PK, it
would make more sense to have a NCI on a column that you might pull from the
table, which of course would include the clustered key already that you
could join on.
Thanks!
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:evf2SfJ1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> Hi Ray
> This would only be true if the join was a SEMI-join, checking for
existence
> in of matching rows in the inner table. If you were doing a true inner
join,
> and you needed data from the matching rows, a clustered index would be a
> better choice.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:eg#KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> > If you join by the PK a lot in your queries it would be faster for the
> > queries to use a non-clustered index for the joining instead of the
> > clustered index as simply more entries could be stored in the pages of
the
> > NCI. As the others have pointed out this would slow down your
> > updates/inserts but if you do join to this table a lot and on that
column,
> > it might behoove you to keep a seemingly-redundant index.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Kman" <kman@.toocoolwebs.com> wrote in message
> > news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> > > I ran accross something I had not seen before on a MSSQL 2000 db
today.
> > All
> > > of the tables had a PK and an index for the PK. In addition there was
> also
> > > created another Clustered Index on the same PK column. As I have never
> > seen
> > > this before I dropped all existing indexes and put a single Clustered
> > Index
> > > in place on the PK columns. Currently I am wondering if this double
> index
> > is
> > > something that may have caused some performance issues? All is working
> > great
> > > with the single clustered index.
> > >
> > > Thanks, Kim
> > >
> > >
> >
> >
>sql

Multi Column Subreport

Hi,

I am trying to use a multi column report as a subreport. I want to know how can I do it without loosing multi column.

Thanks,

-Rohit

Can you be a bit more specific? Have tried something already and not got the expected behaviour? What exactly are you trying to do?|||

I have these reports which I am running separately. One of them is a multi column report also. I want to put together all these reports in one report, for that I am using these reports as subreports.

The problem I am facing is, when I use multicolumn reports as subreport, I loose the multicolumn part of it. All the data come as one single column. I am using Rectangle to hold the subreports. May be I am not doing it wrong.

Can you please help me, how can I bind all my individual reports into one single report and not loose any formatting (i.e.multicolumn and page size)?

Thanks a lot,

-Rohit

|||

I have these reports which I am running separately. One of them is a multi column report also. I want to put together all these reports in one report, for that I am using these reports as subreports.

Can you please help me, how can I bind all my individual reports into one single report and not loose any formatting (i.e.multicolumn and page size)?

Thanks a lot,

-Rohit

|||I'm still not sure what you mean by multicolumn subreport. Are you using a table or a matrix in the subreport?|||

You know how there are multi column reports (news paper style)... the same way I have one of those multi column report. But I want to use that report in another report. I am useing "subreport" feature for this. But the problem is when I use that multi column (news paper style) report in the main report, I loose the multi column from the report. All the data comes in one single column. So my question is how can I use my multi column report in another report without loosing multiple columns ability.

Thank You,

-Rohit

|||

Hi,

I am having exactly the same issue. Did you ever find a resolution to this problem? Are you using SQL Server 2005 and VS 2005?

Thanks,

Amy Bolden

|||

Hi Amy,

I am also facing the same problem and it seems that we cannot have multiple column sub reports.

Refer to the reply provided by MS in the following post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=105411&SiteID=1

Regards,

Ashish

|||

Dear Ashish,

I finally broke down and called Microsoft and they confirmed this as well. Fortunately they have a money back guarentee on service calls if they are unable to provide a solution so at least it didn't cost the company any money.

Thanks for your help!

Amy

Multi Column Subreport

Hi,

I am trying to use a multi column report as a subreport. I want to know how can I do it without loosing multi column.

Thanks,

-Rohit

Can you be a bit more specific? Have tried something already and not got the expected behaviour? What exactly are you trying to do?|||

I have these reports which I am running separately. One of them is a multi column report also. I want to put together all these reports in one report, for that I am using these reports as subreports.

Can you please help me, how can I bind all my individual reports into one single report and not loose any formatting (i.e.multicolumn and page size)?

Thanks a lot,

-Rohit

|||

I have these reports which I am running separately. One of them is a multi column report also. I want to put together all these reports in one report, for that I am using these reports as subreports.

Can you please help me, how can I bind all my individual reports into one single report and not loose any formatting (i.e.multicolumn and page size)?

Thanks a lot,

-Rohit

|||I'm still not sure what you mean by multicolumn subreport. Are you using a table or a matrix in the subreport?|||

Thank You,

-Rohit

|||

Hi,

I am having exactly the same issue. Did you ever find a resolution to this problem? Are you using SQL Server 2005 and VS 2005?

Thanks,

Amy Bolden

|||

Hi Amy,

I am also facing the same problem and it seems that we cannot have multiple column sub reports.

Refer to the reply provided by MS in the following post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=105411&SiteID=1

Regards,

Ashish

|||

Dear Ashish,

Thanks for your help!

Amy

multi column subquery using IN clause

Is there a way to do a multi column subquery using a IN clause?
Example of what works for Oracle and needs to get it to work for SQL Server
2000:
SELECT *
FROM Test T
WHERE (T.Key1, T.Key2) IN
(SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
NULL)
I would like to keep this a prepared statement if possible.
Thanks in advance, KevinUse EXISTS:
SELECT *
FROM Test T
WHERE EXISTS
(SELECT *
FROM SubSelect S
WHERE S.End_Date IS NULL
AND S.Key1 = T.Key1
AND S.Key2 = T.Key2)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in message
news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
> Is there a way to do a multi column subquery using a IN clause?
> Example of what works for Oracle and needs to get it to work for SQL
> Server
> 2000:
> SELECT *
> FROM Test T
> WHERE (T.Key1, T.Key2) IN
> (SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
> NULL)
> I would like to keep this a prepared statement if possible.
> Thanks in advance, Kevin|||n columns = n conditions
:)
Try using this function (several times in the same query in as many joins as
you need):
http://solidqualitylearning.com/Blo.../10/22/200.aspx
E.g. four columns => four variables => four instances of the function in
four joins
ML|||Why not just do a JOIN?
SELECT T.<col1>, T.<col2>, ...
FROM Test T
INNER JOIN SubSelect S
ON T.Key1 = S.Key1
AND T.Key2 = S.Key2
WHERE S.End_Date IS NULL
Tom's EXISTS works also, but the above truly represents what you're doing...
seems to me the most intuitive approach to the query. Try to avoid SELECT *
in production code, FWIW.
"Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in message
news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
> Is there a way to do a multi column subquery using a IN clause?
> Example of what works for Oracle and needs to get it to work for SQL
> Server
> 2000:
> SELECT *
> FROM Test T
> WHERE (T.Key1, T.Key2) IN
> (SELECT S.Key1, S.Key2 FROM SubSelect S WHERE S.End_Date IS
> NULL)
> I would like to keep this a prepared statement if possible.
> Thanks in advance, Kevin|||On Mon, 31 Oct 2005 12:23:19 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>Why not just do a JOIN?
>SELECT T.<col1>, T.<col2>, ...
> FROM Test T
> INNER JOIN SubSelect S
> ON T.Key1 = S.Key1
> AND T.Key2 = S.Key2
> WHERE S.End_Date IS NULL
>Tom's EXISTS works also, but the above truly represents what you're doing..
.
>seems to me the most intuitive approach to the query.
Hi Aaron,
If the relationship between Test and Subselect is one to many (with
Subselect being the many), the join will introduce duplicates that are
avoided with Tom's EXISTS.

>Try to avoid SELECT *
>in production code, FWIW.
Amen to that!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> If the relationship between Test and Subselect is one to many (with
> Subselect being the many), the join will introduce duplicates that are
> avoided with Tom's EXISTS.
True. When given crappy specs, we have to make some assumptions. If we ask
more information, we're being pesky. Or so I'm told.|||Your heart is in the right place and that is legal SQL-92 syntax. But
SQL Server is behidn the curve.
Use an EXISTS() predicate and watch out for NULLs.|||Tom,
The EXISTS works and is generic to work with both Oracle and SQL Server.
Thanks for everyone's help.
"Tom Moreau" wrote:

> Use EXISTS:
> SELECT *
> FROM Test T
> WHERE EXISTS
> (SELECT *
> FROM SubSelect S
> WHERE S.End_Date IS NULL
> AND S.Key1 = T.Key1
> AND S.Key2 = T.Key2)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Kevin Shephard" <Kevin Shephard@.discussions.microsoft.com> wrote in messa
ge
> news:DF8C2250-9861-4D50-B6AA-7EC5A806F46C@.microsoft.com...
>
>|||On Mon, 31 Oct 2005 21:46:07 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>True. When given crappy specs, we have to make some assumptions. If we as
k
>more information, we're being pesky. Or so I'm told.
>
Aye, it's a thin line we're treading. And we can only lose.
Ask for more information, and you get toasted by the OP for asking
questions instead of serving instant gratification.
Make a wrong assumption, and you get toasted by the OP for giving an
incorrect answer.
Make a correct assumption, and some smartypant other poster informs you
that it might have been wrong.....
My post was, of course, not intended as criticism, but as additional
information and a warning for a common pitfall, both to the OP and to
any others that might be reading this thread.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> My post was, of course, not intended as criticism,
Don't worry, it wasn't taken as such...sql

Multi column subquery help requested

IDkey| ColA| ColB| ColC
100| A12693| 1528602| 132.56
101| A12693| 1528602| 132.56
200| A13823| 363985| 52.12
201| A13823| 363985| 52.12
300| A23234| 435675| 12.14
To pull out the dups, I can use:
select cola, colb, colc, count(*)
from table
group by cola, colb, colc
having count(*)>1
I want to get the IDkey associated with them, but dont know how.
Conceptually I want to do
select idkey
from table
where
(
select cola, colb, colc, count(*)
from table
group by cola, colb, colc
having count(*)>1
)
Any help is greatly appreciated.I havn't tested it yet, but i would try something like:
select idkey, cola, colb, colc
from table
INNER JOIN (SELECT cola, colb, colc
group by cola, colb, colc
having count(*)>1) AS a
ON (a.cola = table.cola AND a.colb = table.colb AND a.colx = table.colc)
Hope this helps you.
--
Elmar Jansen
http://www.learnit.nl
"SandpointGuy" wrote:
> IDkey| ColA| ColB| ColC
> 100| A12693| 1528602| 132.56
> 101| A12693| 1528602| 132.56
> 200| A13823| 363985| 52.12
> 201| A13823| 363985| 52.12
> 300| A23234| 435675| 12.14
> To pull out the dups, I can use:
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> I want to get the IDkey associated with them, but dont know how.
> Conceptually I want to do
> select idkey
> from table
> where
> (
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> )
> Any help is greatly appreciated.|||A typo, of course "a.colx = table.colc" should be "a.colc = table.colc".
So:
select idkey, cola, colb, colc
from table
INNER JOIN (SELECT cola, colb, colc
group by cola, colb, colc
having count(*)>1) AS a
ON (a.cola = table.cola AND a.colb = table.colb AND a.colc = table.colc)|||Almost. Add an exists and correlate:
select idkey
from table T1
where EXISTS
( select cola, colb, colc
from table T2
where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
group by cola, colb, colc
having count(*)>1
)
You can also skip the superfluous group, count and having completely with
the following simple formulation
select idkey from table T1
where exists
(select * from table T2
where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
and T1.idkey <> T2.idkey)
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:C3FC8DBD-ED40-4BC6-BEB5-6BA416B623E7@.microsoft.com...
> IDkey| ColA| ColB| ColC
> 100| A12693| 1528602| 132.56
> 101| A12693| 1528602| 132.56
> 200| A13823| 363985| 52.12
> 201| A13823| 363985| 52.12
> 300| A23234| 435675| 12.14
> To pull out the dups, I can use:
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> I want to get the IDkey associated with them, but dont know how.
> Conceptually I want to do
> select idkey
> from table
> where
> (
> select cola, colb, colc, count(*)
> from table
> group by cola, colb, colc
> having count(*)>1
> )
> Any help is greatly appreciated.|||Thanks much!
"Mark Yudkin" wrote:
> Almost. Add an exists and correlate:
> select idkey
> from table T1
> where EXISTS
> ( select cola, colb, colc
> from table T2
> where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
> group by cola, colb, colc
> having count(*)>1
> )
> You can also skip the superfluous group, count and having completely with
> the following simple formulation
> select idkey from table T1
> where exists
> (select * from table T2
> where T1.cola = T2.cola and T1.colb = T2.colb and T1.colc = T2.colc
> and T1.idkey <> T2.idkey)
>
> "SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
> news:C3FC8DBD-ED40-4BC6-BEB5-6BA416B623E7@.microsoft.com...
> > IDkey| ColA| ColB| ColC
> > 100| A12693| 1528602| 132.56
> > 101| A12693| 1528602| 132.56
> > 200| A13823| 363985| 52.12
> > 201| A13823| 363985| 52.12
> > 300| A23234| 435675| 12.14
> >
> > To pull out the dups, I can use:
> > select cola, colb, colc, count(*)
> > from table
> > group by cola, colb, colc
> > having count(*)>1
> >
> > I want to get the IDkey associated with them, but dont know how.
> > Conceptually I want to do
> >
> > select idkey
> > from table
> > where
> > (
> > select cola, colb, colc, count(*)
> > from table
> > group by cola, colb, colc
> > having count(*)>1
> > )
> >
> > Any help is greatly appreciated.
>
>

multi column report question

Has anyone figured out a way to do a true page break in a multi-column
report. If a group ends somewhere in the first column what I would like to do
is start the next group on the next page not at the top of the next column.
If this is possible, I'd sure like to know how.
ThanksOn Jun 18, 5:55 pm, B. Mark McKinney <b...@.newsproups.nospam> wrote:
> Has anyone figured out a way to do a true page break in a multi-column
> report. If a group ends somewhere in the first column what I would like to do
> is start the next group on the next page not at the top of the next column.
> If this is possible, I'd sure like to know how.
> Thanks
These steps should give you your desired outcome.
1. In the 'Layout' view of BIDS, select the table/matrix control
(where applicable).
2. Right click the table/matrix and select 'Properties.'
3. On the 'Groups' tab, select the 'Add...' button.
4. Below 'Group on:' and 'Expression' enter the following:
'=Ceiling(RowNumber(Nothing)/18)'
5. Select 'Page break at end'
5. Select 'OK' and 'OK' again.
[NOTE: The format in step 4 is: =Ceiling(RowNumber(Nothing)/
NumberOfRowsPerPage) ]
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for the response. Unfortunately that did not solve my problem with
multi-column reports. I have two tables inside of a list and the list
controls the grouping. It correctly breaks at the end of each group but if
the group ends in the first column it starts the next group at the top of
column two instead of the next page. Adding a second list and grouping using
your method inserts more page breaks but does not give you the control that
you need. Infact in some instances you end up stopping one group in the
second column of one page and start the next group at the top of column two
on the next page.
I don't think that there is a solution to this problem currently. Hopefully
there will be one in the future as I have many reports that could benefit
from the multi column layout but the way it is now the output would be
unusable.
That and not being able to deal with large reports are my two biggest beefs.
Yes you can break them up into smaller pieces and you can use pdf print
drivers etc etc but that is a really sloppy and inefficient way to handle
what should be a very basic function. Actually my two beefs are somewhat
related in that if mult- column reports worked I could probably shrink some
of my mid sized reports down to manageable sizes like 200 - 800 pages.
Glitz is great but when the basics don't work the end product looks real
cheezy when customers use it.
Anyway, back to the problem at hand, the way I look at it there is a huge
difference between a column break and a page break.

Multi column report as a subreport

I have a table report where amoung other things there is a list of names
from another data set. I don't want the names to print in one column because
the list maybe more than one page. I can create a multi column report for
just the names and when printed or print preview will display in multiply
columns (good). But when I add that report as a subreport to my main report
I believe it ignores the subreport's 'report properties' and uses the main
'report's properties'?
Any suggestions?
JohnI found this in 2005 Books online, I would guess that 2000 is the same.
Apparently the column setting is inherited from the parent report.
Denny
SQL Server 2005 Books Online
Writing Multi-Column Reports
Updated: 5 December 2005
You can design a report that uses a multi-column layout, similar to a
traditional newspaper column where data flows down multiple adjacent
columns. A multi-column layout applies to the entire report. It is not
possible to specify a multi-column layout on the top half of the report, and
a tabular layout on the bottom half of the report. When you specify a
multi-column layout, the report server creates each column as a series of
very narrow pages that are rendered in close sequence, giving the appearance
of multiple columns. Properties that you set at the page level are applied
to each column in the report. You can define as many columns you want.
For best results, use data regions that provide repeating rows of data (for
example, table or list box). A list box placed within a multi-column report
will display data from the top left of the page to the bottom left of the
page, and then continue the list in the adjacent column at the top of the
page. If you want to use text boxes or images, put them in a list so that
they repeat in each column.
If you are accustomed to using subreports to embed a separate report within
a parent report, be aware that you cannot use subreports to get the same
outcome in a multi-column layout. In a multi-column report, a subreport
inherits the column settings of the parent report. This means that if you
define a multi-column layout on a subreport, the subreport ignores the
column settings that are specified for it. It also means that you cannot use
subreports to create a free-form or single column layout within the
multi-column report. Subreports that you include in a multi-column report
always use the column settings of the parent report
"johnsh" <johnsh@.axiumae.com> wrote in message
news:%23%23usVHbhGHA.4368@.TK2MSFTNGP03.phx.gbl...
>I have a table report where amoung other things there is a list of names
>from another data set. I don't want the names to print in one column
>because the list maybe more than one page. I can create a multi column
>report for just the names and when printed or print preview will display in
>multiply columns (good). But when I add that report as a subreport to my
>main report I believe it ignores the subreport's 'report properties' and
>uses the main 'report's properties'?
> Any suggestions?
> John
>

Multi Column Report

I can't get my table to wrap to column 2. When I run the report it makes the overall width the width of one column. There is plenty of room for this 2nd column. Why isn't it showing?Multi column reports appear as one column in Preview and the HTML renderers.
To render the report so all columns show you must use Print Preview, PDF, or
TIFF.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> I can't get my table to wrap to column 2. When I run the report it makes
the overall width the width of one column. There is plenty of room for this
2nd column. Why isn't it showing?|||I am having a problem with multi-column report displaying the multiple
columns when viewed as a sub-report, whether or not I am in Print Preview
mode or in Preview mode.
When I view the report as a master report, all columns show up as expected
in the Print Preview mode (not in Preview mode).
Does anyone have a work-around or is there a fix for this problem?
"Bruce Johnson [MSFT]" wrote:
> Multi column reports appear as one column in Preview and the HTML renderers.
> To render the report so all columns show you must use Print Preview, PDF, or
> TIFF.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> > I can't get my table to wrap to column 2. When I run the report it makes
> the overall width the width of one column. There is plenty of room for this
> 2nd column. Why isn't it showing?
>
>|||Can you tell us if there are any plans in the future to have the html
renderer display multiple columns?
Can you suggest any workarounds for this?
Thanks,
John
"Bruce Johnson [MSFT]" wrote:
> Multi column reports appear as one column in Preview and the HTML renderers.
> To render the report so all columns show you must use Print Preview, PDF, or
> TIFF.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:99AAF6E1-0B63-4AE6-8B51-F5B1909D3A7A@.microsoft.com...
> > I can't get my table to wrap to column 2. When I run the report it makes
> the overall width the width of one column. There is plenty of room for this
> 2nd column. Why isn't it showing?
>
>sql

Multi column primary keys

I have used full-text indexing on some of my past jobs and just recently convinced my supervisor to try it out. I had forgotten that one can only index single column primary key tables. At my current work environment we have several identical databases on
several servers. Those databases are being consolidated into another database via dts. For this reason multi-column PK's had to be set up to avoid duplication.
To rewrite the application and redesign the database is not an option. Is there a workaround to be able to index tables with multiple column PK's or do I have to give up on trying to use the FTI feature?
Ralph Schwehr
Programmer / Analyst
can you add another int column to the tables you want to FTI which has a
unique index on it?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ralph Schwehr" <RalphSchwehr@.discussions.microsoft.com> wrote in message
news:76928EFC-6552-4AAC-9869-95DD1BAC21BF@.microsoft.com...
> I have used full-text indexing on some of my past jobs and just recently
convinced my supervisor to try it out. I had forgotten that one can only
index single column primary key tables. At my current work environment we
have several identical databases on several servers. Those databases are
being consolidated into another database via dts. For this reason
multi-column PK's had to be set up to avoid duplication.
> To rewrite the application and redesign the database is not an option. Is
there a workaround to be able to index tables with multiple column PK's or
do I have to give up on trying to use the FTI feature?
> --
> Ralph Schwehr
> Programmer / Analyst
|||Thanks Hilary,
that is what I did, although this could be propblematic when consolidating the databases into one. But I think we will just not use this new key in the consolidated database.
Ralph Schwehr
Programmer / Analyst
"Hilary Cotter" wrote:

> can you add another int column to the tables you want to FTI which has a
> unique index on it?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Ralph Schwehr" <RalphSchwehr@.discussions.microsoft.com> wrote in message
> news:76928EFC-6552-4AAC-9869-95DD1BAC21BF@.microsoft.com...
> convinced my supervisor to try it out. I had forgotten that one can only
> index single column primary key tables. At my current work environment we
> have several identical databases on several servers. Those databases are
> being consolidated into another database via dts. For this reason
> multi-column PK's had to be set up to avoid duplication.
> there a workaround to be able to index tables with multiple column PK's or
> do I have to give up on trying to use the FTI feature?
>
>

Multi Column Keys

Is it possible to create multi column keys in SQL Express? If so, how?

Thanks

MisterT

I found the answer.

Just hold down the Shift key while clicking on the columns. Then click on the "Set primary key".

Have a good day !

Thanks

Multi Column grouping

I have a table with 9 code columns in it. I want a listing of every
possible code in any of the 9 columns with a count of each. Is there a way
to do this without creating a new table that has 9x the rows that the
current table has or without 9 queries plus a sumation query?
The two ways I can get the correct number now are more time consuming that I
would like because they both require "running the table" a number of times
and the table is very large (20MM+ rows).
Thanks,
ScottGROUP BY, of course
SELECT
Col1
, Col2
, Col3
, etc.
, count(1)
FROM MyTable
GROUP BY
Col1
, Col2
, Col3
, etc.
This will provide a row (and its count) for each distinct combination of cod
es.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Scott Cadreau" <scadreau@.aros.net> wrote in message news:2uDmg.289$Mz3.260@.fed1read07...[c
olor=darkred]
>I have a table with 9 code columns in it. I want a listing of every
> possible code in any of the 9 columns with a count of each. Is there a wa
y
> to do this without creating a new table that has 9x the rows that the
> current table has or without 9 queries plus a sumation query?
>
> The two ways I can get the correct number now are more time consuming that
I
> would like because they both require "running the table" a number of times
> and the table is very large (20MM+ rows).
>
> Thanks,
>
> Scott
>
>[/color]

Friday, March 23, 2012

Multi Column full-text search question

I would like to select rows containing some text which is more than one word.
The problem is that the text can appear in different colums, meaning, one
word in this column and another in that. moreove, I need to select only the
rows containing all the words.
The fulltext search is on one table only (multi columns)
The text size in words is variable
Can someone help?
Thanks in advance
This is a difficult question. Basically Contains will only see the words in
your search phrase if they occur in the same column, no matter what type of
search you are doing; single column, multiple column, or all columns.
For FreeText the hit will come if the word or one of the generations of the
word occurs in any of the columns.
create database multicolumn
go
use multicolumn
go
sp_fulltext_database 'enable'
go
create table fulltexttable
(pk int not null identity constraint fulltexttablepk primary key,
charcol1 char(30),
charcol2 char(30),
charcol3 char(30))
GO
insert into fulltexttable(charcol1, charcol2) values('james','bond')
insert into fulltexttable(charcol1, charcol2) values('james bond','mike')
GO
create fulltext catalog test as default
GO
create fulltext index on fulltexttable(charcol1, charcol2)
key index fulltexttablepk
GO
select * from containstable(fulltexttable,*,'"james bond"') -- 1 row, 2nd
one
select * from containstable(fulltexttable,charcol1,'"james bond"') -- 1 row,
2nd one
select * from containstable(fulltexttable,(charcol1,charcol2),'" james
bond"') -- 1 row, 2nd one
select * from freetexttable(fulltexttable,*,'james bond') -- 2 rows, 1 and 2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"dedi" <dedi@.discussions.microsoft.com> wrote in message
news:CFE85410-5AFF-4F84-B376-3E864DFDB623@.microsoft.com...
>I would like to select rows containing some text which is more than one
>word.
> The problem is that the text can appear in different colums, meaning, one
> word in this column and another in that. moreove, I need to select only
> the
> rows containing all the words.
> The fulltext search is on one table only (multi columns)
> The text size in words is variable
> Can someone help?
> Thanks in advance
sql

Multi Column FTS (2005) Query

Hi
I am trying to get my query syntax to work given a typical query I expect to
passed into my FTS procedure.
The typical search string could be Italy investment. The FTS must search
for Italy filtering by investment.
With an FTS index on CountryName and Item (item is where the word investment
resides)
Some t-sql like this would do the trick:
SELECT CountryName, ItemName
FROM SearchView
WHERE CONTAINS(*, 'italy AND investment')
Given that a single textbox is displayed to allow users to type their search
criteria, how would one seperate such a query so that country names were
searched against the CountryName FTS column and other (non country names)
searched against the FTS Item column?
Should I have several queries to determine if countries exist? If so, then
apply those country names in the AND of the WHERE clause, then how would I
then parse to remove the country names from the intial query string so that
the remaining words can be searched against the FTS Item column?
To add to the delimea, I'm using the FORMSOF(THESAURUS...) functionality, so
a user can query investment Europe (getting countries in the thesaurus file
matching Europe)
Craig
Unfortunately what you would have to do is the following
select *From SearchView where contains(CountryName, 'Italy') and
contains(ItenName,'investment')
This would offer the best performance, you could also do the following
select * from SearchView where CountryName='Italy' and
contains(ItenName,'investment')
unfortunately you will have to parse the search string to see which
countries are present in it, and then build your query accordingly.
Hilary

> To add to the delimea, I'm using the FORMSOF(THESAURUS...)
functionality, so
> a user can query investment Europe (getting countries in the
thesaurus file
> matching Europe)
> Craig
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

Multi column

Hi,
I dont know how to word this but I'll try. I have a report that shows two
field in the output, i.e city and count. When the report is run it displays
about 44 rows on one page, but since there are only two fields the rest of
the page is left blank on the right hand side which can easily accomodate two
more column of same fields of city and count. For example it prints row 1
thru 44 on one page and 45 thru ... I would like it to print the 45 thru ...
on the right side of the page which is blank. I'm pretty sure this can be
done.
Please help!while in Layout tab in Report Designer, go to Report then Report Properties.
Click the Layout tab and play around with the number in Columns box.
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> Hi,
> I dont know how to word this but I'll try. I have a report that shows two
> field in the output, i.e city and count. When the report is run it
> displays
> about 44 rows on one page, but since there are only two fields the rest of
> the page is left blank on the right hand side which can easily accomodate
> two
> more column of same fields of city and count. For example it prints row 1
> thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> ...
> on the right side of the page which is blank. I'm pretty sure this can be
> done.
> Please help!|||I changed the column from 1 to 2 but it still wont move over to the second
column. Any idea?
Thanks
"ME" wrote:
> while in Layout tab in Report Designer, go to Report then Report Properties.
> Click the Layout tab and play around with the number in Columns box.
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> > Hi,
> >
> > I dont know how to word this but I'll try. I have a report that shows two
> > field in the output, i.e city and count. When the report is run it
> > displays
> > about 44 rows on one page, but since there are only two fields the rest of
> > the page is left blank on the right hand side which can easily accomodate
> > two
> > more column of same fields of city and count. For example it prints row 1
> > thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> > ...
> > on the right side of the page which is blank. I'm pretty sure this can be
> > done.
> >
> > Please help!
>
>

Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktfWhat do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||select count(IdentityColumn) from tablename
I get ~87,000,000 in return
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||also to just be sure:
Select count(*) IdentityColumn
group by IdentityColumn
having count(*)>1
Returns 0
Thank you
ktf
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||It is:
SQL enterprise 2000 clustered
NT.5.0.(2195)
8.00.760. SP3
7GB memory
4 processor
"Jasper Smith" wrote:
> What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
> and see if that works. It sounds like this bug
> FIX: A parallel query may return unexpected results
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||Sounds like you're running into the bug then. Did you try doing a count with
option(maxdop 1) ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> It is:
> SQL enterprise 2000 clustered
> NT.5.0.(2195)
> 8.00.760. SP3
> 7GB memory
> 4 processor
>
> "Jasper Smith" wrote:
>> What service pack are you on? Also try doing a count with OPTION(MAXDOP
>> 1)
>> and see if that works. It sounds like this bug
>> FIX: A parallel query may return unexpected results
>> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> > The problem I have is the count(*) or count(table identity column) show
>> > a
>> > much bigger number than the actual number of rows.
>> >
>> > When I run
>> > select count(*) from tablename I get ~87,000,000 in return.
>> >
>> > But the MAX number of table identity is in 10 M range also rowcnt from
>> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
>> > not
>> > help me.
>> >
>> > Why I get such different result. What should I do to correct this.
>> >
>> > Thank you,
>> > ktf
>>|||Before I do that:
Is it going to reconfigure and change the server setting or it is only
within the session?
Is it going to put a big impact on the server?
Because I do not want to make that change yet.
Do I have to install SP4? Because we are not on 64-bit server.
Thank you,
"Jasper Smith" wrote:
> Sounds like you're running into the bug then. Did you try doing a count with
> option(maxdop 1) ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> > It is:
> > SQL enterprise 2000 clustered
> > NT.5.0.(2195)
> > 8.00.760. SP3
> > 7GB memory
> > 4 processor
> >
> >
> > "Jasper Smith" wrote:
> >
> >> What service pack are you on? Also try doing a count with OPTION(MAXDOP
> >> 1)
> >> and see if that works. It sounds like this bug
> >>
> >> FIX: A parallel query may return unexpected results
> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> > The problem I have is the count(*) or count(table identity column) show
> >> > a
> >> > much bigger number than the actual number of rows.
> >> >
> >> > When I run
> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >
> >> > But the MAX number of table identity is in 10 M range also rowcnt from
> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> >> > not
> >> > help me.
> >> >
> >> > Why I get such different result. What should I do to correct this.
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
> >>
>
>|||It's a query hint, it only affects the specific query in question. It won't
impact anything else. To avoid any issues just run
select count(*)
from tablename with(nolock)
option(maxdop 1)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> Before I do that:
> Is it going to reconfigure and change the server setting or it is only
> within the session?
> Is it going to put a big impact on the server?
> Because I do not want to make that change yet.
> Do I have to install SP4? Because we are not on 64-bit server.
> Thank you,
>
> "Jasper Smith" wrote:
>> Sounds like you're running into the bug then. Did you try doing a count
>> with
>> option(maxdop 1) ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> > It is:
>> > SQL enterprise 2000 clustered
>> > NT.5.0.(2195)
>> > 8.00.760. SP3
>> > 7GB memory
>> > 4 processor
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> What service pack are you on? Also try doing a count with
>> >> OPTION(MAXDOP
>> >> 1)
>> >> and see if that works. It sounds like this bug
>> >>
>> >> FIX: A parallel query may return unexpected results
>> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> > The problem I have is the count(*) or count(table identity column)
>> >> > show
>> >> > a
>> >> > much bigger number than the actual number of rows.
>> >> >
>> >> > When I run
>> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >
>> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> > from
>> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
>> >> > will
>> >> > not
>> >> > help me.
>> >> >
>> >> > Why I get such different result. What should I do to correct this.
>> >> >
>> >> > Thank you,
>> >> > ktf
>> >>
>> >>
>> >>
>>|||Jasper,
It gave me the correct number.
the config_value and run_value are set to 0 for "max degree of parallelism.
do you think I should turn it on.
Is it necessary to install sql sp4. The site does not say much about sp4.
Thank you,
ktf
"Jasper Smith" wrote:
> It's a query hint, it only affects the specific query in question. It won't
> impact anything else. To avoid any issues just run
> select count(*)
> from tablename with(nolock)
> option(maxdop 1)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> > Before I do that:
> > Is it going to reconfigure and change the server setting or it is only
> > within the session?
> > Is it going to put a big impact on the server?
> > Because I do not want to make that change yet.
> > Do I have to install SP4? Because we are not on 64-bit server.
> >
> > Thank you,
> >
> >
> > "Jasper Smith" wrote:
> >
> >> Sounds like you're running into the bug then. Did you try doing a count
> >> with
> >> option(maxdop 1) ?
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> >> > It is:
> >> > SQL enterprise 2000 clustered
> >> > NT.5.0.(2195)
> >> > 8.00.760. SP3
> >> > 7GB memory
> >> > 4 processor
> >> >
> >> >
> >> > "Jasper Smith" wrote:
> >> >
> >> >> What service pack are you on? Also try doing a count with
> >> >> OPTION(MAXDOP
> >> >> 1)
> >> >> and see if that works. It sounds like this bug
> >> >>
> >> >> FIX: A parallel query may return unexpected results
> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Jasper Smith (SQL Server MVP)
> >> >> http://www.sqldbatips.com
> >> >> I support PASS - the definitive, global
> >> >> community for SQL Server professionals -
> >> >> http://www.sqlpass.org
> >> >>
> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> >> > The problem I have is the count(*) or count(table identity column)
> >> >> > show
> >> >> > a
> >> >> > much bigger number than the actual number of rows.
> >> >> >
> >> >> > When I run
> >> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >> >
> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
> >> >> > from
> >> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
> >> >> > will
> >> >> > not
> >> >> > help me.
> >> >> >
> >> >> > Why I get such different result. What should I do to correct this.
> >> >> >
> >> >> > Thank you,
> >> >> > ktf
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||The fix for this bug is in SP4. Changing the server wide maxdop settings
will obviously affect all other queries so should only be done after
extensive testing. If you are not seeing any application related issues due
to this bug and it is only affecting "DBA" type activities then you don't
necessarily need to get on SP4 however it's probably worth doing anyway to
keep upto to date with the latest bug fixes
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:30CBAF3D-FC19-4F06-89A8-ED074AF96A98@.microsoft.com...
> Jasper,
> It gave me the correct number.
> the config_value and run_value are set to 0 for "max degree of
> parallelism.
> do you think I should turn it on.
> Is it necessary to install sql sp4. The site does not say much about sp4.
> Thank you,
> ktf
> "Jasper Smith" wrote:
>> It's a query hint, it only affects the specific query in question. It
>> won't
>> impact anything else. To avoid any issues just run
>> select count(*)
>> from tablename with(nolock)
>> option(maxdop 1)
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
>> > Before I do that:
>> > Is it going to reconfigure and change the server setting or it is only
>> > within the session?
>> > Is it going to put a big impact on the server?
>> > Because I do not want to make that change yet.
>> > Do I have to install SP4? Because we are not on 64-bit server.
>> >
>> > Thank you,
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> Sounds like you're running into the bug then. Did you try doing a
>> >> count
>> >> with
>> >> option(maxdop 1) ?
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> >> > It is:
>> >> > SQL enterprise 2000 clustered
>> >> > NT.5.0.(2195)
>> >> > 8.00.760. SP3
>> >> > 7GB memory
>> >> > 4 processor
>> >> >
>> >> >
>> >> > "Jasper Smith" wrote:
>> >> >
>> >> >> What service pack are you on? Also try doing a count with
>> >> >> OPTION(MAXDOP
>> >> >> 1)
>> >> >> and see if that works. It sounds like this bug
>> >> >>
>> >> >> FIX: A parallel query may return unexpected results
>> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >>
>> >> >> Jasper Smith (SQL Server MVP)
>> >> >> http://www.sqldbatips.com
>> >> >> I support PASS - the definitive, global
>> >> >> community for SQL Server professionals -
>> >> >> http://www.sqlpass.org
>> >> >>
>> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> >> > The problem I have is the count(*) or count(table identity
>> >> >> > column)
>> >> >> > show
>> >> >> > a
>> >> >> > much bigger number than the actual number of rows.
>> >> >> >
>> >> >> > When I run
>> >> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >> >
>> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> >> > from
>> >> >> > sysindexes shows the correct number of ~10 M. So DBCC
>> >> >> > UPDATEUSAGE
>> >> >> > will
>> >> >> > not
>> >> >> > help me.
>> >> >> >
>> >> >> > Why I get such different result. What should I do to correct
>> >> >> > this.
>> >> >> >
>> >> >> > Thank you,
>> >> >> > ktf
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>sql

Monday, March 26, 2012

multi lingual where clause

Multi Indexes - One Column

Multi Column Subreport

Multi Column Subreport

multi column subquery using IN clause

Multi column subquery help requested

multi column report question

Multi column report as a subreport

Multi Column Report

Multi column primary keys

Multi Column Keys

Multi Column grouping

Friday, March 23, 2012

Multi Column full-text search question

Multi Column FTS (2005) Query

Multi column

Much bigger result from Count(*) than Max(table identity number)

Much bigger result from Count(*) than Max(table identity number)

Much bigger result from Count(*) than Max(table identity number)

MSSQL Mistake

Blog Archive

About Me