Seems like the most difficult part if finding out which products are sold in all stores. As with most things, there's more than one way to do this, but the following query should return all product IDs that are sold in all stores:

SELECT product_id
  FROM Product
EXCEPT -- exclude product IDs that are not sold in all stores
SELECT product_id
  FROM (-- Generate list of product IDs and the stores that do NOT sell them
        -- select all possible products and stores ...
        SELECT product_id, store_id
          FROM Product CROSS JOIN Store_location
        EXCEPT -- ... for the products actually sold by each store
        SELECT product_id, store_id
          FROM Sells
       ) Not_At_All_Stores

The sub-query takes the list of all possible products and stores, and removes the combos that represent products actually sold in the stores in question. This just leaves products that are not sold at the stores indicated.

Then, we take the full list of products, and remove any product that we (now) know are not sold in at least one store. That just leaves the products sold at all stores.

From here, it's just a matter of joining everything together. You'll wind up with a FROM clause like this:

SELECT Store_location.store_name
      ,Product.product_name
      ,Sizes.size_option
  FROM (
        SELECT product_id
          FROM Product
        EXCEPT -- exclude product IDs that are not sold in all stores
        SELECT product_id
          FROM (-- Generate list of product IDs and the stores that do NOT sell them
                -- select all possible products and stores ...
                SELECT product_id, store_id
                  FROM Product CROSS JOIN Store_location
                EXCEPT -- ... for the products actually sold by each store
                SELECT product_id, store_id
                  FROM Sells
               ) Not_At_All_Stores
       ) uprod -- for a Universally sold PRODuct
         INNER JOIN Product ON (uprod.product_id = Product.product_id)
         INNER JOIN Available_in ON (uprod.product_id = Available_in.product_id
           INNER JOIN Sizes ON (Available_in.size_id = Sizes.size_id)
         INNER JOIN Sells ON (uprod.product_id = Sells.product_id)
           INNER JOIN Store_location ON (Sells.store_id = Store_location.store_id)
 ORDER BY store_name, product_name, size_option

No WHERE clause is necessary (based on what you asked for). Note that the store information is redundant - since these products are sold at all stores, you're just getting 5 copies of the same underlying result, with one of the 5 store names prepended to each copy. The store name is much more interesting for products that aren't sold at every store.

NOTE: Depending on how your Product table is indexed, you could add the product_name column into the Not_At_All_Stores subquery, and carry it through from there. However, this does mean that we'd have to include it in our list of all possible products and stores, which would consume more memory while doing our EXCEPT operations.

UPDATE: From your latest comments, apparently this assignment requires that you use a WHERE clause, a subquery in the WHERE clause, and EXISTS.  To get there from here:

Drop uprod from the query; anything linked to uprod.product_id should link to Product.product_id instead;
Use the very first query in may answer as your sub-query;
You want the records where the product_id exists in the subquery.

Since this is a homework assignment, I'll let you actually assemble it, at least.

use the very first query in my answer as your sub_query, and look for product


Plans created several times under Forced Parameterization

Okay, this is the reason:

From Caching Mechanisms on MSDN:
You should notice that the two individual queries with their distinct constants do get cached as adhoc queries. However, these are only considered shell queries and are only cached to make it easier to find the autoparameterized version of the query if the exact same query with the same constant is reused at a later time. These shell queries do not contain the full execution plan but only a pointer to the full plan in the corresponding prepared plan.

For more in-depth technical information, see:

4.0 Query Parameterization on the SQL Programmability & API Development Team Blog
There are benefits in caching the shell query: If the same query were to be re-executed, then we would compute the hash value of the sql text of the query and find an exact match in the cache i.e. the shell query. Since this shell query points to the compiled plan, the compiled plan is executed and we are done.
If we had not cached this shell query and if the same query was re-executed then the steps followed would be slightly different: first we would compute the hash of the sql text of the query and not find an exact match in the cache. Next, the query is auto-parameterized. Now for this auto-parameterized query we will search the cache and find an exact match in the cache avoiding the need to go to the query optimizer. Finally we execute this compiled plan and are done.
Clearly there are performance gains from caching the shell query, especially for applications that re-execute the same query with the same literal values as well. Note that we do not cache insert shell queries because the probability of re-using the exact same adhoc query is low.

For more information read the whole documents (and series of posts, in the second case).


Looks like simple math. Assuming that all the ranges are of the same inclusive-exclusive type [):

SELECT some_data, int4range(lower(a), LEAST(upper(a),lower(b))) AS ab
FROM test
WHERE lower(a) < lower(b)

UNION ALL

SELECT some_data, int4range(GREATEST(lower(a),upper(b)), upper(a))
FROM test
WHERE upper(b) < upper(a)

UNION ALL

SELECT some_data, a
FROM test
WHERE b = int4range(0,0)
   OR a = int4range(0,0) ;

Tested at SQL-Fiddle.


filling a table by triggering multiple tables on insert

The trigger code you just displayed in your question needs to be manually defined on each table you intend to copy from. For tables x, y and z the answer to your question is:
CREATE TRIGGER some_name AFTER INSERT ON `x` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `y` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `z` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);

I suggest you make table m use MyISAM to prevent all potential deadlocks.
UPDATE 2013-10-15 19:39 EDT
First, define the table m  like this:
CREATE TABLE m
(
    id int(11) not null auto_increment, //PK
    name varchar(100) null,
    something_else varchar(100) default 'not important',
    primary key (id),
    unique key (name)
) ENGINE = MyISAM;

Then, make the trigger like this:
CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = 'new thing'
;

I would suggest that make something_else have the date and time as well
CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing ',NOW());
;

and maybe the table where the insert came from
CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing from x : ',NOW());
;

Give it a Try !!!


Creating Linked Server Object to same instance

I've already worked it out...
The problem was incorrectly setting the provider string to SQLNCLI.
When I removed it (leaving it empty) it connected immediately...

Linked Server: NewServer
Server Type: Other data source
Provider: Microsoft OLE DB Provider for SQL Server
Product name: {empty}
Data source: LOCALHOST\SQLEXPRESS
Provider string: {empty} (UPDATED)
Security, Login: Using the login's current security context


postgresql copy comand with date in filename

You have to use dynamic SQL, for example with a DO statement:
DO
$$DECLARE
   datestr text := to_char(current_timestamp, 'YYYY-MM-DD-HH24_MI_SS');
BEGIN
   EXECUTE format(
              'COPY '
              '(SELECT * FROM mytable) '
              'TO %L CSV HEADER',
              'C:\Users\me\mylocation\' || datestr || '.csv'
           );
END;$$;


it is absolutely correct error, You are select single column in main select, so it must return only one column for avoid collisions

for each column make a new sub-select:

                (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid) as username

                (SELECT avatarurl
                FROM   users
                WHERE  id = topicanswers.userid) as avatarur1

the same will be relate and to TOP level select:

   (SELECT (SELECT username
            FROM   users
            WHERE  id = topicanswers.userid)

IF ADD SECOND QUERY THERE - it will throw an error for TOP Level SELECT

FROM   topicanswers
WHERE  topicid = topics.id
ORDER  BY id DESC
LIMIT  1)        AS LastPost

You query should be like this :

