Why do I have multiple (unassociated) temporal history tables?

You need to provide the name of the history table in order to maintain data continuity when turning system versioning off and on.  This behavior is mentioned in the documentation for ALTER TABLE:

  If you don't use the HISTORY_TABLE argument, the system generates a new history table matching the schema of the current table, creates a link between the two tables, and enables the system to record the history of each record in the current table in the history table.

Here's a demo.  I'll create the example table from the documentation:

CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON);

This results in a history table named MSSQL_TemporalHistoryFor_1253579504.  Now I'll disable and enable system versioning:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON);

And I'm in your exact situation:

Now I'll clean everything up:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Employee;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504_D0055BB4;

Then create the table with a specific history table name:

 CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Then turn system versioning off and on, but continue specifying the history table name:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Note: in your specific situation, you should be able to use this syntax to "reattach" one lost history table to your base table

No extra tables:

The takeaway

Always specify a history table name explicitly when creating temporal tables or enabling system versioning.

The MS docs now call this out specifically on the Stopping System-Versioning on a System-Versioned Temporal Table page:

  When turning system versioning back on, do not forget to specify the HISTORY_TABLE argument. Failing to do so will result in a new history table being created and associated with the current table. The original history table will still exist as a normal table, but won't be associated with the current table.


Update only the oldest entry in a table with foreign key criteria

Yes, your query will update all rows in posts. You have to include a condition that restricts the update to one row. One way to do this:

UPDATE Posts
SET Content = @Content
WHERE Id =
    (SELECT TOP 1 Id
     FROM Posts
     WHERE FkThreadId = @Id
     ORDER BY CreationTime ASC) ;

In SQL-Server, you can't update two tables in one statement, so you have to use two statements. You can put them inside a transaction though, so they are treated as an atom, they either both succeed or fail.


Are 'per second' Performance Counters measured over a certain period of time?

It is like it says, per second. Great explanation of what constitutes a 'batch' here: https://www.brentozar.com/archive/2017/02/what-is-batch-requests-per-second/


Need help in converting this cursor to a set based operation

Why do you need to DROP and RECREATE the table each time?  Since you're doing a DROP-CREATE, it must already exist. So, outside of the SP, recreate it as follows:

CREATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP (
  SEQ_NUM SMALLINT,
  CASE_ID DECIMAL,
  ASSIGNED_TO NCHAR(30),
  AUDIT_STAMP DATETIME,
  AUDIT_OPRID NCHAR(60),
  RC_STATUS NCHAR(10),
  PROVIDER_GRP_ID NCHAR(20),
  PRIMARY KEY(CASE_ID, SEQ_NUM)
); -- don't leave an 8 million record table in a heap!

Since all you need is merely to number the records within a CASE from 1 to N, you can use ROW_NUMBER().

ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP]
AS
  SET NOCOUNT ON;
  TRUNCATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP;

  INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP
  SELECT ROW_NUMBER() OVER (PARTITION BY CASE_ID ORDER BY AUDIT_STAMP),
         CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID
    FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2;
GO


Two usual methods: aggregate and ranking function.

The aggregate works on SQL Server 2000. Both ways can use a CTE or derived table

For performance, I've found the aggregate works better. However, it looks like SQL Server 2008 ranking functions run far better than on SQL Server 2005. I'm not using SQL Server 2008 day to day yet (large dinsoaur corporate) so can't comment.

There are 2 relevant SO Questions but I can't find them currently. One is a questions about high logical IO with ranking functions, another is testing of ranking in comments over SQL 2k5 vs 2k8. Sorry.

--aggregate + CTE
;WITH cOldest AS
(
    SELECT name, MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
)
SELECT
    T.*
FROM
    @Test T
    JOIN
    cOldest C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
    T.ID

--aggregate + derived table
SELECT
    T.*
FROM
    @Test T
    JOIN
    (
    SELECT name, MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
    ) C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
    T.ID

--ranking + CTE
;WITH cOldest AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test
)
SELECT
    C.*
FROM
    cOldest C
WHERE
    C.rnDOB = 1
ORDER BY
    C.ID

--ranking + derived table
SELECT
    C.*
FROM
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test) C
WHERE
    C.rnDOB = 1
ORDER BY
    C.ID


The InnoDB buffer pool is essentially a huge cache. (A variant of LRU - 'Least Recently Used'). If your working set data fits into that cache, then SELECT queries will usually be relatively fast. On the other hand, if the working set data doesn't fit into that cache, then MySQL will have to retrieve some of the data from disk (or whichever storage medium is used), and this is significantly slower.

Running mysqldump can bring huge amounts of otherwise unused data into the buffer pool, and at the same time the (potentially useful) data that is already there will be evicted and flushed to disk.

There are ways to avoid or minimise this problem:

You can manipulate system variables like innodb_old_blocks_time (increase this - 1000 = 1 second) and innodb_old_blocks_pct (default is 37 - allowed range is from 5 to 95, set a smaller value to evict data from mysqldump and similar faster). Both these variables are dynamic, so they can be given special values just before you run mysqldump, and then restored to the original values once it has completed. For details, see Making the Buffer Pool Scan Resistant.
With MySQL 5.6+ (or MariaDB 10.0+) it's also possible to run a special command to dump the buffer pool contents to disk, and to load the contents back from disk into the buffer pool again later. (See MySQL Dumping and Reloading the InnoDB Buffer Pool | mysqlserverteam.com.) This way you can still use mysqldump or other tools that "pollute" the buffer pool and then restore it afterwards.
A way to prevent that running backup is unintentionally evicting your working set data at all would be to replace your mysqldump backup method with Percona Xtrabackup or another physical backup tool that doesn't access the InnoDB buffer pool as such. Physical backup methods are also faster, and can be less disruptive than mysqldump. The disadvantage is that you'll need the exact same MySQL version and configuration on the system where the backup is restored.


You have to define what you mean with "similar workgroups" and alter the relevant line but this is what I understand from your wording.

It will show all combinations of users from different workgroups (and similar in an arbitrary way, matching the first 3 characters of group name) that do not appear (in either order) in the cluserrx table:

; WITH UserCombinations AS
    ( SELECT r.logonid AS logonid,
             u.workgroup AS User_workgroup,
             p.logonid AS rxuser
             p.workgroup AS Provider_workgroup
      FROM cluser AS u
        JOIN cluser AS p
          ON  u.workgroup < p.workgroup                     -- not identical workgroups
          AND LEFT(u.workgroup, 3) = LEFT(p.workgroup, 3)   -- but similar
    )
SELECT
    uc.logonid,
    uc.User_workgroup,
    uc.rxuser
    uc.Provider_workgroup
FROM UserCombinations AS uc
WHERE NOT EXISTS
      ( SELECT *
        FROM cluserrx AS rx
        WHERE rx.logonid = uc.logonid  AND  rx.rxuser = uc.rxuser
           OR rx.logonid = uc.rxuser  AND  rx.rxuser = uc.logonid
      ) ;


How to fetch the records which is belongs to only one group in postgresql

You could use count(distinct collage_id) grouping by stud_id in this way:

select   stud_id
from     tbl
group by stud_id
having   count(distinct collage_id) = 1;

| stud_id |
| ------: |
|       6 |
|       7 |

db<>fiddle here

If you want to fetch rows filtering by collage_id:

select *
from   tbl
where  collage_id = 3
and    stud_id in (select   stud_id
                   from     tbl
                   group by stud_id
                   having   count(distinct collage_id) = 1);

Note I've modified a bit your sample data.

_id | stud_id | collage_id | university_id | active
--: | ------: | ---------: | ------------: | :-----
  8 |       7 |          3 |             2 | t

db<>fiddle here


Best Practices - Copying Table with datetime stamped on name

The message warns you, "after rename the table, any dependent objects like views, triggers, SP or function will fail to execute. Which is normal.

If you want to avoid this message, you can use following script to backup table in same database.

DECLARE @TableToBackup VARCHAR(128) = 'Tbl_MyTable'
DECLARE @BackupTableName VARCHAR(512)  = @TableToBackup+convert(nvarchar(MAX), getdate(), 13)

EXEC  ('SELECT * INTO ['+@BackupTableName+'] FROM '+@TableToBackup+';')

Thanks!


Oracle autotrace - pstart and pstop always the same

Sure it should, given that you wrote proper SQL. But you used a derived value in the filter conditions instead of the actual column.
Actual condition in SQL should be something like this (I also changed the date format string and the values to make some sense):
where event_time between BETWEEN TO_DATE('06-JUL-2020', 'dd-MON-yyyy')
AND TO_DATE('07-JUL-2020', 'dd-MON-yyyy')


Postgresql: How to index jsonb array of objects column

The following code will be able to use a GIN index on the jsonb column:

SELECT COUNT(*) FROM results
WHERE subjects @> '[{"subject": "B/MATH", "grade": "A"}]'

The difference to your example is that I don't unpack the array, I just query the jsonb column directly. The GIN index can be used for the @> operator on jsonb columns, but it can't be used for arbitrary functions. You're using the @> operator in the end, but not on the actual jsonb column but on your extracted json.

Indexing a LIKE condition with a wildcard at the beginning is far, far more difficult. You would need a trigram index for this, but I've no idea how to use one for data inside an array in a jsonb column, if that is even possible. You should strongly consider storing this information in a more formal way that avoids having to use LIKE at all.

A LIKE that doesn't have a wildcard at the beginning like 'MATH%' can use a btree index under some conditions (see https://www.postgresql.org/docs/9.5/static/indexes-types.html for some details, you have to pay attention to the locale for this).

