MySQL How to copy all data in tables from one huge database to another in the same server

You can query information schema for list of all tables in your source database and build the import commands.

SELECT
    CONCAT('INSERT INTO target_db.', TABLE_NAME, ' SELECT * FROM source_db.', TABLE_NAME, ';') restore_command
FROM information_schema.TABLES
WHERE
    TABLE_SCHEMA='source_db'
    AND TABLE_TYPE='BASE TABLE'
ORDER BY TABLE_NAME;

Then copy/paste the output in query window.


Does SQL profiler affect server performance?

SQL Server Profiler is a GUI that utilizes SQL Server Trace through the client-side.  Due to this nature, yes you can potentially see a mild to severe performance impact depending on the environment.

SQL Server Profiler is suited mainly for a quick glimpse at what is happening on the server (provided the database server can handle the extra latency).  It is not intended to be run for long periods of time.  For longer traces, use a server-side trace or Extended Events.


If it was the client that force-closed, not the server, then the server has not gone through recovery.  The client going away in the middle of a transaction is the same thing as the client explicitly aborting the transaction.

Recovery is what happens when the server crashes. I think you have an incorrect mental model of how PostgreSQL handles transaction aborts. The old data is still there, simply because the transaction that would have rendered it invisible failed to commit.  No explicit recovery is needed.


Restore a clustered SQL database from a specific time

There isn't a difference between a restore on a standalone instance and one on a Failover Cluster Instance. There IS a difference if it is in an Availability Group.

Yes you can restore the FULL from Monday and the LOG from 9pm.

Whether or not you have to restore the system databases is dependent upon what you are needing to recover. If it's just data in the user database, then simply restoring the user database is sufficient.


What does Can't find file: 'table_name' (errno: 2) mean?

Connect to your database and run the following:

show tables;

This will list all tables in the DB. Are the ones you are trying to query from in the list?


You should be able to locate this within the default trace for SQL Server, if it is still enabled on the instance. One of the events captured is the Security Audit: Audit DBCC Event, can read more on the event here.

If you find the default trace is enabled the following query from MSSQLTips.com article to find when DBCC were executed:

DECLARE @path NVARCHAR(260)

SELECT @path=path FROM sys.traces WHERE is_default = 1

--Security Audit: Audit DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG,
--DBCC CHECKALLOC, DBCC CHECKFILEGROUP Events, and more.
SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (116) AND TextData like 'DBCC%CHECK%'
ORDER BY StartTime DESC


You just asked

  Does:

INSERT INTO table1... SELECT .. FROM table2

  Also create a lock on table2?

Yes, it does create a lock on table2.

I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation:

  By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot


how to show a specific value for a non existing value in second table in join?

Use an outer join and replce NULLs using COALESCE:

SELECT C.city_name, COALESCE(CS.count_staff,0) AS count_staff
FROM City AS C
LEFT OUTER JOIN count_staff_in_city AS CS ON C.city_id = CS.city_id;


'#' denotes a temporary table.

This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped.

You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Temporary tables are available in MySQL version 3.23 onwards. If you use an older version of MySQL than 3.23, you can't use temporary tables, but you can use heap tables.

Read about usage and syntax here.


Using LAG you can achieve your goal without any joins:

WITH cte AS
  (
    SELECT
      ProductID,
      SampleDate,
      MaxSampleDate = MAX(SampleDate) OVER (PARTITION BY ProductID),
      PrevFailed    = LAG(Failed, 1, 0) OVER (PARTITION BY ProductID, LevelCode
                                              ORDER BY SampleDate)
    FROM
      @x
  )
SELECT
  ProductID,
  IsLastRunSameLevelAsPreviousRun = PrevFailed
FROM
  cte
WHERE
  SampleDate = MaxSampleDate
;

The cte obtains the previous Failed state for the same LevelCode for each row within each ProductID group, returning 0 when there is no matching row. It also calculates the last date in each group to use it later to determine the last row in the group.

This is what it returns:

ProductID  SampleDate  MaxSampleDate  PrevFailed
---------  ----------  -------------  ----------
101        2015-12-01  2015-12-08     0
101        2015-12-05  2015-12-08     0
101        2015-12-08  2015-12-08     1
101        2015-12-06  2015-12-08     0
102        2015-12-02  2015-12-05     0
102        2015-12-04  2015-12-05     0
102        2015-12-05  2015-12-05     0
103        2016-01-01  2016-01-03     0
103        2016-01-02  2016-01-03     0
103        2016-01-03  2016-01-03     1
104        2016-01-01  2016-01-03     0
104        2016-01-02  2016-01-03     0
104        2016-01-03  2016-01-03     0

The main SELECT essentially just takes the last row of each group using the SampleDate = MaxSampleDate filter, pulling only ProductID and PrevFailed and also renaming the latter to IsLastRunSameLevelAsPreviousRun, so that the final output becomes what you want:

ProductID  IsLastRunSameLevelAsPreviousRun
---------  -------------------------------
101        1
102        0
103        1
104        0


100,000-ish records per second doesn't sound too bad as a headline number. Without context, however, it meaningless. What's the spec on the storage array? Are you getting even close to the manufacturer's IOPS and bandwidth numbers? How does it compare to a disk performance testing tool's reported values? When profiling the application under heavy load, where is the time taken? Is the system IO or CPU bound?

Some top-end servers do have ridiculous amounts of memory. 64TB is the largest I've seen. But you're right, holding absolutely everything in memory won't work in the general case. There is the concept of the working set. These are the rows that are being read and written by current users. The system holds these in memory and synchronizes them to disk from time to time. When users move on to new work the system makes room by purging the least recently used data from RAM.

For finding specific records you will need indexes. These trade additional disk and pre-processing time for reduced lookup time. There are many different indexing algorithms, each with benefits and drawbacks.

For large OLAP work it might be that you simply must read all of the data off disk to perform the query. It may be possible to pre-process to get summary values (cubes and similar).

This is a huge undertaking. Whole careers are spent trying to get this right. That you have a working product at all is impressive.


Getting an error ORA-00933 while using EXECUTE IMMEDIATE

At the end of the string, you're concatenating a final closing semi-colon; however, you don't have a closing quote after it.

Instead of:

AND RowNum < '||v_LastRec||';

Try this:

AND RowNum < '||v_LastRec||';'


Alternative ways to find tables with specific storage engine?

Login as root@localhost and query information_schema.tables by Storage Engine

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';

To locate the InnoDB tables that are belong to you, run this

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB'
AND table_schema NOT IN
('information_schema','performance_schema','mysql');

GIVE IT A TRY !!!


Is there some function in PostgreSQL to simply check if the input string is valid UTF-8 according to PG?

PostgreSQL is very strict about encodings, so you normally won't be able to get an incorrectly encoded string into the database. That is, your proposed function cannot operate on text, because you won't get an incorrectly encoded string into the database as text.

But if you load the string as binary data (type bytea), you can check the string with a function like this:

CREATE FUNCTION is_valid_utf8(bytea) RETURNS boolean
   LANGUAGE plpgsql AS
$$BEGIN
   PERFORM convert_from($1, 'UTF8');
   RETURN TRUE;
EXCEPTION
   WHEN character_not_in_repertoire THEN
      RAISE WARNING '%', SQLERRM;
      RETURN FALSE;
END;$$;

Here it is in action:

SELECT is_valid_utf8('\x736368c3b66e');

 is_valid_utf8
---------------
 t
(1 row)

SELECT is_valid_utf8('\x736368c3f66e');

WARNING:  invalid byte sequence for encoding "UTF8": 0xc3 0xf6

 is_valid_utf8
---------------
 f
(1 row)


Optimising an Update statemement with a very large Where clause

