Computed column

I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?

ChenderRegarding your failing inserts and updates, the likely cause is that one ore
more connection options are not set properly for maintaining a computed
column index. The error message will indicate which option(s) is
incompatible. The required options specified in the Books Online
<createdb.chm::/cm_8_des_05_8os3.htm> are:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

All but ARITHABORT are set automatically when connection via OLEDB or ODBC.

Please post your DDL, query and sample data for help with your performance
issue.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"BCR" <bcrao@.yahoo.com> wrote in message
news:32c11012.0411181258.7d47dd2b@.posting.google.c om...
>I created a index on a computed column. I did not see any improvement
> in performance with a join to this column and also my inserts and
> updates to this table are failing. Any ideas?
> Chender|||[posted and mailed, please reply in nes]

BCR (bcrao@.yahoo.com) writes:
> I created a index on a computed column. I did not see any improvement
> in performance with a join to this column and also my inserts and
> updates to this table are failing. Any ideas?

As Dan pointed out the update/inserts are failing because of the settings.
I like to add two things. The first is that the lack of performance is
very likely due to the same problems. If the proper settings are not in
effect, SQL Server will not consider the index.

The other is that the two settings ANSI_NULLS and QUOTED_IDENTIFIER are
saved when you create a stored procedure, view, trigger or user-defined
functions. So that if even if these are on at run-time, they will not be
in effect if they were off when the procedure was created. Some tools,
Enterprise Manager and OSQL to be precise, have QUOTED_IDENTIFIER off by
default.

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

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

compression of database backups - as it is being created - options

Most if not all of the sql servers that I support are not on a SAN, and are
suffering from not having enough local space. This could be mitigated if we
were using some form of database dump compression. The optimum solution
would be one which compresses the data as it was being initially written to
the dump file.
( versus creating a full blown dump file and then running a second step to
compress it )
This compressed file would also benefit us in any subsequent copy steps,
whereby we would be copying the file to another server for stand-by purposes
.
( less network time/traffic )
If Microsoft Sql server does not have built in compression, what other
options are available? Are there any 3rd party plug-ins that interface with
sql server and provide a compressed dump file as it is being initially
written?I've listed some such 3:rd party apps at : http://www.karaszi.com/SQLServer/links.
asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JohnR" <JohnR@.discussions.microsoft.com> wrote in message
news:556E82D2-EA42-462F-BFDB-BA9A152AE2B0@.microsoft.com...
> Most if not all of the sql servers that I support are not on a SAN, and ar
e
> suffering from not having enough local space. This could be mitigated if
we
> were using some form of database dump compression. The optimum solution
> would be one which compresses the data as it was being initially written t
o
> the dump file.
> ( versus creating a full blown dump file and then running a second step to
> compress it )
> This compressed file would also benefit us in any subsequent copy steps,
> whereby we would be copying the file to another server for stand-by purpos
es.
> ( less network time/traffic )
> If Microsoft Sql server does not have built in compression, what other
> options are available? Are there any 3rd party plug-ins that interface wi
th
> sql server and provide a compressed dump file as it is being initially
> written?

composite index and column order

Hi,

I created a composite index (lastname, firstname). I know the following
queries will use this index:

WHERE lastname = ...
WHERE lastname = ... AND firstname = ...

Also this won't use the index:
WHERE firstname = ...

But how about: WHERE firstname = .. AND lastname = ...

And why?

Thanks a lot,

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORGBaihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...
> But how about: WHERE firstname = .. AND lastname = ...

It will use the index.

> And why?

Because, as far as the query optimizer is concerned, these two are
exactly the same:

WHERE lastname = ... AND firstname = ...
WHERE firstname = ... AND lastname = ...|||Thanks for your help, I really appreciate it.

Baihao

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG|||Baihao Yuan wrote:
> Hi,
> I created a composite index (lastname, firstname). I know the following
> queries will use this index:
> WHERE lastname = ...
> WHERE lastname = ... AND firstname = ...
> Also this won't use the index:
> WHERE firstname = ...

