Quale Tipo Di Indice Sulla Tabella? Wich Kind Of Index On Table..??

Jul 20, 2005

ciao a tutti...
ci risono...
devo migliorare delle performance di accesso ad una tabella...
la tabella non ha indice primario, ne altri indici...
sulla tabella ci accedo con select di questo tipo..

select @ExistInOAG = count(*)
FROM caprs05dev.dbo.OAG
WHERE Air_Carrier = @Aircarrier and
cast(ltrim(rtrim(Flt_nbr)) as int) =
cast(ltrim(rtrim(@ComFltNbr)) as int) and
Aipt_Dpt = @AIPTDep and
Aipt_Des = @AIPTDst and
DT_FLIGHT = @Date_Rif and
convert(varchar, STD, 102) = convert(varchar, @STD,
102) and
convert(varchar, STA, 102) = convert(varchar, @STA,
102)

ho provato a creare diversi tipi di indici, ma le performance migliori
le ottengo se lascio stare tutto come era senza nessun'indice!!!! vi
sembra possibile???
le prove che ho fatto sono queste...

inserire un indice tipo Primary con i campi
Flt_nbr,Aipt_Dpt,Aipt_Des,STD,STA
create Unique, tipo Index, con Ignore duplicate key...
create as clustered checcato e fill factor 100%... ci accedo
praticamente solo in select nel file...

avete consigli per migliorare le performance di accesso alla
tabella????

grazie.. stefano!!!

View 2 Replies


ADVERTISEMENT

What Kind Of Index

May 12, 2004

I have 20 tables (the same schema)

I use view to search it with union operator.
now i have indexes on all of the tables on 1 kolumn (nvarchar)

what kind of index i should use to make it fastest


index on view ?

View 3 Replies View Related

Query To Get Linked List Kind Of Data From The Table

Sep 18, 2006

hi Experts,



I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of









ID
Duplicate_of
State

77637
65702
Duplicate

65702
42217
Duplicate

42217
-
Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.



Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.



/soni

View 1 Replies View Related

Query To Get Linked List Kind Of Data From The Table

Sep 18, 2006

hi Experts,



I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of









ID
Duplicate_of
State

77637
65702
Duplicate

65702
42217
Duplicate

42217
-
Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.



Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.



/soni

View 5 Replies View Related

Sql Gives 78 Wich I Don't Like

Apr 10, 2008

Hi,
I've the sql code below wich give interesttarget 78 as a result wch I shouldn't. I think it has something to do with the'(' and  ')' .
SELECT Top 5 *
  FROM TestnetCampaign a, TestnetAds b
WHERE a.campaignid = b.campaignid
     AND b.sizeid = 21
     AND (
                 a.interesttarget LIKE '10,%'
           OR a.interesttarget LIKE '%,10,%'
           OR a.interesttarget LIKE '%,10'
             )
    AND (
                 a.interesttarget NOT LIKE '78,%'
           OR a.interesttarget NOT LIKE '%,78,%'
           OR a.interesttarget NOT LIKE '%,78'
            )
ORDER BY a.ecpc DESC
Any suggestions?
Thanks!
Roel

View 2 Replies View Related

Wich Datatype?

May 5, 2006

Hi Group,

Wich datatype can I best use for a number with several decimals (can be anything between 1 and e.g.10)

If the number has 10 decimals the customer needs the EXACT numbers. They couldn't explain why....

TIA

Regards,

Sander

View 7 Replies View Related

Wich JOIN To Use For ..

May 20, 2008

Hello, if i have

TABLE ITEMS
ItID(PK) | ItName
---------------------------------------------------------------
1 Bike
2 Car
3 Boat

TABLE DET
ItID(PK)(FK of ItID) | Detail
---------------------------------------------------------------
1 Mountain
1 With Suspension
2 5 Airbag
2 3500 c.c.
2 215cv.
3 with cabinet

And i want a table result like
ItId ItName Detail
---------------------------------------------------------------
1 Bike Mountain
2 Car 5 Airbar
3 Boat with cabinet

WHAT "JOIN" I NEED TO USE OR WHAT QUERY I NEED TO WRITE. CAN ANYONE WRITE ME THE QUERY, that as i show in example, will return only the first Detail for each Item

View 13 Replies View Related

The Index Entry For Row ID Was Not Found In Index ID 3, Of Table 357576312

Jul 9, 2004

Hi,

I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:

The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.

All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .

Any ideas as to what is causing this error?

View 3 Replies View Related

Advantages Of Using Nonclustered Index After Using Clustered Index On One Table

Jul 3, 2006

Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??

