What are alternatives to Replication to distibute data (Microsoft SQL Server)

The reality is that SQL Server does not have a read-write scalability story in the same way that Oracle does in RAC so whatever you choose, you will always needing to consider that data-sets in one place or the other could be slightly behind. Availability Groups are probably the easiest solution to manage but then the only thing you are scaling out is readable replicas. Merge or Peer-peer replication probably provides much of what you need out of the box, but can be a bit of a headache especially in the wrong hands. You also need to think about avoiding conflicts.

I wouldn't attempt to roll out something yourself (i.e through SB or CDC) because this would ultimately become an administrative nightmare to manage in years to come and probably not provide you with richer functionality than is already provided.


Why does mySQL automatically add display length to numeric values when adding zerofill property

In the past, INT(5) was identical to INT.  The number was useful only when ZEROFILL was also specified.
In the future, ZEROFILL and the number will go away.  This incompatible change is predicted for some time in 8.0.xx.  You are probably seeing some step toward it.
Zerofill is extremely rarely used.  US Zipcodes stored in MEDIUMINT(5) UNSIGNED ZEROFILL is the only case I can think of off-hand.
When Zerofill goes away, there are workarounds for the rare cases where you really need it.
The number confuses a lot of people.  It takes time for newbies to understand that it is normally of no use.
Although DESCRIBE and information_schema.TABLES are tempting for displaying the schema for a table, please get in the habit of using SHOW CREATE TABLE; it rolls the useful information together in a canonical form.


How To Store EAV Data When It Has Nested Objects/Entities

Proposed data structure is a tree. You have the root node having leaves and trunks attached to it. Each trunk also have a leaves/trunks. Here leaves are the attributes/values while trunks are the elements of the tree. You need at least two tables for the Tree and EAV stored separately:

Tree                EAV
+----+--------+     +----+---------+------+-------+
| id | parent |     | id | tree_id | attr | value |
+----+--------+     +----+---------+------+-------+

Here Tree stores the structure/subordinance while EAV stores the items within the structure. An exact implementation is not so complicated with modern versions of MySQL/MariaDB having recursive CTEs.


Azure SQL Database patch and update doubts

Is it possible for the customer to reverse a patch, or test it and authorize it before being applied?

No.  It’s part of the service definition that updates won’t break your application.  Any potentially breaking changes, including query optimizer enhancements won’t be active until you opt in by changing your Database Compatibility Level.

See
https://docs.microsoft./en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017


Database migration Oracle to PostgreSQL, domain indexes

INDEXTYPE IS CTXSYS.CTXCAT is Oracle's way of defining a "full text index" on a column -- as opposed to an ordinary B-tree.

The good news is that Postgres also has full text indexes, and these are documented extensively at https://www.postgresql.org/docs/current/static/textsearch.html

The basic syntax for creating a similar index in Postgres is

create index CTX_IDX_UFA_EMAIL
on USR_ACCOUNT
using gin (to_tsvector('english', EMAIL));

The bad news for you is that substituting this type of index in the database will mean you also need to change the queries on this column/table. This may or may not also have implications for the user interface -- it could be that end users are currently able to enter search terms that are understood by the Oracle full text index searching implementation.


enum vs boolean in case of multiple yes/no fields


Define "combine".  My guess is that you simply want to OR the predicates together

SELECT a, b, c
  FROM my_table
 WHERE (dt_column < sysdate+1 and c IS NULL)
    OR (dt_column < sysdate and b IS NULL)
 ORDER BY id

You may, however, want to do something else.  Perhaps you want to UNION ALL the two result sets, for example, it order to get duplicate rows.


SQL Server 2016 - ColumnStore Clustered Index vs. Non-ColumnStore Clustered Index

Clustered Columnstore Indexes are designed to be used primarily with Data Warehouse, OLAP or reporting solutions.  They are not great at WHERE x = y type queries; they are excellent at WHERE x < 1 and x > 1000000 type queries where the query processor can perform a range-scan on the CCI.

Assuming your workload is OLTP-critical, I'd recommend, without a ton of work on my part, to create the table with a standard B-Tree clustered index, and consider a well-designed nonclustered columnstore index that will help with your point #2.

If the OLTP component of the workload is small, or not very critical, but the reporting component is critical, you could possibly create the table as a clustered columnstore index, then add a number of starndard b-tree nonclustered indexes to help support the OLTP component.


How does to_tsquery and to_tsvector decide which characters to omit?

They are alphanumeric in some alphabet, just not the one you want to use.

You can alter your text search configuration to drop the types of tokens you do not want.  You should probably copy and alter, not alter in place.

CREATE TEXT SEARCH CONFIGURATION ascii_only ( COPY = pg_catalog.english );
alter text search configuration ascii_only drop mapping for word;
select * from to_tsvector('ascii_only', '平仮名ひらがな dogs');

Yields:

"'dog':1"

This is just a minimal example, you will probably need to drop the mappings for other token types as well, chosen from this list.  You might not be able to get exactly what you want, for example there is no asciinumword, so there is no way keep 'dog1' but reject '平仮名ひらがな1'.  So in that case you would have to implement your own non-default parser, or use functions (hopefully making them inefficient is optional--we might be able to help with that).

You can use ts_debug to see what is going on.  A word that is entirely ASCII letters [a-zA-Z] gets tokenized to 'asciiword', and not tokenized to 'word'.  So a better name for 'word' might be 'word excluding asciiword'.  So by dropping the mapping for 'word', you are excluding words other than asciiwords.  This means dropping not only words that are entirely foreign, but also words with accents.  It sounded like you were OK with either dropping the accent, or dropping the entire word, but maybe not.

But something with a mixture of letters and numbers is not tokenized to either 'word' or 'asciwword', but rather to 'numword', so would not be excluded unless you also dropped 'numword' in addition to 'word'.

If these don't do what you want and you need to use a function, I don't know what you want the function to do precisely, so it is hard to give an example.  If you go that route and implement the function that does what you want and find it not efficient enough, you can start a new post on that topic.


Veeam will give you full backups of your SQL Server VM which are not that useful when all you want to do is restore a database to see what a particular value was on a certain date. You don't want to de restoring an entire server to get at a database.

The replication gives a level of High Availability but it wouldn't save you from someone running a stupid update statement that wiped a load of valuable data.

You really need to be making regular full backups (nightly is normal) with log backups during the day depending on your RTO and RPO. If you don't know what your RTO and RPO is then you need to research these terms and sit down with management to make a decision on this. It's this decision that will determine your backup strategy and the recovery mode you run each database in.

There are so many reasons to list why you should be making regular SQL backups and retaining them but here are a few

You need to be retaining backups so you have the ability to look back and see what state your data was on a certain date. This can save your job.

Data corruption can hide in your data for a long time before it becomes an issue. If it does you will want to be able to go back before the point the corruption occurred.

It's your job to make sure your data is safe.


Why is it important for every table to have a primary key?

The big benefit is that the key is very narrow and naturally in order. Every non-clustered index you add by definition also contains the primary key in order to do look ups. So even though it doesn't appear to have much benefit, using an identity column as a PK would still be helpful.

Also, if you ever expand on to this and add other tables that need to reference this table, you just need the one column for the foreign key.


it looks like like the log records backed up to the nul device isn't being registered on the secondary node of the cluster.

Not to me.  The First Log Backup on the secondary (after the full) starts with the last LSN covered by the backup to 'nul' taken on the primary.

But stepping back to "We had a request to minimize the amount of log backups": why?  And why are you breaking the log chain with the backup to 'nul'?

  Is this how it is supposed to work?

You are supposed to maintain an unbroken chain of log backups in addition to occasional Full backups.  The log records included in a Full backup will also be present in the next Log backup, as Full backups never break the log chain.  This allows you to loose a Full backup, and restore from a previous Full plus all the log backups.


Payment methods conceptual and logical model

In a transactional system you will want to record the values that applied to the transaction at the time it happened.

It is a mistake to try to use relationships to connect a transaction to a date-depenendent lookup data.  The reason for this is that changes to the date-dependent data that happen after the transaction will restate what should have happened to the transaction.  This will cause confusion and errors.

The way to handle this type of situation is to denormalize the actual value applied to the payment transaction.  Your model doesn't reflect this, but if you had named categories for your discounts and timespan rates, you could keep a relationship from the transaction to the category name while still denormalizing the actual (time sensitive) rate/percent.

EDIT: What should the logical model look like?

In your logical model, the issue is that you need multiple discount (or price) rates because they change over time - or just because there may be multiple rules, for example one price for regular customers and one for special customers, etc.

What is missing is the notion of a rate table.  This is like a header which then has rate details attached to it.  What you have in your model now is really just the rate detail.  You need to insert the rate table header in between the transaction and the rate detail.  In your logical model, the relationship is between the transaction and the rate table header.  This is OK because the rate table is more stable than the details which change over time.

In your physical model you still need to denormalize make a point in time snapshot of the actual rate used into your transaction because that is the only reliable way to make sure that editing the rates won't restate history.


It is possible to extract Extented Properties using SQL Developer Data Modeler?

I'm not sure I understand what your use case is, but you can retrieve them by querying the sys.extended_properties system view.

SSMS doesn't provide a helpful way to view extended properties outside of the individual dialogue window for a single object anyways, so this is probably your best route.

Neither tool will be able to build you a data dictionary necessarily, but you can generate a simple one by querying from any tool.


multiple table inner join didn't get expected values

Suppose that you want sem3 then query will be

Case 1#

Select stureg.regno,stureg.pro,stureg.name,stureg.start_day,
sem.regno,sem.curSemmester,sem.name
from student_register as stureg left outer join semester as sem on stureg.regno=sem.regno
where stureg.pro REGEXP '^H' and sem.CurSemmester='3'

group by regno

order by regno
;

Case 2#

Select stureg.regno,stureg.pro,stureg.name,stureg.start_day,
sem.regno,sem.curSemmester,sem.name
from student_register as stureg left outer join semester as sem on stureg.regno=sem.regno
where stureg.pro like '%H%' and sem.CurSemmester='3'

group by regno

order by regno
;

Give it Try.


SSIS - Best way to insert large number of rows

Have you considered using a Data Flow Task with a Script Component acting as a source? You could just do an AddRow for each row you consume out of the file, and let SSIS handle the batches.


