What Are Common Indexes?

Aug 14, 2005

I just inherited a Java application with a fairly complex data model
that does not yet have any indexes except those on primary keys. It is
still in development and before I get to do any performance testing I
want to put some default indexes on it. I realize that this a very
vague request but it's better than nothing so I want to start with
those that are generally a good idea and then tune those areas that
require more fine grained approach. By the way, I'm mostly a Java
programmer so please forgive my DB ignorance and I thank you for any
help.

Since the full schema is quite large I will use a simple example (fully
cited below my questions). Here is list of indexes that I think would
be a good idea. If anyone can add to it or comment on my questions I
would appreciate it.


1. Index on primary keys in all three tables. My understanding that
this indexing happens automatically just by declaring that a column is
a PK.

ALTER TABLE employees ADD PRIMARY KEY (emp_id);
ALTER TABLE employee_addresses ADD PRIMARY KEY (address_id);
ALTER TABLE departments ADD PRIMARY KEY (dept_id);

Question: Does index get created automatically because this is a PK?


2. Index on foreign keys in the children tables to prevent deadlocks
and lock escalations.

CREATE INDEX fk_index ON employee_addresses (emp_id)


3. Indexes on common queries on all three tables.

CREATE INDEX common_query_idx on employees(last_name, first_name,
position)


CREATE INDEX common_query_idx on departments(last_name, first_name,
position)


CREATE INDEX common_query_idx on employee_addresses(street, city)


Question: Given that the searches can be on any field separately and
in any combination should I also put an index on each column
individually or will the composite index take care of individual
searches as well? For example, will the above indexes be sufficient
for the following SELECT:

SELECT e.last_name, e.first_name from employees e, departments d,
employee_addresses ea, dept_employees de WHERE e.emp_id = de.emp_id AND
d.dept_id = de.dept_id AND ea.emp_id = e.emp_id AND e.position =
'master chief' AND d.dept_name = 'galactic affairs' AND ea.city='Los
Angeles'


4. Unique index on the association table. Again this is accomplished
using PK

ALTER TABLE dept_employees ADD PRIMARY KEY (dept_id, emp_id)

Question: Is the index on (dept_id, emp_id) automatic because of PK?

5. The association table has to go both ways and PK takes care only of
the first half. Thus add an index to go the other way.

create unique index dept_employee_idx on dept_employee(emp_id,dept_id)

Question: should I use INDEX ORGANIZED TABLE?

Question: should I have UNIQUE qualifier on the second index given that
PK already takes care of it?


Thanks,

Robert


===== EXAMPLE ======


1) An employee can be in many departments and a department can contain
many employees.

2) Common searches for employees are on last_name, first_name,
position, department_name, department_location separately and in any
combination.

3) There are common searches for departments that contain certain
employees e.g. find all departments containing John Smith.


CREATE TABLE employees
(
emp_id INTEGER NOT NULL,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(25) NOT NULL,
position VARCHAR(10) NOT NULL
);


CREATE TABLE employee_addresses
(
address_id INTEGER NOT NULL,
emp_id INTEGER NOT NULL,
street VARCHAR(50) NOT NULL,
city VARCHAR(25) NOT NULL,
);


CREATE TABLE departments
(
dept_id INTEGER NOT NULL,
dept_name VARCHAR(50) NOT NULL,
dept_location VARCHAR(25) NOT NULL,
);


CREATE TABLE dept_employees
(
dept_id INTEGER NOT NULL,
emp_id INTEGER NOT NULL,
);

ALTER TABLE employee_addresses ADD FOREIGN KEY (emp_id) REFERENCES
employees(emp_id)

ALTER TABLE dept_employees ADD FOREIGN KEY (emp_id) REFERENCES
employees(emp_id)

ALTER TABLE dept_employees ADD FOREIGN KEY (dept_id) REFERENCES
departments(dept_id)

View 7 Replies


ADVERTISEMENT

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

View 4 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

Indexes Vs Clustered Indexes

Sep 17, 2006

What is the difference please?

View 1 Replies View Related

What Are The Most Common SqlExceptions?

Mar 13, 2007

I'm building an exception management system into my application.
What are the most common errors when working with a DAL and a SQL server.
The biggest problem can be Database connection.
My questions:
* Are all SQL related errors catched by the SqlExeption class?
* Which are the most common sql errors?
 

View 1 Replies View Related

Just A Common Question

Mar 31, 2004

