Replica Set in MongoDB by using IPs instead of domains?

From your primary machine run the following command

mongo --host COM2 --port 27018

You should be able to connect to the MongoDB running in the secondary node using the above command. If it fails to connect, check the following configuration settings

Check the bindIp settings of the secondary node configuration file, it should allow connection from 192.168.1.1:27018.
Check the port number 27018 is accepting inbound connections in the secondary node. If not add an entry in IP Table.
Check if secondary nodes hosts file is updated with primary machines DNS name
and IP Address.

Follow the same for Primary node as well and try to connect to each machine from other machine using the first command. If everything working fine you should be able to set up your replica set.


Unique key constraint violation not escalated to parent stored procedure

You are missing the error handling piece (i.e. TRY...CATCH). Please see the stored procedure template that I posted in the following DBA.StackExchange answer:

Are we required to handle Transaction in C# Code as well as in stored procedure


Re-map DB Users/Logins after Restore WITH STANDBY

Your users from db on server A will be moved to the database on server B with the backup. Then all you need is to recreate the logins with the same sid. You do this using WITH SID option:

CREATE LOGIN My_login WITH SID = 0x14585E90117152449347750164BA00A7

the sid obviously is your user sid that you can find in sys.database_principals:

select name, sid
from sys.database_principals
where type_desc = 'SQL_USER';


How to Implement Different Object Types as Similar Tables?

Assuming that the app will be treating these various shop-types in the same manner for some operations (operations that do not care what type of shop that it is), then my preferred approach is to use a subclass / inheritance model. I have detailed this out in a couple of other answers here:

Don't know how to transform variable entity into relational table
Can't convert this type of ER diagram into SQL or relational tables

The basic concept would be as follows (using Microsoft SQL Server T-SQL semantics).

First you need the main entity table. It represents various "types" so those need to be defined in a lookup table (and the lookup table should have a matching enum in the app layer). Please note that I am using a TINYINT here which holds values from 0 to 255. Use the smaller numeric type that will meet your needs.

