Does index need to cover all columns selected?

Short answer:  No.

Long answer:

A "covering" index is usually better than a non-covering one.
A covering index for the query you mention would contain all the columns in the table.  This is usually a 'bad' idea if there are more than, say, 5 column.

But...  I suspect that is not the real problem, nor the real question.

What is the DATATYPE of phonenumber?  I suspect it is VARCHAR(...), correct?  Yet you are asking for a numeric comparison by saying where phone_number = 98371381.

Add quotes (and make sure it is indexed):  where phone_number = "98371381".  Based on the table name, I suspect that the that is the PRIMARY KEY?  If so, simply PRIMARY KEY(phone_number) is better than any "covering" index.

Always provide SHOW CREATE TABLE when asking performance questions.  Else, we have to guess at too many things (datatype, PK, number of columns, engine, etc).


Upgrade production database during software development lifecycle

Advice

Never manually apply database changes; use scripts that are in a version control system (VCS).

After initial deployment, store the "delta" scripts in the VCS. When upgrading, apply all scripts that are missing. Tracking the scripts that have been applied is a standard problem.

Solutions

There are tools to automate this task, including:

Liquibase
Flyway

Liquibase

We are pretty satisfied with Liquibase, which stores the schema definition in XML files. A commandline tool then applies a "changelog" to the database. The changelog can be applied to all environments: development, test, staging, production, continuous build, etc.

Flyway

Also consider Flyway, which uses SQL text files with a naming convention.

Other Considerations

The downside of Liquibase and Flyway is that you basically manage an incremental script. If you need to create a database from scratch, you need to run all change sequentially.

Data Modeling Tool

Document your data model using an ER Modeling tool, such as:

DbSchema
TOAD Data Modeler
Erwin

We use a macro to convert TOAD-based models into a Liquibase changelog. This means we can recreate the database "from scratch" from our model documentation, as well as incremental (Liquibase) scripts that are used to maintain existing installations.


re-enabling a table for mysql replication

I don't use pt-table-sync because I don't agree with the approach it uses:

When synchronizing a server that is a replication slave with the –replicate or –sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly.  This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place. However, the changes it makes on the master should be no-op changes that set the data to their current values, and actually affect only the replica. Please read the detailed documentation that follows to learn more about this.
— http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

My philosophy is "no, that's okay, you guys can go ahead and stay away from the master."
While it's true that a common cause of replication errors are related to things being changed directly on the slave, it's also true that if two servers have an identical set of data at an identical set of binlog coordinates, that's a valid place to begin replication.  (I would also suggest that the other common cause of replication errors is people trying to replicate only some of their tables and not all, which is almost never a good idea, unless you really know what you're doing, in which case, you'll know better than to try it.)
If you do use it, and I'm sure a lot of people do, then I don't think you actually want to stop the slave.  I think you need to keep the slave running to it can make those "no-op changes to the master" and watch them replicate over to fix the inconsistent tables.  I don't know what it does with errors, perhaps it skips over them.
Here is one of the alternative approaches that I use.
You need three console windows for this.  Do not disconnect your session from the master or you will lose the global lock.  You need a console connection to the master and one to the slave, and you need to have your commands to do mysqldump set up and ready to go in a third window.
First, check the master's binlog coordinates and the slave's binlog coordinates to make sure the slave is not lagging.
master-mysql> SHOW MASTER STATUS;

+-----------------------+-----------+--------------+------------------+
| File                  | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+-----------+--------------+------------------+
| pri-master-bin.004421 | 142052212 |              |                  |
+-----------------------+-----------+--------------+------------------+

slave-mysql> SHOW SLAVE STATUS\G

    Relay_Master_Log_File: pri-master-bin.004421
      Exec_Master_Log_Pos: 142052212
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
    Seconds_Behind_Master: 0

There are more values included with SHOW SLAVE STATUS but these are the important ones.  The Exec_Master_Log_Pos needs to be close to or identical to the master, and the log file the same.
Next, obtain a brief read lock on the master.  This locks all of the tables for write allows the master to settle down long enough that we know the slave and master are at the same place in the binlog at this point in time.
master-mysql> flush tables with read lock;
Query OK, 0 rows affected (24.26 sec)

master-mysql>

Note that this sometimes takes a few seconds before your prompt returns.
Do not disconnect this session.  You need this session active because it is holding the lock on the master.  You can use it for the following, but don't close it.
Now, repeat the SHOW MASTER STATUS; on the master and SHOW SLAVE STATUS\G on the slave.  You should find that they have settled down to the same set of binlog coordinates.  Wait until they do.  If they don't settle down quickly, you might want to drop the read lock on the master to figure out why not.
Next:
slave-mysql> STOP SLAVE SQL_THREAD;

This will stop the slave from executing any more events from the master, which is still locked.
In the third window, start a backup of the tables from the master.
shell> mysqldump --verbose --single-transaction database_name table1 table2 table3 table4 > dump.sql

-- Connecting to *hostname*...
-- Starting transaction...
-- Retrieving table structure for table...

As soon as you see "Retrieving table structure..." you can release the global read lock on your master, so your application can start writing to it again.
mysql-master> unlock tables;
Query OK, 0 rows affected (0.01 sec)

If your tables are InnoDB, the --single-transaction option should get a consistent backup from the master at a point in time during the few seconds that you had the global read lock on the master.  Since you also stopped the slave at that point, the slave should be positioned at precisely the appropriate point in time for applying that backup.
When the backup of those tables is complete, apply it to the slave:
shell> mysql --host=slave.host.name database_name < dump.sql

At this point, you have restored the tables onto the slave in a form that should be identical to the way they appeared on the master at the time when the slave was stopped, and replication should resume when you remove the configuration not to replicate those tables, and then:
slave-mysql> START SLAVE SQL_THREAD;

Be sure your backups are solid before you begin.


In my testing, this DID lock the table after I executed the disable for the old trigger up until I committed my transaction.  I had to put the GO statements in before disable/enable to keep SSMS from fussing.

begin transaction
go
--Disable the old trigger
go
--Enable the new trigger
go
--Populate the data to the new table
commit

As far as automatically adding a new column to the history table upon adding a column to the main table, see Aaron Bertrand's post on how this 'might' be achieved (and caveats) using DDL triggers.


1:M relation exclude row if any row in M table does not contain specific value

Plan A:

SELECT a.group
    FROM ( SELECT DISTINCT `group` FROM tbl ) AS a
    WHERE
         EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'ACTIVE' )
     AND
     NOT EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'NOT_BUYABLE' )

Plan B:

SELECT a.group,
       GROUP_CONCAT(status) AS stati
    FROM tbl AS a
    GROUP BY `group`
    HAVING      FIND_IN_SET('ACTIVE',      stati)
        AND NOT FIND_IN_SET('NOT_BUYABLE', stati)

(To get rid of the second column, you need to wrap that in another SELECT.)

(Suggest changing your column name away from the keyword "group".)

Recommend this for both queries:

 INDEX(`group`, `status`)