Instead of one massive update statement, use multiple update statements one for each where clause.

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = 'X Ray'
WHERE  [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- X-Ray -%')

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = 'BMD'
WHERE  [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- bone densitometry -%')


Why is Server Configuration in Pgadmin3 inaccessible for me?

adminpack extension

You need to install the adminpack extension on the server.

CREATE EXTENSION IF NOT EXISTS adminpack

Tips:

Verify the extension is available for installation.SELECT * FROM pg_available_extensions ORDER BY "name" ;
Verify installation.SELECT * FROM pg_extension ORDER BY extname ;


How do I revert the MUST_CHANGE in this SQL Statement?

That query didn't change anything so you don't need to revert anything.

That is just a simple select generating an ALTER LOGINstatement.

If you didn't execute the resulting statement nothing has been changed.


Difference of sets with different amount of columns?

If I recall, the difference operation requires the two relations involved to be compatible, implying that they should have the same number of columns.

Quoting from Wikipedia about relational algebra

  For set union and set difference, the two relations involved must be
  union-compatible—that is, the two relations must have the same set of
  attributes.


How to convert a MySQL database from utf8mb4 to utf8?

I just solved the charset problem by using Notepad++.

The problem was that my php version is 4.5.5.1 and the hosting firm's was 3.3.8. Because of that, it could not work with the utf8mb4_unicode_520_ci.

To solve the problem open the exported SQL file, search and replace the utf8mb4 with utf8, after that search and replace the utf8mb4_unicode_520_ci with utf8_general_ci. Save the file and import it into your database. After that, change the wp-config.php charset option to utf8, and the magic starts.

Note that it worked in a Hungarian database.


Why is it recommended to store BLOBs in separate SQL Server tables?

While I disagree that BLOBs should just be in another table -- they should not be in the database at all. Store a pointer to where the file lives on disk, and then just get that from the database...

The primary issue they cause (for me) is with indexing. Using XML with query plans, because everyone's got'em, let's make a table:

SELECT TOP 1000
ID = IDENTITY(INT,1,1),
deq.query_plan
INTO dbo.index_test
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq

ALTER TABLE dbo.index_test ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (ID)

It's only 1000 rows, but checking on the size...

sp_BlitzIndex @DatabaseName = 'StackOverflow', @SchemaName = 'dbo', @TableName = 'index_test'

It's over 40 MB for just 1000 rows. Assuming you add 40 MB every 1000 rows, that can get pretty ugly pretty quickly. What happens when you hit 1 million rows? That's just about 1 TB of data, there.

Any queries that need to use your clustered index now need to read all of that BLOB data into memory clarification: when the BLOB data column is referenced.

Can you think of better ways to use SQL Server memory than storing BLOBs? Because I sure can.

Expanding it to nonclustered indexes:

CREATE INDEX ix_noblob ON dbo.index_test (ID)

CREATE INDEX ix_returnoftheblob ON dbo.index_test (ID) INCLUDE (query_plan)

You can design your nonclustered indexes to largely avoid the BLOB column so regular queries can avoid the clustered index, but as soon as you need that BLOB column, you need the clustered index.

If you add it as an INCLUDED column to a nonclustered index to avoid a key lookup scenario, you end up with gigantic nonclustered indexes:

More problems they cause:

If anyone runs a SELECT * query, they get all that BLOB data.
They take up space in backups and restores, slowing them down
They slow down DBCC CHECKDB, because I know you're checking for corruption, right?
And if you do any index maintenance, they slow that down as well.

Hope this helps!


sp_help_jobsteplog fails when run as a @query of sp_send_dbmail

Did you try making the query 'EXEC msdb.dbo.sp_help_jobsteplog ''...'';' ? Chances are that internally there are other commands injected before the stored procedure is called, and if the procedure call is not the first in the batch, it must use EXEC or EXECUTE. I wish they would banish the ability to just say sp_anything without a preceding EXEC, it would make our lives far easier than the current savings of 5 keystrokes does.


Estimation of creating index in SQL Server

On SQL Server Std Ed, 2005-2014, there is no way to get est time remaining (and Sporri's answer on this thread is simply incorrect, sorry).

This thread has an actual answer to this question:
SQL Server: How to track progress of CREATE INDEX command?


Can PostgreSQL support integration test with some kind of throwaway overlay?

One way you could approach this, is to create a "base" test database (including all test data) once. Then before each test suite, create a new database using the "base" database as the template.

create database test_db
   with template = base_test_db;

Using this, everything that is in the template will be copied to the new database (including data, stored procedures, extensions).

After each test suite, you simple drop test_db and re-create it with the above statement.

This approach might not be fast enough if you do it before each test, but that highly depends on how large your test database is and how fast the harddisk of your build environment is.


I found a small workaround for this.
SELECT     tnsp.nspname                           AS schema_name,
           trel.relname                           AS table_name,
           irel.relname                           AS index_name,
           a.attname                              AS column_name,
           1 + Array_position(i.indkey, a.attnum) AS column_position,
           CASE o.OPTION
                                 & 1
                      WHEN 1 THEN 'DESC'
                      ELSE 'ASC'
           END      AS order
FROM       pg_index AS i
join       pg_class AS trel
ON         trel.oid = i.indrelid
join       pg_namespace AS tnsp
ON         trel.relnamespace = tnsp.oid
join       pg_class AS irel
ON         irel.oid = i.indexrelid
cross join lateral unnest (i.indkey) WITH ordinality    AS c (colnum, ordinality)
left join  lateral unnest (i.indoption) WITH ordinality AS o (OPTION, ordinality)
ON         c.ordinality = o.ordinality
join       pg_attribute AS a
ON         trel.oid = a.attrelid
AND        a.attnum = c.colnum
WHERE      tnsp.nspname='public' -- can be replaced
AND        trel.relname='test1' -- can be replaced
GROUP BY   tnsp.nspname,
           trel.relname,
           irel.relname,
           a.attname,
           array_position(i.indkey, a.attnum),
           o.OPTION ;


Performance of different precedence pseudocolumn solutions with LIMIT

Your test design is flawed. You are testing incorrect results.

I added an answer to the question on SO you are referring to.

In your CASE version you can't add ORDER BY col1, col2. Would have to be ORDER BY precedence. But it would still be incorrect. You would have to ORDER BY the sum of scores for individual conditions to get rows fulfilling the most conditions first.

Similarly, your UNION ALL version yields incorrect results.

However, none of this seems necessary, there is a simpler and faster solution with UNION ALL. Refer to my answer to the SO question or use the sqlfiddle to play with


Column name or number of supplied values doesn not match table definition

dbo.ADGroups requieres 3 columns/values:

GroupID int NOT NULL PRIMARY KEY,
ADUserID int NOT NULL,
ADGroup varchar (200)

But your query only selects 2 values:

@ID
ADGroup

And SQL Server does not know which one is missing and what to do with the other 2.

You can add a third value for GroupId but it is a lot better to add the column names you are using after the table name when you insert something into a table:

INSERT INTO dbo.ADGroups(ADUserID, ADGroup)
SELECT @ID, ADGroup
FROM UserADGroups
WHERE ADGroup IS NOT NULL

You should always use this syntax with a list of column name: insert into table(colA, colB, ...). It will allow SQL Server to know which value belongs to which column.

Since GroupId cannot be null, it will fail as well. You then have 2 option:

Supply a valid GroupId value
Use Identity GroupID int identity(0, 1) NOT NULL PRIMARY KEY


Postgresql create database from backup programmaticly

Creating a database inside a transaction is not possible, so that precludes doing it directly in a trigger, and more generally in any function called by the SQL engine.

The usual method to issue script-like operations from a trigger is to have such script running as a daemon and listening to events through the SQL LISTEN command, the events being signalled from the trigger with a NOTIFY command, possibly including a payload with parameters. If the transaction is rolled back, its notifications are not sent.

From a design point of view, this is a misuse of triggers. A trigger that "does something" is a side effect by nature and side effects are more a problem than a solution in programming. Creating and populating a new database is a massive side-effect.

Unfortunately some think that triggers being cool, they should use and abuse them no matter what problem they're trying to solve.


How can I permanently remove replication by script if I only have access to the subscriber?

It sounds like what you're asking for is sp_removedbreplication, which you would run like:

EXEC sp_removedbreplication @dbname = 'MySubscriberDb';

From docs.microsoft.com:

  This stored procedure removes all replication objects ...on the
  subscription database on the Subscriber instance of SQL Server.
  Execute in the appropriate database, or if the execution is in the
  context of another database on the same instance, specify the database
  where the replication objects should be removed. This procedure does
  not remove objects from other databases, such as the distribution
  database.

That said, doing just this may not resolve your issue, depending on your configuration.

Option 1) Change your replication topology

I'd also recommend configuring your Distribution Agent to run as a pull subscription. This will enable you to disable the SQL Agent Jobs associated with loading any snapshots or transactions. Because the job is on your subscriber, you will be able to disable it, even when the publisher/distributor are offline. If/when the publisher/distributor comes online, you don't need to worry about transactions/snapshots being loaded to the subscriber, until you explicitly re-enable the Distribution Agent job. Note that if you do this, you would not need to use sp_removedbreplication.

Option 2) Change your replication topology another way

Is your distribution database on the same server as your primary? If you move your distribution database & jobs to a dedicated distributor, then this provides similar benefit to Option 1. Additionally, you would have access to the distribution database in order to add/remove subscription information from the replication metadata.

Option 3) Revoke permissions

You could also evaluate the permissions in use by your Distribution Agents. By revoking permissions for a remote Distribution Agent, you can prevent the distribution agent from logging in to the database to re-establish replication.

Option 4) Is replication really the right choice?

While you're asking about replication, I'd challenge you to ask if replication is the right choice for your need. It sounds like you're using replication for High Availability / Disaster Recovery (HA/DR).  Replication isn't a great choice for HA/DR. You may find that Availability Groups make for a better HA/DR solution. They come with different challenges, but are specifically built to solve HA/DR needs, and ensure only a single writable primary in order to avoid this particular challenge.


Clustered columnstore index performance SQL Server 2014

If you can post the specific data and queries you are using, that is probably the only way we can help answer the question in the context of your specific case.  You can use a script that generates anonymous data in roughly the same scale as your real example.

However, I went ahead and created a similar type of script myself.  For the sake of simplicity, I am using fewer than 225 columns.  But I am using the same number of rows and random data (which is unfavorable for columnstore) and I saw results that are much different than yours.  So my initial thought is that yes, you do have some sort of problem with either your configuration or your test queries.

A few of the key takeaways:

Columnstore has dramatically faster performance than rowstore for simple aggregations across all rows in a column
If loaded carefully, columnstore can perform surprisingly well for singleton seeks.  There is an I/O hit, but with a warm cache performance was very good.  But not as good as rowstore for this use case, of course.
If you need to be able to perform both singleton seeks and large aggregation queries, you might consider using a non-clustered columnstore index on top of a standard b-tree table.
You mention that you have 225 columns, but an average row is just 181 bytes.  This seems a little unusual; is your table mostly BIT columns?  That might be something to look into further.  I did see very good compression ratios on a simple BIT column columnstore (over 99%), but it may be the case that much of that is due to the absence of row overhead and this advantage would disappear with many BIT columns on a single row.
If you want to learn (a lot) more about columnstore, Niko's 66-part (and counting) blog series has been the most valuable reference that I've come across.

And now on to the details:

Create rowstore data set

Nothing too exciting here; we create 40MM rows of pseudo-random data.

SELECT @@VERSION
--Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
--  Jun  9 2015 12:06:16
--  Copyright (c) Microsoft Corporation
--  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
GO

-- Create a rowstore table with 40MM rows of pseudorandom data
;WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
, E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b)
, E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
, E8(N) AS (SELECT 1 FROM E4 a CROSS JOIN E4 b)
SELECT TOP 40000000 ISNULL(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0) AS id
    , ISNULL((ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5) + 1, 0) AS col1
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col2
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col3
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col4
    , ISNULL(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) * RAND(), 0) AS col5
INTO dbo.test_row
FROM E8
GO
ALTER TABLE test_row
ADD CONSTRAINT PK_test_row PRIMARY KEY (id)
GO

Create columnstore data set

Let's create the same data set as a CLUSTERED COLUMNSTORE, using the techniques described to load data for better segment elimination on Niko's blog.

-- Create a columnstore table with the same 40MM rows
-- The data is first ordered by id and then a single thread
-- use to build the columnstore for optimal segment elimination
SELECT *
INTO dbo.test_column
FROM dbo.test_row
GO
CREATE CLUSTERED INDEX cs_test_column
ON dbo.test_column (id)
GO
CREATE CLUSTERED COLUMNSTORE INDEX cs_test_column
ON dbo.test_column WITH (DROP_EXISTING = ON, MAXDOP = 1)
GO

