is it possible to recieve result of SQL stored procedure to web page?

I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.

for (int i=0; i<dataset.Tables.Count; i++) {
 Response.Write(dataset.Tables[i].TableName);
 Response.Write("\n");
 // loop rows
 for (int j=0; j<dataset.Tables[i].Rows.Count; j++) {
 // loop columns
 for (int k=0; k<dataset.Tables[i].Columns.Count; k++) {
 Response.Write(dataset.Tables[i].Rows[j][k].ToString());
 if (k < dataset.Tables[i].Columns.Count - 1) {
 Response.Write(", ");
 }
 }
 Response.Write("\n");
 }
 Response.Write("\n");
}

this isn't exactly what you want, but idea.|||If the procedure returns a fixed number of result sets, you can return the results a datareader and then bind each result set to something like a datagrid or a repeater using .nextresult between bindings. A datareader can hold more than one result set. It can be pretty handy.

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

Is it possible to link two datasets together ?

Saludos !
given two datasets:
---
dataset1: select * from pubs.dbo.titles
dataset2: select * from pubs.dbo.sales
---
My question now is, how can i establish a relation between this two datasets
? And where (data register, layout register) ?
Or only with Subreports ?
thanks for any suggestion, hint...
JackHi,
It isn't possiple to link two datasets together or use more than one dataset
on a control.
But what about one dataset which unions both sql queries? And then you can
use a filter on a control to get only data from a single table.
e.g. the dataset:
select *, 'Titles' as TableName from pubs.dbo.titles
UNION
select *, 'Sales' as TableName from pubs.dbo.sales
and as filter to get titles data:
=Fields!TableName.Value='Titles'
Be sure that both queries you use to union have the same column names, so
don't use the *.
--
| Jan Pieter Posthuma
--
"Jack" wrote:
> Saludos !
> given two datasets:
> ---
> dataset1: select * from pubs.dbo.titles
> dataset2: select * from pubs.dbo.sales
> ---
> My question now is, how can i establish a relation between this two datasets
> ? And where (data register, layout register) ?
> Or only with Subreports ?
> thanks for any suggestion, hint...
> Jack
>
>|||Thanks Jan, but that is not exactly touching my issue: "titles" and "sales"
are different tables. In my opinion a union is isn't a proper way to do
this. Anyway...
My central idea behind my question is: If I could solve this problem, I
would be more flexible in using data for several reports. For example, I 've
got one special view for clients (supposing there are more tables to build
this view) , a second special view for sales and many more views for other
matters. In every report where I now have to use client-data, I can take the
same view. Otherwise I have to change every select statement for every
special report if anything changes...
>> Its a thing of strategy, do you see ?
jack
"Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> schrieb im Newsbeitrag
news:BF29CB19-BD2C-4218-A9AE-F970A4D6E011@.microsoft.com...
> Hi,
> It isn't possiple to link two datasets together or use more than one
> dataset
> on a control.
> But what about one dataset which unions both sql queries? And then you can
> use a filter on a control to get only data from a single table.
> e.g. the dataset:
> select *, 'Titles' as TableName from pubs.dbo.titles
> UNION
> select *, 'Sales' as TableName from pubs.dbo.sales
> and as filter to get titles data:
> =Fields!TableName.Value='Titles'
> Be sure that both queries you use to union have the same column names, so
> don't use the *.
> --
> | Jan Pieter Posthuma
> --
>
> "Jack" wrote:
>> Saludos !
>> given two datasets:
>> ---
>> dataset1: select * from pubs.dbo.titles
>> dataset2: select * from pubs.dbo.sales
>> ---
>> My question now is, how can i establish a relation between this two
>> datasets
>> ? And where (data register, layout register) ?
>> Or only with Subreports ?
>> thanks for any suggestion, hint...
>> Jack
>>|||> My central idea behind my question is: If I could solve this problem, I
> would be more flexible in using data for several reports. For example, I 've
> got one special view for clients (supposing there are more tables to build
> this view) , a second special view for sales and many more views for other
> matters. In every report where I now have to use client-data, I can take the
> same view. Otherwise I have to change every select statement for every
> special report if anything changes...
Not sure this answers your question but...
Use a stored procedure to provide your data, then this stored procedure
can be re-used for all reports. And only has to be edited once for all
reports.
sarah|||You're right. thats the way we all doing it for years. But now for several
reasons we would like to use views instead of procs... so our customers are
able to easily create their own reports and links between their required
tables. without any knowledge of the underlying structure.
Jack
<sarah.abnett@.jarvis-uk.com> schrieb im Newsbeitrag
news:1119879460.100655.234790@.g44g2000cwa.googlegroups.com...
>
>> My central idea behind my question is: If I could solve this problem, I
>> would be more flexible in using data for several reports. For example, I
>> 've
>> got one special view for clients (supposing there are more tables to
>> build
>> this view) , a second special view for sales and many more views for
>> other
>> matters. In every report where I now have to use client-data, I can take
>> the
>> same view. Otherwise I have to change every select statement for every
>> special report if anything changes...
> Not sure this answers your question but...
> Use a stored procedure to provide your data, then this stored procedure
> can be re-used for all reports. And only has to be edited once for all
> reports.
> sarah
>|||Hi,
First sorry for my late reply, but I must say your last reply explained your
needs perfectly.
What you can try is to create a datasource with integrated authentication
turned on. This would force SRS to use the users credentials to log in to the
database. In the database views (to which the users only have read rights)
you can create a user specified select statement by filtering with the SQL
function SYSTEM_USER (SUSER_SNAME).
Unfortunaly you can only use select statements in views. So if your database
structure can't cope with this kind of behaviour, you can only fall back to
stored procedures.
Hope this would help you further. Feel free to ask for more information.
--
| Jan Pieter Posthuma
--
"Jack" wrote:
> You're right. thats the way we all doing it for years. But now for several
> reasons we would like to use views instead of procs... so our customers are
> able to easily create their own reports and links between their required
> tables. without any knowledge of the underlying structure.
> Jack
> <sarah.abnett@.jarvis-uk.com> schrieb im Newsbeitrag
> news:1119879460.100655.234790@.g44g2000cwa.googlegroups.com...
> >
> >
> >> My central idea behind my question is: If I could solve this problem, I
> >> would be more flexible in using data for several reports. For example, I
> >> 've
> >> got one special view for clients (supposing there are more tables to
> >> build
> >> this view) , a second special view for sales and many more views for
> >> other
> >> matters. In every report where I now have to use client-data, I can take
> >> the
> >> same view. Otherwise I have to change every select statement for every
> >> special report if anything changes...
> >
> > Not sure this answers your question but...
> > Use a stored procedure to provide your data, then this stored procedure
> > can be re-used for all reports. And only has to be edited once for all
> > reports.
> > sarah
> >
>
>

