How to check there is enough disk space before overwriting a DB with restore command

but I can't find any examples online of how to use verifyonly with a
  destination to test. How can I do this?

You can use this syntax:

restore verifyonly
from disk = 'V:\SQL_backup\myBak.bak'
with move 'myDb'     to 'C:\myDb.mdf', -- put here your destination
     move 'myDb_log' to 'C:\myDb_log.ldf' -- put here your destination

In case of insufficient space you'll get this error:

  Attempting to restore this backup may encounter storage space
  problems. Subsequent messages will provide details. There is
  insufficient free space on disk volume 'C:\' to create the database.
  The database requires 500254375936 additional free bytes, while only
  57038258176 bytes are available.

This is not an optimal way to check for disk space because this will not only check for a space available and you will wait the whole database backup check to finish.

You can use your own code like this, for example, here I use xp_fixeddrives to check free space:

declare @t table (drive varchar(10), Mb_free bigint);
declare @Mb_free bigint;

insert @t exec xp_fixeddrives;

select @Mb_free = Mb_free
from @t
where drive = 'C';

if @Mb_free < ...


How to find what tables are stored in what data files

You can find directly which rowset (partition of an index/heap) belongs to which filegroup: look at data_space_id in sys.allocation_units. But is not going to give you details at the individual file-within-filegroup level. It is possible to find exactly how each rowset page lays out in each file by chasing the IAM chains manually, or by using undocumented features like DBCC IND or %%physloc%%. Not trivial, but possible.
But I would encourage you to go down a different path. Specially in the light of your comment about correlating space used in the file with IO, since the largest file by size does not necessarily imply most IO in the file (think data at rest). Instead, use sys.dm_db_index_usage_stats and, especially, sys.dm_db_index_operational_stats instead. You will get the load on each rowset, and then you can correlate this with the actual physical IO.


How we can recover MySQL database (Innodb,myisam)

First of all don't use this version. The recovery toolkit migrated to LaunchPad and since then version 0.4 and 0.5 were released. But even 0.5 is outdated because I never had a chance to release a lot of useful features and bugfixes which are still in the trunk. The current home of Percona Data recovery toolkit is https://launchpad.net/percona-data-recovery-tool-for-innodb

After I quit Percona I forked the data recovery toolkit and made undrop-for-innodb ( https://github.com/twindb/undrop-for-innodb ) which includes Percona's features + performance optimizations and even more bugfixes.

But it still works on Linux only.

To use it on Windows there are two options:

Create a virtual machine and run it on Linux. InnoDB files format is same on Linux and Windows (and any other OS InnoDB supports) so the tool will perfectly work with InnoDB files from Windows environment.
Use online service https://recovery.twindb.com/ . It's a frontend to the toolkit and of course you can use it on Windows. However not all possible scenarios are implemented so depending on your case you may be left with option #1 only.


SQL Server recovery model cannot be changed

To change the recovery model requires the ALTER permissions on the database. You can revoke or deny that permission to a user and it will prevent them from altering the recovery model.
Note that denying permissions overwrites any grants, ensure you test any permissions changes before applying to a production server!

Required permissions
Requires ALTER permission on the database.

Ref: https://msdn.microsoft.com/en-au/library/ms189272(v=sql.120).aspx


How to reset statistics after UPDATE STATISTICS ... WITH ROWCOUNT

Use DBCC UPDATEUSAGE with the COUNT_ROWS option.

DBCC UPDATEUSAGE
(   { database_name | database_id | 0 }
    [ , { table_name | table_id | view_name | view_id }
    [ , { index_name | index_id } ] ]
) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ]

Documentation


A network-related or instance-specific error occurred

Under configuration manager I set the server browser to start (right click and start).

The following page/Answer gives more detail: https://stackoverflow.com/questions/6987709/unable-to-connect-to-sql-server-instance-remotely


Error: 9245, Severity: 16, State: 1. / During the last time interval XXX query notification errors were suppressed

The error 9245 message appears to be directly connected to the 'The query notification dialog on conversation handle...' message in that SQL Server 2012 SP1 appears to only show that error a maximum of 10 times (with the same timestamp) in the error log, the rest are aggregated into a single error message:

  During the last time interval 257 query notification errors were suppressed.

Which is preceded by the message:

  Error: 9245, Severity: 16, State: 1.

Prior to SQL 2012 I do not believe the error notifications were aggregated at all, and did not result in a severity 16 error.


If this can be helpful to someone, as a workaround I've created a view instead of using CTE.

CREATE VIEW foo_view AS
  SELECT col1
  FROM spectrum.bar
with no schema binding;

SELECT * FROM foo_view UNION ALL
SELECT * FROM foo_view;


Any better way to rewrite the following "create table"?

Even on MySQL, you don't need to specify DEFAULT null. That's always the default, well usually always because MySQL is a special monster.

Use PostgreSQL.
UID should be a number, not varchar.
Class_A is pretty silly, even in an enum. If your classes are all one letter, just make them a char(1) or the like and just add "type" to the column name.
Class_A_Speed sounds like a function over a real speed, rather than a category. Just add the function to the database, class_from_speed(speed)

Like this,

CREATE TABLE employee (
  employee_id   int       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name          text,
  class_type    "char",   -- or char(1)
  speed         int
);


High disk IO with MySQL master-master replication

ALTERs being replicated?  They could be low replication bandwidth but high I/O.
How much RAM?  innodb_buffer_pool_size is quite large.
Do your tables have lots of secondary indexes?
Was there previously a lot of activity that led to updating secondary, non-UNIQUE, indexes?  The Change Buffer is in the buffer_pool and it is flushed asynchronously.  See innodb_change_buffer_max_size, but don't necessarily change it.
Lots of TRIGGERs?
How big are the tables?
In spite of ROW, might a full-table UPDATE have gone through as STATEMENT?
LVM's COW may double (or triple?) the I/O activity.  (But this would only partially explain what you are seeing.)
Are some big SELECTs happening?  (Turn on the slowlog, with a small value for long_query_time.)

No, I don't see any configuration changes to make.

More

Some issues that may be relevant (especially after updates to the Question)

For a single UPDATE of 1K rows,  Row Based Replication is rather verbose.  Each row is a separate record in the replication stream, and it is rather large (compared to the update, itself).  These records will be buffered and written to the Master's disk (binlog) and to the Slave's disk (relay log).  I would assume that they are buffered, etc, so that the amount of writing is the same.  But maybe I am wrong.
The "sql thread" on the Slave reads all of these 1-row updates, presumably one at a time.  Again, one would hope that there is buffering such that this does not add to the I/O.
Both machines need to update any secondary keys via the Change buffer.  However, this is a delayed action.  In fact, it might be delayed so long that it won't show up on the graphs you have presented.
In a Master-Master setup, I do not know when the "loop" is stopped.  That is, server_id 123 replicates to its Slave, server_id 234, which then replicates to all of its Slave, including 123.  Upon discovering 123=123, the replication packets are dropped.  But does that happen before getting back to 123 and being written to the relaylogs there?  I don't know.  You can figure it out by looking at the growth of the relaylogs on the active Master.
What other logs does the Slave have turned on?  Presumably the same as the Master.  General log?  Slow log?  Others?

Scenario details

SET autocommit=0;
DELETE all of 1-2K rows in a particular table. If all at once, and Row-based-replication, then a lot is sent through the replication stream.
INSERT one row back in
INSERT one row back in
... 1-2K rows inserted
COMMIT;  It is not until this point that anything goes across to the Slave.  I suspect this is when the binlog is being written all at once.

Well, I guess this still does not answer your question, but maybe it provides some more insight.

How to replace a table

This is a more efficient way to replace all of a table.  It avoids the table ever being out of service:

CREATE TABLE new LIKE real;
load the new data into the table (LOAD DATA or multi-row INSERT(s) is best)
RENAME TABLE real TO old, new TO real;
DROP TABLE old;


Slow Query Gets Even Slower After Indexing

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)
ORDER BY
    from_field DESC
LIMIT 1 ;

or

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)
ORDER BY
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table
where
  (from_field <= 1533177608)
and
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).

The above queries will still be not very efficient when there is no match at all, as a large part of the index will have to be scanned.

If you are 100% sure that there are no overlaps of the intervals, you can use this version, which will only need an index on (from_field) and do an index seek only:

SELECT * FROM my_table
WHERE from_field =
  ( select MAX(from_field) from my_table
    where
      (from_field <= 1533177608)
  )
  AND
    (to_field   >= 1533177608) ;


Creating UUID extension as the non super privileged user

I see two possibilities:

1.

Install the extension into a template database (just a regular database) and use it as template for CREATE DATABASE:

CREATE DATABASE new_db TEMPLATE my_template_db;

That copies all installed extensions into the new database. Your user obviously can do that.

Also, if she created the database she should have the necessary privileges to run CREATE EXTENSION anyway. I quote the manual:

  For most extensions this means superuser or database owner privileges are needed.

2.

Create a SECURITY DEFINER function executing all necessary DML statements, owned by a superuser. Revoke all privileges from public and grant EXECUTE to the user role:

CREATE FUNCTION foo() ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION foo() FROM public;
GRANT EXECUTE ON FUNCTION foo() to my_user;

Read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.


find if the column of the PostgreSQL table is empty with minimum cost

As far as I know (but not sure), if there are no data at all for the column, there is some property in the database saying that the chunk or the table has no data for this column, at least this happens when adding a new column to the existing table, so the database doesn't update all existing rows if the default value of a new column is NULL

If the row data ends early, it assumes all column after that for that particular row are NULL, or in newer versions, are whatever DEFAULT was at the time the column was added.  But this says nothing at all about what might be in other rows.
You can't avoid scanning the table unless you a had a very strange constraint which forces the values to all be NULL.  But you should be able to scan the table just once, not once for every column.
select count(col1), count(co2), count(col3)...count(col100) from the_table;


Yes, that is true (and a bit surprising).

You need to add quite a lot of bigint values to exceed numeric(1000,0), so I think you should be safe with that.


I don't have data on what links your tables so you might have to do a very little change to the ON clauses, but here it is:

Creating tables with data

CREATE TABLE #sales (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #sales VALUES (1,2012,9,5,3,4);
INSERT INTO #sales VALUES (2,2013,7,2,6,3);
INSERT INTO #sales VALUES (3,2014,6,5,6,1);
GO
CREATE TABLE #quantity (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #quantity VALUES (1,2012,14,54,13,49);
INSERT INTO #quantity VALUES (2,2013,8,12,17,31);
INSERT INTO #quantity VALUES (3,2014,16,25,25,3);
GO
CREATE TABLE #orders (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #orders VALUES (1,2012,2,5,3,9);
INSERT INTO #orders VALUES (2,2013,1,2,7,1);
INSERT INTO #orders VALUES (3,2014,1,5,5,2);
GO

And the query:

SELECT PK, CAST(Year AS CHAR(4)) + '_' + Year_Week, Sales, Quantity, Orders
FROM
    (SELECT s.PK, s.Year
        , s.Wk1, s.Wk2, s.Wk3, s.Wk4
        , q.Wk1 AS qWk1, q.Wk2 AS qWk2, q.Wk3 AS qWk3, q.Wk4 AS qWk4
        , o.Wk1 AS oWk1, o.Wk2 AS oWk2, o.Wk3 AS oWk3, o.Wk4 AS oWk4
    FROM #sales s
    INNER JOIN #quantity q ON s.PK = q.PK AND s.YEAR = q.YEAR
    INNER JOIN #orders o ON s.PK = o.PK AND s.YEAR = o.YEAR) p
UNPIVOT
    (Sales FOR Year_Week IN
        (Wk1, Wk2, Wk3, Wk4)
    )AS unpvt_s
UNPIVOT
    (Quantity FOR Year_Week_q IN
        (qWk1, qWk2, qWk3, qWk4)
    )AS unpvt_q
UNPIVOT
    (Orders FOR Year_Week_o IN
        (oWk1, oWk2, oWk3, oWk4)
    )AS unpvt_o
WHERE 'q'+Year_Week = Year_Week_q
    AND 'o'+Year_Week = Year_Week_o;
GO


Why database has char data type when varchar is more efficient?

You should read this article SQL varchar data type deep dive, and specifically the Storage and performance considerations using SQL varchar section:

Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length. So in cases where disk size is not an issue, it is recommended to use CHAR.

Note this specifically speaks to the differences from a Microsoft SQL Server perspective, YMMV for other database systems, and not all support both CHAR and VARCHAR data types (e.g. SQLite has neither data type and only supports what it calls the TEXT data type).
And here's some good information, in similar vein, from MySQL's perspective, courtesy of RolandoMySQLDBA - DBA.StackExchange answer to What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?:

Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields.

Surprisingly, in the domain of PostgreSQL, it sounds like the same performance differences don't exist, and therefore the practical differences between the data types are less abundant. Please see this StackOverflow answer to Difference between text and varchar (character varying) and this DBA.StackExchange answer to Index performance for CHAR vs VARCHAR (Postgres):

...the performance of inserts and selects for all 4 data types are similar.

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types.


understanding (and modelling) grocery shopping lists

Your Order should be Primary Key which you can link to another table where order details will be given.

Products {
ProductID,
Name,
Price
}

Customers {
CustomerID,
FirstName,
SecondName
}

Customer_Orders {
OrderID (Primary Key)
CustomerID
...
}

Order_details {
OrderID
ProductID
Quantity
}


(Moved here from an edit on the question: Reddit helped me out with this already. Eventually I used this code)

fiveminaverage <- dbGetQuery(db,"
SELECT DATE, AVG(PRICE), MIN(TIME) AS firstSample, MAX(TIME) AS lastSample
FROM currentdata
WHERE DATE=19990603
GROUP BY DATE, strftime('%s', (CASE WHEN TIME LIKE '_:__:__' THEN '0' || TIME ELSE TIME END)) / (5 * 60);
")


In my biased opinion (I'm author of TwinDB) the best way to take backups from MySQL is to use TwinDB. Not only it will save from blunders like rsyncing database files (which with 100% guarantee leads to corrupted tablespace) but also lets you take incremental copies correctly.


get all posts with their associative tags without duplicates

You didn't provide DDL and sample data so we can't test anything.
Also, it is not clear what is the join predicate, but I think you are looking for GROUP_CONCAT, something along the lines of (Pseudo code):

SELECT P.Title, GROUP_CONCAT(T.Tag SEPARATOR ',') AS Concatenated_Tags
FROM Post P INNER JOIN Tag T ...
GROUP BY P.Title;

HTH


You want to group by again the result of the union. For that you need to enclose the union in parentheses and add an alias ("name" it), i.e. make it a derived table:

select datum, sum(resColA) as resColA, sum(resColB) as resColB
from
(   select datum, sum(colA) as resColA, 0 as resColB
    from TableA
    group by datum
  union
    select datum, 0 as resColA, sum(colB) as resColB
    from TableB
    group by datum
) as drv              -- an alias for the derived table
group by datum
order by datum ;

or make it a CTE (common table expression):

with
  cte as
    (   select datum, sum(colA) as resColA, 0 as resColB
        from TableA
        group by datum
      union
        select datum, 0 as resColA, sum(colB) as resColB
        from TableB
        group by datum
    )
select datum, sum(resColA) as resColA, sum(resColB) as resColB
from cte
group by datum
order by datum ;

There is practically little difference between the above 2 options and I think no difference in efficiency in DB2. Pick whatever feels more readable to you. Alternatively, you could first union the data from the 2 tables and then group by. This will probably result in different execution plans, so test which is more efficient:

select datum, sum(resColA) as resColA, sum(resColB) as resColB
from
(   select datum, colA as resColA, 0 as resColB
    from TableA
  union
    select datum, 0 as resColA, colB as resColB
    from TableB
) as drv
group by datum
order by datum ;


The best way to maintain a size limit for a table (or set of tables or a database) is to create a separate file system with the desired size maximum, define a tablespace on that file system and put the tables into that tablespace.

It is OK to limit size for user tables in that way; exceeding the limit will cause an error, but processing will continue. But you should make sure that PostgreSQL does not run out of space for system tables or (most important) WAL. That's why a separate tablespace is the right solution.

If you are using large objects, you have to take care that the large objects are removed when you delete a table row. That does not happen automatically.

VACUUM (FULL) rewrites a table, so you must have room for both the old and the new table until the operation is finished.

Your “baffling experience” is easily explained like this:

In your first experiment, there was not enough space to hold a new copy of the table with the rows that were not deleted.
In your second experiment, the new table was smaller, so the space was sufficient.


What you see is completely normal and expected.

  Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of
  values which appears in the column, even if no rows are ever deleted.
  A value allocated from the sequence is still "used up" even if a row
  containing that value is never successfully inserted into the table
  column. This may happen, for example, if the inserting transaction
  rolls back.

Unless you really need a gapless series of values, you don't have to worry about it.  This is how most (well over 99%, I guess) database tables work.

If you need it, be careful if you have concurrent inserts on the table - you will need some locking to exclude the possibility of choosing the same value by two or more sessions running in parallel.


query doesnt use column dedicated index after a certain amount of rows

In this plan:

Seq Scan on foo mp  (cost=0.00..42.56 rows=1332 width=20) (actual time=0.012..0.435 rows=1332 loops=1)
  Filter: (asset_id = 1)
  Rows Removed by Filter: 873
  Buffers: shared hit=15
Planning Time: 0.074 ms
Execution Time: 0.707 ms

You can see that roughly 60% (1332 of 2205) of the rows from that table are returned. Doing that with a Seq Scan is much more efficient that doing an index lookup for each of those rows.

In the first query Postgres only expected 8 rows to be returned (in reality no row was returned) and for that it is more efficient to probe the index.

See this related answer


Postgressql text search: pg_trgm index with order by going very slowly

The core performance problem is the selectivity estimation for the predicate path ilike '%cat%', which is off by a factor of 130:

  (cost=0.00..809.15 rows=1753 width=0) (actual time=2108.286..2108
  .286 rows=223590 loops=1)

Probably leading to a sub-optimal query plan.

In this particular case, Postgres identifies a quarter million rows, just to filter the top 15. A giant waste of time. Depending on missing information, some other query plan (some other query) will be much more efficient. Here is a related question with some generic techniques in the answers that apply in similar fashion to your case:

Can spatial index help a "range - order by - limit" query

Selectivity estimation for pattern matching is hard, but there have been major improvements in Postgres 9.6. Detailed explanation in this closely related answer:

Trigram index for for ILIKE patterns not working as expected

Your best course of action may be to install a current version of Postgres.

Of course, there may be all kinds of additional problems, your question does not reveal details.

Or you may be using the wrong tool altogether, you did not define what you are trying to search exactly.

Concerning:

  It looks like I need a compound index with the gin, but it won't let me build one based on peers desc nulls last ...

Again: essential details like your CREATE INDEX command and the resulting error msg are missing. My educated guess: You need to install the additional module btree_gin first. You probably know that by now, since you just commented on this related answer explaining as much:

Best index for similarity function

But don't bother, it probably won't help with your query.


We can easily test this.. but first, stop using date_part. Instead, use extract. It's the same thing with slightly different syntax, but it's standardized.

Testing it

Let's create a table with all the second TIMESTAMPS for the year.

CREATE TEMPORARY TABLE foo AS (
  SELECT to_timestamp(x) AS ts
  FROM generate_series(
    extract(EPOCH FROM '2016-01-01'::timestamp)::int,
    extract(EPOCH FROM '2017-01-01'::timestamp)::int
  ) AS t(x)
);

Then we can implement your check in a WHERE condition to get a basic idea of speed. BTW, there are a ton more seconds in a year than I first though. There are 31,622,401 seconds in a year. It takes 1093 MB to load this sample data.

took, 12.6, 10.8, 11.0 seconds.

SELECT count(*) FROM foo
WHERE extract(YEAR FROM ts) = 2016;

took, 6.1, 6.3, 6.2 seconds

SELECT * FROM foo
WHERE ts BETWEEN '2016-01-01 0:00:00' AND '2016-12-31 23:59:59.999';

Conclusion

Yes, the second is roughly 2x as fast in three trial runs. For the purposes of creating a check constraint, you should use the latter.

Addendum

PostgreSQL has the ability to add functional indexes, however an index on date_part() is not possible

CREATE INDEX onyear ON foo (date_part('year',ts));
ERROR:  functions in index expression must be marked IMMUTABLE

I assume this is because the result of datetime functions comes back in the client's timezone. Either way, this makes it even more advantageous to use the timestamp ranges. One easy to get more speed if need be would be with materialized views though..

CREATE MATERIALIZED VIEW matviewfoo AS
SELECT
  extract(YEAR FROM ts)::smallint AS year,
  ts
FROM foo;
CREATE INDEX yearidx ON matviewfoo (year);
VACUUM FULL matviewfoo;

Now you can

SELECT count(*) FROM matviewfoo WHERE year = 2016;

And, you're down to 4.1 seconds


Can I drop and recreate a system extended stored procedure?

No, you can't drop and re-create xp_cmdshell. It's not even its own procedure, but part of a library in xpstar.dll (or at least it was at one point). And I am extremely skeptical that, even if you could re-create this procedure, that would fix the problem you're having.

Maybe instead of banging your head against this problem with xp_cmdshell, you should just bite the bullet and implement the functionality you need in CLR. Or perform the tasks you need outside of SQL Server altogether.


How to restore a SQL Server database along with a standalone application?

Good deal with getting the connection string out.  Based on that and the error it looks like you'll need to change some things around with your SQL config.

First, SQL 2012 does not support the database compatibility level for SQL Server 7.  You'll need to go back down to at least SQL Server 2005.

Based on that connection string info the app is for a SQL server named SCFRANGOS.  You can try to create a host entry for SCFRANGOS on the machine you're running the app from and give it the IP of your SQL server, or create it in DNS.

The database name you need to use FVBraganca.  Since you have both the mdf (data) and ldf (log) you can try to reattach the database to your SQL Server.

You also need to create a local user on the server and name it usuario.  Then you need to create a Windows login on the SQL server for usario and that login needs to be added to the FVBraganca dabase as a user.

Alternatively, if you're comfortable with editing the binary you could edit your own connection information into the exe.

Steps:

Install SQL Server 2005 or earlier on your server.  You can download SQL Server 2005 Express from here.  Again, SQL 2005 is the newest version you can use that will support the SQL 7 database format.
Make sure the server where SQL Server is installed can be resolved as SCFRANGOS on your network from wherever you are running the application.  You can add a host entry or an alias in DNS.  Or you can add an entry in the hosts file where you will run the application (c:\windows\system32\drives\etc\hosts typically).  When this step is complete you should be able to receive replies when doing a ping SCFRANGOS from the machine where you want to run the application.
Attach the database and name it FVBraganca.  Connect to SQL Server and run this (assuming your mdf and ldf files are in c:\myapp and are called myapp.mdf and myapp_log.ldf):

CREATE DATABASE FVBraganca
ON (FILENAME='c:\myapp\myapp.mdf'),
    (FILENAME='c:\myapp\myapp_log.ldf'),
FOR ATTACH;
GO
Create a user called usario on the server where SQL Server is installed.  Instructions are found here.
Create a login in SQL Server for the user created above

CREATE LOGIN [myserver\usario] FROM WINDOWS
GO
Add the new login as a user of the database.  If the user already exists you'll need to remove it first so you don't have a mismatch of the security identifiers.  The existing user will have the security identifier tied to it from the original SQL Server where the database was last used.

USE FVBraganca
GO
DROP USER usario
GO
CREATE USER usario FOR LOGIN usario
GO
Since you don't know what the user needs access to yet you might just want to grant it dbo access to the database.  This is typically not a good idea but in this case it'll be easier until you know for sure what objects in the database the application uses.

sp_addrolemember @rolename='db_owner', @membername='usario'

That should be it.  As long as the database attaches cleanly I think this should get you going.


Is there a way to prevent the deadlock while maintaining the same queries?

The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case):

As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in different orders. The SELECT query needs to access the index before the table due to the RID lookup, whereas the UPDATE query modifies the table first, then the index.

Eliminating the deadlock requires removing one of the deadlock ingredients. The following are the main options:

Avoid the RID lookup by making the nonclustered index covering. This is probably not practical in your case because the SELECT query returns 26 columns.
Avoid the RID lookup by creating a clustered index. This would involve creating a clustered index on the column Proposal. This is worth considering, though it appears this column is of type uniqueidentifier, which may or may not be a good choice for a clustered index, depending on broader issues.
Avoid taking shared locks when reading by enabling the READ_COMMITTED_SNAPSHOT or SNAPSHOT database options. This would require careful testing, especially with respect to any designed-in blocking behaviours. Trigger code would also require testing to ensure the logic performs correctly.
Avoid taking shared locks when reading by using the READ UNCOMMITTED isolation level for the SELECT query. All the usual caveats apply.
Avoid concurrent execution of the two queries in question by using an exclusive application lock (see sp_getapplock).
Use table lock hints to avoid concurrency. This is a bigger hammer than option 5, as it may affect other queries, not just the two identified in the question.

  Can I somehow take an X-Lock on the index in the update transaction before the update to ensure the table and index access are in the same order

You can try this, by wrapping the update in an explicit transaction and performing a SELECT with an XLOCK hint on the nonclustered index value before the update. This relies on you knowing for certain what the current value in the nonclustered index is, getting the execution plan right, and correctly anticipating all the side-effects of taking this extra lock. It also relies on the locking engine not being smart enough to avoid taking the lock if it is judged to be redundant.

In short, while this is feasible in principle, I do not recommend it. It is too easy to miss something, or to outsmart oneself in creative ways. If you really must avoid these deadlocks (rather than just detecting them and retrying), I would encourage you to look instead at the more general solutions listed above.


Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute

Your query plan shows two table scans on leads, which is not surprising given the search predicates with OR: FROM leads WHERE to_id = lead_id OR from_id = lead_id.

It also shows a spilled sort, suggesting that the sort buffer is too small.

Consider rewriting your query with joins instead of subselects, something like:

SELECT
  COALESCE(leads_to.lead_id, leads_from.lead_id) as lead_id,
  COALESCE(CONCAT(leads_to.first_name, " ", leads_to.last_name), CONCAT(leads_from.first_name, " ", leads_from.last_name)) as lead_name
FROM
  text_messages t
LEFT JOIN leads leads_to ON to_id = leads_to.lead_id
LEFT JOIN leads leads_from ON to_id = leads_from.lead_id
WHERE sent_to_number = "xxx" OR sent_from_number = "xxx"
  AND organization = 1 GROUP BY lead_id ORDER BY sent_at DESC

To address the sort spill try increasing sort_buffer_size.


Multiple Publications for Merge Replication?

This is done in Merge Replication by using Parameterized Row Filters.  They allow for different partitions of data to go to different Subscribers without requiring multiple publications.


Columnstore Index in read_only filegroup prevents CheckDB

The problem occurs when DBCC tries to verify a deleted bitmap for a read-only columnstore table.

Deleted bitmaps are stored on the same filegroup as the columnstore table. They track rows logically deleted from compressed row groups.

As far as I can tell, everything is organized correctly in the internal system tables (on SQL Server 2017 CU3), and most of the DBCC code correctly accounts for the hidden rowsets that hold the columnstore deleted bitmaps.

For some reason, a check for offline or read-only file groups results in an unhandled exception:

Msg 8921, Level 16, State 1, Line 69
Check terminated. A failure was detected while collecting facts.
Possibly tempdb out of space or a system table is inconsistent.
Check previous errors.

The same offline/read-only check is performed several times earlier in DBCC processing (when facts are being collected) without issue.

The problem occurs when DBCC CHECKDB or DBCC FILEGROUP is run (on any filegroup), or DBCC CHECKTABLE is asked to check a specific read-only columnstore table. None of these should produce a fatal error condition that prevents the rest of the DBCC checks running, so this must be a bug.

  Or am I precluded from integrity checks in this scenario?

As a workaround, run DBCC CHECKFILEGROUP on the columnstore filegroup immediately before it is made read-only (or run DBCC CHECKDB at that time) then:

DBCC CHECKALLOC on the database
Run DBCC CHECKCATALOG
Run DBCC CHECKTABLE for each table (excluding columnstore tables on a read-only filegroup)
You may also want to run DBCC CHECKCONSTRAINTS.

See Consistency Checking Options for a VLDB by Paul Randal and the Q & A Dividing DBCC CHECKDB over multiple days.


How and what do you backup for a simple Oracle DB?

Use RMAN to back up to the network share. An export is NOT a true backup as you cannot perform a point-in-time recovery with it. XE comes with backup scripts that you can just schedule with AT or similar, the documentation says:

  To schedule automatic backups, use any operating system or third party
  task scheduling software to run the supplied backup script for your
  platform

If you are running the database in archivelog mode, then the archived redo logs are your "continuous export", so you would ideally want to have those on seperate disks from the DBFs, and hopefully mirrored. RMAN will back those up too. As you say you have a single disk, well, this is a judgement call you will need to make - I would say if the data is valuable enough to back up, it is valuable enough to run on server-grade hardware.


Programmatically finding minimum set of fields necessary to create a unique composite key

While comments and srutzky offer some great advice, there is a tool made exactly for your situation. The SSIS Data Profiling Task is intended for the purpose of identifying potential Primary Keys (for multiple columns) along with providing a lot of other useful insights into your data.

Simply create a new SSIS package, add the task then use the wizard like interface to profile your data. Create a new output file somewhere you can access it, select Quick Profile... then profile the appropriate information from the database and table desired.

Once you're done run the package and return to the component to select Open Profile Viwer... and look at all the exciting data! The tool gave me a 96% match for one of my fact tables for a three column PK when up to 7 columns were requested for the candidate key consideration (not shown).

Just to be clear I definitely agree that the business rules should determine data uniqueness... just because you find a combination of columns that fits over your data for uniqueness doesn't necessarily mean it makes any sense. =)


Some discipline is needed.  A location_id must always refer to a particular location, even if the name of the building changes or you make a correction to the address.

The UI must make it obvious whether to the user as to whether he is editing a "location" or creating a "new" location.

When providing the "pull down" in the UI, the id is hidden and uniquely identifies it to the rest of the data; the name, address, etc is useful for humans to know which 'location' it is.

The UI needs a straightforward way to change an even from one location to another -- in case the original association to an event was erroneous or in the case that the even is moved to a different venue.

Nothing if foolproof.

You need scripts that check for double-booking.  They should provide sufficient info to help the admin figure out whether it was a typo, a confusion of two similar location names, etc.


De-duplicate rows in place vs de-duplicate on transfer

Is there anything wrong with overall approach of using staging
  databases to store "raw data", and transferring batches of that stage
  into a target, eliminating duplicates within stage and between
  stage+target during the transfer.

This is a standard and typically the best approach. Staging should be a fast, logicless "ETL" step. Get into your OLTP source systems, get your data, and get out! The only reason to spend time doing logic on the source query is if it will result in a total reduced load on the OLTP. e.g. Incremental logic on a 10million row table may result in a faster query and shorter locks than if you try and do a dumb load of 10million rows every execution.

If you don't have resource contention issues (like a flat file) you can still maintain this approach, such that all of your Transform operations occur in a DB and ETL tool for efficiency, maintenance, and consolidation of ETL logic. Nothing like tricking another dev by leaving a little key piece of logic in the staging steps while the majority of data transformation resides elsewhere.

  If the general approach is sound, are the above queries OK? Is
  "EXISTS" a safe clause for scenario 1? Is the ROW_NUMBER PARTITION BY
  and JOIN BY the some columns a fast way to avoid transferring
  duplicates in scenario 2?

I personally love Window Functions as they are performant with clean, concise code. EXCEPT is also, typically, a very efficient operator and you are likely safe using it. There's nothing immediately wrong with your approach, so I'd save optimization until it actually becomes an issue. Since you used the word "safe" just double check the rules and how they apply to your data - you may get unexpected results if you don't account for how EXCEPT operates.


C++ stoi equivalent in SQL Server's T-SQL

DECLARE @Test VARCHAR(32)

SET @Test = '99ABC 123ABCD ABC DEFGH'

DECLARE @stoi INT = TRY_CONVERT(INT, LEFT(LTRIM(@Test), PATINDEX('%[^0123456789]%', LTRIM(@Test + ';')) - 1))

SELECT @stoi

  99


You set a savepoint and after the comand a Rollback

CREATE tABLE tablename (blah varchar(3), thing varchar(3))

BEGIN;
SAVEPOINT my_savepoint;
   UPDATE tablename
   SET blah = 'xxx'
   WHERE thing= '123';
ROLLBACK TO my_savepoint;
COMMIT;

db<>fiddle here


What administration utilities does Ubuntu and Debian package with PostgreSQL?

Here is the Ubuntu-specific management utilities,

pg_archivecleanup
pg_basebackup
pg_config
pg_conftool
pg_createcluster
pg_ctlcluster
pg_dropcluster
pg_dump
pg_dumpall
pg_isready
pg_lsclusters
pg_receivexlog
pg_recvlogical
pg_renamecluster
pg_restore
pg_upgradecluster
pg_virtualenv

There are some other subtle differences with the variants provided upstream. Because Ubuntu is capable of running multiple PostgreSQL's of different versions, it distributes but disables some of their utilities (like pg_ctl as addressed in this question). In place of those utilities, it simplifies use with service [start|stop|restart|reload] postgresql and pg_ctlcluster.


No, if the user connects to SQL Server using a SQL Authentication login, there is no way to determine from that which Windows login was responsible. SQL Server  can only record the information it has been provided, and when you use SQL Authentication, no Windows login / domain / group information is passed to SQL Server.

You can look at DMVs like sys.dm_exec_sessions and sys.dm_exec_connections to get other supporting information, such as IP address and host name, but:

You can only do this going forward, you can't look back in time unless you were specifically collecting this information (you can do this now with a server-side trace, or a logon trigger, or audit, or extended events, or...)
This will only help if the user connects directly to SQL Server from their workstation. If your users use a jump box in the data center or have common access to other machines, you'll be able to tell which machine they came from, but not which Windows user was logged into the box at the time. If you know exactly when the change happened in SQL Server, you might be able to correlate that time with the logs on the machine to determine which user it was.

You can't even prevent users from connecting as this SQL Authentication login unless you change the application to use Windows Auth and disable SQL Auth altogether. Sure, you could set up a logon trigger that prevents logging in unless host_name and/or program_name are within a desired set of values, but these things are very easy to spoof, so if this behavior is malicious, your options are quite limited.


Conditional Query selection on the basis of paarameters

I'm not sure you know what you are doing.

Two things about query #1: it is invalid, and it is useless.

What is emp.? There's no table alias emp there ... It might be a function id that belongs to package named emp, but I somehow doubt it. You probably meant something like

select emp.id from employee emp
where ...

If that's so, it just doesn't make sense. The way you wrote it, it is equal to

select id from employee

because the first SELECT returns values values "equal" to something, while the second one returns values "different" from that something, and you then UNION ALL them. That's the whole contents of the EMPLOYEE table.

The first part of query #2 is wrong as well; you're performing Cartesian product of employee and salary tables (and then restrict the result with WHERE clause). What is

and '&BI' is not null

condition used for? Did you mean

and I is not null

instead?

Also, what is I? The way you put it, it is a column in one of those tables (probably employee, according to query #1). No problem if that's so, it's just that it is somewhat unusual name for a column.

Now, what do you mean by "combining" those two queries? How? You can't UNION them because their column list doesn't match. What would you combine, then?

Finally, how do you run anything dynamically? What do you mean by saying that?

Please, provide a test case (create table & insert into sample data), and - using that data - explain the result you'd want to get and rules that should make that happen.


I would first consider trying to combine the two queries into one. If the second query could be refactored to return null in the case where the first query returned zero would you be able to work with that?

For example,

testbed:

create table t1( cp_id integer,
                 brl_id integer,
                 security_enabled_ind char(1) );
create table t2( brl_id integer,
                 ranking number );
create table t3( ppr_id integer,
                 brl_id integer );
create table t4( ppr_id integer,
                 plcy_id integer,
                 where_clause varchar(1000),
                 ranking number );
create table t5( plcy_id integer,
                 db_object_name varchar(100),
                 statement_type integer );

refactored query:

with w as ( select rpr.brl_id, ppr_id, plcy.statement_type
            from t3 rpr join t4 ppr using(ppr_id) join t5 plcy using(plcy_id)
            where plcy.db_object_name=:2 )
select where_clause
from( select where_clause
      from t4 ppr_o
      where not exists ( select *
                         from t1 cpr join t2 brl using(brl_id)
                         where cpr.cp_id = 1 and brl.ranking > 0
                               and cpr.security_enabled_ind='Y'
                               and brl_id not in (select brl_id from w) )
            and ppr_o.ppr_id in ( select ppr_id
                                  from t1 cpr join w using(brl_id)
                                  where cpr.cp_id=:1
                                        and cpr.security_enabled_ind='Y'
                                        and w.statement_type=:3 )
    order by ppr_o.ranking desc nulls last )
where rownum=1;


Best Practice to Store an Admin Configured Variable

I have worked with databases that had a settings table, which was table with one row and a lot of columns, where each column represented a value that was configurable, and should hold true everywhere in the application.

As I believe most DBMSes have an upper limit on the number of columns a table can have, there are limits on this, but it can be a practical solution for setting what amount to configurable global constants for the application.

That said, given that there is a logical place to store this information, I would agree that your suggestion (store it with the rest of the manager data, and a special category (default)) makes perfectly good sense, and should make queries a bit easier to write, and to follow later.


Ok. I discovered that the source of this problem is a Server upgrade where the server name was changed and thus the outbound SQL instance name changed.The maintenance plans are stored in the msdb system database and this is where they may be edited. So after some online search, I found this SP (which I have modified):

USE msdb go
BEGIN TRY
    DECLARE @errno INT, @errmsg varchar(100)    DECLARE @mpname varchar(200)

--Set the NAME of the Rogue Maintenance plan here!
SET @mpname = 'myPlanToDelete'

BEGIN TRAN

-- Delete the JobStep Logs for this plan's jobs
-- SELECT jl.*
DELETE dbo.sysjobstepslogs
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobsteps as js ON (sb.job_id = js.job_id)
    JOIN dbo.sysjobstepslogs as jl on (js.job_id = jl.step_uid)
 WHERE pk.name = @mpname

-- Delete the JobHistory for this plan's jobs
--SELECT jh.*
DELETE dbo.sysjobhistory
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobhistory as jh ON (sb.job_id = jh.job_id)
WHERE pk.name = @mpname

-- Delete the Job Schedules for this plan
--SELECT js.*
DELETE dbo.sysjobschedules
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobschedules AS js on(sb.schedule_id = js.schedule_id)
 WHERE pk.name = @mpname

-- Delete the JobSteps for this plan's jobs
--SELECT js.*
DELETE dbo.sysjobsteps
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobsteps as js ON (sb.job_id = js.job_id)
WHERE pk.name = @mpname

-- Delete the Jobs for this plan
--SELECT sj.*
DELETE dbo.sysjobsteps
FROM
    dbo.sysdtspackages90 as pk
    JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
    JOIN dbo.sysjobs as sj ON (sb.job_id = sj.job_id)
WHERE pk.name = @mpname

-- Delete the Plan LOGS for this plan
--SELECT pl.*
DELETE dbo.sysmaintplan_log
FROM
    dbo.sysmaintplan_log as pl
    join dbo.sysdtspackages90 as pk ON (pk.id = pl.plan_id)
WHERE pk.name = @mpname

-- Delete the SUB-Plans for this Plan
--SELECT sp.*
DELETE dbo.sysmaintplan_subplans
FROM
    dbo.sysmaintplan_subplans as sp
    join dbo.sysdtspackages90 as pk ON (pk.id = sp.plan_id)
WHERE pk.name = @mpname

-- Delete the PLAN
-- SELECT *
DELETE dbo.sysdtspackages90
FROM dbo.sysdtspackages90
WHERE name = @mpname

-- All done!
COMMIT WORK

END TRY
BEGIN CATCH
    SELECT @errno = ERROR_NUMBER() ,@errmsg = ERROR_MESSAGE()
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRAN
    END
    RAISERROR(@errmsg,16,127)
END CATCH
GO

Thanks.


Not an ideal answer....but may be we will have to add a table column and use that to denote different locations. So, with merge replication in place, the node1 location bit as 0 and node2 location bit as 1....so it doesn't matter for hub.....however, for node1 can have further reporting proc code with that location filter. Same way for node2 process code also. And don't replicate this proc. Seems some coding effort. So, basically we are letting the data to replicate..... however limiting it to not come in reports by using filtered proc code. Thanks.


Autotrace in SQL Developer gets the plan from v$sql_plan, and also gets the stats from your session, does a delta of session stats before and after running the query.

Explain Plan asks the database what it THINKS the plan will be for your query.

Your co-worker is right, they can differ wildly, and you're better served to use AutoTrace or our new feature in 4 and higher where we show the cached plan (a drop down control on the explain plan button makes these available.)


Possible reasons for slow BLOB updates in Oracle?

There are various tricks for some performance improvements in the OCI library(for example chunk size settings). But since you do not have direct access to OCI you should contact you DOA vendor.

If you want to trace user's session from begging you have to create a "LOGON TRIGGER ON SCHEMA" and then call something like dbms_support.start_trace(true) in this trigger.
Or you can set tracing in other session using dbms_monitor.

You should first "prove" that the root cause is BLOB handling. Server side log should give you some numbers. (Just beware - it's not easy to read).

PS: there is also a way how you can register your "interceptor" library in OCI.dll. This will give detailed tracing for the client's side and will tell you how your DAO is working. But this really requires some Oracle experience.


The owner of your database on the old server was some Windows account that does not exists as login on the new server (if it existed, its login sid would match user sid as it's Windows account, so it's Windows sid).

To fix it you can make sa the owner of your database:

alter authorization on database::YourDBName to sa

Or any other login (I just don't see other logins on your picture that are not certificate logins)


Is it possible to obtain number of prepared statements a connection holds?

You can view prepared statements with the pg_prepared_statements view. Query this view to get a count.


QUERY - pivot multiple columns, variable number of rows

The problem here seems to me to be largely more of a scoping issue - you are likely having difficulty solving this problem on account of the requirements not being defined well enough.  With the description and sample data provided, there are at least three partial solutions, none of which may be applicable to your particular use cases.  With the test data set up as follows,

IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   name = 'Recipe'
                    AND type = 'U' )
BEGIN
    --DROP TABLE dbo.Recipe;
    CREATE TABLE dbo.Recipe
    (
        Recipe          INTEGER NOT NULL,
        VersionID       INTEGER NOT NULL,
        Ingredient      VARCHAR( 8 ) NOT NULL,
        Percentage      DECIMAL( 5, 2 )
    );

    INSERT INTO dbo.Recipe ( Recipe, VersionID, Ingredient, Percentage )
                SELECT  4000, 100, 'Ing_1', 23.0
    UNION ALL   SELECT  4000, 100, 'Ing_100', 0.1
    UNION ALL   SELECT  4000, 200, 'Ing_1', 20.0
    UNION ALL   SELECT  4000, 200, 'Ing_100', 0.7
    UNION ALL   SELECT  4000, 300, 'Ing_1', 22.3
    UNION ALL   SELECT  4000, 300, 'Ing_100', 0.9
    UNION ALL   SELECT  4001, 900, 'Ing_1', 8.3
    UNION ALL   SELECT  4001, 900, 'Ing_100', 72.4
    UNION ALL   SELECT  4001, 901, 'Ing_1', 9.3
    UNION ALL   SELECT  4001, 901, 'Ing_100', 70.5
    UNION ALL   SELECT  5012, 871, 'Ing_1', 45.1
    UNION ALL   SELECT  5012, 871, 'Ing_100', 0.9
    UNION ALL   SELECT  5012, 877, 'Ing_1', 47.2
    UNION ALL   SELECT  5012, 877, 'Ing_100', 0.8
    UNION ALL   SELECT  5012, 879, 'Ing_1', 46.6
    UNION ALL   SELECT  5012, 879, 'Ing_100', 0.9
    UNION ALL   SELECT  5012, 880, 'Ing_1', 43.6
    UNION ALL   SELECT  5012, 880, 'Ing_100', 1.2;

    ALTER TABLE dbo.Recipe
    ADD CONSTRAINT PK__Recipe
        PRIMARY KEY CLUSTERED ( Recipe, VersionID, Ingredient );

    CREATE NONCLUSTERED INDEX IX__Recipe__Recipe__VersionID
        ON  dbo.Recipe ( Recipe, VersionID )
    INCLUDE ( Percentage );
END;
GO

we can use our new table to explore some possible solutions.  Expanding on the sample output, we add the next recipe into the result set to illustrate the difficulty with the question.

RECIPE --- INGREDIENT_V100 --- PERCENTAGE_V100 --- INGREDIENT_V200 --- INGREDIENT_V200
4000       Ing_1               23,0                Ing_1               20,0
4000       Ing_100              0,1                Ing_100              0,7
4001       Ing_1                8,3                Ing_1                9,3
4001       Ing_100             72,4                Ing_100             70,5

Columns %_V100 and %_V200 make perfect sense in the case of the 4000 recipe, but quickly lose their meaning as additional recipes are added.  The 4001 recipe would need new and separate columns to properly label the data by version, but since the version numbers differ for each and every recipe, that path leads us to a very sparse result set which would be downright annoying to use, or we must alias the columns, losing the version number data.

Solution 1:

Starting with what I feel is the absolute worst way of going about this, let's look at the sparse result set.  For the sample data, we would be attempting to generate a query which would look like something along the following lines:

SELECT  p.Recipe,
        [Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100],
        [Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200],
        [Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300],
        [Ingredient_v871] = CASE WHEN p.[871] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v871] = p.[871],
        [Ingredient_v877] = CASE WHEN p.[877] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v877] = p.[877],
        [Ingredient_v879] = CASE WHEN p.[879] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v879] = p.[879],
        [Ingredient_v880] = CASE WHEN p.[880] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v880] = p.[880],
        [Ingredient_v900] = CASE WHEN p.[900] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v900] = p.[900],
        [Ingredient_v901] = CASE WHEN p.[901] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v901] = p.[901]
FROM (  SELECT  r.Recipe,
                r.VersionID,
                r.Ingredient,
                r.Percentage
        FROM    dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
        FOR s.VersionID IN ( [100], [200], [300], [871], [877], [879], [880], [900], [901] ) ) p
ORDER BY p.Recipe;

On account of the variable number of versions, we can use some dynamic SQL to generate and execute the query.

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX );

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
                    + ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
                    + ' ELSE p.[Ingredient] END, '
                    + '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.['
                    + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  r.Recipe,
                        r.VersionID,
                        r.Ingredient,
                        r.Percentage
                FROM    dbo.Recipe r ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.VersionID IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO

This result set plainly sucks.  Here's an SQL Fiddle which displays the results, take a peek and let's move on.

Solution 2:

With the sparse result set proving to be next to useless, we can accept losing the version numbers of the recipes and simply order them in ascending version number. For the purpose of the example we'll alias alphabetically, so that versions 100, 200 and 300 of recipe 4000 will receive A, B and C designations, whereas versions 900 and 901 will receive just A and B.  The query we would like to generate for this should look somewhat like the following:

SELECT  p.Recipe,
        [Ingredient_vA] = p.[Ingredient], [Percentage_vA] = ISNULL( p.[Percentage_vA], 0 ),
        [Ingredient_vB] = p.[Ingredient], [Percentage_vB] = ISNULL( p.[Percentage_vB], 0 ),
        [Ingredient_vC] = p.[Ingredient], [Percentage_vC] = ISNULL( p.[Percentage_vC], 0 ),
        [Ingredient_vD] = p.[Ingredient], [Percentage_vD] = ISNULL( p.[Percentage_vD], 0 )
