Effect on msdb transaction log of running sp_delete_backuphistory for first time?

You may want to run the deletes in smaller chunks to avoid excessive log file growth for this one-time cleanup, particularly because your msdb database is on the C:\ drive.
If you have 5 years of data to clean up, the easiest way would likely be to simply call sp_delete_backuphistory several times, to clear out the backup history in 6 month or 1-year chunks.
For example:
EXEC sp_delete_backuphistory @oldest_date = '20150101'; --Jan 2015
EXEC sp_delete_backuphistory @oldest_date = '20160101'; --Jan 2016
EXEC sp_delete_backuphistory @oldest_date = '20170101'; --Jan 2017
EXEC sp_delete_backuphistory @oldest_date = '20180101'; --Jan 2018
EXEC sp_delete_backuphistory @oldest_date = '20190101'; --Jan 2019
EXEC sp_delete_backuphistory @oldest_date = '20200101'; --Jan 2020

Simply by running this and experimenting with the @oldest_date, you could pretty easily determine how quickly you can chunk through. Depending on the number of databases and frequency of backups, doing yearly chunks will likely be sufficient. If this is a one-server, one-time cleanup, this would be the method I use.
You could also script out the definition of sp_delete_backuphistory and create your own copy of it that does the cleanup more efficiently. Erik Darling has created an alternative that is available on GitHub distributed as part of my own DBA Database project. I would use this option for ongoing/long term cleanup, and if the problem is more pervasive than a single server needing a one-time cleanup.


Replicate single table from SQL Express to SQL Enterprise 2014

You can use SQL Server Merge Replication for this.  The SQL Enterprise instance would be the publisher, but Merge Replication can synchronize data both ways.  So changes made at the subscriber would be replicated to the publisher.

If you configure this to be a Push Subscription  the agents will all run at the central instance, and you can schedule them with SQL Agent.


How do I upload the .mdf file and associated .ldf file from visual studio to a host using WebsitePanel?

You can check with them, but it's highly unlikely this method of connection is supported by the host.

The preferred way of getting your database set up is to restore a backup (you can do this through WebsitePanel), and follow their instructions for connecting. A host will normally have example connection strings that you can copy/paste into your configuration file.


Does page verification mode affect DBCC CHECKDB behavior?

It is highly recommended to set a database to use Page Verify CHECKSUM.The Checksum is safer and its performance overhead is still small enough.
As discussed in this article.

When changing a database to use CHECKSUM it is important to know that
the option isn’t used for pages that have already been written to disk
and as such it will only be applied to new pages are written or when
existing pages are modified and re-written to the IO subsystem.
A common misconception regarding the CHECKSUM option is that it
replaces the need for consistency checking databases. This is a very
incorrect assumption; as we’ve seen the CHECKSUM error is only
reported on the read operation of an inconsistent page, if the page
isn’t read the error isn’t reported. However the biggest reason is
that the DBCC CHECKDB routine performs a lot more thorough checking at
the database level and includes checks that a CHECKSUM simply doesn’t
cover, therefore the best practice is to combine CHECKSUM page
verification and regular consistency checks.


Multiple WHERE conditions on same column =val1 but doesnt=val2 or val3

Your WHERE clause does not make much sense, because it is applied to each row individually and it is pointless to check if the same value is equal to a and at the same time not equal to b or c – of course, it will not be equal to b or c if it is a. What you want instead, therefore, is for the conditions to be applied to a group of rows as a whole – more specifically, to each group of rows sharing the same txn_id.

So, you need to use GROUP BY and, to apply conditions to groups of rows, HAVING. This query will give you the list of txn_id values matching your requirements:

SELECT
    t.txn_id
FROM
    1_txns AS t
    INNER JOIN 2_products AS p USING (sku)
GROUP BY
    t.txn_id
HAVING
    COUNT(p.rk_group = :group1 OR NULL) > 0
    AND COUNT(p.rk_group IN (:group2, group3) OR NULL) = 0
;

As you do not seem to want the list, only the number of its items, use the above as a derived table to count the rows:

SELECT
   COUNT(*)
FROM
(
    SELECT
        txn_id
    FROM
        1_txns AS t
        INNER JOIN 2_products AS p USING (sku)
    GROUP BY
        t.txn_id
    HAVING
        COUNT(rk_group = :group1 OR NULL) > 0
        AND COUNT(rk_group IN (:group2, group3) OR NULL) = 0
) AS s
;

As you can see, COUNT(DISTINCT ...) is not necessary: the derived table is grouping by txn_id and thus cannot return duplicates – so, COUNT(*) is enough to get the correct result.

In case you are not aware, the OR NULL bit lets the COUNT function count only matches and omit mismatches, as explained in detail in this answer:

Why do I need “OR NULL” in MySQL when counting rows with a condition


Is it any good for performance to have master and slave on the same server

It's very unlikely that running master and slave on the same machine would be beneficial, because the two instances would not actually be sharing resources in any positive or helpful way.

Running two instances with the same data means you have to divide up the available resources in some combination of implicit (cpu, disk, memory including OS cache) and explicit (buffer pool/key cache configuration) ways...

Significantly, the fact that one is a replica of the other means that database writes are contending for double resources at almost the same time: for each write done by the master, a comparable write has to be done by the slave, which means -- approximately -- twice the disk utilization, plus the fact that every binlog entry is written at least twice, first to the master binlog, then to the slave's relay log, then to the slave's binlog if enabled.

The fact that each instance now has less memory available means that less data can be cached in memory, meaning that you will further increase the need for disk access, as less cached data means more disk I/O, almost by definition.

There might be specific scenarios where splitting uo the workload like this could serve a useful purpose, but situations where such a setup would degrade performance seem to significantly outnumber any scenarios where you'd see an improvement... and any such cases would likely be very workload-specific.


MYSQL: log the query executed on certain table

You could use log_output and general_log (USE CAREFUL), for those blessed with MySQL >= 5.1.12:

Steps:

I've created a new table called new_table:

CREATE TABLE new_table (
  id int(11) NOT NULL,
  text varchar(45) DEFAULT NULL,
  text2 varchar(45) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I've created a new log table called new_table_log:

CREATE TABLE test.new_table_log LIKE new_table;

ALTER TABLE test.new_table_log
CHANGE COLUMN text2 text2 VARCHAR(1024) NULL DEFAULT NULL COMMENT '' ;

Trigger:

USE `test`;
DELIMITER $$
DROP TRIGGER IF EXISTS test.new_table_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`new_table_AFTER_INSERT` AFTER INSERT ON `new_table` FOR EACH ROW
BEGIN
    DECLARE vOriginal_query VARCHAR(1024);
    DECLARE vLAST_LO VARCHAR(15) DEFAULT 'NONE';
    DECLARE vLAST_GL ENUM('ON','OFF') DEFAULT 'OFF';
    SET vLAST_LO=@@log_output;
    SET vLAST_GL=IF(@@general_log=0,'OFF','ON');
    SET GLOBAL log_output = 'TABLE';
    SET GLOBAL general_log = 'ON';
    SELECT argument INTO vOriginal_query FROM mysql.general_log WHERE argument LIKE 'INSERT%' AND argument NOT LIKE '%NEW.%' AND thread_id=CONNECTION_ID() ORDER BY event_time DESC LIMIT 0,1;
    INSERT INTO new_table_log(id,text,text2)VALUES(NEW.id,'INSERT',vOriginal_query);
    SET GLOBAL log_output = vLAST_LO;
    SET GLOBAL general_log = vLAST_GL;
END$$
DELIMITER ;

Insert into new_table:

INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST');

Test:

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE new_table (
    ->   id int(11) NOT NULL,
    ->   text varchar(45) DEFAULT NULL,
    ->   text2 varchar(45) DEFAULT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test.new_table_log LIKE new_table;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE test.new_table_log
    -> CHANGE COLUMN text2 text2 VARCHAR(1024) NULL DEFAULT NULL COMMENT '' ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test.new_table;
+----+------+-------+
| id | text | text2 |
+----+------+-------+
|  1 | Lel  | TEST  |
+----+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test.new_table_log;
+----+--------+-----------------------------------------------------------------+
| id | text   | text2                                                           |
+----+--------+-----------------------------------------------------------------+
|  1 | INSERT | INSERT INTO test.new_table(id,text,text2)VALUES(1,'Lel','TEST') |
+----+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE         |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql>


do I need to remove the existing native client and install again?

No you do not need to remove any old native client. Quoting from official document Installing SQL Server Native Client

If a previous version of SQL Server Native Client is also installed on the computer, SQL Server Native Client 11.0 will be installed side-by-side with the earlier version.

Please also note that as per Microsoft after SQL Server 2012 native client download no native clients of future SQL Server versions would be available for download separately and SQL Server 2012 native client can be used for SQL Server 2012 and later versions.

Note that Microsoft will not be releasing a SQL Server 2014 or later version of the SQL Server Native Client. The SQL Server 2012 Native Client (v11, e.g. sqlncli11.dll), can continue to be utilized by SQL Server 2014 and later versions.


Select full record with minimum value for column grouped by joined value

you are missing on {condition} for your last join :
however I'm not sure
select images.*, unit_id
from images
join images_units
    on images.id = images_units.image_id
join images_sites
    on images.id = images_sites.image_id
join (
    select min(sort) as minSort, unit_id as matchID
    from images
    join images_units
        on images.id=images_units.image_id
    group by matchID
    ) as sq
    on sq.matchId = ??  -- <-- here add the proper join condition
where
images.sort = minSort
and a.unit_id = matchID
and site_id = xyz;


wait on database engine handle recovery failed

It sounds like you have TLS 1.2 enabled, but:

you're not running a build of SQL Server 2012 that supports TLS 1.2, and / or
you're not running a version of the .NET Framework that supports TLS 1.2

The following KB lists out the 2012 builds that support TLS 1.2:

TLS 1.2 support for Microsoft SQL Server
- SP2
  - 11.0.5644.2 (CU 10) or
  - 11.0.5352.0 (GDR update)
- SP3
  - 11.0.6518.0 (CU 3)
  - 11.0.6216.27 (GDR update)

The .NET Framework version you need depends on your Windows Server version, and is listed out in the following KB article:

FIX: Error when you install SQL Server 2012 or SQL Server 2014 on a server that has TLS 1.2 enabled

It's also possible that you don't have TLS enabled at all, which is required to run the product.  So the solution may be that you just need to enabled TLS 1.0 or SSL 3.0 (although 1.2 is recommended).


Foreign keys can be made conditional...sort of. You don't show the layout of each table, so here is a typical design showing your relationships:

create table TransactionalStores(
    ID        int   not null auto_increment,
    StoreType char  not null,
    ..., -- other data
    constraint CK_TransStoreType check( StoreType in( 'B', 'K', 'O' )),
    constraint PK_TransactionalStores primary key( ID ),
    constraint UQ_TransStoreTypes unique( ID, StoreType ) -- for FK references
);
create table Kiosks(
    ID         int   not null,
    StoreType  char  not null,
    ..., -- other Kiosk data
    constraint CK_KioskStoreType check( StoreType = 'K' ), -- kiosks only
    constraint PK_Kiosks primary key( ID, StoreType ),
    constraint FK_Kiosks_TransStores foreign key( ID, StoreType )
        references TransactionalStores( ID, StoreType )
);

The Onlines and BrickMorters would have the same basic structure but with StoreType constrained to only 'O' or 'B' as appropriate.

Now you want a reference from another table to TransactionalStores (and through it to the various store tables) but limited to Kiosks and BrickMorter. The only difference would be in the constraint:

create table Employees(
    ID         int       not null,
    StoreID    int,
    StoreType  char,
    ..., -- other Employee data
    constraint PK_Employees primary key( ID ),
    constraint CK_Employees_StoreType check( coalesce( StoreType, 'X' ) <> 'O' )), -- Online not allowed
    constraint FK_Employees_TransStores foreign key( StoreID, StoreType )
        references TransactionalStores( ID, StoreType )
);

In this table, the FK reference forces StoreType to be either 'K', 'O' or 'B' but the field constraint further limits it to only 'K' or 'B'.

For illustration, I've used a check constraint to limit the store types in the TransactionStores table. In real life, a StoreTypes lookup table with StoreType being a FK to that table would probably be a better design choice.


query to replace text with sequential numbers

Use a variable to get an auto-incremented value and update description using this value. Or use Rick James's answer and add a sequenced column to your table as a number for each picture.

create table t (name varchar(10), description varchar(100));
insert into t values ('b', '"Photo number # - Gallery name"');
insert into t values ('c', '"Photo number # - Gallery name"');
insert into t values ('a', '"Photo number # - Gallery name"');
insert into t values ('x', '"Photo number # - Gallery name"');

update t
join (select
          name, @num := @num + 1 as num
      from
          t, (select @num := 0) t
      order by
          name) t2
on t2.name = t.name
set description = replace(description, '#', cast(num as varchar(10)));

select * from t order by name;

name | description
:--- | :------------------------------
a    | "Photo number 1 - Gallery name"
b    | "Photo number 2 - Gallery name"
c    | "Photo number 3 - Gallery name"
x    | "Photo number 4 - Gallery name"

db<>fiddle here


Normalization and Breaking down tables for InnoDB

This is how I see it, it's better to have it separate, that's relational database

If N User can have 1 address, the design should be:

if N user can have M address (user with more address, and an address for multiples user), should be:

For both design, the table USER, Column ID is the Primary key of that table, and User_id is just a FK.
In the second design, Table Address, column ID, it's another PK, that make a relation N:M with user, and create "user_has_table" with 2 foreign keys (user PK and address PK).


You need to create a temporary table with the merged data, then remove the old table and then rename the temporary table with the correct table name.

EXAMPLE:

CREATE TABLE table_temp SELECT user,is_su,last_login,GROUP_CONCAT(roles-for_groups) AS roles-for_groups FROM table1 GROUP BY user;

Check if table_temp is ok and then you can remove your old table.

DROP TABLE table1

The last step, rename the new/temporary table to the actual table name

RENAME TABLE table_temp TO table1;

PS: I'm assuming your table name is table1 because you never posted the name.


Creating many-to-many relation on demand?

In your scenario, since (if I'm understanding correctly), every User will need to complete every Task in the Projects they belong to, there will need to be one UserTask per Task per User if you want to store things such as dateCompleted.

Because of this, Option A makes sense to me. You need to be "reacting to these events all the time and acting in response" anyway. One modification I would make though is that I would include a defaultHideOnComplete field in the User table. The TaskUser's hideOnComplete field would be initially set to the defaultHideOnComplete value for the User associated with it.

Option B doesn't make sense to me with what you're trying to accomplish, because it doesn't allow for every User to set their own hideOnComplete and priority values.

Let me know if I'm misunderstanding something : ]


Error creating table (Error in your sql syntax). What am I doing wrong?

CREATE TABLE Customer ( Customer_ID INTEGER(8) PRIMARY KEY, f_Name VARCHAR(25) NOT NULL, l_Name VARCHAR(25) NOT NULL);

CREATE TABLE Users ( Manager_ID INTEGER(8) NOT NULL, Employee_ID INTEGER(8) NOT NULL, f_Name VARCHAR(25) NOT NULL, l_Name VARCHAR(25) NOT NULL,
    CONSTRAINT Users_Manager_ID_PK PRIMARY KEY(Manager_ID, Employee_ID),
    CONSTRAINT idx_unque_users UNIQUE KEY (Employee_ID,Manager_ID));

CREATE TABLE Advertising ( Sales_Person VARCHAR(25) PRIMARY KEY, Manager_ID INTEGER(8) NOT NULL,Employee_ID INTEGER(8) NOT NULL,
    CONSTRAINT Advertising_Manager_ID_FK FOREIGN KEY (Manager_ID) REFERENCES Users(Manager_ID),
    CONSTRAINT Advertising_Employee_ID_FK FOREIGN KEY (Employee_ID) REFERENCES Users(Employee_ID));


Creating Bitmap Indexes on a Materialized View

The key phrase is "after batch row loading".  If your process does not meet this criteria then you do not need to drop/rebuild the bitmap indexes.
Jonathan Lewis has an excellent article on this.  A key point he gives is

You can get lucky -- but in general you should start with the
assumption that even a serialized batch update will be most effective
if you drop the bitmap  indexes before the batch and rebuild them
afterwards.

The whole article is good and ends with some key facts to remember about bitmap indexes:

If a B*tree index is not an efficient mechanism for accessing data, it is  unlikely to become more efficient simply because you convert it to a bitmap  index.

Bitmap indexes can usually be built quickly, and tend to be surprisingly  small.

The size of the bitmap index varies dramatically with the distribution of  the data.

Bitmap indexes are typically useful only for queries that can use several  such indexes at once.

Updates to bitmapped columns, and general insertion/deletion of data can  cause serious lock contention.

Updates to bitmapped columns, and general insertion/deletion of data can  degrade the quality of the indexes quite dramatically.


Using sub queries this should extract all ID_Ps which only occur in the Layout table once and then INNER JOIN them with all ID_Bs which only occur in the Layout table once... essentially returning only ID_Ps with no more than one box per palette, and no more than one palette per box.

SELECT x.ID_P FROM
  (SELECT ID_P, ID_B, COUNT(*) as count FROM Layout GROUP BY ID_P) x
  INNER JOIN
  (SELECT ID_P, ID_B, COUNT(*) as count FROM Layout GROUP BY ID_B) y
WHERE x.count = 1 AND y.count = 1 AND x.ID_P = y.ID_P;


You have two physical NUMA nodes (Memory Nodes), with Automatic Soft-Numa dividing each one into two NUMA nodes in SQL Server.

I'd like to understand the meaning/importance of VAS Reserved

Virtual Address Space (VAS) is reserved as a first step to allocating memory, see: Reserving and Committing Memory.  For 64bit processes the VAS is immense, and so it's common practice to reserve a huge block of it for later use.  This doesn't allocate any physical memory, it just reserves a contiguous range of VAS.  Later the application will "Commit" chunks of this address space to map the VAS to either physical RAM or the page file.
And in a NUMA system the memory allocation happens on the same node that the code is running on.  So that query just shows that a large VAS range was reserved on Node 0, probably at startup.
But SQL Server also supports a secondary method of memory allocation using the Address Windowing Extensions APIs originally added to Windows to allow 32bit applications to address more than 4GB of ram.  AWE allocations have the benefit of being exempt from the Virtual Memory Manager paging.  SQL Server calls memory allocated this way Locked Pages, and will use them if it has the Lock Pages In Memory permission.  AWE allocations won't show up under Reserved or Committed VAS, and are tracked separately in SQL Server's DMVs.
To to see the total amount of memory in use by a node you need to add virtual_address_space_committed_kb and locked_page_allocations_kb
sys.dm_os_memory_nodes
And, no I have no idea why sp_Blitz reports on VAS reservations instead of real memory allocations.


What you can do is set the output of your query to concat a string variable.  Something like the following:

declare @sql_command varchar(max);
set @sql_command = '';

SELECT @sql_command += 'DROP LOGIN [' + name + ']' + char(13)
FROM [master].[dbo].[syslogins]
WHERE isntgroup = 0
  AND isntuser = 0
  AND sysadmin = 0
  AND name != 'sa'
  AND name NOT LIKE '##%'
ORDER BY name

print @sql_command;
-- exec (@sql_command);

The PRINT will show you your command, and I've commented out the EXEC but that'll run the generated SQL.  Note, you'll have to uncomment the EXEC for anything to actually happen (be wary beforehand, and ensure this is the behavior you're looking for).

As for the one step to generate, and the other to execute, I'm not quite sure what you'd gain by doing that.  It doesn't sound like you're giving yourself time to "review" the drop statements prior to execution.  But, either way, you can store the statements in a table in one step then execute the recently generated DROP LOGIN commands in a subsequent step if you so choose.  That'd be one way to go about that requirement.

I realize you probably have a requirement for the above task, but please note that you are asking to create a SQL Server Agent job to do something not-so-routine such as dropping server principals.  This may call for seeing why there is actually a requirement and working on the situation at the cause of needing to drop logins rapidly on a reoccurring basis.  Again, just something to think about.


Independent serial for each foreign key value

You can't do this with SERIAL.

You could create a SEQUENCE for each owner_id but that'd be painful and scale poorly.

Instead, use a counter table where each owner_id has a separate counter, and

update players_per_user_counter
set next_val = next_val + 1
where player_id = $1
returning next_val

to get new identifiers. For more info search for "PostgreSQL gapless sequence".

See:

https://stackoverflow.com/q/9984196/398670


How to configure account to be able to create/delete folders and files?

You might find a better answer asking this question on ServerFault but in general I believe you'd just have to add that domain user to the Folder Security tab of the root folder where files will be created and deleted. You'll likely want to give that user Read, Write, and Modify permissions in the Security tab. Please see this article for more information.


How to programmatically find out where LocalDb is installed

Checking from the registry may not work all the time becasue if user uninstall the localDb then registry entries may still exist.

Here is the function I am using to identify the localDB installation from command line -

internal static bool IsLocalDBInstalled()
{
    // Start the child process.
    Process p = new Process();
    // Redirect the output stream of the child process.
    p.StartInfo.UseShellExecute = false;
    p.StartInfo.RedirectStandardOutput = true;
    p.StartInfo.FileName = "cmd.exe";
    p.StartInfo.Arguments = "/C sqllocaldb info";
    p.StartInfo.CreateNoWindow = true;
    p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
    p.Start();
    // Do not wait for the child process to exit before
    // reading to the end of its redirected stream.
    // p.WaitForExit();
    // Read the output stream first and then wait.
    string sOutput = p.StandardOutput.ReadToEnd();
    p.WaitForExit();

    //If LocalDb is not installed then it will return that 'sqllocaldb' is not recognized as an internal or external command operable program or batch file.
    if (sOutput == null || sOutput.Trim().Length == 0 || sOutput.Contains("not recognized"))
        return false;
    if (sOutput.ToLower().Contains("mssqllocaldb")) //This is a defualt instance in local DB
        return true;
    return false;
}


Postgres: Disable "similarity" column output on "SELECT SIMILARITY"?

Generally, you can always wrap a query as subquery if you don't want to output all rows:

SELECT title FROM (SELECT ...) sub;

But you can also use expressions in ORDER BY, not just input or output columns. So there is no need for this (like @ypercube already commented).

For the case at hand, it must be mentioned that you are using the additional module pg_trgm. What you are trying to do can be simplified to:

SELECT title
FROM   gallery
WHERE  title % 'Nation'
ORDER  BY title <-> 'Nation'
LIMIT  5;

I replaced SIMILARITY(title, 'Nation') DESC with the simpler, equivalent title <-> 'Nation', since the <-> operator (per documentation):

  Returns the "distance" between the arguments, that is one minus the similarity() value.

Either version works, but the latter is shorter and index support is bound to the operator, not the function. And you should have a GiST index on title.

Details:

PostgreSQL FTS and Trigram-similarity Query Optimization

Also relevant:

Finding similar strings with PostgreSQL quickly


Chances are that a lot of data has been updated or deleted, or you have adjusted the size of some of your columns, or you have truncated / deleted the table and re-loaded many times. Some of these operations can lead to un-reclaimed space in some scenarios. I would try this first:

ALTER TABLE schema.table REBUILD;

Next I would consider adding a clustered index.


Performance improvement by placing tables on a virtual RAM disk

Why bother with a RAM disk, when you can just increase SGA, increase buffer cache, define KEEP pool, use supported, built-in tools?

Why lie to the database, and make it think it uses disk and perform all the related extra unnecessary operations, while it uses memory indirectly ineffectively?

Why risk database corruption and losing the data by using memory as "persistent" storage?

What makes you think the database worked for 5-6 minutes? Did you check what the database really did?

Even if the database read the whole table in let's say, 20 seconds, fetching and displaying 350 million rows takes a lot more. People often think wall clock time = execution time, but that is not true.

Forget EXPLAIN PLAN, that is just an assumption. At least, check the following:

alter session set statistics_level=all;
SELECT PRICE, QUANTITY
FROM TEST_TABLE;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

Run the above for both tests, and compare the results.

Or enable SQL tracing. Check at least the "disk I/O" statistics between the test. Measure your tests.


Your query is invalid SQL, but MySQL accepts it with the default settings. It will give you a random result. You can change this behavior by adding 'ONLY_FULL_GROUP_BY' to sql_mode.

EDIT: Similar to @MickyT's solution but using a join instead:

SELECT M.*
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);

It is not clear what time that should be used in case of a draw, the query outputs all times where a min or max temp occurred. Assuming that you would like the first occurrence, something like:

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
GROUP BY M.AMBIENT_TEMPERATURE

can be used.

I left out the predicate for the current month, but that is easily added. Since MySQL does not support LATERAL join, it is probably better to add that predicate to the derived table as well. I.e.

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
GROUP BY M.AMBIENT_TEMPERATURE

As @RolandoMySQLDBA points out the WHERE clause can be rewritten in a index friendlier way.


If you restore a database as a new database, the dbi_crdate field is the restore date/time.

Here's how to reproduce it:

Create a new database.
Run DBCC CHECKDB() on it.
Look at DBCC DBINFO() for the creation date and DBCC dates.
Back up the database.
Delete the database.
Restore the database (if you want to get fancy, use a different database name.)
Look at DBCC DBINFO() for the creation date and DBCC dates. The creation date will be the restore time, and the DBCC date will be earlier.


Passing parameters for dynamic SQL in a function

To make it work dynamically, do something like this:
CREATE OR REPLACE FUNCTION return_result_table(_param1 integer[], _param2 text[])
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE
   _table_name text := to_char(current_date, '"_XYZABC_"YYYY_MM_DD"_ZXCVBN"');  -- equivalent, but still not the real code
   _select     text := $$(select * from some_other_function($1, $2))$$;  -- !
BEGIN
    EXECUTE format('CREATE TABLE some_schema.%I AS ', _table_name)
         || _select
    USING _param1, _param2;

    RETURN _table_name;
END
$func$;

db<>fiddle here
This passes parameters as values to EXECUTE with the USING clause, which is cheapest and safest.
$N ($1 and $2) in the string passed to EXECUTE refer to appended arguments, not to function parameters like it would in SQL code outside of EXECUTE. (Ordinal numbers are in sync only by chance.)
Note this restriction:

Another restriction on parameter symbols is that they only work in
SELECT, INSERT, UPDATE, and DELETE commands. In other
statement types (generically called utility statements), you must
insert values textually even if they are just data values.

The manual is a bit fuzzy there. The above command CREATE TABLE AS is just such a utility statement, but parameter passing still work because the SELECT statement is evaluated on its own before results are passed to CREATE TABLE AS.
format() is instrumental to make it simpler and safer, too.
The used format specifier %I preserves the name as given (case sensitive) adding double quotes as needed automatically. (No SQL injection possible.)
Note that I only use nested dollar-quoting to fix the currently broken syntax highlighting of the site. Else, it can just be:
  _select     text := '(select * from some_other_function($1, $2))';  -- !

There are quite a few related answers with more details here on dba.SE. Try a search for [postgres] [dynamic-sql] format code:EXECUTE code:USING
Prominently:

How to use function parameters in dynamic SQL with EXECUTE?

Also:

Table name as a PostgreSQL function parameter


Mysql is not really my usual dbms, but looking at your sample I would suggest you another schema by adding [chapter], and use it like a hierarchyid field of MS Sql.

+----+----------+-------------+-----------+
| id | guide_id | description | chapter   |
+----+----------+-------------+-----------+
|  1 |        1 | Step 1      | /1        |
|  2 |        1 | Step 2      | /1/2      |
|  3 |        1 | Step 2.1    | /1/2/1    |
|  4 |        1 | Step 2.2    | /1/2/2    |
+----+----------+-------------+-----------+

Then indexing by guide_id + chapter you can fetch the whole tree, or you can get one chapter using:

WHERE chapter LIKE '/1/2%' ORDER BY chapter


Why does this partitioning helper view give an 'Input string was not in a correct format' error?

The problem lies down in one of the sources used by that view, the system view sys.partition_range_values. The Value column of this view is of type SQL_VARIANT, which can lead to problems with indiscriminate SELECT *s, because SQL has to pick a specific type for the resultset, which all the values then need to be coerced to (Thanks to this blog post for this insight).

You can see for yourself if this is what's causing your problem, by doing

SELECT
    function_id
    , boundary_id
    , parameter_id
    , SQL_VARIANT_PROPERTY(Value, 'BaseType') AS DataType
FROM
    sys.partition_range_values
;

If you have multiple different DataTypes, and you can imagine there might be problems casting values between them, then that's the problem.

To fix, you can update your helper view so that instead of

prv.value as [boundary_value]

instead you have

CASE SQL_VARIANT_PROPERTY(prv.Value,'BaseType')
WHEN 'datetimeoffset' THEN CAST(CAST(prv.Value AS datetimeoffset) AS varchar(30))
WHEN 'smallint' THEN CAST(CAST(prv.Value AS smallint) AS varchar(30))
-- etc as necessary for the types used to partition on in your database
ELSE SQL_VARIANT_PROPERTY(prv.Value,'BaseType')
ELSE 'Value of type ' + CAST(SQL_VARIANT_PROPERTY(prv.Value,'BaseType') AS varchar(15))
END
    AS boundary_Value

This will give you appropriate textual representations of the partition range values.


How can I narrow down the cause of a deadlock after the fact?

When the timeouts occur, do you see any blocking?  You can run sp_who2 to determine that.  It sounds like you are having intermittent performance issues.  You need to look at your wait types to see if there is a bottleneck.  Activity Monitor will show you the top resource waits.  The waits will help you determine where to focus your attention.  Check the SQL Errorlog, System & Application event log for any dumps or entries that provide clues into what is happening.


Find out number of active locks on a table

Here is a start. Remember that locks can go parallel so you may see the same object being locked on multiple resource_lock_partition values.

USE yourdatabase;
GO

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.yourtablename');

Please look at the documentation for sys.dm_tran_locks


How to optimize this Select MAX() SQL query?

