Why is the sum() of zero numbers not zero?

This is a requirement from the SQL standard. I'm most familiar with the 1992 standard. Quoting from it, with emphasis mine:

  General Rules

  1) Case:

  a) If COUNT(*) is specified, then the result is the cardinality
       of T.

  6.5

  b) Otherwise, let TX be the single-column table that is the
       result of applying the  to each row of T
       and eliminating null values. If one or more null values are
       eliminated, then a completion condition is raised: warning-
       null value eliminated in set function.

  2) If DISTINCT is specified, then let TXA be the result of elimi-
  nating redundant duplicate values from TX. Otherwise, let TXA be
  TX.

  Case:

  a) If the  COUNT is specified, then the
       result is the cardinality of TXA.

  b) If AVG, MAX, MIN, or SUM is specified, then

  Case:

  i) If TXA is empty, then the result is the null value.

In short, NULLs are always excluded from the result of SUM, MAX, MIN, and AVG. I can't tell you exactly why this is, other than I think it's useful to be able to distinguish between a sum of a single value of 0 and a sum of an empty set.


How to insert in a table with only an IDENTITY column?

From the documentation:

  DEFAULT VALUES
  Forces the new row to contain the default values defined for each column.

So:

INSERT dbo.TABLE DEFAULT VALUES;

In addition:

always use the schema prefix
always terminate statements with semi-colons


SQL Server 2008 and Management Studio - Intellisense is Patchy

It should revive when you refresh it's cache. In the menu is Edit - Intellisense - Refresh Local Cache (ctrl+shift+r). Or you can use another auto complete client like SQL Prompt or SQL Complete. First one has only paid version, while the second has also a free version.


mysqldbcompare - ERROR: The list of objects differs among database

Check that you have enough permission on both databases to see and select your tables.

You could do a poor's man mysqldbcompare to check that:

mysqldump -d -h server1 -u user1 -ppassword1 pressflow6 > objects1.sql
mysqldump -d -h server2 -u user2 -ppassword2 pressflow6 > objects2.sql
diff objects1.sql objects2.sql


Oracle 12.1 string literal on varchar2 with trailing space

For anyone facing the same problem: I applied patch bundle 12.1.0.2.170117 and everything seems to be ok now... :/


Grant MonitorAdmin / PROCESS privilege to user

From the MariaDB documentation:

  To set a global privilege, use *.* for priv_level.

So your command should be

GRANT PROCESS ON *.* TO dynatrace;


pulling multiple entries from a match of two tables

You should filter using pairs:

WHERE (attribute_entries.title = 'ford' AND attributes.title = 'manufacturer')
      OR
      (attribute_entries.title = 'whatev' AND attributes.title = 'nickname')

Or you can use IN

WHERE attribute_entries.title IN ('ford', 'whatev')
      AND attributes.title IN ('manufacturer', 'nickname')

In case you need both conditions true you can use EXISTS:

WHERE EXISTS(<your first query>)
      AND
      ExISTS(<your second query>)

You can check it :db<>fiddle here


Insert and Update with AES_ENCRYPT is mangling character set on double byte script

Forcing utf8mb4 does the trick. Run a query against MySQL immediately after making the connection with:

SET NAMES'utf8mb4';

Alternatively, use these settings in your my.cnf file to get the same effect:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

(Note: These settings are defaults (for the most part) in MySQL 8.0 and MariaDB 10.3, so this probably isn't a problem there.)

Chinese (and other) scripts are multibyte - standard utf8 allows 3 bytes per character - fine for plain text but using AES_ENCRYPT ON 3 bytes requires another byte - thus setting utf8mb4 allows the required 4 bytes per character.


I was doing it the wrong way. It's not in the "Cell Data" tab, It's in the "Dimension Data" tab. The "Enable Visual Totals" checkbox has to be checked.


Stress test MySQL with queries captured with general log in MySQL

Here is a more direct approach using brute force mysql: Setup the general as a MyISAM table. On installation, you will find the table mysql.general_log as a CSV table.
Rather than reinventing the wheel and doing a whole lot of typing, please read my past posts:

Nov 16, 2011 : Log MySQL DB changing queries and users
Feb 11, 2012 : MySQL general log
Jan 08, 2012 : How to enable MySQL general log?

Once you get mysql.general_log converted to MyISAM and queries are getting recorded, simply access the log like any other MyISAM table.
Here is the structure of the MyISAM version of mysql.general_log
mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

From here, just write all content starting with a query:
SQLSTMT="SELECT argument FROM mysql.general_log"
SQLSTMT="${SQLSTMT} WHERE LEFT(argument,6) IN"
SQLSTMT="${SQLSTMT} ('INSERT','UPDATE','DELETE','SELECT')"
mysql -uroot -p -ANe"${SQLSTMT}" > SQLCommands.sql

There you go.
Give it a Try !!!


SQL Server 2008 R2 .mdfs write protected

Might be OS-level access rights--every now and then I hit something like this. In Windows Explorer, right click on the file and select properties. Go to the Security tab. Are there any entries in the "Group or usernames:" box? These are the entities that can manipulate the file. Yes, even if you are local and domain admin, you can be blocked from accessing a file.

However, you can't be blocked from managing access rights over the file. Click on "Add...", grant yourself (or whoever) "Full Control", and you should be good. (If, of course, this is the actual problem.)


How to show missing rows with default values

I have updated your fiddle with:

add one more category for to see the query is correct when there is more than 1 category
alter amounts making category amount sets different and altering zero values for to see the records inserted additionally.

Edited fiddle.

The final query is

With
Categories AS (
  SELECT DISTINCT Category
  FROM Category
  ),
MonthCategories AS (
  SELECT m.MonthNameLong, cat.Category
  FROM Month m, Categories cat
  )

select mc.Category, mc.MonthNameLong, coalesce(cat.Amount, 0.0) as Amount
FROM MonthCategories mc
LEFT JOIN Category cat ON cat.Month = mc.MonthNameLong
                      AND cat.Category = mc.Category;

Of course you may "pack" the query combining CTEs to one and/or moving CTEs to subquery.

PS. If there is only one category in the table you may simply replace s.Category with MAX(s.Category) OVER (ORDER BY s.Category) as Category in output list of your initial query.


Online text based game design - Am I linking the databases correctly?

Yes, it's correct, although, as noticed by yourself , it's pretty crude.

This has been asked months ago. Why didn't anyone answer it yet?

Are you still working on this project?

I wonder if you really have the right skills to complete this... If it's indeed interesting, I could help you with the database architecture.

One word of advice: MySQL does not work well with huge amount of relational data.

Regards, Marcus Vinicius.


ROW_NUMBER() without PARTITION BY still generates Segment iterator

I found this 6 year old blog post mentioning the same behavior.

It looks like ROW_NUMBER() always includes a segment operator, whether PARTITION BY is used or not. If I had to guess I would say this is because it makes creating a query plan easier on the engine.

If the segment is needed in most cases, and in the cases where it's not needed it's essentially a zero-cost non-operation, it's a lot simpler to just always include it in the plan when a windowing function is used.


Troubleshooting TempDB for Performance Bottlenecks

Tempdb is used to hold:

Temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.To capture sessions which are causing tempdb allocation ,try to use sp_blitz or refer this article more insight into tempdb. https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/


Postgresql streaming replication - choose databases to sync

No, traditional streaming replication cannot do that.

To replicate per database, you would have to use logical replication instead.  But this is not available until v10.  (Certain pieces of it were available back to 9.4, but using it required some very deep expertise until v10 when a user-friendly front-end was added)


My question is ,is there a way to read logrecord output of fn_dblog(null,null) from sql

Reading transaction log using fn_dblog is not supported by Microsoft. So you should avoid running this command on production server. You can run this on UAT/Test environment.

What do you exactly want to read from output of fn_dblog, can you be precise. There are myraids of information you can get from output of fn_dblog. Its not easy to read the output and one requires some level of expertise to read it.

I will give you a Demo.

BEGIN TRANSACTION TRANSACTION_NAME
SQL QUERY
COMMIT TRANSACTION OR ROLLBACK TRANSACTION

--Then use below query to filter out records related to the transaction

USE DATABASE_NAME
GO
SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitId],
[Page ID],
Description,
[Lock Information]
FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(
select [Transaction ID] from
fn_dblog(Null,Null)
where [Transaction Name]='TRANSACTION_NAME'
)

You can run

SELECT * FROM FN_DBLOG(NULL,NULL)

To see all column names which fn_dblog would produce

Example: I would show what all things are logged when simple update command is fired

Begin tran T1

update [dbo].[IXBUILD] set C2='XXXXXX' where C1=1

Commit

Its has one row for begin tran one for commit and one regarding update operation. You can see LOP_BEGIN_XACT for beginning of transaction and COMMIT for commit of transaction.
LOP_MODIFY_ROW says a row was modified like we did in update statement.
LCK_Clustered index is in picture because table had CI and row must have been exclusively locked for update
Then comes hexadecimal page ID which actually says what page had row which was modified
Then last column Includes the lock Information. The lock which was taken on

HoBt 72057594041860096:ACQUIRE_LOCK_IX OBJECT: 8:613577224:0 ;ACQUIRE_LOCK_IX PAGE: 8:1:344 ;ACQUIRE_LOCK_X KEY: 8:72057594041860096 (8194443284a0)

Database ID=8 file number 1 and page 334. You would also see KEY value
There is also a log sequence number for every transaction and column Log Record Length which would tell you LSN and size of log record for particular operation

This is just a bit of information. There is too much to derive from content of transaction log and it would be difficult to write down all here. But I can suggest you various blogs of Paul Randal where you can find him writing about how to take help of the output of fn_dblog to get various information.

Time Pass with Transaction Log

Another dive into Transaction Log

Read various articles by Paul you would learn a lot about contents of transaction log

Tracking page split using Transaction Log

How to tell who changed Log file characteristics

Using fn_dblog,fn_dump_dblog and restoring STOPBEFOREMARK using LSN


SQL Server Setup has encountered the following error: Mixed mode assembly is built against version 'v2.0.50727'

Not a direct answer but some suggestion:

Use SQL Server Installation Center to start: System Configuration Checker. This tool will check for conditions that prevent a successful SQL server installation.

You can alterantivly:

1) Check setup log files at %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log

2) Check Your .net 4.0 installation and search your GAC for redundant dll files:


phpMyAdmin can use a special database, called the phpMyAdmin Configuration Storage, to enable a whole range of advanced features, including the export templates. Along with this, there is usually an additional user, the controluser, added to give phpMyAdmin permissions to work with the database in a shared environment. While phpMyAdmin makes efforts to work around any of these through a zero-configuration feature, it's suggested that to use these features, the database and tables must exist, and the controluser and table names must be set in the configuration file, config.inc.php.
Since you are using the Debian packaged version, the installer should have attempted to create (or migrate) all of this for you automatically. Perhaps there was an authentication problem or some other error during installation. You should try to run the post-install script again with dpkg-reconfigure -plow phpmyadmin.
For a user who has installed their own phpMyAdmin outside the package manager, I would suggest following the installation steps from the documentation; create the control user, import the sql/create_tables.sql, and add the configuration directives to config.inc.php. But in this case, the package manager should do all of that for you and attempting to manage things by hand could cause difficulty.


MySQL query to display inserts/updates/deletes/reads per second

For example to get "reads per second" I've tried querying information_schema.global_status using com_select/uptime but that gives me an all time average.

Yes, "all time" since the MySQL instance was started.

  Maybe I need to store results every so often and compare to those?

Yes, I think this could be the way to go as I don't think there are any stats in MySQL for historic status variables.

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.global_status
WHERE VARIABLE_NAME IN ('COM_SELECT', 'COM_INSERT', 'COM_UPDATE', 'COM_DELETE');

Then wait for an interval of your preferred length, and then re-run the query, and store the difference somewhere. (Obviously, running the query itself is a SELECT, so it will influence the result, as will an INSERT query be if you store the results in the same MySQL instance.)

For reference: Server Status Variables: Com_xxx


MySQL 5.1: change datadir variable value in my.cnf

Changing the datadir requires the following steps: 1) Stopping the service completely mysqladmin shutdown / service mysql stop. 2) Changing the options on the config file for everything that points to the old location to the new one 3) moving the actual files and directories at the old datadir manually to the new location with cp/rsync (make sure that it has the right permissions in the end, normally owned by the mysql user) 4) Starting the server again
You can check that new values on my.cnf have effect by executing: mysqld --print-defaults which will confirm you that the options changed are actually applied and that there are no syntax errors on the file.


How to get count of an object, through 3 different tables in postgres with ID's stored in each table

select c.prefered_contact_method,count(*)
from orders  o
inner join contact c on o.contact_id=c.id
inner join order_details od on od.orders_id=o.id
group by c.prefered_contact_method


Mysql multiple check and multiple update queries

I can suggest little more longer method, but work fine:

Load file into table, for example TableB (original in this case TableA), TableB has only PK - id
Update - before INSERT

UPDATE TableA t1 INNER JOIN TableB t2 ON t1.id = t2.id

Insert

INSERT INTO TableA
SELECT *
FROM TableB t1
WHERE NOT EXISTS
      (SELECT id FROM TableA t2 WHERE t2.id = t1.id)

I prefer this way, because:

it fast
it flexible - steps in-dependent and logic for UPDATE/INSERT could be
changed separately


Will a SP which writes records fail if accessed by a db_datareader role?

If the same user owns both table and stored procedure (usually dbo), then permissions are not checked. This is known as "ownership chaining".

So you could have a DENY on the table and it won't be checked. db_datareader makes no difference: it isn't checked.

If the owners are different (and note that it's the owner of the schema that matters) then rights will be needed. See the MSDN example above.

Anyway, you need to catch the errors to make sure, either by TRY/CATCH or via SQL Profiler


Some queries slower in MySQL 5.5 than 5.0?

Disabling the binary log on the slave fixed it. This makes me worry about when we upgrade the master to 5.5, but for now things are working much much better.

Edit: The issue was sync_binlog; now that it's set to zero, everything's happy.


Executing stored procedures in oracle inside a package using dbms_job

If you just want to run the job now then don't add a date/time.
You can use this as a test case, run as an anonymous block to verify it works then modify your package.

DECLARE
l_jobno1 NUMBER;
BEGIN
dbms_job.submit(l_jobno1, 'SP_ABC();');
END;

You should definitely qualify the procedure name with the schema owner as in
XYZ.sp_ABC()


Filtering extended events with configuration table

I would say it's better to collect the information then filter out at reporting time, because then at least you have it.  Constantly updating the session seems fiddly and someone else might be able to make use of it: "Oh I hear you are tracking logins over time, do you track login X?"

Also, have you thought about using the histogram target?  This might be a good use case for it.  There would be a single entry per login with a count property indicating how many times that login has logged in during that extended event session.  You would have to record the start and end time of the Extended Events (XE) session for it to be meaningful, but here's a simple example:

IF EXISTS ( SELECT * from sys.server_event_sessions WHERE name = 'xe_watchLogins' )
DROP EVENT SESSION [xe_watchLogins] ON SERVER
GO

CREATE EVENT SESSION [xe_watchLogins] ON SERVER
ADD EVENT sqlserver.login (
    ACTION ( sqlserver.username )
)
ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.login', source = N'sqlserver.username' )
WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF )
GO

ALTER EVENT SESSION [xe_watchLogins]
ON SERVER
STATE = START;
GO

/* Sample output from the histogram
<HistogramTarget truncated="0" buckets="256">
    <Slot count="4">
        <value>someDomain\bob</value>
    </Slot>
    <Slot count="1">
        <value>someSqlUser</value>
    </Slot>
</HistogramTarget>
*/

SELECT IDENTITY( INT, 1, 1 ) AS rowId, GETDATE() AS dateCreated, CAST( st.target_data AS XML ) XEhistogramData
INTO #tmp
FROM sys.dm_xe_sessions s
    INNER JOIN sys.dm_xe_session_targets st ON s.[address] = st.event_session_address
WHERE st.target_name = 'histogram'
  AND s.name = 'xe_watchLogins'

-- Optionally create xml index
ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( XEhistogramData );
GO

SELECT
    h.c.value('(value/text())[1]', 'SYSNAME') AS loginName,
    h.c.value('@count', 'INT') AS logins
FROM #tmp t
    CROSS APPLY t.XEhistogramData.nodes('HistogramTarget/Slot') AS h(c)
-- WHERE NOT EXISTS ( ... )

The histogram defaults to 256 buckets or distinct login names in this example.


What does: SET @Variable1 = (Select field1 From INSERTED) mean?

The problem is that the trigger was initially written with very simplistic logic and with minimal testing - it assumed that the update or insert could only ever affect a single row at a time.

The line you are asking about is attempting to assign a single value to a variable from a table that may contain more than one row (triggers in SQL Server fire per action, not per row). Different forms of variable assignment behave differently when there is more than one row to choose from, for example:

DECLARE @foo TABLE(id INT);
INSERT @foo VALUES(1),(2);

DECLARE @id INT;
-- this will succeed, picking a single, may-as-well-be arbitrary row:
SELECT @id = id FROM @foo;
PRINT @id;

-- this will fail:
--SET @id = (SELECT id FROM @foo);
--PRINT @id;

If you run the second version, you will get an error message you've probably already seen from your trigger:

  Msg 512, Level 16, State 1
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Not all is lost - your trigger can be re-written to handle multiple rows being affected - I am going to make the assumption that if you insert two rows and only one of them fails, you want the whole transaction rolled back, even though the other row was perfectly fine. (Also, your initial code populates @errstr but never uses it, so I've just left that out for now.)

ALTER TRIGGER [dbo].[DocDist_Dup_Check]
ON [dbo].[DocumentDistribution]
AFTER UPDATE, INSERT
AS
BEGIN
  SET NOCOUNT ON;
  SET ANSI_WARNINGS OFF;

  IF EXISTS
  (
    SELECT DocumentDistDocID, DocumentDistSourceID
    FROM dbo.DocumentDistribution AS dd
    WHERE EXISTS
    (
      SELECT 1 FROM inserted AS i
      WHERE i.DocumentDistDocID = dd.DocumentDistDocID
      AND i.DocumentDistSourceID = dd.DocumentDistSourceID
    )
    GROUP BY DocumentDistDocID, DocumentDistSourceID
    HAVING COUNT(*) > 1
  )
  AND EXISTS
  (
    SELECT 1 FROM inserted
    WHERE DocumentDistSourceID NOT IN
    (
      SELECT DocumentSourceID FROM dbo.DocumentSource
      WHERE DocumentSourceName IN
      ('eBinder','eBinder - West Division',
       'Consolidated e-Binder','MA Consolidated eBinder')
    )
  )
  BEGIN
    ROLLBACK TRANSACTION;
  END
END

I think I have your logic correct; please test on a restored backup or dev system.

You might consider implementing this as an INSTEAD OF trigger - this allows you to either insert or not depending on your business logic (or only insert the rows that don't violate your constraints), instead of always inserting and then sometimes rolling back.

You might also consider implementing a unique constraint (or maybe a filtered unique index or an indexed view, if the duplicates only need to be enforced on a subset) if the primary purpose of this trigger is to prevent duplicates.


SQL Server Availability Group configuration steps

The learning material is incorrect.  You have to enable the high availability feature and restart the SQL Server service before you can add a SQL Server to an availability group.


Here's where the problem creeps in. The plan contains a Constant Scan defining the output.

  CASE WHEN CASE WHEN (1) = (1) THEN (1) ELSE (0) END = (0) THEN '' ELSE CONVERT_IMPLICIT(varchar(1),CASE WHEN (1) = (1) THEN 'asdfg' ELSE NULL END,0) END

Why does it down-cast to varchar(1)? I guess it takes the length of the first string it encounters during optimization (presumably the empty string) with a floor of 1. If I change the fall-through result to return 'yy' the implicit cast becomes varchar(2).

I think the best work-around would be to explicitly cast the result:

RETURN convert(varchar(300), '')

Bizarrely, it's also corrected by this:

declare @x sql_variant;
SELECT @x = dbo.GetPayload2(1);

select Result = @x, Property = SQL_VARIANT_PROPERTY(@x, 'MaxLength');

Result    Property
--------  --------
asdfg     300


That is called a column alias.

An alias is used to give columns a new name for the result set. This can be used to name a column based on an expressions (because otherwise the expression wouldn't "have" a name) or to rename columns, e.g. because the select list contains columns from different tables with the same name:

select t1.foo as t1_foo,
       t2.foo as t2_foo
from t1
   join t2 on ...

returns a result with the columns t1_foo and t2_foo

or

select avg(salary) as avg_salary
from employees

Btw: using a reserved word as an object name (count) isn't such a good idea in the first place.


The closest internal documentation I can find on the RM is from the PSS team here: How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

Which specific to your question on which internal processes may contribute to changing the indicator there is no specific documentation that answers this...that I can find. The article references actually shows that there is a startup trace flag 8020 that can be used (with extreme caution) to narrow down what is triggering RM.

I think the main bit of text from the article above would be this:

  There are memory broker decisions that come into play at this time.   What the information is is saying is that a cache or the cache’s predicted usage will exceed internal targets and we need to start doing cache cleanup.    By looking at the memory broker, ring buffer entries, you can see the behavior that broker it asking of RM.   Using the clock hands you can see the internal and external hand movement.   This will help you determine which caches are involved.    The dbcc memorystatus() show good details and last notification states as well.

Not sure if that answers the question in full, but is about as close to what I can find online.


MySQL - get latest record when date and time are two fields

For questions like this, CREATE TABLE statements together with INSERT statements for sample data is truly useful. Even better is a Fiddle or similar. Right now, all I can do is give you some ideas, but I won't be able to test it.

Looking at your query:

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X
  WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

There are probably some syntax errors ( un-balanced ")" for the sub-select, or at least they are in the wrong place ), but you should not use the VALUES clause together with a sub-select, better to re-write like:

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer)
FROM tbl Y
WHERE timeStamp(date,time) =
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X
  WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

As a side-note, avoid using reserved words as identifiers (date, time, timestamp).

The WHERE clause also looks wrong, I assume 3003 and 'ABCD' are values for totalOdometer, vno. Is this what you mean?

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno)
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer)
      , 3003, 'ABCD'
