Update a Column with sequential number based on the order by of a column in Oracle Sql

create table training(
 action_id       NUMBER not null,
 training_id       NUMBER,
 course_name       VARCHAR2(100),
 display_order     NUMBER default 0
);

insert into training (action_id, training_id) values (675150, 770650);
insert into training (action_id, training_id) values (675150, 780700);
insert into training (action_id, training_id) values (675150, 780701);
insert into training (action_id, training_id) values (675180, 770550);
insert into training (action_id, training_id) values (675180, 780800);
insert into training (action_id, training_id) values (675180, 780801);
commit;

select action_id, training_id, display_order from training;

 ACTION_ID TRAINING_ID DISPLAY_ORDER
---------- ----------- -------------
    675150      770650             0
    675150      780700             0
    675150      780701             0
    675180      770550             0
    675180      780800             0
    675180      780801             0

Then with some magic:

merge into training t using
(select action_id, training_id, row_number() over (partition by action_id order by training_id)
  as display_order
from training) t2
on (t.action_id = t2.action_id and t.training_id = t2.training_id)
when matched then update set t.display_order = t2.display_order;
commit;

select action_id, training_id, display_order from training;

 ACTION_ID TRAINING_ID DISPLAY_ORDER
---------- ----------- -------------
    675150      770650             1
    675150      780700             2
    675150      780701             3
    675180      770550             1
    675180      780800             2
    675180      780801             3


PostgreSQL concurrent increment of counter

The second update would wait for the previous update on the same rows to be comitted, but will then see the committed value.

Assume two concurrent transactions updating the same row with an initial value of 0

Time Transation 1  T1 value    Transaction 2  T2 value
--------------------------------------------------------------
1    update ...    1                          0
2                  1            update ..     "undefined"
                                (waits)
3    commit        1                          2
4                  1            commit        2
5                  2                          2

"T1 Value" and "T2 Value" means the value that that transaction sees.

If you want to make sure you catch situations where there are "incompatible" changes (e.g. one transaction setting the usage column to a specific value, rather than just incrementing it) you can put all transactions into the "serializable" isolation level. But you will need to prepare for error handling then.

Updates to different names can run concurrently without any waits (because different rows are affected).

SELECTs will never be blocked but will only see comitted values.


Get ID which has two rows with different values for one column

select id
from the_table
where location in ('a', 'd')
group by id
having count(distinct location) = 2;


MySQL - Many to Many Join. Left Join already Present

As a rule of thumb conditions on the Outer table are usually placed in WHERE, but on the Inner table in ON:

SELECT  DISTINCT student.Student_ID as 'Student_ID',
                  case when class.class_id is null then 'No Class ATM' else class.class_id end as 'Class_ID',
                  case when Attendance.Attendance_Status is null then 'Not Present' else Attendance.Attendance_Status end as 'attendence_status'
          FROM Student
          LEFT JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id)
          LEFT JOIN class ON class.class_id = student_has_class.class_id
            AND NOW() between class.class_start_timestamp and class.class_end_timestamp
          LEFT JOIN Subject ON(class.subject_id = subject.subject_id)
          LEFT JOIN Attendance on(Attendance.class_id = class.class_ID
                                  AND Attendance.student_id = student.student_id)
          WHERE Student.student_id like '1'

And please no like '1', it's = '1', but probably student_id is numeric so = 1


MySQL 8.0 Replication filters lost between restarts

The short answer is: this is a feature.

The CREATE REPLICATION FILTER syntax was created as a way to dynamically modify replication filters on the slave without a restart. (bug report)

You are right, that the documentation is a bit lean on this, but I found this snippet from FAQs in the MySQL Release announcement of the feature in 5.7:

11) Are these changes made through the new command persistent upon restarting the server?
A) No, the changes are not persistent. You have to change the values again after restarting the server. If you want these changes to be persistent, you can put them in my.cnf file.

[src]

What is interesting is that you claim this is the same setup on 5.7. It would be good if you could get that behavior reproducible (5.7 setup without options in my.cnf survive restarts).

I'd also recommend opening a bug report for 8.0/5.7 to at the very least get this behavior documented on the official documentation.

Immediate fix is of course to set the config options.


Why can't I use a cname/alias in the permissions table?

Plan A:  If dull and shiny don't change, leave both in effect?...

GRANT ... TO dbuser@dull ...;
GRANT ... TO dbuser@shiny ...;

But, beware, this could leave the "production" system accessible to "QA".  (Assuming that is really what you are doing.)

Plan B:  A simple script (after transferring):

UPDATE mysql.user SET host = 'shiny' WHERE host = 'dull';

But, again, beware -- If the script fails to be run successfully, QA could be hitting production.

Plan C:  Well, there is likely to be some other trick.


pg_upgrade fails with an unspecified error

Basically pg_upgrade is production ready.

Unfortunately the early builds of 9.1 had some bugs related to pg_upgrade and Windows. These were fixed in 9.1.3 as far as I know.


How large space will be occupied by mysql for a varchar utf8 column?

CHAR(...) CHARACTER SET utf8 always takes 3 bytes per character -- CHAR(100) occupies 300 bytes (no length needed).

VARCHAR occupies 1-2 bytes for a length, plus only as many bytes as needed.  So VARCHAR(100) with hello will occupy 7 (2+5) bytes in any character set.

Señor, in CHARACTER SET latin1, take 5 bytes (plus length).  In utf8, it takes 6 bytes (plus length).  This is because ñ is the 1-byte hex F1 in latin1 or the 2-byte C3B1 for utf8.

Some Chinese characters and some Emoji, need 4 bytes, so utf8mb4 is a better choice for them.

There is very little use for ever using CHAR instead of VARCHAR.  They are usually for consistent-length fields such as postal code, country code, md5, uuid, etc.  And all of those may as well be CHARACTER SET ascii or possibly latin1, but not utf8.

Back to your question...  With CHARACTER SET utf8 (or utf8mb4):

English letters take 1 byte.
Western European accented letters take 2 bytes.
Greek, Hebrew, and other languages in that general area, take 2 bytes.
Japanese and Korean take 3 bytes.
Most Chinese characters are 3 bytes; some are 4 and need utf8mb4.
A longer list

"mysql will physically store characters using 1 byte for latin and 3 bytes for CJK" is not phrased correctly.  Change 'latin' to 'English' to make it correct.  ('Latin' tends to include various accented characters.)


Perhaps I'm misunderstanding your question, but when you write data to the cluster, the primary key is hashed using the Murmur3 partitioner (usually) and then the data is written to the node that is responsible for that hash range, which could be any one of your 5 nodes.

In addition, your data is also being written at the same time to 2 other nodes for a total of 3 copies of the data spread around the cluster. When you do nodetool status some-column-family you should see that each node is responsible for (3 copies/5 nodes) 60% of the data.

It doesn't matter which node you query for a read, because all of the nodes are equal and any node can act as a coordinator to gather the results to pass back to the client.

Hopefully that answers your question or points you in the right direction.


operator does not exist: timestamp with time zone + integer in PostgreSql

Maybe someone created the operator with those types in one database, but not in the other one.

create function addit(timestamptz,int) returns timestamptz immutable language sql as $$
    select $1+ interval '1 day'*$2
$$;
create operator + (leftarg =timestamptz, rightarg =int, procedure=addit);


Postgres hstore check if a key exists and does not match value

The usage of defined() in the documentation:

  does hstore contain non-NULL value for key?

In addition to a NULL value for an existing key, it returns FALSE also when the key does not exist.

To keep it clean, you might want to use the exist() function instead:

SELECT exist('"bla" => 1234', 'bloo');
 exist
───────
 f

So your query would look like

SELECT changed_fields -> 'updated_by'
  FROM audit.logged_actions
 WHERE changed_fields -> 'updated_by' <> 'system'
       OR NOT exist(changed_fields, 'updated_by')
 ORDER by event_id desc;

Notes:

never test logical values using the equality operator (unless you can be really sure the value cannot be NULL).  You can directly use the value if it is guaranteed to not be NULL (like in my query), or use the IS operator.
In the current form, the query is checking a simple 'OR' - from your wording it is not quite clear what should happen when both conditions are true.


How to check for existence of an INDEX on a column independent of used SQL database

I'm afraid not.

The ANSI SQL standard defines some data dictionary views in a schema  ("INFORMATION_SCHEMA") that can be used to look for specific types of objects (tables, views, table columns), but it doesn't include anything regarding indexes.

Most RDBMSes have their own internal data dictionary views that expose this information (sysindexes in SQL Server & Sybase, DBA_INDEXES in Oracle, for example).


There are 3 alternatives:

create a new controlfile and try to add those file in one or more tablespaces, they look part of a partitioned object (you might want to use dbverify before running create controlfile)
use UNIX strings command, this should recover some information, but not all...
use the BBED tool as documented here:
http://orafaq.com/papers/dissassembling_the_data_block.pdf
this is a powerful tool but takes a bit of time to master

Also, if you have Oracle Support contract you might try contacting them, it is their format,
it is the easiest path to "success".. but probably the most expensive.


The name reply_id is a bit confusing.  Consider in_reply_to or parent_id.
I assume that post_id is the id for the entire thread?  Maybe it should be called thread_id?  ("Post", to me, is somewhat synonymous with "post".)

I don't think you need two tables, at least not for comments.  However, if you do have information about the thread as a whole, I recommend a separate table for that; it would have PRIMARY KEY(thread_id).

What's the problem with this?

SELECT ...
    FROM ...
    WHERE thread_id = 2
    ORDER BY id DESC;

Can have multiple replies to a single comment?  If so, you should change the question to make that clear.  It adds a level of complexity.

You seem to have a "hierarchical" tree.  But, since you have post_id, the above query should get all of the messages, approximately in chronological order.

Why have comment_id if you already have id?

Or, start over...

Information about the entire Thread is in a separate table, and identified by thread_id.
The Comments are in a second table, with PRIMARY KEY(thread_id, comment_id). comment_id can simply be an AUTO_INCREMENT.
Each comment in a "thread" has the same thread_id.
Each row has a unique comment_id.
"Replies" need to identify their parent_id.


Access Web App query 3 Tables with aggregatios - Need a workaround

I have one workaround going which is making a seperate query for each 1:n-relation which connects DNS and SUM(foo) and one query which has the results of all the queries. Then in the little ERM for the query I connect every DNS entry of the queries.
For 1:1 relations in the DB I can just use the standard table in the overallQuery.

still looking for other workarounds though


Autocomplete in Intellisense SSMS 2017. Where'd it go?

Having just spent two days with the same problem, I finally found the answer (in my case).  Intellisense does not work in SQLCMD mode, and mine was on by default (Tools, Options, Query Execution, SQL Server, General, second last!)


Let's assume your tables are defined like this (by converting your descriptions to SQL Data Definition Language - DDL):

CREATE TABLE operator
(
    id integer NOT NULL PRIMARY KEY,
    logo_path varchar(255),
    tos_path varchar(255),
    tax_number integer
) ;

CREATE TABLE product
(
    id integer NOT NULL PRIMARY KEY,
    operator_id integer NOT NULL REFERENCES operator(id),  /* This is actually ignored by MySQL, but not by "well-behaved" databases */
    name varchar(100),
    description varchar(255),
    price decimal(12,2)
) ;

Let's put some sample data:

INSERT INTO
    operator
    (id, logo_path, tos_path, tax_number)
VALUES
    (1000, '/path/to/logo/1000', '/path/to/tos/1000', 1234),
    (1001, '/path/to/logo/1001', '/path/to/tos/1001', 5678)
;

INSERT INTO
    product
    (id, operator_id, name, description, price)
VALUES
    (1, 1000, 'Product Name', 'Product Description', 1234.56),
    (2, 1001, 'Product 2', 'Description 2', 2345.67)
;

... and now we can perform a SELECT with a JOIN

SELECT
    product.id, product.name, product.price, operator.logo_path,
    operator.tos_path, operator.tax_number
FROM
    product
    JOIN operator ON operator.id = product.operator_id ;

This is the result you would get, you retrieve the data from both product and operator with just one query. The database will handle how to fetch it from the tables. One of the objectives of relational databases is to just let them do this kind of things

id | name         |   price | logo_path          | tos_path          | tax_number
-: | :----------- | ------: | :----------------- | :---------------- | ---------:
 1 | Product Name | 1234.56 | /path/to/logo/1000 | /path/to/tos/1000 |       1234
 2 | Product 2    | 2345.67 | /path/to/logo/1001 | /path/to/tos/1001 |       5678

You can see the full example to play with at dbfiddle here

You always want to use relationships, and JOIN, and not repeat. This is formally called Normalizing your database

Of course, all rules tend to have exceptions. Having denormalized data is left to a few specific cases (normally, data which is read only, and where a need for speed is of the utmost concern; this is typical of data warehouses and for analytics (OLAP)).

If in doubt: normalize. Always.


MySQL: How to create Column if not exists?

Actually exists now for Maria DB 10.219

ALTER TABLE test ADD COLUMN IF NOT EXISTS column_a VARCHAR(255);

Bonus, it works for MODIFY as well

ALTER TABLE test MODIFY IF EXISTS column_a VARCHAR(255);


PostgreSQL: Displaying inheritance relationship?

SELECT relname
FROM pg_class c,pg_inherits i
WHERE c.oid=i.inhrelid;

I am the guy that asked Adam about this, I found a solution so I added it here for future reference of others.


Original question (dupes within hstore value)

I think the root cause of your problems is explained in this quote from the manual:

  Each key in an hstore is unique. If you declare an hstore with
  duplicate keys, only one will be stored in the hstore and there is no
  guarantee as to which will be kept.

Bold emphasis mine.

You are mistaken in assuming that you could have the same key twice in a single hstore value.

I can also not reproduce your count of 2. I get a count of 1 for the key 1614. Start by not using count as column name. It is a reserved word (but allowed in Postgres).

I get (tested with Postgres 9.1.9):

WITH carts AS (
   SELECT '"1614"=>{:quantity=>"100"}, "1938"=>{:quantity=>"50"}, "1614"=>{:quantity=>"50"}, "1983"=>{:quantity=>"100"}, "1322"=>{:quantity=>"10"}, "1691"=>{:quantity=>"25"}, "1614"=>{:quantity=>"77"}, "1734"=>{:quantity=>"20"}'::hstore items
    )
SELECT key, count(*) AS ct FROM
  (SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY ct DESC, key;

Result:

 key  | ct
------+----
 1322 |  1
 1614 |  1
 1691 |  1
 1734 |  1
 1938 |  1
 1983 |  1

Updated question (dupes across multiple rows)

To aggregate values you need a subquery (or CTE).

Simplified test case:

CREATE TEMP TABLE carts(c_id serial, items hstore);
INSERT INTO carts(items) VALUES
   ('"1614"=>"100", "1938"=>"50", "1983"=>"100", "1322"=>"10", "1691"=>"25", "1734"=>"20"')
  ,('"1614"=>"50"');

Query:

SELECT item_ids, count(*) AS ct, sum(items) AS sum_items
FROM (
   SELECT (each(items)).key AS item_ids
         ,(each(items)).value::int AS items  -- assuming values can be cast to int
   FROM   carts
   ) sub
GROUP  BY 1           -- positional parameter is just a notational convenience
ORDER  BY 2 DESC;

Result:

 item_ids | ct | sum_items
----------+----+-----------
 1614     |  2 |       150
 1734     |  1 |        20
 1691     |  1 |        25
 1983     |  1 |       100
 1938     |  1 |        50
 1322     |  1 |        10


Your backup solution for MySQL User Grants is fine.

I have been advocating doing that for years:

Mar 24, 2013 : MySQL export user with semi-colon ";" on the end
Dec 24, 2012 : Backup and restore "mysql" database
Jun 13, 2012 : Fastest way to move a database from one server to another
Apr 12, 2012 : Cannot GRANT privileges as root
Apr 09, 2012 : Mysql users deleted
Jun 26, 2011 : Restoring an old backup to latest MySQL release

Also, please notice that Percona created pt-show-grants and xtrabackup. It is curiously funny that pt-show-grants has not been integrated into xtrabackup already (Hey I could be wrong. After all, a physical backup of the mysql schema should be very adequate for a Backup of Grants). Perhaps, one of the following is the reason:

Business Decision to keep Text version of Grants separate from the Backup
Potential Security Reasons (should anyone get a hold of the Backup)
No one ever thought of it before

Going back to you original question, copies of the User Grants in SQL and the my.cnf are sufficient.


Background processes are processes that are part of the instance. They start when the instance starts, and exit when the instance shuts down. These processes are required for the instance to function or to do the work that would otherwise require starting these processes every time the work needs to be done (which would probably be ineffective). The examples of background processes are job coordinator background process (cjqN) which coordinates job-slave processes running jobs on schedule, and LGWR background process which writes redo entries to the redo log files.

Server processes, on the other hand, are mostly transient: they start when needed, do some work, and exit. Sometimes people refer to server processes as foreground processes, as opposed to background processes. The examples of foreground processes are the server processes that support user sessions, Scheduler job-slave processes (jNNN) which are started by the job coordinator process described earlier, do some work, and eventually exit if there are no other jobs to do.

Server processes are sometimes also referred to as shadow processes, but I would avoid this term, because it seems that it's not commonly used, and may confuse you – I found only 25 occurrences of "shadow process" in Oracle Database 11g Release 2 documentation (one being mentioned in "Shadow Server Architecture" section of Database Concepts), as opposed to 305 occurrences of "server process", and 182 occurrences of "background process".

As far as OCA exam is concerned, I didn't encounter "shadow process" term in any of the questions on the real exam, and this term appeared only twice in the guides I used to prepare for the exam, one of them explicitly stating that its use should be avoided.


I'm attempting to write a stored procedure that can read from a file and act on the contents

This can be done simply with powershell. Use get-content to get queries from file, invoke-sqlcmd to run the queries, and write-sqltabledata to output results to table. Save example below to .ps1 and run in powershell session passing the path to file with queries. (ex. C:\you\powershell> .\run-tsql.ps1 C:\temp\sql_queries.txt)

#paramter passed to script
param( [string] $auditlist)

#read contents of file
$queries = Get-Content $auditlist

#adds sql server module to get the sql server commands
import-module sqlserver

#cycle though all results retrived from file
foreach ($query in $queries)
{
    #run each command and load into table
    Invoke-Sqlcmd -serverinstance <instance name> -database <databasename> -query $query -OutputAs DataTables |Write-SqlTableData -ServerInstance <instance name> -DatabaseName <dbname> -SchemaName <schema> -TableName <table>
}


Select distinct values in column with condition

Solution 1

The idea is to find distinct groups for each product, then the highest priority match calculated using a CASE expression:

SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    V.[Value]
FROM dbo.Products AS P
CROSS APPLY
(
    -- Find groups for the current ItemId
    SELECT DISTINCT
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = P.ItemId
) AS G
CROSS APPLY
(
    -- Find the highest-priority Value
    SELECT TOP (1)
        PP2.[Value]
    FROM dbo.ProductPrices AS PP2
    WHERE
        PP2.ItemId = P.ItemId
        AND PP2.[Group] = G.[Group]
    ORDER BY
        CASE
            -- Colour match is best
            WHEN PP2.Color = P.Color THEN 1
            -- Otherwise accept NULL
            WHEN PP2.Color IS NULL THEN 2
            ELSE 3
        END ASC
) AS V
ORDER BY
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;

Solution 2

This joins the tables in the natural way, then uses a window function to find the highest priority match for each group. This code only accesses each base table once:

WITH
    Joined AS
    (
        SELECT
            P.ProductId,
            P.ItemId,
            PP.[Group],
            Color = ISNULL(PP.Color, P.Color),
            PP.[Value],
            rnk =
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END
        FROM dbo.Products AS P
        JOIN dbo.ProductPrices AS PP
            ON PP.ItemId = P.ItemId
    ),
    Ranked AS
    (
        SELECT
            *,
            rn =
                ROW_NUMBER() OVER (
                    PARTITION BY Joined.ItemId, Joined.[Group], Joined.Color
                    ORDER BY Joined.rnk ASC)
        FROM Joined
    )
SELECT
    R.ProductId,
    R.ItemId,
    R.[Group],
    R.Color,
    R.[Value]
FROM Ranked AS R
WHERE
    R.rn = 1
ORDER BY
    R.ProductId,
    R.ItemId,
    R.[Group] DESC;

Solution 3

This is the same logic as solution 2, but uses a subquery and a more compact syntax:

SELECT
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group],
    Q1.Color,
    Q1.[Value]
FROM
(
    SELECT
        P.ProductId,
        P.ItemId,
        PP.[Group],
        Color = ISNULL(PP.Color, P.Color),
        PP.Value,
        rn = ROW_NUMBER() OVER (
            PARTITION BY P.ItemId, PP.[Group], ISNULL(PP.Color, P.Color)
            ORDER BY
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END)
    FROM dbo.Products AS P
    JOIN dbo.ProductPrices AS PP
        ON PP.ItemId = P.ItemId
) AS Q1
WHERE
    Q1.rn = 1
ORDER BY
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group] DESC;

Solution 4

With the following index on dbo.ProductPrices:

CREATE INDEX [IX dbo.ProductPrices ItemId, Group (Color)]
ON dbo.ProductPrices
(
    ItemId,
    [Group],
    Color
)
INCLUDE
(
    [Value]
);

The following solution can perform well:

SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    [Value] =
        IIF
        (
            EXISTS
            (
                SELECT
                    1
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color IS NULL
            )
        )
FROM dbo.Products AS P
CROSS APPLY
(
    SELECT DISTINCT TOP (9223372036854775807)
        PP.ItemId,
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = CONVERT(varchar(50), P.ItemId)
) AS G
ORDER BY
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;

Demos

db<>fiddle demo

As a side note, ItemId is defined as nvarchar(50) in one table, and varchar(50) in the other. Same with the Color column. These problems should be fixed, if possible.


Reorganize and Rebuild Indexes with active connections

I want to rebuild or reorganize indexes in a table

These are two different modes to remove index fragmentation.

Rebuilding an index means that a whole new set of pages is
allocated for it.

It cames in two flavours: online and offline. When rebuilding
offline your index is completely unavailable, but the operation is
faster than that online. It can be minimally logged (in simple
or bulk logged recovery models), and even when it's fully logged
it's logged very efficiently (the whole index pages when built are
written to log).

Online rebuild is available only in SQL Server Enterprise,
Developer, and Evaluation editions. The operation is always
fully logged and produce a notable amount of log as new index is
logged row by row. Besides, more exact term for it is "almost
online operation". Yes there is no lock held for the entire duration
of the index rebuild operation, but the locks are still held.

Here is a great explanation of Paul Randal:

Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

  Myth 5: online index rebuild doesn't take any locks

  This myth is untrue. The 'online' in 'online index operations' is a
  bit of a misnomer.  Online index operations need to take two very
  short-term table locks. An S (Shared) table lock at the start of the
  operation to force all write plans that could touch the index to
  recompile, and a SCH-M (Schema-Modification – think of it as an
  Exclusive) table lock at the end of operation to force all read and
  write plans that could touch the index to recompile.

  The most recent time this came up on the forums was someone noticing
  insert queries timing out after an online index rebuild operation had
  just started. The problem is that the  table lock that online index
  rebuild needs has to be entered into the grant queue in the lock
  manager until it can be acquired – and it will stay there until
  existing transactions that are holding conflicting locks either commit
  or roll-back. Any transaction that requires a conflicting lock AFTER
  the index rebuild lock has been queued but not acquired (and then
  released) will wait behind it in the lock grant queue. If the query
  timeout is reached before the transaction can get it's lock, it will
  timeout.

Reorganizing is designed to remove logical fragmentation from the leaf level of an index while
keeping the index online and as available as possible. When reorganizing an index, SQL Server acquires an Intent-Exclusive lock on the index B-tree. Exclusive page locks are taken on individual pages only while those pages are being manipulated. So you can consider reorganizing an online operation.


TimeStamp Column Auto Update Upon Insert and Modification

Here is a list of automated features you can use to accomplish this, without using Triggers:

Temporal Tables - System versioned copies of the user defined tables that track changes.

Change Tracking - Automated tracking of changes for your user defined tables.

Change Data Capture - Keeps track of DML changes to the specified user defined tables.

Audit - Automatically tracks a multitude of actions at the server level.


ERROR 1 (HY000): Can't create/write to file (Errcode: 13 - Permission denied)

Not sure if this applies to your situation but I ran into similar problems on RHEL 8.
First gotcha is that systemd was configured to run the MySQL service with the privateTmp option (see https://www.freedesktop.org/software/systemd/man/systemd.exec.html#PrivateTmp=).
This means that when you write to /tmp it actually attempts to write to a directory named something like /tmp/systemd-private-<random>-mariadb.service-<random>
Secondly the directory you write to needs to be owned by mysql (and group mysql).
Lastly the SELinux context needs to be correct as well when SELinux is Enforcing.


Need to migrate a SQL Server 6.5 Prod to SQL Server 2012 in VM

You can update 6.5 to 2000, from 2000 to 2008 and from there go to 2012 but its much better to script out the schema and bcp the data as Kin suggests. As he seems to be to humble to link to his own post I'll do it for him: Restore SQL Server 2012 backup to a SQL Server 2008 database?


Database functional dependency BCNF help

Look up "dependency preservation".

It is a known phenomenon that when decomposing relation schemas (along some given FD), certain other FD's can become inexpressible.

In a complete logical design, they have to be reinstated as a constraint on the database.  However, normalization theory does not cover constraints.  Normalization theory deals only with relation schemas, not with constraints between them.

If you first decompose over CD->E (leaving ABCD / CDE), you can still further decompose over A->D (leaving AD / ABC / CDE), but you will now have "lost" the BE->A dependency.


Same Query takes 0 seconds on Server A, 7.5 minutes on Server B (same db/hardware/config)

This problem has been resolved, although not quite how I anticipated.

Performance did not change after flushing the buffer pool, the procedure cache, rebooting the server, or rebuilding the indexes.  The lengthy index spool continued to appear sometimes and not others in the query plan.

The fix was to change the reference in the where clause from ib_charge.patient_account_fk = ### to ib_header.patient_account_fk  (the itemized bill (IB) header table is much smaller than the ib_charge table).  This resulted in the server ceasing to use the Index Spool in all cases, which was the cause of the performance hit.


Regardless of the Storage Engine (MyISAM or InnoDB), when it comes to BTREEs, you must make sure you understand the following characteristics:

Keys should be as small as possible
Random Keys for PRIMARY KEYs

Insertions (Bulk or Programmatic) will perform root node and internal splitting periodically
Introduces Overhead early in an index's life
Breeds node fragmentation (especially for index pages)
Causes Index Scans for Queries to be performed Randomly

Ordered Keys for PRIMARY KEYs

Bulk Ordered Insertions delay root node and internal splitting
Reloading data via mysqldump files and LOAD DATA INFILE commands promote the use of  sorting mechanisms to address index initialization/reorganization (See my Oct 26, 2012 post: How badly does innodb fragment in the face of somewhat out-of-order insertions?)
Programmatic Ordered Insertions promote root node and internal splitting of index pages in 45% of the cases
Delays creation of Overhead
Prevents node fragmentation
Causes Index Scans for Queries to be performed Sequentially (less disk I/O)

When it comes to BTREEs in InnoDB, they tend to be more bloated than that of its counterparts MyISAM because of InnoDB's gen_clust_index, where row data live.

The PRIMARY KEY of an InnoDB table points right to its gen_clust_index. Secondary indexes always include a PRIMARY KEY entry. If you run a query that uses a Secondary Index and also has non-indexed columns in the WHERE clause, you could easily be doing two Index Lookups. With that in mind, you need to make sure all Secondary Indexes have all the needed columns for you queries' WHERE clauses (a.k.a. Covering Index).


Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

You are looking at the wrong place.
You have to check as below :

Using T-SQL ..
Use database_name
go
ALTER FULLTEXT INDEX ON schema.table_name SET CHANGE_TRACKING AUTO;

Once done, you can check the status of the last populated datetime
-- script source : http://stackoverflow.com/a/10505496/1387418
-- Modified by Kin on Dec 14' 2015 to reflect the FTCatalogName
DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'AW2008FullTextCatalog' -- change here !

SELECT name as FTCatalogName,
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateStatus')
        WHEN 0 THEN 'Idle'
        WHEN 1 THEN 'Full Population In Progress'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Throttled'
        WHEN 4 THEN 'Recovering'
        WHEN 5 THEN 'Shutdown'
        WHEN 6 THEN 'Incremental Population In Progress'
        WHEN 7 THEN 'Building Index'
        WHEN 8 THEN 'Disk Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs

Insert some data ..
insert into HumanResources.JobCandidate
(BusinessEntityID, Resume, ModifiedDate)
values
    (4, NULL, DEFAULT)

Now you will see that the FT Catalog is updated.

In the logs (..\MSSQL\Log\SQLFT*)also, below will be the message...

2015-12-14 12:36:51.29 spid50s     Informational: Full-text Auto population completed for table or indexed view '[AdventureWorks2012].[HumanResources].[JobCandidate]' (table or indexed view ID '1589580701', database ID '5'). Number of documents processed: 1. Number of documents failed: 0. Number of documents that will be retried: 0.

From BOL :

By default, or if you specify CHANGE_TRACKING AUTO, the Full-Text Engine uses automatic population on the full-text index. After the initial full population completes, changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background, however, so propagated changes might not be reflected immediately in the index.
By default, SQL Server populates a new full-text index fully as soon as it is created. However, a full population can consume a significant amount of resources. Therefore, when creating a full-text index during peak periods, it is often a best practice to delay the full population until an off-peak time, particularly if the base table of an full-text index is large. However, the full-text catalog to which the index belongs is not usable until all of its full-text indexes are populated.

Also, refer to Improve the Performance of Full-Text Indexes


Deadlock graph with a lock on an index on a seemingly unrelated table

The UPDATE query has an X lock on a key on "dbo.ACCOUNTS" blocking the SELECT from getting an S lock.

The SELECT query has an S lock on a key of htt_customers_overlay_ultra. The UPDATE query has a U lock on the same key and is blocked trying to convert that to an X lock.

The execution plan for the UPDATE doesn't feature Accounts at all so there is no obvious reason for it to have a key lock on Accounts. The Update transaction starts 0.01 seconds before the batch does. 2013-01-13 08:49:30.213 vs 2013-01-13 08:49:30.223.

Perhaps there was a preceding statement in a different batch (hence not shown in the deadlock graph) that actually started the transaction and acquired the mysterious X lock.


Promotion of PostgreSQL standby and application reconnection

To be truly highly available you will need some logic built into the application to handle this.  You can do this sort of thing with pg_pool, but what if pg_pool breaks?

I think there are many ways you could work this one, but the way I would do it would be:

Master DB (lets call it A), and two slaves (lets call them B and C).  B pulls changes from A and C pulls changes from B in a cascading replication setup.  This means if A fails and is a gonner promote B, no need to change C as its already a slave of B, when A is back you make it a slave of C and so on.

You will need to make changes to your application so that it has some intelligence in it regarding the state of the database.  This would be typically done by having a layer that manages the connections, and it runs a test query (like "SELECT current_timestamp") if the query passes all is fine and it gives the connection to your application to use.  Each application will need to know a list of servers to use in which order so the one that writes will use A first then B.  So in the event the primary fails it tries the secondary (and potentially the third and so on), until it gets one where the test query works.

With this in place if C fails its only read only, the applications will just fail over to B or A and start using C if you get it back.  If B fails, same deal though you might want to do something with C pointing it to A.  If A fails then the system will go read only (but will still work for all read queries).  There will then need to be some human intervention to say "yep A is gone", promote B (which will only take seconds), when B is promoted the writers will go though the connection layer, it will test A and fail test B and pass to the application will get a connection to B which is now a master so writes will be allowed.

I appreciate that this is still not a full HA solution (as there is still a manual step, but its a very simple and quick one), but I think we are going to have to wait for PostgreSQL 9.5 for that.

As I say I dont think there is one dead cert right answer to this, but this is how I would have done it.

Hope it helps


How to select Overdue Rows with Date Frequencies?

WITH RECURSIVE
cte AS
( SELECT @starting_deviation num
  UNION ALL
  SELECT num + @delta_deviation
  FROM cte
  WHERE num < @final_deviation )
SELECT *
FROM invoices
JOIN cte ON invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY

For the query from the question @starting_deviation := 5, @delta_deviation := 5 and @final_deviation := 15. These values may be specified literally (inserted immediately into the query instead of variables), transferred via variables values assignement or taken from some service table.

In MySQL 5+ you must generate the numbers table in a subquery or use external static numbers table.

SELECT *
FROM invoices
JOIN ( SELECT 5 num UNION ALL
       SELECT 10 UNION ALL
       SELECT 15 ) cte ON invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY

  i want to add is my frequency is not upto 3 series. it can be more than 3 maybe any number.... so for example if n is 5 and i want 10 frquesnces means 5,10, 15, 20......, 50

Use numbers generator. For example it can be (shown generator may give up to 64 values):

SELECT *
FROM invoices
JOIN ( SELECT 5 * (1 + t1.num + t2.num * 4 + t3.num + 16) num
       FROM (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1,
            (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t2,
            (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t3
     ) cte ON  invoices.due_date = CURRENT_DATE + INTERVAL cte.num DAY
WHERE cte.num BETWEEN 1 AND 10;


GRANT PRIVILEGES TO users stored in Database Users table

Database engines do not understand the contents of your tables. You can instruct them to set up a foreign key relationship between the Users table and various other tables in the database, and they can enforce that - but the engine cannot automatically prevent a query from returning data that's tied to a different UserId than the one that's stored in a variable or cookie or whatever, indicating who the person connected to your application is.

That's something that generally must be done specifically by the application. In most cases, I'd expect this to be done by giving the users of the application forms to fill out; from the input on the form, the application would build a query, including whatever business logic applied (like UserId must match the ID value for the user running the query).

If you want to set up every user of your database as an actual database-level login, then some engines (can't recall if MySQL has this capability or not) allow for row-level security. However, this works with users and role set up in the database itself, not user accounts created in and managed by an application.


If a select statement returns no result, change the where condition

In SQL (not PL/SQL) you can do it like this:

with p as (
  select * from drscan.oem_program p
  WHERE upper(OEM_PROGRAMID) LIKE upper('%Subvented%')
    and oem_code = 'NCI'
    and rownum=1
    and p.ISACTIVE='Y'
)
, p2 as (
    select *
  from drscan.oem_program p
  where oem_code ='NCI'
    and p.PROGRAMCATEGORY='NEW'
    and p.ISACTIVE='Y'
    and trunc(p.STARTDATE)<=trunc(sysdate)
    and trunc(p.ENDDATE)>= trunc(sysdate)
    and rownum=1
)
select * from p
union all
select * from p2 where not exists(select 1 from p)


Given your test data, this will parse out the section of the string you want between either the first semi-colon after the PATH=, or the end of the string if that doesn't exist. From here, you should be able to use an update to replace that section of the string.
Further reading: Get The Text Between Two Delimiters
SELECT
    x.*,
    SUBSTRING(x.PARAMETERLIST,
        CHARINDEX('PATH=', x.PARAMETERLIST) + LEN('PATH='),
        CASE WHEN x.PARAMETERLIST NOT LIKE '%PATH=%;%'
             THEN LEN(x.PARAMETERLIST)
             ELSE CHARINDEX(';', x.PARAMETERLIST, CHARINDEX('PATH=', x.PARAMETERLIST) + LEN('PATH='))
                  - LEN('PATH=') - CHARINDEX('PATH=', x.PARAMETERLIST)
        END
             ) AS parsed_string
FROM
(
    VALUES
        ('KF=138;SCANANDSEND=TRUE;ENDBARCODE=999999999999;PATH=K:\SSTEST;BARCODELENGTH=8'),
        ('KF=138; PATH=s:\'),
        ('DESIGNATION=JOB; KF=81; PATH=w:; SENDALLDATA=TRUE'),
        ('KF=76; PATH=\srv05\L3OERAPMFC; SENDALLDATA=TRUE'),
        ('KF=386; PATH=W:; SENDALLDATA=TRUE')
) AS x(PARAMETERLIST);


How do I fix #1062 error received while importing EE database to localhost from remote server

Your local table is likely not empty, thus the error when conflicting with existent data. If you do not want to keep any of the current data, you need to delete all rows before the insert. One fast way to do that is with:

TRUNCATE TABLE exp_accessories;

Usually, mysqldump will drop the table before import, like this:

DROP TABLE IF EXISTS `exp_accessories`;

Regarding French, client sessions can change the default language setting by doing SET lc_messages = 'en_US'; and receive error messages in a different language.


Can't change root password: "The system cannot find the file specified."

There are two techniques you can try:

TECHNIQUE #1

The comment from Michael - sqlbot already said it. Use double quotes

mysqladmin -u root password "Pe%8XiduwOqdZ<ZFE5!"

TECHNIQUE #2

root@localhost has no password. Try running this:

mysql -uroot -e"SET SQL_LOG_BIN = 0; SET PASSWORD = PASSWORD('mynewpassword');"

This should do it as well.

CAVEAT FOR MySQL 5.6 users : TECHNIQUE #2 will not work in Linux for MySQL 5.6. There is a separate file with the temp password for root@localhost ( ${HOME}/.mysql_secret ). You login with that password and change the password from there.

Give it a Try !!!


Import CSV in Postgres with static data in other columns

You will have to create an intermediate table as the target of COPY, and then insert into the final table in the second step.

If you don't have to keep the intermediate table (for cross-checking your results, or as an easy retry point), you can make it temporary and unlogged:

BEGIN;

CREATE TEMP UNLOGGED TABLE intermediate (... columns matching the CSV contents ...);

COPY intermediate FROM 'your.csv' WITH (FORMAT csv, ...);

INSERT INTO final_table (column1, column2, column_with_default1)
SELECT col1, col2, 'default'
  FROM intermediate;

-- check if everything looks fine, row counts match the expectation, etc.

COMMIT;

UNLOGGED will prevent a lot of WAL writes when the CSV is big.  I put the defaults into the SELECT part of the INSERT instead of the (temp) table to save resources - there is no need to store the same default values many times.


How can a fill factor of 0 or 100 be the same?

It is legacy from SQL Server 2000

0 and 100 were different back then

100 meant "fill all pages including all b-tree index levels"
0 meant "leave some space at higher levels in the b-tree index"

Since SQL Server 2005, both mean "fill all pages including all b-tree index levels"

Quotes from BOL (My bold)

SQL Server 2000:

  A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree.

SQL Server 2005

  A fill factor of 0 or 100 creates clustered indexes with full data pages and nonclustered indexes with full leaf pages, but it leaves some space within the upper level of the index tree. Fill factor values 0 and 100 are identical in all respects.

What I can't find is something to show this explicitly in "behaviour changes"


Prefix full text search with numbers in SQL Server

It seems the problem is caused by 8 (and 0,1,2,3,4,5,6,7 and 9) being on the stopword list.

A workaround is to base the table on an empty stopword list (or at least one without the 1-digit numbers):

CREATE FULLTEXT STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test SET STOPLIST empty_stoplist;
ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;


What are the pros/cons of Oracle's Grid Control compared to DB Control?

In Oracle Grid Control you can make a global view of your entire enterprise.
This is an advantage above DB Control.
It is slightly easier to authorize employees for certain targets in Grid Control.
If your mail gateway changes, you have the advantage of only one location to change.
When making clones, Grid Control has the advantage of not having repository parts in the target databases.

On the other hand,
DB Control is closer to the database and most of the times better support for new features.
Oracle Grid Control Requires quite some resources to run and in many cases at least one dedicated dba to keep it running.
You have no single point of failure for management.
Oracle DB Control is very easy to setup.
With a little smart thinking you can build a central page with links to all DB Consoles that you want, complete with links to the documentation.
Grid Control easily gets mixed up when making clones, even without a local repository in the target.

I think it is a personal choice. At the moment, after having wrestled with many GC installations, I think having an own repository for historic data and links to Oracle DB Consoles is not a bad idea.


You could add a calculated column to the table and build an index from the calculation.

For instance, the table would be:

CREATE TABLE dbo.InverterData
(
     InverterID bigint NOT NULL
    , TS datetime NOT NULL
    , ValueA decimal(18, 2) NULL
    , ValueB decimal(18, 2) NULL
    , TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0))
    , CONSTRAINT PK_InverterData PRIMARY KEY CLUSTERED
    (
        TS DESC,
        InverterID ASC
    ) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        )
);

I've modified the name of the TimeStamp column to TS since TimeStamp is a reserved word.

After looking at Paul's comment; I think a good index might be:

CREATE INDEX IX_InverterData_ID_TS151
ON dbo.InverterData (TS15, InverterID);

If you change the WHERE clause to operate on the indexed timestamp column, I get no sort in the plan.  I figure the change to the where clause is likely not a problem since you appear to be selecting on whole days.

SELECT InverterData.InverterID
    , InverterData.TS15
FROM InverterData
WHERE InverterData.TS15 >= '20140107'
    AND InverterData.TS15 < '20160108'
GROUP BY InverterData.InverterID
    , InverterData.TS15
OPTION (RECOMPILE);

Granted, I've not included the other tables in my example, since you have not provided those details.

The plan for the query above is, with 100,000 rows in my sample table:

Having said all that, without the actual table definitions, including the partitioning scheme, etc, it is difficult to tell how well this will actually work for you.

Since adding a (non-persisted) computed column is a meta-data only operation, modifying the table should be nearly instantaneous.  You'd still likely want to do this when you know there are no other transactions (or as few as possible) occurring so the required schema lock (although very short lived) won't block.  The DDL to modify the table is:

ALTER TABLE dbo.InverterData
ADD TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0));


DB2 9.7, create or replace tablespace possible?

You can do it programmatically by querying the catalog. However, there is no command like in DB2.

You can query the view SYSCAT.TBSPACES and look for you tablespace. If it exists drop it, then create a new one.


MySQL for Administrators course from Oracle: worth it?

I previously taught that class.

It's not the worst one Oracle offers, that would be the Performance Tuning course (which I hear was recently refreshed, so maybe it is better).

It depends a lot on your learning style, but you will never learn as much through a CD or internet based training as you will with having the right instructor in front of you who can answer all your questions, and challenge you.

There's not 5 days worth of material in it, since now on the last day they do the DBA exam.  You may also spend the first half of day 1 being marketed to if your instructor follows the schedule (some just exercise civil disobedience and skip the garbage).

You may also find it disturbing the over-focus on things like stored-procedures.  That's more marketing, because MySQL is trying to make sure everyone knows they have them.


Database design is tricky. First some suggestions for you:

Make sure you understand properly how tables works and what constraints and keys are. Do you know what a unique constraint and a non-null constraint is, how primary keys and foreign keys work?
Don't try to skimp on the number of tables. Beginners often have far too few tables in their databases. This actually makes the database more complicated and less efficient, not the reverse. You need to have tables for any 'entity' which exists in your system, even if it's not a concrete 'thing', and even if it's not visible to the user. You are also probably going to want some tables which just express relationships between other entities. Trying to do without these will tie you in knots.
Tables in relational databases are very good at storing exactly one different kind of 'thing'. They are very bad at storing more than one completely different kind of thing. It's hard to build tables which work like objects in OO languages, with inheritance and polymorphism.
Do you definitely need to use a relational database? You might find that a noSQL solution such as MongoDB is what you need. Typically Mongo (there are lots of similar ones) is used to store collections of 'documents'. It is much harder to store relationships between several collections. But it's much easier to store lots of 'things' together, each of which has slightly different types of information. For example, a document could contain either fields for user information, or for group information, or for event information.

Getting back to your problem, one of the following might work for you.

If an activity belongs to a single user, AND a single group, AND a single event, you probably want to have a columns in the activity table for each different type of foreign key. You then probably should do one search for activity by users that are followed, one for groups, and one for events.
If an activity can belong to multiple users, groups and events, you should have 'relation tables'. Each of these will be called something like activity_user, and will have a column which is a foreign key to user, and a column which is a foreign key to activity. You link one to the other by creating a new row in this table. One user can have multiple activities, and one activity can have multiple users, and so on. You query the list of activity which belongs to a user by JOINing user, activity and activity_user all together.
If the activity belongs to all groups that its user belongs to, you will want a similar association table between groups and users. You should probably look up all the users a group contains, and then look for all their activity.
If each activity either belongs to a single user, OR a single group, OR a single event, you should probably divide your activity table into three. Call them useractivity, groupactivity and eventactivity. You do a different query for each of them and then combine the results. These are not 'relation tables' but tables which contain the actual data in each case. This means that it possible to enforce each activity only being associated to one thing. There are other ways of doing this, such as having three foreign key columns, and adding constraints that exactly one of them should be non-null, but they are much uglier and more breakable. One advantage of having these extra tables is that you can them easily add some information to one type of activity which doesn't have to be in the others.


Insert into partitioned tables with select, handle conflicts

Do the same "by hands" like

WITH cte AS ( INSERT INTO dest
              SELECT FROM src
              WHERE (non-matching condition)
            )
UPDATE dest
SET dst.fieldset = src.fieldset
FROM src
WHERE (matching condition)

Dependent by the data and matched/non-matched relation you may swap inserting and updating parts or return processed records identifiers from CTE and use it in main query.


Are there any performance gains for recycling the SQL Error log?

Not in terms of sql server's performance. Better for maintenance and when you want to find specific error - the size of the log file will affect any app or even opening the error log.

To recycle error log - use EXEC sp_cycle_errorlog. You can even schedule this using a sql agent job on a weekly or biweekly (depending on the activity of your server).

If you have Logshipping, to avoid getting your log bloat, I use TF 3226

  How often should you recycle it?

It depends ! I have it every week on my prod servers with a maximum of 10 error logs (Limit the number of error log files before they are recycled setting in sql server log --> configure option) .

This is official from Microsoft - How to manage the SQL Server error log ?


This is similar to pivot table , Try This

create view pivot_questions as (
  select
    questions.*,
    case when question = "question1" then itemvalue end as question1,
    case when itemname = "question2" then itemvalue end as question2,
    case when itemname = "question3" then itemvalue end as question3,
    case when itemname = "question4" then itemvalue end as question4,
    case when itemname = "question5" then itemvalue end as question5
  from questions
);

select * from pivot_questions;


If you afraid that the condition will keep the optimizer from using an index on the columns, you are right, the use of functions in most DBMS has this effect. There are some exceptions to the rule but not in MySQL.

If you want indexes to be considered (and possibly used), you have to conevrt the condition to one without functions applied to columns.

Your condition, (if table2.other_date_column is not nullable), is equivalent to:

 (  table1.indexed_column > table2.other_date_column
 OR table1.indexed_column IS NULL )


I just found the answer myself using CASE WHEN for every column.

I was trying to achieve this because I was moving an audit table that was saving the information of the modified columns in lines (one by one) to another new table copying the whole line information (that could be many columns) in just one line.

Basically I was trying to merge the old data (old structure) to the new table.


Memory Optimized Tables - can they really be so difficult to maintain?

No, in-memory really is this unpolished.  If you are familiar with Agile you will know the concept of "minimal shippable product"; in-memory is that.  I get the feeling that MS needed a response to SAP's Hana and its ilk.  This is what they could get debugged in the timeframe for a 2014 release.

As with anything else in-memory has costs and benefits associated with it.  The major benefit is the throughput that can be achieved.  One of the costs is the overhead for change management, as you mentioned.  This doesn't make it a useless product, in my opinion, it just reduces the number of cases where it will provide net benefit.  Just as columnstore indexes are now updatable and indexes can be filtered I have no doubt that the functionality of in-memory will improve over coming releases.

SQL Server 2016 is now generally available. Just as I supposed, In-Memory OLTP has received a number of enhancements. Most of the changes implement functionality that traditional tables have enjoyed for some time. My guess is that future features will be released at the same time for both in-memory and traditional tables. Temporal tables is a case-in-point. New in this version it is supported by both In-Memory and disk-based tables.


How to know if anybody change the db_owner in SQL server

Are you running any DMV capture tools like sp_whoisactive, redgate, DPA, etc or had tracing enabled? That will make it easier to see if it catches the SQL that ran making the change.

What I would do now is set up a trigger that will track the aspects of who makes permission changes to your database and log those transactions for future review.

Example of DDL Trigger.


Giving EXEC (@Variable) a Column name and Concatenation

You will need to mix your 3 dynamic SQLs into 1 that returns 3 columns, or keep each dynamic SQL but mix their result at the end. There are 2 possible solutions following:

One solution is to use UNION ALL and the PIVOT to get 1 row with 3 columns, all inside the dynamic SQL.

Change this part:

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX)
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