If this weren't an array, but a plain object inside the jsonb column, you could use a functional index like e.g.

CREATE INDEX ON results((subject->>'grade'));

This is maybe also possible with an array inside jsonb, but I can't think of a reasonable way to do this right now.

Your current schema makes everything an order of magnitude more difficult to write and makes it very hard to impossible to properly take advantage of indexes. If you have the option, consider storing this data in a table with a subject and a grade column, this would make this problem far easier.


Has some command that lists the privileges of a user in db2?

You can use the system catalog views.

Generally speaking any view SYSCAT.xxxxxAUTH will show the authorizations for that particular area.

Examples include SYSCAT.DBAUTH is authorizations at the database level. SYSCAT.ROLEAUTH is authorizations to roles. SYSCAT.INDEXAUTH is authorizations for indexes, etc.


Prefix matching while importing large CSV file

DRY -- a database principle -- "Don't Repeat Yourself".

Instead of copy the data over as you load it, look it up when you need it.

SELECT c.contact, tz.state, tz.timezone
    FROM contacts AS c
    JOIN timezones AS tz  ON tx.prefix = LEFT(c.contact, 3)

Caveat:  This assumes that you won't modify the timezone after assigning it to a contact.

What is the application?  Perhaps some form of call center?


Could someone recommend a db management tool that supports SQLite, MySQL, PostgreSQL, and is web-based?

If a remote desktop is an acceptable web-based solution, you could have Navicat Premium on a remote server that way you don't need the tool locally (premium is the cross-databases edition of the tool: it has MySQL, PostgreSQL, SQL Lite and even SQL Server) .

But you would find cheaper/free if you simply install a tool per database type (e.g. phpMyAdmin, Chive, phpPgAdmin, SQLiteManager).


Licensing SQL Server 2012 Standard Edition for development and test severs

There is certainly an economical way to do this - get an MSDN subscription. The licenses that you get allow you to use the software specifically in development and test environments. It looks like currently $1,199 would cover your needs for SQL Server (and assuming you can make use of some of the other software too), but you could always invest more to get additional software:

https://msdn.microsoft.com/en-US/subscriptions/buy/buy.aspx

Also, we've been asking for the ability to tell Developer Edition what our target edition is for ages. Please vote and comment on these items, explaining your use case and what you have to do to get around the problem:

http://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version

http://connect.microsoft.com/SQLServer/feedback/details/664953/target-platforms-by-allowing-disabling-features-in-developer-edition

http://connect.microsoft.com/SQLServer/feedback/details/507277/allow-developer-edition-to-emulate-feature-set-of-different-editions

http://connect.microsoft.com/SQLServer/feedback/details/420180/implement-a-configuration-option-that-allows-a-sql-server-developer-edition-instance-to-behave-as-a-standard-edition-instance

http://connect.microsoft.com/SQLServer/feedback/details/331297/developer-edition-in-standard-workgroup-mode

http://connect.microsoft.com/SQLServer/feedback/details/125239/edition-compatability-level-in-developer-edition

The more comments, the better!

This feature should also make it into SSDT at some point. But they're only going to do it if there is enough customer noise about it. :-)


SQL Comments disappear with psql and `\e`

Workaround with C-style comments

For whatever reason.. this seems to work when you use C-style comments instead of standard SQL comments,

CREATE TABLE foo
AS
  SELECT x AS id,
    /* x AS id2, */
    x AS id3
  FROM generate_series(1,50) AS x;

Now you can do \e and edit it.


It looks to me as if you are trying to do an SELECT and an UPDATE in the same statement and onto the same table.

The SELECT is holding a shared lock on the values inside the IX_system_Queue_DirectionByStatus index, and the UPDATE needs for those locks to be released before it can get it's exclusive lock which will update the primary key (which I will guess is clustered and also part of the IX_system_Queue_DirectionByStatus key value).

Anyway, my guess is this query would only succeed on the rare chance that the index values it is selecting and updating are not conflicting. Is it deadlocking each time you execute (I assume that it would be).

Here is a link that explains deadlocks in more detail: http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx


Applying Multiple Window Functions in 1 SELECT using different partition

That should be fine.

Your first column (LAG(salary)) will be calculated over the partition you specified, which the second column will be calculated over the second partition.

Note that each partition specifies its own ORDER BY clause - so your windowed functions are calculated based on your row's position within the partition and its own order by clause.

I can't see any issues with this.

When you run it, do you get the results you expect?


Follow the steps in Connect to SQL Server When System Administrators Are Locked Out:

start the server in maintenance mode with -m
connect as local administrator
add necessary accounts
restart the server in normal mode

Is all described, with detail step-by-step, in the linked article.


I think I found the answer. The following code works.

select @realtime,
@realtime := date_add(@realtime, interval realminute minute) as j, t.*
from test as t, (select @realtime := "2018-12-12 03:03:03") a
order by id asc;


Normalization using DENSE_RANK and LAST_INSERT_ID

You don't need to stand on your head to get the id:
Step 1: Populate Orders in a single statement:
INSERT INTO Orders (scanId, customerId)   -- Leave out; `id` it will be set
    SELECT DISTINCT scanId, customerId FROM temp;

Step 2: Populate the other table:
INSERT INTO OrderDetails (orderId, productId)
    SELECT Orders.id AS orderId,
           temp.productId
        FROM Orders
        JOIN temp USING (scanId, customerId);

It would be helpful to have
temp:  INDEX(scanId, customerId)


What is the best data type to store prices upto 8 decimals + indicators in SQLite?

