Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently

Repro!

I was able to reproduce your issue using SQL Query Stress, both using your example, and changing things to create the indexes inline, as Paul suggested:

CREATE TABLE #temp_table_name_goes_here
     (
         the_first_col BIGINT NULL,
         the_second_col INT NULL,
         another_col VARCHAR(20) NULL,
         INDEX tmp_indx_temp_table_name_goes_here ( the_first_col )
               WHERE the_first_col IS NOT NULL,
         INDEX tmp_indx_outstanding_inventory ( the_second_col )
               WHERE the_second_col IS NOT NULL
     );

Of course, SQS was running 200 concurrent sessions for 20 iterations., so it took some work to get there.

I'm on SQL Server 2017, so the situation was not changed by enabling Trace Flag 3427.

If tempdb is not configured optimally on your system, I'd start there:

One data file per core up to 8 cores
Enable Trace Flags 1117 and 1118

If you still have issues, I'd open a support case with Microsoft. Scalability issues like this should be front and center.

Follow Up

After stumbling on this question, I decided to re-try the test using table variables. Under the same circumstances as above, the same contention was observed.

DECLARE @temp_table_name_goes_here TABLE
     (
         the_first_col BIGINT NULL,
         the_second_col INT NULL,
         another_col VARCHAR(20) NULL,
         INDEX tmp_indx_temp_table_name_goes_here ( the_first_col )
               WHERE the_first_col IS NOT NULL,
         INDEX tmp_indx_outstanding_inventory ( the_second_col )
               WHERE the_second_col IS NOT NULL
     );

I also re-ran the temp table test with an explicit DROP IF EXISTS (or the old style check for a NOT NULL object_id) at the beginning, per Jonathan Fite's comment:

  I've hit this error before. My solution was to add explicit drop of
  the indexes and the temp table (DROP IF EXISTS) before creating it. It
  only happens when the same session runs the stored procedure multiple
  times.

This did help. Contention was either non-existent or minimized.

Adding an explicit DROP TABLE, or DROP IF EXISTS at the end had an odd effect: curiously, the contention switched to dropping the temp tables:


Load clob from "insert into" output file

Since they are sql insert files you can only import them using the sqlplus utility. I'd say a dumpfile in this case is the best solution.


Startup warnings shown in the mongo shell are excerpts from your MongoDB server logs. These warnings do not change until the process is restarted, so if you can't find corresponding entries in your default MongoDB log file I expect that the log file has been rotated since the process was started.
Depending on your environment configuration, old log files are typically renamed and compressed so you may need to search for different file names or use a utility like zgrep to search compressed files.
You can redisplay the warnings via show log startupWarnings or the getLog administrative command.


It is not possible to run shell scripts, install other software, or do any kind of serious administrative tasks on Amazon RDS.

In an RDS environment, you're completely isolated from the operating system and from administration of the actual system that is running MySQL for you.  Your RDS instance is an opaque black box that you can only communicate with via the MySQL command line client, and even that is not likely to be what you think it is.

  I was able to connect to RDS with shell command line from MySQL.

That's good, but that's not the same thing as being at a "command shell" or "command prompt" on the underlying server, which is where you would need to be to install DBT2 or any other utility, and this is not allowed with RDS.

The mysql command line client is, fundamentally, just a tool that allows you to craft queries, issue them to the server, and see the responses.  The underlying protocol is exactly the same protocol that your applications use to issue queries and retrieve results from the server.  What it can do isn't all that different than connecting with Workbench, Query Browser, Toad, HeidiSQL, or any other graphical MySQL administrative tool.  It just isn't graphical.

  So far i have 'cd' to directory where files for dbt2 are located.

If you are saying you have 'cd' into a directory on the RDS instance, then, no, you haven't.  That's not possible.  Perhaps on your local machine, which explains why you are seeing a Windows error message.

C:\Users\Sqlbot>./configure
'.' is not recognized as an internal or external command,
operable program or batch file.

I am a big fan of AWS, a huge fan of MySQL (of course), but not as much of RDS, because I do not generally see being "relieved" of the "burden" of administering my systems as a positive thing, and I don't like the things I am restricted from doing.  On the other hand, I am moving forward with plans to migrate some of my production systems to RDS -- but only the systems that with more mundane database requirements, systems which don't leverage some of the more creative things I do with MySQL.

But I don't want my comments to be taken as anti-RDS.  I realize that to provide a managed, hosted service like they are doing, they have little choice but to lock things down... I just wish some things weren't locked down quite so tightly.  My solution so far is to run MySQL on EC2 and set it up myself, but I digress.

If you are trying to learn to administer MySQL (in depth) and Linux (at all), RDS will severely limit you.


Copy a mongo db collection without locking whole mongod instance

I am trying to run mongodb copy collection on same server. Requirement
  is we delete database as per our archival policy but we need to keep
  one collection from this data as a backup in separate db on same
  server.

Yes, it's possible through mongoexport from your source db and mongoimport the collection to the target database on the same mongod server.

Let's start from beginning.

Suppose that i have two database StackExchange and StackOverflow in same mongod.

> show dbs
StackExchange     0.000GB
StackOverflow     0.000GB

Suppose that in StackExchange there is collection orders having 4 documents.

> show collections
orders
>

Let's check there is documents records in Orders collection exist in StackExchange or not through mongo shell.

 > db.orders.find().pretty()
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea526"),
        "cust_id" : "A123",
        "amount" : 250,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea525"),
        "cust_id" : "A123",
        "amount" : 500,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea528"),
        "cust_id" : "A123",
        "amount" : 300,
        "status" : "D"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea527"),
        "cust_id" : "B212",
        "amount" : 200,
        "status" : "A"
}
>

Here, as we can see that there is 4 document records in orders collection.

Suppose that  i want to copy the orders collection of  StackExchange database to the target database StackOverflow with targetorders documents name.

>mongoexport -d StackExchange -c orders | mongoimport -d StackOverflow -c targetorders
2018-02-14T10:52:16.630+0300    connected to: localhost
2018-02-14T10:52:16.631+0300    exported 4 records
2018-02-14T10:52:16.693+0300    connected to: localhost
2018-02-14T10:52:17.047+0300    imported 4 documents

To verify the targetorders collection documents in StackOverflow database.

> use StackOverflow
switched to db StackOverflow
> show collections
targetorders
test
> db.targetorders.find().pretty()
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea528"),
        "cust_id" : "A123",
        "amount" : 300,
        "status" : "D"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea525"),
        "cust_id" : "A123",
        "amount" : 500,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea526"),
        "cust_id" : "A123",
        "amount" : 250,
        "status" : "A"
}
{
        "_id" : ObjectId("5a44c1479adf6e5fc5cea527"),
        "cust_id" : "B212",
        "amount" : 200,
        "status" : "A"
}
>

So, here we can see all the 4 documents of orders collection from the StackExchange has successfully copied in StackOverflow.

  Very Import Note: Run mongoexport & mongoimport from the system command line, not the mongo shell.

Hope this will help out to you.

For further your ref mongoexport and mongoimport


The Audit Transformation in SSIS Data Flow 'Other Transforms' section is really just for adding data about the environment in which the package is running to the data flow, things like the package name, package id, computer name etc.

If you want to work with SQL Server Audit data that has been output to a file, you probably want an OLE DB Source running SQL similar to this, and some kind of management around which audit files you've already imported:

SELECT *
FROM fn_get_audit_file ( 'S:\SQLAudit\auditTableAccess_*.sqlaudit', DEFAULT, DEFAULT );

This article covers that process quite well:
https://www.mssqltips.com/sqlservertip/3444/automate-the-import-of-sql-server-audit-files-into-sql-server-using-powershell/


Number of tables in join and query performance

More than five tables in a query leads to performance degradation.

A join will increases the cost of running a query to some degree no matter what.  But what's the right thing to do ultimately depends on what's ideal or acceptable for any given project.

For example.  I have a 1-column table that contains 1 million row, whose only column is an ordered bigint clustered index. Make 10 queries ranging from no join to 10 joins, and compare execution plan and execution time.  You'll see the increased cost and time pretty much in a linear manner.  In my personal environment, SELECT * with no join might take 5 seconds to complete while 6 joins might take 6 seconds.  What if the table had only 10,000 records?  It will run significantly faster even if the number of joins is doubled (in my environment <200ms).

Now, if the only way to get the right data set is by joining 10 tables and it takes 5 seconds, is that good or bad?  If the speed is critical to your application, like high-speed stock trading, then you might want to reconsider.  What if it was a report for sales department?

  Does it depends on the database type (SQL Server Vs Oracle Vs MySQL)
  or number of rows in the tables, indexing, INNER Vs OUTER Join,
  cardinality of the tables joined, etc.?

Database product, yes and no.  Each products have different optimization logic.  So one might not execute a query the same as others.  One might be faster than others in some cases and vice versa.  Everything else you mentioned affects performance, including selectivity.  OUTER can be slower than INNER.  There are occasions where OUTER joins can be faster or costs less.

Of course, my very simple example is far from any real-life scenario but the underlying logic is the same.  How many you should join depends on your environment and your requirements.  Things you mentioned will affect the performance, and if the query is running below acceptable level or started having a performance issue, you should examine them.  But none of them are relevant to how many joins you SHOULD have.


Common TVF in SQL server to get results from differnt schema

Is there a way I can have a table valued function that can query based
  on the schema context?

No. The TVF is bound to the table at the time of creation, not at execute time, so you won't be able to dynamically access different tables from a single function.

  The only possible way I can think of is to create the same table
  valued function in all schemas.

That would be the cleanest method. Another approach is to create a UNION ALL view like the example below and pass the desired schema as a parameter. This will not perform as well since all the referenced tables will need to be scanned.

CREATE VIEW dbo.vw_Festivals
AS
          SELECT 'dbo' AS SchemaName, Col1, Col2 FROM MyDb.dbo.festivals
UNION ALL SELECT 'India' AS SchemaName, Col1, Col2 FROM MyDb.India.festivals
UNION ALL SELECT 'China' AS SchemaName, Col1, Col2 FROM MyDb.China.festivals
UNION ALL SELECT 'USA' AS SchemaName, Col1, Col2 FROM MyDb.USA.festivals;
GO

CREATE FUNCTION getFestivals(@SchemaName sysname)
RETURNS TABLE
AS
RETURN
(
    SELECT Col1, Col2
    FROM vw_Festivals
    WHERE SchemaName = @SchemaName
);
GO


