Credentials blank out-default behaviour?

Hi we have projects that a number of people work on,

When someone opens a project that someone else has been working on all the credentials for the different data sources have disappeared. This seems to be a default behaviour of reporting services, is there a way to change this?

if so how?

Have you tried using shared data sources?|||These are shared datasources

Creating Test Data for a Table

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

u can use some existing table...transform and import that data...

or..there are a few data generator tools u can search and use them.... if u have visual studio 2005 for database developer, that has a cool data generation functionality too...

|||

Thanx, though I was looking for a T-SQL query equivalent solution

Maybe a while loop? I'm not so good at writing code in sql, any other suggestions please?

|||

JohDas wrote:

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

INSERT INTO
MyTable (id, Description)
(
SELECT
Id, Description
FROM
MyOtherTable
)|||

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

|||

ManiD wrote:

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

Nice, after seeing this I realised I mis understood the question |||Thanx a lot, this is the query I've been searching!!!|||

JohDas:

I try to avoid these WHILE loops as much as possible. I normally try to use a "numbers" table to generate my test data; this is normally much more efficient. One of the tools that I find helpful for generating repetitive data is the modulo "%" operator

-- --
-- Give a look to this link for a description of the use of a
-- "numbers" table:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
--
-- A table that can be used on a "test only" basis is the
-- "master.dbo.spt_values" table; but be forwarned: this type
-- of use is not supported! In fact, if you use this table with
-- SQL Server 2000 you will find that you get a different range
-- of values for the "number" column than you do with SQL Server
-- 2005.
--
-- Nonetheless, I still frequently use this table as a source of
-- values for test data. If the range of values from this table
-- is not sufficient to provide enough data, then I might
-- generate my data by cross joining the spt_values table with
-- itself.
-- --
select number,
'Description #' + convert (varchar(10), number%21) Description
from master.dbo.spt_values (nolock)
where name is null

-- Output:

-- number Description
-- -- --
-- 0 Description #0
-- 1 Description #1
-- 2 Description #2
-- ...
-- 20 Description #20
-- 21 Description #0
-- 22 Description #1
-- ...

|||

Thanx Mugambo, it's a more complex solution (to comprehend ;) ) but I think it'll be faster

Creating property get and set clauses based on a table or sp

Is there a way to automatically (wizard?) to create the get and set clauses of a class based on a SQL table or sp? I need to wrap a number of tables and sp into classes to use them in an ASP.net application and it's rather tedious to do type each one.

What I would like is something that I could point at a table and have it create a class and the get and set properties. I could save that as a class and use it in the program.
eg. Point to a table people.
Results would be
Class People
property FirstName()
get

set

...

Aim.You can create a strong typed data set.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcongeneratingstronglytypeddataset.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingannotationswithtypeddataset.asp

It will generate the class based on a xml schema, and you can easily build a schema by drag and dropping your table on the xml scheman designer.|||Thanks for the reply, I appreciate your time. However, I am not sure that it is what I want. Maybe I am missing the point.

I want to create a class based on a table. The class would have a local variable and a property get/set clause for each field in the table.
eg if the table were
FirstName
LastName
Middle Initial

I would end up with a class that looked something like

class Name
dim fname as string
dim lname as string
dim mi as string

Public Property FirstName() As String
Get
Return fname
End Get
Set(ByVal Value As String)
fname = Value
End Set
End Property

Public Property LastName() As String
Get
Return lname
End Get
Set(ByVal Value As String)
lname = Value
End Set
End Property
Public Property MiddleInitial() As String
Get
Return mi
End Get
Set(ByVal Value As String)
mi = Value
End Set
End Property

end class

If anyone knows how I can do this I would appreciate a hint or two.

Aim.|||You can do it yourself if you set your query analyzer output to text mode with space separators.

