Database

8-Jun-07 9:13

SELECT TOP 10 *<br />
FROM Shareholders<br />
ORDER BY whatEverColumnMakesThemTop

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: top rows

Harini N K10-Jun-07 19:33

Harini N K

10-Jun-07 19:33

Colin Angus Mackay wrote:
SELECT TOP 10 *
FROM Shareholders
ORDER BY whatEverColumnMakesThemTop

Put Descending in the Order by clause to get max. top 10 shareholders. Smile | :)

Harini

SQL Server Speed

3fonov8-Jun-07 3:23

3fonov

8-Jun-07 3:23

What can cause slowness of SQL Server 2005 Enterprise?
When start same DB on Enterprise and Express, express are mach more faster (100 times)
Thanks for advises.

Re: SQL Server Speed

Mike Dimmick8-Jun-07 9:20

Mike Dimmick

8-Jun-07 9:20

Typically your databases on Express are set to the Simple recovery model. This keeps only the transaction logs required to roll back active transactions. It rapidly overwrites the log records once the corresponding transaction is committed.

In contrast databases on the true server editions of SQL Server default to the Full recovery model, which logs everything that's done and keeps log records until the log is backed up, in order that recovery can occur to the point of failure if the data files are lost (and you restore a full backup). If you're not backing up the transaction log through a SQL Server-aware product, they simply keep growing. This may well take longer to scan on opening a database.

Also, SQL Server Express Edition only actually opens databases and performing the roll forward of logged committed operations and rollback of logged uncommitted operations when that database is accessed (this is the 'auto close' option). The full editions of SQL Server open and recover all database at service startup.

The time to perform recovery is also affected by the disk subsystem you're running the process on - it will be directly proportional to the read speed of the disks. If you're running on RAID 5, check that you haven't got a failed disk - your read speed will have gone through the floor. You will also get a complete failure if another drive fails.

Stability. What an interesting concept. -- Chris Maunder

Why this is faster?

Arun.Immanuel8-Jun-07 2:33

8-Jun-07 2:33

Here are the 2 queries I tested for speed.

<br />
select replace(convert(nvarchar(10),getdate(),108),':','') <br />
from tblName

<br />
select replace(convert(nvarchar(10),getdate(),108),':','')*1<br />
from tblName

The former took 6 seconds and the latter took only 4 seconds.
The table tblName has around 5 lakhs of rows.

Confused | :confused:

Regards,
Arun Kumar.A

Colin Angus Mackay8-Jun-07 2:38

8-Jun-07 2:38

Arun.Immanuel wrote:
5 lakhs of rows

Which means?

Rob Graham8-Jun-07 2:51

Rob Graham

8-Jun-07 2:51

Colin Angus Mackay wrote:
Reporting

I think it means he's of the Indian persuasion.

Arun.Immanuel8-Jun-07 2:54

8-Jun-07 2:54

Yes, You are right. Smile | :)

Regards,
Arun Kumar.A

Arun.Immanuel8-Jun-07 2:52

8-Jun-07 2:52

I have 5,00,000 rows in the table tableName

Regards,
Arun Kumar.A

andyharman8-Jun-07 3:38

andyharman

8-Jun-07 3:38

This one creates a column that takes about 13 bytes:

select replace(convert(nvarchar(10),getdate(),108),':','') <br />
from tblName

While this one casts the result into an integer (which takes just 4 bytes):

select replace(convert(nvarchar(10),getdate(),108),':','')*1<br />
from tblName

The second is probably only faster because SQL Server can store the results in less space (or the data takes less packets to pass across your network.

Arun.Immanuel8-Jun-07 5:21

8-Jun-07 5:21

Thank you very much Smile | :)

Regards,
Arun Kumar.A

How to alter table

rzthebeginner8-Jun-07 2:21

rzthebeginner

8-Jun-07 2:21

I want to alter a table using sql query.In that i want to insert new field between other fields,not at the last position.How to do that?

Re: How to alter table

Arun.Immanuel8-Jun-07 2:37

8-Jun-07 2:37

Why do you want to add the column inbetween?

What problem do you face when you add the column at the last?

No matter where the column positions are.

Always try to include the column names along with the statements like:

<br />
select col1,col2 from tblName<br />
<br />
<br />
insert into tblName(col1,col2) values(val1,val2)

Regards,
Arun Kumar.A

Re: How to alter table

Rob Graham8-Jun-07 3:02

Rob Graham

8-Jun-07 3:02

In general you can't, unless you are willing to destroy all the columns that are to come after the new one, then add them back.

In most cases, column order at definition time is not particularly relevant. Unless they are part of a clustered index, the definition order has no relation to the storage order on disk. The only impact is if you do a query of the form "select * from tablename", the definition order becomes the default order for returning the columns. This is bad practice anyway, since it usually means you are fetching more data than you really need; "select col3,col1,col2, col4 from tablename" is the recommended approach, and in that case you have specified whatever return column order you desire.

SQL Connection DialogBox

ScottM18-Jun-07 2:05

ScottM1

8-Jun-07 2:05

Does anybody know how I can get the SQL Connection DialogBox to show up in C#?
You know, the one that comes up when you open query analyzer. I think that I need to use Microsoft.Data.ConnectionUI which I need to install another SDK to use. Does anybody know which SDK this is?

Any help would be appreciated
Thanks

There are 10 types of people in the world, those who understand binary and those who dont.

meaning of @ in sql

emiralp8-Jun-07 1:44

emiralp

8-Jun-07 1:44

Hi,
I am new to sql and don't know much. what does the @ mean ?
FOr ex: select * from table where column= '@param '

thnks !

wout de zeeuw8-Jun-07 1:53

wout de zeeuw

8-Jun-07 1:53

It indicates a parameter in so-called parameterized queries (as opposed to adhoc queries). Parameterized queries are safer than adhoc queries because adhoc queries are sensitive to SQL injection.

Wout

kubben8-Jun-07 1:53

kubben

8-Jun-07 1:53

@ is used for variables. You used it in parameters coming in on a stored procedure. You can declare local variables "Declare @cnt as int" You can also declare table variables.

In your example above it is a parameter that is being used to pass in the column.

Ben

Colin Angus Mackay8-Jun-07 2:41

8-Jun-07 2:41

emiralp wrote:
what does the @ mean ?

In general it indicates that this represents a parameter name. However, in the example you've given it means nothing as you've enclosed it in apostrophes and has become part of a string literal.

Colin Angus Mackay8-Jun-07 2:40