Money Data Type: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

More on querying remote server

i'll just keep posting the problems, as my own archive.
SELECT TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
Issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT Tbl1001."TransactionGUID"
Col1003,Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
)
And tries to bring back 8.5M rows - and takes minutes to return, when really
there is only one row.
Question #1: Why doesn't it include the WHERE clause in the remote query?
If i change the query to:
SELECT TOP 1 TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
It still issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT
Tbl1001."TransactionGUID" Col1003,
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
)
But this time it only brings over 10 rows (somehow), performs a top 1
filter, and returns instantly.
Question #2: How is it bringing over 10 rows only? (hint: cursor)
If i change the query to:
SELECT TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE TransactionNumber = 9679
It issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
)
which returns only one row, and returns instantly. Obviously SQL Server can
*sometimes* do the optimization, othertimes it won't.
Question #3: Why does it include the WHERE clause in the remote query.
If i change the query to:
SELECT TransactionNumber
FROM servertest.CMSArchiveTraining.dbo.Transactions_90
WHERE TransactionDate = '2002-06-10 08:19:10.513'
It issues the remote query:
SOURCE:(servertest),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "cmsarchivetraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionDate"='2002-06-10T08:19:10.513'
)
Question #4: Why does it include the WHERE clause in the remote query?
Now i create a view:
CREATE VIEW CMSArchiveTranasctions AS
SELECT *
FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90
and issue the query:
SELECT TransactionNumber
FROM CMSArchiveTransactions
WHERE TransactionNumber = 9679
It wants to issues the remote query:
SOURCE:(SERVERTEST),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1005
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
)
And tries to bring back 8.5M rows - and takes minutes to return, when really
there is only one row.
Question #5: Why does it not include the WHERE clause in the remote query?
If i change the query to:
SELECT TOP 1 TransactionNumber
FROM CMSArchiveTransactions
WHERE TransactionNumber = 9679
it issues the remote query:
SOURCE:(SERVERTEST),
QUERY:(
SELECT TOP 1 Col1004
FROM (
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
) Qry1018
)
Question #6: Why is it now including the TOP inside the remote query, when
my earlier issue of a TOP query didn't include the TOP limiter?
Question #7: Why is it now including the WHERE clause in the remote query,
when not including a TOP 1 it won't include the where clause limiter?
Question #8: Why does it include the WHERE clause when i have any TOP
limiter (even TOP 999999999, or TOP 100 PERCENT), but does not include the
where clause when i don't?
So i try changing my query to:
SELECT TransactionNumber
FROM (
SELECT *
FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90) t
WHERE TransactionNumber = 9679
And it issues the remote query:
SOURCE:(SERVERTEST),
QUERY:(
SELECT
Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
)
So querying through a view it will not include the WHERE clause, but if i
include the view as a derived table, it can include the WHERE clause.
Question #9: Why does it include the WHERE clause when i query through a
derived table, and not through a view?
So i try changing the query to
SELECT TransactionNumber
FROM (
SELECT *
FROM CMSArchiveTransactions) t
WHERE TransactionNumber = 9679
So here we are, pay attention to this one. If i attempt to query the view
directly, it doesn't get optimized. Now i am going to query my view THROUGH
a derived table. What do you think it will do?
SOURCE:(SERVERTEST),
QUERY:(
SELECT Tbl1001."TransactionNumber" Col1004
FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
WHERE Tbl1001."TransactionNumber"=(9679)
)
It in fact now DOES include the where clause, but if i try to query my view
directly, it can't optimize it. This is a stunning development.
It means the rules are random, which makes it difficult to optimize
distributed queries.
Rules for including where clause:
Filter on uniqueidentifer:
Query includes TOP 1: Yes*
Query includes TOP 2: No
Query includes TOP n: No
Query includes TOP n PERCENT: No
Query doesn't include TOP: No
Filter on integer:
Query linked SQL Server: Yes
Query view that queries linked SQL Server:
Query view directly:
Query includes TOP 1: Yes
Query includes TOP 2: Yes
Query includes TOP 100: Yes
Query includes TOP 999999999: Yes
Query includes TOP 100 PERCENT: Yes
Query doesn't include TOP: No
Query view through derived table:
Query includes TOP 1: Yes
Query includes: TOP 2: Yes
Query includes: TOP n: Yes
Query includes: TOP 100 PERCENT: Yes
Query includes TOP: YesYou haven't posted any DDL. Are there any indexes on the remote table? Which
columns are indexed?
ML
http://milambda.blogspot.com/|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:E89D60ED-EAEF-46F9-9F31-1D56842B277E@.microsoft.com...
> You haven't posted any DDL. Are there any indexes on the remote table?
> Which
> columns are indexed?
Before answering that, let me ask you this: why does it matter? It doesn't
seem to matter *sometimes*. And even if there aren't an indexes on the
remote tables, everyone can agree that sending any filtering criteria to a
remote server is faster than trying to filter rows after they've crossed a
link.
Even a table scan on a remote server is better than bringing the rows over a
network to be filtered.
Additionally, my post is not dealing with query performance. When SQL Server
does what it should do, the queries run fine. If SQL Server doesn't do what
it's supposed to do, the queries do not run fine. Indexes are not the
performance limitation here.
That having been said: yes.|||If you think you know your data better than the optimizer does (as might be
the case - especially with outdated statistics), you could use the REMOTE
join hint to tell the optimizer to process the join on the remote server.
Basically, the optimizer can only rely on statistics. If they are missing on
the remote site, he prefers "staying local" although filtering data remotely
might have yielded better performance. Why put a stress on a remote server i
f
poor performance is expected anyway?
But when statistics are up to date on both sites, then the optimizer still
has full control over the execution of queries (unless specified otherwise
through the use of hints) which basically means that he can still make the
wrong assumptions - and I'm as much in the dark here as you are.
ML
http://milambda.blogspot.com/|||> Basically, the optimizer can only rely on statistics. If they are missing
> on
> the remote site, he prefers "staying local" although filtering data
> remotely
> might have yielded better performance. Why put a stress on a remote server
> if
> poor performance is expected anyway?
i guess this is where common sense gets to take a back seat.

