Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?

First, note that 1 billion rows can be handled effectively with a partitioned architecture on ordinary commodity server hardware.  Exotic shared nothing architectures will not be necessary for this data volume, however, you will probably get significant benefits from table partitioning.

Sharding is something different from horizontal partitioning, and implies a 'shared nothing' architecture, which is not supported by most versions of SQL Server1

SQL Server can support horizontal partitioning, and a shared disk architecture will be adequate for ~1 billon rows.

In SQL Server, you create a partition function selects a partition based on values or ranges of values in a column on a table, e.g.

create partition function F_AccPrdPart (int)
    as range left for values (
    180001                         -- Dummy value
   ,199012 ,199112 ,199212 ,199312, 199412 ,199512
   ,199612 ,199712 ,199812 ,199912 ,200012 ,200112
   ,200212 ,200312 ,200412 ,200512 ,200612 ,200712
   ,200812
   ,200901, 200902, 200903, 200904, 200905, 200906
   ,200907, 200908, 200909, 200910, 200911, 200912
   ,201001, 201002, 201003, 201004, 201005, 201006
   ,201007, 201008, 201009, 201010, 201011, 201012
   ,201101, 201102, 201103, 201104, 201105, 201106
   ,201107, 201108, 201109, 201110, 201111, 201112
   ,202012
   ,939999                         -- Dummy value
)
go

Then create one or more filegroups to allocate the partitions to.  For a large data set, these file groups could be set up on different physical volumes.  Note that direct attach storage will be much faster than a SAN for this in almost all cases.  In the example below, we would have created 6 filegroups called PartVol1-PartVol6.

One or more partition schemes can be created to allocate table partitions to filegroups based on the value of the partition function, e.g.

create partition S_AccPrdPart as partition F_AccPrdPart
    TO ([PRIMARY]
      ,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
      ,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
      ,[PartVol1], [PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6]
      ,[PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2], [PartVol3], [PartVol4], [PartVol5], [PartVol6], [PartVol1]
      ,[PartVol2]
      ,[PRIMARY]
      ,[PRIMARY])
go

This scheme is designed to partition on an accounting period.  Dates are also frequently used for this, although any key could be used.

You can create a table on the partition scheme as if it was a filegroup, e.g.

Create table FooTrans (
       FooTransID        int identity (1,1) not null
      ,AccPeriod         int not null
      ,[...]
) on S_AccPrdPart (AccPeriod)
go

Note that table is created on the partition scheme instead of a specified file group, and the clause specifies the column to be used as the partition key.  Based on the partition key, rows in the table will be allocated to one of the filegroups in the partition scheme.

Note: A rule of thumb for designing a partitioning scheme is that each partition should have a row count in the low 10's of millions, say between 10 and 50 million depending on the width of the rows.  The disk volume that the partition sits on should be fast enough to do a scan of at least a single partition in a few seconds.

Partitioning, Sharding and Shared Nothing systems

A bit of terminology seems in order here to disambiguate some of the discussion on this topic.

A 'shared nothing' system is a parallel system where the nodes do not have shared SAN storage, but use storage local to the node.  The classic example of this type of architecture is Teradata.  Shared nothing systems scale well to very large data sets as they have no central I/O bottlenecks.  I/O throughput scales with the number of nodes in the system.
A 'shared disk' system is one where one or more database servers share a single disk storage subsystem.  The database can be a single server with either local storage or attached to a SAN, or a cluster of servers attached to a shared SAN.  Systems of this type are constrained by the throughput available from the storage subsystem.
'Sharding' is a term used to describe splitting a database amongst multiple physical servers in a shared nothing architecture.  Various platforms will have greater or lesser support for sharded databases.  In Teradata circles, the term is not used because Teradata presents a transparent single system image to the clients, even though the physical architecture is a shared nothing type.Older versions of SQL Server have limited support for sharding through distributed partitioned views.  Microsoft now make a version of SQL Server 2008 R2 that supports a shared nothing architecture with a single system image, but this version is only available to OEMs and can only be purchased in a hardware bundle.

For 1 billion rows

For 1 billion rows (unless the individual rows are extremely wide) a shared nothing or sharded architecture is comfortably in the realms of overkill.  This type of volume can be handled on a single server of reasonable spec if it has an adequately fast disk subsystem.

Local direct attach disk is by far the most cost effective in terms of price for performance.  A single SAS RAID controller can take several arrays, and multiple controllers can be installed in a server.  Depending on the configuration, a modern 24-25 slot SAS array can put through thousands of IOPS or 1GB+/sec in streaming performance; a server with multiple PCI-e busses and multiple controllers can theoretically handle more.

The type of performance necesary to work with a 1 billion row database can be achieved quite easily and cheaply with commodity server hardware and direct attach storage of this type.  A SAN could also be used, but you might need multiple SAN controllers to get equivalent performance, and the hardware is likely to be an order of magnitude more expensive.

As a general recommendation, use direct attach storage for applications with heavy I/O
requirements unless you need really good uptime.  Configuration and
change control errors are a far bigger source of unscheduled downtime than hardware failure in modern data centre ops.

SANs can give you a more manageable storage platform if you have a large portfolio of
applications as they give you a range of centralised storage management facilities.  However, this comes at a steep price and getting high performance out of SAN based infrastructure is difficult and expensive.

1 Microsoft do make a parallel version of SQL Server, but it is only available through OEM channels bundled with hardware.  The versions available off the shelf do not support this capability.


SqlServer: can one table's PK be the FK of other two table's?

If I read your question correctly, you're describing the following:

TABLE project (
    project_id PRIMARY KEY
    ...
)

TABLE users (
    user_id PRIMARY KEY
    ...
)

TABLE proj_user (
    project_id,
    user_id,
    PRIMARY KEY (project_id, user_id),
    FOREIGN KEY (project_id) REFERENCES projects (project_id),
    FOREIGN KEY (user_id) REFERENCES users (user_id)
)

... which is entirely valid, yes.


Aggregating data - Getting data inserted after last time query was run

This is a classic senario that we experienced with out datawarehouse. We already had timestamp column, but still to process new records from the last time the query ran was difficult just like you are currently facing.

What we did was - we added 3 columns - LATEST, END_OF_DAY and PROCESSED columns to our main table where the data was loaded for further processing.

LATEST = All the data that gets loaded is defaulted to 1. Meaning that the data is latest.

PROCESSED = Will be set to 1 for the data that is already read and processed by our ETL job. The new data will be having a default of 0.

END_OF_DAY = This is another flag that is useful just in-case if you want to process all the records for the entire day. Just our requirement. Your case might be different.

Note: Above is just a simple example of how a sort of similar issue like you mentioned was addresses. Obviously there were many factors and business requirements that lead us to introduce such extra fields.

Also, you can use Calendar Table and Date/Time Functions to supplement what I mentioned above.

On the top of above, I would suggest to look into Data Loading Performance Guide to get benefit when the data is loaded and the Isolation level (especially RCSI) of the database as if the data is being continuously loaded then there would be blocking issues. Test it, balance it and then implement it !!


No, the time when a sequence is used is not recorded by PostgreSQL.

I would figure out the last_value of each sequence, wait for a week and do it again.  Then you can easily see which sequences have advanced and how much.


uncommitted xmin during vacuum, what to do?

A somewhat elegant solution that needs no downtime

I can find the specific rows, and update some insignificant column (comment in my case) in them to its current value:

SELECT id, comment FROM table WHERE xmin=359569171;

and then with the results, do updates for each row.

UPDATE table SET comment=(SELECT comment FROM table WHERE id=<ID>) WHERE id=<ID>;

As there were only three rows, I did not bother to create a formalised script.

Old brute force solution

This is what I could come up with first, and it is not the most elegant way to get rid of the message. I simply dump the table, delete all rows, and then restore it.
This is the shell script I created, someone may find it useful.

#!/bin/bash

DB=database_name

for TABLE
do
    SAVEFILE=/tmp/$TABLE.sql.gz
    echo "dumping $TABLE to $SAVEFILE"
    su - postgres -c pg_dump $DB --table=$TABLE --data-only | pigz -1 > $SAVEFILE"
    echo "disabling triggers for $TABLE"
    su - postgres -c "psql  $DB -c 'ALTER TABLE $TABLE DISABLE TRIGGER ALL'"
    echo "deleting the contents of $TABLE"
    su - postgres -c "psql $DB -c 'DELETE FROM $TABLE'"
    echo "restoring contents of $TABLE"
    zcat $SAVEFILE | su - postgres -c "psql $DB"
    echo "re-enabling triggers for $TABLE"
    su - postgres -c "psql $DB -c 'ALTER TABLE $TABLE ENABLE TRIGGER ALL'"
done

The drawback of this approach is that the application must be stopped during this operation to avoid inconsistencies, as triggers must be stopped for the table. This causes about half an hour of downtime in my case.

I tried this approach on a copy of the database and it works, but I am still searching for a better solution that may be done without any downtime.


You can use a query like below to get all tables with dependencies;

WITH fkeys AS (
SELECT

  c.conrelid AS table_id,
  c_fromtablens.nspname AS schemaname,
  c_fromtable.relname AS tablename,

  c.confrelid AS parent_id,
  c_totablens.nspname AS parent_schemaname,
  c_totable.relname AS parent_tablename

FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace

JOIN pg_class c_fromtable ON c_fromtable.oid = c.conrelid
JOIN pg_namespace c_fromtablens ON c_fromtablens.oid = c_fromtable.relnamespace

JOIN pg_class c_totable ON c_totable.oid = c.confrelid
JOIN pg_namespace c_totablens ON c_totablens.oid = c_totable.relnamespace
WHERE
  c.contype = 'f'
)