The FAQ pretty much explains the reason why you don't want to use their REAL data type for storing numbers with 8 decimal places.

  (16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

https://www.sqlite.org/faq.html#q16

Workaround 1

The trick of using an INTEGER to achieve your required level of precision has been used since computers existed.

With today's powerful CPUs, I wouldn't worry about the overhead of any additional calculations.  You'll need to run benchmarks to (dis)prove my statement.

Workaround 2

IMHO - You shouldn't use a tool that doesn't support your requirements.


what is the best practice for tempdb autogrowth?

For me, the best practice is to just size the files large enough, at the outset, to accommodate the growths you expect.

Let's use some simple numbers. Say you have a 100 GB drive that you've allocated to tempdb (or 100 GB of some shared drive). Your instance has 4 tempdb files, and you've decided to set them initially at 100 MB and to grow 10 MB at a time. Initially, that 100 GB drive or slice looks like this (not to scale, obviously):

As your system uses tempdb resources, each tempdb file will grow, 10 MB by agonizing 10 MB. After a few growths:

Note that each growth event, while likely fast-ish, forces all connections to wait. This is more pronounced when Instant File Initialization is not enabled, and if the files are configured to grow together (which requires a trace flag in some versions, but this is the right thing to do, because you don't want any single file to be bigger than the others).

When you get to the end of the drive, all 4 files will fail to grow at the same time. And this is a business continuity event. When tempdb can't grow here, activity halts:

Now, if you're getting to this point, ever, you need to acknowledge that either you need to change the way you use tempdb, or 100 GB isn't enough and you need to allocate more space. But I would argue that it's better to simply start here and configure this size for your tempdb initial data file sizes:

Well, technically, I would size them at, say, 20 GB each, with a 500 MB autogrowth. This allows you to still be warned well in advance of filling up the 100GB slice, but without paying for all of those incremental file growths in the meantime.

After all, if tempdb is going to grow to fill that space, it's not like you can use it for anything else.

  which size should i give the autogrowth

There is no magic number (the defaults have been traditionally way too conservative for practical use, but this is getting better). Your autogrowth setting will be based on the performance characteristics of your disk, and how often you expect to grow. If you have SSD or similar, and have IFI enabled, size is probably much more tied to how much warning you want to have before hitting the end of the slice. If you have spinny, mechanical drives, and/or don't have IFI enabled, you will probably want to test various settings and see where the time it takes to grow the file by different amounts meets your tolerance for all transactions on the system to wait.


If you are using ubuntu, give a try by using the below steps:

    run the command nano /etc/mysql/my.cnf
    comment bind-address = 127.0.0.1 using the # symbol
    restart your mysql server once.


how to rebuild / reinstall ssrs (reportserver, reportservertempdb) databases?

I’m not 100% sure but I think there is no supported way to rebuild these.

If you have backups of these databases then just restore them. If not you can try creating another empty database and then try to synchronize structure and data and see if that can help.

I’d suggest you use some third party tools for this.


Insert multiple times XML data (SQL SERVER)

One way is to insert all at once

INSERT INTO #tmpObject (tmpTagId,tmpObjectTypeId , tmpObjectId , tmpMode )
SELECT
    o.value ,
     ParamValues.item.value('objectTypeId[1]','INT'),
     ParamValues.item.value('objectId[1]','INT'),
     ParamValues.item.value('mode[1]','VARCHAR(16)')
FROM dbo.f_Split(@TagIdList,',')(@TagIdList,',') as o
     cross apply @ObjectList.nodes('objectList/object') AS ParamValues(item)

select * from #tmpObject order by tmpTagId

output:

tmpTagId    tmpObjectTypeId tmpObjectId tmpMode
----------- --------------- ----------- ----------------
1           1               654321      ADD
1           1               13456       ADD
2           1               13456       ADD
2           1               654321      ADD
3           1               654321      ADD
3           1               13456       ADD
4           1               13456       ADD
4           1               654321      ADD


Managing SQL Server 2008 R2 transaction logs when using principal/mirror databases

Your principal database is in full recovery mode which means you cannot (by rule) shrink your transaction log until you take a transaction log backup.

Here is some information from Microsoft on the subject:

http://technet.microsoft.com/en-us/library/ms191429.aspx

http://technet.microsoft.com/en-us/library/ms179478.aspx

In SSMS, highlight your database in the Object Explorer. Then right click it, choose Tasks then Back up... Next, choose your database. Change your backup type to Transaction Log. Back up to disk somewhere that has plenty of storage (at least as much as your transaction log).

At 80GB, your transaction log backup will take a long time. Once that's done you will be able to shrink your transaction log.

I would recommend running a transaction log backup regularly in order to keep your transaction log from growing out of control.

To answer your question, no, you cannot delete a transaction log (while the database is online) and if you do delete it, you risk losing any data that has not been flushed.

ALSO, Microsoft says you cannot take a transaction log backup until you run a full backup. If you have never run a full backup on the database in question, run that first before the transaction log backup.


How many users are supported on SQL Server 2012 Express?

The limitation is for database not for instance. Plus 10G limitation is ONLY for data file, the log file can be 10.5 G or 20G for example.
As per this BOL document it says that

  The actual number of user connections allowed also depends on the
  version of SQL Server that you are using, and also the limits of your
  application or applications and hardware. SQL Server allows a maximum
  of 32,767 user connections. Because user connections is a dynamic
  (self-configuring) option, SQL Server adjusts the maximum number of
  user connections automatically as needed, up to the maximum value
  allowable. For example, if only 10 users are logged in, 10 user
  connection objects are allocated. In most cases, you do not have to
  change the value for this option. The default is 0, which means that
  unlimited user connections are allowed

There is no LIMIT set as such for express edition. However when connecting from application connection pooling comes into picture, if enabled. In connection pooling, after a connection is created, it is placed in the pool and it is used again so that a new connection does not have to be established.

Yes number of connections are limited by the OS you are using. Please refer to this thread which says only 20 connections are allowed on windows 7. I am not sure about other client OS but if you use server OS there is no such restriction.
Unlimited for practical purpose. There is no restriction as such if your OS is Windows server 2012.
32767 databases is what maximum capacity specification states. But you wont be able to go far than 10 due to express limitation on memory and CPU


Undefined symbol when migrating to Postgres 11

# ld -v /usr/pgsql-11/lib/postgis-2.4.so
GNU ld version 2.27-28.base.el7_5.1
ld: warning: cannot find entry symbol _start; not setting start address
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `repalloc'
/usr/pgsql-11/lib/postgis-2.4.so: undefined reference to `hash_create'

That's not actually a problem. You can't use ld like that, I think you want to use ldd there. It's a shared object, not a executable. Without a _start (or an entry point with -e) you can't call ld and produce an executable

  could not load library "$libdir/postgis-2.4": ERROR: could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /usr/pgsql-11/lib/postgis-2.4.so: undefined symbol: geod_polygon_init

That error is because I believe you're linking against a version of Proj4 that is too old. That's a distro problem if anything.


May be you need much more simple table?

----------------------------------------------------
|  Server  | Date       |                          |
---------------------------------------------------|
| Server 1 | 2014-04-01 | input calc | output calc |
| Server 2 | 2014-04-01 | input calc | output calc |
| Server 3 | 2014-04-01 | input calc | output calc |
| Server 4 | 2014-04-01 | input calc | output calc |
| Server 5 | 2014-04-01 | input calc | output calc |
| Server 1 | 2014-04-02 | input calc | output calc |
| Server 2 | 2014-04-02 | input calc | output calc |
| Server 3 | 2014-04-02 | input calc | output calc |
| Server 4 | 2014-04-02 | input calc | output calc |
| Server 5 | 2014-04-02 | input calc | output calc |
| Server 1 | 2014-04-03 | input calc | output calc |
| Server 2 | 2014-04-03 | input calc | output calc |
| Server 3 | 2014-04-03 | input calc | output calc |
| Server 4 | 2014-04-03 | input calc | output calc |
| Server 5 | 2014-04-03 | input calc | output calc |
----------------------------------------------------

That table can be produced by:

SELECT z.server_name,
       w.date,
       SUM(w.input_calc)  AS input_calc,
       SUM(w.output_calc) AS output_calc
   FROM log_apache AS w
   JOIN servers    AS z ON z.id = w.server.id           -- server_id / server_name table
   WHERE w.date BETWEEN '2014-04-01' AND '2014-04-05'   -- for example only
     AND w.server_id IN (1,2,3,4,5)                     -- for example only
   GROUP BY w.server.id, w.date
   ORDER BY w.server.id, w.date;

Some suggestions about efficiency:

Replace server char(30) NOT NULL with server_id INT NOT NULL. Grouping by INT way more faster than by CHAR() as far as no collation conversions needed.
Create separate index (date, server_id) for GROUP BY.
Precalculate count*input on insertion to spread the load.


Composite primary key plus a separate (surrogate) id column for foreign key references

I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.

You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.


MySQL subquery slows down drastically, but they work fine independently

In Query 3, you are basically executing a subquery for every row of mybigtable against itself.

To avoid this, you need to make two major changes:

MAJOR CHANGE #1 : Refactor the Query

Here is your original query

Select count(*) as total from mybigtable
where account_id=123 and email IN
(select distinct email from mybigtable where account_id=345)

You could try

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A;

or maybe the count per email

select email,count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    INNER JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
) A group by email;

MAJOR CHANGE #2 : Proper Indexing

I think you have this already since Query 1 and Query 2 run fast. Make sure you have a compound index on (account_id,email). Do SHOW CREATE TABLE mybigtable\G and make sure you have one. If you don't have it or if you are not sure, then create the index anyway:

ALTER TABLE mybigtable ADD INDEX account_id_email_ndx (account_id,email);

UPDATE 2012-03-07 13:26 EST

If you want to do a NOT IN(), change the INNER JOIN to a LEFT JOIN and check for the right side being NULL, like this:

select count(*) EmailCount from
(
    select tbl123.email from
    (select email from mybigtable where account_id=123) tbl123
    LEFT JOIN
    (select distinct email from mybigtable where account_id=345) tbl345
    using (email)
    WHERE tbl345.email IS NULL
) A;

UPDATE 2012-03-07 14:13 EST

Please read these two links on doing JOINs

http://en.wikipedia.org/wiki/Sql_join
http://dev.mysql.com/doc/refman/5.5/en/join.html

Here is a great YouTube Video where I learned to refactor queries and the book it was based on

http://youtu.be/ZVisY-fEoMw
http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/B006TR0ZEO


SQL Server database size didn't decrease after deleting large number of rows.

While shrinking is dangerous indeed for the reasons mentioned here. There is a happy medium between Jimbo's answer and John's answer... You should always seriously consider whether or not you want to shrink your database.

In an ideal world - you'd create your DB with plenty of free space to grow into. I call this "Right Sizing" your database. You would allow this free space to be there and not strive to give it back and keep your total size right at your used size.. Why? Because your database will eventually grow again.. Then you'll shrink again.. And you'll be stuck in this horrible pattern of useless shrinks followed by growths - and the entire time, as a few have pointed out, you'll be increasing your index fragmentation.

I've blogged about this where I admonished folks to "Don't touch that shrink button!" but sometimes... Sometimes you need to. If you have a large database, just freed significant space and don't expect to grow back into it ever - well then it is okay to consider shrinking as a one time operation as long as you can take care of your index fragmentation afterwards through rebuilding them. The shrink operation can be time consuming so you'd want to plan it for a time where you can pay that price of a shrink running. The approach of creating an empty DB and copying data into it works - but that can become very difficult with larger databases and a lot of data.

If you plan on adding that space back to the DB through normal usage and growth patterns into the future, then you may just want to leave the space there.

Also
You said you "cleared" your transaction log. I'd be curious to know how you did this but as you read the post I shared and the others in the series you'll see some tips on transaction log management. But in short - if you are in Full Recovery mode you should be taking regular log backups to keep the log reusing itself. Otherwise - with no log backups while in Full Mode - the log file keeps growing and growing and growing and always saves what you've done because you told SQL you don't just want to maintain that log for crash recovery but want to keep a manual backup of it to replay transactions/undo transactions to recover to a specific point in time for recovery purposes... If you are in simple and seeing the log grow excessively, this can be a sign (typically) that you are doing a LOT of work in one transaction (whether you said BEGIN TRAN ... do work.... COMMIT TRAN or whether you just issued one big DELETE statement and deleted a whole mess of data in one implicit transaction.)

I am also assuming that you are looking for this free space on your file system. If you are looking for it within SQL and within that large file you have - it could be that you are waiting on ghost cleanup to complete if looking immediately after your operation. Paul Randal blogs about Ghost Cleanup.


SQL Server 2014 SQL Job CmdExec and access denied

When you configure a Proxy account, you have to check "CmdExec" subsystem in order to use CMDExec with this Proxy Account.


What is an effective way of labeling columns in a database?

In your case, the prefix user is redundant. We (the devs in charge) know that this is the table user, so why add user_ prefix in front of every field?

What I would suggest to you is to do it with a more natural approach.

What are the characteristics of a Person: Last Name, First Name, Birthdate, Nationality, etc...

What are the characteristics of a Car: Model, Year, Color, Energy, etc...

Your column should be named as natural as possible, it would make the schema more clear for everybody, for you and the ones coming after you. This is also called the Maintenance phase, and anything you can do to make the maintenance easier is usually worth the effort.


how to find vertica community edition and how to export / copy data

HP Vertica has their community edition for download at https://my.vertica.com/download-community-edition/. If you don't have one already, you will be prompted to create a MyVertica login. If you have problems creating a login, please email me at dsandahl@vertica.com. If you have further issues loading your data, you can tweet at @VerticaHelp and support will follow up with you.


Is there a way to set different destination order with mysql-router?

using different specific host order, rather than using metadata-cache in mysql-router config "destination" option on each mysql-router seems to do it.
ie.
destination=server1,server2,server3


PostgreSQL, how to query the database to retrieve the percentile elements of a dataset?

You can utilize NTILE to create n quantiles:

select id, min(value)
from
 (
   select id, value,
      ntile(10) over (order by value) as nt
   from tab
 ) as dt
group by id, nt


I have two databases (Mongodb 3.6.5) using ReplicaSet, when I made a split in the network, I am forcing the secondary database to be the primary.

Forced reconfiguration should only be used as an administrative last resort. This is a risky approach with only two replica set members as you can easily create data challenges that will require significant manual recovery effort.

If you have a network partition and inadvertently reconfigure both sides to have a primary, you will have to deal with rollbacks and manual reconcilation of conflicting writes. The worst case scenario would be having enough independent writes for the oplogs to no longer have a common point: you would have to choose one member to use as the primary and then resync the other.

  Now I unplugged the network again and did several operations on the secondary machine transformed into primary until the size of the Oplog exceeds the maxSize. After some time without doing operations, the size of the Oplog decreased somewhat. I did some more operations to increase the size value of Oplog but the same is not growing as before.

The replication oplog is a system capped collection whose implementation varies somewhat based on the underlying MongoDB storage engine and version of MongoDB server. Conceptually a capped collection is maintained in insertion order with a maximum size in bytes or documents. You can think of this as similar to a circular buffer: once the capped collection reaches its maximum size, the storage engine removes or overwrites the oldest documents based on insertion order.

The capped collection implementation for the oplog has some additional nuances (as compared to a standard capped collection) to improve performance for the replication use case. WiredTiger (the default storage engine in MongoDB 3.2+) does not preallocate the full space for the oplog so this collection will grow until it approaches the maximum oplog size. WiredTiger has a background thread that periodically prunes a batch of the oldest entries from the oplog.rs collection, so the size of this collection on disk is expected to fluctuate depending on overall write activity.


SQL Server 2017 Enterprise vs Standard Edition?

Asking one question here asking for ALL of the differences is a lot like me asking you, "Well, which features of SQL Server do you use?" It's such a tough question to answer because it's so huge (which is also why this question is likely to get closed, heh.)

You'll be better off asking individual specific questions about each of the feature/edition restrictions that you don't understand.

For example, if you want to know what "Advanced Adaptive Query Processing" is, read the documentation pages on Adaptive Query Processing, and then ask questions specific to that feature.

In my experience, the features that usually cause folks to jump to Enterprise Edition are:

Leveraging more than 24 CPU cores
Leveraging more than 128GB memory
Transparent Database Encryption (TDE) - if you're storing personally identifiable data that could put you on the front page of the paper if someone got a snapshot of your database, then the Enterprise Edition costs just kinda are what they are


Same restore file (.bak), huge difference of execution duration time on various computers

Is Instant File Initialisation on on your colleagues computer?

Basically, this hugely decreased the time to initialise the MDF file. Without it, the entire file needs written with zeros. The log (LDF) file is always zero-initialized.


Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file

The easiest way to do this (read: least amount of additional code you need to write) is to use Invoke-DbaQuery from the dbatools PowerShell module.

Remove the foreach loop - this function will handle it for you. Pass the list of instances into Invoke-DbaQuery and include the -AppendServerInstance switch, and all the instances will be queried and the name of each included in the output - just like when you do a multi-instance query in SSMS. You'll get one object back which is a collection of the results of the query against each instance.

$InstanceList = get-content "C:\Users\<NAME>\Documents\InstanceList.txt";
Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -QueryTimeout 99999 -AppendServerInstance

Since you don't mention script performance/elapsed time being an issue, I intentionally did not mention running multiple queries in parallel and then merging the results.


Substract two database entries based on value of a certain field

As RDFozz has pointed out in his comments, this solution only works if:

There is one OPEN and one CLOSE entries
Both entries has the same type, 'LONG' or 'SHORT'

select case when t1.datetime > t2.datetime
            then t1.datetime
            else t2.datetime end as datetime,
       t1.orderid,
       t1.type,
       case when t1.type = 'SHORT'
            then t1.price - t2.price
            else t2.price - t1.price end as diff
from   tbl t1
join   tbl t2
on     t1.orderid = t2.orderid
and    t1.type = t2.type
and    t2.action = 'CLOSE'
where  t1.action = 'OPEN'

datetime   | orderid | type | diff
:--------- | :------ | :--- | ---:
2018-01-03 | a34hsbf | LONG | 0.24

dbfiddle here


If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower?

It depends.

No - if you run exactly the same queries as before (no access to the new data at all).

SQL Server's partitioning implementation creates a separate rowset for each partition, so when you create a partitioned index, it creates a separate b-tree structure* for each partition (note that partitioned heaps also exist).

Simply adding more partitions therefore changes nothing from the point of view of the existing partitions - the indexes are exactly the same. Your queries access only a single partition, so nothing changes.

Maybe - if you query the new data at any stage. Bringing the new data into memory may displace data needed by the original queries, depending on the amount of memory you have. If the change results in new physical I/O, you will see an impact on performance, with the severity depending on the capability of the storage subsystem.

* This is mentioned in many places in the product documentation, for example:

Clustered Index Structures
Nonclustered Index Structures
Heap Structures

From those links:

  When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition.
  When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition.
  When a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition.

You can also explore this for yourself by looking at the system catalog views like sys.partitions, which shows the hobt_id (heap or b-tree id) of the structure that contains the rows for a particular partition.


There is more than one school of thought concerning the naming of columns or fields.

One school of thought says that the name should express the intent of the data being conveyed even if that name is separated from the context where it is found.  Thus, a name like post_date or another name like comment_date tells the reader not only that this is a date, but that it's the date of a post or a comment.

One advantage of this convention is that it makes it easier to maintain a data dictionary, where the full definition of each data element is to be found.  It's easy to navigate from a column name to the corresponding data dictionary.

This convention was apparently used in Wordpress.

Another school of thought says that the name of a column or field ought to convey the intent of the column or field in the table or record where it is found,  according to the role it plays in that context.  Thus,  date merely tells you that it's a date,  but post.date presumably tells you that it's the date of a post.  And comment.date would tell you that it's the date of a comment.

There are advantages and drawbacks to both schools of thought.  The most important thing is to be consistent, once a naming standard has been agreed to.  A naming convention that is sometimes disregarded creates pitfalls for the maintainer.

One drawback to the second convention is that FKs can generally never have the same name as the PKs to which they refer.  Another drawback is that, if there is a context, such as the forms manager,  in which "date" is a reserved word,  now you're forced to violate your own convention.

One drawback to the first convention is that it makes it awkward to express a reflexive relationship, one in which an FK refers to a different row in the same table.  Take, for example, "supervisor_id", a column you find all the time in employee tables.   You can't give it the same name as the PK that it refers to, because then you would have two columns with the same name in the same table, and that's forbidden.  So now you're forced to violate your convention.


PostgreSQL complaining about shared memory, but shared memory seems to be OK

Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks you can hold within a transaction (to prevent any one transaction from using all of shared memory).

You could either increase that limit a bit (I would recommend against setting it arbitrarily large or you will run in to a separate situation of actually running out of total shared memory) or do your drops and creates either in batches of transactions or as one drop/create per transaction.

Edit: Apparently I was wrong about how max_locks_per_transaction works. From the documentation, the total number of locks available is max_locks_per_transaction * (max_connections + max_prepared_transactions) - any one transaction may hold more than max_locks_per_transaction, as long as the number of locks held everywhere is less than this total value.


You can run EXPLAIN ANALYZE inside a transaction and then roll back the transaction.  This will write to the table and indexes, but the writes will not become visible to other users, other then through locks and side-channels (e.g. table and index bloat).

If you already have a script which runs the updates you want, adding BEGIN and ROLLBACK (or changing COMMIT to ROLLBACK if your script already does explicit transactions) should be incredibly trivial; so it is not clear what you are asking.

But you should clone your database to a test environment, and then run your tests in the test environment in a realistic manner.  Running aborted updates in a tight loop might not have the same performance as running committed updates in a tight loop.


Availability Group Listener - Dedicated Server?

The listener is part of the Availability Group and will reside in the resource group of the Availability Group in the Windows Server Failover Cluster. Thus it is not possible to have a specific listener server as the listener will move with the owner of the resources in WSFC.


SQL Server 2005 Index Rebuild/Reorganize

There are two different types of fragmentation to worry about:  physical and logical.  Physical fragmentation means the file that stores your indexes is fragmented at a file system level.  Logical fragmentation means that page splits have occurred while inserted or updating data in an index.  Incidentally, shrinking your database files will eventually cause physical fragmentation if (when) your database files are forced to grow to accommodate more data and the space needed for the grow is not contiguous with the existing data file.  Also, reorganizing and rebuilding your indexes, unless sorting is performed in tempdb, will, more than likely, cause grows on the file containing your indexes.  This could further compound the physical fragmentation issue.  This is why shrinking your datafiles is almost never a good idea; they grew for a reason, they will simply grow again, and when they do they will cause physical fragmentation.

Unfortunately, I cannot speak to your first question.  I assume that logical index fragmentation will not cause problems at an IO level.  However, physical fragmentation will manifest itself as an increased number of random reads, due to the file system fragmentation.

As for your second question, index maintenance should not cause any issues.  As a matter of fact, you should already be performing index maintenance as a part of your routine maintenance.  An important thing to note is that index rebuilds implicitly update your index statistics with a full scan.  However, index reorganizations do not.  As a part of my index maintenance, I explicitly perform a full scan statistics update against indexes I reorganize, instead of rebuild.

Finally, I doubt your issue is caused by indexes.  It sounds like you are loading a lot of data into either a staging table or a temporary table, prior to running this report.  If you are loading into a staging table, perhaps you could move the staging table to its own filegroup, on another storage volume.  If you are loading into a temp table, you are probably running into tempdb running out of space.  Either way, it appears you either need additional storage or a new filegroup on a different storage medium for your staging table.

Hope this (admittedly too long) answer helps!

Matt


MariaDB: calculate the reads/writes ratio in percent

The Reads% and Writes% does not have the @totals until too late.  This should work better:

SELECT  Total,
       `Total reads`,
       `Total writes`,
       ROUND((@total_reads / @total_com * 100), 2) as `Reads %`,
       ROUND((@total_writes / @total_com * 100), 2) as `Writes %`
    FROM
      ( SELECT  @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete',
                             'Com_insert', 'Com_update', 'Com_replace'),
                             variable_value, 0)) AS `Total`,
                @total_reads := SUM(IF(variable_name = 'Com_select',
                                variable_value, 0)) AS `Total reads`,
                @total_writes := SUM(IF(variable_name IN ('Com_delete',
                                        'Com_insert', 'Com_update', 'Com_replace'
                          ), variable_value, 0)) as `Total writes`
            FROM  information_schema.GLOBAL_STATUS
      ) x;


