How to check if the current connection is in a transaction?

A simple way is to compare now() to statement_timestamp().

now() gives the current date and time (start of current transaction).
statement_timestamp() gives the current date and time (start of current statement).

Example:

SELECT now() = statement_timestamp();
-- TRUE

BEGIN;
SELECT now() = statement_timestamp();
-- FALSE

The other alternative is to execute two queries:

SELECT txid_current();
SELECT txid_current();

and compare the resulting xid. If it is the same, then you are in a transaction.

The downside to the latter approach is that every txid_current() increments xid value and will further advance you to a wraparound.


Multiple self Join on Mysql 5.7 - Poor Perfomance

You need 4 composite indexes such as

INDEX(country, geonames_code, adm2)

(The columns can be in any order.)

Please provide SHOW CREATE TABLE; my following two comment would be clarified by it.

LOWER(gp.description) like 'lo%' needs for the collation of description to be ..._ci.  If it is, then this would work much better:

`gp.description like 'lo%'`

That is, a "case insensitive" collation obviates the need for LOWER().

If you don't have an index starting with description, add such.

For lat/lng, decimal(65,7) is much too large -- it takes 33 bytes!  This discusses much smaller alternatives.


You can use a table constructor with VALUES:

update account
set currency = nv.currency
from
    ( values
        (12, 'EURO'),
        (18, 'DOLLAR'),
        (13, 'Pound')
    ) as nv (id, currency)
where account.id = nv.id ;


script to find all bigint that can be converted to int fields in postgresql performance

Following script should help to do the job:

create or replace procedure cbi ()
language plpgsql
as $$
--
declare
s text;
max_value bigint;
c cursor for
 select
  pg_namespace.nspname,
  pg_class.relname,
  pg_attribute.attname,
  pg_type.typname
 from pg_class
 join pg_namespace
 on pg_class.relnamespace = pg_namespace.oid
 and pg_namespace.nspname <> 'pg_catalog'
 join pg_attribute
 on pg_class.oid = pg_attribute.attrelid
 join pg_type
 on pg_attribute.atttypid = pg_type.oid
 and pg_type.typname='int8'
 where pg_class.relkind in ('r','v');
begin
 for r in c
 loop
  s := 'select  max(' || r.attname || ') from ' || r.nspname || '.' || r.relname;
  execute s into max_value;
  if max_value <= +2147483647 and max_value >= -2147483648
  then
   raise notice '%.%.% max=% can be replaced with int.', r.nspname, r.relname, r.attname, max_value;
  else
   raise notice '%.%.% max=% CANNOT be replaced with int.', r.nspname, r.relname, r.attname, max_value;
  end if;
 end loop;
end;
--
$$;

Example:

postgres=# call cbi();
NOTICE:  public.pg_stat_statements.queryid max=9148650842799727408 CANNOT be replaced with int.
NOTICE:  public.pg_stat_statements.calls max=118 can be replaced with int.
NOTICE:  public.pg_stat_statements.rows max=4060311 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_hit max=577699 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_read max=637 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_dirtied max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_written max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_hit max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_read max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_dirtied max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_written max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_read max=171 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_written max=171 can be replaced with int.
NOTICE:  public.t.x max=1 can be replaced with int.
CALL
postgres=#


separate data from one column to 2 columns

sample data

;DECLARE @Data Table(Name  VARCHAR(20), Value INT, Valueid  INT
)
;INSERT INTO @Data
SELECT 'test1', 132, 1 UNION ALL
SELECT 'test2', 322, 2 UNION ALL
SELECT 'test3', 342, 1 UNION ALL
SELECT 'test1', 433, 2 UNION ALL
SELECT 'test2', 123, 1 UNION ALL
SELECT 'test3', 244, 2

Sql script

;WITH CTE
AS
(
    SELECT Name,[1] AS Value1,[2] AS Value2 FROM
    (
    SELECT * FROM @Data
    ) AS SRC
    PIVOT
    (
    MAX(Value) FOR Valueid IN([1],[2])
    )
    AS Pvt
)

SELECT c.Name,
       d.Value,
       d.Valueid,
       c.Value1,
       c.Value2
FROM CTE c
RIGHT Join @Data d
ON c.Name=d.Name

Expected Result http://rextester.com/AYGM10195

╔═══════╦═══════╦═════════╦══════════╦══════════╗
║ Name  ║ Value ║ Valueid ║ Valueof1 ║ Valueof2 ║
╠═══════╬═══════╬═════════╬══════════╬══════════╣
║ test1 ║   132 ║       1 ║      132 ║      433 ║
║ test2 ║   322 ║       2 ║      123 ║      322 ║
║ test3 ║   342 ║       1 ║      342 ║      244 ║
║ test1 ║   433 ║       2 ║      132 ║      433 ║
║ test2 ║   123 ║       1 ║      123 ║      322 ║
║ test3 ║   244 ║       2 ║      342 ║      244 ║
╚═══════╩═══════╩═════════╩══════════╩══════════╝


Does separating VARCHAR values based on size have a performance gain?

My gut says that any performance gain you get is unlikely to be worth the extra hassle (and potential for bugs ) resulting from needing to enforce the separation and perform multiple lookups in your application logic.

If you have a lot of small values and were only querying them and none of the rest you would see some performance gain as more rows would fit in each page so overall less pages will need to be processed in RAM or read from disk for a given query. As soon as you need all the properties in one go (or just a mix) this benefit is going to be blown out of the water by either needing to query multiple tables separately or via a UNION.

The only way to be sure of course is to rig up a reasonably realistic large dataset and run some performance tests against the arrangements you are considering. But I very much doubt you will see any change worth the extra complexity. If you data can be split in more logical ways (i.e. ways implied by your business logic) than I suggest you look into data partitioning, particularly if you can split the partitions over different drives. Whenever you find yourself contemplating a potentially complexity laden optimisation (including partitioning) always go back and reconsider your overall data structure and make sure it is not working against your business logic and check your hardware is sufficient for the load you expect - while it is not guaranteed you are likely to find much more significant gains through looking into these core areas instead.


This is only a partial answer as your requirements for viewing the reports in graph form are a bit vague for me, but that could also be because I tend not to aggregate blocking reports; instead I enable their collection on an as-needed basis.

This answer addresses the 5 second threshold.  As already pointed out by @Aaron Bertrand in the comments, blocking is a natural and expected behavior of SQL Server.  You can adjust the duration that blocking needs to occur before a query shows up in the Extended Event blocking report by adjusting the blocked process threshold (s) value via sp_configure.

The default value is 0, and while misleading isn't number of seconds, it means Off; all values greater than 0 refer to seconds.  The minimum number of seconds that blocking is required before queries will show up in the report is 1 second per my experience.  Also, take note that there are no guarantees when collecting blocked processes via the blocked process report.  As noted in the aforementioned article:

  The blocked process report is done on a best effort basis. There is no guarantee of any real-time or even close to real-time reporting.


CREATE DATABASE permission denied in database 'master'. unable to get the permission

The user must be a member of dbcreator server role for user to have enough permissions to create a database.

You can execute the following statement to make a user member of dbcreator server role.

EXEC master..sp_addsrvrolemember @loginame = N'Shubhankar', @rolename = N'dbcreator'
GO

Members of sysadmin role are by default members of dbcreator server role therefore they can create databases too.


Can I use LetsEncrypt to secure SQL Server?

Per Igor's suggestion, I went with the VPN method.  What this entailed was installing, and configuring OpenVPN, disabling all external TCP interfaces in the SQL Configuration Manager, and adding one for the standard OpenVPN IP (10.8.0.1), then under protocol, setting listen all to 'none.'

This solved a lot of issues - I also was running FileZilla server to transfer files, and now I no longer need to maintain a certificate, or keep this open to the outside world.  Now I can connect via VPN, and access the FTP server that way (binded the OpenVPN IP to the server).

