InfoPath XML importing to SQL 2000

Hi all,

We are having issues in getting a Bulk Upload for an XML which has been created through InfoPath 2007 (and it being accessed on SharePoint 2007).

We have found examples on how to do it with a normal XML but when it is created through InfoPath the schema does not seem to be the same as standard XML and therefore cannot work out the naming convention for importing the scripts.

If anyone has had any experience or suggestions all would be greatly appreciated.

Thanks in advance.

Hello,

If you send me the schema and the data file, I will take a look.

Regards,

Monica Frintu

InfoPath XML importing to SQL 2000

Hi all,

We are having issues in getting a Bulk Upload for an XML which has been created through InfoPath 2007 (and it being accessed on SharePoint 2007).

We have found examples on how to do it with a normal XML but when it is created through InfoPath the schema does not seem to be the same as standard XML and therefore cannot work out the naming convention for importing the scripts.

If anyone has had any experience or suggestions all would be greatly appreciated.

Thanks in advance.

Hello,

If you send me the schema and the data file, I will take a look.

Regards,

Monica Frintu

InfoPath 2007 and endpoint yielding HTTP authentication failed.

I created a HelloWorld like example using InforPath 2007 and endpoint with SQL Server 2005. Very simple example.

I created a stored procedure that took in one parameter, a datetime field. This parameter is then inserted into a db table that has two columns, an id column and a datetime column.

I took the store procedure just discribed to create an endpoint. Cool. The WSDL is showing, I'm feeling good.

I go to InfoPath and make it so that submit a datetime to the webmethod associated with the endpoint. The data table shows one more row has been added and it is the datetime I sent from InfoPath. YEA!! I go to the Event View and see that I have an error message. The message is HTTP authenticaiton failed. [CLIENT: 10.8.2.26]. Bummer!!

This does not make sense to me. I am able to update the targeted database table but I get an exception. I do not like exceptions nor warnings without an explanation. Does anyone have an idea why this is happening?

The endpoint code looks like: <code snippet>

CREATE ENDPOINT [GeraldTesting_endpoint]
AUTHORIZATION
STATE=STARTED
AS HTTP (PATH=N'/testEndPoint',
PORTS = (CLEAR),
AUTHENTICATION = (INTEGRATED),
SITE=N'*',
CLEAR_PORT = 8080,
COMPRESSION=DISABLED)
FOR SOAP (
WEBMETHOD 'testEndPoint'
( NAME=N'[geraldstest].[dbo].[Gerald_Test_Proc]'
, SCHEMA=STANDARD,
FORMAT=ALL_RESULTS),
BATCHES=ENABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]',
SESSIONS=DISABLED, SESSION_TIMEOUT=60,
DATABASE=N'geraldstest', NAMESPACE=N'http://tempuri.org', SCHEMA=STANDARD, CHARACTER_SET=XML)

</code snippet>

I had to give up for the project I am currently working because of time constraints. But in the future, for out-of-the-box functionality for proof-of-concept/prototyping, I would like to use endpoints.

Hi Gerald,

If I understand your situation correctly, your InfoPath is able to connect to the SQL SOAP endpoint and submit the data correctly, but then you are seeing a HTTP authentication failure in the Event View. It sounds like InfoPath itself is not raising any errors or exceptions. Is this Event View part of InfoPath? Or, is this the SQL Errorlog?

I am assuming you've verified that the timestamp of the HTTP authentication failure entry is the same time when you submitted the data with InfoPath. Also, I am assuming that the connection setting to SQL Server in the InfoPath form is done through Web Services instead of a direct SQL connection.

If you are able to retrieve the WSDL document from SQL using InfoPath, the same user will have access to submit data to the endpoint. If the InfoPath form as opened by another user, then you will need to grant the enduser access.

It would be really helpful if you can provide the complete error message and the whether the user has been granted access to the endpoint.

From what you've mentioned, the insert did work, so there's either a major bug in the SQL product where we are incorrectly logging bad messages or else there is some other configuration issue.

Jimmy

InfoPath 2003 - insert data in SQLServer?

Hello,

I created an InfoPath 2003 form, with a datasource from SqlServer 2000. When I try to insert a new record the follow error occurs:

"InfoPath cannot connect to the data source.
Safety settings on this computer prohibit accessing a data source on another domain."

I checked Enabled -"Access data source accross domains" from Security Settings->Miscellaneous, but the error still appears.