--.....

SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

For this:

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    ;WITH PrePivot AS
    (
        SELECT
            Amount = COUNT(*),
            Type = ''OrphanAuditItems''
        FROM
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
        WHERE
            FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])

        UNION ALL

        SELECT
            Amount = COUNT(*),
            Type = ''OrphanAuditAnswers''
        FROM
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
        WHERE
            AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])

        UNION ALL

        SELECT
            Amount = COUNT(*),
            Type = ''OrphanAuditQuestion''
        FROM
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
        WHERE
            AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])
    )
    SELECT
        P.OrphanAuditItems,
        P.OrphanAuditAnswers,
        P.OrphanAuditQuestion
    FROM
        PrePivot AS T
        PIVOT (
            MAX(T.Amount) FOR T.Type IN ([OrphanAuditItems], [OrphanAuditAnswers], [OrphanAuditQuestion])
        ) AS P '

-- PRINT (@DynamicSQL)
EXEC (@DynamicSQL)

Make sure to use the PRINT to validate the resulting SQL before executing the EXEC.

Another solution is to retrieve the values into variables, using sp_executesql with OUTPUT parameters:

-- Items
DECLARE @OrphanAuditItems INT

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    SELECT
        @OrphanAuditItems = COUNT(*)
    FROM
        #OrphanResults AS O
        CROSS JOIN [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
    WHERE
        FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditItems INT OUTPUT',
    @OrphanAuditItems = @OrphanAuditItems OUTPUT

-- Answers
DECLARE @OrphanAuditAnswers INT

SET @DynamicSQL = N'
    SELECT
        @OrphanAuditAnswers = COUNT(*)
    FROM
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers]
    WHERE
        AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

EXEC sp_executesql
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditAnswers INT OUTPUT',
    @OrphanAuditAnswers = @OrphanAuditAnswers OUTPUT

-- Questions
DECLARE @OrphanAuditQuestion INT