Well I guess you just deleted the database's data files (.myd) and the database's index files (.myi) from your database server. To top it off you deleted the *.frm file which are also part of your database.

One reference I came across: Normalize Table File Name Extensions
Another reference describes what you did manually: What happens when you drop your database
The full reference can be found here: MySQL Glossary

I do hope you have a backup of your database?


MongoDB Indexing - How many fields should I index?

It seems that your collection is quite small (5MB?). I would create one index for each query and check execution plans.
The limit of creating too many indexes are :

If your indexes are too big and don't fit in RAM (I suppose you have
several GB of RAM ?)
It increases I/O during documents insert/update because each time you write a document, indexes are also updated.

Also, indexes are necessary when sort operation cannot fit in memory. There is a limit of 32MB, so here again you are below this limit.

Please refer to indexing strategies for more details.


How to query results from a huge postgres database?

SELECT COUNT (DISTINCT column_name) FROM table_name;

The part of the PostgreSQL code that implements "COUNT(DISTINCT...)" is quite old and hasn't had much performance work done on it recently.  It can't take advantage of either parallel processing, or hash tables, for example.  You could rewrite with a subquery to possibly take advantage of some newer techniques:

select count(*) from
    (select distinct column_name from table_name where column_name is not null) as foo

But still, this is not as trivial a task as you seem to think it is.  It is a hard task and may take a long time when fed huge amounts of data.

  I am worried that if use joins operations on these tables this wait time will increase significantly and will be more painful.

