Doubts On Indexing.

Sep 5, 2007

Hi,

set @DN=''

set @DN=@DN+'AA9999'
select @ID = ID from TableA where status='A' and ColumnA like '%'+@DN
ColumnA is NonClusteredIndex

When I use = symbol it goes for Index Seek (like below query) otherwise its going for table scan. Could anyone advice me which index need to be used for getting 'Index Seek' when I use like Operator. Basically TableA has millions of records.

select @ID = ID from TableA where status='A' and Code = 'AA9999'

Regards
JMR

View 2 Replies

FLAT File Indexing Vs RDBMS Indexing

Sep 22, 2006

Hi

I want to know is a flat file faster than a RDBMS for indexing for example a search engine indexing would a flat file be better in terms of performance, scalability etc than a RDBMS?

View 14 Replies View Related

Online Re-indexing Vs Offline Re-indexing

Sep 10, 2007

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it��s performing faster than offline re-indexing. Could you please validate if it��s supposed to do be this way? I always thought offline should be faster than online.

Thanks,
Ritesh

View 5 Replies View Related

BCP - Some Doubts

Jul 29, 1999

Hi,

I am currently using SQL Server version 6.5 and am trying to execute the BCP command
from the command prompt to update my database. Right now, if the tables doesn't have
any primary key constraints, the BCP works fine and inserts all the rows to the database.
Suppose the table has some primary keys and the datafile to the BCP has some duplicate
records in it. It says, the following

Starting copy...
Msg 2627, Level 14, State 1:
Server 'DHRUVA', Line 1:
Violation of PRIMARY KEY constraint 'PK_docket_booking4_1__10':
Attempt to insert duplicate key in object 'docket_booking4'.

It does not insert any rows to the database. Is there anyway I can overcome this error and
proceed on with the BCP to copy data. THe datafile which I give will have duplicate records.

regards
Ramesh Seetharaman.

View 3 Replies View Related

Some Doubts

Apr 24, 2007

hi all,Please find below my queries, and help me to understand things..1. How to find the port number of a specific SQLServer instance?(I have heard that the different instance of the SQLServer runs in different port number)2. How to create a new instance for an existing SQLServer database? and how would i connect to it with my JDBC Connection string which usually reads as,jdbc:jtds:sqlserver://<Host_Name>:<Port_Number>/<DataBase_Name>Thanks in advanceShefu

View 7 Replies View Related

Some Doubts

Apr 13, 2008

i. If we have files and filegroups in place we have improved performance on backup and restore .Can we have any other improved perfortmance ?
2.If we are rebuilding or reorganising indexes on a table,which one will the degrades the performance more on the production server and which sits more on the transaction log.
3 how can we check the tableid in the database.

View 5 Replies View Related

Some Doubts

Nov 19, 2007

Which SP can be used to set or clear a stored procedure for automatic execution?

Which TSQL DDL statement cannot be at the database scope?
a)Create Database
b)Alter Database
c)Drop Contract
d)Create Certificate

What is schema binding?

Which Tsql extension in sql 2005 allows SP's to be exposed as Web Service?

View 2 Replies View Related

Some Doubts

Mar 30, 2008

Hello i attended one interview . I have some doubts

1.) Indexes decreases the performance for insert ,UPDATE commands.? what will u do to increase the performance with these statements.
2.).tell me the 5 steps do u consider while writing a stored procedure to increase the performance.?
4).Is truncate a DDL or DML operation ?
5.) How to delete duplicate rows from a table?
6).table1 has an identity coluimn with 5 rows.
delete opearation is performed on table 1.If user enters a new row what wiil be identity column no 1 or 6?
table2 has an identity coluimn with 5 rows.
truncate opearation is performed. .If user enters a new row what wiil be identity column no 1 or 6?
7.) when do we go for sub queries and when do we go for joins ?
8) could me tell the difference between sp and function.?

waiting for ur replies.
SRIPATHI

View 3 Replies View Related

Some Basic Doubts

Oct 9, 2007