As mentioned in the comments, an index on StartDate will let your query seek right to the row it needs, rather than scanning the whole table or another index (which is what it's likely doing right now):

CREATE NONCLUSTERED INDEX IX_StartDate
ON dbo.ProductActivity (StartDate DESC);

Like any other index, this one will need to be maintained anytime a row is inserted into, or deleted from, the ProductActivity table.  And anytime the StartDate column is updated (although that seems unlikely based on the name alone).

This doesn't seem like it would add a lot of overhead, but you should test with your workload to be sure.  If the MAX(StartDate) is needed very often, it's probably worthwhile to keep this index around.

If there are lots and lots of writes to this table (hundreds or thousands per second), and the MAX(StartDate) isn't needed often, then you might be better off just periodically waiting the 1 minute or so for that query to finish rather than paying the overhead on each write.


Single or multi tenancy database recommendations

There are diverging views on this (thorny) topic within the database world!

However, the consensus (as best as I can "take the pulse" of the database community is, if at all possible, to use separate databases!

If one app goes down, at least the others will still work, with a
central database, if one app brings the server down, you're bunched
(nothing will work).
Also, for migrations, if you need to, say, migrate one of your apps
(size/speed/whatever problems - or you wish to dip your toe into the
cloud testing with one app), then you will have difficulty separating
out your apps, whereas if you use separate systems, you won't have
any difficulties on that score!

In 10 years time, when you tell people that you have software on-prem(-ises), you'll get strange looks and you'll be taken for a Luddite (think generating your own electricity in the basement which did happen before the rise of electricity companies, esp. in US cities) so best be prepared for the cloud ahead of time.

You mention NoSQL or MySQL - my strong advice is to go with PostgreSQL if at all possible - it is vastly superior to MySQL on a large number of levels and NoSQL is a bandwagon whose time has come and gone - all the major vendors are scrabbling to put SQL interfaces and ACID semantics on their products.

Take the time to look here at my answers and the other posts and links in the threads there to get some "feel" for the area.

HTH (oh, and did I mention that you should give PostgreSQL careful consideration? :-) ). I'm not saying that my suggestion above is the definitive answer for all scenarios - I just believe that it is (in the majority of cases) the go-to solution and, unless you have a compelling reason to the contrary, you should go with multiple separate databases. Modern servers can cope with thousands of schemas - and if you have 1000's of clients, maybe you can afford more than 1 machine? :-)


(A possible answer.)

Setting a global variable (anyone, not just this one), does not take effect immediately:

If you set it in my.cnf (etc), it would take effect until after a restart
If it is set globally (SET GLOBAL @@... ), it won't take effect until someone logs in -- he then gets that setting.  (Caveat: Some globals have no "session" counterpart, so they don't act this way.)
If you set it in your session, it does not apply to anyone else.

If this does not answer the question, please show us precisely how you set it.  And provide SELECT @@version;


Where does each MySQL data file type belong?

That's fine, just you'll likely need to reconfigure you bin and tmp locations in your .cnf file after you move the files.
Here's an article specifically about reconfiguring the tmp location (I would imagine the bin directory is similar): How to change your MYSQL tmpdir


SQL Server AS: Set ImpersonationMode via PS

Got it working:

$ImpInfo=New-Object Microsoft.AnalysisServices.ImpersonationInfo
$ImpInfo.ImpersonationMode = 'ImpersonateServiceAccount'

$db.DataSourceImpersonationInfo = $ImpInfo
$db.update()

here a full working PowerShell (PS) example

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$DBASserver = New-Object Microsoft.AnalysisServices.Server
$DBASserver.connect("mysqldbserver.dom.com")

$database=$DBASserver.databases
$database|select-object name, id

$DBASserver.databases.add("Finance5")

$db = $DBASserver.databases.item("Finance5")
$db.update()
$db.description = "Finance Test 5"
$db.update()

$db = $DBASserver.databases.item("Finance5")
$dbmode = $db.DataSourceImpersonationInfo
$dbmode #This doesn't post info, maybe a refresh issue.

$ImpInfo=New-Object Microsoft.AnalysisServices.ImpersonationInfo
$ImpInfo.ImpersonationMode = 'ImpersonateServiceAccount'

$db.DataSourceImpersonationInfo = $ImpInfo
$db.update()
$db.description = "Finance Test 5 now changed ImpersonationInfo"
$db.update()
$dbmode = $db.DataSourceImpersonationInfo
$dbmode


how to prevent multiple pg_dump to execute at the same time

You could check if there is running pg_dump process by looking at the pg_stat_activity and also the state of the process:

SELECT
    pid,
    datname,
    usename,
    state
FROM pg_stat_activity
WHERE
    application_name = 'pg_dump';

It would be better if you could also lock some file with your script when you are starting pg_dump and check the lock status of that file when starting new pg_dump to avoid re-triggering.


A transport-level error has occurred when sending the request to the server

If an application opens a connection (successfully) to SQL Server, and leaves it open in anticipation of sending a (or possible 'another') query, and in the meantime, SQL kills that connection for some reason (eg, service restarts, or a kill command is sent), then when the connection (which thinks it's open) tries to send a command, you'll get this error.

So catch the error in your application, and reconnect. Then try the command again.


How to select a particular element from json array

IF column is json:

 SELECT id
  FROM notifications n
  WHERE json_array_elements(n.dest_user_id_arr->'users') = 83;

If column is jsonb:

 SELECT id
  FROM notifications n
  WHERE jsonb_array_elements(n.dest_user_id_arr->'users') = 83;


SQL Replication: Which tables/views should be a part of replication?

I have replicated entire databases, performance is an issue with frequently changed data when your talking larger volumes. For us, a larger amount of data was 500gb of data being supported by 32gb of memory and 8 cores (didn't have control or say in hardware specs). We had transactional replication, and about 1-2 times a month it would get behind, far enough behind where we had to re-snapshot it to get our subscriber current (this would take us about 4 hours each time). Whenever replication had an issue and data started to queue up, it just couldn't catch up.


SQL Server Availability Groups between virtual server and a bare metal sql server

I have had no issues with Mirroring between a hardware & VM. As long as the servers are very similar (ie: same version of SQL Server, same RAM given, data files are named & stored in the same locations, etc).

Then it should be exactly the same for Availability Groups. You shouldn't see a difference.

  My concern is AG utilizes windows failover clustering - an unfamiliar territory for me - so I'd like to know if there's anything additional to look out for.

The biggest thing is quorum. In mirroring there was the option to use a witness instance for HA which sort of but not really acted like quorum. I say sort of because it didn't really vote because there could only ever be two instances in mirroring. Availability Groups rely on WSFC (except read-scale AGs, though they can have an optional metadata instance - and anything on Linux) which means there will be voting to determine quorum.

Here is a small introductory, there is more out there but this should give you an idea what you'll want to research.


Grouping data with an interval but with overlapping time range groups

Below is one technique you could use to get counts for the overlapping ranges. This method this uses an inclusive start and exclusive time range to ensure datetime values with fractional seconds don't get dropped on the floor (e.g. '2018-01-28 00:59:59.123') but still reports the end time without fractional seconds like the data in your question.

WITH
    intervals_15_minutes AS (
        SELECT DISTINCT
             DATEADD(MINUTE, (DATEDIFF(MINUTE, '', DATETIME_COLUMN) / 15)*15, '') AS TIME_START_INTERVAL
        FROM dbo.YourTable
    )
SELECT
      CAST(TIME_START_INTERVAL AS date) AS DATE
    , CAST(TIME_START_INTERVAL AS time(0)) AS TIME_START_INTERVAL
    , CAST(DATEADD(second, -1, DATEADD(MINUTE, 60, TIME_START_INTERVAL)) AS time(0)) AS TIME_END_INTERVAL
    , COUNT(*) AS OVERLAPPING_COUNT
FROM  intervals_15_minutes
JOIN  dbo.YourTable ON
    YourTable.DATETIME_COLUMN >= intervals_15_minutes.TIME_START_INTERVAL
    AND YourTable.DATETIME_COLUMN < DATEADD(MINUTE, 60, TIME_START_INTERVAL)
GROUP BY
      intervals_15_minutes.TIME_START_INTERVAL;


Or, to retrieve all rows, just press the blue data grid.

The tooltip "View data in the selected object" is almost making it too easy.
Try the button with the funnel next to it to retrieve only a selection (for big tables).


Welcome to the site, very nice first post that includes table ddl, well done!

As indicated in comment, OLD exists only in update and delete triggers. Furthermore, there is a spelling error on rentamiento (at least it is spelled differently in your table).

For changes of the row under insertion, typically a BEFORE trigger is used. You can use something like:

DELIMITER $$
CREATE TRIGGER categoria
BEFORE INSERT on automoviles
FOR EACH ROW
    SET new.categoria = CASE WHEN new.rentamiento BETWEEN 15 AND 25 THEN 'a'
                             WHEN new.rentamiento BETWEEN 26 AND 35 THEN 'b'
                             WHEN new.rentamiento BETWEEN 36 AND 50 THEN 'c'
                             ELSE 'd'
                        END;
$$
DELIMITER ;

I added a fallthrough case, which may or may not be necessary.


SQL Server express is a good choice.  You can also create a very low cost (a few bucks a month) database only on Azure.  In that scenario there would be no maintenance.  I would argue SQL is what you are dealing with when working on data and Excel can easily connect to a table.  This may be your best solution.


Using of delete obsolete in Oracle 11gR2 RMAN on Linux

Tell RMAN what you want/need to keep and let it do the worrying.

CONFIGURE RETENTION POLICY TO REDUNDANCY 2 ;

That will keep two copies of your database and consider anything before that as obsolete.

I am a little concerned that you say you're only taking "full backups".

Are you backing up the ControlFiles?  The ArchiveLogs?
Without the former, you may have problems restoring a database after changing its structure (removing or even adding DataFiles) and without the latter, you lose any ability to perform a Point-in-Time Recovery.


Yes, this is possible, though not recommended. You can create a user with an empty string as username, also known as the anonymous user. It matches any username. This is the only possibility, wildcards like in the hostname (i.e. 'user'@'%.any.domain.de') are not allowed.

GRANT ALL ON my_database.* TO ''@'localhost';


Is it a good idea to use a personal number as a primary key for a customer table?

Some good criteria for choosing or designing keys are: Simplicity, Stability and Familiarity but first you need to understand the fundamental business requirement your key is supposed to address. Ask yourself how you intend to identify customers in your business processes. Assuming you have a requirement to identify customers and Customer Number is the means your business will use to do that then it makes perfect sense as a key in your customer table - not necessarily the only key, but certainly a key of that table.


Troubleshooting / traceroute within Oracle Instant Client?

There is no trcroute for the instant client, but even if you had the full administrator client, trcroute may not return anything useful.

  By the way, from the command line I can ping the destination DB (defined in tnsnames.ora) just fine, so I believe I can completely
  rule out the networking (e.g. DNS/firewall/accessibility).

And that is the mistake people usually make, based on a false assumption. Just because you can ping, let's say, even tnsping the database server, does not mean at all you will be able to connect or name resolution works properly. Ping and tnsping checks work only up to the first entry point in a database connection, and they stop there, these tools alone are not enough for testing database connections.

Based on my experience, ORA-12545/TNS-12545 is a result of indirect name resolution problems. Even the official description suggests trying IP addresses instead of host names:

12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

Whenever you connect through a listener, the listener may forward your request to another address that a participant does not understand => ORA-12545.

Make sure your client and server can resolve all addresses specified in the connection string, especially the addresses that appear in the output of lsnrctl services on the database server.

And below is an example.

My client:

[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.6.2     o62.balazs.vm o62
192.168.12.5    o6ca-scan.balazs.vm o6ca-scan

My TNS entry:

[oracle@o62 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
MINDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mindb)
    )
  )

Run some checks:

[oracle@o62 ~]$ ping o6ca-scan.balazs.vm -c 3
PING o6ca-scan.balazs.vm (192.168.12.5) 56(84) bytes of data.
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=1 ttl=64 time=0.090 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=2 ttl=64 time=0.111 ms
64 bytes from o6ca-scan.balazs.vm (192.168.12.5): icmp_seq=3 ttl=64 time=0.094 ms

--- o6ca-scan.balazs.vm ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 1998ms
rtt min/avg/max/mdev = 0.090/0.098/0.111/0.012 ms

[oracle@o62 ~]$ tnsping mindb

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:35:06

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = o6ca-scan.balazs.vm)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mindb)))
OK (0 msec)