CREATE TABLE dbo.ShopType
(
  ShopTypeID TINYINT NOT NULL PRIMARY KEY, -- NO IDENTITY / auto-increment!
  ShopType   VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.Shop
(
  ShopID     INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ShopTypeID TINYINT NOT NULL REFERENCES dbo.ShopType (ShopTypeID), -- FK to ShopType
  ShopName   NVARCHAR(100) NOT NULL,
  VisitorsLastWeek INT NOT NULL DEFAULT (0),
  -- {OtherCommonFields},...
  CreateDate DATETIME NOT NULL DEFAULT (GETDATE()), -- or GETUTCDATE()
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

Next you create a subclass table for each particular "type". It is sometimes appropriate to share a subclass table for multiple "types", but just be careful when doing that so one type doesn't evolve differently than the other type(s) that might be sharing the same subclass table. Please note that the PK of the subclass tables is the same field as the PK in the parent class (i.e. the [Shop] table) and even FKs to it. The reason is that the row in both tables (e.g. [Shop] and [ShopRepair] that have the same value for [ShopID] are together a complete entity; the row in the subclass table does not represent anything by itself and doesn't need a separate ID.

CREATE TABLE dbo.ShopFood
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  MostPopularItem  NVARCHAR(50) NULL,
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

CREATE TABLE dbo.ShopRepair
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  CurrentComputerRepairs SMALLINT NOT NULL DEFAULT (0),
  RepairEstimatedHours TINYINT NOT NULL DEFAULT (0),
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

CREATE TABLE dbo.ShopRetail
(
  ShopID INT NOT NULL PRIMARY KEY REFERENCES dbo.Shop(ShopID),
  NextPromotionDate DATETIME NULL,
  CurrentWeekSalesEstimate MONEY NULL,
  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()
);

To simplify SELECT operations, you can create Views against each type, if you like:

CREATE VIEW dbo.FoodShop AS
SELECT sh.ShopID, sh.ShopTypeID, sf.MostPopularItem
FROM   dbo.Shop sh
INNER JOIN dbo.ShopFood sf
        ON sf.ShopID = sh.ShopID;

CREATE VIEW dbo.RepairShop AS
SELECT sh.ShopID, sh.ShopTypeID, sr.CurrentComputerRepairs, sr.RepairEstimatedHours
FROM   dbo.Shop sh
INNER JOIN dbo.ShopRepair sr
        ON sr.ShopID = sh.ShopID;

CREATE VIEW dbo.RetailShop AS
SELECT sh.ShopID, sh.ShopTypeID, sr.NextPromotionDate, sr.CurrentWeekSalesEstimate
FROM   dbo.Shop sh
INNER JOIN dbo.ShopRetail sr
        ON sr.ShopID = sh.ShopID;

EDIT:
I forgot to add in the "audit" fields of [CreateDate] and [ModifiedDate]. I have now added them to the tables above. Please note that:

these fields are not in the [ShopType] table as there really is no purpose here, but that is a bit subjective and some people still like to know at least when the records were created, so maybe [CreateDate] is fine there
the [CreateDate] field is not in the subclass tables as the assumption is that those rows are created at the same time as the parent record. But when it comes to updates, those might just touch one of the two tables, depending on which fields are being updated.  If using SQL Server, it is possible to create the records for both tables in a single statement even, using the OUTPUT clause as follows:

CREATE PROCEDURE dbo.Shop_CreateRepairShop
(
  @ShopName NVARCHAR(100),
  @CurrentComputerRepairs SMALLINT,
  @RepairEstimatedHours TINYINT,
  @ShopID INT OUTPUT = -1
)
AS
SET NOCOUNT ON;

INSERT INTO dbo.Shop (ShopName, ShopTypeID)
  OUTPUT INSERTED.ShopID, @CurrentComputerRepairs, @RepairEstimatedHours
  INTO   dbo.ShopRepair (ShopID, CurrentComputerRepairs, RepairEstimatedHours)
  VALUES (@ShopName, 2); -- assuming ShopTypeID for "Repair Shop" = 2

SET @ShopID = SCOPE_IDENTITY();


Cannot get LOB data from SQL Server transaction logs

If you absolutely want to do this then I would advise you use a 3rd party product that does this for you. These undocumented functions are just that, along with the log structure.

  I can get real data from RowLogContents0 column when I have IN_ROW_DATA allocation unit type in my table.

Each type of log record may have its own way of storing the data and could change between operations. There may be additional considerations for each record type as well... for example, in-memory records will not be properly processed using your above methods. There also may be additional considerations between versions of SQL Server.

  Note : I don't want to use any third party tool to do this, because I am trying to make a third party tool for my program.

Then I wish you the best in your trial and error to figure out what works and what doesn't work for each specific type of interaction of DML, row store, etc.

The only other recourse you may have is talking with Microsoft if you're a partner and attempting to obtain help with this endeavor.

Disclaimer: I work for Microsoft.


Database function SELECT with optional argument

select d.field1, d.field2, COUNT(*) as record_count

WHERE d.fieldX = argument OR argument IS NULL

from my_table d
group by d.field1, d.field2;

Be aware that this may not always produce an efficient access plan. If you experience performance issues, you'll likely be better off executing one of the two query variants conditionally, something like
IF argument IS NULL THEN
   SELECT ... -- without conditions
ELSE
   SELECT ... WHERE fieldX = argument
END IF


Migration of MariaDB/MySQL to another server without downtime

Are you using LVM on Linux servers? In that case, you can have a minimal downtime solution:

Switch the database to master, stop the server just a few seconds to make a LVM snapshot, start the master again, mount the snapshot in a temp directory to copy the data to the new (bigger) server and then configure a slave with that. Then you can start the slave, let it sync with the master until is up to date and finally promote the slave as master.

Detailed howto: https://www.percona.com/blog/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/


Set var="NEW.this_word_column" in a trigger function if "word" is present in a column name

You can use INFORMATION_SCHEMA views and special variables: TG_TABLE_SCHEMA, TG_TABLE_NAME

Quoted from docs:

  When a PL/pgSQL function is called as a trigger, several special
  variables are created automatically in the top-level block. They are:

  TG_TABLE_NAME: Data type name; the name of the table that caused the
  trigger invocation.

  TG_TABLE_SCHEMA: Data type name; the name of the schema of the table
  that caused the trigger invocation.

Given next example:

create table a (id int primary key, latitude int, longitude int);
create table b (id int primary key, _loc_latitude int, _loc_longitude int);
create table c (result text);

create function trg_tables()
returns trigger as
$$
declare
  varlat text;
  varlng text;
  cmd text;
  vallat int;
  vallng int;
begin
  varlat := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%latitude%');

  varlng := (select column_name
            from   information_schema.columns
            where  table_schema = TG_TABLE_SCHEMA
                   and table_name = TG_TABLE_NAME
                   and column_name like '%longitude%');

  execute 'select $1.' || varlat
  using NEW
  into vallat;

  execute 'select $1.' || varlng
  using NEW
  into vallng;

  execute format('insert into c values (''lat: '' || ''%s'' || ''  lng '' || ''%s'' )',
                 vallat::text, vallng::text);

  return NEW;
end;
$$
language plpgsql;

create trigger trg after insert on a
  for each row execute procedure trg_tables();
create trigger trg after insert on b
  for each row execute procedure trg_tables();

insert into a values (1,11,1);
insert into b values (1,20,2);
select * from c;

| result         |
| :------------- |
| lat: 11  lng 1 |
| lat: 20  lng 2 |

db<>fiddle here

It's just an example on how to use INFORMATION_SCHEMA and special variables inside a trigger, but maybe a Postgres pro-user can give you a better solution.


Plan Cache Pages by Database in SQL Server 2008R2

Here are queries that will give you buffer breakdown and plan cache breakdown per database. Note that plan cache is broken down by MB, not by pages - I don't know of a handy way, off the top of my head, to calculate the pages used by the plan cache (you'd think it's simple division, but not necessarily).

-- buffer pool breakdown

SELECT
  db = DB_NAME(database_id),
  c = COUNT(DISTINCT page_id),
  size_MB = CONVERT(DECIMAL(12,2), COUNT(DISTINCT page_id)*8.0/1024)
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY database_id
ORDER BY c DESC;

-- plan cache breakdown

SELECT
  db = DB_NAME(t.[dbid]),
  size_MB = CONVERT(DECIMAL(12,2), SUM(p.size_in_bytes)/1024.0/1024)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE t.[dbid] < 32767
GROUP BY t.[dbid]
ORDER BY size_MB DESC;

On my test instance there are cases where plan cache is larger than the buffer pool for a given database.

The buffer pool contains both the data cache and the plan cache. But AFAIK sys.dm_os_buffer_descriptors only contains information about the data cache (index and data pages), so it does not necessarily reflect the entire buffer pool. The cases I saw above seem to support that theory.


SQL Server 2008 R2 - Unable to delete Login

Short intro

To connect to the server you have to have a login to access the server. To access a database you have to be a user in that database. All Logins are member of the server role public which can access the master database.

So to find users to drop within a database you query sys.database_principals and to find logins sys.server_principals. When you drop a login it's not removed from the user list of the databases and the SID is there until you drop the user.

So henryhr exists as a user in the master database but not as a login on the server so try use master; drop user henryhr; to drop the user henryhr from the master database


What is the optimum way to store, update, and analyze integer data for 100+ attributes?

Assumed business rules
As I currently understand your specifications, the following assertions are of prime relevance:

A User inscribes zero-to-many Texts.
A User registers zero-to-many Characters.
A Character is counted in zero-to-many Sums.
A Text contextualizes one-to-many Sums.
A User computes zero-to-many Sums.

Logical data model
And then, from the above enumerated assertions, I have derived the IDEF1X logical data model shown in Figure 1.

As you can see, with this structure you could have a series of Sums related to a specific Character in the context of a particular Text. Instead of performing an UPDATE each time that a new Sum is computed, you just have to INSERT a new row, which would hold the exact point in time that such Sum was calculated.
In this way, you do not need to have a table with about 100 columns, instead, as stated in comments, I suggest using a Character table that stores each character occurrence in an individual row.
I have also added a User entity in order to depict a more complete scenario, since I assume that all the sums (or counts) are computed by a determined user, probably with the aid of an automatic method.
This structure allows you to analyze with ease the trends of the sums that a given Character presents in relation to a particular Text by comparing the set of values contained in the aforementioned Sum series.


PgAdmin 4 - How to connect to database using unix socket via ssh tunnel?

That should work fine with the correct ssh command.
To forward from a local Unix socket to a remote one runs
ssh -L /tmp/.s.PGSQL.5555:/var/run/postgresql/.s.PGSQL.5432 -N laurenz@dbserver

Then you would use host /tmp and port 5555 to connect.
To forward a local TCP socket to a remote Unix socket, run:
ssh -L 5555:/var/run/postgresql/.s.PGSQL.5432 -N laurenz@dbserver

Then you would use host 127.0.0.1 and port 5555 to connect.


NVARCHAR issue when migrating from SQL Server to MySQL with MySQL migration wizard

You are trying to execute this statement on the SQL Server side:

SELECT  [id],
        CAST([endretFra] AS NVARCHAR(8000)) AS [endretFra],
        CAST([endretTil] AS NVARCHAR(8000)) AS [endretTil],
        CAST([endringskilde] AS NVARCHAR(255)) AS [endringskilde],
        CAST([sistEndretBruker] AS NVARCHAR(255)) AS [sistEndretBruker],
        CAST([sistEndretFulltNavn] AS NVARCHAR(255)) AS [sistEndretFulltNavn],
        [sistEndretTidspunkt],
        CAST([felt] AS NVARCHAR(255)) AS [felt],
        CAST([handling] AS NVARCHAR(255)) AS [handling],
        [aliste_id],
        [utvalg_id]
FROM    [gruppergister].[dbo].[Endring]

That statement tries to convert two columns to the NVARCHAR data type with a length of 8000. However,the maximum allowed length of NVARCHAR in SQL Server is 4000. So, whatever tool you are using is producing invalid SQL Server syntax.

As a side note, if you are trying to create a development environment for a SQL Server hosted database, you probably should use SQL Server. The syntax for some statements is quite different and using the from DBMS can only cause confusion and grief.

You can install a trial version of SQL Server for free, if your project is less than 180 days, or you can buy the developer edition for about $50 (e.g. here). If your database is less than 4GB and does not use advanced features, you could also go with the free SQL Server Express Edition.


What is the way to paginate last updated rows from multiple tables?

You may add a column timestamp and a trigger that update the column when the row is updated.

Then, sorting by that column will get your result.


Transactional replication - Table / Stored Procedure changes coming over, but not new Tables / Stored Procedures

New objects need to be added since they are not part of the original publication  when you established replication between the publisher and subscriber.

You can add objects to the existing publication w/out needing to stop replication activity. Please see this link and the section titled "Replication Maintenance"


First, I love that you have isolated your actual messages into it's own table, that was going to be my first suggestion but you had already gotten there.
Otherwise, I think your first design is good.  By keeping the message log itself (the meta data) isolated from the message body you will (with proper indexes) be able to find messages very quickly.
My only word of advice for you would be to do the delete check as part of the transaction that is writing the message (and to make that a small number).  This will make the cleanup action part of the message writing logic and keep things moving along quickly.
Something like:
Delete TOP (100) old messages that satisfy criteria
Insert new message


SSIS Script Task appears to run, but actually doesn't

There are 3 things you can check:

(1) The TargetServerVersion property

Check that you are selecting the appropriate TargetServerVersion property

GoTo Project Properties >> Configuration Properties >> TargetServerVersion

(2) ForceExecutionResult property Value

Click on Fail Task and press F4 to show the perperty Tab, check that ForceExecutionResult is set to False.

(3) Run in 32-bit mode

Try running the package in 32-bit mode:

GoTo Project properties >> Debugging >> Run64BitRuntime  = False


How to get MS Access to reach a Linked server on MSSQL

As Sean Lange mentioned, MS Access cannot use a linked server which is only accessible from within the SQL Server. But MS Access is external to SQL Server.

If you think of MS Access as the Client in a 2-tier Client/Server project, then it should be clear that you can use the 3-part naming convention (ServerName.Schema.Object) to access the SQL Server.

If you have sufficient rights, you can update data in SQL Server from MS Access through the common use of T-SQL to manipulate SQL Server data.

If you want the SQL Server itself to fetch data from a MS Access database, then you could create a Linked Server in SQL Server that would reach out through a provider to get data from the MS Access.

https://msdn.microsoft.com/en-us/library/ms190479.aspx gives a sample provide setup from the Northwind database.

EXEC sp_addlinkedserver
   @server = N'SEATTLE Payroll',
   @srvproduct = N'',
   @provider = N'MSDASQL',
   @datasrc = N'LocalServer';
GO


How can I replicate MySQL data from many servers to one?

Sorry.  If you are trying to have many Masters and one Slave -- that is not supported.  (Some third party addons may have such.)  It is better to have the 5 servers all write to the 'headquarters' Master.  Network latencies are probably not a serious problem.


How can I find the VLF count for a SQL 2005 database without running dbcc loginfo?

I went and checked the POSH script I had that tied to an Excel spreadsheet:

http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/

And you mentioned that DBCC LOGINFO was 'verbose', which makes me want to ask, have you tried using the WITH NO_INFOMSGS option?


Each column of each table of each database can have a different CHARACTER SET and COLLATION.  There is no downside unless you JOIN on a column that has different charset and/or collation.

Ascii (7-bit stuff) is a subset of latin1 and of utf8mb4.  As such, the encoding is identical, so no space difference will be seen for purely ascii text.

However, there are a few hiccups when you declare unnecessarily large strings, especially with wider character sets.  Probably you won't hit any hiccups.  Please provide SHOW CREATE TABLE for a review of likely cases.

The client must declare what charset it is using, so UTF-8 is what you would need there.  In that case, any non-utf8mb4 columns would be automatically converted when going between client and server.

More

When storing into VARCHAR(...) CHARACTER SET utf8mb4, there is a 1-byte or 2-byte length overhead, plus

"abc" (ascii) will take 3 bytes - each ascii character takes 1 byte,
"ÛØÎ" will take 6 bytes - most European chars take 1- or 2-bytes,
"José" will take 5 bytes - the "é" takes 2 bytes,
"" (Emoji) will take 12 bytes; each Emoji is 3 or 4 bytes.  Ditto for Chinese.
"$€" (currency symbols) will take 4 bytes -- 1 for "$" plus 3 for Euro.

With CHARACTER SET latin1:

"abc", "ÛØÎ", "José", "$€" can be correctly stored in latin1, using 3,3,4,2 bytes, respectively.
Emoji, all Asian languages, Greek, Cyrillic, and others cannot be represented in latin1.  Garbage ensues.  The particular garbage depends on the various settings; this Q&A lists common garblings.

For clear sailing:

The column must be declared (explicitly or implicitly) with a CHARACTER SET that can hold the characters.  (English will work with any character set, but Emoji will work only with utf8mb4.)
The connection parameters (or SET NAMES) must correctly say what encoding is used in the client.
There must be a mapping between the two charsets.  (English always maps.  (Western Europe can map among latin1, utf8, utf8mb4, but not much else.)


Does SQL Server 2017 CU1 break clusterless availability groups?

My division is is doing an upgrade from SQL Server 2008R2 with mirroring to SQL Server 2017 with clusterless availability groups.

So you're upgrading versions but REMOVING high availability and disaster recovery? Clusterless AGs are called "Read-Scale" AGs and do not give high availability and you can argue on the disaster recovery part...

  The group could be failed over multiple times without issue. Ah, but add a second database and issues would arise on the failovers. One of the databases would invariably wind up in a not synchronizing state. No amount of fiddling could resurrect it.

I've been seeing this when a configuration only replica (came in CU1) isn't used with Read-Scale AGs that are being used to fail over. Read-Scale wasn't made to fail over and all that jazz, it was made to horizontally scale out read copies for intense read situations (or as a way to replica across Windows/Linux for migrations). I must reiterate, "clusterless" AGs are not made for HADR. If this is part of your use case, use WSFC or Pacemaker (Linux). Info on Configuration Only Replica.

  A pertinent error message in the logger was "Failed to update Replica status due to exception 35222." This seems to be a message related to clusters, but since we are clusterless I was confused.

There should be an error directly before this, that's the actual error you want to look into. This doesn't have anything to do with clustering and is not a clustering error, it's a replica error.

  After we uninstalled CU1 on both replicas, I was able to create the AG and add 22 databases (including the two original). Failovers were without issue

This goes back to pre-configuration only replicas for adding into Read-Scale replicas. Again, surprised you didn't run into a few different issues as Read-Scale isn't made for HADR.

  On a side note, automatic seeding did not always work with multiple databases. The operation would fail with a "Seeding Check Message Timeout".

Seems unrelated, but you never know - could be a side effect of whatever was going on. Impossible to say at this point.

  I thought CUs were going to be tested at the same level as SPs.

You're correct. Changes in behavior between CUs (much like also were involved in SPs) can and do happen given the newer model (even in the SP + CU days this happened). I'd be interested to see if the configuration replica solves your issue since it was specifically added in CU1 for metadata safety as metadata issues for the replicas can and did happen since Read-Scale again wasn't made for HADR.


Replication testing is a Materialized view Refresh equivalent of continious update?

For most practical purposes, UPDATE is pretty much the same as DELETE + INSERT in PostgreSQL. The difference between your tests is that test1 refreshes the materialized view twice and runs one more VACUUM.

Refreshing a materialized view is something entirely different. It will effectively drop the materialized view and re-create it from scratch. If you use REFRESH MATERIALIZED VIEW CONCURRENTLY, you will see activity similar to INSERT and DELETE.

max_standby_archive_delay does not apply to streaming replication at all.
max_standby_streaming_delay governs if there will be a delay in applying data changes on the standby server in the case of replication conflicts.

Both your tests will eventually generate replication conflicts as soon as autovacuum removes old row versions from a_table or b_table, but only if hot_standby_feedback = off. If hot_standby_feedback = on, you will not see a replication conflict until a random autovacuum run truncates empty pages off the end of one of the tables, which requires a short ACCESS EXCLUSIVE lock.

You could reliably trigger a replication conflict by selecting from the materialized view on the standby while it is being refreshed on the primary, because that takes an ACCESS EXCLUSIVE lock on the materialized view.
hot_standby_feedback: If set to on, then if either of your tests runs long enough while the query on the standby is running, you will slowly see the tables grow and get bloated, because old row versions won't get removed on the primary.

If set to off you will get replication conflicts as soon as one of the tables is vacuumed.


Issues installing SQL Server 2008 on Windows Server 2008 R2

Thanks Aaron B. for the help. The issue seems to be with the server itself. I had SA reimage the server and now SQL installed without any issues.


Connect two remote oracle databases as a schemas on each other

Several issues, in no particular order.
Your SELECT syntax is wrong. To select a table from another database the syntax is
select some_columns from remote_table@remote_db;

Note the "@remote_db".  Here 'remote_db' refers to an entry in the local database's tnsnames.ora file, which in turn resolves to the remote db in question.   CORRECTION:  Here 'remote_db' is the name of the database link.
Second, you say you have no permission to create a db link. Well, someone is going to have to create one.  That is the mechanism by which one database queries another. Period. Full stop.
Third, you say you find no db links when you query ALL_LINKS.  The name of the view is ALL_DB_LINKS.  And that only shows the links that the current user has permissions on.  If you want to see all links in the database, you query DBA_DB_LINKS, as a user that has permissions to query that view.  Plus, I see nothing in your question description that would cause me to suspect that there indeed are any db links.


How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?

please, try this:
with stable as (
SELECT
    ROW_NUMBER() over(order by [code]) as [ID]
    , [code]
    , [name]
    , [type]
    , CASE
        WHEN (SUM([web]) = 1 AND SUM([mobile]) = 1) THEN 'web&mobile'
        WHEN (SUM([web]) = 1 ) THEN 'web'
        WHEN (SUM([mobile]) = 1) THEN 'mobile'
        ELSE null END AS deviceType
FROM (
    select [id], [code], [name], [type], [web], [mobile]
    from (
        SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
    ) as source
    pivot
    (
        COUNT(deviceType) FOR deviceType IN ([web],[mobile])
    ) as tb
) source
group by [code], [name], [type])
,ttable as (
SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
)
MERGE
    INTO ttable as T
    USING stable as S
    ON t.id = S.id
    WHEN MATCHED THEN
        UPDATE SET
            T.[code] = S.[code], T.[name] = S.[name], T.[type] = S.[type], T.[deviceType] = S.[deviceType]
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE
;


Using the same table structure and data

use test
DROP TABLE IF EXISTS test_dba;
CREATE TABLE `test_dba` (
  `id` int(11) DEFAULT NULL,
  `date_en` date DEFAULT NULL,
  `count_rec` int(11) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO test_dba VALUES
(1,'2000-01-01',15),(1,'2000-02-01',20),
(2,'2000-01-01',20),(2,'2000-02-01',30),
(3,'2000-01-01',20),(3,'2000-02-01',17);
SELECT * FROM test_dba;

Here is that data loaded

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test_dba;
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE `test_dba` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `date_en` date DEFAULT NULL,
    ->   `count_rec` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO test_dba VALUES
    -> (1,'2000-01-01',15),(1,'2000-02-01',20),
    -> (2,'2000-01-01',20),(2,'2000-02-01',30),
    -> (3,'2000-01-01',20),(3,'2000-02-01',17);
Query OK, 6 rows affected (0.20 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_dba;
+------+------------+-----------+
| id   | date_en    | count_rec |
+------+------------+-----------+
|    1 | 2000-01-01 |        15 |
|    1 | 2000-02-01 |        20 |
|    2 | 2000-01-01 |        20 |
|    2 | 2000-02-01 |        30 |
|    3 | 2000-01-01 |        20 |
|    3 | 2000-02-01 |        17 |
+------+------------+-----------+
6 rows in set (0.00 sec)

mysql>

Please note that I added id 3 to demonstrate negative differences

Here is my proposed query

SELECT
    id,ABS(diff) diff,
    IF(diff>0,'Increase',IF(diff<0,'Decrease','NoDifference')) delta
FROM
(
    SELECT A.id,B.count_rec firstcount,C.count_rec lastcount,
    C.count_rec - B.count_rec diff
    FROM
    (
        SELECT id,MIN(date_en) mindate,MAX(date_en) maxdate FROM test_dba
        WHERE date_en BETWEEN '2000-01-01' AND '2000-02-01' GROUP BY id
    ) A
    INNER JOIN test_dba B ON A.id=B.id AND A.mindate=B.date_en
    INNER JOIN test_dba C ON A.id=C.id AND A.maxdate=C.date_en
) AA;

Notice I used the following:

MAX() / MIN() on the dates rather than on the counts
ABS() to get absolute value of difference

Here is the output

mysql> SELECT
    ->     id,ABS(diff) diff,
    ->     IF(diff>0,'Increase',IF(diff<0,'Decrease','NoDifference')) delta
    -> FROM
    -> (
    ->     SELECT A.id,B.count_rec firstcount,C.count_rec lastcount,
    ->     C.count_rec - B.count_rec diff
    ->     FROM
    ->     (
    ->         SELECT id,MIN(date_en) mindate,MAX(date_en) maxdate FROM test_dba
    ->         WHERE date_en BETWEEN '2000-01-01' AND '2000-02-01' GROUP BY id
    ->     ) A
    ->     INNER JOIN test_dba B ON A.id=B.id AND A.mindate=B.date_en
    ->     INNER JOIN test_dba C ON A.id=C.id AND A.maxdate=C.date_en
    -> ) AA;
+------+------+----------+
| id   | diff | delta    |
+------+------+----------+
|    1 |    5 | Increase |
|    2 |   10 | Increase |
|    3 |    3 | Decrease |
+------+------+----------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!


DB2 archive log extraordinary growth for any operation after runstat/reorg

Finally found what actually happen after 150GB in one day for archive log.
After doing offline runstat again, It seems that we ignore the "SQL2310N The utility could not generate statistics. error -930" on table RECONCILIATION_INFO

At first, we thought it might not be an important error but when we dig through db2diag.log, we found error 964 (transaction log full) as well during runstat however, we didn't found any error during reconcile in db2diag.log

So we increase LOGPRIMARY value to 25 (ISIM default is 13) and then, runstat work smoothly with a few GB increased in archive log. After that, reorg and runstat again didn't eat up space anymore.

We did some test by reconcile as usual and now disk space is not increase anymore.

I still wonder why runstat can cause a disk space problem like this even we just run it manually, restart database didn't help at all once we trigger it.


Figured it out!

The key here was piping it out to an array successfully. I've had a lot of drawbacks here but you've got to work thru them.

$pids = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\id process" -and       $_.path -like $filter} | select cookedvalue | ForEach {$_.cookedvalue}
function GetMemoryUsage  { ...code here... }
function GetAppID { ...code here.... }
$myresults = @()
$x = foreach ($ids in $pids) {
 $thisapp = GetAppID $ids
 $thismem = GetMemoryUsage $ids
 $robj = New-Object System.Object
 $robj | Add-Member -type NoteProperty -name WP -value $thisapp
 $robj | Add-Member -type NoteProperty -name "Process ID" -value $ids
 $robj | Add-Member -type NoteProperty -name Memory -value $thismem

 $myresults += $robj
}
$myresults | ft -auto

The great thing abou this method is that you can pipe in an infinite number of variables and as long as you can relate these to the array then you're in business.


More efficient/cleaner way of doing a select that traverses a specific column?

A recursive CTE will find that for you.  By adding in the desc and TOP 1 it will look for the longest possible path.  If there were two paths it could follow it will pick one based on the ORDER statement

;WITH follow(idstart, idend, oldurl, target, depth, path) as
(
    SELECT S.id idstart, S.id idend, S.oldurl, S.target, 1 AS depth, cast(S.oldurl as varchar) AS path
    FROM mytable as S
    UNION ALL
    SELECT S2.id idstart, p.idend idend, S2.oldurl, p.target, p.depth + 1 AS depth, cast(RTRIM(p.path) + '->' + S2.oldurl as varchar) AS path
    FROM follow AS p JOIN mytable as S2 on S2.target = p.oldurl
    WHERE p.target is not null
)
SELECT TOP 1 * FROM follow
WHERE idstart=1
ORDER BY depth desc;


Place your tnsnames.ora in a directory of your choice, then set the TNS_ADMIN environment variable.

Open CMD, enter sysdm.cpl
In the Advanced tab, select Environment Variables
Under "System Variables", click on 'New' and enter TNS_ADMIN as the name, and the path where your TNSNAMES.ORA resides as your value

Windows steps from here.

On Linux, it's as easy as setting the variable in your shell startup script, eg .bashrc:

export TNS_ADMIN=/path/to/instant/client


Mysql -Which Column table the index should be added

There are several semantic issues with the structure of the query.

Using OR with conditions from various different tables, in combination with the Cartesian product of these 4 tables looks very weird. I find unlikely that this is what you actually want.
Using DISTINCT. That's probably needed only because of the weird structure mentioned above.

So, I suggest a few things:

Get rid of the DISTINCT
Rewrite the joins using proper JOIN .. ON syntax or - probably makes more sense in this case - rewrite using EXISTS subqueries which can be combined with OR.

The query becomes:

SELECT u.UserID,
       u.Role,
       u.FirstName,
       u.LastName,
       u.Email
FROM   Users AS u
WHERE  EXISTS
         ( SELECT *
           FROM Hires as h
           WHERE u.UserID = h.User_ID AND h.Offices_OfficeID = 3
         )
   OR  EXISTS
         ( SELECT *
           FROM Contractors AS c
           WHERE u.UserID = c.User_ID AND c.Offices_OfficeID = 3
         )
   OR  EXISTS
         ( SELECT *
           FROM ContractorCompanies AS cc
           WHERE u.UserID = cc.User_ID AND cc.Offices_OfficeID = 3
         )
 ;

If the above query returns the wanted results, then add an index on (Offices_OfficeID, User_ID) on each of the 3 tables.

I assume you already have an index on Users (UserID).


Create a .pgpass file on the postgres server in the $HOME directory of the postgres Linux user. You can then store the password of that database user in that file.
Then you can remove the password option from the user mapping, as Postgres (or more precisely: libpq) will pick up the password from the password file.
Don't forget to set password_required to 'false' on the user mapping unless the user mapping is for a superuser (which is an altogether awful thing to do).


Usually, when you want to keep the Access front end, you migrate from Access DB to SQL Server (Express).

  But what do I do about the MySQL backend? Where should it be placed?

Database servers are usually located on a dedicated "server".  Now days, that server can be Virtualized.  Many regulations require limited physical access to that server (eg physically placed in a locked "server room").

  Should I move the mysql workbench to the network drive?

It looks like MySQL Workbench can be used as an Admin tool.  Only developers and DBAs need it installed.  Placing it on a network drive may not be the best method when you are in a disaster situation.

  does the team need to have the ODBC connector installed in their computers?

Everyone using the Access front-end will probably need the ODBC driver for the backend (DB Server).  This can make deployment (and maintenance) a small nightmare.

  how do I deploy this for the team to use.

I don't know.

  PS: I am a new developer and just learning about databases

The first commandment of a Database Administrator (DBA) is Thou shall not lose data.  Make backups and test them on a regular basis.

Work with the Business to find out how frequently you need to do backup, how often you should test, and how long to keep the backups.  There are Financial commitments that need to be made and probably some Legal ones too.  These decisions are "above your pay grade".

Try to document everything.  "Security" should be one of the first. (Who can do What to Which pieces of data). This information is usually broken down into ROLEs.

Backup/Recovery procedures will also need to be well documented.  This includes the Business Requirements gather from above.

Use a Code Repository to store your CREATE scripts, etc.


SSAS Tabular model Date Dimension with Time possible?

Generally I keep Date and Time in separate dimensions as this reduces the overall cardinality but even with your date dimension having a time element, you should not have a problem obtaining this value. The example below was written on my own system and tested in DAX Studio:

DEFINE
    MEASURE Sales[TransactionCountPerTimePeriod] = COUNTROWS('Sales')
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[DateKey],
    "Transaction Count", [TransactionCountPerTimePeriod]
);

In order to get this work within a model, just take the definition of the measure and include it, like so:

TransactionCountPerTimePeriod:=COUNTROWS('Sales')

Then, after publishing your model you should see it show up in DAX Studio, Power BI, Excel or any other reporting tool:

-- no need to define the measure if it is already in the model
-- just use it
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[DateKey],
    "Transaction Count", [TransactionCountPerTimePeriod]
);


Monitoring replication status from the subscriber

You can use the canary method to monitor replication status on the subscriber. Check it out here in the "Easy Replication Monitoring: Alert on Latency with Canary Tables" section.

With this method, you would be writing to a table on the publisher that has just one column. Update the table every minute with CURRENT_TIMESTAMP. Monitor the table on the subscriber and fire off an alert if the difference between the table and the current time has exceeded your threshold.


MongoDB as a log storage. Choosing shard key

For even write distribution, the SessionId looks like a much better idea (Or a hashed index on other fileds). Proper indexing should solve queries then.

The other two options have very low cardinality (and therefore very large chunks).


How to determinate in a trigger direct inserts and inserts via stored procedure

You can't determine it. All you have available to you is the INSERTED and DELETED tables. You could probably cheat but it's a gawdawful, bloody hack. Define a view with an "extra" column, what it is doesn't matter. All your proc insert/update assign one value to that column, direct updates supply a different (or non-existent value). Create an instead of trigger on that view and then do your update logic based on the source flag

CREATE VIEW dbo.vwTableX
AS
SELECT
    X.*
,   'T' AS fake_source_column
FROM
    dbo.tableX X

GO
CREATE TRIGGER insertHack ON dbo.vwTableX
INSTEAD OF INSERT
AS
BEGIN

    SET NOCOUNT ON

    IF EXISTS (SELECT * FROM INSERTED I WHERE I.fake_source_column = 'P')
    BEGIN
        -- perform logic for PROC sourced data
        INSERT INTO
            dbo.tableX
        SELECT
            I.col1
        ,   I.col2
        -- everything but our fake column
        FROM
            INSERTED I
        WHERE
            I.fake_source_column = 'P'
    END

    IF EXISTS (SELECT * FROM INSERTED I WHERE I.fake_source_column = 'D' OR I.fake_source_columns IS NULL)
    BEGIN
        -- perform logic for direct access data
        INSERT INTO
            dbo.tableX
        SELECT
            I.col1 * 2 -- or whatever special logic you wish to apply
        ,   I.col2
        -- everything but our fake column
        FROM
            INSERTED I
        WHERE
            I.fake_source_column = 'D' OR I.fake_source_columns IS NULL
    END
END
GO

-- lather, rinse, repeat for update and/or delete trigger

So that's a solution, but really, what is the problem you are trying to solve?  Why do you care whether the DML comes from proc or non-proc?


Table partition in aws MYSQL RDS master slave setup

First, let's discuss what benefit you will get from partitioning.  Please provide SHOW CREATE TABLE (with the partitioning).  Partitioning, in itself, does not provide any performance benefits.  Let's see the query/queries that you think will benefit.

pt-online-schema-change is likely to be a way to convert to partitioning with virtually no downtime.  But check carefully; there may be restrictions on partitioning.

The manual says nothing about whether you can have replication between a non-partitioned on the master and a partitioned table on the slave.  The silence of the manual worries me; there could be issues.


How do I limit dynamic data masking with a where clause?

I don't think that this (masking only certain rows in the table) can be done with Dynamic Data Masking.
You could create a view that will do what you want to do by doing something like this:
create table email (id int identity, email varchar(100));
insert into email values ('mail1@hotmail.com'),('email2@gmail.com'),('email3@mycmp.com');
go

create view email_filter as
select case when email like '%@mycmp.com' then email
else left(email,charindex('@', email,0)) end "email"
from email
go

select * from email_filter;

drop table email
drop view email_filter

Note that this will not mask the data from the "email" table so if you want to make sure the users cannot access it, you will have to restrict the access.
Note also that this have some limitation. If you are expecting the view to return result for a query like :

Select * from email_filter where email like '%hotmail.com'

it will not return any row.


1 Stop the slave on the first master

mysql> STOP SLAVE;

If the second master becomes a read only then the first master should no longer replicate from it.

2 Change the 2nd master's configuration to read only.

mysql> SET GLOBAL READ_ONLY=1;

Add read_only=1 to my.cnf for the next restart

Read Only variable

3 Unlock the tables on the second master

UNLOCK TABLES;

You don't need to lock tables on the second master (now slave)

4 Secure privileges

Redefine privileges on the 2nd master, follow this answer here : Should a MySQL replication slave be set to read only?

5 Verify slave status on 2nd Master, now slave.

mysql> show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.11.128.52

If the second master is not replicating then you may have to reinitialize the replication to the first master.

A. On first master, identify current log and position

SHOW MASTER STATUS;

+------------------+----------+--------------------------+------------------+
| File             | Position | Binlog_Do_DB             | Binlog_Ignore_DB |
+------------------+----------+--------------------------+------------------+
| mysql-bin.000001 |      107 | base1, base2             |                  |
+------------------+----------+--------------------------+------------------+
1 row in set (0.00 sec)

B. On second master now slave, reinitalize replication

Stop SLAVE ;
CHANGE MASTER TO MASTER_HOST = '10.11.128.52',
MASTER_USER = 'repli', MASTER_PASSWORD = 'repli',
MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
Start SLAVE;

C. Show status on second master(now slave)

mysql> show slave status \G;
Slave_IO_State: Waiting for master to send event
Master_Host: 10.11.128.52


Is It Needed to Normalize of Date Columns

There is not a real need to have a dates table. However, there are some cool things you can do with a dates table such as add columns to that table that have different pieces of information about that day such as holidays or weekends or week of the year, etc. Instead of doing a function to get that information.


Oracle itself will generally generate redo in the process of running various background jobs.  For example, by default in 10g, there is a job that gathers statistics on objects.  That will generate redo.  By default, 10g has a job that gathers AWR information as well every hour.  That will generate redo as well.  There are a number of other background jobs (checkpointing, updating the SCN to timestamp mapping table, etc.) that are going to be running as well which will be generating redo on an ongoing basis just as a matter of course.

If you run the query

select current_scn, systimestamp
  from v$database

in a (from a user standpoint) idle database, you'll note that the current_scn is incrementing every few seconds.  That is Oracle doing various sorts of housekeeping.  That housekeeping will generate a small amount of redo each time.  Over the course of a full day, it's pretty common that this would add up to enough for a couple of archived logs even if your system is otherwise pretty idle.


Inspecting the status of a long-running COPY statement

There is not yet a formal facility for monitoring the progress of a COPY operation.  You can use the pageinspect extension to get an estimate of the number of rows, even uncommitted ones.  Assuming the existence of the table has been committed, and the table was empty other than the in-progress COPY, then you could use:

select sum(
        (select count(*) from
          heap_page_items(get_raw_page('pgbench_accounts', x))
        )
    )
from generate_series(0, (pg_relation_size('pgbench_accounts')/8192)::int-1) as f(x);

(This still assumes you are using the default blocksize of 8192.)


You cannot use an aggregate like CountRows() in a calculated field in your dataset. But you can use CountRows() as an expression in a textbox (alone or within a table) scoped to your dataset. Now that you have your dataset created, you can put a textbox on the report and populate it with the expression =CountRows("DataSet1"), and it will provide the correct answer.

Otherwise, you will need to modify your dataset to include the rowcount. It would seem that using the expression in the report would be the desired option as modiying the dataset would give you a column populated with either a running total or the total rows repeated on each column.


Performance issue with TOP (1) on a nullable column

The query takes a lot of time because it finds no rows.

The plan first is setup top pick one row from tabSparePart where sparepartname = '1270-3132'. It then moves along to the index scan of tabData. For each row fetched from tabData there is a clustered index seek in locSL followed by a clustered index seek in tabDataDetail which also checks for not null on gsp_part_price and finally returns the values of fiSparePart and GSP_Part_Price. fiSparePart is then compared against the idSparePart returned from the Index Seek in tabSparePart. If they are the same the query immediately terminates and return the gsp_part_price. If they are not the same the index scan in tabData moves to the next record and all the checks are executed again. That keeps happening until there is a row returned or all rows in tabData have been processed.

As it turns out you get a totally different query plan when you do top(1000).

You can use the optimize for hint to make SQL Server think you actually want 1000 rows when you only need one.

Your scalar valued function would then look something like this:

CREATE FUNCTION [dbo].[FunctionName]()
RETURNS INT
AS
BEGIN

  DECLARE @TOP INT
  DECLARE @RET INT

  SET @TOP = 1

  SELECT TOP (@TOP) @RET = dd.gsp_part_price
  FROM  tabdata
          INNER JOIN locsl
                  ON tabdata.fisl = locsl.idsl
          INNER JOIN locgsp
                  ON locsl.figsp = locgsp.idgsp
          INNER JOIN tabdatadetail dd
                  ON dd.fidata = tabdata.iddata
          INNER JOIN tabsparepart sp
                  ON dd.fisparepart = sp.idsparepart
  WHERE  sp.sparepartname = '1270-3132'
          AND dd.gsp_part_price IS NOT NULL
  ORDER BY Claim_Submitted_Date DESC
  OPTION (OPTIMIZE FOR(@TOP = 1000))

  RETURN @RET

END


You would need to check the security setting of the package configuration (inside the package). The default for a package is "EncryptSenstiveWithUserKey", which means only that user can modify the package or read the connection manager objects.

You also don't provide how the packages are deployed. If they are deployed to the Package Store or SQL Server then the security setting mentioned above does not apply. If they are using file level deployment who executes the job or owns it can play a role in the issue you are having....meaning you will have to adjust the packages.


How to search with partitions in my query?

As per my understanding, you are using a function in the where clause. That's why the explain plan is not able to identify the exact partition number in table's partitions.

Function You used : (date_sub(now(), interval 2 day))

For using index: you have to create an individual index for that column like

create index index_tei_trn_date on transaction_event_items(tei_trn_date);

If explain plan still doesn't use the index, use hint index. For more details on this read: http://dev.mysql.com/doc/refman/5.1/en/index-hints.html


How to determine efficacy of index on child side of foreign key

There are basically three situations where you would want to index the child in addition to the parent in an FK/PK relationship:

If you ever select from ITEM based on order_id (obviously!)
If you ever run queries on from the JOIN of ITEM and ORDERS with a filter on ORDERS
As with 2, but with a filter on ITEM that is less selective than that on ORDERS

In both cases, ITEM will benefit from becoming the inner part of the join and an index from on the child will help support a loop join strategy.


Granting only SELECT permissions on specific views to a specific user

Yes.  What you have will do what you want.  I do question the need for UserA to have db_owner access even on database A though.  Do they have a need to be able to drop the database?  Run backups that could interrupt your backup chain?  Change recovery model (same problem)?

Generally even if a developer has complete control over a given database I would still limit them to db_datareader, db_datawriter, db_ddladmin and EXECUTE (database level).  With very few exceptions that's sufficient for a development environment.  Permissions of course get fewer the higher up the SDLC stack you go.


You can create a .my.cnf file in your home directory then store the user/password info on the [mysqldump] section.

vi ~/.my.cnf

[mysqldump]
user = dump_user
password = dump_password

Then, with your mysqldump command, use the --defaults-extra-file option to specify explicitly your conf file has your are in crontab.

/usr/bin/mysqldump --defaults-extra-file=/home/max/.my.cnf  --single-transaction  --all-databases >/space/mydump.sql

Max.


The recommendation for clustered indexes is that they are ever-increasing or ever-decreasing, but that doesn't mean they have to be. GUIDs aren't increasing or decreasing unless you are using sequential GUIDs. Most people don't use sequential GUIDs.

If you are worried about the performance hit from page splits, then lower your fill factor to accommodate more inserts before a page split is needed. This is what is advised for GUIDs too if the GUID is the clustered index (I'm not saying it should be the clustered index, I'm saying if it is). Be careful how low you lower it though as it will impact read performance, which probably is important to you since you mentioned the clustered index on OrderID helps your queries.


There is no way to avoid the drawbacks of symmetric encryption on the server.

PostgreSQL has to have access to the key and the encrypted data to decrypt it if you want to "avoid having the application decrypt". There are only two sources that can do it.

the consumer
the database (in which case it's also subject to being written in a log file)

Of course, if you have a single access point to the data like an webserver or the like, you could potentially decrypt there too and keep the keys off the server.


Trying (and failing) to create a DVD cataloging database table design

Per Martin Fowler, there are basically three ways to model inheritance in a relational database:

Single Table Inheritance: everything goes into one table and you use nullable columns that may or may not be present for the
Class Table Inheritance: base class goes in one table with one-to-one relationships with sub-classes
Concrete Table Inheritance: each concrete class is given a distinct table (what you have now)

Some good answers here going into more detail:

https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database
https://stackoverflow.com/questions/1567935/how-to-do-inheritance-modeling-in-relational-databases

Which you choose depends on exactly what you want to do, and there is not always one right answer, although you could probably lean towards class table inheritance as a "default" that is probably correct, and then work from there.

I'd suggest using class table inheritance you generalise movies and episodes into a "work" (or whatever terminology you think best) and then have movie or series as a lightweight entitity used to classify these "works".

This is all just quick suggestions on how you might do it- remember, there is not necessarily One and Only One Right Way, it depends like Evan says on what exactly your use case is and what exactly you are trying to model.

One small thing to point out- don't use "case" as a table name. You CAN use it but you would have to escape it everywhere as it is a SQL reserved word and this is a pain in the neck and will make you wish you didn't. Use something else.


Unable to open .sps.bak database backup file with SQL Server 2012 Express edition

That's probably a Sharepoint backup. Restore it in Sharepoint, or ask for a SQL server backup

Your error message shows that you are connected to a SQL 2008 server, not SQL 2012, and that the backup was performed on a SQL 2008 R2 server. You need an R2 server, or SQL 2012, to restore it.


Update multiple rows with different date for each row

Since you have a primary key userid, you can do it with a query like :

update p
set passDays = '60',
    passChangeDate = dateadd(dd, offset, '2017-01-01 00:00:00')
from [POSUSER] p
join (
    select  userid,
            row_number() over(order by userid) / 10 as offset
    from [POSUSER]
    ) t on p.userid = t.userid

This query will work the same with any type of primary key (numeric or not) or any set of unique columns. But if there is no way to build a unique identifier of a row, the query won't work and you'll have to make your update another way.


How to simplify SSAS deployment from DEV to PRO environment

I will tell you the way we deploy in my place SSAS projects between different environments. We use a set of Powershell scripts that:

Part 1:

get the last version from the build server and deploy the file to generate the last working version of XMLA;
take the generated XMLA from the build server and copy it to the QA server;

Part 2:

Now, on the QA server, we change manually the server name, the database name (set as parameters to the PS script), we verify that all required scripts are there;
one PS script runs the resulted XMLA on the current QA server, so the SSAS database is created (not processed though);
a XMLA script that commands the full process of the cube and its dimensions;

All of these steps are done as automatically as possible, with as little intervention from the developer/QA guy as possible. We do, though, insert some eyeballing in between, just to keep our minds safe. I won't say that it's the best, but it works and gets the job done. Before the last step I'm sure you can insert any XMLA script to remove existing roles and create new ones. And to change a PS script to execute some new SQL or XMLA script is not hard at all. I suppose that the roles and permissions don't change that much, so they can be scripted and reused.

PS: this is an environment with domain users and fixed servers (so we can use shares, domain accounts and all that fluff). If you have another environment it might be a more difficult to get it done.


Can I edit single timestamp so it recalculate all the following timestamps in a table?

I wouldn't recommend to create a trigger for this, as it can have unwanted side-effects.
A stored procedure just for this purpose seems more appropriate to me. Something like this:

DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks
    (`id` int, `task` varchar(13), `start_time` time, `end_time` time, `minutes` int)
;

INSERT INTO tasks
    (`id`, `task`, `start_time`, `end_time`, `minutes`)
VALUES
    (1, 'Send email', '12:00', '12:10', 10),
    (2, 'Design banner', '12:11', '12:36', 25),
    (3, 'Call Jake', '12:37', '12:42', 5)
;

DROP PROCEDURE IF EXISTS adjust_task_times;
DELIMITER $$
CREATE PROCEDURE adjust_task_times(IN p_new_time int, IN p_which_row int)
BEGIN
DECLARE diff_times int;

SET diff_times = (SELECT p_new_time - minutes FROM tasks WHERE id = p_which_row LIMIT 1);

UPDATE tasks SET minutes = p_new_time, end_time = ADDTIME(end_time, diff_times * 100) WHERE id = p_which_row;
UPDATE tasks SET start_time = ADDTIME(start_time, diff_times * 100), end_time = ADDTIME(end_time, diff_times * 100) WHERE id > p_which_row;

END $$
DELIMITER ;

CALL adjust_task_times(15, 1);

SELECT * FROM tasks ORDER BY id;

+------+---------------+------------+----------+---------+
| id   | task          | start_time | end_time | minutes |
+------+---------------+------------+----------+---------+
|    1 | Send email    | 12:00:00   | 12:15:00 |      15 |
|    2 | Design banner | 12:16:00   | 12:41:00 |      25 |
|    3 | Call Jake     | 12:42:00   | 12:47:00 |       5 |
+------+---------------+------------+----------+---------+

In case you're wondering, this

ADDTIME(start_time, diff_times * 100)

looks a little odd. Why the 100? In this example the diff_times variable has a value of 5. 5 times 100 equals 500, which is in this case short for 00:05:00

For more details see the manual entry for ADDTIME().


Move content between filestream containers

According to Paul Randal (who is as reliable as they come) there is no simple way to do this and you essentially have to recreate the table in a new filegroup that is pointed to your new filestream location.  The link is there so you can see for yourself, but this looks like the only real alternative.  It is not an ideal one, obviously but it should get you the end result that you need.   But not in a simple fashion, I am sorry to say.


Querying sums of grouped consecutive rows in PostgreSQL 9

select min(country) as country,
       min(begintimestamp) as first_begin_ts,
       sum(distance) as distance
from (
  select t1.*,
         sum(group_flag) over (order by begintimestamp) as grp
  from (
      select *,
             case
                when lag(country) over (order by begintimestamp) = country then null
                else 1
              end as group_flag
      from travel
  ) t1
) t2
group by grp
order by first_begin_ts;

The inner most query (alias t1) creates a number each time the country changes). The second level query (alias t2) then does a running sum on those flags, which essentially gives each consecutive set of countries a different number. The outer most query then groups by that number and sums the distance. The min(country) is necessary to make the group by operator happy, but as all rows with the same grp have the same country anyway, it doesn't matter.

SQLFiddle: http://sqlfiddle.com/#!15/fe341/1


Command fails as CmdExec job but succeeds from console

Potential Workarounds

Since you're running PowerShell through SQL Server CmdExec from an
agent job, you could try calling a PowerShell script with your saved
PS logic in it rather than running raw PowerShell commands to see if
that makes a difference for a potential workaround.
Try explicitly putting the full OS path on the server to point to
PowerShell.exe and then pass your PowerShell commands after that
in case the issue has to do with environmental variables not working correctly for PowerShell within the CmdExec shell.

  My question: Why does the same command fail as a job, but succeed from the console when both executed from the same user account on the
  same server?

Potential Reasons

It could be an issue when calling PowerShell.exe thru the SQL
Server Agent job with the CmdExec shell and how it interprets
security context of the account it's run from or as (e.g. the service
account) when it tries to run PowerShell.exe that way so it's not able to authenticate the execution access to the EXE due to this.
Check and read up on the version of PowerShell installed, the version of SQL Server installed, and the Windows OS all these are on and run from for "known" issues related to version incompatibilities in this respect - it may be a known issue depending on ALL the specifics in this environment.

A working way I run PowerShell from SQL Agent Jobs

You will need to:

Create the [user account object] service account in AD (<DomainName>\<ServiceAccount>)
Create a SQL login i.e. the service account in #1 (<DomainName>\<ServiceAccount>)
Create a SQL credential with that same (<DomainName>\<ServiceAccount>) account credentials,
Create a SQL Server Agent Proxy account to use for the "Run As" from the SQL Agent job to execute PowerShell

Below is a script of the T-SQL part of this process which I run after doing the above 4 steps, and it always works in my environment to accomplish what seems to be similar to what you've explained that you're trying to accomplish. I also have to ensure the AD account has a strong/complex password, and set it to never expire.

--- // Create login on SQL Instance for domain\PSSQLJobs service account if it does not exist
IF NOT EXISTS (
        SELECT *
        FROM sys.server_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
BEGIN
    CREATE LOGIN [domain\PSSQLJobs]
    FROM WINDOWS WITH DEFAULT_DATABASE = [master]
        ,DEFAULT_LANGUAGE = [us_english]
END

USE [master]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[master]
    TO [domain\PSSQLJobs]

USE [msdb]

IF NOT EXISTS (
        SELECT *
        FROM sys.database_principals
        WHERE NAME = N'domain\PSSQLJobs'
        )
    CREATE USER [domain\PSSQLJobs]
    FOR LOGIN [domain\PSSQLJobs]
    WITH DEFAULT_SCHEMA = [dbo]

EXEC sp_addrolemember N'db_datareader'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentOperatorRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentReaderRole'
    ,N'domain\PSSQLJobs'

EXEC sp_addrolemember N'SQLAgentUserRole'
    ,N'domain\PSSQLJobs'

GRANT CONNECT
    ON DATABASE::[msdb]
    TO [domain\PSSQLJobs]

--- // Create Credential *** Type in password of account into value of SECRET ***
USE [msdb]

CREATE CREDENTIAL PSSQLJobs
    WITH IDENTITY = 'domain\PSSQLJobs'
        ,SECRET = '*******'

--- // Create PowerShell Proxy account and give PSSQLJobs access to it
USE [msdb]

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'ExecutePowershell'
    ,@credential_name = N'PSSQLJobs'
    ,@enabled = 1

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name = N'ExecutePowershell'
    ,@subsystem_id = 12

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'ExecutePowershell'
    ,@login_name = N'domain\PSSQLJobs'


SQL Server Replication causing locks on table

That procedure is what replication uses to update the table (you probably figured this out already).  I'd take a look at the execution plan and the waits for the procedure when it is running to see what the waits are showing.

I haven't specifically seen that before, but I can imagine it when the procedure is being run from a very remote distributor with a crappy network link in between.


Get username and/or IP address responsible for a query

You can use the below query to pull currently executing requests and the corresponding session/connection information:

select
    r.session_id,
    s.login_name,
    c.client_net_address,
    s.host_name,
    s.program_name,
    st.text
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s
on r.session_id = s.session_id
left join sys.dm_exec_connections c
on r.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
--where st.text like '%your query string to search for%';

What you are querying in your question, sys.dm_exec_query_stats, is cumulative and aggregated statistics that aren't by session/connection.  In other words, that information isn't stored there, nor could it easily be retrieved.

You could put in place a SQL Trace or XE session to grab this data that you're looking for, basically monitoring for the occurrence of your offending query(ies).

There are a couple of ways to get the data you're looking for, but unfortunately with your given query there isn't an easy way to mold that into what you're looking for.  Simply different types of data (you provide aggregated data, but you're looking for snapshot and granular data).


This is definitely a head-scratcher, but here's one possibility, especially if you're running MySQL 5.1 or earlier.  MySQL Bug #37346 addresses an issue where there are locks at two different layers that are (at least partially) unaware of each other's existence... an uncommitted transaction is holding locks inside the storage engine (invisible to MySQL's core), while the ALTER TABLE is holding a different kind of lock, outside the storage engine (invisible to InnoDB).

In the bug report, when the ALTER TABLE is almost done, it bumps into the inner locks and ultimately times out with the message you see.

This is referenced as "fixed" in the release notes for 5.5.3 but I didn't find it mentioned in 5.1.x, which suggests to me that it might still exist there.  It seems like a long shot but it did seem worth mentioning.

Until I found the above, my initial thoughts had to do with the table that is referenced by fk_foo_1... I was going to speculate that InnoDB might be trying something unnecessary related to validation of the foreign key constraint (unnecessary, since the constraint is about to disappear)... or that an uncommitted update or delete on the referenced table might be setting locks on the table you're trying to alter... an update or delete "over there," needing to be validated or trying to cascade "over here," while the ALTER TABLE is underway, and not waiting as it should for the lock held by the ALTER TABLE.


Run Powershell Script From Agent As Administrator

Create a PowerShell proxy in SQL Server agent that uses a privileged Windows account via a SQL Server credential.

CREATE CREDENTIAL docs.

How to create a proxy

That will allow you to run PowerShell as an administrator, directly from SQL Server.

Creating a credential is the first step, and consists of running a T-SQL statement like this:

CREATE CREDENTIAL MyPowerShellCredential
WITH IDENTITY = 'DOMAIN\AdministratorAccount'
    , SECRET = 'AdministratorAccountPassword'

Once the credential is created, you need to create a SQL Server PowerShell Proxy.  This should work:

USE msdb;
EXEC dbo.sp_add_proxy @proxy_name = N'MyPowerShellProxy'
    , @enabled=1
    , @description = N'Proxy used to run PowerShell as an administrator'
    , @credential_name = N'MyPowerShellCredential';

After creating the proxy, you need to enable it for use by the PowerShell subsystem.  This code does that:

USE msdb;
EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'MyPowerShellProxy'
     , @subsystem_name = N'PowerShell'

Once you have the proxy created, you'll need a SQL Server Agent Jobstep configured to run PowerShell, which will automatically make use of the PowerShell proxy.

This is a sample SQL Server Agent Job that will run your code:

USE [msdb]
GO
DECLARE @jobId binary(16);

EXEC  msdb.dbo.sp_add_job @job_name = N'ResetAppPool'
        , @enabled = 1
        , @notify_level_eventlog = 0
        , @notify_level_email = 2
        , @notify_level_page = 2
        , @delete_level = 0
        , @category_name = N'[Uncategorized (Local)]'
        , @owner_login_name = N'sa'
        , @job_id = @jobId OUTPUT;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'ResetAppPool'
    , @server_name = N'(LOCAL)';

EXEC msdb.dbo.sp_add_jobstep @job_name = N'ResetAppPool'
    , @step_name = N'Step1'
    , @step_id = 1
    , @cmdexec_success_code = 0
    , @on_success_action = 1
    , @on_fail_action = 2
    , @retry_attempts = 0
    , @retry_interval = 0
    , @os_run_priority = 0
    , @subsystem = N'PowerShell'
    , @command = N'# Load IIS module:
Import-Module WebAdministration
# Set a name of the site we want to recycle the pool for:
$site = "Default Web Site"
# Get pool name by the site name:
$pool = (Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
# Recycle the application pool:
Restart-WebAppPool $pool'
    , @database_name = N'master'
    , @flags = 0;

EXEC msdb.dbo.sp_update_job @job_name = N'ResetAppPool'
    , @enabled = 1
    , @start_step_id = 1
    , @notify_level_eventlog = 0
    , @notify_level_email = 2
    , @notify_level_page = 2
    , @delete_level = 0
    , @description = N''
    , @category_name = N'[Uncategorized (Local)]'
    , @owner_login_name = N'sa'
    , @notify_email_operator_name = N''
    , @notify_page_operator_name = N''
GO

You could modify the job to send an email to you when it runs, just so you know it worked.

Regarding this error you're seeing:

  The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {longguidhere} and APPID {longguidhere} to the user NT SERVICE\SQLSERVERAGENT SID (longguidhere) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

That indicates User Account Control is running in Admin Approval Mode, requiring every action that requires elevation to ask the user for consent.   Since SQL Server Agent is running as a service, there is no chance for the elevation prompt to run, hence the failure.  You can assign the NT SERVICE\SQLSERVERAGENT account rights to locally activate PowerShell in elevated mode via the Component Services applet in Control Panel without requiring the prompt.

Alternately, you can configure Windows so it never requires elevation for members of the local Administrators group.  Do that via the Local Security Policy applet.  Set the following settings:

Open the Security Settings\Local Policies\Security Options folder.
Set "User Account Control: Admin Approval Mode for the Built-in Administrator Account" to "Disabled"
Set "User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode" to "Elevate without Prompting"
Set "User Account Control: Run all administrators in Admin Approval Mode" to "Disabled"


How to store data in look-up tables when some of it may be semantically different, but logically the same?

Am I missing something or does this seem like the best way to achieve
  this?

Item 1

You seem to be missing the part of the contract with the laboratories that state "you will send us data in this format.".  That document should clearly list the valid values for TestReason (even if it is their list of values).

Design Idea 1

  receives patient appointment status information, specimen collection
  information and final laboratory test results.

I'm pretty sure there are ISO/Government standards for accomplishing this.  CDISC comes to mind.

I would look at various Government web sites to see if they have a standardized lists of Sample Types.  The Open Data StackExchange might be able to help you out (or even state that this does not exist).

Design Idea 2

Natural Keys.

In both your tables (Order.TestReasons and Results.TestReasons) should have a Unique Constraint on (LaboratoryId, Value).  These can be your PK.  You may want to investigate why they are two separate set of values.

Design Idea 3

You should already have a TestReasons dimension table (and a table for each of the other 12 dimensions).  That would narrow your requirement down to 26 tables.  Then, you just need to reduce the two tables into one.  (eg add a "Domain" column that indicates "Orders" vs "Results")


SSIS Package Data Flow Task RollBack on Error

I would design each package as a self-contained unit that can be rerun without any manual preparation or clean-up.  Relying on SQL transactions can dramatically slow execution and as you noted does not cover all the non-SQL Tasks.


Short answer:

Either option 1 or option 2:

In-line (one address per source record)
Child table with an FK linked to source record (allows >1 address per source)

Longer...

A separate address table will normally have an FK to some parent record.
Each address will maybe have a type (billing, delivery, etc)

Addresses are not shared or reusable or finite: that is, each address for a source entry has little in common with other entries.

different source records wouldn't have shared addresses (eg many-many in the middle between source and address)
separate address table with FK(s) can't have multiple parents either
having the FKs on source records to some address table would be unusual.

Finally, consider how you use addresses. You add or update them. You don't look for an existing address and link to that. Each address is per source only.


You can use a 3rd party tool like Red-Gate SQL Compare. It has a command-line interface that you can easily invoke using cmd or PowerShell (and you can do these from a SQL Server job, but you don't necessarily need to saddle SQL Server Agent with this task - you can just as easily do it using Windows scheduled tasks or a PowerShell job).

You can use your script to tell the tool to compare your source database with an intentionally empty database every night and either:

save the script somewhere, or
create a new database, apply the script, backup the new database and drop it.

(Depending on whether you want a script of the schema or an actual empty database that looks like your real database.)

That isn't the only tool to use, of course, but it's the one I have the most experience with. I know that it works and I know they invested in the command-line tool for exactly this purpose. You can check out some other potential offerings here:

Re-Blog : The cost of reinventing the wheel

You may also see if tools like Visual Studio SSDT (which replaces Visual Studio Team Edition for Database Professionals) does this - you may already have a workable solution in house. I haven't done much with SSDT yet so can't be sure its schema comparison features are scriptable.


This will be Yes if the server is configured as a failover cluster, this does not change based on being  a part of a WSFC or Always On AG.

Source:

  IsClustered:  Server instance is configured in a failover cluster.

1 = Clustered.

0 = Not Clustered.
"
NULL = Input is not valid, an error, or not applicable.

Query to check this:

SELECT SERVERPROPERTY ('IsClustered')


SQL Server renaming table from another database

You should use dynamic sql to do this. Build a string and execute it. As long as you know the name of the destination database, everything should be fine.

use CurrentDB
declare @x varchar(1000), @otherDb sysname = 'NewDB';

set @x = 'use ' + @otherDB + '

EXEC sp_rename ''dbo.temp'', ''temp2''
'
exec (@x);
print @x;
GO


using SUM on foreign view's keys grouped on multiple key/values?

You barely got it, once you have unnested the arrays you can simply aggregate the result by building.id, name and level in this way:

WITH ct AS
(
  SELECT
    buildings.id AS building_id
    ,buildings.name AS building_name
    ,jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS level_number
    ,((jsonb_array_elements(jsonb_array_elements(buildings.document->'levels')->'rooms')->'meta')->>'sqft')::numeric AS sqft
  FROM
    buildings
)
SELECT
    building_id, building_name, level_number,
    SUM(sqft) AS sqft
FROM
    ct
GROUP BY
    building_id, building_name, level_number;

building_id | building_name | level_number | sqft
----------: | :------------ | :----------- | ---:
          1 | my blding     | 0            | 1300
          1 | my blding     | 1            | 1800

db<>fiddle here


How to fill gaps with calendar table, count and group by

First, get your list of dates from the dimension. Just specify the start and end you need - let's say all of January, since you should have at least a gap at the end:

DECLARE @Start date = '20170101',
  @End date = DATEADD(MONTH,1,'20170101');

SELECT [Date] FROM dbo.DateDimension
  WHERE [Date] >= @Start AND [Date] < @End;

You should see 31 rows here. Now, use an outer join to pull in the rows you have aggregates for:

DECLARE @Start date = '20170101',
  @End date = DATEADD(MONTH,1,'20170101');

SELECT created = d.[Date], total = COALESCE(s.total, 0)
  FROM dbo.DateDimension AS d
  LEFT OUTER JOIN
  (
     SELECT [Date] = CONVERT(date, CreatedAt),
            total  = COUNT(*)
     FROM dbo.whatever
     WHERE CreatedAt >= @Start
       AND CreatedAt < @End
     GROUP BY CONVERT(date, CreatedAt)
  ) AS s ON d.[Date] = s.[Date]
  WHERE d.[Date] >= @Start
    AND d.[Date] < @End
  ORDER BY d.[Date];


INT is 4 bytes.  VARCHAR(12) is a little longer.  I would gladly use either for a PRIMARY KEY without worrying about performance.

A BTree is efficiently structured such that a longer key does not impact performance much.

If you switch that value to INT, perhaps with ZEROFILL, the speed up will be small.

If you add an INT AUTO_INCREMENT, then you are likely to slow down some uses of the table.  For more details, let's see some queries, including inserts and updates.


You can download and install the Oracle Database XE. It's free and has a default database that will be useful for your tests. You could search for an online SQL simulator, but it's not as useful as your own machine.

Good luck with school and stop skipping classes. At least database classes, skip others :-).


Solution for a readonly copy of a standalone MongoDB server

Can I choose which databases/collections are replicated ?

A replica set is a group of mongodb instances that maintain the same data set, so it is necessary for the entire set of data to be replicated between nodes.

  Is a 2-members (a primary and a secondary) replica set eligible ?

A replica set should have an odd number of voting members. If you want to maintain only two data-bearing nodes, you would want to include an arbiter in the replica set as a third voting member.

  Can I make our production application servers "always" read-write on the primary, and our data-analysis server "always" read on the secondary ?

All writes must be directed to the Primary node of the replica set; clients are not allowed to write to a data bearing node in the 'Secondary' replica set state.

Specifying a read preference is a good way of directing queries to different nodes of a replica set.


Redshift table not showing up in tables for schema?

PG_TABLE_DEF in Redshift only returns information about tables that are visible to the user, in other words, it will only show you the tables which are in the schema(s) which are defined in variable search_path. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schema(s).

Try this -

mydb=# set search_path="$user",db;

Then run your query -

mydb=# select tablename from pg_table_def where schemaname = 'db';


Database size not reduced after compressing tables

You can also shrink a database and shrink database files. See MS:

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

Erik


selecting next todo item from child table

Step by step. First look for not completed object_items, and the min(sequence). This is done with

-- Select object_items that are *not* completed, find min(sequence)
SELECT
    object_id, min(sequence) AS sequence
FROM
    object_items a
WHERE
    completed = 0
GROUP BY
    object_id
ORDER BY
    object_id, sequence ;

object_id | sequence
--------: | -------:
       10 |        4
       11 |        3

You can JOIN back with the original table to retrieve the rest of the corresponding row data:

SELECT
    item_name, q.object_id, q.sequence, completed, id
FROM
    (
    -- Select object_items that are *not* completed
    SELECT
        object_id, min(sequence) AS sequence
    FROM
        object_items a
    WHERE
        completed = 0
    GROUP BY
        object_id
    ) AS q
    JOIN object_items oi
         ON oi.object_id  = q.object_id
        AND oi.sequence   = q.sequence
ORDER BY
    q.object_id, q.sequence ;

item_name | object_id | sequence | completed | id
:-------- | --------: | -------: | --------: | -:
DEFG      |        10 |        4 |         0 |  4
CDEF      |        11 |        3 |         0 |  7

The query plan looks reasonable enough to scale well (it does not have a DEPENDENT SUBQUERY that needs reevaluation every time the DB loops):

id | select_type | table      | type   | possible_keys                           | key               | key_len | ref                    | rows | Extra
-: | :---------- | :--------- | :----- | :-------------------------------------- | :---------------- | :------ | :--------------------- | ---: | :-------------------------------------------
 1 | PRIMARY     | <derived2> | ALL    | null                                    | null              | null    | null                   |   10 | Using where; Using filesort
 1 | PRIMARY     | oi         | eq_ref | uni_seq_object_id,idx_object_id,idx_seq | uni_seq_object_id | 8       | q.sequence,q.object_id |    1 |
 2 | DERIVED     | a          | ALL    | null                                    | null              | null    | null                   |   10 | Using where; Using temporary; Using filesort

Adding the following index makes the plan even better, because it allows for covering the query named q:

CREATE INDEX idx_object_items_completed_object_id_sequence
    ON object_items (completed, object_id, sequence) ;

id | select_type | table      | type   | possible_keys                                 | key                                           | key_len | ref                    | rows | Extra
-: | :---------- | :--------- | :----- | :-------------------------------------------- | :-------------------------------------------- | :------ | :--------------------- | ---: | :--------------------------
 1 | PRIMARY     | <derived2> | ALL    | null                                          | null                                          | null    | null                   |    3 | Using where; Using filesort
 1 | PRIMARY     | oi         | eq_ref | uni_seq_object_id,idx_object_id,idx_seq       | uni_seq_object_id                             | 8       | q.sequence,q.object_id |    1 |
 2 | DERIVED     | a          | ref    | idx_object_items_completed_object_id_sequence | idx_object_items_completed_object_id_sequence | 1       | const                  |    3 | Using where; Using index

You can check everything at dbfiddle here


Does SQL Server 2016 Standard Edition support table partitioning?

Starting with SQL Server 2016 SP1 the Standard Edition supports all functionality most programming surface of the Enterprise Edition, as announced here: SQL Server 2016 Service Pack 1 (SP1) released !!!.

Quoted from that article:

  The following table compares the list of features which were only available in Enterprise edition which are now enabled in Standard, Web, Express, and LocalDB editions with SQL Server 2016 SP1. This consistent programmatically surface area allows developers and ISVs to develop and build applications leveraging the following features which can be deployed against any edition of SQL Server installed in the customer environment. The scale and high availability limits do not change, and remain as–is for lower editions as documented in this MSDN article.

What still differentiates EE from SE are primarily performance and administrative improvements.

So, in your case, SE will support table partitioning (functionality) but will not leverage certain performance enhancements available on partitioning, like 'partitioned table parallelism'.


How to make pg_basebackup use temporary replication slots

The slot to use for -X stream is specified with the --slot option. Its documentation says:

  If this option is not specified and the server supports temporary replication slots (version 10 and later), then a temporary replication slot is automatically used for WAL streaming.


What is the fastest way to compare polygon geometry data?

Building on the comments provided by @John M, I've contrived the following example of how to build a geometric index, and then query the index using the STEquals function:

USE tempdb;

DROP TABLE dbo.G1;
DROP TABLE dbo.G2;

CREATE TABLE dbo.G1
(
    G1ID INT NOT NULL CONSTRAINT PK_G1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Shape1 GEOMETRY
);

CREATE SPATIAL INDEX SIndx_G1_Shape1
ON dbo.G1(Shape1)
WITH (BOUNDING_BOX = (0, 0, 9999, 9999));

CREATE TABLE dbo.G2
(
    G2ID INT NOT NULL CONSTRAINT PK_G2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Shape2 GEOMETRY
);

CREATE SPATIAL INDEX SIndx_G2_Shape2
ON dbo.G2(Shape2)
WITH (BOUNDING_BOX = (0, 0, 9999, 9999));

DECLARE @c NVARCHAR(50);
SET @c = '';
DECLARE @LoopCount INT;
SET @LoopCount = 0;
WHILE @LoopCount < 5000
BEGIN

    SET @c = 'LINESTRING(2 ' + CONVERT(NVARCHAR(10), @LoopCount) + ', '
        + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, 4 '
        + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)';
    IF @LoopCount < 10 PRINT (@c);
    INSERT INTO dbo.G1 (Shape1)
    SELECT geometry::Parse(@c);

    SET @c = 'LINESTRING(' + CONVERT(NVARCHAR(10), @LoopCount) + ' '
        + CONVERT(NVARCHAR(10), @LoopCount) + ', '
        + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, '
        + CONVERT(NVARCHAR(10), @LoopCount + 2) + ' '
        + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)';
    IF @LoopCount < 10 PRINT (@c);
    INSERT INTO dbo.G2 (Shape2)
    SELECT geometry::Parse(@c);

    SET @LoopCount = @LoopCount + 1;
END

SELECT TOP(10) *
FROM dbo.G1;
SELECT TOP(10) *
FROM dbo.G2;

SELECT *
FROM dbo.G1
    INNER JOIN dbo.G2 ON G1.Shape1.STEquals(G2.Shape2) = 1;


How do I write a Postgres SQL command based on metadata of the tables themselves?

Postgres 9.5 introduced a feature to record commit timestamps. The documentation:

  track_commit_timestamp (bool)

  Record commit time of transactions. This parameter can only be set in
  postgresql.conf file or on the server command line. The default value
  is off.

And functions to work with it.

Once you have activated the setting and restarted the DB server, Postgres starts tracking commit timestamps. Then, for example, to get all rows from a table that were changed in any way during the last 4 hours:

SELECT * FROM tbl
WHERE  pg_xact_commit_timestamp(xmin) >= now() - interval '4 hours';

Related:

How list all tables with data changes in the last 24 hours?
Atomically set SERIAL value when committing transaction


Sqlserver IPV6 handling function or numeric conversion

Is there a way to create a custom type that would store a bigint of 128bits ? (Only need to perform Greater than or lesser than operations)

You might not need a custom type - numeric(38,0) might be suitable here (though I don't know the full range for valid 128-bit integers or whether IPv6 might be constrained to the range of values that would fit):

CREATE TABLE #ips
(
  iplow numeric(38,0),
  iphigh numeric(38,0),
  c varchar(64),
  d int,
  e nvarchar(128)
);

INSERT #ips VALUES
(58568835385568506466387976054061924352,58568835464796668980652313647605874687,
    '2c0f:f288::/32',328039,N'JSDAAV-ZA-Telecoms-AS'),
(58568842991472107835764385034281156608,58568842991473316761583999663455862783,
    '2c0f:f2e8::/48',37182 ,N'TERNET'),
(58568844892948008178108487279335964672,58568844892949217103928101908510670847,
    '2c0f:f300::/48',37153 ,N'Hetzner'),
(58568847428249208634567290272742375424,58568847507477371148831627866286325759,
    '2c0f:f320::/32',37126 ,N'BELL-TZ'),
(58568849329725108976911392517797183488,58568849408953271491175730111341133823,
    '2c0f:f338::/32',327983,N'Interworks-Wireless-Solutions');

DECLARE @ip numeric(38,0) = 58568842991472107835764385034281156617;

SELECT iplow,iphigh,c,d,e
  FROM #ips
  WHERE @ip BETWEEN iplow AND iphigh;

DROP TABLE #ips;

If this doesn't work for the addresses you need to support then you may need to separate out the network prefix (48 bits), subnet ID (16 bits), and interface ID (64 bits) into three separate numbers before passing them off to SQL Server. Your where clauses would become more complex but this might be a simpler approach than trying to represent an IPv6 as a single number anyway.

  Is there a way to properly handle IP address ranges in SqlServer ?

SQL Server has no built-in capability to understand what an IP address is.


How to pass a set or unnamed relation into a PostgreSQL function?

You could define a record type that represents one tuple of what you want to pass, then pass an array of that type:

create type path_info
(
  id  integer,
  source integer,
  target integer,
  cost float
);

Then pass an array to your function:

select *
from shortest_path((select array_agg( (gid,start_id,end_id,shape_leng)::path_info )
                    from network),
                   ...);

The function parameter needs to be defined as path_info[]:

create function shortest_path(p_path_info path_info[], ...)
  returns ...
as
...

Depending on what you are doing with those values, a function that receives a single record used with a lateral join might be more efficient:

Something like:

select *
from network n
  join lateral shortest_path( (n.gid, n.start_id, n.end_id, n.shape_leng)::path_info, ...) on ....


No, I think you are doing the Right ThingTM.

Performing a bigger query to get all the data you need in one round trip almost always beats fetching the data points one by one (“nested join implemented in the application”).

Your normalized database design is perfect for a transactional application that performs data modifications and small queries. If that is your workload, keep it like that. Such a layout avoids storing information redundantly, which is good for consistency and saves space.

If you have a database for reporting or analytical processing, the many large joins required by the typical queries will often be too inefficient In that case judicious de-normalization (using fewer tables with potentially redundant data) is often more efficient.


Am I using the ORDER BY clause incorrectly

Here is a sample in sqlfiddle-

The syntax for the select statment is the following

SELECT
    ...
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    ...

in the statement

SELECT * FROM table_name where id='2' order by 7 and '1';

7 and '1' must be interpreted as expression by the parser: 7 is the
integer 7 and not a row position in the select list. The manual says

  Logical AND. Evaluates to 1 if all operands are nonzero and not NULL,

so the expresion evaluates to 1


MySQL Cluster On Cloud vs MySQL Cluster On Dedicated Cluster

The biggest difference will come from networking - Mikael's tests were run over Infiniband whereas most hosting or cloud providers will not give you that type of dedicated bandwidth.  As a distributed database, MySQL Cluster needs high network bandwidth to support message passing between the nodes, so the higher the network latency, the lower the read or write performance, though this can be offset by increasing concurrent load to the database

If you want to know more about the benchmark itself and how to run it on your own cloud vendors environment, check out this blog:
https://blogs.oracle.com/MySQL/entry/performance_testing_of_mysql_cluster


Customize psqlrc to be different based on host or username

You can use \gset (introduced in 9.3) to query information about the server and save it in a psql variable, then set the prompt to include that variable.

select case when inet_server_addr()='10.0.0.34' then 'PROD' else 'TEST' end as host \gset
\set PROMPT1 '%:host: %/%x%x%x%R%# '

I don't know how you get this introduce color, rather than just text, in my hands your color control sequences are just presented literally.

If you want different .psqlrc files, you have to set it as an environment variable as their is no command line option.  So something like:

alias psql_prod='PSQLRC=~/.psqlrc_prod psql'


I think I nailed this thing! Took me a lot of work but I think I could put this together and here it is what I did on my system:

C: SYSTEM = where SQL is installed
D: TEMP DATA = Temp DB directory
E: TEMP LOGS = Temp DB for logs
F: SERVICES DATA = Database Engine root INSTALLSQLDATADIR=F:\ServicesData
G: SERVICES LOGS = MSSQLServer Service startup to -G:\ServiceLogs\Logs\ERRORLOG
H: CONTENT DATA = User database  (*.mdf)
I: CONTENT LOGS = User database logs (*.ldf)
J: BACKUP DATA = The SQL backups


I don't believe you can apply a template after a trace file is saved, however you can work around this by exporting the results to a table.

If you open the trace file in SQL Profiler and the navigate to File > Save As > Trace Table, you can output the result set to a table in an existing or new database and then you can query/filter the result set to your hearts content.


does sequence of columns in where condition matters the sequence of composite key? in Oracle Database

No, it will not matter. Make sure:

Data types are same in both tables
All columns are indexed  Index
Index statistics are not stale.

SELECT stale_stats FROM user_ind_statistics
WHERE index_name = upper('&index_name');