Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>This is a Sybase ASE Answer for a Sybase ASE Question</h2> <p>It applies to any Standard SQL Compliant DBMS; it <strong>does not</strong> apply to others.</p> <p>The answer you have chosen is not correct.</p> <ol> <li><p><code>COUNT()</code> is an ISO/IEC/ANSI Standard SQL expression. It is required to <strong>physically count the rows</strong>, which means it will be slow on a large table. It is required to <strong>not</strong> use the internal memory resident tables or catalogue tables ("metadata"). This is a run-time value, so if the table is active, it will keep changing for every execution</p> <ul> <li><p>What you place within the brackets is very important.</p></li> <li><p><code>COUNT(*)</code> returns the number of rows including nulls</p></li> <li><p><code>COUNT(column)</code> returns the number of rows where <code>column</code> is Not Null</p></li> <li><p>Yes, the placement of <code>DISTINCT</code>is also important. This forces the use of a work table. If you need to count a column that is not Unique, then you do not have a choice; but for unique columns, there is no need to use <code>DISTINCT</code></p></li> <li><p><code>DISTINCT</code> applies to a column or an expression derived from a column;<br> <code>COUNT (DISTINCT *)</code><br> is meaningless ("distinct all columns"), and ASE 15 has a substantially improved parser, which catches such things (previous version woul dhave returned a less accurate error message).</p></li> <li><p>the rows actually read will depend on your ISOLATION LEVEL (the correct count will be returned for the level specified) and the current activity on the database</p></li> <li><p>the cleanest method, that avoids the weird results you are getting, is to use<br> <code>COUNT(PK_column)</code></p></li> <li><p>(Since this is a CW) Never use any form of <code>COUNT()</code> for an existence check, as it physically counts the rows. Always use <code>IF EXISTS</code> with the correct <code>WHERE</code> clause, because it will use the index only.</p></li> </ul></li> <li><p>If you need an accurate count but do not want to read all the rows, there is a function to read catalogue table <code>systabstats</code>, which has a count of rows in each table. This returns instantaneously, regardless of table size. The currency of those values depends on how your server is configured for performance, flushing, checkpointing, etc. <code>systabstats</code> is updated from the memory-resident tables by two commands: UPDATE STATISTICS and "flush stats". Try this:<pre><code>EXEC sp_flushstats SELECT ROW_COUNT (DB_ID(), OBJECT_ID("table_name") )</pre></code></p></li> </ol> <h2>Response to Comments</h2> <h2>This Section is Not Relevant to Sybase</h2> <ol> <li><p>I have provided a clean explanation re your problem, and the subject, without explaining why the other two answers are incorrect, or why your second query returns unpredictable results.</p></li> <li><p>Here is the <a href="http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.blocks/blocks.pdf" rel="nofollow"><strong>Sybase ASE 15.0 Reference/Building Blocks Manual</strong></a>, <code>COUNT()</code> is on page 121. Note that icyrock has misquoted it, and both of you have mis-interpreted it, inadvertently of course. Your starting point was confusion, lack of distinction re <code>*</code> and <code>DISTINCT</code>, hence my attempt at a clear answer.</p></li> <li><p>I made this a Community Wiki, therefore my answer is complete re the subject, normalised, so that it can stand alone as a complete answer to any question re <code>COUNT()</code>.</p></li> <li><p>In response to comments, for those people who have not heard (no offence, but there are a lot of freeware SQL users seeking answers here at SO). SQL is a <strong>Standard</strong> language, invented and progressed by IBM in the 1980's and accepted as a Standard by:</p> <ul> <li><a href="http://www.iso.org/iso/catalogue_detail.htm?csnumber=45498" rel="nofollow"><strong>International Standards Organisation</strong></a>, </li> <li><a href="http://www.xcdsql.org/Summary%20of%20SQL.html" rel="nofollow"><strong>International Electrotechnical Commission</strong></a> and <a href="http://webstore.iec.ch/servlet/GetPreview?id=40405&amp;path=info_isoiec9579%7Bed2.0%7Den.pdf" rel="nofollow"><strong>(Book Format)</strong></a>, </li> <li><p>and copied by <a href="http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt" rel="nofollow"><strong>American National Standards Institute</strong></a> (free publication thanks to good old Digital Equipment Corp) somewhat later.</p></li> <li><p><strong>None</strong> of the OpenSource or freeware "SQL" comply with the <strong>Standard</strong>. They provide some components (language structures, facilities, commands) of the Standard. And of those they provide, they seldom provide the Standard <strong>Requirement</strong> (eg. transaction handling, security).</p></li> <li><p>Therefore what Sybase and DB2 (rigid re Standards) do, and to a lesser extent what MS ("flexible" about implementations) and Oracle (stretches the definitions) do, because they are Standard-Compliant (without arguing the small variations), is light years away from the MySQLs and the PostGreSQLs of the world.</p></li> <li><p>Then there is <strong>non-Standard</strong> or anti-Standard category.<br> MySQL/MyISAM provides <code>COUNT()</code> in a manner that is specificaly <strong>against</strong> the Standard (this is plainly evident from the <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count" rel="nofollow"><strong>MySQL Manual link</strong></a> provided by Lee; good for non-transactional apps).<br> MySQL/InnoDB &amp; BDB provide <code>COUNT()</code> in the Standard-compliant manner.</p></li> <li><p>All Standard-SQL vendors provide a large array of Extensions to the Standard</p></li> <li><p>Where the freeware has value is, they provide quite a number of Extensions (mostly instead of the Standard), to ease coding on web servers. And they are all different.<br> . </p></li> </ul></li> <li><p>The <code>NOT NULL</code> in the table definition cannot be immediately trusted, because the table may well have had null columns in it before the current definition was implemented. Nevertheless, Sybase and DB2 will count throws physical, as per requirement for Standard compliance. You can prove with with a series of counts:<code>SELECT COUNT(column_1) from table_name</code>; and then compare the counts.</p></li> <li><p>The second query will further confuse you, yes, because when the inner table is created, and populated, the count will be accurate. Since you created it with an expectation, it fulfilled your expectation. But that does not prove anything about that original table.</p></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POHow can SELECT COUNT(*) different from count of all records in the table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPerformanceDBA
    1. USPerformanceDBA
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. COCorrect info, but not related to the actual question (see table schema and alternative query @Ickster posted)
      singulars
      1. PO
      1. USJohn
    2. CO@John. Don't worry about the "correct info" The table schema is not relevant (a) I posted a CW; (b) OP could have set the columns NOT NULL after populating the rows with nulls. Please explain how "it is not related" or what the alt query has to do with it (I have not explained why the alt query gets its weird results separately). I trust you gave the two incorrect answers -1 as well.
      singulars
      1. PO
      1. USPerformanceDBA
    3. COYou have made a number of interesting assertions here - and you sound quite well informed -- it would be great if you would provide some links (eg. to the relevant parts of spec) that would support the things you've said.
      singulars
      1. PO
      1. USLee
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload