Database design to track student attendance

To expand more on @Twinkles alternative, I'm concerned about why you're storing present and absent students in your attendance data. Is it because you want to be able to run a report that shows all kids and whether they were absent on a given day? There's a better way to do it.

Most systems I've seen store either the that the student is present (Positive attendance) or the student is absent (Negative attendance, although rarely referred to by that name). The decision on which to use came down to the structure and reporting requirements of the school (and whoever they report to).

If you're school is concerned when the student is absent for reasons like having to track Truant students, you should use a standard (negative) attendance structure. Most K-12 schools in the US  do it this way.

If you're school only awards credit for courses after a verified number of hours have been completed (e.g. Johnny doesn't pass the course until he's completed his coursework and attended 12 sessions of the class), then a positive attendance structure could be used.

Assuming your data looked like this: (Ignoring the course_id in your example for simplicity)

Attendance:
id | student_id | date       |
------------------------------
1  | 2          | 2013-10-24 |

Students:
student_id | student_name   |
-----------------------------
1          | Johnny Johnson |
2          | Bobby Tables   |
3          | Suzie Smith    |

To get a report of all the kids at your school and their attendance status for the day, you could run a query like:

SELECT s.student_id, s.student_name
,  CASE WHEN a.id IS NOT NULL THEN 'Absent' ELSE 'Present' END as attendance_status
FROM Students s
LEFT JOIN Attendance a on s.student_id=a.student_id AND a.date = '2013-10-24'

Which would return:

Result :
student_id | student_name   | attendance_status |
-------------------------------------------------
1          | Johnny Johnson | Present           |
2          | Bobby Tables   | Absent            |
3          | Suzie Smith    | Present           |


Application specific users table or mysql.users

You want a feature like Oracle Virtual Private Database. Unfortunately MySQL does not have this. There is a similar question with answers at https://stackoverflow.com/questions/5527129/mysql-how-to-do-row-level-security-like-oracles-virtual-private-database.


Lob Logical Read and lob read-ahead reads in NCCI

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?

There is one LOB physical read and 1,877,324 read-ahead reads. Read-ahead is still a physical read, just performed in advance (prefetching). The quote from Redgate is incorrect.

  How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?

Logical reads count the number of times a page in memory was touched. The reduction is from 469,697 (cold cache) to 229,248 (warm). I don't have a complete explanation for this, but it may be in part because columnstore data is cached contiguously in a separate columnstore object pool, rather than the general page-sized buffer pool.

Read-ahead reads pages from the upper levels of a b-tree to identify pages to read in advance of the scan, which may also result in a varying number of 'extra' logical reads, depending on timing and the characteristics of the storage system. Columnstore indexes have a b-tree structure on persistent storage.

Another factor is parallelism (it seems your test was run at DOP 24) since overlapping requests from different threads can result in additional logical reads. You may find the excess logical reads reduce somewhat if you run with OPTION (MAXDOP 1).

Internal storage details for columnstore are not well-documented, nor fully supported by the usual DMVs. At this stage, I would say the best explanation for the reduction in reads between cold and warm cache cases is the difference between on-disk and in-memory (cached) storage.

Columnstore data (dictionaries and segments) are stored at LOBs on disk, but are not held in memory in the same format, for performance reasons. Because they are different things, you can't just add logical reads to physical reads to get a meaningful result.


Calculating the maximum seen so far for each point in time

If you need a rolling max you can use both CROSS APPLY or LEFT JOIN, here is the CROSS APPLY version

SELECT Product, Date, Price, b.[Max Price PIT]
FROM   Table1 a
       CROSS APPLY (SELECT MAX(Price) [Max Price PIT]
                    FROM   Table1 b
                    WHERE  a.Product = b.Product
                      AND  a.Date >= b.Date) b

SQLFiddle demo

With SQLServer 2012 or better it'll be possible to use the new ROWS clause to get the same effect

SELECT Product, Date, Price
     , MAX(Price) OVER (PARTITION BY Product ORDER BY [Date]
                        ROWS UNBOUNDED PRECEDING) [Max Price PIT]
FROM   Table1

The key point is to get all the rows before the current for the group and apply the statistical function only to them, sometime this type of presentation is called "rolling", for example rolling sum/total if you get the total value of cash till the current row.


SQL Server Agent logging actions and changes done to jobs

For sake of simplicity, I will assume that you want to track sysjobs, sysjobsteps and sysjobschedules. There may be other tables you want to monitor.

Option 1: SQL Audit (requires Enterprise Edition)

USE [master]
GO

-- Audit
CREATE SERVER AUDIT [jobs]
TO FILE
(   FILEPATH = N'PathToSomeFolder'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e807469a-6c9d-43f1-af46-cf7b89ba898d'
)
ALTER SERVER AUDIT [jobs] WITH (STATE = ON)
GO

USE [msdb]
GO

CREATE DATABASE AUDIT SPECIFICATION [job_changes]
FOR SERVER AUDIT [jobs]
ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobsteps] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobschedules] BY [public])
WITH (STATE = ON)
GO

The data captured by the audit appears like this:

Option 2: Extended Events session

-- Step 1: extract object_id for the following tables
SELECT object_id
from sys.tables
WHERE name IN ('sysjobs','sysjobsteps','sysjobschedules');

-- Step 2: use those object_ids in the following session:
CREATE EVENT SESSION [capture_job_changes] ON SERVER
ADD EVENT sqlserver.lock_acquired (
    SET collect_database_name = (0)
        ,collect_resource_description = (1)
    ACTION(sqlserver.client_app_name, sqlserver.is_system, sqlserver.server_principal_name)
    WHERE (
        [package0].[equal_boolean]([sqlserver].[is_system], (0)) -- user SPID
        AND [package0].[equal_uint64]([resource_type], (5)) -- OBJECT
        AND [package0].[equal_uint64]([database_id], (4))  -- msdb
        AND (
               [object_id] = 1125579048 -- sysjobs
            OR [object_id] = 1269579561 -- sysjobsteps
            OR [object_id] = 1477580302 -- sysjobschedules
        )
        AND (
               [mode] = (8) -- IX
            OR [mode] = (5) -- X
        )
    )
)
WITH (
     MAX_MEMORY = 20480 KB
    ,EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY = 30 SECONDS
    ,MAX_EVENT_SIZE = 0 KB
    ,MEMORY_PARTITION_MODE = NONE
    ,TRACK_CAUSALITY = OFF
    ,STARTUP_STATE = OFF
);
GO

-- Step 3: add a convenient target to the session (file target?)

The data in the XE session appears like this:

Regarding this second option, I wrote a blog post on a similar subject (tracking object usage) where I describe the details of the technique. Basically, you can consider IX/X locks as updates to the underlying tables.

This session captures the bare minimum, but you can add more fields/actions to it to capture the sql text or the computer name or whatever makes sense for you.


Is this a normal/common approach to solving this kind of problem?

Yes it is a common approach (though not common enough, perhaps).

  Why do I have to help out the optimizer in this way?

Primarily because the optimizer is cost-based, and relies on cardinality estimations (expected number of rows) as a main input to that calculation.

Assuming the right statistics are available, and they are reasonably up-to-date, estimates on base relations may be quite accurate. These derived estimates are the input to the next operation (for example, a join), which produces another cardinality estimation as its output, and so on. The nature of this process (and the statistical inputs) means that the accuracy of the estimations will tend to degrade as derivations continue up the tree. Poor estimations lead to poor cost-based choices, and other side-effects such as an insufficient memory reservation for a hashing or sorting operation.

Materializing intermediate results (using temporary tables, or another method) gives the optimizer a new starting point, perhaps with new statistics automatically built on the materialized data, and maybe even with helpful indexes. These considerations mean that simpler queries (using operations with accurate estimation logic) tend to produce higher-quality execution plans.


What is difference between primary index, secondary index, unique index and normal index?

Primary index

A primary index is an index on a set of fields that includes
the unique primary key for the field and is guaranteed not to contain duplicates.
Also Called a Clustered index.
eg. Employee ID can be Example of it.

Secondary index

A Secondary index is an index that is not a primary index and may have duplicates.
eg. Employee name can be example of it. Because Employee name can have similar values.

Dense Index

Index record appears for every search key value in the file.
Dense indexes point directly to individual records.

Sparse index

contains index records for only some search key values.
Applicable when records are sequentially ordered on search key.
Just as with book indexes, sparse database indexes don’t point to individual records, but to ‘pages'


How to add Subpartition to all partitions

You can just easily loop through the partitions in a PL/SQL loop, and add the subpartitions:

begin
  for p in (select partition_name from user_tab_partitions where table_name = 'PART_TEST')
  loop
    execute immediate 'alter table part_test modify partition ' || p.partition_name || ' add subpartition ' || p.partition_name || 'AXCS values (''AXCS'')';
  end loop;
end;
/


Stop on error in PL/SQL developer or Oracle SQL Developer

When you process a script in SQL Developer using the "Run Script" button (not the "Run Statement" button), it uses SQL*Plus internally to execute your script.  Therefore using a SQL*Plus control setting which stops processing on errors should work. Try adding to the top of your script line like this:

WHENEVER SQLERROR EXIT FAILURE


Create an Audit trail on SQL Server 2008 to trace all privilege users

You might be looking for something like Change Data Capture.

  Change data capture is designed to capture insert, update, and delete
  activity applied to SQL Server tables, and to make the details of the
  changes available in an easily consumed relational format. The change
  tables used by change data capture contain columns that mirror the
  column structure of a tracked source table, along with the metadata
  needed to understand the changes that have occurred.

  Change data capture is available only on the Enterprise, Developer,
  and Evaluation editions of SQL Server.


passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb

Unfortunately, sp_ineachdb was not designed with this specific use case in mind (nested dynamic SQL with parameters), and I don't know if you can do all you want to do with this script without any dynamic SQL at all. I think this approach will be a bit simpler - you'll have to call sp_ineachdb twice, but the dynamic SQL portion is much, much simpler, and you won't have to relay parameters for the procedure to in turn pass along to its own internal sp_executesql call.

DECLARE @indexname nvarchar(128) = N'uc_emailAddress',
        @cmd       nvarchar(max) = N'SELECT db = DB_NAME(),0;';

DROP TABLE IF EXISTS #radhe;

