1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail?

This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID principles you mention. The triggering statement and trigger code both succeed or fail as a unit.

The ACID properties also guarantee the whole transaction (including the trigger code) will leave the database in a state that does not violate any explicit constraints (Consistent) and any recoverable committed effects will survive a database crash (Durable).

Unless the surrounding (perhaps implicit or auto-commit) transaction is running at the SERIALIZABLE isolation level, the Isolated property is not automatically guaranteed. Other concurrent database activity could interfere with the correct operation of your trigger code. For example, the account balance could be changed by another session after you read it and before you update it - a classic race condition.

  2. My IF and UPDATE statements look strange. Is there any better way to update the correct [Account] row?

There are very good reasons the other question you linked to does not offer any trigger-based solutions. Trigger code designed to keep a denormalized structure synchronized can be extremely tricky to get right and test properly. Even very advanced SQL Server people with many years of experience struggle with this.

Maintaining good performance at the same time as preserving correctness in all scenarios and avoiding problems like deadlocks adds extra dimensions of difficulty. Your trigger code is nowhere close to be robust, and updates the balance of every account even if only a single transaction is modified. There are all sorts of risks and challenges with a trigger-based solution, which makes the task deeply unsuitable for someone relatively new to this technology area.

To illustrate some of the problems, I show some sample code below. This is not a rigorously-tested solution (triggers are hard!) and I am not suggesting you use it as anything other than a learning exercise. For a real system, the non-trigger solutions have important benefits, so you should carefully review the answers to the other question, and avoid the trigger idea completely.

Sample tables

CREATE TABLE dbo.Accounts
(
    AccountID integer NOT NULL,
    Balance money NOT NULL,

    CONSTRAINT PK_Accounts_ID
    PRIMARY KEY CLUSTERED (AccountID)
);

CREATE TABLE dbo.Transactions
(
    TransactionID integer IDENTITY NOT NULL,
    AccountID integer NOT NULL,
    Amount money NOT NULL,

    CONSTRAINT PK_Transactions_ID
    PRIMARY KEY CLUSTERED (TransactionID),

    CONSTRAINT FK_Accounts
    FOREIGN KEY (AccountID)
    REFERENCES dbo.Accounts (AccountID)
);

Preventing TRUNCATE TABLE

Triggers are not fired by TRUNCATE TABLE. The following empty table exists purely to prevent the Transactions table being truncated (being referenced by a foreign key prevents table truncation):

CREATE TABLE dbo.PreventTransactionsTruncation
(
    Dummy integer NULL,

    CONSTRAINT FK_Transactions
    FOREIGN KEY (Dummy)
    REFERENCES dbo.Transactions (TransactionID),

    CONSTRAINT CHK_NoRows
    CHECK (Dummy IS NULL AND Dummy IS NOT NULL)
);

Trigger Definition

The following trigger code ensures only necessary account entries are maintained, and uses SERIALIZABLE semantics there. As a desirable side-effect, this also avoids the incorrect results that might result if a row-versioning isolation level is in use. The code also avoids executing the trigger code if no rows were affected by the source statement. The temporary table and RECOMPILE hint are used to avoid trigger execution plan problems caused by inaccurate cardinality estimates:

CREATE TRIGGER dbo.TransactionChange ON dbo.Transactions
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF @@ROWCOUNT = 0 OR
    TRIGGER_NESTLEVEL
    (
        OBJECT_ID(N'dbo.TransactionChange', N'TR'),
        'AFTER',
        'DML'
    ) > 1
    RETURN;

    SET NOCOUNT, XACT_ABORT ON;

    CREATE TABLE #Delta
    (
        AccountID integer PRIMARY KEY,
        Amount money NOT NULL
    );

    INSERT #Delta
        (AccountID, Amount)
    SELECT
        InsDel.AccountID,
        Amount = SUM(InsDel.Amount)
    FROM
    (
        SELECT AccountID, Amount
        FROM Inserted
        UNION ALL
        SELECT AccountID, $0 - Amount
        FROM Deleted
    ) AS InsDel
    GROUP BY
        InsDel.AccountID;

    UPDATE A
    SET Balance += D.Amount
    FROM #Delta AS D
    JOIN dbo.Accounts AS A WITH (SERIALIZABLE)
        ON A.AccountID = D.AccountID
    OPTION (RECOMPILE);
END;

Testing

The following code uses a table of numbers to create 100,000 accounts with a zero balance:

INSERT dbo.Accounts
    (AccountID, Balance)
SELECT
    N.n, $0
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND 100000;

The test code below inserts 10,000 random transactions:

INSERT dbo.Transactions
    (AccountID, Amount)
SELECT
    CONVERT(integer, RAND(CHECKSUM(NEWID())) * 100000 + 1),
    CONVERT(money, RAND(CHECKSUM(NEWID())) * 500 - 250)
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND 10000;

Using the SQLQueryStress tool, I ran this test 100 times on 32 threads with good performance, no deadlocks, and correct results. I still do not recommend this as anything other than a learning exercise.


Can I change the SID of an Oracle database?

You need to recreate the control file

This post by Kaunain Ahmed describes the necessary steps:

  do: alter database backup controlfile to trace;
  extract the "create controlfile" command from the
  background-dump-destination tracefile.
  shutdown the DB.
  Change the DB-Name in your init.ora and change the init.ora
  Change the SID in the /etc/oratab or /var/opt/oracle/oratab
  Change the SID in your environment and source it
  Startup the database to mount-status
  startup mount
  Re-Create the controlfile with the statement from position 2.
  Do a alter database rename global_name to
  10.Change the TNS-Configuration accordingly
  $ORACLE_HOME/network/admin/*.ora Look for SID and GLOBAL_NAME

There are other tools referenced in the thread.

Here's a post by AskTom which references the process in more detail. While it's for 10g, it should still work.


Emulate a TSQL sequence via a stored procedure

I've had a look and MS themselves offer a solution without locks

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...


With SQL Server 2008, how can I roll back a transaction done some time before?

There's no way to roll back a specific transaction like that using the native tools.  There are (or at least there used to be) some third party tools which would let you do this, but it depends on a lot of things.

Your best bet will be to log the before and after values for all the rows that you change during out of band update, then if you need to roll back those changes you've got the needed values.  You might want to log the new values as well as the old values so that you can see if the values have been since changed again so that you can then figure out if you want to roll back those changes.


I suggest not using telnet to connect to database servers.

Recommend using ServerAliveInterval (man 8 ssh_config) to keep your connection alive rather than a mysql mechanism.


If you are saying your actual backup file is corrupted (the .trn file) but that the backup was successful from SQL Server's perspective, and the source DB is still online, I would take a full backup as soon as possible so you don't lose any data.

You will have to rebuild your log shipping if you've got a corrupt .trn, but your main concern should be having the ability to restore your source data at all if you've got a bad .trn in your chain.


PostgreSQL : send all rows of a table to a function

UPDATE mytbl
SET col2=myfn(col1)

should do the trick


ORDER BY on field that isn't in a SELECT using DISTINCT or GROUP BY to get distinct records

Actually, did manage to figure it out with:

SELECT
    code,
    title,
    country,
    MIN(sortorder)
FROM
    data_a
GROUP BY
    code,
    title,
    country
ORDER BY
    4


Tabular mode disabled during SSAS installation

That option is only available for Enterprise and Business Intelligence editions:

Features Supported by the Editions of SQL Server 2012

And, of course, for Developer Edition.


Pinging SQL Server Availability Group Listener sometimes fails

The AG1 listener is set to static IP with two IP address
  ...
  If I open a command prompt and ping AG1 , sometimes it succeeds (primary ip replies) and sometimes it fails (secondary ip address is unreachable)

Yes.  This is normal when the nodes are on different subnets.  The listener requires two separate IP addresses, only one of which is ever online.

Ping will resolve the DNS name, but will attempt to connect only to whichever IP is returned first.  The order address are returned varies based on the client's subnet, and through "DNS Round-Robin".

The current SQL Server client libraries are coded to understand that when a DNS name resolves to two IP addresses, it must try both, as only one will be online.


How to get the sum with more then one table

When you do the aggregation without ingredient, then LEFT JOIN result to ingredient you get this

mysql> SELECT A.*,ig.qty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id;
+----+----------+---------+--------------------+------+
| id | nom      | thisqty | thissum            | qty  |
+----+----------+---------+--------------------+------+
|  1 | Amande   |   19470 | 204.74464999999998 |  600 |
|  1 | Amande   |   19470 | 204.74464999999998 |  200 |
|  2 | Chocolat |    7920 |              62.97 |  100 |
|  2 | Chocolat |    7920 |              62.97 |  200 |
|  4 | Beurre   |     500 |               2.48 |   60 |
|  4 | Beurre   |     500 |               2.48 |   60 |
+----+----------+---------+--------------------+------+
6 rows in set (0.00 sec)

mysql>

Perhaps doing an aggregation after the INNER JOIN would help

mysql> SELECT A.*,SUM(ig.qty) igqty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id
    -> GROUP BY A.id,A.nom,A.thisqty,A.thissum;
+----+----------+---------+--------------------+-------+
| id | nom      | thisqty | thissum            | igqty |
+----+----------+---------+--------------------+-------+
|  1 | Amande   |   19470 | 204.74464999999998 |   800 |
|  2 | Chocolat |    7920 |              62.97 |   300 |
|  4 | Beurre   |     500 |               2.48 |   120 |
+----+----------+---------+--------------------+-------+
3 rows in set (0.00 sec)

mysql>


RSAT on Windows 10 fails to add node to server 2016 cluster

The RSAT tool was updated with the general availability of Windows Server 2016. By going to the same link as the preview RSAT, https://www.microsoft.com/en-us/download/details.aspx?id=45520, you can update your workstation with the new version.


Why is my planning time consistently slower than execution time?

The only time I see planning times that slow for such simple queries is the first time a table is used in a given connection.  Then it has to read the metadata about the table and its indexes and statistics, perhaps from disk.

Is that is actually a problem?  If so, you could use a connection pooler so you re-use the connections with the metadata already in memory.


Merge performs always additional INSERTS

I found the issue myself.

It was NULL values in the compared columns that could not be matched with one another. After adding ISNULL function around source and target to replace NULL in case it is there, it works as expected.

 MERGE INTO [Z].[IAPP] AS Target
    USING (
        SELECT
    [SXD],[UUZQ],[SSRTD],[AAFFT],[IID] From [Z].[vIAPP]
    ) AS Source

([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])

  ON (
     ISNULL(Target.[SXD],'nul') = Isnull(Source.[SXD],'nul') AND isnull(Target.[UUZQ],'nul') = isnull(Source.[UUZQ],'nul')  /* etc. */
    )
            WHEN NOT MATCHED BY