SELECT
  t.schemaname,
  t.tablename,
  fkeys.parent_schemaname,
  fkeys.parent_tablename

FROM pg_tables t
LEFT JOIN fkeys ON  t.schemaname = fkeys.schemaname AND
                    t.tablename =  fkeys.tablename
WHERE
  t.schemaname NOT IN ('pg_catalog', 'information_schema')

ORDER BY
  3 NULLS FIRST,
  4 NULLS FIRST

Moreover, the query below provides you foreign key details. It provides relation types by using primary key definition. Note that it does not check unique constrains.

WITH fkey AS (

SELECT
  n.nspname AS fkey_schema_name,
  c.conname AS fkey_name,

  c_fromtablens.nspname AS table_schema_name,
  c_fromtable.relname AS table_name,

  c_totablens.nspname AS foreign_table_schema_name,
  c_totable.relname AS foreign_table_name,

  c.conrelid,
  unnest(c.conkey) AS fkey_field_num,

  c.confrelid,
  unnest(c.confkey) AS fkey_foreign_field_num

FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace

JOIN pg_class c_fromtable ON c_fromtable.oid = c.conrelid
JOIN pg_namespace c_fromtablens ON c_fromtablens.oid = c_fromtable.relnamespace

JOIN pg_class c_totable ON c_totable.oid = c.confrelid
JOIN pg_namespace c_totablens ON c_totablens.oid = c_totable.relnamespace
WHERE
  c.contype = 'f'

),

pkey AS (

SELECT
  c.conrelid,
  unnest(c.conkey) AS conkey
FROM pg_constraint c
WHERE
  c.contype = 'p'
)

SELECT
  fkey.fkey_schema_name,
  fkey.fkey_name,
  fkey.table_schema_name,
  fkey.table_name,
  fkey.foreign_table_schema_name,
  fkey.foreign_table_name,
  a.attname AS field_name,
  a_f.attname AS foreign_field_name,
  CASE
    WHEN pkey.conrelid IS NULL AND pkeyf.conrelid IS NULL THEN 'N-N'
    WHEN pkey.conrelid IS NOT NULL AND pkeyf.conrelid IS NOT NULL THEN '1-1'
    WHEN pkey.conrelid IS NULL AND pkeyf.conrelid IS NOT NULL THEN 'N-1'
  END AS relation_type
FROM fkey
JOIN pg_attribute a ON a.attrelid = fkey.conrelid AND a.attnum = fkey.fkey_field_num
JOIN pg_attribute a_f ON a_f.attrelid = fkey.confrelid AND a_f.attnum = fkey.fkey_foreign_field_num
LEFT JOIN pkey ON pkey.conrelid = a.attrelid AND pkey.conkey = a.attnum
LEFT JOIN pkey pkeyf ON pkeyf.conrelid = a_f.attrelid AND pkeyf.conkey = a_f.attnum


Failed Connection Attempts will still increment the Process ID next to be assigned

Here is an example:

I will restart mysql on my PC (MySQL 5.5.12) and connect for the first time

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.12-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Then I connect to mysql with an intentional bad password

I then connect to mysql with the right password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.12-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

and BINGO a MySQL Connection ID of 3 instead of 2

There is a record of it in the Global Status variables

mysql> show global status like 'abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 1     |
+------------------+-------+
2 rows in set (0.00 sec)

mysql>

You evidently have connections still coming in from somewhere who can no longer authenticate. Maybe you could try using netstat and watch port 3306. If your have removed certain user@host values from mysql.user, then there is no need for real concern in terms of the process ID established and used.

You may want to run this query now and then...

SELECT hostaddr,COUNT(1) connections FROM
(SELECT LEFT(host,LOCATE(':',host) - 1) hostaddr
FROM information_schema.processlist
WHERE LOCATE(':',host) > 0) A
GROUP BY hostaddr;

This will tell you how many connections are coming in per IP address or DNS Name


Simple Oracle query hangs depending on WHERE clause

following the advice of a_horse_with_no_name,

  Please show us the execution plan for the second query –
  a_horse_with_no_name 4 hours ago

I have posted the relevenat parts of the execution plan... however I can't make heads or tails of what is wrong with it from that....

I followed Mat's advice and guess that lack of indexing is the issue.  Because there is no indexing, it must be as Matt says, that this simple query is doing a full table scan, which could take longer than the few minutes I have been waiting for the query to complete...

Useful link about the concept of indexing: http://www.orafaq.com/node/1403

  Is that column indexed? Otherwise you'll get a full table scan, which
  can take a while if the table is big. – Mat 7 hours ago

  select index_name, column_name from dba_ind_columns where index_owner
  = 'THE_SCHEMA' and table_name = 'YOUR_TAB' order by index_name will get you all the indexed columns. Look at the explain plan to see what
  it is doing (look into set autotrace or use a GUI that has that
  feature, like sqldeveloper) – Mat 7 hours ago

Thanks for your help guys.  Let me know if I might be wrong in that assumption, or if you might have additional insight a_horse_with_no_name....


enable logging of password-like patterns in mysql client history

Working on a project with db columns like "password_hash", this behavior was at first very confusing and then over time, simply maddening.  There appears to be no way to disable this.

Here's an evil hack that I only resorted to out of desperation after other workarounds failed:

cp /usr/bin/mysql ~/bin     # copy to local bin dir
vim ~/bin/mysql             # vim can edit binary files
/PASSWORD                   # search for the offending string
RPWXUK4FD [ESC]             # overtype w/ string unlikely to ever match
:wq                         # save & quit

This assumes that you already have a ~/bin directory and it is listed before /usr/bin in your $PATH


Determine databases specified Login has access to on SQL Azure

-- Run this query on master

SELECT A.name as userName, B.name as login, B.Type_desc, default_database_name, B.*
FROM sys.sysusers A
     FULL OUTER JOIN sys.sql_logins B
     ON A.sid = B.sid
WHERE islogin = 1 and A.sid is not null

-- Run this on user databases

SELECT DB_NAME(DB_ID()) as DatabaseName, *
FROM sys.sysusers


Is there Such Thing As The Opposite of Unique Constraint

Subqueries are not allowed in a CHECK constraint, or in the WHEN clause of a trigger, but you can use them inside a trigger function, something like this:

CREATE FUNCTION my_little_trigger_function()
RETURNS TRIGGER
AS $$
DECLARE
   row_count int;
BEGIN
   row_count := (SELECT COUNT(*)
                 FROM ...
                 WHERE ... = NEW.x ...);
   IF (row_count = 0) THEN
       RAISE EXCEPTION 'I''m sorry, Dave. I''m afraid I can''t do that.';
   END IF;
   RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_little_trigger
BEFORE INSERT ON my_little_table
FOR EACH ROW
EXECUTE PROCEDURE my_little_trigger_function();


Yes, your understanding is correct - this allows evenly distribute load between nodes & to handle more traffic.

Why would you need replicas if they just idle? If you're afraid of inconsistent data, then just set correct consistency levels to get strong consistency...


addition in datetime column during insertion in mysql

I doubt what are you trying can be done in a single go.You could do it specifying timezone for all inserts for a particular country.
Or something like this after insert:

UPDATE....
  CASE WHEN Region='India' THEN event_time = event_time+INTERVAL 330 MINUTE
  CASE WHEN Region='America' THEN event_time = event_time+INTERVAL someother MINUTE
  CASE...


how to get a better execution plan for empty sets?

To reproduce your issue, I created three tables with 1000 rows. The view definition does left outer joins on the primary keys of the table which should allow for join elimination. Here's the code:

CREATE TABLE dbo.BASE_TABLE (ID BIGINT NOT NULL, PRIMARY KEY (ID));
INSERT INTO dbo.BASE_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.COL1_TABLE (ID BIGINT NOT NULL, COL1 VARCHAR(1), PRIMARY KEY (ID));
INSERT INTO dbo.COL1_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'A'
FROM master..spt_values;

CREATE TABLE dbo.COL2_TABLE (ID BIGINT NOT NULL, COL2 VARCHAR(1), PRIMARY KEY (ID));
INSERT INTO dbo.COL2_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'A'
FROM master..spt_values;

GO

CREATE VIEW HugeView AS
SELECT b.ID AS B_ID, c1.COL1, c2.COL2
FROM dbo.BASE_TABLE b
LEFT OUTER JOIN dbo.COL1_TABLE c1 ON b.ID = c1.ID
LEFT OUTER JOIN dbo.COL2_TABLE c2 ON b.ID = c2.ID;

GO

The following query access all three tables when it shouldn't have to:

DECLARE @Val BIGINT = 1;
SELECT 1 FROM dbo.HugeView
WHERE (Col1 = 'A' AND @val = 1) OR (Col2 = 'A' AND @Val = 0);

Note the thickness of the arrows in the actual plan:

If I add a RECOMPILE hint then I only get two tables in the actual plan as desired:

Probably the most straightforward fix is to use UNION ALL. Here's the estimated plan:

At first the plan might look bad because it references all three tables. However, the highlighted startup predicate filter is important:

What that means is that branch of the query plan may not be executed depending on the value of the parameter. If I get the actual plan you can see that only one half of the plan was executed:

dbfiddle link for everything in this post.

Note that depending on the complexity of the view the startup filter may not appear in the optimal place. However it should save you some amount of unnecessary work.


How to get TNS connect strings (like tnsping) to check a dblink via a session?

I do not think that is possible.
Someone or something needs access to the Filesystem to read the tnsnames.ora.
When using the DB-link oracle will get the definition for RDBX from the tnsnames.ora file. This is transparent to the user, so the user will not notice. As @EdStevens suggested I would try to solve the political issues and work with the DBA.
Or, ask the OS-admin to have a look for you.


SQL Question: Selecting different language versions based on availability