IS it possible to insert a blank line in my report

Hello,

I my dataset is like following

sql:

select quarter, sum(amount) as amount from table1 group by quarter

then I get the dataset

field: quarter,amount

date: quarter1,100

quarter2,500

Is it possible to get my report from the dataset above.

my report:

quarter,amount

quarter1,100

quarter2,500

quarter3,0

quarter4,0

thanks!

This is possible if you have a time (or quarter) table in your database:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q

LEFT JOIN table1 t1

ON q.quarter = t1.quarter

|||

thank you for reply

I can't get the tabe quarters ...

|||

Use this SQL:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q LEFT JOIN

(SELECT 'quarter1' AS quarter UNION SELECT 'quarter2' AS quarter UNION SELECT 'quarter3' AS quarter UNION SELECT 'quarter4' AS quarter) AS qrt

ON quarters.quarter = qrt.quarter

This will work.

Please mark the post as answer.

Shyam

|||

In order to more easily manage date groupings (weeks, months, quarters, years, etc.) it is a VERY good idea to have a Calendar table in your database. Having a Calendar table makes tasks such as this one very simple.

Here is additional information about creating and using a Calendar table.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

is it possible to have variant condition clause in procedure?

i want to use OLEDB to build a COM for my app

in the case, i want to execute a select statement which the where-clause is variant.