declare @.TABLE_NAME='Name'
print 'Class ' + @.TABLE_NAME + '
'
select 'dim _'+COLUMN_NAME+' as'
, CASE DATA_TYPE WHEN 'int' THEN 'Integer' ELSE 'string' END
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@.TABLE_NAME
select 'Public Property '+COLUMN_NAME+' As',
CASE DATA_TYPE WHEN 'int' THEN 'Integer' ELSE 'String' END, '
Get
Return _'+COLUMN_NAME+'
End Get
Set(ByVal Value As',
CASE DATA_TYPE WHEN 'int' THEN 'Integer' ELSE 'String' END,
')
_'+COLUMN_NAME+' = Value
End Set
End Property
'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@.TABLE_NAME
print 'End Class'

|||Thanks for that. It's a cute solution. Maybe I can package that and make my own wizard.

Cheers

Aim.

Creating procedures in PL/SQL

I want to create a procedure that takes a product number as an argument and displays the name of the product. This is what I have so far, but I am getting errors. I was wondering if someone could please help me out with this. Thanks.

PROCEDURE displayProd(prono PRODUCTS.PNO%TYPE, name PRODUCTS.PNAME%TYPE)

IS


BEGIN

SELECT PRODUCTS.PNAME
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.print_line('The name of the product is ' || name);

END displayProd;

/Hello,

which errors do you get ?

Here are some suggestion ...

1) define your in or out vars for better reading
2) define a field name as a placeholder for the result in your select
3) What is print_line ? Do you mean put_line ?

PROCEDURE displayProd(prono IN PRODUCTS.PNO%TYPE, name OUT PRODUCTS.PNAME%TYPE)

IS
BEGIN
SELECT PRODUCTS.PNAME
INTO name
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.put_line('The name of the product is ' || name);

END displayProd;

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||I get an error that says: invalid sql statement. i think i need a create or replace procedure. I tried that, and it said: Warning: Procedure created with compilation errors. So, I still don't know what the prob. is.|||Originally posted by izy
I get an error that says: invalid sql statement. i think i need a create or replace procedure. I tried that, and it said: Warning: Procedure created with compilation errors. So, I still don't know what the prob. is.

Does it have to be a procedure? I think a function would be more appropriate. You would pass in one argument (the product number) and return one argument (the associated product name).

However, if you need to write a procedure (i.e., a homework assignment) then the recommendations from the previous reply should work (i.e., including the INTO clause into your SQL statement).|||Hello,

of course the beginning is
CREATE OR REPLACE PROCEDURE ...

If you would like to see the errors use f.e. the AlligatorSQL at
http://www.alligatorsql.com/download/alligator.zip

or use the statement

SELECT e.*
FROM sys.error$ e,
sys.all_objects a
WHERE a.object_id = e.obj#
AND a.owner = 'SCOTT'
AND a.object_name = 'MYPROC'
ORDER BY e.sequence#

(Please change the owner SCOTT and the object_name MYPROC to your schema and procedure name)

Hope that helps ?

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||Sorry, I meant something else. I am told to write a procedure that displays the names of products for these orders that have not been shipped. When I run this, I get a 'Warning: Procedure created with compilation errors. I have no idea what the problem is. Am I declaring the null right?

CREATE OR REPLACE PROCEDURE ItemsShipped(myname IN PRODUCTS.PNAME%TYPE)

IS

BEGIN
SELECT PRODUCTS.PNAME
INTO myname
FROM PRODUCTS, ORDERS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO AND
ORDERS.SHIPPED IS NULL;

END ItemsShipped;

/

Originally posted by izy
I want to create a procedure that takes a product number as an argument and displays the name of the product. This is what I have so far, but I am getting errors. I was wondering if someone could please help me out with this. Thanks.

PROCEDURE displayProd(prono PRODUCTS.PNO%TYPE, name PRODUCTS.PNAME%TYPE)

IS


BEGIN

SELECT PRODUCTS.PNAME
FROM PRODUCTS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO;

dbms_output.print_line('The name of the product is ' || name);

END displayProd;

/|||Hello,

the only thing I can see is, that you have declared the myname parameter as an IN and you shift the result into this var.

