The oracle documentation says this about this Golden gate error

  OGG-00146: Call to {0} returned with error status {1,number,0}: {2}

  Cause: One of the VAM API functions implemented in the  VAM module
  returned an error status on completion of the call to that function in
  the VAM kernel,  after previously reporting a fatal error in the call.

  Action: Contact Oracle Support or, if you are working with an  Oracle
  GoldenGate developer, contact that person


Connecting to a new RDS instance from an EC2 instance

Check my server logs in MySQL Workbrench or Command-line Could not acquire management access for administration.

Right, you can't do that with workbench and RDS because that requires shell access, which RDS does not allow.  The logs are accessible through the console and API.

  Remove rdsadmin or Revert the asingeded roles and privileges

Right, you can't do that, because rdsadmin is the account that the RDS infrastructure uses to manage your instance.  You don't control it.

  Grant DBA/MaintenenaceAdmin/ProcessAdmin or Replication Admin to WPuser Error changing account WPuser@%: Access denied for user 'WPuser'@'%' (using password: YES)

The problem here is threefold.

The "roles" in workbench were a ridiculous idea on the part of Oracle because they do not correspond to real structures on MySQL... They're just fancy packaging for permission presets and you'd be better served to ignore that functionality and understand how the permissions actually work.

The DBA role doesn't work on RDS because that includes the SUPER privilege, which RDS does not provide, so that one wouldn't work anyway, even if you hadn't made the final error:

You appear to be trying to give "WPUser" permissions... while you're logged in as "WPUser."  You can't grant permissions to yourself.  You also can't grant permissions you don't hold.  If WPUser is the master user for your RDS instance, it already has all the permissions it can have... though you shouldn't use your master user for your application's access to the database.

  I added the Elastic/Private and even Public IP address for the EC2 instance to the VPC security group

That isn't likely the problem, here.  If the security group isn't right, you'll fail to connect, after a lengthy timeout, and the error code will not be "access denied."

Also potentially incorrect, depending on what you are using as 'ENDPOINT':

GRANT ... ON WIKIdb.* to 'WIKIuser'@'ENDPOINT';

If 'ENDPOINT' is the RDS endpoint, this is incorrect.  You can never connect to RDS from the RDS endpoint, or even from 'localhost'.

The final argument there needs to be the IP address of the server where the wiki code is running, or a wildcard for the netblock, like '172.31.%'.


Query to compare 2 table results and filter extra records found in a table