> But when statistics are up to date on both sites, then the optimizer still
> has full control over the execution of queries (unless specified otherwise
> through the use of hints) which basically means that he can still make the
> wrong assumptions - and I'm as much in the dark here as you are.
Is there anyone who could explain the bewildering optimizer choices?|||Can anyone
ever imagine
any situation
under any circumstances
in any manner
in any capacity
in any way
with any product
running on any kind of data link
fast or slow
on any database setup
existing in any universe
moving at any velocity
at any time since the big bang itself
where it would be better to filter rows after fetching them?|||Here is some SQL to create a table named "Transactions", and will contain
some fields including "TransactionDate", "TransactionNumber",
"TransactionGUID"
USE pubs
go
DROP TABLE Transactions
go
CREATE Table Transactions (
[TransactionID] [int] NOT NULL IDENTITY,
TransactionGUID uniqueidentifier NOT NULL default newid(),
[title] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[price] [money] NULL ,
[advance] [money] NULL ,
[royalty] [int] NULL ,
[ytd_sales] [int] NULL ,
[notes] [varchar] (200) NULL ,
[TransactionDate] [datetime] NOT NULL DEFAULT (getdate())
)
go
INSERT INTO Transactions (title, type, pub_id, price, advance, royalty,
ytd_sales, notes, TransactionDate)
SELECT
[title],
[type],
[pub_id],
[price],
[advance],
[royalty],
[ytd_sales],
[notes],
CAST(CAST([pubdate] as real) + RAND(3234)*2000 - 1000 AS datetime)
FROM Titles
CROSS JOIN (
SELECT (a.Number * 256) + b.Number AS Number
FROM master..spt_values a,
master..spt_values b
WHERE a.Type = 'p'
AND b.Type = 'p') numbers
You'll have to create the linked server yourself, you'll need two servers.
You'll also have to substitute your own values for TransactionGUID,
TransactionDate, TransactionNumber|||I would hazard a guess that the local SQL server has no knowledge of the
statistics on the remote server and can only determine which query to send
across to the remote server based on the existence of keys (possibly indexes
or unique indexes).
The question would be what indexes exist on the remote table. I believe if
you look at these closely your results will turn out to be much more
consistent than you think.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:064A71B2-9B67-4F97-8635-6854A4B9551C@.microsoft.com...
> If you think you know your data better than the optimizer does (as might
be
> the case - especially with outdated statistics), you could use the REMOTE
> join hint to tell the optimizer to process the join on the remote server.
> Basically, the optimizer can only rely on statistics. If they are missing
on
> the remote site, he prefers "staying local" although filtering data
remotely
> might have yielded better performance. Why put a stress on a remote server
if
> poor performance is expected anyway?
> But when statistics are up to date on both sites, then the optimizer still
> has full control over the execution of queries (unless specified otherwise
> through the use of hints) which basically means that he can still make the
> wrong assumptions - and I'm as much in the dark here as you are.
>
> ML
> --
> http://milambda.blogspot.com/|||I am guessing to a fair extent here, but some possible (and I think likely)
explanations for this behavior...
The main thing is, I think, that SQL Server has no statistics available when
accessing a remote database and makes a best guess based on the existence of
keys.
Incidently, when working with Oracle 8 I found similar issues with their
"database links".

>Question #1: Why doesn't it include the WHERE clause in the remote query?
No Index on transactionGUID?

>Question #2: How is it bringing over 10 rows only? (hint: cursor)
10 rows is the size of the chunks retrieved. The top 1 is in the first ten
rows, so SQL Server doesnt bother retrieving any further data from the
remote database.

>Question #3: Why does it include the WHERE clause in the remote query.
Index or PK on TransactionNumber?

>Question #4: Why does it include the WHERE clause in the remote query?
Index or PK on TransactionDate?

>Question #5: Why does it not include the WHERE clause in the remote query?
Entire view is retrieved. The optimizer will sometimes do this with local
views as well, depending on how they are used.

>Question #6: Why is it now including the TOP inside the remote query, when
>my earlier issue of a TOP query didn't include the TOP limiter?
>Question #7: Why is it now including the WHERE clause in the remote query,
>when not including a TOP 1 it won't include the where clause limiter?
>Question #8: Why does it include the WHERE clause when i have any TOP
>limiter (even TOP 999999999, or TOP 100 PERCENT), but does not include the
>where clause when i don't?
Top says return only X rows from the view. Because it needs to order the
results, a different execution plan is returned. This will happen with
local views also, depending on statistics and how they are used.

