How to find the query that is still holding a lock?

SQL Server doesn't keep a history of the commands that have been executed1,2.  You can determine what objects have locks, but you cannot necessarily see what statement caused those locks.

For example, if you execute this statement:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

And look at the SQL Text via the most recent sql handle, you'll see that statement does show up.  However, if the session did this:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

You'd only see the SELECT * FROM dbo.TestLock; statement, even though the transaction hasn't been committed, and the INSERT statement is blocking readers against the dbo.TestLock table.

I use this for looking for uncommitted transactions that are blocking other sessions:

/*
    This query shows sessions that are blocking other sessions, including sessions that are
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Max Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS
(
    SELECT v.*
    FROM (VALUES
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.QueryText
    , agg.CountOfBlockedSessions
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;

If we setup a simple test-bed in SSMS with a couple of query windows, we can see that we can only see the most recently active statement.

In the first query window, run this:

CREATE TABLE dbo.TestLock
(
    id int NOT NULL IDENTITY(1,1)
);
BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

In the second window, run this:

SELECT *
FROM  dbo.TestLock

Now, if we run the uncommitted blocking transactions query from above, we see the following output:

╔═══════════╦═══════════════════════════════╦════════════════════╦═════════════════════════════════════════╗
║ SessionID ║           ItemType            ║ BlockedBySessionID ║                QueryText                ║
╠═══════════╬═══════════════════════════════╬════════════════════╬═════════════════════════════════════════╣
║        67 ║ Transaction                   ║                  0 ║ BEGIN TRANSACTION                       ║
║           ║                               ║                    ║ INSERT INTO dbo.TestLock DEFAULT VALUES ║
║        68 ║ Session Request, Waiting Task ║                 67 ║ SELECT *                                ║
║           ║                               ║                    ║ FROM  dbo.TestLock                      ║
╚═══════════╩═══════════════════════════════╩════════════════════╩═════════════════════════════════════════╝

(I've removed some irrelevant columns from the end of the results).

Now, if we change the first query window to this:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

and re-run the 2nd query window:

SELECT *
FROM  dbo.TestLock

We'll see this output from the blocking transactions query:

╔═══════════╦═══════════════════════════════╦════════════════════╦════════════════════╗
║ SessionID ║           ItemType            ║ BlockedBySessionID ║     QueryText      ║
╠═══════════╬═══════════════════════════════╬════════════════════╬════════════════════╣
║        67 ║ Transaction                   ║                  0 ║ SELECT *           ║
║           ║                               ║                    ║ FROM dbo.TestLock; ║
║        68 ║ Session Request, Waiting Task ║                 67 ║ SELECT *           ║
║           ║                               ║                    ║ FROM  dbo.TestLock ║
╚═══════════╩═══════════════════════════════╩════════════════════╩════════════════════╝

1 - not entirely true.  There is the procedure cache, which may contain the statement responsible for the lock.  However, it may not be easy to determine which statement is the actual cause of the lock since there may be many queries in the cache that touch the resource in question.

The query below shows the query plan for the test queries above since my procedure cache is not very busy.

SELECT TOP(30) t.text
    , p.query_plan
    , deqs.execution_count
    , deqs.total_elapsed_time
    , deqs.total_logical_reads
    , deqs.total_logical_writes
    , deqs.total_logical_writes
    , deqs.total_rows
    , deqs.total_worker_time
    , deqs.*
FROM sys.dm_exec_query_stats deqs
OUTER APPLY sys.dm_exec_sql_text(deqs.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) p
WHERE t.text LIKE '%dbo.TestLock%'  --change this to suit your needs
    AND t.text NOT LIKE '/\/\/\/\/EXCLUDE ME/\/\/\/\/\'
ORDER BY
    deqs.total_worker_time DESC;

The results of this query may allow you to find the culprit, but be aware, inspecting the procedure cache like this can be quite demanding on a busy system.

2SQL Server 2016 and above offer the Query Store, which does retain complete history of queries executed.


Comparing dates in a BEFORE INSERT trigger

With MySQL 5.5 or 5.6, you can use SIGNAL instead of the hacks to bail from a trigger.

Also, on a BEFORE INSERT trigger, only the NEW alias is used.  Not OLD.

CREATE TRIGGER chk_dates
BEFORE INSERT ON `job_history`
FOR EACH ROW
BEGIN
  IF (NEW.end_date < NEW.start_date) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'end_date cannot be earlier than start_date';
  END IF;
END;

No "else" is needed because taking no action in a BEFORE trigger allows the original action to proceed.


Best practice bring MS SQL database to remote sites

This is exactly what replication is designed for.  Sounds like Transactional replication with updateable subscriptions will work although MS is saying this will be removed down the road (no specifics)

Merge replication is also a choice, but is more complex to maintain.


Primary replica set server goes secondary after secondary fails

MongoDB requires a primary to be able to see a majority of the set in order to remain a primary. The majority is defined as being greater than n/2 machines where n is the number of machines in the replica set. So, if n is 1, then one machine constitutes a majority. When n is 2 or 3, 2 machines constitute a majority.

When the secondary is added, the primary now requires to be able to access two machines, itself and the secondary. When the newly added secondary goes down, the primary no longer sees a majority, and steps down.

This scenario is why an arbiter is recommended in addition to the primary and secondary. If you have two machines and one arbiter in the replica set, assuming the arbiter always stays up (which should be true because the arbiter does barely anything), then as long as one other machine is up, the arbiter will ensure that a primary will exist, because the other machine and the arbiter constitute a majority (2 out of 3).


What is the term for a “wide”, non-normalized view/query?

I am quite sure that the term you are looking for is derived relation.
In relational jargon, a single and normalized table is called a base relation (or base table). In a SQL database management system (DBMS), relations (tables) of this category are the ones declared with the aid of the data definition language (DDL).
The fact of applying certain relational operations (such as JOIN, for instance) on these base relations (or base tables), yields a different type of relation, which is known as derived relation (or derived table). It may be useful to think of this kind of relations as “virtual” tables (as you properly stated) that arise as a result of “playing” with the aforementioned base ones. Tables of this class are declared with the aid of the data manipulation language (DML) in a SQL DBMS.
In order to get practical advantages, a derived relation (table) can be set up as a VIEW in a DBMS. Of course, some of them can be wide and some of them can be narrow, and this aspect is determined by the number of base relations involved and the number of attributes (columns) defined in a particular operation.
To support the above, and because you want an accepted term, it is necessary to refer to an authoritative source. The best one is, naturally, the inventor of the Relational Model, Dr. Edgar Frank Codd. Regarding to these type of relations (tables), here is a small excerpt1 from his paper Extending the Database Relational Model to Capture More Meaning:

Base relations are those which are defined independently of other relations in the database in the sense that no base relation is completely derivable (independently of time) from any other base relation(s). Derived relations are those which can be completely derived from the base relations. It is this kind of relation which is normally employed to provide users or application programs with their own views of the database.

And here is an extract2 from his book The Relational Model for Database Management: Version 2:

…[R]elations, or R-tables, that are internally represented by stored data in some implementation-defined way are called the base relations or base R-tables. All R-tables other than base R-tables are called derived relations or, synonymously, derived R-tables. An example of a derived relation is a view. A view is a virtual R-table defined in terms of other R-tables, and is represented by its defining expression only.

References
1 Codd, E. F. (Dec. 1979). Extending the database relational model to capture more meaning, ACM Transactions on Database Systems, Volume 4 Issue 4 (pp. 397-434). New York, NY, USA.
2 Codd, E. F. (Jan. 1990). Introduction to Version 2 of the Relational Model. In The Relational Model for Database Management: Version 2 (pp. 17-18). Boston, MA, USA: Addison-Wesley


Querying a database efficiently for a huge chunk of data

Is your intention to get every row eventually?  For example, are you fetching all the rows and passing them to some sort of process that needs to process every row?  Or is your intention to present pages of results to a human who will likely only look at the first or second page of results.

Assuming that you are presenting results to a human that will only be looking at the first couple pages of data, the query you have is likely to be reasonably efficient.  Assuming that there is an index on the id column, in order to fetch rows 101-200, Oracle would simply have to read the first 200 id values from the index then filter out rows 1-100.  That's not quite as efficient as getting the first page of results but it's still pretty efficient.

Of course, as you get further and further into the data, the pagination query gets less and less efficient.  That's a problem if your intention is to eventually fetch every row.  It's generally not a problem, though, if you are presenting results to a human.  Humans don't really care how long it takes to fetch page 50 of a result set-- they're going to give up long before then.

If your intention is to send the data to a Java process to process the data (this will be substantially less efficient than processing the data in the database-- Oracle and PL/SQL are designed specifically to process large amounts of data), it would generally make sense to issue a single query without an ORDER BY, have a master thread on the Java application server that fetches N rows of data, spawns a thread to process that data, fetches the next N rows of data, etc.


How to update the statistics of an index that has just been reorganized, both with IndexOptimize script

I take it that you are referring to Ola Hallengren's scripts.
I can't find any parameter combo that would do what you ask. It is a pretty reasonable thing to ask for, though. IndexOptimize is pretty much separate in the handling of defrag vs updstats. Your options, as far as I can see:

Modify the proc yourself. It is only TSQL, in the end. Add an UPDATE STATISTICS after it does index reorg.
Create a branch on GitHub and see if it is merged into the main bransch. Now others can enjoy your effort (like we all are enjoying Ola's effort).
Send an email to Ola and see if he feels like doing the change.
Schedule a separate job for statistics - but you won't get the coupling to the previous reorg.
Choose a different maint solution.
Measure whether you actually gain anything from defrag, and potentially focus on statistics update instead.

Possibly some more, but those are the ones I can think of right now. :-)


Periodic snapshot fact table with monthly grain - Question on dimensions

You could go either in generally assuming you want to follow best practices of conformed dimensions you would want to update the customer daily so that other fact tables would have the updated information. what you don't want to do is have your previous snapshots updated to point to latest member as facts they are immutable and they should always point to the record as of the snapshot period


Let's do a comparison
MyISAM Caching vs InnoDB Caching
MyISAM only caches index pages from the .MYI files into a global buffer called the MyISAM keycache (sized by key_buffer_size). MyISAM Data does not cache data global. It only does so per DB Session (sized by read_buffer_size and read_rnd_buffer_size)
InnoDB has a very elaborate framework for managing data and index pages cached in memory (Pictorial Representation of InnoDB by Percona CTO Vadim Tkachenko).

The memory side of the framework caches data and index pages in the InnoDB Buffer Pool (sized by innodb_buffer_pool_size).
See my earlier posts on this

Apr 22, 2011 : Wordpress Database Slow - should I switch to InnoDB?
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Dec 22, 2011 : How does Wordpress handle MySQL row lock errors?

Table Write Behavior
MyISAM performs a full table lock with each DDL and and DML statement. InnoDB allows multiple transactions to access and update an InnoDB table. To increase write throughput for many transactions, you would need to increase the log buffer size of InnoDB (sized by innodb_log_buffer_size)
Read Speed
There are rare occasions where MyISAM can be faster to read than InnoDB. Such an occasion would be a high read/low write. For high read/high write systems, my money would be on InnoDB. Please read my answer to the May 03, 2012 post Which is faster, InnoDB or MyISAM?
CPU Utilization
Only InnoDB can take advantage of tuning for more CPU engagement

Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

MySQL 5.5 vs MySQL 5.6
While I could write many things about this, you are better off reading What's New in MySQL 5.6 to see those differences. You will even better off reading What Is New in MySQL 5.7 and going to MySQL 5.7 instead.


Updating a table with a not-joined query

You can either run the following as many times as it takes to get zero rows updated:

UPDATE IncentiveAssignments SET
  PersonID = (SELECT MIN(PersonID) FROM UnassignedPeople up WHERE NOT EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID)
WHERE AutoNum = (
  SELECT MIN(AutoNum)
  FROM IncentiveAssignments
  WHERE PersonID IS NULL);

And then run the following to clear all assigned persons:

DELETE FROM UnassignedPeople up
WHERE EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID);

Alternatively, you can use a couple of transient tables:

CREATE TABLE TmpAssignments (
  TmpID AUTOINCREMENT,
  AutoNum int);

CREATE TABLE TmpPersons (
  TmpID AUTOINCREMENT,
  PersonID int);

INSERT INTO TmpAssignments (AutoNum)
SELECT AutoNum
FROM IncentiveAssignments
WHERE PersonID IS NULL;

INSERT INTO TmpPersons (PersonID)
SELECT PersonID
FROM UnassignedPeople up
WHERE PersonID NOT EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = up.PersonID);

UPDATE IncentiveAssignments SET
  PersonID = (SELECT ta.AutoNum, tp.PersonID
              FROM TmpAssignments ta
              JOIN TmpPersons tp ON (tp.TmpID = ta.TmpID)
              WHERE ta.AutoNum = IncentiveAssignments.AutoNum)
WHERE PersonID IS NULL;

DELETE FROM IncentiveAssignments ia
WHERE EXISTS (SELECT 1 FROM IncentiveAssignments WHERE PersonID = ia.PersonID);

DROP TABLE TmpPersons;
DROP TABLE TmpAssignments;


Pt-query-digest date ranges do not work as expected

Preserve the correct sequence

I finally figured out the problem. The way how I extract all the gzipped log files and add them to a consolidated log file did not preserve the correct sequence and therefore pt-query-digest did not find the queries in the order it was expecting them.

The manual even says:

  Events are assumed to be in chronological: older events at the beginning of the log and newer events at the end of the log

Solution

The solution is to process the logrotate files in reverse order in order to end up with a logfile that has the oldest logs first and the newest ones at the end of the file.

ls mysql-slow.log* |sort -nr -t . -k 3,3 | xargs zcat -f > MySQL-All-Slowlogs.log

The command above does the following things:

ls: list all files starting with the file name mysql-slow.log.*
sort -nr -t: sort based on a numeric literal (otherwise the sequence number of the file will be interpreted as string alphabetically, sorting 111 before 2. -r reverses the order. -t uses "." as field seperator. k specifies the key to sort on.
xargs: For each line cat the zipped or unzipped slow log file to the culmulative file.

Then you can use the following comand to obtain

 pt-query-digest --since '2017-04-06 12:00:00' --until '2017-04-09 12:00:00'  MySQL-All-Slowlogs.log  > output.txt


MySQL Master/Slave. Actual use of Slave Machine

Can I simply point my office web server to the Slave MySQL server? Will they be able to write to the slave?

Short answer: Yes, but they probably shouldn't.

Depending on how your application works, it is possible (even likely) that writing to the slave will break replication. Even if it doesn't, over time it's likely that your databases will develop split brain - for example, someone could change some data on your slave that remains undetected for a long period of time.

If you are able to maintain two MySQL servers in your office, I would suggest the following:

Master server #1 on your trailer
Replicant server #2 in your office, acting as slave to #1
"Working" server #3 in your office

Server #3 would be the server that your staff connect to. Set up a job - maybe one that runs each night - which takes a backup of server #2 and then restores it over the top of server #3. This way staff can work with fairly recent data without the risk of affecting replication.

You could even make server #3 a slave to server #2 if it looked like that might work, though I suspect this might cause more problems than it solves.

Public cell and wifi networks are unlikely to be as secure as you like - I'm assuming that you will secure the connection to your office via VPN. If not, MySQL can secure communications via SSL and you may want to consider that.


Where can I find old versions of SQL Developer (4.x)?

Oracle removed the links, but the files are still there.
Example: http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-4.1.3.20.78-no-jre.zip
Of course before using that direct link, you need to go to any other public download page and accept the license agreement, but after that, it works.
Also the full clients contain some old SQL Developer versions.
11.2.0.4 client (for example: p13390677_112040_MSWIN-x86-64_4of7.zip) contains SQL Developer 1.55.
12.1.0.2 client (for example: p21419221_121020_MSWIN-x86-64_7of10.zip) contains SQL Developer 3.2.20.10.
I did not check newer versions.
You can download those files from https://support.oracle.com/, or any random site you can find with a Google search using the above filenames.


Cost threshold for parallelism and wait stats

SOS_SCHEDULER_YIELD and CXPACKET indicates that you’ve got contention and that queries are spending a lot of time waiting to get access to a CPU.
Check MAXDOP and Cost Threshold for Parallelism to determine when the query should be parallelized.


Can a function be immutable while calling a volatile function?

The documentation describes IMMUTABLE as

  IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. [...]

This means you can do the following:

CREATE OR REPLACE FUNCTION immutablerandom()
    RETURNS integer
    LANGUAGE plpgsql
    IMMUTABLE
AS $$
DECLARE a numeric;
BEGIN
    a := random();
    RETURN 1;
END;
$$;

random() is a volatile function, and immutablerandom() fulfills the criteria of the definition of IMMUTABLE.  If this actually makes sense is a different thing - you have to discard the results of the volatile function altogether, and in practice I cannot see a case where I could use it.


Create view in a different database with dynamic SQL

This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:

DECLARE
  @DB  NVARCHAR(255) = QUOTENAME(N'dbname'),
  @SQL NVARCHAR(MAX);

SET @SQL =  N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
  SELECT x = 1, y = ''''x'''', z = GETDATE();''';

EXEC sys.sp_executesql @SQL;

So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.

Another way that I've picked up since this original question appeared:

DECLARE
  @DB  NVARCHAR(255) = QUOTENAME('dbname'),
  @SQL NVARCHAR(MAX),
  @DBExec NVARCHAR(MAX);

SET @DBExec = @DB + N'.sys.sp_executesql';

SET @SQL = N'CREATE VIEW dbo.whatever
  AS
    SELECT x = 1, y = ''x'', z = GETDATE();';

EXEC @DBExec @SQL;

This is slightly tidier because you don't have to double-nest single quotes.

And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += NCHAR(13) + NCHAR(10)
  + N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.views WHERE name = ''vNonhiddenCategories'')
  AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + '.sys.tables WHERE name = ''categories'')
  BEGIN
    EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
    CREATE VIEW dbo.vNonhiddenCategories3
    AS
      SELECT x = 1, y = ''''x'''';''
  END'
  FROM sys.databases
  WHERE database_id BETWEEN 5 AND 32766;

PRINT @sql;
-- EXEC sp_executesql @sql;

Of course "tidier" is in the eye of the beholder.


Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

According to the official ALTER TABLE documentation, there should be no issue with such an operation and there is no window when there is no UNIQUE constraint at all. See (emphasis mine):

  Storage, Performance, and Concurrency Considerations

  In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary copy of the original table is created ...

Of course nothing forbids you from doing the operation in two steps (but this will probably be less efficient).

first add the new UNIQUE constraint (with a different name):

ALTER TABLE table_name
    ADD CONSTRAINT unique_name_2
    UNIQUE (field1, field2, ...) ;

the delete the old one:

ALTER TABLE table_name
    DROP INDEX unique_name ;

This allows a small window where you'll have both constraints but again no time where none of them is there.


Where can I download oracle 10g software for solaris?


Permissions for a MySQL user which will monitor replication status?

According to the MySQL Documentation on SHOW SLAVE STATUS\G

Either SUPER or REPLICATION CLIENT should do it. I would go with the minimum:

GRANT REPLICATION CLIENT ON *.* to user@host;


Visual Studio SSDT Unresolved reference to SSISDB

What we have done is create a new SSDT project, then import SSISDB and create a dacpac that can then be referenced in other projects.


In mysql if table fit into buffer pool is there any benefit to have partition?

If your purge strategy includes time-specific purge eligiblity, partitions could be helpful.  For example, if every quarter, you will purge everything over 5 years old, it will be in your best interest to have a quarterly data table plan in place.  Taking a table out of service is quicker/cleaner than DELeting a quarter's worth of data rows.  innodb_file_per_table=ON will be needed to be effective.  Filenames need to represent the datacontent, such as 2018Q1, 2018Q2 etc or possibly 2018W01 - W53 if you want calendar weeks to be able to purge.  Dream big, plan carefully.

For alternative to simple filenaming strategy as above,

Read more on: http://mysql.rjweb.org/doc.php/partitionmaint


MySQL join two table if second table is not empty

SQLFiddle demo

select users.*, notes.note from users
LEFT JOIN
(
select owner,max(created) as mc from notes group by owner
) t on users.id=t.owner
LEFT JOIN notes on t.owner=notes.owner and t.mc=notes.created


SQL Server unattended / Silent installation override Configuration.ini file in command line parameter

If you provide a parameter at the command line it will override that parameter in the configuration file. Additionally, any parameters not included in the configuration file can be added to the command line instead.

More info here.


Error purging PostgreSQL database. Reason: ERROR: syntax error at or near "exists"Position: 15

After checking postgres logs, I update postgres version from 8 to 9.2 and everything works now.


Oracle 10XE: Stop schemas seeing the all_users table?

You have a few options, some of which will make your database unsupportable by Oracle (if you care!) as you need to fiddle with SYS owned objects.

The first option is to revoke select privs from everyone:

revoke select on all_users from public;

Then grant it back to certain users, plus all other internal Oracle users.

The 2nd option is to look at the source for the view, drop it, then recreate an altered version that supports your needs.

Another option that would trick casual users would be to create a table called ALL_USERS in each schema. Unless the logged in person explicitly qualified ALL_USERS (ie: SYS.ALL_USERS) they would get the resultset from the table in their own schema. This trick could also be done with a synonym pointing to a view that does:

select * from all_users where username = (SELECT sys_context('USERENV', 'SESSION_USER') from DUAL);

Not much else you can do with XE I'm afraid.


Heartbeat Options with MySQL and Possibilities

The make-or-break element you should be concerned with is Replication Lag. Why ?
If you run SHOW SLAVE STATUS\G and Seconds_Behind_Master on the Slave is significantly high, failing over to such a Slave could be disastrous. Think of it:
If a Slave is 60 seconds behind, there is a 60 second window where

primary keys you need do not exist
changed column values do not yet exist
rows that should exist are not present
rows that should be deleted are still present

just to name a few possibilities.
SUGGESTION
Right after you've checked that the Master is fully down, you then need to check the Slave by running SHOW SLAVE STATUS\G to look for the following:

if Slave_IO_Running is not Yes
if Slave_SQL_Running is Yes
if Relay_Log_Space stops changing (This indicates that there are no more binlog entries coming over from the Master)
if Exec_Master_Log_Pos stops changing (This indicates that all relay log entries have been processed)

Once these four criteria have been met, the DB VIP can be assumed by the Slave.
CAVEAT
You may need to look into using MySQL with DRBD
Please read some of my earlier MySQL/DRBD posts:

Dec 26, 2012 : MySQL Replication and High Availability
Oct 17, 2012 : Setting up DRBD on an active MySQL server
Mar 29, 2011 : MySQL high availability, failover and replication with Latency

I suggest DRBD because the Slave is Replicating at the Disk Level synchronously.
A failover would just need DRBD mounting and InnoDB Crash Recovery on MySQL Startup.


SET @x = 0;
SELECT (@x:=@x+1) Pos,* FROM
(
    SELECT songname,COUNT(*) as count FROM etrstats
    WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
    GROUP BY songname ORDER BY count DESC LIMIT 5
) A;

Give it a Try !!!

I do incrementing of temp variables in other posts

Jun 08, 2012 : Update rank on a large table
Jun 03, 2013 : Embedded select with rank


Is there a way to disable / disallow someone from using View Creator using Diagram Tool in SQL Server?

The view designer comes with SSMS and cannot be managed by any database engine. So, you can completely block them from creating views with DENY permission (as long as they are not sysadmin role members), but you cannot restrict them using SSMS features as the features installed in their local PC:
DENY create view to ApplicationUser


SQL User with only access to maintain indexes and statistics

Create stored procedures that do these tasks for them - these can be set to execute as owner and the user account can just be granted execute rights on the stored procedures. It means they'll have to learn the interface to your stored procedures (so document them well or give them view definition rights to them also).


Detecting the locked table or row in SQL Server

I think this does what you need.

USE 'yourDB'
GO
SELECT
    OBJECT_NAME(p.[object_id]) BlockedObject
FROM    sys.dm_exec_connections AS blocking
    INNER JOIN sys.dm_exec_requests blocked
        ON blocking.session_id = blocked.blocking_session_id
    INNER JOIN sys.dm_os_waiting_tasks waitstats
        ON waitstats.session_id = blocked.session_id
    INNER JOIN sys.partitions p ON SUBSTRING(resource_description,
        PATINDEX('%associatedObjectId%', resource_description) + 19,
        LEN(resource_description)) = p.partition_id


Using NEXT clause to set periodic materilized view refresh in oracle and verifying refresh

I'd use DBMS_SCHEDULER instead, with a job for each refresh. It's then easier to manage, and view previous executions.

CREATE OR REPLACE PROCEDURE REFRESH_MY_VIEW
AS
BEGIN
    DBMS_MVIEW.REFRESH('MY_VIEW');
END;
/

Refresh at 8am every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8am'
    );
END;
/

Refresh at 8pm every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=20; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8pm'
    );
END;
/

The *_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS data dictionary views provide all the information you need to track what executed, when, and if any errors occurred. Documentation link here.

As pointed out in a comment, you can add multiple by hour clauses, comma-separated, as follows: repeat_interval => 'freq=daily; byhour=8,20; byminute=0; bysecond=0;'


what you have is a cross-table you need to uncross it.

create a table with three columns bulk,dip,and slide, put each cell of the excel table into a row of this table.

then you can query like

select bulk from bdstable where slide=0.8 and dip=212


What function quotes an identifier in dynamic-sql with SQL Server?

The function that you're looking for is QUOTENAME!

Through the practical use of square bracket technology, you can safely encapsulate strings to aid in the prevention of hot SQL injection attacks.

Note that just sticking square brackets around something does not safely quote it out, though you can avoid your code erroring with invalid characters in object names.

Good code

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = 'SELECT ' + QUOTENAME(d.name) + ' FROM your_mom'
FROM sys.databases AS d

Bad code

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = 'SELECT [' + d.name + '] FROM your_mom'
FROM sys.databases AS d

To give a specific example...

The following works fine for the initial input

DECLARE @ObjectName SYSNAME = 'sysobjects';

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT COUNT(*) FROM [' + @ObjectName + ']';

EXEC (@dynSql);

But with malicious input it is vulnerable to SQL injection

DECLARE @ObjectName SYSNAME = 'sysobjects];SELECT ''This is some arbitrary code executed. It might have dropped a table or granted permissions''--'

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT  COUNT(*)  FROM [' + @ObjectName + ']';

EXEC (@dynSql);

Using QUOTENAME correctly escapes the embedded ] and prevents the attempted SQL injection from happening.

DECLARE @ObjectName SYSNAME = 'sysobjects];SELECT ''This is some arbitrary code executed. It might have dropped a table or granted permissions''--'

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT  COUNT(*)  FROM ' + QUOTENAME(@ObjectName);

EXEC (@dynSql);

  Invalid object name 'sysobjects];SELECT 'This is some arbitrary code
  executed. It might have dropped a table or granted permissions'--'.


What is the purpose of the keyword "ID" in PostgreSQL?

In PostgreSQL, inserting a column named

"ID"

is one thing. Inserting a column named

ID

is another.

create table test (
  "ID" integer not null
);

insert into test values (1);

select *
from test
where ID = 1;

ERROR: column "id" does not exist

  Quoting an identifier also makes it case-sensitive, whereas unquoted
  names are always folded to lower case.


Is it possible to optimize this query? Currently taking days (no sign of stopping) on ~11 million records

First of all you need to get rid of the dependent subquery.
For this you could rewrite your query like this:

SELECT o.time_of_observation as last_seen_on, o.number,
       o.departure_time, o.departure_delay as last_known_delay
FROM observations as o
JOIN (  SELECT
        number,
        departure_time
        MIN(time_of_observation) AS minobstime
        FROM observations
        GROUP BY
        number,
        departure_time
) m ON m.number = o.number
   AND m.departure_time = o.departure_time
   AND m.minobstime = o.time_of_observation;

or this:

SELECT o1.time_of_observation as last_seen_on, o1.number,
       o1.departure_time, o1.departure_delay as last_known_delay
FROM observations as o1
LEFT JOIN observations as o2 ON o1.number = o1.number
  AND o1.departure_time = o2.departure_time
  AND o1.time_of_observation < o2.time_of_observation
WHERE o2.<any column, preferably your primary key> IS NULL;

Then it would be a good idea if you had a primary key. It can be an auto_increment column, too.

The columns in the index you have are in the wrong order. time_of_observation should be the last column in the index. First should be the columns where you make an equality comparison, then the ones where you scan a range.

Create an index like (number, departure_time, time_of_observation) and try one of the two queries from above.


Database Engine Tuning Advisor - The minimum storage space required exceeds the default

You have to go to Tuning Options and then Click Advanced Options. On the popup, define the max. space for recommendations.

https://www.sqlservercentral.com/Forums/Topic1383773-391-1.aspx


I don't think it was the run time edition that was the problem, most likely you:
-Were not set to a high enough compatibility level in your DBs
-Some of your tables might have not had primary keys
-End nodes perhaps were not accessible

Replication is great as it gives you a lot of flexibility in what to replicate over and what not to.  It also gives you very near real time reporting.  It does create 6 jobs and requires you to handle schema changes differently based on scenario you use.

Something much easier to manage might be to use async mirroring (if you have enterprise).  With Asynch mirroring you will not slow down commits on the primary site and you can take snapshots every day/12 hours/etc. then do your reporting off that.  Everything, including DLL and DML changes are replicated over and it doesn't need to create 6 jobs.


Splitting array of string values into Rows Numbers Table Ref Error

Your numbers.number is a double precision. But split_part() expects the third parameter to be an integer and the value isn't implicitly cast. Try to explicitly cast numbers.number.

...
, split_part(test_lead.test_mediums, ', ', numbers.number::integer) as medium_name
....


To explore a bit more about the question and comment, I want to note that generally when normalizing your goal (among others) is to reduce duplication.

A naive example of your table might be everything you have but adding things like the CSV list you mention, this ends with a very wide table that has multiple areas of duplication and which will require you to manage and clean your data (and is generally bad for performance.)

A solution to this problem is to simply project the data into another table and provide the keys to join back to the original.

Eg:

Table: Exams:
Columns: ExamID, ExamTitle, ExamCreatedDate

This table would only store information relevant to an exam, such as the title of the exam, the exam's attributes and categories.

Table: Questions:
Columns: QuestionID, QuestionTitle, QuestionValue

This table would store only the information relevant to the question itself.

Table: ExamQuestions:
Columns: ExamID, QuestionID, QuestionValueOverride (made up)

This table would store the ids for both the previous tables and any information that is specific to that exam's instance of that question.

This would allow you to write queries such as "How many questions belong to one exam"

select e.title, count(*)
from exams e
join examquestions eq on eq.examid = e.examid
join questions q on q.questionid = eq.questionid


How to recreate a SQL Server audit without an audit gap?

I want to reapply the audit and audit spec definition every evening in case the DBAs change it

Engaging in an arms race against the rightful admins is only going to cause delay and confusion.

Explain to DBAs the role and purpose of the audit, ask them not to change it
Use the audit itself to detect tampering
Follow up upon detecting tampering with administrativia to get the responsible party questioned/rewarded/punished depending on situation

If you cannot enforce above, it means by definition that you are not be able to enforce an audit, so why bother?

As for specifically the question, this advice applies always, to any situation: do not blindly re-apply a template 'in case it changed', only apply it if it had changed.

Ask your DBAs to help you, do not ask us to help you against your DBAs.


Your query wants to delete all the rows in table1 where the value of c is n.

Simply modify the DELETE statement so it no longer mentions the COUNT(*) > 0, since that doesn't change the semantics of the query at all.

DELETE
FROM table1
WHERE c = 'n';


The syntax you used was invalid. This is how you select the first 10 rows in pre-12c versions.

select * from
(
 select
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
    from
        customer,
        orders,
        lineitem
    where
        c_mktsegment = 'BUILDING'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '1995-03-15'
        and l_shipdate > date '1995-03-15'
    group by
        l_orderkey,
        o_orderdate,
        o_shippriority
    order by
        revenue desc,
        o_orderdate
)
     where rownum <= 10;


Is it possible to insert multiple IP addresses in a single entry in a pg_hba.conf file?

The documentation says:

  This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

  An IP address range is specified using standard numeric notation for the range's starting address, then a slash (/) and a CIDR mask length.

Your addresses cannot be expressed with a CIDR mask (without also matching the other 125 addresses in the 0…127 range).


Check Java Driver version in clients from Db2 server

This page https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001170.html  describes the client_product_id monitor element thus

  Use this element to identify the product and code version of the data server client. It is in the form PPPVVRRM, where:

  PPP identifies the product. This is "SQL" for IBM® relational database products.
     VV identifies a 2-digit version number.
     RR identifies a 2-digit release number.
     M identifies a 1-character modification level (0-9 or A-Z).

comparing what CLIENT_PRDID shows

SELECT distinct CLIENT_PRDID  FROM TABLE(MON_GET_CONNECTION(NULL, -2))

JCC04220

vs the jdbc type 4 driver version

java -cp $DB2_HOME/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -version

IBM Data Server Driver for JDBC and SQLJ 4.22.29

gets a close match for me.  I can't say I know why the mod level is not set.

on a different system, looking at the type 2 driver, I get JCC03690 from CLIENT_PRDID but IBM DB2 JDBC Universal Driver Architecture 3.72.44 from java com.ibm.db2.jcc.DB2Jcc -version.. so it's maybe nearly of right...


Try:

WHERE FileName >= N'ATT000'
AND filename < N'ATT001'

And make sure you have included columns to avoid lookups.


Looking at the query: most of your LEFT JOINs are not really LEFT JOINs because of the predicate ON TRUE which makes them behave as CROSS JOINs. Your query is considering every row that comes out of:
FROM screens s
INNER JOIN theatres t ON s.theatre_id = t.id
LEFT JOIN company_screen_mappings AS csm ON csm.screen_id = s.id AND csm.is_deleted = FALSE AND csm.company_id = 'f5003aa3-3621-498a-ab0f-526706fda88f'

against every combination of rows coming out of the sub-queries that get called r, p, c, ste, activation, and rc. This is going to be time-consuming if any or all of those parts return a significant number of rows. As it is needing to compare everything against everything with little or no constraining predicates, scans are unavoidable (it needs to read everything to do the comparisons).
To me that looks like a logic flaw which the GROUP BY existed to try work around (much like the common anti-pattern of fixing accidental cartesian products by adding DISTINCT), furthermore it looks like a query that has been generated by an ORM which has been given some incorrect joining options, though not knowing the application or what the query is trying to do I of course don't know either of those things with any certainty.
For how to proceed in such cases: first I'd ask the developer to look at what the application is asking for (if it is working through an ORM), it may be that the fix or simplification is to be done there. If this is a hand-written query (I doubt it would have been written in one go like this, but it may have grown over time as more and more features were added to the report/screen it supports) rather than ORM generated then my first thought would be to return to the spec and rewrite it to those requirements from scratch.


SQLAnywhere: Failure on creation of procedure

The problem is you are mixing the CREATE FUNCTION syntax with that for CREATE PROCEDURE

This should work:

CREATE OR REPLACE PROCEDURE __generate_new_VSN(@oldVSN varchar(100), OUT @newVSN  varchar(100))
BEGIN
      RETURN '1'
END

the full syntax is here


SQL Server: hierarchy of permissions for schema?

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

To see what can be GRANTed: GRANT Schema Permissions
To see what each permission means: Permissions Naming Conventions

The intersection of these two give the schema permission meanings:

CONTROL implies the rest and is the highest permissions of any securable
SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
EXECUTE to run scalar UDFs and Stored Procedures in that schema
ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy


I think 2 options that can help you:

Instead of getting the last number and add 1 you can create a table with an IDENTITY column (SerialNumber). When you insert one row the identity column adds the increment you specify (in your case, 1). If 2 users hit the button at the same time SQL server is capable to manage the concurrency and it will assign different numbers.
The same solution as number 1, but instead of using IDENTITY column you extract the next serial number from a sequence object. You create this sequence object and define the "Starts With" number and "Increment" number. Same way about the users, you extract the number from the sequence and this number can only be extracted once avoiding concurrency issues. With this solution, you have to keep in mind that if by any reason your insert fails the serial number is now lost, once you extracted it the counter of the sequence was incremented even though your insert was not committed.

Here you have the official links for extra details about how to create an identity column and a sequence object:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017


You need to create an Oracle DIRECTORY first.

CREATE DIRECTORY MYSTREAMSDIR as '/usr/oracle';

GRANT READ, WRITE ON DIRECTORY MYSTREAMSDIR TO YOURUSER;

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'nwisxfer',
    grant_privileges => false,
    file_name        => 'grant_strms_privs.sql',
    directory_name   => 'MYSTREAMSDIR');
END;

The grant isn't needed if you create the directory logged in as the streams admin user (usually strmadmin).


How can I force a scalar UDF to be evaluated just once in a query?

Ultimately, it is not possible to force SQL Server to evaluate a scalar UDF just once in a query. However, there are some steps which can be taken to encourage it. With testing I believe that you can get something that works with the current version of SQL Server, but it's possible that future changes will require you to revisit your code.

If it's possible to edit the code a good first thing to try is to make the function deterministic if possible. Paul White points out here that the function must be created with the SCHEMABINDING option and the function code itself must be deterministic.

After making the following change:

CREATE OR ALTER FUNCTION dbo.EXPENSIVE_UDF () RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @tbl TABLE (VAL VARCHAR(5));

    -- make the function expensive to call
    INSERT INTO @tbl
    SELECT [VALUE]
    FROM STRING_SPLIT(REPLICATE(CAST('Z ' AS VARCHAR(MAX)), 20000), ' ');

    RETURN 1;
END;

The query from the question is executed in 64 ms:

SELECT x1.ID
FROM dbo.X_100_INTEGERS x1
WHERE x1.ID >= dbo.EXPENSIVE_UDF();

The query plan no longer has the filter operator:

To be sure that it executed only once we can use the new sys.dm_exec_function_stats DMV released in SQL Server 2016:

SELECT execution_count
FROM sys.dm_exec_function_stats
WHERE object_id = OBJECT_ID('EXPENSIVE_UDF', 'FN');

Issuing an ALTER against the function will reset the execution_count for that object. The above query returns 1 which means the function was only executed once.

Note that just because the function is deterministic does not mean that it will be evaluated only once for any query. In fact, for some queries adding SCHEMABINDING can degrade performance. Consider the following query:

WITH cte (UDF_VALUE) AS
(
    SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;

The superfluous DISTINCT was added to get rid of a Filter operator. The plan looks promising:

Based on that, one would expect the UDF to be evaluated once and to be used as the outer table in the nested loop join. However, the query takes 6446 ms to run on my machine. According to sys.dm_exec_function_stats the function was executed 100 times. How that is that possible? In "Compute Scalars, Expressions and Execution Plan Performance", Paul White points out that the Compute Scalar operator can be deferred:

  More often than not, a Compute Scalar simply defines an expression; the actual computation is deferred until something later in the execution plan needs the result.

For this query it looks like the UDF call was deferred until it was needed, at which point it was evaluated 100 times.

Interestingly, the CTE example executes in 71 ms on my machine when the UDF is not defined with SCHEMABINDING, as in the original question. The function is only executed once when the query is run. Here is the query plan for that:

It's not clear why the Compute Scalar isn't deferred. It could be because the nondeterminism of the function limits the rearranging of operators that the query optimizer can do.

An alternative approach is to add a small table to the CTE and to query the only row in that table. Any small table will do, but let's use the following:

CREATE TABLE dbo.X_ONE_ROW_TABLE (ID INT NOT NULL);

INSERT INTO dbo.X_ONE_ROW_TABLE VALUES (1);

The query then becomes:

WITH cte (UDF_VALUE) AS
(
    SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
    FROM dbo.X_ONE_ROW_TABLE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;

The addition of the dbo.X_ONE_ROW_TABLE adds uncertainty for the optimizer. If the table has zero rows then the CTE will return 0 rows. In any case, the optimizer cannot guarantee that the CTE will return one row if the UDF is not deterministic, so it seems likely that the UDF will be evaluated before the join. I would expect the optimizer to scan dbo.X_ONE_ROW_TABLE, use a stream aggregate to get the maximum value of the one row returned (which requires the function to be evaluated), and to use that as the outer table for a nested loop join to dbo.X_100_INTEGERS in the main query. This appears to be what happens:

The query executes in about 110 ms on my machine and the UDF is evaluated only once according to sys.dm_exec_function_stats. It would be incorrect to say that the query optimizer is forced to evaluate the UDF only once. However, it is hard to imagine an optimizer rewrite that would lead to a lower cost query, even with the limitations around UDF and compute scalar costing.

In summary, for deterministic functions (which must include the SCHEMABINDING option) try writing the query in as simple of a way as possible. If on SQL Server 2016 or a later version, confirm that the function was only executed once using sys.dm_exec_function_stats. Execution plans can be misleading in that regard.

For functions not considered by SQL Server to be deterministic, including anything lacking the SCHEMABINDING option, one approach is to put the UDF in a carefully crafted CTE or derived table. This requires a little care but the same CTE can work for both deterministic and nondeterministic functions.


Extend iso currency data with user currency

You shouldn't be copying data from the currencies table into the user_currencies table, otherwise that defeats the purpose of having a currencies table.
If users are creating new currencies (besides the ISO currencies) then you could just have a single currencies table with the currency_code (primary key), name, and possibly an isStandard BIT field that you insert into. Then your user_currencies table will just store the mapping of currencies each user has created.
Then to get all standard codes and the ones a user has created you just need to join the two tables together like so:
SELECT currencies.[name]
FROM currencies
INNER JOIN users_currencies
    ON currencies.currency_code = user_currencies.code -- Gets only the user created currency codes
    OR currencies.isStandard = 1 -- Gets all the ISO currency codes
WHERE users_currencies.user_id = 'SomeUserId'

You can even create a View to save the above query minus the WHERE clause, and adding in the user_id field to the SELECT list, so you can later filter by a specific user.


It looks fine to me. It correctly avoids listing any employees from department five, even if there are multiple employees with the same highest salary in that department.


Why does SAMPLE BLOCK often return zero?

There doesn't seem to be anything specific mentioned in the documentation, but this Oracle Data Mining blog is interesting:

  SAMPLE relies on the existence of primary keys

I'm not sure this is true, but the existence of a primary key does play a role. The docs do say:

  Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the FULL or INDEX_FFS hint.

If you look at the plan for the query you are using, you can see it never touches the table, instead it is doing a fast full scan of the primary key:

select * from table(dbms_xplan.display);

| PLAN_TABLE_OUTPUT                                                                              |
| :--------------------------------------------------------------------------------------------- |
| Plan hash value: 130955250                                                                     |
|                                                                                |
| ---------------------------------------------------------------------------------------------- |
| | Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | |
| ---------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT               |             |     1 |    45 |     3  (34)| 00:00:01 | |
| |   1 |  SORT AGGREGATE                |             |     1 |    45 |            |          | |
| |   2 |   VIEW                         | VW_DAG_0    |   500 | 22500 |     3  (34)| 00:00:01 | |
| |   3 |    HASH GROUP BY               |             |   500 |  6000 |     3  (34)| 00:00:01 | |
| |   4 |     INDEX SAMPLE FAST FULL SCAN| SYS_C007394 |   500 |  6000 |     2   (0)| 00:00:01 | |
| ---------------------------------------------------------------------------------------------- |

The SAMPLE BLOCK is operating on the index — which has far fewer blocks and is distorting the sample:

select sum(blocks) blocks from user_extents where segment_name = 'PK_FOO';

| BLOCKS |
| -----: |
|      8 |

With a primary key with more blocks the variability is much less, and you can alleviate the issue even more by using the second parameter in SAMPLE BLOCK to restrict contiguous blocks sampled to 1:

create table foo(bar integer, baz char(1000), constraint pk_foo primary key (bar,baz));

…

select segment_name, sum(blocks) blocks
from user_extents
where segment_name like '%FOO'
group by segment_name;

SEGMENT_NAME | BLOCKS
:----------- | -----:
FOO          |    256
PK_FOO       |    256

with w(estimated_rows,distinct_blocks) as(
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1))
select * from w;

ESTIMATED_ROWS | DISTINCT_BLOCKS
-------------: | --------------:
          1076 |              77
           896 |              64
           924 |              66
          1006 |              72
           810 |              58

dbfiddle here

This isn't a full explanation, and it might not be possible to have one without knowing how Oracle does the sample internally. What is clear is that whatever Oracle is sampling from, either table or index, needs to be big to iron out the sampling wrinkles.


function to receive a character input and return date format (with incorrect input)

On SQL Server 2012 and later you can use TRY_CONVERT to check to see if the input can be converted.  If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.

begin
   declare @result date
   set @result = COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01')
   return @result
end

You could also use a TRY CATCH block and return the fixed date in the CATCH block, but it's best practice to use TRY_CONVERT so that SQL Server doesn't have to handle an error as that requires more time and resources.

A function for this type of code will incur more overhead than simply using the same logic in the query, so if it is being called many times every second you might chew up significant resource by using a function for it.  I understand that this may be called from numerous pieces of code so there is a desire to make it a function in case the default date needs to be changed--then it's no compiled code changes and just update this function.

If this code is going to be run a lot, you should consider other options that will provide better performance than a user-defined function.  Please see  Solomon's answer  for an overview of your options and further explanation of why you might choose one over the other.

For example, the following shows the same logic implemented as an inline table-valued function, which needs to be used with CROSS APPLY if not supplied with a static value, but performs much better than a scalar UDF:

USE [tempdb];

GO
CREATE
OR ALTER -- comment out if using pre-SQL Server 2016 SP1
FUNCTION dbo.ReturnDate (@Date VARCHAR(8))
RETURNS TABLE
AS RETURN
  SELECT ISNULL(TRY_CONVERT(DATE, @Date, 111), '2020-01-01') AS [TheDate];
GO

SELECT *
FROM   (VALUES (1, '20120101'), (2, '2012ABCD')) tab(ID, Input)
CROSS APPLY dbo.ReturnDate(tab.[Input]) dt
/*
ID    Input       TheDate
1     20120101    2012-01-01
2     2012ABCD    2020-01-01
*/