FROM tbl Y
WHERE timeStamp(date,time) = (
  SELECT MAX(timeStamp(X.date,X.time))
  from tbl X
  WHERE vno= 'ABCD'
    AND IMEI = 'ABCD'
);


You could formalize the approach that you're already using and make it more efficient to both maintain and query.  Consider an ERD like so:

Note that all of your hierarchy is flattened into a single set of columns.  There is an involuted relationship so that each child entity in the hierarchy can point to it's immediate parent.  I've also included left and right visitation numbers which would really help you with your price lookups.  Lastly there is a Level column to store the rank within the hierarchy.

If you're not familiar with visitation numbers please have a look at my answer to this question, where I go into quite a bit of detail.

So why is this better than what you're doing now?

You only track each item in the hierarchy once.  Instead of having
4001 show up in the channel column many, many times, you just have
one record with 4001.
The structure is flexible and allows for insertion of new levels at a later time.  It also allows for uneven trees, say for example ZONE 3001 is subdivided into three SUBZONEs and that cities are under the subzones there (but go direct to zone outside of 3001).
All of your prices are in exactly one place (which doesn't need any reading logic that goes outside of normal SQL queries.  Instead of having to have logic that joins a price to different Entity tables depending on what range the EntityID is in, you have one consistent price retrieval query.
You don't need coded meaning in your identifiers so you won't run into trouble when you get too many entities at any one level, thus breaking your coding scheme.
The price retrieval query is dead simple:

-- Look up the left and right numbers for the store in question:
declare @StoreLeft int
declare @StoreRight int
select @StoreLeft = H.left, @StoreRight = H.right
from HIERARCHY H where H.ID = @DesiredStoreId

-- Look up the lowest level (most granular) price for this store:
select TOP 1
  P.price_amt
, P.currency
from PRICE P
  inner join HIERARCHY H
    on H.Left <= @StoreLeft and H.right >= @StoreRight
  inner join ARTICLE A
    on P.article_id = A.id
where A.id = @TheArticleInQuestion
order by H.Level ASC   -- ASC or DESC depends on how you count levels.


Query shows sort cost,even when required index is available

In query plans, ordered property set to true means that an IAM-driven scan was not done. The data was read in the logical order according to the index definition.

Hash join does not preserve order. Your first query has a hash join so the explicit SORT is needed at the end. One way to avoid the sort in the query is to do a nested loop join with LogTable as the outer table. On my machine I was able to accomplish this with a variety of hints:

SELECT  DI.LogID
FROM LogTable DI
        INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID
        WHERE  DP.UserID = 1
ORDER BY DateSent DESC
OPTION (MAXDOP 1, LOOP JOIN, FORCE ORDER, NO_PERFORMANCE_SPOOL);

This is a very inefficient query plan because the right index is not defined on the LogTable_Cross table. A seek is done against that table, but only one the UserId column:

Filtering on LogID is done in the join itself. I can create an index that better supports the query plan that I'm looking for:

CREATE INDEX IX_LogTable_Cross ON LogTable_Cross (LogID, UserID);

The creation of that index does not guarantee a query plan without a sort. SQL Server may estimate that a plan with a sort has a lower cost. However, if I eliminate all hints except for the LOOP JOIN then I get a reasonably efficient query plan without a sort:


To answer this, I did the following. I used PostgreSQL 9.6 on db-fiddle (a very useful site for putting up code to help ask good questions!). All other servers should act the same way (unless you're using MySQL without ONLY_FULL_GROUP_BY when you can get, ahem..., unpredictable results. You should never do this!). p.s. SQLite actually models itself on PostgreSQL, so any results from one are normally applicable to the other!

CREATEed my TABLEs:

CREATE TABLE customer
(
  id SERIAL,
  fname VARCHAR (20)
);

CREATE TABLE sales_order
(
  order_id SERIAL,
  cust_id INTEGER,  -- put in FK stuff yourself
  sales_rep INTEGER
);

Input data:

INSERT INTO customer (fname) VALUES ('cust_1'), ('cust_2'), ('cust_3');
INSERT INTO sales_order (cust_id, sales_rep)
VALUES
(1, 111), (1, 121), (1, 129), (2, 111), (2, 121), (3, 111), (3, 129), (3, 129);

Running your original query (see db-fiddle here), gives the error

Query Error: error: column "so.sales_rep" must appear in the GROUP BY clause or be used in an aggregate function.

If you modify your query slightly, as shown below (db-fiddle)

select c.id, c.fname, count(*)
from customer c inner join sales_order so
on c.id = so.cust_id
group by c.id, c.fname, so.sales_rep  -- Note addition of so.sales_rep!
having so.sales_rep = 129;

you get the correct answer

id  fname   count
3   cust_3  2
1   cust_1  1

I personally would find it more helpful to include the sales rep's name (or join it to a sales_rep table to include his/her name (db-fiddle).

The HAVING clause is looking for so.sales_rep in its processing, but can't find it in your original - adding it in the GROUP BY gives the HAVING the information it needs to only produce results for sales_rep no. 129!


I did as described here and it solved my problem.
So basically I added the following to [mysqld] section in my.cnf files:

Master-1:

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

Master-2:

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2


There are two ways to efficiently (read: quickly) load 7000 rows.

LOAD DATA INFILE -- After you have built a 7000-line CSV file.
"Batch" INSERT -- like INSERT INTO t (a,b) VALUES (1,2),(5,6),(9,2), ...; -- Be cautious about the number of rows.  100 to 1000 is a good range of what to do at a time.

max_allowed_packet=536870912 -- NO, not in a tiny 2GB VM; change to 16M.  Other likely settings to check:

key_buffer_size = 10M
innodb_buffer_pool_size = 200M

I assume your tables are InnoDB??


How can I use expressions as arguments to EXEC?

Yes, this is a limitation in T-SQL. There are several scenarios where you can't build up a string dynamically, but rather have to do so beforehand.

For example:

RAISERROR('String' + @variable,1,0);
EXEC dbo.procedure @param = 'String' + @variable;

There are other cases where it is valid, but usually just for assignment, not for passing or doing anything constructive with the result, e.g.

DECLARE @var VARCHAR(32) = 'String' + @variable;

T-SQL is just a quirky language with a lot of parsing peculiarities and odd rules. I think it's just the nature of a long-evolving language with different folks in charge of implementation at different times (made more obvious by their changing stance on adhering to things like the ANSI standard).


Table structure for many custom profiles

You can use what is called a SuperType / Subtype data structure. You have one SuperType table that contains information that is the same for all subscribers:

[SuperType]
SubscriberId
FirstName
LastName
Email

Create a SubType table for Ecommerce

[SubType Ecommerce]
SubscriberId (Foreign Key to SuperType)
Address1
Address2
City
State
Zip

Create a SubType table for Cinema

[SubType Cinema]
SubscriberId (Foreign Key to SuperType)
FavoriteCinema
FavoriteGenre

[SuperType] + [SubType Ecommerce] = EcommerceSubscribers
[SuperType] + [SubType Cinema]    = CinemaSubscribers
[SuperType] + [SubType Category3] = Category3Subscribers
[SuperType] + [SubType Category4] = Category4Subscribers

This lets you build new subtype relationships as the need arises.


Cassendra schema design suggestion for chat archive

I created a chat program a while back which is available on github

https://github.com/akc42/MBChat

which has some of the characteristics you are talking about.  In particular it provides both rooms which people can enter and discuss on an open way, and whisper boxes where a few people can get together and discuss things.

When people enter a room, or join a whisper, they can see a little bit of the past conversation.

Before explaining the schema a bit more, it is worth just pointing out that I have two methods of validating users.  This is because the normal use is a chat extension to an smf forum - so users are already logged in to the forum, and they carry their same identity over to chat (and have some characteristics that are derived from membership groups in the forum).  However so chat can be used stand alone without a forum, there is a separate user database which can be queried during the logon phase which identifies the users and their capabilities.

So first that database is at inc/user.sql in the repository.

BEGIN;

CREATE TABLE users (
  uid integer primary key autoincrement NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role text NOT NULL DEFAULT 'R', -- A (CEO), L (DIRECTOR), G (DEPT HEAD), H (SPONSOR) R(REGULAR)
  cap integer DEFAULT 0 NOT NULL, -- 1 = blind, 2 = committee secretary, 4 = admin, 8 = mod, 16 = speaker 32 = can't whisper( OR of capabilities).
  password character varying NOT NULL, --raw password
  rooms character varying, -- a ":" separated list of rooms nos which define which rooms the user can go in
  isguest boolean DEFAULT 0 NOT NULL
);
CREATE INDEX userindex ON users(name);
-- Below here you can add the specific users for your set up in the form of INSERT Statements

-- This list is test users to cover the complete range of functions. Note names are converted to lowercase, so only put lowercase names in here
INSERT INTO users(uid,name,role,cap,password,rooms,isguest) VALUES
(1,'alice','A',4,'password','7',0),
(2,'bob','L',3,'password','8',0),
(3,'carol','G',2,'password','7:8:9',0),
(4,'dave','H',0,'password','10',0),
(5,'eileen','R',8,'password','',0),
(6,'fred','R',16,'password','',0),
(7,'gail','R',0,'password','',0),
(8,'harry','R',0,'password','',1),
(9,'irene','R',32,'password','',0);

COMMIT;
VACUUM;
-- set it all up as Write Ahead Log for max performance and minimum contention with other users.
PRAGMA journal_mode=WAL;

However once they have been logged on, these credentials are copied over into the actual chat database that is being used

The following are elements of the schema (taken from data/chat.sql file from that repository) that might help you.

Firstly the the open rooms (actually not all rooms are equal, there is some limitations - see comments)

CREATE TABLE rooms (
  rid integer primary key NOT NULL,
  name varchar(30) NOT NULL,
  type integer NOT NULL -- 0 = Open, 1 = meeting, 2 = guests can't speak, 3 moderated, 4 members(adult) only, 5 guests(child) only, 6 creaky door
) ;

INSERT INTO rooms (rid, name, type) VALUES
(1, 'The Forum', 0),
(2, 'Operations Gallery', 2), --Guests Can't Speak
(3, 'Dungeon Club', 6), --creaky door
(4, 'Auditorium', 3), -- Moderated Room
(5, 'Blue Room', 4), -- Members Only (in Melinda's Backups this is Adults)
(6, 'Green Room', 5), -- Guest Only (in Melinda's Backups this is Juveniles AKA Baby Backups)
(7, 'The Board Room', 1), --various meeting rooms - need to be on users room list
(8, 'Marketing', 1),
(9, 'Engineering',1),
(10, 'IT Dept', 1),
(11, 'Finance', 1);

Now users themselves are carried over from the previous database (or from the forum and have a matching table)

CREATE TABLE users (
  uid integer primary key NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL default 'R',
  rid integer NOT NULL default 0,
  mod char(1) NOT NULL default 'N',
  question character varying,
  private integer NOT NULL default 0,
  cap integer NOT NULL default 0,
  rooms character_varying
);

But this is not all - I have another concept - which is that of a Participant in a conversation - with a conversation being just an ID.

CREATE table wid_sequence ( value integer);
INSERT INTO wid_sequence (value) VALUES (1);

CREATE TABLE participant (
  uid integer NOT NULL REFERENCES users (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  wid integer NOT NULL,
  primary key (uid,wid)
);

Not there is a wid_sequence table which my code manually increments when anyone creates a new conversation - (I call it a whisper box, but sometimes it becomes what I call a private room ), and then a record in the participant table whilst the conversation is in place.

Finally, so I have an archive of messages - which I can reference and play back at a later date (either a short list of recent messages when someone enters a room, or someone with the secretary capability can enter a room and obtain a printout of the entire conversation - as minutes of a meeting for instance).

I think it is this table that is your archive that you mention in the question

The schema for this is

CREATE TABLE log (
  lid integer primary key,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  uid integer NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL,
  rid integer NOT NULL,
  type char(2) NOT NULL,
  text character varying
);

The important item here is the type field.  It is in essence a replay of the actions as well as the conversation, so we also can replay new conversations starting up (where rid then maps to the wid created from wid_sequence), and people entering and exiting rooms etc.

I trust that gives you an idea how you can proceed.  Feel free to examine my code on github and ask any questions that you might have of it.

As a postscript, there is a table I didn't mention called 'parameters' which controls a lot of how things work.  One of those is whether encryption needs to be used.  It is beyond the scope of this answer to go into that - as it is fairly complicated - but you may need to consider how private you want private conversations to be and whether or not they should be stored encyrpted in your archive.


SQL Server Cannot drop database  because it is currently in use... but no sessions displayed

A session connected to another database might have an open transaction that also affects your database - sp_who2 will only show one database. It could also be something as simple as Object Explorer or Object Explorer Details open in SSMS, which again would only show one database in sp_who2.

Don't bother trying to find the session that is responsible; just kill them all with one statement (and make sure it isn't your copy of SSMS that is connected, e.g. another query window, Object Explorer, etc.):

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Now you will be able to drop it, and do that using DDL, not the UI:

DROP DATABASE dbname;


How to select maximum row value using optimal way

This is the SQL view of a msaccess query for your table
SELECT
    t1.[lClientId]
    ,MAX(t1.datAccess) AS datAccess
    , MAX((SELECT TOP 1  t2.[lProp1] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp1] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp1] DESC))  AS lProp1
    , MAX((SELECT TOP 1  t2.[lProp2] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp2] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp2] DESC))  AS lProp2
    , MAX((SELECT TOP 1  t2.[lProp3] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp3] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp3] DESC))  AS lProp3
