MS SQL Server - Working Mechanism of Top clause

As well as the documentation MBuschi pointed to, Bren Ozars free "how to think like the SQL engine" is a good resource as you start to dive into how SQL Server works in more depth to help understand how to optimise things. Also Use The Index, Luke is an excellent free resource, and not just for MS SQL Server.
To answer your question specifically:
If there is an index on Id, which is likely as it is probably the table's primary key, SQL server can use that for both the ORDER BY and WHERE clauses: it will use the index to seek to the first instance of Id being >0 only needing to read a couple of index pages to do so, then it will walk down the index to pick off the next 9 rows too. As you are only asking for Id to be output, it doesn't need to look for any more information after that.
If there is no index on Id then it has to work harder. It will scan the table from the beginning until it finds ten rows that match the where clause (which could be the first ten it reads, or it could have to scan all the way to the end, if there are not 10 there for it to find then it will have to scan all the way to the end). Once it has found those rows it will sort them before output. For such a small number this will be done in memory, but if you'd asked for a lot of rows it would have to spill the data to disk to perform the sort. In terms of complexity you can think of it as essentially creating a temporary table with an index on Id and put the matching rows into that to sort them then reading them back out - in fact if you look at the IO stats (add SET STATISTICS IO ON before your query, or click the relevant toolbar button in SSMS) you'll see this internal entity referenced as "Worktable".


SQL - Update certain database records with a loop

If you have to do it inside of a procedure with a set amount of BetGenerationBetID's, then you could use user defined table types.

Step 1: Create Type and test table

CREATE TYPE dbo.BetGenerationBetIDS AS TABLE
    ( BetGenerationBetID int);

CREATE TABLE dbo.TableBetGeneration(BetGenerationBetID int, [description] varchar(20));
INSERT INTO dbo.TableBetGeneration(BetGenerationBetId,[description])
VALUES (1,'Failed'),(2,'Failed'),(3,'Failed');

Step 2: Create the Procedure that uses the table type

CREATE PROCEDURE dbo.uspOverrideBetAsSuccesful
@BetGenerationBetIDTable dbo.BetGenerationBetIDS READONLY
AS
    UPDATE TBG
    SET TBG.[description]   = 'SUCCEEDED'
    FROM  dbo.TableBetGeneration TBG
    INNER JOIN @BetGenerationBetIDTable AS BGID
    ON TBG.BetGenerationBetID  = BGID.BetGenerationBetID

GO

Step 3: Enter your values and run the procedure

DECLARE @BetGenerationTable as dbo.BetGenerationBetIDs
INSERT INTO @BetGenerationTable(BetGenerationBetID)
VALUES(1),(2)

EXEC dbo.uspOverrideBetAsSuccesful @BetGenerationTable

Validate

SELECT * FROM  dbo.TableBetGeneration

Result

BetGenerationBetID  description
1                   SUCCEEDED
2                   SUCCEEDED
3                   Failed


I cannot fully answer this question but I can give you some tips on what to look around for.

First thing I would do is check the dates inside each partition as follows

SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p16q1) GROUP BY date_x;

If dates are partitioned correctly, then you have a bug-infested mysql in relation to the query optimizer. If dates appear in a partition that do not belong, I would reload the table from scratch as follows:

CREATE TABLE TABLE_Y LIKE TABLE_X;
INSERT INTO TABLE_Y SELECT * FROM TABLE_X;
ALTER TABLE TABLE_X RENAME TABLE_XXXX;
ALTER TABLE TABLE_Y RENAME TABLE_X;

Then, retry your queries.

I noticed you are using MySQL 5.1. There are many patches to the partition code (such as ALTER TABLE ... REPAIR PARTITION). I would suggest installing MySQL 5.6 on a test server, copying the data into it and try out your queries on.


For each value in field A, select the row with some special value in field B, while respecting further criteria

I think that is exactly the same case of the max-per-group but with some extended conditions and can be solved by single subquery:

SELECT IF( b.id IS NULL, NULL, a.id ) AS id -- an opposite to COALESCE()
     , a.cmid
     , a.userid
     , b.maxts -- can be NULL
  FROM table AS a
  LEFT JOIN ( SELECT id -- if you need no rows for users have no rows before
                        -- the given timestamp replace the LEFT JOIN
                        -- by plain JOIN
                   , cmid
                   , userid
                   , MAX(timecreated) AS maxts
                FROM table
               WHERE cmid = given_cmid
                 AND timecreated < given_timecreated -- additional condition
               GROUP BY userid
            ) AS b ON b.userid = a.userid
                  AND b.cmid   = a.cmid
                  AND b.maxts  = a.timecreated
  WHERE a.cmid = given_cmid
;

This query produce the result for each userid in the table including those having no rows before the given_timecreated but with maxts, id and cmid set to the NULL.

Here condition WHERE cmid = given_cmid is used twice to reduce the size of the subquery result.

Sure you need an index (cmid, userid, timecreated) for acceptable performance. The order columns listed in the index can be different according to the specific data you have.


SQL Server 2012 - Create Alias from default instance (i.e. '.') to named instance of SQL 2012

Turns out, it is possible, and after some more experimentation I figured this out. I think the missing elements were:

Using .\SQL2012 as the server name instead of MACHINENAME\SQL2012
Ensuring SQL Server Browser service is running.

Full Steps

Ensure SQL Server Browser service is started, as well as your named SQL2012 instance.
Open SQL Server Configuration Manager on your machine
Expand the SQL Server Network Configuration node, and double click Protocols for
Ensure TCP/IP is set to Enabled, if it's not, right-click and choose Enable
Double click TCP/IP

Ensure Listen All is set to Yes
Select the IP Addresses tab, and scroll to the bottom. Make a note of the value in TCP Dynamic Ports.
Press OK

Expand the SQL Native Client 11.0 Configuration node and double click Aliases

Right click in white space and select 'New Alias'
In Alias Name, enter '.'
In Port No, enter the value you noted earlier from TCP Dynamic Ports.
Leave Protocol as TCP/IP
In Server, enter .\<yourinstancename>
Press OK

Repeat the steps for SQL Native Client 11.0 Configuration for SQL Native Client 11.0 Configuration (32 bit)
Test the connection by using SSMS to attempt to connect to '.'


SSIS - insert system date variable as a execute sql task parameter

According to BOL DT_DBDATE is "A date structure that consists of year, month, and day". Which can't be used to store System::StartTime output as its DATETIME by data type, so try to use datatype DT_DATE instead of DBDATE. As datatype DT_DATE "A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits."


Updating rows in a large table in sql server

Adding an index on SOW_Number will allow SQL Server to very quickly identify the rows that need to be updated, without requiring a scan of the entire table (assuming a relatively small number of rows match the WHERE clause, making the index highly selective).

CREATE NONCLUSTERED INDEX IX_SOW_Number
ON dbo.FPP_Temp_Revenue(SOW_Number)
INCLUDE (
        Till_Prev_Inv_Amt_In_USD
        , Cur_Inv_Amt_In_USD
        , YTD_Inv_Amt_In_USD
    );

Even though you have SOW_Number defined as part of the clustered index, it is not the leading column of the index, which means SQL Server must scan the entire index looking for rows that fulfill the WHERE clause.  Adding the three columns listed in the INCLUDE clause will allow SQL Server to not perform a lookup into the clustered index for those values, which it would otherwise need to do to as they are used in the calculations inside the SET clause.

I wouldn't normally suggest adding an index like this without evaluating the workload carefully; however I'm assuming you don't have any indexes other than the clustered index you identify in your script.  Adding a single index in this case will most likely make a very big difference to the performance of your update, without dramatically affecting other portions of your workload.

I setup a test-bed on my laptop so I could test the performance of adding the suggested index vs having no index on SOW_Number.