I have a stored procedure I use to fill my datagrid..... While doing this is there a way to get the number of records returned...
I use a datareader........any help

View 2 Replies View Related

Common Password

Aug 19, 2005

Hi, I would like to setup a common password for all the MS SQL Server Users. Is there any generalized script for the same ? Thanks In Advance.

View 7 Replies View Related

Common Table Expression

May 28, 2008

Hi
 
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
 
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan 
 

View 3 Replies View Related

Delete The Common Row Of A Table

Jan 7, 2006

hi i am using MS SQL Server 2000, i want to delete the common row from a table

table name EMP
rows
Eno Name Sal
1 Jim 1000
2 Mark 1200
1 Jim 1000
2 Mark 1250
1 Jim 1300

no primary key defile in to the table ....

i want to delete the row of
1 Jim 1000

please anybody can can help me ...

samarjit

View 1 Replies View Related

Field Common In Several Tables.

Dec 1, 2005

I have a database with 10 tables

I have a field named CustID .... I want to find out how many tables have this field CustID

Now one way was check up the design of every table & see whether its there or not (the field)

is there any query through which we can get this info Please help

View 4 Replies View Related

Common Interview Question

May 11, 2006

In my experience of going through plenty of interviews, I have came across a question that I am reminded of by someone's signature.

The common question in this instance is:
What is the advantage of a cursor over a stored procedure? Vice Versa?

I could only, in my so few experiences, have answered that stored procedure consumes less system resources than a cursor. The advantage a cursor has is record by record examination and looping capabilities.

Needless to say that whenever asked those questions, I haven't landed the position. So I'm wondering, what is the more appropriate answer?

View 20 Replies View Related

Get Common Columns Name Between Tables

Aug 8, 2006

Hi,

Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database

For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)

name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?

THANK YOU

View 6 Replies View Related

Select Common Data

Sep 21, 2007

Hi - I'm trying to construct a select statement from the following tables(see example)

_________________________________
|Customer | Product |Shop |
|----------|----------|---------|
|Customer1 | Milk |Dairy |
|Customer2 | Cream |Dairy |
|Customer3 | Milk |Dairy |
|Customer1 | Trainers |Sports|
|Customer2 | Football |Sports|
_________________________________

REQUIRED RESULT SET: Milk, Cream.

Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.

This is an example of a larger problem where there can be numerous products, customers and shops. Here's a DESCRIBE of the relevent columns in each of the tables:

CUSTOMER:
customerID

PRODUCT:
productID
customerID
shopID

SHOP:
shopID

This looks like it should be easy but my SQL isn't the best ;-)

I'd really appreciate any help you could give!!

Cheers!

View 1 Replies View Related

Common Properties Design

Jan 14, 2008

I try to find the best design for a SQL Server 2005 database structure.

I have the following 'objects':

Facilities
Departments - 'dependents' of a Facility
Users

Addresses - each of the above can have none or more Addresses.

