Integrating intranet web logins with AD emails

It would appear that the responsibility of sending the email lies with the ASP.NET application not the database.

I would perform a lookup of the users email from AD within the ASP.NET application and generate the email that way.


You should use the open source stored procedure sp_BlitzLock to analyze these deadlocks.  It will give you what you're looking for in terms of timing, which tables / objects are involved in deadlocks most often, etc.

I downloaded your .xel file and ran the proc like this:

EXEC master.dbo.sp_BlitzLock
    @EventSessionPath = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Log\DL_0_132091861398080000.xel';

As an example, the output tells me that B7R2_CHKVN.dbo.B331GeneralLedger was involved in 217 deadlocks captured in this event session (out of the total 383 deadlocks for that database).

Adding the @ObjectName = 'B7R2_CHKVN.dbo.B331GeneralLedger' parameter to the procedure call filters things down to just deadlocks that involved that object.

Note that the database names are a little wonky because it's pulling the names by id, and my instance has different databases than yours

This will let you start going through each deadlock and seeing if you can find patterns of the queries involved.  Consider loading the deadlock XML (the last column of the first resultset) in SentryOne Plan Explorer in order to better visualize the locks being taken.


Partitioning a large table has not improved performance, why?

You generally followed a delusion - that partitioning will give you a significant performance boost for queries compared to a standard index.

This is not the case. There is little difference between filtering with an index and with a partition.

Partitions are not there to make your queries faster, but to allow fast DELETE - by swapping out a partition with an empty version of the table. This allows "truncate" style performance for deleting parts of a table - and that is significant. Brutally significant, if you ever experience the long time it may take to delete dozens of gigabytes of data.

There are also insert scenarios where a partition can help.

But for queries - no, the partition will not be better than proper indices. In fact, it will be slower - as there the work is more complex (management of which partitions to access).


SQL Server 2014 Batch Transaction Log Management

Prior years during batch in Sybase the transaction logs would fill up freezing the system.

Sybase before ASE 15 did not have the concept of autogrowth. SQL server has autogrowth and there are ways wherein you can presize your transaction log and choose a sensible autogrowth value.

Refer to : 8 Steps to better Transaction Log throughput and Manage the Size of the Transaction Log File

Also, Why Does the Transaction Log Keep Growing or Run Out of Space? has excellent answers detailing majority of techniques and scenarios.

  I would propose sizing the transaction log appropriately and running log backups every 15 minutes.

Initial sizing would be a trial and error method, but you can use my script to detect autogrowth events and then adjust your log size.

Running log backups will allow you to reuse your existing log file in FULL and bulk logged recovery modes.

A checkpoint (manual or automatic) will clear the log file in simple recovery.

  heavy batch at night.

You should refer to data loading performance guide (Referenced in my answer) to optimize your nightly load batch. Also, you should check Recommended updates and configuration options for SQL Server 2012 and later versions with high-performance workloads


Percona Audit Log plugin not auditing account

The resolution is SET GLOBAL audit_log_include_accounts='someuser@'; - without host part of definition, BUT with @


SQL select with unique_list - losing an item

There are two ways: first - using regexp_replace like here - Eliminate duplicates in ListAgg (Oracle), second - use distinct inside a subquery:

select fk_proddes_id,fk_id_cod_deseu,
       listagg(to_char(cod_eliminare), ',') within group (order by cod_eliminare desc)
  from (select distinct fk_proddes_id,fk_id_cod_deseu, cod_eliminare
          from sim_sd.proddes_ag_elim)
 group by fk_proddes_id, fk_id_cod_deseu


How to troubleshoot/debug Oracle permission problems

Another option (short of full auditing) is a servererror trigger for the schema in question (or the whole database).

drop table error_log;
create table error_log (error_time timestamp, username varchar(50), msg varchar(4000), stmt varchar(4000));

CREATE OR REPLACE TRIGGER servererror_trigger
  AFTER SERVERERROR
  ON SCHEMA
declare
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  insert into error_log
  (error_time, username, msg, stmt)
  values
  (current_timestamp, ora_login_user, msg_, stmt_);
end;
/

If it's for a single schema/user the trigger needs to be created under that user. You also can trap errors for the whole database by specifying ON DATABASE. In that case you'll need to create that trigger and the table as DBA I assume (haven't tried that).

Now nearly all errors that happen will be recorded by the trigger (see the manual for a list of errors that will not be reported: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS1992)


You should be able to turn the decode into a case:

UPDATE
  [SCHEMA].TLS_FAC_PRC_ONG
SET
  FPO_RID_OWNER = CASE FPO_RID_OUTSTANDNG
                    WHEN 'NONE' THEN FPO_PID_FACILITY
                    WHEN NULL THEN FPO_PID_FACILITY
                    ELSE FPO_RID_OUTSTANDNG
  FPO_CDE_OWNER = CASE FPO_RID_OUTSTANDNG
                    WHEN 'NONE' THEN 'FAC'
                    WHEN NULL THEN 'FAC'
                    ELSE 'OST'
                  END


After change to 13306, Galera logs error: Slave I/O: error connecting to master 'repuser@:3306'

You look like you are using classical async master/slave replication on a galera server. Was this intentional? Galera uses a different replication protocol and master/slave off, or especially between nodes, is odd.

Use CHANGE MASTER TO to change the master's port.


Barry Morris here from NuoDB:

You are correct that NuoDB can use S3 as it's primary storage.  In NuoDB terms you are configuring a Storage Manager for S3.

You can have as many other Storage Managers as you want, including others on S3, on a file system somewhere else, or on another supported Key/Value store.

All Storage Managers store the full database redundantly, so loss (or switching off) of Storage Managers will not cause data loss as long as one Storage Manager is still running somewhere in the system.

If you add an empty Storage Manager to a running database it will catch up to the current state of the database before taking part in database activity.  For a very large database this will not be the most efficient way to bring on a new Storage Manager, but it will work.

As relates to backup, you can adopt one of a few different strategies:
a) Do a conventional JDBC/ODBC/SQL level backup.
b) Take a Storage Manager offline, and rely on the fact that we can restart directly from that Storage Manager if needed.
c) Some people will feel that redundant storage in multiple storage managers is good enough and they don't need separate backups.

As for performance on S3 it will be load dependent.  If you want us to give you some estimates then let us know what you are doing and we can estimate it or run some tests for you.


PostgreSQL High Availability/Scalability using HAProxy and PGBouncer

Your existing configuration of HAProxy -> PGBouncer -> PGServer approch is better. And that only works.
Here is the reason: HAProxy redirects connection to different servers. this results in MAC address change in the database connection. So if PGBouncer is above HAProxy, each time the connections in the pool gets invalidated because of MAC address change.


You are running RTM (10.50.1600.1): the latest patch is SP2 + CU3 (10.50.4266)

These include patches like KB 2498786


MySQL error in where clause : Unknown column

Now, if you rewrite the query in a clear manner, you can see where's the troublesome column. From what I know of general SQL you can't use the alias of the column in the WHERE clause (but you can use it in the ORDER BY clause).

So you'd have to rewrite the query something like:

Select *
from (
    -- Your nice query without the have_discount filter
) as Aux
WHERE Aux.have_discount = 1
ORDER BY date_activated DESC
LIMIT 0,12


SSIS package to connect to HTTP source and download file

Your only option is going to be calling a script. Either using PowerShell script via Execute Process task, or using C# in a Script Task to download it.

For the PowerShell script you would use Invoke-WebRequest as it supports passing in credentials. A good example of this can be found here. The example from the article:

$Credentials = Get-Credential
Invoke-WebRequest -Uri "https://www.contoso.com" -OutFile "C:\path\file" -Credential $Credentials

To keep from being prompted you can use various ways to encrypt it, just depends on if the password has to be kept secure. If not just modify it to be something like this:

$pwd = ConvertTo-SecureString "MyT0pSecr3tP@ssw0rd" -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential (“username”, $pwd)
Invoke-WebRequest -Uri "https://www.contoso.com" -OutFile "C:\path\file" -Credential $creds


A common cause of this problem can be the difference between 32 bit and 64 bit installations.

If your SSIS package is running in 64 bit the Access driver needs to be installed in 32 bit too.

My guess would be that on your machine you installed the 64 bit driver, but on the server the 32 bit driver has been installed.

You could test this by executing the package from the 32 bit dtexec found here:

C:\Program Files (x86)\Microsoft SQL Server\<version>\DTS\Binn

And trying to execute it from the 64 bit dtexec found here

C:\Program Files\Microsoft SQL Server\<version>\DTS\Binn

See the documentation for more information dtexec Utility (SSIS Tool)

Then it's just a matter of installing the correct drivers (32 or 64 bit)


A Common Username for Both Database hosted in Azure

You can create Login in master database using syntax ..

CREATE LOGIN <SQL_login_name, sysname, login_name>
    WITH PASSWORD = '<password, sysname, Change_Password>'
GO

and then create user in individual databases using syntax

CREATE USER <user_name, sysname, user_name>
    FOR LOGIN <login_name, sysname, login_name>
    WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

GRANT SELECT  TO <user_name>

This can be done via SSMS from local machine or VM. I believe you can achieve this using Query Editor in Azure as it does not allow switching context to master database .


Script to Delete records in multiple table

You have to achieve this using Dynamic SQL Query

1- First you have to list all Tables With there Corresponding Databases in a Temp Table
2- Building Query for databases that contains Students and Winners dataTable

Your script should look like

--Get all Databases With corresponding Database

declare @SQL nvarchar(max)
DECLARE @strQuery AS NVARCHAR(MAX)

SET @strQuery = ''

CREATE TABLE #TblTemp (DatabaseName Varchar(255), Tablename Varchar(255))

set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
INSERT INTO #TblTemp
exec (@SQL)

-- Building Queries

SELECT @strQuery = @strQuery + 'Delete T1 from [' + name  + '].dbo.Students As T2
                        Inner join  [' + name  + '].dbo.Winners as T1
                        On T1.[st_ID] = T2.[ID]
                        Where    T1.[name] = IN(''Roy'',''Peter'')  ;

DELETE FROM [' + name  + '].dbo.Students WHERE [name] = IN(''Roy'',''Peter'') ;