>Question #9: Why does it include the WHERE clause when i query through a
>derived table, and not through a view?
The local SQL server is rewriting your select to directly access the table.
This particular SQL may look different to a human, but logically, and to SQL
Server, it is identical to your 3rd example. This is perfectly consistent.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:O$7N6IZWGHA.4424@.TK2MSFTNGP05.phx.gbl...
> i'll just keep posting the problems, as my own archive.
> SELECT TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
> Issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT Tbl1001."TransactionGUID"
> Col1003,Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> )
> And tries to bring back 8.5M rows - and takes minutes to return, when
really
> there is only one row.
> Question #1: Why doesn't it include the WHERE clause in the remote query?
> If i change the query to:
> SELECT TOP 1 TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE transactionGUID = '4956E240-8B6E-437E-B9B0-5E83FC25E3F0'
> It still issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT
> Tbl1001."TransactionGUID" Col1003,
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> )
> But this time it only brings over 10 rows (somehow), performs a top 1
> filter, and returns instantly.
> Question #2: How is it bringing over 10 rows only? (hint: cursor)
> If i change the query to:
> SELECT TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE TransactionNumber = 9679
> It issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> )
> which returns only one row, and returns instantly. Obviously SQL Server
can
> *sometimes* do the optimization, othertimes it won't.
> Question #3: Why does it include the WHERE clause in the remote query.
> If i change the query to:
> SELECT TransactionNumber
> FROM servertest.CMSArchiveTraining.dbo.Transactions_90
> WHERE TransactionDate = '2002-06-10 08:19:10.513'
> It issues the remote query:
> SOURCE:(servertest),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "cmsarchivetraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionDate"='2002-06-10T08:19:10.513'
> )
> Question #4: Why does it include the WHERE clause in the remote query?
> Now i create a view:
> CREATE VIEW CMSArchiveTranasctions AS
> SELECT *
> FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90
> and issue the query:
> SELECT TransactionNumber
> FROM CMSArchiveTransactions
> WHERE TransactionNumber = 9679
> It wants to issues the remote query:
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1005
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> )
> And tries to bring back 8.5M rows - and takes minutes to return, when
really
> there is only one row.
> Question #5: Why does it not include the WHERE clause in the remote query?
> If i change the query to:
> SELECT TOP 1 TransactionNumber
> FROM CMSArchiveTransactions
> WHERE TransactionNumber = 9679
> it issues the remote query:
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT TOP 1 Col1004
> FROM (
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> ) Qry1018
> )
> Question #6: Why is it now including the TOP inside the remote query, when
> my earlier issue of a TOP query didn't include the TOP limiter?
> Question #7: Why is it now including the WHERE clause in the remote query,
> when not including a TOP 1 it won't include the where clause limiter?
> Question #8: Why does it include the WHERE clause when i have any TOP
> limiter (even TOP 999999999, or TOP 100 PERCENT), but does not include the
> where clause when i don't?
> So i try changing my query to:
> SELECT TransactionNumber
> FROM (
> SELECT *
> FROM SERVERTEST.CMSArchiveTraining.dbo.Transactions_90) t
> WHERE TransactionNumber = 9679
> And it issues the remote query:
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT
> Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> )
> So querying through a view it will not include the WHERE clause, but if i
> include the view as a derived table, it can include the WHERE clause.
> Question #9: Why does it include the WHERE clause when i query through a
> derived table, and not through a view?
> So i try changing the query to
> SELECT TransactionNumber
> FROM (
> SELECT *
> FROM CMSArchiveTransactions) t
> WHERE TransactionNumber = 9679
> So here we are, pay attention to this one. If i attempt to query the view
> directly, it doesn't get optimized. Now i am going to query my view
THROUGH
> a derived table. What do you think it will do?
> SOURCE:(SERVERTEST),
> QUERY:(
> SELECT Tbl1001."TransactionNumber" Col1004
> FROM "CMSArchiveTraining"."dbo"."Transactions_90" Tbl1001
> WHERE Tbl1001."TransactionNumber"=(9679)
> )
> It in fact now DOES include the where clause, but if i try to query my
view
> directly, it can't optimize it. This is a stunning development.
> It means the rules are random, which makes it difficult to optimize
> distributed queries.
>
> Rules for including where clause:
> Filter on uniqueidentifer:
> Query includes TOP 1: Yes*
> Query includes TOP 2: No
> Query includes TOP n: No
> Query includes TOP n PERCENT: No
> Query doesn't include TOP: No
> Filter on integer:
> Query linked SQL Server: Yes
> Query view that queries linked SQL Server:
> Query view directly:
> Query includes TOP 1: Yes
> Query includes TOP 2: Yes
> Query includes TOP 100: Yes
> Query includes TOP 999999999: Yes
> Query includes TOP 100 PERCENT: Yes
> Query doesn't include TOP: No
> Query view through derived table:
> Query includes TOP 1: Yes
> Query includes: TOP 2: Yes
> Query includes: TOP n: Yes
> Query includes: TOP 100 PERCENT: Yes
> Query includes TOP: Yes
>|||Absolutely. The same way there are cases when a full table scan is better
than using an index.
If you have 1,000 rows in a table and you add a filter on and indexed field
that returns 999 records, then SQL server will do a full table scan instead
of using the index.
If you have 1,000 rows in a remote table and you add a filter that returns
999 records, let the database that is actually going to use the data spend
the cycles filtering it. Let the remote server just do a quick IO and send
the data over the network with minimal CPU usage.
Now, if the local server has statistics on the tables in the remote
database, then it could make much better decisions regarding when to send
the filter and when to not. Because it doesn't know the remote database, it
has no idea how many rows will be returned (unless querying on a key field
maybe?) and makes a (rather useless) judgement call.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23fsEP7ZWGHA.2064@.TK2MSFTNGP03.phx.gbl...
> Can anyone
> ever imagine
> any situation
> under any circumstances
> in any manner
> in any capacity
> in any way
> with any product
> running on any kind of data link
> fast or slow
> on any database setup
> existing in any universe
> moving at any velocity
> at any time since the big bang itself
> where it would be better to filter rows after fetching them?
>sql

Wednesday, March 28, 2012

More Info: Stored Procedure Security Question

Dear Group

I have found that table A had SELECT permissions for 'Public' but not table
B.
Giving 'Public' SELECT permissions on table B did the trick.

HOWEVER, I don't want anyone to be able to do a direct SELECT on table A or
B but only give them access to the data by using the stored procedures. Is
there any way this can be set up?

Thanks for your efforts!
Have a nice day!

Martin

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message news:...
> Dear Group
> I'm having two stored procedures, sp_a and sp_b
> Content of stored procedure A:
> CREATE PROCEDURE dbo.sp_a
> SELECT * FROM a
> GO
> Content of stored procedure B:
> CREATE PROCEDURE dbo.sp_b
> SELECT * FROM b
> GO
> I have created a user that has execute permissions for both procedures.
> When I run procedure A, all works fine but when running procedure B I'm
> getting an error saying that the user must have SELECT permissions on
> table B.
> Both tables are owned by dbo, and the security role for the user doesn't
> has any SELECT permission on table a and b.
> I'd be grateful if anyone could point me in a direction why this error
> might come up for procedure B but not for A,
> with a possible solution without giving the user SELECT permissions.
> Thanks very much for your help!
> MartinMartin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
>> Content of stored procedure A:
>> CREATE PROCEDURE dbo.sp_a
>> SELECT * FROM a
>> GO
>>
>> Content of stored procedure B:
>> CREATE PROCEDURE dbo.sp_b
>> SELECT * FROM b
>> GO
> I have found that table A had SELECT permissions for 'Public' but not
> table B. Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or B but only give them access to the data by using the stored
> procedures. Is there any way this can be set up?

I have a strong feeling that you are not telling us the full story,
because what you have described is the typical usage of ownership
chaining, and users should indeed be able to access the data in the
tables through the stored procedures.

Is there by chance some dynamic SQL involved?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I also responded to your previous thread. As Erland said, this should work
as long as the objects are in the same database. If in different databases,
you'll need to enable cross-database chaining and the databases need to have
the same owner in order to maintain an unbroken ownership chain for
dbo-owned objects.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:cg53pg$rbo$1@.sparta.btinternet.com...
> Dear Group
> I have found that table A had SELECT permissions for 'Public' but not
table
> B.
> Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
or
> B but only give them access to the data by using the stored procedures. Is
> there any way this can be set up?
> Thanks for your efforts!
> Have a nice day!
> Martin
>
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:...
> > Dear Group
> > I'm having two stored procedures, sp_a and sp_b
> > Content of stored procedure A:
> > CREATE PROCEDURE dbo.sp_a
> > SELECT * FROM a
> > GO
> > Content of stored procedure B:
> > CREATE PROCEDURE dbo.sp_b
> > SELECT * FROM b
> > GO
> > I have created a user that has execute permissions for both procedures.
> > When I run procedure A, all works fine but when running procedure B I'm
> > getting an error saying that the user must have SELECT permissions on
> > table B.
> > Both tables are owned by dbo, and the security role for the user doesn't
> > has any SELECT permission on table a and b.
> > I'd be grateful if anyone could point me in a direction why this error
> > might come up for procedure B but not for A,
> > with a possible solution without giving the user SELECT permissions.
> > Thanks very much for your help!
> > Martin|||Thanks for your help guys!
Well, as Erland suspected, I haven't given you the full story as I
thought it doesn't matter but as I found out the hard way, it was
indeed dynamic SQL that caused the problem.