Size comparison

Because we are loading random data, columnstore achieves only a modest reduction in table size.  If the data was not as random, the columnstore compression would dramatically decrease the size of the columnstore index.  This particular test case is actually quite unfavorable for columnstore, but it's still nice to see that we get a little bit of compression.

-- Check the sizes of the two tables
SELECT t.name, ps.row_count, (ps.reserved_page_count*8.0) / (1024.0) AS sizeMb
FROM sys.tables t WITH (NOLOCK)
JOIN sys.dm_db_partition_stats ps WITH (NOLOCK)
    ON ps.object_id = t.object_id
WHERE t.name IN ('test_row','test_column')
--name          row_count   sizeMb
--test_row      40000000    2060.6328125
--test_column   40000000    1352.2734375
GO

Performance comparison

In the following two test cases, I try two very different use cases.

The first is the singleton seek mentioned in your question.  As commenters point out, this is not at all the use case for columnstore.  Because an entire segment has to be read for each column, we see a much greater number of reads and slower performance from a cold cache (0ms rowstore vs. 273ms columnstore).  However, columnstore is down to 2ms with a warm cache; that's actually quite an impressive result given that there is no b-tree to seek into!

In the second test, we compute an aggregate for two columns across all rows.  This is more along the lines of what columnstore is designed for, and we can see that columnstore has fewer reads (due to compression and not needing to access all columns) and dramatically faster performance (primarily due to batch mode execution).  From a cold cache, columnstore executes in 4s vs 15s for rowstore.  With a warm cache, the difference is a full order of magnitude at 282ms vs 2.8s.

SET STATISTICS TIME, IO ON
GO

-- Clear cache; don't do this in production!
-- I ran this statement between each set of trials to get a fresh read
--CHECKPOINT
--DBCC DROPCLEANBUFFERS
GO

-- Trial 1: CPU time = 0 ms,  elapsed time = 0 ms.
    -- logical reads 4, physical reads 4, read-ahead reads 0
-- Trial 2: CPU time = 0 ms,  elapsed time = 0 ms
    -- logical reads 4, physical reads 0, read-ahead reads 0
SELECT *
FROM dbo.test_row
WHERE id = 12345678
GO 2
-- Trial 1: CPU time = 15 ms,  elapsed time = 273 ms..
    -- lob logical reads 9101, lob physical reads 1, lob read-ahead reads 25756
-- Trial 2: CPU time = 0 ms,  elapsed time = 2 ms.
    -- lob logical reads 9101, lob physical reads 0, lob read-ahead reads 0
SELECT *
FROM dbo.test_column
WHERE id = 12345678
GO 2

-- Trial 1: CPU time = 8441 ms,  elapsed time = 14985 ms.
    -- logical reads 264733, physical reads 3, read-ahead reads 263720
-- Trial 2: CPU time = 9733 ms,  elapsed time = 2776 ms.
    -- logical reads 264883, physical reads 0, read-ahead reads 0
SELECT AVG(id), SUM(col3)
FROM dbo.test_row
GO 2
-- Trial 1: CPU time = 1233 ms,  elapsed time = 3992 ms.
    -- lob logical reads 207778, lob physical reads 1, lob read-ahead reads 341196
-- Trial 2: CPU time = 1030 ms,  elapsed time = 282 ms.
    -- lob logical reads 207778, lob physical reads 0, lob read-ahead reads 0
SELECT AVG(id), SUM(col3)
FROM dbo.test_column
GO 2


Would elasticsearch or RavenDB be better for fueling a statistics engine/random forest?

Ravendb embedds quite nicely into a .net application and also allows you to create full text (embedded) lucene.net indexes. Given your constraints on the hosting elasticsearch won't be a viable option since you'll need it to run as a service alongside of your MVC application.

Lucene.net does not support facets out of the box but ravendb comes to the rescue here too:
http://ravendb.net/documentation/faceted-search

Ravendb also allows you to control your lucene.net analyzers quite nicely:
http://ravendb.net/documentation/how-indexes-work

Disclosure: I'm the author of the elasticsearch .net client NEST so if anyone would try to sell you Elasticsearch it be me :)


Can't connect through sqldeveloper because locale not recognized even though my language/regional settings are English/US

Try adding the following to your sqldeveloper.conf:

AddVMOption -Duser.language=en
AddVMOption -Duser.region=US


SQL How to get multiple values into one column (MS SQL)

The FOR XML PATH solution for creating csv from rows tend to be the most common for 2016 and lower.

WITH cte
     AS (SELECT one.Batchnumber,
                one.Weight,
                two.Description
         FROM #table1 AS one
         LEFT OUTER JOIN #table2 AS two
              ON one.ErrorID = two.ErrorID)

SELECT Batchnumber,  SUM(Weight),
            STUFF((SELECT ',' + inr.Description
         FROM cte AS inr
         WHERE outr.Batchnumber = inr.Batchnumber FOR XML PATH(''), TYPE
     ).value('.', 'VARCHAR(MAX)'), 1, 1, SPACE(0))
FROM cte AS outr
GROUP BY Batchnumber;

In 2017 there is the STRING_AGG function which makes this easier.

WITH cte
     AS (SELECT one.Batchnumber,
                one.Weight,
                two.Description
         FROM #table1 AS one
         LEFT OUTER JOIN #table2 AS two
              ON one.ErrorID = two.ErrorID)

SELECT  Batchnumber,SUM(Weight),STRING_AGG(Description,',')
FROM    cte
group by Batchnumber;


