Test table:

create table dbase234936 (
id integer,
serial varchar(7),
version timestamp,
is_deleted integer
);

Test data:

insert into dbase234936 values ( 10 , 'AAAAAA' , '2019-04-14 15:28:08', 0);
insert into dbase234936 values ( 22 , 'AAAAAA' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 13 , 'AAAAAA' , '2019-02-10 15:28:08', 0);
insert into dbase234936 values ( 40 , 'BBBBBB' , '2019-04-17 15:28:08', 0);
insert into dbase234936 values ( 27 , 'BBBBBB' , '2019-02-20 15:28:08', 0);
insert into dbase234936 values ( 17 , 'CCCCCC' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 35 , 'CCCCCC' , '2019-01-01 15:28:08', 0);

Query:

with windows as (
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
select id, serial, version, case when rn=1 then 0 else 1 end as is_deleted
from windows
;

Update based on the above CTE:

with windows as (
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
update dbase234936 set is_deleted = case when rn=1 then 0 else 1 end
from windows
where dbase234936.id = windows.id;

I like windowing functions :-)

DB Fiddle link


Postgresql function for detecting alerts

If I'm understanding the question correctly?

I would run the alert code in a Insert Trigger, and I would  add a column to measurement_tab to track which alerts matched the measurement.

alter table measurement_tab add column trigger_alert_id integer[];

CREATE TRIGGER alert
BEFORE INSERT
ON measurement_tab
FOR EACH ROW
EXECUTE PROCEDURE _alert();

CREATE FUNCTION _alert()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$

declare _r record;

begin

for _r in select id from alert_tab
        where alert_tab.room_id = measurement_tab.room_id
        and value < alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

for _r in select id from alert_tab
        where alert_tab.room_id = measurement_tab.room_id
        and value > alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

Run_alert_function(new.trigger_alert_id);
         # this is where the other alert code
         # that emails/sends out the alerts gets fired off;
return new;
end;

$BODY$

If there is need to block alerts that have already been triggered.  Meaning once alert has been set it must be reset before it can be fired off again,   this can be done with additional logic in the loop,  that looks for previous entries in measurement_tab that set this alert previously.


Returning all rows from multiple partitions of a partitioned table

As long as you have the appropriate check constraints on the date column on each of the underlying tables, you can create a partitioned view:

CREATE VIEW dbo.2015Sales
AS
SELECT date, sales FROM dbo.Jan2015Sales
UNION ALL
SELECT date, sales FROM dbo.Feb2015Sales
...

This would allow the UNION ALL to be hidden behind the view, and SQL Server can eliminate irrelevant tables (based on your check constraints) from the query plan if the query explicitly specifies a date range.

However, partitioned tables, in which all of the data would be in one table and no UNION ALL would be needed, is the more common approach.

That said, there are a lot of potential performance implications for either approach, so it's best to test on your specific data and workload. For example, partitioned views frequently yield query plans with extraneous Concatenation operators, a situation that may reduce the accuracy of the Cardinality Estimator (especially in SQL 2012 and earlier). And partitioned tables, while very useful and probably what you want, do come with a couple potential downsides as well.


Whether Logfile Shrinking is good or bad one for Performance in SQL?

Shrinking of log file ON DAILY BASIS like one you do with query you posted is seriously bad and you should NOT do that. The main reason is that after you have shrinked the file it will grow again when new transaction comes and since INSTANT FILE INITIALIZATION is not there for Log files, its only for data files, when log file will grow DB engine will actually have to zero out space to write information so instead of overwriting free space it has to be overwritten with zero values (0×0) and then information can be written. This process takes time and thus delays processing of query

Nice explanation has been done in This link

Please note that instant file initialization is applied to some extent for Tempdb log files even though this is correct you should not shrink tempdb log files on daly basis.

A more simple thought, why to shrink if space has to eventually grow when log informations will be written and log will grow. Its a bad practice, internet is filled with advise not to shrink data files but shrinking log file is equally bad

EDIT: Needless to say( as already pointed out) you are breaking chain of logs by changing recovery model to simple. You loos epoint in time recovery. If point in time recovery is not required kep recovery model of database as simple and log truncations would be handled by DB engine if something is not holding logs and need it in transaction.


Fastest way to change column type from varchar2 to number on large table in Oracle

I guess, you can use parallel operations:

Parallel DML

Let your table support parallel operations:

ALTER TABLE EFF parallel;

Then you perform all your steps as you describe in the question.

Parallel DDL + CTAS

CREATE TABLE temp_table_EFF
PARALLEL
AS
SELECT MOD, MP, PROG, TO_NUMBER(RNK), EFF_PART
  FROM EFF

Then you drop your table:

DROP TABLE EFF;

Then you rename your temp table:

ALTER TABLE temp_table_EFF RENAME TO EFF;

Then you add indexes for to this new table:

ALTER TABLE EFF ADD CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, NRNK);

I have no tables with 30M records, so I can't tell you what method is faster. Try both. If you think that there won't be decent performance improvement try to use the NOLOGGING option to forbid your table generate the redo information.


Two (almost) identical queries, but only one of them uses the index

If the table is InnoDB, here is what's happening:

The query optimizer sees SELECT * and does this

Sees all columns are included in the SELECT list
Uses the clustered index since all columns are included

The query optimizer sees SELECT batch_id, study_id and does this

Sees SELECT list has two columns, not all columns
Sees the study_id index (and other indexes)
Chooses the study_id index
Retrieves batch_id using PRIMARY KEY attached to study_id index entry

Why would this happen at all? The Query Optimizer is kinda picky

Aug 13, 2013 : Why does MySQL ignore the index even on force for this order by?
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?

Sometimes, it can go the other way

Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?

The key to your question is that the Query Optimizer does not view the two queries as almost identical. To the query optimizer, they are completely different in appearance and in its treatment.


How do I list the database files contained in a SQL Server 2005 backup file using T-SQL?


Implement AKA in SQL Server Master Data Services

You would use a separate entity and a derived hierarchy.
Ingredient(Code, Name, ...)

IngredientAKA(Code, Name, Ingredient)

Where the Ingredient.Name is the "official" or "canonical" name, and IngredientAKA.Name is has all the alternate names.


When to use lateral joins vs correlated subqueries in Postgres?

Using the subquery version would become awkward if you wanted to return more than one column or more than one row from the innermost subquery. If you never want to do that, then I find the first form easier to read.  (Or at least it would be if you removed the outermost subquery and reformatted a bit--can't the expression and the ORDER BY be done directly in the middle query "c"?)

The difference in plan between the two is unlikely to have any practical significance.


Prepared statements with many parameters is slow

1) IN clauses are during the optimization phase expanded to multiple OR. Example: C1 = @P1 Or C1 = @P2.

2) If the parameter count is greater than 63 the optimization process will build an internal table for it. Having said that, I’m not so sure about the stats being generated on the dynamically generated temp table.

3) Putting your parameters in a #temp table may result in a slightly different execution plan, however, this may also introduce concurrency issues depending on the design of your application.

4) I would, before introducing a temp table, try to rewrite the query into smaller logical chunks. For example, isolate the filter process against an optimized smaller outer table and cross apply that with the inner table.

5) Ensure that your stats are updated and that you are getting a parallel execution plan. Would be nice if you could share your plan so we can have a peek at what’s going on.

  Blockquote
  The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes,

6) "index seek" can be "bad" if they represent a lot of logical reads. You should check the statistics in order to be sure that index tuning won't yield any better results.


How do I find out if a procedure or function exists in a mysql database?

A generic answer to this type of question is that all MySQL databases include a database called information_schema which includes all the metadata as tables you can just query.

The information you want is in a table called ROUTINES.  For example:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
       ROUTINE_TYPE="PROCEDURE"
   AND ROUTINE_SCHEMA="dbname"
;


How to grant rights for a user in DB2 Express?

DB2 will by default look for the group information in the same directory where the user is defined. For a domain user that would be the domain itself. If a domain user is a member of a local group, after setting DB2_GRP_LOOKUP=local make sure to restart the DB2 instance.

If you have enabled extended security (which is the default installation option), you may want to try adding your ID to the DB2USERS group.

Since the DB2 instance will need to perform the domain directory lookup when authenticating domain users, the account that runs the DB2 instance service must have sufficient permissions to perform the AD lookup. For example, Local Service authority would not be able to do that.

Each DB2 instance service needs such authority. There are other DB2-related services (DB2 DAS, DB2 Remote Command Server, etc. -- described in http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.qb.server.doc/doc/r0023249.html) -- they do not perform authentication and therefore do not require domain rights.


regex character class :print: in postgres

It is the LC_CTYPE setting that defines character classification, case conversion, and other character attributes. So this is what determines if a character is printable or not.

ñ is not printable in the C locale:

SHOW lc_ctype;

 lc_ctype
----------
 C
(1 row)