You can create a query to get the two language as two column, as translation, and use COALESCE to get the value that is present.
If you have one language that have all the data you'll want to use it as the anchor and write a LEFT JOIN (if word is the field with the data)

SELECT COALESCE(e.word, d.word) word
FROM   table D
       LEFT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name

otherwise a FULL JOIN is needed

SELECT COALESCE(e.word, d.word) word
FROM   table D
       FULL JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name

Edit
As ypercube pointed out MySQL don't support the FULL JOIN, but it can be simulated

SELECT COALESCE(e.word, d.word) word
FROM   table D
       LEFT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name
UNION
SELECT COALESCE(e.word, d.word) word
FROM   table D
       RIGHT JOIN table E ON e.lang = 'E' and d.lang = 'D' and e.name = d.name


Query that was working on MySQL is not working on PostgreSQL

My educated guess (while proper information is missing): You declared bin_number as character(n), char(n) or just char data type.

character(n) is an outdated, inefficient, weird data type, but defined in the SQL standard. char (without double quotes!) or char(1) are aliases for character(1).

Typically you do not want to use it. It's a blank-padded data type where all strings containing only blanks or nothing (i.e. the empty string '') are considered identical. Effectively all of these are treated as empty strings because trailing blanks are considered insignificant.

SELECT '  '       <> '';  -- TRUE
SELECT '  '::text <> '';  -- TRUE (identical, because text is default
SELECT '  '::char <> '';  -- FALSE (!!)

Use varchar or text instead.

SQL Fiddle with a matrix of comparisons.

More:

Compare varchar with char
Any downsides of using data type “text” for storing strings?
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

It's either that, or you have NULL values in the column where you had empty strings in MySQL. Either way, the table definition (\d tbl in psql) in the question and some sample values would allow answers instead of speculation.


What permissions do I need to grant on service accounts on SQL root path when relocating?

I was able to get guidance from the following MSDN link: Configure Windows Service Accounts and Permissions. The pertinent section (bookmarked in the link) is "File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups".

The short version for the solution is that either Read, Execute or Full Control are needed on the leaf-level subdirs under the system root (DATA, LOG, FTDATA, etc).

On my host, NTFS permissions weren't propagating from the mountpoint volume root or the folder by which the SYSDB LUN were mounted. Applying NTFS permissions on the leaf-level corrected the issue.


pgBouncer - multiple servers with same username but different password

There is a good workaround at the link in your question.
Another thing you can try is to use auth_user and auth_query with pgBouncer so that it fetches the password from the respective database. I am not sure if that works or if pgBouncer caches the password for each user.


Are my MongoDB servers right in setting?

At first you should move to a 3 node replica set to ensure High Availability. At your case i would suggest to add an arbiter to your set, and give higher priority to the server A to ensure that will be the Primary.

1) Probably your question is about atomicity rather than consistency (correct me if i am wrong) the most safe choice is command findAndModify but you need to be careful because on high concurrency you may get delays.

2) Calling fsync in your code after every operation is a bad idea because you may get stale periods. fsync just flush the pending operations to disk, it doesn't ensure atomicity of an operation.


Does SQLite choose wrong query plan when rowID is the second column of an index?

In theory, there's nothing wrong with having the rowid as a second column in an index.
And in practice, with the current SQLite version, it works as you expected it to:

sqlite> .eqp on
sqlite> SELECT value FROM event
   ...> WHERE netAddr = 250 AND ID > 100 AND ID < 110
   ...> ORDER BY ID DESC LIMIT 1;
--EQP-- 0,0,0,SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?)


Endpoint modification in SQL Server mirroring

There is no need of modifying the endpoints. This is the process:

ALTER DATABASE IICS SET PARTNER OFF (Server2)
Change IP address in Server2
Change IP address in Witness
Change IP address in Server1
ALTER DATABASE IICS SET PARTNER = 'TCP://SERVER1:Port' (Server2)
ALTER DATABASE IICS SET PARTNER = 'TCP://SERVER2:Port' (Server1)
ALTER DATABASE IICS SET WITNESS = 'TCP://WITNESS:Port' (Server1)


A lot of ad-hoc queries impact on server

It can impact the amount of memory available. Each query plan takes a little memory away from SQL Server.

Also, each time a plan has to be created, SQL Server has to compile it. This means it uses CPU which could be used for other processes. If you have a high CPU contention than that could become a problem.

This query can help you see how large the query plans actually are:

SELECT TOP 1000
    st.text ,
    cp.cacheobjtype ,
    cp.objtype ,
    cp.refcounts ,
    cp.usecounts ,
    cp.size_in_bytes ,
    cp.bucketid ,
    cp.plan_handle
FROM    sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE   cp.cacheobjtype = 'Compiled Plan'
    AND cp.objtype = 'Prepared'
ORDER BY cp.usecounts DESC;


There is no general way to answer this (other than try it on your test server and see).  GIN supports many different operators, like tsvector or trigrams, and they have different characteristics.  In newer versions, GIN indexes use compression which can be pretty impressive when the same key value shows up over and over again.  But that level of compression depends on the ordering of the rows.

For example, if I index a single text column with a lot of duplicate values (~50 million rows, with ~1.5 million distinct values, using the btree_gin extension for the GIN), I get 2010 MB for the b-tree index and 435 MB for the GIN index.  So no, the GIN is not always bigger.  But in general (i.e. other than with btree_gin) you don't index the same types of data with GIN as you do with b-tree, so a direct comparison of sizes does not make much sense.


How to design a versioned database with PostgreSQL?

Use liquibase (you can write 99% of it in sql now, not xml)
Store liquibase schema in VCS (like git)
branch different versions in git
loop and apply branches to either different database schemas (tenant per schema. you could set schema at top of liquibase file) or loop and apply to different databases (tenant per database, would need to change liquibase db per loop)

see http://msdn.microsoft.com/en-us/library/aa479086.aspx for architectural discussion (applies equally to postgres). shared schema does not apply here.


Storing objects in key/value stores - serialized or separate values?

It really depends. You've identified one of the primary flaws with a key->value design (structure).

Your former example is more like a document-oriented database, which is kind of an extra implementation on top of a key->value store. Using something like JSON for your document is one approach. Some document-oriented DB's will optimize/index that for you.

Your latter example is more of a traditional key->value approach.

To answer your question more specifically, one problem with storing a JSON string/object as your value would be that every time you want to access part of that value, you generally have to parse (and possibly decompress) the whole thing. The problem with the key->value design is a lack of structure, everything is almost flat. How are you going to represent a class/object this way, and what if you have a web of complicated classes/objects, etc.?

https://en.wikipedia.org/wiki/NoSQL#Types_and_examples


select specified number of unique IDs where second column is unique

SELECT id,
       sec
FROM (
  SELECT id,
         sec,
         row_number() OVER (PARTITION BY sec ORDER BY id DESC) AS rn
  FROM my_table
) t
WHERE rn = 1
ORDER BY id DESC
LIMIT 4;

SQLFiddle example: http://sqlfiddle.com/#!15/1ca01/1


Buffer Pool and Multiple instances of SQL Server

The buffer pool in SQL Server isn't shared across intances.

DBCC DROPCLEANBUFFER removes all "clean" pages from the buffer pool, "clean" pages are pages which haven't been modified since being read into the buffer pool.

DBCC FREEPROCCACHE removes all cached execution plans from the plan cache.


I happen to work for an IoT\M2M company and we have been using SQL Server just fine for years...  Much of the architecture is all event\transaction driven and process based on an \time\event\status and have denormalized the data for multi-purpose uses.

Now, ideally when you experience the type of growth that we have over the last few years with millions of connected devices your problem becomes one of carrier relations and how are you going to get better rates and roaming agreements from your carriers...

GPRS, SMS, Data... It all cost's but I digress. Your going to want to store LAT\LONG in datatypes of FLOAT. I think GOOGLE does it with a length of FLOAT(10,6)?

MySQL or Postgres Spatial with GIS extensions is another option.

Good Luck!


SQLPlus configuration problem or firewall issue

I end up finding the answer :)
On my test server on the same network as the DB I made a tcpdump and I notice that my connection started on the primary IP and the DB reply through the secondary IP. There was a request on the DNS server in the middle.
I made the same test on my ansible-test server and found out that there was problem on then DNS request and fail the connection. Basically, it was not able to understand the secondary IP name.
So this end up being a weird networking problem.
Thanks All.


DATA BACKUP from SQL Server Express to SQL Server 2012

Yes. The database format is identical across all editions. An Express Edition database for sure can be restored on a non-Express Edition (you fail to mention whether is a Standard or an Enterprise Edition). The other way around it may be subject to restrictions, Express Edition will refuse to attach or restore a database that:

exceeds the size limitation of Express Edition
contains Enterprise Edition only features, like partitioned tables. On SQL Server 2012 anything from sys.dm_db_persisted_sku_features  will block an Express Edition. This restriction was lifted on SQL Server 2016 SP1.

It is important that both servers (the Express Edition and the non-Express one) be at the same version.


How can I speed up an ASC sort on a column that only holds an integer between 0 and 9 across multiple millions of rows?

I would recommend using the same syntax for all of your WHERE clauses (your index would build for negative values of bar, but you never SELECT those)

CREATE INDEX has an implicit order of ASC, so it unlikely that not specifying the indexing order alone is the source of your problem (as you stated the DESC sort is faster).

Naively, I'd also recommend that you include the id column in your index, but this may negatively affect insert/index rebuild performance, as well as overall disk and memory usage.

CREATE INDEX foo_bar_idx
  ON foo(bar ASC, id ASC)
  WHERE bar > 0

You could try converting all zeros to NULL (and setting NULL as the default), then changing your WHERE clauses to WHERE bar IS NOT NULL. So your create table code would look like

