Is there a public repo where I can contribute to the MySQL .NET connector?

At the time of this writing, there is not public repository for Connector/NET, if you have performance/scaling improvements you are welcome to contribute them (together with a bug report) at bugs.mysql.com.


Just do a LEFT JOIN instead:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P
LEFT JOIN product_groups GP ON P.parent = GP.id

That will return all rows from P and any non-matching rows in GP will have NULL values.


Using column size much larger than necessary

SQL Server uses column lengths when allocating memory for query processing.  So, yes, in short, you should always size columns appropriately for the data.

Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.

Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely.  Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).

Your second question asks about aligning column lengths on binary multiples.   That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.


Set of datetime versioned rows, how to build the most up to date row

Something like this should work if you specify the dog_id and version_stamp. It's going to SELECT the top dog_name and dog_color ORDER BY the latest version_stamp. Then you roll those up based on dog_id using MAX().

Fiddle example.

SELECT
   a.dog_id
  ,MAX(a.version_stamp) AS 'Version Stamp'
  ,MAX(a.dog_name) AS 'Dog Name'
  ,MAX(a.dog_color) AS 'Dog Color'
FROM
   (
    SELECT
      *
    FROM dogs
    WHERE version_stamp <= '2099-12-12 12:12:12'
    AND dog_id = 19850
    AND dog_name IS NOT NULL
    UNION
    SELECT
      *
    FROM dogs
    WHERE version_stamp <= '2099-12-12 12:12:12'
    AND dog_id = 19850
    AND dog_name IS NULL
    ORDER BY version_stamp DESC
    LIMIT 2
   ) a
GROUP BY
   a.dog_id

DOG_ID  VERSION STAMP   DOG NAME    DOG COLOR

19850   September, 27 2014 02:16:41+0000    Dieter  brown


localhost:port_number/em (what is my port number)?

the default port for 11g OEM is 1158. If it's not set to the default in your installation, then the value will be in portlist.ini. You can also find the full URL in the emd.properties file:

http://docs.oracle.com/cd/E25054_01/server.1111/e10897/em_manage.htm#BABHJAGE

  In your Web browser, enter the following URL:

  https://hostname:portnumber/em
  For example, if you installed the database on a host computer named comp42.example.com, and the installer indicated that your Enterprise Manager Console HTTP port number is 1158, then enter the following URL:

  https://comp42.example.com:1158/em
  If you have recently installed Oracle Database, you can determine the port number for Database Control on Linux and UNIX systems by viewing the $ORACLE_HOME/install/portlist.ini file.

  Otherwise, you can determine the port number for Database Control by searching for REPOSITORY_URL in the $ORACLE_HOME/Oracle_sid/sysman/config/emd.properties file, where Oracle_sid is the system identifier (SID) for your database instance.

$ORACLE_HOME is the home directory you chose when installing the database.


Load balancer versus connection pooling -- is there a difference?

With PostgreSQL you have two different areas that can do pooling, either at the app layer (i.e. jdbc built in pooling etc) or in an intermediate layer that sits between the app and the db(s), such as pgbouncer or pgpool.

If you do pooling in an intermediate layer, like pgbouncer or pgpool, then that layer can ALSO do load balancing of select queries.  Further when load balancing, you can do writes in one of two ways: you can either have a single write master that replicates via other means to your read slaves, using a tool like slony or the built in streaming replication that showed up in pg 9.0 and above, or you can have the load balancer do all the writing, so that incoming reads hit only one db, but writes hit each database to keep them all updated.

Or if you're adventerous, you can move the load balancing layer down another layer into postgresql itself using plproxy.  This is a pl language for pgsql designed to allow you to put one pg database on the front end that has no actual data in it, and that database then can run on top of multiple possibly redundant dbs for incredible throughput.  plpoxy is pretty complex to get set up and running but it is also quite scalable as well.  Note that your application has to be re-written to support it so it can't be tossed under any old app and just work.

http://slony.info/
http://wiki.postgresql.org/wiki/PL/Proxy
http://pgpool.projects.postgresql.org/


pt-table-checksum and mysql 5.7 with binlog_format=ROW

with --no-check-binlog-format you would not be able to checksum from master to cascading slaves aside from master's immediate replicas, --no-check-binlog-format also sets the tool's session to STATEMENT format but does not change the global status. there should not be any issues when you use the option but we recommend you test them first.


A table

You have two types of entity so almost certainly need two tables to model them. As the relationship is one-or-zero-to-many from the SIM side it should have a nullable foreign key to the phone table. Something like

Sim
============         Phone
ID      (PK)         =======
inPhone (FK)  ---->  ID (PK)

That way a SIM is either in a phone or floating. If you want to enforce the maximum number of SIMs in each phone at the schema level you'll need custom check constraints or triggers.


What are the alternatives for an ORDER BY clause in a View?

Only the outermost ORDER BY will guarantee order

Any intermediate or internal ORDER BY is ignored.This includes ORDER BY in a view
There is no implied order in any table
There is no implied order from any index (clustered or not) on that table

Links

"Sorting Rows with ORDER BY" (MSDN)

  ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition: (and example follow that matches this question)