FROM Table1 AS t1
GROUP BY t1.[lClientId];

Result

lClientId
datAccess
lProp1
lProp2
lProp3

5
10.12.2020
3
10
9

6
29.10.2020
9
11
16


I'm sure there may be other ways of doing this, but at my shop, I run DBCC CHECKDB via SQLCMD bat files and use the -o parameter to route the output to a flat text file.  Then, in the same bat file, I use SQLCMD to invoke

EXEC msdb.dbo.sp_send_dbmail

and attach the results of the DBCC

This process could be scheduled via either Windows Task Scheduler or you could create a Sql Agent job to run 'Operating System (CMDEXEC) to invoke the SQLCMD.

If you wanted to loop through each online database and run CHECKDB, you could create a utility stored procedure like this with the DBCC options that you want to use. This example skips TEMPDB, but I run a separate automated task for that database alone due to frequent timeouts.

It prints the database name, start time and end time.

Create PROCEDURE [dbo].[DBCC_CheckAllDatabases]
AS
BEGIN
    DECLARE @Message VARCHAR(max);
    DECLARE @databaseList as CURSOR;
    DECLARE @databaseName as NVARCHAR(500);
    DECLARE @tsql AS NVARCHAR(500);

    SET @databaseList = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
          SELECT QUOTENAME([name])
          FROM sys.databases
          WHERE [state] = 0 and [name]<>'tempdb';
    OPEN @databaseList;
    FETCH NEXT FROM @databaseList into @databaseName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @Message = 'Starting DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
       RAISERROR(@Message,0,1) WITH NOWAIT
       SET @tsql = N'DBCC CheckDB(' + @databaseName + ') WITH ALL_ERRORMSGS,NO_INFOMSGS,PHYSICAL_ONLY;';
       RAISERROR(@tsql,0,1) WITH NOWAIT
       EXECUTE (@tsql);
       SET @Message = 'Completed DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
       RAISERROR(@Message,0,1) WITH NOWAIT

       FETCH NEXT FROM @databaseList into @databaseName;
    END
    CLOSE @databaseList;
    DEALLOCATE @databaseList;
END

You could also utilize Aaron Bertrand's InEachDB process, if you don't like my example for looping through the databases.


this needs a lot more information
they are going to have registry, church, and a LOT of other stuff

you need to read a book on database design or do some tutorials as your design is not very good

starts with weddings plural
they are not going go want a separate database for each wedding

if everyone needs to logon then you need one table for that

wedding:
ID   PK
brideID   FK to user
groomID   FK to user
vendorID  FK to user

if you can have multiple vendors then need a separate table

user:
weddingID  PK  FK to weddingID
usrID      PK
password
fname  (vendor just does not have fname)
lastname

if they are not brideID, groomID or vendorID then they are a guest


How do I prevent my clients viewing my database definition when it is installed on their server?

Short Answer: you can't.

If the database is being run on the customer's infrastructure, then the customer must have control over their own infrastructure.  Without it, they cannot operate.

If you really don't want them to have that control, then you cannot give them the database.  This is the "Software-as-a-Service" Operating Model wherein you licence the application but don't get to play with the "back-end" of things.

I would suggest that this is not a Technical problem.  It's a Licencing one.

I assume that the licence agreement for your software includes a provision that prevents any Customer from "Reverse Engineering" any part of your product.  That clause should be extended to include your database.

Any "mucking about" with the database should invalidate [at least] your support agreement with that Customer.  If they want to take that Risk, well, that's up to them.


What are some best monitoring tools available for mysql?

Have you looked into MySQL Enterprise Monitor? Or are you only looking for freeware?

EDIT: You could also use this comparison chart from Wikipedia to pick one based on your needs:

http://en.wikipedia.org/wiki/Comparison_of_network_monitoring_systems


Will a WHERE query do checks on simpler comparisons (i.e. bit) before running more arduous comparisons (i.e. varchar)?

It is important to recognize that SQL is a declarative language. The SELECT query you wrote specifies the logical results that should be returned. It is up to the database engine, specifically the query optimizer, to determine an efficient physical strategy to return those results.

The final physical execution plan will depend on the optimizer's reasoning abilities, the amount of time it is prepared to spend on the problem, the availability of suitable access methods (primarily indexes and materialized views), representative statistics information, and the particular code path your query specification takes through the optimization code.

In general, if your database design is relational, you provide good access methods & accurate statistical information, and the query is well-written, the optimizer will normally find a reasonable physical execution strategy without you needing to worry too much about the written form of the query specification too much.

There will always be cases where expressing the same logic requirement using different (but semantically identical) syntax will affect the physical execution plan , but that should be a secondary concern. Again, generally, only consider expressing the query differently if runtime characteristics are unacceptable, once all the basics mentioned above have been covered.

It would be rare in the extreme for the written order of simple conjunctive WHERE clause predicates (as in the question) to affect the physical execution plan in any measurable way. In short, this is not something you should spend time worrying about. Get the database design, indexes, and statistical information right first.

To answer the question directly (eventually!), adding the extra redundant condition might improve performance, but only if it enables use of a more efficient access method - for example if there is only an index on (BitField, VarcharField). If there were already an index on (VarcharField), it would add only overhead.

As an implementation detail, no, SQL Server does not consider the cost of comparisons depending on data type or apparent computational complexity. In fact, there is precious little costing of scalar operations at all, but that leads on to a whole separate topic.