What are the differences between SQL I/O Sim and SQL IO tools?

SQLIO is a generic tool for testing your disk subsystem. It allows you to specify whether to test random/sequential, data block sizes, queue depth, threading, etc.

SQLIOSim is a tool that tries to emulate the patterns that SQL Server would expose to your system.

Usually I'll use SQLIO to test the subsystem when benchmarking for raw specs. Once satisfied with my SQLIO results I'll run SQLIOSim to get a real world run through of the disk subsystem.

You might want to see Brent Ozars tutorial on SQLIO, which also mentions SQLIOSim:
http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/


Which IN-LIST values are not in the table

You need to create a list of values which is unfortunately quite cumbersome in Oracle (due to the missing values() clause).

But the following will work:

with id_list as (
  select 12345 as id from dual union all
  select 98765 from dual union all
  select 65436 from dual union all
  select 81764 from dual
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = il.id
where i.item_code is null;

But given the nature of the query this is a bit complicated to parameterize though. The best solution is probably to create a (global) temporary table, then put the list of IDs you receive into that table and use that instead of the common table expression (the with (...) part)

Edit

There is a (cumbersome) way to "explode" a comma separated list of values, which might be "easier" in this case:

with base_value(id_string) as (
  select '12345,98765,65436,81764' from dual
), id_list(id) as (
  select regexp_substr (id_string, '[^,]+', 1, level)
  from base_value
  connect by regexp_substr (id_string, '[^,]+', 1, level) is not null
)
select il.id as missing_id
from id_list il
  left join item i on i.item_code = to_number(il.id)
where i.item_code is null;

But that might quickly hit Oracle's limit of 4000 bytes for a string literal....


Transfering a table with millions of rows in a fast efficient way without blocking

Are there any solutions more efficient than simple INSERT INTO or
  exporting and importing files to move millions of rows from a table
  with 3 columns to a similar table in another database,

Your best bet for this is going to be SSIS.

  without blocking the destination database

Inserting into one table won't block the database, but it will cause blocking on the table to one degree or another. The length and severity of the blocking will largely be determined by the number of indexes and how well they suit your queries.

If you want to reduce blocking, insert data in smaller chunks. I usually go for around 1000 rows at a time to avoid lock escalation.

Another way to reduce blocking is to use an optimistic isolation level on queries that read data from the able.

  and not losing any of the records in transactions that can be rolled
  back in case of failure?

This is another reason why smaller batches is a good idea. It will make it easier for you to figure out the offending row, retry the transaction, etc.

There are also some neat trick shots in this post, by Michael Swart: Modifying Tables Online – Part 1: Migration Strategy


From the documentation (GRANT Schema Permissions):

GRANT SELECT, DELETE -- , etc.
   ON SCHEMA::schema_name
   TO user_name;

For certain permissions they need to be granted at both the database level and the schema level. For example, you can give a user CREATE TABLE permissions at the database level, but that doesn't just allow them to create tables in any schema. Here is an example you can work through:

-- create a server-level SQL auth login
CREATE LOGIN DouglasExample WITH PASSWORD = 'x',
  CHECK_POLICY = OFF;

-- create an empty database and add a schema
CREATE DATABASE Douglas;
GO
USE Douglas;
GO
CREATE SCHEMA foo;
GO

-- create a user linked to the login
CREATE USER DouglasExample FROM LOGIN DouglasExample;
GO

-- grant the user the ability to create tables
GRANT CREATE TABLE TO DouglasExample;
GO

-- grant them explicit permissions on the schema only
GRANT SELECT, INSERT, DELETE, ALTER, EXECUTE, CONTROL
  ON SCHEMA::foo TO DouglasExample;
GO

-- now try to create tables in foo and in dbo
EXECUTE AS USER = N'DouglasExample';
GO
CREATE TABLE foo.what(id INT); -- succeeds
GO
CREATE TABLE dbo.who(id INT); -- fails, not in schema foo
-- CREATE TABLE who(id INT); would also fail with same error
-- unless DouglasExample had foo as their default_schema
GO
REVERT;
GO

The attempt in dbo fails with:

  Msg 2760, Level 16, State 1
  The specified schema name "dbo" either does not exist or you do not have permission to use it.

-- clean up:
USE master;
GO
DROP DATABASE Douglas;
GO
DROP LOGIN DouglasExample;

As a side note, please observe how important it is to always explicitly state the schema name when creating/referencing all objects.


SQLAnywhere: Rename Foreign Key or Primary Key

You don't say which version of SQL Anywhere you are using, but in 16 I think you can use ALTER INDEX to do what you want. From the help:

  ALTER INDEX statement

  Renames an index, primary key, or foreign key, or changes the clustered nature of an index.

  Syntax

  ALTER { INDEX index-name
   | [ INDEX ] FOREIGN KEY role-name
  | [ INDEX ] PRIMARY KEY }
  ON [ owner.]object-name { REBUILD | rename-clause | cluster-clause }

  object-name : table-name | materialized-view-name
  rename-clause : RENAME { AS | TO } new-index-name
  cluster-clause : CLUSTERED | NONCLUSTERED

  Parameters

  rename-clause   Specify the new name for the index, primary key, or foreign key.

  When you rename the underlying index for a foreign or primary key, the corresponding RI constraint name for the index is not changed. However, the foreign key role name, if applicable, is the same as the index name and is changed. Use the ALTER TABLE statement to rename the RI constraint name, if necessary.

  cluster-clause   Specify whether the index should be changed to CLUSTERED or NONCLUSTERED. Only one index on a table can be clustered.

  REBUILD clause   Use this clause to rebuild an index, instead of dropping and recreating it.

  Remarks

  The ALTER INDEX statement carries out two tasks:

  It can be used to rename an index, primary key, or foreign key.

  It can be used to change an index type from nonclustered to clustered, or vice versa.

  The ALTER INDEX statement can be used to change the clustering specification of the index, but does not reorganize the data. As well, only one index per table or materialized view can be clustered.

  ALTER INDEX cannot be used to change an index on a local temporary table. An attempt to do so results in an Index not found error.

  This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots.

Note what it says about using the ALTER TABLE statement to rename the constraint name if needed. If you need more help try the dedicated SQL Anywhere forum at http://sqlanywhere-forum.sap.com/


How can I make MySQL client read password from mylogin.cnf?

use following command to set login path first

  mysql_config_editor set --login-path=lgpath --user=root -p
  password

and then use below command to login mysql

  mysql --login-path=lgpath

Help about mysql login path options can bee seen by

  mysql_config_editor set --help


search for something in the first 1000 records in the table

You can't do something like a 'first 1000' without an order, since each query could give you a different order if you don't specify it.
So, let's try this way:

select * from (select * from table order by PUTYOURORDERFIELDHERE LIMIT 1000) where name
like '%john%' limit 5;


Using an index for both uniqueness and fast lookup?

You only need a single index, the unique one. Queries to lookup by username will use the unique index. There's no advantage to having a second index on the same column marked as non-unique.

You can verify that this by performing an EXPLAIN on the query. Connect to you database and compare the results of the following before and after you drop the index and confirm that they are the same:

EXPLAIN VERBOSE
SELECT *
FROM public.players
WHERE username = 'foobar'

In both cases it should show something like:

Index scan using using players_username_key on public.players (cost=...[truncated])
  Output: id, username, trophies ...


You can use row_number and the modulo operator

Tables don't have any inherent order. You need to define an ordering expression (e.g column names) to define what "first" and "next" mean.

DECLARE @StepSize int = 2;

WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY YourOrderingColumn) AS RN
FROM YourTable
)
SELECT *
FROM CTE
WHERE RN % @StepSize = 1;


Setting up Log shipping without enabling LS copy job

Setup log shipping and disable LS copy job.
As long as you setup backup folder correctly you should be ok.


@@identity is not a good solution and will return incorrect results since .  SCOPE_IDENTITY() is session based and will return the last inserted record for the session.  There isn't a way to correct this without a code change.


SSIS 2012: The environment reference n is not associated with the project

The reason for this is that the existing job step that executes the package contains a /ENVREFERENCE switch in the command line, and that value must be updated for the new version of the package.

This is not obvious, as using SSMS to view the job step doesn't display this switch anywhere. The only way that I have found to fix this is to script out a drop and create of the job, obtain the correct reference_id using code like this:

SELECT  reference_id
  FROM  SSISDB.[catalog].environment_references er
        JOIN SSISDB.[catalog].projects p ON p.project_id = er.project_id
 WHERE  er.environment_name = @Environment
   AND  p.name              = @ProjectName;

and then updating the @command parameter of the call to sp_add_jobstep with the new number.

