I want to start by saying everything I do is SQL Server so those are the examples I give.  In general however this applies to any form of code regardless of system.

Let's start by breaking this down a bit.

Upgrades

You have a system and are about to upgrade some or all of it.  For example upgrading an instance from SQL Server 2012 to 2014.  At this point testing is essential.  Unfortunately testing every part of even a small application is probably not going to be possible.  At that point I would do what I would call a "working" test.  Is the basic system working.  Run through your common tasks start to finish.  Don't test every option, just the main path.

When doing a SQL Server upgrade there is also some required reading.  Basically you want to read the Backward Compatibility entry for the new version (here is the 2014 one) and make sure that you don't have anything in any of the lists (breaking changes, behavior changes etc).

Application Code

Here we are looking at new/changing application code (because of course anything existing has already been tested right?).  In this case everything should be tested.  You should have test cases set up ahead of time and run through at least the majority of your affected features.  Preferably at this point you should also have someone else do a similar check.  This code is going to be in place, probably for a fairly long time, and used by a large number of people.  You want to make sure it works and works well.

One of the things that can really help with this is to generate a set of unit tests that are easily repeatable.  Steve Jones recommends using tSQLt for testing your TSQL code (SQL Server only I'm afraid).  But by doing this you can run through a fixed set of tests quickly and it will really aid in regression testing (testing everything, say before doing an upgrade).

Features/Configurations

Even more than application code changes you want to test new features and configuration changes thoroughly.  If for example you decide to start working with columnstore indexes for the first time you will need to test every piece of code that touches the affected tables.  Use the unit tests that you generated to test your application.  These features are probably new to you (and possibly new in the platform) and will probably have some gotchas you didn't expect.  As for configuration changes, you are talking about something that can affect your whole system, possibly significantly.  The rule of thumb is to test, and test carefully.  There are some changes that you won't really see until you get into an active system (possibly only your production system) but that isn't an excuse not to try them in a test environment first.

Ad hoc queries that reference/affect user data

When you have code that affects your user data you generally need to test it, even, and perhaps especially, because it's Ad Hoc.  Now that being said if you are running the same piece of code, over and over again, just with different parameters, then  you probably don't need to worry about testing each time.

For example you need to delete one or more Ads from the AdList table every quarter.

DELETE FROM AdList WHERE AdName IN ('January 2015 Ads','February 2015 Ads','March 2015 Ads')

At that point you've already tested the code (you are just changing fixed strings) and are probably fairly safe just running the code (assuming you have good backups just in case).

One easy way to test a DELETE, UPDATE or INSERT is to change them to a SELECT and run them, then confirm that the number and type of rows you expect are returned.

You might think that you don't need to test SELECTs because they don't actually change any data.  However you are running the code for a reason right?  Let's say you are doing research for your manager, who will in turn hand this data to their manager and so on.  You test to make sure that you aren't getting the wrong data (or blocking others from collecting their data).

Ad hoc queries that reference/affect system data

This is possibly the one exception to the "test everything" rule.  You are running information queries on system data.  The important thing here is to get back the data you expect.  If the query is something simple (querying a system view) then you are probably ok as long as you've checked what the view/columns really mean.  If the query is complex (say hitting 3 or 4 system views with calculations on the columns returned) then you may want to run a few tests just to make sure you are going to get back the data you expect.

Summary

In summary, yes, you want to test everything.  If it's important enough for you to write it and run it then it's important enough for you to test.  That doesn't mean you have to spend enormous amounts of time testing every branch of every line of code however. But some level of testing needs to be done.

Automated unit testing is your friend here.  With the advent of DevOps and Continuous Integration you will see more and more applications and methods of quickly and easily testing your code.  Of course that does require having a good test environment and data to go along with it, but that's a whole different discussion.


Insert data between 30 minute time intervals

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.)

DECLARE @i INT = 30; -- interval in minutes

DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME;
SELECT @ft = MIN([Start Time]), @lt = MAX([End Time]) FROM dbo.aTable -- WHERE?;

;WITH d1(dt) AS
(
  SELECT TOP (DATEDIFF(MINUTE,@ft,@lt)/@i+2)
    DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(MINUTE,@i*(ROW_NUMBER()
    OVER (ORDER BY [object_id])-1),@ft))/@i*@i,0)
  FROM sys.all_columns
), d2(s,e) AS (SELECT dt,DATEADD(MINUTE,@i,dt) FROM d1
), t AS
(
  SELECT [User],[Start Time],[End Time],
    StartInterval = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Start Time])/@i*@i, 0),
    EndInterval   = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [End Time])/@i*@i, 0)
  FROM dbo.aTable -- WHERE?
),
n AS
(
  SELECT d2.s, d2.e, [User], [Start Time], [End Time],
    sr = CASE WHEN [Start Time] >= d2.s AND [Start Time] < d2.e THEN 1 END,
    er = CASE WHEN [End Time] > d2.s AND [End Time] <= d2.e THEN 1 END
   FROM d2 INNER JOIN t ON d2.s BETWEEN t.StartInterval AND t.EndInterval
 )
 SELECT [Interval From] = s, [Interval To] = e, [User],
   [Elapsed Time] = CASE WHEN sr = 1 OR er = 1 THEN
       DATEDIFF(MINUTE, CASE WHEN sr = 1 THEN [Start Time] ELSE s END,
       CASE WHEN er = 1 THEN [End Time] ELSE e END)
   WHEN s = [End Time] THEN 0 ELSE @i END
 FROM n ORDER BY [User], [Interval From];


How can I manage Primary Key length limit of 900 bytes when using hirarchyid 2 columns?

Are you trying to create a key column that represents the a tree structure? If so, you could use concatenated, 32-bit hash values of each node in the tree to represent tre hierarchy.

For example, assume you wanted to represent the following:

C:\
C:\MyData\
C:\MyData\MyFolder\
C:\MyData\MyFolder\MySubFolder

You would store the following three rows with these keys:

Size = 4B:  Hash("C:")
Size = 8B:  Hash("C:")+Hash("MyData")
Size = 12B: Hash("C:")+Hash("MyData")+Hash("MyFolder")
Size = 16B: Hash("C:")+Hash("MyData")+Hash("MyFolder")+Hash("MySubFolder")

...where + represents binary concatenation. You would then store the concatenated key in a VARBINARY(900), which leaves you space for a tree that is 900 / 4 = 225 nodes deep. Hopefully this would be enough? This can be indexed.

Note that you don't need the hash to be without collisions, as long as you can reduce the number of keys that must be searched.


According to the IBM DB2 UDB for iSeries SQL Reference
V5R3  documents, the SYSCOLUMNS view contains an IS_NULLABLE column.

DB2 for z/OS 10 has a NULLS column that indicates if a column is nullable in SYSIBM.SYSCOLUMNS table

Potentially, SYSCAT.COLUMNS may have the IS_NULLABLE or NULLS column.

I'm not a DB2 expert, so this may or may not be applicable to your system.


In terms of support, there are some very basic differences you should be prepared for that have nothing to do with the databases themselves, but how they are supported.  PostgreSQL support comes from two, maybe three places.  For comprehensive support by the people who write the code, look to the pgsql-general mailing list.  Use the postgresql wiki (like this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions) and the docs to figure out the basics, and then post a question with as much relavent info as you can.  You'll generally get an answer within a few hours to a day, depending on how obscure your problem is.  The faster channel is the IRC channel (google it) which has a few folks on from around the world all the time.  Not as comprehensive an answer usually, but much faster.  Finally you can pay for commercial support and have someone you pay on the other end of the phone line to do anything up to and including log into your servers to fix problems.  While bulletin boards are a common place for MySQL support, they aren't for PostgreSQL so don't expect to find that to be the case.

In terms of code maturity and quality PostgreSQL is pretty high up there, and you will seldom if ever see crashes or data corruption that is PostgreSQL's fault.  It's generally more strict on interpretation of the SQL specification than either MSSQL or MySQL, so if you find a data type from one of those two dbs not working, it's likely not a legit SQL type but something created by those dbs (i.e. datetime or unsigned ints in MySQL).  If you have a query that works in MySQL and not in PgSQL, it's likely that MySQL is more permissive than the SQL spec actually allows, and PgSQL is following the spec.

The majority of PostgreSQL users who need high performance machines run some flavor of unix, i.e. Solaris, BSD, Linux etc.  The Windows port is ok, but is and always will be, a bit of a red-headed step-child in terms of performance and support.  There just aren't as many users of high performance Windows / PgSQL machines out there, and likely never will be.  It's stable on Windows, and a decent performer, but if you want 10k TPS you'd best look to Unix.

The GUI tools built just for pgsql are primitive compared to other dbs.  Most PostgreSQL DBAs use whiteboards or commercial applications like TOAD to do data modelling.  OTOH, PostgreSQL has the absolute best command line interface of any database I have ever used.  Learn the two commands \h and \? and from there you're set.  Complete syntax diagrams for all SQL statements are stored in the psql command line interface, and it has tab completion that is very very good.  The psql user interface is very much like a unix command shell, fast and powerful, but a bit cryptic for a beginner.  Learning it is well worth the effort though.

In terms of replication the latest versions are easily a match for MSSQL server and mostly a match for Oracle.  Note that there isn't just ONE replication solution for postgresql.  There's built in streaming replication, simple PITR as well.  Then there's Londiste from the folks at skype and Slony paid for originally by the folks at Afilias, who happen to run the .info, and .org domains on pgsql with slony replication.  Slony is still in many ways the most mature, but also the most complex, and the least forgiving.  It's not the absolute fastest,  but it is the most flexible.  Expect to spend a few weeks learning it if you need to use it.

PostgreSQL releases on a 12 to 18 month release cycle, and the cycle has been getting officially shorter of late, trying to maintain a one year release.  New releases are officially supported for 5 years.  Each release is often much faster or more capable or both than the last version, and the development is pretty transparent, assuming you're willing to pay attention to the pgsql-hackers mailing list.

