PostgreSQL force upper case for all data

Forcing correct values is one thing. Simple CHECK constraints per column can do the job reliably:

CREATE TABLE foo
   foo_id serial PRIMARY KEY
 , text_column text CHECK (upper(text_column) = text_column)
 , ...
);

Auto-correcting all input is another thing, and not as simple. But it can be done with a generic trigger function that looks up column names and data types in the system catalog and converts all character data to upper case. Safe enough, but not quite as fast and not as bullet-proof because more complex and triggers can more easily be circumvented or counteracted by other triggers.

One generic trigger function for all:

CREATE OR REPLACE FUNCTION trg_all_upper()
  RETURNS trigger AS
$BODY$
DECLARE
   -- basic character types. possibly add citext, domains or custom types
   _typ   CONSTANT regtype[] := '{text, bpchar, varchar, \"char\"}';
   _sql   text;
   _found bool;
BEGIN
   SELECT INTO _sql, _found
          'SELECT ' || string_agg(
              CASE WHEN a.atttypid = ANY(_typ)
              THEN format ('upper(%1$s)::%2$s AS %1$s'
                         , a.col, a.atttypid::regtype)
              ELSE col END
            , ', ') || ' FROM (SELECT ($1).*) t'
        , bool_or(a.atttypid = ANY(_typ))
   FROM  (
      SELECT a.atttypid, quote_ident(attname) AS col
      FROM   pg_attribute a
      WHERE  a.attrelid = TG_RELID  -- object ID of table that fired trigger
      AND    a.attnum >= 1          -- exclude tableoid & friends
      AND    NOT a.attisdropped     -- exclude dropped columns
      ORDER  BY a.attnum
      ) a;

   -- RAISE NOTICE '%', _sql;
   IF _found THEN
      EXECUTE _sql USING NEW INTO NEW;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql VOLATILE;

A trigger per table:

CREATE TRIGGER all_upper_bef_insupd
BEFORE INSERT OR UPDATE ON big
FOR EACH ROW EXECUTE PROCEDURE trg_all_upper();

Converts all values in columns with a character data type as defined in  _typ to upper case.
Tested in Postgres 9.4.

Related:

Create Alias for PostgreSQL Table
Replace empty strings with null values
How to set value of composite variable field using dynamic SQL


Problems installing Postgis extension on Ubuntu 14.04

As we can see here all the required packages are rightly updated in the official repository.
To solve the problem you can find a good start point here.


Delete into new table (like select into)

Not that I know of. The OUTPUT.. INTO clause requires an existing table.

On a related note

Using SELECT.. INTO as opposed to INSERT INTO.. SELECT or OUTPUT.. INTO can have negative side-effects depending the situation. Some examples why I think you should create the table first:

If you're creating a table inside of a transaction, SQL Server may place a lock on (parts of) the database schema, which would prevent other users from creating, altering or dropping database objects. I'm assuming that this could even affect reading the schema, such as opening a database in Object Explorer in Management Studio. Create the table outside the transaction, then populate inside the transaction.
If you want to assign a clustered index to your new table, and that clustered index matches the populating query's sort order, SELECT.. INTO will create the table as a heap (unsorted), so when you apply the clustered index after, SQL Server will have to sort the contents of the table all over again.


SQL Server: UPDATE permission for table variable

Martin Smith's comment ("Does the subquery itself work?") was ultimately the answer and highlighted my insufficient forensic work! ;-)

The subquery was returning ROW COUNTS from tables:

...
INNER JOIN sys.dm_db_partition_stats AS ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
...

The fixed roles I was using don't give VIEW DATABASE STATE permission (which is required to view the db_partition_stats table).  So, I'll either need to mess with the roles for these users or find another workaround.

I guess it's worth noting that if you come across this circumstance, you need to deconstruct your query.  The fixed db roles that I refer to CAN CREATE, INSERT, UPDATE, and DELETE records from a table variable.


Adding PRIMARY KEY and IDENTITY attributes to SQL Server 2008 tables

The easiest way to do this is to use the SSMS GUI, right click the designer and use "Generate Scripts". That generates you a table rebuild that preserves data and makes the schema changes for you.

If you care about performance you need to use the SWITCH technique. Make the schema of the two tables compatible with switch, then switch, then alter the schema back. That way you can add the IDENTITY property without (or with reduced) data movement.

  SWITCH from a table with no PK to a table with a PK

Yes, that doesn't work. Create a compatible table for use with switch. You can switch heaps just fine, but both sides need to be heaps.

You can write the rebuild script yourself.

Create a table that has the desired schema
IDENTITY_INSERT ON on that table
INSERT...SELECT the data over to that table
DBCC CHECKIDENT(RESEED) the temp table
Delete the original table and rename the temp table to have the original name


You may want to turn on AUTO VACUUM? AWS Docs suggests that as a best practice

Set the autovacuum and once it is done, you can turn it off if you wish.

If you want to really run it in a terminal and then disconnect, refer to this answer:
How to execute a query from psql without waiting for the result?


Why is Include Actual Execution Plan not included in @@Rowcount when it produces a row count?

That DONE_IN_PROC message from the execution plan is completely independent of your WHILE loop, and isn't what's being checked in your conditional BREAK. Martin deleted his answer but this is very much by design. Your query shouldn't behave differently depending on whether or not your client tool is retrieving execution plans - that would be bad. Execution plans are for monitoring and reviewing, not for influencing actual behavior.

Note that the inverse is also not necessarily a given - not all things that will populate @@ROWCOUNT will lead to the DONE_IN_PROC message, e.g.

DECLARE @foo int, @x varchar(32) = 'hello';
SET @foo = @@ROWCOUNT;
PRINT @foo;

This will yield 1 in the messages pane, but you will not see 1 row(s) affected.

Now, I've always been a big fan of using SET NOCOUNT ON;, which renders this question rather moot. But it seems the impact of DONE_IN_PROC in modern versions is not what it used to be when I formed this opinion:

Performance Surprises and Assumptions : SET NOCOUNT ON


What exact version?

DELETE on a huge table will take a huge amount of time because it is building an "undo" log in case of a crash.  Don't use that technique.

What was the value of innodb_file_per_table when the table was built?

If OFF (or 0), the table is in ibdata1, which will not shrink and return space to the OS.  It will only free up blocks for subsequent use.
If ON (or 1), then DROP TABLE will be much faster, but still not instantaneous.
If you have FOREIGN KEYs connecting the table (in either direction), well, let's see them.  (SHOW CREATE TABLE)

If you need to delete a big part of a table, here are several techniques.  This discusses how to do the delete in chunks, and takes Replication issues into account.


Oracle 11g encrypt traffic from clients to database server

Check the licensing again, these options became free about 2 years ago.

http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC143

  Network encryption (native network encryption and SSL/TLS) and strong
  authentication services (Kerberos, PKI, and RADIUS) are no longer
  part of Oracle Advanced Security and are available in all licensed
  editions of all supported releases of the Oracle database.


How can I inform myself that an automatic SQL Server process has worked?

count lines/records in your textfile
import
count records after import
if count1=count2 then continue
tidy up fields  (update blablabla set changefield='new' where myfield='badinfo')
test tidy up :  select count(*) from blablabla where myfield='badinfo' and changefield<>'new'
if testTidyUp-count = 0 then cleanup = correct ... continue
count records after tidy up
export to production
count records in production
if count3=count4 then ... let mysql know all is 'ok'

All the else ... mail that something is wrong


Can I modify the fill factor value while a database is in use?

"yes", you can change the fill factor while the database is in use... If you can accept the blocking! I've found that it is seldom a good idea to have it different that 0 (or 100).