select regexp_replace('niño', '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 ni[X]o
(1 row)

But it is printable in de_DE.utf8:

SHOW lc_ctype;

  lc_ctype
------------
 de_DE.utf8
(1 row)

select regexp_replace('niño', '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 niño
(1 row)

If you don't want to change the lc_ctype of the database (which can only be done with dump/restore), you can also use the COLLATE clause to give a string expression a different collation (and character type):

select regexp_replace('niño' COLLATE "en_US.utf8", '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 niño
(1 row)

You see that ñ is also recognized as a printable character in the English UNICODE collation, but you can also use the language independent ICU collation if you have built PostgreSQL with ICU support:

select regexp_replace('niño' COLLATE "und-x-icu", '[^[:print:]]', '[X]','g');

 regexp_replace
----------------
 niño
(1 row)


What is the algorithm behind the EXCEPT operator?

What is the internal algorithm of how the Except operator works under the covers in SQL Server?

I wouldn't say that there's a special internal algorithm for EXCEPT. For A EXCEPT B, the engine takes distinct (if needed) tuples from A and subtracts rows that match in B. There are no special query plan operators. The distinct and the subtraction are implemented through the typical operators that you would see with a sort or with a join. Nested loop join, merge join, and hash join are all supported. To show this, I'll throw 15 million rows into a pair of heaps:

DROP TABLE IF EXISTS dbo.TABLE_1;

CREATE TABLE dbo.TABLE_1 (
    COL1 BIGINT NULL,
    COL2 BIGINT NULL
);

INSERT INTO dbo.TABLE_1 WITH (TABLOCK)
SELECT TOP (15000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS dbo.TABLE_2;

CREATE TABLE dbo.TABLE_2 (
    COL1 BIGINT NULL,
    COL2 BIGINT NULL
);

INSERT INTO dbo.TABLE_2 WITH (TABLOCK)
SELECT TOP (15000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

The optimizer makes it usual cost-based decisions about how to implement the sort and the join. With two heaps I get a hash join as expected. You can see other join types naturally by adding indexes or by changing the data in either table. Below I force merge and loop joins with hints just for illustrative purposes:

  Does it internally take a hash of each row and compare?

No. It is implemented as any other join. One difference is that NULLs are treated as equal. This is a special type of comparison which you can see in the execution plan: <Compare CompareOp="IS">. However, you can get that same plan with T-SQL that does not include the EXCEPT keyword. For example, the following has the exact same query plan as the EXCEPT query that uses a hash join:

SELECT t1.*
FROM
(
    SELECT DISTINCT COL1, COL2
    FROM dbo.TABLE_1
) t1
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.TABLE_2 t2
    WHERE (t1.COL1 = t2.COL1 OR (t1.COL1 IS NULL AND t2.COL1 IS NULL))
    AND (t1.COL2 = t2.COL2 OR (t1.COL2 IS NULL AND t2.COL2 IS NULL))
);

Diffing the XML of the execution plans only reveals superficial differences around aliases and things like that. The probe residuals for the hash joins do the row comparison. They are the same for both queries:

If you still have doubts, I ran PerfView with the highest available sample rate to get call stacks for the query with EXCEPT and the query without it. Here are the results side by side:

There is no real difference. The call stacks there reference hashing are present because of the hash matches in the plan. If I add indexes to get a natural merge join, you won't see any references to hashing in the call stacks:

Any hashing that occurs is due to the implementation of hash match operators. There isn't anything special about EXCEPT which leads to a special, internal hashing comparison.


Postgresql replication slot used by barman has a long replay_lag

This large replay lag in pg_stat_replication seems to belong to a pg_receivewal process. Now pg_receivewal writes a copy of the WAL files, but it does not apply them anywhere. Consequently, it will not report back to the primary server that WAL was applied.

This is perfectly normal; compare commit fd7d387e05.

The “Failures of WAL archiver” are also nothing you have to worry about. This number probably comes from pg_stat_archiver and indicates that there has been a problem archiving WALs in the past. This problem must have been resolved, because “Last archived WAL” indicates that more recent WAL files have been archived successfully.

PostgreSQL won't skip archiving WALs — if archiving fails, the archiver gets stuck at that place and retries until successful.


"Cannot insert the value NULL", but there is no NULL values!

NULL is not a value. Something cannot '=' NULL

You want:

SELECT Name FROM FinancialInstitution WHERE Name IS NULL


SQL Select query to include each row a number of times

Silly, I just misinterpreted the results by using wrong ordering. The CROSS JOIN was indeed the correct approach. Here's what I did (please do correct me if this is not the proper approach to take):

WITH week(day) AS
(
  SELECT TOP (7) number + 1
  FROM [master].dbo.spt_values
  WHERE [type] = N'P' ORDER BY number
)

SELECT key, prc, fixweight, week.day
  FROM weights
  CROSS JOIN week
  ORDER BY key

As have been suggested by the comments: use spt_values only as a quick hack for ad-hoc queries or testing, in production you should use a number table or, if the list of numbers is so slow, you can use ypercube's solution:

SELECT key, prc, fixweight, week.day
  FROM weights
  CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7)) AS week(day)
  ORDER BY key


I'm going to assume you have some sort of identity or unique key column on your SI table. (This will be a little more difficult if you haven't.)
I'm also going to assume your autoInsert column is a BIT data type. This means the only values we can possibly have are 1, 0 or NULL. And you want the value updated to 0.
And finally, I'm going to assume you only care about updates to the autoInsert column.
If that's all true, try this:
CREATE OR ALTER TRIGGER dbo.trg_ZeroAutoInsert
    ON dbo.SI
AFTER UPDATE
AS
UPDATE          dbo.SI
SET             autoInsert = 0
FROM            dbo.SI
    INNER JOIN  inserted AS i
        ON      SI.id = i.id ;

If, however, your autoInsert column is an INT (or similar) data type, you can add the where clause to only cover values of 1 or NULL:
CREATE OR ALTER TRIGGER dbo.trg_ZeroAutoInsert
    ON dbo.SI
AFTER UPDATE
AS
UPDATE          dbo.SI
SET             autoInsert = 0
FROM            dbo.SI
    INNER JOIN  inserted AS i
        ON      SI.id = i.id
WHERE           i.autoInsert = 1
    OR          i.autoInsert IS NULL ;


How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

From my guide How to support full Unicode in MySQL databases, here are the queries you can run to update the charset and collation of a database, a table, or a column:

For each database:

ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

For each table:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

For each column:

ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

(Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a VARCHAR column.)

Note, however, that you cannot fully automate the conversion from utf8 to utf8mb4. As described in step 4 of the abovementioned guide, you’ll need to check the maximum length of columns and index keys, as the number you specify has a different meaning when utf8mb4 is used instead of utf8.

Section 10.1.11 of the MySQL 5.5 Reference Manual has some more information on this.


Is Minimum Canonical cover supposed to be singular?

In general different results can be produced, depending on the order in which the dependencies are considered for the elimination of extraneous attributes and redundant dependencies. This means that even the normalization algorithms, like the analysis algorithm for BCNF, can produce different results in certain cases.

A terminological note: in general the algorithms used for the normalization start by computing any canonical cover. A minimal canonical cover is a cover which has a number of dependencies less than that of equivalent covers. An optimal canonical cover is a cover that has less symbols of attributes than each other set equivalent to it. Different algorithms exists for the different type of covers, some of them are discussed in Maier, D. The Theory of Relational Databases, Computer Science Press, Rockville, Maryland, 1983.


Probably different timezone is configured on those machines. Check:

SELECT SESSIONTIMEZONE FROM dual;


MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Disable binary logging in your session, drop the table, and enable binary logging.

SET sql_log_bin = 0;
DROP TABLE IF EXISTS ... ;
SET sql_log_bin = 1;

This works because if the Slaves do not see the DROP TABLE command registered in the Master's binary logs, it cannot run the DROP TABLE.

I have recommended using SET sql_log_bin = 0; before in my earlier posts

Nov 12, 2012 : Mysql replication and ignore tables
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Jul 11, 2011 : ALTER TABLE on a large table with an indexed column (This is where I use SET sql_log_bin = 0; to directly prevent data from being written to Slaves)

Give it a Try !!!


Set boolean field to true if query match otherwise set to false

Yes, one update is sufficient:

UPDATE spaceShips
    SET hasArtificialIntelligence = spaceShips.id in ( ...,someIds,... )
WHERE shipType='warship'

spaceShips.id in ( ...,someIds,... ) will evaluate to either true or false (assuming someid is not null)


Percentage difference of the last value from the previous values of a column based on certain data group within the same table

If your version of SQL Server is recent enough (2012+), the problem can be solved with the window/analytic function LAST_VALUE():

Column4 = (LAST_VALUE(Column3) OVER (PARTITION BY Column2 ORDER BY Column1)
           - Column3) / Column3 * 100

or FIRST_VAUE() with inverse order:

Column4 = (FIRST_VALUE(Column3) OVER (PARTITION BY Column2 ORDER BY Column1 DESC)
           - Column3) / Column3 * 100


begin tran instead of use db_name inside a SQL Job for executing Stored Procedure

When you do that in a job it basically ends the session and rolls back the transaction when the job finishes.  You can trace that using SQLTransaction event class in Profiler.
That's the reason why you do not see any open transaction/sessions or the work being done.


What do I need to develop an integration service to extract data from an Oracle DB to a SQL Server DB using Visual Studio 2015?

There are two ways to synchronize data or extract database from one (source) to another (target) database, based on what is a business requirement.
If you need to extract raw data from Oracle to SQL Server, then you can use Transactional Replication which helps you to replicate tables and its data from Oracle to SQL Server.
ETL/SSIS can be used for the same purpose, but it's for more than just replication raw data. It also helps you to extract data from the source, analyze it, manipulate it and load to target.
To configure Transaction Replication for Oracle (as source):

Both databases should be accessible on respective servers.
Oracle Client should be installed on the SQL Database Server.
Configure listener on SQL DB server. The listener It is used while we configure replication.

Rest of the steps are mentioned here.
Thanks


Database with many publication, How to make snapshots for publications run faster

Here is one thing you can do to keep your database from getting pegged while a snapshot is in place:

Set up master -> slave replication.
Use the slave replica for snapshots.

If you were to provide the flavor of SQL you are using as well as what mechanism you are using to create snapshots (i.e Xen, AWS, etc.), I might be able to provide a bit more context.


Give third-party access to AWS hosted MySQL

GRANT SELECT ON My_Database.* TO bi_user;

It's explained in detail here (sample code below).

Don't forget though that people with SELECT privileges can cripple a database as surely as if they had DELETE privileges by running complex reports.

Code from website:

If you want give a user read-only access to a database, you can grant to him/her only the "SELECT" privilege, so that he/she can not run any DDL statements, and any INSERT, UPDATE, or DELETE statements. The tutorial exercise gives you a good example:
>cd \mysql\bin
>mysql -u root -ppassword

mysql> USE fyi;
Database changed

mysql> CREATE TABLE links (id INTEGER, name VARCHAR(80));
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO links VALUES (1, 'dba.fyicenter.com');
Query OK, 1 row affected (0.05 sec)

mysql> CREATE USER guest IDENTIFIED BY 'other_password';
Query OK, 0 rows affected (0.24 sec)

mysql> GRANT SELECT ON fyi.* TO guest;
Query OK, 0 rows affected (0.00 sec)

mysql> QUIT;

>mysql -u guest -pother_password

mysql> use fyi;
Database changed

mysql> SELECT * FROM links;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | dba.fyicenter.com |
+------+-------------------+
1 row in set (0.04 sec)

mysql> INSERT INTO links VALUES (2, 'dev.fyicenter.com');
ERROR 1142 (42000): INSERT command denied to user
'guest'@'localhost' for table 'links'


Is there a way to display current noise words in SQL 2000 Full Text Indexing?

You are experiencing a known issue (where the default Windows noise.enu file is used instead of SQL Server's version). According to KB #889708, you need to copy the following registry key from the good node to the bad nodes:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\Override

Then you may still need to re-populate your FT indexes, not sure, and no 2000 instance handy to test, sorry. Also I don't believe there is any way to query the list of noise words (like you can on more modern versions).


SQL Server Alias won't work for my database instance

The problem was that the 'SQL Server Browser' service was not running.

I right-clicked this, clicked 'Properties', set the start mode to 'Automatic' then manually selected to start the service.


Short answer would probably be no to the actual question. That being said it should be a fairly simple thing to fix.

1) Have your process bulk insert the CSV file to the same table every night after a TRUNCATE.

2) Do a LEFT JOIN to find with a null against your employee table to find the new ones and insert them.

3) Do a right join with null against your employee table to find the "users no longer on the feed" and update their "left_date" field.

If you need to run these updates manually (sounds like it from your question) you could still have the SQL just create the statements and so you could review and run them manually.


Relational DB seems a perfect fit for your needs.

MySQL will probably give you some hard time on some of the queries you want to run or you need to off-load that to the application side but that doesn't mean it cannot be done. Depends on the size of your tables too.

Databases with window function capabilities (practically anything else than MySQL) will help you with queries like "time between commits". I'd personally recommend PostgreSQL which is great for analytical queries as well.

Document stores and key-value stores are not meant to be backends for serving analytical data retrieval. You might gain some performance on read/write operations but since your application has to do the heavy lifting instead of the database the number of operations increases significantly. So with those it might even be slower at the end.

How you represent that data at the end is more of a business questions than technical. What your users want. Do they prefer excel sheets over for example an internal websites where they can check? Google Sheets might be a middle ground there. Just an idea.


how many 'text' column a table could have in mysql? I have this error “Row size too large (> 8126).”

The problem is the row size limit for InnoDB tables, in this links you can find some approaches to solve this:

http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitation/
InnoDB create table error: "Row size too large"


Reorganize full text catalog is offline or online?

First of all, Welcome to stackexchange and thanks for your 1st Post.

To answer your questions :

if this process of reorganize the Full Text Catalog is done online or offline?

The reorganize process is Online, but is slower than Rebuild.

When the catalog reorganize is occurring the users will still be able to query the full text data?

Yes, but there might be blocking. Also you need to take account of below from BOL :

Depending on the amount of indexed data, a master merge may take some time to complete. Master merging a large amount of data can create a long running transaction, delaying truncation of the transaction log during checkpoint. In this case, the transaction log might grow significantly under the full recovery model.

Also, I would suggest you take a look at the fragmentation of the full text Indexes using :

I normally go for REORGANIZING if you have between 30-50 fragments per table

 -- for SQL Server 2008 and up
SELECT OBJECT_NAME([table_id]) AS TableName, COUNT([fragment_id]) AS Fragments
    FROM sys.fulltext_index_fragments
GROUP BY OBJECT_NAME([table_id])
HAVING COUNT([fragment_id]) >=30

Also, Improve the Performance of Full-Text Queries is a good reference along with this whitepaper.


According to my knowledge of indexes (i haven't work with MySql lately), first: whatever field you have in your LIST of SELECT should also be indexed.

But i noticed another probable behavior that could be happening in your given query: in your INs you are looking for strings ('1', '2', 'etc'), are those fields Varchars, Chars?.

If not, try to use INT instead of STRING, if your "product_family_id" is INT and you are doing "product_family_id IN ('1','2') the engine executes an implicit conversion, resulting in more executon time.

If "product_family_id" is string and you are storing numbers as text. and if you only have numbers stored in there (as string though) then try to cast to int

cast(product_family_id as int) IN (1,2,3,4,5)

Try both solutions. Add an Index to the select list members, use numbers instead of text. and try casting as well.


For security reasons SQL Server 2008 no longer opens TCP port 1433 upon installation.  You will need to create a firewall rule that allows communication over TCP port 1433 (and UDP port 1434 if you wish to use the browser service) to allow applications to communicate with SQL Server.


Try this untangled version to update all rows of char_check:

UPDATE car_check c
SET    fencing = ST_Contains(u.geom, ST_MakePoint(t.lat::float8, t.lng::float8))
FROM   test t
      ,rule r
JOIN   (
   SELECT DISTINCT ON (polyname)
          polyname, geom
   FROM   unknown_table
   ORDER  BY polyname, version DESC
    ) u USING (polyname)
WHERE  t.id = c.carid
AND    r.cargroupid = c.cargroupid

Major points

Use the simpler ST_MakePoint to construct a point geometry from numeric input.
You do not need ST_AsText() at all, since your column geom is of type geometry already.
Fastest and simplest way to get the geometry for the biggest version per polyname in table unknown_table is to use DISTINCT ON, which is a Postgres extension of the SQL standard. More details in this related answer:
How do I efficiently get "the most recent corresponding row"?
In reference to the currently accepted but invalid answer: the concatenation operator in Postgres (and standard SQL) is || (not +)


You can use a Derived Column transformation. Add an expression that converts the gender column into the initial. You can use something like SUBSTRING(GENDER,1,1) for this.


Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Apples and Oranges. Roles are roles and schemas are schemas. The fact that there is a role called db_accessadmin and also a schema called db_accessadmin does not mean a role is a schema nor that a schema is a role. Roles are security membership containers, a principal is member of a role. Schemas contain database schema bound objects and are owned by a principal. When you create a new user you can choose his default schema, add him to certain roles, and grant him ownership of schemas. Although the actions are separate, the designers of this dialog feel they are somehow related as to merit all that UX real estate (that is questionable, but a completely different topic).


How to know which columns value is different between two rows

This is the solution I propose, If you have more columns to test differences you just have to union another select in the same manner.
As I previously commented, in the sample date you provide for id =3 both [Address] and [Address Code] have different values, for these situations two rows are given one showing that column [Address] is different and another showing that column [Address Code] is different.

select id, 'Address Code' As [Column Changed] from
(
select distinct id, [Address Code] from Addresses
) As Differents
group by id
Having count(*) > 1
UNION
select id, 'Address'  As [Column Changed] from
(
select distinct id, [Address] from Addresses
) As Differents
group by id
Having count(*) > 1

Output

id  Column Changed
1   Address Code
2   Address Code
3   Address Code
3   Address


PIVOT, Row_Number() returning multiple rows

Try removing isPrimary from your SELECT where you're calculating the ROW_NUMBER().

SELECT ID, [1], [2], [3]
FROM (
  Select ID, EMAIL
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY isPrimary desc) AS rn
  from #tmp
) eml
PIVOT (
  min(EMAIL)
  FOR rn IN ([1],[2],[3])
) P

| ID                   | 1                  | 2                   | 3            |
|----------------------|--------------------|---------------------|--------------|
| B4100358183Q!ZH.ETES | ABoyd@Energize.com | BSykes@Energize.com | TC6@test.com |


Hierarchical queries, as those recursive queries are known, are now supported in MySQL 8.

Old Answer

Alternatively, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries

You can also find a discussion on how to store hierarchical data with other models than with an Adjacency List (i.e. the Parent column) here: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/

Good luck!


Non Super User cannot alter stored procedures and functions

Perhaps you want this in the CREATE:

SQL SECURITY INVOKER

or, more likely,

SQL SECURITY DEFINER


SQL Server - login failure every day at same time with the service account

Using an Extended Events session I was able to track down the issue and see that it came from the syspolicy_purge_history job that SQL Server creates by default to clean up Policy Management records. Step 3 is "Erase Phantom System Health Records". This contains a PowerShell script that tries to connect to OTHER instances on the machine, causing the login failures. This is a known bug and only affects machines with more than 1 SQL Server instance.  https://connect.microsoft.com/SQLServer/feedback/details/606801/syspolicy-purge-history-job. So when you see login failures on an instance, they are not that instance trying to connect to itself, but another instance on the same machine.

The link here explains further and the code below will update the job step to connect to only the instance the job is running on.

https://www.codykonior.com/2015/05/31/login-errors-with-syspolicy-purge-history/

  Exec  msdb.dbo.sp_update_jobstep @job_name = 'syspolicy_purge_history', @step_name = 'Erase Phantom System Health Records.', @step_id = 3,
      @command=N'$applicationName = "SQLPS ($env:USERNAME@$env:COMPUTERNAME)"
  $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection
  $SQLServerConnection.ConnectionString = "Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=master;Integrated Security=SSPI;Application Name=$applicationName"
  $PolicyStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SQLServerConnection)
  $PolicyStore = New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($PolicyStoreConnection)
  $PolicyStore.EraseSystemHealthPhantomRecords()
  '

OR, copy and paste the below into the job step:

$applicationName = "SQLPS ($env:USERNAME@$env:COMPUTERNAME)"
$SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLServerConnection.ConnectionString = "Data Source=$(ESCAPE_NONE(SRVR));Initial Catalog=master;Integrated Security=SSPI;Application Name=$applicationName"
$PolicyStoreConnection = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection($SQLServerConnection)
$PolicyStore = New-Object Microsoft.SqlServer.Management.Dmf.PolicyStore ($PolicyStoreConnection)
$PolicyStore.EraseSystemHealthPhantomRecords()


Messaging Database - How to handle multiple recipients

