Showing posts with label copied. Show all posts
Showing posts with label copied. Show all posts

Wednesday, March 7, 2012

Indexes not working after converting to 2000

I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
DonThis is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Two things:
1. Order is not guaranteed, unless you include an ORDER BY in your
SELECT.
2. You should update statistics on all user tables (preferably WITH
FULLSCAN) when upgrading a database from 7.0 to 2000.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
Don
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Two things:
1. Order is not =guaranteed, unless you include an ORDER BY in your SELECT.
2. You should update =statistics on all user tables (preferably WITH FULLSCAN) when upgrading a database =from 7.0 to 2000.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Don" wrote =in message news:047601c3b2c4$e3=e7cfa0$a301280a@.phx.gbl...I detached a SQL 7 DB, copied it to a server running SQL 2000 and =attached it.The indexes don't appear to be working. I've reindexed the =tables and executed a simple query that should display the data in date order, =but it's displayed in a random order. The same query on the original DB =in SQL 7 shows the data in date order.Anyone know what's going =on?There's only one index. It's a nonclustered index on two fields. Name+date.Thanks,Don

--=_NextPart_000_02C4_01C3B29B.F1DDDD30--|||Hi Don
The only queries that 'should' display data in a particular order are
queries that contain ORDER BY. If you were formerly getting data back in a
preferred order, you were just lucky. It was never documented or supported
that any query, using any index, would return data in any speficic order
without using ORDER BY.
OTOH, having a good index can help speed up the order by, and possibly make
a sort unnecessary, but the ORDER BY must be there in order to guarantee the
order of the rows in the result set.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
> I detached a SQL 7 DB, copied it to a server running SQL
> 2000 and attached it.
> The indexes don't appear to be working. I've reindexed
> the tables and executed a simple query that should
> display the data in date order, but it's displayed in a
> random order.
> The same query on the original DB in SQL 7 shows the data
> in date order.
> Anyone know what's going on?
> There's only one index. It's a nonclustered index on two
> fields. Name+date.
> Thanks,
> Don
>|||Thanks for you help. I didn't realize I was just being
lucky :-)
Don
>--Original Message--
>Hi Don
>The only queries that 'should' display data in a
particular order are
>queries that contain ORDER BY. If you were formerly
getting data back in a
>preferred order, you were just lucky. It was never
documented or supported
>that any query, using any index, would return data in
any speficic order
>without using ORDER BY.
>OTOH, having a good index can help speed up the order
by, and possibly make
>a sort unnecessary, but the ORDER BY must be there in
order to guarantee the
>order of the rows in the result set.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>> I detached a SQL 7 DB, copied it to a server running
SQL
>> 2000 and attached it.
>> The indexes don't appear to be working. I've reindexed
>> the tables and executed a simple query that should
>> display the data in date order, but it's displayed in a
>> random order.
>> The same query on the original DB in SQL 7 shows the
data
>> in date order.
>> Anyone know what's going on?
>> There's only one index. It's a nonclustered index on
two
>> fields. Name+date.
>> Thanks,
>> Don
>
>.
>|||I didn't know that about the ORDER BY.
Thanks for your help. I'll do the Scan update statistic.
Don
>--Original Message--
>Two things:
>1. Order is not guaranteed, unless you include an
ORDER BY in your
>SELECT.
>2. You should update statistics on all user tables
(preferably WITH
>FULLSCAN) when upgrading a database from 7.0 to 2000.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>I detached a SQL 7 DB, copied it to a server running SQL
>2000 and attached it.
>The indexes don't appear to be working. I've reindexed
>the tables and executed a simple query that should
>display the data in date order, but it's displayed in a
>random order.
>The same query on the original DB in SQL 7 shows the data
>in date order.
>Anyone know what's going on?
>There's only one index. It's a nonclustered index on two
>fields. Name+date.
>Thanks,
>Don
>
Posted by Unknown at 8:36 AM 0 comments
Labels: , , , , , , , , , , , , , ,

Friday, February 24, 2012

Indexes and keys are not synced w/ snapshot publication