The dbreindex will not work on your heaps, so you should create a clustered index instead. (see for instance this Kimberly Tripps blog post or this https://www.brentozar.com/blitzindex/sp_blitzindex-self-loathing-indexes/ on "heaps with forwarded records"
You may also want to download sp_Bliz from https://www.brentozar.com/blitz/. This will help you investigate what may help your server run faster.


Create new database with large initial size

Unless you're using Instant File Initialization, SQL Server must zero out all sectors for the files.  This is a very time consuming process, especially for 250 GB (data+log).  In order to speed things up, enable this feature and then SQL Server will not zero out the space for the data files.

Please note, SQL Server will always zero out the log file, so you will still be limited by that. Another reference for log file initialization


MS-Access: How do I go about building a database with inconsistent data sources?

You are building a basic BI/DW environment. You need to get:

Cleaner data, as it is generally more efficient to fix the problem upstream.
A real database. I would not use Access for anything important like that. It gets corrupt and can only grow to a certain size. Ask the client if they can host a DB to hold this data or can spin something up in the cloud.
Use some form of tool like ETL to extract and load the data into your database.

If you want to go really cheap, Microsoft's Tableau competitor (Power BI) has basic ETL functionality built into it, can source directly from flat files, can call R scripts for clean-up before presenting data, and can store a limited amount of data internally.


PostgreSQL 8.4 replication of more than 100 tables

The solution was to use Postgres version 9.1 and Oracle Version 11.2.0.1.0.

I am running oracle in Debian Wheezy all do i need is to do some upgrades in certain libraries and now it works good:

Version of ODBC:

ii  odbc-postgresql:amd64              1:09.01.0100-1+deb7u1         amd64        ODBC driver for PostgreSQL

Version of Libssl required by ODBC:

ii  libssl1.0.0:amd64                  1.0.1e-2+deb7u13              amd64        SSL shared libraries

Postgres version in the another server:

ii  postgresql-9.1                     9.1.15-0+deb7u1               amd64        object-relational SQL database, version 9.1 server

This is how do i install Oracle with PostgreSQL 9.1 datalink and its working very fine now, also with Mysql 5.5.

Think now i will be able with this new version to do easy replication just like i do in the Mysql instances!


Basically you want this.
The problem is that john has 2 user_ids 1 and 6
Else i would put the user_id also in the GROUP BY
AND if you only the highest 3 You have to add also LIMIT 3
A MySQL 5.x Solution to your problem

CREATE TABLE sumprice (
  `id` INTEGER,
  `name` VARCHAR(4),
  `price` INTEGER,
  `email` VARCHAR(9),
  `user_id` INTEGER
);

INSERT INTO sumprice
  (`id`, `name`, `price`, `email`, `user_id`)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5'),
  ('2', 'John', '50', '@john.com', '6'),
  ('3', 'Mike', '100', '@mike.com', '5'),
  ('4', 'John', '50', '@john.com', '1'),
  ('5', 'Mark', '50', '@mark.com', '3'),
  ('6', 'Mike', '100', '@mike.com', '5');

SELECT   @id:= @id +1  `id`, `name`, SUM(`price`) price , `email`, MAX(`user_id`)
FROM sumprice,(SELECT @id:=0) a
GROUP BY `email`, `name`
ORDER BY price DESC

id | name | price | email     | MAX(`user_id`)
-: | :--- | ----: | :-------- | -------------:
 1 | Mike |   300 | @mike.com |              5
 2 | John |   100 | @john.com |              6
 3 | Mark |    50 | @mark.com |              3

db<>fiddle here
In Mysql 8

CREATE TABLE sumprice (
  `id` INTEGER,
  `name` VARCHAR(4),
  `price` INTEGER,
  `email` VARCHAR(9),
  `user_id` INTEGER
);

INSERT INTO sumprice
  (`id`, `name`, `price`, `email`, `user_id`)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5'),
  ('2', 'John', '50', '@john.com', '6'),
  ('3', 'Mike', '100', '@mike.com', '5'),
  ('4', 'John', '50', '@john.com', '1'),
  ('5', 'Mark', '50', '@mark.com', '3'),
  ('6', 'Mike', '100', '@mike.com', '5');

SELECT   ROW_NUMBER() OVER(
        ORDER BY SUM(price) DESC
    )  `id`, `name`, SUM(`price`) price , `email`, MAX(`user_id`)
FROM sumprice
GROUP BY `email`, `name`
ORDER BY price DESC

id | name | price | email     | MAX(`user_id`)
-: | :--- | ----: | :-------- | -------------:
 1 | Mike |   300 | @mike.com |              5
 2 | John |   100 | @john.com |              6
 3 | Mark |    50 | @mark.com |              3

db<>fiddle here
In Oracle it is the same

CREATE TABLE sumprice (
  id NUMBER(5),
  name VARCHAR(4),
  price NUMBER(5),
  email VARCHAR(9),
  user_id NUMBER(5)
);

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES
  ('1', 'Mike', '100', '@mike.com', '5');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES('2', 'John', '50', '@john.com', '6');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('3', 'Mike', '100', '@mike.com', '5');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('4', 'John', '50', '@john.com', '1');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('5', 'Mark', '50', '@mark.com', '3');

INSERT INTO sumprice
  (id, name, price, email, user_id)
VALUES  ('6', 'Mike', '100', '@mike.com', '5');

SELECT     ROW_NUMBER() OVER(
        ORDER BY SUM(price) DESC
    ) id ,name, SUM(price) price , email, MAX(user_id)
FROM sumprice
GROUP BY email, name
ORDER BY price DESC

ID | NAME | PRICE | EMAIL     | MAX(USER_ID)
-: | :--- | ----: | :-------- | -----------:
 1 | Mike |   300 | @mike.com |            5
 2 | John |   100 | @john.com |            6
 3 | Mark |    50 | @mark.com |            3

db<>fiddle here


If you're doing it through a T-SQL statement such as below, then no table drop will occur and you can safely do it in a production environment:

alter table <table> alter column <column> nvarchar(biggernumber) [not] null

If you do it through the SSMS Design Table GUI, it will depend on what script it decides to use to implement the change. Sometimes it will insert data into a temporary table, drop the original table, create a new version of that table, and insert it back into the new one. An easy way to find out what it will do is to click the "Generate Script" button and look at the T-SQL it plans on executing.


If you are moving the users to another DB Server running the same major version of MySQL, copying mysql.user is not sufficient. If the users have access to specific databases, copying mysql.user brings the user and the password.

Then, you would have to copy the following

mysql.db for database-level grants
mysql.tables_priv for table-level grants
mysql.columns_priv for column-level grants

Here is logical way to dump it: as SQL GRANT commands !!!

SUGGESTION #1

If you already have Percona Toolkit installed, run pt-show-grants to a text file

GRANT_FILE=MyDatabaseUSers.sql
pt-show-grants -uroot -p > ${GRANT_FILE}

SUGGESTION #2

If you don't have Percona Toolkit and can't wait, here is my personal emulation of it

GRANT_CMDS=/tmp/ShowGrantsToExport.sql
GRANT_FILE=MyDatabaseUSers.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')"
SQL="${SQL} FROM mysql.user WHERE user<>'' AND host<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${GRANT_CMDS}
mysql ${MYSQL_CONN} < ${GRANT_CMDS} | sed 's/$/;/g' > ${GRANT_FILE}

EPILOGUE

On the new DB Server, after migrating the data, you login to mysql as root and run

mysql> source MyDatabaseUSers.sql

GIVE IT A TRY !!!


How to get Page Name given Page ID in Oracle Application Express

Everything you want (and a lot more) is available in the view APEX_050000.APEX_APPLICATION_PAGES

SYS@mydb01> describe apex_050000.APEX_APPLICATION_PAGES
Name                        Null?    Type
--------------------------- -------- -------------------------------------
WORKSPACE                   NOT NULL VARCHAR2(255)
WORKSPACE_DISPLAY_NAME               VARCHAR2(4000)
APPLICATION_ID              NOT NULL NUMBER
APPLICATION_NAME            NOT NULL VARCHAR2(255)
PAGE_ID                     NOT NULL NUMBER
PAGE_NAME                   NOT NULL VARCHAR2(255)
PAGE_TITLE                           VARCHAR2(255)
PAGE_MODE                            VARCHAR2(16)
...
PAGE_ALIAS                           VARCHAR2(255)

And a sample query would be

select WORKSPACE, APPLICATION_NAME,
   PAGE_ID, PAGE_NAME, PAGE_TITLE, PAGE_MODE
from  APEX_050000.APEX_APPLICATION_PAGES
where workspace  = 'MY_WS' and page_id=210;

There is a built-in substitution string APP_PAGE_ID and APP_ID, so you could do this in your page to use the current page ID and application ID to get the page title:

select PAGE_TITLE from APEX_050000.APEX_APPLICATION_PAGES
where PAGE_ID = :APP_PAGE_ID and APPLICATION_ID = :APP_ID;

See https://docs.oracle.com/cd/E59726_01/doc.50/e39147/concept_sub.htm#BEIIBAJD for all the built-in substitution strings.


SQL Server: How does Uniquifier work with Replication?

Replication requires a primary key constraint on the replicated tables, not a clustered index. Primary key constraints enforce uniqueness. If you have more than one publisher to the same subscriber, you'll need to ensure that there are no collisions. This is often done with identity columns. For instance, one server would use negative numbers, the other server would use positive numbers.

If you also have a non-unique clustered index, the uniqifier doesn't get transferred as it's not part of the INSERT/UPDATE/DELETE command. The uniquifier value will be added on the subscriber and would be different than the value on the publisher.


Ternary relationship: what's the difference between having single table and having multiple tables?

The two solutions model different rules.  With the ternary table you are saying that a company may only have certain products in a particular sector.  In a different sector there will be a different set of products, though the two sets may overlap, of course.

With the binary tables you are stating that sector has no influence over what products a company my relate to.  Similarly the company has no influence over what products are in which sector.

The choice between these alternatives will be determined by your business rules.  It cannot be answered by an abstract, academic discussion.  I have found it best to name the relationship between the entities.  Saying that company is related to product, say, is interesting; saying why company is related to product is even better.  "Company buys product" is a different piece of information to "company makes product" or "company does not have security clearance to use product".  By doing this I often discover new relationships, attributes and entity types.  You may end up needing both the binary and ternary tables!

Edit: For the rules

a Company produces many products / each product is produced by exactly one company
a Company reported in exactly one sector / each sector reports on many companies
a Product is sold in exactly one sector / each sector has available many products.

I would have these entity types

Sector - SectorID

Company - CompanyID, SectorID

Product - ProductID, CompanyID

If any of your rules are many-to-many then you'll need the binary association tables.

As an aside, the relationship names "has", "belongs to" and "is a" often hide more than they illuminate.  If you find your BAs using these ask them to have another think.


What does this error mean "A slave with the same server_uuid/server_id as this slave has connected to the master;"?

You must have a different value in the my.cnf on each server in a replication setup.  server_id must be different on each machine, else replication will be trying to go in a loop.


Convert existing subscriber Database to a publisher database. Current publisher will be subscriber

Best option for you will be to reestablish replication with reverse role but avoid a full snapshot and re-sync to minimize downtime.
I will outline high level steps here and if you need further details let me know.

Once downtime start (confirm app servers are not making any new connection/writing to publishing articles):

Continue running the distribution agent jobs till undelivered command count is zero.
Stop the distribution agent job/jobs.
Remove replication (publisher and subscriber only) but keep the distributor as is.
Add remote distributor to new publisher if it is a separate instance (old subscriber).

USE [Master]
GO
exec sp_adddistributor @distributor = N'instance name where distribution database reside', @password =
N'xxxxxxxxx'
GO

Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.
Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

  replication support only  Provides automatic generation at the
  Subscriber of article custom stored procedures and triggers that
  support updating subscriptions, if appropriate. Assumes that the
  Subscriber already has the schema and initial data for published
  tables. When configuring a peer-to-peer transactional replication
  topology, ensure that the data at all nodes in the topology is
  identical. For more information, see Peer-to-Peer Transactional
  Replication.

  Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher,
       [name]     AS Agent,
       publication,
       a.publisher_db,
       subscriber_db,
       art.article,
       art.destination_object,
       art.source_object,
       undelivcmdsindistdb,
       delivcmdsindistdb,
       s.article_id,
       agent_id,
       a.publisher_id,
       subscriber_id
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock)
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock)
                    ON s.agent_id = a.id
       JOIN master.dbo.sysservers AS ss WITH (nolock)
         ON a.publisher_id = ss.srvid
       JOIN distribution.dbo.msarticles AS art WITH (nolock)
         ON s.article_id = art.article_id
WHERE  subscriber_db <> 'virtual'
       AND subscriber_db = 'SubscriberDBName'
ORDER  BY undelivcmdsindistdb DESC
COMPUTE sum(undelivcmdsindistdb)


I'm not sure this is what are you looking for because IMHO you must deal with recursive functions.

Using only centers table

select IdCenter, IdNode
from
(
    select     ce.IdCenter, ce.IdNodeIn IdNode, 0 as nodeOrder, Row
    from       center ce
    union all
    select     ce.IdCenter, ce.IdNodeOut IdNode, 1 as nodeOrder, Row
    from       center ce
) x
order by Row, idCenter, nodeOrder;