Related questions:

Logical operators OR AND in condition and order of conditions in WHERE
SQL Server 2008 and constant expressions
Bitwise operators affecting performance
Strange SQL Statement Behavior


PostgreSQL Select value row depending on another value row

You can use NOT LIKE to include all the non taxi related data -- check that the phase doesn't begin with TAXI_. Then add for each size a comparison that couples the airport size to the taxi route with OR.
SELECT *
       FROM aviation.emissionsco2
       WHERE phase NOT LIKE 'TAXI$_%' ESCAPE '$'
              OR (phase,
                  type) IN (('TAXI_LARGE',
                             'large_airport'),
                            ('TAXI_MEDIUM',
                             'medium_airport'),
                            ('TAXI_SMALL',
                             'small_airport'));


STRING_AGG Not Recognized on V15.0.2 of SQL Server LocalDB

Per my comments: Have you tried to use the STRING_AGG() function with a very simple test case to confirm it's an issue with the function and not your syntax in your main query?
It seems your syntax was not formatted properly, rather than the STRING_AGG() function not being available on your instance version.


When is it appropriate to use SQL Server Developer Edition?

Development edition licenses can be used to install anything that isn't production, provided that everyone that connects to the server has their own development license.  MSDN licenses can be used as well if everyone has an MSDN license.


How is a table locked regarding UPDATE-SELECT queries in PostgreSQL?

The question about locks was addressed by a_horse_with_no_name in the comments to the question:

  an UPDATE will never lock a table - it will only lock the rows that have been updated (in the target table). A select never locks any rows

And as for combining the three update statements into one, yes, it is possible, though not necessarily a good idea. Still, if you insist, here is one way:

UPDATE
  myTable1
SET
  fld1 = CASE myTable2.style WHEN 45 THEN myTable2.value ELSE myTable1.fld1 END,
  fld2 = CASE myTable2.style WHEN 46 THEN myTable2.value ELSE myTable1.fld2 END,
  fld3 = CASE myTable2.style WHEN 47 THEN myTable2.value ELSE myTable1.fld3 END
FROM
  myTable2
WHERE
  myTable1.id = myTable2.id
  AND myTable2.style IN (45, 46, 47)
;

Basically, you do the join, combine the three filter conditions with an IN predicate and in in each of the three assignments you use a CASE expression to check if the myTable2.style value matches a corresponding value from the filter, so that you can decide whether to assign the myTable2.value to the column (THEN branch) or keep the old value (ELSE branch).

Your three separate statements look clearer/more readable to me, though.


Permission error when executing a 5.6 GB sqlcmd file

@Mihai, As  Daniel Hutmacher already said,Express Edition is limited in database size. As per sqlmag BOL http://sqlmag.com/sql-server-2012/sql-server-2012-express-editions, if you are using SQL Server Express with Advanced Services. It is limited to 4GB and can access only the local instance.

Even in dba.stackexchange the Shanky How many users are supported on SQL Server 2012 Express? has also explain the limitation of SQL Server Express edition .


Design for table with multiple links to other table

Site1 and site2 can both be FKs, linked to the ID_site PK. This works on any rdbms that i'm aware of.

Example Query:

SELECT CT.site1, CT.site2, S1.site_description, S2.site_description
FROM circuit_table AS CT
INNER JOIN site_table as S1
  ON CT.site1 = S1.ID_site
INNER JOIN site_table as S2
  ON CT.site2 = S2.ID_site


Enabling SQL Server authentication for restored database user

The screenshot you've posted is just showing that it's a user which uses Windows authentication to log on to SQL Server, though this doesn't have any bearing on whether or not the user can actually access a database.

If you go to the Server Roles tab you can assign a fixed server role which will allow various sets of permissions to the instance, or if you go to User Mapping, you can specify a role for that user against a particular database, or under securables you also have more granular permissions if you only want to assign very specific permissions.

I would ensure that your user has the required permissions mapped against the database you are trying to access.  If you're still seeing an error after that, post the specific error message.

Also, just to more specifically answer your question - it's not possible to switch the user to use SQL Server authentication; in this case you would just create a new user which uses SQL authentication, as it is specified at the time the user is created.


Foreign key with additional constraints?

Original answer is wrong! See Edit 1 for the corrected version.

Original answer