Moral of the story is:  If you have a private server, configure a VPN, and stop wasting your time trying to secure things like SQL Server to the outside world (unless your web server isn't on your SQL box, of course)


You could use a merge statement with serializable hint.

merge table1 with (serializable) as T
using (select @_othervalue as othervalue) as S
on T.othervalue = S.othervalue
when not matched then
  insert (othervalue) values (othervalue);


How do I query PostgreSQL enums like in MySQL?

I think you have a misconception about ENUM because of MySQL breaking the spec and being goofy. They're different in both MySQL and PostgreSQL, but they're more apparently different in PostgreSQL. You're trying to seamlessly query an internal representation detail. I'm actually glad PostgreSQL prevents it.

Instead use the ENUM support functions

CREATE TYPE stuff AS ENUM ('a', 'b', 'c');
SELECT * FROM enum_range('b'::stuff, NULL);

This returns everything from 'b' to the unbounded top of the enum (in this case 'c')

Now create a table and insert some values,

CREATE TABLE bob ( foo stuff );
INSERT INTO bob (foo) VALUES ('a'),('b'),('c'),('a'),('a');

Now to query it, do this

SELECT *
FROM bob
WHERE foo = ANY(enum_range('b'::stuff, NULL));

Or more simply as Abelisto suggested in the comments. Note that the details are still abstracted. You don't know what the sort order of b is,

WHERE foo >= 'b'::stuff;
WHERE foo >= 'b';

Enums are ordered by default. In PostgreSQL they're stored as int4. In MySQL they're stored as int2 or int1. However, their implementations should not be revealed to the user.

Just think, in your query above, reloading from a database after you add a value could potentially re-order the enums. Then your query breaks because you've assumed a specific value for 2.

See also,

How does MariaDB handle ENUM types used in FOREIGN KEY constraints?
How does MariaDB handle joins on ENUMs?


IORA-00904: Invalid Identifier when converting Radians to Degrees

The DEGREES function you refer to is not in the Oracle database code.  It is in the JavaDB which is also known as Derby.  There is a construct in MySQL called DEGREES so this may be what you were thinking of.

You can make your own function like this

CREATE OR REPLACE FUNCTION radians_to_degree(p_radian IN NUMBER)
   RETURN NUMBER IS
BEGIN
   RETURN p_radian * 0.0174533;
END radians_to_degree;


How to insert values in junction table for many to many relationships?

You want to maintain atomicity of the action of storing a new place and trip, along with the intersection between the two.  In MySQL, you do this with a START TRANSACTION / COMMIT transactional wrapper.

You use the MySQL function LAST_INSERT_ID() (See documentation; similar to SQL Server variable @@IDENTITY) to get the value of the most recent auto increment value assigned.

To add a place and a trip and associate the two all at once, you would write code like this:

START TRANSACTION

DECLARE placeKey int

INSERT INTO places (place_name)
VALUES ('XYZ')

SET placeKey = LAST_INSERT_ID()

DECLARE tripKey int

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

SET tripKey = LAST_INSERT_ID()

INSERT INTO trips_places_asc(trip_id, place_id)
VALUES (tripKey, placeKey)

COMMIT

You should add error handling to the above and if an error occurs part way through, use the ROLLBACK command to undo the partially completed work.

You should also read up on LAST_INSERT_ID() to make sure you know how it works.  It can give you undesirable results in some cases, such as when you insert multiple rows with a single insert statement.


SQL Transactional Replication and Table level compression

There's no real pitfalls from doing this. You just need to be aware that there will be a slight increase in CPU on the subscriber, so if you're already CPU bound it might not be the best path forward.

You could consider using either pre or post snapshot script in order to help ensure that index compression is maintained in the event you need to run another snapshot (and to add any additional indexes that you require).

When performing the compression be aware that you will probably experience transaction log growth, much like you would if you rebuilt any other index. Sorting in tempdb is generally a good practice for something like that.


How to stop OSX MySQL filling up error logs

[This question is almost a year old, but anyways I just came across it while investigating the same issue, and although the answer is probably no longer relevant for you, it might be for someone else reading this.]

This can happen when using the standard MySQL installation including the MySQL preference pane. When opening the MySQL preference pane, it will periodically (about once per second) try to connect to localhost, presumably to find out if MySQL is running and display the status accordingly. Interestingly, these connections do not stop when going back to the System Preferences start screen, but they do stop when closing the System Preferences.

When initiating these connections, the preference pane uses various usernames (for whatever reason). On my macOS Sierra with MySQL 5.7.17, I observed _spotlight, _mysql and the current user’s username.

However, if you get those log entries even when System Preferences are closed, this is a different issue in your case.


Unable to migrate local database to RDS instance

I prefer using MySQL workbench. It's much more easier & user friendly than the command line way.

It provides a simple GUI.

MySQL workbench or SQL Yog.

These are the steps that I did:

Install MySQL Workbench.
In AWS console, there must be a security group for your RDS instance. Add an inbound rule to that group for allowing connections from your machine. It's simple. Add your IP-address.
Open MySQL workbench, Add a new connection.
Give the connection a name you prefer.
Choose connection method- Standard TCP/IP
Enter your RDS endpoint in the field of Hostname.
Port:3306
Username: master username (the one which which you created during the creation of your RDS instance)
Password: master password
Click Test Connection to check your connection.
If connection is successful, click OK.
Open the connection.
you will see your database there.
Now you can export your mysqldump file to this database. Go to-> Server. Click Data Import.
You can check whether the data has been migrated by simply opening a blank SQL file & typing in basic SQL commands like use database, select * from table;

That's it. Viola.


Can't create any objects within OLAP database

Use SQL Server Data Tolls (SSDT) to create the SSAS objects and deploy them to the SSAS server, where they can be managed through SSMS.  Once created, modifications can be made to cubes in SSDT with the changes deployed and accessible in SSMS.  The following items outline this further.  While the last link covers the deployment process, the third item (configuration) details the deployment server property and other deployment related settings.

SSDT
Creating SSAS Projects
Analysis Services Projects Configuration
Deploy SSAS Projects


Is index like key(timetime) usable when we query: SELECT * ....... ORDER BY timetime DESC

Yes, it does. MySQL can use the index and use it for scanning/seeking in either direction. You can read in MySQL documentation: How MySQL Uses Indexes

  To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order. See Section 8.13.9, “ORDER BY Optimization”, and Section 8.13.10, “GROUP BY Optimization”.


SQL Server 2012 Always On AG - DBEngine Service account locking

So just in case someone wants to know what caused this, It was a group policy!

Some time ago, unbeknown to me, the domain controllers for the domain in question had been upgraded to Server 2012. Along with this came a whole bunch of Windows Server 2012 group policies. Additional policies had been added to one of the parent server OU's with a filter applied.

Unfortunately the filter had a typo, and so it was being applied to all of my database servers on this domain.

I had almost ruled out it being a GP issue, as I seemed to have all the permissions I needed, and I could see connections coming in on the correct ports between each node. The servers were happily running as single nodes!

I asked the server team to move the servers (just to see) to the 'computers' OU, and after a forced gpupdate, bingo!

Unfortunately, I am unsure exactly which policy caused the problem as there were a lot! There was a number to do with NTLM usage and authentication, and I'm convinced the issue was related to this. I will at some point set up a test lab to try and replicate the issue.

So there you go! Always check group policies, even if you believe (like I did) that nothing had changed!


Short answer: No.

The only way to guarantee the order of rows returned is through the "order by" clause.

You have little or no control over how the database chooses to "get at" any of your data, and, therefore, the order in which it gets to those records in doing so.

You might get lucky and things might come out in the order you want ...   but don't rely on it.


Your Question
What you are looking to do is achievable in SQL Server using CTE recursive queries. MySQL's dialect of SQL does not support such querying.
I do understand the data and the output. For what you are looking to do, this is the solution:
R : 4
NodeQueue: <Empty>
OutputString: <Empty>

Add R's Children to NodeQueue
R : 4
NodeQueue: 2,10,11
OutputString: <Empty>

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 2
NodeQueue: 10,11,1
OutputString: 2

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 10
NodeQueue: 11,1,3
OutputString: 2,10

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 11
NodeQueue: 1,3,35
OutputString: 2,10,11

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 1
NodeQueue: 3,35,34
OutputString: 2,10,11,1

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 3
NodeQueue: 35,34 (3 has no children to add)
OutputString: 2,10,11,1,3

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 35
NodeQueue: 34 (35 has no children to add)
OutputString: 2,10,11,1,3,35

Remove Front of NodeQueue as R, Add R's Children to the NodeQueue, Add R to OutputString
R : 34
NodeQueue: <Empty> (35 has no children to add)
OutputString: 2,10,11,1,3,35,34

Algorithm ends here because the NodeQueue became empty.
GOOD NEWS
I have already written MySQL Stored Functions that do this

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query

Please look over these over because the algorithm I described is in these two posts. You will have to decide what constitutes the Root to start with.
Give it a Try !!!


We keep an .mwb file in the repo. Our team is small and changes to the schema are rare, so we can agree who modifies the file.

If the team were bigger I would introduce a lock file in the repo that's created by a person who wants to make a change and who deletes it when the change is committed.


Key points here, are  :

ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Version] DESC) AS RN

and

WHERE RN = 1

Here is:

CREATE TABLE Student
    ([ID] int, [Version] int, [Name] varchar(6), [Email] varchar(15))
;

INSERT INTO Student
    ([ID], [Version], [Name], [Email])
VALUES
    (1, 1, 'Johan', 'Johan@email.com'),
    (1, 2, 'Johan1', 'Johan@email.com'),
    (2, 1, 'Saif', 'saif@email.com'),
    (2, 2, 'Saif1', 'saif2@email.com'),
    (2, 3, 'Saif4', 'saif1@email.com')
;

-- select

;WITH StudentCTE AS
(
SELECT [ID], [Version], [Name], [Email]
  ,ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Version] DESC) AS RN
FROM Student
)

SELECT  [ID], [Version], [Name], [Email]
FROM StudentCTE
WHERE RN = 1

output:

ID  Version Name    Email
1   2   Johan1  Johan@email.com
2   3   Saif4   saif1@email.com


Abnormally high MySQL writes and larger than normal Binary Log files. How can I determine what caused this?

Don't use --short-form.  You are suppressing much of what you want to see.

--short-form
Display only the statements contained in the log, without any extra information or row-based events. This is for testing only, and should not be used in production systems.
— http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_short-form

That is a largely pointless option, since it returns only the statements contained in the log.  If you have a binlog that large, it's unlikely that it's full of statements -- it's full of row events.
--base64-output=decode-rows --verbose are the options you are looking for.
http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-row-events.html
You will, at the very least, not be disappointed by the quantity of data returned... it will be much larger than the actual binlogs themselves, which are stored in a relatively tightly-packed format.


postgresql statement taking ages to cancel in pgAdmin III

PostgreSQL generally does not visibly respond to cancellation requests while it is inside an externally-defined function.  It will simply set a flag, and then process the request between calls to ST_UNION, but not within them.


protect sensitive information if the database is ever hacked, stolen or mislaid.

This is a very broad requirement.

Encryption comes at the cost of CPU cycles, so better load test your application if the outcome is acceptable to your application performance.

There are areas to explore and combination of them will get you want you want.

Encrypting data at rest - this is done using TDE (Transparent data encryption). I have written about my experience embracing TDE in our environment here.
Encrypting all connections to SQL Server using Force Encryption. This is done using loading certificate on the database server, enabling force encryption (will require sql server service restart !) and granting read access to sql server service account to the certificate. You can verify using sys.dm_exec_connections --> column encrypt_option will be TRUE.
Column level encryption - protecting sensitive data - see this article.
If you are going to upgrade to SQL Server 2016, you can explore Always Encrypted option.

On the other hand, make sure you follow the security best practices (warning: its word doc !) with regards to sql server service accounts, logins & users, port allocation, etc.


MongoDB Too many open file while reSync member

Since you have 2TB of data to be resynced, try the following approach.

Follow the below approach for each replica set as required.

Connect to the Primary and back up the database using mongodump, also take the latest document oplog collection.
Shutdown the secondary where you want to resync the data.
Start this secondary as standalone server
Drop the oplog collection in the local database and recreate it by inserting the latest entry from the primary node.
restore the backup data taken from step 1 using mongorestore
Once the restoration is completed, shut down and restart the server as replica set member.


Syntax error, Hello World example. Begin ... End

It seems like mysql is confused about the delimeter.

The delimeter for RETURN CONCAT("Hello ", s); is ";".

The delimeter for the query is ";"

I had to change the delimeter to "//" get a good query and change it again to ";".

DROP FUNCTION IF EXISTS helloWorld;

DELIMITER //

CREATE FUNCTION helloWorld(s char(20))
   RETURNS CHAR(50)
   BEGIN

     RETURN CONCAT("Hello ", s);
   END; //

DELIMITER ;