Not necessarily. Consider the following query:

select lastname, firstname from some_table where firstname = ...

It will use the index, and, more to the point, it will not touch the
table at all - the index already has all the information the query
needs. It is called "index covering".|||Alexander Kuznetsov wrote:
> Baihao Yuan wrote:
> > Hi,
> > I created a composite index (lastname, firstname). I know the following
> > queries will use this index:
> > WHERE lastname = ...
> > WHERE lastname = ... AND firstname = ...
> > Also this won't use the index:
> > WHERE firstname = ...
> Not necessarily. Consider the following query:
> select lastname, firstname from some_table where firstname = ...
> It will use the index, and, more to the point, it will not touch the
> table at all - the index already has all the information the query
> needs. It is called "index covering".

No, it won't. If you had a list of people on a piece of paper, sorted
by last name and then by first name, explain how you would use that
list to find everyone with a first name of "Joe" without looking
through the entire list.|||I created a table called tblNames with nonClustered index defined on
lastname,firstname (composite index).

select * from tblnames where lastname = 'smith'--Performed an Index
Seek

select * from tblnames where lastname = 'smith' and firstname =
'john'--Performed an Index Seek

select * from tblnames where firstname = 'john'--Performed a Table Scan

select * from tblnames where firstname = 'john' and lastname =
'smith'--Performed an Index Seek

ZeldorBlat wrote:
> Alexander Kuznetsov wrote:
> > Baihao Yuan wrote:
> > > Hi,
> > > > I created a composite index (lastname, firstname). I know the following
> > > queries will use this index:
> > > > WHERE lastname = ...
> > > WHERE lastname = ... AND firstname = ...
> > > > Also this won't use the index:
> > > WHERE firstname = ...
> > > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.|||> > Not necessarily. Consider the following query:
> > select lastname, firstname from some_table where firstname = ...
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Why don't you try it out in practice? You might be in for some
surprise. If the index is smaller than the table, and contains all the
necessary information, it is likely to be used instead of the table.
Google up "index covering".|||Alexander Kuznetsov wrote:
> > > Not necessarily. Consider the following query:
> > > > select lastname, firstname from some_table where firstname = ...
> > > > It will use the index, and, more to the point, it will not touch the
> > > table at all - the index already has all the information the query
> > > needs. It is called "index covering".
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Why don't you try it out in practice? You might be in for some
> surprise. If the index is smaller than the table, and contains all the
> necessary information, it is likely to be used instead of the table.
> Google up "index covering".

I did try it in practice -- as did the OP who posted his results in
this thread.|||>
> select * from tblnames where firstname = 'john'--Performed a Table Scan

If you only select 2 columns, firstname, lastname

select columns, firstname from tblnames where firstname = 'john'

and the table has a lot of other columns, the index is likely to be
used even if firstname is not the first column in the index. The reason
is simple: the index contains all the information necessary to satisfy
the query and it is smaller than the table.|||> I did try it in practice -- as did the OP who posted his results in
> this thread.

the OP tried for

select * from ...

while I was speaking aobut

select lastName, firstname from ...

Big difference.|||correction:

If you only select 2 columns, firstname, lastname

select firstname, lastname from tblnames where firstname = 'john'

> and the table has a lot of other columns, the index is likely to be
> used even if firstname is not the first column in the index. The reason
> is simple: the index contains all the information necessary to satisfy
> the query and it is smaller than the table.|||ZeldorBlat (zeldorblat@.gmail.com) writes:
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

Say further that with each list there is a page number to references
where the persons appear in the book.

If all you want to know is the name of the persons, you can scan
the index, you don't have to read the whole book.