I can ping the host in my TNS entry, even tnsping works, so I will be able to connect, right? Well, no:

[oracle@o62 ~]$ sqlplus bp/bp@mindb

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:36:06 2016

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

ERROR:
ORA-12545: Connect failed because target host or object does not exist

So let's check what trcroute says:

[oracle@o62 ~]$ trcroute mindb

Trace Route Utility for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:36:59

Copyright (c) 1995, 2014, Oracle.  All rights reserved.

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP  HOST=o6ca-scan.balazs.vm  PORT=1521

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
07-APR-2016 18:36:59 ADDRESS= PROTOCOL=TCP  HOST=o6ca-scan.balazs.vm  PORT=1521

No errors, everything should be OK, still, I can't connect. So much for trcroute.

Now let's do some real troubleshooting, and check the server side. The address I am connecting to:

[oracle@o6ca1 ~]$ lsnrctl status listener_scan1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:40:13

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-APR-2016 18:15:18
Uptime                    0 days 0 hr. 24 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/grid12102/network/admin/listener.ora
Listener Log File         /opt/oracle/base/diag/tnslsnr/o6ca1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.12.5)(PORT=1521)))
Services Summary...
Service "mindb" has 2 instance(s).
  Instance "mindb1", status READY, has 1 handler(s) for this service...
  Instance "mindb2", status READY, has 1 handler(s) for this service...
The command completed successfully

And the services registered here:

[oracle@o6ca1 ~]$ lsnrctl services listener_scan1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-APR-2016 18:39:37

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Services Summary...
Service "mindb" has 2 instance(s).
  Instance "mindb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=o6ca1-vip.balazs.vm)(PORT=1521))
  Instance "mindb2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=o6ca2-vip.balazs.vm)(PORT=1521))

Notice in the above output, the mindb service has 2 instances/handlers, with different addresses. Whenever I connect to o6ca-scan.balazs.vm, my requests will be forwarded to o6ca1-vip.balazs.vm or o6ca2-vip.balazs.vm. My client can't understand those addresses, but if I fix it:

[oracle@o62 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.6.2     o62.balazs.vm o62
192.168.12.3    o6ca1-vip.balazs.vm o6ca1-vip
192.168.12.4    o6ca2-vip.balazs.vm o6ca2-vip
192.168.12.5    o6ca-scan.balazs.vm o6ca-scan
[oracle@o62 ~]$ sqlplus bp/bp@mindb

SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 7 18:45:13 2016

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

Last Successful login time: Thu Apr 07 2016 18:45:07 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL>

The above is a very common mistake when using Oracle RAC. But ORA-12545 is not specific to RAC, that can happen in single instance environments as well. All you need is a shared server configuration, and name resolution problems on the database server. Same story short:

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Services Summary... Service "exdb" has 1 instance(s).   Instance "exdb", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D000" established:55 refused:0 current:15 max:1022 state:ready
         DISPATCHER <machine: o61.balazs.vm, pid: 592>
         (ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=42571))

I have a dispatcher listening on o61.balazs.vm. If I request a shared server connection, and the listener tries to forward the request to o61.balazs.vm, but can not resolve that address, that will result an ORA-12545 as well.


If you want exactly the 2 words, do

... AGAINST('"pto request"' IN BOOLEAN MODE)

If you want some context:

SELECT ...
    SUBSTRING(content,
              GREATEST(1, LOCATE('pto request', content) - 20),
              50)
FROM ...


PostgreSQL full text search words delimiter

The "natural" way to do this would be to change or write your own text search parser.  While it is possible to do that, it is unreasonably difficult.

Do you want both '11.22.33.44' and '33' to show up as individual tokes, or just '33'?  If the latter, then maybe just preprocess your data by replacing all '.' with ' ':

replace(doc,'.',' ')

Of course you should do the same replacement on the queries as well before passing them to plainto_tsquery.


What are the MariaDB/MySQL servers types, and how do they differ?, e.g., 'standalone' vs 'embedded', vs ...?

"embedded" is for applications that link to a server library and run a database internal to their application. They don't have a connection and all their SQL is via library calls. These aren't that common.

"standalone" seems most appropriate for your use.

embedded and "networked (including sockets)" are the main two server types. Withing networked there are MySQL cluster and MariaDB's galera as special kinds of networked configurations.


How do I design a database to store quotes online?

Your second option is better, though I'm confused: is that a Quotes table? If so, are those two records the same quote or different quotes? Also, there's no need to store total, you can calculate that when you need it. I'd generally say that calculated values should only be stored if the calculations are very complex and long-running.

Here's a simple table structure for the quotes that might help get you started:

Quotes
------
  quote_id (primary key)
  client_id - so you know who the quote belongs to.
  date - when the quote was generated