CREATE TABLE #radhe
(
  dbname sysname,
  indexname nvarchar(128),
  the_index_exist_here bit
);

INSERT #radhe(dbname,the_index_exist_here)
EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;

UPDATE #radhe SET indexname = @indexname;

SET @cmd = N'UPDATE r SET the_index_exist_here = 1
    FROM #radhe AS r
    INNER JOIN sys.indexes AS i
    ON r.dbname = DB_NAME()
    AND i.name = r.indexname;';

EXEC master.dbo.sp_ineachdb @cmd;

SELECT dbname, the_index_exist_here FROM #radhe;


After having a nap I found solution by myself.

The Event Scheduler was not running on the slave machine.

SET GLOBAL event_scheduler = ON;

did the trick.

It's running now.


Optimize query to not read all rows in table

Try this:

CREATE INDEX ix_bot_message_queue_status ON bot_message_queue (status, priority desc, id, botId, chatId);
select q.id
from bot_message_queue q
where
              q.status=0
              AND
              (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId)
              OR EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId AND w1.retry_after < 1583743366))
              AND
              (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId)
              OR EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId AND w2.retry_after < 1583743366))

order by q.priority DESC,q.id ASC
limit 1;

you may need to use a trick with generated column if your MySQL version doesn't support descending indexes


BIGINT(15) or even BIGINT(18) might not be long enough to store all possible phone numbers. Depending which country and if a PBX is involved 18 digits are not going to be enough. I have seen quite a number of longer ones already. Even so E.164 recommends to not assign phone numbers with more than 15 digits to phone providers world wide, not all of them adhere to these standards.

I am not sure where exactly you are heading with your database, but if you want to store phone numbers I would strongly suggest to use a character based format.
In most applications that really use phone numbers you might not only want to store them but also use them. For using a phone number you might need some transformations to add, change or replace prefixes, or maybe filter for certain prefixes, since international phone numbers do not have all the same length it is much easier and faster on the database to use a character based format like CHAR(32) or VARCHAR(32). If speed of character based filter operations (number pattern, prefixes, areacodes, etc) is important for you the CHAR typ will usually outperform VARCHAR but you will have to clean up trailing spaces.
Internationally there are two common ways to realize direct in dial lines. I would call them "direct dial" and "post connection dial". Any direct dial number can be put into the "regular" number field since they can be dialed in one succession with the carrier assigned number. The other type usually requires a pause before continuing to dial the extension (like in most smaller US legacy PBX systems) these "post dial digits" should go into a separate field, which for better design should be nullable.
I would not use the E.164 extension format that you mentioned, it is so rarely used that in myself working in the industry for well over 10 Years I have never seen it in production.


How to use SET operation in CASE clause in SQL?

Based on your comment, you seem to be looking for a way to avoid repeating the same calculation over and over again.

You could use APPLY VALUES to calculate the required values once, then use the aliased column in the rest of your calculations.

DECLARE @Declining FLOAT,
@StableStart SMALLINT,
@StableEnd SMALLINT,
@Increasing SMALLINT

SELECT  @Declining = 0.90,
@StableStart = 90,
@StableEnd = 110,
@Increasing = 110

DECLARE @TrendValue FLOAT;

UPDATE R
SET
 R.TtlNOfCmpSaleSpanFirst =
            CASE
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendFirst > @Increasing THEN 0
                        WHEN Trends.TrendFirst >  @StableStart AND Trends.TrendFirst <  @StableEND THEN 1
                        WHEN Trends.TrendFirst < @Declining THEN 2
                  END
            END,
 R.TtlNOfCmpSaleSpanSecond =
            CASE
                WHEN R.TtlNOfCmpSale4to6!= 0 THEN
                  CASE WHEN Trends.TrendSecond > @Increasing THEN 0
                        WHEN Trends.TrendSecond >  @StableStart AND Trends.TrendSecond <  @StableEND THEN 1
                        WHEN Trends.TrendSecond < @Declining THEN 2
                  END
            END,

 R.TtlNOfCmpSaleSpanThird =
            CASE
                WHEN R.TtlNOfCmpSale7to12 != 0 THEN
                  CASE WHEN Trends.TrendThird > @Increasing THEN 0
                        WHEN Trends.TrendThird >  @StableStart AND Trends.TrendThird <  @StableEND THEN 1
                        WHEN Trends.TrendThird < @Declining THEN 2
                  END
            END

FROM Result R
CROSS APPLY (
    VALUES (IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale4to6*2/R.TtlNOfCmpSale7to12, 0)
              IIF(R.TtlNOfCmpSale4to6 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale4to6, 0)
              IIF(R.TtlNOfCmpSale7to12 <> 0, R.TtlNOfCmpSale0to3/R.TtlNOfCmpSale7to12, 0)
) AS Trends(TrendFirst, TrendSecond, TrendThird);


How to drop a procedure across a lot of databases (SQL Server 2016)

You were very close, but forgot a small thing :

execute sp_msforeachdb 'USE [?] drop procedure Usages'

The use [?] will actually go to each db, and execute it there, this should solve your issue.
You could however add something that firsts checks if the procedure exists before dropping it, cause now you'll still get the error if it doesn't exists on the database.

Edit:
A cleaner version would be this:

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
USE [?]
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N''Usages'')
                    AND type IN ( N''P'', N''PC'' ) )
drop procedure Usages
'

EXEC sp_MSforeachdb @SQL

It'll check if it exists before dropping, so if there's so such sp, it won't give you an error.

EDIT2:
ElliotMajor made the comment

  I would note that sp_msforeachdb is undocumented and shouldn't be
  used. Plenty of known bugs

And he's right about that. For what you're doing right now it should be fine, but if you're ever in need to put this in some production code, there's a couple replacements for this sp.

Improved versions by :Spaghetti DBA and Aaron Bertrand


Materialized view logs — with condition?

MV logs are all or nothing; they have no visibility of criteria for any given MV. Any MV Log records will be purged up to the oldest common refresh date as soon as the source DB determines that all registered MVs have refreshed.


In most situations, the recalculation of an expression is much less of a performance burden than any solution.  So, don't worry about it.

You have another issue -- The GROUP BY does not include the first thing in the SELECT, so the value you will get for that is unpredictable.


Innodb mutex locking ?!? Inserts start stacking after 18-24hours, restart required

Similar to my problem if you have the performance_schema enabled, solved after the upgrade of MySql server version.

Starting from 5.5.16 to 5.5.22 is affected by a deadlock.
No queries can be run when it happens:
Bug 13898343 - THREAD LOOPS ENDLESSLY IN LF_PINBOX_PUT_PINS WHILE HOLDING LOCK_THREAD_COUNT