You need to have a many-to-many table for your message recipients.  Think about it this way, each message can be sent to one or more recipients.  Also, each recipient can presumably receive zero, one or more messages.  This means you have a many-to-many and you need an intersection table to record this information in your database.

Consider this ERD:


How to connect UDL file to a Msaccess DB 2007 (Protected with password)?

I found to ways to solve this problem.
First, I have opened the 'UDL' file using a text editor and change it as follows :

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source='Your_DB_Full_Name';
Persist Security Info=False;
Jet OLEDB:Database Password='Your_Database_Password'

Second, I have viewed the UDL file properties (mouse right click) then:

At the Provider, tab select the driver.
At the All tab, double click on the Data Source property and write the full name of your DB (with full path).
At the all tab, double click on Jet OLEDB:Database Password property and write your DB password.
At the All tab, double click on the User ID property and write your DB user if it was different from Admin.
Finally, click OK.


Access control on Azure SQL Data warehouse

I haven't tested this on Azure, but on my local SQL Server 2016, I cannot reproduce what you're seeing.  Perhaps the target user has more rights than you think?  Do they have membership in a role that is giving them access?

Anyway, the testbed I used to test this is:

CREATE TABLE dbo.SomeTable
(
    SomeTable_id int NOT NULL
        CONSTRAINT SomeTable_pk
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
) ON [PRIMARY];

CREATE USER UserA WITHOUT LOGIN;
DENY SELECT ON dbo.SomeTable TO UserA;
GRANT CREATE VIEW TO UserA;
GRANT CREATE SCHEMA TO UserA;

EXECUTE AS USER = N'UserA'; --anything past this point is executed using UserA's permissions

--fails with SELECT permission denied
SELECT *
FROM dbo.SomeTable;
GO

--this works due to the GRANT CREATE SCHEMA statement above
CREATE SCHEMA UserA;
GO

--this works due to the GRANT CREATE VIEW statement above
CREATE VIEW UserA.SomeTableView
AS
SELECT *
FROM dbo.SomeTable;
GO

--this fails since the user who created the view doesn't have rights to the underlying table.
SELECT *
FROM UserA.SomeTableView;
DROP VIEW UserA.SomeTableView;

--revert back to "me" instead of UserA
REVERT

UserA is able to create the view, but they cannot actually select from it.  The error returned is:

  Msg 229, Level 14, State 5, Line 27
  The SELECT permission was denied on the object 'SomeTable', database 'MaxTest', schema 'dbo'.


MySQL very slow query when changing one WHERE field despite no index/key

VIEWPOINT #1 : You many need to take a look at column value population

SELECT COUNT(1) rowount,type FROM games GROUP BY type WITH ROLLUP;
SELECT COUNT(1) rowcount,leaguelevel FROM games GROUP BY leaguelevel WITH ROLLUP;

From your question, I gather two things:

The number of rows in games with type='R' must be a low number against the number of rows in the games table.
The number of rows in games with leaguelevel='mlb' must be a high number (greater than 5% of the table) against the number of rows in the games table. (5% is a rule-of-thumb number in the eyes of Query Optimizers)

VIEWPOINT #2 : You may need to refactor this query

Notice that query will perform the WHERE portion after all JOINs are complete. If the WHERE portion could  be performed earlier that could help reduce the time. Try reorganizing the query like this:

SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN (SELECT * FROM games WHERE leagueLevel = 'mlb') games
ON games.gameName = hits.gameName
LIMIT 50;

VIEWPOINT #3 : Retrieve only the columns you really need

I see you have SELECT * and you have four tables (hits, stadiums, players, games). You will have a lot of duplicate data to drag into the query, particularly when dragging the gameName column from all four tables.

You should reorganize the query to bring only one gameName column:

SELECT hits.gameName,hits.*,players.*,staduims.*,games.* FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON (players.gameName = hits.gameName AND players.id = hits.batter)
JOIN (SELECT * FROM games WHERE leagueLevel = 'mlb') games
ON games.gameName = hits.gameName
LIMIT 50;

Additionally, if you do not need every column from the hits tables, then only include the column you know you will access. The same goes for players, stadiums, and games.

In other words, as an example, if you only need the playerName from the player table, then you do not need player.* in the SELECT. You will need just player.playerName.

VIEWPOINT #4 : You may need to index the leagueLevel column

You will need to do the following to make the needed index:

ALTER TABLE games ADD INDEX (leagueLevel);

Before doing so, run this

SELECT COUNT(1) rowcount,leaguelevel FROM games GROUP BY leaguelevel WITH ROLLUP;

Any value for leagueLevel whose count is greater than 5% of the table will cause the MySQL Query Optimizer not to use the index.


Is the size limit of a json field before or after compression in Postgres?

The limit of 1GB on varlena data types like json is before compression.
Note that you will need a lot of RAM to read and write such values. It is usually a problem and bad design to store such large values in a database.


Install the extension pg_stat_statements with the SQL command

CREATE EXTENSION pg_stat_statements

You may want to make sure you create this by using an appropriate user (such as the user your application uses or some dba account). Be aware that whichever user creates the extension will also own it.

This will require a server restart for it to be usable (because you have to change the config in postgresql.conf a tiny bit that affects memory consumption), but it will give you statistics on all queries.

Please see the documentation page for more information about what pg_stat_statements provides. See here for a quick reference on configuration of the postgresql.conf config file for pg_stat_statements.

Once you have it installed and running, you can view statistics by querying the pg_stat_statements view..

select * from pg_stat_statements;
-- or
select * from pg_stat_statements where total_time / calls > 200; -- etc ..


Redis doesn't do scheduling of jobs/scripts - you'll have to use something (e.g. cron) to trigger the script.

The script itself sounds simple enough - LRANGE should be used to iterate the list's elements, sum them up, SET total-count to the result and DEL the list. Lua would be perfect for this but you can also .sh with redis-cli for example.

What bothers me, however, is that LRANGE would be O(N) and you want to run this all the time... that means your Redis will be busy doing this and all that just for an aggregate? Perhaps you should consider an alternative that will save all the work (from Redis) and the need to schedule a script. One possible alternative, for example, would be to add the list's members directly to the total-count key, possibly even without storing them in the list in the first place - unless I missed something, this appears a simpler solution to you request.


Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)?

If you have a working maintenance plan, which is really just an SSIS package, you can export that .dtsx file out of the database and redeploy to the next one.

For 2008/2012, those packages are stored in msdb.dbo.sysssispackages which you can verify with a query

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT
    F.FolderPath
,   P.PackageName
,   F.FolderPath + '\' + P.PackageName AS PackagePath
,   P.packageFormat
,   P.packageType
,   P.vermajor
,   P.verminor
,   P.verbuild
,   P.ownername
,   P.PackageId
FROM
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
-- uncomment this if you want to filter out the
-- native Data Collector packages
WHERE
     F.FolderPath <> '\Data Collector';

You will then want to pipe that information to repeated dtutil calls much as I describe on this SSIS Package Extract From msdb. Instead of the /file output though, you'd want to make it /destserver thatdatabase /copy SQL;PkgName See the answer over on
https://stackoverflow.com/questions/7603021/updating-deployed-ssis-package/7605044#7605044

As noted in the comments, if you used package level encryption, you will need to add a parameter for that as well /encrypt file;"foo.dtsx";3;<somepass> This is presented as is because I don't advocate package passwords, nor have I tested it.


That was a full day pre-conference session, for which a fee (separate from the main conference) was required to attend. It was not recorded, and so will never be available.
From the PASS Summit 2013 FAQs: (emphasis added)

What will be included on the PASS Summit 2013 session recordings and how can I purchase them?
The PASS Summit 2013 session recordings include all the Conference Sessions, including half-day sessions and spotlights. (Note: Pre-Conference Sessions are not recorded.) Attendees can purchase either the session recordings download option for $195 or the recordings on USB flash drive for $240 (one session recordings purchase at the attendee price per attendee). Note: Shipping charges for the USB flash drive are $20 within the continental US and $30 international. Streaming of the sessions will be made available for free to attendees after Summit.


Where are connections to databases made from when using SSRS?

From Server2 to Server3.  The SSRS service is what connects to the database which contains the database which the report is querying.


There are a few good DMVs (system views) where you can find this info: sys.partition_functions and sys.partition_schemes can be joined to sys.partition_range_values to get all existing ranges. The number of ranges should be the number of partitions.

The data_space_id of the partition scheme can be joined to that of the index or heap in sys.indexes on the table (sys.tables) that you're interested in.


Truncated Incorrect date value string with comma

My guess is that you have one or more malformed values. Given the information you provided:

create table Uye_Uye_Liste ( DogumTarihi varchar(20) ) engine = innodb;
insert into Uye_Uye_Liste (DogumTarihi) values ('13.04.1996');

update Uye_Uye_Liste set DogumTarihi =  str_to_date(DogumTarihi,'%d.%m.%Y');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

alter table Uye_Uye_Liste modify DogumTarihi datetime;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

So if every row corresponds to this pattern you should be alright. I suspect that some rows does not, focus on identifying and transforming those rows.

For the row given in the original query it is sufficient to to manually truncate the row using substr:

insert into Uye_Uye_Liste (DogumTarihi) values ('23.07.2002 00:00:00');
update Uye_Uye_Liste set DogumTarihi = str_to_date(substr(DogumTarihi,1,10),'%d.%m.%Y');
select * from Uye_Uye_Liste;
+-------------+
| DogumTarihi |
+-------------+
| 1996-04-13  |
| 2002-07-23  |
+-------------+
2 rows in set (0.00 sec)

alter table Uye_Uye_Liste modify DogumTarihi datetime;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0


Unable to attach database to Amazon RDS using SQL Server Management Studio 2016

With Amazon RDS, you don't get direct access to the underlying filesystem.

If you have an existing database that you want to migrate into RDS SQL Server, rather than using the MDF/LDF files, you'll need to do a restore instead.

