Search on concatenate columns I have this :

CREATE TABLE users
(
    first_name varchar(60) NOT NULL,
    last_name varchar(60) NOT NULL
);

INSERT INTO users
    (first_name, last_name)
VALUES
    ('Sylvia ', 'Hoeks'),
    ('Harrison', 'Ford');

I get the list like this

SELECT first_name || ' ' || last_name AS user_full_name FROM users;

But I would like to be able to search on the column user_full_name. Is it possible without a subquerie or CTE (like below)?

WITH users_list AS (
    SELECT first_name || ' ' || last_name AS user_full_name
    FROM users
)

SELECT * FROM users_list WHERE user_full_name ILIKE '%lvi%';

I tried with the first column !?

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE 1 ILIKE '%lvi%';
/*
ERROR:  operator does not exist: integer ~~* unknown
LINE 1: ...|| last_name AS user_full_name FROM users WHERE 1 ILIKE '%lv...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
*/

I would like to be able to do something like this.

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE user_full_name ILIKE '%lvi%';

For the moment I do:

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE first_name ILIKE '%lvi%' OR last_name ILIKE '%lvi%';

Fiddle if needed : https://www.db-fiddle.com/f/c3HANw8M9W7kSVeEwidpne/0

The usual way to do this, is to use a derived table:

select *
from (
 select first_name,
        last_name,
        first_name || ' ' || last_name AS user_full_name
 from users
) t
where user_full_name ilike '%lvi%';

That is extremely basic and generic SQL and any obfuscation layer (aka "ORM") should be able to deal with that.

Other options you have:

1. Use a view

Create a view with the calculated function and tell your obfuscation layer to use that view.

2. Create a "virtual" column

Postgres doesn't really have calculated columns (like e.g. Oracle or SQL Server) but you can simulate them with a function:

create function user_full_name(p_row users)
  returns text
as
$$
  select concat_ws(' ', p_row.first_name, p_row.last_name);
$$
language sql;

This can be used like this:

select u.user_full_name
from users u
where u.user_full_name ilike %lvi%'

Note that the prefix for the column name is required otherwise it does not work. I don't know if you can teach your obfuscation layer to create SQL like that.

3. Populate an additional column with a trigger

CREATE TABLE users
(
    first_name varchar(60) NOT NULL,
    last_name varchar(60) NOT NULL,
    user_full_name text
);

create or replace function update_full_name()
  returns trigger
as
$$
begin
  new.user_full_name := concat_ws(' ', new.first_name, new.last_name);
  return new;
end;
$$
language plpgsql;

create trigger update_full_name_trigger
  before update or insert on users
  for each row
  execute procedure update_full_name();

Then you can simply write:

select user_full_name
from users u
where user_full_name ilike %lvi%';

That is probably the solution that is the easiest to be integrated into an obfuscation layer.


Asynchronous commit on Postgres over iSCSI with BBU storage It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller.

In my environment I am on a VM whose block storage is connected via iSCSI, where the iSCSI node is using DRBD and both the primary and secondary nodes have battery backed up controllers.

FWIW, here's the specifics:

PostgreSQL 9.3
CentOS 6.3 (VM)
VMware 5.1
Infiniband QDR transport
SLES 11 iSCSI nodes
DRBD
LSI BBU SCSI controllers with CacheCade Pro 2.0 (in-use).

Can I safely turn off synchronous_commit?

It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller.

Actually, that's kind of backwards. Turning synchronous_commit off is unsafe no matter what, in that it permits the database to lose recently committed transactions if it crashes. This is true with, or without, BBU or crash-safe SSD storage.

What synchronous_commit = off does is allows you to trade durability for speed when you're on storage where flushes are expensive so you want to batch them up and want to avoid client latency while apps wait for flushes. It has much less effect on storage where flushes are fast, since you do less waiting for commits to happen - so it's pretty much all downside and little benefit.

In general, you should not set synchronous_commit off globally. As the documentation advises, you should SET LOCAL synchronous_commit = off in specific transactions that you don't need to be durable, leaving it enabled otherwise. That way you might lose transactions you're not so fussed about, but not the ones that're making important changes.

If you can't afford potential loss of transactions that clients think are committed at all, you may instead want to consider a commit_delay, which pauses to try to batch a few commits together before flushing. This can produce a throughput improvement on I/O subsystems with really slow flushes without sacrificing durability.

You may also want to consider using UNLOGGED tables for specific tables you can afford to lose in a crash. If Pg crashes while the table is dirty it'll truncate it and you'll have to re-populate it, but there'll be no wider database corruption.

If anyone tells you to turn fsync off - please don't. It should be called eat_my_data = on, and is totally unsuitable for anything except throw-away instances where you can easily reconstruct the lot after a crash.


Select unique value whereas the time is highest in the most optimal way Given a simple, with a text and time column, I want to select X unique values from the text column, whereas that row contains the highest value for time.

INSERT INTO `test` (`id`, `text`, `time`)
VALUES
    (1, 'test1', 1),
    (2, 'test1', 3),
    (3, 'test1', 2),
    (4, 'test2', 1),
    (5, 'test2', 100),
    (6, 'test2', 20) ;

The query that meets most of my requirements is:

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    a.`text` ASC
LIMIT 0,50

For small tables, this works perfect. Though in my table (300k+ rows) it makes mysql crash, due to the subquery.

Is it possible to optimize this query? If it cannot be optimized, would it be possible to select the last inserted unique values for text? (the id and time are theoretically uncorrelated, though in 99% of the cases a correlation will be found, whereas the higher the id, the higher the time)

The obvious index for this query is on (test,time). Add it with:

ALTER TABLE test
  ADD INDEX text_time_IX          -- choose a name for the index
    (test,time) ;