An infinite thread loop could develop within Performance Schema,
causing the server to become unresponsive. (Bug #13898343)

See changelog

First of all try disabling performance_schema

Hope his helps


returning a null value instead of no value

Your last 2 SELECTs are without a FROM clause. These will always return a single row of scalar values.
The fact that the subquery did not return any rows, caused the expression to be NULL - unknown.
Think about the following statement - how would you expect the engine to handle this:

 SELECT 1 AS FirstCol, (<some SELECT that returns no rows>) AS SecondCol

Note: This is a non standard T-SQL extension. In Oracle for example, you need to add the FROM DUAL or you will get ORA-00923: FROM keyword not found where expected. However, this same statement with FROM DUAL added run in Oracle will also result in a single row of scalar values.


MySQL tar.gz backup keeps getting bigger but MySQL folder size is the same

You are backing up the raw files, not a "dump" of the rows (via mysqldump or Xtrabackup), correct?

When you DELETE, the rows are held onto in case of a ROLLBACK (explicit or due to crash).  This may increase the file size.

Data (and index) files never shrink in MySQL without explicit action.  In particular, there is no way to shrink ibdata1 without dumping and reloading.

So...

Plan A:  Use mysqldump or xtrabackup to extract the rows.  Then, if you like, send that through tar and gzip (or whatever).

Plan B:  If the tables are "file_per_table", OPTIMIZE TABLE to shrink the .ibd files before the next dump.  (You are stuck with the bloated ibdata1.)

Plan C:  If not file_per_table, that is, if all the data is in ibdata1, do a one-time dump, rm ibdata1, reload.  This is somewhat tricky and somewhat risky.  Test before risking the production data.

If you will regularly be purging old data, consider using Partitioning as an efficient way to purge old data.  If you intend to keep only 3 months' worth, then partition by week (14 partitions).  Details here.  It involves DROP PARTITION which will not leave behind stuff like DELETE does.

As for why the "steep increase" did not continue to happen...  The deleted rows left "free" space in the tablespace; that was being reused.  For now, your dumps will continue to be about the same size.  (Note:  gzip, alone, will fluctuate in size as the data changes, even if the the input size seems to stay the same.  This is a characteristic of compression algorithms.)


Getting catagory title total number of questions and answers order by number of questions

SELECT @no := @no + 1 AS no
  ,catagory
  ,number_of_questions
  ,number_of_answers
FROM (
SELECT c.title AS catagory
    ,COUNT(DISTINCT q.id) AS number_of_questions
    ,COUNT(a.id) AS number_of_answers
FROM material_univarcity_list_content AS c
LEFT JOIN user_questions AS q ON q.note_id = c.id
LEFT JOIN user_answers AS a ON a.question_id = q.id
GROUP BY c.title ) AS t1
CROSS JOIN (SELECT @no := 0) AS t2
ORDER BY number_of_questions DESC;

Since you use MySQL 5.7 which doesn't support window functions like ROW_NUMBER you need to use user variables to emulate it.


Should I use tempdb or memory optimized table variable?

Parallel Execution:

SQL Server does support parallel inserts into temp tables in SQL Server 2014 with the SELECT INTO syntax. In SQL Server 2016 parallel inserts are also supported into temp tables that are heaps. In SQL Server 2016 SP1 parallel inserts into heaps require the TABLOCK hint.

A query that modifies table variables will not contain any parallel zones. However, a query that references a table variable may run in parallel.

Memory:

Yes, memory-based tables can only be in RAM and cannot spill to disk. See this article.

Performance Boost:

My advice would be to first measure the performance of your stored procedure to confirm that the temp tables are the source of the unacceptable performance. If you confirm that consider the following simpler options first:

Only save data to temp tables that you need. Apply filters when inserting data as soon as possible.
Create columns with the right data types.
Create indexes as appropriate for your temp tables.
Drop your temp tables as soon as you no longer need them.
Make sure that your tempdb database follows best practices. This could be a separate post but here's one resource.

If none of that works and you feel confident that you understand the limits of memory optimized table variables go ahead and test your procedures with them. The previously referenced blog post does mention that as a use case:

  As replacement for traditional table variables, and in some cases for #temp tables that are local to a stored procedure.  This is particularly useful if there is a lot of tempdb contention in the system.


The OPENROWSET documentation describes the SINGLE_CLOB option as:

  returns the contents as a single-row, single-column rowset of type varchar(max)

So you will either need to split the string into multiple columns using the STRING_SPLIT function (or a similar method), use a format file to specify a row terminator for OPENROWSET, or use BULK INSERT, which allows you to specify a row terminator without the need to create a format file.

You could adapt the following code by customizing the FIELDTERMINATOR and ROWTERMINATOR as necessary for your input file. For example, the following code was tested on your provided sample file:

CREATE TABLE #bulkInsert (col1 NVARCHAR(MAX) NULL)

BULK INSERT #bulkInsert
FROM 'C:\Temp\myfile.csv'
WITH (FIELDTERMINATOR = N','
    , ROWTERMINATOR = N'0x0a' /* See https://stackoverflow.com/a/26758288/1582862 */
    , BATCHSIZE = 50000
    , TABLOCK
);

-- The sample rows were correctly bulk loaded, including the header row,
-- which you can remove from your file or filter out after bulk loading
SELECT *
FROM #bulkInsert
--Nummer
--072XXXXXX63
--072XXXXXX76
--07XXXXXX66
--072XXXXXX4


Does Percona Xtrabackup support RedHat release 4

Percona XtraBackup 2.2 does not seem to have support out of the box for Red Hat 4:

  Supported Releases

  The CentOS repositories should work well with Red Hat Enterprise Linux
  too, provided that yum is installed on the server.

CentOS 5 and RHEL 5
CentOS 6 and RHEL 6
CentOS 7 and RHEL 7
Amazon Linux AMI (works the same as CentOS 5)

Try, however, first the generic Linux binary and see if you can at least run the xtrabackup binary.

If not, you can either try to compile it by yourself (it shouldn't be too difficult in terms of dependencies, but Xtrabackup is a monster source and you may en up in dependency hell) or hire their paying support/development so they can make it work for you (they have great engineers).


inserting multiple values into SQL with %

The % operator takes a string on its left side, and a list of values on its right side:

execute("SELECT ... WHERE B_MONTH = '%s' AND B_DAY = '%s'" % (currentMonth, currentDay))

Please note that using % introduces the risk of SQL injections when used with strings, so you should always use SQL parameters instead:

execute("SELECT ... WHERE B_MONTH = ? AND B_DAY = ?", (currentMonth, currentDay))


Personal Finance App Monthly Reports Database Design ( SQLite )

From what I'm understanding you need to design a database which will be record transactions of a user and draw some conclusion like a profit/loss at the end of a month. To achieve this you don't necessarily need to create a wallet every month this is just basic accounting. You will need Transaction table and Transaction_History_Log and Charts_Of_Accounts for more detail check this. Your Transaction table will have a one-to-many relationship to your Transaction_History_Log and Transaction_History_Log will have many-to-one with your Charts_Of_Accounts. So just basically you will need to record journal entries this will help you get a more understanding about what journal entries are in accounting. You can set a createDate attribute in your Transaction_History_Log to just get the a snapshot of month or a whole year or whatever the user likes.


Problem adding a new node to a SQL Server 2012 Failover Cluster

As per your comments in your question, what you need to do is two-fold:

Add SQL Server Agent as a cluster resource type
Add the SQL Server Agent cluster resource to the failover cluster resource group

For the first step, one way to do this is through PowerShell (utilizing the FailoverClusters module):

Import-Module -Name FailoverClusters

$ClusterName = "YourClusterName"
$FciClusterGroupName = "SQL Server (MSSQLSERVER)" # may need to modify to reflect your environment

# check to see if you have the SQL Server Agent cluster resource type
Get-Cluster -Name $ClusterName |
    Get-ClusterResourceType |
    Where-Object {$_.Name -eq "SQL Server Agent"}

# if not, add it
Add-ClusterResourceType -Name "SQL Server Agent" -Dll "sqagtres.dll"

Execute each of those blocks of code separately instead of just all at once.  You'll be able to confirm whether or not that cluster resource type exists before just blindly attempting to add it.

Then for the second step, here's what you need to do to add the SQL Server Agent as a resource in your failover cluster resource group:

Right-Click on your FCI resource group within the failover cluster manager
Select "Add a resource" and select the SQL Server Agent resource type
Rename the new cluster resource to "SQL Server Agent"
Go into the new SQL Server Agent resource properties and set the virtual network name and instance name properties
On the dependency tab, set SQL Server as its dependency resource

You may need to go into the registry at the following location and ensure those keys (one will contain the string "AGENT", another "SQL_ENGINE") are set to 1 (this is where it is recorded on what was installed correctly.  1 denotes correct installation):

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\YOURINSTANCE\ConfigurationState

That should get you to a place where you can now install an additional node and have the SQL Server Agent service account appear.  Of course, prior to starting ensure that you have a back out here (especially with the registry editing).


Why did my UPDATE statement not complete?

Check to see if these statements are blocking each other.  Since they are in the same transaction.  Do they somehow lock conflicting rows?

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p30Days
and OperationType <> 10

update AsyncOperationBase
set deletionstatecode=2
where deletionstatecode = 0 and
statecode = 3 and
completedon is not null and
completedon < @p3Years
and OperationType = 10


SQL Server cluster not available after failure of the "main" server

Without looking at the cluster logs or any other form of error reporting, all I can do is guess here.

But my initial thoughts are that you may have hit the failover threshold.  By default, this is going to be set to a maximum of n - 1 failures (where n is the number of nodes) over a period of 6 hours.  Yes, that's a long time, and especially in a 2-node cluster that isn't very many failures (only equates to one failure).  This threshold is set to prevent the Ping-Pong effect of cluster groups.

In production, this is probably a good thing.  But in testing/development/non-prod it is pretty common to run into this initially perplexing problem, as you may be trying to consecutively failover.  It is worth noting that these parameters are 100% configurable.  All you need to do is go into the properties of the cluster group, and in the "failover" tab you will have the option to change these two parameters (Maximum failures in the specified period and Period (hours).  Here is what this looks like in the Failover Cluster Manager:

Note: in my screenshot, the threshold is set to 2 because I have a 3-node cluster.

Likewise, this can be seen with PowerShell (accessing the FailoverClusters module).

# you may need to set your cluster group name to whatever it is named
# in your environment
#
Get-ClusterGroup -Name "SQL Server (MSSQLSERVER)" |
    Select-Object Name, FailoverThreshold, FailoverPeriod


Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent?

It's probably just database contention.

Doing a parallel dump, you postgres can write to many different files at once, so the load can be spread around.

Doing a parallel restore, postgres has to manage all that [heavy-weight] traffic coming in from jobs+1 connections, all filling up data pages in the buffer cache and writing them to disk, all at once.  To create indexes, you need to read data pages back in and scan them; doing this for lots of tables at once puts a lot of pressure on the buffer cache ...   more work for postgres.

Also, you've specified four jobs - does your database [server] have enough CPUs / cores to support that?  If not then the extra jobs will still get spun up, but will suffer contention at the operating system level, as they get switched in an out of memory as the [poor] machine tries to run them all "at the same time".
On smaller servers, it may be that you get [some] benefit from running with two jobs, but more than that and the benefits are rapidly outweighed by the cost.


Can SQL Server R Services be enabled on a readable secondary?

Readable Secondary is a feature of Always On Availability Groups and you can easily create one of these in Azure using the template from the gallery.

You have to do a little bit of extra config, to configure the read-only routing, something like this:

-- Configure the READ_ONLY_ROUTING_URL
ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-1'
WITH ( SECONDARY_ROLE( READ_ONLY_ROUTING_URL = N'TCP://sqlserver-1.contoso.com:1433') )

ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-0'
WITH ( SECONDARY_ROLE( READ_ONLY_ROUTING_URL = N'TCP://sqlserver-0.contoso.com:1433') )

-- Configure the READ_ONLY_ROUTING_LIST
ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-1'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'sqlserver-0', N'sqlserver-1')))

ALTER AVAILABILITY GROUP [Contoso-ag] MODIFY REPLICA ON N'sqlserver-0'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'sqlserver-1', N'sqlserver-0')))

Connecting to the readable secondary is then a matter of using the ApplicationIntent=ReadOnly facet in your connection string.  I have not tried doing this from R but imagine you could.  There's an easy way to verify whether or not you are connected to the secondary using sqlcmd which I used recently here.

As far as the push compute goes, this is not yet known.  Looking on the Always On Availability Groups: Interoperability R Services is not listed.

I would personally be surprised if these features worked together in that way as R is so new, but it's a good thought.  If it turns out it doesn't work, you could make it a feature request.  I would also say though, when you connect to an AG listener and request read-only routing, it is only that, a request.  As AOAG is a high-availability feature, if the secondary is down, you would connect to the primary.  So let's say you only enabled R Services on the secondary, you create a situation where these scripts failed when the secondary was not available.

Unfortunately I can't test this out myself as I've burned all my Azure credits this month answering some question for @BrentO : )  Maybe later in the month.


SQL Server 2008 R2 Express high insert slowdown possibly due to PAGEIOLATCH_SH

PAGEIOLATCH_SH means a page is read from the disk. There must be more at play in your workload than just INSERTs.

Simply and cheaply put you need a non-Express instance, more RAM and a faster disk. The complex and more expensive answer is you need to fix the application so that it does not read that many pages (ie. it does not do table scans).


Better approach for "LIKE OR LIKE, OR LIKE, OR LIKE, OR LIKE"

Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns.

Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use Elasticsearch or something similar).

For smaller use-cases, computed columns are generally a better approach.

Here's a quick demo setup:

use tempdb