An amazing solution would require a foreign key to a column in a view or a inherited table, but unfortunately PostgreSQL (I suppose that's your RDBMS because of the tag) does not have that (yet).

I think a simple change in the way you organize the data would suffice: create a table like ItemsAvailableQuantity, connecting an Item with its availability which will be references in the orders. When an item is not available anymore, DELETE it from it.

CREATE TABLE Item (
    id   serial  NOT NULL
  , name text    NOT NULL
  , cost numeric

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
    );

CREATE TABLE ItemAvailableQuantity (
    id       serial  NOT NULL
  , item_id  integer NOT NULL
  , quantity integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES Item (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
  , CONSTRAINT postive_quantity -- This constraint is the same as
        CHECK (quantity > 0)    -- checking something like `available = TRUE`.
    );

CREATE TABLE ItemOrder (       -- Changed the name from `Order` because
    id      serial  NOT NULL   -- PostgreSQL refuses that name, somehow
  , bill_id integer NOT NULL
  , item_id integer NOT NULL
  , units   integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES ItemAvailableQuantity (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
-- Uncomment when `Bill` table is ready
--  , FOREIGN KEY (bill_id)
--        REFERENCES Bill (id)
--        ON UPDATE CASCADE
--        ON DELETE CASCADE
  , CONSTRAINT positive_units
        CHECK (units > 0)
    );

Notice! The constraint positive_units may cause problems when your software reduces the units and reaches 0. Make it something like CHECK >= 0 if needed, or add a trigger that automatically DELETE-s rows when units reaches 0 (or less) on each INSERT or UPDATE. This would preserve the table ItemAvailableQuantity to have only actually available items, which is what we want for being referenced from the table ItemOrder.

This should solve your problem. It's not an exact answer to your question. That would involve a trigger or a CHECK calling a function as in the link you provided.

To easily see the quantity of the items then, just create a view that joins ItemAvailableQuantity and Item. If you really want then, make it INSERT-able with a trigger (see yellow-box warning).

Edit 1

Actually Order (a.k.a. ItemOrder) should reference the Item instead of ItemAvailableQuantity to avoid any problem when the Item is not currently available, as stated in the comment.

This suggest we should remove the whole table ItemAvailableQuantity and only add a column available_quantity on Item.

CREATE TABLE Item (
    id                 serial NOT NULL
  , name               text   NOT NULL
  , cost               numeric
  , available_quantity integer NOT NULL

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
  , CONSTRAINT non_negative_quantity
        CHECK (quantity >= 0)
    );

Then, to be certain of inserting only available items into orders we could just run

INSERT INTO ItemOrder (bill_id, item_id, units) VALUES
    (SELECT id FROM Bill WHERE condition = something -- customize at will
    , SELECT id FROM Item WHERE available_quantity >= wanted_quantity
        AND other_condition = something
    , wanted_quantity)
    ;

where wanted_quantity is a parameter passed by your software to the query.

Still, solves the problem, but is no direct answer to the question.


Transfer microsecond timestamp into table using COPY

What you're trying to do is normally referred as an ETL (Extract, Transform, Load) process.

You want to do the transformation within the database. This is normally possible by using an intermediate table, where you store (as text, most probably) the data from STDIN via libpq and COPY.

COPY intermediate_table (orig_col1, orig_col2)
FROM stdin
WITH FORMAT CSV  ;  -- Choose your parameters

Then transfer it to the second table by means of

INSERT INTO
     dest(col1,                     col2)
SELECT
          f1(orig_col1, orig_col2), f2(orig_col1, orig_col2)
FROM
     intermediate_table ;

That is, you have some transforming functions on the original data to produce the data you want to store. For instance f1 could just be your TO_TIMESTAMP(orig_col_1 ::double precision / 100000).

After transforming the data from the intermediate table(s), it is common to truncate them (or drop them, and recreate them next time).


How to setup encryption on a column that joins two tables in two different databases

The answer to your question depends on whether or not you empasize security over performance and potentially how many records we are talking about. You can create a secure solution that won't scale very well by creating the same symmetric key on both servers by using identical values for key_souce and identity_value for the CREATE SYMMETRIC KEY command. All server side encryption in SQL Server is non-deterministic, meaning that the same value encrypted twice with the same key will result in different encrypted values. Therefore, you won't be able to join the data without decrypting it. If the data is decrpted on each server before it's combined, then you will want to add an SSL Certificate directly to the SQL Server to encrypt at the transport level.

Alternatively, you could put the key on only one server and always perform a remote join on the server with the key after encrypting both tables with it. You would still want an SSL certificate on the instance if you require that the result is returned unencrypted.

Another idea would be to use an Extensible Key Management system, which is a hardware security module that offloads encryption processing and would allow the same key to be used by both systems.

Since server side encryption is non-deterministic, you won't be able to index it. There are two potential solutions to that issue. One is to create a hash on part of the original data and index that to help improve performance and the other would be to use an deterministic method for encryption that is either external or encapsulated in a CLR that is installed on both systems. Be careful with hashes because they are deterministic and can be reproduced easily, so avoid hashing the entire value. If anyone can use the  CLR or external encryption who is not authorized, then it's possible that a hash table can be created by running all possible values through the system and then it can be joined back to the original data.

To encrypt in SQL Server, you will also need to use varbinary columns instead of numerics.


PostgreSQL Return Joined Results of Select

Use RETURN QUERY SELECT ...;.

PostgreSQL is complaining because you aren't specifying what to return. Also, since you aren't actually passing parameters to the function this could also be accomplished by a view.


The solution heavily depends on how much work you are willing to put in your initial query and what is going to consume the data (and how).

-- Option 1 --

If this is a one-off requirement, you can use variations of sp_MSForEachDB to retrieve a separate data set for each database.

Personally, I don't like sp_MsForEachDB for a number of reasons, mainly because it uses a wrongly configured cursor to enumerate the databases, which can make it skip databases at times. I coded my own alternative to it some years ago and blogged about it here

The main issue with this solution is that it doesn't return a single data set, which I suppose is what you want. In this case, you should create a temporary table upfront and use it to store data returned from your query. All executions against different databases will populate the temporary table with the data you need.

Here's an example:

DECLARE @sql nvarchar(max);

SET @sql = '
select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from syscomments
    where text like ''%Janice\SQL%DTS%''
';

CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL
);

DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)');

INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

SELECT *
FROM #results;

-- Option 2 --

This solution requires that you put your sql statement in a string variable and you know the output of the query. If this is not acceptable for you, some time ago I coded a stored procedure that can take the SELECT statement inside a view and use it to execute against all databases (or a subset of your databases). In this case, you just need to create a view with the statement you want to run and pass the name to that stored procedure.
You can find the code here.

-- Option 3 --

You could also UNION ALL the results that come from your query, something like this:

DECLARE @sql nvarchar(max)

SET @sql = '
select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from <dbname>.dbo.syscomments
    where text like ''%Janice\SQL%DTS%''
';

DECLARE @statement nvarchar(max);

SET @statement = STUFF((
SELECT 'UNION ALL ' + REPLACE(@sql,'<dbname>',QUOTENAME(name))
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,9,SPACE(0));

EXEC sp_executesql @statement

If you use this approach, you also have to take care of everything that could go wrong in the UNION ALL. Collation conflicts between databases, for instance, have to be resolved in the query.

Hope this helps.

BTW, syscomments is deprecated, so if you're using SQL Server 2005 and above, use sys.sql_modules instead.


Piplelined Result Set over Database Link Alternatives

Hoo boy - that's not going to be simple, as far as I know.

A few things I guess you could try:

Drop a view on top and stream those results across the link. The problem is going to be passing parameters though...
Have a routine across the link that you can pass parameters to that dumps your pipelined results to a table that you can then stream across the link. Then perhaps have a cleanup function that nukes the table when you're done with it (but beware concurrency...)
Re-architect? Instead of pipelining rows, you could return a collection of records (not sure how that translates across the link, though -- never tried it). Alternatively, a method whereby you could call it several times, each time returning the values of a row in a record. Neither is particular elegant. (You'd have to have some sort of start/next function at least, if not more.)

Pipelining is such a useful feature that it is a shame it doesn't translate across the db-link, though. Maybe Oracle will, one day, see fit to fill that particular gap. Otherwise, though, I fear you've got your work cut out for you.


What does "Heap Block" in "Bitmap Heap Scan" mean?

Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks.  It can do this selectively, so some blocks can be converted lossy while others not.

If it goes lossy, then the Heap Scan must recheck every row in every lossy block which it visits, because it no longer has information about which particular rows in that block met the search criteria.

"Heap Blocks: exact=1" means that it only visited one block, and that it was not lossy.  (So both of yours rows returned were in the same block)

This agrees with the Buffers data.  The Bitmap Index Scans and BitmapAnd in total hit 7 blocks.  The Heap Scan hit one more, bringing it up to 8.


In Postgres - when transaction IDs wraparound what are their numbers?

The docs state that

  the XID counter wraps around to zero


How many files are created by default using WiredTiger storage engine?

Yes, WiredTiger Engine creates one file per collection and per index.

Files are the part of storage layer that is being maintained by the Storage Engine part. you can locate the created files in the dbpath specified in the configuration file used to start the server instance. For example in case if your underlying OS is ubuntu the default config file path is:

/etc/mongod.conf

In case of WiredTiger engine when you create any collection, then you will be able to find new files like:

collection-22--9037026691096837958.wt
index-23--9037026691096837958.wt

here numbers 22,23 indicates the unique identity of collection and index to the storage engine and being maintained withing the storage engine's metadata.

in last part of your question about threshold, you may be interested in storage capacity limits, so you can consider following these links:

https://docs.mongodb.com/manual/reference/limits/
https://stackoverflow.com/questions/4327723/mongodb-limit-storage-size


Using Postgres COPY TO for data export without overwriting file

You could use the meta command \copy in psql (which encapsulates SQL COPY) on the command line, send it to stdout and and redirect output to be appended to a file - with >> instead of >:

psql -c '\copy (SELECT 1, 2) to stdout' >> /path/to/file_to_append_to.csv

(You probably need more connection parameters.)
You can use SQL COPY as well:

psql -c 'COPY (SELECT 1, 2) TO STDOUT' >> /path/to/file_to_append_to.csv

But it's often more convenient to use \copy instead because (quoting the manual):

  file accessibility and access rights depend on the client rather than
  the server when \copy is used.

Or, like Abelisto commented, from inside psql or any other client - if the program cat is available (basically on all *nix OS):

COPY (SELECT 1, 2) TO PROGRAM 'cat >> /path/to/file_to_append_to.csv'

Or, still from inside psql, on any system, you can set the current directory and an output file with:

\cd /path/to/
\o file_to_append_to.csv

The file gets created (only) if it does not exist yet.
Then you can also SQL COPY (or still \copy as well):

COPY (SELECT 1, 2) TO STDOUT;

The output is appended to the file.


sp_spaceused reveals 0 rows but 72 KB of reserved space

Likely, there is an IAM page and extent reserved for this table. TRUNCATE TABLE will remove these. When all data is just deleted, a bare minimum structure will still exist for the table.

Have a demo!

--Setup
DROP TABLE IF EXISTS dbo.welcometable
CREATE TABLE dbo.welcometable
(ID INT)

EXEC sp_spaceused 'dbo.welcometable'
--all 0s

-----------------------------------------------------
--Let's see what spaceused looks like when there's data
INSERT dbo.welcometable
VALUES (1)

EXEC sp_spaceused 'dbo.welcometable'
--72KB reserved, 8KB data, 8KB index, 56KB unused

-----------------------------------------------------
--Now what if we delete?
DELETE FROM dbo.welcometable

EXEC sp_spaceused 'dbo.welcometable'
--72KB reserved, 8KB data, 8KB index, 56KB unused

-----------------------------------------------------
--What if we truncate?
TRUNCATE TABLE dbo.welcometable

EXEC sp_spaceused 'dbo.welcometable'
--all 0s
--success!

If you want to actually see the pages and where they are, use this:

SELECT page_type_desc, allocated_page_page_id, is_allocated
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.welcometable'),NULL,NULL,'DETAILED')

This shows one IAM page and one reserved extent for me, even when all rows have been deleted.


Orderable representation of multi-part values

Your tables look adequate to me.  I'd just have a "project_materials" table with columns including "project_id", "material_name", and "material_quantity", but your teacher may not like it.  Also, is a "unit" a quantity, or distinct thing, e.g. copper tube, copper screws, pieces of wood in various sizes and shapes?

So anyway, for instance:

SELECT p.name FROM projects AS p
INNER JOIN project_materials AS m1
ON ( m1.project_id = p.id AND m1.material_name = 'Copper' )
LEFT OUTER JOIN project_materials AS m2
ON ( m2.project_id = p.id AND m2.material_name = 'Steel' )
WHERE ( m1.material_quantity > 4 AND m2.material_quantity IS NULL )


Viability of surrogate key based from existing column as primary key

You could do this - but I wouldn't. Here's why:

I generally try to avoid using "human meaningful" values as keys - because humans may decide they want the value to be something else. Since your key is partly a human meaningful value, there will be a temptation to change it.

For instance, "Alex" decides they want to use their full name, "Alexos". Do you change the PK to "Alexos1", or leave it as "Alex1"? With cascading updates, it's not hard to update PKs - but, it can cause locking issues, if there are a lot of foreign keys tied to it.

Next, there's the "random number" you want to add to the name to create your key.

If it's truly random, you have to check for collisions, and set some upper limit (which could have to change, if you exhaust the possible values when tied to a particular name).
If it's not random, but tied to the underlying data in some way, you may have to calculate it (for instance, if the next "Alex" should be "Alex2").
If it's actually unique (for instance: if you have a auto-increment integer column, uid, that's actually unique, and you combine the name and uid columns to make name_id), it's easy to build (as long as the name column can't have digits at the end of it - if it can, then collisions are possible again), but:

you may start to lose the easy linkage - if you've got "Alex326", "Alex3156", and "Alex12581", you may get to a point where matching the number 3156 in both tables would be just about as easy as locating "Alex3156".
it's overkill, because uid is already a perfectly good key value.

The big pro for your team would be the ability to connect data rows in two different lists. If you're dealing with Excel, this can make sense; in a SQL database, it really doesn't. If you want to match the rows of two different tables, just run a query that JOINs them.

I mention a few potential "cons" above. Some of them tie back to more generic DB design concerns.

The issue of whether to change the key if the name value changes, and of creating a combined key out of name and a uid column - both entail denormalizing the data, in effect.
String-valued keys almost always consume more disk space than unique integer keys. This will affect every index on the table; they'll consume more space, too. And, the more space your data takes up, the more time it takes to for each query to run. (Admittedly, in practice, with a relatively small table, the size of your key may not cause a noticeable difference to your users; but at several thousand rows, the difference will be there).

I would be inclined to use an auto-increment integer ID in the table, and get the users used to pulling the data back via queries that join the relevant tables, instead of relying on keys that people can "eyeball" in separate table dumps. If necessary, create views for them to hide the joins.

This may make data entry via PHPMyAdmin a little harder, initially. However, users will adapt - for instance, copying and pasting the ID into a linked record, if necessary. Designing a database to make data entry tasks a smidgen easier isn't generally a great strategy - in most cases, the benefit you're looking for in having the data in a database in the first place is more a matter of using it once it's in your system, than just getting it entered.


SQL Server backup media set and file separation

I would say that there isn't necessarily a Best Practice, this is more of a matter of personal preference or your businesses operating procedures.  I've had separate folders per database, then sub folders for FULL, DIFF, LOG at some jobs, others everything goes to a single folder and the backup name is some combination of database name, backup type, and datetimestamp to make each file uniquely named.  I'd recommend looking at Ola Hallengren's maintenance solution:

https://ola.hallengren.com/

This is what I recommend to all my clients for database maintenance and backups and it handles everything dealing with the file naming and folder structure automatically for you.  Just run his script and set the jobs to run at the frequency and schedule that meets your requirements and you are all done.


Create multiple output tables from one query

I'm an inexperienced Postgres user and need help if someone can suggest it.

A few things, this is a bad idea, you probably want Table Partitioning because essentially you're creating partitions of another table.

If you don't want to partition a table, you probably want a MATERIALIZED VIEW which sits as a table on the disk, but it can be refreshed from the query which is was created from with a simple command REFRESH MATERIALIZED VIEW.

What I think you want is something like this,

SELECT FORMAT ($$
  CREATE MATERIALIZED VIEW %I.%I AS
    SELECT *
    FROM my_countries
    WHERE country = %L;
  $$,
  'myschema',
  'test_' || country,
  country
)
FROM (
  SELECT DISTINCT county
  FROM my_counties
) AS t(country);

Run that in psql and then run \gexec, or loop through that result set and run EXECUTE in plpgsql.


Why does SQL Server Mangement Studio not script Indexes by default?

You don't have to change the option every time. If you change the selection in Tools > Options > Object Explorer > Scripting, it should persist.

As for why not scripting indexes is the default, I have no idea. Not a great question for your peers; if you really want the background, hit up someone on the SSMS team and buy them a beer - maybe they'll be able to share it. You could also consider submitting a suggestion on the SQL Server feedback forum (if one doesn't already exist), and they will possibly tell you why it's the default and/or why they won't be changing it.