FROM (  SELECT  Lvl = 'Percentage_v' + CHAR( 64 +
                    DENSE_RANK() OVER (
                        PARTITION BY r.Recipe
                        ORDER BY r.VersionID ) ),
                r.Recipe,
                r.Ingredient,
                r.Percentage
        FROM    dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
        FOR s.Lvl IN ( [Percentage_vA], [Percentage_vB], [Percentage_vC], [Percentage_vD] ) ) p
ORDER BY p.Recipe;

Similarly to solution one, dynamic SQL can be leveraged to accomplish this.

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX );

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Percentage_v' + CHAR( 64 + a.Lvl ) + '], '
        FROM (  SELECT  DISTINCT Lvl = DENSE_RANK()
                            OVER (  PARTITION BY r.Recipe
                                    ORDER BY r.VersionID )
                FROM    dbo.Recipe r ) a
        ORDER BY a.Lvl
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Col, LEN( b.Col ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CHAR( 64 + a.Lvl ) + '] = p.[Ingredient], '
                    + '[Percentage_v' + CHAR( 64 + a.Lvl ) + '] = ISNULL( p.[Percentage_v'
                    + CHAR( 64 + a.Lvl ) + '], 0 ),'
        FROM (  SELECT  DISTINCT Lvl = DENSE_RANK()
                            OVER (  PARTITION BY r.Recipe
                                    ORDER BY r.VersionID )
                FROM    dbo.Recipe r ) a
        ORDER BY a.Lvl
        FOR XML PATH ( '' ) ) b ( Col );

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  Lvl = ''Percentage_v'' + CHAR( 64 +
                            DENSE_RANK() OVER (
                                PARTITION BY r.Recipe
                                ORDER BY r.VersionID ) ),
                        r.Recipe,
                        r.Ingredient,
                        r.Percentage
                FROM    dbo.Recipe r ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.Lvl IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO

This ends up in a much prettier result set, as can be seen in this SQL Fiddle, despite the loss of the specific version numbers of each recipe.

Solution 3:

If the loss of the version numbers can not be tolerated, a hybrid approach can be implemented, though the results from each call would be limited to only a single recipe.  In effect, our goal SQL would be similar to the first solution, but with an expressly defined Recipe number.

SELECT  p.Recipe,
        [Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100],
        [Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200],
        [Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300]
FROM (  SELECT  r.Recipe,
                r.VersionID,
                r.Ingredient,
                r.Percentage
        FROM    dbo.Recipe r
        WHERE   r.Recipe = @Recipe ) s
PIVOT ( MAX( s.Percentage )
        FOR s.VersionID IN ( [100], [200], [300] ) ) p
ORDER BY p.Recipe;

Generation could be handled as follows:

DECLARE @Piv            NVARCHAR( MAX ),
        @Col            NVARCHAR( MAX ),
        @Param          NVARCHAR( MAX ),
        @SQL            NVARCHAR( MAX ),
        @Recipe         INTEGER = 4000;

SELECT  @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r
                WHERE   Recipe = @Recipe ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SELECT  @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
                + ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
                + ' ELSE p.[Ingredient] END, '
                + '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.['
                    + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
        FROM (  SELECT  DISTINCT r.VersionID
                FROM    dbo.Recipe r
                WHERE   Recipe = @Recipe ) a
        ORDER BY a.VersionID
        FOR XML PATH ( '' ) ) b ( Piv );