CREATE TABLE #fulltextindexesarestupid (Id INT PRIMARY KEY CLUSTERED, StopAbusingFeatures VARCHAR(100))

INSERT #fulltextindexesarestupid (Id)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (@@ROWCOUNT))
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2

UPDATE #fulltextindexesarestupid
SET StopAbusingFeatures = CASE WHEN Id % 15 = 0 THEN 'Bad'
                               WHEN Id % 3 = 0 THEN 'Idea'
                               WHEN Id % 5 = 0 THEN 'Jeans'
                               END

ALTER TABLE #fulltextindexesarestupid
ADD LessBad AS CONVERT(BIT, CASE WHEN StopAbusingFeatures LIKE '%Bad%' THEN 1
                    WHEN StopAbusingFeatures LIKE '%Idea%' THEN 1
                    ELSE 0 END)

CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #fulltextindexesarestupid (LessBad, Id)

Querying based on even a non-persisted column gives us a plan that 'uses indexes' and everything :)

SELECT COUNT(*)
FROM #fulltextindexesarestupid AS f
WHERE LessBad = 1


Find continuous records with equal values

Or a more traditional approach:

SELECT @ID ID, SUM(a.TAG) r
FROM @tools a
WHERE a.ID Between @ID And ( SELECT MIN(ID) FROM @tools b WHERE b.ID > @ID AND b.TAG = 0 )

The trouble with all these lovely window function-based solutions is that if you write them as pseudo-code you get something like this:

Do something across ALL the IDs, then work out the sequence, then aggregate...

You also can't inject the ID at any point as it invalidates the window range.  Even @JulienVavasseur's solution which uses an ID variable, will scan 999,999 rows in a 1 million row table where the @ID is 2.  Basically these solutions do not scale well.  So if you have 1 million or 1 billion rows in your tools table, well the query will take a long time, 4+ seconds versus 0-50ms in my simple test rig (see below).  Now maybe you might have only 10 rows but it's a bad habit to write code that doesn't scale IMHO.  You might know the volume today but you might not tomorrow.

More traditional set-based solutions can often work best, even recursive CTEs (under certain conditions) and these were the first two that occurred to me:

DECLARE @tools TABLE ( ID INT PRIMARY KEY, TAG INT NOT NULL )

;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO @tools ( ID, TAG )
SELECT rn, CAST( ( rn % 7 ) AS BIT )
FROM cte

DECLARE @id INT = 2

-- Set-based with subquery
SELECT @ID ID, SUM(a.TAG) r
FROM @tools a
WHERE a.ID Between @ID And ( SELECT MIN(b.ID) FROM @tools b WHERE b.ID > @ID AND b.TAG = 0 )

-- CTE
;WITH cte AS
(
SELECT ID, ID AS parentID, TAG
FROM @tools t
WHERE ID = @id
UNION ALL
SELECT b.ID, a.parentID, a.TAG
FROM cte a
    INNER JOIN @tools b ON b.ID = a.ID + 1
WHERE b.TAG = 1
)
SELECT parentID AS ID, SUM(TAG) TAG
FROM cte
GROUP BY parentID
OPTION ( MAXRECURSION 999 );        -- <-- this can be set to 0 but set to lower boundary for safety

Plugging the other solutions into my test rig does not end well for them:

In my tests, my recursive CTE solution (which normally would be thought of unfavourably as RBAR) performs the best with short sequences (< ~10,000), although this dips with really long sequences;  My subquery was best with these.

I would be interested to see if a window-based approach could out-perform either of these two, even with different indexes eg I was surprised not to see a LAG / LEAD solution here.  Does anyone know Itzik? : )


Could anyone explain this code to me？Leetcode #180

But it works in leetcode

Yes. This code is syntactically correct, so it does not produce errors. But it is not deterministic -- two executions of it can return different outputs whereas source data is the same. Forget this code. It is non-deterministic - i.e. it is wrong.
The code is processing order-dependent see online demo.  But ORDER BY clause is absent - so the query is wrong.


Export that table to JSON format (f.ex. MySQL Workbench) and use mongoimport to import it.


SQL Server Express Edition feature and upgrade concerns

You should simply download and use SQL Server 2017 developer edition. Dev edition is same as enterprise edition so regarding feature you would not have any limitation and free to use for testing and learning. Do not use it in production


Choosing shard key with compound index field

You should be able to use the index listed to cover the shard key. It is a super set of your shard key fields.

The shard key listed should be fine for distributing write load, given you don't expect any individual articleId/host pairs to take the bulk of your writes at a given point in time.

I would be concerned about this shard key for reads. In order to target a single shard for a query, you need to include the shard key values. My guess is your queries do not include timestamp. Without timestamp your queries will be sent to every shard which is inefficient. With scatter gather reads, your hamper your ability to scale reads by adding shards.


SQL Server 2005 query erroring with message 7105, slot for LOB data type node does not exist

Read what the bloke (Paul Randal) who wrote some of the associated SQL Server code says

And logged on MS Connect

And have you ran DBCC CHECKDB (KB 2015760)?


MYSQL Master-Slave replication not working with PHP

You have an syntax error in your php code, dbname.tblname and atlast semicolon needed, you had that inbetween