SELECT helloWorld("Albert");

I don't know why, but this is happening only in my DB.


Oracle Equivalent of the following SQL Server syntax


To remove all elements from the column images (holding a json array) where 'id' is 'note_1':

pg 9.3

UPDATE items i
SET    images = i2.images
FROM  (
  SELECT id, array_to_json(array_agg(elem)) AS images
  FROM   items i2
       , json_array_elements(i2.images) elem
  WHERE  elem->>'id' <> 'note_1'
  GROUP  BY 1
  ) i2
WHERE  i2.id = i.id
AND    json_array_length(i2.images) < json_array_length(i.images);

SQL Fiddle.

Explain

Unnest the JSON array with json_array_elements() in a subquery using an implicit JOIN LATERAL for the set-returning function. Details:

PostgreSQL joining using JSONB
How to turn json array into postgres array?

JOIN to the base table and add another WHERE condition using json_array_length() to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.

pg 9.4

This gets much easier with jsonb and additional jsonb operators.

UPDATE items i
SET    images = i2.images
FROM  (
  SELECT id, array_to_json(array_agg(elem)) AS images
  FROM   items cand
       , json_array_elements(cand.images) elem
  WHERE  cand.images @> '{[{"id":"note_1"}]}'::jsonb
  AND    elem->>'id' <> 'note_1'
  GROUP  BY 1
  ) i2
WHERE i2.id = i.id;

Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.

Here are some example, benchmarks and comparison of new features to old json and MongoDB,  plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:

"CREATE INDEX ... USING VODKA"


check my.inior my.cnf if its contain sql-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" if so you need to change it to sql-mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" then restart the SID service.
Hope that will fix the issue for you


Add column to show rows with close time differences without cursor

Based on the question at this time, I assume you have a list of dates and times, and you want to assign a sequential number to them, with the same number assigned to all values where the previous value is no more than 10 minutes less than the current value, as shown in the desired results.

Let's assume your table of date time values is myDtTm, and the column name is theDateTime

SELECT theDateTime
      ,SUM(TimeDiff) OVER (ORDER BY theDateTime)
  FROM (
        SELECT
               theDateTime
              ,CASE WHEN COALESCE(DATEDIFF( minute
                                           ,LAG(theDateTime, 1)
                                             OVER (ORDER BY theDateTime)
                                           ,theDateTime
                                          ), 100000) > 10 --< this is the max time diff
                 THEN 1
                 ELSE 0
               END as TimeDiff
          FROM myDtTm
       ) sq
 ORDER BY theDateTime
;

This dbfiddle.uk link shows this in action, providing precisely the results shown in the question.

Explanation:

In the sub-query, we use the window function LAG to compare each date and time to the one before it, and figure out how many minutes' difference there is. We assign 1 to TimeDiff for the current date if the difference more than the target (ten minutes), and 0 otherwise. (Note: we get back NULL for the first row, because there is no previous row at that point; if you have NULL datetime values in your data, you may need to change this).

Then, we simply do a rolling total (SUM of TimeDiff over our datetime value gives us a total of TimeDiff for this row and all previous rows).

Note: I took the idea from this DBA.SE question; the differences were notable enough that I don't think this is a duplicate.


Merge Replication API with SQL Server 2012 / 2014

The RMO API is deprecated from SQL Server 2012 and onwards. Microsoft suggests using T-SQL instead.


SQLcmd error 'Failed to initialize sqlcmd library with error number -2147467259'

After hunting around a lot more, I found a few articles discussing security and granting permissions that sent me on a wild goose chase and ultimately made no difference to the outcome.

The script continued to work outside of sp_send_dbmail, but would not work with it.

I don't recall how, but I have subsequently discovered that stored procedures the indexing script allegedly uses had gone AWOL (ones created by the file CommandExecute.sql), even though the indexing script worked fine outside of sp_send_dbmail.

Once I created those stored procedures, everything seems to work as it should!


Advantages and Disadvantages to using ENUM vs Integer types?

I found a very bizarre but informative article about 8 reasons why one should not use ENUM.

Even without the article, I know

there is no easy method for adding new values, some techniques are very high risk
numbers should never be used
only use strings (@DTest already mentioned this in his answer)


unique constraint on a column with duplicate values

I'm sure this has already been asked before, but .. from the Oracle documentation
Further down the page, when you look for what each constraint state is:

VALIDATE | NOVALIDATE The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. Therefore they are described in the context of "ENABLE Clause" and "DISABLE Clause".
ENABLE Clause Specify ENABLE if you want the constraint to be applied to the data in the table.
If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specify KEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.
You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.
ENABLE VALIDATE specifies that all old and new data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.
If any row in the table violates the integrity constraint, then the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, then Oracle enables the constraint. Subsequently, if new data violates the constraint, then Oracle does not execute the statement and returns an error indicating the integrity constraint violation.
If you place a primary key constraint in ENABLE VALIDATE mode, then the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before entering data into the column and before enabling the primary key constraint of the table.
ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint.

So you would want to specify at the end of the constraint that it is ENABLE NOVALIDATE.  Here is an example
ALTER TABLE my_table ADD CONSTRAINT my_constraint UNIQUE (my_columns) ENABLE NOVALIDATE;

Apparently this alone will still throw an ORA-2299 because Oracle is still attempting to back the UNIQUE constraint with a UNIQUE index upon creation.  The most simple way to get around that is to create the (non unique) index first.
CREATE INDEX my_index ON my_table (my_columns);
ALTER TABLE my_table ADD CONSTRAINT my_constraint UNIQUE (my_columns) ENABLE NOVALIDATE;


How to disable "Completion time:..." in SQL Server Messages window

In SSMS 18.3, they added the option to turn off the completion time.

Tools > Options > Query Execution > SQL Server > Advanced.

Uncheck the Show Completion Time checkbox.

Start a new query window and execute something...
No completion time after query finishes. Yay!


Amazon RDS Slow Query Log Does Not Show SQL Statement

Check various settings:
log_output = TABLE    # versus FILE
slow_query_log = ON
# slow_query_log_file = (fullpath to some file)
long_query_time = 1
log_slow_admin_statements = ON       # optional
log_queries_not_using_indexes = OFF  # optional


Insert to SQL Azure through Linked Server very slow

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes

Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).

Another alternative for ad-hoc needs is to build a string containing a single INSERT statement with multiple rows in the VALUES clause. The statement is then used with EXECUTE AT, for example:

-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';

-- Build a list of 1000 numbers to insert
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);

-- For debugging
PRINT @sql;

-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;

The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).

Note that EXECUTE ... AT requires RPC and RPC OUT enabled in the linked server options.

The VALUES clause for a plan INSERT statement is limited to 1000 elements. There are ways around that (a VALUES clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.


Can I programmatically generate reports that are emailed as Excel docs from SSRS?

Ini. My first thought here is that you could use a List control to iterate across Districts. SQL Server MVP Jes Borland has a great intorductory post on Lists here: http://jesborland.wordpress.com/2010/11/17/the-power-of-reporting-services-%E2%80%93-lists/

Inside the list control will be a rectangle. By setting the Page properties of that rectangle, you can create yours tabs for each District, dynamically setting the Page Name property to an expression that returns the District.

You could configure your dataset (ideally, a stored procedure) to take the Region as a parameter and then you can run this ONE report for each of your Regions and they will all work the same and keep you from having to create multiple copies of the same object.

Assuming I understood your question correctly, this should get you moving in the right direction.


How can we drop a constraint without losing the automatically created index?

Use KEEP INDEX.

drop table demo purge;
create table demo (id number, constraint demo_pk primary key (id));
alter table demo drop constraint demo_pk keep index;
select index_name from user_indexes where table_name = 'DEMO';

INDEX_NAME
------------------------------
DEMO_PK


Have you tried this one?

Case # 1

This SQL Query only check out in February month of 2014 of all type (that is Cat,dog,horse):

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28'
GROUP BY type
ORDER BY type;

Case # 2

This SQL Query check out the only type cat for the month of February 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28'
AND      type LIKE '%cat%'
GROUP BY type
ORDER BY type;

Case 3#

This SQL Query check out all type in the year 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-01-01'
                  AND '2014-12-31'
GROUP BY type
ORDER BY type;


Why is WITH NOLOCK running as Serializable

Lock hints are orthogonal to isolation level. While they address similar concerns, adding a lock hint does not change the isolation level. Your transaction will still be a 'serializable' transaction. Of course, the lock hint make the query operation itself violate the transaction serializability, but you are looking at a property of the transaction.


SQL Server 2005 encrypted database works on new PC without restore keys!

Yes, it is normal. The certificates and keys are part of you database.

CREATE CERTIFICATE my_certificate
ENCRYPTION BY PASSWORD = 'blahblah' ...;

CREATE SYMMETRIC KEY my_symmetric_key
... ENCRYPTION BY CERTIFICATE my_certificate;

Your posted code creates a symmetric key encrypted with a certificate encrypted with a password. The master key is not involved in any way. This arrangement requires you to provide the certificate decryption password in the session. Your application must provide it, or your stored procedures do.


Missing view privileges in USER_TAB_PRIVS

Unquoted identifiers in SQL are implicitly converted to upper case, so the statement create view remotetabletest creates the view named 'REMOTETABLETEST', not 'remotetabletest'.

In other words, the following should return the expected result:

select * from user_tab_privs where TABLE_NAME = 'REMOTETABLETEST'


trigger to delete records with same value from another table after inserting record in another table

Try this:

DELIMITER $$
CREATE TRIGGER tgr_1 AFTER INSERT ON abnormal
FOR EACH ROW
BEGIN
  DELETE FROM normal where id = NEW.id;
END $$


How to know what index is set on the table with command line in informix?

I think understand your problem.

The dbaccess break lines at the middle of the statement and the table name goes to line bellow...
Something like this (dbschema output)

create unique index "informix".ix1c_h_dbases_2012_06 on "informix"
    .h_dbases_2012_06 (seq,site_name) using btree  filtering

I consider two solutions 100% functional...

First suggestion

Parse the dbschema with awk to join the lines until found a semicolon.
This will give to you long lines...
Then you can work little more with awk/sed to get only the index and table name... not so hard.
But this probably will become a shell script.

$ dbschema -d dba  -t h_dbases_2012_06| awk '{printf $0} /;/{print ""}' | egrep "create.index"
create index "informix".ix10_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,pagwrites) using btree ;
create index "informix".ix11_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,total_sorts) using btree ;
create index "informix".ix12_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,isreads) using btree ;
create index "informix".ix13_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,iswrites) using btree ;
create index "informix".ix14_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,iscommits) using btree ;
create index "informix".ix15_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,net_write_mb) using btree ;
create index "informix".ix16_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,horini) using btree ;
create index "informix".ix1_h_dbases_2012_06 on "informix".h_dbases_2012_06     (seq) using btree ;
create index "informix".ix1b_h_dbases_2012_06 on "informix".h_dbases_2012_06     (seq,hostname) using btree ;
create index "informix".ix2_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,username) using btree ;
create index "informix".ix3_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,cpu_time) using btree ;
create index "informix".ix4_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,programa) using btree ;
create index "informix".ix5_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,tarefa) using btree ;
create index "informix".ix6_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,hostname) using btree ;
create index "informix".ix7_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,maxlog_kb) using btree ;
create index "informix".ix8_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,net_read_mb) using btree ;
create index "informix".ix9_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,pagereads) using btree ;

