Hi, still new to all this I'm afraid! I've created a DTS routine that copys some table data from an Access database into another Access database. In the second mdb, I would like the routine to add an ID field and populate all the lines. I'm sure it must be kind of easy but I can't find how!
Any help appreciated.Ah sorted - just added the ID as an Identity when the table was being created in the routine.
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
adding 140,000 records requires index rebuild. make sense?
hello all,
i have a table thats getting about 140,000 new records a day from a program
reading data from a socket (about 300 every 3 minutes). in addition to the
primary key it has an 8 byte date/time field thats indexed. there are also
two full-text indexed columns with change tracking and update in background
enabled.
as we started to load the database we noticed after some hours 'insert'
operations began to fail with a timeout and queries began timing out.
When i rebuild the index based on date/time it cleared up and queries were
fast again. This cycle has been going on for a couple of w
s. I haven't
had to do anything to the full-text portion, these problem orbit around the
date/time index.
Does this make sense? It really sounds like the indexes have exceeded some
internal resource and are being added to in an inefficient manner causing
the timeout.
is there a body of knowledge about defining indexes for tables that grow
like this that i should be aware of?
thanks,
john mottJohn,
I have not heard of any issues like this. Maybe a blocking issue?
That said...you might consider decreasing the fillfactor for the index (note
will require more space) to see if that helps. Also, you might consider
scheduling a rebuild of the index as a nightly job (off peak hours or after
hours).
HTH
Jerry
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of ws. I haven't
> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Is the table more empty than full? Perhaps the statistics are out of whack.
140,000 rows is a considerable number, but it is not a tremendous amount.
Then again, that statement is relative to your environment. If your table
only has 300,000 rows you are adding 50% to the table.
How large is your database? How much room for growth do you have? SQL
Server might be expanding (growing) the database size during the mass
insert. This could cause reduced performance which would lead to query
timeouts.
How are you adding the rows? Are you performing 140,000 INSERT INTO
statements? Can you look into using BCP/Bulk Insert/DTS to get the data in
to the database? You might find that this option is faster.
Keith
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of ws. I haven't
> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Hey John,
Is the date-time index a clustered index? If not, do you have one, and
if so, what datatype is the index associated with? Tables of this size
really ned a clustered index to help in the retrieval process; a
clustered index should be built on a monotonically increasing value
(like a datetime, assuming that the INSERT is inserting data in a
relatively sequential manner). A common mistake is to use a
uniqueidentifier for a primary key, and using that as a clustered
index, which will lead to fragmentation (because the data is being
inserted out of order).
Sorry; not feeling well today, so my answers may be less than cogent.
However, your first step is to determine if your clustered index is on
thewrong column.
HTH,
Stu|||There is a good body of knowledge on www.msdn.com
Here is what a few minutes of searching churned up:
Try to discover the root cause of why your queries are timing out:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Planning and Creating Indexes
http://www.microsoft.com/technet/pr...s/c0618260.mspx
DBCC SHOWCONTIG will reveal if index fragmentation is an issue and DBCC
INDEXDEFRAG can be periodically issued to help minimize it. Defragmenting is
faster than re-indexing, and based on your description, this application
sounds like it is running in an environment where you don't want the system
down for extended periods of time.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Rather than performing 300 inserts per minute, you can initally write the
records to a tab delimited text file and then bulk insert perhaps every 5
minutes
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...calability.mspx
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of ws. I haven't
> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Thank you for your response.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eit6m%23QxFHA.3740@.tk2msftngp13.phx.gbl...
> Is the table more empty than full? Perhaps the statistics are out of
whack.
> 140,000 rows is a considerable number, but it is not a tremendous amount.
> Then again, that statement is relative to your environment. If your table
> only has 300,000 rows you are adding 50% to the table.
At this point there about 2,300,000 records.
> How large is your database? How much room for growth do you have? SQL
> Server might be expanding (growing) the database size during the mass
> insert. This could cause reduced performance which would lead to query
> timeouts.
Currently its about 23 Meg, but its fair to say that when i created it it
created with a default size with instructions to grow at 10% at a time.
Should i re-allocate this? would that lead to bad organization?
> How are you adding the rows? Are you performing 140,000 INSERT INTO
> statements? Can you look into using BCP/Bulk Insert/DTS to get the data
in
> to the database? You might find that this option is faster.
i am indeed doing a set of inserts, but the speed of insertion is
satisfactory. I'd heard about BCP before; would that have a different impact
on how the indexes were managed?
> --
> Keith
>|||Thank you for responding.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1128010124.506187.10780@.g49g2000cwa.googlegroups.com...
> Hey John,
> Is the date-time index a clustered index? If not, do you have one, and
> if so, what datatype is the index associated with? Tables of this size
> really ned a clustered index to help in the retrieval process; a
> clustered index should be built on a monotonically increasing value
> (like a datetime, assuming that the INSERT is inserting data in a
> relatively sequential manner). A common mistake is to use a
> uniqueidentifier for a primary key, and using that as a clustered
> index, which will lead to fragmentation (because the data is being
> inserted out of order).
The date/time is not clustered, and i did exactly what you indicated was a
common mistake, created a generic primary key that is clustered. Since the
date/time is really how the data is retrieved (always searching last 'n'
units of time) it should be 'appending' the data as it add it. Of course, in
this case newer data goes 'at the end' so its not dissimilar to having it be
that way in a large sense; there is a correlation between the order as
defined by the normal primary key and the order defined by the date/time. I
can see, however, that i have essentially a wasted key since i really only
care about the date/time, i've used two keys where one would have done.|||Thank you for responding. These look like the kind of meaty stuff i should
have read before hand :-) I've always been able to treat the SQL Server as a
black box but this is the largest database app i've created and i'm flying
solo on the tuning and configuration front...
john
"JT" <someone@.microsoft.com> wrote in message
news:u7y2ImRxFHA.1028@.TK2MSFTNGP12.phx.gbl...
> There is a good body of knowledge on www.msdn.com
> Here is what a few minutes of searching churned up:
> Try to discover the root cause of why your queries are timing out:
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/defaul...kb;EN-US;224453
> Planning and Creating Indexes
> http://www.microsoft.com/technet/pr...s/c0618260.mspx
> DBCC SHOWCONTIG will reveal if index fragmentation is an issue and DBCC
> INDEXDEFRAG can be periodically issued to help minimize it. Defragmenting
is
> faster than re-indexing, and based on your description, this application
> sounds like it is running in an environment where you don't want the
system
> down for extended periods of time.
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> Rather than performing 300 inserts per minute, you can initally write the
> records to a tab delimited text file and then bulk insert perhaps every 5
> minutes
> SQL Server 2000 Incremental Bulk Load Case Study
>
http://www.microsoft.com/technet/pr...ncbulkload.mspxed">
> With 140k inserts per day, I don't know what the total row count is, but
> consider vertical paritioning of rows into multiple tables and perhaps
> partitioned views.
> Scalability and Very Large Database (VLDB) Resources
> http://www.microsoft.com/sql/techin...calability.mspx
>
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
the
also
were
haven't
some
causing
>|||Sorry John, let me try to clarify (hang on, it's gonna be rough):
First, a key is not an index; a key is used to identify and enforce
relational validity between tables. The primary key uniquely
identifies a row, and can be used as a foreign key to enforce a
relationship with another table. When you create a primary key on a
table, SQL Server will create an index associated with that key; by
default, that index is clustered (if there is not already a clustered
index on the table).
So what's an index? An index is simply a collection of pointers to the
location of a row within a table. Indexes are used to improve
retreival time, because the SQL Server optimizer should use the index
to quickly identify rows that meet the query requirements. Indexes may
be clustered or nonclustered; only one clustered index can exist on a
table. The cluster status refers to the physical ordering of data
within a page; a simple way of understanding it is that a clustered
index sorts the data by the indexed value and physically remembers that
sort (not really, but the analogy is close).
On a large table with a lot of inserts, you should always use a
clustered index on a monotonically increasing value (like an IDENTITY
integer or a date/time value). This will greatly reduce performance
problems because as data comes in, it always gets appended to the end
of the table, rather than having to be inserted somewhere in the
middle. For example, if you have a clustered index on a LastName
column, and you already have some data like so:
LastName
Ainsworth
Smith
Thomas
West
and you want to insert a row with the name Bice in it, SQL Server has
to split the page holding the data to insert that one record.
Continued page splits can lead to fragmentation. If your clustered
index is on a increasing value (like a datetime variable), fewer page
splits occur.
So, using your scenario, I would create a PRIMARY KEY constraint on the
unique row identifier, and created a clustered index on the datetime
column. Just to be clear, you can have as many nonclustered indexes on
a table as you need, but you can only have one clustered index.
Hope that clarifies.
Stu
i have a table thats getting about 140,000 new records a day from a program
reading data from a socket (about 300 every 3 minutes). in addition to the
primary key it has an 8 byte date/time field thats indexed. there are also
two full-text indexed columns with change tracking and update in background
enabled.
as we started to load the database we noticed after some hours 'insert'
operations began to fail with a timeout and queries began timing out.
When i rebuild the index based on date/time it cleared up and queries were
fast again. This cycle has been going on for a couple of w
s. I haven'thad to do anything to the full-text portion, these problem orbit around the
date/time index.
Does this make sense? It really sounds like the indexes have exceeded some
internal resource and are being added to in an inefficient manner causing
the timeout.
is there a body of knowledge about defining indexes for tables that grow
like this that i should be aware of?
thanks,
john mottJohn,
I have not heard of any issues like this. Maybe a blocking issue?
That said...you might consider decreasing the fillfactor for the index (note
will require more space) to see if that helps. Also, you might consider
scheduling a rebuild of the index as a nightly job (off peak hours or after
hours).
HTH
Jerry
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of w
s. I haven't> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Is the table more empty than full? Perhaps the statistics are out of whack.
140,000 rows is a considerable number, but it is not a tremendous amount.
Then again, that statement is relative to your environment. If your table
only has 300,000 rows you are adding 50% to the table.
How large is your database? How much room for growth do you have? SQL
Server might be expanding (growing) the database size during the mass
insert. This could cause reduced performance which would lead to query
timeouts.
How are you adding the rows? Are you performing 140,000 INSERT INTO
statements? Can you look into using BCP/Bulk Insert/DTS to get the data in
to the database? You might find that this option is faster.
Keith
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of w
s. I haven't> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Hey John,
Is the date-time index a clustered index? If not, do you have one, and
if so, what datatype is the index associated with? Tables of this size
really ned a clustered index to help in the retrieval process; a
clustered index should be built on a monotonically increasing value
(like a datetime, assuming that the INSERT is inserting data in a
relatively sequential manner). A common mistake is to use a
uniqueidentifier for a primary key, and using that as a clustered
index, which will lead to fragmentation (because the data is being
inserted out of order).
Sorry; not feeling well today, so my answers may be less than cogent.
However, your first step is to determine if your clustered index is on
thewrong column.
HTH,
Stu|||There is a good body of knowledge on www.msdn.com
Here is what a few minutes of searching churned up:
Try to discover the root cause of why your queries are timing out:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Planning and Creating Indexes
http://www.microsoft.com/technet/pr...s/c0618260.mspx
DBCC SHOWCONTIG will reveal if index fragmentation is an issue and DBCC
INDEXDEFRAG can be periodically issued to help minimize it. Defragmenting is
faster than re-indexing, and based on your description, this application
sounds like it is running in an environment where you don't want the system
down for extended periods of time.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Rather than performing 300 inserts per minute, you can initally write the
records to a tab delimited text file and then bulk insert perhaps every 5
minutes
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...calability.mspx
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of w
s. I haven't> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Thank you for your response.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eit6m%23QxFHA.3740@.tk2msftngp13.phx.gbl...
> Is the table more empty than full? Perhaps the statistics are out of
whack.
> 140,000 rows is a considerable number, but it is not a tremendous amount.
> Then again, that statement is relative to your environment. If your table
> only has 300,000 rows you are adding 50% to the table.
At this point there about 2,300,000 records.
> How large is your database? How much room for growth do you have? SQL
> Server might be expanding (growing) the database size during the mass
> insert. This could cause reduced performance which would lead to query
> timeouts.
Currently its about 23 Meg, but its fair to say that when i created it it
created with a default size with instructions to grow at 10% at a time.
Should i re-allocate this? would that lead to bad organization?
> How are you adding the rows? Are you performing 140,000 INSERT INTO
> statements? Can you look into using BCP/Bulk Insert/DTS to get the data
in
> to the database? You might find that this option is faster.
i am indeed doing a set of inserts, but the speed of insertion is
satisfactory. I'd heard about BCP before; would that have a different impact
on how the indexes were managed?
> --
> Keith
>|||Thank you for responding.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1128010124.506187.10780@.g49g2000cwa.googlegroups.com...
> Hey John,
> Is the date-time index a clustered index? If not, do you have one, and
> if so, what datatype is the index associated with? Tables of this size
> really ned a clustered index to help in the retrieval process; a
> clustered index should be built on a monotonically increasing value
> (like a datetime, assuming that the INSERT is inserting data in a
> relatively sequential manner). A common mistake is to use a
> uniqueidentifier for a primary key, and using that as a clustered
> index, which will lead to fragmentation (because the data is being
> inserted out of order).
The date/time is not clustered, and i did exactly what you indicated was a
common mistake, created a generic primary key that is clustered. Since the
date/time is really how the data is retrieved (always searching last 'n'
units of time) it should be 'appending' the data as it add it. Of course, in
this case newer data goes 'at the end' so its not dissimilar to having it be
that way in a large sense; there is a correlation between the order as
defined by the normal primary key and the order defined by the date/time. I
can see, however, that i have essentially a wasted key since i really only
care about the date/time, i've used two keys where one would have done.|||Thank you for responding. These look like the kind of meaty stuff i should
have read before hand :-) I've always been able to treat the SQL Server as a
black box but this is the largest database app i've created and i'm flying
solo on the tuning and configuration front...
john
"JT" <someone@.microsoft.com> wrote in message
news:u7y2ImRxFHA.1028@.TK2MSFTNGP12.phx.gbl...
> There is a good body of knowledge on www.msdn.com
> Here is what a few minutes of searching churned up:
> Try to discover the root cause of why your queries are timing out:
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/defaul...kb;EN-US;224453
> Planning and Creating Indexes
> http://www.microsoft.com/technet/pr...s/c0618260.mspx
> DBCC SHOWCONTIG will reveal if index fragmentation is an issue and DBCC
> INDEXDEFRAG can be periodically issued to help minimize it. Defragmenting
is
> faster than re-indexing, and based on your description, this application
> sounds like it is running in an environment where you don't want the
system
> down for extended periods of time.
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> Rather than performing 300 inserts per minute, you can initally write the
> records to a tab delimited text file and then bulk insert perhaps every 5
> minutes
> SQL Server 2000 Incremental Bulk Load Case Study
>
http://www.microsoft.com/technet/pr...ncbulkload.mspxed">
> With 140k inserts per day, I don't know what the total row count is, but
> consider vertical paritioning of rows into multiple tables and perhaps
> partitioned views.
> Scalability and Very Large Database (VLDB) Resources
> http://www.microsoft.com/sql/techin...calability.mspx
>
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
the
also
were
haven't
some
causing
>|||Sorry John, let me try to clarify (hang on, it's gonna be rough):
First, a key is not an index; a key is used to identify and enforce
relational validity between tables. The primary key uniquely
identifies a row, and can be used as a foreign key to enforce a
relationship with another table. When you create a primary key on a
table, SQL Server will create an index associated with that key; by
default, that index is clustered (if there is not already a clustered
index on the table).
So what's an index? An index is simply a collection of pointers to the
location of a row within a table. Indexes are used to improve
retreival time, because the SQL Server optimizer should use the index
to quickly identify rows that meet the query requirements. Indexes may
be clustered or nonclustered; only one clustered index can exist on a
table. The cluster status refers to the physical ordering of data
within a page; a simple way of understanding it is that a clustered
index sorts the data by the indexed value and physically remembers that
sort (not really, but the analogy is close).
On a large table with a lot of inserts, you should always use a
clustered index on a monotonically increasing value (like an IDENTITY
integer or a date/time value). This will greatly reduce performance
problems because as data comes in, it always gets appended to the end
of the table, rather than having to be inserted somewhere in the
middle. For example, if you have a clustered index on a LastName
column, and you already have some data like so:
LastName
Ainsworth
Smith
Thomas
West
and you want to insert a row with the name Bice in it, SQL Server has
to split the page holding the data to insert that one record.
Continued page splits can lead to fragmentation. If your clustered
index is on a increasing value (like a datetime variable), fewer page
splits occur.
So, using your scenario, I would create a PRIMARY KEY constraint on the
unique row identifier, and created a clustered index on the datetime
column. Just to be clear, you can have as many nonclustered indexes on
a table as you need, but you can only have one clustered index.
Hope that clarifies.
Stu
Saturday, February 25, 2012
ADD INDEX in SQL server Mobile
new to MS SQL
please help.
How do you add an Index in SQL server Mobile ?
I tried
ALTER TABLE customers ADD INDEX (CUSTOMER_NAME)
did not work
ALTER TABLE customers ADD INDEX custname (CUSTOMER_NAME)
did not work
ALTER TABLE customers ADD custname INDEX (CUSTOMER_NAME)
did not work
is ADD INDEX supported in mobile?
is there a complete sql syntax reference for mobile?
BTW I'm using Sql Mobile query analyser directy on PDA
You can use the t-SQL query for 'CREATE INDEX'
The syntax is CREATE [UNIQUE] [NONCLUSTERED] INDEX index_name ON table_name (column_name [ASC|DESC][,…n])
Search for "CREATE INDEX (SQL Server Mobile)" on www.msdn2.microsoft.com.
Thanks
Pragya
|||
Fantastic!
It works.
I used normal sql command on pda, and works..
and I spent 2 hours on this with no results from MSDN,
where would I be without your help!
BTW there is no www in front of msdn2..the link won't work
add index
I know someone will refer me to online books, but I did a search on the
microsoft site and can't get to it.
I know the general format for adding an index is:
Alter Table tablename ADD INDEX indexname, type, FieldName
The thing is, I'm not sure what to put in for type? Do I use quotes? Can
someone actually give me an URL to an example that will show?
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1HockeyFan via webservertalk.com wrote:
> I know someone will refer me to online books, but I did a search on
> the microsoft site and can't get to it.
> I know the general format for adding an index is:
> Alter Table tablename ADD INDEX indexname, type, FieldName
> The thing is, I'm not sure what to put in for type? Do I use quotes?
> Can someone actually give me an URL to an example that will show?
Search for CREATE INDEX in BOL.
examples:
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
HTH,
Stijn Verrept|||>I know someone will refer me to online books, but I did a search on the
> microsoft site and can't get to it.
SQL 2000:
http://msdn.microsoft.com/library/e...portal_7ap1.asp
SQL 2005:
http://msdn.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx
> Alter Table tablename ADD INDEX indexname, type, FieldName
Actually, I'm not sure where you got that syntax, it's...
CREATE [UNIQUE] [NON|CLUSTERED] INDEX
indexname
ON tablename (columnName [DESC] [, ...]);
SQL 2000:
http://msdn.microsoft.com/library/e...create_64l4.asp
SQL 2005:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
microsoft site and can't get to it.
I know the general format for adding an index is:
Alter Table tablename ADD INDEX indexname, type, FieldName
The thing is, I'm not sure what to put in for type? Do I use quotes? Can
someone actually give me an URL to an example that will show?
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1HockeyFan via webservertalk.com wrote:
> I know someone will refer me to online books, but I did a search on
> the microsoft site and can't get to it.
> I know the general format for adding an index is:
> Alter Table tablename ADD INDEX indexname, type, FieldName
> The thing is, I'm not sure what to put in for type? Do I use quotes?
> Can someone actually give me an URL to an example that will show?
Search for CREATE INDEX in BOL.
examples:
CREATE INDEX emp_order_ind
ON order_emp (orderID, employeeID)
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
HTH,
Stijn Verrept|||>I know someone will refer me to online books, but I did a search on the
> microsoft site and can't get to it.
SQL 2000:
http://msdn.microsoft.com/library/e...portal_7ap1.asp
SQL 2005:
http://msdn.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx
> Alter Table tablename ADD INDEX indexname, type, FieldName
Actually, I'm not sure where you got that syntax, it's...
CREATE [UNIQUE] [NON|CLUSTERED] INDEX
indexname
ON tablename (columnName [DESC] [, ...]);
SQL 2000:
http://msdn.microsoft.com/library/e...create_64l4.asp
SQL 2005:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
ADD FILE __cannot be reused until after the next BACKUP LOG operation
Hi all,
I have a database containing a full text index, I am trying to add a file to
a filegroup but an exception occurred while execution.
the error:
File 'C:\FullTextCatalogs\CMS_sanayaFT_data.ndf' cannot be reused until
after the next BACKUP LOG operation.
File 'CMS_sanayaFT_data' cannot be reused until after the next BACKUP LOG
operation. (Microsoft SQL Server, Error: 1833)
Thanks in advanced
Backup the log and try to add the file.
relevantNoise - dedicated to mining blogs for business intelligence.
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
"yaser" <yaser.abu-khudier@.hotmail.com> wrote in message
news:%23LEiVZM3HHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi all,
>
> I have a database containing a full text index, I am trying to add a file
> to a filegroup but an exception occurred while execution.
>
> the error:
>
> File 'C:\FullTextCatalogs\CMS_sanayaFT_data.ndf' cannot be reused until
> after the next BACKUP LOG operation.
> File 'CMS_sanayaFT_data' cannot be reused until after the next BACKUP LOG
> operation. (Microsoft SQL Server, Error: 1833)
>
> Thanks in advanced
>
|||thanks Hilary, the issue solved by taking a full backup then taking a log
back up.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23ziAf8h3HHA.5984@.TK2MSFTNGP04.phx.gbl...
> Backup the log and try to add the file.
> --
> relevantNoise - dedicated to mining blogs for business intelligence.
> 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
> "yaser" <yaser.abu-khudier@.hotmail.com> wrote in message
> news:%23LEiVZM3HHA.748@.TK2MSFTNGP04.phx.gbl...
>
I have a database containing a full text index, I am trying to add a file to
a filegroup but an exception occurred while execution.
the error:
File 'C:\FullTextCatalogs\CMS_sanayaFT_data.ndf' cannot be reused until
after the next BACKUP LOG operation.
File 'CMS_sanayaFT_data' cannot be reused until after the next BACKUP LOG
operation. (Microsoft SQL Server, Error: 1833)
Thanks in advanced
Backup the log and try to add the file.
relevantNoise - dedicated to mining blogs for business intelligence.
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
"yaser" <yaser.abu-khudier@.hotmail.com> wrote in message
news:%23LEiVZM3HHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi all,
>
> I have a database containing a full text index, I am trying to add a file
> to a filegroup but an exception occurred while execution.
>
> the error:
>
> File 'C:\FullTextCatalogs\CMS_sanayaFT_data.ndf' cannot be reused until
> after the next BACKUP LOG operation.
> File 'CMS_sanayaFT_data' cannot be reused until after the next BACKUP LOG
> operation. (Microsoft SQL Server, Error: 1833)
>
> Thanks in advanced
>
|||thanks Hilary, the issue solved by taking a full backup then taking a log
back up.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23ziAf8h3HHA.5984@.TK2MSFTNGP04.phx.gbl...
> Backup the log and try to add the file.
> --
> relevantNoise - dedicated to mining blogs for business intelligence.
> 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
> "yaser" <yaser.abu-khudier@.hotmail.com> wrote in message
> news:%23LEiVZM3HHA.748@.TK2MSFTNGP04.phx.gbl...
>
Thursday, February 16, 2012
Add a new index in to my table
I wanted to inprove my database performance to add a new index in my table.
I just wanted to know are there any impact and should I add index without
any activities on the database?
Thanks millions for the informaiton,You can add an index when people are using the system, but be aware that the
entire table will be read to create the index, so they may see a performance
impact...
If you CAN wait until the evening, you probably should. but it is not the
end of the world.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:99EEC771-1D7C-45EE-A086-70C199A7DA2E@.microsoft.com...
>I wanted to inprove my database performance to add a new index in my table.
> I just wanted to know are there any impact and should I add index without
> any activities on the database?
> Thanks millions for the informaiton,
I just wanted to know are there any impact and should I add index without
any activities on the database?
Thanks millions for the informaiton,You can add an index when people are using the system, but be aware that the
entire table will be read to create the index, so they may see a performance
impact...
If you CAN wait until the evening, you probably should. but it is not the
end of the world.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:99EEC771-1D7C-45EE-A086-70C199A7DA2E@.microsoft.com...
>I wanted to inprove my database performance to add a new index in my table.
> I just wanted to know are there any impact and should I add index without
> any activities on the database?
> Thanks millions for the informaiton,
Monday, February 13, 2012
Add a clustered index in a published table
Hi,
I am using SQL 2K standard and have created a transactional replication
between two sql servers using push subscritpion. It is working fine. Now I
want to add a clustered index in one of the published tables and I get an
error message saying that "Cannot alter the table because it is being
published for replication". Based on this message, I assume I will have to
disable all publishing and distribution, add the clustered index and then
redo the publishing and subscriptions for all the tables from scratch again.
Am I right? Is there another way to do this?
Any help on this will be very much appreciated.
Wingman
Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
are you doing anything else in the same batch eg changing the PK?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick response.
Here are the steps I did.
1. Open the existing publishing database in the SQL enterprise manager.
2. Select 'Design' of the table which is a published table in the
publication.
3. Select the primiary key
4. Check the box of the clustered index.
5. Close the design window and click 'save' to save the changes.
6. Then the error occurs.
There are no other batches involved and I was just doing the above. I hope
this clarifies any questions for you.
Wing
"Paul Ibison" wrote:
> Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
> are you doing anything else in the same batch eg changing the PK?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I can't test this right now, but I'd try to run this command:
CREATE CLUSTERED INDEX index_name ON table ( column_name )
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yep, I did and it ran succussfully without any problem. The clustered index
is created. Thanks for this suggestion.
Can you explain why I can't do this in EM? Is it just the limitation of
using this type of interface?
Also, do I need to do the 'create clustered index...' in the subscription
server or the push replication will take care of it? In the future, if I
need to add non-clustered index, will this be the way to do it?
Wingman
"Paul Ibison" wrote:
> I can't test this right now, but I'd try to run this command:
> CREATE CLUSTERED INDEX index_name ON table ( column_name )
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you run profiler when applying changes to a table using the Enterprise
Manager interface you can usually determine the cause of tis type of problem.
In this case the table was probably being dropped in the background. The same
issue applies in SQL Server 2005 where if you select to replicate DDL
changes, the Management Studio will fail whereas ALTER TABLE will succeed.
If you want this applied to the subscribers you have to apply initiate this
yourself. You could use sp_addscriptexec to use the replication setup or just
apply it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry, don't mean to keep the discussion going that long but I have a few
follow-up questions regarding sp_addscriptexec.
1. Are there any benefits and differences using sp_addscriptexec compared
to manually executing the script in the subscriber computer?
2. If I use the sp_addscriptexec command to move the scritpt to the
subscriber(s), when or under what situation will the script be excecuted and
how often?
3. If I decide that I no longer need those scripts after using
sp_addscriptexec, how do I remove it from the subscriber computer?
Wingman
"Paul Ibison" wrote:
> If you run profiler when applying changes to a table using the Enterprise
> Manager interface you can usually determine the cause of tis type of problem.
> In this case the table was probably being dropped in the background. The same
> issue applies in SQL Server 2005 where if you select to replicate DDL
> changes, the Management Studio will fail whereas ALTER TABLE will succeed.
> If you want this applied to the subscribers you have to apply initiate this
> yourself. You could use sp_addscriptexec to use the replication setup or just
> apply it manually.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||There are no extra benefits of sp_addscriptexec in terms of running the
script. However, it may be the case that you have dozens of subscribers, or
perhaps some are offline. In this was it gives you convenience and assures
you of getting the script to the subscribers. They'll receive the script when
they synchronize, and the script won't be run more than once and won't be
retained on the subscriber's computer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, thanks.
When I go to the subscription computer and check all replicated tables, I
notice that none of the primary key is defined but the index key for the
primary key is there. Why is it?
Also, when I checked the snapshot properties of the pubished article
defaults in EM, the non-clustered index check box is grayed out. Is there a
way I can activate it so I can uncheck it because I don't want the
non-clustered index to be replicated?
Wingman
"Paul Ibison" wrote:
> There are no extra benefits of sp_addscriptexec in terms of running the
> script. However, it may be the case that you have dozens of subscribers, or
> perhaps some are offline. In this was it gives you convenience and assures
> you of getting the script to the subscribers. They'll receive the script when
> they synchronize, and the script won't be run more than once and won't be
> retained on the subscriber's computer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||PKs are replicated as unique indexes by default in transactional replication.
If you enable the DRI option checkbox on the snapshot tab of the article
properties, the PK will be recreated on teh subscriber. For the chackboxes on
this tab, some are related to others, so try checking/unchecking others to
see if you can enable the nonclustered option. However not all combinations
are supported using this method and in some cases you have to resort to
scripting the replication instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I am using SQL 2K standard and have created a transactional replication
between two sql servers using push subscritpion. It is working fine. Now I
want to add a clustered index in one of the published tables and I get an
error message saying that "Cannot alter the table because it is being
published for replication". Based on this message, I assume I will have to
disable all publishing and distribution, add the clustered index and then
redo the publishing and subscriptions for all the tables from scratch again.
Am I right? Is there another way to do this?
Any help on this will be very much appreciated.
Wingman
Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
are you doing anything else in the same batch eg changing the PK?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick response.
Here are the steps I did.
1. Open the existing publishing database in the SQL enterprise manager.
2. Select 'Design' of the table which is a published table in the
publication.
3. Select the primiary key
4. Check the box of the clustered index.
5. Close the design window and click 'save' to save the changes.
6. Then the error occurs.
There are no other batches involved and I was just doing the above. I hope
this clarifies any questions for you.
Wing
"Paul Ibison" wrote:
> Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
> are you doing anything else in the same batch eg changing the PK?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I can't test this right now, but I'd try to run this command:
CREATE CLUSTERED INDEX index_name ON table ( column_name )
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yep, I did and it ran succussfully without any problem. The clustered index
is created. Thanks for this suggestion.
Can you explain why I can't do this in EM? Is it just the limitation of
using this type of interface?
Also, do I need to do the 'create clustered index...' in the subscription
server or the push replication will take care of it? In the future, if I
need to add non-clustered index, will this be the way to do it?
Wingman
"Paul Ibison" wrote:
> I can't test this right now, but I'd try to run this command:
> CREATE CLUSTERED INDEX index_name ON table ( column_name )
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you run profiler when applying changes to a table using the Enterprise
Manager interface you can usually determine the cause of tis type of problem.
In this case the table was probably being dropped in the background. The same
issue applies in SQL Server 2005 where if you select to replicate DDL
changes, the Management Studio will fail whereas ALTER TABLE will succeed.
If you want this applied to the subscribers you have to apply initiate this
yourself. You could use sp_addscriptexec to use the replication setup or just
apply it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry, don't mean to keep the discussion going that long but I have a few
follow-up questions regarding sp_addscriptexec.
1. Are there any benefits and differences using sp_addscriptexec compared
to manually executing the script in the subscriber computer?
2. If I use the sp_addscriptexec command to move the scritpt to the
subscriber(s), when or under what situation will the script be excecuted and
how often?
3. If I decide that I no longer need those scripts after using
sp_addscriptexec, how do I remove it from the subscriber computer?
Wingman
"Paul Ibison" wrote:
> If you run profiler when applying changes to a table using the Enterprise
> Manager interface you can usually determine the cause of tis type of problem.
> In this case the table was probably being dropped in the background. The same
> issue applies in SQL Server 2005 where if you select to replicate DDL
> changes, the Management Studio will fail whereas ALTER TABLE will succeed.
> If you want this applied to the subscribers you have to apply initiate this
> yourself. You could use sp_addscriptexec to use the replication setup or just
> apply it manually.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||There are no extra benefits of sp_addscriptexec in terms of running the
script. However, it may be the case that you have dozens of subscribers, or
perhaps some are offline. In this was it gives you convenience and assures
you of getting the script to the subscribers. They'll receive the script when
they synchronize, and the script won't be run more than once and won't be
retained on the subscriber's computer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, thanks.
When I go to the subscription computer and check all replicated tables, I
notice that none of the primary key is defined but the index key for the
primary key is there. Why is it?
Also, when I checked the snapshot properties of the pubished article
defaults in EM, the non-clustered index check box is grayed out. Is there a
way I can activate it so I can uncheck it because I don't want the
non-clustered index to be replicated?
Wingman
"Paul Ibison" wrote:
> There are no extra benefits of sp_addscriptexec in terms of running the
> script. However, it may be the case that you have dozens of subscribers, or
> perhaps some are offline. In this was it gives you convenience and assures
> you of getting the script to the subscribers. They'll receive the script when
> they synchronize, and the script won't be run more than once and won't be
> retained on the subscriber's computer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||PKs are replicated as unique indexes by default in transactional replication.
If you enable the DRI option checkbox on the snapshot tab of the article
properties, the PK will be recreated on teh subscriber. For the chackboxes on
this tab, some are related to others, so try checking/unchecking others to
see if you can enable the nonclustered option. However not all combinations
are supported using this method and in some cases you have to resort to
scripting the replication instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)