TARGET AND @Action = 'UI' THEN
            INSERT([SXD],[UUZQ],[SSRTD],[AAFFT],[IID])
            VALUES(Source.[SXD],Source.[UUZQ],Source.[SSRTD],Source.[AAFFT],Source.[IID])
        WHEN NOT MATCHED BY SOURCE AND @Action = 'D' THEN
            DELETE;


Execute new query when current query finalises

Assuming it is just a single statement that you are waiting to complete, you can first run the following to get the start time of the current request

SELECT start_time FROM sys.dm_exec_requests WHERE session_id = <<spid_of_other_connection>>

And then having got the start_time do some simple polling in another SSMS window

SET NOCOUNT ON;

WHILE EXISTS
(SELECT *
        FROM    sys.dm_exec_requests
        WHERE   session_id = <<spid_of_other_connection>>
                AND start_time = <<start_time_from_above>>)
BEGIN
  WAITFOR DELAY '00:00:10'
END

PRINT 'DO SOMETHING HERE';

The filter on start_time rather than just session_id is because the session_id can be recycled if the original connection is closed.


MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

KISS

Assuming daily means every day including weekends and holidays.

Insert price for all items everyday
Use AVG() for value
Delete prices over 180 days.


Select all rows with same ID when 1 column meets criteria

There are a few ways that you can achieve this...

You can use a subquery to return the docNum of the rows that match the criteria from RDRO and then use an outer query to get the rest; like so:

SELECT r.docNum, r.itemCode
  FROM (SELECT DISTINCT docNum
          FROM rdro
         WHERE itemCode = 'DDS200') AS a
  JOIN rdro AS r
    ON r.docNum = a.docNum;

Or, as pointed out by Aaron, you can also use a WHERE EXISTS to achieve the same result:

SELECT r.docNum, r.itemCode
  FROM rdro as r
 WHERE EXISTS (
         SELECT *
           FROM rdro
          WHERE docNum = r.docNum
            AND itemCode = 'DDS200');

Or you can use a WHERE...IN:

SELECT r.docNum, r.itemCode
  FROM rdro as r
 WHERE r.docNum IN (
         SELECT docNum
           FROM rdro
          WHERE itemCode = 'DDS200');

Or you could even do it straight in the JOIN:

SELECT r.docNum, r.itemCode
  FROM rdro as r
  JOIN rdro AS o
    ON r.docNum = o.docNum
   AND o.itemCode = 'DDS200';

Heck, for completeness lets even throw in a CTE version:

 WITH docs
 AS
 (SELECT docNum FROM RDRO WHERE itemCode = 'DDS200')
  SELECT r.docNum, r.itemCode
    FROM RDRO AS r
    JOIN docs AS d
      ON r.docNum = d.docNum;

Try each one and review the execution plans that are generated for them before choosing which one to go for. Normally if two query plans are the same I choose the query that is easier to maintain.

Example of execution and results can be found on sqlfiddle.


How to identify all queries in a pending transaction in Oracle?

I'm pretty sure you can only view the most recent SQL of the transaction. Oracle records transactions at the block level, not at SQL level, so this information is not retained.

To see the most recent SQL, join the SES_ADDR column of v$transaction with the SADDR column of v$session. e.g.

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk, b.status
  FROM v$session a, v$transaction b
  WHERE a.saddr = b.ses_addr
  order by b.used_urec;

You can then join the sql_id from v$session with v$sql to see the most recent SQL for the session. e.g.

select sql_id,sql_text from v$sql where sql_id in
    (select sql_id from v$session where sid=&sid);

You'd need to join the sessionid with the dba_hist_active_sess_history table to see more SQL executed by the session. Even then it will not neccessarily be a complete list, as this view works by periodic polling.

You could use tracing or auditing if you wanted a list of all SQLs for specific sessions, but again this would not be recorded at the transaction level. Perhaps you could compare this result with the start_date or start_scn from v$transaction to figure out which SQLs belonged to which transaction.


1822, "Failed to add the foreign key constraint. Missing index for constraint

Your problem with the table photos

is that you want to make qa reference to photo varchar(255) NOT NULL

But in mysql you need that column to be INDEXED
So make your query like this

cursor.execute("""CREATE TABLE IF NOT EXISTS users(
id int AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER NOT NULL UNIQUE,
goal VARCHAR(255) DEFAULT NULL,
gender VARCHAR(255) DEFAULT NULL,
dob DATE DEFAULT NULL,
country VARCHAR(255) DEFAULT NULL,
city VARCHAR(255) DEFAULT NULL)""")

cursor.execute("""CREATE TABLE `IF NOT EXISTS photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `photo` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`,`photo`),
  KEY `id` (`id`),
  KEY `photo` (`photo`),
  CONSTRAINT `photos_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
""")

cursor.execute("""CREATE TABLE IF NOT EXISTS avatars (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER,
avatar VARCHAR(255),
shower_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
FOREIGN KEY (avatar) REFERENCES photos (photo) ON DELETE CASCADE
,FOREIGN KEY (shower_id) REFERENCES photos (id) ON DELETE CASCADE
)""")

there was another problem, that you referende the same userid to user_id and shower_id, that also gets you an error, bit i beleive you are missing only a showwer table.


How to write "Verify backup when finished" in T-SQL?

You can use ola.hallengren Backup scripts and schedule the job once you have created the required scripts from Ola's link:

In job you can use:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = '\\Server1\Backup',
@BackupType = 'FULL',
@Verify = 'Y'

Here as you can see Verify='Y' will do the job in addition to backing up the user databases.

The best approach would be above as you can log the history in table as well for the verification that is being done by using extra parameter in above code:

i.e. @LogToTable='Y' which Log commands to the table dbo.CommandLog.

Apart from this if you still like T-SQL that would be

RESTORE VERIFYONLY FROM DISK = B:\databasebackup.BAK
GO


Using sys.diana and sys.pidl to parse PL/SQL

The official documentation covering unusual errors describes DIANA:
PL/SQL is based on the programming language Ada. As a result, PL/SQL uses a variant of Descriptive Intermediate Attributed Notation for Ada (DIANA), a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA is used internally by compilers and other tools.

At compile time, PL/SQL source code is translated into machine-readable m-code. Both the DIANA and m-code for a procedure or package are stored in the database. At run time, they are loaded into the shared memory pool. The DIANA is used to compile dependent procedures; the m-code is simply executed.

I see why you are interested.  Access to the compiler underlying PL/SQL is tantalizing if you like looking under the hood.  Nonetheless I would be reluctant to invest a lot of time
when the same goal can be accomplished by parsing with PL/SQL.

As someone who has used your site many times I can only thank you for having an inquiring mind.


Select sales from different stores and group by product

Take sales out of the group by

SELECT ST.Name, sum(S.Qty) as Sales, P.Description
FROM SalesTable S
INNER JOIN StoresTable ST ON S.Store = ST.Name
INNER JOIN ProductsTable P ON S.Product= ST.Description
group by ST.Name, P.Description


multiple case statements using multiple columns

CREATE TABLE #three
(
  colA varchar(32),
  colB varchar(32)
);

INSERT #three(colA, colB) VALUES
  ('abc', 'xyz'),
  ('def', NULL),
  (NULL,  'ghi'),
  (NULL,  NULL);

SELECT ColA, ColB,
  ColC = LTRIM(COALESCE(colA, '') + COALESCE(' '+colB,''))
FROM #three;

DROP TABLE #three;

Results:

ColA  ColB  ColC
----  ----  -------
abc   xyz   abc xyz
def   NULL  def
NULL  ghi   ghi
NULL  NULL


NULL out value in table 1, depending on a time-log in table 2

This will get you the result you want, but it's a strange requirement.

If you want to make it fast, we'd need a lot more information, like table and index definitions, query plans, etc. "It's slow" isn't particularly helpful.

CREATE TABLE #crap1 (myDateTime TIME, Produced INT)

CREATE TABLE #crap2 (myStartDateTime TIME, myEndDateTime TIME )

INSERT #crap1 ( myDateTime, Produced )
SELECT *
FROM (
        VALUES('12:01', 100),
              ('12:02', 110),
              ('12:03', 115),
              ('12:04', 100)
     ) x (myDateTime, Produced)

INSERT #crap2 ( myStartDateTime, myEndDateTime )
SELECT *
FROM (
        VALUES('12:02', '12:03'),
              ('12:47', '12:58')
     ) x ( myStartDateTime, myEndDateTime )

SELECT c.myDateTime, CASE WHEN c2.myStartDateTime IS NULL THEN c.Produced
                          ELSE NULL
                     END AS Whatever
    FROM #crap1 AS c
    LEFT JOIN #crap2 AS c2
    ON  c.myDateTime >= c2.myStartDateTime
        AND c.myDateTime <= c2.myEndDateTime;


