Load data from a temporal table to another temporal table in Azure

You have two interacting problems here: transferring data between Azure databases, and updating data maintained by system versioning. And possibly a third: live data in dev.

Transferring Data in Azure

If Azure Data Sync is not appropriate then there are other options that you might consider.

You could perhaps use SSIS packages or other ETL options to shuffle the data from A to B.
Linked tables are not supported in the same way as in on-prem SQL Server, but you can use "external tables" as an alternative.
Elastic Database Jobs offer a way to query data from one DB and write it to another, though this is more intended for logging rather than fuller data transfer so using it this way feels a bit hacky.

Updating History Tables

What-ever method you use to transfer data from one DB to the other, you have the issue of needing to directly manipulate the history data. As you have noted the system versioned tables protect themselves from this sort of modification: you can't directly modify the history table at all or specify the timings logged for modifications in the base table.

What you have to do for each table is:

Turn off system versioning:
ALTER TABLE schema.table SET(SYSTEM_VERSIONING = OFF);
ALTER TABLE schema.table DROP PERIOD FOR SYSTEM_TIME;
Make the changes you need to both the base and history tables
Restart history tracking:
ALTER TABLE schema.table ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); ALTER TABLE schema.table SET(SYSTEM_VERSIONING = ON); (you may need to add other options depending on how your tables are currently setup, for instance if your tables do not use the default naming convention).

So your overall process might be in four stages: bring updated data from the source, turn off history tracking, update the target data, turn back on history tracking. While those last three stages are happening you need to block other access to the target DB or corruption of the history data could result.

Alternative

Depending on the size of your databases, and the nature of your dev workflows, it may be far easier to simply backup and restore a complete fresh copy each time you need to refresh the other copies.

You may want to work with a restored copy of the database anyway, to remove the extra IO load that may slow production instances of your application, to easily make sure you get an accurate snapshot of the data without holding many locks, or to allow for a full depersonalisation process (see below).

Live Data In Dev

Unrelated to the technical matters of your question, this is likely to be problematical from a data protection and security PoV. If your application contains any personally identifying, or otherwise sensitive, information it is a big "no no".

If you do take data directly from Live systems for development then you should be "depersonalising" is on the way. This is usually easier to do reliably with the whole database backup+restore method: copy the DB in production, depersonalise the copy, back that up, transfer to the dev server, delete the now unneeded copy on the production server.

It is often better to generate sample data as part of your development process rather than trying to use production data. This way a problem in any depersonalisation processes won't expose you to security problems (and failed audits if you work with companies that require you be audited) because you never have live data to accidentally not alter. This test data can be more useful than real data if well-designed because you can make sure it contains all your expected edge cases (and some unexpected ones!) so it becomes part of your development and testing procedures.

You may need to occasionally copy production data for debugging purposes, when you have failed to reproduce a reported problem using other data, but this sort of testing is different from development and should be a last resort not a first port of call.


Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

OMG I spent hours thinking through this issue.

I answered three very tough questions addressing this exact issue.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

Please read them carefully so you can relive my episode of philosophical InnoDB, right or wrong.


Check if the trigger was fired by INSERT or DELETE

As long as the trigger is only for insert/delete:

IF EXISTS (SELECT 1 FROM inserted)
   -- I am an insert
ELSE
   -- I am a delete

And in fact if you are doing things based on a join to inserted/deleted, it can be even simpler:

-- do something for an insert
-- this will only actually do anything when it's an insert
INSERT dbo.somewhereElse SELECT whatever, 'insert'
  FROM dbo.sourceTable AS s
  INNER JOIN inserted AS i ON s.id = i.id;

-- do something for a delete
-- this will only actually do anything when it's a delete
INSERT dbo.somewhereElse SELECT whatever, 'delete'
  FROM dbo.sourceTable AS s
  INNER JOIN deleted AS d ON s.id = d.id;

If you add the update action to the trigger, the logic gets more complex, of course:

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
  IF EXISTS (SELECT 1 FROM deleted)
  BEGIN
    -- I am an update
  END
  ELSE
  BEGIN
    -- I am an insert
  END
END
ELSE
BEGIN
  -- I am a delete
END


What does TBSCAN GENROW mean in Explain Plan?

I think I've figured it out.  GENROW means it's creating a temporary table which is then joined to an actual table.  This is usually done when the query contains an IN (...) statement.  Specific to SAP, this is usually done for FOR ALL ENTRIES queries.


Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

I discovered that pgAdmin III does this by default. When you create a new foreign key constraint using the GUI, there is a checkbox "Auto FK Index" on the Definition tab. I hadn't noticed this until just now so it explains why the index was being created.


Database design tracking daily personal expense

I would use a variation of your second approach:

**Expense:**
id | date       | amount | category_fk  | description      |
------------------------------------------------------------
1  | 2014-12-01 |  1000  | 1            |                  |
2  | 2014-12-01 |  120   | 2            |                  |
3  | 2014-12-02 |  12    | 3            | Dinner at Arby's |
4  | 2014-12-02 |  10    | 4            |                  |

**Category:**
id | category_name |
--------------------
1  | rent          |
2  | parking       |
3  | restaurant    |
4  | entertainment |

I don't think you need a separate table for dates. You gain nothing by doing that, unless you want to store extra information for that date, and even then, the date itself would be a natural key for that.

The description of a specific expense has a 1:1 relation with that expense, so it doesn't need a table of its own, unless you want to reuse descriptions (at which point you no longer have a 1:1 relation).

The category needs a separate table, since that will not have a 1:1 relation but a 1:n relation (a single category will be used on several expenses).

If you want to model a n:n relation, where an expense can belong to several categories as well, you will need a cross-reference table like so (removing the category_fk column from the expense table):

**Expense_Categories:**
expense_fk | category_fk |
--------------------------
1          | 1           |
2          | 2           |
3          | 3           |
3          | 4           |
4          | 4           |

As you can see, the Dinner at Arby's is considered both 'Restaurant' and 'Entertainment'.


There is likely something wrong with your model. Mutual dependencies are very rare in relational database designs. This is what I assume you want to do:

create table photos
( photo_id ... not null primary key
, <additional attributes> );

create table venues
( venue_id ... not null primary key
, photo_id ... not null
      references photos (photo_id)
, <additional attributes> );

create table products
( product_id ... not null primary key
, photo_id ... not null
      references photos (photo_id)
, <additional attributes> );

To determine whether a photo belongs to a venue, product or both you could do something like:

select x.photo_id
     , case when y.photo_id is not null then 'Venue' end
     , case when z.photo_id is not null then 'Product' end
from photos x
left join venues y
    on x.photo_id = y.photo_id
left join products z
    on x.photo_id = z.photo_id


You almost get it, try by putting SUM() at the beginning of the CASE statement.

SELECT   shop_id,
         SUM(CASE WHEN currency= "GBP" THEN price ELSE 0 END) AS POUND,
         SUM(CASE WHEN currency= "USD" THEN price ELSE 0 END) AS USDOLLAR
GROUP BY shop_id


T-SQLRevoke permissions to all tables in the sql server in one go

you can try

EXEC sp_MSforeachtable @command1 = 'PRINT (''REVOKE SELECT on ? TO [Role] AS [dbo]'')'

and instead of print

EXEC sp_MSforeachtable @command1 = 'REVOKE SELECT on ? TO [Role] AS [dbo]'


converting mysql columns to bits in a single column

Use logical shifts << plus a bitwise or | operation.

For example:

SELECT (col1<<16) | (col2<<15) | (col3<<14) | (col4<<13) ...

MySQL docs are here.


@TimeLimit setting in Ola-Hallengren's IndexOptimize Running Much Longer Than The Value Set

As explained in the documentation (and evident from the procedure source code), TimeLimit is used to

Set the time, in seconds, after which no commands are executed.

(emphasis above is mine).
A command that started before the threshold will keep running until it completes.


Not really. There is pgAgent (part of pgAdmin package), that could be abused to do it, but it's not its main purpose.

Generally, to run such long queries/jobs, you need somewhere to have a psql session to live until it will finish. Usually, psql on screen on some kind of server - database server, or app server, doesn't matter much.


Active/ Active SQL Server Clustering on AWS

Active/Active denotes two live datastores that can be simultaneously accessed by the application(s) in question.  SQL Server does not have a solution for this, including AlwaysOn Availability Groups, which at best will only provide you with additional readable replicas.  Outside of Oracle RAC, there isn't a solution for this.


How to implement table purge with partition on SQL Server 2012?

The goal is to reduce the amount of transaction log by only updating metadata.

When data is move or remove from a partition, it Insert and Delete rows which will result in (tons of) LOB_INSERT_ROWS and LOB_DELETE_ROW in the transaction logs.

The only option would be to truncate the partition but this option does not exist.
We can avoid it by only using Merge and Split on empty Partitions.

In the sample below, I will keep things shorter and only create data for the past 3 months (ie. August, September and October) but you can easily expand it to 9 months. August will be removed once data starts being added to November and so on with September and December...

Create Files and Filegroups:

I first create 6 files and file groups [Part_0] to [Part_5]:

Alter Database [Test] Add Filegroup [Part_0];
...
Alter Database [Test] Add Filegroup [Part_5];
Alter Database [Test] Add File( NAME = N'Part_0', FILENAME = N'...\Part_0.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_0];
...
Alter Database [Test] Add File( NAME = N'Part_5', FILENAME = N'...\Part_5.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_5];

Create Function and Scheme:

Create Partition Function [DateKeyPartFunction] (datetime2)
as Range Right For Values ('20150801', '20150901', '20151001', '20151101', '20151201');
Create Partition Scheme [DateKeyPartScheme] as Partition [DateKeyPartFunction]
To ([Part_0], [Part_1], [Part_2], [Part_3], [Part_4], [Part_5]);

Again there are 6 partitions. This will be explained later but this is mostly due to the need to have empty partitions.

Create Table and Clustered Index:

Because I don't know the exact design of your table, I will be working with this table:

Create Table dbo.DataPart(id int identity(0, 1), name char(1000), name_date datetime2);

and this Clustered Index:

Create Clustered Index IDX_Part On dbo.DataPart(name_date) On DateKeyPartScheme(name_date);

Dummy data:

This code creates over a million record in a range of dummy dates every 6 seconds from October to August(now):

With inc(n) as(
    Select ROW_NUMBER() over(order by (select 1))-1 From (
        Select 1 From (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x3(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x4(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x5(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x6(n)
    ) as x(n)
)
Insert into dbo.DataPart(name, name_date)
Select TOP(1000000) '', DATEADD(second, -n*6, getdate()) From inc;

Partitioned Data:

This will partionned as follow:

Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    184042
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0

[Part_1], [Part_2], [Part_3] contain data for August, September and October.
[Part_0] (ie. <'20150801') only exists because it needs an empty partition before we can empty [Part_1] (August) once in November. It would be data for July and before but must remain empty.
It make things easier to already have an empty partition for next month although it will remain empty until it reach November. This is the purpose of [Part_4] for November.
Partition Scheme needs an extra partition for everything outside of the right boundary. This is [Part_5] for December and beyond and it must remain empty as well.

Add November:

Once in November, new rows will go to [Part_4] and August data can be removed from [Part_1].
The only way to remove it without having to delete hundred of thousands of rows is to move [Part_1] away from the table:

Create Table dbo.DataPart_Temp(id int identity(0, 1), name char(1000), name_date datetime2);
Create Clustered Index IDX_Part_temp On dbo.DataPart_temp(name_date) On [Part_1];
Alter Table DataPart Switch Partition 2 to DataPart_temp Partition 1;

DataPart_Temp must be identical to DataPart (columns, indexes)
Because [Part_1] is move from DataPart to DataPart_temp, the clustered index on DataPart_temp must be created on the same filegroup: [Part_1]
[Part_1] is the second partition of DataPart and is switch  to the first and only Partition of DataPart_temp. All August rows are now in DataPart_temp.

the table is now partitioned as follow:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    0
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0

Merge Partition:

[Part_0] and [Part_1] are now empty and can be merge:

Alter Partition Function [DateKeyPartFunction]() Merge Range ('20150801');

[Part_1] has been removed:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    0
5  [Part_5] >= '20151201'                   0

Add Next month:

Now that [Part_1] is not used anymore, it can be added to the Partition Scheme as the next available partition:

Alter Partition Scheme [DateKeyPartScheme] Next Used [Part_1];

And then [Part_5] (December, >= '20151201') can be splited:

Alter Partition Function [DateKeyPartFunction]() Split Range ('20160101');

Since [Part_5] is empty, nothing has to be move. The other half of [Part_5] will go to the next available partition which is [Part_1]:

Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    x rows in November
5  [Part_5] >= '20151201'   < '20160101'    0
6  [Part_1] >= '20160101'                   0

Clean Up:

DataPart_temp can now be truncated and then dropped (or at least drop its Clustered Index).

Add Following months:

In December, Partition ID 2 must be move away ([Part_2]), merged with partition 1 and then added back before January split.

In order to automate this process, you will have to look for the name of the filegroup for Partition 2:

And then create dynamic SQL to:

Add Clustered Index to DataPart_temp on [Part_X] (similar to DataPart)
Switch [Part_X] from DataPart to DataPart_temp
Merge Partition 1 and 2
Add [Part_X] to DataPart
Split the last partition
Truncate DataPart_temp
Drop DataPart_temp or its Clustered Index

If you use fn_dblog, you should see that transaction logs are minimal.


Setting the isolation level to SERIALIZABLE would have the same effect:

  The highest isolation level, serializable, guarantees that a
  transaction will retrieve exactly the same data every time it repeats
  a read operation, but it does this by performing a level of locking
  that is likely to impact other users in multi-user systems.

TABLOCK will suffice, there is no need for TABLOCKX to achieve what you've described so far. If the transaction you mention later updates or inserts to the table then TABLOCKX, if it's just to read for the MAX it's unnecessary and other processes will at least be able to read from the table.

Whether you use the isolation level or TABLOCK hint depends on what proportion of the table you will be scanning to find the MAX aggregate. If you need to touch any significant percentage of the rows TABLOCK is logical. A small percentage and a desire to maintain some degree of concurrency then try SERIALIZABLE. TABLOCK would also eliminate the deadlock risk.

Both options would achieve your aim of preventing an inconsistent result from the aggregate.


Troubleshooting Oracle ORA-08103 - object no longer exists

How intermittently are you getting the error?

Do you have any logging in the trigger that is truncating a partition? You could use a simple procedure that writes (in an autonomous transaction) some information about which partition is being truncated.

Also create the on servererror trigger as suggested by Leigh Riffel, and log the query and error stack.

Finally, I would enable tracing of all occurrences of the long running query (assuming this query isn't executed all too often!):

alter session set sql_trace = true;
alter session set tracefile_identifier = wrschneider08103;

before the query and

alter session set sql_trace = false;

after.

Then, once the error has again reared its ugly head, run tkprof on the last trace file with 'wrschneider08103' in its name...

Hopefully you'll be able to correlate the information you've thus collected.


Oracle has an index type called a Bitmap Index which it describes as...

  A database index in which the database
  stores a bitmap for each index key
  instead of a list of rowids.

If a table has a bitmap index or a hint is used, it can use a bitmap access plan on regular B-tree indexes.  Bitmap indexes can be joined, union-ed, and intersected.

There is an excellent explanation at use-the-index-luke.com where it includes the following implementations of combining multiple B-Tree indexes:

  DB2:
  DB2 supports multiple index access on
  LUW 9r7 (using a dynamic bitmap) and
  on zOS v10.

  MySQL:
  MySQL has an index merge optimization
  starting with release 5.0.

  Oracle
  The Oracle database uses BITMAP
  CONVERSIONs to combine multiple indexes on the fly (introduced with
  9i).

  PostgreSQL
  PostgreSQL uses bitmaps to combine multiple indexes since version 8.1.

  SQL Server
  SQL Server can use multiple indexes ("Index Intersect") starting with V7.0 using a hash algorithm.

See also this StackOverflow question in which the first answer says that SQL Server does something similar to Bitmapped indexes using index intersection.

Efficient and Flexible Bitmap Indexing for Complex Similarity Queries is the closest reference I have found associating Bitmapped indexes with the word multidimensional.  Multidimensional seems to be more of a way to use indexes rather than an attribute of them.


DB2: Why does translate function also replace spaces?

The "to-string" in your TRANSLATE is a single "X", so only the first character found in "from-string" (X'00' in your case) will be translated to "X". All other characters found in "from-string" will be replace by the "pad" character. You don't specify one, so a blank is used by default. This is why the X'15' at the end of your inut field is translated to X'40', which is an EBCDIC blank.


How do I completely remove MySQL from a 64-bit windows 7 machine?

You can run the command sc delete [MySQL] where [MySQL] is the name of the service.


Mysql LOAD DATA INFILE 100,000 csv records perfect BUT 300,000 records - 0 rows inserted

Files checked with hex editor and answer revealed.

My CSVs differed in that rows either terminated with:

  Hex 0D CARRIAGE RETURN (\r), or

  Hex 0D CARRIAGE RETURN (\r) AND 0A LINE FEED (\n)

This is the line of code:

LINES TERMINATED BY '\r\n'

or

LINES TERMINATED BY '\r'

In my case, taking out the \n took care of it - either CSV imports perfectly.

If this line does not match the CSV, you get the silent treatment.  The SQL import will run without error yet no rows will import.


Does any RDBMS parallelize or otherwise optimize UNION queries?

Can each section of UNION/UNION ALL run concurrently?

Oracle

YES (oracle 12c+)

MySQL

NO (per OP's question)

PostgreSQL

No for Postgres 10 or earlier
Yes for Postgres 11 currently in Beta

Microsoft SQL Server

Mostly no, when UNION ALL is implemented by a Concatenation operator. Although not documented (or guaranteed), Concatenation has been observed to always read from its inputs in sequence. Each input may be processed using multiple threads of execution. There may be a period of time where threads from different instances are reading from different inputs as processing moves from one input to the next.
Somewhat yes, when UNION ALL is implemented by a Concatenation operator and the inputs reside on different servers (Open() calls are performed asynchronously - Async Concat operator). How much processing occurs during Open() depends on plan shape.
Mostly yes when UNION or UNION ALL is implemented by a Merge Join Concatenation operator, since each instance (thread) reads from any input as needed to perform an order-preserving union.

Are there other "under the hood" sorts of optimizations?

There are several approaches found in literature regarding common subexpression identification: detecting the same expression in two or more union subqueries. Having done so, you can either use the following equivelence: (A join B join C) union (A join B join D) -> A join B join (C union D) to push union before joins when you estimate that this plan will be cheaper, or use methods from an area known as multi-query optimization (here obviously we have a single query, but we can regard different subqueries as separate queries) in order to reuse an expression. Again the decision to reuse an expression is not trivial, as doing so often requires materializing the intermediate result and can lead to much worse execution times. Also, in case that several complex common expressions exist in the subqueries, deciding which one should be materialized (or even which subexpression of an expression) affects the decisions for the rest, making the search space during optimization doubly exponential. In any case, examining these issues heavily affects optimization time, and although there are promising research prototypes, I believe that most commercial products do not consider this kind of optimization. Also note that using such methods results in the whole query being "less-parallelizable", as you can no longer execute each subquery completely independently.


Retrieving What variables mean when retrieving view definitions?

a and c are table aliases; they're defined in the FROM clause of the SELECT statement:

...
FROM (((actor a
        LEFT JOIN film_actor fa ON ((a.actor_id = fa.actor_id)))
       LEFT JOIN film_category fc ON ((fa.film_id = fc.film_id)))
      LEFT JOIN category c ON ((fc.category_id = c.category_id)))
...

a refers to actor, c to category, etc.

It's a little hard to follow until you notice that there's a sub-query involved inside the outermost group_concat function.


The answer is in the bold portion of my answer.

Starters

You have a table of Lead Managers (Lead_Managers) and a table for Companies (Company). A Lead Manager can be in multiple companies and can have multiple Lead Manager roles in one company.

Inner Query

The inner query retrieves the DISTINCT number of (COUNT) Lead Manager's  Codes (Lead_Manager.company_code) where the Lead_Manager's company code matches ...

Outer Query

... the company code (c.company_code) in the Company (Company c) table.

Result

The result should be a list of Company Codes, Founders and distinct count of Lead Managers for each given company. The Distinct will only select unique Lead Manager', or put another way: Even if a lead manager has multiple Lead Manager Roles in a company only 1 count will be made for that company.


You need to use CONVERT(VARCHAR(xx), ColumnName) on all columns if you want the column to appear shorter in text output view.

Convert your query into something like:

SELECT [Server] = CONVERT(VARCHAR(30), SERVERPROPERTY('Servername'))
   , DatabaseName = CONVERT(VARCHAR(30), '''' + bs.database_name + '''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(30), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = 'D'
GROUP BY bs.database_name
ORDER BY bs.database_name;

This will give output similar to:

Server                         DatabaseName                   LastDatabaseBackupDate
------------------------------ ------------------------------ ------------------------------
[ServerName]                   'A'                            Sep 25 2015 11:32AM
[ServerName]                   'B'                            Apr 21 2015 12:09PM
[ServerName]                   'C'                            Feb 24 2015  9:16PM
[ServerName]                   'D'                            Oct  8 2014 11:02AM
[ServerName]                   'E'                            May 14 2014  6:27PM

(5 row(s) affected)

If you want to be able to dynamically change the column widths without modifying the T-SQL code, you'll need to use Dynamic SQL:

DECLARE @ColumnWidth VARCHAR(4);
DECLARE @Cmd NVARCHAR(MAX);

SET @ColumnWidth = '24';
SET @Cmd = '
SELECT [Server] = CONVERT(VARCHAR(' + @ColumnWidth + '), SERVERPROPERTY(''Servername''))
   , DatabaseName = CONVERT(VARCHAR(' + @ColumnWidth + '), '''''''' + bs.database_name + '''''''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(' + @ColumnWidth + '), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = ''D''
GROUP BY bs.database_name
ORDER BY bs.database_name;
';

EXEC (@cmd);

Here, I've set the width to 24 for all columns, and it comes out looking like:

Server                   DatabaseName             LastDatabaseBackupDate
------------------------ ------------------------ ------------------------
SERVERNAME               'A'                      Sep 25 2015 11:32AM
SERVERNAME               'A'                      Apr 21 2015 12:09PM
SERVERNAME               'A'                      Feb 24 2015  9:16PM
SERVERNAME               'A'                      Oct  8 2014 11:02AM
SERVERNAME               'A'                      May 14 2014  6:27PM

(5 row(s) affected)

If you really want to go crazy and have the columns automatically size themselves, you would do this:

DECLARE @ColumnWidthServer VARCHAR(4);
DECLARE @ColumnWidthDatabase VARCHAR(4);
DECLARE @ColumnWidthLastBackup VARCHAR(4);

DECLARE @Cmd NVARCHAR(MAX);

SELECT @ColumnWidthServer = 1 + LEN(CONVERT(VARCHAR(128), SERVERPROPERTY('Servername')))
   , @ColumnWidthDatabase = 1 + MAX(LEN('''' + bs.database_name + ''''))
   , @ColumnWidthLastBackup = 1 + MAX(LEN(CONVERT(VARCHAR(128), bs.backup_finish_date)))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = 'D';

SET @Cmd = '
SELECT [Server] = CONVERT(VARCHAR(' + @ColumnWidthServer + '), SERVERPROPERTY(''Servername''))
   , DatabaseName = CONVERT(VARCHAR(' + @ColumnWidthDatabase + '), '''''''' + bs.database_name + '''''''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(' + @ColumnWidthLastBackup + '), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = ''D''
GROUP BY bs.database_name
ORDER BY bs.database_name;
';

EXEC (@cmd);


It depends on if you use an INSTEAD OF trigger or an AFTER trigger.
It sounds like you want an INSTEAD OF trigger where you can prevent the default values from being INSERTed or UPDATEd and replace them with NULLs instead.
(An AFTER trigger would effectively cause the index to update twice.)
You can read more on how to implement an INSTEAD OF trigger here.


You will want to use dynamic SQL to change the DB you are executing the query in:

DECLARE @dbname AS VARCHAR(100)
DECLARE @query AS VARCHAR(MAX)

SELECT @dbname = name
FROM sys.databases d
WHERE /*enter criteria here to get DB*/

SET @query = '
use ' + @dbname + '
/*enter your query here*/
'

PRINT @query
EXEC(@query)


heroku postgres sql changes not reflected in db

You're never committing your transaction. Add connection.commit() before you call cursor.close(). Docs here.


Any parameter that needs a variable list or array is probably a good candidate for a user-defined table type. I would create the type as:

CREATE TYPE [PropertyVariableTableType] AS TABLE (
    PropertyName nvarchar(255),
    PropertyValue nvarchar(255) )

Table types can be used as parameters to stored procedures just as any other type. You can then either join on the user-defined table or iterate the rows to dynamically build the query.


Is it possible to get schema ID in another database without using dynamic sql?

To answer the question literally, no there isn't a way to do this in T-SQL without using dynamic SQL (but see Aaron's suggestion to use a view).  You can, however, use a SQLCLR function (which can be called from a T-SQL function).  The following is demo code to illustrate the principle:

-- Returns 7 from any database context
SELECT dbo.GetSchemaID (N'AdventureWorks2012', N'Production');

T-SQL script:

CREATE ASSEMBLY [CLR] AUTHORIZATION [dbo]
FROM [encoded data, 9218 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[GetSchemaID] (@DatabaseName [nvarchar](4000), @SchemaName [nvarchar](4000))
RETURNS [int]
AS EXTERNAL NAME [CLR].[UserDefinedFunctions].[GetSchemaID];

C# source:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        IsDeterministic=false,
        IsPrecise=true,
        SystemDataAccess=SystemDataAccessKind.Read
        )
    ]
    public static int GetSchemaID(string DatabaseName, string SchemaName)
    {
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format("SELECT @id = [schema_id] FROM {0}.sys.schemas WHERE [name] = '{1}';", DatabaseName, SchemaName);
                cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                return (int)cmd.Parameters[0].Value;
            }
        }
    }
}


Get normalized representation of SET datatype

SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set`
FROM `test_set`,
      -- generate masks based on SET values list count
     (SELECT 1 `mask` UNION
      SELECT 2 UNION
      SELECT 3) `mask`
WHERE `set_2` & 1;

fiddle

If server version allows build mask in recursive CTE (dynamic count may be used)


How to efficiently store impressions in db?

Summary tables.  Separate table for counts.
Are you keeping information about each impression?  Or just a counter?  It makes a big difference.  But we can solve it either way.
If you go with a table with just daily counter:
CREATE TABLE FooImpressions (
    foo_id  INT UNSIGNED NOT NULL,  -- same as main table, but not AUTO_INCREMENT
    date DATE NOT NULL,
    counter INT UNSIGNED NOT NULL,
    PRIMARY KEY(foo_id, date)
) ENGINE=InnoDB;

And
INSERT INTO FooImpressions (foo_id, date, counter)
    VALUES (?, ?, 1)
    ON DUPLICATE KEY UPDATE
        counter = VALUES(counter) + 1;

This automatically starts a new row for each Foo each day (as needed).
Then, to get last weeks counts:
SELECT foo_id, SUM(counter) AS impressions
    FROM FooImpressions
    WHERE date >= CURDATE() - INTERVAL 7 DAY
      AND date  < CURDATE()
    GROUP BY foo_id;

Of course, the date range can be changed to handle any consecutive date range.
There are variants on this for more complex situations and for situations that need more performance.  But this is a simple "summary table" together with its maintenance and its usage.
Yes, one variation would have DATETIME and truncate that to the hour.  Don't have both an hourly summary table and a daily one until you have demonstrated that the hourly one is not fast enough.
If you need up-to-the-minute counts, some Summary techniques do not provide such (because the do nightly or hourly batch updates for speed); the use of IODKU is always up-to-the-minute.
Even if you have another table with more info on the impressions, have this Summary table for speed of reporting.
More discussion:  http://localhost/rjweb/mysql/doc.php/summarytables


Is there any function like checksum in Oracle

You can use Oracle's STANDARD_HASH function.

  Oracle's STANDARD_HASH function "computes a hash value for a given
  expression" (see the documentation here). Use the checksums in the WHERE
  clause of the UPDATE (in the MERGE statement).

You might do something like this:

-- 2 tables
create table table1 as
select 1 id, 1 a1, 1 b1, 1 c1, 1 d1, 1 e1, 1 f1 from dual;

create table table2 as
select 2 id, 2 a2, 2 b2, 2 c2, 2 d2, 2 e2, 2 f2 from dual;

and then calculate a SHA256 checksum:

SELECT
  STANDARD_HASH ( T.id || T.a1 || T.b1 || T.c1 || T.d1 || T.e1 || T.f1, 'SHA256' )
AS my_hash_1
FROM table1 T ;

MY_HASH_1 0x2558A34D4D20964CA1D272AB26CCCE9511D880579593CD4C9E01AB91ED00F325

and

SELECT
  STANDARD_HASH ( T.id || T.a2 || T.b2 || T.c2 || T.d2 || T.e2 || T.f2, 'SHA256' )
AS my_hash_2
FROM table2 T ;

MY_HASH_2
0xCC2E018AA6EB9612CCD027BBDCDC9B8C8D351789F14CAE4D688A876C18938235

This will tell you if your records are the same or different (well, to a very high probablity! :-) ).

With STANDARD_HASH, you get to choose your hashing algorithm. You might want to be careful about your choice - MD5 is much less processor intensive than SHA256. If security is not a major concern, you might want to use that instead? See the fidddle here for more.

See here for the excellent question, and here (same thread) for the excellent answer, both of which I (shamelessly!) lifted to answer this question. I felt that the thread and answer were both good enough to merit a place on dba.stackexchange!


high writelog wait on OLTP system and fast SSD storage, log flush is slow

This is not an answer for 'what is the solution from now on' but more of how to identify the problem.

Transaction log is not like data pages in terms of how it is hardened to disk. Transaction log is first written to log buffer and then asynchronously written to disk.  In 2016 there is a limit of 112 concurrent log-flush I/Os at any one time for each database’s transaction log.

There are 3 things that cause SQL Server to need to flush a log buffer to disk (to an adjoining log block in the transaction log).

A transaction commits and that transaction has an active log record
in a log buffer.  This will cause all log records in those buffer
(again, up to 60K worth of transaction log record for each log
buffer) to be flushed to the log file.
We hit the 60K size limit for the log buffer and must flush it to
disk.
We have data pages that we must write to disk and there are log
records related to those pages.  We must write those log records to
the log file BEFORE we write the data pages to the data files (this
supports our Write Ahead Logging (WAL) protocol).

The smallest log flush possible is a single 512-byte log block. If all transactions in a workload are very small (e.g. inserting a single, small table row) then there will be lots of minimally-sized log flushes occurring. Log flushes are performed asynchronously, to allow decent transaction log throughput, but there is a fixed limit of 112 concurrent log-flush I/Os at any one time.

As you described you have good io subsystem in conjunction with a large number of short transactions/sec your problem may be following.

On a high-performing I/O subsystem, the writes may complete extremely quickly, but the limit of 112 concurrent log-flush I/Os creates a bottleneck when trying to make the log records durable on disk. This situation can be identified by low write latencies and a near-constant number of outstanding transaction log writes near to 112 in the aggregated output of sys.dm_io_pending_io_requests.

Most of what I mentioned above are from following resources and there are few solutions described.  Also Paul Randal has a short video with demo scripts to confirm if you are having the issue I mentioned above.

Transaction Log Monitoring by Paul Randal
Trimming the Transaction Log Fat part 1  by Paul
Trimming More Transaction Log Fat part 2 by Paul Randal
WHAT IS SQL SERVER’S IO BLOCK SIZE? by Argenis Fernandez
Video by Paul Randal
Demo code to use with the video by Paul Randal


SQL Server - monitor changes on linked server

Are you looking to synchronize the data locally?  Ideally, you would accomplish this through some form of replication from the source.

If you have a local copy of all tables, you can create a script or stored procedure to check for records deleted, inserted, or updated by comparing to your copy of the data.  This would have to be done on a table by table basis.  If there are hundreds of tables, then this approach is probably not feasible.

It helps if there is a time stamp of some sort on the table row.  That will assist in determining when an update occurred.  Inserts and deletes are easier as you just find records that exist at the source but not in the destination and vice versa.

Schedule your script to run at defined intervals via SQL Agent.


Get streak count and streak type from win-loss-tie data

Since you are on SQL Server 2012 you can use a couple of the new windowing functions.

with C1 as
(
  select T.team_id,
         case
           when M.winning_team_id is null then 'T'
           when M.winning_team_id = T.team_id then 'W'
           else 'L'
         end as streak_type,
         M.match_id
  from FantasyMatches as M
    cross apply (values(M.home_fantasy_team_id),
                       (M.away_fantasy_team_id)) as T(team_id)
), C2 as
(
  select C1.team_id,
         C1.streak_type,
         C1.match_id,
         lag(C1.streak_type, 1, C1.streak_type)
           over(partition by C1.team_id
                order by C1.match_id desc) as lag_streak_type
  from C1
), C3 as
(
  select C2.team_id,
         C2.streak_type,
         sum(case when C2.lag_streak_type = C2.streak_type then 0 else 1 end)
           over(partition by C2.team_id
                order by C2.match_id desc rows unbounded preceding) as streak_sum
  from C2
)
select C3.team_id,
       C3.streak_type,
       count(*) as streak_count
from C3
where C3.streak_sum = 0
group by C3.team_id,
         C3.streak_type
order by C3.team_id;

SQL Fiddle

C1 calculates the streak_type for each team and match.

C2 finds the previous streak_type ordered by match_id desc.

C3 generates a running sum streak_sum ordered by match_id desc keeping a 0 a long as the streak_type is the same as the last value.

Main query sums up the streaks where streak_sum is 0.


Should an update statement query plan ever go parallel?

From the documentation:

The update and delete operators in a parallel query execution plan are executed serially, but the WHERE clause of an UPDATE or a DELETE statement may be executed in parallel. The actual data changes are then serially applied to the database.

As to your second question,

does it guarantee a communication buffer resource deadlock

Any deadlock is predicated on the query / queries being run and how they are structured and executed, SQL Server does not inherently guarantee any deadlocks on its own.


How to make snapshot for new tables only

There sure is. There are two settings on the publication that need to be set correctly to get the behavior that you want. allow_anonymous and immediate_sync must be set to false. You can change both with a call to sp_changepublication. Note that the tables needn't be empty for this to work.


This can't be done without some kind of workaround, so here's one for you.

You can recompile the PL/SQL after creation & raise an exception if the recompilation fails. This will cause SQL*Plus to exit on failure.

For example:

test.sql:

create or replace procedure foo
as
begin
  this is an error;
end;
/

exec execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error')

Example:

[oracle@node1 ~]$ sqlplus phil/phil

SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 14 20:45:47 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> @test.sql

Warning: Procedure created with compilation errors.

BEGIN execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error'); END;

*
ERROR at line 1:
ORA-20000: compilation error
ORA-06512: at line 1

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$


How are SQL backups different from IT regular nightly server backups?

Database backups give you point-in-time restore capability (provided you have FULL recovery model). Even if your IT people take backups every few minutes, which is extremely unlikely, you'll still have a gap.

Server backups do not replace database backups, they complement them by "archiving" the database backup files long-term (i.e. more than just today).

In the end you and your management have to decide your RPO (the recovery point objective - how much you need to be able to recover in a crash). With only daily server backups and no database backups, you stand to lose a full day's work in the worst case.

Edit: @Sting has a valid point in that shadow copies (the mechanism most likely used to make server backups) are not very likely to take an exactly simultaneous copy of all your database files (including log files), which may lead to inconsistencies when you restore the backup. For instance, if the shadow copy reads the transaction log a few milliseconds before it reads the database file, the database file could contain an uncommitted transaction, but as the transaction was committed a millisecond later, the log will not have any record of it.


How to prevent Performance issue & going for Denormalization

credits : Vladimir Oselsky
Normalization is never for a performance of reads it is for data integrity and performance of writes. You normalize to avoid redundant data and creating problems by having same data exists in different locations. If you want most amazing performance, you denormalize by throwing everything into a single table and create a lot of indexes. That is why for reporting (DW) it is a practice to denormalize to increase the performance of reads. Save yourself a lot of headache and do a lot of reading on how other people solve same problem.
I strongly believe in 3NF, but going beyond that is absurd in my book, I have yet to see a valid business case for doing anything above that. Sooner or later you will find a need to denormalize for a specific process, usually it is done for reporting, data warehouse need. A good rule is OLTP = Normalize, OLAP = Denormalize.
It is a nature of database design and request from the business. Whenever you are designing a system, you are designing for a specific purpose, and later someone else comes along and request information that you might not have been prepared to give, in that case, you are required to denormalize to provide a best performance.
To increase the performance go for "Indexes, query tuning, hardware upgrades, server configuration and etc"....


Is there performance loss in out of sequence inserted rows (MySQL InnoDB)

Tables are by nature unsorted, so you will not have any performance loss on that site, after inserting your data, but we doen't how your instances is smaller, we can't  tell the impact it will have.
Your index on that field will be sorted and so will find the wanted rows quite fast, at least faster as scanning the hole column.


Lost SQL credentials and unable to connect to SQL via SQLCMD

The correct format of the parameter -S for the SQLCMD prompt is: Server\Instance.

You seem to have tried localhost\instance\database which doesn't work.

SQLCMD -S .\BACKUP-MGMT-01\CONSULT
          ^^        ^     ^   ^
          ||        |     |   +--- Database
          ||        |     +------- Separator
          ||        +------------- Instance
          |+---------------------- Separator
          +----------------------- Server (. = localhost)

In any case the error message is stating you are having issues with the server name and/or instance name.

If your server is named BACKUP-MGMT-01 and your instance is named CONSULT then you might want to try:

SQLCMD -S BACKUP-MGMT-01\CONSULT -E

If your instance is really named BACKUP-MGMT-01 then you might want to try:

SQLCMD -S .\BACKUP-MGMT-01 -E -d CONSULT

Where -d is the parameter for database and -E is for trusted connections.

Reference Reading

sqlcmd Utility (Microsoft | Docs | SQL Docs)


SQL Job step history to display DML changes

Use SQLCMD through a cmdexec jobstep, instead of TSQL step.


Pull entire row per user based on MAX(Column) MySQL

You need to group by both ID and Name:

SELECT ID, Name, MAX(Score)
FROM TestTable
GROUP BY ID, Name


MS Access: error 3045 while linking to back-end

Turns out the problematic user could create new files and delete them in the location, but did not have permission to delete files made by other users due to changes made by our IT.

As such the problem is now resolved.

Thanks to everyone who read and sorry for taking your time.


How to avoid losing information via UPDATEs due to database design issues

The simplest way to implement the requirements inside the database would be to create two separate tables.  Then, you just don't GRANT UPDATE on the set of tables used to store the "final documents".  This assumes that the end-users are logging in with an account that does not own the tables.

create table not_final_documents ( document_id int );
grant insert,update,select,delete on not_final_documents to end_user_role;

create table final_documents ( document_id int );
grant insert,select on final_documents to end_user_role;

This enforces the requirement of "can't modify final documents" even if the users use a different application to work on the document/packages.

You'll want to create database side procedures (T-SQL, PL/SQL, etc) to handle the data management between not_final_documents and final_documents.


SQL Server 2014: Graceful shutdown of an Availability Group node

You should failover to one of the other nodes first, before shutting down SQL.

This can be done either in SQL Management Studio (under the Always On High Availability node in the Object Explorer tree), or alternatively using the Windows Server Failover Cluster Manager program, which will be available in the Start Menu of all the Windows servers hosting the SQL instances.

Using SSMS is considered the better approach, as the WSFC utility won't know the synchronisation state of the other replicas.

To do a failover via TSQL instead, connect to the node you want to failover to (not from), and execute this, once for each listener group you have:

T-SQL : ALTER AVAILABILITY GROUP My_Group_Name FAILOVER;

In PowerShell you have this option: Switch-SqlAvailabilityGroup

(Source for code samples)


Syncing a SQL Server table with a CSV file

I have a partial answer - according to Microsoft's documentation it should work but I have not tested it. As I understand it you have a CSV file which is regularly updated, and you want the data to be available in a SQL Server 2014 database.

SQL Server 2012 onwards supports FILETABLE - this is a special type of table that maps onto folders and files in the file system. Each row in the filetable corresponds to an actual file, and updates to the physical files are reflected by updates to the rows in the filetable, and vice versa. See https://msdn.microsoft.com/en-us/library/ff929144(v=sql.120).aspx that describes FILETABLE. This Microsoft article further states that external to SQL Server, the filetable appears as a file share with files and directories. Another article https://technet.microsoft.com/en-us/library/gg492083(v=sql.110).aspx states you can use Windows explorer, or a command prompt to move and copy files into the filetable.

So assuming this works, you could create a SQL Server filetable that maps to the CSV file you want to load. You still have the problem of extracting the data from the filetable into a usable form in SQL Server. To do this, create a database view that retrieves the row from the filetable, converts it to a varchar(max), then uses a SQL Server split function with APPLY to split the string at line boundaries, then at column boundaries. A split function is discussed here https://stackoverflow.com/questions/20512855/split-function-by-comma-in-sql-server-2008 . SQL Server 2016 has an inbuilt string split function, although you are not using that version.

The result should be you have a database view, which gets the current state of the CSV file from the filetable (file system) and converts it to tabular form. By using the view you should always have an up to date version of the data from the CSV file.

DIsclaimer: As stated I have not tested this, so feel free to amend or delete if it's known to be incorrect.


Copy Database from Production to Development

Backup from PROD with COPY_ONLY, compression and restore it on DEV server is the best option.

Make sure to enable Instant File initialization to cut down the restore time.

Edit:

  I don't have a permissions to restore database. is it safe to use copy database ?

This might imply that you are not authorized to do such operation. You can ask your DBA to help you out once you have put in a proper support ticket.

Also, its quiet important to wipe out PII information when you do a restore on a NON PROD system - as @MichaelGreen pointed out.


sql architect regarding one to many relationship between table

I would just add b.id into table C and avoid columns [zip] and [information] at all because you can easily find [zip] and [information] by joining table B via b.id (which is a PK)


SELECT RELEASE_LOCK causing "Mysql has gone away"

The most likely explanation for this is that an intermediate piece of network hardware is tearing down or "forgetting" your idle TCP connection, assuming your wait_timeout system variable is not set too low.

Stateful firewalls maintain internal data structures of all current "flows" -- source/destination address/port quads -- and if a flow has no traffic for too long, the record of the flow is simply dropped from the firewall's memory.  The next TCP packet to arrive doesn't match any established flows, and doesn't have the SYN flag set, so it's not a new connection, and the firewall considers it invalid and either drops it or spoofs an RST packet from the destination.

Most likely, you would get this same error with any query SELECT NOW(); for example, would probably return the same result, once the connection has been totally idle for too long.  Note that running a query doesn't prevent the connection from being "idle" at the TCP layer.

The connection might not disappear from the processlist in the mean time, because, since it's completely idle, the server would not have sent anything and would therefore not have realized that at some point on the network, it is an invalid TCP connection.

This occurs, for just one example in my experience, within Amazon EC2.

The workaround is to enable TCP keepalives in the operating system itself on at least one end of the connection.  This will cause TCP traffic to continue to be exchanged by the hosts during the idle time, even though there's no payload on the connection.  Enabling these should mediate this issue.

Linux: http://tldp.org/HOWTO/TCP-Keepalive-HOWTO/usingkeepalive.html

Windows: https://technet.microsoft.com/en-us/library/cc957549.aspx

If curious, then you should also, by trial and error, be able to discover a period of time after which idle connections are no longer usable... for example, connections left idle for 14 minutes may be fine, but connections left idle for 16 minutes will always be dead, suggesting the firewall has a 15 minute flow expiration timer.


Getting postgreSQL binaries without third party tools

Is there any other source where I can download only postgreSQL binaries for linux?

You can use BigSQL's command line client for that:

-bash-4.2$ python -c "$(curl -fsSL https://s3.amazonaws.com/pgcentral/install.py)"

Downloading BigSQL PGC 3.3.6 ...
Unpacking ...
Cleaning up
Setting REPO to https://s3.amazonaws.com/pgcentral
Updating Metadata

BigSQL PGC installed.  Try 'bigsql/pgc help' to get started.

-bash-4.2$ cd bigsql
-bash-4.2$ ./pgc download pg10

-bash-4.2$ ll -h
total 19M
drwxrwxr-x 4 zaphod users  115 Aug 29 15:50 conf
drwxr-xr-x 3 zaphod users   18 Aug 29 15:49 data
drwxrwxr-x 5 zaphod users   47 Mar  1 22:41 hub
drwxr-xr-x 2 zaphod users   27 Aug 29 15:49 logs
-rw-r--r-- 1 zaphod users  19M Aug 29 15:50 pg10-10.5-1-linux64.tar.bz2
-rwxrwxr-x 1 zaphod users 1.7K Mar  1 22:41 pgc
-bash-4.2$

The archive pg10-10.5-1-linux64.tar.bz2 contains the following top-level directories:

pg10/lib/
pg10/init/
pg10/bin/
pg10/include/
pg10/share/

This can also be used to download a ZIP archive for Windows. "Installation" instructions for BigSQL on Windows can also be found in the above link.


Why do I only see SYS and INFORMATION_SCHEMA tables? Access 2007 ODB to SQL Server 2008

Thanks to Jimbo and Shark's comments I was able to fix my problem by adjusting the permission for the tables I wanted to see. I did not have SELECT permission on the tables so I was not able to see them. Once I added the SELECT permission to the tables I was then able to see my data perfectly.


Why use synchronous_standby_names = 'barman_receive_wal' will cause database aberrant?

I see you've set up PostgreSQL to push WAL files to barman via archive_command - is barman also set up for WAL streaming? If that's not the case, there will be no synchronous standby with the name barman_receive_wal and hence any writes to the primary will not succeed, as PostgreSQL is waiting for a standby commit confirmation which never arrives.


SQL Server does indeed like RAM and will take everything it is allowed to. For that reason, an upper limit of RAM should be assigned, leaving sufficient for the OS and anything else running on that server.

It would seem your 2GB database would easily fit into your existing RAM, but again, it depends on what else is running on your server. In addition to your buffer pool, you have the plan cache, and other applications - SSIS, 3rd party apps?

The only way to tell if an upgrade would be of benefit would be to monitor your memory usage via perfmon, DMV or any other appropriate means to see if your server is using fully the existing memory.

I found this great Technet article (http://technet.microsoft.com/en-us/library/ms176018.aspx) that explains which counters to monitor. Is also explains the conditions where adding more memory is appropriate: -

  The Buffer Cache Hit Ratio counter is specific to an application.
  However, a rate of 90 percent or higher is desirable. Add more memory
  until the value is consistently greater than 90 percent. A value
  greater than 90 percent indicates that more than 90 percent of all
  requests for data were satisfied from the data cache.

  If the Total Server Memory (KB) counter is consistently high compared to the amount
  of physical memory in the computer, it may indicate that more memory
  is required.


Common database shared to other database

It is not a bad design at all, and it is not something new.

In MySQL any operation involving multiple tables can also involve multiple databases, so you won't have functional limitations.

The drawback is the complexity that you will need to add if you need to shard the databases across multiple servers/clusters. Because you will need a way to replicate the common_db, and nothing else, to all servers. But, even if you choice another model, you will hardly have an easy solution for this problem.


Is it possible to join MySQL 8.0 to a cluster of MySQL 5.7

It's not possible to make a Percona MySQL server 8.0 join a Percona XtraDB Cluster, regardless of version. The 8.0 server has to be Percona XtraDB, otherwise it won't have the necessary Galera libraries and bindings.


Random Assignment of Employees to Locations Oracle SQL

Use the random function to generate a number. "Number" your locations with slots equal to the number of positions they need. For example, if location A needs 15 and location B 16 then request a random number from 1 to 31 and:

1-15 go to A
16-31 go to B

Save that result to a table, and then pull all employees assigned per site.


Get correct row version based on combination of dates - query simplification

If I've understood what you are trying to do correctly then

WITH T AS
(
SELECT *,
       RN = RANK() OVER (PARTITION BY  iud, id, date ORDER BY insert_date DESC, update_date DESC)
FROM #temp
)
SELECT *
FROM T
WHERE RN=1;

May well perform better - especially if there is a covering index on (iud, id, date, insert_date DESC, update_date DESC)


What is the actual difference between innodb_autoinc_lock_mode 0,1,2

What replication method are you using?  Option 2 is fine with ROW based, but for STATEMENT replication your auto inc values on the slave are not guaranteed to be the same.

Option 2 also means that your auto inc values probably won't be consecutive, but maybe that does not matter to you.

Option 1 is the default because it is the safest method with STATEMENT based replication (which I think is the default).  It is more scalable than option 0 since it only does a table level auto inc lock for bulk inserts but it still provides consecutive auto inc values with no gaps.  Some folks rely upon that behavior.

Sounds like for your use case you're fine with the setting you have selected, as long as your replication settings are OK.

Hope that helps.


Do not have permission to use 'dbo' when creating a view

Your user should have the permission to modify the dbo schema. You can accomplish that by assigning the permission to a role the User is a member of:

So its like:

CREATE ROLE Limitedaccess;
GO

GRANT CREATE VIEW TO Limitedaccess;
GO

GRANT SELECT ON SCHEMA::dbo TO Limitedaccess;
GO

CREATE USER user WITHOUT LOGIN; --- this would be the user here
GO

EXEC sp_addrolemember 'Limitedaccess', 'User';
GO

Once permission is granted, re-run the previous CREATE VIEW statement. It will now succeed.

GRANT ALTER ON SCHEMA::dbo TO Limitedaccess;
GO


Should I replace my varchar primary key with an integer primary key?

You should have an autoincrement PRIMARY KEY. Since I do not know the storage engine of the table, I will try answer based on both.
InnoDB
In general, a smaller primary key is always better than a bigger one. The PRIMARY KEY for an InnoDB table is stored in the Clustered Index (known within InnoDB as the gen_clust_index). Since an InnoDB Page is 16K, smaller keys will make more keys fit inside an index page.
What should be noted is the fact that for each entry in a Secondary Index, there is Primary Key. Thus, not only will a smaller PRIMARY KEY benefit the table, but all non-unique Indexes will corresponding shrink as well.
MyISAM
Similar principles apply to MyISAM in terms of key sizes and indexes. Additionally, there is an added bonus in your particular case that is not often discussed when it comes to MyISAM.
MyISAM allows you to have an auto_increment key per column value. What do I mean?
Look at the table in your question with additional rows:
Id          |     Prod     |    Acc    | Val
---------------------------------------------
ABC-AB12_1  |    ABC-AB12  |   ABC1    |  1
ABC-AB12_2  |    ABC-AB12  |   DEF1    |  2
ABC-AB12_3  |    ABC-AB12  |   GHI1    |  A
DEF-AB12_1  |    DEF-AB12  |   ABC1    |  1
DEF-AB12_2  |    DEF-AB12  |   DEF1    |  2
DEF-AB12_3  |    DEF-AB12  |   GHI1    |  A
GHI-AB12_1  |    GHI-AB12  |   ABC1    |  1
GHI-AB12_2  |    GHI-AB12  |   DEF1    |  2
GHI-AB12_3  |    GHI-AB12  |   GHI1    |  A

You could replace the Id with an auoincrement value and end up with this:
Id |     Prod     |    Acc    | Val
----------------------------------------------
1  |    ABC-AB12  |   ABC1    |  1
2  |    ABC-AB12  |   DEF1    |  2
3  |    ABC-AB12  |   GHI1    |  A
4  |    DEF-AB12  |   ABC1    |  1
5  |    DEF-AB12  |   DEF1    |  2
6  |    DEF-AB12  |   GHI1    |  A
7  |    GHI-AB12  |   ABC1    |  1
8  |    GHI-AB12  |   DEF1    |  2
9  |    GHI-AB12  |   GHI1    |  A

This you would do if the Id looks likr this:
PRIMARY KEY (Id)

OK, great. Now here is the added bonus: If you make the PRIMARY KEY look like this:
PRIMARY KEY (Prod,Id)

the data can be stored like this:
Id |     Prod     |    Acc    | Val
----------------------------------------------
1  |    ABC-AB12  |   ABC1    |  1
2  |    ABC-AB12  |   DEF1    |  2
3  |    ABC-AB12  |   GHI1    |  A
1  |    DEF-AB12  |   ABC1    |  1
2  |    DEF-AB12  |   DEF1    |  2
3  |    DEF-AB12  |   GHI1    |  A
1  |    GHI-AB12  |   ABC1    |  1
2  |    GHI-AB12  |   DEF1    |  2
3  |    GHI-AB12  |   GHI1    |  A

How is that possible? Only the MyISAM Storage Engine Has This Mechanism Built In !!!
I have discussed this before:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin

One more thing: Why have PRIMARY KEY (Prod,Id) as a PRIMARY KEY? This would allow you to sequence each Product ID. Thus, you can look for sequence 3 of one product and sequence 3 of another product.
EPILOGUE
Whichever way you decide to go, using a smaller autoincrement PRIMARY KEY (4 bytes) make more sense for performance and diskspace than a larger PRIMARY KEY (more than 4 bytes).
Give it a Try !!!


PostgreSQL. Is using random tag in dollar quoting without escaping input safe?

Postgres doesn't cache ad-hoc SQL query plans; only if your host environment is using prepare will the query plan be cached. But if prepare is used, then parameterisation is also being used so dollar quoting won't be necessary.

It follows, then, if you're not using prepare that each query will be parsed. In that case, you're probably best off accepting the 2 * (32 + 2) = 68 byte penalty and use UUIDs inside dollar quoting.

You could write code to generate a random short string -- eg $a$ and test if that appears in the input, and loop making that string longer if it appears. But the cost of such code might exceed just generating a UUID and using that.


Why doesn't nodetool work on my Cassandra Cluster?

After some tinkering I figured it out; posting here to close the topic and to help others.

All the scripts in the conf/ directory work in concert and there are a number of environment variables you need to redirect if you install in a non-standard place (i.e. a tarball installation).

For each node in our cluster, we're (now) using:
  (1) cassandra-env.sh
  (2) cassandra-rackdc.properties
  (3) cassandra.yaml
  (4) jvm.options

All the other configuration files are pointed back to the conf/ directory from the tarball using an environment variable.

(2) and (3) set the properties of the node, such as its name, turning on gossip, etc:

(1) and (4) are used by the cassandra bash script when it launches -- the script goes looking for additional configuration files.  We've set it so that the cassandra.in.sh is found in the install area; but previously (1) and (4) were not being found, and these are required to activate JMX, port 7199, which nodetool uses to connect to the cluster.


Alternative query to avoid count(distinct)

DISTINCT is always a huge overhead.
You might try to move the aggregation into a Derived Table (of course this might only help if the optimizer is not smart enough to do that automatically)

select dc.Country,
  fvcd.Visitors
from
 (
   select
       fvcd.IdCountry,
       COUNT(DISTINCT fvcd.IdAppInstance) AS Visitors
   FROM
       dbo.Fact_VisitorsCountryDetails fvcd
   WHERE
       fvcd.IdDate BETWEEN @IdMinDate AND @IdMaxDate
       AND fvcd.IdPartnerid = @IdPartnerId
   GROUP BY
       fvcd.IdCountry
 ) fvcd
INNER JOIN dbo.Dim_Country dc ON dc.Id = fvcd.IdCountry
ORDER BY
    Visitors DESC;


You need to restore the last full backup, then all transaction logs in sequence, issuing a STOP AT for the final transaction log restore at a time prior to the loss of data. See this reference.


The default User is created during installation of the server instance. But you can query for a list of all current Users using this query:
SELECT usename AS role_name,
  CASE
     WHEN usesuper AND usecreatedb THEN
       CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN
        CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN
        CAST('create database' AS pg_catalog.text)
     ELSE
        CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

For more information please see this article.
You can change the Users and their passwords using ALTER USER, as discussed here. You can also disable the login for the default User as stated here.


How to use Common Table Expression properly - Example where it seems to be very slow in TSQL

To expand a bit more on Jonathan's comment, each time you reference a CTE, the query within the CTE will be called.

Each UNION you are using is creating a separate execution of the CTE, thereby executing the table value function each time as well. By putting the results into a temp table, you are only then reading the static data in the temp table for each UNION and avoid recalculating the data.

If you check the execution plan for each of the queries, you'll be able to see a visual confirmation of this fact.

In your case, it makes much more sense to use a temp table as illustrated by the vastly different execution times. I personally find non-recursive CTEs rarely offer much by way of performance and are usually more helpful for readability  and logical separation of query contents.


MongoDB - count current read/write operations

The serverStatus command contains operation counts, which report on database operations by type since the mongod instance last started.
If you need to find the number of operations over a specific time period, retrieve the serverStatus result periodically for comparison over time.


In my opinion you should use TRY/CATCH every time when you have update, insert or delete statements. This has saved me a lot of headaches. Check this -- 1


Can view adapt to implicitly reference user's default schema?

Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.

With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you hard-code) if a match is not found or if you are using Windows groups for example (which prior to SQL Server 2012 do not support default schema):

CREATE PROCEDURE dbo.whatever_procedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX), @s SYSNAME;

  SELECT @s = d.default_schema_name
    FROM sys.database_principals AS d
    LEFT OUTER JOIN sys.server_principals AS s
    ON d.[sid] = s.[sid]
    WHERE SUSER_SNAME() IN (d.name, s.name);

  SELECT @s = COALESCE(@s, N'dbo');

  SET @sql = N'SELECT x FROM ' + QUOTENAME(@s)
    + '.whatever_view_name;';

  EXEC sp_executesql @sql;
END
GO

You can test this:

USE tempdb;
GO
CREATE SCHEMA foo;
GO
CREATE LOGIN flubat WITH PASSWORD='flubat',CHECK_POLICY = OFF;
GO
CREATE USER flubat FROM LOGIN flubat WITH DEFAULT_SCHEMA = foo;
GO
GRANT SELECT ON SCHEMA::foo TO flubat;
GO
CREATE VIEW dbo.whatever_view_name AS SELECT x = 'dbo';
GO
CREATE VIEW foo.whatever_view_name AS SELECT x = 'foo';
GO
-- create the above procedure
GO
GRANT EXEC ON dbo.whatever_procedure TO flubat;
GO

And then execute it as yourself and also as flubat:

EXEC dbo.whatever_procedure;
GO
EXECUTE AS USER = N'flubat';
GO
EXEC dbo.whatever_procedure;
GO
REVERT;

Results:

x
----
dbo

x
----
foo

But like I said, this is potentially fragile, and I would lean toward either creating a view per schema, or changing the design altogether.


Keep local variable after rollback in mysql

It's not because of ROLLBACK. Value is reset because error is thrown later during INSERT. Handling the error inside procedure body (for instance, with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ) will give you what you want.  Alternatively, in your case you can just LEAVE inside IF @uid IS NOT NULL THEN block :

CREATE PROCEDURE sp_insert_values(IN uid INT, IN val_from INT, IN val_to INT, OUT retval VARCHAR(25) )
proc_body:
BEGIN
    SET retval = 'ERROR_UNKNOWN';
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        -- is there already record, registered to some user?
        SET @uid = (SELECT user_id FROM test WHERE value = @val);
        IF @uid IS NOT NULL THEN
            SET retval = 'ERROR_ANOTHER_USER';
            -- is it already registered to me?
            IF @uid = uid THEN
                SET retval = 'ERROR_ALREADY_HAS';
            END IF;

            ROLLBACK;
            LEAVE proc_body;
        END IF;
        INSERT INTO test (value, user_id) VALUES (@val, uid);
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    SET retval = 'OK';
    COMMIT;
END proc_body;


Total number of read/writes in a day in MySQL

If you have binary logging enabled, you can analyse your binlogs to identify types and number of statements executed.

mysqlbinlog your_binlog | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr

You might need --base64-output=DECODE_ROWS for row based binlog format.
You can analyze your day worth of binlogs or use --start/--end options to select time.

Check out this and this blog posts.


Instant File Initialisation on Windows Oracle Server

No, Oracle does not take advantage of that security policy and always zeroes out the files before writing to them.

I have no idea whether ASM behaves in a different way, but in the filesystem files are always initialized.


PostgreSQL insert into table (not origin) based on a condition on fields on different tables

You seem to be under the impression that some kind of automatic "row number" would exist. That is not the case. Unlike rows in a spreadsheed, tables in a relational database have no natural order.

This query should do the job, but it relies on the the contents of name_1 and name_2 to make the connection. If you rely on a row number, you have to add an actual column for that.

INSERT INTO table2 (id1, id2, score1, score2, comment_string)
SELECT t1.id1, t1.id2, t1.score1, t1.score2
      ,CASE WHEN t3.name_1 IS NULL
            AND  t4.name_2 IS NULL THEN 'removed_because ...'
       ELSE END AS comment_string
FROM   table_1 t1
LEFT   JOIN table_3 t3 USING (name_1)
LEFT   JOIN table_4 t4 USING (name_2)
ORDER  BY id1; -- undeclared in Q

Based on the assumption that table_3.name_1 and table_4.name_2 are unique. Else, the query could create a "proxy cross join", possibly multiplying rows, if there are several matches.
More about this caveat in this related answer on SO.


Restore a specific file group to a test environment without restoring the Primary file group (Error 3154)

Well, sad to say you can't do a piecemeal restore and bring a secondary filegroup online without the primary being online first. If there is a hack to get around this I am not aware of it.

If your primary filegroup is at 1TB and you are not using it, why not just do a one time shrink to a more manageable size? This is not uncommon to do in production environments where a large amount of data may have been purged and you want to reclaim the space. As well, if you have no ability to test your backups your are in a far worse situation.


MySQL: execute an "UPDATE" only if an specific row exists

The IF statement can only be used inside Stored Routines.  This explains the syntax error.

Are you trying to change all rows in the table if a certain row exists?

It's a bit clumsy, but here is a purely SQL way:

SET @x := EXISTS ( SELECT * FROM gallery_image WHERE position = 8 );
UPDATE gallery_image
    SET position = - position
    WHERE @x;

(It is not normal to change all rows of a table.  What is the application doing?  Can it be achieved without such a massive query?)

If you only want to change the one row with 8:

UPDATE gallery_image
    SET position = - position
    WHERE position = 8;


MySQL: Find Extra Days From List Of Date Ranges

You could use a Dates table to help with this.

This table will hold a single row-per-day from 2015-01-01 into the future:

CREATE TABLE Days
(
  Day date NOT NULL
);

INSERT INTO Days (Day)
SELECT ADDDATE('2015-01-01', INTERVAL (n1.Num + (n2.Num * 10) + (n3.Num * 100) + (n4.Num * 1000)) DAY)
FROM (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n1
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n2
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n3
CROSS JOIN (
  SELECT 0 AS Num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n4;

This is the list of ranges you have defined in your question:

CREATE TABLE RangeList
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO RangeList (FromDate, ToDate)
VALUES ('2015-02-12', '2015-02-16')
  , ('2015-02-17', '2015-02-18')
  , ('2015-02-20', '2015-02-20');

This is the range of days you're interested in checking:

CREATE TABLE DaysNotInRange
(
  FromDate date NOT NULL
  , ToDate date NOT NULL
);

INSERT INTO DaysNotInRange (FromDate, ToDate)
VALUES ('2015-02-11', '2015-02-19');

This will show you the missing days:

SELECT Days.Day
FROM Days
    INNER JOIN DaysNotInRange ON Days.Day >= DaysNotInRange.FromDate
        AND Days.Day <= DaysNotInRange.ToDate
WHERE Days.Day NOT IN (
  SELECT Days.Day
  FROM Days
      INNER JOIN RangeList ON Days.Day >= RangeList.FromDate
        AND Days.Day <= RangeList.ToDate
  )
ORDER BY Day;

The results:

╔════════════╗
║    Day     ║
╠════════════╣
║ 2015-02-11 ║
║ 2015-02-19 ║
╚════════════╝


The problem is:

Solution:

Result:

Thanks!


Single Slave with Multiple Masters. Does this pattern exist in MongoDB?

I don't think so Single Slave with Multipe Master Possible. As per MongoDB BOL Here Master-slave replication preceded replica sets and made it possible to have a large number of non-master (i.e. slave) nodes, as well as to restrict replicated operations to only a single database; however, master-slave replication provides less redundancy and does not automate failover.

  WARNING
  Deprecated since version 3.6: MongoDB 3.6 deprecates the use of
  master-slave replication.

  Master-slave replication has been deprecated for components of sharded
  clusters since MongoDB 3.2.

Replica sets replace master-slave replication for most use cases. If possible, use replica sets rather than master-slave replication for all new production deployments. This documentation remains to support legacy deployments and for archival purposes only.

For further your ref Master Slave Replication


SQL Server Transactional Replication snapshot delivery loop

So, by some reasons it was happened because triggers and non-clustered indexes were included in replication. After they were excluded the snapshot delivery didn't loop anymore. And after snapshot was delivered, I just created all missing indexes and triggers manually on the subscriber as a workaround.


Advanced threat detection vs IP restriction

Even from your own internal network you can receive  anomalous database activities. Your database could face internal and external threats every day. Threat detection can detect SQL injection attacks and anomalous database access patterns coming from your own internal networks.


How to improve performance for read only innodb MySQL Database

Can't optimize it without some serious surgery.  Think of it as a "bounding box" as discussed in http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

It discusses 5 ways of doing "find nearest".  You are on the second-worst, and probably do not have the best indexes for that.  Then it moves on to 3 faster ways to run the query, but they require non-trivial effort.  But the speedup is quite significant.

As for concurrency, those algorithms provide performance by decreasing the I/O and CPU.  This allows for more concurrency and even lower CPU usage.


Check Constraints on Decimals In SQL Server 2012

You could use TRY_CAST to cast the column to a decimal format before comparing.

weekReviewWk10='A' OR
TRY_CAST(weekReviewWk10 AS decimal(3, 1)) BETWEEN 0.0 AND 25.0

If the cast fails, the result of TRY_CAST will be NULL and the comparison is false, but it won't crash. TRY_CAST requires SQL Server 2012 or newer.

It's worth mentioning that you're potentially opening up a pit of bad things when you allow text values in a numeric column, but judging from the question, you already know that. :)


Insert Into table Exec SP with bad performance

INSERT...EXEC might prohibit parallelism, but I highly doubt that is the main issue here. The problem is that INSERT...EXEC works differently than INSERT...SELECT. When inserting the results of a query (i.e. a SELECT statement or OUTPUT clause) into a table, the results are streamed into the table. If you are watching the row_count and used_page_count from sys.dm_db_partition_stats, you will seem them increase immediately as the INSERT...SELECT starts.

But, when inserting the results of an EXEC (typically a Stored Procedure but could be Dynamic SQL), the sub-process / Stored Procedure needs to fully complete before anything is inserted into the table (yes, I just tested this). If you are watching the row_count and used_page_count from sys.dm_db_partition_stats, you will seem them stay at 0 (or whatever they started at) for a looooong time after the INSERT...EXEC starts. This is not a recipe for success when the Stored Procedure returns millions of rows as those results need to be stored somewhere prior to them being inserted into the table.


How much space is needed to reclaim space from a table in INNODB

DISKSPACE FOR EVERYTHING INNODB

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace
FROM information_schema.tables
WHERE engine='InnoDB';

DISKSPACE FOR DATABASE mydb BY TABLE

SELECT
    IFNULL(tbl,'Total') table_name,
    FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size
FROM
(
    SELECT table_name tbl,SUM(data_length+index_length)table_bytes
    FROM information_schema.tables WHERE table_schema='mydb
    GROUP BY table_name WITH ROLLUP
) A ORDER BY ISNULL(tbl) DESC,table_bytes;

DISKSPACE FOR INDIVIDUAL TABLE (mydb.mytable)

SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable';

These queries will tell you how much space you need for any table, database, and engine.

SUGGESTION #1

If you would like to simulate OPTIMIZE TABLE just to see diskspace usage, you run the following steps on mydb.mytable

use mydb
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new SELECT * FROM mytable;
ANALYZE TABLE mytable_new;
SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) Table_Diskspace
FROM information_schema.tables
WHERE table_schema='mydb;
AND table_name='mytable_new';
#
# Stop Here. If you are happy with the size, do the last two commands
# If you prefer, drop table mytable_new and do do the online schema change
#
RENAME TABLE mytable TO mytable_old,mytable_newTO mytable;
DROP TABLE mytable_old;

Doing this can give an idea who big the .ibd file will be after running OPTIMIZE TABLE.

SUGGESTION #2

Please do not convert the table to MyISAM. It will be building two files (.MYD and .MYI). Then, you try to converting to InnoDB again. It is essentially the same as doing #SUGGESTION #1. MyISAM is also a little more prone to corruption with no recovery processes in place.

EPILOGUE

By all means, you can proceed with pt-online-schema-change if your data is live. My answer simply gives you queries to estimate sizes of the tables and approximate temp table sizes for OPTIMIZE TABLE.

If you are doing this during off hours, try my post Recover the disk space after deleting rows from table

GIVE IT A TRY !!!


Mongodb ReplicaSet Changing Config Format

After a few days of looking at this and testing different things, I found that there was a missing configuration value

  systemLog.destination: file

I discovered this by running the following command

  sudo mongod -f /etc/mongod.conf

instead of doing basic service startup commands

  (i.e. sudo service mongod start...)

It showed the following error:

  BadValue: Can only use systemLog.path if systemLog.destination is to a
  file

A couple of other things as you troubleshoot. Make sure you maintain the mongo file and folder permissions. I was deleting log and config files as I was troubleshooting and did not realize it was not allowing the engine to start a few times. I ran the following commands to correct the permissions

sudo chown -R mongodb:mongodb /var/log/mongodb
sudo chown -R mongodb:mongodb /var/lib/mongodb

I also went through the process of removing one of the secondaries from the replicaSet

rs.remove("<server>:27017")

and

rs.reconfig()

Then logging directly to the old secondary that was a stand alone made sure I can access the database as a standalone.

I then readded the system during the changes of the config

rs.add({host: "<servername>"})

Here is the final config file:

net:
    bindIp: [0.0.0.0,<host_name>,127.0.0.1]
    port: 27017
    http:
        enabled: true
        RESTInterfaceEnabled: true
storage:
    engine: wiredTiger
    dbPath: "/var/lib/mongodb"
    journal:
        enabled: false
processManagement:
    fork: false
security:
    keyFile: "/etc/mongod.key"
    authorization: "enabled"
systemLog:
    logAppend: true
    destination: "file"
    path: "/var/log/mongodb/mongod.log"

replication:
    replSetName: "rs0"

I hope this helps someone


2016 Read Only Secondaries With Different Indexes

I can say with a very high level of confidence that there is no plan to support alternate indexes on AG secondaries of any kind in SQL Server 2016.

Perhaps your friend is confusing indexes and statistics - you can create alternate statistics on read-only secondaries (details here).


Get postgresql exact log file name with sql

There is now a pg_current_logfile() function returning this information:

  Primary log file name, or log in the requested format, currently in
  use by the logging collector.

but it's a new feature of PostgreSQL 10, which you probably don't run since it hasn't yet got out as a General Availability release (it's still Release Candidate at the moment).

With previous versions, you're pretty much on your own to figure out the current name, either by interpreting log_filename, or by finding the latest modified, for instance with a combination of pg_ls_dir and pg_stat_file:

SELECT file, -- should be the latest logfile
  (pg_stat_file(current_setting('log_directory')||'/'||file)).modification
  FROM  pg_ls_dir(current_setting('log_directory')||'/') as list(file)
  ORDER BY 2 DESC
  LIMIT 1;

This can fail if the server's clock goes backward or if non-log files are dumped into the same directory as the log files.


Let's say on the new server you want datadir to be D:\MySQLData

Simply copy everything and all subfolders from old server's C:\Program Files\MySQL\MySQL Server 5.5\data to the new server's D:\MySQLData. You said you are using XAMPP. RUn this query SHOW VARIABLES LIKE 'datadir';. Copy that folder to D:\MySQLData.

Then add this

[mysqld]
datadir="D:/MySQLData"

in the new server's my.ini

This will work seamlessly if the new server has the same version of MySQL as the old.

If the new server has the next higher major release (5.1 -> 5.5,5.5 - > 5.6) you will then have to run mysql_upgrade --upgrade-system-tables. Make sure you keep a backup.

If you are using InnoDB (in whole or in part), get all the InnoDB settings from the old my.ini over the new server's my.ini.

Give it a Try !!!


You are correct - if you have two datasets with identical columns then UNION is the correct way to append the second dataset onto the first. Moreover I would suggest you use UNION ALL. UNION by itself guarantees unique values in the output. Usually this requires a sort and further processing to detect and eliminate duplicates. In your case I do not think you have duplicates so UNION ALL would be appropriate.

The real problem seems to be the sort order. You want the combined dataset to be sorted by Record but with 0 as the last item. The rows exist in two blocks: the zero rows and the other rows. If we assign a "block number" to each they can be sorted accordingly. Clearly this cannot be achieved by changing Record itself so another column must be introduced. This new artificial column need not be returned form the query.

You don't mention which DBMS you use. I have SQL Server to hand; I'll use it. For future questions please do not include screen shots of the data. I makes it much too hard to reproduce your situation. Instead include in the quesiton the CREATEs and INSERTs required to reproduce the test cases and also what queries you've tried and why the don't work. See mcve. I've written the minimum to demonstrate the proposed solution.

drop table if exists Table1;
drop table if exists Table2;
go

create table Table1(Name varchar(9), Record int); -- other columns omitted for simplicity
create table Table2(Name varchar(9), Record int);
go

insert Table1 values ('Joy', 1), ('Joy', 2);
insert Table2 values ('Joy', 0);
go

-- I've written this as a Common Table Expression (CTE). A sub-query would work just as well
;with Data as
(
select
    Name, Record, BlockNumber = 2   -- BlockNumber for Table2 is greater than that for Table1
from Table2  -- the order of queries within the CTE does not affect the output

union all

select
    Name, Record, BlockNumber = 1
from Table1
)
select
    Name, Record -- note that BlockNumber is not returned
from Data
order by
    Name,           -- the leading column of the sort so one person's rows all appear together
    BlockNumber,    -- will put Table2's row after Table1's.
    Record;         -- since all Table1's rows are in the same block they'll sort by Record


which design better when use foreign key instead of a string to store a list of id

As ypercube and marc_s pointed out, it's best to have a 2-column "mapping" table to store which questions are used by which general exams.

This approach provides the following benefits:

Foreign Keys can then be used to ensure data integrity
Queries are much easier to write (eg: find all general exams that use question 17)
Extending the database schema at a later date (to support new features) will be much easier
The schema will be easier to understand by new/other developers/DBAs since it confirms to best practice


Large Transaction Log Backups in Service Broker-enabled system

You could use a different database for your SSB load. It seems like your SSB data is transitory in nature. That would allow you to either switch to SIMPLE recovery model for that DB or throw away the log backups in case you need FULL for some other reason like mirroring.

I don't think you'll be able to reduce log usage for SSB operations. I'm interested to see other answers but usually log usage is not tunable for OLTP-style transactions. They are always fully logged. Minimal logging only applies to special cases.


Database table data transfer between different servers using bcp.exe and mysqldump

No you can't take the output of mysqldump and use it with bcp.  Mysqldump creates a SQL Script which can then be run.  If the schema is the same you can probably just run the outputted script using sqlcmd.


SymmetricDS Setup - SQL Server 2008 to MySQL

It seem that the official documents are not clear enough to help you.
The steps are below:

You need 1( or 2) intermediate node(s)(Linux or Windows) which have connection between Source and Destination databases and install symmetricDs on it.

You need to build the schema(database structure) on the destination(Mysql) node by symmetricds script or by insert yourself.

You need to create 2 users on 2 nodes who have access at read and write to database

You need to create the engine file for source and destination in the intermediate(s) node similar like this:

For mssql.properties
  engine.name=MSSQL
  db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://[sqlserver:port]/[DatabaseName];useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880

 db.user=[username]
 db.password=[password]

registration.url=
sync.url=http://localhost:8080/sync/MSSQL

group.id=source
external.id=0000000

job.purge.period.time.ms=7200000

job.routing.period.time.ms=5000

 job.push.period.time.ms=10000

 job.pull.period.time.ms=10000

 initial.load.create.first=true

For mysql.properties
engine.name=mysql

db.driver=com.mysql.jdbc.Driver

db.url=jdbc:mysql://<IP>/databasename?tinyInt1isBit=false

registration.url=http://localhost:8080/sync/MSSQL

group.id=destination
external.id=0000002

job.routing.period.time.ms=5000

job.push.period.time.ms=10000

job.pull.period.time.ms=10000

jobs.synchronized.enable=true

On  the intermediate  Node, You will need to CREATE SYMMETRICDS CONFIGURATION TABLES with following bin\symadmin --engine source create-sym-tables
On  the intermediate  Node, You need  sql file containing the initial table configurations to insert to source engine node with at least 4 parameter below to make trigger and synchronization:

insert into sym_node_group (node_group_id, description) values ('MSSQL', 'SQL Servers Group');

insert into sym_node_group (node_group_id, description) values ('MYSQL', 'MYSQL server');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('MSSQL', 'MYSQL', 'P');

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('MSSQL_2_MYSQL', 'MSSQL', 'MYSQL', 'default',current_timestamp, current_timestamp);

insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values ('main_channel', 1, 100000, 1, 'channel for replication');

insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table1','table1','main_channel',current_timestamp,current_timestamp);

insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table1','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);

insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table2','table2','main_channel',current_timestamp,current_timestamp);

insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table2','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);

And the last step is run the script for source engine node and destination engine node.
If you want the connection is only one-way just


Formatting Timestamp in Default Constraint

Can you format a TIMESTAMP WITH TIME ZONE column in a CREATE TABLE default constraint in Postgres v10.x?

No because that doesn't make any sense. Timestamps are stored as timestamps. It's a special type. You format things for display. Any point in time is stored the same way regardless. Formatting time should arguable not be done in the database at all.

Think of it like this, you may need Roman Numeral representation for some columns, but even if you do the database will stored integers in their binary representation.

Now if you want to change the clients default output formatting for timestamps, you can certainly do that.  From the docs

  The date/time style can be selected by the user using the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.


Why does adding a column to a MySQL table take incredibly long?

Adding a column in MySQL 5.6 requires the full table to be rebuilt.  This becomes online in MySQL 8.0 though, provided the column is at the end of the table! The MySQL manual covers this under 14.13.1 Online DDL Operations (search in page for "Adding a column"):

  Rebuilds Table?: Yes

Adding secondary indexes (i.e. not the primary key) should always be delayed until the last step so they can be sorted and optimally created. In the manual page I linked to, any operation that says In Place=No it will make sense to drop indexes first as well.


Improving the performance of STIntersects

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item.

Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first.  Matches against these simplified polygons could then be run through the primary filter to get the final results.

1) Add a new geography and geometry column to the [dbo].[T_POLYGON] table:

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeom geometry;
ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog geography;

2) Create the bounding box polygons (this involves an initial conversion to geometry to take advantage of STEnvelope()):

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeom = geometry::STGeomFromWKB(
    COORD.STAsBinary(), COORD.STSrid).STEnvelope();

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeog = geography::STGeomFromWKB(
    SimplePolysGeom.STAsBinary(), SimplePolysGeom.STSrid);

3) Create a spatial index on the simplified geography column

4) Get the intersections against this simplified geography column, then filter again on the matching geography data types. Roughly, something like this:

;WITH cte AS
(
   SELECT pinID, polygonID FROM T_PIN INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.SimplePolysGeog ) = 1
)
SELECT COUNT(*)
FROM T_PIN
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1
    AND T_PIN.pinID IN (SELECT pinID FROM cte)
    AND T_POLYGON.polygonID IN (SELECT polygonID FROM cte)

EDIT: you can replace (1) and (2) with this computed, persisted column. credit to Paul White for the suggestion.

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS  ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([COORD].[STAsBinary](),[COORD].[STSrid]).STEnvelope().STAsBinary(),(4326))) PERSISTED