How far to go when Validating a SQL Service Pack install?

The ideal situation is to have a server that, in terms of OS and other software, exactly matches the production environment, upon which you can perform the update first. That way you know everything that is required.

This would preferably be a VM that you could snapshot, so if you encounter and fix a problem you can revert to the snapshot and start again with your updated procedure. Repeat this until the upgrade procedure works and you know the reboot requirements and such, then plan to repeat the process in production.

One of your development/testing VMs might be ideal for this if you have them (i.e. if your dev/test/release process isn't just "smash code together and throw directly into production"!). This way you are essentially treating the service pack the same as you would one of your own bug fix or feature releases meaning you can perform a full regression test on your application after the service pack is applied to the test environment (to make sure MS haven't introduced any bugs or changes to undefined behaviour that your application depends upon - or that they haven't fixed a bug that your code is depending on!).

Obviously this "ideal" could be more time consuming than other options...


SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

If you can't change the query you can use a plan guide.

Test the performance of the query with OPTION (QUERYTRACEON 4138) (will need someone with sysadmin permissions to try this).

If that produces satisfactory performance you can apply this with a plan guide. If it doesn't produce satisfactory performance try and find a hint that does. Possibly OPTION (HASH JOIN, MERGE JOIN) if inappropriate nested loops is the problem. You might need to resort to the USE PLAN N'...' hint.

Once you know the hint(s) required you can apply them using the information here.


Double FOREIGN KEY, i.e. how to create a table with two different FOREIGN KEYs that reference a single PRIMARY KEY in another table - DDL

There are two possibilities here:

1) is that it is a case of 2 fields in the same table being FOREIGN KEYs pointing to the same field in the parent table.
2) is that this is an example of an Associative Entity. These are also called joining, linking or many-to-many tables.

1. Double FOREIGN KEY.

This is quite simple to resolve.

You have two tables - department and dept_transit (see fiddle):

CREATE TABLE department
(
  department_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT department_pk PRIMARY KEY (department_id)
);

CREATE TABLE dept_transit
(
  dt_original_dept_id INTEGER NOT NULL,
  dt_new_dept_id      INTEGER NOT NULL,
  CONSTRAINT dt_original_dept_id_fk FOREIGN KEY (dt_original_dept_id) REFERENCES department (department_id),
  CONSTRAINT dt_new_dept_id_fk      FOREIGN KEY (dt_new_dept_id)      REFERENCES department (department_id)
  -- optional here CONSTRAINT dept_transit_pk PRIMARY KEY (dt_original_dept_id, dt_new_dept_id)
);

A few things to notice:

my solution uses long_variable_names! Many years of experience have taught me that you are far better off making your variable names (table_names, field_names and PKs, FKs &c.) as long and as meaningful as necessary. This makes life much easier for debugging and more than outweighs the trouble of the extra typing! Also, note the singular name for department! YMMV :-)
I always try to keep fieldnames unique within a schema (e.g. new_dept_id, dt_new_dept_id) - this helps greatly with logic when joining tables. I make exceptions for NATURAL KEYs, but not surrogate ones as is the case here.
My own personal preference is for SQL keywords to be in upper-case and for the variables (tables, fields &c.) to be in lower_snake-case. This allows the eye to readily pick out different elements when debugging &c. If you look up SQL coding standards on the web, you will find lots of debate about this (i.e. here). The main thing is to pick a standard (for yourself, company, whatever) and STICK TO IT.
Not entirely sure of requirements, but I think that (for MySQL) a TRIGGER will be required for any requirement that original_dept_id and new_dept_id be different (which makes sense - I think).
Unfortunately, MySQL still (Amazingly) doesn't allow for CHECK CONSTRAINTs. See the MUCH nicer solution possible with PostgreSQL here. A good reason to switch to the far superior PostgreSQL if you're not too invested in MySQL! :-)

2. Joining Table.

If this is the case, I would do something like the following (fiddle here):

CREATE TABLE original_dept
(
  original_dept_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT original_dept_pk PRIMARY KEY (original_dept_id)
);

CREATE TABLE new_dept
(
  new_dept_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT new_dept_pk PRIMARY KEY (new_dept_id)
);

CREATE TABLE dept_transit
(
  dt_original_dept_id INTEGER NOT NULL,
  dt_new_dept_id      INTEGER NOT NULL,
  CONSTRAINT dt_original_dept_id_fk FOREIGN KEY (dt_original_dept_id) REFERENCES original_dept (original_dept_id),
  CONSTRAINT dt_new_dept_id_fk      FOREIGN KEY (dt_new_dept_id)      REFERENCES new_dept (new_dept_id),
  CONSTRAINT dept_transit_pk PRIMARY KEY (dt_original_dept_id, dt_new_dept_id)
);

The PRIMARY KEY of the joining table is a natural one, make up of the PKs of both of the entities that comprise it.


How to fix my Merge Replication upload order?

There are cases in Merge Replication when parent and child rows can be processed out of order on synchronization resulting in constraint violations and conflicts.

To understand how and why this can occur and how it may apply to your specific scenario, I recommend reading through:

How to understand Merge Replication article processing order
Merge Article Processing Order and using the "Not for Replication" Attribute

To immediately alleviate the constraint violations and conflicts from occurring on synchronization you can mark the foreign keys as NOT FOR REPLICATION.  This means that the foreign key constraint will not be enforced when the Merge Agent performs an operation.  To mark the foreign key constraint as NOT FOR REPLICATION please see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

Another approach is to increase -UploadGenerationsPerBatch and the -DownloadGenerationsPerBatch Merge Agent parameters to avoid splitting parent and child changes across generation batches.


Replication error: the principal "dbo" does not exist

It turns out that someone had deleted an account on the publishing database that had been the database owner without fixing the database ownership before doing so.  This orphaned user, since it didn't exist, caused replication to break.  I assume it's because it was the database owner.


First of all, your Rec_Amount columns should be changed from varchar to something numerical like float.
Secondly, i think you wrote the wrong range in this sentence so i don't understand what you mean here.

if Date is giving from 12-06-2020 to 12-06-2020 ,then opening will be 09-03-2020 balance 10500

I believe this query gives the desired output:
SELECT q.Customer_ID, q.E_Date, q.Payable, q.Received, q.Balance
FROM
(SELECT Customer_ID, E_Date, Payable, Received, SUM(CAST(ISNULL(Payable, 0) AS float)-CAST(ISNULL(Received, 0) AS float)) OVER(PARTITION BY Customer_ID ORDER BY E_Date) AS Balance
FROM
(SELECT s.Customer_ID, s.E_Date, s.Rec_Amount, s.Inv_type
FROM #tbl_Shop_InvM s
UNION ALL
SELECT r.Customer_ID, r.Rec_Date, r.Rec_Amount, r.Inv_type
FROM #tbl_Receivable r) AS a
PIVOT
(MAX(Rec_Amount)
FOR Inv_type IN ([Payable],[Received])) AS p) AS q

/*Set date range */
--WHERE q.E_Date BETWEEN '2020-03-07' AND '2020-03-11'

Output:


What does SQL Server use to do a count(*) on a table?

With a trivial SELECT COUNT(*) FROM dbo.YourTable; query on a disk-based table, SQL server counts rows by scanning either the heap or leaf nodes of an index. The cost-based SQL Server optimizer chooses the narrowest one available in order to minimize the number of pages scanned.

This example shows the behavior with an index scan.

CREATE TABLE dbo.YourTable(
      Column1 int NOT NULL
    );
--load 10K rows
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.YourTable WITH (TABLOCKX) (Column1)
SELECT num
FROM t1g
WHERE num <= 10000;
GO
CREATE INDEX ncidx_YourTable_IntColumn ON dbo.YourTable(Column1);
GO
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO
--show heap and index size
SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;

Index/heap sizes:

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+

If we fragment the index, it becomes larger than the heap so the heap is scanned instead of the index:

--fragment index and update stats
UPDATE dbo.YourTable SET Column1 = 2;
UPDATE STATISTICS dbo.YourTable;
DBCC FREEPROCCACHE;
--heap scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

Heap and index size with fragmentation:

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         648 |
+---------------------------+-------------+

After the index is rebuilt, the index is scanned again because it is narrower:

--rebuild index
ALTER INDEX ncidx_YourTable_IntColumn ON dbo.YourTable REBUILD;
DBCC FREEPROCCACHE;
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

Heap and index size after rebuild:

SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;
GO

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+


Grouping data to move up and down in a Table depending on Month day

Lets assume that the query is:

SELECT
    -- long list
FROM
    -- complex joins
WHERE
    -- multiple conditions
ORDER BY
    a, b ;

and that the cutting numbers (0, 42, 85) can be found by some functions fun(@X) where @X is a number from 1 to 7. Then we can use a CASE expression in the ORDER BY:

SELECT
    -- long list
FROM
    -- complex joins
WHERE
    -- multiple conditions
ORDER BY
    -- the "cutoff"
    CASE WHEN ROW_NUMBER() OVER (ORDER BY a,b) > fun(@X)
        THEN 0 ELSE 1
    END,
    a, b ;

If the cutoff is as simple as diving the rows into 7 equal (or almost) parts, then we can use ((@X-1) * COUNT(*) OVER ()) / 7 as fun(X), where @X is between 1 and 7.

If the total count of the returned rows is 300, the expression will give 0, 42, 85, 128, 171, 214 and 257.


Prevent failure in conditional insert in mysql database

Don't bother with SERIALIZABLE.

START TRANSACTION;
SELECT caseid, status, ... FROM ... WHERE ... FOR UPDATE;
check `status`, do some processing
optionally do
    INSERT ...
COMMIT;

With that logic, you can handle lots of concurrent actions.


IMHO you should use UNION (yes, 19 times)

create table tbl (server_name varchar(100), app1 varchar(100), app2 varchar(100), app3 varchar(100));
insert into tbl values
('server1', 'app xx', 'app xy', 'app yx'),
('server2', 'app xx', 'app xy', null),
('server3', 'app xx', 'app xy', null);
GO

3 rows affected

select server_name, app1 as application from tbl where app1 is not null
union
select server_name, app2 as application from tbl where app2 is not null
union
select server_name, app3 as application from tbl where app3 is not null
GO

server_name | application
:---------- | :----------
server1     | app xx
server1     | app xy
server1     | app yx
server2     | app xx
server2     | app xy
server3     | app xx
server3     | app xy

dbfiddle here


Query slows with additional FROM item (even with no extra actions)

As far as I understand if you want to know which is % of 'deleted' rows, do not cross join with the same table, get total rows and calculate the result on your initial query.

with totalc2 as
(
    select count(*) nrows
    from comment
)
SELECT CAST(COUNT(c1.body) AS float) /totalc2.nrows) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%'