Your query does not exactly do a count of cities, but rather the count of users per listed city. To get that after de-duplicating users:
SELECT city, count(*) AS users
FROM  (
   SELECT DISTINCT ON (user_id) city
   FROM   cities
   ) sub
GROUP  BY city;

This picks one row per user_id arbitrarily like you specified. So we need no ORDER BY in the inner SELECT.
We need nothing but the city from the inner query for the bare count.
Detailed explanation for DISTINCT ON:

Select first row in each GROUP BY group?

Not deterministic for arbitrary pick
The above is typically fastest for few rows per user_id, while implementing stated requirements.
But the result is not deterministic while we pick rows arbitrarily. Can return different numbers for repeated executions as Postgres is free to pick any row for one user. (The total sum over all cities is stable, though, being the count of users.)
The result is normally stable, but any change to the table can trigger a different result. Like autovacuum doing its job in the background, or any unrelated write operation on the table.
To get deterministic results you need to add a deterministic ORDER BY to the inner query, so that DISTINCT ON always picks the same row. Like:
SELECT city, count(*) AS users
FROM  (
   SELECT DISTINCT ON (user_id) city
   FROM   cities
   ORDER  BY user_id, city  -- making the pick determinisitic
   ) sub
GROUP  BY 1;

Which is equivalent to:
SELECT city, count(*) AS users
FROM  (
   SELECT min(city)
   FROM   cities
   GROUP  BY user_id
   ) sub