For my testing, I just created a couple of temporary tables (#CN for ORIN and #INV for OINV).  You should be able to replace them with your above queries without too much difficulty.

The following query should be a relatively efficient query to get the results you want:

with cn as
(
    select
        *,
        ROW_NUMBER() over (partition by JrnlMemo order by DocEntryCN desc) as rownum
    from
        #CN
),
inv as
(
    select
        Comments,
        count(*) as rowstoget
    from
        #INV
    group by
        Comments
)
select
    cn.DocEntryCN,
    cn.Comments,
    cn.JrnlMemo,
    cn.DocTotal
from
    cn
    inner join inv on cn.JrnlMemo = inv.Comments
where
    cn.rownum >= inv.rowstoget
order by
    DocEntryCN;

Sample data used:

create table #CN
(
    DocEntryCN int,
    Comments varchar(20),
    JrnlMemo varchar(20),
    DocTotal int
);

insert into #CN(DocEntryCN, Comments, JrnlMemo, DocTotal) values
(67,            'reverse',     'kaka-19',       500),
(69,            'reverse',     'kaka-19',       500),
(70,            'reverse',     'kaka-19',       500),
(71,            'reverse',     'kaka-19',       500),
(74,            'reverse',     'kaka-19',       500),
(75,            'reverse',     'Rob-23',        750),
(77,            'reverse',     'Rob-23',        750),
(78,            'reverse',     'Rob-23',        750),
(79,            'reverse',     'Rob-23',        750),
(84,            'reverse',     'Paul-17',       1250),
(86,            'reverse',     'Paul-17',       1250),
(87,            'reverse',     'Paul-17',       1250)

create table #INV
(
    DocEntryINV int,
    Comments varchar(20),
    DocTotal int
);

insert into #INV(DocEntryINV, Comments, DocTotal) values
(1,             'kaka-19',        500),
(3,             'kaka-19',        500),
(5,             'kaka-19',        500),
(6,             'kaka-19',        500),
(7,             'Rob-23',        750),
(9,             'Rob-23',         750),
(10,            'Alex-09',        1000),
(11,            'Olma-08',        1150),
(12,            'Paul-17',        1250 ),
(13,            'Paul-17',        1250),
(16,            'Paul-17',        1250),
(17,            'Rita-99',        1300),
(19,            'Rita-99',        1300);

I should point out that the results will be slightly different from what you indicated in your Desired Results section because you have a Rita there with a DocEntryCN of 90, but no Rita entries in ORIN.  Otherwise, the results are the same.


SELECT * Returns 1 Record, But SELECT COUNT(*) Shows 3k+ Records

So I figured this out less than a minute after I hit post, despite thinking about it and googling for a bit.

So the problem is that I ran a procedure earlier that deleting some duplicate values in a table using SET ROWCOUNT 1 and a WHILE loop.  I realize that I didn't actually set rowcount back to zero (SET ROWCOUNT 0) after the break in the loop.  After doing that, I can see all the records now.


Can Windows Authentication logins be disabled on SQL Server 2008 (or R2)?

As far as I'm aware it is not possible to disable Windows authentication on SQL Server 2008 + R2:

http://msdn.microsoft.com/en-us/library/ms144284%28v=sql.105%29.aspx

The only Windows accounts that will have access are those that are explicitly added as logins (or are members of a group which has a login).


How to replicate schema changes from local database to a remote?

Next time, click the Generate Scripts button and then execute the generated SQL script at both the local and remote databases.

For the changes you made locally, that you now want to replicate to the remote server, you'll need to either recreate them manually on the remote server or use a tool to identify the differences, such as RedGate's SQL Compare or Visual Studio's Schema Compare tool.

There are other methods, but I think these are likely going to be the easiest approach for you.

Finally, the "proper" way to handle changes like this is to tap into some release management methodologies.  This topic can get pretty complex depending on your needs, but a good primer can be found here along with a listing of some of the tools available on the market.


Simple DELETE, but complicated execution plan

The top levels of the plan are concerned with removing rows from the base table (the clustered index), and maintaining four nonclustered indexes. Two of these indexes are maintained row-by-row at the same time the clustered index deletions are processed. These are the "+2 non-clustered indexes" highlighted in green below.

For the other two nonclustered indexes, the optimizer has decided it is best to save the keys of these indexes to a tempdb worktable (the Eager Spool), then play the spool twice, sorting by the index keys to promote a sequential access pattern.

The final sequence of operations is concerned with maintaining the primary and secondary xml indexes, which were not included in your DDL script:

There is not much to be done about this. Nonclustered indexes and xml indexes must be kept synchronized with the data in the base table. The cost of maintaining such indexes is part of the trade-off you make when creating extra indexes on a table.

That said, the xml indexes are particularly problematic. It is very hard for the optimizer to accurately assess how many rows will qualify in this situation. In fact, it wildly over-estimates for the xml index, resulting in almost 12GB of memory being granted for this query (though only 28MB is used at runtime):

You could consider performing the deletion in smaller batches, hoping to reduce the impact of the excessive memory grant.

You could also test the performance of a plan without the sorts using OPTION (QUERYTRACEON 8795). This is an undocumented trace flag so you should only try it on a development or test system, never in production. If the resulting plan is much faster, you could capture the plan XML and use it to create a Plan Guide for the production query.


You can use pg_archivecleanup.

You will find documentation here : https://www.postgresql.org/docs/current/static/pgarchivecleanup.html


The job has several steps. In the step that ran the package, I had to select "SQL Server" as the package source instead of "SQL Server Package Store". This stopped the use of MSDTC.


Why does `row_to_json(...)` accept a sub-query?

Given next example:

CREATE TABLE tbl (id int, title text);

INSERT INTO tbl VALUES
(1, 'Title 1'),
(2, 'Title 2'),
(3, 'Title 3');

  Does this mean a sub-query is the same as a record and a row value/composite value?

Let me select some records from tbl:

SELECT id, title FROM tbl;

id | title
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

Now using a subquery:

SELECT * FROM (SELECT id, title FROM tbl) t;

id | title
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

As you can see both queries return same rows, then you can use both queries to generate a JSON result.

SELECT row_to_json(x)
FROM   (SELECT id, title FROM tbl) x;

| row_to_json                |
| :------------------------- |
| {"id":1,"title":"Title 1"} |
| {"id":2,"title":"Title 2"} |
| {"id":3,"title":"Title 3"} |

dbfiddle here

As @ypercube has pointed out, when you executes:

SELECT tbl FROM tbl;

you get this result:

| tbl           |
| :------------ |
| (1,"Title 1") |
| (2,"Title 2") |
| (3,"Title 3") |

same output as using:

SELECT ROW(1, 'Title 1');

| row           |
| :------------ |
| (1,"Title 1") |

It is a (named) composite type. (Have a look at docs about TYPE)

  By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type — either the row type of a table, or a composite type created with CREATE TYPE AS

Having a look at row_to_json() function:

+------------------------+----------------------------------------------------+---------------------------+---------------------+
| row_to_json(record [,  | Returns the row as a JSON object.                  | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
|          pretty_bool]) | Line feeds will be added between level-1 elements  |                           |                     |
|                        | if pretty_bool is true.                            |                           |                     |
+------------------------+----------------------------------------------------+---------------------------+---------------------+

Uses a row as example: row_to_json(row(1,'foo'))


How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections?

"Can support" != "optimal throughput".

You can use lots of connections, but it's slower.

If you use fewer connections and queue work, you get the same amount of work done in a smaller time.

  Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well?

Either they're using a connection pooling frontend like PgBouncer in transaction pooling mode, or it won't work well.

People like big numbers though, so they'll give you big numbers.

They're actually hurting performance by doing so. PostgreSQL has some costs that scale linearly with max_connections, so even if the connections are not used it still has a performance impact.

Additionally, even idle connections have some further housekeeping costs.

If the connections are actively working, then you also have contention on system resources and on internal locks.

I routinely run into people who're having PostgreSQL performance issues - and who try to solve them by adding more connections, more workers in their application, etc. Especially people running queuing systems. It's surprisingly hard to persuade them that lowering the number of workers will make the system go faster, and that their original performance issues stemmed from having too many in the first place.


Oracle 12c and Jobs running one hour apart after changing time for hourly use

If you found the timezone of your database to be incorrect and you are sure this causes the issue (I have seen this before), you can change it with.

set_time_zone_clause::=

For example:

SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';

PROPERTY_VALUE
--------------------
+01:00

You can set a named time zone like:

SQL> alter database set time_zone = 'Europe/Budapest';

Database altered.

Or a fixed value:

alter database set time_zone = '-05:00';

I used the former. Then restart the database:

SQL> shu immediate
Database closed.
Database dismounted.
startup
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             285212672 bytes
Database Buffers          771751936 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';

PROPERTY_VALUE
--------------------
Europe/Budapest

SQL>

  set_time_zone_clause

  This clause has the same semantics in CREATE DATABASE and ALTER
  DATABASE statements. When used in with ALTER DATABASE, this clause
  resets the time zone of the database. To determine the time zone of
  the database, query the built-in function DBTIMEZONE. After setting or
  changing the time zone with this clause, you must restart the database
  for the new time zone to take effect.

Keep in mind this may not always work, because:

  Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data
  to the time zone of the database when the data is stored on
  disk.Oracle Database does not automatically update existing data in
  the database to the new time zone. Therefore, you cannot reset the
  database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data
  in the database. You must first delete or export the TIMESTAMP WITH
  LOCAL TIME ZONE data and then reset the database time zone. For this
  reason, Oracle does not encourage you to change the time zone of a
  database that contains data.

  For a full description of this clause, refer to set_time_zone_clause
  in the documentation on CREATE DATABASE.

Also, you should apply the latest DST patch as well, so the database knows when to set the time forward or backward for daylight saving.


Can indexes be deactivated for particular queries in DB2?

There is no equivalent Access Request with Db2 Optimization Guidelines unfortunately.
You may specify either desired index to use or another access method (TBSCAN, for example) for your table. But not "not to use some particular index" guideline.


Why is Postgres not respecting my log file name convention dictated by "log_filename"?

You need to edit the values for logging_collector and log_directory so that it knows you want to manage the logs and where they should go.

https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html

Look at the section for log_filename.


How to trigger newlines in log messages?

It's the same as any other string. Either use non-standard E'' strings to tell PostgreSQL you want escape processing:

RAISE WARNING E'Line 1\nLine 2';

or use SQL-standard strings with literal newlines:

RAISE WARNING 'Line 1
Line 2';


To provide All privileges to an user from a specific server your Grant commands can be like the one below.

     GRANT ALL PRIVILEGES ON database_name TO 'user'@'hostname' IDENTIFIED BY PASSWORD <password>;
     FLUSH PRIVILEGES;

To provide select and execute grants to your user,

GRANT SELECT, EXECUTE ON database_name TO username@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES

Don't forget to FLUSH PRIVILEGES after executing the grants.


Is SARGable query the first thing to put in developers' minds?

This is a bit tough to answer with the lack of details so I will share my experience. I help teach a short class to new developers about SQL optimization at the company that I work for. We do OLAP on SQL Server.

The concept of SARGable arguments is important and definitely mentioned in the class. It can be used to avoid basic mistakes, but it won't be much help for developers if their query is performing badly and they aren't making any of those mistakes. We try to focus on the fundamentals of queries and to teach developers how to interpret query plans. In my opinion, the three most important things to understand well are table access methods, join types, and join order. If developers understand those three things well then they'll be in a good spot to troubleshoot most performance issues, at least with our workload.

By table access methods, I mean how to identify if a table is being accessed through an index or not, when to expect index use, and the consequences of not using an index when you should. Same ideas for scans versus seeks. By join types, I mean the usual nested loop, merge, and hash varieties. When to expect each and what the consequences are when SQL Server gets it wrong from our perspective. By join order, I mean the order of tables in the query plan. If there is a table that greatly restricts the number of rows returned by the query is it processed relatively early in the plan? If not, why not?

If developers have a decent grasp on the basics then they should be well armed to identify lots of different performance issues, including those covered by the SARGable concept.


Delete query uses 100% CPU with in Azure SQL Database

As you note the plan shows many index scans[1], a couple of the indexes multiple times such as Facilities.IX_CustomerID. That will be why is it spinning the CPU resource: those indexes will be in the buffer pool so little IO is needed and the CPUs are being made to chew them, probably in their entirety, repeatedly.

If this is from a simple DELETE statement and all the other activity is the engine enforcing foreign key constraints then I would check for foreign keys without supporting indexes. For instance one of the scans of [Facilities]" Index="[IX_CustomerID] has the search predicate [DataMateWeb].[dbo].[Facilities].[SurfaceCleanerID]=[DataMateWeb].[dbo].[Chemical].[ChemicalID],  which suggests an index covering [Facilities].[SurfaceCleanerID] is not present or for some reason can not be used. When you define a foreign key an index is not automatically created to go with it which can surprise people and that would explain a scan of the table/cluster rather than multiple seeks on an index. In any cases where those indexes are the supporting indexes for the foreign keys, make sure that they are correctly defined and don't have badly out-of-date statistics.

If the delete operation isn't that simple then make sure any joins and sub-queries used in deciding which rows will be deleted are supported by appropriate indexes and that the joining and filtering predicates are arranged such that they are sargable where ever possible.

Failing the above (i.e. all the FKs and any other filtering & joining activity all look fine with regard to having indexes they could potentially seek on), the next straw I'd clutch for is: if you are deleting many rows at once (DELETE WHERE IN (<a result that could be a long list>)) then I've seen that make SQL Server scan the index instead of using multiple seeks. This feels a bit nasty[2] but: check this by trying to confirm the behaviour with just one row and if that changes at least some of the scans to seeks (and improves performance as a result) consider cursoring through the rows that need to be removed and doing them one at a time.

[1] Which as they are clustered index scans are effectively table scans which for large tables means a lot of data is being touched.
[2] Because it is nasty. But if it is nasty and works, it still works!


Mysql - Opitmizing a range query with group by

Try this.

Change index like.

KEY i_sales_test_index (shopId,rowStatus,customerEmail)

SELECT
    1 AS sortKey,
    avg(avgs.a) AS reorder_freq
FROM (
    SELECT
        b.total_freq AS a
    FROM
        sales AS s, (
            SELECT
                saleId,
                get_total_freq (MAX(a.processedAtNano),
                    MIN(a.processedAtNano),
                    count(a.customerEmail)) AS total_freq
            FROM
                sales AS a
            WHERE
                a.shopId = 'p2k-c5k-o100k'
                AND a.rowStatus = 'ok'
            GROUP BY
                a.customerEmail
            HAVING
                count(a.customerEmail) > 1) AS b
        WHERE
            s.saleId = b.saleId
            AND s.processedAtNano < 1525132799999
            AND s.customerEmail IS NOT NULL
            AND s.customerEmail != '') AS avgs


Try the following log parameters. After modify them, remember to reload the configuration file ($PGDATA/postgresql.conf).

# These are only used if logging_collector is on:
log_directory = '/var/applog/pg_log/1921'               # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600                    # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 10MB               # Automatic rotation of logfiles will


Oracle ASM is both a volume manager and filesystem from Oracle database files. It does not have an 'underlying filesystem', it is the filesystem.

  Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations.

ACFS is something else: it's a filesystem based on ASM, but for "regular" files. It is meant as a general purpose filesystem, that happens to use ASM as a backend.

  Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle Automatic Storage Management (Oracle ASM) functionality to support customer files maintained outside of Oracle Database. Oracle ACFS supports many database and application files, including executables, database trace files, database alert logs, application reports, BFILEs, and configuration files. Other supported files are video, audio, text, images, engineering drawings, and other general-purpose application file data.


MaxScale not correctly recognizing new topology when we failover to a new master

Turns out the issue was that mysqlfailover was causing a conflict with maxscale.  When the master would fail, mysqlfailover would automatically promote a new slave to master.  However, when we did this it would cause a conflict with Maxscale (since it usually does it on its own), so it would lose track of which servers were the master and which were the slaves.


How knowing queryid get query string in Postgresql?

This is managed by pg_stat_statements extension in the view that has the same name.
To link queryid and query you need to query the view pg_stat_statements:

postgres=# \d pg_stat_statements;
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |

postgres=#

For example:

select queryid, query from pg_stat_statements;


Turns out Cross Database Chaining was the answer (for us at least).  Turning this on allowed me to give the user limited (dataread/datawriter) access to the database that only shows their data, and NOT the underlying database that actually stores everyone's data.

I appreciate the suggestions - thanks for weighing in.

As an aside - the reason I don't like using schema's is due to generally wanting to have each be assigned a default schema, and not have to qualify every table name with the schema name.

I find that for testing purposes, constantly having to either update my default schema (to simulate working as different users), or actually connecting as lots of different users who already have preset default schemas ends up being more of a headache than just logging in with Windows Auth as me (always) and just connecting to different databases to get different user's data.

This also allows me to (conceptually) have an entire database for each user - which really cleanly and nicely isolates their data.

Data liberation can then be achieved by literally just SELECT * FROM each table in the user's database.

Also - from my testing, of separating by schema or database (in SQL 2012) made no difference that I was able to detect across many hundreds of thousands of a variety of different types of operation.

If someone knows of some performance issue I'm not aware of - I'd love to know about it, but... at this point, keeping each customer's data in their own database is the right answer for us.

Thanks again.  Good night.


psql does not require a password on macOS with Postgres.app

Found the answer in another post on stackexchange.

I've edited the authentication lines in pg_hba.conf and changed "trust" into "password" for all lines. It now looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             all                                     password
host    all             all             127.0.0.1/32            password
host    all             all             ::1/128                 password
local   replication     all                                     password
host    replication     all             127.0.0.1/32            password
host    replication     all             ::1/128                 password

Now psql asks me for a password every time.


Load Testing When SQL Server Caching Is Disabled

Your tests are flawed.  Your data sizes are too small to produce any meaningful results.  CHECKPOINT/DROPCLEANBUFFERS after every query is giving results that would never correlate with reality.

As for your questions:

Assume a simple query run in a loop:

UPDATE questions SET up_votes = up_votes + 1 WHERE id = @id

This query will generate a small transaction log entry for each query, and dirty the page it hits.  If you update the same row 1000 times, you could generate 1000 small transaction log entries with one 8k write (at some point later).  If you issue a CHECKPOINT, you will force the dirty page to be written to disk after every query (at least 1 additional 8k write, maybe more depending on indexes).  The DROPCLEANBUFFERS will force the next query to issue multiple 8k reads. (the data, the intermediate index levels, GAM pages, SGAM pages, PFS pages, etc, etc, etc)

No.  Even if you have a 500 GB database and 8 GB of RAM, a lot of meta-data and intermediate level index pages will remain in memory and are critical for performance.
See above about GAM/SGAM, etc.
CHECKPOINTS will generate a trivial amount of additional log activity.  You are still squarely in the "background noise" range with your data volumes to evaluate log growth in any meaningful fashion.

As an analogy, It is like attempting to measure the gas mileage in a vehicle just by backing out of the driveway.

If you are concerned with SAN performance, then look into Microsoft's DiskSpd.exe tool (formerly SQLIO).  The documentation has sample configurations that relate to known SQL Server access patterns.


I figure something like:

SELECT p.id, p.first, p.last, TIMESTAMPDIFF(YEAR, p.dob, NOW()) AS age
     , MIN(r.description) as image
FROM profile p
JOIN reference r
    ON r.id = p.id
WHERE r.type = 'I' AND r.active = 'Y'
  AND p.active = 'Y'
GROUP BY p.id, p.first, p.last, p.dob

;

should work


How to select all Mondays (or any day combo like MTW, WTTh) between two given dates in MySQL?

From here, I modified the query slightly to get

select adddate('2015-02-01', numlist.id) as `my_date`,
       weekday(adddate('2015-02-01', numlist.id)) as day_no,
       dayname(adddate('2015-02-01', numlist.id)) as `day_name`
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-02-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

which gives

+------------+--------+----------+
| my_date    | day_no | day_name |
+------------+--------+----------+
| 2015-02-02 |      0 | Monday   |
| 2015-02-03 |      1 | Tuesday  |
| 2015-02-09 |      0 | Monday   |
| 2015-02-10 |      1 | Tuesday  |
| 2015-02-16 |      0 | Monday   |
| 2015-02-17 |      1 | Tuesday  |
| 2015-02-23 |      0 | Monday   |
| 2015-02-24 |      1 | Tuesday  |
+------------+--------+----------+
8 rows in set (0.01 sec)

or from here

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

result

+---------------+
| selected_date |
+---------------+
| 2015-02-02    |
| 2015-02-03    |
| 2015-02-09    |
| 2015-02-10    |
| 2015-02-16    |
| 2015-02-17    |
| 2015-02-23    |
| 2015-02-24    |
+---------------+
8 rows in set (0.23 sec)

CREATE TABLE mydate( blah date);

INSERT INTO mydate (blah)
select adddate('2015-02-01', numlist.id) as `my_date`
--       weekday(adddate('2015-02-01', numlist.id)) as day_no,
--       dayname(adddate('2015-02-01', numlist.id)) as `day_name`
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-05-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

(need a table num)

mysql> select * from mydate;
+------------+
| blah       |
+------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
| 2015-03-02 |

OR

insert into mydate (blah)
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-05-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

No table necessary. Same result as above.


How to speed up counting and filtering on table with a lot of columns and filter conditions?

I feel your pain, nik. We have a similar use case here, and have been struggling through as best we can with a metric ton of indexes.

I know you said you need to stick with SQL, but I really think you might want to consider a column store (https://en.wikipedia.org/wiki/Column-oriented_DBMS), perhaps one SQL-like enough for you to still find workable.

MariaDB seems to be embracing this now too:
https://mariadb.com/resources/blog/why-columnstore-important


Postgresql: No data left on drive, how can I wipe it and start over?

Since you say that the config directories are in /etc/postgres I'm guessing you're running Ubuntu or Debian, which use pg_wrapper for management of their PostgreSQL installs.

On these platforms you use pg_dropcluster to destroy and remove all databases, then pg_createcluster to create a new blank server instance.

This guide for PostgreSQL on Ubuntu may be informative.


How to structure db where main records are large

NoSQL is not a direct solution to an amount of data problem, rather it is mainly a solution for a schema-less or highly variable schema problem (as you've experienced when you said "we lose a bit of the standard easy relational schema" regarding using NoSQL). Because you admit your data is highly relational, fitting a standard schema, then you'll likely want to continue to use a RDBMS like MySQL.
That being said, millions of rows in a table is on the small side and certainly no concern for a relational database when architected and indexed appropriately. If I play out the calculation with your example's numbers above, it seems like we're actually talking about 100 million records per year, which is now what I subjectively consider a medium sized table. The fact the width of the table is only 10 columns helps keep it compact as well. This is still no problem for any modern RDBMS. I've personally worked with larger tables in the 10s of billions of records, about 50 columns wide, and regular B-Tree indexing still works amazingly well even at that scale. I could query thousands of rows from that table in a second or less and millions of rows from that table in about 10 seconds, on very modest hardware.
Aside from appropriately indexing and architecting your database, you can use things like Partitioning, and routinely archiving old data that is less frequently queried into a separate table. Long story short, it sounds like your architecting things correctly and I wouldn't stress too much on the amount of data you plan to support yet, until it becomes problematic (which I don't think it will).


SQL Server: Is altering collation on existing databases safe?

Changing a database's Collation doesn't affect existing columns.  It affects new non-XML string columns created that don't specify the COLLATE clause (including table variables), string literals, and variable values (not variable name resolution, which is determined by the Instance-level Collation). Meaning, something like the following will be affected:

IF (@Variable = 'string')
BEGIN
    ...
END;

This change will also affect Database-level meta-data, such as names of schemas, objects, columns, indexes, etc. Meaning, the following two scenarios will be affected:

SELECT ...
FROM   sys.indexes si
WHERE  si.[name] = N'somename'; -- real name = SomeName

and:

SELECT ...
FROM   dbo.sometable st -- real name = SomeTable

In both of those examples, they would work in a case-insensitive Collation, but return nothing or error, respectively, in a case-sensitive Collation.

Finally, as @JonathanFite was kind enough to remind me of, changing the DB Collation may impact queries involving temporary tables. The default Collation for string columns in temporary tables (not table variables) is the default Collation for [tempdb] (which should be the same as [model], which should be the Instance default, unless someone restored [model] from a server that had a different default Collation), not the Collation of the local Database. Meaning, even though the temp tables get created each time and thus you might expect them to act like "newly created tables" with the new Collation, they will in fact act like "existing tables" and will continue to behave as they did prior to the Collation change. If you need string columns in temporary tables to use the new Collation, you will need to explicitly set their Collation using COLLATE DATABASE_DEFAULT in the CREATE TABLE statements.

Hence you really need to do a lot of testing!

If you want to change existing columns then you will need to drop existing constraints, issue an ALTER TABLE ... ALTER COLUMN and then recreate the Constraints. You will also need to rebuild indexes that use any column that has its Collation changed as the sort order might be different.

Also, it is best to not use Collations starting with SQL_. Instead use Latin1_General_100_CS_AS. The Collations starting with SQL_ have been obsolete (even if not officially deprecated) since SQL Server 2000 was released. Their handling of VARCHAR / 8-bit data is obsolete and is not inline with newer behavior. Unfortunately, for backwards compatibility reasons, the default Collation for US English installations used to be the SQL_Latin1 Collations, as noted in the Using SQL Server Collations MSDN page:

  For backward compatibility, the default English-language (US) collation is SQL_Latin1_General*.

This is also noted in the default Collation chart in the Collation Settings in Setup MSDN page (hit Control-F and paste in sql_latin). I believe this default changed to a Windows Collation starting in SQL Server 2014, but the documentation, even for the SQL Server 2016 setup, still points to the 2008 R2 setup page for Collations.

Below is a script to see some of the behavior differences when changing a Database's Collation:

USE [master];
GO

IF (DB_ID(N'ChangeDatabaseCollationTest') IS NULL)
BEGIN
    CREATE DATABASE [ChangeDatabaseCollationTest] COLLATE Latin1_General_100_CI_AS;
END;
GO

USE [ChangeDatabaseCollationTest];
GO
-- Current DB Collation: Latin1_General_100_CI_AS

EXEC sp_help 'sys.objects';
-- Collation for [name] = Latin1_General_100_CI_AS

IF ('A' = 'a')
BEGIN
    SELECT 'Case INsensitive comparison works.';
END;
ELSE
BEGIN
    SELECT 'Case INsensitive comparison did NOT work.';
END;
-- Case INsensitive comparison works.

CREATE TABLE dbo.CaseTest_a (ID INT); -- success
SELECT * FROM dbo.CaseTest_A; -- success

CREATE TABLE dbo.CaseTest_A (ID INT); -- error:
-- Msg 2714, Level 16, State 6, Line 5
-- There is already an object named 'CaseTest_A' in the database.

ALTER DATABASE [ChangeCollationTest] COLLATE Latin1_General_100_CS_AS; -- success

IF ('A' = 'a')
BEGIN
    SELECT 'Case INsensitive comparison works.';
END;
ELSE
BEGIN
    SELECT 'Case INsensitive comparison did NOT work.';
END;
-- Case INsensitive comparison did NOT work.

SELECT * FROM dbo.CaseTest_A; -- error:
-- Msg 208, Level 16, State 1, Line 56
-- Invalid object name 'dbo.CaseTest_A'.

CREATE TABLE dbo.CaseTest_A (ID INT); -- success

EXEC sp_help 'sys.objects';
-- Collation for [name] = Latin1_General_100_CS_AS

ALTER DATABASE [ChangeCollationTest] COLLATE Latin1_General_100_CI_AS; -- error:
-- Msg 1505, Level 16, State 1, Line 23
-- The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for
--    the object name 'dbo.sysschobjs' and the index name 'nc1'. The duplicate key
--    value is (0, 1, CaseTest_A).
-- Msg 5072, Level 16, State 1, Line 23
-- ALTER DATABASE failed. The default collation of database 'ChangeCollationTest'
--    cannot be set to Latin1_General_100_CI_AS.


Should a Fact table contain a column with a single unique value?

What I recommend is that you have a fact_source table:

CREATE TABLE fact_source
(
  fact_source_id INTEGER,  -- auto-increment.
  fact_source_name VARCHAR (50)
);

Use your own RDBMS's (you didn't say which) own auto incrementing key system to generate the fact_source_id)