ex,

select * from db1 where code='abc'

select * from db1 where name='mike'

As it's very difficult to change sql-command in oledb, i want to build a procedure like this,

create procedure viewDB
@.filter CHAR(20)

as

select * from db1 where @.filter

go

but failed!

i tried EXEC(select), but i cant get the variants when building a oledb consumer

No, you cannot pass the whole WHERE clause as a parameter. You could pass only values. In your case if this is predefined set of the types of conditions, then you could create additional parameter in your SP and pass type of the condition there. then, inside of SP, first check type of the query using IF statement and based on it call specific SQL

IF @.MyTYPE='A'

select * from db1 where code=@.filter

ELSE

select * from db1 where name=@.filter

|||

oh, yeah! why i didnt come up with this smart idea, haha

but i found a better solution, bind the columns manually!

|||What is that? Is it constracting SQL statement dinamically inside of SP?|||

no, it's not that!

is easy!

build a SP like this:

create procedure test
@.p char(40)
as
exec( 'select * from testdb where ' + @.p )
go

when executing this sp in sql-server, we can get the columns correctly! but, when using the ole-wizard to build a oledb consumer , the columns disappear. the fact is, the columns lay there steadily, it's ole-wizard didn't bind the columns for us, haha

so let's DIY

|||

But this is worst way to do. It is a pure SQL injection. If I pass next string in your parameter then it will be executed with the different result

Assuming I am passing next value in a parameter

1=1; SHUTDOWN --

Then it will execute your SELECT and then it will shutdown server completely. I could execute DELETE statement or something else. This is how hakers could get control of your server

|||

oh, thanks for telling me that!

but, what if i remove the string after the semi-colon?

my plan is to create a procedure, and use a ATL oledb consumer to access it, if i dont expose the SP name, i think the hackers wont hack me this way

|||They do not need to know your SP name. All the nee to do is to pass value like that to the parameter from your application and job is done. For example, if your screen accepts input for the parameter from outside then screen will accept this value and code will be executed. Another drawback of the dynamic SQL is that it is slower. It means your SP will be recompiled each time when you call it and new execution plan will be prepared.|||

really thanks this piece of infomation!

so, 'select * from table where col=@.p' is safe right?

ok, i will try to re-code my SP

|||

i fond it is almost impossible to code my SP like you suggestted, because my condition clause is so complicate.

i figured out this new plan, and i want to get some advice from you, thx

create procedure proc
@.p1 varchar(10),
@.p2 varchar(10),
...
@.pn varchar(10)

as

if @.p1 is not null
begin
select * into retTable from table where col1= @.p1
--select * into tmpTable from table where col1= @.p1
end
if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2=@.p1
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

if @.p3 is not null
begin
if object_id('tmpTable') is not null
drop table tmpTable
select * into tmpTable from retTable where col3=@.p3
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

...
...

go

BUT, i wonder if it's efficiency !

could you give me some suggestion or a better solution, thx

|||

I see that code for the second and third IF statements is the same. Does it mean that it suppose to be something like below? It is not an actual code that could work, but shows an idea. If idea is correct then you could pass array of values as one parameter into stored procedure using XML string and then use it inside of the IN clause. If this is what you need, then I will post a code that shows how to pass arrfay of values into SP and how to use it there

if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2 IN (@.p1, @.p3)
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

|||

well, it should be 'col3' in the 3rd IF statement

so, u mean my idea wont work actually right?

what do u mean pass a parameter using XML string? how can i parse it in the SP?

|||

Here is my article about how touse XML to pass array of values into SP.

http://support.microsoft.com/kb/555266/en-us

I will try to think about ideas how to do this in your case and will let you know

|||

now i have a problem about injection attack!

could you tell me if the following code safe.

procedure sp_a
@.p CHAR(40)
as
select * from tab1 where col1=@.p
go

|||Yes, it is safe if you do not do anything else inside if this SP. Just small suggestion. If you can, select just the fields you need and avoid using *. It will impove performance

