How to prevent duplicate VARCHAR without a key limit?

We keep giving you answers that do not directly answer the question, because that is how we solve this problem.  An index of unlimited length is impractical and inefficient, but a unique hash provides a solution that sufficient to the task because of the astronomically low likelihood of a meaningful collision.

Similar to the other offered solutions, my standard approach does not check for duplicates up front -- it is optimistic in that sense: it relies on constraint checking by the database, with the assumption that most inserts are not duplicates, so there's no point in wasting time trying to determine if they are.

Working, tested example (5.7.16, backwards compatible to 5.6; previous versions do not have a built-in TO_BASE64() function):

CREATE TABLE web_page (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  url LONGTEXT NOT NULL,
  url_hash CHAR(24) COLLATE ascii_bin,
  PRIMARY KEY(id),
  UNIQUE KEY(url_hash),
  KEY(url(16))
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Note that I am storing the base64 version of the hash.  This is a 4:3 size tradeoff compared to storing it in binary form because it makes the table contents and the error message human readable, and the inefficiency is partially offset by the table compression.  The hash column has a unique constraint.  The data type is CHAR, not VARCHAR, since this eliminates the byte needed to store the size -- the hash is always a fixed size.  The column uses the ascii character set with ascii_bin (case-sensitive) collation, keeping the column and the unique index as small as practical.

The url_hash is set by a trigger, below, but the trigger does not check for a collision -- there is no need to check, because of the unique constraint on url_hash.  The database will block a duplicate insert.

Note that url_hash should have been declared NOT NULL but MySQL incorrectly enforces this before the BEFORE INSERT trigger fires, instead of after, so we are limited by that.  The trigger does prevent it from being null.

The url column has a prefix index length of 16, which was chosen arbitrarily.  This isn't a unique constraint, just an index for lookups, and it is probably shorter than you might want it to be, but its length has no operational impact on the problem we are solving, here.

Here's the trigger to set the url_hash.  We don't need to include this value in an INSERT statement when we insert rows.

DELIMITER $$
DROP TRIGGER IF EXISTS web_page_bi $$
CREATE TRIGGER web_page_bi BEFORE INSERT ON web_page FOR EACH ROW
BEGIN
  SET NEW.url_hash = TO_BASE64(UNHEX(MD5(NEW.url)));
END $$
DELIMITER ;

You need a trigger on update also, either to block updates if the table is supposed to be immutable, or to update the hash if the URL changes.  We also need this trigger to ensure that the url_hash column can't be inappropriately set to NULL since the limitation in MySQL doesn't allow us to actually declare it that way, as we should.

Now, to test.

mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
+----+---------------------+--------------------------+
1 row in set (0.00 sec)

So far, so good.  Now, a different URL:

mysql> INSERT INTO web_page (url) VALUES ('http://example.net/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
|  2 | http://example.net/ | ZVk/eLfvBI6tHN0Luj3NnQ== |
+----+---------------------+--------------------------+
2 rows in set (0.00 sec)

Still works.  Now, a duplicate.

mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
ERROR 1062 (23000): Duplicate entry 'pr8XV//wV/JmtpffnPF2/Q==' for key 'url_hash'

Perfect.  If you want an even lower risk of hash collisions than MD5 provides, use a SHA variant, increasing the length of data_hash to CHAR_LENGTH(TO_BASE64(UNHEX( /* your hash function */ ))) to accommodate the values generated by the hash algorithm in use.


What is the difference between mongod in standalone vs replica set

Switching a primary in a replica set to a standalone will not impact CPU unless you were offloading something to the secondaries that will now hit the standalone node like reads or Map Reduce (and you mention you were not). As well as that, mongod is generally not CPU intensive unless you are doing lots of sorts or distincts (or a lot of javascript, Map Reduce etc.). If you are just doing reads/writes then the CPU usage will be minimal.

The load metrics mentioned are both very low, and indicate the server is doing very little - that kind of difference could come from users doing things even. To be completely sure, use the mongod normally and check out what it is doing with CPU in top, I suspect it won't be using CPU heavily.

If there is a difference, then my final suspect would be a slight increase in write load or similar - depending on the options used, the non-MMAP engines can use a little more CPU when writing data (using compression, or cleaning up data on the filesystem). If your write load has not increased then that's probably not the issue.


Full and differential restore with two backup files - SQL Server 2008

You will need to set up a separate backup device for the FULL and the DIFF.  When you create the Backup Device it is a single file.  You can't save a FULL and a DIFF in the same Backup Device.

More information about backup devices can be found here: http://msdn.microsoft.com/en-us/library/ms179313.aspx

If you are trying to restore the database over an existing database and want to replace the data you will need to use the WITH REPLACE option in the restore.  More information can be found here: http://msdn.microsoft.com/en-us/library/ms186858.aspx

  REPLACE should be used rarely and only after careful consideration.
  Restore normally prevents accidentally overwriting a database with a
  different database. If the database specified in a RESTORE statement
  already exists on the current server and the specified database family
  GUID differs from the database family GUID recorded in the backup set,
  the database is not restored. This is an important safeguard.

  The REPLACE option overrides several important safety checks that
  restore normally performs. The overridden checks are as follows:

  Restoring over an existing database with a backup taken of another
  database.

  With the REPLACE option, restore allows you to overwrite an existing
  database with whatever database is in the backup set, even if the
  specified database name differs from the database name recorded in the
  backup set. This can result in accidentally overwriting a database by
  a different database.

  Restoring over a database using the full or bulk-logged recovery model
  where a tail-log backup has not been taken and the STOPAT option is
  not used.

  With the REPLACE option, you can lose committed work, because the log
  written most recently has not been backed up.

  Overwriting existing files.

  For example, a mistake could allow overwriting files of the wrong
  type, such as .xls files, or that are being used by another database
  that is not online. Arbitrary data loss is possible if existing files
  are overwritten, although the restored database is complete.


When dropping a partition on InnoDB table does mysql flush pages in the whole table or only in a partition to be dropped?

According to the MySQL 5.7 Documentation under the subheading Affected DDL statements, here is what says about Locking for DDL on Partitioned Tables:

CREATE VIEW does not cause any locks.
ALTER TABLE ... EXCHANGE PARTITION prunes locks; only the exchanged
table and the exchanged partition are locked.
ALTER TABLE ... TRUNCATE PARTITION prunes locks; only the partitions
to be emptied are locked.
In addition, ALTER TABLE statements take metadata locks on the table
level.

As far as I can see in that page, it locks the table's metadata. There is nothing here suggesting that all the .ibd files of the other partitions need to be flushed. It would affect only those files involved.
In your case, it would involve the following steps:

Acquire metadata lock on the .par file of the partitioned table
Update list of partitions in the .par file
Remove locks of .ibd you are going to drop
Unlock the .par file

NOTE : Dropping a partition is DDL not DML.
DIRECT ANSWER TO YOUR QUESTION
Only partition to be dropped


Use database to track locking/unlocking of objects

The goal is to make the query quick and to avoid deadlocks.

This is an unrealistic goal. Deadlocks are determined by the application acquiring the locks and is out of control of how you implement the locking. The best you can hope for is to detect deadlocks.

Implementing locks as record is problematic. Rows persist and you'll leak locks on application crash, even when the implementation is perfect. Do locks with applocks. They have clear transactional semantics and deadlocks are detected by the engine.

However, looking at what you're trying to achieve, is unlikely you need locks at all. You are describing a queue (pick the next item available to process and avoid conflicts => queue, not locking). Read Using tables as Queues.

As for your specific implementation (using a table that keeps the history of the locking) I must be honest: it is a disaster. To start with the table design is completely inadequate for the intended usage: you query by name but the table is a heap with no indexes. It has an identity column for no apparent reason. You may reply that it is just a 'pseudo-code' table, but this is DBA.SE, you do not post here incomplete DDL!

But more importantly, the implementation does not implement locking! There is nothing to prevent two users from 'locking' the same object twice. Your 'locking' relies entirely on the callers magically behaving correctly. Even a best written application could not use this locking, because there is no way of checking and acquiring the lock atomically. Two users can check, conclude that 'a' is unlocked, and simultaneously insert the ('a', 1) record. At the very least you would need an unique constraint. Which of course would break the semantics of "count the locks vs. unlocks to determine status".

Sorry to say, but this is an F grade implementation.

UPDATE

  So we want to provide a capability to track each lock call. Upon restarting the server, we will call unlock on the objects which previously locked.

W/o engaging into two-phase commit distributed transaction with the remote system all you can do is a 'best effort', because there are many race conditions between you writing the 'unlock' and actually calling 'unlock' on the third party system. As a best effort, here is my recommendation:

create a simple table to track the locks:

CREATE TABLE locks (name VARCHAR(255) NOT NULL PRIMARY KEY);
before calling lock insert the lock into your table and commit.
after calling unlock delete the lock from your table and commit
on system startup look at the table. any row there is a left over lock from a previous run and must be 'unlocked'. Call unlock for each row, then delete the row. Only after all pending locks have been 'unlocked' you can resume the app normal functionality.

I'm proposing this because the table will stay small. At any time it only contains current, active locks. It will not grow ad-nauseam and cause problems later due to sheer size. Is trivial to see what is locked.

Of course this implementation does not  offer an audit history of what was locked by whom and when. You can add that if needed, as a different table, into which you only insert the events (lock or unlock) and never query that to figure out 'orphaned' locks.

You still have to be prepared with calls to 'unlock' to fail during startup because you cannot guarantee that your system did not crash after calling unlock but before deleting the row (in other words your table and the third party system have drifted apart and have different versions of the truth). Again you cannot prevent this w/o distributed transactions and I would never advise for DTC.


Alternative methods for client data integration (Azure SQL Database)


One reason could be that your oplog is too small.

On the master:

db.getReplicationInfo()

If timeDiffHours is shorter than the time that your initial sync takes, then the initial sync will fail and start again...

Resizing the oplog is quite easy, if this is indeed your problem. The documentation is here:
http://docs.mongodb.org/manual/tutorial/change-oplog-size/


Database Design Concept - Are Multiple Relationships A Bad Thing?

In your model, every Service has one Site and every Site has one Customer.
Now you have to ask yourself: Is there a use case where the CustomerID associated with the service can be different from the customerID associated with its site?

If a service can have a service-customer and a site-customer, then this design would be correct. If not, you have stored redundant information in your database by including the CustomerID column in the Service table and redundancies should be avoided in good database design.


Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases?

I would imagine that it is enabled to minimize blocking in the master and msdb databases of the system processes.  It is disabled in user databases because in user databases it can greatly increase the disk IO requirements of the tempdb database.


Update a view with daily tables in Postgres

If you are on postgresql 10, the best way to handle this is to use partitioning so you have a single table instead of a view. On lower versions you can do partitioning, but it is not declarative (i.e. you have to do a lot of the work yourself).

Can't think of a way to do this from within Postgres/Pl/PgSQL, but you could do something like this:

Create a shell script that you run in psql to output the list of tables. Something like psql -c "\dt h2018*" or the equivalent query looking at system tables
Use the output to send it to AWK, or your favorite scripting language, and then build a new .sql file and then call that with psql. You likely can do it with a scripting language completely to build the query, but likely the above is the easiest and fastest way to accomplish what you want.


Is it possible to pick next row's cell value on same column if condition not matched?

I assume you mean you want to find the "first" row with same Ticker_ID and same or bigger DateTime, first when ordered by DateTime ascending. One way to do this, assuming that the price table has a unique constraint on (Ticker_Ticker_ID, Price_DateTime):

UPDATE flaggedcomment AS fc
    INNER JOIN price AS p
        ON  p.Ticker_Ticker_ID = fc.Ticker_Ticker_ID
        AND p.Price_DateTime =
            ( SELECT pi.Price_DateTime
              FROM price AS pi
              WHERE pi.Ticker_Ticker_ID = fc.Ticker_Ticker_ID
                AND pi.Price_DateTime >= fc.Comment_DateTime
              ORDER BY pi.Price_DateTime ASC
              LIMIT 1
            )
SET
    fc.Price_DateTime = p.Price_DateTime,
    fc.Price_Open = p.Price_Open ;


MSSQL. Tree structure fetching with conditions

WITH cte AS ( SELECT *
              FROM tree
              WHERE id = @starting_node
                  UNION ALL
              SELECT tree.*
              FROM tree, cte
              WHERE tree.parent_id = cte.id
                AND cte.IsCollapsed = 'expanded' -- if collapsed then stop
            )


Can't SHOW GRANTS after MySQL re-install

Entries existing in the mysql.user table and privileges being recognized by the MySQL server are two different things that are only loosely-coupled.

mysql> FLUSH PRIVILEGES;

Any time you change the content of the mysql.user table, other than by issuing a GRANT ...-type statement, including restoring from a backup, you need to issue the FLUSH PRIVILEGES command before MySQL will realize those changes have happened.  This should make the users visible.

Or, by now you may have restarted the server, which would also fix it.


Creating Indexed View GROUP BY Epoch Date

I think there's some misunderstanding about what you're attempting to do here.

Since your current design is to return all 24 rows from the base table, presumably all the supplementary fields are returned as well (to display in a grid, or something).

In order to fully aggregate the Value column, all the supplementary columns cannot be included in the SELECT list. Alternatively, if those columns are included in the GROUP BY clause, the view would represent only a partial aggregation, as there would be one row for each unique combination of the columns in the GROUP BY column list.

The only way I see something like this being useful is if the supplementary columns aren't included in the view, and there is some other process that requires only the daily aggregated values, without the base row data. Such a view could be defined like this:

CREATE TABLE [dbo].[BaseTbl]
(
    ColRowID bigint NOT NULL,
    AggregateID int NOT NULL,
    Epoch int NOT NULL,
    CustomerID int NOT NULL,
    TypeID tinyint NOT NULL,
    ErrorID smallint NOT NULL,
    Value int NOT NULL,

    PRIMARY KEY CLUSTERED(Epoch, CustomerId)
);
GO

CREATE VIEW [dbo].[ixvw_AggTbl]
    WITH SCHEMABINDING
AS
    SELECT
        t.Epoch / 86400 AS EpochDay,
        CustomerID,
        TypeID,
        SUM(t.Value) AS TotalValue,
        COUNT_BIG(*) AS __RowCount
        FROM [dbo].[BaseTbl] t
        GROUP BY
            t.Epoch / 86400,
            CustomerID,
            TypeID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_ixvw_AggTbl
    ON [dbo].[ixvw_AggTbl](EpochDay, CustomerID, TypeID);

Unfortunately, you can't go farther and convert the EpochDay column to an actual date within the indexed view because DATEADD is non-deterministic (see Aaron's comment below for why), so you'd have to convert it in the actual SELECT query against the view. But that's not too difficult.

In any event, as I said before, I'm not sure how useful this would be for your specific application.


db_datawriter has no items in the permissions page because it doesn't have explicit object permissions as such. Rights are implied by the role.

MSDN for db_datawriter says

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

It has INSERT, UPDATE, DELETE on tables as per another MSDB page Permissions of Fixed Database Roles

  Granted: DELETE, INSERT, UPDATE

Finally, what does the DB engine say (SQL Server 2008 R2)?

EXEC sp_dbfixedrolepermission 'db_datawriter'

db_datawriter   DELETE permission on any object
db_datawriter   INSERT permission on any object
db_datawriter   UPDATE permission on any object

The MSDN pages for SQL Server 2008 are here (different page hierarchy)


Access: Number format for VBA in queries of variant type

The answer can be found here in Allen Browne's page:

  in a query, a column can be only be ONE data type. JET therefore treats Variants as Text, since anything (numbers, dates, characters, ...) is valid in a Text column.

Numeric formatting options will not affect the text values which are automatically converted from the VBA Variant, even if the original value was coded as numeric.

His recommended solution is to do what you have already discovered: Use the IIF statement.  The Jet engine uses datatype clues from the parameters to establish the returned column's data type.

Here's some code that will reveal the automatically-selected column type:

Dim rs As Recordset2
Set rs = CurrentDb.OpenRecordset("SELECT f([Col]) FROM table")
Debug.Print "Is text? " & (rs.Fields(0).Type = dbText)
rs.Close


Difference in number format (comma and dot) on SSMS and SSIS

The issue here is that the Locale settings of the file connection in SSIS are different to the language settings in SQL Server.

In SSIS, go to the Properties window for your File Connection (Right-click->Properties). There should be a setting for LocaleID that will need to be set to match SQL Server (or to English).

Further information can be found in the following Stack Overflow post


MongoDB/Mongochef: import from JSON changed dates to string

All right. After digging a LOT I found the correct formatting is:

time: {$date:"2013-11-20T23:32:18Z"}

So I used 'sed' to change it throughout the json file and imported it.

I'm leaving this answer for the benefit of anyone who might come across this problem.


Error in table 'sys.sysbinobjs' on master database

There's nothing you can do to fix it, I'm afraid, without a backup, you can't fix errors in system tables like this and I actually would've been surprised if installing SP4 would've worked.

The best (and probably only) way to resolve this is to create a brand-new, clean install of SQL Server somewhere else, then restore all of users database there, and script out your logins and jobs etc. to the new site.

Not ideal, I know, and a bit time consuming perhaps, but easy to do and it'll definitely solve your problem. You might be able to sell it as/use it as an opportunity to upgrade everything, spec the system exactly who you'd like it and get everything updated. There will be some downtime (but this can be minimised).

This is similar to your situation (but perhaps worse) and Paul Randal's reply puts it quite succinctly. This on SSC is another similar one, but not answers (except a confirmation that there's not a lot you can do). Finally, this MSDN post explores the problem as well - same conclusion.