Quote_Items
-----------
  quote_item_id (primary key)
  quote_id (foreign key to Quotes.id)
  product_id (foreign key to you products)
  single_item_cost  (copy from products so that if the product price changes the quote won't change)
  quantity (multiply by single_item_cost to get total cost for this quote_item)


Obtain column names of referenced objects

As much as I've tried, I cannot get sys.sql_expression_dependencies to display any column details.

However, there is a Table-Valued-Function, sys.dm_sql_referenced_entities(), that can be employed to help us in our quest for referenced column details.

The following displays a single table denoting all referenced entities, with column details where appropriate, for the given SQL Server instance:

DECLARE @AllCmds nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY
FOR
SELECT
'SELECT ReferencingDatabaseName = ''' + d.name + '''
    , ReferencingSchemaName = s.name
    , ReferencingObjectName = o.name
    , ReferencedClass = sed.referenced_class_desc
    , ReferencedServerName = sed.referenced_server_name
    , ReferencedDatabaseName = ISNULL(sed.referenced_database_name, ''' + d.name + ''')
    , ReferencedSchemaName = sed.referenced_schema_name
    , ReferencedEntityName = sed.referenced_entity_name
    , ReferencedColumnNames = (STUFF((
        SELECT '', '' + COALESCE(dsre.referenced_schema_name, '''') +
                ''.'' + COALESCE(dsre.referenced_entity_name, '''') +
                ''.'' + COALESCE(dsre.referenced_minor_name, '''')
        FROM sys.dm_sql_referenced_entities(s.name + ''.'' + o.name, N''OBJECT'') dsre
        WHERE dsre.referenced_minor_id > 0
        FOR XML PATH ('''')
    ), 1, 2, ''''))
FROM ' + QUOTENAME(d.name) + '.sys.sql_expression_dependencies sed
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.objects o ON sed.referencing_id = o.object_id
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.schemas s ON o.schema_id = s.schema_id
'
FROM sys.databases d
ORDER BY d.name;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @AllCmds = CASE WHEN @AllCmds IS NOT NULL THEN @AllCmds + 'UNION ALL
' ELSE '' END + @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
PRINT @AllCmds;
EXEC sys.sp_executesql @AllCmds;

The output contains a single row per referencing object.  If that referencing object includes column references, those column names will be listed in comma-delimited form under the ReferencedColumnNames column.


Does SQL Server Support Parallel Bulk Inserts From Multiple Connections?

Does Microsoft have any sort of documentation on this?

Yes, lots. One particularly good reference is the Data Loading Performance Guide.

I am trying to understand what is happening under the hood on the SQL Server side. Is there a queue for all of the bulk inserts, and each bulk insert is executed one by one, in the order the came in? Or are all the inserts performed at the same time, in parallel?

There's nothing particularly special about bulk inserts. There isn't a special queue, or anything like that. Each is processed as a normal command, which will either proceed or be blocked based on concurrent locking activity. To be clear: yes concurrent bulk imports to the same clustered table from different server connections are possible.

We are also assuming that no two distinct connections will be attempting to insert the same records (based on Primary Key), BUT what if two of the parallel connections WERE trying to insert records that would cause a PK violation?

If two connections attempt to insert the same records into a unique index, one will be blocked behind the other. When the first one releases its exclusive lock, the second will throw a uniqueness violation error (unless esoteric options like IGNORE_DUP_KEY are present on the target index).

Are the answers to the questions above different if we are asking for SQL Server 2008 vs 2017?

Not materially different. SQL Server 2016 and later can achieve minimally-logged bulk inserts to an index without trace flag 610.

It can be tricky to achieve truly concurrent bulk inserts to a table with one (or more) b-tree indexes in practice. You may need to disable lock escalation for the target and/or take other actions. It is a more complex undertaking when several indexes are present. See the Data Loading Performance Guide for more details.


Problem with unmasking a database user during a sproc

To be honest, I've never used SQL Server masking before so this is mostly a guess, but if you try to run just the line of code EXEC('GRANT UNMASK TO apiuser'); directly, does it work?
I'm thinking by executing dynamic SQL you're running into the same limitation as if you created a temp table with dynamic SQL and then tried to access that table outside of the dynamic SQL after (it's not possible).
I can't find exactly why that's how SQL Server works with dynamic SQL, but here's some relevant posts (regarding temp tables) and I bet you're experiencing the same thing with unmask:
SQL Authority: SQL Server Dynamic SQL and Temporary Tables
StackOverflow: T-SQL Dynamic SQL and Temp Tables
As a solution, can you run just the GRANT UNMASK directly (eliminate the dynamic SQL) after the IF instead like your first example?
if exists (select name from [sys].[database_principals] where name=N'apiuser')
GRANT UNMASK TO apiuser;


Having four different tables or having one table with one more field?

Having all languages in one table is an overhead. Each time you will have to load the data of all languages and use part of (for one language), or you will add a condition to your query to load one language's data, which is also an overhead. In my opinion, having each language is a separate table is lighter. However, if the load on your website is low, and your translation data is not big, the difference will be negligible.


The mystery backups just show a long alpha-numeric string in curly brackets. Anybody know what this is?

This might be a 3rd party software using Volume Shadow Service (VSS) backup .

  I would like to see if I can disable this job, but I have no idea where it is scheduled.

if you are using native TSQL backup, then disable the SQL Server writer service using services.msc

you can use sysinternal's process explorer to find out what the process or just ask your windows admin if they are doing snapshot of entire folders or machine.

Related question with some more info : Can't find process that is creating backups


Is Locked Pages In Memory Still Recommend in SQL Server 2017?

In general, I still recommend to enable the "Lock Pages in Memory" policy on the SQL Server service account. This instructs SQL Server to ignore Windows' requests to trim the working set and page it to disk.

As Erik points out in the comments, this is really needed when you need to preserve SQL Servers' working set, which is necessary when the same Windows machine hosts other services or applications that may end up competing with SQL Server for RAM.

Another thing to keep in mind is Virtualization: nowadays almost all SQL Servers are installed on virtual machines, with several possible memory configurations and often sharing the same hypervisor host with many other VMs. Overcommitting memory is commonplace and the hypervisor tries to balance RAM allocations to VMs using memory balooning. Even if your SQL Server is not sharing the same Windows guest with other services, you might want to enable LPIM to let it play nicely with memory balooning and avoid unwanted memory trimming on production workloads.

Here is a whitepaper from Microsoft describing how to configure Hyper-V and SQL Server.

That said, if this is a test instance, you probably don't care much about the performance implications of memory trimming.


I think you want to do something like

UPDATE sciana1
   SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0);

COALESCE() will return you the first non-NULL parameter.  Then max(id) is NULL for an empty table, which means the COALESCE expression returns 0 when the table is empty, and the actual maximum when it is not.

You can further improve this by checking if the value will be different after the update, thus sparing unnecessarily bloating the table:

UPDATE sciana1
   SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0)
 WHERE id IS DISTINCT FROM gid + COALESCE((SELECT max(id) FROM sciana), 0);

IS DISTINCT FROM is an operator that behaves similarly to <>, but also considers NULLs.  With a plain <> you might not get anything updated, as the comparison itself results in NULL (thus not matching any rows) if sciana1.id is empty.

One more important thing is that if sciana is big, an index on id can help a lot in finding its maximum.

And a last note: in the second UPDATE query, the SELECT max()... part is executed twice.  This can be prevented by moving it out to a CTE or a subquery like

UPDATE sciana1
   SET id = gid + COALESCE(m, 0)
  FROM (SELECT max(id) FROM sciana) AS t(m)
 WHERE id IS DISTINCT FROM gid + COALESCE(m, 0);

One should check which one results in a more efficient execution plan.  In my experiments, the latter produces a nested join, which with a higher number of rows in both tables gets slower than the first version, while being faster with low row numbers.


Forcing postgres to use a GIN index on a varchar[]

You can use an EXCLUDE constraint to have the system automatically do the check for you.  Once in place, it will automatically throw an error if you try to insert a row that violates the constraint.  This is probably your best option.  If you first check manually and then insert the row, there could be a race condition which allows two conflicting rows to be inserted successfully.

If for some reason you really need to do the check manually, you could re-write the query to use a COUNT(*) rather than LIMIT 1, and then check to see if the count is zero or not.

The problem is that the planner things the seq scan will find one matching row very quickly, and can then stop.  Writing the query with a COUNT(*) will force the planner to realize the true cost of the seq scan.  Alternatively, you could just drop the LIMIT 1 from the query, and on the client side have it fetch only one row and then not fetch any more.


Determine when a PostgreSQL database was last changed

While using select datname, xact_commit from pg_stat_database; as suggested by @Jack Douglas doesn't quite work (apparently due to autovacuum), select datname, tup_inserted, tup_updated, tup_deleted from pg_stat_database does appear to work. Both DML and DDL changes will change the values of tup_* columns while a vacuum does not (vacuum analyze on the other hand...).

In the off chance that this may be useful for others, I'm including the backup script that I've put in place. This works for Pg 8.4.x but not for 8.2.x-- YMMV depending on the version of Pg used.

#!/usr/bin/env perl
=head1 Synopsis

pg_backup -- selectively backup a postgresql database cluster

=head1 Description

Perform backups (pg_dump*) of postgresql databases in a cluster on an
as needed basis.

For some database clusters, there may be databases that are:

 a. rarely updated/changed and therefore shouldn't require dumping as
    often as those databases that are frequently changed/updated.

 b. are large enough that dumping them without need is undesirable.

The global data is always dumped without regard to whether any
individual databses need backing up or not.

=head1 Usage

pg_backup [OPTION]...

General options:

  -F, --format=c|t|p    output file format for data dumps
                          (custom, tar, plain text) (default is custom)
  -a, --all             backup (pg_dump) all databases in the cluster
                          (default is to only pg_dump databases that have
                          changed since the last backup)
  --backup-dir          directory to place backup files in
                          (default is ./backups)
  -v, --verbose         verbose mode
  --help                show this help, then exit

Connection options:

  -h, --host=HOSTNAME   database server host or socket directory
  -p, --port=PORT       database server port number
  -U, --username=NAME   connect as specified database user
  -d, --database=NAME   connect to database name for global data

=head1 Notes

This utility has been developed against PostgreSQL version 8.4.x. Older
versions of PostgreSQL may not work.

`vacuum` does not appear to trigger a backup unless there is actually
something to vacuum whereas `vacuum analyze` appears to always trigger a
backup.

=head1 Copyright and License

Copyright (C) 2011 by Gregory Siems

This library is free software; you can redistribute it and/or modify it
under the same terms as PostgreSQL itself, either PostgreSQL version
8.4 or, at your option, any later version of PostgreSQL you may have
available.

=cut

use strict;
use warnings;
use Getopt::Long;
use Data::Dumper;
use POSIX qw(strftime);

my %opts = get_options();

my $connect_options = '';
$connect_options .= "--$_=$opts{$_} " for (qw(username host port));

my $shared_dump_args = ($opts{verbose})
    ? $connect_options . ' --verbose '
    : $connect_options;

my $backup_prefix = (exists $opts{host} && $opts{host} ne 'localhost')
    ? $opts{backup_dir} . '/' . $opts{host} . '-'
    : $opts{backup_dir} . '/';

do_main();

########################################################################
sub do_main {
    backup_globals();

    my $last_stats_file = $backup_prefix . 'last_stats';

    # get the previous pg_stat_database data
    my %last_stats;
    if ( -f $last_stats_file) {
        %last_stats = parse_stats (split "\n", slurp_file ($last_stats_file));
    }

    # get the current pg_stat_database data
    my $cmd = 'psql ' . $connect_options;
    $cmd .= " $opts{database} " if (exists $opts{database});
    $cmd .= "-Atc \"
        select date_trunc('minute', now()), datid, datname,
            xact_commit, tup_inserted, tup_updated, tup_deleted
        from pg_stat_database
        where datname not in ('template0','template1','postgres'); \"";
    $cmd =~ s/\ns+/ /g;
    my @stats = `$cmd`;
    my %curr_stats = parse_stats (@stats);

    # do a backup if needed
    foreach my $datname (sort keys %curr_stats) {
        my $needs_backup = 0;
        if ($opts{all}) {
            $needs_backup = 1;
        }
        elsif ( ! exists $last_stats{$datname} ) {
            $needs_backup = 1;
            warn "no last stats for $datname\n" if ($opts{debug});
        }
        else {
            for (qw (tup_inserted tup_updated tup_deleted)) {
                if ($last_stats{$datname}{$_} != $curr_stats{$datname}{$_}) {
                    $needs_backup = 1;
                    warn "$_ stats do not match for $datname\n" if ($opts{debug});
                }
            }
        }
        if ($needs_backup) {
            backup_db ($datname);
        }
        else {
            chitchat ("Database \"$datname\" does not currently require backing up.");
        }
    }

    # update the pg_stat_database data
    open my $fh, '>', $last_stats_file || die "Could not open $last_stats_file for output. !$\n";
    print $fh @stats;
    close $fh;
}

sub parse_stats {
    my @in = @_;
    my %stats;
    chomp @in;
    foreach my $line (@in) {
        my @ary = split /\|/, $line;
        my $datname = $ary[2];
        next unless ($datname);
        foreach my $key (qw(tmsp datid datname xact_commit tup_inserted tup_updated tup_deleted)) {
            my $val = shift @ary;
            $stats{$datname}{$key} = $val;
        }
    }
    return %stats;
}

sub backup_globals {
    chitchat ("Backing up the global data.");

    my $backup_file = $backup_prefix . 'globals-only.backup.gz';
    my $cmd = 'pg_dumpall --globals-only ' . $shared_dump_args;
    $cmd .= " --database=$opts{database} " if (exists $opts{database});

    do_dump ($backup_file, "$cmd | gzip");
}

sub backup_db {
    my $database = shift;
    chitchat ("Backing up database \"$database\".");

    my $backup_file = $backup_prefix . $database . '-schema-only.backup.gz';
    do_dump ($backup_file, "pg_dump --schema-only --create --format=plain $shared_dump_args $database | gzip");

    $backup_file = $backup_prefix . $database . '.backup';
    do_dump ($backup_file, "pg_dump --format=". $opts{format} . " $shared_dump_args $database");
}

sub do_dump {
    my ($backup_file, $cmd) = @_;

    my $temp_file = $backup_file . '.new';
    warn "Command is: $cmd > $temp_file" if ($opts{debug});

    chitchat (`$cmd > $temp_file`);
    if ( -f $temp_file ) {
        chitchat (`mv $temp_file $backup_file`);
    }
}

sub chitchat {
    my @ary = @_;
    return unless (@ary);
    chomp @ary;
    my $first   = shift @ary;
    my $now     = strftime "%Y%m%d-%H:%M:%S", localtime;
    print +(join "\n                  ", "$now $first", @ary), "\n";
}

sub get_options {
    Getopt::Long::Configure('bundling');

    my %opts = ();
    GetOptions(
        "a"             => \$opts{all},
        "all"           => \$opts{all},
        "p=s"           => \$opts{port},
        "port=s"        => \$opts{port},
        "U=s"           => \$opts{username},
        "username=s"    => \$opts{username},
        "h=s"           => \$opts{host},
        "host=s"        => \$opts{host},
        "F=s"           => \$opts{format},
        "format=s"      => \$opts{format},
        "d=s"           => \$opts{database},
        "database=s"    => \$opts{database},
        "backup-dir=s"  => \$opts{backup_dir},
        "help"          => \$opts{help},
        "v"             => \$opts{verbose},
        "verbose"       => \$opts{verbose},
        "debug"         => \$opts{debug},
        );

    # Does the user need help?
    if ($opts{help}) {
        show_help();
    }

    $opts{host}         ||= $ENV{PGHOSTADDR} || $ENV{PGHOST}     || 'localhost';
    $opts{port}         ||= $ENV{PGPORT}     || '5432';
    $opts{host}         ||= $ENV{PGHOST}     || 'localhost';
    $opts{username}     ||= $ENV{PGUSER}     || $ENV{USER}       || 'postgres';
    $opts{database}     ||= $ENV{PGDATABASE} || $opts{username};
    $opts{backup_dir}   ||= './backups';

    my %formats = (
        c       => 'custom',
        custom  => 'custom',
        t       => 'tar',
        tar     => 'tar',
        p       => 'plain',
        plain   => 'plain',
    );
    $opts{format} = (defined $opts{format})
        ? $formats{$opts{format}} || 'custom'
        : 'custom';

    warn Dumper \%opts if ($opts{debug});
    return %opts;
}

sub show_help {
    print `perldoc -F $0`;
    exit;
}

sub slurp_file { local (*ARGV, $/); @ARGV = shift; <> }

__END__

Update: the script has been put on github here.


The way I'm interpreting your use of the term "shopping cart", is it's just the collection of items purchased at one time by a customer. At my company we refer to that as a "Sales Order". To handle Sales Orders there are two methods you can use: 1) it often makes sense to have a Header level Fact table and a Detail level Fact table and seperate information into one of those two tables, or 2) you have one Sales table that's denormalized where you keep all your sales information.

