A. Canonical Cover

The example is almost a canonical cover, since in a canonical cover each dependency has only an attribute on the right part, so the dependency

C → DF

must be replaced by the two dependencies:

C → D
C → F

B. Second step of the 3NF Synthesis Algorithm

The step says to divide the functional dependencies in groups with the same left part, and create a relation scheme with all the attributes of each group, not to create a relation for each functional dependency. In this case the effect is the same as your solution.

So the second step produces the following schemas:

R1 <(A H), { A → H }>
R2 <(A B D), { A D → B }>
R3 <(A B F), { A B → F }>
R4 <(B E), { B → E }>
R5 <(C D F), { C → D, C → F }>
R6 <(D E), { E → D }>
R7 <(C F H), { F H → C }>

Note that in this the decomposition the dependencies shown are only the original dependencies used to produce the decomposition, since in the algorithm the projection of the dependencies is not calculated, and this is due to the fact that this computation requires an exponential algorithm.

If, however, we look at the projection of the dependencies, here you are correct, since both A → D and F → B are not present in the projection. Here is the above decomposition together with a projection of the original dependencies over the subschema:

R1 <(A H), { A → H }>
R2 <(A B D), { A D → B, B → D }>
R3 <(A B F), { A B → F, A F → B }>
R4 <(B E), { B → E }>
R5 <(C D F), { C → D, C → F }>
R6 <(D E), { E → D }>
R7 <(C F H), { F H → C, C → F }>

C. Third step of the 3NF Synthesis Algorithm

Correct

D. Fourth step of the 3NF Synthesis Algorithm

Here again the step is incorrect. The fourth step simply says: “if no schema exists with a key of the original relation, than add a new schema with this key”. See for instance Algorithm 16.6, R.Elmasri, S.B. Navathe, Foundamentals of Database Systems, 6th Edition, pag. 561 (or any other good book on databases):

  If none of the relation schemas in D contains a key of R, then create one more relation schema in D that contains attributes that form a key of R.

And since in this case there exist relations with a key, no other relation must be added, and the final decomposition is not modified:

R1 <(A H)>
R2 <(A B D)>
R3 <(A B F)>
R4 <(B E)>
R5 <(C D F)>
R6 <(D E)>
R7 <(C F H)>

So the solution given, with R1', R2' and R3' is not correct and it is not in Third Normal Form. It is easy to show this. Taking for instance the second relation,

R2' (A B C F H)

and projecting the original dependencies over it, we found the following dependencies:

{ A F → B
  A B → C
  A → H
  C → F
  F H → C }

with keys:

{ (A B) (A C) (A F) }

So we can see that the dependency A → H violates the Third Normal Form (A is not a (super)key and H is not a prime attribute).

As shown in the answer, if the relations must still be in 3NF, the final solution with three schemas is not the minimal 3NF normalization, since the relation R2' is not in 3NF. In other words, the solution is incorrect.

The classical synthesis algorithm produces the decomposition in 7 relations. I am not aware of any published algorithm that will minimize the number of relations obtained by that algorithm. You could try to combine relations and check if the combination is still in 3NF, but note that checking if a relation is in 3NF is an exponential task, since one should find all the keys, to exclude the possibility that a determinate be a prime attribute.


PostgreSQL: Restoring 1 file from a directory format dump

I found the line in the toc.dat file that made the call and unpacked
  the .dat.gz file to find it only contains "\." (sans the quotes) in
  it.

\. is not a command, it marks the end of data in a COPY stream.
If the corresponding data file inside the dump contains only that, then the source table was empty.

Also note that you can pg_restore --list to list the contents of a dump in directory or compressed format.

You may also restore the contents of an individual table into an SQL file rather than in the target database, then modify that file as convenient, and play it into the target. See the pg_restore manpage for more.


Is there a way to reference a column dynamically within a trigger?

I was not able to solve this issue in the method I had wanted.  I used an if statement as suggested by Serg.

if ((:NEW.ONSALE = 'T') AND (:OLD.ONSALE = 'F')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT1;
elsif ((:NEW.ONSALE = 'T') and (:OLD.ONSALE = 'T')) then
    :NEW.PRICE := :OLD.PRICE - :OLD.PRICE * :NEW.DISCOUNT2;
end if;

This was a very simplified example but I couldn't figure out a better way to do this.


Multiple conditional statement checking SQL

Assuming no pattern can contain a space, the following may work. First, you need a splitting function that can handle a space (also a problem for some splitting techniques), and that returns the number of rows after the split.

CREATE OR ALTER FUNCTION dbo.SplitStrings
(
   @List       varchar(max),
   @Delimiter  varchar(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1
       FROM n WHERE n <= LEN(@List))
     SELECT [Value] = SUBSTRING(@List, n,
       CHARINDEX(@Delimiter, @List + @Delimiter, n) - n),
       Fragments = COUNT(*) OVER() FROM n
     WHERE n <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, n, 1) = @Delimiter
   );
GO

Now, you can pass your pattern into that function, and you get returned the set of patterns. We need to check that all patterns are found in a given string, which means the number of rows that match in the join must equal the number of patterns. This translates to something like this:

DECLARE @BaseTable TABLE(SearchColumn varchar(255));

INSERT @BaseTable(SearchColumn) VALUES
  ('|8=FIX.4.4|9=70|35=A|34=1|10=008|'),  -- will match
  ('|8=FIX.4.4|9=70|35=QA|34=1|10=008|'), -- will not match
  ('|8=FIX.4.4|9=70|35=A|34=71|10=008|'); -- will not match

DECLARE @pattern varchar(50) = '%|35=[A,D]|% %|34=[1]|%';

SELECT bt.SearchColumn
  FROM @BaseTable AS bt
  INNER JOIN dbo.SplitStrings(@pattern, ' ') AS ss
  ON bt.SearchColumn LIKE ss.[Value]
  GROUP BY bt.SearchColumn
  HAVING COUNT(bt.SearchColumn) = MAX(ss.Fragments);

If a pattern may contain spaces, you should simply use a different delimiter between your patterns, e.g. three tildes (~~~):

DECLARE @pattern varchar(50) = '%|35= [A, D]|%~~~%|34 = [1]|%';

...
  INNER JOIN dbo.SplitStrings(@pattern, '~~~') AS ss
...


How to Choose Between VALUES and SELECT for INSERT?

The measured difference is almost certainly noise. Run some more iterations, you won't get consistent result. The difference in performance (if any exists) won't be measurable.

You can use either method here. Both are equally good for the purpose.
There are often multiple ways in SQL. And sometimes there is no clear winner.

The more important issue concerning performance here:

  100k of such INSERTs done per cycle

For big bulk-inserts it would be faster to INSERT in both tables instead of firing a trigger for every row.

If you are using a serial PK that's generated automatically, you can employ the RETURNING clause in a data-modifying CTE

WITH ins1 AS (
   INSERT INTO measurement (measurement)
   VALUES ...   -- OR SELECT ... if data comes from inside the DB :)
   RETURNING measurement_id  -- generating a serial ID?
   )
INSERT INTO file_headers (measurement_id, file_header_index_start, file_header_index_end)
SELECT measurement_id,  1, 666  -- here it *must* be SELECT
FROM   ins1;

A numerical constant like 666 (no quotes, just digits)  defaults to type integer automatically.

May not be applicable, depending on your workflow.

Related:

Bulk insert in multiple tables


run
{
  set newname for database to 'C:\app\administrator\oradata\DB\%U';
  restore database;
}
switch database to copy;

This will generate filenames in the specified directory. If you want nice looking names following your naming convention, specify them manually, example:

run
{
  set newname for datafile 1 to 'C:\app\administrator\oradata\DB\system01.dbf';
  set newname for datafile 2 to 'C:\app\administrator\oradata\DB\sysaux01.dbf';
  ..
  restore database;
}
switch database to copy;

SET


One to many relation getting recent records plus one column from previous record

You can get the previous row value for a column using the LAG function and limit to the most recent row using ROW_NUMBER:

SELECT ProductId,
  RecentAmount,
  PreviousAmount,
  Status
FROM
(
SELECT
  t1.ProductId,
  t2.Amount AS RecentAmount,
  LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,
  t1.Status
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.ProductId = t2.ProductId
) t
WHERE Rn = 1

Your example data is a little confusing though. For PD_2, your expected results indicate that 1000 is the most recent amount and 900 the previous amount, however, the 1000 record has a lower Id value the 900 record. Typically, one would assume the higher Id value records were entered alter than the lower ones.

In any case, you can see a working example of the LAG function, combined with ROW_NUMBER to pick the single row here. This example uses specific ordering in the LAG (DESC) and ROW_NUMBER (ASC) functions to return your expected resultset.

If your example data is incorrect and the 1000 record should actually be a higher Id value, you can change the LAG and ROW_NUMBER functions from

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS Rn,

to

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,

as shown in this example.


"CREATE INDEX" Background process in SQL Server

If you are seeing the same index(es) being created over and over again, this is most likely being initiated by the application.  Some applications, particularly reporting or analysis applications, will drop indexes before importing data, and then create them again after the import is complete.

Another possibility is that Automatic index management is enabled.  However, you won't see this creating the same index over and over again, unless something is dropping an index that it creates.

Can you find that index--what table is it on?  I'm wondering now if it might be on a tempdb table that is created, populated, indexed, and dropped repeatedly.

If you can catch it happening with sp_whoisactive that should provide more clues.  Set @show_system_spids = 1.


Distribution statistics on VARCHAR / VARGRAPHIC columns?

It is "partially documented"/"not really documented" AFAIK. More specifically, there is APAR:

IT13369: SUB-OPTIMAL QUERY PERFORMANCE WHEN DISTRIBUTION STATS COLLECTED ON STRING COLUMN WITH A COMMON PREFIX LARGER THAN 32 BYTES

that mentions it. I assume what you actually care about are cardinality estimates for a query that has predicate on a column that has a common prefix in which case you might try the workaround from it (i.e. NOT collect distribution stats for that column)


In MS SQL how do I find out what tables have not been queried or updated in a long time?