'

 from sys.databases
 WHERE EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Students') AND
 EXISTS (SELECT 1 FROM #TblTemp WHERE #TblTemp.DatabaseName = name AND #TblTemp.TableName = 'Winners')

--VIEW QUERIES (you can copy result and execute it manually)
SELECT @strQuery

--EXECUTE QUERIES
EXEC(@strQuery)

--DROP Temp Table
DROP TABLE #TblTemp

This Will Result a query like the following (If these databases contains Students and Winners Table)

Delete T1 from [master].dbo.Students As T2   Inner join  [master].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [master].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [tempdb].dbo.Students As T2   Inner join  [tempdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [tempdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [model].dbo.Students As T2   Inner join  [model].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [model].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [msdb].dbo.Students As T2   Inner join  [msdb].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;
DELETE FROM [msdb].dbo.Students WHERE [name] = IN('Roy','Peter') ;
Delete T1 from [AdventureWorks2008R2].dbo.Students As T2   Inner join  [AdventureWorks2008R2].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;     DELETE FROM [AdventureWorks2008R2].dbo.Students WHERE [name] = IN('Roy','Peter') ;     Delete T1 from [DbMail].dbo.Students As T2   Inner join  [DbMail].dbo.Winners as T1    On T1.[st_ID] = T2.[ID]    Where    T1.[name] = IN('Roy','Peter')  ;

I don't Now if that is what you was asking for in your question below (Your question was not specific like this one, so my answer was providing the dynamic sql logic in general)

Truncate with where clause


As for the view being valid after dropping a column: The view is defined as SELECT * so the view is still valid.

Regarding the validity of the view after dropping the table just use field VALID from SYSCAT.VIEWS, see below.

db2 => DROP TABLE DB2INST1.TEMP_T
DB20000I  The SQL command completed successfully.
db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           N

  1 record(s) selected.

db2 => CREATE TABLE DB2INST1.TEMP_T ( COL_A  CHAR(5) )
DB20000I  The SQL command completed successfully.
db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           N

  1 record(s) selected.

db2 => SELECT * FROM DB2INST1.TEMP_V

COL_A
-----

  0 record(s) selected.

db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           Y

  1 record(s) selected.


Cannot insert record in remote database - Linked server

Ooo, bad news - this means you're using a trigger:

from ZTable Zh
inner join inserted

If you're trying to work in a trigger, that means there are other changes to other tables in your transaction. The "inserted" table you're using means that you're working with inserted records from another table.

This means you're trying to do a distributed transaction: committing multiple changes on different servers at the same time. This is a recipe for slow performance.

Take a step back and consider the business problem you're trying to solve. For example, if you're trying to log inserts, consider logging on the same table rather than trying to coordinate transactions across multiple SQL Servers. Or, consider using a built-in feature like Change Data Capture or Change Tracking.

There's a chance, depending on your SQL Server version, that it's fixed by this SQL Server 2005 issue, but even if it is - I'd recommend a different approach than triggers that call linked servers.


CSV would not have the cell ordinals you have specified. This happens only when you try to open your file in Excel. In order to emulate this, you will have to read the content of 2nd row and 2nd column.

This can be done using a Script Task in SSIS.


Insert record only if a combination of column values doesn't exist

MYSQL doesn't support such types of constraints.
You should use stored procedure for inserting data. So you can do some checking and validation data.


You can separate the logic into 3 parts:

first, for "normal" shifts, that don't include midnight
and second and third for shifts that have midnight.

The query:

-- time now
declare @tn as time = getdate() ;

select *
from ShiftMaster
where ShiftStartTime <= @tn and @tn < ShiftEndTime
   or ShiftEndTime < ShiftStartTime and @tn < ShiftEndTime
   or ShiftEndTime < ShiftStartTime and ShiftStartTime <= @tn
  ;

Test in dbfiddle.uk.

Please also note that I used closed-open intervals and comparisons (avoiding the evil BETWEEN*), so the shift ('00:15:00', '07:15:00') stands for any time just of after 00:15 and before 07:15 (but not exactly 07:15). So every point in time should be in one and only one shift, assuming that you define the shifts correctly.

insert into ShiftMaster
values
('SHF1', 'Shift A', 'First Shift',
             '07:15:00', '15:45:00'),   -- notice the starts
('SHF2', 'Shift B', 'Second Shift',
             '15:45:00', '00:15:00'),   -- and the ends
('SHF3', 'Shift C', 'Third Shift',
             '00:15:00', '07:15:00') ;

With your method, there would be gaps of 1 second between shifts.

*: More info and detailed explanation about why to avoid BETWEEN with datetime types on the excellent blog post by Aaron Bertrand: What do BETWEEN and the devil have in common?.


SQL Server 2016: sp_send_dbmail, Failed to initialize sqlcmd library with error number -2147467259

The problem is that you are a) using double-quotes instead square-brackets when specifying column names, and b) somehow the session setting of QUOTED_IDENTIFIER is OFF.

You can alter the stored procedure, BUT only after specifying the proper setting first. The setting is stored along with the Stored Procedure definition. You can see the setting (the uses_quoted_identifier column) by running the following:

SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID(N'sp_alert_nav_jobqueue_test');

Hence, do the following and then re-run the query above to see that the setting did change:

SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[sp_alert_nav_jobqueue_test]
AS
...

You should also change the double-quotes to be square-brackets. Even if it starts working with the correct session setting, square brackets always work and so are by far the better choice. You can see the difference in behavior below.

SET QUOTED_IDENTIFIER ON;
SELECT cast("Start Date_Time" as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

SET QUOTED_IDENTIFIER OFF;
SELECT cast("Start Date_Time" as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- error:
/*
Msg 241, Level 16, State 1, Line 210
Conversion failed when converting date and/or time from character string.
*/

SET QUOTED_IDENTIFIER ON;
SELECT cast([Start Date_Time] as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

SET QUOTED_IDENTIFIER OFF;
SELECT cast([Start Date_Time] as date) FROM (SELECT GETDATE() AS [Start Date_Time]) tmp;
-- works

Side note: It is also best to not name stored procedures starting with sp_ as that has special meaning in SQL Server. It causes SQL Server to first look in [master] for the stored procedure and then, if not found, it will check the current DB. Meaning: it is a performance hit unless it is in [master] and intended to execute this way.


Single DELETE statement deadlocking in Sybase ASE

Original post describes a classic deadlock scenario with a twist, namely, a single DELETE ... WITH statement, which acquires 2 different kinds of locks.

Recommendation - change table from All Page Lock (APL) to Data Row Lock (DRL) scheme

clustered index will be replaced by placement index separate from data page
index access / updates will use latches rather than logical locks
row-level deadlock conflict is less probably (than page-level's)

Both SPIDs are executing the same sproc with the 1st DELETE ... WHERE statement demanding 2 different kinds of locks:

initially an UPDATE lock on a page when scanning for a row to delete (UPDATE lock is compatible with shared locks only but not with another UPDATE lock or exclusive lock);
once a target row is found on a page, then the UPDATE lock must be upgraded to an exclusive lockbefore deleting that row.

The DEADLOCK events posted in your original question confirm the scenario :

SPID 134 has held an exclusive lock on a page while waiting to acquire an UPDATE lock on another page i.e. it has deleted at least one row already and is continuing with searching for another (row to delete);
SPID 166 blocks the first SPID's acquiring an UPDATE lock on a page while waiting to apply an exclusive lock on another page, which has an exclusive lock held by SPID 134 already (after deleting 1 row or more).


How performance schema in mysql 5.6 improve performance?

Please note the difference between the information_schema and performance_schema databases
INFORMATION_SCHEMA
The information_schema database is an inventory of all objects within the MySQL instance
Such objects include:

databases
tables
columns
constraints
indexes (called statistics)
processlist
locks

I wrote a nice post about this 3 years ago : How is INFORMATION_SCHEMA implemented in MySQL?
PERFORMANCE_SCHEMA
The performance_schema database is an instrumentation platform that collects statistics about phases of a running query. Such info can give you hints to tune the query or the environment. You only need to enable it when debugging and troubleshooting queries in conjunction with adding indexes (may or may not be necessary) and adjusting buffer settings (again, may or may not be necessary).
I have not written much about performance_schema since I do not use it that often (Mentioned in my old post Is there any way to get the estimate cost of executing a query in MySQL?).
Derek Downey also wrote a quick post on it : What is the usage of performance_schema in MySQL 5.5?
There is a wealth of information on how to use the performance_schema I think you will enjoy

PERFORMANCE_SCHEMA vs Slow Query Log
PERFORMANCE_SCHEMA in MySQL 5.6: The Good, The Bad and The Ugly
A Visual Guide to the MySQL Performance Schema


How to set up multiple fields as primary key in MySQL?

Use a compound primary key:

CREATE TABLE yourtable
  (
     employeeid  INT,
     blahblah    VARCHAR(255),
     blahblah2   VARCHAR(255),
     recordmonth DATE,
     recordyear DATE,
     PRIMARY KEY (employeeid, recordmonth, recordyear)
  )

And if your table already exists, drop the old primary key:

ALTER TABLE yourtable
DROP PRIMARY KEY;

And recreate it:

ALTER TABLE yourtable
ADD PRIMARY KEY (employeeid, recordmonth, recordyear);


Azure SQL - Server login vs. database login

Your login is mapped to different database user, most probably to dbo . You can check this by executing:

SELECT CURRENT_USER

You can also compare the output from following views (and find matching SIDs to figure out login to user mapping):

SELECT *  FROM sys.sql_logins -- you need to execute this on master azure db

SELECT * FROM sys.database_principals -- you need to execute this on the database of interest


For this you need to grant select permission to user (MySQL User) on that particular column of table.

GRANT SELECT (name) ON MyDb.User TO 'MySQLUser'@'MySQLHost';

For explanation have a look at MySQL Documentation on column Privileges


Well... So I ended up partially wipe the database and resync it again by reset master and drop database mysql_innodb_cluster_metadata. Now It works fine.

Still wondering if there's anyway to rewind gtid instead of full reset...


PostgreSQL CREATE EXTENSION file not found error on Mac OS

The problem is that the needed contrib files are not installed. This was actually the case for several contrib modules.

I found help with the solution to this here:
https://wiki-bsse.ethz.ch/display/ITDOC/Get+PostgreSQL+contrib+modules+on+Mac+OS+X

$ su
# port build postgresql92
# cd `port work postgresql92`/postgresql-<version>/contrib
# for d in *; do test -d $d && ( echo $d; cd $d; make all && make install; cd .. ); done

I suspect reinstalling everything would work too, but this highlights the actual problem.


dbcc checkdb runs for interminably long time, 50% of the time

Was there an error from CHECKDB for those occasions when it took so long? Anything useful in the errorlog file for those occasions?

If CHECKDB find an error, it might do a "deep dive" to further investigate (traverse the linked list for the index instead of just checking a bitmap, mentioned here: https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/).


Like you've already suggested, as soon as I got there I'd perform an inventory of all SQL Servers before then spending time running the latest version of sp_Blitz® – Free SQL Server Health Check Script and auditing the results.

Depending on the results I'd then probably look to get in and standardise Ola Hallengrens SQL Server Maintenance Solution. You can then concentrate on running checkDB, index maintenance and getting backups offsite, security (logins, linked servers) etc.

Once the data is safe, I'd then look to either bring in a third party monitoring solution or build a basic one myself. At a minimum, I'd start capturing wait stats every minute or so.


Get top SQL Queries that aren't stored procs/functions/triggers

There's an easy (and free!) way to do that using sp_BlitzCache (full disclosure, I contribute to this OSS).

You can run it with the @QueryFilter option (which can also get only stored procs, and only functions (but functions are only 2016+).

EXEC master.dbo.sp_BlitzCache @QueryFilter = 'statement'

That will give you your top 10 statements by overall CPU. If you want to order by other metrics, you can use the @SortOrder parameter to go by reads, duration, executions, and more.

Hope this helps!


How to manually set a return value to a subquery with multiple results

You could try something like this, add a select case to your subquery

select (select case when (select count(*) from subquery) >1 then 'duplicate' else 'not duplicate' end)

Though I'm not sure if I would recommend that, but there is one method.


Design a betting system for Fifa World Cup Russia 2018

I recommend that you work through the bets as follows:

First, determine what data you need to have for each type of bet. Then, look at what's common to all bets, and what only applies to some. Where possible, see if some things that appear different can be turned into something common. Last, look at where you connect to other tables, and figure out how to do that most effectively.

For example (I may get some of this wrong; not sure of how the World Cup matches all fit together):

All bets would involve:

a person making the bet
a winning team
a match of some sort (can you tell based on rounds, etc. what match would determine a group winner in advance, and what match will be the final match?)

Some bets involve:

Winning team's goals
Losing team's goals
120 minute match or not

So I would try to build a bet table that would allow for any type of bet. If score is irrelevant for some bets, ignore it. Treat the "penalty" bets as if they are separate from the regular bets (assuming the "penalty" bet is scored independently of the regular bet's score; if there's one score, then you have to keep them together).

Since ultimately each bet will be determined by a single match (again, assuming I'm following correctly, and that winning isn't a "best two out of three" or something), I'd tie each bet to its user and to the specific match that will determine it. If the "two out of three" situation is true, try to define a "match" such that it covers either a specific game, or a series of games; or, define two different things a bet can tie to, a match or a series (or some such). If you can have "match" and "series" bets, it might make sense for them to be in separate tables (especially if all series bets have certain different options than all match bets); still, you'd want to keep the tables as similar as possible, as you're likely to want to pull some information through both tables. Do try to minimize the this sort of table split; it does complicate calculations, even if it keeps the overall DB design cleaner.

Your actual solution will be different, I'm sure, but this should help point you to the basic principles you can use.


Your database is unhappy.

Login to your Linux box as the DB owner user (usually oracle), restart it & then do the following to recreate the broken data dictionary objects:

sqlplus / as sysdba

@?/rdbms/admin/catrepr.sql

@?/rdbms/admin/catrep.sql

@?/rdbms/admin/utlrp.sql

Restart the database once done.

This is an unusual situation to be in - Has the DB been recently upgraded?


auto set value of a specific column after 24 hours and to delete record after one week: Oracle SQL

Rather than query a table, why not query a view that has your rules built in?
CREATE VIEW vwPasswordResetLink
    AS
SELECT link_id,
       link_date,
       CASE WHEN sysdate > link_date + INTERVAL '1' DAY THEN 1
            ELSE 0 END AS isExpired,
       email,
       linkUsed
  FROM PasswordResetLink
 WHERE link_date >= sysdate - INTERVAL '7' DAY;

Then you can write a scheduled job to delete records older than 7 days to clear out expired records.
Alternatively, you could just use the above query to filter results and skip the creation of the view altogether …


alter table (mysql) adding column with default value of space (' ')

Either the rules have changed, or somebody missed something in compiler builds.

According to the MySQL Documentation on ENUM

  For example, a column specified as ENUM('one', 'two', 'three') can have any of the values shown here. The index of each value is also shown.

  Value   Index

  NULL    NULL

  ''      0

  'one'   1

  'two'   2

  'three' 3

  An enumeration can have a maximum of 65,535 elements.

  Trailing spaces are automatically deleted from ENUM member values in
  the table definition when a table is created.

You are using MySQL 5.1.49. That build probably errors out on seeing a one-character string with a blank.

MySQL 5.5 probably removes trailing spaces leaving an empty string.


Take a look at PSOUG's notes on NULL.  As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'.  In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison.  You need the special IS [NOT] NULL syntax to check if a value is NULL or not.


How can I Automate MongoDB schema design from existing MySQL Database and Logs?

I would not recommend doing automatic migrations from any relational databases (or other that differs in logic) to MongoDB. Of course it can be done, but the database performance might not be (and will most probably not be) good.

Now, answering your questions directly:

1) what do you mean by MySQL parameters?

2) Check the official docs page about the topic: Data Modeling in MongoDB

3) This question has already been answered in StackOverflow - Embed vs Reference.
Nevertheless, here is where automatic migration rules will most probably fail. You need to decide this based on your use cases and necessities. Also, be aware that mongoDB doesn't support joins (meaning you will have to do it manually on the client side).

4) Only know MongoHub - it has an import/export tool that supports mysql


Offline Index Rebuild on a Partitioned Table

I had some time to look at this, and since I already had some of the demo scripts written, it was pretty easy to check on the rest. Let's do the setup, then look at the results. This will create the necessary partitioned table and index.

CREATE PARTITION FUNCTION YourMom ( INT )
    AS RANGE LEFT FOR VALUES ( 1000000, 2000000, 3000000, 4000000, 5000000 );

CREATE PARTITION SCHEME YourDad
    AS PARTITION YourMom
    ALL TO ( [PRIMARY] );

CREATE TABLE dbo.YourAuntDebbie
(
    Id INT,
    StopUsingDeprecatedDataTypes NTEXT
) ON YourDad (Id);

DECLARE @counter INT = 1;
WHILE @counter < 6
    BEGIN

        RAISERROR('Run number: %d', 0, 1, @counter) WITH NOWAIT;

        INSERT dbo.YourAuntDebbie WITH ( TABLOCK ) ( Id, StopUsingDeprecatedDataTypes )
        SELECT TOP 1000000 x.n + CASE WHEN @counter = 1 THEN 0
                                      WHEN @counter = 2 THEN 1000000
                                      WHEN @counter = 3 THEN 2000000
                                      WHEN @counter = 4 THEN 3000000
                                      WHEN @counter = 5 THEN 4000000
                                      ELSE 0
                                      END,
                           REPLICATE(N'A', x.n % 10000)
        FROM   (   SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
                   FROM   sys.messages AS m
                   CROSS JOIN sys.messages AS m2 ) AS x;

        SET @counter += 1;

    END;

CREATE CLUSTERED INDEX ix_whatever
    ON dbo.YourAuntDebbie ( Id ) ON YourDad(Id);

That gives me 5 partitions with 1 million rows, and one empty partition.

SELECT OBJECT_NAME(p.object_id) AS table_name, p.partition_number, p.rows
FROM   sys.partitions AS p
WHERE  p.object_id = OBJECT_ID('dbo.YourAuntDebbie');

Fancy table:

+----------------+------------------+---------+
|   table_name   | partition_number |  rows   |
+----------------+------------------+---------+
| YourAuntDebbie |                1 | 1000000 |
| YourAuntDebbie |                2 | 1000000 |
| YourAuntDebbie |                3 | 1000000 |
| YourAuntDebbie |                4 | 1000000 |
| YourAuntDebbie |                5 | 1000000 |
| YourAuntDebbie |                6 |       0 |
+----------------+------------------+---------+

Here's the XE session I'm using to see which locks the index rebuilds need:

CREATE EVENT SESSION Locks
    ON SERVER
    ADD EVENT sqlserver.lock_acquired
    ( SET collect_resource_description = ( 1 )
     ACTION ( sqlserver.sql_text )
     WHERE (   sqlserver.equal_i_sql_unicode_string(sqlserver.database_name, N'Crap')
               AND package0.equal_uint64(sqlserver.session_id, ( 61 )))),
    ADD EVENT sqlserver.lock_released
    ( SET collect_resource_description = ( 1 )
     ACTION ( sqlserver.sql_text )
     WHERE (   sqlserver.database_name = N'Crap'
               AND sqlserver.session_id = ( 61 )))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\Locks' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = ON,
           STARTUP_STATE = OFF );
GO

With that in place, I can rebuild my partitions, and then dig into XE.

ALTER EVENT SESSION Locks ON SERVER STATE = START;

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 1 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 2 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 3 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 4 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 5 WITH (ONLINE = OFF);
GO

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 6 WITH (ONLINE = OFF);
GO

ALTER EVENT SESSION Locks ON SERVER STATE = STOP;

Now, I'm gonna put the XE event shredding stuff at the end, because it's pretty ugly, and there's no reason to make everyone sit through that to see the results. I'm going to use the first partition's results as an example, but they're just about identical across all 6 partitions, even the empty one.

I'm limiting the results of the update to only the object level locks. These are the only ones we care about.

   +---------------+-------------------------+----------------+--------------+-------+------------------+--------+
|   EventName   |        EventDate        |   ObjectName   | ResourceType | Mode  | PARTITIONREBUILT | Events |
+---------------+-------------------------+----------------+--------------+-------+------------------+--------+
| lock_acquired | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_M | PARTITION = 1    |      1 |
| lock_acquired | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |      1 |
| lock_released | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |      1 |
| lock_acquired | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | S     | PARTITION = 1    |      6 |
| lock_acquired | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |     30 |
| lock_released | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |     24 |
| lock_released | 2017-10-03 13:21:14.867 | YourAuntDebbie | OBJECT       | SCH_M | PARTITION = 1    |      1 |
+---------------+-------------------------+----------------+--------------+-------+------------------+--------+

From what I can tell, for each partition, a SCH-M lock  is taken out at the beginning of the index rebuild, and released at the end ON THE TABLE.

For example, if I run a single partition rebuild in a transaction:

BEGIN TRAN

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 1 WITH (ONLINE = OFF);

--ROLLBACK

And then in another SSMS window:

SELECT *
FROM dbo.YourAuntDebbie AS yad
WHERE yad.Id = 6000000
AND 1 = (SELECT 1)

The select is blocked until I kill the rebuild. When it finishes, the query plan shows partition elimination occurring, so only partition 1 being rebuilt appears to effect the entire table.

Hope this helps!

Now here's the awful XE session shredding code:

    CREATE TABLE #Locks
       (
         ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
         WaitsXML XML
       );

INSERT  #Locks
        ( WaitsXML )
SELECT    CONVERT(XML, event_data) AS TargetData
FROM      sys.fn_xe_file_target_read_file( 'c:\temp\Locks*.xel', NULL, NULL, NULL);

WITH locks
AS ( SELECT l.WaitsXML.value('(/event/@name)[1]', 'VARCHAR(128)') AS EventName,
            l.WaitsXML.value('(/event/@timestamp)[1]', 'DATETIME2(3)') AS EventDate,
            l.WaitsXML.value('(event/data[@name="object_id"]/value)[1]', 'NUMERIC') AS ObjectId,
            l.WaitsXML.value('(event/data[@name="resource_type"]/text)[1]', 'VARCHAR(128)') AS ResourceType,
            l.WaitsXML.value('(event/data[@name="mode"]/text)[1]', 'VARCHAR(128)') AS Mode,
            l.WaitsXML.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(128)') AS SQLText,
            l.WaitsXML
     FROM   #Locks AS l )
SELECT   locks.EventName,
         locks.EventDate,
         ISNULL(OBJECT_NAME(locks.ObjectId), 'Unknown') AS ObjectName,
         locks.ResourceType,
         locks.Mode,
         SUBSTRING(
             locks.SQLText,
             CHARINDEX('PARTITION', locks.SQLText),
             CHARINDEX('WITH', locks.SQLText, CHARINDEX('PARTITION', locks.SQLText))
             - CHARINDEX('PARTITION', locks.SQLText)) AS PARTITIONREBUILT,
         COUNT(*) AS Events
FROM     locks
WHERE    OBJECT_NAME(locks.ObjectId) = 'YourAuntDebbie'
         --OR OBJECT_NAME(locks.ObjectId) IS NULL
GROUP BY ISNULL(OBJECT_NAME(locks.ObjectId), 'Unknown'),
         SUBSTRING(
             locks.SQLText,
             CHARINDEX('PARTITION', locks.SQLText),
             CHARINDEX('WITH', locks.SQLText, CHARINDEX('PARTITION', locks.SQLText))
             - CHARINDEX('PARTITION', locks.SQLText)),
         locks.EventName,
         locks.EventDate,
         locks.ResourceType,
         locks.Mode
ORDER BY locks.EventDate;


SQL Server evaluates where condition before or during the run?

As your procedure is written, you cannot pass only the @first_name parameter, since the other parameters has no default value. But I assume you meant them to have NULL as default, or that you will pass NULL for them.

There is nothing special about NULL. SQL Server will create a plan based on the values for the parameters (aka Parameter Sniffing). That plan will then be cached for re-use. I.e., the plan might be OK for when you search for precisely firstname. But it can be a disaster for the next execution, when you search for lastname.

Adding OPTION(RECOMPILE) will allow a "perfect" plan for each execution, but at the cost of creating a plan for each execution.

Or you can use sub-procedures, executing a sub-proc conditionally based on the parameters passed (which is not the same as doing IF and have several queries in the STP_CampaignTransaction_Get procedure!).

Or use dynamic SQL and construct precisely the SQL you need (only include the WHERE condition(s) needed) and then execute precisely that SQL. I.e., you will have different query plans cached, based on the combo of search arguments.

Erland explains this very well in his article, the one that Bijy refers to in his comment, so go check it out!


Optimize SQL query in form of a Procedure

As far as returning a ref cursor the following should work;

CREATE OR REPLACE PROCEDURE get_table1_data(p_column2 IN table3.column2%TYPE,
                                            p_tables_recs  OUT SYS_REFCURSOR ) AS
BEGIN

   OPEN p_table_recs FOR
      SELECT sp.column1
           , sp.column2
           , sp.column3
           , gen.column1
           , NVL(gen.column2, 0) + NVL(gen.column3, 0) gensize
           , gen.column4
           , gen.column5
      FROM   table1 sp
          , (SELECT *
             FROM   table2
             WHERE  table2.column1 IN (SELECT table1.column4
                                       FROM   table1
                                       WHERE  table1.column2 IN (SELECT table3.column1
                                                                 FROM   table3
                                                                 WHERE  table3.column2 = p_column_2))) gen
       WHERE  gen.id = sp.id;
END get_table1_data;


Get Job step run for all jobs and job steps using TSQL

This should get you close, if NULL for ProxyName then it's using the SQL Server Agent credentials.

USE msdb

SELECT J.job_id
    , J.name
    , S.step_name
    , S.step_id
    , P.name AS ProxyName
    , SP.name AS CredentialUserName
    , SP.type_desc AS CredentialUserType
FROM msdb.dbo.sysjobs J
    INNER JOIN msdb.dbo.sysjobsteps S ON S.job_id = J.job_id
    LEFT OUTER JOIN msdb.dbo.sysproxies P ON P.proxy_id = S.proxy_id
    LEFT OUTER JOIN sys.server_principals SP ON SP.sid = P.user_sid


How can one make a partial index to optimize a query for a single value?

You always need a column in an index.
You could try to index a constant that occupies only one byte:
SELECT typname FROM pg_type WHERE typlen = 1;

 typname
---------
 bool
 char
(2 rows)

If you choose boolean, that would be
CREATE INDEX ON foos ((TRUE)) WHERE thing_id IS NULL;

But my recommendation is to index id. Sure, that would make the index somewhat bigger, but your second query could use a much faster index-only scan.


Storing data in multiple rows vs serialized data

2M rows is "medium-sized".  That should not be your concern; databases are designed to handle millions, even billions, of rows.  However you will need to have suitable indexes, and include the optimal PRIMARY KEY.

Think about what "entities" you have -- departments, students, sessions, courses, etc.  Thin think about the "relationships" -- course in department; student in a session of a course; etc.

1:many is handled by an id in one table for JOINing to another table.  many:many is handled by an extra table with two ids in it.  1:1 is (usually) a no-no.

Work through some tutorials on database design.  Be sure to understand JOINs.


How to specify the system database location when installing SQL Server?

you can select the root directory path here


Optimal hash technique to index large text

bytea will be optimal for storing the hash.

It'll be transferred in/out of the database as a hex string anyway, unless you use PostgreSQL's binary wire protocol (supported by libpq and partly by PgJDBC) to transfer them.

For best results, store as bytea and have the client application use a PQexecParams call that requests binary results.

Though on re-reading, this is confusing:

  For my implementation, the hash never needs to leave the database, but the hashed data must be compared with external data for existence frequently

Do you mean that the hash isn't transferred for comparison, the original unhashed text data is? If so, the above is irrelevant, as the binary protocol offers no benefits for text-form data.

Also: "tens of billions" of rows is a lot. PostgreSQL has quite a large per-row overhead at 28 bytes, so you're going to be losing a lot of space. Especially once you factor in index overheads too. Is PostgreSQL the right tool for this job?

A final thought: With that many rows, you're getting up into hash-collision territory. Do you care if it's possible - though unlikely - for two different strings to have the same hash, so an incorrect unique violation is reported? If that's a problem then a unique b-tree index on the hash probably isn't the right tool for the job.


Migrate from SQL Server 2016 Failover Cluster to Availability Groups

Prereqs, Restrictions, Recommendations - Always On Availability Groups states:

  The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine.

An availability group is really just a container to group mirrored databases and associate a listener with them, and to provide the interface with Microsoft Cluster to determine what server should be primary.  The only significant associated work is the replication of the databases.

So the short answer is "yes," you can use the same number of instances and databases, but you have to consider whether the additional load of replication will overload the system.  So you'll want to pay close attention to the databases that have higher I/O and try to distribute them amongst the nodes so that one node isn't a primary and/or secondary for multiple heavy hitters.  Or, if that's unavoidable, make sure you've got lots of bandwidth, CPU, memory, and disk space.

AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc. is also something that you'll want to peruse.


Tuning Postgres for Single connection use? Or is postgres wrong tool?

If possible i would do following : Define 2 tablespaces, one for data , one for result tables. work_mem and maintenance_work_mem to 1Gb and 2Gb , shared buffer to high only if you run two or more queries same time to same set of data, if you don't do that then shared memory will be more or less wasted.

Biggest problems you will have is that Postgresql uses only one core per session , so easiest way to get more done is just run several queries on different sessions. Second problem will be disk i/o, several fast disks + different tablespaces help. Third is RAM settings. I would configure work_mem so that sessions*work_mem is somewhere at 10GB and keep maintenance_work_mem on 1-2GB. maintenance_work_mem can be set from query like this set maintenance_work_mem='6GB'; so you can set it higher when you do maintenance. You can also set work_mem like set work_mem='6GB' for session.

Table definitions also affect space used see: https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql . Probably not needed, but with 600GB of data scientific data it may help alot.


How to insert a record with generated identifiers using UUID()?

One way to vercome the multiple trigger error would be ot use one trigger - and not 4 - to set the new UUID values:

DELIMITER //
CREATE TRIGGER before_insert_on_user_set_uuids
  BEFORE INSERT ON user
  FOR EACH ROW
    BEGIN
      SET new.info = replace(uuid(),'-','');
      SET new.note = replace(uuid(),'-','');
      SET new.auth = replace(uuid(),'-','');
      SET new.test = replace(uuid(),'-','');
    END ;  //
DELIMITER ;

It would also be good idea to store the UUIDs in BINARY(16) columns instead of VARCHAR(32). Saves 16x4 bytes per row (plus 16 per row multiplied by the number of simple indexes on the columns) and some speed for not considering collations and character sets. And I assume you had the varchar column as ASCII and not UTF-8 which would be a real waste.


How to efficiently design MongoDB database for Uber-like application?

As you demonstrated in your question, there is a clear relational structure to your data.

There are different objects (Users, Requests, and Messages), which all relate to each other, potentially in multiple ways.
These objects have attributes (eg driver or passenger names) that would be referenced many times from other objects (Requests). This requires either data duplication or normalization (and thus joins)
The different objects are tightly coupled to each other, and your anticipated data access patterns reflect the need to query from multiple angles.

The title of this article is intentionally provocative, but there is a lot of good content: Why You Should Never Use MongoDB

In particular, the section "How MongoDB Stores Data" and the several sections after that discuss precisely the questions you ask regarding Normalization vs Denormalization, and issues with how to efficiently query your data.

Ultimately, you have one main question:

  What would be the best way to design a database here?

The answer is: Because your data is relational, don't use MongoDB.

MongoDB may be the right solution for storing unstructured (or less structured) data related to your application, but for the specific data you mention, it is the wrong choice.


postgresql updating integer column taking more space than expected

Well, you are running this giant UPDATE statement of all rows in table B:

update B t1 set b1 = t2.a1 from (select b1, b2 from B) t2 where t1.a2 = t2.b2;

which will more-or-less double the size of the table, since the UPDATE has to keep old row versions around. You may want to read up a bit on how PostgreSQL implements MVCC and how vacuuming works, but to answer this question:

  If so could I expect the size to go down if i use VACUUM?

The space utilized by the table should go down if you run a VACUUM FULL or CLUSTER; probably a VACUUM alone will not be sufficient to immediately reclaim space.


TOP(1) BY GROUP of very huge (100,000,000+) table

If you have a table of SourceID values, and an index on your main table on (SourceID, End) include (othercolumns), just use OUTER APPLY.

SELECT d.*
FROM dbo.Sources s
OUTER APPLY (SELECT TOP (1) *
    FROM data.OperationData d
    WHERE d.SourceID = s.SourceID
    ORDER BY d.[End] DESC) d;

If you know you’re only after your newest partition, you could include a filter on End, like AND d.[End] > DATEADD(day, -1, GETDATE())

Edit: Because your clustered index is on SourceID, Source, End), put Source into your Sources table too and join on that as well. Then you don’t need the new index.

SELECT d.*
FROM dbo.Sources s -- Small table
OUTER APPLY (SELECT TOP (1) *
    FROM data.OperationData d -- Big table quick seeks
    WHERE d.SourceID = s.SourceID
    AND d.Source = s.Source
    AND d.[End] > DATEADD(day, -1, GETDATE()) -- If you’re partitioning on [End], do this for partition elimination
    ORDER BY d.[End] DESC) d;


My Ola Hallengren nightly backup job failed but It works fine when I execute the job manually by right clicking

You can try to address the problem by increasing the timeout periods.

Increase the standard settings on your server for remote login timeout (s) and remote query timeout (s).

Or in your connection string you could include "Connect Timeout=120;General Timeout=120;" to increase the time allowed for the connections.


Migrating a cluster from SQL Server 2005 to SQL Server 2016

This is one of those questions that I love to see. There are a lot of things to add to do to add to migration and upgrade successes and it warms the hear to see the question.

You've already covered a lot of things to consider and I'll add a few thoughts but I am looking forward to seeing other answers with more ideas from others.

I'm not including things you've already included in the great list you have started and knowing me I'll be editing and adding to this answer as I go.

**One disagreement I may have with your approach: Regarding the cluster network name, why not make a new cluster name and just use CNAME to point connections to the old name to the new instance? This lets you make a new name, avoid messing with conflict potential, makes go live night smoother and gives you a clearer and easier rollback. I would do a CNAME approach and change the CNAME once you are sure the old can be taken offline. I may be alone here so I welcome comments supporting that idea or disagreeing.

I see a few areas of focus when it comes to migrating. If you make an "outline" of the phases and outline of the areas to focus on comes to mind: Planning, Pre-Migration Steps, Day of Migration, Post Migration. I'll cover some of those sections here and I am hoping others join in with ideas.

Planning Thoughts

Since you are starting with new hardware. This is a great chance to make sure you are considering all of the best practices that may have been overlooked before. Things like:

Split out your drives. At the very least I'd love to see separate drives for (Your Mileage May Vary here also, really depends on performance profile of your setup):

Operating System (C drive)
SQL Server binaries and maybe system DBs excluding TempDB
User Data Files
User Log Files
TempDB data and Log Files
Local backup and/or misc drive for file imports, staging of data, etc

When formatting your drives for Data files and TempDB specifically, consider doing the 64KB allocation unit size. (Source)
Now is your chance to maybe clean up some of those security holes and flaws that could exist. Look to build with best practices and least access.

Before Migration

Lots of things to do before the migration. Some that come to mind immediately are:

Run the upgrade advisor. SQL Server 2016 made some improvements there and it is a separate download. Look to see what issues may exist.
Do a more thorough test of your DB to see what breaks and what works. Do a test migration and put into 2016 (130) compatibility mode. And do thorough testing. If you don't have a test approach you can look at something like profiling activity and replaying to see what breaks. Test. Test. Test.
Grab the SQL Server and Windows Server 2016 documents about clustering and failover clustering. Look to the checklists and prerequisites carefully.
Ensure you have good transaction log management - why migrate an excessively large LDF file due to poor management.
Make sure you are doing maintenance properly on your databases - especially make sure you are running regular DBCC CHECKDBs - this is a good thing to do before migration or really as part of final migration, but you don't want the first time you do one to be on migration night and find surprises.
Determine success criteria and have a rollback plan (doing a migration to new is great, you can simply rollback any CNAME changes, connection strings and point back to old) in case of issues. And know what issues constitute a rollback and what issues you'll "stay and play" with post migration. Get buy in for this list.
Have a really good backup strategy. Know you can restore. Know where your backup files are, etc.
Look to all server objects. You covered a lot. But don't forget credentials, proxy accounts, alerts, maintenance jobs, SP_Configure settings, etc. A GREAT tool for you to look at that can actually help you remember things is the PowerShell DBATools scripts. These scripts can help you migrate all of these items - including SQL authenticated logins with the correct SIDs for your databases, but they also can help you remember what to migrate when you look through all of the modules. You can look into the scripts here.

Immediately After Migration

Lots more here. And a category of "On migration week" could be added and maybe someone will or I will edit and add later.

Some things here that come to mind straight away:

Have a smoke test / check to verify all came over well.
Change compatibility mode to SQL Server 2016 on all databases if testing proved that out to work. I see this getting missed a lot.
Make sure maintenance and backups are in place. Run a post migration DBCC CHECKDB on the databases.
Make sure your jobs are running.
Configure monitoring and alerting so you don't miss alerts about this server
Once you are satisfied with a limited test, turn the old off and make a CNAME change.


MySQL Innodb - Truncate Partition. Does it reclaims space?

You have not said what value innodb_file_per_table was set to when you created the table.  If OFF, you have no chance of shrinking any file on disk.  Instead, you can only send it off to be 'free' and potentially reused by inserts.  With ON...

A third option is ALTER TABLE REORGANIZE PARTITION ... where you essentially state the same partition as the 'from' and 'to'.  This degenerate version effectively does what OPTIMIZE PARTITION should do (but fails to).

Fourth option, which would work in some partition types:  DROP PARTITION and add it back.

Yes, OPTIMIZE PARTITION rebuilds the entire table.  After 5.6.17 (5.7.4), you may may be able to do it non-blocking (ALGORITHM=INPLACE).

Even if TRUNCATE does not free the space, will you be adding data back in?  Of so, then one could argue that it is "not a problem".


SQL SERVER - "ApplicationIntent=ReadOnly"Listener redirect to fully qualified domain name

My initial suspicion is that the initial setup didn't include the fully qualified name, but check for certain with the following statement:

SELECT ag.NAME AS "Availability Group"
    ,ar.replica_server_name AS "When Primary Replica Is"
    ,rl.routing_priority AS "Routing Priority"
    ,ar2.replica_server_name AS "RO Routed To"
    ,ar.secondary_role_allow_connections_desc
    ,ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
INNER JOIN sys.availability_replicas ar ON rl.replica_id = ar.replica_id
INNER JOIN sys.availability_replicas ar2 ON rl.read_only_replica_id = ar2.replica_id
INNER JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
ORDER BY ag.NAME
    ,ar.replica_server_name
    ,rl.routing_priority

The query was gratuitously lifted from this MSDN blog: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/22/modifying-alwayson-read-only-routing-lists/

In addition, make sure your JDBC driver supports the ApplicationIntent=ReadOnly parameter.


My suggestion is to use Microsoft Data Migration Assistant. It will perform an assessment of your on-premises databases first, then it allows you to migrate the database to Azure SQL Database. This tool is very user friendly. It's very easy to migrate a database to Azure with this tool.


Hard limits:

JOINs, including implicit ones in Views: 61.
Columns per table < 1000 (1017 in 5.6.9, InnoDB); 2598(?) for MyISAM -- But don't use MyISAM.
others

But why do you need a lot of JOINs?  Please elaborate on the schema (SHOW CREATE TABLE, leaving out many of the columns) and SELECT (leaving out all but a couple of representative JOINs).

Do you actually do arithmetic or searching in MySQL on the 240 readings?  If not, simply pack them in a JSON string and let the app break them apart.  Further, compress the JSON string (in the client) and store in a BLOB.  (Smaller --> faster)  PHP would have no problem unpacking 4000 fields with json_decode().  And it could be in the desired structure (array / hash / nested arrays / etc)

The main thing is to structure the schema for what works well in the database, then leave the rest for the application.  The database is a repository; the app is the compute engine.

One table per hour or one table per location -- this smells bad.  One table per location per hour would be really bad.  Once you have thousands of tables in a database (or thousands of databases), the OS gets sluggish in accessing them.

So... 3 (or 1?) tables with

location_id (normalized, to save space)
hour (probably a DATETIME)
one (or a few or maybe 80) JSON strings each with 87(?) numbers, structure for the convenience of the app.

(Sorry, I got lost in the dimensions -- 3 somethings * 80 something else * 87 * 52 * ???)

Plus, PRIMARY KEY(location_id, hour) (or possibly in the opposite order.  And (probably) a plain INDEX with those columns in the opposite order.  Pick the order in the PK based on which order is more important for the SELECTs.  (Don't worry about INSERTs.)


PGBouncer Pause Waits for Remote Connection to Close

At least with pool_mode=session, it looks like a 'PAUSE' command will succeed only if the other client(s) have not yet issued any queries in their sessions. If the other client(s) have already issued any queries, the 'PAUSE;' command hangs as you describe.

This basically makes sense from PgBouncer's point of view, as it doesn't need to give a real connection to a client before the client issues any queries, but once it does a 'PAUSE' must wait for that client to finish its session.


SQL Server 2012. Update second table based on input from first table

There are two options that come to mind. The first you have identified as a trigger that you could implement in TSQL for real-time synchronization of data. Depending on the activity of your system, the quality of your hardware (particularly I/O subsystem).

Synchronous approach

A trigger, implemented something like this. You would know better specifically what logic you need but the heart will be an AFTER trigger on the table in the system of record.

CREATE TRIGGER dbo.tr_TableSync
ON dbo.Table1
AFTER INSERT, UPDATE
BEGIN
    SET NOCOUNT ON;

    -- sync existing
    UPDATE
        T2
    SET
        RefVal = I.Val
    FROM
        T2
        INNER JOIN
            inserted AS I
            ON I.SK = T2.SK;

   -- Add new stuff
   INSERT INTO
       T2
   (
       SK
   ,   RefVal
   )
   SELECT
       I.SK
   ,   I.Val
   FROM
       inserted AS I
   EXCEPT
   SELECT
       T2.SK
   ,   T2.RefVal
   FROM
       T2;

   -- Do we need to account for deleted items?
   -- If so, need to add to declaration above
   -- and code here
END

Asynchronous approach

The challenge around the trigger is that it can have a heavy footprint on performance to data manipulation operations in that table. SQL Server offers two approaches for asynchronous operations that provide a lighter footprint. You can look at implementing Service Broker or creating a CLR trigger.

Service Broker is a message queue. You'd create a message in your AFTER trigger (unless you can guarantee all manipulation is only performed through a proc or some other codified method) which will then get parsed when the system has a "chance to breathe" but it is guaranteed to happen just not RIGHT NOW DO IT NOW I'M WAITING ARE YOU DONE like the synchronous approach above forces.

I've never had cause to implement a CLR trigger before but it's possible and in your case, it'd be much like implementing your own message queue.

Final thought

Everyone loves to say "it needs to be instantaneous/real-time" but when they learn what that can cost to implement correctly, near-real-time of a few minutes to a day (yes, I've had people go from it must be accurate to the nano-second to as long as it's within 24 hours, we'll be fine). If that can satisfy your business needs, then you have even more doors opened (a SQL Agent job that fires SQL statements or SSIS or a custom .NET app every N interval).


query to search a particular word/string in toad/oracle

You can use this:

select name , line,text from dba_source where regexp_like(text,'audapro_ind','i');

'i' specifies case-insensitive matching.


Best practices for storing multiple SQL tables per user (built from uploaded csv file?)

One option is to use different schemas per user, and then tables with the convention you want.

CREATE TABLE '<user_id>'.'<filename>'...


Check if restaurant sells only the pizzas that customer likes using SQL

I'd logically do (2) first with a join ("Restaurant has the same location as customer"), and then eliminate the results with a restaurant that sells a pizza that the customer doesn't like:

create table customers(
  cname text
 ,area text
);

insert into customers(cname,area)
values('alice', 'east')
     ,('bob', 'north')
     ,('james', 'central');

create table restaurants(
  rname text
 ,area text
);

insert into restaurants(rname,area)
values('Pizza Place', 'east')
     ,('Pizza Local', 'west')
     ,('Pizza World', 'central');

create table sells(
  rname text
 ,pizza text
 ,price int
);

insert into sells(rname,pizza,price)
values('Pizza Place', 'hawaiian','10'),
  ('Pizza Local', 'pepperoni','11'),
  ('Pizza World', 'chicken','12'),
  ('Pizza World', 'meat feast','13'),
  ('Pizza World', 'veggie','8');

create table likes(
  cname text
 ,pizza text
);

insert into likes(cname,pizza)
values ('alice', 'hawaiian')
      ,('bob', 'pepperoni')
      ,('james', 'chicken')
      ,('james', 'meat feast');

select *
from customers c join restaurants r using (area)
where not exists( select pizza from sells where rname=r.rname
                  except
                  select pizza from likes where cname=c.cname );

area | cname | rname
:--- | :---- | :----------
east | alice | Pizza Place

dbfiddle here

To get the list of customers just change select * to select distinct cname


Manual sql failover machine with replication

Yes, if you are using Peer2Peer replication between the primary and secondary servers which is an enterprise only feature
I would personally use Mirroring/AlwaysOn for redundancy between primary and secondary sites as this includes ways to failover without changing the DNS records. Either by adding ';Failover Partner=PartnerServerName;' to the connection string or by using Availability groups with multisite failover
Mirroring or AlwaysOn, depending on your SQL Server version - or Peer2Peer replication.
Using Mirroring or Availability groups this will in most cases simply by achieved by bringing the server online and the service will automatically send all the changes over and then you can manually failover.


How to store massive quantities of machine generated data?

There's a few systems specifically tailored to what you're requesting. I don't know exactly what kind of logs you're dealing with (application specific logs, system logs, web-server logs, a combination, etc.), so you may need to research a bit further.

Please pardon the lack of inline links - I currently don't have the reputation to post more than 2 links (you can find the writeup with inline links here).

Commercially there's:

Splunk
Sumo Logic
Loggly
Papertrail

On the open-source side there's:

Graylog2 (meant as an open-source alternative to Splunk)
OpenTSDB
Roll your own with Apache Flume and elasticsearch
Logstash

Personally, I'd look into Graylog2, Logstash, and (depending upon the requirements) OpenTSDB.

One thing to also look at is the underlying database system and how well it works with the hardware that you've already bought (i.e. MongoDB may not work very well with the hardware you have and the size of data that you have, however Hadoop/Hbase or a SQL server may work out better).


Please do not get fooled by the error message.

Note that the error is table is full. It does not say disk is full.

What would create a table is full condition ?

It has to do with the changes that are pouring into the rollback segments for a transaction.

I have addressed this many times

May 31, 2013 : How to solve "The table ... is full" with "innodb_file_per_table"?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Jun 16, 2014 : MySQL Index creation failing on table is full
Apr 07, 2015 : MySQL query INSERT INTO ... failed - The Table "x" is full

It appears that one of the following occurred

The SELECT was in the middle of a transaction
The SELECT attempted to hold too much rollback info in the face of  many transactions

In either case, the tblinvoice table probably had so many changes pending that the SELECT just had to give up. This could have caused all write transactions against tblinvoice to rollback.

If you were running this SELECT on a busy Master with lots of writes against tblinvoice, then I could this SELECT having this problem.

What you should do is set up a reporting slave (running MySQL Replication) and run this type of query on it instead on the main server.

UPDATE 2018-02-27 10:10 EDT

Your problem may be the amount of RAM free.

You currently have this

tmp_table_size=2G
max_heap_table_size=2G

Try running the query again so that the temp table goes immediately to disk

set tmp_table_size = 1024 * 1024 * 16;
set max_heap_table_size = 1024 * 1024 * 16;
SELECT
    InvoiceNo,
    NetAmountAbs,
    InvoiceDate
  FROM tblinvoice
  WHERE GrossAmountAbs >= 200
  GROUP BY InvoiceNo,
           NetAmountAbs,
           InvoiceDate
  HAVING COUNT(*) > 1
  AND SUM(CASE WHEN NetAmount_Doc >= 0 THEN 1 ELSE 0 END) > 1
  AND MIN(AccountNumber) != MAX(AccountNumber)
  AND MAX(GrossAmountAbs) != MIN(GrossAmountAbs)
  AND MAX(ImportID) = 2;

Keep watch on the C:\Windows\SERVIC~2\NETWOR~1\AppData\Local folder. Watch the temp file that shows up in that folder. What I am hoping is that the temp table will reach the needed size on disk. Why ?

With tmp_table_size=2G and max_heap_table_size=2G, the temp table has to reach 2G in RAM before transferring to disk. My working theory is that you do not have 2GB of RAM free (We are talking Windows, right ???). This is why I am suggesting lowering these to 16M (which is the default value anyway). Give it a try !!!


What are the risks of copying MDF/LDF files without detaching the DB first?

This is a terrible way to take a backup. Why aren't they using, uh, backups? Not just because they don't require that you take down the entire instance.

If you just stop the instance, there isn't really a guarantee that the MDF/LDF files will be detached gracefully. So just because you are able to copy them, does not mean they are good. Having the service stopped removes the very protection SQL Server provides by locking the files from file system access in the first place (what is preventing users from accidentally deleting one of these files? They are human, after all). And for all you know the files could be corrupt before you even copy them, or could become corrupt while you copy/move them. If the user accidentally moves them instead of copies them, and something happens to them during that process, you now have zero copies of your database.

Backups are very easy, and protect you from all of the above.

BACKUP DATABASE dbname TO DISK = 'C:\whatever\dbname.bak'
  WITH COPY_ONLY, INIT, COMPRESSION;

Restoring is similarly easy, though if the file structures aren't the same, you may need to use the WITH MOVE arguments. This effort is worth it.

RESTORE DATABASE dbname FROM DISK = 'C:\whatever\dbname.bak'
  WITH MOVE 'dbname_data' TO 'C:\...\dbname.mdf',
       MOVE 'dbname_log'  TO 'C:\...\dbname.ldf';


"Warning: The join order has been enforced because a local join hint is used" returned with no Join hints

You can get the definition of that DMV by running the following T-SQL:

EXEC sp_helptext 'sys.dm_exec_query_memory_grants'

Which shows that a join hint is being used in the underlying view definition:

CREATE VIEW sys.dm_exec_query_memory_grants AS
 SELECT A.session_id, A.request_id, A.scheduler_id, A.dop, A.request_time, A.grant_time,
 A.requested_memory_kb, A.granted_memory_kb, A.required_memory_kb, A.used_memory_kb, A.max_used_memory_kb,
 A.query_cost, A.timeout_sec,
 convert(smallint, A.is_small) as resource_semaphore_id,
 B.queue_id, B.wait_order, B.is_next_candidate, B.wait_time_ms,
 A.plan_handle, A.sql_handle,
 A.group_id, A.pool_id, A.is_small, A.ideal_memory_kb
 FROM OpenRowset(TABLE DM_EXEC_QE_GRANTSINFO) A
 LEFT OUTER LOOP JOIN OpenRowset(TABLE DM_EXEC_QE_GRANTWAITERS) B
 ON A.session_id=B.session_id and A.request_id=B.request_id and A.sql_handle=B.sql_handle and A.plan_handle=B.plan_handle

Specifically it's the "LOOP JOIN" hint that is triggering the join order warning.  Check out these Microsoft docs on join hints:

  If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query...

So even though the view just has a hint to enforce a particular join strategy (loop), a join order hint is automatically applied.


OLEDB connections using the MSOLEDBSQL driver should use this format:

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=MyServer;Database=tempdb;UID=test;Pwd=Test;'
    , 'SELECT name FROM sys.databases;'
    ) c;

ConnectionStrings.com is invaluable for obtaining valid connection string details.


How to user the Tablediff Utility with Replication Filters

Run TableDiff.exe against a view configured to limit the source table in the same way your replication filter does.  I've created an example here:

USE Test;

    /* Create two test tables, TableDiff1 is the source and TableDiff2 is the destination */
CREATE TABLE TableDiff1
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff1_TableData DEFAULT (NEWID())
);

CREATE TABLE TableDiff2
(
    TableID INT NOT NULL CONSTRAINT PK_TableDiff2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , FilterColumn INT NOT NULL
    , TableData NVARCHAR(255) CONSTRAINT DF_TableDiff2_TableData DEFAULT (NEWID())
);

    /* Create some dummy data in TableDiff1 */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (2, DEFAULT);

    /* Copy the dummy data into TableDiff2, with a FILTER */
INSERT INTO TableDiff2 (FilterColumn, TableData)
SELECT FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

    /* Add some more data to TableDiff1 that does not exist in TableDiff2
       so TableDiff.exe shows differences between the two tables */
INSERT INTO TableDiff1 (FilterColumn, TableData) VALUES (1, DEFAULT);

SELECT * FROM TableDiff1;
SELECT * FROM TableDiff2;

Next, we run TableDiff.exe against both tables, clearly there will be differences since we are not excluding the filtered rows (FilterColumn = 1)

  C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost
  -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1 -destinationserve
  r localhost -destinationdatabase Test -destinationschema dbo -destinationtable T
  ableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1] on localhost and Table [Test].[dbo].[TableDiff2]
 on localhost have 2 differences.
Err     TableID
Src. Only       3
Src. Only       4
The requested operation took 0.1573824 seconds.

Next, we create a View that has a WHERE filter to select only the FilterColumn = 1 rows:

GO
CREATE VIEW TableDiff1_Filtered
AS
SELECT TableID, FilterColumn, TableData
FROM TableDiff1
WHERE TableDiff1.FilterColumn = 1;

Now, when we run TableDiff.exe against the view, we get the desired results.  Only a single row is identified as missing from the destination table (the row we added after "replicating" the rows from the source table).

  C:\Program Files\Microsoft SQL Server\110\COM>TableDiff -sourceserver localhost
  -sourcedatabase Test -sourceschema dbo -sourcetable TableDiff1_Filtered -destina
  tionserver localhost -destinationdatabase Test -destinationschema dbo -destinati
  ontable TableDiff2

Microsoft (R) SQL Server Replication Diff Tool
Copyright (c) 2008 Microsoft Corporation

User-specified agent parameter values:
-sourceserver localhost
-sourcedatabase Test
-sourceschema dbo
-sourcetable TableDiff1_Filtered
-destinationserver localhost
-destinationdatabase Test
-destinationschema dbo
-destinationtable TableDiff2

Table [Test].[dbo].[TableDiff1_Filtered] on localhost and Table [Test].[dbo].[Ta
bleDiff2] on localhost have 1 differences.
Err     TableID
Src. Only       4
The requested operation took 0.1220787 seconds.


SQL 2005: Can we determine how much the rebuild index maintenance job can grow database log files?

Yes, you should look at the excellent whitepaper on this topic although it refers to the online index rebuild, it still has lot of good info

http://technet.microsoft.com/en-us/library/cc966402.aspx

If the log files are auto growing then you can find that information using the default trace after the action is completed.

DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;

--Check if the data and log files auto-growed. Look for tempdb, log files etc.
SELECT
    gt.ServerName
    , gt.DatabaseName
    , gt.TextData
    , gt.StartTime
    , gt.Success
    , gt.HostName
    , gt.NTUserName
    , gt.NTDomainName
    , gt.ApplicationName
    , gt.LoginName
FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass in ( 92, 93 ) --'Data File Auto Grow', 'Log File Auto Grow'
ORDER BY StartTime;
--


What hardware to purchase for MySQL server? Details below

This is really tiny. Test it on a machine you have already. Index the right fields and you should get significantly better than 1 second even on very basic hardware.
cpus - 1 - you are never going to be executing more than a query at once based on usage described
RAM 2G+, but probably not more than 6. Depends a bit on table index types.
Raid - basic low end mirror (RAID1).
Bandwidth - not much at all. You could do this on 64kbps probably.
On a small business, I wouldn't by buying hardware. Use a cloud service if the terms of data usage allows it.


Does oracle database have a mechanism to replace specific character when storing data

The database does not offer such an option that you asked for.

It is the responsibility of the client/application to insert correct data into the database.

If you want to change existing data, you can use the TRASNLATE function to replace each occurence of specific characters.

update table1
set column1 = translate(column1, 'AB', 'XY')
where column1 != translate(column1, 'AB', 'XY');


Using a keywords table with multiple other tables

Your design is correct. Tables with many to many relationship needs to have an interface table. Interfaces table contains foreign keys to the main table, it must have a primary key. Good luck


The part of the query is maxing out the CPU for long periods is the the functions in the GROUP BY clause and the fact that the grouping is always going to require an unindexed sort in this instance. While an index on the timestamp field will help the initial filter this operation has to be performed on every row that the filter matches. Speeding up this are using a more efficient route to do the same job as suggested by Alex will help, but you still have a huge inefficiency there because what-ever function combination you use the query planner is not going to be able to come up with something that will be helped by any index so it will have to run through every row first running the functions to compute the grouping values, only then can it order the data and compute the aggregates over the resulting groupings.

So the solution is to somehow make the process group by something it can use an index for, or otherwise remove the need to consider all the matching rows at once.

You could maintain an extra column for each row containing the time rounded to the hour, and index this column for use in such queries. This is denormalising your data so might feel "dirty" but it would work and would be cleaner than caching all the aggregates for future use (and updating that cache as the base data is altered). The extra column should be maintained by trigger or be a persisted computed column, rather than maintained by logic elsewhere, as this will guarantee all current and future places that might insert data or update the timestamp columns or existing rows result in consistent data in the new column. You can still get the MIN(timestamp) out. What the query will result in this way is still a walk down all the rows (this can not be avoided, obviously) but it can do it index order, outputting a row for each grouping as it gets to the next value in the index rather than having to remember the whole set of rows for an unindexed sort operation before the grouping/aggregation can be performed. It will use a lot less memory too, as it won't need to remember any rows from prior grouping values in order to process the one it is looking at now or the rest of them.

That method removes the need find somewhere in memory for the whole result set and do the unindexed sort for the group operation and removes the computation of the group values out of the big query (moving that job out to the individual INSERTs/UPDATEs that produce the data) and should allow such queries to run acceptably without needing to maintain a separate store of the aggregated results.

A method that doesn't denormalise your data, but does still require extra structure, is to use a "time table", in this case one containing one row per hour for all the time you are likely to consider. This table would not consume a significant amount of space in a DB or appreciable size - to cover a timespan of 100 years a table containing one row of two dates (the start and end of the hour, such as '2011-01-01@00:00:00.0000','2011-01-01@00:00:59.9997', the "9997" being the smallest number of milliseconds a DATETIME field won't round up to the next second) which are both part of the clustered primary key will take ~14Mbyte of space (8+8 bytes per row * 24 hours/day * 365.25 days/year * 100, plus a bit for the overhead of the clustered index's tree structure but that overhead won't be significant). With such a table you could do something like the following:

SELECT CONVERT(VARCHAR, [timestamp], 1)+' '+ CAST(DATEPART(Hh,[timestamp]) as VARCHAR) AS TimeStampHour
     , MIN([timestamp]) as TimeStamp
     , AVG(MyField) As AvgField
FROM TimeRangeByHours tt
INNER JOIN MyData md ON md.TimeStamp BETWEEN tt.StartTime AND tt.EndTime
WHERE tt.StartTime > '4/10/2011'
GROUP BY tt.StartTime
ORDER BY tt.StartTime

This means that the query planner can arrange for the index on MyData.TimeStamp to be used. The query planner should be bright enough to work out that it can walk down the tame table in step with the index on MyData.TimeStamp, again outputting one row per grouping and discarding each set or rows as it hits the next grouping value. No storing all the intermediate rows somewhere in RAM then performing an unindexed sort on them. Of course this method requires that you create the time table and make sure it spans far enough both backwards and forwards, but you can use the time table for queries against many date fields in different queries, where as the "extra column" option would require an extra computed column for each date field you needed to filter/group by this way, and the small size of the table (unless you need it to span 10,000 years of course, in which case it'll be around 1.4Gb) will mean it it likely to always be in RAM.

The time table method has an extra difference (that could be quite advantageous) compared to you current situation and the computed column solution: it can return rows for periods for which there is no data, simply by changing the INNER JOIN in the example query above to be a LEFT OUTER one.

Some people suggest not having a physical time table, but instead always returning it from a table returning function. This means the content of the time table is never stored on (or needs to be read from) disk and if the function is well written you never have to worry about how long the time table needs to span back and forth in time, but I doubt the CPU cost of producing an in-memory table for some rows every query is worth the small saving of hassle of creating (and maintaining, should its timespan need to extend beyond your initial version's limit) the physical time table.

A side note: you don't need that DISTINCT clause on your original query either. The grouping will ensure that these queries only return one row per period under consideration so the DISTINCT will do nothing other than spin the CPU a little more (unless the query planner notices that the distinct would be a no-op in which case it'll ignore it and use no extra CPU time).


three tables relation order is correct but values are false

It is common multiplying in multi-table source. Each record in table3 is joined to 3 (in above sample data, really it can differ, of course) records from table2.

If table3(t1_id,letter) is unique (by index) then COUNT(DISTINCT t3.letter) will help.

If not then the only solution is in calculate this COUNT() in subquery by table3 separately and join the result to table1.


Foreign keys without any checking or index

Have you tried the NOCHECK option?

https://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx


Just 4 Table Join query takes too much time and some time fail to execute

Red flag!  x AND y OR z is the same as (x AND y) OR z.  I suspect you need parentheses around the ORs.

And something seems to be missing (even before my edit) on the last OR.

(I suggest you use short "aliases" to help de-clutter the query.)

Please qualify all columns.  Meanwhile, I will assume all the columns mentioned in the WHERE come from candidate, else some of the following will not be correct....

Leading wildcards with LIKE prevents usage of indexes, so I will ignore name, etc.  Can you use FULLTEXT instead of LIKE?

ids are usually positive values, so why do you have AND  candidate.id >0??

So, we are down to two choices for a useful INDEX: (total_experiance) or (expected_salary).  But I suspect neither of those will be very useful.

As for the 4 LEFT JOINs -- Are they "many:1"

Ouch.  2 different tables in the GROUP BY.  That forces the generation of a temp table leading into the GROUP BY.  That temp table will have lots of rows, and be slow.

Since the ORDER BY is different than the GROUP BY, there will be another temp table and sort.  This can be avoided by saying

ORDER BY `candidate_key_skills`.`candidate_id` DESC,
         `candidate_desired_job`.`id` DESC

to match the GROUP BY.  This will speed up the query a little.

Ordinarily I would suggest a subquery instead of a LEFT JOIN, thereby avoiding some of the "inflation" caused by the join.  But I see that each case involves two select items.

Sorry, there is not much that will speed up the query.


SQL Server "TOP 101" much slower than "TOP 100"

"As far as I know, this happens for the reason that SQL Server in a top
100+ query simply orders all dataset and picks top records (while top
100 and less queries use more sophisticated algorithm)."

Your two queries likely have different execution plans. There's some hard-coding in the engine that says the top 100 will perform differently, but you might have stumbled into a problem like a spill into TempDB.
To find out for sure and get custom advice based on the queries you're facing, you can post the execution plans for download. You can use SQL Sentry Plan Explorer to anonymize them if necessary. Post the actual plans, not the estimated plans.


sql org chart hierarchical table to xml json

You can use the for xml path('') trick to concatenate the strings you need directly in the recursive function.

Something like this should do it for you:

create function dbo.SelectChild(@name as varchar(99)) returns varchar(max)
begin
return
(
  select ',"children":[' +
           stuff((
                 select ',{"name":"' +
                          E.employee + '"'+
                          isnull(dbo.SelectChild(E.employee), '')+'}'
                 from dbo.emp_mgr as E
                 where E.manager = @name
                 for xml path(''), type
                 ).value('text()[1]', 'varchar(max)'), 1, 1, '')+']'
)
end

go

select '{"name":"' + E.employee + '"'+isnull(dbo.SelectChild(E.employee), '')+'}'
from dbo.emp_mgr as E
where E.manager is null

Result:

{
   "name":"Adam",
   "children":[
      {
         "name":"Bob",
         "children":[
            {
               "name":"Charles",
               "children":[
                  {
                     "name":"Frank",
                     "children":[
                        {
                           "name":"Mike"
                        },
                        {
                           "name":"Ned"
                        },
                        {
                           "name":"Oliver"
                        },
                        {
                           "name":"Peter"
                        }
                     ]
                  },
                  {
                     "name":"George",
                     "children":[
                        {
                           "name":"Quinn"
                        },
                        {
                           "name":"Robert"
                        },
                        {
                           "name":"Scott"
                        },
                        {
                           "name":"Ted"
                        },
                        {
                           "name":"Urban"
                        }
                     ]
                  },
                  {
                     "name":"Henry",
                     "children":[
                        {
                           "name":"Victor"
                        }
                     ]
                  }
               ]
            },
            {
               "name":"David",
               "children":[
                  {
                     "name":"Ian",
                     "children":[
                        {
                           "name":"Wes"
                        }
                     ]
                  }
               ]
            },
            {
               "name":"Ethan",
               "children":[
                  {
                     "name":"James",
                     "children":[
                        {
                           "name":"Xavier"
                        },
                        {
                           "name":"Young"
                        },
                        {
                           "name":"Zach"
                        }
                     ]
                  },
                  {
                     "name":"Ken"
                  },
                  {
                     "name":"Larry"
                  }
               ]
            }
         ]
      }
   ]
}


Strategies for reducing data to summaries within a table over time?

I would simply call it "aggregation".

As for elegance, you can do this with a single SQL command without another table using a data-modifying CTE:

For (only) hourly aggregation of rows that are older than a week:

WITH del AS (
   DELETE FROM data d
   WHERE  stamp < now() - interval '7 days'   -- ">" would be wrong
   AND   (date_trunc('hour', stamp) <> stamp  -- not truncated yet.
          OR EXISTS (  -- more than one in same hour
             SELECT 1
             FROM   data
             WHERE  date_trunc('hour', stamp) = date_trunc('hour', d.stamp)
             AND    ctid <> d.ctid   -- except self
             )
         )
   RETURNING *
   )
INSERT INTO data(stamp, data, count)
SELECT date_trunc('hour', stamp), data, sum(count)
FROM   del
GROUP  BY 1,2;

Only replaces rows that need replacing. Repeated calls would not repeat writes.

If you know a lower bound where all rows have been aggregated already, add another WHERE condition to make it faster:

AND stamp > $lower_bound

Obviously, you need an index on stamp or, ideally, a partial index only including rows younger than $lower_bound that would need to be recreated from time to time. Related:

PostgreSQL partial index unused when created on a table with existing data
Add datetime constraint to a PostgreSQL multi-column partial index

Using ctid since no PK is defined.

In-order sequence generation
How do I decompose ctid into page and row numbers?

You may want to VACUUM FULL data; to reclaim table bloat after that. But be aware of implications. Maybe pg_repack is for you:

VACUUM returning disk space to operating system

Aside: DISTINCT and GROUP BY was redundant in your original query. And you were aggregating rows younger than a week instead of older.


You indexed the value of width1 but then you added another number to it and did the absolute value of that.  The value has changed so much that the system no longer knows how to associate that to the index.

The answer here is to not do any math on width1, height1, width2, height2 but instead to do it on width and height only since those are passed in (not indexed values).  This query isn't any prettier but it should turn to your indexes at least.

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE
    (
        (width1  between (width-10) and (width+10)
            AND height1 between (height-10) and (height+10)) OR
        (width1  between (height-10) and (height+10)
            AND height1 between (width-10) and (width+10)) OR
        (width2  between (width-10) and (width+10)
            AND height2 between (height-10) and (height+10)) OR
        (width2  between (height-10) and (height+10)
            AND height2 between (width-10) and (width+10))
    )

Even better would be to pass in height_min, height_max, width_min, width_max (instead of just height and width) and then you don't have to do the math on every row:

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE
    (
        (width1  between (width_min) and (width_max)
            AND height1 between (height_min) and (height_max)) OR
        (width1  between (height_min) and (height_max)
            AND height1 between (width_min) and (width_max)) OR
        (width2  between (width_min) and (width_max)
            AND height2 between (height_min) and (height_max)) OR
        (width2  between (height_min) and (height_max)
            AND height2 between (width_min) and (width_max))
    )


Returning data based on if another column in another table is NULL

Would it be better to use a NOT IN or NOT EXISTS query?

Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter.

This should work for what you need to do:

SELECT
      [Call_Num],
      [Call_Cust_Num],
      [Call_Cust Name],
      [Call_Status]
FROM tableCall
JOIN tableREPORTS
ON tableCALL.Call_Num = tableREPORTS.Report_Call_Num
WHERE tableCALL.Call_Status = 'COMP'
  AND tableCALL.Call_Num in(select tableREPORTS.Report_Call_Num from tableREPORTS where tableREPORT.Report_Billing_Num IS NULL)
ORDER BY Call_Status

  How would i have it so duplicated aren't shown?

If there are duplicate values in tableREPORTS.Reportz_Call_Num, then eliminating the join should eliminate duplicate records in the results. However, you may still get duplicate records in the results if the tableCall.Call_Num column isn't unique or a primary key.


T-SQL How to grant role to user with ADMIN/GRANT option in SQL Server 2008

For the specific code you used in the question you cannot use WITH GRANT OPTION which is normally used to grant privilege so the the user can grant same to other users.

For details and example see this:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql

  Permissions

  Adding members to flexible database roles requires one ofthe
  following:

  Membership in the db_securityadmin or db_owner fixed database role.
  Membership in the role that owns the role.
  ALTER ANY ROLE permission or ALTER permission on the role.

  Adding members to fixed database roles requires membership in the
  db_owner fixed database role.

For privileges using GRANT statement see these links:

https://blog.sqlauthority.com/2010/04/03/sql-server-difference-between-grant-and-with-grant/
http://sqlity.net/en/1884/grant-option-delegate-permission-management/
http://sqlity.net/en/1893/grant-using-grant-grant-option-roles/


Most effective way to determine the minimum set of permissions for user

This should be available from the developer who wrote the software. However, on occasions this information is not available - for instance if the software is written by a third party vendor. Most vendors tend to say it needs sa or db_owner which is generally down to poor coding on their part if it turns out to be the case.

If this information is not available from the developer of the software then your best solution would be to run a profiler trace against that user account over a set period of time - this should give you the majority of the objects that it needs access to.

Best practice would be to set up a test environment and put the trace on that - you don't really want to put a trace on a production database unless it is absolutely necessary - and expect significant testing before you can change the production accounts permissions.

I hope this helps you.