(I don't know which Plan will be faster.)


Problems with insert trigger and primary key

At first I thought this was due to the INSERT INTO pre_case that you are doing, not the INSERT INTO trigger. But that turned out not to be the case after more info was supplied.

Judging by the google results, this is a common error with INSTEAD OF triggers and presumably an identity column. The value for that column has not been generated when the INTEAD OF trigger fires, so it's NULL from ID. Change the column to be nullable and then see what you get in the ID column in the new table. If you have a value for it, then you are good to go. If you don't, it seems you'll need to generate it.

  (My end solution will be deployed onto SQL2000, SQL2005 and SQL2008R2 boxes, but I'm testing/developing on SQL2000)

CRAZY


Identical tables, identical columns, identical records, but why different record sizes?

Thomas Cleberg is correct.

The first row you insert into bit2 shows up in DBCC page as

10000500 ff080000 63

This breaks down as follows

+-------+-----------------+--------+-------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                Comments               |
+-------+-----------------+--------+-------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                  |
| 1     | 00              | 0x00   | TagB                                                  |
| 2-3   | 0500            | 0x0005 | Column Count offset 0x0005 = 5                        |
| 4     | ff              | 0xff   | The bit values. All are 1. 11111111 in binary = 0xff  |
| 5-6   | 0800            | 0x0008 | The column count. 0x0008 = 8. You have 8 columns.     |
| 7     | 00              | 0x00   | The Null bitmap. None are NULL.                       |
| 8     | 63              | 0x63   | Spare                                                 |
+-------+-----------------+--------+-------------------------------------------------------+

(If you wonder why there is a "spare" byte. This is explained here. It is to allow the row to be replaced by a forwarding pointer should the need arise. The value of it is arbitrary. For me it was 0x63. For you it looks like it was 0x00)

After you alter the table and add a new column with a NULL value (or a runtime constant default value in SQL Server 2012+ enterprise edition) then this is a metadata only change. SQL Server doesn't update existing rows. It can deduce from the column count stored in the row that the value for col9 should be NULL for that row. The change will be written out next time the row is updated.

Newly inserted rows will get written out in full though. Two extra bytes are needed because the single byte in the fixed data section is full up with values for bit columns 1-8 so an additional byte is needed for bit columns 9 to 16. Additionally the Null bitmap grows by a byte too. However there is no need to retain that "spare" byte to keep the rowsize at a minimum level so the net effect is an increase in one byte.

+-------+-----------------+--------+------------------------------------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                      Comments                                      |
+-------+-----------------+--------+------------------------------------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                                               |
| 1     | 00              | 0x00   | TagB                                                                               |
| 2-3   | 0600            | 0x0006 | Column Count offset 0x0006 =6                                                      |
| 4-5   | ff00            | 0xff00 | The bit values. An extra byte has now been added to accomodate the 9th bit column. |
| 6-7   | 0900            | 0x0009 | The column count. You now have 9 columns.                                          |
| 8-9   | 0001            | 0x0100 | The Null bitmap. The 9th column is NULL. 100000000 in binary is 0x0100             |
+-------+-----------------+--------+------------------------------------------------------------------------------------+


I need a way to give a sql authenticated user read access to a windows directory

One thing you could try is to create stored procedure that would select from a View
When creating stored procedure, use EXECUTE AS clause and set it to a Windows-authenticated account (grant all necessary permissions to this account, required to read from I:\Folder)
Grant execute on procedure to SBA_USER
This way when SBA_USER  executes procedure, procedure does select from View, from behalf of the Windows-authenticated account


How to do housekeep the database in sql server 2008 by specific date range?

Deleting data is easy from the syntax point of view, but that's not all there is. DELETE FROM myTable WHERE someDate < deletionDate might give you a few nasty suprises.

Constraints might block you from doing a delete. A textbook example is a foreign key constraint between customer and order tables. Deleting a customer is not allowed as long as there are orders. If it would, you would end up having orders not linked to a customer. In order to overcome this kind of limitations, you must understand the database schema and start deleting from the right table first (or rely to constraint to use ON DELETE CASCADE.)

Don't underestimate the performance effects. If you are lacking proper indexing (or have overextensive indexing), deleting records might generate serious a performance hit by creating lots of unnecessary index updates and/or full table scans. In addition, write locks will cause overall performance hits for any operations from the affected tables.

Transaction log management is a common gotcha. Pruning a lot of data will generate lots of transactions and you log files will go sky high. As a best practice, break the delete operations in batches. Aaron's blog has detailed discussion about the issue.


Am I clearing the query cache correctly?

To bypass the query cache you need to change your SELECT to SELECT SQL_NO_CACHE.

This will allow you to properly benchmark your query performance.

Be aware, that this does NOT affect the InnoDB buffer pool. Whether or not the pool is populated will also affect performance.

A good rule of thumb is to run one query (with SQL_NO_CACHE), discard the result, and then run the query 3 more times to get a good idea of how fast the query is with the buffer pool populated.


22046 "Impersonation error" running SQL Server Agent job

Check the account that SQL Agent itself is running as.
See this article on the Redgate "SimpleTalk" website about setting up SQL Agent correctly (section under "What service account to use") for more information.
Key part is this:

The core of the SQL Server Agent infrastructure is the SQL Agent
Service. This is a windows service that is responsible for executing
the different types of job steps supported by SQL Server Agent. The
service account defines the Microsoft Windows account used to execute
the SQL Agent service.
You select an account for the SQL Server Agent service by using SQL
Server Configuration Manager, where you can choose from the following
options:
Local System– This is the NT AUTHORITY\System account on the local
machine. It is a member of the Windows Administrators group on the
local machine, and therefore, is a member of the SQL Server sysadmin
fixed server role. Since the account is a Windows administrator, it
has permissions beyond what is required for running SQL Server Agent,
and it is therefore not recommended to be used as a service account.
Local Service– not recommended. Has minimum privileges on local
machine.
Network Service – not recommended. Has minimum privileges on
local machine.
Windows Domain Account – You can also choose a windows
domain account as the service account for SQL Server Agent. Using a
windows domain account and granting it the necessary permissions is
the best practice when choosing a service account. It is also
recommended that this account not be a member of the Windows
Administrators group. Further, the service account is required to be a
member of the SQL Server sysadmin fixed server role on the SQL Server
instance.

In our case, our Sql Agent was running as Local Service.  Changing it to run as Local System immediately fixed the issue.


Warning before issuing delete/update command in mysql/oracle , then executes

if i issue some delete/update command wrongly

There is no such thing as a "wrong" delete or update command unless it is "wrong" in the sense of being syntactically invalid, referencing nonexistent objects, or attempting something for which the user does not have permission... and those aren't going to execute anyway.

So you must be talking about something like this:

DELETE FROM t1; -- not wrong, deletes all of the rows by excluding none
UPDATE users SET email = 'test@localhost'; -- not wrong, updates all rows with same value

There is sure-fire no way for the system to correctly guess "hey, that seems wrong" and ask you to rethink your decision. The DBMS's job is to do what you ask in the most optimal way, not second-guess whether you know what you're doing.

MySQL does have a mode that tries to protect you, by stopping you from executing updates or deletes that aren't using an index to find rows, with the --i-am-a-dummy flag that enables sql_safe_updates on a per-connection basis -- or you can enable that setting at a global level on the server.

$ mysql --i-am-a-dummy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4642931
Server version: 5.5.30 MySQL Community Server (GPL)

mysql> use sakila;
Database changed

mysql> delete from film;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table
                    without a WHERE that uses a KEY column

I would argue that this fixes the wrong problem, which is that you have privileges granted to people that they are not qualified to hold.

You could, of course, disable autocommit, requiring you to type COMMIT; to actually commit your DML changes to the database, but that's only true and fully helpful if you're exclusively using a transactional storage engine like InnoDB, and not MyISAM, so I don't know how much that solves.

Those blessed with extra lazyiness will figure out that they can type ; COMMIT; at the end of every statement (true in the mysql cli, not universally).

The person who is sufficiently conscientious to think before committing is probably sufficiently conscientious to not type queries that don't make sense.


How Sql server 2012 join cardinality is calculated?

To answer your first question, Microsoft released a white paper for the new cardinality estimator in SQL Server 2014. That white paper describes how join cardinality was calculated in the 2012 CE. I also know of a blog post that dives into the internals of join cardinality quite a bit.

To answer your second question, I can go over a few techniques.

Trace flag 2301 changes some aspects of cardinality estimation along with other things. It may improve performance of your queries. Test carefully before using.
Trace flag 2371 causes statistics to be updated more frequently on large tables. This is on by default in SQL Server 2016. This will not help you if your statistics have already been updated with a FULLSCAN.
Trace flag 4139 can provide better estimates when your queries suffer from the ascending key problem. That is, you are filtering on new data on an ascending column and your filter range is outside of all histogram steps.
Trace flag 4199 contains a rollup of query optimizer fixes that are on by default in SQL Server 2016. I don't know if any of them directly affect join cardinality estimates but they might improve the performance of your queries in other ways.
If you upgrade to SQL Server 2014 you can use the new cardinality estimator which drastically changes the algorithms for cardinality estimation. It could offer a significant improvement for your workload.
You can create filtered statistics to get a full histogram with up to 200 steps on a table filtered down to the data that you're interested in. This can be useful for certain types of data skew when you know that queries will be commonly filtering on a specific value.
You can create multi-column statistics when you need a more accurate value for the density of both columns. The histogram for the multi-column statistics is only on the first column. SQL server will not automatically create multi-column statistics for you.
For some queries I've only had luck with putting an intermediate result set into a temp table, gathering stats on the temp table, and referencing that temp table in the query. Sometimes even putting just a few thousand rows into a temp table can make a huge improvement in overall cardinality estimates.


Applying a rule dynamically to all columns in different tables

You can retrieve only one name the way you are doing it. One relatively easy change to your current script to account for all columns would be to define a cursor on INFORMATION_SCHEMA.COLUMNS to go through all columns in a loop and apply your current method, as Justin Cave suggested in a comment:

DECLARE CURSOR ColumnsCursor LOCAL FAST_FORWARD
FOR
SELECT
  COLUMN_NAME
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = @tablename
;

OPEN ColumnsCursor;

FETCH NEXT FROM ColumnsCursor INTO @FieldName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = '
  UPDATE  ' + @tablename + '
  SET     ' + @FieldName + ' = (SELECT REPLACE(' + @FieldName + ',''"'',''''))';

  EXECUTE(@sql);

  FETCH NEXT FROM ColumnsCursor INTO @FieldName;
END;

CLOSE ColumnsCursor;

DEALLOCATE ColumnsCursor;

Another good point by Justin is that you may want to introduce an additional filter to your COLUMNS query to process only columns that are either varchar or nvarchar:

...
WHERE
  TABLE_NAME = @tablename
  AND DATA_TYPE IN ('varchar', 'nvarchar')

One other minor point is that (SELECT REPLACE(...)) is redundant, just REPLACE(...) is enough, so the @sql assignment would look like this:

  SET @sql = '
  UPDATE  ' + @tablename + '
  SET     ' + @FieldName + ' = REPLACE(' + @FieldName + ',''"'','''')';

However, while these changes are simple, the resulting script would be rather inefficient against tables with multiple string columns. That might not matter much if yours is a one-off task, but you might still be interested in avoiding multiple passes over a table where one is enough to complete the job.

And you can do that if you change the basic logic slightly – instead of these steps:

take one column name,
build an UPDATE statement,
execute,
repeat for the next column until done,

you implement it like this:

take all columns and build an assignment list for the SET clause of the UPDATE,
build the UPDATE statement around the list,
execute.

Here is what it can look like in Transact-SQL:

DECLARE @SetList nvarchar(max), @sql nvarchar(max);

SELECT
  @SetList = ISNULL(@SetList + N', ', N'')
           + QUOTENAME(COLUMN_NAME) + N' = REPLACE('
           + QUOTENAME(COLUMN_NAME) + N',''"'','''')'
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = @tablename
  AND DATA_TYPE IN ('varchar', 'nvarchar')
;

SET @sql = N'UPDATE ' + @tablename + ' SET ' + @SetList + ';';

EXECUTE(@sql);


View with OPENQUERY won't build in SSDT project

Have you tried this?
CREATE VIEW dbo.VW_SOMEVIEW AS
SELECT *
FROM OPENQUERY (
    [$(LinkedServer)] /*we need the [] brachets to make this parse ok in VS*/
    ,'SELECT SESSION_NAME,SESSION_ROWS,ACTUAL_START
    FROM DATABASE2.dbo.TABLE2 (NOLOCK)
    WHERE SUBJECT_AREA = ''XYZ''
    ORDER BY ACTUAL_START'
    )


While running Mongodb, getting “enabling http interface” message and stopped

The REST API (--rest) depends on the HTTP interface (--httpinterface) so the warning here is that the HTTP interface is being enabled for you automatically in order to support REST.

Both the REST API and the HTTP interface are strongly discouraged outside of trusted development environments as they are not secure. Starting from MongoDB 2.6, both of these options are disabled by default.

See also: Network Exposure and Security and the MongoDB Security Checklist.


How to allow only a certain type of foreign key in a table

Possible realization.

Main table

CREATE TABLE main ( id INT UNSIGNED PRIMARY KEY,
                    type VARCHAR(16)
                  );

Prepare main table for referencing

ALTER TABLE main
    ADD COLUMN for_reference INT UNSIGNED AS (CASE WHEN type LIKE 'allowed%' THEN id END) STORED,
    ADD UNIQUE key_for_reference (for_reference);

Create referenced table

CREATE TABLE slave ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                     reference INT UNSIGNED,
                     CONSTRAINT fk_to_main FOREIGN KEY (reference) REFERENCES main (for_reference)
                   );

db<>fiddle

PS. Pay attention - the field in main table which is used for referencing primarily (id) must be unique but NOT autoincremented (because the expression of generated column cannot refer to AI). In general this field must be unique but not primary key.


An Oracle database can generate "Server-Generated Alerts".

You can view the alert thresholds in DBA_THRESHOLDS view, and query the alerts from the DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY views.

But I do not think this is what you are looking for. I have yet to see someone who uses this feature specifically and deliberately for alerting and notifications.

Usually there is Enterprise Manager (provided by Oracle) or some kind of monitoring software like Nagios, Zabbix, etc. that periodically logs in to the database, runs its checks, and sends notifications if needed. Still, some companies just simply use custom scripts scheduled in crontab for monitoring the database. Others use custom Scheduler jobs for monitoring. So no, there is no universal way to find what you are looking for.

The command you tried to execute is not a sqlplus command, it can be executed in adrci, and all it does it shows the default log of the selected location. Example:

[oracle@o71 ~]$ adrci

ADRCI: Release 12.2.0.1.0 - Production on Tue Sep 18 18:59:29 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the home from which to view the alert log:

1: diag/rdbms/etalon/ETALON
2: diag/clients/user_oracle/host_2676003343_107
3: diag/tnslsnr/o71/listener
Q: to quit


You can't create an Indexed view on linked server table.

According to BOL http://msdn.microsoft.com/en-us/library/ms191432.aspx
"The view must reference only base tables that are in the same database as the view."


Reason of poor performance of ANSI joins in Oracle 9i

Well, after a long time with no answers. I've done some testing.

I ran the same query in 10g and 11g and both versions, the one with ANSI joins and the ones with the WHERE joins run in less than 1 second.

As problems exists only in 9i, the same version the support for ANSI joins where introduced I assume it's a bug in 9i that could or could have not be solved in a patch.

Gladly as I mentioned, from 10g onwards both flavors of joins perform well.


Tablespace Quotas for a User

  ...

  By default, a user has no quota on any tablespace in the database.

  ...


Yes, it's possible, The query you already tried was on the right lines but had the wrong join condition (b.INVOICES_ID = a.INVOICES_ID should be b.ORDER_NO = a.ORDER_NO)

try this

SELECT DISTINCT a.order_no
FROM   x_invoic a
       INNER JOIN x_invoic b
         ON a.order_no = b.order_no
WHERE  a.item_code = 'DONE'
       AND b.item_code = 'LAPMACPRO15.4'


Is it possible to redesign your schema?  It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets.

CREATE TABLE dbo.Hardware -- hw?
(
    [Event_Num] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(100) NOT NULL,
    [Install_Date] DATE NULL,  -- Install after pulling?
    [Pull_Date] DATE NOT NULL,
    [Install_Tech] INT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    [Pull_Tech] DATE NOT NULL FOREIGN KEY REFERENCES dbo.techs(UserId), -- separate out your techs to another table
    <Add other common fields here>
)

CREATE TABLE dbo.ComponentType -- Or some better name for PMP, Motor, dis, etc. table
(
    [ComponentTypeId] INT NOT NULL PRIMARY KEY,
    [Description] NVARCHAR(50) NOT NULL -- This is where you would put PMP, Motor, etc.
)

CREATE TABLE dbo.WorkPerformed
(
    [WorkPerformedId] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [ComponentTypeId] INT NOT NULL FOREIGN KEY REFERENCES dbo.ComponentType(ComponentTypeId), -- FK to type lets us reuse this structure for all components and prevents the need to pivot the excel data
    [Event_Num] INT NOT NULL FOREIGN KEY REFERENCES dbo.Hardware(Event_Num), -- Allows you to associate the work performed with the hw
    [Description] NVARCHAR(400) NULL,
    [SerialNumber] NVARCHAR(50) NOT NULL,
    [Part] NVARCHAR(50) NOT NULL,
    <Other valuable info not currently tracking, ie pulled/installed, tech doing work, etc>
)

Obviously that is just a rough sample of the way the schema could be setup.  I'm sure you can see that mapping the data will be much easier now, and more flexable for future updates.  In order to maintain backwards compatibility, if needed, you could just create a view with the current table's name and select the data from the new tables.

If going down this path is not possible/acceptable I would look at pulling your data out of the temp table and inserting it into your current table with a PIVOT.  See this TechNet article for basic information about pivoting (the syntax from 2008R2 will work in 2012).


Migration of records to the table with different column names

The SQL would be like:

insert into tab1 (category_id, created_at, title, promo, content)
as
select 3, date_art, title_art, short_art, full_art
from   tab2

The ID column will get it's value during the insert.


You can do this quickly with SQL if the actual data inserted doesn't matter. Create your table and insert one record manually. If the table has a unique/primary key then remove this for the moment.

Then do a insert into table select * from table; This will double the number of records. Do it again to double that (having so 4 records) and so on. Each run will double the (same) records giving you qickly a huge number of them. To make this even faster you can duplicate the query string in the editor. Eg. having 30 insert queries in the editor you can get 2^30 records with a single run of the full editor content.

Once you have enough rows and you want your primary/unique key back run an update over the table, setting the key column to some unique value and then add the primary/unique key back.


Geo-clustering using SQL Server 2012 Enterprise with AlwaysOn

You are essentially trying to create a DR node. Availability Groups in SQL Server 2012 and above supports this scenario. Your node has to be part of the same windows cluster. The SQL Server instance on the secondary site can be configured as a secondary replica for the existing availability group. Once that is done, you will be able to sync changes from primary replica to the secondary replica. Your scenario is documented here: https://msdn.microsoft.com/library/hh781257.aspx


Upgrading from SQL Server 2012 SP1 to SQL Server 2019

This handy page from Microsoft should be very useful for you as it explains in detail how to carry out a migration and the tools and methods available:
Supported Version and Edition Upgrades for SQL Server 2019

Use the Data Migration Assistant (DMA) to scan your databases to make sure they can be migrated to the 2019 server. It will highlight any potential blocking issues such as deprecated features that you may need to resolve before you can migrate.

It looks like you will still need to upgrade your SQL Server 2012 to SP4 to be fully supported though.


Restore a single database backup (.bak) file into multiple filegroups

No it is not possible to do what you are asking.  SQL is backing up the current state of the database.  What tables are in what filegroup is part of the database information. It is possible to restore the backup to a new database name.  Once the data is on the new server you could research and create the file groups you want, make your changes, etc.   Get everything you the way you want it.   Back it up a final time and then restore it to the database name you want or you can simply rename the database.  Depending on the size it might be faster to restore it.


How can I add a column to multiple tables at once in MySQL Workbench?

You can use this Python Script in the Scripting Shell of MySQL Workbench. It is an improved version of the script found here: http://mysqlworkbench.org/2012/06/mysql-workbench-script-for-adding-columns-to-all-tables-in-a-model/

# define your columns and types here
columns_types = {"created_at" : "DATETIME", "updated_at" : "DATETIME"}

# tables you want to skip
skip_tables = ["main_defs", "main_menu_items", "delayed_jobs", "delayed_job_workers", "delayed_job_logs"]

# get a reference to the schema in the model. This will get the 1st schema in it.
schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]

# iterate through all tables
for table in schema.tables:

    # skip the current table if it is in skip_tables
    if table.name in skip_tables:
        continue

    # iterate through all columns to be added
    for column_name, type in columns_types.items():

        # skip this column_name if there is already a column with this name
        column_names = [x.name for x in table.columns]
        if column_name in column_names:
            continue

        # create a new column object and set its name
        column = grt.classes.db_mysql_Column()
        column.name = column_name
        # add it to the table
        table.addColumn(column)
        # set the datatype of the column
        column.setParseType(type, None)


Can MongoDB be configured to sit behind a load balancer?

recently I attended the online course provided by mongo university M202 and in the CHAPTER 3 FAULT TOLERANCE AND AVAILABILITY it specifically talks about these 2 questions:

Load balancer with a replica set;
Load balancer in a sharding environment.

Within a  replica set having a load balancer is discouraged because the load balancer should be aware about the actual primary server and also it should know about read preferences in the cluster.
So the answer given about the firs question is that no, it is not possible to have an external load balancer over a replica set cluster.
For the second question the answer given is that is possible to have a load balancer in front of a sharded system but with only one caveat, which is that the load balancer should stay sticky to the mongos addressed, something very similar to the session affinity in web application.
The reason is that during a request, i.e. a read from a cursor, the retrieval of the next elements will fail if the load balancer would use a different mongos from the one chosen before.


My question is - why do these features lack in so many Postgres editors?

What are you missing? You can do it all with psql[1] or PGAdmin III[2].

Take a look[3][4] how to customize psql to interact with our editor/environment.

  Postico for example has no easy save option - who does not save queries they write?

That's a good question. :P

  Is the way Postgres typically is used fundamentally different than other databases? I understand some are optimized for high throughput etc, but is my use (analyst) so very different than features developers would need?

IMHO the PostgreSQL community share a ideia of make a simple tool for each job. You can do all the job, but using each of this 'tiny' tools.

Database vendors, like Microsoft and Oracle, always make a super tool with all options and probably call this small tools to help.

MSSQL it's a great database but, without the Management Studio, it's hard to work with.

  Is it just because I'm relying on open source editors? Anyone have recommendations?

There's a few comercial options, like @Lauri already suggested, take a look at the wiki[5][6] for more options.

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/app-psql.html
https://www.pgadmin.org/
http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/
http://akorotkov.github.io/blog/2016/06/09/psql-graph/
https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
https://wiki.postgresql.org/wiki/GUI_Database_Design_Tools


Existence of Missing Index in MySQL. Does it exist?

There is another article here: https://stackoverflow.com/questions/30626876/how-to-find-missing-indexes-in-mysql

I, too, would recommend using the explain plan and looking at results.  I have had instances where adding an index to a table actually slowed the results down to a crawl.

Here is an interesting way to look up columns that could possibly need indexed:
http://www.youdidwhatwithtsql.com/spotting-missing-indexes-mariadb-mysql/2036/

Again, like the author said, "a query I use for MySQL / MariaDB to spot any columns that might need indexing" [emphasis mine]


I have an article on this here: http://www.tocker.ca/configuring-mysql-to-use-minimal-memory.html:

  The MySQL defaults have to balance performance with what is considered reasonable for what may be a development system with other applications needing to run alongside MySQL. In many cases, this will mean 4-8GB, but on virtual machines (or in my case with 7 copies of mysqld running), there is a lot less available.

  Obligatory warning: If you are running these settings on a machine with 1GB+ RAM, you should expect worse performance when compared to the defaults.

Setting                     Default      Minimum
innodb_buffer_pool_size     128M         5M
innodb_log_buffer_size      1M           256K
query_cache_size            1M           0
max_connections             151          1 (although 10 might be more reasonable)
key_buffer_size             8388608      8
thread_cache_size           (autosized)  0
host_cache_size             (autosized)  0
innodb_ft_cache_size        8000000      1600000
innodb_ft_total_cache_size  640000000    32000000
thread_stack    262144      131072
sort_buffer_size            262144       32K
read_buffer_size            131072       8200
read_rnd_buffer_size        262144       8200
max_heap_table_size         16777216     16K
tmp_table_size  16777216    1K
bulk_insert_buffer_size     8388608      0
join_buffer_size            262144       128
net_buffer_length           16384        1K
innodb_sort_buffer_size     1M           64K
binlog_cache_size           32K          4K
binlog_stmt_cache_size      32K          4K

  To summarize these changes:

# /etc/my.cnf:
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K


Just use SUM() and a CASE statement:

SELECT sum(case when zcsms_studentgender=11 then 1 else 0 end) as malestudents,
       sum(case when zcsms_studentgender=12 then 1 else 0 end) as femalestudents,
       sum(case when zcsms_studentgender in (11,12) then 1 else 0 end) as totalstudents
FROM zcsms_studentdefinition
WHERE zcsms_studentactive = 'Y';


Subtract time difference between two columns in a table

If you are only looking for the difference between two rows, you can reference them by their ids.

SELECT
  TIMESTAMPDIFF(MICROSECOND, s1.date, s2.date) DIV 1000
FROM
  via1 s1
  , via1 s2
WHERE
  s1.id = 1
  AND
  s2.id = 2
;

There are a few errors in your CREATE TABLE statement:

CREATE TABLE via1 (
  id int auto_increment primary key,
  sensor varchar(10) NOT NULL,
  date timestamp(6) default current_timestamp(6)
);

You're using an old version of MySQL (5.5.44).  From the docs:

11.3.6 Fractional Seconds in Time Values - MySQL 5.6 Reference Manual

  Before MySQL 5.6.4, the instances are limited in which a fractional seconds part is permitted in temporal values. ...when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.


Select postgresql within 1 hour range of current date

Maybe you need something like

SELECT MIN(clock)
FROM history
WHERE date_trunc('day', clock) = CURRENT_DATE
/* or WHERE clock >= CURRENT_DATE */
GROUP BY date_trunc('hour', clock)

?


SQL Server 2012: flatten hierachy to dynamic number of columns

I can't come up with anything better than building out a Dynamic SQL Statement for what you're trying to do.  This is neither easy to read nor efficient in execution, but it should do what you want and will work with any number of parent/child levels.  I'm also sure there are other ways to assemble this statement, so feel free to adjust this to your liking.

DB Fiddle for the following:

CREATE TABLE #Hierarchy (AccountId INT, ParentId INT, Name VARCHAR(50))
INSERT INTO #Hierarchy VALUES (1,NULL,'Revenue')
INSERT INTO #Hierarchy VALUES (2,NULL,'Costs')
INSERT INTO #Hierarchy VALUES (3,1,'Sales')
INSERT INTO #Hierarchy VALUES (4,1,'Interests')
INSERT INTO #Hierarchy VALUES (5,2,'COGS')
INSERT INTO #Hierarchy VALUES (6,2,'Expenses')
INSERT INTO #Hierarchy VALUES (7,6,'Salaries')
INSERT INTO #Hierarchy VALUES (8,6,'Other')

DECLARE @maxLevels INT

-- Retrieve Max Number of Levels From Hierarchy
;WITH depthCheck as
(
  SELECT H.AccountId, H.ParentId, Name, 0 AS Level
  FROM #Hierarchy H
  WHERE H.ParentId IS NULL

  UNION ALL

  SELECT Hc.AccountId, Hc.ParentId, Hc.Name, dc.Level + 1 AS Level
  FROM #Hierarchy Hc
  INNER JOIN depthCheck dC
  ON Hc.ParentId = dC.AccountId
 )
-- Add 1 Level because your column labels start with 1 instead of 0
SELECT @maxLevels = MAX(Level) + 1 From depthCheck

-- Declare some variables needed to assemble the Dynamic SQL Statement
DECLARE @qLevel INT = @maxLevels, @iLevel INT = @maxLevels, @sqlSelectStmt VARCHAR(MAX) = '', @sqlFromStmt VARCHAR(MAX) = ''

-- This nested While Loop builds out both custom SELECT and FROM clauses
WHILE @qLevel > 0
BEGIN

    -- If Root level, the format will be different
    IF @qLevel = 1
    BEGIN
        SET @sqlSelectStmt = CHAR(9) + ', /\Name/\ AS Level_' + CAST(@qLevel AS VARCHAR(20)) + '_Name' + CHAR(13) + CHAR(10) + @sqlSelectStmt
        SET @sqlFromStmt = CHAR(9) + '#Hierarchy L' + CAST(@qLevel AS VARCHAR(20)) + CHAR(13) + CHAR(10) + @sqlFromStmt
    END
    -- If not a Root Level, COALESCE statements will be required for the SELECT clause and JOINS will be required in the FROM clause
    ELSE
    BEGIN
        SET @sqlSelectStmt = CHAR(9) + ', COALESCE(/\Name/\) AS Level_' + CAST(@qLevel AS VARCHAR(20)) + '_Name' + CHAR(13) + CHAR(10) + @sqlSelectStmt
        SET @sqlFromStmt = CHAR(9) + 'LEFT JOIN #Hierarchy L' + CAST(@qLevel AS VARCHAR(20)) + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9)
                         + 'ON L' + CAST(@qLevel - 1 AS VARCHAR(20)) + '.AccountId = L' + CAST(@qLevel AS VARCHAR(20)) + '.ParentId' + CHAR(13) + CHAR(10) + @sqlFromStmt
    END

    -- Build out the cascading COALESCE statment
    WHILE @iLevel > 0
    BEGIN
        SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, '/\Name/\', 'L' + CAST(@iLevel AS VARCHAR(20)) + '.Name, /\Name/\')

        IF @iLevel = 1
            SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, ', /\Name/\', '')

        SET @iLevel = @iLevel - 1
    END

    -- Decriment @qLevel and Reset @iLevel counters
    SET @qLevel = @qLevel - 1
    SET @iLevel = @qLevel
END

-- Include the AccId Column
SET @sqlSelectStmt = CHAR(9) + '  COALESCE(/\ID/\) AS AccId' + CHAR(13) + CHAR(10) + @sqlSelectStmt

-- Build out the cascading COALESCE statment for the AccId column
SET @iLevel = @maxLevels
WHILE @iLevel > 0
BEGIN
    SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, '/\ID/\', 'L' + CAST(@iLevel AS VARCHAR(20)) + '.AccountId, /\ID/\')

    IF @iLevel = 1
        SET @sqlSelectStmt = REPLACE(@sqlSelectStmt, ', /\ID/\', '')

    SET @iLevel = @iLevel - 1
END

--PRINT @sqlSelectStmt
--PRINT @sqlFromStmt

-- Assemble final Dynamic SQL Statement
DECLARE @finalSQL VARCHAR(MAX)
SET @finalSQL = 'SELECT' + CHAR(13) + CHAR(10) + @sqlSelectStmt
              + 'FROM' + CHAR(13) + CHAR(10) + @sqlFromStmt
              + 'WHERE' + CHAR(9) + 'L1.ParentId IS NULL'

-- Print for good measure
PRINT @finalSQL

-- Execute the assembled statement
EXEC(@finalSQL)

DROP TABLE #Hierarchy


First my recommendation is to start with text dumps and expect you may need to manually edit them.  There are a large number of differences between 9.2 and 8.4 and these are especially important if you have used extensions in 9.2.

There are however a few things you can do to make the import less troublesome but slower:

Don't use -F to select a format.  Work with plain text dumps since you can edit them if you need to.
Don't use -j.  Keep things simple.
Use --inserts, so INSERT statements are used instead of copy.

Be prepared to edit the dump if needed.  Be comfortable with text editors used for managing large files (vim or emacs, both of which have Windows versions).


How to get multiple "last values" for different criteria from a single column?

Assuming only few entries per (user_id, foo_id), the query you have is typically faster this way:

SELECT DISTINCT ON (user_id, foo_id)
       user_id, foo_id
       action = 'DISMISS' AS is_dismissed
FROM   interest_log
ORDER  BY user_id, foo_id, id DESC
WHERE  action IN ('DISMISS', 'UNDISMISS');

But this only produces rows for (user_id, foo_id) that actually have (un-)dismissed.

If there are relatively few rows per (user_id, foo_id) for both items of interest (is_dismissed and is_favored), just run a FULL [OUTER] JOIN on two of these queries to produce the set of all that either (un-)dismissed or (un-)favored:

SELECT *
FROM  (
   SELECT DISTINCT ON (user_id, foo_id)
          user_id, foo_id
        , action = 'DISMISS' AS is_dismissed
   FROM   interest_log
   WHERE  action IN ('DISMISS', 'UNDISMISS')
   ORDER  BY user_id, foo_id, id DESC
   ) d
FULL   JOIN (
   SELECT DISTINCT ON (user_id, foo_id)
          user_id, foo_id
        , action = 'FAVOR' AS is_favored
   FROM   interest_log
   WHERE  action IN ('FAVOR', 'UNFAVOR')
   ORDER  BY user_id, foo_id, id DESC
   ) f USING (user_id, foo_id);

About DISTINCT ON and few vs. many:

Select first row in each GROUP BY group?

Support with two partial indexes:

CREATE INDEX interest_log_dismiss ON interest_log (user_id, foo_id, id DESC, action)
WHERE  action IN ('DISMISS', 'UNDISMISS');

CREATE INDEX interest_log_favor ON interest_log (user_id, foo_id, id DESC, action)
WHERE  action IN ('FAVOR', 'UNFAVOR');

Or using a custom aggregate function last():

CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement)
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS 'SELECT $2';

CREATE AGGREGATE public.last(anyelement) (
  SFUNC = public.last_agg
, STYPE = anyelement
);

Then this simpler query does the same:

SELECT user_id, foo_id
     , last(action) FILTER (WHERE action IN ('DISMISS', 'UNDISMISS')) = 'DISMISS' AS is_dismissed
     , last(action) FILTER (WHERE action IN ('FAVOR', 'UNFAVOR'))     = 'FAVOR'   AS is_favored
FROM  (
   SELECT *
   FROM   interest_log
   WHERE  action IN ('DISMISS', 'UNDISMISS', 'FAVOR', 'UNFAVOR')
   ORDER  BY user_id, foo_id, id
   ) sub
GROUP  BY user_id, foo_id;

Faster with the additional module first_last_agg providing a C implementation. See:

Calculating follower growth over time for each influencer

There are more ways (have a look at the linked answer), and depending on undisclosed information, different query styles may be (much) faster.


How is it possible to model in a database many different products?

You probably have some columns that are common to almost all products:  Price, SKU, Manufacturer, etc.  Those may as well be columns.  The rest can be put into a JSON string.  More discussion:  http://mysql.rjweb.org/doc.php/eav


Priority 1: Fix the table. You should not be storing date and time data, in varchar columns, and quite possibly should not be separating date and time into their own separate columns. You should also avoid reserved words/keywords as columns, but I suspect you may have just dumbed down your actual table structure.

Priority 2: Stop using BETWEEN. This can cause all kinds of issues, especially if you fix the table and combine the date and time into a single column. The end of a range is different depending on the data type, but using >= beginning of the range and < beginning of the NEXT range will always work. Consider that the table has changed, you can say as follows (and I'm not sure what you're trying to do with your second and third where clauses, but you should just be able to look at the date column):

WHERE [DATE] >= '20160101'
  AND [DATE] <  '20160401';
-- note that I don't have to look at the TIME column at all
-- also note that I use a *SAFE* format for the string literal

Priority 3: Stop comparing dates as strings. If you can't fix the table, then your clauses need to first convert the string columns into valid date/time values, so you aren't comparing strings. And you need to use string formats that are safe in SQL Server; dd-mm-yyyy is not one of those formats - it can break (or just return the wrong data) if you change language settings, for example.

WHERE CONVERT(datetime, [DATE], 105) >= '20160101'
  AND CONVERT(datetime, [DATE], 105) <  '20160401';
-- 103 makes sure the stored strings are interpreted as d/m/y

Additional reading:

Dating Responsibly
Bad habits to kick : mis-handling date / range queries
What do BETWEEN and the devil have in common?


Return all possible combinations for each group

if your dataset is small, something like this could be suitable:

select class1.id ,class1.class class1,class2.class class2 from
t1 class1,
t1 class2
where
class1.id = class2.id and
class1.class < class2.class
order by id,class1,class2


Is the transfer time from PostgreSQL to the application optimisable?

That time is spent by pgAdmin to pack and render data and is not the time spent by Postgres to complete the query execution. Why are you fetching 250.000 rows into pgAdmin? If you need to export the table to a plain-text file (like a CSV with header) you can execute this query:

COPY (
  SELECT
    mobile_state_message.latitude,
    mobile_state_message.longitude
  FROM
    public.mobile_state_message
  WHERE
    topic_name = 'MyCar'
)
TO '/path/to/file.csv' WITH (FORMAT CSV, HEADER);

Remember that /path/to/ must be owned by postgres user, so run chown -R postgres /path/to/.

This way you can use wathever to analyze the table (if you need to).


No it is not.

Consider this data

id | name | city        | state      | country
----------------------------------------------
 1 | john | Los Angeles | California | USA
 2 | mary | San Antonio | Texas      | USA
 3 | joe  | Los Angeles | California | USA

It is clear that (city,state,country) is a candidate key for another table.  Then, you would quickly realise that the cities belong to the same (state,country) combinations.  Eventually, you end up with

Person
------
id
name
city_id

City
--------
city_id
name
state_id

State
--------
state_id
name
country_id

Country
----------
country_id
name


Extended Events XEL file reader; Is there a FileName property avaialble in QueryTableXEventData or SqlBulkCopy

I don't believe the file name is exposed via a property.
Instead of passing a wildcard path to the QueryableXEventData constructor, you could get a list of the files and process each individually:
$eventFiles = Get-Item "\\some_file_path\XELog_Files*.xel"
foreach($eventFile in $eventFiles) {
    $events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($eventFile.FullName)
    foreach($event in $events) {
        # event processing here, using $eventFile.FullName or $eventFile.Name
    }
}


If you want the parts that were not ordered, then you should start with Part, not OrderLine. When you're thinking about sets, that is the only one that could conceivably have the items you're looking for. Part can be a Superset of ordered parts and OrderLine could easily be a Subset of parts.

SELECT PartName
FROM Part
WHERE PartNum NOT IN (
        SELECT PartNum
        FROM OrderLine
        )

More often than not, comparing integers will be far more efficient than strings, so I've changed the WHERE clause to look for PartNums, not PartNames.


Do temporal tables log changes when there are none?

Converting my comment to answer ..

If the value is actually updated, then it will log changes. If you update the same value, new versions for every row will be recorded, but the column values will remain the same.

Below is the working example :

CREATE TABLE dbo.blah1
    -- PK is a requirement !!
    (
    foo INT identity(1, 1) PRIMARY KEY
    ,baa INT
    ,SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
    ,SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
    ,PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
    )
    WITH (SYSTEM_VERSIONING = ON);

-- insert data
insert into dbo.blah (baa)
values (1)

Now check for the data

-- check data
    SELECT * FROM  dbo.blah;
    select * from [dbo].[MSSQL_TemporalHistoryFor_629577281]

perform dummy update

update blah
set baa = baa

Perform onemore dummy update .. there will be rows with same values but different systime - start and end.

You have to have a proper mechanism to clean up history table as that can grow significantly larger if you forget to trim it.


Restoration will be

Per database (alphabetical order)
Per table within each database (alphabetical order)

If you want to see the progress, you can run the following every few minutes:

SHOW DATABASES;

To be sure that restore is not blocked, use SHOW PROCESSLIST. It will show you the restore transactions (create table, insert into...).

You can also followed your disk space usage but this is'nt a really good metric...

Max.


If you are using Windows Authentication in the linked server setup it will not work using the SQL Server agent even if you have Kerberos correctly setup. You can configure the linked server to use SQL authentication for those connections but I would recommend to create a SSIS package for this as the authentication will be easier to configure.

Can you please post the linked server configuration for further information?


The driver in the connection string needs to be Vertica, not Vertica ODBC Driver 4.1.

EXEC dbo.sp_addlinkedserver
    @server = N'VERTICA',
    @srvproduct=N'',
    -- SQL Server driver
    @provider=N'MSDASQL',
    -- Connection string
    @provstr=N'Driver=Vertica;
            Servername=ServerName;
            Port=5433;
            Database=DatabaseName;
            UserName=user;
            Password=pass'


The UPDATE Quote_Base ... needs a semicolon at the end ... Fld_Revision);

CREATE TRIGGER trigger_quote_number AFTER UPDATE on Quote_Base
BEGIN
    UPDATE Quote_Base SET Fld_QuoteNumber = printf('%s/%d', Fld_QuoteNumberRoot, Fld_Revision);
END


If you don't have the required permissions, pg_dump will give you an error message rather than an empty COPY statement. Moreover, permissions do not apply to superusers.

You should verify what the database user used by pg_dump sees when selecting from the original table.

The only explanations I can think of are:

Somebody deleted all rows from the table.
You have experienced data corruption that somehow rendered the table's file empty of zeroed out.

Try the following to find the file:

SELECT oid FROM pg_database
WHERE datname = 'db';

SELECT relfilenode FROM pg_class
WHERE oid = 'schema1.some_table'::regclass;

If the numbers returned are 1234 and 5678, examine the file base/1234/5678 in the data directory: How big is it, what does it contain?


If a person's name is Null then how would it break the database?

I have seen database interfaces (e.g.  framework libraries) that return 'null' as a string for null columns.  I believe there was a flag that would turn this on or off for debugging.  This flag allows developers to easily determine if the empty field was a result of a null value or an empty value.  This is a bad setting, especially in production, and would explain the issues explained in the article.

The reverse processing of converting 'null' to a null value should generate an application error for a name field.  I would expect this to be rather quickly resolved.


Identifying why query is sleeping with open transactions

Been there 10+ years ago, here's what we did

We had the same issue with Kronos/Payroll. It get's pretty serious when people cannot get their paycheck. Like yourself, I narrowed things down to open/sleeping transactions and it was Java related as well. What's more when we contacted the vendor, they refused to take any responsibility for it. So we internally had to find a solution. I reviewed the vendor API and it definitely looked like the issue was on the vendor side.

In my case, which MAY not be your case, I discovered by working with the payroll staff that these open sleeping transactions had actually completed their work. So we implemented the following job that still looks for these processes and kills them to this day.

Basically, a T-SQL job shown in Listing A calls stored procedure in Listing B to kill the open/sleeping transactions, and the data about these issues is stored in Listing C. My apologies for any legacy T-SQL faux pas you may find--this code is crusty. Please try it in your dev/test environment first to see if it works for you. Just talking about killing these is just as creepy today as it was then--I don't like it.

Oh yes, the kicker for us all is that killing the open/sleeping transactions did NOT roll them back--but allowed them to complete (as well as stop all the blocking chains). My guess is that they were not programmed correctly to be distributed but rather they programmed the app and SQL Server to be on the same server.

Listing A: T-SQL job killing open/sleeping transactions (first four lines should be commented out)

This process kills transactions that have the following attributes
1.) They're blocking other transactions
2.) They're sleeping (i.e. doing no work)
3.) They're in a transaction that has not closed (continues to remain open)

while (1=1)
begin
declare @sleepingblocks int
exec sp_kill2 @sleepingblocks=@sleepingblocks output
--print @sleepingblocks
while (@sleepingblocks > 0)
begin
      waitfor delay '00:00:15'
      exec sp_kill2 @sleepingblocks=@sleepingblocks output
--    print @sleepingblocks
end
end

Listing B: the sp_kill2 stored procedure looks for open/sleeping transactions and kills them

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_kill2]    Script Date: 10/13/2017 4:07:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    ALTER proc [dbo].[sp_kill2]
    @sleepingblocks int output
    as
    --declare @sleepingblocks int
    --select count(*) from kronos..totaleventts where totalizationstatus <> 0
    select @sleepingblocks=0
    set nocount on
    create table #one
    (
          RowID int primary key identity(1,1),
          spid int,
          sqlhandle binary(20)
    )

    create table #two
    (
          RowID int primary key identity(1,1),
          spid int,
          sqlhandle binary(20)
    )

    create table #three
    (
          RowID int primary key identity(1,1),
          killcmd varchar(4000),
        spid varchar(4000)

    )

    insert into #one(spid,sqlhandle)
    select distinct T.spid,T.sql_handle from master..sysprocesses T --where spid=169
    join (select distinct blocked,spid,sql_handle from master..sysprocesses where blocked <> 0) D
    on T.spid = D.blocked
    where T.status like '%sleeping%' and open_tran > 0
    select * from #one

    waitfor delay '00:00:15'

    insert into #two(spid,sqlhandle)
    select distinct T.spid,T.sql_handle from master..sysprocesses T --where spid=169
    join (select distinct blocked,spid,sql_handle from master..sysprocesses where blocked <> 0) D
    on T.spid = D.blocked
    where T.status like '%sleeping%' and open_tran > 0
    select * from #two

    insert into #three(killcmd,spid)
    select distinct 'kill ' +  convert(varchar(255),a.spid),convert(varchar(255),a.spid) from #one a join #two b on a.spid = b.spid and a.sqlhandle = b.sqlhandle

    declare @currow int, @maxrow int, @cmdstring varchar(8000)
    select @currow=1
    select @maxrow=0
    select @maxrow = count(*) from #three

    while (@currow <=@maxrow)
    begin

    select @cmdstring = 'select distinct spid
    ,kpid
    ,blocked
    ,waittype
    ,waittime
    ,lastwaittype
    ,waitresource
    ,sp.dbid
    ,uid
    ,cpu
    ,physical_io
    ,memusage
    ,login_time
    ,last_batch
    ,ecid
    ,open_tran
    ,status
    ,sid
    ,hostname
    ,program_name
    ,hostprocess
    ,cmd
    ,nt_domain
    ,nt_username
    ,net_address
    ,net_library
    ,loginame
    ,context_info
    ,sql_handle
    ,stmt_start
    ,stmt_end
    ,request_id
    ,text from master..sysprocesses sp cross apply sys.dm_exec_sql_text(sql_handle) as st where sp.spid ='+spid  from #three where RowID = @currow

    --print @cmdstring
    --select @cmdstring = 'dbcc inputbuffer('+spid+')' from #three where RowID = @currow

    insert into dbadmin..KilledProcesses3(
    spid
    ,kpid
    ,blocked
    ,waittype
    ,waittime
    ,lastwaittype
    ,waitresource
    ,dbid
    ,uid
    ,cpu
    ,physical_io
    ,memusage
    ,login_time
    ,last_batch
    ,ecid
    ,open_tran
    ,status
    ,sid
    ,hostname
    ,program_name
    ,hostprocess
    ,cmd
    ,nt_domain
    ,nt_username
    ,net_address

    ,net_library
    ,loginame
    ,context_info
    ,sql_handle
    ,stmt_start
    ,stmt_end
    ,request_id
    ,text
    )
    exec (@cmdstring)
    --print @cmdstring

    --select @cmdstring = 'dbcc inputbuffer('+spid+')' from #three where RowID = @currow
    select @cmdstring = killcmd from #three where RowID = @currow
    --print @cmdstring
    exec (@cmdstring)
    select @currow = @currow +1
    select @sleepingblocks =1
    end

    drop table #one
    drop table #two
    drop table #three

    /*
    select
    spid
    ,kpid
    ,blocked
    ,waittype
    ,waittime
    ,lastwaittype
    ,waitresource
    ,sp.dbid
    ,uid
    ,cpu
    ,physical_io
    ,memusage
    ,login_time
    ,last_batch
    ,ecid
    ,open_tran
    ,status
    ,sid
    ,hostname
    ,program_name
    ,hostprocess
    ,cmd
    ,nt_domain
    ,nt_username
    ,net_address
    ,net_library
    ,loginame
    ,context_info
    ,sql_handle
    ,stmt_start
    ,stmt_end
    ,request_id
    ,text
    from master..sysprocesses sp cross apply sys.dm_exec_sql_text(sql_handle) as st where sp.spid =169
    select * from dbadmin..KilledProcesses3

    truncate table dbadmin..killedprocesses3

    */

Listing C: Processes3 table contains data about the sleeping/open transactions

USE [dbadmin]
GO

/****** Object:  Table [dbo].[killedprocesses3]    Script Date: 10/13/2017 4:08:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

    CREATE TABLE [dbo].[killedprocesses3](
        [spid] [smallint] NOT NULL,
        [kpid] [smallint] NOT NULL,
        [blocked] [smallint] NOT NULL,
        [waittype] [binary](2) NOT NULL,
        [waittime] [bigint] NOT NULL,
        [lastwaittype] [nchar](32) NOT NULL,
        [waitresource] [nchar](256) NOT NULL,
        [dbid] [smallint] NOT NULL,
        [uid] [smallint] NULL,
        [cpu] [int] NOT NULL,
        [physical_io] [bigint] NOT NULL,
        [memusage] [int] NOT NULL,
        [login_time] [datetime] NOT NULL,
        [last_batch] [datetime] NOT NULL,
        [ecid] [smallint] NOT NULL,
        [open_tran] [smallint] NOT NULL,
        [status] [nchar](30) NOT NULL,
        [sid] [binary](86) NOT NULL,
        [hostname] [nchar](128) NOT NULL,
        [program_name] [nchar](128) NOT NULL,
        [hostprocess] [nchar](10) NOT NULL,
        [cmd] [nchar](16) NOT NULL,
        [nt_domain] [nchar](128) NOT NULL,
        [nt_username] [nchar](128) NOT NULL,
        [net_address] [nchar](12) NOT NULL,
        [net_library] [nchar](12) NOT NULL,
        [loginame] [nchar](128) NOT NULL,
        [context_info] [binary](128) NOT NULL,
        [sql_handle] [binary](20) NOT NULL,
        [stmt_start] [int] NOT NULL,
        [stmt_end] [int] NOT NULL,
        [request_id] [int] NOT NULL,
        [text] [nvarchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


Postgresql perl : "NOTICE: elseif should be elsif at line ..."

I can't reproduce your error, it works fine on my PostgreSQL 9.5 on Linux. What version and platform are you running?

Note that your function can be exploited really badly, e.g.

select hds_updatewebservers_products('1; rm -rf /')

Why are you using elseif? Why not just else? See perl's documentation on elseif

Finally, you can simply (and elegantly):

CREATE FUNCTION hds_updatewebservers_products(character varying) RETURNS boolean AS $_$
  my $product_ref = @_[0]; # Launch update

  $product_ref =~ /^\w$/ or die 'Invalid product ref!'; # added for safety

  $checkexitcode = system ("sudo -u dbliveud /home/dbliveud/scripts/maj/launch-all.sh " . $product_ref . " ");

  # positive value means error while updating, otherwise update done OK
  return !($checkexitcode > 0);
$_$ LANGUAGE plperlu STRICT;


Is it ok to use 4 application server against one database server?

Having 4 application servers pointed at the same DB is not a problem. You may want to monitor the various metrics such as connection counts on each server. In theory, there may be a few more connections than previously but that is ok. You may also want to start planning on sharding. A cheap and easy way to do this is to have auto increment offsets being different on each machine. This would allow you to use both machines in a distributed fashion rather than a failover (failover would still work).

I would just be careful on the other side of the fence. With 2 additional application servers people might suspect that they're able to double the traffic.


Sales/excise/VAT tax rules can be very complicated.  There can be different rules about the applicability of different taxes based on things like:

What kind of item is it?
How many of the items are being purchased?
Who is purchasing the item?

Also, some taxes are calculated on the base amount and others are compounded (applied to other taxes) as well.  These are some of the kinds of rules that apply in the jurisdiction where I live.  You might have others as well.

I would suggest not limiting yourself to two applicable taxes per item.  Instead, I would create an intersection table (many-to-many) that indicates which taxes are applied to each item.  This could be driven by a similar intersection table between PRODUCT and TAX to indicate which taxes are usually applicable to each product, if that is an important distinction in your case.

While adding another table may not seem like a simplification, it will actually make your code simpler and easier to maintain because you are normalizing your applicable tax data.  The design you have illustrated isn't even in 1NF, which is almost always a recipe for trouble.

Compounding Taxes

To handle the situation where some taxes compound and some don't, I've used a calculation_order attribute, which is just a number that indicates when to apply the tax.  Taxes with the same calculation_order value get applied to the same base.  Taxes with a higher calculation_order get applied to the net amount of the product and all taxes with lower calculation_order values.


Script to monitor database growth on daily basis

There is a stored procedure called SpaceUsedRating:

https://github.com/aleksey-vitsko/Database-Administrator-Tools

When you execute it exec SpaceUsedRating without any parameters, it will show space usage stats inside a given database

This procedure can also be called with parameter @command = 'log'

When called with @command = 'log' parameter, this SP will log result to logging table DatabaseGrowthLogger at database ServerLogsDB (DB and table need to be created)

Here is the script for creating logging table

USE [ServerLogsDB]
GO

/****** Object:  Table [dbo].[DatabaseGrowthLogger]    Script Date: 5/12/2020 12:11:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DatabaseGrowthLogger](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Server_Name] [varchar](50) NULL,
    [Database_Name] [varchar](250) NOT NULL,
    [Date_Full] [smalldatetime] NOT NULL,
    [Day_Of_Week] [tinyint] NULL,
    [Log_Date] [date] NULL,
    [Log_Hour] [tinyint] NULL,
    [Table_Count] [int] NULL,
    [Total_Rows] [bigint] NULL,
    [Database_Data_MB] [decimal](16, 2) NULL,
    [Database_Index_MB] [decimal](16, 2) NULL,
    [Database_Unused_MB] [decimal](16, 2) NULL,
    [Database_Allocated_MB] [int] NULL,
    [Database_File_Size_MB] [int] NULL,
    [Percent_Used] [decimal](5, 2) NULL,
    [Hours_Diff] [smallint] NULL,
    [Database_Allocated_Delta] [int] NULL,
    [Percent_Used_Delta] [decimal](5, 2) NULL,
PRIMARY KEY CLUSTERED
(
    [Database_Name] ASC,
    [Date_Full] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Hours_Diff]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Database_Allocated_Delta]
GO

ALTER TABLE [dbo].[DatabaseGrowthLogger] ADD  DEFAULT ((0)) FOR [Percent_Used_Delta]
GO

After you create ServerLogsDB..DatabaseGrowthLogger database and table, and deploy stored procedure SpaceUsedRating to all databases you want to monitor growth for, you need to schedule a job that will run once a day, for example at 23:58 UTC, and run below t-sql:

exec Database1..SpaceUsedRating @command = 'log'
exec Database2..SpaceUsedRating @command = 'log'
...
-- create SP in whatever number of databases you need to monitor growth

This will log daily growth of your databases, and when you check ServerLogsDB..DatabaseGrowthLogger table, it will look similar to below:

Looking at logging table, you will have an idea how much data inside your database has grown over time

Finally, you can add a step to the job, that will read last row from ServerLogsDB..DatabaseGrowthLogger and send it by email using sp_send_dbmail procedure


How to copy table data from one db to another?

No, you probably can't do that automatically.  If you are trying to dump and restore only fragments of the dependency chains, pg_dump and pg_restore have no way of knowing what your intentions are.  You are most likely going to need to spell them out for it, by supplying your own supplementary pre/post commands.

It is possible you could make a custom file to supply to pg_restore's -L command which correctly drops and recreates the constraints you need.


SQL Server AlwaysOn Availability Groups Cluster File Share Permissions

HA cluster - does each server node within the cluster need access to that file share or is it just the cluster name that needs write access to the file share?

Each node participating in WSFC should have access to the FS witness and the WSFC name or cluster name object( CNO) should have read write permission on fileshare folder and also at NTFS level.
A FS witness should not be part of WSFC and can reside on "any" windows machine which is accessible to both the nodes. Please note that the file share witness should reside on one of the machines which is part of domain in which WSFC is created.


Copy Database error Index was out of range. Must be non-negative and less than the size of the collection

"Trying to "Copy Database" from SQL Server 2016 to SQL Server 2012
  through SSMS."

You cannot copy a database from a 'higher' version (2016) of Sql Server to a 'lower' version of Sql Server (2012).  That's called 'downgrading' and it not supported.  There are numerous Internet posts on 'options' (like scripting individual objects, etc.) for doing this type of activity.

UPDATE 2/11/2017

There is an open connect item that appears to be addressing the same problem you're having


Your problem is with where condition.

Do have a look at mysql date/datetime formats, this will give you clear idea of how you should use date functions in mysql.

If you need data of previous year, simply change your where condition to:
YEAR(product.created_at) < YEAR(NOW())

Hope this helps!


Why mongodb logs commands in the log file and how to avoid that?

To increase the slowms value , follow the below steps:

For Exmple, If you want to increase the slowms vlaue to 500ms

db.setProfilingLevel(1, 500)

It will set the slowms to 500ms and will also turn on the profiling. So to stop the profiling, run the below command.

db.setProfilingLevel(0)

This can also be done by restarting MongoDB with the --slowms option.

Now, the commands will get logged in the log file only when the command takes more than 500ms to execute.


MS SQL Server 2016 - Single Partitioned Table vs Multiply Separated Tables

Is it really builtin Partitioned tables/indexes make developing more
  simple?

Compared to developing against independent tables that do not have view abstraction, partitioned tables are much easier to query because partitioning is transparent to application queries.

Partitioned table sliding window maintenance involves partition TRUNCATE (or SWITCH in the pre-SQL 2016 world) plus a partition function SPLIT/MERGE. Older data/index partitions can be compressed during the maintenance. One should also update partition stats after a TRUNCATE or SWITCH. DDL scripts for these operations are easily automated and less complex than scripts that create/drop/compress separate tables and indexes dynamically.

  Does it have really inconvenient limitations now in MS SQL Server 2016
  (as Enterprise, as or everything not so bad?

The biggest limitation with table partitioning is that the partitioning column must be part of all unique indexes and constraints.  As long as the tables you plan to partition table fit within these considerations, partitioning provides a number of manageability benefits without changes to app code. Note that Microsoft recommends partitioned tables over partitioned views when possible. Beginning with SQL Server 2016 SP1, partitioned tables are available in Standard Edition.

Columnstore compression often provides much higher compression that page or row compression on a rowstore. You can also create partitioned non-clustered indexes (compressed or not) on a partitioned columnstore to improve performance of queries that return relatively few rows. I suggest you consider both table partitioning and columnstore.

I'm not sure I fully understand why you would want to make queries faster only for last month. I'm guessing you just don't want the space overhead for indexes on data that are infrequently used. Given the low cost of storage nowadays, I wouldn't bother introducing additional complexity to save some index space. I suspect compression, especially columnstore, will eliminate the need to jump through such hoops.

If you are still unsure of the path forward, I suggest you develop prototypes of the candidate designs and choose the one that best addresses your needs with the least complexity.


Oracle - Is it possible to maintain transaction across multiple schemas?

yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).


From what I understand of your problem your "duplicity" is fine.  If your saying that all of the shops act totally independently then yes in your example shop E and shop D might have the same data but the data is not related so it might look like a duplicate, but I would not consider that a duplicate.

Another example, I am answering this question now, but I expect someone else is going to hit the submit button to a different question at exactly the same time, so in their "answer" table (or whatever) there will be two rows with "duplicate" data on it (the time of answer would be the same), is this a problem..... no because the data is unrelated, its simply a record of what happened.

The only time I would suggest normalising this further is if you had to attach extra information to the time which would be the same (so if there was information that would always be the same whenever any shop does any deal that starts at 6pm on a Sunday).  A counter argument to this could be do you have shops in different time zones?  If yes then 6pm could mean 6pm local time, but not at the same time, in this case what possible extra information could there be which would always be the same in every country, time-zone, shop and deal.


What are all these databases with funny names?

Sharepoint System Databases. And they are pretty self explanatory, or? Search service, secure store, session state - easy to understand.

THe rest is a GUID that is used in case multiple instances are installed - the Guid makes the database name unique.


IMHO your question requires a PIVOT solution.

CTE statment get the sum of seconds grouped by developer and day.

SELECT   account,
         m_idPlayer AS player,
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec,
         DATEPART(DAY, Start_Time) as Dy
from     logs
GROUP BY account, m_idPlayer, DATEPART(DAY, Start_Time)
GO

account      | player  |  Sec | Dy
:----------- | :------ | ---: | -:
developer    | 0000001 | 1984 |  2
infinity13   | 0000005 |  190 |  2
kevlaire05   | 0000004 |   56 |  2
runewars21   | 0000002 | 1230 |  2
speedyturtle | 0000003 |  514 |  2

PIVOT part, simply shows this data in columns. Keep in mind you must add all days of one month or deal with a dynamic query.

WITH grp AS
(
SELECT   account,
         m_idPlayer AS player,
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec,
         DATEPART(DAY, Start_Time) as Dy
FROM     logs
GROUP BY account, m_idPlayer, DATEPART(DAY, Start_Time)
)
    SELECT account, player, [1], [2], [3], [4]
    FROM
    (SELECT account, player, Sec, Dy
     FROM   grp
    ) src
    PIVOT
    (SUM(Sec) FOR Dy IN ([1], [2], [3], [4])) as Pvt
;

account      | player  |    1 |    2 |    3 |    4
:----------- | :------ | ---: | ---: | ---: | ---:
developer    | 0000001 | null | 1984 | null | null
runewars21   | 0000002 | null | 1230 | null | null
speedyturtle | 0000003 | null |  514 | null | null
kevlaire05   | 0000004 | null |   56 | null | null
infinity13   | 0000005 | null |  190 | null | null

dbfiddle here

In reference to the second (or main) question:

How to check if logs has 7 consecutive days?

I've used a STD solution, you can find it more by googling a bit.

SQL and contiguous data ranges.

create table logs
(
m_idPlayer varchar(20),
Start_Time datetime,
End_Time datetime,
TotalPlayTime int,
account varchar(20),
SEQ int);
insert into logs values
('0000001','2017/02/02 13:17.59','2017/02/02 13:23:11',  312,'developer',1),
('0000001','2017/02/03 13:23:51','2017/02/03 13:26:15',  144,'developer',2),
('0000001','2017/02/04 13:58:34','2017/02/04 14:13:42',  908,'developer',3),
('0000001','2017/02/05 14:16:45','2017/02/05 14:18:14',   89,'developer',4),
('0000001','2017/02/06 14:23:41','2017/02/06 14:25:59',  138,'developer',5),
('0000001','2017/02/07 14:27:00','2017/02/07 14:27:56',   56,'developer',6),
('0000001','2017/02/08 14:29:15','2017/02/08 14:32:25',  190,'developer',8),
('0000001','2017/02/10 14:26:09','2017/02/10 14:32:25',  376,'developer',9),
('0000001','2017/02/11 14:18:20','2017/02/11 14:37:21', 1141,'developer',10),
('0000001','2017/02/12 14:28:21','2017/02/12 14:37:42',  561,'developer',11);
GO

10 rows affected

DECLARE @StartDate datetime = '2017/02/02';
DECLARE @EndDate   datetime = '2017/02/12';

WITH gpr AS
(
SELECT   account,
         m_idPlayer AS player,
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec,
         DATEADD(DAY, 0, DATEDIFF(DAY, 0, [Start_Time])) as Dt
from     logs
GROUP BY account, m_idPlayer, DATEADD(DAY, 0, DATEDIFF(DAY, 0, [Start_Time]))
), grp2 AS
(
    SELECT account,
           player,
           Dt,
           DATEDIFF(DAY, '17530101', Dt) - ROW_NUMBER() OVER (PARTITION BY account ORDER BY Dt) AS GroupID
    FROM gpr
    WHERE Dt BETWEEN @StartDate AND @EndDate
)
SELECT     grp2.account,
         grp2.player,
         MIN(grp2.Dt )AS StartDate,
         MAX(grp2.Dt) AS Enddate
FROM     grp2
GROUP BY grp2.account,
         grp2.player
HAVING    COUNT(*) >= 7;

GO

account   | player  | StartDate           | Enddate
:-------- | :------ | :------------------ | :------------------
developer | 0000001 | 02/02/2017 00:00:00 | 12/02/2017 00:00:00

dbfiddle here


Interpolate values on Sorting in PSQL query

You can sort using an expression:

order by case
        when se.status in (2, 12, 22) then 1
        when se.status in (1, 11, 21) then 2
        else 3 -- just in case
      end

This would sort rows with status "processing" before rows with "completed"


COMMIT after EXPLAIN ANALYZE of a read-only query

It will result in either a

  WARNING:  there is no transaction in progress

or committing a previously started transaction (done by the BEGIN; command or a version of it), which might have included some non-readonly statements, too.

Before running the COMMIT;, you can check if you are in an open transaction from an other connection, issuing

SELECT * FROM pg_stat_activity WHERE usename = 'your_user';

You'll get (at least) two rows, one reporting exactly this query, and the other your other session.  If the state of the latter says idle in transaction (on version 9.1+), then you are in an open transaction there.  It is hard to tell what was run there beforehand, but you may be able to scroll back through your previous commands stored in your client's history.


Availability Group database stays in Reverting for too long after a failover

One thing to check whenever database recovery is running long, whether it's a normal restore or an AG failover, is your VLF count.  Having lots of VLFs (thousands or tens of thousands), or VLFs of unusual size (one or two extremely large VLFs) will cause this process to slow to a crawl.

Run the following command on your the database in question:

USE YourDatabaseName;
GO

DBCC LOGINFO;

Note: if you're on SQL Server 2016 SP2 or newer, you can use this dynamic management function instead of the DBCC command: sys.dm_db_log_info

The number of rows that comes back is the number of VLFs you have.  If that number is very large, or if the FileSize column shows extreme outliers among your VLFs, then you can likely resolve the slow recovery problem by (at a high level):

shrinking the log file as small as possible
growing it back out to its target size
making sure autogrowth is set to a reasonable number based on your typical log growth rate and the frequency of your transaction log backups

The details of fixing VLF sizing issues have been covered extensively elsewhere, here is one example: A Busy/Accidental DBA’s Guide to Managing VLFs


WordPress database error Table './databasename/wep_options' is marked as crashed and should be repaired for query

I think you are using MyISAM Engine

Don't use MyISAM for any case. It's becoming deprecated.

To get rid of this problem first repair your table.

Do the following:

REPAIR table wep_options;

Depending on the table size, this command takes time.

If your table is big and you can stop temporary mysql, I think myisamchk command is faster than repair command.

Check out end of this topic:
[https://mariadb.com/kb/en/mariadb/myisamchk/#repairing-tables][1]

After that To avoid repeating crash convert all tables to InnoDB Engine.

Example:

ALTER TABLE your_tables ENGINE=InnoDB;


upgrading sql server 2000 database to sql server 2012

You can easily restore your 2000 DB to any higher version of SQL Server. The issue you may face is feature deprecation. If you aren't using any deprecated features, the actual DB migration is a non-event for the most part.

You can also use Profiler to trace for deprecated calls.


Breaking Semisynchronous Replication in MySQL 5.5

The one thing you can probably do is to increase the sensitivity of the acknowledgement

Please look for this variables in /etc/my.cnf

[mysqld]
rpl_semi_sync_master_timeout=5000

When rpl_semi_sync_master_timeout (Default is 10000 or 10 seconds) is set to 5000, Replication should switch a Master from SemiSync to Async if no acknowledgement is received from the Slave in 5 seconds (5000 milliseconds). You may want to lower this even less than 5000.

You may need to check your network performance. As long as you not doing geographic distance replication, you should the following: On a Separate NIC, use a crossover cable over 192.168.xx.xx so as not have faster replication response. Also, check the switch for dropped packets.

You should not have to restart the server every time. As a quick-and-dirty band-aid, make up this SQL script:

STOP SLAVE IO_THREAD;
SELECT SLEEP(5);
START SLAVE IO_THREAD;

Simply write a cronjob to execute these commands every 5 minutes. Again, this is a band-aid. Otherwise, due diligence on the networking side is in order.


Large table load slow mysql php high resources usage

check indexes and table structure

for 99% id - is INT
so

SELECT picture_url FROM table WHERE id='1'

born scan of all column and convert it to archer, same if no indexes - it start scan all table and sort

at the same time

SELECT * FROM table

just start send data without transformation

if I right - just change query for

SELECT picture_url FROM table WHERE id=1

and add index if it not already there


How to delete duplicate records in MySQL, in a table without IDs?

There is no any field combination which identifies the record uniqually.

I see at least 2 different solutions.

First solution: move unique records to a copy of table and replace original table.

CREATE TABLE temp LIKE products;
INSERT INTO temp
    SELECT DISTINCT * FROM products;
DROP TABLE products;
RENAME TABLE temp TO products;

Second solution: add temporary autoincrement, delete records using it, and drop temp field.

ALTER TABLE products ADD COLUMN temp SERIAL PRIMARY KEY;
DELETE t1.*
    FROM products t1
    LEFT JOIN ( SELECT MIN(temp) mintemp
                FROM products
                GROUP BY field1,field2 /* , ... */ , fieldN) t2
        ON t1.temp=t2.mintemp
    WHERE t2.mintemp IS NULL;
ALTER TABLE products DROP COLUMN temp;

UPDATE

In second variant: the additional column definition as a primary key is redundant. It is enough to use

ALTER TABLE products ADD COLUMN temp SERIAL;


Don't use pg_resetxlog except as a last resort. (I think I'll submit a patch to add an --i-have-read-the-manual option to pg_resetxlog, I see it abused too often.)

You should dump the server and restore the dump.

The transactions logs are crucial for data integrity. Without them, you might have incompletely written transactions, transactions that were committed but never actually made it to the main database, transactions out of order, and more.

So it's not as simple as losing a few recent transactions. You lose the write-ordering that preserves data integrity.

Index blocks might not match the data in the heap. Visibility maps etc may be invalid. Queries could produce incorrect results, disk blocks could be split (resulting in errors on read), and more.

It's really a very good idea to dump, re-initdb, and restore. Keep a copy of the old damaged database around just in case. Even then, your database is damaged - the dump won't magically fix it if data is inconsistent and wrong, and it's possible you may have to repair the dump to get foreign keys to restore correctly, etc.


auto_increment incremented on non-affecting queries

This shouldn't be right, should it?

It's right.

Databases (not just MySQL) generally ensure that sequences will only ever increase (once a transaction is committed), but not that they will be without gaps. For instance, TFM says:

  You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

and:

  As of MySQL 5.1.22, InnoDB can avoid using the table-level AUTO-INC lock for a class of INSERT statements where the number of rows is known in advance, and still preserve deterministic execution and safety for statement-based replication. Further, if you are not using the binary log to replay SQL statements as part of recovery or replication, you can entirely eliminate use of the table-level AUTO-INC lock for even greater concurrency and performance, at the cost of permitting gaps in auto-increment numbers assigned by a statement and potentially having the numbers assigned by concurrently executing statements interleaved.

Consider the case where two uncommitted transactions exist in an MVCC system like InnoDB. They may both need a unique primary key before being committed, but the first transaction may fail. Hence you will end up with a gap.

You can't avoid this behaviour, so embrace your gappy primary keys :)

https://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html


The error is that you're trying to use a constraint that is already identified.

SELECT
   constraint_name,
   table_name
FROM
   information_schema.table_constraints
WHERE
   constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
   constraint_name;

Should give you the constraints you've already established. Also, you can use SHOW ENGINE INNODB STATUS or SHOW INNODB STATUS after you get the error to get the logs and more information.


How to allow remote connections to mysql from specific IPs only?

You can use hostname in grant statements as well, as you are using shared ip then you can limit it with hostname specific grant such as below

grant all on db.* to 'user'@'hostname' identified by 'password';

To allow from all IP's/hosts

grant all on db.* to 'user'@'%' identified by 'password';

For localhost only

grant all on db.* to 'user'@'localhost' identified by 'password';


UPSERT with ON CONFLICT using values from source table in the UPDATE part

Multiple problems.
Your setup, extended:
CREATE TABLE a (
  pk_a int PRIMARY KEY
, a int
, comment text  -- added column to make effect clear
);

CREATE TABLE b (
  pk_b int PRIMARY KEY
, b int
, comment text
);

INSERT INTO a VALUES (1, 11, 'comment from a')
                   , (2, 22, 'comment from a');

INSERT INTO b VALUES (1, 77, 'comment from b');

This works:
INSERT INTO b (pk_b, b, comment)
SELECT pk_a, a, comment
FROM   a
ON     CONFLICT (pk_b) DO UPDATE  -- conflict is on the unique column
SET    b = excluded.b;            -- key word "excluded", refer to target column

Result:
TABLE b;

 pk_b | b  |    comment
------+----+----------------
    1 | 11 | comment from b   -- updated
    2 | 22 | comment from a   -- inserted

The problems

You are confusing table_a and A in your demo (like @Abelisto commented).
Using legal, lower-case, unquoted identifiers helps to avoid confusion.

Like @Ziggy mentioned, ON CONFLICT only works for actual unique or exclusion constraint violations. The manual:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

Consequently, ON CONFLICT (b) cannot work, no constraint there. ON CONFLICT (pk_b) works.

Like @Ziggy also mentioned, source table names are not visible in the UPDATE part. The manual:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access
to the existing row using the table's name (or an alias), and to
rows proposed for insertion using the special excluded table.

Bold emphasis mine.

You also cannot use column names of the source table in the UPDATE part. It must be column names of the target row. So you really want:
 SET    b = excluded.b

The manual once more:

Note that the effects of all per-row BEFORE INSERT triggers are
reflected in excluded values, since those effects may have contributed
to the row being excluded from insertion.


Write Oracle proc/function to create dynamic views with changing column headers

Why does it have to be one query?
Why not retrieve the column headers in one step, pull the real data in another and then stitch the two together, if that's even required?
If this is being done under application control (as opposed to a single database "query"), you may not need to do this "reassembly" at all.  It might be possible to populate your column headers separately from the real data.


Getting generate_series(date, date) does not exist

PostgreSQL 8.4+

This is because generate_series currently requires a step when the inputs are not int, bigint or numeric from the docs

returns: timestamp or timestamp with time zone
generate_series(start, stop, step interval)

As compared to

returns: int, bigint or numeric, step defaults to 1.
generate_series(start, stop, step DEFAULT 1)

To remedy, simply add a day,

SELECT d
FROM generate_series(
  'YESTERDAY'::date,
  'TODAY'::date,
  '1 day' -- REQUIRED STEP
) AS gs(d);

PostgreSQL pre-8.3

Note: you may also get this error if you're using PostgreSQL 8.3 or lower in which case the generate_series doesn't support non-numeric types

Amazon Redshift

generate_series may or may not be currently supported in Amazon Redshift, which isn't really PostgreSQL anyway. If it still isn't supported you're likely to get an error with or without the step,

ERROR:  function generate_series(timestamp without time zone, timestamp without time zone, "unknown") does not exist

See also,

Amazon Redshift API Version 2012-12-01
Unsupported PostgreSQL Functions

Many people report this to work, but the docs have not been updated to reflect that.


Pass an array or record to a function in PostgreSQL?

Postgres has very flexible handling of arrays and composite types. This may be the sort of thing you are trying to do:

create type my_type as (val1 integer, val2 integer);

create function my_function(arr my_type[]) returns text language plpgsql as $$
begin
  return arr::text;
end;$$;

select my_function(array[row(1,2),row(3,4)]::my_type[]);

| my_function       |
| :---------------- |
| {"(1,2)","(3,4)"} |

dbfiddle here


mysql - Right join not retriving correct data

Your problem is in the WHERE clause

WHERE A.user_id = '1'

You are creating correctly the RIGHT JOIN but this condition is forzing to showthe row only if the Log table (renamed as A) is equal to 'Y'.
You need to change this condition for example:

SELECT SUM(TIMESTAMPDIFF(MINUTE, A.start_time, A.end_time)) AS prep_time,
                    B.subject_name,
                    A.subject_id
                FROM prep_learn_log A
                RIGHT JOIN prep_subject B ON A.subject_id = B.subject_id
                    AND B.active = 'Y'
                WHERE COALESCE(A.user_id, '1') = '1'
                GROUP BY A.subject_id

I


Goto to the following folder

cd /usr/lib64/mysql/plugin
ls -l

If you see something like this

-rw-r--r-- 1 root root 11827298 Sep 13  2010 ha_innodb_plugin.a
-rwxr-xr-x 1 root root      962 Sep 13  2010 ha_innodb_plugin.la
lrwxrwxrwx 1 root root       25 Jun 23  2011 ha_innodb_plugin.so -> ha_innodb_plugin.so.0.0.0
lrwxrwxrwx 1 root root       25 Jun 23  2011 ha_innodb_plugin.so.0 -> ha_innodb_plugin.so.0.0.0
-rwxr-xr-x 1 root root  1204496 Sep 13  2010 ha_innodb_plugin.so.0.0.0

You have located the InnoDB Plugin files

Getting back to your question :

  1.Is it possible to install the InnoDB plugin just on the master and not on the slave?

Yes

  2.Are there any other implications to installing the plugin?

Once the plugin is installed and you configure my.cnf for the Master, do not use the options for InnoDB Plugin on another server where the plugin was not installed.

  3.What parameters do I need to change in my.cnf to better utilize the plugin and the multi core options ?

Please see my past posts on this

Possible to make MySQL use more than one core?
Multi cores and MySQL Performance

  4.Any recommended tutorial for installing the plugin?

Please follow the instructions from the MySQL Documentation on InnoDB Plugin Installation