Boolean logic on binary data

Hello,

I have a 21-byte binary field in a SQL Server table with which I want to do boolean AND logic in a report. (Basically, I want to test whether or not individual bits are turned on.) How can I do this? Is it possible to deal with the binary data without first converting it to a string?

Thanks,
MarkYou can analyze your bitmap values using RDL expressions which are regular VB.NET.|||How exactly do I refer to the data, however? What I mean is, I need to access the bits of this big blob of binary data, but SSRS is not binary-friendly--it wants a string or an int.

All I want to do is access 1 particular bit of this binary blob. If I have to convert this to a string somewhere I will, but I'd prefer not to if at all possible.

(FYI, the report needs to analyze the logonHours attribute for an Active Directory user. This is stored as a byte array, with each bit representing 1 hour in a given week--it's 1 if the user can logon, or 0 if he cannot. I'm converting this to be a 21-byte binary field in SQL.)sql

boolean data type

What data type should I use as a replacement of Boolean in SQL server? I need to create a table column with a "two-state" data type and must ensure, that only one record can hold value "True" in this column... Any ideas?

ThanksYou could use the bit datatype for this purpose. If the bit is always defined I think it even may save some space. However, if there are (possibly in the future) some procedures or external programs involved I'd stick to using int instead.

Boolean data type