Hello,
I am having trouble getting the indexes and primary keys copied to the
subscriber in a snapshot publication. I created a snapshot publication and
added an article to it using the wizard. Then I used the SQL Management
Studio to configure the properties of the article and set the following
options to true.
Copy primary key
Copy clustered index
Copy nonclustered indexes
Copy check constraints
Copy foreign keys
I generated the SQL script and got the code listed below to re-create the
article. When I use this script to create the article, the settings mentioned
above are "true" as expected. However, when the snapshot is applied to the
subscriber, none of the keys or indexes are transferred eventough everything
suceeds.
Does anyone know what else I can do to make this work? Did I not configure
it correctly?
Here is the code to recreate the article.
exec sp_addarticle
@.publication = @.PUBLICATON_NAME,
@.article = @.TABLE_NAME,
@.source_owner = N'dbo',
@.source_object = @.TABLE_NAME,
@.type = N'logbased',
@.description = null,
@.creation_script = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00000000080350DD,
@.identityrangemanagementoption = N'none',
@.destination_table = @.DESTINATION_TABLE_NAME,
@.destination_owner = N'dbo',
@.vertical_partition = N'false'
P.S - FYI, the destination table name is different than the source table
name as per our biz requirements. Could this be why?
Thanks!
Johnny
UPDATE: I just did some additional testing and found that if the destination
table stays the same as the source, the PKs and the indexes are copied to the
subscriber as they should be. It seems like this problem only occurs when the
destination table is different.
Does anyone know if and how I can work around this or force it to include
them even though the destination table is different?
Johnny
"Johnny" wrote:

> Hello,
> I am having trouble getting the indexes and primary keys copied to the
> subscriber in a snapshot publication. I created a snapshot publication and
> added an article to it using the wizard. Then I used the SQL Management
> Studio to configure the properties of the article and set the following
> options to true.
> Copy primary key
> Copy clustered index
> Copy nonclustered indexes
> Copy check constraints
> Copy foreign keys
>
> I generated the SQL script and got the code listed below to re-create the
> article. When I use this script to create the article, the settings mentioned
> above are "true" as expected. However, when the snapshot is applied to the
> subscriber, none of the keys or indexes are transferred eventough everything
> suceeds.
> Does anyone know what else I can do to make this work? Did I not configure
> it correctly?
>
> Here is the code to recreate the article.
> ----
> exec sp_addarticle
> @.publication = @.PUBLICATON_NAME,
> @.article = @.TABLE_NAME,
> @.source_owner = N'dbo',
> @.source_object = @.TABLE_NAME,
> @.type = N'logbased',
> @.description = null,
> @.creation_script = null,
> @.pre_creation_cmd = N'drop',
> @.schema_option = 0x00000000080350DD,
> @.identityrangemanagementoption = N'none',
> @.destination_table = @.DESTINATION_TABLE_NAME,
> @.destination_owner = N'dbo',
> @.vertical_partition = N'false'
>
> P.S - FYI, the destination table name is different than the source table
> name as per our biz requirements. Could this be why?
> Thanks!
> Johnny
|||Hi Johnny,
The indexes, constraints should be copied to the subscriber even though the
destination object name is different than source object name. I suspect that
you probably still have the "old" table with the source table name and the
same constraints at the subscriber. What happens in this case is that since
constraint names have to be unique across all tables, the distribution agent
simply cannot create a constraint on the "new" table with the same name as
one on the "old" table.
-Raymond
"Johnny" wrote:
[vbcol=seagreen]
> UPDATE: I just did some additional testing and found that if the destination
> table stays the same as the source, the PKs and the indexes are copied to the
> subscriber as they should be. It seems like this problem only occurs when the
> destination table is different.
> Does anyone know if and how I can work around this or force it to include
> them even though the destination table is different?
> Johnny
>
> "Johnny" wrote:
|||Wow!!!!! You hit it right on the money. That was the problem - I had the old
table with the same index names. After dropping this table, I was able to
successfully distribute the snapshot with a different destination name and
all indexes, primary keys, etc. were successfully transferred.
Thanks Raymond.
- Johnny
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Johnny,
> The indexes, constraints should be copied to the subscriber even though the
> destination object name is different than source object name. I suspect that
> you probably still have the "old" table with the source table name and the
> same constraints at the subscriber. What happens in this case is that since
> constraint names have to be unique across all tables, the distribution agent
> simply cannot create a constraint on the "new" table with the same name as
> one on the "old" table.
> -Raymond
> "Johnny" wrote:
Posted by Unknown at 9:37 PM 0 comments
Labels: , , , , , , , , , , , , , , ,