SET @DynamicSQL = N'
    SELECT
        @OrphanAuditQuestion = COUNT(*)
    FROM
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion]
    WHERE
        AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditQuestion INT OUTPUT',
    @OrphanAuditQuestion = @OrphanAuditQuestion OUTPUT

SELECT
    OrphanAuditItems = @OrphanAuditItems,
    OrphanAuditAnswers = @OrphanAuditAnswers,
    OrphanAuditQuestion = @OrphanAuditQuestion

I find the latter a bit more flexible and readable than the first.


Backup Restore, CPU usage and Extended Events

You can fetch CPU utilisation by adding this event:
sqlos.scheduler_monitor_system_health_ring_buffer_recorded
Adding this to your Extended Events session will return CPU utilisation statistics (among others).
When you query the data, there are two fields - process_utilization and system_idle which represents the SQL process CPU utilisation and the idle CPU % respectively. By subtracting process_utilization from system_idle you get the non-SQL used CPU from the server.


Need to setup a sql alert on "waiting tasks" - similar to "waiting tasks" in Activity Monitor

you can trace out the exact query in profiler..below is the query you are referring to

WITH profiled_sessions as (
    SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT
   [Session ID]    = s.session_id,
   [User Process]  = CONVERT(CHAR(1), s.is_user_process),
   [Login]         = s.login_name,
   [Database]      = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end,
   [Task State]    = ISNULL(t.task_state, N''),
   [Command]       = ISNULL(r.command, N''),
   [Application]   = ISNULL(s.program_name, N''),
   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
   [Wait Type]     = ISNULL(w.wait_type, N''),
   [Wait Resource] = ISNULL(w.resource_description, N''),
   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   [Head Blocker]  =
        CASE
            -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
            WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
            -- session is either not blocking someone, or is blocking someone but is blocked by another party
            ELSE ''
        END,
   [Total CPU (ms)] = s.cpu_time,
   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024,
   [Memory Use (KB)]  = s.memory_usage * (8192 / 1024),
   [Open Transactions] = ISNULL(r.open_transaction_count,0),
   [Login Time]    = s.login_time,
   [Last Request Start Time] = s.last_request_start_time,
   [Host Name]     = ISNULL(s.host_name, N''),
   [Net Address]   = ISNULL(c.client_net_address, N''),
   [Execution Context ID] = ISNULL(t.exec_context_id, 0),
   [Request ID] = ISNULL(r.request_id, 0),
   [Workload Group] = ISNULL(g.name, N''),
   [Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
    -- waiting for several different threads.  This will cause that thread to show up in multiple rows
    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,
    -- and use it as representative of the other wait relationships this thread is involved in.
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
ORDER BY s.session_id;


Why does query runtime dramatically increase when made into subquery?

Querying across databases introduces a lot of uncertainty to the query optimizer because it doesn't know much about the other end. For example, if you try to join two tables, it has no idea how big the other table is (10 rows? 1 million?), what is indexed, or how the data is organized. Should it try to use an index to get 10 rows or a full scan for 1 million?

Generally, what you want to do is minimize the amount of interaction or joining that needs to happen between the two. Is there a filter you can put on JoiningTable to reduce the number of rows? Can you structure it into two CTEs that only pull the results together at the end?


I am looking for a query to determine when the snapshot has been finished applying on the subscriber

I don't fully understand the part where you said:

but due to the internal rules and way of working I cannot change them

Could you elaborate on that a little more?...what can you not change, the actions of the Agent Job?
In any case, why not just start the existing SQL Agent jobs in a post-replication script using the system stored procedure sp_start_job?
You can specify the name or id of the Agent Job you want to run like this:
-- Executes the job by job id (unique identifier)
EXEC sp_start_job '53A3FA3E-2AAE-4FF1-A853-DEEC1FF494A2'

-- Executes the job by job name
EXEC sp_start_job 'AgentJobName'


How to recompile all stored procedures and table valued functions in SQL Server database?

You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help:

DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF'));
DECLARE @name SYSNAME;
OPEN C;
FETCH NEXT FROM C INTO @name;
WHILE @@FETCH_STATUS=0 BEGIN
    EXEC sp_recompile @name;
    FETCH NEXT FROM C INTO @name;
END;
CLOSE C;
DEALLOCATE C;

or you could produce ad-hoc SQL and run that via EXEC, takes less code which might be marginally more efficient:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_recompile '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('P', 'FN', 'IF');
EXEC (@sql);

(though I find this form sometimes throws people due to looking set-based but building the string up iteratively, and not being a standard SQL pattern)

Another set of objects that might be a similar concern here is views. You can similarly mark them as needing to be reassessed to make sure stored plans and other meta-data is not stale with sp_refreshview, by small modifications to either the cursor or ad-hoc SQL methods shown above:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'EXEC sp_refreshview '''+[name]+''''+CHAR(10) FROM sys.objects WHERE [type] IN ('V');
EXEC (@sql);

  The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.

There is sometimes more to optimisation than preferring seeks over scans and so forth, sometimes an index scan is more efficient than many executions of seek operations, and the cost estimates upon which the percent figures you are looking at are calculated are that (estimates) at best (a useful guide but sometimes far from at all accurate).

While "throw more memory at it" can help some database performance issues, at least temporarily, if your bottlenecks are very CPU bound rather than memory and/or IO bound then adding more memory will have very little effect.


Update Table with Network Client Connection Status, I need help with DB design. SQL 2008 Ex R2

On the Devices table, add two fields: LastStatus and LastDateTimeChecked.  Update it whenever you insert records into the Connections table.  The Connections is your history, but you shouldn't be hitting it for live reporting queries.


Inserting ID and Date to locate data for C#/VBA application

-Please don't use short, cryptic column names.
-There's no reason to wrap individual statements in explicit transactions.

You'll want to extract the key value from the table variable in a separate statement.  Something like:

Create Procedure dbo.Insert_Student
(
SName varchar(10),
SAge int,
SAdmit_Dt date
)
BEGIN
    Declare @ids table(id int)
    Declare @pk int

    BEGIN TRY

      BEGIN TRANSACTION

      Insert into dbo.Student(Name,Age,AdmissionDt)
      OUTPUT INSERTED.S_ID into @id
      Values  (@SName,@SAge,@SAdmit_Dt);

      set @pk = (Select id from @id);

      Insert into dbo.DB_TRACK (TBL_N, ID, ENTR_DT )
      VALUES ('dbo.Student', @pk,GETDATE());

      COMMIT TRANSACTION

    END TRY
    BEGIN CATCH

      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION

      THROW;

    END CATCH
END


If the first transaction has committed and the second transaction started after the first one committed (more accurately, it obtained its snapshot after the first one committed), then everything that the first transaction wrote will be visible to the second one.


How to delete list of email addresses from MySQL database?

If you have a main table like this

CREATE TABLE users
(
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255),
    ....
    PRIMARY KEY (id)
);

You can built the table that contains the ids of the users with bad email addresses

CREATE TABLE users_invalid_emails
SELECT id.email FROM users WHERE email LIKE 'email%' and email LIKE '%@domain.com';
ALTER TABLE users_invalid_emails ADD PRIMARY KEY (id);

Look over the content of users_invalid_emails. If the emails in that table are to be deleted, you can now do a DELETE JOIN:

DELETE A.* FROM users A INNER JOIN users_invalid_emails B USING (id);

Before you do this, make sure you have a backup of the table.

In your case, the code will be

CREATE TABLE myBBusers_invalid_emails
SELECT id.email FROM my_BBusers WHERE email LIKE 'email%' and email LIKE '%@domain.com';
ALTER TABLE myBBusers_invalid_emails ADD PRIMARY KEY (id);
DELETE A.* FROM my_BBusers A INNER JOIN myBBusers_invalid_emails B USING (id);
DROP TABLE myBBusers_invalid_emails;

Give it a Try !!!


How to update a table from a query result of another table related to the first one?

I think what you are searching for is

CREATE TABLE table_a ("id" int , "firstCreationTime" timestamp)

INSERT INTO table_a VALUES (1,CURRENT_TIMESTAMP - INTERVAL '1 days')

CREATE TABLE table_b ("id" int , "created_at" timestamp)

INSERT INTO table_b VALUES (1,CURRENT_TIMESTAMP - INTERVAL '10 days')
,(1,CURRENT_TIMESTAMP - INTERVAL '20 days'),(1,CURRENT_TIMESTAMP - INTERVAL '30 days')

WITH desired AS(
SELECT "id", MIN("created_at") as "created_at" FROM table_b GROUP BY "id")
UPDATE table_a AS a
SET "firstCreationTime" = "created_at"
FROM desired b
where a.id = b.id

1 rows affected

SELECT * FROM table_a

id | firstCreationTime
-: | :-------------------------
 1 | 2021-03-11 17:13:08.966639

db<>fiddle here


Taking Back up on Non readable Secondary Replica Availability group

Backups are only allowed in readable secondaries, so not really.

Also you need to take in mind that running checkdb might be considered a production workload if you don't run it in production too.

You should be running checkdb on all your databases regardless primary or secondary and also your backups, that way you wouldn't have to license the instance where you restore your backups and run checkdb on them (you are validating backups, not production databases), otherwise MS can make you pay for that, but I'm not a licensing expert, so better ask to confirm this point.


understanding ORA-01792 maximum number of columns in a table or view is 1000

According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug.

Oracle suggests to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the
 checking can be disabled by:

SQL> alter system set "_fix_control"='17376322:OFF';

Alternatively, an interim patch(patch 19509982) can be applied to disable the error by default.

References:

Bug 19509982  Disable raising of ORA-1792 by default
Select Statement Throws ORA-01792 Error


Group together similar queries in sys.dm_exec_sql_text

What you are looking for is the column query_hash, which was introduced in SQL Server 2008. You can find this in sys.dm_exec_query_stats. Here's a sample query to look at top 20 most common patterns:

WITH agg AS (
SELECT TOP 20 COUNT(*) AS similar_query_count, query_hash
FROM sys.dm_exec_query_stats qs
GROUP BY qs.query_hash
ORDER BY similar_query_count DESC
)

SELECT similar_query_count,
SUBSTRING(st.text, (ca.statement_start_offset/2) + 1,
    ((CASE ca.statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE ca.statement_end_offset
    END - ca.statement_start_offset)/2) + 1
) AS statement_text
,st.text AS full_text
FROM agg
CROSS APPLY (SELECT TOP 1 sql_handle, statement_start_offset, statement_end_offset FROM sys.dm_exec_query_stats WHERE query_hash = agg.query_hash) ca
CROSS APPLY sys.dm_exec_sql_text(ca.sql_handle) st

Another related column is query_plan_hash, which is useful for seeing similar execution plans.


I don't know about your application/workload but generally, you should try with:

wsrep_slave_threads=N (Do not use a value for wsrep_slave_threads that is higher than the average given by the wsrep_cert_deps_distance status variable)
innodb_flush_log_at_trx_commit=0 (default is 1)
Have you tried redirect all writes to one node and leave the other as a hot-backup? Some workloads get a huge improvement by doing this.


There is a column named in table but it cannot be referenced from this part of the query

First off, partition by A,B,C order by A,B,C makes no sense. Since you intent to keep the "last" row (meaning the one with the greatest ID), you probably meant:

partition by A,B,C order by ID

Still, the syntax is invalid on top of this. This subquery expression contains a reference to a column of the outer query: (select max(t.rn) from t). The scope of the subquery does not include columns from the outer query so rn is not visible in there. Only columns of the table t are.

You could use a CTE to allow the reference and make the syntax valid:

WITH cte AS (SELECT id, row_number() OVER (PARTITION by a,b,c ORDER BY id) AS rn FROM t)
DELETE FROM t
WHERE  id IN (
    SELECT id
    FROM   cte
    WHERE  rn < (SELECT max(rn) FROM cte)
    )

Still, the query is dangerous nonsense. Do not use this!
Comparing to the greatest row number is logical nonsense as each group of peers might have a different number of dupes. Would delete a lot more than it should.

Simpler, and correct:

DELETE FROM t
WHERE  id IN (
    SELECT t1.id
    FROM  (SELECT id, row_number() OVER (PARTITION by a,b,c ORDER BY id DESC) AS rn FROM t) t1
    WHERE  t1.rn > 1  --  all but the latest
    );

Which, in turn, can be had more cheaply as (assuming all columns NOT NULL!):

DELETE FROM t
WHERE  EXISTS (
   SELECT FROM t AS t1
   WHERE t1.a = t.a
   AND   t1.b = t.b
   AND   t1.c = t.c
   AND   t1.id > t.id
   );

"... where a dupe exists with a greater ID".

Related:

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


Select Ingredients of a product based on unit type

The simple answer looks something like this:

SELECT nip.ProductID, n.Name, ut.UnitName, nip.Unit
FROM NutritionInProduct AS nip
INNER JOIN NutritionMaster AS n ON nip.NutritionID=n.NutritionID
INNER JOIN UnitType AS ut ON nip.UnitTypeID=ut.UnitTypeID
WHERE nip.ProductID=20785
ORDER BY nip.ProductID, n.Name, ut.UnitName;

I have intentionally not pivoted the results because the PIVOT syntax needs you to hard-code the names of the columns, which in this case are dynamically defined in the UnitType table.

While we're at it, you may wish to consider a few revisions to your schema:

There are some naming issues that can confuse other developers, for instance the "Unit" column should probably be called "Content" or something a bit more accurate.
"Unit" should probably also be numeric.
The unit of "Unit" (i.e. grams, ml, etc) is a property of the "NutritionMaster" table. For instance, for "Energy", the unit is "kcal" or "J", while for proteins, carbs, etc, the unit property is "grams", and so on.
NutritionMaster should probably be called something like ProductContents or ProductValues.
Consider adding a "quantity" column to the product, so you can calculate the numbers dynamically. In your example, you've stored the 100g and 60g values in duplicate - ideally, you would want to store just the 100g values, and then multiply those by (60/100).

Here's how I would design a similar solution:

--- A table for all the products:
CREATE TABLE dbo.Product (
    ID          int PRIMARY KEY NOT NULL,
    ProductName varchar(100) NOT NULL,
    ServingSize int NOT NULL,
);

INSERT INTO dbo.Product (ID, ProductName, ServingSize)
VALUES (20785, 'Spring rolls, oven cooked', 60);

--- A table for the different measures (these are the "rows" in your output)
CREATE TABLE dbo.ContentType (
    ID          int PRIMARY KEY NOT NULL,
    ContentType varchar(100) NOT NULL,
    UnitName    varchar(100) NOT NULL
);

INSERT INTO dbo.ContentType (ID, ContentType, UnitName)
VALUES (1, 'Energy', 'kcal'),
       (2, 'Protein', 'g'),
       (3, 'Carbohydrates', 'g'),
       (4, '.. of which sugars', 'g');

--- And the "fact" table with just the 100g contents:
CREATE TABLE dbo.ProductValues (
    ProductID     int NOT NULL,
    ContentTypeID int NOT NULL,
    Value100g     decimal(10, 1) NOT NULL,
    CONSTRAINT PK_ProductValues
        PRIMARY KEY CLUSTERED (ProductID, ContentTypeID),
    CONSTRAINT FK_ProductValues_Product
        FOREIGN KEY (ProductID) REFERENCES dbo.Product (ID),
    CONSTRAINT FK_ProductValues_ContentType
        FOREIGN KEY (ContentTypeID) REFERENCES dbo.ContentType (ID)
);

INSERT INTO dbo.ProductValues (ProductID, ContentTypeID, Value100g)
VALUES (20785, 1, 201.0),   --- Energy, kcal
       (20785, 2, 3.8),     --- Protein, g
       (20785, 3, 28.3),    --- Carbs, g
       (20785, 4, 3.8);     --- ... of which sugars

... and here's how to query those tables:

SELECT p.ProductName,
       CAST(p.ServingSize AS varchar(10))+' g.' AS ServingSize,
       ct.ContentType AS Content,
       STR(pv.Value100g, 10, 1)+' '+ct.UnitName AS ContentsPer100g,
       STR(pv.Value100g*p.ServingSize/100, 10, 1)+' '+ct.UnitName AS ContentsPerServing
FROM dbo.ProductValues AS pv
INNER JOIN dbo.Product AS p ON pv.ProductID=p.[ID]
INNER JOIN dbo.ContentType AS ct ON pv.ContentTypeID=ct.ID;


How do I pass multiple statements into sqlcmd?

There are a couple of problems with this approach:

The immediate problem is that you cannot continue an empty line. So the two lines (following each go ^ line) that are only the single character ^ are causing the line to end and not continue. It has nothing to do with the go. So you can just add a space before the carrot and it will build the full string.
However, even if you fix problem #1, you are still left with invalid SQL since GO needs to be on a line by itself or, at most, optionally followed by some whitespace and an INT value for how many times to repeat the prior batch (e.g. GO 12). But, using line continuation via ^ there won't be any embedded returns so everything will be submitted as a single line, and that is not valid syntax with the GO commands in there.

This leaves you with two options:

If you are submitting SQL that does not require any batch separation, then just get rid of the GO commands (and wouldn't hurt to get rid of the @ sign, and the ENABLEDELAYEDEXPANSION and final GO aren't needed either):

SET SQLSTRING=select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
 ^
ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; ^
ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; ^
 ^
select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null;

ECHO.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -Q "%SQLSTRING%"

If you do need batch separation (e.g. you have a CREATE PROCEDURE statement or something like that), then you can create a SQL file in your CMD script, add each line individually, and then use that as the SQL script that SQLCMD will run. This allows you to embed returns as well as use GO commands:

@ECHO OFF

ECHO. > MultiLine.sql
ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
ECHO from sys.database_mirroring >> MultiLine.sql
ECHO where mirroring_state_desc is not null; >> MultiLine.sql
ECHO GO >> MultiLine.sql
ECHO. >> MultiLine.sql
ECHO ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; >> MultiLine.sql
ECHO ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; >> MultiLine.sql
ECHO GO >> MultiLine.sql
ECHO. >> MultiLine.sql
ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
ECHO from sys.database_mirroring >> MultiLine.sql
ECHO where mirroring_state_desc is not null; >> MultiLine.sql

ECHO.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -i MultiLine.sql


Having issues with a CTE in a Nested Join

This sounds like dynamic SQL where you're constructing a query from other subqueries...

The thing is that subqueries have slightly different rules. A subquery can't contain a CTE, nor can it be ordered without a TOP/OFFSET/FOR XML.

So what you need to do is have a CTE section at the start of your main query, and add your CTE to that. You don't have an ORDER BY, so that's good.

You could find it useful to make WipDet a CTE too... Like this:

WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate < @BegRange
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate <= @EndRange
GROUP BY StockCode, WeekJobStartDate
),
WipDet AS (
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    )
SELECT ...
FROM InvMaster
INNER JOIN WipDet
ON InvMaster.StockCode = WipDet.StockCode


How to delete the primary index of a table in Progress, release 11.6?

You need to first designate a different index as "primary" before you can drop the existing index. The data admin tool has a menu option for doing so:
Database Schema Admin DataServer Utilities PRO/SQL Tools
┌──────────────────────────────────────────────────────────────────────────────┐
│Name: CustNum                    PRIMARY UNIQUE     ACTIVE
│      ┌──────────────────────────────────┐
│Area: │  Cust_Index                      │
│      └──────────────────────────────────┘
┌─────────────────────────────┐┌───────────────────────────────────────────────┐
│ Comments                    ││Seq Field Name            Type        Asc  Abbr│
│ CountryPost                 ││─── ───────────────────── ─────────── ──── ────│
│ CustNum                     ││  1 CustNum               integer     Asc  No  │
│ Name                        ││                                               │
│ SalesRep                    ││                                               │

│                             ││                                               │
│                             ││                                               │
└─────────────────────────────┘└───────────────────────────────────────────────┘
│     Next  Prev  First  Last  Rename  Add  Delete  ChangePrimary  Uniqueness
└──── MakeInactive  Browse  SwitchTable  GoField  ROWID  Undo  ChangeLocal  Exit
 Index Editor                                                  Data Dictionary
Database: s2k (PROGRESS)               Table: Customer

Change the Primary Index of this table.

(Your screen may look different depending on what version of Progress you are running and whether or not it is Windows. But the functionality is the same...)
If you do not have another index you will first need to create one.


I changed a few things so that it ended up like this:

Create TRIGGER [sonfor_ags].[trg_date] ON [sonfor_ags].[MitigationMeasure]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;

    UPDATE t SET DateRemove = CURRENT_TIMESTAMP
    FROM [sonfor_ags].[MitigationMeasure] AS t
    INNER JOIN inserted AS i ON t.OBJECTID = i.OBJECTID
       AND i.Removed = 1; END GO

and now it works perfectly. Thank you very much


SentryOne Plan Explorer properties window

If you right-click on the column header on any of the grids, there is an option called Column Chooser:

This exposes a panel that lists all the columns that are available in that grid. You can drag any of these onto any location in the grid. Here is an example from Top Operations, where both Predicate and Seek Predicates are available:

The really nice thing about the grids is that all operators are listed together. In SSMS, to compare some of the more obscure properties between two operators, you either have to flip between tooltips and/or properties panels, or take screen shots (ugh).

If you post your plan somewhere and describe which index seek you mean, I can show you how to make these properties front and center.


select rows where values equal the result of count(*) group by?

You can either nest your select and use a where clause in the outer select:

select keyword
from (
    select keyword, count(*) as cnt
    from mytable
    group by keyword
) as t
where cnt < 4

or you can use having which essentially is a shorter form of the above:

select keyword
from mytable
group by keyword
having count(*) < 4


Max number of tables (objects) in SQL Server editions (compact, express)

The capacity specifications are valid for Express apart from the limitations listed in:

Features Supported by the Editions of SQL Server 2008 R2
Maximum Number of Processors Supported by the Editions of SQL Server (max 1 cpu)
Memory Supported by the Editions of SQL Server (max  1GB ram)

So yes, the maximum number of tables is limited by the 2,147,483,647 object maximum in Express.

Compact Edition differs substantially from Express, with very different capacity specifications. Haven't found an explicit reference to the maximum table or object count in CE but the 4GB size limit would prevent you getting anywhere near 2,147,483,647.


Different plans on Readonly Secondary Replica

It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.

Details here:

FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014


Return multiple columns (int datatype) inside a CASE expression

If you do not want to return anything (i.e. if you want to omit the row entirely) when the condition is not met, then just do not use CASE, use your condition in the WHERE clause:

WITH
  ...
SELECT
  a.id AS a_id,
  b.id AS b_id
FROM
  ...
WHERE
  ...
  AND (b.id - a.id) > 1
;

If, however, you want to return something (like nulls) in both columns instead and would like to avoid repeating the condition, then you could use OUTER APPLY, but you would probably need to rewrite your comma join to use the explicit join syntax, like this:

WITH
  ...
SELECT
  x.a_id,
  x.b_id
FROM
  cte AS a
  INNER JOIN cte AS b ON b.Rownumber = a.Rownumber + 1
  OUTER APPLY
  (
    SELECT
      a.id AS a_id,
      b.id AS b_id,
    WHERE
      (b.id - a.id) > 1
  ) AS x
;

Alternatively, you could replace the inner join with an outer join in this manner:

WITH
  ...
SELECT
  CASE WHEN b.id IS NOT NULL THEN a.id END AS a_id,
  b.id AS b_id
FROM
  cte AS a
  LEFT JOIN cte AS b ON b.Rownumber = a.Rownumber + 1 AND (b.id - a.id) > 1
;

Note that if you are using SQL Server 2012 or later version, you can avoid the self-join entirely by using the LEAD function:

WITH cte AS
  (
    SELECT
      id = id,
      next_id = LEAD(id) OVER (ORDER BY id ASC
                               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM
      dbo.Test
  )
SELECT
  id,
  next_id
FROM
  cte
WHERE
  next_id - id > 1
;


What is a non-atomic yield in the context of SQL Server?

The following is what an Atomic block in SQL is:

  Every natively compiled stored procedure contains exactly one block
  of Transact-SQL statements. This is an ATOMIC block.

This is how Atomic blocks relate to transactions and error handling:

BEGIN ATOMIC

  If a transaction already exists on a session (because a batch executed
  a BEGIN TRANSACTION statement and the transaction remains active),
  then starting an atomic block will create a savepoint in the
  transaction. If the block exits without an exception, the savepoint
  that was created for the block commits, but the transaction will not
  commit until the transaction at the session level commits. If the
  block throws an exception, the effects of the block are rolled back
  but the transaction at the session level will proceed, unless the
  exception is transaction-dooming. For example a write conflict is
  transaction-dooming, but not a type casting error.

Atomic Blocks in Native Procedures


How does CALs Licenses work with Azure SQL

There are no license restrictions in Azure SQL Database. No CAL licenses are required.  You pay for the database, and you may use it for any kind of application you wish and for any number of hosts or users. You only need to take in consideration Azure SQL Database limits for the maximum number of concurrent sessions allowed on each tier. Here are the limits for the DTU model and here you will find limits for the vCore model.


The two reasons that I find the most compelling not to use SELECT * in SQL Server are

Memory Grants
Index usage

Memory Grants

When queries need to Sort, Hash, or go Parallel, they ask for memory for those operations. The size of the memory grant is based on the size of the data, both row and column wise.

String data especially has an impact on this, since the optimizer guesses half of the defined length as the 'fullness' of the column. So for a VARCHAR 100, it's 50 bytes * the number of rows.

Using Stack Overflow as an example, if I run these queries against the Users table:

SELECT TOP 1000 DisplayName
FROM dbo.Users AS u
ORDER BY u.Reputation;

SELECT TOP 1000 DisplayName, u.Location
FROM dbo.Users AS u
ORDER BY u.Reputation;

DisplayName is NVARCHAR 40, and Location is NVARCHAR 100.

Without an index on Reputation, SQL Server needs to sort the data on its own.

But the memory it nearly doubles.

DisplayName:

DisplayName, Location:

This gets much worse with SELECT *, asking for 8.2 GB of memory:

It does this to cope with the larger amount of data it needs to pass through the Sort operator, including the AboutMe column, which has a MAX length.

Index Usage

If I have this index on the Users table:

CREATE NONCLUSTERED INDEX ix_Users
    ON dbo.Users
(
    CreationDate ASC,
    Reputation ASC,
    Id ASC
 );

And I have this query, with a WHERE clause that matches the index, but doesn't cover/include all the columns the query is selecting...

SELECT u.*,
       p.Id AS [PostId]
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE  u.CreationDate > '20171001'
       AND u.Reputation > 100
       AND p.PostTypeId = 1
ORDER BY u.Id

The optimizer may choose not to use the narrow index with a key lookup, in favor of just scanning the clustered index.

You would either have to create a very wide index, or experiment with rewrites to get the narrow index chosen, even though using the narrow index results in a much faster query.

CX:

 SQL Server Execution Times:
   CPU time = 6374 ms,  elapsed time = 4165 ms.

NC:

 SQL Server Execution Times:
   CPU time = 1623 ms,  elapsed time = 875 ms.


tds: unable connect to SQL Server using servername specified in freetds.conf file

tds7_get_instance_port means freedts is using udp 1434 to query the port of the instance (source: net.c). But this should only be called if the instance is specified. Please doublecheck your freetds.conf


Oracle 11gR2 IMP Won't Import Table in Dump File From 10g

Your table is owned by USERGOD, but you are importing as DIGITALNOISE.
Yes, you have the IMPORT FULL DATABASE, but you are not doing a full import.

Using FROMUSER/TOUSER is one solution.
Performing a full import by adding the FULL=Y option is another.


Can I implement a 'gapless' identity column in Oracle?

The only way to guarantee a gapless series (assuming no deletes) is to serialize - almost always a bad idea

With a sequence or identity you cannot assume your series will be gapless as @a_horse mentions - but it looks like you are assuming that even with gaps, there is some relationship between sequence order and insertion order - this is not true either! A higher sequence number could be inserted before a lower. Therefore trying to join "consecutive" rows is a meaningless concept

example:

create sequence seq;
create table t1(id integer);
create table t2(id integer);

--session 1:
insert into t1 select seq.nextval from dual connect by level<1000000;
commit;

--session 2:
insert into t2 select seq.nextval from dual connect by level<1000000;
commit;

result:

select min(id), max(id) from t1 union all select min(id), max(id) from t2;

MIN(ID)                MAX(ID)
---------------------- ----------------------
1                      1671679
356547                 1999998


Alter on huge table consumes all disk space

Make use of the new, better, ways of doing ALTER:

SET SESSION alter_algorithm='INSTANT' ;
ALTER TABLE foo ADD COLUMN ... ;  -- without the AFTER

SET SESSION alter_algorithm='NOCOPY' ;
ALTER TABLE foo ADD INDEX name(col1, col2);

If these cannot be done with the requested algorithm, they will give you an error without doing the ALTER.  From there, you can decide what to do.

A single-column index on a "flag" is rarely useful.  Perhaps you don't need the new index?


Overcome SubQuery Returned More Than 1 Result

No GROUP BY, no subqueries needed. The answer assumes that @Two has only one row per employee:

SELECT
    a.employee,
    [EOD] = a.eod,
    [Paid] = CASE WHEN a.eod = MIN(a.eod) OVER (PARTITION BY a.employee)
                 THEN b.amtpaid
                 ELSE 0
             END
FROM
    @One a
    JOIN @Two b
    ON  a.employee = b.employee ;

Another way would be to use window functions and a left join:

SELECT
    a.employee,
    [EOD] = a.eod,
    [Paid] = COALESCE(b.amtpaid, 0)
FROM
    ( SELECT a.employee, a.eod,
             rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                     ORDER BY a.eod)
      FROM @One AS a
    ) AS a
    LEFT JOIN @Two b
    ON  a.employee = b.employee
    AND a.rn = 1 ;

which would be easier to modify if @Two has more than one rows per employee and we want the sum:

SELECT
    a.employee,
    [EOD] = a.eod,
    [Paid] = COALESCE(b.amtpaid, 0)
FROM
    ( SELECT a.employee, a.eod,
             rn = ROW_NUMBER() OVER (PARTITION BY a.employee
                                     ORDER BY a.eod)
      FROM @One AS a
    ) AS a
    OUTER APPLY
    ( SELECT amtpaid = SUM(b.amtpaid)
      FROM @Two b
      WHERE a.employee = b.employee
        AND a.rn = 1
    ) AS b ;