The basic tables design would be:
CREATE TABLE [dbo].[Facility](
[cFacilityID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED ,
[cFacilityName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Department](
[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES [dbo].[Facility] ([cFacilityID]) ON UPDATE CASCADE ON DELETE CASCADE,
[cDepartmentID] [nvarchar](3) NOT NULL,
[cDepartmentName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED ( [cFacilityID], [cDepartmentID])
)
GO
CREATE TABLE [dbo].[User](
[cUserID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED,
[cUserName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Addresses](
[pkAddress] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[cAddress] [nvarchar](255) NULL,
[cZip] [nvarchar](5) NULL,
[cEmail] [nvarchar](255) NULL
)
GO


The question regards the Addresses table:
How can I design the Addresses table(s) so I can enforce relational integrity and cascade update and delete for each Facility, Department and User tables?

I figured out the following options, but none achives my requirements:

1. An Address table for each 'object': FacilityAddress, DepartmentAddress, UserAddress with the corresponding foreign key in each Address table. This solution duplicates all the addresses fields (which are more than I showed in the example).

2. A common Addresses table with a discriminator field (1=Facility, 2=Department, 3=User). This doesn't allow me to enforce referential integrity with constraint, maybe only with triggers.

3. A common Addresses table with a foreign key for each 'object (fkFacility, fkDepartment, fkUser) allowing nulls. In this case, the 'dependence' between Facility and Department stops me to enforce cascade deletes/updates on both Facility and Department foreign key (circular...).

4. An intermediate (link) table between each 'object' and Addresses table with the foreign keys (Facility, Addresses). Again, no referential integrity.

So, is there a method to design such structure?

PS. Please keep in mind that this is just a simplified example (reduced at the key information) of my real structure.

Thanks,
Ioan

View 8 Replies View Related

Finding Common Joins

Oct 2, 2007

At the risk of asking a stupid question -Is anyone familiar with either a query against the systables or maybean outside tool that will provide a list of the most common joins thathave been made in user created views? I'm not talking about tablerelationships that are established at the database level, but rather,I'm referring to the ability to find which joins have been utilized inpoorly constructed databases where no relationships were establishedin the first place.Thanks in advance -Cindy T.

View 2 Replies View Related

Common Table Expression?

Jul 20, 2005

What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston

View 2 Replies View Related

Common Criteria Certification

Dec 18, 2006

I see that part of SP2 is the new CC certification -- can anyone give me some more details. What level is it at? EAL4? I can't seem to find the certificate on the CC Web site but it could be because it's not SP2 yet.

View 3 Replies View Related

Common Need: History Table

Jan 22, 2008

Hi there!

I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.

When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.

First, what do you think of that implementation?

We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...

What is the common solution for this need?

Thanks
Frantz

View 4 Replies View Related

Is It Common To Have CPU Usage At 50% Constantly.

Jul 19, 2007

The comapny i work for has a server running the following.

Opteron 246x2

2 Gig memory

320 Gig Sata 2 drives

Windows 2003 Standard Edition

SQL Server 2005 Express Edition. The Free one.

There are approx 10 users that connect to the server.

There are two programs which seem to use sql server. Act 7.0 and service ceo.



When the computer is rebooted its at 0% for sqlserver.exe

Than when all connect it maxes it 50% and its steady there. Seems Service ceo affects it the most.

Told by comapny need to buy full blown sql server to resolve problem. But i dont think this is the problem.



Questions: Is it common for server to be at 50% all the time with sql server running?

And if its not is there a way to reduce the sql cpu usage.



I am new to sql server and have done alot of research and fixes. Ive unsitalled and reinstalled all sql instances and done the tweaks suggested. Any fresh ideas would be great thanks.



John



View 3 Replies View Related

Problem Getting 5 Most Common Occurences

Feb 14, 2008

I need to get the 5 most common occurences from a table. Not really sure of the SQL statement I would use to do that. Would someone be kind enough to throw out a select statement that would do this? psuedo code would be fine, I just need to know the jest of how to do it. I originally thought Select Top 5 would work, but then I actually started thinking and realized it wasnt it. So now im stuck. Any help would be appreciated.

Thanks,
Steve

EDIT: I should add that this will be coming from a view with multiple tables..thanks

View 3 Replies View Related

Common Image In Reports

Apr 17, 2007

I need to display the company logo on 100 odd reports that run from different folders on the report manager. Since the company logo has changed before and we had to change every report, I want the image stored on a server (with SSL on) and want the reports to point to the server. The server is going to be different in development and production environments.



When I try to add the image using "Image Wizard" I get the error "Invalid image.". The wizard wouldn't let me use a function to build the path based on the environment and select the image from the correct server. It doesn't accept anything that doesn't start with "http:" when I select "web" as the image source.



Please Help!!!

View 4 Replies View Related

Transact-SQL Common Features?????

Jan 12, 2007

Hi



The white paper which compares SQL Express and SQL Compact says that they both share Common Features

Can someone please indicate if SQL Compact will support functions like

Average, STDEv etc

Thanks

Touraj

View 7 Replies View Related

Common Table Expressions

Aug 31, 2006

Hi guys,

I know its not a good idea to give you a big query...but/...

I have a query (given below) with Common table expressions. It is giving me error : The multi-part identifier "dbo.tmpValidServices_ALL.Service" could not be bound. for all the fields .. Do you have any idea.. I fed up with analysing query.. made me mad.. please help me...

 

----------------------------------------------

DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000)

SET @sql = 'WITH Q1 (SiteID,Programme,Complete,PID,COC,NotionalSum,TVMinsSUM,Postcode,Suburb,Qty)
AS

(SELECT
 dbo.tmpValidServices_ALL.SiteID,
 dbo.tmpValidServices_ALL.Programme,
 dbo.tmpValidServices_ALL.Complete AS Complete,
 dbo.tmpValidServices_ALL.RID AS PID,
 dbo.tmpValidServices_ALL.COC# AS COC,
 (dbo.lkpService.Notional$) AS NotionalSum,
 (TVMins) AS TVMinsSUM,
 dbo.tmpDemographics_ALL.Postcode,
 dbo.tmpdemographics_ALL.Suburb,
                count(*) as Qty

FROM  lkpService

INNER JOIN dbo.tmpValidServices_ALL vs  ON dbo.lkpservice.code = dbo.tmpValidServices_ALL.Service
INNER JOIN dbo.tmpDemographics_ALL ON dbo.tmpValidServices_ALL.RID = dbo.tmpDemographics_ALL.RID '

IF @COCGroup IS NOT NULL
SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON dbo.tmpValidServices_ALL.COC = c.pvcode '

SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '      

SELECT     @sql = @sql + 'WHERE dbo.lkpService.Schedule = @Schedule '

 IF @StartDate IS NOT NULL
     SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete  >= @StartDate ) '

 IF @EndDate IS NOT NULL
      SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete <= @EndDate ) '


 IF @SiteID IS NOT NULL
       SELECT     @sql = @sql + 'AND (ag.EXACT# = @SiteID) '

 IF @COCGroup IS NOT NULL
 SELECT     @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '

 IF @Provider IS NOT NULL
 SELECT     @sql = @sql + 'AND (dbo.tmpValidServices_ALL.Provider = @Provider) '

 IF @Postcode IS NOT NULL
SELECT     @sql = @sql + 'AND (dbo.tmpdemographics_ALL.Postcode = @Postcode) '

SELECT     @sql = @sql  + ' GROUP BY dbo.tmpValidServices_ALL.SiteID,
                                                              dbo.tmpValidServices_ALL.Programme,
                                                              dbo.tmpValidServices_ALL.Complete ,
                                                              dbo.tmpValidServices_ALL.RID , 
                                                              dbo.tmpValidServices_ALL.COC# ,
                                                              dbo.tmpDemographics_ALL.postcode, 
                                                              dbo.tmpdemographics_ALL.Suburb,
                                                              (dbo.lkpService.Notional$),
                                                              (dbo.lkpService.TVMins)  ) 

SELECT COUNT(DISTINCT PID + CAST(Complete AS varchar(20))  + CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Visits,
 COUNT(DISTINCT PID + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Patients, 
 COUNT(DISTINCT  CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20)) + CAST(PID AS varchar(20))) AS COCs,
 SUM(NotionalSum) AS NotionalSum,
 SUM(TVMinsSUM) AS TVMinsSUM,Postcode,Suburb,sum(Qty) as Qty
 
FROM Q1

Group by Postcode,Suburb

Order by Postcode,Suburb  '

SET              @paramlist = '@SiteID as numeric,
@COCGroup as varchar(20),
@Postcode as varchar(20),
@StartDate DateTime,
@EndDate DateTime,
@Schedule varchar(20),
@Provider varchar(20)'
                     
 EXEC sp_executesql @sql, @paramlist, @SiteID, @COCGroup, @Postcode, @StartDate, @EndDate, @Schedule, @Provider

View 2 Replies View Related

How To Group 2 Datasets On A Common ID?

Apr 29, 2008

How can I achieve the following?

Dataset 1

EntityID ColValA ColValB
1 56 78
1 16 75
1 06 71
2 12 14
2 3 74
3 1 15
3 5 12
3 12 78
3 34 56


Dataset 2

EntityID AnotherColZ AnotherColY AnotherColX
1 a f g
1 f g s
2 t w a
3 j r s
3 l e a


required output
-------------------------------------------------------------------------------------
Entity1 (group the data at this level)
ColValA ColValB (table for values from dataset 1)
56 78
16 75

AnotherColZ AnotherColY AnotherColx (table for values from dataset 2)
a f g
f g s
------------------------------------------------------------------------------------
Entity2 (now we move on to the next entity)
ColValA ColValB (again one table for values from dataset 1)
12 14

(and another for values from dataset 2)
-----------------------------------------------------------------------------------
etc

View 9 Replies View Related

Where Do You Put Common Stored Procs?

Sep 7, 2007

I have a few stored procs that I use from more than one database. There has to be a place I can put these so I don't have a copy of the proc in each DB, right?

Where do you put them and how do you reference them? Thanks.

J

View 6 Replies View Related

Distinct In Common Table Expressions CTE

Feb 20, 2007

I have managed to write my first CTE SQL that handles recursion but I have a problem with distinct - can't get that to work!!
My CTE:WITH StudentsHierarchy(SystemID1, GroupID, AccessType, AccessGroupID, StudentID, HierarchyLevel) AS
(
--Base Case
SELECT
SystemID,
GroupID,
AccessType,
AccessGroupID,
StudentID,
1 as HierarchyLevel
FROM UserGroup a

UNION ALL

--Recursive step
SELECT
u.SystemID,
u.GroupID,
u.AccessType,
u.AccessGroupID,
u.StudentID,
uh.HierarchyLevel + 1 as HierarchyLevel
FROM UserGroup u
INNER JOIN StudentsHierarchy uh ON
u.GroupID = uh.AccessGroupID

)
Select sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
 
and I would like to have a distinct on the StudentID like:Select DISTINCT sh.StudentID, sh.SystemID1, sh.GroupID, sh.AccessType, sh.AccessGroupID, sh.StudentID, sh.HierarchyLevel, (select StudentName from Student s
where sh.StudentID = s.StudentID) AS StudentName from StudentsHierarchy sh
WHERE AccessType = 'S'
 
 
How should I do?
 

View 1 Replies View Related

Need Help With MAX Function Using Two Tables With Common Columns

Apr 9, 2006

I have two tables that contain product SKUs (12-character strings):
Table 1Product IdSKU...
Table 2ProductVariantIdSKU...
I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers.
Thanks!
Duncan

View 4 Replies View Related

Common Temp Tables In Procedures

Jul 20, 2004

I have 3 jobs each consists of set of stored procedures.The stored procedures have lots of temp tables. And all the jobs run at the same time.

job1:

EXEc sp1
EXEC sp2
EXEC sp3

Job2

EXEC abc1
EXEC abc2
EXEC abc3
EXEC abc4
EXEC abc5

Job3

EXEC xyz1
EXEC xyz2
EXEC xyz3
EXEC xyz4


But the issue is that the stored procedures in the job1 has temp tables with the same name as stored procedures in the job 2 have.

Eg:

procedure abc1 and procedure sp2 have the temp table #temp1.
procedure abc4 and procedure xyz2 have the temp table #temp3.

Like this i have some more common temp tables. So my question is that can I use the temp tables like that.If so does it degrade the perforamnce of the sps.

View 1 Replies View Related

Index On Used Table Common Fields

Jan 6, 2012

I would like to know if SQL Server creates automatic index on the used tables' common fields.

For example:

Code:
CREATE VIEW [dbo].[ORMAN]
AS
SELECT dbo.PARSEL.OBJECTID, dbo.PARSEL.ADAPARSEL, dbo.PARSEL.ORMANADI, dbo.PARSEL.MULKIYET, dbo.PARSEL.ALAN, dbo.PARSEL.YERKOD,
dbo.PARSEL.UYG_KAN, dbo.PARSEL.KomNo, dbo.PARSEL.KadKan, dbo.PARSEL.Mescere, dbo.PARSEL.ACIKLAMA, dbo.PARSEL.URETIM_YONTEMI,

[Code] .....

On the above view example, if table PARSEL has 1 million records do I have to create an index for PARSELDURUM or when I create a view does SQL server create automatic virtual index?

View 6 Replies View Related

Union Based On Common Column

Jul 15, 2013

Trying to figure out a SQL query.I have 2 identical tables: Table A and Table B..This query doesnt seem to be working:

SELECT * FROM (

SELECT ClientID, sum(ABC), count(*)
FROM Table A
Where <condition>

[code]...

The result I am looking for is that for each ClientID, I need the total sum of ABC and total count.

View 4 Replies View Related

Common DTS Source && Multiple Destinations

Feb 2, 2004

I want to run multiple DTS packages which export data into text files.
There is only one Data Source ..and multiple destinations.
When i write a code for this in VB ,for each Package i need to define the source connectioninividually. Can't i use the same Source connection which i used for the first package in the subsequent packages?

View 1 Replies View Related

How To Join Two Tables Having No Common Coloumn

Apr 15, 2008

hi friends i am new to this forum and to the tech. also , i am reading a csv file and storing the data to the table of sql server 2005 database but the problem is i cannot join both the tables because both the tables doesnot have common cols and ther is no primary key field in any of the coloumn i have tried the sql query but i didn't got the desired output so could anyone help me please see the sql query of mine is like this:

SELECT dbo.table1.country, dbo.table1.code, dbo.table1.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost,
dbo.table2.dialed_digits
FROM dbo.table1 INNER JOIN
dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR
dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6)

please help me its urgent

View 3 Replies View Related







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