There are none that I know of that will show you the exact results - maintaining hooks so such tools could spy on what is going on would probably be inefficient in production (no doubt MS can do it in their development builds). While intermediate results aer sometimes dumped to temporary tables for further processing, they are often never materialised as a whole unit instead being streamed between parts of the query engine as they are produced.

If you run the query in SQL Server Management Studio, you can request that the query plan used is output. While that will not give you the entire intermediate results it will show you what comparisons are being performed and approximately how many rows are effectively output at each step - if you have a Cartesian product between a pair of large tables this should hopefully make it clear where in the process this happens.

For more specific help you'll need to add more detail to your question (please add it to the question, not as comments here, then there is better visibility of the new information for people who visit this page later):

What makes you think there is a cross join happening? Are you getting many more rows than you expect then whittling them down with a DISTINCT? If it is just that the query is running very slowly and spinning the CPU then it may instead be a index use problem or a correlated subquery that is resulting in a scan per output row (a situation that can actually be worse than a cross join performance wise)
Could you provide a copy of the query and relevant table structures?
What size of data are we talking about?


Modify function to use it in multiple triggers

Answers so far still suffer from one or the other shortcoming.

Use the USING clause of EXECUTE to pass values. Do not concatenate the text representation of values into the SQL string. Expensive and error-prone.
Properly escape all identifiers when used in dynamic SQL to defend against sneaky errors with double quoted identifiers and possible SQL injection. Even if you think you do not need it. Do it on principal.

But this would be an error waiting to happen:

'_bit' || quote_ident(TG_TABLE_NAME)

For a table name "MyTable" this would produce _bit"MyTable" - syntactical nonsense. It must be:

quote_ident('_bit' || TG_TABLE_NAME)

Or use format('... %I ...', '_bit' || TG_TABLE_NAME)

The only correct solution is what you added to your question yourself:

CREATE OR REPLACE FUNCTION bitacoratabla()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I SELECT $1.*', '_bit' || TG_TABLE_NAME)
   USING OLD;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Detailed explanation:

INSERT with dynamic table name in trigger function

But why does it work with an additional column?

  the other table has the old values and an additional column to store the date data

This happens to work as long as the additional column in the target table comes last because, quoting the manual on INSERT:

  If no list of column names is given at all, the default is all the
  columns of the table in their declared order; or the first N column
  names, if there are only N columns supplied by the VALUES clause or query.

So you better be sure the additional column(s) come last in the table definition of the target table, and all other columns are listed in matching order, now and in all future - or you must add an explicit target column list (which would prevent a generic function to begin with).


How to configure Standby database in oracle for read/write

For this you need a logical standby database, instead of a physical one. This is a little more complex. Not all data types are supported and when data is updated on both sides on the same time, there could be some problems ahead.


Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

Your all points are covered in backup myths - by Paul Randal

30-01) backup operations cause blocking

No. Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might look like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.

  Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file.

A backup when done to a single file or device will use 1 writer thread. So if you are backing up to Multiple files /devices (be that multiple .bak files) will have one writer thread per file/device.

  The easiest way to improve backup performance is to allow the backup operation to parallelize, which is known as backup striping. By default, there’s a single data reader thread for each drive letter or mount point being read from and a single data writer thread for each backup device being written to.

Check

SQL Server 2008 Microsoft Certified Master (MCM) Readiness videos especially Backup Internals.
A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - By: Jonathan Kehayias
A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 2)- By: Jonathan Kehayias


Multiple left joins with common fields are overwritten

You can use Coalesce to show s.amount or p.amount which one is not null:
Query:
select t.transaction_type, t.transaction_name, coalesce( s.amount, p.amount) Amount
from transactions t
left join sales s on t.transaction_type='sale' and t.transaction_name=s.name
left join purchases p on t.transaction_type='purchase' and t.transaction_name=p.name

create schema:
create table transactions(transaction_type  varchar(30),transaction_name varchar(30));
insert into transactions values('sale','one');
insert into transactions values('sale','two');
insert into transactions values('purchase','two');

create table sales (name    varchar(30), amount float);
insert into sales values('one', 10.0);
insert into sales values('two', 5.0);

create table purchases (name    varchar(30),amount float);
insert into purchases values('two', 7.0);

Check the db-fiddle link:
https://www.db-fiddle.com/#&togetherjs=E7luJAKsF5


ORA-30009: Not enough memory for CONNECT BY operation

Here are a few tips that might help you inserting huge amounts of data:

use APPEND hint, this should solve your problem, as the data will go directly into the datafiles per-say.
try to break the data into smaller chunk, and insert them sequentally
use NOLOGGING mode to avoid frequent log-switchees (don't forget to enable it back afterwards!) - or at least increase the size of redo-log files
avoid using cursors
drop any indexes that you might have
try to COMMIT less frequently (use a transaction for each chunk of data, and commit at the end)

Hope this helps!


SQL Server 2008 R2 sys.dm_exec_sql_text question

No, regarding your query that means, that this procedure takes much time than others

And

Periodically sql server recompiles procedures or its inner statements, it is an intended behavior depending from server activity, memory pressure, user commands, RECOMPILE option, amount of underlying data changes and many more.

Update:

The queries is started from CREATE PROCEDURE because sql server presents the SP's body in such a way.

You may even drill down into procedure's inner statements by this query:

Select
    s3.name as [Obj Name],
    s3.type as [Obj Type],
    (select top 1 substring(text,(s1.statement_start_offset+2)/2,
    (CASE when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2
    else s1.statement_end_offset end - s1.statement_start_offset) /2 ) FROM sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement],
    execution_count,
    plan_generation_num,
    last_execution_time,
    ((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time],
    total_worker_time/1000.0 total_worker_time,
    last_worker_time/1000.0 last_worker_time,
    min_worker_time/1000.0 min_worker_time,
    max_worker_time/1000.0 max_worker_time,
    ((total_logical_reads+0.0)/execution_count) as [avg_logical_reads],
    total_logical_reads+0.0 total_logical_reads,
    last_logical_reads+0.0 last_logical_reads,
    min_logical_reads+0.0 min_logical_reads,
    max_logical_reads+0.0 max_logical_reads,
    ((total_logical_writes+0.0)/execution_count) as [avg_logical_writes],
    total_logical_writes+0.0 total_logical_writes,
    last_logical_writes+0.0 last_logical_writes,
    min_logical_writes+0.0 min_logical_writes,
    max_logical_writes+0.0 max_logical_writes,
    ((total_logical_writes+0.0)/execution_count + (total_logical_reads+0.0)/execution_count) as [avg_logical_IO],
    total_logical_writes + total_logical_reads+0.0 total_logical_IO,
    last_logical_writes +last_logical_reads+0.0 last_logical_IO,
    min_logical_writes +min_logical_reads+0.0 min_logical_IO,
    max_logical_writes + max_logical_reads+0.0 max_logical_IO
from sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(sql_handle) as s2
join sys.objects s3 on ( s2.objectid = s3.object_id )
left join sys.schemas sch on(s3.schema_id = sch.schema_id)
where s2.dbid = db_id()
order by s3.name, s1.sql_handle


I'd have a few points built up over the years.

There is always risk involved in projects such as
this - for every extra 9, you can add a 0!

What I mean
but that is that in order to move from (say) 99% reliability
to 99.9%, you'll have to multiply your costs by 10.

Minimise, to the greatest extent possible, your web-facing data.
Always perform your upgrades at your least busy time.

DDL on a running server is difficult.

In order for upgrades to proceed smoothly - ALWAYS put field lists in your SQL, NEVER SELECT or INSERT more than you absolutely have to.

i.e. SELECT f1, f2, f3... FROM tab1.... (not SELECT * FROM tab1) and

INSERT INTO tab1 (f1, f2, f3) VALUES (not INSERT INTO tab1 VALUES(f1, f2...fn)

The best thing for your scenario IMHO is to have some sort of
replication scenario. Get to know your RDBMS's HA capacties
very well. Take a look at my answer here (it's for MySQL, but
the general principals will hold for any system).

Normally, Master is replicated to Slave.

At cutover time, Halt Slave. Upgrade the Slave. Old Slave becomes new Master. Old Master becomes new Slave.

Test, test and test again!

Good luck.


How Count Row Value in SQL Server database?

One way

SELECT
      *,
      CASE WHEN [1] = 'A' THEN 1 ELSE 0 END +
      CASE WHEN [2] = 'A' THEN 1 ELSE 0 END +
      CASE WHEN [3] = 'A' THEN 1 ELSE 0 END +
      CASE WHEN [4] = 'A' THEN 1 ELSE 0 END AS TotalA
FROM TimeSheet

or

SELECT *,
       (SELECT count(*)
        FROM   (VALUES([1]),
                      ([2]),
                      ([3]),
                      ([4])) V(c)
        WHERE  c = 'A') AS TotalA
FROM   TimeSheet


SQL: How to Select Multiple Row Data for the Same ID?

If I interpret of your requirement correctly:

SELECT id
    FROM tbl
    WHERE col1 IN ('00020', '00023')
    GROUP BY id
    HAVING GROUP_CONCAT(DISTINCT col1 ORDER BY col1) = '00020,00023'

Performance might benefit from INDEX(col1, id).

A different query might include HAVING (COUNT DISTINCT col1) > 1, but the implied presence of dups may mess it up.

To select ids that have at least one of those col1s:

SELECT DISTINCT id
    FROM tbl
    WHERE col1 IN ('00020', '00023')

To select rows that have at least one of the values in ('00020', '00010') and in ('00023', '00033'):

SELECT id
    FROM tbl
    WHERE EXISTS( ( SELECT 1 FROM tbl
                      WHERE id = a.id
                        AND col1 IN ('00020', '00010') ) )
      AND EXISTS( ( SELECT 1 FROM tbl
                      WHERE id = a.id
                        AND col1 IN ('00023', '00033') ) )

(There are probably other ways, but this was the first one that came to mind.)


Error while configuring mirroring, Microsoft SQL Server, error : 1418

Microsoft error no 1418:-
this error occurs due to communication problem between endpoints.first of all you have to check TCP/IP for the instance was enabled or not,if any duplicate endpoints exists,in
case login was not created inn the other instances of mirroring pair,check for the edition of principal and mirror servers.


The way to gauge hardware performance is to be take a look at your current workload. So I would begin by quantifying that:

Take a look at Brent Ozar's perf tuning stuff
Identify your bottlenecks (are you seeing bad parallelization, CPU chugging away without finishing, memory, storage)
Determine what differences there will be between your new and old system.

You're right that SQL Server rarely bottlenecks at the CPU, but it's not impossible and you can tell whether you're seeing a CPU bottleneck by looking at some perf counters.

Then you'll know whether or not you'll see a performance improvement simply by updating the CPU to a faster one or one with more cores. If your SQL isn't written to take advantage of parallel processing, it won't matter if you throw more cores at it.

Additionally, there's more things I would want to consider in buying a system:

Upgradability - SQL Server will rapidly test the limits of your hardware if you let it, so try to anticipate that
Affordability - SQL Server licensing in 2014 is core-based, so this should be a consideration.

This is in addition to other IT concerns you'd want to keep in mind like maintainability, support contracts, and your space/power requirements.


Transaction log uses (not backups of transaction logs)

I would 'suggest' that VSS snapshots are 'mostly' intended for an out-and-out disaster restore scenario.  It is my understanding that VSS snapshots allow you to restore to that snapshot time.  No time just before snapshot and no time just after the snapshot (until the next snapshot).

What are you going to do when an accidental delete occurs due to user error?
What happens 10 minutes after your VSS snapshot is taken and the server dies or a disk crashes.  What about corruption that hits 10 minutes after your VSS snapshot?

There is tremendous benefit in having your databases in FULL recovery mode and taking timely transaction log backups (even down to the minute)

Honestly, this gets down to your company RPO (Recovery Point Objective) and RTO (Recovery Time Objective).  Are YOU the one that can actually decide these things?


Using AWS DynamoDB Geospatial index with Python

The "geospatial feature" of dynamodb isn't really anything else then calculating a geohash from the coordinates, storing the hash in the same row, create an index on the hash, and when querying based on a location, calculate the hash of that location and compare to the indexed hashes in the table. The algorithm of geohash is really simple and with a reasonable knowledge it could be coded in a couple of hours in any language. You could even create a lambda function (since October 2015 even in Python!) that is attached to a dynamodb stream and stores the geohash automagically.

But probably it is a much better idea to check out dynamodb streams + lambda + cloudsearch integration that has native support for geospatial queries and boto also has a cloudsearch interface.


Duplicate database leaves orphan archivelog files

The duplicate database ... from active database is designed to work without any existing backup.
I would expect Oracle to clean up the archivelogs which were used during the clone process.

So I would say this is an unexpected behavior. Try to log a Service Request at Oracle.

I cannot recall if I had to clean up archivelogs after a clone processes. Well, I can say that I never did delete archivelogs after a db clone because I did not check for any. I rarely clone from active database. I normally clone from backup location without a connection to the primary database. I somehow prefer that.

As workaround I would delete them with a script afterwards.


Difference between transaction log and redo log in MySQL

REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.

The transaction log is, by default- it can be changed- the two files located in $DATADIR called ib_logfile0 and ib_logfile1. It serves the same functions as the REDO log in other databases- storing writes in a safe way and recovering in the case of a crash, although there are some details in implementation that differ in functionality from other RDMS. It is the main component for InnoDB to be a transactional engine.

Do not confuse the transaction log with the binary logs in MySQL. The binlog, by default, is on the $DATADIR and is *hostname*-bin.index and several *hostname*-bin.00001, etc. It is particular confusing for people coming from other databases, because it is used for other things that other databases use the REDO log for: replication and point in time recovery. The main difference is that the transaction log is InnoDB-only, the binary log is (mostly) transaction-independent, as it is for all storage engines, transactional or not. MyISAM will write (if enabled) to the binary log. InnoDB will write to the transaction log and the binary log.

More info on the manual: REDO log, binary log.


How to update the parent/child of all rows in the tree (ltree)?

One can view the ltree data type as a simple text with some constraints.  The most important of these constraints is that a label is bordered by dots if part of a label path.  Obviously, if it stands at either end, one dot will be missing; when alone, the dots are completely missing.

Fortunately, casting between the two types is easy.  This way we can set up a small set of rules for handling replacement, using lquery to find the cases:

-- 1. replace when the label is the whole labelpath
replace(section_path::text, 'to_be_replaced', 'replacement')
    WHERE section_path ~ 'to_be_replaced'::lquery

-- 2. replace when the label is at the end
replace(section_path::text, '.to_be_replaced', '.replacement')
    WHERE section_path ~ '*.to_be_replaced'::lquery

-- 3. replace when the label is at the beginning
replace(section_path::text, 'to_be_replaced.', '.replacement')
    WHERE section_path ~ 'to_be_replaced.*'::lquery

-- 4. replace when the label is in the middle
replace(section_path::text, '.to_be_replaced.', '.replacement.')
    WHERE section_path ~ '*.to_be_replaced.*'::lquery

In cases 2-4, mind the dots in the search and replacement values of replace().  This way you can be sure you always replace the whole label, not just parts of it.


"Unable to display page" with Oracle 12c Enterprise Manager Database Express

Try browsing to
https://[IP-address]/em

For me this works, but everything is -very- -very- slow (a few minutes just to load the login page).

I'm suspecting the slowness has something to do with the certificate. Why the IP-address works and the hostname doesn't... probably some configuration issue.

At least now anyone with more experience has something to go on...

For reference, all these posts seem to be about the same issue to me, but so far none has been answered:

Unable to open/connect to Oracle 12c Enterprise Manager Express
Start Oracle 12c enterprise manager in Windows 7
How to run ORACLE 12C EM?
Oracle 12c Express Enterprise Manager on Centos 6.5 is not reachable


decrypt data in column before load into destination

When calls to decrypt methods return NULL, either the column is NULL or the account that is using the methods doesn't have access to the key.

From BOL

  Permissions

  Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.

Look at at the account that is executing the SSIS package and verify it has the required permissions.

You can also launch SSMS as the account that runs the SSIS packages and try stepping through the underlying code of DecryptByKeyAutoCert. Something approximately like

OPEN SYMMETRIC KEY MyKeyNameHere
   DECRYPTION BY CERTIFICATE Usercert;

SELECT
    city_id
,   CONVERT(nvarchar(50), DecryptByKey(city_name)) AS city_name
FROM city;

CLOSE SYMMETRIC KEY MyKeyNameHere;


SQL Server Secure and Unsecure Traffic Configuration Help

Am I missing something?

No. "Force Encryption" is an instance-wide setting.


Vacuum increases bloat size as reported by Heroku's bloat SQL query

All these queries that estimate bloat are not precise, but rely on heuristics.

If you want to find out the truth, install the pgstattuple extension and run the pgstattuple function on the table in question. That will tell you how much space is occupied by user data, dead tuples and empty space, respectively.


PostgreSQL: websearch_to_tsquery precedence / predicate ordering

This is how PostgreSQL interprets your query:
SELECT websearch_to_tsquery('english', 'exp1 exp2 OR "multi word expression" -weak');

                     websearch_to_tsquery
--------------------------------------------------------------
 'exp1' & 'exp2' | 'multi' <-> 'word' <-> 'express' & !'weak'
(1 row)

Since & ties stronger than |, your suspicion is correct.
I don't think that there is a simpler way to express what you want with websearch_to_tsquery than
exp1 exp2 -weak OR "multi word expression" -weak


How to write a query for picking 20 items from six tables with two conditions

Finally i made the query.

SELECT can.date_created,jobo.title,can.first_name,can.phone_cell,can.email1,can.current_employer,comp.name,canstatus.short_description,can.notes,can.city,jobo.city,extra.TotalExperience,extra.RelevantExperience,can.current_pay,can.desired_pay,extra.Noticeperiod,extra.Education,can.candidate_id
FROM candidate AS can
LEFT JOIN candidate_joborder AS canjob
ON can.candidate_id = canjob.candidate_id
LEFT JOIN joborder AS jobo
ON jobo.joborder_id = canjob.joborder_id
LEFT JOIN company AS comp
ON comp.company_id = jobo.company_id
LEFT JOIN candidate_joborder_status canstatus
ON canjob.status = canstatus.candidate_joborder_status_id
LEFT JOIN (
 SELECT T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience FROM extra_field AS T
GROUP BY
    T.data_item_id) extra
ON extra.data_item_id = can.candidate_id
WHERE can.entered_by =$user_id can.date_created LIKE '$date%'"

Thank you for every one who helped me


Building where clause based upon parameters

The output of a CASE expression has to be a single value not a list of values.

The statement did not give a syntax error for cases 3 and 4 because  '''Value1'',''Value2''' is evaluated as a single string (which includes 4 quote characters and a comma): 'Value1','Value2'

You could rewrite your condition using a CASE expression that evaluates to boolean, like this:

select fld1, count(*)
from xyz
where case $1
          when 1 then fld1 in ('Value1')
          when 2 then fld1 in ('Value2')
          when 3 then fld1 in ('Value1', 'Value2')
          when 4 then fld1 in ('Value4', 'Value5', 'Value6')
      end
group by fld1 ;

but I'd prefer to write it more more simply using OR:

select fld1, count(*)
from xyz
where ( $1 = 1 and fld1 in ('Value1')
     or $1 = 2 and fld1 in ('Value2')
     or $1 = 3 and fld1 in ('Value1', 'Value2')
     or $1 = 4 and fld1 in ('Value4', 'Value5', 'Value6')
      )
group by fld1 ;


Splitting SQL Server tempdb to use a second drive when first drive is full

there a way to put a preference on the SSD drive until full in which case it will resume on the SAN storage?

You can limit the tempdb autogrowth for the primary datafile (that you have on SSD) to 30GB
and then you can create additional datafiles on the SAN.

The drawback will be that you will see imbalance in the tempdb datafile growth.

SQL Server allocates space in a round-robin fashion.

Important Trace flags when dealing with Tempdb :

-T1117 (helps keep data base files equally sized over their lifetime, by triggering all database files in a filegroup for autogrow at the same time.). Note that you wont need this trace flag as you will be eventually restricting the tempdb to NOT grow on SSD once you reach the threshold.

-T1118 (this trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load of small temp table creation and deletion.) for tempdb.

Refer to Misconceptions around TF 1118 by Paul Randal and SQL Server 2008 Trace Flag -T 1117


Some thoughts...

what filters or joins will you use on the view? These determine what indexes you'll need on the table
have you considered a computed column to pre-sum cola+colb at the table level?

Personally, I avoid views because a view doesn't have persistence (unless it is indexed itself): it is simply a macro expanded by the query optimiser. That is, say you have 2 views that join (one with 3 table, one with 4 tables): the actual query plan will show all 7 tables.

In the case above, you have no filters. This means that no index can help you: you'll get a clustered index (which is actually the table and you do have a clustered index, right?) scan


Joining two processed tables without Primary Key

I'm not sure if this is what are you looking for.

I've set up next example:

CREATE TABLE INDIVID (PAANUM INT, PAWHLO VARCHAR(10), PAANTX INT);
INSERT INTO INDIVID VALUES (1, 'h550', 100),(2, 'h560', 300),(3, 'h550', 50);

CREATE TABLE PORDHV (P1MTRL INT, P1STAT CHAR(1), P1PTF VARCHAR(10), P1UPL1 INT);
INSERT INTO PORDHV VALUES
(1, 'A', '20180301', 50),
(1, 'T', '20180401', 50),
(1, 'A', '20180401', 75),
(1, 'A', '20180501', 60),
(2, 'A', '20180301', 50),
(2, 'A', '20180401', 75),
(3, 'A', '20180301', 50),
(1, 'A', '20180401', 75);
GO

If you're trying to get all orders, order by quantity you can use a CROSS APPLY solution:

SELECT    PAANUM AS SKU,
          PAANTX AS ON_STOCK,
          P.PLANNED
FROM      INDIVID
CROSS APPLY (SELECT P1UPL1 AS PLANNED
             FROM   PORDHV
             WHERE  P1MTRL = INDIVID.PAANUM
             AND    P1STAT NOT IN ('E','T','P','R')
             AND    PORDHV.P1PTF >= '20180101' -- IS IT A TEXT OR A DATE FIELD?
            ) P
WHERE     PAWHLO = 'h550'
AND       PAANTX > 0
ORDER BY  PAANUM, PLANNED DESC
GO

SKU | ON_STOCK | PLANNED
--: | -------: | ------:
  1 |      100 |      75
  1 |      100 |      75
  1 |      100 |      60
  1 |      100 |      50
  3 |       50 |      50

But it you want the sum of all orders by SKU you can sum all orders of each SKU and then LEFT JOIN with INDIVID table.

SELECT    PAANUM AS SKU,
          PAANTX AS ON_STOCK,
          P.PLANNED
FROM      INDIVID
LEFT JOIN (SELECT   P1MTRL, SUM(P1UPL1) AS PLANNED
           FROM     PORDHV
           WHERE    P1STAT NOT IN ('E','T','P','R')
           AND      PORDHV.P1PTF >= '20180101' -- IS IT A TEXT OR A DATE FIELD?
           GROUP BY P1MTRL) P
ON        P.P1MTRL = PAANUM
WHERE     PAWHLO = 'h550'
AND       PAANTX > 0
ORDER BY  PAANUM;
GO

SKU | ON_STOCK | PLANNED
--: | -------: | ------:
  1 |      100 |     260
  3 |       50 |      50

dbfiddle here


Spliting tables with *almost* same values

Certainly not one table for each document.  Perhaps one for each document type.

In general you are correct - reading or writing to larger tables typically takes longer than the same operation on smaller tables.  There are techniques to help with this - indexing for example.  Modern DBMSs can easily handle tables of several million rows, however, so unless you are pushing these limits it is better for your design to match your requirements than to work around the limitations of the technology.

If you commonly update several different documents of different types simultaneously then a single table would be beneficial.  If each document type is processed independently then separate tables would be better.


Updates for SQL Server consist of applying the most recent service pack, then the most recent cumulative update, then any updates you require that are newer than the most recent cumulative update.  i.e. do 1, then 2, then 3.

Service packs are cumulative in that they embody all the changes made in the prior service packs, allowing you to go from the RTM release directly to service pack 2, skipping service pack 1 since those changes are included in service pack 2.

Cumulative updates are, as the name indicates, also cumulative, although they only capture the changes since the most recent service pack released prior to the cumulative update package.

Check this post for details of which updates you should consider.  This site provides excellent details for various service packs, cumulative updates, and fixes for SQL Server.

Note that as-of SQL Server 2017, service packs have been discontinued in favor of simple cumulative updates.  This should help eliminate confusion regarding what to apply since you only need to apply the most recent cumulative update, then any fixes you need that have been released since that cumulative update.


Let me know if this solve your problem.

I've set up a rextester example to test it.

I've used this data:

create table #mov(produto_id int, documento int, data_cadastro datetime, entradas int, salidas int);

insert into #mov values
(1, 1, '2017-01-01', 10, 0),
(1, 1, '2017-01-02', 10, 2),
(2, 1, '2017-01-01', 10, 0),
(1, 1, '2017-01-03', 10, 0),
(3, 1, '2017-01-02', 10, 0);

To get the cumulative sum, apply SUM() OVER clause in this way:

select
    produto_id,
    documento,
    entradas,
    salidas,
    sum(entradas-salidas) over (partition by produto_id order by produto_id, data_cadastro
    rows between unbounded preceding and current row) as acumulato
from
    #mov

This is the result:

+------------+-----------+----------+---------+-----------+
| produto_id | documento | entradas | salidas | acumulato |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |    10    |    0    |     10    |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |     0    |    2    |     8     |
+------------+-----------+----------+---------+-----------+
|      1     |     1     |    10    |    0    |     18    |
+------------+-----------+----------+---------+-----------+
|      2     |     1     |    12    |    0    |     12    |
+------------+-----------+----------+---------+-----------+
|      3     |     1     |     5    |    0    |     5     |
+------------+-----------+----------+---------+-----------+

If you need the last acumulato in the same row, you can use:

;with cteSum as
(
    select
        produto_id,
        data_cadastro,
        documento,
        entradas,
        salidas,
        sum(entradas-salidas) over (partition by produto_id order by produto_id, data_cadastro
        rows between unbounded preceding and current row) as acumulato
    from
        #mov
)
select produto_id, documento, entradas, salidas, acumulado,
       lag(acumulato,1,0) over (partition by produto_id order by produto_id, data_cadastro) as last_acm
from cteSum;

+------------+-----------+----------+---------+-----------+----------+
| produto_id | documento | entradas | salidas | acumulato | last_acm |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |    10    |    0    |     10    |     0    |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |     0    |    2    |     8     |    10    |
+------------+-----------+----------+---------+-----------+----------+
|      1     |     1     |    10    |    0    |     18    |     8    |
+------------+-----------+----------+---------+-----------+----------+
|      2     |     1     |    12    |    0    |     12    |     0    |
+------------+-----------+----------+---------+-----------+----------+
|      3     |     1     |     5    |    0    |     5     |     0    |
+------------+-----------+----------+---------+-----------+----------+


Setting the right Max Server Memory value on an Active/Active SQL Server Clustered Environment

Please note this is called multi instance cluster not active active actually. Your concern is correct about what would happen when both nodes are on same node, in that case you need a dynamic script would identify a failover and adjust SQL Server max server memory accordingly.

Let us focus on how to set max server memory correctly when both nodes are running on respective nodes for that please see my answer on SE thread What is Sensible way to calculate max server memory.

Now for scenario when failover happens and both instances are on same node. You have to use scripts from follwoing resources.

Use PowerShell Script via startup Agent Job to balance Memory between two instances on a cluster on a Failover
A similar thread talking about how to adjust memory for Multi Instance cluster

Now when both instances are on same node you would have to evenly distribute memory leaving enough for OS. In that scenario 29 G for each instance and 6 G for OS is what I would say would be good value. Because you have 2 instances running leaving few more gigs for OS would actually help.

Also note you should immediately come out of this situation as both SQL Server would be extremely slow.


LSN numbers query regarding Differential backups

While not exactly answering your LSN questions, I think you definitely need to revisit your Recovery Point Objective (RPO).  How much data are you willing to lose?  Assuming good hourly differentials, you should be able to restore back to the latest hour (currently), but you wouldn't really be able to take advantage of any transaction log backups. I'd definitely suggest increasing the frequency of transaction log backups - maybe every 15 minutes.  If they take 10 minutes to complete, then that would most likely be the most data you'd lose - assuming your other backups are good (full and diff).

All backups have enough of the log to be able to restore a consistent database by applying committed changes after the backup started and rolling back transactions that had not completed before the backup completed.

Check out Paul Randal's excellent post on Understanding Logging and Recovery in SQL Server.


What is the simplest way of excluding the current row from the result of an analytic?

There may be a simpler way, but here is a solution.  If the current row is the minimum foo for the bar, then we always want the next (lead) foo.  When there is only one foo for a bar the lead will always be null and for every other case we really do want the minimum foo for the bar.

with w as (select level as foo, decode(level,8,1,mod(level,4))*100 as bar
   from dual connect by level<9)
select foo, bar
   , case
        when foo = min(foo) over (partition by bar)
           then lead(foo) over (partition by bar ORDER BY foo)
        else min(foo) over (partition by bar)
     end pseudolowest
from w;

Unfortunately rows excluding current row doesn't exist.


How to get table columns with datatypes (and lengths) using PowerShell?

The lengths are found at <Column>.Properties['Length'].Value, so you can select it like:

#Get column names
$colNames = dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' |
    Where-Object {$_.DisplayName -match "dbo.MYTABLE"} |
        ForEach-Object {$_.Columns} |
            Select-Object Name, DataType, `
                @{Name='Length'; Expression = {$_.Properties['Length'].Value}}
$colNames


Domain User lockout after SQL Cluster Failover

The problem with finding saved creds in SQL Server is that it's usually stored client side.  You will have to capture people trying to connect to the server, but it's interesting that it only occurs You could run a extended event or a trace and capture failed logins by that user, that will tell you if it's MS SQL Server that is locking him out.  Then you could view what time it occurred and capture other data.

A lot of things could be trying to log in with his AD account.  Even a Excel reporting server elsewhere.  It's interesting the log in attempts stop because after you change the password, it works and it only happens after fail over.

Are you sure it's in the SQL Server instance itself and maybe not something with the cluster group?  Perhaps it's something like a script on a failover disk or something related with the cluster.  I've had scripts do this before, or applications that store login creds.  Services are often a culprit as noted, and other potential configs.


Disable SQL Server 2012 Management Studio remote access

Management studio communicates with SQL Server using the same interfaces as normal clients. You can modify user permissions so they can only read from certain tables, and can't write or alter.


Error deleting FileTable - HRESULT = '0x80070490'

Due to the urgency we do not expect a return of support from Microsoft, but it seems that the error occurred due to attempt to put the database offline.

We solve the error as follows:

Since we had a base equal to the base model which showed the error, made the copy of the data base with an error (except FileTable) for the base model and recreate the FileTable. It was in this way that we solve.

Because we applied the solution before the return of support, they could not diagnose prescision.


Having both a integer Primary Key and a GUID/UUID column on same table-is this bad

I personally don't see a problem with having both a ID and a UID, I use this technique quite a lot actually. However, I still use the primary key to perform the JOIN on as this performs better - the UID is used mainly in the WHERE clause when searching for records.

In your example, the biggest point I would make is not to use a VARCHAR(36) for your UID - I would use BINARY(16) as this fits a UUID perfectly. The CHAR type is 1 byte and so you are using an extra 20 bytes of storage to persist your UUID. Also, joining on this column will be slower than a join on the BINARY(16) equivalent.


How to best store the Google Web Ngram data?

I have the script you need right here

USE test
DROP TABLE IF EXISTS ngram_key;
DROP TABLE IF EXISTS ngram_rec;
DROP TABLE IF EXISTS ngram_blk;
CREATE TABLE ngram_key
(
    NGRAM_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    NGRAM VARCHAR(64) NOT NULL,
    PRIMARY KEY (NGRAM),
    KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
CREATE TABLE ngram_rec
(
    NGRAM_ID BIGINT UNSIGNED NOT NULL,
    NGRAM_COUNT SMALLINT NOT NULL,
    PRIMARY KEY (NGRAM_ID)
) ENGINE=MyISAM ROW_FORMAT=FIXED;
CREATE TABLE ngram_blk
(
    NGRAM VARCHAR(64) NOT NULL,
    NGRAM_COUNT SMALLINT NOT NULL
) ENGINE=BLACKHOLE;
DELIMITER $$
CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
BEGIN
    DECLARE NEW_ID BIGINT;
    INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.NGRAM_COUNT);
END; $$
DELIMITER ;
INSERT INTO ngram_blk VALUES
('rolando',85),
('pamela',86),
('dominique',87),
('diamond',88),
('rolando edwards',185),
('pamela edwards',186),
('dominique edwards',187),
('diamond edwards',188),
('rolando angel edwards',285),
('pamela claricia edwards',286),
('dominique sharlisee edwards',287),
('diamond ashley edwards',288);
SELECT * FROM ngram_key;
SELECT * FROM ngram_rec;
SELECT A.ngram NGram,B.* FROM
ngram_key A,ngram_rec B
WHERE A.ngram IN ('rolando angel edwards','rolando edwards','rolando')
AND A.ngram_id=B.ngram_id;

here is what the sample data generates:

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS ngram_key;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS ngram_rec;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS ngram_blk;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE ngram_key
    -> (
    ->     NGRAM_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     NGRAM VARCHAR(64) NOT NULL,
    ->     PRIMARY KEY (NGRAM),
    ->     KEY (NGRAM_ID)
    -> ) ENGINE=MyISAM ROW_FORMAT=FIXED PARTITION BY KEY(NGRAM) PARTITIONS 256;
Query OK, 0 rows affected (0.53 sec)

mysql> CREATE TABLE ngram_rec
    -> (
    ->     NGRAM_ID BIGINT UNSIGNED NOT NULL,
    ->     NGRAM_COUNT SMALLINT NOT NULL,
    ->     PRIMARY KEY (NGRAM_ID)
    -> ) ENGINE=MyISAM ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE ngram_blk
    -> (
    ->     NGRAM VARCHAR(64) NOT NULL,
    ->     NGRAM_COUNT SMALLINT NOT NULL
    -> ) ENGINE=BLACKHOLE;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER populate_ngram AFTER INSERT ON ngram_blk FOR EACH ROW
    -> BEGIN
    ->     DECLARE NEW_ID BIGINT;
    ->     INSERT IGNORE INTO ngram_key (NGRAM) VALUES (NEW.NGRAM);
    ->     SELECT NGRAM_ID INTO NEW_ID FROM ngram_key WHERE NGRAM=NEW.NGRAM;
    ->     INSERT IGNORE INTO ngram_rec VALUES (NEW_ID,NEW.NGRAM_COUNT);
    -> END; $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> INSERT INTO ngram_blk VALUES
    -> ('rolando',85),
    -> ('pamela',86),
    -> ('dominique',87),
    -> ('diamond',88),
    -> ('rolando edwards',185),
    -> ('pamela edwards',186),
    -> ('dominique edwards',187),
    -> ('diamond edwards',188),
    -> ('rolando angel edwards',285),
    -> ('pamela claricia edwards',286),
    -> ('dominique sharlisee edwards',287),
    -> ('diamond ashley edwards',288);
Query OK, 12 rows affected (0.10 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ngram_key;
+----------+-----------------------------+
| NGRAM_ID | NGRAM                       |
+----------+-----------------------------+
|       11 | dominique sharlisee edwards |
|        1 | rolando                     |
|        9 | rolando angel edwards       |
|        4 | diamond                     |
|        8 | diamond edwards             |
|        2 | pamela                      |
|        3 | dominique                   |
|        6 | pamela edwards              |
|        5 | rolando edwards             |
|       12 | diamond ashley edwards      |
|        7 | dominique edwards           |
|       10 | pamela claricia edwards     |
+----------+-----------------------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM ngram_rec;
+----------+-------------+
| NGRAM_ID | NGRAM_COUNT |
+----------+-------------+
|        1 |          85 |
|        2 |          86 |
|        3 |          87 |
|        4 |          88 |
|        5 |         185 |
|        6 |         186 |
|        7 |         187 |
|        8 |         188 |
|        9 |         285 |
|       10 |         286 |
|       11 |         287 |
|       12 |         288 |
+----------+-------------+
12 rows in set (0.00 sec)

mysql> SELECT A.ngram NGram,B.* FROM
    -> ngram_key A,ngram_rec B
    -> WHERE A.ngram IN ('rolando angel edwards','rolando edwards','rolando')
    -> AND A.ngram_id=B.ngram_id;
+-----------------------+----------+-------------+
| NGram                 | NGRAM_ID | NGRAM_COUNT |
+-----------------------+----------+-------------+
| rolando               |        1 |          85 |
| rolando angel edwards |        9 |         285 |
| rolando edwards       |        5 |         185 |
+-----------------------+----------+-------------+
3 rows in set (0.00 sec)

Give it a Try !!!


You should not put any faith in triggers when it comes to federated tables. You are better off creating a temporary trigger on the real MyISAM table on the source side.

Picture this:

On Server S1
- T1 is MyISAM table
- T2 is FEDERATED table to T1 over on S2

Do the following:

Create the Trigger on T1 to insert a row into T2
Insert Data into T1
Drop the Trigger

ALTERNATIVE

You would be way better off using MySQL Replication as follows:

S1 is the Master
S2 is the Slave
Set replicate-do-table=db1.T1 in my.cnf on S2

This would automatically do all DML against that table provided you replicate to the same named table.


Can connect to SQL Server through tsql but not from the bsqldb

I know this is an old question but for the sake of "drive by googlers" I shall attempt an answer.

I'm assuming you know the backslash doesn't belong in your cmd line and that the servername showing in your output is something random you typed to hide the actual name.

Check this page http://www.freetds.org/userguide/confirminstall.htm

Its got info on troubleshooting your config.

Use this command to confirm your instance / server config:

tsql -LH servername

Make sure your pointing to the proper port, remember you can only specify an instance OR a port, not both.

Once you confirmed your settings use this command to connect to the server and open an interactive prompt from which you can run SQL commands on the server:

tsql -S servername -U username

You will then have to input your password. Remember this user should be created on the DB server itself and have proper permissions to access your DB. If your dead set on using some directory services instead of a local user read the manual http://www.freetds.org/userguide/

Once your logged in enter this:

USE databasename
GO

You are basically making sure your connecting at the right place, then that you are actually able to log in and then manual try to USE the database. This process should give you a better idea of whats going on.

If it still bugging you with the db "master" after that Id say check your server / instance config. Also make sure your using the proper TDS protocol version.


Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables

Tried to create a database with the exact tables, PKs and FKs as yours and I receive no error.
Tried both creating the tables with the constraints and without it, creating the relationships via PHPMyAdmin.

This "Error" you're getting really doesn't have a message or a error number?

To answer you, yes, you can set two columns as Primary Keys that are also Foreign Keys. They should look like this:

person_id_number 1 (referencing p.id_number 1)
email_addres_idnumber 1 (referencing e.id_number 1)

Now, as they are PKs, you can't repeat them. You could only use the Person ID 1 to another Email ID, and vice-versa.

But, as you also have and ID number in your map, there will be some repetitions:

id_number 1
person_id_number 1
email_address_id_number 1

Looks ok, right? But:

id_number 1
person_id_number 1
email_address_id_number 2

Now you have two id_numbers, because your Primary Key consists of three different tables. What I'd suggest you is for you to create the PK as the id_number of the map table and the foreign keys as a Unique Key. It should look like PK(id_number), UK(person_id_number, email_address_id_number).


This is an idea I came up with that can help (see attached image).

The Deals table is simple. It has an ID that defines the details of a deal (like product name, origin, etc). That info through the ID is passed onto a deal_instance table thats designed to setup the individual sales to that deal. This is where your "K" comes in.

Then I added a customer deals table because any number of customers can buy any number of deals and vice versa, but in the customer deals table, the number bought is recorded so you know which customer bought which deal and how many times.

Then you use that information to find out when n people bought the deal and how many times. Once that value is met, then you go on and create another instance for the same deal just by adding another record to the deal_instance table using the same deal ID. If I'm not mistaken, the left most line needs to be an arrow, not a slash. (1 to many relationship).


Is there anyway to change the standard SQL SERVER reports? And/OR See the SQL Behind it?

Yes, run a Profiler trace while you run the report. It's just plain old T-SQL.

(Source: that's how I wrote the Performance Dashboard Reports chapter in the book SQL Server 2008 Internals and Troubleshooting.)


Is there a limit on query time for an Oracle linked server?

The problem turned out to be on the Oracle side:

Tue Oct 18 05:41:17 2016
ORA-01555 caused by SQL statement below (Query Duration=30945 sec, SCN: 0x0aee.4682f3e7):
Tue Oct 18 05:41:17 2016

It's the classic 'Snapshot too old' issue with a long-running transaction and MVCC.


An SQL Server 2008 R2 based program fails when updating stored procedures?

Considering that you're running the upgrade, this SQL Server instance must not be in production, right? If so, why don't you fire up the SQL Profiler tool to see what's happening behind the scenes. The trace should show you where the troublesome spot is. Happy troubleshooting! Hope you solve the problem.


Why does it take so long for Postgres to return a sequence number?

I have studied your question hard, but can't make sense of the procedure you describe. (You might work on the description some more.)

Why would you generate sequence numbers by hand, when you can just have Postgres generate them automatically? Per documentation:

  If a list of columns is specified, COPY will only copy the data in the
  specified columns to or from the file. If there are any columns in the
  table that are not in the column list, COPY FROM will insert the
  default values for those columns.

Bold emphasis mine. The default value for a serial column is the next id from its sequence.

Are you sure you are not doing a lot of redundant work in a very expensive fashion?


raster2pgsql

This a helper to make the job easy. It's not strictly speaking required.

  raster2pgsql  is a raster loader executable that loads GDAL supported raster formats into sql suitable for loading into a PostGIS raster table. It is capable of loading folders of raster files as well as creating overviews of rasters.

You can just redirect the output of raster2pgsql into a file

raster2pgsql > myFile.sql

Then run myFile.sql on Google Cloud.


How do you declare a set-returning-function to only be allowed in the FROM clause?

I am afraid that's only possible with unnest() after all.

unnest() with multiple parameters is a special Postgres feature, that's internally rewritten into multiple unnest() calls that are combined more reasonably than parallel unnest() in the SELECT list would be, i.e. in a sane way if the number of elements does not match.

The behavior was finally sanitized with Postgres 10:

What is the expected behaviour for multiple set-returning functions in select clause?

See:

EXPLAIN (VERBOSE) SELECT *
FROM   unnest('{a,b}'::text[], '{1,2}'::text[]);

Function Scan on unnest  (cost=0.01..1.00 rows=100 width=64)
  Output: unnest, unnest_1
  Function Call: unnest('{a,b}'::text[]), unnest('{1,2}'::text[])

It's a special use case for the ROWS FROM construct introduced with the same Postgres version 9.4. The manual:

  The special table function UNNEST may be called with any number of
  array parameters, and it returns a corresponding number of columns, as
  if UNNEST (Section 9.18) had been called on each parameter separately
  and combined using the ROWS FROM construct.

Bold emphasis mine.

If you try unnest() with multiple parameters in the SELECT list, you get:

  ERROR:  function unnest(text[], text[]) does not exist

In fact, there is no unnest() function with multiple parameters registered in the system:

SELECT proname, proargtypes, proargtypes[0]::regtype
FROM   pg_proc
WHERE  proname = 'unnest';

 proname | proargtypes | proargtypes
---------+-------------+-------------
 unnest  | 2277        | anyarray
 unnest  | 3614        | tsvector

That would be confusing if you were not aware of the first part of my answer.


The native string processing functions in TSQL are not very sophisticated and this approach requires parsing of the validation message to find items delimited by angle brackets.

Then it requires splitting the ReplacementValue by comma (but not if the comma is inside an angle bracket) and finally removing the angle brackets and doing the replacement for each place holder by corresponding ordinal position.

This could be done in TSQL but would likely require some poor performing scalar UDF - or alternatively would need to call out to CLR/Java code for more advanced string processing capabilities.

I suggest restructuring as follows

CREATE TABLE ValidationMessage
(
ValidationMessageID int primary key,
Text varchar(2000)
)

insert into ValidationMessage
values
(1,'Item(s) %s do not exist in project %s.'),
(5,'This value %s is invalid.');

CREATE TABLE ReplacementValues
(
ValidationMessageID INT,
[Index] INT,
Param1 VARCHAR(50) NULL,
Param2 VARCHAR(50) NULL,
Param3 VARCHAR(50) NULL,
Param4 VARCHAR(50) NULL,
Param5 VARCHAR(50) NULL
)

INSERT INTO ReplacementValues(ValidationMessageID, [Index], Param1,Param2)
VALUES
(1,10,'614882,614889','8068637'),
(1,11,'614882','8068634'),
(1,12,'614883','8068639'),
(5,22,'John Smith', NULL);

With an eye on making the final query simple with available functions

SELECT VM.ValidationMessageID,
       FORMATMESSAGE(Text, Param1, Param2, Param3, Param4, Param5),
       [Index]
FROM   ValidationMessage VM
       JOIN ReplacementValues RV
         ON RV.ValidationMessageID = VM.ValidationMessageID

db <> fiddle


psql: how to turn off context messages at raise notice

The solution :
As write – dezso Feb 17 '15 at 14:56, using \set VERBOSITYE 'terse' is the right way.
I've to add this option in my scripts using psql :
psql --dbname=DB_production --VERBOSITY=terse

But, I can't understand why on others instances, servers, environments, I haven't to do this.


Return count from not direct related tables

If your dealers are related to the manufacturers and entries are related to the dealers so you have to JOIN three tables, then GROUP resulting table by manufacturers and COUNT() all entries grouped together:

SELECT w.ID AS manufacturer
     , count( q.ID ) AS counter
  FROM manufacturers AS w
  JOIN dealers       AS z ON z.manuf_ID  = w.ID
  JOIN entries       AS q ON q.dealer_ID = z.ID
 GROUP BY w.ID
 ORDER BY w.ID ASC
;


The tmp tables won't be in /mysql/data, but rather in the subdirectory corresponding to the database.  Or maybe in the tmpdir directory.

key_buffer_size = 2344M -- that is just for indexes.  In a 4GB machine, that does not leave enough room for data.  Change it to 700M.

thread_cache_size -- 0 is better for Windows.

query_cache_size=132M -- too big; drop to 50M

max_allowed_packet=156M -- Probably too high.  Suggest 32M.

None of those necessarily explain the symptoms you have.  Can you tell if mysql is "swapping"?  If it is, that would be deadly, and my suggestions would probably prevent swapping.

Is the "repair" because of a crash?  If so, you should seriously consider switching to InnoDB.  At least put it on your roadmap.  But be aware that the disk footprint with be 2-3 times as big.

"Repair by sorting" would, I think, proceed as follows:

Load all the data into the #sql...MYD file.  Meanwhile the .MYI file would be 1K or empty.
Read the data, sort it, and load it into the .MYI for one index.
Ditto for second index.

I do not know if having two unique index complicates things, and necessitates use of the key_buffer.


Try mysqlcheck --all-databases.  That will probably say that mysql.user needs repair.  Then do

mysqlcheck --all-databases --repair

Ref: https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html


MySQL: Is there a way to find out which user called stored procedure?

Back on Jan 10, 2012, I answered MySQL error: Access denied for user 'a'@'localhost' (using password: YES) where I show two function calls:

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

Since CURRENT_USER() shows the definer, try using USER(). It might display the MySQL user and the host where that user was coming in from. Please note that this may not work if USER() is an internal thread running the Stored Procedure.


Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instances

You can obtain most of the info from your screen shot from xp_msver. It goes back as far as SQL 2005. The other information might be in sys.configurations: http://msdn.microsoft.com/en-us/library/ms188345.aspx


How to use WITH RECURSIVE with values in an array?

Evidently, the answer required me to step away from it for a little bit.

Here's the query that solved my problem:

WITH RECURSIVE descendents AS (
  SELECT parent,unnest(children) AS child
  FROM offspring o
  WHERE parent='aaaaaaaa-8865-4b40-a482-78fc8a27d1bb'
    UNION ALL
  SELECT o.parent,unnest(o.children) AS child
  FROM offspring o, descendents d
  WHERE o.parent = d.child)
SELECT distinct(child) FROM descendents;

SQL Fiddle


SQL Server Full Text Search Engine is not returning consistent results

Took me a little while to see what now seems obvious. I can't seem to replicate your experience with John v. Smith on 2012, but if we check out the default system stopwords...

select * from sys.fulltext_system_stopwords
WHERE stopword = 'v.'

The result is

stopword    language_id
v.          1053

So it seems the stoplist is the problem. Let's try a blank one and see what happens:

CREATE TABLE [dbo].[fts_table](
    [name] [nvarchar](50) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
  CONSTRAINT [PK_fts_table] PRIMARY KEY CLUSTERED
  (
    [id] ASC
  )
GO

INSERT INTO [dbo].[fts_table]
           ([name])
     VALUES
           ('John v. Smith'), ('Bockelman v. MCI Worldcom, Inc.')
GO

--Pretend I scripted out the fulltext index creation here

CREATE FULLTEXT STOPLIST [modified_stoplist]
AUTHORIZATION [dbo];
GO

ALTER FULLTEXT INDEX ON [fts_Table]
SET STOPLIST = modified_stoplist;
GO

Then the query now returns the results desired:

SELECT  *
 FROM CONTAINSTABLE(dbo.fts_Table, name,
'FORMSOF(INFLECTIONAL, ''Bockelman'')
AND FORMSOF(INFLECTIONAL,''v'')
AND FORMSOF(INFLECTIONAL, ''MCI'')
AND FORMSOF(INFLECTIONAL, ''Worldcom'')
AND FORMSOF(INFLECTIONAL, ''Inc'')')
   AS KEY_TBL

Returning...

KEY     RANK
1       32


Access to performance counters from T-SQL procedure

This should be possible via SQLCLR. The PerformanceCounter class is in in System.dll, which is one of the Supported .NET Framework Libraries. The Assembly containing the custom .NET code will need to be set to at least EXTERNAL_ACCESS.

I will try this myself later and update this answer with the results.


Did you make any changes in my.cnf file recently? Most of the time MySQL fails to start due to any incorrect changes made in its conf file.

Next option is to check the ownership and permission of /var/lib/mysql. It should be owned by MySQL user and not the root user.


Help designing tables for storing lab test results: values include numbers, number

At first glance, it seems like the most appropriate thing to do would be to make the column storing the test result value nullable, and declare an additional column, perhaps an ENUM, for the out-of-range condition.  For example

CREATE TABLE ...
   ...
   result_value DECIMAL(6,3) DEFAULT NULL,
   out_of_range ENUM('low','high') DEFAULT NULL,
   ...

When a test comes back with an out-of-range result, it would seem like you wouldn't want to store anything in the `result_value` column, because when it comes to averages or other statistics the low or high threshold value would completely skew your average... but when you take the AVG() of a data set that includes NULL the denominator used to calculate the average is the total number of not-NULL values... so AVG(5,NULL,NULL,10,NULL) would be 7.5.

Setting a flag that indicates that your result was, instead, out of range, would allow you to easily tally those values separately, and an ENUM column with a small number of possible values requires only 1 byte of storage per row... the "low" or "high" would actually be stored as the byte 0x01 or 0x02, with the corresponding labels stored in the table definition only.

When the minimum threshold is "5" the number "5" is not a meaningful number when it comes to analysis, so I would think that you wouldn't likely want to store that in the `result_value`.  If you did, your analytic queries would have to go to extra steps to exclude those from calculations.

I would suggest that you do need a table that specifies the high and low boundaries for each test, because such a table could be used to constrain the data going into the results table via triggers, blocking the insertion of out-of-range data, in addition to being valuable information when doing analysis.

"But we already know those values."  The database also needs to "know" those values, because subsequent analysis will be more straightforward if those values can be joined and understood by a query.

Also, I would suggest that if there is any possibility that the sensitivity, selectivity, granularity, precision, range, or whatever the proper term might be, of any test could change -- due to improved technology or whatever the reason, that you consider each test to actually be a 1 of N possible subtypes of a type of test (where initially, N is 1).  If the possible range (or other property) of a result is different, then that's technically, arguably, a different test, even though it's measuring the same thing... and these are the kinds of things that are a small hassle now, and a much bigger hassle later.

  needing to add new ones easily isn't a concern.

Famous last words.  Speaking of bigger hassles later, that's generally not a good position to hold, even if it seems unshakable now.

A final word about a common design error I sometimes encounter: If you are using the FLOAT or DOUBLE data types, reconsider that decision, because they are imprecise.  A DECIMAL column has a fixed precision and stores exactly the value you insert.  Data stored in FLOAT and DOUBLE columns are stored as approximate values which can lead to problems with comparisons.  This limitation is one of floating point arithmetic in general, and not of MySQL in particular; it is something with which you may already be familiar but I thought it worth mentioning.


Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries)

With only that information, I can give only the below.
Yes, I am aware that the bug is fixed in 12.2 and you wrote in the title the database was 12.2 (but no other proof).
ORA-04031: ("shared Pool".."KKSSP^nnnn","kglseshtSegs") (Doc ID 2151847.1)

SOLUTION

Download and apply Patch 23315153.

In case the needed patch is not available, please open a Service
Request with Oracle Support.

The fix is included in Oracle release 12.2.

The bug has no workaround.


You cannot move just indexes to another location. You have to move both data and indexes.

When you use innodb_file_per_table, this will create a file with the extension .ibd.

For example, if you have an InnoDB table as follows

datadir /var/lib/mysql
database mydb
table mytable

The physical files are the following

/var/lib/mysql/mydb/mytables.frm
/var/lib/mysql/mydb/mytables.ibd

The file /var/lib/mysql/mydb/mytables.ibd contains both data and index pages for mydb.mytable.

If you have another disk (says an SSD or HDD) and you want the .ibd file to be moved over there, you may have to use the DATA DIRECTORY directive for the CREATE TABLE command.

Here is what the MySQL Documentation for CREATE TABLE says on this:

  DATA DIRECTORY, INDEX DIRECTORY

  For InnoDB, the DATA DIRECTORY='directory' option allows you to create
  new InnoDB file-per-table tablespaces in a location outside the MySQL
  data directory. Within the directory that you specify, MySQL creates a
  subdirectory corresponding to the database name, and within that a
  .ibd file for the new table. To use the DATA DIRECTORYoption with
  InnoDB tables, the innodb_file_per_table configuration option must be
  enabled. The directory must be the full path name to the directory,
  not a relative path. See Section 14.5.4, “Specifying the Location of a
  Tablespace” for additional information.

Just a heads up on this: INDEX DIRECTORY only applies to MyISAM not InnoDB.
Suppose you have an SSD mounted on /innodb_data and you ran chown -R mysql:mysql /innodb_data

Here is how you can move mydb.mytable (by essentially copying)

CREATE TABLE LIKE mydb.mytable_new DATA DIRECTORY '/innodb_data' LIKE mydb.mytable.
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

If you are concerned with indexes in RAM, just tune the InnoDB Buffer Pool size.

Give it a Try !!!


How do graph databases differ from older navigational (hierarchical/network) databases?

Graph databases are largely irrelevant. What matters is the graph processing engines that use them. For that a dedicated graph database is not necessary: graphs can be represented in any sorts of stores: relational database, "nosql" databases (HBase, ...). Or even flat file structures. Some graph processing engines include their own dedicated graph store (that is the case for Neo4J and Graph). Others (Oracle Property Graph) let you use any storage you like.

In terms of processing capabilities, graph processing is very different from what was possible with the old Codasyl/Network databases. Graph processing lets you do graph analytics: finding influencers in social networks (page rank, closeness centrality, betweenness centrality ...), community detection, shortest paths, clustering ... They also let you traverse graphs using specialized languages such as PGQL or Cypher (for Neo4J). This is used to detect cycles: very useful for analyzing financial fraud and money laundering. Graph techniques have been used to analyze the Panama Papers for example. Same techniques are commonly used to analyze dependencies in complex processes, like car manufacturing or IT infrastructures. Note that work is under way to extend the SQL language with graph traversal expressions.

I don't know much about DGraph. A look at the documentation lets me believe that it does not provide any of the above: no graph analytics, and no actual graph traversal language.  That may be because it actually implements graphs as RDF (semantic) graphs where data is abstracted as triples. RDF graphs can be constrained using ontologies that allow new triples to be inferred. RDF triple stores are generally queried using the SparQL language, but DGraph does not seem to support that either.

It would be interesting to know what sort of use case you have in mind that you think would be better solved by graph techniques, rather than relational techniques.


Cannot connect to . A severe error occurred on the current command

When applying any changes to the SQL Server services it is advisable to do this via the SQL Server xxxx Configuration Manager to guarantee that any related permissions (files / directories / databases / instances) and policies are set correctly.

Fixing Service Accounts (Permissions and All)

We will reset all the permissions for files, directories and registry settings incl. any policies that may exist according to the requirements of the service accounts by following these quick steps.

Change the Service Accounts to Local Accounts

Open up the SQL Server 2016 Configuration Manager and navigate to the SQL Server Services branch.
In the right pane of the Configuration Manager right-click on the SQL Server service and select Properties.
In the Properties of SQL Server (InstanceName) window check that you are in the Log On tab.
Change the selector to Built-in account and select one of the three options:

Local System
Local Service (preferred; least privileged)
Network Service

Press Apply and when I dialog pops up notifying you that "...any change you have made will not take effect until you reboot" click on OK.
Stop the SQL Server Service (InstanceName)
Start the SQL Server Service (IntanceName)
Double-check the SQL Server ERRORLOG for any issues.
Repeat steps 2 to 8 for the SQL Server Agent (InstanceName) service.

Change the Service Accounts back to Group Managed Service Accounts

Open up the SQL Server 2016 Configuration Manager and navigate to the SQL Server Services branch.
In the right pane of the Configuration Manager right-click on the SQL Server service and select Properties.
In the Properties of SQL Server (InstanceName) window check that you are in the Log On tab.
Change the selector to This account and enter the details of your gMSA for the SQL Server (InstanceName) service.
Press Apply and when a dialog pops up notifying you that "...any change you have made will not take effect until you reboot" click on OK.
Stop the SQL Server Service (InstanceName)
Start the SQL Server Service (IntanceName)
Double-check the SQL Server ERRORLOG for any issues.
Repeat steps 2 to 8 for the SQL Server Agent (InstanceName) service.

The services should now have been re-configured according to the requirements of the individual services.


SQL Server 2008, Sum two columns from different tables

You are probably getting a syntax error from:

Select
        CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CODE

You probably need a full group by:

Select
        CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CMP, CODE

or if CMP is functionally dependent on code you can apply an aggregate for CMP to make it valid:

Select
        MAX(CMP) as CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CODE

Next thing I find peculiar is that you are doing a cross join between your two sub-selects, are they guaranteed to return exactly 1 row each? I would guess that what you are trying to do should be something like:

SELECT
    cash.CMP as Name,
    cash.CODE,
    cash.sum_cash + bal.FORECAST as Totalization
From(
    Select MAX(CMP) as CMP, CODE
         , sum(CASE when BUDGET in ('4','25')
                    then AMOUNT
                    else AMOUNT * -1
               end) sum_cash
    From TEST1
    where Nature=12
    GROUP BY CODE
) cash
JOIN (
    SELECT CODE, FORECAST
    FROM TEST2
    where BALANCE_TYPE=-2
) bal
    ON cash.code = bal.code


How to shard 14 gb of data for archival database?

Any full featured database will be able to handle this with ease. My preference would be for Postgres, however SQL server, Oracle and MySQL are all valid options.

When designing the tables you need to consider how you will read the data so that you can optimize read performance. There are a couple of things to look at.

1> Appropriate indexes for the queries you are going to run.
2> The use of Partitioning (read the appropriate manuals). You would use partitioning in this case perhaps by having a partition per state assuming the queries that need to perform affect only one state's data at a time.
3> Disk and network.  These can often be the cause of slow reads so make sure that they are as fast as you can afford.  Consider if you need to have a RAID type replication or can live without this.  i.e.: do you need 100% up-time [RAID] or can you afford to have some downtime [single disk and backup] if there is a disk failure.
4> If you have a choice of storage methods with the chosen DB, choose the most appropriate t the task at hand. Again refer to the DB's documentation for advice.
5> Maintenance plan. Ensure you regularly rebuild fragmented indexes and tables, this will help prevent slowing performance over time.

When it comes to RAM, get as much as you can fit into the server you have.


Use LINES TERMINATED BY '\n' or just leave it out altogether.  '\n' is the linefeed character on Linux, and with '\n\r' it will never see a linefeed, so the entire file looks like one long line, and you told it to ignore 1 line, so it sees nothing to insert.

You also need  "OPTIONALLY ENCLOSED BY '"'.  Try something like this:

mysql> LOAD DATA LOCAL INFILE 'routes.txt'
    INTO TABLE routes FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM routes;
+----------+--------+-------------+----------+
| route_id | origin | destination | distance |
+----------+--------+-------------+----------+
|        1 | ABE    | ATL         |  692.000 |
|        2 | ABE    | DTW         |  425.000 |
|        3 | ABE    | ORD         |  655.000 |
+----------+--------+-------------+----------+
3 rows in set (0.00 sec)

Obviously, leave off LOCAL if using a file on a remote server and be sure the file is readable by MySQL.


How to have MS Analysis server setup of both modes: multi dimension and tabular

When you begin installation wizard via new stand-alone Installation option, you might have missed to select Perform a new installation .. option at Installation type which is first thing to select in the wizard.

After this you would proceed with Product key -> License terms -> Feature selection as Tabular

Or following command straight-away install new SSAS Instance with Tabular mode, command has to be run from the same directory of installation media or relace Setup.exe with media path.

Setup.exe /q /IAcceptSQLServerLicenseTerms /ACTION=install /FEATURES=AS /ASSERVERMODE=TABULAR /INSTANCENAME=SSAS_Tab /INDICATEPROGRESS /ASSVCACCOUNT=.\administrator /ASSVCPASSWORD=<administratorpw> /ASSYSADMINACCOUNTS=.\administrator


How do you restore a particular table in MySQL?

I'm not familiar with MySQL, so don't have a straight line of directions. What I'd do is to restore the last backup (do you have one?) of the database on the same or a different server and copy the missing table table to the main on-line database.

PS: oh, and also remove the write permission for that user :-).


Do not replicate Delete operations AWS DMS

I've recently encountered a similar problem (my source endpoint is SQL Server and target endpoint is Redshift) and after a futile search through AWS documentation, I've contacted AWS support. They have confirmed that there is no direct way to filter out replication of DELETE statements while using a single DMS replication task.

However, they have suggested a workaround, which I think might also solve your issue.

Basically, you need to set up a migration task from SQL Server to S3, which serves as an intermediary step, and then from S3 to the final destination (which in your case is again the SQL Server).

Reason for this is that when you are setting up the target endpoint, you can specify an Extra Connection Attribute (ECA) to modify its behaviour. One of these ECAs can actually filter out all UPDATE and DELETE statements, but strangely, it is available only when creating an S3 target endpoint.

Set up a migration task from SQL Server to an S3 bucket, which will serve as an intermediary. As a target endpoint, choose an S3 bucket and at the bottom of the endpoint setup screen at the 'Endpoint-specific settings', you specify an ECA cdcInsertsOnly=true; This way, only INSERT statements are written to the output file (either .csv or .parquet) during the CDC load. [1]
Set up a second DMS task to migrate the now filtered data from S3 to the target endpoint of your choice. You will need to create a source endpoint for the S3 bucket from previous steps. You will also need to provide a JSON with external table definition so that DMS is able to replicate the data correctly. [2]

Of course, it is possible that this inability to filter out DELETE statements only applies to the Redshift target endpoint and there is some easier way to solve your problem. However, the evidence suggests that there is indeed no direct way since the ECA cdcInsertsOnly is available only in S3 target endpoint and not in SQL Server nor Redshift.


SQL Query: Print string (i.e. header) in output before returning table results

I stumbled upon this thread which contained a suitable solution. I ended using a query similar to the following to generate my desired results:

select customer_account, store_number, invoice_total
from ((select 'Frank’s Fish Supply' as customer_account, '' as store_number, '' as invoice_total, 1 as which
      ) union all
        (select 'Remit To:' as customer_account, '' as store_number, '' as invoice_total, 2 as which
      ) union all
        (select '123 First St., Wonderland, HI 12345' as customer_account, '' as store_number, '' as invoice_total, 3 as which
      ) union all
        (select '' as customer_account, '' as store_number, '' as invoice_total, 4 as which
      ) union all
        (select 'customer_account' as customer_account, 'store_number' as store_number, 'invoice_total' as invoice_total, 5 as which
      ) union all
      (select customer_account, store_number, invoice_total, 6 as which from arinvc where company LIKE '%Joe’s Bait Shop%'
      )
     ) t
order by which;


The behavior you are seeing here is due, in a general sense, to the fact that the Unicode Collation Algorithm (UCA) allows for complex, multi-level sorting. More specifically:

Sorting is not Comparison:

Determining whether two strings are the same or different is fairly straight forward (given a particular locale/language and set of sensitivities). But determining the order of 2 or more strings can be highly complex.
Sorting is done in a series of steps, with each step applied to the entire string, not character by character:

Standard: sort base characters (regardless of accent and case differences)
IF Accent-sensitive, apply accent / diacritic weights
IF Case-sensitive, apply casing weights

When you sort by col1 (single character), it first determines that all characters have the same weight since they are all "e". Next, it applies the accent / diacritic weights. There are no casing differences so the third step wouldn't change anything. So the only differences are in step 2, which is why there is a preferred order for those rows based on col1.

When you sort by col2 (two characters), it first determines that each row has a different weight since both characters are used to determine the sort weight (e.g. "ea", "eb", etc). Next, it applies the accent / diacritic weights. There are no casing differences so the third step wouldn't change anything. So there are differences in steps 1 and 2 this time. But since the differences in step 1 have already been applied to each string before the weights of step 2 are considered, the weights from step 2 don't have any effect on the ordering; they would only apply if weights from step 1 for two or more rows were the same.

The following adaptation of the sample code from the question hopefully illustrates the sorting behavior described above. I added some additional rows and an additional column to help show the impact of the Collation being case-sensitive (since the original sample data is all lower-case):

SETUP

USE [tempdb];

-- DROP TABLE dbo.OddSort;
CREATE TABLE dbo.OddSort
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    col1 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col2 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col3 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS
);
GO

INSERT dbo.OddSort (col1, col2, col3)
VALUES (N'e', N'eA', N'e A')
     , (N'ê', N'êE', N'ê E')
     , (N'é', N'éH', N'é H')
     , (N'ë', N'ëC', N'ë C')
     , (N'E', N'EG', N'E G')
     , (N'Ë', N'ëh', N'ë h')
     , (N'è', N'èD', N'è D')
     , (N'é', N'éB', N'é B')
     , (N'ë', N'ëH', N'ë H')
     , (N'ē', N'ēF', N'ē F');

TEST 1

SELECT [id], [col1], UNICODE([col1]) AS [CodePoint]
FROM dbo.OddSort
ORDER BY col1;

Returns:

id    col1    CodePoint
1     e       101
5     E       69
8     é       233
3     é       233
7     è       232
2     ê       234
4     ë       235
9     ë       235
6     Ë       203
10    ē       275

What we can see in the results above:

The Code Point is not determining the sort order
The non-accented characters are sorted before the accented characters (within the same letter: f would still come after all of these). Clearly, accent weights are applied before case weights.
Lower-case sorts before upper-case within the same accented (or non-accented) character (i.e. the e then E, and the ë then Ë). This tailoring is used by most of the Windows Collations, while most of the SQL Server Collations sort upper-case first.

TEST 2

SELECT [id], [col2]
FROM dbo.OddSort
ORDER BY col2;

Returns:

id    col2
1     eA
8     éB
4     ëC
7     èD
2     êE
10    ēF
5     EG
3     éH
6     ëh
9     ëH

What we can see in the results above:

First-level sorting truly is the base characters. If it were accents / diacritics then the ëC (id = 4), ēF (id = 10), and EG (id = 5) rows would not be where they are. If it were casing, then the EG (id = 5) row would not be where it is.
Second-level sorting truly is the accents / diacritics. This explains why the last three rows are éH -> ëh -> ëH instead of ëh -> éH -> ëH (i.e. IDs 3 -> 6 -> 9 instead of 6 -> 3 -> 9).
Third-level sorting truly is the casing. This is why the last 2 rows are ëh -> ëH, since lower-case sorts first.

TEST 3

SELECT [id], [col3]
FROM dbo.OddSort
ORDER BY col3;

Returns:

id    col3
1     e A
8     é B
4     ë C
7     è D
2     ê E
10    ē F
5     E G
3     é H
6     ë h
9     ë H

What we can see in the results above:

The sort order is exactly the same as in Test 2. The only difference in the test values here is that there is a space between each character, removing the possibility of contextual rules. Hence, we know that the reason for the difference in sort order for col2 in the question is again due to "Multi-Level Comparison", and not "Contextual Sensitivity".

Additional notes:

With regarding to getting the exact rules, that is not as easy as it should be. The problem with getting concrete explanations of these rules is that the Unicode sorting rules, while definitely documented, are a recommendation. It is up to vendors, such as Microsoft, to implement those recommendations. Microsoft did not implement the recommendations exactly as stated in the Unicode documentation so there is a disconnect there (similar to how neither the HTML or CSS specifications are implemented as completely, nor even in the same way, across vendors). Then, there are different versions of the Windows Collations (you are using version 100 which came out with SQL Server 2008) and that is tied to a Unicode version that is much older than the current version of Unicode or of the ICU Collation demo is using. For example, the What's New in SQL Server 2008 Collations section of the "Collation and Unicode Support" documentation for SQL Server 2008 makes 2 very interesting points about what is "new" to the _100_ series Collations:

  Unicode 5.0 case table.

Unicode 5.0 was published in July of 2006 (well, the character database was released then, and the full spec followed in late 2006). The current version is 10.0 which was published in June of 2017. And given the release pattern of the past 4 years, it is likely that version 11.0 will be out sometime mid-2018.

  Weighting has been added to previously non-weighted characters that would have compared equally.

Those weights were more than likely defined in the Unicode Standard, just not in this implementation of it.

Still, the UCA documentation linked above is a good place to start.
Sort Keys used by Windows / .NET / SQL Server are not exactly the same as shown in the Unicode Standard (See @Patrick's answer) or implemented in the ICU. To see what Windows / .NET / SQL Server use, you can try the CompareInfo.GetSortKey Method. I created a SQLCLR UDF to pass in these values and get the sort key. Please note that I am using SQL Server 2017 on Windows 10 with .NET Framework 4.5 - 4.6.1 installed, so .NET should be using Unicode 6.0.0. Also, Level4 is not being used for these strings.

CHAR    L1     L2     L3     L4
e      0E21
E      0E21           12
ë      0E21    13
Ë      0E21    13     12

Looking at these sort keys for Test 1, and realizing that the levels are sorted like multiple columns within an ORDER BY clause (L3 is sorted within same values of L2, which is sorted within same values of L1), should illustrate that the reason for the behavior noted in the question is in fact the multi-level sorting capability of Unicode. Likewise:

CHAR       L1         L2       L3       L4
EG      0E210E25              1212
éH      0E210E2C      0E      0212
ëh      0E210E2C      13
ëH      0E210E2C      13      0212

Looking at some of the sort keys for Test 2, we can see that base characters are sorted first (L1), then accents are sorted (L2), and then casing is sorted (L3).
Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.
The weights described in the Default Unicode Collation Element Table (DUCET) (Version 5.0.0 which should map to the _100_ series Collations) are fine for US English, but not other locales / languages. Other languages need to start with the DUCET and then apply locale-specific override rules as defined by the Common Locale Data Repository (CLDR) project. From what I can tell, versions 1.4 / 1.4.1 were released in 2006. To get those overrides, download the CLDR 1.4 "core" file via http://unicode.org/Public/cldr/1.4.0/core.zip, then, in that zip file, go to the collation folder and find the XML file corresponding to the locale being used. Those files contain just the overrides, and are not complete sets of collation rules.


Choosing the right field data type in SQL Server 2008

You should use varchar(max). The text data type "will be removed in a future version of Microsoft SQL Server."

Related on SO Using varchar(MAX) vs TEXT on SQL Server

  The basic difference is that a TEXT type will always store the data in
  a blob whereas the VARCHAR(MAX) type will attempt to store the data
  directly in the row unless it exceeds the 8k limitation and at that
  point it stores it in a blob.


Why the backup file size is much larger than the restored database

23068672 bytes = 22 MB, not 20 GB.

If it were really 20 GB the only thing I could think that would make it that big would be reusing the same file for multiple backups and appending each backup to it.


Pull all parent records with or without child record - if child record exists select latest record

In your WHERE clause, your v1.VMDateLink = SELECT MAX(VWDateLink) ... will not be true since the MAX will return NULL, and NULL is never equal to anything including NULL.

Try adding OR v1.VMDateLink IS NULL, but in general, I would create an inline view on vac_watch_check_dates with a single row per VWLink with the max VMDateLink.


Is there a way to get the SQL corresponding to all of the locks held by a session, instead of just the most recent batch?

Only in XEvents/Trace by capturing the begin/end statement and acquisition and release of the locks. And that is a very verbose thing to trace, so you would rarely turn on such a trace on a production system.
sys.dm_tran_locks lists all the locks, but there's no DMV that stores the statement that caused the lock to be acquired.


Failover Cluster Instance work without quorum

I agree with @Spörri but to add to his answer :
You have 3 nodes, and then you shut one down leaving you with 2. With 2 nodes(votes), there is no sure way the cluster can maintain a failure.If both nodes vote for themselves it will mess things up. So to avoid this, it sets the one node to 0 votes.
That way, only one node can vote. This does however mean that if the active node fails, you will have the entire cluster fail because the inactive node is not participating in voting at this point in time.
The easy resolution for this, is to add a file share or disk witness.
This will resolve a situation like this and is recommended for a fail over configuration.
With Dynamic quorum, having all 3 nodes active, the witness will be set to 0, as you already have 3 votes, a odd number. However when a node is shutdown/fails etc, the witness will gain a vote, allowing you to still have a odd number voting, meaning that you can loose 2 full nodes before your cluster is in trouble, whereas with your current configuration, once one node goes down your cluster is at risk of failing.
See this table for more info : Understanding cluster and pool quorum


Replicating multiple databases into one database

How do you manage the primary keys on those 5 instances? Unless you carefully planned this ahead so that each instance generate non-overlapping keys on each instance, on each table (sounds like you didn't) there is no way aggregate the data. See Managing Identity Columns for identity keys. For application generated key it gets more complicated as is the application responsibility to generate non-overlapping keys.

After you set up proper key management on each of the 132 table involved on each of the 5 instances, you can set up replication, making sure you apply the initial snapshot each time carefully not to overwrite existing data.


Any option for mysqldump to ignore databases for backup?

Back on Dec 16, 2011, I answered the question How do you mysqldump specific table(s)?

I collected all tables not including a certain set of table names.

Using the same principles, you could have collect all the database names from the metadata table information_schema.schemata that you want mysqldump'd, create a query to return that list, then use that list of databases to formulate the mysqldump command.

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

All you need to do is put the databases you do not want mysqldump'd into DATABASES_TO_EXCLUDE

Give it a Try !!!


How can I configure maximum number of concurrent connections WITHOUT SSMS?

This should do it:

EXEC sp_configure 'user connections', 0;
RECONFIGURE

That will set the number of connections back to the default, which is 32767.

Once you have SSMS open, disconnect from the server in the "Object Explorer" Window.  Then you should be able to create a new query window and connect to it there.  Alternately, you could use sqlcmd.exe to connect from the command-line without having SSMS open, then run the command from there.

The setting is stored in the master database, and can be seen through the following statement:

SELECT *
FROM sys.configurations c
WHERE c.name = 'user connections';

sys.configurations is actually a view, defined as:

CREATE VIEW sys.configurations AS
    SELECT configuration_id,
        name,
        value,
        minimum,
        maximum,
        value_in_use,
        description,
        is_dynamic,
        is_advanced
    FROM sys.configurations$
    WHERE is_not_use = 0

The only way to see the sys.configurations$ table is through the Dedicated Admin Connection, more commonly known as the DAC.  The DAC needs to be turned on through SQL Server Configuration Manager, since it is off by default in SQL Server Express.


You did blunder by restarting SQL Server. You should have not done that.Unless SQL server will recover the database fully and comes to state where database finds itself in same consistent state as it was before the force shutdown it will show in recovery state. Your best bet is to wait and watch you cannot run any transaction on recovering database. Also since you manually killed SQL server process while it was  rolling back a query its even more worse. Now when SQL server database will come online the whole rollback process will start from beginning not from point where you killed SQL server process/query. So it might take time and unless it fully rolls back the previous killed query it won't come online neither you can force it to.

One advantage you can have is if you have Enterprise edition SQL Server will come online after redo phase of recovery. So that might be little relief to you.

PS: Seriously avoid any such random suggesstion given on web. You should take this as a lesson to never kill SQL server process or restart SQL Server if certain query is taking time. You should wait and chec rollback status of query using below script

select session_id,
blocking_session_id,
wait_type,
percent_complete,
((estimated_completion_time)/1000) as Estimated_Complettion_time_Sec,
((total_elapsed_time)/1000) Toatal_Elapsed_time_Sec
from sys.dm_exec_requests where status='recovery'


A residual predicate is one that has an hidden extra cost because the predicate has to be tested again on every combination of rows that is fetched.

See Probe Residual when you have a Hash Match – a hidden cost in execution plans by Rob Farley.


fetch data from multiple tables using single query

For the above requirement, following query should work.

SELECT j.jobid, j.name, af.id, af.kind, af.location
FROM jobs j
INNER JOIN job_queues jq ON jq.id = j.job_queue_id
INNER JOIN organisations o ON o.id = jq.organisation_id
LEFT OUTER JOIN job_attached_files jaf ON jaf.job_id = j.id
INNER JOIN attached_files af ON af.id = jaf.attached_file_id
WHERE o.Id = 1

P.S: I am posting this query without any testing, so if there are any compilation errors or any unwanted results, please correct the query if you want or notify me, so that I can edit my answer accordingly


Performance of query with complex where clause on 10M rows

A few suggestions:

Remove the DISTINCT as the ID is the primary key. There is no way you'll get duplicate rows in the result.
Do not convert the datetime columns. This makes your conditions non-sargable and the query will always do a table scan. The variables would need no conversion either if they are declared as dates but that is not a problem for sargability.
Use close-open ended ranges for date and datetimes, as @Aaron suggests in the comments. Please read his blog post: Bad habits to kick : mis-handling date / range queries .
Use NOT EXISTS instead of (SELECT COUNT(*) ...) = 0 to check if there is no rows matching some condition.
Drop the WITH (NOLOCK) hints. Another blog post: Bad habits : Putting NOLOCK everywhere and a question in this site with several valuable points: Is NOLOCK always bad?.
Add appropriate indexes. I'd guess separate indexes on (Date1) and (Date2) or an index on (Date1, Date2) would be ok but that needs testing. An index on (CaseId, Date1) will be useful for the subquery.
(Minor note) always use the schema prefixes. Another blog post by @Aaron Bertrand: Bad habits to kick : avoiding the schema prefix.
Add OPTION (RECOMPILE) (as @Mikael Eriksson suggested). This basically tells the optimizer to not rely on cached plans but spend some time for each query run to recompile the query - i.e. produce a new plan according to the new parameter values. With 7 variables that can change the query substantially, this seems a very good option. Read the article from @Paul White for more detailed explanation of the "Parameter Embedding Optimization", parameter sniffing and also other options and advantages: Parameter Sniffing, Embedding, and the RECOMPILE Options.

The query rewritten:

SELECT
    mt.ID
FROM
    [dbo].[MyTable] AS mt
WHERE
      (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
  AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
  AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
  AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
  AND ( @status IS NULL
     OR @status <> 2
     OR ( @status = 2
          AND NOT EXISTS
              ( SELECT *
                FROM dbo.MyTable AS mt2
                WHERE mt2.CaseID = mt.CaseID
                  AND mt2.Date1 > mt.Date1
              )
        )
      )
OPTION (RECOMPILE) ;

The part about @status can be written in a slightly more compact way.

I don't think this will make much difference (if any) in performance and it may look obfuscated but I add for completeness this version as well:

SELECT
    mt.ID
FROM
    [dbo].[MyTable] AS mt
WHERE
      (@aVariable       IS NULL OR mt.Date1 >= CAST(@date1 AS DATE))
  AND (@aSecondVariable IS NULL OR mt.Date1 < DATEADD(day, 1, CAST(@date1 AS DATE)))
  AND (@aThirdVariable  IS NULL OR mt.Date2 >= CAST(@date2 AS DATE))
  AND (@aFourtVariable  IS NULL OR mt.Date2 < DATEADD(day, 1, CAST(@date2 AS DATE)))
  AND NOT EXISTS
      ( SELECT *
        FROM dbo.MyTable AS mt2
        WHERE @status = 2
          AND mt2.CaseID = mt.CaseID
          AND mt2.Date1 > mt.Date1
      )
OPTION (RECOMPILE) ;


postgresql: how to define a JSONB column with default value

That's the same as with any other default value:

create table amsterdam
(
   id       integer primary key,
   payload  jsonb not null default '{}'::jsonb
);


@nbk had given what you want - atleast what you made us to understand.

But...

Think of re-designing your whole job. Do not go by your fluctuating number of columns. There are better ways. json is one. Transactional database is what is been used in lot of projects. Those are the right methods to store / retrieve than having variable number of columns.


Changing the SGA and/or PGA in Oracle 11g affects the performance of the queries?

I'll outline SGA and PGA, and then will try to give you a couple of appropriate examples.

SGA consists of many structures, shared by many processes (both background and foreground). Just a few of them:

shared pool
buffer cache
log buffer

If you undersize, for example, buffer cache, the queries will perform slower. Buffer cache is the area of memory used to hold the blocks of data read from and written to data files. Whenever you query a table, data is read from the data files in the units called blocks. The requested data is not returned straight to the user process, instead it is copied to the buffer cache, and then is read from the buffers in the buffer cache if other queries request the same data. Undersized buffer cache leads to lots of I/O and thus adversely impacts performance.

PGA, on the other hand, is the area of memory private to one process. It cannot be shared shared by processes. It is used to maintain the process state. One of the parts of PGA is sort area. It is used to sort a set of rows. In the best case the sort is performed in memory, but if the PGA (sort area in particular) is undersized, the sorting involves the temporary tablespace segments residing on disk. This makes the queries perform slower because disk is one of the bottlenecks in the computer systems.

For more info read "Overview of the Program Global Area" and "Overview of the System Global Area" in Concepts.