INSERT INTO fact_source fact_source_name) VALUES ('Sales'), ('Testing');

and use that as a FOREIGN KEY in the relevant tables.

You can then "pre-cook" your user-friendly (i.e. text) output in report queries if your testing shows that it works nicely and isn't a resource burden.

You should not use ENUMs for the reasons outlined here. Just because systems have features does not mean that you have to use them! You should always use either CHECK constraints (Unbelievably, MySQL doesn't have these) or a lookup table as described above.

Storing your key as a SMALLINT (or similar) will save a bit of space!


View delete statements in the Transaction log

You can view the individual rows that were deleted by looking for LOP_DELETE_ROWS operations still in the log:

select * from fn_dblog(NULL, NULL)
where Operation = 'LOP_DELETE_ROWS'

If the log was recycled (in simple recovery model) or truncated by backup (in full or bulk recovery model) then you will only be able to see the log operations still available in the log.

Understanding the log operations is quite complex, you need to be aware of things like undo or compensating operations to make sense of some log patterns you can encounter, but straight forward committed DELETEs are fairly easy to comprehend.


Specify the account in domain format Error in SQL Server 2016 installation on Windows 10

Error message you are getting because you did not provide any service account for the following services. You can see those are blank in the picture you provided.

SQL Server PolyBase Engine.
SQL Server PolyBase Data Movement.


After understanding your use better, I think the real answer is: you have no need to REINDEX.

REINDEX recreates the entire index. But indexes are maintained incrementally; as rows are inserted and deleted, or updated with new key values, the index is updated to reflect the change, as well as prior versions.

Because of the way MVCC works, when you delete a row, postgres can't actually delete the row or the index entry immediately, as there may be transactions that still have visibility to the row, so it just marks it deleted. VACUUM is run periodically to garbage collect dead tuples,  But indexes can still retain bloat where there are dead entries to old row versions. So, frequent updates to indexed columns or massive deletes can produce many dead index entries which make the key density of index blocks lower and produce inefficiencies in index operations.  This is called "index bloat".

You can detect whether you're suffering it using some scripts which inspect catalog views and calculate bloat (available through the prior link). If you do incur index bloat, periodic REINDEX operations may be called for.  In your use case, you have several million rows, and on a monthly basis delete or modify patches on the order of a few hundred rows.  Over the course of a year, this access pattern is likely to touch only a small fraction of a percent of the total rows, so any index bloat resulting will be minuscule and not justify the expense of a REINDEX operation.


Is DB mail supported on SQL Server hosted on Azure VM (IaaS)?

Database Mail is available on SQL Server on Azure IaaS and my suggestion is to configure it using SendGrid email delivery service (available on Azure Marketplace) that supplies your virtual machine with an SMTP relay. Leverage this service to setup database mail for your alerting or reporting needs.

Here you will find how to setup the SendGrid service on Microsoft Azure.

Once you have set up the SendGrid service you can configure it on your SQL Server VM as shown below.

First enable it on sp_configure:

/*
   Turn on database mail
*/

-- Select the correct database
USE [msdb]
GO

-- Just shows standard options
sp_configure
GO

-- Turn on advance options
sp_configure 'show advanced options', 1;
GO

-- Reconfigure server
RECONFIGURE;
GO

-- Turn on database xp's
sp_configure 'Database Mail XPs', 1;
GO

-- Reconfigure server
RECONFIGURE

Next, create an email account:

/*
   Creating mail account with Send Grid SMTP server
*/

-- Create a Database Mail account 1
EXEC msdb.dbo.sysmail_add_account_sp
  @account_name = 'act_Default_Email',
  @description = 'Mail account for use by all database users.',
  @email_address = 'craftydba@outlook.com',
  @replyto_address = 'craftydba@outlook.com',
  @display_name = 'SQL SERVER (IAAS-SQL16DEV)',
  @mailserver_name = 'smtp.sendgrid.net',
  @username = 'azure_d2dfaaa7a26e3f645f978bb723cd95cb@azure.com',
  @password = 'enter your unique password';
GO

-- Show the new mail accounts
EXEC msdb.dbo.sysmail_help_account_sp;
GO

In addition, you need to configure a mail profile.

/*
   Creating a mail profile
*/

-- Create a Database Mail profile
EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'prf_Default_Email',
  @description = 'Profile used for administrative mail.' ;
GO

-- Show the new mail profile
EXEC msdb.dbo.sysmail_help_profile_sp;
GO

Next step is to link the email account to the profile:

/*
  Linking the mail profile to the account
*/

-- Add the account 1 to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = 'prf_Default_Email',
  @account_name = 'act_Default_Email',
  @sequence_number = 1 ;
GO

-- Show the link between profile and accounts
EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'prf_Default_Email';

Next step, configuring database mail to give public access to the mail profile. This allows database users to send mail.

/*
   Given public access to profile
*/

-- Grant access to the profile to all users in the msdb database
EXEC msdb.dbo.sysmail_add_principalprofile_sp
  @profile_name = 'prf_Default_Email',
  @principal_name = 'public',
  @is_default = 1 ;

-- Show the new default profile
EXEC msdb.dbo.sysmail_help_principalprofile_sp

Finally use the following code to validate this solution.

/*
   Send test message
*/

-- Plain text message
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'prf_Default_Email',
  @recipients = 'craftydba@outlook.com',
  @body = 'The stored procedure finished successfully.',
  @subject = 'Automated Success Message' ;
GO


SQL Server not starting at Windows startup but starts manually

This issue happens because the SQL Server lost the logon id credentials or used an account without rights to start services.

For starting your services automatically, SQL Server needs an account with rights to start services during operating system start up.

To solve this issue you need to go
Administrative Tools > Services > SQL Server(MSSQLSERVER or the name of your instances) > Right Click >  Properties > Move to Log On > Set the Log On option using a Local System Account

If you have chosen this option, hit on Browse Button and find the Local Service Account or Network service Account there > Click in Apply.

Restart your computer and you will able to start your SQL Server.


You have a few options (TL;DR -> working function is towards the end of Option #2, BUT also see Update section after Option #3 !):

If you are ok with just hex values (0 - 9 and A - F) you can just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008:

SELECT CRYPT_GEN_RANDOM(5) AS [Hex],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS [HexStringWithout0x],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(10)) AS [Translated-ASCII],
       CONVERT(NVARCHAR(20), CRYPT_GEN_RANDOM(20)) AS [Translated-UCS2orUTF16]

returns:

Hex             HexStringWithout0x    Translated-ASCII    Translated-UCS2orUTF16
0x4F7D9ABBC4    0ECF378A7A            ¿"bü<Ý±Øï           튄ꆠ䤅㫘ᓟ멿ৢ폫씎䛯

As you can see, you need to use the format option of 2 on the CONVERT function so that it doesn't give you characters that, while more diverse, might be harder to type in, and you don't get the leading 0x. But you can also see that even with a single row, multiple calls will get different return values (just like NEWID() ).

Please note the "length" values passed into the CRYPT_GEN_RANDOM function. Since a hex byte is 2 characters, you only need to generate a 5-byte value. The CONVERT(VARCHAR... uses a "length" of 10 since each hex byte becomes a character. And the CONVERT(NVARCHAR... uses a "length" of 20 since every 2 bytes will become a character (when running this in a database with a default collation that does not end in _SC, which is most cases).
You can always create your own algorithm and pass in the value of CRYPT_GEN_RANDOM for the random aspect. AND, if you can structure your algorithm to be a single SELECT such that it can be done in an Inline Table-Valued Function / iTVF (usually using CTEs), then you technically can use a non-deterministic function (at least effectively) since the function call will pass in the expression instead of the result of that expression, like it does with Multistatement TVFs and Scalar UDFs:

USE [tempdb];
GO

CREATE FUNCTION dbo.GeneratePassword_InlineTVF(@RandomValue VARBINARY(10))
RETURNS TABLE
AS RETURN
  SELECT CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2) AS [HowRandomAmI?];
GO

CREATE FUNCTION dbo.GeneratePassword_MultistatementTVF(@RandomValue VARBINARY(10))
RETURNS @Result TABLE ([HowRandomAmI?] VARCHAR(100))
AS
BEGIN
  INSERT INTO @Result ([HowRandomAmI?])
         VALUES (CONVERT(VARCHAR(20), @RandomValue, 2) +
                 '~~' +
                 CONVERT(VARCHAR(20), @RandomValue, 2));
  RETURN;
END;
GO

CREATE FUNCTION dbo.GeneratePassword_ScalarUDF(@RandomValue VARBINARY(10))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2);
END;
GO

And then run the tests:

SELECT * FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10));
-- 27169EEC2B9CF7CC2731~~E9A95F49060BD41C0FF6

