SARGable WHERE clause for two date columns

Just adding this quickly so it exists as an answer (though I know it's not the answer you want).

An indexed computed column is usually the right solution for this type of problem.

It:

makes the predicate an indexable expression
allows automatic statistics to be created for better cardinality estimation
does not need to take any space in the base table

To be clear on that last point, the computed column is not required to be persisted in this case:

-- Note: not PERSISTED, metadata change only
ALTER TABLE #sargme
ADD DayDiff AS DATEDIFF(DAY, DateCol1, DateCol2);

-- Index the expression
CREATE NONCLUSTERED INDEX index_name
ON #sargme (DayDiff)
INCLUDE (DateCol1, DateCol2);

Now the query:

SELECT
    S.ID,
    S.DateCol1,
    S.DateCol2,
    DATEDIFF(DAY, S.DateCol1, S.DateCol2)
FROM
    #sargme AS S
WHERE
    DATEDIFF(DAY, S.DateCol1, S.DateCol2) >= 48;

...gives the following trivial plan:

As Martin Smith said, if you have connections using the wrong set options, you could create a regular column and maintain the computed value using triggers.

All this only really matters (code challenge aside) if there's a real problem to solve, of course, as Aaron says in his answer.

This is fun to think about, but I don't know any way to achieve what you want reasonably given the constraints in the question. It seems like any optimal solution would require a new data structure of some type; the closest we have being the 'function index' approximation provided by an index on a non-persisted computed column as above.


How to dump multiple databases of the same cluster in single execution?

pg_dump can only dump a single DB. The manual:

  pg_dump only dumps a single database. To back up an entire cluster, or
  to back up global objects that are common to all databases in a
  cluster (such as roles and tablespaces), use pg_dumpall.

pg_dumpall, on the other hand can only dump all DBs in the cluster, even if you provide a specific DB name in the connection string. The manual:

  -d connstr
  --dbname=connstr

  Specifies parameters used to connect to the server, as a connection
  string. See Section 34.1.1 for more information.

  The option is called --dbname for consistency with other client
  applications, but because pg_dumpall needs to connect to many
  databases, the database name in the connection string will be ignored.
  Use the -l option to specify the name of the database used for the
  initial connection, which will dump global objects and discover what
  other databases should be dumped.

To dump some DBs but not all, use a shell script, like has been advised.
You may want to include commands to create each DB in the dump:

How to get a pg_dump -s to include the CREATE DATABASE command?


You need to apply what MongoDB Docs say:

https://docs.mongodb.org/manual/tutorial/transparent-huge-pages/

I installed MongoDB 3.0.8 as couple of months ago and got the same warnings. This setting was set by Amazon. First thing to do is create a script that can enable and disable Transparent Pages. Documentation gives this:

#!/bin/sh
### BEGIN INIT INFO
# Provides:          disable-transparent-hugepages
# Required-Start:    $local_fs
# Required-Stop:
# X-Start-Before:    mongod mongodb-mms-automation-agent
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Disable Linux transparent huge pages
# Description:       Disable Linux transparent huge pages, to improve
#                    database performance.
### END INIT INFO

case $1 in
  start)
    if [ -d /sys/kernel/mm/transparent_hugepage ]; then
      thp_path=/sys/kernel/mm/transparent_hugepage
    elif [ -d /sys/kernel/mm/redhat_transparent_hugepage ]; then
      thp_path=/sys/kernel/mm/redhat_transparent_hugepage
    else
      return 0
    fi

    echo 'never' > ${thp_path}/enabled
    echo 'never' > ${thp_path}/defrag

    unset thp_path
    ;;
esac

I use CentOS. I had this script made and I chkconfig'd it as a service so THP is disabled at startup.

In your case, you will have to run the script, restart MongoDB (service mongod restart), login to mongo and that warning should be gone. You need to follow the docs for Ubuntu as it says to install using:

sudo update-rc.d disable-transparent-hugepages defaults


No, you can't do that. Your best bet would be to give your top variable a very high default value.

CREATE PROC dbo.if_branch (@top INT = 2147483647)
AS
BEGIN

WITH d AS
    (
        SELECT *
        FROM sys.databases AS ds
        WHERE ds.name = 'tempdb'
    )
SELECT TOP (@top) *
FROM d;

END


The documentation you linked to links to the JSON page, which says:

  JSON is a textual format so the JSON documents are can stored in NVARCHAR columns in SQL Database.

Wait a second...I think this documentation needs a pull request :)

Anyways...As always with MAX, if you don't need it, don't use it! You're correct that it will cause a (slight) performance hit and SQL Server in no way prefers NVARCHAR(MAX) for JSON storage. Consider yourself lucky you're storing such small JSON.


Set default value for all columns with specific name within a database

Let's say the new default you want is 5:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + ';
  ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c
  + ' DEFAULT (5) FOR [project_id];'
FROM
(
  SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name),
    c = QUOTENAME(d.name)
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  INNER JOIN sys.columns AS c
  ON t.[object_id] = c.[object_id]
  INNER JOIN sys.default_constraints AS d
  ON d.parent_object_id = t.[object_id]
  AND d.parent_column_id = c.column_id
  WHERE c.name = N'project_id'
) AS x;

PRINT @sql;
--EXEC sp_executesql @sql;

Notes:

print is limited to 8K, so if you have a lot of tables with this column, you might not actually see the entire command output in SSMS, and it may look truncated.
This will only add the constraint to project_id columns that already have a default constraint. If you have other tables that have this column but don't currently have a constraint, you'll need a separate query.
This will not change the values already generated based on the old defaults.

Here is why I recommend against INFORMATION_SCHEMA for this type of problem:

https://sqlblog.org/2011/11/03/the-case-against-information_schema-views


What are the various Informix column data types?

The values in the coltype column of the syscolumns system catalog table are defined primarily in the Informix ESQL/C header sqltypes.h.  This header is used throughout the Informix server code.
The values in the coltype column are a mixture of an 8-bit (unsigned) integer in the low-order bits, and various flag values in the high-order bits.  In particular, when a column is defined with the NOT NULL qualifier, the 0x100 bit is set — which corresponds to 'add 256'.  There are other flag bits which you are less likely to see.
The value 4118 (decimal) shown in another answer corresponds to hex 0x1016; the 16 (aka 22 decimal) corresponds to SQLROW, and the 0x1000 (4096 decimal) bit corresponds to #define SQLNAMED 0x1000 /* Named row type vs row type */.  The type is (as noted in the other answer) a 'named row type'.
Consider a table:
CREATE TABLE bool_check
(
    b1      BOOLEAN NOT NULL,
    b2      BOOLEAN
);

The values in the sqltype column of syscolumns are:

b1 = 297 = 256 + 41
b2 = 41

These correspond to SQLUDTFIXED (type 41).  The type SQLBOOL is marked 'used by FE [front end], … not real major types in BE [back end, meaning database server]'.  The collength is 1 for both.
Relevant segments of the header include:
SQL Types:
#define SQLCHAR         0
#define SQLSMINT        1
#define SQLINT          2
#define SQLFLOAT        3
#define SQLSMFLOAT      4
#define SQLDECIMAL      5
#define SQLSERIAL       6
#define SQLDATE         7
#define SQLMONEY        8
#define SQLNULL         9
#define SQLDTIME        10
#define SQLBYTES        11
#define SQLTEXT         12
#define SQLVCHAR        13
#define SQLINTERVAL     14
#define SQLNCHAR        15
#define SQLNVCHAR       16
#define SQLINT8         17
#define SQLSERIAL8      18
#define SQLSET          19
#define SQLMULTISET     20
#define SQLLIST         21
#define SQLROW          22
#define SQLCOLLECTION   23
#define SQLROWREF       24
/*
 * Note: SQLXXX values from 25 through 39 are reserved to avoid collision
 *       with reserved PTXXX values in that same range. See p_types_t.h
 *
 * REFSER8: create tab with ref: referenced serial 8 rsam counter
 *      this is essentially a SERIAL8, but is an additional rsam counter
 *      this type only lives in the system catalogs and when read from
 *      disk is converted to SQLSERIAL8 with CD_REFSER8 set in ddcol_t
 *      ddc_flags we must distinguish from SERIAL8 to allow both
 *      counters in one tab
 *
 * SQLSTREAM: Is a synonym for SQLUDTFIXED used by CDR (Enterprise
 *      Replication) code
 */
#define SQLUDTVAR       40
#define SQLUDTFIXED     41
#define SQLSTREAM       SQLUDTFIXED
#define SQLREFSER8      42

/* These types are used by FE, they are not real major types in BE */
#define SQLLVARCHAR     43
#define SQLSENDRECV     44
#define SQLBOOL         45
#define SQLIMPEXP       46
#define SQLIMPEXPBIN    47

/* This type is used by the UDR code to track default parameters,
   it is not a real major type in BE */
#define SQLUDRDEFAULT   48
#define SQLUNKNOWN      51
#define SQLBIGINT       52
#define SQLBIGSERIAL    53
#define SQLMAXTYPES     54

#define SQLLABEL        SQLINT

Flags:
#define SQLNONULL       0x0100  /* disallow nulls       */
/* a bit to show that the value is from a host variable */
#define SQLHOST         0x0200  /* Value is from host var. */
#define SQLNETFLT       0x0400  /* float-to-decimal for networked backend */
#define SQLDISTINCT     0x0800  /* distinct bit         */
#define SQLNAMED        0x1000  /* Named row type vs row type */
#define SQLDLVARCHAR    0x2000  /* Distinct of lvarchar */
#define SQLDBOOLEAN     0x4000  /* Distinct of boolean */
#define SQLCLIENTCOLL   0x8000  /* Collection is processed on client */

/* we are overloading SQLDBOOLEAN for use with row types */
#define SQLVARROWTYPE   0x4000  /* varlen row type */

There are also 'C-ISAM types' with numbers 100 to 125 and names such a CCHARTYPE and CDECIMALTYPE.  They are not of immediate concern here.  There are 524 lines in the header file (at least in the version I looked at). Of those, 74 are blank, 315 contain code, and the remaining lines are pure comment lines.  AFAIK, the SQLREFSER8 type is stillborn; it does not exist outside this file.
The types BLOB NOT NULL and CLOB NOT NULL are both encoded in coltype as 297 (41 + 256 — the same a BOOLEAN NOT NULL), or SQLUDTFIXED with collength of 72 (as opposed to 1 for BOOLEAN NOT NULL).  The fixed-length data is a descriptor that provides all the details about where the BLOB or CLOB value is actually stored.