CREATE TABLE foo (
  id TEXT NOT NULL,
  bar INTEGER NULL DEFAULT NULL,
  PRIMARY KEY (id)
);

Once you have NULLs established, you could alternatively play around with the NULLS FIRST / LAST parameters in your index, rather than using a WHERE clause, but that is unlikely to improve performance.


How MySQL processes the query largely (and primarily) depends on the indexes on the table.

If no indexes exist, then of course a full table scan is required. Now, you are looking for a way to reduce the search space based on conditions. However:

Your query uses one "equals" condition
Your query uses two "range" conditions
It uses one LIKE clause with a prefix '%'

The LIKE clause, beginning with '%' negates any use of an index, unless it is a covering index, which is not the case in your query. This part of the clause negates any use of an index on the name column.

Well, you can use as many "equals" conditions as you like, where possible, but up to one range condition only, when using an index.

Any index you'll want to put on that table will start with the condition column, since it is the one column where you say condition = 1, which is an equality check. You are then left to choose any of the two columns on which you place a range condition. Choose the one which will most likely reduce more rows (leaving less rows in the search space).

So, you options are:

KEY (condition, qty)
KEY (condition, price)

So that MySQL will first match the condition column in the index, then follow up to the next column, whichever it is.

MySQL does not read conditions from start to end nor from end to start. It just notes down the various conditions, then tries, when possible, to find an index which satisfies them (or part of them). It then depends on the order of columns in the index -- not on the order by which the columns appear in the query.


SQL Server: Has anyone used the large-page allocations tuning option?

Thomas Kejser from Microsoft (blog) regularly presents that the 834 trace flag is the only go-faster trace flag for SQL Server. He would caveat that, I'm sure, with plenty of testing!


This looks mostly complete, though the BookingID field in Guest seems a little pessimistic. What will you do if a guest wants to stay at the hotel again in the future? As you already have a relation between Guest and Booking via GuestID, you can probably remove BookingID from Guest. A similar thing can be done in Bill as, if you know the BookingID then you know the GuestID. So GuestID can be removed from Bill … if you choose, of course. You don’t need to make either change.


SQL Outer Join while conditioning on an extra column

You don't need subqueries. You only need to use both columns in the ON condition of the FULL JOIN:

SELECT
    -- t1.*,     -- adjust, keep only
    -- t2.*,     -- needed columns
    COALESCE(t1.date, t2.date)
                  AS date,
    COALESCE(t1.identifier, t2.identifier)
                  AS identifier,
    t1.attribute1 AS a_attribute1,
    t1.attribute2 AS a_attribute2,
    t2.attribute1 AS b_attribute1,
    t2.attribute2 AS b_attribute2
FROM table1 AS t1
     FULL JOIN table2 AS t2
     ON  t1. date = t2. date
     AND t1. identifier= t2. identifier
-- WHERE
--     (t1.date = '2000-01-01' OR t2.date = '2000-01-01')
 ;

If we want date and identifier combined in one column each, we can use COALESCE() function.


swappiness:

= 0 --> crash if you run out of memory -- not nice
small --> best
large --> might swap mysql preemptively, leading to poor performance