Helpful references (that you won't find by searching for the error message):

http://www.biadmin.com/2012/08/ssis-2012-gotcha-dtexecexe-and.html
http://blogs.msdn.com/b/mattm/archive/2011/07/24/using-dtexec-with-packages-on-the-is-server.aspx


How to use LIKE operator with multiple conditions at once in PostgreSQL

You can combine the LIKE operator with an array:

select *
from employees
where name like any (array['%.%', '%,%', %;%', ...]);

Or a completely different approach, that does not require to repeat the wildcard for each value:

select e.*
from employees e
where exists (select *
              from (values('.', ',', ';')) as t(ch)
              where e.name like concat('%', t.ch, '%'))

As you only check for single characters, you could use Postgres' array functions as well: by turning the name into an array of characters and checking for an overlapping array:

select *
from employees
where string_to_array(name, null) &&  array['.', ',', ';'];

or

select *
from employees
where string_to_array(name, null) &&  string_to_array('.,;', null);


MySQL to SQL Server - Unixtime being major problem

You mentioned Unixtime being major problem

Please note how FROM_UNIXTIME() is being used

FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",

From this line, here is what I can tell:

spre.CREATEDTIME is number of milliseconds since 1970-01-01 00:00:00 UTC
FROM_UNIXTIME() requires the number of seconds, not milliseconds
spre.CREATEDTIME has to be divided by 1000 before calling FROM_UNIXTIME()

Based on someone else's post in StackOverflow (Truncate Datetime to Second (Remove Milliseconds) in T-SQL), you will have to change it to something like:

CONVERT(datetime,char(19),spre.CREATEDTIME/1000,126) "criacao da pesquisa",

Please read T-SQL Documentation on CONVERT

  what is this # in front of some sentences??

The # character as the first character makes that whole line a comment

When you execute this script, only the first SELECT is executed

The other SELECT queries and the one UPDATE query are not executed

The first query has comments as well. If you ignore those two lines, this is what really runs:

 SELECT wo.WORKORDERID,
dpt.DEPTNAME "Department",
aau.USER_ID,
aau.FIRST_NAME "Requester",
sadt.RATING "Rating Value",
spre.COMMENTS "Comments",
    FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",
            MONTH(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Mes Pesquisa",
            YEAR(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Ano Pesquisa",
aau1.FIRST_NAME "Technician"
    FROM SurveyPerRequest spr
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID
LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu1 ON wos.OWNERID=sdu1.USERID
LEFT JOIN AaaUser aau1 ON sdu1.USERID=aau1.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
    WHERE (spre.CREATEDTIME >= UNIX_TIMESTAMP('2014-04-01 00:00:00')*1000) AND (spre.CREATEDTIME <= UNIX_TIMESTAMP('2014-08-31 23:59:59')*1000)
AND aau.FIRST_NAME LIKE '%Pumputis%'
        ORDER BY spre.CREATEDTIME


Is this correct normalization for my tables?

I'm not familiar enough with Laravel 7 to understand how well it implements alternate keys/composite keys, but this is basically what you've described:

Notes:

The alternate keys are crucial as an Id in and of itself does not provide uniqueness as required by the Relational Model.  If I can spam "Customer 1, Dwayne Johnson" a million times into Contact then no key exists and there is a normalization error.
The composite key (CustomerId, ContactId) is required to ensure CustomerId is migrated properly to Order
IE is inversion entry, or non-unique index.  This allows orders to be quickly searched by customer.
ContactId itself is not unique.  If you are using a ORM that requires an Id column on everything, you would need to make the relationship between Customer and Contact non-identifying.  In doing so you will remove the ability to retrieve a customer's orders without joining to Contact first.  Under no circumstances should you create a relation between Customer and Order as the Contact selected must belong to the Customer in question and this would introduce a normalization error.

Revised model based on comment:


Is it safe to execute Flush status in production ?

Well it should not affect anything as such but resets the status counters and you should be "Safe".

  Is it possible to reset Max_used_connections alone ?

No

  but "We need to find out Max_used_connection after deploying ...."

How about introducing graphs into monitoring? Say Cacti / Grafana will give you good glances over what's-going-on-inside-your-mysql!


Are these slow tables under any kind of access from other applications?  If the tables are being placed under an explicit read lock that could be blocking phpmyadmin.  There could be an implicit lock by a large insert going on as well.

If it's taking that long you should be easily able to run a show processlist from a straight command prompt to see if you PMA user is sitting locked, sending data, nor maybe nothing at all on the database side.


Eventually I figured this out with help from this stack overflow post.

    SELECT CONCAT(
      'SELECT `mytable`.Name', GROUP_CONCAT(CONCAT('
         ,    `t_', REPLACE(Y, '`', '``'), '`.X
             AS `', REPLACE(Y, '`', '``'), '`'
       ) SEPARATOR ''),
     ' FROM `mytable` ', GROUP_CONCAT(CONCAT('
         LEFT JOIN `mytable`   AS `t_', REPLACE(Y, '`', '``'), '`
                ON `mytable`.Name = `t_', REPLACE(Y, '`', '``'), '`.Name
               AND `t_', REPLACE(Recall, '`', '``'), '`.Y= ', QUOTE(Recall)
       ) SEPARATOR ''),
     ' GROUP BY `mytable`.Name'
    ) INTO @qry FROM (SELECT Recall FROM `mytable` WHERE ... ) t;
    PREPARE stmt FROM @qry;
    EXECUTE stmt;

Initially this was not working because the datatype of columns X and Y was FLOAT which I suspect was messing up the conversion to string. I changed the type to be DECIMAL(10,9) (i.e. a single digit, followed by up to nine decimal points), which made the code work.


BCP stops after the last "1000 rows sent to SQL Server. Total sent: ..."

After 3 hours, I finally got my prompt back:

55837 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 10582641 Average : (5.28 rows per sec.)

Although I still don't know why it didn't return immediately since the rows were already inserted. My best guess was it did index rebuilding.

I will try terminating the console the next time it hangs up like that.


How can I create a database where entries are compressed files searchable by index?

Have you tested the lz6 compression level?  lz6 provides less overall compression but it has a index on values which you can pull out without going through the overhead and cost of uncompressing the whole file.  I

If you cannot give up any compression at all, have you tried a simple json document store DB such as Mongo?  I feel using filestream in SQL Server might be overkill but it depends on your environment.  It seems like you're a linux shop from the terminology used, is that correct?

I feel you can use any DBMS to store the simple values such as customerid, date, and filepath then pass the filepath over to your app.  It depends on what solutions you already have in place or which you're very comfortable with that aren't overkill.  For example, Hadoop or something complex would be crazy for this but a key value store DB might be perfect.  Maybe you can make the zip files have the customer id or something in it and only store the date (key) and path (value) in your document store.


how to set bulk_insert_buffer_size in mysql?

When you ran

SET GLOBAL bulk_insert_buffer_size =1024*1024*256;

what you did was set the insert buffer to 256M for incoming DB Connections going forward.

To set it for your current DB session, run this in that session:

SET bulk_insert_buffer_size =1024*1024*256;

Then, you can run

SHOW VARIABLES LIKE '%bulk%';

and see a different value.

In a post made back on April 18, 2013 (see Steps 2 and 3), I suggested setting the bulk_insert_buffer_size to 256M within a current session.

If you want to keep that value when you restart mysql, just add this to my.cnf

[mysqld]
bulk_insert_buffer_size = 256M

Give it a Try !!!


Azure tempdb filling up while trying to convert a large table to columnstore... how to mitigate?

Bulk inserting into a CLUSTERED COLUMNSTORE index by itself shouldn't be blowing up tempdb.  Rows are read and put into compressed rowgroups in chunks of 1 million, there is no reason for long term tempdb usage.
I believe the problem is the non-clustered index ix_SRR_JobId on temp.SRR (JobId);
First try moving the index creation to after the bulk insert:
insert Logs(Message) values('Populating SRR table...');
insert into temp.SRR with (tablock) select * from dbo.SRR (nolock);
create index ix_SRR_JobId on temp.SRR (JobId) WITH (SORT_IN_TEMPDB = OFF);

This will substantially reduce tempdb pressure.
If tempdb is still blowing up, then try loading the data in smaller chunks. How you partition the data doesn't really matter, but, it should be based on the leading column(s) of an existing clustered index (unique or not).  The chunks should be small enough to load but large enough to avoid excessive "delta rowgroups"  (Columnstore Indexes: Data Loading Guidance).
As a starting point, aim for 10-20 chunks of equal size.  If the base table is 2 TB, then these would be 100-200 GB.  Adjust up or down until you get it to work.
For example, if column1 is a datetime, then try loading by year or quarter or month.


Get first and last record of a selection without using min() max()

as it turned out, it wasn't the MIN()/MAX() functions that slowed the query down, but the
WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE()

statement. Since the table has a column with the unix-timestap, using this instead accelerates it a lot:
WHERE TIMESTAMP > UNIX_TIMESTAMP(CURDATE())

Query-time from ~3.6s down to ~0.02s. I call that a good improvement.


compare the variance of sales from one day to another on the same table

There is probably a better way to do it but here's how I get the expected result.
I went with dynamic SQL as your date will change and that was the only way I can think of to get different column name depending on the content of the table.
/* Creating a temp table to reproduce */
create table #demo (id int identity(1,1), vendor varchar(30), sales int, [date] date);
insert into #demo (vendor, sales, [date])
values ('John',10,'2020-07-20'), ('John',5,'2020-07-20'), ('Jeff',15,'2020-07-21'), ('Jeff',20,'2020-07-21'), ('John',5,'2020-07-21'), ('Jeff',30,'2020-07-20');
--Adding data with another date to make sure the query works when there will be multiple days
insert into #demo (vendor, sales, [date])
values ('John',8,'2020-07-19'), ('Jeff',12,'2020-07-19');

declare @maxDate date;
declare @mindate date;
select @maxDate = max([date]) from #demo;
set @mindate = dateadd(day,-1,max(@maxDate));

declare @cmd varchar(max)
set @cmd ='
with Last2Days as (
select vendor, sum(sales) sales, [date]
from #demo
group by vendor, [date]
having date between convert(date,'''+convert(varchar(10),@mindate)+''') and convert(date,'''+convert(varchar(10),@maxdate)+''')
)

select  vendor, ['+convert(varchar(10),@mindate)+'] as "Sale '+convert(varchar(10),@mindate)+'", ['+convert(varchar(10),@maxdate)+'] as "Sale '+convert(varchar(10),@maxDate)+'", pv.['+convert(varchar(10),@maxdate)+']-pv.['+convert(varchar(10),@minDate)+'] "Variance"
from
(select vendor, [date], sales from Last2Days) as s
Pivot
( max(sales) for [date] in ([2020-07-20] , [2020-07-21] )) as pv'

exec (@cmd);

drop table #demo;

To recap, I create a CTE to get only the data from the last 2 days. I also sum up the sales value per day/vendor.
From that CTE, I ran a select with a PIVOT to get the output expected.


Use a table alias:

select t1.*
from table_1 t1
  join table_2 t2 on t1.id = t2.id1;


I can't find any documentation on this

Docs are here.  An S lock is compatible with a U lock, but the UPDATE will reuquire an X lock to actually complete, which is incompatible with an S lock.

and also don't know how to test this

You can always run a a query like
select count(*)
from sales.SalesOrderDetail with (tablock), master..spt_values v, master..spt_values v2, master..spt_values v3

And see if you can update from another session.  The tablock is to ensure that the whole table is locked with an S lock, as it may or may not be with a long-running SELECT.
You'll discover that unless the database has the READ COMMITTED SNAPSHOT option set, the SELECT will block the UPDATE.


SQL Server 2005 query with multiple conditions, some indexed, some not

Is there a better way to write this type of query?

No, the way you've written it is the "best" way.  Unless, of course, that way doesn't work.  The joy and frustration of using a declarative language is the optimiser.  It is your best friend when it works and worst enemy when it doesn't.

One way to kick the optimiser into doing the right thing is to re-write your query in a semantically identically way such as:

select <whatever>
from table as t
inner join
    (
    Select <primary key columns>
    from table as b
    where b.zip='12345'
    ) as z
    on t.PrimaryKey = z.PrimaryKey  -- repeat if multi-column key
    and t.otherField = 'Y';

Now the optimiser is starting from a different place and may, possibly end up with a different, better, answer. For simple single-table queries I fear it is unlikely to show much change, however.

I would only suggest you try this after you're absolutely sure you've done all you can with indexes.  Re-writing queries in non-obvious ways adds maintenance overhead to the application.  It may also prove counter-productive when you patch or upgrade SQL Server.


Select records that fit criteria in another record

select t1.*
  from table t1
  join table t2
    on t2.name = t1.name
   and t1.procedure = 'Procedure1'
   and t2.procedure = 'Procedure2'


Creating index for large table is taking forever

Create clustered index ix1_table1 on dbo.table1 (col1) with (online = on)

This will take a little longer, the online part, but it will allow users to continues using the table while the index is being created. Every table (with very few exceptions) should have a clustered index.


permissions for schema compare and view job history

For the msdb SQL Server Agent roles, granting SQLAgentUserRole allows them the ability to manage jobs which only they own, and see the job history of those jobs too.

To compare schema on all table of any DB, assuming you want to also allow them to have SELECT to all tables, the db_datareader role should be fine per DB.

Otherwise, if you want to be specific, then allow explicit VIEW DEFINITION on the schema per DB which you want to all them to compare and SELECT to the tables you want them to be able to compare the data.

EXPLICIT EXAMPLES TSQL (per the article)

GRANT VIEW DEFINITION ON OBJECT::Region TO Ted
GRANT SELECT ON OBJECT::Region TO Ted

EXPLICIT EXAMPLES TSQL (per the request in your screen shot)

GRANT VIEW ANY DEFINITION TO User

USE msdb

EXECUTE sp_addrolemember @rolename = 'SQLAgentReaderRole'
    ,@membername = 'username'

I think it depends on how well you want to lock it down really, and how trustworthy they are for what you are allowing them to have access to.


An alternative would to remove the article from the publication and re add, causing a new snapshot to be taken of the table. This way there's no question whether the right data is in the subscriber.

To do this you need to make sure the publication properties are set to FALSE for allow_anonymous and immediate_sync - if either of these are true then the a database snapshot will be taken when you remove and re add table rather than just the table.

To check run sp_helppublication against the distribution.

To change if either of these are set to 1:

EXEC sp_changepublication
@publication = 'REP_P', --Enter your publication_name
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'REP_P', --Enter your publication name
@property = 'immediate_sync' ,
@value = 'false'
GO

I have tested this and it works in my environment, however strongly suggest you do this in a lab first.


How about Dynamic SQL ?

SET @query = 'SELECT Col1, Col2... FROM Tab1, Tab2 WITH JOINS...';
CASE pSearchType
  WHEN 1 THEN
    SET @where = 'Col1 = pInput1';
  WHEN 2 THEN
    SET @where = 'Col1 = pInput1 AND Col2 = pInput2';
  WHEN 3 THEN
    SET @where = 'Col1 = pInput1 ORDER BY Col4';
END CASE;
SET @sql = CONCAT(@query,' WHERE ',@where);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Please make sure you create indexes or any appropriate tuning that will support each case.

Give it a Try !!!


Some tables not found after moving MySQL database from Linux to Windows

You can force case sensitivity with lower_case_table_names

Add this to your my.ini

[mysqld]
lower_case_table_names=0

Since lower_case_table_names is not dynamic, you must then restart mysql as Administrator with

C:\> net stop mysql
C:\> net start mysql

The MySQL Documentation says the following

  You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On OS X, the default value is 2.

Please read my 2.5 year old post Is it okay to have different lower_case_table_names value on master and slave? for more clarification.

Please backup the data folder before doing any of this

You might have more issues if the tablename mentioned in constraints are case sensitive. You will have to post that as a separate question because it requires a deeper dive.


Restore database into new DB without replacing the file structure

If your current database is made up of one data file and one log file then that is how it will be restored.

What purpose does the network admin in doing this if the current database is one single data file already? Does network admin understand how file structure works in a database?

If you just wanted to split the tables and indexes between multiple files you would need to manually create each in their own file group, assigned to its own physical file. Then you could import the data into those tables. The other way is to do that same thing after you restore it.

Outside of that if you involve partitioning tables between files it becomes more cumbersome and involved.


How to select specific rows if a column exists or all rows if a column doesn't

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true


Installing Oracle 11g on Windows SBS 2011 64 bit

According to Oracle support document 1307195.1, Oracle's stance is:

  Microsoft Windows Small Business Server

  Windows Small Business Server is a product suite that bundles several
  Microsoft products, including the Windows operating system. Oracle
  Database does not certify with product suites, only operating systems
  and platforms. To distinguish whether Oracle Database certifies with a
  particular Windows Small Business Server version, find that suite's
  operating system version and consult My Oracle Support's certification
  matrix using that operating system version.

Small Business Server 2011 is based on Windows Server 2008 R2. According to the certification matrix at Oracle support, the following versions of Oracle are certified for Windows Server 2008 R2 x64:

10.2.0.5.0
11.2.0.1.0
11.2.0.2.0
11.2.0.3.0
11.2.0.4.0
12.2.0.1.0


MySQL Monitoring Script in Shell or Perl for Linux

For mysql server monitoring you should concern these-
 Mysql server is running or not by command (service mysql status) and by telnet the server that is running or not. You should consider these parameters.

ibdata size
no of connections or Threads
reads per second
write per second
cpu load
memory utilization
no of open tables
query per second
insert/update per second
mysql-server uptime
slow queries
flush tables
no of questions


Simply right-click over pgadmin.exe and select "Pin to start".


Full text catalog does not allow me to hit on singular forms

You could use either CONTAINS with FORMSOF(INFLECTIONAL, smörgås) or FREETEXT

CONTAINS:

SELECT pc.SearchData from PageContent pc
WHERE CONTAINS(pc.SearchData, 'FORMSOF(INFLECTIONAL, smörgås)', LANGUAGE 'Swedish')

FREETEXT (includes INFLECTIONAL and some other stuff automatically):

SELECT pc.SearchData from PageContent pc
WHERE FREETEXT(pc.SearchData, 'smörgås', LANGUAGE 'Swedish')


PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Unreferenced CTEs are not executed at all (except data-modifying CTEs!)
Related thread on pgsql-bugs with Tom Lane explaining the behavior.

In your first example you have:

SELECT * FROM firstquery;

No reference to the CTE nonemptycheck. So the CTE is never executed.

In the second example you have:

SELECT firstquery.* FROM nonemptycheck, firstquery;

nonemptycheck is referenced, so it is executed, resulting in the exception.

No row

Your added test case in the comment fails for a similar reason. Since the first CTE returns no row, the outer SELECT returns no row. There is no need to execute the second CTE, since the result will not be displayed. The optimizer's job is to avoid fruitless work ..

Appending nonemptycheck as cross-joined subquery (CROSS JOIN or appended after comma) instead of a second CTE does not help, either. A similar optimization avoids execution: Since firstquery returns no row, there is no point in evaluating the nonemptycheck, even in a subquery:

WITH firstquery AS (
   SELECT *
   FROM   emptytable
   WHERE  FALSE
   )
SELECT f.*     -- even if you append ", n.*" to SELECT list
FROM   firstquery f
    , (
   SELECT assert(count(*) = 42, 'check failed')
   FROM   firstquery
   ) n         -- not executed

Solution

You can force evaluation with a FULL OUTER JOIN:

WITH firstquery AS (
   SELECT *
   FROM   emptytable
   WHERE  false
   )
SELECT f.*
FROM   firstquery f
FULL   JOIN (
   SELECT assert(count(*) = 42, 'check failed')
   FROM   firstquery
   ) nonemptycheck ON TRUE;  -- always executed

Side effect: This would return a single row filled with NULL values, when firstquery returns no row. Not in this particular case, though, since your assert raises an exception in this case.


Is it worth it to create an "Id" column?

As name is unique and will never change, it is certainly a good candidate key from a relational theory point of view.

You might find using a integer surrogate key preferable for space and performance reasons though as it will take less space than the text in every table that has a foreign key to this table (and every index as FKs are usually indexed columns). Operations to search/join on an integer column will be faster too, though for queries that join in this table and need to output and/or sort by the name you might find the extra work going from id to name removes some of that benefit. Of course the space and performance differences may not be significant enough to make a difference to your project at which point this comes down to preference.

In fact I suspect even using a UUID might be faster than using name, assuming your DBMS has a proper UUID type so they are stored in compact binary form not as a text field, despite the type's fixed 16 byte length probably being longer than the average length of your name values, as comparisons against fixed length binary values are faster than those between variable length strings (it would be interesting to benchmark this and see if the difference is indeed significant) - though unless you have reason to use a UUID (replication issues or such) the smaller integer type will of course be more efficient and take a quarter of the space.

tl;dr: the name column as described is a perfectly good candidate key, perfect in theory in fact, but in practise an integer surrogate key will be more space and processing efficient.


Option 3.

Unplug the PDB:

Unplugging a PDB

ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml';

Copy the files (XML + database files), then perform the checks listed below:

Plugging In an Unplugged PDB

Finally plug in the PDB:

Examples of Plugging In an Unplugged PDB

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'
  NOCOPY
  TEMPFILE REUSE;


Should name suffix (Jr. Sr. III) be a separate field?

One issue with this is that a query such as where last_name = 'Brown' will miss Brown Jr.

That's not really an issue. Just do WHERE last_name LIKE '%Brown%'. Generally, I don't put them in separate fields because my word loads don't require it. You can go by the name Jo2e Bob44 and I don't much care. Sometimes I break things apart into First Name, and Last Name to build Rolodex functionality or custom sorts.

The only time I think the application requires it would be if you're making an ancestry.com or the like. I could see it being more significant there.


Does range improve match operation and which runs first, range or match

As you indicated by the small_table only containing one range, adding the date adds a hint that could be used in some cases. As worst it will just add a quick condition to an already retrieved row.

'had indexing' really doesn't help, it matters what the index begins with. Indexes can have multiple parts and the order matters significantly. An index with a 'prefix of A' could include A as the entire index.

The indexing on the small_table is largely a second consideration as the entire table needs to be scanned.

A uuid as a prefix on an index (or primary) key should be considered the minimium on the large_table. In this case the small table will be scanned and each uuid will be looked up in big table.

If a time is a prefix of a index (or primary key) of the large_table, date criteria in the index be searched and matched against the small_table. It depends here if the optimizer thinks the date range in the table is bigger or small than the small_table. If there is no uuid indexes this is the only optimal solution.

If there are no indexes, much slowness will happen, i.e. the large_table will be entirely scanned for each element in small_table

To check what will be used check:

EXPLAIN
SELECT small_table.uuid, small_table.time, large_table.uuid, large_table.time
FROM small_table, large_table
WHERE
    small_table.uuid = large_table.uuid
    AND large_table.time >= '2019-11-20' and large_table.time < '2019-11-21'


AND conditions within unbracketed ORs work but why?

This is explained by the rules for operator precedence:

Operator Precedence (Transact-SQL)

As noted there, AND has a higher evaluation precedence than OR.

The 1 = 1 parts of your query are redundant (and removed by the optimizer). You could equally well have written:

DECLARE @Test AS table
(
    Id integer PRIMARY KEY,
    [Description] varchar(10) UNIQUE
);

INSERT @Test
    (Id, [Description])
VALUES
    (1, 'One'),
    (2, 'Two'),
    (3, 'Three'),
    (4, 'Four'),
    (5, 'Five'),
    (6, 'Six'),
    (7, 'Seven'),
    (8, 'Eight'),
    (9, 'Nine'),
    (10, 'Ten'),
    (11, 'Eleven');

SELECT
    T.Id,
    T.[Description]
FROM @Test AS T
WHERE
    T.Id = 1 AND T.[Description] = 'One'
    OR Id = 7 AND T.[Description] = 'Seven'
    OR Id = 3 AND T.[Description] = 'Three';

This produces the same result, for the same reason (precedence):

Demo on Stack Exchange Data Explorer

I would strongly suggest using the version with parentheses for clarity.


Any impact of upgrade db2 from V10.5 to V11.5?

If you will need a Solaris Client, you won't get a client for the last version. IBM drop the support for Db2 in that platform.

Remember to rebind packages.

Also, update Db2 clients and JDBC clients to have everything in the same version.


From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads

The manual for CREATE PROCEDURE states: "Not Yet Implemented"

The documentation of the SELECT statement neither shows a WITH nor an alternative like connect by (as e.g. Cubrid does).

For bulkloading there is nuoloader but there doesn't seem to be an API for it.

But you will probably get a better answer if you post directly to the NuoDB forum: http://www.nuodb.com/community/forum.php?

I had a look at NuoDB as well and was pretty disappointed by the lack of SQL features. I guess that's not their main focus right now.


I ended up using a separate table to hold translation because maintaining the custom functions and translation blob is a non necessary complexity and is not a portable solution.

CREATE TABLE IF NOT EXISTS Route (
  id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
  name text NOT NULL,
  nameLocalizationId INTEGER REFERENCES Localization(id)
);

The localization table have 3 columns:

CREATE TABLE IF NOT EXISTS Localization (
    id INTEGER NOT NULL,
    language TEXT NOT NULL,
    text TEXT NOT NULL
);

id is the translations id (its possible to have multiple rows with same id),
language is the BCP 47 language tag,
text is the localized string.


SQL Server : Is it necessary to disable SA user for security issues?

Renaming or disabling sa account is just part of the story.

There is no drawback if you disable sa account.

It means if you have databases whose owners are sa, there isn't a problem.

System databases like master, or tempDb require that sa be the database owner.

If you try to change the owner of system databases you will receive 15109 error.

Similar situation is with SQL Server Agent jobs, owned by sa. They won't fail, either.

IMO good practice is to have at least one account enabled with sysadmin role.


Problems of having DR as Synchronous Commit

If i want to change the DR to Synchronous Commit will there be any performance issues ?

Well, it will take longer to commit transactions, as the primary will wait for the log records to be saved on both sync replicas during each commit.  Whether that causes a "performance issue" depends on the transaction throughput, network bandwidth, distance to the DR location, and the tolerance of the application and users for slower commits.


Immediate e-mail alerting for error 229 (permission denied)

Oh, damn it, not 30 seconds after I post this, I stumble across sp_altermessage (naturally, after spending a significant amount of time trying to figure out if I could do this with extended events). So if anybody else is wondering how to do this:

EXEC sp_altermessage 229, 'WITH_LOG', 'true'


The dump database on a SSD disk is slowly restored in SQL Server

Basic reason for restore operation to be slow can be

Hardisk where you are restoring is not able to cope up with I/O request which is required to meet all requests. May be its a busy disk whoes I/O is getting utilized by other process and you started a restore creating extra load and more candidated for I/O. May be disk is slow, requiring some firmware upgrade
Database which you are restoring had to many Virtual log files or when backup was taken some process was rolling back. So now when database will recovery it would make sure that process rolls back completely and then only it can make database online. Backup includes both data and some amount of transaction log to bring database in transactionally consistent state. The amount of transaction log included depends on transaction that were running when backup was  happening. To understand backup please read this article

You must read Optimizing backup and restore in SQL Server

Why copy operation took 30 mins totally depends on load on network and size of backup file being transferred


How do I safely drop a list of tables, when i have the table names listed in a column?

Something along the lines of the following should work.

CREATE TABLE dbo.TablesToDrop
(
    TableName SYSNAME
);
INSERT INTO dbo.TablesToDrop VALUES ('Table1');
INSERT INTO dbo.TablesToDrop VALUES ('Table2');
INSERT INTO dbo.TablesToDrop VALUES ('Table3');
DECLARE @Cmd NVARCHAR(MAX);
SET @Cmd = '';
SELECT @Cmd = @Cmd + 'DROP TABLE ' + QUOTENAME(t.TableName) + ';'
FROM dbo.TablesToDrop T;
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
--EXEC sp_executesql @Cmd;

Uncomment the last line to actually execute the DROP TABLE statements.


AlwaysOn - Problem with adding secondary replicas

Did you add the browser service to the firewall? UDP 1434. Because you're using instance names so they have to get resolved. Otherwise remove the instance name to use that 1433 port.


ORA-00942 when trying to select from gv$session inside of procedure

Try doing this:

GRANT SELECT ON gv_$session TO system;

Because you are accessing a sys table by system in a stored
procedure you need a direct grant on the underlying table.


Need to encrypt certain columns in a table but also be able to sort and search

You don't want to encrypt specific columns, what you want to do is encrypt the filesystem that the database is written to, and any backups of your database that you make. AWS offers facilities for doing both of those ( RDS Encrypted resources and S3 server-side encryption ) or you can do it yourself by using LVM volumes that are mounted using ecryptfs.

You fundamentally cannot search or sort an encrypted column without decrypting the contents. If your encryption is effective then any sorting or indexing process will see it as gibberish and if it's not effective then why are you bothering with it?

The other consideration you should be taking into account is the threat model and risk models associated with your data. If you are dealing with certain classes of data you will need to manage your exposure by following mandated security protocols. HIPAA for medical information, PCI DSS for payment information. Detailed guides for implementing those protocols are available, and if you are covered by one of them; follow it.

Encryption and data protection is not an area in which you should be creative; it's hard to get right and will bite you hard if you get it wrong.


Serialize a column based on sorting order?

UPDATE sourcetable
SET number_order = (@tmp := @tmp + 1)
WHERE NOT (@tmp := 0)
ORDER BY products_name ASC

fiddle


Availability groups using multiple subnets changes

... but we are looking at modifying the RegisterAllProviderIP cluster resource setting to 0 due to some limitations on the MultiSubnetFailover parameter ...

Could you let me know what those limitations are? AFAIK there aren't any... MultiSubnetFailover is the defacto way to deal with your issue. Period. Hence why I would be interested to know what the limitations of the parameter are.

  My question though is if AlwaysOn changed the way connections are made in any of the newer versions of SQL (or .NET Framework) that prevents the need to do some of this.

No, there are no changes in SQL Server. There is a change in .NET 4.6.1 that will use multisubnetfailover logic if the initial connection doesn't happen in the first 500 miliseconds if multisubnetfailover was not added to the connection string.

  Is “MultiSubnetFailover=True” or changing the RegisterAllProviderIP still required in SQL 2016 or is any of this handled automatically\different?

You should ALWAYS use multisubnetfailover=true unless the client driver does not support it... in which case, upgrade the client driver and/or application... or deal with the application timing out.


set postgres instance as readonly with no master

You probably haven't set up replication properly. The recovery.conf file needs something like this:
standby_mode = 'on'
primary_conninfo = 'host=127.0.0.1 port=5888 user=replicator password=xxx'
trigger_file = 'postgresql_xxx_12345.trigger'

The port should/can be a port that isn't used. The cluster will try to connect there - and fail but it will come online and allow read-only connections as if it is a slave. Which it is actually, with its imaginary master being down for the moment and indefinitely.
A side effect that you probably want to avoid - disable logging perhaps? - is that the log will be full of errors, of the streaming replication connect failures:

2017-12-05 22:19:08 UTC [18096-1] FATAL:  could not connect to the
primary server: could not connect to server: Connection refused       Is
the server running on host "127.0.0.1" and accepting      TCP/IP
connections on port 5888?
2017-12-05 22:19:13 UTC [18099-1] FATAL:
could not connect to the primary server: could not connect to server:
Connection refused        Is the server running on host "127.0.0.1" and
accepting         TCP/IP connections on port 5888?
2017-12-05 22:19:18 UTC
[18103-1] FATAL:  could not connect to the primary server: could not
connect to server: Connection refused         Is the server running on host
"127.0.0.1" and accepting         TCP/IP connections on port 5888?


Risk of changing clocktime on MySQL server host

Binary logs will record the events sequentially with whatever timestamp it has. Using any binary log around the time of a clock change makes the particular log unusable for PITR (Point-in-Time Recovery).
If the DB Server is a Slave, Replication may skip processing certain events.
If you have Binary Logging or Replication Enabled, I would do  the following

On DB1, stop writes to the database
On DB2, run SHOW SLAVE STATUS\G multiple times to make sure there are no new commands being processed and Seconds_Behind_Master is 0
On DB2, STOP SLAVE;
On DB1, RESET MASTER; (Erases all binlogs)
Stop MySQL Shutdown on DB1
Stop MySQL Shutdown on DB2
Set the Linux Time on DB1 and DB2 (Make sure times match)
Start MySQL Shutdown on DB2
Start MySQL Shutdown on DB1
On DB1, RESET MASTER; (Erases all binlogs again)
On DB2, CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4;
On DB2, START SLAVE;

The idea here is to make sure all binary logs and relay logs are time synchronized. The NOW() function could then be correctly interpreted from these logs.
CAVEAT
Be very careful about changing clock times when it may not be necessary when it comes to Daylight Saving Time. Since the clock time is written in binary logs in seconds since Jan 1, 1970, it may not be necessary. Since the clock is 75 minutes off, do this ASAP. Don't wait until DST kicks in.


I hate precision-based issues. I dealt with one before: Data Truncated for Column

You may have do the ALTER TABLE manually

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT CONVERT(foo,DOUBLE) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

or

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT CONVERT(foo,DECIMAL(10,7)) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

or

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT FORMAT(foo,7) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

Not sure what will happen, but give it a try and see what happens.


Is there an option to prioritize a table while replication conflict occurs?

No.

The Master and the Slave should always (aside from a minor delay) have identical data.  If they don't, you need to investigate and fix it.  Else, things could get worse and worse.

Percona Tools has a tool to discover whether there are differences between Master and Slave, plus a tool to fix them for you.  However, it assumes that the Master is the Master, not the Slave.

Is the Slave universally the "better"?  Then consider "failing over" -- That is, make the Slave the Master and the Master the Slave.  Now, your problem (as stated) goes away.

(Sorry, there are too many details to spell out details of all the steps you need in this Q&A.)


you first need to connect as root to the database, then you create the admin user.

console

connect /as sysdba

sql

create user admin identified by secret
grant dba to admin
conn admin/secret


SQL Server Express 2014 Installation directory

That dialog asks you for the installation media, pay attention. You need to specify the location from where you've run the original install. That is not an Express uninstall, you are uninstalling a licensed SSMS, so you should provide your original, licensed, CDs.


Quickly restoring a backup over top of an existing database - error "database in use"

Variants of the following which uses ROLLBACK IMMEDIATE are common:

:setvar DatabaseName "SampleDatabase"
:setvar BackupDevicePath "D:\Temp\sample.bak"
:setvar DataFilePath "D:\SQLData\I1\"

USE master;
GO

IF (DB_ID('$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO

RESTORE DATABASE
    [$(DatabaseName)]
FROM
    DISK = N'$(BackupDevicePath)'
WITH
    FILE = 1
    , MOVE N'$(DatabaseName)_Data' TO N'$(DataFilePath)$(DatabaseName).mdf'
    , MOVE N'$(DatabaseName)_Log' TO N'$(DataFilePath)$(DatabaseName).ldf'
    , REPLACE
    , STATS = 1;

Note SQLCMD mode.


need help joining two tables with a having count clause

very close only the GROUP BY Columns forgotten

Try this:

select
PTC_DIAGNOSIS.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
PT_BASIC.NAME_FIRST,
PT_BASIC.NAME_LAST
from PTC_DIAGNOSIS
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015'
group by
PTC_DIAGNOSIS.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
PT_BASIC.NAME_FIRST,
PT_BASIC.NAME_LAST
having COUNT(*)=1


Why don't relational databases have read-only indexes on columns?

Sure, some database platforms do handle read-only or mostly-read data differently.

For example, in Oracle, you can choose to enable results caching, which lets the database engine save query results and reuse them when it sees the same query come in again. That's really useful for data that rarely changes. However, you wouldn't want to bother enabling that in scenarios where you know the data will change.

Another example: in SQL Server, if you set the entire database as read-only, SQL Server handles locking differently. It doesn't need to worry about transactions when you can't modify the data.

I know what you're thinking: "Why can't the database engine figure it out?" Because data access patterns can be complicated. For example, you might have a data warehouse that is loaded from scratch every night, and then is read-only all day long. You wouldn't want the engine to bother even trying to cache ETL query results overnight during the loads, so you'd want to turn that kind of thing off overnight to maximize load speeds.


Very high I/O on database during a backup maintenance plan

Depending on your database size, checkdb can take hours to finish. It is heavy on the disk subsystem as well incurring lots of I/O's.

Check out Aaron's excellent article on : Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts

Backups are also I/O intensive operation.

I would suggest to look for a maintenance window - when activity is low or minimal on the server.

Now if you want to dig deeper(prove it), then you can use sys.dm_io_virtual_file_stats. It will show you where the hot-spots are and you can ask your SAN admin to move them away.

 ---Ref: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

SELECT
    --virtual file latency
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    --avg bytes per IOP
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [io_stall_write_ms] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    --[vfs].*,
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

As a side note, highly recommend you to look into Ola's : SQL Server Maintenance Solution - backup and index maintenance


Why is my pg_restore command not working?

Try specifying the name of the DB to start in, using the -d or --dbname argument. This worked for me when I had the same problem. Note that it doesn't have to be the target DB:

pg_restore -h localhost -p 5434 -U postgres -d postgres -W -C -v z:\pg_dump\96_junk.dump


postgres crosstab with unknown number of categories

Seeing as you might be querying all months at some time, you could just add all the months to the crosstab query beforehand.

select * from crosstab
    (
    'select project, month, hours from bar order by 1,2',
    $$SELECT unnest('{jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec}'::text[])$$
    ) as  c
    (
    "project text" text,
    "jan" int, "feb" int, "mar" int, "apr" int,
    "may" int, "jun" int, "jul" int, "aug" int,
    "sep" int, "oct" int, "nov" int, "dec" int
    );

  This solution is based on the answer to the question PostgreSQL Crosstab Query on stackoverflow.com.


Cognos, modifying canned report. Namespace of tag doesn't exist

I'm copying the comment I made above here so that I can set this question as 'Answered'.

I think the reason for this was because the entire thing was written in sql. Some of the reports provided for us do not use the Data Items given to us, they are just written in SQL


SQL Server Integration Services (SSIS) Package Failing with no error

Working backwards from the job, have you been able to run the package straight from the server? In older versions of SQL Server it might be in the Integration Services server, that you can connect to through Management Studio, or in newer versions of SQL Server it could be in the Integration Services Catalog on the database server. If you can run the package directly through there, then I would take a look the job credentials, or any configuration settings that are being set in the job. If the package fails when running it directly, then it may be the package itself that's the problem, which then I would go back to running and troubleshooting it from Visual Studio\BIDS to see where the problem is occurring.

On a more unusual path, if the package is doing a lot over an unsteady network, then you might be getting network related problems, or timeouts that aren't being reported as such to SQL. This might need you to get creative in staging data or breaking up the tasks into smaller packages and then calling those packages as additional steps.


What could cause "lagging" in SSIS execute SQL task?

Chances are your drop/create statements are blocking each other on occasion.  As already mentioned, you can truncate the tables instead, however a fairly simple test would be to run the drop/create tasks sequentially, and the data loads afterwards in parallel.

If you're desperate to know the detailed reasons behind the variable times, then do as suggested and run a trace or XE session to capture locking & blocking during the runs.


Mongo repair database "ERROR: error: exception cloning object in local.system.replset "

ReplSet configuration exists on many other members of that replica set.  You can safely drop the local db collections (you can even drop the whole DB but then you will just end up syncing from another replica and there is no point to repairing this entire DB).  So drop the troublesome collection - it will be recreated when the host rejoins its replica set.  The only local collection you need to keep will be the oplog.rs collection - this would allow this member to know where to start catching up for operations it misses while you are doing repairs.


move content from one table to another table and add a new value

Your table needs to be like this

CREATE TABLE `insc_mwc` (
 `idwater` int(11) NOT NULL AUTO_INCREMENT,
 `nome` varchar(45) NOT NULL, -- Make nome NOT NULL
 `categoria` varchar(45) DEFAULT NULL,
 `empresa_idempresa` int(11) NOT NULL,
 `mwccar_idcat` int(11) NOT NULL,
 `points_pontos` int(11) NOT NULL,
 `n_registo` INT DEFAULT 0,
 PRIMARY KEY (`idwater`),
 UNIQUE KEY (`nome`), -- Add unique index on nome
 KEY `fk_water_empresa1_idx` (`empresa_idempresa`),
 KEY `fk_water_mwccar1_idx` (`mwccar_idcat`),
 KEY `fk_water_points1_idx` (`points_pontos`),
 CONSTRAINT `fk_water_empresa1` FOREIGN KEY (`empresa_idempresa`) REFERENCES `empresa` (`idempresa`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_mwccar1` FOREIGN KEY (`mwccar_idcat`) REFERENCES `cat_mwc` (`idcat`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_water_points1` FOREIGN KEY (`points_pontos`) REFERENCES `points` (`pontos`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

To get this, first make sure nome is unique by running this:

SELECT nome,COUNT(1) DupCount FROM insc_mwc GROUP BY nome HAVING COUNT(1) > 1;

If you get nothing back (Empty Set) from this query, then you can safely run

ALTER TABLE insc_mwc MODIFY n_registo INT DEFAULT 0, ADD UNIQUE INDEX (`nome`);

With a unique nome and n_registo as an INT, you can increment n_registo for each time there is a DUPLICATE KEY on nome. The INSERT can now be done like this:

INSERT INTO `insc_mwc`
(`nome`,`empresa_idempresa`,`cat_mwc_idcat`,`points_pontos`)
SELECT nome,empresa_idempresa,cat_mwc_idcat
FROM temp_mwc WHERE temp_mwc.empresa_idempresa=$id
ON DUPLICATE KEY UPDATE n_registo = n_registo + 1;


Which parameters was a stored procedure or command executed with?

If you have a suspicion about which processes/procedures, etc cause the issue, could you not use SQL Profiler / Trace / extended events to get the data? - it is then fully recorded including the Parameters that were passed. - of course this would need to be running ahead of time - not any use once it's already running. However a well filtered Trace can have relatively low performance impact, so you could leave the Trace running all the time.

Here's basic how to setup a Trace/Profiler: How To Create a Trace with SQL Server Profiler

You can find better sources of info on this once you have your head round the concepts.


Altering Tables using Tablenames from other Table

You can loop using an EXECUTE statement.  I believe this post has the exact answer you're looking for, you just need to change the ALTER statement.


Getting by list of ids: `unnest() JOIN` vs `= ANY()`

You can see one difference in the plans, one has an estimated row count of 100, the other has a (correct) estimated row count of 42.  (Note that this is fixed in v12, where UNNEST has been taught to count its input size when given a constant array, rather than just blindly reporting an estimate of 100).  This could become important if the difference were larger than 42 vs 100, and if you added further joins (or other complexities) to the query.

  One thing that I thought of was that unnest can work for a composite key lookup (searching by two indexed fields, not just one).

How would you use UNNEST for this, where you can't use =ANY equivalently instead?  Usually a VALUES list is used for this (which also has its own issues, which also differ from version to version.)


Possibly tempdb out of space or a system table is inconsistent

DBCC CHECKDB makes extensive use of tempdb. It relies on a mechanism similar to snapshot isolation to make sure it always sees a transactionally consistent image of the database, independent what else might be going on.

The error you receive is most likely due to the fact that there is not enough room on the drive that tempdb is on. However, the error also states to check any prior errors. Were there any?


You are importing TESTPR SCHEMA so, change the value of SCHEMAS parameter to TESTPR as shown below. You have given TEST_2 to the SCHEMAS which is not included in the dump file thus you have hit the error ORA-39165.

...REMAP_SCHEMA=TESTPR:TEST_2 SCHEMAS=TESTPR


MySQL Master - Slave replication stops after 20min with no errors

The first thing I would do is check the network between the Master and Slave. Why ???
Almost 8 years ago, I wrote this post : Slave I/O thread dies very often
I once had an occasion where I saw a Slave with SHOW SLAVE STATUS\G looking exactly as you have it, but it was not replicating. This is what actually happened:

The firewall on the Slave would become intermittent
The Master could not talk back to the Slave through the I/O thread
SHOW PROCESSLIST on the Master revealed that the I/O thread was no longer there
Slave assumed the Master was OK

What I recommend is this

Start troubleshooting the traceroute on dynamic....org
See if DNS is acting up. If DNS is involved, add skip-name-resolve and skip-host-cache to my.cnf on Master and Slave and restart mysqld

Please let us know what you found in your troubleshooting


how to convert a date string like this MM/YYYY to 01-MM-YY

SQL> select to_char(to_date('03/1996', 'MM/YYYY'), 'DD-MON-YY') from dual;

TO_CHAR(T
---------
01-MAR-96

Based on that (if by convert you meant changing the data in the table):

update table tableX set columnX = to_char(to_date(columnX, 'MM/YYYY'), 'DD-MON-YY');


Specifying Oracle Service Name in UNIX Profile

Use the TWO_TASK variable and set it to [server]/[service]

export TWO_TASK=server.example.com/mydb

This will override ORACLE_SID and make a sqlnet connection to the DB.


try this..

SELECT X.shipManagementInvoiceNetValue - Y.shipManagementCreditNoteNetValue
FROM
(
   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
   FROM tckopf AS shipManagementInvoice
   WHERE shipManagementInvoice.referenzid = 1
   AND shipManagementInvoice.btyp = 5
)X,
(
   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
   FROM tckopf AS shipManagementCreditNote
   WHERE shipManagementCreditNote.referenzid = 1
   AND shipManagementCreditNote.btyp = 6
)Y


How to grant more memory to run R processes in SQL Server 2016 R Services

You'll want to use the resource governor.  Here is a good blog.

Enable Resource Governor (do some research on this):

ALTER RESOURCE GOVERNOR RECONFIGURE;

This will tell you how much % of your memory R can use.

SELECT * FROM sys.resource_governor_external_resource_pools

Sample query to change values from the link:

-- Default value
ALTER EXTERNAL RESOURCE POOL [default]
WITH (AFFINITY CPU = AUTO)
GO

CREATE EXTERNAL RESOURCE POOL RService_Resource_Pool
WITH (
     MAX_CPU_PERCENT = 10
    ,MAX_MEMORY_PERCENT = 5
);

ALTER RESOURCE POOL [default] WITH (max_memory_percent = 60, max_cpu_percent=90);
ALTER EXTERNAL RESOURCE POOL [default] WITH (max_memory_percent = 40, max_cpu_percent=10);
ALTER RESOURCE GOVERNOR reconfigure;

Don't forget to run reconfigure again:

ALTER RESOURCE GOVERNOR RECONFIGURE;


Performance at finding all foreigns with 3 one-to-many relation

Instead of doing 3 separate joins, you could accomplish the same thing by simply using an OR clause (the execution time seems to be ~1-2ms):
SELECT DISTINCT u.id
FROM user u
JOIN translation_revision t1
    ON u.id = t1.translator_id OR u.id = t1.proofreader_id OR u.id = t1.reviewer_id
GROUP BY u.id
LIMIT 30

Other ideas would be to denormalize your data and keep some additional info about your users roles.


Databases: are there universal datatypes?

For the most basic datatypes, nearly all DBMS either support the ANSI data types directly or at least allow to specify them.

The following statement:

create table products
(
   product_id    integer not null primary key,
   product_name  varchar(100),
   price         decimal(10,2)
);

works on nearly every DBMS. The data types will however be translated to the DBMS-specific ones (e.g. integer will be changed to number in Oracle, or varchar to varchar2).

When it comes to more "advanced" datatypes like CLOB, BLOB, XML, Json, or geospatial types then this doesn't hold true any more (CLOB, BLOB is pretty common, but e.g. not understood by Postgres although you can easily create a domain or a user data type with that name to make it more compatible).

The basic data types as defined by the ANSI standard are:

CHARACTER
CHARACTER VARYING (or VARCHAR)
CHARACTER LARGE OBJECT
NCHAR
NCHAR VARYING
BINARY
BINARY VARYING
BINARY LARGE OBJECT
NUMERIC
DECIMAL
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE PRECISION
BOOLEAN
DATE
TIME
TIMESTAMP
INTERVAL

Not all DBMS suppor all of them (MySQL doesn't have an interval data type, MySQL and SQL Server don't have a real boolean type, Oracle does not have a boolean type at all or a real DATE or TIME data type, Postgres doesn't have nchar - that list could go on and on). But the most common ones usually work quite well.

From my experience, the following ones work without (major) problems across different DBMS (although that is my experience - this is by far not a complete or definite list!)

VARCHAR
INTEGER
DECIMAL
DATE (with surprises: Oracle has it but includes a time)
TIMESTAMP (does something different than expected on SQL Server and MySQL)

If you stick with those, you are pretty much "universal", but anything else will give you some (unpleasant) surprises when you go from one DBMS to another.


Generate due dates using function in a select statement

One solution uses recursion:

Setup

CREATE TABLE dbo.Test
(
    instno integer PRIMARY KEY,
    amount money NOT NULL,
    duedate date NULL
);
GO
INSERT dbo.Test
    (instno, amount, duedate)
VALUES
    (1, 1000.00, '20200130'),
    (2, 1000.00, NULL),
    (3, 1000.00, NULL),
    (4, 1000.00, NULL),
    (5, 1000.00, NULL);

CREATE FUNCTION dbo.SemiMonthlyAmortizationScheduleDate
    (@dt date)
RETURNS table
WITH SCHEMABINDING
AS
RETURN
    SELECT
        ScheduleDate =
            CASE
                WHEN DAY(@dt) < 16
                THEN
                    CASE
                        WHEN MONTH(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF MONTH
                        THEN EOMONTH(@dt)
                        ELSE DATEADD(DAY, 30 - DAY(@dt), @dt) -- SET AS 30
                    END
            ELSE
               DATEADD(MONTH, 1, DATEADD(DAY, 15 - DAY(@dt), @dt)) -- SET AS 15
            END;

Solution

WITH
    R AS
    (
        SELECT
            T.instno,
            T.amount,
            T.duedate
        FROM dbo.Test AS T
        ORDER BY
            T.instno
            OFFSET 0 ROWS
            FETCH FIRST 1 ROW ONLY

        UNION ALL

        SELECT
            Top1.instno,
            Top1.amount,
            duedate = SD.ScheduleDate
        FROM
        (
            SELECT
                T.instno,
                T.amount,
                R.duedate,
                rn = ROW_NUMBER() OVER (
                    ORDER BY T.instno ASC)
            FROM R
            JOIN dbo.Test AS T
                ON T.instno > R.instno
        ) AS Top1
        CROSS APPLY dbo.SemiMonthlyAmortizationScheduleDate(Top1.duedate) AS SD
        WHERE
            Top1.rn = 1
    )
SELECT
    R.instno,
    R.amount,
    R.duedate
FROM R
ORDER BY
    R.instno;

Try it online at db<>fiddle demo

The ROW_NUMBER thing is just a way to get TOP in the recursive part of a CTE see Performance Tuning the Whole Query Plan for details on that if you need it.

If you can use SQL CLR, a faster solution is available using the technique I show in my answer to How can I eliminate this costly index seek operation from my query?


Short Answer

Your 7-way Cartesian JOIN is going to have some serious performance problems.

Long Answer

Think in Sets.

I am assuming that your Data Set needs to contain:  speedcat, bucket_index, count(*)

The simple solution for that Data Set is straight forward:

select t.speedcat
  , WIDTH_BUCKET (t.weight, 0, 0.6, 601) as bucket_index
  , count(*) N
from my_link_t t
group by t.speedcat, WIDTH_BUCKET (t.weight, 0, 0.6, 601)

This is format (x,y,z) is the expect format for most graphing packages.

If you want the results in a "grid" format, then PIVOT the results.

with data as (
  -- same SELECT as previous answer
  select t.speedcat
    , WIDTH_BUCKET (t.weight, 0, 0.6, 601) as bucket_index
    , count(*) N
  from my_link_t t
  group by t.speedcat, WIDTH_BUCKET (t.weight, 0, 0.6, 601)
)
select *
from data
  pivot (
    sum(N)
    for speedcat in ( 2 as "speedcat_2"
                     ,3 as "speedcat_3"
                     ,4 as "speedcat_4"
                     ,5 as "speedcat_5"
                     ,6 as "speedcat_6"
                     ,7 as "speedcat_7"
                     ,8 as "speedcat_8"
                     )
  )


This isn't an exact answer because I don't know how I did resolved the issue but the following options did resolve my issue. When I tried to create a mysql dump to re-build the server I got:

  mysqldump: Error: Binlogging on server not active

Which to me indicated there was something wrong with the mysql configuration on the master because binary logging is enabled in the my.cnf. I also looked at the most recent binary log file for the past 6 hours and it was only 2.8K. I ran

service mysql restart

which got my binary logs working as intended. The new binary log began functioning again as expected (within 5 minutes 2.8M). So now my log file became:

-rw-rw----  1 mysql mysql  54M Aug  1 02:00 mysql-bin.000926
-rw-rw----  1 mysql mysql 2.8K Aug  1 10:58 mysql-bin.000927
-rw-rw----  1 mysql mysql 2.4M Aug  1 11:02 mysql-bin.000928

I then went back to my slaves and re-ran the change master commands to the new bin log and it worked as expected.

stop slave;
change master to master_log_file='mysql-bin.000928', master_log_pos=0;
START SLAVE;

With this show slave status\G now shows:

Master_Log_File: mysql-bin.000928
Read_Master_Log_Pos: 2439078
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 299660
Relay_Master_Log_File: mysql-bin.000928
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:


How do you use pvkconverter.exe - not working for me?

Finally got this to work.  A couple of issues.

I had to shell out to the command prompt "as administrator"
had to install "Visual C++ Redistributable for Visual Studio 2012".

Here's the weird part.  If I run "pvkconverter.exe' with the -d option, it gives me an error saying it requires a decryption password.  If omit the "-d" option, it generates the "PVK/DER certificate pairs" which i can see my .cer and .pvk files now.  I got my files now but not sure if they are legit.

I guess the ONLY definitive way to find out if the .cer and .pvk files are valid is to run them on SQL Server.


SPN for Availability group(AG) listener for SQL Server 2016

If you configure the listerner with a domain admin account it should be automatic but usually it is not enough.
It's better to have full control of what you are doing.
First thing to remember is that an spn is a registration done on the domanin compunter account or domain user account that is running sql server service. While it is the same for both nodes, you have to do only one registration.
Check existing spn for your system:
setspn -L domain\sqlserverservicelogin

or
setspn -L domain\clusterservercumputeraccount$

Then you can register a listener in this way:
setspn -S MSSQLSvc/listener_name:port domain\sqlserverservicelogin
setspn -S MSSQLSvc/listener_name.domain.local:port domain\sqlserverservicelogin

It's better to register both listener name and listener FQDN to be sure to match less precise application configurations.
The same commands with computer accounts:
setspn -S MSSQLSvc/listener_name:port domain\clusterservercumputeraccount$
setspn -S MSSQLSvc/listener_name.domain.local:port domain\clusterservercumputeraccount$

To register spn you have to be domain admin.
A Microsoft tool that can help you if Microsoft Kerberos Configuration Manager for SQL Server:
https://www.microsoft.com/en-us/download/details.aspx?id=39046
Once everything is on place you don't have to do anyting else unless you change something like serfice users, listener names and/or ports


select data from related tables and display their counts in the relating table

name != 'NULL'

That assumes that name can be the 4-letter string 'NULL'.  If, in reality, it can be the special value NULL, then do this:

name IS NOT NULL

Please provide SHOW CREATE TABLE for each table, plus a small amount of representative data including some nulls.


Do I need a separate IP address per subnet for every database in a Basic Availability Group?

Do I really need to create a separate AG Listener with two unique IP addresses >for every AG and thus every database, or is there a way to reuse either the IP >addresses or the AG listeners?

Yes, really, you do.

In fact, you'll need a unique IP address for each listener for each subnet that listener could potentially live in. Why? Well it's not very helpful to have a listener that can't listen. This is true whether Basic AGs are used or not.


SSMS 2012 / 2014 / 2016 Object Explorer hangs when Expanding Databases list on a server with many DBs

This issue is finally fixed in the new SSMS 17.9.

You can get it here.


What is the getdate() equivalent for MongoDB?

You can try this.
db.testDBA.updateOne(
   {},
   {
     $currentDate: {
        modifiedOn:true
     },
     $set: {
        "item":"anything"
     }
   })

I inserted a new document and updated it. It worked.

If I run this code again, the document will be updated like this


SQL Server Truncates Transaction Logs with Copy Only Backups

I recommend you use the code in one of the two existing answers to validate that t-logs are not being taken.  Depending on the access your users have, someone may be taking backups without your knowledge.

There was a comment and link by Denis Rubashkin talking about Pseudo-Simple SQL Server Recovery Model Essentially it says that if you are in full recovery and have never taken a full backup (or otherwise disrupted the LSN chain) , SQL knows there is no way to recover, and it does not save the t-logs.

But given your description:

  the backup appears to truncate the transaction log. The transaction log is about 40GB. Before the backup it is 75% full. When the backup run, it is only 1% full.

I don't think either of the two possibilities above are the cause of you issue.

I suspect that your backup job sets the database to SIMPLE recovery at some point in the process, this would clear the t-logs.  Then it sets the database back to FULL recovery. This scenario would create the symptoms you are seeing.

Look through the backup code, if you find that a change to SIMPLE and back to FULL is occurring, you will want to change something.  Either leave the database in SIMPLE recovery all the time, or take regular t-log backups.  Which choice you make is a business decision, dependent on your recovery objectives.

There is no good reason to be in full recovery if you are not taking t-log backups.

Edit I just noticed another clue in your question.

  nightly of the database Full (Copy Only).

Why are you taking Full (Copy Only) backups?  The only reason to do this is you are taking a single backup and want to preserve the backup chain when you are relying on differential backups for your your recovery.  See Copy-Only Backups

This clue suggests that there are Full, Differential and t-log backups occuring that you are not aware of, it would suggest that whomever wrote your nightly backup job as copy only was aware of the differentials and wanted to preserve the backup chain. If this is the case, I would expect t-logs to be taken several times a day, the normal scenarios is Fulls once per week, Differentials the other 6 days a week, and t-logs regularly through the day.

Or it could mean that the person who put the change to SIMPLE and back to FULL just randomly added it because they did not know the reason or impact of copy only either.


Have your declaration at the top.. Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement.

DECLARE @CompanyName VARCHAR(200);
SET @CompanyName = 'CompanyName';

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))


Old question, but never answered.
The correct answer is that ClearDB does not allow certain functionality on  multi-tenant cluster, including create database, views, UDF's and triggers.

You would need to upgrade to a dedicated cluster in order to perform this statement.


It depends. Pre-calculating aggregate values places a larger load on writes, deriving them makes reads more difficult

If you are frequently accessing a derived value, pre-calculation is a valid de-normalization step. However, in this instance, I recommend using a Materialized View (a view, written to disk, linked by trigger to the parent tables). The materialized view is designed to store frequently asked but tedious-to-derive data, and is useful for high numbers of writes and low numbers of reads.

In a high-write, high-read scenario, consider having a task in the background which mimics the effects of a materialized view, but in less than real-time. This will present a "good enough" average while preserving write and read performance.

In no circumstances, should you treat the derived column like a "normal" column: make sure the data presented in the Widgets "view" is present elsewhere in the table, such that the entire tuple can be derived by whatever processes you emplace. This question is also strongly database (and database-version) specific, so I recommend performance testing of the aggregate (with appropriate indexes) against a normal-sized data set and the materialized view.


Postgresql condition on subquery results

Either you run an additional UPDATE (best in one transaction):

UPDATE site_stats s
SET    total_users   = 0
     , monthly_users = 0
     , weekly_users  = 0
WHERE  NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id)

Or use this instead (I would do that):

UPDATE site_stats s
SET    total_users   = COALESCE(u.total, 0)
     , monthly_users = COALESCE(u.monthly, 0)
     , weekly_users  = COALESCE(u.weekly, 0)
FROM   site_stats s0
LEFT   JOIN (
   SELECT local_site
        , count(*) AS total
        , count(last_visit >= now()::date - 30 OR NULL) AS monthly
        , count(last_visit >= now()::date - 7  OR NULL) AS weekly
   FROM   users
   GROUP  BY 1
   ) u ON u.local_site = s0.site_id
WHERE  s.site_id = s0.site_id;

Including some other minor simplifications.


Using Powershell can you insert a macro into Access?

Doing this via a macro seems to be an odd and unnecesary step. In theory you should only need to create the new schema once.

I would create the new schema in postgress and use an ETL tool to map between them.

I would export the access schema and convert the names to lower case using an editor or as part of the export process. This link gives details on how that can be done with vba https://www.techrepublic.com/article/exporting-access-schema-using-xml/

Another option is to import the schema and then use an alter table statement to rename the columns using lower case. It shouldnt be too hard to write something that can be reused.


Actually you don't need a dynamic query, you need a pivot query:

SELECT Nama,
SUM(CASE WHEN Motor = 'Vario' THEN 1 ELSE 0 END) as Vario,
SUM(CASE WHEN Motor = 'Beat' THEN 1 ELSE 0 END) as Beat,
SUM(CASE WHEN Motor = 'Supra' THEN 1 ELSE 0 END) as Supra,
SUM(CASE WHEN Motor = 'Megapro' THEN 1 ELSE 0 END) as Megapro
SUM(CASE WHEN Motor = 'CB150' THEN 1 ELSE 0 END) as CB150
FROM MatrixReport
GROUP BY Nama;

This query will automatically sum up whether new rows are added or not.

This is a SQL query, not an SP. You need to learn more about how to create a stored procedure. Take a look at these Stack Overflow Q & As to help you in creating your first SP:

Beginners' guide to stored procedures with MySQL
Creating a Procedure MySQL
MySQL create stored procedure syntax with delimiter

You should also learn more about GROUP_CONCAT function in MySQL:

Aggregate (GROUP BY) Function Descriptions - GROUP_CONCAT
How to use GROUP_CONCAT in a CONCAT in mysql


PostgreSQL replicas never finish recovering.  This is by design.  Basically a replica is always in "recovering from disaster" mode except that it is using receiving the WAL segments from the master rather than on disk.

So what you are seeing is not cause for concern.  If it is not working yet, then you will need to provide a more detailed description of what you are trying to do and what is not working.  But as far as you are posting it seems normal.