How to migrate SQL Server credentials from 2005 to 2008 R2

I'd suggest you take a look at the Copy-DbaCredential Powershell cmdlet from dbatools.

From their web page on this cmdlet:

  By using password decryption techniques provided by Antti Rantasaari
  (NetSPI, 2014), this script migrates SQL Server Credentials from one
  server to another, while maintaining login names and passwords.

Here is an example of how you'd invoke this cmdlet

Copy-DbaCredential -Source sqlserver2014a -Destination sqlcluster


MySQL Replication Master Slave Architecture - Web app that is based on the SLAVE DB

Yes
It's not going to be a problem but of course it depends on the problem itself. Lets use the term - incident. Problem is already a problem by definition. Solve incidents, analyze the cause , prevent the problem!
In case of an error, try to think what caused it. You are right about worrying, as it is a very common cause of issues.

Try to strictly separate the replicated objects from the unique ones in slave. Different DB
Use some constraints and "tips" for the replication process, like:

--replicate-wild-ignore-table=db_name.tbl_name
include slave status verification in your backup procedures
redirect all reads to the slave (of course the important reads should be done in master and ONLY they)
backup the slave only
include the GTID (you might need it)

Be more AGILE with it. Do it, start testing and you will see how it goes. It's easy and you sound competent enough to do it.

Always monitor your replication. Using GTID and row-based replication may help you a bit with the consistency.


Is there a way to clone a row in a "maintainable" way (with PostgreSQL)?

With the additional module hstore installed, there is a very simple way to replace the value(s) of individual field(s) without knowing anything about other columns:
INSERT INTO mytable
SELECT (t1).*  -- note the parentheses
FROM  (
   SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mytable', 'id'))::text) AS t1
   FROM   mytable t
   WHERE  id = 12
   ) sub;

All columns are cast to text and back in the process.
Closely related answer with detailed explanation and more (possibly a bit faster) alternatives:

Duplicate row with Primary Key in PostgreSQL

Clone parent row and all its children
That's more tricky. But based on the above, plus using a data-modifying CTE, there is a very elegant solution:
WITH ins_parent AS (
   INSERT INTO mytable
   SELECT (p).*  -- note the parentheses
   FROM  (
      SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mytable', 'id'))::text) AS p
      FROM   mytable t
      WHERE  id = 2
      ) sub
   RETURNING id
   )
INSERT INTO mychild
SELECT (c).*
FROM  (
   SELECT t #= hstore('id', nextval(pg_get_serial_sequence('mychild', 'id'))::text)
            #= hstore('parentid', (SELECT id::text FROM ins_parent)) AS c
   FROM   mychild t
   WHERE  parentid = 2
   ) sub;

This uses the new parentid for all copies in the child table and assigns new serial values to the id column.


mysql, performance issue with FOR UPDATE query

My Mantra:  "Don't queue it, just do it."  Although I don't fully understand your design, I suspect that the problems would decrease if you simply processed the data as it comes in.

A Rule of Thumb:  If an item can be processed in less time than it takes to enqueue and dequeue it, then the Queue is wasting time.

To better discuss this, I need to understand all the dataflow.  For starters, fill in the blanks:

Item(s) coming in ___ times a second.
__ arrive in each batch.
The client threads are limited to ____ threads by ______ (Apache/Tomcat/...).
The threading uses _____ (PHP/Java/C#/...).
max_connections = ____
Processing one item takes, on average ___ ms.
The table domains is current ____ rows; it is purged ______ (never/daily/immediately/...)  And provide SHOW CREATE TABLE domains.

More

Thanks for the update!

I gather that something else is changing test_stage, thereby effectively re-queueing rows?

Get rid of KEY test_stage (test_stage), since it is redundant with two other indexes.

Change KEY test_stage_id (test_stage, id) to KEY tid (test_stage, id, domain_name) so that it is "covering".

One thought is to get rid of id and promote domain_name to be the PRIMARY KEY.  This avoids the need to deal with two unique keys.  Note that that would change the query and optimal index to

SELECT domain_name  FROM domains  WHERE test_stage=1
    ORDER BY domain_name  LIMIT 200  FOR UPDATE
INDEX(test_stage, domain_name)

Another thought is to assign the "worker" threads to contiguous chunks of the table, based on the PRIMARY KEY.  That way, they should never (or at least rarely) collide over which rows to grab.  (I suspect this is the core of the issue.)

I assume your buffer_pool is big enough.  But if you would like a critique of the VARIABLES, please provide SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES.


The password is never recorded.  If it was it would be the first place a hacker would check for clues about correct passwords.

By default, TDS (Tabular Data Stream, the network protocol used by SQL Server), encrypts SQL Server secured passwords before sending them over the network.  If Active Directory logins are used with Kerberos authentication (the default), the client doesn't even send an encrypted version of the password over the wire, instead it sends proof of the Kerberos ticket to the server for authentication.  This means you cannot even sniff network traffic to determine what password has been tried.


Azure Virtual Machine - local temp storage (D: drive) - how much IOPS it can handle?

For VM Series with premium storage a portion of the local SSD is reserved for caching of premium disks.  That's why the D3 has 200GB of Temp Storage, but the DS3 has only 28GB of available Temp storage, with 172GB reserved for disk caching.
The IOPS are similiarly shared between the Temp storage (D:) and the disk cache.  The total IOPS is 16,000, but your effective IOPS and throughput will depend on the utilization of the disk cache.  If you turn off read caching on your data disks, or they are lightly-utilized, you'll have more IOPS available for temp storage.
That's why caching and temp storage are together in the grid:
Max cached and temp storage throughput:
16,000 IOPS
128 MBps
172GB cache size
28GB temp storage


Nightly reload of tables from a linked server

It depends, but options include (and are not limited to):

Restore a backup of the linked database on the local instance (for better uptime, restore to a new name, then DROP DATABASE the old one and then sp_rename the fresh restore), and either

create synonyms to the restored copy from your current local database so as to allow current queries to continue

optionally drop every table except the ones you need, SHRINK, and then do index maintenance to make up for the index fragmentation the SHRINK caused (all before the sp_rename)

do your "table refresh" from the restored database, then DROP DATABASE the restore database

Use OPENQUERY or EXECUTE AT to send a good JOIN and/or WHERE clause or set of clauses to the linked server, including the maximum update date your local server has, so you retrieve (and insert or update) only the rows that have been inserted or updated since your last refresh

deal with deleted rows painfully

Use a history table on the remote database to track inserts, updates, and deletes, and use that in your JOIN and/or WHERE clauses sent using OPENQUERY or EXECUTE AT.


Are expdp/impdp commands appropriate for daily schema sync?

No, that is not a possible way:

Most of the non-table objects will already exist in your schema. You already get such error messages. The import will not overwrite it.
You can configure how export should work if a table already exists but if there is a table that s referenced by another table then TRUNCATE and REPLACE will not  work.
Even if there is no foreign key reference to a table indexes will slow down the import dramatically
A lot of other problems may occur (concerning triggers, partitions, references to other tables and maybe more)

So the only reasonable way to synchronize the schema using expdp/impdp is to drop the schema before you import.

If you don't want to drop the schema for some reason but replace the data of the tables  a lot of additional operations are necessary.


There is insufficient system memory in resource pool default to run this query

While this probably isn't an exact answer to your problem I'll post it anyway hoping it may help you in a way.

What you are seeing is not the MEMORYCLERK_SQLBUFFERPOOL but the MEMORYCLERK_SQLLOGPOOL taking up all the memory.

There is a known issue with SQL 2012 SQL Server 2012 experiences out-of-memory errors. Even though you are running 2014 there is a chance that you are running into the same issue (even though I couldn't find a connect item for 2014).

There is also this known issue for both 2014 and 2012 involving index rebuilds and always on. You didn't specify you are using that but maybe the same bug has other triggers: FIX: SQL Cache Memory decreases and CPU usage increases when you rebuild an index for a table in SQL Server

So my best suggestion for now is to update to the latest CU and see if it still happens, since there have been a few fixes around MEMORYCLERK_SQLLOGPOOL recently. Since you are running RTM with 2 security fixes only that could make sense, there is an SP1 + multiple CU's available.

Since there is also fairly high memory usage in MEMORYCLERK_XTP used by in-memory OLTP this article may help you troubleshoot: Monitor and Troubleshoot Memory Usage


I would stop using VS2013 for SSIS 2012. It builds SSIS 2014 packages that can't be deployed to SSIS 2012. Instead install SSDT-BI for VS2012.

Another option is installing SSDT preview for VS2015. It is a currently a release candidate for SQL 2016 but is able to support downlevel versions back to SSIS 2012. So the story will be much less confusing soon (or now if you are willing to use beta software).


Can a database file be brought back online if the file is recovered from a disk backup?

I'm 99% sure that setting a file to offline is a one-way operation. The only route to bring them back online is to restore the data from a full backup and then apply log backups.

There may be an obscure hack route to get this working but I'd be inclined to rebuild the database. Create a new database, transfer the schema you want to keep, copy the data across.


Can't SQL Server store NULLs in fixed-length columns?

Well, this was the case for Sybase, which is the ancestor for SQL Server. Unfortunately, I did not find any information as to when this exact change was made, but according to this article the engine was fully rewritten in SQL Server 2005. As this is not the case for any modern SQL Server edition, you can just ignore this statement.


Shortcuts/Snippets to select a block of code

Using Autohotkey, I was able to develop a solution to select a block of code and here is the script I have:

!b::

send ^f

sendraw ^\r$

send {F3}

send +{F3}

send {Esc}

send {down}

send ^{=}

return

Specifically, pressing Alt + B will invoke this script, and the script will select the block of code where the cursor is in. Note that the block of code has to be separated from other blocks of code by blank lines, and the block of code cannot have blank of lines inside itself.

The key steps of the about script are

using regular expression "^\r$
" to locate the blank lines above and below the cursor
moving the cursor to these two blank lines one by one
using ctrl + = to select the block of code between these two blank lines.

Note that !/^/+ are alt/ctrl/shift separately.


Could you go with one SQLServer instance at one of the offices and then just configure VPN and join the other two in with a site-to-site VPN tunnel? That would greatly simply your architecture as long as it met your performance requirements (and possibly some SQLServer license costs).


MySQL Table Crashed Needs Repair, but Doesn't seem to Repair?


Permission denied for view beyond its own schema

To diagnose, inspect the output of this query before and after reconnecting in your question:
SELECT current_user, current_setting('search_path'::text) AS search_path
     , relowner::regrole, relnamespace::regnamespace, relname, relacl
     , pg_get_viewdef(c.oid) AS view_definition
FROM   pg_class c
WHERE  relname = 'view'  -- actual view name
OR    (relname = 'table' AND relnamespace = 'schema1');   -- actual table & schema name

You should get two or more rows each time. I omitted the schema of the view on purpose to see whether there might be others with the same name.
Oh, and make sure, your transaction is committed. Read the manual here.
This is not a direct answer, but it should enable you to get your answer.


Sequential GUID or bigint for 'huge' database table PK

I'm in a similar same situation. Currently, I'm using the sequential GUID approach and have no fragmentation and easy key generation.

I have noticed two disadavantages that caused me to start migrating to bigint:

Space usage. 8 bytes more per index. Multiply that by 10 indexes or so and you get a huge waste of space.
Columnstore indexes do not support GUIDs.

(2) Was the killer for me.

I will now generate my keys like this:

yyMMddHH1234567890

I'll be using a leading date plus hour and having a sequential part after that. That allows me to range-query my data by date without any addition index at all. This is a nice bonus for me.

I'll generate the sequential part of the bigint using a HiLo algorithm that lends itself well to being distributed.

Hope some of this transfers to your situation. I definitely recommend using bigint.


ADD or modify column in one call (upsert) - avoid DROP calls

If I understand your question correctly, you just want to make the column NOT NULL. This can be done with the ALTER <column_name> sub-command
ALTER TABLE mbk_user ALTER login_attempts SET NOT NULL;

Obviously this only works if there are no NULL values in that column.


There is nothing in your question that would speak against inheritance.

One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually.

nba_players, nfl_players etc. could conveniently inherit from a general parent table players (possibly in a separate schema for empty parent tables). ALTER TABLE on the parent cascades to all children (except for  ALTER TABLE ... RENAME). This makes it easy to run queries on all player tables together by targeting the parent.

If you never need to look at all player tables together or cascade changes to all tables, it might be better to just keep a template table (possibly in a separate schema for template tables) and create instances with CREATE TABLE ... (LIKE ...):

CREATE TABLE nba_players(LIKE players);


I need help with a SQL-query that results in an error

The user you are trying to delete have rows in the feedback table. Given your foreign key definition:

CONSTRAINT fk_feedback_user1
FOREIGN KEY (user_id)
    REFERENCES user (user_id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION

You need to first delete from the feedback table:

delete from feedback where user_id = ?;
delete from user where user_id = ?;

An alternative is to change the foreign key to:

CONSTRAINT fk_feedback_user1
FOREIGN KEY (user_id)
    REFERENCES user (user_id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION

Any rows in the feedback table for that user will then be deleted automatically when you delete a user


Storage snapshots for consistent backup of postgresql - different data and log volumes

The documentation you cited says it all, but I wouldn't blame you if you want to try to verify the claims of the vendor regarding snapshots taken at the same time. Perhaps a way of uncovering something could be to stress test the WAL system more specifically.

e.g. In addition to your pgbench-based tests, try adding random calls to pg_switch_xlog() to force log rotation, shorter and longer checkpoint intervals (shortening and lengthening checkpoint_timeout and checkpoint_timeout) and even using small or large wal file sizes.

Unless there's something I'm missing, for your snapshots not taken at the same time, I would attribute your recovered DBs perhaps to a bit of lucky timing. In the last case, imagine you took your log snapshot while the current xlog location was, say, 0/A1C0FFEE. Then you have 3 minutes of particularly heavy load on the system, that causes a full cycle through the WAL files, and your DB is now at 0/DEADBEEF when the data snapshot is taken. When you attempt to restore, the WAL files being written to at the time of the data snapshot are long gone, and recovery will fail.


On your server, there is a file named MsDtsSrvr.ini.xml which is located at C:\Program Files\Microsoft SQL Server\120\DTS\Binn (for sql server 2014). When I examined this file it stated "..\packages" for the location, so I went to the DTS folder then into the packages folder and the files were there.

Depending on your version, architecture, and installation path) of SQL Server, you might find these files at

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml (2005)
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml (2008/2008R2)
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml (2012)
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\MsDtsSrvr.ini.xml (2014)
C:\Program Files\Microsoft SQL Server\130\DTS\Binn\MsDtsSrvr.ini.xml (2016)


Mysqld can`t start after I changed the socket path

When everything you can think of to check seems right, you have to think of the things you can't think of. :)

Like possibly "apparmor."

https://askubuntu.com/questions/234668/cant-start-mysql-server-if-the-sock-file-is-changed-in-etc-mysql-my-cnf

https://wiki.ubuntu.com/AppArmor

http://en.wikipedia.org/wiki/AppArmor


MySQL replication for non-standard port (3306)

You need to add the port to my.ini for the Master

[mysqld]
server-id=1
port=1000
log-bin=black-bin.log
datadir=d:\mysql\master\
innodb_flush_log_at_trx_commit=1
sync_binlog=1

and restart mysql

First run this

STOP SLAVE;
SHOW SLAVE STATUS\G

From the output

use Relay_Master_Log_File as CurrentLogFile
use  Exec_Master_Pos as CurrentLogPosition

As was mentioned in the comment, run CHANGE MASTER TO on the Slave like this

CHANGE MASTER To
MASTER_HOST='ipaddrofmaster',
MASTER_PORT=1000,
MASTER_USER='repl_user',
MASTER_PASSWORD='password'
MASTER_LOG_FILE='CurrentLogFile',
MASTER_LOG_POS=CurrentLogPosition;
START SLAVE;

Give it a Try !!!


Index optimization for table that is truncated every day

Go ahead and drop the clustering key while you're importing data.  When you've finished your INSERTs, create the clustering key first, then the PK if it's non-clustered, then any remaining indices.  I'm running such scripts at this very moment, and it takes about half as long as inserting into a table which is fully indexed.

There's no problem in going without a clustering key while loading data.  I would recommend that you import your data in the same order that it will be clustered, if possible; this will reduce the need to shuffle the data around when it's clustered.  If you're using an arbitrary IDENTITY column, I suggest you reconsider; there may be a better candidate for clustering (or you may not even need a clustering key).

There's no problem in going without a PK while loading data.  It's most important in maintaining referential integrity and in giving your indices a narrow target to hit; neither applies when you're bulk-loading data, assuming that you trust your data to not contain duplicates.

Duplicates are not always the devil either, and it may be faster to remove them after bulk load than to build processes at the front end that de-dupe in other ways.

My preference is to pull in data in all its ugly rawness first, often into a heap table, then do SQL cleanup before copying it to a new table.  Maybe that's just because I'm a SQL guy and everything looks like a nail to me, but SQL is optimized for set-based operations.  On the other hand, if the volume of data is huge you may need to do conversions and cleanup on an RBAR basis as you import it via SSIS or whatnot.

I would also recommend explicitly defining your PK (if any) with an ALTER TABLE statement after your CREATE TABLE.  This reminds you to make an explicit choice (including whether to cluster on the PK), puts it next to your other index declarations, and lets you give it a non-random name.


MySQL 8.0 Triggers Current_User not working

Since I cant get the right user inside the trigger using current_user() and user() but works fine on Stored Procedure. What I did is I create a Stored Procedure and call it inside the trigger.

Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_log`()
BEGIN
    INSERT INTO audit_trail
    values( aud_no,
           ( SELECT user_acc_id
             from user_accounts
             where user_username = REPLACE( user(), '@localhost', '')),
           ( SELECT user_fname
             from user
             where user_id = ( SELECT user_acc_id
                               from user_accounts
                               where user_username = REPLACE( user(), '@localhost', ''))),
           current_date(),
           current_time(),
           "logs in and out");
END

Trigger

CREATE DEFINER=`root`@`localhost` TRIGGER `user_log_AFTER_INSERT` AFTER INSERT ON `user_log` FOR EACH ROW BEGIN
        CALL insert_log();
END


Wrong sort order using utf8_unicode_ci and swedish characters like ö

Interestingly, someone addressed this apparent difference.

Matthias Bynens answered the post MySQL Collation utf8_unicode differences abour 4 years ago.

He mentioned "Weight Keys"

version-4.0.0 UCA weight keys
utf8_unicode_520_ci is based on UCA 5.2.0 weight keys

For more information, go to The Unicode Consortium.


How to extract values from a JSON field with Mongodb?

Have you tried with aggregate yet?

Following I used $addField and $project

var c = db.getCollection("json_column")
if (c == null) {
    db.createCollection('json_column');
}
db.getCollection("json_column").remove({});

db.getCollection('json_column').insert({user:{"email": "mail6@mail6.com", "name": "name1"}});
db.getCollection('json_column').insert({user:{"email": "mail2@mail.com"}});
db.getCollection('json_column').insert({user:{"email": "mail7@mail7.com", "name": "mail2"}});
db.getCollection('json_column').insert({user:{"email": "mail3@mail.com"}});
db.getCollection('json_column').insert({user:{"email": "mail5@mail8.com", "name": "name3"}});
db.getCollection('json_column').insert({user:{"email": "mail5@mail9.com", "name": "name4"}});

db.getCollection('json_column').aggregate([
{
    $addFields: {
        "email": "$user.email",
        "name": "$user.name"
    }
},
{
    $project: {
        "user":0
    }
}
]);


How long can Deadlock info hold in innodb status page?

The first thing you must look for is the setting for innodb_lock_wait_timeout.
Suppose you run this SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; and get
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

At the very least, you should rightly expect a newly detected deadlock on a busy DB server every 50 seconds.
InnoDB cannot detect deadlock under two conditions:

LOCK TABLES is explicit executed
A lock set by a storage engine other than InnoDB is involved

These two conditions are usually self-inflicted and relatively easy to find. Setting the innodb_lock_wait_timeout would be the usual workaround.
However, there is a one boobytrap you need to be aware of. It is possible for SELECT queries to create row-level locks. Many developers are not aware of this quirky behavior. In fact, according to the MySQL Documentation:

If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

I discussed this back on Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
RECOMMENDATIONS

Spend time searching for SELECTs that do JOINs of InnoDB and MyISAM tables (hopefully you have none). If any, please convert the MyISAM tables to InnoDB so that InnoDB locks can be better monitored and better managed.
Search carefully for deadly embrace situations
Avoid doing mysqldumps with --lock-tables and --single-transaction mixed.

With these recommendations, you can hopefully assure that the deadlock status in SHOW ENGINE INNODB STATUS\G should be updated with a degree of regularity.


SSAS -> MDX -> Create a price comparision against resellers

If you have the following database schema with Date, Product and Reseller dimensions and a price fact table:

create table DimProduct (
       ProductID    int not null
      ,Product      varchar (50)
)
go

Create table DimDate (
       DateID    datetime not null
      ,MonthID   int not null
)
go

create table DimReseller (
       ResellerID   int not null
      ,Reseller     varchar (50)
)
go

create table FactPrices (
       ProductID    int not null
      ,ResellerID   int not null
      ,DateID       datetime not null
      ,Price        money
)
go

insert DimProduct (ProductID, Product) values (1, 'Widget')
insert DimProduct (ProductID, Product) values (2, 'Dohickey')
insert DimProduct (ProductID, Product) values (3, 'Whoflungdung')
go

insert DimDate (DateID, MonthID) values ('2012-01-01', 201201)
insert DimDate (DateID, MonthID) values ('2012-02-01', 201202)
insert DimDate (DateID, MonthID) values ('2012-03-01', 201203)
go

insert DimReseller (ResellerID, Reseller) values (1, 'Braise and co')
insert DimReseller (ResellerID, Reseller) values (2, 'Cocost')
insert DimReseller (ResellerID, Reseller) values (3, 'Walls Mart')
go

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 1, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 1, '2012-02-01', 9)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 2, '2012-02-01', 10)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (1, 3, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 1, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 1, '2012-02-01', 10.5)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 2, '2012-02-01', 9.90)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (2, 3, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 1, '2012-01-01', 12)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 1, '2012-02-01', 10.5)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 2, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 2, '2012-02-01', 11)

insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 3, '2012-01-01', 10)
insert FactPrices (ResellerID, ProductID, DateID, Price) values (3, 3, '2012-02-01', 10.5)

go

Create a cube with Date, Product and Reseller dimensions and a SalesTrends fact table with a single metric 'Price'.  The MDX query below will produce counts of products for which the price is increasing, static or decreasing in a given period.

with
member [Measures].[Delta]
    as [Measures].[Price] -
       (parallelperiod ([Date].[Calendar].[Month]
                       ,1
                       ,[Date].[Calendar].currentmember)
       ,[Measures].[Price])

member [Measures].[Rising]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] > 0))
      ,solve_order = 20

member [Measures].[Static]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] = 0))
      ,solve_order = 20

member [Measures].[Falling]
    as count (filter ([Reseller].[Reseller].Children
                      ,[Measures].[Delta] < 0))
      ,solve_order = 20

select {[Measures].[Rising]
       ,[Measures].[Static]
       ,[Measures].[Falling]}
    on columns
      ,[Product].[Product].Children
    on rows
  from [SalesTrends]
 where ([Date].[Calendar].[Month].[201202])

The query calculates the delta from the previous period, and calculates three metrics for rising, static or falling based on counts of resellers where the delta is positive, zero or negative.  Note that the prices will aggregate in strange ways if you to calculate this at anything but the lowest grain of the underlying fact table.


How to transfer the ownership of the dbo schema in SQL Server 2012

Slightly different approach would be to give the schema back to the original owner/schema, instead of to dbo:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner;


Is there a FOR XML variant that can add attributes with arbritrary values?

The query you have

SELECT
'foo' AS Detail,
'bar' AS Detail,
'baz' AS Detail
FOR XML PATH ('Header')

concatenates the values to a single Detail element.

<Header>
  <Detail>foobarbaz</Detail>
</Header>

To have multiple Detail elements you need to separate the columns with a null.

SELECT
'foo' AS Detail,
 null,
'bar' AS Detail,
 null,
'baz' AS Detail
FOR XML PATH ('Header')

<Header>
  <Detail>foo</Detail>
  <Detail>bar</Detail>
  <Detail>baz</Detail>
</Header>

And then you use the @ syntax suggested in the answer by Rob Farley to get the result you are looking for.

SELECT
'A' AS 'Detail/@type',
'foo' AS Detail,
 null,
'B' AS 'Detail/@type',
'bar' AS Detail,
 null,
'C' AS 'Detail/@type',
'baz' AS Detail
FOR XML PATH ('Header')

<Header>
  <Detail type="A">foo</Detail>
  <Detail type="B">bar</Detail>
  <Detail type="C">baz</Detail>
</Header>


Why do sequential GUID keys perform faster than sequential INT keys in my test case?

I modified @Phil Sandler's code to remove the effect of calling GETDATE() (there may be hardware effects/interrupts involved??), and made rows the same length.

[There have been several articles since SQL Server 2000 relating to timing issues and high-resolution timers, so I wanted to minimise that effect.]

In simple recovery model with data and log file both sized way over what is required, here are the timings (in seconds): (Updated with new results based on exact code below)

       Identity(s)  Guid(s)
       ---------    -----
       2.876        4.060
       2.570        4.116
       2.513        3.786
       2.517        4.173
       2.410        3.610
       2.566        3.726
       2.376        3.740
       2.333        3.833
       2.416        3.700
       2.413        3.603
       2.910        4.126
       2.403        3.973
       2.423        3.653
    -----------------------
Avg    2.650        3.857
StdDev 0.227        0.204

The code used:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(88))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,1) PRIMARY KEY, rowNum int, adate datetime)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum, adate) VALUES (@LocalCounter, GETDATE())
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate, batchNumber)
SELECT adate, rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber)
SELECT adate, rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms, @IdTimeStart, @IdTimeEnd) AS IdTime, DATEDIFF(ms, @GUIDTimeStart, @GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp
GO

After reading @Martin's investigation, I re-ran with the suggested TOP(@num) in both cases, i.e.

...
--Do inserts using GUIDs
DECLARE @num INT = 2147483647;
DECLARE @GUIDTimeStart DATETIME = GETDATE();
INSERT INTO TestGuid2 (SomeDate, batchNumber)
SELECT TOP(@num) adate, rowNum FROM #temp;
DECLARE @GUIDTimeEnd DATETIME = GETDATE();

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber)
SELECT TOP(@num) adate, rowNum FROM #temp;
DECLARE @IdTimeEnd DateTime = GETDATE()
...

and here are the timing results:

       Identity(s)  Guid(s)
       ---------    -----
       2.436        2.656
       2.940        2.716
       2.506        2.633
       2.380        2.643
       2.476        2.656
       2.846        2.670
       2.940        2.913
       2.453        2.653
       2.446        2.616
       2.986        2.683
       2.406        2.640
       2.460        2.650
       2.416        2.720

    -----------------------
Avg    2.426        2.688
StdDev 0.010        0.032

I wasn't able to get the actual execution plan, as the query never returned! It seems a bug is likely. (Running Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64))


Why Oracle sessions are still alive after two weeks being Inactive

I suspect they're part of a connection pool & therefore haven't idled out because they're being used frequently.

INACTIVE in v$session merely means there isn't a SQL statement being executed at the exact moment you check v$session.

If they're part of a connection pool they're doing their job properly by being logged in for long periods of time. The whole point of connection pooling is to remove the need for lots of logons/logoffs & keep persistent sessions for fast query startup - there's a much larger overhead logging in to execute one query, then disconnecting every time.

To get the last activity time for each session:

select username, UPPER(program), logon_time,
       floor(last_call_et / 60) "Minutes since active", status
from v$session
where UPPER(program) LIKE '%W3%'
order by last_call_et;

I'd advise against killing sessions unless you know that doing so will not cause problems on the application side (trying to use a session that's been killed, for example).

It may be the case that you're looking at an incorrectly configured connection pool which creates hundreds of connections once the app starts up - the connection pool may be an order of magnitude bigger than it needs to be. I suggest reaching out to the developers/application support staff & taking a look at how the connection pool is configured.

Having done a bit of research, w3wp.exe is the IIS Application Pool Process - you almost certainly want to talk to your IIS webserver admins to help get to the bottom of the connection pooling configuration.


Query data from two tables and substitute foreign keys of first table with values of second

PROPOSED QUERY

SELECT
    IFNULL(B.uid,'') left_uid,
    IFNULL(C.uid,'') right_uid
FROM
    relation A
    LEFT JOIN data B ON A.left  = B.id
    LEFT JOIN data C ON A.right = C.id
;

I did it as LEFT JOIN rather than INNER JOIN in case data is missing from data table

YOUR SAMPLE DATA

drop database if exists gumkins;
create database gumkins;
use gumkins
create table data
(
    id int not null auto_increment,
    uid char(36),
    primary key (id)
);
create table relation
(
    id int not null auto_increment,
    `left` int not null,
    `right` int not null,
    primary key (id)
);
insert into data (uid) values ('466a5039-c4c1-403d-826d-07ed2399e288');
insert into data (uid) values ('188a193f-0638-4b71-9cda-03c30b4e008a');
insert into data (uid) values ('f8ed4eba-635b-4eff-a203-e72a8735fb27');
insert into data (uid) values ('7aa235f0-2441-4a00-bc5b-06194609a3d0');
insert into relation (`left`,`right`) values (1,3);
insert into relation (`left`,`right`) values (4,2);

YOUR SAMPLE DATA EXECUTED

mysql> drop database if exists gumkins;
Query OK, 2 rows affected (0.02 sec)

mysql> create database gumkins;
Query OK, 1 row affected (0.00 sec)