Amazon's instructions to restore a database are pretty good, but since you're asking this question, I'm guessing I probably need to fill you in on a background concept. Remember how I said you don't get access to the filesystem? Well, you can get access to OTHER file systems, kinda like how mapped drives work in Windows. S3 is Amazon's simple storage service where you can create buckets and put files in 'em. You're going to need to:

Create an S3 bucket
Put your database backup (not MDF/LDF) in the bucket
Give your RDS instance permission to read from that S3 bucket (that's where Amazon's instructions come in)
Use Amazon's custom stored procedure rds_restore_database to restore the backup


Why Cassandra is not considered a relational database?

Cassandra is not an RDBMS because it does not support the relational data model.

The fundamental assumption of the relational model is that all data is
represented as mathematical n-ary relations, an n-ary relation being a
subset of the Cartesian product of n domains

https://en.wikipedia.org/wiki/Relational_model
Instead, it models data as a key-value store, where the values are rows. But there's no requirement that all the "rows" in a "table" have the same "columns", as is the case in the relational model.
From Wikipedia:

Unlike a table in an RDBMS, different rows in the same column family
do not have to share the same set of columns, and a column may be
added to one or multiple rows at any time.[29]
Each key in Cassandra corresponds to a value which is an object. Each
key has values as columns, and columns are grouped together into sets
called column families. Thus, each key identifies a row of a variable
number of elements. These column families could be considered then as
tables. A table in Cassandra is a distributed multi dimensional map
indexed by a key. Furthermore, applications can specify the sort order
of columns within a Super Column or Simple Column family.

https://en.m.wikipedia.org/wiki/Apache_Cassandra


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Comments') AND name = 'IX_CreationDate')
CREATE NONCLUSTERED INDEX IX_CreationDate
ON dbo.Comments(CreationDate)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Posts') AND name = 'IX_PostID_CreationDate_Title')
CREATE NONCLUSTERED INDEX IX_PostID_CreationDate_Title
ON dbo.Posts(Id, CreationDate,Title)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Comments') AND name = 'IX_PostID_Text')
CREATE NONCLUSTERED INDEX IX_PostID_Text
ON dbo.Comments(PostId)
INCLUDE(Text)
GO

WITH BASE AS
(

SELECT
    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 0 AND 59 ) AS WithinHour,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 60 AND 1440) AS WithinDay,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 1441 AND 10080) AS WithinWeek,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 10081 AND 44640) AS WithinMonth,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 44641 AND 132480) AS Within3Month,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 132481 AND 525600) AS WithinYear,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) BETWEEN 525601 AND 7884000) AS OverAYear,

    (SELECT COUNT(*)
    FROM Posts T1
    LEFT JOIN Comments T2 ON T1.Id = T2.PostId
    WHERE T2.CreationDate = (SELECT MIN(T3.CreationDate)
                                FROM Comments T3
                                WHERE T3.PostId = T1.Id)
    AND DATEDIFF(minute, T1.CreationDate, T2.CreationDate) > -1) AS Total
)

,