Don't invent things to worry about.  The performance of a COUNT(DISTINCT...) tells you very little about the performance of a JOIN.  Write the query you want, and then worry about optimizing that query, not some other query that has little to do with the one you want.

It can make sense to simplify a query to see how long component parts of it might take.  But COUNT(DISTINCT...) is unlikely to be such a simplifying component part.


You want to use the HAVING keyword
SELECT TOP 1 TargetPriceRec as mode
FROM PricingRecurring
GROUP BY TargetPriceRec
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC


Database Technology for Large Scale Logging

Considering the volumes you are discussing moving it to the cloud isn't going to be magic fix.

"There is no such thing as a free lunch"

A computer still has to process that volume, and that comes at a cost. If you are asking a 3rd party vendor to provide that theyre not just covering costs, but trying to make a profit too.

Changing to a different db platform can also have a lot of hidden costs. Even though licensing might be "free", there can be additional costs in maintenance, integration and compatibility. Even learning a new system is an overhead.

The spec you mention is not a big machine by server standards. I would be confident saying thats undersized for the volumes you describe.

What performance tuning have you done already? What are the bottlenecks? RAM/CPU/IO?

How much faster does it need to be? How fast is fast enough? Do you need a 20% improvement? 50% 100% You might easily get that with more RAM and some SSD's.

If you cant add more resources, is there anyway you can make the server do less work?

Archiving: Could you move old data off to a separate db. Or even better could you aggregate the data and purge the raw records?


an error in your SQL syntax; check the manual that corresponds to your MySQL server version