Is it possible to have more than one table footer rows ?

Well, we can add another table footer row by right-clicking on the footer row of the table and select, Inser Row Above, or, Insert Row Below.

But, I want to set different values to same properties of rows. Such as;

Row3.RepeatOnNewPage = False

Row4.RepeatOnNewPage = True

What do you say ?

-SMang the Frustrated Developer

SMang,

You can't hide your table rows independently with the RepeatOnNewPage, but you can set the rows "hidden" property to true or false. The catch here is that you will need to tie it to a toggle item.

Ham

sql

Is it possible to get the max length of a TEXT field?

I have a text field and want to know if any of the text exceeds 10,000 characters
I can do a select max(len(rtrim(convert(varchar(8000)))) on the field but I'm not able to do for more than 8000 and you can't manipulate TEXT datay type.
Any ideas?
Thanks!SELECT DATALENGTH(Col1) FROM myTable99

is it possible to do this?

Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select Cusip,Fundnofeeclass,FundNamewfeeclass,FundDescription FROM [Sheet1$] Select Cusip, FundNoFeeClass,InvestmentName,InvestmentPercentage FROM [Sheet2$]", connection)

Regards,

Karen

Hi Karen,

Yes this is possible! Altough you have to seperate the query statements with a; (a semicollon with a dot or how do you call it? )

Then when you assign the command to an sqlDataReader, you can first touch your 1st resultset, and by calling SqlDataReader.NextResult, you can touch the next resultset and so on.

To view an example of this kind of code you should look here:http://msdn2.microsoft.com/en-us/library/haa3afyz(VS.80).aspx (Using the SQL DataReader).
Scroll down a bit and you will see the examples with the nextresult.

Good luck!
Wim

Is it possible to custom format the parameter dropdownlist?

I have a reporting services 2000 report that has only one string report
parameter with a prompt 'Select a Project'. The project list contains
project names that are so long that they take up about 95% of the page
width and the View report button becomes a partially visible button
with a horizontal scroll bar to scroll further to the right to view the
entire page.
My users hate to have to scroll to the right. They are requesting a
fixed width dropdownlist with fully visible 'View report' button.
We want to make our DDL pretty narrow so that it only shows the first n
characters of the project name string when the DDL is not dropped down.
When the user
drops the list down we want to resize it wide enough to show the entire
string.
We could also assign a tool tip to the DDL if we could supply a unique
tip
to each row in the DDL?
Any ideas? Does SSRS 2005 let the designer specify the parameter
control width?
Many thanks in advance.I've found that the parameter panel is not at all configurable and have
been creating a front-end web page for each report giving me total
control over presentation. Then I hide the parameter panel via a
parameter on the URL. It's my understanding RS 2005 has more control
over the parameter panel.|||Many thanks, Kent.

Is it possible to create the columns of a #Temp table base on a query result?

I’ve been trying without success something like this…

CREATE TABLE #table

(

(SELECT MAX(Column) FROM Table) varchar(50)

)

I'm working with SQL 2005; thank you for any help in advance.

Not quite like that.

You can SELECT ... INTO.

In the process, you need to provide a column Name for any computed or derived columns, and you can change the datatype.

Something like this:

Code Snippet

USE Northwind
GO

SELECT cast( max( EmployeeID ) AS decimal(6,2)) AS MaxEmp
INTO #MyTable
FROM Employees

|||

There are 2 options,

Create Table #Table (

ColumnValue Varchar(50)

);

Insert Into #Table

SELECT MAX(Column) FROM TableName;

--OR

SELECT Cast(MAX(Column) as Varchar(50)) ColumnValue Into #Table FROM TableName;

|||Thank you for the help provided so far. Another question on the same subject. How can the Column name be assigned dynamically after a query result? Thanks.
|||

You should use the aliase name. if you failed to give the aliase name for the expression sql server thow an error says that "No column was specified for column n on tablename".

Code Snippet

Select Max(Column) as MaxColumn Into NewTable From OldTable

Select Max(Column) MaxColumn into Newtable From OldTable

|||

As indicated above, No.