ResponseTime AS (

SELECT
    WithinHour,
    CAST(CAST(WithinHour AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinHourPerc,
    WithinDay,
    CAST(CAST(WithinDay AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinDayPerc,
    WithinWeek,
    CAST(CAST(WithinWeek AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinWeekPerc,
    WithinMonth,
    CAST(CAST(WithinMonth AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinMonthPerc,
    Within3Month,
    CAST(CAST(Within3Month AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS Within3MonthPerc,
    WithinYear,
    CAST(CAST(WithinYear AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS WithinYearPerc,
    OverAYear,
    CAST(CAST(OverAYear AS numeric(10,2)) / CAST(Total AS numeric(10,2)) * 100 AS numeric(5,2)) AS OverAYearPerc,
    Total

FROM BASE

)

SELECT *
FROM ResponseTime


Yes, separating data files and logs files will help to reduce I/O.
The best practices for SQL Server are separated log, data, temp, backup files on several disks.
Example :

data db files (.mdf) : drive D
log db files (.ldf) : drive L
tempDB files (.mdf & .ldf) : drive T
backup files (.bak) : drive B

You can also improve performances by separating tempDB in x differents files (on x differents drives) where x is the number of your CPU cores.
Use SSD drives for tempDB files and db log files. Put indexes in different file on another drive.

Be aware that if you're using virtualized environment you need physically separated disks, or where your vdisks have fixed IOPs throttles vdisks with their own IOPs quota, to have full advantage.

  Placing both data AND log files on the same device can cause contention for that device, resulting in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files.

https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/place-data-and-log-files-on-separate-drives?view=sql-server-2017

https://searchsqlserver.techtarget.com/tip/Optimize-disk-configuration-in-SQL-Server

Don't forget to perform maintenance tasks as rebuild/reorganise indexes...


Configure second SQL Server Alias for named instance

In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Aliases, and then click New Alias.

In the Alias Name box, type the name of the alias. Client applications use this name when they connect.

In the Server box, type the name or IP address of a server. For a named instance append the instance name.

In the Protocol box, select the protocol used for this alias. Selecting a protocol, changes the title of the optional properties box to Port No, Pipe Name, or Connection String.

http://msdn.microsoft.com/en-us/library/ms190445.aspx


Only "grant usage", but can still select, drop, create?

Most likely, your user5 has database (i.e. schema) privileges, just not server-wide privileges.  So look here.

USE app_db;
SHOW GRANTS FOR user5;

or check in the data dictionary for the same:

SELECT *
FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
WHERE GRANTEE LIKE '%user5%';

if not anything, then maybe also check

SELECT *
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE LIKE '%user5%';


Incorrect process utilization from System health extended events?

Values for disk and processor % counters that go over 100% Should be divided by the number of processors or disks present in the system.  So 100% means it is using 100% of one processor. 250% would mean it's using two and half processors. So if it's reporting 120%, see if that is the equivalent of 120 / <number of processors>.

Note that the latest change to tigertools was June 2018. You might check out sqlwatch.io as it appears to be a much more active project.

See social.msdn.microsoft.com/Forums/sqlserver/en-US/… and just change it to TOP(1) to get the current SQL CPU usage.

– Tony Hinkle


Shrinking will probably take quite a while

It will definitely with that size of data, unless by some fluke most of the free space is at the end of the file.

  and I am not sure what will happen if something goes wrong

The operation should be safe even if you experience a complete power-out and the server shuts down uncleanly, pages are copied then relinked as needed, then the old pages unallocated, in an atomic fashion.

  and if it's OK to do backups in the middle of shrinking.

You can take backups during a shrink, though they will take longer as there will be extra IO contention. You could stop the shrink operation and restart it after the backups are complete - it should pick up (more or less) where it left off.

Another option is to shrink in small increments when the server is otherwise quiet so that it doesn't conflict with your users either (i.e. if most of your user activity is between 0800UTC & 1900UTC and your full/diff backups and other maintenance like ETL processes usually happen between 2200 & 2330, perhaps running partial shrinks between 0100 & 0700 would be best. If your app has global reach then this is less useful as you won't have quite the same distinct busy/quiet periods.

Don't shrunk right down to the data size though - leave yourself enough space for some months worth of expected data growth. No point fragmenting the data more than you need to as the space saved will just get reclaimed soon enough.


Locking issue with concurrent DELETE / INSERT in PostgreSQL

Mat and Erwin are both right, and I'm only adding another answer to further expand on what they said in a way which won't fit in a comment. Since their answers don't seem to satisfy everyone, and there was a suggestion that PostgreSQL developers should be consulted, and I am one, I will elaborate.

The important point here is that under the SQL standard, within a transaction running at the READ COMMITTED transaction isolation level, the restriction is that the work of uncommitted transactions must not be visible. When the work of committed transactions becomes visible is implementation-dependent. What you are pointing out is a difference in how two products have chosen to implement that. Neither implementation is violating the requirements of the standard.

Here's what happens within PostgreSQL, in detail:

S1-1 runs (1 row deleted)

The old row is left in place, because S1 might still roll back, but S1 now holds a lock on the row so that any other session attempting to modify the row will wait to see whether S1 commits or rolls back. Any reads of the table can still see the old row, unless they attempt to lock it with SELECT FOR UPDATE or SELECT FOR SHARE.

S2-1 runs (but is blocked since S1 has a write lock)

S2 now has to wait to see the outcome of S1. If S1 were to roll back rather than commit, S2 would delete the row. Note that if S1 inserted a new version before rolling back, the new version would never have been there from the perspective of any other transaction, nor would the old version have been deleted from the perspective of any other transaction.

S1-2 runs (1 row inserted)

This row is independent of the old one. If there had been an update of the row with id = 1, the old and new versions would be related, and S2 could delete the updated version of the row when it became unblocked. That a new row happens to have the same values as some row that existed in the past doesn't make it the same as an updated version of that row.

S1-3 runs, releasing the write lock

So S1's changes are persisted. One row is gone. One row has been added.

S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???

What happens internally, is that there is a pointer from one version of a row to the next version of that same row if it is updated. If the row is deleted, there is no next version. When a READ COMMITTED transaction awakens from a block on a write conflict, it follows that update chain to the end; if the row has not been deleted and if it still meets the selection criteria of the query it will be processed. This row has been deleted, so S2's query moves on.

S2 may or may not get to the new row during its scan of the table. If it does, it will see that the new row was created after S2's DELETE statement started, and so is not part of the set of rows visible to it.

If PostgreSQL were to restart S2's entire DELETE statement from the beginning with a new snapshot, it would behave the same as SQL Server. The PostgreSQL community has not chosen to do that for performance reasons. In this simple case you would never notice the difference in performance, but if you were ten million rows into a DELETE when you got blocked, you certainly would. There's trade-off here where PostgreSQL has chosen performance, since the faster version still complies with the requirements of the standard.

S2-2 runs, reports a unique key constraint violation

Of course, the row already exists. This is the least surprising part of the picture.

While there is some surprising behavior here, everything is in conformance with the SQL standard and within bounds of what is "implementation-specific" according to the standard. It certainly can be surprising if you are assuming that some other implementation's behavior will be present in all implementations, but PostgreSQL tries very hard to avoid serialization failures in the READ COMMITTED isolation level, and allows some behaviors that differ from other products in order to achieve that.

Now, personally I'm not a big fan of the READ COMMITTED transaction isolation level in any product's implementation. They all allow race conditions to create surprising behaviors from a transactional point of view. Once someone becomes accustomed to the weird behaviors allowed by one product, they tend to consider that "normal" and the trade-offs chosen by another product odd. But every product has to make some sort of trade-off for any mode not actually implemented as SERIALIZABLE. Where PostgreSQL developers have chosen to draw the line in READ COMMITTED is to minimizing blocking (reads don't block writes and writes don't block reads) and to minimize the chance of serialization failures.

The standard requires that SERIALIZABLE transactions be the default, but most products don't do that because it causes a performance hit over the more lax transaction isolation levels. Some products don't even provide truly serializable transactions when SERIALIZABLE is chosen -- most notably Oracle and versions of PostgreSQL prior to 9.1. But using truly SERIALIZABLE transactions is the only way to avoid surprising effects from race conditions, and SERIALIZABLE transactions always must either block to avoid the race conditions or roll back some transactions to avoid a developing race condition. The most common implementation of SERIALIZABLE transactions is Strict Two-Phase Locking (S2PL) which has both blocking and serialization failures (in the form of deadlocks).

Full disclosure: I worked with Dan Ports of MIT to add truly serializable transactions to PostgreSQL version 9.1 using a new technique called Serializable Snapshot Isolation.


SQL Server 2008: The database [dbName] is not accessible

Typically this is caused when the user's default database is set to a database they don't have permissions in, or it's offline.

For example, say I set you up a login in the Sales database, and then I right-click on your login in SSMS and change your default database to master - but you don't have any permissions in Master. When you try to connect, even though you want the Sales database, you'll go into Master by default, and get this error.

This can also happen if someone takes away your permissions in Sales and doesn't change your default database to something else.

This is why I love setting all users' default databases to TempDB. User databases can come and go, but TempDB will always be there. Plus if somebody accidentally creates an object, at least it's in a throwaway database instead of something like Master or Sales.


Does having no brackets at "WHERE" produce same results as having brackets?

In the order of operations, AND is always evaluated first. So in this case it is the same as:

WHERE (`document`.`user_id` = 4358 AND document.status = 4) OR document_info.status > 0

It is always best practice to just use parentheses anyways. Even if the logic works out the way you want it to, it is much easier to read if you use them (and also properly placed indentation).


It is possible that the number of rows exceed the value of PLS_INTEGER. Here is the quote from Oracle on %ROWCOUNT.

  %ROWCOUNT Attribute: How Many Rows Were Fetched? %ROWCOUNT returns:
      Zero after the explicit cursor is opened but before the first fetch
      Otherwise, the number of rows fetched (a PLS_INTEGER)
      Note:
      If the number of rows exceeds the maximum value for a PLS_INTEGER, then SQL%ROWCOUNT returns a negative value. For information about
  PLS_INTEGER, see "PLS_INTEGER and BINARY_INTEGER Data Types".


Will transactional replication break if there is a heavy update/delete query run on the publisher DB

Sounds like you were attempting to perform the delete operation in a single transaction.  This being the case, the answer is "Yes, it will most likely break."

Even without replication, it's a bad idea to do such a large operation in a single batch.  You would be better off looping a smaller number of deletes - it's faster and less of a performance issue.  If you do this, the answer is, "No, it shouldn't be a problem."

Here's an example

Declare @keepGoing bit
set @keepGoing = 1

while @keepGoing =1
begin

    if (select top 1 from someTable) is null
        set @keepGoing = 0
    else
        delete sometable where ID in (select top 10000 ID from sometable)

end


Moving postgresql data to different drive

The following should do it:

Shut down PostgreSQL
Make sure PostgreSQL does not run any longer
Check that PostgreSQL is really stopped
Copy the old data directory to the new drive This is usually defined through a commandline parameter  (-D) for your service or through the PGDATA environment variable.
Update your PostgreSQL configuration (service, environment var) to point to the new data directory.
If you copied the data, rename the old data directory to make sure PostgreSQL cannot find it any longer. That way you'll get an error message if you didn't get the configuration right.
Start the service
If everything is running fine, you can delete the old directory.


Why am I getting the error "non-boolean type specified in a context where a condition is expected" for this request?

SQL Server doesn't support a multi-column IN clause (unlike Oracle).

delete
from constitue
where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers

This can be rewritten as a EXISTS:

DELETE c
FROM constitue c
WHERE EXISTS
    (SELECT 1 FROM @num_dossiers nd WHERE n.num_dossier = c.num_dossier AND n.indice = c.indice);


whitelist for allowed user queries in postgres

Looking for a way to limited a user to a specific set of queries (i.e. a whitelist).

Wrap all of your queries in a plpgsql function that takes a SQL command and executes dynamic SQL.

SELECT * FROM mystupidwrapper('SELECT * FROM foo');

It will have to return a specific type, SETOF jsonb sounds great. You can write mystupidwrapper in perl, or plv8 and it'll even be trusted. If you want to write it in plperl, you can even use SQL::Statement::Structure to do basic SQL Statement processing. That said, SQL is a query language it provides READ permissions on both table and columns but the goal is still a declarative language that provides the ability to query abstractly.

Perhaps you want something more like pg-promise which natively supports a QueryFile


Should I add it as an additional column or a seperate table?

from your question i understand that you have a "One to many" relationship between The Division to the Regions,

the best way with this kind of relational data from my experience is to create a Division table and a Region table that holds the DivisionID.

that will also use less storage space because you won't need to write the division name in string, you can reference the int ID column value that uses less space than the string(char/varchar etc.)
:)
example

    CREATE TABLE  dbo.Division
(
    [ID] [int] NOT NULL IDENTITY(1,1) CONSTRAINT PK_Division PRIMARY KEY,
    [Name]  NVARCHAR(255) NOT NULL
)

CREATE TABLE  dbo.Region
(
    [ID] [int] NOT NULL IDENTITY(1,1) CONSTRAINT PK_Region PRIMARY KEY,
    [Name]  NVARCHAR(255) NOT NULL,
    DivisionID INT CONSTRAINT FK_DivisionRegion FOREIGN KEY REFERENCES dbo.Division (ID)
)

shimon.


Single query to return counts over different IDs in a single record

Similar to @ypercubes but to get one row without 3 separate queries

select
     count(CASE WHEN pub_id = '1389' THEN title_id END) as algodata,
     count(CASE WHEN pub_id = '0877' THEN title_id END) as binnet,
     count(CASE WHEN pub_id = '0736' THEN title_id END) as newmoon
from titles
where pub_id IN ('1389', '0877', '0736')

Also, decide if your values are numbers or strings and be consistent to avoid datatype conversions


Sleeping processes take up virtually no resources.  I think I have seen 100, with no obvious detriment.


Alternative to Mysql that handles increment/decrement with new or existing keys

I'm not sure I fully understand the use-case, but in MySQL you can use:

    INSERT INTO balances (address, balance) VALUES (12, 15)
    ON DUPLICATE KEY
    UPDATE balance = balance + 15;

... so you may not need a specialized DBMS for this sort of thing.

This type of statement is known as 'merge' (as in the SQL standard) or 'upsert'. See this Wikipedia article for more info, including details about the implementation in other relational database systems.


Select Column Values from all the tables of database ORACLE

You can do something like this:

select table_name,
       extractvalue(
          xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name||' where campus_id = 1')),
         '/ROWSET/ROW/C'
       ) as count
from user_tables ut
where exists (select *
              from user_tab_columns c
              where c.table_name = ut.table_name
                and c.column_name = 'CAMPUS_ID');

dbms_xmlgen.getxml runs the passed SQL statement and returns the result as an XML value. extracvalue() is then used to extract the actual rowcount.

This will return the table name and the count of rows in that table where campus_id = 1


Truncate tables with dependent foreign key constraints

You cannot truncate a table that has foreign key constraints. I would script your truncate statement to drop the constraints, then truncate the table, and then re-create the constraints.

Because TRUNCATE TABLE is a DDL command, it cannot check to see whether the records in the table are being referenced by a record in the child table.
This is why DELETE works and TRUNCATE TABLE doesn't: because the database is able to make sure that it isn't being referenced by another record.

Reference by SO user Michael
One more reference by SO John Rudy
As per your reference to truncate cascade, there is a delete cascade or update that is based on constraints. This is not the same as a truncate. Here is a link with some information regarding that.
In regards to your link, all it is doing is running a script that creates statements you can copy and paste, then run on your database. If you wanted it to become more automated with your truncate; you would need to find a way to store the create statements, run the drop constraint statements, truncate your table, then pull back and run the create statements.
Dynamic SQL is likely your best bet if this had to be automated. I'd look into another solution though if this were the case. Perhaps partition swapping or something else, but we'd need a lot more information to dig into other suggestions for an automated best practice process.


Changing mysql root password on Windows after removing mysqld-nt.exe

I got that work using otherwise a much simpler way:

sign in normally to mysql on command line or using the MySQL workbench;

mysql -u root -p

Use mysql database

use mysql;

update user password using simple update query.

update user set password=PASSWORD("NEWPASSWORD") where User='root' and Host = 'localhost';

then flush:

flush privileges;

Done!


According the error message posted in the comments this is a faulty disk that cannot serve write requests (at a certain location).


What is the generally accepted method and tools for profiling MySQL queries?

The first tool is the EXPLAIN that reports the query execution plan supposed by SQL engine. That plan contains the sequence of subselects, available and chosen indices for each, the number of rows selected in each subselect, as well as mechanism chosen to perform each subselect.

Also mysql have a nice profiling tool that can help to localize the problem:

SET profiling=1;
SELECT . . . . .
SHOW PROFILE;
SET profiling=0;


Is there a way to prevent a database from being deleted from the SSMS GUI?

You cannot prevent it directly from the GUI, as far as I know.  You can create a Server-scoped trigger to monitor for DROP_DATABASE events and throw an error to the user if invoked.

USE [master]
GO
CREATE DATABASE [CannotDropMe];
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    RAISERROR('You cannot drop databases on this server.', 16,1);
    ROLLBACK;
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO

-- DB still exists
SELECT * FROM sys.databases WHERE name = 'CannotDropMe';
GO

Also if you want to log the statements (attempted DROP DATABASE) along with any other info you want, you can alter the trigger to something like this.

/* If logging we need a log table */
CREATE TABLE [SomeDB].dbo.[DropDatabaseLog] (Command VARCHAR(100), DBUser NVARCHAR(100), ServerName NVARCHAR(100), EventTime DATETIME DEFAULT(GETDATE()));
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    DECLARE @CommandText NVARCHAR(MAX);
    SELECT @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
    ROLLBACK;

    SET NOCOUNT ON;
    INSERT INTO [SomeDB].dbo.[DropDatabaseLog] (Command, DBUser, ServerName)
    SELECT @CommandText,
        CURRENT_USER,
        @@SERVERNAME
    RAISERROR('You cannot drop databases on this server.', 16,1);
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO

SELECT * FROM [DropDatabaseLog];

The database(s) will still exist, and you get some info on potentially who to yell at :)

Edit:  I blindly answered this without seeing Shaulinator's answer and links.  Check out both links, the first shows how you can filter on specific databases, which may be useful.


Combining data from 3 tables depending values stored in 1 table

I splitted the problem into two basic SELECTs, one for members and the another one for guests and I join both later.
MySql's GROUP_CONCAT aggregate funtion is very useful here too.

http://sqlfiddle.com/#!9/58786/1

SELECT
TT1.day as day
,TT1.booking_id as booking_id
,TT1.member_id as member_id
,CASE
  WHEN TT2.guest_ids IS NULL
  THEN 0
  ELSE TT2.guest_ids
  END as guest_id
,TT1.start_date as start_date
,TT1.end_date as end_date
,CASE
  WHEN TT2.guest_ids IS NULL
  THEN TT1.member_name
  ELSE CONCAT(TT1.member_name, ', ', TT2.guests_name)
  END as names
FROM
(
SELECT
Min(day) as day
, booking_id
, member_id
, Members.id
, CONCAT(first_name,' ',last_name) as member_name
, start_date
, end_date
FROM Bookings
INNER JOIN Members
ON Bookings.member_id = Members.id
WHERE member_id <> 0
GROUP BY booking_id, member_id, guest_id, start_date, end_date
) AS TT1
LEFT JOIN
(
SELECT
booking_id
, GROUP_CONCAT(guest_id SEPARATOR ', ') as guest_ids
, GROUP_CONCAT(CONCAT(first_name, ' ', last_name) SEPARATOR ', ') as guests_name
FROM
(
SELECT
booking_id
, guest_id
FROM Bookings
WHERE guest_id <> 0
GROUP BY booking_id, guest_id
) AS T1
INNER JOIN Guests
ON T1.guest_id = Guests.id
GROUP BY booking_id
) AS TT2
ON TT1.booking_id = TT2.booking_id
ORDER BY TT1.day, TT1.booking_id


How do you correct a corrupted index in Oracle?

There is a command line utility dbv which can scan a datafile a will report and mark corrupted blocks. If you want for "fix" it you can either use RMAN block recover option - if you run Ent. Edition and if you have RMAN backup.

Otherwise search for document named "How to format corrupted block" on Metalink. This describes a situation when a table block is corrupted but it is applicable for indexes too.
First of all you mast mark the block as corrupted.

PS: the is also an SQL comment "ALTER TABLE  VALIDATE STRUCTURE CASCADE", this will also generate a report, but will not fix anything.


Try the below:

Add below 2 lines in my.cnf

skip-grant-tables
skip-networking

Restart mysql service

service mysqld restart

Note: if you don't have a service, you can do from /etc/init.d/
Connect mysql:

mysql

Update root password:

UPDATE mysql.user
SET PASSWORD=PASSWORD('MyNewPass')
WHERE USER='root';

flush privileges";
exit;

Remove newly added lines from my.cnf and restart the mysql service.

Permission errors mean you don't have admin permission where you are trying to do the task, so login with admin login first.


How to query row containing the given element inside a JSON column in Laravel PostgreSQL

Select id, a from (SELECT Id,
  json_array_elements(r.agents) A
   FROM Cnms_rosters r) ra
   Where A::TEXT = '13';

See http://sqlfiddle.com/#!17/eb400/19


SQL Server on SAN, same LUN: one logical drive vs multiple

In your question you say "one LUN assigned to a SQL Server VM" - what you want is one LUN assigned to the hypervisor machine that can have multiple virtual machine disks for the SQL Server stored on it.  This allows the VM to use a much larger number of threads to access the disk.
In order to get the best queue depth out of VMware, specifically, you'll need to use as many separate virtual controllers, and drives as possible.  Make sure you use the para-virtualized controller, if at all possible.  Refer to this blog from VMware.
I'd put tempdb on 2 controllers (with 4 files, spread over the two controllers/LUNs).
I'd put data on a controller, and tempdb on a controller.
Virtual controllers have their own queues (just like hardware); typically at most 32.  Pretty clearly, 32 is not a lot, so having more controllers actually increases the effective queue depth.
Clearly, if you're going after performance, there are also a lot of other settings, such as CPU and Memory reservations.
The standard warning applies here; do what you have tested as being the best solution for your system.  Since we have no idea about your actual load, I/O requirements, etc, etc, it's pretty hard for us to give you a hard-and-fast rule to follow for every installation.

VMware whitepaper on pushing I/O performance is here (warning, PDF).


Single SQL query to retrieve data in a particular order (not ASC/DESC)

You can simply do

SELECT * FROM reviews
WHERE product_id = 723
ORDER BY language_id = 4 DESC, language_id ASC;

language_id = 4 returns true or false, 1 or 0. Sort descending and you have language_id = 4 first, then all others.


This should work for you.

SELECT t.timestamp, t.deviceid, coalesce(eventcount, 0)
FROM foo
RIGHT OUTER JOIN (
  SELECT timestamp, deviceid
  FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
  CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
  CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)
) AS t
ON foo.deviceid = t.deviceid
AND foo.timestamp = t.timestamp
ORDER BY 2, 1;

In the first run we generate a table with this

SELECT timestamp, deviceid
FROM ( SELECT DISTINCT deviceid FROM foo ) AS d
CROSS JOIN ( SELECT min(timestamp), max(timestamp) FROM foo ) AS ts
CROSS JOIN LATERAL generate_series(ts.min, ts.max, '10m') AS gs(timestamp)

This table we generate looks like this

      timestamp      | deviceid
---------------------+----------
 2017-01-01 00:00:00 | AAAAA
 2017-01-01 00:10:00 | AAAAA
 2017-01-01 00:20:00 | AAAAA
 2017-01-01 00:30:00 | AAAAA
 2017-01-01 00:40:00 | AAAAA
 2017-01-01 00:50:00 | AAAAA
 2017-01-01 00:00:00 | BBBBB
 2017-01-01 00:10:00 | BBBBB
 2017-01-01 00:20:00 | BBBBB
 2017-01-01 00:30:00 | BBBBB
 2017-01-01 00:40:00 | BBBBB
 2017-01-01 00:50:00 | BBBBB
(12 rows)

It has all the time-ranges needed. Then we RIGHT-OUTER join it.

The end result looks like this

      timestamp      | deviceid | coalesce
---------------------+----------+----------
 2017-01-01 00:00:00 | AAAAA    |     1000
 2017-01-01 00:10:00 | AAAAA    |        0
 2017-01-01 00:20:00 | AAAAA    |        0
 2017-01-01 00:30:00 | AAAAA    |     1000
 2017-01-01 00:40:00 | AAAAA    |     1000
 2017-01-01 00:50:00 | AAAAA    |        0
 2017-01-01 00:00:00 | BBBBB    |     2000
 2017-01-01 00:10:00 | BBBBB    |     2000
 2017-01-01 00:20:00 | BBBBB    |        0
 2017-01-01 00:30:00 | BBBBB    |        0
 2017-01-01 00:40:00 | BBBBB    |        0
 2017-01-01 00:50:00 | BBBBB    |     2000
(12 rows)


A common approach is to use a different SSIS configuration file for each environment.

Give it the same name, but different contents (server name, local paths, any other variables) in each environment.


Can you copy the 11.2.0.4.0 rman executable from your friend or get and perform a second "dummy" installation of Oracle Database on another temporary computer/server? If so, here's how you can solve it:

Let's say you were able to copy the 11.2.0.4.0 ORACLE_HOME folder from your friend, and called it dbhome_2 (do not replace current, otherwise you can mess up things).

Using the 11.2.0.4.0 rman executable:
- startup instance in nomount and restore controlfile from backup
- mount the db
- restore the archivelogs required (recover database until cancel)

Shutdown the database, and using 11.2.0.2.0 executable:
- restore the control file again, from a 11.2.0.2.0 generated backup
- mount the db
- recover database until cancel
- open database with resetlogs

Hope it helps.


You can edit $ORACLE_HOME/sqlplus/admin/glogin.sql file and add inside your col format settings :)

[ora11g@botor ~]$ cat /u01/app/ora11g/product/11.2.0/db_1/sqlplus/admin/glogin.sql
define _editor=vi
set lines 450
set pages 100
set time on
set timi on
set serveroutput on
......


PostgreSQL/PostGIS 9.6 broke my compound index

You need to install a specific EXTENSION in your database:
CREATE EXTENSION btree_gist ;

According to PostgreSQL documentation on btree_gist:

btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, inet, and cidr.
In general, these operator classes will not outperform the equivalent standard B-tree index methods, and they lack one major feature of the standard B-tree code: the ability to enforce uniqueness. However, they provide some other features that are not available with a B-tree index, as described below. Also, these operator classes are useful when a multicolumn GiST index is needed, wherein some of the columns are of data types that are only indexable with GiST but other columns are just simple data types. Lastly, these operator classes are useful for GiST testing and as a base for developing other GiST operator classes.

(emphasis mine)
btree_gist is part of the standard (current) PostgreSQL installation, so, you don't actually need to install any files in your system.
After installing this extension, you can execute all these instructions on a clean install of PostgreSQL 9.6.2, without a glitch:
-- If there is not there, create extension PostGis as well
CREATE EXTENSION IF NOT EXISTS postgis ;

-- Create Schema `portal`
CREATE SCHEMA IF NOT EXISTS portal ;

And execute all your CREATE statements without a glitch.
CREATE TABLE portal.inventory
(
  type character varying,
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying,
  outline geography(Polygon,4326)
);

CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);

CREATE INDEX inventory_icompound_idx
  ON portal.inventory
  USING gist
  (pid, outline);

NOTE: This was also needed for version 9.2, according to comment from @Erwin Brandstetter. So, most probably, if you make a dump of the version 9.2 databse, the CREATE EXTENSION btree_gist ; statement should appear.


List all mysql records in a master table and last record in a related child table

Your WHERE-condition is reducing the output. If you need all the records from the contact table, please try something like this:

$strsql="SELECT contact.id, contact.title, contact.init, contact.first_name, contact.last_name, contact.city, contact.home_phone, contact.mobile_phone, contact.e_mail,  status.status AS status, fundsource.fundsource AS fundsource, category.category AS category,contact.score, ROUND(value/1000000,1) AS value_M, jstate.jstate AS to_do, journal.l_date AS last_activity, journal.note AS journal FROM contact

LEFT JOIN journal ON journal.con_id=contact.id AND journal.l_date=(SELECT MAX(journal.l_date) from journal WHERE journal.con_id = contact.id)
LEFT JOIN jstate ON jstate.jstate_code=journal.jstate_code
LEFT JOIN category ON category.cat_code=contact.cat_code
LEFT JOIN status ON status.st_code=contact.st_code
LEFT JOIN fundsource ON fundsource.fundsource_code=contact.fundsource_code

WHERE contact.scode=$user->scode and contact.dcode=$user->dcode AND contact.st_code !=17 AND contact.st_code !=18
GROUP BY contact.id " ;


Because the group by on empno and empname has basically no point (supposing they're unique in the table), a better way to do this would be:

select empno, empname
from emp.employee
where empmsal > (select avg(empmsal) from emp.employee)

The thing is that (select avg(empmsal) from emp.employee) is computed only once - appearing in the where condition, and unless you have an index on empmsal it would perform a table scan, and then another for the main query. That's as best as it could get.

Another way is using window functions:

with cte as
( select empno, empname,
         avg(empmsal) over () as avg_empsal
  from emp.employee
)
select empno, empname
from cte
where empmsal > avg_empmsal ;


Preserve order of array elements after join

The problem with your query is the join condition id = ANY(ancestors). Not only does it not preserve original order, it also eliminates duplicate elements in the array. (An id could match 10 elements in ancestors, it would still be picked once only.) Not sure if the logic of your query would allow duplicate elements, but if it does I am pretty sure you want to preserve all instances - you want to keep "original order" after all.
Assuming current Postgres 9.4+ for lack of information, I suggest a completely different approach:
SELECT n.entity_id, p.ancestors
FROM   tree t
JOIN   nodes n ON n.id = t.node_id
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM   unnest(t.ancestors) WITH ORDINALITY a(id, ord)
      JOIN   entity.nodes p USING (id)
      ORDER  BY ord
      ) AS ancestors
   ) p ON true;

You query only works as intended if nodes.id is defined as primary key and nodes.entity_id is unique as well. Information is missing in the question.
Normally, this simpler query without explicit ORDER BY works as well, but there are no guarantees (Postgres 9.3+)...
SELECT n.entity_id, p.ancestors
FROM   tree t
JOIN   nodes n ON n.id = t.node_id
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM   unnest(t.ancestors) id
      JOIN   entity.nodes p USING (id)
      ) AS ancestors
   ) p ON true;

You can make this safe as well. Detailed explanation:

PostgreSQL unnest() with element number

SQL Fiddle demo for Postgres 9.3.
Opional optimization
You join to entity.nodes twice - to substitute for node_id and ancestors alike. An alternative would be to fold both into one array or one set and join only once. Might be faster, but you have to test.
For these alternatives we need the ORDER BY in any case:
Add node_id to the ancestors array before we unnest ...
SELECT p.arr[1] AS entity_id, p.arr[2:2147483647] AS ancestors
FROM   tree t
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM   unnest(t.node_id || t.ancestors) WITH ORDINALITY a(id, ord)
      JOIN   entity.nodes p USING (id)
      ORDER  BY ord
      ) AS arr
   ) p ON true;