How to safely recreate a MS SQL table that has other objects depending on it?

The simple way around this is to change some options in Management Studio.

Go to Tools, Options, Designers, Table and Database Designers. Uncheck "Prevent saving changes that require table re-creation".

Generally speaking, Management Studio will properly handle dependencies when you modify a table in a way that requires it to be recreated (it will do assorted tricks with temp tables behind the scenes, which you can see if you tell it to generate a change script instead of actually making the changes). However, this may involve modifications/changes to related tables or foreign keys. In other words, don't do this on a live system if you can help it, and make sure you've got backups before you do it.


Do I have to worry that root user can login into MariaDB 10.5 without password

Only if you have only localhost access.
When  the attacker has somehow direct baccess to your server, it isn't that much important, but will still remain a nuisance for the hacker.
When you let access from outside the computer, it is vital that there is a strong password, which lets casual users not gain full access to the hole database.
Overall, yes make a passowrd


Rman not replacing logs with same name on networked drive

If logging with RMAN, you'd have to set the filename yourself - there's no wildcard.  But there is an APPEND option.

spool log to 'my_log_file' append

Similarly, invoking RMAN with the APPEND option causes it to append to any log file specified.

Our backup scripts do the log maintenance at the OS level:

export LOGPREFIX=$ORACLE_ADMIN/$ORACLE_SID/rman/log/my_backup_script
export MYLOG=${LOGPREFIX}.$$   # add the pid to the log file name
rman log=${MYLOG} append <<EOF
... various RMAN commands
EOF

#Perform error checking and notification
fgrep -i RMAN- ${MYLOG}
if [ $? -eq 0 ]
then # we had a failure - email me
   mail -s "$0 : failure for `date +%m/%d/%y`" me@myself_and_i.com < ${MYLOG}
fi
# add the incremental log to the permanent log
cat ${MYLOG} >> ${LOGPREFIX}.log
# clean up the incremental log
/bin/rm -f ${MYLOG}


SELECT
    A.id build_owner,
    COUNT(C.build) build_member_count
FROM member A
INNER JOIN build B ON A.id = B.member       -- or LEFT joins
INNER JOIN up    C ON B.id = C.build        --
GROUP BY A.id ;


Arithmetic overflow error for type varchar

An "Arithmetic overflow" occurs when you are converting data from one data type, ConvertFrom to a different type, ConvertTo and the datatype of ConvertTo cannot store the data coming from ConvertFrom

i.e. ConvertFrom INT has a value of 234,567 which you are assigning to ConverTo TINYINT.

As TINYINT only allows values 0 to 255 you get an "Arithmetic overflow" error.

In your example above you have multiple type conversions that may result in an "Arithmetic overflow" error i.e.

[POSTAL_CODE]      [float]         -> [shp_to_pstl_cd] [char](10)
[PART]             [nvarchar](255) -> [catlg_nbr]      [char](34)
[SHIPPED_QUANTITY] [float]         -> [shpd_qty]       [int]

In the case of POSTAL_CODE, this will only work if the size of the FLOAT field contains no more then 10 digits including the decimal point where as the FLOAT fields supports up to a 53 digit number.

In the case of PART this will only work if the there are 34 characters or less.

In the case of SHIPPED_QUANTITY its a bit more complicated for instance 123456 will convert as will 123456.789 but the data will convert as 123456 loosing the digits after the decimal point which could be critical.

You need to review your table and use common data types for storing common data.


How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

We used replication (no GTID) to replicate from our production 5.5 servers to our new 5.6 servers so to minimize downtime. Basically, set up your new 5.6 server to replicate from the 5.5 (cannot use GTID at this time). Dump 5.5 and restore to 5.6, start the replication.

When you move production to the 5.6 server, stop the replication from the 5.5 server . Reconfigure 5.6 server to use GTID (need to issue RESET MASTER and RESET SLAVE on the 5.6). Then configure your downstream 5.6 slaves.


Best and most reliable way is to use backup / restore

If your database is big and you are on sql server 2008 R2 and up, standard edition also has compression feature to allow you to compress your backups.

Dont forget to sync up users after the restore :-)


Will Postgres perform the computation of calculated columns that aren't selected in a view?

As @Laurenz said, your analysis is correct: the optimiser will avoid evaluating column expressions which do not affect the outcome of the query (and your attempt to force a divide-by-zero error is proof of this).

This depends on which columns you're selecting, but it also depends on the volatility category of the column expressions. The optimiser is free to omit immutable and stable function calls if their output is never used, as they cannot affect the outcome, but volatile functions might have side-effects, so they are not so readily optimised away.

For example:

create function stable_function() returns int as $$
begin
  raise notice 'stable_function() called';
  return 1;
end
$$
language plpgsql stable;

create function volatile_function() returns int as $$
begin
  raise notice 'volatile_function() called';
  return 1;
end
$$
language plpgsql volatile;

create view v as
  select stable_function(), volatile_function();

If only the volatile column is selected:

test=# explain (analyse, verbose) select volatile_function from v;
NOTICE:  volatile_function() called
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Subquery Scan on v  (cost=0.00..0.27 rows=1 width=4) (actual time=0.057..0.057 rows=1 loops=1)
   Output: v.volatile_function
   ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual time=0.056..0.056 rows=1 loops=1)
         Output: NULL::integer, volatile_function()

...then as you can see, stable_function() is absent from the explain output, and the lack of a NOTICE confirms that this call has been optimised away.

However, if the stable column is selected instead:

test=# explain (analyse, verbose) select stable_function from v;
NOTICE:  stable_function() called
NOTICE:  volatile_function() called
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Subquery Scan on v  (cost=0.00..0.52 rows=1 width=4) (actual time=0.139..0.139 rows=1 loops=1)
   Output: v.stable_function
   ->  Result  (cost=0.00..0.51 rows=1 width=8) (actual time=0.138..0.138 rows=1 loops=1)
         Output: stable_function(), volatile_function()

...then we see both column expressions appearing in the plan, and the NOTICEs show that both functions have been executed.

There doesn't seem to be any explicit mention of this behaviour in the docs, so there are no hard guarantees as to whether or not an expression will be evaluated, and you should not rely on any side-effects your function calls might have.

But if your only concern is performance, then as long as you mark your functions as stable or immutable where appropriate, you can be reasonably sure (especially in simple cases like this) that they will not be evaluated unless they're needed.