SOLUTION:
Somehow the password for that user got modified/blasted. By resetting the password I have regained access.


Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`?

I confirm that yes, this is atomic.

This is what I wanted to avoid, but in the end, I wrote a Python script testing this. I'm pasting it below for reference. The prerequisite is a schema my_schema with the following table:
CREATE TABLE `t` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `col` int(10) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `t` (id, col) VALUES (1, -1);

Python script below. Note that you need to comment / uncomment one of the two 'SELECT' lines as needed.
import mysql.connector
import random
import string
import threading
import time

# How many threads to run.
num_worker_threads = 30
# Flag to signal to threads that they should finish.
should_threads_finish = False
values = []

def main():
    global should_threads_finish

    # Start threads.
    print('Starting threads.')
    threads = []
    for i in range(num_worker_threads):
        t = threading.Thread(target=worker, kwargs={'i':i})
        t.start()
        threads.append(t)

    # Let threads run for a while.
    time.sleep(8)

    # Stop threads.
    print('Stopping threads.')
    should_threads_finish = True
    for t in threads:
        t.join()

    # Verify that values queried from DB form a sequence increasing by 1.
    values_sorted = sorted(values)
    for i in range(len(values_sorted)):
        if i != values_sorted[i]:
            print(values_sorted)
            raise RuntimeError('Set and get was not atomic: ' + str(i) + ' vs ' + str(values_sorted[i]))

# What each thread should do.
def worker(i):
    print('Worker ' + str(i) + ' starting.')
    while True:
        if should_threads_finish:
            print('Worker ' + str(i) + ' finishing.')
            break
        # Do work.
        cnx = mysql.connector.connect(host='localhost', database='mysql', user='root', password='root')
        cur = cnx.cursor()

        # Get random name for user variable.
        var_name = ''.join(random.choice(string.ascii_uppercase + string.digits) for _ in range(10))

        # Do the UPDATE.
        cur.execute("UPDATE my_schema.t SET col = @%s := col + 1 WHERE id = 1", (var_name,))
        cnx.commit()

        # Get either the user variable value or actually select from the table.
        cur.execute("SELECT @%s", (var_name,))
        # cur.execute("SELECT col FROM my_schema.t WHERE id = 1")
        row = cur.fetchone()
        values.append(int(row[0]))

        cnx.close()

if __name__ == "__main__":
    main()

As expected, when selecting the user variable, I'm getting no exception, meaning that the sequence of values queried increased exactly by 1. For my use case, this is a good enough check that this is atomic.
Also as expected, when selecting the value directly from the table (not using the user variable), I'm getting exceptions, signifying non atomicity.


MySQL Error1822 failed to add the foreign key constraint

I made tables and I used index to be able to make a relationship between my tables, then I added a foreign key (the relationship is one-to-many).
FYI, you don't need to repeat the column in a different table(city). You can save the data in one table and you get the data by joining them. I kept the city on both tables, but you can remove it from one of your tables.

CREATE TABLE `Attendance` (
  `id` int(11) NOT NULL PRIMARY KEY  AUTO_INCREMENT,
  `Attendance_ID` int(11) NOT NULL,
  `4_jan` int(11) NOT NULL,
  `6_feb` int(11) NOT NULL,
  `7_mar` int(11) NOT NULL,
  `18_apr` int(11) NOT NULL,
  `20_may` int(11) NOT NULL,
  `21_jun` int(11) NOT NULL,
  `10_jul` int(11) NOT NULL,
  `30_aug` int(11) NOT NULL,
  `16_oct` int(11) NOT NULL,
  `12_nov` int(11) NOT NULL,
  `15_des` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Post_Address` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `Student_ID` int(11) NOT NULL,
  `Street_Address` varchar(99) NOT NULL,
  `Postal_Code` int(11) NOT NULL,
  `City` varchar(99) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Student_Information` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
  `Attendance_ID` int(11) NOT NULL,
  `Student_ID` int(11) NOT NULL,
  `Student_name` varchar(99) NOT NULL,
  `Phone_number` int(11) NOT NULL,
  `Email` varchar(99) NOT NULL,
  `City` varchar(99) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `Attendance`
  ADD KEY `Attendance_ID` (`Attendance_ID`);

ALTER TABLE `Post_Address`
  ADD KEY `Student_ID` (`Student_ID`);

ALTER TABLE `Student_Information`
  ADD KEY `Attendance_ID` (`Attendance_ID`),
  ADD KEY `Student_ID` (`Student_ID`);

ALTER TABLE `Attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`Attendance_ID`) REFERENCES `Student_Information` (`Attendance_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `Post_Address`
  ADD CONSTRAINT `post_address_ibfk_1` FOREIGN KEY (`Student_ID`) REFERENCES `Student_Information` (`Student_ID`) ON DELETE CASCADE ON UPDATE CASCADE;


Old user removed, now having DEFINER errors

as You can see from text of both commands - they not work with data other than single table, so from this point of view - both safe.

In worst case - You already have not worked views and continue have them :), but it not expected result.

But, in any case - the best practice - make backup before any kind of similar operations, so You always can rollback.

Notice in server documentation:

  The server manipulates the mysql.proc table in response to statements
  that  create, alter, or drop stored routines. It is not supported that
  the  server will notice manual manipulation of this table.

more related to other types of manipulation, like - manual edit of BLOB object with text or parameters, but change definer - it just change single column.


I recently solved this issue on my own system and it turned out to be SELinux blocking the reads for the certificates.  I fixed my problem by running a restorecon -v /path/to/certs/* but it could also be solved by disabling selinux by running setenforce 0 or by editing the /etc/selinux/config file and setting 'SELINUX=DISABLED'.

If you haven't disabled SELinux already that is.


In there interest of showing a generalized example until we see your particular DDL and query, take the below as a basic example:

create table SomeTable
(
    id int identity(1, 1) not null
        primary key clustered,
    AnotherInt int null,
    SomeData nvarchar(1024) null
)
go

insert into SomeTable(AnotherInt, SomeData)
values(null, null)
go 1000

update SomeTable
set
    AnotherInt = id * 3 + 152,
    SomeData = 'My ID value is ' + cast(id as nvarchar(16))

So we have a test table, SomeTable, with a clustered index.  Execute the below query:

select SomeData
from SomeTable
where AnotherInt < 200

This causes a Clustered Index Scan, like you are seeing:

So analyzing the query, we have the column AnotherInt that is in the WHERE clause and being searched on.  We are also retrieving the SomeData column, so to prevent a key lookup in the clustered index (or the optimizer may even just use a Clustered Index Scan again), we'll have SomeData as an INCLUDE column:

create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt)
include (SomeData)
go

Now, executing the same query above:

select SomeData
from SomeTable
where AnotherInt < 200

SQL Server will utilize that non-clustered index to return the data.  It is a covering index, because it won't need to do a lookup on the clustered index for the remaining data:

Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.


Try this:

SELECT
    x.MonitorId,
    x.configXml.value('(IntervalSeconds/text())[1]','INT') CheckInterval,
    x.configXml.value('(NumSamples/text())[1]','INT') SamplesBeforeDown,
    x.configXml.value('(SystemDriveWarningThreshold/text())[1]','INT') SystemDriveWarningThreshold,
    x.configXml.value('(CounterName/text())[1]','VARCHAR(100)') Countertype
FROM
    (
    SELECT
        MonitorId,
        CAST( ConfigurationXML AS XML ) AS configXml
    FROM [dbo].[monitor] mt
    WHERE MonitorId = '4960E39A-59C8-A2C2-99B1-59B73D73156F'
    ) x

Note I have changed a couple of things about your code: 1) always use the text() accessor for better performance against untyped XML and 2) I've specified better datatypes.

Let me know if that works for you.


Repair database on Replica in sharded set up

In my opinion if your fragmentation is not over 15-20% its not worth doing that (except if you are running out of disk space). What i would do is:

1) Add an arbiter to each replica set (optional)

2) Shutdown one of the secondaries and delete the data directory

3) Start the secondary and let it do the initial sync which removes fragmentation

(at this point you can evaluate if the gain on disk worth continue)

4) When Secondary catch up do the same for the next Secondary

5) When all Secondaries done, step-down Primary and do the same on the x-Primary

6) Remove the arbiter

Important: Your oplog size must be able to keep all operations during the initial sync. Else you will need to resize it (Same applies if you choose repair option)

For your second question replica set member don't hold any metadata. If the majority of Replica set is always available you will not face any issues.


A quick check on Wikipedia doesn't mentioned if an "outer join" implies left, right or full when this important bit is omitted.

Practically,

"outer join" by iself isn't supported. You normally require LEFT, RIGHT or FULL
"natural" means "join on column with the same names"

This means

"Natural outer join" won't be recognised
"Natural full outer join" is "full outer join" with "natural" matching

Indexs/keys don't matter in this case and make no difference.

The result you get is correct for the standard

select *
from
   R
   full outer join
   S ON R.B = S.B

or

select *
from
   R
   full outer join
   S USING (B)

Note: not all RDBMS support all syntax:

SQL Server doesn't support NATURAL (a good thing)
MySQL doesn't support FULL OUTER JOIN (can be worked around)

Natural joins are dangerous anyway (SO links)

https://stackoverflow.com/questions/4826613/natural-join-in-sql-server/4826659#4826659
https://stackoverflow.com/questions/3277580/sql-server-lack-of-natural-join-x-join-y-usingfield
https://stackoverflow.com/questions/3063107/is-natural-join-any-better-than-select-from-where-in-terms-of-performance


Stored Procedure Resultset Row Count and Resultset Size in Bytes/KBs

The question is unanswerable as asked since stored procedures do not produce result sets. Only (certain) statements produce result sets. Executing procedures may produce none, one or more results sets depending on the statements contained and the branches executed.

For statements the row count will be tracked in sys.dm_exec_query_stats.

Result sets have no size (in bytes or KB), size is an attribute pertinent only to encoding and representation. A sensible question would be to ask how many bytes are transferred between the client and the server, and this can be tracked by transport performance counters.


Since you are using InnoDB with innodb_file_per_table enabled, you need to raise innodb_open_files because this dictates the number of open file handles on .ibd files.

In MySQL 5.6, this setting is treated a little different.

    This variable is relevant only if you use multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL
    can keep open at one time. The minimum value is 10. As of MySQL 5.6.6,
    the default value is 300 if innodb_file_per_table is not enabled, and
    the higher of 300 and table_open_cache otherwise. Before 5.6.6, the
    default value is 300.

    The file descriptors used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit
    server option, and do not affect the operation of the table cache. For
    general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk
    I/O”.

Here is an additional reference from MySQL Performance Blog about setting innodb_open_files.

Raising innodb_open_files to a big number should not be that all that troublesome, but please be sensible about the number you choose.


Oracle DB license for only non-production environments

Yes you do.  If you install the Oracle software you need to pay for it.  However numerous exceptions exist including

you can use Oracle Express 11g for free
your customer may have licencing provisions that apply to you

Either way, do not take anything from my answer or anyone else's answer as valid.  Oracle licencing  is complex and a final word can only come from them.  Contact  your representative and ask for clarification.


index for OR operator with different columns

If id is the primary key, any query with where id=:1 and (..whatever..) can effectively use the primary key index and retrieve, lookup only one row (if there is one.) It will then have to do the additional checks, the (..whatever..) but that is not going to be slow, since it's only one row.

If id was not the primary key, the situation might be different, depending on the selectivity of the 3 columns involved. The best index, would probably be two indexes, on (name, id) and on (lastname, id)

Oracle's optimizer can then use both indexes, doing an "index join". See the SQL-Fiddle.


What would make a MySQL database create tables in a different collation other than its default?

Finally actually adding this to the question.
You must specify the COLLATION while creating the table. It will inherit the collation from the database which is latin1_swedish_ci. Wordpress sets the collation only for the connection, not for the database. E.g. for WHERE or ORDER BY clauses... See Answer and comment similar to this in link above.

hrake gets the credit not I


Database design: Dividing multiple identical tables, good or bad?

You should use one table for this, unless you need more stringent constraints.

  Each area has the same number of rows and the same number of seats per row.

Let's say there are 5 rows in every area, and 6 seats in every row. You'd want to use something along these lines.

create table seats (
  area char(1) not null check (area in ('A', 'B', 'C', 'D')),
  row integer not null check ( row between 1 and 5 ),
  seat integer not null check ( seat between 1 and 6 ),
  primary key (area, row, seat)
);

To select a single seat, put three values in the WHERE clause.

select *
from seats
where area = 'A' and
       row =  1   and
      seat =  2;

To use a table like this to model seat reservations, populate it with every possible area, row, and seat. Then set a foreign key reference to it.

create table reservations (
  performance_time datetime not null,
  party_name varchar(40) not null,
  area char(1) not null,
  row integer not null,
  seat integer not null,
  primary key (performance_time, party_name, area, row, seat),
  foreign key (area, row, seat) references seats (area, row, seat)
);

You can see all the seating for a performance with this query.

select s.area, s.row, s.seat, r.performance_time, r.party_name
from seats s
left join reservations r
       on r.area = s.area and
          r.row  = s.row  and
          r.seat = s.seat and
          r.performance_time = '2013-04-30 08:00 pm'

And you can get all the available seats for a performance with something along these lines.

with seating as (
  select s.area, s.row, s.seat, r.performance_time, r.party_name
  from seats s
  left join reservations r
         on r.area = s.area and
            r.row  = s.row  and
            r.seat = s.seat and
            r.performance_time = '2013-04-30 08:00 pm'
)
select *
from seating
where performance_time is null

By default, SQL Server will create a clustered index for a primary key constraint. You'll want to give careful thought to the order of columns in your primary key constraints, and consider adding other indexes as well. (Especially since your outputs will often need to be ordered by area, row, and seat.)


If it's a sql login and you have access to the application servers, you could look for it in connection strings on those. Otherwise, there is no way to find the password.  Your only option is to reset the password.


The binlog position is the byte offset into the current binlog.  So, the pair of binlog filename and pos uniquely identify the location in the virtually unlimited binlog.

The GTID (if being used) also provides a unique location, but it is not "offset" based.


I've found this blog, and the solution the author described works like a charm for me.

Below is command line install command

setup.exe /QUIETSIMPLE /ACTION=install /FEATURES=SQL /INSTANCENAME=SQL2008
/SQLSVCACCOUNT=”NT Authority\System” /SQLSYSADMINACCOUNTS=”sreekanthpc\sreekanth”
/AGTSVCACCOUNT=”NT Authority\System” /SECURITYMODE=SQL /SAPWD=”Pa$$w0rd”
/SQLTEMPDBDIR=”D:\SQL2008\TempDB\\” /SQLUSERDBDIR=”D:\SQL2008\SQLData\\”
/SQLUSERDBLOGDIR=”D:\SQL2008\SQLLog\\”

I hope this helps


Indexed (Materialized) View for Parent and Newest Child

There isn't a way to do this currently, as finding the "latest" widget date will require an aggregate (grouping), ranking function, subquery, or apply; none of which the indexed view will allow in its definition.

However - is there a particular reason you want to have an indexed view? Do you have appropriate indexing on the underlying Widget table? I'm assuming that the reason you want to index the view is so you can pull out the values for a particular Widget ID.

I would index the underlying table on (WidgetID, CreatedWhen DESC) and implement your query as a normal view - or perhaps a inline table valued function if you always want to get the date for a single Widget.


Oracle SQL Developer 4: how to tell under which user I connected

You could save multiple connections to the same db but with different credentials. Give them useful names like user@thisdb and you'll be able to view that in SQL Developer at a glance in the top right corner of the worksheet.

And my favorite part: if you need to run the same SQL as multiple users or in different environments or both, you'll find that the connection name in the top right corner of the worksheet is actually a dropdown list, so it can be changed on the fly.


Oracle DBCA utility: "Listener is not up or database service is not registered with it"

As far as I understand, there is a database on the machine. This database should register itself on the listener. It can do so when it knows where - on which port - the listener runs. In you case the listener is on port 15321. To enable the auto registration of the instance, you should make sure that the LOCAL_LISTENER parameter is filled with the correct port number in order to be able to contact the listener.
For docu check Oracle® Database Reference 11g Release 1 (11.1)

. oraenv
[YOUR_ORACLE_SID]
sqlplus / as sysdba
-- if the instance appears not to be running:
startup
alter system set local_listener = '(ADDRESS = (PROTOCOL=TCP)(HOST=Myhost)(PORT=15321))';
-- to force immediate registration:
alter system register;

Now your database should be visible in the listener services list
listener.ora should look like this - note the PORT =

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = Myhost)
        (PORT = 15321)
      )
    )
  )

check the results:

  lsnrctl services


DBCC SHRINKFILE on the distribution database

It is ok to shink the datafile,  there WON'T be any data loss.

The "issues" when shrinking could be performance, during the rellocation of data pages. Also if you know that your DF will grow again to the same size, shrink is not recommended. If it was because a one time only operation, or other stuff where the DF won't grow at the same big size again, it is ok to perform your shrink operation.


What Mechanism does MySQL use to schedule Backups on Windows Server 2008?

Are you running MySQL Administrator? That includes a backup scheduling mechanism.


Outer Join on all columns if possible, or a subset of columns if not possible, and sum

You will need three left joins. Semipseudo code as stuck in trafic
Select sum(coalesce(l1,l2,l3 quantity)
From t1
Left Join t2 as l1 on a,b,c
Left join t2 as l2 on a,b and l1 is null
Left join t2 as l3 on a and l1 is null and l2 is null


Interactive INSERT / UPDATE function to implement UPSERT

If you want to catch NULL violations as well you have to cover the UPDATE as well, which makes the function a little more expensive.

SET search_path = netcen;

CREATE OR REPLACE FUNCTION fun_test(myobj test, OUT myoutput text)
  RETURNS text AS
$func$
BEGIN
   UPDATE test
   SET    tes = myobj.tes,
          testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      myoutput := 'UPDATE successfull. testkey = ' || myobj.testkey;
      RETURN;
   END IF;

   INSERT INTO test
   SELECT (myobj).*;
   myoutput := 'INSERT successfull. testkey = ' || myobj.testkey;

EXCEPTION
WHEN null_value_not_allowed THEN
   RAISE NOTICE 'null_value_not_allowed occurred.';
   myoutput := 'A field was null.';

WHEN not_null_violation THEN
   RAISE NOTICE 'not_null_violation occurred.';
   myoutput:= 'A field was null.';

WHEN unique_violation THEN
   RAISE NOTICE 'unique_violation occurred.';
   myoutput:= 'Duplicate value.';
END
$func$ LANGUAGE plpgsql;

Using an OUT parameter and made some simplifications and clarifications.

I would just let the regular EXCEPTION happen. People are not supposed to enter NULL values for NOT NULL columns. This way, the UPDATE can run outside the EXCEPTION block.


How to write concat query for multiple 'select into' statement in mysql stored procedures?

SIMPLY use user defined variales instead of variables.
CREATE PROCEDURE `new_procedure` (IN tableA varchar(255))
BEGIN
SET @sql = CONCAT(' select count(id) , orgid , (select id from org_subscription t where t.orgid=mainOrgID)  into @count,@ParamOrgId ,@isPremium  from ', tableA );

PREPARE stmt from @sql;
EXECUTE stmt ;

SELECT concat('my val is ', @ParamOrgId);
END

But this only works if you get only 1 result row. IF you want more, you need a Loop


Bulk insert not retaining special chars of UTF-8 Encoded txt file Sql Server 2008

SQL Server 2008 does not support UTF-8 natively prior to SQL Server 2019.

So how can you import your data correctly?

Convert your data to UTF-16 prior to your insert
Upgrade to SQL Server 2014 (SP2) or above and take advantage of code page 65001 in the bulk insert statement as below

BULK INSERT #table

FROM  'C:\MyFile.csv'+ WITH

(
    CODEPAGE = '65001',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR ='\n'
);

Since UTF-8 is an encoding style, not a collation, store the data as VARBINARY and decode it in the front-end rather than the database.


There are various ways to rewrite your query, so it uses indexes that cover it. For example:

SELECT d.chain_id
FROM docs AS d
WHERE d.family = 1
  AND d.id_status IN (4,5)
  AND EXISTS
      ( SELECT *
        FROM chains AS c
        WHERE c.is_archive = 0
          AND c.chain_id = d.chain_id
      )
GROUP BY d.chain_id
ORDER BY MAX(d.date_created) DESC
LIMIT 200 OFFSET 0 ;

or this:

SELECT d.chain_id
FROM docs AS d
  JOIN ( SELECT c.chain_id
         FROM chains AS c
         WHERE c.is_archive = 0
       ) AS c
    ON c.chain_id = d.chain_id
WHERE d.family = 1
  AND d.id_status IN (4,5)
GROUP BY d.chain_id
ORDER BY MAX(d.date_created) DESC
LIMIT 200 OFFSET 0 ;

In combination with proper indexes that will "cover" the query. I'd start by adding these and testing for efficiency:

docs
(id_status, family, chain_id, date_created)

chains
(id_archive, chain_id)


(SELECT courses  FROM wp_category WHERE CatID =401)
        OR
        (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )

This is a condition, but you need n values.

This should work:

(
    ID IN
        (
            (SELECT courses  FROM wp_category WHERE CatID =401)
        )
    OR
    ID IN
        (
            (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN
                (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )
        )
)

Also take care about subqueries with MySQL. In your case a sub- subquery.

Take a look here: https://stackoverflow.com/questions/12356784/mysql-dependent-sub-query-with-not-in-in-the-where-clause-is-very-slow


Create sequence based on the value of another column

This will produce the desired result for a single query.

drop table if exists T1;
go

create table T1(RowId int, UserId int, Desired_SequenceNumber int);
go
insert T1
values
(1, 1, 9997),
(2, 1, 9998),
(3, 2, 1),
(4, 2, 2),
(5, 3, 1),
(6, 1, 9999);   -- Have increased User 1's values to test wrap-around.
go

select
    T1.*,
    SequenceNumber = ((ROW_NUMBER() over (partition by UserId order by RowId) - 1) % 9999) + 1
from t1
order by RowId;

The ROW_NUMBER built-in function creates a sequential integer for each row in the result. partition by UserId ensures each value of UserId starts counting again at 1. Order by RowId ensures the ordering of SequenceNumber matches the ordering of RowId. The - 1) % 9999) + 1 bit ensures wrap-around at 9999 and starting again at 1, rather than at zero.

If the SequenceNumbers must be written once and consistent for all subsequent reads the above will not work. Then you will need something like

;with NewData as
(
    select *
    from
    (
        values
            (7, 1),
            (8, 4)
    ) as T(RowId, UserId)
),
LatestRow as
(
    select
        UserId,
        RowId = MAX(RowId)
    from T1
    group by UserId
),
NextSequence as
(
    select
        T1.UserId,
        Number = (T1.Desired_SequenceNumber + 1) % 9999
    from T1
    inner join LatestRow
        on LatestRow.RowId = T1.RowId
)
-- insert T1(RowId, UserId, Desired_SequenceNumber)
select
    NewData.RowId,
    NewData.UserId,
    Desired_SequenceNumber = ISNULL(NextSequence.Number, 1)
from NewData
left outer join NextSequence
    on NextSequence.UserId = NewData.UserId;

I've structured it using CTEs for ease of exposition. It could be re-factored for performance.

CTE NewData introduces test data without having to manage tables. Replace this with your actual source. Notice that RowId 7 is for an existing user and 8 is for a new user. I've hard-coded RowIds for ease of reference. This is not necessary for this solution. Your tables likely use an IDENTITY and that's OK.

Because the numbers wrap-around the solution must find the most recent value, not the largest value, and increment from there. I use RowId as a proxy for time. CTE LatestRow finds the most recent i.e. largest / MAX() RowId for each existing user. If RowId is not monotonic this solution will not work and some other measure of time will be required. Note that gaps are OK so deletions and IDENTITY / SEQUENCE is acceptable. Depending on your cardinalities performance may be improved by INNER joining to NewData in this CTE.

CTE NextSequence maps the RowId to an existing sequence value and calculates the next sequence number. The modulo (%) does the wrap-around at 9999. I changed my sample data from that in your question so UserId 1 will wrap around. I'm assuming one row per user in the new data. If there are more you can can assign each in-coming row a local sequence within the new data and add & modulo to the existing base sequence number.

The final select gives the data to be inserted. If your real table has an identity omit RowId. Using an outer join allows new users to be inserted. The ISNULL sets new users' sequences to 1.

Since you get to name SEQUENCE objects you could, in a very convoluted way, create a new one for each value of UserId, and try to code which one to pull from for each in-coming new row. As an exercise it would be interesting. As a production system it would be appalling. Don't do this. The meta-coding would be awful and there will be some limit to the number of sequence objects available in a database.


Do it directly:

SELECT idscore,
       ndeporte,
       category,
       gender,
       'ROJO' team1,
       CASE WHEN team1 = 'ROJO' THEN score1
            WHEN team2 = 'ROJO' THEN score2
            WHEN team3 = 'ROJO' THEN score3
            END score1,
       'AMARILLO' team2,
       CASE WHEN team1 = 'AMARILLO' THEN score1
            WHEN team2 = 'AMARILLO' THEN score2
            WHEN team3 = 'AMARILLO' THEN score3
            END score2,
       'TURQUESA' team3,
       CASE WHEN team1 = 'TURQUESA' THEN score1
            WHEN team2 = 'TURQUESA' THEN score2
            WHEN team3 = 'TURQUESA' THEN score3
            END score1
FROM score;


same field in bothe "where" and "order by" - how to index for this query?

The OR will most likely affect the index usage, because it hinders index seeking. Generally speaking "OR" is 'bad' for index usage in where clauses.

The time field, it would be sensible to have it in an index sorted in the DESC order when you use it DESC;

Other than that - we'll need more information to give more exact/concrete advice


Monitor For Disabled Queue On RDS SQL Server

We are having the same issue, I see no one else has responded.  Our interim solution is to have a job running every 5 mins to just re-enable the queue if disabled.

If you have devised a solution that hasn't been published, I'd love to hear about it.


Insert into a table selecting data from a sub query

Add some alias:

insert into mytable (columns)
select t.* from (
    select columns from tableA
    UNION ALL
    select columns from tableB
    UNION ALL
    select columns from tableC
) t


Are SQL Server statistics stored in database or buffer pool?

The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool.

As for the question: statistics are in the database so a backup/restore will preserve the statistics.

Note though that preserving statistics is not a guarantee to plan reproducibility. Other factors influence plan generation, like number of CPUs and amount of RAM.


SQL Replication across the internet give named pipe error

I've finally managed to connecto both servers!

As it turns out even though both server are x64 and running 64bit os and SQL Server 64 bit, it needs the aliases to be configured both on 64 & 32 Bit Native Client Aliases.

Once I configured the one for the 32 bit driver, it worked.


What are the possible causes for sp_reset_connection taking a long time to execute?

Finally got some time to write a more detailed answer.

There are typically three main reasons a simple procedure like sp_reset_connection will take a long time to run.

You are waiting for CPU resources
You are blocked on a lock somewhere (perhaps as a result of DML or a competing transaction)
Your network is slow and it takes a long time to return the result back to the client

Ad 1) If you are waiting for CPU resources, this should show up as signal waits. Please see my comment on your question on how to diagnose if this is the issue

Ad 2) If you are waiting for a lock, this is best diagnosed by comparing two snapshots of sys.dm_os_wait_stats. See this article on how to do this:

http://www.sqlsoldier.com/wp/sqlserver/sqludbaweekbeanefficientdba

If you see long waits for LCK_[Something], query sys.dm_tran_locks to track down which objects are being locked. In your case, I would expect to see some form of SCH-[Something]> locks blocking you.

Ad 3) The easiest way to diagnose network issues to first look for OLEDB and ASYNC_NETWORK_IO waits in step 2 (if you wait a long time for network, one of those show up). If those waits are high, use xperf -on latency or a network monitoring program like netmon or wireshark to check your latencies. If the network looks slow, this could also be caused by the calling application server not responding fast enough to the connection being recycled.


What operating systems are certified for Oracle 11gr2 RAC?

My Oracle Support has a certify tab which lists current certifications, including the one you are looking for. Here is how:

Select "Oracle Database" as the Product.
Select the release "11.2.0.1".
Click the link "Check certifications with another product.
Select the "Product Category" radio button.
In the Category drop down select "Operating Systems".


I typically drop #temp tables explicitly just out of habit (I like cleaning up anything I create). There is a myth out there that dropping a #temp table explicitly will prevent #temp table caching and reuse, but this is not true: See this blog post by @PaulWhite for a lot more details, and this one for even more.


No way to set maximum retrieved rows in pgadmin query tool

I don't think there is a global parameter to tweak in pgAdmin 1.16.*

As an alternative you can open tables with:

Tools -> View Data -> View Top 100 rows

This is also available from the context menu of a table in the object browser.
Once the data grid is open, a "Limit Bar" is available, where you can set a maximum for returned rows.

I would consider it a reasonable feature request to make this "Limit" element available in the "View data options" you get when you open a table with a filter (grid icon with funnel). This could also remember the last setting.

A global setting is a bit of a foot gun. People forget and then wonder why they don't get more rows.


My database has been deleted suddenly on the server how to recover it?

Before restoring from the latest full database backup, you can try Paul Randal's advise - set the database to the EMERGENCY mode and use a login that is a member of the sysadmin SQL Server role to access it. Keep in mind that you will be able only to read the data, as the database will be in the read-only mode.

Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database


How many requeries/second can my server handle?

It answer is - It depends on many many many factors. How many cubes can you fit in a box is a similar question in a non technical sense.

What are the queries, What are the specs of the server, is it dedicated mysql or does your webserver go on there too, Is it overclocked any ? Whats the temperature ? Do you cache querys ?

The list goes on. The best answer is to just test it with your set up and a similar set of queries and see. We can not give a better answer


What signals do the DBWn processes send to LGWR?

Actually, you're not required to have that deep knowledge for OCA exam, you just need to know which processes are required for database to function (PMON, SMON, DBWR, LGWR, CKPT, MMON, MMNL, RECO), some other processes that may be required when you configure other database options such as ARCHIVELOG database mode, what each of these processes does, and how they communicate with each other in rough outline.

As far as inter-process communication (IPC) is concerned, it is quite possible, for example, that on *nix, Oracle uses POSIX calls for communication, whereas on Windows it uses Windows API calls, in other words, the way Oracle implements inter-process communication differs between platforms because these platforms are implemented differently.

If you're preparing for OCA exam, you may already know (and you really should know) the memory architecture of Oracle, and that there's an area of memory called System Global Area (SGA). This memory area is used to keep the information used by many background and foreground processes including server processes and shared server processes (when Shared Server Architecture is used).

There's also the Fixed SGA area within the SGA which contains:

  Information communicated between processes, such as information about
  locks

You can also determine if Oracle allocates some IPC resources on *nix if you run ipcs command before and after you start up the database. (I stumbled upon this in this AskTom article).

Therefore, you can see that Oracle uses both system calls, and shared memory to share information between Oracle processes.


SQL Server 2012 Remote Access to Certain Users

sp_configure 'remote access', 1 doesn't do what you think it does. The option name is misleading as it has nothing to do with allowing/disallowing users to access the server from a remote location, it's server-to-server connections. There's a ton of misinformation out there on this. Try it yourself, set the value to 0 then connect to the SQL Server from a different machine. So long as a network protocol is enabled and there are no firewall blocks, you will connect successfully.

As for your users, unless your users typically connect to the machine/vm where your SQL Server instance is hosted to access the SQL Server, all are remote users and that should be way. Unless your SQL Server is running on a network that is publicly accessible, there's little harm/risk in letting the server accept all incoming requests. These requests have to be authenticated and authorized anyway so it's not like you're letting random connections through.

If you really want to lock it down so only specific users can even attempt to connect to SQL Server, you can do it via the Windows firewall with Advanced Security Settings. You've probably already configured an inbound rule to allow connections from other machines so just modify that rule by restricting access to specific users only. Just look for the Users tab and add the authorized users from there (you'll need to require secure connections only in the General tab). If you haven't yet configured an inbound rule, just create a new one and add the restrictions on users mentioned above.

Again, if the SQL Server is running in a corporate network not directly accessible by outsiders/the internet, this type of lockdown is rarely needed. If you really have some highly sensitive data in there then this lockdown is probably insufficient.


Forwarded Records, effective way to fix them

SQL Server does not keep a "statistic" around for the number of forwarded records. To return the number it has to access the physical table pages. If the table is large, that can take a while.

That said, you can query sys.dm_db_index_physical_stats in SAMPLED mode. This will give you an estimate for the number of forwarded records based on a 1% sample of the base table.

Alternatively, you could correlate the number of updates (see sys.dm_db_index_usage_stats) with the number of forwarded records on each table. Say if every 10th update causes a forward, then you can check if the table has crossed your threshold by looking if the number of executed updates is 10 times higher then your threshold. Querying sys.dm_db_index_usage_stats is instant. However, it accumulates starting fresh with each server restart.

I would still regularly (once a month maybe) run the sys.dm_db_index_physical_stats, to make sure that the ratio is still accurate.

To actually get rid of the forwarded records you can use ALTER TABLE dbo.test REBUILD WITH(ONLINE=ON); though this will also rebuild all nonclustered indexes on a heap table. That should be a lot faster than creating and dropping a clustered index, which rebuilds all nonclustered indexes twice. (The WITH(ONLINE=ON) clause requires Enterprise Edition.)

Now, the bigger question you have to ask yourself is, if you really should have 4000 heaps in a database. In general a well chosen clustered index will improve performance of the system as a whole. The storage structure "heap" is usually only recommended for tables with a high number of inserts, and very few selects, updates or deletes.

In a typical OLTP environment that would be only true for log tables. Try to figure out if you could add clustered indexes to your tables, and your forwarding pain will go away. (Then however you need to deal with fragmentation, but there are some good (and free) solutions on the market already; e.g. Ola Hallengren's scripts).


Moving MySQL Cold Backup to Another DB Server

You missed three(3) things

On DB1, mysql -uroot -ppassword -e"SET GLOBAL innodb_fast_shutdown = 0"
On DB1, service mysql stop (DB1)
Copying the backup (/var/lib/mysql) on DB1
Copy /etc/my.cnf on DB1 to /etc folder on DB2
Importing DB1 backup to DB2 (/var/lib/mysql)
On DB2, chown -R mysql:mysql /var/lib/mysql
Start mysql on DB2

Step 1 flushes everything InnoDB not yet committed to disk. That makes for a faster mysql startup.

From here, you have the option of changing settings

Give it a Try !!!


NOAFFIRM means the redo transport will be acknowledged before writing the received redo to disk (standby redo logs).

  ASYNC noaffirm means the log transfer method is ARCH right?

No, it does not. Without specifying LGWR or ARCH, LGWR is the default. You can check it on the primary database by:

select archiver from v$archive_dest where dest_id = 5;

There you should see LGWR. However, specifying ARCH or LGWR became deprecated in 11g:

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams125.htm#CHDJDFCJ

Deprecated does not mean it will not work, so it is still possible to specify ARCH in log_archive_dest_n and revert to the way it worked in 10.2 (and what you described). But that is not how you configure MAXIMUM PERFORMANCE anymore, because of the changes, even MAXIMUM PERFORMANCE uses LGWR and standby redo logs. See below:

http://docs.oracle.com/cd/E11882_01/server.112/e41134/log_transport.htm#BABCBEGJ

  The synchronous and asynchronous redo transport modes require that a
  redo transport destination have a standby redo log.


Can one assign a performance score to a stored proc to compare performance of 2 procs

When you review/compare a SQL Server Execution Plan, each plan is given a cost value. Although these values can indeed be used for a relative comparison between plans, it is often more beneficial to dig a little deeper than that.

For example, you could compare the execution plans of the two stored procedures, perhaps using a tool such as the Free Plan Explorer from SQL Sentry.

This would allow you to break down the "score" you seek into more useful metrics, such as execution time, CPU consumption, Disk IO etc. and use these for your comparison.

Using this method would enable you to establish exactly where your performance gain(or loss) is achieved.


Why does Oracle indicate my table is using so little space?

Your table's segment is only 2 MB.  The rest of the data is stored in the LOB segments which are physically separate from the table segment.

SELECT column_name, segment_name
  FROM dba_lobs
 WHERE owner = 'MYUSER'
   AND table_name = 'MYTABLE'

should show you that there are two LOB segments associated with the table.  You can then query dba_segments to get the size of those segments

SELECT sum(bytes)/1024/1024 mb
  FROM dba_segments
 WHERE owner = 'MYUSER'
   AND segment_name IN (SELECT segment_name
                          FROM dba_lobs
                         WHERE owner = 'MYUSER'
                           AND table_name = 'MYTABLE')


What does it mean? ORA-06502: PL/SQL: numeric or value error: character string buffer too small

As stated in the comments, both errors have been caused by declaring @error_msg varchar where it should have been ,@error_msg varchar(max)


Looking for ways to optimize sql query with inner join

Your statistics are way off.

In the scan on index_event_subscriptions_on_user_id_group_id it estimates 4 rows returned, yet 7295 are returned.

WHERE (event_subscriptions.user_id = 456)
AND (event_subscriptions.group_id IN (123, 321, 222, 333))
AND (event_subscriptions.valid IS true)

The additional AND (events.type != 1) is not removing anything.

I would try running ANALYZE event_subscriptions (or, even better, if you can afford it VACUUM FULL ANALYZE event_subscriptions see if that fixes it. If it doesn't I would try ALTER TABLE event_subscriptions SET STATISTICS 10000 and try ANALYZE event_subscriptions again to see if that fixes it.

If neither of these worked, I would try pushing events.type check down..

INNER JOIN event_subscriptions
  ON event_subscriptions.event_id = events.id
  AND events.type <> 1

At the point that you're talking 220 million rows, you may also want to consider partitioning.


How to change "deployment target version" on SSIS 2019?

Be sure to go to the Project properties, not the solution properties. See image below.


Yes, statspack is an easy free way to go. First thing to look at is the top 5 timed events. Unfortunately statspack won't tell you the problem for specific sql statements. To see where and how particular sql statements are spending their time, you need ASH. A graphical interface to ASH along with the collection of ASH (ie no perf pack license needed)  is available with WASH.
http://www.oraclerealworld.com/web-ash-w-ash/
I'd recommend lab128 which has a 2 day free trial
http://www.lab128.com/lab128_download.html
Lab128 can collect ASH itself or optionally use the ASH collection from the performance pack.
One you have on of those running, see where you are spending time - CPU, I/O, Locks or other stuff.
In both the above tools you can see where individual SQL spend time to help you distinguish a problem in one SQL vs loosing that SQLs problems in the overall stats of the database.


How do I find out what version of SQL Server for Linux I have?

Using dpkg

dpkg -l | grep -i mssql-server
ii  mssql-server                              14.0.3008.27-1                              amd64        Microsoft SQL Server Relational Database Engine

Using PAL_PROGRAM_INFO=1

You can set the undocumented environmental variable PAL_PROGRAM_INFO for a lot of information,

PAL_PROGRAM_INFO=1 ./sqlservr
sqlservr
  Version 14.0.3008.27
  Build ID 5fb3474a5f63ad2f4b7eddadad44a086839721f18a66c5fb5d7cfcce25c0f539
  Build Type release
  Git Version 693be7c
  Built at Sat Nov 18 04:54:59 GMT 2017

PAL
  Build ID a984d7631c4f1eb55ae1c77cfb51a09b905528d4e05e728a6343af04e744d96f
  Build Type release
  Git Version 2fe1654
  Built at Sat Nov 18 04:54:06 GMT 2017

Packages
  system.sfp                    6.2.9200.2,2fe1654c9,
  system.common.sfp             10.0.15063.540
  system.certificates.sfp       6.2.9200.2,2fe1654c9,
  system.netfx.sfp              4.6.1590.0
  secforwarderxplat.sfp         14.0.3008.27
  sqlservr.sfp                  14.0.3008.27


What is the most efficient way to make a Locality Entity (Town, City, Village etc) with a Country Entity?

In Relational Database terms efficiency revolves around avoiding the duplication of data.  The 3 tables that you show in your question seem reasonable provided:

You can have a Locality without having a Country
You can have a Country without having a Locality

This is actually quite a viable design, since in some parts of the world they may not define locations as you describe.  (There are a lot of unique decisions made around the planet.) Of course you can generate an artificial name or  description for your tracking purpose should that be necessary.

For example: There is no named locality, you could create a Locality like: "Island NW of Salty Creek"

If you intend for each Locality to belong to a single Country then you might benefit from a small change to add a CountryLocality table:

Locality

PK UN LocalityID
UN LocalityName

Country

PK UN CountryID
UN CountryName

CountryLocality

PK UN CountryLocalityID
FK    CountryID
FK    LocalityID

UserProfile

Username...Password...etc
FK    CountryLocalityID

This is definitely a design and usage issue for you to decide on since the CountryLocality table adds some overhead. It depends on how strong the Country-Locality connection should be and whether this extra step is worth it for your application.


Populating missing data based on previous month-end values

One approach to this problem is to do the following:

Emulate LEAD on SQL Server 2008. You can use APPLY or a suquery for this.
For rows without a next row, add one month to their account date.
Join to a dimension table that contains month end dates. This eliminates all rows that don't span at least a month and adds rows to fill in the gaps as necessary.

I modified your test data a little bit to make the results deterministic. Also added an index:

create table #histories
(
    username varchar(10),
    account varchar(10),
    assigned date
);

insert into #histories
values
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-08'), -- changed this date to have deterministic results
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07') ;

-- make life easy
create index gotta_go_fast ON #histories (account, assigned);

Here's the laziest date dimension table of all time:

create table #date_dim_months_only (
    month_date date,
    primary key (month_date)
);

-- put 2500 month ends into table
INSERT INTO #date_dim_months_only WITH (TABLOCK)
SELECT DATEADD(DAY, -1, DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '20000101'))
FROM master..spt_values;

For step 1, there are plenty of ways to emulate LEAD. Here's one method:

SELECT
  h1.username
, h1.account
, h1.assigned
, next_date.assigned
FROM #histories h1
OUTER APPLY (
    SELECT TOP 1 h2.assigned
    FROM #histories h2
    WHERE h1.account = h2.account
    AND h1.assigned < h2.assigned
    ORDER BY h2.assigned ASC
) next_date;

For step 2, we need to change the NULL values to something else. You want to include the final month for each account, so adding one month to the starting date suffices:

ISNULL(next_date.assigned, DATEADD(MONTH, 1, h1.assigned))

For step 3, we can join to the date dimension table. The column from the dimension table is exactly the column you need for the result set:

INNER JOIN #date_dim_months_only dd ON
    dd.month_date >= h1.assigned AND
    dd.month_date < ISNULL(next_date.assigned, DATEADD(MONTH, 1, h1.assigned))

I didn't like the query that I got when I put it all together. There can be issues with join order when combining OUTER APPLY and INNER JOIN. To get the join order I wanted I rewrote it with a subquery:

SELECT
  hist.username
, hist.account
, dd.month_date
FROM
(
    SELECT
      h1.username
    , h1.account
    , h1.assigned
    , ISNULL(
        (SELECT TOP 1 h2.assigned
            FROM #histories h2
            WHERE h1.account = h2.account
            AND h1.assigned < h2.assigned
            ORDER BY h2.assigned ASC
        )
        , DATEADD(MONTH, 1, h1.assigned)
    ) next_assigned
    FROM #histories h1
) hist
INNER JOIN #date_dim_months_only dd ON
    dd.month_date >= hist.assigned AND
    dd.month_date < hist.next_assigned;

I don't know how much data you have so it might not matter for you. But the plan looks how I want it to:

The results match yours:

╔══════════╦══════════╦════════════╗
║ username ║ account  ║ month_date ║
╠══════════╬══════════╬════════════╣
║ PETER    ║ ACCOUNT1 ║ 2017-01-31 ║
║ PETER    ║ ACCOUNT1 ║ 2017-02-28 ║
║ DAVE     ║ ACCOUNT1 ║ 2017-03-31 ║
║ DAVE     ║ ACCOUNT1 ║ 2017-04-30 ║
║ FRED     ║ ACCOUNT1 ║ 2017-05-31 ║
║ FRED     ║ ACCOUNT1 ║ 2017-06-30 ║
║ FRED     ║ ACCOUNT1 ║ 2017-07-31 ║
║ JAMES    ║ ACCOUNT1 ║ 2017-08-31 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-01-31 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-02-28 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-03-31 ║
║ JAMES    ║ ACCOUNT2 ║ 2017-04-30 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-05-31 ║
╚══════════╩══════════╩════════════╝


Pass information to another table with the same inherit

You can create trigger after update like this

CREATE TRIGGER trigger_name
    AFTER UPDATE ON Z
    FOR EACH ROW
    ... delete/update/insert to table A;
    ... delete/update/insert to table B;

I hope this helps

Reference
https://www.postgresql.org/docs/10/sql-createtrigger.html


How innodb alter table inplace algorithm inner work?

There are too many different cases.  I suggest you read https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-overview.html , then come back with a more specific question.  (Back up the version if you are using something older.)

My feeling in watching MySQL evolve over the years is this last round of Optimizations of ALTER has added ways to speed up just about everything in ALTER.  That includes avoiding rebuilding the table (when practical) and avoiding locks (when practical).

"says mysql can dml when modify column" -- Well, that depends on the modify column -- add/remove NULL, change ENUM / DEFAULT / etc, ADD/DROP, etc.

The first ALTER optimization was for adding an option to ENUM at the end and not exceeding 255.  That was added many versions ago -- It was rather trivial in that it changed the "meta" data only.  That can even be INSTANT in the new 8.0 terminology (even better than INPLACE).


You have two options:

1) Rollback the DELETE in case you're deleting the row "Peter". You do this with an AFTER trigger. Be aware that it will rollback the whole transaction in case yuo're deleting multiple rows among which at least one is "Peter".

CREATE TRIGGER TR_CUSTOMER_DEL ON customer
FOR DELETE
AS
BEGIN

    SET NOCOUNT ON;

    IF EXISTS (
        SELECT *
        FROM deleted
        WHERE name = 'Peter'
    )
    BEGIN
        ROLLBACK;
        THROW 50001, 'Cannot delete Peter', 1;
    END
END;

2) You override the way SQL Server does DELETEs on the table, using an INSTEAD OF trigger. In this case, you silently ignore DELETEs for the row "Peter".

CREATE TRIGGER TR_CUSTOMER_IO_DEL ON customer
INSTEAD OF DELETE
AS
BEGIN

    SET NOCOUNT ON;

    DELETE c
    FROM customer AS c
    INNER JOIN deleted AS d
        ON c.customer_id = d.customer_id
    WHERE d.name <> 'Peter';

END;

Which one is best for you highly depends on your business logic.


Update all the elements of JSON array nested in a JSON column (Postgres)

I think I worked it out. Something like:

SELECT my_column::jsonb ||
    jsonb_build_object('a',
            (SELECT json_agg(a::jsonb || jsonb_build_object('r','[]'::jsonb))
            FROM jsonb_array_elements(my_column->'a') a)
    )
FROM my_table;

Though this might not be perfect since I'm translating to this example without testing.


MultiSubnetFailover=true for DBMSes besides SQL Server

MultiSubnetFailover can be used only for connecting to SQL Server part of the client connection string.
For MySQL, if you are using .NET, then you can use multiple hosts in connection string or you can even use single DNS that provide DNS Service (SRV) records to provide failover, load balancing, and replication services.


Database Migration - Can it be this easy?

High level your approach will work but there is an additional step you will need to do along with a gotcha.

First off the gotcha - you will need to ensure that the instance names are identical on each server (although you can have a different server names). This isn't an issue though if you are just using default instances.

Once you cut over the databases you can then rename the new server to the old name. Once renamed you will need to rename the server name within SQL Server (again not to be confused with the instance name) using the steps outlined in this article.

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

I would either use log shipping or mirroring for the 5 databases and then manually copy across each instance level object (logins, jobs, linked servers, mail profiles, etc) and goes without saying that the applications should be tested before hand to make sure you are aware of all the instance level components and don't forget anything.

I have used approaches like this before to migrate clustered instances with numerous databases. This approach has more work up front getting everything set up and ready but it makes the cut over relatively quick and painless for the client applications.


T-SQL equivalent to Python Pandas Concatenate

Something like this?:
CREATE VIEW MyUnion AS
SELECT
    a, b, c,
    d,
    CAST(NULL AS varchar(32)) AS e
FROM table_1
UNION ALL
SELECT
    a, b, c,
    CAST(NULL AS bigint) AS d,
    e
FROM table_2 ;


Collations and codesets in PostgreSQL 9.x

The documentation is not very clear with respect to the relationship between the encoding / character set of the database and the ctype / codeset of the collation. All that it mentions are the following statements (all found on the 22.3. Character Set Support documentation page):

  each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings

  On Windows, ..., UTF-8 encoding can be used with any locale.

  Important: On most modern operating systems, PostgreSQL can determine which character set is implied by the LC_CTYPE setting, and it will enforce that only the matching database encoding is used. On older systems it is your responsibility to ensure that you use the encoding expected by the locale you have selected. A mistake in this area is likely to lead to strange behavior of locale-dependent operations such as sorting.

There is an implication here that the LC_CTYPE value only has rules for the characters in its Character Set. A value of 1252 indicates an Extended ASCII Code Page for Windows Latin1. All of those characters can be encoded into UTF-8 (you current encoding), but that doesn't necessarily mean that locale-aware functions such as upper, lower, initcap, etc will behave as expected when operating on characters that exist outside of the Code Page. This should be testable by running one of those functions on a character that is not in the Windows Latin1 / Code Page 1252 character set. For example, Latin Small Letter Nj U+01CC:

ǌ

should capitalize to:

Ǌ

So, if (sorry, I don't have PostgreSQL at the moment to test with) the following;

SELECT upper('ǌ'), lower('Ǌ');

returns:

Ǌ   ǌ

then it looks very positive that the "1252" LC_CTYPE value is not adversely affecting anything. It would still be good to try a SELECT with an ORDER BY since sorting was mentioned a few times in the documentation as an area that would likely be affected if these values -- encoding and LC_CTYPE -- were in conflict.

You cannot change either LC_COLLATE or LC_CTYPE once a database has been created, so you could try creating a new database to see if you can get the desired settings, regardless of what the installer thinks you should have:

CREATE DATABASE my_db_name WITH
            ENCODING 'UTF8'
            LC_COLLATE='English_United States.UTF8'
            LC_CTYPE='English_United States.UTF8'
            TEMPLATE=template0;

You might need to look in the pg_collation system catalog to see what is available.


How do I go about creating this SQL query?

You can self join to tables for this sort of criteria(at least, I can do this in SQL Server):

SELECT
     r.name,
     m.title
FROM
    rating rt
    JOIN movie m ON (rt.mid = m.mid)
    JOIN reviewer r on (rt.rid = r.rid)
    JOIN rating rt2 on (rt.mid = rt2.mid and rt.rid = rt2.rid)
WHERE
    rt.ratingDate > rt2.ratingDate
    AND rt.stars >  rt2.stars;

Note that we're joing rating back to itself.  This way we can use the WHERE clause to find rows in rating that are greater in both date and stars to other rows in rating.

You can review the full example I built in SQL Fiddle here.

If you want to add the condition that the reviewer made exactly these 2 reviews of the movie and no more, you can add:

    AND NOT EXISTS                                  --- not exists
        ( SELECT *
          FROM rating rt3                           --- another rating
          WHERE rt3.mid = m.mid                     --- for same movie
            AND rt3.rid = r.rid                     --- by same reviewer
            AND rt3.ratingDate <> rt2.ratingDate    --- on different date
            AND rt3.ratingDate <> rt1.ratingDate    --- than the first 2 reviews
        )

This assumes that the Rating table has  (rID, mID, ratingDate) as primary key.


Move data from one oracle db to another db using toad

If you have Toad I assume you can connect to both databases from it.

Select the origin table in the schema browser in Toad, right click, select "Create in another schema" or "Copy Data to another schems".
A dialog box appears, select your options as required.
then a "Destination" dialog box appears.  Select the connection to the destination database and schema of your choice
Click on execute.

Toad will show a failure or success message.

Job done!


How to fix MySQL anonymous account security issue?

Do you want to remove the anon users? You can just use DROP USER to remove them. They shouldn't have any special privileges, as stated in the MySQL Docs and should not be a major problem as they're only in the localhost.

If, even removing, you still can login or something, check out this other question.


Are null values allowed in any column of a table in SQL?

Allowing Null Values

  Columns defined with a PRIMARY KEY constraint or IDENTITY property
  cannot allow null values.

  The nullability of a column determines whether the rows in the table
  can contain a null value for that column. A null value, or NULL, is
  different from zero (0), blank, or a zero-length character string such
  as "". NULL means that no entry has been made. The presence of NULL
  typically implies that the value is either unknown or undefined.

Microsoft SQL Server's definition of NULL:

  A value of NULL indicates that the value is unknown. A value of NULL
  is different from an empty or zero value. No two null values are
  equal. Comparisons between two null values, or between a NULL and any
  other value, return unknown because the value of each NULL is unknown.

  Null values generally indicate data that is unknown, not applicable,
  or that the data will be added later. For example, a customer's middle
  initial may not be known at the time the customer places an order.

SET ANSI_NULLS setting can change the behavior of NULL.  In future versions this setting will be ON by default and you will not be able to turn it off.
Details here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

This is an article which go in depth about NULL.
https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/

Few example of NULL in TSQL here.


Is there any way to efficiently perform the equivalent of DENSE_RANK in MongoDB?

MongoDB doesn't have any concept of ranking.  The closest I could find comes from here:

Here's some sample data:
 > db.scoreboard.find()`
 { "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "user" : "dave", "score" : 4 }
 { "_id" : ObjectId("4d99f71b50f0ae2165669eaa"), "user" : "steve", "score" : 5 }`
 { "_id" : ObjectId("4d99f72350f0ae2165669eab"), "user" : "tom", "score" : 3 }

First, find the score of the user "dave":
 db.scoreboard.find({ user : "dave" }, { score : 1 }) { "_id" : ObjectId("4d99f71450f0ae2165669ea9"), "score" : 4 }

Then, count how many users have a higher score:
 db.scoreboard.find({ score : { $gt : 4 }}).count()
 1

Since there is 1 higher score, dave's rank is 2 (just add 1 to the
count of higher scores to get the rank).

Obviously, this is far from ideal.  However, MongoDB simply does not have any type of functionality for this since it's simply not designed for this type of querying.


SQL Server Max Memory not limiting use of RAM

SQL Servers max memory setting defines the limits for buffer pool usage only. There will be variable but significant allocations required over and above that limit.

Jonathan Kehayias's, Christian Bolton and John Samson have level 300/400 posts on the topic. Brent Ozar has an easier to read article that might be a better place to start.

Also related: SQL Server 2008 R2 “Ghost Memory”


In general, the optimizer is much better at figuring out whether a range scan would be more efficient than a skip scan (or vice versa). If the optimizer is making the wrong choice, the problem almost always comes down to the human not providing the optimizer with the proper information in the form of accurate statistics on the table and on the indexes (including things like histograms on columns that contain skewed data).

For any given query, the only way to know with certainty which of two plans is more efficient is to try it out with your data on your hardware with your query. You can try adding the hint to force the plan to change and you can then measure which approach is actually more efficient. If the plan the optimizer is choosing isn't the most efficient, I would tend to suspect that the root cause is that some aspect of the statistics are not correct but that is something that you'd need to look at a 10053 trace to get to the bottom of in most cases. Rarely, there are cases where the optimizer is wrong because the optimizer isn't able to take into account some aspect of the data (for example, until recently, it was unable to deal with situations where the data in multiple columns was not independent such as a car table with make, model, and year columns where knowing one value constrained the set of valid values in the other columns).


Replace Null values with values from the same column by matching values in a different column

UPDATE `table` t1, `table` t2
SET t1.`date` = t2.`date`
WHERE t1.id = t2.id
  AND t1.platform = 'mob'
  AND t2.platform = 'web'
  AND t1.`date` IS NULL
  AND t2.`date` IS NOT NULL

PS. The solution assumes that in a subarray of the table which matched platform = 'web' AND t2.`date` IS NOT NULL condition the id value is unique. If not, a random value from possible values list will be used, and you must use a subquery instead of t2 table copy, which filters data by platform = 'web' condition, groups it by id and selects alone data value (maximal, for example) for each id value. If your MySQL version is 8+, you may do it in WITH clause.


Slower SQL Server performance after allocating more CPU and RAM

There's a lot going on here, and most of it is pretty broad and vague.

2008R2 RTM came out on April 21, 2010. It's totally out of support. You'll want to prioritize getting on the latest Service Pack, which came out just about 3 years ago to the day. That way you'll be covered if you're hitting a weird bug or something. Head on over here to figure out what you need to download.
Since you added vCPUs (from 1 to 4) and didn't change any settings, your queries can now go parallel. I know this sounds like they'll all be faster, but hang on!
You may have added RAM, but you may not have changed Max Server Memory so your server can take advantage of it.
Figure out what your server is waiting on. An open source project I work on provides free scripts to help you measure your SQL Server. Head on over here if you wanna give them a try.

You'll wanna grab sp_BlitzFirst to check out your server's wait stats. You can run it a couple ways.

This will show you what your server has been waiting on since it started up.

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

This will show you what queries are waiting on now, during a 30 second window.

EXEC dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

Once you figure out what queries are waiting on (there's a ton of stuff written about wait stats out there), you can start making changes to get things under control.

If you see them waiting on CXPACKET, that means your queries are going parallel, and maybe trampling over each other. If you hit this, you'll probably want to consider bumping Cost Threshold for Parallelism up to 50, and maybe dropping MAXDOP down to 2.

After this step is when you want to use something like sp_WhoIsActive or sp_BlitzWho (the latter is in the GitHub repo from earlier) to start capturing query plans. Aside from wait stats, they're one of the most important things you can look at to figure out what's going wrong.

You may also want to check out this article by Jonathan Kehayias about VMWare Counters to check out in relation to SQL Server.

Update

Reviewing the wait stats and boy are they weird. There's definitely something up with the CPUs. Your server is mostly sitting around bored, but when things heat up, things get bad. I'll try to break this down easily.

You're hitting a poison wait called THREADPOOL. You don't have a ton of it, but that makes sense because your server isn't terribly active. I'll explain why in a minute.
You have really long average waits on SOS_SCHEDULER_YIELD and CXPACKET. You're on a VM, so you're gonna wanna make sure that the SQL Server has reservations, or that the box isn't horribly oversubscribed. A noisy neighbor can really ruin your day here. You're also going to want to make sure that the server/VM guest/VM host aren't running in Balanced Power mode. This makes your CPUs spin down to unnecessarily low speeds, and they don't immediately spin back up to full speed.
How do they tie in? With 4 CPUs you have 512 worker threads. Keep in mind, you had the same amount with a single CPU, but now that your queries can go parallel, they can consume many more worker threads. In your case 4 threads per parallel branch of a parallel query.

What's going parallel? Most likely everything. The default Cost Threshold for Parallelism is 5. That number was made the default sometime in the late 90s working on a desktop that looked like this.

Granted, your hardware is smaller than most laptops, but you're still a bit ahead of that thing.

When lots of parallel queries get going, you're running out of those worker threads. When that happens, queries just sit around waiting for threads to get going. That's also where SOS_SCHEDULER_YIELD comes in. Queries are stepping off CPUs and not getting back on for a long time. I don't see any blocking waits, so you're most likely just all stuffed up on intra-query parallelism waits.

What can you do?

Make sure nothing is in Balanced Power mode
Change MAXDOP to 2
Change cost threshold for parallelism to 50
Follow the Jon K. article above to validate VM health
Use the script called sp_BlitzIndex to look for any missing index requests.

For more thorough troubleshooting, check out the whitepaper I wrote for Google on hardware sizing in the cloud.

Hope this helps!


SQL Server backup practice with limited disk space

If you need to be able to restore to any point in time then you need to be in full recovery mode (which you are). When in full recovery mode, you need to do transaction log backups in addition to your full backups.

Doing a Full Database backup does not make any difference to your log file, only a transaction log backup will clear it out and allow you to reuse the space.

When you're in full recovery mode NEVER truncate your transaction log - this will break the backup chain and you won't be able to do a point in time restore.

Transaction Log backups can run as frequently as you want or as often as you need to keep the file from growing bigger than your disk space or are required to by the business. I've seen some companies run them daily and some run them every minute and a lot in between. The advantage of running them more frequently is that if your disks die then you only lose data since your last log backup (assuming they're on different drives/hardware). It also keeps your log backup files smaller and the backup operation is usually faster because it has less to backup.

You can schedule a job to clean up old log backup files that you no longer need (e.g. in your case more than 7 days old).

If you need to restore from backups then you need to do the following:

Restore last Full Backup
Restore last differential backup (if any)
Restore all log backups up to the point you want to restore to

Can I strongly suggest using Ola Hallengren's scripts if you're not using a 3rd party tool to do backups (https://ola.hallengren.com/)


BCNF decomposition step without functional dependencies

When you decompose R(T) to find the BCNF, if the dependency X -> Y violates the BCNF, the decomposition that the classical algorithm requires is not R1(X) and R2(T-X), but R1(X+) and R2(T-(X+ - X)).

So the first decomposition, considering AB → C, should be:

R1(A, B, C, E) (since AB+ = ABCE)
R2(A, B, D) (= {ABCDE - (ABCE - AB))

In R1 the remaining dependencies are AB → C, C → E, C → B, while in R2 there are no (non-trivial) dependencies.

R1 is not in BCNF, since the two dependencies C → E, C → B violates that form (the only candidate keys are AB and AC). So it can be decomposed in R3(B, C, E), with dependencies C → E, C → B, and R4(A, C) (again without non-trivial dependencies).

So the final decomposition is:

R2 < (A B D) ,
{ } >

R3 < (B C E) ,
{ C → E
C → B } >

R4 < (A C) ,
{ } >

Note that the dependencies:

{ C D → A
A B → C }

are not preserved in this normalization.

Finally, note that if, during this process, you get a relation schema without (non-trivial) dependencies, this schema is already normalized, since the only candidate key is formed by all the attributes and there is no dependency that violates the BCNF.


Is there a faster way to get UNION ALL behavior in Postgres?

For the simple case, I can only think of minor improvements to the query:

(
  SELECT DISTINCT ON (t2_id)
         t1_id, t2_id
  FROM   t0
  WHERE  t2_id IS NOT NULL
  ORDER  BY t2_id, t1_id  -- to get consistent results
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id)
         t1_id, NULL      -- cheaper
  FROM   t0
  WHERE  t2_id IS NULL
  -- if you retrieve more columns, add ORDER BY, too
)

As ypercube mentioned You need to add ORDER BY with an unambiguous list of expressions to get deterministic results.
You can use the constant NULL instead of t2_id in the second leg of the query. Also relevant for below index support.
The key to performance is indexing. Try two partial indexes matching the two parts of the query:

CREATE INDEX t0_part1_idx ON t0 (t2_id, t1_id) WHERE t2_id IS NOT NULL;
CREATE INDEX t0_part2_idx ON t0 (t1_id)        WHERE t2_id IS NULL;

You may or may not want to include additional columns to allow index-only scans.

Depending on table size and data distribution, there may be faster alternatives:

Select first row in each GROUP BY group? (last chapter)
Optimize GROUP BY query to retrieve latest record per user

Single SELECT

If you want to condense it into a single SELECT:

SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id)
       t1_id, t2_id
FROM   t0
ORDER  BY coalesce(t2_id, t1_id), t2_id, t1_id;

Equivalent, except for sort order. If you want this to be fast, try a functional index:

CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), t2_id, t1_id);


Cleaning disabled SQL agent jobs in SQL Server 2005 and above

Only you can answer if the jobs can be dropped completely. If they are not jobs like from the import/export wizard I would probably script them out. This can be done easily with PowerShell.

A quick way of dropping them is via Object Explorer details pane, it allows multiple selections and then just hit the delete key or right click and delete.


The poor performance of this is due to the pager of view, just modify the view setting to "Mini Pager" or others except "Full". Your database performance will boost twice times as before.


Are duplicates in Read Commited standard behaviour?

The SQL Standard isolation level definitions are (probably deliberately) imprecise. They are given in terms of allowable concurrency phenomena:

P1 - dirty read
P2 - non-repeatable read
P3 - phantom

At READ COMMITTED isolation, only P1 is disallowed.

extract from ISO/IEC 9075-2

Reading the "same record" more than once due to movement within a physical structure is therefore not disallowed, so long as each read is not a dirty read (i.e. the read is performed on committed data).

Therefore, SQL Server's locking implementation of READ COMMITTED isolation conforms with the standard.

I understand the behaviour may be surprising, but the root of that surprise is in misunderstanding what this isolation level does, and does not, guarantee. For example, one might expect a concurrent UPDATE never to change the number of records returned by a SELECT query. This is a reasonable expectation, but not one that must be met to conform with the standard.

If you want a point-in-time (stable) view of the data, locking read committed is not the isolation level implementation you need. While not mandated by the standard, read committed using row versioning (RCSI) happens to provide a statement-level point-in-time consistent view of the committed state of the database. For a transaction-level point-in-time consistent view of the committed state of the database, you would need snapshot isolation (SI).

  As far as I know only MS SQL Server behaves this way.

It would be possible in any database that implements read committed using locks (specifically short-term locks for reads) and where a concurrent modification might cause a record to move ahead of the current scan position. In other words, it is implementation-specific.

Related reading:

A Critique of ANSI SQL Isolation Levels by Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil, and Patrick O'Neil.
The Read Committed Isolation Level by me


why is there an index range scan on max() query?


How to write SQL queries to populate the tables while synchronizing transactions and account balance?

Most financial systems of record are very complex. They involve separate concepts of "posting" transactions, then "settling" transactions afterwards, as well as "closing" of financial periods, after which settled transactions can't be modified.

Say you walk into your bank and withdraw $100. The banker posts that transaction in front of you, but it isn't settled until later (often overnight). If the banker accidently typed in the wrong withdrawal amount, they can adjust the transaction and that corrected transaction will appear on your statement as a single -$100 withdrawal. If the error isn't noticed until after the transaction is settled, the correction is posted as a separate transaction and your statement will show the incorrect transaction + correction = net correct amount. Most banks close each day as part of settling standard bank accounts, making the two fairly similar. Credit cards generally close months (which is when they often compute fees, send statements, etc.)

One reason for the complexity is the concerns for concurrency and isolation on these methods. Consider the race condition on option 1:

two users try to execute transactions on the same user at the same time.
The two SELECT statements run in parallel to get balance (say, $500).
Then they both insert transactions (again in parallel) of -100.
Both will calculate the new balance of $500-$100=400. They'll block each other on the update, but the race condition will make the balance incorrect because they selected the starting balance without knowledge of the prior transaction.

So... That's my way of saying this is actually extremely complex if you care about being correct. Assuming accuracy matters, you might consider an alternate approach. Real life will be a bit more complicated to implement than this, but this is a high level approach :
- post transactions as normal, but don't update the balance.
- asynchronously, "close" a time period, at which time you process all transactions during that period to compute the new balance & cache that on the user, along with the time it was computed.
 - when you need a balance, look up the cached balance, and all transactions since it was computed. Return that balance to the user.


To start with you have SQL Server 2012 and memory architecture changed significantly in this version there is only one memory allocator (yes there is VAS allocator as well but I still would say there is only one 'Any Page' allocator) and this does Almost All memory allocations. Here is what BOL has to say. Please read this definition clearly

  Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, Memory heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

For your question

  Total components which use memory in SQL can be found through below query,were each distinct clerk means a component.

From above definition please note that not all memory allocations are tracked by clerks. But for practical purposes you can safely assume sys.dm_os_memory_clerks is place to look for memory breakup as used by various components.Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces. Frequently, components create their corresponding clerks at the time SQL Server is started.

Please note clerks only track memory allocated to various components they never do allocation of SQL Server memory.

  Buffer Pool plus query plans will be able to use maximum of 58 GB ram

Not only buffer pool and query plans but almost all components which require memory will now be catered from max server memory setting. Specially SQLCLR, memory to this is now allocated from max server memory setting previously in SQL Server 2008 R2 and before memory was allocated from outside max server memory setting.

  Since sql server calculates number of locks during startup,will the memory used by locks fall into 6 GB category ?

No memory will be allocated by Max server memory setting(will fall under 58G category) not from outside this limit. I am not using word buffer pool as from SQL Server 2012 buffer pool is only consumer not allocator of memory so its better to use max server memory

  Suppose i have executed a query which requires more memory ,will this memory be allocated from 6 Gb or from buffer pool (58 Gb ) memory or will this be spilled to disk ?

Well it depends on what components require memory. Please read the definition on top if resource that requires memory is Thread stack, memory heap, Third Party linked server provider the memory would be allocated from outside max server memory limit otherwise from inside. When query requires more memory Lazywriter would start ageing out unused pages and internal threads would ask various memory consumers to free some memory. This would create new space in buffer pool and SQL Server can bring would bring new pages into the memory.

  There are different memory clerks all shown above in screenshot,will they fall in 6 GB category ?

You have not added screenshot but when you query sys.dm_os_memory_clerks whatever clerks information come they all track memory allocated to various components and would fall under max server memory limit category (58 G limit category).

  In 64 Bit systems,does Sql still calculate memtoleave option or with the exception of few memory clerks ,everything falls under 58 GB category ?

NO THERE IS NO MEMORYTOLEAVE IN 64 BIT. Please read This Blogs.msdn article for more details.

  Further largest consumer of these clerks would be buffer pool and query plans. If other clerks face memory pressure or need more memory,memory allocation will come through buffer pool clerk in terms of stolen pages if they pages flushed to disk to accomodate this request.

No it is not exactly like that. Yes in SQL Server 2012 as well largest consumer of memory is buffer pool. Stolen memory is memory utilized by SQL Server to do miscellaneous tasks NOT REALTED to SQL Server database. These other Miscellaneous task might include sorting, has joins etc. So in case of memory pressure it might or might not take stolen memory depending on memory pressure.


Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL

There is no way to do that with COPY alone. You either have to pre-process or post-process the data.

Perhaps UNLOGGED tables can make this a little bit faster:

/* no WAL logging for better performance */
CREATE UNLOGGED TABLE test1 (v1 double precision);

COPY test1 FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.

/* this will rewrite the table */
ALTER TABLE test1
   ALTER v1 TYPE timestamp without time zone
      USING (to_timestamp(v1) AT TIME ZONE 'UTC');

/* this will dump the table to the transaction log */
ALTER TABLE test1 SET LOGGED;

This will allow you to keep the intermediate manipulations out of the transaction log, which is good for performance.


Can SQL Server replication be used for Point-of-Sale (POS)?

You have 3 types of replication:

Snapshot
Transactional
Merge

Reference Link - Selecting the Appropriate Type of Replication

Per Microsoft, these are easily broken down into two broad categories:

Server to Server
Server to Client

In the scenario of Point of Sale (POS). You are recommended to use Merge Replication due to it's Server to Client category and scenario.

Reference Link - Merge Replication

  Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

Reference Link - Consumer Point of Sale (POS) Applications

POS applications typically have the following characteristics, which an appropriate replication solution must address:

Most data is entered and updated at the remote sites.
Remote users must be able to make updates independently, without
requiring a connection to the central site.
Data updated at a remote site is not updated at any other sites;
therefore conflicts typically do not occur.
Some data should only be updated at the central site; for example,
data in product description tables.
Users synchronize data at scheduled times (such as the end of the
business day).
The application must control how long a remote site can remain
unsynchronized.
Some tables require filtering so that each store receives different
data for one or more tables. For example, each store receives
information only for products it stocks.
The application might require custom business logic to be executed
when data is synchronized.
The application might require that data be synchronized over the
Internet rather than through a dedicated connection.

Hope that helps!


Well, it is somewhat possible to have an Instance GUID, but only if you are including [msdb] in your backup / restore process. Assuming that this is indeed the case, then just check the service_broker_guid field in sys.databases for the [msdb] database:

SELECT [service_broker_guid]
FROM   sys.databases
WHERE [name] = N'msdb';

It will be different across each instance, but should be consistent across restores of backups of the same [msdb] database. This even appears to hold true for the Express editions. And while I have not tracked this value across updates and Service Packs, I don't see why it would change unless forced to manually.

If you want to create new SQL Server instances from a "master" set of backup files (including [msdb]) and need the new instances to be seen as different, then just run the following after the initial restore of the system databases:

ALTER DATABASE [msdb] SET NEW_BROKER;

NOTES:

The service_broker_guid field in sys.databases for the [master] and [model] databases is always set to 00000000-0000-0000-0000-000000000000 and cannot be changed via ALTER DATABASE [{database_name}] SET NEW_BROKER;.
If you are using this technique to distinguish instances of SQL Server Express LocalDB, then the the service_broker_guid field in sys.databases for the [msdb] database will initially contain the same value for all newly created instances. In this case, you just need to execute ALTER DATABASE [msdb] SET NEW_BROKER;, just like when creating new instances from a backup restore.


Collect data with central management servers and keep them in local table

Surely you can automate via CMS as explained in Automate Your Central Management Server Registrations (SQL Server Central, free registration required), which I've used and works well in 2012 and should be the same for 2014 as mentioned:

  Script 03_populate_cms_folders_from_inventory.sql could be scheduled as a SQL Agent job to regularly refresh the CMS folder
  contents
  Adding a section to [dbo].[ap_CMS_Populate_CMS_folder] to allow for SQL 2014 could be done (though I haven’t tested it)

Therefore by running the stored procedures from above article at the desired frequency via SQL Server Agent you can gather the data at your local table and later query for same.

One more way of doing this (apart from CMS) is to use Data Collector, which has quite significant changes in 2014 as explained in the article which also will help you automate the process.

Adding: Also you can use both as explained in:

Centralize Your Database Monitoring Process (SimpleTalk)

I hope the above articles/methods will help you in automating the tasks.


Getting only 4 rows per idea from ideas table

As the comments mentioned, your question is lacking a lot of details. Specifically what is the criteria / logic you want to use to select 4 rows when idea with id = DC889 has 10 rows, for example?
In any case, this will get you a random set of 4 rows for idea DC889:
SELECT * -- Replace * with only the columns you need, as * is bad practice
FROM IdeaTable
WHERE id = 'DC889'
LIMIT 4;


SQL Server alerts not sending notification emails to the operator

Check SQL Server Agent Properties: on the "Alert System" page, is "Enable mail profile" checked?

Sometimes with new installations, after I set up options on that page I've found notification emails for alerts aren't sent. A restart of the SQL Agent service often addresses that issue for me. - dave-mason

An article that explains more details: How to setup SQL Server alerts and email operator notifications by David Bird - joshua-washburn

Helpful debugging queries: Notes on debugging Database Mail problems by Kenneth Fisher - erik-darling


Transaction log reported full but is not

In all probability you had a large transaction that filled up your transaction log.  When the transaction failed it rolled back.  This cleared up your transaction log.

If that particular transaction needs to be processed then you can either

Modify your log size and max log size - This is best if this is a regular process.  Running a transaction log backup right afterwards is not a bad idea either.
Change the code to reduce the amount of transaction space needed.  This could be by using a minimally logged operation or by doing several smaller transactions (with a transaction backup in the middle).


how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server

You don't want them to be running with the same parameters in the first place. Very likely that you want to enable the 11g features. Things that you might want to make sure to be at least equal size are the memory footprint, temp tablespaces and undo tablespaces.

In Oracle 11g you might be tempted to use Autmatic Memory Management. There are some issues with it so it might be smarter to configure your shared pool and database buffer according to the 10g database. In the run check if you are not over or undersized.

Best is to hire a dba to help you with the setup, next best is to dive into Oracle® Database 2 Day DBA 11g Release 2 (11.2) There is a lot to learn from.


This will get you the top 100 with the highest average duration since the last service restart. This DMV is reset when SQL Server restarts, so if you need to go beyond that, you'll need to trace, use extended events, use auditing of some kind (built-in or manual), or invest in a 3rd party monitoring tool.

You can adjust that obviously, or go after different metrics such as physical or logical reads...

USE [Your Database Name];
GO

SELECT TOP (100)
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]),
    type_desc,
    cached_time,
    last_execution_time,
    execution_count,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes,
    total_logical_reads,
    last_logical_reads,
    min_logical_reads,
    max_logical_reads,
    total_elapsed_time,
    last_elapsed_time,
    min_elapsed_time,
    max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) = N'Your Database Name'
ORDER BY total_elapsed_time / execution_count DESC;