INSERT INTO `database`.`tmdb` (`id`...


Is a join faster with 5 tables joined together Or 5 separate queries?

Look at it this way...  There is  a lot of overhead in each SELECT:

Round trip between client and server
Allocate a thread to work on the query
Parse the SQL
Optimize it
Perform it  (The meat!)
Send the results back

In some experiments, I have seen the "overhead" be 90% of the work.  5 queries takes nearly 5 times the overhead.


Amazing improvement of script execution speed when split - not understood

Your comment confirms that the original long script was using variables and these have become parameters.

Probably you are benefitting from parameter sniffing here and better execution plans. The values for variables are not sniffed unless you use the ’option recompile' query hint so you will get the same plan regardless of what the runtime values are.

When you parameterize it SQL Server sniffs the parameter value at the time the plan for the statement is actually compiled and can look in the column statistics using these actual values. Based on that you can get hopefully better row estimates and a more suitable plan.

As a side note parameter sniffing can have its own problems if the plan is cached and the procedure is executed again with a different parameter value as the original plan might not be optimal for the new parameter value.


but at night it is deleted because of the maintenance task

I would not say deleted as more "overwritten" or "evicted". Maintenance may simply use the memory for other things. Not a big issue normally.

  and so a next day, when customers do transactions, data pages have to be again loaded
  to  Cache, which takes time and slows a lot of things down.

If that is really that brutal the case, you have either an edge case or a hardware problem.

Generally you can preseed the cache by running a number of "maintenance queries" in the early morning (to reload the data into the cache at 0600 in the morning for example) as part of maintenance. I.e. you end it by querying for the data you want loaded into the cache. Problem solved.

But generally this should not result in an EXTREME slowdown - yes, initial requests will be slower, but that should fix itself quite fast as the cache is going to fill up with every query. Is it possible that your experienced slowdown is not "cache" but "morning spike"? There are a lot of systems where at specific times (one of them the morning when people log in and check their work) the load is a lot higher than later during the day. In this case, you need to scale the hardware up to handle this. WOuld be among the first times I would have seen maintenance as a very significant issue.


Why does restoring postgresql database from dump produce errors

pg_dumpall --clean will not issue DROP DATABASE statements for databases like template1 or postgres that have been created by initdb at cluster initialization time.

However it will dump the contents of these databases if you created any objects in it (by default they're empty). So when restoring the result of pg_dumpall --clean into the same instance, it's likely that such errors will occur if these databases happen to have contents.


VMware Socket/Core allocation for SQL Server 2016

I found another blog post discussing this same question:

https://blogs.vmware.com/performance/2017/03/virtual-machine-vcpu-and-vnuma-rightsizing-rules-of-thumb.html

I'll likely need to ask for another VM for testing different configurations, but I think we're going to start with 2 Socket | 8 Core configuration.


Transaction level difference between using a large IN filter VS. "BEGIN TRAN/COMMIT"

From a logging point of view 1 and 2 will be about the same as in both cases you are doing all the deletes within a single transaction.  There will be lots of locking and probably blocking while the delete is being run.  #3 is a single transaction per batch, so users won't be impacted very much and you'll have lots of small transactions in the transaction log instead of one large one.  #2 and #3 should take about the same amount of time to run.  #1 should take less time than #2 and #3 because it's just one command, however the run time will probably still cause problems.

I'd probably want to do something like this.  This will minimize the locking and blocking that needs to happen by only dealing with 1000 rows at a time.

SELECT NULL
WHILE @@ROWCOUNT <> 0
BEGIN
  DELETE TOP (1000) FROM dbo.x
  WHERE ID IN ( /* if possible use "BETWEEN 1 and 100000" instead*/
  1
  ,2
  ,3
  ,4
  ...
  ,100000
  )
END


How to determine the data type of all fields in a result set in SSDT?

You can use the SELECT..INTO syntax to create a table on-the-fly and examine the resultant column definitions in sys.columns.

select
  *
into #SomeName
from <your query goes here>
where 1=0 -- because we only want the metadata

I think sys.columns has definitons for functions, too, but I don't have an instance to hand just now to check.  Alternately you could create a view with the same query you are about to put into your function and check the catalogue for that.


PL/SQL group orders by date and sum the total

You can think of aggregate queries as two parts:

columns that are included in GROUP BY, and
columns that are included in aggregate functions, like SUM(), MAX(), COUNT(), etc.

To put things very simply, if the column is in the SELECT clause, it has to either appear in the GROUP BY or inside an aggregate function.

In your case, you're probably looking for this:

SELECT TEST_DATE,
       COUNT(TEST_ID) AS numberOfTestID,
       SUM(SUCC_ID) AS sumOfSuccID
FROM MYTABS
GROUP BY TEST_DATE;


It's very simple, tempdb database re-creates every time when SQL Server re-started. So, create_date of tempdb database is the date & time when SQL Server restarted/started.

SELECT  case when datediff(mi,create_date,getdate())<1440
        then 'SQL Server got restarted last '+cast(datediff(hh,create_date,getdate()) as varchar)+' hours '+cast(datediff(mi,create_date,getdate())%60 as varchar)+' minutes back. Restart Time is '+Cast(create_date as varchar)
        end
FROM sys.databases where database_id=DB_ID('tempdb')

NOTE: Above query would not return correct result if TF 3609 is enabled in SQL Server

So, in addition to, below query will also help you to find out SQL Serve Start time.

SELECT case when datediff(mi,sqlserver_start_time,getdate())<1440
then 'SQL Server got restarted last '+cast(datediff(hh,sqlserver_start_time,getdate()) as varchar)+' hours '+cast(datediff(mi,sqlserver_start_time,getdate())%60 as varchar)+' minutes back. Restart Time is '+Cast(sqlserver_start_time as varchar)
end
FROM sys.dm_os_sys_info

Thanks


Does it make sense to use OPTION(RECOMPILE) in dynamic SQL?

No.
Now that I got that out of the way, , there's no need to use query hints until there is a need to use them. In other words, don't proactively use them, only reactively use them after you've measurably determined your query's use case will benefit from them.
Now to the specifics. OPTION (RECOMPILE) (and RECOMPILE hints) is useful in edge case parameter sniffing issues where there's no alternative fix, as  discussed in this Kendra Little article. Until you determine you have an otherwise unsolvable parameter sniffing issue with your Dynamic SQL, then there's no need to use the OPTION (RECOMPILE) query hint. In fact it'll only slow your queries down, possibly by an insignificant amount of time, but perhaps not when you have the same query recompiling over and over at a high frequency.
The reason SQL Server caches query plans is to reduce latency on repeated runs of the same query, when possible, by not having to recompile and re-decide on what execution plan to use to serve your queries. Also your Dynamic SQL will already elicit a recompile every time any change is made to it from the last time it ran or when it's no longer in the plan cache, as cached query plans are only re-usable by the same exact query (even a comment change will generate a new plan) since the query text is hashed as the key to the cached plan.
All of that being said, I just want to reiterate that query hints are not necessarily bad, and don't let my hard no above scare you away. It's just that query hints are meant to be used in more edge case scenarios, not your normal use cases, and improper use of them can actually severely impact performance. OPTION (RECOMPILE) is a good tool to keep in the back of your mind for when you're in a parameter sniffing performance jam as a temporary quick fix, or when there's an actual valid case to use it. But I would definitely not recommend just using it without the testing to back it up, and there's nothing special about Dynamic SQL that automatically makes sense to use RECOMPILE hints with it.


Is it possible to trace the password the application is attempting to use to connect?

The short answer is you can't see the password being used to connect.  That being said, you can restore a backup copy of the master database to a test instance, get the hashed password, and then apply it with the HASHED option of ALTER LOGIN.

The password hash can be retrieved from the restored master database with:

SELECT LOGINPROPERTY('your-sql-login', 'PasswordHash');

The password can then be applied to the other instance, substituting the value from the script above:

ALTER LOGIN your-sql-login
WITH PASSWORD = 0x01000CF35567C60BFB41EBDE4CF700A985A13D773D6B45B90900 HASHED;


Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?

You are seeing the victims.  Probably only a few of them are the villains.
Look at "Time".  A few 'system' processes may have very large times; look at the next couple -- they may be the ones that started the problem
How big is max_connections?  If it is more than a few dozen, it is inviting a log jam.
When a lot of threds are running (check Threads_running), they tend to stumble over each other, waiting for resources (CPU, I/O, buffer_pool space, table cache space, etc).  Meanwhile, the allocation mechanism is "playing fair".  That is, it is given each player a little of what it needs, so as to give a fair amount to the others.  Net effect:  Latency goes through the roof and throughput flattens or declines.

Cures:

Check for swapping -- Swapping is terrible for performance, and it may be a factor in what you are seeing.
Lower (yes, lower) max_connections.
Lower (yes, lower) the number of web server clients that can run simultaneously.
Use the slowlog (with a low value of long_query_time to identify both the long-running queries and the fast, but frequently running, queries.  Improve both.
It is usually easier to give the end-user a civilized message "System is busy, DO NOT HIT RELOAD", than to let things clog up and "never" finish.
Use Replicas to spread the readonly queries across multiple servers.


As indicated in comments these queries are not equivalent. To understand why the are different, let's examine them:

create table A (x int);
insert into A(x) values (1);

create table B (x int, y int);
insert into B(x,y) values (2,2);

select A.x, B.x, B.y
from A
left join B
    on A.x = B.x
    and B.y = 2;

Since no rows match the join predicate, B.x and B.y are replaced with null in the resulting relation:

A.x B.x     B.y
1   null    null

If we move B.y to the where clause:

select A.x, B.x, B.y
from A
left join B
    on A.x = B.x
where B.y = 2;

you will get 0 rows as a result. To understand why, the WHERE clause is evaluated after the JOIN, so this is the same as:

select Ax, Bx, By
from (
    select A.x as Ax, B.x as Bx, B.y as By
    from A
    left join B
        on A.x = B.x
) as T
where By = 2;

But

    select A.x as Ax, B.x as Bx, B.y as By
    from A
    left join B
        on A.x = B.x

still evaluates to

Ax  Bx      By
1   null    null

so your where clause will be:

where null = 2

i.e.

where null

which isn't TRUE, so the result is empty

Fiddle


Setting a default principal server in SQL Server 2008

This is not an out of the box feature of SQL Server. And here's why:

After a fail-over scenario, when the formerly primary server is back online, that doesn't necessarily mean that it should be put back into the primary role. Just because the server is online doesn't mean it's necessarily totally healthy from a hardware/software perspective (after all, it just went down). What if the issue is going to happen again in an hour? Do you really want to be playing ping pong with mirroring nodes every time the primary is down? There should be a manual QA of the node before putting it (preferably after business hours) back into the primary role.

Simply put: failing over automatically outside of a disaster situation adds unnecessary risk to your database up-time.

This article on SQL Server Central provides the following stored procedure to facilitate the action you describe, but I'd caution against doing it once again.

Create Procedure dbo.dbm_FailoverMirrorToOriginalPrincipal
     @DBName sysname
As
Declare @SQL nvarchar(200)

Set NoCount On;

/*
     If database is in the principal role and is in a synchronized state
     then fail database back to original principal
*/
If Exists (Select 1 From sys.database_mirroring
          Where database_id = db_id(@DBName)
          And mirroring_role = 1 -- Principal partner
          And mirroring_state = 4) -- Synchronized
  Begin
     Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'

     Exec sp_executesql @SQL;
  End

Set NoCount Off;


Is it a good practice to build in as many foreign keys as possible?

Another approach is to add player_id to instances table as a foreign
  key. Now players can queried directly from instances table. Shorter
  query, but one more column for a foreign key.

IMO, this will be bad approach.This is kind of circular reference  .

Since you have one to many relation so mapping table will be separate,dungeon,instance and player each will have master table and mapping table defining their one-to-many relation.

    CREATE TABLE instances (
    instance_id INT PRIMARY KEY
    ,instance VARCHAR(100) NOT NULL
    )

CREATE TABLE dungeons (
    dungeon_id INT PRIMARY KEY
    ,dungeon VARCHAR(100) NOT NULL
    )

CREATE TABLE player (
    playerid INT PRIMARY KEY
    ,player VARCHAR(100) NOT NULL
    )

CREATE TABLE dungeon_instance (
    dungeon_instance_id INT identity(1, 1)
    ,dungeon_id INT NOT NULL
    ,instance_id INT NOT NULL CONSTRAINT FK_instances FOREIGN KEY (instance_id) REFERENCES instances(instance_id)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        dungeon_id
        ,instance_id
        )
    )

CREATE TABLE player_dungeon (
    player_dungeon_id INT NOT NULL
    ,playerid INT NOT NULL
    ,dungeon_id INT NOT NULL
    ,CONSTRAINT FK_player FOREIGN KEY (playerid) REFERENCES player(playerid)
    ,CONSTRAINT FK_dungeon FOREIGN KEY (dungeon_id) REFERENCES dungeons(dungeon_id)
    ,PRIMARY KEY (
        playerid
        ,dungeon_id
        )
    )

if you have very frequent query between player and instance where there is no dungeon table require in output and volumne of data is high then you can go for Denormalisation.

Whether to create player_instance mapping table or not depend upon above requirement.

But it desirable to achieve Denormalisation via index view


Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

SQL Server has a limitation of 8060 bytes per row. See "Bytes per row" in Maximum Capacity Specifications for SQL Server. Additional information for this line has this phrase:

  SQL Server supports row-overflow storage which enables variable length
  columns to be pushed off-row. Only a 24-byte root is stored in the
  main record for variable length columns pushed out of row; because of
  this, the effective row limit is higher than in previous releases of
  SQL Server. For more information, see the "Row-Overflow Data Exceeding
  8 KB" topic in SQL Server Books Online.

So, when you insert two varchar values each 5000 bytes long in one row, one or both of them would be stored off-row and occupy only 24 bytes instead of 5000+2.

But, if you have 470 varchar columns in a table, you can insert only 8060/24=335 non-null varchar values in the same row of such table. In fact, even slightly less than 335, because there are other structures in a row that take some space.

MSDN: Row-Overflow Data Exceeding 8 KB

So, to reproduce it in test environment, create a table with at least 336 varchar columns and try to insert 336 non-null values that are longer than 24 characters in the same row.


How to capture all user's query on Oracle database

As per my knowledge, there is no straightforward way to achieve this goal. However, I have tried to compose the following steps to fulfill the requirements.

You can enable trace for all sessions by using the following statement.

alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

To disable this tracing, use the following statement.

alter system set events '10046 trace name context off';

Steps:

First, enable 10046 event trace.

SQL> conn / as sysdba

SQL> alter system set events '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

To get the names of these trace file use the following query where username should be your username used by the application.

SQL> SELECT LISTAGG(tf, ' ') WITHIN GROUP (ORDER BY tf) "Trace_List"
FROM (
      SELECT sys_context('userenv','instance_name') || '_ora_'|| p.spid || '.trc' as tf FROM
                                                                                v$process p join v$session s
                                                                                ON (s.paddr=p.addr)
                                                                                WHERE s.username='JAY'
);

Trace_List
--------------------------------------------------------------------------------
orcl_ora_3443.trc orcl_ora_3458.trc orcl_ora_3473.trc

 After that, go to USER_DUMP_DEST to find trace files.

SQL> show parameter user_dump_dest

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest               string  /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Now, you have got multiple trace files scattered on your user dump dest. In order to combine and create a single trace file, you can use trcsess utility, which allows trace information from multiple trace files to be identified and consolidated into a single trace file. You can consolidate these trace files based on following criteria. Choose the which is common for these all sessions.

Session Id

Client Id

Service name

Action name

Module name

In this case, I am going to use service name.

SQL> select service_name from v$session where username='JAY';

SERVICE_NAME
----------------------------------------------------------------
SYS$USERS
SYS$USERS
SYS$USERS

Let's use the trcsess utility.

[server1@oracle ]$ trcsess output=/home/oracle/Desktop/main_trace_file.trc service='SYS$USERS' orcl_ora_3443.trc orcl_ora_3458.trc orcl_ora_3473.trc

We have created a single trace file and we can use tkprof utility to create formatted output from this trace file.

[server1@oracle ]$ tkprof main_trace_file.trc main_formatted_trace.txt

Note: If you were using Enterprise Edition you could use fine-grained auditing which is not available in Standard Edition

References:
TRCSESS
TKPROF


Assuming that your table is for storing Sales Record, we can do the following:

For 1st Normal Form:

Eliminate columns with duplicate (not really eliminate but rather re-arrange).

I just separated the columns with repeating data and made them new rows.

For 2nd Normal Form

The table should be in 1NF
Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
Create relationships between these new tables and their predecessors through the use of foreign keys.

Created new table for the Seller, Customer, Product, Rating and the Records.
Seller No., Cust ID, Prod ID and Prod Rating are their primary keys (which is foreign keys for Sales Record Table)

For 3rd Normal Form

The table should be in 2NF
Remove columns that are not dependent upon the primary key.

I usually encounter this form when dealing with addresses. As you can see i added the table City and used Zip Code as a foreign key.
I also added a primary key in Sales Record table (Record ID).

You can read about normalization here and here.

Last word:
Normalization is good for reducing data redundancy and ensuring that their relationships is used. Useful with JOINs but can be a headache when there is too much data and tables.


Changing foreign key to ON DELETE CASCADE with least amount of impact

The docs say this about NOT VALID

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

Your concern,

I know that adding NOT VALID to the constraints means that existing data is not validated, but any rows inserted/updated before the VALIDATE CONSTRAINT will be checked.

They'll only be checked during validation AFTER you tell it to validate, so you can delay that until you have scheduled downtime.

Because the foreign key is dropped/added in the same transaction, is there any possibility of creating inconsistent data?

No, because the second you add the NOT VALID it applies to all rows inserted AFTER the statement as if they were always there. VALIDATION is for the rejection of creating an FOREIGN KEY when the referenced rows do not exist. It has nothing to do with cascading, observe
CREATE TABLE foo
AS
  SELECT 1 AS a;

CREATE TABLE bar
AS
  SELECT a
  FROM ( VALUES (1),(2) )
    AS t(a);

ALTER TABLE foo
  ADD PRIMARY KEY (a);

ALTER TABLE bar
  ADD FOREIGN KEY (a)
  REFERENCES foo
  ON DELETE CASCADE
  NOT VALID;

DELETE FROM foo;

TABLE foo;
 a
---
(0 rows)

test=# TABLE bar;
 a
---
 2
(1 row)

At this point you can see

the deletion from bar cascaded to bar
the lack of validation on bar means it still has a row that is invalid

The constraint still can't be validated (as shown below), but for the purposes of cascaded deletion all things are good.
ALTER TABLE bar VALIDATE CONSTRAINT bar_a_fkey  ;
ERROR:  insert or update on table "bar" violates foreign key constraint "bar_a_fkey"
DETAIL:  Key (a)=(2) is not present in table "foo".

Btw you can write this
begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade not valid;
commit;

Like this
alter table posts
  drop constraint posts_blog_id_fkey,
  add constraint posts_blog_id_fkey
    foreign key (blog_id)
    references blogs (id)
    on update no action
    on delete cascade
    not valid;

You don't have to wrap it in a txn. You also don't have to wrap any single statement in a txn -- PostgreSQL isn't MySQL. Everything is already transactional.


I'll do it in stages to make it easier to understand.

I put your data into a table called EX_DATA -  added a primary key as I assume you'll have one and it makes things a little easier:

create table ex_data
(
  contract_id number(18),
  expire_dt date,
  term_notice number(18),
  notice_freq varchar(20),
  notice_months number
);

insert into ex_data values(1,TO_DATE('01-MAR-2005','DD-MON-YYYY'), 60, 'YEARLY', 12);

insert into ex_data values(2,TO_DATE('01-DEC-2007','DD-MON-YYYY'), 30, 'MONTHLY', 1);

insert into ex_data values(3,TO_DATE('28-FEB-2008','DD-MON-YYYY') ,30, 'YEARLY', 12);

The next expiry date from the first given date will be:

select add_months(expire_dt, notice_months) as next_expiry
from ex_data;

Then, it follows that the notice period is a bit of easy date arithmetic too:

select add_months(expire_dt, notice_months) as next_expiry,
       add_months(expire_dt, notice_months) - term_notice as notice_date
from ex_data;

Using DUAL as a source, generate expiry dates for lots of years:

select expire_dt as first_expiry_date,
       l as expiry_num,
       add_months(expire_dt,l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
order by l;

The next expiration date, and the date they should have been contacted for renegotiation:

select first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as renegotiation_date
from
(
select expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates >sysdate
and all_expiry_dates - term_notice < sysdate
;

But, that's not what I think you're asking. You want the next renewal date where the contact period hasn't yet been hit, which can be done as follows:

select * from (
select contract_id, first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as notification_date, ROW_NUMBER( ) OVER (PARTITION BY
contract_id ORDER BY all_expiry_dates) r
from
(
select contract_id, expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates, notice_months
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates - term_notice > sysdate
)
where r=1;

Can probably be done with some simple SYSDATE maths along with the source data, but my brain is a little slow this evening ;)


How to tell which windows group login I used when logging in via windows authentication

Windows Authentication in SQL Server doesn't work exactly like that. When you log in as a domain user who has an associated login, but also has access via a domain group that has an associated login, the access is determined by the combined DENY/GRANT permissions and SQL\DB role membership assigned to both the user login and the group login.

There is no concept of being 'logged in' as the group, the group is simply a container to provide access to a collection of domain users based on their membership in this group in Active Directory.

You can check the various access paths for a particular user by running this command:

EXEC xp_logininfo '<DOMAIN\user>', 'all'

This will list out the various access paths for a user, i.e. all the group logins and user logins this Windows account is linked to.


How to backup and restore in oracle with RMAN

When it comes to recovering data, restoring the database is actually the last weapon in the Oracle DBA's arsenal.

Look into the "Flashback" suite of technologies; there are about six of them, all using slightly different methods to achieve slightly different things.

For example, to recover a dropped table, you might use "Flashback Table", which simply gets that one table back from the Recycle bin (unless some clever Bod uses "drop table .. purge" to obliterate it completely).

To recover data that's been deleted, you can use "Flashback Query" to query the table as it was in the past (but only as far as back as Guaranteed Undo will support).

The quickest way to recover the database to a point in time is "Flashback Database", which uses completely separate Flashback Logs to [very] rapidly roll the whole database back to a point in time (this time limited by the amount of Flashback logs in the FRA).

To do a full-blown recovery of the database to a point in time, you need the database to be in archivelog mode and you need to be backing up the database, controlfiles and archived redo logs.  Assuming you've got all of that:

RMAN>
run
{
  set until time "to_date('20190528120000','YYYYMMDDHH24MISS')";
  restore database;
  recover database;
  alter database open resetlogs;
}

BTW, the most pointless activity you can do as an Oracle DBA is to omit the "set time" clause in the above.  That will destroy the database, overwrite it with its [latest] backup and then roll forward the archived redo logs, thereby putting the database back into exactly the same state as when you started the operation!


SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?

You can!
Run this to find your plan's ID:
select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

Then place that ID in the stored procedure called DBCC FREEPROCCACHE
DBCC FREEPROCCACHE (plan_handle_id_goes_here)

You could always just free the whole thing though. It'll add a few seconds to the first time each query is ran, but no more than what a server reset (or an IISRESEt in a web server) would do anyways.
This is how you clear it all:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Check here for more info https://serverfault.com/questions/91285/how-do-i-remove-a-specific-bad-plan-from-the-sql-server-query-cache


Query a database specific configuration parameter

These settings are stored in the pg_catalog.pg_db_role_setting system catalog.  You can search for the databases that have the above setting like

SELECT datname
  FROM (SELECT datname, unnest(setconfig) AS setting
          FROM pg_db_role_setting
          JOIN pg_database AS d ON setdatabase = d.oid) AS t
 WHERE setting = 'default_transaction_read_only=false';


Backend Table/Row Locking Structure using CTE Queries

No, the locks will not change because of CTEs. What you have shown are non-recursive CTEs. These are just another way of introducing a sub-query into the SQL statement. The query
with some_CTE as
(
  select < whatever > from < something >
)
select * from some_CTE;

is semantically identical to
select * from
(
  select < whatever > from < something >
) as some_alias;

As the query optimizer is free to rearrange your statement into any logical equivalent it is likely these two statements will end up with identical execution plans.  (For very complex queries, where the CTE or the main query has many joins, or the CTE is referenced many times, the execution plans may be different, but this is an artefact of the optimization, not a fundamental property of how CTEs work.)
This paragraph is tangential to your main point about locking, but worth knowing. You say "When the SELECT for CTE1 is finished.." It doesn't necessarily work like that. The execution may proceed in any manner that satisfies the meaning of our query but does not have to follow the sequence in which it was written. See here and here for examples of how execution order may differ to written order.
As to your main question "I was under the impression that .. when the SELECT .. completes the tables are released. Is that incorrect?" I'll refer you to this Microsoft page:

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

Here is another explanation which may explain it better. Both sources make clear that the granularity of locking (row, page or table) affect when the lock is released. There are many things that affect this, including system configuration and lock escalation.
I'll note also that your example uses UNION. This removes duplicates from the results. You may get different lock behaviour using UNION ALL, but that leaves duplicates and could return more rows, depending on your data.


Just update one column, strange execution plan

Let's do a small thought exercise:

How do you think SQL Server finds the row with that book_id?
How do you think it finds (not updates) the value for last_read?

Remember that the clustered index is the table. If you update the table, you must update at least the clustered index.

Let me continue the thought exercise by asking a different question - do you think there is (or should be) a more efficient way of updating this value, than by finding the row via the clustered index, and changing the value there?

Let's leave heaps out of this for now, but the concept is similar there.


What will happen to SQL Service Account when SQL Server is uninstalled?

Are we going to need to request for a new Service Account to be created for the SQL Services of the Developer Edition?

You can re-use the service account, assuming you know the information or can reset the information (password, for example) if it's not known. Managed Service Accounts (MSA's) can be re-used on the same server, but if moving between servers or renaming servers would need to have their metadata changed in active directory and uninstalled from the previous server, first.


One way to enforce this is to add CategoryID in Composition. Then you can add a UNIQUE constraint on (ParticipantID, CategoryID, Date, Time).

This requires to modify the foreign key to Game and to add a UNIQUE constraint on Game (or modify the existing one). The tables that need changes:

Game
    Id (int, pk)
    Name (varchar(50))
    CategoryId (int, fk: Category->Id)
    etc...
      UNIQUE (CategoryID, Id)                            -- added

Composition
    Id (int, pk)
    ParticipantId (int, fk: Participant->Id)
    CategoryId (int)                                     -- added
    GameId (int)
      FK (CategoryId, GameId) -> Game (CategoryId, Id)   -- modified
    Date (date)
    Time (time)
      UNIQUE (ParticipantID, CategoryID, Date, Time)      -- added


Your query is too complex; the join between movie and rating is not necessary because the movie ID is already directly available from the subquery.

Anyway, using subqueries instead of joins usually is easer:

First, these are all reviews by anyone named Chris Jackson:

SELECT *
FROM rating
WHERE rID IN (SELECT rID
              FROM reviewer
              WHERE name = 'Chris Jackson')

(If you know that there is only one Chris Jackson, use WHERE rID = (SELECT ... instead of WHERE rID IN (SELECT ....)

Now you want all movies that do not have such a review:

SELECT title
FROM movie
WHERE mID NOT IN (SELECT mID
                  FROM rating
                  WHERE rID IN (SELECT rID
                                FROM reviewer
                                WHERE name = 'Chris Jackson'))

It would be possible to combine the two inner subqueries with a join:

SELECT title
FROM movie
WHERE mID NOT IN (SELECT rating.mID
                  FROM rating
                  JOIN reviewer USING (rID)
                  WHERE reviewer.name = 'Chris Jackson')


Run a program on a remote computer after a SQL job has completed

You can do it using

PSEXEC from sysinternals.
powershell using remoting functionality. (https://social.technet.microsoft.com/wiki/contents/articles/7703.powershell-running-executables.aspx
)

you cam have a batch file call your program and can use xp_cmdshell (if allowed) or operating system task in SQL agent job to run the job locally. This job can be called using linked server or powershell after the main step in the original job is completed.


SQL Server 2008 R2 Restore constantly hanging

I think I have found the issue.

The backups are stored our data domain, so when performing the restore we select the backups from this location. I think there is some type of connection issue between this server and the data domain. Because when I move the backup files over to the server and restore from the server directly, I cannot reproduce the issue and the restores are probably 10x faster.I set a continuous restore of a database (11 GB) that has been running for about 25 minutes and has completed the restore successfully 23 times with no hanging

My thinking is that the connection issue probably lies somewhere with our test server still. Because we use the Data Domain for all of our production backups (although it is changing) and have no problems restoring elsewhere, including the databases I have been working with. Not to mention, we have automated nightly restores on other servers that have never failed.

I think this at least pinpoints the issue. I will look into where the connection issue is and work with the server\networking guys to see if we can resolve it.

Thanks you guys for your quick assistance


Why are certain objects loaded into Buffer?

First, check the plan cache with queries that hit sys.dm_exec_query_stats. My favorite is the open source sp_BlitzCache (disclaimer: I'm one of the coauthors). Queries could be running outside of your control, like monitoring systems. I've seen third party tools that do things like check table fragmentation.

Next, not everything stays in the plan cache. Unfortunately - especially when you're dealing with monitoring software - some queries are smart enough to do a WITH (RECOMPILE) in order to avoid going into the plan cache. (sp_BlitzCache even does this.) To catch diabolical queries like this, consider doing a Profiler trace or Extended Events capture - although I wouldn't recommend leaving those in place long term. Only do it to satisfy your curiosity.


SQL Server 2012 Availability Group issue

You have to do following :

Drop the availability group then
using regedit, (first backup the registry of old availability group that starts with HKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap) and then delete that key and
Finally create a new Availability Group with new name.

Note: Extra Caution .. as you are dealing with Windows Registry, Back it up first


Why are correlated subqueries sometimes faster than joins in Postgres?

To start, a correlated subquery can't return more than one row. Take for instance.

SELECT t.x
FROM ( VALUES (1),(2) )
  AS t(x)
LEFT OUTER JOIN (
  VALUES (1),(1)
) AS y(x)
USING (x);
 x
---
 1
 1
 2
(3 rows)

Versus the same query written as a correlated subquery

SELECT t.x, (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS y(x)
  WHERE y.x = t.x
)
FROM ( VALUES (1),(2) )
  AS t(x);
ERROR:  more than one row returned by a subquery used as an expression

That tells the planner something useful, and it can optimize that. Adjusting for this, by reducing the inner table to one row -- you'll see the plans:

EXPLAIN ANALYZE SELECT t.x
FROM ( VALUES (1),(2) )
  AS t(x)
LEFT OUTER JOIN (
  VALUES (1),(1)
) AS y(x)
USING (x);

                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=0.05..0.10 rows=2 width=4) (actual time=0.019..0.021 rows=3 loops=1)
   Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.002..0.003 rows=2 loops=1)
   ->  Hash  (cost=0.03..0.03 rows=2 width=4) (actual time=0.005..0.005 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.001..0.003 rows=2 loops=1)
 Planning time: 0.107 ms
 Execution time: 0.046 ms
(8 rows)

Correlated subquery method.

EXPLAIN ANALYZE SELECT t.x, (
  SELECT *
  FROM ( VALUES (1) )
    AS y(x)
  WHERE y.x = t.x
)
FROM ( VALUES (1),(2) )
  AS t(x);

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=2 width=4) (actual time=0.014..0.017 rows=2 loops=1)
   SubPlan 1
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.004 rows=0 loops=2)
           One-Time Filter: (1 = "*VALUES*".column1)
 Planning time: 0.097 ms
 Execution time: 0.043 ms
(6 rows)

This follows vaguely your above examples. So when is a correlated subquery better than a join? Not sure, but the correlated subquery gets to skip the hash join.

You have in your plan this

Filter: ((id_site IS NULL) AND ("labelDate" < '2015-09-01'::date))

That's not even the same SQL you've got above. That makes me call into question the whole thing.

I would do this though

CREATE INDEX ON pages ( labelDate ) WHERE id_site IS NULL;

If you don't have an index on id_site IS NULL or id_site then it seems likely you're just forcing a seq scan there.


Query All Tables In PostgreSQL Database For Text

Try to generate the whole list of SQL statements to be run in a single SQL file and run this SQL file with psql.


How do you use psql client to connect to a postgresql ipv6 host?

As Daniel Vérité mentioned in the comment, on Unix, "%interface" should be given as a suffix to the ipv6 address. You can get the interfaces by running ifconfig command. So, the right psql command was

psql --command="select * from test;" -d webdb -h fe80::242:ac12:2%br-67d2c47f5b8e -p 5432 -U webserver

I had other problems. But now, the host was reachable. Next, I noticed that the suffix "clientcert = 1" had to be added to pg_hba.conf (don't know why)

# pg_hba.conf
hostssl  webdb  webserver   ::0/0    cert clientcert=1

listen_addresses value in postgresql.conf was fine (restricting requests & responses to only ipv6 addresses) - so is the value '::0/0' in pg_hba.conf

I then got ssl validation errors from the server. I had to set path variables on my client side for psql.

export PGSSLCERT=/my-folder/my-certificate.pem
export PGSSLKEY=/my-folder/my-private-key.pem

If your private key is password protected, psql prompts you for the password. Once the connection went through, (=> ssl works fine) , I noticed that the role "webserver" didn't have login permission. I had to alter-role to allow login and had to grant select and update access to the test table.

Now it works. Thanks to Daniel for the ipv6 suffix solution.


Data that are sometimes entered and sometimes computed

There's no hard-and-fast rule here.
This pattern is very common, and here you could either:

Have application logic update the StorageFacility.Capacity (etc) every time a StorageTank is added, removed, or perhaps undergoing maintenance.

or

Store the data only at the StorageTank and roll it up as needed.

Things like the frequency of changes, the level of complexity of the roll-up rules, frequency of query, etc should make your decision.
In either case I don't believe it'helpful that "Application code should treat a collection of StorageTanks at the same Location as an aggregated StorageFacility".  You should simply have two seperate entities.


You need something like that:

BEGIN
  IF CURTIME() BETWEEN '08:00:00' AND '11:59:59'
    THEN UPDATE ...
  END IF
END

This trigger will be activated each time the event occure but it will do something only if current time is within the given range and will do nothing otherwise.


How does sampling work when updating statistics?

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT)
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) is responsible for the sampling and is documented here

  TABLESAMPLE SYSTEM returns an approximate percentage of rows and
  generates a random value for each physical 8-KB page in the table.
  Based on the random value for a page and the percentage specified in
  the query, a page is either included in the sample or excluded. Each
  page that is included returns all rows in the sample result set.

The documentation  also states

  Although the plan shows a table scan is performed, only those pages
  that are included in the result set are actually required to be read
  from the data file.

The STATMAN call is to an internal aggregate function briefly described here


mysql db backup excluding specific tables

You can backup databases and tables using the mysqldump command.  There are parameters for excluding certain tables.

mysqldump -u username -ppasswword -h servername --ignore-table=dbname.tabletoskip dbname

If you need to get fast backups of MyISAM or ARCHIVE tables, you can also use mysqlhotcopy.  That has more restrictions though.


ORA-24155 - Why am I getting this error when creating a chain rule

I never did find a "proper" way to resolve this, but I was able to fix by:

select * from sys.scheduler$_job for update -- manually remove a running job
-- Delete the job from this table

The job kicked off and then the machine was rebooted in the middle of the job.

Later, the job was deleted, but apparently the old ruleset still existed somewhere in the database even though there wasn't a record of the ruleset existing (EG: data was still there, but the meta saying it was there did not exist).  When I deleted the job object from the appropriate table, it gave me a different ruleset when I passed it "null" to the ruleset parameter.  I guess it was somehow pulling the old rule set and the name was being detected as a duplicate.


How to isolate general Oracle 10g issues through SQL

first thing you should do is get AWR(10g on)/statspack(if your on an older version of the DB) running on your database so that it generates a snap every few minutes (few being say 15, 30 ..if you need more resolution, make the snaps more frequent). this will give you a system overview on IO, CPU etc and will show you the TOP SQL (in terms of IO, CPU, runtime, executions and the like). it will advise on SGA / buffer cache usage too.

From that point onwards you should start looking at the worst sql/areas and run tools like explain plans/tkprof/sql trace on sql statements and plsql profiler on any slow running pl/sql code.


Write lock during mysqldump even using --lock-tables=false

Your Question : When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this?
You need to understand why other cannot update. Using MyISAM, each table experiences a full table lock with every INSERT, UPDATE, and DELETE. Using --lock-tables will attempt to lock every table, but will be held up of SELECTs are being issued in a heavy-read environment. There are other alternatives.
Your Question : As I am using MyISAM, does --single-transaction for InnoDB help?
InnoDB is an ACID-compliant storage engine and can have data updated and read in transactions. Row-level locking  is normally issued in InnoDB, which allows for other to read and write to tables while they being backed up. MyISAM does not allow this given its full-table locking protocol. Therefore, MyISAM is not assisted by --single-transaction at all. If every table you created was InnoDB, then you could perform an online backup on a running master.
Your Question : Also, is Master-Slave architecture the ultimate method to handle backing up online DB?
Master/Slave does make it easier to backup an online database because you perform the backup using mysqldump on the slave while the Master continues to accept INSERTs, UPDATEs, and DELETEs. In addition, this allows for backups that allow for point-in-time recovery. You would simply perform the following steps on a slave:

STOP SLAVE;
mysqldump
START SLAVE;

If you issue this on the Slave at midnight, the data is frozen in time. That way, when you restore the mysqldump, all the tables are from the same timeframe.
SIDENOTE : You are not obligated to convert all the tables on the Slave to InnoDB.
If all tables were InnoDB, using single-transaction would create point-in-time backups also againsa a master. Since you have MyISAM tables, then Master-Slave is the ideal solution, not necessarily the ultimate. There are still other methods out there, but for the sake of simplicity: GO WITH MASTER-SLAVE !!!


Introduce or not introduce SSIS to an ETL project

The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple Execute SQL tasks operating concurrently).