The sys.objects table keeps track of the last time a table's structure was modified but the closest you are going to get for finding out the last time a table's data was updated or used is by querying sys.dm_db_index_usage_stats.  This keeps track of index updates (including heap's, or tables without an index).  The catch is, it only keeps track of this data since the last time you're server was restarted.

select db_name(database_id) dbname,
object_name(object_id,database_id) oname,
MAX(CASE WHEN last_user_update < last_system_update THEN last_system_update ELSE last_user_update END) as LastUpdated
from sys.dm_db_index_usage_stats
group by database_id,object_id
order by db_name(database_id),object_name(object_id,database_id)

This sample query was borrowed from the following link:
http://www.sqlservercentral.com/Forums/Topic852747-146-1.aspx#bm852757


SQL Server Configuration Manager is not showing SQL Services

SQLExpress is installed as a named instance. Try connecting to  <server name>\SQLEXPRESS

Or localhost\SQLEXPRESS will work as well.


Unattended SQL install on a remote machine

To perform unattended install to a remote machine you should consider providing a separate installation option that allows just the database engine to be installed on a machine, and forget about doing it remotely.  Remote installation will be very difficult to troubleshoot and is not transparent for the installing individual.

Having said that you could look at using SysInternals PSexec.exe to remotely spawn the unattended install.


Why does Oracle APEX Shared_Servers = 0 Break Apex 3.2

Configuring the SHARED_SERVERS Parameter

  The embedded PL/SQL gateway uses the shared server architecture of the
  Oracle Database. To achieve acceptable performance when using the
  embedded PL/SQL gateway, ensure the  SHARED_SERVERS database
  initialization parameter is set to a reasonable value (that is, not 0
  or 1). For a small group of concurrent users, Oracle recommends a
  value of 5 for SHARED_SERVERS.

It is a requirement for EPG (which is suitable only for a development environment). Better use OHS (or a J2EE web server for newer APEX versions).


How to import DB with .sql extension into SQL Server 2012?

From BOL:

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

You're creating a database on your server, regardless of the fact that it already existed on another server, therefore you need CREATE DATABASE permissions.


Why would you use a managed service account rather than a virtual account in SQL Server 2012?

Here's the way I see it.

When you use a VA, you impersonate the machine account.

The problem is, that it is easy to make a VA or use an existing one (ex. NT Authority\NETWORKSERVICE). If you grant the machine account access to an instance, an application that is running as a VA will be able to connect to that instance and perform actions.

With a managed account, you will have to provide the credentials for that account to whatever application wants to use them, allowing you more granularity with permissions.


Your design has a big disadvantage: Getting just the current values (which you will work with most of the time) is an expensive operation that requires a join.

Your system would be much faster if you have a table with the current data and an extra table for historical information, consisting of all columns of users and a valid_until timestamp column, with a primary key over (user_id,valid_until).


The value for the parameter @MirrorDirectory is not supported error with Ola Hallengren Backup script

The backup procedure does a number of validation checks against the path to ensure it is valid. One of them checks the edition of SQL Server to ensure it can perform mirrored backups.

Run the below code and if it does not return 3 then you don't have Enterprise Edition and cannot use Mirrored Backups:

SELECT SERVERPROPERTY('EngineEdition')

See: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/mirrored-backup-media-sets-sql-server?view=sql-server-2017


mongorestore - documents fail validation

If only needing to validate, the best combination would be to add --dryRun --objcheck --verbose or --dryRun --objcheck -vvvvv to your mongorestore command.

When running the command for real, drop the --dryRun.


Column A or Column B required MS Access DB

I think of a scenario, where you can have a MasterUser table that will asign ID to both Customer and Prospect, auto incremented PKId can be named as UserID of the table.

let Say you create a table MasterUser and stored common details like names etc. that both Customer and Prospect have, now the unique info can be better moved to table Customer and ProspectiveCustomer.

Now as you have uniqueId for each user, no matter the user is Prospect or Customer.

So you Enquiry table will be something like

Enquiry
-----------------------------------------------
 EnquiryID  | UserID| Notes |
       3    |    2  |   x   |
       4    |    3  |   y   |


Go to SQL Server Configuration Manager

Start->all program->SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager

Explore "SQL Server Network Configuration" and then select "Protocols For MSSQLSERVER".

Check your TCP/IP Protocols in right side pane. If it is disabled then enabled it and Restart the "Sql Server(MSSQLSERVER) service" that available in  "SQL Server 2008 R2 services pane".

You can follow the same steps to make sure for other protocols and services.


Let's order things by complexity and performance.

The cropped image will be displayed many times.  And cropping is a rather complex process.  So, store the cropped image as a file.  Note:  By storing as a file, you can simply use an <img src=...> tag in HTML to fetch it -- no extra processing (for you) when providing the web page.
Given that you are doing #1, the rest becomes somewhat moot.  Yes, you could store the original image somewhere, plus meta info about cropping.  But will you ever change the cropping?  Or maybe the only real use case is when the user resends the image and you redo the cropping?  My point is, you business model now takes over and answers what to do for #2; not database considerations.


Can I copy the table structure of a #temp table to a new physical table?

Why not use the same approach you used to create the #temp table?

SELECT * INTO dbo.NewTable FROM #tempTable;


Secure method beside phpMyAdmin for remote mysql

Use a VPN connection to connect to the network which is hosting the database server.

A database server should never be directly accessible from the public Internet.  If it is directly accessible it will be broken in to. It's just a mater of time.


Having trouble designing my preferred backup scheme

First of all grab Ola Hallengren's scripts to do the backups (http://ola.hallengren.com) and drop the maintainance plans. Ola's scripts have much better retention settings and will allow you to set a mark in hours how long to store the backups and know how to take the backups of the secondaries.

And for your questions:

Set up a small script in SQL Server agent or Windows task scheduler that copies a backup file to a secondary folder where you keep it longer than the others or do a mirrored backup to a secondary folder every six months. A small powershell script or xcopy command that runs every six months and copies the last backup file will do just fine
Backup log DatabaseName to disk ='NUL' will make a log  backup to the windows equivalent of /dev/null, no disk writes or space wasted. Nogood to restore from though.


I found this just before posting.  The difference is the linux environment setting for NLS_LANG.  It was set to American_America.UTF8 on the system it added padding to.  Unsetting this on one and setting this on the other swapped where the behavior.


This should work:

WITH cte(id, controller_id, RTC, signal_plan, rw) AS
(
     SELECT
         id,
         controller_id,
         RTC,
         signal_plan,
         ROW_NUMBER() OVER (PARTITION BY signal_plan ORDER BY id) rw
     FROM your_table)

     SELECT
         id,
         controller_id,
         RTC,
         signal_plan
     FROM cte
     WHERE rw = 1;


how to know how many instances of an application are running on server

Use pstree -p do display process tree with pids of individual nodes. And then use the particular pid you want, and grep it from ps aux to see the CPU and memory consumption.

So the overall process will be:

$ pstree -p

init(1)─┬─apache2(6532)─┬─apache2(6575)
        │               ├─apache2(6576)
        │               ├─apache2(6716)
        │               ├─apache2(6803)
        │               └─apache2(6860)
        ├─cron(7474)
        ├─events/0(4)
        ├─getty(7507)
        ├─khelper(5)
        ├─klogd(7243)
        ├─ksoftirqd/0(3)
        ├─kthread(6)─┬─aio/0(64)
        │            ├─kblockd/0(19)
        │            ├─kjournald(693)
        │            ├─kjournald(7081)
        │            ├─kjournald(7082)
        │            ├─kjournald(7083)
        │            ├─kjournald(7084)
        │            ├─kjournald(7085)
        │            ├─kseriod(21)
        │            ├─kswapd0(63)
        │            ├─pdflush(61)
        │            ├─pdflush(62)
        │            ├─xenbus(8)
        │            └─xenwatch(7)
        ├─master(7414)─┬─pickup(6847)
        │              └─qmgr(7416)
        ├─migration/0(2)
        ├─mysqld_safe(7260)─┬─logger(7281)
        │                   └─mysqld(7280)───mysqld(7282)─┬─mysqld(7283)
        │                                                 ├─mysqld(7284)
        │                                                 ├─mysqld(7285)
        │                                                 ├─mysqld(7286)
        │                                                 ├─mysqld(7287)
        │                                                 ├─mysqld(7288)
        │                                                 ├─mysqld(7289)
        │                                                 ├─mysqld(7290)
        │                                                 └─mysqld(7291)

And then say you want to see the mysqld instance of pid 7285 's memory and cpu usage details:

$ ps aux | grep 7285
mysql     7285  0.0  2.9 58720 6016 ?        S    Feb04   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/blah.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
metin     6935  0.0  0.3  3300  724 ttyp0    R+   11:57   0:00 grep 7285

The third and fourh columns are CPU and memory respectively.


If you want the query to return just 1 row, a zone with a random proxy (chosen from the associated ones), then it's easy to fix the query:

SELECT z.*, ps.*
FROM Zones AS z
  LEFT JOIN ProxyServers AS ps
  ON ps.ProxyType = z.ProxyType
-- WHERE <some condition to choose from some zones or just one>
ORDER BY RAND()
LIMIT 1 ;

or even better:

SELECT z.*, ps.*
FROM
    ( SELECT *
      FROM Zones
      ORDER BY ZoneID DESC
      LIMIT 1
    ) AS z
  LEFT JOIN ProxyServers AS ps
  ON ps.ProxyType = z.ProxyType
ORDER BY RAND()
LIMIT 1 ;

If you want multiple or all the zones, each one with a randomly chosen proxy server, it's really not trivial to solve, due to the way that MySQL executes subqueries (in ON and WHERE clauses).

In short, the subqueries (the ON condition in your case) is executed for each and every combination of z and ps. Sometimes the randomly chosen ProxyID matches the ps.ProxyID, sometimes it doesn't. Therefore, for each zone, you may get 0 matches, 1, 2 or more, which is exactly what you see in the results.
See previous answers in similar issues:

How can this return more than 1 result?
Problem with MySQL subquery

Writing a query that behaves as expected - and return 1 row for each zone - it really hard because the values of RAND() are not found previously stored in a table but provided during run time.

A work around would be to abuse two other non-standard MySQL "features". First, that subqueries allow ORDER BY without LIMIT, which is normally redundant. Second, that in GROUP BY queries, it is allowed to have expressions in the SELECT list which are not aggregates and are not in the GROUP BY list. This is also against the standard and often leads to unexpected results for poorly written queries.

Combining these two ill-behaving features, we can have a monster:

SELECT g.*
FROM
  ( SELECT z.*,
           ps.ProxyID, ps.ProxyIP, zs.ProxyPort, ps.Enabled
    FROM Zones AS z
      LEFT JOIN ProxyServers AS ps
      ON ps.ProxyType = z.ProxyType
    ORDER BY z.ZoneID, RAND()
  ) AS g
GROUP BY ZoneID ;

It's certainly not guaranteed to work, as recent or future versions of MySQL may or do detect the ill behaviour and may either remove the redundant ORDER BY killing the wanted functionality or may throw an error due to the non-standard GROUP BY clause.


Hundreds of sessions queued in suspended state

It was tempdb contention. Adding more tempdb data files and enabling trace flag 1118 solved the issue.


Composite constraint of numrange and boolean

You can add a WHERE clause to make it a partial EXCLUSION constraint (creating a partial index to implement it):

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint
  EXCLUDE USING gist ((uuidcol::text) WITH =, numrange(start, end) WITH &&)
  WHERE (active);

The parentheses are not optional. The manual:

  The predicate allows you to specify an exclusion constraint on a
  subset of the table; internally this creates a partial index. Note
  that parentheses are required around the predicate.

BTW, in Postgres 10 or later you can install the additional module btree_gist to add (among others) the necessary operator class for type uuid in GiST indexes. So you can simplify:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint
  EXCLUDE USING gist (uuidcol WITH =, numrange(start, end) WITH &&)
  WHERE (active);

Considerably more efficient than casting to text. Related:

What is the optimal data type for an MD5 field?


Clever way to order a json item by key in postgresql (two examples)

Be aware that order of array items is a different issue than order of keys in JSON object. See:

How to sort objects in an array inside a json or jsonb value by a property of the objects?

For your first example (implying integer sort), simply casting to jsonb would do the job. It happens to sort keys the way you want by default:
SELECT jsonitem::jsonb FROM jsontable;

db<>fiddle here
Or use jsonb instead of json to begin with.
However, it's not strictly integer sort. Keys are strings. Shorter keys sort before longer keys, which works as desired with only digits. But not necessarily with anything else. Like negative numbers:
2, 3, -1

Then again, your solution does string sort according to the default collation, i.e. breaks integer sort for numbers not comprised of the same number of digits:
111, 22, 3

You didn't specify exactly. This would do integer sort properly:
select id, json_object_agg(key, value) AS jsonitem
from  (
   select id, key, value
   from   tbl
   ORDER  BY id, key::int  -- !!!
   ) tbl
group  by id;

Of course, the cast to integer raises an exception for any string that's not a legal integer representation.
Sorting in a subquery once as this is typically much faster than the per-item sort you get from an added ORDER BY in an aggregate function. This works, because, quoting the manual:

Alternatively, supplying the input values from a sorted subquery will usually work.

See:

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?
Customize jsonb key sort order involving arrays

Your second example is a different beast, trying to sort an array with JSON objects by the (single!?) nested key. You could reorder arrays like this:
SELECT j.id, arr.js_sorted
FROM   jsonarr j
CROSS  JOIN LATERAL (
   SELECT json_agg(value) AS js_sorted
   FROM (
      SELECT *
      FROM   json_array_elements(j.jsonitem)
      ORDER  BY (value->>'key')::int  -- true integer sort!
      ) sub
   ) arr;

It really depends on where your values come from, what they can be exactly, and how you want to sort exactly.


SQL Server: Case expression returns NULL even with an ELSE

I would try adding some ISNULL functions to the left sides of the Case checks (h.encounter_date and h2.encounter_date), as I don't see a WHERE h.encounter_date/h2.encounter_date IS NOT NULL in your WHERE clause.


Note: This is for Microsoft SQL Server.

For testing purpose, I downloaded a corrupt database backup file from SQLSkills.com

Below is a quick script that will help you (obviously you can adapt the logic to do it dynamically for all your databases and send email in a nice HTML format) :

begin try
    BACKUP DATABASE broken TO DISK='D:\broken2005\broken2.bck'
    WITH CHECKSUM  --- very Important
end try
begin catch

    declare @errorSubject char(100)
    declare @errorBody varchar(max)

    set @errorSubject = 'Full backup failed for database Broken on ' +@@servername
    set @errorBody = 'Full backup failed '+ char(10)+Cast(Error_Number() as nvarchar)+' - ['+Error_Message()+']'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'databaseMail Profile',
    @recipients ='DBATeamEmailAddress@somecompany.com',
    @body = @errorBody,
    @subject = @errorSubject

end catch

Alternatively, you can use SQL Server Backup solution from Ola Hallengren.


T-SQL Calculate average value for Duration Field

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle?

Don't be tempted to change this from nvarchar to time. Again, time is a point in time, not an interval. You should be storing start and end values as datetime; you can always calculate duration from that (typically I see duration as an additional nonsense column that doesn't need to be stored in the first place).

