You have 2 issues that I can see:

You cannot pass a VARCHAR(MAX) to xp_cmdshell, largest you can pass is VARCHAR(8000) or NVARCHAR(4000), as per the xp_cmdshell documentation here.
Your temp table (and your Table variable for that matter) are out of scope for other connections, so bcp won't be able to see it.

I worked around these issues with the following:

DECLARE @TestData TABLE (
    pinkpurpleblue VARCHAR(MAX)
)

INSERT INTO @TestData (pinkpurpleblue) VALUES ('xx11'), ('rr22'), ('xx11'), ('rr22')

CREATE TABLE ##distinctValues (
    pinkpb VARCHAR(MAX)
)

INSERT INTO ##distinctValues (pinkpb) SELECT DISTINCT pinkpurpleblue FROM @TestData

DECLARE @file VARCHAR(200), @sql VARCHAR(8000)
SET @file = 'C:\Prod\Results\testing.csv'
SET @sql = 'bcp "SELECT ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpb AS XML) FROM ##distinctValues" queryout "'
SET @sql = @sql + @file + '" -c -t, -T -S '+@@SERVERNAME
EXEC MASTER..xp_cmdshell @sql

DROP TABLE ##distinctValues

Here, I make sure that the type of @sql is VARCHAR(8000) rather than VARCHAR(MAX) and I use a global temp table instead of a local one, so that bcp will be able to see it.


Load Numerical Column with Variable Comma

Here you go:

SQL> desc loadertst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> !cat loadertst.ctl
load data
infile *
into table loadertst
fields terminated by ',' enclosed by '"'
(
col1 "to_number(replace(:col1,',',''))"
)
begindata
"123456"
"1,2,3,4,5,6"

SQL> !sqlldr phil/phil control=loadertst.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Nov 29 23:33:17 2012

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

Commit point reached - logical record count 2

SQL> select count(*) from loadertst;

  COUNT(*)
----------
         2

SQL>

Obviously you may need to fiddle with the fields terminated by ',' enclosed by '"' part to suit your data.


My first guess was wrong so I deleted that answer, the OP simply made 3 backups in the same file, while the last backup was the only backup in the file.

To avoid this issue in future, add with init in your backup command, so even if you press F5 two times at the same minute (your backup file has datetime in its name so it seems that you cannot have more than 1 backup in one file, but your database size is very small so the backup is nearly instant), the first backup will be overwritten with the second and there will be only 1 backup in 1 file.


how to write conditional where clause query

It can be done using some multiple joins from table1 into table2 and then use those joins  combined with case/calculation logic, or using sub-selects.
But it seems rather complex to want to do that in a single query/single join - that I'd recommend finding an alternative way to do it. It's something which is a good candidate to solve in application logic.

However, I think the route you want to go is something like this:

SELECT
  T1.*,
  CASE
    WHEN T2.value = '1' THEN 'CalculateStuffBasedOnT3OrT4'
    WHEN T2.value = '2' THEN
        CASE
          WHEN t3.value IS NULL THEN 'CalculateOtherStuffBasedOnT3OrT4'
          WHEN T3.value = '2' THEN 'CalculateMoreStuffBasedOnT3OrT4'
          ELSE 'SomethingElse'
        END
     ELSE 'Something'
    END
FROM tbl1 T1
LEFT JOIN tbl2 T2 ON T1.id = T2.id AND T2.key = 'm_type'
LEFT JOIN tbl2 T3 ON T1.id = T3.id AND T3.key = 'm_routine'
LEFT JOIN tbl2 T4 ON T1.id = T4.id AND T4.key = 'm_date'

Such a query can then be used as foundation in a subquery to select the posts you want.

But this will quickly become quite complex and unmaintainable.
So I'd advice you to rethink the strategy and go with designing the query dynamically in you php code, or rethink the data structure, or utilize multiple queries to solve it.


SQL 2008 Server - performance loss possibly connected with a very large table

You can use the following query to determine how much RAM is being used by each database:

USE master;

SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory
FROM sys.dm_os_buffer_descriptors bd
    INNER JOIN sys.databases d ON bd.database_id = d.database_id
GROUP BY d.name
ORDER BY d.name;

To see memory used by objects in a specific database, do:

USE <DB_NAME_IN_QUESTION>

SELECT
    obj.name AS ObjectName
    , index_id
    , CAST(count(*) AS BIGINT) * 8192E0 / 1048576 AS CachedMB
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_name(object_id) AS name
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.partition_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY count(*) DESC;

The last column in the above query shows the amount of RAM being used by the given database object.

You can see query stats by looking at:  (among a great deal of other things)

SELECT qs.execution_count
    , qs.total_worker_time
    , qs.total_elapsed_time
    , qs.total_logical_reads
    , qs.total_physical_reads
    , qs.total_logical_writes
    , qt.text /* This is the actual query text */
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.execution_count DESC;


How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?

Scheduler agent is part of database Oracle Home. Anyway must be manually deployed; there is no way to use the database as agent. I think that the documentation is misleading on this case. To help you I will write steps to be performed for agent configuration. I suppose that on the central database you have already enabled HTTP port (etc.).
On the remote database (the one where agent have to be configure):

cd $ORACLE_HOME\bin
extjobo -createagentzip execution_agent.zip

Unzip the file into a directory under the $ORACLE_HOME\scheduler.

cd $ORACLE_HOME\scheduler
unzip execution_agent.zip

Modify the file named schagent.conf located in the $ORACLE_HOME\scheduler\execution_agent directory setting the Scheduler Agent port number to listen for connections. (the one you should have already set with dbms_xdb.sethttpport() )

PORT=<schagent_port>

You have to run the root.sh to fix the uid. (You can follow the README file in the execution_agent directory and run the root.sh). NOTE Verify a directory named /wallet exists under /execution_agent/data before running root.sh. If not create them manually and then run root.sh.

Register the database with.

set ORACLE_SID=<your sid>
cd $ORACLE_HOME\scheduler\execution_agent\bin
schagent -registerdatabase <db_host> <db_http_port>

You will be asked to input the registration password. Last start manually the agent:

schagent -start

These steps will install and configure the agent. Now you can create credentials on the central database and create the database destination. Database destination can exists along with a remote agent destination.


Forget login from connection drop down menu

In SSMS 2008, I believe you need to delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\%Version%\SqlStudio.bin

This will, however, clear ALL of the logins and saved servers.  You may be able to edit the file using an editor, but I believe it would just be easier to start from scratch.

If you are using SSMS 2012 or greater, you can simply delete the server from the dropdown and that will allow you to start fresh with just that server by highlighting it in the dropdown and hitting Delete

See below link for reference to Aaron Bertrand's answer:

https://stackoverflow.com/questions/10993861/how-to-remove-cached-server-names-from-the-connect-to-server-dialog


Unfortunately, no there isn't because ALTER TABLE ... DISABLE KEYS disables nonunique indexes

  For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

If you have to do a bulk insert, you need to use LOAD DATA INFILE along with a larger bulk_insert_buffer_size. Here is why:

  MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.


Figuring out the physical size of a group of tables in SQL server

If you are looking to get the size information for all tables in the database you can use this query:

SELECT O.type_desc,
       IndexSize.obj_name,
       I.name AS index_name,
       IndexSize.reserved_MB,
       IndexSize.used_MB,
       IndexSize.row_count,
       IndexSize.object_id,
       IndexSize.index_id
  FROM (
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(DDPS.object_id))+'.'+QUOTENAME(OBJECT_NAME(DDPS.object_id)) obj_name,
           SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
           SUM(DDPS.used_page_count)/128.0 AS used_MB,
           SUM(row_count) AS row_count,
           DDPS.object_id,
           DDPS.index_id
      FROM sys.dm_db_partition_stats AS DDPS
     GROUP BY DDPS.object_id,DDPS.index_id
   )IndexSize
   JOIN sys.objects AS O
     ON IndexSize.object_id = O.object_id
   JOIN sys.indexes AS I
     ON IndexSize.object_id = I.object_id
    AND IndexSize.index_id = I.index_id;

It returns the reserved megabytes and the used megabytes as well as the row count for every index in the database. The first column tells you the object type. This includes system_tables as well as indexed views. If you want a subset, filter on that column.

If you do not need the per-index detail you can use this query instead:

SELECT O.type_desc,
       ObjectSize.obj_name,
       ObjectSize.reserved_MB,
       ObjectSize.used_MB,
       ObjectSize.row_count
  FROM(
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(IndexSize.object_id))+'.'+QUOTENAME(OBJECT_NAME(IndexSize.object_id)) obj_name,
           SUM(IndexSize.reserved_MB) AS reserved_MB,
           SUM(IndexSize.used_MB) AS used_MB,
           MAX(IndexSize.row_count) AS row_count,
           IndexSize.object_id
      FROM(
        SELECT SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
               SUM(DDPS.used_page_count)/128.0 AS used_MB,
               SUM(row_count) AS row_count,
               DDPS.object_id
          FROM sys.dm_db_partition_stats AS DDPS
         GROUP BY DDPS.object_id,DDPS.index_id
      )IndexSize
     GROUP BY IndexSize.object_id
  )ObjectSize
  JOIN sys.objects AS O
    ON ObjectSize.object_id = O.object_id

It gives the same information, but on an object basis. Again, filter on the object type if you are for example not interested in system_tables.

If you are just interested in normal tables, you can get the information in SSMS too by using the "Object Explorer Details" tab:

You can open it by selecting the table folder in the database in Object Explorer and then pressing F7.

You might have to add the size columns. For that just right-click on the column list and select the columns you want to see:


I face a similar issue with MariaDB when using page compression.
My inserts would be fast, but after a few hours, inserts would 'freeze' for many seconds/minutes at a time.
I tried tweaking the innodb_log_file_size and set it to a higher value (like
56GB) and it reduced the number of times would happen.
https://mariadb.com/kb/en/innodb-system-variables/#innodb_log_file_size
You can also try tweaking innodb_io_capacity and innodb_io_capacity_max
https://mariadb.com/kb/en/innodb-page-flushing/
I got these recommendations from AWS RDS support team.