Please declare it as IN OUT ...

Again ...

If you have error use a GUI that displays you the errors or select the error table like I mentioned before.

Otherwise you are developing in the dark

Best regards
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com|||Originally posted by izy
Sorry, I meant something else. I am told to write a procedure that displays the names of products for these orders that have not been shipped. When I run this, I get a 'Warning: Procedure created with compilation errors. I have no idea what the problem is. Am I declaring the null right?

CREATE OR REPLACE PROCEDURE ItemsShipped(myname IN PRODUCTS.PNAME%TYPE)

IS

BEGIN
SELECT PRODUCTS.PNAME
INTO myname
FROM PRODUCTS, ORDERS, ODETAILS
WHERE PRODUCTS.PNO = ODETAILS.PNO AND ORDERS.ONO = ODETAILS.ONO AND
ORDERS.SHIPPED IS NULL;

END ItemsShipped;

/
In SQL Plus, just type SHOW ERROR after receiving the warning. In this case, you should see this:

SQL> show error
Errors for PROCEDURE ITEMSSHIPPED:

LINE/COL ERROR
--- --------------------
4/1 PL/SQL: SQL Statement ignored
5/6 PLS-00403: expression 'MYNAME' cannot be used as an INTO-target
of a SELECT/FETCH statement

This is bacause IN parameters are read-only. Change IN to OUT or IN OUT.

Creating primary key on a view

i'm connecting a view to microsoft mappoint using a .UDL. when i link the
data i need to select the primary key so i choose the customer number, but
there are a few shops that have to shops but one customer number so neither
go in to the map because they are duplicates. so i was wondering if i could
create a primary key in the view that is sort of like the autonumber in
access. so each shop would be given a number when the view is called and tha
t
could be my primary key. the number wouldn't even have to match up with the
same number every time i would just need it to get every shop into mappoint.
if anyone has any suggestions i would really appreciate it. thank you.
jb
Message posted via http://www.webservertalk.comjbennett via webservertalk.com wrote:
> i'm connecting a view to microsoft mappoint using a .UDL. when i link the
> data i need to select the primary key so i choose the customer number, but
> there are a few shops that have to shops but one customer number so neithe
r
> go in to the map because they are duplicates. so i was wondering if i coul
d
> create a primary key in the view that is sort of like the autonumber in
> access. so each shop would be given a number when the view is called and t
hat
> could be my primary key. the number wouldn't even have to match up with th
e
> same number every time i would just need it to get every shop into mappoin
t.
> if anyone has any suggestions i would really appreciate it. thank you.
> jb
> --
> Message posted via http://www.webservertalk.com
Adding an "auto-number" (the similar feature is called IDENTITY in SQL
Server) doesn't seem to make much sense for the situation you
described. For future reference, it's a reliable rule of thumb that
doing anything "like Access" in SQL Server is often going to be a big
mistake ;-)
Instead, create a table called Shops. Shops obviously isn't the same as
Customers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>
>Instead, create a table called Shops. Shops obviously isn't the same as
>Customers.
Thank you for your reply, i'll keep in mind not to try stuff in SQL that i
can do in access:) we already have a customer table. the problem is that we
ship ups and some of our shops have po boxes for address so we need a ship t
o
address which is stored in another table. so i created a view that pulls in
the customer info with the shipping address. after i did that i came across
a
problem that some shops had 2 stores under one customer number creating a
duplicate primary key, because in mappoint i had to use custno as the primar
y
key. we have shops added wly and inactivated wly so i would have to
update a table all the time, that is why i used a view. if there is a way to
create a table that automatically updates that would be great. thanks again
for all your help.
jb
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Creating Mathematical Formulas and Calculations

I've created a sql statement that retrieves number data from various table joins. The number data is then grouped according to various categories. What I need to do is to calculate the average of all the number data in a particular group. How do i go about this? Once calculated, the average needs to be displayed under the data.