First, I created a new database TestDB for this test (I'm using SQL Server on Linux RC3):

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON (NAME = 'TestDB_Primary', FILENAME = '/var/opt/mssql/data/TestDB_Primary.mdf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB)
LOG ON (NAME = 'TestDB_Log', FILENAME = '/var/opt/mssql/log/TestDB_Log.ldf', SIZE = 5GB, MAXSIZE = 10GB, FILEGROWTH = 1GB);
GO

USE TestDB;
CREATE TABLE dbo.FPP_Invoice_Revenue(
         Project_Code varchar(10) NOT NULL
       , Project_Desc varchar(50) NULL
       , SOW_Number varchar(10) NOT NULL
       , SOW_Desc varchar(50) NULL
       , Invoice_No varchar(50) NOT NULL
       , Inv_Month int NOT NULL
       , Inv_Year int NOT NULL
       , Billing_Date smalldatetime NULL
       , Doc_Currency varchar(10) NULL
       , Vertical varchar(255) NULL
       , Till_Prev_Inv_Amt numeric(24, 10) NULL
       , Cur_Inv_Amt numeric(24, 10) NULL
       , YTD_Inv_Amt numeric(24, 10) NULL
       , Till_Prev_Inv_Amt_In_USD numeric(24, 10) NULL
       , Cur_Inv_Amt_In_USD numeric(24, 10) NULL
       , YTD_Inv_Amt_In_USD numeric(24, 10) NULL
CONSTRAINT PK_FPP_Invoice_Revenue PRIMARY KEY CLUSTERED
(
    Project_Code ASC
    , SOW_Number ASC
    , Invoice_No ASC
    , Inv_Month ASC
    , Inv_Year ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY];
GO

Next, I inserted 10,000,000 rows to have a sufficiently large table.  I know, this is a bit smaller than your table, but it provides enough data to make reasonable extropolations:

TRUNCATE TABLE dbo.FPP_Invoice_Revenue;
;WITH v AS (
       SELECT v.num
       FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.FPP_Invoice_Revenue
SELECT Project_Code = CONVERT(varchar(10), ROW_NUMBER() OVER (ORDER BY v1.num, v2.num))
       , Project_Desc = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , SOW_Number = v5.num /* only 10 distinct values */
       , SOW_Desc = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , Invoice_No = CONVERT(varchar(50), CRYPT_GEN_RANDOM(50))
       , Inv_Month = ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 12
       , Inv_Year = 2009 + ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 8
       , Billing_Date = DATEFROMPARTS(2009 + ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 8, 1, (ROW_NUMBER() OVER (ORDER BY v1.num, v2.num) % 12) + 1)
       , Doc_Currency = CONVERT(varchar(10), CRYPT_GEN_RANDOM(10))
       , Vertical = CONVERT(varchar(255), CRYPT_GEN_RANDOM(255))
       , Till_Prev_Inv_Amt = CONVERT(numeric(24, 10), 1)
       , Cur_Inv_Amt = CONVERT(numeric(24, 10), 2)
       , YTD_Inv_Amt = CONVERT(numeric(24, 10), 3)
       , Till_Prev_Inv_Amt_USD = CONVERT(numeric(24, 10), 4)
       , Cur_Inv_Amt_In_USD = CONVERT(numeric(24, 10), 5)
       , YTD_Inv_Amt_In_USD = CONVERT(numeric(24, 10), 6)
FROM v v1 --10 rows
       CROSS JOIN v v2 --100 rows
       CROSS JOIN v v3 --1000 rows
       CROSS JOIN v v4 --10000 rows
       CROSS JOIN v v5 --100000 rows
       CROSS JOIN v v6 --1000000 rows
       CROSS JOIN v v7;--10000000 rows

Next, I create the non-clustered index I've suggested above:

CREATE NONCLUSTERED INDEX IX_SOW_Number
ON dbo.FPP_Invoice_Revenue(SOW_Number)
INCLUDE (Till_Prev_Inv_Amt, Cur_Inv_Amt, YTD_Inv_Amt);

This took about 1:20 to create on my laptop.

Now, the query:

UPDATE dbo.FPP_Invoice_Revenue
SET Till_Prev_Inv_Amt_In_USD = Till_Prev_Inv_Amt / 0.0285
    , Cur_Inv_Amt_In_USD = Cur_Inv_Amt / 0.0285
    , YTD_Inv_Amt_In_USD = YTD_Inv_Amt / 0.0285
WHERE SOW_Number = '6';

The test data is evenly distributed on 10 SOW_Number values; WHERE SOW_Number = '6' indicates we'll update 1,000,000 rows:

  (1000000 row(s) affected)

The actual execution plan for the update query:

The execution statistics for this:

  SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.
  Table 'FPP_Invoice_Revenue'. Scan count 1, logical reads 6567261, physical reads 8920, read-ahead reads 209603, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1000000 row(s) affected)

  SQL Server Execution Times:
    CPU time = 9376 ms,  elapsed time = 13689 ms.

As you can see from the above, the query only took around 14 seconds to update 1,000,000 rows.  If SOW_Number is more selective, the time required will be proportionally less.

As you've identified in your question, dropping the clustered index does not help increase performance of the update query.  Understand that all data in a table that has a clustered index is in fact stored in the clustered index.  Dropping and re-creating the clustered index will result in all 83 million rows being written to the log while data is moved in and out of the clustered index.  This is a lot of extra I/O that is not necessary for the update to succeed.

You have SOW_Number defined as a varchar(10); if the content of that column is always actually 10 characters long you might consider modifying it to be a char(10) instead of varchar(10), since that is actually more efficient.  Consider this for all the varchar columns you have.  Also, does Vertical actually need to be 255 characters long?  Presumably, Inv_Month and Inv_Year are actually not required to store up to 2,147,483,647?  You could likely convert those columns to smallint and save 4 bytes per row.  Keeping in mind the columns in the clustered index key are used (duplicated) in every non-clustered index, the space savings from right-sizing the data might be substantial.


How to use sqlcmd to create file names for output based on the day of the week or the date?

Below script will help you. Just save it as a .bat file.

@echo off
setlocal
set timehour=%time:~0,2%
sqlcmd -S SQL-CLUST1 -E -Q "SELECT * FROM TABLE" -o report-%date:~-4,4%%date:~-10,2%%date:~-7,2%-%timehour: =0%%time:~3,2%.txt

If you have xp_cmdshell enabled, then it is much easier :

DECLARE       @sqlCommand   VARCHAR(max)
DECLARE       @filePath     VARCHAR(100)
DECLARE       @fileName     VARCHAR(100)

SET    @filePath = 'C:\Temp\'

SET    @fileName = 'Output_' +
       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +
         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'

SET    @sqlCommand =
       'SQLCMD -S server_name -E -d master -q "select @@servername" -o "' +
       @filePath + @fileName +
       '" -h-1'

PRINT       @sqlCommand

--EXEC   master..xp_cmdshell @sqlCommand
GO


In a school database, do I need to have repeating data when a student can also be a teacher?

What you are saying makes sense, yes.

One thing is that you actually can't select students without excluding Persons that are teachers...  And even if you did, the students that are also teachers would be missing without further efforts. So maybe it makes sense to have a students table as well to assign Persons that are students the same way you did it with teachers.

Meaning:
P1 is a teacher.
P2 is a Student.
...

There are for sure more possible solutions. I think it all depends on the requirement you have and any solution must stand.

It's possible that a student also sometimes is a teacher and maybe even teaches the course where he or she is a Student... Or Maybe the school Administration used a database for different school let's say elementary same time as for parents night school. Maybe then additional flexibility is required.

Basically just go ahead and think about normalization to solve your requirement and get to a valuable Model. Using m:n tables to solve this just follows the normalization rules.

Normalization howto example:

https://www.studytonight.com/dbms/database-normalization.php


How to return null values from a joined table if the matching row is missing?

did you try outer join?

select sales_people.person_id, last_name, first_name, sales_region.Region_id, coalesce(trim(sales_region.name),'NULL')  AS 'Region Name'
from sales_region
right outer join sales_people_region on sales_people_region.region_id = sales_region.region_id
right outer join sales_people on sales_people_region.`person_id` = sales_people.person_id
group by  sales_region.region_id, sales_people.person_id
having sales_people.person_id >= ''
order by sales_people.person_id, sales_region.region_id asc;

`


How to create a versioning DDL/DML change script for SQL Server?

This is just my 2 cents worth...

The way the company I work for currently does their changes is by keeping each script separate and having every script re-runnable, therefore if you notice an issue with a piece of code, there will not be a problem altering it and re-running the whole script.

So we wrap every DDL statement inside a check to see if the change has already been made:

IF OBJECT_ID(N'dbo.my_table', 'U') IS NULL
BEGIN
    CREATE TABLE.....
END

Otherwise, with the way you've currently got the setup, if you make a mistake in version 2 and accidentally omit a key DDL statement, you cannot just add it to that version and re-run the entire v2 script. Firstly because the version number would have updated to 3, then, even if you alter the version number back to 2, the following line would throw an error as the column already exists:

alter table newtable add column newcolumn ...


Unable to create a tablespace, unusual error

Tracked it down. I set FILESYSTEMIO_OPTIONS=SETALL the other day. It appears that direct I/O works for the local RAID array but not for the external. When I set it back to NONE, I was able to create tablespaces on the external device.

A follow up: Does anyone know if you can enable direct I/O on a per device basis without using ASM or RAW?


I noticed you are running version 130. Apparently SSMS 2016 fails to run as different user as reported below. I have experienced this myself. Quite frustrating.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1315261c-0d9e-49bf-8334-6c0195015598/ssms-2016-fails-on-windows-10-version-1607-while-trying-to-execute-it-as-a-different-domain-user?forum=sqltools

https://connect.microsoft.com/SQLServer/feedback/details/3009405/ssms-2016-fails-on-windows-10-version-1607-while-trying-to-execute-it-as-a-different-domain-user


MySql: #1221 - Incorrect usage of UNION and ORDER BY

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.*
FROM comment AS tbl
    JOIN
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp
      ON tmp.sscm_id = tbl.sscm_id ;


How to sync the data between two identical DB

SQL Server Replication can ensure that your data is synchronized.  SQLRockstar is correct in saying that you need to determine if policy will allow the data to be stored on your laptop.

Questions to think about before moving ahead with replication:

How often is your laptop on the same network as your main server?
Do data modifications need to be two-way?  Do you just want your laptop to mimic the office server, or will you be making changes that need to be pushed back to the main server?
How big is your database expected to grow?  Will you have enough room on the laptop?
What happens if your laptop is lost/stolen/destroyed?  Will your data be safe?  Do you need to investigate in hard-drive level encryption (i.e. do you need to use bitlocker)?
How quickly do changes in one database need to be propagated to the other?

You may want to start off by investigating MERGE replication.


SQL Server Agent Jobs - Long running job delaying other jobs?

An easy workaround solution would be to nudge either of the jobs to start earlier/later and to not run together with the other job.

E.g.

    Job 1 Schedule: 00:14, 00:29, 00:44, 00:59, 01:14, ....
    Job 2 Schedule:                        01:00, .......

It's not an issue with the job scheduler, but an issue with the underlying tables that are being accessed. When Job 2 starts, it manages to acquire a lock on the users table (possibly: IX, IU) before the Job 1 is able to acquire its required locks on the same users table. Then Job 1 has to wait.

You are encountering blocking as previously observed by @MaxVernon. You could run the following script at the full hour to see if you can catch the statements involved:

SELECT sp.spid,
       sp.kpid,
       sp.blocked,
       sp.hostprocess,
       sp.waittype,
       sp.lastwaittype,
       DB_NAME(sp.dbid),
       sp.cpu,
       sp.physical_io,
       sp.memusage,
       CASE
            WHEN sp.blocked = 0 THEN sp.spid
            WHEN sp.blocked != 0 THEN blocked
       END               AS chain,
       dest.text
FROM   sys.sysprocesses  AS sp
       OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS dest
WHERE  spid > 50
       AND spid <> @@spid
       AND (
               spid IN (SELECT blocked
                        FROM   sys.sysprocesses
                        WHERE  blocked != 0
                               AND spid > 50
                               AND spid != @@spid)
               OR blocked != 0
           )

The output will show statements that are blocking or blocked. You will probably find that the statements are from the Job 1 and Job 2.

Possible solutions:

Adding a NOLOCK hint can help, but should be avoided if possible.
Then you could also consider optimising the code so that the steps in Job 2 run faster.
Changing the schedules of the Job 1 and/or Job 2.


The transaction log for database 'database' is full due to 'LOG_BACKUP'. Nothing works

Performing full database backups does not truncate the transaction log.  In order to truncate the transaction log you have to perform a transaction log backup.  If you changed the recovery model to simple and still received the error, then this means you had active transactions that could not be cleared by checkpoint.

Relying on autogrow for capacity planning is not a good idea.  A better idea is to set your transaction log large enough so that auto grow does not occur.  I don't see where you tried to manually increase your transaction log size in your troubleshooting steps, nor do I see where you tried to perform a transaction log backup.

Another thing you could have tried is to add another transaction log file, just to see if that would resolve the error and allow processing to continue.  I would remove that additional file after completing my troubleshooting.

There are a lot of possibilities here, so I would recommend setting the log size larger than it was before, change the autogrow settings larger than 1mb and set up frequent, regular transaction log backups to keep the tranlog size on the smaller side.

If you don't want to mess with tranlog backups, then keep the recovery model on simple, but realize this means you will only be able to restore back to your last full backup, and it is still possible for a tranlog to fill up when in simple recovery mode.


When was data last inserted/updated/deleted in a table?

If the data in the other solution is acceptable to you, then you could simply roll your own data collection to make it persist. For example, create a table to store object_id and last seek/scan/update. Every n minutes, take a snapshot of the DMV. Then check the max in the snapshot, and if it's greater than the max in the rollup, update the rollup. You can keep the snapshots as history or you can clean them up over time once you've coalesced.


How to architect an analytical BI platform?

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL.  I would not do that unless you perceive a specific need to do so.  The only times I've ever heard of this being done due to interactions with legacy data sources.  Although @Kermit works with Vertica and will be more familiar with the platform, I don't see any reason to assume you need another DBMS platform for your ETL.

The reason Vertica and other systems are ill suited to transactional applications is that they use data structures that are fast to read from and batch load, but have a significant overhead per load operation.  A batched ETL process that requires index rebuilds or other such operations will be a non-issue on these systems.  The operations are too expensive for a high-volume OLTP platform but will not be too slow for a batched ETL job.

A common architecture used in telcos and other VLDB shops is to use flat files for intermediate storage and then load into the database.  Ab Initio and Ascential Datastage are tools that are designed to work in this way and this type of architecture is common on telcos.  However, ETL tools tend not to support complex transformations well, so you may well end up with a layer of data manipulation taking place within the database.

All VLDB platforms are expensive, and some are tied to proprietary hardware.  Be prepared to spend up big on your server.  3 years data at 50GB/day is around 50TB, which is getting into the region where you would need specialised kit to perform well on these data volumes.


There is a setting that was introduced in MySQL 5.5.30 : innodb_print_all_deadlocks

  When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. Otherwise, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command. An occasional InnoDB deadlock is not necessarily an issue, because InnoDB detects the condition immediately, and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are happening if an application does not have appropriate error-handling logic to detect the rollback and retry its operation. A large number of deadlocks might indicate the need to restructure transactions that issue DML or SELECT ... FOR UPDATE statements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition.

Just add this setting to my.cnf

[mysqld]
innodb_print_all_deadlocks = 1

or

[mysqld]
innodb_print_all_deadlocks = on

You don't have to restart mysql. Just login to mysql and run

mysql> SET GLOBAL innodb_print_all_deadlocks = 1;

or

mysql> SET GLOBAL innodb_print_all_deadlocks = 'ON';

This setting is new to me too.

Give it a Try and tells us all what you think !!!


in my case I could simply fix the table-space-missing problem by:

cd /var/lib/mysql/psa/
cp -a ModuleSettings.ibd.bak ModuleSettings.ibd

psa.ModuleSettings was the table which gave me «#1812 table space is missing»-erros.


I believe that is because I'm not creating the procedure inside each
  DB. What is the clean way to do the "If Exists" and "Create Procedure"
  repeatedly?

If I understood you correct, you need to execute the same procedure in many databases.

For example, it can be a procedure that makes a script of all the indexes of the specific table, in this case the procedure is always the same but it should be "local" respect to database where the table resides, this way you can use sys.indexes/sys.columns that are "database sensitive".

The simplest way to do this is to make your procedure "system" procedure, i.e. it will be created in master database, it will have sp prefix and marked as system, so that it can be called from any database and will always use system tables "local" to the database from which it was called.

To accomplish this you should create your procedure only one time in master database as you did:

USE master
CREATE PROCEDURE sp_procedure AS ...

And mark this procedure as system one:

exec sys.sp_MS_marksystemobject 'sp_procedure'

Noe when you run your code like this

USE aDB
exec sp_procedure 'With Arguments'

You will not get

  sp_procedure does not exists

anymore.

........................................................

If you still prefer to create this procedure many times in every database the simplest way to do it is to save your procedure script in a file including the rows

IF EXISTS  Procedure
THEN DROP
GO

And then launch this script from every database:

sqlcmd -S <ComputerName>\<InstanceName> -d <dbname> -i <MyScript.sql>

(you should add -U MyLogin -P MyPassword if you don't want to use Integrated Security).

You should format this string dynamically in your cursor addind dbname like this:

set @cmd = 'sqlcmd -S <ComputerName>\<InstanceName> -d '+ @db_name + ' -i <MyScript.sql>';

exec master.dbo.xp_cmdshell @cmd

.................................................................

Otherwise, you should put the whole text of your sp (create proc...) in a string variable and execute it in your cursor:

declare @sp varchar(max) = cast(' CREATE PROCEDURE ' as varchar(max)) + '[dbo].[Procedure].....'

declare @sql varchar(max) = 'USE ' + @db_name + @sp
EXECUTE(@sql);


Its clear to me now that the Host name in the listener configuration on the Oracle server is indeed the IP/Name of the Oracle server where the database exists and the port number is the port this database will listen to incoming connections. I was wrong in assuming that each incoming connection must be defined as a listener.


How can I change default backup location for oracle11g using RMAN?

Some things which might help:

Don't backup into a single file.

allocate CHANNEL d1 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

Use as many channels as you can stand.

allocate CHANNEL d2 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

allocate CHANNEL d3 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

allocate CHANNEL d4 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

RMAN parallelizes based on the number of channels you allocate; each channel takes part of the work to do.

Have faster disks - both on the system being backed up AND on the system to which backups are being written.  Are you backing up the database over the network?  If so, you're limited by the transmission speed of the network and competing network traffic - probably database-related activity.


You can run them in SQLPlus like this;

@DDL/table1.sql
@DDL/table2.sql
@fns/function1.sql
@fns/function2.sql
@views/view1.sql

Make sure each one commits where necessary and don't finish each individual script with EXIT.


calculate days spent of all visitors from encounters

I have achieve required results with this query

(WITH params (start_dt, end_dt) as (values ('2019-02-14'::timestamp, '2019-02-18'::timestamp)),
  dates AS ( SELECT (CASE
                      WHEN (visitors.start_date < params.start_dt) THEN params.start_dt
                      ELSE visitors.start_date END)                        AS start_date,
                    (CASE
                      WHEN (COALESCE(visitors.end_date, params.end_dt) > params.end_dt) THEN params.end_dt
                      ELSE COALESCE(visitors.end_date, params.end_dt) END) AS end_date,
                    visitors.id                                             as visitor_id
            FROM "visitors",
                  params)
  SELECT date_part('day', age(dates.end_date, dates.start_date)) days,
        dates.end_date,
        dates.start_date,
        dates.visitor_id
  FROM dates,
      params
  where dates.start_date >= start_dt
    AND dates.end_date <= end_dt);

Note: I would love to edit this answer(if wrong or needs update) or will appreciate if someone has better answer


operator does not exist: interval < date

Although the question states that DATE_FIN_SOUHAITEE is a DATE, the error message "operator does not exist: timestamp without time zone - numeric" makes it clear that it really is a timestamp. In Postgres - unlike Oracle - a timestamp is something substantially different than a date (in Oracle they are more or less the same thing - they just differ in the precision).

In Postgres you can only subtract an interval from a timestamp, and you can subtract integer values from a date

If V_SEUIL_ALERT is indeed a varchar that stores a number that represents years, you need to convert that string value to a proper interval in order to be able to subtract it from a timestamp:

V_C_CRITERE2.DATE_FIN_SOUHAITEE - make_interval(years => V_SEUIL_ALERT::integer)

the above can then be compared using < with current_date.

I don't know what the original code in Oracle did, because DATE_FIN_SOUHAITEE - V_SEUIL_ALERT would implicitly convert V_SEUIL_ALERT to a number which is then taken as days to be subtracted from DATE_FIN_SOUHAITEE, not years.

If that column indeed stores days rather than years (as stated in the comments), you need to use:

make_interval(days => V_SEUIL_ALERT::integer)

You should really take the opportunity of that migration and fix the wrong data type for the column V_SEUIL_ALERT to be an integer, numeric or even interval if that is what you use it for. Never store numbers in VARCHAR columns. That was already a really, really bad idea in Oracle.


Which will be faster for Millions of records? Procedure or Insert statement with multiple values?

Stored Procedure is better, why? Read this: http://www.sqlbook.com/SQL-Server/Why-use-Stored-Procedures-41.aspx

When a query runs, it calculates the most efficient method of processing the query and stores it in something called the execution plan. Depending on the query, a good deal of the time taken to execute a query is actually spent on calculating the execution plan.

When inline SQL statements are executed the execution plan must be generated each time the query runs. When a stored procedure is called its execution plan is stored in memory and the next time the stored procedure is called the execution plan is retrieved from memory without needing to be recalculated. This increases the speed of execution and improves database performance.


There is a handy dandy utility in DB2 LUW (in at least version 9.7 and higher). I'm not sure when it exactly entered the product.

Sizing tables! And stored procedures that help calculate size.

First you need to run the stored procedure CREATE_STORAGEMGMT_TABLES, specifying the tablespace where you wish to put these (I recommend SYSTOOLSPACE). Once you have those you can run the CAPTURE_STORAGEMGNT_INFO stored procedure. It is generally recommended that you first run RUNSTATS on all tables with RUNSTATS ON TABLE (TABLESCHEMA.TABLENAME) ON KEY COLUMNS AND INDEXES ALL. Then run call capture_storagemgmt_info(0,<SCHEMA_NAME>,<DATABASE_NAME>) at least the first time (as per recommendation from IBM). After that you can run it for specific objects. This gives you the size in bytes for everything, tablespaces, tables, indexes, partitions, your database itself....

You do need to prune these tables manually as they will grow over time with use. (The reason is they just INSERT new snapshots of what sizes are.) The nice thing is you can use these to trend growth for your objects and even the whole database. If you wish to prune the tables, I tend to use delete from systools.stmg_root_object where stmg_timestamp < (current timestamp - 30 day). There is a cascading DELETE from that parent object (at least right now) that takes care of cleaning out all the child tables.

Here is an example of a report I run across this

select
t.main_tbspace as tablespace,
o.obj_schema as schema,
o.obj_name as table,
(max(t.estimated_size))/1024/1024 as maximum_size_in_mb,
(min(t.estimated_size))/1024/1024 as minimum_size_in_mb,
(avg(t.estimated_size))/1024/1024 as average_size_in_mb,
count(t.stmg_timestamp) as snapshots
from
systools.stmg_object as o,
systools.stmg_table as t
where
o.obj_id = t.obj_id and
o.stmg_timestamp = t.stmg_timestamp
group by t.main_tbspace,o.obj_schema,o.obj_name
order by maximum_size_in_mb desc, average_size_in_mb desc, minimum_size_in_mb desc
with UR"

And if you ever don't want the tables anymore, there is always the DROP_STORAGEMGTM_TABLES stored procedure.

Hope this helps!


How could I improve the performance of this MySQL query?

Try using EXISTS instead of IN, it tends to take good shortcuts when filtering your main records.

Also, you had a redundant ORDER BY within your IN filter, I hope the MySQL optimiser got rid of that for you, but you might as well remove that from the code since is has no effect on your results.

Actually, looking closer at your last IN filter, it seems pretty redundant, as it's joining receentry to itself, using horsename and raceid?

Try the below:

SELECT DISTINCT r.racedate, re.trainerid
FROM raceentry re
JOIN race r ON r.raceid = re.raceid
WHERE re.trainerid IS NOT NULL
    AND re.Trainer30in90Starts IS NULL
    AND EXISTS (
        SELECT 1
        FROM pp_entries ppe
        WHERE re.trainerid = ppe.trainerid
        )
ORDER BY r.racedate DESC, re.trainerid
LIMIT 500


What does "as anonymous on" means in mysql's general log?

After some major league Googling all over the planet, I think I found the answer.

http://dbaspot.com/mysql/70551-how-disable-any-logging-mysql.html
https://lists.drupal.org/pipermail/support/2014-June/025447.html

From these posts I saw something interesting

13679547 Connect @localhost as anonymous on test
140612 12:33:07     3 Connect   gk_admin at localhost as anonymous on dTEST_db

My conjecture is the following: What follows as anonymous on is the currently connected database
mysql -u root -Dmysql -p -BNe 'SELECT USER(),CURRENT_USER()'

The general log entry must say as anonymous on mysql
mysql -u root -Dtest -p -BNe 'SELECT USER(),CURRENT_USER()'

The general log entry must say as anonymous on test
mysql -u root -p -BNe 'SELECT USER(),CURRENT_USER()'

The general log entry must say as anonymous on
Give it a Try !!!
UPDATE 2014-12-04 14:26 EST
Are you ready for this? I have an old post where someone has the source code with as anonymous on
log_warnings doesn't seem to work for aborted connections in MySQL 5.1
In the code from my 2-year-old post, as anonymous on is hardcoded !!!
Look at the code again
[29 Sep 2008 8:03] Konstantin Osipov
OK, it was fixed differently:
      /*
        Log the command before authentication checks, so that the user can
        check the log for the tried login tried and also to detect
        break-in attempts.
      */
      general_log_print(thd, command,
                        (thd->main_security_ctx.priv_user ==
                         thd->main_security_ctx.user ?
                         (char*) "%s@%s on %s" :
                         (char*) "%s@%s as anonymous on %s"),
                        thd->main_security_ctx.user,
                        thd->main_security_ctx.host_or_ip,
                        db ? db : (char*) "");

So, this is logged in the general log at least.

I was reviewing a patch that added more logging,
but I can't remember the worklog task number.

Please note the part of the code that references the database db ? db : (char*) "");. Either the db variable (the current database) points to a database name or an empty string.
The weird part about answering this question is that this is the second time I had to refer back to a post I myself wrote years ago. The first time was this: Does MySQL still handle indexes in this way?
UPDATE 2014-12-17 16:45 EST
Based on your last comment

But what exactly is your answer? That it doesn't matter what "as anonymous" mean? Otherwise, we've got yet to find out why thd->main_security_ctx.user is falsy.

In your question, you asked What is this "as anonymous on" thing even means? The answer I said was Either the db variable (the current database) points to a database name or an empty string. As for thd->main_security_ctx.user, thd->main_security_ctx.host_or_ip, that would be root@localhost. What comes after as anonymous on is the current DB. This is nothing left to figure out.
You then asked Am I still anonymous, or even "anonymous on"?. The answer would have to yes because you have been authenticated as a user of a DB Connection and your connection becomes an active thread like any other thread.
Any connection

DB connection from an authenticated user
system user (internal DB Connection for Replication)
Event Scheduler (DB Connection for Processing events)

are all equal threads. The general log does not differentiate one DB Connection from another. The code checks all connections the same way regardless of connections being internal or external.
If what my answer has stated still does not answer your question, there is really one thing you can do: Go to the MySQL Forums and post this question to the Developers. Since Konstantin Osipov was the developer who was responding to the Bug List and refers to this source code (bugs.mysql.com/bug.php?id=24761) that uses the expression as anonymous on in the general_log_print function, he would have to know the correct context and reason for using the expression as anonymous on.
UPDATE 2018-08-12 19:46 EDT
In ight of your conjecture, I went Googling around and found sample output from a general log:
131021 17:43:50    43 Connect root@localhost as anonymous on pnet_blog
       43 Init DB pnet_blog
       43 Query SELECT count(id) as total_posts FROM posts WHERE date_published is not null AND date_published <= '20131021144350'
       43 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0,10
       44 Connect root@localhost as anonymous on pnet_blog
       44 Query SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0, 10
       44 Quit
       43 Quit
131021 17:44:28    45 Connect root@localhost as anonymous on pnet_blog
       45 Init DB pnet_blog
       45 Query SELECT * FROM posts WHERE url='how-and-when-to-enable-mysql-logs'
       45 Query UPDATE posts SET impressions=impressions+1 WHERE id='41'
       45 Query SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published < '20131020150000' ORDER BY date_published DESC LIMIT 0,1
       45 Query SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published > '20131020150000' ORDER BY date_published ASC LIMIT 0,1
       45 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144428' AND date_published >= '20130421144428' ORDER BY impressions DESC LIMIT 0,10
       46 Connect root@localhost as anonymous on pnet_blog
       46 Query SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144428' ORDER BY date_published DESC LIMIT 0, 10
       46 Quit
       45 Quit

Please look at both timestamp entries from the sample log:
131021 17:43:50    43 Connect root@localhost as anonymous on pnet_blog
131021 17:44:28    45 Connect root@localhost as anonymous on pnet_blog

as well as the original log entry from your question:
141017  8:57:31     4 Connect   root@localhost as anonymous on

At these points in time, the user is not authenticated even though  root@localhost appears. The comment for the code does support your conjecture. You now have the answer directly from the source code's author.
Thank you for accepting my posted answer (which lead to you finding the real answer)


You should do 2 things for efficiently deleting the data from your big table :

Delete in batches and/or do careful batching
Watch out your Transaction log size - if db in simple recovery mode - perform frequent CHECKPOINTS or if db is full recovery mode - perform frequent log backups.


SQL Server Log Shipping: Copy copies 0 files

The cause is not clear, but the solution is: get full connectivity between primary and 2ndary and use the sql server wiz to set up log shipping

that works.

Update:
I should add that this solution stinks. The log shipping wizard should be more flexible, and allow me to set up the pieces myself. (E.g. I may use some other method to move the log files, etc.) Given how easy it is to set up an RO mirror of Redis, Mongodb, etc, SQL should be much better than it is now.


Large Identifier Uniqueness Across Table

Perhaps look into Jimmy Nilsson's COMBs? Discussed here: https://stackoverflow.com/questions/1155454/performance-value-of-comb-guids
Or, if you don't need uniqueness over several SQL Server instances, just some int-type with a sequence or identity? (As suggested by Mo64.)
The fact that NEWSEQUENTIALID() is "re-seeded" at re-start isn't anything we can prevent, AFAIK.


What's the difference between a temp table and table variable in SQL Server?

Contents

Caveat
This answer discusses "classic" table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discussed below! (more details).
Storage Location
No difference. Both are stored in tempdb.
I've seen it suggested that for table variables this is not always the case but this can be verified from the below
DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Example Results (showing location in tempdb the 2 rows are stored)
File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Logical Location
@table_variables behave more as though they were part of the current database than #temp tables do. For table variables (since 2005) column collations if not specified explicitly will be that of the current database whereas for #temp tables it will use the default collation of tempdb (More details). Additionally User-defined data types and XML collections must be in tempdb to use for #temp tables but table variables can use them from the current database (Source).
SQL Server 2012 introduces contained databases. the behavior of temporary tables in these differs (h/t Aaron)

In a contained database temporary table data is collated in the collation of the contained database.

All metadata associated with temporary tables (for example, table and column names, indexes, and so on) will be in the catalog collation.
Named constraints may not be used in temporary tables.
Temporary tables may not refer to user-defined types, XML schema collections, or user-defined functions.

Visibility to different scopes
@table_variables can only be accessed within the batch and scope in which they are declared. #temp_tables are accessible within child batches (nested triggers, procedure, exec calls). #temp_tables created at the outer scope (@@NESTLEVEL=0) can span batches too as they persist until the session ends. Neither type of object can be created in a child batch and accessed in the calling scope however as discussed next (global ##temp tables can be though).
Lifetime
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
Although the parser will not allow you to try and use the table variable before the DECLARE statement the implicit creation can be seen below.
IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends (if created in a child batch with @@NESTLEVEL > 0) or when the session ends otherwise.
NB: Within stored routines both types of object can be cached rather than repeatedly creating and dropping new tables. There are restrictions on when this caching can occur however that are possible to violate for #temp_tables but which the restrictions on @table_variables prevent anyway. The maintenance overhead for cached #temp tables is slightly greater than for table variables as illustrated here.
Object Metadata
This is essentially the same for both types of object. It is stored in the system base tables in tempdb. It is more straightforward to see for a #temp table however as  OBJECT_ID('tempdb..#T') can be used to key into the system tables and the internally generated name is more closely correlated with the name defined in the CREATE TABLE statement. For table variables the object_id function does not work and the internal name is entirely system generated with no relationship to the variable name. The below demonstrates the metadata is still there however by keying in on a (hopefully unique) column name. For tables without unique column names the object_id can be determined using DBCC PAGE as long as they are not empty.
/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B)
    WITH (FILLFACTOR = 80,
         IGNORE_DUP_KEY = ON,
         DATA_COMPRESSION = PAGE,
         ALLOW_ROW_LOCKS=ON,
         ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM   tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
         ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
         ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
         ON c.object_id = p.object_id
WHERE  c.name = 'dba.se'

Output
Duplicate key was ignored.

object_id
name
rows
type_desc
total_pages
used_pages
data_pages
data_compression_desc

574625090
#22401542
13
IN_ROW_DATA
2
2
1
PAGE

574625090
#22401542
13
LOB_DATA
24
19
0
PAGE

574625090
#22401542
13
ROW_OVERFLOW_DATA
16
14
0
PAGE

574625090
#22401542
13
IN_ROW_DATA
2
2
1
NONE

Transactions
Operations on @table_variables are carried out as system transactions, independent of any outer user transaction, whereas the equivalent #temp table operations would be carried out as part of the user transaction itself. For this reason a ROLLBACK command will affect a #temp table but leave the @table_variable untouched.
DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

Logging
Both generate log records to the tempdb transaction log. A common misconception is that this is not the case for table variables so a script demonstrating this is below, it declares a table variable, adds a couple of rows then updates them and deletes them.
Because the table variable is created and dropped implicitly at the start and the end of the batch it is necessary to use multiple batches in order to see the full logging.
USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141),
       (2, 0x41414141414141414141)

UPDATE @T
SET    B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id
FROM   sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
         ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
         ON c.object_id = p.object_id
WHERE  ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM   fn_dblog(NULL, NULL)
WHERE  AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
     AS (SELECT Operation,
                Context,
                CASE
                  WHEN AllocUnitId = @allocId THEN 'Table Variable'
                  WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
                  ELSE AllocUnitName
                END AS AllocUnitName,
                [Log Record Length]
         FROM   fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
                     WHEN GROUPING(Operation) = 1 THEN 'Total'
                     ELSE Operation
                   END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM   T
GROUP  BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER  BY GROUPING(Operation),
          AllocUnitName

Returns
###Detailed view

###Summary View (includes logging for implicit drop and system base tables)

As far as I've been able to discern operations on both generate roughly equal amounts of logging.
Whilst the quantity of logging is very similar one important difference is that log records related to #temp tables can not be cleared out until any containing user transaction finishes so a long running transaction that at some point writes to #temp tables will prevent log truncation in tempdb whereas the autonomous transactions spawned for table variables do not.
Table variables do not support TRUNCATE so can be at a logging disadvantage when the requirement is to remove all rows from a table (though for very small tables DELETE can work out better anyway)
Cardinality
Many of the execution plans involving table variables will show a single row estimated as the output from them. Inspecting the table variable properties shows that SQL Server believes the table variable has zero rows (Why it estimates 1 row will be emitted from a zero row table is explained by @Paul White here).
However the results shown in the previous section do show an accurate rows count in sys.partitions. The issue is that on most occasions the statements referencing table variables are compiled while the table is empty. If the statement is (re)compiled after @table_variable is populated then this will be used for the table cardinality instead (This might happen due to an explicit recompile or perhaps because the statement also references another object that causes a deferred compile or a recompile.)
DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

Plan shows accurate estimated row count following deferred compile.

In SQL Server 2012 SP2, trace flag 2453 is introduced. More details are under "Relational Engine" here.
When this trace flag is enabled it can cause automatic recompiles to take account of changed cardinality as discussed further very shortly.
NB: On Azure in compatibility level 150 compilation of the statement is now deferred until first execution. This means that it will no longer be subject to the zero row estimate problem.
No column statistics
Having a more accurate table cardinality doesn't mean the estimated row count will be any more accurate however (unless doing an operation on all rows in the table). SQL Server does not maintain column statistics for table variables at all so will fall back on guesses based upon the comparison predicate (e.g. that 10% of the table will be returned for an = against a non unique column or 30% for a > comparison). In contrast column statistics are maintained for #temp tables.
SQL Server maintains a count of the number of modifications made to each column. If the number of modifications since the plan was compiled exceeds the recompilation threshold (RT) then the plan will be recompiled and statistics updated. The RT depends on table type and size.
From Plan Caching in SQL Server 2008

RT is calculated as follows. (n refers to a table's cardinality when a query plan is compiled.)

Permanent table

If n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.

Temporary table

If n < 6, RT = 6.
If 6 <= n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.
Table variable
RT does not exist. Therefore, recompilations do not happen because of changes in  cardinalities of table variables.
(But see note about TF 2453 below)

the KEEP PLAN hint can be used to set the RT for #temp tables the same as for permanent tables.
The net effect of all this is that often the execution plans generated for #temp tables are orders of magnitudes better than for @table_variables when many rows are involved as SQL Server has better information to work with.
NB1: Table variables do not have statistics but can still incur a "Statistics Changed" recompile event under trace flag 2453 (does not apply for "trivial" plans) This appears to occur under the same recompile thresholds as shown for temp tables above with an additional one that if N=0 -> RT = 1. i.e. all statements compiled when the table variable is empty will end up getting a recompile and corrected TableCardinality the first time they are executed when non empty. The compile time table cardinality is stored in the plan and if the statement is executed again with the same cardinality (either due to flow of control statements or reuse of a cached plan) no recompile occurs.
NB2: For cached temporary tables in stored procedures the recompilation story is much more complicated than described above. See Temporary Tables in Stored Procedures for all the gory details.
Recompiles
As well as the modification based recompiles described above #temp tables can also be associated with additional compiles simply because they allow operations that are prohibited for table variables that trigger a compile (e.g. DDL changes CREATE INDEX, ALTER TABLE)
Locking
It has been stated that table variables do not participate in locking. This is not the case. Running the below outputs to the SSMS messages tab the details of locks taken and released for an insert statement.
DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

For queries that SELECT from table variables Paul White points out in the comments that these automatically come with an implicit NOLOCK hint. This is shown below
DECLARE @T TABLE(X INT);

SELECT X
FROM @T
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

###Output
*** Output Tree: (trivial plan) ***

        PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002  Hints( NOLOCK )

The impact of this on locking might be quite minor however.
SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

Neither of these return results in index key order indicating that SQL Server used an allocation ordered scan for both.
I ran the above script twice and the results for the second run are below
Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0  (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0  (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0  (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0

The locking output for the table variable is indeed extremely minimal as SQL Server just acquires a schema stability lock on the object. But for a #temp table it is nearly as light in that it takes out an object level S lock. A NOLOCK hint or READ UNCOMMITTED isolation level can of course be specified explicitly when working with #temp tables as well.
Similarly to the issue with logging a surrounding user transaction can mean that the locks are held longer for #temp tables. With the script below
    --BEGIN TRAN;

    CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

    INSERT INTO #T (X) VALUES(1)

    SELECT CASE resource_type
             WHEN  'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
             WHEN  'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                                           FROM  tempdb.sys.allocation_units a
                                           JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                                           WHERE  a.allocation_unit_id = resource_associated_entity_id)
             WHEN 'DATABASE' THEN DB_NAME(resource_database_id)
             ELSE (SELECT OBJECT_NAME(object_id, 2)
                   FROM   tempdb.sys.partitions
                   WHERE  partition_id = resource_associated_entity_id)
           END AS object_name,
           *
    FROM   sys.dm_tran_locks
    WHERE  request_session_id = @@SPID

    DROP TABLE #T

   -- ROLLBACK

when run outside of an explicit user transaction for both cases the only lock returned when checking sys.dm_tran_locks is a shared lock on the DATABASE.
On uncommenting the BEGIN TRAN ... ROLLBACK 26 rows are returned showing that locks are held both on the object itself and on system table rows to allow for rollback and prevent other transactions from reading uncommitted data. The equivalent table variable operation is not subject to rollback with the user transaction and has no need to hold these locks for us to check in the next statement but tracing locks acquired and released in Profiler or using trace flag 1200 shows plenty of locking events do still occur.
Indexes
For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key. This does of course mean that only unique indexes are supported. A non unique non clustered index on a table with a unique clustered index can be simulated however by simply declaring it UNIQUE NONCLUSTERED and adding the CI key to the end of the desired NCI key (SQL Server would do this behind the scenes anyway even if a non unique NCI could be specified)
As demonstrated earlier various index_options can be specified in the constraint declaration including DATA_COMPRESSION, IGNORE_DUP_KEY, and FILLFACTOR (though there is no point in setting that one as it would only make any difference on index rebuild and you can't rebuild indexes on table variables!)
Additionally table variables do not support INCLUDEd columns, filtered indexes (until 2016) or partitioning, #temp tables do (the partition scheme must be created in tempdb).
Indexes in SQL Server 2014
Non unique indexes can be declared inline in the table variable definition in SQL Server 2014. Example syntax for this is below.
DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

Indexes in SQL Server 2016
From CTP 3.1 it is now possible to declare filtered indexes for table variables. By RTM it may be the case that included columns are also allowed albeit they will likely not make it into SQL16 due to resource constraints
DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

Parallelism
Queries that insert into (or otherwise modify) @table_variables cannot have a parallel plan, #temp_tables are not restricted in this manner.
There is an apparent workaround in that rewriting as follows does allow the SELECT part to take place in parallel but that ends up using a hidden temporary table (behind the scenes)
INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')

There is no such limitation in queries that select from table variables as illustrated in my answer here
Other Functional Differences

#temp_tables cannot be used inside a function. @table_variables can be used inside scalar or multi-statement table UDFs.
@table_variables cannot have named constraints.
@table_variables cannot be SELECT-ed INTO, ALTER-ed, TRUNCATEd or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT and do not support table hints such as WITH (FORCESCAN)
CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Table variables don't seem to qualify for the rowset sharing optimisation meaning that delete and update plans against these can encounter more overhead and PAGELATCH_EX waits. (Example)

Memory Only?
As stated at the beginning both get stored on pages in tempdb. However I didn't address whether there was any difference in behaviour when it comes to writing these pages to disc.
I've done a small amount of testing on this now and so far have seen no such difference. In the specific test I did on my instance of SQL Server 250 pages seems to be the cut off point before the data file gets written to.

NB: The behavior below no longer occurs in SQL Server 2014 or SQL
Server 2012 SP1/CU10 or SP2/CU1 the eager writer is no longer as
eager to write pages to disc. More details on that change at SQL
Server 2014: tempdb Hidden Performance Gem.

Running the below script
CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

And monitoring writes to the tempdb data file with Process Monitor I saw none (except occasionally ones to the database boot page at offset 73,728). After changing 250 to 251I began to see writes as below.

The screenshot above shows 5 * 32 page writes and one single page write indicating that 161 of the pages were written to disc. I got the same cut off point of 250 pages when testing with table variables too. The script below shows it a different way by looking at sys.dm_os_buffer_descriptors
DECLARE @T TABLE (
  X        INT,
  [dba.se] CHAR(8000) NULL)

INSERT INTO @T
            (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM   master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                                 FROM   tempdb.sys.partitions AS p
                               INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                                          ON p.hobt_id = a.container_id
                                        INNER JOIN tempdb.sys.columns AS c
                                          ON c.object_id = p.object_id
                                 WHERE  c.name = 'dba.se')
GROUP  BY is_modified

###Results
is_modified page_count
----------- -----------
0           192
1           61

Showing that 192 pages were written to disc and the dirty flag cleared. It also shows that being written to disc doesn't mean that pages will be evicted from the buffer pool immediately. The queries against this table variable could still be satisfied entirely from memory.
On an idle server with max server memory set to 2000 MB and DBCC MEMORYSTATUS reporting Buffer Pool Pages Allocated as approx 1,843,000 KB (c. 23,000 pages) I inserted to the tables above in batches of 1,000 rows/pages and for each iteration recorded.
SELECT Count(*)
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE'

Both the table variable and the #temp table gave nearly identical graphs and managed to pretty much max out the buffer pool before getting to the point that they weren't entirely held in memory so there doesn't seem to be any particular limitation on how much memory either can consume.


I don't think you have a problem with the relationships.  I think instead the problem is that by using surrogate keys (ie Ids) for each table the resulting database is unable to prevent Workers from being inserted whose Department is of one Company while the Classification is of another and vice versa.  A good way to understand this is to visualize the schema using an ER Diagramming tool.  I will use the Oracle Data Modeler tool which is a free download.
ER Diagram

As it stands, you could have 2 companies - say IBM and Microsoft.  IBM can have a Software Development department, and Microsoft can have a Desktop Software department.  IBM can have a Software Engineer classification, and Microsoft can have a Software Developer classification.  Now, because you have a surrogate key for Department and Classification, the fact that Software Development is an IBM department and Desktop Software is a Microsoft department is lost for future child relationships.  This is also the case with Classification.  Therefore it is easy to accidentally assign Harlan Mills, who is an IBM employee in the Software Development department, a classification of Software Developer which is a Microsoft classification!  Likewise, the worker could be given the right classification and wrong department!  Here is a diagram showing the first example:

The 1 Ids represent IBM, and the 2 Ids represent Microsoft.  I've highlighted in red the scenario where Harlan Mills and Bill Gates are assigned to the wrong departments, which is visualized by the 10 department Id associated to the 200 classification Id and vice versa.
Options to Resolve
So what are the options to prevent his from happening?  There are two immediate options.  The first is to realize that by using a surrogate key for every table this issue exists and introduce additional programming to verify it doesn't occur.  This could be done in the application, but if inserts and updates can occur outside of the application then incorrect associations can still occur.  A better approach would be to create a trigger that fires on insert and update of an employee to make sure that the assigned department is of the same company as the assigned classification, and if not fail the insert or update.
The second option is to not use surrogate keys for every table.  Instead, use the surrogate keys only for the Company table, which is fundamental and has no parents, and then create identifying relationships to the Department and Classification child tables.  The Department and Classification tables now have a PK of the Company Id plus a Sequence Number or Name to distinguish them.  Then, the relationships from Department and Classification to Worker also become identifying and thus the PK of Worker becomes the Company Id, plus the Department Number (I'm using a sequence number in this example), plus the Classification Number.  The result is there is only one Company Id in the Worker table.  It is now impossible to assign a Worker to a Department in one Company and to a Classification in another Company.
Why is this impossible?  It is impossible because the schema implements referential integrity between Worker and Department and Classification.  If an attempt is made to insert a Worker for a Department in one Company and and a Classification of another, the combination that does not exist in the corresponding parent table will trigger a referential integrity violation and the insert will not work.
Here is an updated diagram of an implementation of the second option:

Preferred Option
Of the two options, I absolutely prefer the second - using the identifying relationships and the cascading keys - for two reasons.  First, this option achieves the desired rule with no additional programming.  Developing a trigger is not trivial.  It must be coded, tested, and maintained.  Ensuring the trigger logic is optimal so as not to impact performance is also not trivial.  The book Applied Mathematics for Database Professionals provides a lot of detail on the complexity of such a solution.  Second, the rules imply that a Department and a Classification can not exist outside the context of the Company, and so the schema now more accurately reflects the real world.
This is a great question because it shows exactly why simply assuming every table requires a surrogate key is a bad idea.  Fabian Pascal has an excellent blog post on just this topic showing that not only can a surrogate key be a bad idea from a data integrity standpoint it can also result in making some retrievals slower at the physical level precisely because joins are required that, had keys been properly cascaded, would be unnecessary.  Another interesting topic this question reveals is that a database cannot ensure that all data inserted into it is accurate with respect to the real world.  Instead, it can only ensure that the data inserted into it is consistent with the rules declared to it.  In this case we can do the best possible by using the cascading key approach to ensure the DBMS can keep the data consistent with respect to the rule that a Worker of a given Company needs to be assigned a Classification and a Department of that same Company.  But, if in the real world Microsoft has a department called Desktop Software but the user of the database asserts the department is instead Software Development the DBMS can do nothing but assume it has been given a true fact.


SQL Server 2012 Failed to launch local ScenarioEngine.exe

(Community Wiki answer from comments provided by Jon Seigel.)

Try putting the install files in a location that is not on your desktop. It seems there's an issue resolving the base path to that executable


Postgres 12.1 uses Index Only Scan Backward instead of index when LIMIT is present

PostgreSQL estimates that there will be 1557 rows that satisfy the condition, so it thinks that it will be faster if it avoids an explicit sort and rather scans the rows in ORDER BY order and does nested loop joins until it has found enough rows.

Unfortunately, that doesn't work out at all, and PostgreSQL has to scan the whole table that way, since there are only 8 matches total.

The problem seems to be that the estimates are quite off: it thinks that the index scan on public."functionCall" for function will produce 5897 rows, when really there are only 8.

As a first measure, try to calculate new distribution statistics on the table:

ANALYZE public."functionCall";

If that alone does not improve the estimate, increase the granularity:

ALTER TABLE public."functionCall" ALTER function SET STATISTICS 1000;
ANALYZE public."functionCall";

That should improve the estimate. You can try with higher values than 1000 too.

If all fails, tell PostgreSQL explicitly not to use that strategy by using

ORDER BY c.input + 0 DESC


How to create a summary report that contains other reports?

You can do this.  You can create a single report that has multiple data sources (one per environment).  Add the data sets required.  Recreate the graphs (you may be able to leverage some cut-and-paste).  In order to format them nicely, you will need to place the graphs into the cells of a matrix.

You could also build a single report that links to other reports.

I realize this is a high-level description of how to do it - if you need further details, let me know.


Switching between databases with dynamic SQL

Sure, there is a way - there's always a way...

If you declare variable and store in it the database and the procedure to run, you can exec it, with parameters.

Example

use tempdb;

select db_name();

declare @db sysname = 'master.sys.sp_executesql';

exec @db N'select db_name()';

set @db = 'msdb.sys.sp_executesql';

exec @db N'select db_name()';

It is trivial to then pass a query with parameters to be run in any database

declare @proc sysname, @sql nvarchar(max), @params nvarchar(max);

select
  @proc = 'ssc.sys.sp_executesql'
, @sql = N'select top 10 name from sys.tables where name like @table order by name;'
, @params = N'@table sysname';

exec @proc @sql, @params, @table = 'Tally%'

I know this doesn't change database context in the main query, but wanted to demonstrate how you can conveniently work in another database in a safe parameterised way without too much bother.


The SQL Server parser optimizer has a feature called "Constant Folding" that eliminates tautological expressions from the query.
If you look at the execution plan, nowhere in the predicates you will see that expression appear. This implies that constant folding is performed anyway at compile time for this and other reasons and it has no effect on query performance.

See Constant Folding and Expression Evaluation During Cardinality Estimation for more info.


If you add more information on how your schema is structured then we can provide a more specific answer. It's likely you'll want to JOIN your data together into one dataset to consume in your SSRS. Though depending on how your Tables are structured, you may want to UNION it too.
If you're using Microsoft SQL Server, you can use a View, or even better a Stored Procedure (since you'll be filtering and parameters will be helpful here).
Then you need to setup a new SSRS and configure how the filters for your stored procedure's parameters are shown in the report.
Finally you can add a Pie Chart via this Microsoft Tutorial: Add a Pie Chart to Your Report (Report Builder).


Join multiple tables without duplicated results and without UNION in PostgreSQL

One neat option to avoid querying the tables twice is to unpivot A first.
You need some CASE expressions because you also want to split out element_name back into different columns.

Not so good at Postgres, I may have the syntax slightly off

SELECT
    A.title,
    CASE WHEN A.first_group_id  = v.group_id THEN B.element_name END AS element_name0,
    CASE WHEN A.second_group_id = v.group_id THEN B.element_name END AS element_name1
FROM A
INNER JOIN LATERAL (VALUES
    (A.first_group_id),
    (A.second_group_id)
) AS v(group_id)
LEFT JOIN B ON v.group_id = B.group_id;


Oracle 12c introduced TRUNCATE ... CASCADE:

TRUNCATE TABLE

  CASCADE

  If you specify CASCADE, then Oracle Database truncates all child
  tables that reference table with an enabled ON DELETE CASCADE
  referential constraint. This is a recursive operation that will
  truncate all child tables, granchild tables, and so on, using the
  specified options.

Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.

In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.


SQL Log Messages - FILESTREAM and xp_cmdshell

Really not sure what else aside from PBM might be set up to try so forcibly to disable xp_cmdshell, but it definitely sounds like something set up by IT in some way. I don't think this is really causing any problems, so you could just wait until the rest of your team is available and bring it up to them. If you want to get more information about it, you could set up a server-side trace that captures all calls to sp_configure - this will at least tell you the host name, application name, user name etc.

declare @rc int, @TraceID int, @maxsize BIGINT = 5;

exec @rc = sp_trace_create @TraceID output, 0, N'C:\myfolder\mytrace', @maxsize, NULL
if (@rc != 0) goto error

declare @on bit = 1;
exec sp_trace_setevent @TraceID, 42, 1, @on;
exec sp_trace_setevent @TraceID, 42, 6, @on;
exec sp_trace_setevent @TraceID, 42, 7, @on;
exec sp_trace_setevent @TraceID, 42, 8, @on;
exec sp_trace_setevent @TraceID, 42, 10, @on;
exec sp_trace_setevent @TraceID, 42, 11, @on;
exec sp_trace_setevent @TraceID, 42, 14, @on;
exec sp_trace_setevent @TraceID, 42, 34, @on;
exec sp_trace_setevent @TraceID, 42, 41, @on;
exec sp_trace_setevent @TraceID, 42, 64, @on;

exec sp_trace_setfilter @TraceID, 34, 0, 6, N'sp_configure';
exec sp_trace_setstatus @TraceID, 1;
select TraceID=@TraceID;
goto finish

error:
select ErrorCode=@rc

finish:
go

Probably more event columns there than necessary, but it's not like you're going to be running this for a long time. You should be able to turn it off as soon as you see another message pop up in the log (because I doubt this is coming from multiple sources).


Running a command in psql `-c` that requires identifier quoting

It is very poor practice to misuse reserved words. Frankly, messing with multiple periods ., the table/column separator operator, you deserve any hardships coming to your way. There is no need for funky table names, barring some odd edge cases. The DB should be transparent to applications anyway, so stick to sensible naming.

Escape the inner quotes. As how to do that, depends on your shell. The error message looks like *NIX shell, so try backslash \". Like so:

"select * from \"mytest.us\".\"a.b.c\"";


Which view contains all Oracle's catalog views?

DICTIONARY

DICTIONARY contains descriptions of data dictionary tables and views.


You have something seriously wrong going inside the SQL Server and that is why it is producing so many dumps and if you look closely dumps file SQLDumpxxx are getting dumped every minute of so this may be due to bug in SQL Server or sql server facing some exception because of queries running. I would start by looking whether the SQL Server is patched to latest SP and CU or not. If you run select @@version and add the output in the question I can tell you the SP level. You can also find SP/CU information about SQL Server 2017 on SQLServerbuild.blogspot.com.

Similar question discussed on This Thread

MS engineers are the best person to analyze these dumps and tell you what is causing this but before opening case with MS please make sure the SQL Server instance is patched to latest SP and CU so that you rule out all the possibility of bug. If you go to them without patched SQL Server they are most likely to ask you to update to latest SP.

Some links which show how to analyze dumps

SQL Server Diagnostics (Preview)

Looking deeper into SQL Server using Minidumps

Steps to Catch a Simple “Crash Dump” of a Crashing Process

Extracting Information from Crash and Hang Dumps

The first 2 links should be good if you want to try it yourself but my suggestion would be to make sure latest SP and CU is there and if that does not solves the issue raise case with MS.

  Microsoft SQL Server 2017 (RTM-GDR) (KB4494351) - 14.0.2014.14 (X64)

You can see the version is RTM you need to apply SQL Server 2017 CU15 and see if that resolves the issue


What TSM guy is doing is taking File level backup of mdf and ldf file which does not guarantee point in time recovery. You are correct to say that he should also be backing up folder(s) which holds transaction log backup or other similar backups i.e I:/Log_Backup.
In case of disaster you would always want zero data loss or as much less data loss as possible. If you follow what TSM guy is doing you would be only able to attach database to point when it was Snapshot backed up. But with full backup and log backup you could do point in time restore(depending on level of disaster/corruption) and that would give you no or minimum data loss.

What you can do is you can ask TSM guys to also backup the folder holding full and transaction log backups.


Using exception with raise_application_error

There are two ways to raise exceptions in Oracle.  If you want to specify your own message, you wouldn't declare a local variable of type exception.  You'd simply put the error code in your raise_application_error call

BEGIN
  raise_applicaton_error( -20001, 'Some message' );
END;

If you want to declare a local exception type, then you wouldn't use raise_application_error to throw it.  You'd just use raise.  But then you won't be able to specify a custom message.

DECLARE
  my_fault EXCEPTION;
  PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
  RAISE my_fault;
END;

If you just don't like hard-coding the error code in your raise_application_error call, you can put that in a local integer variable

declare
  myfault_code integer := -20001;
begin
  raise_application_error( myfault_code, 'Some error' );
end;


How do I find out who is the resource hog in SQL Server 2008?

One way to do this would be through Resource Governor.  You can set up the appropriate Resource Pools and Workload Groups for each vendor/database.  Then you would create a classifier function (basically just a UDF that's registered in Resource Governor as the classifier function) to determine which Workload Group the incoming request would belong to.

To make this easy you could create Windows Groups (one for each vendor/db) and base your classifier function off of this, and set the appropriate workload.  These workloads can be configured according to your specifications above.


SQL Server: Can I log ship from SQL Server 2014 --> 2016?

You can always log ship UP as it's a basic supported upgrade process.  you can't log ship DOWN.  The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014.  You will need to setup a process to get the data in sync such as replication, red gate data compare, etc.


SQL Server replication, creates jobs which runs through SQL Server Agent, but because SQL Server Agent doesn't run with SQL Server Express Edition, Replication also can not run and the feature is not added in the edition and you can not implement replication. So if you need to sync both databases you need to upgrade your database to standard edition or developer edition, write lengthy query and schedule using PowerShell, or use some third party SW.

Thanks


represent multiple entities to one entity relationship

Don't use an attribute value to indicate what entity a person owns. Instead, take advantage of the relational nature of the database in your queries. Remove the owns column from person and find related entities by joining the tables:

SELECT
p.id AS person_id, p.first_name, p.last_name,
h.id AS home_id, h.no_of_rooms, h.area, h.city,
c.id AS car_id, c.make, c.model, c.color
FROM person p
LEFT JOIN home h
ON p.id = h.person_id
LEFT JOIN car c
ON p.id c.person_id
WHERE p.id = 1

A LEFT (OUTER) JOIN will select the desired person whether it owns any entities or not. You can then use logic in your application to detect which owned entities have values and which are NULL. Use an INNER JOIN to only select the person if it also owns one of the entities.


What difference does it make if my COLLATION_CONNECTION is utf8mb4_0900_as_cs or utf8mb4_0900_ai_ci

Changing the collation of the connection will have little to no impact on performance, but also on what you probably want to achieve. From the manual:

  collation_connection is important for comparisons of literal strings.
  For comparisons of strings with column values, collation_connection
  does not matter because columns have their own collation, which has a
  higher collation precedence

If you want to make comparisons in with or without case sensitivity/accent sensitivity, you will have to update the actual column's collation, or force the collation on every comparison manually (e.g. WHERE ). The latter may have low impact if no index is required, or a huge impact if the usage of an index is prevented because the comparison and the column use different collation.

In practice:

mysql> SET NAMES utf8mb4; -- we will be using this charset to send literals
mysql> SELECT 'A' = 'a';
+-----------+
| 'A' = 'a' |
+-----------+
|         1 |
+-----------+
1 row in set (0.000 sec)

mysql> SET SESSION collation_connection = 'utf8mb4_bin';
Query OK, 0 rows affected (0.000 sec)

mysql> SELECT 'A' = 'a'; -- it works on literals, but you will have to force it manually on columns
+-----------+
| 'A' = 'a' |
+-----------+
|         0 |
+-----------+
1 row in set (0.000 sec)

mysql> MariaDB [(none)]> SELECT 'A' = 'a' COLLATE utf8mb4_bin; -- may prevent index usage on columns
+-------------------------------+
| 'A' = 'a' COLLATE utf8mb4_bin |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.000 sec)

mysql> select 'A' = 'a' COLLATE utf8mb4_unicode_520_ci;  -- may prevent index usage on columns
+------------------------------------------+
| 'A' = 'a' COLLATE utf8mb4_unicode_520_ci |
+------------------------------------------+
|                                        1 |
+------------------------------------------+
1 row in set (0.004 sec)


Oracle DB: Can one db file be shared between table space

From the Oracle 11g Documentation:

  A datafile can be associated with only one tablespace and only one
  database.

So you don't have to worry about losing data from another tablespace.


Are there design patterns/best practices in writing dynamic SQL in stored procedures?

Best is to read The Curse and Blessings of Dynamic SQL by  Erland Sommarskog.

Also read Using Dynamic SQL in Stored Procedures and Stored Procedures - Executing & Dynamic SQL


Map-Reduce on `system.profile` collection

So it seems special readWrite privileges are needed to execute mapReduce on system.profile collection. So I got the query to work eventually after checking through:
https://docs.mongodb.com/manual/tutorial/manage-users-and-roles/


Most cost efficient way to page through a poorly ordered table?

Essentially, you are asking if you can perform a single ordered scan through the data overall, while making no copies of the data, and returning 'x' disjoint sets of rows from the full set on each call. This is exactly the behaviour of an appropriately-configured API cursor.

For example, using the AdventureWorks table Person.EmailAddress to return sets of 1,000 rows:

DECLARE
    @cur integer,
    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
    @scrollopt integer = 16 | 8192 | 16384,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt integer = 1 | 32768 | 65536,
    @rowcount integer = 1000,
    @rc integer;

-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT *
    FROM AdventureWorks2012.Person.EmailAddress
        WITH (INDEX([IX_EmailAddress_EmailAddress]))
    ORDER BY EmailAddress;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption
        @cur,
        2,
        'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch
            @cur,
            2,
            0,
            1000;
    END;
END;

Each fetch operation returns a maximum of 1,000 rows, remembering the position of the scan from the previous call.


Optimizing PostgreSQL hstore for lots of small updates

With lots of UPDATEs, each one, no matter how small, will cause the entire row to be rewritten into a new version, as a consequence of the MVCC mechanism.
Then the old version of the row will be picked up at some point by autovacuum when it's certain that no transaction may need it, and its space will be flagged as reusable .
The constant turnover of disk space for old and new rows leads to fragmentation, especially if the rows are large in size.
Additionally, the whole set of operations are logged into the WAL files (unless the table is unlogged).

For these reasons, high frequency UPDATEs of large columns is a worst case scenario for postgres.

So, if that session data doesn't really require durable storage in the first place, a mixed disk-memory specialized key/store engine like Redis is likely to perform way better.

Otherwise, this other question: What fillfactor for caching table? has good information and advice on how to mitigate the difficulties.


Trace to find "error messages"( SQL Server 2008 R2 )

Have you looked into SQL Server's Ring Buffer, especially the Exception Ring Buffer?

From SQL Server Ring Buffers and The Fellowship of the Ring by Sudarshan Narasimhan, I got the following query which works great for detecting uncaught exceptions and errors:

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
    dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
    cast(record as xml).value('(//Exception//Error)[1]', 'varchar(255)') as [Error],
    cast(record as xml).value('(//Exception/Severity)[1]', 'varchar(255)') as [Severity],
    cast(record as xml).value('(//Exception/State)[1]', 'varchar(255)') as [State],
    msg.description,
    cast(record as xml).value('(//Exception/UserDefined)[1]', 'int') AS [isUserDefinedError],
    cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
    cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
    cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
    tme.ms_ticks as [Current Time]
from sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
cross join sys.sysmessages msg
where rbf.ring_buffer_type = 'RING_BUFFER_EXCEPTION' --and cast(record as xml).value('(//SPID)[1]', 'int') <> 0--in (122,90,161,179)
and msg.error = cast(record as xml).value('(//Exception//Error)[1]', 'varchar(500)') and msg.msglangid = 1033 --and [Error] = 4002
ORDER BY rbf.timestamp ASC


Atrocious performance joining INSERTED and DELETED tables in a trigger

You could evaluate using explicit INNER MERGE JOIN or INNER HASH JOIN hints but given that you are presumably using these tables again later in the trigger you are probably better off just inserting the contents of inserted and deleted tables into indexed #temp tables and being done with it.

They do not get useful indexes created for them automatically.


Provided that you have alerts set up on the jobs, Log Shipping is a feature that is available on all editions, doesn't require a whole lot of effort to set up, and I believe fulfills all of your requirements.

I would avoid any wizards that you might stumble upon in Management Studio. Essentially you need three jobs for the following tasks:

Initialize the secondaries (restore the latest full backup with NORECOVERY)
Take full and log backups
Restore log backups with STANDBY, NORECOVERY

Essentially, testing the restores of your full and database backups will be verifying them (and you can add other tasks to this process, such as bringing a restored database into recovery and checking data, running CHECKDB, etc)

I go into great detail about how I have implemented log shipping (different purpose, but generally the same approach):

Readable Secondaries on a Budget


Assign foreign keys only after populating database

Option 1:
Use COPY from a valid dump of the tables in order to avoid any foreign key conflict (Copy is the fastest way i've found to load bulk data)

Option 2:
Create the schema without foreign keys, load the dumps, and recreate the foreign keys after that:
ALTER TABLE table2 DROP CONSTRAINT fkey;
ALTER TABLE table2 ADD CONSTRAINT  fkey; FOREIGN KEY (column_name) REFERENCES table1 (column_name);

Option 3: If you are in a production environment you can try a replication solution, check out how to create a stream replica from mongo to postgres.

Option 4: If you are just trying to migrate some subset or data and keep it sync you can use a connector.

For sure people here can help you with other approaches.


Need to use one and one only of multiple parameters in a stored procedure

I think something like the following pattern might work:

WHERE
  (PARAMETER1 = DBFIELD1 AND PARAMETER2 IS NULL AND PARAMETER3 IS NULL AND PARAMETER4 IS NULL)
  OR (PARAMETER2 = DBFIELD2 AND PARAMETER1 IS NULL AND PARAMETER3 IS NULL AND PARAMETER4 IS NULL)
  OR ...

That pattern is pretty ugly but it will only give you rows if one parameter is set and the others are null. In order to give you something more I'd need you to edit your question to better explain what you want.


DB2 LUW Query Optimization - IXSCAN timerons greater than total timerons

Just want to tie this off.
As was suggested in the comments, this simply should not be so. It must have been a bug in the level of DB2 I was using.


Turns out mongo cli client needs to be on version 4.4
I had version 3


There is no way to pull changes via SQL. But you can read the binary log (be sure it's enabled) just like MySQL slaves do. So, instead of using JDBC, you'll need to use something like mysql-binlog-connector-java.

The basic idea is the same for all RDBMSs: you act as a slave and receive the changes. A real slave would apply those changes to have an exact copy of master's data, but actually once you get the changes, you can do whatever you want with them.

However each database implements replication in a different way, so there is no universal tool to do this. If you need to get changes from Oracle, you'll need to find a specific library for that purpose.