How to check for null in column? and use other column instead?

According to SQL Standards (works with multiple flavors of SQL), you can use the COALESCE() function:

SELECT
    colB = COALESCE (colA, (colB1 + colB2))
FROM theTable;

or a CASE expression (which is not so elegant but can be used for more complex conditions):

SELECT
    colB = CASE WHEN colA IS NOT NULL
                  THEN colA
                ELSE colB1 + colB2
           END
FROM theTable;

Using T-SQL (works with Sybase and Microsoft SQL Server)

SELECT
    colB = ISNULL (colA, (colB1 + colB2))
FROM theTable;

However, you've not told us about any guarantees that either colB1 or colB2 will always have a value. If either of those are NULL then everything will be NULL. If that's the case then you can chain along conditions in COALESCE which would look like

colB = COALESCE(colA, (colB1 + colB2),0)

or using ISNULL

colB = ISNULL(ISNULL(colA, (colB1 + colB2)),0)

If you don't want to use it in the result but in a WHERE or HAVING condition, no change is needed. Either:

WHERE    colB = COALESCE (colA, (colB1 + colB2))

or:

WHERE    colB = CASE WHEN colA IS NOT NULL
                       THEN colA
                     ELSE colB1 + colB2
                END

but you have one more option:

WHERE (  colB = colA
      OR colA IS NULL  AND  colB = colB1 + colB2
      )


How to restore SQL Server bak files in Access?

It is not possible to restore a SQL Server backup into MS Access.


Changing Default Values of Percona Audit Log

There are a handful of options that are Dynamic, but for the most part they have to be added to the cnf file, which means you need to restart your live databases if you want to use it.


You don't need to include the table activities in the query at all. Only messages:
SELECT DISTINCT activity_id
FROM   messages m
WHERE  sender_id <= receiver_id   -- reduce rows
AND    EXISTS (
    SELECT 1
    FROM   messages
    WHERE  activity_id = m.activity_id
    AND    sender_id   = m.receiver_id
    AND    receiver_id = m.sender_id
    );

That's much like @dezso's query, just on the right table and with an additional feature.
The condition sender_id <= receiver_idis not strictly needed, but excludes half of the rows right away, increasing performance.
Index
The perfect index for the case should be a multicolumn index on all three columns in the WHERE condition of the EXISTS semi-join:
CREATE INDEX foo_idx ON messages (activity_id, sender_id, receiver_id);

Two squeeze out the last drop of read performance, you could make that a highly specialized partial index:
CREATE INDEX foo_idx ON messages (activity_id, sender_id, receiver_id)
WHERE receiver_id <= sender_id;

And adapt the query accordingly:
...
WHERE  EXISTS (
    SELECT 1
    FROM   messages
    WHERE  activity_id = m.activity_id
    AND    sender_id   = m.receiver_id
    AND    receiver_id = m.sender_id
    AND   receiver_id <= sender_id  -- redundant, just for partial index
    );
The added condition lets Postgres know, it can use the partial index.
This will only be moderately faster than the plain solution on top. Only worth the hassle if you need to optimize performance.
Alternatives
If there are many messages per activity, in particular also multiple message exchanges between different pairs, then this query will be faster:
SELECT activity_id
FROM   activities a
WHERE  EXISTS (
   SELECT 1
   FROM   messages m
   WHERE  m.activity_id = a.activity_id
   AND    sender_id <= receiver_id
   AND    EXISTS (
      SELECT 1
      FROM   messages
      WHERE  activity_id = m.activity_id
      AND    sender_id   = m.receiver_id
      AND    receiver_id = m.sender_id
      AND    receiver_id <= sender_id -- ?
      )
   );

Similar optimization possible as above. But you'd need another index:
CREATE INDEX foo2_idx ON messages (activity_id, sender_id, receiver_id)
WHERE  sender_id <= receiver_id;

In this case the simple index might prevail (instead of reading from two smaller indexes). Again, test.
And while you are retrieving the whole table I would also compare performance with INTERSECT:
SELECT DISTINCT activity_id
FROM (
   SELECT DISTINCT activity_id, sender_id, receiver_id
   FROM   messages m
   WHERE  sender_id <= receiver_id
   INTERSECT
   SELECT DISTINCT activity_id, receiver_id, sender_id
   FROM   messages m
   WHERE  receiver_id <= sender_id
   ) sub;

Might perform well, even without index. I would experiment with indexes and possibly GROUP BY instead of DISTINCT, or drop the DISTINCT in the subquery (depending on data distribution) to find the fastest variant.
All of these queries count a message to self as "exchange", since those qualify for both directions. If there are no messages with sender_id = receiver_id, replace all the <= with < ...


Why does an Oracle database require both the SYSTEM and the SYSAUX tablespaces?

Tradition and the ability for elegant failure degradation.
As the database has evolved over 11 versions, important views and tables that maintain and support the database have been programmed into the two tablespaces. Furthermore, they represent a fantastic logical separation between the "NEVER EVER TOUCH" and the "third parties can put stuff here."
From the docs:

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.
If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

Thus, while it's critical to have the SYSTEM tablespace never ever fail, one doesn't need to be nearly as paranoid for SYSAUX, this allows the designers to keep the size of SYSTEM down, while allowing third-party auxiliary features to be part of the "core" database.


Read up here before you shrink your log file.

You dont have to change the recovery model ...

Update:

you can use Invoke-sqlcmd cmdlet.

eg.

$query = "DBCC SHRINKFILE(db1_log)"
Invoke-Sqlcmd -ServerInstance $instanceName -Query $query

EDIT: Below is an excerpt from Powershell V3 cookbook ...

Not all DBCC commands are wrapped in SMO methods. For other DBCC commands that are not nicely wrapped in methods, you can still execute them using the Invoke-Sqlcmd cmdlet.


PostgreSQL: Creating view of multiple tables in function

What you need to do is:

Build each individual query; then
string_agg them together with UNION ALL as the joining string.

UNION ALL is important, not just UNION, as UNION may force a sort-and-de-duplicate operation, which is slow.

I will use the format function and its %I and %L format-specififers instead of quote_ident and quote_literal respectively, because it's massively more readable that way.

I have no idea what the stray ROM in:

'select * from ROM ' ||  quote_ident("TableName") ||' UNION'

is. It doesn't make sense. I'm ignoring it. I assume it's probably an editing error, the end of an accidentally remaining FROM.

So, first, take a query that produces a list of queries for each table:

SELECT
   format('SELECT * FROM %I', "TableName")
FROM "AllTables" a
INNER JOIN INFORMATION_SCHEMA.TABLES so ON a."TableName" = so.TABLE_NAME;

then wrap it in an aggregate (string_agg) to combine the strings, prefix the CREATE VIEW and store the result in your query variable and execute it:

SELECT INTO que
   'CREATE VIEW view1 AS ' ||
   string_agg( format('SELECT * FROM %I', "TableName"), ' UNION ALL ')
FROM "AllTables" a
INNER JOIN INFORMATION_SCHEMA.TABLES so ON a."TableName" = so.TABLE_NAME;

raise notice '%',que;
execute que;

BTW, needing to do things like this often means you have a bit of a database design issue. You should not need tons of little tables with the same schema like this. It usually means you're doing this:

CREATE TABLE customer01_transactions (
    trans_id serial primary key,
    ...
)

CREATE TABLE customer02_transactions (
    trans_id serial primary key,
    ...
)

when you should usually be doing this:

CREATE TABLE customer_transactions (
    customer_id integer,
    trans_id serial,
    ...,
    PRIMARY KEY(customer_id, trans_id)
)


Use users / logins from another database

This means that database users have no matching server logins.

That is, each database sys.database_principals has no match in sys.server_principals

For Windows logins this is easy. This generates your missing CREATE LOGINS

USE MyDB
SELECT
    'CREATE LOGIN ' + QUOTENAME(SUSER_SNAME(sid)) + ' FROM WINDOWS'
FROM
    sys.database_principals DP
WHERE
    DP.type IN ('G', 'U')
    AND
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);

For SQL Logins, you need to recreate it with the correct SID.

SELECT
    'CREATE LOGIN ' + QUOTENAME(DP.name) +
                ' WITH PASSWORD = ''ChangeMe'', SID = ' +
                CONVERT(varchar(128), sid, 1)
FROM
    sys.database_principals DP
WHERE
    DP.type = 'S' AND DP.principal_id > 4
    AND
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);

Note that sp_change_users_login is deprecated.

Now, if your SQL Logins already exist, then the names match but the sid values are different. For this you use ALTER USER use the LOGIN option.

Finally, the SQL Login passwords can be recreated if you have a backup of the "old" master database. If you restore this as, say, FixLogins then you can use this

SELECT
    'CREATE LOGIN ' + QUOTENAME(DP.name) +
                ' WITH PASSWORD = ' + CONVERT(varchar(256), SL.password_hash, 1) + ' HASHED, SID = ' +
                CONVERT(varchar(128), DP.sid, 1)

FROM
    sys.database_principals DP
    JOIN
    FixLogins.sys.sql_logins SL ON DP.sid = SL.sid
WHERE
    DP.type = 'S' AND DP.principal_id > 4
    AND
    NOT EXISTS (SELECT * FROM sys.server_principals SP WHERE SP.sid = DP.sid);


How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE

Answer to the new question:

SELECT `dept_name`
    , `emp_name`
    , `emp_sal`
    , `ej_joining date`
FROM (
    SELECT emp.`dept_name`, emp.`emp_name`, emp.`emp_sal`, emp.`ej_joining date`
        , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
        , @dept_id := `emp_dept_id` as `dept_id`
    FROM (
        SELECT DT.`dept_name`
            , EMP.`emp_name`, EMP.`emp_sal`, EMP.`emp_dept_id`
            , EJ.`ej_joining date`
        FROM `Dept` DT
        INNER JOIN `Employee` EMP ON DT.`dept_id` = EMP.`emp_dept_id`
        INNER JOIN `JoiningDate` EJ ON EJ.`ej_emp_id` = EMP.`emp_id`
        WHERE DT.`dept_status` = '1' AND EMP.`emp_status` = '1'
        ORDER BY Dt.`dept_id`, RAND()
    ) emp
    CROSS JOIN (
        SELECT @dept_id := 0 as dept_id, @num := 0 as num
    ) v
) num
WHERE num.num <= 2
ORDER BY `dept_name`, num.num
;