GROUP  BY 1;


The write and sync phase mean rather different things (unfortunately).  In the middle phase of a checkpoint, it writes out all the dirty buffers from shared buffers.  It does this in a throttled way, mainly regulated by checkpoint_completion_target.  The time reported here is the time it spent writing those buffers, as well as the time it spend sleeping in order to regulate the rate at which the buffers get written.  So not all of the "write" time was spent actively writing.  The length of time spent in the write phase, 2.5 minutes, doesn't really mean anything without knowing what your other settings are.

During the sync phase, it syncs all of those files it just wrote to (as well as any data files other processes may have written to).  This stage is unthrottled, it syncs them as fast as it can.  That this took 34 seconds, with one file in there taking 16.143 seconds, suggests that your IO hardware is not up to the workload you are throwing at it.

The 159 synced files refers to the number of data files that had been dirtied since the end of the last checkpoint, and so needed to be synced before the end of this one.  The 47 refers to WAL files, not data files.  WAL files contain references to changes made to data files.  It is quite easy for 47 WAL files to contain references for 159 data files (or for that matter, 15,900 data files).  But it is not quite that simple, because the WAL files being recycled are the ones from two checkpoints ago, so the relation between 47 and 159 is an analogy, not a concrete count.


For that you should configure SQL*Plus output. Documentation what can be done and how to do that is here.