pool_instances -- should not be more than pool_size/1G; the specific value makes only a small difference in performance.  (Don't worry about it.)  Some say 16 should be the max number of instances, but I have not heard 'why'.
CPU spikes -- Usually a missing index or a poorly phrased query.  Find it and fix it.  (And/or ask for advice)
The buffer_pool(s) cache data and indexes.  It is (roughly) a least-recently-used cache.  It is also used for delaying index updates due to writes.
52G/64G -- reasonable.  It obviously can't hold the entire 300GB, but that may not be an issue -- especially if most of the action is in isolated portions.  Example:  Only "recent" data is actively accessed.  Counter example:  UUIDs have terrible performance when the data is bigger than the buffer_pool.

For further analysis of settings, post SHOW VARIABLES; and SHOW GLOBAL STATUS;.  (Not here; there is a size limitation.)


Which AWS database services are designed for storing logging records like log4j or log4perl?

Since you're looking at storing and querying 135,000,000 * 30 * 10 records that likely wouldn't benefit from traditional RDBMS features, I think Hadoop would be the way to go.

My experience is exclusively with Microsoft Azure - if you're not attached to Amazon you might check it out. Either way, Hadoop is open source so the majority of operations and activity should be the same regardless of your platform provider. I'd go with an option that allows you to test the performance of different cluster sizes so you're only paying for what you really need (especially with the 10x increase as an unknown right now).

The following link, despite its Azure focus, gives you a good tutorial of how to query log4j records with Hive. Amazon EMR appears to have great documentation.

Key points for your situation:
Create a table structure over your files:

    CREATE EXTERNAL TABLE log4jLogs
    (t1 string, t2 string, t3 string, t4 string, t5 string, t6 string, t7 string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ';'
    STORED AS TEXTFILE LOCATION 'yourdrive:///logs/';

With the table defined you can then query similar to SQL:

    SELECT t4 AS sev, COUNT(*) AS ErrorCount
    FROM log4jLogs
    WHERE t4 = '[ERROR]'
    AND INPUT__FILE__NAME LIKE '%.log' GROUP BY t4;

With the EXTERNAL definition you can have multiple files and the table will include them all, so just drop them in the appropriate directory. If you're keeping 30 days worth you may want to get fancier and separate days into different folder structures/partition your Hive table.


sysadmin security error creating Reporting Services cache refresh plan in Report Manager

If you are on CU7 or CU8, you may be hitting the issue mentioned this KB article since cache refresh plans also make use of SQL Agent. If so, updating to the newest CU should fix it


Informatica Source Qualifier not working from Windows ODBC data source (system DSN) from Excel xlsx file

To use an Excel file as a source you need to define a named range containing the cells with data (that's Step 1 of the instruction you referenced in the question).

All the named ranges found in the file will be shown in the Select tables pane. After you select a table (i.e. a range) and click OK, a new source will be created.

You can also consider converting the Excel spreadsheet to the CSV format and using simple flat files as a source. If you're in a Windows environment and have have Excel installed, there's a VBS script that can help you automate this process.


Restoring 40 GB Oracle backup to get db objects into Oracle 11g XE

In case of expdp/impdp backup

impdp ... CONTENT=METADATA_ONLY

This will import only the DB objects, but no data. Should you require some data, you can get them for example on table, tablespace, or schema level:

impdp ... CONTENT=DATA_ONLY TABLES=my_table,my_table_2...
impdp ... CONTENT=DATA_ONLY TABLESPACES=tablespace_1,tablespace_2...
impdp ... CONTENT=DATA_ONLY SCHEMAS=schema_1,schema_2

In case of exp/imp backup

You can try partially importing the tables or tablespaces (syntax same as with impdp):

imp ... TABLES=(jones.accts, scott.emp, scott.dept)
imp ... TABLESPACES=tablespace_1,tablespace_2...

In case of rman backup

From this answer on partial restore from RMAN backup:

  RMAN is a physical backup - it copies files - and not surprisingly is restricted to physical restoration. As a consequence, its supported granularities are: block, file, tablespace and database.

So if you are lucky, and can get by with restoring a tablespace, it might work if there are different tablespaces in the backup and the one you need fits into the 10GB limit.

Using Oracle 11g EE

If you have access to a full Oracle 11g (beware, the development license prohibits using it for internal data processing), just import the full DB, and do a selective export if none of the above is successful.


SSPI handshake failed with error code 0x8009030c - only stops when SSMS is closed

Possibly the developer is using a 3rd-party tool that provides a plug-in for SQL Server Management Studio. The 3rd party plug-in is started with SQL Server Management Studio and is trying to connect to the SQL Server using the credentials the process was initially started with.

If the initial process owner of the 3rd-party tool is running with an un-privileged account that does not have access to he SQL Server instance the developer is connecting to, then you will possibly see multiple error messages in the ERRORLOG file and in the Windows Event Log on the target server (see Link below for examples).

If the initial process owner of the 3rd-party tool is not allowed to delegate permissions on its own object, then you might see the SSPI Handshake error.

There is a Microsoft Blog article titled Common ‘SSPI handshake failed’ errors and troubleshooting which provides you with some ideas on how to find the main cause.

Most of the SSPI Handshake erros are because a process can not handover/delegate the permissions during the SSPI Handshake to another user/process.


You can script a table relatively easy using the UI of course:

This will output a CREATE TABLE script and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist).

But if you're trying to automate this (e.g. generate the create table script in code), it is a little more cumbersome. The above scripting option doesn't just pull the entire CREATE TABLE DDL from a single location in the metadata; it does a whole bunch of magic behind the scenes in the code to generate the eventual CREATE TABLE script (you can use Profiler to see where it gets its data, but you can't see how it assembles it). I suggested an option for this:

http://connect.microsoft.com/SQLServer/feedback/details/273934

However this was met with very few votes and was quickly shot down by Microsoft. You may find it much more worthwhile to use a 3rd party tool for generating schema (I've blogged about this).

In SQL Server 2012 there are new metadata functions that allow you to get much closer than the work you have to do in 2005, 2008 and 2008 R2, piecing together column information from the metadata (which has a lot of caveats, for example if it's decimal you have to add the precision/scale, if [n[var[char]] you have to add the length specification, if n[var]char you have to cut the max_length in half, if it is a MAX you have to change -1 to MAX, etc etc). In SQL Server 2012 this part is a little easier:

SELECT name, system_type_name, is_nullable FROM
  sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)

Results:

name                   system_type_name  is_nullable
--------------------   ----------------  -----------
name                   nvarchar(128)     0
object_id              int               0
principal_id           int               1
schema_id              int               0
parent_object_id       int               0
type                   char(2)           0
type_desc              nvarchar(60)      1
create_date            datetime          0
modify_date            datetime          0
is_ms_shipped          bit               0
is_published           bit               0
is_schema_published    bit               0

I've blogged about this, too.

Arguably this is much closer to your targeted CREATE TABLE statement than a convoluted approach using sys.columns, but there is still a lot of work to do. Keys, constraints, text in row options, filegroup information, compression settings, indexes, etc. It's a very long list and I'll once again suggest you look at a 3rd party tool for this instead of, at the risk of repeating an over-used analogy, re-inventing the wheel.

That all said, if you need to do this through code but you can do it outside of SQL Server, you can consider SMO/PowerShell. See this tip and the Scripter.Script() method.


Trigger to log Users Assigned to website

Your best option here is to create a Stored procedure to handle everything.

On user login, the stored procedure is called with the username and password(Encrypted) for example.

This stored procedure would do the following

Validate the login.
Update a Lastlogin timestamp
write the login audit row (irrespective of successful or unsuccessful login.

This would be the best way to handle the process. I would then wrap the above in a transaction, to ensure that the audit record is always created for any login. If the transaction fails, the user will have to login again.

Here is a very basic example for the sake of keeping it simple. It can definitely be improved on for your specific use case, but this is broken down for easy reading and logic, especially around the password validation as encryption should be used but is not for this example :)

CREATE PROCEDURE ApplicationLogin @Username nvarchar(255), @Password nvarchar(255), @IpAddress
AS
BEGIN
   DECLARE @UserID int;
   BEGIN TRANSACTION;

   SET @UserID = (SELECT UserID FROM Logins WHERE Username = @Username AND Passwword = @Password );

   IF @UserID > 0
   BEGIN
     UPDATE Logins SET LastLogin = GETDATE() WHERE UserID = @UserID;
   END

   INSERT INTO UserAudit (Username, UserID, Password, IpAddress, TimeStamp)
   Values(@Username, @UserID, @Password, @IpAddress, @Timestamp);

   COMMIT TRANSACTION;

   SELECT @UserID --On your client, check if the returned @UserID is >0. If it is, then successful login. If not, then unsuccessful.

END

The login check and LastLogin Update could probably be combined. You could also use a try catch instead of a transaction. In this case I believe the difference in negligible. Also, as mentioned, you would want to have your passwords encrypted. What i liked to do, was encrypt the password in the client, and only compare encrypted strings. That means you never need to decrypt the password. Its always encrypted and therefore i believe is more secure.


Index for a complex similarity query Postgres

Your only chance is to index all possible conditions, perhaps PostgreSQL will use bitmap operations to cover the ORs. Perhaps it will think that a sequential scan is cheaper and go for that.

CREATE INDEX ON mymatview (lower(d));

CREATE INDEX ON mymatview USING gin (d gin_trgm_ops) WHERE length(d) > 4;


How to determine queries per hour executed by a particular MySQL user?

I talked to the MySQL engineers, and the philosophy of the metrics you will find on performance_schema is to store only global metrics (even if it can also store individual queries and events), as in, no temporal (differential) breakdown. The way they suggested would be to take hourly snapshots of the interesting metrics (query or user statistics) and then store those somewhere else. Because performance_schema stores metrics on memory since restart, you would only have to sample those every hour, or average them over the SHOW GLOBAL STATUS like 'Uptime' seconds.

I normally use performance_schema using sys, as it is way easier to query (but all data there comes from performance_schema). You can install it even if you are using an older MySQL version and it will use the performance_schema data already stored, assuming it is enabled (default).

sys.user_sumary contains the summary status per user, including total statements, total_latency, total_connections, etc.
sys.user_summary_by_statement_type contains the breakdown per user and statement type.

You could enable individual query<->user storage, but you would convert
performance_schema into the general log, and either storing only a small window, or -if increased the backlog- taking a huge amount of memory.

So to "know that how many queries a user has executed this hour" query the above tables, wait 1 hour, and query it again, and substract the first value from the second. You will have available, the amount of queries, inserts, updates, deletes, rows read, and other resources used. There are tools that will automate the collection for you. If for some reason you cannot use performance_schema, there are plugins such as user_statistics that will create new tables in information_schema with similar information (this was the only way on older MySQL versions).


How can I apply heuristics to optimise an expression tree?

I've just figured out how to do this, so I'll answer it in case anyone else has a similar problem.

Introduced SELECTs as far down the tree as possible, to reduce the number of comparisons needed.
Introduced PROJECTs as far down the tree too, to reduce the number of columns.
Used a JOIN wherever a CARTESIAN PRODUCT was followed by a SELECT. (There weren't any in my tree but this is what's done apparently).

My final tree looks like this:


Different results returned when ignoring Primary Key index. Daylight Savings Time related bug?

I've got an answer that I don't like :

  Indexed searches convert the "value or range to be matched " into a
  UTC time to be compared against the indexed value.

  Non-indexed searches convert the "stored value" from UTC to the session time zone
  for comparison.

That explains why I get different results with and without an index.  To me it feels like the implementation is broken, but I don't know what I would do for an easy fix. /shrug

Fortunately we can work around the issue in this case, but we are reconsidering how to handle this in the future.


Pivot functionwith multiple conditions (concatenated in one header)

CREATE TABLE Sales(SaleDest int, Tons decimal(18,2), DateShipped datetime, DataSet char(2));
INSERT INTO Sales VALUES
(2311, 20.4,  '20170401', 'AC'),
(2311, 9,     '20170402', 'AC'),
(2313, 1.4,   '20170405', 'AC'),
(2313, 1.4,   '20170501', 'BU'),
(2314, 29.56, '20170401', 'AC'),
(2314, 20.0,  '20170505', 'BU'),
(2320, 12,    '20170404', 'BU'),
(2320, 12,    '20170505', 'BU');
GO

I'd suggest you to use a temp table or a table variable to store the data source for the pivot solution.

I've simulated it in this way:

SELECT SaleDest,
       Tons,
       DataSet
       + '-' + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
       + '-M' + Format(MONTH([Sales].[DateShipped]),'00') as Caption
FROM   Sales;
GO

SaleDest | Tons  | Caption
-------: | :---- | :----------
    2311 | 20.40 | AC-2017-M04
    2311 | 9.00  | AC-2017-M04
    2313 | 1.40  | AC-2017-M04
    2313 | 1.40  | BU-2017-M05
    2314 | 29.56 | AC-2017-M04
    2314 | 20.00 | BU-2017-M05
    2320 | 12.00 | BU-2017-M04
    2320 | 12.00 | BU-2017-M05

Now you should build columns names:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ','
                                   + QUOTENAME(DataSet
                                   + '-'
                                   + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
                                   + '-M'
                                   + Format(MONTH([Sales].[DateShipped]),'00'))
                   FROM Sales
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SELECT @cols;
GO

| (No column name)                          |
| :---------------------------------------- |
| [AC-2017-M04],[BU-2017-M04],[BU-2017-M05] |

And finally the dynamic pivot query:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT DISTINCT ','
                                   + QUOTENAME(DataSet
                                   + '-'
                                   + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
                                   + '-M'
                                   + Format(MONTH([Sales].[DateShipped]),'00'))
                   FROM Sales
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @CMD =
'SELECT SaleDest, ' + @cols + ' FROM
(
SELECT SaleDest,
       Tons,
       DataSet
       + ''-'' + CAST(YEAR([Sales].[DateShipped]) AS varchar(4))
       + ''-M'' + Format(MONTH([Sales].[DateShipped]),''00'') as Caption
FROM   Sales) SRC
PIVOT
(
    SUM(Tons) FOR Caption IN (' + @cols + ')
) PVT'

EXEC(@cmd);
GO

SaleDest | AC-2017-M04 | BU-2017-M04 | BU-2017-M05
-------: | :---------- | :---------- | :----------
    2311 | 29.40       | null        | null
    2313 | 1.40        | null        | 1.40
    2314 | 29.56       | null        | 20.00
    2320 | null        | 12.00       | 12.00

dbfiddle here


You can use the following:

 WHERE before <=  date(at, (station || ' months') )

Please note that according to the documentation:

  "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.


syntax error at or near "current_timestamp" while creating partition table

what am I doing wrong here?

You are incorrectly using non-literal values for partition bound specifications. Documentation states that partition_bound_spec is

IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
  TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )

that is, only literals or special keywords MINVALUE and MAXVALUE are allowed.  current_timestamp is a function, not a literal value.

As suggested in the comments, you will need to generate your create table ... partition ... statement dynamically if you want to use the function return value as the partition bound.


Fast paginated result, for various filter clauses

I believe you should be aiming for a plan that avoid any actual sort operation, and "stops short" as soon as possible.

To avoid the sort (and "materializing" the inner view), your sort order must match exactly the index columns, or your where clauses must be strict equals only on all the leading columns. Otherwise there will be a need to sort subsets, and that will require evaluating the whole inner view.

Here's an example with a hypothetical foo table:

create table foo (a number, b number, c varchar(100));
-- fill with dummy data
exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');
create index foo_ix on foo(a, b, c);

Closest simple equivalent to your select:

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by c
  )  i
) where r between 5 and 10;

The explain plan isn't good:

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   163 | 14833 |     5  (20)| 00:00:01 |
|*  1 |  VIEW                 |        |   163 | 14833 |     5  (20)| 00:00:01 |
|   2 |   COUNT               |        |       |       |            |          |
|   3 |    VIEW               |        |   163 | 12714 |     5  (20)| 00:00:01 |
|   4 |     SORT ORDER BY     |        |   163 |  9291 |     5  (20)| 00:00:01 |
|   5 |      INLIST ITERATOR  |        |       |       |            |          |
|*  6 |       INDEX RANGE SCAN| FOO_IX |   163 |  9291 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<=10 AND "R">=5)
   6 - access(("A"=3 OR "A"=4) AND "B"=3)