If you are using traditional SQL then you probably don't need 1), but you might need 2).  Your ETL must be running in serial, all one after the other.  If you look across your whole ETL process, are there any tasks that could run in parallel thus potentially saving you time?

SSIS also has several other abilities, eg the Script Component and Script Task allow you access to sophisticated .net scripting without having to use CLR in your SQL Server.  The Fuzzy Grouping Task allows you to group similar terms for comparison, way better than Soundex!  There are also third-party add-ins such as CozyRoc which has a vastly improved Excel connector and a partitioning task.

Regarding the permissions, SSIS jobs running under SQL Agent normally run under the context of the SQL Agent account, although you can change this using proxies.

Regarding the scale of your project, using SSIS would give you the ability to scale out in the future, eg if you suddenly have to process 10 facts instead of 1, you could do this in parallel whereas your current process would have to do them one after the other, in potentially the same time.  Maybe your scheduler handles some parallelism but I can't imagine it would have the level of control (eg conditional flow, redirect error output) that SSIS gives you.

HTH


Movement of data from one data file to another within PRIMARY Filegroup

There's no direct command for this.

You can cause enough data movements using ALTER TABLE or ALTER INDEX with the REBUILD option table by table or index by index, so that the new storage allocated by the rebuild will come from the new files. I.e., this will cause "emptiness" of the mdf file for each rebuild. And then finally a shrink (not using EMPTYFILE, but to the desired size, same size as the other files) will do whatever movements there is left to do (the pages allocated to the part of the mdf file that will be removed).