Assuming that you are using PL/SQL to dequeue the message (there may be additional JMS limitations-- I don't know enough about the interplay between JMS and AQ), you should be able to use a priority queue to implement LIFO (though it would be a bit hokey).  When you enqueue a message, you can specify a priority

  The priority attribute specifies the
  priority of the message. It can be any
  number, including negative numbers. A
  smaller number indicates higher
  priority.

If you create a sequence that counts back from some really large number, you could assign an ever increasing priority.  Then your prioritized dequeue would get messages in a LIFO order.


When is a multi-attribute index less beneficial?

I'm going to have to make a couple of assumptions.  First that when you said + you meant =.  Next that when you say less benefits you mean less benefits than the other options.

First lets say there are four indexes (based on your image)

R(Age)
R(Sal)
R(Age, Sal)
R(Sal, Age)

And re-writing your queries to match:

SELECT * FROM R WHERE Age = 10;
SELECT * FROM R WHERE Sal = 20;
SELECT * FROM R WHERE Age > Sal;
SELECT * FROM R WHERE Age > Col3;
SELECT * FROM R WHERE Col3 < 100 AND Age = 10;

First of all let's look at the easy ones:

SELECT * FROM R WHERE Age = 10;
SELECT * FROM R WHERE Sal = 20;
SELECT * FROM R WHERE Age > Col3;
SELECT * FROM R WHERE Col3 < 100 AND Age = 10;

The reason these are easy is because they only reference one or the other of the columns being indexed.  That being the case I'm going to use one example to server for all of them.

SELECT * FROM R WHERE Age = 10;

In this case the R(Age) index will be best. It is the shortest possible index on that column.  Next R(Age, Sal). This is more than sufficient because Age is still first in the list, meaning that seeks will still work or even a scan of everything between two points. The only down side here is that it is slightly longer and so a bit slower.  The difference in speed however is pretty negligible so if I had a reason for creating R(Age, Sal) that's what I would create and I wouldn't mess with R(Age). Lastly R(Sal, Age)' might get used because it's smaller than the entire table, but it will still have to be scanned (just like the unindexed table) to get any useful information. I.E. If my indexed pairs for(Sal, Age)are(10,20), (11,5), (12,45), (12, 2)` and I'm looking for an Age of 45 I'm still going to have to scan through all of the pairs to get anywhere.

Now the last one (the one that references both columns)

SELECT * FROM R WHERE Age > Sal;

In this case the indexes R(Age, Sal) or R(Sal, Age) are equivalent.  Both values are indexed and since both are in the expression it doesn't matter which comes first. Given the expression you are going to have to scan the index (there is no way to use the order of either value to help) but it's better than scanning the whole table.  I'm honestly not sure if R(Age) or R(Sal) would be of any user or not.  They contain some of the required information, but you still have to go back to the original table to get the remaining information. It might be faster just to scan the table itself.

Just to add an example of a query that would benefit from a multi-attribute index.  Let's say your query looked like this:

SELECT * FROM R WHERE Age = 10 AND Sal = 40;

At this point the system can benefit from a multi-attribute index.  It can seek to find all rows where Age = 10 and then seek in those rows for cases where Sal = 40.  The best order (R(Age, Sal) vs R(Age, Sal)) is going to depend. For example: how many possible values are there? If there are 100 possible Ages, but only 5 possible values for Sal then R(Age, Sal) would probably work best.  And vise-versa of course.


Yes, you can and should use multiple servers if you have a high load in your mysql.

you need to see what solution works to you:

Replication

Master -> Slave:
In this one, you will write(and also will be able to read) in the master server, and read from slave,
Master -> Master: In this one, you will be able to write and read in both servers (they will be action as master and slave), you need to be careful with AUTO_INCREMENTS

Cluster: You can have a look on mysql cluster, I'll recommend this option if you have more than 4 server (masters or slaves in total).


Taking it as a given that the query as shown in your question generates the three rows shown, turning the existing query into a subquery would give you the grand total for "Big Boy Records":

SELECT venID, venName
      ,COALESCE(SUM(TotalAmountToInvoice),0) as TotalAmountToInvoice
      ,COALESCE(SUM(costAmt),0) as costAmt
      ,COALESCE(SUM(fullon),0) as fullon
  FROM (
        SELECT vendor.venID,vendor.venName,((
        select  coalesce(sum(rl.amountToInvoice),0)
        from    AR r join ARItems rl on r.arID=rl.AR
               join scottCalvin sc on rl.scottCalvin=sc.asID
        where   rl.AR=AR.arID and sc.arRPT=true
           )) AS TotalAmountToInvoice,((
        select  coalesce(sum(ja.actualcost),0)
        from    workDetails ja
        where   ja.workID=AR.workID and ja.
               vendorID=AR.vendorTran
           )) AS costamt,((
        select  coalesce(sum(ja.markup),0)
        from    workDetails ja
        where   ja.workID=AR.workID and ja.
               vendorID=AR.vendorTran
           )) AS fullon
        FROM "public".AR AR LEFT OUTER JOIN "public".vendor
               vendor ON AR.venID=vendor.venID
        WHERE ((NOT ( (AR.vendorTran = 'Pending') ))) AND ((NOT (
               (AR.vendorTran = 'Basis') ))) AND ((AR.payDay
               BETWEEN '01/01/2017' AND '02/01/2017'
               AND vendor.venName = 'Big Boy Records'))
        ORDER BY vendor.venID DESC
       ) grandTotal
 GROUP BY venID, venName

If this grand total is all you want, you can go back to the original query, GROUP BY venID, venName, and add the SUM() function around each of the other three fields.

As PostgreSQL is not my usual language, I have tried to modify the original query as little as possible. It appears that you want one row per vendor, per AR entry. You might be able to eliminate many of the subqueries (turning them into LEFT JOINs instead), by adding the unique ID from the AR table. You could then use `GROUP BY GROUPING SETS ( (venID, venName, arID), () ) to get the individual lines, with the grand total at the end (or possibly the beginning, not sure how it'd sort). You'd have to include the arID in the SELECT list, I think, but once you've got the results you want, you could find a way to filter that out.


If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
  c refcursor;
BEGIN
  c:='mycursorname';
  OPEN c FOR select * from generate_series(1,100000);
  return c;
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;

 Output:

 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10

CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.


Copy complete structure of a table in Oracle without using Backup

select dbms_metadata.get_ddl('TABLE','SCHEMANAME','TABLENAME') DDL from dual;

TABLE is the type of object (you can also extract INDEX, VIEW, FUNCTION, PROCEDURE etc)

or:

create table t1 as
( select * from t2 where 3=4 );

dbms_metadata.get_ddl is the way to go, as it preserves various aspects that a CTAS query doesn't.


I would actually use dbatools (https://dbatools.io) these days

Get-DBALastBackup -SQLinstance Instance

Which looks like this

Old Answer Below

I am Powershell dude (sounds like I am attending a Powershell Anonymous meeting!!)

so this is the script I would use although I would use the objects returned to write to a database for a report or write to Excel or create an HTML email for my manager or whatever was required.
Remember the $Servers should hold SERVERNAME\InstanceName,PORTNumber if any of those are not standard

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$Servers =
## A list 'Servername1','Servername2' a text file Get-Content 'PATHTOSERVERFILE' or query a database Invoke-SQLCmd -Server SERVERNAME -Database ALLMyInstances -Query "Select Name FROM Instances"
foreach($Server in $Servers)
{
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
$lastDBCC_CHECKDB = @{Name="Last DBCC Check";Expression={$_.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where {$_.Field.ToString() -eq "dbi_dbccLastKnownGood"} | Select Value -ExpandProperty Value}}
foreach($db in $srv.databases)
{
$db|Select Parent,Name,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate,$lastDBCC_CHECKDB
 }
}


TSQL retrieve backup date from database after restore

Answer from author's comment converted to community wiki

select  top 1
        s.backup_finish_date
from    msdb.dbo.backupfile f
left outer join
        msdb.dbo.backupset s
    on  s.backup_set_id = f.backup_set_id
where   f.logical_name  = '<name of mdf-file>'
order by
        f.backup_set_id desc

In case the original MSDB is not available, or backup history has been purged, the data is available within backup headers. Use RESTORE HEADERONLY to retrieve the header contents. In the header, there's about 60 pieces of metadata; refer to the Microsoft documents for detailed description.

For example,

restore headeronly from disk = N'<path-to-backup-file.bak>';


Can I configure MySQL's typecasting to consider 0 != 'foo'?

The problem, though, is that you are supplying a numeric value, unquoted, so MySQL tries to cast the other side of the comparison as a DOUBLE, to compare it with the numeric argument you've supplied... which leaves you with a 0 that's equivalent to 0.
mysql> select 'foo' = 0;
+-----------+
| 'foo' = 0 |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

If you supply the 0 in a string context, this evaluates to false.
mysql> select 'foo' = '0';
+-------------+
| 'foo' = '0' |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

So one option you could use to avoid this behavior would be by casting your input value as a CHAR... which, most of the time, it already is... but if not, it evaluates as you would expect.
mysql> select 'foo' = cast(0 as char);
+-------------------------+
| 'foo' = cast(0 as char) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

update, to more fully answer the question:

Can I configure MySQL not to do this kind of typecasting?

No, there does not appear to be any way to avoid this through configuration -- only through an explicit cast of your potentially-numeric-looking string -- to a string, which you've said can't be accomplished since you're using an ORM.  The most likely place, if something like this existed, would be SQL Server Mode, and none of those options modify this behavior when it comes to comparisons.
There's a little bit of discussion of implicit conversions like this in Bug #63112:

SELECT orange > banana;
Is this true or false? Well .. most often a banana is longer than an orange.  So if lenght is the criteria the above statement is false.  But most often an orange is heavier than a banana, so if weight is the criteria the above statement is true.
When comparing different types (orange versus banana, string versus integer) some 'criteria' or 'rule' will have to be used.  When MySQL compares different datatypes they are [both] cast to DOUBLE internally before comparison. The integer "1" casts to the double "1" - the string 'a' or 'UUID()' casts to the double "0".  [...]

Note that the scope of this bug report is not strictly limited to implicit casting in out context, and the mention of UUID() here is a bit of a distraction, since UUID() can return strings beginning with digits and cast to other numbers... but I think it does serve to confirm that no alternative exits, since nothing is presented other than "don't use different types for comparison if you want to avoid a cast," which we already know.  It continues:

To avoid casting to DOUBLE internally ensure that the values compared are same datatype. In your case the id columns are strings and the value you compare with should also be strings (not 1 but '1' - not 2 but '2' etc.) or use cast()/convert() in your statement [...]

Regarding your suggestion that if implicit casting is to be done, then the number should become a string instead of the string becoming a number... I can see your point, to a point, for strict equality comparisons... but there are many other operators besides = and using that logic, a whole new series of problems arises...
mysql> SELECT CAST(100 AS CHAR) < '2';
+-------------------------+
| CAST(100 AS CHAR) < '2' |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

Correct!  The string '100' is "less than" the string '2' in an alphabetical sense... in the same way the string 'AXX' is "less than" the string 'B'.  So this alternative certainly has its down-sides.
There is, however, a third alternative, which seems like the most accurate logic (though I welcome a constructive objection): If a string that begins with a non-digit character is cast to a DOUBLE (or any other numeric type, for that matter), it seems like the most correct result of that cast would be NULL.
Unfortunately, this isn't the MySQL approach.  It seems like this could be fixed in the source, possibly in sql/field.cc where the '0' returned by my_strntod() appears to be simply returned to the caller after throwing the warning if an error occurred, but attempting such a hack this deep in the source is beyond my expertise.


Your code has a number of issues:

You can simplify to a  COUNT(DISTINCT here as you want the number of distinct products
The subquery returns ProductID you would need CustomerID instead. Since you need the grouped count, you are better off with an EXISTS query instead
You are querying Customer again unnecessarily. Products is also not needed as you can get ProductID from OrderItems

SELECT c.CustomerID
     , c.EmailAddress
     , c.FirstName
     , c.LastName
     , '#3: Customers who have ordered more than 1 product' AS queryInfo
  FROM Customers c
 WHERE EXISTS (SELECT 1
             FROM Orders o
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             WHERE c.CustomerID = o.CustomerID
           HAVING COUNT(DISTINCT oi.ProductID) > 1
         )
     ;

You could also remove the HAVING and check the COUNT(DISTINCT from the subquery on the outside:
 WHERE (SELECT COUNT(DISTINCT oi.ProductID)
             FROM Orders o
             JOIN OrderItems oi
                  ON o.OrderID = oi.OrderID
             WHERE c.CustomerID = o.CustomerID
         ) > 1


does character ' and ` equal in mysql command?

' or " for strings

` (backtic) for column/table/database names.

The first should give you user_url; the second should look up the value in the user_url column.


How to handle user with no password in postgresql

In pg_hba.conf, you should set:
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local     all           all                                   trust

Authentication method should be TRUST, that will allow you to enter postgres without a password and set it for postgres user. Below you see how to change password using SQL:
ALTER USER postgres WITH PASSWORD 'pass';

However, you should use psql's \password instead, so the password change (and the password itself) is not logged under any circumstance.
From the PostgreSQL 9.4.12 Documentation for psql:

\password [ username ]
Changes the password of the specified user (by default, the current user). This command prompts for the new password, encrypts it, and sends it to the server as an ALTER ROLE command. This makes sure that the new password does not appear in cleartext in the command history, the server log, or elsewhere.

Of course you should change option TRUST to for example md5 later for security reasons.


Is it worth partitioning a Table by single value in Oracle?

My opinion is that partitioning of that table in the manner presented may be useful.

Your queries will be two times faster (in the case when partitions are almost equal) if they make full scans.

In case your queryes have another tone of filters/conditions and use indexes, partitioning is not useful, because the level of an index is almost not affected by doubling the number of values.

UPDATE: For the test you did (SELECT * FROM table1 WHERE TENANTID=2) it is sure that partitioning is best. The bitmap solution need to scan the index, and after this, to scan all table's blocks containing rows with tenantid=2(it knows what are the rows). But partitioning will cause just scanning the table's partition with tenantid=2. They are phisically separated to tenantid=1.

So, two scans(index scan + table scan) vs one table scan(wich may be smaller).


Insert data into a column where other columns have constraints

You cannot insert a row without specifying values for columns that have NOT NULL constraints. What would be the point of having NOT NULL constraints on those columns if it wasn't enforced?

You would need to remove the NOT NULL constraint clause for each column in the table in order to insert a row that only contains the Zip code value.

This begs the question:  Are you really trying to insert a row that ONLY has a zip code, and nothing else?  Or are you actually trying to modify the zip code for already existing rows in the table?  If that is the case, you need to use an UPDATE <TABLE> statement, not an INSERT INTO statement.


Seek time on a spinning disk is a few milliseconds -- a combination of the arm motion and the rotational delay waiting for the sector(s) to come under the read head.

arm motion (a few ms).
10K RPM (rotations per minute) = 667/sec --> 1.6 ms.  Consider half that for the average delay.
fetch the sectors.  One sector is 512 bytes; 32 sectors needed for an InnoDB 16KB block.  (Timing depends on how many sectors per track.)
Checksum and transfer to RAM
Poke around in the block to find the row(s) desired.
Work with the rows.

10ms is a simple, and often accurate enough, Rule of Thumb for the total time to read 1 block.

The 100MB/1s is probably predicated on optimal conditions of streaming consecutive sectors from each track, and hitting consecutive tracks -- thereby eliminating the first 2 steps in my list.  These are often the most costly two.

SSDs replace the first 2 steps by a much faster random fetch.

Yes, all row actions in MyISAM's InnoDB involve first having the desired 16KB block(s) sitting in the buffer_pool (in RAM).  Blocks will be fetched as needed, not pre-fetched, so don't expect the benefits of "streaming".  Also, most tables are not laid out on disk consecutively, so there will be frequent arm and/or rotational delays.  InnoDB is more optimized for efficiency when handling multiple threads than for speed of a single thread.


Separating data to different tables relating to the same object

One concern with a large column counts is to do with the number of rows which can fit in a page. DBMS performs IO one fixed-size page at a time. If any part of a page is needed, all of it is read into memory where the required bit is processed. So, if there are a small number of short columns many rows can fit in a single page. Conversely tables with many, long columns can only fit a few rows per page and more IO is required to retrieve a given number of rows.

Is this good or bad? Well, it depends. (If you're new to database design you'd better get used to hearing that phrase!) If you only ever look for a single row at a time it would be better to have all the columns in single table so one IO will return all the values to memory. They are likely to stay there for a while, meaning subsequent actions on the row will not have to do physical IO. This is great for a typical transaction processing workload where an item goes through a number of steps until it reaches completion.

If your workflow is more BI oriented, where you need sums and averages and whatnot, then a query will retrieve many rows and will benefit from having as many rows per page as possible. Ideally, those rows should only have the columns required to answer the query. Other columns would be better held in a secondary table.

Sadly, few systems are this cut-and-dried. Often there is a mix of OLTP and BI work. No one query is so much more important than the others that it drives the DB design. The additional IO cost of referencing a secondary table can be troublesome. This is where the art takes over from the science of being a DBA. You have to understand the intended usage and juggle options accordingly.

For my two cents, without any understanding of your actual workload whatsoever, I'd suggest you keep everything in a single table for now. Anything else is premature optimisation.  When you run into an actual problem you can't solve another way, that's the time to split tables. The additional complication of keeping the secondary tables synchronised with the primary can be surprisingly tricky.

Before then covering indexes will help a lot. Materialised views are useful, too. Neither are free lunches, of course, so understand the costs and benefits before deploying either.

Should you decide to split over several tables I'd suggest keeping together those columns which are used together in a single business action. Mixing read-only and updatable columns in a table is not a problem. I can think of no advantage of separating them on those lines only.


Oracle SQL count occurances of character in VARCHAR

What version of Oracle?

If you're using Oracle 11.1 or later, you can use REGEXP_COUNT

  1* select regexp_count( '11001110', '1' ) from dual
SQL> /

REGEXP_COUNT('11001110','1')
----------------------------
                           5

In earlier versions, there are various less elegant options such as

SQL> select length( '11001110' ) - length( replace( '11001110', '1' ) ) cnt
  2    from dual;

       CNT
----------
         5


Extended Events to find Blocking and Deadlocking

Glad you liked the post - it was by Jeremiah Peschka though, not me. (I know because I use it all the time myself!)

Make sure to read the entire article, though, which includes the answer you seek:

  Extended Events deadlock graphs use a slightly different XML schema
  than what SSMS expects. You should see an error along the lines of
  “There is an error in XML document”. For folks using SQL Server 2012
  and earlier, you can either parse the XML by hand or use SQL Sentry
  Plan Explorer.


As i said in the comment simply Replace the SUM with what ever you need in your

Create Table `data`
(
   callDate text
  ,callerName text
  ,callLength text
);

Insert Into `data` (`callDate`, `callerName`, `callLength`) Values
('07/01/2020', 'Client A', '0:00:23'),
('07/01/2020', 'Client B', '0:15:23'),
('07/01/2020', 'Client C', '0:7:10'),
('07/02/2020', 'Client A', '0:01:23'),
('07/02/2020', 'Client B', '0:12:23'),
('07/02/2020', 'Client C', '0:3:10'),
('07/03/2020', 'Client A', '0:011:23'),
('07/03/2020', 'Client B', '0:22:23'),
('07/03/2020', 'Client C', '0:23:10');

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL)) AS "',
          callDate,'"'
        )
  ) INTO @sql
FROM
   `data`;
SET @sql = CONCAT('SELECT callerName,', @sql, ' FROM  `data` GROUP BY callerName');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

callerName | 07/01/2020 | 07/02/2020 | 07/03/2020
:--------- | :--------- | :--------- | :---------
Client A   | 0:00:23    | 0:01:23    | 0:011:23
Client B   | 0:15:23    | 0:12:23    | 0:22:23
Client C   | 0:7:10     | 0:3:10     | 0:23:10

db<>fiddle here
It is quite ugly and it has the same limitation as the explained here

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL)) AS "',
          callDate,'"'
        )
  ) INTO @sql
  FROM
   `data`;
  SET @sql2 = NULL;
  SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
          'SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "',
          callDate,
          '" ,`callLength`, NULL), "%H:%i:%s")))) AS "',
          callDate,'"'
        )
  ) INTO @sql2
FROM
   `data`;
SELECT @sql2;
SET @sql = CONCAT('SELECT callerName,', @sql, ' FROM  `data` GROUP BY callerName UNION SELECT "total",',@sql2,' FROM  `data`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

| @sql2                                                                                |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/01/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/01/2020",SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/02/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/02/2020",SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(IF(callDate = "07/03/2020" ,`callLength`, NULL), "%H:%i:%s")))) AS "07/03/2020" |

callerName | 07/01/2020 | 07/02/2020 | 07/03/2020
:--------- | :--------- | :--------- | :---------
Client A   | 0:00:23    | 0:01:23    | 0:011:23
Client B   | 0:15:23    | 0:12:23    | 0:22:23
Client C   | 0:7:10     | 0:3:10     | 0:23:10
total      | 00:22:56   | 00:16:56   | 00:56:56

db<>fiddle here


Where and with what should I be looking for a good hardware backup tool?

My opinion:
A NAS with RAID 5 would be a good solution. This would give you decent fault tolerance without costing too much space. You can even get NASes that support AD authentication, thereby taking your security up one level. This will take care of your onsite backup needs.

Tape backups will provide you with an offsite storage solution, but the price difference is much more for the tape drive and management software.

Don't go for the cheapest NAS hardware. That was a decision taken by the management at my previous company. Due to inconsistent behavior/performance they dropped the solutions after paying full price for implementing NASes in 11 divisions. Check out reviews about the NAS before buying them.


Does SQL Server re-create system databases if they are lost/missing?

Answering the initial question

No.

Providing a solution

The procedure of restoring the system databases is explained in the following Microsoft Article:

Reference: Rebuild System Databases (SQL Server 2012)

Prerequisites

Ensure you have a clear documentation of the old system:

SQL Server Instance Configuration
Service Packs and Hotfixes
Current location of database files

Restore steps

Rebuild the system databases:

Insert SQL Server 2012 installation media
Perform a rebuild using the following command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Replace the relevant information with the values for your instance.

Verify that the installation was successful by consulting the setup summary file:

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Logs

Restore the most recent backups of the system databases: master, msdb, (model):

When restoring the master database start the SQL Server instance from a DOS prompt with the -m parameter.
Reference: Restore the master Database (Transact-SQL) for more details
When restoring the msdb database start the SQL Server instance with the Trace Flag 3608:

  Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Moving System Databases and Moving User Databases. Do not use during normal operation.

Reference: Trace Flags (Transact-SQL)

Rebuild the ressource database:

Launch SQL Server 2012 setup program from installation media
Click Maintenance and then Repair
Select your Instance
On the Ready to Repair summary page click Repair

Restore your User databases

Please read

  Important Notice
  If you never perform a restore, you will never know if you backup (procedure) works. Test and test again.


How to suppress activation procedure output from the error log?

Seems like you have an activation procedure that explicitly outputs the string y (probably using print but possibly raiserror, select, or output).

Why don't you change the procedure so that it doesn't have any output? Output shouldn't be needed when run through service broker. Or if the procedure is also sometimes run interactively, and output is required in that case, you could require a param for output (or create a wrapper procedure and call that instead).

There is no trace flag to suppress activation procedure output from the error log. This was a design decision to assist in troubleshooting activation procedures (since they are not typically run interactively).


Automating timestamp fields in PostgreSQL

Different columns,

created_at this simple, just give it a DEFAULT now()
updated_at use the spi module which provides a higher-order trigger than can be applied to any table, see this post, also see row-modification-time
deleted_at how would you automated that anyway? What would that even look like? Just set it when you delete the post.


Brent here - the guy in the hairy chest wig in the video.

Much like that hairy chest wig, I don't use those tools much anymore. The video is still up just for comic relief sake.

Over time, I wanted better tools, so with the help of my coworkers, I built 'em and open sourced 'em. Here's the Github repo for the First Responder Kit, which contains:

sp_Blitz - health check, replaces the BPA for my needs
sp_BlitzFirst - performance check, replaces Diag/Nexus for most of my needs, especially with the @SinceStartup = 1 parameter
sp_BlitzCache - performance check that focuses on queries
sp_BlitzIndex - index design check that replaces the DTA for me


How to add a volume to current in use MySQL database

I followed this guide, very useful and complete
https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04


I was able to replicate a similar condition by setting the system clock on a server (not Windows) to a time before the time when the MySQL Server process was started.  The numbers in my SHOW ENGINE INNODB STATUS are small since this is a test machine, but they're suddenly negative after changing the clock.

-0.00 inserts/s, -0.00 updates/s, -0.00 deletes/s, -0.01 reads/s

You might try this:

mysql> SHOW STATUS LIKE 'uptime';

This is a value in seconds that appears to be calculated as the difference between the current system time and what the system time was when the server was started... and may be the same number used to calculate these stats.

If you see a massive number (an artifact of unsigned subtraction) that seems completely inconsistent with the server's true uptime then that would be confirmation that this is the cause.

If this is what you find, then I suspect the only remedy is to restart MySQL Server with the system clock set accurately.


Figured out what the issue was.  For some reason the primary database had been joined to the availability group but the secondary was not, do not know if another DBA forgot to join it when they restored or if there was an error.  Either way, this wasn't allowing the logs to harden so CDC wasn't picking up the changes.

Did a fresh backup of the primary, restored to the secondary and joined it up and CDC started working again.

Thanks,
Tim


Rolando told you how to do it and his answer is excellent.  The truth is, circular replication is an awful, terrible, terrible idea.  Don't do it. Ever. "Absolutes are never true," but in this case, it's pretty close.

Instead of having a single point of failure, now you have three.

What if one of the servers goes down?  Now replication is broken everywhere.

How do you replace it easily and align the binary log positions?  You can't without doing some very complicated and error prone binary log analysis.

So, now you have to restore all three servers from a backup.  Wait, was that backup taken at the same moment in time?  Nope, because there's no way to make a transactional backup across multiple servers.  So, you're left with broken replication and attempting to fix it with pt-table-checksum and pt-table-sync...or rebuilding the entire cluster from scratch.

Just because you can do something, doesn't mean you should.  Don't do this, really.  I'm not kidding.


Upgrading to SQL Server 2017 from SQL Server 2012 time allowed

Backup restore depends on a lot of different factors, the biggest ones being

Your network
Your storage

The best way to ever give a good estimation is to test it.

Setup the second server/dev env, and do the migration as a test, this will give you a very good estimation of the time it will take.
And as a second huge benefit, you'll have all scripts ready + experience for the production migration.


Converting Database from GUID to Integer Keys

Your plan sounds basically OK to me. I would proceed cautiously though - add a new int column to the tables, populate it with unique values (the ROW_NUMBER() function is handy for this kind of thing) and then change it to be the primary key and check all is ok before considering dropping the GUID cols.

You perhaps should create a set of archive tables containing the GUID and PK for each of the tables that you can refer back to if you needed to.


PostgreSQL GIN index not used when ts_query language is fetched from a column

I suggest a solution with partial expression indexes:

CREATE TABLE search (
   search_id serial PRIMARY KEY
 , language  regconfig NOT NULL  -- order of columns matters a bit
 , content   text NOT NULL
   --  *no* redundant fulltext tsvector
);

No redundant fulltext column - makes the table smaller, which helps overall performance.
Create one partial expression index for every relevant language:

CREATE INDEX search_fulltext_dutch ON search USING GIN(to_tsvector('dutch', content))
WHERE language = 'dutch'::regconfig;
CREATE INDEX search_fulltext_english ON search USING GIN(to_tsvector('english', content))
WHERE language = 'english'::regconfig;
-- more?

All of the partial indexes together are only about as big as your total index.

Then match index conditions in the query:

SELECT * FROM search  -- does not return useless column fulltext now
WHERE  language = 'dutch'::regconfig  -- match partial index condition
AND    to_tsvector('dutch', content) @@ to_tsquery('dutch', 'vliegen')

UNION ALL
SELECT * FROM search
WHERE  language = 'english'::regconfig
AND    to_tsvector('english', content) @@ to_tsquery('english', 'vliegen');

-- more?

You get bitmap index or index scans this way.
Another index on language may be useful for other purposes, this query does not need it.


MySQL - Alter table to automatically put in a UUID

Just create a trigger to run before insert to generate the UUID for the given column.

CREATE TRIGGER before_insert_mytable
  BEFORE INSERT ON mytable
  FOR EACH ROW
  SET new.LogID = uuid();


Managing SQL Server logins and database users

The easiest and most manageable way to achieve this is using Active Directory groups.

Have your ad administrator create a security group called SQL_Writers (or something similar) for those users and add them (and any new users to that group).

Then in SQL Server, simply grant that security group the appropriate permissions to the server / databases.

If you want the permissions to be inherited to any newly created database on your instance, remember to grant the security role to the model database as well.


Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

  This dynamic management function takes a Transact-SQL statement as a
  parameter and describes the metadata of the first result set for the
  statement.

--Demo set up
drop table if exists dbo.customer
drop table if exists dbo.[transaction]

create table dbo.Customer(CustomerId int, CustomerName varchar(25))
create table dbo.[Transaction](TransactionId int, PurchaseAmount decimal(10,2))
DROP VIEW IF EXISTS CustomerTransaction
GO
create view dbo.CustomerTransaction
as
select
   cust.CustomerId,    -- this is int
   cust.CustomerName,  -- varchar(25)
   tr.PurchaseAmount   -- decimal(10,2)
from dbo.Customer cust
inner join dbo.[Transaction] tr
   on cust.CustomerId = tr.TransactionId
go
-------------------------------
--The solution
SET NOCOUNT ON;

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

SELECT @cols += N',' + NAME + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerTransaction', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
SET @sql = replace(@sql,',',',')
print @sql

CREATE TABLE #tmp(CustomerId int,CustomerName varchar(25),PurchaseAmount decimal(10,2));


Verify if user has read access to a table

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME*.

For example, to show if the current security context has SELECT permission on the Production.Product table in the AdventureWorks sample database:

SELECT
    HAS_PERMS_BY_NAME
    (
        N'Production.Product',
        N'OBJECT',
        N'SELECT'
    );

Related: To list effective permissions on a securable, use sys.fn_my_permissions:

SELECT
    P.[entity_name],
    P.subentity_name,
    P.[permission_name]
FROM sys.fn_my_permissions
    (
        N'Production.Product',
        N'OBJECT'
    ) AS P
ORDER BY
    P.subentity_name,
    P.[permission_name];

* The built-in function takes everything into account. SQL Server has to know how to correctly determine effective permissions. This function exposes that ability.


What is the default order of records for a SELECT statement in MySQL?

Reposting my answer to a similar question regarding SQL Server:

  In the SQL world, order is not an inherent property of a set of data.
  Thus, you get no guarantees from your RDBMS that your data will come
  back in a certain order -- or even in a consistent order -- unless you
  query your data with an ORDER BY clause.

So, to answer your question:

MySQL sorts the records however it wants without any guarantee of consistency.
If you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

This is a property of all SQL, not just MySQL. The relevant text in the SQL-92 spec is:

  If an <order by clause> is not specified, then the ordering of the rows of Q is implementation-dependent.

There are similar bits of text in the spec for cursors.


SQL Server is started but didn't listen on any port

What made my SQL server listen for TCP connections on port 1433 was the following...

Configuration Tools / SQL Server Configuration Manager
click the instance name in SQL Server Network Configuration
enable TCP communication
Right-click the TCP communication entry, choose Properties
Click the IP addresses tab
Enable all non-VMnet IP addresses and enter 1433 in the Port entry for "IPAll".
Restart the SQL Server on SQL Server Services tab
Check your configuration with netstat -an:

C:\>netstat -an | find "1433"
  TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING
  TCP    [::]:1433              [::]:0                 LISTENING


Extension created for public schema not available within database

Short answer

You are actually using (at least) two databases. You are installing CITEXT in the wrong one.

Longer answer

In the first database (whose name is not shown), you install CITEXT. This database is probably the postgres database, which is created by default when you install PostgreSQL.

Then you use the \c command and you switch to another database (sensordata). You need to create your extension in this database.

That is, you should do:

\c sensordata
CREATE EXTENSION citext;
\dx

If you need to use the citext module in more databases, you need to install it in each one. The extensions on the other databases don't matter. Databases do not interfere with each other.

Every PostgreSQL cluster (=database installation) has (by default) one database named postgres. If you use psql, it is the one to which you connect "by default". You don't actually need to have a postgres database. I guess the postgresdatabase is where you installed the CITEXT extension.

Within every PostgreSQL database, there is, by default, one public schema.

PostgreSQL does not work like (for instance) MySQL, where you can acces several databases at once through a single connection (provided you have a user with the right permissions).

In PostgreSQL you access one database through a single connection. Within a database, you can access as many schemas as needed. The role that schemas play in PostgreSQL is nearly the same as the one played by a database in MySQL.

See also:

Relationship between catalog, schema, user, and database instance
What's the difference between a catalog and a schema in a relational database?


How can I handle delayed records a month or older on a table clustered on time?

For question 2.

I think I will break the large table up into many different tables and then use a view to join them together. This will allow me to have something like table partitioning without the feature of table partitioning as outlined in this article.

https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/

View with schemabinding that unions all the tables together. Then I can insert data and select data from this view as if it was the primary fact table. I would only need to ensure all my SELECT queries on this view include the column I chose to partition the tables with to get the full benefit.


Do you have different browser processes running? I haven't tested this but try to close all the open browser windows. pgAdmin could cause some troubles regarding to system default browser https://stackoverflow.com/a/51780450/6618610

Did you try to restart the pgadmin service ?
https://stackoverflow.com/a/45519198/6618610

Restarting postgres would be even better.

I had an issue with pgAdmin4 so I restarted everything. That didn't go through seamlessly neither:

~ % pg_ctl -D /usr/local/var/postgres status
pg_ctl: server is running (PID: 1238)
/usr/local/Cellar/postgresql/12.1/bin/postgres "-D" "/usr/local/var/postgres"
~ % pg_ctl -D /usr/local/var/postgres restart
waiting for server to shut down............................................................... failed
pg_ctl: server does not shut down
~ % pg_ctl -D /usr/local/var/postgres stop
waiting for server to shut down............................................................... failed
pg_ctl: server does not shut down

I had a brew install so I used:

~ % brew services stop postgresql

After that I used pg_ctl to have the control over auto-start:

~ % pg_ctl -D /usr/local/var/postgres start

See the comments section https://stackoverflow.com/a/24251522/6618610

Or another approach, certainly better than killing processes:
https://stackoverflow.com/a/56611776/6618610

Your postgres service should be back running. Now retry again to start pgAdmin from the launchpad. Don't forget to check on Slonik, because it might appear up there and you won't even know : )

Copy the server URL from there or get it in the pgAdmin address file located in home folder. Type

~ % cat .pgAdmin4.###.addr

  ### a random numeric value use TAB for autocomplete

Now start your favourite browser and paste the link in the bar.

Also read those references:
pgAdmin 4 version 3 fails to open a second time
https://stackoverflow.com/questions/45671327/correct-way-to-start-stop-postgres-database-pg-ctl-or-service-postgres
https://stackoverflow.com/questions/7975556/how-to-start-postgresql-server-on-mac-os-x#23628638


Is it a cause of degradation of performance? Nobody can really tell until you have measured it. From your description, it is certainly possible, but before I'd get going on the tasks I'd make sure if this is really the case. I'd also weigh in that reimplementing an existing setup doesn't come for free.

As for the steps to do: I'd put in place test cases that make sure that the external applications get the same data when you change something.

Lastly, I'd also get the commitment of management before you start doing that. It wouldn't be the first time that someone tries to put the fault on you if something with the database doesn't, even if you're not involved (and even if it is only marginally related to the database) just for the fact that someone heard that "this guy" is "chaning the system".


Deadlock Priority High Chosen as deadlock victim

The process with high deadlock priority was chosen as a victim...

I've run into this before with maintenance operations - specifically index reorgs, as described on my blog here: Deadlock Victim Choice in SQL Server - An Exception?

In that case, I was able to reproduce the problem, having a process with the highest deadlock priority (10) chosen as the victim when deadlocked with a process with the lowest deadlock priority (-10).

  Is there a way to prevent a process with high deadlock priority from becoming a victim?

The only solution I'm aware of is to avoid doing these operations (partition switching, altering partition functions, index maintenance) during times of heavy user activity.  Preferably during some kind of maintenance window.

It seems like there's something internal to the way deadlock victims are chosen that "knows" one of the operations is a maintenance task, and is hard-coded in some way to choose that as the victim over a non-maintenance user query.  That's all just speculation though based on my observations.

  ...probably because of high log usage compared to the other process

For what it's worth, this is actually the opposite of how deadlock victims are normally chosen.  The process with the higher log usage is not chosen as the victim, because it would require more work to roll it back.

There's an interesting example similar to yours on Stack Overflow as well, which implies that acquisition of a SCH-M lock is part of the problem (which would make sense, given the types of operations we're discussing): Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?


SQL server indexing foreign keys, covering indexes included columns

If a FK does not have a dedicated index on them but are part of wider indexes used for covering queries, Should they have a dedicated index created?

It depends on the table's access patterns. If the column is being searched a lot (and, ideally, is highly selective), then yes, you absolutely should have an index on that column, with the column as the first key column in the definition.

  Should I be removing some of these indexes and combining them with included columns instead? then have dedicated indexes for my foreign keys?

What was given in the question is somewhat unclear, and the question you've asked is a bit... confused, so let's take a step back for a second.

In SQL Server 2005+, the three most important parts of an index definition are:

The key columns, which determines the index sort order. This means the order of the key columns is very important, because SQL Server uses an index by searching for a value in the first key column, then in the second key column, etc.
The included columns, which are copies of row data tagged onto the index structure. The order included columns are specified is irrelevant.
Is the index unique? This means that the index key can contain only unique combinations of column values.

(While this is not relevant to the discussion at hand, for completeness I will mention it here: SQL Server 2008+ introduces the concept of filtered indexes, which only includes rows in the index that satisfy a predicate.)

The first thing you should do is index consolidation. This involves using the points above to combine indexes that share commonalities.

For example, consider the following two indexes:

CREATE INDEX IX_1 ON [dbo].[t1](C1) INCLUDE(C3, C4);
CREATE INDEX IX_2 ON [dbo].[t1](C1, C2) INCLUDE(C5);

These indexes share the leading key column, C1. Included columns can be specified in any order, so these two indexes could be combined as follows:

CREATE INDEX IX_3 ON [dbo].[t1](C1, C2) INCLUDE(C3, C4, C5);

Where index keys differ in their composition or other properties, you have to be very careful. Consider these indexes:

CREATE INDEX IX_4 ON [dbo].[t1](C1, C3) INCLUDE(C4);
CREATE UNIQUE INDEX IX_5 ON [dbo].[t1](C1, C4) INCLUDE(C5);

Now the decision is not as easy. You have to determine what to do based on your workload, which queries hit the table, and the selectivity of the data itself.

So to answer the question more directly: if you currently have one or more indexes where the column of interest is the first key column in those indexes, you don't have to add more indexes, because the indexes you have are useful.

If the column is searched frequently and there isn't an index with that column as the first key column, you should create an index with that column as the first key column. (Depending on query requirements, you may want to specify other columns as well, for either the key or the included columns.)

If the column is not searched frequently, you can potentially get away with having it contained in another index (not the first key column): the query may be satisfied by scanning the index that contains the column. This is not as efficient as an index seek (for many reasons), but if this operation doesn't happen too often, and the performance in this case is acceptable, you may be okay.

Remember that creating indexes isn't free -- they take up data space, log space, cache memory, and can potentially slow down INSERT/UPDATE/DELETE activity (having said that, there can be other advantages to creating indexes). It's a balance you have to strike for your environment.


SQL Server Maintenance Plan TSQL query to log the backup databases

I would recommend using these free backup (and other) scripts from http://ola.hallengren.com/

These have full logging already and have lots of other useful options.

Personally, these are what I use. Thoroughly tested and used by many, many folk.


While it's running try running the CHECKPOINT command separately. It could be that your system is not getting to perform checkpoints and may need some extra prodding to do so.


Read Replicas with multiple instances of MySQL running on different ports

Yes, Amazon uses standard replication for its instances. Not only it is documented here, but also is proven with the fact that you can replicate from and to MySQL instances outside of RDS. No need for any extra packages/software, a standard MySQL server can be a master and/or a slave with the appropriate configuration and preparation.
Yes, if you run several MySQL instances on the same node (assuming you have changed its port, data directory, pid and socket), you can configure them with different buffer sizes, max_connections, etc. However, make sure to understand that there is no way to hard limit the maximum memory or CPU usage on MySQL configuration - if you want real isolation, you have to go to virtualization or other operating system limits (this is what Amazon actually does).

I recommend you to have a look at MySQL Sandbox, which makes easier to setup MySQL and replication among them on the same machine.


Version of Witness Server for mirroring SQL 2012

No witness server has to be SQL 2012, you can use SQL Express 2012 though

All server participating in database mirroring should be running same version of SQL server.

Taken from Microsoft SQL Server BOL

  Except during an upgrade from an earlier version of SQL Server, the
  server instances in a mirroring session must all be running the same
  version of SQL Server. For example, a SQL Server 2005 witness is
  supported when you are upgrading from a SQL Server 2005 mirroring
  configuration but cannot be added to an existing or new SQL Server
  2008 or later mirroring configuration.

Database Mirroring will be deprecated in future release of SQL Server if possible use AlwasyOn Availability Group


Improve BLOB Writing Performance in Oracle 11g

In Oracle, LOB (including BLOB) is stored as:

in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is enabled, unless you specify DISABLE  STORAGE IN ROW
normal LOB - stored in a separate segment, outside of table, you may even put it in another tablespace; for these:

a minimum of CHUNK bytes are allocated and entirely redo-logged (even if LOB has only 1 byte)
there is an internal intermediate index behind a LOB column, which gets contentious on updates and may practically serialize them
access is multi-level and thus relatively slower
NOCACHE is the default
with CACHE option, the CACHE_SIZE_THRESHOLD is not taken into account, so a large LOB can waste your cache

The Metalink note ID 66431.1 describes this and may be of interest to you, if you have access there.


Oracle: Not able to create tablespace from stored procedure

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
  More

First of all dont create object under SYSTEM schema. Second in order to execute commands in PL/SQL you should have privileges granted directly to the user not from role. And SYSTEM user inherits the CREATE TABLESPACE system privilege via DBA role, so that you are able to create tablespace directly not from PL/SQL procedure.

SQL> conn sys as sysdba

SQL> grant create tablespace to system;

Grant succeeded.

SQL> conn system/password
Connected.

SQL> exec testprc

PL/SQL procedure successfully completed.

SQL>


Upgraded Postgres; how to access now-missing databases?

Before every major upgrade you need to do a backup/restore. The databases are there but 9.4 does not recognize them. The easiest way would be to remove 9.4, reinstall 9.3, do a full backup, then install 9.4 again and do a restore.


DB replication and recovery of a previous version in mysql

First, why do u use MySQL for such a huge dtb?
Second, how about MYLVMBACKUP ?? :)

HW conf plays a big role (in regards to performance and d2d operations) -> you should never keep >1TB data size within a single MySQL node/server (it's an overkill)


Do you want to move data as a one off exercise or do you want to add the data to source control?

SSMS Tools Pack includes a script generator for resultsets, tables or databases.
SSMS has table level data script generation built-in.
If the datasets are large, consider using BCP.
SSIS Import/Export wizard


SQL Server - High Performance Record Comparison

Have you looked at using the Merge command, this was introduced in SQL Server 2008 and does an update or insert based on your matching criteria.

I use this for merging XML quite a lot and it seems very efficient.

MERGE [Database] AS TargetTABLE
USING (SELECT @Var1 AS [Value1]
    @Var2 AS [Value2],
    @Var3 AS [Value3]) AS SourceTABLE
ON TargetTABLE.[id] = SourceTABLE.[Value1]
WHEN MATCHED THEN
    UPDATE SET TargetTABLE.[Value2] = SourceTABLE.[Value2],
        TargetTABLE.[Value3] = SourceTABLE.[Value3]
WHEN NOT MATCHED THEN
    INSERT (
        [id],
        [Value1],
        [Value2],
        [Value3])
    VALUES (
        SourceTABLE.[Value1],
        SourceTABLE.[Value2],
        SourceTABLE.[Value3]
    );

Merge Command


How does MySQL rotate binary log files when number extension reaches its maximum?

An error is generated when the numbers run out:

source code reference


Unexpected Transaction Log Flush events and LOG_BACKUP errors

Did you have a look at log_reuse_wait_desc in sys.databases?

We had a similar issue and I have asked a similar question.

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA


Adding Calculated Columns together to create a Total

You don't specify any specific RDBMS.

A method that should work in all/most would be to use a derived table.

SELECT Val1,
       Val2,
       Val1 + Val2 AS Total
FROM
(
Select Case
     ....
End as [Val1],
Select Case
     ....
End as [Val2]
From Table
) d /*<-- arbitrary derived table alias*/

Probably you are on SQL Server (from the square brackets) in which case you could also use

SELECT Val1,
       Val2,
       Val1 + Val2 AS Total
FROM   TABLE
       CROSS APPLY (VALUES(Case ....End,Case ....End)) V( Val1, Val2)

depending on the exact expression behind Val1 and Val2 (e.g. you can't use this and get correct results if they reference window functions).


How can I import Create Table Statement from SQL Developer into Visio to create an Entity Relationship Diagram?

You can't do that in Visio. However, you can reverse engineer a database using Visio. The answer to the question below should help you.

How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?


1- Create DB Role [db_support]

2- GRANT ALTER ON SCHEMA::support TO [db_support] DENY ALTER ON SCHEMA::dbo TO [db_support]

3- GRANT CREATE TABLE TO [db_support]

4- CREATE TABLE sup.asd (asd int)

  Command(s) completed successfully


Oracle SQL, how to filter on a set by a rank?

There are actually several ranking functions you can use. You can read at the Analytic functions at Oracle documentation. In your case, RANK() and DENSE_RANK() would work, if I have understood you:

select *
from
  ( select uc.*,
           DENSE_RANK() OVER (PARTITION BY ac.HEALTHSYSTEMID
                              ORDER BY ac.ACCESSLEVELTYPE ASC)
             AS drnk
    from usercredential uc
      inner join users u
              on u.userid = uc.userid
      inner join credential c
              on c.credentialid = uc.credentialid
      inner join healthsystemcredential hsc
              on hsc.credentialid = c.credentialid
      inner join accesslevel ac
              on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID
    where c.fulfillmentmethod = 'Attach'
    and c.display = 1
    and uc.auditstatus <> 0
    and ( (uc.auditstatus is null)
       or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate)
        )
  ) tmp
where drnk = 1 ;

The condition on drnk = 1 (lowest Type Rank) will be applied of course after the other conditions in the internal where are applied. If you want to first filter on the lowest Type Rank (this may give you less rows in the final result), you can either group by in a derived table and then join:

select uc.*
from usercredential uc
  inner join users u
          on u.userid = uc.userid
  inner join credential c
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc
          on hsc.credentialid = c.credentialid
  inner join ( SELECT HEALTHSYSTEMID,
                      MIN(ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               FROM accesslevel
               GROUP BY HEALTHSYSTEMID
             ) ac
          on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null)
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate)
    ) ;

or use a subquery in the ON clause. This is closer to your try but quite complicated. SQL-Server has CROSS APPLY which makes this kind of joins more easy to write:

select uc.*
from usercredential uc
  inner join users u
          on u.userid = uc.userid
  inner join credential c
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc
          on hsc.credentialid = c.credentialid
  inner join accesslevel ac
          on ac.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
         and ac.ACCESSLEVELTYPE =
             ( SELECT MIN(aci.ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               FROM accesslevel aci
               WHERE aci.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
             )
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null)
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate)
    ) ;

Tried at SQL-Fiddle. You can test with data in the tables, to see which one fits your requirements and whether the joins should be on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID or with some other column.


Database design that handles data growth with time

Your first tactic should be good indexing. Somewhere down the road, you'll want to consider partitioning.

You always want to think about sargable expressions.


How do I differentiate between user-defined public synonyms and built-in public synonyms?

Well, you could go by object creation date:

SELECT AO.*
  FROM ALL_OBJECTS AO
  INNER JOIN ALL_SYNONYMS ASY ON AO.OWNER = ASY.OWNER and AO.OBJECT_NAME = ASY.SYNONYM_NAME
  AND ASY.OWNER = 'PUBLIC'
  ORDER by AO.CREATED DESC

The system created synonyms should all have the same created date, equal to the creation time of the database and the earliest of this list.

You can also filter out the system owned objects being referenced by the synonym using this :

SELECT AO.*
  FROM ALL_OBJECTS AO
  INNER JOIN ALL_SYNONYMS ASY ON AO.OWNER = ASY.OWNER and AO.OBJECT_NAME = ASY.SYNONYM_NAME
  AND ASY.OWNER = 'PUBLIC'
  AND ASY.TABLE_OWNER NOT IN ('CTXSYS','EXFSYS','APPQOSSYS','XDB','SYS', 'DVSYS', 'FLOWS_FILES', 'LBACSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'ORDDATA','WMSYS','SYSTEM', 'DBMS_PRIVILEGE_CAPTURE')
  and ASY.TABLE_OWNER not like 'APEX_%'
  ORDER by AO.CREATED DESC


Will altering a column name in a MySQL master be replicated to the slave

I was able to test this on our replicated environment.

Surprisingly, the column name was updated in the slave environment. I assumed this would not be the case because the row-based replication was configured.


Ranking each row in Postgres from 1-100 when comparing in groups

I think I may have found the answer. The following query seems to produce results that are as I expect:

select
*,
(100*cume_dist() over (partition by year, month, size order by price))::int as percentile
from mytable;


If you don't know in advance the name of the columns you want to put in your 'Other' column, you have to use something else in addition to the PIVOT statement.

You can try something like :

select  t.PromotionInd  as [Promo Id],
        'BRK'           as [Charge ID],
        t.[BRK]         as [Amount],
        'CLR'           as [Charge ID],
        t.[CLR]         as [Amount],
        'SDC'           as [Charge ID],
        t.[SDC]         as [Amount],
        'BIN'           as [Charge ID],
        t.[BIN]         as [Amount],
        'Other'         as [Charge ID],
        o.Amount        as [Amount]
from (
    select PromotionInd, sum(chrgAmt) as Amount
    from DtlChrg
    where ChrgCode not in ('CLR', 'SDC', 'BRK', 'BIN')
    group by PromotionInd
    ) o
join (
   select p.PromotionInd, p.BIN, p.BRK, p.CLR, p.SDC
   from (
       select PromotionInd, ChrgCode, chrgAmt
       from DtlChrg
        ) s
   pivot
       (
       sum(chrgAmt)
       for ChrgCode in ([CLR], [SDC], [BRK], [BIN])
       ) p
   ) t on o.PromotionInd = t.PromotionInd

(but not very nice to have several columns with the same name or with spaces inside...)

(and you can't have a "double header" as in your example, this is something you should build in your reporting tool, not directly in SQL I think)


INSERT violating unique key constraint on primary key

You inserted rows manually into the table providing explicit values for the id column, which means the sequence was not advanced as no default value was used. Now your sequence is out of sync with the actual values in the table.

You need to sync the sequence using setval() with your actual values if you do that:

select setval( pg_get_serial_sequence('the_table', 'id'),
               (select max(id) from the_table)
             );

If you want to prevent this kind of problem in the future, you can re-define the column as an identity column which will reject passing explicit values for it, so that you get an error if you try to bypass the default value.


How do you gain access to embedded MySQL database via localhost command line?


How should I best name my timestamp fields?

You should describe the purpose of the column and not necessarily the data type.  You can include date/time/timestamp in the name, but you should also include the meaning.  For example

CreationDate
StartDate
StatusTime
Accessed
Updated

Adding Date/Time/Timestamp and such at the end is particularly useful when the abscence of the addition would conflict with another column.  For example, a table may need both a Status and a StatusTime.


From the docs:

  A serious limitation of the inheritance feature is that....foreign key constraints only apply
  to single tables, not to their inheritance children.


SQL Server 2008 R2 Performance Degradation - High Lazy writes/sec and free list stalls/sec

This sounds like VMware might be using the balloon driver to remove memory from the SQL Server VM.

Ensure the VM is configured with locked memory and a CPU reservation.

Typically, for a SQL Server, you want reserved memory to match the amount of memory assigned to the VM.  You'd want to ensure SQL Server is configured with a valid max server memory setting.

Check "Architecting SQL Server on vSphere for some great best-practices from VMware.

Once you've disabled the balloon driver, if you keep seeing a high ratio of stolen pages, check to see what memory clerks are using more than 16MB memory with this query:

SELECT MemoryClerkName = domc.name
    , MemoryClerkType = domc.type
    , Pages = SUM(domc.pages_kb / (domc.page_size_in_bytes / 1024))
    , MB = SUM(domc.pages_kb * 1024) / 1048576
FROM sys.dm_os_memory_clerks domc
WHERE domc.pages_kb > 16384 /* clerks using more than 16MB */
GROUP BY domc.type
    , domc.name
ORDER BY SUM(domc.pages_kb) DESC;

The "Default / MEMORYCLERK\SQLBUFFERPOOL" should typically be the main consumer of memory.

This query will show how much memory SQL Server has allocated, how much it wants to be able to allocate, and how much physical memory is present in the machine:

SELECT TargetMB = dosi.committed_target_kb * 1024 / 1048576
    , CommittedMB = dosi.committed_kb
    , PhysicalMB = dosi.physical_memory_kb * 1024 / 1048576
FROM sys.dm_os_sys_info dosi

This will show you what Windows is reporting for memory state:

SELECT dosm.system_memory_state_desc
FROM sys.dm_os_sys_memory dosm

If there is lots of free memory in your system, you'll see "Available physical memory is high" reported.  If you see "Available physical memory is low", you'd probably benefit from adding memory to the server.