In your case it is:

SET HEADING OFF


Many-to-One replication / Transactional Replication

I see at least two options:

if you don't need near-real-time replication, but just once a day (or maybe every hour) is enough for you, and the amount of data is small, you can quite simply create a script that will run in each remote location:

connect to the local database
do a dump of the local data (pg_dump --data-only should be fine)
connect to the remote database
remove all data for that location from it
push the data from the dump.

if you need near-real-time replication, or the volume of data is large (but the changes are small in comparison), you could look into Slony. Haven't used it in a while, and it's not necessarily its typical use scenario, but I believe it should work as the datasets are distinct.

Slony uses triggers on the tables to store changes in a log, and then daemons that transfer the logs from one server to the other, and apply the changes. It would be worth checking if this would work in the "can't connect from central to remote" scenario, though. If it doesn't, you'll need to consider some form of VPN (which you should anyway).


MySQL - How to check for a value in all columns

SQL doesn't provide a good way of doing this because it's probably not a good table design to have N columns that might have values in the same domain. This is potentially a case of "repeating groups" like if you have columns phone1, phone2, phone3, ... phoneN.

If you have a number of columns that have the same logical domain of values, it could be a case where it's a multi-valued attribute (like the phones example). The standard way of storing multi-valued attributes is as multiple rows in another table, with a reference to the row they belong to in the primary table.