Note though that this final shrink will fragment your indexes and if you care about index fragmentation you would be in for another rebuild.

The reason for using rebuild first is for you to control the pace, doing bits by bits. And also because the shrink code can be horribly slow (for instance if you have heaps and LOBs).


Downsides to using SQL 2012 Upgrade as opposed to a fresh install?

Even if you install SQL Server 2012 clean, you get menu items in the start menu under SQL Server 2008 (e.g. Configuration Tools > Installation Center). In Add/Remove Programs do you see anything other than 2008 R2 Management Objects and 2008 Setup Support Files? I think these get installed by 2012 setup for backward compatibility reasons.

To be on the safe side, you could remove all the SQL Server 2008 components (except the database engine or other services you're using - e.g. remove client tools etc.) before installing SQL Server 2012, if cleanliness is important. The system does allow some components from both version, such as 2008 Management Studio and 2012 Management Studio, to co-exist, so for some parts of the ecosystem, upgrading is an addition rather than a replacement. It might be beneficial for other reasons to also remove peripheral things - for example I don't think BIDS 2008 plays very well with the 2012 client tools (not that I have tried it, just overheard rumblings about it).

When upgrading I would certainly make sure to do the following things, in addition to reading this very thorough upgrade guide:

Update the compatibility level of each database
Update all statistics

When upgrading from 2000 it has always been recommended to run DBCC ... WITH DATA_PURITY but I don't believe that step is necessary anymore (besides, you can't upgrade to 2012 directly from 2000 anyway).