Second suggestion

Query the database.
For this , they should be a script and your user should have access to database with dbaccess

$ cat x.sh
dbaccess -e - <<EOF
database $1 ;
select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
  and t.tabname matches lower("$2*")
  and t.tabid > 99
order by 1,2
;
EOF

$ sh x.sh dba h_dbases_2014_04
database dba ;
Database selected.

select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
  and t.tabname matches lower("h_dbases_2014_04*")
  and t.tabid > 99
order by 1,2
;

table                idx

h_dbases_2014_04     ix10_h_dbases_2014_0
h_dbases_2014_04     ix11_h_dbases_2014_0
h_dbases_2014_04     ix12_h_dbases_2014_0
h_dbases_2014_04     ix13_h_dbases_2014_0
h_dbases_2014_04     ix14_h_dbases_2014_0
h_dbases_2014_04     ix15_h_dbases_2014_0
h_dbases_2014_04     ix16_h_dbases_2014_0
h_dbases_2014_04     ix1_h_dbases_2014_04
h_dbases_2014_04     ix1b_h_dbases_2014_0
h_dbases_2014_04     ix1c_h_dbases_2014_0
h_dbases_2014_04     ix2_h_dbases_2014_04
h_dbases_2014_04     ix3_h_dbases_2014_04
h_dbases_2014_04     ix4_h_dbases_2014_04
h_dbases_2014_04     ix5_h_dbases_2014_04
h_dbases_2014_04     ix6_h_dbases_2014_04
h_dbases_2014_04     ix7_h_dbases_2014_04
h_dbases_2014_04     ix8_h_dbases_2014_04
h_dbases_2014_04     ix9_h_dbases_2014_04

18 row(s) retrieved.

Database closed.


Ok, looks like I had to get rid of the pipe.  Using the following pg_dump command worked for me:
pg_dump -U username --format=c --file=filelocation.sqlc dbname


If I'm reading you right, the function of Next_Id is to group the entries into ordered sets.  The way you've done it is the way an old fashioned network database would have done it.  Each next_id "points to" another entry.

The relational model groups items into (unordered) sets by putting a common value in a foreign key.  I don't have a model for your data, so I'll take a blind stab at a design.  We use the Id of the first entry in the set to identify the group membership.  The first entry doesn't have a null, instead, it points to itself.

Now, the table looks like this:

Id       Table_Name         Table_Id        Set_Id
1        Login              1               1
2        Logout             10              1
3        Close              2               1
4        Open               3               1
5        Login              4               5
6        Login              3               6
7        Logout             4               6
8        Close              9               6
9        Login              2               9
10       Logout             3               9

(I fixed the presumed bug in row 9).

This offers one advantage at storage time:  The set membership is known when a new value is inserted.  The next_id is not known, because the next event hasn't happened yet.  This means you only have to store it once, instead of storing it once and then updating it when the Next _id changes from NULL to something else.

It also offers an advantage at retrieval time.  Once you figured out the Set_id you are looking for, you can get all the members with a single trip to the database,  using something like this:

select *
from mytable
where set_id = somevalue
order by Id

I have guessed at the correct order for the retrieved set, based on the idea that later events will have a higher id.  But I don't know that that's true in your case.

If this select runs too slow, you could consider adding an index on Set_id.

A better solution would require knowing more about your data and your process.  Hope this helps.


Execution plan using B+ tree index, but also sorts

Oracle says about Indexes and Index-Organized Tables under Full Index Scan: In a full index scan, the database reads the entire index in order.

Yet, unter Fast Full Index Scan, it reads: A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order. (Emphasis mine)

Now, probably, the question should be: why did the optimzier choose INDEX FAST FULL SCAN over INDEX FULL SCAN.

A hint to the answer of the latter question is given in  under 11.2.3.7 Fast Full Index Scans:  A fast full scan is faster than a normal full index scan because it can use multiblock I/O and can run in parallel just like a table scan.

If you insist that Oracle use a full index scan, you might want to try the /*+ index() */ hint:

create table tq84_foo (
  fooID number not null
);

create table tq84_bar (
  fooID number not null
);

create unique index ix_foo on tq84_foo(fooID);
create unique index ix_bar on tq84_bar(fooID);

explain plan for
 select /*+ index(f ix_foo) */fooID from tq84_foo f
        MINUS
select /*+ index(b ix_bar) */ fooID from tq84_bar b;

select * from table(dbms_xplan.display);

resulting in

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    26 |     4  (75)| 00:00:01 |
|   1 |  MINUS              |        |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|        |     1 |    13 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | IX_FOO |     1 |    13 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|        |     1 |    13 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | IX_BAR |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------


Change all databases in "RESTORING" state to ONLINE

You need to use the WITH RECOVERY option, this seems likely caused by the restore script adding the WITH NO RECOVERY parameter to make the database ready for a transaction log apply after the restore. With your database RESTORE command, to bring your database online as part of the restore process.
This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

Your command should look like this,

**RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' WITH REPLACE, RECOVERY, STATS = 10**

You may have more success using the restore database wizard in SQL Server Management Studio (SSMS). This way you can select the specific file locations, the overwrite option, and the WITH Recovery option. Sometimes, The restoration process stuck just because of size of the database file.

You can either Use Below one:

How to Change the state from RESTORING To ONLINE

The following will go through each of the lines returned by the query building a RESTORE DATABASE statement around the name, using WITH RECOVERY at the end. The statement is then shown in the messages window which you can then use as needed. We'll make use of the state value to restrict the statement to only altering those that are not at state 1.

DECLARE @SetWithRecovery nvarchar(max) = ''

SELECT @SetWithRecovery += 'RESTORE DATABASE ' + quotename(Name) + ' WITH RECOVERY; ' + Char(10)
FROM sys.databases
WHERE [State] = 1

PRINT @SetWithRecovery

--If you wish to execute it as well

EXEC (@SetWithRecovery)

When you execute the code you'll receive a message for each restore statement showing the processing information.

Before you do this, please make sure you understand the options what you has to do, if not it may cause data loss.

  Check this link if needed:
  https://devondba.blogspot.com/2014/11/how-to-change-sql-database-from.html


Why can't I update or delete certain records in SQL Server?

Few Quick Possibilities:

Bad Statistics. Seems unlikely on a table under millions of rows, but technically possible. Try running UPDATE STATISTICS . If this does work look into why your statistics are getting out of whack and what you can do to mitigate the problem.
Lack of a good Clustered Index. This can be related to #1, if your CI is bad it can slow down UPDATEs/DELETEs as the CI needs to be searched and adjusted during such changes, but an INSERT may just be adding to the end of the CI.
Locks are being held too long over some sections of the table via other processes. Other transactions may be holding a lock on the table for excessive amounts of time, this may also be visible as deadlocking if you have the appropriate flags turned on for the SQL Logs.

Manually grabbing the query plan and taking a look at that may also help you figure out what is going on.


Fastest way to sync (or keep import) 3.5TB data from hadoop to sharded mongodb cluster

Why would you want to copy TB's of data from one environment to another system. There are rich projects available such as Apache Drill (to name few) which can seamlessly access Mongo for querying requirements. Have you explored that option?

When you say you have TB's of data in HDFS, what is the format of the data because this is a critical question to be aware of


You ARE getting row locks!

See Understanding Locking in SQL Server to understand why a ROWLOCK update must acquire hierarchical intent locks, for an explanation of the Process 54 acquiring IU lock on PAGE: 16:1:196 (class bit0 ref1) result: OK.


Table-valued Data in Common Table Expression

No, SQL Server only currently supports table-valued constructors in a derived table (or the VALUES clause of an INSERT ... VALUES statement). So, you can use VALUES in a join (including apply) or derived table ("subquery") but not a Common Table Expression.

Sadly, T-SQL is lagging in several areas compared with other popular SQL databases, for example Add support for ANSI standard row value constructors.


Upgrading MongoDB ReplicaSet to 3.0 causes BSONElement: bad type 108

Found the answer:

In 3.2 WiredTiger is the default storage engine. So I had to remove existing databases (/var/lib/mongod). After starting mongod it will replicate from the primary member.

I accidently updeted directly to 3.2.9 and it worked. That was the reason that WiredTiger was default engine.


Need some recommendations on organizing data

Using a surrogate key as a primary key for tables is a very common approach and it seems to fit your situation quite well. The foreign key in local maxima can then be a single column referencing the surrogate key.

If the relationship between the definition of the run and the outcome of the run is a one-to-one relationship, then you should at first consider having them in the same table, and only split them up if you feel it's more natural, convenient, or better for performance.


Best Way to design a table with different types of attachment

You are probably over-complicating matters by using separate tables for message, message_text and location. Just merge them into one table:

message
-id
-type
-sender_id
-body
-latitude
-longitude

If the columns may not have values, be sure to set them to NULLable in the table definition.

There are various valid reasons for using one to one relationships, for example for example if you expect to have a lot of records with shorter fields in the primary table but then only occasionally need records with longer fields in the secondary table but in most cases they are not necessary and only serve to complicate your design which will require more joins it otherwise would not.

From the general tone of your question you seem to be new to database design and I doubt you really want to do this. Keep everything about the message in one table and then you can inherently only have one location, one body, etc.

IF you wanted to create a one-to-one relationship the usual way you would go about this would be to make the primary key on the secondary table also a foreign key referencing the primary table. So both tables would have the same primary key value for a given record- this would ensure you could only have one.