This is an old question that I actually landed on in my quest trying to accomplish the same task recently.  I wanted to follow up with my solution in case someone else lands here as well.

I actually wrote a few functions in powershell to get the job done.
You can see the code and process here


Selecting and Inserting into linked server

All of the scenarios work if I SET XACT_ABORT OFF. So does XACT_ABORT has any kind of restriction when it comes to linked server?

The setting of XACT_ABORT is propagated to the remote SQL Server, as documented in Handling Errors in Server-to-Server Remote Stored Procedures.

The setting also affects if updates are possible, and how they are handled, as documented in Distributed Queries and Distributed Transactions. Your INSERT is allowed with XACT_ABORT OFF because SQL Server supports nested transactions.

Nevertheless, there appears to be an implementation bug, because tracing the activity on the 2014 server during the insert reveals that an error occurs when SQL Server attempts to release a schema lock:

This error aborts the statement (attempting to release the schema lock) on the remote server, but when XACT_ABORT is OFF, the remote server continues processing the next statement. The insert completes, despite the schema lock release error.

When XACT_ABORT is ON, the whole remote batch is aborted, so the insert is rolled back.

I was able to reproduce your problem locally, but the ORDER BY clause was not significant.

You can avoid the problem in several ways, including wrapping the INSERT in an explicit transaction (assuming DTC is available to you).

I recommend you avoid four-part name syntax for remote changes, since the implementation is based on a cursor model. You will normally get better performance using a bulk method, or by pulling the data at the remote server (rather than pushing it from the local server).

See the related Q & A Which one is more efficient: select from linked server or insert into linked server?


T-SQL Statement Task choose specific database name on server

Do not rely on GUI .. use TSQL instead :-)

Many options you have to do your simple task :

use 3 part naming SELECT * INTO dbname.schema.mytable FROM dbname.schema.anothertable
explicitely use use dbname; SELECT * INTO mytable FROM anothertable;
Use sqlcmd - either sqlcmd.exe or in SSMS query window
Use powershell

BTW, dont use maintenance plans - instead use Ola's maint solution


Figured it out used ADMIN_CMD

set v_sql ='LOAD FROM (SELECT '|| v_colquery || ' FROM ' || srctablename  || ') OF CURSOR INSERT INTO ' || destablename || '_LOG  ( ' || v_colquery || ')';
call admin_cmd(v_sql);


PostgreSQL: Do regclass/oid columns persist across backups?

I wouldn't rely on the specific values of OIDs to be portable at all. Even if you try it and you find it to work with the current version of PostgreSQL, it might not do so in newer releases.

If you backup your database in a source machine and try to restore it on a different machine (or, in general, another PostgreSQL Database Cluster) that does already have other databases, schemas, etc. it is very likely that the different OIDs your source database was using are already used to represent other different things in the target database.