It's important to keep in mind that an index can be used in two
ways: Seek (look up data through the index tree) and Scan (read
the entire index from left to right): While the latter is far more
expensive, it can still be useful at times.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> ZeldorBlat (zeldorblat@.gmail.com) writes:
> > No, it won't. If you had a list of people on a piece of paper, sorted
> > by last name and then by first name, explain how you would use that
> > list to find everyone with a first name of "Joe" without looking
> > through the entire list.
> Say further that with each list there is a page number to references
> where the persons appear in the book.
> If all you want to know is the name of the persons, you can scan
> the index, you don't have to read the whole book.
> It's important to keep in mind that an index can be used in two
> ways: Seek (look up data through the index tree) and Scan (read
> the entire index from left to right): While the latter is far more
> expensive, it can still be useful at times.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Point taken. Thanks, Erland.|||I'd like to repeat the suggestion to do your own experimenting. The
technology evolves quite fast, so anything you might have read in any
book / article / whatever esle may be already obsolete. The optimizer
is way smarter now than it used to be 5 or 10 years ago. In this
particular case you did not need to read anything, you could just take
any table of, say, 100K rows, with, say, 20 columns, create an index on
it

create index i1 on t1(col1, col2)

and see the execution plan for the query

select col1, col2 from t1 where col2 =...

That's all it takes, it's that simple.

Good luck!

Complex sum

I have a report created using SQL Server Reporting tools.

The report contains a field 'FIELD A' which displays the difference of hours in that record.
At the end of the report, I need to sum all FIELD A and display in FIELD B.

How do I do it?One way to do this, if all the 'FIELD A' textboxes exist on one page, is to add the 'Field B' textbox to the Page Footer, and use the SUM aggregate over the textbox. For example,

=(Sum(ReportItems!FieldATextbox.Value))

This will only work if the expression is in the Page Header or Footer, and if all instances of the 'Field A' textbox is on the same page.

If not all instances will exist on one page, then you could use a simple custom function that is called from the 'Field A' textbox. This custom function adds the value passed to a member field, and then returns the value passed. In the 'Field B' textbox call a separate custom function that returns the value stored in the member field.

Ian

Complete Newbie Question

I have just installed Reporting Services and created a few reports that
I want to run in a web page - that seems easy enough to do but my boss
has asked me to provide a batch reporting solution.
He wants users to be able to request a report and the request be
written away to a sql db. I have a windows service that picks up all
new requests and fires up some .exe's depending on what sort of request
it is e.g password reset , password reminder etc..
When the reports are run I need them to be saved as PDF's into a
specific folder that can be accessed later from a web page.
Now all this is written and working using ActiveReports for .NET but we
hate that package and want to use sql reporting services. Can it be
done in sql reporting services and if so how do I do it - I mean I need
some real code examples
any help would be brilliant
cheers
JimI have been using log4net to log to SQL DB. You can log the web request when
reports are rendered. It is upto you to write the logic to who views the
page( for eg using session object who the user was like,
Session["UserName"]). log4net allows you to log when certain events are
triggered blah blah... Check out:
http://logging.apache.org/log4net/release/config-examples.html
Look for ADONETAppender which is what you want.
HTH
Rajesh Meenrajan
MCSD.NET
http://meenrajan.blogspot.com
"JimW13UK" wrote:
> I have just installed Reporting Services and created a few reports that
> I want to run in a web page - that seems easy enough to do but my boss
> has asked me to provide a batch reporting solution.
> He wants users to be able to request a report and the request be
> written away to a sql db. I have a windows service that picks up all
> new requests and fires up some .exe's depending on what sort of request
> it is e.g password reset , password reminder etc..
> When the reports are run I need them to be saved as PDF's into a
> specific folder that can be accessed later from a web page.
> Now all this is written and working using ActiveReports for .NET but we
> hate that package and want to use sql reporting services. Can it be
> done in sql reporting services and if so how do I do it - I mean I need
> some real code examples
> any help would be brilliant
> cheers
> Jim
>

Compatibility Level Question