My apologies, I guess this statement was not clear enough.

In the process, you need to provide a column Name for any computed or derived columns...

sql

Is it necessary to install Intergration Service for different instance ?

We have to install another instance to an existing SQL Server 2005 Server.
We would like to know whether it is necessary for us to select Integration
Service ? Besides, we would like to know besides Database Engine, is there
any other service we have to select ?
Moreover, we find that Reporting Services is shown as an instance when we
view "Installed Instance", we would like to know why it behaves as an
instance ?
In addition, it seems that even though we have installed default instance,
SQL Server 2005 still gives us a choice of installing Default Instance.
What will happen if we choose "Default Instance" if there is already have
one ? In SQL Server 2000, the choice of Default Instance is disabled.
Thanks
Peter> We would like to know whether it is necessary for us to select Integration Service ?
You can only have one instance of SSIS (which will serve all database engine instance that need to
use it). So, if SSIS is already installed and you select to install it again, setup will tell you
that it is already installed.
> Besides, we would like to know besides Database Engine, is there any other service we have to
> select ?
Not really. But only you know if you after installing this instance need also, say SSAS.
> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
> Instance", we would like to know why it behaves as an instance ?
Because you can have several instances of RS, just the same way as you can have several instances of
the database engine.
> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
> gives us a choice of installing Default Instance. What will happen if we choose "Default Instance"
> if there is already have one ?
Setup will tell you that it already is installed and won't do anything.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:e9v0AnyCIHA.4956@.TK2MSFTNGP06.phx.gbl...
> We have to install another instance to an existing SQL Server 2005 Server. We would like to know
> whether it is necessary for us to select Integration Service ? Besides, we would like to know
> besides Database Engine, is there any other service we have to select ?
> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
> Instance", we would like to know why it behaves as an instance ?
> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
> gives us a choice of installing Default Instance. What will happen if we choose "Default Instance"
> if there is already have one ? In SQL Server 2000, the choice of Default Instance is disabled.
> Thanks
> Peter
>|||Dear Tibor,
From your mail, my understanding is that for SSIS, only 1 instance can be
installed.
On the other hand, for others - like RS, AS, Database Engine, we can install
more than 1 instance.
Is there any other services that only installed once - Like Notification
Services ... ?
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uZ9QyvzCIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> We would like to know whether it is necessary for us to select
>> Integration Service ?
> You can only have one instance of SSIS (which will serve all database
> engine instance that need to use it). So, if SSIS is already installed and
> you select to install it again, setup will tell you that it is already
> installed.
>> Besides, we would like to know besides Database Engine, is there any
>> other service we have to select ?
> Not really. But only you know if you after installing this instance need
> also, say SSAS.
>> Moreover, we find that Reporting Services is shown as an instance when we
>> view "Installed Instance", we would like to know why it behaves as an
>> instance ?
> Because you can have several instances of RS, just the same way as you can
> have several instances of the database engine.
>
>> In addition, it seems that even though we have installed default
>> instance, SQL Server 2005 still gives us a choice of installing Default
>> Instance. What will happen if we choose "Default Instance" if there is
>> already have one ?
> Setup will tell you that it already is installed and won't do anything.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:e9v0AnyCIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> We have to install another instance to an existing SQL Server 2005
>> Server. We would like to know whether it is necessary for us to select
>> Integration Service ? Besides, we would like to know besides Database
>> Engine, is there any other service we have to select ?
>> Moreover, we find that Reporting Services is shown as an instance when we
>> view "Installed Instance", we would like to know why it behaves as an
>> instance ?
>> In addition, it seems that even though we have installed default
>> instance, SQL Server 2005 still gives us a choice of installing Default
>> Instance. What will happen if we choose "Default Instance" if there is
>> already have one ? In SQL Server 2000, the choice of Default Instance is
>> disabled.
>> Thanks
>> Peter
>>
>|||Peter,
> From your mail, my understanding is that for SSIS, only 1 instance can be installed.
Correct.
> On the other hand, for others - like RS, AS, Database Engine, we can install more than 1 instance.
Also correct.
> Is there any other services that only installed once - Like Notification Services ... ?
Actually RS, AS and Db Engine are the only services for which you can install several instances.
You can only have one SQL Server browser - it doesn't make sense to have several.
You can only have one SQL Server VSS Writer - it doesn't make sense to have several.
You can only have one SSIS service
As for Notification Services (NS), you can only install it once. But you need to read about NS to
understand what that means. Installation of NS only installs some binary files (essentially). When
you develop an NS solution, you (among other things) run a program (NSCONTROL.EXE) to create the
Windows service. You can have several of these services, but that part is something you do *after*
you have installed the binary files. Also, NS will not ship with 2008, so it is essentially a dead
component which you probably don't want to build new solutions on.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:ez0wn7%23CIHA.4308@.TK2MSFTNGP06.phx.gbl...
> Dear Tibor,
> From your mail, my understanding is that for SSIS, only 1 instance can be installed.
> On the other hand, for others - like RS, AS, Database Engine, we can install more than 1 instance.
> Is there any other services that only installed once - Like Notification Services ... ?
> Peter
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uZ9QyvzCIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> We would like to know whether it is necessary for us to select Integration Service ?
>> You can only have one instance of SSIS (which will serve all database engine instance that need
>> to use it). So, if SSIS is already installed and you select to install it again, setup will tell
>> you that it is already installed.
>> Besides, we would like to know besides Database Engine, is there any other service we have to
>> select ?
>> Not really. But only you know if you after installing this instance need also, say SSAS.
>> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
>> Instance", we would like to know why it behaves as an instance ?
>> Because you can have several instances of RS, just the same way as you can have several instances
>> of the database engine.
>>
>> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
>> gives us a choice of installing Default Instance. What will happen if we choose "Default
>> Instance" if there is already have one ?
>> Setup will tell you that it already is installed and won't do anything.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:e9v0AnyCIHA.4956@.TK2MSFTNGP06.phx.gbl...
>> We have to install another instance to an existing SQL Server 2005 Server. We would like to know
>> whether it is necessary for us to select Integration Service ? Besides, we would like to know
>> besides Database Engine, is there any other service we have to select ?
>> Moreover, we find that Reporting Services is shown as an instance when we view "Installed
>> Instance", we would like to know why it behaves as an instance ?
>> In addition, it seems that even though we have installed default instance, SQL Server 2005 still
>> gives us a choice of installing Default Instance. What will happen if we choose "Default
>> Instance" if there is already have one ? In SQL Server 2000, the choice of Default Instance is
>> disabled.
>> Thanks
>> Peter
>>
>>
>

