Query about Ansi-Syntax

I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively.

Query:
1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance.
Any idea why?

2) If I write a query (shown below), it tries to join table A and B returning large number of rows.

Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'

Why does it try to join the table B with A though there is no join specified.As far as the second query is concerned this is the same as writing

Select A.Col1, A.Col2
from A join B
where A.Col3 = 'xyz'

Basically, don't put the table in the from clause if you are not going to use it because it makes the query REALLY inefficient. This is a cross join and will probably give you heaps of extra records.|||actually, ejustuss, your query generates an error

if we want a cross join, we have to say CROSS JOIN, not just JOIN

and yes, there are many situations where we want to join one table to another, and yet select columns only from one|||This old school syntax can cause a lot of heartburn when you get into multiple outer joins. I do not think MS SQL 2K supports *=

Select A.Col1, A.Col2
from A, B
where A.Col3 = 'xyz'|||I do not think MS SQL 2K supports *=yes, it (still) does
create table Oldschool1
( id tinyint not null primary key identity
, foo varchar(9)
)
insert into Oldschool1 (foo) values ('Curly')
insert into Oldschool1 (foo) values ('Larry')
insert into Oldschool1 (foo) values ('Moe')
insert into Oldschool1 (foo) values ('Shemp')
insert into Oldschool1 (foo) values ('Joe')
insert into Oldschool1 (foo) values ('Curly Joe')

create table Oldschool2
( id tinyint not null primary key identity
, bar varchar(9)
)
insert into Oldschool2 (bar) values ('Curly')
insert into Oldschool2 (bar) values ('Larry')
insert into Oldschool2 (bar) values ('Moe')

select t1.id,t1.foo,t2.id,t2,bar
from Oldschool1 as t1
, Oldschool2 as t2
where t1.foo *= t2.bar
order by 1 :)|||As far as your first question,

The difference between ANSI (inner join) syntax and non-ANSI is that the optimizer will not try to derive joins that are ANSI compliant. That's because you've explicitly defined the joins. The optimizer will have to derive non-ANSI compliant joins. In many cases the query plans will be the same. However there's a greater risk the optimizer won't use the optimal plan if it has to derive the joins.

There's probably some people here that can explain this a lot better than me. Hope this helps.|||As far as your second question,

The results you're getting from that query are called a cartesian product. Since a join hasn't been specified it will match each record from on table to each record from the other. Thus if you have two tables with 100 rows each 10,000 rows will be returned.|||What I was trying to say was that in example 2 of the original post the join was implicit. Sorry about the error in syntax. Where would you want a query of the form

Select A.Col1, A.Col2
from A cross join B
where A.Col3 = 'xyz'

?|||where would you want a cross join?

1. to generate test data, e.g. a large range of dates from integers

2. with a left outer join to find missing many-to-many rows

3. to join a one-row table with application constants such as today's interest rate|||I was actually refering to the query as originally put with no reference to any column in the second table AT ALL. The generation of test data seems like a legitimate use though the other 2 surely require a reference to a column from the 2nd table in the the query somewhere even if only in the join condition.

Its just that I have seen queries with additional tables in the from clause which were unnecessary and unused and actually increased the amount of time taken to run the query substanitially.|||oh, that question

okay, well, the join would be used to ensure existence of a related row, even if you didn't need to actually return any data from the second table

for example, assume car owners are in one table, and parking tickets are in another, you might say "give me the names of every owner who had a parking ticket" and not return any row from the parking ticket table at all|||Well in this case would you not say something along the lines of

select car.owner
from car,ticket
where car.owner = ticket.reciever

?

my point being that you are actually referencing a column from the ticket table in the where clause (or join condition depending on how you write the query) - which is not what is happening in the original example.|||...which is not what is happening in the original example.that's right, it's a cross join

query a table on different server instance

I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!
First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!

query a table on different server instance

I have a table TA in database A on SQL server instance IA
and I have a stored procedure in database B on SQL server instance IB.
in stored procedure I want to select all from table TA,
could you tell me what is the correct select statement?
THANKS!First create a linked server (say, LinkedServerName) for <server name>\IA,
then you can say
SELECT <column names>
FROM [LinkedServerName].A.dbo.TA;
"bangwo" <bangwo@.discussions.microsoft.com> wrote in message
news:98010BE5-6C18-4DE6-9C4F-ADFE8E54DD6B@.microsoft.com...
>I have a table TA in database A on SQL server instance IA
> and I have a stored procedure in database B on SQL server instance IB.
> in stored procedure I want to select all from table TA,
> could you tell me what is the correct select statement?
> THANKS!sql