https://www.mkyong.com/mysql/how-to-define-one-to-one-relationship-in-mysql/

As it is now, you could have multiple records in these tables, a one-to-many relationship.

But I would strongly suggest you just merge into one table.

Your attachment and image tables look fine, that will be a one-to-many relationship with separate primary keys and linked using a foreign key.


Fetch the newest row grouped by a column in MySQL

Create a Subquery that Gathers Keys from the the log_table with Maximum Date Per Entity. Then, perform an INNER JOIN of that Subquery back to the log_table.

SELECT
    B.entity_id,B.last_log_date,B.last_comment
FROM
(
    SELECT entity_id,MAX(last_log_date) last_log_date
    FROM log_table GROUP BY entity_id
) A INNER JOIN B USING (entity_id,last_log_date);

Give it a Try !!!

You can really speed this up if you have a compound index like this

ALTER TABLE log_table ADD INDEX entity_date_ndx (entity_id,last_log_date);

Indexes on each separate column may yield index merges. This compound index will bypass that.

Please try LEFT JOIN instead of INNER JOIN

SELECT
    B.entity_id,B.last_log_date,B.last_comment
FROM
(
    SELECT entity_id,MAX(last_log_date) last_log_date
    FROM log_table GROUP BY entity_id
) A LEFT JOIN B USING (entity_id,last_log_date);


Mongodb: how to create multiple indexes on same field?

Sadly, a restriction of MongoDB:

Restrictions
In MongoDB, you cannot create multiple versions of an index that
differ only in the options. As such, you cannot create multiple
partial indexes that differ only by the filter expression.

Right now, you can only do this:
db.test.createIndex({field1:1}, {partialFilterExpression:{field2:{$gt:10,$lte:10}}})

Or use composite indexes with a placeholder field:
db.test.createIndex({field1:1, never_use_this_field1: 1}, {partialFilterExpression:{field2:{$gt:10}}})
db.test.createIndex({field1:1, never_use_this_field2: 1}, {partialFilterExpression:{field2:{$lte:10}}}


Splitting a deck of cards, and returning the results as if they were shuffled once

I wanted to provide an alternative solution (using the sample data in Serpiton's answer - thanks!) that avoids sorts. This ought to be possible using ROW_NUMBER, but the query optimizer doesn't currently recognize its projection as unique. So:

DECLARE @TopHalf AS TABLE
(
    id integer IDENTITY (1, 1) PRIMARY KEY,
    CardID integer NOT NULL
);

DECLARE @BottomHalf AS TABLE
(
    id integer IDENTITY (0,1) PRIMARY KEY,
    CardID integer NOT NULL
);

INSERT TOP (26) @TopHalf (CardID)
SELECT D.id
FROM dbo.deck AS D
ORDER BY D.id ASC;

INSERT TOP (26) @BottomHalf (CardID)
SELECT D.id
FROM dbo.deck AS D
ORDER BY D.id DESC;

SELECT
    D.id,
    D.[card]
FROM
(
    SELECT id, CardID FROM @TopHalf AS TH
    UNION
    SELECT id, CardID FROM @BottomHalf AS BH
) AS Shuffled
JOIN dbo.deck AS D
    ON D.id = Shuffled.CardID
ORDER BY
    Shuffled.id,
    Shuffled.CardID;

SQLFiddle

Output:

Execution plans:


Update statement with strange side effects to data

In the select, you're using a different table - dbo.phone_Copy. The update is running on dbo.phone.


Unable to Generate Scripts for Publications

DECLARE @distributor sysname

DECLARE @distributiondb sysname
DECLARE @distproc NVARCHAR (255)
DECLARE @publisher sysname

SELECT @publisher = @@SERVERNAME

EXEC sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distributiondb OUTPUT

select @distributor, @distributiondb

SELECT @distproc = QUOTENAME (@distributor) + '. ' + QUOTENAME (@distributiondb) + '.dbo.sp_MSenumdistributionagentproperties'

select @distproc ---what do you get here?

---Line 537
EXEC @distproc @publisher = @publisher,
@publisher_db = @dbname,
@publication = @orig_publication,
@show_security = 1

The exec @distproc section is roughly at Line 537 of sp_MSrepl_helpsubscription. It's building the proc call and should be something like [SeverName].distribution.dbo.sp_MSenumdistributionagentproperties

From the error,  it appears that @distproc variable is not getting set properly, or at least getting set to an empty string.

Why? That's hard to know without more information or being able to test on your system. Some unusual setup with the distributor, perhaps? But this will hopefully point you in the right direction so you can walk through it. (Run that code on the Database that is being published).


Mongo Zone Sharding Implementation Issue

Problem here is your replSetName: parameters.

BOTH config servers must be at same replica set, f.ex. conf and then those shards (data nodes) both should have different replica set names. f.ex. rs0 and rs1.

Now your config1 is rs0 and config2 is rs1.


How to resynchronize replication for a single database

It depends a bit on your setup, there are different ways to archive what you want. I will describe a very basic one which only uses MySQL tools (and some standart Linux tools).

First of all you have to make sure a few things:

It can be guaranteed that the databases which you want ro resync will have no writes or other DML or DDL statements issued against while you perform the resync. E.g. you are able to revoke privileges for these database users.
You are familiar with backup and restore using mysql tools :)

Now the procedure:

Ensure that there will be no access on the databases which you want to resync, easiest way to do this is to revoke all priviliges for users which have access to this database. A possible way to find those users would be querying mysql.db table.
Now you have your three schemas and there will be no changes to it, it means it will be on a consistent state until you have finished your operations on it. You can now safely dump only there required databases using mysqldump. Please refer here to the mysqldump docs. If you are unsure just ask.
Copy your dumps to your slave machine and restore your three databases there using these dumps. Beforehand make sure that all binary logs are applied to the slave which might contain changes to these databases (make sure replication has catched up and there is no lag or something).
After the restore your databases will be identical on the master and the slave. It is now safe to grant back the permissions to the users on the master, as soon as there are changes on the master they will be replicated to the slave.

Keep in mind that there are different approaches for restoring failed slave databases, above is one of them, so maybe some additional research would be a good idea. If you have questions to particular points of my description don´t hesitate to ask, I will then edit my answer.


Linux sqlcmd connecting to Windows Sql Server using kerberos

Try this guide: Tutorial: Use Active Directory authentication with SQL Server on Linux

And then go to point:
Connect to SQL Server using AD Authentication

Basically you will need to ssh to the client using your AD credentials and the run sqlcmd without credentials

ssh -l user@contoso.com client.contoso.com
sqlcmd -S mssql-host.contoso.com


Preloading a Java library in DB2, similar to ORACLE's loadjava

Ok, what I believe you are after is creating Java routines (ie, stored procedures implemented in Java and stored within DB2).

You will want to follow the documentation in this section on Java routines. Pay special attention to the Creating, Building, and Deploying sections.

And even though this is an older article, you may wish to check out this article "Solve common problems with DB2 UDB Java stored procedures" on developerworks. It also discusses the when/where of using Java routines in DB2, how you go about creating them, and common problems encountered.


Data is present in data folder of MySQL which is usually in ProgramData for windows installations. look into "C:\ProgramData\MySQL\MySQL Server 5.6"..as programdata usually have the files on by default


The simplest way is to create a formula in the additional column.
This formula has to make a correct INSERT statement. E.g.:

="INSERT INTO MYTABLE (field1, field2) VALUES ('" & A1 & "', '" & B1 & "');"

Then fill in the whole column with this formula
(A1 and B1 become A2 and B2, A3 and B3, etc.)

Select the whole column and copy to clipboard,
then activate the terminal and insert from clipboard.


Should I set document version field to be part of a primary key

The DocumentVersion table makes sense to me.  The two columns well define the Document by the DocumentID relationship to the Primary Key on Document and the Version makes a unique entry for DocumentVersion.  (Just as the name implies.)

There is no problem having a primary key that included two different data types, so an INT and a DECIMAL(5,2) will coexist happily in the same Primary Key.

What you do want to avoid is having a join between mismatched data types, such as joining a VARCHAR(10) with an INT. This will likely result in problems with the join performance even when all the data is clean.  (And, of course, a VARCHAR can hold strings that would not convert to INT which would be another class of problem.)

EDIT: Depending upon your usage, a surrogate PK column DocumentVersionID could be beneficial for other tables to reference.  However, for integrity of tracking the versions, the DocumentID,Version should at least be enforced by a UNIQUE CONSTRAINT.

E.g.  ... CONSTRAINT Unique_DocumentVersion UNIQUE (DocumentID, Version);


Re-phrasing, do you mean:

  don't allow Sales or Shipping staff values in restock?

Then the foreign key is from Restock to R&D_Staff to allow only values from RnD staff in the Restock table


How to correctly return a query result only if it isn't NULL?

I don't think you need a cursor here at all. To shorten your code, you could just use a view. To improve performance, a materialized view should get you furthest. Postgres 9.3 has built-in features, but you can easily implemented it in older versions yourself.

Consider this simplified form:

CREATE FUNCTION store_distance(_lat    double precision
                              ,_long   double precision
                              ,_radius double precision
                              ,_tries  integer)
RETURNS TABLE(
   store_id   store.id%type
  ,store_name store.name%type
  ,distance   double precision) AS
$func$
DECLARE
   _ct  int   := 0;
   _pos point := point(_lat, _long);
BEGIN
   LOOP
      EXIT WHEN _ct >= _tries
           OR   EXISTS (
            SELECT 1 FROM store s
            WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius));

      _radius := _radius * 2;
      _ct     := _ct + 1;
   END LOOP;

   RETURN QUERY
   SELECT s.id, s.name
         ,get_distance(_lat, _long, s.latitude, s.longitude)
   FROM   store s
   WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius);
   ORDER  BY 3;
END
$func$ LANGUAGE plpgsql STRICT;

I made the function STRICT to disallow NULL input, which could result in an endless loop.

Note how I use circles with the "Contained" operator <@ instead of a boxes. One would assume calculations to be slightly more expensive than with boxes, but it hardly matters at all once you support your query with a GiST index like:

CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));

You might consider to store lat / lon as point to begin with and replace the index on an expression with a simpler one on the column. Works either way, just make sure the query matches the index so it gets used. Big difference for big tables.

You may be interested in this closely related answer on SO I posted last year - with a lot more explanation and links.