The formatting as hh:mm:ss should not happen at storage time, only at presentation time. And no idea why you would ever use nvarchar for this in the first place - what Unicode characters do you expect to support in hh:mm:ss?

In the meantime:

SELECT CONVERT(TIME(0), DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(DATETIME,
  CASE WHEN ISDATE(Duration) = 1 THEN Duration END))),0)) -- in case of garbage data
FROM dbo.your_table_name;

See how messy that is? Wouldn't it be much easier as:

SELECT AVG(Duration) FROM dbo.your_table_name;

Or:

SELECT AVG(DATEDIFF(SECOND, [start], [end])) FROM dbo.your_table_name;

And then format as hh:mm:ss in the client language? Yes, I think so.


Extracting string after and before a character '-' or '<' using Sql Query

The LEFT and RIGHT functions are helpful for this kind of parsing. They only return X number of characters from that side of a VARCHAR like so:
SELECT RTRIM(LEFT(AccountMasterUDF.UDF_WorkLocation_6, CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) - 1)) AS Column1,
LEFT(LTRIM(RIGHT(AccountMasterUDF.UDF_WorkLocation_6, LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6))), CHARINDEX('<',LTRIM(RIGHT(AccountMasterUDF.UDF_WorkLocation_6, LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)))) - 1) AS Column2

As you can see, combined with CHARINDEX to locate the - in both cases, and LTRIM and RTRIM to remove any unneeded whitespace.
In the second case you need to apply the LEFT function on top of your output from the RIGHT function to do one more search and removal of the ending .
Also here's a more formatted version of the stacked functions above that I find more readable if you prefer:
SELECT
    RTRIM
    (
        LEFT
        (
            AccountMasterUDF.UDF_WorkLocation_6,
            CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) - 1
        )
    ) AS Column1,
    LEFT
    (
        LTRIM
        (
            RIGHT
            (
                AccountMasterUDF.UDF_WorkLocation_6,
                LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)
            )
        ),
        CHARINDEX
        (
            '<',
            LTRIM
            (
                RIGHT
                (
                    AccountMasterUDF.UDF_WorkLocation_6,
                    LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)
                )
            )
        ) - 1
    ) AS Column2


/dev/shm size recommendation for postgres database in docker

There are two things that use shared memory:

The shared memory that is allocated at server start. This consists of several parts, but the lion's share is shared_buffers, the data cache.
So you have to have more shared memory than that.

Shared memory segments allocated by parallel query to communicate between parallel workers.
Their size depends on the data transferred, and several of them can be allocated, so that is a resource that is harder to control.

On a memory-constrained system, I'd recommend that you disable parallel query. It makes queries faster, but uses more resources per query, so overall throughput doesn't improve. It is good if you want to throw a lot of resources on a single query.
To disable parallel query, set max_parallel_workers to 0.


Wrong query results on column ORA_ROWSCN with bind variables and NVL (Oracle 12c)

The plan clearly shows the OR-expansion query transformation. It is not the first time it produced wrong results due to various bugs.

Try running your query as:

SELECT /*+ NO_EXPAND */
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

Or set the below parameters before executing it:

alter session set "_no_or_expansion"=true;
alter session set "_or_expand_nvl_predicate"=false;

Then run your query.


ssis ado.net destination has failed to acquire the connection

configure your connection as xml
set variable in connection
set computer name & logic [Admin]


How to optimize this crazy SQL Query (Specific)

What do you want to achieve?  How are the two tables linked?

Perhaps a LEFT JOIN + USING(RaagID) or something like that is better?

Also, it might help to know what Shabad and Raag mean in English.

Update from comments:
shabad is the main table and has references to the other two, so I believe the answer is

SELECT *
FROM shabad s
LEFT JOIN raag r using (raagid)
LEFT JOIN writer w using (writerid)

then filter that with where-clause and your criteria


How to get the registers in the highest time of period

You are doing a left join that then includes a where clause that is filtering against the b table in the where clause.  I'm guessing you want to say OR b.id IS NULL or something to that affect.
To confirm, comment out the where clause.


EXECUTE AS USER = 'SomeUser'; where 'SomeUser' is a user who doesn't have a linked login at the server level will result in SQL Server reporting the login name as a SID starting with s-1-9-3.

You can use the original_login_name column in sys.dm_exec_sessions to determine who or what is impersonating the user without a login; for instance:

SELECT SessionID = des.session_id
    , LoginName = des.login_name
    , OriginalLoginName = des.original_login_name
FROM sys.dm_exec_sessions des
WHERE des.is_user_process = 1;

I wrote a blog-post over at SQL Server Science showing this behavior, and how to identify which database-level-principal is being impersonated.


Why does SQL Server Profiler crash when its run against a DB machine from a client machine?

The profiler.exe will run on the local Windows 7 machine, where you have the Management Studio installed. It will connect to remote instance over network to collect the data, but the profiler.exe itself will run locally on your computer. If you want the collection to happen at the same server where the SQL Server is installed, then you need to implement server-side tracing for that.