Or add node_id to the unnested elements of ancestors before we join ...
SELECT p.arr[1] AS entity_id, p.arr[2:2147483647] AS ancestors
FROM   tree t
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT p.entity_id
      FROM  (
         SELECT t.node_id AS id, 0 AS ord
         UNION ALL
         SELECT * FROM unnest(t.ancestors) WITH ORDINALITY
         ) x
      JOIN   entity.nodes p USING (id)
      ORDER  BY ord
      ) AS arr
   ) p ON true;

You did not show our CTE, this might be optimized further ...


You will typically get result #1 (Value=2).

Update locks make the queries run sequentially

The UPDATE query will place an update lock from the start, because it knows that it will update the row. This prevents another query from placing an update lock of its own, meaning that they will run sequentially.

There are different so-called isolation levels that may be implemented slightly differently in different database platforms, but to the best of my knowledge, the above reflects how it works in SQL Server.

The second scenario

Your second scenario (read-only lock, which escalates to an exclusive read-write lock) can only happen if you separate the UPDATE into two statements, and provided you have a less restrictive isolation level (i.e. not for instance SERIALIZABLE).

DECLARE @val int;

BEGIN TRANSACTION;

    SELECT @val=Value FROM [Some table] WHERE Id=1;
    UPDATE [Some table] SET Value=@val+1 WHERE Id=1;