More info on option #1:

The header level table would unique by Sales Order (or Shopping Cart) as long as you have a unique ID for each of them. And can include information that's specific to the entire Order like who placed it (since the person placing the order can't vary by line item). And then the Line Item table would be unique by Sales Order and Line Item (aka Product) and would include the information like Quantity, Price, etc. The benefits of this set up are there's less data redundancy, but the downside is the need for more joins to connect the two tables.

More info on option #2:

The other option I mentioned is have one bigger table that includes both header level information and detail level information. So even though all line items have the same customer you still list it on each item. And you would still want a Sales Order ID or Shopping Cart ID to help distinguish all the items on an order. The benefit of this is less joining and easier to query, the downside is more data redundancies and the chance for errors.

So part of your question though is whether you should have a foreign or not. If you went with Option #2 then you definitely wouldn't have a foreign key. With Option #1 you can or can not have a foreign key it just depends. For a lot of database models it gets really tricky having a fact table point to a fact table. Because when you're loading those tables you need to be extra careful to always make sure your records exist in the first table before you try to load the table with the Foreign Key. Sometimes what we do instead of using a foreign key is we instead have a Stored Procedure that checks if all the values in Column A exist in Column B and if not reports any exceptions and we just check and review that so we have an informal Foreign Key that we just keep an eye on.

Just some ideas. Hope this helps.


Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text

Substrings first argument is the offset, the second argument is the number of characters from the offset. You need to subtract the offset from the second parameter:

SELECT title
     , SUBSTRING(`content`
                -- offset in string
                ,LOCATE('[flv:',`content`)
                -- number of characters from offset
                ,LOCATE(']',`content`) - LOCATE('[flv:',`content`) + 1 )
FROM amazonlinks

I added +1 to include "]" in the result


Problem sending an attachment using dbo.sp_send_dbmail

You can check the database mail log by going to management -> right-click database mail -> select view database mail log.  Any errors should be in here, for example:

  Message
  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-05-16T02:21:47). Exception Message: Cannot send mails to mail server. (A recipient must be specified.).
  )

Also, you could fire up SQL Server Profiler to check the user for sp_send_dbmail.  I think it uses the current user account to send the mail, so I would also confirm your windows account has permissions to the file.


Evict node from SQL Server 2016 Cluster on Windows Core

Use the command-line parameters for setup.exe on Windows Core.  The details can be found at Microsoft Docs.  Using the command-line parameters allows use of the setup.exe without requiring the GUI pieces, which are problematic on Windows Core.

From that page:

  Use the parameters in the following table to develop command-line scripts for RemoveNode. To uninstall a failover cluster, you must run RemoveNode on each failover cluster node. For more information, see Always On Failover Cluster Instances (SQL Server).

A simple example, taken from the page linked, is:

setup.exe /q /ACTION=RemoveNode /INSTANCENAME="<Insert Instance Name>" [/INDICATEPROGRESS] /CONFIRMIPDEPENDENCYCHANGE=0


Correlated Subquery Versus Non-Correlated Subquery

The non-correlated version you proposed will fail if the subquery returns more than one row (which it likely will).  You should use IN rather than = here.  The purpose of Celko's correlation is to make sure only zero or one rows is returned from the subquery (because it matches and groups on emp_id).

Also, don't forget to alias the table inside the subquery and explicitly reference the alias.  Since both tables (Personnel and Absenteeism) have an emp_id column, you want to be very clear where you are referring to which table.

This is more of a "best practice" for readability, but can also prevent logical issues and mistakes in queries.  For instance, this query would delete every row in the table:

DELETE FROM Personnel WHERE emp_id IN (SELECT emp_id);

Of course, this may seen like a contrived example, but it happens: sql server 2008 management studio not checking the syntax of my query

So here's the updated query:

DELETE FROM Personnel
WHERE emp_id IN
(SELECT a.emp_id FROM Absenteeism a
GROUP BY a.emp_id
HAVING SUM(a.severity_points) >= 40)

Given those changes: you're right that the correlation isn't strictly necessary in this case.

Logically, it seems like the correlated version might be more efficient, especially if there are lots and lots of rows in the Absenteeism table that don't have a match in the Personnel table (because the unbounded subquery might read more data).

But one would have to test to be sure.  The query optimizer can take the declarative query you've written and do many different things with it.


Rename cell based on duplicate compound key in MS Access

You will need two things

First, make sure mytable as a autoincrement column called ID

Next, place this SQL in the SQL View of a Query and run it:

SELECT Col_1,Col_2,IIF(DupKey=0,Col_3,Col3+TRIM$(STR$(ID))) as Column_3 FROM
(SELECT AA.*,
AA.ID - BB.ID as DupKey
FROM
(
   SELECT A.*,B.ID,
   IIF(DupCOunt=1,B.Col_3,B.Col_3+"Dup") AS Col3
   FROM
   (
      SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
      FROM mytable GROUP BY Col_1, Col_2, Col_3
   ) AS A, mytable AS B
   WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
) as AA,
(
   SELECT A.Col_1,A.Col_2,A.Col_3,A.DupCount,MIN(B.ID) as ID
   FROM
   (
      SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
      FROM mytable GROUP BY Col_1, Col_2, Col_3
   ) AS A, mytable AS B
   WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
   GROUP BY A.Col_1,A.Col_2,A.Col_3,A.DupCount
) as BB
WHERE AA.Col_1=BB.Col_1 AND AA.Col_2=BB.Col_2 AND AA.Col_3=BB.Col_3
) as AAA;

Give it a Try !!!

It will not make consecutive Dup numbers but it will assign unique Dup Numbers. This is too hard to get consecutive numbers without VB Code or another table


best way to deal with imperfect xls source file that needs to be imported into SQL via SSIS

Two options come to mind. If you go with the local copy approach you can manually use the AutoFill feature to fill in the blank values. For an automated approach, the one approach I would think of in T-SQL is to use a cursor to loop through all the rows, capture the non-NULL Dates and Types into variables and when you encounter a NULL value, UPDATE the current record with the last non-NULL value. An integer identity may be needed in the table you're importing to confirm the rows have been inserted in the order as found in the file.


Pass-through is a feature of Windows  authentication. It's not related to SQL Server at all, and it's "on" by default (I'm not sure if there's a way to turn it off, now that I think about it).

Which accounts you use depends on the environment, and how the permissions are configured on the backup share. I'm assuming in this scenario that the copy job will be a "pull" from production to QA (in other words, the backup share exists within the production domain).

