DPV's

Is there any other way to do load-balancing other than SQL Server 2000's
Distributed partition Views? I am aware that 2005 has done something like
Distributed Tables, but that is, for my purposes, the same thing.
What I do not want to do is try to guess what gets accessed in what fashion
and try and place tables on various nodes of the cluster.\
Thanks,
jay2005's table partitioning cannot be distributed across servers. DPVs are
still the way to do that in 2005.
By the way, this isn't really "load balancing"--it's more along the lines of
scale out. For more traditional load balancing you might take a look at SQL
Server 2005's peer-to-peer replication technology (although I've heard very
mixed reviews on its effectiveness).
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Jay" <nospam@.nospam.org> wrote in message
news:OtIO7jo6HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Is there any other way to do load-balancing other than SQL Server 2000's
> Distributed partition Views? I am aware that 2005 has done something like
> Distributed Tables, but that is, for my purposes, the same thing.
> What I do not want to do is try to guess what gets accessed in what
> fashion and try and place tables on various nodes of the cluster.\
> Thanks,
> jay
>

Dose it make sense to create indexted views on a single table?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if there is any sense to create indexed views on single table? I simple want to improve the report query performance as most of the reports data are from a single table. As views most of the time are created as for joined across tables.

Thank you very much for your advices and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

H Helen,

Theres is no point creating a indexed view with a view that has the columns from single table.

Infact, you will creating an overhead on your transactions as they will have to keep the indexes on view upto date on top the indexes on the table.

regards

Jag

|||

Hi, Jag,

Thank you very much for your advices and kind help. It's been very helpful.

With best regards,

Yours sincerely,

|||

Jag,

What if the single table was a transaction table, and the view was an aggregation of that transaction table.

I was under the impression that that was one of the primary reasons to utilise Indexed Views, that the aggregated data is available without having to transverse the underlying table rows, therebye improving query performance.

Cheers,

Will

|||

i would agree with Will ... though the view is refering single table... if this view is aggregation and the table is having millions of records, it make sense that you may create indexed view.

Madhu

|||

Hi Will,

You are right, if view is going to be storing aggregated data, then It is worth creating an index on a view even if is a single table.

regards

Jag

Domains

What is the best way to move Views and Stored Procedures from Dev to Prod?
Is DTS a good option. Thankshi niles,
Go to Enterprise manager. Right click on the database. click on "all
tasks" . click on "generate sql script". click on "show all". Click on "all
views" and "all stored procedures", this will generate a script file with
DDL commands. You can run this file on the destination database/server
through query analyzer.
Vishal Parkar
vgparkar@.yahoo.co.in|||Thanks, works beautifully!

Domains

What is the best way to move Views and Stored Procedures from Dev to Prod? Is DTS a good option. Thanks
hi niles,
Go to Enterprise manager. Right click on the database. click on "all
tasks" . click on "generate sql script". click on "show all". Click on "all
views" and "all stored procedures", this will generate a script file with
DDL commands. You can run this file on the destination database/server
through query analyzer.
Vishal Parkar
vgparkar@.yahoo.co.in
|||Thanks, works beautifully!

Domains

What is the best way to move Views and Stored Procedures from Dev to Prod? Is DTS a good option. Thankshi niles,
Go to Enterprise manager. Right click on the database. click on "all
tasks" . click on "generate sql script". click on "show all". Click on "all
views" and "all stored procedures", this will generate a script file with
DDL commands. You can run this file on the destination database/server
through query analyzer.
--
Vishal Parkar
vgparkar@.yahoo.co.in

Does views use table indexes.

Hi.
I have a situation where I have a table containg data, but several columns
can be used to designate that the data is "hidden". E.g. if we delete a
record, it isn't actually deleted but a datetime field called deleted_date
is changed from null to the current date-time.
Therefore I made a view like
create view vw_LiveData as select * from Data where deleted_date is null
The original table had a full-text indexed column, but using that search
criteria on the view didn't work
select * from vw_LiveData where contains(fullTextField, 'blahblahblah')
The line produces an error saying that the view doesn't have a fulltext
index.
What I speculate now is all the other indexes defined on the table, do they
have any importance when searching through data in the view?
Suppose the Data table has a foreign key to a user called User_ID that is
indexed on the table
Clearly the index is used when I type
Select * from Data where User_ID=@.User_ID
Is it also used when I type
select * from vw_LiveData where User_ID=@.User_ID
or is it necessary to created index views if I want both easy to read sql
code AND acceptable performance?
Thanks in advance,
Peter StrimanPeter Striman wrote:
> Hi.
> I have a situation where I have a table containg data, but several
> columns can be used to designate that the data is "hidden". E.g. if
> we delete a record, it isn't actually deleted but a datetime field
> called deleted_date is changed from null to the current date-time.
> Therefore I made a view like
> create view vw_LiveData as select * from Data where deleted_date is
> null
> The original table had a full-text indexed column, but using that
> search criteria on the view didn't work
> select * from vw_LiveData where contains(fullTextField,
> 'blahblahblah')
> The line produces an error saying that the view doesn't have a
> fulltext index.
> What I speculate now is all the other indexes defined on the table,
> do they have any importance when searching through data in the view?
> Suppose the Data table has a foreign key to a user called User_ID
> that is indexed on the table
> Clearly the index is used when I type
> Select * from Data where User_ID=@.User_ID
> Is it also used when I type
> select * from vw_LiveData where User_ID=@.User_ID
> or is it necessary to created index views if I want both easy to read
> sql code AND acceptable performance?
No. The index is used. It may be different with full text indexes -
check with BOL. Btw, IMHO it's better to name all columns in the view
explicitely. That way your view does not change if the underlying table
changes.
Kind regards
robert|||Thanks for the reply, just what I wanted to hear :)
It is different with full-text indexes - and that was what gave me the
concern. But it's no issue in my case since I would only use the
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Peter Striman wrote:
> No. The index is used. It may be different with full text indexes -
> check with BOL. Btw, IMHO it's better to name all columns in the view
> explicitely. That way your view does not change if the underlying table
> changes.
> Kind regards
> robert
>|||
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:<#F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl>...
<snip>
Btw, IMHO it's better to name all columns in the view

> explicitely. That way your view does not change if the underlying table

> changes.

>

> Kind regards

>

> robert

>
I STRONGLY agree. A view using a SELECT * will do very strange things if
fields are added to the table.
Daniel Wilson <Mailto://d.wilson@.embtrak.com>
Senior Software Solutions Developer
Embtrak <http://www.Embtrak.com> Development Team
DVBrown Company
(864)292-5888

Does this make sense ?

We have one publication having all the tables, then another publication for
stored procs, yet another for views and one for indexed views. And all set
up for transactional replication. I understand the need for the tables
portion. Right now, the stored procs, views and indexed views are just to
have their schemas on the subscriber as a one time. I believe its an
overkill to have those set up as transactional replication right ? We could
maybe set them up as snapshot replication ... right ?
Thanks
Hassan,
for stored procedures and views and UDFs in many ways it doesn't make any
difference, however sp_addscriptexec can be useful in the case of incorrect
dependencies, and it is not available with snapshot replication. For this
reason I'd recommend you use transactional. NB be sure to select the option
to have an independant distribution agent, so problems synchronizing the
views publication won't block the tables one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Other than Paul's excellent advise - keep in mind the sp_addscritpexec
only works for subscribers deployed through UNCs.
You may also need to run sp_refreshsubscriptions before re-running any
publications which contain procs, views, and functions which you have
modified. IIRC Paul pointed out to me that replication is not aware of
changes to procedures which it is publishing and you need to drop and
recreate these publications.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com