Thanks

View 4 Replies View Related

Wich Collation For Euro Character

Mar 14, 2008

Hi,
 Wich collation should I use for the right display of the euro character? I now get a '?'.
Thanks!
Roel

View 1 Replies View Related

Express Edition 2005 Wich The Limits ?

Sep 22, 2006

Hi, i'd like to try Express2005 but before i'd like to know
wich are the limit vs Sql Server.
I had seek in Microsoft's site but i haven't found it
anyone kwon it ?
Thank's for the answer

Daniele Pinai
(Italy)

View 2 Replies View Related

Wich One Is More Efficient ? Datafile Or Attached On Server ?

May 8, 2008

Hello friends !
Wich one is more efficient way to use a sql mdf file in Sqlexpress ?
attaching a mdf file on sqlexpress ( and use initialcatalog in connection string ) or use AttachDbFilename on connection string directly ?
Is there any difference in performance and speed ?
Thanks a lot

View 1 Replies View Related

Wich Is Faster? In Relation To These Two Business Object Scenarios

Jun 27, 2006

I kow for a solid comparison between using datareaders and datasets I will have to perform that myself.  But for now I will be utilizing datasets...
What I am doing is currently utilizing assemblies to create my datasets ahead of time.  I will eventually compile them as dlls.  I'm just utilizing assemblies during my building/testing fase.
My questions is:
Is it faster to completely build the datasets and all needed connections inside the assemblies/dlls and fill them?  Or to build the datasets and connections as a sub procedure that can be accessed and then fill them as each required set of data is needed?
I ask because I will be having many different data connections and so I'm not sure if it's faster to explicitly build/fill almost each and every one and have them compiled at runtime ready to be accessed, or to file them when called from a sub etc...?
As I take it, the server should track and monitor which are used the most, and cache them, so as to operate faster.  I wonder if it will still do this if the datasets aren't pre-filled?

View 2 Replies View Related

Create A Data Flow In SSIS Wich Updates Som Rows.

Aug 14, 2006

Hi,

I have a table customer wich has the columns phone_number(char type) and ok_to_call(bit type). There are already data in the table and the column ok_to_call only contains the value false for every row.

Now i want to update the latter column. I have a text file with a list of phone numbers and i want that all the rows in the Customer table(phone_number column)that matches the number in the text file to update ok_to_call to true.

This is to be done in SSIS(Integration Services). I'm new at this and i've looked around that tool but is a lot of items, packages and stuff so i dont know where to begin.

Would appreciate help on how to solve this issue in SSIS. What controlflow/Data flows to use,wich items and packages to use, how to configure and how to link together?

Regards
/Tomas

View 3 Replies View Related

Difference Between Index Seek && Index Scan && Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

How To Create Index On Table Variable (Table Don't Have Primary Key)

Feb 26, 2008



Hi all,


my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop...
below is my table variable and I need to create 3 indexes on this...


DECLARE @t_Replenishment_Rpt TABLE
(
Item_Nbr varchar(25) NULL,
Item_Desc varchar(255) NULL,
Trx_Date datetime NULL,
Balance int NULL,
Trx_Type char(10) NULL,
Issue_Type char(10) NULL,
Location char(25) NULL,
Min_Stock int NULL,
Order_Qty int NULL,
Unit char(10) NULL,
Issue_Qty int NULL,
Vendor varchar(10) NULL,
WO_Nbr varchar(10) NULL,
Lead_Time int NULL,
PO_Nbr char(10) NULL,
PO_Status char(10) NULL,
Currency char(10) NULL,
Last_Cost money NULL,
Dept_No varchar(20) NULL,
MSDSNbr varchar(10) NULL,
VendorName varchar(50) NULL,
Reviewed varchar(20) NULL
)

I tryed all below senarios...it is giving error...


--Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number
--EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr))
--CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr
INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr )
--EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')')
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex'))
--EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))


View 3 Replies View Related

Index Seek, Index Scan, Table Scan

Oct 4, 2007



Hey,

what is the difference between Table Scan und Index Scan?

I find no difitions in the internet

Finchen

View 5 Replies View Related

What Is Table Scan, Index Scan And Index Seek??

Sep 21, 2007

Hi,
I want to know wht is a


TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????

View 5 Replies View Related

What Kind Of Project?

Jul 18, 2007

I'm not looking for details or code, just general direction I should go. Here is my goal:
I have a SQL stored procedure that returns data which I will need to add spaces to, then end up with a space delimited text file.
This txt file will then be needed to be saved and emailed.
Also, I would like to be able to schedule this in the Task Scheduler for once a month execution.
Is this all just one big crazy idea?
 Thanks for any help.