(And while you're there auditing your volatility declarations, you might want to set the parallel safety flags as well.)


postgresql logical replication -- subscriber size discrepancy

This is to be expected and no problem.

When logical replication starts, it copied the existing data from the source tables. The data in the target tables will then be densely packed and consume less space.

As replication proceeds, the target tables get modified and “dead tuples” will accumulate and be removed by autovacuum. The tables will become slightly “bloated” over time, which is normal and healthy.

Since you don't plan to use logical replication for a long time, the replica will probably never grow as big as the original.


Extended Events: Specify Session Data Storage left blank

If you specify no target, but choose to use an Extended Events wizard template for the the session, it is created with a single histogram target.

You can see this by clicking through to the last page of the wizard, then hitting the "script" button.

This is a sample I created using the "script" button.  Below this is one where I specified a ring buffer as a target:

CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.additional_memory_grant,
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.query_hash)
    WHERE (([package0].[greater_than_uint64]([database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'sqlserver.query_hash')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

This is the one with the ring-buffer:

CREATE EVENT SESSION [asdf] ON SERVER
ADD EVENT sqlserver.additional_memory_grant,
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.query_hash)
    WHERE (([package0].[greater_than_uint64]([database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired',source=N'sqlserver.query_hash'),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

As you can see above, the only difference is the lack of a ring-buffer target in the first result, even though they bot have a histogram target.

However, if you do not use a template when building the Extended Events session, the session is created like so (I just added a single event and action):

CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.additional_memory_grant(
    ACTION(sqlserver.client_pid))
WITH (STARTUP_STATE=OFF)
GO

This results in the session only providing live data, which is viewable via the "Watch Live Data" context-menu of the XE Session:

You can obtain a live stream of the rows returned by the Extended Event by looking at the very lightly documented system function, fn_MSxe_read_event_stream function, as in:

DECLARE @source nvarchar(256);
DECLARE @sourceopt int;
SET @source = N'test';
SET @sourceopt = 0;

SELECT xes.type
    , CONVERT(xml, CONVERT(varbinary(max), xes.data))
FROM sys.fn_MSxe_read_event_stream(@source, @sourceopt) xes

However, the format of the data returned is image, and apparently "not customer readable".


Copy SQL Server database to same server & decrypt by symmetric key return null

Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY

For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.


Password hashes: Fixed-length binary fields or single string field?

There are 2 bits of information. This means 2 fields. It is that simple.

In practical terms, selecting 2 columns is zero extra complexity. Having to read a large field to parse one bit out is unnecessary complexity and performance overhead.

A couple of SO questions on storing salts with hashes

https://stackoverflow.com/q/1219899/27535
https://stackoverflow.com/q/1191112/27535


SQL Server 2016: Temporal table performance vs. Triggers and CDC

It depends on your business case, Temporal tables and change data capture offer different functionality.

Temporal tables are used to provide a version of your table at a point in time. A use case might be a slowly changing dimension where you want to track the changes in dimension attributes and report them from any moment in time.

Change data capture might be used on an OLTP table, to allow you to easily facilitate the export to a data mart. It logs all changes to a separate table, so you can easily view changed rows since your last export LSN point.


Proper way to map IDs: By adding additonal columns for each external column or by inserting new rows

INDEX(external_source, external_id) in that order.  This would facilitate looking up the external id to find the internal one.  Now, assuming the main use of this table is to go from "external" to "internal", then I would do

PRIMARY KEY (external_source, external_id)
INDEX(...)   -- for either `internal_id` or `id`, whichever you keep.

You have two internal ids -- internal_id and id.  Why both?  id could be AUTO_INCREMENT to take care of inventing new numbers.  internal_id would need some mechanism for inventing new numbers.

The external_* may as well be VARCHAR, since you can't trust them to stick to numbers.

I would not do the extra 'normalization' even if you have a million rows in this mapping table.  I might do it for a billion rows.  (I claim there is such a thing as "over-normalization".)


Sounds like you don't have SELECT access on the sequence. Ask the DBA to run:

GRANT SELECT ON SCHEMA_OWNER.MY_SEQ TO APP_USER;

Can you edit your post with the full error message you're receiving?

Regarding your comment. I suspect you've got SELECT ANY TABLE etc privs. These don't apply to sequences, as demonstrated below:

PHIL@PHILL11G2 > create user test1 identified by test1;

User created.

PHIL@PHILL11G2 > grant connect,select any table to test1;

Grant succeeded.

PHIL@PHILL11G2 > create sequence testseq;

Sequence created.

PHIL@PHILL11G2 > conn test1/test1;
Connected.
Loading glogin.sql

Session altered.

TEST1@PHILL11G2 > select count(*) from phil.bigtable;

  COUNT(*)
----------
   2300640

TEST1@PHILL11G2 >

TEST1@PHILL11G2 > select phil.testseq.nextval from dual;
select phil.testseq.nextval from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges


Is innodb_log_file_size not relevant if you are not using transactions in MySQL?

If you are not in a transaction (i.e. if autocommit is 1 and you didn't BEGIN a new transaction), then every query you run implicitly starts its own transaction, and the transaction is committed or rolled back (depending on circumstances, such as when certain errors occur) immediately when the query finishes executing.

In other words, all DML is done in a transaction... it's only a matter of whether it's implicit or explicit.  In InnoDB, everything (except DDL of course) is a transaction.

So, whether you control transactions yourself or not doesn't impact the range of appropriate settings, here.  Like almost all other variables, you should leave innodb_log_file_size at the default value unless you have a specific and valid reason to change it and understand the rationale for the change... and note that the suggestions output by tuning scripts do not constitute a valid reason to change anything. As a rule, stay away from those.

innodb_flush_log_at_trx_commit controls strict ACID compliance, and should be set to 1 if the database is critical (typical setting for master), 2 if it's important (typical setting for replicas) or 0 if you don't care all that much.  It's a little more complex than that, but safest to less safe, the order is 1, 2, 0.  Fastest to slowest is generally the opposite, 0, 2, 1.  2 is a relaxed version of 1 but the difference between 1 and 2 is typically more significant than the difference between 2 and 0.  The impact of this variable is also fundamentally the same whether your transactions are explicit or implicit because, again, everything in InnoDB is a transaction.


Compare last data and second last data sql server based on id

You have what we call a "greatest-n-per-group" problem. Plenty of community members from this stackexchange wrote up generic solutions for that problem here.

One approach for your problem is to use the ROW_NUMBER() window function to assign a number to all of the rows and to pivot the results from columns into rows. One implementation is as follows:

SELECT
  CompanyId
, MAX(CASE WHEN RN = 1 THEN Value ELSE NULL END) Last_Value
, MAX(CASE WHEN RN = 1 THEN Dt ELSE NULL END) Last_Date
, MAX(CASE WHEN RN = 1 THEN Value ELSE NULL END) - COALESCE(MAX(CASE WHEN RN = 2 THEN Value ELSE NULL END), 0) DIFF
FROM
(
    SELECT
      CompanyId
    , Value
    , Dt
    , ROW_NUMBER() OVER (PARTITION BY CompanyId ORDER BY Dt DESC) RN
    FROM #161689
) t1
GROUP BY CompanyId;

To break it down step by step, the t1 derived table returns the following results:

╔═══════════╦═══════╦════════════╦════╗
║ CompanyId ║ Value ║     Dt     ║ RN ║
╠═══════════╬═══════╬════════════╬════╣
║        10 ║    10 ║ 2010-12-01 ║  1 ║
║        10 ║     5 ║ 2010-11-01 ║  2 ║
║        10 ║     2 ║ 2010-10-01 ║  3 ║
║        10 ║     1 ║ 2010-08-10 ║  4 ║
║        20 ║     7 ║ 2010-12-01 ║  1 ║
║        20 ║     3 ║ 2010-10-01 ║  2 ║
║        20 ║     2 ║ 2010-09-01 ║  3 ║
║        20 ║     2 ║ 2010-08-10 ║  4 ║
╚═══════════╩═══════╩════════════╩════╝

You're interested in the rows that have an RN value of 1 or 2. You need to combine the relevant two rows for each CompanyId into a single row. This can be accomplished with GROUP BY. I use MAX to get the latest or second latest as needed. The use of MAX is a bit misleading, since there's always just one row that matches. However, I need to use some aggregate function because this is a GROUP BY query so MAX works fine.

The final results are:

╔═══════════╦════════════╦════════════╦══════╗
║ CompanyId ║ Last_Value ║ Last_Value ║ DIFF ║
╠═══════════╬════════════╬════════════╬══════╣
║        10 ║         10 ║ 2010-12-01 ║    5 ║
║        20 ║          7 ║ 2010-12-01 ║    4 ║
╚═══════════╩════════════╩════════════╩══════╝


Call a stored procedure for every select result

Create another Stored Procedure.  In it, DECLARE a CURSOR for SELECT id FROM view WHERE another_id=100;.  Create a loop; in the loop FETCH one id, then CALL deleteProcedure(id);.  (See the manual and online examples for the details.)


Need help configuring Memcached to store values in MySQL Cluster

The basic docs for using the Memcached API for MySQL Cluster 7.2 start at http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbmemcache.html

On the memcached command line, you need to supply an option like "-E ndb_engine.so".  Memcached has a "pluggable engine" architecture, and the MySQL Cluster component is implemented as an NDB Engine.  When memcached starts up with the correct engine you should see a log message like this:

01-Mar-2012 11:27:27 PST NDB Memcache 5.5.20-ndb-7.2.5 started [NDB 7.2.5; MySQL 5.5.20]
Contacting primary management server


How can you tell when a query plan reuse is causing performance problems

Clearing the plan cache is never a good idea.  You're starting with a cold cache then, and you'll see a performance hit as SQL Server will have to compile everything from that moment on until plans are then cached to be reused.

  He came to this conclusion after noticing performance improved after a reboot.

Oftentimes when I see DBAs reboot, restart the service, and/or clear the procedure cache that gives a really misleading sense of the "fix".  Sometimes, the poor performance can be caused by a poorly chosen execution plan.  By doing any one of the three ways I mentioned above to get an empty cache, you're just forcing SQL Server's hand to compile everything.  Sometimes (although not always) the first query in after that is a typical workload and appears to be better performance....for the typical workload.  This is all rooted in bad parameter sniffing.

Parameter sniffing is a good thing.  But, if the first query that comes into SQL Server uses a parameter set to "Costa Rica", then SQL Server will compile with that parameter value and make an optimal plan for that parameter value.  But if 99.9% of the workload coming in uses the parameter set to "United States", then 99.9% of the workload will be reusing the plan that "Costa Rica" generated.

Fast forward and clear the cache (don't clear the cache, please, I'm just using this as an example).  Now you have a cold cache, and the next query that comes in uses the parameter "United States", and voila!  SQL Server compiles with that parameter value and now 99.9% of the workload is using an optimal plan.

The point I'm trying to get at is DBCC FREEPROCCACHE is like using a bulldozer to shovel snow off of a walkway.  You really should be finding out where the poor performance is and what is causing it.  And then, if it is a particular query that is giving you issues, and you've narrowed it down to a poorly selected plan, there are many potential routes to get around bad parameter sniffing (OPTIMIZE FOR hint, recompile, and a few others).


Transaction log restore fails with error "Operating system error 5(Access is denied.)."

First Error

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device '\\serverX1\d$\trlogs' Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally1

Solution:
It means the service account of SQL Server (which used to start SQL Server service) does not have access to the source.

START > RUN > type SERVICES.MSC.
Find SQL Server Service > Right click and go to PROPERTIES.
Go to LogOn/Security tab

If it's system account (NETWORK SERVICE, LOCAL SYSTEM etc.) then change it to any specific account (domain account like ex. dba\sqlserviceact) and grant the account (ex. dba\sqlserviceact) read & write privilege on the shared folder (network path).

Second Error

Msg 3117, Level 16, State 1, Line 1 The log or differential backup cannot be restored because no files are ready to rollforward. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally

**Solution: **

Verify your differential has Differential Base LSN same as FirstLSN of Full Backup, using below query.

RESTORE HEADERONLY FROM DISK='\\PATH\Full_Backup.BAK'

RESTORE HEADERONLY FROM DISK='\\PATH\Diff_Backup.BAK'

If it's same, then First Restore Full backup with NORECOVERY.

    RESTORE DATABASE DatabaseName
    FROM DISK = '\\PATH\Backup_File_Full.bak'
    WITH NORECOVERY;

Then Restore Differential correspond to the full backup you restored with NORECOVERY

    RESTORE DATABASE DatabaseName
    FROM DISK = '\\PATH\Backup_File_Diff.bak'
    WITH NORECOVERY; --Use WITH RECOVERY if you need to recover data for the point of time

Now if you have Transaction Log backup to restore. Restore all the transaction log backup with NORECOVERY. Only Last T-Log backup (To the point of time you want to recover) will be restored with RECOVERY

To Restore Transaction Log Backup

RESTORE LOG DatabaseName FROM DISK = '\\Path\Backup_File_TLog.bak' WITH NORECOVERY; --If it's the last backup you need to restore use `WITH RECOVERY`

And your problem will be resolved.

Thanks


PostgreSQL: Add or drop column under load give wrong number of columns errors

If your function truly uses SELECT * then I would suggest switching to using an explicit list of fields and then separate out the amendment of the table and the function.

ADDING COLUMNS:
Alter the table first, then change the function once the table is complete.

DELETING COLUMNS:
Change the function first and then the table.  If the table is large and under heavy load I'd still expect problems.


This is my solution
select t.*
from dating_likes t
where exists (select t1.like_id,t1.user_id
              from dating_likes t1
              where t.user_id = t1.liked_user and
                    t.liked_user = t1.user_id)

The output is this :
like_id  User_id   Liked_user
 56       13          11
 54       11          13

I don't know which solution(using Join or Exist) is better in terms of performance but I like to know if anyone wants to share their opinion.
Thanks


Data never expires from shared buffers merely due to the passage of time. It will stay in shared buffers until it gets evicted to make room for some other data, until the PostgreSQL server is restarted, or until the object the data belongs to gets dropped (or truncated).  If the data is getting evicted, it is probably because other activity in the database needs to work with a different part of the data and needs to make room for it.

The amount of effective_cache_size which is more than shared_buffers is not dedicated to the DB in any formal sense.  It is an estimate only, it is not reserved or set aside.  That memory is subject to the OS kernel's eviction algorithm, not PostgreSQL's.


mysql database migration of schemas with updated data and structures

There could be following two ways of doing the same.

First is you take unloads from source using following command

  select * from table_name into outfile 'file_path' FIELDS TERMINATED BY ',' enclosed by '"' ;

Then on target you can do following

  load data local infile 'file_path' into table table_name(Column_list_comma_seprated) FIELDS TERMINATED BY ',' enclosed by '"' escaped by '' ;

Or Else you can try following of inserting data

  Replace into target_table(Column_list) select (Column_list) from source table;

Hope it helps.


Slow Query not using index in one of the tables

SELECT  n.*
    FROM  `reservation_notes` AS n
    INNER JOIN  `reservations` AS r  ON r.`id_hash` = n.`reservation_id_hash`
    INNER JOIN  `contacts` AS c  ON c.`id_hash` = r.`contact_id_hash`
    WHERE  c.`user_id` = 1 */

A guess:  user_id is VARCHAR??  It should be INT (or some other numeric datatype).  And c (Contacts) needs INDEX(user_id).  (Please don't use those awfully long names for indexes; it clutters things unnecessarily.)

That would let the Optimizer start with c and do the filtering (user_id=1) first.

Also, provide INDEX(user_id, id_hash), in this order.  That will make the index "covering".  This means that the processing can be done entirely in the index's BTree, without touching the Data BTree.  This is an extra optimizaiton.

Then r (Reservations) needs INDEX(contact_id_hash, id_hash).  The first column is for reaching into the table.  The extra column is to make it "covering".

Then n (Notes) needs INDEX(reservation_id_hash), which you apparently have.

With these changes, EXPLAIN will list the table in a more optimal order:  c, r, n.  (I prefer to write JOINs in the order that the Optimizer will perform the query.  However, the Optimizer will rearrange the tables as it sees fit.)

When you get millions of rows, you may find that "hashes" slow things down.  But that is another topic.


Calculating percentage of count and adding it to the count in MySQL table with the query

For general case use your original query as derived table:

SELECT cnt + (cnt * 0.75) AS total
FROM (
    SELECT COUNT(order_id) AS cnt
    FROM orders
    WHERE order_date BETWEEN CURDATE() + ' 00:00:00' AND NOW()
) AS tmp

But in your case it is much simpler:

SELECT (COUNT(order_id) * 1.75) AS total
FROM orders
WHERE order_date BETWEEN CURDATE() + ' 00:00:00' AND NOW()


I assume it is the .ibd file that is 20GB, not the .frm?

If you want two physically separate tables you could copy tab1-1 into tab1-2 (so you have two copies) then Delete half the data from each. You'd still need to Optimize both tables afterwards to shrink them.

Using the MySQL partition option you would have one table tab1, which you would partition by some range (e.g. all ID's below 10000 in partition 1, 10001 - 20000 in partition 2 etc.). This would then show up on your filesystem as two (or more) separate files (in the form of tab1.frm and various files like tab1#p#partition1.ibd, tab1#p#partition2.ibd).
It wouldn't give you control over the filesizes however.

In the above example, if all your ID's were below 10000 for instance, it would make no difference to your filesizes.

If you provide the table structure, and mysql version it would help.

UPDATE: 2015-04-13 11:44 (GMT+1):

Looking at your table structure, the first thing I see is that you are going to have problems because you have a Primary key and a Unique Key.

  All columns used in the partitioning expression for a partitioned
  table must be part of every unique key that the table may have.

  In other words, every unique key on the table must use every column in
  the table's partitioning expression. (This also includes the table's
  primary key, since it is by definition a unique key.

http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html

At present, any attempt at partitioning will result in an error:

e.g.

Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

or

Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function

I would recomend looking at some of the following first:

http://dev.mysql.com/doc/refman/5.6/en/partitioning.html
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html
http://mysql.rjweb.org/doc.php/partitionmaint (credit to Rick James for this one)

Also, test this on a non-live server, as it may well take quite some time to build the partitions, which you wouldn't want to do on a Live database.

UPDATE: 2015-04-27 09:52 (GMT+1):

It seems you have some decisions to make. . .

In order to use the Start column, you would need to add start to both the Primary Key (PK) and the Unique Key.

    ALTER TABLE `test`.`cdr`
    DROP PRIMARY KEY
    ,DROP INDEX `id`
    ,ADD PRIMARY KEY (`gid`,`start`)
    ,ADD UNIQUE INDEX `id` (`id` ASC, `prefix` ASC, `start` ASC);

The affect on your PK would be negligible (as long as you DON'T write directly to it - which would defeat the point of the auto-increment anyway).

It would however, potentially cause a problem for your Unique Key, as it would now only be unique across id,prefix,start.

e.g.
insert into test.cdr (id,prefix,start) values (100,'abc','2015-04-27 09:19:00'),(100,'abc','2015-04-27 09:19:01');

will become valid, whereas previously it would have generated a duplicate key error.

so you would need to consider how important that uniqueness is.

If it IS very important, you may want to consider partitioning by id instead, but that depends on what your data looks like as to how easy that would be (and using id,prefix as your PK, and converting gid to a standard (not unique) key.

If it isn't very important, then you could convert the Unique Key to a standard key, and make gid,start your PK.


How to identify the db orphan mdf and ldf files

The files in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL\MSSQL\Binn\Templates are basic copies of the system databases that are needed for repairing and rebuilding your system dbs in case of problems - http://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx

While you could delete them, you'll be in for a longer recovery period if you need to rebuild the system dbs as you'll need to find the templates from somewhere else.

But other than that, that script appears to do what it proclaims to do. Though, it should be updated to:

select
    os.filename as orphaned_files
from
    #os_files os
    left outer join master.sys.master_files db on rtrim(db.physical_name) = os.filename
where
    db.database_id is null

as sysaltfiles is deprecated and will be removed from SQL Server in a future version.


How to copy & paste "Missing Index" in "Execution plan" tab of SSMS?

You can Right-Click on the green text showing the suggested index, select "missing index details" which will open a new tab in SSMS with the index create script.


Uninstall Postgres 10 on macOS – EnterpriseDB installer

postgres user

The user account named postgres (by default) created by the installer is actually a macOS user account.

Apple allows deleting a user account in the more recent versions of macOS: System Preferences > Users & Groups > - button in list, after authenticating with padlock icon in lower corner.
In older macOS versions that do not delete user accounts, you may be able to hide that user account.

Every other trace of Postgres can be removed.

Backup/Export

Of course, first backup your database with a tool like pgBackup. And/or export your data.

Uninstaller app

The installer app provided by EnterpriseDB does install an app named Uninstaller. Find it in the Postgres folder, probably located at the root Library folder (not the Library folder in your home folder).

  /Library/PostgreSQL/10

Run that uninstaller app, and supply your macOS system password. After the run, the data folder remains as that is owned by the postgres user rather than your regular macOS user. The uninstaller app reminds you of this fact.

  /Library/PostgreSQL/10/data

data folder

Select that data folder in the Finder, and choose File > Move to trash. When prompted, provide your own macOS user account password.

Delete that outer Postgres folder(s) that contained data.

Applications folder

Remove the PostgreSQL 10 folder in your Applications folder if it remains. Again, provide your own macOS user account password.


Why am I getting ERROR: duplicate key value violates unique constraint "movies_pkey'

You cannot have duplicate keys on primary key column i.e movie_num=20
 Change Movie Die Another Day with movie_num =20 to 24

   -- DROP'ping tables clear out any existing data
    DROP TABLE IF EXISTS movies;
    DROP TABLE IF EXISTS actors;

    -- CREATE the table, note that id has to be unique, and you must have a name
    CREATE TABLE actors(
        id INTEGER PRIMARY KEY,
        name VARCHAR(20) NOT  NULL
    );

    INSERT INTO actors(id, name) VALUES(
        1,
        'Sean Connery');
    INSERT INTO actors(id, name) VALUES(
        2,
        'George Lazenby');
    INSERT INTO actors(id, name) VALUES(
        3,
        'Roger Moore');
    INSERT INTO actors(id, name) VALUES(
        4,
        'Timothy Dalton');
    INSERT INTO actors(id, name) VALUES(
        5,
        'Pierce Brosnan');

    INSERT INTO actors(id, name) VALUES(
        6,
        'Daniel Craig');

    -- CREATE the table, note movie_num is unique and title and year are required fields, though the actor can be optionally empty
    CREATE TABLE movies(
        movie_num INTEGER PRIMARY KEY,
        title VARCHAR(35) NOT NULL,
        actor INTEGER,
        year INTEGER NOT NULL
    );

    INSERT INTO movies(movie_num, title, actor, year)
    VALUES(
        2,
        'From Russia With Love',
        1,
        1963);
    INSERT INTO movies(movie_num, title, actor, year)
    VALUES(
        3,
        'Goldfinger',
        1,
        1964);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        4,
        'Thunderball',
        1,
        1965);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        5,
        'You Only Live Twice',
        1,
        1967);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        6,
        'On Her Majesty''s Secret Service',
        2,
        1969);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        7,
        'Diamonds Are Forever',
        1,
        1971);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        8,
        'Live and Let Die',
        3,
        1973);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        9,
        'The Man With The Golden Gun',
        3,
        1974);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        10,
        'The Spy Who Loved Me',
        3,
        1977);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        11,
        'Moonraker',
        3,
        1979);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        12,
        'For Your Eyes Only',
        3,
        1981);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        14,
        'A View To A Kill',
        3,
        1985);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        15,
        'The Living Daylights',
        4,
        1987);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        16,
        'License To Kill',
        4,
        1989);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        18,
        'Tomorrow Never Dies',
        5,
        1997);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        24,
        'Die Another Day',
        5,
        2003);  -- changed movie_num from 20 to 24*/
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        1,
        'Dr. No',
        1,
        1962);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        13,
        'Octopussy',
        3,
        1983);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        17,
        'Goldeneye',
        5,
        1995);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        19,
        'The World Is Not Enough',
        5,
        1999);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        20,
        'Casino Royale',
        6,
        2006);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        21,
        'Quantum of Solace',
        6,
        2008);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        22,
        'Skyfall',
        6,
        2012);
    INSERT INTO movies(movie_num, title, actor, year) VALUES(
        23,
        'Spectre',
        6,
        2015);