Is there a boolean data type in SQL? I'm trying to bind a number of check
boxes and radio buttons in a .net app to some columns in a SQL table and I
don't see any boolean data type. What should I do if I want to assign a data
type "boolean" to a table's column in SQL?
--
TSThere is not... Many developers use CHAR(1), with a check constraint
restricting the values to 'Y' or 'N'. Some people like to use BIT instead,
but I personally prefer the former approach...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"TS" <TS@.discussions.microsoft.com> wrote in message
news:205D2093-75E3-49A5-B2EE-267113AF7ADB@.microsoft.com...
> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a
> data
> type "boolean" to a table's column in SQL?
> --
> TS|||> Is there a boolean data type in SQL?
No, you probably want BIT, though it can take 0, 1, or false. Many people
use CHAR(1) and set a constraint to be either T/F or Y/N.
A|||Well, there is the BIT type. You could also just use a numeric field (of
some sort) and treat it as 0=FALSE, !0=TRUE.
I _think_ bit is preferable, because being a bit, it is either 0 or 1
(or -1, im not sure) and is therefore a logical boolean, but a plain numeric
is more portable if that is a concern.
HTH.
"TS" <TS@.discussions.microsoft.com> wrote in message
news:205D2093-75E3-49A5-B2EE-267113AF7ADB@.microsoft.com...
> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a
> data
> type "boolean" to a table's column in SQL?
> --
> TS|||> No, you probably want BIT, though it can take 0, 1, or false.
Of course, I meant 0, 1, or NULL.|||Uh, oh. Now you're starting to think like SSMS :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u$vi6Yt7FHA.2608@.TK2MSFTNGP10.phx.gbl...
> Of course, I meant 0, 1, or NULL.
>|||> Uh, oh. Now you're starting to think like SSMS :)
Nah, I would have put NULL in italics and spelled out 0 as "false" and 1 as
"true"... :-)|||There isn't a boolean type in SQL but you can use bit type.
If you want to assign a data type "boolean" to table column ( I assume that
column's name is Male), you can do
if chkMale.Checked =true then
insert into ... (PersonID, Name, Male) values ('P001','Richard',1)
else
insert into ... (PersonID, Name, Male) values ('P001','Richard',0)
end if
That's it.
"TS" wrote:

> Is there a boolean data type in SQL? I'm trying to bind a number of check
> boxes and radio buttons in a .net app to some columns in a SQL table and I
> don't see any boolean data type. What should I do if I want to assign a da
ta
> type "boolean" to a table's column in SQL?
> --
> TS

Boolean Calculated Fields in SQL Server

Hi
This may seem amazing and a stupid question but:

Consider there is a parent table A and child table B and we want to write a query that has some fields from A and a calculated field which indicates whether A has any child record in B or not. The Value 1 means Yes and 0 means No. Has anybody an idea to write this in SQL Server?select foo
, bar
, fap
, case when exists
( select 1 from tableB
where Afk = tableA.pk )
then 1 else 0 end as calcfield
from tableA|||Originally posted by r937
select foo
, bar
, fap
, case when exists
( select 1 from tableB
where Afk = tableA.pk )
then 1 else 0 end as calcfield
from tableA

Boolean ? in SQL (2005) table

I was wondering what should be used for a yes/no/ entry identifier . Should it be a simple int , tinyint , or uniqueidentifier?

Thank you

The bit data type is for values that can be either 0 or 1, such as No/Yes or False/True.|||The way you have written your answer suggests that 0 =yes and 1 = no which goes against my common sence. Could you clarify. I am sorry if this seams a bit picky.|||

No problem:

0 --> No, False

1 --> Yes, True

Boolean ? in SQL (2005) table

I was wondering what should be used for a yes/no/ entry identifier . Should it be a simple int , tinyint , or uniqueidentifier?

Thank you

The bit data type is for values that can be either 0 or 1, such as No/Yes or False/True.|||The way you have written your answer suggests that 0 =yes and 1 = no which goes against my common sence. Could you clarify. I am sorry if this seams a bit picky.|||

No problem:

0 --> No, False

1 --> Yes, True

sql

Bookmark lookup takes time in SQL server 2000

Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
--
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
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> > Hi All
> > I have an application with SQL Server 2000 SP4 as my RDBMS.
> > There is a table with around 1200000 records with all the necessary
> > indexes defined when i execute the query through query analyzer the no
> > of rows returned are around 300000, execution plan shows a Bookmark
> > Lookup. But the bookmark lookup taks lot of time?
> > Also if i remove the particular index it shows me a Table Scan in
> > Execution Plan.
> > Can anyone help me explain why Bookmark lookup takes the same time as
> > Table Scan?
> > Thanks & Regards
> > Vishal.
> >
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/article.asp?p=27015&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Bookmark lookup takes time in SQL server 2000

Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.
I am not sure if I can but perhaps a covering index would be helpful here.
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
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159864815.076365.227450@.k70g2000cwa.googlegr oups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Can u tell me more about covering Indexes?
As in :
What do they mean?
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table and
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/art...&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Bookmark lookup takes time in SQL server 2000

Hi All
I have an application with SQL Server 2000 SP4 as my RDBMS.
There is a table with around 1200000 records with all the necessary
indexes defined when i execute the query through query analyzer the no
of rows returned are around 300000, execution plan shows a Bookmark
Lookup. But the bookmark lookup taks lot of time?
Also if i remove the particular index it shows me a Table Scan in
Execution Plan.
Can anyone help me explain why Bookmark lookup takes the same time as
Table Scan?
Thanks & Regards
Vishal.I am not sure if I can but perhaps a covering index would be helpful here.
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
"Vishal" <vishal.bhute@.gmail.com> wrote in message
news:1159864815.076365.227450@.k70g2000cwa.googlegroups.com...
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>|||Vishal wrote:
> Hi All
> I have an application with SQL Server 2000 SP4 as my RDBMS.
> There is a table with around 1200000 records with all the necessary
> indexes defined when i execute the query through query analyzer the no
> of rows returned are around 300000, execution plan shows a Bookmark
> Lookup. But the bookmark lookup taks lot of time?
> Also if i remove the particular index it shows me a Table Scan in
> Execution Plan.
> Can anyone help me explain why Bookmark lookup takes the same time as
> Table Scan?
> Thanks & Regards
> Vishal.
>
A bookmark lookup occurs when the index that was used to satisfy the
query doesn't contain all of the columns that were requested from a
specific table. Below is a snippet from an earlier response that I
posted to a similar question:
You can look at the execution plan to see what indexes are being used by
your query. I'll try to give an overly simplified explanation:
Suppose I have a table with 7 columns, Col1 thru Col7.
I have an index named Index1 using Col1, Col2, Col3 as a composite key.
Running the following queries (data volumes/optimizer whims may alter
these behaviors):
-- This will do an index seek using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col1 = 'x'
-- This will do an index SCAN using Index1, with a bookmark lookup to
get Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
Does that make sense? Now let's add another index, Index2, to the
table, using Col2, Col4 as a key:
-- This will now do an index seek using Index2
SELECT Col1, Col2
FROM MyTable
WHERE Col2 = 'x'
-- This will do an index seek using Index2, with a bookmark lookup to
get the value of Col1, not Col4
SELECT Col1, Col2, Col4
FROM MyTable
WHERE Col2 = 'x'
These are ridiculously simple examples, and when run against real data
volumes, the optimizer may choose a different course of action,
depending on statistics, distribution of values, etc...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Vishal,
to add another point to the mix, you might just want to check if it's
possible to use a covering index.
I mention this as you are referring to queries against one single table and
there is often a huge time saving if you can cover the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Can u tell me more about covering Indexes'
As in :
What do they mean'
How to define them?
Rgds
Vishal.
Paul Ibison wrote:
> Vishal,
> to add another point to the mix, you might just want to check if it's
> possible to use a covering index.
> I mention this as you are referring to queries against one single table an
d
> there is often a huge time saving if you can cover the query.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Tracy Thanks for ur info on Bookmark Lookup.
But does anyone have any Idea Why Bookmark Lookup is taking up same
time as a Table Scan does?
The table on which the query executes has about 80 columns defined.
Could this be the reason when I Execute a Select * on such a Table it
takes same time as a Table Scan?
Rgds
Vishal.
Tracy McKibben wrote:
> Vishal wrote:
> A bookmark lookup occurs when the index that was used to satisfy the
> query doesn't contain all of the columns that were requested from a
> specific table. Below is a snippet from an earlier response that I
> posted to a similar question:
> You can look at the execution plan to see what indexes are being used by
> your query. I'll try to give an overly simplified explanation:
> Suppose I have a table with 7 columns, Col1 thru Col7.
> I have an index named Index1 using Col1, Col2, Col3 as a composite key.
> Running the following queries (data volumes/optimizer whims may alter
> these behaviors):
> -- This will do an index seek using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col1 = 'x'
> -- This will do an index SCAN using Index1, with a bookmark lookup to
> get Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> Does that make sense? Now let's add another index, Index2, to the
> table, using Col2, Col4 as a key:
> -- This will now do an index seek using Index2
> SELECT Col1, Col2
> FROM MyTable
> WHERE Col2 = 'x'
> -- This will do an index seek using Index2, with a bookmark lookup to
> get the value of Col1, not Col4
> SELECT Col1, Col2, Col4
> FROM MyTable
> WHERE Col2 = 'x'
> These are ridiculously simple examples, and when run against real data
> volumes, the optimizer may choose a different course of action,
> depending on statistics, distribution of values, etc...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Vishal,
here's an article that sums it up nicely:
http://www.informit.com/articles/ar...5&seqNum=6&rl=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Vishal,
avoiding the * (all columns) and just getting the ones you need might make
it possible to use a covering index (as in other part of this thread).
But to answer your question with a question, why would you assume that
bookmark lookups are always faster than a tablescan? IE there's a logical
read ("set statistics io on" to see) cutoff point after which carrying out
the bookmark lookup will prove more expensive than a tablescan, eg in a
tablescan you'll read each page once while using a bookmark lookup you might
read each page 100 times.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Bookmark Lookup

Fellow Developers

i have a query that has joins of tables with huge data (more than 2G of records per table). the execution plan shows me the 80% of the execution is on a "Bookmark Lookup" on the biggest table. Does anyone have clue how can I optimize this query? other than using covering indexes...

best regards

Jeries Shahin wrote:

Other than using covering indexes...

Well, if you know the answer already....

Seriously, you need to understand how indexes in SQL Server work (you may already, so here is the short version.) The nonclustered index uses the key of the clustered index rather than keeping a pointer to the physical page. Of course, this is great almost all of the time, but can be a costly operation at times.

Are you using any hints? And what join operators are being used? That is a lot of data (assuming 2G = 2 GB and not 2 grand :) and I would have assumed it would do a hash join, unless this is doing a merge join. Posting the output of showplan_text would be a good place to start:

set showplan_text on
go

select ...
go

set showplan_text off
go

And what version of SQL Server are you on? The new INCLUDE clause on the CREATE INDEX statement could actually be the ticket. It gives you a covering index without the overhead of including data you aren't using for searching on in the B-Tree (only the leaf nodes are affected)

|||

Why don't you want to use a covering index. Thats like saying I want my car to go but I don't want to use gas. If you want performance from a relational DB system you need to use the right indexes.

I agree that the "include" option in SQL 2005 maybe an option.

|||

Using a covering index can be really costly if the index keys are really large, so it might not be a good idea in 2000 or earlier to cover an index. We don't know his usage pattern and that is a lot of data (again assuming that G means GB :) This query might only be executed once a day/week/month. There may be thousands of modifications a minute on the table.

It might even be that a reporting database/warehouse is in order.

|||

I agree but to some extent if the performance isn't what is required, then something has to be done and there are a number of options covering indexing being on of them, redesign being another.

One always has to balance out their performance needs. This gets much more complex when doing DSS stuff on an OLTP system. ideally they should be mutually exclusive.

booking db design help

Hey Guys,
I am trying to create a web app that uses an SQL db to handle employee
scheduling. I can design a table to hold the appointments with no problems
but I need to be able to search for free time for the employees.
all bookings are based on 30 minutes blocks.
So far I managed to create all tables and this is what I have concerning the
booking tables "after seeing this post on
http://www.webservertalk.com/archiv...-9-1206387.html due to my lack
of DB design I am having a hard time understanding the solution.
eventually I need to be able to achieve the search using similar statement
to this
select min(starttime)
from schedule
where usedflag='free'
and duration >= :desired_duration
but I am having a problem with tables structure.
would someone explain the structure posted please?
thanks in advancesammy
It ishard to suggest soemthing without seeing the actual data and some DDL
Take a look at Itzik Ben-Gan's example showing the intreval connections to
the internet and resturns the connections was made less than 5 minutes
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
select * from tblConnection
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
"sammy" <sammy1971@.hotmail.com> wrote in message
news:ejgXXazbGHA.4672@.TK2MSFTNGP04.phx.gbl...
> Hey Guys,
> I am trying to create a web app that uses an SQL db to handle employee
> scheduling. I can design a table to hold the appointments with no problems
> but I need to be able to search for free time for the employees.
> all bookings are based on 30 minutes blocks.
> So far I managed to create all tables and this is what I have concerning
> the booking tables "after seeing this post on
> http://www.webservertalk.com/archiv...-9-1206387.html due to my lack
> of DB design I am having a hard time understanding the solution.
> eventually I need to be able to achieve the search using similar statement
> to this
> select min(starttime)
> from schedule
> where usedflag='free'
> and duration >= :desired_duration
> but I am having a problem with tables structure.
> would someone explain the structure posted please?
> thanks in advance
>
>|||Thanks for the response Uri,
what I am thinking of best way to to describe the data is like this. I will
create a table to hold sku's refering to the services to be booked the
skutable should be something like this
SKuID --PK
Description -- char
Durration -->30 minutes blocks number field where 1 = 30 and 2=60 and so on
The employee table should be like this
EmpID -->PK
Fname
Lname
location
ScheduleID -->FK
the scheduletable should be like this
SID -->PK
sundayStart --> ie 12/06/2006 9:30 AM
SundayEnd --> 12/06/2006 6:00 PM
MondayStart
MondayEnd
TuesdayStart
TuesdayEnd
WedStart
WedEnd
ThurStart
ThursEnd
FridayStart
FridayEnd
SaturedayStart
SatureDayEnd
what I am trying to achieve can be described best as calenday with all
employees, the workdays, starttime, endtime and the times they are available
to take calls "calls are based on 30 minutes blocks incremening by 30
minutes.
I hope that explains the issue better
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uij3ouzbGHA.536@.TK2MSFTNGP02.phx.gbl...
> sammy
> It ishard to suggest soemthing without seeing the actual data and some DDL
> Take a look at Itzik Ben-Gan's example showing the intreval connections to
> the internet and resturns the connections was made less than 5 minutes
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:00','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:20','20000610 10:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:23','20000610 10:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:27','20000610 10:45')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 11:57','20000610 12:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:01','20000610 12:04')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:04','20000610 12:40')
> select * from tblConnection
> SELECT
> StartTimeCon,
> ISNULL(
> (SELECT MIN(EndTimeCon)
> FROM tblConnection AS S3
> WHERE S3.StartTimeCon >= S1.StartTimeCon
> AND ISNULL(
> DATEDIFF(
> minute,
> S3.EndTimeCon,
> (SELECT MIN(StartTimeCon)
> FROM tblConnection AS S4
> WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
> EndTimeCon) AS EndTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
>
>
> "sammy" <sammy1971@.hotmail.com> wrote in message
> news:ejgXXazbGHA.4672@.TK2MSFTNGP04.phx.gbl...
>|||sammy
Take a look at http://www.aspfaq.com/show.asp?id=2519
"sammy" <sammy1971@.hotmail.com> wrote in message
news:OdtVFi0bGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response Uri,
> what I am thinking of best way to to describe the data is like this. I
> will create a table to hold sku's refering to the services to be booked
> the skutable should be something like this
> SKuID --PK
> Description -- char
> Durration -->30 minutes blocks number field where 1 = 30 and 2=60 and so
> on
> The employee table should be like this
> EmpID -->PK
> Fname
> Lname
> location
> ScheduleID -->FK
> the scheduletable should be like this
> SID -->PK
> sundayStart --> ie 12/06/2006 9:30 AM
> SundayEnd --> 12/06/2006 6:00 PM
> MondayStart
> MondayEnd
> TuesdayStart
> TuesdayEnd
> WedStart
> WedEnd
> ThurStart
> ThursEnd
> FridayStart
> FridayEnd
> SaturedayStart
> SatureDayEnd
> what I am trying to achieve can be described best as calenday with all
> employees, the workdays, starttime, endtime and the times they are
> available to take calls "calls are based on 30 minutes blocks incremening
> by 30 minutes.
> I hope that explains the issue better
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uij3ouzbGHA.536@.TK2MSFTNGP02.phx.gbl...
>

BOM structure using SQL

Hi there,
Im trying to create a multi-level BOM using MS SQL.
Here is my Table structure:
Table 1:
Part-Number
Table 2:
Part-Number
Part-Owner

So if I link Part-number from T1 to Part-owner I get all parts for that level.
If I link back from T2 using Part-number to T1 I can link again to T2 and check if there are any lower level part structures.
This imbedded link has no limits but usually does not go more then 6-7 levels.
Can anyone help with setting up code for this BOM?

ThanksYour table structure looks valid for logically modeling a hierarchal system. The join you described sounds valid as well.

You might want to add a Where clause and use that in a criteria driven stored procedure that will generate a BOM explosion out of a select, given an assembly SetID; (to return a specific sub part listing result set for a setID representing a desired 'assembly'). Im not sure I understand what you are asking for help with, if you'd like, post an email to me with more specifics, and perhaps I can send you some examples that would help.

Maps
INNER JOIN
Sets
ON Maps.SetID = Sets.SetID
INNER JOIN
Sets Sets_1 ON Maps.SubID = Sets_1.SetID
WHERE
Sets.SetID = [desired SetID]

NOTE:
Sets = "Table 1"
TABLE [Sets] (
[SetID],
[SetName])

Maps = "Table 2"
TABLE [Maps] (
[SetID],
[SubID])|||Hi,
I think you went over my head here
My SQL exposure is somewhat limited. I have not used stored procedures and it sounds from your explanation that I should be using them.
The logic I was going to use was to link Table1 to Table2, then Table2 to Table1 using the other part, however, at some point that becomes imposable.
If the stored procedure is the way to go, can you elaborate a little more how to set it up and what it does?

Thanks much|||RE: If the stored procedure is the way to go, can you elaborate a little more how to set it up and what it does?

Q1[How one may use a stored proc that accepts a parameter?]
A1 OK, I'll try to give you a short example here. If you'd like, post an email to me, and I can send you more complete demo examples.

Example using proc hr_Set_SubSets:
Assume you wish to see a BOM Explosion for a part / assembly number; say for part / assembly number 387. A stored proc that accepts an Int parameter and uses it as criteria on a properly constructed Select statement (built on the Maps and Sets tables) can do just that (assuming the Maps and Sets tables are correctly populated with data). Executing the example proc hr_Set_SubSets proc with 387 assigned to its parameter, would then return all 'components' for the part / assembly with a SetID = 387.

-- Example use of a stored proc hr_Set_SubSets:
Declare
@.vi int
select @.vi = 387
Exec dbo.hr_Set_SubSets @.pSetID = @.vi

-- Creating the stored proc hr_Set_SubSets in the example:
CREATE Proc dbo.hr_Set_SubSets
(@.pSetID int)
AS
SELECT
dbo.Sets.SetName AS SetName,
dbo.Sets.SetID As SetID,
Sets_1.SetName AS SubSetName,
dbo.Maps.SubID As SubSetID
FROM
dbo.Maps
INNER JOIN
dbo.Sets
ON
dbo.Maps.SetID = dbo.Sets.SetID
INNER JOIN
dbo.Sets Sets_1
ON
dbo.Maps.SubID = Sets_1.SetID
WHERE
dbo.Sets.SetID = @.pSetID

Maps and Sets schemas:
TABLE [Sets] (
[SetID],
[SetName])

TABLE [Maps] (
[MapID],
[SetID],
[SubID])sql

BOL Profiler Eventclasses not correct?!

Good morning all
I'm creating a table drawn from all the various books online for SQL
Profiler event classes and have discovered seeming duplicates in the SQL 2005
BOL topic "SQL Server Event Class Reference" (which appeared AFTER the
December update):
Broker:Forwarded Message Sent - EventClass 190 (was 139), designated as
Always 190
Progress Report: Online Index Operation - EventClass 190
Broker:Forwarded Message Dropped - EventClass 191 (was 140)
TM:Save Tran Starting - EventClass 191
Data File Auto Grow event class - EventClass 92
Log File Auto Grow event class - EventClass 92
Data File Auto Shrink event class - EventClass 95
Log File Auto Shrink event class - EventClass 95
Broker:Queue Disabled - EventClass 143 (REMOVED?)
Anyone else discovered this or is this by 'design', and of course has anyone
from MS got an idea on the 'right' event numbers?
Cheers
CharlCharl wrote:
> Good morning all
> I'm creating a table drawn from all the various books online for SQL
> Profiler event classes and have discovered seeming duplicates in the
> SQL 2005 BOL topic "SQL Server Event Class Reference" (which appeared
> AFTER the December update):
> Broker:Forwarded Message Sent - EventClass 190 (was 139), designated
> as Always 190
> Progress Report: Online Index Operation - EventClass 190
> Broker:Forwarded Message Dropped - EventClass 191 (was 140)
> TM:Save Tran Starting - EventClass 191
> Data File Auto Grow event class - EventClass 92
> Log File Auto Grow event class - EventClass 92
> Data File Auto Shrink event class - EventClass 95
> Log File Auto Shrink event class - EventClass 95
> Broker:Queue Disabled - EventClass 143 (REMOVED?)
> Anyone else discovered this or is this by 'design', and of course has
> anyone from MS got an idea on the 'right' event numbers?
> Cheers
> Charl
Query the system view sys.trace_events
--
David Gugick
Quest Software
www.quest.com|||Hi Charl
I addressed this in your other thread, posted 10 minutes earlier than this
one.
Please look at the new metadata view in SQL Server 2005 called
sys.trace_events.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:AC94110E-12E3-41C3-AE20-52F4F20AD891@.microsoft.com...
> Good morning all
> I'm creating a table drawn from all the various books online for SQL
> Profiler event classes and have discovered seeming duplicates in the SQL
> 2005
> BOL topic "SQL Server Event Class Reference" (which appeared AFTER the
> December update):
> Broker:Forwarded Message Sent - EventClass 190 (was 139), designated as
> Always 190
> Progress Report: Online Index Operation - EventClass 190
> Broker:Forwarded Message Dropped - EventClass 191 (was 140)
> TM:Save Tran Starting - EventClass 191
> Data File Auto Grow event class - EventClass 92
> Log File Auto Grow event class - EventClass 92
> Data File Auto Shrink event class - EventClass 95
> Log File Auto Shrink event class - EventClass 95
> Broker:Queue Disabled - EventClass 143 (REMOVED?)
> Anyone else discovered this or is this by 'design', and of course has
> anyone
> from MS got an idea on the 'right' event numbers?
> Cheers
> Charl
>|||Thanks to Kalen and David for the reply, I have joined the table previously
created from the BOL information to sys.trace_events. Having retained the
original data from BOL before the December update and ignoring the Broker
eventclass changes there is only one missing eventclass - 144 Broker:Mirror
Route.
My point is that the BOL data is somewhat inaccurate in this case especially
given that most DBAs needing to run Profiler will (I hope!) be refering to
BOL...
Thus when you refer to the EventClass information and it lists an eventclass
it is surely fair to expect the data to be valid?
Best regards,
Charl
"Kalen Delaney" wrote:
> Hi Charl
> I addressed this in your other thread, posted 10 minutes earlier than this
> one.
> Please look at the new metadata view in SQL Server 2005 called
> sys.trace_events.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Charl" <Charl@.discussions.microsoft.com> wrote in message
> news:AC94110E-12E3-41C3-AE20-52F4F20AD891@.microsoft.com...
> > Good morning all
> >
> > I'm creating a table drawn from all the various books online for SQL
> > Profiler event classes and have discovered seeming duplicates in the SQL
> > 2005
> > BOL topic "SQL Server Event Class Reference" (which appeared AFTER the
> > December update):
> >
> > Broker:Forwarded Message Sent - EventClass 190 (was 139), designated as
> > Always 190
> >
> > Progress Report: Online Index Operation - EventClass 190
> >
> > Broker:Forwarded Message Dropped - EventClass 191 (was 140)
> >
> > TM:Save Tran Starting - EventClass 191
> >
> > Data File Auto Grow event class - EventClass 92
> >
> > Log File Auto Grow event class - EventClass 92
> >
> > Data File Auto Shrink event class - EventClass 95
> >
> > Log File Auto Shrink event class - EventClass 95
> >
> > Broker:Queue Disabled - EventClass 143 (REMOVED?)
> >
> > Anyone else discovered this or is this by 'design', and of course has
> > anyone
> > from MS got an idea on the 'right' event numbers?
> >
> > Cheers
> >
> > Charl
> >
> >
>
>|||Charl wrote:
> Thanks to Kalen and David for the reply, I have joined the table
> previously created from the BOL information to sys.trace_events.
> Having retained the original data from BOL before the December update
> and ignoring the Broker eventclass changes there is only one missing
> eventclass - 144 Broker:Mirror Route.
> <SNIP>
You can email feedback directly to Microsoft about the page in question
using the Send Feedback option at the top and then pressing the Send
Feedback button at the bottom.
David Gugick
Quest Software

BOL May 2007 shows no index and no table of contents

I downloaded and installed SqlServer2K5_BOL_May2007.msi, and it had no index and no table of contents. The shortcut is
"C:\Program Files\Common Files\Microsoft Shared\Help 8\dexplore.exe" /helpcol ms-help://MS.SQLCC.v9 /usehelpsettings SQLServerBooksOnline.9.0 /LaunchFKeywordTopic sql9.portal.f1

there are table of contents files:

Directory of C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033

01/26/2007 11:59 AM 12,949 sql90.hxt
07/27/2006 07:53 AM 271 SQLCC.hxt
11/07/2006 02:16 PM 687 ssm3.hxt

and there are index files too -- the most important one seeming to be :

05/23/2007 12:44 PM 13,630,606 sql90.hxq

I re-executed the .msi and chose repair.
That didn't work to show the index or TC either.

I will try a reboot -- but then I'll have to resintall the previous BOL.

I downloaded and installed it, I was asked about a previous version and choose yes to override it. I am also running Katamai BOL both runs fine. I have no problems, so I think you should delete the download and get a new file for the next install. Hope this helps.|||TOC and index have showed up after a reboot. (Windows XP SP2)

Body is overlapping header

Hello~
I have a report with a header and body.
The header has the usual stuff, title, logo, parameter fields, etc
The body is a table.
When I run the report, the table overlaps the header... I don't
understand why this is happening...
So... what I'm doing right now is I have to leave an inch of blank
space just before the table in the body. But cosmetically the report
doesnt look right...
Thanks!On Jul 12, 4:55 pm, OogleGoogle <Yvonh...@.gmail.com> wrote:
> Hello~
> I have a report with a header and body.
> The header has the usual stuff, title, logo, parameter fields, etc
> The body is a table.
> When I run the report, the table overlaps the header... I don't
> understand why this is happening...
> So... what I'm doing right now is I have to leave an inch of blank
> space just before the table in the body. But cosmetically the report
> doesnt look right...
> Thanks!
This seems like a unique situation. I would suggest putting the
contents of the header into one rectangle and doing the same for the
table control in the body. Also, you might want to right-click inside
the blank area of the header -> select Properties -> and increase the
BorderWidth. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jul 12, 9:40 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Jul 12, 4:55 pm, OogleGoogle <Yvonh...@.gmail.com> wrote:
> > Hello~
> > I have a report with a header and body.
> > The header has the usual stuff, title, logo, parameter fields, etc
> > The body is a table.
> > When I run the report, the table overlaps the header... I don't
> > understand why this is happening...
> > So... what I'm doing right now is I have to leave an inch of blank
> > space just before the table in the body. But cosmetically the report
> > doesnt look right...
> > Thanks!
> This seems like a unique situation. I would suggest putting the
> contents of the header into one rectangle and doing the same for the
> table control in the body. Also, you might want to right-click inside
> the blank area of the header -> select Properties -> and increase the
> BorderWidth. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Under the properties tab for the report itself, try changing the
margins it may or may not help.

blocks hangup database when inserting in 1 specific table

Hi,

We use a database with about 40 related tables. Some tables contain as
much as 30.000 records. We use Access97 as an interface to the
database. Now recently we have the problem that when we want to insert
a row in one specific table (alwasy the same) the database makes
blocks.

Details:
- about 10% of the data was inserted using copying from Excel, before
this action there was no problem, though there is no evidence that
this causes the problem.
- inserting rows via the Query Analyzer works fine, via Access causes
trouble.
- the tempdb lofile has grown to 48Mb.

Has anyone ideas about what is going on and what I can do to solve the
problem?

TAV,
Jan WillemsJan Willems (jwillems@.xs4all.nl) writes:
> We use a database with about 40 related tables. Some tables contain as
> much as 30.000 records. We use Access97 as an interface to the
> database. Now recently we have the problem that when we want to insert
> a row in one specific table (alwasy the same) the database makes
> blocks.

"Makes blocks"? You mean that the INSERT operation is blocked, and you
have to cancel the operation to continue?

When the situation occurs, use sp_who from Query Analyzer, and see if
any process has a non-zero value in the Blk column. In such case, the
process listed in Blk, blocks the spid of that row.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Blocking updates of a table

Hi,
I've been trying to design a way for me to issue a transaction that:

Block all inserts on a table when row X has a certain value (call it A)
Add a row to the table with row X containing A Add rows to another table Unblock inserts Commit transaction

Is this possible? Can anyone give me some pointers as to what to do?
Thanks in advance!

For (1) and (2)
While inserting the row that has a certain value (A), you can request a TABLOCKX. This will acquire X lock on the table and there by block other inserts by other concurrent transactions.

For (3): Is it like any other insert to another table? In that case, nothing special needs to be done

Thanks
Sunil Agarwal

|||For 3 that is what I meant.
For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly? Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?
Thanks for your help!
|||For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly

sunil> yes

Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?

sunila> if you only want to block inserts by other transactions under the condition you have mentioned but allow updates/selects, then there is no way.
thanks,|||I assume you only want to block Inserts but not Updates, Deletes and SELECTS.

You could try using an Insert trigger that would rollback the other transactions until a condition has been achieved.

Blocking issue in DTS package running on 2005

I'm testing an existing 2000 DTS package in 2005. A Data Transformation Task selects from a view and loads a table that was just truncated. The view definition contains a subselect selecting data off the table,

View: select ... from a, (select ... from b) b2 ON ...

Table to insert into: b

There is blocking going on during the execution of the package. The select * from view (Source) and the insert bulk into the table (Destination) are blocking each other. This does not happen in 2000.

Is there a command that I can put in the view definition subselect that will allow me to just grab the data that it can without worrying about blocking? Would READCOMMITTED or READUNCOMMITED work without impact on the view? Other recommendations?

Has anyone ever run into this?

I found that when you uncheck the Table Lock box of the Data Transformation Task, that this blocking does not occur. Does anyone know why this is required in 2005 but works fine in 2000 with the box checked?

blocking alerts

Is there a way to create an alert when a query is blocked for however long I
choose? I know I can query the SysProcesses table but that will just tell me
who is blocked right now, not who has been blocked for say 60 seconds.
sql2k
TIA, ChrisR
Hi,
There is no build in functionality to alert for blocks based on some time.
Only way is to create a table and insert the contents of
blocked SPID's based on some intervals (say 60 seconds). After that
implement the logic to alert by querying the new table.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>
|||You can use the Blocked column and if it is other than 0 and the Wait Time
is > 60 seconds you have your man.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>
|||I cant do that... it would be way too easy. ;-)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>
|||P.S.
Forgot to say thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>

blocking alerts

Is there a way to create an alert when a query is blocked for however long I
choose? I know I can query the SysProcesses table but that will just tell me
who is blocked right now, not who has been blocked for say 60 seconds.
sql2k
TIA, ChrisRHi,
There is no build in functionality to alert for blocks based on some time.
Only way is to create a table and insert the contents of
blocked SPID's based on some intervals (say 60 seconds). After that
implement the logic to alert by querying the new table.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>|||You can use the Blocked column and if it is other than 0 and the Wait Time
is > 60 seconds you have your man.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>|||I cant do that... it would be way too easy. ;-)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>> Is there a way to create an alert when a query is blocked for however
>> long I choose? I know I can query the SysProcesses table but that will
>> just tell me who is blocked right now, not who has been blocked for say
>> 60 seconds.
>>
>> sql2k
>>
>> TIA, ChrisR
>|||P.S.
Forgot to say thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>> Is there a way to create an alert when a query is blocked for however
>> long I choose? I know I can query the SysProcesses table but that will
>> just tell me who is blocked right now, not who has been blocked for say
>> 60 seconds.
>>
>> sql2k
>>
>> TIA, ChrisR
>