Your CREATE TABLE will fail because there is no PIN column mentioned in KEY `idxPIN` (`PIN`) line.
MySQL 5.6 does not allow an expression as default value. Use trigger:
CREATE TABLE `Customer` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Version` bigint(20) DEFAULT 0,
  `FirstName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `LastName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UserName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordSalt` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PasswordExpires` datetime DEFAULT NULL,
  `ForcePasswordChange` bit(1) DEFAULT b'0',
  `Email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `NickName` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TextNumber` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `IsValid` bit(1) DEFAULT b'0',
  `IsMigrated` bit(1) DEFAULT b'1',
  `UUID` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `InsertTs` timestamp NOT NULL DEFAULT current_timestamp(),
  `UpdateTs` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`ID`),
  UNIQUE KEY `uk_User_UserName_UNIQUE` (`UserName`),
  UNIQUE KEY `uk_User_UUID_UNIQUE` (`UUID`),
-- -------------
`PIN` INT,
-- -------------
  KEY `idxPIN` (`PIN`)
) ENGINE=InnoDB AUTO_INCREMENT=90184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER set_uuid
BEFORE INSERT
ON Customer
FOR EACH ROW
SET NEW.UUID = COALESCE(NEW.UUID, sha2(uuid(),256));


database design - table with multiple nullable foreign keys

I can't see the entire data model or exactly the context of "Operation" so I can't tell you if there's a better solution, but in general the second approach is preferable, any complexities it creates can be handled by creating a view that creates a simple presentation of the data without requiring someone to even ponder the joins that occur behind the scenes.  Any insert/update/delete procedures only need to be programmed once, the additional complexity will be minimal.
Yes, more tables... BUT:

Those tables will be narrow and small.
You would probably build some sort of filtered index on those columns to facilitate searches, and they would look remarkably identical to the tables you've created.  But in creating them you give more meaning to them than just breaking rules that exist for a good reason in order to save a few lines of code.
If in the future OperationTarget not has more "targets", much easier to add another table instead of more columns to the base table.

I'd also look harder at this to see if this is a good application for exclusive subtypes, as I said I don't have a full picture of your data model so it would be something you'd have to evaluate on your own.


MySQL do not accepts wildcard for tables name level grant

The MySQL 5.7 GRANT Syntax Documentation says something interesting about wildcards
Under the subheading Object Quoting Guidelines, Paragraph 1 says:

Several objects within GRANT statements are subject to quoting, although quoting is optional in many cases: Account, database, table, column, and routine names. For example, if a user_name or host_name value in an account name is legal as an unquoted identifier, you need not quote it. However, quotation marks are necessary to specify a user_name string containing special characters (such as -), or a host_name string containing special characters or wildcard characters such as % (for example, 'test-user'@'%.com'). Quote the user name and host name separately.

Paragraphs 3 and 4 say

The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level (GRANT ... ON db_name.). This means, for example, that to use a _ character as part of a database name, specify it as _ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern (for example, GRANT ... ON foo\_bar. TO ...).
When a database name not is used to grant privileges at the database level, but as a qualifier for granting privileges to some other object such as a table or routine (for example, GRANT ... ON db_name.tbl_name), wildcard characters are treated as normal characters.

What about table privileges ? Please note the Docs on Table Privileges:

Table privileges apply to all columns in a given table. To assign
table-level privileges, use ON db_name.tbl_name syntax:
GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than db_name.tbl_name, the statement
applies to tbl_name in the default database. An error occurs if there
is no default database.
The permissible priv_type values at the table level are ALTER, CREATE
VIEW, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, REFERENCES,
SELECT, SHOW VIEW, TRIGGER, and UPDATE.
Table-level privileges apply to base tables and views. They do not
apply to tables created with CREATE TEMPORARY TABLE, even if the table
names match. For information about TEMPORARY table privileges, see
Section 13.1.18.2, “CREATE TEMPORARY TABLE Statement”.
MySQL stores table privileges in the mysql.tables_priv system table.

As shown from the MySQL 5.7 GRANTS Documentation

Wildcards are applicable to hostname (which are bold italiczied)
Wildcards are applicable to database name (which are bold italiczied)
Nowhere do the docs specify wildcards for table names
Wildcard characters are treated as normal characters for all other database objects

The workaround given in the comment is what I used to do when giving away SELECT on views.


Deny DDL_ADMIN permissions to a role on [dbo] schema

Some of the permissions you're trying to change don't exist in the way you're trying to use them.
For example, to deny CREATE TABLE and most other DDL statements, for a particular schema, the correct syntax and permission would be DENY ALTER ON SCHEMA::dbo TO MyRole.
This should cover the equivalent of the db_ddladmin role's permissions. (I know it's a little less than intuitive, but denying all of ALTER, like in my example above, does include denying the CREATE permissions as well.)


How to fetch the transaction activities/logs for a specific table?

You could try the following:

Retrieve the id for your user table:

SELECT id, name FROM sys.sysobjects WHERE NAME = '<Your_Table_Name>'

e.g. 658101385

Retrieve the Transaction Log information with the undocumented function:

SELECT * FROM ::fn_dblog(NULL,NULL) WHERE [Lock Information] LIKE '%658101385%'

And then carry on from there.

Reference: Identifying Object Name for CREATE and ALTER Using fn_dblog()


Denormalized tables within OLTP database

What you are thinking about is relatively common and pre-processing the data can indeed help the performance of reports and web pages.  The 'cost' to this is that the pre-processing takes time, so your denormalized tables (you can think of this as a  mini-OLAP Data Warehouse) are never fully up-to-date.

I do not consider that to be a big problem, but just one that your users need to understand.  (E.g. - "I just finished the xyz transaction and it is not in my report.")

Any reports that need the latest transaction will need to include at least some elements from the live data, instead of the pre-processed "OLAP" data.

Here is a generic link explaining the relationship. http://datawarehouse4u.info/OLTP-vs-OLAP.html

The web page also explains some nuances of the OLTP/OLAP divide, but there is nothing really surprising in the description.


2013. Lost connection to MySQL server during query error

Check your memory usage or, more probably, your disk usage (is there enough free space during the process?). Altering a table may require either a large amount of memory or a copy on disk of your table. Changing the alter algorithm from INPLACE to COPY can be even faster in your particular case.

You may also be hitting the innodb_online_alter_log_max_size limit, although in that case, only the query should fail, not the entire server. It is possible that the crash may be happening due to the ROLLBACK, and not the operation itself, though.

Finally, some application configurations or hosting servers cancels a query/http request that is taking too long, I recommend you to execute the same query on the command line client for testing purposes.


What's the biggest bottleneck (time) in writing data to a SQL database?

You need to go an read:

The Data Loading Performance Guide
How to analyse SQL Server performance
Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)
bcp.exe
Transferring Data Efficiently by Using Partition Switching
Operations That Can Be Minimally Logged
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
SqlBulkCopy Class
SSIS OleDB destination
Performing Bulk Copy Operations (for OleDB and ODBC examples)

You have to read each link. Really. The short story is that efficient loading must use bulk insert and achieve minimal logging. SSIS is by far the better tool for that, but you can achieve this also programatically. OleDB is the best, but SqlClient (C#) will also do, if needed. Things like row size, network speed and the like are unlikely to be your concern here, but you should always measure (and the articles linked will teach you how). Load performance should be completely orthogonal to existing DB size, if load speed decreases as the DB size increases you must be doing something wrong.

  Finally --- why is it that multiple parallel connections to the database seem to speed up the process to a point? I mean, when I have 20 connections making inserts round-robin style, it's about 6-7x faster than one connection writing ALL the data. I'm curious why this is.

I'll be speculating, due to lack of any investigation and measurement. But most likely you are now blocked by commit flush rate: every time you COMMIT SQL has to stop and wait for the log to be written to disk. If you do not explicitly begin transactions then every statement must stop and wait. Adding more loaders causes causes better log utilization, the log gets committed for 20 writers at once. Read What is an LSN: Log Sequence Number.


Redundant plans in cache because of missing type length

Take a look at Plan cache, adhoc workloads and clearing the single-use plan cache bloat.

To summarize:

If you're running SQL Server 2008 and you have cache being wasted by
single-use plans, be sure to use the new "optimize for adhoc
workloads."
If you still find that you're wasting 100s of MB or GB of cache,
consider creating a job that programmatically checks cache and then
clears the 'SQL Plans' from cache based on one of the options/code
below.

  OPTION 1 - Clearing JUST the 'SQL Plans' based on just the amount of
  Adhoc/Prepared single-use plans (2005/2008):

DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE
                    WHEN usecounts = 1
                        AND objtype IN (
                            'Adhoc'
                            ,'Prepared'
                            )
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE
            WHEN usecounts = 1
                AND objtype IN (
                    'Adhoc'
                    ,'Prepared'
                    )
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 10
BEGIN
    DBCC FREESYSTEMCACHE ('SQL Plans')

    RAISERROR (
            '%s MB was allocated to single-use plan cache. Single-use plans have been cleared.'
            ,10
            ,1
            ,@StrMB
            )
END
ELSE
BEGIN
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            ) —

    Note: this IS ONLY a warning message
        AND NOT an actual error.
END
GO

OPTION 2 -

Clearing ALL of your cache based on the total amount of wasted by single-use plans (2005/2008):

DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE
                    WHEN usecounts = 1
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE
            WHEN usecounts = 1
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 1000
    DBCC FREEPROCCACHE
ELSE
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            )
GO


Do I need to create tablespace and also add all datafiles before importing a schema using datapump?

impdp is able to create the tablespaces. You can even change the location of the datafiles if needed. Check the documentation for more information since there are many parameters with this tool (REMAP_DATAFILES and REUSE_DATAFILES for example).


If binary logging is already turned on and you are simply creating a new slave, unless you are nearing the limit of network bandwidth on the master (in which case, you have bigger problems), there are really no risks.  From the master's perspective, the slave is just a single client which happens to be grabbing binary logs instead of executing SQL.

If binary logging is not already on the master, this will add to the I/O and storage overhead on the master because each transaction will have to be written to the binary log file after it completes.  This is sequential writes, though, so unless you are already nearing the limits of your I/O subsystem, it shouldn't be a huge deal.


500 databases or 1 database with 500 tables or just 1 table with all the records?

Having been on the receiving end of this multiple times from COTS applications, I would choose multiple databases almost every time. In this case, I would still choose multiple databases over any other, and here is why:

!This is for the SQL Server related tag and is not transferrable in logic to other RDBMSs!

Standards - each database is setup the same way with the same standard names and tables.
Flexibility - a new customer needs their language to be finnish and a different collation to be used, easy enough since it's in it's own database. Try dealing with that with a single table and everyone's information in it. Making a change for a request or feature but only for a single customer, you'll start to like a database for each customer.
Security - depending on needs, a different login and user for each database to keep security tight.
Automation - since each database is an exact copy (or base tables are exact copies at least), this makes it extremely easy to automate tasks.
Upgrades/Changes - changes and updates can be rolled out for each customer on their specific schedules and needs. If a single upgrade or schema change goes wrong, only a single customer is down and not all customers.
Granularity - ever need to restore older data that may have been accidentally deleted? Yep, much easier to do this when segregated by database rather tan all jumbled together in the same table or restoring a huge database for a few records in a different schema.
Locking and Blocking - if all of the records are in the same table, some type of optimistic locking will need to be used if you don't want too many performance problems of all of the customers attempting to read/write their data at the same time. This is going to lead to more tempdb utilization and make a process much heavier in terms of resources than it should be.

Some of the downsides:

Admin Overhead - if automation is not used and scripts created for re-use... it's going to make completing tasks manually a large effort. If automated or scripted properly, there should be little administrative overhead.
Backups - now there are more databases and your restore strategy has to take that into account. The backup routine that is being used needs to automatically understand that new databases should be backed up.
Disk Space - sure, using individual databases could lead to lower space utilization efficiency. I find this to be a very small downside, almost non-existent but depending on implementation could represent an impact, especially if in the full recovery model.

The pros (IMHO) extremely out-weigh the cons. We don't know your specifics so some of these items might be moot, but there are many different things to think about and I hope I captured most of the big ones with what you'll be needing to use these for.


Dropped an index and recreated it in PostgreSQL 9.2, not used anymore

I solved the mystery on pg irc channel - I had a long running query (idle in transaction) and they told me that a new index doesn't get used until all transactions that started before its creation don't finish.

Killed the transaction, and now the indexes are back to normal.


You can get some information about the last change to a table with xmin, eg:

select max(xmin::text::bigint) from t;

But you need to be aware of many caveats including modulo and wraparound and frozen xids.

testbed:

set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create or replace function f(p_schema in text, p_table in text)
                  returns integer language plpgsql immutable as $$
declare
  n integer;
begin
  execute 'select max(xmin::text::bigint) from '||p_schema||'.'||p_table into n;
  return n;
end;$$;
--
create table foo as select generate_series(1, 100) as id;
create table bar as select generate_series(1, 100) as id;
create table baz as select generate_series(1, 100) as id;
--

method:

select table_name, f(table_schema, table_name)
from information_schema.tables
where table_schema='stack'
order by 2 desc;
/*
 table_name |   f
------------+--------
 baz        | 784657
 bar        | 784656
 foo        | 784655
*/
--
update foo set id=id+1 where id=100;
--
select table_name, f(table_schema, table_name)
from information_schema.tables
where table_schema='stack'
order by 2 desc;
/*
 table_name |   f
------------+--------
 foo        | 784658
 baz        | 784657
 bar        | 784656
*/

cleanup:

drop schema stack cascade;
set role dba;
drop role stack;


shrink database in always on availability

For a database to be in an Availability Group it needs to be in FULL recovery mode. Part of your script tries to set it to Simple mode.

The bigger question of course is, why are you shrinking your log at all?

Edit: OK, having seen your edit - the error indicates (one of) your secondary database(s) is not up to date, I'd look into why that might be... You won't be able to truncate the log until all secondaries are up to date.


Storing files with extension in database as BLOB

I think "user has one document" (and the others) is really a business rule - something you might need to change in the future, not a data rule. Therefore it should be enforced by triggers / constraints, not your schema

I'd go with a schema like this:

USERS -< USER_DOCUMENTS >- FILES

INVOICES -< INVOICE_SCANS >- FILES

BUILDINGS -< BUILDING_PHOTOS >- FILES

TRANSACTIONS -< TRANSACTION_RECEIPTS >- FILES

Where FILES looks like this:

FILES
file_id
content_type
name
data (BLOB)

While we're at it "transactions" is a very overused term in databases.


Not Able to access user privileges for root in mysql

Restart mysqld_safe with this option added --skip-grant-tables

That will work only from localhost, and gives you a clean slate for creating a new root password.


Safe to use SQLNET.ALLOWED_LOGON_VERSION_SERVER in 11g sqlnet.ora file?

The parameter exists in 11g as well. This is the 11g documentation:

SQLNET.ALLOWED_LOGON_VERSION

  Allowed Values

  12 for the critical patch updates CPUOct2012 and later Oracle Database
  11g authentication protocols (recommended)
  11 for Oracle Database 11g authentication protocols
  10 for Oracle Database 10g authentication protocols
  9 for Oracle9i Database authentication protocols
  8 for Oracle8i Database authentication protocols (default)


If you want to retrieve columns in a specific order, then select columns in that specific order.

drop table pivot_test;
create table pivot_test
(
  c0 number,
  c1 varchar2(30 char),
  c2 number,
  c3 number
);

insert into pivot_test values (0, 'A', 1, 5);
insert into pivot_test values (0, 'B', 2, 5);
insert into pivot_test values (0, 'C', 3, 5);
insert into pivot_test values (0, 'D', 4, 5);
commit;

select * from pivot_test pivot (sum(c2) as netto, sum(c3) as zysk for c1 in ('A' as a, 'B' as b, 'C' as c, 'D' as d));

        C0    A_NETTO     A_ZYSK    B_NETTO     B_ZYSK    C_NETTO     C_ZYSK    D_NETTO     D_ZYSK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          1          5          2          5          3          5          4          5

select c0, a_netto, b_netto, c_netto, d_netto, a_zysk, b_zysk, c_zysk, d_zysk from pivot_test pivot (sum(c2) as netto, sum(c3) as zysk for c1 in ('A' as a, 'B' as b, 'C' as c, 'D' as d));

        C0    A_NETTO    B_NETTO    C_NETTO    D_NETTO     A_ZYSK     B_ZYSK     C_ZYSK     D_ZYSK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          1          2          3          4          5          5          5          5


As already mentioned in the comments by @JacobH, consider putting btr_orders.id in an index.

I have assumed you have a scenario similar to this one:

 CREATE TABLE users
 (
     user_id integer PRIMARY KEY
 ) ;

 CREATE TABLE btr_orders
 (
     id SERIAL PRIMARY KEY,
     active_since timestamp without time zone,
     premium_until timestamp without time zone,
     intermediate_premium_until timestamp without time zone,
     user_id integer NOT NULL REFERENCES users(user_id),
     temporary boolean NOT NULL DEFAULT true,
     some_cargo text
 ) ;

My assumption is that the column named id is actually your PRIMARY KEY, and declared it as such. By doing so, I automatically get an index on that column.

I simulate users and orders:

 INSERT INTO users (user_id)
 SELECT generate_series(1, 5000) AS user_id ;
 INSERT INTO users (user_id) VALUES (40000000) ;

5000 rows affected
1 rows affected

 INSERT INTO btr_orders
     (active_since, premium_until, intermediate_premium_until, user_id, temporary)
 SELECT
     active_since,
     active_since + interval '1 day' AS premium_until,
     active_since + interval '12 hours' AS intermediate_premium_until,
     case when random() < 0.5 then 40000000 else random()*4000+1 end AS user_id,
     random() < 0.5 AS temporary
 FROM
     generate_series(date '2017-01-01', date '2017-12-31', interval '3 minutes') AS x(active_since);

174721 rows affected

The execution plan for your query is:

 | QUERY PLAN                                                                                |
 | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | Limit  (cost=0.42..201.70 rows=1 width=65) (actual time=25.518..25.519 rows=1 loops=1)                                                                                |
 |   ->  Index Scan Backward using btr_orders_pkey on btr_orders  (cost=0.42..11876.08 rows=59 width=65) (actual time=25.517..25.517 rows=1 loops=1)                                        |
 |         Filter: ((NOT temporary) AND (user_id = 40000000) AND ((active_since IS NULL) OR (active_since <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now()))) |
 |         Rows Removed by Filter: 96067                                                                                |
 | Planning time: 0.143 ms                                                                                |
 | Execution time: 25.536 ms                                                                                |

It takes miliseconds.

You can improve the performance by using a relatively simple partial index:

 CREATE INDEX quite_simple_idx ON btr_orders
     (user_id, id)
 WHERE NOT temporary ;

This index will take care of:

Filtering out all rows where temporary is true. The index will only be used if you filter by temporary = false as one of the factors in your WHERE conditions.
Letting the database quickly seek for user_id = xxxxx.
Once this user_id is found, results will retrievable sorted by id.

This changes the execution plan to this one:

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------------------------------- |
 | Limit  (cost=0.29..19.15 rows=1 width=65) (actual time=9.681..9.681 rows=1 loops=1)                                                             |
 |   ->  Index Scan Backward using quite_simple_idx on btr_orders  (cost=0.29..1546.49 rows=82 width=65) (actual time=9.679..9.679 rows=1 loops=1) |
 |         Index Cond: (user_id = 40000000)                                                                                |
 |         Filter: (((active_since IS NULL) OR (active_since <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now())))     |
 |         Rows Removed by Filter: 23963                                                                                |
 | Planning time: 0.179 ms                                                                                |
 | Execution time: 9.700 ms                                                                                |

If (and that's a big if) the simulated data I put in for test is similar enough to your actual scenario, you can improve by a factor of (about) 2. In any case, by just having an index on id, you already move from "seconds" to "miliseconds".

If you might have queries with both temporary = true and temporary = false (or temporary and NOT temporary), you can have two partial indexes, one with WHERE temporary and another with WHERE not temporary. You can also have one single index where you would add also the temporary column. Some experimentation is needed to find which is the best performing combination for every case (it depends a lot on the ratio of false/true and how often you change this value).

You can check all these simulations at dbfiddle here


This is almost always a Kerberos issue, due to an error somewhere along the way of setting up the SPNs.  See: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed

One easy to make, but harder to find, problem is to have duplicate SPNs on your network.

If that might be your problem, read the following post to help you find the duplicate: http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx

A code snippet from that link:

setspn -q MSSQLSvc/mymachine.mydomain.com:1433
CN=MYMACHINE,OU=Workstations,DC=mydomain,DC=com
        MSSQLSvc/mymachine.mydomain.com:1433
        HOST/MYMACHINE
        HOST/MYMACHINE.MYDOMAIN.COM


SSIS import data or insert data if no match

Here is what i did:

Created a new data flow to look up and insert non matching fields.
Inserted the actual data flow.


SQL Server 2008 R2 Restore a back up to a new database

You cannot access the records in a database backup without restoring it to a database. You can however restore it to a different database from the one that it was backed up from.

The following link illustrates how to do this using Transact-SQL: http://msdn.microsoft.com/en-us/library/ms190447(v=sql.105).aspx

and in Management studio: http://msdn.microsoft.com/en-us/library/ms177429(v=sql.105).aspx

Then once you have the new database you can write SQL to join the two instances (obviously security considerations will need to be taken into account first). E.g

SELECT Table1.*, Table1_Backup.* FROM Table1
INNER JOIN DB_Restore.dbo.Table1 AS Table1_Backup ON Table1.ID = Table1_Backup.ID

or similar.

As @Flem mentions you could then use the MERGE statement to merge the two datasets, however you want.


Add range of values under a column using rowid

Technically, you could use Oracle's SUM function (good explanation here: http://www.techonthenet.com/oracle/functions/sum.php). Your code would look something like this:

select sum(time)/3600
  from table_name
 where rownum >= 1 and rownum <=5;

However, without knowing more about the table, I don't know if the TIME column is simply an integer, or is a date/timestamp field. If it is date/timestamp, SUM won't really do what you need it to.

One other word of caution, using rownum doesn't always return the same results between runs. Please take a look at http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html for a much more succinct explanation than I can provide.


order of inserting record is mismatching

If you don't specify an order by clause your RDBMS is free to return the results as it chooses. Usually it will return the results in the order it can retrieve the records the fastest.

Since you have the column codGenLedger as the clustered key the records will be stored ordered by codGenLedger physically on disk (unless your indexes are fragmented, but they are still read in that order).

Your RDBMS will most likely do a clustered index scan in your example and return your results ordered by the clustered index.

See this sqlfiddle for a sql server example.

That is actually sorted by your uniqueidentifier, it doesn't look like it when you look at it alphabetically but it is sorted by the hex representation of the binary data.

See here for a complete explanation.

  In fact, the data is sorted; it's just not sorted the way you expect.
  SQL Server displays globally unique identifier (GUID) data as a series
  of alphanumeric strings, but the uniqueidentifier data type is
  equivalent to the binary(16) data type. The GUID value you see is
  simply the hexadecimal representation of the binary value. So the data
  is sorted by this hex representation, not alphabetically.


SELECT DISTINCT SUBSTRING(employee_id FROM 5 FOR 5)
FROM employee;

  some of record contains 3 to 4 _ symbol and position is different

SELECT DISTINCT CONCAT('_',
                       SUBSTRING_INDEX(SUBSTRING_INDEX(employee_id, '_', 2), '_', -1),
                       '_')
FROM employee;


Capturing the result(print statements) of SP_UPDATESTATS

I would use the free Ola Hallengren maintenance solution to update statistics.

See Example C from the above link, where you can update the statistics in all user databases via: -

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'

You can also edit this for your needs to only do an individual database.

The logging you require can be achieved by specifiying the LogToTable parameter.

As a final point. Remember you don't have to update statistics if you are rebuilding your indexes. Rebuiliding your indexes during your weekly maintenance window might be your best option. Again you can use the Ola Hallengren solution for this.


What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

This is stated in a lot of places, but I think it worth mentioning always when we compare the timestamp with time zone with timestamp without time zone types: the timestamp WITH time zone does not store the time zone information along with the timestamp. What it does is to store every data in UTC time zone, as stated in the docs:

  For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

It is considered valid for some to use timestamp WITHOUT time zone in situations where (1) everything is in the same timezone or (2) the application layer handles the time zone and just store everything in a certain time zone (usually UTC). But it is also considered an anti-pattern, simple because the correct solution for (1) is to configure the TimeZone setting to the given one timezone for the system and (2) is already solved, as PostgreSQL already stores everything on the same timezone (UTC).

Now, with those two down, I can came with only one good reason to use timestamp WITHOUT time zone. That is when you want to store events in the future and that some kind of alert must be triggered when we got to that time. That could be good for timestamp WITH time zone if, and only if, the rules defined by region's laws about time zone didn't ever change. The most common rule that changes is about the adoption or not of day light saving time (DST).

For example, imagine that you are at, let's say, 2013-06-15 (not yet in DST) schedule some event to happen at 2013-01-15 10:00 (which would be already in DST), and at 2013-06-15 your region was designated to adopt DST; but, some time after that, the government changed the rule and say your region will no longer use DST, and suddenly your scheduled time becomes 2013-01-15 11:00 (instead of 10:00), that if you use timestamp WITH time zone, and keep your TZ configurations up-to-date. Of course you may also notice that it is possible to treat such cases also with time zone, if you keep track of the rule changes in the regions/timezones of your interest, and update the affected records.

Worth mentioning that some regions does often change these rules (like at Brazil, some states - not the entire country - often change), but in most cases it changes it very earlier, so your users would be affected only by events scheduled very far from the current time.

With all that said, I only have one more suggestion. If you do have users, logs, or anything on different timezones, store the timezone they are coming from somewhere and choose and use timestamp with time zone. That way you can (1) cross events happening closer to each other for different sources (independent of their timezones) and (2) show the original time (the clock time) the event has happened.


SQL Server: join two tables with "valid-to" dates

DECLARE @t1 TABLE (T1_key int, T1_Data char(1), T1_ValidUntil datetime)
DECLARE @t2 TABLE (T2_key int, T2_Data char(1), T2_ValidUntil datetime)

INSERT @t1 VALUES (1, 'A', '2000-01-01')
INSERT @t1 VALUES (1, 'B', '2000-06-30')
INSERT @t1 VALUES (2, 'C', '2005-05-31')
INSERT @t1 VALUES (3, 'D', '2004-12-31')
INSERT @t1 VALUES (3, 'E', '2007-04-30')
INSERT @t1 VALUES (3, 'F', '2008-01-31')

INSERT @t2 VALUES (1, 'R', '2002-03-31')
INSERT @t2 VALUES (2, 'S', '2001-06-30')
INSERT @t2 VALUES (2, 'T', '2003-02-28')
INSERT @t2 VALUES (2, 'U', '2005-05-31')
INSERT @t2 VALUES (3, 'V', '2006-09-30')
INSERT @t2 VALUES (3, 'W', '2007-06-30')

SELECT
   T1.*, T2x.*
FROM
   @t1 T1
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t2
   WHERE T1_key = T2_key AND T2_ValidUntil >= T1_ValidUntil
   ORDER BY T2_ValidUntil
   ) T2x
UNION
SELECT
   T1x.*, T2.*
FROM
   @t2 T2
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t1
   WHERE T1_key = T2_key AND T1_ValidUntil >= T2_ValidUntil
   ORDER BY T1_ValidUntil
   ) T1x


How to filter a JOIN on *all* items of a given list (filter articles with all given tags for example)

IMHO the best way to ensure it is by using EXISTS to check one by one every condition:

select a.*
from   __articles a
where  exists (select 1 from __articles_tags where artid = a.artid and tagid = 1)
and    exists (select 1 from __articles_tags where artid = a.artid and tagid = 2)
and    exists (select 1 from __articles_tags where artid = a.artid and tagid = 3);

Bu depending on your table schema, you could use GROUP BY and COUNT to select which articles have the desired tags.

select   artid
from     articles_tags
where    tagid in (1,2,3)
group by artid
having   count(*) = 3

Then use the result list to fetch articles records.

select  *
from    articles
where   artid in (select   artid
                  from     articles_tags
                  where    tagid in (1,2,3)
                  group by artid
                  having   count(*) = 3);

Check it here: http://rextester.com/LOB71207


Can't be done. Azure SQL DB will not accept a non-secure connection. There's no configuration parameter you can set to change this behavior. This is documented in multiple places (e.g. https://technet.microsoft.com/en-us/library/hh351833.aspx). Also, it's not just about the traffic getting sniffed. Azure requires certificate validation also to protect against mitm attacks. Without these, the risk includes credential theft, not just snooping of your traffic.

Btw, what driver are you using? I'm surprised there are drivers that support Azure SQL DB but cannot do it with a secure connection (it's actually TLS, SSL has long been disabled). If you provide more details, folks here might have alternate solutions for your unsafe driver.


As suggested by Andrew
The 1st part, I explicitly mentioned wallet location in sqlnet.ora file, and also included the following line:SQLNET.WALLET_OVERRIDE=TRUE. Also verified the correct tns entry in tnsnames.ora. (This is already taken care of during container start, so I didn't have to do anything).
For the 2nd part, I had to use the same sqlnet.ora with the exception that the WALLET_LOCATION parameter was updated with the location to the wallet folder on the client machine. And for the client program, I had to set the TNS_ADMIN environment variable; I had to point it to the folder where the former file would reside. Additionally added a tnsnames.ora file and added a tns name (similar to the oracle service name on the db server).
Note: On the client machine, we have to be careful of the syntax in the sqlnet.ora and the tnsnames.ora file. Different clients have various ways to connect. I have just noted for the case of a nodejs program which used orcaledb as the oracle client. These specific type of clients make use of the environment variable TNS_ADMIN.


All time we use lot of SQL queries. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

Use views and stored procedures instead of heavy-duty queries.
Try to use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Try to use UNION ALL statement instead of UNION

For more http://cybarlab.blogspot.com/2013/02/sql-queries-optimization-tips.html

Thanks n regard


STARTUP TIME

For everyone running MySQL 5.7

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM performance_schema.global_status
WHERE variable_name='Uptime';

For anyone running MySQL 5.6 and prior, or MySQL 5.7 in MySQL 5.6 compatibility mode

SELECT NOW() - INTERVAL variable_value SECOND MySQL_Started
FROM information_schema.global_status
WHERE variable_name='Uptime';

Just mentioned this three(3) days ago (Make MySQL print a timestamp at each time it starts)

SHUTDOWN TIME

There are four ways to acquire a shutdown time

METHOD #1 : Search the Error Log

If the error log is called error.log, run this

grep "Shutdown complete$" error.log | tail -1 | awk '{print $1,$2}'

This will produce the date (YYMMDD) and time (HH:MM:SS)

METHOD #2 : InnoDB Buffer Pool

If you have innodb_buffer_pool_dump_at_shutdown configured, look for the timestamp of the file that was written. The default filename is ib_buffer_pool. Mentioned this back on Dec 06, 2017 (Control InnoDB buffer pool allocation in MySQL (5.7+) ib_buffer_pool is usually written in folder set by datadir, you can run the following after shutdown:

cd /var/lib/mysql
ls -l --time-style=+"%Y-%m-%d %H:%M:%S" ib_buffer_pool | awk '{print $6,$7}'

METHOD #3 : Binary Logging

If mysql has binary logging enabled, get the timestamp of the last binary log after shutdown.

METHOD #4 : Error Log

You are probably saying, "I DON'T WANT TO LOOK INSIDE THE ERROR LOG !!!"

You don't have to. Just get the timestamp of the error log after the shutdown is complete.

CAVEAT ON SHUTDOWN

Method #1 is not any good to you if mysqld crashes.

The other three(3) methods will simply tell you the last time mysqld did any kind of writes, whether it was a clean shutdown or a crash.

YOUR ACTUAL QUESTION

First off, a trigger is not needed nor would it fire off on shutdown.

You cannot write a shutdown time to a table when mysqld is shutting down.

The best I could suggest is to capture the shutdown datetime with one of the four(4) aforementioned methods and write it in a script. You can execute that script as follows:

Create a file called /var/lib/mysql/startup_stuff.sql

Run chown mysql:mysql /var/lib/mysql/startup_stuff.sql

Write inside that script in such a way that the datetime is written to a table

Add this line to /etc/my.cnf (see MySQL Documentation on init-file)

init-file=`/var/lib/mysql/startup_stuff.sql

Startup mysql using service mysqld start


Oracle - Sync Sequence nextval using CONNECT - side effects?

CONNECT in this context has nothing to do with database connections.

hierarchical_query_clause ::=

You can change the increment of a sequence to set it to a required value. Example:

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 increment by 100;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102

You can also use the undocumented RESTART clause, but that does not work in 11.2:

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 30 12:44:26 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 restart start with 101;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102