Andrew  
 

View 16 Replies View Related

Which Kind Of Replication Should I Use?

Aug 8, 2001

i want to take 70-028 test.
but i still have some questions about administration.
can anybody help me?
Question:
(NY1-----T1-----NY2)-----56kbps-----(Seattle-----T1-----SanFrancisco-----T1-----Los Angeles)
NY1=NewYork1,NY2=NewYork2,SF=SanFrancisco,LA=Los Angeles
A. NY1=pub/dist; NY2,Seattle,SF,LA as subscriber
B.NY1=pubs; NY2=dist;Seattle,SF,LA as subscriber
C.NY1=pubs; SF=subs/dist/pubs; NY2,Seattle,LA as subscriber
D.NY1=pubs; LA=dist; something not workable
which one is correct answer, and why?
How to do the questions like this?

View 1 Replies View Related

I'm Don't Know What Kind Of Replication Should I Use

Sep 18, 2002

In the company that I work there are two offices, one is outside the city and the other is in the city. They are connected trough a DS0 using frame relay. The thing is that the comunication is very slow (the SQL server is in the city) and we want to add a new server at the office outside the city and we want at the end of the day replicate the data between the two servers so every server has the actual data. We didn't use primary keys, instead of that we used identities (autoincremental value administered by the server). What kind of replication should I use and why? (I'm new with SQL Server). There are 3 kinds: Snapshot, Transactional and Merge.

View 4 Replies View Related

What Kind Of Loop And How?

May 22, 2008



Im looking to create a Stock Levels table which goes through each record of my Part table updating the corresponding Part in the Stock Level table.

I know how to perform all the Count calculations etc, if someone could just give me an example of a suitable loop that would be great

cheers.

View 11 Replies View Related

One Kind Of Transformation

Jun 11, 2007

original data type is string ,format like MM/dd/YYYY,also there exists null or "" value

I need to convert them into datetime,format like YYYY--MM-dd

any idea about this?

thanks in advance

View 10 Replies View Related

What Kind Of Package Is It?

Aug 18, 2006

Hi everyone,

Is there any way to know how is stored a package before do loadpackage, loadfromsql... bla,bla??

I mean, is there any property for Package class where you can see how it is stored?

Thanks in advance for your comments,

View 3 Replies View Related

What Kind Of DB Is Members ASPNETDB.MDF?

Aug 3, 2007

Hello,   I created web site using VWD 2005 Express, and I use 'Create New user account' control for signin up. I need to know what kind of database is ASPNETDB.MDF ' in order to set it on server? I created ASPNETDB.MDF (database?) when I first created user in  ASP.Net Web Site Administration Tool . What tipe is ASPNETDB.MDF database?  . I have my web site on server, and DB is not working, because it is not set on server.I have this databases available on my server:Microsoft Access                   Unlimited     Microsoft SQL Server 2000     1 x    MySQL                                 1 x    PostgreSQL                           1 xDSN/ODBC                           Unlimited SQL Database Users              Unlimited         Which one you recommend should I choose for members database (ASPNETDB.MDF)?Thank you,   Beezgetz       

View 11 Replies View Related

Problem With This Kind Of Sql Statement WHERE ... IN (...,...,...)

Oct 28, 2004

Hi,

I have a problem in a Stored Procedure SQL query im writing, basically what I want is something that works like this

WHERE intField IN (@strCommaSepList_Of_Integers)

First I tried this:

SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (@strListUserGroupsAllowed))

But if I passed a 25,26 value to the last paramenet is gave an error saying:

Syntax error converting the varchar value '25,36' to a cllumn of data type int.

How can I get this to work??

If for example I manually put the WHERE IN values it works, ike this I mean:

SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname
FROM dbo.tblComp_Clie_Bran_Users
WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (25, 36))

But thats no use as I have to pass the list of values dynamically...

Please help!!

- Carl

View 2 Replies View Related

Kind Attn.Ray / Kris

Feb 22, 2001

Hi,
I am unable to understand how can I create a new filegroup?
I know how to move certain text/ntext/images column to a new file group - thru design table but my problem is how to create a new filegroup?
Do I have to use Alter Table/Alter Database for this or there is any other better way?
TIA
Jai.

View 2 Replies View Related

How To Implement This Kind Of Sorting?

Jul 15, 2004

Here's a basic overview of my Table

ID identity int
ParentID int
Name varchar

Now, the root level items will have a ParentID of NULL, and the child nodes will have it's parent's ID in it's ParentID column (a simple self-referencing relationship).