I am a little confused on the importance of the Compatability Level setting.
I have a database originally created in SQL Server 2000 that I backed up and
then restored to SQL Server 2005. The compatibility level is currently set
at 80 (SQL 2000). I would like to change this to 90 (SQL 2005) but am not
sure of the ramifications of doing this. Can someone tell me of any negative
issues I should be aware of by doing this? I would like to treat this
database as if it were created as a SQL 2005 database (IOW, I don't need any
2000 support or compatibility).
Amos.Check out the description of sp_dbcmptlevel in the SQL 2005 Books Online for
a description of the behavior differences between 80 and 90. If you see not
changes that affect your app, you are probably ok but it's always a good
idea to test.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:up7T4sgEGHA.2648@.TK2MSFTNGP11.phx.gbl...
>I am a little confused on the importance of the Compatability Level
>setting. I have a database originally created in SQL Server 2000 that I
>backed up and then restored to SQL Server 2005. The compatibility level is
>currently set at 80 (SQL 2000). I would like to change this to 90 (SQL
>2005) but am not sure of the ramifications of doing this. Can someone tell
>me of any negative issues I should be aware of by doing this? I would like
>to treat this database as if it were created as a SQL 2005 database (IOW, I
>don't need any 2000 support or compatibility).
> Amos.
>

Compatibility Level Question

I am a little confused on the importance of the Compatability Level setting.
I have a database originally created in SQL Server 2000 that I backed up and
then restored to SQL Server 2005. The compatibility level is currently set
at 80 (SQL 2000). I would like to change this to 90 (SQL 2005) but am not
sure of the ramifications of doing this. Can someone tell me of any negative
issues I should be aware of by doing this? I would like to treat this
database as if it were created as a SQL 2005 database (IOW, I don't need any
2000 support or compatibility).
Amos.
Check out the description of sp_dbcmptlevel in the SQL 2005 Books Online for
a description of the behavior differences between 80 and 90. If you see not
changes that affect your app, you are probably ok but it's always a good
idea to test.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:up7T4sgEGHA.2648@.TK2MSFTNGP11.phx.gbl...
>I am a little confused on the importance of the Compatability Level
>setting. I have a database originally created in SQL Server 2000 that I
>backed up and then restored to SQL Server 2005. The compatibility level is
>currently set at 80 (SQL 2000). I would like to change this to 90 (SQL
>2005) but am not sure of the ramifications of doing this. Can someone tell
>me of any negative issues I should be aware of by doing this? I would like
>to treat this database as if it were created as a SQL 2005 database (IOW, I
>don't need any 2000 support or compatibility).
> Amos.
>

Compatibility Level Question

I am a little confused on the importance of the Compatability Level setting.
I have a database originally created in SQL Server 2000 that I backed up and
then restored to SQL Server 2005. The compatibility level is currently set
at 80 (SQL 2000). I would like to change this to 90 (SQL 2005) but am not
sure of the ramifications of doing this. Can someone tell me of any negative
issues I should be aware of by doing this? I would like to treat this
database as if it were created as a SQL 2005 database (IOW, I don't need any
2000 support or compatibility).
Amos.Check out the description of sp_dbcmptlevel in the SQL 2005 Books Online for
a description of the behavior differences between 80 and 90. If you see not
changes that affect your app, you are probably ok but it's always a good
idea to test.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:up7T4sgEGHA.2648@.TK2MSFTNGP11.phx.gbl...
>I am a little confused on the importance of the Compatability Level
>setting. I have a database originally created in SQL Server 2000 that I
>backed up and then restored to SQL Server 2005. The compatibility level is
>currently set at 80 (SQL 2000). I would like to change this to 90 (SQL
>2005) but am not sure of the ramifications of doing this. Can someone tell
>me of any negative issues I should be aware of by doing this? I would like
>to treat this database as if it were created as a SQL 2005 database (IOW, I
>don't need any 2000 support or compatibility).
> Amos.
>