Using connection table to set the correct order

with rowOrder as
(
    select    ce.IdCenter, ce.IdNodeIn, ce.IdNodeOut,
              case when co.Row is null then 3 else co.Row end Row
    from      center ce
    left join connection co
    on        co.IdCenterFrom = ce.IdCenter
)
select IdCenter, IdNode
from (
       select IdCenter, IdNodeIn IdNode, Row from rowOrder
       union all
       select IdCenter, IdNodeOut IdNode, Row from rowOrder
     ) x
order by Row;

This is the final result on both cases:

| IdCenter | IdNode |
|----------|--------|
| 470      | 1      |
| 470      | 5218   |
| 22       | 5298   |
| 22       | 1      |
| 20       | 5242   |
| 20       | 5284   |

Rextester here


Concatenate all values of the same XML element using XPath/XQuery

This might work for you:

select @MyXml.value('/R[1]', 'varchar(50)')

It picks up all text() elements from the first R and below.

If you just want all text() you can do

select @MyXml.value('.', 'varchar(50)')

If you want the values for I and J separate do this instead.

select @MyXml.query('/R/I/text()').value('.', 'varchar(50)'),
       @MyXml.query('/R/J/text()').value('.', 'varchar(50)')


SQL Query for searching table 'C' records only if records from table 'A' has specific values

Vivek - just use (Left or Right) outer joins. This will make sure that the null values will be shown if the data does not exist within the joined table.


Is rowversion unique across the database? Or per table?

According to this link, it 'should' be unique across all tables in a database.

Here is the 'should' part - there is a caveat in the documentation:

  Duplicate rowversion values can be generated by using the SELECT INTO
  statement in which a rowversion column is in the SELECT list. We do
  not recommend using rowversion in this manner.

And here is a summary of the information in the link:

  Each database has a counter that is incremented for each insert or
  update operation that is performed on a table that contains a
  rowversion column within the database. This counter is the database
  rowversion. This tracks a relative time within a database, not an
  actual time that can be associated with a clock. A table can have only
  one rowversion column. Every time that a row with a rowversion column
  is modified or inserted, the incremented database rowversion value is
  inserted in the rowversion column.


Update: I have change a bit the stored procedure to cope with null values.

Update: I have added fiddle

I have deduced your schema. I think that you schema is generated by:

create table  os_TICKET (
  ticket_id int not null primary key,
  number int not null
);

insert into os_TICKET values (5,592593);

create table os_form_entry_values (
  entry_id int not null,
  field_id int not null,
  value varchar(100)
);

insert into os_form_entry_values values
(5, 4, "111111"),
(5, 87, "Mr"),
(5, 117, "05/01/2011"),
(5, 3, "0091981123232");

create table os_form_field(
  id int not null primary key,
  name varchar(16)
);

insert into os_form_field values
(4, "SSN"),
(87, "title"),
(117, "dob"),
(3, "mobile");

To pivot the SSN field you can use this query:

select tkt.ticket_id,  tkt.number, SSN
from os_TICKET tkt
inner join (select val.entry_id, val.value as SSN
            from os_form_entry_values val
            where val.field_id in (4))  as SSN
   on tkt.ticket_id = SSN.entry_id
where tkt.ticket_id =5;

  +-----------+--------+--------+
  | ticket_id | number | SSN    |
  +-----------+--------+--------+
  |         5 | 592593 | 111111 |
  +-----------+--------+--------+

To put attribute value as column, we must join table. We can extends the previous query to include the other attributes:

select tkt.ticket_id,  tkt.number, SSN, title, dob, mobile
from os_TICKET tkt
inner join (select val.entry_id, val.value as SSN
            from os_form_entry_values val
            where val.field_id in (4))  as SSN
  on tkt.ticket_id = SSN.entry_id
inner join (select val.entry_id, val.value as title
            from os_form_entry_values val
            where val.field_id in (87))  as title
  on tkt.ticket_id = title.entry_id
inner join (select val.entry_id, val.value as dob
            from os_form_entry_values val
            where val.field_id in (117))  as dob
  on tkt.ticket_id = title.entry_id
inner join (select val.entry_id, val.value as mobile
            from os_form_entry_values val
            where val.field_id in (3))  as mobile
  on tkt.ticket_id = title.entry_id
where ticket_id = 5;

  +-----------+--------+--------+-------+------------+---------------+
  | ticket_id | number | SSN    | title | dob        | mobile        |
  +-----------+--------+--------+-------+------------+---------------+
  |         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 |
  +-----------+--------+--------+-------+------------+---------------+
  1 row in set (0.00 sec)

The above query is using a static attributes (SSN, title, doc, mobile).
If you have a static form that are never changing, the query can be working.
But if your form fields are changing, it is possible to
generate a dynamic sql using a stored procedure.

delimiter $$
CREATE PROCEDURE pivot(in tid int)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE field_name varchar(16);
  DECLARE field_id int;

  DECLARE names CURSOR FOR
    select  FF.id, FF.name
    from os_TICKET tkt
     inner join os_form_entry_values val on tkt.ticket_id = val.entry_id
     inner join os_form_field FF on val.field_id = FF.id
     where tkt.ticket_id = tid;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  set @sql_selector := 'select tkt.ticket_id, tkt.number ';
  set @sql_joins    := ' from os_TICKET tkt ';
  set @sql_where    := concat(' where ticket_id = ', tid);

  OPEN names;
  read_loop: LOOP
    FETCH names INTO field_id, field_name;
    select field_id, field_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    if field_id is not null and field_name is not null then
      set @sql_selector := concat(@sql_selector, ',', field_name);
      SET @sql_joins    := concat(@sql_joins,
       ' left join (select val.entry_id, val.value as ',field_name,
                 ' from os_form_entry_values val
                  where val.field_id in (', field_id,'))  as ', field_name,
                 ' on tkt.ticket_id = ', field_name ,'.entry_id');
    end if;
  END LOOP;
  close names;

  set @sql := concat(@sql_selector, @sql_joins, @sql_where);
  PREPARE dynamic_statement FROM @sql;
  EXECUTE dynamic_statement;
  DEALLOCATE PREPARE dynamic_statement;
END$$
delimiter ;

So if we call pivot the result must be the same of the previous query.

mysql> call pivot(5);
+-----------+--------+--------+-------+------------+---------------+
| ticket_id | number | SSN    | title | dob        | mobile        |
+-----------+--------+--------+-------+------------+---------------+
|         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 |
+-----------+--------+--------+-------+------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

We can insert a new field:

insert into os_form_field values(200, 'new_field');
insert into os_form_entry_values values(5, 200, 'new_value');

mysql> call pivot(5);
+-----------+--------+--------+-------+------------+---------------+-----------+
| ticket_id | number | SSN    | title | dob        | mobile        | new_field |
+-----------+--------+--------+-------+------------+---------------+-----------+
|         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 | new_value |
+-----------+--------+--------+-------+------------+---------------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I advise you to refactor you database, or to do pivoting in your application.
The previous queries and stored procedure could be very very inefficient.

Hope it help.


How can I force a minimum isolation level in a procedure?

There are a couple of options for determining the transaction isolation level in SQL Server depending on whether you want to include read committed snapshot as separate from read committed.  Once you know what isolation level your session is using, you can implement whatever logic you want.  Something like

IF EXISTS( SELECT 1
             FROM sys.dm_exec_sessions
            WHERE session_id = @@SPID
              AND transaction_isolation_level < 3 )
BEGIN
  set transaction isolation level repeatable read;
END;


Is it better to store images in a BLOB or just the URL?

As you can tell from the other answers, this is a big "It Depends." Some other factors might be if you are paying for hosting, do they charge more for file storage or database storage. File storage is typically cheaper, especially for cloud services.

If you are self hosted and using SQL Server, the upcoming version, codename Denali, will extend FILESTREAM to allow access via both TSQL and file system. It also will make sure these stay in sync. You can access, update, delete from either side and it will keep everything organized.

Do your research and find out what's important to you and pick the direction based on that choice.


DATE_ADD(timestamp,INTERVAL -1*-6 HOUR) >= '2016-02-24 15:55:01'

is the same as

timestamp >= '2016-02-24 15:55:01' - INTERVAL -1*-6 HOUR

(You might consider changing that triple-negative into a single-negative.)

A side note about float(16,8) -- Don't use (m,n) notation for FLOAT or DOUBLE; it leads to an extra rounding error and limits the range.  Alternatively you could use DECIMAL(16,8).


Understanding sys.dm_exec_query_stats execution_count vs creation_time

sys.dm_exec_query_stats returns aggregated stats for plans, but with a row per query.

So if a procedure has 2 queries, then sys.dm_exec_query_stats would return 2 rows, but the plan-related columns (such as compile time, execution count, etc) are plan level stats, and cannot be further aggregated.


Reverse Byte-Order of a postgres bytea field

Here is one method of doing it, however I would never do this. There is nothing wrong with storing bytes in a database's bytea column. But, I wouldn't bit wrangle in the database, and if I did I would use,

a C language function, or
some fancy procedural language that didn't require me exploding the inputs into a set of bytes.

This is sql-esque and should work -- here is what we're doing,

Generate a set consisting of a series of offsets 0 - (bytelength-1).
Map those offsets to bytes represented as strings of hex.
String aggregate them in reverse order.

Here is an example,

CREATE TABLE foo AS SELECT '\x813e1486dee46e1a'::bytea AS bar;

SELECT bar, string_agg(to_hex(byte), '') AS hash
FROM foo
CROSS JOIN LATERAL (
  SELECT get_byte(bar,"offset") AS byte
  FROM generate_series(0,octet_length(bar)-1) AS x("offset")
  ORDER BY "offset" DESC
) AS x
GROUP BY bar;

Two notes,

We could probably not use offset because it's reserved but you get the point.
This assumes that your hash (bar in the above) is UNIQUE.


A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

I'm not sure why you need to join all three tables every time. For your specific example, what about the following query:

WITH rel AS (
  SELECT prm.power_relation_id
  FROM power_relation_members prm
  JOIN power_lines pl ON prm.member_id = pl.id
  WHERE pl.geom = :BIND_VAR_HERE -- in this case, 'abc'
  GROUP BY prm.power_relation_id
) SELECT
  pl.id, pl.geom
FROM power_relation_members prm
JOIN rel r ON prm.power_relation_id = r.power_relation_id
JOIN power_lines pl on prm.member_id = pl.id;