query "For XML" problem?...or not!

hi guys

i'm developing a multi-tier web application which at a given point calls a stored procedure that returns data formated as XML ( 'select .... for xml auto' basically).

That SP returns a list of tasks allocated to 1 or more resources.

The problem is that from time to time (and that is totally random, anything from 30seconds to 10mins) whenever the code calls that SP, the XML structure isn't there, so no data is listed.

Question is: is this a problem within SQL Server XML support or anything related to .NET?

I'm using .NET 2.0.50727 with VS2005 8.0.50727.42, and SQL Server 2005 and IE 6.0.2900

Also, if i refresh the browser and go to the Task listing page, the list comes back again!

Note that i'm NOT using SESSIONs in ANY point, so this has nothing to do with sessions.

TIA

Sérgio Charrua
www.pdmfc.com
Portugal

Hi Sergio,

SQL Server XML support is very useful for some issues. But It isn't scalable solution.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||It's diffcult to figure out what's causing the problem. From SQL side, if a SP returns XML data, it just returns a binary stream to the client as other returned data. I suggest you open SQL Profiler to trace the SQL Server when the prolbem repros, so that you can clarify whehter SQL returns expected result set or empty (none) result, or maybe some network issue.sql

query - stored procedure

when we should use query and when should we use stored proceduse?
Thanks
PaulHi,
Please have look into the below link, it says the reason why procedure is
better than a SQL .
http://www.sql-server-performance.c..._procedures.asp
Thanks
Hari
MCDBA
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:#izHeGm4DHA.504@.TK2MSFTNGP11.phx.gbl...

quote:

---

> when we should use query and when should we use stored proceduse?
> Thanks
> Paul
>

---

sql

query - stored procedure

when we should use query and when should we use stored proceduse?
Thanks
PaulHi,
Please have look into the below link, it says the reason why procedure is
better than a SQL .
http://www.sql-server-performance.com/stored_procedures.asp
Thanks
Hari
MCDBA
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:#izHeGm4DHA.504@.TK2MSFTNGP11.phx.gbl...
> when we should use query and when should we use stored proceduse?
> Thanks
> Paul
>

Query - check for a string in stored procedure

Hi,

I would like to check if a string value exist in a string in sql
server stored procedure, e.g.

set @.testString = 'this is my test document.'
if (@.testString contains 'test')
begin
....
end

How do I do this in sql server stored procedure?

Thanks,
June...try this

Declare @.testString varchar(100)
set @.testString = 'this is my test document.'
if charindex('test',@.testString)>0

begin
....
end

Madhivanan

Query

Hi!

I need to create a query(stored procedure) that will select all rows from the second table and only the rows from table 1 where the maxValues for Col1 and Col2 do not exist in table 2 so that the result would look something like table 3

table 1

id	Col1	Col2	maxValues
1	aa	bb	10
2	cc	dd	10
3	ee	ff	10
4	gg	hh	10
5	jj	kk	10

table 2

id	Col1	Col2	CurrentValue
1	cc	dd	7
2	gg	hh	3
3	jj	kk	5

table 3

id	Col1	Col2	AvailableEntries
1	aa	bb	10
2	cc	dd	7
3	ee	ff	10
4	gg	hh	3
5	jj	kk	5

Thanks for your help.

If ALL values in table2 are also in table1, and maxValue always >= currentValue, then this should work:
select col1, col2, MIN(theValues)
from (
select col1, col2, maxValues as 'theValues'
from table1
UNION
select col1, col2, currentValue as 'theValues'
from table2
) as tempTable
group by col1, col2
|||It is perfect, thank you

Query

Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...Try:
use master
go
sp_helptext sp_databases
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
Hai all,
Is it possible to view the contents in built-in stored procedure
like sp_databases?
If possible, can anyone tell how to view it?
Looking forward or the response...
Thanx in advance...|||Hai..
it works... thanx...
Tom Moreau wrote:
> Try:
> use master
> go
> sp_helptext sp_databases
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Prince" <princevictor.moses@.gmail.com> wrote in message
> news:1142334055.274265.194010@.i40g2000cwc.googlegroups.com...
> Hai all,
> Is it possible to view the contents in built-in stored procedure
> like sp_databases?
> If possible, can anyone tell how to view it?
> Looking forward or the response...
> Thanx in advance...

Queries or SP

hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the codeIt is better to use SP, as it has got many advantages|||thanx for answering
but can u list some of them|||Stored Procedure Advantages
Although you can do most of the things a stored procedure can do with simple ad hoc Transact-SQL code, stored procedures have a number of advantages over ad hoc queries, including

Execution plan retention and reuse