The openark kit by Shlomi Noach could be right up your alley!

From the introduction here:

oak-chunk-update: perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.

From the documentation page here:

  This utility allows for splitting long running or non-indexed
  UPDATE/DELETE oprations, optionally multi-table ones. Long running
  updating queries are often used. Some examples:

Purging old table records (e.g. purging old logs).
Updating a column on a table scale.
Deleting or updating a small number of rows, but with a non-indexed search condition.
Inserting into one table aggregated values from other tables.

The tool has a load of options with examples - I was very impressed when I used a couple of his tools for various bits and pieces.

So, kick it off with cron at 03:00 in the morning and relax!


Oracle 11G R2 install using separate instances or multiple schemas in a single instance

One instance should be able to do the job.  I'd recommend simply using AMM and ASMM.  If your DB is on a server dedicated as the DB server (i.e. not running web services or other software), you might try setting:

memory_target = memory_max_target = sga_max_size = 50% to 60% of your server's physical memory.  If this is less than 1500M, you're probably going to have pain. If the server is Unix or Linux, you could push this even to maybe 75%.
sga_target = pga_aggregate_target = db_cache_size = shared_pool_size = shared_pool_reserved_size = large_pool_size = java_pool_size = 0. Setting these to 0 allows Oracle to size them as needed.  Oracle will steal from db cache when it needs more memory for cursors, for example.

If one of your problems is that one of your customers floods the database with too many connections, you would do best to find a way to limit this behavior, ideally at the app server if you can configure db connection pool max sizes.  If you can't change this on the app server, then it can be done at the DB side.  For example, you could allow each schema/user to have maximum 200 connections to the DB.  Beyond that limit, the offending schema gets denied additional sessions, but the other schemas remain healthy.


Protect table from wrong data using locks or transactions

Plan A:  Add FOR UPDATE to the SELECT.

Plan B:  Add UNIQUE(flat_id, creator_id, is_reserved)

If neither of those work, then I am confused by your business logic; please explain further.


Capturing SQL calls sent to a remote server from application

Depending on your SQL version, you could run a Query Trace or an Extended Event to capture the TSQL being run by your app.


Beginner problems administering users on mysql

You obviously have anonymous users in mysql.user

Just run the following:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE host='';
FLUSH PRIVILEGES;

I have past posts on this subject

Aug 14, 2011 : Cannot drop anonymous user from mysql.user
Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Apr 09, 2012 : Mysql users deleted
Jun 09, 2012 : Why ALL PRIVILEGES ON `db`.* gives access to all databases?
Jun 26, 2012 : Best practices to secure mysql

Another observation: You tried to run mysql> drop user project;

By mysql's viewpoint, a user is identified as user@host

To see all defined users, please run this query:

SELECT CONCAT('''',user,'''@''',host,'''') MySQLUser FROM mysql.user;

You should see something like this:

+--------------------------+
| MySQLUser                |
+--------------------------+
| 'advdb'@'%'              |
| 'anadb'@'%'              |
| 'dmvadb'@'%'             |
| 'dmvanswers'@'%'         |
| 'dmvdb'@'%'              |
| 'dmvqnadb'@'%'           |
| 'icdb'@'%'               |
| 'jjohnston'@'%'          |
| 'localdb'@'%'            |

Thus, when you drop a user, it is best to use this style format as shown.

For example, to drop user icdb, you must run

DROP USER 'icdb'@'%';


How to optimize performance for dimension attributes?

MSDN states:

  The value of the AttributeHierarchyEnabled property determines whether an attribute hierarchy is created. If this property is set to False, the attribute hierarchy is not created and the attribute cannot be used as a level in a user hierarchy; the attribute hierarchy exists as a member property only. However, a disabled attribute hierarchy can still be used to order the members of another attribute. If the value of the AttributeHierarchyEnabled property is set to True, the value of the AttributeHierarchyVisible property determines whether the attribute hierarchy is visible independent of its use in a user-defined hierarchy.

IsAggregatable

  By default, an (All) level is defined for all attribute hierarchies. To disable the (All) level for an enabled attribute hierarchy, set the value for this property to False.

  If we set AttributeHierarchyEnabled to False, we cannot add that Attribute to a user defined hierarchy, we restrain SSAS from making any MOLAP-storage optimizations for this attribute. When we use such attributes in an MDX query as Attribute Hierarchy, we get no results. Although we can still use this as a member property in a query using

 Member_Expression.Properties(Property_Name [, TYPED])

According to Dustin Ryan's post:

  This will prevent end users from slicing with the attribute, reducing storage requirements and increasing performance.

If we set AttributeHierarchyVisible to False, the Attribute Hierarchy is active but stays hidden when a user browses the cube while you can still use it in MDX query.

If we set IsAggregatable to False, SSAS does not create the aggregated All member for us for that attribute.

Relevant MSDN thread that might be useful:
Dimension attribute with AttributeHierarchyEnabled=true and AttributeHierarchyVisible=false


Issue moving database between servers (cannot find .frm file even though it's there)

PROBLEM
You have a file permission issue.
The INFORMATION_SCHEMA database will not notice when doing just show tables; because it will scan for the .frm's existence. It's when the storage engine attempts to access the physical files (.MYD and .MYI') that mysqld will complain. All physical files of any table must have mysqlas its owner. When the error saysCan't find file: './roster/users.frm' (errno: 13)`, it really means it cannot find the table's physical files with proper file permissions.
SOLUTION
Change the owner of the roster folder and its contents to mysql.
GIVE IT A TRY !!!


Try the query below:

SELECT (count(*)*0.0078125) AS 'Pages in Buffer pool'
    ,CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 1 DESC

This will show you the amount of space each DB has occupied in the buffer pool. Note that there are other factors in SQL that also use up the memory apart from the buffer pool.


How to design sql database with a subset of tables for each row in a main table?

I'd go for something like:

restaurants table (stores all of your restaurants):
restaurant_id INT PRIMARY KEY
restaurant_name
etc

menus table (stores all of your menus):
menu_id INT PRIMARY KEY
menu_name

restaurant_menus table (stores which restaurants serve which menus):
restaurant_id INT, -- references restaurants.restaurant_id
menu_id       INT  -- references menus.menu_id

ingredients table (stores all available ingredients):
ingredient_id INT PRIMARY KEY
name
etc

menu_items table (stores each available menu item):
menu_item_id INT PRIMARY KEY
name
etc

menu_item_ingredients table (stores which ingredients make up a given menu item)
menu_item_id  INT, -- references menu_items.menu_item_id
ingredient_id INT, -- references ingredients.ingredient_id
name
quantity
etc

menu_item_items (stores which items are on a given menu):
menu_id      INT, -- references menus.menu_id
menu_item_id INT -- references menu_items.menu_item_id

menu_item_items and menu_items probably need better names (dishes for menu_items, perhaps?).


Help on designing SSAS Dim and Fact table

You want your Users table to be just the Self records, as a dimension table. Phone number should be a column here. Then you want your Relationships table to be your Fact table, hooked into the Users Dimension table twice, once through Original User and once through Related User.

For your main report, pull the Phone number from the Related User dimension, not the Original User dimension.


Query that ranks students order by marks and give the position of student even when there are students with equal marks

SELECT t1.id, mark,
(SELECT COUNT(*) FROM marktable t2 WHERE t2.mark > t1.mark) +1
AS rnk
FROM marktable t1 order by rnk;

SQLFIDDLE


Instead of insert trigger VS For insert Triggers in SQL server

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

The INSTEAD OF trigger replaces the original insert. So the code:

Insert into dbo.TestTable (Name,Age,DOB,Address)
Select Name,Age,DOB,Address From inserted;

...is executed instead of the original insert:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

The actions in the instead of trigger are completed before constraints are checked, and before any AFTER triggers fire.

When the instead of trigger completes, a row has been added to the TestTable and CopyTable tables. Note though that the transaction is still active at this point, so the changes are not yet permanent.

Next, the after trigger fires. It sees the name 'Waleed' in the inserted table, prints a message, but does not rollback the changes made so far in the transaction (including the rows added by the instead of trigger). No row is added to the AuditTable.

If the condition that checked for the name 'Waleed' also performed a rollback, everything would be rolled back, and no rows would be added to any table.


How do I sort the results of a recursive query in an expanded tree-like fashion?

An array representing the path from the root up to the leaf should achieve the desired sort order:

WITH RECURSIVE node_rec AS (
   (SELECT 1 AS depth, ARRAY[node] AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    LIMIT  10
   )
    UNION ALL
    SELECT r.depth + 1, r.path || n.node, n.*
    FROM   node_rec r
    JOIN   nodes    n ON n.parent = r.node
    WHERE  r.depth < 4
)
SELECT *
FROM   node_rec
ORDER  BY path;

  If two or more child nodes have the same parent node, I want them to
  be sorted by their timestamp.

Shift the path by one towards the root and order by that column additionally:

WITH RECURSIVE node_rec AS (
   (SELECT 1 AS depth, ARRAY[node] AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    LIMIT  10
   )
    UNION ALL
    SELECT r.depth + 1, r.path || n.parent, n.*
    FROM   node_rec r
    JOIN   nodes    n ON n.parent = r.node
    WHERE  r.depth < 4
)
SELECT *
FROM   node_rec
ORDER  BY path, ts;


How to design a database for non-time invariant data

It sounds like a problem easily solved by placing key tables in version normal form (VNF). This is actually just a flavor of 2nf but done in a way that provides the ability to query current data and past data using the same query.

A datetime parameter is used to provide the distinction. When the value is set to NOW, the current data is returned. When the value is set to a specific datetime value in the past, the data that was current at that date and time is returned.

A brief discussion of the particulars can be found in an answer I gave here. That answer also contains links to more information if you think it is something that would work for you.


At present ( meaning as of writing ) there really isn't an actual "official release date" for MongoDB 3.0 or is there "really" an official source for the repository of binary builds for any platform.

Results vary for each distribution, but in this case you are probably best following the official documentation links and changing the version in the required "drop-down" to "3.0 (upcoming)" (and again, as of writing). The "repo" location links have been consistently changed over most OS distributions as of the 3.0 pending release. Url's in the documentation likely need "massaging".

Right now the RHEL/CentOS repo for current is here: http://repo.mongodb.org/yum/redhat/7/mongodb-org/testing/x86_64/RPMS/

Following that you can install a "binary build" of the "current release candidate" for your own usage. Failing that then just go for something like: https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-rhel70-3.0.0-rc11.tgz and deploy according to your platform mapping of locations and configurations.

When it's released then it will be released. Contact your local MUG for more info if someone is prepared to give it to you. Otherwise just live with "Soon".


T-SQL query to find backup folder for specific maintenance plan

You can use the available views in msdb to find the information regarding backups :

dbo.backupset:  provides information concerning the most-granular details of the backup process
dbo.backupmediafamily:  provides metadata for the physical backup files as they relate to backup sets.
dbo.backupfile: this system view provides the most-granular information for the physical backup files

You can use below T-SQL code to list down all required info:

    SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date,
    msdb.dbo.backupset.expiration_date,
    CASE msdb..backupset.type
    WHEN 'D' THEN 'Database'
    WHEN 'L' THEN 'Log'
    END AS backup_type,
    msdb.dbo.backupset.backup_size,
    msdb.dbo.backupmediafamily.logical_device_name,
    msdb.dbo.backupmediafamily.physical_device_name,
    msdb.dbo.backupset.name AS backupset_name,
    msdb.dbo.backupset.description
    FROM   msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =    msdb.dbo.backupset.media_set_id
    WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
    ORDER BY
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_finish_date


convert default charset utf8 tables to utf8mb4 mysql 5.7.17

5.5 and 5.6 had the 191 problem.  5.7 solved the problem.

See this for discussion of multiple workarounds in 5.5/5.6. Both articles you reference is dated, since they discusses 5.5.  (Note that I have already commented on both.)

I recommend you change 255 to some reasonable limit for each column, anyway.

A caveat with ALTER .. CONVERT TO .. -- If country_code were what it should be, namely CHARACTER SET ascii; the ALTER would change it to utf8mb4.

Q2...

The CHARSET of a column matters.  The CHARSET of a table is just the default for the columns.  Ditto for the database and any newly created table.

I prefer not to depend on such defaulting, and I explicitly state the CHARSET on any table I CREATE.  Others may prefer to do a ALTER DATABASE.


SSRS 2017 - Client found response content type of '', but expected 'text/xml'

I couldn't figure out the specific failure reason here, but found that if I switched to a domain account instead of "Virtual Service Account" or "Network Service" built in to Report Service Configuration Manager and then restarted the machine, things started working properly


That error is nonsequiter with regards to MySQL. This would especially be true if your Windows server crashed for reasons other than MySQL. There are ways to fix IRQL_NOT_LESS_OR_EQUAL.
However, if you are reloading a mysqldump and it is the only reason Windows crashes, you may have to accommodate Windows until you fix the IRQL_NOT_LESS_OR_EQUAL error by applying one of the following suggestions (but not both):
SUGGESTION #1 : Change the mysqldump layout
You may have to create another mysqldump using --hex-blob or --skip-extended-insert but not both. That way, MySQL will ingest data in bite-sized chunks instead of woofing down thousands of rows of data. See my answer to the post When does creating a .sql backup with mysqldump become too large? for clarification.
SUGGESTION #2 : Change the MySQL Packet size
Most people take the MySQL Packet for granted. Some mysqldump could time out if you not paying attention. mysqldump's default for max-allowed-packet in 24M. Extended inserts can benefit from a larger packet upon reload. It could possibly increase the number of rows per extended INSERT command. Since a mysqldump can trigger a crash, you could experiment with  slightly larger values (the max size is 1G). The MySQL Packet never starts out at max_allowed_packet. The size starts out at net_buffer_length and expands to max_allowed_packet on demand.
EPILOGUE
These two suggestions is really no more than a BandAid for your scenario. You need to do your homework and fix the IRQL_NOT_LESS_OR_EQUAL first before resorting to workarounds.


Reverse pattern matching: find the longest prefix

Something like this should work:

SELECT *
FROM prices
WHERE '18127551221' LIKE "Code" || '%'
ORDER BY "Code" DESC
LIMIT 1


Set indexes for database used for login and register actions

I think you don't need to have any indexes (Rather than primary key) for user table which is mainly used for authentication, because most of the fields in user table has unique rows (correct me if i am wrong), so Cardinality is very high. If you index high cardinality field it might be a performance hit. You can go through this article for more information about cardinality and indexing


MySQL 5.7 performance issue with 300 GB Innodb

I think you have found an example of "over-normalization" and why it is bad to "normalize continuous values" such as dates.

I recommend you abandon date_tbl and pull a date into sales.  Also get rid of separate columns for year_no and month_no; instead compute them as needed.

Once you have done that, some of these may be useful:

INDEX(cal_date)
INDEX(brand, cal_date)
INDEX(zone, cal_date)

It depends on what ? are likely to be.  If, say, brand is frequently a single item, then (brand, cal_date) is likely to be the most useful.

Have you measured any improvement after OPTIMIZE TABLE?  I doubt it.


I can see lot of Cannot be found messages in errorlog like

(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\SQLSysClrTypes\x86\SQLSysClrTypes.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\SQLSysClrTypes\SQLSysClrTypes.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSSetup\x86\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSSetup\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSIntSetup\x86\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\redist\VisualStudioShell\VSIntSetup\vs_setup.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\x64\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\x86\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\x64\setup\unused' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\sql2008support\x64\SqlSupport.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\sql2008support\SqlSupport.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\x64\SqlDom.msi' does not exist
(01) 2015-01-28 18:13:57 Slp: Sco: File 'E:\1033_ENU_LP\x64\setup\SqlDom.msi' does not exist

This makes me think that the package is nt complete and while you were converting ISO file to executable file all files related to installation were not extracted.

You are trying to install SQL Server 2012 enterprise and Windows 8 machine not windows server. Its not supported on windows 8 please see hardware and software requirements but it wont stop you from installation.

If you are installing SQL Server 2012 on windows 8 you MUST read this support article before proceeding


Why would there be varying response times for the same query?

After investigation of the historical load, we have found out that the provisioned IOPS we originally configured had been exhausted during the last set of load tests performed on the environment.

According to Amazon's documentation @http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html, after this point, Amazon does not guarantee consistency in execution times and the SLAs are no longer applicable.

We have confirmed that replicating the database onto a new instance of AWS RDS with same configuration yields consistent response times when executing the query multiple times.


Is there a more efficient way to apply a new schema to an existing collection when there's existing data?

You can do some limited adjustments to the schema with ALTER XML SCHEMA COLLECTION.  Here's a simple example:

USE tempdb
GO

IF OBJECT_ID('dbo.users') IS NOT NULL DROP TABLE dbo.users
GO
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
DROP XML SCHEMA COLLECTION xsd_test
GO

-- Create a simple schema
CREATE XML SCHEMA COLLECTION xsd_test AS '<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="users">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="user">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

-- Create a table with XML bound to that schema
CREATE TABLE dbo.users ( userId INT IDENTITY PRIMARY KEY, userXML XML(xsd_test) )
GO

-- Populate the table
INSERT INTO dbo.users ( userXML )
VALUES
    ('<users><user name="Sinaesthetic"/></users>' ),
    ('<users><user name="wBob"/></users>' )
GO

-- Alter the schema
IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test')
ALTER XML SCHEMA COLLECTION xsd_test
ADD '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="skills">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="skill">
                    <xs:complexType>
                        <xs:attribute name="name" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'
GO

UPDATE dbo.users
SET userXML.modify('insert <skills><skill name="someSkill"></skill></skills> after (users)[1]')
WHERE userId = 1


First You have to restore your backup file which was taken at time A , then
you have to read all MySQL binary logs using mysql utility mysqlbinlog.

For example, if your log files were numbered 1 to 49 before the backup and logs 50 to 52 were written after the backup,you will need to process logs 50 to 52 after restoring the backup. If your binary logs are named with a prefix of bin, the log processing command looks like this:

shell> mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql

All the binary log files that you want to process should be handled in a single mysqlbinlog command. There may be inter-file dependencies that will not be satisfied if you process them separately.

If a given binary log file was in the middle of being written during backup, you must extract from it only the part that was written after the backup, plus all log files written after that. Also, if you accidentally drop a table (or database, or some other act that causes data corruption), then you want to restore your backup and the incremental activity in binary logs.

If you do not want to re-execute the dropped table, you can run everything up to the bad command by capturing the binary log file only up to that point. To handle partial-file extraction, mysqlbinlog supports options that enable you to specify the time or log position at which to begin extracting log contents:

The --start-position option can be used to specify extraction beginning at a given log position.

After restoring a backup file, use the position numbers to process the binary log file. For example, you would use commands something like these:

mysqlbinlog --start-position=23456 binlog.000004 | mysql -uroot -p


Adding a field to a table (but in another database)

You might be able to get away with just adding a new schema to the existing database, but either way the solution is approximately the same.

In order to avoid interfering with the dacpac deployment, you may want to avoid actually implementing a true foreign key constraint.  You can either make a stored procedure do the inserts/updates and have it manually do the constraint checking before inserting a new row.

But basically, create a table with a non-incrementing primary key (that should have a 1:1 with the source table.  Then add your custom fields to that table.  You can use a view to merge them together to make it easier to digest the contents if you want.

CREATE TABLE dbo.Extension
    (
    DatasetID INT NOT NULL PRIMARY KEY --make sure to match type to source
    , InventoryCount INT NULL
    )

As I said, if you can add it as a custom schema (assuming that survives the dacpac update process the vendor is using) then it can live inside the same database (my preference).  But you can add it into a new database if you want as well.


Find most frequent values for a given column

get the most frequently appearing user_code where the month is specified

Since user_code is the primary key, that question would be nonsense. There can never be more than one. I assume you meant invite_code?

Just add a WHERE clause. And since the column can be NULL, also consider excluding NULL values:

SELECT invite_code, COUNT(*) AS counted
FROM   invite_table
WHERE  month = 'May'  -- or whatever is stored in your varchar(3) column
AND    invite_code IS NOT NULL -- exclude NULL
GROUP  BY invite_code
ORDER  BY counted DESC, invite_code  -- to break ties in deterministic fashion
LIMIT  10;

Month, date, timestamp?

A month column as varchar(3) doesn't seem very useful if there can be data for more than a single year. I would use data type date for it. You can format that with to_char() any way you like for presentation. Like:

SELECT to_char(date '2017-12-01', 'Mon');  -- 'Dec'

The column could look like this (also addressing your comment):

...
, inserted_at date DEFAULT CURRENT_DATE
...

The default value is entered when the column is omitted in an INSERT statement.

Or, if really only the month is relevant:

... DEFAULT date_trunc('month', now())::date

Or store the complete timestamptz (8 bytes, that's what I would probably do):

...
, inserted_at timestamptz DEFAULT now()
...

Read the manual here and here.

And be aware that date and timestamp depend on your current time zone setting. Details:

Ignoring timezones altogether in Rails and PostgreSQL


MS SQL - Create trigger to prevent insert if it doesn't match specific parameters

I think something like this would solve your problem.  If a payment being inserted has a batch_id that points to a batches row where the batch_type <> 'P', the transaction is rolled back.

CREATE TRIGGER [dbo].[Payments_PreventBatchThatAreNotP] ON [dbo].[Payments]
AFTER INSERT
AS
SET NOCOUNT ON;

IF EXISTS (
        SELECT *
        FROM inserted p
        JOIN dbo.Batches b ON b.batch_id = p.batch_id
            AND b.batch_type <> 'P'
        )
BEGIN
    RAISERROR ('Batch type not equal "P" - transaction rolled back',16,1);

    ROLLBACK
END

GO

ALTER TABLE [dbo].[Payments] ENABLE TRIGGER [Payments_PreventBatchThatAreNotP]
GO


I cannot login to a user I just created in a PDB

You are connecting to root container by using sqlplus testtest/password where the user doesn't exist.

Instead, you can use EZConnect or you can create a TNS name to connect to the PDB.

Example:

EZCONNECT

[oracle@host ~]$ sqlplus testtest@hostname:port/bpdb1

TNS Name

First, find out the service name of the PDB, one of the methods has been shown below.

 [oracle@host ~]$ lsnrctl status listener_name

Service "orclpdb" has 1 instance(s).
  Instance "oracdbuat", status READY, has 1 handler(s) for this service...

In my case, it's orclpdb.
Go to $ORACLE_HOME/network/admin.
Edit/Create tnsnames.ora file to add a similar entry as shown below.

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb)
    )
  )

Now, I can connect to the PDB using the TNS name.

[oracle@host ~]$ sqlplus testtest@orclpdb


For me the way to go is that you create a pre-insert trigger that will set the BILL_ID for that department when no BILL_ID was given. To get the next BILL_ID you could use a table that holds the last BILL_ID per department. An other way is to get the MAX(BILL_ID) for that department and add 1 to it. When you work with multiple users then the first solution is better and faster. The primary key for the BILLS table would be DEPARTMENT_ID, BILL_ID and perhaps even YEAR if you need to start at 1 when the new year starts.


For your first database class and first attempt at an ER Diagram (ERD) I think you have done a great job!  I'd like to give you some feedback in the context of the process I use to break down a set of requirements like you were given and create a draft diagram.  Hopefully by taking this approach I'll help you in your development of the skill of ER modeling and database design and not just give you an answer to a homework assignment.
Find the Entities and Relationships
The idea behind an ERD is to identify the entities - things of fundamental significance to the business - and how they are related.  Thus the name Entity-Relationship Diagram.  The goal at this point is not to design or implement a database but instead to develop a more structured understanding of the domain of interest on which a database will ultimately be based.  A good way to get a start on finding the entities is to look for the nouns in the requirements and then pick out those which are persons, places, things, concepts, or events.  Then, you can find most of the relationships by looking for the verbs that connect those nouns.  Here is my first pass at finding entities and relationships in the requirements provided:

I highlighted the nouns in a bright green, and the verbs in a bright pink.  Now notice there is one noun - supervisor - that is highlighted in a darker green.  We'll ignore that for now.  Comparing what I found to what you placed on your ERD we are on the same page!  The only difference is that I also highlighted location.  I can see why this was left off of your ERD as its not clearly an entity.  You could just as easily think of it as an attribute - which is a common property or characteristic that all entities in an entity type share - of the department and the project and that is how you modeled it.  The reason I elevated it to an entity type is this:

A dept may have several locations

This indicates that each department will be associated to one or more locations, and thus necessitates breaking this out to its own entity type.  A second reason to elevate it is that we can think of a location as a place and as such would have its own attributes such as name, address, city, state, zip, and so on.  A third reason to elevate it is that the location is referenced by more than one entity type - the department and the project.  If you find the element in question is referenced by more than one already identified entity, you should consider it an entity in its own right that is related to the others, and not simply an attribute of the others.  There is no science to this however - its purely a judgment call.  This is why ER diagramming is at the conceptual level as its subjective to individual perspectives.  One person's entity is another person's attribute depending on their perspective into the domain of interest.
Regarding the relationships, here is a list of what I highlighted in pink:

Employee manages Department
Department controls Project
Employee assigned to Department
Employee works on Project
Employee has Dependent

Of these, you identified all but the first one.  This, along with the supervisor I mentioned earlier, gets into a discussion of roles which we'll postpone to later.  Right now we are just trying to identify the fundamentals and I'd say you were right on.
Associative Entities
Looking at the relationships we have a few that are many to many.  The requirements state that an employee can work on many projects, which are not necessarily controlled by the same department.  Given that most projects have more than one member, we can safely assume this is a many to many relationship.  When you have such a relationship on an ERD, it is perfectly acceptable to show a many to many relationship line.  I typically only use the many to many relationship though if I'm not showing attributes at all.  Since we are showing them here, I prefer to resolve the many to many relationship with an associative entity.  Even if there are no attributes for the entity now, we may uncover some as analysis proceeds.  However, in the case of employees and projects, we have attributes - the hours worked - that must be added for that relationship and so it must be resolved by creating the associative entity to show them.  Thus we will create an associate entity called Project Assignment.  But we are still not done.  The requirement calls for us to know the hours worked per week.  When you think about it, you realize there would be many weeks in the life of the project, and we need to record the hours worked by that employee on that project each week.  Thus another entity type is required which I called Hours Worked of which there will be many occurrences per project assignment.  This entity type will hold the week ending date and the hours worked.
Next, by creating a location entity type, the requirement that states a department may have several locations means we now have a relationship where one department can have many locations.  It is safe to assume as well that one location will have many departments operating there.  Therefore I added another associative entity called Operating Site to show this.
Find the Attributes
The attributes were listed pretty clearly in the requirements with statements like:

Each department has a name, an unique number,
keep track of the start date when the employee began managing the dept
projects, each of which has a name, unique number,
store each employee’s name, SSN, address, salary, sex, and DOB.
keep track of the number of hours per week that an employee works for each project.
keep each dependent’s first name sex, DOB, and relationship to the employee.

What I do at this point is list out the entities and relationships identified and place the attributes identified with the entity type it belongs to:

Department - Number, Name
Employee - Name, SSN, Address, Salary, Sex, DOB
Location - ???
Project - Number, Name
Dependent - First Name, Sex, DOB, Relation

Note I can't find any attributes for location.  This would be a red flag to go back to the business to get some additional information about locations!
Then list the relationships and their attributes:

Employee manages Department - Start Date
Department controls Project -
Employee assigned to Department -
Employee works on Project - Hours Per Week
Employee has Dependent -
Department has Location -

For those last two relationships the verb has isn't very descriptive, but that was how the requirements were phrased.  The entity has an attribute, so why wouldn't these be attributes?  I discussed earlier why I would choose to make location an entity type.  For dependent, the choice is more clear as its clear each employee has many dependents and so it must be broken out into its own entity.  Once this is done we could make the leap to improve the verb.  Perhaps something like employee cares for dependent and department operates at location.
Now we are in a position to create the diagram.  Here is what I have:

This was created using Oracle SQL Developer Data Modeler - a free download - which is a great tool if you want to continue the process into database design and creation.
Primary Keys
You have identified primary keys.  Technically, an ERD does not require you even consider keys.  If you think of the ERD as a tool to model the business, its not important to determine at this point exactly how you will uniquely identify an occurrence of each entity.  Instead, you can assume that at a later point you will figure this out, and instead focus only on the entities, their relationships, and their attributes.  It is a good idea to note which attributes are unique for each entity as this will help choose keys.  Once you complete the diagram and iterate upon it with the business, you can go back and complete it by choosing the right keys.  In my diagram I only noted that the two numbers mentioned as unique in the requirements were unique on the diagram using a U beside the attribute name.
Entity Roles
Now to address the dark green highlighted supervisor I mentioned earlier.  The requirements stated that "a department has an employee who manages the department."  They go on to state "We also keep track of the direct supervisor of each employee."  So what we have here is an entity - a person - who is playing different roles.  The employee might be a manager.  The employee might be a supervisor.  So how do we resolve this?  First, we need to decide if the manager and the supervisor are the same thing!  Is it the case that the manager of a department is also the supervisor of the employees assigned to it?  If yes, the solution is simple.  A new entity type called Assigned Manager can be created with a one to many relationship from department to it and from employee to it, with a single attribute of the start date when that employee started managing that department.  Then, each employee's supervisor is assumed to the the manager of the department they are assigned to.  If no, then we can add a recursive relationship from employee to employee to represent the the supervisor and the employee.  This is the simplest approach but does introduce a mixing of roles in the same entity type.  A better approach is to add a new entity type called Assigned Supervisor with two one to many relationships from employee to it - one to represent the supervisor and the other to represent the supervised. If there is a date when the assignment began and ended this could be added as well.
Conclusion
Hopefully this, coupled with the description of the process used to develop it, will be a good learning tool for you to understand how you get from a description of a business process to a draft ERD.  I say draft because it is just a starting point.  Once completed, it serves as a communication vehicle to validate the requirements, find holes in them, uncover new ones, and so on, before any programming or even system design begins.  Remember that doing a great job building a system that implements requirements that were mis-understood will be a failure and it will be much cheaper to modify an ERD than a working system!
References
Two really good reference for ER diagramming are Steve Hoberman's Data Modeling Made Simple, which is a great overview, and David Hay's Enterprise Model Patterns, which gives a great in depth look at common patterns you find when analyzing organizations.  Both of these references give much more detail on describing relationships using verb and prepositional phrases, identifying and non identifying relationships, and strong and weak entities - concepts I didn't address.  Fabian Pascal's Practical Database Foundation Series is also excellent, and has a great first paper that is the perfect compliment to the books as Fabian describes the entire process of determining data requirements.  Remember that ER diagrams can only show keys and references, whereas in fact there are many more kinds of business rules that can be explored and implemented in the ultimate database.


Transactional replication upgrade from SQL Server 2017

Rolling back an in place upgrade on a distributor is pretty similar to any failed in place upgrade. Which is to say, it's not usually quick and simple.
If the failure is during the install, you'll need to uninstall, reinstall, and revert to backups. With replication, msdb is very important, in addition to the distribution database, in order to restore all of the jobs. VM-level snapshots can be very useful here, as that can make reverting easier, without having to do messy uninstall/reinstall. Depending on your VM configuration, VM snapshots might save you from having to restore databases, too.
If the failure/reason to rollback happens after upgrade, when you've re-enabled any of the agents, then restoring from backup won't be enough. Data that is being distributed to subscribers or read from the publisher logs after upgrade, before rollback would cause data inconsistencies. In that case, you'll also need to re-snapshot your publications on rollback.


What does 'Vacuum' do in Menial's Base 2 application?

DISCLAIMER : Not a Menial Base 2 person
VACUUM is a sqlite operation for defragmenting tables and tablespaces. It also helps reclaim diskspace.
It defragments all sqlite tables by copying data from the sqlite tables to a temp tablespace. The VACUUM operation will then swap out the old tablespace and use the new one. Database pages lost due to DELETEs and UPDATEs will no longer exist.
MONyog also uses sqlite tables for collecting database statistics and features a VACUUM operation.


Fan trap and redundant relationships issues


MySQL General error: 126 Incorrect key file for table '/tmp/#sql_27e6_0.MYI'; try to repair it'

You can remove any /tmp/#sql* files -- they are tmp tables left over after a crash.  Or they are part of something (ALTER, CREATE, etc) that is going on, but stuck waiting for disk space.

It is often a mistake to have a separate filesystem for tmp -- you run into problems like this:  Plenty of space to finish a task, but not in /tmp.  Consider changing mysql's tmpdir setting.


Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

Check the CREATE TABLE page of the manual:

  There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE
  (which is the default). MATCH FULL will not allow one column of
  a multicolumn foreign key to be null unless all foreign key columns
  are null; if they are all null, the row is not required to have a
  match in the referenced table. MATCH SIMPLE allows any of the foreign
  key columns to be null; if any of them are null, the row is not
  required to have a match in the referenced table. MATCH PARTIAL is not
  yet implemented. (Of course, NOT NULL constraints can be applied to
  the referencing column(s) to prevent these cases from arising.)

Also, in the chapter on Foreign Keys:

  Normally, a referencing row need not satisfy the foreign key
  constraint if any of its referencing columns are null. If MATCH FULL
  is added to the foreign key declaration, a referencing row escapes
  satisfying the constraint only if all its referencing columns are null
  (so a mix of null and non-null values is guaranteed to fail a MATCH FULL
  constraint). If you don't want referencing rows to be able to
  avoid satisfying the foreign key constraint, declare the referencing
  column(s) as NOT NULL.

And be sure to consult the current manual or the version matching your installation. Don't fall for outdated Google links to outdated versions.


Inserting with implicit type conversion causes warning for cardinality estimates

Like other execution plan warnings, this one is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.

As a purely practical matter, that is pretty much the end of it. The precise conditions that trigger this optimizer warning are undocumented.

That said, I will go into a little detail to satisfy a little of your curiosity.

  1. An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice?

There are things going on of interest to the cardinality estimation component of the query optimizer. This component keeps track of a wide range of properties and other information for each logical operator in the query tree. This includes histograms (from disk, or derived entirely in-memory), domain information, functional dependencies etc.

In your specific example, the warning is triggered when the input to the convert is known to be monotone but the output is not. Conversion from integer literal to bigint retains this property. Conversion from bigint to nvarchar(50) does not.

When there is a single literal integer constant, parse-time constant-folding converts this to nvarchar before optimization starts. This avoids type conversion in the plan, and the associated warning.

There are detailed internal implementation differences between the original cardinality estimator and the new cardinality estimator that mean some statements will generate a warning under one CE but not the other.

  2. What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate?

In your example statements, none. As noted, the conversion can affect other internal data, which might affect e.g. other plan operators, or the code path taken through the optimizer. The broad point is that there is more than a raw cardinality estimate to consider. Internal differences can affect things like indexed view or computed column matching, among other possibilities.

  3. Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is?

This appears to be the same as the previous question.

  4. Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)

There is no option to turn off these warnings.


Low CPU Utilization but High Signal Waits

Community Wiki answer generated from comments on the question by Aaron.

Unless you are having performance issues, the high percentage of CXPACKET waits may only be an indicator that a large portion of the queries are going parallel and not actually a problem.

What is the CXPACKET wait type and how do you reduce it?

High CPU could be a indicator, but from what you have explained I would say look at queries with long durations but low CPU to start with. The CXPACKET wait is sometimes associated with a query waiting for all of the threads to finish before it can merge the results (data skew).

If you are able to modify the queries and procedures, you could set a higher MAXDOP for the warehouse tasks that need it, and set the global MAXDOP lower. However, I would only do that as a last resort. You only really want to make those types of explicit hints when you've exhausted all of the possibilities or you can't make changes to the code, queries or database schemas.


How to send email from SQL Jobs in SQL Server 2014

You need to first configure DB mail in SQL Server in order to utilise this functionality. You can use the below link as a reference for DB mail configuration purposes.
https://www.brentozar.com/blitz/database-mail-configuration/
Once DB mail is set up, you can enable database mail on SQL Server Agent. The same link describes how to do that. Once it is enabled on SQL Server Agent, you need to restart the SQL Server Agent service to make that take effect.
That's it. Now you can use the msdb.dbo.sp_send_dbmail SP to send email using a SQL Server Agent job or a stored procedure.


What is happening when Seconds_Behind_Master oscillates between two very different sets ov values?

I have seen this since about 2002.  Ignore it; it will go away.  I have failed to find out what causes it, and it so elusive that I could not get anyone to look at as a "bug".

I do not know if this will have any impact on innobackupex.


The audit data uses UTC time. See this link for suggestions to adjust your query.

SELECT DATEADD(mi, DATEPART(TZ, SYSDATETIMEOFFSET()), 'HERE goes your date')


No suitable driver found Orientdb jdbc driver

Apparently, the JDBC driver needs to be loaded before the connection is made by Class.forName("com.orientechnologies.orient.jdbc.OrientJdbcDriver").


If you want to see how the query will be run, you should put "EXPLAIN" before the "SELECT" and it will give you the execution plan.

There are three ways it could use the gin index:

It could look up just the location_id that you specify, then fetch those rows and filter out ones not containing 222.
It could look up just the idle_products you specify, then fetch those rows and filter out ones not containing one of your location_id list.
It could look up all the index entries for 222, and all the index entries for your location_id list, and take the intersection of those to go fetch from the table.

It will choose which of those it thinks will work better, based on what it knows about your data distribution.

Also, I don't find composite gin indexes to be useful.  Having separate indexes is more flexible, and PostgreSQL can still combine them (using BitmapAnd) when that is useful to do.  I'd replace the composite index with a ordinary btree index on location_id and a gin index on the array column.


How costly is opening and closing of a DB connection?

Think about the amount of memory being allocated per DB Connection. What things must be allocated? According to MySQL 5.0 Certification Study Guide, page 357:

  The server maintains several buffers for each client connection. One
  is used as a communications buffer for exchanging information with the
  client. Other buffers are maintained per client for reading tables
  and performing join and sort operations.

What settings govern the per-connection buffers?

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
tmp_table_size / max_heap_table_size
net_buffer_length / max_allowed_packet
thread_stack

It takes time to allocate and deallocate these buffers when a connection comes into being. Don't forget to multiple the sum of those values by max_connections. As a side note, please refrain from using mysql_pconnect as PHP and MySQL's persistent connections do not get along well. Here are two informative links on this topic:

Open bug on PHP Not Cleanly Closing MySQL Connections on Apache Death.
Requesting a Persistent ssh Connection.

In heavy-read, heavy-write environment, such as OLTP, this would be expensive in terms of RAM usage and possible inhibition due to swapping in the OS. On a low-write, low_read website, I would not worry as much.


I think you should analyze the scenario from a different perspective, because it looks quite likely that (a) continuing to work with the current structure of the table in question might end up being (b) far less feasible than (c) redesigning it at all the corresponding tiers, i.e.:

the conceptual (modeling and defining [i] the types of things that the table represents and [ii] the respective rules of the business context in which said types of things are involved with [ii] the appropriate precision);
the logical (reflecting the types of things of interest in a database structure with new normalized elements and accurate data types, and setting up the proper constraints so that the data contained complies with the pertinent business rules); and
the physical (establishing a proper indexing strategy based on the exact data manipulation tendencies, installing more powerful hardware, perhaps partitioning and sharding, upgrading the network bandwidth, etc.) levels of abstraction.

Keeping the present table structure (~500 columns!) demands approaching it (manipulation- and administration-wise, etc.) in non-efficient ways, and if you expect that the volume of data will grow considerably then the system will scale very poorly and could become inoperable. It might be much better to employ (1) all the effort devoted to (2) handling this non-optimal situation in (3) a major reorganization.

Of course, there well may be current aspects of the table that can be reused later (e.g., the quite valuable data, which probably needs a clean-up), but I would decidedly consider rearranging—at least partially, at the major hot spots— all the factors mentioned above, instead of putting up workarounds that are in fact palliatives. Better late than never.


SQL Server 2014 Reporting Services issues

Adding the account you are logging in on your OS will allow for you to connect to SSRS. You are provided with a 403 error message directly related to an access denied. The type of access being denied here is at the database level, mainly for the Reportserver$ database. Your OS login is unable to personify NT SERVICE since it does not have a login on the server and guest account being probably disabled.

Additionally, verify the following:
'C:\WINDOWS\Microsoft.NET\Framework\V2.0.050727\Temporary ASP.NET Files'. (or where ever your .NET framework is installed on your machine)

Right click on the "Temporary ASP.NET Files" and select properties->secutity.

See if the "NETWORK SERVICE" is already part of the "User/Group" section. If not add it by typing "Network Service".
If it's already there then grant the write permissions.


The choice of isolation level is driven by the guarantees your application requires from the database. Full ACID isolation is only provided by serializable isolation; all other isolation levels provide a lesser degree of protection for transactions.

  Read Committed admits inconsistent results in repeated reads

The only guarantee provided by read committed isolation is that only committed data will be read (dirty reads are disallowed). Anything beyond that is implementation specific.

For example, in SQL Server, read committed using row versioning (RCSI) also guarantees statement-level repeatable reads and a statement-level point in time view of the database. To be clear, neither of those are guaranteed across multiple statements within the same transaction under RCSI (this is provided by Snapshot Isolation).

SQL Server's implementation of read committed using locking allows repeated reads within the same statement to encounter different values, and allows the database to return data from different points in time.

To emphasise: a single statement that touches the same row more than once may encounter different values. The multiple reads may not be apparent from inspecting the SQL alone, since the query optimizer has considerable freedom in constructing a physical execution strategy.

  I can simply cache the first read result in my transaction and let other transactions to update...

This is a valid approach, if it meets the isolation requirements of whatever it is you are doing with (or based on) the data.

  Nothing bad can happen and I can feel as safe as Repeated Read isolation, right?

Not generally, no. Repeatable read (RR) isolation provides more guarantees than read committed (RC) isolation. Specifically, RR guarantees that data will not change for the life of the transaction once the data item has been read for the first time. RR does not guarantee that reads will in fact be entirely repeatable because new data may appear in subsequent reads (phantoms).

Note that implementations are free to exceed the base requirements of each isolation level, so some products may in fact provide equal guarantees under RC as RR (this is not the case for SQL Server). Indeed, an implementation could make all isolation levels equal to serializable, and still be in compliance with the standard.

  No, I guess that holding a read lock for the data I read helps to prevent some undesirable scenarios...

Locking is an implementation detail. It is better to think about the degree of isolation your use of the data requires to behave correctly in all circumstances (when multiple concurrent data changes are possible).

  ...to prevent some undesirable scenarios in booking reservations, probably. Which ones?

The ways in which multiple concurrent database transactions can interact unhelpfully are limited only by one's imagination.

For example, using RC and caching the value read could result in a lost update, where another transaction updates based on the stored value, before your transaction does the same thing. In terms of histories for two transactions running x:= x + 1:

transaction 1 reads x (=1)
transaction 2 reads x (=1)
transaction 2 writes [x:=x + 1] (=2)
transaction 2 commits
transaction 1 writes [x:=x + 1] (=2)
transaction 1 commits

Only serializable isolation guarantees that if a transaction that can be shown to produce correct results with no concurrent activity, it will continue to do so when competing with any combination of concurrent transactions.

Note that this does not mean that concurrent serializable transactions actually execute sequentially, just that they will have the same effects as if they had (in some unspecified order).

Specifying any other isolation level involves a trade-off: potentially higher performance and/or concurrency with fewer isolation guarantees. Exactly what the trade-off is varies significantly between implementations.

Choosing the appropriate isolation level requires comparing the needs of your application with the guarantees available. In addition, you may need to consider other factors, for example whether the transaction requires a point-in-time view of the data or not.

For more information and some examples from a SQL Server point of view, please refer to my articles in SQL Server Isolation Levels : A Series


mysqladmin - u root -p ping is not executing

You probably have:

local-infile=1
loose-local-infile=1

In your [client] section on /etc/my.cnf , /etc/mysql/my.cnf or /home/karthick.g.s/.my.cnf or somewhere else.

Change it to a [mysql] section so it doesn't affect mysqladmin.

You can also do:

$ mysqladmin --no-defaults -u root -p ping


How to write the `apply` function in postgresql

I'd really like to be able to write sql functions with apply ...

Short answer: You can't.
Not with PostgreSQL.

... to have a function resembling apply in other languages.

Never come across "apply", myself nor do I think it "adds" much to the code.
Given that you'll spend far more time reading [other people's] SQL than writing it, which of these is clearer / easier to understand?
return apply( greatest, arr );
return greatest( arr );

Now, I could see the attraction if you were writing your code in 'C' and so could use Function Pointers for that first argument, which can be immensely powerful, but in a SQL context?  I can't really see the benefit.


Where do Linked Server Queries get executed?

1 - The OLEDB provider is initiated on Server A, Server B sees it as a normal connection and query, it doesn't know that Sever A is a SQL Server, it's just a client to it.

2 - The query is executed on both.  Server A passes the query to Server B which executes it, then Server A works with the results.  The optimizer comes into play on both, but can't talk to the other servers.

Given a query of no complexity the performance will be equivalent.  However, when you start getting into complex queries or large datasets, you will see performance gains by fetching just what you need from Server B into a temp table and then using that with the data contained on Server A.  (Assuming you need to mix and match data across servers). Essentially, with the OPENQUERY syntax, you pass the query as a whole to server B (hopefully including a where clause) which then returns data to Serer A.  With the four part naming, you have no control and the optimizer rapidly will decide to just fetch all of the data in the table from Server B and then eliminate based on other criteria (joins, where, etc.).

3 - The result is not cached (except as SQL caches data normally).  Unless you store the results from the linked server query into a temp table somewhere.

4 - If you are familiar with how applications connect to a database and execute queries then the process is very similar.  Except for authentication differences it's the same, just that it allows for easily mixing and matching data from multiple servers into a single result set.  But your performance will depend greatly on how much data is pulled from Server B and staying aware of the optimizer's limited ability to prevent you from pulling the entire table from Server B, even if you only ended up needing 5 of those rows.


SQL Server: Query Efficiency Having vs Subquery

If your table's clustered index has pd_dt as the first column, this may be the most optimal query. You could simplify it by breaking it into two parts, like so:

DECLARE @dt date;
SELECT @dt=DATEADD(month, -12, CAST(MAX(pd_dt) AS date)) FROM DW_STAGE.ETL.Claims;

SELECT * FROM DW_STAGE.ETL.Claims WHERE pd_dt>@dt;

Things you could do to improve performance:

Reduce the number of columns from * to those specific columns that you need.
Create a covering, non-clustered index for these columns, where the first column of the index is pd_dt or pd_dt DESC.


How to avoid dropping schemas when publishing DB in SSDT Database project

Use this:

https://agilesqlclub.codeplex.com/

And choose to keep schema's and t won't drop them anymore.


You might look into using materialized views, if the partitions become effectively read-only on a daily basis. You can pre-calculate the results of these queries for the older data in the materialized views.


Oracle sqlldr convert date field with different NLS_DATE_FORMAT

TO_DATE functions accepts the NLS language as an optional parameter .. have you tried pulling it in via SQL*Loader as a CHAR, then do a TO_DATE conversion on it inline?

so your SQL*loader syntax might look like:

  ...
    my_date     CHAR  "TO_DATE(:my_date, 'dd-MON-RR', 'NLS_DATE_LANGUAGE=American')"
  ...

See if that works any better ??


What is the difference between a foreign key and a primary foreign key?

There is already an accepted answer.  This answer may shed new light on the topic.

Look up "shared primary key".  This is a design technique that is often used when there is a relationship that is one-to-one, and mandatory in one direction but optional in the other.  IS-A type relationships fit this pattern.  For example, every car is a vehicle, but not very vehicle is a car.  (Some are trucks or motorcycles, etc.).

This pattern is called superclass/subclass in object modeling,  and generalization/specialization in ER modeling.

In Shared Primary Key,  one column in the table is declared as both the primary key in this table,  and a foreign key that references the primary key of some other table.  continuing our example,  the ID field in the CAR table would be the primary key,  and also a foreign key that references the PK of the VEHICLE table.

This allows a each car to have an assigned identity, without introducing yet another identifier,  and it allows for rapid joins between VEHICLE and CAR.

You'll get more details when you do the look up.

Note that I've avoided the term "primary foreign key" which is confusing at best.


How to select the members who has the age more than 18 (from current date) by SQL in teradata?

Simple logic is based on comparing DOB with the date 18 years before today.
If the datatype of DOB is DATE:
where DOB < add_months(current_date, -18*12)

If it's an INT:
where DOB < cast(add_months(current_date, -18*12) as int) + 19000000

If you want to show the age
trunc(months_between(current_date, DOB)/12)

or for INT
trunc(months_between(current_date, cast(DOB - 19000000 as date))/12)

+/- 19000000 is based on the internal format of a Teradata date:
(year - 1900) * 10000
+ month * 100
+ day


Slony is trigger based.  Since you cannot have triggers on views you can not use Slony to replicate views.

However, you can achieve the desired effect by using Slony to replicate the tables used by your views and duplicate the view definitions on your slave servers.


What is the purpose of scalar operator in this seek predicate?

The "Scalar Operator" on the olic.ContainerID column in the question simply indicates that column outer reference is providing a single row (scalar) per iteration of the nested loop (apply). It's purely architectural and nothing to worry about.
Details
Internally, the query processor acts on a tree representation, which at a basic level contains a combination of relational (table-valued) and scalar (single row) operations.
When SQL Server builds showplan output, each operator in the tree (that is enabled for public showplan) is asked to produce a representation suitable for the output target (SHOWPLAN_XML, SHOWPLAN_TEXT, STATISTICS XML...and so on).
The XML output format has to comply with the showplan schema, which contains elements for both relational and scalar operators. The schema specifies scalar operator elements in many places.
Whether the specific text "Scalar Operator(...)" appears in an SSMS tooltip, in the Properties window, or only in the raw XML depends on implementation details at each layer.
For example, the simple query:
SELECT TOP (1) 1;

...produces a plan without "Scalar Operator" in the SSMS tooltips, but present for the Compute Scalar relational operator in the properties window:

...and only in the raw XML for the Top relational operator:

For all practical purposes, the "Scalar Operator" text should simply be ignored. It means nothing except that the thing it encloses is a scalar.


Strange looking where clause in sql server

SQL Server does NOT have guaranteed short circuiting in that scenario. The point of this construct is so that businessLogicCondition is only evaluated as part of the WHERE clause in the case where @term1 is populated. It may still be evaluated as part of query processing, because you can only control order of evaluation in a few special cases (no pun intended).

This WHERE clause would actually have different meaning, unless businessLogicCondition actually referenced @term1 explicitly and in such a way that it could only return true if @term1 is NOT NULL:

WHERE @term1 IS NULL OR (businessLogicCondition)

businessLogicCondition may still be evaluated, but this form of the WHERE clause could, potentially, return rows based on businessLogicCondition, regardless of whether @term1 is NULL or NOT NULL.

Now, with the pertinent info added to your question:

No, in this specific case, the @term1 IS NOT NULL AND is redundant and cannot possibly help the optimizer come up with a better plan (except maybe in the case where there is a filtered index with that same). In fact in the above scenario I mentioned before I had that information, it won't lead to a better plan either, but it can change whether the results are correct or not.

It is important to note that as written SQL Server will optimize and cache an execution plan based on the first execution. It may be the case that a better plan can be used in the scenario where @term1 is NOT NULL (e.g. when @term1 is something like somestring%, it may be able to seek, but not with %somestring%). So you might consider using dynamic SQL to formulate one version of the query or the other, depending on the value of the parameter, especially if you have multiple optional parameters (I call this "the kitchen sink" procedure). The OR that remains may still be problematic; there are a lot of variable factors.

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.


Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

+1 to @RolandoMySQLDBA of course for another valiant answer. But more to the point of your question:

  ...will [there] be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a [write] transaction.

YES.

You can't get a consistent backup even for a single MyISAM table unless you do some type of locking to prevent writes. Rolando gave a pretty thorough answer with options available to you for locking.

One other option that people use for backing up MyISAM data: LVM snapshots. See http://www.lenzg.net/mylvmbackup/ for a great tool to assist with this.

The final recommendation is to stop using MyISAM, and use InnoDB instead. Then you can do fast, non-locking physical backups with Percona XtraBackup.

Re your comment:

Because reading through a large file isn't instantaneous or atomic. While your backup is progressing through the table, other concurrent updates could change both rows that your backup has already read, and rows that your backup hasn't reached yet.

Take a textbook example for transaction behavior: I do a bank transfer by debiting my bank account and crediting your bank account. My bank account is stored on a row that is physically early in the file, and your bank account is stored on a row later in the file.

While this is going on, the backup is reading through the file, and it has read up to a mid-point at the time our transaction happens. When we restore, we get my original account balance, without the debit applied, because the backup had already passed that point when the debit occurred (and it isn't able to go backwards). But the restore includes your updated account balance, because the backup got to that point in the file after we increased your balance.

Ergo, free money! ;-)

MyISAM solves this by requiring the table to be locked against updates while it's doing a read.

InnoDB solves this by keeping multiple versions of rows, for as long as a reading transaction needs to see them for the sake of a consistent view of the database. So anyone can update the data without waiting, even though the backup is in progress.

Percona XtraBackup solves this in a slightly different way: it can go backwards, in a way. While it's reading the data file, it keeps checking the transaction log continually, to see if there are any late changes it needs to include. These changes may apply to parts of the datafile that Percona XtraBackup has already read. But as long as it gets the data file plus any changes that were logged since the backup started, it can reconstruct the full database.

But that only works for storage engines like InnoDB, that create a reliable transaction log. Percona XtraBackup can also back up MyISAM, but only by using locking, like any other backup tool.


How to log SSAS $system.discover_sessions to a SQL Server Database

You could log this periodically by scheduling an SSIS package with a data source to the SSAS server and a destination to the SQL Server but I think your better option would be to have a look at the Flight Recorder. (There unfortunately isn't an enormous amount of official documentation.)

Flight recorder basically captures a sort of server side trace continuously which you can then analyze (and even replay) in profiler to see what was happening at that point in time.

Alternatively you could set up a profiler/server side trace yourself or use the ssasdiag tool.


How do I minimise logging during a large insert?

In my experience, the best way to do this is to:

Create the EMPTY table
Create the clustered index (matching your source table)
INSERT using TABLOCK hint for minimal logging

Details are included in this MS Whitepaper.

Creating the table first, then indexing it on  the same key as the source eliminates sorting.

If the keys match, TABLOCK is used, and trace flag 610 is enabled, the operation will be minimally logged (logging pages not transactions or rows).

For me this is faster as adding the clustered index after SELECT INTO forces a sort which can be very costly and time consuming.

EDIT:

TF 610 is used solely to control minimal logging behavior when inserting into indexed tables.

I'm not aware of any issues with using it, but MS recommends (rather defensively I think) to test before using.


What about setting special permissions to the folder containing the backend file. This page has a table that might help.


There are many considerations when determining the right number of files in a filegroup--Determining the proper number and layout of files in this filegroup is a separate question, but one that should not be ignored. File layouts for a database can be non-trivial, and the topic of adding/removing files from a filegroup raises lots of tangential discussion points.

That being said, I'm going to assume you've already looked at those things and come to the conclusion that you must remove some files.

If you choose to remove data files from a filegroup, the first step is to run DBCC SHRINKFILE(logicalname,EMPTYFILE). (If you don't do this manually, the GUI will do it for you--I recommend against doing this sort of thing via the GUI.) This will evacuate data pages from this file and move them into another file in the filegroup that has room. This is done page-by-page, and is an online operation. However, just like any file-shrinking, it's going to cause all sorts of nasty fragmentation.

You probably want to run index maintenance after you remove the files.

Before you begin, you will  also want to consider which file(s) you want to remain at the end. I would go ahead and pre-grow those files so that you're not trying to autogrow FileA while trying to shrink FileB. Or possibly, I would just create some brand-new files and get rid of the existing 16 files.


How to make MySQL 8 alert when integer overflow happens during INSERT ... ON DUPLICATE KEY?

USE a BEFORE INSERT TRIGGER to check for all fields where you can reach their limits.

With the newest mysql incarnation you can actually can use the CKECK CONSTRAINT to
prevent an INSERT at all, but with the TRIGGER you can manage and define What to do


How come website be random slow? Open sleep processes?

IMHO,reason can be one of the following

i) Inline query :It is prone to sql injection attack.but not necessarily website will be slow.It is Advisable to use parameterise proc.

ii) Unpotimize query : Yes.shortlist those page and related query/proc.

iii) CSRF Attack : your pages can be attack by malware or web crawler.There are various way to overcome it,one of them is Captcha, but not always.google it.

iv) Network/server configuration :this should point i).check with your admin.

v) check for infinite and uncessary loop in pages.Infact identiyfy pages which are slow then examine both code and query.


Adding columns upped my RAM usage significantly

How many GB in the big table?

What was the setting of innodb_file_per_table?  Of innodb_buffer_pool_size?  Please provide the ALTER statement(s).

What probably happened was that the table was copied over to produce the new version with the new columns.  (8.0 can add columns without copying -- in some situations.)

If you had file_per_table OFF, then the copy would be in ibdata1, which won't shrink afterwards.  (The freed up space will be reused, just not returned to the OS, which you are monitoring.)

The buffer_pool is in RAM.  It grows as needed up to the configured limit, which I guess to be 5G.  The ALTER caused the sudden growth.  This is a "cache", so there is no 'permanent' harm.  Again, no shrinkage will happen.