Sure, just use a subquery

select distinct u.*
from
  user_hashes uh
  inner join users u on uh.user_id = u.id
WHERE
  uh.hash IN
  (
    select hash
    from user_hashes buh
    where user_id = ?
  )


How can I get the list of tables in all the stored procedure

Tables don't "belong" to stored procedures, but I think you want sql_expression_dependencies:

SELECT DISTINCT s.name, p.name,
  db  = COALESCE(d.referenced_database_name, DB_NAME()),
  obj = COALESCE(d.referenced_schema_name, s.name) + N'.'
        + d.referenced_entity_name
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.procedures AS p
  ON p.[object_id] = d.referencing_id
INNER JOIN sys.schemas AS s
  ON p.[schema_id] = s.[schema_id]
ORDER BY p.name, obj;

Of course this won't find dependencies that are constructed using dynamic SQL, and the schema references might be wrong in cases where you've explicitly left out the schema name in the query so it can be determined by the user's default schema (if you're doing that, please don't do that).


How do I find all tables in a DB that have no explicit primary key?

Couple ways to skin this cat but this works fine in SQL Server 2005 and up, and I find it a pain free way to handle the problem -

The OBJECTPROPERTY() function can list various properties about objects - like tables. One of those properties is whether or not a table has a primary key.

OBJECTPROPERTY(object_id, tablehasprimarykey) = 0 would be a table without a primary key.

So

SELECT OBJECT_SCHEMA_NAME( object_id ) as SchemaName, name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'tablehasprimaryKey') = 0
ORDER BY SchemaName, TableName ;

Should give you what you need. You can see all about the other ways to use the OBJECTPROPERTY() function in books online. This is the 2012 version of the article.


What throughput should I expect with MPIO?

Disk writes are actually going to memory (NVRAM) on the filer, to be flushed to disk later - on an idle filer, these will be incredibly fast, and iops of 20,000 are quite believable (you'll see similar speeds from most SSDs).

Reads, on the other hand, need to come from disk unless they already happen to be in the filer's read cache (which, unlike writes, are on volatile memory).

It's hard to pin storage vendors down on iops for spinning disks, but for a 7200RPM drive, 80-120 iops is quite believable. Considering that you've probably lost a couple of disks to NetApp's RAID-DP and/or spares, 2,200 iops is close to what you could expect from 22 disks performing around 100 iops each.

This may not explain your read speeds (your disks may not be doing the full 2200 iops when you're performing a sequential read), but it may at least help explain your write performance.


When to use a TCP dynamic port and when TCP Port?

Say that something else uses the port that you have configured for your SQL Server: What do you prefer?
The SQL Server to start but use a different port? Use dynamic.
Not start at all? Use static.

Another aspect is that your developers seem to have hard-wired the port number in the application (connection string) i.e., connect to something e.g. yourserver,45346. That is one way to do it. Another way is to connect using the instance name such as yourserver\instancename. This way the port resolution is handled for you, but it requires that the SQL Server browser service is started on the server machine and you can reach it using UDP port 1434 for the name->portnumber "query".


Why doesn't DELETE + REORG free diskspace (DB2)?

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.)

The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water Mark for the tablespace.

Do a the following

db2 list tablespaces show detail

This will show you each tablespace and what it is using on disk. Used pages is how many pages of disk the database is using. Comparing that against total pages (the total claimed on disk) and the High water mark (pages) will show you if you are "claiming" more than you actually need. (ie, low used pages, very high total pages and a High Water Mark close to the total pages).

To get rid of this unused space and return it to the operating system you would issue the following (under automatic storage): db2 alter tablespace <tablespace name> reduce max.
example

db2 alter tablespace ts1 reduce max;

That will cause DB2 to lower the high water mark and release the unused disk back to the operating system. (Note you can only do this for regular and large tablespaces, not for system temporary, or user temporary tablespaces).

If you are using DMS without automatic storage you need to use a slightly different set of commands:

db2 alter tablespace <tablespace name> lower high water mark;
db2 alter tablespace reduce (<containter name> or [all containers] integer K|M|G or integer PERCENT);

example

db2 alter tablespace ts1 lower high water mark;
db2 alter tablespace reduce (all containers 500 M);

Where we work, we put this into some of our maintenance scripts so that we automatically run this after we do reorgs to make sure we reclaim disk space. In our case we use DB2 LUW 9.7 FP 4, so it doesn't hurt to double check Information Center for 9.5 to make sure you have access to the right information for your version.

  EDIT: If your tablespaces came from a database upgraded to DB2 9.7, you probably will not have the reclaimable storage attribute set. This is true even if you upgrade from DMS to automatic storage. Either
  way bites as you cannot actually lower the high water mark. You have
  to dump the table and data out, drop the tablespaces. Then re-create
  the tablespace using automatic storage and import the data for your
  tables.


Autodetect ‘random_page_cost’ vs ‘seq_page_cost’

Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?

It's certainly possible to set the parameters automatically, but no one has submitted a patch to do it.

You need to know the sequential and non-sequential read speeds of the drive. There are a ton of ways to get this, but you may just as well use Google because it likely doesn't matter that much. For instance, a quick google search for the sequential and non-sequential read performance of the Samsung SSD 840 Pro (256GB) shows this from AnandTech, with

Random Read 101.4/mbps
Sequential Read 510.7/mbps

That's roughly a ratio of 1:5, so

SET random_page_cost = 5;
SET seq_page_cost = 1;

Warning, random_page_cost takes into account the cache,

  Random access to mechanical disk storage is normally much more expensive than four times sequential access. However, a lower default is used (4.0) because the majority of random accesses to disk, such as indexed reads, are assumed to be in cache. The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached.

  If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost.

I've shown my random_page_cost to be 5 times slower than sequential. It's still a wildcard how much of the random_page_cost is already cached. Alas, these values don't really matter much unless the Index scan and the Sequential were so close that you could reasonably pick the Sequential scan by accident. That's seldom the case. It's not uncommon for an Index to speed things up by thousands of times over.

For example, my cpu_index_tuple_cost is 0.005. AFAIK, that means scanning 1000 entries in the index is the same in the planner's eyes as going to the heap once to fetch a block.


Installing scott schema for oracle 11g express

when you are installing oracle database you can install all oracle example schemas like scott, HR, oe ...
you can install these in step "Specify Database Config Details".click the last tab page "Sample Schemas" then check the check box "create database with sample schemas" as displayed bellow in screenshot.

if you already installed database you can recreate it for training "without uninstalling it" by write the command:
start -> run -> dbca
to run oracle database configuration assistant.
follow steps to drop your running database and recreate it with sample schemas.

update

and here in Oracle 12C:


Find continuous records with equal values characters

As mentioned in the comments, you need to have a way to determine the order of rows, so I added an ID column to my example.  I also added another AccountNumber and mixed the data up a bit to verify that my solution would work for other accounts.  You did not specify your RDBMS.  My solution uses SQL Server.

--Set up demo data
DECLARE @T TABLE
    ([ID] int,[AccountNumber] varchar(10), [Remarks] varchar(100))
;

insert into @t(ID,AccountNumber,Remarks) values
(1,'Q32571','Actual Read'),
(2,'Q32571','Locked'),
(3,'Q11111','Locked'),
(4,'Q11111','Locked'),
(5,'Q32571','Actual Read'),
(6,'Q32571','Actual Read'),
(7,'Q32571','Actual Read'),
(8,'Q32571','Locked'),
(9,'Q32571','Actual Read'),
(10,'Q11111','Actual Read'),
(11,'Q11111','Locked')
;

--The actual query
;with _groupings as
(
select AccountNumber, Remarks, count(*) as CountConsecutive
from (select t.*,
             (row_number() over (order by AccountNumber, ID) -
              row_number() over (partition by Remarks order by AccountNumber,ID)
             ) as grp
      from @t t
     ) t
group by AccountNumber, grp, Remarks
)
select AccountNumber, Max(CountConsecutive) as CountConsecutive
from _groupings
group by AccountNumber

| AccountNumber | CountConsecutive |
|---------------|------------------|
| Q11111        | 2                |
| Q32571        | 3                |


Information on a Database that was restored with NO RECOVERY

I'm not sure if I fully understand your question. First you say you want to find the last restore made to a certain database. You can find all relevant information (databases, files, lsns, times) on these restores through

SELECT *
FROM [msdb].[dbo].[restorehistory] r
INNER JOIN [msdb].[dbo].[backupset] b
ON b.backup_set_id = r.backup_set_id

But I'm not sure what you mean with "finding the rest of the needed backups". If you want to look at the backups made in an instance you can do this through

SELECT * FROM [msdb].[dbo].[backupset]

Hope that helps.


I suspect that the 90 refers to the deprecated 90 compatibility level, which is SQL Server 2005.  It is most likely there to support legacy code, as important changes to T-SQL syntax were made in SQL Server 2008, causing previously acceptable code to fail unless the compatibility level was downgraded.
The most likely reason that you are unable to find out further information is that Microsoft have removed a lot of online documentation for versions of SQL Server that are no longer supported.
The reference below seems to be consistent with this use of "TSQL90".
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsql90parser?view=sql-dacfx-150


change file table file stream data folder faces error The container can not be dropped

As the message suggests, you need to backup the transaction log in order to move on to the next step. You can do this inside SSMS with a couple of clicks or, if you prefer writing SQL, you can do this:
BACKUP LOG MyDatabase
   TO MyDatabase_TXN_log1;
GO

Once this is completed the requisite ALTER DATABASE statements can be run before restarting the SQL Server instance


How to calculate average of list of qualifications of each pupil MYSQL

Your current subquery is returning the avg(qualification) for all pupils but it is not relating each row back to a single pupil.  You need to make this a correlated subquery which will query your qualifications while using the pupils from your outer query.  I would suggest rewriting the subquery query the following way:

(select avg(q.qualification)
from qualifications q
where type_qualification='exam'
  and p.id_pupil = q.id_pupil
  and q.id_trimester=1
group by q.id_pupil) as exam_average

You will notice that this subquery doesn't have the JOIN written inside of it but it is referencing your pupils table from the main ares of your query.  The full query would be written:

SELECT
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  (select avg(q.qualification)
   from qualifications q
   where type_qualification='exam'
     and p.id_pupil = q.id_pupil
     and q.id_trimester=1
   group by q.id_pupil) as exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester,
   count(qualification) count,
     round(avg(qualifications.qualification),2) media
  from qualifications
  where type_qualification='class'
  group by id_pupil, id_trimester
) as qua
  ON qua.id_pupil = p.id_pupil