How to retrieve subtotal for order lines (rollup does not exist in sql 2000)

Figured it out:

select TS.Order_no, part_no, cost_per_line,
       case when Order_no = (select top 1 Order_no from #tempsales
                           where cust_po = TS.cust_po
                          order by Order_no asc)
            then (select sum(cost_per_line)
                     from #tempsales
                     where Order_no <= TS.Order_no
                        and cust_po = TS.cust_po)
            else '0' end as 'Sub Total',
       case when Order_no = (select top 1 Order_no from #tempsales
                           where cust_po = TS.cust_po
                          order by Order_no asc)
            then (select CAST(ROUND(SUM(cost_per_line), 2, 1) AS DECIMAL(18, 2))
                      from #tempsales)
             else '0' end as 'Grand Total'
from #tempsales TS


Lockdown database access on certain tables

There's no need to bother with certificate signing of stored procedures.
If I'm reading your question correctly, your requirements are essentially this:

All data is in one SQL Server Database
All authentication is done via a domain group, which most individuals are part of
End users primarily access data through Excel, but do use other tools
For most data, it is OK for users to freely query the tables directly
For some data, users should not be able to read the raw data directly, but should instead only use approved queries

The last requirement is the change you're looking to implement, for which you are considering cert-signed stored procedures.
The answer is in stored procedures, but there should be no need to bother with cert-signing.
Ownership chaining!
SQL Server allows for ownership chaining, which essentially means if you GRANT EXECUTE on a stored procedure, the procedure will be able to access the data it needs, and return an accurate result, even if the user doesn't have direct access to the data.
An example
First, lets set up a database, a SQL Login (though, in your case, you'd just use your domain group), and some data.
--Create a database to test with
CREATE DATABASE TestingPermissions;
GO

--Create a login to test with
CREATE LOGIN ReadOnlyUser WITH PASSWORD = 'SuperDuperSecure!';
GO

--And create a corresponding user
USE TestingPermissions
GO
CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyUser;

--And a couple tables
CREATE TABLE dbo.PublicData (
    PublicId int,
    PublicText varchar(100)
    );

CREATE TABLE dbo.PrivateData (
    PublicId int,
    PrivateId int,
    PrivateText varchar(100)
    );

--And some data
INSERT INTO dbo.PublicData (PublicId, PublicText)
SELECT object_id, name
FROM sys.objects;

INSERT INTO dbo.PrivateData (PublicId, PrivateId, PrivateText)
SELECT object_id, column_id, name
FROM sys.columns;

Now, rather than adding the user to the db_datareader fixed role, we'll grant data explicitly to the table(s) with data they can access directly:
-- And only grant permissions to the one public table!
GRANT SELECT ON dbo.PublicData TO ReadOnlyUser;

And when we query that data as the login we created. We'll get results when we query the public table that we granted SELECT on, and we'll get an error for the private table we have no permissions on.
--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
--This will error
    -- Msg 229, Level 14, State 5, Line 43
    -- The SELECT permission was denied on the object 'PrivateData', database 'TestingPermissions', schema 'dbo'.
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Now, we'll create a stored procedure which does that second query within the procedure, and we'll grant EXECUTE on that procedure to the limited access login.
--We create a stored procedure & Grant execute to the read-only user
CREATE OR ALTER PROCEDURE dbo.GetMyData
AS
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetMyData TO ReadOnlyUser;
GO

Now, ReadOnlyUser has EXECUTE permission on the procedure that access dbo.PrivateData, but doesn't have access to query dbo.PrivateData directly.
--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
EXEC dbo.GetMyData;
--This will error with the same error as before
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Cross-Database problems
Ownership chaining won't cross database boundaries, so in the above example, if dbo.PrivateData were in a separate database, that EXEC dbo.GetMyData would actually return a permissions error. Cross-database ownership chaining can be enabled, but brings with it some security risks, but we can still avoid it.
A more complicated example
Continuing where the last example left off, lets move dbo.PrivateData into a different database
--Put all the private data in one database
USE master
CREATE DATABASE RestrictedAccess;
GO
--And create a corresponding user
USE RestrictedAccess
GO
CREATE USER ReadOnlyUser FROM LOGIN ReadOnlyUser;

--And move dbo.PrivateData into this database
CREATE TABLE dbo.PrivateData (
    PublicId int,
    PrivateId int,
    PrivateText varchar(100)
    );
INSERT INTO dbo.PrivateData (PublicId, PrivateId, PrivateText)
SELECT PublicId, PrivateId, PrivateText
FROM TestingPermissions.dbo.PrivateData;

--Now that its moved, Drop the table in the other DB
DROP TABLE TestingPermissions.dbo.PrivateData;
--And drop the procedure that we broke
DROP PROCEDURE IF EXISTS dbo.getMyData;

And we can create a stored procedure that gives access to just that aggregated data, without accessing the raw data directly. We grant execute to this procedure. In real life, this might be a bit more complicated, and probably would accept some parameters to limit the data.
CREATE OR ALTER PROCEDURE dbo.GetPrivateData
AS
SELECT priv.PublicID,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PrivateData AS priv
GROUP BY priv.PublicID;
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetPrivateData TO ReadOnlyUser;
GO

And we can alter the original GetMyData procedure to call this GetPrivateData procedure, rather than grabbing the data directly. Now, inside GetPrivateData, I'm doing an INSERT..EXEC... to put the data into a temp table, and then join to the temp table.
USE TestingPermissions;
GO
CREATE OR ALTER PROCEDURE dbo.GetMyDataWithInsertExec
AS

CREATE TABLE #PrivateData (
    PublicID int,
    PrivateCount int
    );
INSERT INTO #PrivateData (PublicID, PrivateCount)
EXECUTE RestrictedAccess.dbo.GetPrivateData;

SELECT TOP 1
       pub.PublicText,
       priv.PrivateCount
FROM dbo.PublicData AS pub
JOIN #PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GO -- make sure you have this GO before the grant!

GRANT EXECUTE ON dbo.GetMyDataWithInsertExec TO ReadOnlyUser;
GO

And when we test it as our user, we'll see that the procedure gives access, and querying the data directly gives an error.
USE TestingPermissions;
GO
--Run this as the ReadOnlyUser
EXECUTE AS LOGIN = 'ReadOnlyUser';
--This will work
SELECT TOP 1 * FROM dbo.PublicData;
EXEC dbo.GetMyDataWithInsertExec;
--This will error with the same error as before
SELECT TOP 1
       pub.PublicText,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PublicData AS pub
JOIN RestrictedAccess.dbo.PrivateData AS priv
    ON pub.PublicId = priv.PublicId
GROUP BY pub.PublicText;
GO

--Switch back to our sysadmin credentials
REVERT;
GO

Views work too.
Ownership chaining can work with views, too. In all the places where I used a procedure to "cover" direct data access, I could have used a view similarly. There are differences & trade-offs. In general, views give users more access to go after the data in new & interesting ways, where stored procedures give you more control. This also means that stored procedures can give more protection to ensure data is only accessed in "approved" ways. Depending on what your controls are, and how you need to balance those things, you might find that the right thing for you could be a view, or a stored procedure.
Here's an alternate example using a view:
--Create a view to get Private data
USE RestrictedAccess
GO
CREATE OR ALTER VIEW dbo.SummaryPrivateData
AS
SELECT priv.PublicID,
       PrivateCount = COUNT(DISTINCT priv.PrivateID)
FROM dbo.PrivateData AS priv
GROUP BY priv.PublicID;
GO -- make sure you have this GO before the grant!

GRANT SELECT ON dbo.SummaryPrivateData TO ReadOnlyUser;
GO

--Then just query that view from the public database
USE TestingPermissions;
GO
SELECT TOP 1
       pub.PublicText,
       priv.PrivateCount
FROM dbo.PublicData AS pub
JOIN RestrictedAccess.dbo.SummaryPrivateData AS priv
    ON pub.PublicId = priv.PublicId

No cert signing necessary
It's just a matter of making your grants granular enough that you specifically grant access to the things people should have access to, and don't grant access directly if the user shouldn't have direct access. Stored procedures, views, and functions can be used as the abstraction layer, to give access indirectly to data.


new to PL SQL here, and wanted to help with what i know...

A record is similar to a table row, can hold one value for each column at a time.
cursors are like "tables" they may hold multiple rows (records if you will).

this is one way how you can declare a record:

DECLARE
TYPE myRecType
IS
RECORD
(
  [COLUMN NAME] [DATA TYPE],
  .
  ..
  );

Cursors can be explicit or implicit.
Implicit cursors are created each time you run a query, and have some attributes that may give you an idea of what happened after your query is ran.
e.g.

SQL%FOUND will return either TRUE or FALSE if your DML statement affects at least 1 record.

SQL%ROWCOUNT will return the number of rows afected by your DML statement...

Explicit cursors on the other hand, are needed for advanced data manipulation, you can create your own cursor by supplying a query, and you can iterate through that cursor to do more data manipulations

simple syntax to declare a cursor with a SELECT query and loop through the results:

Declare
  cursor Mycursor is
    SELECT
      ColA,
      ColB,
      Colc
    FROM MyTable;
Begin

   -- Iterate using a for loop
  for X in Mycursor loop
    --print Mycursor.ColA
    -- do whatever you want to do with your data
  end loop;
end;


Check and see if these temporary reports show up through the web service interface. You should be able to setup a powershell script to call the deleteitem web service method to delete the reports.
That or unhide them in the report manager interface and manually delete them.
Those would be the supported ways to do it.


Why Java Pool is not a required component of Oracle SGA?

Java inside the database is optional. If it is not installed, why waste memory on it?

Since 11.2.0.4, it is possible to "disable" it if patch 20681008 is installed, with the below parameter:

"_bug20681008_force_java_pool_zero" = true

Starting with 12.2, the below can be used:

"_force_java_pool_zero" = true


I need help optimizing a not that complicated query that returns unexpedcted performance problems

My bet is on creating the following indexes:

(incoming, targetNumber, dateCreated)
(sourceNumber, incoming, dateCreated)

But you have to rewrite your query to do:

select *
from tesis.call oc --outgoing calls
join tesis.call ic on
    oc.sourceNumber = ic.targetNumber
    and ic.dateCreated > oc.dateCreated
    and (ic.dateCreated - interval 30 second) <= oc.dateCreated --incoming calls
where oc.incoming = 0
    and ic.incoming = 1
    and oc.neighborhood_id is not null

This seems better than doing the JOIN in this direction oc -> ic, where the indexes would be:
(incoming)
(targetNumber, dateCreated)

Please note that this is long shot, as I do not know the cardinality of the conditions, I can only imagine them. Please test it and show the results of EXPLAIN to check it.


Windows Clustering and SQL Availability Groups

How can all 4 servers have access to this drive, when I have one server in a different location on a different subnet?

Get really friendly with your network and security teams. Ask them, politely if need be, to create a file share for that folder and allow your service accounts access to said file share.

Other than the service account, this really doesn't have any involvement with SQL Server.


can mysql cli exit with error if no records returned?

Try changing the query to force a value

Instead of

SELECT id FROM table WHERE id=${LOOKUP}

you can use

SELECT COUNT(1) FROM table WHERE id=${LOOKUP}

Here is bash script to use that new query

LOOKUP=123
SQLSTMT="SELECT COUNT(1) FROM table WHERE id=${LOOKUP}"
RCOUNT=`mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}"`
if [ ${RCOUNT} -eq 0 ]
then
    echo "Row ${LOOKUP} exists"
else
    echo "Row ${LOOKUP} does not exist"
fi

Here is an alternative

LOOKUP=123
SQLSTMT="SELECT CONCAT('Row ',rcount,' ',IF(rcount=0,'Exists','Does Not Exist')) FROM (SELECT COUNT(1) rcount FROM rolando WHERE id=${LOOKUP}) A"
mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}" > /tmp/answer.txt
cat /tmp/answer.txt