Have anyone any idea about this error?

Thanks.

This seems like an InfoPath specific configuration issue. You may want to try re-posting (sorry, I couldn't find a forum to which I could simply move this post) your question to the InfoPath newsgroup at http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.infopath or checking out the InfoPath blog at http://blogs.msdn.com/infopath/default.aspx.

sql

Inexplicable Invalid Syntax error

I created a DB in Access.. then I converted it to work with SQL Server. I have several tables and queries that are working just fine with it.

However, the other day I made a few changes to one of the tables ( Order table), and it seems no matter what kind of query I do with it, it always returns this error:

Invalid syntax near the keyword 'Order'

The query is IDENTICAL to about 10 others like it.. but no matter what kind of query I do to this Order table.. it always returns the same error. I've stepped through it, looked at the query builder, and there's nothing wrong with the format. Here is what the code looks like..

strSQL = "SELECT OrderId FROM Order WHERE SessionId = '"
+ "testfudgeddata" + "' ";
cm.CommandText = strSQL;
dr = cm.ExecuteReader();

while( dr.Read() )
{
orderId = dr.GetInt32(0);
}

It always fails when ExecuteReader() gets called.. with the same error. The syntax is exactly the same as many other queries.. and I even typed in 'testfudgeddata' into the db directly. I am thinking this problem might be related to some proxy-type information that I need to clean out due to the change of the name of this table since it was created in Access.. anyone ever see anything like this?

BrentORDER is a reserved word in SQL Server. change the table name (!)|||Try placing the table name in square brackets.|||AAAAK!

It seems every time I ask for help, I figure out the problem right after. I ran Query Analyzer and was a little confused why my Order table text was blue.. and the other tables were black.

Order is KEYWORD!! DUH!!

Anyways, it's a little funny I guess.. hope you can all get a good laugh from my misery.. heh|||Thanks for the reply guys.. looks like all 3 of us posted at exactly the same time. ;)sql

Indirect Configuration problem

I'm having a problem with indirect configurations on my client's machine.

I've created a package that uses an xml configuration file (.dtsconfig file) that is pointed to by an environment variable. I have this package running on my machine using Windows XP and on my test machine that uses Windows Server 2003. When I set it up on my client's test machine also running Windows Server 2003 the package doesn't recognize the configuration and tries to use the values that it was originally programmed with on my development machine.

I installed the packages the same on both test servers including adding the appropriate environment variables. Is it possible there is something wrong with the environment? I haven't checked the various service pack levels on the two machines. I know that I keep my test machine up to date. I'm not sure about my client. Are there any known issues like this under specific service packs? Or am I just probably missing something glaringly obvious?

Thanks,

Jeff

Having an xml config file point to an environment variable is somewhat redundant, you could just have the environment varialbe as a configuration. Regardless, I've had issues with packages not recognizing new environment variables before. I'd try restarting the machine first.

|||

That was it. Thank you.

Jeff

sql

Indexing Results of Stored Proc (or new table created by one)

Hi,

I am using data from multiple databases and/or queries. It would greatly
simplify and speed things up if I could use CONTAINS in processing the
results. However, "CONTAINS" requires the data to be indexed. Due to the
amount of processing, I think it would be faster even if I had to re-index
every time.

For example, I would like to do something like this (simplified to
illustrate the desired functionality... This should show all of the words
from one table that are not contained in their current or inflectional forms
within another table):

SELECT W1.Content
FROM
(SELECT Word AS Content
FROM MyTable) W1
LEFT OUTER JOIN
(SELECT Phrase AS Content
FROM MyOtherTable) W2
ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
WHERE W2.Content IS NULL

Can the results of a procedure be indexed? If not, can I drop the results
into a new table and trigger an automatic index of it, pausing the procedure
until the indexing is done?

Or, it there another way?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simon|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:sRhdc.1459$k05.510@.newsread2.news.pas.earthli nk.net...
> Hi,
> I am using data from multiple databases and/or queries. It would greatly
> simplify and speed things up if I could use CONTAINS in processing the
> results. However, "CONTAINS" requires the data to be indexed. Due to the
> amount of processing, I think it would be faster even if I had to re-index
> every time.
> For example, I would like to do something like this (simplified to
> illustrate the desired functionality... This should show all of the words
> from one table that are not contained in their current or inflectional
forms
> within another table):
> SELECT W1.Content
> FROM
> (SELECT Word AS Content
> FROM MyTable) W1
> LEFT OUTER JOIN
> (SELECT Phrase AS Content
> FROM MyOtherTable) W2
> ON W2.Content CONTAINS(INFLECTIONAL, W1.Content)
> WHERE W2.Content IS NULL
> Can the results of a procedure be indexed? If not, can I drop the results
> into a new table and trigger an automatic index of it, pausing the
procedure
> until the indexing is done?
> Or, it there another way?
> Thanks!

You may be able to use CONTAINSTABLE() instead, as it returns a table, which
you can then join on. But I'm not really familiar with it -
microsoft.public.sqlserver.fulltext may be a better place to ask.

As a general answer, you could start fulltext indexing on a table from
within a stored procedure, but that's likely to be very slow and there may
be security implications as well.

Simonsql

Indexing for a Dimensional Model

Hello,
I've created a dimensional model (star schema) with a central fact table wit
h a bunch of Foreign Keys to dimension tables. In Oracle there is a special
way that you index these FKs to improve performance. Is there anything lik
e that with SQL Server or i
s there no need to index the FKs in the fact table.
If you could even just point me to some information on this subject I would
appreciate it.
Thank you!
Mark McCoidTypically, you place indexes on the FK's in your fact table, since your app
will usually access the fact table via the dimension tables.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"markmccoid" <markm@.mactive.com> wrote in message
news:F54A999E-D9F9-4A70-BF27-BE4D29B7565D@.microsoft.com...
Hello,
I've created a dimensional model (star schema) with a central fact table
with a bunch of Foreign Keys to dimension tables. In Oracle there is a
special way that you index these FKs to improve performance. Is there
anything like that with SQL Server or is there no need to index the FKs in
the fact table.
If you could even just point me to some information on this subject I would
appreciate it.
Thank you!
Mark McCoid|||markmccoid
Yes , it is a good idea to create an index on FK column to get a performance
benefit.
For more info lookup CREATE INDEX in the BOL.
"markmccoid" <markm@.mactive.com> wrote in message
news:F54A999E-D9F9-4A70-BF27-BE4D29B7565D@.microsoft.com...
> Hello,
> I've created a dimensional model (star schema) with a central fact table
with a bunch of Foreign Keys to dimension tables. In Oracle there is a
special way that you index these FKs to improve performance. Is there
anything like that with SQL Server or is there no need to index the FKs in
the fact table.
> If you could even just point me to some information on this subject I
would appreciate it.
> Thank you!
> Mark McCoid

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?
Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced i
n
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustere
d)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the larges
t
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I ca
n
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