WHERE p.level=1
  and p.class='A'
  and qua.id_trimester=1
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count;

See SQL Fiddle with Demo

This could also be written without using the correlated subquery and you would JOIN on the subquery similar to:

SELECT
  p.id_pupil,
  p.name,
  p.surname,
  IFNULL(qua.media, 0) media,
  IFNULL(qua.count, 0) number_class_qualifications,
  IFNULL(q.exam_average, 0) exam_average,
  sum(case when i.type_incident='miss' and i.id_trimester=1 then 1 else 0 end) as misses,
  sum(case when i.type_incident='delay' and i.id_trimester=1 then 1 else 0 end) as delays,
  sum(case when i.type_incident='attitude' and i.id_trimester=1 then 1 else 0 end) as attitude,
  sum(case when i.type_incident='miss_justif' and i.id_trimester=1 then 1 else 0 end) as misses_justificadas
FROM pupils p
LEFT JOIN incidents i
  ON i.id_pupil=p.id_pupil
LEFT JOIN
(
  select id_pupil, id_trimester,
   count(qualification) count,
     round(avg(qualifications.qualification),2) media
  from qualifications
  where type_qualification='class'
  group by id_pupil, id_trimester
) as qua
  ON qua.id_pupil = p.id_pupil
LEFT JOIN
(
  select avg(q.qualification) as exam_average, q.id_pupil
  from qualifications q
  where q.type_qualification='exam'
    and q.id_trimester=1
  group by q.id_pupil
) q
  on p.id_pupil = q.id_pupil
WHERE p.level=1
  and p.class='A'
  and qua.id_trimester=1
GROUP BY p.id_pupil, p.name, p.surname, qua.media, qua.count, q.exam_average;

See SQL Fiddle with Demo. Both will generate the result:

| ID_PUPIL |         NAME |          SURNAME | MEDIA | NUMBER_CLASS_QUALIFICATIONS |   EXAM_AVERAGE | MISSES | DELAYS | ATTITUDE | MISSES_JUSTIFICADAS |
|----------|--------------|------------------|-------|-----------------------------|----------------|--------|--------|----------|---------------------|
|      140 |        María |     Adámez Nieto |     4 |                           2 | 4.733333269755 |      2 |      1 |        1 |                   2 |
|      141 |       Daniel | Alonso Fernández |     6 |                           3 | 8.400000095367 |      0 |      0 |        0 |                   0 |
|      142 |        Rocío |   Anos Población |     4 |                           1 | 6.366666634878 |      1 |      1 |        0 |                   1 |
|      143 |       Teresa |  Arribas Miranda |   3.5 |                           2 | 6.766666650772 |      0 |      0 |        0 |                   0 |
|      144 | Isabel María |    Barroso Suero |   5.5 |                           2 |              4 |      1 |      0 |        0 |                   1 |


how to query multiple counts on related tables

If your schema has a useful naming convention it could work like this:

SELECT f.forum, p.post_count, p.comment_count
FROM   forum f
LEFT   JOIN (
   SELECT forum_id, count(*) AS post_count, sum(c_ct) AS comment_count
   FROM   posts p
   LEFT   JOIN (
      SELECT post_id, count(*) AS c_ct
      FROM   comments
      GROUP  BY 1
      ) c USING (post_id)
   GROUP  BY 1
   ) p USING (forum_id);

LEFT JOIN is to include posts without comments and forums without posts.
If you had provided actual table definitions, I wouldn't need to speculate on column names.


As suggested a simple UNIQUE constraint on vehicle does the trick. Not sure why I wanted to do complicated.


How do I run a query only asking for a max date of attendance?

I have to assume some things here because I'm not sure what your table looks like or what your columns are, but let's assume you have a table called StudentHistory.

Let's say StudentHistory contains the columns StudentID, StudentName, SchoolName, LeaveDate.

Each student may have multiple entries depending on how many schools they have frequented. These entries can be ordered by the LeaveDate column to get a chronological history of attended schools.

What you want as an output can be articulated as:

  For each student, return the StudentID, StudentName, SchoolName for
  the last School attended based on the latest LeaveDate.

You can achieve this using the RANK function:

WITH StudentData AS (
    SELECT StudentID,
        StudentName,
        SchoolName,
        RANK() OVER(PARTITION BY StudentID ORDER BY LeaveDate DESC) AS TheRank
)
SELECT *
FROM StudentData
WHERE TheRank = 1 ;


MySQL Error 10048 on nested Delphi Queries

Tracked it down, and once again it's Delphi's hidden "Autoclone" option that is the culprit.

Set Autoclone to False and the number of queries gets limited and the error is skipped.


Severity 20 - Fatal Error in LOCAL CURSOR after FETCH

First solution:
When I changed the CURSOR declaration to

DECLARE CurName CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR ...

(or at least added the STATIC keyword), it runs fine.

Final solution:
The table had a geometry and a geography column, which were dropped this weekend (together with the two corresponding spatial indexes).

Since this was the only change to this table, I decide to rebuild the clustered primary key index, which solved this problem.

It seems, that the SP2 CU11 in SQL2014 has a small bug in this special case, so maybe someone with a similar problem find this solution useful...


Error when installing JServer JVM in Oracle XE on Linux AMI

A basic overview of feature availability for different database editions:

Products and Services - Database - Enterprise Edition - Comparisons

Java is not available in Express Edition.


I tried to perform the mechanical process. I hope I remember it right.

This leads to:

SELECT ...
FROM A
         join B on A.A_ID = B.A_ID
    left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID
    left join D on C.C_ID = D.C_ID
    left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEY

In short I think Leigh Riffel's answer is correct.

Note

in old days the rule to memorize was:
oracle where A.a = B.b (+)
becomes A.a *= B.b in SQL-Server old syntax the plus goes to the opposite side and becomes a star,
which means A left join B on A.a = B.b


Alternative approaches to "primary" record in child table

The other design option that came in my mind is to use CustomerID column in child table like Addresses and Contacts so that you will be making entry first in Customer table and that CustomerID will be referred in other table.

As suggested in comment by  @a_horse_with_no_name, you can create Unique Index as

CREATE UNIQUE INDEX UniquePrimaryAddresss ON Addresses(CustomerID) WHERE IsPrimary = 1

Similar indexes on Contacts table as well


Why is SQL Server spliting it's (JSON) response into multiple rows?

After further research, I have discovered from this StackOverflow post that SQL Server breaks FOR JSON queries into "~2kb chunks".

Sql Server splits result of FOR JSON query into ~2KB chunk, so you should either concatenate fragments like on the MSDN page or you can stream results into some output stream.

This means that only ~2000 characters can be sent per chunk.

UPDATE:
With the help of Max Vernon and Andriy M we have found a fairly simple solution to this problem.
DECLARE @json nvarchar(max);

;WITH src (n) AS
(
    SELECT n = sc1.name
    FROM sys.syscolumns sc1
    FOR JSON AUTO
)
SELECT @json = src.n
FROM src

SELECT @json, LEN(@json);

The chats that lead up to this can be found here.
The above query returns two columns.

The fully assembled JSON string
The length of that string

Ideally, you would replace the query between the two parentheses with your own query.
Why does this work?
According to Microsoft's Documentation

SQL Server uses a predefined column name for this rowset with one column of type NTEXT - “XML_F52E2B61-18A1-11d1-B105-00805F49916B” – to indicate chunked XML rowset in UTF-16 encoding. This requires special handling of the XML chunk rowset by the APIs to expose it as a single XML instance on the client side. In ADO.Net, one needs to use ExecuteXmlReader, and in ADO/OLEDB one should use the ICommandStream interface.

(While it is true that the above refers to XML, the same is true for JSON as well.)
The reason that JSON and XML responses are returned in chunks, to begin with, are for performance reasons:

For maximum XML [JSON] publishing performance FOR XML [JSON] does steaming XML formatting of the resulting rowset and directly sends its output to the server side TDS code in small chunks without buffering whole XML in the server space. The chunk size is 2033 UCS-2 characters. Thus, XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML.

The above solution circumvents this by setting the result of FOR JSON to a variable first, then sending the value of the variable, which results in SQL Server returning the response to the client as one row.
It should be noted that some database clients (specifically SQL Server Management Studio) are capable of "rebuilding" the chunked response, but if you are working with PHP (PDO) or operating on a Free Trial client (such as TablePlus for Mac) you will see the raw, chunked response.

Performance
As far as performance goes, I have not done any extensive testing, but I can provide the following data from the limited testing I have done:
Using a MacBook Pro running the latest OS, I have found that on average the following query
SELECT n = sc1.name
    FROM sys.syscolumns sc1
    FOR JSON AUTO

Will process in about 23,300μs
While the query
DECLARE
    @json nvarchar (max);

;WITH src (n) AS
(
    SELECT n = sc1.name
    FROM sys.syscolumns sc1
    FOR JSON AUTO
)
SELECT @json = src.n
FROM src

SELECT @json, LEN(@json);

Spent on average 137.8μs processing.
This seems to directly conflict with what the documentation says, so I am unsure how trustworthy these results are. However, it may be worth testing this yourself.