|
I am new to SQL, especially use SQL in VC++ 6.0 framework.
I am told that creating INDEX on field(s) could speed up a query.
if I create a INDEX like the following
<br />
CREATE nonclustered INDEX IX_XYZ on TableA.field1<br />
Should I use the INDEX name IX_XYZ in some way in the following SELECT statement. Or the following SELECT statement will be carried out automatically based on the INDEX IX_XYZ.
<br />
SELECT * FROM TableA WHERE field1 = xxx:((<br />
|
|
|
|
|
The query optimiser in SQL Server will work out if it can use an index to speed up the query execution. You don't need to specify anything.
|
|
|
|
|
Hello All,
I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.
In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.
<br />
Class Name Age Score<br />
0 Bob 20 78 <====<br />
0 John 26 66 <****<br />
2 Wilson 28 88<br />
1 John 26 77 <****<br />
3 Alice 25 56<br />
1 Bob 20 89 <====<br />
<br />
In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result (a recordset) like the following
<br />
Bob 78 89<br />
John 66 77<br />
Actually, in my application there are only two scores for a given name. One more further question,
If we have another table 'ClassTotalScore' consisting of two fields "Class" , "TotalScore"
<br />
SELECT s1.Name,s2.Score<br />
FROM student_results s1<br />
JOIN student_results s2 <br />
ON s1.Name=s2.Name <br />
AND s1.Age=s2.Age <br />
AND s1.Class<>s2.Class<br />
<br />
Can I add some clause to the above suggested SQL experission to get the sum of total scores of Class=0 and Class=2. Or I have to use another SQL statement to get the sum of total scores of two classes.
Moreover, if either way is possible, I would like to know whether ONE SQL statement is necessarily faster than TWO SQL statements.
Actually, those data in the two tables originally are stored in varibles in my VC++ 6.0 program. However, as more and more data are produced, the PC memory is almost exhaused. Hence, i have to resort to the database technology. I notice that the running speed of the databased based program is 10 times slower than the original program. I wonder if this is normal.
|
|
|
|
|
Try this out.
This query will result like
Bob 78 89
John 66 77
Wilson 88 88
Alice 56 56
(i.e. It will display Wilson and Alice even though they have only one class.)
SELECT A.[Name],[Score1],[Score] "Score2" FROM tblName A,
(
SELECT B.[Name], [Score] "Score1", [NameAge],"Min","Max" FROM tblName B,
(
SELECT ([Name]+CAST([Age] AS varchar))"NameAge",MIN([Class]) "Min",MAX([Class]) "Max"
FROM tblName GROUP BY [Name]+CAST([Age] AS varchar)
) C
WHERE B.[Name]+CAST([Age] AS varchar)=NameAge AND [Class]=[Min]
) D
WHERE A.[Name]+CAST([Age] AS varchar)=NameAge AND [Class]=[Max]
Please let me know if U want a query that will eliminate
the records
Wilson 88 88
Alice 56 56
Regards,
Arun Kumar.A
|
|
|
|
|
Try this query and let me know the result...
SELECT NAME, MIN(AGE)AS SCORE1,MAX(AGE)AS SCORE2 FROM DBT_EMPL_DTL
WHERE NAME IN (SELECT NAME FROM DBT_EMPL_DTL GROUP BY NAME HAVING (COUNT(NAME)>1))
GROUP BY NAME
I have used AGE field, for that you use score field.
Cheers.
Shetty
|
|
|
|
|
Hello All,
i am using ADO in VC++ 6.0 to access a SQL server database table.
I wonder how to put/get BOOL type value into/from the table.
I tried to use _variant_t but I got -1 for '1' when retrieving the BOOL field.
|
|
|
|
|
|
Q> I have a datagrid which is being used to disppay the employee details from employees table but now there is a quiestion how to retrive more tahn one column of the table to a single column of the datagrid without changing the query.
|
|
|
|
|
Wrong thread...
Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.
|
|
|
|
|
Not necessarily, this is dealing with ADO/ADO.NET.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
This is more inclined towards the datagrid functionality and how to display data in it.......and i think thats why the other forums are there for....anyhow its your thought
Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.
|
|
|
|
|
In this case I would say that it is close enough not to be a big deal.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
This is fairly straight forward. You just need to go to the HTML portion of the designer. I would suggest using a template column then use '<%# Container.Item("FirstName") & ' ' & Container.Item("LastName") %>' to set two values to one column.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
Hi
Stored Procedurs r used to perform single task using mutiple tables.
I know only definition ,Can anyone plz tell me how to create dtored procedue with proper syntax with any example.
Thanks
Care Career
|
|
|
|
|
|
Most simply put, a stored procedure is any sequence of program code that executes against a database, and is stored in that database. A single SELECT statement is still a valid stored procedure, in many implementations.
I believe the SQL standard says that a stored procedure is introduced with the keywords CREATE PROCEDURE, but that's about it. Different databases have their own extended dialects of SQL to support looping and branching, and some may support stored procedures in other languages entirely.
SQL Server 2000 supports stored procedures in Transact-SQL, and SQL Server 2005 adds .NET-based 'stored procedures' written in C# and VB.NET. Oracle supports PL-SQL, which is their extended SQL syntax taking cues from the Ada programming language.
SQL Server supports sending multi-statement commands in a single batch operation. The entire batch is parsed and an execution plan formed and potentially cached before the whole thing is executed. You don't need a stored procedure for this. You can still use named parameters in a plain-text query batch, rather than calling a stored procedure. However, in SQL Server, an execution plan for a stored procedure will live longer in the cache than one for a so-called ad-hoc query.
Users can be granted access to execute a stored procedure but not to directly modify or query the tables used by that procedure. In a system I've implemented, the user account that the application logs in as can only execute the stored procedures - it cannot manipulate any of the tables or views. This means that even if it were to be hacked or the login compromised, it still can't do anything that the application couldn't do anyway.
|
|
|
|
|
Dear Folks,
I am new to this forum, S Anand, From Chennai INDIA
There was a very nice tool that was available "Sql Transaction Log Shrink Wizard", is there a similar tool for shrinking the database too?
Regards, Anand
|
|
|
|
|
What's wrong with Enterprise Manager? Right-click the database in the tree, select All Tasks, Shrink Database. For SQL Server 2005 Management Studio, right-click the database, select Tasks, Shrink, Database.
On a real production system you would normally dedicate disks (or RAID arrays) to a specific database's data files or transaction log, and therefore set the files to occupy about 95% or so of the space on the drive/array, and turn off automatic growth.
If you don't intend to make transaction log backups, set the database's Recovery Model to Simple. Otherwise, transaction log space is only reused once the log has been backed up - if you're not backing it up, it will continue to grow. The Full recovery model allows you to restore to the point of failure if the database files or disks containing them are unrecoverable, but the transaction logs are still available, and you have a full database backup.
|
|
|
|
|
HI Mike,
I dont find anything worng with Enterpriese Manager, however the logs that is getting truncated using the tool is of greater advanantge. The DBCC Shrink command dosent shrink to a greater size and that is why my request was. Thanks for your response i got it.
Anand
|
|
|
|
|
Often I face problems while connecting Sql Server2005
|
|
|
|
|
No one sql server is enough on your PC. If you installed something like visual studio on your PC it will auto install sql express. Then if you installed sql server 2005 after you would have both on your box. I would remove sql express if you have sql server 2005 installed.
Ben
|
|
|
|
|
What problems are you having? I have been running both for a while now and have run into few problems. They are both treated as separate servers.
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Hi
I have one table Employee having fields id,Name,Salary (id is primary Key)
I have another table Phone having fields id,phone.(id is forign key)
1)using these two tables I want to get all the details of Employee who does not have phone Nos.
2)& I want details of all employees who have more than 2 phone nos.
Plz send these queries as soon as possible.
Thanks
Care Career
|
|
|
|
|
1)
Select e.* from employee e
left join Phone p on e.id = p.id
where p.id is null
2)
select e.id,p.phone from employee e
join phone p on e.id = p.id
group by e.id, p.phone
having count(*) > 1
You should probably buy a good sql book.
Ben
|
|
|
|
|
Hi,
In my app, I would like to insert data into MS Access. In my databse, have 3 columns-(datetime,datetime,text).Below is my code
strInsert = "INSERT INTO Alarms(Date,Time,description)VALUES ('"+dateTimePicker1.Value.Date+"', '"+dateTimePicker1.Value.Date+"' , 'err orlog')";
:
:
conx.open();
OleDbCommand cmd1 = new OleDbCommand(strInsert, conx);
cmd1.ExecuteNonQuery();
conx.close();
I am getting this error message - "OleDbException was unhandled". I am not idea what was wrong.Can anyone guide me to solve this error.
thanks in advance
cocoonwls
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