Indexing a table

I've created a new table and added data to it. Now I want to index a given column. Will simply creating and saving the index index the column or do I need to do something else to create the actual index?

Barkingdog

you must run a create index tsql statement to actially create an index

however you can use the enterprise manager or the sql server management studio

the create the index using the GUI

|||

Once the index is created it will be maintained by itself, you don′t need to refresh the index. ON the other side, index can get fragmented leading to throttle your response time on the server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Indexing a table

I've created a new table and added data to it. Now I want to index a given column. Will simply creating and saving the index index the column or do I need to do something else to create the actual index?

Barkingdog

Basically all you have to do is expand the table right click Indexes select New Index Select your type clustered or non-clustered and add the index key column field. There are many more option depending on what your needs are. How this helped.

INDEXES: Is this correct

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
--
http://www.aspfaq.com/
(Reverse address to reply.)

INDEXES: Is this correct

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)

INDEXES: Is this correct

I have a temp table and created two indexes on it.
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
> All I want to know is would this code work?
Well, did you try it? What happened?
http://www.aspfaq.com/
(Reverse address to reply.)

indexes urgent please

HI
I created clustered index on 115 million records which runs 6 hours after i
created index now it is running for 16 minutes which is fine.
if i run that query statement(update statement) 1st time it will take 16
minutes and second time it will take 50 minutes
and third time it will take 1hour 30 minutes.this is weired can any one
please let me know what to do
again i am doing rebuilding and updatestats each time.
Thanks in advance
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
This is probably the wrong group for this, however you need to make sure
that you are not updating a clustered key as this will cause movement and/or
page splits in your clustered index. There may also be lots of
locking/blocking going on depending on how many rows you are updating.
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
"pardhiveswar via droptable.com" <u8956@.uwe> wrote in message
news:736fe741cfaeb@.uwe...
> HI
> I created clustered index on 115 million records which runs 6 hours after
> i
> created index now it is running for 16 minutes which is fine.
> if i run that query statement(update statement) 1st time it will take 16
> minutes and second time it will take 50 minutes
> and third time it will take 1hour 30 minutes.this is weired can any one
> please let me know what to do
> again i am doing rebuilding and updatestats each time.
> Thanks in advance
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
>