SELECT * FROM tempdb.dbo.GeneratePassword_MultistatementTVF(CRYPT_GEN_RANDOM(10));
-- 68EBC132814EA78602DE~~68EBC132814EA78602DE

SELECT tempdb.dbo.GeneratePassword_ScalarUDF(CRYPT_GEN_RANDOM(10)) AS [HowRandomAmI?];
-- 702DAF1C441C42FFBF5F~~702DAF1C441C42FFBF5F

And just to be sure about multiple rows in an iTVF:

SELECT TOP 5 rnd.[HowRandomAmI?]
FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10)) rnd
CROSS JOIN [master].[sys].[objects];

returns:

HowRandomAmI?
---------------
1D57F0ABFDE44BCAED00~~AD57E9E2FF01768BB86F
264674BE1C9ABBC1572E~~6C75CD4D472935FDFA40
CB54CC6BB5F31F42FDEA~~B3EC7061027FCD36C9AC
44525355FC15655C1F4D~~4DDF874CD06BC4F2D7A4
0E51B6364F193F588C93~~08E2ED40ED9752267EF7

Putting all of this info into practice, we can do the following with regards to the original request:

CREATE FUNCTION dbo.GeneratePassword_Real(@RandomValue VARBINARY(30))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH base(item) AS
  (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
  ), items(item) AS
  (
    SELECT NULL
    FROM   base b1
    CROSS JOIN base b2
  )
  SELECT (
     SELECT TOP (LEN(@RandomValue))
            SUBSTRING('1234567890QWERTYUIOPASDFGHJKLZXCVBNM',
                      (CONVERT(TINYINT, SUBSTRING(@RandomValue, 1, 1)) % 36) + 1,
                      1) AS [text()]
     FROM   items
     FOR XML PATH('')
  ) AS [RandomPassword];