Give it a Try !!!


MySQL consistent nonlocking reads vs. INSERT ... SELECT

From the MySQL Documentation

The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:
By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
To use a consistent read in such cases, enable the innodb_locks_unsafe_for_binlog option and set the isolation level of the transaction to READ UNCOMMITTED, READ COMMITTED, or REPEATABLE READ (that is, anything other than SERIALIZABLE). In this case, no locks are set on rows read from the selected table.

Evidently, you do not need stronger locks when just doing reads. Picture copying data into a table using INSERT ... SELECT or CREATE TABLE ... SELECT. The SELECT needs to be a frozen snapshot for loading the table. If it were a moving target, that would require a transaction within a transaction. If you wanted transaction within a transaction behavior, you would need to script that using SAVEPOINT. Otherwise, stronger locks allow for consistent SELECTs for an atomic INSERT.
Doing the simple SELECT allows your DB Session to trust other transactions not to play smoke-and-mirrors with your view of the data. If it has to violate that trust, it will politely lock every row it can to do its work, rather than deceive DB Sessions that are only doing SELECTs into thinking the data is stable when it is, in fact, changing.
No matter which isolation level you pick, none of them is so granular that a simple SELECT gets held up. That's why SELECT ... FOR UPDATE and SELECT ... FROM ... LOCK IN SHARE MODE were invented, so you can be that granular if politeless is not an option.
I wrote about this before : How can I make a select statement get blocked?