Erland, please don't tell me off for using dynamic SQL! LOL
I've read your wonderful fantastic guides and obbey all rules on
dynamic SQL but although I'm not happy myself, I think I really can't
avoid it this time.
However, if you'd offer to have a look at my script and just tell me
whether it can be done without dynamic SQL then this would make me
very happy!

Anyway, I solved the permission problem by basing the stored
procedures that contain the dynamic SQL on Views and implementing row
level security in those.

Thanks again for your efforts!
Have a nice day!

Martin

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<07JVc.8691$3O3.3742@.newsread2.news.pas.earthlink.n et>...
> I also responded to your previous thread. As Erland said, this should work
> as long as the objects are in the same database. If in different databases,
> you'll need to enable cross-database chaining and the databases need to have
> the same owner in order to maintain an unbroken ownership chain for
> dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:cg53pg$rbo$1@.sparta.btinternet.com...
> > Dear Group
> > I have found that table A had SELECT permissions for 'Public' but not
> table
> > B.
> > Giving 'Public' SELECT permissions on table B did the trick.
> > HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or
> > B but only give them access to the data by using the stored procedures. Is
> > there any way this can be set up?
> > Thanks for your efforts!
> > Have a nice day!
> > Martin
> > "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:...
> > > Dear Group
> > > > I'm having two stored procedures, sp_a and sp_b
> > > > Content of stored procedure A:
> > > CREATE PROCEDURE dbo.sp_a
> > > SELECT * FROM a
> > > GO
> > > > Content of stored procedure B:
> > > CREATE PROCEDURE dbo.sp_b
> > > SELECT * FROM b
> > > GO
> > > > I have created a user that has execute permissions for both procedures.
> > > When I run procedure A, all works fine but when running procedure B I'm
> > > getting an error saying that the user must have SELECT permissions on
> > > table B.
> > > > Both tables are owned by dbo, and the security role for the user doesn't
> > > has any SELECT permission on table a and b.
> > > I'd be grateful if anyone could point me in a direction why this error
> > > might come up for procedure B but not for A,
> > > with a possible solution without giving the user SELECT permissions.
> > > > Thanks very much for your help!
> > > > Martin
>|||Martin (theintrepidfox@.hotmail.com) writes:
> Erland, please don't tell me off for using dynamic SQL! LOL
> I've read your wonderful fantastic guides and obbey all rules on
> dynamic SQL but although I'm not happy myself, I think I really can't
> avoid it this time.
> However, if you'd offer to have a look at my script and just tell me
> whether it can be done without dynamic SQL then this would make me
> very happy!

Well, there are cases where dynamic SQL is the best solution and there
are cases where dynamic SQL is a really poor choice.

The whole message of http://www.sommarskog.se/dyn-search.html is that
for dynamic search conditions is "use dynamic SQL, if you can handle
the security issues". If you can make it with views, then you should
be fine.

Beware though, that a very skilled person can be able to cram out
information from a view for row-based security that he is not supposed
to have access to. It is not that he can actually get to see the rows,
but he can make conclusions from query plans statistical IO and such.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks very much Erland!

>It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.

Do you know any source with more information on this view security issue?
What harm can it do? It's a CRM app, not a top secret military app.

Thanks for your efforts!

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns954FEB97D2796Yazorman@.127.0.0.1>...
> Martin (theintrepidfox@.hotmail.com) writes:
> > Erland, please don't tell me off for using dynamic SQL! LOL
> > I've read your wonderful fantastic guides and obbey all rules on
> > dynamic SQL but although I'm not happy myself, I think I really can't
> > avoid it this time.
> > However, if you'd offer to have a look at my script and just tell me
> > whether it can be done without dynamic SQL then this would make me
> > very happy!
> Well, there are cases where dynamic SQL is the best solution and there
> are cases where dynamic SQL is a really poor choice.
> The whole message of http://www.sommarskog.se/dyn-search.html is that
> for dynamic search conditions is "use dynamic SQL, if you can handle
> the security issues". If you can make it with views, then you should
> be fine.
> Beware though, that a very skilled person can be able to cram out
> information from a view for row-based security that he is not supposed
> to have access to. It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.|||Martin (theintrepidfox@.hotmail.com) writes:
> Do you know any source with more information on this view security issue?
> What harm can it do? It's a CRM app, not a top secret military app.

As long as you don't let SQL Server MVP Steve Kass anywhere near the
database, I think your data is fairly safe. :-) That is, Steve Kass was
the one who discovered this issue, and to exploit you would need to
a query tool like Query Analyzer, and you would probably have to have
some knowledge about the schema. And you need a very good understanding
of SQL Server. Finally a good dosis of patience is good for the task.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

More info

In the the Log File Viewer, I select Maintenace Plan and there I see to rows
marked with a green icon and a + sign to the left... I clicked on the + to
reveal what's under the row.. then it revealed a row with an red stop icon
to the left...
On one of the rows with red stop icon, I read this error message:
Could not generate mail report.An exception occurred while executing a
Transact-SQL statement or batch.No global profile is configured. Specify a
profile name in the @.profile_name parameter.
any suggestions?Hi Jeff
It looks like it is having problems sending the email notification. Try
changing the maintenance plan to remove these. There is probably a notify
operator task that needs to be re-configured. Also check that you can send
test emails for database mail and SQL Agent mail configuration (in the
properties)
John
"Jeff" wrote:

> In the the Log File Viewer, I select Maintenace Plan and there I see to ro
ws
> marked with a green icon and a + sign to the left... I clicked on the + to
> reveal what's under the row.. then it revealed a row with an red stop icon
> to the left...
> On one of the rows with red stop icon, I read this error message:
> Could not generate mail report.An exception occurred while executing a
> Transact-SQL statement or batch.No global profile is configured. Specify a
> profile name in the @.profile_name parameter.
> any suggestions?
>
>|||I modifyed the Maintenance Plan -> selected the Maintenance plan I selected
"Modify". then I opened the "Reporting and Logging" window and unchecked
"Send report to an email recipient". Maybe the error was here... the agent
operator set as recipient was an domain account which not exist anymore
It's a long time since this domain account was removed - strange if caused
problem for sqlserver now
I saved my modifications to the maintenance plan...
How do I manually test if this actually solved the problem'
Best Regards
Jeff|||Hi Jeff
It may be that the account had been disabled but the mailbox remained until
more recently!!
At least everything works now!
John
"Jeff" wrote:

> I modifyed the Maintenance Plan -> selected the Maintenance plan I selecte
d
> "Modify". then I opened the "Reporting and Logging" window and unchecked
> "Send report to an email recipient". Maybe the error was here... the agent
> operator set as recipient was an domain account which not exist anymore
> It's a long time since this domain account was removed - strange if caused
> problem for sqlserver now
> I saved my modifications to the maintenance plan...
> How do I manually test if this actually solved the problem'
> Best Regards
> Jeff
>
>|||Right click on the maintanence plan and select Execute from the popup menu
and View History for that plan (by right clicking on it again...)
Ekrem nsoy
"Jeff" <donot@.spam.me> wrote in message
news:eDsSQBLQIHA.4584@.TK2MSFTNGP03.phx.gbl...
>I modifyed the Maintenance Plan -> selected the Maintenance plan I selected
>"Modify". then I opened the "Reporting and Logging" window and unchecked
>"Send report to an email recipient". Maybe the error was here... the agent
>operator set as recipient was an domain account which not exist anymore
> It's a long time since this domain account was removed - strange if caused
> problem for sqlserver now
> I saved my modifications to the maintenance plan...
> How do I manually test if this actually solved the problem'
> Best Regards
> Jeff
>

More errors in convert function

In hijri calender any month can be 30 or 29 day
in convert function
if I write
select convert (datetime ,'29-12-1426,131) it is ok
this year 12 month is 30 days
but if I write
select convert (datetime ,'30-12-1426,131)
ther is an error

Thanks for any help

Tamer229,
There are many variations of the Islamic calendar. In some versions,
there are 29 days in the month Thou Alhajja in the year 1426, and in
others there are 30 days. There are only 29 days in this month
according to the Islamic calendar version used by Microsoft, which
is why you are getting an error when you try to convert the 30th day
of this month. In the Microsoft version, the western date January
30, 2006 is the first day of Muharram, 1427. In other versions of
the Islamic calendar, January 30, 2006 is the 30th day of Thou Alhajja,
1426.
See http://www.phys.uu.nl/~vgent/islam/islamyear_en.htm
for a calculator that shows several versions. The version used
by Microsoft is labeled "Ia [15, astronomical = “Kuwaiti algorithm”]"
Steve Kass
Drew University

Monday, March 26, 2012

More Efficient SQL Statement - Select Count(1)

I know that you can write select statement (Select Count(1)) within Oracle
if finds one record it will stop the search critieria.
Please help me modify the SQL statement listed below to use the equivalent
if it finds one records it stops and output is 1 if not the output is 0.
Thank You,
SET @.COUNT_CALLS_REC_1 =
(Select count(Icent_Num)
from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))Preface the query with a return limitation -ROWCOUNT.
USE Northwind
GO
SET ROWCOUNT 0
SELECT CustomerID
FROM Customers
WHERE Country = 'Mexico'
SET ROWCOUNT 0
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-2
5436FBEC8C9@.microsoft.com...
>
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
>
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
>
> Thank You,
>
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||something like this?
if exists(
Select 1 from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472')
)
SET @.COUNT_CALLS_REC_1 = 1
else
SET @.COUNT_CALLS_REC_1 = 0
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Sorry, the first SET ROWCOUNT should be 1 -NOT 0.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23ojjUjHnGHA.2264@.TK
2MSFTNGP04.phx.gbl...
Preface the query with a return limitation -ROWCOUNT.
USE Northwind
GO
SET ROWCOUNT 0
SELECT CustomerID
FROM Customers
WHERE Country = 'Mexico'
SET ROWCOUNT 0
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-2
5436FBEC8C9@.microsoft.com...
>
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
>
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
>
> Thank You,
>
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||Joe k.,
Use the operator "exists" and do not manipulate the columns in the "where"
clause as possible, to let SQL Server to use the indexes optimally in case
they exists.
if exists (
select *
from TKCalls.dbo.tblCalls
where
StartedTime between dateadd(minute, -30, GETDATE()) and GETDATE()
and cast(Icent_Num as varchar(20)) like '962472%'
)
set ...
else
set ...
go
AMB
"Joe K." wrote:

> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
> Thank You,
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||Are you sure that on Oracle, the statement SELECT COUNT(1) will never
return a value > 1? I would be very surprised if this is the Oracle
behavior, because that is not what the SQL statement is specifying...
Gert-Jan
Joe K. wrote:
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
> Thank You,
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||doesn't "select top 1 * from ..." work for you?
Jos=E9.
Gert-Jan Strik wrote:
> Are you sure that on Oracle, the statement SELECT COUNT(1) will never
> return a value > 1? I would be very surprised if this is the Oracle
> behavior, because that is not what the SQL statement is specifying...
> Gert-Jan
>
> Joe K. wrote:
cle
ent|||This should work as well...
scott0100
---
scott0100's Profile: http://www.dbtalk.net/m491
View this thread: http://www.dbtalk.net/t316762|||As I recall, one of the issues with TOP 1 is that it has to find all records
(or at least indexes) first in order to determine which one is the TOP 1. So
that may not be 'efficient'.
;-)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<josearaujof@.gmail.com> wrote in message
news:1151700301.773682.267740@.75g2000cwc.googlegroups.com...
doesn't "select top 1 * from ..." work for you?
Jos.
Gert-Jan Strik wrote:
> Are you sure that on Oracle, the statement SELECT COUNT(1) will never
> return a value > 1? I would be very surprised if this is the Oracle
> behavior, because that is not what the SQL statement is specifying...
> Gert-Jan
>
> Joe K. wrote:|||> As I recall, one of the issues with TOP 1 is that it has to find all records (or at least
indexes)
> first in order to determine which one is the TOP 1. So that may not be 'efficient'
.
Only with ORDER BY. Without ORDER BY, you ask for any one row, so the optimi
zer and engine know it
can stop after the first row it encounters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23G0c2mInGHA.3388@.TK2MSFTNGP05.phx.
gbl...
> As I recall, one of the issues with TOP 1 is that it has to find all recor
ds (or at least indexes)
> first in order to determine which one is the TOP 1. So that may not be 'ef
ficient'.
> ;-)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <josearaujof@.gmail.com> wrote in message
> news:1151700301.773682.267740@.75g2000cwc.googlegroups.com...
> doesn't "select top 1 * from ..." work for you?
> Jos.
> Gert-Jan Strik wrote:
>

more efficient - exists or in

Which is more efficient:

Select * from table1 where id in (select id from table2)

Select * from table1 where exists(select * from table2 where
table2.id=table1.id)On Wed, 11 Aug 2004 14:53:52 +0100, Trev@.Work wrote:

>Which is more efficient:
>Select * from table1 where id in (select id from table2)
>or
>Select * from table1 where exists(select * from table2 where
>table2.id=table1.id)

Hi Trev,

That question has no one correct answer; it depends on lots of factors,
such as table structures, whether there are indexes, etc. If you really
want to know, you'll have to test it for each specific situation. I think
that in many cases, the execution plan will be equal. And you firgot to
include the third option:
Select table1.* from table1
inner join table2 on table2.id = table1.id

Another important thing to remember: when you change the query to find
rows not in the other table, behaviour of the first query will become
unpredictable by NULL values in table1.id and table2.id:

CREATE TABLE table1 (id int)
CREATE TABLE table2 (id int)
INSERT table1 (id) SELECT 1
INSERT table1 (id) SELECT 3
INSERT table1 (id) SELECT NULL
INSERT table2 (id) SELECT 1
INSERT table2 (id) SELECT 2
INSERT table2 (id) SELECT NULL
Select * from table1 where id not in (select id from table2)
Select * from table1 where not exists(select * from table2 where
table2.id=table1.id)
Select table1.* from table1
left join table2 on table2.id = table1.id
where table2.id is null
DROP TABLE table1
DROP TABLE table2

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||'Exists' more efficient. SQL Server hardly calculates 'in' comprassions|||Trev@.Work (no.email@.please) writes:
> Which is more efficient:
> Select * from table1 where id in (select id from table2)
> or
> Select * from table1 where exists(select * from table2 where
> table2.id=table1.id)

In 6.5 EXISTS and NOT EXISTS were often big winners over IN and NOT IN.
In SQL 2000, I have feeling that IN/NOT IN are recast into EXISTS
NOT EXISTS, although I have not confirmed this.

Personally, I always use EXISTS and NOT EXISTS, and as Hugo pointed
out there are some gotchas with NOT IN.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||See
http://groups.google.nl/groups?hl=n...ver.programming

Gert-Jan

"Trev@.Work" wrote:
> Which is more efficient:
> Select * from table1 where id in (select id from table2)
> or
> Select * from table1 where exists(select * from table2 where
> table2.id=table1.id)

--
(Please reply only to the newsgroup)|||On Wed, 11 Aug 2004 21:53:07 +0000 (UTC), Erland Sommarskog wrote:

> Trev@.Work (no.email@.please) writes:
>> Which is more efficient:
>>
>> Select * from table1 where id in (select id from table2)
>>
>> or
>>
>> Select * from table1 where exists(select * from table2 where
>> table2.id=table1.id)
> In 6.5 EXISTS and NOT EXISTS were often big winners over IN and NOT IN.
> In SQL 2000, I have feeling that IN/NOT IN are recast into EXISTS
> NOT EXISTS, although I have not confirmed this.
> Personally, I always use EXISTS and NOT EXISTS, and as Hugo pointed
> out there are some gotchas with NOT IN.

One follow-up question: is there a performance difference between

select * from table1 where exists(select * from table2 where
table2.id=table1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table1.id)

(assuming that table1.id and table2.id are clustered primary keys)|||> One follow-up question: is there a performance difference between
> select * from table1 where exists(select * from table2 where
> table2.id=table1.id)
> and
> select * from table1 where exists(select id from table2 where
> table2.id=table1.id)