Query autoparameterization

Encapsulation of business rules and policies

Application modularization

Sharing of application logic between applications

Access to database objects that is both secure and uniform

Consistent, safe data modification

Network bandwidth conservation

Support for automatic execution at system start-up

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=3

Try seeking information. google's your best friend.

Queries in productions AND history tables

I've got a large and growing database in SQL Server 7.0. I'd like to utilize a monthly stored procedure that will search various tables for records that are older than 3 months and copy this data out to corresponding history tables. Typically, most production queries are run only on the new data, but occasionally (like at year-end, for example), we will need to run some queries on data extending back 12 months -- that is, on data in the production tables AND in the history tables. Notwithstanding the fact that I've never done stored procedures*, I would like to know if it is possible to run a query that can search for data in both production and history tables at the same time. I know this sounds like a stupid question, but I read somewhere that doing this qould require some kind of Joining function that is complex...?

Thanks,
Whill96205

* Also, any good resources I could use for developing stored procedures?Well that would be a VIEW with a union

And here's a fundamental question...do you have an AD_DT column in all your tables

How big is the database?|||UNION queries can be a little bit tricky, but are not too difficult if both the tables you are pulling from have the same structure. Don't be intimidated.

Select [Column1], [Column2], [Column3]...from CurrentTable
UNION
Select [Column1], [Column2], [Column3]...from HistoryTable

Not so tough, eh?|||Thanks Brett and Blindman. I guess there's no big mystery for unioning then. :)

Well that would be a VIEW with a union

And here's a fundamental question...do you have an AD_DT column in all your tables

How big is the database?

Um... No. What is an AD_DT column?

--whill

P.S. btw, I'm using SQL Server 2000, not 7.0, if that makes any difference.|||Oh - almost forgot. :rolleyes: Regarding database size... The database currently has approximately 43000 records (transactions) in it, increasing by about 100 per day. My task is to incorporate a bunch of related data which the client is currently tracking in several huge spreadsheets. This may nearly double the amount of data, but not necessarily the number of transactions. So I am redesigning the db table structure to better accomdate and utilize the new data that will be imported from the spreadsheets.

Queries going to Suspend state with wait type ASYNC_NETWORK_IO

Recently we observed a problem. We are running stored procedure through our c# code. Three machines access the server and update or insert in the required tables in the server. If there is no data in the server, on installing first time our application usually our database is clean. then stored procedure works fine, it takes around 10 to 15 sec to execute. Next time if execute the time goes up to minutes like 15 mins. Next time it goes for hours around 4 hrs. Even to update 4 or 5 records it takes time. Initially we thought it was because of the size of the data and we tried to re tune on indexes, it did not solve. But now what we observe is even with less number of records in server also it wouldn't come of the execution for hours.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.

When we comment one of the query is working fine.
Is this something to do with the query I am not sure. If that is the case it should not work every time.

The query makes any sence or is there any way to write it in better way