A good example of how to set it up, can be found here.

Also by starting Profiler the way you described should first prompt for server details (server name, credentials) even if you have server connection open in the Object Explorer. Now I believe it crashed before ever getting there, so the problem is probably not related to connecting to remote server at all. I would recommend re-installing Management Studio to see if it fixes the issue.

Latest stand-alone installers for SSMS can be found from here.


How to Convert Data to Proper Case In SQL Server

The challenge you'll run into with these approaches is that you've lost information. Explain it to the business users that they've taken a blurry, out of focus picture and despite what they see on t.v. there's no way to make it crisp and in focus. There's always going to be situations where these rules won't work and as long as everyone knows going into this that's the case, then have at it.

This is HR data so I'm going to assume we're talking about getting names in a consistent title case format because the mainframe stores it as AARON BERTRAND and we want the new system to not yell at them. Aaron is easy (but not cheap). You and Max have already identified the problem with the Mc/Mac so it correctly capitalizes Mc/Mac but there are instances where it's too aggressive with Mackey/Maclin/Mackenzie. Mackenzie is an interesting case though - look how the popularity of it has boomed as a baby name

At some point, there will be a poor child named Mackenzie MacKenzie because people are awful beings.

You're also going to run into lovely things like D'Antoni where we should cap both letters around the tick mark. Except for d'Autremont where you only capitalize the letter after the apostrophe. Heaven help you though, if you send mail to d'Illoni as their family name is D'illoni.

For the sake of contributing actual code, the following is a CLR method we used in a 2005 instance for our purposes. It generally used ToTitleCase except for the list of exceptions we built out which is when we basically gave up trying to codify the aforementioned exceptions.

namespace Common.Util
{
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading;