The count is too late, and isn't actually (I think) "stopping things short" in this case.

Add the index columns to the order (might not meet your requirements, sorry):

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by a, b, c
  )  i
) where r between 5 and 10;

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |   163 | 14833 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                |        |   163 | 14833 |     4   (0)| 00:00:01 |
|   2 |   COUNT              |        |       |       |            |          |
|   3 |    VIEW              |        |   163 | 12714 |     4   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |        |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| FOO_IX |   163 |  9291 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<=10 AND "R">=5)
   5 - access(("A"=3 OR "A"=4) AND "B"=3)

At least we got rid of the sort. Now let's try and "stop short":

select * from (
  select rownum r, i.* from (
    select a, b, c
    from foo
    where a in (3,4) and b = 3
    order by a, b, c
  )  i where rownum < 10
) where r > 5;

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     9 |   819 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                |        |     9 |   819 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY      |        |       |       |            |          |
|   3 |    VIEW              |        |     9 |   702 |     4   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR  |        |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| FOO_IX |     9 |   513 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R">5)
   2 - filter(ROWNUM<10)
   5 - access(("A"=3 OR "A"=4) AND "B"=3)

This might be enough for your query: notice the count stopkey (rownum < magic, doesn't kick in with between in what I've seen) and the "rows" column - the inner scan doesn't have to bother about fetching more rows after it's found N.

With the above, you'll still be reading N rows from the table.

You could limit that if all the search criteria are indexed: do the above filtering but retrieving only the ROWID from each match rather than all the columns, and then access the table by ROWID.

select * from foo where rowid in (
 select rid from (
    select rownum r, i.* from (
      select rowid rid
      from foo
      where a in (3,4) and b = 3
      order by a, b, c
    )  i where rownum < 10
  ) where r > 5
);

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    78 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS               |          |     1 |    78 |     6  (17)| 00:00:01 |
|   2 |   VIEW                      | VW_NSO_1 |     9 |   108 |     4   (0)| 00:00:01 |
|   3 |    HASH UNIQUE              |          |     1 |   225 |            |          |
|*  4 |     VIEW                    |          |     9 |   225 |     4   (0)| 00:00:01 |
|*  5 |      COUNT STOPKEY          |          |       |       |            |          |
|   6 |       VIEW                  |          |     9 |   108 |     4   (0)| 00:00:01 |
|   7 |        INLIST ITERATOR      |          |       |       |            |          |
|*  8 |         INDEX RANGE SCAN    | FOO_IX   |     9 |   594 |     4   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY USER ROWID| FOO      |     1 |    66 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("R">5)
   5 - filter(ROWNUM<10)
   8 - access(("A"=3 OR "A"=4) AND "B"=3)

This does not work if any of the search fields are not in the index. And make sure you trace this with real data & usual search criteria to see if it materially makes a difference - it might actually be worse especially for low values of M (which is probably the case you want to be the fastest) or high values of N-M.


SQL Server 2016 Multiple Availability Groups and instances

A) does each sql instance also need to be its own windows cluster or could one cluster suffice for all the named instances?

SQL Server doesn't have cluster membership, servers do. If the server is part of a windows cluster then all instances of SQL Server could be part of the same cluster. It is not possible to have a server be part of more than one cluster.

  B) Will this work at all?

It may... only if you have an unordinary small workload. Just the worker threads alone would make me assume that you're not going to have a good time. This coupled with having 4 instances per server, that don't communicate with each other, that are all trying to step on each others' toes (so to speak) leaves me thinking this, "I am really glad I won't have to admin this!".

My personal take

I would step away from thinking of AGs such as this. If you want to consolidate, that's great and I'm all for it! However, it needs to be done in a way that doesn't end up hurting you or your customers.

If it were me, and I was tasked with the same thing, I'd immediately push back on "6" servers. I don't yet know how many I'll need... unless of course we're not going to do any scientific research or testing - in which case I'd escalate my concerns.

We don't know things such as the number of total databases, how much log generation each database creates, etc., which we'd need to start gaining an understanding of what will be needed.

Undoubtedly you can consolidate these. Is it even the right thing to put them all in a single cluster? I wouldn't.

Each cluster is a fault domain, and while there are some really awesome features such as distributed availability groups that still ends up being more than a single cluster. Think of it this way, all of your AGs are in a single cluster - what happens if you have an issue with said cluster? Are all of the AGs now down? Maybe. Probably. Either way it's not something I'd want to be dealing with at 5 am.

Some of the main things you'll see when you have too many AGs or databases in AGs:

Worker thread exhaustion
Slow DMV queries
Slow Cluster API responses
Slow or Unresponsive Instances of SQL Server

My advice would be to gather metrics:

Number of database
Number of needed replicas for HA and DR
Log generation rate
CPU/Disk/Memory usage

That'll give you a start on how the pieces will need to fit together.
There needs to be much more diving into how this will turn out, but hopefully this will point you in the right direction.


Fastest way to set up MySQL replication from backup

For the Sake of this Post

IP of Master is 10.20.30.100
IP of Slave1 is 10.20.30.110
IP of Slave2 is 10.20.30.120
IP of Slave3 is 10.20.30.130

ALL InnoDB
If all your tables are InnoDB, you can do this:
STEP 01 : If you do not have binary logging enabled, you need to enable it. Here is how you can tell:
Login to MySQL and run this
mysql> SHOW MASTER STATUS;

If you get something like this:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000266 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

then, it is enabled. If you get Empty Set, then it is not enabled.
To enable it, add this to your my.cnf
[mysqld]
log-bin=mysql-bin

and restart mysql
STEP 02 : Create a Replication User on the Master
Run this
GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.*
TO replicator@'%' IDENTIFIED BY 'replicationpass';

STEP 03 : Test the Replication User
On each of the Slaves, run this in Linux
MASTER_IP=10.20.30.100
mysql -h${MASTER_IP} -ureplicator -preplicationpass -ANe"SHOW VARIABLES LIKE 'hostname'"

Make sure it echos the hostname of the Master from all Slaves.
STEP 04 : Clear all binary logs on the Master
Simply run
mysql> RESET MASTER;

To clear away all binary logs and start with a fresh one
STEP 05 : Dump all your data to a text file
From
MASTER_IP=10.20.30.100
echo "CHANGE MASTER TO master_host='${MASTER_IP}'," > LoadSlaveData.sql
echo "master_post=3306,master_user='replicator'," >> LoadSlaveData.sql
echo "master_password='replicationpass'," >> LoadSlaveData.sql
echo "master_log_file='bogus'," >> LoadSlaveData.sql
echo "master_log_pos=2;" >> LoadSlaveData.sql
DUMP_OPTIONS="-single-transaction --master_data=1 --routines --triggers"
mysqldump --uroot -p ${DUMP_OPTIONS} --all-databases >> LoadSlaveData.sql
echo "START SLAVE;" >> LoadSlaveData.sql

Line 28 will have the binary log and position for replication because of --master_data=1.
STEP 06 : Load the SQL file into each Slave
Login to Each Slave and Load that File
mysql -h10.20.30.110 -uroot -p < LoadSlaveData.sql
mysql -h10.20.30.120 -uroot -p < LoadSlaveData.sql
mysql -h10.20.30.130 -uroot -p < LoadSlaveData.sql

THAT'S IT. NO DOWNTIME
ALL MYISAM or SOME InnoDB / SOME MyISAM
What you proposed in your question is more of a fit for databases with any MyISAM tables
I have recommended the same technique over the years

Nov 09, 2012 : How can I reinitialize some MyISAM tables without restarting MySQL?
Jul 24, 2012 : How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
Mar 15, 2012 : Why is mysqlhotcopy slower compared to rsync?
Jun 17, 2011 : What is the difference between XtraBackup and rsync?

I even mentioned this in StackOverflow : MySql - create replication with minimal downtime


Databases missing after exporting server and rebuilding master DB

I have all Databases (mdf and ldf files) in this folder D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA how to restore them and not lose all functionality?

When you rebuild master, it loses track of where the user databases are.  You just need to attach them, and recreate the logins for any orphaned users.
And also consider linked servers, scheduled jobs and everything else which is stored outside the user databases and was scratched by the rebuild.


SSIS runs outside of SQL Server's memory space so it's going to be contending with the memory allocated to the OS.

How much memory you allocate to SSIS is entirely dependent on how you define your SSIS packages. To go fast, which is what SSIS is designed for, it's going to push as much data into memory so that operations can be performed on it and slap it into the target system. You might only have 10GB of data from your source system but in theory you could consume more than 10GB of memory by using semi or fully blocking components as they will result in memory copies and such.


Need for reaching data through clustered index with a non-clustered index

The reason for this is that the "fixed" physical location of your row - the RID (or row identifier) might (and will!) change over time - think page splits that occur when a row needs to be inserted into a table on a page that's already full.

Updating those RIDs in all the nonclustered indices that exist on a given table is quickly becoming both a hassle, and a huge performance killer. You might have 5, 10, 20 nonclustered indices on your table, and SQL Server would have to scan all those indices (basically scanning the whole index, all rows in the index, and that 10, 20 times) and update all RIDs.... that's just not practical - very quickly so.

If you store the value of the clustering index as a "row pointer" instead, that value should typically never change - and most definitely it doesn't need to be updated every time a page is split. Yes, it does involve a second index seek operation - the key lookup - but for simple scenarios, retrieving a single row or a few rows, that's still much more efficient than anything else.


How to export database structure from Firebird?

There are several ways to do this:

A metadata-only backup using gbak:

gbak -backup -meta_data employee employee.meta.fbk

And then restore that backup.
A metadata-only restore using gbak:

gbak -create employee.fbk mytest.fdb -meta_data

This can be useful if you already have a backup with data.
Exporting the DDL of a database with ISQL (or another tool)

isql -ex -o ddldump.sql /path/to/your/database.fdb

And then use that script to populate a new database.

For options 1 & 2 see gbak: Backup & Restore Recipes: Meta Data Only
For option 3 see ISQL: Command Line Switches.


First, take a pg_basebackup, just in case. Then set your archive command to /bin/true. That will allow Postgres to delete everything to its own satisfaction that has already been deleted. To restore from backup you need the WAL since your last basebackup. Restoring to a point in time before is now impossible I’m afraid. When this is finished, set the archive command correctly. And get some monitoring for this!


For your case i would recommend dbForge Server Data Compare for SQL Server from Devart. It looks very similar to the Redgate SQL Data Compare mentioned by Moisés González.

It allows you to compare your database with the backup file directly or with a second database.

It shows you the differences between the databases:

only in backup / source database
only in target database
diffent rows / fields

You can select the data to restore or generate scripts for inserting in your current database.

I use this software for our customers to restore deleted rows and even a restore of 5 million rows and update of another 5 million rows have been done very quickly. It works very well and can execute also very large repair scripts (the largest one i have generated was about 10 gig).

There is a 30 day trial available which may fit your needs. So you can give it a try without any risk.


How to make mongos to be in a replica set?

You cannot have mongos as a replica set, it is just "proxy" between application and cluster. You can have multiple mongos-services. Normally you create mongos service to the same machine where you have the application. So, every application node has its own mongos and your application connects to that localhost mongos. If you mongos is down, your (single) application is down already, probably.

Of course, nothing prevents you to create multiple mongos nodes and put load balancer to the front of them. But I wouldn't recommend that.


Multiple Filters for Databases on SQL Server Server Level Audit

Try this, which slightly modifies your WHERE clause to correctly follow the SQL Server operator precedence rules:

CREATE SERVER AUDIT [PCIAudit]
TO FILE
(   FILEPATH = N'D:\SQLAuditLogs\'
    , MAXSIZE = 2048 MB
    , RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 0
    , ON_FAILURE = CONTINUE
)
WHERE ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2')
    AND [server_principal_id] <> 277
    AND [server_principal_id] <> 278
    AND NOT [statement] LIKE 'ALTER INDEX%REBUILD%'
    AND NOT [statement] LIKE 'ALTER INDEX%REORGANIZE%';
GO

ALTER SERVER AUDIT [PCIAudit]
WITH (STATE = ON);
GO

Notice the round brackets ( and ) in this:  ([database_name] = 'DataBase1' OR [database_name] = 'DataBase2') - they tell the where clause to allow either DataBase1 or Database2.  As you have it, the where clause is set to allow either DataBase1 or Database2+server_principal_id<>277.


sql server - NoSync subscriptions will need to be dropped and recreated

You can update the registry of the Subcriber

here you will see the actual status

select * from [Publish_Database]..syssubscriptions

here the update for all articles subscribes

update [Publish_Database]..syssubscriptions set status=2 where srvname='server'

After that the snapshot should become effective again


Update statement is giving an invalid column name error, even though the column exists

You need to perform the update in dynamic SQL too (or just create the table with all columns in the first place). The error is happening because the update with the new column is being parsed before the dynamic SQL has run.

As an aside, you are probably only getting this error if you try to execute the stored procedure and choose 'Display estimated execution plan' in the toolbar. This is a parsing/binding error that will happen in many scenarios when generating an estimated plan involving #temp tables, but shouldn't happen during normal execution.

So to avoid the error during retrieval of an estimated plan, put any "new" column references in separate dynamic SQL blocks. But to really avoid the problem: stop retrieving estimated execution plans. They're pretty worthless anyway.


How to select mutiple values with like clause

This case is a good candidate for using REGEXP instead of LIKE.

SELECT *
from table
WHERE column REGEXP '^(AAA|BBB|CCC)'

REGEXP is case insensitive. For case sensitive searches use REGEXP BINARY.

SELECT *
from table
WHERE column REGEXP BINARY '^(AAA|BBB|CCC)'

REGEXP can't benefit from indexes, so it may only be an efficient option if LIKE wouldn't use indexes either. With multiple LIKE statements that could individually access an index (like in your case if column is indexed and the like has a wildcard at the end), performing UNION of individual queries with a single LIKE would probably be most effective in a large dataset.

However, if your query filter primarily by, say, a range of dates, then a single REGEXP may be faster than multiple LIKEs ORed.

For more information about REGEXP in MySQL see this and this.


What is considered as a document in mongoDB?

In the context of MongoDB, a document means any piece of valid BSON* (binary JSON). The word does not have the everyday meaning of, say, an MS Word file or PDF.

A collection is a container for zero or more documents. There is no requirement for all documents in a collection to have the same structure. Indeed, that is one of the drivers behind the NoSQL movement. So it would be possible to store a shopping cart, a birth certificate and a recipie for chocolate cake in the same collection. (Though possible it would be an exceedingly poor design.) A collection is also the boundary for some management tasks, like copying, and authorisation.

* BSON is actually an important distinction as there are more data types than JSON. For example, the default primary key (_id field) is an ObjectId, which doesn't exist as a standard JSON type.

For more context see:

Documents; and
MongoDB Extended JSON.

BSON and MongoDB Extended JSON are JSON-like in terms of document structure, but definitely not interchangeable.


First of all you need to create a directory on the database.

Create or Replace Directory IMP_DIR as 'Path\Path\';

Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)

GRANT Read, Write ON DIRECTORY IMP_DIR TO user;

Then, include the directory parameter within the import statement

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR


Create one-to-many to two fields in different table

I would examine your model and see if it needs some normalization.

It would appear that the two types of model are sub-types of a common model.  I have found this kind of implementation requires some design.  Using the same id sequencing for both tables opens up a number of options for implementation.

Creating an intermediate Model table containing the Model id, modelTypeID, and any common fields contained in the subtypes also helps.  Depending on the query requirements you may be able omit either the subtype tables or the model table from some queries.

If there are few columns in the sub-type tables (EffModel and BkgModel) you may want to add them as nullable columns in the Model table.  Alternatively, you can implement the normalized model and use a UNION query, or outer joins to both subtype tables.

In any case you will need to ensure the integrity of the data and ensure the records created match the ModelType.


Is it a bad practice to have several mutually exclusive one-to-one relationships?

The different types of cars are an instance of a general problem that surfaces over and over again in data modeling.  It is called "generalization/specialization" in ER modeling, and "superclass/subclass" in object modeling.

An object modeler uses the inheritance features built into the object model to solve the problem quite easily.  The subclasses simply extend the superclass.

The relational modeler is faced with a problem.  how to design the tables so as to emulate the benefits that one would get from inheritance?

The simplest technique is called single table inheritance.  Data about all types of cars are grouped into a single table for cars.  There is a column, car_type, that groups together all the cars of a single type.  No car can belong to more than one type.  If a column is irrelevant to, say, electric cars,  it will be left NULL in the rows that pertain to electric cars.

This simple solution works well for the smaller and simpler cases.  The presence of a lot of NULLs adds a tiny bit to storage overhead, and a little bit to retrieval overhead.  The developer may have to learn SQL three-valued logic if boolean tests are done on nullable columns.  This can be baffling at first, but one gets used to it.

There is another technique, called class table inheritance.  In this design, there are separate tables for gas_car, electric_car, and hybrid_car, in addition to a combined table, car, for all of them.  When you want all of the data about a specific kind of car, you join the car table with the appropriate specialized table.  There are fewer NULLs in this design, but you do more joining.  This technique works better in the larger and more complex cases.

There is a third technique called shared primary key.  This technique is often used in conjunction with class table inheritance.  The specialized tables for the subclasses have, as their primary key, a copy of the primary key of the corresponding entry in the car table.  This id column can  be declared to be both the primary key and a foreign key.

This involves a little extra programming when new cars are to be added, but it makes the joins simple, easy, and fast.

Superclasses and subclasses happen all the time in the real world.  Don't be afraid.  But do test your initial design for performance. If your first attempt is simple and sound, you'll be able to tweak it to speed it up.


In MySQL if I create a user and grant privileges to the user, will dropping the user revoke privileges?

The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables.

see http://dev.mysql.com/doc/refman/5.7/en/drop-user.html

PROXY is a GRANT privilege so the proxy privilege should be deleted also.


How to see percentage of existing values for all columns in mysql table?

I have a Dynamic SQL solution

PROPOSED QUERY

SET group_concat_max_len = 1048576;
SET @GivenDB = 'mydb';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
')*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

EXAMPLE

Let's use a sample table I made up for another question

mysql> use timyash
Database changed
mysql> show tables;
+-------------------+
| Tables_in_timyash |
+-------------------+
| mytable           |
+-------------------+
1 row in set (0.00 sec)

mysql> desc mytable;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| id           | int(11) | NO   | PRI | NULL    | auto_increment |
| transmitted  | int(11) | NO   |     | NULL    |                |
| connect_time | int(11) | NO   |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |        78073 |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |       596408 |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

PROPOSED QUERY DISPLAYED

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT COUNT(id)*100/COUNT(1) id_pct,COUNT(transmitted)*100/COUNT(1) transmitted_pct,COUNT(connect_time)*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.02 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |         100.0000 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED WITH NULL DATA

Let's change two rows to have NULL column data

mysql> alter table mytable modify column connect_time INT NULL;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update mytable set connect_time = 1/0 WHERE id in (2,5);
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |         NULL |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |         NULL |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