The schema_name.table_name correspondence to an OID is handled by PostgreSQL within its system tables. If you need equivalent functionality, with the guarantee it behaves the way you need it to and not the way PostgreSQL uses its internal OIDs, I'd recommend you actually duplicate this behaviour with a your_tables table, where you store an arbitrary id (a serial) and schema_name, table_name. This way, your database is safe to backup and restore, and you're likely to avoid modifications for any newer postgreSQL version.

You may need an extra lookup in your trigger(s), but might save you a lot of trouble in the future.


SQL Server Point Constructor or version of ST_Point(x,y)?

SQL Server 2012+
Yes, it's not listed there though. You'll find it documented on the docs of geography::Point(x,y,srsid)

The following example uses Point() to create a geography instance.
geography::Point(47.65100, -122.34900, 4326)

Microsoft calls it an "Extended Static Geography Methods"
It's documented in the SqlGeography Class and the Geometry constructor in the SqlGeometry Class


SQL Server database snapshots for integration testing

Unfortunately, it's by design.

Taken from BOL page "Revert a Database to a Database Snapshot":

  Limitations and Restrictions

  Reverting is unsupported under the following conditions:

  The database must currently have only one database snapshot, to which    you plan to revert.
  Any read-only or compressed filegroups exist in the database.
  Any files are now offline but were online when the snapshot was    created

As an alternative, you could drop the first snapshot (db)_Basis. I can understand that this seems very limiting but look at it this way: snapshots are sparse files based on the original data files, so reverting to a specific snapshot would invalidate all snapshots anyway (the base data files would be changed by the revert operation). The limitation can be annoying, but doesn't look unreasonable.


How To Export-Import Database in Oracle 11g(Application Express Edition)?

You have lots of different options for doing this:

http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm

I would recommend using Oracle Datapump as this is the most powerful solution, and would represent the best opportunity for instruction on how this is done in an enterprise environment.


MySQL - Column value depends on values from two different tables

With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view

create table TableA (
id int,
v_time date,
price int,
special_price int
);

create table TableB (
table_a_id int,
v_date date
);

create table SpecialDate (
v_date date,
description varchar(5)
);

insert into TableA values (1,'2019-09-01', 10, 5);
insert into TableB values (1,'2019-10-01');
insert into TableB values (1,'2019-11-01');

insert into TableA values (2,'2019-12-01', 50, 1);
insert into TableB values (2,'2019-12-02');
insert into TableB values (2,'2019-12-03');
insert into TableB values (2,'2019-11-01');

insert into SpecialDate values('2019-11-01', 'sale');

--your view definition

select a.id,
   a.v_time,
   count(*) as count_of_table_b_instances,
   sum(
      case
     when s.v_date is not null then a.special_price
     else a.price
     end
   ) as sum_of_table_b
 from TableA a
 join TableB b
   on a.id = b.table_a_id
 left join SpecialDate s
  on b.v_date = s.v_date
 group by a.id,
      a.v_time

  Will the performance take a hit?

I can't say for sure because I can't assume you have the right indexes setup to support this query without seeing the DDL of the tables. If you have indexes in place to support the join clauses, or if you don't have much data in these tables, I don't think you would take a performance hit.


Readable join for execution plan in Oracle

That is because of query transformations like for example Join Factorization.

Simply put, with JF, queries like this:
select t1.c1, t2.c2
    from t1, t2, t3
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2
  union all
    select t1.c1, t2.c2
    from t1, t2, t4
    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3;

Can be automatically rewritten by the optimizer to something like this:
select t1.c1, VW_JF_1.item_2
    from t1, (select t2.c1 item_1, t2.c2 item_2
                   from t2, t3
                   where t2.c2 = t3.c2 and t2.c2 = 2
                 union all
                   select t2.c1 item_1, t2.c2 item_2
                   from t2, t4
                   where t2.c3 = t4.c3) VW_JF_1
    where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1;

You can see a step called VW_JF_... in your plan whan this happens.
There are other transformations as well that can cause this.

This is too generic and unclear what you want.

Not that I am aware of. You can however display an alias mapping with the format=>'alias' option:

SQL> explain plan for select count(*) from t1 a where a.object_id = 1;
Explained.
Default output:
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - access("A"."OBJECT_ID"=1)

14 rows selected.