How to swap array values in PostgreSQL.?

You can access the values by index, as follows:

postgres=# create table intarray ( aaa int8[] ) ;
CREATE TABLE
postgres=# insert into intarray values ( ARRAY [ 111, 222, 333 ]  ) ;
INSERT 0 1
postgres=# select * from intarray;
      aaa
---------------
 {111,222,333}
(1 row)

postgres=# select aaa[3],aaa[2],aaa[1] from intarray;
 aaa | aaa | aaa
-----+-----+-----
 333 | 222 | 111
(1 row)

postgres=# select ARRAY[aaa[3],aaa[1],aaa[2]] from intarray;
     array
---------------
 {333,111,222}
(1 row)

postgres=#

Reading the documentation may help you.


If T2 is derived data based on the contents of T1, then you may want to replace T2 with a materialized view.  If T2 is rarely accessed, and quick to derive, then you may just want to use a view.

On the application side, if you stop maintaining table T2, then the values it gets will become increasing incorrect.  This can be partially corrected by recalculating the derived values and updating T2 with the new values.


Pagination with PostgreSQL 9.3: counting number of pages

Index definition
Your multicolumn index generally looks good. If all or most of your queries use order by score desc, id desc, it would be a bit more efficient to define it with matching sort order and a simpler condition:
CREATE INDEX prod_category_score_id ON products(category, score DESC, id DESC)
WHERE active;

WHERE active = TRUE is just a more complicated way of saying WHERE active.
While Index Scan Backward is practically as fast as a plain index scan, access is simpler with matching sort order in multicolumn index.

Why the extra Bitmap Heap Scan?
Due to the MVCC model of Postgres it needs to make sure that entries in the index are actually still alive in the table.
The planner method Index Scan works best for a small number of rows. Postgres takes each row pointer from the index and fetches the row from the heap (the table) sequentially, thereby also checking if the row is actually visible to the current transaction.
With a growing number of hits, this gets less effective. If multiple rows reside on the same data page, it's more efficient to collect tuples and visit the same page only once. That's what a Bitmap Index Scan does essentially (among other things).
When counting rows with count(*), Postgres wouldn't need data from the heap. But it still must verify that each tuple is visible and actually counts. The Wiki page about "Slow counting".
Postgres 9.2 introduced Index Only Scans. If the "visibility map" says all tuples of a page are visible to all current transactions, Postgres does not have to visit the heap to verify. That would be faster for a query such as yours, that only counts a small percentage of rows, most efficiently determined by using the index.
Obviously, you have a lot of concurrent write operations. Or your autovacuum settings are not aggressive enough. Else, you might see a faster Index Only Scan.
The Postgres Wiki about that.


PLS-00306 Error: How to find the wrong argument?

No, there's really no shortcuts here.  Examine things in the following order:

Check the procedure name.
Check the number of parameters.
Check the types of the parameters.
Check the parameter names.


SQL - Set a Flag Based on a Conditions Based on Multiple IDs

Something like this may be

select t.*,
  case sum(case when test in ('a','b') then 1 else 0 end) -- count occurrences of tests 'a' and 'b'
       over (partition by primary_id) -- for each primary_id
  when 2 then 1 else 0 end flag  -- if there are two, we're OK
from test t

fiddle


Methods of Denying login by specific SQL Authenticated user using Management Studio

Connecting to management studio should not be that threat to you unless and until that user gets full read/write on the database in question which needs to be secured.

You can use DENY VIEW ANY DATABASE Permission herewhich will make the DB sort of invisible for that user when he tries to connect via SSMS, however if they are smart enough and hit a query with USE <database name> they can proceed.

Else, its better you just give them access via windows authentication (with controlled access) and do not share any SQL login credentials which have the permission to execute the task which you do not want the user to.

Also, read this thread


Combine two table with different column but same value

You should be able to use a simple INSERT/SELECT FROM.

INSERT INTO TABLE_A (
    a_empno
    ,a_checktime
    ,a_updatetime
    ,a_status
    ,a_location
    )
SELECT b_empno
    ,b_checktime
    ,b_updatetime
    ,b_status
    ,b_location
FROM TABLE_B

If your goal is to combine the results from the two tables into another table, you should do as McNets suggests in his comments and use either UNION or UNION ALL.

SELECT a_empno
    ,a_checktime
    ,a_updatetime
    ,a_status
    ,a_location
FROM TABLE_A
UNION   --OR UNION ALL IF YOU WANT TO KEEP DUPLICATES
SELECT b_empno
    ,b_checktime
    ,b_updatetime
    ,b_status
    ,b_location
FROM TABLE_B


Add function parameter to SQL query WHERE clause

If you don't always pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL:

CREATE OR REPLACE FUNCTION func(_a int  = NULL
                              , _b text = NULL
                              , _c text = NULL)
  RETURNS TABLE(...) AS
$func$
    SELECT ... FROM ...
    WHERE (speed = $1 OR $1 IS NULL)
    AND   (stop  = $2 OR $2 IS NULL)
    ...
$func$  LANGUAGE sql;

Now you can call the function with any number of parameters using named notation:

SELECT * FROM func(_c => 'foo', _a => 123);  -- no _b, which defaults to NULL

Note, the assignment operator in the call is => (or := for Postgres 9.4 or older), not =!
See:

The forgotten assignment operator “=” and the commonplace “:=”
Functions with variable number of input parameters

Aside: "string" is not a data type, text is.

Much more is possible with dynamic SQL with EXECUTE in a plpgsql function.

More comprehensive answer on SO with complete recipes:

Test for is null or = value in function


First identify the entities in your design (in hierarchical order, i.e. in dependency):

Department
User
VacationRequest

Then identify the relationships:

Each Department have one or more User
Each User can have 0 or more VacationRequest

Then the business rules:

Each Department must have one responsible and one deputy.
If the responsible of the department have a confirmed vacation then in the same period then the deputy can't have a confirmed vacation (and viceversa)

How the rules could be implemented:

Create the tables Department, User, VacationRequest.
In the user table assign the user as responsible of his own department or deputy.
Each time you enter a new VacationRequest asign it with the Confirmed field on false.
Each time you confirm a Vacation via a stored procedure you must check if the vacations not colide with the deputy or viceversa (also you must check if there is a deputy effectively)

I hope it would help you.


How can I see the current Database Mail configuration?

+1 to @jsauni for the wizard

You can also use the following system functions.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

The wizard is probably easier to follow but these will give you everything at once without having to go back and forth through the wizard.  I find it a bit cumbersome. :)


Row numbering difference between mariadb and mysql

From a logical perspective, ORDER BY is applied after (@row := @row + 1), so which row gets which number is non-deterministic. However, there may be something in the documentation, that guarantees that assignment of @row respects the ORDER BY. In that case, you should file a bug, if not I would not trust that it does.
I really don't understand why you are developing against an ancient DBMS, and running a much newer version in production, I think you should upgrade your local DBMS so that you have the same version.
Upgrading and using window function will get you out of trouble:
SELECT row_number() over (order by qry.used_present DESC, qry.brand ASC) as score,
    qry.brand,
    qry.model,
    qry.used_present
FROM (
   SELECT cars.brand,
          cars.model,
          (used_cars.price IS NOT NULL) AS used_present
   FROM cars
   LEFT JOIN used_cars
       ON used_cars.model = cars.model
      AND used_cars.brand = cars.brand
) AS qry
ORDER BY qry.used_present DESC
       , qry.brand
;


My MySQL server time is not the same as my Server

I had this exact same problem after I had updated my server time. I just had to restart the MySql server then it reset the time to match my server time.

/etc/init.d/mysqld restart

Reference - https://major.io/2007/07/01/mysql-time-zone-different-from-system-time-zone/


One-to-many relationship on a “‘junction’ table”

You can build a unique/primary key in the Workout Exercise table that can be referenced by Sets.  That will maintain referential integrity and minimize redundancy, assuming that the surrogate key made up of key values from workout and exercise is unique, and you can create one primary key on sets that has that foreign key referential integrity to the associative (junction) table.


Oracle Transportable Database export ORA-39340: unsupported object, INDEX