Is Count(*) faster than Count(Field)?

So I know not to do a Select * unless I need all the fields, but what about
Count(*)? Is that somehow (internally) slower than Count(FieldName)?First, is the column in question allow NULLs, then the two options doesn't e
ven do this same thing.
COUNT(*) counts all rows, where COUNT(colname) count rows where value for th
e column isn't NULL.
Assuming the column is defined as NOT NULL, then there is a possibly perform
ance advantage of using
COUNT(*), as SQL server can use a non-clustered index on *any* column in the
table to count the
rows. Hopefully, SQL Server will pick a non-clustered index on the smallest
column, where you fit
most rows on a data page, meaning the least amount of I/O. If you specify a
certain column, then SQL
Server *might* be smart enough to realize in simple cases that using any ind
ex is the same as using
an index over that particular column, but I wouldn't trust it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what abou
t Count(*)? Is that
> somehow (internally) slower than Count(FieldName)?
>|||I saw someone suggested using count(1) and said that is the fastest to count
rows. Just like gurus to confirm.
James
"D. Patrick" wrote:

> So I know not to do a Select * unless I need all the fields, but what abou
t
> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>
>|||You can theoretically find a *slightly* faster initial parse time for that q
uery compared COUNT(*)
(at least this holds for EXISTS, I take it the same is for COUNT()). But the
execution plan should
be the same, and we are talking about a few clock-cycles here, nothing you w
orry about in an RDBMS.
It is unlikely that you will be able to even measure such a difference with
any significance (unless
you have some 1000 columns in the table, and I still don't think you'll see
a significant
difference). And, remember this is parse time for the query, not something t
hat happens for every
row, or even every execution (assuming your plans are re-used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0850C483-D5D2-4614-8C74-6770B5162836@.microsoft.com...[vbcol=seagreen]
>I saw someone suggested using count(1) and said that is the fastest to coun
t
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>|||It has been said about Oracle that this makes a difference. I don't know
about Oracle, but on SQL Server it makes zero difference. "SELECT
COUNT(1) FROM ..." is no faster than "SELECT COUNT(*) FROM ..." (it is
equally fast).
Gert-Jan
James Ma wrote:[vbcol=seagreen]
> I saw someone suggested using count(1) and said that is the fastest to cou
nt
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>|||why dont you use the report on the query to see the time expend in such
query?
Bruno Alexandre
(a Portuguese in Denmark)
"D. Patrick" <replywithinthegroup@.thenotreal.com> escreveu na mensagem
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what
> about Count(*)? Is that somehow (internally) slower than
> Count(FieldName)?
>