There are not difference in
exists ( select * ...
exists ( select 1 ...
exists ( select id ...
SQL Server execute second expression for all situations|||On Thu, 12 Aug 2004 19:26:57 +0300, Garry wrote:

>> One follow-up question: is there a performance difference between
>>
>> select * from table1 where exists(select * from table2 where
>> table2.id=table1.id)
>>
>> and
>>
>> select * from table1 where exists(select id from table2 where
>> table2.id=table1.id)
> There are not difference in
> exists ( select * ...
> exists ( select 1 ...
> exists ( select id ...
> SQL Server execute second expression for all situations

thanks|||Ross Presser (rpresser@.imtek.com) writes:
> One follow-up question: is there a performance difference between
> select * from table1 where exists(select * from table2 where
> table2.id=table1.id)
> and
> select * from table1 where exists(select id from table2 where
> table2.id=table1.id)
> (assuming that table1.id and table2.id are clustered primary keys)

As far as I know the * or id are only syntactic sugar in this case,
so as Garry says, it does not matter which you use.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

More dumb questions

Still learning so bear with me
1) Say I have a complicated select query (many joins) that I'd like users
to run. Which typically
performs better and why: creating a view with the select query or creating
a stored procedure with
the select query. Assume users don't care which mechanism is used.
2) I read where the rows column in sysindexes shows the number of rows in a
table and is a good
alternative to select count(*). The same article went on to state that this
figure can become inaccurate.
What can cause this figure to not match what a select count(*) would bring
back?
3) with select statements with many left outer joins used, do the order of
the joins matter? Do the joins
filter as they go along or does sql server figure it all out?
4) what is an IX lock? I can't figure it out from my reading. How is it
different from the X lock? Is there
a relationship?1. No telling which will be more efficient. You need to compare both
execution plans to determine that. They may be the same, and maybe not.
2. The values in sysindexes can get 'de-synchronized from the actual
table.row count as the result of bulk insert operations, deleting large
blocks of rows, etc. Re-synchronize by update statistics.
3. Order of JOINs 'can' matter. SQL will do it's best to 'figure it out',
but perhaps not the way you intended.
4. IX Lock =Intent Exclusive. Next in line to have an Exclusive (X) lock. IX
lock is marking one's position in the queue to place an exclusive lock (X)
as soon as any preceding shared locks are removed.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||The decision between a view and a stored procedure is usually based on how
the data will be used. A view can be used like a table, but a stored
procedure cannot. A stored procedure can take parameters, and include other
statements, and error checking. There is lots of info available about
optimizing stored procedures so search the Knowledgebase at
http://support.microsoft.com/search/?adv=1 It's impossible to say which
will perform better. It completely depends on what you're doing, what your
parameters are, how the query or proc is called, etc, etc.
The values in sysindexes can get out of date when you do certain bulk
operations. SQL Server doesn't always update the counts for all bulk inserts
and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
STATISTICS will not do it.
The order of joins should not matter. The query is optimized as a whole and
may be completely rewritten internally.
An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism. It
does not mean a process is waiting for an X lock; if it were waiting, it
would be blocked, it doesn't get a special kind of lock to indicate waiting.
An intent lock is acquired a higher granularities when a regular lock is
acquired on a lower granularity unit. For example, if you have an exclusive
lock on a row, you will get an IX lock on the page and another IX lock on
the table, which will keep other processes from getting a lock on the whole
page or table. So yes, there is a relationship between X and IX.
Two IX locks are compatible with each other (if two processes each have X
locks on separate rows in the same table, they will each have IX locks on
the table itself), but X and IX are not compatible. You can read about "Lock
Compatibility" in the Books Online.
I suggest if you need to follow up on any of these questions, you start a
separate thread. It's a bit confusing to have so many separate topics in a
single message. They are not dumb questions, and each topic is certainly
worth of its own discussion. There is more info you can read about each of
these topics. Once you've done that, feel free to post follow-up questions.
--
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||Thank you. From now on, I'll start separate threads. Sorry for these
questions. I am VB developer
who's been tabbed to be the "database guy" since the company does not want
to hire a real
dba *shrug*.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> The decision between a view and a stored procedure is usually based on how
> the data will be used. A view can be used like a table, but a stored
> procedure cannot. A stored procedure can take parameters, and include
other
> statements, and error checking. There is lots of info available about
> optimizing stored procedures so search the Knowledgebase at
> http://support.microsoft.com/search/?adv=1 It's impossible to say which
> will perform better. It completely depends on what you're doing, what your
> parameters are, how the query or proc is called, etc, etc.
> The values in sysindexes can get out of date when you do certain bulk
> operations. SQL Server doesn't always update the counts for all bulk
inserts
> and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
> STATISTICS will not do it.
> The order of joins should not matter. The query is optimized as a whole
and
> may be completely rewritten internally.
> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism.
It
> does not mean a process is waiting for an X lock; if it were waiting, it
> would be blocked, it doesn't get a special kind of lock to indicate
waiting.
> An intent lock is acquired a higher granularities when a regular lock is
> acquired on a lower granularity unit. For example, if you have an
exclusive
> lock on a row, you will get an IX lock on the page and another IX lock on
> the table, which will keep other processes from getting a lock on the
whole
> page or table. So yes, there is a relationship between X and IX.
> Two IX locks are compatible with each other (if two processes each have X
> locks on separate rows in the same table, they will each have IX locks on
> the table itself), but X and IX are not compatible. You can read about
"Lock
> Compatibility" in the Books Online.
> I suggest if you need to follow up on any of these questions, you start a
> separate thread. It's a bit confusing to have so many separate topics in a
> single message. They are not dumb questions, and each topic is certainly
> worth of its own discussion. There is more info you can read about each of
> these topics. Once you've done that, feel free to post follow-up
questions.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> > Still learning so bear with me
> >
> > 1) Say I have a complicated select query (many joins) that I'd like
users
> > to run. Which typically
> > performs better and why: creating a view with the select query or
> > creating
> > a stored procedure with
> > the select query. Assume users don't care which mechanism is used.
> >
> > 2) I read where the rows column in sysindexes shows the number of rows
in
> > a
> > table and is a good
> > alternative to select count(*). The same article went on to state that
> > this
> > figure can become inaccurate.
> > What can cause this figure to not match what a select count(*) would
> > bring
> > back?
> >
> > 3) with select statements with many left outer joins used, do the order
> > of
> > the joins matter? Do the joins
> > filter as they go along or does sql server figure it all out?
> >
> > 4) what is an IX lock? I can't figure it out from my reading. How is
it
> > different from the X lock? Is there
> > a relationship?
> >
> >
>|||Don't worry, they're all valid questions.
Do you have access to the documentation (Books Online) that comes with SQL
Server? If you can't find it, that should be your first question.
--
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Thank you. From now on, I'll start separate threads. Sorry for these
> questions. I am VB developer
> who's been tabbed to be the "database guy" since the company does not want
> to hire a real
> dba *shrug*.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
>> The decision between a view and a stored procedure is usually based on
>> how
>> the data will be used. A view can be used like a table, but a stored
>> procedure cannot. A stored procedure can take parameters, and include
> other
>> statements, and error checking. There is lots of info available about
>> optimizing stored procedures so search the Knowledgebase at
>> http://support.microsoft.com/search/?adv=1 It's impossible to say which
>> will perform better. It completely depends on what you're doing, what
>> your
>> parameters are, how the query or proc is called, etc, etc.
>> The values in sysindexes can get out of date when you do certain bulk
>> operations. SQL Server doesn't always update the counts for all bulk
> inserts
>> and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
>> STATISTICS will not do it.
>> The order of joins should not matter. The query is optimized as a whole
> and
>> may be completely rewritten internally.
>> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism.
> It
>> does not mean a process is waiting for an X lock; if it were waiting, it
>> would be blocked, it doesn't get a special kind of lock to indicate
> waiting.
>> An intent lock is acquired a higher granularities when a regular lock is
>> acquired on a lower granularity unit. For example, if you have an
> exclusive
>> lock on a row, you will get an IX lock on the page and another IX lock on
>> the table, which will keep other processes from getting a lock on the
> whole
>> page or table. So yes, there is a relationship between X and IX.
>> Two IX locks are compatible with each other (if two processes each have X
>> locks on separate rows in the same table, they will each have IX locks on
>> the table itself), but X and IX are not compatible. You can read about
> "Lock
>> Compatibility" in the Books Online.
>> I suggest if you need to follow up on any of these questions, you start a
>> separate thread. It's a bit confusing to have so many separate topics in
>> a
>> single message. They are not dumb questions, and each topic is certainly
>> worth of its own discussion. There is more info you can read about each
>> of
>> these topics. Once you've done that, feel free to post follow-up
> questions.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Dodo Lurker" <none@.noemailplease> wrote in message
>> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
>> > Still learning so bear with me
>> >
>> > 1) Say I have a complicated select query (many joins) that I'd like
> users
>> > to run. Which typically
>> > performs better and why: creating a view with the select query or
>> > creating
>> > a stored procedure with
>> > the select query. Assume users don't care which mechanism is used.
>> >
>> > 2) I read where the rows column in sysindexes shows the number of rows
> in
>> > a
>> > table and is a good
>> > alternative to select count(*). The same article went on to state that
>> > this
>> > figure can become inaccurate.
>> > What can cause this figure to not match what a select count(*) would
>> > bring
>> > back?
>> >
>> > 3) with select statements with many left outer joins used, do the
>> > order
>> > of
>> > the joins matter? Do the joins
>> > filter as they go along or does sql server figure it all out?
>> >
>> > 4) what is an IX lock? I can't figure it out from my reading. How is
> it
>> > different from the X lock? Is there
>> > a relationship?
>> >
>> >
>>
>|||I do and I did read them. I just can't seem to get my arms around some of
the topics so
I look for a layman's explanation.
Thank you for your help!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#MVNBRSyGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Don't worry, they're all valid questions.
> Do you have access to the documentation (Books Online) that comes with SQL
> Server? If you can't find it, that should be your first question.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> > Thank you. From now on, I'll start separate threads. Sorry for these
> > questions. I am VB developer
> > who's been tabbed to be the "database guy" since the company does not
want
> > to hire a real
> > dba *shrug*.
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> >> The decision between a view and a stored procedure is usually based on
> >> how
> >> the data will be used. A view can be used like a table, but a stored
> >> procedure cannot. A stored procedure can take parameters, and include
> > other
> >> statements, and error checking. There is lots of info available about
> >> optimizing stored procedures so search the Knowledgebase at
> >> http://support.microsoft.com/search/?adv=1 It's impossible to say
which
> >> will perform better. It completely depends on what you're doing, what
> >> your
> >> parameters are, how the query or proc is called, etc, etc.
> >>
> >> The values in sysindexes can get out of date when you do certain bulk
> >> operations. SQL Server doesn't always update the counts for all bulk
> > inserts
> >> and truncates. You can run DBCC UPDATEUSAGE to correct the values.
UPDATE
> >> STATISTICS will not do it.
> >>
> >> The order of joins should not matter. The query is optimized as a whole
> > and
> >> may be completely rewritten internally.
> >>
> >> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection
mechanism.
> > It
> >> does not mean a process is waiting for an X lock; if it were waiting,
it
> >> would be blocked, it doesn't get a special kind of lock to indicate
> > waiting.
> >> An intent lock is acquired a higher granularities when a regular lock
is
> >> acquired on a lower granularity unit. For example, if you have an
> > exclusive
> >> lock on a row, you will get an IX lock on the page and another IX lock
on
> >> the table, which will keep other processes from getting a lock on the
> > whole
> >> page or table. So yes, there is a relationship between X and IX.
> >>
> >> Two IX locks are compatible with each other (if two processes each have
X
> >> locks on separate rows in the same table, they will each have IX locks
on
> >> the table itself), but X and IX are not compatible. You can read about
> > "Lock
> >> Compatibility" in the Books Online.
> >>
> >> I suggest if you need to follow up on any of these questions, you start
a
> >> separate thread. It's a bit confusing to have so many separate topics
in
> >> a
> >> single message. They are not dumb questions, and each topic is
certainly
> >> worth of its own discussion. There is more info you can read about each
> >> of
> >> these topics. Once you've done that, feel free to post follow-up
> > questions.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Dodo Lurker" <none@.noemailplease> wrote in message
> >> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> >> > Still learning so bear with me
> >> >
> >> > 1) Say I have a complicated select query (many joins) that I'd like
> > users
> >> > to run. Which typically
> >> > performs better and why: creating a view with the select query or
> >> > creating
> >> > a stored procedure with
> >> > the select query. Assume users don't care which mechanism is used.
> >> >
> >> > 2) I read where the rows column in sysindexes shows the number of
rows
> > in
> >> > a
> >> > table and is a good
> >> > alternative to select count(*). The same article went on to state
that
> >> > this
> >> > figure can become inaccurate.
> >> > What can cause this figure to not match what a select count(*) would
> >> > bring
> >> > back?
> >> >
> >> > 3) with select statements with many left outer joins used, do the
> >> > order
> >> > of
> >> > the joins matter? Do the joins
> >> > filter as they go along or does sql server figure it all out?
> >> >
> >> > 4) what is an IX lock? I can't figure it out from my reading. How
is
> > it
> >> > different from the X lock? Is there
> >> > a relationship?
> >> >
> >> >
> >>
> >>
> >
> >
>