Wrong result for FileTableRootPath Function of SQL Server

Below is a T-SQL example from the documentation:

ALTER DATABASE database_name
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'directory_name' );

Note the database must be locked exclusively for this operation.


Process to map a transaction table to a status table

The problem with a sequence is that you have to specify the top end of the range and that's unpredictable in your scenario.  But you could track it on your own without having to worry about caching. Be sure to index and check as you see fit.

CODE:

--CREATE THE JOIN TABLE
IF OBJECT_ID ('dbo.PERSONS_TRANSACTION','U') IS NULL
    CREATE TABLE PERSONS_TRANSACTION
    (
          SEQUENCE_DATE    DATE
        , SEQNO            INT
        , PERSON_ID        INT
        , TRANSACTION_ID   INT
    );
GO

--CREATE A PROC TO PERFORM INSERTS
IF OBJECT_ID ('dbo.usp_Insert_PERSONS_TRANSACTION','P') IS NULL
    EXEC sp_executesql N'CREATE PROC dbo.usp_Insert_PERSONS_TRANSACTION AS SELECT 1 STUB;';
GO

ALTER PROC dbo.usp_Insert_PERSONS_TRANSACTION  (@personID INT,@transactionID INT)
AS
    DECLARE
          @today  DATE
        , @maxSeq INT;

    --GET THE DATE
    SET @today = CONVERT(DATE,GETDATE());
    --PRINT @today

    --GET THE LAST SEQ NUMBER (NOT A TRUE SEQUENCE)
    SELECT @maxSeq = MAX(SEQNO)
    FROM PERSONS_TRANSACTION
    WHERE SEQUENCE_DATE = @today;
    --PRINT @maxSeq

    --IF THERE IS NO SEQENCE NUMBER IT HASN'T RESET YET, SO THE SEQUENCE NUMBER IS 1, INSERT
    IF @maxSeq IS NULL
    BEGIN
        INSERT PERSONS_TRANSACTION
        VALUES (@today,1,@personID,@transactionID);
    END
    --IF THERE IS A SEQUENCE NUMBER, ADD 1 AND INSERT
    ELSE
    BEGIN
        SET @maxSeq +=1;
        INSERT PERSONS_TRANSACTION
        VALUES (@today,@maxSeq,@personID,@transactionID);
    END;
GO

--ADD SOME TEST RECORDS
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=1,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=2,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=1,@personID=2;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=2,@personID=2;

--MOVE THE DATE BACK A DAY TO TEST RESET
UPDATE PERSONS_TRANSACTION
    SET SEQUENCE_DATE = '2017-09-24';

--ADD SOME MORE RECORDS
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=3,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=4,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=3,@personID=2;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=4,@personID=2;

SELECT * FROM PERSONS_TRANSACTION;

--DROP TABLE PERSONS_TRANSACTION;
--DROP PROC usp_Insert_PERSONS_TRANSACTION;


MySQL 5.6 Aborted_connects increase without error log

Aborted_connects/Connections is nearly 1.  This is extremely high.

Threads_connected is always 1 or more because "1" is the connection that is doing the SHOW STATUS.  Max_used_connections = 2 probably does mean that two connections were connected at some time.  It is a high-water-mark.


odbc connection from windows machine to mysql server running on ubuntu?

It isn't possible as SQL Server Native Client is a client library for SQL Server, not for MySQL. If you want to access a MySQL server through ODBC, then you need the MySQL ODBC driver (Connector/ODBC).


Calculating stock quantity based on change log

Sometimes you can improve query performance just by doing a little bit of tuning instead of changing your entire query. I noticed in your actual query plan that your query spills to tempdb in three places. Here's one example:

Resolving those tempdb spills may improve performance. If Quantity is always non-negative then you can replace UNION with UNION ALL which will likely change the hash union operator to something else that doesn't require a memory grant. Your other tempdb spills are caused by issues with cardinality estimation. You're on SQL Server 2014 and using the new CE so it may be difficult to improve the cardinality estimates because the query optimizer won't use multi-column statistics. As a quick fix, consider using the MIN_MEMORY_GRANT query hint made available in SQL Server 2014 SP2. Your query's memory grant is only 49104 KB and the max available grant is 5054840 KB so hopefully bumping it up won't impact concurrency too much. 10% is a reasonable starting guess but you may need to adjust it up and done depending on your hardware and data. Putting that all together, this is what your query might look like:

WITH t AS
(
    SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY ToPositionId, ProductId
    UNION ALL
    SELECT FromPositionId AS PositionId, -SUM(Quantity) AS Quantity, ProductId
    FROM ProductPositionLog
    GROUP BY FromPositionId, ProductId
)

SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
WHERE NOT t.PositionId = 0
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0
OPTION (MIN_GRANT_PERCENT = 10);

If you wish to improve performance further I recommend trying out indexed views instead of building and maintaining your own checkpoint table. Indexed views are significantly easier to get right than a custom solution involving your own materialized table or triggers. They will add a small amount of overhead to all DML operations but it may allow you to remove some of the nonclustered indexes that you currently have. Indexed views appear to be supported in the web edition of the product.

There are some restrictions on indexed views so you'll need to create a pair of them. Below is an example implementation, along with the fake data that I used for testing:

CREATE TABLE dbo.ProductPositionLog (
    LogId BIGINT NOT NULL,
    ProductId BIGINT NOT NULL,
    FromPositionId BIGINT NOT NULL,
    ToPositionId BIGINT NOT NULL,
    Quantity INT NOT NULL,
    FILLER VARCHAR(20),
    PRIMARY KEY (LogId)
);

INSERT INTO dbo.ProductPositionLog WITH (TABLOCK)
SELECT RN, RN % 100, RN % 3999, 3998 - (RN % 3999), RN % 10, REPLICATE('Z', 20)
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

CREATE INDEX NCI1 ON dbo.ProductPositionLog (ToPositionId, ProductId) INCLUDE (Quantity);
CREATE INDEX NCI2 ON dbo.ProductPositionLog (FromPositionId, ProductId) INCLUDE (Quantity);

GO

CREATE VIEW ProductPositionLog_1
WITH SCHEMABINDING
AS
   SELECT ToPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
    FROM dbo.ProductPositionLog
    WHERE ToPositionId <> 0
    GROUP BY ToPositionId, ProductId
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1
    ON ProductPositionLog_1 (PositionId, ProductId);
GO

CREATE VIEW ProductPositionLog_2
WITH SCHEMABINDING
AS
   SELECT FromPositionId AS PositionId, SUM(Quantity) AS Quantity, ProductId, COUNT_BIG(*) CNT
    FROM dbo.ProductPositionLog
    WHERE FromPositionId <> 0
    GROUP BY FromPositionId, ProductId
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V2
    ON ProductPositionLog_2 (PositionId, ProductId);
GO

Without the indexed views the query takes about 2.7 seconds to finish on my machine. I get a similar plan to yours except mine runs in serial:

I believe that you'll need to query the indexed views with the NOEXPAND hint because you aren't on enterprise edition. Here's one way to do that:

WITH t AS
(
    SELECT PositionId, Quantity, ProductId
    FROM ProductPositionLog_1 WITH (NOEXPAND)
    UNION ALL
    SELECT PositionId, Quantity, ProductId
    FROM ProductPositionLog_2 WITH (NOEXPAND)
)
SELECT t.ProductId, t.PositionId, SUM(t.Quantity) AS Quantity
FROM t
GROUP BY t.ProductId, t.PositionId
HAVING SUM(t.Quantity) > 0;

This query has a simpler plan and finishes in under 400 ms on my machine:

The best part is that you won't have to change any of the application code that loads data into the ProductPositionLog table. You simply need to verify that the DML overhead of the pair of indexed views is acceptable.


MS SQL Row-Level-Security out of a Citirx Store Front

The only way you have here is to use INTEGRATED SECURITY in the connection string.
In this way the connecting login is also used against the database but if there is a firewall between database and frontend that recognize windows authentication (it filters in application layer i suppose) every users need to be included in the allow rule. Usually, it is easily done using domain groups.


How significant is performance difference between "DATE(mydate) = CURDATE()" vs "mydate = CURDATE()"?

You just asked

  How significant is performance difference between “DATE(mydate) = CURDATE()” vs “mydate = CURDATE()”?

My Answer

  Very Gigantic Difference

When you use

DATE(mydate) = CURDATE()

The Query Optimizer will suggest a full table scan even if the column is indexed. Why ? The Query Optimizer is obligated to see the need to call the DATE() function against mydate in every row, thus eliminating the idea of using the index. Additionally, using FORCE INDEX will simply get you nowhere.

If mydate is datetime and is indexed, you could then change the query to

mydate >= (CURDATE() + INTERVAL 0 SECOND) AND
mydate <  (CURDATE() + INTERVAL 86400 SECOND)

or

mydate >= (CURDATE() + INTERVAL 0 SECOND) AND
mydate <  (CURDATE() + INTERVAL 1 DAY + INTERVAL 0 SECOND)

so you are comparing datetime to datetime and isolating just today. In this instance, an index on mydate will be used.

You do not want to split the date and time into spearate columns as that will cause comparisons of datetime ranges across multiple dates and times very difficult to express in SQL.


Cursor-SQL Help- Need another Column for Dynamic SQL

It appears that you are using SQL Server, SQL Server uses T-SQL while Oracle uses PL/SQL.

This query should give you the results you need without using a cursor. char(10) and char(13) are used for line feed & carriage return, QUOTENAME() to get the square brackets around your objects.

SET NOCOUNT ON;

SELECT 'SET IDENTITY_INSERT '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' ON;' + CHAR(10)+CHAR(13)
        +'INSERT into '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' ('+ QUOTENAME(I1.COLUMN_NAME)+', '+QUOTENAME(I2.COLUMN_NAME)+')' + CHAR(10)+CHAR(13)
        +'VALUES(-1, ''Unknown'');' + CHAR(10)+CHAR(13)
        +'SET IDENTITY_INSERT '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' OFF;' + CHAR(10)+CHAR(13)
        +'GO'
FROM INFORMATION_SCHEMA.COLUMNS I1
LEFT JOIN INFORMATION_SCHEMA.COLUMNS I2
on object_id(I1.TABLE_NAME) = object_id(I2.TABLE_NAME)
AND SCHEMA_ID(I1.TABLE_SCHEMA) = SCHEMA_ID(I2.TABLE_SCHEMA) AND I2.ORDINAL_POSITION = 2
WHERE I1.TABLE_SCHEMA = 'dbo' AND I1.TABLE_NAME LIKE '%LU'
AND COLUMNPROPERTY(object_id(I1.TABLE_NAME), I1.COLUMN_NAME, 'IsIdentity') = 1
ORDER BY I1.TABLE_NAME;