One of the table of MySQL database is missing

Not table but tablespace is missing. This means that the file in which table's data was stored on disk was removed (deleted, renamed, moved, made inacessible/nonscannable). Look in the filesystem for it. Try to undelete...


it is connection specific,

All plain set commands are. (see the set command in sql refernce)

You can make the setting persistent using.

ALTER[user|database] name SET xmloption TO DOCUMENT ;

see the ALTER command in sql reference

Alternatively you can prepare the session or prepend to the stream being sent to psql, you don't need to edit the actual disk file.

In the following examples everything upto the $ represents the prompt,

Using the psql command-line:

  database-$ SET xmloption TO DOCUMENT ;
  database-$ \i filename

Or the system commmand-line

~$ psql "conmnection-parameters" -c "SET xmloption TO DOCUMENT" -f "filename"


How to break down RELATIONAL data into HIERARCHY data structure?

A CROSS APPLY would seem perfect for the job:

SELECT
  v.Nodeid,
  v.ParentNodeid,
  v.Header
FROM
  dbo.atable
  CROSS APPLY
  (
    VALUES
    (D1id, NULL, 'D1'),
    (D2id, D1id, 'D2'),
    (D3id, D2id, 'D3')
  ) AS v (Nodeid, ParentNodeid, Header)
;

For every row of the source dataset, CROSS APPLY produces three, using the VALUES row constructor, explicitly specifying which column of the original set goes into which new column.

Now, the above will return duplicates if some pairs in your source repeat. You can suppress them with DISTINCT:

SELECT DISTINCT
  v.Nodeid,
  v.ParentNodeid,
  v.Header
...

In order for the transformed set to follow the order of D1id ASC, D2id ASC, D3id ASC, you could include those columns in the output and use them for sorting:

SELECT DISTINCT
  t.D1id,
  t.D2id,
  t.D3id,
  v.Nodeid,
  v.ParentNodeid,
  v.Header
FROM
  dbo.atable AS t
  CROSS APPLY
  (
    VALUES
    (t.D1id, NULL  , 'D1'),
    (t.D2id, t.D1id, 'D2'),
    (t.D3id, t.D2id, 'D3')
  ) AS v (Nodeid, ParentNodeid, Header)
ORDER BY
  t.D1id ASC,
  t.D2id ASC,
  t.D3id ASC
;

The reason you have to include them in SELECT is because, when you have DISTINCT, you may only sort by columns in the SELECT clause. Naturally, the result set will include the three extra columns as well. If you do not want them in the output, you can use the above as a derived table: your outer SELECT would pull only the three required columns and sort by the other three:

SELECT
  Nodeid,
  ParentNodeid,
  Header
FROM
(
  SELECT DISTINCT
    t.D1id,
    t.D2id,
    t.D3id,
    v.Nodeid,
    v.ParentNodeid,
    v.Header
  FROM
    dbo.atable AS t
    CROSS APPLY
    (
      VALUES
      (t.D1id, NULL  , 'D1'),
      (t.D2id, t.D1id, 'D2'),
      (t.D3id, t.D2id, 'D3')
    ) AS v (Nodeid, ParentNodeid, Header)
) AS s
ORDER BY
  D1id ASC,
  D2id ASC,
  D3id ASC
;

Alternatively, you could use GROUP BY instead of DISTINCT and thus return the sorted three-column set without a derived table:

SELECT
  v.Nodeid,
  v.ParentNodeid,
  v.Header
FROM
  dbo.atable AS t
  CROSS APPLY
  (
    VALUES
    (t.D1id, NULL  , 'D1'),
    (t.D2id, t.D1id, 'D2'),
    (t.D3id, t.D2id, 'D3')
  ) AS v (Nodeid, ParentNodeid, Header)
GROUP BY
  t.D1id,
  t.D2id,
  t.D3id,
  v.Nodeid,
  v.ParentNodeid,
  v.Header
ORDER BY
  t.D1id ASC,
  t.D2id ASC,
  t.D3id ASC
;


pt-online-schema-change -- It will take longer, but it will not interrupt other activity except briefly.


Create a read-write user and a read-only user

ALTER DEFAULT PRIVILEGES only affects tables created by the user who ran the ALTER DEFAULT PRIVILEGES statement.

You'd have to do this:

ALTER DEFAULT PRIVILEGES FOR ROLE read_write IN SCHEMA my_schema
   GRANT SELECT ON TABLES TO read_only;