ORA-39043 Or ORA-39340 May Be Raised During Full Transportable Export of the Database (Doc ID 2365679.1)

  This issue is currently investigated in bug 27244546.

  When encountering this problem, recreate the text index after
  importing into the new database.


GROUP BY can not use aliases defined in the same query block.
You can use this:
SELECT
flow.flowid,
min(flow.CONTEXTTIMESTAMP) contextTime,
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
GROUP BY flow.flowid, flow.STATUT,flow.ETAT, DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1),
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001)
ORDER BY contextTime desc

Or something like this:
SELECT
flowid,
min(CONTEXTTIMESTAMP) contextTime,
STATUT,
idf1,
idf2,
ETAT
from
(
SELECT
flow.flowid,
flow.CONTEXTTIMESTAMP,
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow
WHERE  flow.FLOWCODE = 'HELLO'
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
)
GROUP BY flowid, STATUT,ETAT, idf1 ,idf2
ORDER BY contextTime desc


Finding records not present across 3 tables - Sqlite3

"Find all the Users of a particular workspace that are not associated with a specific task"

You need to move the AND user_task.task_id=? condition from the WHERE clause to the related ON.

(I think you also have a typo. ON user_task.task_id = Task._id should be ON  user_task.user_id = User._id):

SELECT User.*
FROM User
  INNER JOIN user_workspace
    ON  user_workspace.user_id = User._id
  LEFT OUTER JOIN user_task
    ON  user_task.user_id = User._id
    AND user_task.task_id = ?                <-- moved here
WHERE user_workspace.workspace_id = ?
  AND user_task.user_id IS NULL
ORDER BY User.name ASC ;

You could also use NOT EXISTS. I find the code more readable:

SELECT User.*
FROM User
  INNER JOIN user_workspace
    ON  user_workspace.user_id = User._id
WHERE user_workspace.workspace_id = ?
  AND NOT EXISTS
      ( SELECT *
        FROM user_task
        WHERE user_task.user_id = User._id
          AND user_task.task_id = ?
      )
ORDER BY User.name ASC ;


SQL developer - procedure just for a session

The closest thing you can do is defining procedures in anonymous blocks, for example:

Declare
    val number;
    Procedure P1(abc in number, def out number) is
    begin
         def := abc*abc;
    end;
begin
    For i in 1..10 loop
    P1(i,val);
    dbms_output.put_line(val);
    End loop;
end;
/

Another feature is defining PL/SQL functions for a single SQL call in 12c.

Using a PL/SQL Function in the WITH Clause

For example:

WITH
 FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
   pos BINARY_INTEGER;
   len BINARY_INTEGER;
 BEGIN
   pos := INSTR(url, 'www.');
   len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
   RETURN SUBSTR(url, pos + 4, len);
 END;
SELECT DISTINCT get_domain(catalog_url)
  FROM product_information;
/


Size of data in secondary exceeded primary after its initial resync from primary in MongoDB

Does the same data take more storage space in version 2.6?

As noted in the MongoDB 2.6 release notes, the default storage allocation strategy changed from using exact-fit allocation with padding factor to usePowerOfTwoSizes. The powerOfTwoSizes allocation may take up more initial space, but generally results in less storage fragmentation and better reuse of space from deleted documents.

One notable exception is if you are using GridFS and have data saved with older drivers. The original GridFS chunk size was 256KB (an exact power of 2) which resulted in rounding up to 512KB (the next power of 2). Drivers were updated to lower the GridFS chunk size to 255KB, but this is a common reason your database size to grow if you resync with the powerOf2 allocation (see SERVER-13331 in the MongoDB issue tracker).

Changing the allocation strategy

In MongoDB 2.6 you can change the allocation strategy on a collection level using the collMod command, or on a server level with the newCollectionsUsePowerOf2Sizes configuration parameter. If you change the allocation strategy for an existing collection, this will only affect new documents created or moved in storage after the change. You would have to resync, compact, or repair the database to rewrite all documents with new allocations.

If you are using GridFS and have older documents with the 256K chunk sizes, it would make sense to change the allocation strategy for any gridfs chunks collections to not use PowerOf2Sizes. Similarly, if your data use case for a collection happens to be insert-only (or does not grow in size via updates) then the exact fit allocation will be more efficient.

Otherwise, the powerOf2Sizes allocation is recommended.

WiredTiger storage engine supports compression in MongoDB 3.0+

If storage size on disk is a concern, you should also consider testing the new WiredTiger storage engine in MongoDB 3.0, which includes support for index & data compression. Upgrading to MongoDB 3.0 & WiredTiger will require you to use updated drivers & tools, and as with any major version upgrade you should review the upgrade procedures relevant for your deployment as well as any compatibility changes.


I am hesitant to create a job that takes full backups as I do not want to break the log shipping chain so here is where I need some advise on how to control my log sizes.

This is a misconception/myth. A fullbackup will never break the log backup chain. Only an adhoc NON COPY_ONLY backup will break the log backup chain.

In full recovery, only a full backup will truncate the log.

  ran a log_reuse_wait_desc and there is nothing holding the log

Check your autogrowth settings. Make sure it is not set to Percent autogrowth and the MB autogrowth is sensible.

You can check my script to check.

Also, you should check : Why Does the Transaction Log Keep Growing or Run Out of Space?


Windows Server and SQL Server updates and patching - and how to rollback (revert) if necessary

What's the correct order of installing updates - first Windows, and
  then SQL Server ? vice versa ? Or the order does not matter ?

There is unlikely to be any prerequisite for a Windows update that requires specific SQL Server updates to be applied because not all Windows Servers run SQL Server, however, it is possible that you may need a specific Windows update for a given SQL Server update (none spring to mind but it is conceivable). So given this, applying Windows Updates first is probably your best bet.

NOTE: Windows updates often set the restart pending flags and this can cause SQL Server update installers to fail the prerequisite checks. You need to orchestrate your patching process to handle restarts between applying patches if required.

  How do I know if my Windows or SQL Server update failed? Is it
  possible that any of updates can fail and bring server to an unusable
  state?

While it is possible for a SQL Server update to cause a fault that makes SQL Server unusable, it isn't too common. There are examples of it occurring though, check out Aaron Bertrand's blog article on updates for older SQL Server versions to support TLS 1.2 - SQL 2008 and 2008 R2 had a number of early issues causing service interruptions once the TLS 1.2 patch was applied.

For confirming the outcome of your SQL update installation, you need to review the setup log files which will provide a summary status that should confirm if the installation was successful. Generally, you want to check the summary.txt file and confirm the outcome is reported as "Passed" or "Passed, but requires reboot".

For Windows updates, you want to review the CBS.log file for Windows Update installations.

  How to rollback (revert) Windows or SQL Server update if it was
  unsuccessful (or even broke the system)?

Typically, updates can simply be uninstalled from the server to revert to the previous state. This can be done via Programs and Features (for SQL Updates, for SQL 2008+) and Windows Update Center (for Windows updates)

In the case of fatal errors preventing the server from booting, you can take a snapshot of the VM prior to applying updates that you can use for rollback.


How to view security permissions for encryption keys in SQL server

Without going into a long discussion about certificates and keys, I'm just going to give you what you need to create some keys, grant some access to them and then review the access granted.

I've included certificates because your keys depend upon them if you chose ENCRYPTION BY CERTIFICATE.

USE SOME_DATABASE;
GO

-- assuming you have a login testuser

CREATE USER testuser FOR login testuser
GO

--Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my_Strong_Password'
GO

CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'

-- Grant control on certificate to testuser
GRANT CONTROL ON CERTIFICATE::MyCertificateName TO testuser

-- create a SYMMETRIC key
CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,
KEY_SOURCE = 'a very secure strong password or phrase'
ENCRYPTION BY CERTIFICATE MyCertificateName;

-- grant control on symmetrick key to testuser
GRANT CONTROL ON SYMMETRIC KEY::MySymmetricKeyName TO testuser;

-- create ASYMMETRIC key
CREATE ASYMMETRIC KEY MyASymmetricKeyName
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = 'my_Strong_Password';
GO

-- grant control and alter on asymmetric key to testuer
GRANT CONTROL ON ASYMMETRIC KEY::MyASymmetricKeyName TO testuser;
GRANT ALTER ON ASYMMETRIC KEY::MyASymmetricKeyName TO testuser;

-- display permissions for those certificates and keys we just created

declare @sql as nvarchar(max)
declare @db_name as nvarchar(max) = 'some_database'

SET @sql = '
SELECT ''' + @db_name + ''' as database_name,
CASE dperms.state_desc
WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT''
ELSE state_desc
END
+ '' '' + permission_name + '' ON '' +
CASE dperms.class
WHEN 0 THEN ''DATABASE::['' + DB_NAME() + '']''
WHEN 1 THEN
CASE dperms.minor_id
WHEN 0 THEN ''OBJECT::['' + sch.[name] + ''].['' + obj.[name] + '']''
ELSE ''OBJECT::['' + sch.[name] + ''].['' + obj.[name] + ''] (['' + col.[name] + ''])''
END
WHEN 3 THEN ''SCHEMA::['' + SCHEMA_NAME(major_id) + '']''
WHEN 24 THEN ''SYMMETRIC KEY::['' + symm.[name] + '']''
WHEN 25 THEN ''CERTIFICATE::['' + certs.[name] + '']''
WHEN 26 THEN ''ASYMMETRIC KEY::['' + asymm.[name] +'']''
END
+ '' TO ['' + dprins.[name] + '']'' +
CASE dperms.state_desc
WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION;''
ELSE '';''
END COLLATE database_default AS ''Permission''
FROM [' + @db_name + '].sys.database_permissions dperms
INNER JOIN [' + @db_name + '].sys.database_principals dprins
ON dperms.grantee_principal_id = dprins.principal_id
LEFT JOIN [' + @db_name + '].sys.columns col
ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id
LEFT JOIN [' + @db_name + '].sys.objects obj
ON dperms.major_id = obj.object_id
LEFT JOIN [' + @db_name + '].sys.schemas sch
ON obj.schema_id = sch.schema_id
LEFT JOIN [' + @db_name + '].sys.asymmetric_keys asymm
ON dperms.major_id = asymm.asymmetric_key_id
LEFT JOIN [' + @db_name + '].sys.symmetric_keys symm
ON dperms.major_id = symm.symmetric_key_id
LEFT JOIN [' + @db_name + '].sys.certificates certs
ON dperms.major_id = certs.certificate_id
WHERE dperms.type <> ''CO''
AND dperms.major_id > 0
and dperms.grantee_principal_id > 4
and dperms.class in (24, 25, 26)
;'