'UPDATE [server].[dbo].[DocumentMetadata] SET DocumentInfoID = b.DocumentInfoID, [Name] = b.[Name], MetadataType = b.MetadataType,
[Value] = b.[Value], ValueType = b.ValueType
FROM [server].[dbo].[DocumentMetadata] a WITH (UPDLOCK)
INNER JOIN (SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MACHINENAME.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + ''') b
ON a.DocumentInfoID = b.DocumentInfoID AND a.[Name] = b.[Name]'

'INSERT INTO [server].[dbo].[DocumentMetadata]
(DocumentInfoID, [Name], MetadataType, [Value], ValueType)
SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MYCLI.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + '''
AND Ltrim(rtrim(c.DocumentInfoID))+ ltrim(rtrim(c.[Name])) NOT IN
(SELECT Ltrim(rtrim(DocumentInfoID))+ ltrim(rtrim([Name])) FROM [server].[dbo].[DocumentMetadata])'

We have been fighting it out for so many days.
Can anybody help

Thanks
knvdssr

ASYNC_NETWORK_IO means SQL server is waiting for network...

How may rows your select statement for INSERT it is returning?

You are using ltrim and rtrim functions against column which result a table scan and it can't use index...

Try to change the query or just comment the query and test it to make it is the issue.

|||It appears you are using a LINKED SERVER to access another server?

If this is the case, try looking at activity monitor on the source. I would suspect you are getting a deadlock or lock problem or something on the linked server.

The wait you are seeing indicates it is waiting on the linked server to finish the query and send records.

|||When you use 4-part name, all of the data from the remote server is pulled across before the join is formulated. If the remote table is large, you will have to wait for a long time for all the data to cross over. Try using openquery() to only pull/update the necessary remote data.

You can lookup some examples here:
http://groups.google.com/groups?q=sqlserver%20oj%20openquery%20update|||

We commented the query which I mentioned earlier, to see if that one is causing the problem. Then the whole Stored procedure finished executing in minutes.

We changed the the query with our using the NOT IN in that query.

Now first time it is taking only 10 minutes for more than 100,000 records.

But the second one is not coming out at all. This time the server is having lot of rows because is already updated once.

All that we see is the process in suspend state or comes out as timed out.

If I am getting a dead lock on the linked server is there any way to solve it?

Could any body help please

|||

Again, take a look at openquery/openrowset. Bringing the entire resultset back is not recommended when the remote table is large.

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Queries are slow when accessed from remote machine

Hi,

I have succesfully created a Stored Procedure which runs under 2 seconds locally.

However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.

My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.

Any Ideas why this would be happening?

Does the query return a lot of data? If so, it is very likely that network latency and bandwidth are the bottleneck, since the results have to be sent over the network back to the client.

How many rows are you returning? You can turn on Client Statistics in SSMS, and see how much data (in bytes) is being returned to the client (assuming you are calling the SP from SSMS on one machine, talking to a remote server).

|||

Thanks for the reply.

It is returning about 400k. But what is interesting is that, even that delay is not consistant ( from 5 sec to over 40sec, when i have run over 100 tests) The other machine is on the same lan with 100Mbps network card. I couldnt also see any significant rise in network utilization in both the machines

|||Did you ever resolve this? It really sounds like a network issue.|||Yes. It turns out that the SSRS was in a web farm scenario. I was checking only one server. duh!!!|||

Hi, me too have the same problem:

I made a migration from SQL2000 to SQL2005. Before migration, both IIS and SQL were on the same server and performance was good.

We decided to split application server (IIS) from db server (SQL). Now the wait time to display page is 5/10 more high.

We made migration of db (backup/restore, detach/attach), rebuilded indexes and compiled store procedure.

Both server are in the same LAN, switched to 1Gb

Some help?

Regards

vito

Quer runs well on its own but when put into stored procedure, it runs slowly

Hi,

Appreciate if any one can provide some pointers on this issue on a stored procedure that I have.

The required indexes (both clustered and non-clustered) have been created in the database tables.

When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and returns the results back within seconds.

However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

When the execution plan is examined for both, they are both different and it appears that when executing the stored procedure, it doesn't make use of the indexes, whereas in the first case of executing the same T-SQL statements directly, it does make use of the indexes.

We tried using the WITH RECOMPILE attribute to refresh the execution plan, but it doesn't seem to work for the stored procedure.

Wonder if anyone got any ideas or encounter anything similar?

Thanks,

Desmond

Can you post the SP and the DDL/Indexes?

|||

Hi Desmond T,

> When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and

> returns the results back within seconds.

> However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

Can you show us some light, please?

Look in BOL for auto-parameterization and also about the statistics used by the query optimizer. If you executed the "select" statement from QA or SSMS, using variables to simulate the parameters in the stored procedure, like in:

Code Snippet

use northwind

go

select orderid, customerid, orderdate

into dbo.t1

from dbo.orders

go

create index t1_orderdate_nu_nc_ix

on dbo.t1(orderdate)

go

dbcc show_statistics('dbo.t1', 't1_orderdate_nu_nc_ix') with STAT_HEADER, DENSITY_VECTOR, HISTOGRAM

go

create procedure dbo.usp_p1

@.orderdate datetime

as

set nocount on

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

declare @.orderdate datetime

set @.orderdate = '19960704'

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

exec dbo.usp_p1 '19960704'

go

drop table dbo.t1

go

drop procedure dbo.usp_p1

go

They seem to be the same, but the query optimizer uses different statistics to estimate cardinality and based on those statistics, it will choose an execution plan. In the sp case, the query procesor will use the values from the histogram, but for the query, it will use the "All density" value for that column and could be that those values yield different execution plan.

In my box, I got these values for "Estimated Number of Rows".

sp --> 1

query using variables --> 1.72917 (0.002083333 * 830) See "All density" value from the result of dbcc

The query optimizer chose to scan the table for the query using variables and an "index seek" operation followed by bookmark lookup for the sp.

One way of testing the query in QA / SSMS is using sp_executesql, parameterizing the statement.

Code Snippet

declare @.sql nvarchar(4000)

set @.sql = N'select orderid, customerid, orderdate from dbo.t1 where orderdate = @.orderdate'

exec sp_executesql @.sql, N'@.orderdate datetime', '19960704'

go

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

AMB

|||hi,

i had a similar problem before, can you post your proc?, i resolved my problem by tweaking the proc a bit.

/rh4m1ll3

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
> > How can I create stored procedure with data grouped by quarter?
> > I am converting database from Access to SQL and learn SQL in the
same
> > time.
> > In Access that is simple because there is function for that.
> >
> > I was looking in the different news groups and found few answers
like
> > truncating data, but cannot find it it T-SQL.
> >
> > Thank you
> >
> >

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:

> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
>
same[vbcol=seagreen]
like[vbcol=seagreen]

Quarter in the group by

How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank you
Hi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegr oups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:

> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
same[vbcol=seagreen]
like[vbcol=seagreen]

qualifying Stored Procedure with owner. Why is it important/

I've been reading some posts that indicate that it is best to invoke a
Stored Procedure with the owner name. Example
exec dbo.MyStoredProc
instead of
exec MyStoredProc
I never really understood why this makes a difference.
I would appreciate it if someone could shed some light on this
Thanks in advanceWell, what about if some user 'user1' have created a procedure which is
owned by 'user1'
If you do "Exec UserProc" you will get an error, you need to use "Exec
user1.UserProc" generally it is advisable to qualify owner name before
database objects.
Things are changed in SQL 2005 where you have to qualify schema name
instead of user name for database objects
chirag shah|||in my database all objects are owned by dbo. I have read that there is a
performance impact by not qualifying the owner of the SP (even in a case
like mine where all objects are owned by dbo)
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1142951587.510508.143150@.g10g2000cwb.googlegroups.com...
> Well, what about if some user 'user1' have created a procedure which is
> owned by 'user1'
> If you do "Exec UserProc" you will get an error, you need to use "Exec
> user1.UserProc" generally it is advisable to qualify owner name before
> database objects.
> Things are changed in SQL 2005 where you have to qualify schema name
> instead of user name for database objects
> chirag shah
>|||"TJT" <TJT@.nospam.com> wrote in message
news:u0kJDZPTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> in my database all objects are owned by dbo. I have read that there is a
> performance impact by not qualifying the owner of the SP (even in a case
> like mine where all objects are owned by dbo)
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;263889
David|||see
http://www.sqlservercentral.com/columnists/chedgate/worstpracticenotqualifyingobjectswiththeowner.asp
for a discussion of why objects should always be qualified.
"TJT" wrote:
> in my database all objects are owned by dbo. I have read that there is a
> performance impact by not qualifying the owner of the SP (even in a case
> like mine where all objects are owned by dbo)
>
> "c_shah" <shah.chirag@.netzero.net> wrote in message
> news:1142951587.510508.143150@.g10g2000cwb.googlegroups.com...
> > Well, what about if some user 'user1' have created a procedure which is
> > owned by 'user1'
> > If you do "Exec UserProc" you will get an error, you need to use "Exec
> > user1.UserProc" generally it is advisable to qualify owner name before
> > database objects.
> >
> > Things are changed in SQL 2005 where you have to qualify schema name
> > instead of user name for database objects
> >
> > chirag shah
> >
>
>

Qualification of system tables in queries

I found an "undocumented" stored procedure that returns the size of the
tempdb called sp_tempdbspace - this stored procedure queries two different
tables to compute the allocated size of tempdb as well as figuring out how
much of that size is being used.
My question is about the queries used in this stored procedure - the query
that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but the
query that accesses sysindexes qualifies the table as "tempdb..sysindexes".
I am trying to figure out why these two queries use different qualifications
for these system tables - I suspect that there is a reason for this
difference other than cosmetic or stylistic reasons. I have tried to do
research through web searches, but have not really found anything that looks
to be conclusive.
Any answers or pointers in resolving this would be appreciated.My bet is that it's just a different style (maybe two different people
worked on the query, or maybe the same person at different times). Since
the stored procedure is always called with dbo either implicitly or
explicitly, it doesn't make a whole lot of difference. Especially if it is
just a cosmetic issue, in an undocumented procedure, I wouldn't worry about
it... I would worry more about relying on this stored procedure
(undocumented stored procedures can be changed, or dropped from the product
altogether, without any notice).
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
Perhaps they were written by two different programmers. Best practice is to
fully qualify the name. And **NEVER** rely on any script written by MS or
shipped with MS to be a good example of best practices.|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons.
AFAIK, the best practice is to always owner-qualify objects. This can
reduce stored procedure recompilations and avoid ambiguity.
Although there could be a special case for system object access, the more
likely reason for the omission of 'dbo' here is a simple oversight.
Hope this helps.
Dan Guzman
SQL Server MVP
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>