SQL Fiddle

This answers the original question

This first query will randomly order rows by dept_id. Each row in each group of dept_id is given a incremental number (num) from 1 to n.

Random order query:

SELECT `emp_name`, `emp_sal`
        , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
        , @dept_id := `emp_dept_id` as `emp_dept_id`
FROM (
    SELECT `emp_name`, `emp_sal`, `emp_dept_id`
    FROM `Employee`
    ORDER BY emp_dept_id, RAND()
) emp
CROSS JOIN (
    SELECT @dept_id := 0 as dept_id, @num := 0 as num, @numx := 0 as numx
) v

The Dept table can then be joined to this query. A filter on num value being 1 or 2 will give the first 2 rows of each dept_id. SQL Fiddle
The order changes for each execution and the first 2 rows change as well.

Main query:

SELECT DT.`dept_name`, num.`emp_name`, num.`emp_sal`
FROM `DEPT` DT
INNER JOIN (
    SELECT `emp_name`, `emp_sal`
            , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
            , @dept_id := `emp_dept_id` as `emp_dept_id`
    FROM (
        SELECT `emp_name`, `emp_sal`, `emp_dept_id`
        FROM `Employee`
        ORDER BY emp_dept_id, RAND()
    ) emp
    CROSS JOIN (
        SELECT @dept_id := 0 as dept_id, @num := 0 as num
    ) v
) num
    ON DT.`dept_id` = num.`emp_dept_id`
WHERE num.num <= 2
ORDER BY  DT.`dept_name`, num.num


The WRITE method is minimally logged. If you use regular UPDATE statement, it would result in overwriting the entire string using FULL LOGGING. This would become inefficient when dealing with large updates.

To support update for large value data types, the UPDATE syntax supports .WRITE method. This will result in less Transaction log due to its nature of minimal logging - including insert or appending new data.

Note : The UPDATE using WRITE method will fail if the target LOB is NULL.

Below is a quick and dirty repro showing the transaction log generated using REGULAR UPDATE and UPDATE using .WRITE method.

--- create table

create table dbo.testWRITEUpdate (
    ID int identity(1, 1)
    ,COMMENTS varchar(MAX) not null
    )
go

insert into dbo.testWRITEUpdate (COMMENTS)
values (REPLICATE(CAST('KIN' as varchar(max)), 9000))

checkpoint
go

--- Regular Update:

set statistics io on
 begin tran
update dbo.testWRITEUpdate
set COMMENTS =  STUFF(COMMENTS, 9, 55, '$$$$$(((())))))))))))))____GarbageData____ Entered ____')
where ID  =1

set statistics io off
go

commit tran

-- stats IO output

 Table 'testWRITEUpdate'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 15, lob physical reads 0, lob read-ahead reads 6.

-- Transaction Log Generated --> 6 Log records with 54580 bytes:

Update using WRITE method :

begin tran
set statistics io on
update dbo.testWRITEUpdate
set COMMENTS.WRITE('$$$$$(((())))))))))))))____GarbageData____ Entered ____',9,55)
where ID  =1
set statistics io off

commit tran
go

-- Stats IO

Table 'testWRITEUpdate'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.

-- Transaction Log Generated --> 3 log records with 464 bytes:

The above test proves that the WRITE method provides more benefit in terms of Transaction log generated as it is minimally logged operation + incurring less logical reads.

Note: This is just scratching the surface, there can be more tests done to actually prove in terms of performance if WRITE method is performing better than the regular UPDATE especially when dealing with VARCHAR, NVARCHAR OR VARBINARY datatypes.

Update (As requested by OP):

Below is the query for finding the sys.dm_tran_database_transactions

SELECT [database_transaction_log_bytes_used] FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID ('test_kin');
GO

--- or a more detailed version :

SELECT DTST.[session_id],
 DES.[login_name] AS [Login Name],
 DB_NAME (DTDT.database_id) AS [Database],
 DTDT.[database_transaction_begin_time] AS [Begin Time],
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Duration ms],
 CASE DTAT.transaction_type
   WHEN 1 THEN 'Read/write'
    WHEN 2 THEN 'Read-only'
    WHEN 3 THEN 'System'
    WHEN 4 THEN 'Distributed'
  END AS [Transaction Type],
  CASE DTAT.transaction_state
    WHEN 0 THEN 'Not fully initialized'
    WHEN 1 THEN 'Initialized, not started'
    WHEN 2 THEN 'Active'
    WHEN 3 THEN 'Ended'
    WHEN 4 THEN 'Commit initiated'
    WHEN 5 THEN 'Prepared, awaiting resolution'
    WHEN 6 THEN 'Committed'
    WHEN 7 THEN 'Rolling back'
    WHEN 8 THEN 'Rolled back'
  END AS [Transaction State],
 DTDT.[database_transaction_log_record_count] AS [Log Records],
 DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],
 DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],
 DEST.[text] AS [Last Transaction Text],
 DEQP.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions DTDT
 INNER JOIN sys.dm_tran_session_transactions DTST
   ON DTST.[transaction_id] = DTDT.[transaction_id]
 INNER JOIN sys.[dm_tran_active_transactions] DTAT
   ON DTST.[transaction_id] = DTAT.[transaction_id]
 INNER JOIN sys.[dm_exec_sessions] DES
   ON DES.[session_id] = DTST.[session_id]
 INNER JOIN sys.dm_exec_connections DEC
   ON DEC.[session_id] = DTST.[session_id]
 LEFT JOIN sys.dm_exec_requests DER
   ON DER.[session_id] = DTST.[session_id]
 CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST
 OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;
-- ORDER BY [Duration ms] DESC;


SQL Server clustered index, index balancing and insert performance using NewID

SQL Server does not "rebalance the tree" as a periodic event. I have last heard this term in the context of Oracle. All that SQL Server does it increase the tree height when necessary. This is an event that happens only a few times in the entire existence of a B-tree.

In a DML heavy workload there can be many small tree adjustments called page splits. These are indeed detrimental to CPU and IO usage and they can cause fragmentation. If you are inserting in ascending date order this problem does not occur because tree "appends" are a special case that SQL Server optimizes for. In any case a page split only affect a handful of pages.

No periodically occurring tree operations.

Clustered indexes have (almost) the same structure as non-clustered indexes.

All the usual SQL Server B-tree index advice applies: Chose the key wisely (seems like you have a good one based on ascending datetime values) and have a strategy for fragmentation and for reclaiming space in case of deletes.


Online index maintenance in SQL Server using SNAPSHOT isolation level

The problem is in your application layer. You are starting explicit snapshot transactions before doing DDL. You have to modify the app logic to be smarter about its intent. If about to execute DDL then you must either a) use read committed isolation transaction or b) don't start a transaction and let the DDL use an implicit transaction.

Looks like you used a carpet bombing approach and modified your app to use snapshot isolation everywhere. There is no pixie dust, you'll have to make sure you choose the correct isolation/transaction mode on a case by case basis, in your code.


@elimerv,

Are you sure you are not having something else in place of "single-quote" that is expected there? Also, you don't tell what kind of data does the "pm" table has in the pm_id column (BTW, your latest SQL doesn't appear to be correct as your sample_data subquery has an "id" column whereas your main query refers to "pm_id" column).
This is what I get on 11.2.0.1
First, your latest query, as it is (except for tables used):

with sample_data as (
   select '123#@#' id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
                                                     *
ERROR at line 8:
ORA-00904: "PM_ID": invalid identifier

Now, with correct column name used:

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /

no rows selected

And, when I replaced one of the "single-quotes" with a different (but similar looking) character :

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
                                                                                *
ERROR at line 8:
ORA-00911: invalid character

Does this give you any idea?
BTW, are you sure you need XML processing to solve your problem ?


Execute query cache on startup - Reasonable?

please refer to this link? this should answer your question

Yes, 100% reasonable to make use of the MySQL caching system!

How to "warm up" a cold DB server?

  Warm-up is needed because the best performance of a database is
  achieved when its various caches are populated with the most relevant
  data, since reading from memory is generally substantially faster than
  reading from disk.

  InnoDB has the InnoDB buffer pool. MyISAM has the key cache and the OS
  cache. Then there's also the MySQL query cache. MySQL has the open
  table cache. Some or all of these structures contribute to the ideal
  performance of a server. When a server is handling traffic that is
  characteristic of its workload, it tends to have the most frequently
  used -- and therefore presumably the most relevant data -- already
  cached.

  Take the load off of an active MySQL server and you will not see it
  start closing tables and evicting data from its caches. If you leave
  it running, then come back to it tomorrow, you should find that it's
  still as warm as you left it.


Why selecting rowversion using outer join never returns null

This appears to be a bug in .NET's SqlClient.  The column is being returned as an empty byte[] even though it's marked as null in the result.

EG

using System;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;

namespace ConsoleApp14
{
    class Program
    {

        static void Main(string[] args)
        {

            using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select cast(null as rowversion) rv";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();
                    var allowDbNull = rdr.GetColumnSchema()[0].AllowDBNull;
                    var isNull = rdr.IsDBNull(0);
                    var val = rdr[0];

                    Console.WriteLine($"SqlClient: AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");

                }
            }

            using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select @val = cast(null as rowversion) ";

                var p = cmd.Parameters.Add(new SqlParameter("@val", System.Data.SqlDbType.Timestamp));
                p.Direction = System.Data.ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                {

                   SqlBinary val = (SqlBinary) p.SqlValue;
                   Console.WriteLine($"SqlClient (parameter): IsDbNull: {val.IsNull} {val.GetType().Name} {val}");

                }
            }

            using (var con = new OdbcConnection("Driver={ODBC Driver 17 for SQL Server};Server=localhost;Trusted_Connection=yes"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select cast(null as rowversion) rv";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();
                    var allowDbNull = rdr.GetSchemaTable().Rows[0]["AllowDBNull"];
                    var isNull = rdr.IsDBNull(0);
                    var val = rdr[0];

                    Console.WriteLine($"ODBC:      AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");

                }

            }

        }
    }
}

Outputs

SqlClient: AllowDbNull True IsDbNull: False Byte[] System.Byte[]
SqlClient (parameter): IsDbNull: True SqlBinary Null
ODBC:      AllowDbNull True IsDbNull: True DBNull

I've opened an issue at https://github.com/dotnet/SqlClient/issues/255  But it's likely to be closed as WontFix.  According to notes in the source