Friday, March 23, 2012

Month's end

Hi,

I am working on a cube that contains month standings and month totals. This works fine if I select one month.

It goes wrong when I do a year to date selection because it sums up the month standings.

How can I make a cube that does sum up the month total but only returns the last month standings.

With regards,

Constantijn Enders

Hi Constantijn,

If you configure the "month standings" measure (I don't know its details) with the aggregate function: LastNonEmpty, and you use Aggregate() rather than Sum() in your "year to date" calculation, does that work?

http://msdn2.microsoft.com/en-us/library/ms175623.aspx

SQL Server 2005 Books Online

Configuring Measure Properties

Measures have properties that enable you to define how the measures function and to control how the measures appear to users.

...

Property

Definition

AggregateFunction

Determines how measures are aggregated. For more information, see Aggregation Functions.

LastNonEmpty

Semiadditive

Retrieves the value of the last non-empty child member.

|||Or, depending on your data, you may want to use more optimal LastChild aggregation function.sql

Monthly Schedule Problem

I have a question about monthly schedule. I checked all months, select 1st
in 'On week of month' and click 'Mon' in On day of week. It means that the
report will be generated on the First week Monday of each month. But in 2005
February, there is no Monday in the first week.
My question is:
1. Will the report be generated ?
2. If not, is the report generated on February 7 ?
3. Is Sunday count as the first day of week in RS ?I guess it is a little to late to reply to this, but it will run on the
first Monday of the month. Reporting Services uses SQL Agent to do all of
it's scheduling so we are tied directly to how they interpret the occurrence
pattern.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:B5AB2A8E-918E-4909-BD03-381A67D1A4A6@.microsoft.com...
>I have a question about monthly schedule. I checked all months, select 1st
> in 'On week of month' and click 'Mon' in On day of week. It means that
> the
> report will be generated on the First week Monday of each month. But in
> 2005
> February, there is no Monday in the first week.
> My question is:
> 1. Will the report be generated ?
> 2. If not, is the report generated on February 7 ?
> 3. Is Sunday count as the first day of week in RS ?

Monthly report query

Someone please shed some light on how to write a select statement that will only pull out a bunch of records belongs only to a certain month. The field(sys_date) that keeps track of each record is a datatime field. Let's say that I need to select all the records starting from 03/01/2007 to 03/31/2007 at the end of March. I can't hardcode the dates because this report is scheduled to run at the end of every month via a DTS job in Sql 2000. Please help out. Thanks.

blumonde

Found the solution. Just in case anyone needs it:

Where (DATEPART(Month, sys_date) =
DATEPART(Month, GETDATE())) And (DATEPART(Year, sys_date) = DATEPART(Year,
GETDATE()))

Hope that helps.

Friday, March 30, 2012

More Questions on Permission

more questions

More on querying remote server

Wednesday, March 28, 2012

More Info: Stored Procedure Security Question

More info

More errors in convert function

Monday, March 26, 2012

More Efficient SQL Statement - Select Count(1)

more efficient - exists or in

More dumb questions

More dumb questions

Friday, March 23, 2012

Month's end

Monthly Schedule Problem

Monthly report query

Money Data Type

Blog Archive

About Me