    /// <summary>
    /// A class that attempts to proper case a word, taking into
    /// consideration some outliers.
    /// </summary>
    public class ProperCase
    {
        /// <summary>
        /// Convert a string into its propercased equivalent.  General case
        /// it will capitalize the first letter of each word.  Handled special
        /// cases include names with apostrophes (O'Shea), and Scottish/Irish
        /// surnames MacInnes, McDonalds.  Will fail for Macbeth, Macaroni, etc
        /// </summary>
        /// <param name="inputText">The data to be recased into initial caps</param>
        /// <returns>The input text resampled as proper cased</returns>
        public static string Case(string inputText)
        {
            CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
            TextInfo textInfo = cultureInfo.TextInfo;
            string output = null;
            int staticHack = 0;

            Regex expression = null;
            string matchPattern = string.Empty;

            // Should think about maybe matching the first non blank character
            matchPattern = @"
                (?<Apostrophe>'.\B)| # Match things like O'Shea so apostrophe plus one.  Think about white space between ' and next letter.  TODO:  Correct it's from becoming It'S, can't -> CaN'T
                \bMac(?<Mac>.) | # MacInnes, MacGyver, etc.  Will fail for Macbeth
                \bMc(?<Mc>.) # McDonalds
                ";
            expression = new Regex(matchPattern, RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);

            // Handle our funky rules
            // Using named matches is probably overkill as the
            // same rule applies to all but for future growth, I'm
            // defining it as such.
            // Quirky behaviour---for 2005, the compiler will
            // make this into a static method which is verboten for
            // safe assemblies.
            MatchEvaluator upperCase = delegate(Match match)
            {
                // Based on advice from Chris Hedgate's blog
                // I need to reference a local variable to prevent
                // this from being turned into static
                staticHack = matchPattern.Length;

                if (!string.IsNullOrEmpty(match.Groups["Apostrophe"].Value))
                {
                    return match.Groups["Apostrophe"].Value.ToUpper();
                }

                if (!string.IsNullOrEmpty(match.Groups["Mac"].Value))
                {
                    return string.Format("Mac{0}", match.Groups["Mac"].Value.ToUpper());
                }

                if (!string.IsNullOrEmpty(match.Groups["Mc"].Value))
                {
                    return string.Format("Mc{0}", match.Groups["Mc"].Value.ToUpper());
                }

                return match.Value;
            };

            MatchEvaluator evaluator = new MatchEvaluator(upperCase);

            if (inputText != null)
            {
                // Generally, title casing converts the first character
                // of a word to uppercase and the rest of the characters
                // to lowercase. However, a word that is entirely uppercase,
                // such as an acronym, is not converted.
                // http://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase(VS.80).aspx
                string temporary = string.Empty;
                temporary = textInfo.ToTitleCase(inputText.ToString().ToLower());
                output = expression.Replace(temporary, evaluator);
            }
            else
            {
                output = string.Empty;
            }

            return output;
        }
    }
}

Now that all of that is clear, I'm going to finish this lovely book of poetry by e e cummings


Will OPTIMIZE TABLE have any impact on my data?

OPTIMIZE TABLE basically does three(3) things

Shrinks the data pages
Shrinks index pages
Computes Fresh Index Statistics

Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable
USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp RENAME mytable;
DROP TABLE mytablezap;
ANALYZE TABLE mytable;

However, based on

the table mydb.mytable
the datadir being /var/lib/mysql

let's look at Storage Engine Specifics
MyISAM
The MyISAM table mydb.mytable is physically stored in three files

/var/lib/mysql/mydb/mytable.frm (table structure)
/var/lib/mysql/mydb/mytable.MYD (data)
/var/lib/mysql/mydb/mytable.MYI (indexes)

The conceptual description of running OPTIMIZE TABLE would copy data pages and index pages into a new .MYD and .MYI. This will eliminate having fragmented pages in either file.
InnoDB
There are two viewpoints to take into consideration
Viewpoint #1 : innodb_file_per_table disabled
With innodb_file_per_table disabled, all data pages and index pages for every InnoDB table are stored inside the system tablespace (better known as the file ibdata1).
When you run OPTIMIZE TABLE on an InnoDB table that is stored in ibdata1, all the data and index pages are written contiguously so all the pages for the table are together. The bad news is that it makes ibdata1 grow rapidly.
Viewpoint #2 : innodb_file_per_table enabled
With innodb_file_per_table enabled, all data pages and index pages for every InnoDB table are stored outside ibdata1. Here is the physical storage of mydb.mytable:

/var/lib/mysql/mydb/mytable.frm (table structure)
/var/lib/mysql/mydb/mytable.ibd (data and indexes)

When you run OPTIMIZE TABLE on an InnoDB table that is stored outside of ibdata1 (system tablespace), this perform the conceptual steps that lead to shrinking the .ibd file.
I have written about this before

Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (StackOverflow)
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Dec 21, 2012 : Is there a progress indicator for OPTIMIZE TABLE progress?
Jan 07, 2013 : Database space doesn't match ibdata1 size

Now, for your original questions...

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

I just explained how OPTIMIZE TABLE works for both storage engines. This can be time-consuming depending on the size of the data and indexes. Outside of an Earthquake or Power Outage, running OPTIMIZE TABLE does not harm to the data and indexes get rebuilt.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE?

Ditto

Will indexes and primary keys still remain the same?

Yes

Are there areas of the database that will be slower after the optimization?

No way. Querying a table with no fragmentation can only be faster


SQL Server backup versus third party snapshot based backup using VSS

SQL Server uses checkpoint, log sequence numbers, and the transaction log to guarantee that a full backup is restored to a transactionally-consistent state, as of a particular point in time.

The exact point in time for a full database backup is the end of the data-reading portion of the backup process. To recover to an arbitrary point in time requires separate transaction log backups as well.

Online backup means the database remains accessible (and writeable) for other users while the backup is in progress. Enough of the transaction log is saved with the backed up database pages to enable recovery to bring the whole database to a transactionally consistent state when restored.

The basic idea is that pages saved to the backup are already consistent to the target point in time, or can be made so by undoing or redoing transactions from the included portion of the transaction log.

For full details see the TechNet article Understanding SQL Server Backups by Paul S. Randal.


How to improve INSERT speed in large table that has unique constraint

I think the problem could be your RAM, for LOAD INDEX INTO CACHE to make a difference you should have the index size < key_buffer_size < Available RAM

To see your current index size use SHOW TABLE STATUS.

Also LOAD INDEX INTO CACHE works only for MyISAM, for InnoDB you could try the blackhole method:

create table t like innodbtable;
alter table t engine = blackhole;
insert into t select * from innodbtable WHERE indexedRow<>''; -- Get creative in order to stimulate reading of data and indexes you need

source , for more information on InnoDB pre-loading see this and this.

To estimate index usage see this or this .

For example I have a table with 32 Mil records in it and the data size is 2.33 GB (2,496,806,620) and the index size is 2.08 GB (2,230,159,360). So your table should be at least 5 times smaller to be able to fit the RAM limit. Also don't forget I said Available RAM = the ram left after excluding system, mysql (other than the key_buffer_size) & other software, so that would leave you at best with 350MB.

Here is my table for reference:

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a_id` int(10) unsigned DEFAULT NULL,
  `b_id` int(10) unsigned DEFAULT NULL,
  `varchar1` varchar(255) DEFAULT NULL,
  `varchar2` varchar(255) DEFAULT NULL,
  `varchar3` varchar(8) DEFAULT NULL,
  `lastModified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `links_id_email` (`a_id`,`varchar1`),
  KEY `domain_index` (`varchar2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Connect to SQL Server using OLEDB or ODBC?

For most applications hitting SQL Server 2008 R2, you would be fine going either way, I think.

OLEDB support is being removed after SQL Server 2012 but until it is, you'd likely be fine either way based on your standards and your needs. That said, I'd look to future supportability and consider going ODBC with the Native Client.

I would say a more important decisions would be to consider using the Native Client for your .net apps if you aren't already.


In what file does SQL Server Store the configuration data?

Basically there is not a single place where ever configuration can be found.

The more generally used configurations can be found here Server Configuration Options. But they do not necessarily cover everything that you might want to configure.

For example:  Enable SSL Encryption for SQL Server using Microsoft Management Console

Also you can find some information on size limits and allocations at: Features Supported by the Editions of SQL Server 2014

Perhaps these examples will get you started.


DBCC CheckDB : Operating System Error 27(The drive cannot find the sector requested)

Turns out the problem had to do with the SAN returning invalid sectors during disk IO events. What had happened was that the disk had had 400Gb of unusable space removed, and something went sideways in the disk definition in the SAN. The solution was to shrink the disk by 10Gb then grow back that 10Gb to reestablish the disk definition in the SAN. Once that was complete, suddenly everything was fine. Not at all what I expected but I wanted to show what the solution was, because I think it's important that "rock solid, never has an error, why are you even looking at that" systems need to be included in a full error analysis.


VARCHAR automatically changes to VARBINARY

I'm guessing you have binary as your default character set for the server and/or for the database. You probably want to change this to prevent the same thing happening next time you create a table.

You can check by running: SHOW VARIABLES LIKE 'character_set_server'; and SHOW CREATE DATABASE the_database_name;

If the database doesn't have a character set, then the server character set will be used.

You can change the database character set with a command like:

ALTER DATABASE the_database_name CHARACTER SET = 'utf8';

The server character set can be set with a command like:

SET character_set_server = 'utf8';

(Use SET GLOBAL ... for the setting to be made outside of the current session. Or you can set this in your config file so it survives a restart.)


Fastest way to copy data from MyISAM to InnoDB

There are a few solutions. First, however, I'm not sure about the process in the first place. Is this a one time copy operation? A recurring copy operation? Do you want to migrate from MyISAM to InnoDB?

What is the main reason for your desire for a quick operation?

If you're looking for migration, then why don't you use an online table alter tool, such as oak-online-alter-table (disclaimer: I'm author of this tool) or pt-online-schema-change? Both will allow you to change your schema live and online with very little disturbance.

If you're looking to a copy+paste of your data, then I would suggest using chunking: copying the data in small packets. This way you don't get that huge lock and no funny timeouts. You can use either oak-chunk-update or pt-archiver for this. This may actually make the total runtime shorter because of reduces locking, but may also take longer. Also consider that it is not an atomic operation, and changes to original table while copying is made, may not get caught, so you may get an inconsistent copy.

Otherwise (or in addition) you can use all the usual tweaks, such as

SET GLOBAL innodb_flush_log_at_trx_commit := 2;

or set

[mysqld]
innodb_doublewrite = 0

or perhaps, depending on OS and disks,

[mysqld]
innodb_flush_method = O_DIRECT

Each of the above may reduce disk I/O access. First two will also make your server less crash safe. But if for limited time, this may be OK for you.


This depends on table engine?

Yup. MyISAM will lock the table, InnoDB will lock rows

If you're looking for ACIDity, you'll want to be using InnoDB. How InnoDB handles locking is described in this doc page

The following user comments on the above document page illustrate a flaw in InnoDB performance on large tables:

  Major gotcha: Rows are locked during updates using indexes. For example, UPDATE tab SET col1=3 WHERE col2=17; will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked).

and

  To expand on the above comment, any operation that does a table scan for update/delete will lock all the rows in the table

Extra curricular reading:

  TL;DR: InnoDB index lock is major architectural performance flaw, and that is why you hear that large tables are slower. There’s a big chance that there’re more scalable engines for on-disk writes out there, and all the large InnoDB write/insert benchmarks were severely hit by this.


Why does batch mode window aggregate yield arithmetic overflow?

Multiple commenters have been able to reproduce this issue. We initially thought that SQL Server 2017 CU10 resolved the issue, but then found that the error can be reproduced in all versions of SQL Server we tried, including CU10. However, some commenters observed an element of chance where the same script did not always trigger the error.

Because there is no logical way that computing a sum across a set of non-negative numbers whose maximum possible sum is 1,500 can overflow a 32 bit integer, we believe this is a bug in the batch mode window aggregate operator. Being a new operator in SQL Server 2016, it's reasonable to assume that there might still be some edge cases to iron out.

Here is the bug report we filed with Microsoft.

The response was:

  This is fixed in SQL Server 2019 CTP 2.1 and will also be fixed soon in Azure SQL Database.


SELECT tableA.genre,
       COUNT(tableB.genre) hit_movies,
       SUM(CASE WHEN tableB.genre IS NULL
                THEN 0
                ELSE tableA.collection
                END) total_collection
FROM tableA
LEFT JOIN tableB ON tableA.genre = tableB.genre AND tableB.performance = 'Hit'
GROUP BY tableA.genre

fiddle

Option proposed by a_horse_with_no_name (PostgreSQL-specific):

SELECT tableA.genre,
       COUNT(tableB.genre) hit_movies,
       SUM(tableA.collection) FILTER (WHERE tableB.genre IS NOT NULL)  total_collection
FROM tableA
LEFT JOIN tableB ON tableA.genre = tableB.genre AND tableB.performance = 'Hit'
GROUP BY tableA.genre

The difference - first query outputs zeroes for the genres without hits whereas second one outputs NULLs. May be changed by replacing zero with NULL (or by replace the whole CASE expression with CASE WHEN tableB.genre IS NOT NULL THEN tableA.collection END) in the first query or by wrapping the expression with COALESCE() in the second query.


Scramble the data output of a stored procedure

Assuming you want randomish ordering of your output you can just ORDER BY NEWID().  That would jumble up the ordering of the results reasonably well.  If you only need to randomize after a certain order is established then you can still use the NEWID() trick.  Below are some quick examples:

-- Fully random ordering
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    NEWID() ;

-- Random ordering of results but the results still preserve the order of UserId
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    F.UserId
    , NEWID() ;

Another way, which will not produce all possible orderings but only a limited set but can be more efficient with large tables because it doesn't use NEWID(), only a single value for @aRandomNumber which should be drawn from the range of the column that is compared against (F.UserId here) :

WITH cte AS
  ( SELECT *, 0 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId >= @aRandomNumber
    ORDER BY F.UserId
  UNION ALL
    SELECT *, 1 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId < @aRandomNumber
    ORDER BY F.UserId
  )
SELECT <list of columns except the "ord">
FROM cte
ORDER BY ord, UserId ;


MySQL Import CSV with Multiple Field Separators

Since you are not allowed to script but SQL try the following:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\"');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

There you go, another text file that has the doubled double quotes stripped.

If you want all doubled double quotes replaced with single quotes, do this UPDATE:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\'');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

Now, go import that file (improved_importfile.txt) and have fun with it.

Give it a Try !!!


PostgreSQL: ... LIMIT 1 much slower than fetching all rows?

The problem is that rows for 'foo@example.com' are deficient in values with an early date_created, but the planner doesn't know that.  It thinks they are evenly scattered, so it will find one early on when walking modwork_ticket_date_created and will be able to stop then.  But instead it has to walk through 1,705,251 of them.

You can create an index on (email_sender_id text_pattern_ops, date_created) and it will be able to jump directly to the tuple you want.  That should be much faster than either existing plan.   (I proposed the including text_pattern_ops purely because the name of the existing index has the word "like" in it, so I assume it was already defined that way--but maybe you actually have a column named "like".  It would be better if you show us the definitions so we don't have to guess about things such as this).  This index may be able to replace an existing one, so the overall number of indexes might stay the same.

If you don't want to create the index and just want it to the use the 2nd plan, you can force it by writing the query like this:

SELECT * FROM modwork_ticket WHERE
 modwork_ticket.email_sender_id = 'foo@example.com'
 ORDER BY modwork_ticket.date_created + '0 days' ASC limit 1;

By adding something to the date before you sort, it tricks PostgreSQL into thinking it can't use the date index.

Once v10 of PostgreSQL is released and you upgrade to it, you can create an advanced statistics definition for the functional dependency between the two columns, which might also be enough to get it to choose the better plan.


What would I use a MATCH SIMPLE foreign key for?

My preference is to avoid nulls in foreign key columns. Make all foreign keys non-nullable. In fact I have foreign key = not null as a standard check in some static analysis scripts I use for validating database designs.

The FULL / SIMPLE behaviour is obscure enough that in my experience even experts don't always get it (I'm an expert and I have to think twice about it!) As ypercube points out, SIMPLE is effectively the default in Microsoft SQL Server but I suspect most people working with that product never even considered it. If one of your goals in database design is to make data comprehensible by and useful to the average user then foreign keys ought to be non-nullable.


PostgreSQL 11 trigger can't select a value in a table to use it in a second table

There are many errors:

It must be a BEFORE trigger to modify the row about to be inserted.
There is no column base in table log.
TG_OP is always in upper case, so you must compare with 'INSERT'.


Method 1

If you are using Percona Server or MariaDB (>= 5.2), you can simply set the userstat/userstat_running variable to enable a bunch of new INFORMATION_SCHEMA tables including one called TABLE_STATISTICS that provides exactly this information.

For example:

mysql> SELECT TABLE_NAME, ROWS_READ, ROWS_CHANGED, ROWS_CHANGED_X_INDEXES FROM TABLE_STATISTICS ORDER BY ROWS_CHANGED DESC LIMIT 5;
+-------------------+------------+--------------+------------------------+
| TABLE_NAME        | ROWS_READ  | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+-------------------+------------+--------------+------------------------+
| user              |   21122527 |      5989231 |               23956924 |
| audit             |       1208 |      5020929 |               20083716 |
| sometemp          |   13995426 |      3182150 |                9546450 |
| creditcards       |    3566482 |      2998976 |               11995904 |
| order             | 2147483647 |      2662606 |               53252120 |
+-------------------+------------+--------------+------------------------+

ROWS_CHANGED would correspond to the most written to tables and ROWS_READ would be the most read from.  You should also look at INDEX_STATISTICS to find your most and least used indexes.

See also the MariaDB user statistics documentation.

Method 2

If you are not using Percona Server, you could use pt-query-digest to capture a sample of your queries and then filter out only INSERT/UPDATE/DELETEs.  That would look something like this:

mysql> SELECT @@GLOBAL.slow_query_log_file;
+------------------------------------------+
| @@GLOBAL.slow_query_log_file             |
+------------------------------------------+
| /var/logs/mysql/slowquery.log            |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL slow_query_log_file='/tmp/allqueries.log';
mysql> SELECT @@GLOBAL.long_query_time;
+--------------------------+
| @@GLOBAL.long_query_time |
+--------------------------+
|                 0.250000 |
+--------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL long_query_time = 0;
mysql> FLUSH LOGS;
mysql> SLEEP 600; SET GLOBAL long_query_time = 0.25; SET GLOBAL slow_query_log_file='/var/logs/mysql/slowquery.log'; FLUSH LOGS;

Now you have a file, /tmp/allqueries.log that contains every query executed on your server for ~10 minutes.

Next, analyze it with pt-query-digest to get the most frequently written to tables:

pt-query-digest /tmp/allqueries.log --group-by=distill --filter '$event->{arg} =~ m/^(update|delete|insert)/i' --limit 5 > /tmp/writes.txt

If you examine /tmp/writes.txt, you will see a section near the top that looks like this:

# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M   Item
# ==== ======== ============= ===== ====== ==== ===== ====================
#    1 0x        0.0558 26.8%   282 0.0002 1.00  0.00 INSERT UPDATE user
#    2 0x        0.0448 21.5%   246 0.0002 1.00  0.00 UPDATE audit
#    3 0x        0.0228 10.9%    11 0.0021 1.00  0.00 UPDATE sometemp
#    4 0x        0.0108  5.2%    16 0.0007 1.00  0.00 UPDATE creditcards
#    5 0x        0.0103  4.9%    43 0.0002 1.00  0.00 UPDATE order

Roughly, these are your most written to tables for the duration of the sample you chose.  To get the most read from tables (roughly), you can change the --filter parameter to --filter '$event->{arg} =~ m/^select/i' and you will see similar output.

If you are only interested in writes, you can pass a binary log into pt-query-digest and get similar results:

mysqlbinlog mysql-bin.000511 | pt-query-digest --type=binlog --group-by=distill > /tmp/writes.txt

You can also get the same data with tcpdump and pt-query-digest --type=tcpdump

So, this being said, assuming that you are using InnoDB tables, I highly doubt that you will see much performance benefit from doing this at all.  Because of the way data is buffered to the InnoDB log and then written to disk, I wouldn't expect much or any performance gain from moving individual tables around like this.  You might see some benefit from moving the InnoDB log files themselves to separate, faster disk to separate the log read/writes from the tablespace read/writes, but even that is questionable.  Investing in fast, high quality RAID arrays with a battery backed cache (or better yet, SSD) will be a better use of your resources.


Using SELECT within to_tsvector call in CREATE INDEX

You don't. You can't create an index that references another table.
To set the default see default-text-search-config

Workaround

You can however use functions in an index. And functions can references external tables. That said, using this is kind of hack because changing the table (common.lang) will require reindexing and clearing the session cache.

CREATE FUNCTION common.mylookup(id int)
RETURNS regconfig AS $$
  SELECT name::regconfig
  FROM common.lang
  WHERE id = id
$$
LANGUAGE sql
IMMUTABLE;

CREATE INDEX
  ON source.user
  USING GIN (to_tsvector(common.mylookup(profile_lang_id), name || ' ' || screen_name ));

You can mark functions as IMMUTABLE which makes this permissible. If the underlying table mutates you'll have to REINDEX the index. In projects that use this hack, like PostGIS, they recreate indexes on point releases.

Followup

  @EvanCarroll, I'm trying to create the tsvector on name || ' ' || screen_name. – Brooks 4 mins ago

Full text search isn't there to do what you think it does. It's not there to search multiple fields. It's there to vectorize word content and make use of dictionaries, stubbing, lexers, gazetteers, stop-word elimination, and a slew of other tricks none of which apply. If this doesn't make sense to you, you'll have to read the docs. If what you want is grep then FTS is only seldom what you want. If you want to grep over small chunks of non-standard text (like names) it's not what you want. What you likely want trigram indexing.

If all you want is a %term% on two fields, you're better off just doing that with a trigram index.

CREATE EXTENSION pg_trgm;
CREATE INDEX ON source.user USING GIN ((name || ' ' || screen_name) gin_trgm_ops);
WHERE name || ' ' || screen_name like '%$1%';

Or even better,

CREATE INDEX ON source.user USING GIN (name gin_trgm_ops, screen_name gin_trgm_ops);
WHERE name LIKE '%$1%' OR screen_name LIKE '%$1%';


Updating column in table a from top 1 in column b with foreign key relationship

How can I change my update to automatically to this for all players?

Something like this, using a CTE?

with cte as(
select playerprofileid ,Points,row_number() over(partition by playerId order by points desc) as rownum
from scores)

UPDATE playerP
set HighScore = cte.Points
FROM Playerprofile PlayerP
inner join cte on cte.PlayerProfileId = PlayerP.PlayerProfileId
where rownum = 1;


How can I rewrite for SQL Server a trigger that writing for Oracle?

You can use trigger like this:

CREATE TRIGGER tr_P000KUL_INSERT
ON P000KUL
    FOR INSERT
    AS
    update inserted
    set system_date = convert(varchar, getdate(), 103),
        system_time = convert(varchar, getdate(), 108);
go


unixODBC - [IM002][unixODBC][Driver Manager] Data source name not found and no default driver specified

I had similar issues in RHEL 5/7. To resolve it, I installed Oracle Instant Client, and used it, I think unixODBC is designed to be used with instant client, and not with full oracle client.

Oracle Instant Client for RHEL (you can use 11/12/18/19 versions):

sudo yum localinstall -y oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm
sudo yum localinstall -y oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64.rpm
sudo yum localinstall -y oracle-instantclient18.5-odbc-18.5.0.0.0-3.x86_64.rpm

unixODBC from source:

curl -o unixODBC-2.3.7.tar.gz http://www.unixodbc.org/unixODBC-2.3.7.tar.gz
tar -zxvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure --prefix=/ --sysconfdir=/etc
make
sudo make install

#/etc/odbcinst.ini
[Oracle]
Description     = ODBC for ORACLE (64 bit)
Driver          = /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
Setup           =
FileUsage       = 1
CPTimeout       =
CPReuse         =
Driver Logging  = 7

#/etc/odbc.ini
[mydnsname]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle   #the name from [Oracle] in /etc/odbcinst.ini
DSN = mydnsname
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = myservicename    #servicename from tnsnames.ora
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = schemaname
Password = schemapass
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
MaxTokenSize=8192

#tnsnames.ora
myservicename =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myservicename)
    )
  )

# ~/.bashrc
export ORACLE_HOME=/usr/lib/oracle/18.5/client64
export LD_LIBRARY_PATH=/usr/lib:/usr/lib/oracle/18.5/client64/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin #optional

Check your environment variables, then check the dependencies:

NOTE: for some distros you need this

sudo ln -s libodbcinst.so.2 libodbcinst.so.1

Check this (it must resolve all files required) you can adjust LD_LIBRARY_PATH:

$ldd /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
        linux-vdso.so.1 =>  (0x00007ffd417fd000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fe43a762000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fe43a460000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe43a244000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fe43a02a000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fe439e22000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fe439c20000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fe439a07000)
        libclntsh.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntsh.so.18.1 (0x00007fe435a49000)
        libclntshcore.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntshcore.so.18.1 (0x00007fe435466000)
        libodbcinst.so.2 => /lib/libodbcinst.so.2 (0x00007fe43524b000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fe434e7e000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fe434c68000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fe43ac2a000)
        libmql1.so => /usr/lib/oracle/18.5/client64/lib/libmql1.so (0x00007fe434a08000)
        libipc1.so => /usr/lib/oracle/18.5/client64/lib/libipc1.so (0x00007fe434596000)
        libnnz18.so => /usr/lib/oracle/18.5/client64/lib/libnnz18.so (0x00007fe433e41000)
        libons.so => /usr/lib/oracle/18.5/client64/lib/libons.so (0x00007fe433bf1000)

That's all.

To test, you can use isql

$isql -v mydnsname
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from cat;
+-------------------------------+------------+
| TABLE_NAME                    | TABLE_TYPE |
+-------------------------------+------------+
| TEST_TABLE                    | TABLE      |
+-------------------------------+------------+
SQLRowCount returns -1
1 rows fetched


Creating an index on table A but with reference to table B

If I understand you correctly you have two tables (for this question):

CREATE TABLE Clients
(Client int, ClientName varchar(50), Lots Of Other Columns)

and

CREATE TABLE Payments
(PaymentID, Payment Columns, ClientID)

Obviously not actual code there to create your tables...

And your application is somehow joining those tables behind the scenes. Probably on the ClientID. So your user runs a filter on "Acme Company" and behind the scenes SQL Server is doing a JOIN statement.

Again - pseudocode

SELECT TheDataIWantFromPayments, TheCustomerDataIWant
FROM Payments
INNER JOIN Customers
ON Payments.ClientID = Customers.CliendID
WHERE Customer.ClientName = 'Acme Company'

So here is where having more information can really help. On first blush this could be a problem with a simple index create as a solution. Perhaps there is no index on the clientID in each table. Perhaps there is no index on the ClientName.

But there could be other factors at play, too. It is possible that the vendor issues this query as a contains search using the syntax of CompanyName LIKE '%Acme Company%' and then no index on CompanyName would help. It could be that the database design requires a lot of key lookups and an index isn't the solution.

Either way - 6 minutes for 15 rows is not acceptable. I would look at those tables and at least verify that the CompanyName and ClientID (or whatever the columns are that contain those values) are indexed on each side.

I would also strike up a conversation with the vendor. Even just you adding indexes could be an action that violates your support contract. Perhaps there are considerations that they already have. And you shouldn't have to be working through someone else's bad design or poorly performing application.

You can gather some data to help you. Play with the PAL tool and look at your performance data on your system. If you have a development/test environment and you can recreate this, perhaps through playing with Extended Events or SQL Trace you can see the query that issued and then run the query showing a query plan to get a sense for what is happening. But I would definitely loop the vendor in to a question like this.


MySQL - Do not allow two rows to have the same specific columns

CREATE TABLE `t1` (
  `id` int(11) ,
  `source` varchar(30) ,
  `target` varchar(30) ,
  `desc` varchar(100) ,
  PRIMARY KEY `pk_id` (`id`),
  UNIQUE KEY `uc_source_target` (`source`,`target`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

or on existing table

 ALTER TABLE t1 ADD CONSTRAINT uc_source_target UNIQUE (`source`,`target`);

INSERT INTO t1
    (`id`, `source`, `target`, `desc`)
VALUES
    (0, 'hello', 'hola', 'this is a description'),
    (1, 'hello', 'no', 'this is a description'),
    (2, 'what', 'what', 'desc');

 mysql>  INSERT INTO t1
    ->            (`id`,`source`,`target`,`desc`)
    ->     VALUES
    ->             (3,'hello','hola','testing');
ERROR 1062 (23000): Duplicate entry 'hello-hola' for key 'uc_source_target'


CREATE TABLE TEXTIMAGE_ON [PRIMARY] is redundant it there's only one filegroup?

Yes, this is redundant. By default, SSMS will script these

The option is here:


Difference between High Availability data synchronization across local replicas and DAG

What is the difference between synchronizing from Primary to Secondary and across DAG (amount of data)

The main difference is that in a Distributed AGs, there is a "forwarder" which is the primary replica on the secondary side. The data is transferred once across the wire to the forwarder and then sends it to the replicas in its' cluster. My guess is that you've also set the Distributed AG up as asynchronous, which would also show differences over synchronous.

  Can we enable local synchronization to work similar to the one across DAG

I'm not sure what you mean, here.

  What factors that affect the amount of data sent across the wire beside the obvious ones

Generally it can be traced back to insufficient disks, high latency networks, overloaded VMs, etc. The way that applications are written, to an extent such as the size and scope of transactions can play a large part too. There are too many variables so I listed the main ones.


Oracle12c: Objects attempting to import to original tablespace (despite wanting to remap them)

Original import does not have any option to remap tablespaces. The tablespace metadata comes from the dumpfile, that is why import wants to create them in the original tablespace.

As a workaround, you can manually pre-create the tables in the new tablespace then load data into them.

But forget original export and import, just use Data Pump.

In the database:

create directory foo_dir as 'D:\app\ORACLE\admin\orcl\dpdump';
grant read, write on directory foo_dir to foo;
grant read, write on directory foo_dir to foo2;

Export:

expdp foo/foo2019 directory=foo_dir dumpfile=foo.dmp logfile=foo.log

Import:

impdp foo2/foo2019 directory=foo_dir dumpfile=foo.dmp logfile=foo2_imp.log remap_schema=foo:foo2 remap_tablespace=foo:foo2


You can execute Dynamic SQL on that Linked Server by executing sp_executesql on that remote server using the 4-part name. Then you just assign that to a variable marked as OUTPUT:

DECLARE @RemoteName sysname,
        @LoginID INT;

SET @LoginID = 267;

EXEC [LinkedServerName].master.dbo.sp_executesql
    N'SET @tmpRemoteName = SUSER_NAME(@tmpLoginID);',
    N'@tmpLoginID INT, @tmpRemoteName sysname OUTPUT',
    @tmpLoginID = @LoginID,
    @tmpRemoteName = @RemoteName OUTPUT;

SELECT @RemoteName;

Of course, it is debatable as to whether this is "cleaner" than JOINing to that remote table as there are pros and cons to each approach.


If p_key is the primary key of my_table, it should not be necessary to add the other columns, unless you want the statement to comply with the SQL standard.


How to optimize query based on explain analyze in PostgreSQL?

You need this index to makenthe query fast:

CREATE INDEX ON mytable (color_code, shade_type);

If you want to aim for an index only scan, you can use

CREATE INDEX ON mytable (color_code, shade_type) INCLUDE (amount);

But that is only useful if the table gets vacuumed often enough, and you don't need HOT updates for the amount column.


SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Availability Groups have rapidly become Microsoft's key HA strategy. They are slightly easier to setup and maintain than SQL Server Failover Clustering but provide slightly different benefits and drawbacks. FCIs are purely for SQL Instance level HA solutions whereas Availability Groups provide HA at the Availability Group (group of databases) level.

They are not without their faults or restrictions but their simplicity, flexibility and integration with Azure make them compelling to use for SQL Server, SharePoint and Systems Center HA (where supported).


Index Optimize cause DB size increasement

Yes that is normal.

See: Disk Space Requirements for Index DDL Operations

  When a new index structure is created, disk space for both the old
  (source) and new (target) structures is required in their appropriate
  files and filegroups. The old structure is not deallocated until the
  index creation transaction commits.

  The following index DDL operations create new index structures and
  require additional disk space:

  ALTER INDEX REBUILD

So if one of your indexes was rebuilt by the script, the size needed for that operation was the size of the old index structure + the size of the new index structure for the time the operation was running.

Once the new structure is in place the old structure is removed and free space remains inside the data file, but it is not returned to the operating system so your data file ends up larger but should include some free space that will be reused in the future.

Also take a look at Index Disk Space Example

  Total disk space required to support both the source and target
  structures for the duration of the index operation is 816 MB (363 +
  453). The space currently allocated to the source structures will be
  deallocated after the index operation is committed.


Filtered index not chosen, and rejected with hint

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values.

The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid the sort of runtime error you describe.

  Failure to results in a NESTED LOOPS JOIN from my non-clustered index against a clustered index Key Lookup, presumably to grab the parentId. INCLUDING parent ID eliminates this, and leaves me with a nice non-clustered index scan.

This is a known current limitation. Adding the filtered column(s) to the key or include list is the standard workaround, and a current best practice for all sorts of semi-related reasons.

  The FORCE ORDER, MERGE JOIN is definitely needed though.

Be extremely careful using hints (directives) like this unless you fully understand all the consequences. FORCE ORDER in particular is an extremely powerful and wide-ranging hint, with a number of non-obvious side-effects including the placement of aggregate operators, and the order of evaluation of subqueries and common table expressions.

For the most part, you should try to write queries that provide the query optimizer with enough good-quality information to make the right decisions without hints. The hinted plan may be 'optimal' today, but it may not remain so as the data volume and/or distribution changes over time.


Why does sqlcmd truncate varbinary(max)?

Because it's it might be GBs of data, so the query tools like SQLCMD and SSMS truncate the results for display.

You can dump to a file with bcp.exe, setting the "prefix-length" to 0.

bcp "select content from sys.assembly_files where name = 'microsoft.sqlserver.types.dll'" queryout microsoft.sqlserver.types.dll -T

Enter the file storage type of field content [varbinary(max)]:
Enter prefix-length of field content [8]: 0
Enter length of field content [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 62     Average : (16.13 rows per sec.)


SQL Server 2008 R2 - Upgrade Default DB Instance (Express) to Developer Edition

I don't think it is possible to upgrade SQL Server Express to another edition.  You should be able to use all the features of SQL Server you mentioned (agent and profiler) with the new, named instance (DEVEDITION) along side of Express Edition.

I don't think its possible to rename a SQL Server instance name, at least not with a bunch of headache.

What I would do is:

Detach the dbs connected to your SQL Server Express instance and uninstall SQL Server Express.  Then re-install the dev edition to the default instance and reattach the dbs.


Slow MySQL InnoDB performance of table with 100 million rows

Are there multiple rows for each dna_bits?  If not, then that column could be the PRIMARY KEY?  If you can do that, then the speed might double because you won't be bouncing between the secondary index and the PK.  Getting rid of p_id and the secondary key would save space.

How much RAM?  How big is the table?  (I would guess 5GB data+index.)  What is the value of innodb_buffer_pool_size?  If that can be set to 70% of available RAM and still be bigger than the table size, then (eventually) all the data you need will be cached.  Again a speedup.  (Can't tell how much.)  This is because of avoiding I/O.

10K lookups will take some time.


B2B application: users has its own users

Your approach is unnecessarily complicated. Your situation boils down to this:

You have users (also persons), with their corresponding attributes, including login credentials and such, but also status and last login date.
A user (person) can have one (or more?) role(s).
A role has one or more permissions.
A user can be created by one other user, so the user's creator is among the user's attributes.

So, I'd model three entities:

User (with all the properties), with a self-reference to the user's creator.
Role, with a many-to-many relationship to User.
Permission, with a many-to-one (or many-to-many) relationship to Role.

That's it.


Oracle copy DB from one windows server to another

Sounds like a data pump using a network link would do the job.  It's always preferable to run it during quieter hours but if your source database is not under powered then you might be able to run it during regular hours without your user's noticing any difference in performance.

From the article

create a database link from the destination to the source
grant EXP_FULL_DATABASE on the source database to the user in the link
grant IMP_FULL_DATABASE on the destination database to the user with the link OR grant DATAPUMP_IMP_FULL_DATABASE role to the destination database user

impdp test/test@db10g tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DIR logfile=impdpSCOTT.log remap_schema=SCOTT:TEST

Things to investigate

the PARALLEL options which will speed things up.
any custom tablespaces on the origin database?
are you going from the same character set to the same character set?
you do have a development environment to test this..


How to influence the planner on Postgresql

You can influence the planner with

set local enable_seqscan = off;

to see if it helps.

See more at http://www.postgresql.org/docs/9.5/static/runtime-config-query.html


Official documentation will rarely list all the things that could happen if advice or practices are not followed as the list would very quickly become far too long, unwieldy, and outdated. Instead it is much easier for the group — or individual — to state the recommended practice and move on, leaving all the different possible situations, conditions, and failures to sites like this one. By sharing the issue you encountered, other people can learn from your experience and hopefully resolve their issue.
With regards to your second question:

I couldn't get dump operation always make schema level lock -not to lock the whole table. So, how could this happen?

I am not quite sure what you’re asking but, when a backup of a table is being made, the table must be locked to ensure consistency. If records are modified or deleted during a backup process, then the backup may be wrong. If it is wrong, then it is unreliable. If it is unreliable then it cannot be trusted. If one backup cannot be trusted then …
You get the picture.
Generally what I will do with MySQL backups is pause replication, wait for any in-progress transactions to complete, lock the entire database, make a snapshot, unlock the database, then resume replication. This ensures the entire backup is accurate to a specific point in time.


Condsiderations for linked server security options in SQL Server

(Two things to mention for typical simple configurations)

If you have the option set to Be made using the login's current security context then . . .

ensure that the security principal (and context) that the job will RUN AS from the SQL Agentjob also has appropriate access to any of the linked server DB(s) which the job will need to access for it's queries, etc.
if the security principal is a local SQL login and not a domain or trusted domain account, then be sure that the local SQL account is created with a matching SID on both the linked server SQL Server instance and the SQL Server instance where the job is using the linked server from.

If you have the option set to Be made using the security context: then . . .

ensure the local SQL login security principal is created with a matching SID on both the linked server SQL Server instance and the SQL Server instance where the job is using the linked server from.


There was a full set of instructions for the 2.6 upgrade that you don't seem to indicated that you have followed.
In particular are the instructions that indicate you need to process the --upgrade option by launching a single mongos instance while the balancer is switched off:

Start a single 2.6 mongos instance with the configDB pointing to the cluster’s config servers and with the --upgrade option.
To run a mongos with the --upgrade option, you can upgrade an existing mongos instance to 2.6, or if you need to avoid reconfiguring a production mongos instance, you can use a new 2.6 mongos that can reach all the config servers.

mongos --configdb <config servers> --upgrade

As noted there, on completion the following messages should be emitted:
upgrade of config server to v5 successful
Config database is at version v5

So there is an intended upgrade process for the config servers that it would appear you have not followed yet. The full process is in the link at the top of this answer.
Also have a look at the compatibility changes list which has some additional considerations with replica sets and sharding that might possibly apply in your situation.


Mongodump affects app performance really bad

All data dumped via mongodump has to be read into memory by the MongoDB server. It's also worth noting that mongodump backs up data and index definitions; the time to restore can also be significantly longer as compared to other approaches since mongorestore will need to recreate any secondary indexes after the data is loaded.

As noted in the MongoDB documentation, mongodump is useful for backing up and restoring small deployments but is not ideal for capturing full backups of larger systems:

  When connected to a MongoDB instance, mongodump can adversely affect mongod performance. If your data is larger than system memory, the queries will push the working set out of memory, causing page faults.

A standalone server limits your backup options if you also want to keep your deployment available while taking a backup.

Here are a few suggested approaches in order of most to least recommended:

Approach #1: Use a cloud backup service

For the easiest short term solution, I would consider using a commercial cloud backup service like MongoDB Cloud Manager. MongoDB Cloud Manager provides continuous backup with scheduled snapshots and a retention policy (see Backup Preparations for more info). A cloud service also avoids you having to deploy any extra servers/infrastructure, so even if you plan to do so in future this is a helpful short-term solution.

The general approach would be:

Convert your standalone server into a single-node replica set (i.e. restart with the replSet parameter and run rs.initiate()).
Sign up for MongoDB Cloud Manager.
Download & install the Cloud Manager Backup Agent.

As an added benefit, Cloud Manager also includes a monitoring agent which can capture metrics history from your deployment and allow you to configure alerts.

Approach #2: Convert your deployment into a replica set and backup from a hidden secondary

This approach requires provisioning some extra infrastructure, but offloads the impact of backup from your primary server. Typically replica sets are provisioned with at least three members for high availability and automatic failover, but if your only goal is backup you can use a less ideal two server configuration.

The general approach would be:

Provision a second server which will be used for backup
Convert your standalone server into a replica set.
Add your backup server as a hidden secondary with a priority of 0 (it will never become primary) and 0 votes.
Use one of the supported backup methods to take backups on your hidden secondary. The backup methods are listed in general order of recommendation: filesystem snapshots (if supported by your configuration) or file copy (assuming you stop mongod) are preferable to mongodump.
(ideally) add another data-bearing secondary if you'd like the high availability & failover benefits of a replica set configuration.

Approach #3: Use filesystem snapshots (if available & appropriate)

A less impactful backup strategy than your current mongodump would be using filesystem snapshots, assuming you have a filesystem that supports snapshots (and all of your data and journal files are on a single volume so you can get a consistent snapshot of a running mongod). The upside of filesystem snapshots is that all data does not have to be read into memory by mongod, however snapshots can still have impact (particularly when creating the initial snapshot on a busy system). Successive snapshots are more efficient and less impactful, but are still not a complete backup solution as the snapshots are local to your server (and you only have a standalone at the moment).

Caveats

Approaches #1 and #2 both involve enabling replication to faciliate backups. Replication will add some additional local I/O on your primary server as all write operations are noted in a special capped collection called the oplog (operations log).
You've mentioned a likely need for sharding in future, but before doing so I would isolate your MongoDB workload from the other processes sharing the same server. If you can change your backup strategy to something more efficient than mongodump, remove resource contention, and capture some baseline metric history for review ... you may find that sharding is not required yet.


You are working under a false assumption, event_files do not store the data as XML, that is what ring_buffer does.

event_file: Data from an event_file target is displayed very well, with rich features available.
ring_buffer: Data from a ring-buffer target is displayed as raw XML.

There are a few options but for me the simplest is to put the data in a SQL database.
Once you have the data displayed in SSMS: Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data
Use the new toolbar menu item 'extended events' and export to a table in SQL database.
The database needs to exist when you export but the table does not.  I used a database named xEvent on a test/sandbox instance.
From the Toolbar: Extended Events > Export to > Table.. > (Connect to 'test/sandbox instance' > Existing database > (Type in any name you like)
Use standard SQL to query.

Reference Advanced Viewing of Target Data from Extended Events in SQL Serve


MySqldump Error 2013: Lost Connection MySQL During create database backup

Given your DB dimensions try increasing those values a little more ,
Example :
max_allowed_packet=2048M
net_read_timeout=3600
net_write_timeout=3600

Don't forget to restart/reload mysqld aftewards.


Combining nullif while checking if field is an integer with a case statement

I believe Azure supports TRY_CAST.

  (Returns a value cast to the specified data type if the cast succeeds;
  otherwise, returns null.)

Here are three examples that seem to demonstrate what you're after:

--Column is blank, then NULL
DECLARE @YourCol1 VARCHAR(10) = ' '

SELECT CASE
        WHEN @YourCol1 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol1 AS TINYINT), 255)
        END AS YourCol1

--Column won't successfully CAST to TinyInt, then 255
DECLARE @YourCol2 VARCHAR(10) = 'abc'

SELECT CASE
        WHEN @YourCol2 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol2 AS TINYINT), 255)
        END AS YourCol2

--Column successfully CASTS to TinyInt, then actual value
DECLARE @YourCol3 VARCHAR(10) = '21'

SELECT CASE
        WHEN @YourCol3 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol3 AS TINYINT), 255)
        END AS YourCol3


How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

Column-organized tables do not support some features, including identity columns. Apparently, in your database the default table organization (the DFT_TABLE_ORG parameter) is set to columns. Either change that or create the row-organized table explicitly:

CREATE TABLE users (
   user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   user_name VARCHAR(16) NOT NULL,
   email_address VARCHAR(60),
   PRIMARY KEY (user_id)
) ORGANIZE BY ROW


MySQL stored routine performance while using PREPARE

You shouldn't be seeing a perceptible performance difference from preparing a statement compared to having one hard-coded into your stored procedure.  You also should see no difference at all from the fact that the procedures are stored in a different database from your data.

From the start, though you're presenting us with two different unknowns -- different schema and dynamic SQL.  The best way to troubleshoot this is to sectionalize the problem down to the individual variables... by putting a procedure with static (not dynamic) in a different schema from the data (shouldn't matter) or by putting a procedure with a dynamic query in the same schema as the data (also shouldn't matter).

But I suspect the problem is related to neither factor.  I think it's the dynamic queries you're building.  You may actually be getting wrong results, in addition to your queries taking longer to run.

You appear to have startdate and enddate concatenated right into the text of your dynamic sql.  That cannot possibly be valid, since prepared statements do not share procedure variable scope with stored procedures, and concatenation like this puts the names of the variables, not their contents, in to the string... in fact, that dynamic sql should throw an error when you run it -- however, there's an insidious exception, here: it would appear to work if the view you're querying also happens to have columns called startdate and enddate... but the query isn't giving you the correct results, because it thinks you're referring to columns in the queried table in your WHERE clause -- not the procedure variables, making your where clause essentially not eliminate any rows based on those dates.

You could replace startdate with @startdate and enddate with @enddate in the body of your prepared statement, and then, before the actual PREPARE, assign the values of those session ("@") variables to be the same as the procedure variables:

SET @startdate = startdate;
SET @enddate = enddate;

Note that this doesn't put the contents of the variables into your query, either -- concatenating the contents of variables from outside your procedure into dynamic sql almost always is a bad (insecure) idea -- but this doesn't do that.  It puts the variable names into the statement, which is safe.

I think this is the solution to your problem, if the question accurately represents the dynamic sql you're building.

However, I have a further recommendation, in the interest of security and good practice.  I mentioned never concatenating external variables into dynamic sql... but you can easily sanitize the variable db_name, making it safe to concatenate in.

Replace this:

FROM ',
db_name, '.view
WHERE

with this:

FROM `',
(SELECT schema_name FROM information_schema.schemata
  WHERE catalog_name = 'def'
    AND schema_name = db_name), '`.view
WHERE

This scalar subquery will return only two possible things: it will always return either the same value passed in as db_name if that database really does exist; otherwise it will return NULL.  This will make the generated SQL throw an error if an invalid database name is provided, rather than potentially executing arbitary sql code slipped into your dynamic query string via the db_name variable and executed with the permissions of the procedure definer.


How to delete rows from a table when we're joining and return data?

Very simple indeed, but you do need to include the other WHERE clause as well:

DELETE FROM batch bp
USING  sender_log sl
WHERE  bp.log_id = sl.id
AND    bp.protocol = 'someprotocol'
RETURNING bp.*, sl.*;

And to actually return what your question outlines, you need to include both tables in the RETURNING clause.