In SSMS, you could select Results to text:

And then execute the script, giving you the formatted results:

Test tables created

CREATE TABLE [dbo].[PREFIXLU] ([id] int identity(1,1), [val] varchar(255));
CREATE TABLE [dbo].[LUSUFFIX] ([id] int identity(1,1), [val] varchar(255));
CREATE TABLE [dbo].[LU] ([id] int identity(1,1), [val] varchar(255));

From these three tables, two tables, PREFIXLU and LU should be returned by the script.

I would really check and make sure that  ORDINAL_POSITION = 2 is 100% true for all the tables with LU as a suffix.

Result:

SET IDENTITY_INSERT [dbo].[LU] ON;

INSERT into [dbo].[LU] ([id], [val])

VALUES(-1, 'Unknown');

SET IDENTITY_INSERT [dbo].[LU] OFF;

GO
SET IDENTITY_INSERT [dbo].[PREFIXLU] ON;

INSERT into [dbo].[PREFIXLU] ([id], [val])

VALUES(-1, 'Unknown');

SET IDENTITY_INSERT [dbo].[PREFIXLU] OFF;

GO

  (1 row affected)

  (1 row affected)


Is there a way I can shorten the below query into a single query?

Yes you should be able to do it, with something like this:

SELECT r.Name,
       r.NoOfCountries,
       sum(case when ig.Name IN ('High income: nonOECD', 'High income: OECD') then 1 else 0 end) AS HIGH,
       sum(case when ig.Name IN ('Lower middle income', 'Low income') then 1 else 0 end) AS LOW,
       sum(case when ig.Name = 'Upper middle income' then 1 else 0 end) AS mid
FROM   census.Country c
       INNER JOIN census.Region r
            ON  c.fRegionID = r.RegionID
       INNER JOIN census.IncomeGroup ig
            ON  c.fIncomeGroupID = ig.IncomeGroupID
group by
    r.Name,
    r.NoOfCountries


MySQL's query optimizer is not very smart, anything you can do to help it will go a long way to improving your query performance.