Hi,
I am totally new to sql server and have got a few doubts.I couldnt find it in earlier posts.Sorry if it is a repetition

*Can we get all the table names of a database by Query?
*How to create a similar copy of a table by using AS..does this work in sql server 2000?(tried but showing error)
*can we modify the column properties(like ensuring NOT NULL) after the table is created?

Thanks in advance

View 5 Replies View Related

SSIS Doubts

Feb 21, 2006

I am having this question in my mind from when I started using SSIS.. In a package I have an OleDB source and a flat file destination. and I hook up the source to destination in SSIS and run the package.. it works .. fine.. but internally what is done??? does SSIS performs BCP out ???

View 5 Replies View Related

Bulk Insert Doubts

Mar 22, 2004

Hello Guys,

soon soon, ��ll have to devellop some procedures to read an ASCII file to supply MS SQL tables. As I�ve read some old post, I�ve understand that I have to use BULK INSERT , or else, BCP or DTS. I�d like to know the diference between this commands and witch of them is more powerful, faster and efficient. If you can give me some implementation tips, I will be very grateful.

thanx all

View 6 Replies View Related

Doubts At No Of Records In A Table.

Sep 13, 2007

hi,
here i am inserting the total dates between 1983/08/26 to 2006/08/26.
i.e the table will have 36525 recoeds approximately. after executing the command i verified the table n i noticed that the last record in this table was 2025/xx/xx approximately. why? defualtly what is the capacity of a sql table. can i change the table properties to hold more num. of records. please help me on this.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

View 4 Replies View Related

Doubts On Integration Services

Jan 14, 2008

Good morning,

I've to do a mining project and I intend to use the SSIS.

I've done a clustering plugin last year on analysis services and I also want to use it.

Let me try to explain the architecture of the process:

1) Receive data (read data from the database - these data are texts, actually)

2) Pre-process the data (transform the texts in a sparse matrix) using a new plugin

3) Call my clustering plugin and assign it to read the table created on the previous step

4) Call my KNN plugin to classify other pre-processed texts using the clusters found on the previous step as classes.

5) Show results.

Alright... It all running as a workflow on integration services

Here are my doubts:

A) How to view and use my plugin made for Analysis Services on Integration Services ? (is it possible or will I have to create another plugins from zero just to run on Integration Services ?)

B) Assuming the previous step is possible, how to modify my plugins to define inputs and outputs to do the correct communications between each plugin ? I think this is the most important question. Is it simple to do ? Is there any documented examples ?

Thanks a lot for your attention once more,

-Renan Souza

View 1 Replies View Related

Doubts - Excel Using SSIS

Feb 20, 2008

I have 25 columns in my excel which is the second row in the excel. I have merged column comprising of 3-4 columns so like wise 7 columns as the 1st row. How can I suggest SSIS that 2ns row in the header or column names? Is there a way to validate values in each cell in excel for their data types using SSIS?
Also is there any way using SSIS that I can refer to specific cell in the excel?

View 1 Replies View Related

Doubts In SSIS Pls Clarify ...

Dec 27, 2007

Hi All,

Here i am going to ask some questions in SSIS pls clarify my doubts...

1. Can i include a package with in another package? like If i have 20 packages..i need to run all packages at a time ..for this can i create a package and can i place all those packages in to a single package?

2. If i have 700 packages then how to run those packages all at a time?

3.Can i develop a Workflow or package in SSIS with out using Data Flow Tasks? Like with the help of only control Flow Tasks can i create a package or workflow?

Thanks & regards,
Maruthi...

View 4 Replies View Related

SSIS Clustering Doubts

Jan 15, 2007

I have a SQL cluster 2005 with 2 nodes. I involved a ETL (DTS) on SSIS.

I used OLEDB to get a connection ORACLE and INFORMIX.

I used a user and password to authenticate.

When I publish the package, to the Integration Services.

I publish it to the MSDB so in case that we have a failover that the package is the same.

However in case of switching server (failover) the package that I published on NODE 1 does not run on NODE 2.

And it fail:

OnError,EDVSQLCLUSTER02,DOMAIN-LISBOA
olo,Insert pt_fornsap,{F06434D9-431B-48B7-A3E6-F17E04A29488},{4FF70682-705E-4410-885F-36F25BC1EC8C},22-09-2006 2:00:01,22-09-2006 2:00:01,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name 'pt_lfa1'.".

Could you help me

Thanks for advance

AG

View 1 Replies View Related

Doubts About Report Deployment

Feb 2, 2006

hi all

i have doubts abt report deployment

if i am deploying report then what process exactly happens?

also is any entry made in IIS or is any new file created in IIS?

if i am deploying report on remote server then what process exactly happens?

what r the entries or files that r created on remote server?

plz help me so that i will understand this deployment process very clearly.

waiting for reply.

View 8 Replies View Related

Doubts Regarding Sales Forecast Model

Aug 5, 2006

I have few more clarification regarding time series.
Firstly
In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year.
Eg: 2006-01-01, 2006-02-01��. etc. all in (yy-mm-dd) format
But
when I make prediction for next five months, though it makes monthly
predictions the date part for the months are random whereas I expect
the date part to be 1st of every month. What is the reason for this and how can I overcome it.
Secondly
Predicted
sales values for some time period are negative though I do not have any
negative value in the training data. What is the reason for this and
how can I rectify it?

Thirdly
In
one of your earlier posts you had said that the time series algorithm
does not have any built in time intelligence but uses the key time
column as a time sequence stamp. So If have to make predictions for a
particular time period where the time slice for each time period is 25
days or 50 days etc, then I understand that the input data used to
train the model should be in the same time sequence.
Or
Can I specify the span of the time period according to which the prediction needs to be made?
Basically
how can I use the same time series model to make monthly, yearly,
quarterly, daily or predictions or for custom time period like I have
mentioned above.

View 3 Replies View Related

BCP Support In SQL Server 2005 - Doubts

Sep 4, 2007

Hi friends,

I am currently using SQL Server 2000 server. I have run some BCP command to import data to SQL from CSV and Text files with the help of FORMAT files through Windows Scheduler.

My doubt is, if I swich to SQL Server 2005, whether the same BCP commands will supported? If no, What are the things I need to do to run the BCP commands in 2005 as in 2000?

I heard that the BCP utility is no longer support in 2005 and this can be done thru SSIS utility. Is it right...?
Is SQL Server 2005 support to use the BCP commands without going to SSIS utility?

Please help. Thanks in advance.

Regards,
Sethu.

View 1 Replies View Related

My Doubts On Revoked Users In MSSQL...please Reply

Jun 11, 2007

Hi

My doubts on revoked users in MSSQL...please reply

I have following doubts:

1. Is there any stored procedure provided by MS SQL server for checking whether the user is revoked or not?

2. What is the query for checking whether the particular user is revoked or not in MS SQL?

3. Do all revoked users get stored in separate database in MSSQL ?

Please reply.

View 2 Replies View Related

My Doubts On Revoked Users In MSSQL...please Reply

Jun 11, 2007

Hi

I have following doubts:

1. Is there any stored procedure provided by MS SQL server for checking whether the user is revoked or not?

2. What is the query for checking whether the particular user is revoked or not in MS SQL?

3. Do all revoked users get stored in separate database in MSSQL ?

Please reply.

View 4 Replies View Related

Please Help Me To Clarify My Doubts Related To Report Builder

Jun 12, 2007

Dear Expertise

Please give me a solution to my general question.

1. How can we Impliment the report builder in client machene.

2. What is the diffrense between Report Server and Report Builder.

View 6 Replies View Related

Indexing

Jul 18, 2005

Hi!! I dont know if this is the correct forum for this or not, but still...Actually i wanted to know some details about SQl's Indexing services.
I found this link on my hosters help pages :
You need to use SQL Query Analyzer tool for this.

This will enable full-text indexing for the current database:exec sp_fulltext_database 'enable'