If you do that, then you only have to search for the specific value in one column of the dependent table.

SELECT t.* FROM mytable AS t
JOIN phones AS p ON t.primaryKey = p.refKey
WHERE p.phone = ?


Should I use table hints with a process-keyed table?

You said

.. no control over the stored procedures
.. low volume server, e.g. not too many of these batches will be running simultaneously.

Therefore, I'd do nothing. Hints should only be added very judiciously when required. As it stands, you have no known issue to solve.
If you still want to add a hint, then simply use ROWLOCK to disallow table locks.


Single AND operator and multiple OR operators

That's because in SQLite, the AND operator has a higher precedence than OR (see the Operators section on this SQLite documentation page)

This means that SQLite first evaluates the category_id=6 AND user_id = 1 expression and then ORs its result with all the other category_id conditions.

Thus, your query returns all records where category_id is 1,2,3,4, or 5, or where category_id is 6 and user_id is 1.

You should add parentheses to force the ORs to be evaluated first:

SELECT record_id
FROM tbl_people
WHERE
(
    category_id=1 OR
    category_id=2 OR
    category_id=3
)
AND user_id = 1;

Alternatively, you could use the SQL IN operator to make this more concise:

SELECT record_id
FROM tbl_people
WHERE category_id IN (1, 2, 3)
AND user_id = 1;

