Query tuning help needed (Hash Match and Table Scans)

Show the DBA those Eager Index Spools in the query plan. SQL Server is so desperate for supporting indexes here, it is creating its own temporary ones on every execution!

There is always more to see in the sqlplan file, so please do upload it. A statistics-only copy of the tables in question (or table and index definitions at the very least) would also be useful if you want more than general advice).

This answer was originally a comment on your question. I'll update it if a query plan or DDL is provided.


If the parameters are the same (I'm assuming that's what's meant by exact same query), it shouldn't be parameter sniffing (users get a bad plan for the wrong parameter(s)), but rather users are getting different plans for the same parameter(s). It could be because of settings like quoted_identifier and arithabort, which you can compare in sys.dm_exec_sessions for the fast user and the slow user, or it could be because they have different default schemas and objects are referenced without the schema prefix. Parameter sniffing may still be involved (hence why one of them has a bad plan).


Mysql INSERT INTO .. ON DUPLICATE KEY UPDATE

Use UPDATE image_count = VALUES(image_count).

VALUES() expects a name from the columns you are inserting into, not the alias in the query. The query should be:

INSERT INTO countingTable (image_count, article_id)

SELECT COUNT(article_id) AS sum, article_id
FROM imageTable
GROUP BY article_id                  -- I suppose you skipped that line?

ON DUPLICATE  KEY UPDATE image_count = VALUES(image_count) ;

If you want the new values to be added to the existing ones, use:

...
ON DUPLICATE  KEY UPDATE image_count = image_count + VALUES(image_count) ;


How important are transaction log VLFs for performance?

What is a virtual log file?

SQL Server divides the transaction log file for each database into smaller chunks, called 'virtual log files' (or VLFs for short). Their primary function is as truncation markers at a log backup, i.e. the SQL Server will only clear (and mark available for re-use) VLFs that are completely empty. MSDN has a piece on Transaction Log - Physical Architecture.

What determines the number of VLFs?

Each time a log file grows (whether via autogrowth or manual growth), the new section of the log file is divided into a number of VLFs, purely based on the size of the new section (the existing transaction log is left alone). So, small autogrowth settings (i.e. the 10% autogrowth that is the default) will cause a large number of VLFs to be created.

What are the implications of a large number of VLFs?

The primary issue a large number of VLFs causes are:

Slow recovery (recovery is a phase during database restore where completed transactions are written to the data pages and incomplete transactions are rolled back).
Slow log-reader performance in a database mirroring setup.
Slow performance during database snapshot creation (note that this also includes certain functions of DBCC since they use database snapshots in the background to facilitate consistency checks without blocking).

How can I find out how many VLFs my database has?

DBCC LOGINFO will return 1 row for each VLF in your database's transaction log. This question has a couple of useful scripts for applying that across all databases on a server.

How many VLFs is too many?

That's a judgment call you'll have to make for yourself. My personal rule of thumb is that under 50 isn't worth messing with, and over 100 (or so) and I fix autogrowth settings and  make a mental note to (in the next maintenance window) shrink and regrow the log (as below).

Help! I have eleventy billion VLFs and my database recovery takes all day!

Short outline (from Kimberly Tripp's blog):

Make sure you're the only connection active in the database (so, do this during a maintenance window)
Backup the transaction log to disk (using BACKUP LOG)
Run DBCC SHRINKFILE with TRUNCATEONLY to shrink the log file to the smallest size possible.
Run ALTER DATABASE [...] MODIFY FILE [...] SIZE=newsize to resize your transaction log back up in a single step**.

** Note - if you have a very large log file (tens of GBs or more), you may want to resize in multiple steps to get an appropriate number of VLFs with an appropriate size to avoid excessively 'chunky' log backups. Since VLFs are the unit of truncation they also determine the log backup sizes, as detailed in Kim's blog.


Logical replication - Manually triggering update of data on destination tables (after schema change)

You would want to add the new column to the subscriber with the appropriate default already in place.  You will have to fish that appropriate value out of the publisher, I don't know of a better way to get it.  After the column is added, you might want to either drop the default, or turn it back to now().  If the only way the table gets inserted/updated is from the publication, then this isn't necessary as the publisher always sends the actual value and so doesn't use the default.  But leaving the unused default in place seems like a recipe for confusion.

begin;
alter table pgbench_accounts add column newcol2 timestamp not null default '2019-03-26 09:44:56.391128';
alter table pgbench_accounts alter newcol2 set default now();
commit;

Note that in version 10, this will still be pretty resource intensive, as adding a column with a non-NULL default requires a complete table re-write the subscriber, just as it did on the publisher. (This was changed in version 11 to use "fast default"). But the intensive work is confined within each server, not the connection between them.


How to store user's language knowledge (150+ languages) in mysql DB

This is a classic situation where you use a joining table (a fiddle based on this can be found here. It was done much later than the original answer - better candidates for PRIMARY KEY of the language table!.

CREATE TABLE user
(
  user_id INT,
  user_name VARCHAR(25),
  PRIMARY KEY (user_id)
);

CREATE TABLE language
(
  iso_code CHAR(2),  -- or whatever you want the PRIMARY KEY to be.
  language_name VARCHAR(30),
  PRIMARY KEY (iso_code)
);

CREATE TABLE user_language
(
  ul_user_id INT,
  ul_iso_code CHAR(2),
  PRIMARY KEY (ul_user_id, ul_iso_code),
  FOREIGN KEY (ul_user_id) REFERENCES user (user_id),
  FOREIGN KEY (ul_iso_code) REFERENCES language (iso_code)
);

I forgot to mention that the FOREIGN KEYs must point to a PRIMARY KEY or a UNIQUE KEY (and not some other arbitrary field) in the referenced table.

And for completeness, you should put a PRIMARY KEY on your two joining fields. This will help query response time and avoid multiple entries of the same data. Whatever your app does, always try to enforce RI (Referential Integrity) in the database.

This query shows how to get the user id and name for anyone who knows both English and German.

SELECT ul_user_id
    , user_name
FROM user_language ul
    INNER JOIN language l ON ul.ul_iso_code = l.iso_code
    INNER JOIN user u ON u.user_id = ul.ul_user_id
WHERE l.language_name IN (
    'English'
    , 'German'
    )
GROUP BY ul_user_id, user_name
HAVING COUNT(*) = 2;

The HAVING COUNT(*) = 2 clause limits the results to only people who know both two languages.  If the IN (...) clause had 3 different languages, you'd need to modify the HAVING clause to reflect that, like:  HAVING COUNT(*) = 3


Here you have nice article how to link oracle to your SQL Server and query it.

For data import you can you SELECT INTO statement or make SSIS package for that.


A CURSOR with the above query as it's definition would allow you to insert the records into the event log one-by-one.

Another option you may want to look at is querying sys.dm_exec_query_stats:

SELECT TOP 10
    st.text,
    qs.max_logical_writes,
    qs.max_logical_reads,
    qs.max_elapsed_time,
    deqp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text( qs.sql_handle ) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS deqp
ORDER BY
    qs.max_elapsed_time DESC

This query will return the top 10 longest running queries along with their plans. This is just a sample, it could easily be tweaked, and may point you in another direction.


Select Count on Big table take 15 minutes

Yes, it could be normal. With a "large table", you are likely needing to do a lot of I/O (unless you're using InnoDB and you've got a huge buffer pool allocated.)

Unless you're paying for Amazon Provisioned IOPS, your I/O rate is going to be somewhat constrained on shared volumes.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html

So, two basic approaches to addressing the issue: 1) increase the rate at which you can perform I/O from storage (i.e. pay for Provisioned IOPS), and 2) re-organize your data so that requests don't require as much I/O. And that's going to depend somewhat on which storage engine you're using (MyISAM, InnoDB, or something else), but the design goal would be the same, get a much smaller index which MySQL can scan, rather than scanning the entire table. Use EXPLAIN to determine the execution plan MySQL is using, whether it's going to use your index.

Also, you might consider implementing a more advanced approach, for example, if there is a large portion of the table that is "static" history, where the count of those rows won't change. Get the count of those rows separately, and store that result.

For example, all rows that have a date column before a given value... get that count and store it in a small table. This would only have to be done once:

 CREATE TABLE my_counts (before_dt DATE, mycount BIGINT);
 INSERT INTO my_counts (before_dt, mycount)
 SELECT '2014-11-01' AS before_dt, COUNT(1) AS mycount
  FROM mytable t
 WHERE t.dt < '2014-11-01';

Then, to get an "up-to-date" count, you can reference that stored value, and then you'd only need to get a count of rows that aren't included in that count. An index on the dt column would be appropriate. Combine the result from:

SELECT COUNT(1)
  FROM my_table t
 WHERE t.dt >= '2014-11-01'

With the result from:

SELECT mycount
  FROM my_counts c
 WHERE c.before_dt = '2014-11-01'

That is, you can live with a "stale", cached count of a portion of the table.


multi-tenant data related to multiple customer?

Customers come and go.  Sometimes they have the right to take their data with them.  To extract a subset of data from one schema where the data could be anywhere and the only marker might be a tenant number is a lot harder than putting each customer's data in one schema.

One solution I have seen used is to have a master schema with the metadata concerning the unit of work that is relevant to your application.  So if your application sells something you have tables that list the types of sales, types of sales terms, lists of provinces, states, countries.

Then in each customers schema you have child tables which can extend the parent types with additional columns.  These tables have a primary key which is foreign keyed to the master tables.

If your ORM solution supports inheritance this can work quite well.  Reporting gets a bit tedious as you have additional joins to the master tables.


Implement "account" transactions in postgresql

Good Idea For deposits: Allow deposit money directly as an insert.
Bad Idea For withdraws: Check balance > amount of withdraw before continue.

Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.

I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?

BEGIN;
UPDATE accounts
  SET balance = balance - 100
  WHERE name = 'Bob';
UPDATE accounts
  SET balance = balance + 100
  WHERE name = 'Sally';
END;

If the balance on accounts would go under 0 with the deduction, it'll simply fail there.
If it passes then you will see bob's balance go down for your transaction.
Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.

The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that record until the transaction is committed.


Restarting database server or just the database

You can cycle the database by doing:

alter database YourDatabase
set offline
with rollback immediate; -- this line will rollback ongoing transactions
go

And then to bring the database back online you can do:

alter database YourDatabase
set online;
go

This is a way to "restart" the database itself directly within SQL Server, without bouncing either the SQL Server service(s) or the server operating system itself.

As for the question of "would that help?", there is no way for us to answer that.  You should be asking the vendor why do I need to restart the database server?  Oftentimes people that don't know what they are particularly doing will cycle an entire environment after a migration/upgrade/change.  Why?  "Just in case".

If the vendor made a change directly to the database (DDL/DML, whatever) then that change takes effect immediately.  So you really need to follow up with the vendor to find out why they want the database server restarted.


What mechanisms do you use to notify third parties of future schema changes?

We always strived to make schema changes backward compatible. There are probably situations where this isn't possible, and obviously situations where they want to know so they can take advantage of new features, but we were able to do this consistently without breaking anything. I wrote a series of tips on mssqltips.com about this (look under "Database Development" here).

Extended properties aren't a very useful mechanism for this, IMHO. Since nobody is required to ever look at these, and since many tools ignore them completely, you may as well write out sticky notes and paste them on their monitors. How you would notify them in specific cases I guess depends on what impact the change will have on them and whether or not there are other ways you can implement the change to reduce or eliminate that impact.


DB2 Load Utility Primary Key vs. Unique Index

Documentation says:

It is important to understand that there is no significant difference between a primary key or unique key constraint and a unique index. To implement the concept of primary and unique key constraints, the database manager uses a combination of a unique index and the NOT NULL constraint


DELETE deletes rows as it sees them while using record locks. The delete actually happens at commit time. A log of the changes is made place so other transactions that started before the delete can still see the values.

If an UPDATE happens a small period later, then either a deadlock will happen, or it will lock wait and return no rows found.

So main thing to note is programming:

always handle errors like deadlocks, there's no obvious way for the database to resolve to consider your business case which is why it exits.
use transactions when you need all or none approaches to data changes.


Creating an auditing strategy when a user has been defined in a table instead of in SQL

Adding to what @DForck42 said- username is best left to the application layer. But since these tables don't have too many changes everyday,I would include a

     IsDeleted (bit)

column and implement a soft delete (1 or 0).


There are a couple ways that you can get the final result that you want. You can use PIVOT or you can also use an aggregate function along with a CASE expression, but in order to return multiple rows for each Type you'll need to create a unique value that you can GROUP BY.

I'd recommend using a windowing function like row_number to first generate a unique value for each row in your table partitioned by the TYPE.  You'd write this similar to:

select
  [Type],
  [Value],
  rn = row_number() over(partition by [Type] order by (select 1))
from yourtable;

This creates a unique value for each row in your table by Type aka Credit orDebit`. This is needed when you aggregate your data so you will return more than one row.

Once you've created this row number, you can convert your data into columns by using either the PIVOT function or CASE with aggregate.

select
  Credit = max(case when [Type] = 'C' then [Value] end),
  Debit = max(case when [Type] = 'D' then [Value] end)
from
(
  select
    [Type],
    [Value],
    rn = row_number() over(partition by [Type] order by (select 1))
  from yourtable
) d
group by rn;

Or:

select
  Credit = C,
  Debit = D
from
(
  select
    [Type],
    [Value],
    rn = row_number() over(partition by [Type] order by (select 1))
  from yourtable
) d
pivot
(
  max(value)
  for [Type] in (C, D)
) p;

Here is a demo. Both of these return the result:

| Credit | Debit |
|--------|-------|
| 381.22 |    80 |
|  25.49 | 25.48 |
| 705.56 | 25.49 |

If you need the Credit or Debit data to appear specifically in certain rows, than you would need to provide some additional data to tie each Credit or Debit together. These versions just convert the rows into columns of data.


How can I reset Aborted_connects in MySQL?

The reason FLUSH STATUS doesn't reset this is because its a function in MySQL rather than a simple variable (code ref).

Using gdb assignment, you should be able to:

gdb -p $(pidof mysqld) -batch \
  -ex 'set variable Connection_handler_manager::m_instance->m_aborted_connects = 0L' \
  -ex continue \
  -ex quit

Otherwise you don't have much choice apart from a restart.


Transfer of data (lots) from NAS based MariaDB to Postgres at AWS

Trying to do a fast and reliable insert over an intermittent connection will be a headache. I would:

Use something like rsync with compression to get your endpoint files up to a centralized, reliable server.
Once you believe the files are good, do a bulk load from that centralized location to the DB. You could then use the COPY command, a flat file foreign table wrapper, or get fancier with an ETL tool.


SQL SSDT Database Projects: Source Control Lookup Data

There is no specific built-in support for lookup data in SSDT at this time.  As you noted in the "related" links in your question, the currently accepted practice is to script out lookup data in post deploy scripts using MERGE statements.  This can become unpleasant as the size of lookup data grows.

Microsoft actually mentions "reference data" as a primary example of the use of post deployment scripts.  See How to: Specify Predeployment or Postdeployment Scripts:

  These scripts can be used for many purposes. For example:
  ...

  You can insert reference data that must exist in a table in a post-deployment script

You could potentially go outside of SSDT and introduce SSIS packages with flat file sources.  This might be a better experience depending on the preferences and expertise of those on your team.


Estimating IO Requirements for Bursty Usage

The primary metric I've always considered for IO in SQL Server is not IOPs or Disk Queue Length, but disk throughput (sec/reads and sec/writes).  Overall, databases aren't about how many operations you can throw at a disk, but how quickly those operations are completed.  The general rule of thumb is to have less than 20ms/operation (though lower is always better).  More detail can be found in this article.

Disk Queue Length is a bogus stat and no longer relevant.  The problem with it is that the value measures the queue for a single drive, but now that we live in an age of RAIDs, SANs, and other distributed storage, there's no way to properly translate this value to a meaningful number.  A great starting place for performance metrics is this poster from Quest/Dell that gives you a lot of stuff and explanations for why or why not they are important.  You don't have to use all of them, but they are a start.

In order to test your IO, you have to understand your workload at its peak.  How many transactions and how much is cached?  Unless you know and have measured these, it's really hard to judge.  You can create work loads and use tools like SQLIO to test your storage, but you will need workload patterns in order to build a proper test.

Finally, a note on AWS:  To my knowledge, Amazon will not guarantee IO performance in AWS.  This is primarily because the storage is a massive shared resource and it's impossible to gauge the patterns of you and your neighbors on a particular area of storage (see the Noisy Neighbor problem).

My recommendation would be to allocate as much memory as possible.  SQL Server will only push stuff out of memory if it's under pressure and space in the buffer pool (based on LRU-K).  So if you're buffer pool can store most of the database in memory, you can mitigate some of the bursty performance.  Also, consider tactics that can keep cache objects "warm".  Finally, keep an eye on SQL 2014 and the new Hekaton feature.


Postgresql. Real-time copy of a database to another disk on the same server

If the first database "loses data", it's liable to tell the second database to "lose data to match me". (Depends on exactly what you mean by "lose data".) So reduce the chances of your database losing data due to a single disk failure by storing the database on an array: RAID 1, RAID 5, RAID 6, or RAID 10. I recommend RAID 6.

It's not clear whether you've read the docs for PostgreSQL's High Availability, Load Balancing, and Replication. Streaming replication is probably worth looking at.

To mitigate the risk of "losing data", you need to be precise about losing data means. Mitigating the risk of disk failure is different than mitigating the risk of an application deleting data by accident.


Character varying index overhead & length limit

In Postgres, a unique constraint is implemented with a unique B-tree index. Per documentation:

  Adding a unique constraint will automatically create a unique btree
  index on the column or group of columns used in the constraint.

Indexes use the same basic storing mechanisms as tables: arrays of data pages. Indexes have some minor additional features. Per documentation:

  In a table, an item is a row; in an index, an item is an index entry.

  Every table and index is stored as an array of pages of a fixed size
  (usually 8 kB, although a different page size can be selected when
  compiling the server).

The maximum size of an index entry is a third of a data page, I quote Peter Eisentraut on this:
PostgreSQL primary key length limit

That's 2730 bytes (or a bit less). Meaning your UNIQUE constraint is not possible.

I would consider adding a redundant column with a hash value and create a unique index on that.
You could use the built-in function md5() or something more efficient for huge values.

Here is a related solution with a focus on performance:
Index max row size error


Temprary tables on Mariadb with no activity on the server

"slow query log doesn't registered anything" -- Decrease long_query_time down to 1 or less.  (Even 0 if you get desperate.)
Temp tables may be in RAM or on disk, depending on several things.  Please provide SHOW CREATE TABLE for any tables that might involve temp tables.
The prime candidate for going straight to disk (and ignoring the two ...table_size settings) is when there is any kind of TEXT or BLOB column in the query.  Another case is that it will fill up the 32M you have configured, then copy it to disk to continue.
"/tmp and i see nothing there" -- How are you looking?  The code probably creates the disk table, then "rm's" it immediately.  After that it proceeds to use it.  If a crash occurs, the file is already gone, so there is no trash left behind.  (Magic!)


Stored procedure with ROW instead of a list of parameters

Sure.  You can even use the predefined %rowtype record.

CREATE OR REPLACE PROCEDURE insert_row( p_rec IN table_name%rowtype )
AS
BEGIN
  INSERT INTO table_name
    VALUES p_rec;
END;


Why do innodb tables get corrupted so easily?

Innodb is rather advanced and complex engine than myisam. It is a default storage engine for reasons...

you said the system is upgraded from 5.1 to mariadb 10 (eqv to 5.6). So the upgrade path was mysql 5.1 -> maria 5.5 -> maria 10?

Can you share the query?
Does the issue repeats if you: Kill query and then restart?
Is there anything apart from query running while you're shutting down?
I'd try to find out if there is related bug report.

Solutions to consider even before we identify exact error here:
- separate instance for reporting.
- optimize the query.


A query submitted from different applications has differing DOP

There are 2 standard ways to affect this

MAXDOP hint per query
max degree of parallelism Option for the whole server

The SQL Server 2008 resource governor and some trace flags will affect it. I doubt Tibco is using the resource governor so it could be a trace flag.

One more option I've seen is SET IMPLICIT TRANSACTIONS ON.

If no trace flags, no implicit transactions, and no MAXDOP hints, then the queries are not the same.

Edit, after question update

You should be able to change some prepare and/or cursor setting. Example: http://www.streamreader.org/serverfault/questions/177391/impact-on-sql-2000-performance-of-jdbc-selectmethodcursor


How to make inet_server_addr() return localhost in spite of ::1/128

@cavila's answer in comments also answers the question "Why does select inet_server_addr() || '' return a result like 192.168.44.195/32?"

inet is a data type. To suppress the number of bits and get a text IP like '192.168.51.195', use select(host(inet_server_addr())).


Why VIEWS are showing in Data Transfer Service SQL

Either use the SSMS Copy Database Wizard

Or use the SSMS Backup Database UI to backup and restore to the instance's default backup location.


How to solve repetitive ORA-2391 error in DBeaver 4.3.3.1

I found the solution thanks to Balazs. There are a couple of options in the Preferences window that could generate multiple sessions.

The first one is at Preferences > General > Editors > SQL Editor, Connections group and "Open separate connection for each editor" option.
The other one is at Preferences > Database > Metadata, Metadata group and "Open separate connection for metadata read" option.

Both options must be unchecked in order to avoid creation of multiple sessions for same connection, therefore to avoid ORA-02391 error.


Is there a way to give GRANT OPTION recursively for subsets of permissions?

While having SELECT (or another permission) on the entire database implies having SELECT on an object within the database, having the GRANT OPTION for SELECT on the entire database does not imply you have the GRANT OPTION for SELECT on an object in the database.

Correct. There is a difference between "actual" / "explicit" permissions and "effective" permissions. Granting SELECT at the Database-level is a distinctly different permission than granting SELECT on one or more objects within that Database. You would see this difference when looking in the System Catalog View sys.database_permissions. Hence, SELECT permissions on individual tables is not an actual subset of the Database-level SELECT permission. The same holds true for SELECT permission on a Table as opposed to SELECT permission on individual columns in that Table.

Now, while it would help to get some feedback / clarification on the overall goal of this request, it at least appears -- based on the example code in the question -- that the desire is to have a deployment / rollout Login (or Logins) that are not in the sysadmin fixed server role. To that end, you can still accomplish this type of setup using Module Signing. The idea is to create a Certificate and Certificate-based Login and associated User that cannot be Impersonated (i.e. can't log in or become current Login/User via EXECUTE AS) but can hold all of the required permissions necessary to carry out the tasks that the deployment process needs to do. Then, you create one or more Stored Procedures that will execute the required commands. Signing the Stored Procedure(s) with the same Certificate that was used to create the Login and User will allow the permissions granted to the Certificate-based Login and User to apply to code within the Stored Procedure. Given the dynamic nature of the operation, it will need to be done via Dynamic SQL, but this works just fine with Module Signing.

This approach allows you to have a generic set of permissions (assigned to the Certificate-based Login / User) that can be used to assign other permissions. For the moment, it seems easiest to put the User in the db_owner fixed database role as that allows for doing anything you would need to do. And since Module Signing gives the permissions to the code, not the Login / User / Role executing the module (which is how it works with Impersonation via EXECUTE AS), anyone who can execute that code can only ever do what the code is set up to do. In this way, Module Signing allows for much more granular control over extending permissions than Impersonation (it doesn't matter what the permissions granted to the Certificate-based Login / User can do, it only matters what the signed code actually does). And if someone changes that signed code, then the module loses the signature, alerting you to the change, at which time you can review the changes and if ok, then re-sign the module.

Please see the example below that allows for granting SELECT on a particular object to a given role. It is very easy to extend the example to allow for creating the new Role as well as anything else that needs to be done as part of the deployment process.

Solution Setup, Part 1 (General Supporting Structure)

USE [master];
GO

CREATE CERTIFICATE [DeploymentPermissionsKey]
  ENCRYPTION BY PASSWORD = 'not_really_a_password'
  WITH SUBJECT = 'Deployment Permissions';

CREATE LOGIN [DeploymentPermissions]
  FROM CERTIFICATE [DeploymentPermissionsKey];

GRANT VIEW SERVER STATE TO [DeploymentPermissions];

DECLARE @Certificate VARBINARY(2000),
        @PrivateKey VARBINARY(2000);

SELECT @Certificate = CERTENCODED(CERT_ID(N'DeploymentPermissionsKey')),
       @PrivateKey = CERTPRIVATEKEY(CERT_ID(N'DeploymentPermissionsKey'),
                                    'EncryptPass', 'not_really_a_password');

---

USE [tempdb];

--DROP CERTIFICATE [DeploymentPermissionsKey];
DECLARE @CertSQL NVARCHAR(MAX) = N'
CREATE CERTIFICATE [DeploymentPermissionsKey]
  FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @Certificate, 1) + N'
   WITH PRIVATE KEY ( BINARY = ' + CONVERT(NVARCHAR(MAX), @PrivateKey, 1) + N',
                      DECRYPTION BY PASSWORD = ''EncryptPass'',
                      ENCRYPTION BY PASSWORD = ''not_really_a_password''
                    );
';
PRINT @CertSQL;
EXEC(@CertSQL);

--DROP ROLE [DeploymentRole];
CREATE ROLE [DeploymentRole];

--DROP USER [MrDeploy];
CREATE USER [MrDeploy] WITHOUT LOGIN;

ALTER ROLE [DeploymentRole] ADD MEMBER [MrDeploy];

--DROP USER [DeploymentPermissions];
CREATE USER [DeploymentPermissions]
  FROM LOGIN [DeploymentPermissions];

ALTER ROLE [db_owner] ADD MEMBER [DeploymentPermissions];

Solution Setup, Part 2 (Allow Granting Permission to Role)

Please note that due to needing to use Dynamic SQL, we need to protect against SQL Injection by checking / verifying the string input parameters: @RoleName and @ObjectName.

USE [tempdb];
GO
CREATE
--ALTER
PROCEDURE dbo.GrantObjectPermissionsToRole
(
  @RoleName [sysname],
  @ObjectName NVARCHAR(261),
  @PermissionID TINYINT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

-- Check parameter values to prevent errors and SQL Injection:
IF (NOT EXISTS(
               SELECT dp.*
               FROM   sys.database_principals dp
               WHERE  dp.[type] = 'R' -- DATABASE_ROLE
               AND    dp.[name] = @RoleName
              )
    )
BEGIN
  RAISERROR(N'Role does not exist: %s', 16, 1, @RoleName);
  RETURN -1;
END;

IF (OBJECT_ID(@ObjectName) IS NULL)
BEGIN
  RAISERROR(N'Object does not exist: %s', 16, 1, @ObjectName);
  RETURN -2;
END;

SET @SQL = N'GRANT '  + CASE @PermissionID
                           WHEN 1 THEN N'SELECT'
                           WHEN 2 THEN N'EXECUTE'
                           ELSE N'Invalid_PermissionID_'
                                + CONVERT(NVARCHAR(10), @PermissionID)
                         END
            + N' ON ' + @ObjectName
            + N' TO ' + @RoleName
            + N' AS [dbo];';

PRINT @SQL; -- debug
EXEC(@SQL);

RETURN;
GO

GRANT EXECUTE ON dbo.[GrantObjectPermissionsToRole] TO [DeploymentRole];

ADD SIGNATURE TO dbo.[GrantObjectPermissionsToRole]
  BY CERTIFICATE [DeploymentPermissionsKey]
  WITH PASSWORD = 'not_really_a_password';

Example Setup

USE [tempdb];
GO

CREATE TABLE [dbo].[TestTable] ([ID] INT NULL);

CREATE ROLE [TestRole];

CREATE USER [TestUser] WITHOUT LOGIN;

ALTER ROLE [TestRole] ADD MEMBER [TestUser];

Tests

Test 1 shows that initially, TestUser, who is in TestRole, does not have permission to SELECT from TestTable.

Test 2 shows that the deployment User, MrDeploy, also does not have SELECT permission on TestTable. Additionally, MrDeploy does not even have the ability to grant any permissions to TestRole. However, MrDeploy can execute the Stored Procedure which does grant the SELECT permission to TestRole.

Test 3 shows that TestUser, via its membership in TestRole, now does have SELECT permission on TestTable. Using sys.fn_my_permissions() we can see the "effective" permissions which cascade down to individual columns. However, selecting from sys.database_permissions we can see the "actual" permission which is just on the Table itself (showing that actual permissions do not cascade).

Test 4 grants the Database-level SELECT permission to MrDeploy so that it can be tested against the sys.fn_my_permissions() functions (to see how the permission cascades down to subobjects) and the sys.database_permissions system catalog view (to see that there are no object-level permissions at all). This again illustrates the difference between "effective" and "actual" / "explicit" permissions.

--- Test # 1 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER AS [CurrentUser];

--- Test # 2 ---
EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

GRANT SELECT ON [dbo].[TestTable] TO [TestUser]; -- error:
-- Cannot find the object 'TestTable', because it does not exist or you
--    do not have permission.

GRANT SELECT ON [dbo].[TestTable] TO [TestUser] AS [dbo]; -- error:
-- Cannot find the user 'dbo', because it does not exist or you do not have permission.

SELECT dp.*
FROM   sys.database_principals dp
WHERE  dp.[type] = 'R' -- DATABASE_ROLE
AND    dp.[name] = N'TestRole'; -- no rows due to no VIEW SERVER STATE permission

EXEC dbo.[GrantObjectPermissionsToRole]
  @RoleName = N'TestRole',
  @ObjectName = N'TestTable',
  @PermissionID = 1;
-- Success!

SELECT * FROM [dbo].[TestTable]; -- error (still):
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER AS [CurrentUser];

--- Test # 3 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- Success!!

SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');

SELECT *,
       USER_NAME([grantee_principal_id]) AS [Grantee],
       USER_NAME([grantor_principal_id]) AS [Grantor]
FROM   sys.database_permissions
WHERE  [class] = 1 -- OBJECT_OR_COLUMN
AND    [major_id] = OBJECT_ID(N'dbo.TestTable');

REVERT;
SELECT SESSION_USER AS [CurrentUser];

--- Test # 4 ---
GRANT SELECT TO [MrDeploy];

EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');

SELECT *,
       USER_NAME([grantee_principal_id]) AS [Grantee],
       USER_NAME([grantor_principal_id]) AS [Grantor]
FROM   sys.database_permissions
WHERE  [class] = 1 -- OBJECT_OR_COLUMN
AND    [major_id] = OBJECT_ID(N'dbo.TestTable');

REVERT;
SELECT SESSION_USER AS [CurrentUser];


Combine indexes to optimize "OR" queries

Query

Note this equivalent sorter syntax for your query:

SELECT *
FROM   contacts
WHERE  'Cassidy' IN (firstname, lastname, nickname);

Resolves to the same expressions in the query tree:

  (('Cassidy'::text = contacts.firstname)
OR ('Cassidy'::text = contacts.lastname)
OR ('Cassidy'::text = contacts.nickname))

The three columns can even have different data types. The single given string literal 'Cassidy' is cast to different data types for this. Exactly like in your original query.

Index

You could use a single bloom index (after installing the additional module, once per database):

CREATE INDEX contacts_bloom_idx ON contacts USING bloom (firstname, lastname, nickname);

Especially if you have more candidate columns like this and query random combinations. Also if you are short on disk / cache memory or the index is rarely needed (so not in cache). Then size matters more and a bloom index is typically small.

But it's better for AND-ed conditions, less so for OR-ed conditions. And the manual advises:

  This type of index is most useful when a table has many attributes and
  queries test arbitrary combinations of them. A traditional btree index
  is faster than a bloom index, but it can require many btree indexes to
  support all possible queries where one needs only a single bloom
  index. Note however that bloom indexes only support equality queries,
  whereas btree indexes can also perform inequality and range searches.

And:

  Only operator classes for int4 and text are included with the module.

For just three columns, three simple btree indexes should be faster and more versatile:

CREATE INDEX ON tbl (firstname);
CREATE INDEX ON tbl (lastname);
CREATE INDEX ON tbl (nickname);

Postgres can rather efficiently combine multiple indexes in a bitmap index scan. Related:

What is a “bitmap index”?
PostgreSQL FTS and Trigram-similarity Query Optimization

A multicolumn index like:

CREATE INDEX ON tbl (firstname, lastname, nickname);

is not very usefull for this. It happens to cover firstname as first column moderately well (not as well as a smaller index on just (firstname)). Additional columns are covered poorly. You are not looking for combinations of columns, so it's the wrong tool.


Which MySQL engine should i use where most operations are read operations

Although MyISAM genrally matches your characteristics, I advise you against using it, for the following reasons:

Integrity, MyISAM is non-trasnactional: yes, I know you wrote you do not need transactions. The thing is, with MyISAM not even a single operation is "atomic". You see, a power failure in the middle of a write (you do have 0.01% writes), may corrupt your data; now even a single row has any integrity.

You don't need transactions just for complex "grouping multiple queries" or "rollback to savepoint". You need transactions to know your data has integrity. MyISAM has nothing of the sort. There is no mechanism to guarantee you data. A corruption may (and will) occur, and either you will not even know it, or you will not be able to fix it. The closest you have is a REPAIR TABLE which at best will fix your table structure.

I say if you case about your data -- MyISAM is a no-go.
Lack of development: no one is actually improving much of the code. Everyone is busy making InnoDB better and better. It's not that by picking on MyISAM you will stay behind: you're starting waaay behind. Do not expect bugfixes as quick as you would for InnoDB.
Poor for live backups. For taking a live backup of your database you will need to place a READ LOCK on your database for the entire operation. It may not be such a bid deal for you; you may also use replication and have the backup execute on the slave (but replication with MyISAM is again so unsafe due to lack of integrity and error-recovery on master). Or you can also use LVM.


As Justin said,  all_tab_modifications is not a real-time accurate count of the number of modifications to a table since the last time statistics were gathered

I recommend you to track this kind of events, see the following  Link


How to enable sharding for all databases/collections by default

Answer is simple. NO!
Every DB and collection in them, must be shard individually. Just because all collections are not created as equal and _id: hashed is not best solution to everything.


The pattern you describe is known as "friend of a friend." Here are a couple of answers on SO which may guide you.

In the relational database world the friends would be found by following foreign keys.  Document DBs, however, eschew explicit foreign keys enforced in the DBMS and join-like functionality in favour of other benefits. It is down to your application to recurse over successive results, even if that "makes no sense."

If you are able to expand your technology stack, a graph database would provide an excellent solution to this particular use-case. You could hold just the user-user connections to get the total list of UserIDs to whatever depth was required, then look each up in MongoDB; or you could hold the attribute values, too, and filter on like-values in the graph DB.

To speed the like-value search in MongoDB you could store a document for each attribute value, each with an array of all users who have that value.  For a given user you would then retrieve his record and the record for the given user's attribute value. The intersection of the user's friends and the attribute's user list are the friends-with-the-same-value.  Two documents only must be read rather than however many friends the given user has. Similarly on update only two documents must be written.  Sadly this does not help with finding friends-of-friends, of course.


You might want to recover the table from your database backup. You do have a backup right? - hot2use

The real question is: why are you creating tables in the SYSTEM tablespace? Don't do that. - a-horse-with-no-name


Contention on tempdb due to high concurrency temp table creation from multiple databases?

We are loading potentially thousands of rows into this temp table and just dumping the whole thing out, basically it aggregates a bunch of data.

Temporary tables can be a big performance and reliability win, when used correctly.

For example, it can be very useful to store a relatively small (and narrow) intermediate result set in a temporary table so SQL Server can compute automatic statistics on the data, or so the user can add specific indexing.

Temporary tables are most useful when they allow the optimizer to produce a better sequence of plans overall than a single monolithic query.

It is also possible to use temporary tables poorly. Simply storing rows in a temporary table before "dumping the whole thing out" sounds counter-productive. Always bear in mind that temporary tables are not free, and can contribute to contention.

Aside from using temporary tables in a sensible way, try to arrange things so you take advantage of temporary table caching. See my article SQL Server Temporary Object Caching for details.

Ensure you are running the latest cumulative update, since there have been several performance optimizations and bug fixes employed, for example:

PFS page round robin algorithm improvement in SQL Server 2016 and 2017 (2017 RTM CU7)
FIX: Heavy tempdb contention occurs in SQL Server 2016 or 2017 (2017 RTM CU5)


PostgreSQL: Can i use tuple in where clause?

Thanks to Peter Vandivier (couldnt flag his comment as the answer)
Yes it is possible:

What have you tried – Peter Vandivier 12 mins ago


SQL Server - How to compress XML inside Trigger?

While XML is certainly more bloated than native datatypes, it is not as bad as most people would assume, or even incorrectly calculate, as long as you are using the XML datatype. The XML datatype (in SQL Server) has internal optimization whereby it stores a dictionary of element and attribute names and a tree structure for the document. Meaning, element names are not doubled, and a name repeated 100 times is listed only once. Also, all structural whitespace (i.e. that's not part of a value) is removed.

Still, even with this optimization, you still run into two issues with XML:

column names are present in each document (i.e. each row of the audit table has to repeat the column names, whereas those column names only exist once in the meta-data that defines the Table that is being audited).
all data is serialized to strings (well, unless you're using "typed" XML, which means you have an associated XML Schema / XSD, but how often does that happen?). Sometimes this can actually help, such as when you have INT values that are positive and a single digit: 0 - 9. Or it can be equivalent when the values are -9 up through -1 and 10 up through 99, since those two ranges represent the same 4 bytes as the INT. If you are wondering why my maths seem so far off, keep in mind that XML data is UTF-16 (Little Endian), meaning: 2 bytes (well, for most of the characters you would regularly deal with, but 4 bytes per character for Supplementary Characters). So the -9 is 2 characters which is 4 bytes, same as 99. But then 3 characters is 6 bytes and it just goes up from there.

The date values are DATETIMEOFFSET which is 8 - 10 bytes. The values shown in the XML have 0 fractional seconds which would be 8 bytes. The values in the XML are in the form of 1900-01-01T00:00:00. That is 19 characters, which is really 38 bytes.

As far as compression goes, you have two choices:

If you are using SQL Server 2016 or newer, you could compress the XML values using the built-in COMPRESS function, and then DECOMPRESS. These two functions are the same as GZip and GUnzip, respectively.
If you are using SQL Server 2005, 2008, 2008 R2, 2012, or 2014, you could get the same GZip algorithm via SQLCLR. This can be coded somewhat easily, or you can just grab the SQL# SQLCLR library (which I wrote, but these two functions are in the Free version), which contains the Util_GZip and Util_GUnzip functions.

In either case, you can save quite a bit of space by compressing the values. However, you also lose the ability to use define any XML Indexes on this data, and you can't query it directly using XML functions such as .value or .nodes. Querying the data can still be done, but you would first need to use either DECOMPRESS or Util_GUnzip (or similar SQLCLR scalar UDF) on the value to turn it back into usable XML.

Regarding attribute-based XML vs. element-based XML: in most cases you are better off with attribute-based as it does take up less space than element-based, and often performs a little better. However, the size difference between the two is much more obvious when the XML is stored as plain text, whether in VARCHAR / NVARCHAR or a text file encoded as ASCII, UTF-8, or UTF-16 / Unicode. But when stored in SQL Server in an XML datatype (variable or column), the internal optimization reduces the required space, especially if there are element and/or attribute names that are repeated quite often.

For example, take all of the rows in master.sys.all_columns.

DECLARE @ElementBased XML;
SET @ElementBased = (
                     SELECT * FROM master.sys.all_columns FOR XML PATH('Row')
                    );

DECLARE @AttributeBased XML;
SET @AttributeBased = (
                       SELECT * FROM master.sys.all_columns FOR XML RAW('Row')
                      );

SELECT @ElementBased AS [ElementBasedXML],
       @AttributeBased AS [AttributeBasedXML],

       DATALENGTH(@ElementBased) AS [ElementBasedBytes],
       DATALENGTH(@AttributeBased) AS [AttributeBasedBytes],
       ((DATALENGTH(@AttributeBased) * 1.0) / DATALENGTH(@ElementBased)) * 100
               AS [XmlAttributeSizeRelativeToElementSize],

       DATALENGTH(CONVERT(NVARCHAR(MAX), @ElementBased)) AS [ElementBasedNVarCharBytes],
       DATALENGTH(CONVERT(NVARCHAR(MAX), @AttributeBased)) AS [AttributeBasedNVarCharBytes],
       ((DATALENGTH(CONVERT(NVARCHAR(MAX), @AttributeBased)) * 1.0) /
         DATALENGTH(CONVERT(NVARCHAR(MAX), @ElementBased))) * 100
               AS [NVarCharAttributeSizeRelativeToElementSize]

       -- The following 2 fields require either SQL# ( https://SQLsharp.com/ )
       -- be installed, or using SQL Server 2016 (or newer), in which case you
       -- would use the COMPRESS function.
       ,DATALENGTH(SQL#.Util_GZip(CONVERT(VARBINARY(MAX), @ElementBased)))
               AS [ElementBasedGZippedBytes],
       DATALENGTH(SQL#.Util_GZip(CONVERT(VARBINARY(MAX), @AttributeBased)))
               AS [AttributeBasedGZippedBytes]

Returns the following (minus the two fields with the actual XML values):

ElementBasedBytes                           1717896
AttributeBasedBytes                         1544661
XmlAttributeSizeRelativeToElementSize            89.92  (rounded-up)

ElementBasedNVarCharBytes                  11778162
AttributeBasedNVarCharBytes                 6923728
NVarCharAttributeSizeRelativeToElementSize       58.79  (rounded-up)

ElementBasedGZippedBytes                     193629
AttributeBasedGZippedBytes                   146881


Problem with View When Restoring a MySQL Backup

The definition of the View must be contained within the dump file.

To see the definitions, run the following command on the dumpfile:

cat backup.sql | grep -A 3 "CREATE ALGORITHM" > backup_views.sql
cat backup_views.sql

You could then drop the offending view(s) and recreate them manually.

If you have another server with the views established already, you should mysqldump the schema only. It will contain the views as well. @DTest and I wrote earlier posts on this.

Views are always processed last (after all tables) in a mysqldump.

This problem may also occur if one view references another, and the order the views are created doesn't take account of that.


sp_BlitzFirst @AsOf Functionality Not Working?

Unfortunately, compatibility level 90 (SQL Server 2005) is not supported.

From the known issues list at: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

"Only Microsoft-supported versions of SQL Server are supported here - sorry, 2005 and 2000. Some of these may work some of the time on 2005, but no promises, and don't file a support issue when they fail. (For example, we know the output tables won't work on SQL 2005 because one of the output fields is a DATETIMEOFFSET datatype, which isn't available in 2005.)"


MySQL - How to make a DATEDIFF in years?

Yes, TIMESTAMPDIFF is the best approach:

select TIMESTAMPDIFF(year,'2011-01-01', now() ) ;  --> 4
select TIMESTAMPDIFF(year,'2011-08-28', now() ) ;  --> 4
select TIMESTAMPDIFF(year,'2011-09-01', now() ) ;  --> 3


Query slow when using function in the WHERE clause

Consider this simplified equivalent:

CREATE OR REPLACE FUNCTION ext.uf_converte_numerico(_input varchar(30))
  RETURNS bigint LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT NULLIF(regexp_replace(_input, '[^0-9]+', '', 'g'), '')::bigint;
$func$;

IMMUTABLE, because it is, and for the reasons Laurenz explained.
PARALLEL SAFE in Postgres 10 or later, because it is. Without the label, functions default to PARALLEL RESTRICTED, which disables parallel queries. This may or may not affect the query on display. But the 15 seconds you reported indicate you are operating on big tables. So it can make a huge difference in other queries.
LANGUAGE SQL to enable function inlining, which won't matter much for the query on display (after you labelled it IMMUTABLE), but will simplify query plans and improve overall performance.
NULLIF as minor simplification.

Aside: your input is varchar(30), which still allows out of range errors for bigint. Either consider varchar(18) to be sure. Or just make it text to remove the ineffective restriction.


Does an index where the last column is unique and already indexed make sense?

I can think about one scenario where such index is needed. If you want another table to have a Foreign Key constraint that refers (RetailerID, ID), you must create a unique constraint (index).  This pattern makes sense in some cases, for instance if you want to have a common parent table which holds common attributes, and multiple detail tables, and at the same time enforce that details are saved in the proper table.

Update. Example to illustrate an idea  :

vehicle (vehicle_id, vehicle_type_id, year, model, ...
         PK (vehicle_id),
         Unique (vehicle_type_id,car_id),
         CHECK (vehicle_type_id IN ('car','truck'))
);

car (vehicle_id, vehicle_type_id default 'car',
     num_passengers, car_type, ....
     PK,FK(vehicle_id, vehicle_type_id) ref vehicle,
     CHECK (vehicle_type_id ='car')
);

truck (vehicle_id, vehicle_type_id default 'truck',  load,
     dimensions, ... ,
     PK,FK(vehicle_id, vehicle_type_id) ref vehicle,
     CHECK (vehicle_type_id ='truck')
);

Thus you enforce that truck's attributes are always stored in truck table, car's - in car, and at the same time refer to vehicle if vehicle type is not important, say vehicle_owner  can look like vehicle_id, person_id, date_from ,date_thru, FK (vehicle_Id) ref vehicle(vehicle_id)).


Calculating percentile using counts of occurrences

I'll walk you through one solution to calculate PERCENTILE_DISC for percentiles 1, 2, ... 100. Apparently there is not an agreed upon definition for percentile. It appears that PERCENTILE_DISC mostly implements the nearest rank method, except that low percentiles are not undefined (see the last bullet point in the wiki).

  One definition of percentile, often given in texts, is that the P-th percentile of a list of N ordered values (sorted from least to greatest) is the smallest value in the list such that P percent of the data is less than or equal to that value.

Per wikipedia, the percentile P (0 < P <= 100) is the value located at ordinal rank n = CEILING (N * P / 100), where N is the number of ordered values. For your problem, N = SUM(COUNT) from the table. For a given percentile P you need to find the nth row in the table. You can find that without exploding all of the values into separate rows by calculating a running total. If you take the row with the largest speed with a running total less than or equal to n then you have the percentile value. One implementation of that is below.

First we'll need a numbers table. These are invaluable for many SQL problems. In this example, I'll only put 100 integers in my numbers table because that's all I need to solve this one, but if you create one on your database you should add more than that.

CREATE TABLE #X_NUMBERS (NUM INTEGER NOT NULL, PRIMARY KEY (NUM));

-- put 100 integers into #X_NUMBERS table
WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b)
INSERT INTO #X_NUMBERS
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e2;

Here is the sample data from the question:

CREATE TABLE #X_SPEED_TABLE (SPEED INT, CNT INT, PRIMARY KEY (SPEED));

-- this is your test data
INSERT INTO #X_SPEED_TABLE
VALUES
(102, 3),
(201, 2),
(205, 9),
(208, 4),
(301, 1),
(303, 2),
(307, 6);

To make the algorithm easier to present let's just start with percentiles 10, 20, ... 100. Suppose I take the sum of the CNT column from my table and multiply it by 0.1, 0.2, ... 1.0. I need to take the CEILING of the values (this is RANK_COL):

╔══════════╦══════════╦═══════╗
║ PERC_COL ║ RANK_COL ║ SPEED ║
╠══════════╬══════════╬═══════╣
║       10 ║        3 ║ NULL  ║
║       20 ║        6 ║ NULL  ║
║       30 ║        9 ║ NULL  ║
║       40 ║       11 ║ NULL  ║
║       50 ║       14 ║ NULL  ║
║       60 ║       17 ║ NULL  ║
║       70 ║       19 ║ NULL  ║
║       80 ║       22 ║ NULL  ║
║       90 ║       25 ║ NULL  ║
║      100 ║       27 ║ NULL  ║
╚══════════╩══════════╩═══════╝

Now I'll look at my data and calculate a running total based on speed. For your sample data:

╔══════════╦══════════╦═══════╗
║ PERC_COL ║ RANK_COL ║ SPEED ║
╠══════════╬══════════╬═══════╣
║       -1 ║        3 ║   102 ║
║       -1 ║        5 ║   201 ║
║       -1 ║       14 ║   205 ║
║       -1 ║       18 ║   208 ║
║       -1 ║       19 ║   301 ║
║       -1 ║       21 ║   303 ║
║       -1 ║       27 ║   307 ║
╚══════════╩══════════╩═══════╝

Let's combine that data and order it by RANK_COL descending and PERC_COL ascending:

╔══════════╦══════════╦═══════╗
║ PERC_COL ║ RANK_COL ║ SPEED ║
╠══════════╬══════════╬═══════╣
║       -1 ║       27 ║ 307   ║
║      100 ║       27 ║ NULL  ║
║       90 ║       25 ║ NULL  ║
║       80 ║       22 ║ NULL  ║
║       -1 ║       21 ║ 303   ║
║       -1 ║       19 ║ 301   ║
║       70 ║       19 ║ NULL  ║
║       -1 ║       18 ║ 208   ║
║       60 ║       17 ║ NULL  ║
║       -1 ║       14 ║ 205   ║
║       50 ║       14 ║ NULL  ║
║       40 ║       11 ║ NULL  ║
║       30 ║        9 ║ NULL  ║
║       20 ║        6 ║ NULL  ║
║       -1 ║        5 ║ 201   ║
║       -1 ║        3 ║ 102   ║
║       10 ║        3 ║ NULL  ║
╚══════════╩══════════╩═══════╝

Now let's find the minimum SPEED value as I loop through the rows:

╔══════════╦═══════╗
║ PERC_COL ║ SPEED ║
╠══════════╬═══════╣
║       -1 ║   307 ║
║      100 ║   307 ║
║       90 ║   307 ║
║       80 ║   307 ║
║       -1 ║   303 ║
║       -1 ║   301 ║
║       70 ║   301 ║
║       -1 ║   208 ║
║       60 ║   208 ║
║       -1 ║   205 ║
║       50 ║   205 ║
║       40 ║   205 ║
║       30 ║   205 ║
║       20 ║   205 ║
║       -1 ║   201 ║
║       -1 ║   102 ║
║       10 ║   102 ║
╚══════════╩═══════╝

Finally, filter out the rows with a -1 value for PERC_COL. You are left with the percentile calculations for percentiles 10, 20, ... 100:

╔══════════╦═══════╗
║ PERC_COL ║ SPEED ║
╠══════════╬═══════╣
║      100 ║   307 ║
║       90 ║   307 ║
║       80 ║   307 ║
║       70 ║   301 ║
║       60 ║   208 ║
║       50 ║   205 ║
║       40 ║   205 ║
║       30 ║   205 ║
║       20 ║   205 ║
║       10 ║   102 ║
╚══════════╩═══════╝

The above algorithm can be implemented in SQL using window functions. Here is one such implementation for N = 1, 2, ... 100:

SELECT
  PERC_COL
, SPEED
FROM
(
    SELECT
    PERC_COL
    , MIN(SPEED) OVER (ORDER BY RANK_COL DESC, PERC_COL ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SPEED
    FROM
    (
        SELECT
          NUM PERC_COL
        , CEILING(s.SUM_SPEED * 0.01 * NUM) RANK_COL
        , NULL SPEED
        FROM #X_NUMBERS
        CROSS JOIN (SELECT SUM(CNT) SUM_SPEED FROM #X_SPEED_TABLE) s
        WHERE NUM <= 100

        UNION ALL

        SELECT
          -1 PERC_COL
        , SUM(CNT) OVER (ORDER BY SPEED ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RANK_COL
        , SPEED
        FROM #X_SPEED_TABLE
    ) t
) t2
WHERE t2.PERC_COL <> -1;

To check my results, I'm going to calculate all of the percentiles using the built-in PERCENTILE_DISC. They match for your sample data and a few other random data sets that I created.

-- this is code to test solutions against PERCENTILE_DISC for percentiles 1, 2, ... 100
-- it's not meant to be a well performing solution
DECLARE
@perc INT,
@speed INT;
BEGIN
    DECLARE @results_table TABLE (PERC INT, SPEED INT);

    SET @perc = 1;
    WHILE @perc <= 100
    BEGIN
        INSERT INTO @results_table (PERC, SPEED)
        SELECT TOP 1 @perc, PERCENTILE_DISC(0.01 * @perc) WITHIN GROUP (ORDER BY SPEED) OVER ()
        FROM
        (
            SELECT SPEED
            FROM #X_SPEED_TABLE xst
            INNER JOIN #X_NUMBERS n ON xst.CNT >= n.NUM
        ) t;

        SET @perc = @perc + 1;
    END;

    SELECT * FROM @results_table;
END;


How to aggregate pairs of id values into groups

Well this worked for the given sample data but not the cycles scenario / may not work for a more complex data set.  Thought I would post anyway as a starter for 10:

DECLARE @t TABLE ( id1 INT, id2 INT )

INSERT INTO @t VALUES
    ( 1, 2 ),
    ( 1, 3 ),
    ( 3, 4 ),
    ( 3, 5 ),
    ( 6, 7 )

    --( 1, 2 ),
    --( 2, 3 ),
    --( 3, 1 )

;WITH cte AS
(
SELECT ROW_NUMBER() OVER( ORDER BY id1 ) group_id, id1 id
FROM @t a
    CROSS APPLY ( SELECT TOP 1 id2 FROM @t c WHERE a.id1 = c.id1 ) d
WHERE NOT EXISTS ( SELECT * FROM @t b WHERE a.id1 = b.id2 )
  AND a.id2 = d.id2
UNION ALL
SELECT group_id, t.id2
FROM cte c
    INNER JOIN @t t ON c.id = t.id1
)
SELECT *
FROM cte
ORDER BY 1, 2


Converting standard_conforming_strings from off to on when importing Postgres dump

Testing with a 8.1 instance, it doesn't look that there is a problem. See these 3 different examples:

Create data

 SHOW standard_conforming_strings ;
 standard_conforming_strings
 -----------------------------
  off

CREATE TABLE bla(t text);
INSERT INTO bla VALUES('abc''def');
INSERT INTO bla VALUES(E'abc\\def');

1. Dump of 8.1 data with 8.1 pg_dump

$ pg81/bin/pg_dump db

this produces (after removing the irrelevant stuff):

COPY bla (t) FROM stdin;
abc'def
abc\\def
\.

Here standard_conforming_strings does not matter because the COPY format is independant from it. When this COPY is executed, it will make no difference whether it's on or off.

2. Dump of 8.1 data with 8.1 pg_dump with --inserts option

$ pg81/bin/pg_dump --inserts db

So COPY is not used.
The dump now contains, for the interesting part:

INSERT INTO bla VALUES ('abc''def');
INSERT INTO bla VALUES (E'abc\\def');

Since the E'...' syntax is used, standard_conforming_strings is again irrelevant. These strings will be interpreted correctly,  whether the setting is on or off, by any version of postgres>=8.1

3.Dump of 8.1 data with 9.1 pg_dump with --inserts option

$ pg91/bin/pg_dump --inserts db

Now the dump happens to contain this, somewhere at the start

SET standard_conforming_strings = off;

and then in the data section:

INSERT INTO bla VALUES ('abc''def');
INSERT INTO bla VALUES ('abc\\def');

So again it's being taken care of automatically, albeit differently than with 8.1's pg_dump, but this dump is going to be reloadable directly in any version of postgres>=8.1, without tweaking anything.


SQL Staging Tables: Primary Key Clustered or Heap

Having an identity column doesn’t force you to use it as a clustered index key.

You’re right that heaps work well here. I would consider Thomas Kejser to be the authority on the subject, and it’s good you’ve listed him as one of your resources.

As for fragmentation in heaps - doesn’t happen on insert-only.

Edit: Go through this article about parallel insert, and notice the comparisons between heaps and clustered indexes. https://blogs.msdn.microsoft.com/sqlcat/2016/07/21/real-world-parallel-insert-what-else-you-need-to-know/


joining tables with full outer join and... COALESCE?

You can make it a subquery:

select * from (...) a
where coalesce(a.c1, coalesce(a.c2, coalesce(a.c3, coalesce(a.c4, coalesce(a.c5, null))))) is not null

You just put your query at the dots.

EDIT:

You just put where coalesce(TIS.CANT, TIT.CANT, TIMB.CANT, TIFC.CANT, TIA.CANT) is not null as a where clause in your query, as Andriy said. :)


How do I preserve leading zeros for varchar field

In your code sample, 011 is a numeric, not a string. SQL Server simplifies 011 to 11 it's numeric equivalent, then saves 11 as the string you see in your @varchar variable.
If you want the number to be treated as a string, and not a numeric, simply wrap it in quotes. Additionally, since your variable is unicode nvarchar and not simply varchar, you should prefix the quoted string with an N.
This will print the string, preserving leading zeros, using nvarchar as you do above:
DECLARE @nvarchar AS nvarchar(50);
SET @nvarchar = N'011';
PRINT @nvarchar;

But the same holds true with varchar as well:
DECLARE @varchar AS varchar(50);
SET @varchar = '011';
PRINT @varchar;


Assuming that each row in the source table only has either variable1, 2 or 3 populated, and there is only one such row for each combination, then this is probably the simplest approach:

SELECT Name, Column2,
  MAX(Variable1),
  MAX(Variable2),
  MAX(Variable3)
FROM dbo.DataTable
GROUP BY Name, Column2;

SQLfiddle


How to update one column with the value of another if one of their columns match

This will update all rows in the target table that match an email address in the same table that has been marked as 'do not email'

update a
   set a.note = 'do not email'
  from tbl as a
  join (select distinct email
          from tbl
         where note = 'do not email'
       ) as b
    on a.email = b.email
 where a.note <> 'do not email'
    or a.note is null;


I am going to assume that this database (AdminDB) is newly created and newly backed up?

There was an issue created for this as new databases were failing these tests.
https://github.com/sqlcollaborative/dbachecks/issues/207

A new configuration was created "policy.backup.newdbgraceperiod" (defaulting to value 0 for 0 hours) that excludes new databases created.
For example, it defaults to 0 so it includes all new databases e.g. (Get-Date).AddHours( - (policy.backup.newdbgraceperiod) )

As you can see, the new database QuickBackup fails.

Now when we allow a grace period of 1 hour for new databases:

Set-DbcConfig -Name policy.backup.newdbgraceperiod -Value 1

Our new database is excluded now:


Don't bother tracking audit data on a per-field basis.  It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table.

This architecture has a few advantages:

You can easily create a view across the main table and audit table to show a complete history includint current versions.
It is relatively simple to implement the audit triggers - in fact if you hae a large number of tables you can write a utility to generate them from the system data dictionary.
The audit tables can be put on a separate disk to reduce the I/O load on the disk with the main tables.
It's very easy to reconstruct an as-at version of the record, rather than having to apply a list of field-level changes in reverse.

Audit information should include at least: user who created/changed the record, date/time of the creation/change, nature of change (insert, update, delete).  You may want to use logical deletion (i.e. a 'Deleted' flag) if you have the option of doing so.  Otherwise you need to capture the user and date/time from the session and put these on the deletion record, probably along with the last state of the record.  If this is not available from the connection (often the case on N-tier apps where the user is not being impersonated at the database level) then you need to find some other way to get it.


For just the country/capital relationship I would go with option 1 and allow the capital to be NULL for a country record, rather than introducing an extra table. In both cases you are allowing the capital to be undefined at any given time so there is nothing to chose between the options from the completeness and referential integrity points of view.

If you do go for option 2, if the country column is not your PK make sure it is set to be UNIQUE otherwise you could accidentally end up with two capitals. With an extra "relationship type" column you could offer extra flexibility with this table though, by allowing things like "second city" and "religious capital" (some countries have this separate from their political capital for various reasons) to be defined.


Examples of SQL transaction procedures for sales tracking or a financial database

Depending on the complexity of the database design you would probably need to use transacations to maintain data consistency between tables as you change data within the system.

Any examples would be very basic as every database schema would be different.  This is where having a database or data architect comes in handy for a project.


I don't see the configurations are rightly set for memory. Hope you are using innodb engine.

Use this to fill basic my.cnf to effectively make user of your server setup. https://tools.percona.com/wizard


'Can't create thread to handle new connection' on RDS

This might help you
A Percona blog of tale of 2 limits

We (not RDS) had the second issue after we failed over as part of a disaster recovery exercise.

How old are the RDS instances, were they upgraded, replaced failed over...? Enjoy :)


ALTER SYSTEM DISCONNECT SESSION doesn't work

If even an ALTER SYSTEM KILL... does not remove the session after a bit, then the only thing you are left with is to kill it on the OS level.  In Linux you can get the command to kill the process as follows:

SELECT 'kill ' || ' -9 ' || p.spid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid=:SID_To_Kill;

In Windows you can get the command to kill the thread as follows:

SELECT 'orakill ' || sys_context('USERENV', 'DB_NAME') || ' ' || p.spid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid=:SID_To_Kill;

It might be simpler just to have cron bounce the database before running the jobs.


SLEEP_TASK Wait Type in SQL Server - What does it indicate?

SLEEP_TASK is a general wait, used for all sorts of things where a more specific wait type doesn't exist, but it does crop up with hashing operations (join/aggregate) that spill to tempdb, and occasionally at an exchange (Parallelism operator) where a consumer is waiting on something that isn't a normal CXPACKET or EXECSYNC wait. I would check for Hash spills first.


SQL Server - Nonclustered Index with Include on User Defined Table Type?

INCLUDE is not supported for memory-optimized tables or table variables. For these objects, the purpose of INCLUDE is moot, as all columns are available once you arrive at the row location in memory.

Memory-optimized indexes are inherently covering, see Guidelines for Memory-Optimized indexes

Disk-based indexes use row locators that are either a RID or a Clustered Index Key, and following those costs 1-4 Logical IOs, followed by locating the target row in the slot table on the page and going there.  The row locator for an In-Memory index is a pointer.  Following a pointer costs a single CPU instruction, and a main memory read, somewhere in the 100ns-1000ns range.


Well, I feel silly. I eventually saw this again, but it said "current count = 2.266" I thought that was again strange.

And then I noticed that 266 was the error number. Hmm.

Facepalm

Turns out I inherited some boiler plate error handling that was appending the error number to the error message without any separation. So what looked for all the world like a wacky ms thing, was in fact a wacky construct. I must have looked at it 100 times without seeing the actual problem.


Is there any impact whatsoever of enabling FILESTREAM on active SQL Server?

Enabling filestream by it self will not have any impact on the databases not using filestream. Queries on filestream data will be done using system cache not buffer cache so you might need to make sure that the SQL server service is not using all availible resources, see http://www.databasejournal.com/features/mssql/filestream-and-filetable-in-sql-server-2012.html


Is there an analog of GETDATE() that returns DATETIME2


is automated data movement via Excel considered ETL?

The process you describe is very extremely techinically ETL, though if I were reviewing qualifications with a candidate during an interview for a position that required what I usually think of as ETL experience and they described the process you mention as the "ETL" process they were familiar with, it's very likely that the interview would end politely and quickly.

It would be much better to describe this experience in terms of what it unambiguously is- VBA and advanced Excel skills are very valuable. You could call it "data preparation and processing using VBA and Excel," for example.

Calling these processes "ETL" experience is asking for trouble when an employer is looking for experience in one of the formal ETL tools or methodologies is unhappily suprised by the distinction.


To start with the correct primary key (single column vs multiple or artificial vs natural) is to use the primary key that is correct for the task.

Some people will tell you to always use an Artificial or Surrogate Key.  This is a key that is a generated key and has nothing to do with the information it represents.  For example an identity column or a GUID.  Others will tell you that an artificial key is the wrong way to go and you want to use a Natural Key.  This is a key that is constructed from one or more columns of the data itself.  FYI a key with multiple columns is sometimes called a composite key.  LastName, FirstName for example.  Or in your case it looks like you are using a combination of the two.  You have an integer ID (presumably artificial) + a year (presumably natural).

I'm not really going to go into the arguments here.  You can Google them yourself.  In your particular case I would leave your primary keys alone.  In my opinion t makes sense to have SurveyId and FieldId columns that are your primary key.  These are unique values (I assume) and the tables are basically lookup tables in this particular case.  I would however move the Year column out of those tables and into the data table.  If you don’t want to do this then take advantage of the fact that foreign keys can join to a ‘unique key’ as well as a primary key. A unique key is an index that enforces uniqueness but is necessarily the primary key.  As an added bonus columns in a unique key can be nullable.  In this case your structure would look like this:

CREATE TABLE survey (
   surveyID int primary key,
   student_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_survey ON survey(surveyID, year)
GO

CREATE TABLE fields (
   fieldID int primary key,
   field_name nvarchar(20),
   year smallint
)
GO

CREATE UNIQUE INDEX ix_fields ON fields(fieldID, year)
GO

CREATE TABLE data(
   surveyID int,
   fieldID int,
   year smallint,
   value float,
   primary key (surveyID, year, fieldID),
   foreign key(surveyID, year) REFERENCES survey(surveyID, year),
   foreign key(fieldID, year) REFERENCES fields(fieldID, year)
)
GO

I did take the liberty of changing the year column to a smallint which is more than large enough to hold a 4 digit year and only takes 2 bytes instead of 4.

Using the unique key you can enforce the fact that you want the year columns to be the same while not adding an unnecessary column (the IDs are already unique) to your primary key.


Speed up SELECT with WINDOW, (PARTITION ... ORDER) with compound index


Can I consolidate multiple MYSQL databases into one database via a script?

I found a solution to my problem.
I wrote a select statement from the information_schema database that pulls all of the databases and their tables. I then ran these commands via CLI.

select concat('alter table ', table_schema, '.', table_name, ' rename NEWDB.', table_schema, '_', table_name, ';') from tables

Thanks for the advice, help.
Hopefully this helps someone in the future.


The next time you login to MySQL, run this query:

SELECT USER(),CURRENT_USER();

What does this give you ?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

What comes back for CURRENT_USER() contains all the rights you had the momemnt you logged in. Chances are, you do not have the necessary privileges.

Please look over the grants you have and correct them or connect with a user that has the necessary privileges.

To see the privileges assigned, please run

SHOW GRANTS FOR CURRENT_USER();

In your comments, you stated you have the following rights upon login (from the output of the CURRENT_USER() function):

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE ON waitronmain`.* TO ''waitronmain''@''%'';

These are all the rights you have. Since you are in a Managed DB setting, you cannot be grants rights to create a database. What is more, they are not going to make it easy for you to do so.. Although you have the CREATE privilege in the list, it does not apply to the database level, only the table level.

Here is proof: I have a database called zipcodes. I will create a user called rolando

mysql> grant all on zipcodes.* to rolando identified by 'blahblah';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for rolando;
+--------------------------------------------------------------------------------------------------------+
| Grants for rolando@%                                                                                |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rolando'@'%' IDENTIFIED BY PASSWORD '*446525BB82B5E22BD9E525261D37C494F623C52B' |
| GRANT ALL PRIVILEGES ON `zipcodes`.* TO 'rolando'@'%'                                                  |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Here is what the mysql schema says on the CREATE privilege:

mysql> select user,host,create_priv from mysql.user where user='rolando';
+---------+------+-------------+
| user    | host | create_priv |
+---------+------+-------------+
| rolando | %    | N           |
+---------+------+-------------+
1 row in set (0.00 sec)

mysql> select user,host,create_priv from mysql.db where user='rolando';
+---------+------+-------------+
| user    | host | create_priv |
+---------+------+-------------+
| rolando | %    | Y           |
+---------+------+-------------+
1 row in set (0.00 sec)

mysql>

So, as I stated, the user does not have a global CREATE privilege. The user has table CREATE privilege because mysql.db has Y for create_priv.


Creating separate table as data retrieval queue

Separate table.

Using a flag as 'is processed' to simulate a queue is an anti-pattern. Because, as the table grows, finding the rows that need to be processed becomes increasingly difficult. An index on the flag field is useless due to very low selectivity. Using a table as a queue alleviates the problem as the rows in the queue are deleted when processed.

You can make the case also at a more theoretical level as tables are for data (long lived, queried in various ways) and queues are for events (short lived, always dequeued and not queried etc) and they should be stored separately. Also separating the processing into queues will allow you to make much more complex workflows (basically, add more queues), while the same based on row state (flags or status field) is more difficult to achieve (again, the retrieval performance issue) and more invasive (workflow changes affect the row info, hence the model representation in the app).

See Using Tables as Queues and 5 subtle ways you're using MySQL as a queue, and why it'll bite you (I don't agree with everything in the second link, but is still a good read).


Very high (99%) insertion cost in estimated execution plan

The high relative cost for the insert is a result of the cost-based model used by the optimizer.  In particular, it looks like SQL Server assigns a much higher I/O cost to inserts into a temp table compared to reading rows from a heap or clustered index.

The percentages you see are purely based on these cost estimates.

As a simplified example, check out this execution plan, which uses the Stack Overflow database:

SELECT *
INTO #tempusers
FROM dbo.Users;

I'm inserting all of the rows from the dbo.Users table (about 300,000) into a temp table.

The differences in estimated cost between the index scan and the table insert operators is dominated by "Estimated I/O Cost:"

Clustered Index Scan

Estimated rows: 299,611
Estimated I/O cost: 5.42312

Table Insert

Estimated rows: 299,611
Estimated I/O cost: 1,104.13

The estimated I/O cost for the temp table insert is two hundred times higher than the estimated I/O cost of the index scan.

Looking at the actual execution plan, you can see that the scan took 386 ms, and the insert took 514 ms.  The insert, in practice, was definitely not 200 times more "expensive" by any measure.

All of this is to say that, in this case in particular, don't focus on those percentages.  The I/O cost estimates are a bit outdated, and were originally based on systems with spinning disk storage and smaller amounts of RAM.

If you want help with specific parts of the query in your screenshot, you should post a new question that includes the actual execution plan.  I see a few sorts in the screenshot, which can often be avoided with indexes.  You also mentioned that what is inserted into the temp table is filtered - indexes can help with that as well.


Domain Admins can't connect to SQL Server

I found the answer to my problem in this answer by Remus Rusanu.

As an domain-admin connected to that SQL-Server-Host via Remote Desktop. I had to start SQL Server Management Studio explicitly "as an administrator". (right-click -> run as administrator).

If SSMS isn't started in administrator mode, the current login is not in "builtin\administrators"-group. That was the problem.

It comes from Windows user access control (UAC). So that is actually a security "feature".


Upgrading to SQL Server 2014 SP3 _ Clarifications help needed

As far as I know, Service Packs on same major version ( 2014 ) does not have any impacts on these features.


AWS DMS Task getting failed after completion with error as AlwaysOn BACKUP-ed data is not available

I don't know if you're still working through this, but I wanted to post the problem & solution for anyone else.
There is a bug in the AWS DMS replication engine version 3.4.3 when using RDS SQL Server as a source endpoint, where the 5 minute log backup used for point-in-time recovery with RDS disrupts the CDC process. The solution/workaround is to use a different replication engine version set on the replication instance, such as v3.4.2.
From AWS support:

Root Cause:
Researching internally, I observed this error to be a know issue with
DMS replication instance running the version 3.4.3,
where RDS SQL Server has truncated active transaction logs (TLOG) or
there are no activities in the database. This is a known situation
when using RDS SQL Server as a source.
Why task failed:
DMS 3.4.3 tasks are failing due to missing LSNs in source RDS SQL
Sever databases, when source SQL Server MS-CDC enabled tables remains
idle for a long period of time, the DMS task still fails even when you
increase the MS-CDC polling interval on source database.
This is because MS-CDC polling interval prevents TLOGs truncation only
when there are changes on source tables, if source tables remains
idle, the LSNs does not increment, then the RDS t-log backup job
removes the LSNs from the online t-log that are required by DMS,
causing this issue.
Additionally, DMS task is failing with misleading errors such as:
00016458: 2020-12-28T09:30:08 [SOURCE CAPTURE ]E: Failed to access LSN
'00001002:0001c389:0003' in the backup log sets since BACKUP/LOG-s are
not available. [1020465] (sqlserverendpoint_capture.c:717
This error can still occur even when MS-CDC Polling interval is set to
sufficient value, it happens when the RDS t-log backup job removes an
LSN from the online t-log. This issue is especially seen in databases
with less traffic since the LSNs are not incrementing and DMS is
looking at the last committed LSN.
Workaround:
I would recommend trying to migrate the same tables by creating a
similar task using DMS RI version 3.4.2 or 3.3.4


Removing values from array in postgreSQL

This should perform nicely:

UPDATE books AS b
SET    users = bu.users
FROM  (
   SELECT b1.name, u.users
   FROM   books b1
   CROSS  JOIN LATERAL (
      SELECT ARRAY(
         SELECT user_id
         FROM   unnest(b1.users) WITH ORDINALITY u(user_id, ord)
         JOIN   users u USING (user_id)
         ORDER  BY u.ord
         )
      ) u(users)
   ) bu
WHERE   b.name = bu.name
AND     b.users <> bu.users;

Preserves original order of array elements. If you don't need to, drop ORDINALITY & friends from the query.

Can result in empty arrays.

Assuming ...

books.name is the PK. Else use some other UNIQUE column from books instead.
users.user_id is at least UNIQUE.
books.users does not contain NULL values. Else those are eliminated as well.


What is the difference between logical schema and physical schema?

A logical schema won't exist in your database.  A logical schema is a design-centric database structure built to meet your business requirements.  It is a model that exists on a white board or in a diagraming tool.  It is like the architect's drawings of your database.

A physical model is what is actually implemented in your DBMS.

The two can be different for a variety of reasons and in several ways:

Your logical model should be properly normalized, but your physical model may have denormalization which you've added deliberately and for all the right reasons.
Your physical model may have different naming conventions.  Some people use plain English (or the language of choice) for their logical models and impose a more "system-ish" naming convention in their physical model.
Your logical model may have many-to-many relationships.  Physical models implement m:n using intersection tables.
Your logical model may only use natural or business keys.  Physical models may also add surrogate keys.

You should have a logical model because it lets you think about your database design without having to get bogged down in physical constraints.  You need to have the physical model because that will be where your data eventually lives.


Are there any advantages in SQL Server Always On vs. SQL Server 2008 R2 Mirroring with only two nodes?

Readable Secondaries: you can query the 'mirror'. See AlwaysOn: High-Availability and reads Scale-Out.


Short Answer:  Rule of Thumb:  If you have more than 4GB of RAM, then set innodb_buffer_pool_size to about 70% of available RAM.  Set and forget.

Long Answer:

"Greatly improve QPS?" -- It depends.

The buffer_pool is a "cache", maintained roughly as "least-recently-used".

If your data + indexes are bigger than the buffer_pool, there may be some slowdown.  Either live with the speed or get more RAM.

I have seen terabyte datasets run just fine on 32GB servers.  It depends on the "working set".  If the application only rummages through a portion of the rows (eg, "recent" rows), then a small buffer_pool works just as good as a big one.

If you have UUIDs, you are hosed.  These lead to random access that leads to slow-due-to-IO versus buy-more-RAM.

mysqltuner has some good points and some bad points.  Don't increase your buffer_pool to "big enough" unless you have 8GB or more of RAM.  Swapping is much worse for performance than shrinking the buffer_pool.

With a big enough buffer_pool, no it won't automatically load everything.  As a "cache", things (16KB blocks) are loaded as needed, then left there.  If it is not big enough and your "working set size" is big, those blocks will be bumped out to make room for other blocks.  Automatically.  The only cost is speed.


Optimize query plan when your results are either a few rows or hundreds of rows?

It's a Parameter Sniffing problem, so you have a few options:

You can consider using sp_create_plan_guide. Creates a plan guide for associating query hints or actual query plans with queries in a database.

Enables or disables parameter sniffing. The default is ON. Setting PARAMETER_SNIFFING to OFF is equivalent to enabling Trace Flag 4136.

OPTION RECOMPLE avoids plan caching and the risk of reusing an inappropriate plan at the cost of compilation upon each execution.That is not a concern for infrequently executed queries but can be very expensive for an OLTP workload where the query is executed many times per second.

OPTIMIZE FOR UNKNOWN This hint directs the query optimizer to use the standard algorithms it has always used if no parameters values had been passed to the query at all. In this case the optimizer will look at all available statistical data to reach a determination of what the values of the local variables used to generate the queryplan should be, instead of looking at the specific parameter values that were passed to the query by the application. Take a look at this post.


MySQL ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table'

This is a known and well documented issue:

  You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

  This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

One workaround that comes to mind would be to use a "normal" table as a temporary storage. You would probably need to use a session identifier to make the approach work in a multi-user environment.


Replication To Enterprise & Standard Edition subscribers

Take a 1-2GB file and copy it from Dist to a 'good' subscriber. Note speed/sec and elapsed time. Then do the same to the 'latent/new' subscriber. If much difference you have a hardware/storage/bandwidth issue. Failing that, maybe subscriber db settings are off...small autogrows, etc


In the original description of a B-tree the internal nodes held not just the index keys but all columns of the table. In the B+-tree only the leaf nodes store all columns' values. As each node in the tree is a fixed size, removing non-key data from internal nodes frees space for more keys to be held.

All operators can be made to work with the BTree/ B+Tree structure. Some are more efficient than others so, in practice, the query optimizer may choose not to use the index if one of the inefficient operators is involved.

The best case is equality (=). Here the matching key intervals can be followed from root to leaf and the corresponding row(s) returned. It may even be possible to determine from the root node alone that the query will return an empty set so tree walking can stop at that point.

Range queries (<=, >=) can be efficient. The tree can be walked to a leaf for the given predicate value. If leaf pages are a doubly-linked list the remainder of the range can be found by following these leaf-to-leaf links.

Wildcard searches can use the tree, depending on where the wildcard character is. If it is in the middle of the predicate or at the end then the search can proceed as for a range query, with additional checking of each value read. If the predicate starts with the wildcard the tree cannot be used because a B+Tree is an ordered set and a leading wildcard gives no indication of where in that ordering to start.

An inequality operator (!=) can be treated as two range conditions: (value < predicate) or (value > predicate). If the distribution of values in the data is very skewed it may still be efficient to use the tree. For typical cases it is more likely the optimizer will scan the leaf nodes from start to end.


Index Fragmentation - Am I interpreting the results correctly?

Yes, that's what it looks like. Unless there is some error happening with the data after it got moved to Excel.

However, these are all tiny, tiny tables. Stop caring about fragmentation on tables with less than, say, 1,000 pages.* And even then you probably shouldn't care too much until another order or two of magnitude, and even less if you are using SSD and/or your database fits into memory even with the fragmentation.

The work that you'll spend reorganizing them will not have the impact you expect, and the benefit from any changes you actually see, temporarily, will not justify it at all. I would simply filter such small tables out of your queries right from the start.

* 1,000 pages is just my ballpark, pull a number out of your you-know-what suggestion. There is no magic number for this. But seriously, for small tables, focus your efforts elsewhere.


If it's a full backup, and there's no real-time application being in use in the database. I would suggest stopping and deleting the database for a complete clean up. After that the restore can be done and the replication could be setup automatically from the restore.


Perhaps, I'm missing something, but it sounds like to want the max(formidnb) for each NumCavalier in the time slice you're processing.  I haven't fully syntax checked this, but I think you could add the following to just above your semicolon

AND d.FormIdNb =
(
select MAX(FormIdNb)
FROM #tmp_DailyMissions
WHERE NumCavalier = d.NumCavalier and
DateHeure >= t.StartTime
AND DateHeure < t.EndTime
)


"user" is a reserved word. You may want to try "username" instead. http://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html


How to atomically replace a subset of table data

Concurrent reads are not a problem. Writers don't block readers and vice versa in the default READ COMMITTED isolation level. Enclose DELETE and INSERT in a single transaction to make the operation atomic (all applied or nothing).
If there can be multiple transactions trying to write at the same time, that's a game changer. A single transaction protects you from inconsistent updates, but it cannot protect you from race conditions between concurrent transactions: deadlocks.
Say, we have two transactions T1 and T2, and category 'A' has 10 IDs:
T1: DELETE FROM T WHERE category = 'A';
-- starts taking row locks in arbitrary order: id 1,2,3,4,5,6,7 ...
                    T2: DELETE FROM T WHERE category = 'A';
                    -- starts taking row locks in arbitrary order: id 10, 9, 8, ...
T1: wait for T2 to release lock on id 8
                    T2: wait for T1 to release lock on id 7

DEADLOCK.

Postgres detects the deadlock after some time and kills one of the two transactions. (A deadlock error is reported.)
You could switch to SERIALIZABLE transaction isolation. But that's much more expensive and you need to prepare for serialization failures and retry in this case.
Or you can avoid the problem by always deleting rows in identical, deterministic order. Like:
WITH del AS (
   SELECT category, id
   FROM   T
   WHERE  category = 'A'
   ORDER  BY category, id  -- enforce this order in *all* writing queries
   FOR    UPDATE
   )
DELETE FROM T
USING  del
WHERE  T.category = del.category
AND    T.id = del.id;

But typically, there is a more convenient option. If you have a separate table holding unique categories named, say, cat, you can lock the single parent row in cat with:
SELECT * FROM cat WHERE category = 'A' FOR UPDATE;

and then (in the same transaction) write to category 'A' rows in T at will (still encapsulated in a single transaction to avoid intermediary, inconsistent states being visible).  Of course, all writing queries must follow the same protocol. Then, concurrent transactions will wait for the lock on cat before they write to T and everything is groovy ...
In Postgres 9.4 or later consider FOR NO KEY UPDATE instead:

How to perform conditional insert based on row count?

Concerning:

After each write there will be a read to the adjacent categories.

You are aware of the RETURNING clause, right? No need for a separate read, if you just inserted all rows for a given category. Example:

PostgreSQL multi INSERT…RETURNING with multiple columns


How to answer why suddenly we need indexes or query needs to be changed

How to answer what changed question by dev ?

This is a very common question not only with DEV, it applies to every team in IT and business.

What changed ? ==> can be answered by facts and figures.

Facts refer to for example

increase the amount of users that are accessing the database ?
Any change in server configuration parameter ?
Database Maintenance - update stats, reorg/rebuild of indexes not being performed ? Due to this the plans are being incorrectly generated !
Amount of data has increased ?
Changes were made on the network side, OS was patched and/or a new service pack or CU for sql server was deployed - without doing a full regression testing of your application business cycle ?
The underlying SAN has become suddenly slow ?

Figures can be derived if you have data to show. For example :

Baselining your server is critical during this situation. This will alleviate the blame game since you can support the facts with solid figures.
Start collecting data using DMVs or sp_whoisactive to a table, so that the data gets persisted after a sql server reboot.

(you have to workout based on your environment and needs, on how often to collect the data/ what data to collect and how much will be the retention period) or (you can invest in a third party software like sqlsentry or idera's diagnostic manager that will do the above work for you).


Trigger to update only if value is in lookup table

You could change the trigger to only update when the NameOfParameter matches the Tag from TagDescLookup

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TagDescTrigger]
    ON [dbo].[EventLog]
    AFTER INSERT
    AS
       BEGIN
       SET NOCOUNT ON

        UPDATE E
        SET E.Comment =TDL.[Description]
        FROM
        [dbo].[EventLog] AS E
        JOIN INSERTED AS I
        ON E.NameOfParameter = I.NameOfParameter
        JOIN TagDescLookup AS TDL
        ON TDL.Tag = I.NameOfParameter;
        END
    END

You really need to look out for duplicate Tag's.

Testing

CREATE TABLE  TagDescLookup
(ID int NOT NULL PRIMARY KEY IDENTITY (1,1)  ,
 Tag nvarchar(MAX) NOT NULL  ,
 Description nvarchar(MAX)  );

 CREATE TABLE [dbo].[EventLog]
(Comment  nvarchar(MAX),
NameOfParameter nvarchar(max));

Example1

  INSERT INTO [dbo].[EventLog](Comment,NameOfParameter)
  VALUES('bla','bla');

  select * from dbo.EventLog;

Result1

Comment NameOfParameter
bla bla

Example2

  Insert into TagDescLookup(Tag,Description)
  VALUES('TaggedVal','NewDescription');

  INSERT INTO [dbo].[EventLog](Comment,NameOfParameter)
  VALUES('OldDescription?','TaggedVal');

  SELECT * From dbo.EventLog;

Result2

Comment NameOfParameter
bla bla
NewDescription  TaggedVal


You will need the table to exist and have all the columns returned by your statement.

Then you can run something like:

INSERT INTO MyTable Exec (@SQL);


You can define your own exceptions. If you have a package for defining business logic, you can define some exceptions in the package header then raise the proper exception when needed. Which means that you need to keep the exception numbers unique so that you can handle the exceptions from the calling procedures.

ssociating a PL/SQL Exception with a Number (EXCEPTION_INIT Pragma)