|
See the question above; you have an index with two columns. That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2.
So, yes, you search those linear. For a table without index, that means scanning the entire table as there is no guaranteed order to the rows.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
"index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2."
(My quote button still not working.)
Re-read that response after looking at post to other response.
That phrasing is a misstatement about how it works.
A table storage in a database is quite complicated. Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster.
There are some implementation details about how that works technically but 'at the end' is the best technical description. Clustered indexes do this differently but not specifically in terms of sorting anything.
After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself.
Sorting is not something that is related to the table nor even to indexes. In SQL you can specifically ask for a specific sort order. If you do not do that then how the records (rows) are returned is somewhat random. And that becomes even more true when things like joins are factored in.
|
|
|
|
|
jschell wrote: That phrasing is a misstatement about how it works.
Please, explain. Please do? Pretty please?
Do explain how it works?
jschell wrote: A table storage in a database is quite complicated
Nope, and quite well documented.
jschell wrote: Often, but not always, records (rows) are inserted at the end of the table. Doesn't matter whether indexes exist or not. The reason for doing it that way is because it is faster.
Tables are without any inherent order, by design and definition. Indexes aren't in the table, they are entities outside the table that may be updated.
jschell wrote: After the insert the index(es) are updated. That involves storing some of the data from the row (columns in the index) and a pointer to the row itself.
Hence, the "create index" expects some columns to sort on.
jschell wrote: Sorting is not something that is related to the table nor even to indexes
If you need not sort, you need no index. So yes, it might be a tad related. The definition in proper schooling talks more about lookups than sorting, as that is more common.
jschell wrote: In SQL you can specifically ask for a specific sort order
Thanks for explaining that, that's really something new.
Bastard Programmer from Hell 
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
modified 10-Feb-24 19:17pm.
|
|
|
|
|
I have a window based application that creates new database every month programmatically and in that database i have single table which consists of 50 columns which also . But the problem is after crating database programmatically it falls in recovery mode. So my question is Why database falls in recovery mode.
|
|
|
|
|
Your question is very generalized and can have any of many reasons why it crashes, without proper info (remember that we cannot see your screen to guess the cause) - some causes might be Incomplete Transactions, Corrupted Log Files and so much more...
Without specific details about your database creation process and the exact error messages you are encountering, it's challenging to provide a precise solution.
|
|
|
|
|
This design sound like a disaster waiting to happen - oh wait it has happened.
What possible reason would you create a new database for every months data?
I'd wager that the 50 column table is not normalised, leading to a myriad of problems down the track.
As to why the database falls into recovery mode, the Great Ghu only knows but given the above issues it is the least of your problems.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
As has already been pointed out to you, that design is a bad idea. See SQL Server: The Problems of Having Thousands of Databases on a Single Instance[^]
It's bad for lots of other reasons too. Say you wanted to produce some MI for an entire year's worth of data for your application. You would have to query 12 separate databases ending up with something like
SELECT Col1, Col2 FROM DB1.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB2.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB3.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB5.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB6.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB7.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB8.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB9.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB10.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB11.dbo.MyTable
UNION ALL
SELECT Col1, Col2 FROM DB12.dbo.MyTable; And did you spot my deliberate mistake?
Even if you have a new table in the same database each month it doesn't really get any better...
SELECT Col1, Col2 FROM MyTable01
UNION ALL
SELECT Col1, Col2 FROM MyTable02
UNION ALL
SELECT Col1, Col2 FROM MyTable03
UNION ALL
SELECT Col1, Col2 FROM MyTable04
UNION ALL
SELECT Col1, Col2 FROM MyTable06
UNION ALL
SELECT Col1, Col2 FROM MyTable08
UNION ALL
SELECT Col1, Col2 FROM MyTable09
UNION ALL
SELECT Col1, Col2 FROM MyTable10
UNION ALL
SELECT Col1, Col2 FROM MyTable11
UNION ALL
SELECT Col1, Col2 FROM MyTable12; But introduce a Date column into your table and suddenly your code becomes so much clearer
SELECT Col1, Col2, YEAR(MyDate), MONTH(MyDate) FROM MyTable; Next steps are to look at normalizing your single table - see Database normalization description - Microsoft 365 Apps | Microsoft Learn[^] for some starters.
Address these sort of problems first, to take some of the heavy lifting off your SQL Server instance and you might very well cure what ever it is that is going wrong for you.
Failing that you are going to have to examine the Windows Application log to find out more - View the Windows application log (Windows) - SQL Server | Microsoft Learn[^]
|
|
|
|
|
Nilesh M. Prajapati wrote: creates new database every month...Why database falls in recovery mode.
My guess is that it would be due to how you 'create' it.
Certainly if you start with a clean install of SQL Server (just the normal install databases) and if you run you app and it impacts that database, then yes it is specifically how you are creating it.
|
|
|
|
|
The SQLite documentation says that SQLite is thread safe for reading but not for writing. Specifically, they say you can have multiple threads reading from the DB, but only one thread should write at one time.
Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?
IOW, does reading need to be serialized as well?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
It's "rereads" that are (usually) the problem. If one is writing, and another reading, one for one makes little difference; if the record aren't related. On the other hand, if you're not writing "complete" records at one time, then they have to be "locked".
For "rereads" you have to check for "dirty" (e.g. timestamp changes). "Screen data has been updated".
With related records, you may need need to lock a table or the entire DB (for a very short time).
All calls should be async to avoid UI waits.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Well that seems related to the issue that I'm encountering. Occasionally I'll receive a "Access denied - Database locked" error message, and I can't understand it because all of my writes are synchronized around a single mutex. So I thought maybe I can't read either when a thread is writing to the DB.
It's an INSERT statement that's receiving the Database locked message, so the only other thing I can think of is that there's something wrong with my threading code and it's actually attempting two writes at once.
The difficult we do right away...
...the impossible takes slightly longer.
modified 10-Nov-23 21:46pm.
|
|
|
|
|
An insert implies an index update; or a "split" somewhere. A simple append might be different.
In effect, the "internal" state becomes dirty. Because it is "lite".
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Richard Andrew x64 wrote: Do you think this means that I should also avoid two threads accessing the DB at one time if one of the threads is reading and the other is writing?
Yes.
SQLite CVSTrac[^]
|
|
|
|
|
I'm pulling my hair with this one:
(There's a unique index on the [DOMAIN] field.
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE DNSLOG
SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.' The error is clearly in the ON CONFLICT clause because when I run only the INSERT clause, it does what it's supposed to do. And the UPDATE statement works when I run it by itself.
The actual error message is "SQL Logic error near "DNSLOG" syntax error".
Any idea what the syntax error could be?
****SOLUTION****
It's not supposed to have the table name in the "DO UPDATE" statement. IOW:
INSERT INTO DNSLOG ([DATETIME], [DOMAIN], [BLOCKED]) VALUES (133436157350078746, 'optimizationguide-pa.googleapis.com.', 0)
ON CONFLICT (DOMAIN) DO
UPDATE SET HITCOUNT = HITCOUNT + 1
WHERE DOMAIN = 'optimizationguide-pa.googleapis.com.'
The difficult we do right away...
...the impossible takes slightly longer.
modified 5-Nov-23 17:15pm.
|
|
|
|
|
|
|
I would like to have a blank database already written. The list is for monthly entry and exit. Thank you all
|
|
|
|
|
Here is a simple table for SQLite:
DROP TABLE IF EXISTS Times;
CREATE TABLE Times (
Name TEXT,
TimeOn TEXT,
TimeOff TEXT,
);
|
|
|
|
|
Unfortunately using a database is not simple. A post here is unlikely to be able to provide all of the information.
There are tutorials that will explain how to set up a database, then create a schema and finally create a table in that schema. I suspect that is what you want.
You would need to first determine what database you are going to use.
|
|
|
|
|
Entry-Exit System
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
Here is the link I posted to your QA question that was reported then deleted.
Data Models[^] - lots of free, preprepared database schemas.
|
|
|
|
|
I am pretty new using MS SQL. I perform a query over some SQL server table; it returns a record set; but I need to read the last record just to get the value of a certain column.
This is my procedure:
$connectionInfo = array( "Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, $connectionInfo);
$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$connectionInfo = array( "Database"=>$myDB, UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, connectionInfo);
$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$row_count = sqlsrv_num_rows($rec_set);
if ($row_count === false) die( print_r( sqlsrv_errors(), true));
$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, 0);
$first = $reg['column_name'];
$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, $row_count - 1);
$last = $reg['column_name'];
It is clear that the sqlsrv_fetch_array DOES NOT work like that. Does anyone know how to achieve my goal, that is, getting last and first records from that record set without generating another SELECT?
Thank you in advance.
|
|
|
|
|
Have you tried:
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 and then
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 DESC
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
No. The problem here is that I have worked with mysql/postresql and I can retrieve from a recordset any record I want depending on its position in the recordset. So when you have: $var = pg_fetch_result($recordset, $k, 'col'); you are retrieving the 'col' value in the k-th record from that recordset (in PostgreSQL). You can do the same in MySQL. I want to achieve the same in sql server... I have noticed this parameter en the fetch of sql: SQLSRV_SCROLL_LAST in the row parameter of the sqlsrv_fetch_array(resource $stmt, int $fetchType = ?, int $row = ?, int $offset = ?). But for some reason I can not make it run.
|
|
|
|
|
Add a "row number"?
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