COMMIT TRANSACTION;

Again, if you have a restrictive isolation like SERIALIZABLE, the initial SELECT statement will place an exclusive lock on the row immediately, preventing other processes from viewing or changing it. If you use, for instance, READ COMMITTED (the default level), the SELECT statement places a shared lock, so other processes can read the row as well.

In-memory OLTP (Hekaton)

If you're using in-memory tables, like @Piotr commented, the second query will fail because it doesn't wait for the first query to complete. Because of this, the row would have changed by the time the second query tries to commit its own changes, so the result will be Value=1.

For this reason, it is best practice to trap and retry data modifications in the client app when using in-memory tables.


Alternate per 2 postgresql rows, but start over if there are rows remaining

I think you need something using ROW_NUMBER() and (integer) division by 2, along the lines of this:

WITH p AS
( SELECT *, ROW_NUMBER() OVER (PARTITION BY supplier_id ORDER BY id)-1 AS rn
  FROM products
)
SELECT *
FROM p
ORDER BY rn/2, supplier_id, rn ;

Tested at SQLFiddle.


SQL Server 2017 log file growth with memory optimized table

First of all, if you use durable memory-optimized tables, everything from the memory-optimized side will be fully logged - the database recovery setting is not respected.

Next, is your 2017 install fully patched? There have been a number of fixes to the In-Memory engine since RTM.

In order for the log to be cleared of memory-optimized transactions, they must also be written to the checkpoint file pairs. If the CFPs needed to be expanded/added, and the drive/volume that hosts them can't do that, then the transaction log cannot be cleared.


SSRS Matrix Report - Count for NULL data

I changed the structure of my report and instead of Primary Key I used date-time field to count the records and that started giving me 0 where there were no DQ issues.


STRING_SPLIT with a multiple-character separator?

Well, you can always use REPLACE to add a single-character delimiter to the argument before passing it in. You just need to choose a character that is unlikely/impossible to appear in the actual data. In this example, let's say your original data uses three pipes as a delimiter; I chose a Unicode character at random to substitute:

DECLARE
  @olddelim nvarchar(32) = N'|||',
  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)

DECLARE @x nvarchar(max) = N'foo|||bar|||blat|||splunge';

SELECT * FROM STRING_SPLIT(REPLACE(@x, @olddelim, @newdelim), @newdelim);

I blogged about this in more detail here:

Dealing with the single-character delimiter in STRING_SPLIT

Addressing a comment:

  bad solution. what if original string is like 'abc||pqr|||rst||123' (dynamic and can contain anything). desired o/p is  'abc||pqr' and 'rst||123' but your solution will give 'abc' 'pqr' 'rst' '123'

Okay, let's take your input and see if my solution gets the wrong output.

DECLARE
  @olddelim nvarchar(32) = N'|||',
  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)

DECLARE @x nvarchar(max) = N'abc||pqr|||rst||123';

SELECT * FROM STRING_SPLIT(REPLACE(@x, @olddelim, @newdelim), @newdelim);

Result is:

abc||pqr
rst||123

And not what you must have assumed (but didn't test) this:

abc
pqr
rst
123

If your data is in a table, you could create a view so that you don't have to factor that expression into all of your queries.

If that doesn't work, because you might have a pencil (✏) in your data, and you can't find a single character in the 1,111,998 available Unicode characters that won't be in your data set, you'll have to skip STRING_SPLIT(), since it is hard-coded to accept a single character delimiter (separator
Is a single character expression).

Alternatives have been answered here dozens of times before, many before STRING_SPLIT() existed. Those methods still work.

I go over many alternatives, and also discuss the limitations in STRING_SPLIT(), in this series (I also discuss why you might consider not doing this in T-SQL using any method at all):

Split strings the right way – or the next best way
Splitting Strings : A Follow-Up
Splitting Strings : Now with less T-SQL
Comparing string splitting / concatenation methods
Performance Surprises and Assumptions : STRING_SPLIT()
STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
STRING_SPLIT() in SQL Server 2016 : Follow-Up #2