Store categories in database or use hard coded integers?

Performance is an excellent thing to consider but I find it helpful to worry about other costs like data quality. Referential integrity ensures that no values would be assigned that don't have a match in the list of values table. Everyone knows 1 is mailing, 2 is billing, piece of pie. FNG comes on board and makes a code change and oops, we now have addresses with an address type of 12. And we've had that problem for 2 months now, customers are angry that they have not received their product and they're filling complaints with the BBB. RI would have caused the app to blow up when they tried to push those bad values into the database.

"Nobody's going to make that mistake here." Fine, the app is a success and brings in lots of money. Eventually someone's going to want to know more about what's going on. We want reports and yes, we also said we need all these new features done by last week. So now everywhere where you've embedded that knowledge that 1 is billing and 2 is mailing you need to repeat in the reporting thing (Excel, traditional reporting systems, ad-hoc queries, etc) you give to the end users to make them happy. Now you've got that logic in two places so any changes to that logic has to be propagated across two systems.

Each time you carry logic like that across systems just adds to your technical debt. Maybe your system is never going to grow like that and it's fine to embed the logic in the app code but I've found the quickest way to achieve immortality is to implement a quick fix.

Oh and did you notice I switched up the values in the report example? The FNG who you brought in could make a rookie mistake like that and who knows how long it's going to take to catch the mistake.


What is the relationship between the SSIS Package Store and Catalog?

There are two choices where to store SSIS Packages: the file system or the database. Within each of those, there are two variants.

The File System supports anywhere you want to put packages. The Package Store is just a well known location of %Program Files%\Microsoft SQL Server\NNN\DTS where NNN matches your version. The challenge with the Package Store is that for multi-instanced servers, everything is mixed together. SSMS, I would specify I want to connect to Server\DEV or Server\TEST. The Integration Services connector though, I'm just connecting to Server. You can create subfolders to attempt to isolate packages for environments but it's clunky. Not as clunky as the other approach of editing/swapping in different XML files to redirect where the Integration Services process defines the Package Store...

The Database only option pre-2012 was going to be tables in the msdb (and all the dba's rejoiced at the added backup bloat and testing of restores for msdb too). Depending on 2005 vs 2008 will the table will be called dbo.sysdtspackages90 or dbo.sysssispackage and they may also live in folders. Handy SSIS Package query

2012+ forward, we have a new choice and that is the SSISDB. There's a one-time creation required but it is per instance so you get proper isolation. This new SSIS repository though is only for packages that have been built under the Project Deployment Model. Before this, packages were a cohesive solution in Visual Studio/BIDS/SSDT but that was only for the development experience. After that, the packages could be spread to the four corners of the globe which is unfortunate if one package depended on another being in the same folder. The Project Deployment Model attempts to address this shortcoming by "compiling" all the packages in an SSIS project into a .ispac file. By compiling, it's really just zipping them together with a manifest file but now we have a deployable quantum and that's all you need for this to successfully run on a new environment *.

* As long as you have already installed the drivers, third party applications, set file system permissions, created users, applied configurations, etc but it is an improvement.

Back to the original question

Can the Package Store, a server wide feature, coexist with the new instance scoped SSISDB? Sure, why not?


What is a good/healthy mysql query execution time?

The question is a bit vague, but I think this answer (a) may provide some useful guidance and (b) is too big to fit in a comment.

Capacity planning a server is a bit more complex than that.  You need to take some basic queuing theory into account.  Your model would describe a server at 100% capacity, which is actually likely to cause performance issues.  In practice you would want to aim for the server running perhaps 25-50% capacity at peak load if you want your site to have consistent response times.

Queuing models state that the average wait time increases hyperbolically as the system approaches saturation.

A system that is 50% saturated has two requests waiting service on average
A system that is 90% saturated has 10 queued requests on average
A system that is 99% saturated has 100 queued requests on average

You should benchmark your queries under load to see how much resource they actually take up.  Then apply a view of your peak load to see how much capacity you need for the system to remain responsive.


How to backup an Oracle database from SQL Developer

No. And then yes. Well, it depends.

What are your requirements?

Backup / Restore Capabilities

Do you want a backup of the database ...

... for a restore
... to perform recovery
... to roll forward additional archive logs

Then RMAN is your tool of choice. Yes, RMAN is pretty complex and has a steep learning curve. However, once you have mastered it, you will benefit from a large variety of possibilities that RMAN provides you with, e.g. central RMAN catalog for multiple Oracle instances.

Export / Import Capabilities

Do you want a dump of a database ....

... for a "restore" to a new server/instance
... no recovery required
... no additional roll forward of archive logs

...then you might want to consider using the EXPDP (see: Data Pump Export) and IMPDP (see: Data Pump Import). These tools provide you with the ability to obtain schema-specific data and import the data into different tablespaces; or to filter the export via table names, dependencies. On import the schema can then be re-linked to a different schema (see: REMAP_SCHEMA), etc.

SQL Developer

SQL Developer has the capability to export data using a wizard. This is similar to the built-in EXPDP and IMPDP tools provided with the RDBMS installation. A quick guide to using the wizard can be found at: Exporting and Importing Metadata and Data and there in the section Using SQL Developer for Exporting and Importing (Tools | Database Export).


What are some common and useful fields for a users table in a database?

This is what I recommend for absolute, must-have fields in a users table:

Is there a standard implementation of a Users Database?

Things like last login, login location, login IP, etc. are better served in a historical table where you can run inserts and then query against the table if you need information. The idea is to have infrequent updates in a heavily used table, like users. Purge old records as necessary.


Reference multiple rows of one table from another

Setting the column "needed_for_bundle" as a Foreign Key that references the Primary Key of the table Bundle would mean that there's a one-to-many relationship between the two tables. Each product has to belong to one bundle, and one bundle can be composed of one or * product(s).

So first, needed_for_bundle should be the same type as the Primary key of Bundle table which is INT(6).

After changing that you can apply this code, for the foreign key constraint + index:

ALTER TABLE `Product`
ADD FOREIGN KEY (`needed_for_bundle`)
    REFERENCES `Bundle` (`id`)
    ON UPDATE CASCADE
    ON DELETE CASCADE;
CREATE INDEX `fk-id_bundle-Product-Bundle`
    ON `Product` (`needed_for_bundle`) USING BTREE;


AlwaysOn Availability Groups Delayed Replica

No, this is not possible with Availability Groups.  Any incurred delays are usually from things like network, secondary replica log block flush, etc.  There's no way that I can think of to turn that knob and create more of a time gap.

It sounds like if you want to take this "delayed transactions" approach, Log Shipping may have actually been the best solution there.  Or you could just deal with point-in-time restores in the (hopefully corner) case of data manipulation errors.  Ideally that wouldn't be happening all that frequently, and you should already have point-in-time restores as part of your operational capability, so you wouldn't need to incur any other additional technology of processes to accomplish this.


How to dynamically generate rows based on value in a column

This is an example of when a "Number" table becomes very handy! If you are not familiar with this concept, read this blog post from Adam Machanic: You REQUIRE a Numbers Table

The numbers table is used to join to the Appointment Slots column (in this case) to duplicate each row AppointmentSlots number of times.

-- A simple recreation of your CTE data...
CREATE  TABLE #AppointmentPeriods
(
    AppointmentLocation CHAR(1),
    AppointmentStartTime TIME,
    AppointmentEndTime TIME,
    AppointmentSlotsAvailable SMALLINT
)

INSERT  INTO #AppointmentPeriods VALUES
    ('A', '09:00', '09:15', 2),
    ('A', '09:15', '09:30', 2),
    ('A', '09:30', '09:45', 2),
    -- ....
    ('A', '16:30', '16:45', 2),
    ('A', '16:45', '17:00', 2),
    ('B', '09:00', '09:15', 1),
    ('B', '09:15', '09:30', 1),
    ('B', '09:30', '09:45', 1),
    -- ....
    ('B', '16:30', '16:45', 1),
    ('B', '16:45', '17:00', 1)

-- Numbers table
CREATE  TABLE #Numbers (NumberValue SMALLINT)

INSERT  INTO #Numbers
-- TOP value should be changed so it is greater than the
-- maximum number of potential appointment slots any location can have
SELECT  TOP 20
        ROW_NUMBER() OVER (ORDER BY object_id)
FROM    sys.objects

SELECT  #AppointmentPeriods.*, NumberValue AS AppointmentSlotNumber
FROM    #AppointmentPeriods
        INNER JOIN #Numbers
            ON AppointmentSlotsAvailable >= NumberValue

Query output:

Location | StartTime | EndTime | AppointmentSlots | SlotNumber
A        | 09:00     | 09:15   | 2                | 1
A        | 09:00     | 09:15   | 2                | 2
A        | 09:15     | 09:30   | 2                | 1
A        | 09:15     | 09:30   | 2                | 2
B        | 16:30     | 16:45   | 1                | 1
B        | 16:45     | 17:00   | 1                | 1

PS: There are many ways to generate a numbers table: SO: What is the best way to create and populate a numbers table?


Why isn't my count aggregate respecting my pivot columns?

Looks to me like a bug when producing the query plan when you have batch mode execution.

Here is a repro that shows the issue both in SQL Server 2016 (13.0.4001.0) and SQL Server 2017 (14.0.3015.40).

create table dbo.CS(CS bit not null, index IX_CS clustered columnstore);
create table dbo.X(C int not null);

go

insert into dbo.X(C)
select top(10000) row_number() over(order by (select null)) / 10
from sys.columns as c1, sys.columns as c2;

go

select P.[1], P.[2]
from (
     select X.C, 1 as X
     from dbo.X
     ) as T
pivot (
      count(T.X) for T.C in ([1],[2])
      ) as P
left outer join dbo.CS on 1 = 0;

select P.[1], P.[2]
from (
     select X.C, 1 as X
     from dbo.X
     ) as T
pivot (
      count(T.X) for T.C in ([1],[2])
      ) as P
left outer join dbo.CS on 1 = 0
option (querytraceon 9453);

Result:

1           2
----------- -----------
10000       10000

1           2
----------- -----------
10          10

Left join on dbo.CS here makes the query use batch mode and the trace flag 9453 disables batch mode.


Partitioning does not sound like a good fit for your scenario. Partitioning isn't something to be taken lightly. It requires a lot of planning and you'd have to rewrite 5 billion rows in order to convert the data. Instead you should consider a UNION ALL view. Depending on your requirements you could even make it a partitioned view.

A view will give you an easy way to query your data without any date movement. It's by far the simplest solution.

In general, partitioning doesn't improve query performance. However, with columnstore tables it can be helpful if it gives you better rowgroup elimination on the partitioned column. That can be accomplished without partitioning depending on how you build your columnstore tables. If you don't modify the old data it should be relatively straightforward to build your columnstore in a way that maintains order.

Columnstore tables do support partitioning, if you do have a legitimate need for it at some point. It's easy to experiment with it. Simply try to create an empty, partitioned columnstore table.


Mysql datadir and binlogs: same partition or not?

For splitting a single physical drive into two partitions, I see only the big downside (as you pointed out):  You could run out of space on one partition, while having space left on the other.  (Many times I have seen this.)

For multiple drives, I recommend striping as being better than manually segregating this file/log/table/etc from that file/etc.  In many cases the actions are sequential (first scan the index, then reach into the data), making the manual split of no benefit.  And striping avoids the "out of space, but not really" problem.

Let's approach your question from a different angle...  Why give yourself more work (partition maintenance) when the benefit is questionable?

If you have one HDD and one SSD, then put the logs (sequential access) on the HDD and the data+indexes (random access) on SSD.


I won't go into detail about why it's not a great idea, since you're already aware of all that. For anybody else stumbling across this answer: Try not to do this if possible!

For the occasions when I need to schedule an after-hours restart (patching, configuration changes, etc), I normally just run this in a batch file scheduled via Windows Task Scheduler:

net stop SQLSERVERAGENT
net stop MSSQLSERVER
net start MSSQLSERVER
net start SQLSERVERAGENT

Note that service names may vary if you aren't restarting the default instance. Also, the user account that the scheduled task runs under will need to have permission to restart the service.

I don't see any reason why this wouldn't work with a recurring schedule, as opposed to the one-time runs that I do.


How do I remove duplicate records in a join table in PostgreSQL?

In my experience (and as shown in many tests) NOT IN as demonstrated by @gsiems is rather slow and scales terribly. The inverse IN is typically faster (where you can reformulate that way, like in this case), but this query with EXISTS (doing exactly what you asked) should be much faster yet - with big tables by orders of magnitude:
DELETE FROM questions_tags q
WHERE  EXISTS (
   SELECT FROM questions_tags q1
   WHERE  q1.ctid < q.ctid
   AND    q1.question_id = q.question_id
   AND    q1.tag_id = q.tag_id
   );

Deletes every row where another row with the same (tag_id, question_id) and a smaller ctid exists. (Effectively keeps the first instance according to the physical order of tuples.) Using ctid in the absence of a better alternative, your table does not seem to have a PK or any other unique (set of) column(s).
ctid is the internal tuple identifier present in every row and necessarily unique. Further reading:

How do I decompose ctid into page and row numbers?
How list all tables with data changes in the last 24 hours?
How do I (or can I) SELECT DISTINCT on multiple columns?

Test
I ran a test case with this table matched to your question and 100k rows:
CREATE TABLE questions_tags(
  question_id integer NOT NULL
, tag_id      integer NOT NULL
);

INSERT INTO questions_tags (question_id, tag_id)
SELECT (random()* 100)::int, (random()* 100)::int
FROM   generate_series(1, 100000);

ANALYZE questions_tags;

Indexes do not help in this case.
Results
NOT IN
The SQLfiddle times out.
Tried the same locally but I canceled it, too, after several minutes.
EXISTS
Finishes in half a second in this SQLfiddle.
Alternatives
If you are going to delete most of the rows, it will be faster to select the survivors into another table, drop the original and rename the survivor's table. Careful, this has implications if you have view or foreign keys (or other dependencies) defined on the original.
If you have dependencies and want to keep them, you could:

Drop all foreign keys and indexes - for performance.
SELECT survivors to a temporary table.
TRUNCATE the original.
Re-INSERT survivors.
Re-CREATE indexes and foreign keys.
Views can just stay, they have no impact on performance. More here or here.


No, you dont have to do it again.

The differential backups are just that so the only effect your mistake made was to make the restore time longer than neccessary. If your last transaction log backup was restored successfully then you are ok.


temp tables in tempdb are not cleaned up by the system

I believe this is to do with the plan cache. Just ran a little test on my end and I can replicate it.

To remove the table from tempdb DBCC FREEPROCCACHE works, obviously target the plan handle for the stored procedure.

You could do this but it would just come back again and freeing the cache all the time generally isn't the best idea.

If you're concerned about it taking up space you can run this query I wrote for a monitoring tool that should work. On my tests it always shows 0KB after the SP has finished. This leads me to believe it just keeps the shell of the table there for some internal use. So it seems harmless to me.

USE tempdb;
WITH KB
AS (SELECT t.name,
           SUM(a.used_pages) * 8 AS KBUsed,
           p.rows AS [Rows]
    FROM sys.tables t
        INNER JOIN sys.partitions p
            ON t.object_id = p.object_id
        INNER JOIN sys.allocation_units a
            ON a.container_id = p.partition_id
    WHERE t.is_ms_shipped = 0
    GROUP BY t.name,
             p.rows
   ) SELECT * FROM KB


Merging two large tables physically in SQL Server 2014

going to take a while on 3 billion records but this is a query

select tableB.*
from tableB
left join tableA
on tableA.ID = tableB.ID
where tableA.ID is null


You could copy the data into another table which has the same schema and partitioning, then switch out the old data, then switch in the new data.

The better option would probably be to run the update in a loop against small numbers of rows using the TOP syntax.

SELECT NULL
WHILE @@ROWCOUNT <> 0
    UPDATE TOP (1000) YourTable
        SET Something = 'Something'
    WHERE SomeDate = '1/1/2012'
        AND Something <> 'Something'


how to make sure pt-sync-table data is the latest?

Use the --where option

Do only rows matching this WHERE clause. You can use this option to limit the checksum to only part of the table. This is particularly useful if you have append-only tables and don’t want to constantly re-check all rows; you could run a daily job to just check yesterday’s rows, for instance.
This option is much like the -w option to mysqldump. Do not specify the WHERE keyword. You might need to quote the value. Here is an example:

pt-table-checksum --where "ts > CURRENT_DATE - INTERVAL 1 DAY"

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum--where


SQL select statements not matching on certain rows

I would check and see if there are any white spaces after the Value 'User2' in the database.

I would also look at the application inserting the data and if possible profile the data stream before insertion to determine if the application is incorrectly adding white spaces.

Alternatively I would look at the source of the 'User2' and ensure that the source does not contain white spaces.


Why is ON DUPLICATE KEY not working as expected?

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query?

Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. Therefore you need to provide a value for every row INSERT. Not for UPDATE though, the column can be updated or not, depending on your requirements.

  Is it a PHP settings I need to change on my hosting?

No, nothing to do with PHP.

  I had a script which worked perfectly fine on my old hosting ...

If the script was working perfectly in the previous server, when inserting in either table, then the tables were not transferred identically. The extra columns - besides the 3 that the script provides values for - would have been defined as NULL (so they'd have a DEFAULT NULL) or there might be a DEFAULT attribute defined when the table was created.

Another possibility - besides lack of DEFAULT attribute - is that the table had an INSERT trigger that supplied a value for the UserName automatically upon insert and that the triggers were not properly copied when the the database was transfered.


In your size estimates, have you taken into account the amount of space taken by indexes? Also if you have text fields that are set as multi-byte (N[VAR]CHAR rather than [VAR]CHAR) and the input files are UTF-8 or plain one-byte-per-character then that will push your storage requirements up by up to a factor of two. Furthermore remember that if you have a clustered key/index on a table the size of this affects all other indexes on the table because they include the clustered key value for every row (so to give an extreme example if a table has an NCHAR(10) key where an INT would do and that is your clustered key/index you are not only using an extra 16 bytes per row in the data pages you also waste 16 bytes per row in every other index on that table).

Also, some space will be allocated but unused, either because the DB engine has left some space allocated after deletes so that it can be used again quickly for new data in that table or because the pattern of inserts and deletes has left many pages only part full.

You can run:

SELECT o.name
     , SUM(ps.reserved_page_count)/128.0 AS ReservedMB
     , SUM(ps.used_page_count)/128.0 AS UsedMB
     , SUM(ps.reserved_page_count-ps.used_page_count)/128.0 AS DiffMB
FROM sys.objects o
JOIN sys.dm_db_partition_stats ps ON o.object_id = ps.object_id
WHERE OBJECTPROPERTYEX(o.object_id, 'IsMSShipped') = 0
GROUP BY o.name
ORDER BY SUM(ps.reserved_page_count) DESC

to get a quick look at what tables are taking up space.

Also EXEC sp_spaceused run within that DB will return two result sets. The first lists the total space allocated in the filesystem for the data files and how much of that is unallocated, the second lists how much of the allocated space is used for data pages, for index pages, or is currently unused.

sp_spaceused will return the space used by a given object too, so you can loop this to build a table for analysis:

-- TEMP TABLES FOR ANALYSIS
CREATE TABLE #tTables (sName NVARCHAR(MAX), iRows BIGINT, iReservedKB BIGINT, iDataKB BIGINT, iIndexKB BIGINT, iUnusedKB BIGINT)
CREATE TABLE #tTmp (sName NVARCHAR(MAX), iRows BIGINT, sReservedKB NVARCHAR(MAX), sDataKB NVARCHAR(MAX), sIndexKB NVARCHAR(MAX), sUnusedKB NVARCHAR(MAX))
-- COLLECT SPACE USE PER TABLE
EXEC sp_msforeachtable 'INSERT #tTmp EXEC sp_spaceused [?];'
-- CONVERT NUMBER-AS-TEXT COLUMNS TO NUMBER TYPES FOR EASIER ANALYSIS
INSERT #tTables SELECT sName, iRows
                     , CAST(REPLACE(sReservedKB, ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sDataKB    , ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sIndexKB   , ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sUnusedKB  , ' KB', '') AS BIGINT)
                FROM #tTmp
DROP TABLE #tTmp
-- DO SOME ANALYSIS
SELECT sName='TOTALS', iRows=SUM(iRows), iReservedKB=SUM(iReservedKB), iDataKB=SUM(iDataKB),  iIndexKB=SUM(iIndexKB), iUnusedKB=SUM(iUnusedKB) FROM #tTables ORDER BY sName
SELECT * FROM #tTables ORDER BY iReservedKB DESC
-- CLEAN UP
DROP TABLE #tTables

The above code will output all the table sizes in one list, plus a single row for the totals. If needed you can use the various system views (like sys.objects and sys.dm_db_partition_stats used in the first query above, see http://technet.microsoft.com/en-us/library/ms177862.aspx for much more detail) to get more details such as the space used by each index.

There are three classes of unused space in a data file:

That which is not allocated to anything (this shows in the first resultset from sp_spaceused with no object specified)
That which is allocated to an object (reserved) but not currently used (this shows in the "unused" count in sp_spaceused's output.
That locked in part-used pages (this will look to be used as everything is allocated in single page chunks, one page being 8,192 bytes long). This is harder to detect/calculate. It is due to a mix of two factors:

Split pages. As data gets added you often end up with part empty pages (the storage engine could always normalise page contents, but this would be very inefficient), and as rows are deleted page contents are not automatically packed (again they could be, but the extra I/O load is generally far from worth it).
The storage engine won't split a row over multiple pages (this along with the page size where the 8,192 byte-per-row limit comes from). If your rows are fixed size and take 1,100 bytes each then you are going to "waste" at least 492 bytes of each data block allocated to that table (7 rows take 7,700 bytes and an 8th won't fit so the remaining bytes won't be used). The wider the rows, the worse this may be. Tables/indexes with variable length rows (which are far more common than completely fixed length ones) generally fair better (but are less easy to calculate the matter for).
Another caveat here is large objects (TEXT columns, [N]VARCHAR(MAX) values above a certain size and so on) as they do get placed off-page, just taking 8 bytes in the main row data to hold a pointer to the data elsewhere) so can break the 8,192 bytes-per-row-limit.

tl;dr: Estimating expected database sizes can be a lot more involved than it is natural to initially assume.


Speed up an aggregate query on an 11 million row table

As written you technically haven't asked a question here. I assume that you want to improve the performance of your query, but keep in mind that defining an acceptable response time is sometimes an important part of performance tuning. If a query runs once per day and takes a minute to finish is it really worth 8 hours of your time to make it run in 1 second?
More important than performance is correctness. It doesn't matter so much how long the query takes if it returns the wrong results, although of course taking a long time to return the wrong results is worse than taking a short time to return the wrong results. Depending on your time zone the UTC conversion stuff might not work out as you expected it. If there's any data affected by daylight savings time then you can't use the current hour difference between local time and UTC time to convert old data.
Setting all of that aside, I'm going to try to show you a few ways to speed up the query in the question. You have a covering index which is a good start, especially because it avoids reading the unrelated blob data. However there are still ways to speed up the query. I'm deliberately ignoring the clues that you gave about data distribution because I want to make this a more general answer that could help others and perhaps could be more helpful to you if your data changes in the future.
I mocked up 10 million rows with half of them having "OK" for the message and the other half having a long string. The dates are spread out over a few years. WARNING: this code takes up around 60 GB of space and ran in around 10 minutes on my machine.
CREATE TABLE [dbo].[NotificationResult]
(
    [IdNotificationResult] [bigint] IDENTITY(1,1) NOT NULL,
    [Message] [varchar](max) NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateCreatedUTC] [date] NOT NULL,
    [Filler] VARCHAR(1000) NOT NULL,
CONSTRAINT [PK_NotificationResult] PRIMARY KEY CLUSTERED
  (
    [IdNotificationResult]
  )
);

INSERT INTO [dbo].[NotificationResult] WITH (TABLOCK) ([Message], [DateCreated], [DateCreatedUTC], [Filler])
SELECT CASE WHEN RN % 2 = 1 THEN 'OK' ELSE REPLICATE('Z', 3000) END
, DATEADD(SECOND, 11 * RN, '20140101')
, CAST(DATEADD(SECOND, 11 * RN, '20140101') AS DATE)
, REPLICATE('FILLER', 166)
FROM
(
    SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) t;

CREATE NONCLUSTERED INDEX [IX_NotificationResult_DateMessage]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC ) INCLUDE ( [Message]);

If I run the query in the question I get the same query plan as you. It took 38 seconds. Here are some performance stats for the execution:

Table 'NotificationResult'. Scan count 5, logical reads 2509562, physical reads 0, read-ahead reads 2499799
SQL Server Execution Times: CPU time = 20626 ms,  elapsed time = 37663 ms.

The first opportunity to improve performance is that you have an implied WHERE clause predicate in your CASE statements. The query optimizer isn't smart enough to realize that any rows from previous years won't contribute to the totals. We know that 24 hours will always be longer than the difference between local time and UTC time so adding a filter like this shouldn't change the results:
WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))

Now instead of reading and aggregating 10 million rows from the index, SQL Server only has to process 1.4 million rows. The savings that you get with this optimization will depend on how the data is distributed in the plan. If all of your data is in the current year then performance won't improve yet. For my data, the query now finishes in 5 seconds and performance is greatly improved:

Table 'NotificationResult'. Scan count 5, logical reads 352033, physical reads 1, read-ahead reads 350073
SQL Server Execution Times: CPU time = 3062 ms,  elapsed time = 5354 ms.

We can do better than that. We're storing a VARCHAR(MAX) column in the index when really all that we need to know is if the column value matches "OK" or not. Without changing the table definition we can create smaller indexes to seek or scan against by creating three filtered indexes:
CREATE NONCLUSTERED INDEX [IX_NotificationResult_Date_OK]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC )
  WHERE [Message] = 'OK';

CREATE NONCLUSTERED INDEX [IX_NotificationResult_Date_NOT_OK]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC )
  WHERE [Message] <> 'OK';

CREATE NONCLUSTERED INDEX [IX_NotificationResult_Date_NULL]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC )
  WHERE [Message] IS NULL;

The idea here is that these indexes have the data that we need but are much smaller on disk than the existing IX_NotificationResult_DateMessage index. Getting the query optimizer to use the filtered indexes required a query rewrite and an index hint (not sure why). Here's one way to rewrite the query:
SELECT
  sum(case when FlagDTD = 1 then Success else 0 end)   as SuccessDTD
, sum(case when FlagDTD = 1 then [Error] else 0 end)   as ErrorDTD
, round(sum(case when FlagDTD = 1 then Success else 0 end) * 100.0 / sum(FlagDTD),2)
    as RateDTD
, sum(case when FlagYTD = 1 then Success else 0 end)   as SuccessYTD
, sum(case when FlagYTD = 1 then [Error] else 0 end)   as ErrorYTD
, round(sum(case when FlagYTD = 1 then Success else 0 end) * 100.0 / sum(FlagYTD),2)
    as RateYTD
FROM
(
    SELECT
      Success
    , [Error]
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1,  DATEADD(yy,
        DATEDIFF(yy,0,getdate()), 0)) then 1 else 0 end as FlagYTD
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1 ,
        convert(varchar(10), getdate(), 101)) then 1 else 0 end as FlagDTD
FROM
    (
    SELECT 1 Success, 0 Error, DateCreated
    FROM
    [dbo].[NotificationResult] WITH (INDEX (IX_NotificationResult_Date_OK))
    WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))
    AND [Message] = 'OK'

    UNION ALL

    SELECT 0 Success, 1 Error, DateCreated
    FROM
    [dbo].[NotificationResult] WITH (INDEX (IX_NotificationResult_Date_NOT_OK))
    WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))
    AND [Message] <> 'OK'

    UNION ALL

    SELECT 0 Success, 1 Error, DateCreated
    FROM
    [dbo].[NotificationResult] WITH (INDEX (IX_NotificationResult_Date_NULL))
    WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))
    AND [Message] IS NULL
    ) t
) Cnts;

Now the query finishes in less than a second:

Table 'NotificationResult'. Scan count 10, logical reads 3874, physical reads 0, read-ahead reads 0
SQL Server Execution Times: CPU time = 2499 ms,  elapsed time = 890 ms.

(the plan below is missing one of the indexes)

It's true that we read more rows from the indexes than before but the indexes are in total about 100 times smaller than the original index.
If that query still isn't fast enough you can consider an indexed view. If you have a UTC date column in the table then it is straightforward to create a view which is eligible to be indexed:
CREATE VIEW [NotificationResult_indexed]
WITH SCHEMABINDING
AS
SELECT
 [DateCreatedUTC]
, COUNT_BIG(*) AS CNT_BIG
, SUM(CASE WHEN Message = 'OK'  then 1 else 0 end) as Success
, SUM(CASE WHEN Message IS NULL OR Message <> 'OK'  then 1 else 0 end) as [Error]
FROM dbo.[NotificationResult]
GROUP BY [DateCreatedUTC];

CREATE UNIQUE CLUSTERED INDEX CLU_NotificationResult_indexed
    ON [NotificationResult_indexed] ([DateCreatedUTC]);
GO

I believe that this query roughly captures your intent although I probably got some details wrong:
SELECT
  sum(case when FlagDTD = 1 then Success else 0 end)   as SuccessDTD
, sum(case when FlagDTD = 1 then [Error] else 0 end)   as ErrorDTD
, round(sum(case when FlagDTD = 1 then Success else 0 end) * 100.0 / sum(FlagDTD),2)
    as RateDTD
, sum(case when FlagYTD = 1 then Success else 0 end)   as SuccessYTD
, sum(case when FlagYTD = 1 then [Error] else 0 end)   as ErrorYTD
, round(sum(case when FlagYTD = 1 then Success else 0 end) * 100.0 / sum(FlagYTD),2)
    as RateYTD
FROM
(
    SELECT
      Success
    , [Error]
    , CASE WHEN [DateCreatedUTC] > dateadd(YEAR, datediff(YEAR, 0, getdate()), 0)
       then 1 else 0 end as FlagYTD
    , CASE WHEN [DateCreatedUTC] > CAST(GETDATE() AS DATE)
       then 1 else 0 end as FlagDTD
    FROM
      [dbo].[NotificationResult_indexed]
      WHERE [DateCreatedUTC] > dateadd(YEAR, datediff(YEAR, 0, getdate()), 0)
) Cnts;

It finishes in 66 ms:

Table 'NotificationResult_indexed'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0
SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 66 ms.


Invalid syntax near RECURSIVE when creating a routine

It helps to pay attention to what error messages say (emphasis mine):

check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RECURSIVE"

meaning this particular version of the server has trouble understanding what "RECURSIVE" means. When we do check what the manual says about "recursive", we indeed find that

Recursive Common Table Expressions have been supported since MariaDB 10.2.2.

Generally it's a bad idea to use different DBMS versions for development and production. As mentioned in the comments, "grade your desktop up".


MySQL- Best practices for using rollback

There are two ways not to commit a transaction

Explicit ROLLBACK
Close a connection w/o COMMIT

Both of them are fine to use depending on what fits better the app logic.

When to rollback explicitly?

If you want to reuse a connection. If you want to make transactions as short as possible (a good thing to aim for).

When to close the connect w/o commit?

If a transaction is one-time task, and you'd like to simplify error handling.

Few things to consider.

Do not keep uncommitted transactions for long time.  Often that happens when the app opens a trx, goes to do something else, comes back later to complete and commit the transaction. This really hurts. It leads to high CPU usage and kills performance.
ROLLBACK usually goes into except ... block (or whatever applies to the specific language). But ROLLBACK itself may fail, so make sure to handle exceptions correctly to avoid "Failed to rollback a transaction" errors.
ROLLBACK often takes more time than you'd expect. If transaction was large, the rollback takes more time than all the previous statements in the transaction. Make the transactions as short as possible.


Retrieving data from stored procedure's cursor

SQL Server stored procedures can return record-sets from a query, a CURSOR would not be required (unlike in Oracle). The query below is all that's needed to get a recordset:

CREATE PROCEDURE dbo.SampleProcedure
AS
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEE


What is the purpose of the command-line options "-ANe" for MySQL?

The biggest reason was already stated by Vérace, but I wanted to add to it

When a database instance has lots of tables and lots of columns, the information_schema has to inspected in memory. This can be a cause for alarm. Why ?

Back in April 2014, I wrote the answer to Do Inactive MySQL Databases Consume Memory? and Adding new tables -- memory usage increases.

I have seen MySQL instances with 800+ databases with each database containing 162 tables.

Imagine loading metadata for the DB you connect to. Lots of needless prep work for the beginning of a DB Connection's LifeCycle along with allocating memory for a local copy of that metadata in addition to per-session buffers (See my post How costly is opening and closing of a DB connection?)

This is the biggest reason why I use -A to connect to do little things.


When to use hashtable over binary search tree vice verse for Database Index

Use BST where returning ordered records by the indexed column(s) is a typical use case.

Use HT otherwise.


Resolving high VLF count when database is part of an Availability Group

I just tested this on a SQL Server 2012 database that is in an availability group, and the log shrink is replicated to the secondary node(s).  Accordingly, you can simply shrink log files and then resize them as you would on a database that is not in an availability group.


Can I backup (Full) the Log-Shipping Secondary Database?

I believe you cannot take a native backup of the databases if they are in Standby/Read Only mode.

So backing up the secondary database would be not be a good go.

Also, if LS DB is in "Read only mode" you can run DBCC CHECKDB , it will complete but it would not get into the boot page and hence not the check you expected it to be. Moreover even if it reports or finds an error you would not be able to run a repair statement with DBCC CHECK.

What you can do is, take backup of the DB1 during off peak hours over a network to another test/dev machine with appropriate resources to restore the backup.

From the restored backup, you can perform the DBCC CHECKDB on the restored database.

Also, please read this excellent article by Aaron Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts for best practises on it.


SSDT: Requires Visual Studio? Requires connection to SQL Server? (SQL Server 2016)

Yes you can work offline but it will be difficult to develop and test without the input/output connections files/tables your package will use.

One option is to install SQL Server on your pc, create a database and copy the source and target tables and some test data to your pc. Then you can develop and test locally.

Deployment will be difficult unless you have ssdt on the server. Otherwise you will need access to deploy to the server from another machine.


phpMyAdmin configuration storage is not completely configured message

You do not need the features to use phpMyAdmin. You can read more about what they include in the phpMyAdmin documentation.

To disable, just remove the corresponding lines from your config.inc.php (you can start here in the documentation; each entry up to and including $cfg['Servers'][$i]['designer_coords'] relates to the phpMyAdmin Configuration Storage features; if you have any of those lines in your config.inc.php you can simply remove them). You also may have $cfg['Servers'][$i]['controluser'] and $cfg['Servers'][$i]['controlpass'] entries, which generally are only used for the Configuration Storage features as well and in most cases can be removed.


Why would a Clustered Index Seek return a higher "Actual Number of Rows" than there are rows in the underlying table?

The Seek returns more rows because it is on the inner (bottom) side of a Nested Loop. Every row returned by the outer operation results in a new Seek operation. So you're not getting 60m rows from a single Seek, but from over 9000 of them (number of executions).

Also of note: when looking at estimations, the total number of rows estimated will be Estimated Number of Executions multiplied by Estimated Number of Rows


Hiding all databases in SSMS that a user does not have access to without making them db_owner

First of all if you DENY VIEW ANY DATABASE TO [user] it will not "see" even the database where it's a memeber of db_owner role, except for sysadmin, only database owner will "see" the database owned by it.

Database owner is different from just membership in db_owner role, it's a login that has authorization on that database (grant authorization on database::myDB to myLogin), so only one login at a time can own the database, and the corresponding user has all the permissions in that database and can "see" it in OE even when VIEW ANY DATABASE is denied to the corresponding login.

Saying that there is an alternative solution to your problem, it's SQL Server 2012 Contained Database Feature

Starting with SQL Server 20012 you can enable Contained Database Feature at the instance level and then make your databases contained.

This means that there will be a database, not server to authenticate your users, and all these users will "see" only the databases where they were created. They will not "see" nothing at the server level.

If one day you'll need to migrate your databases to another server, you'll not need to transfer server logins to another server, your contained database already contains all its users and they are not "orphaned" because they don't have corresponding logins at all.


ORM-style server-side programming languages (OO replacement for PL/SQL?)

Oracle has Java server-side, but I'd caution that it is not a replacement for PL/SQL. There is no better language than PL/SQL for manipulating data stored in the database - Java may be appropriate for computationally intensive business logic.

For postgres,

  There are currently four procedural languages available in the standard PostgreSQL distribution: PL/pgSQL (Chapter 39), PL/Tcl (Chapter 40), PL/Perl (Chapter 41), and PL/Python (Chapter 42). There are additional procedural languages available that are not included in the core distribution

But I think it is fair to say that PL/pgSQL is the most mature and feature complete (and, you may be sorry to hear, very similar to PL/SQL).

I don't know much about MySQL stored procedures but here is a link to the docs


Please have a look at the basics of Database normalization. In a very general way, 1:N relationships usually end up being represented as 2 tables. That is, if we have several products, and each product can have several reviews, we would create two tables, one for products and another for reviews, and we would link the reviews by using a product_id key (containing the primary key of the product). Same would apply for images, assuming there is a 1:N relationship with product and not a 1:1 relationship with review.

When querying for data, we need to join both tables for obtaining full results.

Please note that sometimes a non-normal form can be used, merging several tables in one. This sacrifices maintainability (potential duplicate data problems, among others, forcing extra work outside of the database scope) for performance, but that is a question of optimisation, not design phase.


Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

@Lennart's answer is one way to negate your query, but it only selects países that are associated with other fabricantes_distribuidores. If you want to include países that are not linked with any distribuidor, too, you have to add an outer join (I use a left outer join as per convention):

SELECT DISTINCT nom.pais_id
FROM nomencladores.pais_fabricante_producto_solicitud nom
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE neg.id IS NULL OR
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

Check the differences in results between the original query, Lennart suggestion and mine:

mysql> SELECT * FROM nomencladores.pais_fabricante_producto_solicitud;
+----------------------------------+---------+
| fabricante_producto_solicitud_id | pais_id |
+----------------------------------+---------+
|                                1 |       1 |
|                                2 |       2 |
+----------------------------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM negocio.fabricante_producto_solicitud;
+----+----------------------------+-----------------------+
| id | fabricante_distribuidor_id | producto_solicitud_id |
+----+----------------------------+-----------------------+
|  1 |                          1 |                     1 |
|  3 |                          3 |                     3 |
+----+----------------------------+-----------------------+
2 rows in set (0.00 sec)

Your query:

mysql> SELECT DISTINCT nom.pais_id
    -> FROM   negocio.fabricante_producto_solicitud neg
    ->        INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
    ->          ON ( neg.id = nom.fabricante_producto_solicitud_id )
    -> WHERE  neg.fabricante_distribuidor_id = 1
    ->        AND neg.producto_solicitud_id = 1;
+---------+
| pais_id |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Lennart's:

mysql> SELECT DISTINCT nom.pais_id FROM
    -> negocio.fabricante_producto_solicitud neg
    -> INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
    -> ON ( neg.id = nom.fabricante_producto_solicitud_id )
    -> WHERE  NOT (neg.fabricante_distribuidor_id = 1 AND
    -> neg.producto_solicitud_id = 1);
Empty set (0.00 sec)

Mine:

mysql> SELECT DISTINCT nom.pais_id
    -> FROM nomencladores.pais_fabricante_producto_solicitud nom
    -> LEFT JOIN negocio.fabricante_producto_solicitud neg
    -> ON ( neg.id = nom.fabricante_producto_solicitud_id )
    -> WHERE neg.id IS NULL OR
    -> NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );
+---------+
| pais_id |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

EDIT: You should only use LEFT JOIN for the "negative" of distribuidor, other relations should be read with normal JOINs:

SELECT DISTINCT ps.id, ps.nombre
FROM nomencladores.pais_fabricante_producto_solicitud nom
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
JOIN pais ps
ON ( nom.pais_id = ps.id )
WHERE neg.id IS NULL OR
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

Example results:

+------+--------+
| id   | nombre |
+------+--------+
|    2 | Perú   |
+------+--------+
1 row in set (0.00 sec)

Again: this will select the id and name of every different country on the database that has a solicitud. If you want to select every possible country (no matter if they have solicitudes or not), you have to move the LEFT JOIN to pais and chain it:

SELECT DISTINCT ps.id, ps.nombre
FROM pais ps
LEFT JOIN nomencladores.pais_fabricante_producto_solicitud nom
ON ( nom.pais_id = ps.id )
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE nom.pais_id IS NULL OR neg.id IS NULL OR
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

On my example, we would get:

+------+---------+
| id   | nombre  |
+------+---------+
|    2 | Perú    |
|    3 | Uruguay |
+------+---------+
2 rows in set (0.00 sec)

Both solutions are technically correct, the confusion on what the definition of "the opposite" is. The first one is countries that have solicitudes but not for that provider, the second is all countries on the database (having solicitudes or not) minus the ones that have solicitudes for that particular provider.


Finding "one of" entries to a composite key/index

Task 1 may be solved by
SELECT t1.*
FROM test t1
JOIN test t2 USING (b_id, c_id, d_id)
WHERE (t1.a_id, t2.a_id) IN ((1,2), (2,1))


Why can a log backup not be taken without first taking a full backup?

When a database is switched to FULL recovery, it behaves as if it was in SIMPLE recovery until you take a full database backup. Up to that moment, you cannot take log backups, because the log gets truncated on CHECKPOINT, as happens in SIMPLE recovery.

See this article by Paul Randal for reference.

Log backups form part of a chain that allow the whole database to be recovered. The chain starts with a full backup (complete copy), and may continue from that point with further differential or log backups.

Image taken from Complete Database Restores (Full Recovery Model)

A transaction log backup contains the information needed to:

Redo all committed changes to the database since the last full or log backup
Undo in-progress (not yet committed) changes at the time the log backup was taken

In principle, it would be possible to recreate a database from log information alone, if every log backup ever was available, including the initial creation of the database itself, but that is not the way it was implemented.

As it is, a full backup is necessary is start the recovery path.


The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ?

IMHO, NO - since you are on sql server 2012.

Why ?

SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is  accessible else it will pick up the next available replica.

So in sql server 2012 and 2014, even if you have multiple secondaries available for honoring read requests, its not possible to spread the read load across the available secondaries.

SQL server 2016 allows you to configure load-balancing across a set of read-only replicas.

  Is this possible to configure with minimum impact on users?

It is possible with minimum impact, but as I said above it would not be worth doing it.

How to do it ?

Add server 3 to windows failover cluster.
Take full backup from server 1 (since it is primary) and restore it with no recovery on server 3.
Take a log backup from server 1 and restore it with norecovery on server 3.
Join the database to the availability group.
Change the sync mode to Synchronous and then Failover the server1.db1 to server3.db1 and now server3.db1 will be primary.
Resume the data movement on all the secondary replicas. In my testing, the data movement gets suspended and you have to resume it.

Below is how it will look (the drawing is not pretty, but it explains what I am telling :-) ):

  the backups can be taken on a secondary replica right?

Yes, you can take copy_only full & regular log backups on secondary replica. A full backup must be performed on primary database.


What is the PREEMPTIVE_REENLIST wait type in Microsoft SQL Server 2008 R2

This is not definitive proof but just my opinion based on what I learned from a PluralSight course Paul Randal did on wait stats.

PREMEPTIVE waits are indicative that SQL Server Operating System (SQLOS) has to change a thread to preemptive mode to handle something outside of SQL Server, at the OS level.

Now as you have already determined this types of waits are not documented much at all. There are a few you can see documented in BOL under the sys.dm_os_wait_stats DMV but it is not extensive. In most cases the calls being made to the OS are via Windows API calls. So what I ended up learning from the course is that if you take off the PREMEPTIVE portion of the PREEMPTIVE_REENLIST you end up with the name of the possible API being used: REENLIST. So if you take that and start searching around on the web you can try and deduce what it means.

So my deducing would be that I came across this MSDN article on IResrouceManager::Reenlist there are also other MSDN articles on similar named methods that all discuss a TransactionManager. It is not the content that catches my eye but the section of MSDN where this article is located: DTC Interfaces. So you don't specify this level of setup for your server but I would make a hunch that you are using distributed transactions. This can come from making remote calls to other instances/databases or if you are working on a clustered instance. If you actually read a statement from the MS DTC Service, although it is from SQL Server 2000 you might catch the key word that caught my eye:

  One or more servers running SQL Server can then be instructed to enlist in the distributed transaction and coordinate the proper completion of the transaction with MS DTC

Just my opinion...


Select ONE most recent post for each author

If you aim is to have queries with maximum efficiency, none of the above queries is really the best. Not always at least.

Efficiency depends on many different things, like the specific DBMS, the specific version (different versions have different improvements on the optimizer and the available syntax), the type of columns, the indexes available, the size of the tables and distribution of values, the hardware the server is running, the configuration settings etc.

You should always test various different ways of writing the queries, on your tables, with the sizes and distribution you expect to have on production, with your hardware and configuration settings, to decide which rewritings of the queries should be kept.

This specific kind of query is often called greatest-n-per-group (there is even a tag for it!) and under certain assumptions, one of the many ways to write them, is often quite efficient in both MySQL and PostgreSQL. It uses a LATERAL join in Postgres, which is available in 9.3+ versions (in SQL Server lingo CROSS/OUTER APPLY) and a simulation of this join in MySQL.

The assumptions are that the number of authors (the attribute we group by on) is small, compared to the number of posts (the table where we apply the group by). It's also best if there is an index or a table to find all the distinct author_id values and an additional index on the posts table for the group by.

This solution to the greatest-n-per-group problem matches also your request about ties, as it returns always one result per group. If you want to be precise about which one (of the tied) will be returned, the ORDER BY in the subquery can be modified (to ORDER BY pi.date DESC, pi.id DESC or ORDER BY pi.date DESC, a.name for example).

Query in PostgreSQL:

SELECT p.*
FROM authors AS a
   , LATERAL
       ( SELECT pi.*
         FROM posts AS pi
         WHERE pi.author_id = a.author_id
         ORDER BY pi.date DESC
         LIMIT 1
       ) AS p ;

Query in MySQL:

SELECT p.*
FROM authors AS a
  JOIN posts AS p
    ON p.id =
       ( SELECT pi.id
         FROM posts AS pi
         WHERE pi.author_id = a.author_id
         ORDER BY pi.date DESC
         LIMIT 1
       ) ;

The useful index is on posts (author_id, date, id) for MySQL and or on posts (author_id, date DESC) for Postgres.

Needless to say again but before using any of the above, they should be tested in your environment and cross tested against all the many other versions/rewritings of the query. In Postgres for example, the DISTINCT ON syntax can be used in version older than 9.3. The resulting query is more compact than the LATERAL and might be more efficient, under different data distributions. Query:

SELECT DISTINCT ON (author_id) p.*
FROM posts AS p
ORDER BY p.author_id,
         p.date DESC ;


Skip multiple rules during SQL Server installation

Yes, just separate the rules with a space, for example:

/SkipRules=PerfMonCounterNotCorruptedCheck FacetPowerShellCheck

While SkipRules is undocumented, the list of rules is documented at:

Edition Upgrade Rules

Even more information at:

SQL 2012 Setup Rules – The 'Missing Reference'


Difference between now() and current_timestamp

There is no difference. Three quotes from the manual:
1)

These SQL-standard functions all return values based on the start time
of the current transaction:
...
CURRENT_TIMESTAMP
...

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is
named to clearly reflect what it returns.

now() is a traditional PostgreSQL equivalent to transaction_timestamp().

Bold emphasis mine. CURRENT_TIMESTAMP, transaction_timestamp() and now() do exactly the same. CURRENT_TIMESTAMP is a syntactical oddity for a function, having no trailing pair of parentheses. That's according to the SQL standard.
If you don't declare a column alias for a function call in an SQL statement, the alias defaults to the name of the function. Internally, the standard-SQL CURRENT_TIMESTAMP is implemented with now(). Up to Postgres 9.6 that shows in the resulting column name, which was "now", but changed to "current_timestamp" in Postgres 10.
transaction_timestamp() does the same, but this one is a proper Postgres function, so the default alias has always been "transaction_timestamp".
Do not confuse either of these functions with the special input constant 'now'. That's just one of several notational shorthands for specific date/time/timestamp values, quoting the manual:

...  that will be converted to ordinary date/time values when read. (In particular, now and related strings are converted to a specific time value as soon as they are read.) All of these values need to be enclosed in single quotes when used as constants in SQL commands.

It may add to the confusion that (up to at least Postgres 12) any number of leading and trailing spaces and brackets ({[( )]}) are trimmed from those special input values. So 'now()'::timestamptz - or just 'now()' where no explicit type cast is required - is also valid and happens to evaluate to the same timestamp as the function now() in most contexts. But those are constants and typically not what you want as column default for instance.
db<>fiddle here
Old sqlfiddle
Notable alternatives are statement_timestamp() and clock_timestamp(). The manual:

statement_timestamp() returns the start time of the current
statement (more specifically, the time of receipt of the latest
command message from the client). [...]
clock_timestamp() returns the actual current time, and therefore its
value changes even within a single SQL command.

Note: statement_timestamp() is STABLE as the above (always returns the same value within the same SQL command). But clock_timestamp() necessarily is only VOLATILE. The difference may be significant.


Should I backup Master key / Service master key if I'm using TDE?

The Master Key is used to protect all your certificates' private and asymmetric keys of each TDE database. It's pertinent it exists on your server for best security and prevention of someone gaining access to the keys of one of your TDE databases, and by Microsoft's design the Master Key lives in both the TDE database and master database.
The certificate also gets created in the master database so that it can be used by the user databases that you want to apply TDE to. The database encryption key gets created in the user database you're applying TDE to and uses that global certificate from the master database.
For more information, these Microsoft Docs - SQL Server and Database Encryption Keys (Database Engine) clarify the purposes of the Master Key and where it's stored, specifically the Database master key section. Additionally the documentation on Transparent Data Encryption provides good details and shows an example of creating the Master Key in the master database but the certificate in the user database they're applying TDE to.


Check this registry key to ensure ORACLE_HOME is configured for the service:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_XE\ORACLE_HOME

If that doesn't exist, check here:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE\ORACLE_HOME

Also make sure this other key isn't pointing to your old client registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_XE\ORACLE_HOME_KEY
or
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE\ORACLE_HOME_KEY


You're getting NULL mainly because of this insert insert into @table (Status) values (@state_desc) since you declared the variable @state_desc and never set any value to it.  Try this instead:

DECLARE @STR_DBServer sysname;
DECLARE @SQLString nvarchar(500);
DECLARE @complete_catalog TABLE(server sysname, database_name sysname, state_desc nvarchar(120));
DECLARE @remote_databases TABLE(name sysname, state_desc nvarchar(120));

--Lists all linked servers on the current instance (except SQL Server 2000)
DECLARE LinkedServersCur CURSOR FOR
    SELECT name FROM master.sys.servers WHERE is_linked = 1 and provider <> N'MSDASQL';

OPEN LinkedServersCur;
FETCH NEXT FROM LinkedServersCur INTO @STR_DBServer;

WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SQLString = N'SELECT m.name, m.state_desc FROM [' + CONVERT(nvarchar(100), @STR_DBServer)+ '].master.sys.databases m';
        INSERT INTO @remote_databases EXECUTE sp_executesql @SQLString;

        INSERT INTO @complete_catalog(server, database_name, state_desc)
            SELECT @STR_DBServer, name, state_desc FROM @remote_databases;

        DELETE FROM @remote_databases;
        FETCH NEXT FROM LinkedServersCur INTO @STR_DBServer;

    END
CLOSE LinkedServersCur;
DEALLOCATE LinkedServersCur;

SELECT server, database_name, state_desc FROM @complete_catalog;

You can compare the result of this query with your local table DBConfig.dbo.T_Datenbanken if you wish.


design DB review and check constraint for postalcode help?

Yes, that would be fine if your design goal is that Persons have only 1 Addresses. With this design, each Persons can have 1 Addresses but two or more Persons can have the same Addresses.

It's all a matter of your business needs. If the above is what you are trying to get, then yes, it is correct.

However, I think it's most common the other way around where the Addresses would have a foreign key to the Persons because a Persons could have more than one Addresses.

As for your constraint to check the postal code - well first off you are missing the space and it's lower case. Whether that will work will depend on which database system you are using. I tested it with PostgreSQL and it does not work.

I don't think you can really have such a simple constraint to fully validate a Canadian postal code. For example, there are some letters and some numbers which are never used. I'm a bit rusty on my Canadian postal office codes but I seem to recall that number 5 is never used as it's too similar to S, etc.


How can I verify that MySQL's slave_compressed_protocol is working

Connection Phase of the Client/Server Protocol

At the Client Response, there is crosscheck of capabilities between the client and the server. The CLIENT_COMPRESS flag (value 0x00000020) needs to be mutually exchanged before continuing authentication.
To be honest with you, I have never heard of a MySQL question this deep. Good the thing all of this appears in the MySQL Internals Documentation.
How on earth would you actually know where to intercept the CLIENT_COMPRESS ?
I would suggest using tcpdump or snort to inspect packets.
I will leave it to you to read up on the needed exchange packets

Protocol::Handshake
Protocol::HandshakeResponse


Change the behavior of temporal tables to log actual value changes rather than dummy updates

On a further dive it seems that this currently falls under : Manage Retention of Historical Data in System-Versioned Temporal Tables as a reactionary solution to the problem, instead of a prior configuration.

And they propose three approaches:

Stretch Database
Table Partitioning
Custom Cleanup Script

There are some other nice articles I found, which partially answer my question, however, I'd love if I can manage this behavior at the point of data creation rather than the cleanup, so I'd request more information in case someone has it, or if this changes in the future.

Other articles:
Managing Temporal Table History in SQL Server 2016
Temporal Table Considerations and Limitations


ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

You get Error 121 when you try to create a table that contains a constraint with the same name as a constraint defined elsewhere in your database. You can avoid this issue by naming the constraints yourself in the creation script. More information can be found below:

InnoDb Error Codes

http://dev.mysql.com/doc/refman/5.5/en/innodb-error-codes.html

Create Table Statement

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

I hope this helps you.


Inherit default privileges: every table created by *any member of* role A is readable by role B

You have researched well, and there is no way to get what you want.

So maybe you should change your requirements.

My suggestion is that all these users are members of a role (let's call it tableowner), and only that role has the CREATE privilege on the schemas.

So in order to create, alter or drop a table, the users first have to use SET ROLE tableowner.

Then all tables are owned by that role, and a single ALTER DEFAULT PRIVILEGES FOR ROLE tableowner ... will do the trick.


You simply don't use begin in sqlplus if you're just going to issue a series of SQL queries. You're in a transaction already as soon as you issue some SQL. You can't really be outside of a transaction anyway for practical purposes (sure, if you've just logged in, or just committed and haven't started anything else, well, you're not in a transaction).

A few things to be careful with though:

sqlplus does have an autocommit setting. It's off by default in modern versions, but just to make sure:

SQL> show autocommit
autocommit OFF

If it happens to be on:

SQL> set autocommit off

sqlplus commits on exit by default even in modern versions. To disable that:

SQL> set exitcommit off

(That's fairly new, appeared in 11g or 11gR2. Previously always committed on exit.)
DDL commits. (Twice. Once before, once after.) Note that truncate is DDL in Oracle.

Aside from these gotchas, you're in a transaction already when you start sqlplus, and can commit or rollback as you please.

$ sqlplus mat

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 20 19:51:31 2014
...
SQL> insert into abc values (1) ;

1 row created.

SQL> select count(*) from abc;

  COUNT(*)
----------
         1

SQL> rollback;

Rollback complete.

SQL> select count(*) from abc;

  COUNT(*)
----------
         0

You need to use begin/end when you want to run a PL/SQL block. (The short/single line form for that being exec.)


The ASE dataserver is configured with a single sort order; see selecting default sort order.

For scenarios where the user needs to use a sort order different from the dataserver's default sort order there is:

sortkey() - allows for sorting data by a user-selected sort order


Schema for lists of relationship differences

If I understand your question correctly you are looking to store, let's say, a Person entity with Name and DateOfBirth attributes.  You will also have an Employee entity which is defined as everything a Person has plus EmploymentNumber plus DeskNumber but doesn't have DateOfBirth.  I'm guessing you want to be able to add a column to a base type and automatically see it showing up in the sub-type(s), and sub-sub-type(s).  In short you are looking for an object-oriented data store.  RDBMSs in general are not will suited to deliver this featrure.

If you know what these differences are and they are stable, at least between schema relases, you can define a type/ sub-type model.  The base table has the columns which are common to all types.  It has a surrogate primary key.  From there you define further tables, strictly adding columns as you go.  The same ID is carried through this hierarchy.  The case where you need to remove columns from one type to a descendant is handled by defining an abstract common ancestor and placing the removed column in one branch but not the other.

For my example above we would end up with these tables

Human
    ID     PrimaryKey
    Name

Person
    ID     Primary key and also foreign key to Human.ID
    DateOfBirth

Employee
    ID     Primary key and also foreign key to Human.ID
    EmploymentNumber
    DeskNumber

You could add an column to the base table (Human) to indicate which of the sub-types any particular occurance writes to.  I don't think that is necessary because the process which writes to this database has to know which type it is dealing with in order to capture the correct values.  Even if that process dynamically builds its list of attributes by examining the DB, it has to know which ultimate sub-type it is looking for to bootstrap the process.

It is tempting to have, say, EmployeeID and a separate foreign key HumanID.  This is unnecessary.

When retrieving values you will either be interested in a known occurrance of a sub-type or will be looking for all available values for an instance i.e. "Human 99 as Employee" or "All about occurrance 99".  The former can be had by INNER JOINing Employee up to its ultimate ancestor using the defined keys.  The latter by OUTER JOINing all tables in the model.

If your attributes can vary at run time you will be forced into using some variant of an entity-attribute-value (EAV) model.  This has been well documented in many, many blogs, papers, forums and SO questions.  While being fractious, conceptually challenging and non-performant these models can be made to work (for an appropriate definiton of "work").  Your tables will be a lot like this:

ItemType
    ItemTypeID    primary key
    Name
    ParentTypeID  fk to ItemTypeID

Attribute
    AttributeID   primarykey
    Name
    AddedOrDeletedInd
    ItemTypeID    fk to ItemTypeID

Item
    ItemID
    ItemTypeID    fk to ItemTypeID
    ParentItemID  fk to ItemID

ItemValue
    AttributeID   fk to Attribute
    ItemID        fk to Item
    Value

ItemValue.Value could be a catch-all varchar() column or you could have one for each datatype you want i.e. ValueInt, ValueDate, ValueChar etc. and and indicator in Attribute to say which is populated.  As you can see reading any one item's full definition will require recursion through a tree.  Good luck.


Query with dynamic ranges in PostgreSQL based on status

A textbook case for window functions.
Building on a few assumptions, it could look like this:

SELECT grp
      ,count(*) AS ct
      ,sum(total) AS total
      ,min(invoice_number) AS initial_correlative
      ,max(invoice_number) AS ending_correlative
      ,status
FROM  (
   SELECT *, count(step OR NULL) OVER (ORDER BY id) AS grp
   FROM  (
      SELECT *, lag(status) OVER (ORDER BY id) IS DISTINCT FROM status AS step
      FROM   tbl
      ) x
   ORDER BY datetime
   ) y
GROUP  BY grp, status
ORDER  BY grp;

Subquery x marks each beginning of a new group (step).
Subquery y counts beginnings to create a unique number for each group (grp).
Then you can GROUP BY this number in outer query. status is the same for each group per definition.

SQL Fiddle.

Related answers:
 - Select longest continuous sequence
 - On SO: Group By and Aggregate Sequential Numeric Values


The PSQL Console in pgadmin3 consists of opening a terminal and running psql --username yourname [other options] dbname inside it for you, with yourname and dbname being taken from what you've used to log in pgadmin3.

This console runs the psql interpreter which expects either psql meta-commands starting with backslash, or plain SQL commands.

When typing this:

  `psql -U postgres -d postgres -f dump.sql`

it's taken as an unfinished SQL sentence for the interpreter, so it's just waiting for the rest. That's why nothing happens as you wrote. This command is out of context.

What you want is:

\i /path/to/dump.sql

\i means 'include' and is the meta-command to feed a file as a stream of SQL commands to the interpreter.
You'll see the output of these commands on the terminal and when finished , type \q to end the session.


Selecting difference between two tables returns different number of results than counts of same two tables

The EXCEPT operator is not going to limit the rows returned from TABLE1 to the difference between the row counts in TABLE1 and TABLE2 - it will eliminate from the resultset every row in TABLE1 that has a matching row in TABLE2.

In your example, the difference in row counts is 1816 but your resultset is 1610 entries. This means there are 4789 entries in TABLE1 that match a row in TABLE2, suggesting that some of the rows in TABLE1 are not unique.

You can test this out by running SELECT COUNT(DISTINCT *) FROM TABLE1. If the value is less than 6399, then there are duplicate rows of data and some of these are likely being eliminated by the EXCEPT operator hence the difference in row counts.


Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Not relevant to readers using very old versions of MySQL, but on more recent versions of MySQL you can use:
SELECT i.name FROM information_schema.INNODB_SYS_TABLES i WHERE i.space = 0;
to see which tables are in the shared table space and use:
SELECT i.name FROM information_schema.INNODB_SYS_TABLES i WHERE i.space <> 0;
to see which ones are in their own files.


MariaDB documentation page for HandlerSocket says:

HandlerSocket gives you direct access to InnoDB/XtraDB and SPIDER.

It doesn't mention TokuDB (despite it being distributed with MariaDB), so I would exclude that this engine is supported.


Select the top 5 records and the specific column from each table in the same database

This will work on SQL Server 2005 and up:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'

SELECT TOP (10) ''' + REPLACE(name, '''','') + ''','
  + STUFF(
    (SELECT TOP (3) ', ' + name FROM sys.columns
      WHERE [object_id] = t.[object_id]
      ORDER BY column_id
      FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'
  ), 1, 2, '') + '
    FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables AS t;

PRINT @sql;
--EXEC sp_executesql @sql;

When you're satisfied with the PRINT output (note that it will truncate at 8K even though the command should be a lot larger than that for 50+ tables), uncomment the EXEC.


MS Access : Navigation pane is suppressed

If you press F11 you can open and close the navigation pane. If it is stuck try grabbing the bar and sliding open manually. You do this by putting the cursor on the bar until it turns into a horizontal arrow. Seen here. Once fully open again it should work fine.


MySQL Replication without stopping master

There are two approaches you can try with no or minimal downtime
Given the following:

Master IP is 10.1.20.30
Slave IP is 10.1.20.40

APPROACH #1 : Data is 100% InnoDB
This is very straightforward.
STEP01) If the Master does not have server-id defined in my.cnf you will have to add it
[mysqld]
server-id=100

STEP02) If the Master does not have log-bin defined in my.cnf you will have to add it
[mysqld]
log-bin=mysql-bin

STEP03) If you have do Steps 1 and/or 2 on the Master, do service mysql restart (mandatory)
STEP04) Create MySQL Replication User on the Master
mysql> GRANT SELECT,REPLICATION USER,REPLICATION CLIENT ON *.*
TO repluser@'10.1.2.30' IDENTIFIED BY 'replpass';

STEP05) Create a mysqldump as a point-in-time snapshot on the Master
MYSQL_CONN="-uroot -ppassword"
MYSQLDUMP_OPTIONS="--master-data=1 --single-transaction --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers --all-databases"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql

When done, line 22 of MySQLData should have the binary log and position of the Master as of the moment the mysqldump was launched. To see it, just run
head -22 MySQLData.sql | tail -1

STEP06) Create replication status on the Slave with
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

STEP 07) Load the mysqldump into the Slave
mysql -u... -p... < MySQLData.sql

Don't worry about replication starting at the right place. Remember, I said line 22 contains the command with correct binary log and position.
STEP 08) Run SHOW SLAVE STATUS\G
If Slave_IO_Running is Yes and Slave_SQL_Running is Yes, CONGRATULATIONS !!!
APPROACH #2 : Data is InnoDB/MyISAM Mix
Rather than reinvent the wheel, please read my earlier posts on using rsync to make a Slave

Jul 08, 2011 - MySQL slave replication reset with no Master Downtime (using MyISAM)
May 23, 2011 - How can I move a database from one server to another?
Apr 08, 2011 - Create a MySQL slave from another slave, but point it at the master

Give it a Try !!!


Should I use a three-way associative table?

If I have followed correctly, the two options is whether to have one or two intermediate table for the relationship between groups, user_types and resources. The answer, from a purely design point of view depends on whether that relationship is a strong or weak one. In other words, if groups_user_types (lets call it authorizations) is something that should exist if one of the other entities do not, and if an authorization is shared between resources or not. Let's see an example:

"manual1" can be accessed by:

painters in group a
foremen in group a
painters in group b

manual2 can be accessed by:

painters in group a
foremen in group a

resources
---------
id|name
 1|manual1
 2|manual2

user_types
----------
id|name
 1|painters
 2|foremen

groups
------
id|name
 1|a
 2|b

groups_user_types
-----------------
id|group_id|user_type_id
 1|       1|           1
 2|       1|           2
 3|       2|           1

groups_user_types_resources
---------------------------
resource_id|groups_user_type_id
          1|                  1
          1|                  2
          1|                  3
          2|                  1
          2|                  2

You know your application better than I do, but I do not see groups_user_types as a strong entity (no attributes, no reasons to exist if  groups_user_types_resources disappears). Maybe I am wrong, and permissions is a big part of our application, and you will give each resource a named authorisation that can be shared between resources. But if not, you can change the last two tables into:

    groups_user_types_resources
    ---------------------------
    resource_id|group_id|user_type_id
              1|       1|           1
              1|       1|           2
              1|       2|           1
              2|       1|           1
              2|       1|           2

Again, this depends on how strong groups_user_types is and if those permissions are shared between resources or not. Are the permissions for resource 1 the same entity that the same permissions for resource 1. Will you have lots of resources with the exact same permissions? Are there permissions that won't be allowed? Only you can answer that.


Switching values in a column with one update statement

You want to use a CASE expression of some type.

In SQL Server the code would look like this:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  WHEN gender = 'W' THEN 'M'
                  ELSE gender END

Edit:   As stated in the comments (and some of the other answers) the ELSE isn't necessary if you put a WHERE clause on the statement.

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  WHEN gender = 'W' THEN 'M' END
WHERE gender IN ('M','W')

This avoids unnecessary updates.  The important thing in either case is to remember that there are options other than M & W (NULL for example) and you don't want to put in mistaken information.  For example:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  ELSE 'M' END

This would replace any NULLs (or other possible genders) as 'M' which would be incorrect.

A couple of other options would be

/*Simple form of CASE rather than Searched form*/
UPDATE TableName
SET    gender = CASE gender
                  WHEN 'M' THEN 'W'
                  WHEN 'W' THEN 'M'
                END
WHERE  gender IN ( 'M', 'W' );

And a more concise

/*For SQL Server 2012+*/
UPDATE TableName
SET    gender = IIF(gender = 'M', 'W', 'M')
WHERE  gender IN ( 'M', 'W' );


Definition of approach to store temporarily unknown data

Option A is similar to what BI developers have dealt with in terms of incomplete data. In our case it's called a late arriving dimension and accommodates the fact that we often times get incomplete data, that is nevertheless important for immediate storage and analysis.

You can implement some form of the following: Instead of leaving NULLs create a default record in related tables so you can make inserts immediately with as much information as you have and later execute minimal updates without breaking your referential integrity design. Product would have a default or unknown row, and this would be used whenever you have no idea what product is coming in. Shipment maintains an FK to Products and once you know the product you can update Shipment with a new FK to Products. You may wish to add date columns to Shipment so you have insight into when these updates occur.

If you have incomplete product information, but enough to tell you've never seen it before (e.g. UPC but no color) do not use the default/unknown row. Insert a new product as you've described using defaults in the Product table, but generate a new PK. Later when your information arrives you should only need to update the product table - since that record has already been created and referenced elsewhere, you can perform minimal updates when your information finally arrives.


Postgres FATAL: could not open relation mapping file

I had the same error when I tried to reinstall PostgreSQL on RHEL6 using a Chef cookbook. In my case this seems to have been due to an incorrect manual uninstallation of PostgreSQL. I used this command to find the offending files that disrupted my installation:

find / -user postgres

And then I deleted all the files I found as they were owned by postgres and I did not even have a postgres user. To be on the safe side I also rebooted the machine and after that it worked like a charm.


Pivoting Dynamic Rows into Columns in MySQL

You must add a GROUP BY and an aggregate around the CASE statement(s). There is no PIVOT operator with MySQL.
Query:
SELECT sa.Student_Id
    , sa.Attendance_Date
    , MAX(CASE WHEN sa.Attendance_Date = '20160203' Then sa.Attendance_Status END) As '20160203'
    , MAX(CASE WHEN sa.Attendance_Date = '20160204' Then sa.Attendance_Status END) As '20160204'
    , MAX(CASE WHEN sa.Attendance_Date = '20160205' Then sa.Attendance_Status END) As '20160205'
    , MAX(CASE WHEN sa.Attendance_Date = '20160206' Then sa.Attendance_Status END) As '20160206'
 FROM student_attendance sa
 INNER JOIN courses c
     ON c.Course_Id = sa.Course_Id
 INNER JOIN students es
     ON es.Student_Id =  sa.Student_Id
 WHERE sa.Course_Id = 1
 ;


How does Yelp efficiently calculate distance in the database?