One of the beauties of PostgreSQL is the speed with which bugs get patched.  I have, on two occasions, reported a bug to the pgsql-general list, and had a patch within 24 hours.  Both times it took longer to test it, schedule production downtime, and apply the patch than it took to get it.  A well described bug or problem gets almost immediate responses on the pgsql-general mailing list.  A whinging, vague complaint about a poorly defined problem often gets deafening silence.

Two books I'd recommend are Performance PostgreSQL 9.0 by Greg Smith, and the PostgreSQL Admin Cookbook from the same publisher Pakit (or something like that).  both are worth every penny to a pgsql admin / dev / dba type.


Relating two fields of one table to one field of another

Use view designer, add Table2, then add Table1 two times.

Select Table1.ID and drag till Table2.App and do the same with Table1_1 and Table2.Dependency.

SELECT Table2.Id,
       Table2.App,
       Table2.Dependency,
       Table1.Appname,
       Table1_1.Appname
FROM
       Table1 AS Table1_1
INNER JOIN
       (Table1 INNER JOIN Table2 ON Table1.Id = Table2.AppId)
       ON Table1_1.Id = Table2.Dependency;


How can I stop script execution if insert value is null and set value if not null?

SET NOEXEC ON is not MySQL syntax.

The thing to do is to avoid selecting it:

INSERT INTO tag (table, repr, tag, value)
    SELECT 'product' AS table,
            @Id AS repr,
            'product_code' AS tag,
            @Code AS value`        -- The WHERE will prevent nulls
        WHERE @Code IS NOT NULL;   -- avoid generating anything to insert when NULL


Copy all agent jobs to separate files based on date and keep five copies

A couple of suggestions:

Don't ask us to write an entire system for you.  It's probably ok if you have a particular problem with how some script operates, as long as it's DBMS related.
Realize that all SQL Server Agent Jobs are stored in the msdb system database.  In order to "save" jobs you could simply take a backup of the msdb database daily.  You should do that anyway, since there is a lot of useful history, etc, stored in there.
If you really need to "script" out the jobs, they're stored in msdb in the dbo.sysjobs and dbo.sysjobsteps tables.


MySQL user permission on stored procedure

As the error message itself says that  user do not have the execute permissions.

mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'

You need to grant the Execute Permission to that user.For that you need to login as root user and grant the permission as

 grant execute on db.* to user@localhost;

For your other queries :

Yes It is possible that your username is an empty string but it is not safe to create the users like this.
For creating and granting privileges in brief  have a look at This Link.
Yes you can grant  all the privileges on all entities within a database.
for this you can execute a command like

Login as root user and issue a command

GRANT ALL ON DB_NAME.* TO 'USER'@'HOST' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;


Executing TSQL script generated from SSMS in SQLCMD. Failing on quotes

$(...) denotes a variable in SQLCMD, so it's interpreting your data as a variable.

Use the -x command-line option to disable variables.


Index rebuild - Not reducing fragmentation

The issue could be not enough contiguous free space inside the database to arrange the index consecutively.  A work around to a very large index that won't defragment, one solution is to pre-size a new database and move all your objects to that DB, then recreate your indexes there as this will surely do the trick.


Prefix scale for UTL_MATCH.JARO_WINKLER_SIMILARITY

You are right that the Oracle documentation does not mention a prefix scale factor. This leads me to conclude that what Oracle is offering is a simpler version of the formula where there is no option to choose a prefix length. There is no other Oracle documentation on this that I know of and it looks unchanged from 10g when it was first implemented.
Even if there was a hidden implementation it would not be advisable to use it in production code as there is no guarantee of support or that it will remain available.
From the Wikipedia reference you gave it looks like you could create your own function which implements the Jaro Winkler similarity formula exactly.


Is there any way to trim data automatically

Yes, one way to do this will be to create a insert trigger on the table...can trim the values before inserting into the table ..


Is there anyway to tell up front whether the blanket grant has been
  applied? For the blanket grant I don't see any users set up under the
  Permissions page in SSMS for any of the SPs in question and yet it
  just works.

Below is a TSQL method to find all explicit DB object permissions and members of fixed or custom DB roles per each DB you run it against. Just search the results for the value of the role name you need to confirm the user is a member of beforehand and that should suffice for your need.

/*
Script DB Level Permissions v2.1
Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/
DECLARE @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR
FOR
/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --]
    ,1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --]
    ,1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --]
    ,3 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --]
    ,4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN (
        'U'
        ,'S'
        ,'G'
        ) -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --]
    ,5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --]
    ,6 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) IN (
        --get user names on the database
        SELECT [name]
        FROM sys.database_principals
        WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
            AND [type] IN (
                'G'
                ,'S'
                ,'U'
                ) -- S = SQL user, U = Windows user, G = Windows group
        )
--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,8 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE
        WHEN PERM.STATE <> 'W'
            THEN PERM.state_desc
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) --select, execute, etc on specific objects
    + CASE
        WHEN cl.column_id IS NULL
            THEN SPACE(0)
        ELSE '(' + QUOTENAME(cl.NAME) + ')'
        END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE
        WHEN PERM.STATE <> 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,9 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
    AND cl.[object_id] = PERM.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,11 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE
        WHEN PERM.STATE <> 'W'
            THEN PERM.state_desc --W=Grant With Grant Option
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE
        WHEN PERM.STATE <> 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,12 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser
WHERE [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN (
        'G'
        ,'S'
        ,'U'
        ) -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,14 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE
        WHEN PERM.STATE <> 'W'
            THEN PERM.state_desc --W=Grant With Grant Option
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name --CONNECT, etc
    + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
    + QUOTENAME(SCHEMA_NAME(major_id)) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default + CASE
        WHEN PERM.STATE <> 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,15 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.schemas s ON PERM.major_id = s.schema_id
INNER JOIN sys.database_principals dbprin ON PERM.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema
ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp

FETCH NEXT
FROM tmp
INTO @sql
    ,@sort

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @sql

    FETCH NEXT
    FROM tmp
    INTO @sql
        ,@sort
END

CLOSE tmp

DEALLOCATE tmp

Source


Keeping sales and purchase data in the same table?

Even if you can at this point, it's not clear that you should.

We'll assume most of the potential technical issues don't apply: that sales or purchases don't connect to some other tables for example.

If you were going to do it, then shifting one or two columns, especially if not heavily used, off to a new linked table could work.

You could update indexes to include the new type column.

However, sales and purchases are logically different, and are used in different ways. Even if today they have few differences, that could change dramatically with time. New fields could be required, or new business rules or constraints, that might be difficult to support from the new single table.

Any reports would need to be updated, and would have to reference the new type column, and reference it correctly (which might seem easy, but....).

Various activities performed by your finance people could be complicated by this. If you ever moved to a double-entry accounting system, then the accounts affecting by the purchase of an item vs the sale of an item might be completely different. You would want them to sign off on the change.

Finally, you'd need to make sure that any auditors you may have to deal with would be OK with the change.

Meanwhile, what's the benefit? You might actually take up more database space (you have all the existing data, plus the need for at least one new column in every row). You've added another field to check to most queries involving the two tables now.

Ultimately, the two entities are logically significantly different, and your new structure hides that complexity, rather than emphasizing it.

All that said, I wouldn't say with 100% certainty that this change couldn't work out well for you - I would just want to be sure that it was very well thought out, that all relevant stakeholders were aware, and agreed - and, perhaps most importantly, that it resolved a real problem or concern about the current structure.


SQL Server : how can I measure database performance increase after index maintenance?

You could use the RML utilities to capture a workload trace before and after the index rebuild and compare execution times.

RML utilities come with a reporter application that can compare the workload analysis of two trace files.

You can download it from here:

X64: http://download.microsoft.com/download/0/a/4/0a41538e-2d57-40ff-ae85-ec4459f7cdaa/RMLSetup_AMD64.msi

X86: http://download.microsoft.com/download/4/6/a/46a3217e-f523-4cc6-96e9-df73dd0fdd04/RMLSetup_X86.msi

The comparison will be more meaningful if the two workloads are comparable, even better if identical.


Multiple transactional publication: pros and cons

Make sure both of these settings for your publication is set to False which is the default.  It these are set to FALSE and you DO NOT reinitialize your subscription snapshot should only be generated for new articles.

If you are changing any portieres of the article you have to reinitialize and recreate full snapshot.

  [ @immediate_sync=] 'immediate_synchronization' Specifies if the
  synchronization files for the publication are created each time the
  Snapshot Agent runs. immediate_synchronization is nvarchar(5), with a
  default of FALSE. If true, the synchronization files are created or
  re-created each time the Snapshot Agent runs. Subscribers are able to
  get the synchronization files immediately if the Snapshot Agent has
  completed before the subscription is created. New subscriptions get
  the newest synchronization files generated by the most recent
  execution of the Snapshot Agent. independent_agent must be true for
  immediate_synchronization to be true. If false, the synchronization
  files are created only if there are new subscriptions. You must call
  sp_addsubscription for each subscription when you incrementally add a
  new article to an existing publication. Subscribers cannot receive the
  synchronization files after the subscription until the Snapshot Agents
  are started and completed.

  [ @allow_anonymous=] 'allow_anonymous' Specifies if anonymous
  subscriptions can be created for the given publication.
  allow_anonymous is nvarchar(5), with a default of FALSE. If true,
  immediate_synchronization must also be set to true. If false,
  anonymous subscriptions are not allowed on the publication.

My personal preference is to group articles into multiple publications. That way even you have to reinitialize subscriptions and generate new snapshot you can run multiple jobs at the same time. You will need to make a choice if you want shared or individual (per publication) distribution agents.


I recommend you read Understanding how SQL Server executes a query, it has an explanation of how reads and writes work and how locking works.

The 10000ft view goes as follows:

read operators acquire shared locks on the data they read, before reading the data
write operators acquire exclusive locks on the data they modify before modifying the data
data locks are just strings, eg. a hash of the key being read scoped by database and object.
the lock manager maintains a list of all locks granted and detects incompatibilities, according to the Lock Compatibility matrix
incompatible requests are suspended until the incompatible grant blocking them is released
operators use a lock hierarchy to declare intent to read or update data at higher level (page or table level, ignoring partition level options). This allow operators to lock entire tables w/o locking every individual row
lock lifetime and range locks are used to enforce higher isolation levels

This is really just the tip of the ice berg. The subject is vast. In your example, nobody can answer your question about what is being actually locked because it will depend on many factors. Of course, no application should issue a SELECT * FROM Table1  because it's missing a WHERE clause and is using *. These are bad practices because, among other things, they will lead exactly to lock contention.

If you encounter read vs. write locks you need to look into row versioning and snapshot isolation. Read Understanding Row Versioning-Based Isolation Levels.


How to remove blockages in MongoDB when multiple write query comes for same collection at same time

From the database perspective you can try using, MongoDB 3.0 with the Wired Tiger storage engine to eliminate these waits. MongoDB 3.0 with the Wired Tiger uses Document level blocking and thus brings down the waits considerably.

In case this is not possible, then you cannot do much on the database layer and will either need to live with these waits or will need to handle it from the Application layer.

A tentative approach, which can be used to  provide control of the waits from the Application layer can be to queue the respective transactions in the application so that the user does not need to make the changes multiple times.


Your basic query is correct, and I don't really see how you could make it simpler, given the definition of what you want to count.

You can simplify it slightly. You don't need the first DISTINCT; and you actually don't need a main FROM:

SELECT
   (SELECT COUNT(DISTINCT rule_id)
      FROM ruleresult
     WHERE code = '1.2' AND result = 'V') as v_count,
   (SELECT COUNT(DISTINCT rule_id)
      FROM ruleresult
     WHERE code = '1.2'
        AND result = 'W'
        AND rule_id NOT IN
        (SELECT rule_id
           FROM ruleresult
          WHERE code = '1.2'
            AND result IN ('V'))) as w_count,
    (SELECT COUNT(DISTINCT rule_id)
       FROM ruleresult
      WHERE code = '1.2'
         AND result = 'P'
         AND rule_id NOT IN
         (SELECT rule_id
            FROM ruleresult
           WHERE code = '1.2' AND result IN ('V', 'W'))) as p_count ;

| v_count | w_count | p_count |
|---------|---------|---------|
|       1 |       1 |       0 |

Check it at SQLFiddle.

You can also have a different approach:

You first PIVOT your rules
You then decide, for every rule, whether the result that counts is V, W or P
You sum how many of them you have.

All together, this would be:

SELECT
    sum(is_v) AS v_count,
    sum(is_w) AS w_count,
    sum(is_p) AS p_count
FROM
(
    -- Computing whether we should consider V, W or P
    SELECT
        rule_id,
        case when count_v0 > 0 then 1 else 0 end as is_v,
        case when count_w0 > 0 and count_v0 = 0 then 1 else 0 end as is_w,
        case when count_p0 > 0 and count_w0 = 0 and count_v0 = 0 then 1 else 0 end as is_p
    FROM
    (
        -- Pivoting V, W, P
        SELECT
            rule_id,
            count(case when result='V' then 1 end) AS count_v0,
            count(case when result='W' then 1 end) AS count_w0,
            count(case when result='P' then 1 end) AS count_p0
        FROM
            ruleresult
        WHERE
            code = '1.2'
        GROUP BY
            rule_id
    ) AS s0
) AS s1;

You can check this second option at RexTester. This second approach is (most probably) more efficient if your table is big, because it will only scan it once...


Is there an Open Source Metadata Management Solution?

MIKE?
What is MIKE2.0?
MIKE2.0 which stands for Method for an Integrated Knowledge Environment, is an open source methodology for Enterprise Information Management that provides a framework for information development . The MIKE2.0 Methodology is part of the overall Open Methodology Framework.


Any program / command to see a preview of .sql dump file?

For MySQL and PostgreSQL at least, database dump files are just text files full of SQL commands. So you can use the usual command line tools to get some idea of the contents. For example

grep 'CREATE TABLE' dump.sql | wc -l

to count how many tables are created. Not as slick as what you're hoping to find, of course.


Filter condition not correctly applied to Clustered Columnstore index

This is a bug with SQL Server. If a column is deleted from a table with a clustered columnstore index, and then a new column is added with the same name, it appears to be using the old, deleted column for the predicate. Here is the MVCE:

This script starts off with 10000 rows with statusId of 1 and statusId2 of 5 - then drops the statusID column and renames statusId2 to statusId. So at the end all rows should have a statusId of 5.

But the following query  hits the non clustered index...

select *
from example
where statusId = 1
    and total <= @filter
    and barcode = @barcode
    and id2 = @id2

... and returns 2 rows (with the selected statusId different from implied by the WHERE clause)...

+-------+---------+------+-------+----------+
|  id   | barcode | id2  | total | statusId |
+-------+---------+------+-------+----------+
|     5 |    5    | NULL |  5.00 |        5 |
| 10005 |    5    | NULL |  5.00 |        5 |
+-------+---------+------+-------+----------+

... whereas this one accesses the columnstore and correctly returns 0

select count(*)
from example
where statusId = 1

MVCE

/*Create table with clustered columnstore and non clustered rowstore*/
CREATE TABLE example
(
id        INT IDENTITY(1, 1),
barcode   CHAR(22),
id2       INT,
total     DECIMAL(10,2),
statusId  TINYINT,
statusId2 TINYINT,
INDEX cci_example CLUSTERED COLUMNSTORE,
INDEX ix_example (barcode, total)
);

/* Insert 10000 rows all with (statusId,statusId2) = (1,5) */
INSERT example
       (barcode,
        id2,
        total,
        statusId,
        statusId2)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 1,
                   statusId2 = 5
FROM   sys.all_columns c1, sys.all_columns c2;

ALTER TABLE example
  DROP COLUMN statusid
/* Now have 10000 rows with statusId2 = 5 */

EXEC sys.sp_rename
  @objname = N'dbo.example.statusId2',
  @newname = 'statusId',
  @objtype = 'COLUMN';
/* Now have 10000 rows with StatusID = 5 */

INSERT example
       (barcode,
        id2,
        total,
        statusId)
SELECT TOP (10000) barcode = row_number() OVER (ORDER BY @@spid),
                   id2 = NULL,
                   total = row_number() OVER (ORDER BY @@spid),
                   statusId = 5
FROM   sys.all_columns c1, sys.all_columns c2;
/* Now have 20000 rows with StatusID = 5 */

DECLARE @filter  DECIMAL = 5,
        @barcode CHAR(22) = '5',
        @id2     INT = NULL;

/*This returns 2 rows from the NCI*/
SELECT *
FROM   example WITH (INDEX = ix_example)
WHERE  statusId = 1
       AND total <= @filter
       AND barcode = @barcode
       AND id2 = @id2;

/*This counts 0 rows from the Columnstore*/
SELECT COUNT(*)
FROM   example
WHERE  statusId = 1;

I have also raised an issue on Azure feedback portal:

And for anyone else who encounters this, rebuilding the Clustered Columnstore Index fixes the problem:

alter index cci_example on example rebuild

Rebuilding the CCI only fixes any existing data. If new records are added, the issue arises again on these records; so currently the only known fix for the table is to recreate it entirely.


Select all data from two tables with partial common data

Check out this db<>fiddle for an example. When running your example SELECT statement, it returned an error because the second and third SELECT in the UNIONs reference aliases from the other SELECT statements.

I'm not sure if this is is something you can do in PL/SQL (I have an MSSQL background), but the code in the fiddle returns the expected result set.


How to determine cause of runtime increase given two query plans with SpillToTempDb warning

The cost is the same (1%) for both the slow and fast cases. Does that
  mean the warning can be ignored? Is there a way to show "actual" times
  or costs. That would be so much better! Actual row counts are the same
  for the operation with the spill.

The cost shown is always the optimizer's estimated cost of the iterator, computed according to its internal model. This model does not reflect your server's particular performance characteristics; it is an abstraction that happens to produce reasonable plan shapes most of the time for most queries on most systems. There is no way to show 'actual' costs/execution times per iterator.

  Besides performing a manual text diff of xml execution plans to find
  the differences in warnings, how can I tell what the 1500% increase in
  runtime is actually due to?

Typically, you can't. Spill warnings (sorts, hashes, exchanges) are new in execution plans for 2012, but they are just an indication of something you should investigate and look to eliminate if possible. The impact of a particular spill is something that needs to be measured - it is not possible to say that a spill of a particular type will always result in an x% performance drop for example.

  For slow case, tempdb before/after (select *
  sys.fn_virtualfilestats(db_id('tempdb'),null)) (only showing a few
  100ms of latency)

Spilling to tempdb and back is certainly undesirable, but the overall impact is hard to assess. For sort and hash spills, the impact is largely due to the I/O and access pattern, which may be small-block synchronous I/O e.g. for sort spills. With ~100ms of latency, you don't need too many synchronous I/Os to introduce a significant delay. The nature of the process and I/O patterns means tempdb spills can still be a problem on very low latency storage systems like fusion-io.

For exchange spills, there is an extra delay. The intra-query deadlock must be detected by the regular deadlock monitor, which by default only wakes up once every 5 seconds (more frequently if a deadlock has been found recently).

The resolver must then choose one or more victims, and spool exchange buffers to tempdb until the deadlock is resolved. The amount of spooling needed and the complexity of the deadlock will largely determine how long this takes.

Ultimately, preserved ordering is a Very Bad Thing for parallelism in general. Ideally, we want multiple concurrent threads operating on data streams with no inter-dependence. Preserving sort order introduces dependencies, so producer and consumer threads in different parallel branches can become deadlocked waiting for order-preserving iterators to receive rows to decide which input sorts next in sequence.

The precise nature of the deadlock depends on data distribution and per-thread sort order at runtime, so it is typically very hard to debug. Hence my recommendation to avoid order-preserving iterators in parallel plans, especially at high DOP. I do explain a very simplified example of an order-preserving parallel deadlock in some talks I do, but real examples are always more complex, though the underlying cause is the same.

In case the concepts are not familiar, it may help to follow the following example, reproduced from the (somewhat epic) 1993 paper Query Evaluation Techniques for Large Databases by Goetz Graefe:

  If a different partitioning strategy than range-partitioning is used,
  sorting with subsequent partitioning is not guaranteed to be
  deadlock-free in all situations. Deadlock will occur if (i) multiple
  consumers feed multiple producers, and (ii) each producer produces a
  sorted stream and each consumer merges multiple sorted streams, and
  (iii) some key-based partitioning rule is used other than range
  partitioning, i.e., hash partitioning, and (iv) flow control is
  enabled, and (v) the data distribution is particularly unfortunate.

  Figure 37 shows a scenario with two producer and two consumer
  processes, i.e., both the producer operators and the consumer
  operators are executed with a degree of parallelism of two. The
  circles in Figure 37 indicate processes, and the arrows indicate data
  paths. Presume that the left sort produces the stream 1, 3, 5, 7, ...,
  999, 1002, 1004, 1006, 1008, ..., 2000 while the right sort produces
  2, 4, 6, 8, ..., 1000, 1001, 1003, 1005, 1007, ..., 1999.

  The merge operations in the consumer processes must receive the first
  item from each producer process before they can create their first
  output item and remove additional items from their input buffers.
  However, the producers will need to produce 500 items each (and insert
  them into one consumer’s input buffer, all 500 for one consumer)
  before they will send their first item to the other consumer. The data
  exchange buffer needs to hold 1000 items at one point of time, 500 on
  each side of Figure 37. If flow control is enabled and the exchange
  buffer (flow control slack) is less than 500 items, deadlock will
  occur.

  The reason deadlock can occur in this situation is that the producer
  processes need to ship data in the order obtained from their input
  subplan (the sort in Figure 37) while the consumer processes need to
  receive data in sorted order as required by the merge. Thus, there are
  two sides which both require absolute control over the order in which
  data pass over the process boundary. If the two requirements are
  incompatible, an unbounded buffer is required to ensure freedom from
  deadlock.


Improve PostgreSQL SELECT performance in a parent table (inheritance)

But when I run the same SELECT in the father table, it is slow. I saw the EXPLAIN and seems that PostgreSQL uses each child's index, instead of use only the father index.

To search in the father table only, use the ONLY keyword:

 SELECT FROM ONLY MasterAccount.imovel ....

The manual:

  If ONLY is specified before the table name, only that table is scanned.


Monitoring blocking queries in SQL Server 2012

The Microsoft Tiger Team has posted a set of scripts that handle this for you. Check this link out

https://github.com/Microsoft/tigertoolbox/blob/master/Running-Blocked-Processes/view_Running_Blocked_processes.sql

This will give you a lot of information about the processes running on your system. You can modify this to send the data to a table if that helps you track issues and do some trending.

Check out the rest of the scripts in the toolbox they have published.

https://github.com/Microsoft/tigertoolbox


Error When Trying to Run MDW Data Collector

So apparently network packet size setting in sp_configure was changed up to 32kb when it needed to be under 16kb. Once I reconfigured the setting, the jobs ran successfully. Who would have thought...


How to restore a database using mdf and ldf files in SQL Server 2008?

Is this possible to restore a particular database using corresponding mdf and ldf files?

Yes, you can try to attach them, probably just try to attach using:

CREATE DATABASE restored_copy_of_db_name
ON (filename = 'c:\temp\backup_of_mdf.mdf'),
   (filename = 'c:\temp\backup_of_ldf.ldf')
FOR_ATTACH;

It is very common for this to fail, because the log file gets damaged from this type of event (see this post for reasons we don't rely on mdf/ldf files as backups), so then you can try:

CREATE DATABASE restored_copy_of_db_name
ON (filename = 'c:\temp\backup_of_mdf.mdf')
FOR_ATTACH_REBUILD_LOG;

  Do we need to have any mdfs from the system databases?

If you're trying to restore full functionality out of the gate, then your attached database above will not include server-level logins, jobs, etc. So you may need master and msdb in order to recover some of that information (but I would attach it as a user database (msdb_copy), rather than replacing msdb, and try to restore master on a throwaway instance - see this thread for possible ideas here, but I would not be confident you'll get that info back).

  Steps and/or or best practices to restore that database into a new machine.

Nothing really in addition to the above, except, once you have these databases back, please set up a proper backup and recovery plan, which should include periodically testing that you can restore your databases - the fact that a backup was successful is not enough to ensure you won't be doing this again.


SQL Server: modify data file size and shrink

When DB itself perform the shrink operation then it computes the value stored in the DB and try to store them in such a way that they takes less size, hence you see reduced size of DB. OTOH when you are saying that you want to modify size, then are you telling your DB in which order it should rearrange your data such that your data remain unchanged?


The task as I understand it
Pick rows from a table where the period overlaps with a given time frame. Determine distinct ranges of overlapping periods within that set and return the greatest sum(quantity) from any range.
Requires Postgres 9.2+, since there are no range types in older versions.
Assumptions

"Overlapping" is meant in a cascading manner, like tiles on a traditional roof: those are "overlapping" (rainproof), though the highest tile does not directly overlap with the lowest.

All values in period have inclusive bounds ([]). Else you have to adjust for exclusive bounds. (The range of the input parameter can still have arbitrary bounds.)

We filter for exactly one event_type_id. Else you have to add PARTITION BY event_type_id to the window definition.

quantity is an integer. Else you have to adjust for the type in calculations.

Quantities for overlapping periods are counted fully, even if parts of the period are outside your given time frame.

Even works for duplicates on (event_type_id, period).

Best performance with a single subquery
This should be dynamite.
SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
FROM  (
   SELECT lower(period) AS p_start
        ,(sum(quantity)                      OVER w)::int AS running_sum
        , lead(lower(period), 1, 'infinity') OVER w
                        > max(upper(period)) OVER w AS range_end
   FROM   event
   WHERE  event_type_id = 1
   AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange
   WINDOW w AS (ORDER BY lower(period))
   ) sub
WHERE  range_end
ORDER  BY 1 DESC
LIMIT  1;

All three window functions in the subquery can use the same window. This avoids additional sort operations and should be fastest.
Verbose CTE variant with more explanation
Same query, just more verbose and slower, since CTEs materialize derived tables and pose as optimization barriers.
WITH cte1 AS (
   SELECT quantity
        , lower(period) AS p_start
        , upper(period) AS p_end
   FROM   event
   WHERE  event_type_id = 1
   AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange
   )
,    cte2 AS (
   SELECT (sum(quantity)               OVER w)::int AS running_sum
        , lead(p_start, 1, 'infinity') OVER w               -- next start ..
                          > max(p_end) OVER w AS range_end  -- .. after last end
        , p_start, p_end
   FROM   cte1
   WINDOW w AS (ORDER BY p_start)
   )
SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
                    -- subtract the previous sum to get the sum of this range
     , p_end::text
FROM   cte2
WHERE  range_end    -- only rows at the end of each range
ORDER  BY 1 DESC    -- biggest sum first
LIMIT  1;           -- only return the winner

sqlfiddle for Postgres 9.3
db<>fiddle here for Postgres 12
You need an index for this to be fast with big tables. The best option would be a GiST index on (event_type_id, period). Details:

Optimizing queries on a range of timestamps (two columns)

Explanation
Filter rows that match your conditions, then sort by the start of the time range (lower(period)) and calculate:

A running sum of quantity (running_sum).
The start of the next period: (lead(lower(period), 1, 'infinity')).
Defaults to 'infinity' for the last row to include the last range.
The latest end of any period so far max(upper(period)).

If 2. is later than 3. it's the end of a (sub-)range (range_end).
In the outer SELECT filter rows with range_end and subtract the previous total to get the sum for the range. ORDER BY that result and return the first (LIMIT 1) greatest sum_quantity. Voilá.
Aside
To select all of Jan 7th, 2016, the clean expression is:
'[2016-01-07 00:00:00+00,2016-01-08 00:00:00+00)'::tstzrange
Not:
'[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange
Details:

PostgreSQL splitting time range into days

Since the default precision of timestamp values is 6 decimal digits (microseconds resolution), you could also use:
'[2016-01-07 00:00:00+00,2016-01-07 23:59:59.999999+00]'::tstzrange
But that's messy and depends on an implementation detail that might change (even if unlikely). It's not subject to rounding errors, since timestamps are stored as integer values in modern Postgres:

Ignoring timezones altogether in Rails and PostgreSQL


I've finally been able to do some testing and playing with the query and can give you more concrete suggestions.  I used AdventureWorks for my sample so I had some actual data to work with.

Option 1 - Add Indexes

This will give you the best performance increase for the least amount of effort.  I saw an increase of 40% in query execution in my sample set just by adding three indexes.

Tables #TR and #TF. Add an index on the saledate, employee, and include the amount.

CREATE INDEX IDXNC_TFSaleDateEmployee ON #TF (saledate, employee) INCLUDE (saleshippingamt)

CREATE INDEX IDXNC_TRSaleDateEmployee ON #TR (saledate, employee) INCLUDE (saleamount)

for the #FinalData, you should have a clustered index on the employee field.

CREATE CLUSTERED INDEX IDXC_FinalDataEmployee ON #FinalData (employee)

Option 2 - Change your query

Your query could use some tweaking, it may even allow you to get rid of the temp table all together.

This query also performs a little faster, but the indexes will give you the best increase in performance.

;WITH CTE_Employee AS
    (
    SELECT DISTINCT employee
    FROM (
        SELECT employee
        FROM #TR
        UNION ALL
        SELECT employee
        FROM #TF
        ) AS D
    )
    , CTE_TR AS
    (
    SELECT employee
        , SUM(saleamount) AS TotalDue
    FROM #TR
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
    )
    , CTE_TF AS
    (
    SELECT employee
        , SUM(saleamount) AS TotalDue
    FROM #TR
    WHERE saledate BETWEEN '2017-01-01' AND '2017-01-25'
    GROUP BY employee
    )

SELECT S.employee
    , TR.TotalDue
    , TF.TotalDue
FROM CTE_Employee AS S
    LEFT OUTER JOIN CTE_TR AS TR ON TR.employee = S.employee
    LEFT OUTER JOIN CTE_TF AS TF ON TF.employee = S.employee

General Comments

You can name CTEs however you like, but it does confuse things a little bit if you give them the same name as the table they represent.  I like to give them a CTE_* prefix, but it's optional.

You can also chain CTEs as I've done here.

In your original query, you were doing two aggregate operations when you were getting the totals for the totalcount in the #FinalData. That would also be something to keep an eye out for, you can see that it's unnecessary, but SQL will do the sort and sum operation multiple times.  You could just join the CTE table on instead of doing a sub-select.

You Had

UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN (Select employee, SUM(Totals) TotalCount
            FROM TF
            GROUP BY employee) As t2
ON t.employee = t2.employee

Should be

UPDATE t
SET tf = t2.TotalCount
FROM #FinalData t
INNER JOIN TF As t2 ON t.employee = t2.employee


Confused about what to use to create the data

You can use PostgreSQL's inheritance feature.


Query to return all Oracle Deadlocks in 12c

Sure:

select
  ss.value
from
  v$sysstat ss
  join v$statname sn using (statistic#)
where
  sn.name = 'enqueue deadlocks'
;

I did not test it extensively though.


New Server Query AD is giving permission error

I would presume the server Adam service GUID changed and or need to create a new computer object for your (virtual) as it's associated with the physical (adsi).

Presuming you've done all the user, machine creation. Perhaps run a com lime logo to confirm the ticket is valid database service and machine etc.?

Objects in ADS is what I'd audit and confirm.
Command kinit


Try running this,

sudo -u postgres dropdb myDatabase


Can anyone help me understand the following paragraph?

I do not know if the following can be useful to explain the involuted phrase that you cited, however all the following facts are true.

A relation must have at least a candidate key and can have more then one candidate key.
By definition, an attribute is prime if it is part of any candidate key.
A transitive dependency is a dependency that derives from two other dependencies through the “transitive” Armstrong’s axiom (if X -> Y and Y -> Z, then X -> Z), so for instance if in a relation R(A, B, C, D) we have that A -> BC and BC -> D, the dependency A -> D is a transitive dependency.
A transitive dependency can be problematic (that is can produces data anomalies) if the second dependency of the transitive rule (the “Y -> Z” dependency of the rule) has a determinant which is not a full candidate key, but (a) a subset of a candidate key, or (b) contains attributes that are not prime.

Consider the relation R(A, B, C, D E), with dependencies AB -> CDE, CD -> ABE, C -> E. In this case there are two candidate keys, AB and CD, so C, the determinant of C -> D, is a prime attribute, since it is an attribute of the candidate key CD (“it is not a nonprime attribute”).

Note that in the previous example the attribute E is transitively dependent from AB (from AB -> CDE we can derive AB -> C, and from  AB -> C and C -> E, we can derive AB -> E) and the relation is not in 3NF, neither in BCNF.


How to create a read-only server role on SQL Server 2012?

Posting this as an answer just because it is too long for a comment, and because it will be relevant to other users pretty soon.

SQL Server 2014 adds some new server-level permissions that will assist with exactly this type of scenario - they were designed with auditing in mind, but this type of requirement seems to fit that bill as well. You could simply add the following two permissions to a server-level login:

CONNECT ANY DATABASE

SELECT ALL USER SECURABLES

The former, like it sounds, allows the login to connect to any database. The nice thing about this is that it will allow this even for databases that are created in the future (provided you don't set explicit deny, which is how you can protect certain user databases from logins that have this permission). The latter allows the login to perform read operations on any database they have access to - so they can SELECT from tables, views, UDFs etc. but they will not be able to perform any UPDATE operations (I haven't tested if this permission understands when a stored procedure performs DML). These work great in combination if you want to give a login wide-open read access to the entire server, or to be more fine-grained you can grant traditional CONNECT privileges to certain databases, and the SELECT ALL USER SECURABLES right will only function for those databases where the login has explicit access to.

The 2014 security changes are documented here - well, partially; they forgot about the database-level permission ALTER ANY DATABASE EVENT SESSION - though that's not relevant here.


We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff accidentally going into old database, so taking the database offline for awhile then drop it would be the solution?

Instead of making database OFFLINE, make the database "READ ONLY" using :

ALTER DATABASE database_name SET READ_ONLY

And monitor what apps/ users are still connecting using server side trace.

  Do you know if SQL Server backups are compressed and can be saved to tape?

Yes, backup compression is available in sql server 2008 R2 standard edition and up.

Enable backup compression :

EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE WITH OVERRIDE ;

When taking backups, use BACKUP DATABASE ... with COMPRESSION.

You can save the backups to tape. Thats what we do in our company as well for data retention (We have to retain data for 10+ years). Alternatively, you can use Windows Azure Blob Storage Service.

Make sure that the archive bit is set (attribute 'A' on the backup file).


How can I alter the output for the sql desc command?

SELECT utc.column_name "Name"
   , utc.nullable "Null?"
   , concat(concat(concat(utc.data_type,'('),utc.data_length),')') "Type"
   , ui.uniqueness
FROM user_tab_columns utc
LEFT JOIN user_ind_columns uic ON uic.table_name = utc.table_name
   AND utc.column_name = uic.column_name
LEFT JOIN user_indexes ui ON ui.table_name = utc.table_name
   AND uic.index_name=ui.index_name
WHERE utc.table_name='TABLE_NAME_TO_DESCRIBE';


Installing Oracle 11gR2 on Oracle Linux 6 - error possibly related to database (unique) name

you must edit bash-profile then install oracle database .
in that you can set oracle_home and oracle_sid and etc .
i have similar mistake and resolve it with edit bash-profile in oracle user to real and correct values
at the end , you can refer to this article , it is useful

http://www.oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-6.php


In most DBs a NOT NULL column will be more efficient in terms of stored data for the reason you state, and also more efficient to query and index - so unless you want to allow NULLs in a column you should explicitly disallow them.

There will be a slight performance implication, as the extra NOT NULL constraints will potentially need to be checked for each row you affect with any INSERT or UPDATE but as most databases are relative write-light and read-heavy this is probably not a concern (the little extra time taken is unlikely to be noticeable at all anyway as it is a CPU-bound operation where the rest of the insert/update operation will be IO-bound and so a much more significant bottle-neck) and it gives you some "free" data checking so your code (or other people's code) can not accidentally put NULLs where other code is not expecting them and so may give incorrect results in their presence.

Edit: As Peter points out in his comment the above is a generalism and may not hold true for all DMBSs, though I'm pretty sure it does for mysql and mssql. Other complications in the area might include features like sparse tables (as implemented MSSQL 2008 for instance) which will alter the performance dynamics of (not)nullable columns.


Large cost change in execution-plan why?

As far as I remember, 10g had issues with bind peeking, and symptoms are very similar to what you have.  I suspect that good plan picks index (MY_TABLE_MY_COL_01_DATE ) for fo.eventdate>sysdate - :2 when value of variable was small, or it was very few rows that satisfy that condition according to statistics.  Then either due to statistics change, or you ran it with different value of variable, or sql got evicted from SGA, either it generated a new plan . You might try to check V$SQL_SHARED_CURSOR , it shows why a new cursor was generated.  In theory, optimizer should switch between FTS and index scan, but in my memory it never happened in 10g - once it chose FTS, it would keep using it.  If upgrading Oracle is not an option, I'd try using literal in this particular case instead of bind variables, i.e. fo.eventdate>sysdate - 1 . (or maybe combine literals and bind variables to help optimizer, say if it's never greater than 10 days you can do fo.eventdate>sysdate - 10 and  fo.eventdate>sysdate - :2


Don't rebuild indexes < 1000 pages - then why bother having them?

Just because the index doesn't require rebuilding does no mean is useless. A 99999% fragmented index is still 9999 times better than a heap for looking up a key. The negative effects of fragmentation are heavily, heavily, heavily, heavily overestimated in popular DBA culture.

Locking is a valid point (w/o indexes all read-write operations are guaranteed to collide), but not the only one. Even if the data is read-only, so locking is never an issue, it still needs an index for seek and range scans operations.

  I'm imagining a phone book where every single entry is in the wrong place

This is a very misguided interpretation of what fragmentation is and its effects. A fragmented phone book still has all the entries in order. Looking up an entry still takes 2-3 page flips, it will be something like:

the cover says to find 'P's go to page 645.
page 645 says to find 'PA's go to page 56.
page 56 says all 'PAUL's are on page 646.

So yes, you flip back and forth 2-3 times. The fragmented phonebook also may have every page half-blank, so it weight twice as much as the newly minted phonebook.

Compare it with the heap:

the cover will say I don't know where 'PAUL's are, but if they're in the book, I'm sure they're between the front cover and the back cover! And do remember that finding the first 'PAUL' does not mean you found all of them...

The only data/workload that really doesn't need indexes is columnstore/data warehousing. Columnstore access and processing is completely different and can get away w/o indexes, because every query is expected to scan all data anyway. Segment elimination, per-column IO and other forces contribute to keeping these always-end-to-end scans competitive.


mysql master and slave has different schema alter table add column

Well, I see no reason why the ALTER would not propagate to the Slave(s) and work there.  Suggest you apply the ALTER to each Slave.  You might need a SLAVE START to get them running after that.  Then proceed with the ADD UNIQUE, etc.

PS, A UNIQUE KEY is a "key", so you have a redundant index on email.


Access Query Design View: Show results in two columns from one table

Put this in the SQL View of the query design and you can then see the GUI required to build what you want.

select ID
, (select colorname from colors where id = outfits.topcolorid) as TopColorName
,(select colorname from colors where id = outfits.bottomcolorId) as BottomColorName
from outfits;


Look in your my.ini file where the variable "datadir" points.
Install another mysql (same version or sup better) and copy everything from "datadir" to the new "datadir" in the new installation, start the mysql service. Look in the log maybe you have to run mysql_upgrade script or face another issue, then connect to the database via the workbench.


Can a Couchbase application work with no network connection (like CouchDB)?

PM from Couchbase here. We've actually been working on an embedded NoSQL database for mobile dubbed "Couchbase Lite" (in parallel with SQL and SQLite) that plays exactly into the scenario you are describing. The idea is to provide your application with a JSON-based database that can:

Opportunistically sync to a backend cluster as needed
Utilize our built-in P2P support to replicate data between devices without going through a central point
Store data locally in its original JSON format for full-offline access to your data

Some initial reading can be found here (http://www.couchbase.com/mobile#lite) but feel free to get in touch with me if you'd like to discuss more in-depth.


How to connect to Microsoft Dynamics Rms 2.0 Running sql2008r2?

You're going to need SQL Server 2005 backwards compatibility components installed at the client PC for that version of RMS to connect to any SQL Server newer than 2005 if I recall correctly.  Good luck finding a download link. Microsoft seems to have nuked all of them from what I can tell.  If you are taking over support of a system, search for an msi installer package named "SQLServer2005_BC_x64.msi" or x86. You probably want x64 on any newer clients though.  Hopefully you have a local copy already in an install folder somewhere assuming the previous RMS clients needed it to connect to the same DB Server.
Hope this helps.


ALTER DATABASE ARITHABORT configuration minimum privilege needed

Well, ended up I changed the user options, so new sessions start with ARITHABORT ON. It worker out for me.

Here's a link better explaining how: Make SqlClient default to ARITHABORT ON

I've used the "Server-wide" part of it.


Select arbitrary single value for GROUP BY: What's the fastest option?

Your query does return the duplicate locations, but does not return the individual points (work orders) with the same location.
This returns those locations again (same as your query, just reformatted in a more compact notation):
select longitudex, latitudey, count(*)
from workorders
group by longitudex, latitudey
having count(*) > 1;

Then this returns the individual work orders that share the same location:
select objectid, longitudex, latitudey, shape
from workorders
where (longitudex, latitudey) in (
  select longitudex, latitudey
  from workorders
  group by longitudex, latitudey
  having count(*) > 1
);

That obviously only works if the geographical coordinates are exactly the same for work orders at the same location, down to all decimals. If not then you need to use spatial operators to compare the locations. Those are available with databases like Oracle (out of the box) or PostgreSQL (with the PostGis extension).


SQL Server 2019 Performance issue with large INSERT

From comments:
To all intents and purposes, those plans are identical. As such, I'd focus more on differences in the physical set up and server settings. If the query optimizer for 2016 and 2019 are arriving at identical plans, but, behavior is different, chasing optimizer settings, query hints, statistics, cardinality estimation and other plan driven decisions is a waste of time. Since the majority of the performance difference is in that clustered index scan (1 sec vs. 28 seconds) it's likely to be a disk or memory problem. Although the actual insert operation is also twice as long. Same likely issue. - Grant Fritchey


Recover Oracle database after drive loss Interview Question

The very first thing I'd try is copying the surviving control file then restarting the DB - the loss of the controlfile is what caused the DB to crash, and if the redo log group happened to be INACTIVE at the time, then I'm pretty much OK. I'd find this out from the alert log. So long as there is a disk in the right place, I can issue CLEAR LOGFILE and they will just be recreated.

If not then we have a problem - the SCN in the controlfile is ahead of the DBFs and we've no way to get there. We have no choice but to perform a point-in-time-recovery, and we have to discard those DBFs as well. In RMAN, restore database (assuming controlfile autobackup), recover until the highest SCN in the archived redo logs, then OPEN RESETLOGS. Since I have the cumulative incremental, I don't need the differential incremental (I'm not sure why you'd mix the two types? Also why if we had several disks for redo why we'd put an entire group on one disk rather than multiplexing all groups).

(There is probably a much better answer involving flashback, but I have to confess that I have only used explicitly created flash restore points so far, never run through a DR scenario).


The problem isn't the index.  The problem is that your query as written does not allow an index to be used.

Modify your query so that the date range is resolved to a pair of constants and the problem is solved:

WHERE datetime_field >= CURDATE()
  AND datetime_field < DATE_ADD(CURDATE(), INTERVAL 1 DAY)

This will use the index on the column.

As a rule, never use a column as an argument to a function in WHERE because this forces a table scan.


How to Start Express SQL Server instance in Single user mode?

According to the documentation, SQL Server Configuration Manager requires Local Administrator permissions since various SQL Server Configuration items are stored in the local system registry.

From the "SQL Server Properties (Startup Parameters Tab)" in the SQL Server Configuration Manager Help:

  Use of this page is restricted to users who can change the related entries in the registry. This includes the following users.

  Members of the local administrators group.
  The domain account that is used by SQL Server, if the Database Engine is configured to run under a domain account.

You can confirm membership in the local Administrators via one of the following methods:

Start a command-prompt on the machine where SQL Server is installed, and run this:

whoami /groups | find /i "BUILTIN\Administrators"

Assuming the machine is a member of your Active Directory Domain, you can run the "Active Directory Users and Computers" Microsoft Management Console, either from the Domain Controller for your domain, or from your workstation if you install the tool by downloading the Microsoft Remote Server Administration Tools.  Once you have "Active Directory Users and Computers" running, you need to find your user account, then check the "Member of" tab to see if you are a "Domain Administrator" or "Enterprise Administrator".
On the server where SQL Server is installed, you can check the "User Accounts" from "Control Panel".


How to persist selection of foreign key in related table

Currently you have something like this, as unusual as it may seem.

user {USR}
  PK {USR}

genre {GNR}
   PK {GNR}

book {BOK, GNR}
  PK {BOK}
  FK {GNR} REFERENCES genre {GNR}

preference {USR, BOK}
        PK {USR}
       FK1 {USR} REFERENCES user {USR}
       FK2 {BOK} REFERENCES book {BOK}

For the stated requirement:

user {USR}
  PK {USR}

genre {GNR}
   PK {GNR}

book {BOK, GNR}
  PK {BOK}
  SK {BOK, GNR}

  FK {GNR} REFERENCES genre {GNR}

pref_genre {USR, GNR}
        PK {USR}
        SK {USR, GNR}

       FK1 {USR} REFERENCES user  {USR}
       FK2 {GNR} REFERENCES genre {GNR}

pref_book {USR, BOK, GNR}
       PK {USR}

      FK1 {USR, GNR} REFERENCES pref_genre {USR, GNR}
      FK2 {BOK, GNR} REFERENCES book       {BOK, GNR}

And if you want to allow more than one preference for each user, just change keys on pref_:

pref_genre {USR, GNR}
        PK {USR, GNR}

       FK1 {USR} REFERENCES user  {USR}
       FK2 {GNR} REFERENCES genre {GNR}

pref_book {USR, BOK, GNR}
       PK {USR, BOK}

      FK1 {USR, GNR} REFERENCES pref_genre {USR, GNR}
      FK2 {BOK, GNR} REFERENCES book       {BOK, GNR}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key


WordPress MySQL writes during "Flush Tables With Read Lock" are rejected instead of being queued

It is a brave soul indeed that still uses an all-MyISAM database.

  Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock?

Look at the error message again

  [20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running
  with the --read-only option so it cannot execute this statement for query
  INSERT INTO wp_options...

WordPress is reporting this as the error, but it not a failure on the part of MySQL. You turned on read_only. That's what WordPress sees.

  And more importantly, how can I fix that so they DO get queued and are eventually run in the order they were requested? :)

Just remove read_only:

FLUSH TABLES WITH READ LOCK;
system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb
unlock tables;

The DB Connections should start to queue up all INSERTs, UPDATEs, and DELETEs.

You said something far more profound in the question

  Both reads AND writes need to be possible during the backup. But the backup time is very short, so it's fine for the writes to be queued until the "unlock tables" command.

SUGGESTION #1

You need to convert all your tables to InnoDB. Reads and Writes can continue and queue up just fine without FLUSH TABLES WITH READ LOCK;. You would dump the database like this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} thedb > thedb.sql

SUGGESTION #2

If you want everything to remain MyISAM, your should get another DB Server and setup MySQL Replication. All your writes would happen on the Master. On the Slave, you would do the backups like this

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb
START SLAVE;

You can also split your reads between Master and Slave, or do all reads on the Slave.

EPILOGUE

If you go with SUGGESTION #1, convert everything to InnoDB with this script

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYISAM_TO_INNODB_CONVERSION_SCRIPT=/tmp/ConvertMyISAMToInnoDB.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}
mysql ${MYSQL_CONN} < ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}

GIVE IT A TRY !!!

UPDATE 2014-11-21 10:52 EST

In your comment, you just asked

  Is there any major reason to NOT install a 2nd MySQL instance (the slave) on the same server?

There is a huge reason why you do not want to setup a 2nd MySQL instance on the same box to do replication.

Everything is MyISAM
MyISAM only caches indexes, never data
MyISAM defers caching data to the OS
Master and Slave caching the same data in two different DB means twice as much data for the OS to cache
See my other reasons in my old post Is it unwise to run replication on same physical server?

If you convert everything to InnoDB (which caches data and indexes, has failsafes for crash recovery and transactions), then, yes, you could setup a 2nd MySQL instance for replication on the same server (provided you have sufficient RAM and diskspace for the 2nd instance).


Why doesn't MySQL installation on an Ubuntu linux 16.04 OS require user to specify LD_LIBRARY

The expert @roaima says, "Because MySQL is a packaged application that stores its libraries in one of the "well known places".


MySQL 8 Geospatial Queries, MakeEnvelope Workaround?

There is clearly no work around for this issue.

MySQL can't do this operation (and many others) on a geographic srs.
You can't reproject in MySQL.

Oracle MySQL doesn't want to compete with Oracle Spatial. So you're SOL. MariaDB is currently behind MySQL in GIS too and it's a low-priority item for them. Either reproject to something Web Mercator and do Cartesian bounding box stuff, or PostgreSQL and PostGIS, which can do geographic bounding boxes, and on an index at that. And, also ST_Transform from one SRS to another.

PostGIS is the goto solution for GIS.


Hint: There is a column named "pma_timestamp" in table "table_a", but it cannot be referenced from this part of the query

You can't use a column if the target table in the values() clause. You will need to repeat the value you provided for that column:
insert into table_A
(id, pma_timestamp, monthend)
values
(103, '2017-08-15', last_day('2017-08-15'));

If you don't want to repeat the value, you could write an INSERT trigger that populates the monthend column. Then you wouldn't need to provide a value for it at all.

In a relational database, it's usually not a good idea to store data that can easily be derived from existing values. The monthend column is such a derived attribute. I would not store it at all, because calculating it from pma_timestamp can easily be done during a SELECT query. If you don't want to repeat the calculation, create a view that provides that information.


Query where condition take from the list of value

Something like this:

SELECT
    DISTINCT (
        SELECT ForeignCurrencyBal
        FROM [InsightWarehouse].[dbo].[v_Account] AS subA
            JOIN (
                SELECT TOP (1) dt
                FROM (
                    VALUES
                        ('2019-02-01')
                    ,   ('2019-01-31')
                    --,etc..
                )d(dt)
                WHERE d.dt < STMT.MIS_DATE
                ORDER BY d.dt DESC
            ) x ON x.dt = BusinessDate
        WHERE subA.AccountNum = STMT.ACCOUNT_NUMBER
    ) AS Balance,

    ACCOUNT_NUMBER,
    CASE WHEN STMT.CRF_TYPE='CREDIT' THEN STMT.AMOUNT_LCY
        ELSE NULL
    END as 'CreditAmount',
    CASE WHEN STMT.CRF_TYPE='DEBIT' THEN STMT.AMOUNT_LCY
        ELSE  NULL
    END as 'DebitAmount',
    STMT.BOOKING_DATE,
    STMT.VALUE_DATE,

    CRF_TYPE

FROM [InsightLanding].[dbo].[v_STMT_ENTRY] AS STMT
    LEFT JOIN [InsightWarehouse].[dbo].[v_Account] AS A ON a.AccountNum = STMT.ACCOUNT_NUMBER

WHERE STMT.MIS_DATE='2019-02-01'
    AND STMT.ACCOUNT_NUMBER='14498478'

UPD: Pay attention that your subquery has to return one row so perhaps you should use TOP (1) or some aggregation function to avoid possible errors.


MySQL, temporary on disk table on primary key retrieval?

OBSERVATION #1

You need to look at what the Query Optimizer sees and what it is interpreting

First, look carefully at the query

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

There is no WHERE clause. It is generating a temp table for an unbounded result set.

LIMIT has low precedence in the eyes of the Query Optimizer. Why ?

According to the MySQL Documentation on SELECT

  The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

Another section of that same page says the following:

  With one argument, the value specifies the number of rows to return
  from the beginning of the result set:

  SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

  In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.

Once you use the clause LIMIT, the SELECT must generate a result first. Your result has no boundary (no WHERE clause). The LIMIT is applied afterwards.

That explains the temp table creation.

OBSERVATION #2

You mentioned the following in your question

  When I replace object (varchar(12)) with description (mediumtext) temporary table is not created

According to the MySQL Documentation on the Temp Tables (your link)

  Some conditions prevent the use of an in-memory temporary table, in
  which case the server uses an on-disk table instead:

  Presence of a BLOB or TEXT column in the table
  Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes
  Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the
  SELECT list, if UNION or UNION ALL is used
  The SHOW COLUMNS and The DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an
  on-disk table.

The problem has a lot to do with the result set and the first bulletpoint (Presence of a BLOB or TEXT column in the table) Since TEXT field cannot occupy an in-memory temp table, it goes straight to disk. That's just the tip of the iceberg.

When you ran

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
limit 1

you were essentially read the entire row. How come ?

The common_descriptions is defined with the InnoDB Storage Engine

The primary key of an InnoDB table sits in a logical structure call the Clustered Index (internally it is called gen_clust_index, which can only be seen when running SHOW ENGINE INNOD STATUS\G)

That document says

  Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records

Since you accessed every column of the PRIMARY KEY (object_id, lang_code, object), internally you were essentially fetching every row (including description) which explains why a PRIMARY KEY retrieval still yielded a temp table on disk. When you changed the SELECT list (object_id, lang_code, description) things evident changed. a little. An in-memory temp table would not have been made. Why?

MySQL Documentation says this:

  When the server creates an internal temporary table (either in memory or on disk), it increments the Created_tmp_tables status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables status variable.

Thus, Created_tmp_tables would not have incremented because of the description being mediumtext. It went straight to disk. Created_tmp_disk_tables would have incremented instead.

SUMMARY

Many factors were involved

LIMIT used again a result set with no WHERE clause
InnoDB Clustered Index
description being MEDIUMTEXT

SUGGESTION

Try adding an ORDER BY

select SQL_NO_CACHE object_id, lang_code, object
from common_descriptions
order by object_id, lang_code, object
limit 1;

I hae seen it when done against a single column PRIMARY KEY.

Give it a Try !!!


How to know how full the MySQL redo log is?

The answer depends on which version of MySQL you are using.  Meanwhile, here are two 'old' answers:

innodb_log_file_size should be at least 10 times the size of the largest BLOB or TEXT being used.  (That has been relaxed recently some.)

Adjust innodb_log_file_size so the following is roughly 60.  This computes how often (in minutes) the log is rotated.  Once every 60 minutes is recommended, but if this gives you anywhere between 20 and 180, your setting is probably fine.
  Uptime / 60 * innodb_log_file_size / Innodb_os_log_written

The recovery time has improved recently.  So, again, upgrading may be useful.

Meanwhile, adjust according to the above guidelines.


Transaction Replication on Azure MI servers

can [Managed Instance Databases] both subscribe to Transactional Replication, and Publish, at the same time

Yes. Both publishing and subscribing are supported and you can do both, even in the same database, even on the same tables ( ie: Bi-Directional Transactional Replication).

Transactional and Snapshot replication are supported on Managed Instance.  And Managed Instance databases can both publish and subscribe, and a Managed Instance can serve as either a local or remote distributor.

Here are the main supported scenarios:

Publisher with local Distributor on a Managed Instance

Publisher with remote distributor on a Managed Instance

Publisher and distributor on-premises with a subscriber on a single, pooled, and instance database

Transactional replication with single, pooled, and instance databases in Azure SQL Database


This is a common trick of counting conditionally into several columns, depending on a condition using the IF() expression:

mysql> SELECT DATE_FORMAT(date,'%m-%d %H:00') AS Date,
              SUM(IF(type = 'client', 1, 0)) AS `Client Count`,
              SUM(IF(type = 'staff', 1, 0)) AS `Staff Count`,
FROM ticket_data
WHERE department = 'support'
GROUP BY DAY(`Date`), HOUR(`Date`)
ORDER BY `Date` ASC
LIMIT 10;


During compaction, a new .couch file is written. So what you would do, when you interrupt compaction is making the file useless. The only way to stop compaction (iirc) is to stop CouchDB. Not a good idea.

Generally, compaction is a background process. The database reads are not affected by it - also for heavy read! If you have heavy write processes, compaction may never end because writing and updating a doc is faster than compaction.

You could consider scheduling compaction to a time slot, where you know that the database is not on 100% capacity. You do that in local.ini like this:

; configuration for automatic compaction.
; see: http://docs.couchdb.org/en/latest/config/compaction.html
[compactions]
_default = [{db_fragmentation, "70%"}, {view_fragmentation, "60%"}, {from, "02:00"}, {to, "05:00"}]

This means, run automatic compaction only during 2 and 5 in the night and only, when the fragmentation of the database is greater than or equals 70% and the view fragmentation is greater than or equals 60%

Further info can be found here: http://docs.couchdb.org/en/latest/maintenance/compaction.html

If you have questions you are very welcome to join the dev@couchdb.apache.org mailinglist or join IRC at #couchdb-dev or #couchdb at irc.freenode.net


Welcome.

As per MySQL Documentation, your MySQL server does not have enough resources.

Also go through this

  The table_open_cache and max_connections system variables affect the
  maximum number of files the server keeps open. If you increase one or
  both of these values, you may run up against a limit imposed by your
  operating system on the per-process number of open file descriptors.


How should deletions be handled in the database?

Yeah, I would definitely go for the second option, but I would add one more field a date field.

So you add :

delete       boolean
delete_date  timestamp

It would let you give a time for the undelete action.

If time is less than an hour one can undelete.

To really delete the entry deleted just create a stored procedure that will clean every entry with delete set to true and time greater than one hour and put it as a cron tab that runs every 24hours

The hour is just an example.


Add previous column alias to calculate sum/total of last column

Aliases are only available in GROUP BY, ORDER BY or HAVING (aside from direct output).
Found this link as helpful : https://stackoverflow.com/questions/3932205/to-calculate-sum-two-alias-named-columns-in-sql?rq=1

Please check it.


enable replicaSet in mongoDB yaml conf file

Check your mongod.log file, there should be line what lists all configuration items from you /etc/mongod.conf

2017-08-12T12:44:23.999+0300 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { port: 27017 }, replication: { oplogSizeMB: 1024, replSetName: "rs0" }, storage: { dbPath: "/var/lib/mongodb", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongodb/mongod.log" } }

if you cannot see replication: { oplogSizeMB: 1024, replSetName: "rs0" } then it has NOT readed your current config file.

You can see what file it has readed when you look to start of that line config: "/etc/mongod.conf"

And remember, if your mongod is not running on standard 27017 port, you must adjust your commands to use the right port. Command rs.initiate() (without parameter), don't work if the port is something else than the default.

Update
If connecting to a specific port ensure you define it when using the client mongo --port 27117. For some reason the rs.initiate() command checks for a custom port based on the command line parameter and not what is defined in /etc/mongod.cof


Not to the current session, no.

You can use the dbms_application_info package to instrument the code so that tools running in other sessions can query v$session_longops and see how far along the process is.  This is the same tool that Oracle internally writes to during long-running operations so lots of front-ends like Enterprise Manager and Toad will already read from it.

Alternately, you could use autonomous transactions to write to a status table as your code runs and read from that status table in a different session.  I can't see why you'd want to, but you could theoretically also use the dbms_alert package to send alerts to other sessions.


Why does this implicit join get planned differently than an explicit join?

Isn't the implicit syntax just rewritten to be the same as the explicit syntax?

Not necessarily. You are building on slightly incorrect assumptions. Like I explained under the referenced question:

What does [FROM x, y] mean in Postgres?

Comma-separated items in the FROM list are almost, but not quite identical to explicit CROSS JOIN notation. Explicit joins bind stronger. The query planner has to handle both cases differently in certain cases.

Obviously, the planner is smart enough to be able to treat VALUES expression with a single row expression with a simplified plan. We see a more complex plan for more than one row in the VALUES expression:

EXPLAIN ANALYZE
SELECT *
FROM  (VALUES (1), (2)) t(x)
    , (VALUES (2), (3)) g(y);

 Nested Loop  (cost=0.00..0.11 rows=4 width=8) (actual time=0.059..0.064 rows=4 loops=1)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.004..0.004 rows=2 loops=1)
   ->  Materialize  (cost=0.00..0.04 rows=2 width=4) (actual time=0.025..0.026 rows=2 loops=2)
         ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)

Simplifying the query plan is easier for a VALUES expression separated by comma(s). When bound by explicit join(s), Postgres needs to consider join conditions before combining with other comma-separated FROM items. I expect the "subqery scan" we see for this case is a (completely harmless) side effect of the more complex code path for this case.


Importing MySQL tables from other database in live site with mysqldump can cause trouble?

If they are on the same server, I'd do the table 'copy/replicate' this way:

CREATE TABLE detinationDB.new_table like sourceDB.TblName;
INSERT INTO detinationDB.new_table SELECT * FROM sourceDB.TblName;
RENAME TABLE detinationDB.TblName TO detinationDB.DropMe, detinationDB.new_table TO detinationDB.TblName;
DROP TABLE detinationDB.DropMe;

This way, you avoid dropping the old table before making sure the new table is there.

This is good if you intend to keep booth DBs on the same server.


Looks like a right problem. Do you realy need "HR." ?

Try

create PROCEDURE TEST_1 AS BEGIN
CURRENT_USER
SELECT A.EMPLOYEE_ID AS "Employee ID",
         A.FIRST_NAME || ' ' || A.LAST_NAME AS "Name",
         B.DEPARTMENT_NAME AS "Department Name",
         A.EMAIL AS "Email Address",
         A.PHONE_NUMBER AS "Contact Number",
         C.STREET_ADDRESS || ', ' || C.CITY || ', ' ||
         NVL(C.STATE_PROVINCE, C.CITY) AS "Address"
    FROM EMPLOYEES A
    LEFT OUTER JOIN DEPARTMENTS B
      ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
    LEFT OUTER JOIN LOCATIONS C
      ON B.LOCATION_ID = C.LOCATION_ID;
END;

And have a look at GRANT (for example)

GRANT EXECUTE ON PROCEDURE <user>.test_1 TO <role>


Can I create and restore partial backups with mariabackup on MariaDB 10.1.23?

Yes, and you do it exactly in the same way, like you would do in Percona, substituting xtrabackup with mariabackup (or innobackupex with "mariabackup --innobackupex" on the command line)


During rollback, is lock escalation reversed

SQL Server does not de-escalate the lock.

I investigated using a "Numbers" table with 100,000 rows. Empirically, updating 5,000 rows produced a corresponding number of RID locks in sys.dm_tran_locks. Updating a further 10,000 rows caused escalation to a single table lock.  This was consistently reproducible. To minimise the objects involved the table was a heap without indexes.

I used Extended Event tracing to capture lock_acquired and lock_released events. For ease of analysis I used separate traces during the UPDATE and ROLLBACK phases.

Two sessions (SSMS windows) were used - one for the DML statements and one for the trace DDL. I could not use as single session as I wanted to stop and start traces while the transaction is open and this is not permitted. The isolation level throughout is READCOMITTED.

The result of select @@VERSION is

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (Intel X86)   Mar 26 2015 21:49:16   Copyright (c) Microsoft Corporation  Enterprise Edition on Windows NT 6.1  (Build 7601: Service Pack 1)

The steps taken were

Create and start the Acquire trace.

CREATE EVENT SESSION DuringUpdate
ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
ADD TARGET package0.asynchronous_file_target
    (SET filename = 'c:\temp\LockRollback\DuringUpdate.xel',
     metadatafile = 'c:\temp\LockRollback\DuringUpdate.xem')
WITH(EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
    );

ALTER EVENT SESSION DuringUpdate
ON SERVER
STATE = start;

Begin a transaction and update 5000 rows.

begin transaction;

update dbo.Numbers
set Number = Number
where Number between 1 and 5000;

Update a further 10000 rows.

update dbo.Numbers
set Number = Number
where Number between 5001 and 15000;

Stop the acquire trace.

ALTER EVENT SESSION DuringUpdate
ON SERVER
STATE = stop;

Verify there are locks by querying sys.dm_tran_locks (2 - DB, table).
Create and start the Release trace.

CREATE EVENT SESSION DuringRollback
ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
ADD TARGET package0.asynchronous_file_target
    (SET filename = 'c:\temp\LockRollback\DuringRollback.xel',
         metadatafile = 'c:\temp\LockRollback\DuringRollback.xem')
WITH(EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
    );

ALTER EVENT SESSION DuringRollback
ON SERVER
STATE = start;

Roll back the transaction.

rollback;

Stop the Release trace.

ALTER EVENT SESSION DuringRollback
ON SERVER
STATE = stop;

Tidy up

DROP EVENT SESSION DuringUpdate
ON SERVER;

DROP EVENT SESSION DuringRollback
ON SERVER;

If lock de-escalation occurred I would expect complete symmetry in the two trace files - every lock acquired during UPDATE, and the escalation, would have a corresponding release during ROLLBACK. What I observed:

SELECT
    COUNT(*) as DuringUpdate
FROM sys.fn_xe_file_target_read_file(
    'c:\temp\LockRollback\DuringUpdate*.xel',
    'c:\temp\LockRollback\DuringUpdate*.xem', null, null);

SELECT
    COUNT(*) as DuringRollback
FROM sys.fn_xe_file_target_read_file(
    'c:\temp\LockRollback\DuringRollback*.xel',
    'c:\temp\LockRollback\DuringRollback*.xem', null, null);

was 383,889 rows and 166 rows respectively. Examining the event XML for the rollback file there is only one event relating to RIDs, PAGEs or OBJECTS - the release of an X lock on the table. I conclude from this that locks were not de-escalated during rollback.

This echos the documentation for rolling back to a named savepoint:

  A ROLLBACK TRANSACTION statement specifying a savepoint_name releases any locks that are acquired beyond the savepoint, with the exception of escalations and conversions. These locks are not released, and they are not converted back to their previous lock mode.


i want to be sure that mysql does not send ALL data every time the databases synchronize

In that case, you are in luck.  MySQL native replication always, only, ever sends "incremental" updates.  In fact, it doesn't have a way to automatically send the whole thing.  To initially configure replication, you have to manually copy a snapshot of the data from master to slave.  After that, only the changes are transferred, in an ordered stream of change events referred to as the binary log, sometimes also called the "binlog."

  Replication is based on the master server keeping track of all changes to its databases (updates, deletes, and so on) in its binary log. The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically, SELECT statements are not recorded because they modify neither database structure nor content.

  Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave. The slave also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the master.

  http://dev.mysql.com/doc/refman/5.7/en/replication-implementation.html


Zapier & MS SQL - Use azure DB to increase security?

Azure connections are encrypted (SSL/TLS) by default when connecting to Azure SQL Database although it is vulnerable to man-in-the-middle attack.

You have many choices to increase security. You can use Transparent Data Encryption (TDE) as explained here. You can also use dynamic data masking, row-level security, Always Encrypted and Cell-level encryption.

To receive alerts over anomalous activities with your Azure SQL Database you can enable Threat Detection.

Finally, you can make use of Azure Active Directory authentication and multi-factor authentication to enhance security.


Order to restore system and user databases

my understanding is that the order is not important... as long as the agent is not running until all db's are restored that have jobs interacting with them.

Yes, there is a dependency, but that dependency only exists upon execution of the jobs.

I would be interested in feedback subsequent to the restore of the master db.

I assume also that you are moving an existing (SQL SERVER 2008 r2) instance...


What ongoing maintenance is needed for a MySQL environment?

It just works!

Seriously though, if you got a master with two replicas environment, you probably want to do occasional consistency checks with pt-table-checksum. Replication does not guarantee consistency (and writes can happen on replicas too).

Otherwise:

Verify your backups to make sure you can actually recover (most of our data recovery customers thought they have backups, but they were actually not working).
Query reviews - definitely worth learning how to do that so you can review them proactively.
Do check error logs - any corruption errors will show up there.

There are many other things you can do, but I try to live by the rule: don't fix what ain't broken.

Good luck!


Taken from the Microsoft Support site:

  To configure a static port for the specialized Dedicated Administrator Connection (DAC), you must update the registry key that corresponds to your instance. For example, the registry key may be the following:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

  Note The "X" in "MSSQL.X" is a number that indicates the directory where the instance is installed for SQL Server 2005 or the instance name for SQL Server 2008.

Although the article talks about SQL Server 2008, this works for all Windows-based versions from 2005 and above.  SQL Server on Linux uses a different mechanism, which I am not yet aware of.