However, when I select it, I would like to Order by ID, but then have all it's children right after it. For example, if this is the unsorted view:

Code:


ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
3 <NULL> Test 2
4 1 Test Child 2
5 3 Test 2 Child 1


I would like the sorted to look like:

Code:


ID ParentID Name
----------------------
1 <NULL> Test
2 1 Test Child 1
4 1 Test Child 2
3 <NULL> Test 2
5 3 Test 2 Child 1


Is this possible with my current structure? If not, how could I change it?

View 4 Replies View Related

A Different Kind Of MSSQL Question...

Jul 21, 2004

Hey all!
This may sound like a weird one but i couldn't think of a better audience to ask!

If you are a SQL Server DBA (or were, or aspire to be, or play one on T.V., etc.) and the CIO of your 2.something billion $ a year company has offered you an 1 hour forum to sit down and ask him anything you want with the pretense of getting a straight answer, what would you ask?

I know this offers an excellent opportunity to cut up a bit...and do so if you must (keeping us entertained around here is important too!)...but i'm looking for work related, SQL related, direction, strategy, etc, etc type stuff.
Not: 'Whats it like to drive a $250k 'benz to work everyday?' OR 'Can i have .5% of your $2,000,000 bonus this year?'

While i genuinely have those questions in my mind...i'm not looking to waste his $5,000 an hour ass's time - and I only have 1 hour to chew his ear off.

What do ya'll think?

View 4 Replies View Related

What Kind Of Fucntions I Cannot Use In Trigger?

Nov 3, 2006

Hi,

What kind of functions i cannot use within a trigger?
Thanks for all your replys for my previous posts.

View 1 Replies View Related

Can We Create A Kind Of Temporary SP

Jan 4, 2007

If we don t have the right to create an SP, can we create a dynamic in memory kind of SP
what s the syntax pls?
Thanks a lot

View 8 Replies View Related

Help On A 'Spread' Kind Of Ranking...

Nov 9, 2007

Hi all,

Just can't figure this out. I'll try not to give a long-winded explanation (I hope), let's say this is the table, for example:
[Table1]
RowNumber | Value
1 | 4
2 | 6
3 | 3
4 |10
5 | 6
6 | 5
7 | 8
8 | 8
9 | 2
10 | 6

I want to write a query that creates a column whose values are similar to (ROW_NUMBER -1), and resets each time a value is present.
Using the above example, 6 will be the test value. So, I want to know how many rows it takes till 6 is repeated.

Output would be:
RowNumber | Value | Spread
1 | 4 | 1
2 | 6 | 0
3 | 3 | 1
4 |10 | 2
5 | 6 | 0
6 | 5 | 1
7 | 8 | 2
8 | 8 | 3
9 | 2 | 4
10 | 6 | 0

I think PARTITION BY can be used somewhere, with the ORDER BY on the [RowNumber] - but I'm just not sure on which ranking function to use, and how to reset the Ranking on the number 6 (above example).

Any help would be appreciated. This has been a mind-teaser for me, and I give

Thanks,
Denvas

View 5 Replies View Related

Looking For Some Guidance From A Kind Person

Apr 4, 2008

I am gonig into interview for a junior developer position. The role involves a lot of SQL based work. Training is on the job, and they know I am new to this, but they want to know what I can do with SQL server by wednesday, and obviosuly I stand a better chance if I can do a reasonable amount by then.I am assuming I can practise with offline databases, so I would like to do that. Also I was wondering if there were any simple example databases I can load up.

I have downloaded and installed the software, and would like to know how to connect and create a test database.
There will be a small test in the interview and the questions are:
1. SQL Management and Data Extraction
For this task you will need to be familiar with database tables, data types and constraints. There will be some administration work using SQL Management Studio along with some T-SQL queries. You will need to show use of the SELECT, INSERT, UPDATE and DELETE statements. If you do not have SQL Server, you can download the express edition for free at the following URL. http://msdn2.microsoft.com/en-us/express/bb410791.aspx


2. XSLT
For this task I will be asking you to produce some HTML output displaying the data from an XML file. The concept is similar to ASP.


3. Database Design
You will be given a scenario for a company that requires some database software for the smooth running of their organisation. You will need to plan and design a data structure to accommodate these requirements. You will be allowed to spend as much time on this part of the test as you wish. Key information here is going to be database normalization.




These questions dont make a whole lot of sense to be at the moment, so would appreciate a breakdown in simpler terms.





This job will be a fantastic opportunity for me to get into development, and would appreaciate any help that you guys have to offer, thanks in advance.

View 21 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved