Disable mysql_tzinfo_to_sql during MariaDB/MySQL installation

Manual is pretty clear: loaded TZs enables a TZ-related functions. Say, with TZs loaded you can transform result's datetimes due to the client's TZ automatically, or calculate FROM_UNIXTIME() with respect to the leap seconds or daysaving times etc.


SELECT Data Set Using Stored Procedure with BCP Error

You have CR\LF in shell invocation, a no-no. Make it a single line:

DECLARE @SQL VARCHAR(8000);
SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" '+
               'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" '+
               '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + '';
EXEC xp_cmdshell @SQL;
GO


What is the best practice for physical deployment of data marts?

It sounds like you're looking at something similar to, if not identical to, a multi-tenant architecture.

In a multi-tenant architecture, all the tenants (clients, customers) share something. They might share only the server and the dbms, with each tenant getting a private database. Or they might share the database, with each tenant getting a private schema. Or they might share tables, where each row in each table carries a tenant identifier.

So there's a pretty broad spectrum of choices. Each one of these can probably be considered a best practice for some applications, and a worst practice for others.

Your architects seem to be thinking "one database per customer," also known as "shared nothing." (That actually means sharing only the dbms, and usually the server.)

  What is the best practice here from a physical deployment and
  maintenance perspective? Or what guidelines are used to define when it
  should be which option?

There isn't really a single best practice, because of the spectrum of possibilities. You want to make deployment and maintenance as easy as possible, but you also have to consider what the tenants are sharing.

Separate, private databases make some kinds of maintenance easy, and it makes other kinds of maintenance hard. For example, when tenants share nothing but the server and dbms,

adding custom columns, tables, and view for a single tenant is easy, but
adding an identical column or table for all tenants is relatively hard.

When tenants share tables

adding custom columns, tables, and views for a single tenant is hard, but
adding an identical column or table for all tenants is easy.

There are other issues. Disaster recovery is a lot easier with "shared nothing"  than with shared tables (also called "shared everything"). With "shared nothing", you can just restore a database. With "shared everything", you have to restore just a few rows in every table.  Cost of hardware can be an issue when you scale up.  Data isolation and security is easier with "shared nothing", harder with "shared everything".


I have dealt with this question before. MySQL has a very flimsy architecture when it comes to triggers.

I have written posts before on how to break triggers midstream. The solution is rather unsavory and would leave a pure SQL developer with a bad taste in the month.

With that said, here are my posts that I hope would help:

Apr 25, 2011 : Trigger in MySQL to prevent insertion
Dec 23, 2011 : check constraint does not work?

Since you are a newbie, writing trigger code requires changing the delimiter to something other than a semicolon and then changing back.

DELIMITER $$

CREATE TRIGGER chk_dates
BEFORE INSERT ON verlof_aanvragen
FOR EACH ROW
BEGIN
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)
THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message';
END IF;
END $$

DELIMITER ;

Please look back at my posts to see how I do this in my code.

UPDATE 2013-06-12 11:34 EDT

Here is your last comment

  I am using above trigger with mysql server v 5.5.24 and it works fine.
  This is my local test site. Now I want to use it at my webhost, they
  have mysql server v 5.1.56 ... and it won; t work ! Am I missing
  something?

The problem is the MySQL version. If you look back in my two trigger posts, I used black market techniques to break the trigger midstream. This is necessary for older versions of MySQL. Notice what I stated on Apr 25, 2011 in Trigger in MySQL to prevent insertion :

  The reason the book suggests preempting the Trigger in this manner
  stems from the fact that the MySQL Stored Procedure Language did not
  have SIGNAL implemented into the language (of course, SIGNAL is ANSI
  standard).

  The authors of the book created work arounds by calling on SQL
  statements that are syntactically correct but fail at runtime. Pages
  144-145 (Chapter 6: Error Handling) of the book gives these examples
  on preempting a Stored Procedure directly (Example 6-18) or by SIGNAL
  emulation (Examples 6-19 and 6-20).

I directly quoted Pages 254-256 of the Book

Please note the following:

MySQL 5.5.8 was released as GA Dec 03, 2010
The book was written March 2006

Therefore, signal processing DOES NOT WORK IN MySQL 5.1.

You will have to do the following:

Rewrite the trigger in MySQL 5.1 to break midstream
The trigger you wrote in the MySQL 5.5 database should stay the way it is now

That's why the trigger does not work in MySQL 5.1.

UPDATE 2013-06-12 11:44 EDT

Here is what you can do to the trigger in the MySQL 5.1.56 database

DELIMITER $$

CREATE TRIGGER chk_dates
BEFORE INSERT ON verlof_aanvragen
FOR EACH ROW
BEGIN
  DECLARE dummy INT;
  IF (NEW.datum < CURDATE() + INTERVAL 8 DAY OR NEW.datum > CURDATE() + INTERVAL 365 DAY)
THEN
  SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
END IF;
END $$

DELIMITER ;

Since mysql.user has no column called used, the query does not execute, breaking the trigger.


Change database and execute update query (T- Transact)

One solution would be to use SQL Servers sp_executeslql function. Utilising this function you could write some sql like the sample below.

DECLARE @DatabaseToUse  VARCHAR(100),
        @SQLToRun       VARCHAR(200);

SELECT  @DateBaseToUse = 'MyDataBaseName';

SELECT @SQLToRun = 'UPDATE ' + @DatabaseToUse  + '.dbo.Table SET Column1 = Column1 + 1';

Execute sp_executesql @SQLToRun;

You should replace the .dbo. with the name of your Schema.


Yes, the results will be the same.  Take a look at this as an example:

if exists(select * from sys.tables where name = 'T1')
    drop table T1
go
if exists(select * from sys.tables where name = 'T2')
    drop table T2
go

create table T1
(
    id int not null,
    someText varchar(100) not null
)
go

insert into T1
values(1, 'hello'),
(3, 'bye'),
(6, 'what')
go

create table T2
(
    id int not null,
    someText varchar (100) not null
)
go

insert into T2
values(2, 'hi'),
(3, 'ciao'),
(4, 'no')
go

select *
from T1
left join T2
on T1.id = T2.id

select *
from T2
right join T1
on T2.id = T1.id

The output will have the same exact fields (mind you, if you use SELECT * the column order will be different between the queries) with the same exact data.

As a reference, here are the two execution plans:


How to define partition according to Date?

Here are the Steps to Partition Your Table by Month

Create a Temp Table, Partitioned and Indexed on LogDate

CREATE TABLE paramlog_ems_new (
  SiteIndex smallint(5) unsigned NOT NULL,
  RegionIndex smallint(5) unsigned NOT NULL,
  OrganizationIndex smallint(5) unsigned NOT NULL,
  DeviceSlaveId smallint(5) unsigned NOT NULL,
  UserIndex smallint(5) unsigned NOT NULL,
  ParameterID smallint(5) unsigned NOT NULL,
  ParamValue double(20,10) NOT NULL,
  DeviceName varchar(50) NOT NULL,
  LogDate datetime NOT NULL,
  Category tinyint(3) NOT NULL,
  KEY LogDate (LogDate)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(`LogDate`)
(PARTITION p00000000000 VALUES LESS THAN ('2015-01-01') ENGINE = MyISAM,
 PARTITION p2015_01_jan VALUES LESS THAN ('2015-02-01') ENGINE = MyISAM,
 PARTITION p2015_02_feb VALUES LESS THAN ('2015-03-01') ENGINE = MyISAM,
 PARTITION p2015_03_mar VALUES LESS THAN ('2015-04-01') ENGINE = MyISAM,
 PARTITION p2015_04_apr VALUES LESS THAN ('2015-05-01') ENGINE = MyISAM,
 PARTITION p2015_05_may VALUES LESS THAN ('2015-06-01') ENGINE = MyISAM,
 PARTITION p2015_06_jun VALUES LESS THAN ('2015-07-01') ENGINE = MyISAM,
 PARTITION p2015_07_jul VALUES LESS THAN ('2015-08-01') ENGINE = MyISAM,
 PARTITION p2015_08_aug VALUES LESS THAN ('2015-09-01') ENGINE = MyISAM,
 PARTITION p2015_09_sep VALUES LESS THAN ('2015-10-01') ENGINE = MyISAM,
 PARTITION p2015_10_oct VALUES LESS THAN ('2015-11-01') ENGINE = MyISAM,
 PARTITION p2015_11_nov VALUES LESS THAN ('2015-12-01') ENGINE = MyISAM,
 PARTITION p2015_12_dec VALUES LESS THAN ('2016-01-01') ENGINE = MyISAM,
 PARTITION p99999999999 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM);

Load the Temp Table

INSERT INTO paramlog_ems_new SELECT * FROM paramlog_ems;

Swap the Temp Table

ALTER TABLE paramlog_ems RENAME paramlog_ems_old;
ALTER TABLE paramlog_ems_new RENAME paramlog_ems;

GIVE IT A TRY !!!


If you need to replicate only a few tables master-master, then Bucardo is probably your best bet.


First-In and Last-Out times for Night Shift Employees

This query shifts the LogIn date by the number of minutes between midnight (0=00:00) and the start of the shift:

17:30 (Start) becomes 00:00
2:30 becomes 9:00.
2016-01-06 03:45:16.0000000 becomes 2016-01-05 10:15:16.0000000
...

It is used to get the date of the shift and it can then be used in a GROUP BY.

Query:

WITH s AS (
    SELECT s.UserID, e.LogIn, e.LogOut
        , [DATE] = CAST(DATEADD(minute, DATEDIFF(minute, s.[Start], 0), e.LogIn) as date)
    FROM @Shifts s
    INNER JOIN @Employees e ON s.UserID = e.UserID
)
SELECT s.UserID, [DATE]
    , FirstIN = CAST(MIN(LogIn) as time)
    , LastOut = CAST(MAX(LogOut) as time)
FROM s
GROUP BY s.UserID, [DATE];

See SQL Fiddle.

Output:

UserID  DATE        FirstIN             LastOut
2267    2016-01-04  20:52:08.0000000    03:57:16.0000000
2267    2016-01-05  18:43:50.0000000    03:45:16.0000000

Arrive early, leave late:

If someone is leaving a little late, it should work fine.

If someone arrive early, you can add some time to DATEADD such as:

[DATE] = CAST(DATEADD(minute, 90 + DATEDIFF(minute, s.[Start], 0), e.LogIn) as date)

Here it adds 90 minutes. This means that if someone arrives up to 90 minutes  early it will be counted on the current day. It will be for LogIn starting at 16:00 with your sample (17:30 - 90mins)
If an employee arrives 2h early (> 90min), it is counted as the previous day.

Working hours:

The number of hours between FirstIn and LastOut in hours is calculated like this:

DATEDIFF(hour, MIN(LogIn), MAX(LogOut))

Or like this:

DATEADD(minute, DATEDIFF(minute, MIN(LogIn), MAX(LogOut)), CAST('' as time))

Working hours output:

UserID  DATE        FirstIN             LastOut             WorkHours   WorkTime
2267    2016-01-04  20:52:08.0000000    03:57:16.0000000    7           07:05:00.0000000
2267    2016-01-05  18:43:50.0000000    03:45:16.0000000    9           09:02:00.0000000


SQL Server Multiple JOINS with Table Value Function - query never ends

Try changing the function to a non-inline table value function, with a clustered index. This will store the tvf's result set in an internal temp table, which may help you. A draft of what I mean:

CREATE FUNCTION [dbo].[GetIndicator]
(
  @indicator varchar(50),
  @refDate datetime
)
RETURNS @out TABLE (
    Id int NOT NULL,
    ColINT_1 int NOT NULL,
    -- ... and so on
    PRIMARY KEY CLUSTERED (Id)
)
AS

BEGIN;

    INSERT INTO @out
    SELECT
    T1.Id
    , T1.ColINT_1
    , T1.ColNVARCHAR_1 collate DATABASE_DEFAULT as ColNVARCHAR_1
    , T1.ColNVARCHAR_2
    , T1.ColSMALLDATETIME_1
    , T1.ColDECIMAL_1
    , T1.ColDECIMAL_1

    FROM TABLE2 T2
        JOIN TABLE3 T3
            ON T2.COLFKT3 = T3.Id
        AND T3.ReferenceDate = @RefDate
        AND T3.State != 'Deleted'
    JOIN TABLE4 T4
    ON T2.COLFKT4 = T4.Id AND T4.Name=@indicator
    JOIN TABLE1 T1
        ON T2.COLFKT1=T1.Id;

    RETURN;

END;

Essentially, I think it's a performance issue you're facing. Here's a simplified view of what happens: SQL Server "expands" the inline table value functions, pretty much like it would with a view. This way, you get 12 joins in a single query:

1. othertable
   join table2, table3, table4
   join table2, table3, table4
   join table2, table3, table4
   join table2, table3, table4

A table value function that isn't inline, however, stores its output in a form of temporary table that it returns to the caller, so it isn't expanded the same way.

1. (join table2, table3, table4) -> store in #temp1
2. (join table2, table3, table4) -> store in #temp2
3. (join table2, table3, table4) -> store in #temp3
4. (join table2, table3, table4) -> store in #temp4
5. join othertable, #temp1, #temp2, #temp3, #temp4

Here, the workload is split up into multiple elements. Some situations will benefit from an inline table-value function, whereas others (like yours) will suffer a performance penalty. It has to do with how many tables you're joining, the amount of data in each table, indexing, etc.


connect two sql server and import to CSV file (ETL)

Welcome to the group....You can also use linked server, below link could help you

https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server

As per the comments, adding the details from the thread mentioned

Querying across 2 different databases is a distributed query. Here is a list of some techniques plus the pros and cons:

Linked servers: Provide access to a wider variety of data sources than SQL Server replication provides

Linked servers: Connect with data sources that replication does not support or which require ad hoc access

Linked servers: Perform better than OPENDATASOURCE or OPENROWSET

OPENDATASOURCE and OPENROWSET functions: Convenient for retrieving data from data sources on an ad hoc basis. OPENROWSET has BULK facilities as well that may/may not require a format file which might be fiddly

OPENQUERY: Doesn't support variables
All are T-SQL solutions. Relatively easy to implement and set up
All are dependent on connection between source and destionation which might affect performance and scalability

Follow these steps to create a Linked Server:
1. Server Objects -> Linked Servers -> New Linked Server
2. Provide Remote Server Name.
3. Select Remote Server Type (SQL Server or Other).
4. Select Security -> Be made using this security context and provide login and password of remote server.


MySQL 'generator' not closing connections

See if you have enable "connection pooling".  If so, don't worry about the connections staying open.


SQL Server Log Shipping for Server Migration - What's the final step?

You need to remove the log shipping metadata. The correct way to do this is via stored procedures, which also removes the jobs (I'm guessing you've deleted those manually).

Execute sp_delete_log_shipping_secondary_primary. This removes the information about the old primaries on the secondary server (Server A). Also removes the jobs if they exist.
Execute sp_delete_log_shipping_secondary_database. This removes the information about the secondary database. NOTE: This does not remove the DB itself, even though the documentation wording makes it sound like it might. Also, this should be called as part of step 1's stored proc, however, I've found that with orphaned log shipping configs, sometimes it doesn't execute so I run it manually afterwards.


Measure it and see, using explain analyze on test data.

I would expect a significant difference between an expression index on lower(col) with lower(col) = lower('constant') vs use of col ilike 'constant', with the expression index on lower(col) the faster.

That's because Pg can do a bitmap index scan with the expression index, but for the ilike it'll have to do a seqscan. AFAIK a text_pattern_ops index (useful for LIKE) won't do you any good for ILIKE.

The other question is whether the difference is worth caring about, and that depends on your data and workload. Sometimes it's smarter to just choose the slower but easier option and spend yourtime/effort elsewhere.

  Will column_name ilike 'value' use lower(column_name) index?

No. Nor will it use an index on lower(column_name) text_pattern_ops. AFAIK there is no way to index a case-insensitive pattern match (ILIKE or ~~*) on a case-sensitive text/varchar in PostgreSQL at this time.

I don't think the citext type changes this, though it does make things more convenient.


If you want to make sure that all days are the same, just compare the min and max. If they are the same, then all values are the same as well.
with dif_zero as (
  select d.id,
         date_part('day', p.docdate)::int pday
  from sh_billing.dogovor d
   join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from
dif_zero
group by id
having sum(char_sal_dif) = 0 -- where does char_sal_dif come from?
   and min(pday) = max(pday)

A more costly (=slower) but maybe easier to understand way, is to compare the count with the count of distinct values:
having count(*) = count(distinct pday)`


SQL Server 2008 - How would I create a dynamic query for finding relationships and references to the primary key?

The query I've used for the scenario above, is below. Let me know what you think, and if you've links to other such generic and useful tips.

/*
Modified from Pinal Dave's original @ SQLAuthority:
    http://blog.sqlauthority.com/2009/02/26/sql-server-2008-find-relationship-of-foreign-key-and-primary-key-using-t-sql-find-tables-with-foreign-key-constraint-in-database/

Supply the name of the table, and optionally the ID (default = NULL) whose
references you wish to see from other tables.
*/

DECLARE @SEARCHED_NAME VARCHAR(50), @SEARCHED_ID INT
SET @SEARCHED_NAME = 'TABLENAME' -- The name of the table whose constraint connections you wish to see.
SET @SEARCHED_ID = NULL -- The parent table ID you want to find in any of the tables referencing the parent table.

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME(f.parent_object_id) LIKE @SEARCHED_NAME
OR OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
ORDER BY f.name

IF @SEARCHED_ID IS NOT NULL
BEGIN

    CREATE TABLE #SEARCHOBJREF (RowNum INT IDENTITY(1,1), TableName VARCHAR(256)
        , ColumnName VARCHAR(256), MatchCount INT)

    INSERT INTO #SEARCHOBJREF (TableName, ColumnName)
    SELECT DISTINCT OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
    WHERE OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
    ORDER BY OBJECT_NAME(f.parent_object_id)

    DECLARE @ROW_NOW INT, @ROW_MAX INT, @SQL VARCHAR(MAX), @TNAME VARCHAR(256), @COLNAME VARCHAR(256)
    SELECT @ROW_NOW = MIN(RowNum), @ROW_MAX = MAX(RowNum) FROM #SEARCHOBJREF

    WHILE @ROW_NOW <= @ROW_MAX
    BEGIN
        SELECT @TNAME = TableName, @COLNAME = ColumnName FROM #SEARCHOBJREF WHERE RowNum = @ROW_NOW

        SET @SQL = 'UPDATE #SEARCHOBJREF '
            + ' SET MatchCount = (SELECT COUNT(*) FROM ' + @TNAME + ' WHERE '
            + @COLNAME + ' = ' + CAST(@SEARCHED_ID AS VARCHAR(64)) + ') '
            + 'WHERE RowNum = ' + CAST(@ROW_NOW AS VARCHAR(64))
        EXEC(@SQL)

        SET @ROW_NOW = @ROW_NOW + 1
    END

    SELECT TableName, ColumnName, MatchCount as 'Matches_for_ID' FROM #SEARCHOBJREF WHERE MatchCount > 0
    DROP TABLE #SEARCHOBJREF

END


why has Lock_time in slow query log when execute select sql?

From the looks of the query, my guess is that you are looking up all the defined columns in INFORMATION_SCHEMA.COLUMNS that are defined as INT.

The reason why there was a lock on INFORMATION_SCHEMA.COLUMNS ???

The table INFORMATION_SCHEMA.COLUMNS is defined as a temporary memory table. Memory tables undergo full tables locks when being accessed for change (such as when one does ALTER TABLE or CREATE TABLE) from anywhere in the database,

Here is an excerpt from my old post How is INFORMATION_SCHEMA implemented in MySQL?

  All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.

Please note what is in bold

  It is a temporary table in memory. It is manipulated using storage engine protocols.

When you look up the MEMORY storage engine in the Docs, it shows that Lock Granularity is Table. So, every table in the INFORMATION_SCHEMA, including INFORMATION_SCHEMA.COLUMNS will received a lock at some point, EVEN FOR A SELECT (just like a MyISAM table).


restrict user host settings to socket connection only

If you only want to listen MySQL connections only using socket add a line in my.cnf and restart MySQL server.

skip-networking

This is recommended practice when there is no reason for it to listen on TCP.


See disclaimer at the end.

First you need to figure out if there is a reason that the log is large/growing. A quick way to do that is to run this query (see http://sqlity.net/en/556/t-sql-tuesday-25-%E2%80%93-sql-server-tips-tricks/ for more info):

SELECT log_reuse_wait_desc,name FROM sys.databases;

If that comes back with "NOTHING" in the log_reuse_wait_desc column for your database you can go ahead and shrink the log file. The easiest way to do that is using SSMS. Right-click your database and select "Tasks", "Shrink", "Files". That brings up this dialog:

Select "Log" as File Type and select "Release unused space". Click OK to execute.

DISCLAIMER: If you follow these steps, you are doing a fairly un-intrusive operation. However, repeated shrinking and growing can have very bad effects on your database performance. So if your file is 12GB because that is the size needed to support normal operations you are better off leaving it that way. As there is no real good way to determine the necessary size (other then letting it grow) I would leave the file alone unless you are running out of disk space. There is no negative performance impact for having a too big log file.


It appears as if ssrs 2016 does not support IE9. The machine I was using to access the server remotely only had IE9 installed. When I used the IE development tools on the server itself to browse to the site using IE9 standards, I had the same problems.


If you are reading from a node other than then node where the transaction was commit, there is a finite response time on the other nodes from the time it is certified, to the time it is applied on the other node.

In theory you should be able to wait to ensure its committed.


You have 2 options, perfmon or get the information directly from SQL. Perfmon is point-in-time, SQL's stats are aggregated since the last service restart.

For Perfmon, you'll want to use Physical Disk and provide them the following information:

Disk Reads/Sec + Disk Writes/Sec = IOPS

Disk Reads Bytes/Sec + Disk Writes Bytes/Sec = Throughput

Make sure you have enough data to fully represent your workload. For instance, if you have off-hours processes that run, like a big ETL process that runs overnight or on the weekend, make sure those are captured as well. Your storage team can't spec for something if they don't know the need exists.

You can also get this information directly from SQL. Remember this aggregated, so you'll need to take periodic snapshots and calculate the diffs. The one advantage with this method is you can break down the Stats by Database. You may find that one database has much higher IOPS/Throughput needs and this will give your Storage Admins more information in case they want to deal with it differently.

This article is a good starting point on how to capture snapshots of virtual_io_stats:  https://www.mssqltips.com/sqlservertip/1416/gather-io-statistics-down-to-the-sql-server-database-file-level/


If you perform a full RESTORE WITH RECOVERY, that means you want to only restore the full backup and immediately bring the database online.

When you perform a full RESTORE and want to also restore differential or transaction log backups as well, then you want to RESTORE WITH NORECOVERY so that the database does not come online and additional restores are possible.

You can use T-SQL to perform both at the same time (copied from BOL):
Restore Transaction Log Backup

RESTORE DATABASE AdventureWorks2012
   FROM AdventureWorks2012_1
   WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 1,
   NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 2,
   NORECOVERY;
GO
RESTORE LOG AdventureWorks2012
   FROM AdventureWorks2012_log
   WITH FILE = 3,
   NORECOVERY;
GO
RESTORE DATABASE AdventureWorks2012
   WITH RECOVERY;
GO


Oracle database link to MySQL database on RedHat Linux

Oracle can connect to MySql with an ODBC connector.  This is described in some detail here.  Also see the official Oracle documentation here.

This will work for Oracle 10 and 11g to MySQL 4.1, 5.0, 5.1 or 6.0.  There are limitations both in performance and what you can do.  My understanding is

no distributed transactions
no insert as select .....
treat numbers as numbers and strings as strings, this is not the place for implicit type casts
as you might expect non-Oracle features are not supported
no Oracle DDL is supported
these functions are supported AVG(exp), LIKE(exp), COUNT(*), MAX(exp), MIN(exp), NOT


PostgreSQL: How can I list the tables to which a sequence belongs?

The information is stored in pg_depend:

SELECT t.oid::regclass AS table_name,
       a.attname AS column_name,
       s.relname AS sequence_name
FROM pg_class AS t
   JOIN pg_attribute AS a
      ON a.attrelid = t.oid
   JOIN pg_depend AS d
      ON d.refobjid = t.oid
         AND d.refobjsubid = a.attnum
   JOIN pg_class AS s
      ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
  AND d.refclassid = 'pg_catalog.pg_class'::regclass
  AND d.deptype = 'i'
  AND t.relkind IN ('r', 'P')
  AND s.relkind = 'S';


Is it a common pattern in having two AlwaysOn Availability Groups for DR and Reporting?

This would be one AG with 3 replicas, not two separate AGs.

In general is fine, but probably overkill.  Normally if you have an FCI and an AG replica for DR you would use the DR node for reporting.  In a disaster you wouldn't have a separate reporting replica, but disasters are, you know, bad.

For Quorum, a cluster with a single FCI should normally use Node and Disk Majority (or Node and File Share if the databases are stored on SMB shares).  Neither of the other nodes should have a vote.

See WSFC Quorum Modes and Voting Configuration (SQL Server)


MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group

So unfortunate news for you is it sounds like this closed source application uses a potentially deprecated feature of SQL Server if it's using SAVE TRANSACTION, and was not designed to run for a database that's part of an Availability Group (or any kind of clustering for that matter). Even more unfortunate is your only three options are:

Thoroughly verify it isn't breaking anything when you use that functionality of the application. That should involve testing that functionality and measuring the results on your primary, and then failing over to your secondary and verifying the same results did in fact commit to that node as well, and the application is still working as expected on the secondary. If all's well, then just live with the error.

Reach out to the vendor and communicate to them that you're trying to run their application on a clustered server with an Availability Group and are receiving an error for a specific function of their application that appears to use a deprecated feature of SQL Server, and that you need their confirmation if this is ok, and/or does their application support Availability Groups. You'll probably want do this anyway, if you pick option 1.

Disable distributed transactions with SET ( DTC_SUPPORT = NONE ); as you've seen this removes the error, but it does make your Availability Group less reliable which I'll answer directly to your questions below.

Out of these options, your best bet is #2, so that you can work with the vendor and let them know the full situation in which you're trying to use their application. It sounds like Availability Groups aren't directly supported by it and they'd be able to confirm that. Unfortunately for you, that's just a constraint from the vendor, and there's not much else you can do other than the options I provided above, and/or don't use Availability Groups / clustering, rather have an alternative high availability plan in place such as maintaining a completely decoupled, second server, which you regularly restore backups to. (Obviously this is less than ideal.)
To directly answer your questions on disabling distributed transactions:

What impact does this change have?

There is a risk of data loss from unhardened (uncommitted) transactions when a failover event occurs from the primary to the secondary. While this risk is potentially small, and I believe only affects your distributed transactions themselves (such as this application functionality with the SAVE TRANSACTION code) it's still a risk. This is discussed in the Microsoft Books Online for Configure distributed transactions for an Always On availability group, specifically when they say:

SQL Server does not prevent distributed transactions for databases in an availability group - even when the availability group is not configured for distributed transactions. However when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions.

Is the cluster still active?

Yes the cluster is still active and you should be able to easily test that by making a minor data change on the primary and watching it update on the secondary.

Is the data still replicated to the secondary server?

Yes, same answer as above.

Does it have a positive or negative impact on the performance?

Not relevant to the change, and probably measurably no difference.

Has a safety feature just been disabled?

Yes, your distributed transactions are at risk for data loss now if a failover were to occur when they were still active transactions (i.e. the secondary now becoming the primary wouldn't know if it's correct to rollback those transactions, rollforward or commit them), as discussed in my first answer to your questions. How much risk this is to you will depend on how important that data is for the feature that uses the SAVE TRANSACTION code, and anywhere else you may be explicitly using distributed transactions.


Surprise, surprise, surprise!  SSIS doesn't fail the Foreach File loop container if the path is inaccessible – wow... how fun.  You'll have to add a script task to check before the loop fires.


Optimal Hardware for > 30K Requests / min?

500 inserts per second is definitely into the area where out-of-the-box MySQL begins to have trouble.

Some things to do:

Don't trust benchmarks; use your code.
Combine one-row INSERTs into transactions.  (Much of what is limiting you to 500qps is the transaction overhead.
"Batch" inserts -- 100-1000 rows per INSERT statement gives you 10x performance improvement.
Use LOAD DATA -- also 10x (maybe more).
Switch to SSDs -- perhaps 10x faster than spinning drives.
innodb_flush_log_at_trx_commit=2 -- much faster, but less secure in a crash.
Decrease the number of indexes, especially UNIQUE, on the table.
Do not use UUIDs.
Gather data some other way (eg, MyISAM table with no indexes), then batch INSERT...SELECT....  This has the added advantage of giving you a change to efficiently do ETL transformations.
My High Speed Ingestion technique.  It involves ping-ponging between two staging tables and handles various variations (multiple connections, etc)


Assuming that you want - if the max(number) in the whole table is say, 27 - to update  all the rows with NULL, to 28.

You need a subquery or a derived table to first find this max and then join back to the table:

UPDATE tableX AS t
  CROSS JOIN
    ( SELECT MAX(number) AS max_number
      FROM tableX
    ) AS m
SET t.number = m.max_number + 1
WHERE t.number IS NULL ;


How can I tell which data sources are being used in SSRS?

Okay, I got it.  Found this link which helped: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/581c7068-0abe-49f9-a1a5-93e94f472641/

I distilled that information to this query:

select
  Catalog.name,
  cat1.Name datasource
from
  Catalog
  join DataSource
    on Catalog.ItemID = DataSource.ItemID
  join Catalog cat1
    on DataSource.Link = cat1.ItemID
where
  Catalog.Type = 2

Hope this helps someone else in the future!


So, this actually wasn't a bug. There is (was) a trigger on the table that was changing data. The changing data is what caused the conversion to fail. Disabling the trigger did not help, it had to be dropped.


Timeout in SSIS package running DBCC CHECKDB

As it turns out, there is a CommandTimeout property on the SqlCommand object.


MySQL Query doesn't work but works some time, whats wrong with it?

NOW() in number context is treated as de-delimitered textual datetime in short format (see Conversion Between Date and Time Types, the last demo). So substraction can cause invalid datetime value.

To obtain correct result perform calculations in datetime context. The best (and simplest) way is to use

NOW() - INTERVAL 60 SECOND

or use some datetime function.


Shrink multiple database files using sp_MSForEachDB

The question mark will evaluate to the database name, not the file name you're trying to shrink.

For instance:

EXEC master.sys.sp_MSforeachdb ' USE [?]; PRINT N''?''; ';

Will return (on my server)

master
tempdb
model
msdb
SUPERUSER
StackOverflow
StackOverflow_CS
Crap
DBAtools
StackOverflow2010
SUPERUSER_CX
ಠ_ಠ
StackOverflow2010ಠ_ಠ

DBCC SHRINKFILE doesn't take that as an argument:

  DBCC SHRINKFILE    (
      { file_name | file_id }
      { [ , EMPTYFILE ]
      | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
      }   )   [ WITH NO_INFOMSGS ]

If you only have a .mdf and a .ldf, you can (likely, though not definitely) replace your code with:

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (1 , 10)'

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)'

More verbose code to look up file ids, etc. is left as an exercise to the reader.

If you want to just shrink the whole thing, use DBCC SHRINKDATABASE instead. That takes a database name, and will work with your original code.

  DBCC SHRINKDATABASE    ( database_name | database_id | 0
       [ , target_percent ]
       [ , { NOTRUNCATE | TRUNCATEONLY } ]    )   [ WITH NO_INFOMSGS ]

Of course, this can cause all sorts of problems, and I wouldn't wanna do it.


Combining separate ranges into largest possible contiguous ranges

Assumptions / Clarifications

No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.)

NULL vs. infinity in PostgreSQL range types

Since date is a discrete type, all ranges have default [) bounds.
The manual:

The built-in range types int4range, int8range, and daterange all use a
canonical form that includes the lower bound and excludes the upper
bound; that is, [).

For other types (like tsrange!) I would enforce the same if possible:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Solution with pure SQL
With CTEs for clarity:
WITH a AS (
   SELECT range
        , COALESCE(lower(range),'-infinity') AS startdate
        , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
   FROM   test
   )
, b AS (
   SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
   FROM   a
   )
, c AS (
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM   b
   )
SELECT daterange(min(startdate), max(enddate)) AS range
FROM   c
GROUP  BY grp
ORDER  BY 1;

Or, the same with subqueries, faster but less easy too read:
SELECT daterange(min(startdate), max(enddate)) AS range
FROM  (
   SELECT *, count(step) OVER (ORDER BY range) AS grp
   FROM  (
      SELECT *, lag(enddate) OVER (ORDER BY range) < startdate OR NULL AS step
      FROM  (
         SELECT range
              , COALESCE(lower(range),'-infinity') AS startdate
              , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
         FROM   test
         ) a
      ) b
   ) c
GROUP  BY grp
ORDER  BY 1;

How?
a: While ordering by range, compute the running maximum of the upper bound (enddate) with a window function.
Replace NULL bounds (unbounded) with +/- infinity just to simplify (no special NULL cases).
b: In the same sort order, if the previous enddate is earlier than startdate we have a gap and start a new range (step).
Remember, the upper bound is always excluded.
c: Form groups (grp) by counting steps with another window function.
In the outer SELECT build ranges from lower to upper bound in each group. Voilá.
Or with one less subquery level, but flipping sort order:
SELECT daterange(min(COALESCE(lower(range), '-infinity')), max(enddate)) AS range
FROM  (
   SELECT *, count(nextstart > enddate OR NULL) OVER (ORDER BY range DESC NULLS LAST) AS grp
   FROM  (
      SELECT range
           , max(COALESCE(upper(range), 'infinity')) OVER (ORDER BY range) AS enddate
           , lead(lower(range)) OVER (ORDER BY range) As nextstart
      FROM   test
      ) a
   ) b
GROUP  BY grp
ORDER  BY 1;

Sort the window in the second step with ORDER BY range DESC NULLS LAST (with NULLS LAST) to get perfectly inverted sort order. This should be cheaper (easier to produce, matches sort order of suggested index perfectly) and accurate for corner cases with rank IS NULL. See:

PostgreSQL sort by datetime asc, null first?

Related answer with more explanation:

Compare multiple date ranges

Procedural solution with plpgsql
Works for any table / column name, but only for type daterange.
Procedural solutions with loops are typically slower, but in this special case I expect the function to be substantially faster since it only needs a single sequential scan:
CREATE OR REPLACE FUNCTION f_range_agg(_tbl text, _col text)
  RETURNS SETOF daterange AS
$func$
DECLARE
   _lower     date;
   _upper     date;
   _enddate   date;
   _startdate date;
BEGIN
   FOR _lower, _upper IN EXECUTE
      format(
         $sql$
         SELECT COALESCE(lower(t.%2$I),'-infinity')  -- replace NULL with ...
              , COALESCE(upper(t.%2$I), 'infinity')  -- ... +/- infinity
         FROM   %1$I t
         ORDER  BY t.%2$I
         $sql$, _tbl, _col)
   LOOP
      IF _lower > _enddate THEN     -- return previous range
         RETURN NEXT daterange(_startdate, _enddate);
         SELECT _lower, _upper  INTO _startdate, _enddate;

      ELSIF _upper > _enddate THEN  -- expand range
         _enddate := _upper;

      -- do nothing if _upper <= _enddate (range already included) ...

      ELSIF _enddate IS NULL THEN   -- init 1st round
         SELECT _lower, _upper  INTO _startdate, _enddate;
      END IF;
   END LOOP;

   IF FOUND THEN                    -- return last row
      RETURN NEXT daterange(_startdate, _enddate);
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:
SELECT * FROM f_range_agg('test', 'range');  -- table and column name

The logic is similar to the SQL solutions, but we can make do with a single pass.
SQL Fiddle.
Related:

GROUP BY and aggregate sequential numeric values

The usual drill for handling user input in dynamic SQL:

SQL injection in Postgres functions vs prepared queries

Index
For each of these solutions a plain (default) btree index on range would be instrumental for performance in big tables:
CREATE INDEX foo on test (range);

A btree index is of limited use for range types, but we can get pre-sorted data and maybe even an index-only scan.


different settings for max_heap_table_size and tmp_table_size (MariaDB 10.1.x)

Short Answer: None of those is "wrong".

How are those settings used?

There are (if I am not mistaken) exactly 2 uses for those two settings.  They are, as alluded to in some of what you quoted:

When you do CREATE TABLE ... ENGINE=MEMORY, it is given a limit of the current value of max_heap_table_size.  You can, and "should", change that setting just before doing that CREATE.  Too large a value could waste precious RAM.
When complex SELECT needs to create a temporary table, such as in preparation for ORDER BY, it first tries to use a MEMORY table; if this fails (for any of several reasons), it resorts to using MyISAM.  The limit on the MEMORY table size is min(max_heap_table_size, tmp_table_size).

I do not believe tmp_table_size is every used by itself.

If you never explicitly create a MEMORY table, the having those two settings the same avoids confusion and discussions like this.

So, the admonition to make them equal is weaker than the wording you quote.  It is "sorta, kinda, oughta", and not "you MUST!".

A different perspective:  Let me approach your question from another point of view.

Intra-select tmp tables can happen often -- in every connection, and even multiple times in a single select.  So, keeping tmp_table_size "low" is important to avoid blowing out RAM.  I recommend no more than 1% of RAM, but this is rather arbitrary.

If, meanwhile, you are playing around with MEMORY tables, you need control over their sizes.

OK, I have hit a dead end.  Why do they have the "min(...)"?  Why not just leave them separate?

I don't know.

Future:  The above discussion applies to at least MySQL versions 4.0 to 5.7, plus all versions (so far) of MariaDB.  MySQL 8.0 uses a "temp table engine", which may lead to some of the above discussion being moot.


Several issues:

You are trying to take an average per customer, but you're not grouping by customer.
Implicit math for datetimes (OrderDate-PriorDate) is not a good idea (try that with date or datetime2) - use DATEDIFF.
Integer division will not give you an average like you think - try SELECT 5/2;. You need to convert at least one input to a decimal, either implicitly (*1.0) or explicitly (CONVERT(), TRY_CONVERT(), CAST(), etc.). Explicit allows you to control decimal places in some cases.
LAG() was not complete - while the default is 1, I think it is good to be explicit that you want the previous row.
The incorrect syntax error is actually coming from the end of the query - when you have something like SELECT ... FROM (<subquery>), that subquery needs to be named, so you need to use something like SELECT ... FROM (<subquery>) AS x; for example.

Try the following:

SELECT
  CustomerID,
  AvgLag = AVG(CONVERT(decimal(7,2), DATEDIFF(DAY, PriorDate, OrderDate)))
FROM
(
  SELECT CustomerID, OrderDate, PriorDate = LAG(OrderDate,1)
    OVER (PARTITION BY CustomerID ORDER BY OrderDate)
  FROM #Orders
  WHERE CustomerID = 100
) AS lagged
GROUP BY CustomerID;

Results:

CustomerID    AvgLag
----------    ------
100           7.50

Of course, if you want the average for all customers, just leave out the WHERE clause. But if you really only ever want a single customer, you don't really need the customer in the output, so you can adjust the query a little bit to get rid of the GROUP BY (let's parameterize the customer ID while we're at it):

DECLARE @CustomerID int = 100;

SELECT AvgLag = AVG(Lag), CustomerID = @CustomerID -- you may not need this
FROM
(
  SELECT Lag = CONVERT(decimal(7,2), DATEDIFF(DAY, LAG(OrderDate,1)
    OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate))
  FROM #Orders
  WHERE CustomerID = @CustomerID
) AS Lagged;


TempDB data files don't shrink well on SQL 2008

You've got two different questions in here:

Q: Sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.

A: When that happens, all of the TempDB data files will grow roughly equally, so you won't have to worry about shrinking specific ones. Frankly, you don't want to shrink them - if you've got drive space set aside for TempDB, just leave these files in place. Why keep re-fighting the same battle? You'll have a rogue developer run another query in a few weeks. Just leave this in place. You don't get bonused based off empty drive space.

Now, having said that, if you've got TempDB's data and log files on the same volume as your user databases (or heaven forbid, the boot drive), then that's the real root cause, and you need to fix that. Even if you don't put them on separate spindles, TempDB should be on a separate logical volume to mitigate this exact problem. When it fills up, it fills up - but it doesn't take user databases (or the entire server, in the event of a full C drive) offline.

Q: (DBCC SHRINKFILE) seems to not work very often in 2008

It's more a function of how SQL Server relies more and more on TempDB in each release. When something's active in TempDB, you can't move its data around, and each new version of SQL Server works more in TempDB. For example, when you enable Read Committed Snapshot Isolation, the version store it uses lives in TempDB. When you use AlwaysOn Availability Groups, it tracks user database statistics in TempDB too. This is just another reason why you set aside a logical volume for TempDB, size the data files to fill it up, and then walk away - your work here is done, and don't try to shrink those files.


Can I bulk insert into an empty page-compressed table and get full compression?

@HandyD is  entirely correct, I only want to highlight some other methods to get compression while inserting into a heap.

From the same document

  When a heap is configured for page-level compression, pages receive
  page-level compression only in the following ways:

  Data is bulk imported with bulk optimizations enabled.
  Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax and the    table does not have a nonclustered index.
  A table is rebuilt by executing the ALTER TABLE ... REBUILD statement    with the PAGE compression option.

According to this, you could leverage minimally logged bulk inserts or use INSERT INTO ... WITH (TABLOCK)  to get PAGE compression without having to do rebuilds.

  (a) what is going on here? and (b) is there a way to get this
  extra-small compressed size directly as I load the table without
  having to rebuild after the data is loaded?

There are rules to get PAGE compression when inserting into a heap, add -h "TABLOCK" to your bcp command to get compression.

ROW compression works without these prerequisites and is the least amount of compression used in below examples, thanks @DenisRubashkin for pointing that out!

Testing

Example start data & BCP out command

--Tested on SQL Server 2014 SP2

CREATE TABLE dbo.CompressedHeap_Source( Val varchar(512),
                                 Datefield Date,
                                 Tinyfield TinyINT,
                                 Floatfield float)
WITH (DATA_COMPRESSION = PAGE);

INSERT INTO dbo.CompressedHeap_Source
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

--bcp TEST.dbo.CompressedHeap_Source out E:\Data\HeapData.bcp -c -T

The ROW compressed and Uncompressed size

The  data size is at  132272 KB when doing a standard insert into the heap, this is ROW compressed but not PAGE compressed.

The data size without any compression is ~ 176216 KB for our test.

exec sp_spaceused 'dbo.CompressedHeap_Source'

name                    rows                    reserved    data      index_size    unused
CompressedHeap_Source   6365530                 132296 KB   132272 KB   8 KB    16 KB

INSERT INTO ... WITH TABLOCK

Inserting WITH TABLOCK gives us the PAGE compressed data size, 69480 KB.

INSERT INTO dbo.CompressedHeap_Source2  WITH(TABLOCK)
(
Val,Datefield,Tinyfield,Floatfield)

SELECT 'Bla',cast(getdate() as date),1,1.2412
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2

BULK INSERT

Now when we create a destination heap table that is also page compressed, and do a bulk insert with tablock:

CREATE TABLE dbo.CompressedHeap_Destination( Val varchar(512),
                                 Datefield Date,
                                 Tinyfield TinyINT,
                                 Floatfield float)
WITH (DATA_COMPRESSION = PAGE);

bulk insert dbo.CompressedHeap_Destination

from 'E:\Data\HeapData.bcp'  with (TABLOCK)

The data gets page compressed and is also at 69480 KB:

name    rows    reserved    data    index_size  unused
CompressedHeap_Destination  6365530                 69512 KB    69480 KB    8 KB    24 KB

BCP IN WITH TABLOCK

You can get the same results as the BULK INSERT WITH TABLOCK by using BCP IN with the -h "TABLOCK" hint. This makes sense, they do the same internally

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T -h "TABLOCK"

With the resulting size being 69480 KB

BCP IN WITHOUT TABLOCK

Using BCP to load data from the same file in a copy of the destination table

And a standard bcp command results into non compressed data:

--bcp TEST.dbo.CompressedHeap_Destination2 IN E:\Data\HeapData.bcp -c -T

With the data size at 132272 KB (row compressed).


How to free temporary lob used in result set of SQL SELECT in Oracle

Before executing DBMS_LOB.CLOSE( lob_loc => lv_clob ); you need to execute dbms_lob.FREETEMPORARY(lv_clob);

According to the Oracle documentation it is required to free lob before closing.


Reducing the number of tables used - mySQL

You have 2 entities (taxes and groups) joined as M:N. 3-table structure for to store this data is optimal in most cases. Additional recommendations:

1) declare id field in val_taxes and val_tax_groups as a primary key and alter their datatype to INT or BIGINT;

2) remove id field from tax_mapping table structure;

3) alter valtaxgroups_id and valtaxes_id datatype to INT or BIGINT;

4) define (valtaxgroups_id,valtaxes_id) field combination as primary key;

5) create foreign key references from valtaxgroups_id to val_tax_groups.id and from valtaxes_id to val_taxes.id.

– Akina


Why does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock?

Can some or all of the 4 steps be replaced by a single query:

INSERT ... ON DUPLICATE KEY UPDATE ...

or maybe

INSERT IGNORE ... and check rows_affected?

It would be better if you showed us all the SQL between BEGIN and COMMIT.

Are you doing any time-consuming processing between BEGIN and COMMIT?  Can any of it be moved out.  The faster the transaction runs, the less likely it will deadlock.

Do you have code to rerun the transaction when it deadlocks?  This a useful "last resort".  (It is not yet obvious whether the particular deadlock can always be avoided.)


Find the last SQL statement executed for a given connection

You can enable "general query log", which will log each executed query, along with its session/connection ID, so if you grep the connection ID, you will have all the queries on one session.

HOWEVER, 'sleep'ing connections are not the cause of max connection error, but a result of it. They are not harmful to stay there. In fact, they are established connections that can be re-used by future sessions.

What would be more useful in this case is to search the slow query log, and check the innodb status to get information about locks and dead locks that have happened:

SHOW ENGINE INNODB STATUS;


To find out more about the PREEMPTIVE_COM_SEQSTRMREAD wait type I would read this great article at sqlauthority, but as Shanky said in the comments, SSIS would more than likely serve you better for a large migration.


How do I find a list of customers that have not placed an order in the past 1 year?

SELECT c.customer_id
    FROM customers AS c
    WHERE NOT EXISTS ( SELECT 1 FROM orders
                      WHERE customer_id = c.customer_id
                        AND sale_date > NOW() - INTERVAL 1 YEAR
                     )


Best relational database structure for this data

The way you've modelled it is fine.  Your model ensures that the business rules are enforced by the database.

There are a couple of things you could do as an alternative.  One would be to eliminate the surrogate key on Roles_Applications.  As an intersection table, you could use the two foreign keys together as a composite primary key.  If you did this, that would propagate Role and Application down to your Applications_Permissions_Roles table.  This would have the advantage of giving you more of a "one stop shop" for your application permission data (i.e. fewer joins) without compromising your normalization in any way.

Another way you could go would be to simplify slightly and define a default permission for each application.  You could call it whatever you like, such as "default access" or "basic access" or "user" or whatever makes sense to you.  This would allow you to flatten your model and essentially drop the Roles_Applications table and join Applications_Permissions_Roles straight to Roles.  This would change the nature of the query that you would use to ask "which roles can access which applications?" but your business rules would still be enforced by the schema.


Can I store half of a record on one page and half of the record on another page?

You are splitting hairs over semantics. A physical record cannot span pages. A physical record has to fit in a page.

But a index (or heap) row consists potentially from more than one physical records. An index row will consist from exactly one record in the IN_ROW_DATA allocation unit and zero, one or more records in ROW_OVEFLOW_DATA and/or LOB_DATA. The IN_ROW_DATA record contains pointers to overflow or LOB records. These off-row records themselves may contain more pointers to form a chain or a tree (this is how a VARCHAR(MAX) column can have  values that are obviously much larger than 8060 bytes). Such a large value will consist from many individual records, each of 8060 bytes or less. None of these record will ever span multiple pages.

And I did not even touch columnstore index rows, which are completely different.


How can I improve performance of update query in PostgreSQL

In general mass-updates like this can take a long time, independent of the RDBMS used.

Often a lot of time can be saved by running the update in batches of a few thousand rows at a time. Something like:

UPDATE schema.table set ...
 WHERE id >=0 and id < 10000

UPDATE schema.table set ...
 WHERE id >=10000 and id < 20000

...

Instead of manually typing the different update statements you certainly can use a loop too. Just make sure that there is no overarching transaction, as that would negate the effort.


Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016)

If you use variables or parameters in the Conditional Split, you can log the values of these.  There are several ways to do this, either insert the values into a custom logging table or use a Script Task to capture these.  The following example uses the Dts.Events.FireInformation method from a C# Script Task.  Under the Basic logging mode, this will be logged in the SYSSSISLOG table, assuming that you've chosen this as a log provider.

string taskName = Dts.Variables["System::TaskName"].Value.ToString();
string varName = Dts.Variables["User::YourVariableName"].Value.ToString();
int infoCode = 99;
bool fireAgain = true;
string loggedMsg = "Value of Conditional Split Variable:  " + varName;

Dts.Events.FireInformation(infoCode, taskName, loggedMsg, "", 0, ref fireAgain);


Is it better to populate variables using SET or SELECT?

Don't pick one or the other strictly for performance.

Pick based on what fits your style and development needs, as is advised in the conclusion to this excellent comparison between SELECT and SET (emphasis and minor formatting added):

  Best practice suggests not to stick to one method. Depending on the
  scenario you may want to use both SET or SELECT.

  Following are few scenarios for using SET:

  If you are required to assign a single value directly to variable and no query is involved to fetch value
  NULL assignments are expected (NULL returned in result set)
  Standards are meant to be follow for any planned migration
  Non scalar results are expected and are required to be handled

  Using SELECT is efficient and flexible in the following few cases:

  Multiple variables are being populated by assigning values directly
  Multiple variables are being populated by single source (table , view)
  Less coding for assigning multiple variables
  Use this if you need to get @@ROWCOUNT and @ERROR for last statement executed


How to avoid deadlock while updating 2 rows in MySQL

I don't like the interview question because in addition to the (presumably) correct SELECT ... FOR UPDATE on all rows, I suspect the other answers they're looking for may be something theoretically accurate but not necessarily the best idea (especially in a high-concurrency environment), such as:

use table level locking to force serialization of the transactions
use a semaphore mechanism such as GET_LOCK() and RELEASE_LOCK() to serialize the transactions with named locks... admittedly, this is a feature in MySQL that I really do find very useful (such as for preventing recurring scheduled events that run longer than expected from firing up parallel instances, and serializing the execution of unrelated low-priority background/maintenance jobs to keep resource utilization by the maintenance jobs at a minimum) but that I do not normally use for serializing live transactions.

@Chris Travers has already given what may be more useful real-world answers than these, but from my reading of the question, standardizing the order (#2) and using stored procedures (#3) aren't permitted modifications, perhaps, by definition... so I offer the speculation, above.


I'm not sure what are your reasons for wanting a hash index.

But if you are looking for indexing text/varchar columns then you can go with MyISAM/full-text or you can use something external like Sphinx or Lucene.

You can alternatively, create an additional column with a hashed version of another and as long as its 1000 bytes or less, then it can be indexed by MySQL.

I hope I have answered your question.


Make sure you are either connecting to the correct database (USE <database>) or using fully qualified names in your maintenance plan.


Change to

COUNT(DISTINCT user.id)

Else you are counting "purchases", not "users".


There is no perfmon counter that would you show you this information.  Why don't you just pull this directly from SQL Server (at least as how SQL Server sees it)?

select
    session_id,
    program_name
from sys.dm_exec_sessions
where is_user_process = 1;

If you're looking for data collection, you can periodically run this and store the output of the above query (or a variation of it, maybe with aggregating by program_name instead of grabbing session_id, depends on really what you're looking for) with a datetime stamp for subsequent querying.


Linked Server from SQL Server to SSAS not working - what is missing?

I found this article here.

There it states that:

To resolve that problem you have 3 options:

Run SQL queries from data server (you need to be remotely connected to the database server)
Enable use of Kerberos on the database server
Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that is issuing t-sql query:

Create the linked server:

EXEC master.dbo.sp_addlinkedserver
@server = N'SSASSERVER', -- name of linked server
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP', -- see list of providers available on SQL Server under Linked Server node in SSMS Object Browser
@datasrc=N'ServerName', -- machine or instance name  that host Analysis Services
@catalog=N'SimplifiedCube' -- Analysis Services database (cube)

and adding a linked server login:

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SSASSERVER',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'myDomain\Login',
@rmtpassword='########'

I have basically just done the number 3 and the linked server is working fine for me now.
As you can see on the picture below.

Also I had problems with the timing out so I had to change this setting as well:
(the default is 600)

EXEC sys.sp_configure N'remote query timeout (s)', N'6000'
GO
RECONFIGURE WITH OVERRIDE
GO

This can also be done through the GUI as per the picture below:


So it seems like you're missing the where clause which would filter out the undesirable results. In this example I'm assuming 2 is a decent threshold and using a between clause to limit the output. Greater than with a limit would also work but it really depends on the dataset.

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
WHERE
    length BETWEEN $input->length AND ($input->length + 2)
    AND width BETWEEN $input->width AND ($input->width + 2)

ORDER BY
    length_difference
LIMIT 2

Here's the example using greater than.

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
WHERE
    length > $input->length
    AND width > $input->width

ORDER BY
    length_difference
LIMIT 2


Pointing the database secondary data file towards a network drive

You can do it, it doesn't mean you should. There is many things to take into account, like performances and reliability.
Putting the files on the network will probably cause you some performance problems as data needs to be moved across the network for every read, insert, update, delete.
If you have historic data, then maybe you could consider putting those older, less accessed data on the network. You would have less performance issue if you don't access the data often.
Then you might have some data corruption, for example if SQL Server is writing to the database, but the network fails. You have to keep that in mind.
That being said, if you want to do it anyways, you basically have to enable trace flag 1807 at the startup of your SQL Server and then you'll be able to do it.
I recommend you read those two references for a detail of the risks involved :

Microsoft's Documentation
Brent Ozar's blog

To transfer part of the database to the new file on the network, you should do the following operations:

You add another filegroup to your database and it's location will be on the network.
You need to recreate the clustered indexes or primary key clustered of the data that you wish to move to the network on the new filegroup.


The documentation states

  On thread startup, the session wait_timeout value is initialized from
  the global wait_timeout value or from the global interactive_timeout
  value, depending on the type of client (as defined by the
  CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also
  interactive_timeout.

Since you are connecting via the command line, rather than a script, it makes sense that the CLIENT_INTERACTIVE is being used as a connection option automatically. As such, the interactive_timeout value is being used, which defaults to 28800.

To make the session wait_timeout value be 180, update the interactive_timeout value to be 180.

As for why that is so, I can only speculate. However, as a programmer, I know it is more useful to only have to look up one value whenever checking to perform a certain action, rather than having to check multiple values, and make sure I'm always check all those values each time I want to check something.

More explicitly, in this case, every time the wait_timeout value is needed, it is best to just have to check that, rather than both wait_timeout and interactive_timeout. It helps keep code maintainable by having the session wait_timeout automatically be initialized with the appropriate value, and then have to only check that one value. To do otherwise invites mistakes, by potentially failing to check both values every time something needs to be done that involves this timeout.


As long as the table is empty, there is no problem. As long as new values for ENUM is appended and not renamed given a populated table, again no problem.

The ENUM you redefined in your question actually kept the original internal values for yes and no as the test table last remembered it.

The following applies to populated tables:

What about this?

ALTER TABLE  `test`
CHANGE  `bool`  `bool` ENUM(  'no',  'yes',  'maybe' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

Now you have a problem. The ENUM values in a fully populated table would have their internal values reversed so that yes is now no and no is now yes.

What about this?

ALTER TABLE  `test`
CHANGE  `bool`  `bool` ENUM(  'maybe', 'no',  'yes' )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL

Big Problem. In a populated table, yes is now maybe. New rows inserted with yes are disconnected from the previous yes rows because they now mean maybe.

SUMMARY

There are very high-risk, bait-and-switch techniques to do this very quickly in MyISAM. I would strongly advise against doing this in InnoDB because of its tablespace id interaction with ibdata1.


Create stored procedure inside try-catch block

From the CREATE PROCEDURE documentation:

  The CREATE PROCEDURE statement cannot be combined with other
  Transact-SQL statements in a single batch.

If you need a try/catch, you'll need to execute the DDL using dynamic SQL so that it is in a separate batch:

BEGIN TRY
    DECLARE @CreateProcedureStatement nvarchar(MAX) =
N'CREATE PROCEDURE [ammeghezi1] (@w INT) AS SELECT ''''';
    EXEC sp_executesql  @CreateProcedureStatement;
END TRY
BEGIN CATCH
    THROW;
END CATCH
GO


processlist showing '%' as host from trigger

Are you doing the insert that fires the trigger from inside a stored procedure?

Apparently, when inside a stored procedure, the row matching your thread in information_schema.processlist is populated (albeit somewhat half-heartedly) with the DEFINER username and hostname if the calling user is not the same as the definer.

This is somewhat unexpected, so I'll document it a bit more, below...

But first, your fix:

SET NEW.hostname = SUBSTRING_INDEX(user(),'@',-1);

If this works, it's definitely a better approach -- it's a much more lightweight way to get the information you're looking for, and seems to return the correct answer where the other doesn't.  Making the call to information_schema.processlist is expensive, since apparently the entire table is rendered each time you call for it.

Now, to replicate the behavior for the benefit of those who (me included) whouldn't have believed it without seeing it in action:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bizarre` $$
CREATE DEFINER=`super_dev`@`%` PROCEDURE `bizarre`()
BEGIN

SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();

END $$

DELIMITER ;

Now to test the SP.  Note that I am not connecting as super_dev, I am connecting as a different super user.  If I connect as super_dev (the DEFINER user), this works the way we expect.

mysql> call bizarre();
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
| ID    | USER          | HOST    | DB         | COMMAND | TIME | STATE     | INFO                                                                    |
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
| 17261 | super_dev     | %:49730 | dev_testsv | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
+-------+---------------+---------+------------+---------+------+-----------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

My best guess, this is an artifact of the environment changes that need to happen internally when a stored procedure is called.


This should do the trick:

select nspname, relname, relkind, reltuples from pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where relkind in ('r', 'v');

Note that the reltuples is an estimate of the rows not the actual count at the moment but generally it's quite accurate (depends on how up to date your statistics are).


SQL Performance Large Variable Varchar into Small Column

Is performance really your main concern here? Or are you just looking for an excuse to not bother making sure the parameter data types match the column data types? (This happens to be a common reason for just declaring everything as nvarchar(max).)

Your parameters should absolutely match the underlying column types. If one changes, you change both.

Declaring larger data types for variables that will never hold that much data is probably wasteful, performance-wise, since memory has to be reserved in case you update the variable later. But this performance difference (if it even exists) is completely irrelevant, IMHO.

The reason? If someone puts 26 characters into the first variable, the insert will fail. And the error message is useless:

  Msg 8152, Level 16, State 14

  String or binary data would be truncated.
  The statement has been terminated.

Imagine you have 10 columns of varying lengths, and 10 varchar(4000) parameters, and then you call this stored procedure 100 times, and get the error 20 times? Happy hunting!

Why invite errors that are preventable (or that can at least happen further up the chain)? You should be validating your input all the way up the stack:

Make sure the form field can't be more than 25 characters (HTML supports this);
make sure the client-side validation check that it is not more than 25 characters (JavaScript supports this);
make sure the .NET data type is declared as 25 characters; and, for the love of all things you deem holy,
make that parameter 25 characters.

Yes, that makes for more work when the length changes, but anything else is inexcusable IMHO.


Postgres slow select query on another table after other table alter and update

There is no reasonable way to determine why a seqscan is slower shy of bloat and dead rows. Removing the dead rows is simple. Try VACUUM FULL cofk_union_queryable_work.
Consider indexing the columns to avoid the seq scan entirely. When you say something like this "this was a large (around 100'000) update for the system, which only usually has small changes" That's a clue there should be indexes. The only reason not to index, is to save space and write/speed. If you're not writing, index away! Feel free to \d table and update your question if you need help.
Your version of PostgreSQL is olldddd. 9.1 was released on 2011-09-11.  That's Shark Night 3D old

Most of this stuff was said by @a_horse_with_no_name, like usual, and in the comments. Also, like usual.


Optimal way(performance wise) to store array like data in MySQL

If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.

If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.

Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.


Setting up an alert to monitor where/when your databases are backed up

You can use a BackupRestore Event Notification to monitor backup events and this solution even works on Sql Express (where the Sql Agent doesn't exist). This link shows you how to set up SQL Mail for SQL Server Express.

I created a database called Test.

I modified this example from Jonathan Kehayias, which was tracking autogrowth, to instead audit the backup/restore event.

Here is my example that was tweaked from Jonathan's:

-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO

-- Drop the notification if it exists
IF EXISTS (
        SELECT *
        FROM sys.server_event_notifications
        WHERE NAME = N'CaptureDatabaseBackupEvent'
        )
BEGIN
    DROP EVENT NOTIFICATION CaptureDatabaseBackupEvent ON SERVER;
END

-- Drop the route if it exists
IF EXISTS (
        SELECT *
        FROM sys.routes
        WHERE NAME = N'DatabaseBackupEventRoute'
        )
BEGIN
    DROP ROUTE DatabaseBackupEventRoute;
END

-- Drop the service if it exists
IF EXISTS (
        SELECT *
        FROM sys.services
        WHERE NAME = N'DatabaseBackupEventService'
        )
BEGIN
    DROP SERVICE DatabaseBackupEventService;
END

-- Drop the queue if it exists
IF EXISTS (
        SELECT *
        FROM sys.service_queues
        WHERE NAME = N'DatabaseBackupEventQueue'
        )
BEGIN
    DROP QUEUE DatabaseBackupEventQueue;
END

--  Create a service broker queue to hold the events
CREATE QUEUE [DatabaseBackupEventQueue]
    WITH STATUS = ON;
GO

--  Create a service broker service receive the events
CREATE SERVICE [DatabaseBackupEventService] ON QUEUE [DatabaseBackupEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [DatabaseBackupEventRoute]
    WITH SERVICE_NAME = 'DatabaseBackupEventService'
        ,ADDRESS = 'LOCAL';
GO

-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureDatabaseBackupEvent] ON SERVER
    WITH FAN_IN
FOR AUDIT_BACKUP_RESTORE_EVENT TO SERVICE 'DatabaseBackupEventService'
    ,'current database';
GO

--  Alter the queue to use the activation procedure
ALTER QUEUE [DatabaseBackupEventQueue]
   WITH STATUS=ON,
      ACTIVATION
         (STATUS=ON,
          PROCEDURE_NAME = [ProcessDatabaseBackupEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

I then tweaked his example stored procedure that consumes the event.

Make sure you change this line in the stored procedure to be your email address

@recipients = 'YourEmailAddress'

DROP PROCEDURE [dbo].[ProcessDatabaseBackupEvents];
GO

CREATE PROCEDURE [dbo].[ProcessDatabaseBackupEvents]
    WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);

WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;

    -- Receive the next available message FROM the queue
    WAITFOR (
            RECEIVE TOP (1) -- just handle one message at a time
            @message_body = CAST(message_body AS XML) FROM dbo.DatabaseBackupEventQueue
            )
        ,TIMEOUT 1000;-- if queue empty for 1 sec, give UPDATE AND GO away

    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION;

        BREAK;
    END

    DECLARE @EventType VARCHAR(128);
    DECLARE @ServerName VARCHAR(128);
    DECLARE @PostTime VARCHAR(128);
    DECLARE @DatabaseName VARCHAR(128);
    DECLARE @TextData VARCHAR(max);
    DECLARE @GrowthPages INT;

    SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)')
        ,@TextData = @message_body.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)')
        ,@ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)')
        ,@PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS VARCHAR)
        ,@DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)');

    -- Generate formatted email message
    SELECT @email_message = 'The following DatabaseBackup event                                      occurred:' + CHAR(10) + CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + @TextData

    -- Send email using Database Mail
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,
        -- your defined email profile
        @recipients = 'YourEmailAddress'
        ,-- your email
        @subject = 'DatabaseBackup Event Notification'
        ,@body = @email_message;

    --  Commit the transaction.  At any point before this, we could roll
    --  back. The received message would be back on the queue AND the
    --  response wouldn't be sent.
    COMMIT TRANSACTION;
END
GO

Then, I ran a simple backup

BACKUP DATABASE [Test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT
    ,INIT
    ,NAME = N'Test-Full Database Backup'
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10
GO

After a minute or so, I received the email with the event data in it. You can modify the event consuming stored procedure to capture whatever you need and send that information along in the email.


Maria/MySQL Possible to allow user to GRANT drop though not use DROP?

True, you cannot use the GRANT command to give away grants you don't own as stated in the MySQL Documentation under the GRANT privilege:

  The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

The only alternative would be to create a user that have the power to the following:

INSERT IGNORE INTO mysql.db SET
    db = '...',
    user = '...',
    host = '...'
;
UPDATE mysql.db SET Drop_priv = 'Y' WHERE ... ;`
FLUSH PRIVILEGES;`

The INSERT IGNORE adds the initial entry into mysql.db in case it did not already exist. Then, the UPDATE sets the drop_priv column to 'Y'. FLUSH PRIVILEGES must be done manually is needed since no GRANT command is ever executed (GRANT always executes FLUSH PRIVILEGES; automatically)

Create a Stored Procedure that accepts a database, user and host and executes these commands.

For the sake of example, suppose the following

Database is mydb
User is 'john'@'10.20.30.40

The commands to run would be

INSERT IGNORE INTO mysql.db SET
    db = 'mydb',
    user = 'john',
    host = '10.20.30.40'
;
UPDATE mysql.db SET Drop_priv='Y' WHERE db='mydb' AND user='john' AND host='10.20.30.40';
FLUSH PRIVILEGES;

Place these commands in a stored procedure

First, login as root@localhost, then run the following:

USE mydb
DROP PROCEDURE IF EXISTS GiveAwayDrop;
DELIMITER $$
CREATE PROCEDURE GiveAwayDrop (givenDB VARCHAR(64),usr VARCHAR(64),hst VARCHAR(64))
BEGIN
    INSERT IGNORE INTO mysql.db SET
        db   = givenDB,
        user = usr,
        host = hst
    ;
    UPDATE mysql.db SET Drop_priv='Y' WHERE db=givenDB AND user=usr AND host=hst;
    FLUSH PRIVILEGES;
END $$
DELIMITER ;

You must give away the EXECUTE grant to the user, so that user can run stored procedures:

GRANT EXECUTE on mydb.* TO 'john'@'10.20.30.40';

See my old post how to GRANT EXECUTE on MySQL for further clarification.

Then, user 'john'@'10.20.30.40' can execute

CALL mydb.GiveAwayDrop('givendb','fred','10.20.%');

to give out the DROP privilege to 'fred'@'10.20.%'. This should work if givendb database exists and 'fred'@'10.20.%' already exists in mysql.user.

GIVE IT A TRY !!!


How to see if index is being read "ordered" from DMVs

That data in the query plan only indicates whether the query plan requires on an ordered scan, or whether an allocation-order scan would work too.  Even if the query plan does not require an ordered scan, you normally get one anyway, as allocation-order scans are only allowed under specific circumstances, as Paul White explains all here.
So since this data is about the query plan, not the scan method, it's only available in the query plan.


Replacing the removed database diagrams feature

Update to the latest version of SSMS. The database diagrams feature was reinstated starting with version 18.1.


How to compare two SQL Server 2008 configurations

You really just have to get creative.  As we all know, there are many places that settings are stored, depending on what exactly you're looking to compare.  For instance, to compare instance-wide configuration settings, you can simple do an EXCEPT query (you may have to create a linked server, or export/import the data depending on how you want to approach that):

select *
from [YourProdInstance].master.sys.configurations

except

select *
from [YourDevInstance].master.sys.configurations

Use this same type of methodology for all other configuration settings that you will want to compare.

Find the root of the config data
Get both instances' config data in a common place
Write a query that will compare the data (whether it is XML, or not)


How to connect via config file in mongodb

Solution

To avoid the error like: not master and slaveOk = false need always using the command: rs.slaveOk().
To avoid typing rs.slaveOk() every time need to add rs.slaveOk() in .mongorc.js file check here and here
More detail information.


Is there a way to partially initialize a TYPE?

Code examples based on your type definition:

CREATE TYPE my_type AS (name text, street text, location text);

Your syntax is not possible (as you already know). A plain cast does not allow a (partial) list of fields.

SELECT ROW('a', 'c')::my_type(name, location);

In a plain SQL cast expression, you have to provide NULL values (or some other default) for missing fields:

SELECT ROW('a', NULL, 'c')::my_type;

Or, using a row type literal as input:

SELECT '(a,NULL,c)'::my_type;

INSERT or UPDATE statements can target fields of composite types individually. (The underlying type is derived from the target.) Demo:

CREATE TABLE tbl (tbl_id serial, comp my_type);

INSERT INTO tbl (comp.name, comp.location) VALUES ('a', 'c')

UPDATE tbl
SET    comp.name = 'X'
     , comp.street = 'Y'
WHERE  tbl_id = 1;

Fields not filled explicitly default to NULL. (Unless the type has a different default, which would be uncommon.)

Not possible for DELETE, obviously, which always removes the whole row. The logical equivalent would be to set a subfield NULL:

UPDATE tbl
SET    comp.name = NULL
WHERE  tbl_id = 1;

Normally you would use variables in a procedural language function - default PL/pgSQL. There you have SELECT INTO. And you can address fields of a composite type individually. Demo:

DO
$$
DECLARE
    _var my_type;
BEGIN
   SELECT INTO _var.location, _var.name  -- in any chosen order
          'c', 'a';
   RAISE NOTICE '%', _var;
END
$$

db<>fiddle here

Do not confuse PL/pgSQL assignment with SELECT INTO and the SQL command SELECT INTO (which should not be used at all, use CREATE TABLE AS instead). See:

SELECT INTO with regexp_replace() doesn't write changes into newly generated table

Related, more sophisticated tricks:

Casting NULL type when updating multiple rows
How to set value of composite variable field using dynamic SQL
(demonstrating the hstore #= operator)


Understanding of Oracle multi-tenant PDBs

It simply means a PDB looks like a normal database instance. When you log into a PDB you only see the users and their database objects which belong to that PDB. The CDB is the actual sever instance, and it has the objects it needs for maintaining the server. In the CDB you can log in, and the tables there have a new column to mark the records associated with a specific PDB.


postgresql - transaction log - versioning?

you should setup point in time recovery. take a look,
http://www.postgresql.org/docs/current/static/continuous-archiving.html


MySql - alternate of auto_increment on multiple columns

You could use a trigger.  The key problem there though is that it will have to do a lookup and so multiple concurrent inserts into the table may get messy (concurrent being within the life of the transaction).

A real alternative here is probably going to go with a natural candidate key (multiple columns of actual rather than artificial data) and add a single autoincrement key for easy joins.  Having a single column reference makes joins to the table a lot easier.

Those probably aren't the answers you want to hear but they should work.  If they won't work, then the question is why and what data modelling scenario you are trying to address.


reading input parameter values in stored procedures

Look here for a possible answer to this question.

https://dba.stackexchange.com/questions/73931/prevent-sql-injection-inside-stored-procedure

But the answer there is also a follow-up question.


How to find inputs that caused different query plans in Query Store

If you want to do this within the Query Store user interface, you can look at the compiled value for each of the plans to see which parameter values led to different plans:

click on one of the colored circles in the plan summary from your screenshot
right-click the graphical execution plan that appears at the bottom and choose "Properties"
expand the "Parameter List" node in the properties pane

Here I'm looking at the compiled values that led to the "green" plan.  Clicking on the "orange" plan will show the compiled values for that one.

If you prefer not to use the user interface, you can get the plans directly from the Query Store tables using the method described by Max Vernon here: Does SQL Server Query Store capture parameter values?

As Aaron Bertrand mentioned in the comments:

  And plans can change for a variety of reasons, such as a significant data change that invalidates statistics. Some other relevant info here and here.

A new plan can get compiled for a number of different reasons (including statistics updates, manual plan clearing, adding up or rebuilding indexes, etc).

In the case in my screenshot, I can see that the new plan for this query showed up at 12:18 pm on 2/9.  Looking at the OptimizerStatsUsage node of the execution plan properties, I can see that several of the statistics used by this query were updated just before that, so this is why I got the new plan:

  which is the process for which SQL Server chooses different plans depending on the parameters values? I would like to read more about that.

The process that SQL Server uses to generate a specific plan based on different input parameters is called "parameter sniffing."  It's a really complex topic that you can read a lot more about it in Erik Darling's answer here:

Why is my query suddenly slower than it was yesterday?

There are some very comprehensive discussions about the topic of parameter sniffing in the "learn more" links at the bottom of that answer.


how do I determine why I cannot drop a schema

The root of the problem is that I have a schema-only database defined that is generated automatically via triggers. When a DDL statement gets replayed by the INSERT trigger of the changelog table, the error occurs when the schema is out of sync (The schema did not exist in my SchemaOnlyDatabase). The error was being thrown in the context of the SchemaOnlyDatabase, not the database that was currently active TheDevDatabase, but that was not clearly indicated by the message.

CREATE TRIGGER [RecordDatabaseChanges]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
--ALL SETTINGS NEEDED FOR XQUERY IN DDL TRIGGERS
--http://tomaslind.net/2013/10/15/zero-footprint-serverdatabase-ddl-trigger/
SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @data XML;
SET @data = EVENTDATA();
DECLARE @EventType NVARCHAR(30)
SELECT @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)')
IF @EventType IN (
N'ADD_ROLE_MEMBER',
N'ALTER_AUTHORIZATION_DATABASE',
N'ADD_SIGNATURE_SCHEMA_OBJECT',
N'DENY_DATABASE',
N'DROP_ROLE_MEMBER',
N'GRANT_DATABASE',
N'REVOKE_DATABASE',
N'UPDATE_STATISTICS'
)
RETURN
INSERT INTO [ChangeLogDb].[dbo].[ChangeLog] ([Event Type], [Database], UserName, SPID, [Schema], Name, [New Name], ObjectType, [New ObjectType], [SQL Used])
SELECT
COALESCE(@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)'),'') AS [Event Type],
COALESCE(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)'),'') AS [Database],
COALESCE(@data.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(128)'),'') AS [UserName],
COALESCE(@data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(128)'),'') AS [SPID],
COALESCE(@data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(128)'),'') AS [Schema],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(128)'),'') AS [Name],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(128)'),'') AS [New Name],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(128)'),'') AS [ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectType)[1]','nvarchar(128)'),'') AS [New ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),'') AS [SQL Used]
END

CREATE TABLE [ChangeLogDb].[dbo].[ChangeLog]
(
[Changelog_ID] [int] IDENTITY(1,1) NOT NULL,
[Event Type] [nvarchar](30) NOT NULL,
[When] [datetime] NOT NULL CONSTRAINT [DF_ChangeLog_When]  DEFAULT (getdate()),
[Database] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](128) NOT NULL,
[SPID] [nvarchar](128) NOT NULL,
[Schema] [nvarchar](128) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[New Name] [nvarchar](128) NOT NULL,
[ObjectType] [nvarchar](128) NOT NULL,
[New ObjectType] [nvarchar](128) NOT NULL,
[SQL Used] [nvarchar](max) NOT NULL,
[NTUserName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_NTUser]  DEFAULT (suser_sname()),
[HostName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_HostName]  DEFAULT (host_name()),
[SessionLoginName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_SessionLoginName]  DEFAULT (suser_name()),
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED ([Changelog_ID] ASC)
)

AFTER INSERT AS
BEGIN
DECLARE @sqlbatch NVARCHAR(MAX)
DECLARE @databasename VARCHAR(255)
DECLARE sqlused_cursor CURSOR FAST_FORWARD FOR
SELECT i.[DATABASE], i.[SQL Used] FROM inserted i WHERE i.[Database] = 'TheDevDatabase'
OPEN sqlused_cursor
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
WHILE @@FETCH_STATUS = 0
BEGIN
-- re-execute in the context of the TheSchemaDatabase db.
EXEC TheSchemaDatabase.dbo.sp_executesql @Stmt=@sqlbatch
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
END
CLOSE sqlused_cursor
DEALLOCATE sqlused_cursor
END


What is the best storing device for DB transaction log?

You really need a fast HDD, but you need a properly sized innodb_log_file_size.

Why not SSD for MySQL ? I learned something from this layout from a FaceBook Engineer's blog

I wrote old posts about this

Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 12, 2014 : Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I even mentioned doing this for PostgreSQL : Postgres Write Performance on Intel S3700 SSD

BTW See my post MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined? for determining the right size for innodb_log_file_size. You don't want a small HDD for ib_logfile0 and ib_log_file1.

Give it a Try !!!


how to sucessfully revoke view any definition (or any other server permissions)?

I believe this is because when you ran:

GRANT VIEW ANY DEFINITION TO [mycompany\webDevSeniors]
GO

Grant was given to that particular user. But when you tried to revoke, that was at the schema level.(2nd run). However when initially you gave the GRANT access , it was not specified at object level.Since REVOKE has no affect if the specified permission does not already exist.

I believe starting with creation of user and then assigning the roles accordingly can help here.

Please read Understanding GRANT, DENY, and REVOKE in SQL Server which will help you in this scenario.


sp_executesql processes uniqueidentifier-param with EntityCommandExecutionException but still acts like expected

You use an API that expects a result set (SqlQuery<...>(...)) but your stored procedure produces no result set. Hence the error message. The error occurs on the client side, after your procedure has executed, to explain the 'magic' you see.