Let's run it again

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |          71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2014-12-29 15:25 EST

In response to your last comment, use this version

SET group_concat_max_len = 1048576;
SET @GivenDB = 'timyash';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',
GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
SELECT @sql\G
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

You do not need to check the datatype with this one.

To show you that it works ...

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',
    -> GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
    -> ','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT SUM(IF(IFNULL(id,'')='',0,1))*100/COUNT(1) id_pct,SUM(IF(IFNULL(transmitted,'')='',0,1))*100/COUNT(1) transmitted_pct,SUM(IF(IFNULL(connect_time,'')='',0,1))*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |          71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>


Get a column from the latest row by another column

This is a good use for a window function, row_number()

select
   x.created,
   x.OriginalMsgId,
   x.Status
from (SELECT
         RN = row_number() over (partition by r.originalMsgId order by Created desc),
         r.originalMsgId,
         r.status,
         created
      from ReplayHistory r) x
where x.RN = 1

Another way is using MAX with a derived table.

SELECT
   r.originalMsgId,
   r.status,
   r.created
from ReplayHistory r
inner join
    (select originalMsgId, max(created) dt from ReplayHistory group by originalMsgId) x on
    x.dt = r.created
    and x.originalMsgId = r.originalMsgId

Notes on performance


Dedicated SQL instance vs multiple database same server instance

assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases)

You already have addressed the concerns.

IMHO, depending on the VM capacity (memory, CPU and disk space), you should consolidate databases to one instance.

This gives you advantage of managing one instance wherein you can have different rules for different databases - e.g. maintenance (index rebuilds, reorgs, stats update, backup schedules) etc depending on their criticality.

When your database size and usage increases, thats the time to move out the heavy used ones out of the instance on a different VM.

Make sure you have proper Disaster recovery inplace e.g. use logshipping/AlwaysON, etc so if the VM crashes or fails, you can failover the databases to secondary server.

Also, refer to my answer for

  Which is better: one database per application, or just one database?.


What is a good way to determine the performance I should expect from a PostgreSQL server?

I think determining the expected performance is a lot harder than planning for capacity.  My guess is that is what you are really trying to do so I will go over what I consider to be the most important rules for OLTP workloads here.

You want the working data set to fit in memory if possible.  Calculating performance where this is not the case depends on how often the disks are hit and what sort of disk I/O.  If you can't fit all of it in memory, at least you should try to fit the most commonly used data, and all commonly used indexes in memory.
You want to have available at least one core per two concurrent queries.  Better is one core per concurrent query.

Both of these are a little tricky to calculate and they depend on row size, number of pages in the table, number of dead tuples in the table, which in turn depends partly on the number of concurrent updates.

Beyond this, performance will slowly degrade, hopefully gracefully.  Estimating performance when these are or aren't met is very difficult because it is very fact specific.


Microsoft SQL Server v13 bulk add users from active directory

Am I missing something? Am I doing something wrong?

It's unusual to add a large number of individual active directory logins to a SQL Server.  Instead add logins for Active Directory Groups to take care of most of your users.

See, eg: https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server


Your query can be simplified to:

SELECT COUNT(DISTINCT t.id)
FROM cs AS t
  JOIN uploads AS u
    ON t.upload_id = u.id
WHERE t.user_id = 1
  AND t.upload_id IN (1,2,3)
  AND u.term > ''
  AND t.amount > 0 ;

and also:

SELECT COUNT(*)
FROM cs AS t
WHERE t.user_id = 1
  AND t.upload_id IN (1,2,3)
  AND t.amount > 0
  AND EXISTS
      ( SELECT *
        FROM uploads AS u
        WHERE t.upload_id = u.id
          AND u.term > ''
      ) ;


Find how many SQL Server instances are installed

Here are some options using what is already available on a server with Windows Server and SQL Server installed.

You can list Services on a server with a name starting with SQL Server (...:

sc \\IP_or_Name query | find /I "SQL Server ("

IP_or_Name is the IP or DNS name of the Windows Server 20xx server.

Or with Powershell:

Get-Service | Where-Object {$_.DisplayName -like "SQL Server (*"}

You can look at the content of the registry key HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL using Regedit.

You can also use Powershell:

Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'

Key HKLM:\Software\Microsoft\Microsoft SQL Server\InstalledInstances gives a list of name separated by line breaks.

Powershell commands can be executed remotely using Invoke-Command:

Invoke-Command -ComputerName computer -ScriptBlock { command } -Credential username

Using sqlcmd you can get a list of the instances which broadcast their names on the network:

sqlcmd -L

Or with Powershell which has the advantage of showing Version number:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()


MyISAM indexes tend to get corrupted when the server crashes (eg, power failure) or is otherwise rudely terminated (kill -9).  You did the 'right' thing by running CHECK and REPAIR.

The datafile size is a bit worrisome; that is worse than the usual (just index issue).  The crash may have happened between "writing" and "flushing", since MyISAM is not careful about syncing.

Changing to InnoDB is the 'right' thing to do.  For each of your tables, do

ALTER TABLE table_name ENGINE=InnoDB;

Caution:  The disk footprint for your tables will double or triple; check that you have enough disk space.

InnoDB automatically recovers after power failure and virtually any other crash.


Very large table makes the application performance too slow

How long do you think it will take to create an additional index?
How often are DML executed? Is this table being read often?

Given this monstrosity has over 1 billion rows I would create separate filtered table with columns SelectID, IDType, UnID, Batch and apply there appropriate indexes.


It took me almost 2 days to resolve, but the following solved my issue

Even with the latest drivers installed the snapshot agent will still use the older version drivers that come with SQL Server 2019.

This has to do with the Path variable in your environment. Have a look at System Properties, Advanced System Settings, Environment Variables, System variables, Path.

Unless you have changed your defaults you will find %SystemRoot%\system32, %SystemRoot% at the top and C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\  further down.

What you will not find is a path reference to %SystemRoot%\SysWOW64\ and %SystemRoot%\SysWOW64\1033 which is where the latest OLE drivers get installed on 64 bit systems. The driver files are msoledbsql.dll and msoledbsqlr.rll

So, when the snapshot agent starts it will use the OLE driver files installed relative to its binary somewhere under the C:\Program Files\Microsoft SQL Server\ branch as it cannot find any others in the Path.

When you insert %SystemRoot%\SysWOW64\ and %SystemRoot%\SysWOW64\1033 into your system Path variable (make sure this is above any references to C:\Program Files\Microsoft SQL Server...) and the restart your SQL Server instance you should find that your problem goes away.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e2c23cda-9e78-4514-bae6-27c6d34767a4/sql-server-2019-snapshot-agent-error?forum=sqldatabaseengine


GLFiscalYearPeriods is a table and this comma implies a cross join. (Cartesian product)

It seems that the query returns some values for each Fiscal Year.

Given this tables:

create table a (id int, foo int);
create table c (id int);
insert into a values (1,1),(2,2),(3,3);
insert into c values (10),(20);

select * from a, c;

select * from a cross join c;

id | foo | id
-: | --: | -:
 1 |   1 | 10
 2 |   2 | 10
 3 |   3 | 10
 1 |   1 | 20
 2 |   2 | 20
 3 |   3 | 20

db<>fiddle here

Just another example:

create table a (id int, foo int);
create table b (id int, foo int);
create table c (id int);
create table d (id int);
insert into a values (1,1),(2,2),(3,3);
insert into b values (1,1),(2,2),(3,3);
insert into c values (10),(20);
insert into d values (1),(2);

select * from a join b on a.id = b.id       ,     c join d on d.id = a.id ;

  Msg 4104 Level 16 State 1 Line 1
  The multi-part identifier "a.id" could not be bound.

but if you cross-join it:

select * from a join b on a.id = b.id  cross join  c join d on d.id = a.id ;

id | foo | id | foo | id | id
-: | --: | -: | --: | -: | -:
 1 |   1 |  1 |   1 | 10 |  1
 1 |   1 |  1 |   1 | 20 |  1
 2 |   2 |  2 |   2 | 10 |  2
 2 |   2 |  2 |   2 | 20 |  2

db<>fiddle here


How do I know my Oracle database is correctly configured and running?

I found the issue. The DB wasn't starting because I forgot to update /etc/oratab after the dbca install. Should have updated the db parameter with "restart:Y".
cdb1:/opt/u01/app/oracle/product/19.3.0/dbhome_1:Y


SQL Server 2000 dateformat permanent change

As stated in the comments, dates are not stored as "dates".  They are actually stored as numbers.  So there is no need to worry about that side of things.  You can change the default output though by changing your language setting.

EXEC sp_configure 'default language', '23';
GO
RECONFIGURE
GO

This is the setting for British English.  You can see all of them displayed in the syslanguages system view.  syslanguages also contains the default date format.  In the case of British English it is dmy.  American English is mdy.


Is there a maximum length constraint for a postgres query?

For current PostgreSQL version (up to 9.5), queries are received by a backend in a Stringinfo buffer, which is limited to MaxAllocSize, defined as:

#define MaxAllocSize   ((Size) 0x3fffffff) /* 1 gigabyte - 1 */

(see http://doxygen.postgresql.org/memutils_8h.html)

So a query is limited to 1 gigabyte (2^30) in size, minus 1 byte for a terminating null byte.

Should a client try to send a larger query, an error looking like this would come back:

  ERROR:  out of memory
  DETAIL:  Cannot enlarge string buffer containing 0 bytes by N more bytes.

where N is the size of the query.

Be aware that a query just below 1GB might require large amounts of memory to be parsed, planned or executed, in addition to that 1GB buffer.

If you need to push a large series of literals into a query, consider the alternative of creating a temporary table, COPY rows into it and have the main query refer to that temporary table.