Is Count(*) faster than Count(Field)?

So I know not to do a Select * unless I need all the fields, but what about
Count(*)? Is that somehow (internally) slower than Count(FieldName)?First, is the column in question allow NULLs, then the two options doesn't even do this same thing.
COUNT(*) counts all rows, where COUNT(colname) count rows where value for the column isn't NULL.
Assuming the column is defined as NOT NULL, then there is a possibly performance advantage of using
COUNT(*), as SQL server can use a non-clustered index on *any* column in the table to count the
rows. Hopefully, SQL Server will pick a non-clustered index on the smallest column, where you fit
most rows on a data page, meaning the least amount of I/O. If you specify a certain column, then SQL
Server *might* be smart enough to realize in simple cases that using any index is the same as using
an index over that particular column, but I wouldn't trust it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what about Count(*)? Is that
> somehow (internally) slower than Count(FieldName)?
>|||I saw someone suggested using count(1) and said that is the fastest to count
rows. Just like gurus to confirm.
James
"D. Patrick" wrote:
> So I know not to do a Select * unless I need all the fields, but what about
> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>
>|||You can theoretically find a *slightly* faster initial parse time for that query compared COUNT(*)
(at least this holds for EXISTS, I take it the same is for COUNT()). But the execution plan should
be the same, and we are talking about a few clock-cycles here, nothing you worry about in an RDBMS.
It is unlikely that you will be able to even measure such a difference with any significance (unless
you have some 1000 columns in the table, and I still don't think you'll see a significant
difference). And, remember this is parse time for the query, not something that happens for every
row, or even every execution (assuming your plans are re-used).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0850C483-D5D2-4614-8C74-6770B5162836@.microsoft.com...
>I saw someone suggested using count(1) and said that is the fastest to count
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
>> So I know not to do a Select * unless I need all the fields, but what about
>> Count(*)? Is that somehow (internally) slower than Count(FieldName)?
>>|||It has been said about Oracle that this makes a difference. I don't know
about Oracle, but on SQL Server it makes zero difference. "SELECT
COUNT(1) FROM ..." is no faster than "SELECT COUNT(*) FROM ..." (it is
equally fast).
Gert-Jan
James Ma wrote:
> I saw someone suggested using count(1) and said that is the fastest to count
> rows. Just like gurus to confirm.
> James
> "D. Patrick" wrote:
> > So I know not to do a Select * unless I need all the fields, but what about
> > Count(*)? Is that somehow (internally) slower than Count(FieldName)?
> >
> >
> >|||why dont you use the report on the query to see the time expend in such
query?
--
Bruno Alexandre
(a Portuguese in Denmark)
"D. Patrick" <replywithinthegroup@.thenotreal.com> escreveu na mensagem
news:5_1ag.2874$9W5.1637@.tornado.socal.rr.com...
> So I know not to do a Select * unless I need all the fields, but what
> about Count(*)? Is that somehow (internally) slower than
> Count(FieldName)?
>