And then execute it as follows:

DECLARE @PasswordLength INT = 10;

SELECT * FROM tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength));

CAUTION: Please see Item # 2 below related to running this for multiple rows!
You can create a SQLCLR function that would have access to randomizing functionality. The main disadvantage here is that a Scalar function is much more likely to have its return value cached than an iTVF.

Update

Given the newly provided context for usage of this function from the Question update:

  The idea behind the function is that if somebody requires a password reset I could do the following:

    UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID

we can address the following concerns as expressed in a comment on this answer:

  One needs to pass a parameter to the function with a random value. This enforces that it is necessary to know one way of creating random strings in order to use the function...

True. And it is admittedly slightly inconvenient. However, that does not negate this approach's ability to solve the issue at hand. It just requires some comments / documentation on how to properly use this function.

  ...the function couldn't be executed inside another function because you wouldn't be allowed to use a non deterministic function to generate random values inside the container function.

True. But there is no need to call this function within another function. This doesn't need to be a scalar function in order to be used in an UPDATE:

  USE [tempdb];
CREATE TABLE #Users (UserID INT NOT NULL PRIMARY KEY, [Password] VARCHAR(20));
INSERT INTO #Users (UserID, [Password]) VALUES (1, 'a'), (2, 'b');

DECLARE @PasswordLength INT = 10;

UPDATE tmp
SET    tmp.[Password] = pass.RandomPassword
FROM   #Users tmp
CROSS APPLY dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
WHERE  tmp.UserID = 1;

SELECT * FROM #Users;

With regards to handling multiple rows (please see multi-row test in Option 2 above), functions of any type (user created, at least) -- Scalar UDF, Multistatement TVF, iTVF, SQLCLR UDF, or SQLCLR TVF -- are not guaranteed to run per row! You might need to get creative in how you convince the Query Optimizer to not cache the result, but there might be cases when nothing works. The following example shows the same iTVF being called twice. Passing in just @PasswordLength causes the output value to be cached. However, when you pass in a formula that incorporates a field from the current row, then it might behave as you are expecting it to:

  DECLARE @PasswordLength INT = 10;
SELECT pass.RandomPassword, pass2.RandomPassword
FROM [master].[sys].[objects] sac
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM((sac.[object_id] % 1)
            + @PasswordLength)) pass2

returns:

  RandomPassword    RandomPassword
--------------  --------------
KX1M1NSKE7        8I1OK6TJI3
KX1M1NSKE7        S0RA6AWKAJ
KX1M1NSKE7        MKXV9UAB0Y
KX1M1NSKE7        4RQ2K6O1RP
KX1M1NSKE7        8J8PKJP7K2

The point made by @BaconBits in a comment on the question, regarding it being best to not store readable passwords, is a valid and good point. However, circumstances might prevent that option from being possible, or at least possible at this point in time.