// Dev10 Bug #479607 - this should have been the same as SqlDbType.Binary, but it's a rejected breaking change

The issue has been raised, but not fixed as a breaking change.  It may get fixed in .NET Core, which is full of breaking changes anyway, and left as-is in .NET Framework.


Linked Server along Oracle 32 and 64 bit Oracle Client

So when I run the job and it is connecting Oracle Server, will it use Linked Server or will directly use Oracle 32-bit client?

Depends on how the DTS package is written, since DTS was the old 2000 era packages I have no idea if they imported a connection in the package or query through the linked servers. You'd have to investigate to see if it references the linked server using TSQL or it uses a connection, directly.

  If linked server is failing will that can be the reason for job failure. Error message when Testing Linked Server is: ORA-12154: TNS: could not resolve the connect identifier specified

Looks like you should check the tnsnames.ora file to make sure the listeners are identified properly.

  Is the order in which oracle bin path is defined in PATH System Environment variable can impact this issue in anyway?

Potentially, depends on how/what the package is doing.

  After making the changes in PATH System Environment variable do we need to restart SQL Server service or entire server.

If you make changes to the system variables it's best to restart the server.


This is because a comparison to NULL actually results in unknown. NULL is the absence of data and so you are comparing a value against nothing and the result cannot be determined.

  When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors. These tables outline the effect of introducing null comparisons.

Taken from the documentation on MSDN.


Cross Database transactions with Availability Groups

The article isn't well worded but here's what I think it's saying

Beginning scenario - DatabaseA is in an AG and is currently primary on Server1 and can run on Server2.  DatabaseB can only run on Server1

T1 - Transaction begins
T2 - Row inserted into DatabaseA (log not yet sent to Server2 though)
T3 - Row inserted into DatabaseB
T4 - Transaction commit called
T5 - DatabaseB commits transaction (since the two DBs commit individually)
T6 - DatabaseA fails over before log is sent

Since the log was never sent DatabaseA wouldn't have the row but DatabaseB would.  If synchronous mode is being used that can't happen because the commit can't be called until the log from the row is sent.  However, it may be possible for the commit on DatabaseB to happen and DatabaseA to fail over before the commit happens there.  This may lead to a rollback in DatabaseA instead of a commit.  I can't say with certainly that you can get into the same situation with synchronous mode but if you can that's how it would likely happen.


Is there a way to catch a dbcc checkident command

Sure, these are captured by the default trace (and maybe by extended events system_health session, not sure). Given the caveats in this answer (for example a command issued last year is unlikely to still exist in the trace):

What event information can I get by default from SQL Server?

You can see recent DBCC events here:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
       CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT TextData, ApplicationName, LoginName, StartTime, DatabaseName
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 116
  --AND DatabaseName = DB_NAME() / or constant
  ORDER BY StartTime DESC;

Note, this will show more than just CHECKIDENT events, but you can filter that further. On the other hand, it might be useful to spot check what other DBCC commands are (or are not) being run, though.


redshift drop table with + and spaces in name

I was able to answer my own question, a co worker was able to see a space in front of the first plus sign. I was not seeing this, so quoted drop worked.

afsproddb01=#drop table " + transtable +";
DROP TABLE


View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

I've not had to track down cursor activity since SQL2000 i.e. pre-DMV days. The old way would still be viable I assume, use profiler and include execution plans in the trace.

I can't remember if the plan will be included in the sp_cursorexecute call or if you need to go back through the trace and find the sp_cursorprepexec or sp_cursorprepare event associated with the handle.


In addition to what @swasheck already explained, you'll probably get better performance with LIKE (~~) and ILIKE (~~*) in combination with a trigram GiST or GIN index. You'll have to install the additional module pg_trgm for that. Find details under these related questions:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Trigram indexes can be used for basic regular expression matches (~) as well, but LIKE is generally faster.

Create an index like:

CREATE INDEX stickers_title_gin_trgm_idx ON stickers
USING gin (title gin_trgm_ops);

Then this should be very fast:

SELECT *
FROM   stickers
WHERE  title ~~* '%case 580%';

Or (not clear from your question):

SELECT *
FROM   stickers
WHERE  title ~~* '%case%'
AND    title ~~  '%580%';


How to get a sense of how busy is the server

I wouldn't buy into an analysis done solely on batch per second. At the very least I would ask for a baseline to compare against (eg. "6 month ago we were handling 50 b/s, now we're doing 500 b/s"). Simply stating "our server handles 342 batches per second" does not say much. At the very least you could capture a timeline and say something like "there are daily spikes of b/s at 9:30 am". But you can do much, much more.