Also, note that you have a typo: in your WHERE clause there's a category=id, which should probably be category_id.

Update: Here's a link to try out the query: http://www.sqlfiddle.com/#!7/d7ad3/1


This is typical Microsoft. The error message is about tcp ports. The actual error is that the mirror database wasn't restored with NO RECOVERY

BTW this is the best step by step guide I've found to setting up mirroring in a workgroup situation

Database Mirroring between a Server in Domain and Server outside domain (workgroup)


Regnerating an execution plan - is this a valid approach?

OPTION (RECOMPILE) can change the execution plan.

For example if you try the below you will see a different plan with and without the hint commented out (on SQL 2008 SP1 CU5 and later) .

EXEC sp_executesql N'
SELECT COUNT(*)
FROM master..spt_values
WHERE @number IS NULL OR number =@number
--OPTION (RECOMPILE)',
N'@number INT', @number=1

If you connect via the DAC then the plans seem to be neither used from the cache nor saved to the cache without this optimisation side effect of RECOMPILE (which may be a practical solution if you are developing against your own instance).

Or failing that on 2008 you can at least be more surgical about just removing the specific plan as shown below.

DECLARE @plan_handle varbinary(64),
        @RC INT

SELECT @plan_handle = cp.plan_handle
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE  t.text LIKE '%@number IS NULL%' AND t.text NOT LIKE '%this_query%'
/*Needs to match some unique text in the query. Add a commented out GUID to it
  if there is no obvious candidate.*/

SET @RC = @@ROWCOUNT

IF @RC = 1
    DBCC FREEPROCCACHE (@plan_handle);
ELSE
    RAISERROR('Cache not cleared, %d matching rows found',11,1,@RC)


Replace value using several criteria in another table

Creating the schema - Tables that will be used for the example:

DECLARE @table_1 TABLE (
    someText VARCHAR(10)
)
INSERT INTO @table_1 values ('abcdefghij')
INSERT INTO @table_1 values ('fajiefhda')
INSERT INTO @table_1 values ('hurineajsd')

DECLARE @table_2 TABLE (
    value VARCHAR(10)
)
INSERT INTO @table_2 values ('d')
INSERT INTO @table_2 values ('f')
INSERT INTO @table_2 values ('h');

Procedural code starts here. First a temporary table is used to get the replace strings numbered so we can iterate on them:

DECLARE @table_2_numbered TABLE (
    RowNum int,
    value VARCHAR(10)
);

INSERT INTO @table_2_numbered
SELECT ROW_NUMBER() OVER (ORDER BY value ASC) AS RowNum, value
FROM @table_2;

The following CTE will pick up the original strings that we want to update (@table_1) and will iterate through each row of the table with the replacing strings, which are now numbered (@table_2_numbered), and recursively apply them to replace every desired occurence. Notice we keep track of the original strings so we know which replaced strings correspond to which original strings:

WITH CTE (originalText, replaced, iteration)
AS
(
    SELECT someText AS originalText
        , someText AS replaced
        , 1 AS iteration
    FROM @table_1 t1
    UNION ALL
    SELECT CTE.originalText
        , CAST(REPLACE(CTE.replaced, value, '') AS VARCHAR(10)) AS replaced
        , iteration + 1 AS iteration
    FROM CTE, @table_2_numbered t2
    WHERE t2.RowNum = iteration
)
UPDATE t1 SET t1.someText = CTE.replaced
FROM @table_1 t1, CTE
WHERE CTE.iteration = (SELECT MAX(RowNum)+1 FROM @table_2_numbered)
    AND t1.someText = CTE.originalText

SELECT * FROM @table_1

And here are the results:

someText
--------
abcegij
ajiea
urineajs


SQL Server Log shipping standby db stuck restoring

Turns out the log shipping configuration has reverted to NORECOVERY instead of STANDBY.

I think the is that there must have been an open session on the database, SSMS or alike and after a set period the log shipping is configured to revert back to NORECOVERY. The original configuration was did not have the 'disconnect users currently connected' option enabled.

To resolve the issue I simply went back into the original database and selected ship transaction logs and then selected the radio button for STANDBY instead of NORECOVERY


Is it possible to limit the scope of a SYNONYM in SQL Server?

Just switch the database context.

A handy way to do this is to call sp_executesql in the target database:

EG, with a static reference to the target database:

exec ClientA.sys.sp_executesql @job;

or dynamically:

declare @dbname sysname = 'ClientA';

declare @job nvarchar(max) = 'select db_name()';

declare @sql nvarchar(max) = concat('exec ', quotename(@dbname), '.sys.sp_executesql @job;');

--print @sql
exec sp_executesql @sql, N'@job nvarchar(max)', @job = @job;


Collation conflict when running dynamic sql FOR XML PATH

Another method would be to use COLLATE DATABASE_DEFAULT vs specifying the collation explicitly.

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);

SELECT @sql = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''
 + [sys].[database_principals].[name] collate database_default + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] collate database_default + '']''

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] collate database_default =[master].[sys].[server_principals].[name] collate database_default
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

SET @sql = (
          SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @sql + @vCrlf
                              FROM sys.databases SD
                              --INNER JOIN @DBS D ON SD.NAME = D.DB
                              WHERE  SD.state_desc = 'ONLINE' -->Skips the database if it is not online
                                AND SD.compatibility_level > 80

    FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)

--EXECUTE master.dbo.sp_executesql @SQL
print @sql

Above query will produce below print statement :

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
'CREATE USER ['
 + [sys].[database_principals].[name] collate database_default + ']' + ' FOR LOGIN [' + [master].[sys].[server_principals].[name] collate database_default + ']'

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] collate database_default =[master].[sys].[server_principals].[name] collate database_default
where [master].[sys].[server_principals].[type] in ('U', 'G', 'S')


What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

Those two columns are the result of

SELECT pg_stat_get_live_tuples(c.oid) AS n_live_tup
     , pg_stat_get_dead_tuples(c.oid) AS n_dead_tup
FROM   pg_class c;

Representing the number of live and dead rows (tuples) in the table.
Find those functions in the manual.

Dead rows are deleted rows that will later be reused for new rows from INSERTs or UPDATEs (the space, not the data). Some dead rows (or reserved free space) can be particularly useful for HOT updates (Heap-Only Tuples) that can reuse space in the same data page efficiently. More on H.O.T.:

PostgreSQL Initial Database Size
Release notes for PostgreSQL 8.3 where this feature was introduced.

Or dead rows may be removed by VACUUM FULL (or plain VACUUM if it gets lucky) or similar operations on the table, thereby shrinking the physical table accordingly.

Whenever a row is deleted or updated, the old row version becomes invisible to all other transactions starting after the transaction has been committed. The row is completely dead as soon as there are no more uncommitted older transactions. That is necessary for PostgreSQL's MVCC model to handle concurrency.

Those are just statistics. You need to enable statistics collection in postgresql.conf if you want them to be updated automatically. track_counts should be on by default, though. Bear in mind that statistics are not updated instantaneously. Read more about that in the manual.


Unable to change innodb_log_file_size in MariaDB Galera Cluster 10.0

In my experience setting up MariaDB 10.1 cluster, this behavior happens due to an override cnf file.

I had the same problem, that changing a setting in my.cnf and restarting mysql but the change was ignored. I found on ubuntu there is /etc/mysql/conf.d/mariadb-enterprise.cnf which overrides many settings. I comment out in the override file the value I want to change and then the value I put in my.cnf is used.


Unable to start the mongo services on debian server

That first log file tells that "client" connects to the server and gives dropDatabase command, so check your application. Why it executes dropDatabase.

What comes to the second problem, you need to check your mongodb.log, what it tells for the reason, why mongodb cannot start. Wild guess is ownership of some files (data, lock, log), all files must be owned by user what you use to start mongod process (probably mongodb -user)
Name and location of mongodb.log, you can find from /etc/mongod.conf -file


setting a foreign key to an empty string

There are two approaches you can take
APPROACH #1 : Add to professor table
INSERT INTO professor (PROFID) VALUES ('');

Then, all INSERTs to DEPTCHAIR will work.
APPROACH #2 : Disable Foreign Key Check
SET foreign_key_checks = 0;
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('', 'BIO');
SET foreign_key_checks = 1;


Design for archiving a sorted list (with ability for a few INSERT)

By all means use an unsigned int surrogate key as your primary, clustered index.  However, instead of using sequential values, build some padding into the sequence.  This means that you'll have to assign the id manually instead of using auto_increment.

If you use unsigned int in MySQL, the max value is 4,294,967,295.  If you expect to have at most 100,000 rows that means you could space each word out by more than 42,000.

When you need to insert a word between two existing words, just plug it into the space half way between.  Let's say you use 40,000 as your intitial padding value.  If you have "house" at 800,000 and "dog" at 840,000 you can insert "nouveau" at 820,000.


Try this:

select
 patients.name AS patient_name
,NULL As nurse_primary
,m.`name` As nurse_morning
,a.name As nurse_afternoon
,e.name As nurse_evening
FROM
patients left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='MORNING' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='MORNING')) AS m
ON m.patient_id = patients.id
 left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='AFTERNOON' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='AFTERNOON')) AS a
ON a.patient_id = patients.id
 left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='EVENING' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='EVENING')) AS e
ON e.patient_id = patients.id

SQL Fiddle


Can we move SQL Server installed bit from default location to other drive

The easiest method is (probably) to uninstall and this time run the command line installer again with a different set of flags and it will install to a different location unless you are talking about components not listed below.

From: https://msdn.microsoft.com/en-us/library/ms144259.aspx

Proper Use of Setup Parameters

Use the following guidelines to develop installation commands that have correct syntax:

   /PARAMETER
   /PARAMETER=true/false
   /PARAMETER=1/0 for Boolean types
   /PARAMETER="value" for all single-value parameters. Using double quotation marks is recommended, but required if the value contains a space
   /PARAMETER="value1" "value2" "value3" for all multiple-value parameters. Using double quotation marks is recommended, but required if the value contains a space

SQL Server Setup Control

/INSTALLSHAREDDIR

Specifies a nondefault installation directory for 64-bit shared components.
Default is %Program Files%\Microsoft SQL Server
Cannot be set to %Program Files(x86)%\Microsoft SQL Server

/INSTALLSHAREDWOWDIR

Specifies a nondefault installation directory for 32-bit shared components. Supported only on a 64-bit system.

/INSTANCEDIR

Specifies a nondefault installation directory for instance-specific components.


applicant_degree_1 its a subquery that contains:

SELECT (applicant_degrees.*)::applicant_degrees FROM applicant_degrees
WHERE applicant_degrees.college_attended_id = college_attendeds.id ORDER BY id LIMIT 1

  The query above try to get the greatest id from applicant_degrees for each college_attended_id.

Then, I beleive your query can run on MSSQL like this:

SELECT applicant_majors.*
FROM applicant_majors
WHERE applicant_majors.applicant_degree_id IN (
    SELECT MAX(id) FROM applicant_degrees
    WHERE applicant_degrees.college_attended_id = college_attendeds.id
)
ORDER BY id
LIMIT 1 OFFSET 1

  Notice that I changed the ORDER BY and LIMIT for a MAX aggregate function.

Hope it helps.


DB Design. Tracking history without losing referential integrity

This is a long-standing problem with lots of awkward solutions like the ones you referenced. The best ones still leave the problem of referential integrity. How do you reference an entity that changes over time and you want to maintain all the versions?

The solution actually turns out to be very simple: normalization. When we normalize an entity, there is a master entity table. This contains the PK of the entity and all other attributes. All FKs from other tables refer to this table as there is one and only one entry for any given entity.

After normalization, this table still exists but there is (at least) one other table that contains some of the attributes. There could be zero, one or several entries for any unique entity but that is fine. In fact, the PK of this table is the PK of the entity (which is also a FK back to the master entity table) and some other attribute.

So now you implement Version Normal Form. Take all the attributes of the main entity and separate out those attributes that change over time and you want to track those changes. What you have is the master entity table with the one and only PK of the entity (referential integrity works as normal) and all attributes that either don't change over time or the changes aren't tracked. Then the "version" table contains all the attributes that change and are tracked. The PK of this table is the entity PK and the change date (the date and time the version took affect).

A partial list of advantages of this scheme are

It uses normalization, a process we all know and work with daily.
Referential integrity is not affected.
Current and past versions are in the same table. In fact, the query to access the current state of the data and the query to look back into the past is the same query.
The change date can be set to the future for those instances when a state change is known ahead of time. Such planned changes do not appear in queries until the change time is reached.

There are two methods of "soft delete" that can be used. A "deleted" datetime attribute can be added to the master record with the default being NULL or (as I prefer) the magic date 9999-12-31. This is what I call a "firm" delete because it should not be undo-able. The other method is to add an "isDeleted" attribute to the version record. A delete operation would insert a version with "isDeleted" set to TRUE (or 'Y' or whatever). The deletion would be just another version. So to undelete, a new version with "isDeleted" turned off would bring the entity back into existence -- with the times it was deleted and then undeleted maintained like any other state change. Of course, both methods may be used if needed.

Here are the slides of a presentation I have made a few times at tech fairs. It covers how all the above can be done including the queries. And here is a document that goes into a lot more detail.


Find uncompressed size of all tables in a database

I need the actual size the uncompressed data will have.
  ...
  I would prefer to have size as correct as possible.

While the desire for this information is certainly understandable, getting this information, especially in the context of "correct as possible" is trickier than everyone is expecting due to faulty assumptions. Whether doing the uncompressed shadow table idea mentioned in the question, or @sp_BlitzErik's suggestion in a comment about restoring the DB and uncompressing there to check, it should not be assumed that the size of the uncompressed table == the size of said data in memory on the app server:

Are all rows in the table being cached? Or just within a range? The assumption here is that it is all, and that might be correct, but I figured it should at least be mentioned that this might not be the case (unless documentation states otherwise, but this is a minor point anyway, just didn't want it to not be mentioned).

Question was updated to state: yes, all rows are being cached.
Structure overhead

On the DB side:
Page and row-overhead on the DB side: How many rows fit on a page is determined by many factors that could throw off estimates. Even with a FILLFACTOR of 100 (or 0), there is still likely to be some unused space left over on the page due to it not being enough for an entire row. And that is in addition to the page header. Also, if any Snapshot Isolation functionality is enabled, there will be, I believe, an extra 13 bytes per row taken up by the version number, and that will throw off estimates. There is other minutia related to the actual size of the row (NULL bitmap, variable length columns, etc) but the items mentioned thus far should alone make the point.
On the app server side:
What type of collection is being used to store the cached results? I assume this is a .NET app, so is it a DataTable? A generic list? A SortedDictionary? Each type of collection has a different amount of overheard. I would not expect any of the options to necessarily mirror the Page and Row overheads on the DB side, especially at scale (I'm sure a small amount of row might not have enough various to matter, but you aren't looking for differences in hundreds of bytes or just a few kB).

Datatypes

On the DB side:
CHAR / VARCHAR data is stored at 1 byte per character (ignoring double-byte characters for the moment). XML is optimized to not take up nearly as much space as the text representation would imply. This datatype creates a dictionary of element and attribute names and replaces the actual references to them in the document with their respective IDs (kinda nice, actually). Otherwise, the string values are all UTF-16 (2 or 4 bytes per "character"), just like NCHAR / NVARCHAR. DATETIME2 is between 6 and 8 bytes. DECIMAL is between 5 and 17 bytes (depending on the precision).
On the app server side:
Strings (again, assuming .NET) are always UTF-16. There is no optimization for 8-bit strings such as what VARCHAR holds. BUT, strings can also be "interned" which is a shared copy that can be referenced many times (but I don't know if this works for strings in collections, or if so, if it works for all types of collections). XML may or may not be stored the same way in memory (I will have to look that up). DateTime is always 8 bytes (like T-SQL DATETIME, but not like DATE, TIME, or DATETIME2). Decimal is always 16 bytes.

All of that to say: there is pretty much nothing you can do on the DB side to gain even fairly accurate memory footprint size on the app server side. You need to find a way to interrogate the app server itself, after being loaded with a particular table, so know how big it is. And I am not sure if a debugger would let you see the runtime size of a filled collection. If not, then the only way to get close would be to go through all rows of a table, multiplying each column by the appropriate .NET size (e.g. INT = * 4, VARCHAR = DATALENGTH() * 2, NVARCHAR = DATALENGTH(), XML = , etc), but that still leaves the question of the overhead of the collection plus each element of the collection.

Given some new definition in the question, one could probably do the following query to get rather close. And it doesn't matter whether the table is compressed or not, though it's up to each person to determine if scanning all rows is appropriate on Production (maybe do from a restore or during off-peak hours):

SELECT
   SUM( DATALENGTH([NVarcharColumn_1]) + DATALENGTH([NVarcharColumn_N]) ) +
   SUM( (DATALENGTH([VarcharColumn_1]) + DATALENGTH([VarcharColumn_N])) * 2 ) +
   SUM(4 * [number_of_INT_columns]) +
   SUM(8 * [number_of_BIGINT_and_DATETIME_columns]) +
   SUM(16 * [number_of_DECIMAL/NUMERIC_and_UNIQUEIDENTIFIER_columns]) +
   etc..
FROM [SchemaName].[TableName] WITH (NOLOCK) -- assuming no Snapshot Isolation

But remember, this doesn't account for collection or collection element overhead. And not sure if we can get that value without a debugger (or possibly something like ILSpy, but I am not recommending that as it might violate the EULA depending on local laws).


HeidiSQL rounds down double values upon insert...select

Apparently HeidiSQL corrupts the table creation code. The proper table creation code is (without bounds after double):

CREATE TEMPORARY TABLE `new_aps` (
  `MinuteStart` datetime NOT NULL,
  `ProxyListID` bigint(20) NOT NULL,
  `SuccessCount` double NOT NULL,
  `UnknownCount` double NOT NULL,
  `FailureCount` double NOT NULL,
  PRIMARY KEY (`ProxyListID`,`MinuteStart`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And the proper way to get it is from the database server, rather than HeidiSQL client:

SHOW CREATE TABLE new_aps


VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102));

Starting from the inside, the ODBC escape sequence {D '2019-06-30'} returns a datetime.

(ignoring the redundant convert to datetime)

You're then converting that to a string with 102 style (rather than 105 for Italian).

You're then relying on an implicit conversion back to datetime to match the type of the target column.

The implicit conversion has a default style of 0 as you can see in the execution plan:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY for the same reason.

The reason it works on one and not the other is the default DATEFORMAT for the language in each case.

See datetime, SET LANGUAGE, and Write International Transact-SQL Statements in the documentation.

  Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the time, date, smalldate, datetime, datetime2, and datetimeoffset data types and character string data types.

Also SQL Server DateTime Best Practices by Aaron Bertrand.


Yes: the pattern you need is "synthetic keys."  For each incoming logical product, you'll assign a key, to be used only within the data warehouse.  If two source systems reference the same product, you can give them the same synthetic key (SK).  If you source systems use the same ID for two different products, you can give them each a different key.  Thus, in your warehouse you have a clean list of products.

As an example, the schema for my current SyntheticKeys.Products table looks like this:

CREATE TABLE SyntheticKeys.Products
(
SourceSystem    VARCHAR(30) NOT NULL,
ProductID   VARCHAR(30) NOT NULL,
ProductSK   INT NOT NULL IDENTITY (2, 1),
-- Housekeeping
ETLCreated  INT NOT NULL,
ETLModified INT NOT NULL
)

In this case, products are de-duplicated before they reach this table, so I can use IDENTITY to allocate synthetic keys, but you may prefer to set up a SEQUENCE object and get IDs from it when a record is identified as being a genuinely new product.

A couple of additional benefits from using synthetic keys:

You can use a 32-bit or even 16-bit integer, rather than 144-bit Salesforce IDs or other wide keys.  This keeps your fact tables narrow and fast.
You can reserve space for special records.  My convention is that SK #-1 means "not applicable" and SK #0 means "unknown"; thus, a customer who's never bought a product may have FirstPaidOrderSK of -1, rather than NULL, which is ambiguous.


Basically it comes to this:

Your photo is abourt a house, that belongs to a person. your photo has no persons in iit, but it still belongs to the person, so you can add a relationship to the photo.

The same goes for for locations the Washington Monument  is located between  Capitol and Lincoln Memorial so you can add a relationship between tose .


You may need to update /etc/hosts to map the local IP with the new hostname.


How to restore SQL Server 2008 R2 database to newly installed SQL Server 2008 R2 instance?

You can use SQL Server Management Studio and attach the database using GUI as described here.

Or using T-SQL, change the path to your drive & folder accordingly.

CREATE DATABASE MyDatabase
    ON (FILENAME = 'D:\MySQLServer\Database.mdf'),
    (FILENAME = 'D:\MySQLServer\Database.ldf')
    FOR ATTACH;

More at http://msdn.microsoft.com/en-IN/library/ms176061(v=sql.105).aspx.

The SQL Server Service Account needs read/write permission to those folders used to store the database files.

More reading on security and permission required at msdn.


Can PostgreSQL results include the query?

Use the -e flag when you start psql.
For example:

psql -e mydatabase

Or, in case of using a script, try the -a flag.
Details in the manual.


How can I find out what my SqlServer 2005 CPU is mostly working on?

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql

You can use SET STATISTICS TIME ON.

You can get a breakdown of:

SQL Server parse and compile time
SQL Server Execution Times

You can turn this on in Management studio for all queries or per session.


I Don't have PFILE or SPFILE just have a backup of init.ora and database is down

yes, backup of your parameter files is a smart thing to do, like backing up your database is a smart thing to do. If you still have an alert log file of your database, you can extract the parameter settings from there and use them to create a pfile using good old vi or whatever simple text editor you like.
Most important parameters are

instance_name
db_unique_name
control_files

Once your parameters are complete, you can verify them by trying to startup the database.

ORACLE_SID={YOUR_ORACLE_SID}
. oraenv
sqlplus / as sysdba
startup nomount pfile={your_pfile_full_name}

If this succeeds, your parameters are good enough to start an instance. Now see if your control_files parameter is OK:

alter database mount;

if your database mounts, you have access to your control file[s].

alter database open;

you could be back in business by now.
Since it is smarter to use an spfile, now is the time to create your spfile:

create spfile from pfile='{your_pfile_full_name}'

This creates the spfile for this instance with the default name in the default location (${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora). Shutdown your database and start it without specifying a pfile or spfile. It should select the just created spfile and give you back your database as it was before.


If you are bothered by being down to essentially one node during an SST, then make it a 4-node cluster.

If you are worried about the heavy disk I/O, well, you have not avoided that -- the restore will be heavy I/O.

If you think you can always avoid SST by trying to trick it into IST, you will probably be mistaken.  Galera sometimes does SST when you thing it should be able to do IST.