You can use it as a subquery:

SELECT CAST(COUNT(c1.body) AS float) / (select count(*) from comment) as percent_deleted
FROM comment c1
WHERE c1.body LIKE '%[deleted]%'


In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?

work_mem is not an "area", it is a just value.
HashSetOp is pretty poor at memory estimation and usage.  It has no provision for spilling to disk. If it ends up using more memory than work_mem is set to, then it just goes ahead and uses more memory than it was supposed to.  There is no external enforcement mechanism (until the kernel steps in), and in the case of HashSetOp no internal one either.
If the planner thinks ahead of time that HashSetOp would overrun work_mem, then it just doesn't get chosen, and you will probably get some sort-based method instead.  But again, the estimation is poor, it will often use several times more memory than the planner thought it would, and will happily blow way past the setting of work_mem.
HashSetOp could really use some improvements, but it is so seldom used that no one is very excited about doing the work.


Accent marks/symbols not displaying correctly in MySQL database

Fix the data source to store actual characters into MySQL, not "HTML entities".

Where necessary, use output routines such as PHP's html_entities() and/or urlencode()


What will happen if master goes down in peer to peer replication?

In a Peer-To-Peer replication topology all the nodes are master. So, there is no concept of just ONE master.

The image in your question seems to be made up and wrong. See original doc.

Read up more on - Peer-to-Peer - Transactional Replication


Table with identity column in merge replication

When using Merge with identity columns — will need to specify an identity range management option of either automatic or manual so that duplicates do not occur.

With automatic, replication will automatically manage the assignment of identity ranges for nodes participating in the Merge topology.

With manual, you will need to manually assign "islands" of ranges for nodes using DBCC CHECKIDENT.

Have a look at Replicate Identity Columns.


db INDEX on ORDER BY FIELD in very simple query does not help - still slow response?

Well, first of all a SELECT * will pretty much void using any indexes. DB2 will see that you require the entire table and thus do a table scan (which in itself can be expensive).

After that you are doing a an ORDER BY, which causes DB2 to do a SORT on the table it just scanned (another expensive operation). So you have two expensive operations one after the other.

Another thing.....if ID is the primary key...it already built an index for you under the covers. (which again it will ignore with a SELECT *). So you now have a second index that is probably just taking up disk space. (Just as an fyi any field that has a unique constraint-including the primary key-DB2 automatically builds a unique index for you.)

So first off, what this really speaks to is a bad query (no other way to put it). Do you have any predicates to add to the SQL? If you can cut down what you are selecting with a WHERE clause, or if you don't need every column in the SELECT, then you could build an index or indexes over those columns and that would help.

I would advise you to do an EXPLAIN over this query so you can see the cost and the query plan that DB2 used. Then as you test out indexes, etc, you can see if DB2 chooses to take advantage of them or not, and which is the most efficient method.


How should I limit a table to N rows, discarding oldest?

You can create a view to hide the rest of the rows when they do exist:

CREATE VIEW dbo.Top1000Rows
AS
  SELECT TOP (1000) ...columns...
    FROM dbo.table_name
    ORDER BY DateCreatedUTC DESC;
GO

Then periodically run this in a background job (just play with the frequency until you're happy with how often the table itself is "off", and by how much):

;WITH x AS
(
  SELECT pk_column,
    rn = ROW_NUMBER() OVER (ORDER BY DateCreatedUTC DESC)
  FROM dbo.table_name
)
DELETE x WHERE rn > 1000;

If it doesn't have to be exactly 1,000 rows all of the time, then you can potentially forget about the view altogether, knowing that sometimes there will be more than 1,000 rows.


Postgres Repeatable Read vs Serializable

REPEATABLE READ is sufficient for your case. It will by definition prevent a “lost update”.
SERIALIZABLE is quite a bit more expensive than REPEATABLE READ, which is the “cheapest” of all isolation levels. More locks will be taken (SI locks that don't block anything, but can cause a transaction to abort), and these locks have to survive a commit. It is impossible to name a figure how much more expensive it will be; that depends on your workload.


Can I remove "/*!50100 PARTITION BY HASH..." to success with import?

When code starts with something like this

/*!50100

the code following till */ is executed only, when MySQL is installed in a version above 5.0.100

The error here is, that you're mixing table options with partition options.

From the manual:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
...

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr) }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

So, try it like this:

CREATE TABLE IF NOT EXISTS `feeditems` (
   `feeditemsID` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `city` varchar(100) DEFAULT NULL,
   `companyID` int(10) unsigned NOT NULL DEFAULT '0',
   `fucID` int(11) NOT NULL DEFAULT '0',
   `feedID` int(10) unsigned NOT NULL DEFAULT '0',
   `title` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `url` text NOT NULL,
   `feeddate` datetime NOT NULL,
   `region` varchar(100) DEFAULT NULL,
   `jobtype` int(11) NOT NULL DEFAULT '0',
   `jobinfomisc` text NOT NULL,
   `joblastdate` varchar(255) NOT NULL,
   `jobcontactname` varchar(255) NOT NULL,
   `jobcontactphone` varchar(255) NOT NULL,
   `jobcontactmail` varchar(255) NOT NULL,
   `jobcontactinfo` text NOT NULL,
   `jobworkers` varchar(255) NOT NULL,
   `jobActive` int(1) NOT NULL DEFAULT '0',
   `logo` varchar(50) DEFAULT NULL,
   `infokus` int(1) NOT NULL DEFAULT '0',
   `deleted` int(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`feeditemsID`,`feeddate`),
   KEY `deleted` (`deleted`),
   KEY `companyID` (`companyID`),
   KEY `feedID` (`feedID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=331451
/*!50100 PARTITION BY HASH ( `feeditemsID` + YEAR(`feeddate`))
PARTITIONS 3 */;


DISCLAIMER : Not an Oracle DBA

Use srvctl, the grid control program that allows you to manipulate parts of the RAC Cluster

Usage: srvctl <command> <object> [<options>]
    commands: enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config
    objects: database|instance|service|nodeapps|vip|asm|diskgroup|listener|srvpool|server|scan|scan_listener|oc4j|home|filesystem|gns
For detailed help on each command and object and its options use:
  srvctl <command> -h or
  srvctl <command> <object> -h

Examples

srvctl stop database -d mydb
srvctl stop instance -i mydb1

Please get an expert to perform these, especially since you want to do something with ASM. If I recall, ASM is a proprietary Oracle file system. Using srvctl would be better than trying to handle it yourself.

Here is more help on srvctl

$ srvctl stop database -h

Stops the database.

Usage: srvctl stop database -d <db_unique_name> [-o <stop_options>] [-f]
    -d <db_unique_name>      Unique name for the database
    -o <stop_options>        Options to shutdown command (e.g. normal, transactional, immediate, or abort)
    -f                       Force stop
    -h                       Print usage
$ srvctl stop instance -h

Stops the database instance.

Usage: srvctl stop instance -d <db_unique_name> {-n <node_name> | -i <inst_name_list>}  [-o <stop_options>] [-f]
    -d <db_unique_name>      Unique name for the database
    -n <node_name>           Node name
    -i "<inst,...>"          Comma separated instance names
    -o <stop_options>        Options to shutdown command (e.g. normal, transactional, immediate, or abort)
    -f                       Force stop
    -h                       Print usage
$ srvctl stop asm -h

Stops ASM instance.

Usage: srvctl stop asm [-n <node_name>] [-o <stop_options>] [-f]
    -n <node_name>           Node name
    -o <stop_options>        Options to shutdown command (e.g. normal, transactional, immediate, or abort)
    -f                       Force stop
    -h                       Print usage
$


Assign dbo role to login without creating a new user in db

If at all possible, this should be fixed from the deployment side of things and not from a questionable modification of the target database side of things (referring to the conversation in the comments on the Question related to using the deprecated sp_addalias).

Depending on what the exact problem is, there are various configuration options of DacPac deployment that can be used to get around such conflicts. These options can either be specified on the command line if running SqlPackage, or can be placed into a DAC Publish Profile (i.e. an XML config file).

The options of interest here are listed in the Publish Parameters, Properties, and SQLCMD Variables section of the SqlPackage page.

To start with, do you have any of the following set to True:

DropObjectsNotInSource (default: False)
DropRoleMembersNotInSource (default: False)
IgnoreLoginSids (default: True)

If you have the DropObjectsNotInSource property set to True, then try adding:

DoNotDropObjectType=Users
ExcludeObjectType=Users
IgnoreRoleMembership=True
IgnoreUserSettingsObjects=True ??

If needing to specify the ExcludeObjectType property, that would look as follows, depending on where it is specified:

On the command-line: /p:ExcludeObjectType=Users
In a DAC Publish Profile:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0"
         xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <ExcludeObjectType>Users</ExcludeObjectType>
    </PropertyGroup>
</Project>


Yes you can create create a domain/type as a work around  but I caution against this

All future upgrades have to maintain this new datatype that added no new feature only overhead.

Converting Access/MSSql SQL queries and VB scripts to PLpg/SQL is a complete rewrite there is no way around that..

There are enough subtle/major difference between MS implementation of SQL and every other implementation to make it not work with any other database at all.

Only the most simplest queries will can be reused

Select * from sometable

but MS typically generates

Select * from [schema].[table]


How to lock entire Postgres table but allow reads

Don't do that. Explicitly locking tables is almost always the wrong thing to do. If you describe the use case in more detail, I am confident that a better solution can be found.

Locking tables frequently will keep autovacuum from doing its important work on the table, and eventually an anti-wraparound autovacuum job will come along and block you, bringing processing to a standstill.


PostgreSQL: Is it possible to get a different function result type (i.e. TABLE) in stored function based on input?

First off, you should take a look at Erwin Brandstetter's answer to a similar question: https://stackoverflow.com/a/23957098/835781

If the return type you want is mimicked by the structure of an existing table, you could use that existing table's regclass to define the type of the results.  That method seems to be a bit overkill for your use case, if I'm understanding it correctly.

My suggestion would be to just create two functions.  If you need to keep the same name, I would suggest you overload the function definition:

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR
)
RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)
AS
$$ ... $$ ;

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR,
    id_flag BOOLEAN
)
RETURNS TABLE (
    message VARCHAR
)
AS
$$ ... $$ ;

This results in you still having to create two function definitions.  If you're going to create two separate functions, you should probably just use different names for each.  If your application code is expecting the two different result sets depending on the flag provided, why not interpret that flag in your application code and use that to determine which function to call?

If you're dead-set on returning two different data structures depending on the value of a variable, you can't use the schema of an existing table to define the result type, and you're ok with the processing overhead, then you could create an array or json return type.  This gives your function the flexibility to build out the result in whatever format you choose, but requires additional processing and logic handling to interpret.  At the end of the day, it's usually just easier to create different functions for the different result types.


Does SQL Server 2017, including older versions, support 8k disk sector sizes?

Is the 8k sector size disk supported in Windows?

Currently, no, it is not supported by Windows and has been documented.

  Does SQL Server care about sector sizes?

Yes, SQL Server does care about sector sizes. In fact, SQL Server checks the underlying physical disk information as it has data structures and algorithms that work with current disk sector sizes for various reasons (data integrity, optimizations, etc.).

If you attempt to use a physical sector size above 4k (4096) you'll receive an error:

Msg 5179, Level 16, State 1, Line 1
Cannot use file 'S:\Folder\TestDB.mdf ', because it is on a volume with sector size 8192.
SQL Server supports a maximum sector size of 4096 bytes.
Move the file to a volume with a compatible sector size.

In this case, you can see that the error message is specifically telling you that the maximum sector size is 4096 (4k). This means that volume can't be used for SQL Server, and as noted above it wouldn't be supported in Windows either.

Solution

If you're receiving this error/issue, unfortunately you'll need to use a supported disk sector size such as 512 bytes or 4k. Other formats are unsupported and can't be used. You'll need to contact your hardware vendor for the Disk subsystem used and ask if there are any other options.

As eckes mentioned in a comment, on Linux you can emulate a 8k drive with the new logical blocksize option of losetup -b with 4.14 kernels.


While DR testing, we brought down nodes running on DC1

Bringing down the primary server normally will cause an automatic failover to a sync AG.  However, you also brought down the only other sync secondary configured for automatic failover.  This puts the AG in a situation where there is no primary, which requires forced manual failover.  From Perform a Forced Manual Failover of an Always On Availability Group (SQL Server):

If the primary replica becomes unavailable when the WSFC cluster has a healthy quorum, you can force failover (with possible data loss), to any replica whose role is in the SECONDARY or RESOLVING state. If possible, force failover to a synchronous-commit secondary replica that was synchronized when the primary replica was lost.

A forced manual failover will always warn of possible data loss.  You need to take the steps outlined in that article to make sure you are avoiding data loss.  See the section Potential Ways to Avoid Data Loss After Quorum is Forced.
You're in the "If a synchronized synchronous-commit secondary replica comes online" situation.  You'll want to check the is_failover_ready column (in the sys.dm_hadr_database_replica_cluster_states DMV) for each database that's part of this AG.  If they are all 1, then you can force a failover without data loss.

Another possibility is that, since node3 is in the DR data center (DC2), one or more of the databases in that availability group (AG) is not in the SYNCHRONIZED state.
Even though node3 is configured as a sync replica, it can fall behind the primary and then be considered not ready for failover.  You should be able to observe this in the AG dashboard, specifically the "Failover Readiness" column in the list of databases.  They should all say "No Data Loss" in before you attempt a failover.

You'll need to wait on node3 to "catch up" (synchronize) with the primary before initiating the failover.


SQL: shopping cart design for a marketplace

It really depends on your use cases. Having the cart and cart_item tables in the first example is just an extra level of normalization which may be needed in some contexts (depending on how different a cart_item is vs an ordet_item etc). But generally speaking, and based on the schema you've designed, it's totally acceptable to eliminate the idea of a cart and cart_item entity entirely, if the order and order_item can support all your use cases for that workflow without much complication in your backend code.
You could even take it one step further and not touch the database at all until the order has been placed. Rather than storing an "in-process order" in the database, you can take the route of just storing an object representation, such as JSON on the client side, until the order is submitted (which is when in the workflow your backend code can convert that JSON to your database entities and persist it in the order and order_item tables respectively). This is one implementation some websites take, and will temporarily persist it on the client side such as in session storage (which is why you'll find on some websites your items only temporarily stay in the cart).
But regardless, it really just depends on the use cases of your workflow whether you choose option A or B in your post, or option C that I just mentioned. They're all valid choices.


How to get only the strings before a character in SQL Server

This looks like you are accepting a filtering clause that is going to be used as part of an ad-hoc query, and you want to try verify that the query won't produce a "column not found" error. This is almost certainly something you don't really want to do so I would question the design overall...

This sort of string parsing is not going to be very efficient or pretty in TSQL so would be probably better done in another layer, and as your business logic is no doubt putting the string together anyway it would be better to check as that happens and discover any error earlier in the process. If your business logic isn't making the string (i.e. you are accepting it from 3rd party code, or worse, a user) then you will have to contend with a lot of variations in the string (tabs instead of spaces, no spaces around the "=", extra whitespace including line breaks, names escaped with [ ] or " ", and so on).

But assuming you have no choice at all because this is someone else's design and you have no power to change it, you could try something like (pseudo code here, not feeling unlazy enough right now write detailed TSQL string manipulation!):

Set a "some not found" flag to 0
Copy @Test into @LeftToProcess
While there is at least one "=" character in @LeftToProcess (check with charindex) do the following:

Copy everything before the first "=" into @ColumnName
Take everything after the first "=" and put it back into @LeftToProcess
TRIM() @ColumnName
Replace AND with `` in @ColumnName
Replace OR with `` in @ColumnName (these two steps take out the logical operator, not that they make a massive set of assumptions like "there will always be exactly one space character between AND/OR and the name, if the input is coming from a user or third party you'll have to be a lot more clever than this)
Check using sys.objects joined to sys.columns that your table or view contains a column of the name now in @ColumnName
If not, set @SomeNotFound to 1
(end of WHILE loop, as we stripped off the part of the string we've just checked when we go back to step 3 the next "=" will be found or if there isn't any more we go to step 12)

If @SomeNotFound is still 0 at the end of the loop, everything mentioned exists in the target object

I hasten to make clear, in case my tone above does not, that I strongly advise against this sort of thing - it is hard to get absolutely correct for all possible inputs and the result will be difficult to maintain if it need to be expanded to deal with other sorts of clause.

There is unlikely to be any significantly cleaner or easier way to do exactly what you asking for in TSQL, your overall design needs a rethink to avoid it I feel.


We are facing the same problem - DACPAC does not support replication (as of now).

What we do is - if the deployment involves/affects replicated tables then

manually drop replication.

Script out drop and create replication using GUI or Powershell
Drop the replication

Run your DACPAC to upgrade your system
Create replication (scripted out previously) and kick off the snapshot of articles.

UPDATE :

USING GUI :

USING PowerShell :

Quickly Script Out Replication Redux - by  CHAD MILLER
Using PowerShell to Script out Replication on SQL Server 2005/SQL Server 2008 by Paul Ibison


Contradictory reports from MySQL that a binlog file exists

As it turns out, the problem was that we were connecting to an old (wrong) master.  The previous master was domain.com, but many months ago, we migrated to db.domain.com.  It seems that we were erroneously connecting to domain.com.

We’re using autossh to set up SSH tunnels for our replication.  To confirm that we were connecting to the old (wrong) master, I executed telnet 127.0.0.1:3305 on the slave.  From this, I could see that the reported MySQL version matched the version from the old master.  I did a full reboot of the slave which seems to have fixed the problem.  I think the root of the problem was likely due to some DNS quirks on our network - it would seem that when the autossh connection was established, db.domain.com resolved to domain.com, hence the symptoms of connecting to the wrong master.  (Certainly, these DNS issues on our network are something to look into)

Note: Major thanks to @Andrew for his answer on the original question: https://dba.stackexchange.com/a/140259/55530


Database design: How do i connect these two tables of my database in a normalisation way

I would have a combined Product table with all common elements (name, protein, etc) and this would tie to the food schedules.  Then there would be two additional tables for ProductUser and ProductMarket that would refer back to the main Product table.  No nulls needed and it's nice and normalized.


How to see today compiled object in Oracle?

I think the timestamp column of dba_objects is what you are after:

create table t(id integer);
create function f return integer as
  n integer;
begin
  select count(*) into n from t;
  return n;
end;
/

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
VALID   2011-09-05:11:01:52

insert into t(id) values (1);
select f() from dual;             -- ***no recompile needed***

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
VALID   2011-09-05:11:01:52

alter table t add dummy integer;  -- ***invalidates f()***

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
INVALID 2011-09-05:11:01:52

select f() from dual;             -- ***forces recompile***
select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
VALID   2011-09-05:11:01:53       -- ***note the different timestamp***

so to get all functions compiled today for example:

select *
from dba_objects
where to_date(substr(timestamp,1,10), 'yyyy-mm-dd')>=trunc(sysdate)
      and object_type='FUNCTION';

--edit

it may be safer to use last_ddl_time instead (see @Alex's comment below), but the caveat is that last_ddl_time is also changed on grants and revokes.


What ways are there to limit the processor / ram requirements of a specific query?

You can set the session to allow uncommitted reads like so:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;

which will reduce the locking abnd concurrency overhead of your queries (but may make the results inconsistent as you will sometimes see "dirty reads" and similar). For more detail see http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

This will not limit the CPU/RAM/IO footprint of the query itself significantly though.

A common solution to this sort of thing (which may be overkill depending on your circumstances) is to maintain a slave replica on another server (or the same server but on different physical disks if I/O contention is your only/main concern) and run hefty reporting queries from there. Similarly if your reports are not real-time you could restore your backups to another place instead of the hassle of maintaining replication, so if you have daily backups you restore those elsewhere after they are made so your reporting queries don't impact the live DB at all but your reports are only correct as of the last backup. Neither of these methods works if your heavy SQL needs to perform updates or inserts though.

Reusing your backups in this way can be a useful way to verify that your backup arrangement is working: if your automatic restore to the reporting copy fails then that may be an early warning that your backup regime needs work. It is surprising how many people religiously schedule backups but never test them until they need to restore one in an emergency (hence there are quite a few panicked "how do I restore from a corrupt backup?" questions out there in various forums!).


SQL Server 2008 R2 Partitioning - same FileGroup, 1 File, 2 partition_numbers - HELP

The results indicate at some point an explicit NULL partition boundary was added to the function when the partition scheme NEXT USED filegroup was set to DailyAlbertFG30.  Also, I don't see DailyAlbertFG2 used.  Perhaps there was once a partition on that filegroup that was subsequently merged.

Below is a script that shows how a FG30 partition with the NULL boundary can be created.  The NULL boundary might have been added accidentally.

CREATE PARTITION FUNCTION DailyAlbertPF1 (datetime2(3)) AS RANGE RIGHT FOR VALUES();
GO
CREATE PARTITION SCHEME DailyAlbertPS1 AS PARTITION DailyAlbertPF1 ALL TO ([DailyAlbertFG1]);
GO
CREATE TABLE dbo.FactAgentAlbertPortalSessionEntries
    (
      DateTimeColumn datetime2(3)
    )
ON  DailyAlbertPS1(DateTimeColumn);
GO

DECLARE @FileGroupNumber int = 1;
DECLARE @DateTimeBoundary datetime2(3) = '2015-04-15T00:00:00.000';
DECLARE @SQL nvarchar(MAX);
WHILE @DateTimeBoundary <= '2015-05-14T00:00:00.000'
BEGIN
    SET @SQL = N'ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG' + CAST(@FileGroupNumber AS nvarchar(5)) + N';';
    EXEC(@SQL);
    ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
    SET @DateTimeBoundary = DATEADD(day, 1, @DateTimeBoundary);
    SET @FileGroupNumber += 1;
END;
--add NULL boundary on DailyAlbertFG30
SET @DateTimeBoundary = NULL;
ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG30;
ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
GO


Teradata ODBC - version 14 and 15 side by side

With regards to your question the readme specifies the following:

  TTU_BASE.15.10.12.00.readme.txt

  This release is not compatible with any Pre-15.10 release and it has
  to be removed  before installing this release.

  This release can co-exist with 16.00 and later releases.

  The TTU installer will block the installation if a pre-15.10 release
  is installed. Options:

  Continue (remove the pre-15.10 release first and then install the 15.10 release)

  Cancel the install

  Please see the Installation Guide for details.
  (https://teradatanet0.teradata.com/Site00BA)

But why keep 2 versions on the same server? TTU is 4 versions backwards compatible and 2 versions upwards compatible, so you are reasonable safe.

The biggest question is: is the software that is using the TTU certified for use of the TTU 15.10 version? I.e. Informatica Powercenter 9.6.1 is certified with TTU 14.10, but is NOT certified with TTU 15.10. In fact: I've found a knowledge base article from the vendor that specifically recommended with an error situation to first downgrade as they were not providing support on an not support combination of software.

In our case Powercenter 9.6.1 talks to TTU 14.10 and will be talking to Teradata 15.10. This combination is certified/supported.


Web application using SQL Server 2008 starts hanging

I would check at logs,
1) IIS Logs
2) Windows Logs
3) SQL Server Logs

I think logs usually gives away a hint in case of any issue.


You can transfer the logins that are associated with orphaned users from server A to server B using the method explained in this KB article.

How to transfer logins and passwords between instances of SQL Server

Or you can use Copy-DbaLogin PowerShell command to do it.

Copy-DbaLogin


On a single table, how is an indexed view different from a non clustered index?

See this article: Resolving Indexes on Views for detail on how the query optimizer treats indexed views.

The normal use case for indexed views is aggregations (eg COUNT_BIG, SUM with GROUP BY etc) or complex formulas / calculations you want to keep in one place but do not want to use a computed column.  Basically the indexed view would look different to the base table, or to put it another way, you can do things with indexed views you can't do with a base table.

If you created an indexed view that is simply the base table and a unique clustered index (which is compulsory for an indexed view), then conceptually it is a duplicate of the base table and you may have missed the point of indexed views as described above.  You have also incurred a considerable index maintenance overhead for no reason.

Indexed View matching, the feature by which the Query Optimizer will use an indexed view even if it is not referenced in the query, (provided that view can 'answer the question') is harder than normal index selection so you also incur a risk of this not happening.  Although in your case where the view is a duplicate of the table it is irrelevant.  Indexed view matching is also an Enterprise edition feature, although you can override this with the NOEXPAND hint in Standard edition.

None of this is a concern for a conventional non-clustered index which may or may not be used depending on a large number of factors, although selection is probably more straightforward than for indexed views.

In summary, an indexed view implemented as you describe is simply an unnecessary duplicate of the base table.  I would be very careful about implementing them, unless there was a specific use case/example where it was shown to be significantly faster using an indexed view, and/or you can live with the overhead having trialled it extensively with your other workloads.

Indexed views do have their uses and there are some interesting discussions on them recently here, here and here.

HTH


Transferring (backup/restore) from development to an online server

pgAdmin backup dialog screen has option "Do not save owner".

Reference:

https://www.pgadmin.org/docs/pgadmin3/1.22/backup.html
https://www.pgadmin.org/docs/pgadmin4/3.x/backup_dialog.html


improve database design for monitoring application

Add an index, not a unique key:

ALTER TABLE md_metrics_status ADD INDEX (`timestamp`);

I believe you are using PHPMyAdmin to add the index, and in PMA, unique is the default choice when you want to add an index, and that is why you are getting the duplicate key error.


Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

If all data access is via stored procedures, then permissions on the table are not checked of the same user (dbo probably) owns both procedure and tables. This is called ownership chaining

This also means the no permissions are needed on the tables at all. You don't need to deny or grant anything on the tables because they won't be checked. Lack of GRANT means no rights anyway, so you don't need DENY

Now, this means nothing to folk with elevated privileges: sysadmin, db_owner etc. These will always have rights. You can only use triggers to block these: but they can disable or drop triggers of course.

I assume that your "end users" are not running as db_owner or sysadmin...


Set Warnings (Alerts & Thresholds) in MSSQL Replication Monitor using TSQL

Detailed information about programmatically monitoring replications can be found on the official Microsoft Docs site:

Reference: Programmatically Monitor Replication

There is detailed information on all the stored procedures required to query monitoring details:

To monitor Publishers, publications, and subscriptions from the Distributor

sp_replmonitorhelppublisher
sp_replmonitorhelppublication
sp_replmonitorhelpsubscription

To monitor transactional commands waiting to be applied at the Subscriber

sp_replmonitorsubscriptionpendingcmd

To monitor merge changes waiting to be uploaded or downloaded

At the Publisher: sp_showpendingchanges

  returns a result set showing information on changes that are waiting to be replicated to Subscribers

At the Subscriber: sp_showpendingchanges

  returns a result set showing information on changes that are waiting to be replicated to the Publisher

To monitor Merge Agent sessions

sp_replmonitorhelpmergesession (either at the subscriber or distributor)
sp_replmonitorhelpmergesessiondetail (either at the subscriber or distributor)

Answering your question

The stored procedures required to read or change the monitoring thresholds are the sp_replmontirohelppublicationthresholds and sp_replmonitorchangepublicationthreshold procedures.

To view the monitor threshold metrics for a publication

sp_replmonitorhelppublicationthresholds

sp_replmonitorhelppublicationthresholds
          [ @publisher = ] 'publisher'
        , [ @publisher_db = ] 'publisher_db'
        , [ @publication = ] 'publication'
    [ , [ @publication_type = ] publication_type ]
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'

The parameters are:

@publisher        : the name of the publisher
@publisher_db     : the name of the published database
@pbulication      : the name of the publication
@publication_type : 0 = transactional publication
................    1 = snapshot publication
................    2 = merge publication

To modify the monitor threshold metrics for a publication

sp_replmonitorchangepublicationthreshold

sp_replmonitorchangepublicationthreshold
          [ @publisher = ] 'publisher'
        , [ @publisher_db = ] 'publisher_db'
        , [ @publication = ] 'publication'
    [ , [ @publication_type = ] publication_type ]
    [ , [ @metric_id = ] metric_id ]
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'
    [ , [ @value = ] value ]
    [ , [ @shouldalert = ] shouldalert ]
    [ , [ @mode = ] mode ]

The parameters are:

@publisher        : the name of the publisher
@publisher_db     : the name of the published database
@pbulication      : the name of the publication
@publication_type : 0 = transactional publication
................    1 = snapshot publication
................    2 = merge publication
@metric_id        : 1 = expiration
................    2 = latency
................    4 = mergeexpiration
................    5 = mergeslowrunduration
................    6 = mergefastrunduration
................    7 = mergefastrunspeed
................    8 = mergeslowrunspeed
@thresholdmetricname: the name of the publication threshold
................      (either this parameter or @metric_id is required)
@value            : the new metric value
@shouldalert      : generate alert (1=yes;0=no)
@mode             : monitoring mode (1=on;2=off)


TIMESTAMP is 4, not 7.  So, you are starting with 18 bytes.

Since it is probably ENGINE=InnoDB, multiply that by 2 or 3.  Sorry, I can't be more precise than that.  So, perhaps 60 bytes/record.

If it is MyISAM, add 1 for overhead.  So, 19 bytes/record.  Add 1 more for up to 8 NULLable columns (apparently 1, the timestamp).  This takes a byte of 8 bits.  So, 20 bytes/record.  If you can make all columns NOT NULL, this byte can go away.

What is the average length of xxx?  If it is less than 6-1, use VARCHAR(6) instead of CHAR(6).  A short VARCHAR takes 1 byte for length plus the actual number of bytes needed for the string.

Consider normalizing xxx to save more space.

Are all the string columns ascii or latin1?  If not, then the above numbers may be wrong.  utf8 may take 3 bytes per character.  utf8mb4, 4 bytes.

If there are not many different values for the CHARs, consider a 1-byte ENUM.

What about indexes?  They take space.

InnoDB without PRIMARY KEY -- a 6-byte hidden PK.  So, it is almost always advisable to have an explicit PK.  If some column (or combo of columns) is always distinct, use it.

Any secondary index, plus MyISAM's PK -- another BTree, with another calculation.  Treat each index (but not  InnoDB's PK) like another table, but add 40% overhead due to BTree.


"Could not find login" failures for remote LocalSystem that exists

SQL Server uses SIDs (Security IDs) behind the scenes https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql?view=sql-server-ver15

The name column is a label, and doesn't have to match what is in AD as long as the SIDs match. For example, I have seen a user who was able to connect to SQL Server after their AD username changed (married surname), but the old username still showed in SQL Server.

I suspect that this might be the other way around, and that the name is the same, but the sid was regenerated when the OS was upgraded. One way to test that would be to create login ... from windows for the computer account on a different, test SQL Server instance, and compare the sid column values for old and new logins.

HTH


I believe I've encountered this sort of problem before, primarily in dealing with Excel.  If it's anything like what I'm referring to, it's probable that during your connection to the Access database you are receiving some form of UI prompt or otherwise "waiting for input" type of event.  The root cause of this could be a number of things, but most likely is that the Access database is being held open by a separate process and the linked server connection attempt is being "warned" about it.

When you run KILL against the spids on the SQL Server 2008 R2 server, I expect you get something along the lines of:

  Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

If that's the case, you will be able to clear these "ghost" sessions by also terminating the pid on the other side of the connection.  Go to the server where the Access database exists and run a tasklist command from the console ( or use the task manager or whatever makes you happy ).  If you have a number of these "ghost" spids, you'll likely notice a number of Access processes as well ( MSACCESS.EXE, [pid], blah blah ).  You can remove these processes by using taskkill /IM "MSACCESS.EXE" /F from the console, or if you're feeling cautious, taskkill /PID [pid] /F.  The termination of these destination processes should allow the "ghost" spids on the server to resolve.

Assuming this is the problem, you'll probably find that your SELECT commands will function as they have in the past as well.


Reasons for disabling statistics auto update?

My advice:

Leave 'auto update stats' on (until you run into a very good reason not to) - you don't want a big delete in middle of a day to throw off query plans until the next time you run maintenance.
However, schedule index maintenance/update statistics at a quiet time. sp_updatestats will update all stats for all tables in a database for you, but that will also cause stored proc recompiles, so choose your time carefully (i.e. sp_updatestats right before your end-of-month reporting is probably suboptimal).

How often you need to run index maintenance/rebuild stats depends on your database load, specifically how often your data is modified (i.e. INSERT/UPDATE/DELETE). If you're modifying data all over the show (i.e. a staging table for a weekly batch process), you probably want to update stats/reorganize indexes nightly. If your data is rather more static you can probably make it a weekly or fortnightly schedule.


A database backup contains the allocated extents plus the parts of the log required to replay any activity that occurred during the backup, and restore the database to the point-in-time at which the backup completed.

So your first backup file could contain more log records than your second backup.

David


Is it possible to make database owner read only?

The privilege to DROP an object does not exist as such and thus can't be revoked. Owning an object implies the right to drop it, and having the right to drop an object implies owning it. Here's a relevant excerpt from GRANT's documentation, which is pretty explicit about this point:

  The right to drop an object, or to alter its definition in any way, is
  not treated as a grantable privilege; it is inherent in the owner, and
  cannot be granted or revoked. (However, a similar effect can be
  obtained by granting or revoking membership in the role that owns the
  object; see below.) The owner implicitly has all grant options for the
  object, too.


Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login

All logins exist in the master database. Why it is default is probably related to this, but you can change the default database to your user database if you wanted to. The default being master simply means they have to explicitly set the user database in the connection string for the application.

The default database has nothing to do on why the logon can connect to the instance. This comes from being granted connection privileges to the instance. This is done by default when using SSMS to create the login.

Additional Info

Public role is granted connect permissions and any login created is automatically part of that role.

The only permissions granted for every endpoint is Connect. The only thing they get at the server level is view any database.

You actually cannot remove a login from the public role either, SQL Server does not let you because it is hard coded in there somewhere. You will most security standards for things like HIPPA or DoD (STIGs) want you to simply harden what the public role can do at the server level and within each database. See "INF: SQL Security - Restricting access to public..." for a bit more information on it all.

Creating a login straight from GUI or from just CREATE LOGIN and you will automatically see these permissions at the server level:


PostgreSQL pattern to split a shared-database into a database-per-service pattern

Pattern: Database per service

That's a horrible idea. PostgreSQL has schemas..

I suggest ignoring that author entirely. That whole article is trash.

  Development time coupling - a developer working on, for example, the OrderService will need to coordinate schema changes with the developers of other services that access the same tables. This coupling and additional coordination will slow down development.

How does two developers developing in different schemas vs database slow down or speed up anything? The only advantage is the planner can make use of the indexes and you can do cross-schema joins and operations.

  Runtime coupling - because all services access the same database they can potentially interfere with one another. For example, if long running CustomerService transaction holds a lock on the ORDER table then the OrderService will be blocked.

That's only possible if "CustomerService transaction" uses "OrderService". If it does not use "OrderService" you can not have that problem. Moving to a configuration, such as splitting the data into discrete databases, that makes such a thing impossible doesn't solve this problem. It just gives you more work to get there, and to get out of that mess after you're there.

  Single database might not satisfy the data storage and access requirements of all services.

What does that even mean? In any interpretation I can conjure it's even wrong. A single database in PostgreSQL can store different tables on different disks, but I'm not sure what the author even means.


How to retain backups if automatic backup is enabled

Generally it is desirable to have old backups pruned at some point otherwise your backup file system is liable to run out of space at some point causing further backups to fail (this assumes that more recent backups are more valuable than aged backups, which is generally the case).

In DB2, there are several database-level configuration parameters which dictate when automatic backups are retained or removed which include:

logarchmeth1 - the primary method for archiving logs
auto_del_rec_obj - automatically remove log files, backup images, etc. when the associated recovery history entry is pruned
rec_his_retentn - the number of days that history entries are retained for
num_db_backups - the number of full database backups to retain for a given database

A typical setup might be:

logarchmeth1 set to DISK:somepath to ensure that transaction logs are saved somewhere for recovery
auto_del_rec_obj set to ON to ensure that old backup objects (log files and full backups) are removed when their associated history entries are purged
rec_his_retentn set to 30 to prune history entries after 30 days
num_db_backups set to 4 so that up to 4 full database backups are available at any given time

You might then set up a weekly cronjob to perform a full online backup of the database (presumably during some reasonably low-usage hours; a more nuanced strategy might involve incremental backups). As you'd be performing weekly backups you'd take about 4 full backups per month which pairs nicely with the 30 days of history pruning.

If something went horribly wrong and you needed to restore, you'd do so presumably from the latest weekly backup then roll forward through the retained logs to bring your database back to the point you wanted. And in case something was wrong with that backup you've got three more full backups before it to try as well.

Your backup file-system should largely look after itself with reasonable settings; just keep an eye on the amount of used storage (as your database grows over time so will your full backups, and if you experience a growth in transaction level, the average amount of log storage may go up too) and expand it as necessary to keep it within sensible limits.

There are various caveats to be aware of. Read up on the effects of the PRUNE HISTORY and ARCHIVE LOG commands particularly, in addition to the obvious BACKUP DATABASE and RESTORE DATABASE.


MySQL - cannot examine view definition - Error getting DDL for object - No database selected

No database selected error message means that no database is selected as current. Specify default database in my.ini, connection definition, or execute USE dbname; as the very first query in the connection.

why no other view required a default schema be defined, and why is a default schema required at all? – Roy Hinkley

Default scheme needed when tablename without database name is specified somewhere in SQL text.
For example, you execute SHOW CREATE TABLE my_view - but without default datatbase set the server does not know where it must take this view from (and even does this is a table or a view).


Is staggering reindexing jobs a good strategy?

As long as the indexes that need to be rebuilt get rebuilt it should be fine to stagger them.

This will of course vary based on your usage - if you have a ton of updates/inserts/deletes run on Fridays then there may be a good reason that you currently reindex on Saturdays.

Something else you can do, instead of blindly rebuilding every index every week, is using some queries to check fragmentation levels and only reindexing when a specific index is over a certain threshold of fragmentation.  The usual threshold (which was chosen somewhat randomly by Paul Randal many years ago) is 30%.  There are dozens if not hundreds of sample scripts available for this.


MySQL - Export two columns from large table without causing a lock

Many experts share many ways on how to overcome this problem. These are my suggestions to play a safe game.

Try to set the below command in a seperate session.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
use db;
SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME;
COMMIT;
exit;

Doing by this way the SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent.When you say not-consistent it means recently changing records i.e.. DML transactions that are currently in process will not be read. I assume which is in your case it is acceptable. This is also called a “dirty read.” Otherwise, this isolation level works like READ COMMITTED.

If I were to be you, the below order is what I follow.

Check for non-peak hours for DB server also for the table that you mind to do select. By this the outfile reads/write, IOs shouldn't cost much to the server.
Manually run this in the server back ground. And keept monitoring the server health.

$ cat bg_loaddata.sh

mysql -uroot -p*** << EOF
use db;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME;
COMMIT;
exit;
EOF

$ nohup ./bg_loaddata.sh > bg_loaddata.log &

If you see any inserts or updates that are taking longer time to complete in show processlist; You may wait for sometime, but if it is causing severe turbulence then goahead and kill the connection id in processlist for your select ...into outfile ...  [I'm 99% sure this will not happen, but always to be prepared for any back fires]

By this way you can be sure for N number of records to be retrieved it taks N number of seconds, and no issues can be faced by doing this way. And whenever you need this to be run next time put the script in cron and have a sound sleep. :) [Ofcourse you have monitoring metrics to wake you up]-> Just to be aware before someone surprises you with the NEWS.

All the best !!


The basic form is:

INSERT dbo.table
(
  cols
)
SELECT
  cols
FROM @tvp AS t
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.basetable AS b WHERE b.key = t.key
);

In your case, that would be:

INSERT INTO [Habib].[dbo].[SE]
(
  [empID],
  [POD],
  [empNotes],
  [hiredate],
  [terminationdate],
  [terminationtime],
  [address],
  [city],
  [state] /* why do people prefer horizontal scrollbars to vertical ones? */
)
SELECT
  [empID],
  [POD],
  [empNotes],
  convert(char(10), [b].[hiredate], 101),
  convert(char(10), [b].[terminationdate], 101),
  LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7)),
  [address],
  [city],
  [state]
FROM @TempTable AS b
WHERE NOT EXISTS
(
  SELECT 1
    FROM [Habib].[dbo].[SE] AS se
    WHERE se.empID = b.empID
);

One thing you need to find out, though, is if users enter existing empID values here and expect to update existing employees with new information for the other columns... if so, you're throwing their updates away without telling them.


Convert clustered primary key to non-clustered and use another column for the clustered index

I don't think you can perform this operation online, since you can't just move the clustered index from one column to another, and you can't use DROP_EXISTING to reduce the amount of work that has to be done.

However, you can avoid an outage, if you're willing to perform the work.

Create a new table with the new structure
Rename the original table, and create a view with the original name - the view will be  a union of old and new table - performance won't be fantastic but it won't be offline
Create INSTEAD OF triggers on the view to apply DML to old or new table

inserts should just go to new table
updates should try both
deletes should try both

Copy rows, in chunks, from the old table to the new table. Will need SET IDENTITY_INSERT ON and you should perform each "chunk" in its own transaction.
Once the old table contains no rows that don't exist in the new table, in a single transaction:

drop the old table
drop the view
rename the new table


Determine the SQL Server version and edition from the available set up, before actually installing it?

To answer your first question :

If you are not sure, then just manually  launch the setup.exe and verify.

When you launch SQL Server 2008 installation using setup.exe, you will see:

Below is for SQL Server 2008R2 :

An alternative to running the setup executable, is to right click and look for the version there. It'll be something like 10, 10.50, 11, etc. so you do need to convert

To answer your second question :

From SQL Server 2008 and up, Microsoft has changed as the edition depends on the PID that can be found in DefaultSetup.ini. The DefaultSetup.ini for SQL Server 2008 and 2008R2 has same format. So essentially if you don't know, you have to make sure that you have the correct installation files.

;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PID="xxxxx-xxxxx-xxxxx-xxxxx-xxxxx"

For SQL Server 2005, it was stated in the Servers folder default.htm. See

How to tell what edition your SQL Server setup files are

I don't think you can guess from the PID a particular edition of SQL Server. A full media will be good for all editions of a particular SQL Server version.


Perhaps you could start with:

SELECT *
    FROM pg_stat_all_indexes
    WHERE schemaname NOT IN ( 'pg_catalog', 'pg_toast' )
        AND idx_scan = 0
        AND idx_tup_read = 0
        AND idx_tup_fetch = 0
        AND indexrelid NOT IN (
            SELECT indexrelid
                FROM pg_index
                WHERE indisunique = true
                    OR indisprimary = true ) ;

No guarantee that the indexes that pop up aren't ever used, but it should provide a list to start looking at.


Best open source DB technology to store and query large, sparse matrices

You should take a look at SciDB - a database designed for precisely this use case. It has as as its major driver Michael Stonebraker, a man who knows a lot about databases - it would be an exaggeration to say that he's been involved with more database systems than most people have had hot dinners, but not by much :-)!

  "including Ingres, Illustra, Cohera, StreamBase Systems, Vertica,
  VoltDB, and Paradigm4. He was previously the Chief Technical Officer
  (CTO) of Informix. He is also an editor for the book Readings in
  Database Systems".

Google "michael stonebraker scidb sparse matrix" and subsets thereof and I think that it might be a good fit for what you want.


how to run db.adminCommand() using pymongo for mongodb 3.6

On mongo shell:
db.adminCommand({getParameter: 1, logLevel: 1})

{ "logLevel" : 0, "ok" : 1 }

On python:
from pymongo import MongoClient

server = 'localhost'

port = 27017

client = MongoClient(server, port)

print(client.admin.command(({'getParameter': 1, 'logLevel': 1})))

{'logLevel': 0, 'ok': 1.0}


Merge data with UNION ALL and INNER JOIN in MySQL

This can probably be simplified, but in it's simplest form all you need to do is to use an aggregate function for price_old and price_young:

select first_coin, second_coin, max(price_old), max(price_young)
from (
    (select t.`id`,
            t.`coins_id`,
            t.`first_coin`,
            t.`second_coin`,
            t.`price` as `price_old`,
            null as `price_young`,
            t.`time`
    from hist_all t
    inner join (
      select `first_coin`,
            MIN(`time`) as `MINDATE`
      from hist_all group by `first_coin`
    ) t1
    on t.`first_coin` = t1.`first_coin`
    and (t.`time` = t1.`MINDATE`)
    WHERE (second_coin = 'USD' OR second_coin = 'USDT')
    and `time` between (NOW() - interval 9999 minute)
    AND NOW()
    group by t.`first_coin`
    order by t.`ID`)
    UNION ALL
    (select t.`id`,
            t.`coins_id`,
            t.`first_coin`,
            t.`second_coin`,
            null as `col_null_1`,
            t.`price` as `new`,
            t.`time`
    from hist_all t
    inner join (
      select `first_coin`,
             MAX(`time`) as `MAXDATE`
       from hist_all
       group by `first_coin`
    ) t1
    on t.`first_coin` = t1.`first_coin`
    and (t.`time` = t1.`MAXDATE`)
    WHERE (second_coin = 'USD' OR second_coin = 'USDT')
    and `time` between (NOW() - interval 9999 minute)
    AND NOW()
    group by t.`first_coin`
    order by t.`ID`)
) as t
group by first_coin, second_coin


Optimizing a large number of INSERT ... SELECT statements

SUGGESTIONS

Increase the size of your Log Buffer. The default value for innodb_log_buffer_size is 8M. You should make 256M or 512M. (Restart Required)
Turn off the Double Write Buffer. Set innodb_doublewrite to 0. (Restart Required)
OPTIONAL : Temporarily disable change buffering, which throttles secondary index updates.
OPTIONAL : In your application, you should limit the number of rows to insert. Try insert 500 or 1000 rows at a time. If the rows have long VARCHAR columns, then insert 100 or 200 rows at a time.

SUMMARY
Add these lines to my.cnf
[mysqld]
innodb_log_buffer_size = 256M
innodb_doublewrite = 0

then, restart mysql.
If these do not improve things, then try disabling the change buffering with
SET GLOBAL innodb_change_buffering = 'none';

You could always change it back to 'all' after a number of large bulk inserts.
GIVE IT A TRY !!!


Cannot add a column to an InnoDB table in Mariadb

What I did

MariaDB [test]> select version();
+---------------------+
| version()           |
+---------------------+
| 10.0.15-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

Are you using 5.5?

CREATE TABLE terms_of_use
(

  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  current_version  varchar(20),
  last_updated     timestamp,
  keyx              varchar(256),  <== note keyx - fails if I use 'key'
  language         varchar(20),
  text
);

It appears that the problem is partly to do with the use of 'key' as
a field name.

INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');
INSERT INTO terms_of_use (current_version, last_updated, keyx, language, text) VALUES( 'afsdf', NOW(), 'asdfadsf', 'adfs', 'terewe');

MariaDB [test]> select * from terms_of_use;
+----+-----------------+---------------------+----------+----------+--------+
| id | current_version | last_updated        | keyx     | language | text   |
+----+-----------------+---------------------+----------+----------+--------+
|  1 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  2 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  3 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  4 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  5 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |
|  6 | afsdf           | 2015-01-28 20:45:06 | asdfadsf | adfs     | terewe |
+----+-----------------+---------------------+----------+----------+--------+
6 rows in set (0.00 sec)

Then I added the column

alter table terms_of_use add column `weight` int(11) not null default 0;

Then SELECT again

MariaDB [test]> select * from terms_of_use;
+----+-----------------+---------------------+----------+----------+--------+--------+
| id | current_version | last_updated        | keyx     | language | text   | weight |
+----+-----------------+---------------------+----------+----------+--------+--------+
|  1 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  2 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  3 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  4 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  5 | afsdf           | 2015-01-28 20:45:05 | asdfadsf | adfs     | terewe |      0 |
|  6 | afsdf           | 2015-01-28 20:45:06 | asdfadsf | adfs     | terewe |      0 |
+----+-----------------+---------------------+----------+----------+--------+--------+
6 rows in set (0.00 sec)

MariaDB [test]>


An INSTEAD OF trigger is not the best solution here. An INSTEAD OF trigger performs no actions against the database unless explicitly done so in the trigger code. Your example will never delete rows regardless of the STATUS value because it contains no DELETE statement. The ROLLBACK is superfluous since no modifications were made.

Consider an AFTER DELETE trigger for this requirement. As with any trigger, multiple rows may be affected so it is important to handle such a case. Avoid using the select @caseid as that will identify only a single cid value even if different ones were deleted.

Here's an example of an after trigger for your need.

CREATE TRIGGER LOAD_DROPID_DEL on WMWHSE2.table2
FOR DELETE
AS
IF EXISTS(SELECT * FROM deleted where deleted.STATUS <>'5')
BEGIN
        RAISERROR ('Deletions not allowed after loaded ', 16, 1);
        ROLLBACK TRANSACTION;
END;
GO


Permissions for Cluster service account in SQL server

Yes, it failing is expected. It is documented here.

AlwaysOn Availability Groups and SQL Failover Cluster Instance and Privileges
When installing the Database Engine as a AlwaysOn Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for AlwaysOn Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).

To answer now:

1.I've heard that creating NT AUTHORITY\SYSTEM within SQL server is not safe, If that is the case, Should I change my cluster service to a domain account?, Will this affect anything?

I'm assuming you're talking about the actual cluster service. If that's the case, then no do not change it. You could change it in WS2003 when domain accounts were used but since 2008 this is not possible. If you do this most likely your cluster will break.

2.I added NT AUTHORITY\SYSTEM to Sysadmin role - Is this really required or can I provide only the required permission explicitly to the login

Sysadmins is not required. Per the link/quote above, VIEW SERVER STATE is required.


Postgres JSON output using specific column as JSON key

You can use json_build_object function which allows you to calculate keys or values.

WITH l AS (SELECT id, my_json as data FROM table)
SELECT json_build_object(id, data) FROM l;

The result will be like;

|                        json_build_object |
|------------------------------------------|
| {"1" : {"a": 1, "b": 2, "c": [1, 2, 3]}} |
| {"2" : {"a": 1, "b": 2, "c": [1, 2, 3]}} |

Here is sample fiddle


Unless you are running the newest builds this should be fixed by applying the latest CU (look down in the comments).  Based on this bug/fix it appears to be a problem the CLR garbage collector.


How to find current setting of "FORCE PARALLEL QUERY" in Oracle

Tested on 12.1 but should work if you have appropriate privileges in versions where this view and feature is available. you will see rows only if value is non-default. If query returns 0 rows, it means value is default. Please test and adjust view names for your environment.

Select name, value From Sys.V_$ses_Optimizer_Env
Where Sid = (Select Distinct Sid From Sys.V_$mystat)
And Name In ('parallel_query_forced_dop','parallel_dml_forced_dop');


Fail to connect from MySQL Workbench to remote server

The issue had nothing to do with MySQL parts. It turned out that Linux's firewall and Selinux were blocking access from outside the server. Once turned off, the connection worked perfectly.

Two things I learned from this:

1) Check better the ENVIRONMENT before blaming the product,

2) MySQL could improve the clarity of the messages and include environment elements that could be the source of the issue (I wasted 1/2 day chasing this stupid issue).


Trigger/Event that fires when a Transaction finishes

Man, I'm terribly sorry.

I'm 'answering' stating that there's no simple solution as the one you devised.

You presented us a very interesting challenge.

I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself.

If you, please, could show us the grand view of the solution architecture, we would devise a least effort solution.

For instance, in the scenario:

one or two ADO.Net applications
one or two EntityFramework applications
one or two SSIS jobs

each potentially modifying the data on those tables, I would:

create a trigger for each table just to flag it has been tempered
create an intercption on each application calling a SP that would deal with the flagged data

and, for the interception points, I would:

on ADO.Net apps - supersede/override AdoDbConnection and rely on VS to quickly and safely refactor the code
on EF apps - override SaveChanges from DbContext
on SSIS jobs - include a call at the end of each one

Below I will present:

the schema of the table that will register events to be processed
a script that will generate the triggers for targeted tables
the snippet of what should be the processing stored procedures, called by the extension points listed above

Registry of events to be processed

To register records to be post-processed, I would design a single table such as:

IF EXISTS(SELECT * FROM sys.tables WHERE name = 'TBL_2PROC') DROP TABLE TBL_2PROC
go

CREATE TABLE TBL_2PROC(
    session_id     INT,          -- the session ID - it's expected each app transaction (or session) to have
                                 -- its own, exclusive, not shared, connection, even if pooled
    transaction_id BIGINT,       -- to assure grouping -> (session_id, transaction_id)
    event          VARCHAR(255), -- string stating an INSERT, UPDATE or DELETE
    tblName        VARCHAR(255), -- name of the table affected
    keyId          VARCHAR(255)  -- value of the (single) id column, converted to varchar
)
go

Making sure no deprecated trigger be left alive

Before creating the actual triggers, we must be sure no old one be left:

-- Clean-up existing ones ------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.triggers
WHERE name LIKE 'TR_2PROC%'
--
DECLARE @trName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)

OPEN cCursor
FETCH cCursor INTO @trName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'DROP TRIGGER [' + @trName + ']'

    EXECUTE sp_executesql @sql

    FETCH cCursor INTO @trName
END
go

Parameterizing targets

Before programmatically create the needed triggers, we have to declare which they will be:

-- Parameterize the target tables and theirs key columns -----------------------
--------------------------------------------------------------------------------

CREATE TABLE #to_log(tblName VARCHAR(255), keyName VARCHAR(255))
go

INSERT INTO #to_log VALUES('SomeTable',      'Id')
INSERT INTO #to_log VALUES('SomeOtherTable', 'OtherId')
go

Programmatically creating the needed triggers

Being the targets set, this script create the triggers:

-- Setup triggers --------------------------------------------------------------
--------------------------------------------------------------------------------

DECLARE @tblName VARCHAR(255)
DECLARE @keyName VARCHAR(255)
--
DECLARE @sql NVARCHAR(MAX)
--

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT tblName, keyName
FROM #to_log

OPEN cCursor
FETCH cCursor INTO @tblName, @keyName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @triggerName VARCHAR(255)

    SET @triggerName = '[TR_2PROC_' + @tblName + ']'

    -- Drop if exists ----------------------------------------------------------

    SET @sql = N'
        IF EXISTS(SELECT * FROM sys.triggers WHERE name = ''' + @triggerName + ''')
        DROP TRIGGER ' + @triggerName

    EXECUTE sp_executesql @sql

    -- Create ------------------------------------------------------------------

    SET @sql = N'
        CREATE TRIGGER ' + @triggerName + '
        ON [' + @tblName + ']
        AFTER INSERT, UPDATE, DELETE
        AS
        BEGIN
            DECLARE @session_id     INT
            DECLARE @transaction_id BIGINT

            SELECT
                @session_id     = session_id,
                @transaction_id = transaction_id
            FROM sys.dm_tran_session_transactions
            WHERE session_id = @@spid

            INSERT INTO TBL_2PROC
            SELECT
                @session_id, @transaction_id,
                CASE
                    WHEN del.[' + @keyName + '] IS NULL THEN ''INSERT''
                                                        ELSE ''UPDATE''
                END,
                ''' + @tblName + ''',
                CONVERT(VARCHAR(255), ins.[' + @keyName + '])
            FROM
                inserted ins
                    LEFT OUTER JOIN deleted del
                    ON del.[' + @keyName + '] = ins.[' + @keyName + ']

            INSERT INTO TBL_2PROC
            SELECT
                @session_id, @transaction_id,
                ''DELETE'',
                ''' + @tblName + ''',
                CONVERT(VARCHAR(255), del.[' + @keyName + '])
            FROM deleted del
            WHERE
                del.[' + @keyName + '] NOT IN(
                    SELECT ins.[' + @keyName + ']
                    FROM inserted ins
                )
        END'

    EXECUTE sp_executesql @sql

    FETCH cCursor INTO @tblName, @keyName
END
GO

Some clean-up

Followed by some clean-up:

-- Clean-up --------------------------------------------------------------------
--------------------------------------------------------------------------------

DROP TABLE #to_log
go

... and, the processing happens here!

Now, the real deal.

Last but far from least, you are left with the job of writing the processing procedure.

Even if it were possible to intercept a COMMIT by a trigger, the resulting output to be processed would be very similar with this.

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'SP_2PROC') DROP PROCEDURE SP_2PROC
GO

CREATE PROCEDURE SP_2PROC AS
BEGIN
    -- Important!
    --
    -- Use WITH(NOLOCK) to avoid dead-locks
    --
    -- Do NOT use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED here,
    -- unless you know what you are doing
    -- (this isolation level is often safe in other contexts)

    -- You, now, shall build your process up from here, based on the
    -- query below

    SELECT *
    FROM
        TBL_2PROC WITH(NOLOCK)
    WHERE session_id = @@SPID
END
GO


Stored procedures prefixed with sp_MSdel, sp_MSins, sp_MSupd

Those procedures are created when SQL Server Replication (Transactional Replication) is configured for a table in the database. See this link for more info.


auto increment for the table with 1000 of records in mysql

You can set it to 1 with this command:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 1;

More info in the mysql manuals, here: https://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html


Will SSDT-BI work with SQL Server 2008 R2?

Step A in Question 2 is not necessary.  The SSDT-BI package contains Visual Studio 2013 shell and will install it for you if it isn't there (see the additional information).

As for will it work?  Kinda.  You will be able to run packages which load data into SQL Server 2008 R2, but you will not be able to take advantage of the project deployment model released in SQL Server 2012.

The reason why I answered Kinda is that you may have some problems running new packages using dtexec on your old server.  Microsoft's guidance on this isn't much more than a stopsign... So YMMY (and please post back here with your experience!)


Insert Array of JSON-s into Postgres table

there are many json functions built into postgresql

the function you are looking for json_to_recordset()  the command will look something like this

insert into table_name (country, category)
   select * from json_to_recordset( '[
  {
     "country": "ANY",
     "category": "poetry"
   },
   {
      "country": "ES",
      "category": "science"
    }
   ]') as x(country text, category text);

On how elegant this is does not matter,  something has to parse the json text and turn it into a record,  postgresql may be able to do it faster than python.
Neither side looks pretty and neither side is fast because of the casting and text parsing that is going on


PostgreSQL 9.6 optimizer uses index even though it's slower

After adding an expression index to a table, you need to manually ANALYZE the table.  Otherwise PostgreSQL does not have access to the data it needs to make good decisions about the proper usage of that index.  It has to make wild guesses, and obviously those guesses are poor in this case (it thinks it will find 43462 rows, but actually finds 1237954)

Perhaps adding an expression index should automatically trigger an ANALYZE to take place--but currently it does not work that way.


How to create two database copies in two users from same dump file

If your dump has been created with exp then you may use imp for importing it. If you exported schema USERA and want to import these objects in schema USERB, then use command:

imp user/password@sid FROMUSER=USERA TOUSER=USERB FILE=yourfile.dmp

you may get more example of imp on the Import Export FAQ. You may import your dump two times using different USERB.


Well, it took a couple of years of my life, but I think I've figured it out.

I had to increase two mysql database configuration values (in /etc/mysql/my.ini) :

max_connections (from 100 to 500 , for now)
max_connect_errors (from 10 to 10.000)

After that, I ran flush hosts on the mysql prompt to clear any blocked hosts.

The forum experiencing these issues is generating some heavy database queries (mainly statistics into a table with over 1 million records - not sure why this would be "difficult", but that's another topic).

I assume this causes a temporary lockup and ultimately a lack of allowed connections. This in turn temporarily blacklists the webserver altogether!

After that, the database was accessible via the web interface again!


Getting error USE statement is not supported to switch between databases with Azure Synapse formerly SQLDW

If you check out the documentation for the USE statement, you'll see that it is not supported for Azure Synapse Analytics (formerly Azure SQL DW).
To change database contexts, you'll have to open an explicit connection to the desired database.
If you're having issues with your IDE, try specifying the desired database in your connection string when you connect so there is no switching required.