Indexes on datasets created froma Union All task

Hi

I have a package that takes data from 3 different sources, unions them all together and then performs a lookup against a tabel on a different server. The 3 sources of data creates a dataset with quite a large volume of rows (approx 100,000) and the look up takes an age to complete.

Is there anyway of adding an index of sorts to the dataset created by the union to speed the process up? I guess i could output the union all to a temp table and look up against this but wondered if there was another way

thanks

scott

100,000 rows is not much in my experience and fully-cached lookups are very fast once the reference data is loaded. I assume you're not running the lookup in cache mode?

If you are running in non-cached mode, I would think you would be focusing your indexing efforts on the reference table, not the rows in the pipeline. And is that really necessary?

I don't follow you on the temp table idea. It sounds like the data from the union all is the reference data. I can't visualize the scenario.

If the lookup is cached, are you sure the slowdown is in the Lookup and not downstream? A component cannot output rows if the next component is not ready to receive them. Try replacing the everything below the Lookup with a Union All and no destination. It should scream.
|||

Hi Jay

thanks for the reply

I made a mistake it is only 10,000 rows so even less!

I have 3 sources going into a union all which takes about a 2 seconds to 'go green'. next the Union and the look up go yellow and it takes about 90 seconds to complete. After this I simply have 2 destinations - success from look up goes to one tabel and error output goes to another table. Once the look up is complete the population of the 2 destination tables takes the blink of an eye.

I am not sure what you mean by caching though?

cheers

scott

|||

On the Advanced tab of the Lookup component, make sure that "Enable Memory Restriction" is not checked. Also, make sure that you are not using the table selection method in the lookup - you should specify a SQL statement that returns the smallest number of columns possible. So ideally you would return two columns, the value to match on, and the value to return.

How many rows in your lookup?

|||

Racsco wrote:

I have 3 sources going into a union all which takes about a 2 seconds to 'go green'. next the Union and the look up go yellow and it takes about 90 seconds to complete. After this I simply have 2 destinations - success from look up goes to one tabel and error output goes to another table. Once the look up is complete the population of the 2 destination tables takes the blink of an eye.

90 seconds for 10,000 rows is 111 rows/second. That could be indicative of not caching. In this situation, the Lookup would issue 10,000 select statements to your reference table. When caching is enabled, the lookup should be almost as fast as the union.

I want to make sure you understand my point about the downstream components. If one of those destinations is slow (i.e. not using fast load), then the lookup and the union will appear slow as they wait for the destination to accept new rows.
|||

Hi John

I checked the advanced tab and enable memory restriction was checked. I unchecked it and ran it again and it was rapid to say the least. However out of the 10000 rows only 770 matched the lookup condition and the rest errored. I tested it again and checked the enable memory restriction box, ran it and all passed the look up condition

any ideas as to why?

If i run the look up as a join in query analyser all 10000 rows pass the look up condition

|||

Hi Jay

both destinations are set to table or view fast load and both are tables. Please see my previous reply above your most recent post as i got it going much faster but now have a problem with the look up

cheers

scott

|||

Racsco wrote:

Hi John

I checked the advanced tab and enable memory restriction was checked. I unchecked it and ran it again and it was rapid to say the least. However out of the 10000 rows only 770 matched the lookup condition and the rest errored. I tested it again and checked the enable memory restriction box, ran it and all passed the look up condition

any ideas as to why?

If i run the look up as a join in query analyser all 10000 rows pass the look up condition

I bet it's the collation. SQL Server performs case-insensitive comparisons by default. The lookup is case-sensitive. The would enable SQL Server to make matches that the lookup can't. You can try converting everything to uppercase.

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?
Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>
|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:

> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>
|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:

> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know i
s
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:

> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> is
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...[vbcol=seagreen]
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:

> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at th
e
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they'
re
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table
on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table witho
ut
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
>
>