You may also get efficiency gain if you use this version of the query (pushing the limit inside the derived table):

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`
    ORDER BY
        `text` ASC
    LIMIT 0,50 ) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    `text` ASC ;

If there cases where the same maximum time appears on more than one row with same text, it will not show the same exact results and will return more than 50 rows (but I think this version is more appropriate). If you really want the arbitrary choice in case of ties, you can add the LIMIT in the external select, too.


Table size bigger than buffer pool size What if table size more than buffer pool size . How data and index are cached in buffer pool size if the table size is more than buffer pool size.

For example:

One of my innodd table size is 231 gb. How data and index are cached in this table

Short Answer.  Not a problem, except for slowing down.

Long Answer: (InnoDB-specific)

innodb_buffer_pool_size controls the InnoDB "cache".  It is broken into 16KB "blocks", each of which comes and goes from RAM based, roughly, on a Least-Recently-Used algorithm.  (See "1 page granularity" mentioned in a Comment.)

So, when you fetch something from a table, the query execution probably needs to look in an index to find where to find the something, and proceeds to fetch blocks as needed to find it.  For simple queries, this is a few blocks, for a full table scan of your 231GB, it will involve fetching all the approx 14 million blocks.

When a block needs to be fetched, but the cache is full, then some block must be bumped out of the cache.  (Hence the reference to LRU.)

If the "working set" of your data is a lot smaller than 231GB, then there won't be much I/O, and the size of the cache (buffer_pool) is not critical.  But if you do table scans, or use UUIDs, then queries will still run, but they will run slower due to the I/O due to [re]loading blocks into the cache.

All operations are done in the cache; almost nothing bypasses the cache.  Indexes and Data are each stored in BTrees that are composed of 16KB blocks.


How did you setup MySQL Replication with autofailover that is app transparent? Recently told MySQL is shelving MySQL Fabric. Interested how others have implemented a MySQL replicated environment that is app transparent.

I am considering using HA Proxy to host virtual IP address for Master, one for slave pool. The using MySQL Failover to monitor replication cluster, auto promote a slave to master. LinuxHA or HAProxy would change which server the virtual IP address points to. Should work for both Master and Slaves.

We're primarily a PHP shop running MySQL DB's on Centos7 Linux.

I recently had to setup a similar rig but with ubuntu and not centos. I used MySQL cluster and HaProxy in a master-master setup and its been solid as a rock, no down time and haven't experienced any instances were latency has been an issue.

I used this guide  to help configure the MySQL cluster. MySQL cluster is great in my opionion but see the link bellow for an explanation as to why.

HaProxy is able to detect down nodes and you could use the answer provided in this question to help with your configuration of automatic fail over to a slave node. Also I would configure a second HaProxy with heartbeat, just incase.

It might be worth taking a look into the answers provided in this question to help decide what kind of cluster is really best for your environment(If you don't already know).


What is an easy query to fix up historical columns valid_from and valid_to? I have a table with typical history rows using valid_from and valid_to dates.  My table might look like this:

| id | object_id | name | valid_from | valid_to
--------------------------------------------------
|  1 | 1000      | Foo  | 2010-07-01 |   null
|  2 | 1000      | Bar  | 2011-12-31 |   null
|  3 | 2000      | Baz  | 2010-08-04 |   null
|  4 | 2000      | Bang | 2013-03-21 |   null

I'd like run a SQL update such that it would update valid_to of a row to next historical valid_from date minus 1 day if possible.  So therefore the table might look like this when the query is done:

| id | object_id | name | valid_from | valid_to
--------------------------------------------------
|  1 | 1000      | Foo  | 2010-07-01 |   2011-12-30
|  2 | 1000      | Bar  | 2011-12-31 |   null
|  3 | 2000      | Baz  | 2010-08-04 |   2013-03-20
|  4 | 2000      | Bang | 2013-03-21 |   null

I've tried a few different variations, but I'm striking out on this one:

select *
from employees as cur
where cur.valid_from < (
    select nx.valid_from from employees as nx
    where
       cur.source_id=nx.source_id
       and nx.valid_from > cur.valid_from
);

And

update employees cur
set cur.valid_to = (
    select nx.valid_from
    from employees nx
    where cur.source_id=nx.source_id
          and nx.valid_from > cur.valid_from
);

Part of the problem with the subquery as I've written it is that it will return multiple rows.  I just need the very first row that occurs after the valid_from of the current row.  And the update statement MySQL complains that cur.valid_from cannot be used in the subquery.

I'm trying to find a query that would be portable between Oracle and MySQL.  Can someone get me on the right track?

There a lot of differences between the two DBMS.

Oracle supports window fundtions, the MERGE statement and several other features that MySQL doesn't.
They have different datetime functions (but you are lucky here, - interval '1' day  will work in both).
Different UPDATE syntax - apart from very simple, one table update statements.

MySQL supports a JOIN in the UPDATE statement, Oracle doesn't.
Oracle supports updatable "views" (derived tables), MySQL doesn't.
MySQL doesn't allow the updated table to be defined (again) in a subquery in the update WHERE.

Therefore, it will be very difficult - if possible at all - to write an update statement that will work in both dbms for youe complex case.

For Oracle, you can use either a MERGE statement, or the LEAD() function (altough I didn't manage to make it work in your case) or a self-join, or an inline subquery in an updatable "view":

SQLfiddle-1

update
( select id, valid_to,
         ( select min(ee.valid_from)
           from employees ee
           where ee.object_id = e.object_id
             and ee.valid_from > e.valid_from
         ) as next_valid
  from employees e
)
set valid_to = next_valid - interval '1' day ;

or with the more simple:

update employees e
set valid_to =
   ( select min(valid_from)
     from employees u
     where u.object_id = e.object_id
       and u.valid_from > e.valid_from
   ) - interval '1' day ;

None of the above will work in mysql.

For MySQL it will have to be something more convoluted:

update
    employees e
  join
    ( select id,
         ( select min(ee.valid_from)
           from employees ee
           where ee.object_id = ei.object_id
             and ee.valid_from > ei.valid_from
         ) as next_valid
      from employees ei
    ) upd
    on e.id = upd.id
set e.valid_to = upd.next_valid - interval 1 day ;

Another possibility for MySQL is using a variable (will update with an example later).

I think your only option, if you truly want something that works in both environments, is to break the problem into two actions:

1, insert the values into a temporary table and
2, do the actual update using that temp table.


index_merge not working in MariaDB There is MariaDB 10.1.20 (also checked in MySQL CE 5.7 and Percona Server 5.7)

1) Create table:

CREATE TABLE test1 (
     id INTEGER NOT NULL DEFAULT 0,
     flag enum('y','n') NOT NULL DEFAULT 'n',
     KEY(id), KEY(flag)
) ENGINE=InnoDB;

2) All index_merge_xx seems enabled:

MariaDB [db1]> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,
index_merge_intersection=on,index_merge_sort_intersection=on,
engine_condition_pushdown=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,
loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowi    d_merge=on,partial_match_table_scan=on,
subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,
outer_join_with_cache=on,semijoin_with_cache=on,
join_cache_incremental=on,join_cache_hashed=on,
join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,
extended_keys=on,exists_to_in=on,orderby_uses_equalities=off
1 row in set (0.01 sec)

3) Do query:

MariaDB [db1]> EXPLAIN SELECT * FROM test1 WHERE id = 1 AND flag = 'n';
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | test1 | ref  | id,flag       | id   | 4       | const |    1 | Using where |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

In more detailed form:

MariaDB [db1]> EXPLAIN FORMAT=JSON SELECT * FROM test1 WHERE id = 1 AND flag = 'n'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "test1",
      "access_type": "ref",
      "possible_keys": ["id", "flag"],
      "key": "id",
      "key_length": "4",
      "used_key_parts": ["id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "(test1.flag = 'n')"
    }
  }
}
1 row in set (0.00 sec)

Why SELECT ignores the second index and uses the first only?

"Index merge intersect" is rarely picked because it involves getting lots of row pointers from two indexes, then intersecting the sets, and only then looking back in the table.

It is always better (as far as I know) to build a composite index:

INDEX(id, flag)  -- in either order

If this is a 1-row table, then the effort of looking in two indexes to ultimately get a single row -- this is a waste.

A side note:  An InnoDB table should always have a PRIMARY KEY.

Put a thousand realistic rows in the table and add a PRIMARY KEY.  Then we can discuss this further.  (But the answer will be the same -- use a composite index instead of hoping for "index merge intersect".)


Sum of points and rank according to points I am creating a game where I am calculating the user points on the basis of user activities. Everything is working fine - I got the sum of points and on the basis of points I calculated the rank. I have two columns for points calculation: pointsup (contains up points) and pointsdown (contains down points)

select sum(pointsup - pointsdown) as points
from table1 t1
join table2 t2
    on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
group by t1.id
order by points desc

Everything is fine, but the problem comes when I have to rank the users with same points. In this case, I have to consider the registration date: who registers first will come up first on the ranking. The registration date is in another table table3. I have tried to join this third table but the sum is behaving oddly, showing wrong sum calculation.

Can anyone help please?

Try this query

select a.*,b.points from table3 a
join(
select Sum(PointsUp-PointsDown) as points,t1.id
from table1 t1
  join table2 t2 on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
Group By t1.Id)b on a.id=b.id
order by b.points desc


Syntax for using Oracle Linked Server with USE keyword I am working on a task where some tables from Oracle Peoplesoft have to pulled into SQL 2014. The concerned person gave me the following code and I see that in the end he mentions the comment Use /* Insert your source database here */. I am assuming the openrow set query should be used in place of the comments. Can someone please clarify if this is the way ahead. Pasting some of the code here for your reference

Create Table dbo.Z_MIGRATION_TABLE
(
 AMOUNT_TYPE nvarchar(255) NULL
,BUSINESS_UNIT nvarchar(255) NULL
,LEDGER nvarchar(255) NULL
,ACCOUNT nvarchar(255) NULL
,DESCR nvarchar(255) NULL
,DEPTID nvarchar(255) NULL
,DESCR_1 nvarchar(255) NULL
,PRODUCT nvarchar(255) NULL
,JOURNAL_DATE nvarchar(255) NULL
,JOURNAL_ID nvarchar(255) NULL
,CURRENCY_CD nvarchar(255) NULL
,MONETARY_AMOUNT float
,FOREIGN_CURRENCY nvarchar(255) NULL
,AFFILIATE nvarchar(255) NULL
,PROJECT_ID nvarchar(255) NULL
,LINE_DESCR nvarchar(255) NULL
,DESCR254 nvarchar(255) NULL
,DOC_TYPE nvarchar(255) NULL
,DOC_SEQ_NBR nvarchar(255) NULL
,JRNL_LINE_SOURCE nvarchar(255) NULL
,JRNL_LN_REF nvarchar(255) NULL
,JOURNAL_LINE float NULL
,STATISTIC_AMOUNT float
,[PRIMARY] int IDENTITY(1,1) NOT NULL
)
on [PRIMARY]
go
**Use /* insert your source database here*/**
go

Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE
(AMOUNT_TYPE
,BUSINESS_UNIT
,LEDGER
,ACCOUNT
,DESCR
,DEPTID
,DESCR_1
,PRODUCT
,JOURNAL_DATE
,JOURNAL_ID
,CURRENCY_CD
,MONETARY_AMOUNT
,FOREIGN_CURRENCY
,AFFILIATE
,PROJECT_ID
,LINE_DESCR
,DESCR254
,DOC_TYPE
,DOC_SEQ_NBR
,JRNL_LINE_SOURCE
,JRNL_LN_REF
,JOURNAL_LINE
,STATISTIC_AMOUNT
)

The USE command changes the database context to the specified database or database snapshot in SQL Server. I believe there should be a query with matching columns after your INSERT statement that would be the source for the INSERT. It may be OPENQUERY and could look like the following:

Insert into LHR_MIGRATION.dbo.Z_MIGRATION_TABLE
    (AMOUNT_TYPE
    ,BUSINESS_UNIT
    ,LEDGER
    ,ACCOUNT
    ,DESCR
    ,DEPTID
    ,DESCR_1
    ,PRODUCT
    ,JOURNAL_DATE
    ,JOURNAL_ID
    ,CURRENCY_CD
    ,MONETARY_AMOUNT
    ,FOREIGN_CURRENCY
    ,AFFILIATE
    ,PROJECT_ID
    ,LINE_DESCR
    ,DESCR254
    ,DOC_TYPE
    ,DOC_SEQ_NBR
    ,JRNL_LINE_SOURCE
    ,JRNL_LN_REF
    ,JOURNAL_LINE
    ,STATISTIC_AMOUNT
    )
SELECT AMOUNT_TYPE
    ,BUSINESS_UNIT
    ,LEDGER
    ,ACCOUNT
    ,DESCR
    ,DEPTID
    ,DESCR_1
    ,PRODUCT
    ,JOURNAL_DATE
    ,JOURNAL_ID
    ,CURRENCY_CD
    ,MONETARY_AMOUNT
    ,FOREIGN_CURRENCY
    ,AFFILIATE
    ,PROJECT_ID
    ,LINE_DESCR
    ,DESCR254
    ,DOC_TYPE
    ,DOC_SEQ_NBR
    ,JRNL_LINE_SOURCE
    ,JRNL_LN_REF
    ,JOURNAL_LINE
    ,STATISTIC_AMOUNT
FROM OPENQUERY(LinkServerName,'SELECT AMOUNT_TYPE
    ,BUSINESS_UNIT
    ,LEDGER
    ,ACCOUNT
    ,DESCR
    ,DEPTID
    ,DESCR_1
    ,PRODUCT
    ,JOURNAL_DATE
    ,JOURNAL_ID
    ,CURRENCY_CD
    ,MONETARY_AMOUNT
    ,FOREIGN_CURRENCY
    ,AFFILIATE
    ,PROJECT_ID
    ,LINE_DESCR
    ,DESCR254
    ,DOC_TYPE
    ,DOC_SEQ_NBR
    ,JRNL_LINE_SOURCE
    ,JRNL_LN_REF
    ,JOURNAL_LINE
    ,STATISTIC_AMOUNT
    FROM SourceTableName');

LinkedServerName would be the real linked server name and SourceTableName would be the name of the table in the linked server.


Update the other columns which are for to have minimum of other 2 columns , difference between 2 columns , and others I am working on data in oracle 11g as below :

subscriber_id |  count_s  |  count_t |   min_count   |  diff    | negative_diff   |  positive_diff

137606401        3          3              3        0          0                  0
102842273        5          4              4        1          0                  1
116638321        14        14             14        0          0                  0
98353910         53        53             53        0          0                  0
102869643        18        20             18       -2          2                  0
107144617        21        21             21        0          0                  0

Can you please help me with a logic to update min_count , diff , negative_diff , positive_diff considering we have values for subcriber_id , count_s and count_t ?

here , min_count should be minimum of (count_s , count_t) for that specific subscriber_id.

diff should be difference between count_s and count_t , which can be easily attained with [count_s - count_t]

negative_diff should be updated for subscribers which has -ve diff values otherwise value for this should be 0 [-ve sign should not be considered while updating]

similarly , positive_diff should be updated for subscirbers which has +ve diff values otherwise value shouild be 0

please suggest the with sql code snippet to be used to update min_count , negative_diff and positive_diff.

Note: Working in SQL

update t set
 min_count = least(count_s, count_t),
 diff = count_s - count_t,
 negative_diff = decode(sign(count_s - count_t), -1, count_s - count_t, 0),
 positive_diff = decode(sign(count_s - count_t),  1, count_s - count_t, 0)
;

Or just use virtual columns since you are on 11g:

create table t(
  subscriber_id number,
  count_s number,
  count_t number,
  min_count as (least(count_s, count_t)),
  diff as (count_s - count_t),
  negative_diff as (decode(sign(count_s - count_t), -1, count_s - count_t, 0)),
  positive_diff as (decode(sign(count_s - count_t),  1, count_s - count_t, 0))
);


How does Hash Join works with relational operators other than equality operator? I have been reading about Hash Join and how does it work in physical level. However, there is some thing that I don't understand (because of my lack of knowledge).

This is the algorithm of Hash Join that I found;

for each row R1 in the build table
    begin
        calculate hash value on R1 join key(s)
        insert R1 into the appropriate hash bucket
    end
for each row R2 in the probe table
    begin
        calculate hash value on R2 join key(s)
        for each row R1 in the corresponding hash bucket
            if R1 joins with R2
                return (R1, R2)
    end

This is seems adequate for join conditions like "TABLE1.NAME == TABLE2.NAME" etc. However, how does it produce hashes or compares tham when join conditions are "TABLE1.NAME == TABLE2.NAME" && "TABLE1.AGE > TABLE2.AGE".

I just couldn't find and understand how they produce or compare the hashes that requires both equailty operator and some other relational operators like bigger than that etc.

From the article you were reading (by Craig Freedman):

  Hash join shares many characteristics with merge join.  Like merge join, it requires at least one equijoin predicate, supports residual predicates, and supports all outer and semi-joins.

(Emphasis added)

The hash keys are built from the (required) equijoin predicates. All other predicates are evaluated during the bucket scan, hence the name "residual predicate".

In your example, Name would be used to form the hash key. The age predicate would be evaluated on all rows in the matching hash bucket, after the initial hash probe. In cases where the hash key is not unique, the actual value of the hashed columns in the bucket will also be compared, naturally.

The execution plan properties (in SQL Server anyway) for the hash join will show you the build hash keys and any residual.


Proper way to setup SQL Agent account I have an AlwaysOn setup in which the jobs are running on domain service account which have been given access to a file share for backups. The jobs are not able to run.

This is the error message which I get

https://ola.hallengren.com     Msg 50000, Level 16, State 1, Server xxxxx , Procedure DatabaseBackup, Line 457  The directory \xxxxxx\backups$ does not exist.     Msg 50000, Level 16, State 1, Server HNATLD1SVRLSQ02, Procedure DatabaseBackup,

Should SQL agent account in the configuration manager run on the same account as the job owner or can it run on the NT\service account as shown in the screenshot attached, because the security context is still that of the job owner which has access to those file shares? What would be the best practice here? Please advice.

We have different service accounts running on the jobs

Add a proxy account for Operating System (CmdExec) jobs and run the Ola jobs under that.  This way the proxy account can have proper access to only the backup shares, etc. and you don't have to elevate permissions on the SQL Agent account (and inherently any other jobs running under the SQL Agent security context).  Principle of Least Privilege for the win!  :)


Postgres Index a query with MAX and groupBy Is there any way to index the following query?

SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id;

I've tried creating sorted (non-composite) indexes on frame and time, and an index on run_id, but the query planner doesn't use them.

Misc info:

Unfortunately (and for reasons I won't get into) I cannot change the query
The frames_stat table has 42 million rows
The table is unchanging (no further inserts/deletes will ever take place)
The query was always slow, it's just gotten slower because this dataset is larger than in the past.
There are no indexes on the table
We are using Postgres 9.4
The db's "work_mem" size is 128MB (if that's relevant).
Hardware: 130GB Ram, 10 core Xeon

Schema:

CREATE TABLE run.frame_stat (
  id bigint NOT NULL,
  run_id bigint NOT NULL,
  frame bigint NOT NULL,
  heap_size bigint NOT NULL,
  "time" timestamp without time zone NOT NULL,
  CONSTRAINT frame_stat_pkey PRIMARY KEY (id)
)

Explain analyze:

HashAggregate  (cost=1086240.000..1086242.800 rows=280 width=24) (actual time=14182.426..14182.545 rows=280 loops=1)
  Group Key: run_id
  ->  Seq Scan on zulu  (cost=0.000..770880.000 rows=42048000 width=24) (actual time=0.037..4077.182 rows=42048000 loops=1)

Too bad
If you cannot change the query at all, that's too bad. You won't get a good solution.
If you had not table-qualified the table (run.frames_stat), you could create a materialized view (see below) with the same name in another schema (or just a temporary one) and adapt the search_path (optionally just in sessions where this is desirable) - for hugely superior performance.
Here's a recipe for such a technique:

How can I fake inet_client_addr() for unit tests in PostgreSQL?

@Joishi's idea with a RULE would be a measure of (desperate) last resort. But I would rather not go there. Too many pitfalls with unexpected behavior.
Better query / indexes
If you could change the query, you should try to emulate a loose index scan:

Optimize GROUP BY query to retrieve latest record per user

This is even more efficient when based on a separate table with one row per relevant run_id - let's call it run_tbl. Create it if you don't have it, yet!
Implemented with correlated subqueries:
SELECT run_id
    , (SELECT frame
       FROM   run.frames_stat
       WHERE  run_id = r.run_id
       ORDER  BY frame DESC NULLS LAST
       LIMIT  1) AS max_frame
    , (SELECT "time"
       FROM   run.frames_stat
       WHERE  run_id = r.run_id
       ORDER  BY "time" DESC NULLS LAST
       LIMIT  1) AS max_time
FROM   run_tbl r;

Create two multicolumn indexes with matching sort order for lightening performance:
CREATE index fun_frame_idx ON run.frames_stat (run_id, frame DESC NULLS LAST);
CREATE index fun_frame_idx ON run.frames_stat (run_id, "time" DESC NULLS LAST);

NULLS LAST is only necessary if there can be null values. But it won't hurt either way.

Unused index in range of dates query

With only 280 distinct run_id, this will be very fast.
MATERIALIZED VIEW
Or, based on these key pieces of information:

The "frames_stat" table has 42 million rows

rows=280  -- number of returned rows = disctinct run_id

The table is unchanging (no inserts/deletes)

Use a MATERIALIZED VIEW, it will be tiny (only 280 rows) and super fast.
You still need to change the query to base it on the MV instead of the table.
Aside: never use reserved words like time (in standard SQL) as identifier.


Speeding Up Query on a Time Series Table. It's Slow to get (MAX - MIN) Aggregation Value Question

How to improve this query to increase the speed of the query shown below from 30+ seconds to milliseconds? I am using PostgreSQL: v 9.6.6.

Context

I have a time series table buildings.hispoint that stores historical data for data points in a table buildings.point.

I need to aggregate the Max(value) - Min(value) as aggregation_value for different time ranges (e.g. year to date) for a large collection of data points.

This proves to be very slow and needs improvement.

Table Structure of buildings.hispoint (20,210,129 rows)

CREATE TABLE buildings.hispoint (
    id int,
    value float,
    datetime timestamp,
    point_id …
    CONSTRAINT foo FOREIGN KEY (point_id)
    REFERENCES buildings.point (point_id),
    …
);

Query

SELECT COUNT(datetime) AS id,
MAX(value) - MIN(value) AS aggregation_value
FROM buildings_hispoint
WHERE point_id = 44
AND buildings_hispoint.datetime BETWEEN '2018-01-01 00:00:00' AND '2018-05-02 09:18:14';

QUERY PLAN

Aggregate  (cost=160967.11..160967.12 rows=1 width=16) (actual time=21713.720..21713.720 rows=1 loops=1)
  Buffers: shared hit=7817 read=66145 dirtied=1
  ->  Bitmap Heap Scan on buildings_hispoint  (cost=5705.82..159708.13 rows=167864 width=16) (actual time=161.733..21585.478 rows=19783 loops=1)
        Recheck Cond: (point_id = 44)
        Rows Removed by Index Recheck: 3474664
        Filter: ((datetime >= '2018-01-01 00:00:00+00'::timestamp with time zone) AND (datetime <= '2018-05-02 09:18:14+00'::timestamp with time zone))
        Rows Removed by Filter: 306305
        Heap Blocks: exact=46580 lossy=26488
        Buffers: shared hit=7817 read=66145 dirtied=1
        ->  Bitmap Index Scan on buildings_measurementdatapoint_ffb10c68  (cost=0.00..5663.85 rows=306589 width=0) (actual time=139.360..139.360 rows=326088 loops=1)
              Index Cond: (point_id = 44)
              Buffers: shared read=894
Planning time: 40.504 ms
Execution time: 21717.750 ms

An index on (point_id, datetime, value) will likely speed up the query, as it will only have to do an index seek, read only the relevant part of the index and also have available (from the index) all the values of the value column to calculate the MIN and MAX.

An index on (A) (point_id) or (B) (point_id, datetime) would need to also find the read the relevant tuples from the table, in order (A) to filter them for the datetime range and (A and B) to read the values of the value column.


SQL 2008 Version Number Question When I run the SQL Server 2008 Setup Discovery Report, all of the components (Database Engine Services, Management Tools - Complete, etc) show as version 10.3.5852.0.

However, I cannot find any information online as to what this version relates to ?

Any advice or help gratefully received.

It looks like there was a bug in a previous patch that messed up the version number.  Replace the 3 with 00 and you will find the correct info.  It should be Cumulative update package 16 for SQL Server 2008 Service Pack 3


Retrieving executed query list in BigQuery via SQL Is there a table similar to v$sql in Oracle where I can retrieve data associated with a particular query that was run in BigQuery using SQL?

Is there a table similar to v$sql in oracle where I can retrieve data associated with a particular query that was run in BigQuery using SQL?

Yes, In BigQuery it's called Jobs and you have few ways to retrieve it:

Via the UI from the menu on the left-hand side, Query History

Via API by calling the Jobs API
Via BigQuery bq command-line using the show option as follow

bq --location=[LOCATION] show -j [JOB_ID]


Error when restoring transaction Logs I'm trying to restore a database log, however I am getting this message:

  Msg 4305, Level 16, State 1, Line 3
  The log in this backup set begins at LSN 76120000013549400001, which is too recent to apply to the database. An earlier log backup that includes LSN 75428000036693300001 can be restored.
  Msg 3013, Level 16, State 1, Line 3
  RESTORE LOG is terminating abnormally.

What is an LSN?

How can I determine which backup contains the correct LSN?

This query will generate the proper restore chain for all databases hosted on the instance.  This should provide you with the proper statements and in their proper order.  Just copy the statements out of the RestoreStatement field and paste them into a new query window:

WITH BackupHist
AS
(
       SELECT
                s.server_name
              , d.name AS database_name
              , m.physical_device_name
              , CASE m.device_type
                     WHEN 2 THEN 'Disk'
                     WHEN 102 THEN 'Backup Device (Disk)'
                     WHEN 5 THEN 'Tape'
                     WHEN 105 THEN 'Backup Device (Tape)'
                     WHEN 7 THEN 'Virtual Device'
                END AS device_type
              , CAST (s.backup_size / 1048576.0 AS FLOAT) AS backup_size_mb
              , CAST (s.compressed_backup_size / 1048576.0 AS FLOAT) AS compressed_backup_size_mb
              , s.backup_start_date
              , s.first_lsn
              , s.backup_finish_date
              , s.database_backup_lsn
              , CASE s.[type]
                     WHEN 'D' THEN 'Database (Full)'
                     WHEN 'I' THEN 'Database (Differential)'
                     WHEN 'L' THEN 'Transaction Log'
                     WHEN 'F' THEN 'File or Filegroup (Full)'
                     WHEN 'G' THEN 'File or Filegroup (DIfferential)'
                     WHEN 'P' THEN 'Partial (Full)'
                     WHEN 'Q' THEN 'Partial (Differential)'
                END AS backup_type
              , s.recovery_model
              , ROW_NUMBER () OVER (PARTITION BY s.database_name, s.database_backup_lsn ORDER BY s.backup_start_date) AS Row
       FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m
              ON s.media_set_id = m.media_set_id
              RIGHT OUTER JOIN sys.databases d
              ON s.database_name = d.name
              AND s.recovery_model = d.recovery_model_desc
       COLLATE SQL_Latin1_General_CP1_CI_AS
), BackupHistFullIterations AS
(
    SELECT database_name
         , backup_finish_date
         , first_lsn
         , ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM BackupHist
    WHERE backup_type = 'Database (Full)'
)
SELECT    bh.server_name
        , bh.database_name
        , bh.backup_finish_date
        , bh.backup_type
        , CASE backup_type WHEN 'Database (Full)' THEN 'RESTORE DATABASE [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
            WHEN 'Transaction Log' THEN 'RESTORE LOG [' + bh.database_name + '] FROM  DISK = N''' + bh.physical_device_name + ''' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
            ELSE ''
          END AS RestoreStatement
FROM BackupHist bh INNER JOIN
        (
            SELECT *
            FROM BackupHistFullIterations
            WHERE BackupIteration = 1 -- Show the X most recent iteration(s)
        ) bhfi
     ON bh.database_name = bhfi.database_name
    AND (bh.database_backup_lsn >= bhfi.first_lsn
         OR bh.first_lsn = bhfi.first_lsn)
    AND (bh.backup_finish_date >= bhfi.backup_finish_date)  -- used in case db was rebuilt/lsn reset
ORDER BY 1, 2, 3

Some general disclaimers, this script does not include any MOVE clauses or other customizations (such as restoring from a striped backup set).  This won't be an issue for the TLog restores, but it may require that you adjust the Full (and potentially Differential) backup restore statement(s) to fit your needs.  I use this often when setting up a new server as an AG replica as the file paths between servers should match up to make life easier.


MySQL: Find ID with only 2 attached IDs in subtable I've got 2 tables. 1 containing my Threads and 1 containing the users attached to these threads.

I want to find the ThreadID with 2 specific UserIDs attached, no more no less.

This is what I've come up with:

SELECT mt.ThreadID
FROM MessagesThreads AS mt
WHERE (SELECT COUNT(DISTINCT mtau.UserID)
       FROM MessagesThreadsAccessUsers AS mtau
       WHERE
        mtau.ThreadID = mt.ThreadID
        AND EXISTS (
         SELECT 0
         FROM MessagesThreadsAccessUsers AS mtau2
         WHERE mtau2.ThreadID = mtau.ThreadID
         AND mtau2.UserID = 1
        )
        AND EXISTS (
         SELECT 0
         FROM MessagesThreadsAccessUsers AS mtau2
         WHERE mtau2.ThreadID = mtau.ThreadID
         AND mtau2.UserID = 2
        )
       ) = 2

This specific situation is about finding the ThreadID with UserID 1 and UserID 2 attached.
If no ThreadID is found, I need to create a new one further down the path.

My question is: Is this the best way to query this or can it be more intelligent?

You've tagged your question with "t-sql", so here goes (although I think/hope that this should be ANSI SQL compliant) :

SELECT DISTINCT mt.ThreadID
FROM MessageThreads AS mt
INNER JOIN MessageThreadAccessUsers AS mtau1 ON mtau1.ThreadID=mt.ThreadID AND mtau1.UserID=1
INNER JOIN MessageThreadAccessUsers AS mtau2 ON mtau2.ThreadID=mt.ThreadID AND mtau2.UserID=2
LEFT JOIN  MessageThreadAccessUsers AS mtau3 ON mtau3.ThreadID=mt.ThreadID AND mtau3.UserID NOT IN (1, 2)
WHERE mtau3.ThreadID IS NULL;

This is probably more efficient than a GROUP BY with conditional SUM()s, particularly so if you have an index on MessageThreadAccessUsers that covers the columns (ThreadID, UserID). If users are distinct for each thread, you can even remove DISTINCT.

Edit: Added LEFT JOIN and WHERE .. IS NULL to exclude any other users.


Postgres corrupted index on update Sometimes, when doing an update on a specific table record, i get this error:

index "myIndexName" contains corrupted page at block 468
Please REINDEX it.

But on the next update it works, without having to do a reindex.
What can cause this issue? Is there a way to prevent this error?

This index is only there to speed up performance on queries, but i'm seriously wanting to drop it to prevent information loss if i can't find a solution.

My postgres version is 9.3.9

It looks like an on disk index corruption. If you can afford to have the table locked for a while you can solve it by running:

REINDEX INDEX "myIndexName";

The command will rebuild the index getting rid of the error.

If you cannot have the table locked for the time needed by the REINDEX command, you can concurrently create an additional index with the same definition. If the index is still usable,  you can drop it after the CREATE INDEX CONCURRENTLY command finished and the index become valid, otherwise you can drop it before to start.

You can find more information here:

http://www.postgresql.org/docs/9.3/static/sql-reindex.html

http://www.postgresql.org/docs/9.3/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY


SQL ServerRecovery Model Change Tracking Are SQL Server recovery model changes logged anywhere...WMI, error logs...etc?

For instance, if I change from FULL to SIMPLE, is this recorded anywhere?

It is recorded in the SQL Server Error Log.

exec xp_readerrorlog 0, 1, N'setting database option recovery'

The result of that should have Text of:

  Setting database option RECOVERY to FULL for database 'YourDatabaseName'.


Security risk of Enabling MS DTC Distributed transaction? I'm doing data exchange and Insert update operations over the linked server between two database servers. To allow distributed transaction, I've changed settings in Windows to enable MS DTC services.

I want to make sure there is not security risk after enabling it for distributed transaction.

https://technet.microsoft.com/en-us/library/cc753620(v=ws.10).aspx

https://serverfault.com/questions/61269/security-risk-of-enabling-msdtc

The more features you turn on, the greater the risk.  The Linux community openly says to turn features off if you don't need them.  Microsoft has a Windows Server 2012 core mode:

Install Server Roles and Features on a Server Core Server

I don't see DTC on the list.  There are good practices, but if there is a business need I turn it on.  I always say the right tool for the right job.  But you can change the default port for DTC, if you do this hacker scanning tools won't find it.

How to Configure MSDTC to Use a Specific Port in Windows Server 2012/2012R2

I have worked at banks that change the default port of all the SQL Servers.  In some cases, each server had a port in a range of 100 and all the firewalls let that group through.  It is a support nightmare, but it is VERY secure.


Find Max Value for each month for the last 3 months, properly I have a query going that gets data for an ID for the last 3 months.  I need to tweak it so I get the highest value for each of the three months.  I've tried a couple of things with the aggregate function MAX, but I'm not getting anywhere.

I'm trying to get the max value for each of the past months ....

Here's the data from the query, currently sorted by date (asc):

ID      Date               Value
12410   01/03/2017 12:17    0.000178
12410   01/10/2017 11:36    0.000186
12410   01/17/2017 11:27    0.000189
12410   01/24/2017 13:09    0.000182
12410   01/31/2017 10:37    0.000169
12410   02/07/2017 11:03    0.000214
12410   02/14/2017 11:52    0.000176
12410   02/21/2017 10:51    0.000200
12410   02/28/2017 12:29    0.000194
12410   03/07/2017 08:39    0.000206

Here's the query:

select AnalysisID as "ID" , AnalysisDateTime as "Date", AnalysisValue as "Value" from AnalysisValueTbl
where
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -3, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-3, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -2, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-2, getdate()))
or
AnalysisID = 12410 and DatePart(m, AnalysisDateTime) = DatePart(m, DateAdd(m, -1, getdate()))
and DatePart(yyyy, AnalysisDateTime) = DatePart(yyyy, DateAdd(m,-1, getdate()))
order by AnalysisValue desc

For SQL Server, you could do something like this.

DECLARE @t TABLE (Id INT, DateVal DATETIME, ValueVal DECIMAL(18, 9));

INSERT @t ( Id, DateVal, ValueVal )
SELECT Id, DateVal, ValueVal
FROM (
    VALUES
    (12410,   '01/03/2017 12:17',    0.000178),
    (12410,   '01/10/2017 11:36',    0.000186),
    (12410,   '01/17/2017 11:27',    0.000189),
    (12410,   '01/24/2017 13:09',    0.000182),
    (12410,   '01/31/2017 10:37',    0.000169),
    (12410,   '02/07/2017 11:03',    0.000214),
    (12410,   '02/14/2017 11:52',    0.000176),
    (12410,   '02/21/2017 10:51',    0.000200),
    (12410,   '02/28/2017 12:29',    0.000194),
    (12410,   '03/07/2017 08:39',    0.000206)
) x (Id, DateVal, ValueVal);

SELECT  DATEPART(MONTH, t.DateVal) AS [DateVal],
        MAX(t.ValueVal) AS MaxVal
FROM @t AS t
WHERE t.DateVal >= DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY DATEPART(MONTH, t.DateVal);

Updated WHERE clause:
Flattening dates is weird, and my previous query got you today's date minus three months. To get back to the first of three months ago, you have to turn some tricks.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS [First Of This Month],
       DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [First Of Three Months Ago]

You can either take some time trying to understand this, or keep a cheat sheet of how to do it handy so you don't have to remember awful date math ;)


Sorting Order by GETSORTED In my sybase 12, when I look at the showplan of a select distinct query, I see GETSORTED in the showplan's output.

I've searched through the entire internet (YES!) and I can not seem to find a link that tells in what order does GETSORTED actually sort the data?

I have reasons to believe that it does the sorting on the size of the data in column for different rows, and for rows of same length, it does alphabetical.

Execution plan:

W (5):     STEP 1
   W (6):         The type of query is INSERT.
   W (7):         The update mode is direct.
   W (8):         Worktable1 created, in allpages locking mode, for DISTINCT.
   W (9):
   W (10):         FROM TABLE
   W (11):             tablename
   W (12):         Nested iteration.
   W (13):         Table Scan.
   W (14):         Forward scan.
   W (15):         Positioning at start of table.
   W (16):         Using I/O Size 2 Kbytes for data pages.
   W (17):         With LRU Buffer Replacement Strategy for data pages.
   W (18):         TO TABLE
   W (19):             Worktable1.
   W (20):
   W (21):     STEP 2
   W (22):         The type of query is SELECT.
   W (23):         This step involves sorting.
   W (24):
   W (25):         FROM TABLE
   W (26):             Worktable1.
   W (27):         Using GETSORTED
   W (28):         Table Scan.
   W (29):         Forward scan.
   W (30):         Positioning at start of table.
   W (31):         Using I/O Size 2 Kbytes for data pages.
   W (32):         With MRU Buffer Replacement Strategy for data pages.

Query:

select distinct col_1 from tab_1

I am in the middle of migration of an application from Sybase 12 to Sybase 15. I see differences in the ordering of the data and that further causes differences downstream. I can not use an ORDER BY because I don't want to touch the existing application. If I can figure out what GETSORTED is doing, I can probably do the same order by on my new dataserver.

What you see in your execution plan is Sybase performing a DISTINCT operator.

When you do a SELECT DISTINCT in Sybase it first builds a Worktable (as you can see in step 1 of your execution plan) and then it selects sorted data removing duplicates from that worktable (which is what you see in step 2). This is documented

The sort algorithm doesn't appear to be disclosed so if the Sybase developers, with all their wisdom, introduced an optimization where they use data length to determine if values are duplicates, that could have the effect you describe as:

  I have reasons to believe that it does the sorting on the size of the
  data in column for different rows, and for rows of same length, it
  does a alphabetical

The moral of the story is:

If you want your data sorted add an ORDER BY clause

As to your question

  in what order does GETSORTED actually sorts the data?

In the order the algorithm can remove duplicates most efficiently.


Access articles from a table if user belongs to qualified group The tables below are myuser & mygroup, where each user has membership to 1 or more groups via an M2M table membership.

The articles are accessible by users if one of the condition is satisfied
1) article belongs to an open group
2) article belongs to a group of which the user is a member

Here's me query to select an article, given group name FOO-BAR and user id BAZ-ID. I'm unable to express the condition that BAZ-ID should belong to list of user id's that are part of group FOO-BAR.

P.S. Using PostgreSQL 9.3

SELECT *
FROM article a
INNER JOIN mygroup g ON g.id = a.mygroup_id
INNER JOIN membership m ON m.mygroup_id = g.id
WHERE
  (g.name = 'FOO-BAR')
  AND
  (
    (g.open is TRUE)
    OR
    (?)                  -- `BAZ-ID` IN a list of m.user_id
  )

Tables

  CREATE TABLE myuser
  (
    id serial NOT NULL,
    name text NOT NULL,
    CONSTRAINT pkey1 PRIMARY KEY (id)
  );

  CREATE TABLE mygroup
  (
    id serial NOT NULL,
    name text NOT NULL,
    open boolean NOT NULL,
    CONSTRAINT pkey2 PRIMARY KEY (id)
  );

  CREATE TABLE membership
  (
    id serial NOT NULL,
    myuser_id integer NOT NULL,
    mygroup_id integer NOT NULL,
    CONSTRAINT pkey3 PRIMARY KEY (id),
    CONSTRAINT fd1 FOREIGN KEY (myuser_id) REFERENCES myuser (id),
    CONSTRAINT fd2 FOREIGN KEY (mygroup_id) REFERENCES mygroup (id),
    CONSTRAINT ukey1 UNIQUE (mygroup_id, myuser_id)
  );

  CREATE TABLE article
  (
    id serial NOT NULL,
    content integer NOT NULL,
    mygroup_id integer NOT NULL,
    CONSTRAINT pkey4 PRIMARY KEY (id),
    CONSTRAINT fd3 FOREIGN KEY (mygroup_id) REFERENCES mygroup (id),
  );

So, as I understand, the input to the query is:

a group name;
a user name.

And the output should be the list of all the articles belonging to the given group provided either of the following is true:

the group is open;
the given user belongs to the group.

If that is correct, I believe the missing condition could be implemented as an EXISTS predicate:

...
    OR EXISTS
    (
      SELECT
        *
      FROM
        myuser AS u
        INNER JOIN membership AS m ON u.id = m.myuser_id
      WHERE
        m.mygroup_id = g.id
        AND u.name = 'BAZ-ID'
    )
...

The join to membership in the main SELECT would then be redundant.

Alternatively, you could outer-join the result of membership INNER JOIN myuser and check for presence of a match in the WHERE clause, like this:

SELECT
  *
FROM
  article AS a
  INNER JOIN mygroup AS g ON g.id = a.mygroup_id
  LEFT JOIN
    membership AS m
    INNER JOIN myuser AS u ON m.myuser_id = u.id
  ON m.mygroup_id = g.id AND u.name = 'BAZ-ID'
WHERE
  (g.name = 'FOO-BAR')
  AND
  (
    (g.open IS TRUE)
    OR
    (u.id IS NOT NULL)
  )
;

An EXISTS predicate is often faster than an equivalent join, though, so you might prefer the former option, but test in your environment to see which is actually better.

If the user is specified by ID instead of a name, the query becomes simpler in each case. The EXISTS predicate would look like this:

EXISTS
(
  SELECT
    *
  FROM
    membership AS m
  WHERE
    m.mygroup_id = g.id
    AND m.myuser_id = <input-user-ID>
)

For the second option, using an ID instead of a name to specify the user would mean a simpler left join:

LEFT JOIN membership AS m
  ON  m.mygroup_id = g.id
  AND m.myuser_id = <input-user-ID>

And, of course, you would need to replace the u.id reference in the WHERE clause with a reference to a non-nullable membership column. The m.id would do, so:

WHERE
  (g.name = 'FOO-BAR')
  AND
  (
    (g.open IS TRUE)
    OR
    (m.id IS NOT NULL)
  )


SQL Server to Oracle export using Microsoft OLEDB Provider for Oracle I am trying to Export Data from SQL Server to Oracle using the "Microsoft OLEDB Provider for Oracle".

I am using "Export Data..." option from Task menu.

I get to connect to the source database (SQL Server) but when I try to connect to the destination database (Oracle) using "Microsoft OLEDB Provider for Oracle" option, I get the following error.

"ORA-06413: Connection not open"

I also checked my connections (tnsnames.ora) and I am able to successfully connect using  SQL Developer to my local Oracle XE instance. My OS is Windows 8 running on a x64 machine.

Any guess what could be the problem here?

I would try installing the Oracle Data Access Components (ODAC).

You will be able to download them from this link.
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html


How to access table datatype like an array? I tried to store data in a table variable @coordinates, which contains 2 rows and 2 columns: latitude and longitude.

To store value:

declare @coordinates table(latitude decimal(12,9), longitude decimal(12,9))
insert into @coordinates select latitude, longitude from loc.locations where place_name IN ('Delhi', 'Mumbai')

to see the value:

select * from @coordinates

result:

latitude    longitude
28.666670000    77.216670000
19.014410000    72.847940000

But I want to access the values one by one like:
@coordinates[1][1] or @coordinates.latitude[1]

or any easy way possible?

While @coordinates is a variable, it is a table variable, not an array. You access data stored in a table variable the same way you would access data in a conventional table, i.e. using a query.

Now when you want to retrieve data of a table's specific row, you need a way of referencing that row. Often you use some sort of an ID column. In this case you could add such column in your @coordinates declaration:

declare @coordinates table
(
  id int identity (1,1),
  latitude decimal(12,9),
  longitude decimal(12,9)
);

The id column is declared as an IDENTITY column and will be populated automatically as rows are added to @coordinates. So now after running your INSERT statement, the contents will look like this:

id  latitude      longitude
--  ------------  ------------
1   28.666670000  77.216670000
2   19.014410000  72.847940000

And in order to retrieve, say, latitude from row 1, you would do

SELECT latitude FROM @coordinates WHERE id = 1;

If necessary, you can use it as a scalar expression where appropriate, for example:

SELECT
  some_columns,
  (SELECT latitude FROM @coordinates WHERE id = 1) + another column AS some_alias
FROM
  some_table
WHERE
   ...

Granted, this is not as pretty as just @coordinates.latitude[1], but it is a conventional way of working with data in SQL.

Note, though, that if you need to retrieve the contents of this table in a query involving other tables and the row ID is going to depend on some table's column, it will be more natural to join @coordinates to the corresponding table:

SELECT
  ...
  c.latitude + t.some_other_column AS whatever
FROM
  some_table AS t
  INNER JOIN @coordinates AS c ON t.some_column = c.id
WHERE
  ...


Postgres LEFT JOIN with recurring dates and users I'm trying to get a query to show me if users have a record on a particular day, and if they don't it would return a NULL.

What I have so far is a I create a sequence of days, then LEFT JOIN it, if I do this on a single user, I get exactly what I need, but if I do the same over multiple users I don't because the table with the records has all users, so it will always be able to join a day at any one point

This is the query I'm running now

WITH days AS (
  select generate_series((current_date - interval '7 days')::timestamp, current_date::timestamp, '1 day'::interval)::date AS day
), eves AS (
    SELECT COUNT(id) as evs, user_id,
        DATE_TRUNC('day', created_at)::timestamp AS full_day
    FROM logged_events
    WHERE DATE_TRUNC('day', logged_events.created_at) > current_date - interval '7 days'
    GROUP BY user_id, full_day
)
SELECT
    eves.full_day, days.day, eves.user_id FROM days
LEFT JOIN eves ON eves.full_day = days.day
    GROUP BY eves.user_id, eves.full_day, days.day

But like I said because on logged_events I have records from all users, when I join days it will always find something to join, if I were to limit to a single user_id in logged_events I would get the output that I am looking for

This is what I am getting

      full_day       |    day     | user_id | evs
---------------------+------------+---------+-----
 2016-05-20 00:00:00 | 2016-05-20 |       4 |  21
 2016-05-21 00:00:00 | 2016-05-21 |       4 |   3
 2016-05-22 00:00:00 | 2016-05-22 |       4 |   5
 2016-05-23 00:00:00 | 2016-05-23 |       4 |  47
 2016-05-24 00:00:00 | 2016-05-24 |       4 |  26
 2016-05-25 00:00:00 | 2016-05-25 |       4 |   2
 2016-05-20 00:00:00 | 2016-05-20 |      19 |  11
 2016-05-24 00:00:00 | 2016-05-24 |      19 |   8

And looking to have

      full_day       |    day     | user_id | evs
---------------------+------------+---------+-----
 2016-05-20 00:00:00 | 2016-05-20 |       4 |  21
 2016-05-21 00:00:00 | 2016-05-21 |       4 |   3
 2016-05-22 00:00:00 | 2016-05-22 |       4 |   5
 2016-05-23 00:00:00 | 2016-05-23 |       4 |  47
 2016-05-24 00:00:00 | 2016-05-24 |       4 |  26
 2016-05-25 00:00:00 | 2016-05-25 |       4 |   2
 2016-05-20 00:00:00 | 2016-05-20 |      19 |  11
 2016-05-21 00:00:00 | 2016-05-21 |      19 |  NULL
 2016-05-22 00:00:00 | 2016-05-22 |      19 |  NULL
 2016-05-23 00:00:00 | 2016-05-23 |      19 |  NULL
 2016-05-24 00:00:00 | 2016-05-24 |      19 |   8
 2016-05-25 00:00:00 | 2016-05-25 |      19 |  NULL

You have to do with users the same thing you're doing with days:

WITH
  days AS (
    SELECT generate_series(current_date-7, current_date, '1d')::date AS day
    ),
  eves AS (
    SELECT user_id, created_at::date AS full_day, COUNT(*) as evs
    FROM logged_events
    WHERE logged_events.created_at >= current_date-6
    GROUP BY user_id, full_day
    ),
  users AS (
    SELECT DISTINCT user_id FROM eves
    )
SELECT eves.full_day, days.day, users.user_id, eves.evs
FROM days
CROSS JOIN users
LEFT JOIN eves ON (eves.full_day = days.day AND eves.user_id = users.user_id)
GROUP BY users.user_id, days.day;


What is the overhead for varchar(n)? I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type:

  The storage requirement for a short string (up to 126 bytes) is 1 byte
  plus the actual string, which includes the space padding in the case
  of character. Longer strings have 4 bytes of overhead instead of 1.

Let's assume that I have a varchar(255) field. And now, the following statements:

If this field holds a string of 10 bytes, then the overhead is 1 byte. So the string will use 11 bytes.
If the field holds string using 140 bytes, then the overhead is 4 bytes. So the string will use 144 bytes.

Are those statements above true? Here someone understands the doc the same way as me but here someone states the overhead is always 4 bytes here?

Unsurprisingly, the manual is right. But there is more to it.

For one, size on disk (in any table, even when not actually stored on disk) can be different from size in memory. On disk, the overhead for short varchar values up to 126 bytes is reduced to a 1 byte as stated in the manual. But the overhead in memory is always 4 bytes (once individual values are extracted).

The same is true for text, varchar, varchar(n) or char(n) - except that char(n) is blank-padded to n characters and you normally don't want to use it. Its effective size can still vary in multi-byte encodings because n denotes a maximum of characters, not bytes:

  strings up to n characters (not bytes) in length.

All of them use varlena internally.
"char" (with double-quotes) is a different creature and always occupies a single byte.
Untyped string literals ('foo') have a single byte overhead. Not to be confused with typed values!

Test with pg_column_size().

CREATE TEMP TABLE t (id int, v_small varchar, v_big varchar);
INSERT INTO t VALUES (1, 'foo', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

SELECT pg_column_size(id)        AS id
     , pg_column_size(v_small)   AS v_small
     , pg_column_size(v_big)     AS v_big
     , pg_column_size(t)         AS t
FROM   t
UNION ALL  -- 2nd row measuring values in RAM
SELECT pg_column_size(1)
     , pg_column_size('foo'::varchar)
     , pg_column_size('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'::varchar)
     , pg_column_size(ROW(1, 'foo'::varchar, '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'::varchar));

 id | v_small | v_big |  t
----+---------+-------+-----
  4 |       4 |   144 | 176
  4 |       7 |   144 | 176

As you can see:

The 3-byte string 'foo' occupies 4 bytes on disk and 7 bytes in RAM (so 1 byte vs. 4 bytes of overhead).
The 140-byte string '123...' occupies 144 bytes both on disk and in RAM (so always 4 bytes of overhead).
Storage of integer has no overhead (but it has alignment requirements that can impose padding).
The row has an additional overhead of 24 bytes for the tuple header (plus an additional 4 bytes per tuple for the item identifier in the page header).
And last but not least: The overhead of the small varchar is still just 1 byte while it has not been extracted from the row - as can be seen from the row size. (That's why it's sometimes a bit faster to select whole rows.)

Related:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
Detailed optimal column order with large text and bytea
Measure the size of a PostgreSQL table row
Configuring PostgreSQL for read performance


Numerate duplicate of every value I need to add a column to my table with the count of every occurrence of. So if the value is unique the column value should be 1, but if there is multiple copy i need a differente value for every row (1,2...)
The database is postgres
for example

id
value
count

1
a
1

2
a
2

3
b
1

4
b
2

5
b
3

6
c
1

database example https://dbfiddle.uk/?rdbms=postgres_10&fiddle=0e7132f3f094bd93b390ccae2e811696

This can done using window functions
select id, value, count(*) over (partition by value order by id)
from the_table
order by id, value;


does partition elimination? We have a partitioned tables partitioned on STATUS Column using below partitioning function. So a table will have total two partitions and both are going to different filegroups as per partition scheme.
CREATE PARTITION FUNCTION PART_FN_STATUS(INT) AS RANGE RIGHT FOR VALUES (1000)

NOTE: As per buisness logic, status cannot be greater than 1000. 1000 is Max.
We have a Non Clustered Index on Status Column as well which is an Aligned Partitioned Index, i.e partitioned on Status Column.
When I run below query and observe actual execution plan, I have below questions:
SELECT COUNT(*) FROM ORDERS WHERE STATUS <> 1000

On NCI Seek, when I hover my mouse on it, I can see "Actual Partition Count" as 2. So why it is not doing partition elimination?
On same tool tip, when i checked estimated / actual number of rows read, it is actually the count of 1 partition. So it looks like partition elimination happened?
<> is a Non-Sargable Operator, so how can a SEEK happen on a NCI?
Can a Non-Sargable Operator do a Partition Elimination?

Any help would be appreciated.

Yes, but...
Your query is asking a bad question. As-is, the optimizer has no idea if the value 1001 exists in the table. The predicate <> 1000 does not mean "everything under 1000", unless you give it more information.
Setup
CREATE PARTITION FUNCTION
    a_func(int)
    AS RANGE RIGHT FOR VALUES (1000);
GO
CREATE PARTITION
    SCHEME a_scheme
    AS PARTITION a_func ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.a_table
(
    id int PRIMARY KEY
) ON a_scheme(id);
GO

INSERT dbo.a_table(id)
    VALUES(999), (1000);
GO

Fix One: A Constraint
A check constraint can help, but...
ALTER TABLE dbo.a_table
    ADD CONSTRAINT ck_a_ck CHECK(id <= 1000);

You'll need to beware of getting a trivial plan/simple parameterization. If your literal value is substituted with a parameter via simple parameterization, you'll need to add something to your query to avoid that happening, like this:
SELECT
    a.*,
    $PARTITION.a_func(a.id) AS pid
FROM a_table AS a
WHERE a.id <> 1000
AND   1 = (SELECT 1);

Fix Two: Hacking The Function
If you feel like directing the optimizer manually to a partition, you can query the table like this to avoid hitting unnecessary partitions:
SELECT
    a.*
FROM a_table AS a
CROSS APPLY
(
    VALUES($PARTITION.a_func(a.id))
) AS c (pid)
WHERE c.pid = 1;


Execute code ONLY if we have the ability to do full text search on database (mysql >= 5.6.4) I found this answer for how to have conditional logic for execution of mysql statements (https://stackoverflow.com/a/7537348/627473). Seems to work great.

But I need to find a way to execute code for a very specific condition. The code can only execute if the database supports FULL TEXT indexing for innodb. This happens in mysql 5.6.4 or greater. BUT I also need this to work for maria db other forks of mysql.

I am sure I could find a way for mysql version; but I don't really like relying on a version and would rather relying on feature detection.

Is there a way to do this?

EDIT: Here is code. I just have on more error that I am not sure can be fixed.

In phpMyAdmin the error "#2014 - Commands out of sync; you can't run this command now" is displayed. This does NOT happen from CLI. Is there a way around this?

delimiter //

create procedure update_phppos()

begin
set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE Variable_name = 'innodb_ft_cache_size');

if  @supports_ft > 0 then

    REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('version', '15.0');

    else
        SELECT 'An error has occured. You MUST have mysql 5.6.4 or higher to update php point of sale' as 'ERROR';
    end if;
end;

//

delimiter ;

-- Execute the procedure
call update_phppos();

-- Drop the procedure
drop procedure update_phppos;

Plan A:
Notice how you can comment out things if the version is older than something:  (I am running 5.6.12.)

mysql> SELECT /*!50604 '5.6.4 is running', */ 'new or old';
+------------------+------------+
| 5.6.4 is running | new or old |
+------------------+------------+
| 5.6.4 is running | new or old |
+------------------+------------+

mysql> SELECT /*!50700 '5.7 is running', */ 'new or old';
+------------+
| new or old |
+------------+
| new or old |
+------------+

You can see lots of examples in the output of mysqldump.

Plan B:
You could also parse @@version inside a Stored Procedure (or client code).

Plan C:
Or, maybe this

mysql> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES
        WHERE Variable_name = 'Junk';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES
      WHERE Variable_name = 'innodb_ft_cache_size';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+


Does it make sense to create an index on foreign key (uuid) column in Postgres? Assuming 2 sample tables in Postgres 10.X:

CREATE TABLE public.order (
id       VARCHAR(36) NOT NULL,
...
)

CREATE TABLE public.suborder (
id       VARCHAR(36) NOT NULL,
order_id VARCHAR(36) NOT NULL,
...
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES public.order(id)
)

All IDs are simple UUIDs. suborder is being queried by order_id quite often. Does it make sense to create a separate index on order_id even though it references unique (UUID) value?

Something like:

CREATE INDEX suborder_order_idx ON public.suborder(order_id)

It's usually recommended to have an index on foreign key column.  It helps when master and detail tables are frequently joined or when delete/update happens on master table. In this case absence of index causes full scan of detail table to enforce foreign key.
If any of above is true for your system , it would be a good idea to add index.

Side note.  You mentioned ids store guid values, so probably you never search by range. Then hash index would be much better choice compared to "normal" b-tree index.

Indexing the foreign key column is also useful if the parent table receives deletes (or updates on the PK). For every row in the parent table that is deleted, the database has to check the referencing tables if they still have rows referencing the parent. That check is done by selecting from the child table with a where condition on the FK column. Obviously this is faster if the FK column is indexed (this is true for other DBMS like Firebird, Oracle, SQL Server or DB2 as well)


How to automatically fix Orphaned Users with PowerShell and SQLCMD I'm trying to properly capture output from SQLCMD in SQL Server 2012.

Here is the PS script:

$dbname = "MyDB"
$InstanceTarget = "localhost\SQLEXPRESS"

$FixOrphansSQL=@"
WITH login_CTE (name, type, sid)
As
(
Select name, type, sid from sys.database_principals where type = 'S'
)
select 'ALTER USER '+a.name+' WITH LOGIN ='+a.name As Fixusers from sys.server_principals a JOIN login_CTE b ON a.sid <> b.sid and a.name = b.name COLLATE Latin1_General_CI_AS
GO
"@
$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname
sqlcmd -S $InstanceTarget -Q $RepairOrphansSQL -d $dbname

If I run this

sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname

it works and the result is the script commands (on separate lines) to fix the orphaned users – but with some extra characters:

(lots of dashes -----------------)
ALTER USER InsightETLUser WITH LOGIN =InsightETLUser
ALTER USER APSDT_CDLinkedServerUser WITH LOGIN =APSDT_CDLinkedServerUser

(2 rows affected)

When I run

$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname

nothing gets captured into $RepairOrphansSQL.

The "(lots of dashes -----------------)" you talk about are a result of the output from sqlcmd.  Instead of using SELECT in your script, you likely want to use PRINT with a cursor.  SELECT will generate a resultset that sqlcmd will print to the screen along with header info.  The dashes are part of this header info.

The T-SQL won't work for logins/users that have spaces in their names, or other special characters such as dashes, etc.  To make it work correctly for those users/logins, you need to wrap the name in square brackets, using QUOTENAME().

Something like this:

$dbname = "test"
$InstanceTarget = "CP708-D377\MV2012"

$FixOrphansSQL=@"
SET NOCOUNT ON;
DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT FixUsersCmd = 'ALTER USER ' + QUOTENAME(dp.name) + '
    WITH LOGIN = ' + QUOTENAME(sp.name) + ';'
FROM sys.database_principals dp
    INNER JOIN sys.server_principals sp ON dp.name = sp.name AND dp.sid <> sp.sid
    AND dp.type_desc = 'SQL_USER';
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
"@
$RepairOrphansSQL = sqlcmd -S $InstanceTarget -Q $FixOrphansSQL -d $dbname
echo $RepairOrphansSQL
if ($RepairOrphansSQL.Length -gt 0) {
    sqlcmd -S $InstanceTarget -Q $RepairOrphansSQL -d $dbname
}

The above code checks if $RepairOrphansSQL actually contains any commands before running it; if the variable is empty, sqlcmd is not called.


SQL CPU trigger on machine memory load Does anyone know of a setting or trigger for MS SQL 2014 standard edition that would trigger SQL to start using CPU% when system memory hits over 90% usage. I have a small application that is not accessing SQL but does utilize large amounts of memory. When the memory on the system hits 90%+ utilization SQL Server started showing CPU usage on a continual basis until the toolset is stopped.

I've run multiple analysis such as profiler, queries to see the last statements processed, the connections to the database, etc... Nothing is standing out as being the trigger for SQL Server to spike. What is even more odd is that this particular machine has both SQL Server 2014 standard and SQL Server express on it and both of the versions will trigger the spike (this is not my server but one that I am having to work on).

It sounds like your machine is memory starved.  If your app uses a lot of memory and there are two sql instances, one standard and one express, with the default max memory setting if 2 tb, you're probably seeing the sql process cpu spikes as sql's memory is being paged out or returned to the os.  You should set you max and min memory for each instance based on the workload, and also set the maximum memory your application will use if possible.  That way you can ensure all three applications plus the Os have enough memory to coexist.


Difference between SQL Server Azure and SQL Server 200x I'm a SQL Server DBA and I can understand the logic behind logins users roles and etc ( login for server access, user for databases and etc ).

I've asked some questions here but I think I need to choose better how to ask this.

I'm using SQL Server for 5 years+ and now I'm trying to use some azure servers and databases.

I have some doubts about how it works, why I need to use master to create a login, where can I find a list of roles for azure?

AS I can understand now, to create a Login I need to use master and then create a login. then I change to the database I want to use and then I create a user there pointing to the login. Ok.

My question is, how can I create a sysadmin login like we have in SQL Server? I would like to create myself a login that can do everything. what role should I assign my login? I've seen to crate databases and etc but not "do everything you want mr. DBA".

Does azure uses Contained users? That's why I need to create first a login inside Master and then a user inside a database?

Please have in mind that I've read a lot of official microsoft sites related to logins, but I still can't understand what is simple.

I would really like to understand something like "when you create a login, you are creating....then when creating a user, it means..."

With SQL Server I can create a login, then a user inside databases and assign what I want them to do, but there's not visual info about this with azure.

An example of adding a dbowner:

CREATE LOGIN teste123
    WITH PASSWORD = '12e32d2c23dohdas970h9h&(*H&*(H*&(HG*&bhiuosnad22s23sd23'
GO

Use DatabaseIwant
CREATE USER teste123
    FOR LOGIN teste123
    WITH DEFAULT_SCHEMA = dbo
GO

EXEC sp_addrolemember N'db_owner', N'teste123'
GO

and see? I'm creating a DBO. what should I do to create a sysadmin login? to be able to access all databases, create delete and etc? thats the point. Should I create a login and create a user inside all databases as a DBO? because with SQL Server I can simply create a sysadmin and thats it. I have acces to everything.

Thanks all.

Edit: I think I found a usefull page finally:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

Using logins are discouraged. Logins that will be used by your applications should be contained users and not server logins. Create contained users for your applications and users, place one or more database users into a database role and then assign permissions to the database role.

One common reason to create a database user based on a SQL Database server login is for users that need access to multiple databases. Since contained database users are individual entities, each database maintains its own user and its own password. This can cause overhead as the user must then remember each password for each database, and it can become untenable when having to change multiple passwords for many databases. However, when using SQL Server Logins and high availability (active geo-replication and failover groups), the SQL Server logins must be set manually at each server. Otherwise, the database user will no longer be mapped to the server login after a failover occurs, and will not be able to access the database post failover.

As the Sysadmin fixed role does not exist in SQL Azure, if you want to create a "sysadmin" on SQL Azure, in addition to the sysadmin created on the portal, you  need to add that sysadmin login to the dbmanager database role in the master database.

CREATE LOGIN YourSysadminLogin WITH PASSWORD = 'hSUj4Grd' -- Dec 13, 2018

CREATE USER YourSysadminLogin FROM LOGIN YourSysadminLogin;

ALTER ROLE dbmanager ADD MEMBER YourSysadminLogin;

YourSysadminLogin, as it belongs to dbmanager, it now can perform privileged tasks like been able to create and drop databases.

If you want YourSysadminLogin to manage logins also (create/alter/drop logins) add it to the loginmanager database role in the master database.

ALTER ROLE loginmanager ADD MEMBER YourSysadminLogin;

Create your YourSysadminLogin as a user on user databases and assign it db_owner.

CREATE USER YourSysadminLogin FROM LOGIN YourSysadminLogin;
GO
ALTER ROLE db_owner ADD MEMBER YourSysadminLogin;
GO

Now you have created a sysadmin.


Using CHECKPOINT vs GO Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is too large) I recommend to them to do something like the following:

-- Delete 1 million rows 1 thousand at a time
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
GO 1000

The result of this on databases that are in full recovery mode is that 1) the wait allows other transactions to process if needed and 2) when the log backup runs it is able to mark the operations already completed as dirty in the log file so it can re-use the space and prevent the log from growing too rapidly.

Rather than doing this, I'd like to know if it's possible to accomplish the same thing using checkpoints.  Would this statement effectively result in the same situation from happening?

-- Delete 1 million rows 1 thousand at a time
WHILE EXISTS ( SELECT 1 FROM TableA WHERE <condition> )
BEGIN
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
CHECKPOINT
END

Again, these are databases in full recovery mode.

The most important thing when doing a large update or delete is to avoid the transaction log growing out of control. To allow log reuse & avoid bloating to Tlog (Transaction log truncation)

In full recovery mode - take log backups
In Simple recovery mode - do checkpoints.

Since you are using Full recovery, your delete logic should be

begin
 begin tran
   -- delete using batches
   -- carefully choose batch size. SQL Server will do a lock escalation
   -- for batch greater than 5000. (https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx)
 commit tran / rollback tran
  -- since you are using Full recovery, take log backups
  -- with CHECKPOINT SQL Server still cannot reuse those portions of the log until you perform a log backup.
end

I use waitfor delay to esp. avoid prolonged blocking.

Refer to this gold mine blog post - Break large delete operations into chunks by Aaron Bertrand. Also, Take Care When Scripting Batches is very useful blog post.


CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server? We are beginning to provision a set of physical servers for a virtual cluster of SQL Server 2016 nodes within VMware. We will be utilizing Enterprise Edition licenses.

We plan on setting up 6 nodes, but there is a bit of a debate on what the ideal way to provision the physical servers with regards to CPU clock speed versus CPU core count.

I know this is largely dependent on transaction volume and number of databases stored among other software-specific factors, but is there a general rule of thumb that is advised?

For instance, is a dual 8-core, 3.2 GHz physical server (16 cores) more preferential to a dual 16-core, 2.6 GHz server (32 cores)?

Has anyone come across a white paper that further delves into this type of topic?

The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible.  The licensing math on that proves the point at ~$7,500 USD per core for Expensive Edition.

Buying the correct hardware can pay for itself in reduced licensing costs. See Processor Selection for SQL Server by Glenn Berry. It's a great resource on how to choose a processor for SQL Server.

Once you take into consideration SQL Server's per-core licensing structure, it makes sense to always go with the fastest processor speed available, regardless of workload type, whether OLTP or analytics.  Having the fastest possible core speed is never going to be a problem.  Increase the core count as required, but never do that by reducing core speed.

In other words, don't think of 16 x 2.2Ghz processors being the same as 8 x 4.5Ghz processors.  The cost savings of using the 2.2Ghz processors over the 4.5Ghz processors is likely to be a maximum of about $10,000 USD (for a typical Xeon-based two processor machine).  Jumping from 8 cores to 16 cores with SQL Server Enterprise Edition is likely to cost over $60,000 USD in licensing fees.  In other words, you might save $10,000 in hardware costs, but you'll lose an extra $50,000 in licensing.

If you decide you need a lot of parallel processing muscle, and decide you need 32 cores for the task at hand, going with the fastest cores will pay dividends in reduced processing time.  Nobody will fault you for that.

Having said all that, if the choice is one CPU or more than one CPU, always go with more than one.  Running SQL Server (or any DBMS) on a single CPU can cause all kinds of problems since the capability for concurrent operations is vastly limited.


What's wrong with my DDL statement? I am trying to run:

CREATE TABLE `members` (
`id` int(11) NOT NULL auto_increment,
`users` text NOT NULL,
`password` text NOT NULL,
`ip` text NOT NULL,
`status`text NOT NULL,
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM;

I just can't seem to find the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE=MyISAM' at line 8

From here, replace TYPE=MYISAM with ENGINE=MYISAM.

This used to be acceptable syntax, but has been deprecated and removed in 5.5 (from the same thread).


Least Privilege for SQL Agent to run DISKPART The DISKPART script

SELECT DISK 1
ONLINE DISK

runs fine as long as SQL Agent has admin rights on the server; otherwise the script fails with this message:

Message
Microsoft DiskPart version 6.3.9600

Copyright (C) 1999-2013 Microsoft Corporation.
On computer: FOO

DiskPart has encountered an error: Access is denied.
See the System Event Log for more information.

Initially working through this issue there were errors in the event viewer.  However, now that I have used dcomcnfg.exe to give Full configuration, Local Access, Local Launch and Local Activation rights for the SQL Agent account to Virtual Disk Service, there are no messages showing in the system log -- just the above message in the SQL Agent job history.  I'm looking for the master tome of Windows configuration but there does not seem to be one. Do I need to set up auditing or traces to see what privileges are being requested?

You are getting deep into internals here

  Membership in the local Administrators group, or equivalent, is the
  minimum required to run DiskPart

And this SE thread points to the same.

Now usually you want to avoid giving to much permission to the service accounts and I'm not certain if giving the SQL Server agent permissions to mount/unmount volumes and at the same time delete them would be considered best practices.

You can probably program a system service that can do the diskpart magic and call that but this really ask the question why you would want to to this from SQL Server.


Linked reports in SSRS Can some one help me how to find out Original report of a Linked report in SSRS.

We tried by editing the permissions on SSRS console and report permissions but no luck.

Thanks,
Venkat Kokulla

I believe the below should give you what you are looking for?

In the ReportServer database:

SELECT link.Name AS LinkName, link.Path AS LinkPath,
source.name AS OrginalReport, source.Path AS OrginalReportPath
FROM dbo.Catalog link INNER JOIN dbo.Catalog AS source ON link.LinkSourceID=source.ItemID


Limit on number of nested IF statements Is there a technical reason when T SQL gives this error?

  Msg 191Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

My scenario is a function with several nested IFs with ELSEs :

IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE

Changing it to the following statement doesn't raise any error:

IF (1=2) BEGIN SET @teste = 1 END
IF (1=2) BEGIN SET @teste = 1 END
IF (1=2) BEGIN SET @teste = 1 END

According to this Microsoft Docs page, nesting of IF statements is dependent on available memory.

On my test workstation, I was able to write and execute this, which is 189 levels deep:

declare @teste int;
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
print 1

189 levels of nesting seems pretty bizarre to me, but then this is a bizarro world, lately.


PostgreSQL procedural languages - differences between PL/pgSQL and SQL Can anybody please summarize the differences between:

http://www.postgresql.org/docs/9.1/static/xfunc-sql.html

and

http://www.postgresql.org/docs/9.1/static/plpgsql.html

?

Main points:

conceptional differences
given a problem family, convenience of use
political issues

PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well:

Use views where possible
Where a view is not suitable, use an SQL function
Where an SQL function isn't suitable, use PL/PgSQL.
Where PL/PgSQL is too limited or not expressive enough, use PL/Perl, PL/Python, PL/V8, PL/Java, or whatever your preference is
... and where no PL will do the job, use an external program and possibly LISTEN and NOTIFY to talk to it.

Very frequently a view is sufficient when you think a function is needed. Even if it's extremely expensive to SELECT the whole view, WHERE clauses in the query referencing the view are usually pushed down into the view and may result in very different query plans. I've often had big performance improvements from converting SQL functions into views.

The main time you find you can't use a view and should consider an SQL function is when:

Parameters that can't be expressed as simple WHERE clauses are needed, like a parameter within a WITH expression
You want a security barrier via a SECURITY DEFINER function, and the security_barrier views in PostgreSQL 9.2 and above aren't sufficient for your needs;
You need parameters that aren't pushed down into sub-clauses of a view by the optimizer and want to control it more directly; or
There are lots of params or there's lots of repetition of the params, so it's impractical to write the query as a view.

For most of those tasks a plain SQL function works fine, and is often easier to read than PL/PgSQL. SQL functions declared STABLE or IMMUTABLE (and not also declared STRICT or SECURITY DEFINER) can also be inlined into the calling statement. That gets rid of the function call overhead and can also sometimes result in huge performance benefits when a WHERE condition in the calling function gets pushed down into the SQL function by the optimizer. Use SQL functions whenever they're sufficient for the task.

The main time SQL functions won't do the job is when you need lots of logic. If/then/else operations that you can't express as CASE statements, lots of re-use of calculated results, building values up from chunks, error handling, etc. PL/PgSQL comes in handy then. Choose PL/PgSQL when you can't use SQL functions or they're a poor fit, like for:

Dynamic SQL and dynamic DDL via the EXECUTE statement
When you want to RAISE errors/warnings for the logs or client
When you need exception handling - you can trap and handle errors with EXCEPTION blocks instead of having the whole transaction terminate on error
Complex conditional logic that doesn't fit CASE ... WHEN very well
Lots of re-use of calculated values that you can't do fit into WITH and CTEs
Building dynamic records
You need to perform an action after producing the result set

With common table expressions (CTEs), especially writable CTEs and WITH RECURSIVE I find I use PL/PgSQL a lot less than I used to because SQL is so much more expressive and powerful. I use views and plain SQL functions a lot more now. It's worth remembering that plain SQL functions can contain more than one statement; the last statement is the function's result.


How can I select using the same fields from an undetermined number of tables using MySQL? I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this:

foo_reference1
foo_reference2
foo_referencea
foo_referenceb
....
foo_referencez

The tables all contain the fields foo_id, bar_id and bar_weight. I need to get the top five bar_id fields ordered by bar_weight (descending) where foo_id equals something from each table.

The problem is, no two servers will have the exact same number of tables, or the same name. However, they always start with foo_reference. I need to run this in dozens of places. In an ideal world, there would just be one properly indexed foo_reference table, unfortunately changes just aren't possible.

Initially, I tried just using SHOW TABLES LIKE 'foo_reference%' in a sub query in order to build the list of tables that have to be queried. Apparently, MySQL does not like that, so I just queried the information schema directly:

select bar_id, bar_weight from
(
    select table_name as name
    from information_schema.tables as tmp
    where tmp.table_name like 'foo_reference%'
) as res
where res.foo_id = '1'
order by res.bar_weight desc
limit 0, 5;

MySQL is telling me that bar_id is an unknown column in the field list. When I run the sub query by itself, it returns the list of tables that need to be queried.

What am I doing incorrectly? All I want are the top 5 bar_id fields from each table where foo_id is a certain number. As you can tell, I'm doing a bit of learning (and quite a lot of guessing) as I go here.

As far as I know, the only way you can dynamically put something in FROM is to use Prepared Statements. Also, I believe you should use UNION to get results you want, not cross join. For instance, you result query should look like

SELECT * FROM
(
   select bar_id, bar_weight from table1
   UNION
   select bar_id, bar_weight from table2
)a
WHERE ... ORDER BY ... LIMIT ...

Not

select bar_id, bar_weight
from table1,table2
WHERE ... ORDER BY ... LIMIT ...