Couple of issues I see at a glance without being able to see an execution plan (please edit your question with the output of explain select * from table...:

Your first join is going from an indexed column on your left table (product_categories.product_id) to a non-indexed column of your right table (orders_products.product_id). With no where clause, that's going to result in a very heavy nested loop through every record on the orders_product for each row in your product_categories table. To speed it up you could add an index to orders_products.product_id, ideally you would also limit the left table using some sort of criteria in a where clause.
Your second join is doing a similar mistake. Your left table column is not indexed while your right column is a PK. I think you're probably signing up for another long queue of nested loops through each of the tables.

The fact that you're left joining also means that regardless of what's in your right table, you're going to have to inspect every element in your left table. Inner joins can be more performant, but without indexing your columns properly and without the use of some kind of where clause criteria to help limit the rowsets you have to inspect, this is going to be a heavy query.

In summary:

index your join columns
use inner joins where possible
use limiting criteria in your where clause if possible ti reduce the amount of index scanning needed.


SQL Server Database Diagram in SQL Management Studio creating multiple schemas

Database Diagrams is one of three Visual database tools.

After reading trough Visual Database Tools F1 Help, where there is no mention of choosing schema before creating a new table, which confirms that it automatically creates the table in the dbo schema.

To overcome this and still use Database Diagrams open your Table Properties window.

Create new schema:

Name your schema, define permissions etc.

Change schema for your table while in design view using Properties window.

For me this approach is still faster that writing scripts, i didn't change to different designer tool and it works.

Hope it helps somebody.


Is it possible to have partition in SQL Server 2016 with both clustered and non-clustered indexes on different partitions?

I don't think it's possible. The notion seems to fly in the face of everything that we know about partitioning and clustered indexes, but I might be wrong.

One possibility is a partitioned view over two tables, one with a clustered index, one as a heap. I have included a demo script for this, but it does come with a number of limitations which might be critical:

inserts seem to need to explicitly insert into each column
the heap table still needs a primary key which includes the partitioning column (even if the primary key is not clustered)
moving data from 'recent' to 'archive' involves moving from one table to another

Overall your situation is probably complex enough that it needs a much more considered analysis.

Use tempdb;

Create Table TestOld (
    id int,
    partition_column char(1) Constraint chk_partition_old Check (partition_column = 'O'),
    other_column char(100),
    Constraint pTestOld Primary Key Clustered (partition_column, id));

Create Table TestNew (
    id int,
    partition_column char(1) Constraint chk_partition_new Check (partition_column = 'N'),
    other_column char(100),
    Constraint pTestNew Primary Key Nonclustered (partition_column, id));
Go

Create View Test As
Select id, partition_column, other_column
  From TestOld
Union All
Select id, partition_column, other_column
  From TestNew;
Go

Insert Into Test (id, partition_column, other_column)
Values  (1, 'O', ''),
        (2, 'N', '');

Select *
  From Test
  Where partition_column = 'N';

A second option which may have more merit in your case is a filtered index which you could add over the old part of your table and include a large number of columns so that it covers the queries that you do on the old part of the table (this would serve to speed up reads to the old part of the table but wouldn't slow writes in the recent part).


Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)?

In short, neither MySQL or MariaDB do. They do use R-Trees so it doesn't sound impossible to implement. I haven't seen this feature request in either of their bug trackers.


You need a hierarchical or recursive query, like

with recursive
 startwith(val) as (
  select 2
  union select 5
 ), AccountTree AS (
   SELECT
      id,
      ParentId,
      id as tree_start,
      name
    FROM ChartOfAccounts
    WHERE id in (select val from startwith)
   UNION
   SELECT
      ca.id,
      ca.ParentId,
      s.tree_start,
      ca.name
     FROM ChartOfAccounts ca
     INNER JOIN AccountTree s ON s.id = ca.ParentId
)
SELECT tree_start, sum(t.amount)
  FROM AccountTree at
  left outer join Transactions t on t.AccountId = at.id
  group by tree_start

See this DB Fiddle


Computed Columns Or Views - Database Table Design

You should store any value using its ISO measure unit. Calculations and conversions should be done at the client side. Don't expect any performance gain over client side conversion when using computed columns.

As far as passing in data in any measure unit, I advise against that design: make the client pass in the data in the ISO measure unit and do any conversion on the client.

If you insist passing data in any unit, this is how an INSTEAD OF trigger could work:

CREATE TABLE length_values (
    id int PRIMARY KEY,
    length_m decimal(21,6)
)
GO

CREATE VIEW v_length_values
AS
SELECT
    id,
    length_m,
    (length_m * 1000) AS length_mm,
    (length_m / 1000) AS length_km
FROM length_values;
GO

CREATE TRIGGER TR_length_values ON v_length_values
INSTEAD OF INSERT, UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    MERGE INTO length_values AS trg
    USING INSERTED AS src
        ON trg.id = src.id
    WHEN MATCHED THEN
        UPDATE
        SET length_m = COALESCE(src.length_m, src.length_mm / 1000, src.length_km * 1000)
    WHEN NOT MATCHED THEN
        INSERT (id, length_m)
        VALUES (id, COALESCE(src.length_m, src.length_mm / 1000, src.length_km * 1000));

END

Some data to test:

INSERT INTO v_length_values (id, length_km) VALUES (1, 20);
INSERT INTO v_length_values (id, length_m) VALUES (2, 20);
INSERT INTO v_length_values (id, length_mm) VALUES (3, 1);

Results:

+----+--------------+-----------------+----------------+
| id |   length_m   |    length_mm    |   length_km    |
+----+--------------+-----------------+----------------+
|  1 | 20000.000000 | 20000000.000000 | 20.00000000000 |
|  2 | 20.000000    | 20000.000000    | 0.02000000000  |
|  3 | 0.001000     | 1.000000        | 0.00000100000  |
+----+--------------+-----------------+----------------+


After "ALTER TABLE MOVE PARTITION" new partitions are still created in the original tablespace

Moving existing partitions to a new tablespace does not change the default tablespace at the table level.

To do that:

alter table OWNER_PUBLIC.T_NO_CONNECT_CONTRACTS modify default attributes tablespace tablespaceB;

ALTER TABLE


With database mirroring, you have to create a snapshot of the database in order to run queries against it.

A few problems there:

Snapshots are an Enterprise Edition feature, which put it out of the price range of most shops
Running queries against the snapshot mean the server is in use, so you have to pay full Enterprise Edition pricing on it (it's not a free standby server at that point)
The queries are only up to date as of when the snapshot was taken
To refresh the snapshot, you have to drop it and take another snapshot (which means getting all users out of the snapshot to drop it)

Because of those, running queries against a mirror isn't usually cost-effective.

If you're willing to stomach the Enterprise Edition price tag, SQL 2012 added Always On Availability Groups, which let you read directly from the secondary with nearly real-time data at a much lower management cost. Still has the EE price tag, though - readable secondaries are an EE-only feature.

Generally speaking, performance tuning is easier than licensing more EE cores ($7k/core USD.)


The benefits of using a surrogate key (that's your newly created single field), in my opinion, outweigh the downsides in most cases.

But there are a few cases that you may experience some drawbacks.

Think of a company's employees that work different shifts on different days of the week. Here's a simplified database:

SHIFT(ShiftID, Shift, SomeOtherColumn);
EMPLOYEE(EmployeeID, FirstName, LastName);
[TRANSACTION](TransactionID, ShiftID, EmployeeID, Date);

So, as constructed, we've created a surrogate key for each table. If we need to, say, find out what shift correspond to a particular shiftID from a particular transactionID, we'd have to run a second query. That's the downside of using a surrogate key in that case.

If the fields Shift and SomeOtherColumn from the SHIFT table can be assumed to be a candidate key (a composite primary key, in other words), then we would have to declare both fields in the our FOREIGN KEYconstraint DDL when we created the [TRANSACTION] table. So, we would be able to directly query the actual Shift from the [TRANSACTION] table without having to run an additional query.

Another issue with using a catch-all surrogate key is that it has no intrinsic value. So, if you work for a larger organization, but there are different databases (in other departments, for examples) that refer to the same items with different primary keys (surrogate keys), you may have a bit of an issue if you have join their data regularly.

Ditto for the developer who has 2 or 3 apps, maintains a different user database for each, with a different surrogate ID for each user but may have a ton of duplicate users between them.

Personally, I go on a case by case basis, but lean toward using a surrogate key more unless there is an outstanding reason I should not.


Restrict access for Windows administrator

No.

If a user is a Windows Administrator of a box, assume that they own everything on the box (including SQL Server). With Windows Administrator rights it is trivial to bypass any targeted protection you apply (such as a logon trigger that identifies their user name), by impersonating someone else (including NT AUTHORITY\SYSTEM, which gets de facto admin rights on all local SQL Server instances). Auditing won't help much either, because they can easily turn that off, but you should have it just in case.

If you don't trust someone, don't give them Windows Administrator rights, period.


Can I set the maximum locking level that a transaction can acquire?

In the case of ACCESS SHARE, you could do that with
SET default_transaction_read_only = on;

That will cause an error as soon as anybody wants to take a lock higher than ACCESS SHARE.
But it seems that your real objective is to detect if a transaction takes ACCESS EXCLUSIVE locks. You could do that by querying the pg_locks table after the statements are done:
BEGIN;

/* run statements that lock objects */

SELECT locktype, relation::regclass, objid, classid::regclass
FROM pg_locks
WHERE mode = 'AccessExclusiveLock';

ROLLBACK;

You'd run that on a test database, and it should show you all relations (tables, indexes, sequences, materialized views, or views) and other objects (where classid is the name of the catalog for the object) on which the statements take an ACCESS EXCLUSIVE lock.


Meaning of 'show variables LIKE '%_database'

Show Variables is not ANSI SQL, it's MySQL specific, so it won't run across database providers.

Reading through that code it looks like it's trying to determine if the database provider will support UTF8.

If you Google it, the Show Variables is a MySQL command which returns information about the current database.

I think you will have to replace this whole section with different code to determine if the database in use will support UTF8, which I suspect is dependent on the collation.

You can find this information in the sys. tables.

Googling MSSQL Server and UTF8 seems to indicate that MS don't explicitly support UTF8, however.

This is an answer to your question "what is show variables", not a solution to your problem "how can my application run a query to determine UTF8 support in the database?"

HTH


Compare an hour between two hour columns

You cannot have an interval defined by an "end" smaller than your "start". Your shift 3 does not work because you're crossing day boundaries, and your "end" (6 o'clock, is smaller than your end 22 o'clock).

If you want to define your shifts by hours, you would need to have, in general, 2 intervals per shift, and shift 3 should have:

+----+--------------+------------+--------------+------------+
| id | start_hour_1 | end_hour_1 | start_hour_2 | end_hour_2 |
+----+--------------+------------+--------------+------------+
|  1 |     06:00:00 |   14:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  2 |     14:00:00 |   22:00:00 |              |            |
+----+--------------+------------+--------------+------------+
|  3 |     22:00:00 |   24:00:00 |    00:00:00  |   06:00:00 |
+----+--------------+------------+--------------+------------+

And you should rewrite your query with:

SELECT
    *
FROM
    Shifts S
WHERE
       (given_hour >= start_hour_1 AND given_hour < end_hour_1)
    OR (given_hour >= start_hour_2 AND given_hour < end_hour_2) ;

As a little advice, I would  use BETWEEN .. AND .. only with discrete variables (basically integers and texts). With continuous variables (and time is continuous), it's safer to include one of the ends and exclude the other (>= AND <):  given_hour >= start_time AND given_hour < end_time. That's why I changed :01 by :00 to work this way (and avoid problems with 14:00:00.123, which could eventually happen).


I can't connect to an instance of SSMS from other servers SSMS

Start by making sure that your Firewall isn't blocking incoming connections to port 1433 (if this is a default instance installation). Check both the Windows Firewall and also the settings on your network firewall ( if you have one configured ).

Also make sure that remote connections are enabled to the SQL server (in SSMS, right click the instance -> Properties -> Connections -> Allow Remote Connections to this server)


Postgresql: how to remove a GRANT on multiple tables?

The privileges are not automatically removed.  Let's consult the documentation, as always:

  dropping a role is often not just a matter of a quick DROP ROLE. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.

So, if the role owns any objects, you have to either transfer the ownership to someone else, or drop the object.  These can be done by

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;

The important fact is that DROP OWNED does not only the dropping:

  DROP OWNED also takes care of removing any privileges granted to the target role for objects that do not belong to it.  Because REASSIGN OWNED does not touch such objects, it's typically necessary to run both REASSIGN OWNED and DROP OWNED (in that order!) to fully remove the dependencies of a role to be dropped.

A limitation you have to observe is that DROP OWNED works only on the current database, and not on other databases that might also contain objects with privileges granted to the to-be-deleted role.


Is "Estimated Number of Rows" always per execution within SSMS execution plans?

Yes, this is the case - SSMS does not always show you the estimated rows * number of executions, and this can obviously lead to some inaccurate assumptions if you don't dig deeper as you did. But technically the answer to your question is no. Sometimes SSMS will get this estimate right, depending on the operator and other semantics.

In SentryOne Plan Explorer we try to compensate for this by showing SQL Server's rows * number of executions in several cases instead of just blindly copying the row count from the plan XML. Your plan, for example:


Delete from table where two of the three keys in another select

Your code is valid SQL but not in SQL-Server. They haven't yet implemented the syntax (a,b) IN ((1,11), (2,12), ...) or (a,b) IN (SELECT expa, expb....).

You can work around with either an EXISTS subquery:

DELETE FROM tableA AS a
WHERE EXISTS
      ( SELECT 1
        FROM tableA AS b
        WHERE b.IDA = a.IDA AND b.IDB = a.IDB
        HAVING COUNT(*) > 1
      )
  AND a.comment = 'temp' ;

or probably more efficiently with a join:

DELETE a
FROM tableA AS a
  JOIN
    ( SELECT
      FROM tableA
      GROUP BY IDA, IDB
      HAVING COUNT(*) > 1
   ) AS b
     ON  b.IDA = a.IDA AND b.IDB = a.IDB
WHERE a.comment = 'temp' ;

and even better with a derived table or a cte (no self-join):

WITH dups AS
  ( SELECT comment,
           cnt = COUNT(*) OVER (PARTITION BY IDA, IDB)
     FROM tableA
  )
DELETE
FROM dups
WHERE comment = 'temp'
  AND cnt > 1 ;


How to persistently set MySQL (Linux mysql command) session time zone to UTC?

Use default_time_zone=UTC in my.cnf.

Ref: https://stackoverflow.com/questions/4562456/mysql-setting-time-zone-in-my-cnf-options-file


This is an informational message for SQL Server stating successful connection. It is one of three that it will usually return. You can find more info here.


SELECT country,city,
       @country_rank :=
          IF(STRCMP(country,@current_country) = 0,  @country_rank + 1, 1)
          AS country_rank,
       @current_country := country  setctry
  FROM  cities,
        (select @current_country="", @country_rank=0) init
  ORDER BY country,sn  DESC

cfr. SQL Fiddle http://sqlfiddle.com/#!9/c388ef7/2


Fast hamming distance queries in postgres

Well, I spent a while looking at writing a custom postgres C extension, and wound up just writting a Cython database wrapper that maintains a BK-tree structure in memory.

Basically, it maintains a in-memory copy of the phash values from the database, and all updates to the database are replayed into the BK-tree.

It's all up on github here. It also has a LOT of unit-tests.

Querying across a dataset of 10 million hash values for items with a distance of 4 results in touching ~0.25%-0.5% of the values in the tree, and takes ~100 ms.


Why am I getting the An expression of non-boolean type specified in a context where a condition is expected, near ',' error

SQL SERVER doesn't support this.
You have to switch to a subwquery

CREATE TABLE mytable (Permit integer,Stampdate Datetime)
GO

INSERT INTO mytable (Permit,Stampdate) VALUES (1,GETDATE()),
 (1,DATEADD(day, DATEDIFF(day, 0, GETDATE()), '05:30:00'))
GO

SELECT *
FROM mytable As t1
WHERE
  EXISTS ( SELECT 1 FROM

   ( SELECT Permit,MAX(Stampdate) AS Stampdate
    FROM mytable As t2
       GROUP BY Permit) t2 WHERE  t1.Permit = t2.Permit
      AND t1.Stampdate = t2.Stampdate
)
GO

Permit | Stampdate
-----: | :----------------------
     1 | 2020-07-02 16:22:06.897

SELECT *
FROM mytable As table1
GO

Permit | Stampdate
-----: | :----------------------
     1 | 2020-07-02 16:22:06.897
     1 | 2020-07-02 05:30:00.000

    SELECT Permit,MAX(Stampdate) AS Stampdate
    FROM mytable As t2
GROUP BY Permit
GO

Permit | Stampdate
-----: | :----------------------
     1 | 2020-07-02 16:22:06.897

db<>fiddle here


Update Column in table on midnight daily

You can write a simple script externally or use the MySQL event scheduler. Both will work just fine. The benefit of the MySQL event scheduler is that it will be part of your database backups.


Adding index on low cardinality column can make sense for instance in case you need to search for value which is quite rare - say you have column State with possibities of New/Processed and 95% rows set as Processed, and you have to get those New often - then index will help you find them fast. But having it present means optimizer can try to use in bad cases.

Usually it is suggested to not index such column by itself, but use it as part of multicolumn index which can help specific queries (for instance - you want to process those new records from oldest to newest, then you add index on (State, CreatedTime) and query it like "where State = 'New' order by CreatedTime ASC" and this index will let you have those New fast and already ordered.


Unexpected growth Transaction log file 100 GB which is part of Transactional Replication

You may run below scripts to check the log reuse wait of the database(s):

SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;


Use this one:

DECLARE @coordinates TABLE (
    latitude1 DECIMAL(12,9),
    longitude1 DECIMAL(12,9)
)

INSERT into @coordinates
SELECT
    latitude,
    longitude
FROM loc.locations
WHERE place_name IN ('Delhi', 'Mumbai');

SELECT * FROM @coordinates

Note:

You created 4 column with NOT NULL behaviors, but your inserting on
2 columns only. It will return an error.
Use INSERT INTO instead of SELECT INTO. Table is already created.
Use SELECT..FROM when calling DECLARE tables.