If I understand the question correctly (and I'm not sure I do), you are worried about computing "(Some formula to compute distance here)" for every row in the table each time you do a query?

This can be mitigated to a degree by using the indexes on latitude and longitude so we only have to compute the distance for a 'box' of points containing the circle we actually want:

select * from business
where (latitude>96 and latitude<116) and
      (longitude>-5 and longitude<15) and
      (Some formula to compute distance here) < 2000

Where 96, 116 etc are chosen to match the unit of the value '2000' and the point on the globe you are calculating distances from.

How precisely this uses indexes will depend on your RDBMS and the choices its planner makes.

In general terms, this is a primitive way of optimising a kind of nearest neighbour search. If your RDBMS supports GiST indexes, like postgres then you should consider using them instead.


Anyway to identify mysql slaves have read upto which binary logs

The easiest method is the following

Go to each Slave and run SHOW SLAVE STATUS\G
Look for Relay_Master_Log_File on Each Slave
Whichever Slave has the oldest Relay_Master_Log_File is the one you purge to on the Master

Why Relay_Master_Log_File ? First Look at SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000255
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

You see Master_Log_File and Relay_Master_Log_File

In this display they are different. So why choose Relay_Master_Log_File ?

Master_Log_File represents the binlog with the last binlog event downloaded to the Slave
Relay_Master_Log_File represents the binlog with the last binlog event executed on  the Slave

In the event of any replication lag, Relay_Master_Log_File is always the oldest. If they are the same, fine. You choose Relay_Master_Log_File always.

In this case, you purge to mysql-bin.000254 on the Master. In the event the SQL thread dies, you don't want to erase binlogs from the Master the Slave has not processed yet.

Getting back to multiple slaves, you choose the oldest Relay_Master_Log_File of all Slaves.

To clarify, the oldest Relay_Master_Log_File is the binary log name with the lowest number.


Select and sum data from a oracle table containing duplicates

You are almost right, just add below date check for 24 hours.

SELECT t3.rec_id AS"Id", COUNT(t3.rec_id) AS"Num messages"
FROM TABLE3 t3 INNER JOIN TABLE2 t2
ON t2.id=t3.rec_id
INNER JOIN TABLE1 t1
ON t2.id=t1.id
WHERE t2.active='1'
  AND t1.block='0'
  AND t1.valid like 'false'
  AND t1.block_date < sysdate - 1
GROUP BY t3.rec_id
ORDER BY COUNT (t3.rec_id) DESC


Use parameters, then only the $1 etc. markers are visible in pg_stat_activity.


Is there a way to find the partition or subpartition name where my ID belongs

First get rowid of the row and object id of the object using DBMS_ROWID.

SQL> insert into t3 values('S00102981655537O',sysdate,'IN-RJ');

1 row created.

SQL> select dbms_rowid.rowid_object(rowid) from t3 where doc_id='S00102981655537O';

DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
             93178

SQL> select subobject_name from user_objects where data_object_id=93178;

SUBOBJECT_
----------
SYS_SUBP81

SQL> select * from t3 subpartition(SYS_SUBP81);

DOC_ID           DOC_DATET STATE
-------------------- --------- ----------
S00102981655537O     31-MAR-16 IN-RJ

As we can see that row belongs to SYS_SUBP81 subpartition.


MySQL: logging queries which would execute without using indexes

I'm not really sure why you expected log_queries_not_using_indexes=1 to not log queries that didn't use an index. The idea of the setting is to see which queries are getting run on your server that could be ideal candidates for optimization. For your example:

SELECT id_test from test where id_test != 69

Unless you change the requirements of the query, there's not much you can do. Of course it's faster to do a full scan and return all rows that don't match that id_test value.

As for ideas on how to approach analysis:

You can set min_examined_row_limit to a higher value. If, for example, you want to exclude queries that examine less than 5,000 rows, set this variable to 5000. This is useful to reduce the 'noise' in the slow query log.
You can use a tool called pt-query-digest with the --review option to review your slow queries. The tool allows you to store your review history so you don't repeat work on the same query during analysis:

  It won’t show you queries you’ve already reviewed. A query is considered to be already reviewed if you’ve set a value for the reviewed_by column. (If you want to see queries you’ve already reviewed, use the --report-all option.)

It also has the added benefit of storing a history of the queries that get logged in your slow query. Who wouldn't love historical evidence of improving your queries?


Why does MYSQL use blocking locks instead of optimistic attempts that fail immediately?

"The client can always retry" - yes it can. However, the transaction could have performed an arbitrarily large amount of work before the read conflict was detected, which would all have to be re-done on a re-try with no guarantee of success the second time. It may even face starvation if other transactions again get to the locked resource before it does. By blocking instead of failing the transaction goes into a queue for the shared resource, guaranteeing it will eventually get to make progress.
"The client has to wait .. in both cases" Let's consider two scenarios where the other transaction that is locking the desired resource is either very short-lived or very long-lived. In the former the lock is removed quickly and if our transaction is blocked we can quickly progress once more. Had we failed we would have had to re-do all our existing work (which may have been little or lots, who knows) before we could progress. With a long-lived blocker we may get back to requesting the lock only to find it is still held. Then we fail again and repeat. Now we're in a busy-wait loop, burning system resources but making no progress. Or we may get back to requesting the resource to find it is free. Then we can make progress, but are no better off than had we blocked and queued.
"committing changes in order" - what order? When they're submitted? Then my really fast one-row write has to wait behind your 4TB bulk load even though there are no rows in common? That's not good for latency.
There are a class of protocols called multi-valued concurrency control dealing with these scenarios. They excel at some workloads and suffer under others. I enjoyed the Carngie Mellon series on this, lectures 3-5 specifically.


Is this relation in third normal form (3NF)?

In the end whether the table is 1NF and 3NF depends on what the domain value of a city is.   This answers whether or not you have atomicity.

In essence what normalization means in the abstract regarding address information is quite controversial and it is not an easy area to answer and so I personally believe that this must be addressed on a case by case basis, based on the semantic use of the data.

Otherwise consider the following addresses:

418 N Bradley St
Chelan, WA 98816

How many atomic fields would this require with maximum decomposition?  So we consider this to be:

CREATE TABLE address (
    id int not null unique,
    street_segment char(1),
    street_name varchar not null,
    city_name varchar,
    state_province varchar,
    mail_code
);

That seems straight forward enough until you get an address that requires a cross-street as is common in South America, then we start to add fields. But now you have to track an address in Managua like:

Bo Altagracia km 3 1/2 carretera sur | Montoya 3c al Oe

Note that this is an ordinal address based on landmarks, distance and direction, and it does not fit into this data model at all and a normalized representation would require at least one additional table and some additional fields, and we can no longer enforce the same not null constraints.

But then this doesn't necessarily mean there is anything breaking 1NF if we just store the address as a text string up through and including the country.

If you have no reason to track countries, then one may see "London, England" as a valid representation of a city designation and then yes, we could see this as 1NF.  In essence such a table has a city domain (where "London, England" is distinct from "London, UK" and "Paris, France" is distinct from "Paris, North Carolina").  This gives you a number of possible issues, but it does not pose classic normalization problems unless you need to track countries (here the country is just an incidental part of the city's domain value, not a domain in itself).

So in this case it may or may not be 1NF depending on how you define the domain of the address to be and what you have to track it relative to.  If it is 1NF then it is also 3NF.

However if you break city and country into separate fields, then you have a normalization problem because city is dependent on country, and so to be truly 1NF you'd probably need to break out your regional hierarchies quite a bit more (a cities table, a state/province table, a country table).

A typical approach to this is exactly what you have done, which is to treat the address as a text string atomic domain, and not break things out any further.

Edit:  Another example of 1NF problems using arrays

Suppose on PostgreSQL I am storing IP addresses and will be querying on octets.  I might represent an IP address as a smallint[] array like:  array[192,168,1,101,24] instead of a cidr representation like '192.168.1.101/24' and this does not break 1NF.  Each array of smallints is distinct in its domain and each represents a single value of its domain.  This does not break 1NF because in the domain of an IP address, each array represents a single value in its domain (and this is ensured by the fact that ordinality is important).  This is a good example of why it is wrong to assume, for example, that the inclusion of complex data structures or arrays necessarily violates 1NF.

Finally if "this datatype can be decomposed and therefore it isn't atomic" breaks 1NF then so does every use of a datetime datatype....

TL;DR

1NF's atomicity requirement is violated only when the column stores two or more values within a given domain.  That is not the case in the initial question so 1NF is not violated given the information shown.  Given that 1NF is not violated and both name and address are functions of customerid, and not functions of eachother, the requirements of 3NF are met.


What is the best query to get the latest row version based on time?

There is a 3rd option, using window functions, either max():

with latest as
(select *,
        max(inserted_day) over (partition by hostname)
          as max_inserted_day
 from age_data
)
select *
from latest l
where inserted_day = max_inserted_day ;

or rank():

with latest as
(select *,
        rank() over (partition by hostname order by inserted_day desc)
          as rnk
 from age_data
)
select *
from latest l
where rnk = 1 ;

If there are ties (rows with same maximum inserted_day for a hostname), all the above queries (and yours) will return all the tied rows. If you want to resolve ties (and return only 1 or only 2 rows), you can replace rank() with row_number() and change the order by clause inside the over according to the requirements.

Regarding efficiency, an index on (hostname, inserted_day) will help, in all the queries.


Only sysadmins can manage the instance and by default Administrators are not members of the sysadmin role.

You need to restart your instance in single user mode. Here is an article on Technet that describes how to do it: Connect to SQL Server When System Administrators Are Locked Out

Basically it boils down to "restart the instance with the -m switch"

Another way to achieve the same is described in this scary post by Argenis Fernandez. In this case, he impersonates the service SID of the SQL Server instance in order to gain access as a sysadmin and add his user name back to the syadmin role.


What's the difference between WITH CTE & WITH CTE ()?

You nearly have the answer for one of your questions already.

In the MSDN page, there is a line directly after your quote that explains this:

  The basic syntax structure for a CTE is:

  WITH expression_name [ ( column_name [,...n] ) ]

  AS

  ( CTE_query_definition )

  The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

(Emphasis added)

This would mean that you would need to specify column names in a few situations:

This would work:

WITH [test_table] ([NoName], [CAST], [Function])
AS
(
    SELECT
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];

as would this:

WITH [test_table]
AS
(
    SELECT
        1 as [NoName]
      , CAST('1' AS CHAR(1)) as [CAST]
      , dbo.CastToChar(1) as [Function]
)
SELECT * FROM [test_table];

But this would not as it does not have distinct names for the columns:

WITH [test_table]
AS
(
    SELECT
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];


Create DB snapshot without specifying the FILENAME attribute

Dynamic SQL is probably your best bet.
See 3rd example here:
https://www.sqlservercentral.com/scripts/create-database-snapshot-dynamically


MySQL queries taking very long/not finishing

Few points here:

"Cartesian product" is not simple :)
In your specific query, and based on the index you have, the time will highly depend on the distribution of the data. Try this query: SELECT COUNT(*) FROM table WHERE column2=''; I guess the result is not a small number.
UNIQUE entry (column2, column3, column4, column5); is not healthy. The index is very big. It affects both, reads and writes. 'Reads' because it has to be loaded into the memory when it is used, and 'Writes' because it has to be checked (and maybe updated) whenever a DML operation is executed. If this uniqueness is a must, I suggest adding an MD5 hash for all the fields, and have that field uniquely indexed.
To get smaller result, avoiding repetition, you may add WHERE... AND a.column1<b.column1. This will avoid you having:

1, 4
4, 1