help on alias...

Dear friends and guru:
I am trying to do insertion with alias
insert into TableName t (t.columnName) value (value)
it does not work, please give me any useful input on this.
Thanks in advance!
--
SincerelyA) This is the wrong group for this request, it is the Reporting Services
group.
B) Don't use aliases, they don't work on inserts. Since Insert only inserts
into a single table, the tablename prefix is totally unneccesary.
"Frank RS" <FrankRS@.discussions.microsoft.com> wrote in message
news:C3EFCB25-67CF-4FED-BFE4-D9A7EB7989A4@.microsoft.com...
> Dear friends and guru:
> I am trying to do insertion with alias
> insert into TableName t (t.columnName) value (value)
> it does not work, please give me any useful input on this.
> Thanks in advance!
>
> --
> Sincerely
>

HELP ME TO OPTIMIZE QUERY

Hello friends,

I'm facing performance related problem while running following query on SQL Server 2000.

This query is basically used to find last location of each unit that are passed. Here I am passing data like "'26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @.Units variable. But it takes too much time and I don't get output. Table is having around5 Million records.

Query:

SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM tblUnit INNER JOIN

tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID

WHERE tblUnitHistory.UnitHistoryDate

IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ') GROUP BY tblUnitHistory.UnitID)

AND tblUnit.UnitID in (' + @.Units + ')

ORDER BY tblUnit.UnitID

Table Structure:

UnitHistoryID int Primary Key

UnitID int

Location varchar(200)

Latitude decimal 9

Longitude decimal 9

Speed decimal5

BatteryVoltage decimal5

ReasonCode int

DistanceFromLastLocation decimal9

UnitHistoryDate datetime

Indexes:

1. Clustered Index on ColumnUnitID

2. Non-clustered Index on ColumnUnitHistoryDate

3. Non-clustered Index on ColumnUnitHistoryID

Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.

Regards,

Sandeep

try to limit processed records by:

'SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid

FROM (SELECT * FROM tblUnit

where

AND tblUnit.UnitID in (' + @.Units + '))tblUnit

INNER JOIN

(SELECT tblUnitHistory.UnitID , MAX(UnitHistoryDate) UnitHistoryDate FROM (SELECT * FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @.Units + ')) tblUnitHistory GROUP BY tblUnitHistory.UnitID) tblUnitHistory

ON tblUnit.UnitID = tblUnitHistory.UnitID

ORDER BY tblUnit.UnitID'

I hope that it will work, The Idea is to limit number of records used in join to minimum.

Help me friends...

Dear All,
I am a software programmer at initio i wish to improve my back-end side in SQL Server 2000.

I wish to learn it asap and i wish to become Database Administrator.

So, I need your help as i am a beginner and if you have any links or turorial to learn it please provide me.

Thanks.
Truly yours,
MaxHi,
I suggest you go to microsoft.com and go to the links for SQL Server. You can also try msdn.microsoft.com, as they have lots of material about SQL Server their too. As you say you're a programmer, try to keep in mind that designing, implementing and maintaining a relational database requires a different way of looking at things than it does in most programming environments. Do some research on relational database management systems (RDBMS), as that's the core idea that SQL Server is built around. Once you start thinking in terms of data structure, rather than in procedural processing, you'll find the topic a lot easier to digest. Good luck. BRN..|||I totally agree with Brian27. You really should be checking outthe source of the database, and that would be Microsoft.com. Onething to understand and acknowledge is that you will not be proficientwithout experience. I've seen people read these tutorials,how-to's, and life stories without experiencing the design from startto end on a number of occasions. They always make a trainwreck ofthings, and it's a very frustrating experience. Your best bet isto read the articles written (aspalliance has a bunch of good onestoo), and try to get yourself involved in the database design processat your current employer if they have a DBA. You can also look upinformation on the MCDBA, as that exam gets pretty extensive in thedatabase management.
Good Luck, and hope you find what you're looking for.
|||

Dear Sir,

Thanking you for your such a valuable suggestion.
I am also seeking job for team environment where i can have such options.. as my current employer dont have much expansion capability. I am good at VB,ASP and at Entry Level for .Net.

So, can you help me in this regard ?

With regards,
< Max_007 >