SELECT *,
       topics.createdate AS TopicCreateDate,
       (SELECT (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost_name,

       (SELECT (SELECT avatarur1
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost_avatarur1

    FROM   topics
           INNER JOIN categories
                   ON topics.categoryid = categories.id
           INNER JOIN users
                   ON topics.userid = users.id
    ORDER  BY topics.lastupdate DESC


You have UNIQUE(end_time, instr), correct?  Then the fast query (SELECT * FROM cs.cs_15 where instr like '%X_T%' and end_time>'2016-09-22' LIMIT 0, 1000) will be performed this way.  (Confirm it by doing EXPLAIN SELECT ....)

Reach into that index at '2016-09-22'.
Scan forward, looking for instr like '%X_T%'
peel off the first 1000 that match.

Note: Apparently many of the rows have X_T, so the query ends quickly.

The slow query (SELECT * FROM cs.cs_15 where instr like '%X_T%' limit 1) will be performed this way.  (Confirm it by doing EXPLAIN SELECT ....)

No index is useful, so scan the entire table, looking for 1 row with instr like '%X_T%'.

Note: Apparently there is no such row early in the table.  That is, old end_times never had X_T.

There is not good way to speed up the slow query.  The leading wildcard in the LIKE make it useless to index that column.


Purchase Microsoft SQL Server Developer Edition for each of your SQL developers in the exact SQL edition that you need. I would personally go for SQL Server 2014 Developer Edition since service pack one is available.

UPDATE: As of Late March 2016 Microsoft SQL Server Developer Edition is now free.

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/?MC=Vstudio&MC=SQL&MC=IE&MC=HTML5&MC=JavaScript

Microsoft SQL Server Developer Edition, contains all the benefits of Microsoft SQL Server Enterprise Edition, and must be applied to each respective SQL developer within your organization that uses Developer Edition. That being said, once your developers have a SQL Server developer license, they can develop indiscriminately on as many Developer Edition SQL Servers and databases as you like with as many cores as you like.

For example, one developer with license can deploy (n...) databases to (n...) SQL Servers with (n...) cores. Another developer within your organization can access all those installations, databases, and SQL Servers provided that they, too, have a developer license.

Microsoft SQL Server and can literally save your organization thousands to tens of thousands of dollars for development servers--especially since core licensing has gone through the roof. It's a shame that many Microsoft licensing VARs are very misinformed and/or uninformed about Microsoft SQL Server Developer Edition.


Deleting large amounts of cascading records with minimal locking

I had the same issue.  Deleting in batches helps, but still tends to lock up my Percona cluster because of the delay in replicating the delete to other nodes.

In my case, most of the cascaded deletes did not exist in practice - there could in theory be records in those tables, but in practice there weren't for most of the tables.  So what I did was query the schema to find which tables the cascade could affect, then for each item to delete:

SET FOREIGN_KEY_CHECKS=0;
Query the affected tables to find if there were any affected records; if so, delete them.
Delete the main record.
SET FOREIGN_KEY_CHECKS=1;

This meant that the actual locking was very much reduced.  In my case this enabled me to delete about an order of magnitude faster.

This approach is not atomic, but won't leave the tables in a bad state if there's a crash.


Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

I'd have to assume that no matching values exist in the tables' respective primary key columns.

mysql> create database testing;
Query OK, 1 row affected (0.02 sec)

mysql> use testing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table foo (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table bar (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table baz (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into bar values (4), (5), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into bar values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>


Whether or not to create separate tables

Indexes which are not very selective are usually ignored by the query optimization engine.  If you have roughly equal numbers of A's and B's then an index on either won't help you.  If you want to filter by this type, then that part of the query will involve a scan.

However, if you don't have some more selective filter criteria on other columns, then you are looking at scans anyway, so there is no real additional penalty for keeping the A's and B's together.

Whether you keep them together or apart should depend on whether they are semantically related, not whether they have (coincidentally) identical columns.


select rest.PNR, res.TRAIN_NUMBER, res.Tr_name, res.AC_I, rest.na,rest.age
     , rest.doj, rest.seat, rest.src, rest.des, rest.class
     , case when rest.class = 'AC I' then res.FIRST_AC
            when rest.class = 'AC II' then res.SEC_AC
            when rest.class = 'AC III' then res.THIRD_AC
            when rest.class = 'Sleeper Class' then res.SL_CLASS
       end as fare
     , case when rest.class = 'AC I' then  res.AC_I
            when rest.class = 'AC II' then res.AC_II
            when rest.class = 'AC III' then res.AC_III
            when rest.class = 'Sleeper Class' then res.SL
       end as tickets
from res, rest
where rest.Tr_Num = res.TRAIN_NUMBER
order by PNR desc limit 4 ;


Modelling two dependent many-to-many relationships

Schematically:

CREATE TABLE users (id,
                    name,
                    PK (id));

CREATE TABLE user_foo (user_id,
                       foo_id,
                       PK (user_id, foo_id),
                       FK (user_id) REF users (id),
                       FK (foo_id) REF foo (id));

CREATE TABLE foo (id,
                  name,
                  ord,
                  PK (id),
                  KEY (id, ord));

CREATE TABLE foo_bar (foo_id,
                      ord AS (1) VIRTUAL,
                      bar_id,
                      PK (bar_id, foo_id),
                      FK (foo_id, ord) REF foo (id, ord),
                      FK (bar_id) REF bar (id));

CREATE TABLE bar (id,
                  name,
                  PK (id));

  One foo may also have many users so the pivot tables are necessary.

Taken into account.

  foo and bar are ManyToMany relations

Taken into account.


Basically your database has stalled because it has ran out of space to store archive logs, and needs to switch one.

To remedy, delete the generated logs (assuming you don't need them for backup and recovery purposes), then execute (as SYSDBA):

alter system archive log all;

alter system switch logfile;

If you don't need to run your database in archivelog mode (ie: you don't need to be able to do any disaster recovery & don't care if your data goes AWOL), you can turn archivelog mode off (as SYSDBA, database in MOUNT mode):

alter database noarchivelog;

alter database open;


Drop tables where the last update is over 1 week ago

All the metadata about tables are stored in the db named information_schema.

Try this:

SELECT
    TABLE_NAME
FROM
    information_schema.TABLES
WHERE
    UPDATE_TIME < (NOW()-INTERVAL 1 WEEK)

UPDATE: i forgot to mention this will get you all the tables that you need to drop, after that you can build the DROP statements.


UPDATE a column by COUNT of another table

Same query. different join syntax.

The first is old style ANSI, the latter is later ANSI. If you must pick between them, pick the latter.

I wouldn't use either of them due to the limitations of USING (can't be aliased). Instead write as:

UPDATE table1 A
INNER JOIN (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) as B
  ON B.id = A.id
SET A.Freq = B.idcount

See this Oracle answer as to why USING is not to be preferred: https://stackoverflow.com/questions/456684/mixing-using-and-on-in-oracle-ansi-join

And here is a similar discussion for SQL Server: https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax

In general, get used to writing SQL syntax in a way that is portable. While you may never port your code, you will at least be able to port your skills around to multiple database (Which is generally a cool thing to do to avoid becoming a one-trick pony).


Please keep in mind that when you run

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

This means you should run this query

SHOW GRANTS FOR CURRENT_USER();

or just

SHOW GRANTS;

to actually see your credentials. If you run,

SHOW GRANTS FOR USER();

you may not see credentials if they do not exist in mysql.user.

Just try to make the appropriate user on the MySQL Instance.

UPDATE 2013-03-05 16:51 EST

In you comment, you said

  Grants shows that 'GRANT USAGE ON . TO \'techboy\'@\'%\' IDENTIFIED BY PASSWORD \'and a character string\'' and 'GRANT ALL PRIVILEGES ON lfc_123awards.* TO \'techboy\'@\'%\' WITH GRANT OPTION'

Since you only have rights to the lfc_123awards database, please make sure you are specifying that database when you connect. If you do not set the default database at authentication, it will not let you connect at all.

Here is what I mean: Let say you trying connecting from the command line

mysql -utechbox -p -hIPofDBServer

This should fail, whereas

mysql -utechbox -p -hIPofDBServer -Dlfc_123awards

should work.


Why doesn't postgresql COPY command work with files in mapped drives

COPY runs as the PostgreSQL server, which usually runs as NETWORKSERVICE on Windows. This is a local service account that does not have access to your user account's network login credentials for shared drives.

Use the psql command \copy, which reads the file from the client application, running under your user ID and with access to your shared drives, then streams it over the client/server network connection to the postgres server.

I think PgAdmin-III has a similar CSV import command that uses COPY ... FROM STDIN like psql's \copy does, too.

If you're on a Windows AD Domain and you really need PostgreSQL to have direct access to shares, you can run the PostgreSQL server under a domain account instead. But that's usually unnecessary.


Redundancy does not mean:

Columns that have the same data type
Columns (or rows) that have the same data value (if this is coincidental)
Columns that are foreign keys that link a child table to its parent (including the same data value as FK and PK in the respective tables)

Redundancy is not about columns or rows for that matter.  Redundancy is about functional dependencies.  If you have predicates (i.e. columns) that are transitively dependent, then you have redundancy.

Hunting for redundancy is a bad approach when optimizing a logical data model.  Trying to decide what is and isn't redundant is tricky and frankly a little bit beside the point.  Focus on normalization.  This will eliminate your redundancy along with other potential problems along the way.

When you move from logical model to physical database, you can then determine whether managing some redundancy is worthwhile for some reason.


Authorization dbo maps to a user with an old machine name

It seems the database was created/restored/attached by a Windows account user that no longer exists. Change the database owner to a valid existing login using ALTER AUTHORIZATION

ALTER AUTHORIZATION ON DATABASE::YourDatabase TO SomeValidLogin;

Consider creating a login, perhaps one per database, without CONNECT permissions for database ownership purposes. That will avoid this sort of problem going forward.


Logon failed for login due to trigger execution / LOGON TRIGGER

The trigger is failing because the query that is used to set @Logon is returning more than one row.  Since @Logon is a varchar variable, an error occurs if anything more than one row is returned.

To test if the login is in the table, eliminate the @Logon parameter and use IF EXISTS to test if LoginName and the other criteria match a row in the table:

IF EXISTS (
    SELECT LoginName FROM [dba]..[MissusesAccounts_test]
    WHERE LoginName = @OriginalLogin
    AND LoginName NOT like '%WIN%'
    AND ( @AppName LIKE '%Microsoft%' OR @AppName LIKE '%PowerShell%' )
    AND @ComputerName NOT LIKE '%sql%'
)

This is also safer from a logon trigger perspective because it always returns either true or false, so it will never cause an error (unless the MissusesAccounts table is dropped or modified).

You might also want to wrap this in some error handling so that if the MissusesAccounts or LogonAccounts_test table is accidentally dropped or modified, the error is handled, you are alerted, and it won't cause all logons to fail.


Find Unique Index that does not have Unique Constraint

sys.indexes includes columns that identify if the index is unique, supports a unique constraint, or supports a primary key constraint. The query below lists unique indexes that do not support a constraint:
SELECT *
FROM sys.indexes
WHERE is_unique = 1 AND is_unique_constraint = 0 AND is_primary_key = 0;


SQL query with GROUPING SETS to get subtotals with a HAVING clause

You have to apply the "having clause"  before grouping set

SELECT Country, State, SUM(Population) AS [Total]
FROM
(
    SELECT Country, State, SUM(Population) AS [Population]
    FROM   City
    GROUP  BY Country, State
    HAVING SUM(Population) > 1000000
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )
--HAVING SUM(Population) > 1000000
ORDER BY Country, State

here it's a sample :

;with City
AS
(SELECT 'Count1' as Country,'St1' as State,'C1' as City,100 As Population Union ALL
 SELECT 'Count1', 'St1' , 'C2', 50 Union ALL
 SELECT 'Count1','St2' , 'CCC 1', 50 Union ALL
 SELECT 'Count1','St2' , 'CCC 2 ', 40
)

SELECT Country, State, SUM(Population) AS [Total]
FROM
(
    SELECT Country, State, SUM(Population) AS [Population]
    FROM   City
    GROUP  BY Country, State
    HAVING SUM(Population) > 100
)A
GROUP  BY GROUPING SETS ( ( Country, State), ( Country), () )
--HAVING SUM(Population) > 100
ORDER BY Country, State

output:

Country State   Total
                150
Count1          150
Count1  St1     150

dbfiddle here


What could be causing this rogue CPU spike on MS SQL Server running on EC2?

For a start, be aware that memory usage (if based on the simple used memory counter, which it likely is) will probably be always static - and likely (for a SQL Server that has been operating for a little while) be the same as the max memory configured for the instance (SQL Server will grab all available memory and won't release it).

It's worth just checking my guess here - is the memory used in you monitoring tool the same as the configured max server memory (it might be lower after a reboot and slowly increase to the max, dependent on usage)

The stats in your screenshot indicate memory pressure (or possibly slow I/O, but likely memory). The CPU spike isn't exactly the problem here, just a symptom of a problem.

A sudden drop in page life expectancy (the length of time a page stays in memory)
A rise in latch wait time (lots of things trying to modify the same page)
The CPU spike (thrashing pages rapidly in and out of memory because the buffer isn't large enough to hold all pages that are needed).

Seeing these counters, I'd definitely think memory pressure. The reason why can be trickier to diagnose, but the other stat on your screenshot indicates something. All of these indicators corresponded with a spike in user connections to the database, I know you mention that their was nothing unusual about site traffic, something/s were connecting to your db and doing something - a lot of things, it seems.

The reboot cleared it because it cut all of those connections. Unless you have a history somewhere else showing more in depth information about connections, you're not likely to be able to tell (from SQL server side) what was connecting and doing what - it might be worth investigating any connected apps (looking for a process or something going loopy - but I wouldn't spend too long doing so). I doubt you'll be able to ascertain with any amount of certainty exactly what caused it this time.

For the future, if this occurs again, I'd recommend seeing what is connected (and doing what) before you bounce the box (although I know sometimes the pressure is just to get things working again, but taking a quick look at connections doesn't take long).

There's a number of ways to do this but a good start would be Adam Machanic's sp_WhoIsActive (Info, Download, Brent Ozar talking about it)

Edit: A bit more
For some more performance monitoring stuff I like Brent Ozar's PerfMon best practices (It's an older post, Sir, but it checks out...). It has a nice video.

Alternate explanation
Yes, it's always possible the cause is infrastructure issues, and you can't be sure on that without separate hardware monitoring information (and even then you can't really be sure...). However, the spike in user connections corresponding to the other metrics behavior really does point to memory pressure due to a spike in activity (although, never assume it's only one problem).


Difference between isolation levels in locktable implementation

In just MySQL, one would do this:

START TRANSACTION;
SELECT @bal := balance FROM acct WHERE id=123  FOR UPDATE;
SET @bal = @bal - @amount;
UPDATE acct SET balance = @bal WHERE id = 123;
COMMIT;

The FOR UPDATE declares the intention to change the row(s) found.  This keeps other threads away -- not via deadlock, but via a delay.

I don't know what is added by lockRecord().  Maybe it is a replacement for FOR UPDATE.  If so, it is over-kill since it grabs the row for longer than the MySQL code does.

I don't think transaction_isolation_level matter, but I am not sure.

A deadlock can occur when you grab multiple rows in a transaction while another transaction is grabbing the same rows in a different order.  In some situations, the "gap" next to a row counts, and can participate in a deadlock.


How to login into mysql shell from localhost instead of '%'?

user() function shows what have your client answered when server asks "Who are you?".

Query shows that your client answers using IPv6 local address.

There is no such record in your mysql.user table. But server finds the record which client's information is matched to. current_user() function shows what account was applied by server for the authentication based on the client's info.

Simply tell your client that it must connect to server using IPv4 - in command line or in configuration file, or disable IPv6 somewhere - on the client, or on the server, or on the workstation. Or add one more user with grant privileges - 'root'@'::1' (recommended).

I don't know why your server cannot convert '::1' adddress to 'localhost' name. Maybe you have no proper record ::1 localhost in your HOSTS file?


2 Linked Servers using MSDASQL with conflicting Provider settings

couldn't figure it out so I just solved it using openquery instead of selecting from linked server! not great but time constraints needed it solved.


Database reporting more space reserved than total size

Looking at the code behind sp_spaceused, it references a system table called sys.allocation_units which stores statistics about how big each object in the database is. In particular, the total_pages and used_pages columns relate to the reserved and used disk space reported by sp_spaceused. The following query can help to pinpoint which objects are reporting the excessive reserved space:

SELECT
    o.name,
    a.total_pages * 8 / 1024. AS 'Reserved_MB',
    a.used_pages * 8 / 1024. AS 'Used_MB'
FROM sys.allocation_units a
INNER JOIN sys.partitions p
    ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.all_objects o
    ON o.object_id = p.object_id
ORDER BY a.total_pages - a.used_pages DESC;

Once a table has (or some tables have) been identified, their statistics can be updated by running one of:

-- Update the whole current database
DBCC UPDATEUSAGE(0);

-- Update the whole database
DBCC UPDATEUSAGE(DATABASE_NAME);

-- Update a single table and its indexes
DBCC UPDATEUSAGE(DATABASE_NAME, 'TABLE_NAME');

-- Update a single index within a table
DBCC UPDATEUSAGE(DATABASE_NAME, 'TABLE_NAME', 'INDEX_NAME');

-- Update the whole current database and run sp_spaceused
EXEC sp_spaceused @updateusage = 'true'

The report should now correctly report (or be closer to correctly reporting) the true reserved and used disk space for the database.

More information is available at MS Docs on DBCC UPDATEUSAGE and sp_spaceused.


Heterogeneous queries error using stored procedure

The message mentions two settings.  The first setting, ansi_nulls, is remembered from when the stored procedure was created.  Make sure it's on when you run create procedure:

set ansi_nulls on
go
create procedure YourProc as ...

The second setting, ansi_warnings, should be enabled on the connection that calls the procedure:

set ansi_warnings on
exec YourProc

Or alternatively, set ansi_warnings inside the procedure:

create procedure YourProc
as
set ansi_warnings on
...

For more information see this MSDN article:

  Stored procedures execute with the SET settings specified at execute
  time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored
  procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the
  setting specified at stored procedure creation time. If used inside a
  stored procedure, any SET setting is ignored.


"Churn" in the most common usage is the rate that existing members of a group leave the group (for example, customers of a company stop being customers- if a company has 15% annual churn, 15% of its customers leave every year).

In this context, then, it would seem that the author is saying that you have to understand the rate at which records may be deleted or otherwise removed from tables in order to understand how to best plan the maintenance of the system.


Can we use subquery in GRANT statement in postgres

No that's not possible.

The typical solution for this kind of problems is to either use dynamic SQL, or to use a query that generates the desired statement. Then spool that to a script and run the script.

To use dynamic SQL you can use a DO block

do
$body$
declare
  table_list text;
begin
  SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' )
    into table_list
  FROM information_schema.tables
  where table_schema = 'try3'
  AND table_name like 'ph1_part_03%';

  if table_list is not null then
    execute 'GRANT SELECT ON '||table_list||' TO xyz';
  end if;
end;
$body$


Pattern matching and operators
Full text search is not the right tool for pattern matching (and possibly even fuzzy, fault tolerant input). Typically, trigram-similarity search with the % operator is the superior approach here. You need to install the additional module pg_trgm once per database:

How to create an index to speed up an aggregate LIKE query on an expression?
PostgreSQL LIKE query performance variations

Possibly combine that with LIKE (also supported by trigram indexes), and text search like you had in mind. Related answer where text search and similarity are combined:

PostgreSQL FTS and Trigram-similarity Query Optimization

Overview of pattern-matching functionality in Postgres:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Database design
json is a very bad choice for your intended functionality. It is very inefficient to have to plow through huge columns holding everything in a semi-structured big string (json).
Consider normalizing your schema, which would result in superior performance (when done properly).
If you are hellbent on json, consider the new jsonb in the upcoming (and soon to be released) Postgres 9.4. More (last paragraph):

How to remove known elements from a JSON[] array in PostgreSQL?

And be sure to use functional indexes like:

How to create an index on an integer json property in postgres
Index for finding an element in a JSON array

Further optimization is likely possible. But that would require more detailed information including table definitions, cardinalities, sample data and typical queries.


Add multiples of rows till they satisfy an arbitrary sum then return the rows that do


ALTER TABLE command with no default value taking forever?

Why would it be taking so long?

You can look at the Session Wait Stats to see. eg

declare @session_id int = 121

select top 10 *
from sys.dm_exec_session_wait_stats
where session_id = @session_id
order by wait_time_ms desc

select command, wait_type, wait_time, last_wait_type, cpu_time, writes, total_elapsed_time
from sys.dm_exec_requests
where session_id = @session_id

where 121 is the session id of the session running the query.

  Why would it be taking so long if I'm not updating the existing records?

It probably wouldn't.  Changing the nullability is a change to the data type, not a constraint.  And it appears that it requires a rewrite of your table.


date aggregation for average temperatures from 2 different tables

Instead of doing a UNION how about just doing a JOIN on them?

SET @odd:='2018-10-28';  SET @doo:='2018-11-03';

SELECT D1, T1_01, T1_02, T1_03, T1_04, T1_05, T2_01, T2_02, T2_03, T2_04, T2_05
FROM
( SELECT
    DATE_FORMAT(T1.temp_data_dodania, '%Y-%m-%d') as D1,
    ROUND(avg(T1.temp_t1), 1) as T01_01,
    ROUND(avg(T1.temp_t2), 1) as T01_02,
    ROUND(avg(T1.temp_t3), 1) as T01_03,
    ROUND(avg(T1.temp_t4), 1) as T01_04,
    ROUND(avg(T1.temp_t5), 1) as T01_05,
FROM temp.temp1 T1
WHERE T1.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D1 ) A

JOIN

( SELECT
    DATE_FORMAT(T2.temp_data_dodania,'%Y-%m-%d') as D2,
    ROUND(avg(T2.temp_t1), 1) AS T2_01,
    ROUND(avg(T2.temp_t2), 1) AS T2_02,
    ROUND(avg(T2.temp_t3), 1) AS T2_03,
    ROUND(avg(T2.temp_t4), 1) AS T2_04,
    ROUND(avg(T2.temp_t5), 1) AS T2_05
 FROM temp.temp2 T2
WHERE T2.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D2 ) B
ON A.D1 = B.D2;


PostgreSQL SSD vs HDD - why is there no difference in insert performance?

I would not expect disk to be the bottleneck for this task on a reasonable machine.  All writes but one (the final commit) should be either buffered, or done in the background.  The bottleneck would be CPU.
If each row was inserted in a separate transaction and your HDD lacks a non-volatile write cache and your system is configured properly for durability, then having WAL on the SSD rather than HDD should be a lot faster due to the fsync speed.
Even with your single-statement INSERT, if you increased the size to the point that the temp files used for generate_series actually had to reach disk and be evicted from RAM, and then be read back in, that could also highlight the difference between SSD AND HDD.


Best way to create relationship between multiple tables

No, you should not have multiple State tables.  Having a single table with relationships to the others is the right way to approach this.  The perception that this somehow implies that the other tables are linked, other than they share a State, is purely in your head - such an interpretation would not be common practice.

You should distinguish between your logical data model and your physical database design.  I would recommend adding the relationships to State into the logical model.  You may choose to omit the foreign key constraints from your database for performance or other reasons.

There's a post on Dell's Toad site which I can't find just now.  It's about a 200 table DB, every one of which has a UpdateUserId column and corresponding FK relationship to the User table.  The plan for a DELETE User.. statement is a thing of beauty - a perfect left-deep triangle of 200 joins!  They would have been better without the constraint.

Edit: here's the link.  I mis-remembered the details but the principle's the same.


The problem was solved by using a new connection to execute the procedure.

I think it might help if someone is on the same situation.


I can not navigate through the objects in sql server managemente studio - lock never goes away

Someone's got an open transaction involving the system tables.
Look at sys.dm_tran_locks.
While SSMS is waiting you can examine sys.dm_os_waiting_tasks to see which session it's blocked by.


Explicit usernames in references to objects

You can use
alter session set current_schema=qa;

Then, all references to objects that aren’t qualified with a schema will be treated as if you wrote qa.object. You will still need the necessary privileges to work with the relevant object.


Does the size of the primary key contribute to table size?

SHORT ANSWER

It is really the other way around : The size of the table contributes to the size of the primary key. Therefore, YES it was to be expected.

LONG ANSWER

The Primary Key resides within gen_clust_index, a.k.a. the Clustered Index.

According to the MySQL Documentation on the Clustered Index

  How the Clustered Index Speeds Up Queries

  Accessing a row through the clustered index is fast because the row
  data is on the same page where the index search leads. If a table is
  large, the clustered index architecture often saves a disk I/O
  operation when compared to storage organizations that store row data
  using a different page from the index record. (For example, MyISAM
  uses one file for data rows and another for index records.)

From this description, the InnoDB Storage Engine has each row sits inside the Clustered Index.

In light of this, two questions need answering:

Why does dropping the PRIMARY KEY not shrink the Clustered Index?
Why does OPTIMIZE TABLE not shrink the Clustered Index?

The answer to both questions is but one. The MySQL Documentation on the Clustered Index says:

  If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB
  internally generates a hidden clustered index on a synthetic column
  containing row ID values. The rows are ordered by the ID that InnoDB
  assigns to the rows in such a table. The row ID is a 6-byte field that
  increases monotonically as new rows are inserted. Thus, the rows
  ordered by the row ID are physically in insertion order.

Think about it : OPTIMIZE TABLE is rendered ineffective since you made no attempt to delete any rows. Since you did not delete any rows, all the space occupied by rows will still sit within the Clustered Index. You merely traded-in one key (your Primary Key) for another (the 6-byte row ID).

What are the ways to shrink the Clustered Index ?

I have one of three(3) techniques you may need to do

TECHNIQUE #1 : Reducing the Length of your Columns

shrink INTs to SMALLINTs or TINYINTs
shrink VARCHAR(255) to VARCHAR(128)

TECHNIQUE #2 : Delete Old Rows

Look for rows with really old timestamps, then either

Delete the Old Rows
Archive the Old Rows in another table

TECHNIQUE #3 : Dropping Columns From the Table

This is true in theory
Nobody wants to lose data in the Process
Archive the Table Before Dropping Columns

CAVEAT

Before you apply one of these three(3) techniques, please backup your table.

EPILOGUE

Once you have applied one of these three(3) techniques, then you can do one of the following in order to shrink the Clustered Index (for let's say, mydb.mytable):

OPTIMIZE TABLE mydb.mytable;
ALTER TABLE mydb.mytable ENGINE=InnoDB;

Give it a Try !!!

UPDATE 2013-06-17 07:34 EDT

Here is your last comment

  So if the original primary key was greater than 6-bytes wouldn't I expect to see a decrease in the clustered index size?

Even if PRIMARY KEY is now CHAR(255), each row sits inside the Clustered Index. Each page in InnoDB is 16K. The 6-byte row ID (which InnoDB still uses internally anyway) is still embedded within the same pages. Dropping the PRIMARY KEY is more of a logical dropping than a physical one. That's why I said earlier

  You merely traded-in one key (your Primary Key) for another (the 6-byte row ID)

The physical act of dropping the PRIMARY KEY may entail running around to every row in tombstoning (flagging) each row not to use some internal column combination for the PRIMARY KEY but to revert back to the row ID. That would hardly change the usage of a 16K InnoDB Page, regardless of the Row Length Being Long (which may allow for a few rows within the Page) or Short (which may allow for many more rows within the Page).

Running OPTIMIZE TABLE to shrink it still won't make any noticeable change because the flagging mechanism still exists for the Clustered Index. The exact same number of rows, given the average length of your rows in the table, keeps the Clustered Index the same size.

Look over the three techniques I mentioned before and the MySQL Documentation on the Clustered Index. Given the number of rows in the table and your current table definition, if you cannot reduce column sizes, remove columns, delete rows and archive them elsewhere, then there is nothing you can do to shrink the table.


You have LIKE and ILIKE and ~ and ~*. This covers the SQL-LIKE conditionals and the more beefy and more powerful regex-like features.

SELECT
 s,
 s LIKE '%is a sentence test%' as "like",
 s ILIKE '%is a sentence test%' AS "ilike",
 s ~ '.*is a sentence test.*' AS "~",
 s ~* '.*is a sentence test.*' AS "~*"
FROM ( VALUES
 ( 'This is a test sentence' ),
 ( 'is a sentence TeST' ),
 ( 'This a IS test sentence' ),
 ( 'iS a tEst sentence tHis' ),
 ( 'This a test sentence is' )
) AS t(s);

            s            | like | ilike | ~ | ~*
-------------------------+------+-------+---+----
 This is a test sentence | f    | f     | f | f
 is a sentence TeST      | f    | t     | f | t
 This a IS test sentence | f    | f     | f | f
 iS a tEst sentence tHis | f    | f     | f | f
 This a test sentence is | f    | f     | f | f
(5 rows)

You're not using any advanced regex powers, so I'd go with LIKE, or ILIKE.


The AG listener is a virtual network name (VNN) or virtual IP (VIP).

  An availability group listener is a virtual network name (VNN) to
  which clients can connect in order to access a database in a primary
  or secondary replica of an AlwaysOn availability group.

You point applications to the listener (which is registered with DNS) and directs traffic in the AG.  So, unless all your DCs go down at the same time, your redundancy is the number of DCs/DNS servers.

  An availability group listener consists of a Domain Name System (DNS)
  listener name, listener port designation, and one or more IP
  addresses. Only the TCP protocol is supported by availability group
  listener. The DNS name of the listener must also be unique in the
  domain and in NetBIOS. When you create a new availability group
  listener it becomes a resource in a cluster with an associated virtual
  network name (VNN), virtual IP (VIP), and availability group
  dependency. A client uses DNS to resolve the VNN into multiple IP
  addresses and then tries to connect to each address, until a
  connection request succeeds or until the connection requests time out.

This MSDN article (which
i quoted from) should be a good starting point for what, exactly, an AG Listener is.

https://msdn.microsoft.com/en-us/library/hh213417.aspx


How can I get the page size of a Postgres database?

If you don't have access to pg_controldata ...

There is a simple way:

test=# SELECT current_setting('block_size');
 current_setting
-----------------
 8192

The manual:

  The following “parameters” are read-only, and are determined when
  PostgreSQL is compiled or when it is installed. [...]

  block_size (integer)

  Reports the size of a disk block. It is determined by the value of BLCKSZ when building the server. The default value is 8192 bytes. The
  meaning of some configuration variables (such as shared_buffers) is
  influenced by block_size. See Section 19.4 for information.

To verify

Create a dummy table with only 1 small row: one data page is allocated. Then check the size of the "main" relation fork with pg_relation_size()

test=# CREATE TEMP TABLE foo AS SELECT 1 AS id;
SELECT 1
test=# SELECT pg_size_pretty(pg_relation_size('pg_temp.foo'));
 pg_size_pretty
----------------
 8192 bytes
(1 row)

So the page size is 8 kB, which is hardly surprising like you mentioned. The manual:

  Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).


MongoDB, Getting Started and Connecting with Compass

Alrighty, I guess this was a noob question.

Answers can be found here as well: https://stackoverflow.com/questions/42739166/could-not-connect-to-mongodb-on-the-provided-host-and-port#49220957

Pretty sure many of their different methods would work. For some reason mongo --bind_ip_all didn't work for me and seems like the simplest. I think it has something to do with openSuse's arm version doesn't use mongod and instead just mongo.

What worked for me was going into my '/etc/mongodb.conf' file and changing the boundIP from 127.0.0.1,::1 to 0.0.0.0,::1

Not to sure what the ::1 is for but I can connect to the db with compass now.


The intent of replication is that the dataset on the Slave is always identical to what is on the Master.

But, Stored Procedures, Stored Functions, Triggers, and Events may (or may not) do things that should not be re-done on the slave.  Therefore, you must decide case by case whether you want them to be on both servers.

To apply a CREATE to all your slaves -- that should be as simple as a script that connects to each slave and performs the same query.

mysqldump is cautious about dumping such Routines, for fear that you will use the dump to initialize a Slave.

The prime example of something that should not be on the Slaves:  A Trigger that Inserts a row into another table.  And you have Row Based Replication.  The Insert is already sent to the Slave via RBR, and the Slave has no way to perform a Trigger on something in the replication stream anyway.


The syntax you used is from a SQL Server example, not from MySQL. It would be good to check the MySQL documentation about ALTER TABLE syntax.

In MySQL, the ALTER COLUMN subclause can only be used for setting or dropping the default value of the column (SET DEFAULT literal or DROP DEFAULT).

You need to use either CHANGE COLUMN (note that column name is doubled as you could use this to change its name):

ALTER TABLE MyTable
    CHANGE COLUMN comment comment BIGINT NOT NULL;

or MODIFY COLUMN:

ALTER TABLE MyTable
    MODIFY COLUMN comment BIGINT NOT NULL;

Now, there are 2 more problems, beside the syntax:

Seeing the CREATE TABLE, converting a "comment" column from TEXT to BIGINT does not make much sense. I'll assume that you want to keep it TEXT and only make it NOT NULL.
There might be NULL values already in the table. If that's the case, the statement will fail with errors. So, you have to first UPDATE those values to some non-null value (say the empty string) and then modify the column:

So our statements become:

UPDATE glpi_ticketsatisfactions
SET comment = ''
WHERE Comment IS NULL ;

ALTER TABLE glpi_ticketsatisfactions
    MODIFY COLUMN comment TEXT COLLATE utf8_unicode_ci NOT NULL ;


Problem using merge into a remote table over dblink

This is a bug. (Bug number 4311273).

  ORA-2064 using MERGE statement over a database link

... which was fixed in Oracle 10.2.0.4. Upgrade your database software.


How to write a query, with multiple foreign key in mysql

Run the script below (in order). Take a look at the image.

You don't need a transaction_fee table - this information is
already in the treatment and medical_procedure tables.

A few words of advice.

You should take a look at Open Source medical practice and/or
hospital management software. You may get some ideas there (maybe even
better than mine!) - you can pick and mix which ideas you choose to
copy/adopt/modify.

The recommendations below about naming are my preferences - the ABSOLUTELY CRITICAL thing is to have a naming convention and STICK to it!

Keep all table names lower case, that way they are easy to distinguish from SQL using upper case.

Separate words using underscore_case - i.e. order_no.

Keep table names singular - tables are collections, so a singular title is appropriate. This one can provoke (almost religious) debate - as above, just pick one (singular or plural) and stick to it. The only reason to breach this rule is for SQL keywords!

If some appointments are with a nurse, you could have a table called medical_professional with a link to a staff_table_id - didn't have time
for that.

CREATE TABLE IF NOT EXISTS `users` -- didn't pick "user" - it is a singular SQL keyword!
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) CHARACTER SET utf8 NOT NULL,
  `status` int(11) NOT NULL,
  `accessLevel` varchar(100) NOT NULL,
  `fk_id` int(11) DEFAULT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `patient`
(
  `patient_id` int(11) NOT NULL AUTO_INCREMENT,
  `fn` varchar(100) NOT NULL,
  `ln` varchar(100) NOT NULL,
  `father_name` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `sex` varchar(20) NOT NULL,
  `DOB` varchar(100) NOT NULL,
  PRIMARY KEY (`patient_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

CREATE TABLE IF NOT EXISTS doctor
-- every appointment must have a doctor (or nurse - see above)
(
  doctor_id int not null auto_increment,
  doc_name VARCHAR(50) NOT NULL,
  speciality VARCHAR(100),
  PRIMARY KEY (doctor_id)
);

CREATE TABLE treatment
(
  treatment_id INT NOT NULL AUTO_INCREMENT,
  treatment_fee INT NOT NULL,
  treatment_name VARCHAR(200),
  PRIMARY KEY (treatment_id)
);

CREATE TABLE appointment
-- an appointment must be with a patient
-- appointment_patient_ is redundant.
(
  appointment_id int NOT NULL,
  doctor_id int NOT NULL,
  patient_id int NOT NULL,
  Date int NOT NULL,
  time int,
  PRIMARY KEY (appointment_id),
  CONSTRAINT fk_ap_pa_id FOREIGN KEY (patient_id)
  REFERENCES patient(patient_id),
  CONSTRAINT fk_ap_do_id FOREIGN KEY (doctor_id)
  REFERENCES doctor (doctor_id)
);

CREATE TABLE medical_procedure  -- and not patient_treatment, English is easier.
(
  medical_procedure_id int NOT NULL,
  order_no int NOT NULL,
  patient_id int NOT NULL,
  doctor_id INT NOT NULL,  -- each treatment must have a doctor (at least supervising)
  treatment_id int NOT NULL,
  PRIMARY KEY (medical_procedure_id),
  CONSTRAINT fk_mp_pa_id FOREIGN KEY (patient_id)
  REFERENCES patient(patient_id),
  CONSTRAINT fk_mp_tr_id FOREIGN KEY (treatment_id)
  REFERENCES treatment(treatment_id),
  CONSTRAINT fk_mp_do FOREIGN KEY (doctor_id)
  REFERENCES doctor(doctor_id)
);


Safest place to download SQL Server 2005 Service Pack 2 Installer

That message is simply saying you won't need to enter a product key at installation.

The version from the MSDN downloads is the best download to use.

The SQL Server installers downloadable via MSDN subscriptions don't require at license key - the installer asks you what edition you want to install.  I'd recommend downloading the full installer for SQL Server 2005 from MSDN, installing it on a virtual machine for test purposes to see how the installer works.  The last thing you want to do is try to figure out how to install it as part of your rollback effort.


How to model a relationship with variable tables (polymorphism)?

There seem to be 2 main ways to model such a situation:

(1) Use supertype/subtypes.  However, as we are not discussing "object oriented" design, the second option may be a bit more appropriate.

(2) Use a so-called ARC, which expresses a XOR relationship.

Suppose we have an entity TRANSACTION.  Each customer can be involved in one ore more transactions AND a customer is either a COMPANY or a PERSON (XOR).
Our ERD/relational model could look something like the ones below.  Notice the "arc", that is drawn across both relationships.

As for the implementation: you can code a CHECK constraint (in order to avoid triggers), which will enforce the XOR (exclusive or).  The complete DDL code looks something like this (notice the CHECK):

CREATE TABLE company (
  c_id NUMBER PRIMARY KEY
);

CREATE TABLE person (
  p_id NUMBER PRIMARY KEY
);

CREATE TABLE transaction (
  t_id NUMBER PRIMARY KEY
, c_id NUMBER
, p_id NUMBER
);

ALTER TABLE transaction
ADD CONSTRAINT fkarc CHECK (
     ( (c_id IS NOT NULL) AND (p_id IS NULL) )
  OR ( (p_id IS NOT NULL) AND (c_id IS NULL) )
);

ALTER TABLE transaction
ADD CONSTRAINT transaction_company_fk
FOREIGN KEY ( c_id ) REFERENCES COMPANY ( c_id ) ;

ALTER TABLE transaction
ADD CONSTRAINT transaction_person_fk
FOREIGN KEY ( p_id ) REFERENCES PERSON ( p_id ) ;

(Tested on Oracle 12c)


MySQL 5.7 doesn't start after installation on CentOS 6.7

I faced the same issue myself and I successfully resolved it.
What needs to be done is:
Open the my.conf file in vi editor and add a line under [mysqld]:

explicit_defaults_for_timestamp = 1

This will fix the first issue.
Now check the access rights of the location pointed by datadir field and make sure mysql can access it.
Worst case rename the location it points to. eg:

datadir=/var/lib/mysql1


Can data be replicated from a MySQL slave back to a master without the slave containing all the master data?

When using master/slave you do not need to replicate the all instance.
You can go up the the table with the replicate-wild-do-table parameter.

May be this could help you in what you are trying to do


PostgreSQL partial index not used depending on boolean comparison

These queries may have slightly different semantics, from the query parser point of view.

select ... where field3 IS FALSE means "evaluate expression field3 IS FALSE and return rows for which the evaluation result is TRUE". The expression matches that of your partial index, so it's a natural choice.

select ... where not field3 means "evaluate expression NOT field3 and return rows for which the evaluation result is TRUE". Here the expression does not match the partial index.

We know that these expressions are logically equivalent, particularly in the presence of the NOT NULL constraint on the column in question, but the parser (or the optimizer) apparently cannot see that.

I suspect if you change your partial index condition to WHERE NOT field3 you'll see that the plans for your two queries switch places.


Queries against read-only secondary replicas implicitly run in snapshot isolation regardless of the session isolation level or RCSI setting. This avoids blocking due to DML changes. Read-only queries still acquire schema stability locks, which will block DDL operations by redo threads and visa-versa.

SPID 129 blocks a session ID 45 ( i am sure this is not a user id) for
almost 6 hours which is dependent on spid129 and wait type is
LCK_M_SCH_M

In your case, it seems a redo thread is waiting for a schema modification lock but is blocked by a very long-running query/transaction schema stability lock. Other queries are in turn blocked by the redo thread.
Take a look at the SELECT query execution plan and duration of the transaction for remediation.


If it's a new deployment then why not upgrade to the latest version of the software? 2014 has many benefits over 2008 and 2012.

The install is simple if you have configured windows clusters before. I would recommend windows 2012R2 as clustering is a little easier with features like network teaming and the automatic quorum level settings.

One of the hardest things to get right can be the LUNs that you configure on the SAN especially if you're using SAN level snapshotting.


SQL Server Snapshot Replication and SRDF compatability

I'd check with EMC's tech support, however I seriously doubt anything at the SAN level will have an effect on SQL Server snapshots themselves.

Snapshots at the software layer are simply files that are handled just like any other file at the SAN hardware level.

Our Dell SAN has some software that integrates with SQL Server to allow you to create snapshots from SQL Server Management Studio, but again, that is a snapshot of the entire file system - not a specific database file.


Yes, there is a way, it's a very cool AWK script, which just needs the MySQL dump. Find it here - https://github.com/dumblob/mysql2sqlite


You can find sys.database_permissions for all the permissions of all objects in database including who did grant permission.


Shall i upgrade Database server from 2005 to 2012 without upgrading SSRS or web Application

Will suggest you to go through this whitepaper SQL Server 2012 Upgrade Technical Guide, to get a proper understanding on what all is needed for the activity.

There are many points which would be need to be think upon, i.e.

  Just upgrading by keeping the compatibility level at a older version is not real upgrade. Evaluate at using some of the new features to increase productivity, performance, maintainability and scalability.

As the upgrade talks happens, get started with tracking if any deprecated features are being used. There are multiple ways to do this – I highly recommend using the “ SQL Server: Deprecated Featurese” Perfmon counter to track it like a background task. As your application runs, this gives you a rough idea of how many deprecated features you might be using inside your application that might have an impact to your upgrade process. There is also a Trace Event for capturing Deprecated features with SQL Server Profiler which you can use but that will be to nail down which code is causing that perfmon spike.

Also before upgrading you should check Upgrade Advisor as a first step. This surely guides you of potential problems upfront and is one of the essential step too for any SQL Server upgrade process.

Also, you need to understand on the OS version. If you're current OS running sql server 2005 is good enough to run SQL server 2012.

Similarly, when you check for apps and SSRS, you need to test their functionality if they are designed to run and function as per the latest SQL server 2012 features.

So, before coming to conclusion you need to have a thorough testing done on test/dev/staging servers to test the above apps\reports.

Get everything documented and then layout a plan to decided a YES or NO.

Hope this helps.


How do I install pgcrypto in PostgreSQL 8.4?

PostgreSQL 9.1+

Note that I'm working on Ubuntu 12.04, which uses postgresql 9.1.

There, I needed to:

sudo apt-get install postgresql-contrib

And then in my database:

postgres@ztrustee:~$ psql test
psql (9.1.3)
Type "help" for help.
test=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION

And now I can use pgcrypto functionality, gen_random_bytes():

test=# create table test (
  id
    text
    not null
    default encode( gen_random_bytes( 32 ), 'hex' )
    primary key,
  value
    text
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
test=# \d test
                            Table "public.test"
 Column | Type |                         Modifiers
--------+------+------------------------------------------------------------
 id     | text | not null default encode(gen_random_bytes(32), 'hex'::text)
 value  | text |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

test=# insert into test (value) VALUES ('scoobydoo');
INSERT 0 1
test=# select * from test;
                                id                                |   value
------------------------------------------------------------------+-----------
 76dd5bd0120d3df797f932fbcb4f8aa5088e215ee2b920dddbff59c8595fbac7 | scoobydoo


Data Files Outgrowing Hardware - Split the File?

1) Yes

2) Yes,

After creating the new files, you'll need to drop and re-create the clustered indexes  of the target tables, moving them on to the new filegroup to move the data:

CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [filegroup_name]

3) If you can get the DBA to back you up on this solution, I think you'll be OK. Based on your limitations this seems like a perfectly reasonable move - but remember to backup before trying to move the data!


Query to generate a new column containing the value of the "previous" column

For version 5.x

SELECT id,
       CASE WHEN @item = item
            THEN @value
            ELSE @value:=NULL
       END prev,
       @item:=item,
       @value:=current
FROM tablename, (SELECT @item:='', @value:=0) variables
ORDER BY item, id

The query firstly processes all item1 in the order of id growth, and then processes all item2 (due to ORDER BY expression). CASE in prev's expression checks the moment when item1 finished and item2 started, and resets the variable to NULL (because first record for item have no previous record).

For version 8.x

SELECT id,
       item,
       current,
       LAG(current) OVER (PARTITION BY item
                          ORDER BY id) prev
FROM tablename

PS. Column name `#` replaced with `item`.

PPS. If you need in final sorting by `id` then wrap the query with proper ordering outer query (for 5+) or add ordering clause (for 8+).


In Oracle multi-tenant architecture, is a PDB a container?

Well yes, a PDB is a container for the database objects of this PDB. However container in this context only means that it contains something (schema objects, not PDBs). This is most likely why the official names/descriptions in your listing are not named containers, even if they are. It is not (like the System or Application Container) a „housing“ for a PDB. (And to make things more confusing I would think of the Application Container more like a collection, not a container)


The solution to this issue was as follows:
Starting with the secondary:

Inside of Mongo I ran the following:

db.shutdownServer()
exit

On the command line:

rm -rf /var/lib/mongodb/*
sudo service mongodb start

It then takes a couple of hours to resync. Once resynced I did the following on the primary:

rs.status() //To make sure that the everything was synced
rs.stepDown(9200)
db.shutdownServer()
exit

On the command line I ran:

rm -rf /var/lib/mongodb/*
sudo service mongodb start

Then in Mongo I ran:

rs.status() //To make sure that everything was synced
rs.freeze(0) //To remove the re.stepDown(9200) lock

On the secondary I ran:

rs.stepDown()

Note: If possible, back up all of your data first.


With MyISAM, you can use the concurrent_insert=2 optimization, where an INSERT will not block existing or new SELECT statements (but will block anything else).

With InnoDB, the rules are:

SELECTs never block
An UPDATE or DELETE on a row will place
a lock on said row. But this can work even while the same row is
being SELECTed.
A second update (on concurrent transaction) on
same row will block, until the first is committed or rolled back.
INSERTs are more complicated: if the table has an AUTO_INCREMENT, then that is locked (though other concurrent operations are allowed if not competing for AUTO_INCREMENT)
Some concurrent INSERTs may block one another because locking is done over the clustered index.
DELETEs, UPDATEs on ranges are also more complex and may involve firther blocks due to locks on clustered index.

This is probably not a thorough list, but should get you the general picture.


How to store LOB-image or PDF in Postgresql and what is the impact of having in Postgresql?

BYTEA is a good choice here.

The BYTEA data type allows storage of binary strings.

It stores a LOB within the table, respectively using TOAST.
It is thus limited to 1 GB
The storage is octal and allows non printable characters (in contrast to character strings which don't).
The input/output format is HEX (as of PostgreSQL 9.0).
Notes:

BYTEA comes close to the SQL standard binary string type 'BLOB'. The functions and operators provided by BYTEA are mostly the same, while HEX input format of BYTEA is different.
BYTEA is slower for lengths >20 MB than the LO facility (it has no random accees).

More details can be found here


I Need to Shrink My Database - I just freed a lot of space

Some Initial Caveats:

It is generally known as a worst practice to ever shrink a production database or data file (Log files are another issue as this question talks about). I advise people to not shrink their databases in blog posts like this where I talk about "right-sizing" and good planning. I'm not alone there (Paul Randal, Brent Ozar, just to provide a couple more links). Shrinking a data file or database fragments indexes, is slow and laborious on your resources, can be a drain on your system and is just a bad thing to do, generally
In this case, we all know the risk is there, we are prepared to deal with it, but we freed a lot of space that we know we'll never ever need again. So in this specific type of case - shrinking makes a lot of sense as one of our options.

If you've read about the concerns and risks and you still need to do this shrink because you freed a significant amount of space, hopefully the rest of this answer will help you out. But do consider the risks.

There are two main approaches two consider here:

1.) Shrink Yes, do the actual shrink - Consider using DBCC SHRINKFILE instead of DBCC SHRINKDATABASE, you have more control over what gets shrunk and how. This will cause some performance degradation for sure - it is a large operation doing a lot of IO. You can potentially get away with repeated shrinks to a target size that gets progressively smaller.

This is the "A.)" example in the above DBCC SHRINKFILE link.. A datafile is being shrunk to 7MB target size in this example. This format is a good way to shrink repeatedly as your downtime window allows. I would do this in testing on development to see how the performance looks and how low/high you can go of an increment and to determine the expected timing in production. This is an online operation - you can run it with users in the system accessing the database being shrunk, but there will be performance degradation, almost guaranteed. So monitor and watch and see what you are doing to the server, pick a downtime window or period of lighter activity, ideally.

USE YourDatabase;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Always Remember: - every time you shrink you fragment your indexes and should do an index rebuild if you are going to shrink in chunks over a prolonged period of time. You are now incurring that cost each time if you can't get it done all in one window.

2.) New Database - You could create a new database and migrate data to it. You would have to script the empty database out and all of it's keys, indexes, objects, procs, functions, etc. and then migrate data to it. You could write scripts for this or you could use a tool like SQL Data Compare from Red Gate or other vendors with similar tools. This is more setup work on your side, more development and testing, and depending on your environment may also blow out your downtime window also but an option to consider.

When I am forced to shrink a Database
If this were my environment, I'd look to leave a fair/hefty amount of white space in the data file because I like being a disk hog and like to be prepared for future/unexpected growth. So I would be okay giving space back if we just deleted a majority of the space, but I'd never trust those saying "but it will never grow again" and still leave some white space. The route I'd probably go with (sigh) is the shrink approach if I had smaller downtime windows and didn't want to incur the complexity of creating an empty DB and migrating data to it. So I would shrink it a bunch of times incrementally (based on how many times I thought I needed to based on my testing in dev and the desired size. progressively choosing a smaller file size)and then rebuild the indexes.. And then I'd never tell any one that I shrank my database ;-)


postgres fetch record from partitions without the check constraints

A few details are still not clear, but let's see, what we can do now.
First, having about 100M rows and 231 partitions sounds not that good.  The resulting tables will be too small, in turn their number too high - I cannot tell the threshold, but at some point the query planning migt get too expensive.  I think it is quite possible that yearly partitions would be enough.  Alternatively, if you really want to fetch a whole month at a time, create monthly partitions.

Now to the actual problem.

It is not quite clear to me why you have rows in the parent table.  The usual way of partitioning is that the parent is empty, and every row is redirected to one of the children.

At the same time, is you have an index on posted_at of the parent table (as you have it on the children), finding rows in the parent based on the timestamp is easy.

On the other hand, while I'm not sure which column shared_parent_id refers to, you can define an index on it, too - looking rows up based on this will be easy, too.

The only thing still has to be added is tell your query to look for parents in the parent table only.  Let's have a look at a possible query:

WITH child_messages AS (
    SELECT shared_parent_id, {other interesting columns}
      FROM messages
     WHERE posted_at {matches your needs}
)
SELECT *
  FROM child_messages

UNION ALL

SELECT shared_parent_id, {other interesting columns}
  FROM ONLY messages -- this way it does not go to the children
 WHERE {unclear column} IN (SELECT shared_parent_id FROM child_messages);

The WITH query may pick up rows from the parent, too - this you may or may not want, adjust the query accordingly.

Furthermore, the performance might not be ideal, in this case there is room for tweaking the query (eg. a JOIN instead of the IN(), pushing the query in the WITH clause into a (sub)query and so on).

And a final notice: varchar(255) is usually a sign of a value of unknown-before length - if you really want to constrain it, you may want to choose a meaningful limit.  Otherwise, an unlimited varchar (or text) has a slight performance advantage in PostgreSQL over the limited ones.  Furthermore, from your example it seems that shared_parent_id is a number (integer) - use the best fitting type.


You can use status to set groups, and then get min and max time of each group.
If there is a serial id (PK), and it can be used to set an order, maybe you can get a better performance.
Due each intermediate status=0 belongs to two groups, I've added a new column with the time of the next row to get max(time).
with ev as
(
  select
      time, status,
      lead(time) over (order by time) as next_time,
      sum(case when status = 0 then 1 else 0 end) over (order by id) as grp
  from
      event
)
select
    min(time) as min_time,
    max(next_time) as max_time,
    array_agg(row(time, status)) filter (where status <> 0) as events
from
    ev
group by
    grp
order by
    grp;

min_time | max_time | events
:------- | :------- | :---------------------------------------------
12:00:00 | 16:00:00 | {"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}
16:00:00 | 20:00:00 | {"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}
20:00:00 | 23:00:00 | {"(21:00:00,1)","(22:00:00,3)"}
23:00:00 | null     | null

db<>fiddle here


How to fix SQL Agent Job using Windows Security after password change

Figured it out.
The job step is also using a Proxy account, which is the same account. So you need to change the proxy account credentials as well.


How do I read Query Cost, and is it always a percentage?

The query cost is reported in execution plans as "estimated subtree cost". This is an absolute figure such as 1.5. Conor Cunningham mentioned in a SQLBits presentation that it originally referred to the number of seconds taken to execute on a particular Microsoft employee's machine ("Nick's Machine")  in the SQL Server 7 days

but now should be interpreted as a unitless measure of overall cost.

The execution plan is a tree. Each iterator in the tree is given an estimated CPU cost and an estimated IO cost and these are added together to get the overall cost (the relative weightings can be adjusted with a couple of undocumented DBCC commands). The estimated subtree cost includes the cost for the iterator itself and all its descendants. To see an example of the costing formulas used you can look at this article.

To determine the estimated cost for an entire query in SSMS select the root iterator (e.g. the SELECT iterator) to the left of the graphical plan and look at this metric in the SSMS properties window.

When running multiple queries (whether in the same batch or not) the percentage is calculated by adding up all of these values and calculating the percentage as you would expect.

You should be aware that even in actual execution plans this cost figure is based on estimates and using this to compare the relative merits of two different queries can be woefully wrong in cases where the estimates are inaccurate.


MariaDB 10.2: Why does Count(*) work, but not Count(t.*)

In MariaDB, the COUNT() function expects an expression (that is to say, a single value). For each row where the expression is not NULL, that row will be counted. With DISTINCT, as you note, it will only count each distinct non-NULL value once.

COUNT(*) is a special case, returning the number of rows in your result set.

COUNT(t.*) would only work if there's only one column in table t.

Per the OP's testing and my own, COUNT(t.*) does not work even if the table only has one column. See this dbfiddle.uk link to confirm this for yourself.

(Note: You can also see that some other versions of SQL can handle this; if you switch the DB engine to a version of Postgres, the last statement runs successfully, instead of generating an error).


How to search for a name in two columns in MySQL?

I suspect that Hamed is the first name and Haddadi is the last name, you can do it in 3 ways;

Solution 1:

Split the query in name and last name:

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed%" OR
      lastname LIKE "%Hamed%";

Solution 2:

Use the wildcard % in between the names(spaces)

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed%Haddadi%" OR
      lastname LIKE "%Hamed%Haddadi%";

Solution 3:(recommended)

Add a fulltext index on this 2 columns:

ALTER TABLE nbaplayermaster ADD FULLTEXT KEY `full_name` (`firstname`,`lastname`);

and then search using the fulltext index

SELECT * FROM nbaplayermaster WHERE MATCH(firstname,lastname) AGAINST ('Hamed Haddadi');


You can set that in the storage.config setting within your YAML configuration file.

Example:

storage:
  dbPath: d:\data\db
  engine: mmapv1

If you're not already starting your MongoDB service using a configuration file, see here for the details on how do to that for Windows.


MySQL: Is it possible to have an IF subquery?

I would probably convert it to a more-standard JOIN and a CASE statement, personally:

SELECT
     t1.*
    ,CASE WHEN t1.tin > 0 THEN sp.name ELSE 0 END
    ,t2.productname
    ,t2.imagepath
FROM cartitems AS t1
JOIN products AS t2
  ON t1.productid = t2.id
LEFT JOIN special_packaging sp
       ON sp.id = t1.tin
WHERE t1.cartid = 8


Try this query with 2 self joins:

select
a.class
,a.total_marks as 1st_Position_Marks
,b.total_marks as 2nd_Position_Marks
,c.total_marks as 3rd_Position_Marks
,abs (a.total_marks - b.total_marks) as Difference

from MyTable a
join MyTable b on a.class = b.class and a.position + 1 = b.position
join MyTable c on a.class = c.class and a.position + 2 = c.position

where
a.position= 1 and b.position=2 and c.position =3 and
c.total_marks > abs(a.total_marks - b.total_marks)

UPDATE. Some explaination.

Basically you are doing calculations on columns, so you need to put data in two columns alongside each other. To do than you need a join, not union. You need to join two rows, that would contain data for your calcultaion. As we need difference between position 1 and 2 we need to put this data on the same row. A.position +1 = B.position does exactly that. as you do not need other permutations of position, I added the filter.


Which is faster among MySQL Community Server, Percona and MariaDB

I recently ran into this benchmark results:

https://blog.mariadb.org/benchmarking-mariadb-5-3-4/

The conclusion here was that MySQL-5.5 is the mos performing among others.

The tested editions here are :

MariaDB-5.3.4 – the Monty Program release candidate, both with XtraDB
and the InnoDB plugin
Percona-Server 5.1.61 because it is based on the same XtraDB
version as MariaDB-5.3
Percona-Server 5.5.20 – the current Percona flagship
MySQL-5.5.20 – the current Oracle flagship

Hope i helped


Why does SQL Server "Compute Scalar" when I SELECT a persisted computed column?

Just to summarise the experimental findings in the comments this seems to be an edge case that occurs when you have two computed columns in the same table, one persisted and one not persisted and they both have the same definition.

In the plan for the query

SELECT id5p
FROM dbo.persist_test;

The table scan on persist_test emits only the id column. The next compute scalar along multiplies that by 5 and outputs a column called id5 despite the fact that this column is not even referenced in the query. The final compute scalar along takes the value of id5 and outputs that as a column called id5p.

Using the trace flags explained in Query Optimizer Deep Dive – Part 2 (disclaimer: these trace flags are undocumented/unsupported) and looking at the query

SELECT id5,
       id5p,
       ( id * 5 )
FROM   dbo.persist_test
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Gives the output

Tree Before Project Normalization

LogOp_Project

    LogOp_Get TBL: dbo.persist_test dbo.persist_test TableID=1717581157 TableReferenceID=0 IsRow: COL: IsBaseRow1002

    AncOp_PrjList

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5

            ScaOp_Arithmetic x_aopMult

                ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id

                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5p

            ScaOp_Arithmetic x_aopMult

                ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id

                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

        AncOp_PrjEl COL: Expr1004

            ScaOp_Arithmetic x_aopMult

                ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id

                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

Tree After Project Normalization

LogOp_Project

    LogOp_Get TBL: dbo.persist_test dbo.persist_test TableID=1717581157 TableReferenceID=0 IsRow: COL: IsBaseRow1002

    AncOp_PrjList

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5

            ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5

        AncOp_PrjEl QCOL: [tempdb].[dbo].[persist_test].id5p

            ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5

        AncOp_PrjEl COL: Expr1004

            ScaOp_Identifier QCOL: [tempdb].[dbo].[persist_test].id5

So it appears that all the computed column definitions get expanded out then during the Project Normalization stage all the identical expressions get matched back to computed columns and it just happens to match id5 in this case. i.e. it does not give any preference to the persisted column.

If the table is re-created with the following definition

CREATE TABLE dbo.persist_test (
      id            INT NOT NULL
    , id5p          AS (5 * id) PERSISTED
    , id5           AS (5 * id)
);

Then a request for either id5 or id5p will be satisfied from reading the persisted version of the data rather than doing the calculation at runtime so the matching appears to happen (at least in this case) in column order.


SQL Server 2008 R2 There is insufficient system memory in resource pool 'default' to run this query

You say you enabled AWE on SQL Server but did you activate /PAE in the boot.ini file?

PAE enables Large Memory support.

Activate PAE in Windows 32Bit Enterprise

c:\boot.ini

[boot loader]
timeout=30
default=multi(0)disk(2)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE

http://support.microsoft.com/kb/283037

Lock Pages in Memory

As part of enabling AWE, you would also need to give the SQL Server Service user the right to Lock Pages in Memory.  Enabling this option in the Group Policy dialog (gpedit.msc), prevents Windows from paging memory out of the SQL Server working set for its own needs, especially if memory starts to run low on Windows.

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

Monitoring AWE Memory Usage

To test AWE usage, I setup a Windows 2003 Enterprise SP2 with PAE enabled in boot.ini.  I used SQL Server 2005 32bit Standard SP4.

The total physical memory is 8192Mb.  The max memory setting for SQL Server is configured at 8192 and the minimum is at 2048.

Does SQL Server see AWE on startup.

  2012-02-09 07:27:51.35 Server      Address Windowing Extensions is
  enabled. This is an informational message only; no user action is
  required.

Is SQL Server using Locked Pages for the Buffer Pool

According to some blogs, I should be seeing this message, but I'm not.

  Using locked pages for buffer pool.
  This is an informational message only; no user action is required.

Memory being allocated through AWE

SELECT  SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM    sys.dm_os_memory_clerks ;

[3648]

Memory allocated to Multi-Page memory.

select sum(multi_pages_kb)/1024 as [MultiPage Memory, MB] from sys.dm_os_memory_clerks

[14]

  Multi-page memory can not use AWE allocated memory

Memory being used outside the buffer pool

SELECT sum(multi_pages_kb
 + virtual_memory_committed_kb + shared_memory_committed_kb) AS
 [Memory used outside BPool, mb]
FROM sys.dm_os_memory_clerks
WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL'

[24]

Overall memory allocation by component

SELECT type, (single_pages_kb)/1024 as Single_Pages_MB, (multi_pages_kb)/1024
AS Multi_Pages_MB, (awe_allocated_kb)/1024 as AWE_allocated_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

This will show you how single-page and multi-page memory is being used by each component.  It will also tell you which one is able to benefit from AWE.

Mutli-page : when request exceeds 8Kb

Single-page : when request is less than or equal to 8Kb

As a side note, I have found sp_whoisactive very helpful in finding slow queries, memory hogs and just about everything that's going on in SQL Server.  Here is a link that Brent Ozar provides on how to set it up and use it.

http://www.brentozar.com/archive/tag/sp_whoisactive/


Use T-SQL to return free space on a network share

I'm not sure if this will give you exactly what you want, but you could try this:

DECLARE @cmd VARCHAR(2000)
SET @cmd = 'DIR "\\SHARENAME"'

EXEC xp_cmdshell @cmd

The last line would give you the freespace

output
04/04/2016  05:39 AM            15,872 TrackSqlLogins_0_131042360126710000.xel
08/15/2014  07:01 AM               449 TransparentDataEncryptionCertificate
08/15/2014  07:01 AM               636 TransparentDataEncryptionCertificatePrivateKey
              13 File(s)  1,208,231,169 bytes
               8 Dir(s)  217,800,073,216 bytes free


How to test a function that selects from real tables?

This is really difficult to answer. What works for others might not work for you and vice versa.
Personally i would set up a battery of repeatable test scripts. Start with a clean set of data, run a standard set of executions and then check the results are valid. Personally i would not put tests inside the code itself.
However, i would put validation checks inside your code so that it exits with sensible warnings, rather than corrupt data.
Im not a programmer, but i have learned that when a function or proc gets so complicated no-one understands it, it may be worth breaking it down into smaller more manageable functions.


Switching context between two instances from one account

The list applications command requires an instance attachment, which is performed implicitly when you first run this command against "instance1". The CLP back-end process db2bp, which handles all server commands and SQL statements on behalf of the front-end program CLP db2, remains attached to the "old" instance after you switch the environment.

You need to end the back-end process by issuing the terminate command (db2 terminate), which will cause a new back-end process to spawn and attach to the new default instance, as specified by $DB2INSTANCE, next time it is required.


Four-part distributed Query issue from 2014 to 2008R2

Ideally, you should be using the SQLNCLI11 and not SQLNCLI10.  SQLNCLI11 since that connects back to SQL 2008R2/2008/2005 versions.

There is a known bug with SQLNCLI10 that is worth knowing (even though shows that there is a problem when connecting from SQL Server 2008R2 to 2000, but it shows that you are encountering it from SQL Server 2014 to 2008R2).

Also, since you are using linked servers - refer to : Which one is more efficient: select from linked server or insert into linked server?


Plan Sound Cool but Depending on Overload on the Server for every 5 minutes will be huge. not to mention an overkill.

Use Triggers on Bulk or Inserts on The Table.

A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. It is important to understand how these small codes make such a huge difference in database performance.

what you need is a DML Trigger on table on Each insert or delete.

here's a mention.
https://www.sqlservertutorial.net/sql-server-triggers/sql-server-create-trigger/


Search for null in id column over all tables programmatically

This is a function that isn't very elegant, nor optimized, but perhaps it can be a start to get you going. It loops over all tables in a database and in case a table contains a column with name id and if any row value in that column is NULL it will return the table name. The function is quite stupid as it will not check if the column is a primary key, just that its name is id. And I'm certain there is quite a big room for improvement.

CREATE OR REPLACE FUNCTION check_tables_id()
  RETURNS table(tablename TEXT) AS
$BODY$
DECLARE row RECORD;
DECLARE has_id_col BIGINT;
DECLARE has_null BIGINT;

BEGIN
    FOR row IN
    -- Restrict to specific database and schema if necessary by specifying
    -- table_catalog='_dbname_' and/or table_schema='_schema_' in WHERE-clause
        SELECT * FROM information_schema.tables
    LOOP
    -- Check if id col exists
    EXECUTE 'SELECT COUNT(*) FROM information_schema.columns
        WHERE column_name =''id''
        AND table_catalog=''' || quote_ident(row.table_catalog) || '''
        AND table_schema=''' || quote_ident(row.table_schema) || '''
        AND table_name=''' || quote_ident(row.table_name) || ''';' INTO has_id_col;
    IF has_id_col > 0 THEN
        -- Check if col ID is null
        EXECUTE 'SELECT count(*)
        FROM ' ||  quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || '
        WHERE id IS NULL' INTO has_null;
        -- Return table name
        IF has_null > 0 THEN
            RETURN QUERY EXECUTE 'SELECT ''' ||  quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || '''::TEXT AS tablename';
        END IF;
    END IF;
    END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';

Just run the function using:

SELECT * FROM check_tables_id();

The result you get is a list like this:

dbase=# select * from check_tables_id();
   tablename
----------------
 dbase.table3
 dbase.table4
(2 rows)


mongodump 3.4.1 error reading collection: Closed explicitly

A quote from the horse's mouth (ie. https://docs.mongodb.com/manual/core/backups/#back-up-with-mongodump):

  mongodump and mongorestore are simple and efficient tools for backing up
  and restoring small MongoDB deployments, but are not ideal
  for capturing backups of larger systems.


I would strongly encourage you to try out InnoDB clusters if you haven’t yet:

http://mysqlserverteam.com/mysql-innodb-cluster-new-5-7-17-preview-release-on-labs-mysql-com/
http://mysqlserverteam.com/mysql-innodb-cluster-a-hands-on-tutorial/

That being said, this is how I had previously setup MySQL Router 2.0 to run as a daemon on my test machines–which are systemd (EL7) based–so that you have a working reference implementation. In this test setup, the username:password combination for Fabric was admin:admin

[root@hanode1 ~]# cat /etc/mysqlrouter/mysqlrouter.ini
#
# MySQL Router configuration file
#
# Documentation is available at
# http://dev.mysql.com/doc/mysql-router/en/

[DEFAULT]
logging_folder = /var/log/mysqlrouter/
plugin_folder = /usr/lib64/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[fabric_cache:lab]
address = 192.168.1.91
user = admin

# Routing into my Fabric managed cluster
# route to the current READ_WRITE / PRIMARY node
[routing:lab]
bind_address = 127.0.0.1:6600
destinations = fabric+cache://lab/group/hagroup
mode = read-write

# Routing into my Group Replication cluster
# route to the first available node
[routing:grouprepl]
bind_address = 127.0.0.1:7700
destinations = 192.168.1.92:3306,192.168.1.93:3306,192.168.1.94:3306
mode = read-write

[logger]
level = debug

[root@hanode1 ~]# cat /usr/lib/systemd/system/mysqlrouter.service
[Unit]
Description=MySQL Router
After=syslog.target
After=network.target
After=mysqlfabric.service

[Service]
Type=simple
User=mysql
Group=mysql

PIDFile=/var/run/mysqlrouter/mysqlrouter.pid

ExecStart=/bin/bash -c "echo 'admin' | mysqlrouter --config=/etc/mysqlrouter/mysqlrouter.ini"

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=true

[Install]
WantedBy=multi-user.target

[root@hanode1 ~]#

Best Regards,

Matt


How to get records from two Dates in MySql if stored in String Format?

You take the wrong way.
You should store the date in date format, not in string


How could duplicate entry on unique key happen if only one master is active?

Do not see the problem. When somebody enters the email address a second time then this happens. Nothing to do with replication. This is what the unique key is used for.

If this error would break the replication then I do not understand how replication could ever work. Every user could easily break the replication by, in this case, entering an email address 2 times.

I am not an MySQL expert but I think that the doc relates to errors other then maintaining data integrity. Perhaps somebody with more in-depth knowledge of clustering can help you on how to configure master-master clustering.


Is there an advantage to not partition aligning an index?

The main advantage of not partitioning a (non-unique) index on a partitioned base object is that it works around a long-standing query optimizer limitation related to ordered data requests such as MIN, MAX, or TOP (n) queries.

On a partitioned index, the optimizer cannot generally translate MIN, MAX, or TOP (n) to the same operation per partition, followed by a final global aggregate over the per-partition partial aggregates. The optimizer instead chooses an execution plan that scans all partitions of the index. The exception to this is the single case where the aggregate or top operation is specified over the partitioning column.

I should mention that there are also very good reasons not to have any non-aligned indexes. Choosing to use a non-aligned index would have to be a very informed choice. I have done it myself (rarely) in the past, but in very specific circumstances where the benefits clearly outweighed the costs, or there was no other reasonable alternative.

Article by Itzik Ben-Gan explaining the issue.


It is not possible to do that, because once the config is loaded on startup, it is no longer used.

MySQL uses a deterministic algorithm for searching for a config file. You can see it on the MySQL Manual (the first table is for Windows).

Two things you can do is: check which options are loaded by default: use --print-defaults:

mysqld --print-defaults
mysqld would have been started with the following arguments:
--log_bin

(in this case, I have log_bin in my configuration file).

Force manually a configuration file to be loaded with the parameter --defaults-file:

mysqld --defaults-file=C:\my\path\to\my.ini


Slow query performance of simple SELECT query (Postgresql 9.6)

I could imagine an index on links_link (submitter_id, id DESC) being of great help here.

CREATE INDEX CONCURRENTLY link_id_submitter_id
                          ON links_link (submitter_id, id DESC);

It can be traversed to quickly find the subtree for submitter_id = 811645. Then the first node can be picked instantly to get the first record ordered by id DESC.


SQL Query with optional WHERE clause parameter for date range?

Assuming that your start date and end date are compared against DlvryDt in DeliveryOrder table, I suggest to use a very low date value for start date and a very high date value for end date in cases where these date variables are null. Please see following query.

SELECT  do.OrdrNmbr AS 'Order Number',
        mb.BOLNmbr AS 'BOL Number',
        do.DlvryDt  AS 'Deliver Date',
        mh.Text3 AS 'Truck',
        do.DlvrdQntty AS 'Gallons Delivered',
        ba.BANme AS 'Carier Business Associate'
FROM dbo.DeliveryOrder AS do WITH (NOLOCK)
INNER JOIN dbo.ManifestBOL AS mb WITH (NOLOCK)
    ON do.OrdrNmbr = mb.OrdrNmbr
INNER JOIN dbo.BusinessAssociate AS ba WITH (NOLOCK)
    ON do.CrrrBAID = ba.BAID
INNER JOIN SRA.dbo.MovementDocument as md WITH (NOLOCK)
    ON md.MvtDcmntExtrnlDcmntNbr = 'DOD' + CONVERT(VARCHAR, mb.OrdrNmbr)
INNER JOIN SRA.dbo.MovementHeader AS mh WITH (NOLOCK)
   ON mh.MvtHdrMvtDcmntID = md.MvtDcmntID
WHERE mb.OrdrNmbr = @OrderNumber
and do.DlvryDt >= ISNULL(@StartDate, '19000101')
and do.DlvryDt < ISNULL(dateadd(day, 1, @EndDate), '21000101');


How can I check the status of transactional replication?

The application to do this with is the replication monitor.  To use it log into SQL Server Management Studio and connect to the publisher.  Right click on Replication in the tree and select Launch Replication Monitor (it might not be labeled exactly that).  Connect that to the distributor and you can see the replication status.

This app uses a funky set of custom queries as well as some stored procedures to figure out what's going on.  If you needed to see what's going on you'll want to run SQL profiler against the distributor so you can see how the data is flowing.  That way you can see all the commands that the replication monitor is using.


Do we need to shutdown DB/Stop DB service before Windows server patching

To upgrade a replicaset, please follow MongoDB upgrade process.
Here's a quick summary:

upgrade all secondary nodes - if necessary check if everything's working properly
stepDown and upgrade the primary node
set the setFeatureCompatibilityVersion to the version matching your upgrade


PostgreSQL - can I use logical replication from a replica's WAL?

You need to assign rds_superuser and rds_replication role to the user.

Refer here: https://aws.amazon.com/blogs/aws/amazon-rds-for-postgresql-new-minor-versions-logical-replication-dms-and-more/


What are the downsides of using varbinary[max]?

[MAX] columns are to be considered like BLOBs (Binary Large Objects) and are physically stored on another "page".
This means that practically, they are like on another table, seamlessly linked to the one with the rest of the data.
One of the main limitations is that you can't index the [MAX] column.

I don't see any issue in you doing things as you suggest.


Why am I getting LOG_BACKUP as log reuse wait reason?

There is nothing to be alarmed here. Even if you are taking periodic transaction log backup you will see this Log_Backup under log_reuse_wait_desc because last transaction log was not able to clear any VLF (in other word - VLF was not marked for reuse) due to low activity and not many transactions are being logged.

Here is the precise explanation
http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/


Are there any issues with sparsely populated partitioned tables

Each partition is essentially a separate table, so there is some cost involved in query optimization, statistics, and metadata management.  So if the third-party product supports you removing partition boundary points they created, I would probably do it.

See

  The new, higher limit of 15,000 partitions affects memory, partitioned
  index operations, DBCC commands, and queries. This section describes
  the performance implications of increasing the number of partitions
  above 1,000 and provides workarounds as needed.

Partitioned Tables and Indexes - Performance Guidelines