If domain trusts are configured such that a principal on the QA domain can authenticate against a production resource, the backup share (folder) permissions can be directly modified to give read access to whichever QA domain account/group is being used (they don't have to match at all). Some environments allow this kind of configuration, while others don't. If you don't know the trust configuration between the two domains, ask your Windows system administrator.

If there are no trusts configured between the two domains, then you'll need to use pass-through authentication. This is set up by creating identical AD accounts (matching user name + password) on both domains. Assuming appropriate permissions are configured on the backup share (folder) for the production account, the Windows authentication system will automatically handle the rest of the process.

See these two TechNet articles for more information about domain trusts and pass-through authentication.


ERROR: date/time field value out of range: "2020-06-0700:00:00"

You are overcomplicating things. concat() and to_char() are not needed and the root cause of the problem.

The start of the day 7 days back can be obtained using: current_date - 7. As that doesn't have a time part, a comparison with a timestamp column will automatically assume 00:00:00.

If you want to compare everything up to yesterday midnight, simply use < current_date.

So your expression can be simplified to:

and created_date >= current_date - 7
and created_date < current_date

Note the < instead of <= in the second condition.

If you want to be explicit about the time, you could use

and created_date >= (current_date - 7) + time '00:00:00'
and created_date < current_date + time '00:00:00'

But that's not needed and is absolutely identical to the first condition.

created_date < current_date is roughly equivalent to created_date <= (current_date - 1) + time '23:59:59' (which is your initial intention).

However, it's only roughly equivalent because your attempt isn't actually correct as it misses the time between 23:59:59.000000 and 23:59:59.999999, that's why it's better to use < with the next day's start.


Substantial difference between database size under MySQL and actual size on disk

If you are using InnoDB tables, the size of your ibdata files will grow over time. So, if you issue DELETE statement, your database size will reduce, but the ibdata file will remain the same (not reduce).

If you are not using innodb_file_per_table option, the only way to reclaim the space is by dumping the database and restoring from the dumpfile.

However, if you are using innodb_file_per_table, you can issue an

ALTER TABLE foo ENGINE=InnoDB;

on tables that grow too large to reclaim the disk space.


Meltdown Patching can be turned on as needed

No, there is no publicly known way to do this for the SQL Server Meltdown/Spectre patches.

Microsoft might have a trace flag to do it, but if so, they haven't shared it publicly as of yet.


How to choose which backup files to restore when identical timestamps exist for diff and log backups

After restoring the full backup, do you restore the second differential backup and the last log backup?

This is going to depend, but assuming:

The source server is offline and the data files cannot be accessed
The most up-to-date copy of the data is what is asked to be restored
All backup files are successfully accessible

Then you'd want to restore the 1:00 AM full backup, then the 11:00 AM differential, and there will most likely be some additional information in the 11:00 AM log backup depending on the length of time the differential and log backups took.

  Or do you restore the first backup and all the log backups (even though the 10AM log backup really has the same issue as the 11AM backup - coinciding with the differential backup in each case).

I'm not sure what you mean about the 10:00 AM log backup having "the same issue" as the 11:00 AM log backup. There isn't an issue at all.

The whole point of a differential backup is to apply the changed extents and then keep just enough transactional information to make everything consistent. This greatly differs from a log backup which is the actual log records. They serve two different functions and you don't need differential backups in a restore to the latest point in time if you're in the full recovery model but you do need log backups to do so.

  … do we really have to resort to header restores to determine which files are able to be restored in sequence?

You don't have to "resort to header restores" if:

You have an up-to-date copy of MSDB and can query the metadata out of it
Some other 3rd party application does the metadata shuffle for you
A human sits in front of a screen using SSMS to load up all the files and let the restore wizard figure it out by utilizing the backup header metadata
You keep some other metadata repository that is synchronized with your DR area

If, however, none of those (not complete, just examples) are the case then... yes, you'll need some way of figuring out which files go in which order. Since the easiest way to figure it out is to ask the backup what metadata it holds, that's what you'll find most people doing.


Backup Error with Msg 3201, Level 16, State 1, Line 1 & Operating system error 3

SQL Server can only see network drives that are mapped in the Windows Profile of the SQL Server service account. So you'd have to log on as that account (not your own) and map the drive, for SQL Server to be able to use it for backups. Backups run using the credentials of the SQL Server service account, not yours. As suggested in the comment above, try using a full UNC patch instead (e.g. '\fileserver\fileshare\backups\backupfile.bak' which will not require mapped drives.

Note that the SQL Server service account will require read/write permissions to the destination. Being able to access external file system is one very good reason to use a domain account for your SQL Server service account, rather than LOCAL SYSTEM etc..


How to print out the query SQL text of a transaction by querying the "fn_dblog" or DBCC LOG('DataBaseName')?

The transaction log does not not contain statements, it contains the physical changes occurred in the database. If you see a log record that indicate a delete you cannot know if this was a DELETE statement, a MERGE statement or a wide (split) UPDATE statement. If you see an operation indicating an INSERT you cannot know if it was an INSERT (...) VALUES (...) or it was an INSERT (...) SELECT (...) or it was an INSERT (...) EXEC or it was a MERGE or it was a wide (split) UPDATE. And so on and so forth. Specifically, the transaction log does not intend to substitute for an audit trace.

The transcriptional replication agent has means to reconstruct T-SQL operation with identical effect as those that changed a published article, but how it does it is not public information.

If you want to monitor data changes, use Change Tracking or Change Data Capture. If you want to monitor T-SQL activity, use profiler traces.


You have two values of max_allowed_packet in MySQL :

one on the client side : [mysql] section, [mysqldump], [client] and more.
one on the server side : [mysqld] section.

The value of max_allowed_packet you see with the command show variables like 'max_allowed_packet'; is the one on the server side.

In order to increase this value, you must increase both sides : in your server configuration file ([mysqld] section in your my.ini file) and in your client configuration file (whether your [client] or [mysql] section in your my.ini file).

This setting can be changed on the server side without restarting the server if you have the SUPER privilege with this command : mysql> SET GLOBAL max_allowed_packet = numeric;.

Don't forget to change the 'numeric' value by a numeric value. Don't forget to change your configuration file too otherwise this value will be reset at reboot.


Postgres Proper Index for Sorting and Join

The reason for the poor performance is that the barcodes occurring earlier in the alphabet have been preferentially depleted from the location table.

It is easy to reproduce this with your random data seeds by just populating locations with twice as many records, and then deleting the "first" half of them:

INSERT INTO locations (id, barcode_id) SELECT seed.id, (RANDOM() * 500000)
    FROM generate_series(1,120000) AS seed(id);

DELETE from locations c where id in (
    select d.id from locations d
        JOIN barcodes ON d.barcode_id = barcodes.id
        ORDER BY barcodes.value ASC limit 60000
);

There is not much hope for getting PostgreSQL to understand this weird cross-table relationship.

You might be able to get somewhat better performance by doing something like

set local enable_nestloop TO off;

But the real solution is probably to rethink how you are organize your data, or what you are trying to do with it.  Usually barcodes serve as their own primary keys.  Why do you need a separate table just to map a barcode to a primary key?  And why do you need to know the lowest barcodes which exist somewhere (anywhere) on a regular basis?  It sounds like an X-Y problem.


Why the sudden increase of my database .mdf file size?

Messages will be retained in Service Broker queues until they are consumed with RECEIVE or the associated conversations ended with END CONVERSATION. Messages will also be retained in the queue after RECEIVE until the conversation is ended if the queue is set to to RETENTION = ON.
A large number of messages indicates there may be a problem with the application or activated stored procedure where messages are not being dequeued properly. Another possible cause is the queue is disabled.
The query below will return the message counts per queue along with the queue settings to determine next steps.
SELECT
      sch.name
    , sq.name
    , p.rows
    , sq.is_receive_enabled
    , sq.is_enqueue_enabled
    , sq.is_retention_enabled
    , sq.is_activation_enabled
    , sq.activation_procedure
FROM sys.service_queues AS sq
JOIN sys.schemas AS sch ON sch.schema_id = sq.schema_id
JOIN sys.internal_tables AS it ON it.parent_object_id = sq.object_id
JOIN sys.partitions AS p ON p.object_id = it.object_id
WHERE p.index_id = 1;

It seems there is only a single long-running conversation on the queue given the few end points so that rules out conversation leakage as the cause. Also, since there is no activation procedure associated with the problem queue, you should reach out to your app team to determine why the messages aren't being read.
That being said, you can dequeue the messages using a T-SQL script if you know the messages are not actually needed. The example script below does this in batches to avoid filling the log. In the case of full recovery model, make sure the log is large enough to accommodate the messages deleted between log backups.
SET NOCOUNT ON;
DECLARE
      @conversation_handle uniqueidentifier
    , @BatchSsize int = 100000
    , @MessagesDeleted int = -1;
WHILE @MessagesDeleted <> 0
BEGIN
    RECEIVE TOP (@BatchSsize) @conversation_handle = conversation_handle
        FROM [dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Receiver];
    SET @MessagesDeleted = @@ROWCOUNT;
    RAISERROR('%d messages deleted this batch', 0, 0, @MessagesDeleted) WITH NOWAIT;
END;
GO


Preventing database access after application license key has expired

If the person has a valid MS SQL Server username and password, they will be able to access the database.

You could arrange your app so that the username/password they use for the app won't allow them to accesss the SQL Server database. In any case, what's the problem if your users wish to use Crystal (or whatever) to access what is, after all, their own data?

What I mean is that surely your app provides some extra service (for which they are paying you) - after that, what they do with their data is their business.

You could warn them that any modifications to data may invalidate/corrupt their data with respect to your application (i.e. you won't support them for that), but personally, I would be rather annoyed to be told by a vendor that I couldn't run ad-hoc reports against my own data!

[EDIT]

What you could do is keep any user whose privileges allow them to modify the data "secret", but give them a username/password with SELECT privileges and that way everybody's happy - they have access to their data and you can rest easy in the knowledge that users can't mess up your app.


I'm not aware of a way to automatically compress new tables. The script below will compress existing tables and indexes that are not already compressed. You could schedule this periodically to compress new tables if you don't have control over the table and index create DDL.

DECLARE @SQL nvarchar(MAX) = (
    SELECT
          N'ALTER '
          + CASE
                WHEN type_desc ='HEAP'
                THEN 'TABLE '
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
                ELSE 'INDEX '
                + QUOTENAME(name)
                + N' ON '
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
        END
        + N' REBUILD WITH(DATA_COMPRESSION=ROW);
'
    FROM sys.indexes
    WHERE
        OBJECTPROPERTYEX(object_id, 'IsMSShipped') = 0
        AND EXISTS(
            SELECT 1
            FROM sys.partitions
            WHERE
                partitions.object_id = indexes.object_id
                AND partitions.index_id = indexes.index_id
                AND data_compression_desc = N'NONE'
            )
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
EXEC sp_executesql @SQL;

Note that the compression feature is only available in Enterprise and Developer Editions in SQL Server 2014 and earlier versions. It is also available in Standard Edition beginning with SQL Server 2016 SP1.