This creates a catalog:exec sp_fulltext_catalog 'catalogname', 'create'

This enables indexing of a table:exec sp_fulltext_table 'tablename', 'create','catalogname', 'indexname'

This adds a column to an index:exec sp_fulltext_column 'tablename', 'columnname', 'add'

This activates fulltext on a table:exec sp_fulltext_table 'tablename', 'activate'

These two enable automatic filling of the full-text index when changes occur to a table:exec sp_fulltext_table 'tablename','start_change_tracking'exec sp_fulltext_table 'tablename','start_background_updateindex'
From the above i get that i need to set up my database for indexing then make a catalog and then add an index of a table to this catalog. Can anyone point any good tutorials for using this is the proper way so that performance is not affected and tells me details on updating indexes etc(esp using some criterias). Moreover does indexing columns lower the performance? Is there a workaround? I am completely new to this.

View 1 Replies View Related

Re-Indexing

Jun 13, 2002

What should I be looking at if I have real-time data (constant transactions) writing to a table that is experiencing index type problems? The table needs to constantly be re-indexed, which is slowing the whole transaction process down.

What can I look at or do to rectify this?

Thanks!

View 3 Replies View Related

Indexing

Mar 13, 2008

Hi,

Can you please help me find out if this statement is always true:

"Adding a new Index slows down updates"

This is more a general question, applicable as well for SQL Server.
If this is not the appropriate subforum then I kindly ask an moderator to move this thread to the appropriate sub-forum.

Thank you,
Ronnyy

View 14 Replies View Related

SQL Indexing

Apr 26, 2006

Few questions about Indexing on tables:

1) How do I find out if there is any indexing already existing on the tables?

2) How often should tables be re-indexed?

3) Can you refer me to some useful article that talks about basics of indexing and how it could affect peformance?

Thanks

View 5 Replies View Related

Sql Indexing

Nov 10, 2006

D.Harinath writes "Hi!
What is exact purpose of indexing.How it will increase the performance of the quey.

Can u give me example

*******with code********[not an theory]

For 1)Cluster and Non-Cluster indexing.

when Should use cluster and should not user cluster .What is major diffrence b/w them."

View 5 Replies View Related

Indexing Help

May 25, 2007

Okay, so i've been creating a .net app that basically gathers data from a web page, and then passes the parameters to a s.p. i wrote in sql, fetches a count, and displays the data to the webpage. My problem layes in that i have the query command timeout set to 1:00 but alot of my quries on the larger tables take longer then that to complete, so the page is timing out quite often.

i KNOW my problem is database design, i'm running an OLAP database. trasactions only occur once a week when we run a federal DO_NOT_CALL database update. i was wondering if anyone would be so kind as to help me tune my database a little more the get some more juice out of it. i can also tell you guys that i've notice every time a query is ran, the Diqk Query length tacs out to nearlly 100% for the entire length of the query. dont know if that helps.

View 16 Replies View Related

Indexing

Nov 19, 2007

Hi evryone...

I have a problem on indexing. The field PK_hrSetBenefitsLeave is the primary key of the table "hrSetBenefitsLeave". When i see it on the "Manage Indexes and Keys", the identity name became PK_hrSetBenefitsLeave_1. Everytime i change it to its original name will get me error...and i can't save it.

Error msg on saving :
'hrSetBenefitsLeave' table
- Unable to create index 'PK_hrSetBenefitsLeave'.
There is already an object named 'PK_hrSetBenefitsLeave' in the database.
Could not create constraint. See previous errors.

I tried to check using this query.

Select * from Information_Schema.Columns where column_name = 'PK_hrSetBenefitsLeave'

- it gives me only 1 record

my_compdbohrSetBenefitsLeavePK_hrSetBenefitsLeave1NULLNOchar55NULLNULLNULLNULLNULLNULLiso_1NULLNULLSQL_Latin1_General_CP1_CI_ASNULLNULLNULL

Thanks.
-RON

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

View 2 Replies View Related

Indexing

Jul 20, 2005