mysql> use gumkins
Database changed
mysql> create table data
    -> (
    ->     id int not null auto_increment,
    ->     uid char(36),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table relation
    -> (
    ->     id int not null auto_increment,
    ->     `left` int not null,
    ->     `right` int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into data (uid) values ('466a5039-c4c1-403d-826d-07ed2399e288');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (uid) values ('188a193f-0638-4b71-9cda-03c30b4e008a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (uid) values ('f8ed4eba-635b-4eff-a203-e72a8735fb27');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (uid) values ('7aa235f0-2441-4a00-bc5b-06194609a3d0');
Query OK, 1 row affected (0.01 sec)

mysql> insert into relation (`left`,`right`) values (1,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into relation (`left`,`right`) values (4,2);
Query OK, 1 row affected (0.00 sec)

YOUR SAMPLE DATA LOADED

mysql> select * from data;
+----+--------------------------------------+
| id | uid                                  |
+----+--------------------------------------+
|  1 | 466a5039-c4c1-403d-826d-07ed2399e288 |
|  2 | 188a193f-0638-4b71-9cda-03c30b4e008a |
|  3 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
|  4 | 7aa235f0-2441-4a00-bc5b-06194609a3d0 |
+----+--------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from relation;
+----+------+-------+
| id | left | right |
+----+------+-------+
|  1 |    1 |     3 |
|  2 |    4 |     2 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     IFNULL(B.uid,'') left_uid,
    ->     IFNULL(C.uid,'') right_uid
    -> FROM
    ->     relation A
    ->     LEFT JOIN data B ON A.left  = B.id
    ->     LEFT JOIN data C ON A.right = C.id
    -> ;
+--------------------------------------+--------------------------------------+
| left_uid                             | right_uid                            |
+--------------------------------------+--------------------------------------+
| 466a5039-c4c1-403d-826d-07ed2399e288 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
| 7aa235f0-2441-4a00-bc5b-06194609a3d0 | 188a193f-0638-4b71-9cda-03c30b4e008a |
+--------------------------------------+--------------------------------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Postgres develops server doesn't listen error spontaniously

This problem was resolved with the following actions:

Postgres 9.3. was uninstalled

Postgres was re-installed with a different data directory

The postgresql-x64-9.3 service was now running.

I stopped postgresql-x64-9.3

Then I cleaned out all the folders in the new data directory (I kept the new config files, in case they were the source of my problem).

I copied across my old data directory files (as per https://stackoverflow.com/questions/3305961/recover-postgresql-databases-from-raw-physical-files)

I restarted postgresql-x64-9.3

The old databases were now working

Thanks everyone for your suggestions


TSQL - Intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF)

When you`re creating a scalar function you define the return type

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500)
AS
BEGIN
RETURN (SELECT dbo.usernames(@user_id))
END
GO

Which was NVARCHAR(500) in this case and SQL server does not need to check the underlying structure of referenced functions/tables as long as knows the return type.

It will fail to execute tho, if usernames function (in this case) would return something other than NVARCHAR(500), which is expected

On the other hand

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE
AS
RETURN
(SELECT dbo.usernames(@user_id) as username)
GO

As specified ,returns table therefore SQL server checks the underlying structure of the table because it needs to know what kind of table is begin returned.
How many columns it has,if any of the columns are without names(in aggregate cases) and inform you about it. You can test this by running the query above without referencing the column name( 'as user name' ) in case username table does not have a specified column name.

Note:
I had to tweak this two functions a little bit, to make them work.

Hope this clarifies what is going under the hood for you


sp_executesql: what can be parameterized

Background

The reason why these sometimes don't work isn't specific to sp_executesql, but rather to how variables work in SQL Server. Your command wouldn't work if you used a local variable and no sp_executesql for MAXOP either.

Per the documentation:

  Variables can be used only in expressions, not in place of object names or keywords.

And expressions are defined as:

  ...a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.

To someone very familiar with SQL Server the above will make sense and probably not require further explanation, but I can see how this definition may be lacking. Especially since the TOP usage doesn't really look like an expression (even though the value supplied for it is one).

Something easier

It might be a fool's errand to simplify the above, but I'll give it a shot.

A loose guide that is more descriptive and helpful might be:

  Parameterized values are valid when they affect the results of the
  query.

That is to say that the following would be valid, because they are affecting the results (which rows, how many rows, what value is returned, etc.) but the query is still doing the same thing essentially. That is because they are being used in expressions which modify the query results but do not change the query.

SELECT * FROM Table WHERE Col = @n;
SELECT TOP(@N) * FROM Table;
SET @var = @n;

Whereas these are not valid because they are changing the context of the query, engine settings, or resulting in a different query altogether, etc. Generally changing any of these areas is done through a custom procedure, keyword, function, or by changing objects used in the query and thus do not use an expression.

SELECT * FROM Table OPTION (MAXDOP @n);
USE @n; --Change db context
SELECT * FROM @n;


I need to Select 100 records (or less if there are not 100 records) per group from multiple tables

This should do what you need:

WITH MyCTE as (
    SELECT DISTINCT
        a.transactionId,
        b.dataItemValue,
        c.transactionRoundTypeName,
    FROM TransactionId_tbl a
    INNER JOIN TransactionDataInteger_tbl b
        ON a.transactionId = b.objectId
        AND b.dataItemId = 601257
    INNER JOIN TransactionRoundType_tbl c
        ON b.dataItemValue = c.transactionRoundTypeId
    WHERE b.dataItemValue is NOT NULL
        AND b.dataItemValue != 7
    ) Src
    ORDER BY transactionId,
        dataItemValue
    ),
MyCTE2 as (
    SELECT transactionId,
        dataItemValue,
        transactionRoundTypeName,
        ROW_NUMBER() OVER (GROUP BY transactionId ORDER BY dataItemValue) RowId
    FROM MyCTE
    )
SELECT transactionId,
    dataItemValue as 'Round Type',
    TransactionRoundTypeName as 'Round Type Name'
FROM MyCTE2
WHERE RowId <= 100
ORDER BY TransactionId,
    RowId  -- Proxy for dataItemValue

This gives you up to 100 rows per DISTINCT transactionid and dataItemValue combination, and orders the data by transaction and rowId (proxy for 'Round Type'). If you really WANT it ordered by 'Round Type', change the last ORDER BY to

ORDER BY dataItemValue


Revoking execute access on builtin functions on SQL Server

You cannot restrict permissions to LEFT built-in function.
You can control permissions that are listed by the output of this command:

SELECT *
FROM fn_builtin_permissions(default)


To be able to check all jobs you would need go add the user to the SQLAgentReaderRole in the msdb database which also gives that user permission to create jobs. To minimize the access granted you can also grant the user select permission on the tables used by the sql server agent


MySQL failing and very slow on importing a large file

Using

max_allowed_packet = 256M

in MySQL config allowed to import the files without errors.


You will be able to do this after the restoration process, when the database is in NORECOVERY mode.  Concurrently?  No, there isn't a way.


get max date by comparing total amount with amount in another table

The requirements seem complex but if you calculate the running totals first, it's a simple comparison.

Find the first running_total from b that has covered the running_total from a:

WITH
  rta AS
    ( SELECT [S.no], amount,
             running_total = SUM(amount) OVER
                                 (ORDER BY [S.no]
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
      FROM tableA
    ),
  rtb AS
    ( SELECT trxn_dt, id,
             running_total = SUM(paid_amt) OVER
                                 (ORDER BY trxn_dt, id
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
      FROM tableB
    )
SELECT
    a.[S.no], a.amount, b.trxn_dt
FROM rta AS a
  OUTER APPLY
    ( SELECT TOP (1) b.trxn_dt
      FROM rtb AS b
      WHERE a.running_total <= b.running_total
      ORDER BY b.running_total, b.trxn_dt, b.id
    ) AS b ;

Tested at rextester.com.


Indexes store actual data (data pages or index pages depending on the type of index we are talking about), and Statistics store data distribution.  Therefore, CREATE INDEX will be the DDL to create an index (clustered, nonclustered, etc.) and CREATE STATISTICS is the DDL to create the statistics on columns within the table.

I recommend you read about these aspects of relational data.  Below are a couple of beginner, introductory articles.  These are very broad topics, and therefore the information on them can go very wide and very deep.  Read up on the general idea of them below, and ask more specific questions when they arise.

BOL reference on Table and Index Organization
BOL reference on Clustered Index Structure
BOL reference on Nonclustered Index Structures
SQL Server Central on the Introduction to Indexes
BOL reference on Statistics

Here is a working example to see these two parts in action (commented to explain):

use testdb;
go

create table MyTable1
(
    id int identity(1, 1) not null,
    my_int_col int not null
);
go

insert into MyTable1(my_int_col)
values(1);
go 100

-- this statement will create a clustered index
-- on MyTable1.  The index key is the id field
-- but due to the nature of a clustered index
-- it will contain all of the table data
create clustered index MyTable1_CI
on MyTable1(id);
go

-- by default, SQL Server will create a statistics
-- on this index.  Here is proof.  We see a stat created
-- with the name of the index, and the consisting stat
-- column of the index key column
select
    s.name as stats_name,
    c.name as column_name
from sys.stats s
inner join sys.stats_columns sc
on s.object_id = sc.object_id
and s.stats_id = sc.stats_id
inner join sys.columns c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
where s.object_id = object_id('MyTable1');

-- here is a standalone statistics on a single column
create statistics MyTable1_MyIntCol
on MyTable1(my_int_col);
go

-- now look at the statistics that exist on the table.
-- we have the additional statistics that's not necessarily
-- corresponding to an index
select
    s.name as stats_name,
    c.name as column_name
from sys.stats s
inner join sys.stats_columns sc
on s.object_id = sc.object_id
and s.stats_id = sc.stats_id
inner join sys.columns c
on sc.object_id = c.object_id
and sc.column_id = c.column_id
where s.object_id = object_id('MyTable1');

-- what is a stat look like?  run DBCC SHOW_STATISTICS
-- to get a better idea of what is stored
dbcc show_statistics('MyTable1', 'MyTable1_CI');
go

Here is what a test sample of statistics can look like:

Notice that Statistics are the containment of the data distribution.  They help SQL Server determine an optimal plan.  A good example of this is, imagine you are going to life a heavy object.  If you knew how much that weight because there was a weight marking on it, you'd determine the best way to lift and with what muscles.  That's sort of what SQL Server does with statistics.

-- create a nonclustered index
-- with the key column as my_int_col
create index IX_MyTable1_MyIntCol
on MyTable1(my_int_col);
go

-- let's look at this index
select
    object_name(object_id) as object_name,
    name as index_name,
    index_id,
    type_desc,
    is_unique,
    fill_factor
from sys.indexes
where name = 'IX_MyTable1_MyIntCol';

-- now let's see some physical aspects
-- of this particular index
-- (I retrieved index_id from the above query)
select *
from sys.dm_db_index_physical_stats
(
    db_id('TestDB'),
    object_id('MyTable1'),
    4,
    null,
    'detailed'
);

We can see from the example above that the index actually contains data (depending on the type of index, leaf pages will be different).

This post has only shown a very very very brief overview of these two large aspects SQL Server.  Both of these could take up chapters, and books.  Read some of the references, and then you will have a better grasp.


Moving MySQL database from UniServer to Ubuntu Server?

The simplest option is to just back up everything in the filesystem and carry it over.

To find out where your data is, look at the datadir variable with in mysql

test> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /var/lib/mysql/   |
+---------------+-------------------+
1 row in set (0.00 sec)

Note Your actual value may differ.

Then from your shell shut down mysql, back it up and copy it over.  Something like:

> sudo service mysql shutdown
> cd /var/lib/mysql
> sudo tar -czf /path/with/enough/space/db.tgz *

Copy your db.tgz over to your new server and uncompress it on your new servers datadir while it is shutdown


Cannot restart or repair mongodb database after the crash (***aborting after invariant() failure)

After an unexpected crash, I can't restart ... the mongodb database

Time to exercise your Recovery Strategy, put your Recovery Plan into operation and get your database back from the Backups you've taken of it.
Your database is actually remarkably resilient.
Left to its own devices (and all other things being equal) it will recover from a server crash all by itself.  This is called Instance Recovery and all the "real" DBMSs do it completely automatically.
However, if your database can't recover on its own, then it's time to seriously consider putting that database out of it's misery and resurrecting it from Backup.  Blithely jumping with "repair" tools is a Sure-Fire(TM) way to lose Data.
Now, don't get me wrong - losing Data is not always bad ...   but only if it's written down in your Recovery Strategy, which would define, amongst other things:

How long you can take to recover your database, and
How much Data you're allowed to lose in doing so.

Your Backup plan should support your Recovery Strategy.
NB: No sensible Recovery Strategy should ever rely on being able to "repair" the database which, by that point, is basically broken.  Always make sure you can get back to a stable, known, Good State (i.e. the last Backup) and that you have practised performing that recovery work.


SSIS package randomly fails with "WriteToServer requires an open and available Connection" error

In my experience I have only removed the package and republished it ...


Seems SQL Server couldn't optimize optional parameters

both queries are identical because the @location parameter is optional and it is null

They are on this invocation, but the execution plan for the query will be cached and may be reused when the @location parameter has a different value. SQL Server has to build a plan that will produce correct results for all possible cases.

To resolve this, you could write the query using Dynamic SQL, or you could use an option (recompile) query hint. More details, including the trade-offs for each method, in the references below:

Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White
Dynamic Search Conditions - Erland Sommarskog
Catch-all queries - Gail Shaw


SQL Server Configuration Manager 64-bit Nodes Missing on 64-bit WIndows 10

Right click the Config Mgr shortcut in the Start Menu, open the file location, change the shortcut Target to:

C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager13.msc

The *.*14.msc was my issue.


Truncate Operation Drops the existing table and recreates it .its a fast process . so we cannot get the deleted records.

Solution to this issue is to do a regular backup of the DB on a a daily or weekly basis , in order to save or restore the tables data  as per requirement . and reuse it

Another solution is to export the table data before truncate and keep the data for Future reference


Read Committed Snapshot Isolation vs Read Committed - pros and cons?

By default, SQL Server writers block readers and vice versa if Read Committed  Snapshot Isolation is not enabled. If the application is not written exclusively for SQL Server, it may cause lots of extra locks (SQL Server can also escalate locks) when database is used more or less extensively.
Enabling Read Committed Snapshot Isolation solves this issue and improves concurrency.

On the other hand, it adds load on tempdb (never a problem from my experience at least for SQL Server 2008R2 and higher) and may potentially cause some consistency issues for applications written for SQL Server only. Note, it has nothing to do with dirty reads.


I have this two models, which one is better?

I would use your second model.  The first might be more compact, but would require constantly CASTing your data; the second will have many NULLs, but they take up little space.

The second model looks denormalized at first, but if I understand your model right it's really not.  You're not really packing 22 records into one, rather you're storing reports each of which may have up to 22 distinct strongly-types measurements.  Some reports do not have every measurement, and that's fine.


What practices will help me write an efficient query against a large partitioned table?

Partitions do not really matter if you have proper indices. So, the practice is to learn your tools.

Partitions are not more effective than an index - they are there for managing data, i.e. fast delete (swapp out a partition in a millisecond instead of spending hours deleting 12 billion rows in it).

So, the question is: How do I make this query faster - without partition.

Start by deleting the distinct. Go on by actually givine a query plan and - with 2200 clients, please tell us about the server cluster you use to handle the load.


Galera clustering does essentially what you describe.  Each "node" acts like a MySQL server for reading and writing.  Each node always contains the same data.

Under the covers is M-S / M-M, plus extra glue so that, on the surface, it does not feel like those replication technologies.

Galera is included in PXC and MariaDB, or you can install it on top of MySQL.  Competing products include Clustrix and InnoDB Cluster.

Caution:  3 nodes is recommended; 5 is getting rather large.  To maintain the peer-to-peer, each node needs to talk to each other nodes; the number of connections grows quadratically.

https://mariadb.com/kb/en/the-mariadb-library/galera-cluster/


MySQL cluster 5.6 doesn't convert invalid TIMESTAMP value to '0000-00-00 00:00:00'?

From mysql.com:

  With strict mode
  disabled, invalid dates such as '2004-04-31' are converted to
  '0000-00-00' and a warning is generated. With strict mode enabled,
  invalid dates generate an error. To permit such dates, enable
  ALLOW_INVALID_DATES.

To disable it you have 2 options.

Via SQL: execute SET @@global.sql_mode = ''; query.
Via my.cnf: change sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" to sql-mode = "". Restart MySQL.


MySQL : Update some fields depending on each other

It is bad practice to have "arrays" stored as columns.

Instead, have another table, delete the row with 'U2', and number the entries while SELECTing WHERE ... IS NOT NULL.


Redshift- How to use previous row's calculations in current row

You need recursive common table expresion , feature not available in redshift , try another server for this or use a loop . Lag is not referring computed columns is not helpful in this case

On another sql server it should look like this :

with cter as (
select r,f , wk , bi+r-f as endinv from test_1test where wk = 1
union all
select t.r, t.f , t.wk, c.endinv + t.r -t.f as endinv from test_1test t inner join cter c on t.wk -1  = c.wk
)
select * from cter order by wk


I would recommend looking at the stored procedures that are provided to help manage Log Shipping.  If you Google "sp_refresh_log_shipping_monitor" you will find that it's just one of many stored procedures available.  It appeared to be the most likely to help you in this situation.  Hopefully, it will keep you from having to resort to tearing down Log Shipping and reconfiguring it.


From the documentation relating to OBJECTPROPERTY:

  Remarks

  The Database Engine assumes that object_id is in the current database
  context. A query that references an object_id in another database will
  return NULL or incorrect results. For example, in the following query
  the current database context is the master database. The Database
  Engine will try to return the property value for the specified
  object_id in that database instead of the database specified in the
  query. The query returns incorrect results because the view vEmployee
  is not in the master database.


Is it possible to automatically generate a Unix millisecond timestamp in MySQL 5.7

MySQL 8.0 supports the expression default like you show.
MySQL 5.7 does not support expressions as default. Only NULL, or a constant value, or CURRENT_TIMESTAMP if it's a DATETIME or TIMESTAMP column.
For MySQL 5.7, you have two alternatives:

Declare the column as created_time datetime(3) default current_timestamp(3).

Use BIGINT as you are doing, but write a trigger to set the value.


ASE's (var)char max is 16384. (assuming ASE 15.x/16.x)
If the query can be broken into chunks, with each chunk no larger than 16K, then you could try concatenating them inside the execute() call.
From the documentation for the execute() command:

There are no restrictions to the number of characters supplied with
the literal string.

And from Example 6 (same link) we see a simple example:
execute ('select ' + @columname + ' from ' + @tablename + ' where id=3')


Sql servr 2014 instant to sql server 2008 r2 instance linked servers query problem

What Provider are you using? Try the "Microsoft OLE DB Provider for SQL Server" or the "SQL Server Native Client".
Also ensure the permissions in the linked server are correct, and that the account actually has access to the database on the remote server.


Your BEGIN TRANSACTION is holding a lock on the table. You have to either:

Commit it with COMMIT
Roll it back with ROLLBACK
Use an isolation level hint in your query, like add WITH (NOLOCK) after the MOVIES table name
Use a different default isolation level in your database, like Read Committed Snapshot Isolation, where writers don't block readers (although you won't see data that isn't committed yet)
Run the SELECT query from the same session as your transaction (if you're working in Management Studio, that means running the query in the same tab/window)


How can I limit a user to only be able to run procs in a schema, which access another db?

I am reluctant to post this as an answer, because I don't have a tangible example to post with code, but I pointed Mark to Erland Sommarskog's great article, Giving Permissions through Stored Procedures, which has a section on Signing Procedures with Certificates.

Cert signing seems to be the right approach for forcing data access through a procedure without opening up all kinds of other security holes, which can happen with ownership chaining, using TRUSTWORTHY, elevating the user to a higher role, or granting them direct access to the tables (and thereby giving them a route to bypassing your procedures). I will mock up an example here as time allows (won't be today, unfortunately).


Set mongodb's `maxIndexBuildMemoryUsageMegabytes` setting in conf file

The different approaches for configuring setParameter options (via runtime admin command, config file setting, or command-line option) are listed in the Synopsis section at the top of the MongoDB Server Parameters page you referenced.

You can use setParameter key/value pairs in the YAML configuration file.

For example:

setParameter:
    maxIndexBuildMemoryUsageMegabytes: 700

To confirm this setting has taken effect via the mongo shell, use either of:

// Check the parameter values parsed from config file or command-line options
db.serverCmdLineOpts().parsed.setParameter

// Check the current setting of the maxIndexBuildMemoryUsageMegabytes parameter
db.adminCommand({getParameter: 1, maxIndexBuildMemoryUsageMegabytes: 1})


The only way you can do it properly is storing those two numbers at appropriate intervals (say, daily) somewhere.  There is no history kept.

With some limitations, you can also compare the sizes of your daily backups, until you start collecting the above mentioned metrics.


Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

The myth goes back to before SQL Server 6.5, which added row level locking. And hinted at here by Kalen Delaney.

It was to do with "hot spots" of data page usage and the fact that a whole 2k page (SQL Server 7 and higher use 8k pages) was locked, rather then an inserted row
Edit, Feb 2012

Found authoritative article by Kimberly L. Tripp

"The Clustered Index Debate Continues..."

  Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there.

Edit, May 2013

The link in lucky7_2000's answer seems to say that hotspots can exist and they cause issues.
However, the article uses a non-unique clustered index on TranTime. This requires a uniquifier to be added. Which means the index in not strictly monotonically increasing (and too wide). The link in that answer does not contradict this answer or my links

On a personal level, I have woked on databases where I inserted tens of thousands of rows per second into a table that has a bigint IDENTITY column as the clustered PK.


AWS RDS Really Odd Error... #1041 OUT OF MEMORY, Buffers OK and Memory OK

I do not believe you are alone here. AWS has push me from 5.7.11 to 5.7.17 and now I am unable to perform Alter table commands when there is any sort of memory pressure on the Database.

Have a look at https://forums.aws.amazon.com/thread.jspa?threadID=251866

At this point it looks like there may be an issue present in MySQL 5.6 < 5.6.37 and 5.7 < 5.7.19.

Unfortunately AWS RDS does not have any 5.6 images > 5.6.35 or 5.7 > 5.7.17

Before using 5.7.17 I had used 5.7.11 for some time without issue. If you are able to dump/restore your databases that might be the best option for you, otherwise you can give larger instances a shot.


Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file?

No this cannot damage your Log file.

  Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it?

Yes database compression affects I/O and as far as my experience goes it decreases the I/O and is actually benefitial if you look at I/O consumption. I will tell you how.

Compressed data will take fewer pages when stored on disk hence when data is read fewer I/O is required to fetch data in memory.
Again since fewer pages are bought into memory this increases buffer pool availibility.
CPU is what consumed when compression or decompression happens. But in most cases you would find that benefits gained by space and I/O would outweight the CPU consumption
Please remember a compressed page will remain compressed on disk as well as compressed when brought in memory for reading or writing. So no additional work of decompressing in memory

This is excellent article on data compression, its big, but would help you in understanding data compression

You must remove all SQL Server related folder and files from Antivirus check specially if you have McAfee Antivirus.

  •I am planning to change target recovery time to < 1 minute of data base (specifically tempdb and my database)

I can only say please leave it to default, I am not sure how recovery time has anything to do with log file and its configuration

Regarding Tempdb make sure you have Data files equal to number of physical cores. Paul has more to say about how many files you can have as per cores IMHO you can start with 4 tempdb files but keep monitoring for contention. Make sure that all tempdb data files have SAME INITIAL SIZE and SAME AUTOGROWTH setting. You can also enable TF 1118 to avoid contention

To check contention you can run below query

select
session_id,
wait_duration_ms,
resource_description
from    sys.dm_os_waiting_tasks
where   wait_type like 'PAGE%LATCH_%' and
resource_description like '2:%'

There are some good article for you to read about Tempdb and Contention

Troubleshooting Tempdb Contention by Paul Randal
Recommendation to reduce Allocation contention in Tempdb database
Monitoring Allocation Bottleneck In Tempdb

Edit:

  SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file

This message means that a particular session or query requested for I/O to get data from disk but that session had to wait more than 15 sec and after that request was catered. You can guess that 15 sec is threshold value moment this time is crossed message is dumped in errorlog. This MOSTLY means that disk is not able to cope up with I/O request which is getting generated and in turn means disk might be slow. Since you said 90 % of your errorlog is filled with this information I am forced to believe that underlying hardware is slow or might require a firmware upgrade. This Article will help you in understanding the issue and fixing it


how to "revoke" a "GRANT OPTION" from an mysql user/database?


Is it possible to use the same directory for 2 MySQL servers?

That would not be possible. Why?
MySQL for Windows is very stringent about locking files. It is impossible for two MySQL Instances on Windows to lock on the same ibdata1 files.
From your question, you mentioned launching one MySQL Instance from Windows and another from Linux hitting the same datadir. At the risk of sounding redundant, MySQL for Windows is very stringent about locking files. Trying to lock ibdata1 exclusively, MySQL for Windows will block another instance from anywhere (Windows, MacOS, Linux, etc) from opening a file handle on ibdata1. The reverse is also true: MySQL for Windows will not be able to acquire an exclusive lock on ibdata1 if it is opened from elsewhere (Windows, MacOS, Linux, etc).
I have seen this done using two Linux-based mysql instances, only to see the data go down in flames due to file corruption.
Even if what you asked was possible, there is another issue you have to account for. The following system variables need to be set properly:

lower_case_file_system
lower_case_table_names

The way Windows handles filenames is radically different from Linux. I can easily see foreign key references going haywire when mixing and matching. I have seen one person mysqldump from a Linux instance and load a Windows instance and had all foreign key references vanish on him. Turns out, the source of the problem was the letter casing for the table names. Believe me, you don't want to troubleshoot this one unless aging rapidly is no big deal to you.
SUGGESTIONS
You are much better off doing the following:

Setup MySQL for Windows with its own datadir on a Windows Disk (not using Samba)
Setup MySQL for Linux with its own datadir on a Linux Disk
Setup Replication from the one Instance to the Other (It's your choice which is Master)
Split your reads between the two
Confine writes to just one server
Use TCP/IP to connect to either instance


How to convert an Update a query from MS Access to Oracle SQL Software

Right.  So ORACLE (at least 10g) does not accept JOIN criteria in an Update Statement.  At least not in the way it's written in your post.

Instead, you'll need to write it as a simple SELECT statement (subquery), then WRAP that in an update, OR as an EXISTS.

Here's how to do it in a "subquery".

https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle

I may have missed something, I didn't test this out fully in Toad as I have to work on some tasks.  Anyone please feel free to edit.

*Note: ORACLE does not accept Brackets [], please remove your spacing in the columns when you convert to ORACLE if you're planning to.  Underscores are easier.  The workaround I'm using is Quotes.

Another note, I'm not sure I like the efficiency of the queries.  Seems like it would be more efficient to start at Orders or Order Details vs. Products, especially considering all your WHERE clauses are on the Orders Table.

So your Select validation query would be something like:

SELECT Products.UnitsInStock AS OLDUnitsInStock,
       (Products.UnitsInStock - Products.UnitsInStock - Quantity) AS NEWUnitsInStock
       --FULLY QUALIFY THE QUANTITY COLUMN ABOVE TO AVOID AMBIGUITY
FROM Products
INNER JOIN "Order Details"
    ON Products.ProductID = "Order Details".ProductID
INNER JOIN Orders
   ON Orders.OrderID = "Order Details".OrderID
WHERE Orders.ShippedDate IS NOT NULL
AND Orders.This_Order_Was_Shipped = 'YES'

Your Update would look something like:

UPDATE
(
SELECT Products.UnitsInStock AS OLDUnitsInStock,
       (Products.UnitsInStock - Products.UnitsInStock - Quantity) AS NEWUnitsInStock
       --FULLY QUALIFY THE QUANTITY COLUMN ABOVE TO AVOID AMBIGUITY
FROM Products
INNER JOIN "Order Details"
    ON Products.ProductID = "Order Details".ProductID
INNER JOIN Orders
   ON Orders.OrderID = "Order Details".OrderID
WHERE Orders.ShippedDate IS NOT NULL
AND Orders.This_Order_Was_Shipped = 'YES'
) updateTable
SET updateTable.OLDUnitsInStock = updateTable.NEWUnitsInStock;


COUNT(*) gives more than 1 with LIMIT 1?

You're limiting the resultset of the aggregate function count(), which will always return 1 row. IE: It's limiting the output of the count(*) function, rather than LIMITing just FROM data WHERE datetime < '2015-09-23 00:00:00'.

Basically:

Postgres reads all the rows FROM data WHERE datetime < '2015-09-23 00:00:00'
Postgres then count(*)s them
Postgres then LIMITs that
count

I suspect you're wanting it to do this:

SELECT COUNT(*) FROM (
    SELECT * FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1
);

As this is basically an existence check, one could also do:

SELECT (EXISTS
           (SELECT 1 FROM data WHERE datetime < '2015-09-23 00:00:00')
       )::integer;


Basic information splitting and table design in a database about people, accounts and transactions

It seems a cliché, but it all depends on the specific informational requirements of relevance, because a database has to be designed with the exact business needs in mind.

As for the circumstances at issue:

In case that in the applicable business environment each Transaction is performed from  Person to Person and the concept of Account does not exist, then leaving the tables as you decribe in the no-particular-sintax example seems to be mirroring the characteristics of the business context accurately.
In case that, on the contrary, in said business scenario every Transaction is carried out from  Account to Account, then, yes, creating an Account(s) table which would be referred to from two FOREIGN KEY constraints in the Transaction(s) table would be the best practice, because by doing so you would be reflecting the characteristics of the business domain with precision. The Transaction(s) rows would then be associated with the respective Person(s) rows via the corresponding Account(s) counterparts.

You state in the question itself that

  [...] a person can have multiple different accounts they make transactions from or receive to.

...therefore, proceeding as mentioned in case no. 2 would be more convenient in your very specific situation.

On the other hand, when designing a relational database, I highly advise that you start (i) analyzing the situation from a conceptual perspective —identifying entity types, properties, associations, drawing diagrams, etc.—, then (ii) continue establishing the logical aspects —laying out the corresponding tables, types, columns, constraints to ensure integrity, domains if possible, testing with the normal forms, etc.—, and then (iii) putting up the physical settings of convenience —optimizing database response by configuring indexes, partitions, hardware upgrades, network configurations, etc.—. In this manner you would be approaching each scenario with a clear separation of concerns.

Regarding first normal form (1NF) —which is brought up in the question—, it basically has to do with the definition of atomic domains (i.e., each column must contain exactly one value of the applicable domain/type per row), which, as well is entirely dependent on the informational requirements of significance. Further normalization (2NF and above), in turn, implies getting rid of dangerous redundancies (special types of undesirable data duplication) and is executed in terms of functional dependencies (interrelationships between sets of attributes/columns).

By the way, you may find of interest this Q & A, which involves a similar scenario and contains several notions about database design that may be applied from a general point of view


Database Connection vs Raw TCP Connection

Do databases support multiple transactions simultaneously on a single database connection from client?

For SQL Server, no.

  If not, why not? (as multiplexing would save on resource overhead per connection)

It would seriously complicate the network protocol, which has to be implemented on multiple client platforms, creating a possible source of bugs and performance issues.

And the resource overhead caused by multiple connections is small, and largely mitigated by connection pooling, where a set of long-lived connections is shared among all the threads in a client program.


SQL Server account permissions for New ReportServer database from wizard

@Kin- Thanks for your answer. I was able to refer to this from your answer and get it resolved.
https://social.technet.microsoft.com/Forums/en-US/7541b1ce-aff8-4385-9544-8f9422d01ba7/cannot-alter-the-role-rsexecrole-because-it-does-not-exist-or-you-do-not-have-permission

The fix is weird- this basically covers all the permissions that are collectively included in the RSExecRole but somehow works when executed invidually. Weird!!


You have two options when running code from Python,

Python has a method called autocommit you either have to turn this on,
or, you have to run a manual COMMIT; after you issue the command.

Without either of those the command will automatically ROLLBACK;

answer inspired by @a_horse_with_no_name in the comments


Convert Date format into bigint format in postgresql

postgres=# create table dttest ( dt date ) ;
CREATE TABLE
postgres=# insert into dttest values ( '2018-08-20' ) ;
INSERT 0 1
postgres=# select * from dttest;
     dt
------------
 2018-08-20
(1 row)

postgres=# select to_char( dt, 'YYYYMMDD' ) from dttest ;
 to_char
----------
 20180820
(1 row)

postgres=#

TO_CHAR and other date/time formatting is documented here.

Oh, and if you actually do want to cast to bigint:

select to_char( dt, 'YYYYMMDD' )::bigint from dttest ;


Updating SQL Server 2008 R2 using slipstreamed source

I don't know of a way to do this. When you build the slipstreamed install, it is no longer an SP1 installer, it simply updates the files that the initial setup program uses during installation. It does not build an RTM installer and a separate SP1 installer that are somehow daisy-chained.

So I think the answer is: you will have to download SP1 again, and run it separately from any media where you have created a slipstreamed install.


sp_BlitzIndex gives self-contradictory recommendations

Erik here -- sorry, I'm not Brent, though I do sometimes play him on Office Hours.

You're misunderstanding, and misreading the script output.

sp_BlitzIndex gets its missing index requests from the SQL Server DMVs. We don't just make them up, and we don't tell you to create every single one of them. You have to use some judgement when doing so.

It also doesn't tell you an index is 'too aggressive', though it will let you know if indexes are locked aggressively by queries.

This can happen for a multitude of reasons that I can't begin to troubleshoot for you with the information you've provided, nor is this a good forum to do that -- it would require a thorough review of all of your indexes and potentially code for locking hints, etc.

Hope this helps, and best of luck!


SQL queries to get the duplicate records based on trim

Test data:

create table test_table(unmask varchar(20), mask varchar(20));
insert into test_table values('123', '897609');
insert into test_table values('00123','896509');
insert into test_table values('0000456','5632789');
insert into test_table values('456','32567889');
insert into test_table values('5678','5632789');

I've done this with a CTE, as I love them. casting the string to a number removes the leading characters, which I find easier than messing around with TRIM.

Query:

with duplicates as
(
  select cast(unmask as number) as unmask_num, count(*) as cnt
  from test_table
  group by cast(unmask as number)
  having count(*)>1
)
select *
from duplicates
join test_table on duplicates.unmask_num = cast(unmask as number)
;

DB Fiddle that demonstrates is here.


finding the most recently updated record with multiple date columns

You can get the aggregates, MAX(Date) in this case, by using GROUP BY:

Quoted from MS Docs:

  A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

SELECT
    SerialNumber,
    MAX(Repair_Date) as Repair_Date,
    MAX(Inspection_Date) as Inspection_Date,
    MAX(Replaced_Date) as Replaced_Date
FROM
    YourTable
GROUP BY
    SerialNumber


Microsoft ODBC Driver 11 for SQL Server: Named Pipes Provider: Could not open a connection to SQL Server

Not knowing the full details of your issue by your question try the following:

Option 1:

Temporarily disable Windows Firewall and test your SQL script once more:

Option 2:

Make sure NetBIOS is enabled in the TCP/IP settings of the network adapter you are using:

Option 3:

Ensure that SQL Server protocols are enabled through SQL Server Configuration Manager:

Option 4:

Try a combination of two or more options above.


Is MySQL Replication appropriate for keeping a laptop in sync?

Replication is a tool specifically designed for load handling and failover--not well suited to online/offline syncing. Most replication systems fail immediately on losing connection with a host, and it is up to the admin to resolve the situation, hopefully while one or more of the machines in the cluster are still functional.

What you need is more of a snapshot/restore, or possibly a queuing method. I had one of my team write a simple tool for point-in-time recovery on mysql that may meet your need, although it isn't maintained. We use it in many of our applications.

It uses binary logging to bring hosts up to any point in time. I would suggest continuing replication with the slave server, and using a tool like this for hosts that go offline occasionally.

https://github.com/bryanagee/Pamiris-MySQL-Backup/branches


Can we configure logins to only connect to read-only replicas in Azure SQL Database automatically?

No.  But you can disable their logins on the instance that normally hosts the primary replica and have them connect directly to an instance that normally hosts a read-only replica.


MySQL importing csv file by Data import wizard

Try using mysqldump command for dumping data from source to destination server.
Step1 : Take backup from Source server for particular table.
mysqldump --single-transaction  --set-gtid-purged=OFF  --force  -h 192.168.XX.XX -u user_name -p --databases database_name --tables table_name --lock-tables=false > mysql_user.sql

Step2 : Restore the backup on Destination.
mysql -u user_name -p -h192.168.X.X database_name < mysql_user.sql


Database schema for passing requests to role based users

OK - here's my first pass at answering your question.

If a user has role1 in a given assigment and can have another role in a different assigment, then my schema will have to change. If necessary, this can be looked at. I think that some of your requirements will require more than SQL to fulfill - you will probably have to start looking at triggers.

You haven't mentioned your RDBMS - my schema is for PostgreSQL. For example MySQL doesn't have the capabilities of PostgreSQL, but you can implement many things  triggers and stored procedures. I would strongly recommend PostgreSQL over MySQL if you're going for an Open Source database. Firebird is also a far more capable RDBMS than MySQL. CHECK CONSTRAINTs, REGEXes, CTEs and analytic functions - not implemented (or very poorly so - REGEXes) in MySQL, present in the others.

There are a couple of extra tables in my schema - let me know if this corresponds to your needs. I'm assuming that a request will be broken into assigments - no need to have 4 roles per assignment - the information on who performed each assignment will be in the workflow tables

You could eliminate the workflow table by putting a user_id there and a boolean for completed - maybe simpler? Though having a small assignment table and moving completed assignments to the workflow table might have  favourable performance impact.

DROP TABLE IF EXISTS workflow;
DROP TABLE IF EXISTS assignment;
DROP TABLE IF EXISTS request;
DROP TABLE IF EXISTS my_user;
DROP TABLE IF EXISTS role;

Remove the tables if they exist.

CREATE TABLE role  -- caution, may be a reserved word in some RDBMSs.
(
  role_id SERIAL CONSTRAINT role_pk PRIMARY KEY,
  role_name VARCHAR(10) NOT NULL,
  role_description VARCHAR(100) NOT NULL
);

CREATE TABLE my_user  -- user is a reserved word in PostgreSQL.
(
  user_id SERIAL  CONSTRAINT user_pk PRIMARY KEY NOT NULL,
  user_name VARCHAR(25) CONSTRAINT user_name_nn NOT NULL,
  user_role INT,                   -- NOT NULL not necessary due to FK constraint
  CONSTRAINT user_role_fk FOREIGN KEY (user_role)
      REFERENCES role (role_id) ON UPDATE CASCADE
);

CREATE TABLE request
(
  request_id SERIAL CONSTRAINT request_pk PRIMARY KEY,
  request_name VARCHAR(20) CONSTRAINT rq_name_nn NOT NULL,
  request_user_id INT CONSTRAINT rq_user_nn NOT NULL,
  CONSTRAINT req_user_fk FOREIGN KEY (request_user_id)
      REFERENCES my_user (user_id) ON UPDATE CASCADE
);

CREATE TABLE assignment
(
  assignment_id SERIAL CONSTRAINT assignment_pk PRIMARY KEY,
  assignment_req_id INT CONSTRAINT assgt_req_id_nn NOT NULL,
  assignment_role INT CONSTRAINT assgt_role_id_nn NOT NULL,
  CONSTRAINT asst_req_fk FOREIGN KEY (assignment_req_id)
      REFERENCES request (request_id) ON UPDATE CASCADE
);

CREATE TABLE workflow
(
  workflow_id SERIAL CONSTRAINT workflow_id PRIMARY KEY,
  workflow_req_id INT CONSTRAINT wkflw_req_id_nn NOT NULL,
  workflow_user_id INT CONSTRAINT wkflw_user_id_nn NOT NULL,
  CONSTRAINT wkflw_req_fk FOREIGN KEY (workflow_req_id)
     REFERENCES request (request_id) ON UPDATE CASCADE,
  CONSTRAINT wkflw_user_fk FOREIGN KEY (workflow_user_id)
     REFERENCES my_user (user_id) ON UPDATE CASCADE
);


Replace content of only one table with new version (should disable foreign keys?)

The deferred constraints solution (see an example in the question) actually works if I make the keys deferrable in normal way.

This didn't work because I did UPDATE pg_catalog.pg_constraint SET condeferrable = true; but forgot to set the same for pg_catalog.pg_trigger.


How locate error "Login failed for user 'user_test'" inside huge stored procedure?

SQL Profiler can capture the individual statements in a stored procedure. See details here. Using such capture I have identified statement preceding an error. Then I have searched through SQL code using Toad. Problem was pinpointed. It was something like:
exec sp_executesql 'select top 1 * from openquery(ext_server, ...

If there exists more elegant solution to this - please post an answer here.


performance issue on using ip2location for group of ips

Please qualify all columns with what table they are in.  Meanwhile, I will guess that ip_from and ip_to are in ip_city.

A problem is that the tables are over-normalized.  ip_city -> cities -> country instead of having a single table slows down the query.

But the main problem is that there is no good index for ip between ip_from AND ip_to.  Any index with ip_from and/or ip_to can, at best, limit the effort down to scanning half the table.  That's O(N).  To get to O(1), see http://mysql.rjweb.org/doc.php/ipranges .  Sorry, it is rather involved.

A first cut at improving your existing schema:

CREATE TABLE `cities` (
  `id` MEDIUMINT UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `country_code` CHAR(2) CHARACTER SET ascii NOT NULL,
  `latitude` FLOAT DEFAULT NULL,
  `longitude` FLOAT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ips` (
  `ip` varchar(39) CHARACTER SET ascii NOT NULL,
  ip_packed BINARY(16) NOT NULL
  `user_id` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY(ip)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

CREATE TABLE `ip_city` (
  `ip_from` BINARY(16) DEFAULT NULL,
  `ip_to` BINARY(16) DEFAULT NULL,
  `city_id` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY(ip_from, ip_to),
  INDEX(city_id)  -- if desired
) ENGINE=InnoDB DEFAULT;

That included getting rid if id when there were good 'natural' PKs, handling IPv6, using standard 2-letter country_codes, tossing the country table, using ascii where appropriate, etc.

See my link for further discussion, including why FLOAT is better.


How do I unit test queries which are based on dates from now?

If I have understood it correctly, what you can do is declare a Datetime variable and store current values in it, run your statements and use datetime variable in your where clause.

so

DECLARE @Now DATETIME
SET @Now = GETDATE()

Your Insert Statements

Your SELECT WHERE Date > @Now

does that solve your problem ? or have I miss understood your Question ?