Same with alias mapping:
SQL> select * from table(dbms_xplan.display(format=>'alias'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

   1 - T1
   2 - T1 / A@T1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OBJECT_ID"=1)

20 rows selected.

SQL>


Managed backup to Azure does not delete backup after retention period

I ended up making a Sql server agent job that runs this with powershell mode:

# Ps1-script to keep azure storageaccount for managed backups clean

Import-Module Azure

$storageAccount = "STORAGEACCOUNTNAME"
$storageKey = "STORAGEKEY"
$blobContainer = "CONTAINERNAME"

$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
$context = New-AzureStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageKey
Get-AzureStorageBlob -Container $blobContainer -Context $context |
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |
Remove-AzureStorageBlob
$CleanupTime = [DateTime]::UtcNow.AddDays(-4)
Get-AzureStorageBlob -Container $blobContainer -Context $context |
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.log"} |
Remove-AzureStorageBlob

This does delete all .bak and .log files older than 4 days. It does not however work on blobs that does have active lease on them.


Find the Sharepoint application server based on Sharepoint Databases

Look at the sessions on that database:

SELECT host_name
FROM sys.dm_exec_sessions
WHERE database_id = db_id('yourSharepointDatabaseName');

If something is using the database, the client host name will show up here.


How to connect remotely to MSSQL database from local, using windows Authentication?

Assuming you are in a different domain, you can launch SSMS using the remote Windows commercials. Open a command prompt in the folder where ssms.exe lives, and run:

runas /netonly /user:remotedomain\remoteuser ssms.exe

You will be prompted for your password. SSMS will be using remote Windows credentials even though several UI elements will look like you're using the local ones.

This technique should work for launching other executables, like sqlcmd or powershell, but I haven't tested those. I know it works for SentryOne and Plan Explorer.

You can also use Credential Manager, which I wrote about (in addition to more details about runas) here:

Connect to SQL Servers in another domain using Windows Authentication


Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure

VOLATILE is completely independent from COST.
The first defines function volatility and VOLATILE is the default, so it can be omitted. Alternatives are STABLE and IMMUTABLE.
The latter is only valid in combination with a COST parameter, like COST 500. The default is COST 100 which, again, can be omitted. It declares the cost per row of the result, which is used by the query planner to find the cheapest plan. If you don't know what you are doing exactly, just leave it at the default.
Read the very fine manual, starting at the chapter CREATE FUNCTION.


Does Service pack have all the changes in a cumulative update

According to SQLServerBuilds SP1 CU15 was released after SP2.

SP1 CU15 was released on 2019-05-16, SP2 was released on 2018-04-24.

Given that from SQL 2017 there will no longer be any service packs, this is no longer relevant moving forwards, but in the past Microsoft would release fixes in parallel for supported service packs. As an example this kb article describes an issue that had a fix that was included in two separate CU's, CU15 for SP1 and CU7 for SP2.

For confirmation of the data on SQLServerBuilds we can check the file details on both SP2 and CU15 SP1

In addition to this as pointed out by David Browne, there is a development and test overhead for all new SP's, meaning an SP does not necessarily contain all fixes in previous CU's by date - this is due to the time taken for finalising and testing the SP.


Is there any reason to stop transaction log backups during a maintenance window?

You are absolutely correct. There is no reason to stop Transaction log backup during index maintenance window. It will only make things worse as you already experienced.  I had cases where I was doing maintenance on smaller size indexes I even took transaction log backup more frequently than my regular schedule.

In case if you are rebuilding large indexes you need to make sure that you have enough storage for transaction log to grow. Even you take transaction log backup during index rebuild logs will not be truncated when you have a long and active transaction.  That is not the case when you reorganize indexes because logs can be truncated in between while reorganizing a single index.

Even in the Frequently Asked Questions page of Ola Hallengren this is mentioned:

The transaction log is growing very large when I run the IndexOptimize job. What should I do?

  Make sure that the transaction log backup job is running as it should.

  Check that the transaction log has the storage that it needs. You
  should not shrink the transaction log file. Doing so costs resources
  to shrink and later to regrow the file.

During full backup there is no need to stop transaction log backup.  Read this article by By Erik Darling.

What happens to transaction log backups during full backups?


mysql multi threading for a single query

Neither MyISAM nor InnoDB have multithreaded query implementation. Nor do other XtraDB, TokuDB.
It is a long standing issue and limitation of MySQL. I don't know that the limitation is within the MySQL server, but rather in the storage engine implementation. No one ever did it...
I'm currently evaluating the column-store InfiniDB and Infobright IEE. They both support multi-cpu queries and the difference is overwhelming (due to multiple reasons, but CPU being obvious one).
You might want to look at Shard Query, a proxy to MySQL which splits your query into multiple queries, executes them on parallel processes and returns the aggregated result.


GROUPING SETS query with many-to-many relationships

Your problem query:
-- this query shows how the join leads to wrong aggregate
-- results, as it obviously duplicates rows
-- 'y' appears only once in the table, but is counted twice here
select grouping(b),b,count(*)
from demo
left join demo_stuff on id = demo_id
group by grouping sets ((b),());
Result:
grouping | b    | count
-------: | :--- | ----:
       0 | z    |     2
       0 | y    |     2
       1 | null |     4

Your comment is slightly inaccurate. The "table" is the derived table after joining demo and demo_stuff, and 'y' appears twice there. Either you want count(DISTINCT demo.id) in the result, or count(*). Those counts are different by nature and should go into separate result columns:

SELECT grouping(b), b
     , count(DISTINCT d.id) AS ct_distinct_demos
     , count(*)             AS ct_rows  -- your org count
FROM   demo d
LEFT   JOIN demo_stuff s ON d.id = s.demo_id
GROUP  BY GROUPING SETS ((b), ())
ORDER  BY 1;
Result:
grouping | b    | ct_distinct_demos | ct_rows
-------: | :--- | ----------------: | ------:
       0 | y    |                 1 |       2
       0 | z    |                 2 |       2
       1 | null |                 3 |       4

db<>fiddle here
You'll have to define precisely what to count for the correct solution. Might result in count(d.b) or count(*) or count (DISTINCT d.b) ...


Can I anchor my data type to system type?

Short answer: No, you can't, and not because it's a system type. You can't anchor a freestanding type to any table's column data type.

%TYPE is a PL/SQL construct. CREATE [OR REPLACE] TYPE is SQL. You can't use %TYPE in SQL.

It somewhat makes sense that you can't. If you use MYTABLE.MYCOLUMN%TYPE in PL/SQL, you have anchored that PL/SQL type to the table, and should the type of MYCOLUMN change PL/SQL can invalidate your code, then recompile it. It's much less clear what Oracle would have to do if your example worked.

Imagine what would happen if you were storing objects of SQLID_T in a table, and the definition of SQL_ID in V_$SQL changed. Would Oracle need to change the definition of the stored objects? What if it couldn't (e.g, SQL_ID was changing from a VARCHAR2 to a NUMBER)? Should that prevent the definition of V_$SQL from changing? Or should the table that used that type become invalid in some way ... you can't SELECT from it anymore?

Oracle tries to prevent this kind of thing from happening (from Oracle's Object-Relational Developer's Guide):

SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (c varchar(20));
  2  /
Type created.

SQL> CREATE TABLE tb1 (c1 t1);
Table created.

SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
  2  /
CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
*
ERROR at line 1:
ORA-22866: cannot replace a type with table dependents


Will PostgreSQL use parts of a composite index even if all parts aren't listed in the where clauses?

Yes, because that composite index in your example is also known as a covering index. Specifically mentioned in the previously linked documentation here:

...people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing
CREATE INDEX tab_x_y ON tab(x, y);
even though they had no intention of ever using y as part of a WHERE clause. This works fine as long as the extra columns are trailing columns...

Basically, as long as the fields of your predicates are all sequentially part of the index definition (from left to right), then that index is covering and applicable to be used for that query.
The reverse of your example would not work though since the index does not cover that scenario. I.e. WHERE colB = 'someValue' is not covered by an index with the definition of (colA, colB) since the order of the field list in the index definition literally defines the structure of the underlying B-Tree used to sort the data. So first the data is sorted on colA and then colB, making it not possible to directly search on colB (aka directly seek on colB, rather an index scan could still be performed to indirectly search on colB) without a predicate to search on colA first.


reloading your listener will allow listener to take new changes (configuration) without stopping it, and it will un-register\register dynamically registered DBs.

stop will stop listener completely by disallowing new session to connect to the database remotely.

start will start listener and this will allow users to continue working on the database.

for me I usually use listener reload for production to make sure I dont have any downtime during working hours. stop\start incase we need to perform some maintenance


How to add foreign key in a table where column value can come from two different tables?

I would suggest you structure your comments like this,

CREATE TABLE comments (
   comment_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   parent_id  int REFERENCES comments,
   comment text NOT NULL
);

Then you three advantages,

you don't have the problem about having to reference sub-comments AND comments
you can go more than one layer deep
the schema between subcomments and comments is in sync

For more information see the postgresql and hierarchy badges.


What's difference between standard view and indexed view in SQL Server

An indexed view is physically stored ("materialised") on disk = requires memory
A standard view is simply an expandable macro: there is no persistence of the data and the base tables are always used

Other notes:

Both will return the correct data from the base tables
SQL Server will consider whether to use the indexed view or just expand it like a macro (subject to edition and NOEXPAND hint)

SELECT * is bad practice anyway, but * means you can't qualify all columns as required for indexed views


Log non indexed queries for multiple databases

To find all queries not using indexes, you need to do two things:

Enable the slow query log
Set the global variable log_queries_not_using_indexes to ON

In my.cnf for 5.6, this would look like:

[mysqld]
...
...
slow_query_log=ON
log_queries_not_using_indexes=ON

Both are dynamic variables and can be set on a running system like this:

mysql> SET GLOBAL slow_query_log=ON;
mysql> SET GLOBAL log_queries_not_using_indexes=ON;

Refer to https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html for full details and note that by default queries not using indexes are not logged to the slow query log, but if log_queries_not_using_indexes is enabled, ALL queries not using indexes will be logged, regardless of if they take longer than 'long_query_time' to execute.  If the number of queries logged is excessive, use log_throttle_queries_not_using_indexes to limit the number of queries not using indexes that will be logged per minute.  Like the other two global variables, this is dynamic and can be changed on a running system like this, e.g. to set it as 10 per minute:

mysql> SET GLOBAL log_throttle_queries_not_using_indexes=10;

That ought to fix you up.


Master-Slave is pretty good, but you must write only to the Master.  Inability to write to the Master means inability to do the various tasks.  A network outage between M and S leads to delayed updating of the Slave.

Master-Master, and writing to both, has several ways to get into trouble, so I recommend against it.

Master-Master, but writing to only one solves some of the M-M problems, and makes failover easier.  That is, if the Master fails, the configuration is ready to promote the Slave in an otherwise M-S topology to be the Master.

Group Replication has a lot of promise, and is likely to be the best in the long run.  However, I cannot say much at the moment.

MySQL NDB Cluster is complex to set up, has many moving parts, uses "eventual consistency" model of replication, etc.  Some applications can use it; hard to say if it fits your situation.  Study its implementation of "eventual consistency"; if that model works for you, then consider putting an instance of the three nodes (data, sql, mgt) on a single server at each of your two sites.  (I assume scaling is not an issue.)

Galera (see MariaDB, Percona's PXC, or Codership), which you did not list, is robust, write-anywhere, self-healing, etc.  The optimal setup would be to have 3, not 2, "nodes" in 3 separate locations with separate network connections.  As long as any 2 nodes can talk to each other, those 2 declare themselves to be online.  If the third is alive, but isolated (because of network outages), it understands that is should be "offline".  When an offline node comes back online (from network outage or servers crash), the data is automatically repaired with the help of the online nodes.


if you're using the "custom" format , you can use the -l (ell) and -L options to do this.

create your dump, using the "custom" format
run pg_restore -l /path/to/dump/file > table_of_contents.txt
edit table_of_contents.txt and just remove references to the tables you don't want anymore. (Make sure you get all of them.  You can comment out a line with a semicolon.)
restore your database with pg_restore ... -L table_of_contents.txt /path/to/dump/file

Profit!


Accessing old version of referenced table in trigger function - Postgresql

I didn't look at the code, but obviously the row is deleted from grouplayer before the BEFORE trigger on layer is executed.

Perhaps you can perform the task in a BEFORE trigger on layer.


I'd say you miss a post for your instance in /etc/freetds.conf. The post should look something like this

[my_alias]
    host = some_db_host_address
    port = 1433
    tds version = 8.0

Then in your connection string you should use that alias. The host and port part shouldn't be needed as they are picked up from the configuration file. You may have to say which driver to use. This works for me

$conn = odbc_connect("Driver=FreeTDS;DSN=my_alias","user","pass");


What are best practices for safely permanently deleting a database?

You could try to set up a trace that only captures connections and to what database they connect to. I would leave this running for a bit and then make sure nothing is connecting to it.

One problem with that would be if you have some code opening up on the master db but calling another DB within the code. I'm not sure how bad the code is that is pointing to your DBs.

I'd also query all your jobs and make sure none are pointing to that DB

You could also use SQL audit if you have the right version of SQL (2008 R2 enterprise).

You could also use logon triggers to update a table when someone logged on to that DB. This would show you if anything was connecting to that DB.


How to use full text search in Persian using Postgresql?

use this.

select post_id
from comments
where to_tsvector('simple', comments.body) @@ to_tsquery('simple', 'کتاب') ;

OR

select post_id
from comments
where to_tsvector('simple', comments.body) @@ to_tsquery('کتاب') ;

Or use this extension:

https://pgroonga.github.io/tutorial/


I've faced the same issue some months ago, and I have managed to solve it with a bash script. This is the piece of code that take care to backup large objects, and save it to a local folder (/path/to/destination/lo/folder/) with filename = oid (the filename is important in order to restore lo with same oid) :

echo -e "going to export largeobjects belonging to community $i..." && sleep 2
psql -U <username> -X -c "SELECT file_oid FROM <my_table>" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    -d <my_source_db> \
| while read file_oid ; do
    echo "Exporting largeobject OID: $file_oid"
    ${PSQL} -d <my_source_db> -c "SELECT lo_export($file_oid, '/path/to/destination/lo/folder/$file_oid');"
done

Then to restore them I've written this other piece of code:

echo "going to import largeobjects belonging to community $i..." && sleep 2
LOBJECTS=/path/to/destination/lo/folder/*
for f in $LOBJECTS
do
    echo "Processing $f file..."
    filename=$(basename "$f")
    oid=${filename}

    psql -U <username> -d <my_source_db> -c"
    -- check if largeobject is already present, if it is delete it
    SELECT lo_unlink(${oid});"

    psql -U <username> -d <my_source_db> -c"
    -- import largeobjects
    SELECT lo_import('$f', ${oid});
    "
done

Out there it may exist a simpler solution, but at the time I've not been able to find it, this is why used this approach. I hope you will be more lucky and find a cleaner solution. I'll follow this post in case I can learn something useful too : )


How to control Segmentation min/max data_id on a non-clustered ColumnStore index

This isn't directly supported for nonclustered columnstore indexes.

It does work for clustered columnstore.

Azure Synapse Analytics has language support for doing it in one step e.g.:

CREATE CLUSTERED COLUMNSTORE INDEX <index_name>
ON dbo.PropertyValue
ORDER (PropertyId, Value);

This syntax has not yet made it to the SQL Server box product, though it is available under an undocumented feature flag so perhaps it isn't far away. It still won't work on a nonclustered columnstore index though.

General Workaround

The best you can do is to create the nonclustered rowstore index with MAXDOP = 1, then replace it with a nonclustered columnstore index with MAXDOP = 1 and DROP_EXISTING = ON.

This isn't guaranteed to preserve the ordering as you want, but it is highly likely:

CREATE NONCLUSTERED INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (MAXDOP = 1);

CREATE NONCLUSTERED COLUMNSTORE INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (DROP_EXISTING = ON, MAXDOP = 1);

This will give you your best chance of achieving rowgroup elimination when filtering on PropertyId.

Special Case

When the desired ordering matches the rowstore clustered index (as appears to be the case in the question), there is no need to create a rowstore nonclustered index first. The documentation says:

  Note, for nonclustered columnstore index (NCCI), if the base rowstore table has a clustered index, the rows are already ordered. In this case, the resultant nonclustered columnstore index will automatically be ordered.

So, in your case, it should be enough to run only:

CREATE NONCLUSTERED COLUMNSTORE INDEX CS_IX_PropertyValue
ON dbo.PropertyValue (PropertyId, Value)
WITH (MAXDOP = 1);

See this db<>fiddle demo.

Metadata

You can see the min and max values for each rowgroup and column using:

SELECT
    CSS.column_id,
    column_name = C.[name],
    rowgroup_id = CSS.segment_id,
    CSS.min_data_id,
    CSS.max_data_id,
    CSS.row_count
FROM sys.partitions AS P
JOIN sys.column_store_segments AS CSS
    ON CSS.hobt_id = P.hobt_id
JOIN sys.indexes AS I
    ON I.[object_id] = P.[object_id]
    AND I.index_id = P.index_id
JOIN sys.index_columns AS IC
    ON IC.[object_id] = I.[object_id]
    AND IC.index_id = I.index_id
    AND IC.index_column_id = CSS.column_id
JOIN sys.columns AS C
    ON C.[object_id] = P.[object_id]
    AND C.column_id = IC.column_id
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.PropertyValue', N'U')
ORDER BY
    C.column_id,
    CSS.segment_id;


With SQL Server 2008 R2 (through current version), you can use SQL Server Audit which uses Extended Events.

Look at Understanding SQL Server Audit:

  Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (Database Engine). Beginning in SQL Server 2008 Enterprise, you can also set up automatic auditing by using SQL Server Audit.

SQL Server Audit is a combination of several parts:

SQL Server Audit = on an instance, define Instance or Database level actions or groups of actions to monitor and destincation of the output
Server Audit Specification = linked to one SQL Server Audit and is used to collect server level predifined groups of actions using Extended Events
Database Audit Specification = linked to one SQL Server Audit and is used to collect database level groups of actions or audit events using Extended Events
Target = the destination of the output to a file, Windows Security event log or Windows Application event log

The general process for creating and using an audit is as follow:

  Create an audit and define the target.
  Create either a server audit specification or database audit specification that maps to the audit.
  Enable the audit specification.
  Enable the audit.
  Do something related to the audit...
  Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

1. Create Audit:

USE [master]
GO
CREATE SERVER AUDIT [AuditTest]
TO FILE
(   FILEPATH = N'...\...'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);

2. Create Audit Specification:

USE [YourDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [SpecTest]
FOR SERVER AUDIT [AuditTest]
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
WITH (STATE = ON)

3. Enable Or Alter Audit Specification:

ALTER DATABASE AUDIT SPECIFICATION [SpecTest] WITH (STATE=OFF);
ALTER DATABASE AUDIT SPECIFICATION [SpecTest] ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)
ALTER DATABASE AUDIT SPECIFICATION [SpecTest] WITH (STATE=ON);

4. Enable/Disable Audit

USE [master]
GO
-- disable
ALTER SERVER AUDIT [AuditTest2] WITH (STATE=OFF)
-- enable
ALTER SERVER AUDIT [AuditTest2] WITH (STATE=ON)

5. Change permission

DENY SELECT on dbo.testA to testLogin
REVOKE VIEW DEFINITION ON dbo.testA to testLogin
GRANT ALTER ON dbo.testA to testLogin

6. Read Audit Logs:

SELECT * FROM sys.fn_get_audit_file (N'C:\_data\AuditTest_*.sqlaudit', DEFAULT, DEFAULT);

Output:

event_time                  sequence_number action_id   succeeded   permission_bitmask                  is_column_permission    session_id  server_principal_id database_principal_id   target_server_principal_id  ...
2016-01-25 13:50:36.3210127 1               D           1           0x00000000000000000000000000000001  0                       57          260                 1                       271                         ...
2016-01-25 13:50:36.3210127 1               R           1           0x00000000000000000000000000000100  0                       57          260                 1                       271                         ...
2016-01-25 13:50:36.3210127 1               G           1           0x00000000000000000000000000000200  0                       57          260                 1                       271                         ...

Note that I am using SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP in place of DATABASE_OBJECT_PERMISSION_CHANGE_GROUP. You can read more from SQL Server Audit Action Groups and Actions toward the middle of the page:

  This event is raised whenever a grant, deny, revoke is performed against a schema object. Equivalent to the Audit Schema Object GDR Event Class.


Two-way replication between SQL Server 2008 R2 and SQL Server 2008 R2 SP3

Those aren't different versions just different service packs. Replicating between 2008 and 2008 R2 would better fit that definition of replication between two specific versions.

So The way I understand the MSDN article on the subject here is this

  When you replicate between or among different versions of SQL Server,
  you are usually limited to the functionality of the earliest version
  used. For example, if you upgrade a Distributor to an instance of SQL
  Server 2008, but you have a Publisher that is running an instance of
  SQL Server 2005 and a Subscriber that is running an instance of SQL
  Server 2000, you are limited to the general functionality and
  replication functionality of SQL Server 2000.

And here is something more specific to merge replication

  For merge replication, a Subscriber to a merge publication can be any
  version no later than the Publisher version. For more information
  about compatibility for earlier versions, see "Compatibility Level for
  Merge Publications"

In reality though, even if you can replicate between different versions, that can't be ideal. Replication is already complex enough by itself without adding in yet another layer of complexity.

So if you can apply those service packs to the other servers, you should in my opinion.


How do I replicate a database over 2 geographical sites?

Based on the answers in the comment section, you are looking at a bidirectional transactional replication.

Microsoft document its implementation at this location:
https://support.microsoft.com/en-us/kb/820675

Using the name "Bidirectional Transactional Replication" in your research should help you identify how-tos and step by step tutorials.

Since it's bidirectional replication, you will experience conflicts (a row updated here and there at the same time).

The document explains all of that very well. It's a good start.


I think this is a better, safer, more self-documenting, and more scalable approach:
IF @ApplicationType IN ('Clinical','Patients')
BEGIN
  SET @whereClause += N' AND vpgl.AppType = @ApplicationType';
END

IF @ApplicationType = 'Web'
BEGIN
  SET @whereClause += N' AND vpgl.AppType IS NULL';
END

...
SET @sql = @sql + @whereClause;

EXEC sys.sp_executesql @sql,
     N'@ApplicationType varchar(32)',
     @ApplicationType;

If you really, really want to use a CASE expression (which is not meant for control of flow), you can say:
SET @whereClause += CASE

   WHEN @ApplicationType IN ('Clinical','Patients')
   THEN N' AND vpgl.AppType = @ApplicationType'

   WHEN @ApplicationType = 'Web'
   THEN N' AND vpgl.AppType IS NULL';

   ELSE N'' END;

...
SET @sql = @sql + @whereClause;

EXEC sys.sp_executesql @sql,
     N'@ApplicationType varchar(32)',
     @ApplicationType;


Is it possible to output multidimensional data from linear table without iteration?

I think you just need include the month in the GROUP BY:

SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
  MONTH(m.rideDate) as monthNumber,
  SUM(mileage) as miles,
  COUNT(*) as cnt
FROM `mileage` m
    INNER JOIN `members` w ON (w.uid = m.riderUid)
WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate')
GROUP BY m.riderUid, w.firstName, w.lastName, w.isMale, MONTH(m.rideDate)
ORDER BY MONTH(m.rideDate), m.riderUid

If the date range is longer than a year, then months from different years will be folded together, so replace MONTH(m.rideDate) with LEFT(m.rideDate, 7) as suggested by @RickJames.

If you want the months as columns, then you need to "pivot" the months, see e.g. this answer on Stackoverflow. So that might give you a query with a subquery similar to the above, something like:

SELECT riderUID, firstName, lastName, isMale,
CASE WHEN monthNumber = 1 THEN miles END AS JanuaryMiles,
CASE WHEN monthNumber = 1 THEN cnt END AS JanuaryCnt,
CASE WHEN monthNumber = 2 THEN miles END AS FebruaryMiles,
CASE WHEN monthNumber = 2 THEN cnt END AS FebruaryCnt,
CASE WHEN monthNumber = 3 THEN miles END AS MarchMiles,
CASE WHEN monthNumber = 3 THEN cnt END AS MarchCnt,
CASE WHEN monthNumber = 4 THEN miles END AS AprilMiles,
CASE WHEN monthNumber = 4 THEN cnt END AS AprilCnt,
CASE WHEN monthNumber = 5 THEN miles END AS MayMiles,
CASE WHEN monthNumber = 5 THEN cnt END AS MayCnt,
CASE WHEN monthNumber = 6 THEN miles END AS JuneMiles,
CASE WHEN monthNumber = 6 THEN cnt END AS JuneCnt,
CASE WHEN monthNumber = 7 THEN miles END AS JulyMiles,
CASE WHEN monthNumber = 7 THEN cnt END AS JulyCnt,
CASE WHEN monthNumber = 8 THEN miles END AS AugustMiles,
CASE WHEN monthNumber = 8 THEN cnt END AS AugustCnt,
CASE WHEN monthNumber = 9 THEN miles END AS SeptemberMiles,
CASE WHEN monthNumber = 9 THEN cnt END AS SeptemberCnt,
CASE WHEN monthNumber = 10 THEN miles END AS OctoberMiles,
CASE WHEN monthNumber = 10 THEN cnt END AS OctoberCnt,
CASE WHEN monthNumber = 11 THEN miles END NovemberMiles,
CASE WHEN monthNumber = 11 THEN cnt END NovemberCnt,
CASE WHEN monthNumber = 12 THEN miles END AS DecemberMiles,
CASE WHEN monthNumber = 12 THEN cnt END AS DecemberCnt
FROM (
    SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
      MONTH(m.rideDate) as monthNumber,
      SUM(mileage) as miles,
      COUNT(*) as cnt
    FROM `mileage` m
      INNER JOIN `members` w ON (w.uid = m.riderUid)
    WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate')
    GROUP BY m.riderUid, w.firstName, w.lastName, w.isMale, MONTH(m.rideDate)
) q1
ORDER BY riderUid

See here for db-fiddle example. Note: If running this query through phpMyAdmin, you may have to embrace each instance of CASE WHEN ... END in parentheses in order to workaround a bug in phpMyAdmin.

(I suspect the above can also be re-written more elegantly without the subquey using window functions, though that requires MySQL 8.0+ or MariaDB 10.2+.)

A couple of other tips:

Include all the fields you SELECT in your GROUP BY - this is required by default in newer MySQL versions.
Also, surely you can use INNER JOIN instead of OUTER JOIN in this case? Or do you have mileage for members who are no longer in the members table? If so, you may want to keep the LEFT JOIN.
Also, InnoDB is a better choice than MyISAM in most use-cases now, and MyISAM is discouraged in MySQL 8.0+.


MYSQL Slow SELECT with multiple joins and ORDER BY

The issue that jumps out at me is the first table in your EXPLAIN:

1   SIMPLE  m3_ const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort

As you've noticed, if you remove the GROUP BY...ORDER BY..., the query runs much faster. Doing so most likely removes the Using temporary;Using filesort (but I can't know for sure without the CREATE TABLE statements.

I would suspect this is creating the temporary table on disk. See this link on how MySQL uses temporary tables.

First, if you are selecting any BLOB or TEXT columns, this will automatically be an on-disk temporary table. From your list, it looks like r0_.vehicle_offer_description AS vehicle_offer_description6 could fit the bill.

If this is the case, try removing that column and see if there is any noticeable difference.


MySQL: Join one Row in a table with two Rows in other table

Perhaps I'm misunderstanding your request, but based on the text in your question that sounds simple enough:

SELECT
    U.first_name AS "User Name",
    U.email_id AS "User E-Mail Address",
    BossUser.first_name AS "Authorizing User Name"
    BossUser.email_id AS "Authorizing User E-Mail Address"
FROM
    user_login AS UL
    INNER JOIN users AS U ON UL.user_id = U.user_id
    LEFT JOIN  user_login AS BossLogin ON U.created_by = BossLogin.user_name
    LEFT JOIN  users AS BossUser ON BossLogin.user_id = BossUser.user_id

This allows for users without a created_by; if that field is NOT NULL, you can replace the LEFT JOINs with INNER JOIN.


Sync logins and Agent jobs across all replicas in Distributed Availability Group

For Database level permissions:
Maintain SID of the Login same across all replicas (SQL Instances). I think, you don't have to even maintain Login sync jo. Instead, create login using Registered Servers using following steps, as long as the SID remain same, the database permissions assigned in Primary replica would automatically synchronized into all secondary (including forwarder) replicas.
Step1: Create login in DEV SQL Instance
Step2: Get the SID of Login (from DEV)
Step3: Create login in Production Instances using Registered Servers
Create Login MyLogin with password = 'xxxx', SID=0x000000000000;

Step4: Assign database level permissions for the login (Only in primary replica)
For SQL Agent Jobs:
You can use anyone of approach:

Script-out and execute via Registered servers.
If it feasible in your environment, you can configure multi-server administration feature.


Why do I have to drop a snapshot in SQL Server 2012 to restore a database?

Actually, you did have to drop the snapshot before the restore.  Here's the Books Online post about it:

http://msdn.microsoft.com/en-us/library/ms189940(v=sql.105).aspx

In particular, "The database cannot be dropped, detached, or restored."

You might have been using a restore script or tool that automatically dropped the snapshots for you.  (My own restore database script does that - checks for snapshots first and deletes 'em.)


Your query includes a lot of tables and columns that aren't listed in your sample data. From this query you can join and pull in other things:

;WITH lastMessage AS
(
    SELECT Id, IdMain, IdReply, DatePosted, rn = ROW_NUMBER() OVER
      (PARTITION BY COALESCE(IdMain, Id) ORDER BY DatePosted DESC, Id)
    FROM dbo.Message
)
SELECT t.Id, t.IdMessage, m.IdMain, m.IdReply, m.DatePosted
  FROM lastMessage AS m
  INNER JOIN dbo.Target AS t
  ON m.Id = t.IdMessage
  WHERE m.rn = 1;

SQLfiddle demo


MySQL ran out of disk creating an index, now I can't reclaim my disk

CREATE INDEX

If the files #sql-2b9f_2178.* were left behind by the failed operation, rm them.

The CREATE INDEX was copying the table over to create the index.  This is the new copy, before it is swapped into place.

Two important bits of info:  What ENGINE is used for the table?  What version of MySQL?  There have been a number of changes in this area; I suspect you have an antique version and/or are using MyISAM.  Recommend upgrading.

Deleting

Do you have any index on the table?  If so, then the following blog has a number of techniques that may be viable for helping clean up the table:  Big Deletes .

However, there may still be an issue of recovering disk space.  So...

If the amount of data you need to keep is small enough to fit, then focus on this part of the blog:  Deleting more than half a table .

Still, there may be a problem.

If MyISAM, no problem -- simply DROP the old table and RENAME TABLE.

If InnoDB, then you really need to be using innodb_file_per_table = ON.  Set this globally, and re-login in order to get the value.

But... There may still be problems.  We can continue this discussion after you have answered my questions.


Oracle impdp - Importing file from remote server

From Oracle Data Pump utilities:

Dump files are read and written directly by the server and, therefore, do not require any data movement to the client.

impdp/expdp unlike imp/exp does not move the data. They only invoke DBMS_DATAPUMP package and actual data movement is done by the Oracle instance. So data pump can access external data the same way as all other Oracle procedures - loading files via directory object or SELECT data via dblink.


Two separate column counts grouped by one of those columns

To count rows:

COUNT(*)

To count non-null values in a column:

COUNT(email)

To count non-null values, without counting the empty strings:

COUNT(CASE WHEN email > '' THEN email END)    -- or
COUNT(CASE WHEN email > '' THEN 1 END)        -- or
SUM(email > '')                               -- or
COUNT(email) - SUM(email = '')


How to connect to a SQL Server installed on Windows Server 2012 running on VPS?

In addition to using SQL Server Configuration Manager to enable TCP/IP connections, you need inbound tcp port 1433 and optionally udp port 1434 open at the firewall for SQL Server to accept inbound connections.

1433 is the default T-SQL tcp endpoint, and udp port 1434 is used by the SQL Server Discovery Protocol to locate SQL Server ports by the instance name, for example SQLSERVER\MYINSTANCE.  If you are not using a named instance, 1434 is not required.

See this Microsoft Docs page for further details.


The error message indicates that, in your cluster, there was shard what didn't have primary available and query went to secondary and with the current configuration, a query to secondary was not allowed.

So, what was a reason that there was no primary? An answer to that is the key to solve the problem.

Can you check (by login to every RS) that every sharded replica set have primary available? Second possibility is that config servers don't have up-to-date information of your shards configuration... But that should not be possible if your cluster works other ways fine.


Thanks to Evan Carrol I figured I simply needed to run # VACUUM FULL ANALYZE store; on my table (which was named store) and then database started using the index on queries.


How to guard against backup corruption due to spurious bit errors?

It is possible to detect such errors using backup checksums.

If you use the CHECKSUM option, then yes it should be found but only when using a RESTORE or RESTORE ... WITH VERIFYONLY. If you don't check it, you'll never know.

  But what do you do when an error is found?

That depends on what is wrong and where it happened. The action to be taken first is to make sure your entire restore sequence is still valid and can be actioned. The second is to figure out what caused the corruption and potentially move to different storage, drivers, etc. In this case, chances aren't that great that it will happen very often if at all.

  If a log backup is corrupted the log backup chain is broken.

This may or may not be true depending on your restore strategy. If you have a differential to bridge the gap, that's perfectly valid and you can still use the other log backups that occur after it. If you're extremely worried about single bit flip in a log backup, mirror the log backups to another storage solution.

  If it is a full backup depending differential backups are now
  unusable. A new full backup is needed in order to restart the usual
  backup schedule.

You're correct on the first part and I don't know what you are considering the "usual" backup schedule. If you have the log backups, you can continue to roll forward through the bad full and covering differentials. You're definitely going to want more than a single recovery sequence.

  What can be done to avoid losing backups randomly due to spurious data
  corruption?

Mirror your backups, for starters. Have more than a single recovery sequence. Check the backups and take action when something does go wrong so that you're not exposed to risk. Disks die, things go wrong. While it shouldn't be happening often, it will most likely happen to you at some point or another in your career. Having a good restore and backup strategy to take care of this is what will help.


Upgrade from MariaDB 10.3 to MariaDB 10.4 with Galera Cluster - mysql.global_priv table

Need to run mysql_upgrade in any upgrade.


Try this - there are two examples.  The first result (jobid=100) has both and open and a closed logdate.  The second result (jobid=200) only has an open logdate.  Take note of the LEFT JOIN against the 'closed' statuses.

--demo setup
DECLARE @customers TABLE (
    customer_id INT
    ,customer_name VARCHAR(100)
    )
insert into @customers(customer_id,customer_name) values(1,'bob')

DECLARE @jobs TABLE (
    job_id INT
    ,job_number VARCHAR(10)
    ,job_description VARCHAR(MAX)
    ,customer_id INT
    )
insert into @jobs(job_id,job_number,job_description,customer_id) values
(1,'100','jobdesc',1),(2,'200','jobdesc2',1)

DECLARE @job_statuses TABLE (
    status_id INT
    ,status_description VARCHAR(50)
    )
insert into @job_statuses(status_id,status_description) values(1,'open'),(2,'closed')

DECLARE @job_status_logs TABLE (
    log_id INT
    ,job_id INT
    ,status_id INT
    ,log_date DATE
    )
insert into @job_status_logs(log_id, job_id,status_id,log_date) values
(1,1,1,'2018-10-01'),(2,1,2,'2018-11-01'),(3,2,1,'2018-12-01')

--the solution
--parameter passed into SP
DECLARE @JobNumber INT

SET @JobNumber = 100    --has both open and closed status

SELECT j.job_id
    ,j.job_number
    ,j.job_description
    ,c.customer_name
    ,jslo.log_date AS LogOpen
    ,jslc.log_date AS LogClosed
FROM @jobs j
JOIN @customers c
    ON c.customer_id = j.customer_id
JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'open'
    ) jslo
    ON jslo.job_id = j.job_id
LEFT JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'closed'
    ) jslc
    ON jslc.job_id = j.job_id
WHERE j.job_number = @JobNumber

---------------------------

SET @JobNumber = 200    --has only open status

SELECT j.job_id
    ,j.job_number
    ,j.job_description
    ,c.customer_name
    ,jslo.log_date AS LogOpen
    ,jslc.log_date AS LogClosed
FROM @jobs j
JOIN @customers c
    ON c.customer_id = j.customer_id
JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'open'
    ) jslo
    ON jslo.job_id = j.job_id
LEFT JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'closed'
    ) jslc
    ON jslc.job_id = j.job_id
WHERE j.job_number = @JobNumber

Result1

| job_id | job_number | job_description | customer_name | LogOpen    | LogClosed  |
|--------|------------|-----------------|---------------|------------|------------|
| 1      | 100        | jobdesc         | bob           | 2018-10-01 | 2018-11-01 |

Result2

| job_id | job_number | job_description | customer_name | LogOpen    | LogClosed |
|--------|------------|-----------------|---------------|------------|-----------|
| 2      | 200        | jobdesc2        | bob           | 2018-12-01 | NULL      |


Single query for retrieving a category path

Assuming that the values in the name column are consistent with the parent_id values (and the resulting tree), you can use a triangular join and GROUP_CONCAT():

SELECT
    t.category_id,
    GROUP_CONCAT(anc.name ORDER BY anc.name
                 SEPARATOR ' > ') AS path
FROM tablename AS t                         -- for every row in t
  JOIN tablename AS anc                     -- find its ancestors
    ON t.name LIKE CONCAT(anc.name, '%')
GROUP BY
    t.category_id ;

Test in SQLfiddle.


Restore Log and Differential Backups Simultaneously in MS SQL Server

Use tsql. Also, highly recommend to use sp_restoregene. This will generate restore commands for you which you can just execute .


Minimizing Indexed Reads with Complex Criteria

The degradation over time occurs due to the increased number of items that are in the "Complete" status. Think about this for a second - you won't get any performance degradation when testing as you probably have a small number of rows with the status as "Complete". But in production, they may have millions of rows with the "Complete" status and this number will increase over time. This, essentially, makes your index on Status less and less useful over time. As such, the database probably just decides that because Status almost always has the value 'Complete', it will just scan the table instead of using the index.

In SQL Server (and maybe other RDBMS's?), this can be worked around using Filtered Indexes. In SQL Server you would add a WHERE condition onto the end of your index definition to say "apply this index only to records with a Status <> 'Complete' ". Then any query using this predicate will most likely use the index on the small amount of records not set to 'Complete'. However, based on the documentation here: http://www.firebirdsql.org/refdocs/langrefupd25-ddl-index.html, it does not look like Firebird supports filtered indexes.

A workaround is to put 'Complete' records in an ArchiveTickets table. Create a table with the exact same definition (though without any auto generated ID) as your Tickets table and maintain rows between them by pushing 'Complete' records to the ArchiveTickets table. The Index on your Tickets table will then be over a much smaller number of records and be much higher performance. This will likely mean you will need to change any reports etc that reference 'Complete' tickets to point to the Archive table or perform a UNION across both Tickets and ArchiveTickets. This will have the advantage of not only being fast, but will also mean that you can create specific indexes for the ArchiveTickets table to make it perform better for other queries (for instance: Give me the average time to completion for complete tickets) which are not needed on the Tickets table.

You should be concerned with this if your production is going to go into the thousands of rows. Performance will degrade over time and negatively impact your user experience.


Dynamic casting from text within plpgsql

My own solution so far is to paste the string literal into the query:

EXECUTE format(
  'UPDATE %I SET %I = ' || quote_literal(newvalue) || ' WHERE %I = $1 ',
  relname, colname, relname || '_id') USING row_id;

or just

EXECUTE format(
  'UPDATE %I SET %I = %L WHERE %I = $1',
  relname, colname, newvalue, relname || '_id') USING row_id;

This works for, e.g., appropriately formatted date types ('1990-05-04'). Probably this sacrifices being able to re-use query plan.


Answer based on clarifications in comments
The question, really, is giving two data scenarios and saying in the first case, all rows should be returned in a select because when you order the rows by lineID and keep track of the cumulative sum of the Amount value then any time the BusinessUnit value changes if the cumulative sum of the Amount value is not zero, return the rows. In the second scenario, the cumulative sum for Amount is zero every time the Business Unit value changes when ordered by LineID - so no row is returned.
There are two ways to do this.
You can write a cursor that will iterate through your rows one by one and use local variables to store values, track the cumulative sum and note which rows fail the check, then somehow store those rows in a temporary table and return the table contents at the end.
However, as someone noted in comments, the strength in a database lies in set operations - dealing with sets of data at a time. So...
The second way to do this is as a "single" statement. This is really an aggregation of multiple statements - but they are all selects. The approach below makes use of the T-SQL lag function, which can read values from prior rows in an ordered result set. This function requires a partition clause - which allows us to create "windows" on our data - but we don't actually need those windows - we are happy to treat the whole dataset as a single window. So I guess ultimately this also processes rows one by one, but using T-SQL's native function rather than writing our own cursor.
Last note before the solution - you have a column [Value] which contains the word "Apple" on every row. It seems irrelevant to the question, so I have ignored it. If this column affects the behviour you are seeking, you will have to adjust the below SQL suitably to deal with your [Value] column.
Here is the solution - including data setup and tear down for each of your two scenarios.
Important! - The lineID value for the failing rows is in the column priorLineID (and not lineID)
This is because we are "reading behind" with the lag function - so we don't know if the businessUnit has changed until we get to the next row and look back at the prior one. At that time we know the businessUnit changed so we test whether the prior cumulative total was zero and if not, return the current row and provide the priorLineID in its own column. You can expand the SQL to return whatever prior row data values you need.
delete from test;
go

-- TEST CASE 1 - all rows returned because the cumulative total for Amount is not zero
--  when BusinessUnit changes, when ordered by LineID

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'DEF', 'Apple', 40.00 UNION
SELECT 3, 'ABC', 'Apple', -20.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

select * from test;

with EXPANDED_DATA as (
select lineID, BusinessUnit, value, amount from test
union select 999999999,'','',0
),
PARTITIONED_DATA as (
select
lineID,
BusinessUnit,
AMount,
lag(BusinessUnit,1,'') over (partition by 1 order by lineID) as priorBusinessUnit,
lag(lineID) over (partition by 1 order by lineID) as priorLineID,
lag(amount) over (partition by 1 order by lineID) as priorAmount
 from EXPANDED_DATA
)
,
WITH_PRIOR_CUMULATIVE_AMOUNT as (
select *,
case when priorBusinessUnit = businessUnit then priorAmount + amount else amount end as CumulativeBusinessUnitTotal
from PARTITIONED_DATA
),
WITH_PRIOR_TOTALS as (
select *,
lag(CumulativeBusinessUnitTotal) over (partition by 1 order by lineID) as priorCumulativeTotal
 from WITH_PRIOR_CUMULATIVE_AMOUNT
)
select * from WITH_PRIOR_TOTALS
where BusinessUnit <> priorBusinessUnit and priorCumulativeTotal <> 0

delete from test;
go

-- TEST CASE 2 - no rows returned because the cumulative total for Amount is zero
--  when BusinessUnit changes, when ordered by LineID

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'ABC', 'Apple', -20.00 UNION
SELECT 3, 'DEF', 'Apple', 40.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00

select * from test;

with EXPANDED_DATA as (
select lineID, BusinessUnit, value, amount from test
union select 999999999,'','',0
),
PARTITIONED_DATA as (
select
lineID,
BusinessUnit,
AMount,
lag(BusinessUnit,1,'') over (partition by 1 order by lineID) as priorBusinessUnit,
lag(lineID) over (partition by 1 order by lineID) as priorLineID,
lag(amount) over (partition by 1 order by lineID) as priorAmount
 from EXPANDED_DATA

)
,
WITH_PRIOR_CUMULATIVE_AMOUNT as (
select *,
case when priorBusinessUnit = businessUnit then priorAmount + amount else amount end as CumulativeBusinessUnitTotal
from PARTITIONED_DATA
),
WITH_PRIOR_TOTALS as (
select *,
lag(CumulativeBusinessUnitTotal) over (partition by 1 order by lineID) as priorCumulativeTotal
 from WITH_PRIOR_CUMULATIVE_AMOUNT
)
select * from WITH_PRIOR_TOTALS
where BusinessUnit <> priorBusinessUnit and priorCumulativeTotal <> 0

Original answer below
(Originally the question seemed to be about validating the insert statements at the time of insertion and I basically made the comment that you really can't do that. I will leave this part of the answer below).
If I understand your question correctly, you are saying the first attempt at insert is invalid because of the sequence of the select statements fails a business rule, and that the second attempt at insert is valid because the sequence of select statements passes that business rule. Is this correct?
If so, note that your select statements have been "joined together" (effectively), using union statements. This means your selection of four rows of data is carried out as a single statement - and there are no guarantees about the order in which those select statements will be processed.
The only difference between your first and second insert - as far as the database is concerned - is that the lineID value varies between the two statements, for a given combination of data.
However the more important implication to you is that it seems you want the database to validate something that it is not designed to validate - re-read my comment that there is no real difference between those two inserts, as far as the database is concerned.
Reading between the lines, I am wondering if your list of select statements is being generated by application code? If so, I would suggest your application should be validating the values it is appending to the query. That said, regardless of the order of rows, the net result (as far as the database is concerned) will be the same (notwithstanding the lineID difference).


creating a 24 hours data table - Suggestions/improvements

Your entities are based on hours, not days, so you should go with one row per hour, just like you did, not a full day in a row.

I did not understand if when you say data between two timestamps, if timestamp refers to the "inserted" column, or if it refers to the date and times on the data file. If it is the latter one, then having a datetime column for your date and hour, instead of two separate columns, would help you doing your comparisons. You would place an index on this datetime column and when you'd want to get data between two datetimes, or even one specific datetime, it would give you the results ultra-fast.


CREATE UNIQUE INDEX your_unique_index ON your_table(b, c, d);

Create an index instead. It has the same as the unique constraint.


How do I show the number of tables in all databases?

You can use the information_schema.tables

select table_schema, count(1)
from information_schema.tables
group by table_schema


can we call CRUD operations as service methods?

Sometimes when people do this (abstract the SQL layer) it's because they want to (maybe in the future) use multiple database backends (with their own code).

Sometimes, it's as a result of a framework abstraction which occurs for the same reason - i.e. those developing the framework want to promise "all things to all men" - i.e. don't worry about SQL - we'll take care of that for you.

See this thread in response to a related question (in particular the accepted answer and also mine) - the perils of database "abstraction layers" - basically they lead to "lowest common denominatorism".

Using these for standard CRUD operations can make sense, but it's when you start trying to optimise complex queries (big differences between different servers) that these "panaceas" become worse than the illness they're trying to cure (my database is sick - i.e. slow).


Why do these similar queries use different optimisation phases (transaction processing vs quick plan)?

Each stage has entry conditions. "Having at least three table references" is one of the entry conditions we talk about when giving simple examples, but it's not the only one.

Generally, only basic joins and unions are allowed for entry to search 0; scalar subqueries, semi joins etc. prevent entry to search 0. This stage really is for the very common OLTP-type query shapes. The rules necessary to explore the less common things just aren't enabled. Your example query has a scalar subquery, so it fails entry.

It also depends how you count table references. I've never looked deeply into this with functions, but it's possible the logic is counting the Table Valued Functions as well as the table variables they produce. It could even be counting the table reference inside the function itself - I'm not sure; though I do know that functions are just hard work all round.

The bug with GenGbApplySimple is ugly. This plan shape was always a possibility, but rejected for cost reasons until the change to 100-rows assumed table variable cardinality came in. It is possible to force the problematic plan shape on the pre-2014 CE with a USE PLAN hint, for example.

You're correct about the new Connect item being the same issue reported previously.

To provide an example, the following query qualifies for search 0:

DECLARE @T AS table (c1 integer NULL);

SELECT U.c1, rn = ROW_NUMBER() OVER (ORDER BY U.c1)
FROM
(
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
) AS U;

Making a small change to include a scalar subquery means it goes straight to search 1:

DECLARE @T AS table (c1 integer NULL);

SELECT U.c1, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -- Changed!
FROM
(
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
) AS U;


Declare @SelectSql nvarchar(max), @InsertSql nvarchar(max);

SELECT @SelectSql = COALESCE(@SelectSql + ' UNION ALL ', '') +
                'Select '''+employeename+''' As employeename, datesold, itemsold, COUNT(itemsold)  As [totalsales] '
                +'FROM '+employeename+' '
                FROM storeinfo;
-- print @SelectSql;
-- Exec(@inlinesql);

SET @InsertSql = 'Insert into holdingtable ' + @SelectSql;

print @InsertSql;
exec (@InsertSql);

Not sure wheather it will help with performance, but surely is more elegant than cursor.

Edit (as requested by Andriy):

If it takes so long, consider aggregating data more often, I mean divide the work to smaller chunks. For example you can every hour take only the data that was added during this time, aggregate, add to the table. This would require more work. Maybe you will need include some table for pre-aggregations -> aggregate every hour to HourlyHoldingTable, then every nigth aggregate this much smaller table to the DailyHodlingTable. Or only add a comumn with a number of rows aggregated so far, to enable correct update. Many solutions possible here. The general idea is to split the big task to more smaller tasks.

Or you could have an indexed view with aggregation for each table.

The problem is, we don't have enough information - the query you pasted is not complete (missing group by), so it is hard to tell wether problem is in grouping huge number of rows, or in inserting loads of rows.


It is more or less the same as moving the data files for a user database, but
you can follow the steps in the documentation To Move the SSIS Database as it has some additional good precautions noted:

Follow the instructions for moving user databases. For more information, see Move User Databases.
Ensure that you back up the master key for the SSISDB database and protect the backup file. For more information, see To Back up the SSIS Database.


RedGate SQL Prompt - Cursor Snippet mistake

YES, it is incorrect.  The inner FETCH NEXT should go at the END of the loop.  The code above would result in the following:

Hello, I am # 2
Hello, I am # 3
Hello, I am # 4
Hello, I am # 5
Hello, I am # 5

Correcting the problem, and putting the FETCH NEXT at the end of the loop, results in the correct & expected output:

Hello, I am # 1
Hello, I am # 2
Hello, I am # 3
Hello, I am # 4
Hello, I am # 5

Now to go submit this to RedGate...


Create view to know missing records between two tables

This should do it if you are using Microsoft SQL Server:

CREATE VIEW SomeView
AS
SELECT ID, ORDER, ASSEMBLY
FROM Tab_A a
WHERE NOT EXISTS (SELECT NULL FROM Tab_B b WHERE a.ID = b.ID);

If you want to know how many are different, then:

SELECT COUNT(*) FROM SomeView;


You may proceed with something like creating a proxy account and then assigning the same to SQL Agent job:

Assuming you have Maintenance plan created, which will have its corresponding job created out there on Job activity. Create a SQL login which later you need to give the required roles to execute the SQL agent jobs and assign this login as job owner of the Main'ce plan job
Before you create a proxy account you will need to create a credential as explained in How to: Create a Credential
Now for the SQL login created earlier assign the roles out of below as per you're security standards:

SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole

Once login has been created with sufficient roles as above you can
create proxy account as explained How to: Create a Proxy. In the ‘Principals’ option, select the SQL login which was created in above step.
Now the final step would be to run this proxy account in the job step
properties of the maintenance plan. You can select the proxy created from dropdown under the section which says "Run As".
Finally test the proxy by executing the job.


mysql - order by first condition in where clause

This is quite a common problem! A case expression helps here. A query something like (untested)

select *
from table
where name like '%ci%'
order by case when name like 'ci%' then 0 else 1 end, name;

should do what you want.


Group by range DATIME of 2h up or down - Mysql

Sorry Woton, but your answer has several issues.

First of all, your usage of GROUP BY is not right. With GROUP BY you collapse several rows of a group to one. To get a meaningful result, all columns of your SELECT clause must either be included in the GROUP BY clause, or an aggregate function like MIN() or AVG() or SUM() has to be applied on it. Otherwise a random row of this group is displayed. So a SELECT * FROM foo GROUP BY bar is as good as always wrong. To make sure, you're doing it right, you could execute SET GLOBAL sql_mode = CONCAT(@@global.sql_mode, ',ONLY_FULL_GROUP_BY'); (for further information you can read this part of the manual)

The second issue with your query is, that the HAVING clause is a filter on the result of GROUP BY. Filters on columns that you are grouping by would be in the WHERE clause. Filters in the HAVING clause include an aggregate function, like HAVING SUM(foo) > 5000.
When you don't use an aggregate function, again a random row is used. Your HAVING clause therefore actually filters nothing at all. x is always greater than x - 5 and x is always smaller than x + 5.

A filter that would work in your case (though I don't think this is what you want) would for example be HAVING TIMESTAMPDIFF(MINUTE, MIN(horaIfull_oco), MAX(horaIfull_oco)) <= 150. This isn't what you want, right?

If I understood you correctly, you want to build a group when consecutive rows are within a certain timerange, right? For this we would have to generate a column first, which we can later use to group on.

Let's do that.

Here's your query, explanation follows:

SELECT sinistro, prefixo, data
/*whatever else you need*/
, MIN(horaIfull_oco), MAX(horaIfull_oco), MIN(id_oco) AS id_oco, GROUP_CONCAT(inclusao_oco) AS incl
FROM (
    SELECT
    t.*
    , @group_number := IF(
        @prev_sinistro != sinistro
        OR @prev_prefixo != prefixo
        OR @prev_data != data
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        , @group_number + 1
        , @group_number) AS gn
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    , @prev_data := data
    , @prev_horaIfull_oco := horaIfull_oco
    FROM your_table t
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v
    ORDER BY sinistro, prefixo, data, horaIfull_oco
) sq
GROUP BY sinistro, prefixo, data, gn

First thing to notice, in a relational database there is no order of rows unless you specify it. So we ORDER BY the columns we want to use in our GROUP BY in the outer query and then by the timestamp/datetime column. The goal is, that we can use variables to access the previous row.
We don't want to execute the queries SET @prev_sinistro := NULL; SET @prev_prefixo := ... everytime before we execute the main query, so we initialize the variables in a subquery aliased with v here.
Then, every time sinistro or prefixo or data is different from the previous row, or when the difference between the previous rows horaIfull_oco and the value for this column in the current row is greater than 150 minutes, we increment the @group_number variable, if not we don't (we just assign the current value of the variable to the variable).
With @prev_prefixo := sinistro and so on we assign the value of the current row to the variable. It's important, that this is done after the IF() function. The SELECT clause is processed sequentially. When the next row is processed, the variables still hold the value of the previous row.

In the outer query finally, we can group by the generated gn column.

EDIT: To meet your further requirements, you could do this:

SELECT sinistro, prefixo, data, MIN(horaIfull_oco), MAX(horaIfull_oco)
FROM (
    SELECT
    sinistro, prefixo, data, horaIfull_oco
    , @group_number := IF(
        @prev_sinistro != sinistro
        OR @prev_prefixo != prefixo
        OR @prev_data != `data`
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)
        , @group_number + 1
        , @group_number) AS gn
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    , @prev_data := `data`
    , @prev_horaIfull_oco := horaIfull_oco
    FROM ocorrencia t
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v
    ORDER BY sinistro, prefixo, data, horaIfull_oco
) sq
GROUP BY sinistro, prefixo, data, gn;

EDIT 2:

So, I excluded data and used this query:

SELECT sinistro, prefixo -- , data
/*whatever else you need*/
, MIN(horaIfull_oco), MAX(horaIfull_oco), GROUP_CONCAT(id_oco) AS id_oco
FROM (
    SELECT
    id_oco,
    sinistro, prefixo, /*data,*/ horaIfull_oco
    , @group_number := IF(
        @prev_sinistro != sinistro
        OR @prev_prefixo != prefixo
        -- OR @prev_data != data
        OR TIMESTAMPDIFF(MINUTE, @prev_horaIfull_oco, horaIfull_oco) >= 150
        OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)
        , @group_number + 1
        , @group_number) AS gn
    , @prev_sinistro := sinistro
    , @prev_prefixo := prefixo
    -- , @prev_data := data
    , @prev_horaIfull_oco := horaIfull_oco
    FROM ocorrencia t
    , (SELECT @prev_sinistro := NULL, @prev_prefixo := NULL, @prev_data := NULL, @prev_horaIfull_oco := NULL, @group_number := 0) v
    -- having id_oco in (254, 277)
    ORDER BY sinistro, prefixo, /*data,*/ horaIfull_oco
) sq
GROUP BY sinistro, prefixo, /*data,*/ gn

Regarding your problems with id_oco 254 and 277, when you include

OR DATE(@prev_horaIfull_oco) != DATE(horaIfull_oco)

in the IF() function, these 2 records are in separate groups, if you don't they are in the same group because they are within the 2.5 hours range.

If you still have problems, please provide the desired result based on your sample data as well, to avoid further confusion.