print @sql
execute(@sql)

Aside from the service master key which is created automatically and stored in the master database, the master key, certificate and symmetric, asymmetric keys would be stored in the databases you created them in.


Maintainability of schema changes during runtime of an app

If the data is going to have many different layouts, you might be overthinking it.

Many DBMSs have great support for JSON now. You can store it natively as JSON while defining a view on top of JSON data that exposes it to other users as a regular view with columns.

https://www.postgresql.org/docs/current/static/datatype-json.html

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server


If the following command is run by someone with the authority to do so, the trace will be stopped.

exec sp_trace_setstatus @traceid=<trace_id>, @status=0 -- Stop the trace

However it still exists in the stopped state.  This is the state you are reporting as 'Paused'.  If the stopped trace should be ended, then a second command is needed:

exec sp_trace_setstatus @traceid=<trace_id>, @status=2 -- Close the trace

This removes the stopped trace from the server.  Once the trace is stopped and closed you can read the full trace.

Of course, if the trace is to be periodically restarted, then you can leave it in the stopped state until the next activation.


Postgres query column with comma-delimited strings for array of strings

You need to convert the comma separated values to an array, then you can use the overlaps operator &&

select *
from pubnet
where string_to_array(oe, ',') && array['F3DH19860AA', 'F4DHAA']


This is normal. T-SQL does not generally specify an evaluation order. Intuitively you would evaluate the where clause first, but that does not have to be the case. SQL Server decided against that which is entirely valid.

Currently, SQL Server does not offer a construct to be 100% sure never to hit this problem. There is a 99% sure technique though: Wrap a case expression around anything that is dangerous:

case when DonNo like '%-%' then (/* convert */) end

This works almost always except in very few corner cases documented on Microsoft Connect. I don't have them at hand right now.


Truncating MySQL table and insert newly records on daily basis

Instead, do

CREATE TABLE new LIKE real;
load new data into `new` by whatever means
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Notes:

The second step is the only slow step.
The RENAME is atomic.
You can continue to read the table during the process.
Do not use any transactions or table locks.


Is it possible to restore a MySql database only using files from Data directory?

InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.

Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)

You cannot recover just a database. You must restore the entire data folder c:/ProgramData/Mysql/Mysql Server 5.5/data. Please see my old post restore table from .frm and .ibd file? on reattaching .ibd files to the data dictionary.

If the database folder has no .ibd files but only .frm files, then all the data are contained within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.

You may have mangled the data dictionary. You have to do a complete restore of your datadir or mysqldump from an original database, shutdown mysqld, delete all of datadir, restart mysqld, and reload the mysqldump.


MySql Order by isnull() Performance Problem

Your original clause:

order by isnull(Price), Price asc limit 50

Uses a function on the Price column. In your case, the function is isnull(); but it does not matter - any function on your column, e.g. COALESCE(Price, ...), Price + 1, etc., would make for the same result.

You see, once there's a function on a column, MySQL is then unable to use that column. Consider: the index uses a BTREE on Price, indexing your millions of rows in ascending order. But your request, to order by isnull(Price) requests an altogether different thing: you just want to provide with NULLs or not-NULLs; well, MySQL will not use the index for that.

I suppose it would be nice of MySQL to recognize a set of "safe" functions, which are monotonic with the index; but MySQL gives up an index the moment it sees a function. It just does not predict how the result of the function looks like.

Other databases support Index functions; the MariaDB fork supports virtual columns and indexes on virtual columns -- both solution can help you out with your query. Plain MySQL just can't eat it.


SQL Get All Data With The Given IDs And With The Same Data On Single Column

It is not absolutely clear what you expect as la should yield te result f 1,3,4 see query below

CREATE TABLE table1
    (`id` int, `col1` varchar(6), `name` varchar(6))
;

INSERT INTO table1
    (`id`, `col1`, `name`)
VALUES

    ('1', 'a', 'la'),
    ('2', 'b', 'ria'),
    ('3', 'w', 'la'),
    ('4', 'q', 'la'),
    ('5', 'y', 'ria');

SELECT id
FROM table1
WHERE `name` IN (SELECT `name` FROM table1  GROUP BY `name` HAVING COUNT(`name`) > 2)
AND id IN (1,2,3,5)

| id |
| -: |
|  1 |
|  3 |

SELECT `name` FROM table1  GROUP BY `name` HAVING COUNT(`name`) > 2

| name |
| :--- |
| la   |

db<>fiddle here


Should I use SQL or NoSQL for this specific design (surveys)?

Typically No-SQL solutions were implemented due to massive scale and not needing 100% ACID compliance.  Flattening data out and de-normalizing it for performance gains were critical in getting us to where we are today with solutions such as GFS, Hadoop, Mongo, Basho RIAK, etc.

Your case doesn't scream No-SQL, if anything you'd probably be better served with MS SQL server since you're using the ASP.NET stack and MS SQL Server provides a lot of functionality with ASP.NET through CLR, powershell (which can also call .Net objects) and playing very nice with IIS (I assume you'll also be hosting it on IIS).  This also sounds like something you can use SQL Server 2012 Express thus saving licensing costs.

MS SQL Server will also let you easily 'join' data across non related tables in cases where you come up with a new idea, and on a No-SQL solution that would be more difficult to implement.

But in reality, looking at your requirements and what you're going to be using it for.  You can go either way.  If it makes you happier to use a No-SQL solution and get your feet wet, awesome!  Go for it!  End result probably won't matter, but to strictly answer the question, which is better for a .Net app with the usage patterns you described, I'd personally go with SQL 2012 Express.

I'm curious to see what others think!


Your question is typically too broad to fit in a post here, but I'll draw out some lines that might help you to get a start.

A vehicle can be either a boat or a car. A good place to start is to think of what identifies a vehicle? Since I don't know your business I'll assume that there exists some kind of vehicle_id, note that this can be a combination of attributes:

CREATE TABLE VEHICLE
( VEHICLE_ID ... NOT NULL
, other attributes such as brand
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE IN ('CAR','BOAT')
);

If you want type safe sub-types (to prevent someone to add a boat in the car table), you can add a super-key in the vehicle table:

CREATE TABLE VEHICLE
( VEHICLE_ID ... NOT NULL
, other attributes such as brand
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE IN ('CAR','BOAT')
      UNIQUE (VEHICLE_TYPE, VEHICLE_ID)
);

A boat can be represented by:

CREATE TABLE BOAT
( VEHICLE_ID ... NOT NULL
, other attributes such as registry
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE = 'BOAT')
      FOREIGN KEY (VEHICLE_TYPE, VEHICLE_ID)
          REFERENCES VEHICLE (VEHICLE_TYPE, VEHICLE_ID)
);

and a similar one for car:

CREATE TABLE CAR
( VEHICLE_ID ... NOT NULL
, other attributes such as seats
, VEHICLE_TYPE CHAR(4) NOT NULL
,     PRIMARY KEY (VEHICLE_ID)
,     CHECK (VEHICLE_TYPE = 'CAR')
      FOREIGN KEY (VEHICLE_TYPE, VEHICLE_ID)
          REFERENCES VEHICLE (VEHICLE_TYPE, VEHICLE_ID)
);

Since registry is an attribute of both car and boat you might concider adding that attribute to vehicle.

An order could be represented as ( I assume the is an order_id that can be used to identify an order):

CREATE TABLE ORDER
( ORDER_ID ... NOT NULL PRIMARY KEY
, VEHICLE_ID ... NOT NULL
      REFERENCES VEHICLE (VEHICLE_ID)
, BUYER_ID ... NOT NULL
      REFERENCES BUYERS (BUYER_ID)
, other attributes such as date_of_contract
);

This is just a rough sketch and probably full of false assumptions. I would start with identifying what tables that are involved. Once that is done, start thinking about what identifies a row in each table.


PostgreSQL will evaluate all (or a high number) of possible query plans, and make a good guess at which one will be the least costly. If the different cost parameters (that depend, for instance, on the speed of your disks; the amount of available memory, etc.) are well set, most of the times that guess will be accurate and the choice of plans will be optimum.

You're asking PostgreSQL two different queries. One doesn't need sorting, the other one does. The rows returned are the same, because the rest of the query is the same. The query with the ORDER BY takes longer. That is to be expected.

PostgreSQL could have decided that performing a Seq Scan then a Sort would be quicker than performing an Index Scan. With the actual settings, it decided the opposite.

Performing an Index Scan takes longer than performing a Seq Scan just because, when scanning the index, PostgreSQL can just skip the entries with (user_id <> 1456) (the info for user_id is in the index). For the non-skipped values, it gets the (internal) reference to the corresponding row (which is also part of the index), and it has to retrieve the actual row in the table. It won't need to sort them, because by following the index, the values are already in the proper order.

With standard settings, the costs for for seq_page_cost and random_page_cost (in arbitrary units) are 4 and 1. That takes into account that, with HDD, jumping from one page to another needs a significant mechanical movement, and needs more time than reading pages one after next. You can assume a Seq Scan will read pages sequentially, and an Index Scan will mostly jump from one to another. [If you use SSD instead of HDD, the value for random_page_cost should probably be put to just 1.05].

If you would be using a covering index (one index that contains at least all the columns that are in your SELECT), then, PostgreSQL would be able to perform an Index Only Scan (meaning it doesn't have to retrieve the whole rows from the actual *table). That would speed up your query significantly. For a covering index to work, your table has to be properly maintained (i.e.: autovacuum must have had time to maintain it).

Try this query and see what the execution plan is:

EXPLAIN (analyze)
SELECT user_id
FROM pending_posts
WHERE user_id <> 1456
ORDER BY user_id ;

If you don't get an Index Only Scan, perform a:

VACUUM ANALYZE pending_posts ;

and retry it.

Note that indexes can help you in several areas:

In the SELECT clause if they are covering indexes. If the indexes contain all the columns specified, Index Only Scans are possible.
In the WHERE clause if they allow to filter out (or go directly to) specific) rows.
In the ORDER BY clause, if they provide already the rows in the sort order you specify.
In the GROUP BY, if the aggregation is done by sorting the rows according to the groups (other techniques are possible).

References:

Manoji Nesh Blog:  Seq scan vs Index Scan
PostgreSQL query plan and SQL performance notes - Types of index scans
Stack Overflow's Why does PostgreSQL perform sequential scan on indexed column?