Hello,I need some help understanding why my indexes do not seem to be affecting mysearches. I would really appreciate help understanding what indexes I needto make this query run faster. I realize that I use wildcards when searchingfor g1.gene_name, but is there anything I can do to make that less of aproblem? I ran EXPLAIN on the search I wanted to optimize and got thefollowing:EXPLAIN SELECT c1.SFID FROM Gene g1, cDNA c1, Transcript t1, Refseq r1 WHERE(c1.SFID = t1.cDNA_SFID AND t1.gene_SFID = g1.SFID AND (g1.gene_sym = 'hh'OR g1.genbank_acc = 'hh' OR g1.gene_name LIKE '%hh%')) OR (c1.genbank_acc ='hh' OR c1.SUID = 'hh') OR (c1.SFID = t1.cDNA_SFID AND t1.gene_SFID =g1.SFID AND g1.locuslink_id = r1.locuslink_id AND (r1.mRNA_acc = 'hh'));+-------+-------+--------------------------+------+---------+------+--------+-------------------------+| table | type | possible_keys | key | key_len | ref | rows| Extra |+-------+-------+--------------------------+------+---------+------+--------+-------------------------+| r1 | index | mRNA_acc,llid,rma,rllid | rma | 25 | NULL | 20093| Using index || g1 | ALL | PRIMARY,llid,ggs,gga,gll | NULL | NULL | NULL | 190475| || c1 | ALL | PRIMARY,cga,cs | NULL | NULL | NULL | 43714| where used || t1 | index | gene_SFID,gS,cS,tg,tc | gS | 4 | NULL | 47238| where used; Using index |+-------+-------+--------------------------+------+---------+------+--------+-------------------------+I have the following indexes (which were all added after the database waspopulated):ALTER TABLE cDNA ADD INDEX cga(genbank_acc, SFID);ALTER TABLE cDNA ADD INDEX co(organism, SFID);ALTER TABLE cDNA ADD INDEX cs(SUID, SFID);ALTER TABLE Gene ADD INDEX ggs(gene_sym, SFID);ALTER TABLE Gene ADD INDEX gga(genbank_acc, SFID);ALTER TABLE Gene ADD INDEX ggn(gene_name, SFID);ALTER TABLE Gene ADD INDEX go(organism, SFID);ALTER TABLE Gene ADD INDEX gll(locuslink_id, SFID);ALTER TABLE Gene ADD INDEX gui(unigene_id, SFID);ALTER TABLE Transcript ADD INDEX tg(gene_SFID, cDNA_SFID);ALTER TABLE Transcript ADD INDEX tc(cDNA_SFID);ALTER TABLE Refseq ADD INDEX rma(mRNA_acc, locuslink_id);ALTER TABLE Refseq ADD INDEX rllid(locuslink_id);

View 2 Replies View Related

Indexing

Mar 21, 2007

Hi,
There is a table which I regularly run a select query on.
The select query always has a fixed where clause on only three of the columns with different parameters.

This is a query that runs each time:

select * from tblData
where
PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()

This are the types of indexes the table currently has:
One index for each of these three fields
i.e. index1 for PersonNo
index2 for EmployeeType
index3 for DataDate
In addition to the above, I also have created a covering index as follows
index4 for PersonNo,EmployeeType,DataDate

Is what I have enough for indexes on this table please?
Is there anything else I have to do on indexing this table?
Thanks

View 3 Replies View Related

Where Can I Go For Help With MS Indexing Services?

Mar 26, 2007

I need some help with MS Indexing Services, and there doesn't seem to be much support for it on the web. Do you know of any good forums or sites?I'm using MS Indexing Services to power the search feature on my site. Should I be using something else like Sharepoint?

View 4 Replies View Related

Indexing - Is It Powerful?

Jan 2, 2008

If you put an index on an integer type column named 'test_column' in a table that had 1,000,000,000 rows in it, and you said select top 50 * from test_table WHERE test_column = 1 since 'test_column' has an index, that would perform extremly fast wouldn't it? Cheers

View 5 Replies View Related