For example, the report will list say five numbers (some sets may have more, it depends on how much data is returned based on the query), then under the five numbers, the average is given

2

4

3

6

0

Average: 5

What technique is best? Do I have to calculate the numbers in sql or do I need to configure the report to calculate the average? If so, how? Can someone show me step by step how to do averages for a set of data in the reporting services?

I am completely new to doing reports, I'm just a hobbyist, and I've only used databases to retrieve basic data, but not make manipulations for reports.

Any help will be appreciated.

You can use the "Avg" function in reporting services to do this calculation. Average returns the average of the numbers.

creating jobs in SQL Server 2005

Hello

I am using SQL Server 2005
I want to delete the data from my table after specific number of days.

This should happen automatically.

After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.

If anyone can give an example for this it will be great.

Thanks in advance.

Hi,
As the title of your post suggests you've already identified that you need a job running on the server to facilitate this.

SQL Server Agent will allow jobs to be scheduled and run - read up about this feature of SQL Server if unsure.

As for how to add a job...

In SQL Server Management Studio connect to the server.

Creating insert scripts from tables

Hi,

A seemingly simple question: How do I create an Insert script from the data of a selected number of tables in a given database?

(The Insert script has to be part of a deployment phase)

Esben

check whether this helps u

http://vyaskn.tripod.com/code/generate_inserts.txt

http://vyaskn.tripod.com/code.htm#inserts

Madhu

|||

http://vyaskn.tripod.com/code.htm#inserts

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Creating FilePath for a BULK INSERT SP

Hi

I am creating an SP that will bulk insert data, the person calling the SP just needs to provide the file path and a number for the file and then the idea is i append this together to get the file to insert.

I have the SQL below which will not go past the BULK INSERT it is to do with my FilePath can anyone explain how I should be typing this?

Thanks very much

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[Get_DataFromFiles]

(

@.FileNumber VarCHAR(255),

@.FileID INT

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

--

-- 1. Insert Tasks Table Data and obtain the new ID

--

BULK INSERT Tasks FROM @.FilePath + 'FileNumber' + @.FileID WITH (FIELDTERMINATOR = '<$%^&>')

declare @.sql varchar(8000)
select @.sql = 'BULK INSERT Tasks FROM ' + @.FilePath + 'FileNumber' + @.FileID + ' WITH (FIELDTERMINATOR = ''<$%^&>'')'

exec (@.sql)|||

No but I forgot to show the backslash in the code i posted should read

select @.sql = 'BULK INSERT Tasks FROM ' + @.FilePath + '\FileNumber' + @.FileID + ' WITH (FIELDTERMINATOR = ''<$%^&>'')'

I still get an error though which is

Incorrect syntax near '@.FilePath'

|||Thanks for your help didnt realise I had to build the statement then execute it rather than just build as needed. Much appreciated.

Creating Dimensions

I have a cube with a dimension 'Destination', that has the follown herarchy.

Country -> State -> City.

As measure I have Number of transactions.

When I browse the cube I can get the total transactions if I expand all the levels, but If I just want o have number of transactions by state or country the cube is no creating totals by those levels. Even in the cube when I expand all levels (including city), there are not totals by state and country. What I am missing?

You can email me to jzuluaga@.msn.com or jzuluaga@.quisqueyana.com

Thanks

Juan,

What are you using to browse the cube?

Steve

|||

Check the calculations of the cube and make sure that it contains the CALCULATE command. This command is always included by default, but if it is deleted accidentally, then you will only get leaf level data as this command is what instructs the server to aggregate data up from the lowest levels up to the higher levels.

creating data for a histogram.

I have a table, TableA with amongst other fields, a field for Qty.
Qty can range from 0 to 100.
How do I count the number of rows with a qty between 1 and 10, 11 and
20, 21 and 30, and so on using one SQL statement?

Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have a table, TableA with amongst other fields, a field for Qty.
> Qty can range from 0 to 100.
> How do I count the number of rows with a qty between 1 and 10, 11 and
> 20, 21 and 30, and so on using one SQL statement?

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That seems to just count the number of times each qty appears, just
like

SELECT qty AS Expr1, COUNT(*) AS Expr2
FROM [Temp]
GROUP BY qty

How should I change it so that it counts the the number of qtys between
each range of 10?

Regards,
Ciarn|||Hi Erland Sommarskog ,

You Always give helpfull and informative answers.
I changed the query a bit to show the LowRange as well HiRange
SELECT LowRange,HiRange,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
but I am facing a problem can You guide me on this

This query {select q=qty+10 from sales order by q} works but
{select q=qty+10 from sales group by q} does not work .SQL Server2000
is not recognising Aliased Columns in second case .
---
With regards
Jatinder Singh (System Analyst )|||(chudson007@.hotmail.com) writes:
> That seems to just count the number of times each qty appears, just
> like
> SELECT qty AS Expr1, COUNT(*) AS Expr2
> FROM [Temp]
> GROUP BY qty
>
> How should I change it so that it counts the the number of qtys between
> each range of 10?

The query I posted was:

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

I would expect to give the desired result, assuming that qty is integer.
If qty is float or decimal, it will indeed just be a roundabout way to
count single qtys.

I will have to admit that I did not test my query, but there is standard
recommendation that posting asking for help with queries should include:

o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired output given the sample data.

This makes it very easy for me or anyone else who anser to cut and paste
into Query Analyzer and test whatever we post.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> You Always give helpfull and informative answers.
> I changed the query a bit to show the LowRange as well HiRange
> SELECT LowRange,HiRange,COUNT(*)
> FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
> ,HiRange=((qty - 1) / 10) * 10 + 10
> FROM sales) AS ds
> GROUP BY lowRange ,HiRange
> but I am facing a problem can You guide me on this
> This query {select q=qty+10 from sales order by q} works but
> {select q=qty+10 from sales group by q} does not work .SQL Server2000
> is not recognising Aliased Columns in second case .

