one table for all blob with a one to one table for metadata which link to one or many tables?

Either of these models look reasonable. The NoticeMasterFile intersection table is not strictly needed since you can get the information through either model. The question is: How do you intend to use the NoticeMasterFile information? Perhaps this change would support some future plans that you have.

Of course, the problem is that looking at a data model does not really reveal the thinking behind the model.

Nonetheless, separating the blob seems like a very good idea to me. If nothing else it protects you when someone runs SELECT * on the FileMeta table.

As an aside and for what it is worth, if you use sp_tableoption to set 'large value types out of row'=1 the blobs will leave a 16-byte key that references the blob pages.  The blob will still be considered part of the table. The separation would allow you to query the other columns of FileMeta without the processing overhead of the blob data.  (This is the default setting.)

Questions: How many documents do you expect to accumulate in your database? How large is the average size? Et cetera?

If your document load will be relatively light, then this should be fine. But remember that every document is included in the backups that you take.

If you think it likely that you will eventually move into the Terabyte size range, you should do some further planning for how to store the documents.

Perhaps:

Consider creating a series of filegroups for the documents as the data grows. At the proper time, set the older filegroups to read only. This protects the data and gives you the option of backing them up less frequently.
Ask your business to reconsider the value of storing documents in the file system.

For what it is worth, we have done it both ways over the years and both ways have their issues. But more systems that were using embedded documents are now using a file share instead.


By default, as documented in MSDN, if no length is specified for varchar it will default to 30 when using CAST or CONVERT and will default to 1 when declared as a variable.
To demonstrate, try this :
DECLARE @WithLength varchar(3),@WithoutLength varchar;
SET @WithLength = '123';
SET @WithoutLength = '123';

SELECT @WithLength,@WithoutLength

This is very dangerous, as SQL Server quietly truncates the value, without even a warning and can lead to unexpected bugs.
However, coming to the topic in question, in the given scenario, with or without length does not make any difference between the two statements and I am unable to see the 2 trailing spaces that you are talking about. Try this:
SELECT CONVERT(varchar(8), GETDATE(), 112)
UNION ALL
SELECT DATALENGTH(CONVERT(varchar(8), GETDATE(), 112))
UNION ALL
SELECT CONVERT(varchar, GETDATE(), 112)
UNION ALL
SELECT DATALENGTH(CONVERT(varchar, GETDATE(), 112))

You will notice that the DATALENGTH() function returns 8 in both cases.
Raj


Is there an alternative to isolation level SERIALIZABLE?

Give this a try:

BEGIN;
SELECT * FROM products WHERE exported = 0 INTO OUTFILE "x/y/z" FOR UPDATE;
UPDATE products SET exported = 1 WHERE exported = 0;
COMMIT;

And I don't think it matters what the isolation mode is.

I am not sure of mixing INTO OUTPUT with FOR UPDATE; if it give you trouble, make it two SELECTs.

Note that this does obviate your program with the loop and write.  (And it will probably run faster.)

Another approach may be to ping-pong 2 tables.  One is receiving new rows; the other is being exported.  No need to have the exported flag.  More details:  http://mysql.rjweb.org/doc.php/staging_table (especially the section on flip-flop staging).


Upgrade mysql from 5.0 to 5.5 in master-master configuration leads to broken replication

Since all the data is MyISAM, you should expect table locks rather frequently since INSERT , UPDATE, and DELETE command issues a full table lock.

If you have a lot of tables that use auto increment keys, you should add the following

Server1

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 1

Server2

[mysqld]
auto_increment_increment = 5
auto_increment_offset = 2

This will prevent PRIMARY KEY collisions with autoincrement columns

I have other suggestions in my post I have been tasked with Mysql Master-Master replication?


Check the connection properties.
Click Option button when connecting to the Management Studio (EM).


How to create new column with value from other table

You will want to JOIN to the pages table a second time to get the parentURL.  The first time that you join to pages use the page_id, then the second join will use the parent_page_id:

SELECT
    m.menu_id,
    p1.id AS page_id,
    m.sort,
    p1.url,
    p2.url parentUrl
FROM menus m
LEFT JOIN pages p1
  ON m.page_id = p1.id
LEFT JOIN pages p2
  ON m.parent_page_id = p2.id
ORDER BY m.sort;

See SQL Fiddle with Demo.  This gives a result:

| MENU_ID | PAGE_ID | SORT |      URL | PARENTURL |
|---------|---------|------|----------|-----------|
|       1 |       1 |    0 | vehicles |    (null) |
|       1 |       2 |    1 |      car |  vehicles |
|       1 |       3 |    2 |  bicycle |  vehicles |
|       1 |       4 |    3 |      bus |  vehicles |


Availability Groups using Multi-Subnet Clustering: Preferred Owners for Roles and Possible Owners for AG Listener IPs

My question is this: should we change the default settings in the Failover Cluster Manager, specifically the Preferred Owners of the AG role and the Possible Owners of the AG listener IP resources?

NEVER check or uncheck the preferred or possible owners for AG resources. When using SQL Server 2016 the preferred owners list can be moved up and down to control preferred failover targets given the multiple failover targets change but do not check or uncheck any boxes, ever, with Availability Groups. Period.

If you check and uncheck things like this, you're AG isn't going to work properly. When I teach the Availability Group class, I always show how this works, why it works, and why we NEVER want to do this (spoiler alert: The AG will most likely fail, hard).

Let me explain this, though, to make it a little clearer.

Preferred and Possible Owners

These values are set by SQL Server based on the settings for the Availability Group. The cluster has its own copy of the metadata of how things should work and SQL Server also has a copy of how it believes things should work.

This is great most of the time, when no one changes anything and both SQL Server and the cluster continue to hum along.

However, SQL Server knows what it believes should and shouldn't be owners of the resources based on the AG settings and reflects this by calling cluster APIs to set the expected behavior. You'll see this reflected in the HADR_CLUSAPI_CALL wait type.

Three Async Replicas

Let's look at SQL Server with an AG setup so that there are three Async commit replicas. If we do this, our AG and role resource should look as such:

Remember, async commit replicas cannot have automatic failover. Thus, this makes sense; our current role owner is the only possible and preferred owner. Since SQL Server is setup so that the only way to fail over is by forcing it then we don't want any other replica to pick up and start running with this, thus enforcing this at the clustering level.

Two Synchronous Manual Failover, One Async - Demo

The results will be the same as the Three Async demo. Why?

This is the same explanation as the three async replicas, we have no automatic failover yet so we don't want anyone AUTOMATICALLY taking over. Thus, the settings are the same.

Two Asynchronous Automatic Failover, One Async - Demo

Here is where things change and get interesting! Since we're now introducing automatic failover - and that's one of the roles of WSFC (along with health checking, metadata distribution, etc) - we're going to want to have preferred and possible owners.

Other Responses

  We recently had a 5 minute outage when we replaced our CO-SQL01 server hardware ...

You've specifically removed the vote from CO and only had 1 other voter left then had an unexpected crash. Sounds like it was working as it should.

  To be honest we have had a number of unexpected issues with using Multi-Subnet Failover Cluster Availability Groups, and it seems like the default preferred role owners and possible resource owners may be incorrect or at least not optimal for our scenario.

I'm going to be brutally honest here. Availability Groups are not a silver bullet, fix everything HA & DR. It seems that the technology is being used, but not too much is known about how it works or why it does these things on both an AG level or a WSFC level, so I completely agree it may not be setup properly... however, it is working as it is setup - I can't fault the cluster for doing what it is supposed to be doing.

  currently are looking at using the new Distributed Availability Groups feature in SQL Server 2016 to split our Multi-Subnet AG into to two single subnet AGs (one for each datacenter) as a way to prevent these issues in the future.

If you're having this many issues with Availability Groups I would not foray into Distributed Availability Groups on your own. It may or may not fit your use cases - but if I were you, I'd bring in someone who helps architect these types of solutions and have your use cases ready. Otherwise, you're going to make another post like this.

  We also think that will allow us to upgrade the cluster OS with minimal downtime.

One of the use cases for Distributed Availability Groups is for extremely low downtime cross cluster migrations, you're correct. If you're using Windows Server 2012R2, you can do rolling cluster upgrades [NOT available in 2012, must be R2] without needing to do Distributed Availability Groups.

  And here are the default settings for the SQL-StackOverflow_AG IP resources

Yes, do not touch them :) Leave them as is.


Connect to sql server with windows authentication in different domains

When you login to a computer you can do so in the following manner:

login to the computer with a user that is stored on the computer (e.g. COMPUTER_1\USER_3)
login to the computer with a user that is stored in the active directory of the domain you are using (e.g. DOMAIN_1\USER_1)

When the login is complete you have verified the authenticity of your account (USER_n) against either the computer (COMPUTER_1) or against the Active Directory domain (DOMAIN_1).

Environment

Now let's say we have the following information based on your questions:

first domain user account USER_1
second domain user account USER_2
first computer user account USER_3
second computer user account USER_4
first computer COMPUTER_1
second computer COMPUTER_2
first server SERVER_1
second server SERVER_2
first domain DOMAIN_1
second domain DOMAIN_2

Next I am assuming the following:

USER_1, COMPUTER_1 and SERVER_1 are members of the DOMAIN_1
USER_2, COMPUTER_2 and SERVER_2 are members of the DOMAIN_2
USER_3 is a user account on COMPUTER_1
USER_4 is a user account on COMPUTER_2

Scenario 1:

You login to COMPUTER_1 using the account USER_3 (computer user account). When you try to connect to the SERVER_1 using Windows Authentication you are denied access.

Why?

The COMPUTER_1\USER_3 local Windows account cannot be added to the SQL Server as a login on SERVER_1. Only SERVER_1\USER_n accounts could be added as SQL Server Logins to SERVER_1 or DOMAIN_1\USER_n accounts. SQL Server is unable to find COMPUTER_1\USER_n accounts when creating Windows Authenticated SQL Serer Logins.

Scenario 2

You login to COMPUTER_1 using the account DOMAIN_1\USER_1 (Domain user account). When you try to connect to the SQL Server SERVER_1 using Windows Authentication you are granted access.

Why?

DOMAIN_1\USER_n accounts can be added as Windows Authenticated SQL Server Logins to the SQL Server instance running on SERVER_1 and then granted access to the SQL Server. SQL Server will determine that you (DOMAIN_1\USER_1) are a valid Domain user and will allow you to connect to the SQL Server (assuming you have previously assigned these permissions to the account on your SQL Server).

Scenario 3

You login to COMPUTER_1 using the account DOMAIN_1\USER_1 (Domain user account). When you try to connect to the SERVER_2 (remember server is in DOMAIN_2) using Windows Authentication you are most likely denied access.

Why?

DOMAIN_1\USER_n accounts cannot be added as SQL Server Logins to the SQL Server instance running on SERVER_2. Unless you have a trust between the domain DOMAIN_1 and DOMAIN_2. The SQL Server instance on SERVER_2 will be unable to verify that you (DOMAIN_1\USER_1) is a valid Domain login and will deny you access SQL Server.

Summary

When you login to a computer you have only verified your authenticity towards either the computer or to the domain the computer belongs to. Any other permissions (SQL Server access, Windows Server access) depend on the rights assigned to your account either in the domain or on the target system. If a system is out of scope of the DOMAIN_1 or SERVER_1, then you will be unable to access these servers with Windows Authenticated SQL Server logins.

If a user is trying to connect to a SQL Server in a non-domain environment then you will possibly be better off with dedicated Native SQL Server Logins instead of Windows Authenticated SQL Server Logins. When you want to connect to the SQL Server you will have access via the SQL Server login.

This is a very short basic summary of how authentication works.


UPDATE with multiple WHERE and SELECT condition - MySql

Your query (while removing PHP code and formatting) looks like

UPDATE ativos
SET ativo = ?,
    comentario = ?,
    data_aquisicao = ?,
    localizacao = ?,
    fabricante = ?,
    modelo = ?,
    imei = ?,
    numero_serie = ?,
    ativo_sap = ?,
    anexo_a = ?,
    evento = ?,
    data_evento = ?,
    SELECT id_colaborador
    FROM colaboradores
    WHERE nome = ?
    AND WHERE id_ativo = ?

It is syntactically incorrect. Use Multiple-table UPDATE syntax. Final query must look near

UPDATE ativos a
  JOIN colaboradores c
/* next line maybe wrong, question is not clear */
    ON a.id_ativo = c.id_colaborador
SET a.ativo = ?,
    a.comentario = ?,
    a.data_aquisicao = ?,
    a.localizacao = ?,
    a.fabricante = ?,
    a.modelo = ?,
    a.imei = ?,
    a.numero_serie = ?,
    a.ativo_sap = ?,
    a.anexo_a = ?,
    a.evento = ?,
    a.data_evento = ?
/* next line maybe wrong, question is not clear */
WHERE c.nome = ?


I'm answering from a SQL Server perspective; however, something similar should work in most environments.

/* SET-UP  */

CREATE TABLE #inv (id INT IDENTITY(1,1), refno varchar(20), inv_amt money);
INSERT INTO #inv (refno, inv_amt)
VALUES ('inv001', 100)
      ,('inv001', 30)
      ,('inv002', 150)
      ,('inv003', 50)
;

CREATE TABLE #pay (id INT IDENTITY(1,1), refno varchar(20), pay_amt money);
INSERT INTO #pay (refno, pay_amt)
VALUES ('inv001', 130)
      ,('inv002', 30)
      ,('inv002', 50)
      ,('inv002', 50)
      ,('inv002', 20)
      ,('inv003', 20)
;

/* ANSWER */

CREATE TABLE #inv_total (refno varchar(20) PRIMARY KEY, inv_amt money);
CREATE TABLE #pay_total (refno varchar(20) PRIMARY KEY, pay_amt money);

INSERT INTO #inv_total
SELECT refno, SUM(inv_amt) FROM #inv GROUP BY refno;

INSERT INTO #pay_total
SELECT refno, SUM(pay_amt) from #pay GROUP BY refno;

SELECT COALESCE(i.refno, p.refno) as refno
      ,COALESCE(i.inv_amt, 0) as inv_amt
      ,COALESCE(p.pay_amt, 0) as pay_amt
  FROM #inv_total i
         FULL OUTER JOIN #pay_total p ON (i.refno = p.refno)
 WHERE COALESCE(i.inv_amt, 0) <> COALESCE(p.pay_amt, 0)
;

Executed on SQL Server 2008R2, this does indeed only bring back inv003.

NOTE: Depending on what version of SQL you're working in, you may be able to use the queries that populate the two _total temporary tables as sub-queries in the final query.

If you do need the temp tables, setting refno as a primary key isn't required; however, for very large amounts of data, it might speed up the final query.

ALSO: In my test data, I assumed that the two payment rows with the same id value was a typo. However, it's not relevant to the solution as far as I can tell, so if it was deliberate, change the set-up to not make the id column an IDENTITY column, and to explicitly populate that column in the INSERT statements.


How to properly use moveChunk when chunks of certain range needs to be moved?

Until a more decent way is found, I used this script solution:

use config
sh.stopBalancer()
var lower=ObjectId("58B60F00e4b03547ad945a8a");
var upper=ObjectId("58BCA680e4b03547ad945a8a");
var query={shard: "rs0", ns: "db.use", "max._id":{$gte: lower}, "min._id": {$lte: upper}};
var cursor=db.chunks.find(query);

cursor.forEach(function(d) {
 print( "chunk: " + d.min._id );
 sh.moveChunk("feedback.usage", { "_id" : d.min._id }, "rs1");
});
sh.setBalancerState(true)


What are the differences between NoSQL and a traditional RDBMS?

NoSQL stands for "Not only SQL" and usually means that the database is not a relational database, which have been very popular the last decades.
The reason why NoSQL has been so popular the last few years is mainly because, when a relational database grows out of one server, it is no longer that easy to use.  In other words, they don't scale out very well in a distributed system. All of the big sites that you mentioned Google, Yahoo, Facebook and Amazon (I don't know much about Digg) have lots of data and store the data in distributed systems for several reasons. It could be that the data doesn't fit on one server, or there are requirements for high availability.
CAP Theorem
The properties of a distributed system can be described by the CAP Theorem. Of the three properties you can only have at most two:

Consistency
Availability
tolerance to network Partitioning

Amazon Dynamo uses Eventual Consistency to come close to get all three properties. The paper Dynamo: Amazon’s Highly Available Key-value Store is worth reading when learning about NoSQL databases and distributed systems. Amazon Dynamo has the A and P properties.
Google take a different approach with BigTable, that has the C and A properties.
Other NoSQL databases
As I wrote in the beginning there are many other kind of NoSQL databases, that are designed for different requirements. E.g. graph databases like Neo4j, document databases like CouchDB and multimodel / object databases like OrientDB.
Finally I would like to say that relational databases will remain popular. They are very flexible and maintainable. But they are not always the best choice.


How to create recurrent weekdays as columns in a pivot?

The most complex part of this is just building the calendar in that format. Pivoting and surrounding it with HTML is pretty easy. First, let's start with this, your employee table with leave dates. leave_type didn't seem relevant to the problem at hand.

CREATE TABLE dbo.EmpLeave
(
  EmployeeID int,
  leave_date date,
  leave_type_color char(6)
);

INSERT dbo.EmpLeave(EmployeeID,leave_date,leave_type_color)
  VALUES(1,'2018-01-02','7777cc'),(1,'2018-04-01','ffffac');

The procedure I came up with looks like this (and warning: it assumes @@DATEFIRST = 7):

CREATE PROCEDURE dbo.BuildLeaveHTMLTable
  @EmployeeID int,
  @Year smallint = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET @Year = COALESCE(@Year, DATEPART(YEAR, GETDATE()));
  DECLARE @FirstDay date = DATEADD(YEAR, @Year-1900, 0);

  ;WITH Numbers AS ( -- 366 possible days (leap year)
    SELECT n = 1 UNION ALL SELECT n + 1 FROM Numbers WHERE n <= 365
  ),
  Calendar AS ( -- a year's worth of dates and dateparts
    SELECT [Date] = d,
      MonthStart = DATEADD(DAY, 1-DAY(d),d),
      Y  = CONVERT(smallint, DATEPART(YEAR,   d)),
      M  = CONVERT(tinyint,  DATEPART(MONTH,  d)),
      D  = CONVERT(tinyint,  DATEPART(DAY,    d)),
      WY = CONVERT(tinyint,  DATEPART(WEEK,   d)),
      DW = CONVERT(tinyint,  DATEPART(WEEKDAY,d))
    FROM
    (
      SELECT d = CONVERT(date,DATEADD(DAY, n-1, @FirstDay)) FROM Numbers
    ) AS c WHERE YEAR(d) = @Year -- in case it's not a leap year
  ),
  BaseSlots AS ( -- base set of 37 ints
   -- month can be spread across 6 weeks, but no more than 2 days in 6th week
    SELECT TOP (37) slot = n FROM Numbers ORDER BY n
  ),
  Months AS ( -- base set of 12 ints
    SELECT TOP (12) m = slot FROM BaseSlots ORDER BY slot
  ),
  SlotAlignment AS ( -- align days of week to slot numbers
    -- this is the most cryptic part of this solution
    -- determines which set of 7 slots, and which slot
    -- exactly, a given date will appear under
    SELECT c.*, slot = DW+(c.WY+1-DATEPART(WEEK,c.MonthStart)-1)*7
      FROM Calendar AS c
      INNER JOIN Months AS m ON c.M = m.m
  ),
  SlotMatrix AS ( -- extrapolate actual dates to 37 x 12 matrix
    SELECT m.m, s.slot, sa.[Date]
      FROM BaseSlots AS s
      CROSS JOIN Months AS m
      LEFT OUTER JOIN SlotAlignment AS sa
      ON sa.m = m.m AND sa.slot = s.slot
  ),
  FinalHTML AS ( -- build some HTML!
    SELECT m = '',
      slot, cell = CASE WHEN slot = 1 THEN '
'
        + COALESCE(DATENAME(MONTH,DATEADD(MONTH, m-1, 0)),'')
        + '	' ELSE '' END + '' + COALESCE(RTRIM(DATEPART(DAY,[Date])), '&nbsp;')
        + '	' + CASE WHEN slot = 37 THEN '
' ELSE '' END
      FROM SlotMatrix AS q LEFT OUTER JOIN dbo.EmpLeave AS el
      ON q.Date = el.leave_date
      AND el.EmployeeID = @EmployeeID
  ) -- now turn it sideways
  SELECT m = '',
    [1]  = '
Month	S	',    [2]  = 'M	',
    [3]  = 'T	', [4]  = 'W	', [5]  = 'T	',
    [6]  = 'F	', [7]  = 'S	', [8]  = 'S	',
    [9]  = 'M	', [10] = 'T	', [11] = 'W	',
    [12] = 'T	', [13] = 'F	', [14] = 'S	',
    [15] = 'S	', [16] = 'M	', [17] = 'T	',
    [18] = 'W	', [19] = 'T	', [20] = 'F	',
    [21] = 'S	', [22] = 'S	', [23] = 'M	',
    [24] = 'T	', [25] = 'W	', [26] = 'T	',
    [27] = 'F	', [28] = 'S	', [29] = 'S	',
    [30] = 'M	', [31] = 'T	', [32] = 'W	',
    [33] = 'T	', [34] = 'F	', [35] = 'S	',
    [36] = 'S	', [37] = 'M	'
  UNION ALL
  (
    SELECT * FROM FinalHTML PIVOT (MAX(cell) FOR slot IN
    (
     [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],[11],[12],[13],[14],
     [15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],
     [29],[30],[31],[32],[33],[34],[35],[36],[37]
    )) AS p
  )
  ORDER BY m OPTION (MAXRECURSION 366);
END
GO

Results of this call:

EXEC dbo.BuildLeaveHTMLTable @EmployeeID = 1;

Look like this (I stopped at the 7th day column):

You'll have to add the
 /
 wrapper yourself, but here is what the output looks like when put in between those and saved as HTML (and of course you can further enhance it with CSS):

When leave falls on a weekend, the leave color trumps the weekend color, but that is easy to adjust. Change this:

  + COALESCE(' bgcolor=#' + RTRIM(el.leave_type_color),
      CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
      THEN ' bgcolor=#cccccc' ELSE '' END)

To this:

  + CASE WHEN DATEPART(WEEKDAY, [Date]) IN (1,7)
      THEN ' bgcolor=#cccccc' ELSE COALESCE(' bgcolor=#'
      + RTRIM(el.leave_type_color), '') END

To convert a color in decimal format (like 65280) to its RGB equivalent (00FF00), you have to do a bunch of manipulation. I would consider storing it as RGB hex in the first place, but I updated the solution here with something similar to this:

SELECT RIGHT(CONVERT(varchar(10),CONVERT(varbinary(8), 65280),1),6);


Why does SQL Server Backupset table goes back a few months, but Agent Logs go back further?

The Agent Log is limited by the number of entries, not by a date. The default is 1000 rows, with 100 rows per job. You can see this via Right-click SQL Server Agent in SSMS -> Properties -> History.

So, if you've had fewer than that number of events since January, the history will go back further. You can change that limit in the dialog noted above.

Many DBAs will configure a job to purge backup history on a rolling month or two month basis, sometimes coordinated with how long backups are kept on local storage.


mysql.log files is where some distros store the MySQL error log. It contains errors, warnings, notes and every time that MySQL has started and stopped.

mysql.log.3.gz is the third archived log, and if there is no mysql.log.4.gz, the latest before the currently being written now. So, it is probably not the latest (current) log.

If you had a UNIX console, the first thing I would do is:

zgrep ERROR mysql.log.3.gz | wc -l  # count how many errors are there

zgrep ERROR mysql.log.3.gz | tail   # show the latest errors

You will see that it contains timestamps, which will give you an idea of the time frame.

zgrep and zless (using the search function /) are good tools to search for specific patterns on compressed files. There are some equivalents on Windows.

Please note that depending what was happening at the time, you may find many useless errors (already corrected) or warnings that are more important than the errors ("table missing, ignoring it").


Grouping data checking against top values


To make it easier for future users to find a working answer:

It appears that one method to fix this problem is to create a symlink from the DB2 location to the Java Home you wish to use. Since I told DB2 to use my user when I installed it (this is for a development copy), the command I used to fix this problem was:

sudo ln -s /Library/Java/JavaVirtualMachines/1.6.0_29-b11-402.jdk/Contents/Home/ ~/sqllib/java/jdk64/jre


Stored procedure slows down after upgrade from SQL Server 2008 to SQL Server 2016

Make sure your statistics are getting updated. Also check your index fragmentation and your plan for maintaining indexes.

Since you are on SQL Server 2016, I think you should look into the Query Store feature. You turn it on by running

ALTER DATABASE YOURDATABASE SET  QUERY_STORE = ON;

Once this is turned on, the Query Store will keep the execution plans. There are several searches you can use, one being "Regressed Queries". Once you find your query, you can look at the different execution plans and force the query to use the more efficient plan with the GUI tools.

To force your stored procedure to recompile it's plan the next time it is executed, you can run

 EXEC sp_recompile 'YOURSTOREDPROCEDURE';

If you would like SQL Server to recompile the procedure plan each time it is executed, you can run

 EXECUTE YOURSTOREDPROCEDURE WITH RECOMPILE;

Here are some links for further reading and understanding.

The SQL Server 2016 Query Store: Overview and Architecture

Monitoring Performance By Using the Query Store

Recompile a Stored Procedure


How to add slots for Slot table such that there are no clash

BEGIN;
SELECT ... FOR UPDATE;
check for no overlaps (or whatever)
INSERT (or update) new entry
COMMIT;

In InnoDB, locking the whole table is equivalent to locking every row.  Do I need to explain that that is a lot more costly than locking a single row?

Your SELECT ... FOR UPDATE should pessimistically lock whatever rows you don't want mucked with until the end of the transaction.  Often it is a single row (lock one row ... update that row).  Or, it could involve locking several rows before deciding which row(s), if any, will actually be updated.

You should minimize the number of rows locked -- for speed of the SELECT, for speed of the ROLLBACK/COMMIT, and for minimizing interference with other queries.

Unless you have hundreds of UPDATEs happening per second, locking too many rows is not a big deal -- after all, your transaction should be designed to finished in only a few milliseconds, not the default `innodb_lock_wait_timeout=50 (seconds).


How to remove an element from a col with a CSV of ints in PostgreSQL 9.2?

Proof of concept for regular expressions:
SELECT trim(regexp_replace(',' || '3,4,5,13,14' || ',', ',(4|5)(?=,)', '', 'g'), ',');

Returns:
3,13,14

Pad commas to cover corner cases at begin and end. The regular expression ',(4|5)(?=,)' explained:
, ... literal comma
(4|5) ... two branches: either 4 or 5
(?=,) ... positive lookahead: next character is a literal comma
Would need a functional trigram index on (',' || allowed_types || ',') for good performance with big tables (only slightly bigger than a regular index due to the added commas). Details:

How is LIKE implemented?

Or, with a more sophisticated regular expression, you can work with the original column and a trigram index on just (allowed_types):
SELECT ltrim(regexp_replace('3,4,5,13,14', '((,|^)(4|5))(?=,|$)', '', 'g'), ',');

dbfiddle here
But I expect the first solution to be faster: complex regular expressions are more expensive.
The according UPDATE
UPDATE tbl
SET    allowed_types = trim(regexp_replace(',' || allowed_types || ',', ',(4|5)(?=,)', '', 'g'), ',')
WHERE  ',' || allowed_types || ',' ~ ',(4|5),';  -- here, plain comma is good

What I would really do:
Update to a current version of Postgres (pg 9.2 reaches EOL Sept 2017) and probably use a normalized 1:n design.


Why Jira can't connect database with pgBouncer?

This works:

<url>jdbc:postgresql://127.0.0.1:5432/jiradb</url>


It's a mix of C and D, depending on the free space in each of your tempdb files.

SQL Server uses a Proportional Fill algorithm to determine which data file should be used for any given write. Paul Randall discusses this in detail here.

Provided that your tempdb data files have roughly the same amount of free space, writes will be performed in a round robin fashion (one extent at a time).


Some variety of this, depending on RDBMS. SQL Server and MySQL accept date literals like this:

SELECT * FROM Employee
    WHERE BirthDate BETWEEN '1970-01-01' AND '1970-01-01'

SELECT * FROM Employee
    WHERE BirthDate BETWEEN '19700101' AND '19700101'


Installing SQL Server 2000 x86 Enterprise on Windows 2003 x64

Yes, SQL Server 2000 x86 can use AWE to buffer data pages above the 4GB limit. See How to configure SQL Server to use more than 2 GB of physical memory:

  sp_configure 'show advanced options', 1
  RECONFIGURE
  GO
  sp_configure 'awe enabled', 1
  RECONFIGURE
  GO
  sp_configure 'max server memory', ...
  RECONFIGURE
  GO

The article also explains how to enable /PAE in boot.ini, but if your OS is amd64 already you don't need it since the OS can already see the entire memory. So all you need is to enable AWE on the x86 SQL Server 2000 instance.


automatically change value on the table mysql

There are two ways to do this. The more traditional way is with a cron job on the server:

http://unixhelp.ed.ac.uk/CGI/man-cgi?crontab+5

Another way is to do it with MySQL events:

https://dev.mysql.com/doc/refman/5.6/en/events.html

I personally prefer to keep this kind of logic in the app via a cron job and keep mysql events dedicated to db maintenance related items (partition maintenance for instance).


Index for column with two possible value

The optimizer evaluates the possible gains of using indexes vs doing a full scan (filtering unwanted rows on the fly). As the ratio of filtered and total rows gets closer to 1 the benefit of using index decreases. The exact tipping point is dependent on the actual data, query, etc. so it's hard to say an exact number when it becomes useless. Generally speaking a 50/50 split is not sufficient for a B+TREE index to work efficiently and most cases full scan will be preferred over using indexes. (Unless it can be used for index-only scans)

The index is still getting updated regardless of its usefulness in queries.


You have to revoke the default privileges:
ALTER DEFAULT PRIVILEGES FOR ROLE master IN SCHEMA some_schema
   REVOKE ALL ON TYPES FROM xxx;


Postgresql: invalid input syntax for integer: "false"

The UNION result set data types will be determined by logic that favours values that are not string literals -- it is described in detail in the manual, also note that, without explicit type specification, string literals are treated as being of type unknown.

Specifically, these rules apply in your case:

  5. Choose the first non-unknown input type which is a preferred type in
  that category, if there is one.

  ...

  7. Convert all inputs to the selected type. Fail if there is not a conversion from a given input to the selected type.

This basically means that if you have at least one numeric literal value in a particular (in your case fourth) column in your UNIONised query, Postgres will attempt to coerce the values in the same column in other SELECTs into an number, which obviously fails for the character value '2017-01-01'.

Subsequently, if you have at least one character value in that column that cannot be cast into a number, you will have to use explicit character values for the fourth column in all SELECTs:

insert into configurations
(id, key, description, value)
select 1,'key1','D1', '25'
UNION
select 2,'key2','D2', '365'
UNION
select 3,'key3','D3','2017-01-01'

H/T to Andriy M for his comment that prompted me to read the manual more thoroughly.


A database trigger is an alternative for?

I don't expect the writer of the question meant it this way, but words mean things and the question specifies a "database trigger", therefore there is no correct answer.

  A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event. (PL/SQL Language Reference - DATABASE Triggers)

Unlike Row and Statement triggers, Database triggers fire for things like
AFTER STARTUP ON DATABASE, AFTER SERVERERROR ON DATABASE and AFTER LOGON ON DATABASE.


Does Mongo have disappearing/phantom writes

Just to answer my question, in case people come across it:

1) Yes, it is possible for A, and C to be committed and B to fail.  Atomicity only exists on documents in MongoDB.
2) Yes, this is possible as well.

See more for reading here:

http://docs.mongodb.org/manual/tutorial/isolate-sequence-of-operations/


How to generate globally unique ids for different tables of the same database?

Back in the day we had an ID table. Single column, single row with an int value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of course, a great source of concurrency errors.

Later, sequences were introduced. A single sequence used across the whole database would show the behaviour you describe. There's an example in the documentation that illustrates this:

CREATE TABLE Audit.ProcessEvents
(
    EventID int DEFAULT (NEXT VALUE FOR Audit.EventCounter), -- same sequence, different table
    <other columns>
);

CREATE TABLE Audit.ErrorEvents
(
    EventID int DEFAULT (NEXT VALUE FOR Audit.EventCounter), -- same sequence, different tables
    <other columns>
);

I've edited the example to highlight this usage.

An identical outcome could be achieved by generating the globally unique numbers in the application code, before they are passed to the database. Were I to implement this I imagine it would be as a static method of some utility class compiled into the executable (though other implementations would be possible). Say the application needs to write a customer's details to the database. As it is marshaling the customer name, address, telephone number etc. it also generates a new global ID. The ID is passed to the INSERT statement (or stored procedure) as just another parameter value.

Whether the ID values are produced by the application architectural tier or the database tier would depend on the specific design considerations. If the app can scale out coordination between instances becomes problematic. After an application re-start the code must figure out the next value to use. The DB server has these features, and others, written into it already.

What I would definitely not do is have the application call the database just for the next ID, then marshal that with the business data into an INSERT. That's too many round-trips to the database when only one is required.


pg_restore restore FK constraints with explicit --table argument

What you probably want is to restore all things that are not data. So try -s, from man pg_restore

-s
--schema-only
    Restore only the schema (data definitions), not data, to the extent
    that schema entries are present in the archive.

    This option is the inverse of --data-only. It is similar to, but
    for historical reasons not identical to, specifying
    --section=pre-data --section=post-data.

    (Do not confuse this with the --schema option, which uses the word
    “schema” in a different meaning.)

Note that schema is everything. Foreign key constraints mean you may not be able to load just the data for a table. From pg_dump

  when -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that the results of a specific-table dump can be successfully restored by themselves into a clean database.

So if you've got a clean database, and you try to restore with -t you're potentially attempting to insert data the conflicts keys and constraints specified in the schema -s.


How to ensure a trigger is fired after variable number of inserts?

You could implement all your inserts on those tables via stored procedures. That would allow you to execute your check_percentage process at the end of said stored procedure. It would also allow you to impose additional conditions on said execution.


Pros and Cons of virtualisation technology for database-server and datastorage

This is one of those "It depends" questions.

Performance depends on resources, contention, configuration, and the VM engine

Uncontended VM host: If you properly resource a VM with uncontended high performance locally-attached or SAN storage, low contention for CPU resources, no memory overcommit or contention, fast dedicated network access, etc, it'll generally perform very well on a properly tuned VM engine. Exact results will depend on the VM system used, on how you provide access to resources, and lots more.

You can get great results on high end VPS plans with guaranteed low contention ratios and good storage.

Contended/under-resourced VM host: If you put it on the same box as three other application servers and a file server, all of which share the same RAID 5 array and are fighting over RAM and CPU, it'll perform terribly.

If you put it on a cheap over-subscribed and overcomitted VPS host somewhere you'll get similarly poor results. If half your RAM is really swap on the host's disk, nothing is going to be fast.

"In the cloud": If you put it on EC2, Azure, or whatever, then performance will depend on the contention ratios for the service, the storage they're using, what other users are doing, how good their QoS is, and lots more.

At least for EC2 the disk subsystem has horrible performance (on standard VMs, at least in 2012) so it only performs OK if you have enough RAM to cache at least your indexes. Amazon has introduced new high I/O instances that might be better but I haven't seen benchmarks yet.

Usually, you'll get something in-between if you choose lightly contended hosting with decent disks, like high quality higher-end virtual private server hosts.

Direct vs VM guarantees

Re specific guarantees about things like file ordering, that depends on your VM setup. Are you using VMs backed by files? By raw block devices? By an iSCSI SAN? It also depends on how your VM engine is configured, and exactly which VM system you're using.

Best case

In the best case - a system with all-paravirt drivers, VT-x, VT-IO, uncontended access to host resources, etc - you'll probably get performance pretty close to the host. If you give the VM direct block devices not host files for storage then you'll get proper file ordering without host-side fragmentation. Exactly how close will depend on your particular hardware, host and guest, configuration, and more; benchmark it with your workload.

fsync() and write durability

One thing to watch out for with VMs is that you must make sure the disk system tells the truth about fsync(). A very easy way to make VMs a lot faster is to ignore fsync() requests. That's fine until the VM host crashes or loses power, at which point your databases are likely to be hopelessly corrupted. The VM host must either honour fsync() requests by respecting the guest OS's disk flush commands, or must offer non-volatile write cache that won't go away if power is lost. Some SANs use SSDs for that, most other systems use battery backed RAID controller cache memory. If your VM can process more than a few hundred transactions per second it's likely to either be ignoring fsync or on write-caching storage, and you should find out which before it eats your data.

But why?

(Updated): As noted by Chris Travers, why should you virtualise DB servers? Why not handle replication, heartbeat and failover at the DB server level, migrate via promotion of replicas, and get the full performance of the bare metal?

I wrote this original response with the mindset that a VM was a given, and the question was how to get the best results. The best virtualisation for a DB server is still, in my mind, no virtualisation. That said, I've only managed fairly small sites.


Adhoc queries memory consumption in SQL Server

Could you please run below query and post the result? This would provide the percentage of Adhoc Plan, Total cache plan and percentage of Adhoc:

SELECT AdHoc_Plan_MB, Total_Cache_MB,
        AdHoc_Plan_MB*100.0 / Total_Cache_MB AS 'AdHoc %'
FROM (
SELECT SUM(convert(bigint,(CASE
            WHEN objtype = 'adhoc'
            THEN size_in_bytes
            ELSE 0 END))) / 1048576.0 AdHoc_Plan_MB,
        SUM(convert(bigint, size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans)A

You may check below query also to get level of adhoc queries:

SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
       S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
       CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
       S.Total_MB_1_Use,   S.Total_MB,
       CAST( (S.Total_MB_1_Use   * 1.0 / S.Total_MB  ) as Decimal(18,2) )[Pct_MB_1_Use]
  FROM
  (
    SELECT CP.objtype[CacheType],
           COUNT(*)[Total_Plan],
           SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
           SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
           SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
           CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
           CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
                      / 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
           CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
           CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
                         ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
      FROM sys.dm_exec_cached_plans as CP
     GROUP BY CP.objtype
  ) AS S
 ORDER BY S.CacheType

You may also run below query to check query level details:

  SELECT  DISTINCT
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
ORDER BY MultipleQ.PlanCount DESC

Needless to say, do a bit more search on these topics and you will see tonnes of result.


A few things you can do:

Use enums or lookups keyed by integer values, or a simple "char" field, instead of varchar sort keys where possible. I'd use an enum  because you can control the sort order easily.

The only serious downside with an enum is that you can't currently drop values from an enum type. You can add them (including inserting them in the middle of the sort order) but not remove them. If that's a problem, you'll want to use a lookup table, or just a field declared "char" that has single character codes.

Also, if you don't need proper language collation, specify COLLATE "C" for character fields, e.g.

CREATE INDEX itemdesc_c ON supplier_management (item_description ASC COLLATE "C");

and then:

ORDER BY ...
    itemdesc COLLATE "C",
    ...

Important things to note:

Pg can combine indexes for predicates (WHERE clauses etc) but not sorting. You can't use a bitmap index scan for a sort. So it can use at most one of the candidate indexes, then it has to sort the rows within each group.
Low-selectivity indexes are a waste of time. If the values aren't widely distributed, don't index the column.
Pg's doing an on-disk sort. Throw more memory at the problem - try SET work_mem = '20MB' to start with. But see my comments below re thrashing with high max_connections. Use a connection pool.
Use a connection pool.
Indexes have a cost - they slow down insert/update/delete and increase vacuum work. So if the index isn't being used lots, get rid of it.
pg_catalog.pg_stat_user_indexes will help you tell which indexes are used.
pg_stat_statements (in contrib) and pg_stat_plans (the latter is an external module) are very useful for capturing data about query patterns, slow queries, etc.
Learn to love the auto_explain module.

Also, if you always do this sort, creating a composite index to match it will help.

CREATE INDEX bigindex ON supplier_management (
      item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC
);

... but be aware that it's only useful for this particular sort, and it'll be a big index so it's only worth having if you do this a lot. In fact, you might as well add supplier_management.buyer_purchase_order_id too, so it can do an index-only scan:

CREATE INDEX bigindex ON supplier_management (
      item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC,
      buyer_purchase_order_id
);


Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance.

It is important to understand that MySQL introduced new feature - Online DLL processing in version 5.6. It did not remove offline processing. So there is a need to differentiate between these 2 modes:

Some operations still work in Offline mode only. See Table 15.10, “Summary of Online Status for DDL Operations” for a list of the DDL operations that can or cannot be performed in-place.
Operations in Online and Offline modes have slightly different behavior, so you can choose "old" one for compatibility reasons.

Some examples (please suggest more):

InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns error.
ADD PRIMARY KEY clause in COPY mode silently converts NULL to default values for that data type (0 for INT,  empty string for varchar), whereas IN_PLACE does not do that.

  With the ALGORITHM=COPY clause, the operation succeeds despite the
  presence of NULL values in the primary key columns; the data is
  silently changed, which could cause problems.

Another reason to prefer COPY:

  Operations for which you specify ALGORITHM=COPY or old_alter_table=1,
  to force the table-copying behavior if needed for precise
  backward-compatibility in specialized scenarios.

Although MySQL manual doesn't talk about actual scenarios, you can imagine some. E.g. developer relied on table being locked during ALTER INDEX operation so table is read-only or fully locked and there is a process that reads static table during index rebuild.


Given with STOCK the same product could be multiple warehouses, the combined (productCode, warehouseCode) is the unique element for this table and that should be the primary key.


RMAN backups, incremental, not compressed: estimate size?

In short: Not really.

An RMAN backupset (e.g. BACKUP DATABASE) will backup up any blocks in your datafiles that have never been used.  This is different from empty blocks.  There's no way that I am aware of to differentiate between blocks which are currently empty and blocks which are unused.

From: http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmcncpt.htm#BRADV89481:

  "RMAN always skips blocks that have never been used"

If you were to do an image copy (e.g. BACKUP AS COPY DATABASE), then this is essentially a direct copy of the datafiles.  So if the datafile making up your system tablespace was called system.dbf, RMAN essentially copies this whole file.  This means your backup will be the same size as your datafiles.


You can try below alternatives:

If you installed SQL Server then you will be having "sa" - if mixed mode authentication is enabled. You can use "sa" account to create a login and map that login to a database.
Alternatively, you can use "DAC" (if you have enabled it as a part of post installation step).
By default, BUILTIN\Administrators has access to sql server 2008 instance. If you are part of BUILTIN\Administrators group then you will have access.
Last resort : Recover access to a SQL Server instance by Aaron Bertrand wherein he shows how to use PsExec to get into sql server.

Once you gain access to sql server, then create a login and map that login to a database.

Make sure to NOT delete it again :-)


Why does adding a computed column prevent predicate pushdown?

I'm just curious why the plan changes this way after I index the computed column

It's not indexing the computed column that really matters; any additional non-covering index can produce the same effect (so long as the table occupies more than one page). With only a clustered index present (or a fully covering nonclustered index), the optimizer can produce a TRIVIAL plan, with the filtering condition fully pushed down.

When there are non-trivial access method choices to make, the query goes through cost-based optimization. Cost-based optimization contains more complex and powerful index-matching rules, as you might expect, but the presence of multiple compute scalars due to computed column expansion and projection can prevent predicates (filters) being pushed down the execution plan, when the computed column is not PERSISTED.

SQL Server tries to push predicates (filters) as far down the logical query tree as it can early in the compilation process, but it can be quite conservative about doing this when non-persisted computed columns are involved. A filter that is not pushed down early is unlikely to be pushed further during cost-based optimization.

Simplified Demo

DROP TABLE IF EXISTS #CC;

CREATE TABLE #CC
(
    id integer IDENTITY PRIMARY KEY,
    c0 integer NULL,
    c1 integer NULL,
    c2 integer NULL,
    c3 AS c1 + c2
);

-- Just enough rows to fill more than one page
-- (so not all data access is trivial)
INSERT #CC
    (c0, c1, c2)
SELECT
    SV.number, SV.number, SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 324;

-- Trivial plan (only a clustered index to choose from)
SELECT * FROM #CC AS C WHERE C.c0 >= 1;

-- Add a non-covering index *not* on the computed column
CREATE INDEX ic2 ON #CC (c2);

-- Filter not pushed
SELECT * FROM #CC AS C WHERE C.c0 >= 1;

Workarounds

There are several ways to workaround this limitation in your example:

Do not project the total_actions column. If the column is not needed, computations to derive its value cannot get in the way of predicate pushing.
Make the computed column PERSISTED. This allows the optimizer consider plans that read the persisted value from the base table, avoiding the computation.
Specify the clustered index using a hint e.g. WITH (INDEX(1)). This allows a trivial plan, since it removes the question of access method choice.
Specify the index on the computed column using a (INDEX(ix_total_actions)) hint. While not covering (so no trivial plan), this hint also allows the column to come from storage rather than being computed. The index does not include last_action_date, so the predicate is applied in a Key Lookup. This plan may therefore be quite inefficient.
...etc.


How can I find a view column's base table column?

Starting with 12c, we have DBMS_UTILITY.EXPAND_SQL_TEXT.

You basically pass the query text through the first, input parameter, and receive the rewritten query back through the second, output parameter.

But since you are on 11.2, you can not use this yet. You can however collect the optimizer trace. If you already ran your query, and have its sql_id:

begin
  dbms_sqldiag.dump_trace
  (
    p_sql_id=>'1a2b3c4d5e6f',
    p_child_number=>0,
    p_component=>'optimizer',
    p_file_id=>'optimzer_trace'
  );
end;
/

Or if you do not know the sql_id and did not run the query before:

alter session set events 'trace[rdbms.sql_optimizer.*]';
-- run your query here, no need to wait for results, it just need to be hard parsed
alter session set events 'trace[rdbms.sql_optimizer.*] off';

These will generate a trace file that you can read and find the final, rewritten query in it.

To be honest, the output produced by all methods can be difficult to read once you have some complex queries, and the way to get them is not so trivial, so I usually end up reading and "parsing" the DDL statements manually...


Is it good practice to assign a system account for database owner?

I advocate having databases owned by a Windows account (login).

If using Mixed mode, sa could be used, but sa is a bit anonymous for my comfort.  Users owning databases (sometimes seen on dev database servers) doesn't work in production.

Like @nopol I prefer using Windows groups to reduce the number of logins under SQL Server, but groups can't own SQL Server databases.


How can I better optimize my tables and a LEFT JOIN query to find items that don't yet exist in the right table?

First, your query is correct. You don't need the wordlocation columns though (they are going to be all NULL anyway) so I'd change  select * to select ul.*:

SELECT ul.* FROM urllist AS ul
LEFT JOIN wordlocation AS wl ON ul.id = wl.urlid
WHERE wl.urlid IS NULL;

There are two more ways this kind of (anti-join or anti-semijoin) queries are usually written. Using NOT IN which is not recommended if the 2 joining columns are not both not nullable. Your urlid columns are not nullable indeed so this would work as well:

SELECT ul.* FROM urllist AS ul
WHERE ul.id NOT IN
      ( SELECT wl.urlid
        FROM wordlocation AS wl
      ) ;

Or using NOT EXISTS:

SELECT ul.* FROM urllist AS ul
WHERE NOT EXISTS
      ( SELECT 1
        FROM wordlocation AS wl
        WHERE ul.id = wl.urlid
      ) ;

All three queries result in very similar execution plans and efficiency in MySQL. So, you should test with your data distribution (and table sizes) and the version of MySQL you are using, to decide which query to use.

The query is of course slow now because of the obvious missing index on wordlocation (urlid). The efficiency will improve (dramatically) when you add this index but that's not all is missing from this table.

You have no Primary Key defined. Check and think about your modelling and find which columns uniquely identify rows in this table. My guess is that you are processing web pages, extracting words from them and storing all (or some of the) words and their locations in the webpages. If that's correct, you could use the (urlid, location) as the primary key:

ALTER TABLE wordlocation
  ADD PRIMARY KEY (urlid, location) ;

And if you do that, you won't need a separate index on (urlid). You will probably be having other queries though, that search for words (I guess you also have a wordlist table), so you will need an index on (wordid) as well - or on (wordid, location) or some other combinations. What indexes you'll need depends on what queries you plan to run against these tables.

For the urllist.url datatype, I'd prefer to use VARCHAR(x) instead of TEXT. What is the longest url you could have? The length x can be as long as 65535 and if you want to index it, the index can be for up to the first 255 characters. (I assume that you are storing the URLs there and not the actual web pages. If you do store web pages, disregard this paragraph.)


Automatic vacuum fails with `ERROR: XX000: cache lookup failed for type 0`

As your investigation uncovered, you are suffering from this PostGIS bug (bug number #4440).

Your options:

Update PostGIS to the latest minor release. The bug has been fixed in June 2019.
Workaround: Use GiST rather than SP-GiST indexes.


mysql inner join if value = true ( share system )

While I don't think anybody is going to be confident they know what you're asking based on the first revision of your "question," I hope the following statement will give you at least some form of direction with how to achieve your... goal, I guess.  Here is an SQL Fiddle in case you would like to see the query in action.  I can only assume you'll also be interested in learning about COALESCE, as illustrated in this SQL Fiddle.

SELECT  *
FROM    `UserPost` up
LEFT JOIN `User` u
    ON  up.shared_id = u.id
    AND up.share_in = 1
LEFT JOIN `Group` g
    ON  up.shared_id = g.id
WHERE   up.user_id = 1;


The database principal owns a schema in the database, and cannot be dropped message

Try the T-SQL to do this:

alter authorization
on schema::YourSchemaName
to dbo
go

drop user TheUserYouWantToDelete
go

You can't drop a principal that is a schema owner, so the ALTER AUTHORZATION changes the owned schema (I used YourSchemaName, but obviously substitute that with the owned schema in your database) to dbo (likewise, you can change ownership to whatever principal you need in your environment).  That will allow you to drop the previously-schema-owning user (for example purposes I used TheUserYouWantToDelete, but that'll be the now non-owner that you want to drop).


Do I need to use NVARCHAR rather than VARCHAR to properly store UTF8 characters in Oracle?

Good question. I'll give a simplified answer.

Oracle supports two character sets simultaneously, by way of different datatypes and parameters. A "normal" database-wide characterset and a "national" characterset.

Now, the "normal" characterset affects the way that VARCHAR2, CHAR and CLOB data is stored. The "national" characterset affects the way that NVARCHAR2, NCHAR and NCLOB data is stored.

These are dictated at database creation time. For example:

CREATE DATABASE ....
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
..... BLAH;

You can see these by checking in your DB. For example:

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL>

NLS_CHARACTERSET is for CHAR/VARCHAR2, NLS_NCHAR_CHARACTERSET is for NCHAR/NVARCHAR2.

If your normal characterset is UTF%, you're generally safe staying away from the N% datatypes unless you have some strange internationalisation requirements. If so, feel free to edit your question!


Around 50 % of the tables in a certain SQL Server database have either zero, one or two rows - is this a sign of a good design?

How many of the tables are not connected to any other table by a FOREIGN KEY relationship? You can do this by checking out the answer here.

It is possible to have tables with 0 records - if you ran a nuclear power plant for example, you'd want the table catastropic_failure to have 0 records.

Some reference tables could only have 1 or 2 reference codes - maybe the devs thought they'd be added to later?

I would say you're looking at a problem of orphaned tables - you've heard of orphaned records - records with no parents in parent-to-child relationship? These tables been lost and forgotten in the development process.

It was somebody's "bright idea" one day but was subsequently vetoed by senior dev/management - but such was the hothouse and/or firefighting atmosphere in the big open plan room where all the devs worked, that these "bright ideas" were never removed from the code base.

It's also possible that these tables are useful for 1 or 2 clients but not used by the bulk of the system users/clients/whatever?

Another possibility is that you should be careful that the coding style of your establishment (maybe prior to your arrival?) wasn't to use various tables as temporary storage - they are used briefly to store bits and pieces (important nonetheless) of data which aren't properly cleared down until they are used the next time?

What I would do:

1) try and search through your APPLICATION code and see if you can find tables that have no references and 0 records in the code - delete those - TAKE BACKUPS FIRST.

Be especially careful of triggers which may refer to those tables and also stored procedures which reference them as well.

Do you have a test suite for your code - I'm guessing not?

Then do the same with those tables which have 1 and then 2 records. You will probably find a few that can't be deleted without provoking obvious problems. Then there are those which will cause problems which will pop up days/weeks/months/years later. The answer here is it depends on what risks you want to take.

Find the dev and/or managers who've been on the project longest and ask them about this issue - that's probably your best best. By my "rule of thumb" reckoning, you should have, at most, say 10% of your tables in the form you describe. However, unfortunately, it's going to be a difficult mess to clear up.

Best of luck with your project and welcome to the forum! :-)


You've got a few different questions in here.

Q: Is it really a good practice to run Profiler on a production system?

Generally, you want to avoid doing it, but there can be situations where it's the most efficient way to solve a problem. For example, if you've got a SQL Server 2005 box, and every query comes in with OPTION RECOMPILE so it doesn't stick around in the plan cache, and you need to troubleshoot a particular sequence of queries, then Profiler can be your best bet.

You also mentioned dynamic queries - depending on how the dynamic queries are built, Profiler might indeed be the only way to capture the problem.

Some monitoring programs are even configured to automatically run a trace whenever the server comes under heavy load so that they can help diagnose the problem.

Q: If the server dies under load, how should we troubleshoot it?

You'll probably need to be more specific about what "dies" means. If it stops accepting any query requests, I'd start by checking the error logs, not by running Profiler.

Q: After running traces for 2 weeks, we haven't solved the problem. Are we using a good approach?

If you haven't solved it after two weeks, that would be no.


Why is the ROW keyword needed when constructing a row with one element, but not two?

As is often the case, the answer is to be found in documentation.
The UPDATE reference says, in particular:

According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. PostgreSQL only allows the source value to be a row constructor or a sub-SELECT.

whereas "row constructor" is defined like so:

A row constructor is an expression that builds a row value (also called a composite value) using values for its member fields. A row constructor consists of the key word ROW, a left parenthesis, zero or more expressions (separated by commas) for the row field values, and finally a right parenthesis. [...] The key word ROW is optional when there is more than one expression in the list.

The last sentence kind of makes sense, because a single expression in parentheses is ambiguous by itself and can be confused with a simple parenthesised expression.
This syntax was introduced in version 10 -- HT to ypercube™.


Can somebody explain BEGIN; and COMMIT; for me once and for all?

You are already using transactions, because every statement in PostgreSQL runs in a transaction. So no need to be afraid of transactions.

By default, PostgreSQL is running in autocommit mode, so every statement has its own transaction. To get a transaction that spans more than one statement, you explicitly start it with START TRANSACTION (or BEGIN).

Until a transaction is committed, its effects won't be visible outside the transaction itself. If you terminate the transaction with ROLLBACK, all its effects remain invisible. If you terminate the transaction with COMMIT, its effects suddenly become visible.

This works for normal data modifying transactions as well as for DDL statements. After all, DDL statements are mostly data modifications of the metadata tables (of course, there is more to it, like files being created or removed, which is deferred until the transaction commits).

If that seems too magical to you, dive into the depths of how it works under the hood. The documentation has little about that, but you can read the source, perhaps starting with src/backend/access/transam/README and src/include/access/htup_details.h. Blogs may be helpful too.

Start two sessions and play with it. Soon you will get the hang of this wonderful feature that makes a coder's life so much easier when applied sensibly.


I work at VoltDB. I wouldn't recommend setting the overall logging level to DEBUG except perhaps for a DEV environment for limited tests. It's hard to say what % the performance impact would be. Everything might be a bit slower, but there could be a lot more GC triggered which can have an unpredictable impact.

You can adjust the level for specific loggers, if you have an idea of the area of functionality where you want to see more detail. We have a howto guide on configuring logging here: https://github.com/VoltDB/voltdb/blob/master/examples/HOWTOs/ConfigureLogging.md


Migrating from text and image to varchar(max) and varbinary(max)

Just a note: these new data types support the same sizes as the deprecated types they replace, e.g. 2GB of data (which means a different number of characters depending on Unicode and other factors).

One thing for sure is you should parse all of your existing application code, stored procedures, functions etc. for instances of built-ins like UPDATETEXT, READTEXT, TEXTPTR, WRITETEXT, TEXTSIZE and @@TEXTSIZE - all of which will likely have to be changed. You can identify those stored in SQL Server this way:

SELECT s.name, o.name
  FROM sys.sql_modules AS m
  INNER JOIN sys.objects AS o
  ON m.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE UPPER(m.definition) LIKE N'%UPDATETEXT%'
     OR UPPER(m.definition) LIKE N'%WRITETEXT%'
     OR UPPER(m.definition) LIKE N'%READTEXT%'
     OR UPPER(m.definition) LIKE N'%TEXTPTR%'
     OR UPPER(m.definition) LIKE N'%TEXTSIZE%';

Note that this could yield false positives (e.g. those terms may be in a comment or naturally occur in an entity name) and it could miss some (e.g. the commands could be constructed using parameters / dynamic SQL). You're on your own for searching your application codebase and/or source control for instances of the same.

Also make sure you find all modules that accept or output parameters of these types:

SELECT DISTINCT s.name, o.name
  FROM sys.parameters AS p
  INNER JOIN sys.objects AS o
  ON p.[object_id] = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE system_type_id IN (34,35,99);

You might also want to consider that you may have logic in jobs and other maintenance routines that currently avoid these tables or treat them differently because of the limitations inherent in these data types. When you move to the newer types (and especially on the most modern versions of SQL Server), a lot of these limitations go away.

Finally, other than the syntax above, I can't think of a single feature the old types support that the new types don't.


How to get the top row value in insert of temporary table

If there are only a few possible columns, then you could do a separate check for each column name:

if (@dynamicCOLUMN = 'Column1')
begin
    insert into @Temp([dateTime],Reading)
    values (
    @startDate,
    (select top 1 Column1 from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))
end

if (@dynamicCOLUMN = 'Column2')
begin
    insert into @Temp([dateTime],Reading)
    values (
    @startDate,
    (select top 1 Column2 from tableABC where DateColumn >= @startDate and DateColumn < @tempdt  order by DateColumn desc))
end

If there are a lot of possible columns, then dynamic queries as suggested by McNets may be more suitable.


How to select the sum from three tables and group by date

With other servers you  can use common table expressions.  With MySQL you can use derived tables as in:

select name, year, month, sum(cnt) from
(select agent.name, year(date) as year, monthname(date) as month, count(*) as cnt
from
agent inner join software on (agent.id = software.agent_id) group by agent.name, year(date), monthname(date)
union (select agent.name, year(date) as year, monthname(date) as month, count(*) as cnt
from
agent inner join works on (agent.id = works.agent_id)
group by agent.name, year(date), monthname(date))) T1
group by name, year, month


By far the quickest and easiest way to print "all the prime numbers (1-100)" is to fully embrace the fact that prime numbers are a known, finite, and unchanging set of values ("known" and "finite" within a particular range, of course). At this small of a scale, why waste CPU each time to calculate a bunch of values that have been known for a very long time, and take up hardly any memory to store?

SELECT tmp.[Prime]
FROM   (VALUES (2), (3), (5), (7), (11), (13),
        (17), (19), (23), (29), (31), (37), (41),
        (43), (47), (53), (59), (61), (67), (71),
        (73), (79), (83), (89), (97)) tmp(Prime)

Of course, if you do need to calculate the prime numbers between 1 and 100, the following is fairly efficient:

;WITH base AS
(
    SELECT tmp.dummy, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [num]
    FROM   (VALUES (0), (0), (0), (0), (0), (0), (0)) tmp(dummy)
), nums AS
(
    SELECT  (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) * 2) + 1 AS [num]
    FROM        base b1
    CROSS JOIN  base b2
), divs AS
(
    SELECT  [num]
    FROM        base b3
    WHERE   b3.[num] > 4
    AND     b3.[num] % 2 <> 0
    AND     b3.[num] % 3 <> 0
)
SELECT  given.[num] AS [Prime]
FROM        (VALUES (2), (3)) given(num)
UNION ALL
SELECT  n.[num] AS [Prime]
FROM        nums n
WHERE   n.[num] % 3 <> 0
AND     NOT EXISTS (SELECT *
                    FROM divs d
                    WHERE d.[num] <> n.[num]
                    AND n.[num] % d.[num] = 0
                    );

This query only tests odd numbers as even numbers won't be prime anyway. It is also specific to the range of 1 - 100.

Now, if you need a dynamic range (similar to what is shown in the example code in the question), then the following is an adaptation of the query above that is still rather efficient (it calculated the range of 1 - 100,000 -- 9592 entries -- in just under 1 second):

DECLARE  @RangeStart INT = 1,
         @RangeEnd INT = 100000;
DECLARE  @HowMany INT = CEILING((@RangeEnd - @RangeStart + 1) / 2.0);

;WITH frst AS
(
    SELECT  tmp.thing1
    FROM        (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) tmp(thing1)
), scnd AS
(
    SELECT  0 AS [thing2]
    FROM        frst t1
    CROSS JOIN frst t2
    CROSS JOIN frst t3
), base AS
(
    SELECT  TOP( CONVERT( INT, CEILING(SQRT(@RangeEnd)) ) )
            ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [num]
    FROM        scnd s1
    CROSS JOIN  scnd s2
), nums AS
(
    SELECT  TOP (@HowMany)
            (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) * 2) +
                (@RangeStart - 1 - (@RangeStart%2)) AS [num]
    FROM        base b1
    CROSS JOIN  base b2
), divs AS
(
    SELECT  [num]
    FROM        base b3
    WHERE   b3.[num] > 4
    AND     b3.[num] % 2 <> 0
    AND     b3.[num] % 3 <> 0
)
SELECT  given.[num] AS [Prime]
FROM        (VALUES (2), (3)) given(num)
WHERE   given.[num] >= @RangeStart
UNION ALL
SELECT  n.[num] AS [Prime]
FROM        nums n
WHERE   n.[num] BETWEEN 5 AND @RangeEnd
AND     n.[num] % 3 <> 0
AND     NOT EXISTS (SELECT *
                    FROM divs d
                    WHERE d.[num] <> n.[num]
                    AND n.[num] % d.[num] = 0
                    );

My testing (using SET STATISTICS TIME, IO ON; ) shows that this query performs better than the other two answers given (so far):

RANGE: 1 - 100

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon      0                 0                   0
Dan        396                 0                   0
Martin     394                 0                   1

RANGE: 1 - 10,000

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon        0                   47                170
Dan        77015                 2547               2559
Martin       n/a

RANGE: 1 - 100,000

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon            0                 984                996
Dan        3,365,469             195,766            196,650
Martin           n/a

RANGE: 99,900 - 100,000

NOTE: In order to run this test I had to fix a bug in Dan's code -- @startnum was not factored into the query so it always started at 1. I replaced the Dividend.num <= @endnum line with Dividend.num BETWEEN @startnum AND @endnum.

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Solomon       0                   0                   1
Dan           0                 157                 158
Martin      n/a

RANGE: 1 - 100,000 (partial re-test)

After fixing Dan's query for the 99,900 - 100,000 test, I noticed that there were no more logical reads listed. So I retested this range with that fix still applied and found that the logical reads were again gone and the times were slightly better (and yes, the same number of rows were returned).

Query      Logical Reads       CPU Milliseconds    Elapsed Milliseconds
-------    ----------------    ----------------    -----------------

Dan                0             179,594            180,096


What kind of maintenance strategy do I need to put in place?

Frankly this is something I have not worked on but if I were in your place I would take some time before closing the system and devote that time to maintenance activity. Depending on fragmentation suppose you plan is to perform the maintenance task weekly. So on Friday or weekends(as you like it) before closing the system why not give couple of hour  to the SQL Server and within that hour perform maintenance task.

  My main concern is the log file: will it grow uncontrollably, or become 'fragmented' (or whatever the right term is).

Yes if database is in full recovery mode and NO proper transaction log backup is in place or no transaction log backup is taken at all the log file can grow huge and can grow greater than data file. In full recovery mode ONLY transaction log backup can truncate the logs and make them reusable. This is how circular nature of transaction Log works. The working of transaction log is quite nicely explained in Logging And Recovery In SQL Server.

Full recovery model is ONLY suggested when point in time recovery is required. If you don't need point in time recovery and you are OK with some data loss change the recovery model to simple. In simple recovery model log truncation happen automatically after checkpoint hapens or when log file grows 70% of its size, only exception is if long running transaction requires a portion of log file then it wont be truncated. So you can see in simple recovery log truncation is managed by database engine. You cannot take transaction log backup in simple recovery mode.


Make two different updates with two different where conditions

Do two UPDATEs.
And have a PRIMARY KEY
And two secondary indexes:
INDEX(recipient),
INDEX(writer)

If you are concerned about ACIT integrity, make a transaction:
BEGIN;
UPDATE ... WHERE writer...
UPDATE ... WHERE recipient...
COMMIT;


Is there a developer edition of Microsoft SQL Server 2017 for Linux?

2017, May:

SQL Server 2017 hasn't been released yet (only previews) so no licensing or pricing is available yet (the name v.Next should have been a clue!).

2017, Dec:

SQL Server 2017 for Linux was released in October and the Developer Edition is free for development purposes, as in previous versions.


Why can't I establish a connection to the local oracle database from within the pre-built oracle virtual machine?

This is the same story over and over again. Oracle uses the multitenant architecture in this VM and they set the environment variable TWO_TASK. This causes a lot of confusion.

When you connect as sqlplus hr, the value of TWO_TASK is automatically appended to the connection string, and you are connecting to SERVICE_NAME=ORCL and not SID=ORCL12 or SERVICE_NAME=ORCL12. The user hr was created in the ORCL pluggable database. With SID=ORCL12 or SERVICE_NAME=ORCL12, you connect to the root container, where this user does not exist, hence you receive the ORA-01017 error.

So instead of the connection string:

jdbc:oracle:thin:@localhost:1521:orcl12c

Use this for connecting:

jdbc:oracle:thin:@localhost:1521/orcl

Note the /orcl instead of :orcl12c.

: specificies an instance connection (SID), while / speficies a service connection (SERVICE_NAME). You have to specify the service name when connecting to a pluggable database, because multiple pluggable databases share the same instance, and the name of the service is what differentiates them.


How the input entered through the front-end form match with the existing data in database

Select diseases with the 3 symptoms:

select DISEASE_ID, count()
  from DiseaseSymptoms_T
 where SYMPTOM_ID in (1, 2, 3)
 group by DISEASE_ID
having count() = 3  -- number of symptoms in the where

That will only list typhoid, because it has all three symptoms

If you want all the diseases with the symptoms 1 and 2

select DISEASE_ID, count()
  from DiseaseSymptoms_T
 where SYMPTOM_ID in (1, 2)
 group by DISEASE_ID
having count() = 2  -- number of symptoms in the where

This last query will also show the disease typhoid, because it has the two symptoms in the where

If you want only the first 5 rows, the syntax depends on the engine. For SQL Server use SELECT TOP 5 DISEASE_ID... For MySQL add LIMIT 1 at the end of the query... For Informix use SELECT FIRST 5...


Insert data into oracle table with loop?

This is only judging from the very little information you provide:

SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter,
       ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER BY l.SEQ_NO)
FROM (
  SELECT h.*, SUBSTR(CAST(1000 + ROW_NUMBER() OVER (ORDER BY MOD_ID, SERIAL_NO) AS char(4)), 2, 3) AS Counter
  FROM "Header" h
  ) h
JOIN (
  SELECT MO_ID, SEQ_NO, QTY, CAST(NULL AS varchar(100)) AS LEVEL
  FROM "line detail"
  UNION
  SELECT DISTINCT MO_ID, SEQ_NO, NULL AS QTY, LEVEL
  FROM "Header"
  ) l ON (l.MO_ID = h.MO_ID)
ORDER BY h.MO_ID, h.SERIAL_NO, l.SEQ_NO


Finding most recent entry per group running very slowly

SELECT n1.Trigger_ASIN
     , n1.UniqueId
FROM notifications n1
JOIN ( SELECT n2.Trigger_ASIN
            , MAX(n2.Trigger_TimeOfOfferChange) Trigger_TimeOfOfferChange
       FROM notifications n2
       GROUP BY n2.Trigger_ASIN ) n3
WHERE n1.Trigger_ASIN = n3.Trigger_ASIN
  AND n1.Trigger_TimeOfOfferChange = n3.Trigger_TimeOfOfferChange
/* ORDER BY something -- obligatorily, limitation does not make sense without it!!! */
LIMIT 20;

And ensure the notifications__index_most_recent_entry index is used.


In a mirror, how can I keep the SQL Server Agent offline, but have it start automatically in the event of a failover on the Principal?

You can keep the Agent enabled but disable the individual jobs on ServerB and create a dedicated job on ServerB that continuously runs (or schedule it to run every X amount of time) and checks if the current server is the Primary. If it is, then enable the other jobs with sp_update_job.
You can also check which jobs are enabled with msdb.dbo.sysjobs so your dedicated job only tries to enable them once.
Using this same methodology you could have the dedicated job disable itself and have a secondary job check for when the current server becomes a Secondary Replica so that it can re-enable the dedicated job, or you can just let the dedicated job continually check but do nothing when the current server is the Primary and it already enabled all the jobs for the first time.


SQL Server Network Configuration node missing from Configuration Manager after install

Posting this up in case anyone else sees this and runs into the same perceived issue as I had. We have SQL Server 2016 SP1 x64 installed and SSMS 2017 installed. When I open SQL Server 2017 Configuration manager, I can't see all of the items (just as you couldn't). However, when I open SQL Server 2016 Configuration Manager I can see all of the items as expected.

Some more details are here:
http://internationaldatascience.com/sql-server-2016-configuration-manager-missing-ssms-17-2-cannot-connect-to-wmi-provider/

Here's the relevant info incase that blog goes down:

  However, “SQL Server Network Configuration” was missing! The “SQL
  Server Network Configuration (32bit)” section was there, but blank as
  usual. Searching the web wasn’t turning up much, so I started poking
  around on the local machine to find the old tool. Looking on a machine
  that had 2016 but not the problem, I could see that the command in the
  Shortcut on the Start Menu for SQL Server Configuration Manager is
  actually a call to open a Microsoft Management Console (MMC):

  C:\Windows\SysWOW64\mmc.exe /32

  C:\Windows\SysWOW64\SQLServerManager13.msc I copied the “SQL Server
  2016 Configuration Manager” shortcut file in the folder

  “C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL
  Server 2016\Configuration Tools” from the donor machine to the one I
  was having the problem with.


MySQL - Optimal indexing for a lookup table. HASH index, BTREE index or composite PK?

a: Satisfy the WHERE with INDEX(something, time)
MyLookup -- If the pair (FKToTableA, FKToTableB) is unique, then make that the PRIMARY KEY, and put the columns in that order so that your SELECT can quickly get into MyLookup.
Don't use BIGINT (8 bytes) unless you expect to exceed 4 billion, the limit for INT UNSIGNED, which takes only 4 bytes.
IP addresses -- for the old IPv4, there are convenient routines for converting to INT UNSIGNED.  For the new IPv6, it won't fit into BIGINT.  See 5.6.3.
Do those Metadata columns need utf8?  Can they be combined into a TEXT column?  And other questions.
Build and maintain a "Summary table" rather than scanning large chunks of the "Fact" table to get this "report".
Do the SUMs before JOINing to b.
Do you really need a many-to-many mapping table?  Seems like this is 1:many.
More tips on many-to-many.


Removing chat history for a particular user from database

A simple way to do this would be to create a new field in the Messages table called MESSAGE_VISIBLE_TO. This field should be of the same type as the USER_ID field and have a default NULL value. When User1 hits the "Clear History" button, the application would need some logic to update all of the messages between the two people, setting the MESSAGE_VISIBLE_TO to the ID of User2. Something like (passing in USER_ID for both User1 and User2):

UPDATE Messages
SET MESSAGE_VISIBLE_TO = @UserID2
WHERE MESSAGE_SENDER_ID = @UserID1 AND MESSAGE_SENDER_ID = @UserID2

The MESSAGE_VISIBLE_TO field could have 4 possible "states":

NULL = the default, both users can see the message
-1 = neither user can see the message
UserID1 = User1 ONLY can see the message
UserID2 = User2 ONLY can see the message

The application would need to incorporate the logic to UPDATE and SELECT the messages either at the database level (probably preferable as you wouldn't need to send more data than required) or at the application level (simply hiding the messages that the user is not supposed to see).

If you wish to eventually upgrade the application to support more than 2 people chatting, your approach would need to be different. You would need to create another table in between the Users and Messages table. You could name the table something like "MessageStatus", including the following fields:

USER_ID
MESSAGE_ID
VISIBLE (bit)

The table would then identify which users can see which messages by the value of the VISIBLE bit field.


How to set uuid-ossp as a default extension for whole server?

The CREATE DATABASE command always clones some existing template database.
If this parameter is not specified, postgresql uses the template1 database as the default. Thus, any prior changes in template1 database will be part of the new database (unless another TEMPLATE  has been specified explicitly).
You need connect to template1 database and create the uuid-ossp extension in this database.


Maintenance plan in AlwaysOn environment

My question is ,Do I need to create in only Primary Replica or all the secondaries also ?

You MUST run dbcc checkdb on all the replicas at least weekly. If you can afford it daily or once in two days that would be even better. Yes you can run checkdb on all replicas because checkdb internally creates snapshot and runs integrity check on that snapshot without affecting database. For more details read Checkdb from every angle
You cannot run index maintenance on read only replicas. You have to do it on primary replica. And since the logs generated from primary replica is replayed on secondary the index maintenance changes would eventually be done on secondary replicas.Please read Recommendations for Index Maintenance with AlwaysOn Availability Groups.

For statistic,s if you are running query on secondary replica which is different from primary replica and the query on secondary requires column statistics which is not there on primary you have option to create temporary statistics. Please read

AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica
AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

I am sure you are aware about Ola Hallengren script for index maintenance, I suggest you download it and use it. It takes care of various things on AOAG database.


SQL Server 2014 accounts for Daylight Saving Time now?

Is daylight saving automatically accounted for now is SQL Server, at least starting from 2014?

No, SQL Server 2014 is not "DST-aware", nor is your test showing it to be.

First, your 4th and 5th tests -- SWITCHOFFSET( SYSDATETIMEOFFSET(), '+05:00') and TODATETIMEOFFSET( SYSDATETIME(), '+05:00') respectively -- are showing +05:00 because that is exactly what you told them to do.

Next, SYSDATETIMEOFFSET() is showing -04:00 because that offset is currently the true difference between your server's TimeZone (which does change, else it wouldn't show the correct time for approximately half of the year) and UTC, which doesn't change during the year. This, however, does indicate DST-awareness: it is the OS that is DST-aware, which is how the server's current time changes based on DST.

Being DST-aware means that the DST date boundaries for prior periods and for other TimeZones can be taken into account when switching TimeZones. And this requires knowledge of TimeZones. However, SQL Server (at least prior to SQL Server 2016) has no concept of TimeZones. Offsets are just that: a specific number of hours and minutes away from UTC. In contrast, a TimeZone is a rule that states a particular offset and if Daylight Saving is ever applied (some TimeZones do not participate in DST) and if DST is applied, what those date boundaries are (which are a matter of law and change over time, per each region).

Hence, being DST-aware means you have the ability to convert a date in the past to, or from, your current TimeZone, or any date between any two TimeZones, neither of which are the server's current TimeZone. Meaning, SQL Server being DST-aware would indicate that you could convert from:

UTC to Eastern Time (your server's current TimeZone), for any date in the year, for 20 years ago, and get the correct time, even though the DST date boundaries changed in the US during that time. But you can't since you can only calculate the current offset, which is -04:00 and have no way to determine when that offset would be -05:00.
Any non-Eastern TimeZone to any other non-Eastern TimeZone, for any date, and get the correct time. This is not possible because there is no chart of TimeZones that details which TimeZone is what offset for which dates, including historical shifts in those date boundaries, so that any particular date, of any particular year, will be adjusted correctly.

More info and some examples

As mentioned above, time zone offsets are not time zones: they are merely a property of a time zone. Meaning, the "US/Eastern" (a.k.a. "America/New_York") time zone (i.e. EST / EDT) is not identified as -04:00 any more than as -05:00; it is an area that has boundaries to the East and to the West, has a base offset of -05:00, and participates in Daylight Saving and so has an offset -- +01:00 -- and a specific start date and end date for when to apply that DST offset. You can check the info on TimeZoneDB.com.

The main database of this Time Zone info is maintained by the Internet Assigned Names Authority (IANA), and can be found at:

http://www.iana.org/time-zones

Time zone information is determined by individual governments. Some countries participate in DST, other do not. Some cities within various countries / states opt in or out of the parent regions rule. See the page for Daylight Saving Time Around the World 2016 on TimeAndDate.com for a chart the various start and end dates around the world (at least for 2016). And check out their page for Eastern Time (ET) for details including exceptions.

Also, whether or not a particular area observes DST, and if so, what the data boundaries are, changes over time. For example, in the United States, the DST start and end dates changed in late 2005, effective starting in 2007: US Daylight Saving Time Changed in 2005. Meaning, even if you know that the current rule is to apply the +01:00 offset to EST between March 13th and November 6th, then if you convert a DATETIME value of 2006-11-02 18:00:00 from UTC to ET, you would get 2006-11-02 14:00:00 which is wrong, because in 2006 DST ended on October 28th, making the correct Eastern Time be 2006-11-02 13:00:00.

Additional explanation in my DBA.StackExchange answer here: How to check the timezone for a given datetime?

How to handle DST (and converting between time zones) in SQL Server 2005 - 2014

The two main ways I know of to properly handle DST-awareness as well as convert between different time zones are:

Matt Johnson's SQL Server Time Zone Support project:

This is a pure T-SQL approach. Please see the ReadMe at the bottom of the main page linked above (i.e. the project name). Pay close attention to the ConvertZone and SwitchZone functions, as they convert between actual time zones, not mere offsets!

Please also note that it states:

  Microsoft SQL Server 2008 R2 and higher are supported, including Azure SQL Database

SQLCLR:

Converting back and forth, between the server's local time zone and UTC is fairly simple using DateTime.ToLocalTime and DateTime.ToUniversalTime. This would work starting with SQL Server 2005.

Stay away from the TimeZoneclass that has methods of the same names, due to the following warning shown on the TimeZone.ToLocalTime page:

  The ToLocalTime method recognizes only the current daylight saving time adjustment rule for the local time zone. As a result, it is guaranteed to accurately return the local time corresponding to a particular UTC time only during the period in which the latest adjustment rule is in effect. It may return inaccurate results if time is a historic date and time value that was subject to a previous adjustment rule.

Converting between time zones is a bit trickier. There is a built-in .NET class that handles this, BUT it requires the calling Assembly to be marked as UNSAFE do to the TimeZoneInfo class having a Host Protection Attribute of MayLeakOnAbort (i.e. memory leak). People have used this class, and it might be fine when not calling its methods terribly frequently. But I did see at least one case of someone reporting what appeared to be a memory-leak when running this across many (hundreds of thousands or millions) of rows.

Regarding the safe ToLocalTime and ToUniversalTime methods in the DateTime class: precompiled versions of these are available in the SQL# SQLCLR library that I created, but only in the Full version (not in the Free version). To show that these 2 methods do work, consider the situation mentioned above regarding the date boundaries change that occurred in the United States, starting in 2007:

SELECT SQL#.Date_ToLocalTime('2007-10-31 20:23:45') AS [CurrentRule],
       SQL#.Date_ToLocalTime('2006-10-31 20:23:45') AS [PriorRule];

Returns:

CurrentRule                PriorRule
2007-10-31 16:23:45.000    2006-10-31 15:23:45.000


[closed] Reinstalled MySQL, removed old DB's. starting over. [closed]


SQL Server license from AWS Marketplace with ? cores

The market place should sell you the number of core licenses you need based on the instance size you select at purchase time.

The price will be based on a per hour or per year rate that you choose at check out. That price is just for the software license, you are still required to pay for the ec2 instance run costs as well as the EBS volume(s) attached to the instance and the data transfer costs.

Example:AWS Marketplace

In the screenshot below the selected instance of an m4.xl for a year the sql server licenses would cost $1607 however you would still need to pay the run cost for the m4.xl at $0.864 an hour plus $0.1 per GB of provisioned EBS storage.

If you were to assume as a simple example, you purchased the licenses for a year and ran the m4.xl on demand with an hourly rate of $0.864 and needed 100GB of EBS for the volume the cost would look:  1607 + ((720 hours per month * 12) * 0.864)+ ((0.1* 100GB) * 12 months) = $9,191.96 + (data transfer costs?)


Why SQL server unload CLR assembly every midnight

SQL server 32 bit instance's virtual memory is limited to 4GB. If you use SQL CLR extensively with a SQL Server 32 bit instance and experience one or more of above errors, Microsoft recommends that you migrate to a 64 bit SQL Server. This will enable SQL CLR to access more virtual memory and may prevent the above errors from happening in your environment.

For futher information check out the following page: http://support.microsoft.com/kb/969962


Are there any reasons not enable SQLCMD-mode as default in SSMS?

Yes, I don't want to work in SQLCMD mode all the time
It suits you but not me

Irritating:

  Enabling SQLCMD mode turns off IntelliSense and the Transact-SQL debugger in the Database Engine Query Editor.

Dangerous?

  When executed from the command line, the sqlcmd utility permits full interaction with the operating system. When you use the Query Editor in SQLCMD Mode, you must be careful not to execute interactive statements. The Query Editor cannot respond to operating system prompts.

And some limitations of course...

  So no flow control or TSQL variable assignments with SQLCMD mode? This is a big disappointment. Not very many SQLCMD keywords are supported and no real flow control commands


Production server Log file full due to replication

Per the error message, it is holding the data in the log because your database is configured to replicate those changes (via transactional replication) to another database, and for some reason the log reader job hasn't run recently or is experiencing an error.

If you don't know anything about replication, don't touch it, and talk to whoever in your company set this up.

If that person is supposed to be you, I can't teach a whole replication course in a SE comment, but in general your solution will fall into one of the following categories:

Fix replication (and let it run). Enable and run the "log reader" replication job, this should be set up to run continuously. This may take a while to clear while it works through all the records, but is the "correct" method. Hopefully the only problem is that the log reader job needs restarted; if it doesn't work or errors out, you have a bigger problem.
Re-initialize replication (this will clear the log immediately, but you will need to push a full snapshot to get replication working again). This is a quick fix to get your publisher database working again, but will leave your subscriber databases orphaned (static) until you push a new full snapshot. This might break your reporting server or wherever you are replicating to, and pushing a new snapshot could take a very long time, depending on the size of your database. Don't do this if you don't know what you're doing, or if you don't understand the business impact (and get approval from the stakeholders in the business).
Remove replication. If you are positive that replication shouldn't be set up on this box, then remove replication altogether and the log will clear. Again, don't do this if you don't know what you're doing, or if you don't know what server you are replicating to, or if you don't understand the business impact (and get approval from the stakeholders in the business).