SET @Param = N'@Recipe  INTEGER';

SET @SQL = N'
        SELECT  p.Recipe, ' + @Col + '
        FROM (  SELECT  r.Recipe,
                        r.VersionID,
                        r.Ingredient,
                        r.Percentage
                FROM    dbo.Recipe r
                WHERE   r.Recipe = @Recipe ) s
        PIVOT ( MAX( s.Percentage )
                FOR s.VersionID IN ( ' + @Piv + ' ) ) p
        ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL, @param = @Param, @Recipe = @Recipe;
GO

The results could then be accessed per recipe, as shown in this SQL Fiddle or this one.


I think the condition you are checking will always be true as for every insert query executed there will be an entry in the dbo.Table and inserted as well so it will rollback the transaction.

From MSDN

  The inserted table stores copies of the affected rows during INSERT
  and UPDATE statements. During an insert or update transaction, new
  rows are added to both the inserted table and the trigger table. The
  rows in the inserted table are copies of the new rows in the trigger
  table.

I would advice you to check the duplicate before insert as

IF NOT EXISTS (Select a.* FROM [dbo].[Table] a WHERE..............)
  INSERT INTO [dbo].[Table] .......

More Readings : http://msdn.microsoft.com/en-in/library/ms191300.aspx


Clear schema from database without dropping it

I assume you are trying to push your changes back to the database from your entity model? If so you can do that from studio. (Right click in the entity modeler, save changes to the database.)

If you have to recreate it - you can also just change where the EF is pointing in your configuration settings and push the model out to the database if you have create permissions. Really just depends what rights you were given.


Can I restore a table with flashback database even if I used "drop table purge"?

Flashback database can be used to restore a dropped table that is no longer in the recyclebin.

Provided the following is true:

1) Flashback database is turned on which you can check via:

select flashback_on from v$database;

2) You have the flashback logs to flashback that far, which you can check via:

select oldest_flashback_time from v$flashback_database_log;.

3) You have the backup archived redo/archived redo on disk to flashback the database that far.

The syntax looks like the following:

sqlplus / as sysdba
shutdown immediate;
startup mount;
FLASHBACK DATABASE TO TIME 'SYSDATE-1';
startup open resetlogs;

The Oracle Database 12c docs about flashback database can be found here.


Deny All DDL & DML Commands In Sql Server

Well...the AD part is unnecessary, that only gives authentication/login privileges to users (basically all AD does in SQL Server is tell SQL Server "I have verified that this person is who she/he says it is and you can allow this person to connect because I say so" - this is a very basic explanation, of course).

If you want to set up privileges about what data users can see and what they can or cannot execute on your SQL Server it is enough to do this in SQL Servers itself. Also, I would not over-complicate things with 'deny', just 'revoking the permission'is more than enough. If they don't have the right to execute DDL and DML, they will not be able to do so, deny is unnecessary.

Here is how to do it:

Step 1. Go to your SQL instance -> Security-> right click the login of the user / group you want to do this for, then on Properties and in your newly opened window got to  'ServerRoles' and make sure only public is selected.

Step2: Go to 'ÚserMapping' --> Database you want to do this for --> make sure only 'public'and 'data reader'are selected.

Step3: Repeat this for all DBs you need to give permission to the user for data reading, but not DML & DDL statements.

Do the same for all other logins you need to do this for.

In SQL Server 2016 or newer make sure you also check the DB user because they decoupled the server level permission from the database level permissions. So you can deny this on server level, but you have this set up on a DB level you and then your sever level setting has no effect.
You can check the database level permissions by going to Database --> Security --> right click on the user you are interested in --> properties. Then on your newly opened window go to both 'owned schemas' and 'membership' and make sure nothing or only 'db_reader'are selected.

You find documentation here and here.

Also, you might want to check this and this out - it shows some examples of how you can get a list of the logins,users and permissions - it will make your life a lot easier.


What is the difference between setting the directive default-character-set under [client] as opposed to [mysqld]?

[client]
default-character-set=utf8

This option can force client programs to use specific character set.
This is normally unnecessary. However, when character_set_system differs from character_set_server or character_set_client, and you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly. In such cases, starting the mysql client with --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the problem.

[mysqld]
default-character-set = utf8

MySQL Server has a server character set and a server collation. These can be set at server startup on the command line or in an option file and changed at runtime.
The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.

Conclusion: Client setting is at session level whenever client connects to server, server forces clients to use specific charset however this is not necessary.
Mysqld section charset indicates that server is configured to use particular charset by default however you can change charset at any level Database,Table and Column level you want.


Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

All of the platforms you have mentioned can run close to zero data loss configurations. All of them could be deployed in a configuration that will fail.

Platform choice is one part of the puzzle. It will be your implementation of the platform that determines whether or not your requirements are met.

MySQL Cluster
Oracle RAC
PostgreSQL High Availability
SQL Server High Availability

In reality, high availability and disaster recovery are a trade off between uptime, Recovery Point Objective, Recovery Time Objective and cost.

Zero data loss and zero downtime is expensive, complicated to setup and complicated to maintain. There is no free lunch.


mysql information schema database last used datetime

No, that information is not available on INFORMATION_SCHEMA (it only stores metadata).

There are several options that I can recommend you:

The user stats plugin from Google: after installed and enabled, it will not tell you the last time a table was accessed, but it will tell you how many times it was
Using the slow log: you can activate the slow log and set long_query_time = 0 (be careful of the extra overhead and disk space and then analyze per table used, so you can detect the last time it was used. Tools like pt-query-digest --review aggregate by query, not by table, but they may be useful to look for specific queries.
With an audit plugin you can fine-tune what you register better than with the slow log, by activating it only for certain queries or objects.
The performance schema can gather information about everything, but you may need MySQL 5.6 and the configuration can be a bit cumbersome

All of them will tell you information since the point you activate them, and not past information. UNIX atime is very unreliable for access time, but you may also want to give that a try, depending on the engine.


Remove invisible null characters a string's ending

One of the reasons I vastly prefer convert() over cast() is that convert() is much more extensible. For example, you can use a style number to convert a binary value to a string as is. So if 3F00 is always the problematic character:

SELECT CASE
  WHEN CONVERT(nvarchar(max), @BrokenString, 1) LIKE N'%3F00'
  THEN 'borked' END;

Result:

borked

So you can find all the offending rows (this will not set any speed records) using:

SELECT ... FROM dbo.table
WHERE CONVERT(nvarchar(max), column) LIKE N'%3F00';


mysql replication master 5.5 slave 5.1 error on create database

Every replication event generated by the master includes the error code that resulted from execution of the query that generated the event.  This is usually "success" (0, no error).

When the slave executes a query, it expects the error it encounters to be the same error as the master encountered (again, usually "no error").  When this doesn't happen, replication stops.  Note that the error message from the master isn't preserved, just the code.  That's why you see placeholders in the error message, because the slave displays the sprintf() template for the error message.

But note, carefully, where the error is.

Check the master server's error log.

It looks as if the master was not correctly upgraded to MySQL 5.5; specifically, it looks like the mysql_upgrade utility was not used to upgrade the system tables to be fully compatible with MySQL 5.5.

http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

You are indeed running an unsupported configuration, with a newer master and an older slave... but in this case, it appears that your master has a problem that is actually unrelated to replication.

Error on master:
message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d.  Created with MySQL %d, now running %d.
Please use mysql_upgrade to fix this error.'
error code=1558 ;

The master encountered an error; you may find similar errors in the master's log, although it's possible that they only occur at startup, if at all.  In any event, your application should have seen this error also... perhaps it's ignoring it. :(

The slave didn't encounter any error executing the actual query.

Error on slave: actual message='no error', error code=0.

It occurred to me that it's very important that the actions performed by mysql_upgrade can't be safely replicated, since the slave server is older.  It turns out, this was anticipated in the design, so the mysql_upgrade utility appears to be safe to run on a master, without disconnecting the slave, even if the slave is older (or was already upgraded). According to this comment in the source code of mysql_upgrade.c:

Master and slave should be upgraded separately.
All statements executed by mysql_upgrade will not be binlogged.


The gen_clust_index (clustered index) under the hood of InnoDB houses entries of primary keys along with rowids. What is interesting about the use of the gen_clust_index is the fact that any non-unique indexes you create will always have a corresponding rowid for the gen_clust_index of a table. Thus, there are always double index lookups, one for the secondary index and one for the gen_clust_index.

Any attempts to improve the layout of a table or primary key gets nullified because of the gen_clust_index, or at least marginal results at best.

EXAMPLE

Some people attempt to sort a MyISAM in PRIMARY KEY order. According to MySQL Database Design and Tuning, Page 236 Paragraph 7, under the subheading "Storing a Table in Index Order":

  If you frequently retrieve large ranges of indexed data from a table
  or consistently sort results on the same index key, you might want to
  consider running myisamchk with the --sort-records option. Doing so
  tell MySQL to sort the table's data in the same physical order as the
  index, and can help speed these kinds of operations. Alternatively,
  you can combine the ALTER TABLE statement with an ORDER BY a
  particular column option to achieve the same results.

Granted, this works and works effectively FOR MyISAM. You could perform ALTER TABLE ... ORDER BY col1,col2,...,coln against InnoDB where the columns may or may not be that of the PRIMARY KEY. This will not produce faster results for InnoDB because ... that's right ... you must consult the gen_clust_index each time.

Some people can make the table's row format FIXED using ALTER TABLE mydb.mytb ROW_FORMAT=Fixed; and can get a 20% increase in read performance without any other changes. This works and works effectively FOR MyISAM. This will not produce faster results for InnoDB because ... that's right ... you must consult the gen_clust_index each time.

You could perform the following on an InnoDB table named mydb.mytb:

CREATE TABLE mydb.mytc LIKE mydb.mytb;
INSERT INTO mydb.mytc SELECT * FROM mydb.mytb ORDER BY col1,col2,...coln;
ALTER TABLE mydb.mytb RENAME mydb.mytd;
ALTER TABLE mydb.mytc RENAME mydb.mytb;
DROP TABLE mydb.mytd;

This will put the table in rowid order in the gen_clust_index. This may produce marginal results for InnoDB at best because ... that's right ... you must consult the gen_clust_index each time.

Now, let's get a little ridiculous. There is a NoSQL interface to query (SELECT only) MyISAM and InnoDB called the HandlerSocket (formerly called HANLDER) interface. This gives you access to data which lets you bypass all SQL, ACID, and MVCC protocols. Although it is possible, IMHO WAY TOO COMPLICATED TO CODE AND MAINTAIN. AFAIK there is nothing in print stating whether the HandlerSocket interface interacts with the gen_clust_index or not.

In summary, there are many ways to skin a cat. In this case, you cannot get a hold of the cat (the gen_clust_index). I guess this is why MyISAM continues to exist for its read performance, its pliability in table ordering, table row format, and the tools in support of it. InnoDB is will remain designed around its ACID-compliant nature until some brave soul takes the InnoDB source code and transforms it into something that has the best of both MyISAM and InnoDB.


If you have all the logic that you want to check within your stored procedure, then storing the time "that it happened" would only be storing the time you actually checked the table. If you are executing this stored procedure every hour via an Agent job then you only know it happend withing that previous hour.

Using a trigger on the inserts would provide a more detailed timestamp as to when the table went over your threshold.

Now for immediate notification I would likely look at SQL Agent Alerts. I don't work with triggers all that much, but would think you could do something similar to the following logic in the trigger:

Check row count of table
If over threshold use RAISERROR to generate event text (custom sys.message) to include your table name
Write entry to your monitoring table, with a timestamp

Now you would need to create a custom message using sp_addmessage (BOL), so you can use it with the RAISERROR. Then your SQL Agent Alert would just check for that message text. When the alert is fired (meaning it caught the message) you configure it to notify you by email.


Job Information - When querying [msdb].[dbo].SYSJOBS_VIEW through linked server

I tested your code by creating a linked server and following @Erik's comment grant the user 'view database state' and then 'view server state'.  Did not work.

If you backtrack the view by using sp_helptext of SYSJOBS_VIEW and sysoriginatingservers_view it uses an undocumented object named sysoriginatingservers.  It is not documented what permission is required to read from sysoriginatingservers.

Minimum permission I was able to get the SYSJOBS_VIEW.ORIGINATING_SERVER column value by adding the user as a member of these to database_role of MSDB database.

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'taiobtest';
EXEC sp_addrolemember @rolename = 'SQLAgentReaderRole', @membername = 'taiobtest';

Another workaround is to replace your code with below by which you can avoid making the user member of SQLAgentReaderRole role.

UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  AS SERVERNAME


Don't put the step in the games table, it's unnecessary.

In your steps table, make a bit column like current_step, and add a unique filtered index on (game_id, current_step) WHERE current_step = 1.

This will prevent duplicates and prevent the circular reference and data integrity issues that are sure to ensue.


SSRS make a conditional format on the column

For each data cell of your table (or matrix) you will have to set an expression on the font color (general tab under text box properties):

Now I am making assumptions of what your dataset/columns are called so you will have to translate those appropriately to your report setup.

For the Revenue and Quantity cells within each category the expression will be something similar to:

==IIF(Fields!ToysRevenue.Value <= 0,"Red","Black")

The expression for your Total Revenue and Total Quantity are a bit more lengthy since you have to check each Revenue value for each category, but it should look something like:

==IIF(Fields!ToysRevenue.Value <= 0 Or
Fields!ClothingRevenue.Value <= 0 Or
Fields!FoodRevenue.Value <= 0, "Red","Black")


List partitioning or filtering a non-partitioned table, which incurs lower cost?

The performance benefits of partition pruning depend on the proportion of rows being selected from the table and the efficiency with which the rows can be isolated to their own partition(s).

If you are selecting 5% of a table's rows and those rows are isolated into a subset of partitions then selecting them is generally going to be around twenty times faster than a full table scan. On the other hand, selecting them via an index could be more or less efficient than a full table scan, but is not going to approach twenty times as fast except maybe in very particular circumstances (perfect clustering of values, bitmap index access, SSD storage ...).

There's virtually no overhead to inserting into a partitioned table. One advantage of partitioning when it comes to inserts is that it can allow multiple simultaneous direct path inserts to the table, which is impossible on a non-partitioned table as the insert would require a segment-level exclusive lock.


Read-Scale Availability Group between a Transactional subscriber and another machine

what sql server version are you using?
The distribution database cannot be placed in an availability group with SQL Server 2012 and SQL Server 2014. Placing the distribution database into an availability group is supported with SQL 2016 and greater, except for distribution databases used in merge, bidirectional, or peer-to-peer replication topologies


Unable to Run psql in Windows Command Prompt

If you want to override the default behaviour that chooses the current Windows' user name as the login for the database, you can define the environment variable PGUSER that contains the user you want to use by default.

Once that is defined and you type psql it assumes that user as the default.

Related: How do I specify a password to psql non-interactively?


Backing up a MongoDB cluster with mongodump?

Upgrading to MongoDB 3.4 was necessary, since the cluster string did not appear to work with 3.2.

Also, based on the blog post for 'importing data', a small tweak to the host parameter seemed necessary, in the form of adding the 'Cluster0-shard-0/' prefix :

mongodump --host "Cluster0-shard-0/cluster0-shard-00-00-2djno.mongodb.net:27017,cluster0-shard-00-01-2djno.mongodb.net:27017,cluster0-shard-00-02-2djno.mongodb.net:27017" \
  --authenticationDatabase admin --ssl --username admin \
  --password mypassword --oplog -o /tmp

Edit: updated the response based on @jjussi's feedback. Also, we aren't trying to avoid using the mongo cloud backup, which is more convenient, it is that we just feel better having our own local copy, for various reasons.


CPU impact, Scan count 9, logical reads 1686, physical reads 0 vs Scan count 1, logical reads 10253, physical reads 20

The context is important when trying to compare the IO statistics of queries. Let's just start by describing what the queries do based on their plans.

The first query scans the heap in parallel until it finds 1000 rows that need to be updated. The update is then performed on the table. In the worst case scenario for performance, the entire table will need to be scanned.

The second query does an index seek until it finds 1000 rows that need to be updated. Both the table data and the index needs to be updated, because you're changing a column in the index. The query's cost is so low that it isn't eligible for parallelism.

Now let's go back to the IO statistics to see if they make more sense. For the first query:

  Table 'hspendingitems'. Scan count 9, logical reads 1686, physical
  reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
  0, lob read-ahead reads 0.

The scan count of 9 means that the query executed with a DOP of 8. The logical read count represents the number of reads needed to find the first 1000 rows and activity required to update the table's data. I'm assuming that the UPDATE query runs in a loop because it only updates 1000 rows. As the query runs it's likely that the logical read count will increase because SQL Server will need to scan more of the table to find the next 1000 rows to update.

For the second query:

  Table 'hspendingitems'. Scan count 1, logical reads 10253, physical
  reads 20, read-ahead reads 0, lob logical reads 0, lob physical reads
  0, lob read-ahead reads 0.

The query doesn't run in parallel so the scan count is 1. The nonclustered index that you created is stored as a separate object so both the table and the index's data need to be updated. I would expect the performance of this query to remain roughly constant as the query runs in a loop.

Should the index be created? It depends. Is there a performance problem right now with the current code? Does the index solve the performance problem in the best way? Are there other queries that could benefit from the index? Will the index slow down other DML on the table too much? We can't answer those questions for you, but I will say that updating 1000 rows against an unindexed heap in a loop often isn't a good strategy in terms of performance.


Transaction Log maintenance when switching to Simple Recovery

I'm switching a lot of databases to SIMPLE recovery mode from FULL recovery mode (T-Logs and point-in-time recovery is not necessary). Will the existing transaction logs be truncated (when checkpoints are created)?

In simple recovery model, the database engine will issue automatic checkpoints and its frequency is determined by the recovery interval (advanced server config setting) or if the log becomes 70% full.

Unless you have some long running transactions occuring that will delay log truncation, an automatic checkpoint will truncate the unused portion of the T-log.

  Some of the existing transaction logs are 50-100GBs, what is the best approach in determining what I should shrink them down to for the purposes of moving forward. I obviously don't want to keep them that large.

If you have the database recovery model set to FULL for those database with 50-100GBs of T-logs, then you have to start doing frequent T-log backups. Remember in Full recovery model, once a log backup chain has been established, even the automatic checkpoints wont cause the log to truncate.

As a last resort, you can truncate the log file and then immediately take a full backup and then start taking T-log backups so that you can do point-in-time recovery if a disaster happens.

  Or, will they shrink on their own over time(I don't think they will)?

As @TomTom pointed out, its a manual operation.

Read up :

Manage the Size of the Transaction Log File
HOW TO SHRINK THE SQL SERVER LOG - Excellent Write up.
How do checkpoints work and what gets logged - By: Paul Randal


SQL Update column with another table column

Update Syntax for PostgreSQL is different:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

In your case please try the below query:

UPDATE TableA AS A
SET indicator = B.indicator
FROM TableB AS B
WHERE B.type = A.type

Result:

gid     type    indicator
--------------------------
1       a       alfa
2       b       alfa
3       e       gama
4       a       alfa
5       d       beta

SQLFiddle working DEMO


SQL Server Cluster Failover Making DB Inconsistency Error

After many trials and errors, the fault lies in NIC card of Server-B. After I replaced it, the cluster move-over operates smoothly without any DB inconsistency ;) However, I do not understand how a NIC card can cause DB inconsistency (a 'ping' to the NIC card works well)


I'm passing an array of strings ...

You are not passing an array, at least not a Postgres array type.
An array literal would look like this:

'{string1, string2}'

You can type it explicitly:

'{string1, string2}'::text[]

The same can be achieved with an ARRAY constructor in most places:

ARRAY['string1', 'string2']

This is constructing a row (of type "anonymous record"):

('string1', 'string2')

It's short syntax for a ROW constructor:

ROW('string1', 'string2')

And can, in similar fashion be provided as string literal (row literal) as well:

'(string1,string2)'

Each of those anonymous records can be cast to a well know row type:

'(string1,string2)'::my_row_type

In the UPDATE statement Postgres coerces the value to the data type of the target column or raises an exception if that cannot be done. If sometable.somefield is data type text, the text representation of the above row is saved, which is  '("string1","string2")'. Double quotes are only added where necessary to keep the syntax unambiguous.

Related:

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?
PostgreSQL: Issue with passing array to procedure


What is the purpose of setting serveroutput in oracle?

set serveroutput on executed in SQL Plus executes behind the scenes:

dbms_output.enable(buffer_size => NULL);

set serveroutput off executes:

dbms_output.disable;


Parallelism with temp table but not table variable?

Quote below from this question on the differences between table variables and temp tables.  Look at the section on parallelism.

  Queries that insert into (or otherwise modify) @table_variables cannot
  have a parallel plan, #temp_tables are not restricted in this manner.


Recovering MySQL data from files in windows

Install the same version of MySQL you had before.  Reload my.cnf (my.ini) if possible.  Reload all the files in that tree.  Start mysqld.  It might work.

If it does not work, look at the error file; there may be clues.  A common one has to do with the size of iblog; this is 'easily' fixed.


How to select only one database in a policy in SQL Server?

Policies are server-wide. But you can have a condition to only bother about the policy based on, for instance, the database name. And that is exactly what that condition that you were about to create would do. So keep creating that for the database name that is relevant for you and you have achieved what you want to achieve.


Will a Transactional Replication break the LSN chain in my backups

I did look at log shipping to keep the copy up to date but this will
  break the LSN and render my backups unrestorable (Log backup every 15
  minutes).

Technically, no they won't break the LSN. It simply means when restoring you would have to coordinate restores with both your managed backups and your log shipping backups. While this is a nightmare from a management overhead perspective, it won't actually break your LSN chain like say changing the recovery model would.

  I reasonable confident that a Transactional Replication will fit the
  bill working from the Distribution Database and leaving the source
  unaffected but I can't find a definitive confirmation of this.

Transactional Replication reads the transaction log and writes the transactions affecting tables marked for replication into the distribution database for distribution to your subscribers. This does not mark the transaction log except to mark those transactions it has already processed for future runs of the log reader agent to avoid duplication of work.

Transactional replication will not impact your managed backups, break the chain or affect the recoverability of your database from managed backups.


Using to_base64 function and regexp_replace with backreferences in MariaDB

The algorythm as I see it.

We don't know how many replaces we need. So we must use recursive CTE or iterative/recursive code.

In it we must:

Find first pattern occurency offset in source data starting from some offset (initially 1)
Split source by head, pattern and tail
Perform pattern replace and encode
Concat head and encoded replace, calculate its length (it will be used as search starting offset on the next iteration)
Concat tail
Iterate next if current starting offset (last "first pattern occurency offset" - see step 1) > 0

In main query we select only those records where "first pattern occurency offset" = 0.

This algo can be realized:

using local variables @variable performing all chained calculations in excess fields which will be skipped in main query
in user-defined function which processes one literal value
in stored procedure

The process can be simplified if there is a guarantee that only one pattern match exists - but in that case the result for values which contains more occurences (randomly) will be unpredictable.


Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view?

I might be misunderstanding your question. Are you asking if given the following tables and views:

CREATE TABLE dbo.Foo
(
    [col1_abc] INT
    , [col2_def] INT
)
GO

CREATE TABLE dbo.Bar
(
    [col1_ghi] INT
    , [col2_jkl] INT
)
GO

CREATE VIEW dbo.vwFoo
AS
SELECT
    [col1_abc] AS col1
    , [col2_def] AS col2
FROM
    dbo.Foo
GO

CREATE VIEW dbo.vwBar
AS
SELECT
    [col1_ghi] AS col1
    , [col2_jki] AS col2
FROM
    dbo.Bar
GO

Will this:

SELECT
    f.col1
    , f.col2
    , b.col2
FROM
    dbo.vwFoo f
INNER JOIN
    dbo.vwBar b
ON  b.col1 = f.col1

perform comparably to:

SELECT
    f.[col1_abc]
    , f.[col2_def]
    , b.[col2_jki]
FROM
    dbo.Foo b
INNER JOIN
    dbo.Bar b
ON  b.[col1_ghi] = f.[col1_abc]

If that's the question the answer is yes, the performance will be identical.

In SQL2000, the optimiser sometimes struggled with deeply nested complex views joining to each other but not with simple scenarios such as this. I've not encountered the problem so much in 2005+.


How to normalize a table that contains a CK not included in the PK?

Model name does not need to be part of the PK.  The problem with this table is brand. "Scott" is repeated. Model relies on Brand, but brand does not rely on model.

Create a brand table. Turn the Model.brand to Brand_FK.


SSIS - Disable Task Based on Variable Value

I would add a script task at the beginning of the package, or right before the tasks in questions.

The script task should:

Take in the Variables (like User::IsCounterpartyReportRequired)
Display those variables in a message box (via MessageBox.Show(Variables.IsCounterpartyReportRequired.ToString())

This way you can see the variables at any stage of the package you wish, and find out if there is some other task that's making them change value.  It's a "quickwatch" style debugging but with MessageBox.

Just make sure you don't leave the messageBox Commands running before you deploy, as your package will fail.


How do i prevent executing os command in oracle 11g

There is no reasonable way how to do that. For example till 11gR1 SELinux was not certified to be used with Oracle database. Since 11gR2 this is at least not recommended.

Let's assume that you and Oracle DBA are adult, where is the problem then?
Oracle is not running with root privs. On the other hand Oracle requires many kernel tunables(sysctl) to be set according to Oracle's manuals, and also Oracle monitoring might require execution of some OS commands, so you should cooperate with DBAs anyway.


Log Sql Server Transactions (Queries + username)

Change tracking provides an efficient, light-weight data change tracking mechanism.  In other words, this only tracks UPDATE, INSERT, DELETE statements; it does not track SELECT statements.  It does not track who made the change.

If you truly need to track all DML and DDL statements made against the server, you'll need to implement an Extended Events monitoring solution, but be aware for a server that is heavily used, you will likely affect performance in a negative way.

If you only are interested in DML changes, then change tracking will allow you to understand what changes were made to the data using the CHANGETABLE T-SQL statement.  This sample code shows how that works.  The code drops any database named ChangeTrackingTest first, then creates that same database with a single table that has change tracking enabled.

USE master;
GO
DROP DATABASE ChangeTrackingTest
GO
CREATE DATABASE ChangeTrackingTest;
ALTER DATABASE ChangeTrackingTest
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
USE ChangeTrackingTest;
GO
CREATE TABLE dbo.t
(
    id int NOT NULL
        PRIMARY KEY
        CLUSTERED
);
ALTER TABLE dbo.t ENABLE CHANGE_TRACKING;

INSERT INTO dbo.t (id) VALUES (1);
UPDATE dbo.t SET id = 2 WHERE id = 1;

SELECT current_id = t.id
    , ctc.*
FROM dbo.t t
    CROSS APPLY CHANGETABLE(CHANGES dbo.t, NULL) ctc
GO

The results:

╔════════════╦════════════════════╦═════════════════════════════╦══════════════════════╦════════════════════╦════════════════════╦════╗
║ current_id ║ SYS_CHANGE_VERSION ║ SYS_CHANGE_CREATION_VERSION ║ SYS_CHANGE_OPERATION ║ SYS_CHANGE_COLUMNS ║ SYS_CHANGE_CONTEXT ║ id ║
╠════════════╬════════════════════╬═════════════════════════════╬══════════════════════╬════════════════════╬════════════════════╬════╣
║          2 ║                  2 ║                           1 ║ D                    ║ NULL               ║ NULL               ║  1 ║
║          2 ║                  2 ║                           2 ║ I                    ║ NULL               ║ NULL               ║  2 ║
╚════════════╩════════════════════╩═════════════════════════════╩══════════════════════╩════════════════════╩════════════════════╩════╝

Cleanup:

USE master;
GO
DROP DATABASE ChangeTrackingTest


Procedure permission issues with a SQL state query

I think that your simplest option is a job.

You can grant the permissions to start a job through the SQLAgentOperatorRole role in msdb. Members of this role can start jobs owned by a sysadmin login, which can execute the commands you need.

Here's an example based on your requirements:

USE master
GO

CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , CHECK_POLICY = OFF
GO

USE msdb
GO

CREATE USER testUser FOR LOGIN testUser
GO

EXEC sp_addrolemember 'SQLAgentOperatorRole', 'testUser'
GO

DECLARE @jobId uniqueidentifier

EXEC sp_add_job @job_name=N'test',
        @enabled=1,
        @owner_login_name=N'sa',
        @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobserver @job_id=@jobid, @server_name = @@SERVERNAME

EXEC sp_add_jobstep @job_id=@jobId, @step_name=N'step1',
    @subsystem=N'TSQL',
    @command=N'DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
SELECT * FROM dbo.sysfiles
SELECT * FROM sys.master_files
SELECT * FROM tempdb.sys.database_files
SELECT * FROM tempdb.sys.dm_db_file_space_usage
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.partitions
SELECT * FROM sys.indexes
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_exec_sessions
'
GO

EXECUTE AS LOGIN = 'testUser'

EXEC msdb.dbo.sp_start_job @job_name = 'test'

REVERT
GO

-- CLEANUP:
-----------
-- EXEC msdb.sys.sp_executesql N'DROP USER testUser'
-- DROP LOGIN testUser
-- EXEC msdb.dbo.sp_delete_job @job_name = 'test'

While granting the SQLAgentOperatorRole is stil a (minor) security concern, I find it less worrying than other solutions.