Sunday, February 19, 2012

Indexed Views, Space Used.

Potentially stupid question but here goes:
Does an indexed view require extra space (due to data being copied) or
is it just a logical construct that uses existing table data?
Thanks, TFD.> Does an indexed view require extra space (due to data being copied) or
> is it just a logical construct that uses existing table data?
A standard view is a logical construct that is temporarily materialized when
a statement referencing the view is executed. An indexed view is
materialized and stored on disk at the time the clustered index is created
on the view. So, yes, it requires additional disk space to hold the
clustered index.
You might find this white paper useful
http://www.microsoft.com/technet/pr.../ipsql05iv.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137686494.821811.34620@.g47g2000cwa.googlegroups.com...
> Potentially stupid question but here goes:
> Does an indexed view require extra space (due to data being copied) or
> is it just a logical construct that uses existing table data?
> Thanks, TFD.
>|||> So, yes, it requires additional disk space to hold the clustered index.
And further to disk space, there is also additional I/O when you issue DML
against the base table (since it has to mirror those changes in the
materialized view(s)).
A|||Is there a way to determine the space used by a materialized view
(after the fact) like you can do for a table?|||Sure. You can use sp_spaceused for indexed views.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137710309.602103.184880@.z14g2000cwz.googlegroups.com...
> Is there a way to determine the space used by a materialized view
> (after the fact) like you can do for a table?
>|||CREATE VIEW V1
AS
SELECT a, SUM(b) AS Revenue
FROM MyTable
GROUP BY a
GO
sp_spaceused 'V1'
and the result comes back as
Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
Views do not have space allocated.
'|||Ummm, that's not an indexed view.
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137725398.306412.146190@.g43g2000cwa.googlegroups.com...
> CREATE VIEW V1
> AS
> SELECT a, SUM(b) AS Revenue
> FROM MyTable
> GROUP BY a
> GO
>
> sp_spaceused 'V1'
>
> and the result comes back as
> Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
> Views do not have space allocated.
> '
>|||Well, at least create the clustered index. :) As soon as it's created the
system procedure will work.
ML
http://milambda.blogspot.com/|||And further to that, you'll need to create the view WITH SCHEMABINDING
in order to create the clustered index on it, the clustered index must
be a *unique* clustered index, you must use 2 part names in the view
(i.e. you must specify the owner of "MyTable"), and the owner of the
view & the base tables referenced in the view must all be the same. For
example:
use tempdb;
go
create table dbo.SalesAmounts
(
InvoiceID int identity(1,1) primary key clustered,
SalesPerson varchar(10) not null,
Amount smallmoney not null
);
go
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 10.90);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 17.45);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 3.95);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Bill', 78.85);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Bill', 26.50);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 16.20);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 12.10);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 18.90);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 9.95);
go
create view dbo.SalesAggregates with schemabinding
as
select SalesPerson, sum(Amount) as Revenue, count_big(*) as NumSales
from dbo.SalesAmounts
group by SalesPerson;
go
create unique clustered index UX_SalesAggregates_SalesPerson on dbo.SalesAgg
regates (SalesPerson);
go
select InvoiceID, SalesPerson, Amount from dbo.SalesAmounts;
select SalesPerson, Revenue, NumSales from dbo.SalesAggregates;
go
exec sp_spaceused 'dbo.SalesAmounts';
exec sp_spaceused 'dbo.SalesAggregates';
go
drop view dbo.SalesAggregates;
drop table dbo.SalesAmounts;
go
There are quite a few caveats and considerations around indexed views,
for more info see BOL
(http://msdn.microsoft.com/library/e...des_06_9jnb.asp).
*mike hodgson*
http://sqlnerd.blogspot.com
ML wrote:

>Well, at least create the clustered index. :) As soon as it's created the
>system procedure will work.
>
>ML
>--
>http://milambda.blogspot.com/
>
Posted by Unknown at 11:51 PM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)