You have several alternatives:

Use a non-query API, like ExecuteSqlCommand
have your stored procedure produce a result set, using the OUTPUT clause of INSERT.
Map the stored procedure in EF


Automate SQL Server install and restore DB

A great choice is dbatools. They have a command (among many, many others) that can help you with this:

Restore-DbaDatabase

An example,

Restore-DbaDatabase -SqlServer server1\instance1 -Path \\server2\backups\$

It can do a lot more complicated stuff, too! Check out the documentation for more info.


last modified rows timestamp for SQL Server 2016

Noting done by default. You can turn on stuff like temporal (probably overkill) or create a trigger to update the table.
SQL Server keeps track of all modification internally, in the transaction log, for its own purposes (like rollback and crash recovery). But the log is volatile and even if you get there before the info is gone, you'd have to decode the info yourself using unsupported means (like fn_dblog).
[Edit] Adding in the options Change Tracking (lower overhead) and Change Data Capture (higher overhead and uses an Agent job but gives more info than CT), as suggested in the comments below by Dominique.


The line that is failing is:

Select top 10 FROM @teststring

a) You're missing the column names (or indeed *)

b) What you're trying to do is not possible unless you use dynamic SQL

You use it in the exact way you are already using it.

-- Declare Table Name
DECLARE @tablename AS nvarchar(10);
SET @tablename = 'MyTestTable';