Correct. I believe that Access does this, but that's not in alignment with
the SQL standards.

Instead, the technique to use is a derived table as a above.

--
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 Erland,
I am facing another problem of displaying a summarised data along with
the detail data
i.e

Item Qty

Item1 10
Item1 10
Item1 20
40 ( Sum for Item1)
and so on .........

I wish to have a single query which runs on all RDBMS . Is it possible
?

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type

This Query works but it would work on MS SQLServer .

---------------
With regards
Jatinder Singh (System Analyst )|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I am facing another problem of displaying a summarised data along with
> the detail data
> i.e
> Item Qty
> Item1 10
> Item1 10
> Item1 20
> 40 ( Sum for Item1)
> and so on .........
> I wish to have a single query which runs on all RDBMS . Is it possible
> ?
> USE pubs
> SELECT type, price, advance
> FROM titles
> ORDER BY type
> COMPUTE SUM(price), SUM(advance) BY type
> This Query works but it would work on MS SQLServer .

Here is a query which I believe should be fairly portable. (But since
I only work with SQL Server, I can make no warranties):

SELECT type, x = '', price, advance
FROM titles
UNION
SELECT type, 'Total', SUM(price), SUM(advance)
FROM titles
GROUP BY type
ORDER BY type, x

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> > I am facing another problem of displaying a summarised data along
with
> > the detail data
> > i.e
> > Item Qty
> > Item1 10
> > Item1 10
> > Item1 20
> > 40 ( Sum for Item1)
> > and so on .........
> > I wish to have a single query which runs on all RDBMS . Is it
possible
> > ?
> > USE pubs
> > SELECT type, price, advance
> > FROM titles
> > ORDER BY type
> > COMPUTE SUM(price), SUM(advance) BY type
> > This Query works but it would work on MS SQLServer .
> Here is a query which I believe should be fairly portable. (But since
> I only work with SQL Server, I can make no warranties):
> SELECT type, x = '', price, advance
> FROM titles
> UNION
> SELECT type, 'Total', SUM(price), SUM(advance)
> FROM titles
> GROUP BY type
> ORDER BY type, x
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Hi Erland,
Thanks ,I think it will work on any RDBMS . Your analysis ablity is
really something. I cannot describe it in words.