You reference member_type and power_relation_members.id in your SQL, but nowhere are they mentioned in your post apart from the DDL.. Is this important logically, or just a filter?


Open .mdf and .ldf files in Microsoft SQL Server without attach

You can't add a database that way, you will need to attach the database using the files you have as follows:

Copy the files to the correct location.
Attached the database with EXEC sp_attach_db '<name>', '<name.mdf>', '<name.ldf>' or using the relevant GUI command in SSMS (if the files are not in the SQL instance's default directories you will need to provide a full path rather than just the filename).

In the question you say "without attach" which might mean you don't want to use this method (or that you can't use the attach command in SSMS for some reason, or <other reason> - could you explain that part?


Exporting Access DB to SQL Server: unexpected EOF?

First, use tab delimited files unless you want to use -t to specify a different delimiter (such as a comma). By default it will accept the tab.

Next, unless you're exporting data with the -n flag, use -c to import the data.

Finally, based on your comments, it looks like there is a mis-match between the size of your table columns and the actual width of the data. That's why you're getting notified the data is being truncated. Match those two up and you'll be all set.

You can check out the MSDN page on bcp for more information on flags, etc.


Can a clustered index with low cardinality cause deadlocks?

One of the two locks is a page lock in the table (One process has
  update lock the other one wants Intent Exclusive lock)

This mean that the table is big enough so that the engine has decided to use page granularity locks in order to discover the rows to be updated. Then as it 'drills' in' to actually apply the update to the rows, the deadlock occurs because the two updates both located the row-to-update on the same page. You could force rowlock, or you could disable page locks on the table, but the real problem is that it looks like the WHERE clause of the UPDATE is non-sARG-able.


When building an index for a = constant AND b >= ..., put any and all '=' columns first; end with one 'range': INDEX(a, b).  In your case INDEX(account_id, event_date).

You do need a PRIMARY KEY on your table, but it is unclear whether this is suitable.  The PK must be unique.

For more discussion, see my cookbook. The info is the same for MySQL as for MariaDB.  If you prefer, see MySQL: Building the best INDEX for a given SELECT


Multi-Server Extended Events investigations

You can set up Data Collection and create custom collection items for each event that you want to capture. There's this small project called Extended T-SQL Collector that can take care of collecting the data from Extended Events sessions and upload the events to a centralized MDW.

Here is an example of monitoring blocking and deadlocking with Extended T-SQL Collector


How to migrate Oracle database to MySQL server?

I recommend to do this migration in two steps.

Use MySQL Workbench to migrate most of the db objects and data to MySQL (table data will be copied by this process too). See Using the MySQL Workbench Migration Wizard for details. This will leave you with manual work for specific objects like stored procedures. No tool can do an automatic migration as stored routines differ vastly between Oracle and MySQL.
Use MySQL Workbench to reverse engineer the migrated db into a model.
Use this model to adjust objects to your new schema structure.
Synchronize the model to your server to apply the changes. Depending on what changes you made this might be non-destructive, but of course, having a backup is always a good idea.

You can repeat steps 3 and 4 as many times as you wish to incrementally adjust your schema. Synchronization will also take over any changes made in schema by others (two way sync).

Migration from Oracle to MySQL is not supported by MySQL Workbench. All my suggestions above are just that, ideas one can try out to get it at least partially working. Nothing of that is being tested, however, because, as I said, Oracle to MySQL migration is not supported.


Which system catalog view has the create_date of a database which name was modified? Or an alternative reliable method if such view doesn't exist.

You're correct in your assessment of create_date in sys.databases, which is confirmed by the documentation:

  Date the database was created or renamed.

So the create_date is the ideal value to use as the end time of the refresh process, being that the rename is the final step.

That being said, given the restrictions in place here I don't think you'll find any more accurate of a start time than the timestamp on the first table.

So the unsatisfying answer is you're as close as you're going to get, I think.


plpgsql function to replace ' ' with null after insert using a trigger

Just modify your SET clause and use CASE ... WHEN ... END:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = case when deliv1 ='' then NULL else deliv1 end,
SET deliv1 = case when deliv2 ='' then NULL else deliv2 end,
SET deliv1 = case when fmt ='' then NULL else fmt end,
SET deliv1 = case when spec_instr ='' then NULL else spec_instr end
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
END $_$ LANGUAGE 'plpgsql';


Database deployment script for SQL Server 2008 has different behaviors

Why are you using ##global temp tables instead of #local? It is possible that when you are changing the procedure there is already a global temp table in existence with the same name - they're global so it could have been created by anyone. Use a local temp table and that problem will go away.

Also, please don't use the sp_ prefix. Stored procedures shouldn't really need to have a prefix - what is the point of adding sp_ to the beginning of every stored procedure? They still sort the same (e.g. in Object Explorer), it just makes it that much harder to find the one you're after. If you absolutely must use a prefix for some reason, pick something other than sp_.


How do I migrate data from a SQL Server DB to MongoDB?

Joins you either pre-compute and store the denormalised data in the document. Or the application implements a join algorithm for runtime.

Indexes can be defined for whichever values inside the documents you'd like to have them. The documentation adequately  describes the options. As currently, they help reads but have a write overhead.

Keys: there are no keys. Each row has a unique identifier which you can assign or let the system generate a value. This would correspond to a surrogate primary key. Foreign keys, uniqueness, check constraints, assertions etc. are the appapplication's responsibility.


I Want to Become a Database Administrator - How Can I Start?

I have a feeling that this may be off topic and/or opinion based and may get closed, but I'll answer anyway for now. This is from a Microsoft SQL Server perspective, but it really applies well across the board in my experience.  I've been a DBA or a DBA consultant for about 15 years now and I've hired and been on the hiring team for junior, mid-level and senior DBAs. Others may have differing experiences in other answers..
On Certifications
I don't look at them. Especially when I am hiring someone who is relatively new. If you want to use a certification process to properly and effectively study to learn and to find where your gaps in knowledge are - then definitely. If you think the certification will make you hirable in and of itself - it just doesn't typically hold true. But it is a catch-22, some recruiters will ask for those in job requirements. But then so many people just pass low-level certs with brain dumps and a complete lack of testing. When I see a resume with little experience and a lot of certifications, I get nervous.
I guess the takeaway on this topic is: Certifications can help, but they are seldom most important or even top of criteria
On Becoming a DBA
My first question back to someone who wants to become one is why? In fact if you interviewed and told me that you really wanted to become one I'd want to hear why. Most of the best DBAs I know fell into it from development, system administration or other career paths. This does not mean that you won't be successful if you want to be one. I just like to know why.
If it is because you have the right mindset, you like troubleshooting, you like to keep things in order, you like to make sure systems are alive, data is protected and you are drawn to the career field? Then that is great.
What Skills Are Important?
To a new DBA? I want to see you have a proficiency in the basics of being a DBA. You need to know what the most important job is (and back it up with a why and how you will do it). My personal opinion is Recovery - because without the ability to recover you don't have a DBA and how I'd do it - I'd focus on the restore end and work hard at making sure that everything we did had an eye towards a possible restore. The implementation, the testing, the best practices in place, etc.. Now is that the only right answer? I don't know. If you said security and you explained it right, I'd be interested in hearing more.
Basically, though, I want to see that you have the right character traits (below) and the right basic knowledge that you should have at your skill level and experience. Starting out? I want to see that you can backup and restore.. That you can do some basic investigation into current activity. I want to know that you understand some of the basic building blocks of performance (what resources are important and why, how DB Design and development affect things) and have a lot of the character traits I describe below in place or heading there.
Character Traits
I wrote a blog post about this (Six reasons I won't hire you) awhile back. I won't regurgitate everything I said there here- but basically a good DBA is someone who has:

Great Troubleshooting skills - if you are a scatter brain and try 65 things to fix one problem and don't even remember 2 of the things you tried - I am going to pass on you if I can root that out. You have to be a calm troubleshooter with a methodology and an ability to discover and work out problems. This isn't just DB problems, but it should be verified in life and any problems one may encounter.
willingness/desire to learn and grow - I want you to be always striving to learn the next thing. Spend some of your after work time reading books, going to user groups and community events. It is a tight market out there - I want the people who are continuing to improve themselves.
Common Sense - I am starting to think you can't teach this.. Have some before you interview.
A little touch of paranoia - you are the DBA or want to be. You are about to manage a lot of important sensitive data. I want to see you be fair and easy to get along with, not arrogant, but I want someone with a bit of paranoia. I want someone who doesn't just trust by default or without verifying. You are asking to have the keys to a database environment that is important.

So How Do you Start?
Entry level jobs. Perhaps getting a job as a developer or general IT admin with some DB skills on the job. Going to community events and user groups and learning and applying yourself. Trying some volunteer or helping gigs with non profits or start ups. Basically get near databases. Work out a career path where you can do something with data.
I started out working as a support rep. Dealing with all sorts of questions, but SQL Server questions were one of the types. I studied hard, became a go to person for tough SQL problems and moved up from there. Then I worked as a Jr. DBA someplace and the rest is history.
Patience
I was just discussing this question with a friend on twitter and they mentioned "you don't get there overnight" - That is great advice. I see a lot of people starting out who want to be the senior DBA right now. So you need a little patience and humility thrown in there.
The first years of a DBA career are learning, figuring out which way you want to go and a lot of support time doing tasks that are basic. While you learn in these tasks and disciplines and prove yourself, more and more tasks will be added and more responsibility granted. If you stick with it, work hard, keep things online, build the right character and grow in your skills continually, you will get to senior DBA and do more fun stuff (well combined with more meetings and time spent with project managers) - but it is a marathon and not a sprint, in the DBA world.
These answers are opinions, and that is why this whole thread not survive, but if it helps you out - copy and paste it and give some of the advice a whirl. Best of luck!


You need to look into the different JOIN types.

With an INNER JOIN as you have here the result will only include rows for cases where a match is found in all the sources.

With a LEFT OUTER JOIN or RIGHT OUTER JOIN the result will include rows for all data in one side of the join even if there is no match at the other side (columns SELECTed from the other side will come out as NULL).

With a FULL OUTER JOIN you will get rows output for all three conditions (match both sides, data on left with no match on right, and data on right with no match on left). I think this is what you want for the join between contacts and users.

Note that the word "outer" is usually optional so you could write LEFT JOIN instead of LEFT OUTER JOIN

This page has a useful diagram of join types: http://stevestedman.com/2015/03/mysql-join-types-poster/

It suggests that mySQL doesn't support FULL OUTER joins (which I didn't know was the case) so you will need to UNION two selects, one with a RIGHT join and one with a LEFT to get the sort of result you are looking for.


Looking for performance improvements for this query

Here is your query refactored

SELECT * FROM
(SELECT * FROM `xgen_studentMLs`
WHERE student = 27126 AND topic = 1829 AND metatype = 'topic'
AND department IN ('Nursing','Biology')
ORDER BY timestamp DESC LIMIT 100) A
LEFT JOIN xgen_quizzes Q ON A.quizid = Q.idnum
WHERE IFNULL(Q.book,0) = 0;

The subquery is meant to retrieve only 100 rows before doing the LEFT JOIN. You will need an index to assist the subquery

ALTER TABLE xgen_studentMLs ADD INDEX new_ndx (student,metatype,topic,department);

Give it a Try !!!


SQL errors Microsoft.ACE.OLEDB.12.0 on dev box, but works on server

Have you tried to give permissions to the temporary folder that may be in use for the user? (The root of the drive)
It would be very common that your dev box would have permissions to C:\stuff and not on another box.

A simple way to test would be to use xp_cmdshell (if enabled, included the enable and disable stuff since its pretty dangerous):

exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
exec xp_cmdshell 'nul >> C:\test.xls'
go
exec sp_configure 'xp_cmdshell',0
go

exec sp_configure 'show advanced options',0

go
reconfigure

When I have an issue, I will get the response:
Access is denied.

Also, if the issue is that the server cannot access the files locally and you try this method, cmdshell will fail because cmd doesnt handle UNC paths well.
You could invoke powershell within xp_cmdshell (oh gawd), but I leave that to another SO answer https://stackoverflow.com/questions/16436405/how-to-run-powershell-in-cmd


Although inherent column order does not matter, but since you're looking for 'industry standards', you can search publicly available databases, like Microsoft's AdventureWorks, to see where Foreign Key columns are placed.
It's usually at the beginning.

This MSSQL query lists all FKs in a database :

SELECT
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT
                i1.TABLE_NAME,
                i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE
                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME


What's the difference between Replicate_Wild_Ignore_Table & Replicate_Ignore_Table?

The Replicate_Wild_Ignore_Table option allows you to specify table names using wildcard characters. The Replicate_Ignore_Table option does not (only complete table names may be used).

See the MySQL manual for more details.


SHOW VARIABLES LIKE 'tmpdir'; -- You could change that (and restart) to immediately workaround the problem.  But that is not a long-term solution.

Complex queries with inadequate indexes are likely to be the cause.  Set long_query_time=1 and turn on the slowlog; wait a day; run pt-query-digest or mysqldumpslow -s t to see the 'worst' queries.  Focus on them.

If you don't see how to improve a query, post a question with EXPLAIN SELECT..., SHOW CREATE TABLE and a tag it [mysql] [query-optimization].


Assign result of regexp_split_to_array() to Pl/pgSQL array variable

array is a reserved word. You cannot use it as variable name in plpgsql.
This works:

DO
$do$
DECLARE
   _arr text[];
BEGIN
SELECT regexp_split_to_array('thiiis splits into array with spaces', '\s+')
INTO _arr;

RAISE NOTICE '%', _arr[1];
END
$do$;

SQL Fiddle

Also simplified  E'\\s+' -> '\s+'.
standard_conforming_strings = ON is the default since Postgres 9.1 and should be the universal setting by now.


How to fold an event stream into a table?

assuming the are no duplicate events you can do this.

create view "users" as
  select payload->>'name'
  from es.events
  group by 1
  having
      sum ( case type
            when 'user-registered' then 1
            when 'user-banned' then -1
            end ) > 0
union
  select payload->>'activation_token'
  from es.events
  where type='user-changed-password';


I might be wrong, but I don't think that the MySQL optimizer takes foreign keys into consideration. An index, on the other hand, may be very useful as a support for a foreign key.

However, the main purpose of a foreign key is completely different than what you are asking for. The purpose is to keep the database consistent and maintain referential integrity. When deciding for or against foreign keys, I think this what you should be concidering.


Daily SQL Server Alert: BackupVirtualDeviceFile::SendFileInfoBegin: failure on backup device

BackupVirtualDeviceFile usually indicates a VDI or VSS backup from a third-party tool. These are usually snapshot-based VM backups that use VSS writers to take application-consistent snapshots of machines, tools like Veeam or CommVault for example.

They can also be third-party backup tools that backup to a Virtual Device and stream the data to an agent or application for further processing, such as compression or encryption.

Check your Windows System and Application event logs for errors, warnings or info messages relating to VSS providers, requestors or writers. In the case of SQL Server, it is installed by default with the SQL VSS Writer which handles VSS requests from these sort of tools. If you see warnings in your SQL Server Error Log about I/O freezes at the same time, this is another indicator of VSS-based backups occurring.

For VDI-based backups not using VSS, it can be a little harder to identify. Try capturing the output of sp_whoisactive frequently during the window this occurs and identify the source of the BACKUP command.

Once you identify the tool in question, check the relevant logs, or with the system administrators responsible to get further error details as this is potentially an issue outside of SQL Server causing this problem.


PLSQL: Package stored procedure cannot call exe imediate statement

You're doing something wrong. Test case follows...

Create schemaa and schemab:

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 21 14:28:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> create user schemaa identified by "schemaa";
SP2-0640: Not connected
SQL> conn / as sysdba
Connected.
SQL> create user schemaa identified by "schemaa";

User created.

SQL> grant connect, resource to schemaa;

Grant succeeded.

SQL> create user schemab identified by schemab;

User created.

SQL> grant connect, resource to schemab;

Grant succeeded.

Login as schemaa, create two tables & grant select on the first to schemab, but don't grant anything on the 2nd table:

SQL> conn schemaa/schemaa;
Connected.

SQL> create table satable ( aaa integer ) ;

Table created.

SQL> insert into satable values ( 1 );

1 row created.

SQL> grant select on satable to schemab;

Grant succeeded.

SQL> create table satablenograntsforb( aaa integer ) ;

Table created.

Login as schemab, create the stored procedure & then test it with the table we have permission to select from:

SQL> conn schemab/schemab;
Connected.

SQL> CREATE OR REPLACE PROCEDURE schemabproc(TableName IN VARCHAR2)
  2  IS
  3  v_query varchar2(2000);
  4  BEGIN
  5
  6  v_query := 'select aaa from schemaa. ' || TableName ;
  7  execute immediate v_query ;
  8
  9  END;
 10  /

Procedure created.

SQL> exec schemabproc('satable');

PL/SQL procedure successfully completed.

SQL>

As you can see, it executed ok.

Let's try on the table we don't have permission on:

SQL> exec schemabproc('satablenograntsforb');
BEGIN schemabproc('satablenograntsforb'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCHEMAB.SCHEMABPROC", line 7
ORA-06512: at line 1

Let's grant and re-test:

SQL> conn schemaa/schemaa
Connected.

SQL> grant select on satablenograntsforb to schemab;

Grant succeeded.

Now we should be able to select from the 2nd table without error:

SQL> conn schemab/schemab

SQL> exec schemabproc('satablenograntsforb');

PL/SQL procedure successfully completed.

SQL>

Conclusion: There's something else happening. Note that the procedure above runs with AUTHID DEFINER, which is the default.


Will a specific denormalization increase performance in a specific scenario?

The question is: will it boost the performance of select to split the
  table into five tables each corresponding to one of the five possible
  values of z?

Yes, you could use partitioning here. Partition elimination will kick in. The partitioning will act as a leading index column.

Which brings me to the following point: Just add z as the leading column to all indexes. As you always filter on z (using an equality predicate) this will only help. (Except, if the index was unique anyway like an index on an ID column).

I'm generalizing a bit here like always with databases, but make sure to add filtering on z to your indexes.

Other techniques apply as well, of course, like making indexes covering.

I don't see the necessity for creating 5 tables, or using partitioning here. I can see how that might improve performance by a few percent, though, because z no longer needs to be stored.

If your app is generating enough load that a few percent of load matter, then your server is overloaded anyway by my definition. Still, it is worth a try if you don't care about increased development and maintenance costs.


Pivot query issue while converting row to column

It is obvious, by how you have arranged the rows in your example, that there are two entities in the second table, each with its own set of attribute values.

However, in SQL it is a convention that rows in a table have no inherent order. Therefore, if you want the server to distinguish between the two sets, you need either

a column that would specify the row order (so that by using that order you could somehow determine where a new set of values starts):

category | fieldname | fieldvalue       | roworder
--------------------------------------------------
1        |     1     | SSC              | 1
1        |     2     | H.B.Kapadiya     | 2
1        |     3     | Primary school   | 3
1        |     4     | Mar-2000         | 4
1        |     5     | 89%              | 5
1        |     1     | HSC              | 6
1        |     2     | Vishwaniketan    | 7
1        |     3     | Higher Secondary | 8
1        |     4     | Mar-2002         | 9
1        |     5     | 70%              | 10

or
a column that would serve as an entity identifier, so that it would be clear which set of values belongs to which entity (and, thus, on which row in the output it should end up):

category | fieldname | fieldvalue       | entityid
--------------------------------------------------
1        |     1     | SSC              | 1
1        |     2     | H.B.Kapadiya     | 1
1        |     3     | Primary school   | 1
1        |     4     | Mar-2000         | 1
1        |     5     | 89%              | 1
1        |     1     | HSC              | 2
1        |     2     | Vishwaniketan    | 2
1        |     3     | Higher Secondary | 2
1        |     4     | Mar-2002         | 2
1        |     5     | 70%              | 2

The first option is certainly much inferior, because in order to get the desired result you would need to obtain some kind of entity identifier one way or another, and with the first option you would have to derive it somehow based on the order of rows. Note that you would probably be restricted to always storing the values in a specific order, and in particular it would be mandatory that values belonging to the same entity be stored in consecutive rows only.

With the second option you could store the values arbitrarily: the dedicated entity ID column would unambiguously determine which set of values the row should belong. Your query would then be very similar to what you already have, you would only need to add entityid to the GROUP BY:

SELECT
  b.Category,
  MAX(IF(f.fieldname = 'educationlevel',   b.fieldvalue, NULL)) AS educationlevel,
  MAX(IF(f.fieldname = 'institution_name', b.fieldvalue, NULL)) AS institution_name,
  MAX(IF(f.fieldname = 'course',           b.fieldvalue, NULL)) AS course,
  MAX(IF(f.fieldname = 'year_of_passing',  b.fieldvalue, NULL)) AS year_of_passing,
  MAX(IF(f.fieldname = 'percentage',       b.fieldvalue, NULL)) AS percentage
FROM
  tbl_bulkuploads AS b
  INNER JOIN tbl_bulkuploadfields AS f on f.fid = b.fieldname
WHERE
  b.Category = 1
GROUP BY
  b.Category,
  b.entityid
;


How do I determine how much data is being written per day through insert, update and delete operations?

MONITORING

There are so many writes to keep track of

Data

Innodb_data_writes : The total number of data writes.
Innodb_data_written : The amount of data written so far, in bytes

SQL

Innodb_rows_deleted : The number of rows deleted from InnoDB tables
Innodb_rows_inserted : The number of rows inserted into InnoDB tables
Innodb_rows_updated : The number of rows updated in InnoDB tables

Double Write Buffer

Innodb_dblwr_writes : The number of doublewrite operations that have been performed
Innodb_dblwr_pages_written : The number of pages written to the doublewrite buffer

Operating System

Innodb_os_log_written : The number of bytes written to the InnoDB redo log files
Innodb_data_fsyncs : The number of fsync() operations so far. The frequency of fsync() calls is influenced by the setting of the innodb_flush_method configuration option.

MyISAM-related writes

Data writes come in the form of

External sources

DB Connections From Your Apps
Data Restorations

Reloading of mysqldumps
LOAD DATA INFILE

MySQL Replication via incoming I/O Threads (if DB Server is a Master)

Internal Sources

Read and Write I/O Threads for Writing to Transaction Logs
Insert Buffer from the InnoDB Buffer Pool
Double Write Buffer
MySQL Replication

IO Thread (if the DB Server is a Slave)
SQL Thread

Log Buffer Write
Individual Tablespace Files (.ibd)
FSyncs (pushing changes to disk)

InnoDB Architecture

SSD LIFESPAN

If you more concerned with the SSD's longevity, try moving some of the InnoDB parts away from the SSD over to a fast HDD. Which parts go where ?

HDD (RAID 10)

InnoDB System Tablespace (ibdata1)
InnoDB Transaction Logs (ib_logfile0,ib_logfile1)
Binary Logs
Relay Logs (if your server is a MySQL Replication Slave)

SSD

.frm files
.ibd files

See this good blog entry from a MySQL FaceBook Engineer on this suggestion

Here are some of my earlier posts on moving splitting logging mechanisms between SSD and HDD

Jun 25, 2013 : Postgres Write Performance on Intel S3700 SSD
Jun 18, 2013 : How big can innodb_doublewrite_file grow?

GIVE IT A TRY !!!


Advice for ETL from Oracle to SQL Server?

SSIS does a good work of exporting data from Oracle to SQL Server. I suggest you create staging tables in SQL Server and use SSIS to extract the data for the 10 Oracle tables. Then either use stored procedures in SQL Server and execute them using SSIS or use Merge syntax and try to do both insert as well as update in Control Flow. Updates tend to be slow in Data Flow task of SSIS using Oledb command for very large number of rows.

Go through this link Using SSIS to get data out of Oracle: A big surprise! which explains the performance of SSIS while loading fairly large amount of data in a short time.

To get started for ETL process from Oracle to SQL Server try to read this documentation.


LOCK=NONE when adding NOT NULL constraint fails with "cannot silently convert NULL values" error despite no NULL values being present

This seems to be a bug that was introduced in MySQL 5.6.10 and has since been fixed (although there is no bug report on http://bugs.mysql.com that I can find). The behaviour described in the question does not occur in the latest version of MySQL.

Observed behaviour in MySQL 5.7.5-m15:

mysql> CREATE TABLE `Test` (
    ->   `id` int(10) auto_increment PRIMARY KEY,
    ->   `stuff` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.38 sec)

mysql> ALTER TABLE Test MODIFY stuff varchar(100) NOT NULL, LOCK=none;
Query OK, 0 rows affected (0.97 sec)
Records: 0  Duplicates: 0  Warnings: 0


You can create an enumerated type for this:

CREATE TYPE direction AS ENUM ('in', 'out');

CREATE TABLE a_table (
  ID        serial,
  direction direction NOT NULL
);

INSERT INTO a_table (direction) VALUES ('in');        -- succeeds
INSERT INTO a_table (direction) VALUES ('sideways');  -- fails


Postgresql permissions on schemas to groups

You forgot to set default privileges for schemas:

ALTER DEFAULT PRIVILEGES GRANT CREATE, USAGE ON SCHEMAS TO grp_admin;


Ideal MAXDOP & CPUs - how to trace for parallel queries?

The post on Five SQL Server Settings to Change:

http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

Suggests the following:

Maximum Degree of Parallelism: Limit parallelism to the number of physical cores in a single CPU socket,
Cost Threshold for Parallelism: Start with 50 then tune up or down depending on your need.

Also: A parallel plan running in serial means that although a parallel plan was created, the needed resources for running parallel are not available.  So the plan will serially runs sections of the parallel plan until finished.


Having state in User table doesn't look good to me. It introduces dependency between User.group and User.state - group should be null if state equals 0, and non-null if state is 1 or 2. Also, column name is very confusing (I'd expect User.state to reflect user status (say active/inactive), or part of address (e.g. California/New York) , not state of relationship between user and group).

I'd put value in User.group only when user belongs to group, and handle requests in separate table user_group_request (user_group_request_id (PK),user_id, group_id, request_date, request_text, request_state,response_date). When user wants to join group, you add a new record into user_group_request. Setting User.Group should be part of approval routine. Also, depends on your model, you may want to add unique constraint on user_group_request(user_id, group_id), so one user cannot send multiple requests.


In my personal opinion debugging table locks in MySQL can be a pain in the *ss sometimes.
Some queries that may give you some insights are:

Get a list of the locked tables

SHOW OPEN TABLES WHERE In_Use > 0;

Get a list of all processes using MySQL resources with corresponding queries

SHOW FULL PROCESSLIST;

Get specific information about the InnoDB storage engine including deadlocks

SHOW ENGINE INNODB STATUS;


Deadlock happens when one query acquires a lock on a object (rows, data pages, extent, tables etc) and other resource tries to access it. Smallest unit in SQL Server is data pages and SQL holds a lock on page while working on it. So, yes it is possible that two select statement can create deadlock.

Solution:

WITH(NOLOCK) - If your data is fine to be read dirty
Use Read committed snapshot or Snapshot isolation
Try to keep transaction small and in batches
Use bound connection - it will allow two connection to use same transaction & lock

This answer is based on your question, as your screenshot doesn't contain any information.


Does the apply operator use row by row processing?

The APPLY operator is a tabular operator in the T-SQL language. As the language itself, it is declarative and does not indicate in any way what is the physical implementation that the relational engine should be using to retrieve the data. This means that the correlation strategy between the input set and the applied function is not limited to nested loops. This highly depends on the type of tabular function that you are applying to the input set, as some functions support predicate pushdown and/or reordering and others don't.

The APPLY operator per se is not inherently good or bad, so it is not among the things that you should try to avoid in your code.


Scheduling backup jobs for an Availability Group

In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule
  backup jobs for a given availability group. The ultimate goal is to
  have the regular backups run on the synchronized secondary and, most
  importantly, not bound to the availability of a particular secondary
  node.

This can be accomplished without issue. Just keep in mind that your systems should be spec'd to be able to take the full load of the synchronization mechanics for AlwaysOn Availability Groups, plus the redo needed, plus the backups. The last thing that you want is to overwhelm your IO system and cause outages or a large redo queue. This is assuming it's not actively being used as a readable secondary - if it is, you'll want to factor that in and watch for blocked redo threads.

  1.I want to run the scheduled action on a secondary just once, but I have multiple secondaries
  2.I want to ensure that it is ultimately run, regardless if there are any secondaries left at all - if no secondaries are left, it should run on the primary

There is an Availability Group setting and a system function that can be used in order to accomplish this within your expected guidelines.

The first is the setting, per AG, called AUTOMATED_BACKUP_PREFERENCE which has four different options. The one you're describing is called SECONDARY which will prefer any secondary node over the primary node. The secondary node chosen is by the BACKUP_PRIORITY which is set per replica ranging from 0-100, where the higher the number the more weight it has. If there is a tie in weights for secondary replicas, the replica that is sorted first given the system collation will be chosen. If all secondary replicas should fail, the primary replica will be chosen.

The second part of the equation is the system function used to check if the replica that is running the job is the preferred replica based on the values in the previous paragraph. This system function is called sys.fn_hadr_backup_is_preferred_replica(). Given a database name (any database in the AG) a value of 0 will be returned if it is not the preferred replica and a value of 1 if it is preferred.

When creating the agent job to accomplish this, you'll want to wrap the backup logic in an IF conditional to check for the preferred replica. That's it. Put the identical agent job on all replicas.

Please be sure to test that this truly is what you want and expect.


How to convert strings extracted from JSON to a numeric type for aggregates?

You need to extract them as text using jsonb_array_elements_text(), then you can cast it to an integer:
select min(x.val::int), max(x.val::int), avg(x.val::int)
from the_table
  cross join jsonb_array_elements_text(data #> '{temperature,boiler_temp,values}') as x(val)


We had a solution but don't know how it happened. Here what we did:

Took snapshot of both servers in case of data loss over AWS
We detached Volume where data is stored. (for instance 172.x.y.k)
We formatted instance and loaded Cassandra with new version. Then we
attached Volume where data is stored.
Finally we started nodetool repair --full in terminal and it took 4
days.

Now our nodes are equal.


Why doesn't "Total Space Reserved" equal the sum of Data File space reserved and Transaction Log space reserved in the Disk Usage Report?

Those files can get out of sync.  Run DBCC UPDATEUSAGE
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql

Do not run DBCC UPDATEUSAGE routinely. Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.
Consider running DBCC UPDATEUSAGE routinely (for example, weekly) only if the database undergoes frequent Data Definition Language (DDL) modifications, such as CREATE, ALTER, or DROP statements.


Active/active SQL Server Clustering in Installation Center (setup.exe)

You want to choose "Add node to a SQL Server failover cluster".  When creating a Failover Cluster Instance (FCI), you will always do one initial cluster installation for the instance, then add nodes to that installation.

Keep in mind that Active/Active and Active/Passive as descriptors is very misleading.  By it's very nature, each SQL Instance you install can only be active on a single node at any one time. To leverage multiple physical nodes at the same time, you will need to install multiple named FCIs to your cluster.

For example, let's assume a two node cluster.  If we install a single named FCI to this, our layout would look like this:

NODE1:  CLSTSQL01\SQLServer01 (Active)
NODE2:

This would be the result of you performing one "New SQL Server Failover Cluster installation" with the additional node additions for each node in your cluster.  Now let's add a second instance to it so we can utilize each node.  For this, we would install a second FCI to the cluster and end up with something like this:

NODE1:  CLSTSQL01\SQLServer01 (Active)
NODE2:  CLSTSQL02\SQLServer02 (Active)

Now, keep in mind that in a failover situation, on either node, the SQL instances could end up on the same node.  For example, if NODE1 failed:

NODE1: <<DEAD>>
NODE2: CLSTSQL01\SQLServer01, CLSTSQL02\SQLServer02 (Active)

This is the basics of how you would arrange your instances.  You will need to monitor your cluster if you want to make sure your instances are properly balanced across your nodes.


How do I optimise XPath Querying over a large dataset?

Create an XML index on the column, you will have to create a primary index and then a secondary index on the path or value you are querying.
CREATE PRIMARY XML INDEX PXML_NAME ON SCHEMA.TABLE (XMLCOLUMN);
then create a secondary index for the { VALUE | PATH | PROPERTY } you are querying with Xpath

https://msdn.microsoft.com/en-us/library/ms191497.aspx and
https://msdn.microsoft.com/en-us/library/bb934097.aspx


Sub Query with group by returns more than 1 row Issue

Instead of using the subquery to get "count of correct questions", you can get the same count using conditional aggregation utilizing CASE .. WHEN statement with COUNT(..) in the main query itself:

SELECT category_id_fk AS category,
       Count(*) AS total_questions,
       Count(CASE WHEN correct = 1 THEN 1 ELSE NULL END) AS total_correct
FROM   attempted_questions
GROUP  BY category

COUNT(NULL) returns 0; so if an attempted question is not correct (ELSE condition against WHERE correct = 1), we return NULL to avoid counting it as correct.

Another possibility can be using SUM(..) with CASE .. WHEN. We can also utilize MySQL's implicit typecasting of boolean to int:

SELECT category_id_fk AS category,
       Count(*) AS total_questions,
       SUM(correct = 1) AS total_correct
FROM   attempted_questions
GROUP  BY category

correct = 1 returns either True or False for correct and incorrect questions, respectively. During SUM(..) operation on these boolean values, MySQL implicitly typecasts true to 1, and false to 0; thus giving us the desired count.

You can also write it in SQL standard way (by avoiding implicit typecasting), as follows:

SELECT category_id_fk AS category,
       Count(*) AS total_questions,
       SUM(CASE WHEN correct = 1 THEN 1 ELSE 0 END) AS total_correct
FROM   attempted_questions
GROUP  BY category

The only difference with COUNT(..) approach here is that it we are returning 0 instead of NULL in the ELSE part for SUM(..).


SSIS Ensure a sequence container is not marked as failed if a specific task fails

Have you looked at the Propagate variable for the OnError Event Handler of the task within the sequence?

Check this link for info. Basically, you disable propagation of the OnError event so the ancestor tasks (or sequence in this case) don't receive the error.


multiple select query for the same tables with union

I'm not entirely clear on what you are trying to do, how should the top 4 in each category be chosen for example, randomly?

WITH PRIMARY_CHOICE AS (
  SELECT ApplicantName, Category, Plus2Percentage
  FROM (
    SELECT ApplicantName, Category, Plus2Percentage
         , row_number() over (partition by Category
                              order by Plus2Percentage desc) as rn
    FROM RKMVApplicationFormData
  ) as T
  WHERE rn <= CASE CATEGORY WHEN 'SC' THEN 25
                            WHEN 'ST' THEN 30
                            WHEN 'OBC' THEn 15
                            ELSE 0
              END
)
SELECT ApplicantName, Category, Plus2Percentage
FROM PRIMARY_CHOICE
UNION ALL
SELECT ApplicantName, Category, Plus2Percentage
FROM (
    SELECT ApplicantName, Category, Plus2Percentage
         , row_number() over (order by Plus2Percentage desc) as rn
    FROM RKMVApplicationFormData x
    WHERE NOT EXISTS (
        select 1 from primary_choice y
        where x.ApplicantName = y.ApplicantName
    )
) AS T2
WHERE rn <= 30;


Is there a data type in MySQL similar to varchar(n) that not allow numbers or special characters in it?


Searching all Stored Procedures for an update of a specific column

Do you have many procedures that getting the rough net and then eliminating any false positives manually would be extremely objectionable?

SELECT s.name, p.name, m.definition
  FROM sys.procedures AS p
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  INNER JOIN sys.schemas AS s
  ON p.[schema_id] = s.[schema_id]
WHERE LOWER(m.definition) LIKE '%update%transaction%terminalid%'
   OR LOWER(m.definition) LIKE '%update%terminalid%transaction%'
   OR LOWER(m.definition) LIKE '%with%transaction%update%terminalid%';

The last case to cover this form:

;WITH x AS (SELECT ... FROM dbo.Transaction)
UPDATE x SET TerminalID = ... FROM x INNER JOIN ...;

I don't know how much more reliable a "better" RegEx could be at identifying only those that truly update that column, and never identifying a false positive. In fact any real update statement that matched the above expressions (or any RegEx expression you would be likely to cook up initially) could be embedded in a comment or a string.

I'd suggest start simple. If the above yields way too many false positives then get more precise.


What are the things to looks for or capture when there are blockages in Sybase

when i posted this question, i was quite not aware of what to do next after you have captured information related to blockages like, SPID, Showplan and SQLText.

answering this question, just in case someone comes across same doubt.

Once you have found the details during blockages, next steps is to find out what caused it in first place.

sp_sysmon is tool you need to look into.
sp_sysmon provides a detailed report of the activity on your system, and
provides you with a number of ways to specify the type of information you
want to receive, the time interval during which data for the report will be
collected, and other options that determine how the report is generated.

The sp_sysmon report consists of a number of separate sections. You can
run sp_sysmon to generate the complete report or only one of the
individual sections. You can also specify a time interval over which the
report should run or execute the stored procedure yourself at the beginning
and end of the desired time period.


Here is the MySQL Documentation FAQ on MySQL Cluster that addresses your question

The cause is very likely to be that your setup does not provide sufficient RAM for all table data and all indexes, including the primary key required by the NDB storage engine and automatically created in the event that the table definition does not include the definition of a primary key.
It is also worth noting that all data nodes should have the same amount of RAM, since no data node in a cluster can use more memory than the least amount available to any individual data node. For example, if there are four computers hosting Cluster data nodes, and three of these have 3GB of RAM available to store Cluster data while the remaining data node has only 1GB RAM, then each data node can devote at most 1GB to MySQL Cluster data and indexes.
In some cases it is possible to get Table is full errors in MySQL client applications even when ndb_mgm -e "ALL REPORT MEMORYUSAGE" shows significant free DataMemory. You can force NDB to create extra partitions for MySQL Cluster tables and thus have more memory available for hash indexes by using the MAX_ROWS option for CREATE TABLE. In general, setting MAX_ROWS to twice the number of rows that you expect to store in the table should be sufficient.
For similar reasons, you can also sometimes encounter problems with data node restarts on nodes that are heavily loaded with data. In MySQL Cluster NDB 7.1 and later, the addition of the MinFreePct parameter helps with this issue by reserving a portion (5% by default) of DataMemory and IndexMemory for use in restarts. This reserved memory is not available for storing NDB tables or data.

Based on the 3rd paragraph, you should run the create table with max_rows:
create table datadump (name varchar(30) not null) engine=ndb max_rows=2000000;


Is it bad to have index space larger than data space?

Think of index design like a sliding switch. You can move this red triangle switch knob anywhere along the line that you want:

I don't usually measure it in terms of size - I usually think of it in terms of index quantity, but size would be fine as well.

It sounds like your DBA thinks the switch is too far over to the right - that you've added too many indexes, and deletes/updates/inserts are performing too slowly.

Rather than arguing about where the switch is, try asking him about the performance problems you're having due to the high number of indexes. Maybe your users are complaining about delete/update/insert speed, or he's seeing lock waits, or he's having a tough time backing up the database due to its size.

My starting point is usually 5 and 5: around 5 indexes per table, with around 5 or less fields per index. There's nothing magical about that number - it just comes from the fact that I have 5 fingers on each hand, so it's easy to hold my hands up and explain the rule.

You may need to have many LESS indexes than 5 when your workload is heavily biased toward delete/update/insert operations, and you don't have enough hardware horsepower to keep up.

You may be able to have many MORE indexes when your workload is mostly read-only, or when you heavily invest in hardware (like cache the entire database in memory, and have all solid state storage underneath it.)


Is it possible for master, tempdb, model and msdb to have a database_id other than 1,2,3,4 respectively?

In theory

Below actions are only for testing purposes. Do not attempt any of this if you are not ok with your database instance becoming unusuable, unresponsive and/or your data being lost

Detaching the master database to try and reuse it's database_id is not possible.

Detaching model  will result in the instance crashing and you not able to attach it back. This results in you having to Rebuild all your system databases. This process removes all information such as logins, jobs, .... And your user databases are detached.

Detaching Tempdb to try and reuse it's database_id's by attaching it back under a different name resulted in the database_id 2 not being reused & tempdb not working.

name    database_id
master  1
model   3
msdb    4
test3_2__   5
test3_2_    6
tempdb  7

I was able to change the name back to tempdb, but the instance was pretty messed up, and I was unable to start it normally.

  Could not find database ID 2. Database may not be activated yet or
  may be in transition. Reissue the query once the database is
  available.

---> Rebuild all your system databases.

But, in theory there are 2 (or more) options to change the msdb system database id:

You can change the msdb database id by detaching msdb, creating a new user database and finally creating a new msdb database while following the documented steps to create a new msdb database.

You can change the msdb database id by detaching msdb, reattaching it under a different name and changing the name back to msdb.
UNSUPPORTED, please do not do this

First start sql server in 'master only recovery mode'

NET START MSSQL$InstanceName /f /T3608"

Detach msdb and create a new database

EXEC sp_detach_db msdb;
GO
CREATE DATABASE test3;
GO

Change the names of msdb files, Restart the instance normally

Initialize msdb:

SQLCMD -E -S<servername> -i"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.out"£

View the database id's:

SELECT name,database_id
FROM sys.Databases;

name    database_id
master  1
tempdb  2
model   3
test3   4
...
msdb    12

Unsupported method

The 2 normal attach procedures
sp_attach_db and create database ... for attach do not work when trying to attach msdb.

Error message:

  FILESTREAM database options cannot be set on system databases such as
  'msdb

What you can do, solely for testing purposes is create a user database, msdb2

CREATE DATABASE msdb2
    ON (FILENAME = N'X:\MSSQL\DATA\MSDBData.mdf'),
    (FILENAME = N'X:\MSSQL\DATA\MSDBLog.ldf')
    FOR ATTACH;

Rename the database to msdb

ALTER DATABASE msdb2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE msdb2 MODIFY NAME = msdb ;
GO
ALTER DATABASE msdb SET MULTI_USER

Restart the instance normally

NET STOP MSSQL$InstanceName
NET START MSSQL$InstanceName

and view the database_id's:

SELECT name, database_id
FROM sys.databases;

Result

name    database_id
master  1
tempdb  2
model   3
test    4
...
msdb    10

In practice

I have fortunately not seen this anywhere, but database_id 4 could in theory be reused by user databases. If this happened filtering out system databases in my queries would probably be the least of my problems.

That said, using WHERE DB_NAME(database_id) NOT IN ('master','tempdb','model','msdb','distribution','ssisdb') is the sure choice.


DB2 zOS increasing DSSIZE vs adding partition?

As long as there is no application code that uses the partitioning scheme in their logic, adding a partition is a solution to your space issue.

I would suggest that you use 5 partitions instead of 4. Your data should then spread out more evenly.

P1 00-20
P2 21-40
P3 41-60
P4 61-80
P5 81-100


SQL command to clean job queue in pgAdmin 4

The file

C:\Users...\AppData\Roaming\pgAdmin\pgadmin4.db

is an sqlite-database.

Open it (e.g. with https://sqlitebrowser.org/) and delete all rows in the process table. Then restart pgAdmin - the boxes should be gone.


Exporting certificate without private key

Then I export the certificate and import it into the machine where the SQL Server is running.

That defeats the purpose of using Always Encrypted if the server that is running SQL Server also has the certificate to decrypt all the data.

Please, please, please don't do this! Put it on the client machine or a "tools" or "intermediate loading" machine to get the data in and out. Then remove the key and keep it only on the client machines that are locked down.

I'd also keep, in the back of your mind, a solution for when a client machine is lost/stolen/breached (laptop), or a server is compromised. How do you find out, how long do you have to rotate CMKs, do you know how to rotate CMKs, etc.

  I am wondering is there any cases where I may need to export only the certificate without the privet key in the context of Always Encrypted feature (as the work of this certificate it to protect the column encryption keys only) ?

The private key is what keeps everything safe, so no private key then no decryption. I can't think of any times when this would be useful.


Why does SQL server run ths inline SVF query slower - both in CPU and
  elapsed time?

Scalar valued functions are executed in a different context than the main query and setting that up for each call takes time.

  By centralising some simple logic it appears I impede performance
  through code reuse.

Yes, for scalar valued functions that is true.

  If SQL server always impedes performance when using SVF why would I
  ever use them

Good question, not sure if I can provide a use case where a scalar valued function would be the obvious answer.

  is there a different solution?

Yes, you can use an inline table valued function instead.

create function dbo.usvf_CalculateShipping2
(
    @PricePerKG float = 1,
    @Type tinyint,
    @WeightInKG float = 1
)
returns table as return
select case
         when @Type = 1 then 0.1
         when @Type = 2 then 0.2
         when @Type = 3 then 0.35
         when @Type = 4 then 0.43
       end * @PricePerKG * @WeightInKG as Shipping

You also have to change the way you call the function.

select ProductID,
       (
       select Shipping
       from dbo.usvf_CalculateShipping2(Price, [Type], [Weight])
       )
from temp_Products
where [Weight] between 2 and 8


Select unique records from multiple records based on max date

For the first condition

count() Over (Partition by id, addr, Ind)
max(update_date) Over (Partition by id, addr, Ind)

For the secontd condition

row_number Over (Partition by id, addr, Ind and update_Date)


To fix it you need a State of error message - explanation why it failed. It could be found in error message or SQL Error log if failed logins added
Login failed for user - Error 18456 - Severity 14, State 38


Is this update, select combination query thread safe?

ASSESSMENT
Your query is kind of dangerous to the MySQL Query Optimizer.
I have an old post (Problem with MySQL subquery). The question posed involved this query
DELETE FROM test WHERE id =
(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1);

Although the MySQL Query Parser will work with this query and accept it's syntax, the MySQL Documentation implies that some data may disappear during certain optimization phases. In your case, you are accessing just one row to get an id and then update that row. Notwithstanding, the remote possibility of accessing data from a subquery that might not return an id because it can intermittently disappear does not strike me as transaction-safe, let alone thread-safe.
Your query looks similar to the query in that old post

Non-SELECT query
SELECT subquery
Subquery uses LIMIT

This issues goes a limit beyond just being thread-safe or not. Even if it is, you should not trust MySQL's interpretation of grabbing an available seat in the middle of a subquery. You need your query to be transaction-safe.
SUGGESTION #1
My advice to you would be to rewrite this as three queries in a transaction.
Perhaps something like this:
START TRANSACTION;
SELECT id from seats WHERE taken is null limit 1 FOR UPDATE;
SELECT id INTO @available_id from seats WHERE taken is null limit 1;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

or with two queries
START TRANSACTION;
SELECT id INTO @available_id from seats WHERE taken is null limit 1 FOR UPDATE;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

For further info, please read MySQL Documentation on SELECT ... FOR UPDATE.
SUGGESTION #2
I would also make sure that the seats table has an index on the taken column.
If not, add this index
ALTER TABLE seats ADD INDEX (taken);

Do this in Dev/Staging Environment and test your query before doing this in Production.
GIVE IT A TRY !!!


SQLite - Should I add some indexes even if select queries are fast?

Now, tables have max 5k rows and in future they may go maximum up to 100k rows

In cases like this, where time and resource permits, the best way to check this is to manufacture realistic looking data of that size and test your application against that to see how well it scales (or if it doesn't, where the bottlenecks lie). Make sure your data is realistic though: ~100,000 identical rows won't be a good test as the indexes will not be selective enough to be useful, likewise truly random numbers in the column where the data is expected to "bunch" would also not be the most useful test.


Understanding MAXDOP behaviour for queries going parallel

How can i test/check if those queries are actually distributing the
  work across all the cores evenly or is it still the case where all of
  them might be doing all the work on one NUMA node socket with 20 cores
  while remaining 60 are idle?

I would start by looking at overall CPU utilization on the server. If CPU is relatively high then you know that you don't have the problem that you described. In that situation, you might do one or more of the following to improve throughput:

Tune your queries to use less CPU
Run fewer queries
Get more or better hardware

If CPU is relatively low then you have a different type of CPU problem. In that situation, you might do one or more of the following to improve throughput:

Run more queries
Check wait stats to see if there's something stopping queries from running or using as much CPU as you'd like

As a rule of thumb, you should not expect that if you run 10 MAXDOP 8 queries then all 80 schedulers will end up with at least one parallel worker. The algorithm for allocating worker threads just isn't that smart. Bob Dorr provides some details here. In my experience with large servers I've found that issuing more queries than I would otherwise like was essential to get all of the schedulers to have a chance of having at least one parallel worker. Parallel worker threads are by default not bound to the same NUMA node. The situation that you're describing (only one busy NUMA node) can happen if your queries on other NUMA nodes finish first.

You may find some of the following links to be helpful if you want to look into the amount of work each CPU is performing:

Overview of Windows Performance Monitor
Resource Monitor
sys.dm_os_tasks


Is group replication available in Mysql community?

Based on the MySQL Developer manual:

https://dev.mysql.com/doc/refman/5.7/en/group-replication-requirements.html

It appears that so long as you're on the appropriate version you'll have access to this feature.


SQL queries failing, Memory not releasing

SQL Server is kind of a memory hog. You could try clearing the cache, BUT I would highly recommend doing some reading on these commands first before you using them. From what I hear, their can be negative side effects when using these especially in a production environment.

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREEPROCCACHE

Here are some links I found useful:

Difference between FreeProcCache and FreeSystemCache

https://sqlserverperformance.wordpress.com/2009/12/28/fun-with-dbcc-freeproccache/

http://jamessql.blogspot.com/2012/04/clean-sql-server-cache.html


How can I get a list of dates between two given dates with mysql?

A really easy way to do it is to have a table of numbers. Query this table for numbers less than the number of days between the two dates, and select the smaller date plus the number to return the dates themselves. Easy to stick a predicate in the where clause to handle Tuesdays and Thursdays too.


Is EXPDP the best backup approach when running Oracle on Windows with no RMAN?

When you install an Oracle database RMAN is installed along with it. This has been the case for many years.
As I work at the Enterprise level backup has a fairly restrictive meaning and an RMAN backup is the only backup solution that Oracle supports.  Even third party solutions like Commvault still use RMAN commands to backup the database.
Dump files are very useful for refreshing development but you cannot do a complete restore of a database from a dump file.  You would need to install a new database first. See this excellent answer from Phil here
Ultimately the answer is what is required for backups?

Is there a mean time to recovery?
A disaster recovery plan?
SLA level agreements to clients?

If your shop has to answer these kind of questions then best practice is RMAN backups copied to another server with older backups sent off site and aged out after so many days.


Oracle 11g - PL/SQL block - Unable to use a newly added column in subsequent DML

The column does not exist at compile time. Try this:

begin
   execute immediate 'alter table my_table add new_column varchar2(30)';
   execute immediate 'update my_table set new_column=old_column';
end;


The answer to this question varies depending on what database engine backend you're using.

If you're wildly unfamiliar with all of them, I really recommend that you use TokuDB, because it's entirely self-configuring with no modifications necessary out of the box.

I use MariaDB (MySQL clone) with Aria tables. My configuration would be totally, and completely different from yours.

If you use InnoDB, set innodb_buffer_pool_size to 80% of what you find appropriate for a database to be using, up to a maximum of 10% larger than your data size.

If you use TokuDB, use stock config.

If you use MyISAM, may Loki have mercy on your soul.