-- Execute a query
DECLARE @query_a AS nvarchar(500);
SET @query_a  = 'SELECT TOP 10  * FROM dbo.' +  @tablename;
EXECUTE sp_executesql @query_a;

-- Execute another query
DECLARE @query_b AS nvarchar(500);
SET @query_b = 'UPDATE dbo.' + @tablename + ' SET col_a = ''xyz''';
EXECUTE sp_executesql @query_b;

Warning: If you are inputting the tablename from a GUI, you need to sanitise the data to avoid SQL Injection attacks (https://msdn.microsoft.com/en-us/library/ms188001.aspx)

N.B. Use an ORDER BY when using TOP


mysqldump - does it have non-repeatable DMLs?

2TB restore from mysqldump?? How long do you have?

mysqldump  produces logical backup and it'd take longer to restore. Not scaring but I'd not dare to dump a 2TB database (unless you must)! I'd rather consider taking hotbackup.

About your other questions:

mysqldump will generate simple D[MD]L queries... create table, insert, create triggers/routines etc..
If mysqldump stops in between your dump will be incomplete, just like an incomplete SQL file!
You  can always check mysqldump finished correctly by checking last line of the output file, should say something like "Dump complelted..."
You may also check "$?" on shell  to confirm if the mysqldump command completed successfully.
About your progress, mysql internally doesn't report progress but from linux shell you can use (pipe viewer) pv command to know the progress.

Also:

Do refer mysqldump reference from documentation.
There existst mysqldumpsplitter to split from large dumpfiles if required.
If you consider to setup physical backup, setup instructions for xtrabackup+holland framework are there too.


This is how you should have mention in the question:

Create Table Tracker
(LocationID int,
RequestID int,
TrackingDatetime datetime,
Status varchar(30));
go

Insert into Tracker
values
(1, 22, '2019-06-17 14:10:00', 'Stop'),
(2, 22, '2019-06-17 14:08:00', 'Driving'),
(3, 22, '2019-06-17 14:06:00', 'Driving'),
(4, 22, '2019-06-17 14:04:00', 'Resume'),
(5, 22, '2019-06-17 14:02:00', 'Pause'),
(6, 22, '2019-06-17 14:00:00', 'Driving'),
(7, 22, '2019-06-17 13:58:00', 'Driving'),
(8, 22, '2019-06-17 13:56:00', 'Start');

Following query that you can start with for desired result (for test..):

WITH T as (
            select  LocationID,
                    RequestID,
                    TrackingDatetime,
                    CASE    WHEN Status = 'Resume'
                            Then TrackingDatetime
                            Else LEAD(TrackingDatetime) OVER (PARTITION BY RequestID ORDER BY TrackingDatetime desc)
                    END as PrevTrackingTime,
                    Status,

                    ISNULL(DATEDIFF(minute,
                                (CASE   WHEN Status = 'Resume'  Then TrackingDatetime Else LEAD(TrackingDatetime) OVER (PARTITION BY RequestID ORDER BY TrackingDatetime desc) END),
                                 TrackingDatetime
                            ), 0) as Duration_Minutes
            from Tracker
            )
select *, SUM (Duration_Minutes) over (order by TrackingDatetime) as Duration_Cumm
from T


decrement all columns entry when row is deleted

If the objective is to be able to sort the rows according to row_number then don't bother updating values on a delete.  The rows will come out in the same order after a row is deleted as they would before.  It is not worth the effort to close up the gaps.  In the worst case, if you delete the lowest-numbered row, you will have to update every remaining row in the table, in a transaction, with locking and all the concurrency problems that involves.  Don't do it.

If you absolutely must have some visual indication to users then let the application UI produce one, or use a function like ROW_NUMBER() at run time (SQL Server; other RDBMSs have similar).