Xp_delete deletes old backup even when the backup job failed

Raising an error does not necessarily stop the execution of the script and this is why your files are being deleted. See this Connect request for Microsoft's response to a similar question and this Question on StackOverflow for more details.

Overcoming this issue in your particular example is actually pretty simple by using program flow like so:

DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak';

BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10

DECLARE @backupSetId AS INT
SELECT @backupSetId = position
  FROM msdb..backupset
 WHERE database_name=N'MYDB'
   AND backup_set_id = ( SELECT MAX( backup_set_id )
                           FROM msdb..backupset
                          WHERE database_name=N'MYDB' );

IF @backupSetId IS NULL
BEGIN
     RAISERROR( N'Verify failed. Backup information for database ''N'MYDB''' not found.', 16, 1 )
END
ELSE
BEGIN

RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId

DECLARE @cutoff NVARCHAR(50) = CONVERT(NVARCHAR(25), DATEADD(HOUR, -20, SYSDATETIME()), 120 );

EXECUTE master.dbo.xp_delete_file 0, N'V:\Backup', N'bak', @cutoff
END

If you has a more complicated example I would recommend a try-catch block but I don't think that is needed here.


Searching through dates query not working?

(This is why we asked for the datatype of date_created!)

Assuming date_created is a TIMESTAMP:

2018-11-11 is an arithmetic expression that evaluates to 1996; certainly not what you wanted.

"2018-11-11" (with quotes) will compare correctly to 1446976737.

UNIX_TIMESTAMP(ts_column) will generate something like 1446976737, so it should work correctly.  But don't "hide a column in a function".

As for

`e`.`date_created` AS `date_created`

Since the alias (AS...) does not do anything extra, leave it off.  I am worried that

AND  `e`.`date_created`

will look at the alias, not the value.  It needs to look at the value to use this:

INDEX(date_created)

Do you have that index?

By using >= and <= you are including midnight from both ends; did you intend to do that?

WHERE AND is syntactically incorrect.  Did you leave some stuff out?  It may be important.  If you have

WHERE x = 1
  AND date_created ...

then a better index would be

INDEX(x, date_created)

1446976737 dates back a few years:

mysql> SELECT FROM_UNIXTIME(1446976737), UNIX_TIMESTAMP("2018-11-11");
+---------------------------+------------------------------+
| FROM_UNIXTIME(1446976737) | UNIX_TIMESTAMP("2018-11-11") |
+---------------------------+------------------------------+
| 2015-11-08 01:58:57       |                   1541923200 |
+---------------------------+------------------------------+

When you can't figure out what to do, here's a hint:  See what you get from SELECT * FROM tbl.  Then put quotes around something in that format.  In the case of a TIMESTAMP column, you will get something like

2015-11-08 01:58:57

So, you need date_created = "2015-11-08 01:58:57"

Assuming date_created is INT:

Either of these is a valid way to compare:

date_created >= 1446976737
date_created >= UNIX_TIMESTAMP("2015-11-08 01:58:57")

It is still valid, and wise, to have the index(es) mentioned above.


How to produce an output of fields that are NULL (combined from multiple columns+tables)?

Try this:

WITH CTE(Assessor,Date,Field) AS (
select Assessor,Date,Field
from(
select Assessor,Date,ISNULL(Length,'Length') as Length,ISNULL(Colour,'Colour') as Colour,ISNULL(Weight,'Weight') as Weigth
from TestTable) as OP
unpivot(
Field for Fields IN (Length,Colour,Weigth)) as UP)
select Assessor,Date,Field from CTE
where Field in ('Colour','Length','Weigth')


You half miss the connection between the two tables.  The ...relid columns must match, too:

SELECT attname, c.*
  FROM pg_attribute a
  JOIN pg_constraint c
    ON attrelid = conrelid -- this was missing
   AND attnum = ANY (conkey)
 WHERE attrelid = 'test_table'::regclass;


How to fix errors reported by DBCC CHECKDB

The output of DBCC CHECKDB has told you exactly what to do:

USE [master];
GO

ALTER DATABASE houseme SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB(N'houseme', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE houseme SET MULTI_USER;

In case the name of the option isn't obvious enough, this can lead to data loss - but without backups, you don't really have any other choice.

If this doesn't work, and you don't have backups, well, you may have just learned a very expensive lesson about why you should have backups. You can see if Paul Randal has some other ideas, or this idea from Steve Stedman. No guarantees, of course.

If you still have consistency errors after multiple tries, it might be time to create a new database, migrate as much consistent data as you can, and cut your losses.


Optimizing a query that gets the opening balance

I created a minimal, complete, and verifiable example for your query.

CREATE TABLE oss_collection_history
(
    org int
    , Balance int
    , DateFrom date
);

INSERT INTO oss_collection_history VALUES (1, 1, '2019-01-01');
INSERT INTO oss_collection_history VALUES (1, 2, '2019-01-02');
INSERT INTO oss_collection_history VALUES (2, 1, '2019-01-03');
INSERT INTO oss_collection_history VALUES (2, 2, '2019-01-04');

CREATE INDEX oss_collection_history_Charges_IDX2
ON oss_collection_history (org, DateFrom, Balance);

This query returns a single row-per-org, with the associated opening balance, using the index:

SELECT och1.org
    , och1.Balance
    , och1.DateFrom
FROM oss_collection_history och1
INNER JOIN (
    SELECT MIN(och2.DateFrom) AS MinDateFrom
        , och2.org
    FROM oss_collection_history och2
    GROUP BY och2.org
) t ON och1.org = t.org AND och1.DateFrom = t.MinDateFrom;

Results:

╔══════╦══════════╦════════════╗
║ org  ║ Balance  ║  DateFrom  ║
╠══════╬══════════╬════════════╣
║   1  ║       1  ║ 2019-01-01 ║
║   2  ║       1  ║ 2019-01-03 ║
╚══════╩══════════╩════════════╝

This fiddle for this is here.


Switching to Simple Recovery - shrinking transaction logs

First of all I'd like to again point out that, in most cases, SIMPLE recovery is not recommended for a production database.  However, if that is something you wish to change the best way I've found to shrink the LOG file, after you put the database into SIMPLE recovery model, is as follows.  This will not only accomplish releasing free space from the log, but also shrinking the file down to its smallest possible size.

As you stated, shrinking also is not normally a good practice, but there are times when it is warranted.  There are many articles pro/con about it, and I agree with those that will tell you to never use DBCC SHRINKDATABASE; always use DBCC SHRINKFILE if you absolutely have to do a shrink.

However, the log file does indeed have to have a reasonable size to it, even in the SIMPLE recovery model, as that is where information is kept during transactions and long running SPIDs need more of a log file.  Among many reasons, one of those is for roll-back in the event of a problem.  As I stated above, these scripts will reduce your log file to its absolute smallest size possible, but because you need at least some log space you should follow up by regrowing it manually and also make sure your "auto grow" is set to a reasonable number; arguably, a percentage is to always be avoided.  Shrinking the log to its smallest size and the regrowing it manually accomplishes a very important process - that of keeping your VLF fragmentation/counts down.  That's another topic I wont go into here, and many sites including Brent Ozar can go into the actual statistics and testing showing why, but always grow your log file by 8 GB per growth if it needs to be that large or larger.  You mentioned above 25 MB and I can't imagine that being large enough.  I personally manage over a hundred databases at the moment, and less than a dozen are set to 64 MB - All others are much larger ranging up to 64 GB.  Your actual SQL activity and queries will determine what your optimal size should be.  At minimum I'd go 512 MB to 2 GB and set your growth to the same until you know what your log file should sit at.

I find running each line separately works out the best, ensuring success before going to the next step.  The final line of course is what regrows your log to the size you set.  I have it at 8 GB, but you can change that to fit your needs.  If you do not at this time want to fully shrink and then regrow remove the last line and change the "1" in the line above to your size choice.

One last thing before you go out and shrink your log file.

IMPORTANT:  Any time a log file grows, manually or automatically by SQL, essentially your entire database is locked until the grow is done.  Pick a good time when doing that won't impact active transactions -  if you have "after hours" or maintenance windows.  Such a small size of 1 GB or less should be fairly fast and invisible to the end user, but that will truly be determined by your infrastructure.

USE [DatabaseName]

DBCC SHRINKFILE (N'LogicalName', 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'LogicalName', 1)

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'LogicalName', SIZE = 8192 MB )


Finding out who changed the owner of a database when performing a restore

I'm not aware of a way to find out who changed a db owner without some additional logging in place such as Auditing, but you can definitely tell who restored a database by checking the restorehistory table in the msdb database:

SELECT  *
FROM    msdb.dbo.restorehistory

If your coworker restored the database using impersonation they may be telling the truth as the database would be restored by the account being impersonated and this account would end up as the database owner.  The impersonated user would show up as the user_name in the restorehistory table, so it'd be the first place I'd recommend you look.

Maybe someone else has a trick up their sleeve, though.


How to write case when in where statement in Oracle

You're mixing up two ways of doing case. You either need:

CASE <expression>
WHEN <comparison expression> THEN <return expression>
…

or

CASE
WHEN <condition> THEN <return expression>
...

your query is malformed

SELECT count(*)
FROM  userTable
WHERE ( CASE WHEN mac IS NULL THEN mac IS NULL ELSE mac = '000fe95erd32' END )

your clause is not well good. you can do it differently
 like this :

SELECT count(*)
FROM  userTable
WHERE mac is null or mac = '000fe95erd32';

You can read here for further information.


Case insensitive comparison is always costly than case sensitive one. The documentation also cites that in Limitations section. As I understood, the main problem is that you have a performance issue and you cannot locate the culprit(s). I suggest you to use pgBadger or pg_stat_statements to detect slow queries.


Is it a bad practice to query pg_type for enums on a regular basis?

What @Jack said. Plus, if all you need is the list of registered values for an enum type, there are some Enum Support Functions to do that. Based on Jack's example:

SELECT enum_range(null::mood);

enum_range
--------------
{sad,ok,happy}

That's simpler and resilient against (unlikely) changes in future major Postgres versions that might break your query.

dbfiddle here


The database engine won't care about the extension, it is just a logical name for humans to understand what they are seeing.


MySQL Slave SQL Running State - “Waiting for dependent transaction to commit” meaning

If slave_parallel_workers is changed from default 0 value then a number of events applier threads will be launched. Independent events are applied in parallel. If some event is dependant on another one (say INSERT and then an UPDATE to the same row), an UPDATE event will be applied sequentially after the INSERT. Threads dependant on some other event becomes in "Waiting for dependent transaction to commit" state.
This is not a problem but rather a sign that some queries are long enough to create the trailing queue of dependant queries. Some optimization may be requiered but that is not mandatory.


The question really depends on what kind of querying of the JSON data you're doing, but I suspect if your data grows large enough (or when your use cases become complex enough) you'll find the file processing operations you're doing with fs-extra will be outperformed by the equivalent operations in a database, such as MySQL, especially because you mentioned the JSON file is accessed remotely since it's stored on the server, rather than the client's end.
I think local JSON files are good on the client's end for simple things like user app settings and user preferences, and fine to store on the server for basic things like the navigation menu's structure of the website, for example. But I wouldn't plan to store the main data of the application in a file, rather you'll likely be better off storing it in a database.


SQL Server does not rely on the binary order for its "own" data types. For CLR datatypes you could use the iComparable interface, but as @MattJohnson mentioned, SQL Server ignores it:

http://connect.microsoft.com/SQLServer/feedback/details/252230/sqlclr-provide-the-ability-to-use-icomparable-or-a-similar-mechanism-for-udts

Microsoft does not publish the details about how the different datatypes are stored and worked with. However Books Online explicitly states that you cannot rely on a specific binary format for a specific datatype and that the format they use might change any time. So it is a good idea to store an INT as just that and not as VARBINARY, because you might not be able to read your data anymore after the next SP.

As for the sorting: Most of the SQL Server core is written in C++. I assume internally a method similar to an iComparable is used. But again, there is no publicly accessible documentation about this available. Even if it were, you probably would not be able to exploit it because of the inherent differences between .NET and C++.


VIEW it is just a query (since mysql does not start support materialized views)

as a query - it doesn't store data and doesn't need to have any keys.
and view - does not replicate across a cluster and/or replicas, it just executed over local tables.

but underlying tables (included in a views query), yes tables included in this query must have.


Do you set an index on JOIN clauses or where clauses, or both?

I would recommend to always put a non-clustered index on the columns that will be used in JOIN conditions - the foreign key columns. This helps in several ways - JOIN operations will be faster, and enforcing the FK constraint (checking whether there's a child row attached when attempting to delete the parent row) will also benefit from those indices.

Then check to see how your system performs. If it performs below your expectations - carefully add one index and see if the overall system performance improves. If not: remove the index again. Repeat over until you're happy with the performance. Columns used in WHERE or ORDER BY clauses are the prime candidates for those indices - but don't over-index! That's even worse than having no indices at all.

See Kimberly The Queen of Indexing Tripp's excellent blog post - Indexes: just because can, doesn't mean you should! on that very topic.


Mariadb default logs location & --log-error

Going off the documentation, the error log should be in the default location of /var/lib/mysql and it should be named the default hostname.err, but it's just not there.

In the directory /var/lib/mysql I do have a tc.log, iblogfile0, iblogfile1, aria_log.00000001, and aria_log_control files. But I cannot read any of them, they are encrypted. Anyone know what these are?

  BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?

After running the following command,

cat /etc/systemd/system/mysqld.service | grep "mysql"

we can see that the line that systemd uses to start the process is ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION. The options passed to MariaDB are stored in the $MYSQLD_OPTS variable.

If we also run cat /etc/systemd/system/mysqld.service | grep "MYSQLD_OPTS" we get the following.

MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
Use the [Service] section and Environment="MYSQLD_OPTS=...".
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

The file MY_SPECIAL.conf does not exist on my machine, as find / -name "*MY_SPECIAL*" reveals.

I do have entries for mysqld in journalctl. So I'm thinking that it uses the the systemd system logs by default for us systemd users.

On the MariaDB Knowledge base page for the error-log there is a paragraph that states.

  systemd has its own logging system, and Linux distributions running systemd may log errors there instead. To view the systemd logs, use journalctl -u mariadb.

This answers the question because --log-error would be an option that could be added to MY_SPECIAL.conf file and journalctl is where mariadb logs errors by default on a systemd system.


LIMIT is applied late in the query, after aggregation, so you get the complete first group.
You should apply the LIMIT in a lateral subquery:
SELECT users.*, p.action
FROM users
   CROSS JOIN LATERAL (SELECT permissions.action
                       FROM permissions
                       WHERE permissions."userId" = users.id
                       ORDER BY permissions.id
                       LIMIT 1) AS p
WHERE users.id = 1;

And alternative is the non-standard DISTINCT ON:
SELECT DISTINCT ON (users.id)
       users.*, p.action
FROM users
   JOIN permissions AS p
      ON p."userId" = users.id
WHERE users.id = 1;
ORDER BY users.id, permissions.id
LIMIT 1;


You need to group by a substring of source:

SELECT shop_id,
       substring(source,1,3),
       COUNT(DISTINCT user_id) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, substring(source,1,3);

http://rextester.com/EFZZSG27212


This will do the trick in recent versions of SQL Server and Oracle:

SELECT ID, Flag,
  LAG(Flag) OVER(ORDER BY id) AS ShiftFlag
FROM tab
ORDER BY id;

If you don't have LAG(), for example in MS SQL Server 2008, you can use ROW_NUMBER() like this:

;WITH num AS (
  SELECT ID, Flag, ROW_NUMBER() OVER(ORDER BY id) AS n FROM tab
)
SELECT t1.ID, t1.Flag, t2.Flag AS ShiftFlag
FROM num t1
LEFT OUTER JOIN num t2 ON t2.n = t1.n - 1
ORDER BY t1.ID;

Tested on SQLFiddle.


assignment in trigger function does not work

I found that changing the table trigger to a BEFORE trigger allowed the function to work as desired. While I am glad that it works and now understand that NEW is only valid in a BEFORE trigger, I am left with a question: what is the correct way to refer to the inserted or updated row in an AFTER trigger?


It could be as simple as:

GRANT SELECT ON SCHEMA::[sitename] TO [user];

If there are procedures and functions you will also need:

GRANT EXECUTE ON SCHEMA::[sitename] TO [user];

The full list of permissions applicable at the schema level is listed in the documentation:

GRANT Schema Permissions (Transact-SQL)

You can also set the default schema for any user, which may be helpful in some scenarios (for example, if they create tables without explicitly specifying the sitename schema):

ALTER USER [user] WITH DEFAULT_SCHEMA = [sitename];

Note that DENY overrides GRANT (and also overrides role membership rights, including db_owner), so check to be sure someone hasn't inadvertently blocked access to the schema(s). You can start your investigation if you find any rows here:

SELECT * FROM sys.database_permissions
  WHERE class_desc = N'SCHEMA' AND [state] = 'D';

Note that permissions can apply to a user indirectly (e.g. through AD group membership or database roles), so make sure you follow the bread crumbs for any database principal with a relevant DENY...


How to copy MySQL Database from Linux to Windows?

Linux -> Windows you have two obvious options.

Setup a SSHD on your Windows machine and use the above command (mysql binary would need to be in your search path under Windows).
Configure your root (or similar privileged account) to access your Windows MySQL host over network then do:

mysqldump -u username -p 'password' db-name | mysql -h windowsip -u username -p 'password' db-name

Unfortunately with #2, if you have complex indicies (or huge tables) you'll have issues with net_read_timeout on your Linux host. For most situations I would expect this to just work, though.


SQL Server 2012 Managment Studio not saving auto recovery files

According to AutoRecover information is not saved, SSMS 2012 only saves recovery files for queries that have been previously saved (which has led to some complaints). Files that have not been saved seem to get dumped in C:\Users\%USERNAME%\AppData\Local\Temp.

So you can check:

C:\Users\%USERNAME%\Documents\SQL Server Management Studio
C:\Users\%USERNAME%\AppData\Local\Temp

Personally I use TFS for version control on my queries and save them before every run. I've lost a fair number of queries in SSMS so I tend to be very careful.

If you can't find them on disk, there is a way to recover queries from the procedure cache as long as you catch them before they get pushed out of the cache. See also Execution Plan Caching and Reuse.


Postgres incomplete startup packet error

There are 3 different items in this question:

Incomplete startup packet occuring at server start is inconsequential, you may ignore it. Read Incomplete startup packet help needed (in pgsql-general mailing-list) for more.
syntax error at or near "exit" at character 1 means that a client issued exit as if it was an SQL statement.
The could not exec error when issuing service postgresql restart looks like a serious installation problem but it's contradicted by the log entry database system is ready to accept connections meaning that the server started up just fine.


Performing frequent backups of a large MySQL slave

With 120GB, there are two ways to get a daily dump.  (Or even hourly.)

With Replication, a Slave machine can be always tracking what is on the Master.  That is, it is an up-to-the-minute dump of the Master, even without pushing the "dump now" button.

With LVM (available on some OSs), you can take a full disk "snapshot" 'instantly', then at your leisure copy the dump to somewhere else.

Perhaps the faster way to use mysqldump with multiple machines is to use

mysqldump ... | gzip | something-to-copy-to-other-machine

By zipping the file before hitting the network, you 'minimize' the bandwidth needed.

See also Percona's xtrabackup for other techniques -- especially full versus incremental backups.


That looks more like a hobt_id than an object_id. Try:

SELECT [schema] = s.name, [table] = o.name, [index] = i.name
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.[schema_id] = o.[schema_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i
ON i.index_id = p.index_id
AND i.[object_id] = p.[object_id]
WHERE p.hobt_id = 1234560112364076
GROUP BY s.name, o.name, i.name;

Also, I recommend opening the .xdl in SentryOne Plan Explorer, which should present you with friendly object names instead of long, indecipherable ids. I explain the deadlock analysis functionality here.


SQL query to find Employees Skiils,Companies and projects

You can solve this types of problems with a simple "For XML Path" clause in sql.
You can do this by:

SELECT
E.EmployeeID,
E.FirstName+' '+E.LastName AS EmployeeName,
(
    SELECT SkillName+', ' FROM Skill WHERE SkillID IN
    (
        SELECT SkillID FROM EmployeeSkill WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Skills,
(
    SELECT Name+', ' FROM Company WHERE CompanyID IN (
        SELECT CompanyID FROM EmployeeCompany WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Companies,
(
    SELECT ProjectName+', ' FROM Project WHERE ProjectID IN
    (
        SELECT Project FROM EmployeeProject WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Projects
FROM EmployeeInfo AS E

UPDATE

You can try this to remove comma(,) at last of skills, companies & project..

SELECT EmployeeID,
EmployeeName,
left(Skills,LEN(Skills)-1) AS Skills,
left(Companies,LEN(Companies)-1) AS Companies,
left(Projects,LEN(Projects)-1) AS Projects
FROM(
    SELECT E.EmployeeID AS EmployeeID, E.FirstName+' '+E.LastName AS EmployeeName,
    (
        SELECT SkillName+', ' FROM Skill WHERE SkillID IN
        (
            SELECT SkillID FROM EmployeeSkill WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Skills,
    (
        SELECT Name+', ' FROM Company WHERE CompanyID IN
        (
            SELECT CompanyID FROM EmployeeCompany WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Companies,
    (
        SELECT ProjectName+', ' FROM Project WHERE ProjectID IN
        (
            SELECT Project FROM EmployeeProject WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Projects
    FROM EmployeeInfo AS E
) AS Tmp


AlwaysOn Availability Groups - Proper Quorum across WAN

For this you would (hopefully) be using AGs in an asynchronous fashion (latency will kill performance otherwise). This means that you will not get automatic failover to your DR site, that's just something to be aware of. I would also, typically, not recommend using less than 3 nodes, but if that is not an option for you...

As regards quorum. You would want to use a file share witness + node majority configuration. Keep the file share witness in your primary site, on a server that is not a part of the WSFC (Windows Server Failover Cluster).

In the event that you need to move to your disaster recovery site, adjust your file share witness to be local to the server there. This will allow you to maintain a voting quorum in your primary site at all times.

Just be very careful when performing patching of your witness server not to end up losing quorum.


Cannot remotely connect to oracle express

More often than not, a firewall on the database vm is the culprit.
You can test this with

telnet oraclehost 1521

from your mac. This should give a quick response. If it does not give a quick response, like not response at all, a firewall is blocking the request. In that case allow traffic on port 1521, or disable the firewall.


You have a performance problem, so investigate it like a performance problem. Use a methodology like Waits and Queues. This will reveal what is the performance bottleneck, including any blocking that may occur. Likely culprits are blocking (your query is blocked by... you and did not do anything in the past 6 hours), log growth, just poor performance (lack of indexes, including on the ##temp_table).

As a side note the use of SET ROWCOUNT to restrict deletes is deprecated and should be replaced with usage of TOP keyword.


How to programatically change the login audit settings?

Sure, here are the commands listed below. (Reformatted from original post for reading ease.)

These can derived from clicking on Server Properties, then choosing the Security tab and changing the Login auditing settings and scripting out the code, instead of clicking on the OK button to close the dialog.  This type of scripting is usually an option on actions taken through the SSMS interface.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 0 -- None
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 1 -- Successful Only
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 2 -- Failed only
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'AuditLevel', REG_DWORD, 3 -- Failed & Successful
GO

So, for your requirement just set the key to 0 (no auditing) then back to 2 (failed only).

But, alas, as Shawn Melton pointed out, it requires the server to be restarted before the change can be applied. Of course, you can program for restarting the server as well. Does allowing a restart (or 2 restarts) fit into your process?


Using Cursor to loop through a Table variable in SQL Server

If possible, just treat this as a set and consider if you can rewrite the --Some operation as a single batch.

It may require you to get smart with joins to other tables based on the date ranges - or perhaps you could split the date range further into StartDate and EndDate columns before doing your additional processing which might help with joins


How to optimize a query that's running slow on Nested Loops (Inner Join)

The problem appears to be in this part of the code:

JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
(
    l.category_id = c4.category_id OR
    l.category_id = c5.category_id
)

or in join conditions is always suspicious.  One suggestion is to split this into two joins:

JOIN category_link l1 on l1.sku_id in (SELECT value FROM #Ids) and l1.category_id = cr.category_id
left outer join
category_link l1 on l2.sku_id in (SELECT value FROM #Ids) and l2.category_id = cr.category_id

You then have to modify the rest of the query to handle this . . . coalesce(l1.sku_id, l2.sku_id) for instance in the select clause.


Combine multiple queries into single query

You can use UNION [ALL] to combine rows from multiple queries, as long as the number of columns are the same and the columns are compatible, if not of the exact same types.

SELECT 'Driver' as SourceTable, Driver_No, Name, Available
  FROM Driver
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Freight', Freight_No, Freight_Wagon_Type_Name, Available
  FROM Freight
 WHERE Available = 'Y'
 UNION ALL
SELECT 'Locomotive', Loco_No, Loco_Name, Available
  FROM Locomotive
 WHERE Available = 'Y';

(added SourceTable column per a_horse_with_no_name's suggestion)

Note that the column names are taken from the first query in the UNION, which means you end up with (SourceTable, Driver_No, Name, Available).  You can always alias them as required.


MySQL specify default value when data has incorrect values

When you need to 'cleanse' the incoming data, there are two techniques to consider.  Suppose the column foo needs to be -1 to represent "NA":

Using @variables...

LOAD into and @variable, then use SET to manipulate the value while storing it.  There are examples in the docs. In your case you might use something like SET foo := IF(@foo = 'NA', -1, @foo) inside the LOAD DATA statement.

Using a temp table...

Instead of LOADing directly into the target table, load into a separate table (possible TEMPORARY).  Then have a few SQL statements to manipulate the data in that table before copying to the "real" table.  In your case:  UPDATE tmp SET foo = -1 WHERE foo = 'NA';.  Then INSERT INTO real SELECT * FROM tmp;.  Note foo would be VARCHAR in tmp in spite of being INT in real.

The temp table approach can handle more complex transformations.


Revoke delete from table for database owner

You cannot revoke privileges that are not granted.

The table (not database) owner implicitly has full rights on the table. They cannot be revoked, except by changing the owner of the table.

What you probably want to do is connect with a user other than the owner of the database/tables, and GRANT that user only the rights it should have over the table(s).


MariaDB/InnoDB: What to do on "Locks in Status-Log but no locked table found"

You are right that the transaction is 'running' for several hours. This basically means you have a query that has failed to run 'commit' and is holding the transaction open.

If you are running in the default REPEATABLE READ transaction isolation level, you will also see your 'history list length' growing large in the TRANSACTION section of the SHOW ENGINE INNODB STATUS output

The lock status in SHOW OPEN TABLES, assuming you meant 'name_locked', is only for whether the name is locked for operations such as dropping or renaming the tables src

I am curious if SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS returns anything, especially if db1.table_a and db1.table_b are InnoDB tables. If they are MyISAM I would urge not using BEGIN TRANSACTION or SET autocommit=0, as you can run into some serious issues with potentially mixing InnoDB and MyISAM tables in a transaction.

Unfortunately, getting access to what that transaction has done is fairly impossible unless you're running the PERFROMANCE_SCHEMA to capture statemen. See: http://www.markleith.co.uk/2012/07/13/monitoring-processes-with-performance-schema-in-mysql-5-6/ Even still, it might not be possible for a transaction active that long depending on your server's activity and performance_schema settings.

So, you're really only left with the choice to kill the thread, which will rollback that transaction (again, assuming it's InnoDB tables being modified). Letting it run until it hits a timeout will result in the same rollback. And rollbacks might take a really long time, depending on what it's done!

I would scour the application's code-base to make sure you are closing/committing every transaction you are creating to prevent this in the future, as long-active transactions and an unruly history list length (undo space) can have a severe impact on the performance of your queries.


Agent Job Running as Computer Domain Account Rather than Service account

The job is running as the same identity in both cases.  This is a difference only in how the information is reported.  NT SERVICE\SQLSERVERAGENT is a least-privilege local identity used for running SQL Agent jobs.  Like other built-in local Windows identities this account can access network resources using the computer account.  So from the local point-of-view Agent is running as NT SERVICE\SQLSERVERAGENT, but from the network point-of-view it's running as the computer account DOMAIN\SERVERNAME$.

I don't know why the difference in reporting between your servers, but it's nothing to worry about.

It looks like sp_whoisactive might get the login name directly from sys.dm_exec_sessions, which returns the domain identity of the login.  If you use the security_id to look up the session's server_principal you'll see the local identities differentiated.  eg:

select s.session_id, p.name
from sys.dm_exec_sessions s
join sys.server_principals p
  on s.security_id = p.sid


Database Normalization Causing Inefficiency

With regard to the threading issue that people have picked up on as much as your question about user id and name normalisation, without using CTEs there are a number of ways to arrange hierarchies to allow efficient retrieval of threads or portions thereof. You could store a materialised path to each post for instance. Efficiently dealing with trees in SQL is a subject whole books could be (and have been) written about, so I'll not go into more detail here. For a quick primer the relevant chapter in Bill Karwin's "SQL Antipatterns" (a book I recommend generally) is a good place to start, or for a fuller analysis Joe Celko's "Trees and hierarchies in SQL for Smarties", or search online for the tree management methods (with search terms like adjacency list, materialised path / cached path / path enumeration, nested sets, and of course recursive CTE / recursive common table expression).

On to the actual question:

  For storing the author of a post, I can store only the ID. However, this means that I will have to make quite a few requests to the Users table for the usernames of the various IDs of the posters. This seems highly inefficient, seeing that the usernames will change very rarely.

This is normal and should not be significantly inefficient. You will not be performing a separate lookup for every user ID spotted, instead you will use a JOIN and let the SQL engine worry about that for you which it will do well if you give it good index choices to work with. Something like:

SELECT posts.postTitle
     , posts.postText
     , posts.postTimeStamp
     , users.fullName
FROM   posts
JOIN   users
       ON users.id = posts.userID

Having said that, it is important to note that data normalisation is not about performance efficiency it is about protecting data integrity/correctness and (less importantly) storage efficiency: in this instance if you avoid having several copies of the same data (the username) you avoid the possibility of inconsistency because one or more copies disagree with the others.

  I can store both the author ID and the author username in the Posts table.

Unless you want to allow usernames to change and keep the original name displayed, this is unnecessary and you should use normal form and a join, as above.

Before trying to support name changes in a particular way, first decide if you actually need to allow them at all. If the name in question is an account name then perhaps you don't need or want to support it changing at all. If it is a personal name or email address, both of which can change over time, then you perhaps want to change all instances when this happens so you don't have to worry about it: just update the name in the users table and future queries like the above will get the new data instead of you needing to

  go through the [posts] table and change all usernames among the posts

at all.

Things get a bit more complicated if you want to maintain the display of the user's name at the time they made a post instead of old posts being displayed with the new name. You could store "name at time of post" in the posts table, and with this requirement you don't need to update it when the user's name changes (you are wanting to keep the old name). The other option is to keep a history of the user's record in normal form and using that to ready the correct name. This will be more efficient for storage (and less likely to become inconsistent due to bugs) but reading the username-at-time-of-post can be more complicated and will vary depending on what your chosen database supports (does it have efficient window functions?) and how you chose to store the history (using "system versioned history table" support in SQL Server or Oracle for instance). I'll not go in depth on this, as I suspect simply letting the displayed name change when the user's details change is perfectly fine and easy to implement.

Side note: key type choice

With CHAR(36) I assume you are using UUIDs for internal user ID? Unless you have reason to use UUIDs (the requirement comes from elsewhere and is not in your control, you are writing a distributed system and are concerned about key collisions for users created in different locations, ...) you might want to consider using an automatically incrementing integer instead (INT NOT NULL AUTO_INCREMENT in mysql/mariadb, INT NOT NULL IDENTITY(1,1) in MSSQL, ...) which will only use 4 bytes per key.

The same for post IDs if you are using UUIDs for them also.

For space efficiency some DBS support packed UUID types (UNIQUEIDENTIFIER in MS SQL Server, UUID in postgres, ...) which will store the value in 16 bytes instead of 36, but I don't think mySQL does. You could use BINARY(16) but you may need to faf around converting back and forth when reading/writing the values so that probably isn't recommended.

Depending on the number of rows you expect the difference in key size may not be significant to worry about, but if you plan for your application to scale into something huge it should be considered in the early design stages (making key type changes is a nightmare once an application has already rolled out!).


Permission Requirement to migrate MongoDB to ApsaraDB for MongoDB

DTS allows you to migrate data from on-premises MongoDB to ApsaraDB for MongoDB. With the data change replication function, you can perform the migration without stopping services.

Migration types

For data migrations from on-premises MongoDB to ApsaraDB for MongoDB, DTS supports the following migration types: migrate existing data and replicate data changes. The features and restrictions of these migration types are as follows:

Migrate existing data

This migrates all existing data in the source instance to the target instance.
Replicate data changes

If you also choose to replicate data changes, the data changes made to the source instance during the migration will be synchronized to the target instance, so that eventually the source instance is dynamically synchronous with the target instance. This allows you to perform the migration while on-premises MongoDB remains operational.

Migration functions

The supported MongoDB versions are 3.0 and 3.2.
MongoDB existing data migration supports:

Database migration
Collection migration
Index migration

MongoDB data change replication supports:

Synchronization of create, delete, update operations on documents
Synchronization of create and delete operations on collections
Synchronization of create and delete operations on databases
Synchronization of create and delete operations on indexes

Migration procedure

The following describes the procedure of configuring a migration task from on-premises MongoDB to ApsaraDB for MongoDB.

Create accounts
When configuring a migration task, you need to provide the accounts of the source and target instances. The accounts must have the permissions listed in the preceding Migration permissions section.

If the source or target MongoDB instance does not have an account with required permissions, create one as follows:

db.createUser({user:"username",pwd:"password",roles:[{role:"rolename1",db:"database_name1"},{role:"rolename2",db:"database_name2"}]})

Parameter description:

username: Username of the account to be created
password: Password of the account to be created
rolename1/rolename2: Roles to be assigned to the username. Example roles are read and readWrite.
database_name1/database_name2: In the preceding example, role1 and role2 of database_name1 and database_name2 are assigned to the username.

You can check this link for more detailed information


Based on your question "What support is there for multiprocessing":

SQL Server CE and SQLLite are single-user DBs that work in your app and deployed as DLLs
SQL Server (Express and above) (and MySQL, Oracle, Postgres et) are server engines where you submit a request

Either you have proper multiprocessing or you don't: not much grey area in between. The rest of your questions don't matter because of this.

An as example, SQLLite locks tables locking so you don't have much concurrency even if it nominally supports multiple processes

Edit, about SQL Server CE from SO

"SQL Server CE in a multi user scenario - is this really, really stupid?"
"What is the number of maximum concurrent connections for Sql CE 3.5?"

It looks like SQL Server Express is recommended for multi process access


Restore database from individual datafile backups

Jay, this isn't officially documented, so I'm speaking only from my own experience.

In RMAN, the command BACKUP DATABASE ... is synonymous with BACKUP DATAFILE 1, 2, ..., n-th ....

Also the command RESTORE DATABASE ...  is synonymous with  RESTORE DATAFILE 1, 2, ..., n-th ....

I'm not sure about RECOVER DATABASE; it might be also a synonym of RECOVER DATAFILE 1,2, ... n-th , although  I've never tested the latter. In your scenario, the former would work as expected.

In particular, neither RESTORE DATABASE or RECOVER DATABASE require all the datafiles to come from "a single backup of database" (from a single run of BACKUP DATABASE). Actually, RMAN doesn't even have a concept of "a single backup of database".


some SQL Server MSX targets show as Normal, Blocked

When using a centralized MSX (Multi-Server SQL Agent jobs), there are some shortcomings in the SSMS GUI for administering those multi-server jobs.

When a target server has a problem downloading instructions for a job, those errors will be listed in msdb.dbo.sysdownloadlist. However, in the SSMS GUI, this only manifests as the job showing as having a "Blocked" status.

You can use EXEC sp_help_targetserver @server_name = 'MyServer'; to see the instructions, including errors & unsent instructions.

Microsoft recommends re-syncing the entire server when you see that it is blocked. This is done by executing the command EXEC sp_resync_targetserver @server_name = 'MyServer';  Doing this will delete & re-create all jobs on the target server. This can be disruptive (you can imagine what might go wrong if you drop & recreate all your jobs at the same time a job should run). Additionally, it will result in losing job history on the target.

Instead, you may choose to manually repair the specific job(s) that are a problem:

DECLARE @server_name sysname = 'MyServer';
DECLARE @job_name    sysname = 'The Job That Broke';

-- Get the Job_ID
DECLARE @job_id uniqueidentifier;
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = @job_name;

--Delete existing download instructions for this server/job
DELETE dl
FROM msdb.dbo.sysdownloadlist   AS dl
WHERE dl.target_server = @server_name
AND dl.object_id = @job_id;

--Post new instructions to delete & re-add job
EXECUTE msdb.dbo.sp_post_msx_operation
                @operation = 'DELETE',
                @object_type = 'JOB',
                @job_id = @job_id,
                @specific_target_server  = @server_name;
EXECUTE msdb.dbo.sp_post_msx_operation
                @operation = 'INSERT',
                @object_type = 'JOB',
                @job_id = @job_id,
                @specific_target_server  = @server_name;


SQL Server : not allocating all memory that it has on disposal

Check the following counters:
select object_name,counter_name,instance_name,cntr_value/1024/1024 [GB]
from sys.dm_os_performance_counters
where counter_name like 'T%Server%'

Total Server memory - current consumption
Target Server memory - intention to consume next


Limit TLS and Cipher Suites on SQL Server 2016-2019

Looks like the same registry entries (SCHANEL) are used for IIS as well, so using the IIS Crypto application should work for both.

IIS Crypto Faq - What registry keys does IIS Crypto modify?

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server\DisabledByDefault
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\NULL\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\DES 56/56\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC2 40/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC2 56/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC2 128/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 40/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 56/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 64/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\RC4 128/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\Triple DES 168\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\AES 128/128\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\AES 256/256\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\MD5\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA256\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA384\Enabled
HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA512\Enabled

These are the advanced keys:

HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms\Diffie-Hellman
HKLM\SYSTEM\CurrentControlSet\Control\Lsa\FipsAlgorithmPolicy

To reorder the cipher suites, it modifies the registry key here:

HKLM\SOFTWARE\Policies\Microsoft\Cryptography\Configuration\SSL\00010002


Comparing the data in two Postgres databases

This question (in some shape or form - no two questions are identical), for example see the threads here and here - I did answer there.

The two most popular (Open Source) tools in this area are Liquibase and Flyaway. There are numerous commercial tools which you can find by Googling.

I would like to echo Paul White's welcome, but just a wee word of warning. Tool recommendations aren't really on-topic here. Check out the tour and also the "help us to help you" blog - both at the bottom left of the page. Also, take a look at the help centre for everything you could ever want to know about dba.se. These forums are a great resource and you'll get the most from them if you take the time to check out and follow the guidelines.

Googling the term "diff tool for postgresql" gives some other ideas. Perhaps a good approach would be to try out a couple (few) of them and if you're having problems or require more specific advice (too broad is also off-topic :-( ) - I got this and this - PostgresQL specific. Best of luck.


For your first question:

Multiply Things you must know before using Galera Cluster.

Galera Cluster is not for scale write
Galera Cluster is not load balancer. It must integrate with HA Tools for this option
It's virtually synchronized Multi-master Replication
You need minimum of 3 nodes/servers(or 2 nodes and 1 abrirator)  to avoid split/brain.
You can't use MyISAM all of tables must be InnoDB.

So if you want to use Galera Cluster I thinks this article is good for start.


Short answer: No.

Detailed answer: Unless you add/already have an index there that covers the country column the database engine has no way of telling the total number of instances to count. Even if you limit the output the entire table has to be scanned to retrieve every instance first. Of course, since this is a logging table, with potentially a large dataset, I'd avoid adding indexes to the table so:

not to increase INSERT time
to minimize space/memory usage the indexes would otherwise require

Solution: create a second table that will keep a running total for all countries, that gets updated by a trigger on the hits_table or directly by the application that inserts the data into hits_table. Something in the lines of:

CREATE TABLE `country_hits_totals` (
  `country` VARCHAR(255),
  `date`    DATE,
  `visits_total` BIGINT
);

This would make the table very small and allow you to create a PRIMARY KEY on the country and date columns, dedicating minimum resources. Once that key is set up, you could employ a trivial upsert statement (see this SQL Fiddle session for a full test case with a trigger-based usage):

INSERT INTO `country_hits_totals` (
  `country`,
  `date`,
  `visits_total`
)
SELECT
  NEW.country,
  NEW.timestamp,
  1
ON DUPLICATE KEY UPDATE `visits_total` = `visits_total` + 1;

Query blocking note: In a heavy traffic scenario you ideally want to query country_hits_totals with non-blocking statements only. Otherwise the upsert on the trigger gets delayed, and by extensions entire transaction that kicked it off. In a perfect world country_hits_totals would get copied over to a data warehouse at the time of least daily traffic, and you'd run your BI queries on that warehouse. That said, this is large traffic/enterprise territory and possible solutions depend on how quickly you want to get the data. Say, if you need an update only once a day you're better off pre-aggregating data onto country_hits_totals on a schedule instead of relying on a trigger updating the rows live.


Not able to install MSSQL 2014 on an additional disk(some disk other than the OSdisk) silently on Windows 2012 R2

Still, everytime, the MSSQL installation happens onto the OSdisk/System Drive/C drive. Why

What you are seeing is called binaries of SQL Server and as per rule binaries always goes on system drive and you have no control over it.

If you read SQL Server file location of default and named instance you can get various default locations.


How do I reraise an exception in a PL/pgSQL EXCEPTION block?

You can use RAISE without any parameters. This is documented on the Errors and Messages page:

  The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block's EXCEPTION clause; it causes the error currently being handled to be re-thrown.

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE;
        END IF;
    END
    $$


Error creating connection pool for dbid:

Connection Pooling occurs completely at the client-side of an application, is seldom affected by the SQL Server directly.

Connection pooling depends on parameters in the connection string used by the client to connect to the SQL Server.  Identical connection strings are typically candidates for pooling, assuming the following connection string settings allow pooling:

╔════════════════╦═══════════════╦═════════════════════════════════════════════════════════════╗
║    Setting     ║ Default Value ║                         Description                         ║
╠════════════════╬═══════════════╬═════════════════════════════════════════════════════════════╣
║ Max Pool Size  ║          100  ║ The maximum number of connections allowed in the pool.      ║
║ Min Pool Size  ║            0  ║ The minimum number of connections allowed in the pool.      ║
║ Pooling        ║            1  ║  Enable connection pooling.  0 Disables connection pooling. ║
╚════════════════╩═══════════════╩═════════════════════════════════════════════════════════════╝

The SQL Server may be running out of service worker threads, if existing connections are not closed properly, which would typically result in connection problems.  If the max worker threads server configuration option is set incorrectly, or too low, that might be a problem.  The default value for this option is 0, indicating that SQL Server will automatically configure the value, according to these rules:

╔══════════════════╦══════════════════╦═════════════════╗
║ Number of CPUs   ║ 32-bit computer  ║ 64-bit computer ║
╠══════════════════╬══════════════════╬═════════════════╣
║ <= 4 processors  ║             256  ║             512 ║
║ 8 processors     ║             288  ║             576 ║
║ 16 processors    ║             352  ║             704 ║
║ 32 processors    ║             480  ║             960 ║
║ 64 processors    ║             736  ║            1472 ║
║ 128 processors   ║            4224  ║            4480 ║
║ 256 processors   ║            8320  ║            8576 ║
╚══════════════════╩══════════════════╩═════════════════╝

You can check the configured value using this query:

SELECT c.name
    , c.value
    , c.value_in_use
FROM sys.configurations c
WHERE c.name = 'max worker threads';

Based on the error message, it looks like your client end is configured incorrectly, but that is only a guess.  Check with your developers or vendor for help determining the cause of the problem.


Record doesn't show if selecting earlier date interval

I think the problem with you query is the use of aggregated functions: sum and count. Using group by category, subcategory  shows only rows with different category and subcategory values: only one row for each category and subcategory.

You can add comments column on your group by clause. Another option is to remove group by clause, sum and count functions, but this second option generates a result without aggregates values.

I hope this helps

Miquel Boada


LEFT vs CHARINDEX for matching the start of a string in T-SQL

The biggest factor that I can think of is that LEFT(MyField,5) and CHARINDEX('HELLO',@MyField) won't use an index unless the index matches the expression exactly.

However MyField LIKE 'HELLO%' will use any index with MyField in it.


How to identify when to move all or part of the data from Sybase ASE to Sybase IQ?

Tough call. Look at it from a transactional RDBMS versus reporting-style OLAP perspective, at a high level. The IQ methodology is column-store, versus a traditional row-store for ASE. Column-stores are very fast when running aggregated queries over them, but that depends on how your reports are run, right?

I think you almost nailed it with this:

  Ok, we have a lot of data archived and need better timings retrieving
  it, we should go to IQ!

So, do your analysis on volume of data you'll be querying all round, that is fairly static or slowly changing. If that's going to be queried a lot, look into IQ.

Don't be scared of a hybrid solution either. I have several SQL Server customers that have massive warehouses running with columnstore indexes, right in the same cabinet as their highly transactional RDBMS.


Getting VLF info with least privileged to run across multiple servers of different version

I don't think you'll find a way prior to 2016.
As of 2016, you can use the DMV sys.dm_db_log_info instead. In fact, your script does just that, checks for the version and uses one or the other command (the other of course being DBCC LOGINFO, which requires sysadmin).
For sys.dm_db_log_info you can just GRANT VIEW SERVER STATE permission.


Mirroring sql server , how to change the port?

Just specify the port when you set it up

ALTER DATABASE <DBNAME> SET PARTNER = 'TCP://<ServerName>.<Domain>.com:5022'


How many files should I create for one filegroup and 9 weekly partitions?

You need one file.

Unless you have specific requirements to have multiple files, you only need one file for the filegroup. The 'specific' requirements would be things like requirement to spread the filegroup on multiple disk locations or a requirement for round-robin allocation. You did not mention any such requirement, you only give information about the number of rows in the tables, which is largely irrelevant when it comes to deciding the number of files in the filegroups.


listagg in order and including a sequence of separator when missing any row of a sequence

We have no problem with the aggregation but there is a special
  condition that we must follow. If a row or more rows, from sequence
  (R, D, K, N) for Atrib1, doesn't exist we must even include a set of
  ';' for each Atrib(1..4).

Suppose you have the following table (called TEST).  Some of the the "nrows" - N2, N3, N4, N5 - do not have a complete set of (R,D,K,N) values.

create table test ( nrows, a1, a2, a3, a4, final )
as
select 'N1', 'R', 'X', 'One', 'A1', 'True' from dual union all
select 'N1', 'D', 'Y', null, null, 'False' from dual union all
select 'N1', 'K', 'Z', null, null, 'False' from dual union all
select 'N1', 'N', 'A', null, null, 'True'  from dual union all
-- N2: D, K, N missing
select 'N2', 'R', 'X', 'two', 'B1', 'True' from dual union all
-- N3: R, K, N missing
select 'N3', 'D', 'Y', null, null, 'False' from dual union all
-- N4: D, R, N missing
select 'N4', 'K', 'Z', null, null, 'False' from dual union all
-- N5: D, K, R missing
select 'N5', 'N', 'A', null, null, 'True'  from dual ;

select * from test ;
NROWS  A1  A2  A3    A4    FINAL
N1     R   X   One   A1    True
N1     D   Y   NULL  NULL  False
N1     K   Z   NULL  NULL  False
N1     N   A   NULL  NULL  True
N2     R   X   two   B1    True
N3     D   Y   NULL  NULL  False
N4     K   Z   NULL  NULL  False
N5     N   A   NULL  NULL  True

As a first step, you could write a query that generates the missing nrows/A1 combinations (NA1), and LEFT JOIN it to the original (O) table.

select *
from (
  select nrows, a1
  from (
     select unique a1 from test
  ) cross join (
     select unique nrows from test
  )
) NA1
left join  (
   select nrows, a1, a2, a3, a4, final from test
) O
on NA1.nrows = O.nrows and NA1.a1 = O.a1
;
-- result
NROWS  A1  NROWS  A1    A2    A3    A4    FINAL
N1     R   N1     R     X     One   A1    True
N1     D   N1     D     Y     NULL  NULL  False
N1     K   N1     K     Z     NULL  NULL  False
N1     N   N1     N     A     NULL  NULL  True
N2     R   N2     R     X     two   B1    True
N3     D   N3     D     Y     NULL  NULL  False
N4     K   N4     K     Z     NULL  NULL  False
N5     N   N5     N     A     NULL  NULL  True
N2     K   NULL   NULL  NULL  NULL  NULL  NULL
N2     N   NULL   NULL  NULL  NULL  NULL  NULL
N4     D   NULL   NULL  NULL  NULL  NULL  NULL
N3     R   NULL   NULL  NULL  NULL  NULL  NULL
N2     D   NULL   NULL  NULL  NULL  NULL  NULL
N3     N   NULL   NULL  NULL  NULL  NULL  NULL
N3     K   NULL   NULL  NULL  NULL  NULL  NULL
N4     N   NULL   NULL  NULL  NULL  NULL  NULL
N4     R   NULL   NULL  NULL  NULL  NULL  NULL
N5     D   NULL   NULL  NULL  NULL  NULL  NULL
N5     K   NULL   NULL  NULL  NULL  NULL  NULL
N5     R   NULL   NULL  NULL  NULL  NULL  NULL
-- 20 rows selected.

Then, use LISTAGG and DECODE (if CASE ... , as used in the example below, is too "bulky") and replace the NULLs with ;

select
  NA1.nrows
, NA1.a1    as "RDKN"
, listagg ( O.a1 || ';' ||
    case when O.a2 is null then '' else O.a2 end || ';' ||
    case when O.a3 is null then '' else O.a3 end || ';' ||
    case when O.a4 is null then '' else O.a4 end || ';'
  , ';' ) within group ( order by O.a1 ) "Attribs 1-4"
from (
  select nrows, a1
  from (
     select unique a1 from test
  ) cross join (
     select unique nrows from test
  )
) NA1
left join  (
   select nrows, a1, a2, a3, a4, final from test
) O
on NA1.nrows = O.nrows and NA1.a1 = O.a1
group by NA1.nrows, NA1.a1
;

Result

NROWS  RDKN  Attribs 1-4
N1     D     D;Y;;;
N1     K     K;Z;;;
N1     N     N;A;;;
N1     R     R;X;One;A1;
N2     D     ;;;;
N2     K     ;;;;
N2     N     ;;;;
N2     R     R;X;two;B1;
N3     D     D;Y;;;
N3     K     ;;;;
N3     N     ;;;;
N3     R     ;;;;
N4     D     ;;;;
N4     K     K;Z;;;
N4     N     ;;;;
N4     R     ;;;;
N5     D     ;;;;
N5     K     ;;;;
N5     N     N;A;;;
N5     R     ;;;;

20 rows selected.

Tested with Oracle 12c, and Oracle 11g. Dbfiddle here.


Does database mirroring replicate the time stamp for database backups?

Backup data is stored in msdb, which is a system database and cannot be part of mirroring or AlwaysON.

So the software should be smart enough to check or implement a custom solution using PowerShell or linked server that will tell if a database is lacking a recent backup.


I would recommend the following approach:

a) Each developer has their own database that they can work with, try out different things,  destroy and restore at will independent of the other team members.

b) Have a shared database instance for integration testing so that the whole team can see the shared progress at any one time

c) Use windows authentication to speed up the connection process, and the devs do not have to remember new passwords, and easier to track and audit

Of course all this depends on the size of the database and what resources are available on the test server.


Logging user's id with the query he executed

Based on your comments, the INSERT could look something like this:

INSERT INTO log (userid, clientaddr, calltime, query)
SELECT user_id,  -- function argument
       a.client_addr,
       a.query_start,
       a.query
FROM pg_stat_activity AS a
WHERE a.pid = pg_backend_pid();

The key to identifying the current session is the process ID of the backend process.


Try my free Learn to Query SQL Server with the Stack Overflow Database.

You can either run the queries locally with the Stack Overflow SQL Server data export, or through your browser with Data.StackExchange.com.


Postgresql ignoring a substring in the middle of a string

One solution is to use regexps to remove that part of the filename. Assuming the separators are known

regexp_replace(filename, '^(.*?)[-_][rR][0-9]+(\.[^.]+)$', E'\\1\\2')

This would assume separators - or _, then r or R followed by at least one number, a dot and then something but never more dots. The part with r/R+numers would be removed.

The efficiency of this depends on the amount of data and how the searches are done. It could be used in an index to speed up searching. For example

CREATE INDEX filename_name_idx ON documents (regexp_replace(filename, '^(.*?)[-_][rR][0-9]+(\.[^,]+)', E'\\1\\2'));

Then doing a search

SELECT filename FROM documents
  WHERE regexp_replace(filename,
    '^(.*?)[-_][rR][0-9]+(\.[^,]+)', E'\\1\\2') = 'LLE-MET-AP-0000-PLA-COB.pdf';

would use index (if the query optimizer deems it faster).

Do note that the regexp part has to be exactly the same in the index and the search, otherwise the index cannot be used. You can also combine it with LOWER() if case insensitive searching is required.

The same regexp can be used to remove the revision identifier from the search string also, if there is a need to search for matching files for, e.g., LLE-MET-AP-0000-PLA-COB-R00.pdf

Making a view with this would make it a lot nicer to use and the actual regexp would be in the database and not in the application layer.


Non-clustered index with all columns for "select * from"?

Definitely don't do that. The missing index hints can be very useful but the recommendations can be dumb, occasionally outright ridiculous. Creating a copy of the entire table for the benefit of this query fits the later.

If your most common queries use a predicate on DataDate then it may be appropriate to change your tables clustered index to this. Only you can make that call based on your understanding of the workload.

SELECT * on a 340 column table smells suspicious. Do you really need all of those columns, every time?


modelling grocery orders, that can be split into multiple short orders

Your model is almost there for your stated purposes, so as I suggested in comments Picks doesn't need too many fields to capture the basic information you need to both accomplish splitting an order to different people, and tracking when/how it was completed.

While I mentioned the basics above, what you also really need are tracking/audit fields. With these you can actually monitor the status and recover history of who picked what and when. I'd suggest process audits and system audits. With both you can track issues with the software solution and your business process (allows some basic support for things like connectivity loss of scanners in a warehouse, system down etc.).

OrderID INT NOT NULL
StaffID INT NOT NULL
ProductID INT NOT NULL
ItemRetrievedFlag bit NOT NULL
ItemPickDateTime DATETIME NULL
InsertSystemID INT NULL --something like a barcode scanner device ID
AuditInsertDate DATETIME NULL
AuditUpdateDate DATETIME NULL
UpdateSystemID INT NULL

With a table like this you can retrieve order fulfillment history as simply as:

SELECT *
FROM Picks
WHERE OrderID = X

The query above gives you all recorded activity for the order, but it may not tell you what state the order as a whole is in. You should also have some kind of an OrderTracking table(s).

OrderID INT NOT NULL
StatusID INT NOT NULL
StatusDateTime DATETIME NULL
OrderTotalRetrievedItems INT NULL
OrderTotalItems INT NULL
AuditInsertDatetime DATETIME NULL
AuditUpdateDateTime DATETIME NULL
AuditUpdateSystemID INT NULL

You may find that with a consistent order fulfillment process you can instead dedicate columns to the status, but this pushes you into an update pattern instead of inserts as orders move through the process. If you keep a single row per status change you can more fully Denormalize your design.


Do not have WP connecting as root.  Create another 'user'
change innodb_buffer_pool_size to 1G.
Turn on the slowlog and set long_query_time = 1; you have some naughty queries that need to be investigated
What is MaxClients in Apache?  Not more than 20, I hope.


Historical reporting from the database with Reporting Services?

If you have lost the historical data (i.e. it gets overwritten rather than stored as a transaction history) then you can't reconstruct the historical state - full stop.  However, there are a few approaches to dealing with this.

Build an automated process that queries the system through SSRS or some other mechanism and saves the historical reports to an archive somewhere.  SSRS exports a web service API that lets you run reports (paramaterised if necessary) and then save the output to a file.  This can be operated by anything capable of consuming it.
Build an ETL process that takes a snapshot of the data and compares it with the previous snapshot position.  Where changes are detected they can be written out to a historical table.  If you have the option of putting triggers on the source database then you can make triggers that write out the audit logging information instead.
If you need something more elaborate then you could build a data mart - do a google search on 'type 2 slowly changing dimensions' for some ideas on how to implement this.  You can use a change capture mechanism like that described in (2) to source the data as necessary.  Either you can store the transaction history and reconstruct the status in the reports, or you can make a periodic snapshot of the data with its status at that point.

Note that Standard Edition doesn't support changed data capture.


The Distributor has not been installed correctly. Could not enable database for publishing

I think I have nailed it,
what I did is very simple

A call to the procedure sp_adddistpublisher was missing.

  Configures a Publisher to use a specified distribution database. This
  stored procedure is executed at the Distributor on any database. Note
  that the stored procedures sp_adddistributor (Transact-SQL) and
  sp_adddistributiondb (Transact-SQL) must have been run prior to using
  this stored procedure.

I have run added the last command on the script below step01:

--==============================================================
-- step 01 --  configuring the distributor
-- tell this server who is the distributor and the admin password to connect there

-- create the distributor database
--==============================================================

use master
exec sp_adddistributor
 @distributor = N'the_same_server'
,@heartbeat_interval=10
,@password='#J4g4nn4th4_the_password#'

USE master
EXEC sp_adddistributiondb
    @database = 'dist1',
    @security_mode = 1;
GO

exec sp_adddistpublisher @publisher = N'the_same_server',
                         @distribution_db = N'dist1';
GO

I noticed that now when I call the following proc:

EXEC sp_get_distributor

I see the distribution db installed  column equals 1, I should have noticed this indication before.

If it works I will leave this answer as it is, otherwise I will add to it accordingly.


When checking if a string is empty or not, append a non-space character to it and compare the result to the same character. This

@string + '.' <> '.'

will evaluate to TRUE only when the @string is truly empty. The @string's trailing spaces will count (because they will no longer be trailing after appending the .).

And you can go similarly about finding the length. This

LEN(@string + '.')

will only ever evaluate to 1 if the @string is '', not when it's ' ' or '     ' or anything of the kind.

By the way, when you want the SUBSTRING function to return a substring from a certain position to the end of the string, you can specify any fairly large number as the length parameter, no need to use LEN(). Very often something like

SUBSTRING(@string, 2, 999999999)

would do.


Query to find and replace text in all tables and fields of a mysql db

You need to use the information_schema database to generate the script.

Collect all columns from every table of every database that have the following criteria:

Exclude the following databases:

information_schema
performance_schema
mysql

COLUMN_TYPE values with one of the following characteristics:

starts with CHAR(
starts with VARCHAR(
ends with TEXT (TEXT, MEDIUMTEXT, LONGTEXT)

Here is the query to get those columns

SELECT table_schema,table_name,column_name FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text');

Using the above query, construct a set of queries that outputs SQL for converting fuschia to fuchsia:

SELECT CONCAT('UPDATE ',table_schema,'.',table_name,
' SET ',column_name,'=REPLACE(',column_name,',''fuschia'',''fuchsia'');')
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%'
OR column_type LIKE 'varchar(%'
OR column_type LIKE '%text');

Take that query and send its output to a text file. Import text into mysql:

SQL="SELECT CONCAT('UPDATE ',table_schema,'.',table_name,"
SQL="${SQL}' SET \`',column_name,'\`=REPLACE(\`',column_name,'\`,''fuschia'',''fuchsia'');') "
SQL="${SQL} FROM information_schema.columns WHERE "
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema') "
SQL="${SQL} AND (column_type LIKE 'char(%' "
SQL="${SQL} OR column_type LIKE 'varchar(%'"
SQL="${SQL} OR column_type LIKE '%text');"
mysql -u... -p... -ANe"${SQL}" > GlobalReplace_fuschia_to_fuchsia.sql
less GlobalReplace_fuschia_to_fuchsia.sql

If the file looks good, feel free to execute it.

Give it a Try !!!


Try naming that query, and then using the column with PARTITION BY in an OVER clause.

WITH MyCTE AS (
SELECT
  S.Product_Name,
    SPD.*,
    Sp.Delivery_Status,
  CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount,
  CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost AS decimal(10, 2)) AS [ShippingCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost AS decimal(10, 2)) AS [CustomsCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost + (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost + SPD.Quantity * SPD.UnitPrice - (SPD.Discount / 100) * SPD.Quantity * SPD.UnitPrice AS decimal(10, 2)) AS [TotalAmount]
FROM dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
  ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
  ON SPD.Pro_ID = S.Pro_ID
)
SELECT *, SUM(TotalAmount) OVER (PARTITION BY Purchase_ID)
FROM MyCTE;

But also, you may like to consider using APPLY to make this a lot simpler. Read http://blogs.lobsterpot.com.au/2011/12/13/a-t-sql-tip-working-calculations/ for more info on this.


Dynamic query in Stored Procedure over table values

SELECT COUNT(*)
FROM Slots s, TempSlots t
WHERE (s.starttime < t.endtime AND t.starttime < s.endtime)
   OR (s.starttime > t.endtime AND t.starttime > s.endtime)

This query counts the overlapping pairs (one record from Slots overlapped one record from TempSlots) only.

To increase the performance you may try to divide this query by two UNIONed queries escaping OR.


Mysqldump from production database, every morning

since the database size is large you should make the dump command like the following:

mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz

--quick:This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out

--single-transaction: This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

--lock-tables=false : option stops MyISAM tables (if they exsit) being locked during the backup.


join_buffer_size= 8G is dangerously high.  If you have a lot of queries running, all needing the "join buffer", you could quickly run out of RAM, as you appear to have.

8M is a better value for that setting.  Keep in mind that if a query 'wants' a join buffer, it will either use the space you provide or perform the query a different way.

max_connections=8000 is quite high.  What is the status value of Max_used_connections?  If it is more than a few hundred, we should discuss why it is so large.  That could have an impact on memory usage -- many things are "per connection".

Another possibility...  What else is running?  Could it be chewing up RAM?

For a more thorough review, provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (perhaps on some postit site).


Any advantage of having a second index with included columns on a primary key?

Most of the time, this type of index is only useful for index scans against those columns.

The index tree structure above the leaf level will usually be very similar in terms of tree levels, so if that's the case, there's little or no performance difference in navigating the tree for seek operations (that don't also scan). If you want to learn the basics of index internals, I have a video here that will help you.

Anyways, back to the scans. The reasons why this index would be useful are much less about the index itself, and more about why not to scan the clustered index (or keep those data pages in memory). If the other columns make the table relatively wide (based on the sizes you gave, I would say they do, although it's unclear how large this table is in the context of the entire database), the narrow index may be of benefit in some cases. Frequent scans (or seeks on many different key values) will tend to keep the entire index in memory, so the larger the index, the less memory space there will be for other objects, and of course this will incur more I/O if the buffer pool is cold.

Although it's somewhat of a rare use case (I've done it once or maybe twice that I can remember), the biggest benefit of this type of index is it will greatly help queries that want to use a merge join to join this table. (Note: optimize the query first if an index scan isn't appropriate!) In the query plan, a scan of the clustered index, or a scan and sort of a nonclustered index before a merge join can give an indication of when this might be a good idea. But again, this is rare. The query should be executed frequently enough, or the base table expensive enough to keep stuck in memory (i.e., you don't need it there all the time), to justify the extra storage and maintenance for the index.

If you're unsure how this index is being used right now, check the DMV sys.dm_db_index_usage_stats. Bear in mind that the counts are reset on server restart, so if you're considering dropping the index, make sure it isn't useful during the entire business cycle.


So, you want to show only rows that there is more than one with same table and column name.

We can use COUNT(*) OVER (PARTITION BY table_name, column_name) to count how many rows have the same table and column name and then WHERE ... >= 2. Also:

I don't think you need the DISTINCT.
If you do need the DISTINCT there, it can be with either one more CTE to apply it or use GROUP BY as below.
Please don't use single quotes for table and column aliases. If you need to, use square brackets or double quotes.

The query:

  ;
WITH cte AS
(
    SELECT
        table_name               AS Tabela,
        column_name              AS Coluna,
        character_maximum_length AS Tamanho,
        numeric_precision        AS Precisao_Numerica,
        numeric_scale            AS Escala_Numerica,
        COUNT(*) OVER (PARTITION BY table_name, column_name)
                                 AS Count_Duplicates
    FROM TBL_RELATORIO_TAMANHOS
    GROUP BY
        table_name, column_name,  character_maximum_length,
        numeric_precision, numeric_scale
)
SELECT
    cte.*,
    ROW_NUMBER() OVER (PARTITION BY Tabela, Coluna) AS Rn
FROM cte
WHERE Count_Duplicates >= 2
ORDER BY Tabela, Coluna, Rn
  ;


Help with this query, joining three tables in mysql

Order resulting table in the reverse chronological order and limit result to single row:

SELECT c.old_id,c.old_text
  FROM text     AS c
  JOIN revision AS b ON c.old_id=b.rev_text_id
  JOIN page     AS a ON a.page_id=b.rev_page
 WHERE a.page_id=3
 ORDER BY b.rev_text_id DESC
 LIMIT 1
;


Why does my server have thousands of "HIDDEN ONLINE" schedulers?

Converting my comment to answer :
Some one might be running undocumented fn_dump_dblog and it is creating a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. It is fixed in 2012 + SP2 though.
Also, you can use this query to find out the creation time of schedulers :
 SELECT s.scheduler_id,  s.status, th.started_by_sqlservr, th.creation_time
FROM sys.dm_os_workers w
JOIN sys.dm_os_schedulers s
ON w.scheduler_address = s.scheduler_address
 JOIN sys.dm_os_tasks t
ON t.task_address = w.task_address
  JOIN sys.dm_os_threads th
ON th.thread_address = w.thread_address
where s.status = 'HIDDEN ONLINE' -- just get the hidden online schedulers for your specific case
ORDER BY s.scheduler_id


How can a datapump export have its resource usage limited on windows?

Send it over the network

While extensive searching found no way to limit disk io natively (and serverFault will probably have more details on that) a "natural" way of limiting IO is to send the output directly across the network without ever touching the local disk. This then allows you to take advantage of all the network IO throttling resources out there. (If you want to be really sneaky, try to set up the network resource on the local machine, but that may be an overly-cute solution.)


PostgreSQL conditional join and value distribution with materialized view

A Window Function can be used (SUM): Display.impressions * Spend.spend / SUM(...) OVER(...)
Query:
SELECT D.date, D.pid, D.creative
    , COALESCE(
        D.impressions * S.spend
        / SUM(D.impressions) OVER(PARTITION BY D.date, D.pid)
      , D.spend
    )
FROM display D
LEFT JOIN spend S ON D.date = S.date AND D.pid = S.pid;

SQL Fiddle
Output:
date                        pid     creative    spend
January, 01 2016 00:00:00   1234    a           133.33
January, 01 2016 00:00:00   1234    b           266.66
January, 01 2016 00:00:00   1235    a           600
January, 01 2016 00:00:00   1236    a           300
January, 02 2016 00:00:00   1234    b           150
January, 02 2016 00:00:00   1235    a           1200
January, 02 2016 00:00:00   1236    a           166.66
January, 02 2016 00:00:00   1236    b           333.33
January, 02 2016 00:00:00   1237    a           400
January, 02 2016 00:00:00   1237    b           600


You could RESTORE and never use REPAIR_ALLOW_DATA_LOSS (the article is worth reading)! Seriously, no amount of DBCC voodoo will bring your database back to a pristine, working state.