I need to do an aggregate query against an integer data type, and want to
return three columns based on the value: 1, 2, or 3. If the field name is
"Action", I should be able to sum (action=1), (action=2), and (action=3).
First I tried the following:
SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2), SUM(SU.[Action]
= 3)
The result of each would be -1 or zero. That doesn't work, I guess because
no boolean data type exists. Using CAST or CONVERT doesn't work either for
the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I would
try IF/ELSE, but cannot get the syntax right. I've used it before but can't
remember how I did it. May have been with DB2. Should go something like:
SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] = 3
BEGIN 1 END ELSE BEGIN 0 END)
If someone would enlighten me I'd appreciate it. ThanksSELECT
SUM CASE WHEN foobar_action = 1 then 1 ELSE 0 END) AS total_1,
SUM CASE WHEN foobar_action = 2 then 1 ELSE 0 END) AS total_2,
SUM CASE WHEN foobar_action = 3 then 1 ELSE 0 END) AS total_3
FROM Foobar;|||David McDivitt wrote:
> I need to do an aggregate query against an integer data type, and
> want to return three columns based on the value: 1, 2, or 3. If the
> field name is "Action", I should be able to sum (action=1),
> (action=2), and (action=3). First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action] = 3)
> The result of each would be -1 or zero. That doesn't work, I guess
> because no boolean data type exists. Using CAST or CONVERT doesn't
> work either for the same reason: (SU.[Action] = 1) cannot be
> interpreted. So thought I would try IF/ELSE, but cannot get the
> syntax right. I've used it before but can't remember how I did it.
> May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF
> SU.[Action] = 3 BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
Try using a CASE statement:
create table #abc (action int)
insert into #abc values (1)
insert into #abc values (2)
insert into #abc values (2)
insert into #abc values (3)
insert into #abc values (3)
insert into #abc values (3)
Select
SUM( CASE action
WHEN 1 THEN 1
ELSE 0
END ) as "Action 1",
SUM( CASE action
WHEN 2 THEN 1
ELSE 0
END ) as "Action 2",
SUM( CASE action
WHEN 3 THEN 1
ELSE 0
END ) as "Action 3"
From #abc
Action 1 Action 2 Action 3
-- -- --
1 2 3
drop table #abc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Look up CASE WHEN in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David McDivitt" <x12code-del@.del-yahoo.com> wrote in message
news:ve9pg1dr6kvo45sc4s2mmmt003cisrhg6t@.
4ax.com...
>I need to do an aggregate query against an integer data type, and want to
> return three columns based on the value: 1, 2, or 3. If the field name is
> "Action", I should be able to sum (action=1), (action=2), and (action=3).
> First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action]
> = 3)
> The result of each would be -1 or zero. That doesn't work, I guess because
> no boolean data type exists. Using CAST or CONVERT doesn't work either for
> the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I
> would
> try IF/ELSE, but cannot get the syntax right. I've used it before but
> can't
> remember how I did it. May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] =
> 3
> BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
>|||>From: "David Gugick" <david.gugick-nospam@.quest.com>
>Date: Wed, 24 Aug 2005 13:17:27 -0400
>Lines: 62
>David McDivitt wrote:
>Try using a CASE statement:
>create table #abc (action int)
>insert into #abc values (1)
>insert into #abc values (2)
>insert into #abc values (2)
>insert into #abc values (3)
>insert into #abc values (3)
>insert into #abc values (3)
>Select
> SUM( CASE action
> WHEN 1 THEN 1
> ELSE 0
Thanks guys the CASE statement was what I was looking for.
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
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
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 bit field Which is True False
I have a field that I need to query for true and false.
The field is a bit.
Is True 0 or 1.
I can't open Books Online and the download instructions based on my version
SQL 2000, are not clear and I don't know what detailed version I have nor
where to find it.
--
MichaelMI guess it depends on how your app inserts the data...
create table foo (hidden bit)
insert into foo values ('Y')
select * from foo
error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Y' to a column of data type bit.
Keith Kratochvil
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
>I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
> version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Michael Miller wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM
A bit column is neither True nor False because bit is a numeric
datatype not a boolean. Usually 1 is understood to mean True and 0 is
understood to mean False. Of course it's always possible that the
person who designed your database may not have respected that
convention at all. One reason I dislike using the bit type is precisely
because of this ambiguity. A meaningful code makes it much clearer just
what the designer's intention is. On the other hand proper
documentation ought to answer your question in any case :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||They are what you want them to be; SQL server only stores the 0 or 1. In
Access, true is numerically represented by -1, and false is 0.
You could say that 1 is true and 0 is false, and extract data from the
column using "true" and "false" with a CASE expression:
SELECT
CASE
WHEN Column1 = 1 THEN 'True'
ELSE 'False'
END
FROM [Your Table]
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||I think .Net automatically converts bit columns to 0=False and 1=True.
Outside of .Net it would be application specific. If you are going to use a
bit column, then I would make it a point to code around values of 0 and 1,
rather than True or False. You also need to take into account NULL values,
if this column allows them. BOL does not have much to say about this
datatype. Bottom line, bit is an integer value, and not a boolean value.
It is up to your code to determine how to use it. Here are some quotes from
BOL.
Converting bit Data
Converting to bit promotes any nonzero value to 1.
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on.
Special Data
Special data consists of data that does not fit any of the categories of
data such as binary data, character data, Unicode data, date and time data,
numeric data and monetary data.
Microsoft SQL ServerT 2000 includes four types of special data:
bit
Consists of either a 1 or a 0. Use the bit data type when representing TRUE
or FALSE, or YES or NO. For example, a client questionnaire that asks if
this is the client's first visit can be stored in a bit column.
Constants
A constant, also known as a literal or a scalar value, is a symbol that
represents a specific data value. The format of a constant depends on the
data type of the value it represents.
bit constants
bit constants are represented by the numbers zero or one, and are not
enclosed in quotation marks. If a number larger than one is used, it is
converted to one.
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||bit is an integer data type, and can be used to store 0, 1, or NULL.
You will need to define whether a value of 1 stands for true (or otherwise)
throughout your application.
- Data Types (SS2000 books online)
http://msdn.microsoft.com/library/d...br />
7msw.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> bl
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com g...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Thank you ALL.
I'll investigate and see what the intention of the creator was.
I'll start with 1=true/yes and 0=false/no
--
MichaelM
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||>> I have a field [sic] that I need to query for true and false.<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Totally different concepts!
You are missing another basic point here. There is no BOOLEAN data
type in SQL and it is a sign of bad programming to write with lower
level bit flags in a high-level language.
Get a book on RDBMS and read about three-valued logic and NULLs to
learn why this cannot be implemented in the SQL model.
Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?|||Understood. I am querying and reporting and needed to know how they are
using a particular field, which is bit. I didn't set up the database and
have differed with the contractor on other design matters as well. For
example, we have two types of tables, one called SAG and the other called
TRG. Instead of making a 3 char field, he set up one fieldname that called
TRG, and then he says whether it is true or not (the bit field). Since he
has 0's and 1's, I was trying to figure out which was which.
I would have named the field GridType, and entered either SAG or TRG, for
readability.
I understand about the education. I have no formal education in SQL Server,
other than some OJT working on a VB6 project with SQL, for about 3 years.
Added to that is 5 years in Access and 15 years in Paradox (DOS 1.0 -->Win
8.0).
Thanks for your input.
--
MichaelM
"--CELKO--" wrote:
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Totally different concepts!
>
> You are missing another basic point here. There is no BOOLEAN data
> type in SQL and it is a sign of bad programming to write with lower
> level bit flags in a high-level language.
> Get a book on RDBMS and read about three-valued logic and NULLs to
> learn why this cannot be implemented in the SQL model.
> Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?
>
The field is a bit.
Is True 0 or 1.
I can't open Books Online and the download instructions based on my version
SQL 2000, are not clear and I don't know what detailed version I have nor
where to find it.
--
MichaelMI guess it depends on how your app inserts the data...
create table foo (hidden bit)
insert into foo values ('Y')
select * from foo
error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Y' to a column of data type bit.
Keith Kratochvil
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
>I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
> version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Michael Miller wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM
A bit column is neither True nor False because bit is a numeric
datatype not a boolean. Usually 1 is understood to mean True and 0 is
understood to mean False. Of course it's always possible that the
person who designed your database may not have respected that
convention at all. One reason I dislike using the bit type is precisely
because of this ambiguity. A meaningful code makes it much clearer just
what the designer's intention is. On the other hand proper
documentation ought to answer your question in any case :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||They are what you want them to be; SQL server only stores the 0 or 1. In
Access, true is numerically represented by -1, and false is 0.
You could say that 1 is true and 0 is false, and extract data from the
column using "true" and "false" with a CASE expression:
SELECT
CASE
WHEN Column1 = 1 THEN 'True'
ELSE 'False'
END
FROM [Your Table]
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||I think .Net automatically converts bit columns to 0=False and 1=True.
Outside of .Net it would be application specific. If you are going to use a
bit column, then I would make it a point to code around values of 0 and 1,
rather than True or False. You also need to take into account NULL values,
if this column allows them. BOL does not have much to say about this
datatype. Bottom line, bit is an integer value, and not a boolean value.
It is up to your code to determine how to use it. Here are some quotes from
BOL.
Converting bit Data
Converting to bit promotes any nonzero value to 1.
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on.
Special Data
Special data consists of data that does not fit any of the categories of
data such as binary data, character data, Unicode data, date and time data,
numeric data and monetary data.
Microsoft SQL ServerT 2000 includes four types of special data:
bit
Consists of either a 1 or a 0. Use the bit data type when representing TRUE
or FALSE, or YES or NO. For example, a client questionnaire that asks if
this is the client's first visit can be stored in a bit column.
Constants
A constant, also known as a literal or a scalar value, is a symbol that
represents a specific data value. The format of a constant depends on the
data type of the value it represents.
bit constants
bit constants are represented by the numbers zero or one, and are not
enclosed in quotation marks. If a number larger than one is used, it is
converted to one.
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||bit is an integer data type, and can be used to store 0, 1, or NULL.
You will need to define whether a value of 1 stands for true (or otherwise)
throughout your application.
- Data Types (SS2000 books online)
http://msdn.microsoft.com/library/d...br />
7msw.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> bl
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com g...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Thank you ALL.
I'll investigate and see what the intention of the creator was.
I'll start with 1=true/yes and 0=false/no
--
MichaelM
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||>> I have a field [sic] that I need to query for true and false.<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Totally different concepts!
You are missing another basic point here. There is no BOOLEAN data
type in SQL and it is a sign of bad programming to write with lower
level bit flags in a high-level language.
Get a book on RDBMS and read about three-valued logic and NULLs to
learn why this cannot be implemented in the SQL model.
Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?|||Understood. I am querying and reporting and needed to know how they are
using a particular field, which is bit. I didn't set up the database and
have differed with the contractor on other design matters as well. For
example, we have two types of tables, one called SAG and the other called
TRG. Instead of making a 3 char field, he set up one fieldname that called
TRG, and then he says whether it is true or not (the bit field). Since he
has 0's and 1's, I was trying to figure out which was which.
I would have named the field GridType, and entered either SAG or TRG, for
readability.
I understand about the education. I have no formal education in SQL Server,
other than some OJT working on a VB6 project with SQL, for about 3 years.
Added to that is 5 years in Access and 15 years in Paradox (DOS 1.0 -->Win
8.0).
Thanks for your input.
--
MichaelM
"--CELKO--" wrote:
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Totally different concepts!
>
> You are missing another basic point here. There is no BOOLEAN data
> type in SQL and it is a sign of bad programming to write with lower
> level bit flags in a high-level language.
> Get a book on RDBMS and read about three-valued logic and NULLs to
> learn why this cannot be implemented in the SQL model.
> Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?
>
Sunday, March 25, 2012
Bookmarks (Management Studio)
How do you save bookmarks so you can use them next time you edit the saved query file?
I asked the same quesiton with 0 responses, I am creating a feedback suggestion now for it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351510&SiteID=1
Derek
vote here:
someone allready suggested this...
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=7e600860-0662-44cf-bc9b-b6565e3d213c
|||Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.|||My bookmarks do not show up when I open the solution. Is there a special way to save the bookmarks with the solution?|||Here's the answer:
Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.
|||My files are part of a solution. When I open the solution the query files are displayed in the solution file. I add bookmark to the query files, save all the files, and then close the solution. When I open the solution again the bookmarks that I created are all gone. It is not working like you stated.|||If you add bookmarks to a script in a database solution created in Visual Studio 2005, when you save the script the bookmarks persist.|||Dan, I created a new project. Then I opened my .sql, added bookmarks, then added the .sql to the project. I closed and saved both the .sql and the project but, when I opened the project again, the bookmarks are gone. I'm a little new so I had our DBA come over and help me. He, too, doesn't understand why the bookmarks aren't there.
Do you have any suggestions? Any obscure tip or setting that I should know about? If not, can I assume it's a bug in SQL Server 2005? I see that someone "opened a ticket" but I wanted to ask before I assumed there was no answer.
Thanks for your help -- or anyone else with a suggestion,
Kate
|||Is this a bug with SQL Server Management Studio?|||FYI:
There is feedback to Microsoft concerning this issue and even though it was suppose to be in this version, they said they would be considering it for the next release. If anyone else want bookmarks to persist in Management Studio, let Microsoft know in their Feedback.
Bookmarks (Management Studio)
How do you save bookmarks so you can use them next time you edit the saved query file?
I asked the same quesiton with 0 responses, I am creating a feedback suggestion now for it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351510&SiteID=1
Derek
vote here:
someone allready suggested this...
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=7e600860-0662-44cf-bc9b-b6565e3d213c
|||Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.|||My bookmarks do not show up when I open the solution. Is there a special way to save the bookmarks with the solution?|||Here's the answer:
Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.
|||My files are part of a solution. When I open the solution the query files are displayed in the solution file. I add bookmark to the query files, save all the files, and then close the solution. When I open the solution again the bookmarks that I created are all gone. It is not working like you stated.|||If you add bookmarks to a script in a database solution created in Visual Studio 2005, when you save the script the bookmarks persist.|||Dan, I created a new project. Then I opened my .sql, added bookmarks, then added the .sql to the project. I closed and saved both the .sql and the project but, when I opened the project again, the bookmarks are gone. I'm a little new so I had our DBA come over and help me. He, too, doesn't understand why the bookmarks aren't there.
Do you have any suggestions? Any obscure tip or setting that I should know about? If not, can I assume it's a bug in SQL Server 2005? I see that someone "opened a ticket" but I wanted to ask before I assumed there was no answer.
Thanks for your help -- or anyone else with a suggestion,
Kate
|||Is this a bug with SQL Server Management Studio?|||FYI:
There is feedback to Microsoft concerning this issue and even though it was suppose to be in this version, they said they would be considering it for the next release. If anyone else want bookmarks to persist in Management Studio, let Microsoft know in their Feedback.
Bookmarks (Management Studio)
How do you save bookmarks so you can use them next time you edit the saved query file?
I asked the same quesiton with 0 responses, I am creating a feedback suggestion now for it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351510&SiteID=1
Derek
vote here:
someone allready suggested this...
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=7e600860-0662-44cf-bc9b-b6565e3d213c
|||Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.|||My bookmarks do not show up when I open the solution. Is there a special way to save the bookmarks with the solution?|||Here's the answer:
Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.
|||My files are part of a solution. When I open the solution the query files are displayed in the solution file. I add bookmark to the query files, save all the files, and then close the solution. When I open the solution again the bookmarks that I created are all gone. It is not working like you stated.|||If you add bookmarks to a script in a database solution created in Visual Studio 2005, when you save the script the bookmarks persist.|||Dan, I created a new project. Then I opened my .sql, added bookmarks, then added the .sql to the project. I closed and saved both the .sql and the project but, when I opened the project again, the bookmarks are gone. I'm a little new so I had our DBA come over and help me. He, too, doesn't understand why the bookmarks aren't there.
Do you have any suggestions? Any obscure tip or setting that I should know about? If not, can I assume it's a bug in SQL Server 2005? I see that someone "opened a ticket" but I wanted to ask before I assumed there was no answer.
Thanks for your help -- or anyone else with a suggestion,
Kate
|||Is this a bug with SQL Server Management Studio?|||FYI:
There is feedback to Microsoft concerning this issue and even though it was suppose to be in this version, they said they would be considering it for the next release. If anyone else want bookmarks to persist in Management Studio, let Microsoft know in their Feedback.
sqlBookmarks (Management Studio)
How do you save bookmarks so you can use them next time you edit the saved query file?
I asked the same quesiton with 0 responses, I am creating a feedback suggestion now for it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=351510&SiteID=1
Derek
vote here:
someone allready suggested this...
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=7e600860-0662-44cf-bc9b-b6565e3d213c
|||Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.|||My bookmarks do not show up when I open the solution. Is there a special way to save the bookmarks with the solution?|||Here's the answer:
Bookmarks in Management Studio are consist with Visual Studio. To use bookmarks your files need to be part of a solution/project, rather than just loose files. Yes, the environment allows the creation of bookmarks on files that are not part of a solution but these bookmarks are not persisted. When you create a solution, add a file, create some bookmarks, the bookmarks will be persisted and show up each time the solution is opened.
|||My files are part of a solution. When I open the solution the query files are displayed in the solution file. I add bookmark to the query files, save all the files, and then close the solution. When I open the solution again the bookmarks that I created are all gone. It is not working like you stated.|||If you add bookmarks to a script in a database solution created in Visual Studio 2005, when you save the script the bookmarks persist.|||Dan, I created a new project. Then I opened my .sql, added bookmarks, then added the .sql to the project. I closed and saved both the .sql and the project but, when I opened the project again, the bookmarks are gone. I'm a little new so I had our DBA come over and help me. He, too, doesn't understand why the bookmarks aren't there.
Do you have any suggestions? Any obscure tip or setting that I should know about? If not, can I assume it's a bug in SQL Server 2005? I see that someone "opened a ticket" but I wanted to ask before I assumed there was no answer.
Thanks for your help -- or anyone else with a suggestion,
Kate
|||Is this a bug with SQL Server Management Studio?|||FYI:
There is feedback to Microsoft concerning this issue and even though it was suppose to be in this version, they said they would be considering it for the next release. If anyone else want bookmarks to persist in Management Studio, let Microsoft know in their Feedback.
Thursday, March 22, 2012
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 database - query
Hi,
I need to write a qry for sql 7/ASP to select a 72hr X 5 matrix of 1/2 hr periods - values need to show booked/unbooked for each of 5 cars for all 72 hours. The problem is the database only stores records for the booked periods.
The query (below) works ok for 1 car & one time slot, but calling this 720 times is not good! - Ideally I need some query that can return all the data in one go, and use the recordset to build a table showing availablity.
How do I write this kind of query ?
Thanks
---
dteDateTime="27-Jul-02 15:30"
iCarID=3
SELECT [pk_bookedTimeID] FROM tblBookedTimes INNER JOIN tblBookings ON [tblBookedTimes].[fk_bookingID]=[tblBookings].[pk_bookingID] WHERE [tblBookings].[fk_carID]=" & iCarID & " AND " & "[tblBookedTimes].[bookedTime] = '" & dteDateTime & "'"can you give a brief example of what the result set is supposed to look like with the holes in it?
have a look at Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) which shows how to use an integer table to generate dates
rudy
http://rudy.ca/|||Hi,
The results would be as follows :
car_id | 24/07/02 00:00 | 24/07/02 00:30 | 24/07/02 01:00 ...
1 0 1 1
2 1 1 1
3 1 1 1
Each row would be a car, each column a date/time.
The date/times would cover 72 hrs ( 144 columns)
from any date/time specified.
The problem I have is that I only store the 1's in the db not the 0's!
The article looks like exactly what Im trying to do - Thanks.|||okay, what you want is a cross-tab report, and since you'll have to do that in asp anyway, there's not much point generating the missing values with sql, just do them with asp too...
rudy|||Thanks rudy,
At least I know what its called now! that was half the problem trying to research it.
I have solved this already in ASP, the performance was so bad- thats why I looked into doing it on the db server with SQL.|||the performance was bad in ASP?
then you must have been doing something wrong, like calling the database in a loop :cool:
call the database like this:SELECT bookedTime
FROM tblBookedTimes
INNER JOIN tblBookings
ON tblBookedTimes.fk_bookingID
= tblBookings.pk_bookingID
WHERE tblBookings.fk_carID=" & iCarID & "
ORDER BY bookedTimenotice that you will get back all the bookedTime values for the car, and they will be in bookedTime sequence
now "cross-tab" the bookedTime values using ASP logic, laying them out into an array with zeroes in the "empty" spots...
there's no way that's going to have bad performance
laying out the results into an array should be easy, because from your original question it looks like all your datetime values were previously entered with times on the half hour (which makes sense for a booking table)
so just retrieve the data in one query (you may want to add a WHERE condition for the right date, and maybe get more info on which car, etc.), and ask an ASP programmer for help with the array
i guess i should mention that i don't do ASP :rolleyes:
rudy
http://rudy.ca/
I need to write a qry for sql 7/ASP to select a 72hr X 5 matrix of 1/2 hr periods - values need to show booked/unbooked for each of 5 cars for all 72 hours. The problem is the database only stores records for the booked periods.
The query (below) works ok for 1 car & one time slot, but calling this 720 times is not good! - Ideally I need some query that can return all the data in one go, and use the recordset to build a table showing availablity.
How do I write this kind of query ?
Thanks
---
dteDateTime="27-Jul-02 15:30"
iCarID=3
SELECT [pk_bookedTimeID] FROM tblBookedTimes INNER JOIN tblBookings ON [tblBookedTimes].[fk_bookingID]=[tblBookings].[pk_bookingID] WHERE [tblBookings].[fk_carID]=" & iCarID & " AND " & "[tblBookedTimes].[bookedTime] = '" & dteDateTime & "'"can you give a brief example of what the result set is supposed to look like with the holes in it?
have a look at Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) which shows how to use an integer table to generate dates
rudy
http://rudy.ca/|||Hi,
The results would be as follows :
car_id | 24/07/02 00:00 | 24/07/02 00:30 | 24/07/02 01:00 ...
1 0 1 1
2 1 1 1
3 1 1 1
Each row would be a car, each column a date/time.
The date/times would cover 72 hrs ( 144 columns)
from any date/time specified.
The problem I have is that I only store the 1's in the db not the 0's!
The article looks like exactly what Im trying to do - Thanks.|||okay, what you want is a cross-tab report, and since you'll have to do that in asp anyway, there's not much point generating the missing values with sql, just do them with asp too...
rudy|||Thanks rudy,
At least I know what its called now! that was half the problem trying to research it.
I have solved this already in ASP, the performance was so bad- thats why I looked into doing it on the db server with SQL.|||the performance was bad in ASP?
then you must have been doing something wrong, like calling the database in a loop :cool:
call the database like this:SELECT bookedTime
FROM tblBookedTimes
INNER JOIN tblBookings
ON tblBookedTimes.fk_bookingID
= tblBookings.pk_bookingID
WHERE tblBookings.fk_carID=" & iCarID & "
ORDER BY bookedTimenotice that you will get back all the bookedTime values for the car, and they will be in bookedTime sequence
now "cross-tab" the bookedTime values using ASP logic, laying them out into an array with zeroes in the "empty" spots...
there's no way that's going to have bad performance
laying out the results into an array should be easy, because from your original question it looks like all your datetime values were previously entered with times on the half hour (which makes sense for a booking table)
so just retrieve the data in one query (you may want to add a WHERE condition for the right date, and maybe get more info on which car, etc.), and ask an ASP programmer for help with the array
i guess i should mention that i don't do ASP :rolleyes:
rudy
http://rudy.ca/
Book Recommendations Needed
I am looking for a book that discusses query tuning, index tuning, execution
plans, etc. Can anyone recommend something?
plans, etc. Can anyone recommend something?
--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com"BenignVanilla" <bv@.tibetanbeefgarden.com> wrote in message
news:-Pqdna74lMVGzVKiRVn-tw@.giganews.com...
> I am looking for a book that discusses query tuning, index tuning,
execution
> plans, etc. Can anyone recommend something?
Inside SQL Server 2000 by Kalen Delany
>
> --
> BV.
> WebPorgmaster - www.IHeartMyPond.com
> Work at Home, Save the Environment - www.amothersdream.com
Book recommendation
Looking for an overview book on SQL Server 2000, can anyone recommend a Titl
e
or Good author?
Id like to learn the basics and how to use query analyser to manager the
database setup. Also the book can show what are the SQL functions (such as
date manipulation) and how to use them.
Thanks.What about Books Online?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"JD" <JD@.discussions.microsoft.com> wrote in message
news:2F1B2593-E3E9-4782-808B-FDF32F04668E@.microsoft.com...
> Looking for an overview book on SQL Server 2000, can anyone recommend a
> Title
> or Good author?
> Id like to learn the basics and how to use query analyser to manager the
> database setup. Also the book can show what are the SQL functions (such as
> date manipulation) and how to use them.
> Thanks.
>|||Also
http://vyaskn.tripod.com/sqlbooks.htm
Madhivanan|||If I had to choose 1 book besides BOL this would be it
The Guru's Guide to Transact-SQL
by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
http://sqlservercode.blogspot.com/
e
or Good author?
Id like to learn the basics and how to use query analyser to manager the
database setup. Also the book can show what are the SQL functions (such as
date manipulation) and how to use them.
Thanks.What about Books Online?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"JD" <JD@.discussions.microsoft.com> wrote in message
news:2F1B2593-E3E9-4782-808B-FDF32F04668E@.microsoft.com...
> Looking for an overview book on SQL Server 2000, can anyone recommend a
> Title
> or Good author?
> Id like to learn the basics and how to use query analyser to manager the
> database setup. Also the book can show what are the SQL functions (such as
> date manipulation) and how to use them.
> Thanks.
>|||Also
http://vyaskn.tripod.com/sqlbooks.htm
Madhivanan|||If I had to choose 1 book besides BOL this would be it
The Guru's Guide to Transact-SQL
by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
http://sqlservercode.blogspot.com/
Monday, March 19, 2012
BOL
Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder SinghWhat's your question? I am not sure.
Have you checked the "CREATE VIEW" page in BOL, which states:
There are a few restrictions on the SELECT clauses in a view definition. A
CREATE VIEW statement cannot:
a.. Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder Singh|||There are issues with doing that.
Yes you can, but doing will force SQL Server to execute the entire view and
perform the order by before using the information. There are other better
ways to accomplish the same thing...
If your view really needs to be sorted, then you should create an index on
the view. As the first index must be clustered, the provides a sort order.
That aside, back to your workaround. By creating a view without
specifying a top and order by, SQL Server has a chance to optimise the
combined select and view query. By doing this it can filter the data before
applying any sort operations. Don't forget that a view (unless indexed)
does not contain any data, it's effectively a pre-prepared SQL statement.
Hope this helps a little.
Regards
Colin Dawson
www.cjdawson.com
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
> Hello All,
> BOL states that :-
> Before you create a view, consider these guidelines and one of the
> guideline is
> "The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword."
> But we can include Order By in A view if we use TOP like this
> create view v1 as
> select top 100 percent * from tablename order by columnName
> Any Thoughts
> With warm regards
> Jatinder Singh
>|||Hi
The guidelines might be for Updatable Views. However you can use Order
By clause in a view.
please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Some people do suggest using this "trick" to order views. I believe
that there are good reasons to avoid doing this.
This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of
defining the TOP x PERCENT so intuitively you would not expect it to
apply to the result of a SELECT from the view. 99% of the time it *may*
work but there is no guarantee that it will always continue to work.
A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:
CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT
NULL)
GO
CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y
GO
SELECT x FROM foo_view
The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimizer therefore has a choice either to
ignore the ORDER BY and retrieve an unsorted result set or to force a
sort which gives a sub-optimal execution plan. So TOP 100 PERCENT
either hurts performance, or doesn't work at all!
As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which
could break your code if it relies on an undefined feature.
In short, don't use undocumented tricks as a substitute for good design
and if you do use this feature be aware of its limitations and risks.
The "correct" and safe solution is simple:
SELECT ... FROM view ORDER BY ...
Why would you need to order the view itself?
Hope this helps.
David Portas
SQL Server MVP
--|||Hi All,
Thanks Naryan for your informative Answer
In Transact-SQL Reference (Location)
Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
In Creating and Maintaining Databases (Location)
The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword.
(The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.)
Try this
select * fom (select * from tablename order by columnname) AA
The point you mentioned appear even in this case Derived Table . My
Question was simple why there is inconsitency while mentioning
information in BOL. Now prehaps I made myself clear. Thanks again for
showing interest .
David
Excellent post !!
As always your answer give something new to learn .
"So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
As my assumption was that Query Optimizer will first expand the query
of view then it will rearrange the whole stuff and the execute it . My
assumption can be totally wrong .
I could not understand "doesn't work at all" part could you please
explain it further.
With Warm regards
Jatinder Singh|||"The optimizer therefore has a choice either to ignore the ORDER BY and
retrieve an unsorted result set [= TOP 100 doesn't work at all] or to force
a
sort which gives a sub-optimal execution plan [= hurts performance]". In
other words, both options are logically possible; neither is very
satisfactory.
David Portas
SQL Server MVP
--
"jsfromynr" wrote:
> Hi All,
> Thanks Naryan for your informative Answer
> In Transact-SQL Reference (Location)
> Include ORDER BY clause, unless there is also a TOP clause in the
> select list of the SELECT statement.
> In Creating and Maintaining Databases (Location)
> The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword.
> (The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.)
> Try this
> select * fom (select * from tablename order by columnname) AA
> The point you mentioned appear even in this case Derived Table . My
> Question was simple why there is inconsitency while mentioning
> information in BOL. Now prehaps I made myself clear. Thanks again for
> showing interest .
> David
> Excellent post !!
> As always your answer give something new to learn .
> "So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
> The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
> As my assumption was that Query Optimizer will first expand the query
> of view then it will rearrange the whole stuff and the execute it . My
> assumption can be totally wrong .
> I could not understand "doesn't work at all" part could you please
> explain it further.
> With Warm regards
> Jatinder Singh
>|||On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
(snip)
>If your view really needs to be sorted, then you should create an index on
>the view.
Hi Colin,
That won't work. Clustered indexes govern how data is stored on the
disk, not in which order it is processed. The optimizer might still pick
an unexpected plan. The order might even change between two consecutive
executions of the same query. See repro below for a proof that an
indexed view doesn't guarantee that the data is returned in order.
The only way to make sure you get the data in the order you need is to
provide an ORDER BY clause in the SELECT statement actually used to
retrieve the data.
create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
fname varchar(20) NOT NULL,
lname varchar(20) NOT NULL)
go
create view SortedBeatles with schemabinding
as
select fname, lname from dbo.Beatles
go
create unique clustered index BeatleIndex on SortedBeatles (fname)
go
insert into Beatles
values ('John', 'Lennon')
insert into Beatles
values ('Paul', 'McCartney')
insert into Beatles
values ('George', 'Harrison')
insert into Beatles
values ('Ringo', 'Starr')
go
select fname, lname from SortedBeatles
go
drop view SortedBeatles
go
drop table Beatles
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9kjff159m2i4qtb39a50hganommi87qgid@.
4ax.com...
> On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
> (snip)
> Hi Colin,
> That won't work. Clustered indexes govern how data is stored on the
> disk, not in which order it is processed. The optimizer might still pick
> an unexpected plan. The order might even change between two consecutive
> executions of the same query. See repro below for a proof that an
> indexed view doesn't guarantee that the data is returned in order.
> The only way to make sure you get the data in the order you need is to
> provide an ORDER BY clause in the SELECT statement actually used to
> retrieve the data.
> create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
> fname varchar(20) NOT NULL,
> lname varchar(20) NOT NULL)
> go
> create view SortedBeatles with schemabinding
> as
> select fname, lname from dbo.Beatles
> go
> create unique clustered index BeatleIndex on SortedBeatles (fname)
> go
> insert into Beatles
> values ('John', 'Lennon')
> insert into Beatles
> values ('Paul', 'McCartney')
> insert into Beatles
> values ('George', 'Harrison')
> insert into Beatles
> values ('Ringo', 'Starr')
> go
> select fname, lname from SortedBeatles
> go
> drop view SortedBeatles
> go
> drop table Beatles
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I had a feeling that it wasn't guarenteed. Your dead right that the only
way to guarentee the sort order is to use the Order by clause. Bad on me
for trying to cut a corner.
Regards
Colin Dawson
www.cjdawson.com
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder SinghWhat's your question? I am not sure.
Have you checked the "CREATE VIEW" page in BOL, which states:
There are a few restrictions on the SELECT clauses in a view definition. A
CREATE VIEW statement cannot:
a.. Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
Hello All,
BOL states that :-
Before you create a view, consider these guidelines and one of the
guideline is
"The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword."
But we can include Order By in A view if we use TOP like this
create view v1 as
select top 100 percent * from tablename order by columnName
Any Thoughts
With warm regards
Jatinder Singh|||There are issues with doing that.
Yes you can, but doing will force SQL Server to execute the entire view and
perform the order by before using the information. There are other better
ways to accomplish the same thing...
If your view really needs to be sorted, then you should create an index on
the view. As the first index must be clustered, the provides a sort order.
That aside, back to your workaround. By creating a view without
specifying a top and order by, SQL Server has a chance to optimise the
combined select and view query. By doing this it can filter the data before
applying any sort operations. Don't forget that a view (unless indexed)
does not contain any data, it's effectively a pre-prepared SQL statement.
Hope this helps a little.
Regards
Colin Dawson
www.cjdawson.com
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123504963.100055.120900@.f14g2000cwb.googlegroups.com...
> Hello All,
> BOL states that :-
> Before you create a view, consider these guidelines and one of the
> guideline is
> "The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword."
> But we can include Order By in A view if we use TOP like this
> create view v1 as
> select top 100 percent * from tablename order by columnName
> Any Thoughts
> With warm regards
> Jatinder Singh
>|||Hi
The guidelines might be for Updatable Views. However you can use Order
By clause in a view.
please let me know if u have any questions
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Some people do suggest using this "trick" to order views. I believe
that there are good reasons to avoid doing this.
This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of
defining the TOP x PERCENT so intuitively you would not expect it to
apply to the result of a SELECT from the view. 99% of the time it *may*
work but there is no guarantee that it will always continue to work.
A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:
CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT
NULL)
GO
CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y
GO
SELECT x FROM foo_view
The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimizer therefore has a choice either to
ignore the ORDER BY and retrieve an unsorted result set or to force a
sort which gives a sub-optimal execution plan. So TOP 100 PERCENT
either hurts performance, or doesn't work at all!
As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which
could break your code if it relies on an undefined feature.
In short, don't use undocumented tricks as a substitute for good design
and if you do use this feature be aware of its limitations and risks.
The "correct" and safe solution is simple:
SELECT ... FROM view ORDER BY ...
Why would you need to order the view itself?
Hope this helps.
David Portas
SQL Server MVP
--|||Hi All,
Thanks Naryan for your informative Answer
In Transact-SQL Reference (Location)
Include ORDER BY clause, unless there is also a TOP clause in the
select list of the SELECT statement.
In Creating and Maintaining Databases (Location)
The query defining the view cannot include the ORDER BY, COMPUTE, or
COMPUTE BY clauses or the INTO keyword.
(The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.)
Try this
select * fom (select * from tablename order by columnname) AA
The point you mentioned appear even in this case Derived Table . My
Question was simple why there is inconsitency while mentioning
information in BOL. Now prehaps I made myself clear. Thanks again for
showing interest .
David
Excellent post !!
As always your answer give something new to learn .
"So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
As my assumption was that Query Optimizer will first expand the query
of view then it will rearrange the whole stuff and the execute it . My
assumption can be totally wrong .
I could not understand "doesn't work at all" part could you please
explain it further.
With Warm regards
Jatinder Singh|||"The optimizer therefore has a choice either to ignore the ORDER BY and
retrieve an unsorted result set [= TOP 100 doesn't work at all] or to force
a
sort which gives a sub-optimal execution plan [= hurts performance]". In
other words, both options are logically possible; neither is very
satisfactory.
David Portas
SQL Server MVP
--
"jsfromynr" wrote:
> Hi All,
> Thanks Naryan for your informative Answer
> In Transact-SQL Reference (Location)
> Include ORDER BY clause, unless there is also a TOP clause in the
> select list of the SELECT statement.
> In Creating and Maintaining Databases (Location)
> The query defining the view cannot include the ORDER BY, COMPUTE, or
> COMPUTE BY clauses or the INTO keyword.
> (The ORDER BY clause is invalid in views, inline functions, derived
> tables, and subqueries, unless TOP is also specified.)
> Try this
> select * fom (select * from tablename order by columnname) AA
> The point you mentioned appear even in this case Derived Table . My
> Question was simple why there is inconsitency while mentioning
> information in BOL. Now prehaps I made myself clear. Thanks again for
> showing interest .
> David
> Excellent post !!
> As always your answer give something new to learn .
> "So TOP 100 PERCENT either hurts performance, or doesn't work at all! "
> The first part "So TOP 100 PERCENT hurts performance" is clear ( a bit)
> As my assumption was that Query Optimizer will first expand the query
> of view then it will rearrange the whole stuff and the execute it . My
> assumption can be totally wrong .
> I could not understand "doesn't work at all" part could you please
> explain it further.
> With Warm regards
> Jatinder Singh
>|||On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
(snip)
>If your view really needs to be sorted, then you should create an index on
>the view.
Hi Colin,
That won't work. Clustered indexes govern how data is stored on the
disk, not in which order it is processed. The optimizer might still pick
an unexpected plan. The order might even change between two consecutive
executions of the same query. See repro below for a proof that an
indexed view doesn't guarantee that the data is returned in order.
The only way to make sure you get the data in the order you need is to
provide an ORDER BY clause in the SELECT statement actually used to
retrieve the data.
create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
fname varchar(20) NOT NULL,
lname varchar(20) NOT NULL)
go
create view SortedBeatles with schemabinding
as
select fname, lname from dbo.Beatles
go
create unique clustered index BeatleIndex on SortedBeatles (fname)
go
insert into Beatles
values ('John', 'Lennon')
insert into Beatles
values ('Paul', 'McCartney')
insert into Beatles
values ('George', 'Harrison')
insert into Beatles
values ('Ringo', 'Starr')
go
select fname, lname from SortedBeatles
go
drop view SortedBeatles
go
drop table Beatles
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9kjff159m2i4qtb39a50hganommi87qgid@.
4ax.com...
> On Mon, 08 Aug 2005 12:53:34 GMT, Colin Dawson wrote:
> (snip)
> Hi Colin,
> That won't work. Clustered indexes govern how data is stored on the
> disk, not in which order it is processed. The optimizer might still pick
> an unexpected plan. The order might even change between two consecutive
> executions of the same query. See repro below for a proof that an
> indexed view doesn't guarantee that the data is returned in order.
> The only way to make sure you get the data in the order you need is to
> provide an ORDER BY clause in the SELECT statement actually used to
> retrieve the data.
> create table Beatles (ID int NOT NULL IDENTITY PRIMARY KEY,
> fname varchar(20) NOT NULL,
> lname varchar(20) NOT NULL)
> go
> create view SortedBeatles with schemabinding
> as
> select fname, lname from dbo.Beatles
> go
> create unique clustered index BeatleIndex on SortedBeatles (fname)
> go
> insert into Beatles
> values ('John', 'Lennon')
> insert into Beatles
> values ('Paul', 'McCartney')
> insert into Beatles
> values ('George', 'Harrison')
> insert into Beatles
> values ('Ringo', 'Starr')
> go
> select fname, lname from SortedBeatles
> go
> drop view SortedBeatles
> go
> drop table Beatles
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I had a feeling that it wasn't guarenteed. Your dead right that the only
way to guarentee the sort order is to use the Order by clause. Bad on me
for trying to cut a corner.
Regards
Colin Dawson
www.cjdawson.com
blonde to write query or design fault
Dear All,
Im wondering if its the design that needs to be changed or I simply cant put this together.
I have 3 tables.
1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)
Codes represent different skills of people, example the sort of job functions theyve held in their employment. Like:
t-CEO,
t-CFO
t-Founder
etc.
people, clearly holds data about people.
CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO
What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.
However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.
I cant seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!"the query takes t-CEO AND t-CFO I get no result."
Is that wrong? No person is t-CEO AND t-CFO in your example.
Please tell us what you want the result to be.|||Thanks for getting back!
Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)
I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CFO, t-Founder)
I also coded person2 as a technology-Chief Financial Officer (t-CFO)
I want to write a query that takes codes as parameters:
t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)
t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)
t-CFO or t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)
Am I describing it correctly?
Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder
I have:
SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName
But thats useless!|||Let me correct that, I have mixed up the similar codes of t-CEO and t-CFO, the correct one is:
Thanks for getting back!
Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)
I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CEO, t-Founder)
I also coded person2 as a technology-Chief Financial Officer (t-CFO)
I want to write a query that takes codes as parameters:
t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)
t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)
t-CFO OR t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)
Am I describing it correctly?
Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder
I have:
SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName
But thats useless!
Im wondering if its the design that needs to be changed or I simply cant put this together.
I have 3 tables.
1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)
Codes represent different skills of people, example the sort of job functions theyve held in their employment. Like:
t-CEO,
t-CFO
t-Founder
etc.
people, clearly holds data about people.
CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO
What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.
However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.
I cant seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!"the query takes t-CEO AND t-CFO I get no result."
Is that wrong? No person is t-CEO AND t-CFO in your example.
Please tell us what you want the result to be.|||Thanks for getting back!
Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)
I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CFO, t-Founder)
I also coded person2 as a technology-Chief Financial Officer (t-CFO)
I want to write a query that takes codes as parameters:
t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)
t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)
t-CFO or t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)
Am I describing it correctly?
Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder
I have:
SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName
But thats useless!|||Let me correct that, I have mixed up the similar codes of t-CEO and t-CFO, the correct one is:
Thanks for getting back!
Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)
I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CEO, t-Founder)
I also coded person2 as a technology-Chief Financial Officer (t-CFO)
I want to write a query that takes codes as parameters:
t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)
t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)
t-CFO OR t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)
Am I describing it correctly?
Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder
I have:
SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName
But thats useless!
Thursday, March 8, 2012
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...
>
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
>
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
>
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...
>|||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...
>
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...
>|||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...
>
Wednesday, March 7, 2012
Blocked transaction problem
Hello,
I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.
SERVER2 is a linked server.
BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN
I have same configuration in other 2 computers and it works ok.
What is the problem?
Thank you!!
This should not lock your transaction. But depending on the server you are using it can be that all data will retrieved from the linked server first to do a filter on the calling server. That will take more time than just doing the Select on the remote server.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Hello,
there are only 3 rows on remote server.
Both servers are SQL Server 2000.
|||What about if you run the thing without any transaction ? Is it the same problem ? Do you see any locks on the table during the stale time ?HTH, Jens K. Suessmeyer
http://www.sqlserver2005.de
Hello,
if I run it without any transaction it finishes very fast.
But I want to know the reason of the problem, because I have one place that runs it this way, and I can not install a new one with same queries.
Thank you.
|||See which locks are allocated when using the command with transactions to see where the blocking is based on.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
You are essentially doing a distributed transaction because of the use of DML with linked server table. So the delay might be attributed to network issues. Check the MSDTC configuration including network configuration to make sure everything is fine. You can also see the transaction statistics in the MSDTC console (Component Services applet) to see the average response time. If that is close to your execution time then the problem is external to SQL Server and you should ask in the Windows newsgroups or search MSKB for optimizing MSDTC setup.
If the problem is not related to MSDTC, then you can determine the wait types in SQL and troubleshoot the issue. Run following:
1. DBCC SQLPERF(WAITSTATS, 'CLEAR') WITH NO_INFOMSGS
2. your current batch
3. DBCC SQLPERF(WAITSTATS) WITH NO_INFOMSGS
Now, based on the wait type you can figure out what is the cause of the delay. Search MSKB articles for more details on using DBCC SQLPERF.
|||Hello,
I can not see anything because the execution of the query never ends.
I leave up to 30 minutes and it does not end.
I can execute the query without a transaction and it finishes in a few miliseconds. So, it is posible to query other server.
Could it be a problem with server names?
I mean, the name registered at SQL Server Enterprise Manager, the one at Linked Server and aliases (SQL Server Client Network Utility).
Currently all of them are the same, but at SQL Server installation time the server name was a diferent one.
Thanks!!
|||Hi!
I have the same problem!!
A simple select againts a second server with a TRAN
BEGIN TRAN
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2
COMMIT TRAN
execution of the query never ends
Without TRAN it finishes in a few miliseconds
someone have any ideas to resolve this problem?
thanks in advance.
Block remote login
How can I block users from using their SQL accounts to access SQL 2000 server
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.
Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.
Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.
Block remote login
How can I block users from using their SQL accounts to access SQL 2000 server
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.
Block remote login
How can I block users from using their SQL accounts to access SQL 2000 serve
r
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.
r
remotely thru enterprise manager or sql query analyzer? Appreciate any help.
Thanks.Hi,
There is no methodology in SQL Server 2000 to restrict the remote access; if
the users have access to network. The only way to restrict
is by setting complex password to the SQL Account.
Thanks
Hari
SQL Server Mvp
"Benny" <Benny@.discussions.microsoft.com> wrote in message
news:0EE6279A-1977-4B3E-A4AB-EC1719C2B447@.microsoft.com...
> How can I block users from using their SQL accounts to access SQL 2000
> server
> remotely thru enterprise manager or sql query analyzer? Appreciate any
> help.
> Thanks.
Subscribe to:
Posts (Atom)