Thanks Again
With warm regards
Jatinder Singh (System Analyst)|||Hi Erland,

Can we similarly replace CUBE operator in SQL Server by using simple
queries that will run on any RDBMS?

With warm regards
Jatinder Singh (System Analyst)|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Can we similarly replace CUBE operator in SQL Server by using simple
> queries that will run on any RDBMS?

I don't use CUBE very often, so I may miss some fine detail. But the
two queries below returns the same result:

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id WITH CUBE
ORDER BY type, pub_id

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id
UNION
SELECT type, NULL, SUM(price), SUM(advance)
FROM titles
GROUP BY type
UNION
SELECT NULL, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY pub_id
UNION
SELECT NULL, NULL, SUM(price), SUM(advance)
FROM titles
ORDER BY type, pub_id

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland
I am bit confused by the output produced by the Query Analyzer and
finding it bit difficult to decide which one of the following query is
faster.

In query one I am using Correlated subquery Approach and it consumes
78% of batch time when run with 2nd query but time of mere 20
micrseconds

In query two I am using functions (these functions berely takes
PolicyNumber and Endrosment No to give output and does the same query )
Approach and it consumes 22% of batch time when run with 1st query but
time of 400 micrseconds

Query 1:-
select RowId,PolicyNumber,EndoNumber,status,SF,case when SubQ is Null
then 'No' else 'Yes' end as Lock,RCount from
(
SELECT RowId,PolicyNumber,EndoNumber,status,SF,
(select status from InProcessData052005MstM WHERE status in ('pen') and
IP.PolicyNumber=PolicyNumber and IP.EndoNumber=EndoNumber) as subQ,
(select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and IP.PolicyNumber=PolicyNumber and
IP.EndoNumber=EndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData052005MstM IP
WHERE status in ('cur','pen')
) X
ORDER BY PolicyNumber

select getdate()

SELECT
RowId,PolicyNumber,EndoNumber,status,SF,dbo.fnTryG etPolicyCount(PolicyNumber,EndoNumber)
as RCount,
dbo.fnTryGetPolicyLock(PolicyNumber,EndoNumber) as Lock
--(select status from InProcessData052005MstM WHERE status in ('pen')
and IP.PolicyNumber=PolicyNumber and IP.EndoNumber=EndoNumber) as subQ,
--(select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and IP.PolicyNumber=PolicyNumber and
IP.EndoNumber=EndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData052005MstM
WHERE status in ('cur','pen')
order by PolicyNumber
select getdate()

create function fnTryGetPolicyCount(@.p varchar(16),@.e varchar(3))
returns int
as
begin
return (select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and PolicyNumber=@.p and EndoNumber=@.e)
end

create function fnTryGetPolicyLock(@.p varchar(16),@.e varchar(3))
returns varchar(3)
as
begin
declare @.Lock varchar(3)
select @.Lock=status from InProcessData052005MstM WHERE status in
('pen') and PolicyNumber=@.p and EndoNumber=@.E
if @.Lock is null
set @.Lock='No'
else
set @.Lock='Yes'

return (@.Lock)
--(select status from InProcessData052005MstM WHERE status in ('pen')
and PolicyNumber=@.p and EndoNumber=@.E)
end

------------------
Jatinder|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I am bit confused by the output produced by the Query Analyzer and
> finding it bit difficult to decide which one of the following query is
> faster.
> In query one I am using Correlated subquery Approach and it consumes
> 78% of batch time when run with 2nd query but time of mere 20
> micrseconds
>
> In query two I am using functions (these functions berely takes
> PolicyNumber and Endrosment No to give output and does the same query )
> Approach and it consumes 22% of batch time when run with 1st query but
> time of 400 micrseconds

The difference in estimate may be because the function is not considered.

Anyway, the one way to benchmark queries is this:

DECLARE @.d datetime, @.tookms int
SELECT @.d = getdate()
-- run query here
SELECT @.tookms = datediff(ms, @.d, getdate())
PRINT 'This query took ' + ltrim(str(@.tookms) + ' ms to run.'

You need to consider the effect of the cache. If the two queries operates
on the same data, the easiest may be to run the queries several times
and discard the first result. You can also run DBCC DROPCLEANBUFFERS to
clean the cache, but that affects the entire server.

Also, beware that datetime has a resolution of 3.33 ms. For the
measurement method above, I have never seen any value between 0 and
13 ms. I consider values below 50 ms to be too inaccurate to be
taken as a significant. 400 ms is certainly significant.

Note: above you talk "microseconds". I assume this is a typo for
"milliseconds".

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

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

Thanks for your answer and time.
Sorry for the typo error.
I can be wrong in my assumption buut isn't it that the two queries are
working in simliar fashion. Both are taking a value(or two) passing it
to inner corelated query (funtion) and getting the result.

Waiting for your reply.

Jatinder|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Thanks for your answer and time.
> Sorry for the typo error.
> I can be wrong in my assumption buut isn't it that the two queries are
> working in simliar fashion. Both are taking a value(or two) passing it
> to inner corelated query (funtion) and getting the result.

Just because two queries logically are the same, that does not mean that
performance is. There is quite some overhead with calls to saclar user-
defined functions. Also, when you stuff a subquery into a scalar function,
all the optimizer sees is a call, it does not see the contents of rhe
UDF, so it cannot take any shortcuts.

Table-valued functions are different. Particularly inline functions. Table-
valued inline functions are really just macros, and the query text is
pasted into the query, so the optimizer can rearrange as it likes.

As for the estimates you saw in Query Analyzer, they are just estimates, and
I would not pay too much attention on them.

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

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

Thanks Again for your time .

Explaination is good. So may I consider that the UDF will always be
little slower because the Query Optimizer can never arrange it for
optimization. but using function make query more manageable

Please correct me if my assumption is wrong.

I have yet another question (query ) .
I have two tables
One empmast which store emp current designation
Other promotion table which store the promotions of an employee during
his service.It stores the information of employee designation promotion
date.

Empmast(empid int primary key,desigid int references desigmast
,.........)
PromotionDtls(empid int references Empmast,promotatedTo int references
desigmast, promotedFrom int references Desigmast,DateOfPromotion
smalldatetime)

EmpMast
empid desigid (current designation of employee)
1 3 ........................
2 1 ..................

PromotionDtls
empid promotedTo PromotedFrom effectiveDate
1 2 1 1-jan-2003
1 3 2 2-dec-2003
..........

Now I wish to use the designation Id in a query
such that if the employee data exists in Promotion Table the promotedTo
should be picked according to Effectivedate
otherwise the Empmast designation
e.g If I say desigId of employee having empid 1 on date 2-jun-2003 then
it should be desigId 2
I did this using isnull but I wish to find a better method.

select isnull( ( select top 1 promotedTo from promotionDtls where
empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
Jatinder Singh|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I have yet another question (query ) .

Sorry for not coming back to you earlier, but I had limited time for
some days to read the posts in the newsgroups, so I deferred the
difficult stuff until later.

A general advice is that it's better to post a new problem to a new
thread. Then other people might be more keen to answer it.

> I have two tables
> One empmast which store emp current designation
> Other promotion table which store the promotions of an employee during
> his service.It stores the information of employee designation promotion
> date.
> ...

I've now looked at the problem again, but I still could not really
understand what you are looking for. Since I don't like guessing, I
answer with the standard suggestion that you include:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

The first two makes it simple to copy and paste into Query Analyzer,
and the last makes it possible to actually produce a tested query, and
also helps to clarify what you are looking for.

It's not only that I'm lazy - neither do I like guessing.

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

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