To start with, you need to learn some basics about the performance toolset at your disposal. First and foremost logman.exe. Absolutely read Windows Performance Monitor (all the sub-chapters too, there's a lot to learn there).

Next I would recommend reading How to analyse SQL Server performance. It really answers a lot of your questions.

And now the answers to your questions:

  Do batch request per second the right approach to see how busy/overloaded/overworked the server is?

No.

  if yes then how do I capture this?

Not applicable.

  do I have to keep SQL profiler open and save the data daily?

Never. Even if trace events would be relevant, never SQL Profiler. Read about SQL Trace and server side tracing.

  is there an automated way that allow me to capture some sort of metrics on how much request is hitting the server?

Yes, many. the engine itself cpature a lot of info in wait stats, the OS exposes perf counter and a rich toolset around them. Read the articles linked above.

  If batch request per second is not the right data, then what would be the right info to get? and how to get it?

Ditto, read the articles linked above to get some basic info and narrow down your question.


Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper

In SQL*Plus and SQL Developer you need a / at the end of a PL/SQL block.

variable pStartDateBegin VARCHAR2(10);
variable pEndDateFinish VARCHAR2(10);

begin
      select '01-01-2000', '30-11-2011'

      into :pStartDateBegin,:pEndDateFinish
        from dual;

end;
/

[...]


Upgrade SQL Server 2008 R2 to SQL Server 2012 and then set up data replication to SQL Server 2016

Yes, you'll be able to configure Transactional Replication with 2012 as the Publisher and 2016 as the Subscriber.

However, there are some changes made in replication procedures (that get created at subscribers to replicate transactions like INS, UPD & DEL) in 2016 ,that you'll have to correct manually. I have done it earlier but now migrated to AlwaysOn according to requirement.


Granting permission to the service account

Have a look at the fixed db roles in SQL server:

In your first example (db_datareader and db_datawriter), the service account can only read and modify data in the tables.  With these 2 roles, the service account cannot modify the tables or perform and DDL.  Nor does it have EXECUTE permissions.

In your second example, you've given all the permissions above but you've added EXECUTE and ALTER.  This allows that account to execute SPs and also modify objects (structurally, or otherwise) within the DB.

So, to answer your question, no, they are not the same.  The second example has more permissions and can do more.


Audit Trail trigger can't detect if dates are the same

I believe you just have to drop the WHERE clause from this SELECT:

SELECT @insertedDate = i.DueDate, @deletedDate = d.DueDate
    FROM inserted i, deleted d WHERE d.DueDate <> i.DueDate


ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block"

Since the problem is in your undo tablespace. You can drop it and create a new one. Steps in this article:

http://www.my-whiteboard.com/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data-files/


If an error condition in your stored procedure raises an exception, your only option without changing the code inside the procedure, really, is to wrap the call in try/catch:

BEGIN TRY;
  EXEC sys.sp_helpdb @foo = 1;
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
END CATCH;

If your procedure returns an error number without bubbling up an exception, e.g.:

CREATE PROCEDURE dbo.bob
  @fail bit = 0
AS
BEGIN
  IF (@fail = 1)
  BEGIN
    RETURN -1;
  END
  -- unnecessary, but:
  ELSE
  BEGIN
    RETURN 0;
  END
END

Then you can use this format:

DECLARE @TestErrorFlag int;
EXEC @TestErrorFlag= dbo.bob @fail = 1;
PRINT @TestErrorFlag;

Of course in this case if any error condition in the procedure does lead to an exception, the PRINT won't happen.

Error handling is a massive topic. I would browse around these articles:

Error and Transaction Handling in SQL Server - Part 1
Error and Transaction Handling in SQL Server - Part 2
Error and Transaction Handling in SQL Server - Part 3


Get one row between two duplicate rows with one different value in a column

This is an example of where Analytic functions can shine! In this case, you have a choice between the FIRST_VALUE() function (also its "mirror image" - LAST_VALUE()) and the ROW_NUMBER() function! This thread gave me the idea for using FIRST/LAST_VALUE to emulate  PostgreSQL's DISTINCT ON. See the fiddle here!

Create and populate the table:

CREATE TABLE my_tab
(
  title VARCHAR (25) NOT NULL,
  start_time TIMESTAMP NOT NULL,
  end_time   TIMESTAMP NOT NULL,
  location    VARCHAR (10) NOT NULL,
  CONSTRAINT my_tab_pk PRIMARY KEY (title, start_time, end_time, location),
  CONSTRAINT my_tab_st_lt_et_ck CHECK (start_time < end_time)
);

populate (sample):

INSERT INTO my_tab VALUES
(
  'Title - abc',
  TO_DATE('2020-03-18 10:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  TO_DATE('2020-03-18 10:30:00', 'yyyy-mm-dd hh24:mi:ss'),
  'Loc1'
);

You have to put in a date as well as a time - Oracle doesn't have a TIME datatype - see here. This thread also proved helpful for final formatting of the answer using the TO_CHAR function.

And then (to check), run:

SELECT * FROM my_tab;
ORDER BY location, start_time, end_time, title;

Result:

TITLE           START_TIME                      END_TIME                      LOCATION
Title - abc     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1
Title - def     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1
Title - abc     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1
Title - def     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1
Title - ghi     18-MAR-20 12.00.00.000000 PM    18-MAR-20 12.30.00.000000 PM    Loc1
Title - jkl     18-MAR-20 01.00.00.000000 PM    18-MAR-20 01.30.00.000000 PM    Loc1
Title - mno     18-MAR-20 02.00.00.000000 PM    18-MAR-20 02.30.00.000000 PM    Loc1

We can see that abc and def overlap at 10:00 and 11:00.

FIRST_VALUE()

In the fiddle there is a first attempt which shows my train of thought - not included here.

The query:

SELECT
  DISTINCT
  FIRST_VALUE (t.title) OVER (PARTITION BY t.start_time ORDER BY t.start_time ASC) AS title,
  TO_CHAR(t.start_time, 'HH24:MI') AS "start",  -- be careful, both "start" and "end"
  TO_CHAR(t.end_time,   'HH24:MI') AS "end",     -- SQL keywords - fails if not quoted!
  t.location
FROM my_tab t
-- can have ORDER BY 2, 1, but I think actual names are more legible!
-- Again, the SQL keyword "start" must be quoted!
ORDER BY "start", title;

Result:

TITLE           start     end   LOCATION
Title - abc     10:00   10:30       Loc1
Title - abc     11:00   11:30       Loc1
Title - ghi     12:00   12:30       Loc1
Title - jkl     13:00   13:30       Loc1
Title - mno     14:00   14:30       Loc1

Et voilà - the desired result. If you want to eliminate abc instead of removing abc, you can use the LAST_VALUE() function or change the ORDER BY in the OVER() clause of the query (shown in the fiddle - not included here).

ROW_NUMBER()

I'll run the inner query of my final query first so that my train of thought can be easily followed:

SELECT
  m.title,
  m.start_time,
  m.end_time,
  m.location,
  ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
FROM my_tab m;

Result:

TITLE           START_TIME                      END_TIME                     LOCATION  RN
Title - abc     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1    1
Title - def     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1    2
Title - abc     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1    1
Title - def     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1    2
Title - ghi     18-MAR-20 12.00.00.000000 PM    18-MAR-20 12.30.00.000000 PM    Loc1    1
Title - jkl     18-MAR-20 01.00.00.000000 PM    18-MAR-20 01.30.00.000000 PM    Loc1    1
Title - mno     18-MAR-20 02.00.00.000000 PM    18-MAR-20 02.30.00.000000 PM    Loc1    1

We can see that we want to hold on to the records where rn = 1, so we wrap this in another query (since we can't put Analytic functions in WHERE clauses).

So, we run the following:

SELECT
  t.title,
  TO_CHAR(t.start_time, 'HH24:MI') AS "start",  -- be careful, both "start" and end are
  TO_CHAR(t.end_time,   'HH24:MI') AS "end",    -- SQL keywords - fails if not quoted!
  t.location
FROM
(
  SELECT
    m.title,
    m.start_time,
    m.end_time,
    m.location,
    ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
  FROM my_tab m
) t
WHERE rn = 1
ORDER BY t.title, t.location, t.start_time;

Result:

      TITLE     start     end  LOCATION
Title - abc     10:00   10:30      Loc1
Title - abc     11:00   11:30      Loc1
Title - ghi     12:00   12:30      Loc1
Title - jkl     13:00   13:30      Loc1
Title - mno     14:00   14:30      Loc1

Et voilà encore - the desired result again. If you have a preference for one title over another, you can vary the ORDER BY clause in the OVER part of the ROW_NUMBER() function.

Analytic (aka Window) functions are very powerful and will repay (many, many times over) any effort you put into learning them! As previously mentioned, this thread proved very helpful.

For future reference, henceforth, could you please include your table structure(s) as DDL (CREATE TABLE foo (f1 t1,....);) and your data as DML (INSERT INTO foo VALUES (v1, v2...);)? It's a help to have a single point of truth for the data and saves duplication of effort!

p.s. welcome to the forum!


How can I monitor a production database to see if there are table scans happening?

If you are looking for missing indexes inside your plan cache, you can try this:

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT dec.usecounts, dec.refcounts, dec.objtype
      ,dec.cacheobjtype, des.dbid, des.text
      ,deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
     CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
     CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE
deq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

You can find more info in the plan cache as well, I've listed a few examples here: http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries


How do I set the external editor for PostgreSQL's psql client?

Contrary to my comment above (which was the result of insufficient testing), you should put a line into .bashrc (or any similar place):

export PSQL_EDITOR="/Users/username/bin/mate"

In psql coming with PostgreSQL 9.2, there is a \setenv command, it is supposed to work from .psqlrc as well.

(Credits to Pedro Romano.)


PostgreSQL JSONB update array elements query

This is not a trivial task.

Assuming the two nested JSON arrays reliably exist in every row, this works:

UPDATE forms f
SET    form = u.form1
FROM  (
   SELECT f.id, f.form || jsonb_build_object('widgets', jsonb_agg(w.widget1)) AS form1
   FROM   forms f
   CROSS  JOIN LATERAL (
      SELECT w.widget || p.permissions AS widget1
      FROM   jsonb_array_elements(f.form ->'widgets') w(widget)
      LEFT   JOIN LATERAL (
         SELECT jsonb_build_object('permissions', jsonb_agg(p1.permission)) AS permissions
         FROM  (
            SELECT *
            FROM   jsonb_array_elements(w.widget->'permissions') p(permission)
            WHERE  p.permission <> jsonb '"user1"'       -- delete user1
            UNION
            SELECT jsonb '"user5"'                       -- add user5
            ) p1
         ) p ON true
      ) w
   GROUP  BY 1
   ) u
WHERE  u.id = f.id
AND    u.form1 IS DISTINCT FROM f.form;

But it is complex and expensive. I'd rather not use it. Instead, I would aim to normalize the DB schema if at all possible.

db<>fiddle here - with intermediate steps to help understanding.

There is too much to explain here ...

Most importantly:

It works even with empty permissions arrays because we add at least one user. Else we would get NULL instead of an empty array, and might need to do more.
The manual about the concatenation operator ||:

  Note

  The || operator concatenates the elements at the top level of each
  of its operands. It does not operate recursively. For example, if both
  operands are objects with a common key field name, the value of the
  field in the result will just be the value from the right hand
  operand.

Avoid updating rows where nothing changes with the last line added to the WHERE clause. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

While you only delete a user and most rows don't have that user to begin with, we could make it much faster with a condition:

WHERE  f.form @> '{"widgets": [{"permissions":["user1"]}]}'

Checks whether any permissions array has a "user1" element, and can use a matching index. If the table is big you'll want a matching index. See:

Find rows containing a key in a JSONB array of records

To identify rows where not all permissions arrays have "user5", the check is not as simple, and index support even less. Though still possible.


SQLPLUS: SELECT INTO prompting for variable value

The ampersand character (&) prompts for values before execution time by default. That is not how you display values of variables. Try the below instead.

set serveroutput on

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

    dbms_output.put_line('precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF

Passing a variable to a script in SQL*Plus. The script, let's say, 1.sql:

set serveroutput on feedback off verify off
define CLIENTID=&1

SPOOL logs\MVP_MEC_UPDATED.log APPEND
DECLARE
   precount   NUMBER;
   nowcount   NUMBER;
BEGIN
    SELECT COUNT(*) INTO precount FROM PLAN_OFFER_BK_PRE_SCRIPT WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';
    SELECT COUNT(*) INTO nowcount FROM PLAN_OFFER WHERE COV_IN = 'Y' AND PLAN_IN = 'Y';

    dbms_output.put_line('CLIENTID: ' || '&CLIENTID' || ', precount: ' || precount || ',  nowcount:' || nowcount );
END;
/

SPOOL OFF
exit

Invoking it:

sqlplus -s user/password @1.sql 101

Where 101 is the CLIENTID. Or:

sqlplus -s user/password @1.sql %CLIENTID%

Where %CLIENTID% is a variable in your batch script.


Standard Meaning of "Bounce the Database"

It's been consistent for me: bounce generally means restart
- Bounce SQL Server = restart the service, normally
- Bounce the server = reboot
- Bounce the PC = reboot
- Bounce IIS = run iisreset

I'd interpret "bounce Oracle" as restart the DB engine processes


How to find usage of each object in other objects from a database?

Hopefully, they have named the FK's the same as the PK on the table it is linked to, this could then make it a whole lot easier. Why? What you could do is first return all the table/column names in the database with something like this:

SELECT Table_Name, Column_Name, Ordinal_Position
INTO #TempColumnNames
FROM information_schema.columns
ORDER BY Table_Name, Ordinal_Position

You could then join the table to itself based on the Column_Name, something like:

SELECT t1.Table_Name, t1.Column_Name
FROM #TempColumnNames t1
INNER JOIN #TempColumnNames t2 ON t1.Column_Name = t2.Column_Name
WHERE t1.Table_Name <> t2.Table_Name
AND t1.Ordinal_Position = 1

That should give you a long list of all the tables that have another table with the same column name in it. Remember, this is all dependent on the FK column having the same name as the PK column!

EDIT: I have edited the above to add "Ordinal_Position" which indicates the order the field appears in tables. Almost always, the PK field is listed first, but not always. Check that this is consistent in your DB. This will only link the first field that has the same name as another field and it assumes the table where that field appears first is your PK table.

You can then do something like this:

SELECT t1.Table_Name, t1.Column_Name, t2.Table_Name, t1.Ordinal_Position,
    'ALTER TABLE [' + t2.Table_Name + '] ADD CONSTRAINT FK_' + t2.Table_Name
    + '_' + t1.Table_Name + ' FOREIGN KEY (' + t1.Column_Name + ')'
    + ' REFERENCES [' + t1.Table_Name + '] (' + t1.Column_Name + ')' as FK_TSQL
FROM #TempColumnNames t1
INNER JOIN #TempColumnNames t2 ON t1.Column_Name = t2.Column_Name
WHERE t1.Table_Name <> t2.Table_Name
AND t1.ORDINAL_POSITION = 1

This should give you a whole stack of statements for creating the FK on the FK table and linking it to the PK table. Be careful though, this doesn't do it perfectly. Limitations are:

PK's that are more than 1 field will not work (think of many to many link tables etc).
It does not include schema's though this can be added easily (you may have multiple schemas with the same table names)
You will likely still need to check RBAR (Row By Agonising Row) to ensure these are valid, but essentially you have written out all the code for creating the relationships, you just need to delete those not applicable.


MySQL failover - Master to Master Replication

I'm thinking of doing exactly as you describe using Amazon's Route 53 DNS Failover.  So the idea is to setup 2 databases as master-master

db1.domain.com
db2.domain.com

Then do a Route53 "failover" set on

db.domain.com
-> primary is db1.domain.com => TCP-based health check on port 3306
-> secondary is db2.domain.com  => TCP-based health check on port 3306

I have not gotten to this point yet but that is my plan


How to support arbitrary column sorting in a database?

You don't need index to sort data. Because this customers data must have customer_id included to every document, just create index for that customer_id. You make query with that customer_id and set sort parameters according what customer wants data to be sorted. Sorting is done in memory at mongodb node (or nodes, if you have cluster). Of course, if customer have more data than 32MB, sorting without index is impossible. With aggregate and allowDiskUse you can do sort of very large results.


Arg, found the problem. My username is ops$account and PowerShell treats $ as a start of variable token. Because $account was null the username I was passing was just "ops". I fixed the issue by escaping the $ with the backtick, i.e.

C:\oracle\client\BIN\sqlplus.exe ops`$account@server


How to revoke DBADM and clean up any related objects from DB2 LUW

There are several aspects that you might want to consider.

Firstly, you can assign proper authorities to the target instance owner during the restore automatically by using the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable. If it's set to ON prior to the restore, the user performing the restore will get those authorities.

Secondly, most of the binding/compiling problems come from the fact that only the object owner, that is, the authorisation ID that created the object in the first place, is given permissions to perform certain operations on it. If you run DDL scripts in production as the instance owner, then this would be the owner ID of all objects after the restore, no matter the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES setting. You could, of course, use the TRANSFER OWNERSHIP statement to change that, but in real life, with tens of thousands of objects in the database, this may not be practical.

To avoid the "secondly" part, in many organisations folks use a special "builder" authorisation ID, separate from the instance owner, to run DDL scripts. This ID will not be given any unnecessary privileges, can be tightly controlled, and, when existing in all environments, should be able to perform all necessary tasks without adding security exposure.


non clustered index coverage with INCLUDED columns

You didn't show us any queries.

Perhaps you asked whether IX3 will be a full substitute for 1 and 2? If so:

Pretty much. Except:

For IX1, SQL Server can navigate the index tree to find all values for COL1 and keep navigating the tree to find all COL2.
Also, IX1 can potentially assist in eliminating a sort for COL1, COL2 DESC.

Except for above cases, as far as I can see, IX3 will be a substitute for both IX1 and IX2 (pls comment other cases I missed and I'll add them to the above list).

DESC has no bearing on included columns. They are just "brought along", they don't influence any sorting for the index (and the values are only in the leaf).


How do I allow non sysadmin accounts the ability to create/query linked servers in SQL Sever 2014?

According to Article: Access to the remote server is denied because no login-mapping exists.

Need to specify User ID in the provider string if Non-SYSAdmin accounts are used.

Example:

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'SQLNCLI',
    @provstr = N'SERVER=MyServerName\MyInstanceName;User ID=myUser'


2 Different Oracle DB Version on same server

Yes, it is possible.

https://docs.oracle.com/database/121/NTDBI/intro.htm#NTDBI2648

  Oracle Database supports multiple Oracle homes. You can install this
  release or previous releases of the software more than once on the
  same system, in different Oracle home directories. This allows
  flexibility in deployment and maintenance of the database software.
  For example, it enables you to run different versions of the database
  simultaneously on the same system...


Does canceling update operation perform automatic rollback

If you do that, the statement is rolled back, not the transaction. Below I hit Ctrl+C after starting the UPDATE.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

23849 rows created.

SQL> insert into t1 select * from t1;

47698 rows created.

SQL> insert into t1 select * from t1;

95396 rows created.

SQL> insert into t1 select * from t1;

190792 rows created.

SQL> insert into t1 select * from t1;

381584 rows created.

SQL> commit;

Commit complete.

SQL> conn bp/bp
Connected.
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                                   0
transaction rollbacks                                                     0

SQL> set timing on
SQL> update t1 set object_id = object_id + 1;
update t1 set object_id = object_id + 1
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:00:01.51

SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                              173323
transaction rollbacks                                                     1

Elapsed: 00:00:00.00
SQL>

1 transaction was rolled back and 173323 undo records were applied to perform the rollback. But that is because I had no other change in that transaction.

If I have other changes in the transaction, those changes will not be rolled back automatically:

SQL> conn bp/bp
Connected.
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                                   0
transaction rollbacks                                                     0

Elapsed: 00:00:00.00
SQL> insert into t1(object_id) values (-1);

1 row created.

Elapsed: 00:00:00.00
SQL> select count(*) from t1 where object_id = -1;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.09
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                                   0
transaction rollbacks                                                     0

Elapsed: 00:00:00.00
SQL> update t1 set object_id = object_id + 1;
update t1 set object_id = object_id + 1
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

Elapsed: 00:00:02.16

SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                              274294
transaction rollbacks                                                     0

Elapsed: 00:00:00.01
SQL> select count(*) from t1 where object_id = -1;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.04
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.00
SQL> select count(*) from t1 where object_id = -1;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.06
SQL> select name, value from v$mystat mt join v$statname sn on (mt.statistic# = sn.statistic#) where sn.name in ('transaction rollbacks', 'rollback changes - undo records applied');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
rollback changes - undo records applied                              274295
transaction rollbacks                                                     1

Elapsed: 00:00:00.00

Notice how the value of transaction rollbacks remained 0 this time, and how the INSERT was not rolled back, and I was still able to rollback the transaction manually.


| root   | %         | - This is the root user from any computer
| root   | 127.0.0.1 | - This is the root user for localhost without resolved IP for IPv4
| root   | ::1       | - This is the root user for localhost without resolved IP for IPv6
| root   | localhost | - This is the root user for localhost with resolved IP
| root   | mysrverhostname | - This is the root user if you connect from the localhost to its externel IP.

You could the delete the first one if you don't like to connect from another computer to this server as root.

All other root users are useful, because you can do the follow types of mysql connections:

mysql -h 127.0.0.1 -u root (IPv4)
mysql -h ::1 -u root (IPv6)
mysql -h localhost (If the mysql server is configured to resolve IPs, the the 2 above would get used)
mysql -h 192.168.0.1 -u root (the IP of the server)
mysql -h mysqlserver.local -u root (the hostname of the server)

and if I remember correctly the "localhost" user get used if you connect using a socket connection.


problem with service broker in always-on

I'm sure this is sorted now, but you have to enable service broker before creating the availability group.  This message suggests that you created the AG, then tried to configure service broker afterwards.


Formal name for functions that use the OVER clause

I believe the term you are looking for is window functions. Most databases have support for these types of functions.

Window functions belong to a type of function known as a ‘set function’, which means a function that applies to a set of rows. The word ‘window’ is used to refer to the set of rows that the function works on.
Windowing functions were added to the standard SQL:2003 that is managed by the ISO and it was specified in more detail in SQL:2008 For some time, other DBMSs such as Oracle, Sybase and DB2 have had support for window functions. Even the open source RDBMS PostgreSQL has a full implementation. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012.

Source
A window function is one of:

A rank function.
A distribution function.
The row number function.
A window aggregate function.
The ntile function.
The lead function.
The lag function.
The first-value function.
The last-value function.
The nth-value function.


SSIS ODBC: Mapping result set columns to variables returns error "Value does not fall within expected range"

With ODBC in SQL task the first position is 1 not 0, this totally cured my problem. Also note that ODBC does not seem to recognize the alias so do not use the alias as the resultName. It seems that the message "expected range" is referring to the result set positioning; i.e. '0' or a name is not seen in the range of variable positions (such as 1 and 2 if you have two selected, 0 or 3 is out of range)


The system_traces.events and system_traces.sessions tables are used to troubleshoot performance problems.

What you are after is audit capacity. In Apache Cassandra, this is still work in progress, you can follow the work with the following JIRA ticket.


CALCULATE TOTAL NUMBER OF ESTIMATED HOURS

When I look at the question, it is not important what you create to achieve the result: calculate and return the total number of estimated hours involved in a given stage at the date of the estimate.

I would advise though to just create a query to return the result.


postgres: Insert multiple rows into a table with id from other table if not exists insert to other table

Why not split up your separate goals into separate queries to handle them appropriately? It seems a little dangerous to be modifying data within a CTE that is then used to be read from, though not sure if this is common convention in PostgreSQL.
For example:
WITH CTE_UsersToBeAdded AS
(
    SELECT 'a' AS username
    UNION ALL
    SELECT 'b' AS username
    UNION ALL
    SELECT 'c' AS username
)

INSERT INTO users (username)
SELECT c.username
FROM CTE_UsersToBeAdded c
LEFT JOIN users u
    ON c.username = u.username
WHERE u.usernams IS NULL; -- Filters out already existing usernames

INSERT INTO feedbacks ('static_row', userid)
VALUES
('sample_data', (Select id from users where username='a')),
('sample_data', (Select id from users where username='b')),
('sample_data', (Select id from users where username='c'))


Calculate Average between two dates of different rows

WITH ct AS
(
SELECT
    CASE WHEN AppStatus = 'Approved'
              AND LAG(AppStatus)
                  OVER (PARTITION BY BIDID, AppID ORDER BY [Time]) = 'In Review'
         THEN
             DATEDIFF (day, LAG([Time]) OVER (PARTITION BY BIDID, AppId
                                             ORDER BY [Time]), [Time])
         ELSE
             0
    END days
FROM
    t
)
SELECT
    SUM(days) / COUNT(*) Average
FROM
    ct
WHERE
    days <> 0;

The CTE part calculates DATEDIFF with the previous row, every time it finds an 'Approved' AppStatus after an 'In review'AppStatus.

BIDID | AppID | AppStatus | Time                | days
----: | ----: | :-------- | :------------------ | ---:
23390 |    16 | In Review | 07/03/2017 00:00:00 |    0
23390 |    16 | Approved  | 09/03/2017 00:00:00 |    2
23390 |    16 | In Review | 10/11/2017 00:00:00 |    0
23390 |    16 | Approved  | 12/11/2017 00:00:00 |    2
23390 |    16 | Approved  | 14/11/2017 00:00:00 |    0

The simply sums the calculated days and divide by the rows that has calculated days.

| Average |
| ------: |
|       2 |

db<>fiddle here

UPDATE

Due you are on 2008 and as per comments you pointed out there is an IDENTITY column, you can simulate LAG/LEAD function using an APPLY join with the next ID.

WITH ct AS
(
SELECT
    CASE WHEN t1.AppStatus = 'In Review' AND t2.AppStatus = 'Approved'
         THEN DATEDIFF(day, t1.[Time], t2.[Time])
         ELSE 0
    END as Days
FROM
    t t1
CROSS APPLY(SELECT TOP 1 *
            FROM t
            WHERE id > t1.id
            ORDER BY id) t2
-- WHERE id < 100
)
SELECT
    SUM(days) / COUNT(*) Average
FROM
    ct
WHERE
    days <> 0;

db<>fiddle here


Uber-wide tables: Time to switch to Mongo?

1) Consider how this information will be used.  Will the owner want to try lots of ad hoc analysis and data exploration?  Does your potential data store have tools or connection libraries that allow this?  Will the user have to resort to writing Map and Reduce functions for every question posed?

2) Do you mean that the same question is on different questionaires and you're wondering if you should recognise they're the same in your data store design?  Yes, absolutely you should, whatever thechnology you end up with.

3) The DBMSs have column limits, too.  They may be larger, but they're not infinite.

4) Store your data as one row per answer.  There are several qustions here on quiz design. Here's one picked at random.  These will give you pointers and any detailed questions remaining will be good material for future DBA.SE posts.


MS Sql Server Management Studio gives error on start

Error code 3417 means that you have problems with your master database. More detailed information on this error can be found here: (http://msdn.microsoft.com/en-us/library/aa337262%28v=sql.105%29.aspx). The list of SQL server errors can be found in the MSDN by following address: http://msdn.microsoft.com/en-us/library/cc645601(v=sql.105).aspx. The solution is to recover the master database from the last backup. After this the SQL server will start and you will be able to connect.


Removing a FILESTREAM windows share after uninstall

I managed to find this answer on Stack Overflow.

https://serverfault.com/questions/543326/how-to-remove-a-windows-share-when-the-folder-does-not-exist-anymore/543350#543350

I removed the MSSQLSERVER share key from the registry at the below path, and rebooted. The share is no longer there in net share in command prompt.

I was able to proceed with my re-installation with FILESTREAM enabled.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares


What type of locks are needed when creating a Postgres index concurrently?

Well, when documentation is not enough it is possible to looking directly in code. I will give links to already released PostgreSQL 13.1 (REL_13_1 tag) in order to avoid possible line number changes in future.
I probably will not describe the entire code path of the command from the client. By grammar we know that we need IndexStmt command node - same grammar for both concurrently and non-concurrently create index. Everything useful will start in ProcessUtilitySlow
And here, in comment to case T_IndexStmt, can be found direct answer for question:
/*
 * .... To avoid lock upgrade hazards, it's
 * important that we take the strongest lock that will
 * eventually be needed here, so the lockmode calculation
 * needs to match what DefineIndex() does.
 */
lockmode = stmt->concurrent ? ShareUpdateExclusiveLock
    : ShareLock;

In general PostgreSQL will do not upgrade lock during command execution. And will take the strongest lock that will eventually be needed. For CREATE INDEX CONCURRENTLY it's ShareUpdateExclusiveLock. Not a SHARE lock as mentioned in question.
But let's continue reading:

in case of partitioned table find_all_inheritors will acquire same lockmode on inheritors.
in transformIndexStmt can be found some locking modes, but NoLock and AccessShareLock - they are weaker than ShareUpdateExclusiveLock
in DefineIndex we see another reminder:

     * To avoid lock upgrade hazards, that lock should be at least
     * as strong as the one we take here.

And decide to take againg ShareUpdateExclusiveLock for concurrently build. This part of the code is noticeably long (still not as large as, for example, planner), but readable.

I am missing something? Probably. So let's build PostgreSQL with -DLOCK_DEBUG option to debug locking behavior and see that happens:
./configure  --prefix=/home/melkij/tmp/pgdev/inst --enable-cassert --enable-debug CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG" --enable-tap-tests
make -sj 4
make install
initdb ...
pg_ctl start ...
# then psql with follow commands
create table foo as select generate_series(1,1000) as id;
set trace_locks = true;
create index concurrently on foo (id);
select oid, relname, relkind from pg_class where oid in (16387,16393);
#  oid  |  relname   | relkind
#-------+------------+---------
# 16387 | foo        | r
# 16393 | foo_id_idx | i

trace_locks will log many things, but we are interested in:
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] AccessExclusiveLock
[vxid:3/62 txid:495] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] ExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] ExclusiveLock

lock [12664,16387] here means database OID = 12664, pg_class OID = 16387 (such rows are logged from LockAcquireExtended).
So, we actually acquire only ShareUpdateExclusiveLock on table itself. OID=16393 with several other locks is the index that this command built. Although heavy locking levels are mentioned for this object, it will not interfere with other queries. Create index concurrently command is specially designed to work safely while running normal application queries.


Make sure two columns are sorted in the same order

I don't think it's easy to make such a guarantee and even if you do manage to make such an assertion actually work, it may slow down your Inserts into the table.

Since this is a performance issue for the ordering queries, here's a suggestion:

Make (CreateDateTime, ID) or (CreateDateTime) the clustered index of the table, instead of the (usual) clustered index on (ID) alone.

Then you can run queries with ORDER BY CreateDateTime and they will use the clustered index.


How can I generate TPC-DS queries for PostgreSQL?

Not sure about the TPC-DS, but I know that HammerDB will perform a TPC-H(ad hoc DSS) benchmark test against PostgreSQL.  If you're looking for a benchmark, but not specifically a TPC-DS, this is a good tool for testing.


MySQL count time between each in/out and show total

What's the question?

If you need to add up the last column, there two ways:

SELECT ... SUM(minutes) ...
    FROM (
          your query
         ) x
    GROUP BY ...

or...

GROUP BY ... WITH ROLLUP

Add WITH ROLLUP to your query and see if that is enough.


It works for me. The H2 change log says it was added in version 1.3.171. Here is the original bug

alter table credentials add column domain VARCHAR(255) after credentialType;

What didn't work for me was having multiple adds inside one alter.


Postgres, RDW, WHERE condition not pushed to remote (JSONB attribute, using postgres_rdw)

I believe the reason for this is that in details->>'hostname', 'hostname' is of a text type, and PostgreSQL cannot prove to itself either that the same default collation is in use on both sides of the FDW, or that the ->> operator will always return the same answer regardless of collation of its right argument.  So it won't push it over.

There has been some talk of adding a way to mark functions and operators as not being sensitive to collation.  But that won't done until at v12 at the earliest.


Incredibly long execution time for a update query?

Your problem is simple
PROBLEM #1 : KEY DISTRIBUTION
The MySQL Query Optimizer evaluates the best course of action and publishes it in the EXPLAIN plan. Note that the EXPLAIN plan says selectType SIMPLE while using the index on the state. That is an index scan.
I have a basic rule-of-thumb I always go by : If an index has to read more that 5% of a table while perusing an index, it starts doing scans. You are basically at a tipping point. Why ???

205 rows to be updated
3504 total rows
5.85% of the table rows being read/updated

I am not just coming up with this. I have always mentioned this in the DBA StackExchange (Se my post from Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY? and the other posts mentioned in it)
PROBLEM #2 : CONFIGURATION OF HARDWARE
The table is way too small to say it's just indexing. If the hardware you are using is small (1 vCPU), don't expect performance of any kind with MySQL 5.6. There are corner cases where MySQL 4.1 would outperform MySQL 5.6 in a single CPU environment. You are probably in that corner right now.
See my posts

Apr 26, 2012 : Is the CPU performance relevant for a database server?
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

WHERE TO GO FROM HERE ...
You best bet at this point would be to run OPTIMIZE TABLE invoices;. This will shrink that table (eliminating fragmentation) and refresh the index stats. In a high-write production environment, you could run ANALYZE TABLE invoices; to refresh index stats only and run OPTIMIZE TABLE invoices; during a maintenance window.
Also, try setting up more that 1 CPU for test servers.
GIVE IT A TRY !!!


Is MariaDB easier and better suited for a WordPress website?

1) There is very little difference between MySQL and MariaDB, especially for a beginner. Stick with whatever ships with your preferred Linux distribution.

2) You cannot do this even between different versions of MySQL and MariaDB.

2.1) You can only upgrade in-place, and not downgrade. If you need to downgrade, you will have to mysqldump and import on the new version.

2.2) If you are upgrading, MySQL and MariaDB versioning differs, so choose carefully if you intend to switch in-place. In some cases, upgrading requires special preparation, e.g. between MariaDB 10.1 and earlier / MySQL 5.7 and earlier to MariaDB 10.2 and later or very recent MySQL 8.0.x.

Bottom line - don't version hop. Pick one and stick with it until you know exactly what you are doing.

3) Yes, MariaDB speaks the same wire protocol as MySQL so anything that will run against one at a low level will run against the other.


By adding raise notice. It will keep printing value of i -- so that you will always have idea about loop progress.
    DO
    $do$
    BEGIN
    FOR i IN 1..20000
    -- update some rows
    raise notice 'Current Progress Value i:%',i;
     LOOP
    END LOOP;
    END;

Or more better way.

Create a sql file  : vi loop.sql  -- add your loop into this file with raise notice.

Run as a job: psql -U username -d database_name -a -f loop.sql &>progress_log

Keep monitoring latest id : tail progress_log

Note: One more tip- I think you need to initialize i - need to include a declare section. Like below:(sample)
    DO
    $do$
    DECLARE
    i int8;
    BEGIN
    -----your code--
    END;


Usage of ApplicationIntent Parameter in SSIS connections

This is by design

Depending on the SNAC protocol you are using there is a slight variation in the application intent connection string property.

When using SQL Native Access Client SQL OLE DB, the application intent connection string should be specified with a space: 'Application Intent'
When connecting using SQLClient or ODBC specify 'ApplicationIntent' with no space

https://blogs.msdn.microsoft.com/alwaysonpro/2013/08/02/connect-to-sql-server-using-application-intent-read-only/


INSERT rows in two tables preserving connection to a third table

It took me an awfully long while to figure it out, but here it goes:

Setup:

CREATE TABLE sites
(
  site_id INT PRIMARY KEY
);

CREATE TABLE properties
(
  property_id SERIAL PRIMARY KEY,
  property_def_id INT NOT NULL
);

CREATE TABLE site_properties
(
  property_id INT NOT NULL,
  site_id INT NOT NULL,
  PRIMARY KEY (property_id, site_id),
  FOREIGN KEY (property_id) REFERENCES properties(property_id),
  FOREIGN KEY (site_id) REFERENCES sites(site_id)
);

INSERT INTO sites VALUES (101),(102),(103);

INSERT INTO properties(property_def_id) VALUES (3), (4);
INSERT INTO site_properties(property_id, site_id) VALUES (1, 101), (2, 101);

Actual query:

WITH props AS (
  INSERT INTO properties (property_def_id)
    SELECT 1 FROM sites
  RETURNING property_id
), new_props AS (
  SELECT row_number() OVER () AS row, property_id
    FROM props
), all_sites AS (
  SELECT row_number() OVER () AS row, site_id
    FROM sites
)
INSERT INTO site_properties (site_id, property_id)
SELECT s.site_id, np.property_id
FROM all_sites s
  JOIN new_props np USING (row)
;

Checking everything works as expected:

SELECT *
FROM site_properties
  JOIN properties USING (property_id);

property_id | site_id | property_def_id
---------------------------------------
     1          101            3
     2          101            4
     3          101            1
     4          102            1
     5          103            1


Returning auto-generated primary key after SQL insertion

Nevermind. I used mysqli_insert_id and it worked.  The documentation for this solution is available here.


Windows Storage Server, SQL Server, regular files -- means to protect from insider threats?

What you can do is restrict access to the SQL Server and the Windows server that SQL is on. There are server level roles, with sysadmin giving you the highest privileges. Review Server-Level Roles to see what you will need to give your temp. The temp should never be in the sysadmin server level role, based on what I read from your requirements.

Then there are database level roles. This is set at each database level for each DB. See Database-Level Roles to read more. Your temp may be in the db_backupoperator, or the db_datareader role for each DB. This will prevent him from deleting/modifying the DBs.

Encrypt your confidential data. You can also use passwords for your backups, but this feature is not meant for security, rather is helps maintain related backup sets.

On the Windows server part, you can further restrict access. Querying the SQL Server can be done from a laptop with SSMS installed on it, thereby not having to give your temp much rights on the Windows server.

To track changes on the DB you will need auditing. There is a plethora of information in the Auditing in SQL Server 2008 Microsoft technical article.

Remember, too much auditing comes at the cost of performance. With SQL Server 2008, there are new and much granular auditing controls, like CHANGETABLE and policy-based management. These can help with your Tracked down, Proven, and Rectified requirements.

Above is just a quick summary of the possibilities.


Let me give you a very broad piece of advise: I think you are trying optimisation in the wrong way. You are doing "We have spare memory, let's use it for something else" (which is otherwise very lucky and many people will envy you) instead of "let's identify our bottleneck and fix that with our resources".

I am not telling you that your idea is wrong, but in general, scaling out with slaves does not provide a better latency (and in many other cases, clustering it reduces it), but a better throughput (which if you were under high load, can sometimes mean better latency). I do not know if that is a problem that must be solved in your case. While having multiple mysql servers in the same machine is common, for example, in the hosting industry, they do not do it for performance, but for a cost-saving reason (consolidation). Also have into account that you may be more than doubling your maintenance/administration costs.

My real advice would be to identify your bottleneck: latency, throughput on where? Memory? Disk? Network? And try to use the extra resources in the best way- Are temporary tables being created? Can we use ramdisk for that? Are many queries too slow even if they are executed on memory? Can we setup a on-memory key-value cache system (memcache, by the way, integrated on MySQL 5.6-InnoDB) for caching results of frequent queries? Your write throughput is maximised? Maybe you need sharding. Your read throughput is maximised while your write throughput, network and disk bandwidth is low? Maybe your idea is ok. But you need metrics in order to support such an important architecture decision.


Schema design for metered billing depending on account usage

I regularly handle tables with 250GB of data that needs "counting" (Example: https://phabricator.wikimedia.org/T124676).

Let me start by saying that MySQL is not a good place for these kind of queries- its focus is OLTP (in a nutshell, short reads and writes in parallel), not OLAP. There are multiple storage/computing models (such as column-based data stores, or datawarehouse solutions that allows map/reduce jobs in parallel) that would be better suiting for your task, so if this is a business-critical/very frequent part of your needs, I would suggest setting up a complementary service that allows easier/faster handling of these kind of tasks. The reason is that counting things on MySQL usually require "snapshoting" and reading all rows one by one, and that can only be done on a single thread.

Having said that, your needs may not merit a full-blown solution, and only make that specific need faster. You can implement analytics by implementing such functionality on regular MySQL objects. The implementation will depend on your requirements, and that is mainly: "freshness" of your data, its dynamic nature, concurrency and if it is append-only.

If your counter does not need to be 100% accurate, you can run counting jobs every X time. Make sure no more than 1 job is running at each time by locking on a particular resource. We do such things with special pages such as: https://en.wikipedia.org/wiki/Special:WantedPages
If data is not always modified, but it needs to be more or less up to date with the rows, you can start a background thread that gets notified when data changes. If it gets notified several times before it runs, it de-duplicates the job by updating it only once. We do such a thing for Category updates: https://en.wikipedia.org/wiki/Category:Contents
Both of the "counters" from the previous tables are stored on auxiliary tables.
If you need to have perfect counting, you need to cache it with a trigger-like execution. It doesn't need to be a on server side, but be part of the transaction that updates the row. This will be more costly, as it will increase the latency of each individual DML, but it may be interesting in some cases. In our model, user have (or at least, used to have) an edit counter that it is transactionally updated every time an edit is created or deleted.
If your data is append-only, you can avoid counting all data every time, and only count the most recent records. Store the last counted record and then the total/per month/yearly/etc. count on another column, per item. We use such a model for counting things like number of visits per article.

In general, with MySQL you are forced to count- whether you cache or precache that value is a question of handling caches, which is a very common problem (although as you may know, not a simple one).

One common mistake is to store a single counter field when there is a lot of concurrency- e.g. visits for a webpage. For such cases you can distribute your counters according to your structure (e.g. one per fronted) or just use some kind of hashing table so you do not have to serialize every single update. On actual counting, you can sum the much fewer counters, which will be easier than counting the individual values.

If you need to speed up counting, there are even frameworks such as Shard-query that could simulate the panellization of queries for MySQL to some extent.

The summary is that you have 3 options: make things consistent, make them fast or make them cheap, choose 2 :-)


Depending on the amount of data being deleted, and if you are in the full or bulk-logged recovery model. Then you might want to look into adjusting the TOP N value and/or increasing your t-log backup frequency.

DECLARE @MinDate    DATETIME = DATEADD(DAY,-60, GETDATE())
        ,@Maxdate   DATETIME = DATEADD(DAY,-30, GETDATE())
        ,@RowCount  INT = 1

WHILE   @RowCount > 0

BEGIN

        DELETE  TOP (20000)
                tn
        FROM    table_name tn
        WHERE   order_date >= @MinDate
        AND     order_date <= @Maxdate

        SET @RowCount = @@ROWCOUNT

    RAISERROR('%d rows deleted', 10, 0, @RowCount) WITH NOWAIT

END


Update MySQL user table of active/inactive members based on array

Don't use EXISTS or IN ( SELECT ... ).

Use a multi-table UPDATE.  Twice.

Once with a JOIN to set the active rows.
Once with a LEFT JOIN ... IS NULL to set the inactive rows.

Or...

Once to set all users to inactive.
Once with a JOIN to set the active rows.


Help understanding discrepency between sys.dm_exec_cached_plans and dm_os_memory_clerks

FWIW - and I'm only posting this as a temporary answer because there's no way I could make this a digestible comment - I ran these queries and saw some discrepancies as well:

select cp.cacheobjtype, cp.objtype,
  sum(cast(cp.size_in_bytes as DECIMAL(19,4)))/1024/1024 as sizeMB
from sys.dm_exec_cached_plans as cp
group by cp.cacheobjtype, cp.objtype
ORDER BY sizeMB DESC;

select mc.type, mc.pages_kb/1024 as pagesMB
from sys.dm_os_memory_clerks as mc
where mc.type LIKE N'CACHESTORE[_]%'
ORDER BY pagesMB DESC;

Most notably, the CACHESTORE_SQLCP total in the second query is less than the four Compiled Plan results in the first query, and there was substantial memory clerk usage under other cachestores as well. This is a lowly VM that doesn't have 300 GB of RAM but still demonstrates that these numbers do not always correlate and add up nicely.


Can't connect to remote SQL server from some machines

Here are some of the things from my connection issue checklist

Check for an alias on the machine that can't connect
Check for firewall rules
Check and make sure the SQL Server drivers on the machines that don't work are up to date.
Try connecting with servername:port rather than just servername
Try pinging the SQL Server IP address from the machines that can't connect.


TOAST table is treated as system table , so you need to query pg_stat_sys_tables, not pg_stat_user_tables:

select relname, vacuum_count, n_tup_del, n_live_tup, n_dead_tup
from pg_stat_sys_tables
where relname='pg_toast_147777'

Or you can put it all together,

select  cl.relname ,vacuum_count, n_tup_del, n_live_tup, n_dead_tup
 FROM pg_class cl
 inner join pg_stat_sys_tables st on st.relid = cl.reltoastrelid
 WHERE cl.relname = 'messages1';

Quoting the manual:

  The pg_stat_all_tables view will contain one row for each table in the
  current database (including TOAST tables), showing statistics about
  accesses to that specific table. The pg_stat_user_tables and
  pg_stat_sys_tables views contain the same information, but filtered to
  only show user and system tables respectively.


How to get Size of db tables one month back

Unless you keep a log of it , you cannot. There is no history mechanism to keep track of tbl sizes.

you also can get a lot of scripts and articles on Vertica here www.aodba.com

What i do to keep an eye on this kind of stuff is logging this info in a dba use table using a cron job.

##will run every 30 minutes
*/30 * * * * /home/dbadmin/scripts/dba/database_growth.sh

Content .sh script

#!/bin/bash
#!/bin/bash

. /home/dbadmin/.profile
export VSQLDIR=/opt/vertica/bin
export SQLDIR=/home/dbadmin/scripts/dba
export data=`date`
echo " Execution date -" $data >> /home/dbadmin/scripts/dba/log/database_growth.log
echo "#################################################" >> /home/dbadmin/scripts/dba/log/database_growth.log
$VSQLDIR/vsql  -U $username -w $password -d analytics -f $SQLDIR/database_growth.sql >> /home/dbadmin/scripts/dba/log/database_growth.log
echo "#################################################" >> /home/dbadmin/scripts/dba/log/database_growth.log

Note:/home/dbadmin/.profile -- holds all the parameters values (this is to avoid text passwords - they are all logged as text - you wanna avoid this )
Content database_growth.sql

 insert INTO dba.database_growth(time,schema_name,size_gb) SELECT * from dba.pre_database_growth;

Content view dba.pre_database_growth

CREATE VIEW
    dba.pre_database_growth AS
SELECT
    (statement_timestamp())::TIMESTAMP AS GETDATE,
    p.anchor_table_name,
    s.schema_name,
    ROUND(((((SUM(s.used_bytes) / 1024::NUMERIC(18,0)) / 1024::NUMERIC(18,0)) /
    1024::NUMERIC(18,0)))::FLOAT, 3) AS size_in_bytes_gb
FROM
    v_monitor.storage_containers s join v_catalog.projections p
    on s.projection_id=p.projection_id
GROUP BY
    s.schema_name,p.anchor_table_name;

You can use this then to create growth reports on your objects over time.

I hope this helped.


problem running xp_cmdshell when Application Name set in connection string

the database(s) associated with the application should be trustworthy in order to access the machine's command line.

Try

select name,is_trustworthy_on from sys.databases;

if your target database is not trustworthy then:

alter database db_name set trustworthy on;

but beware the compromise to security


Workaround the snapshot isolation in a writable CTE

If you return * from the CTE then you are effectively reading the row that you are currently trying to read from t1 -- that's how you mange to read the changes you're making to t1.

In your current code the join to t1 is redundant by the way, but I get the impression that you're using an example more simple than you really want.


Restoring a replicated SQL Server database in a non-replicated testing enviroment

You have to just restore the database using

restore database <db_name>
from disk = 'Disk path\db_name.bak'
with recovery, replace -- only if there is already database that you need to overwrite
,stats =10,
move 'logical_data' to 'physical_path.mdf',
move 'logical_log' to 'physical_path.ldf'

once the database is restored, you can remove the replication bit

sp_removedbreplication 'database_name'


SQL Server : keep track of a running calculation monthly

Here is my idea:
-I used recursive approach,  - for this , added a Row_number, used this to join previous records

;WITH cte_Source AS
(SELECT
    year_month
    ,original_threshold
    ,manualChange
    ,CONVERT(int,LEFT(year_month,4)) As mYear
    ,CONVERT(int,RIGHT(year_month,2)) As mMonth
    ,ROW_NUMBER()OVER(ORDER BY CONVERT(int,LEFT(year_month,4)),CONVERT(int,RIGHT(year_month,2))) AS RN
FROM
    #Test AS T
)
,cteR AS
(
    SELECT TOP(1)
        T.year_month
        ,T.original_threshold
        ,T.manualChange
        ,0 AS change
        ,T.original_threshold  +  0 + T.manualChange AS  new_threshold
        ,T.RN
    FROM cte_Source AS T
    --WHERE T.year_month ='2017_01'
    ORDER BY T.mYear * 100 + T.mMonth ASC

    UNION ALL

    SELECT
        S.year_month
        ,S.original_threshold
        ,S.manualChange
        ,CASE WHEN  S.original_threshold > R.new_threshold THEN R.new_threshold - S.original_threshold ELSE 0 END AS  change
        ,S.original_threshold
            + CASE WHEN  S.original_threshold > R.new_threshold THEN R.new_threshold - S.original_threshold ELSE 0 END
            + S.manualChange AS  new_threshold
        ,S.rn AS rn
        FROM cte_Source AS S
            INNER JOIN cteR AS R
            ON r.RN + 1 = S.RN

)

select
    [year_month]
    ,[original_threshold]
    ,[change]
    ,[manualChange]
    ,[new_threshold]
from cteR

output for it:

year_month original_threshold change      manualChange new_threshold
2017_01    458                0           0            458
2017_02    439                0           0            439
2017_03    412                0           0            412
2017_04    390                0           0            390
2017_05    393                -3          0            390
2017_06    389                0           -7           382
2017_07    397                -15         -5           377
2017_08    396                -19         0            377
2017_09    401                -24         0            377


return percentage of records based on value in column

;WITH CTE
     AS (SELECT OrganizationID,
                CustomerID,
                OrderID,
                COUNT(*) OVER (PARTITION BY OrganizationID) AS C,
                ROW_NUMBER() OVER (PARTITION BY OrganizationID
                                       ORDER BY (SELECT 0))     AS RN
         FROM   YourTable)
SELECT OrganizationID,
       CustomerID,
       OrderID
FROM   CTE
WHERE  RN <= CEILING(0.5 * C)

SQL Fiddle


Does `SERIAL PRIMARY KEY` create two indexes on the table?


How to pivot without fixed columns in TSQL?

Prior to writing a dynamic SQL query, you should always write a version that is hard-coded so you can get the syntax correct. So the first thing you need to do is write a working PIVOT query for any of the TruckId values that you need.

Static Version:

Let's say you need TruckID = 3, your code for the PIVOT would be similar to the following:

select Name, TruckId, [1], [2], [3], [4], [5], [6]
from
(
  select Name, TruckId, Compartment, Capacity
  from Trucks t
  inner join Compartments c
    on t.Id = c.TruckId
  where t.Id = 3  -- your truck id here
) d
pivot
(
  max(capacity)
  for compartment in ([1], [2], [3], [4], [5], [6])  -- your Compartment values here
) p;

See SQL Fiddle with Demo

Dynamic Version:

Now, your problem is that you are going to have a variety of Compartment values for any of your TruckIds so you need to write something that will accept any TruckId and produce the results that you want.  The PIVOT function will work but first you'll need to concatenate a SQL string with all the Compartment values for each TruckId.

First, you'll define all your parameters:

DECLARE
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @ParmDefinition NVARCHAR(500),
  @TruckId as int;

set @TruckId = 3;  -- this would be the value you submit via your SP
set @ParmDefinition = '@id int';

Next, you'll concatenate the list of the new column headers, using FOR XML PATH and STUFF:

select @cols = STUFF((SELECT ',' + QUOTENAME(Compartment)
                    from #Compartments
                    where TruckId = @TruckId
                    group by id, Compartment
                    order by Id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

Once you have the list of columns, you'll create the full sql string that will be executed. This should be similar to the static version above:

set @query
    = N'SELECT Name, TruckId, ' + @cols + N'
        from
        (
            select t.Name, c.TruckId, c.Compartment, c.Capacity
            from #Trucks t
            inner join #Compartments c
              on t.Id = c.TruckId
            where t.Id = @id
        ) x
        pivot
        (
            max(Capacity)
            for Compartment in (' + @cols + N')
        ) p ';

The last step is executing the sql string:

exec sp_executesql @query, @ParmDefinition, @id = @TruckId;

See a demo.  This gives a result of:

Name    TruckId 1   2   3   4   5   6   7   8   9   10
------- ------- --- --- --- --- --- --- --- --- --- ---
BBB-WWW 3       500 500 500 500 500 500 500 500 500 500


Getting 'Connection refused' after installing PostgreSQL and PG Admin 4

Edit postgresql.conf and change listen_addresses = 'localhost' to listen_addresses = '*'
Edit pg_hba.conf file and add an entry for the IP/network you are connecting from.
To allow anyone to connect add:

host    all             all             0.0.0.0/0            md5

Only one IP (10.0.0.10):

host    all             all             10.0.0.10/32            md5
Change firewall settings on the server to allow connections to port 5432.


How do we create spatial index on views if it is possible?

Unfortunately I don't believe you can.  XML and Spatial Indexes can only be created tables.  Not views.

Edit:

Here's some proof for you straight from BOL: Restrictions on Spatial Indexes

  Spatial Indexes cannot be specified on indexed views.


If a database only ever has one insert, is it bad to index every possible column combination?

Yes, it will influence initial plan compile time as the optimizer will have many extra access paths to the data to consider.

Since you're on SQL Server 2017, loading once, and running reports, why not just use a clustered column store index instead?

That seems to be the ideal solution to your need to index every possible column combination.

Columnstore indexes - Overview


How to toggle a boolean field by sql in postgresql?

This will do:

SET show = NOT show

so a value of

TRUE becomes FALSE,

FALSE becomes TRUE,

UNKNOWN (NULL) stays UNKNOWN.


Time series data of several sources in one SQLite table or several? File size impact?

Personally, I hate to throw away potentially useful data. I'd create a table for the sources, and include source_id in the time series table as a foreign key to the source table. This should take up less space, but still retain the source information (without requiring multiple identical tables).

I've put together a short example; see this db-fiddle link.

Here's the code form my example:

CREATE TABLE source
     ( source_id INTEGER PRIMARY KEY ASC
      ,name varchar(10)
     );

CREATE TABLE time_series
     ( series_id INTEGER PRIMARY KEY ASC
      ,timestamp INTEGER
      ,value1 INTEGER
      ,value2 INTEGER
      ,value3 INTEGER
      ,source_id INT
      ,FOREIGN KEY (source_id) REFERENCES source(source_id)
     );

INSERT INTO source (name)
VALUES ('AAAA'), ('BBBB'), ('QZQZ');

INSERT INTO time_series (timestamp, value1, value2, value3, source_id)
VALUES (12345678, 100, 105, 110, 1)
      ,(12345681, 105, 105, 105, 1)
      ,(12345684, 110, 105, 100, 1)
      ,(12345678, 9, 27, 81, 3)
      ,(12345681, 27, 81, 243, 3)
      ,(12345684, 81, 243, 729, 3)
;

SELECT * FROM source;

SELECT s.name as source, timestamp, value1, value2, value3
  FROM source s INNER JOIN time_series ts ON (s.source_id = ts.source_id)
 ORDER BY source, timestamp
;

I should note that I don't normally use SQLite. As you noted in your response to my original comment, SQLite maintains a 64-bit integer row ID column in every table by default. I've set up the tables in the example to use that row ID value as the primary key for each table. If I've read the documentation correctly, the foreign key column should just be big enough to hold the value from the primary key. Assuming you don't manually insert a source_id that's huge, I believe that source_id in the time_series table should only require 1 byte.

You might want to put a UNIQUE index on source_id and timestamp (you should almost certainly have some sort of index on them); presumably, you should never have two entries for the same source and the same time. As pointed out by Serge Stroobandt, this could even be the primary key. However, since SQLite will create a unique row ID value anyway, I'd be inclined to leave that as the key. If you really wanted to use source_id and timestamp as the primary key, look into creating the table WITHOUT ROWID.