The replication configuration (which master to sync from, which relay log & which change it's up to) isn't stored in the my.cnf, it's stored in the master.info / relay.info files, so you should be OK to follow that process.


Computing values on the fly vs decrementing bucket contents

Computers don't get tired.

Nobody has so many employees that the summation over a well-indexed set of rows of vacation actually taken would grind their servers to a halt.

What is a problem is precalculated data getting out of whack with respect to your raw transactional data.  If you're tracking vacation you're going to need to keep a list of days taken no matter what, for audit purposes, if nothing else.  What happens if an employee challenges your calculation of their vacation taken?  The only way you'll be able to justify your number is to go back and show the details.

Since you'll already have the transactional details, there's not much point in keeping a precalculated sum of these details.

From a code complexity/maintenance standpoint you're not really saving anything.  You write the code that calculates the remaining vacation entitlement once.  It's a single select statement that has three or four more keywords in it than the single select statement that would read a precalculated row.  That's not much of a savings.

What you're thinking about is often called pre-optimization and it's something to be avoided, because it actually makes things worse more often than it makes things better.


How widespread is the adoption and usage of database snapshots?

Database snapshots are used in SQL Server 2005 or later every time you do a DBCC CHECKDB - so they are widely used, even if it is behind the scenes. Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support.

It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs. The only downsides, apart from no tools support, is to watch disk capacity where you put the snapshot file, and ensuring you don't have too many snapshot databases running on the server at the same time.


comparing two time values with 2 column respectively after converting in 24 hour format

Based on the example output I'm assuming the requirement is to display rows where the time1-time2 range falls completely outside of the desired 10:00am-12:00pm range.

Assumption:

time2 >= time1

This is going to be easier if we convert the strings to times and let the database do the time comparisons for us.

In MySQL we can convert the given strings to (date)times via the following construct:

str_to_date(<string_value>,'%h:%i %p')

The rows we're looking for should match one of the following tests:

time1 > 12:00 pm
time2 < 10:00 am

One possible solution:

select time1,
       time2
from   splash
where  str_to_date(time1,'%h:%i %p') > str_to_date('12:00 pm','%h:%i %p')
or     str_to_date(time2,'%h:%i %p') < str_to_date('10:00 am','%h:%i %p')
order by 1,2;

time1      time2
========   ========
01:00 pm   10:00 pm
05:00 pm   11:59 pm

Was having some timeout issues with the OPs fiddle so here's a rextester fiddle for the above solution.


Perform UNION ALL regardless of column order in the two joined tables

I think you have two options. Either type out the columns in your query or do something with dynamic SQL.

declare @sql nvarchar(max)
declare @col nvarchar(max)

select @col = stuff((select ','+quotename(C.COLUMN_NAME)
                     from INFORMATION_SCHEMA.COLUMNS as C
                     where C.TABLE_NAME = 'Table2'
                     order by C.ORDINAL_POSITION
                     for xml path(''), type).value('.',  'nvarchar(max)'), 1, 1, '')

set @sql = 'select '+@col+' into Table1 from Table2 union all '+
           'select '+@col+' from Table3'

exec (@sql)


Migrating or exporting a database from azure to SQL Server 2016

Export the Data-tier to local disk from the Azure database. It will save a bacpac file.

  Right click the database you need to export --> Task-->Export
  data-tier application and follow the steps until the bacpac file is
  created.

  Import the backpack file from the local disk to the On premises Instance by using the same utility from tasks.

  Right click the database branch in your on premise instance --> Import
  data tier application and now use the bacpac file saved in first step
  to create the database.


What are the other types of database architectures?

Most databases are client/server, although some embedded databases can be used purely like libraries, and share the application's address-space.

The server architecture can vary between databases, and differ from what you described too.  MySQL runs entirely as a single process (with multiple threads).  It does not require any OS shared memory segments or inter-process communication.


What is the date time format for msdb.dbo.agent_datetime scalar value function?

Are you asking about the format or the value?
By "value" I mean if it is in UTC or the local datetime value.
The function doesn't have a stored value, it work with what you pass into it and returns a datetime value based on that. I.e., there's no UTC relevance for this.
The columns, for instance in sysjobhistory, are not in UTC. They are in the local timezone (for your SQL Server instance).
As for the format:
The tables uses a pair of int columns - one for date and one for time. So the int 20191223 means 2019-12-23 (I'm using ISO 8609 format when communicating datetime values).
The function accepts two parameters, both int. One for the date and one for the time, as described above. It returns a datetime value (which has no format - it is just a value).
In case you wonder how I know it returns a datetime and not datetime2(3): You can investigate a value by casting it to sql_variant and then investigate properties of that value using the SQL_VARIANT_PROPERTY function:
DECLARE @a sql_variant
SET @a = dbo.agent_datetime(20201023,154500)
SELECT SQL_VARIANT_PROPERTY(@a, 'BaseType')

Above returns:
datetime


Optimal database for large number of columns or rows

PostgreSQL

  @Dobob everything can be normalized :) If it's not very secret, what sort of data it is? (Asking as a geneticist-turned-DBA.)

This is generally true. And you should start at looking how you can normalize. Clearly you're averse to the idea, and you can't always normalize. For instance, a raster is often a collection of readings from a scientific instrument, and you can't really denormalize that. They're taken at the same time, represent the same sample and frequently have 1,000s of data points. (Though now you can store them in PostGIS).

Solution for Problem 1

PostgreSQL does not support 16,383 columns on a table (or more than 1,600+ for that matter), but it does supports SQL Arrays. You can probably make use of that,

CREATE TABLE foo AS
  SELECT
    r AS id,
    array_agg(c) AS genetic_stuff
  FROM generate_series(1,567)
    AS r
  CROSS JOIN LATERAL generate_series(1,16382)
    AS c
  GROUP BY r;

Now you have a table that meets the first criteria but isn't insane.

          Table "public.foo"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 id            | integer   |
 genetic_stuff | integer[] |

Finding average is simple..

SELECT id, avg(x)
FROM foo
CROSS JOIN LATERAL unnest(genetic_stuff)
  AS t(x)
GROUP BY id;

And for that whole operation Execution time: 3865.308 ms! Can complain about that. Feel free to use a MATERIALIZED VIEW if you want to cache that average.

For Standard Deviation just use the appropriate aggregate function in the place of avg(). Execution times are about the same.

I'm not sure how you're querying this here, but without an index (worst case situation) is pretty simple and fast (considering it's a seq scan) scanning through an array with 16,000 items isn't slow either for a modern a CPU, and on PostgreSQL 9.6 this seq scan should even run in parallel (afaik).

SELECT * FROM foo
WHERE genetic_stuff @> ARRAY[5];

Can you use an index? Sure. From the intarray module,

  Two GiST index operator classes are provided: gist__int_ops (used by default) is suitable for small- to medium-size data sets, while gist__intbig_ops uses a larger signature and is more suitable for indexing large data sets (i.e., columns containing a large number of distinct array values). The implementation uses an RD-tree data structure with built-in lossy compression.

Let's give it a shot...

CREATE INDEX ON foo
  USING gist(genetic_stuff gist__intbig_ops);
VACUUM FULL foo;

                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_genetic_stuff_idx on foo  (cost=0.14..8.16 rows=1 width=22) (actual time=0.119..47.846 rows=567 loops=1)
   Index Cond: (genetic_stuff @> '{5}'::integer[])
 Planning time: 0.072 ms
 Execution time: 47.948 ms
(4 rows)

Not sure what more you want than index scan on that. Probably be more useful if not every row had an element of 5, but you know it's only sample data.

Anyway have at it.

Solution for Problem 2

Shy of that, the other thing isn't a problem at all a bit of overhead because Pg rows are fat, but you're not talking many rows and ultimately it shouldn't matter this below table meeting that criteria is only 5088 kB on my system. Hardly worth concerning yourself with.

CREATE TABLE baz
AS
  SELECT
    x AS id,
    x*2 AS x2,
    x*3 AS x3,
    x*4 AS x4
  FROM generate_series(1,117493)
    AS t(x);


PL/SQL Can't grant privileges while logged as system

SYSTEM is a typical database user (such as HR or OE)

The DBMS_LOB package belongs to SYS schema so that only the owner(Or user having the GRANT OPTION) can grant the object privilieges to other users. So if you connect as SYSDBA using SYSTEM user you will be connected to SYS schema.

SQL> conn system/oracle
Connected.
SQL> show user
USER is "SYSTEM"
SQL>

SQL> grant execute on dbms_lob to hr;
grant execute on dbms_lob to hr
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn system/oracle as sysdba
Connected.
SQL>
SQL> show user
USER is "SYS"
SQL> grant execute on dbms_lob to hr;

Grant succeeded.

SQL> revoke execute on dbms_lob from hr;

Revoke succeeded.

OR

You can grant EXECUTE on DBMS_LOB to SYSTEM user with GRANT OPTION then user SYSTEM user to grant that object privilege to other users.

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_lob to system with grant option;

Grant succeeded.

SQL> conn system/oracle
Connected.
SQL> grant execute on dbms_lob to hr;

Grant succeeded.

SQL>


Alrighty well I sorted it out. It was indeed the having clause messing things up. Took advice from a similar issue and wrapped the count query in a query to get the count of the results. Should have dug up the solution earlier but searching for "get count in query with having clause" returns thousands of results for Having Count(*) type of queries. Thanks for the advice though guys. I appreciate it.

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);
SELECT COUNT(*) as search_results FROM (
SELECT
    id, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
) search_counts;

END


Query in PostgreSQL with large quantity of squid access requests

You are representing time in 3 different was.  The query is using epoch, while the index is using timestamptz, and the partition constraints are using year and month as two separate fields.  So the query can make use of neither the index nor the partition constraints.

You should probably change them all to use timestamptz.  In the query, instead of converting timestamptz to epoch on the column "time", convert epoch to timestamptz for the BETWEEN constants. (Or just have the client send timestamptz rather than epoch in the first place)

For the check constraint, you could use date_trunc("month",NEW."time") and date_trunc("month",NEW."time" + "1 month") to arrive at the endpoints to put into the check constraint. You would want to spell out the check constraint something like "time" >= low_limit and "time" < high_limit rather than using BETWEEN.


MySQL COUNT Query taking too long time to execute

There are three aspects you need to rework
ASPECT #1 : THE QUERY
Please look at the Query
SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
    ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
LEFT JOIN `yuldi`.`categories` AS `Category`
    ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction';

You are doing LEFT JOINs. That is not necessary for doing a COUNT. Why ?
If you are counting the number of businesses, you only need to count from one table
SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`;

Since you have a WHERE clause on Category.slug, the JOINs are needed. You should switch from LEFT JOINs to INNER JOINs. That will produce a smaller internal temp table. That temp table will be searched for the slug 'building-construction'.
ASPECT #2 : INDEXES
From the EXPLAIN plan, I see two indexes similarly named

fk_businesses_has_categories_categories1_idx
fk_businesses_has_categories_businesses_idx

You need to look at the columns for them and make sure the column lists are not identical.
You may want to make two indexes
ALTER TABLE BusinessesCategory
    ADD INDEX bus_cat_ndx (business_id,category_id),
    ADD INDEX cat_bus_ndx (category_id,business_id)
;

Having compound indexes could mean less culling through tables for JOIN information.
Keep in mind, this suggestion is a blind one since I do not know the key distribution of your data.
ASPECT #3 : CACHING
Gathering index statistics is the sake of query evaluation does not immediately translate into a slow query. That slowness is manifested by the real culprit : CACHING. The reason your query runs faster the second time around has to do with the data you read the first time. The data you needed was most likely not available in MySQL's caches when you issued the query.
The two main caches for MySQL are the InnoDB Buffer Pool (for queries hitting InnoDB tables) and the MyISAM key cache (for queries hitting MyISAM tables).
InnoDB caches data and index pages, while MyISAM caches only index pages.
When retrieving data for comparison, mysqld tries to determine if the data it needs is in RAM first. There are server status variables to monitor for this:
InnoDB Caching

Innodb_buffer_pool_read_requests : The number of logical read requests.
Innodb_buffer_pool_reads : The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

MyISAM Caching

Key_read_requests : The number of requests to read a key block from the MyISAM key cache.
Key_reads : The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

If your query was slow the first time, it simply means the data you need for the query was not in a cache. This would have been indicated by either your Innodb_buffer_pool_reads or Key_reads incrementing.
The second time, your data and/or index pages for the same query would be in RAM and more available for your queries and queries from others.
I suggest you look over the sizes of your InnoDB Buffer Pool and/or MyISAM key cache.


How do you roll up header-level values on a fact table with line-item granularity?

I'd introduce a magic Shipping product, and put the shipping value against this only (leaving open the possibility to have multiple shipping products too - Heavy / Overnight / etc).  I'd probably still keep the ShippingAmount separate from the ProductAmount column though, so you can easily add up product sales only.


I updated class_teachers table to class_teachers_section to include section. The idea is a class can include different sections with different teachers. For example a grade 1 PE class can include all sections; the teacher for boys will be different from that for girls.

select class.name as classname,
   class.hours as hours,
   teachers.first_name as teacherFirstName,
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls
 left join class on class.id = cls.class_ref
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'


Electricity off while shrinking database, how can I recover my database?

Wait patiently for it to recover.  This is what is supposed to happen.  While waiting, try to track down your latest backup, just in case.  I suggest a small investment in a UPS will avoid this problem in the future.


Statistics not collected on Insert-Select

This only works with direct path loads: INSERT /*+ APPEND */ INTO ... SELECT ....
It does not apply to conventional path insert (INSERT INTO ... SELECT ..., without /*+ APPEND */ hint).
This is also explained on the site behind the link.


MySQL slow query with IN() clause on indexed column

You may try a temporary table and join against that:

create temporary table t1
( type varchar(255) not null
);

insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent1Happened');
insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent2Happened');
insert into t1 (type) values ('Namespace.Domain.User.Event.SomeEvent3Happened');
...

create index x1 on t1 (type);

SELECT COUNT(id)
FROM events e
JOIN t1
    ON e.type = t1.type;


all rows from left table with sum of amount from right table

Figured it out myself.  My where clause is telling sql to only show rows that are 'Bet' and have that date criteria, so without me realising it I'm telling sql to show me rows that had bets.

Here is my alternative query...

select i.accountnumber, i.firstname, i.lastname, sum(isnull(amount,0))
from accountinformation i
left join accountactivity a
    on i.accountnumber = a.accountnumber
   and a.transactiondate >='2015-07-31'
   and a.transactiontype = 'Bet'
group by i.accountnumber, i.firstname, i.lastname

Putting the conditions in the join clause instead of the where clause makes the left join behave as expected.


Red Gate SQL Server Source Control
Visual Studio Database Projects
Developer discipline


Select only rows with at least 2 non-missing values

I have the following way to get the IDs of the rows that have at least two not null values; however, I am afraid this is not efficient when it comes to few million rows per table. I'd like to share it anyway:

mysql> select * from l;
+------+------+------+------+------+------+------+------+
| id   | var1 | var2 | var3 | var4 | var5 | var6 | var7 |
+------+------+------+------+------+------+------+------+
|    1 | NULL |    4 |    9 | NULL | NULL | NULL |    7 |
|    2 | NULL | NULL | NULL | NULL | NULL |    1 |    2 |
|    3 |    4 |    5 |   23 |    1 |    2 |    4 |   22 |
+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l;
+------+----------------+
| id   | non_null_count |
+------+----------------+
|    1 |              2 |
|    2 |              0 |
|    3 |              5 |
+------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT id FROM (select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l)x WHERE non_null_count>=2;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.01 sec)

Basically, I am "counting" how many non-null values exist in each row.


Re: partitioning:

This is by far the best way to deal with large data sets. By allowing multiple indexes to run across different ranges instead of one index for the whole set, each individual index will stay at much higher quality.

If you can configure your application to maintain referential integrity itself, then you can safely drop the foreign keys. You'll have to make sure that referenced rows in child tables are updated appropriately whenever the parent row is updated. The database will no longer prevent you from messing that up, and cascade operations won't be available anymore. So you would need to program that into your application. Creating triggers to do it automatically will help.

Re: Indexing:

B+Tree indexes will start to perform poorly if the depth gets too high. The post you linked contains some good information. e.g. forget about even trying to access columns without an index.

For writes, if you have periodic content loads then it would make sense to drop the indexes before bulk insertion and recreate them afterwards. This would likely be faster than sequential individual inserts to both table and index. Partitioning would make this easier, as you could insert all data into a new partition, and then index it after.

Re: Alternative options

Use a better database. ;-) You will really start to feel the limitations of MySQL if your database grows to this scale. Other DBMSes offer a far more capable set of tools for dealing with data of this scope. Which database that is depends on your budget, use cases and contraints. MySQL may very well be "good enough", but you should definitely evaluate alternatives before diving in.

Re: Clustering

Clustering is better in some situations, worse in others. e.g. It will allow you to shard data, but sharding is just horizontal partitioning, so will have the same restrictions on foreign keys. Maintaining a cluster can also create a lot of overhead, particularly for write-intensive applications.


How to use floating-point storage for time data type

From the linked page you provide:

  Note: When timestamp values are stored as eight-byte integers (currently the default), microsecond precision is available over the full range of values. When timestamp values are stored as double precision floating-point numbers instead (a deprecated compile-time option), the effective limit of precision might be less than 6. ...

What this says is that in order for timestamp values to be stored as double precision floating-point (and to allow precision up to 10), you need to compile Postgres yourself with that option.

It also says that the precision is then not exactly 10 but it varies depending on the value and may even be less than 6 for some (very high or very low) values.


Setting NLS params in logon trigger don't work in oracle 11g

Changing the order of the SET seemed to make a difference, as it is working now.

create or replace
TRIGGER schema.django_nls_tr
AFTER logon ON DATABASE
BEGIN
execute immediate 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA'' NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'' NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF''';
END;

I don't really like this solution, as I still don't understand exactly why changing the order should matter. Perhaps the TERRITORY isn't being set correctly, but it was already the default so we're not TOO worried right now.

Also, django does do a TO_TIMESTAMP. It expects most date fields to be TIMESTAMP(6) but we had it pointed at a legacy db with DATE type fields. The NLS shouldn't matter too dramatically in standard deployments.


How to select a subset from a query result

This query SUM all the distances between 6 and 7:

SELECT AssetId, [Event], GeofenceId
    , DistanceCoveredK = SUM(DistanceCoveredK)
    , TimeSpentDuringVisitSeconds
FROM (
    SELECT AssetId = vm.iAssetId
        , [Event] = c.eEventCode
        , DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance(
            LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION BY vm.iAssetId, vM.dtUTCDateTime ORDER BY c.dtUTCDateTime)
        )
        , GeofenceId = vm.iGeofenceId
        , TimeSpentDuringVisitSeconds = DATEDIFF(SECOND, vm.dtUTCDateTime, c.dtUTCDateTime)
    FROM VehicleMonitoringLog vm
    CROSS APPLY (
        SELECT top(1) eEventCode, dtUTCDateTime, sptGeoLocaitonPoint
        FROM VehicleMonitoringLog WHERE iAssetId = vm.iAssetId AND dtUTCDateTime > vm.dtUTCDateTime AND eEventCode = 7
        ORDER by dtUTCDateTime
    ) c
    INNER JOIN VehicleMonitoringLog p ON p.iAssetId = vm.iAssetId AND p.dtUTCDateTime >= vm.dtUTCDateTime AND p.dtUTCDateTime <= c.dtUTCDateTime
    WHERE vm.eEventCode = 6
) g
GROUP BY AssetId, [Event], GeofenceId, TimeSpentDuringVisitSeconds

Output:

AssetId Event   GeofenceId  DistanceCoveredK    TimeSpentDuringVisitSeconds
6024    7       1015        172,642601234594    100
6024    7       1015        1210,11662866215    28393

I changed the datatype of sptGeoLocaitonPoint in your SQL Fiddle from int to geography. New SQL Fiddle is here


USAGE = no privilege

https://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_usage

Now you have to options, either delete the user or grant all privileges to the user, which ever make sense to you

CASE 1: If you want to remove 'foo'@'10.0.0.3'

REVOKE USAGE on *.* from 'foo'@'10.0.0.3' ;

will never work as there is no grant called USAGE So use

DROP USER 'foo'@'10.0.0.3';

CASE 2: If you want to grant all privileges 'foo'@'10.0.0.3'

GRANT ALL PRIVILEGES ON *.* TO 'foo'@'10.0.0.3';
FLUSH PRIVILEGES;


Does an analysis/optimization tool like SqlYog exist for SQL Server 2005 (and later)?

Microsoft provided, for free, SQL Server Management Studio (SSMS) starting with SQL Server 2005. Is it not exactly like SqlYog but there are similarities. With regards to table space I would direct you to the Standard Reports that were added to SSMS after SQL 2005 SP2. They provided a more eye friendly look at allocation of space, index usage, performance information, etc.


Short version: It depends. Generally spoken Sybase SQL Server is smart enough to do things the fastest way, though.

Long version:

Sybase's query processor is, at it's core, very similar to the one used in MS SQL Server.

It will create worktables (internal temporary tables; not visible to the user) if the result set is sufficiently large to overflow available memory (similar to a table spill in SQL Server). Otherwise, it'll do a pair of index scans (you do have indexes on date and symbol in both tables, right?), then a join and output, all in memory.

Caveats:

If the indexes (indices?) aren't selective enough, it will default to a table scan instead. I'm not sure what "enough" is, but I think it's around 30% (i.e. if statistics tell the optimiser it'll get more than 30% of the rows in the table, then just scanning straight through the table will cost the optimiser less than index scanning+lookups).
Worktables also can come into play if you have an ORDER BY statement or if the optimiser decides to use a merge join and the join criterion isn't the primary key (since a merge join requires sorted inputs).
Also, the cost estimation at the bottom of any showplan for any non-trivial plan is meaningless, it's a dimensionless value (i.e. just a number) meant for comparisons to other plans for that query only. i.e. You can't compare two queries on two different data sets (even on the same server) and say one's more expensive than the other, because the cost estimation isn't comparable like that. There's also some cases I've seen where the cost estimation for the same query has been wildly off (e.g. merge join with a cost of 1000 takes several hours to complete, SET MERGE_JOIN OFF and it defaults to NL joins with a cost of 10000, but runs in minutes). Optimising Sybase's query processor is a whole different book (actually there's four books, I just linked the advanced QP one).
There are some tiny corner cases where the query processor will do things in an obviously suboptimal way, but in 99% of cases you won't run into those.

That said, based on what I've seen the query plan would look something like this: (this is vastly simplified, the actual output from sp_showplan is rather more detailed)

|ROOT:EMIT (aka select)
|
|  | NL JOIN (nested loop join)
|  |
|  |  | RESTRICT Table X
|  |  |
|  |  |  | SCAN Table X (using index, forward scan)
|  |
|  |  | RESTRICT Table Y
|  |  |
|  |  |  | SCAN Table Y (using index, forward scan)

The reason it shows the RESTRICT after the SCAN is because without the SCAN there's no data to RESTRICT - I'm reasonably sure that you can effectively consider them as one operation in this case (i.e. the restriction conditions will be part of each index scan, rather than the index scan copying a whole lump of data and restrict throwing most of it out).


Postgresql subquery speed much slower than individual queries

Seems you are running in a weakness of the query planner: The best index is sometimes not used for joining tables. Had a similar problem here:
Algorithm for finding the longest prefix (Chapter "Failed attempt with text_pattern_ops")

In Postgres 9.3 You could try this version with LEFT JOIN LATERAL:

SELECT *
FROM  (
    SELECT coord
    FROM   taduler.postal_code
    WHERE  postal_code = 'T1K0T4'
    LIMIT  1
    ) pc
LEFT JOIN LATERAL (
    SELECT *
    FROM   public.timezones tz
    WHERE  ST_Intersects(pc.coord, tz.geom)
   ) tz ON TRUE;

Something similar Worked for @ypercube's solution in this related answer.
LATERAL requires Postgres 9.3+, though.

In PostgreSQL 9.1, it might help to encapsulate the first query in a CTE, but I doubt it. (Don't have a PostGis installation here to test.):

WITH pc AS (
    SELECT coord
    FROM   taduler.postal_code
    WHERE  postal_code = 'T1K0T4'
    LIMIT  1
    )
SELECT *
FROM   pc
JOIN   public.timezones tz ON ST_Intersects(pc.coord, tz.geom);

A plpgsql function to encapsulate two separate queries should certainly do the trick:

CREATE OR REPLACE FUNCTION f_get_tz(_pc text)
  RETURNS SETOF public.timezones AS
$func$
DECLARE
   _coord geom;
BEGIN

SELECT coord
INTO  _coord
FROM   taduler.postal_code
WHERE  postal_code = _pc
LIMIT  1;

RETURN QUERY
SELECT *
FROM   public.timezones tz
WHERE  ST_Intersects(_coord, tz.geom);

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_get_tz('T1K0T4');


SQL Server large transaction log for simple recovery model? help!

For completeness, converting my comment to answer :

If the database is EMPTY, what is the initial size of the transaction log defined when the DB was created ?

If the initial size of the T-log is defined more than you need, then thats the problem.

You should always carefully define the transaction logsize.


You can calculate a RANK/ROW_NUMBER based on this logic:

ROW_NUMBER()
OVER (PARTITION BY IMMS_ID
      ORDER BY CASE WHEN status='DI' THEN 1
                    WHEN status='OI' THEN 2
                    WHEN status='FI' THEN 3
                    WHEN status='OV' THEN 4
                    WHEN status='FV' THEN 5
                END)


How does Postgres handle integer data of different sizes in the same column?

If I have a bigint column and in one row store 1 and in another store 999999999999, will these take up different amounts of space on disk?

Bigint is always 8 bytes regardless of what the decimal representation is

1            | 0000000000000000000000000000000000000000000000000000000000000001
999999999999 | 0000000000000000000000001110100011010100101001010000111111111111

  Will Postgres have an easier time doing queries and calculations with the smaller data?

Yes. int4 takes half the amount of space as int8. That means at most you'll save 4 bytes per tuple in the row, and the index will be half the size.

  The motivation for my question is that I have a lot of data in this form: 1000000306429071. I'm wondering if it will be valuable to strip the 1 and leading 0s.

That sounds like a wise idea to me. The max value for an int4, is 2147483647, putting them next to each other.

2147483647
306429071

You can see that you're not even close to exhausting that range. You're only 14% there. Do you ever expect to need more than 2147483647? If so, you definitely want to stay with int8.

You can return the value with..

1e15::bigint + 306429071


Couple of ways that I know of

Run procedure in ssms, then copy results into excel (make sure to turn on the option to copy column headers in tools,options,query results)
Create a SQL server integration services (SSIS) package to run the procedure and write the results to a CDC or excel file.
If you have SQL server reporting services (SSRS) you can create a report to run the procedure and display the results, then from the report website you can export the report to excel/csv. This can also be scheduled through SSRS subscriptions.


basic question about user-/role concept in postgres-databases

There are basically two choices:

Your application does the user and permission management itself. Then it typically has a single “application user” in the database.
Your application has per-user or per-group database users and handles user and permission management with database means.

Both solutions are fine. The second solution is maybe less commendable if you have lots of users; also, connection pooling usually works better with the first technique.

If you use database users like in the second solution, you should use NOLOGIN roles (user groups) in PostgreSQL to carry the permissions, and you assign the named users to the groups as appropriate. That way you don't have to GRANT and REVOKE a lot of permissions whenever you add or remove a user or change a user's role in the application.

To answer your question, there are no restrictions on concurrency with different database users.


Is wrapping only possible for package bodies?

PL/SQL Source Text Wrapping

  You can wrap the PL/SQL source text for any of these stored PL/SQL
  units, thereby preventing anyone from displaying that text with the
  static data dictionary views *_SOURCE:

  Package specification
  Package body
  Type specification
  Type body
  Function
  Procedure

A script containing a CREATE TABLE statement does not fall into any of the above categories.

Also, keep in mind, unwrapping such source takes seconds (for example: http://www.codecrete.net/unwrapit/). Oracle generously classified this method as low-assurance security (see above URL), but I would rather classify this as non-existent security.


Is there a good way to select a row where the key represents the lower bound of a range?

There's no single right answer for any performance question.  The answer is always that query optimizers are very smart, to a point.  So the most efficient design or query will depend on the number of records, what kind of indexes you have, how selective they are, and many, many more factors.

If you want to avoid keeping both bounds of your ranges, you can search using TOP 1 and order by lower_bound but I wouldn't expect this to be efficient necessarily.  I'd suggest profiling it this way to see if the performance is acceptable.

Having an index on lower_bound will probably help, depending on the number of records in your table.


How to select two columns from two different tables using foreign keys from another third table?

SELECT user.name, movie.name
FROM user, movie, list
WHERE list.user_id  = user.id
  AND list.movie_id = movie.id
ORDER BY 1,2;

Or, if you need to see the word JOIN in the query text

SELECT user.name, movie.name
FROM user
JOIN list  ON list.user_id  = user.id
JOIN movie ON list.movie_id = movie.id
ORDER BY 1,2;


Based on the complexity of your wizard, you may face here two possible scenarios. A wizard is nothing else but a paged form.
1. Your wizard has always the same amount of answers
1.a. The questions are always the same.
2.b. The questions may be different (opting between one payment method or the other, and thus the answer might be different)
2. Your wizard may have different amounts of questions, eg. you have a groups of answers that might be enabled or disabled.

So let's analyze case by case.
1.a That's easy, you have questions Q1, Q2, Q3, Q4... And you can simply store the answers in the same order as a simple series, as in your example.
1.b. This is a bit more complex, since storing simply the answers is not enough, as the questions are not the same. However, since we know the amount of answers is always the same you can create a table with as many columns as possible answers answers, adding a special columns to identify ambivalent questions. So the structure would be something like

CREATE TABLE FormResults (
A1 VARCHAR(50), -- eg. Name
A2 VARCHAR(50), -- eg. Surname
Q3 INTEGER, -- Q3 is the Payment method chosen, can be PayPal, credit card, bank transfer
A3 VARCHAR(50), -- This answer can be the email for PP, the card number, the bank account... corresponding to the Q3 chosen.
A4 DECIMAL(4,2) -- e.g. The amount
);

The benefit of this solution is that your table will be quite compact with nulls only when an answer is optional.

For the case 2, if the number of answers is variable that's the most complicated case. The easiest way to solve this is simply to create a table with columns for all possible answers. However depending on your wizard this may result in a table plenty of NULL values. If this is the case, and if this is a problem in your RDBMS, then you may create a table like

CREATE TABLE FormResults (
AnswerSetId INTEGER,
QuestionId INTEGER,
AnswerValue VARCHAR(50)
);

Being the PK (AnswerSetId, QuestionId)

Here you may find that different answer sets will have different number of rows in this table, depending on the choices of the user. The good thing of this solution is that it creates a very compact and easily indexable table.


The WHERE clause is specific to each individual SELECT so you would need to move the one you have to the first SELECT like this:

SELECT PubKey, Title FROM Publication
WHERE EXISTS (
    SELECT *
      FROM UserPublication
     WHERE UserPublication.PubKey = Publication.PubKey
)
UNION
SELECT NoteKey, Title FROM Note

However, if you want to specify the WHERE clause across the entire result set. This must be done like so:

SELECT * FROM (
    SELECT PubKey, Title FROM Publication
    UNION
    SELECT NoteKey, Title FROM Note
) AS A
WHERE EXISTS (
    SELECT *
      FROM UserPublication
     WHERE UserPublication.PubKey = A.PubKey
)


Determining which SQL Server tables are accessed over a week?

One possible way is to look into index usage statistics, such as sys.dm_db_index_usage_stats() (and several others with similar names).

Two possible catches here are:

Their data isn't persisted between server restarts, so make sure
that uptime is sufficiently long.
If you have scheduled maintenance jobs, for example, regular index rebuilds, they will spoil the statistics. But at least you will be able to separate your objects into 2 categories: one with similar access counts across all of them and the rest is well above that.

Overall it's not very reliable, but at least will give you some quick initial insight. The second step could be, for example, denying all permissions on suspected tables and watch for your work phone / email box :).


Maintaining mongodb for a huge list of message "documents"?

A capped collection would allow you to maintain a relatively small list, though you would want to have a decent buffer, or be very confident in terms of the size of your documents.  The other option would be a TTL collection, but that would be more prone to fragmentation since it will be doing a lot of deletes (this will mean having to deal with said fragmentation regularly with repair, compact or a resync).
Based on what you outline, this sounds very like what people have done when looking to implement a message queue with MongoDB - you use the capped or TTL collection for your immediate needs, then you decide what level of durability you want for the documents long term. That is, store them in another collection, database, or even another instance of MongoDB entirely before they are removed.  At a larger scale I have even seen people have long term and short term shards, but that should be a long way down the line from your initial attempts.
If you do go down the path of a capped collection, with "long term" storage elsewhere, it is often beneficial to have the capped collection on one disk and the other data elsewhere (capped collections tend to have sequential access patterns, whereas regular collections tend towards random access).  That means using different databases, different disk mount points, and the directoryperdb option.
There are a lot of potential options here but thankfully several of the approaches have been publicised and documented.  To start I would take a look at these:
http://www.mongodb.com/presentations/mongodb-message-queue
http://captaincodeman.com/2011/05/28/simple-service-bus-message-queue-mongodb/
http://blog.mongodb.org/post/29495793738/pub-sub-with-mongodb
Hopefully that's enough to get you started.


MySQL adds broken comment script after each line

This is a bug on MySQL Workbench >=6.3.* reported in these cases #76578 and HERE.

This is what ALTER TABLE do:

It adds a COMMENT '' on every line of the SQL Script to apply:

I recommended you to downgrade your MySQL Workbench to 6.2.* until they fix this.

Tested in Ubuntu LTS 14.04 and MySQL Workbench 6.3.4.0.