You cannot prevent this issue of reordering once you begin inserting data.

Notwithstanding, if you want to reorder the IDs sequentially, you have no choice but to rebuild the table.

Here is the SQL to do that

ALTER TABLE students RENAME students_outoforder;
CREATE TABLE students LIKE students_outoforder;
TRUNCATE TABLE students;
INSERT INTO students (stud_name,class,subject)
SELECT stud_name,class,subject FROM students_outoforder ORDER BY id;

After running this, run this

SELECT * FROM students;
SHOW CREATE TABLE students\G

You will see the IDs reordered 1..8 and the AUTO_INCREMENT of the table at 9

Then, you can get rid of the old table

DROP TABLE students_outoforder;

Give it a Try !!!

CAVEAT: Do not try this with the students table has foreign key constraints in it.


User Password Expiry date is null in user_users?

It might be that your account has been assigned the profile with PASSWORD_LIFE_TIME set to UNLIMITED, and thus your password will never expire. You can query the USER_PASSWORD_LIMITS view to determine what password limits are currently in effect for your account:

SQL> select * from user_password_limits;

RESOURCE_NAME                    LIMIT
-------------------------------- ---------
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7


How to get the PC models with identical speed?

This should do the trick:

select distinct
  pca.model, pcb.model, pca.speed, pca.ram
from pc pca
join pc pcb
  on (pca.speed = pcb.speed
  and pca.ram = pcb.ram
  and pca.model > pcb.model)


Azure SQL Database Elastic Pools - TSQL to get current Elastic Pool

What about this?
-- TSQL to find Databases, corresponding elastic pool names and DB edition

SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
ORDER BY
       d.name, dso.elastic_pool_name


On the Slave:

stop slave
reset slave

On the Master

reset master

On the Slave

start slave
show slave status

If you can include info like which version/flavour of MySQL and OS you are using that will help people responding to your query.


DB Permission: User can drop only their created db's?

to grant use privliges to create database you can use the following command:

use [master]
GO
GRANT CREATE ANY DATABASE TO [LoginName]
GO

To drop it you need to make sure the user has owner privileges on the database, you can use the command ALTER AUTHORIZATION ON DATABASE::db TO [LoginName];


`make install` on a module, I get `pgxs.mk:114: recipe for target 'install' failed`

That's a problem with the pg-bignum's Makefile. Filed upstream here.


The GROUP BY id is useless in the query, since there is only one row for every id (it appears to be the primary key of the table).

What you need a count over the whole result. This is easy using a window aggregate:

SELECT count(*) OVER () AS total_count,
       id, parent_id
FROM paths
WHERE parent_id = 5 ;

The last requirement is rather weird but you could have that, too, with a more complicated construct:

SELECT count(p.id) OVER () AS total_count,
       p.id, p.parent_id
FROM (SELECT 1) AS dummy
  LEFT JOIN paths AS p
    ON p.parent_id = 5 ;


Here's what I'd do:

Use EXPLAIN on your SELECT to reveal which indexes it's using.
Tune your query and repeat step 1 until satisfied.
Create your new table with the desired structure.
Use INSERT ... INTO new table SELECT FROM ... old table replacing the select part with the SELECT query you tuned in step 2.


Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off

Usually deadlock happens when:

Query 1 tries to do an update - but before updating it needs to do a select. So first it applies READ lock (S - shared lock). Let's say it managed to lock a couple of pages.
Query 2 tries to do a select and sets a READ lock (S - shared lock) on some of these pages.
Query 1 now tries to do an update - so it tries to change READ lock to a WRITE lock (i.e. S (shared) to an X (exclusive) lock). It  manages to do it on certain pages but can't do it on all of them since some of the pages are locked by Query 2.
Query 2 in turn tries to obtain more pages - but they now have a X lock (WRITE lock).

In the end both queries are waiting for each other so SQL Server decides to kill one query with DEADLOCK exception.

By the way - issue is not Primary Key Index here.


Not sure if this the answer you are looking for but Percona Server has a Log Archiving for XtraDB feature and you can restore a backup to a specific point in time by specifying an LSN.

Just be aware of this note from the documentation:

  This feature implementation is considered BETA quality.


Should I worry about failed login attempts on SQL Server

If possible, you should change your security approach to be denied by default, allowed by permission.

Block access from all unknown IP's.  Allow access from trusted IP's.


Can anyone describe the step by step process of recovery , backup and restore in sql server

Follow the links:

1.Backup Under the Full Recovery Model

http://msdn.microsoft.com/en-us/library/ms190217%28v=SQL.100%29.aspx

2.BACKUP and RESTORE Commands

http://msdn.microsoft.com/en-us/library/ff848768

3.Back Up and Restore of SQL Server Databases

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

Hope this will help.


Use what you need and don't worry about performance.  But use only what you need.  There are minor inefficiencies in saying TEXT when VARCHAR(255) will suffice.  Ditto for VARCHAR(255) when VARCHAR(20) will suffice.

Here's why:  If a SELECT needs build a tmp table (eg, for ORDER BY), it first tries to use a MEMORY tmp table (better), but may have to fall back on a MyISAM table (slower).  Any kind of TEXT or BLOB forces MyISAM.  VARCHAR turns into CHAR (for the tmp table); if the tmp table gets too big for MEMORY, it converts to MyISAM.

TINYTEXT is probably never a good idea.

A related issue:  CHAR(...) is useful only if the field is (1) constant length, and (2) declared with the appropriate CHARACTER SET.  A common mistake is putting a fixed-length hex string in a utf8 CHAR field.

VARCHAR and TEXT (all sizes) have a small length field prefixing them.  This is relatively insignificant.


How to restore a filegroup from its backup in SQL Server

After you restore the filegroup, you need to take a tail log backup of the restored database .
Restore the lastest differential you may have to  with norecovery.
Restore all the tlog backups to  with norecovery.
Restore the tail log backup from step 1 to  with recovery.


How to export a single huge table from SQL Server?

I'd of thought BCP, with a format file rather than defaults, would be your best bet.

Creating a format file


Combining 2 temp tables to aggregate into a single table

Seems to me like you need a JOIN instead of a UNION:

SELECT  ISNULL(T1.[user],T2.[user]) [user],
        ISNULL(T1.TotCall,0) TotCall,
        ISNULL(T2.[120Calls],0) [120Calls]
FROM #tmpTotCalls T1
FULL JOIN #tmp120s T2
      ON T1.[user] = T2.[user]
;


Is it possible to record all the SQL executed by a session?

You can find all SQLs that were executed in the sys.v$sqltext view. Together with the sys.v$session view you can see who executed it. Is this what you are looking for?

Otherwise check to see if you can get the information from the application. See if you can change the log level for the classes that access the database.


Setting connection properties (application_name) for Postgres connections

If you can't configure it via the DSN itself, you can always have your connection establishing subroutine set the application name by executing a SQL command on the connection before it hands that connection back to the caller:

set application_name to 'my application name';

In VBScript, this looks like

dim cn
set cn = CreateObject("ADODB.Connection")
cn.ConnectionString = "DSN=PostgreSQL35WDsnHere"
cn.open
cn.execute("set application_name to 'my_application_name';")


Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

I have a client at my employer's company with 192GB RAM using 162GB Buffer Pool.

Here is what we did for the client

InnoDB Tuning : I set the following options

innodb_read_io_threads (maxed it to 64)
innodb_write_io_threads (maxed it to 64)
innodb_thread_concurrency = 0 (It's default now in MySQL 5.5/5.6)
innodb_io_capacity (set it to IOPs of Disk, for this client, 20000 [2-0-0-0-0 not typo])
innodb_buffer_pool_size=162G
innodb_buffer_pool_instances=2 (Number of Physical Cores)

OS Tuning (numactl --interleave=all)

If you forget numactl --interleave=all, the buffer pool experiences swapping.

Here are my past posts on tuning InnoDB

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?


Performance Issues on MSSQL Server Web Edition

These types of decisions should be backed up by a lot more investigation and testing. However, from the information you have provided I can give you a few pointers to put you in the right direction to make the right decision.

Since you are already spiking CPU to 100%, reducing cores to save cost and get more memory will only make things worse.
Also, check what is causing CPU spikes on the server, maybe some performance tuning for the code can help the situation.
Why add 128GB RAM when SQL Server can only make use of 64GB on the server? Do you have any other applications on the server? If you do, move them to a separate machine, it will help with the CPU spike issue.
Even if you do plan to upgrade to Standard Edition why add 256GB RAM, your SQL Server will only be able to use 128GB RAM.
Collect some query stats on the server during peak hours to check if more memory will actually help the situation.
Also, look into moving "old" data into a separate environment (data purging), to keep the application DB size small. (not an easy thing to do but is something should be on your todo list).
Setup a test environment, to test your conclusion drawn from the above investigation to find out whether your suggested fix will actually work. Also, make sure you test your new solution in the proper test environment (Like prod) and not with 10 rows in the testing environment.

Once you have done all the above, the chances are you will make the right decision or what ever you do will not be very far off the right solution.

Also, if possible convince your business to buy the enterprise license, present them the case, educate them and let them make the decision.


It really does come down to what you need and why you need it. Managed Instance has a single, huge, strength over Azure SQL Database, cross-database queries. If you need to query between databases and you don't want to rewrite the code, then Managed Instance is the way to go. There are some other advantages such as having SQL Agent (easily replaced with Azure Automation) and some other behaviors, but the cross-database query is the huge driver.

I can't answer the geo-replication question because I haven't tested that from Managed Instance, so I'm not aware of the level of support there.


Oracle RAC 11g R2 DB silent install: parameter needed for system class

Try using these 3 options :

oracle.install.option=INSTALL_DB_SWONLY \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=true


To count how many contributors have contributed 5 images or more:

SELECT COUNT(*) AS number_of_contributors
FROM
  ( SELECT 1
    FROM images
    GROUP BY contributor_id
    HAVING COUNT(*) >= 5
  ) AS t ;

It could be written without the derived table but it's obfuscated:

SELECT COUNT(*) OVER () AS number_of_contributors
FROM images
GROUP BY contributor_id
HAVING COUNT(*) >= 5
LIMIT 1 ;

(there is a slight difference between the two queries. If there are no contributors with 5+ images, the first query will return one row with 0. The second query will return no rows.)


Are relay logs necessary for replication?

Replication does require relay logs. IF you have a replicating slave then it does use relay logs, and you have not disabled them. Please check the following:

SHOW SLAVE STATUS \G

Do you have Slave_IO_Running: Yes? Then your replication seems to be running. Please look for the Relay_Log_File column - what does it say? It should point out the current relay log file.

Look for this file. It should be by default on your @@datadir. Is it there? There's your replication relay logs.

You say you've disabled the relay logs -- I suggest you haven't (I don't even recall there's a way to disable the relay logs). How in your opinion have you disabled the relay logs?


How to tell if SQL is autogenerated by some tool

Trying to reverse engineering to find the tool will take too much time and you will possibly never find the solution. Debug this through an Agile methodology:
“The most efficient and effective method of conveying information to and within a development team is face-to-face conversation”
So go to your colleagues and don't be afraid to ask:
"Are we using an automated tool or a drag-n-drop tool to create queries? I ask this because they really look machine-made to me"
And the result can be:

Yes, they are written by a tool: and show to your colleagues how painful this choice can be for your performances
No, they are written by Tom: and talk to Tom and say "Hey, Tom, I was having an hard time reading through your code, is there any way I can help you..."

Be nice to people. Machines cannot change but humans can.


As shown from your SHOW PLUGINS;, InnoDB is present. XtraDB is also present.

If you want to be sure, you must run SHOW ENGINES;. That will show what storage engine is the default. The fact that the XtraDB plugins are active shows that XtraDB must be in use.


Database Log File Size Growth So Huge Unexpectedly

Full backup doesn't clear the logs, I suppose your database is not in simple recovery mode as logs are increasing and its filling up rapidly. Do you take transnational log backup anytime in a day or week?

Ideally Full backup should be taken every day, Differential backup few times in a day and log backup few times in an hour however it also depends upon RPO and RTO.

One thing to note here is transaction log backup helps you in storing active part of transaction as illustrated below:

  A transaction log backup allows you to backup the active part of the
  transaction log.  So after you issue a "Full" or "Differential" backup
  the transaction log backup will have any transactions that were
  created after those other backups completed.  After the transaction
  log backup is issued, the space within the transaction log can be
  reused for other processes.  If a transaction log backup is not taken,
  the transaction log will continue to grow.

Please read recommendation from Microsoft on backup and frequency:

  A database administrator typically creates a full database backup
  occasionally, such as weekly, and, optionally, creates a series of
  differential database backup at a shorter interval, such as daily.
  Independent of the database backups, the database administrator backs
  up the transaction log at frequent intervals. For a given type of
  backup, the optimal interval depends on factors such as the importance
  of the data, the size of the database, and the workload of the server.
  For more information about implementing a good strategy, see
  Recommendations in this topic.

You may read more about RPO and RTO from this link as nicely explained by Mr. Paul Randal.

You may also check best practice for Transaction log autogrowth and other details:

https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/


Should I in general partition tables by tenant IDs in a multi-tenant system?

Partitioning is often more about administration (swapping partitions in and up while updating a warehouse, etc.) than performance in general OLTP scenarios and, especially if all your data is on the same drive/array, can result in a performance decrease.
If you need partitioning later you may choose to do it by a different property, perhaps partitioning such that newer, more active, data can be kept on your fastest drives. If this is the case then you have made more work for yourself as you will need to unpick the existing partitioning scheme before implementing the new one.
So while this is at least a little opinion and not 100% fact, I would say partitioning now is premature.


Why is there no predicate applied to the Index Scans?

is there any way to change it to filter before it does the Hash Match without rewriting the query?

In Enterprise Edition, an indexed view can be used. For example:

CREATE VIEW dbo.OINV_OCRD
WITH SCHEMABINDING
AS
SELECT
    INV.CardCode,
    CRD.FatherCard,
    COUNT_BIG(*) AS cnt
FROM dbo.OINV AS INV
JOIN dbo.OCRD AS CRD
    ON CRD.CardCode = INV.CardCode
GROUP BY
    INV.CardCode,
    CRD.FatherCard;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.OINV_OCRD (CardCode, FatherCard);
CREATE NONCLUSTERED INDEX i ON dbo.OINV_OCRD (FatherCard) INCLUDE (cnt);

Then a query like:

DECLARE
    @P2 nvarchar(50) = N'D20B5DD1-C729-4B7A-A276-950CE7DCF128',
    @P3 nvarchar(50) = N'6A0DBEAB-FECB-40DB-86C5-AAFA612DA691';

SELECT COUNT_BIG(*)
FROM [dbo].[OINV] T0
JOIN [dbo].[OCRD] T2
    ON T2.[CardCode] = T0.[CardCode]
WHERE
    T0.[CardCode] = (@P2)
    OR T2.[FatherCard] = (@P3);

Produces an execution plan using index intersection on the indexed view:

Table 'OINV_OCRD'.
    Scan count 2,
    logical reads 8, physical reads 0, read-ahead reads 0,
    lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

All the usual caveats around indexed view usage apply. Automatic indexed view matching is only available in Enterprise Edition (or equivalent). Using COUNT('A') does not affect the core mechanism, though a Compute Scalar is added to convert bigint to integer.

For more details around the indexed view matching feature see my answer to a related question.

If you add tables to the query, the indexed view matching is likely to continue to work (assuming the match is valid) since the optimizer is capable of matching parts of a query to one or more indexed views. This answer can only address the details actually presented in the question.

If the vendor queries follow a set pattern, you could force the indexed view plan to be used via a Plan Guide.


Can migration from MS Access to SQL Server or ... make database faster?

We can't really answer whether you should consider other platforms such as mongodb - we know nothing about the application or its data and whether such a platform would be appropriate.

I can tell you that SQL Server has a lot more features geared toward performance and tuning than Access will ever have. Access is pretty simplistic and, while it can function as a multi-user database, it is not a true RDBMS and really is meant for the desktop space.

Whether those features in SQL Server will make your company's specific application any faster, again, shrug, we just don't know.


Load PostgreSQL database from corrupt drive

Theoretically, your database does not have problem, if it is running.

But to verify it, first Stop your database, make a physical copy of PostgreSQL folders.

After that, start your database make an ordinary backup using pg_dump, after that try to restore this backup in a new environment, if you don't have a problem your database is ok.

If you have some problem I will share a link with the main problems of corruption and how to solve it.

https://stackoverflow.com/questions/11030173/repair-corrupt-database-postgresql

But analyze all of the possibilities before starting this process does not exist the only way to resolve this problem.


GRANT REFERENCE on all user defined types

If you need to do something like this, just use dynamic SQL to generate the command.

SELECT t.name,
       'GRANT REFERENCES ON TYPE::'
       + SCHEMA_NAME(t.schema_id)
       + '.'
       + t.name
       + ' TO public;' AS command_to_run
FROM   sys.types AS t;


The problem is not the user account, as queries are able to run without issue. The problem is firewall-related. Make sure that ports 139 and 445 are open between the application server and the database server, as per http://msdn.microsoft.com/en-us/library/dd283098.aspx:

  To use FILESTREAM in a firewall-protected environment, both the client
  and server must be able to resolve DNS names to the server that
  contains the FILESTREAM files. FILESTREAM requires the Windows
  file-sharing ports 139 and 445 to be open.


Database index on FK with only NULL values

The only situation where an index with full key cardinality = 1 will help is when you are looking for something that does not exists. By using such index you can quickly conclude that nothing will match your predicate, whereas without the index you will have to scan the table.

If you have a large table and you frequently look for things that does not exists, you can benefit from such index (fairly rare situation).

The downside of such index is that the only leaf-page will have a long rid list that has to be maintained during modification of the table.

In most cases you will see an overall performance improvement by dropping that index, but it is impossible to tell for sure without having access to the system. Ideally you have a test system where you can run a representative load for your system and compare the performance with and without the index.

For the big picture, what is the purpose of the attribute that never exists? You may want to consider normalizing this table and move this attribute to a separate table.


How do I insert a record and put the ID into an existing record in another table?

You can use DO block:

DO $$
DECLARE
    rows CURSOR FOR
        SELECT * FROM foos;
BEGIN
    FOR r IN rows LOOP
        WITH _ins AS (
            INSERT INTO aaa (foo)
            VALUES (r.foo)
            RETURNING aaa.id AS aaa_id
        )
        UPDATE foos
            SET aaa_id = (SELECT aaa_id FROM _ins)
            WHERE id = r.id;
    END LOOP;
END$$;


Create failed for Index 'ClusteredColumnStoreIndex'. Timeout expired

In Management Studio for SQL 2014, the timeout for the designers is held in Tools > Options > Designers > Table and Database Designers with a default of 30 seconds.

However, as the syntax is so simple for creating a clustered columnstore, as already suggested, you should script it rather than using the designer, eg

CREATE CLUSTERED COLUMNSTORE INDEX IX_MyTable ON dbo.MyTable ON [PRIMARY]
GO

This option won't time out, but may struggle if you don't have enough memory.

In order to get the best out of your compression, you might also consider creating a clustered index on the table to pre-sort it, then drop it prior to creating the clustered columnstore.  YMMV.


It can be normal for Page Life Expectancy (PLE) on SQL Servers in a Data Warehouse setup with frequent reloads to jump up and down.

Why?

Well which data will users normally query? It's the data that has been most recently inserted, because that is the data that is interesting. Unless you are performing analysis over long periods of time and conducting forecasts based on last years data.

Page Handling in Memory (Buffer Cache)

Microsoft SQL Server has an internal counter in each page that is stored in memory. This counter starts at four and is deducted by one, each time the SQL Server database engine looks at the page, when no reads occurred.

Under this algorithm, there are “Clock Hands” that sweep the cache at regular intervals. Every time the clock hand steps on a not-in-use entry, it decreases cost by some amount. If the entry is not in use and its cost is zero, the clock hand makes the entry invisible and then attempts to remove it from the cache. In fact, Lifetime of an entry is managed by an embedded reference count in the Clock Entry Info class. After this count goes to 0, an entry is destroyed.

(Reference: An in-depth look at SQL Server Memory–Part 2)

In a Data Warehouse setup each time you load new data and each time the users request this new data it has to be read from disk into memory (buffer cache). This results in all pages that have a zero in the internal counter to be removed from the buffer cache.

Page Life Expectancy

Removing data pages from the buffer cache and moving new data from disk into the buffer cache, results in the Page Life Expectancy value to fall below the recommended threshold of 300, because the new pages initially have a PLE of zero (0). The sum of all new pages in memory (PLE=0) and all old pages in memory (PLE > 300) can fall below 300.

A PLE of 300 is not much of a recommendation nowadays:

A PLE of 300 means your entire buffer pool is being effectively flushed and re-read every five minutes. When the threshold guidance for PLE of 300 was first given by Microsoft, around 2005/2006, that number may have made more sense as the average amount of memory on a server was much lower.
Nowadays, where servers routinely have 64GB, 128GB, and higher amounts of memory, having roughly that much data being read from disk every five minutes would likely be the cause of a crippling performance issue...

Reference: Knee-Jerk PerfMon Counters : Page Life Expectancy)

Your SQL Server is performing as designed, given the current hardware

More Hardware?

Now if your users are experiencing performance issues when retrieving data, then you might benefit from more memory, because SQL Server will then be able to store more data in the Buffer Cache without having to purge all of the data when new data is loaded. Your PLE counter will probably still fall when new data is stored in your DW database, but it might not fall so dramatically.

Baseline

You will have to monitor your system and have a baseline that shows you the normal behaviour. Anything outside the norm is then worth an analysis.

5 Reasons You Must Start Capturing Baseline Data

  Baselines are a good idea. Every DBA that I’ve spoken with seems to understand the value: So why is it that so many companies do not have baseline data? I think there are two factors at play. The first is the startup cost, and by cost I really mean time and effort. If you’re going to roll your own method for capturing baselines, you have to put some thought into it, answering the questions:
     - What data are you going to capture?
     - When are you going to capture data?
     - What methods will you use to get the data?
     - Where are you going to store it?
     - How are you going to report on it?
     - How are you going to manage the data long term?

Back to Basics: Capturing Baselines on Production SQL Servers

Once you know your baseline, you know what values for PLE are good for your DW SQL Server.


Specifying a Case-Insensitive or Accent-Insensitive Collation

  Append _AI to an Oracle Database collation name for an
  accent-insensitive and case-insensitive collation.

So I could set, for example:

alter session set nls_sort=hungarian_ai;

But this above will affect only sorts and not the comparisons. You can set comparions to use the sort settings:

NLS_COMP

  LINGUISTIC

  Comparisons for all SQL operations in the WHERE clause and in PL/SQL
  blocks should use the linguistic sort specified in the NLS_SORT
  parameter. To improve the performance, you can also define a
  linguistic index on the column for which you want linguistic
  comparisons.

alter session set nls_comp=linguistic;

With both of the above set:

SQL> select 'equals' as result from dual where 'ÁbCdEf' = 'aBcDéF';

RESULT
------
equals


What are System Dynamics Variables on MariaDB?

For the MySQL/MariaDB server "VARIABLES":

Dynamic: Yes -- You can SET the variable and immediately see the effect.
Dynamic: No -- You need to change the configuration file (my.cnf) and restart the server.

That covers most cases; there are a few that are more complicated.  Also, there is some move toward making more of the Variables "dynamic", so you will see differences based on Version of MySQL/MariaDB.

Also, this is usually the history of the values:

When the server starts, the GLOBAL copy of the variables are set from my.cnf or formulas, etc.
When a user connects, the GLOBAL values are copied into SESSION copies.
If dynamic, SET variable_name = value changes the SESSION value.

SET GLOBAL ... will change the default value for all subsequent connections, not your current connection.  There are exceptions to this...

Some VARIABLES do not distinguish between SESSION and GLOBAL.  For example, since InnoDB's buffer_pool is shared among connections, most settings are GLOBAL; there is no separate value for each SESSION.

(Sorry if I digressed too much; it gets messy.  But usually you don't need to worry about these issues.)


How to manage constants within an Oracle Database

This seems to be an open and shut case for placing the meaningful codes into another table with a foreign key to it from this column.

It restricts the values that can be used and provides a meaning for the numbers.


If I get it right you would like the last row for each column combination (regardless of column order). Easiest is probably to enumerate the rows (using least, GREATEST for column order) in desc order and choose the first one

SELECT ... FROM (
    SELECT...
           , row_number() over (partition by least(author_id, recipient_id)
                                           , greatest(author_id, recipient_id)
                                order by updated_at desc) as rn
    FROM private_message
    WHERE 665 IN (author_id, recipient_id)
) as t
WHERE rn = 1
LIMIT 20


The Oracle Net Listener

  The Oracle Net Listener (the listener) is a server-side process that
  listens for incoming client connection requests and manages traffic to
  the database.

Service Names

  A service name is a logical representation of a service used for
  client connections.

  When a client connects to a listener, it requests a connection to a
  service. When a database instance starts, it registers itself with a
  listener as providing one or more services by name. Thus, the listener
  acts as a mediator between the client and instances and routes the
  connection request to the right place.

  A single service, as known by a listener, can identify one or more
  database instances. Also, a single database instance can register one
  or more services with a listener. Clients connecting to a service need
  not specify which instance they require.


Is it possible to create this type of database for a massive forum?

You can choose this option

Create on table to list all the state as below

State table with two columns

StateId - int(PK)
StateName - varchar

City table with two column as

CityId - int(PK)
StateId - int(FK)
StateName - varchar

Now you can have User table to save member where you add column CityId as 'FK' as below

UserId - Int/bigInt(PK)
CityId - int(FK)
..Other columns

Now in webpage you show list of state, and on selcting state populate city list from City table and on selecting city populate members.


You should use ANY, not IN to search in an array

...where super_user_id = ANY (s.super_users)


I'm working on the assumption that the table in question is a fact table, not a dimension table with a huge composite key:

Just to fix the performance issue in the short term, I would add all of these key columns as the table's clustered index, which means you won't have to INCLUDE a lot of measures and stuff, like the suggested index does. Also, make the index unique if the data allows for this.

As for the column order of a clustered index on a fact table, it depends on how you're accessing them. If you're only using a cube to read large chunks of data, I would probably prioritize INSERT priority by making the index chronological, i.e. putting the date column first - that way, new rows get added to the end of the index (in the best of worlds).

If you're running user T-SQL queries on the fact table, I would try to arrange the index columns in an order that gives you Index Seeks or Range Scans as much as possible: first, columns that are filtered on single dimension keys (think "year", "type", "unit" or "department"-type dimensions), then those columns that are filtered on multiple dimension members, ranges, or used for sorting.

There are, of course, other schools on how to build indexes - this is not a "single correct answer".

Edit: More on clustered vs non-clustered indexes:

I'm guessing that you already have an existing clustered index, and that's why SQL Server suggests a non-clustered index. However, non-clustered indexes have to be explicitly defined with INCLUDE columns. Clustered indexes define the actual storage/sort order of the table, and as such, they will implicitly include all columns in the table (I won't go into LOB columns like varchar(max) and xml).

The clustered index is normally the "catch-all index" that takes care of queries that are not suitable for an existing non-clustered index, which makes it even the more important (in my opinion) that it's well-designed and not, for instance, just on an IDENTITY() column.

Plus, a non-clustered index will take up more drive space, so a non-clustered index that covers all of the table's columns will in effect take up as much space as the table itself. A clustered index is the table.


Security implications of restoring a backup from an unknown source?

A database may contain malicious code, possibly a procedure that is going to change a password for the "sa" login or drop every database. However the only way that I can see that causing an issue is for an individual to restore the database, and then manually execute any code within that database. It would not execute in any automated manner.

There is no setting that can be applied within a database to have SQL Server automatically execute some bit of code within the database upon restoring it to a server. If it did I would expect Microsoft would loose the Common Criteria certification for the product. That is to great of a bug to have allowed in a DBMS to me.


Avoid circular reference with configuration tables

You can solve this problem by either removing ProjectName from Inventory (since it is redundant because it is already specified in ProjectCategory), or enforce the relationship via a foreign key:

CREATE UNIQUE INDEX ON ProjectCategory (ProjectCategoryID, ProjectName);

ALTER TABLE Inventory ADD
  FOREIGN KEY (ProjectCategoryID, ProjectName) REFERENCES (ProjectCategoryID, ProjectName);


what is the penalty for NOT setting Disconnect users in the database when restoring backups?

From this page in the MS documentation:

  There are two options for configuration when you place the secondary
  database in standby mode:

  You can choose to have database users disconnected when transaction
  log backups are being restored. If you choose this option, users will
  be disconnected from the database each time the log shipping restore
  job attempts to restore a transaction log to the secondary database.
  Disconnection will happen on the schedule you set for the restore job.

  You can choose not to disconnect users. In this case, the restore job
  cannot restore transaction log backups to the secondary database if
  there are users connected to that database. Transaction log backups
  will accumulate until there are no user connections to the database.

So yes, it could be hours, days, weeks, or years  without a restore if users are still connected to the secondary.


Truncating table without using Alter Table permission

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila.

There are basic issue:

table names are NVARCHAR, not VARCHAR
table names are length 128, not 60
sysname is a handy type to represent object names, is an alias for NVARCHAR(128)
tables are qualified by schema and name, not by name only
procedures that build dynamic SQL must properly quote the names, using QUOTENAME

Lets try again:

 CREATEPROCEDURE [dbo].[spTruncate]
    @schemaName sysname,
    @tableName sysname
 WITH EXECUTE AS CALLER
 AS
 SET NOCOUNT OFF;
 DECLARE @QUERY NVARCHAR(max);
 SET @QUERY = N'TRUNCATE TABLE ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName );
 EXECUTE sp_executesql @QUERY;

No need to use execute as OWNER. As CALLER is fine, as long as the procedure is properly signed. Ask you DBA to sign the procedure following the standard procedure. See Module Signing and Signing Stored Procedures in SQL Server. Your DBA should know how to do this.


Modeling discounts - Product specific and Customer specific discounts

You should have a table for each class of thing you want to offer a discount on.  From your question that would be a table each for products, customers and stores.  Most likely you will have these tables in your design already. Each will have a column of an appropriate type to hold the discount. You don't say what you will offer but a percentage discount could be a TINYINT UNSIGNED since it can only be in the range 0 to 100 or a DECIMAL if you prefer to handle your math that way, and so forth if you'll offer fixed-amount discounts ("$10 off your purchase over $120. This month only!"). Name your column to match your rule e.g. DiscountPercent or DiscountAmount.  If it is important to track changes to discounts over time separate tables keyed by date will be required e.g. CustomerDiscount (CustomerID, StartDate, EndDate, DiscountPercent).

Be sure to work out with your business how these rules interact.  If I'm a 5% customer buying a 6% product do I get an 11% discount or a (0.95 * 0.94) price?  For a $100 item will I be charged $89.00 or $89.30? Can I even get more than one discount on a single purchase? Which?

If only a few combinations receive the discount you could have an intersection table keyed by, say, product and customer as you mention in your question. This works up to a couple of levels then gets very unwieldy. Then a general decision table will be your best bet. This will have a column for each of your decision variables (product, customer and store in your examples) and another for precedence/ sort order. You manually assign the precedence to match your rules and at run time pick the highest precedence which matches this sale's parameters.

An example:

Store      Product  Customer  Precedence  Discount%
Sao Palo                               1          2
Melbourne                              1          2
           Foo                         2          3
           Bar                         2          3
                    Chris              3          5
                    Sam                3          5
           Foo      Sam                4          7

Sam buys some Foo in Melbourne.  She gets 7% off since that's the row with the highest Precedence that matches all the criteria (or has a NULL). Anything else Sam buys has a 5% discount. Anyone else who buys Foo gets 3% off.

It is usual to copy relevant values to the Sales table for each transaction. This gives you
a) a complete audit history, which accountants like
b) flexibility to award non-standard discounts.


Why is turning on Event Scheduler Maxing my CPU?

The answer actually has turned out to be a bug which is reproducible - Bug#: 100449


Permissions to Create DB's and be able to perform any function only on those DB's

I think you need to clear up the distinction between logins (security entities at the server level) and users (security entities at the database level, which may be associated with a server-level login).

What you want to do here is grant CREATE ANY DATABASE to the server-level login. For example:

CREATE LOGIN foo WITH PASSWORD = N'bar', CHECK_POLICY = OFF;
GO
GRANT CREATE ANY DATABASE TO foo;
GO

Now, log in as foo in another window, and note that they can do this:

CREATE DATABASE foo_1;
GO
USE foo_1;
GO
CREATE TABLE dbo.bar_1(foo_what INT);
GO

But as soon as they try to use a database they did not create:

USE AdventureWorks2014;
GO

They are blocked:

  Msg 916, Level 14, State 1
  The server principal "foo" is not able to access the database "AdventureWorks2014" under the current security context.


How to grant user to dbms_crypto ?? Oracle (User which created inside SYSTEM)

In your first attempt it would appear that the unknown user issuing the command doesn't himself have access sys.dbms_crypto.  In the second case, privs are GRANTed to either users or roles.  Neither users nor roles are themselves part of some other schema, as suggested by your qualification of "DB_Project" with the schema SYSTEM is invalid syntax for a GRANT.

Also beware that you have enclosed "DB_Project" in double-quotes.  This will force case sensitivity on the name, which is a Very Bad Practice in oracle.  If you create an object name enclosed in double-quotes, you will always and forever have to refer to in in double quotes and the exact case in which it was created. Create the name without double-quotes and it will be case INsensitve .. you will always be able to refere to it in any case you want, as long as you do not enclose it in  double-quotes.


Stuck with self-referencing linked server - will replacing the primary server name be better?

A linked server even if it links to itself is still a linked server with all of the pain points that entails (as you outlined in your question). I would either stand up a new server/instance with the name that you had installed and run the database there without the linked server, or get the app to reinstall with the actual server name.

Having an Alias does make things simpler - mainly that you can move the database around without the app knowing - but if it's going to do every query through a linked server then benefit is outweighed (in my opinion) by the cost.


Splitting data into two tables, in one go

If you use MERGE to insert the data into T2, you can generate a mapping table between T1.ID and T2.ID:

DECLARE @Mapping TABLE
(
  T1ID int,
  T2ID int
);
MERGE INTO
  dbo.T2 AS tgt
USING
  dbo.T1 AS src
ON
  1 = 0
WHEN NOT MATCHED THEN
  INSERT (    Col1,     Col2,     Col3)
  VALUES (src.Col1, src.Col2, src.Col3)
OUTPUT
  src.ID, inserted.ID INTO @Mapping (T1ID, T2ID)
;

Unlike INSERT, which would let you reference the inserted table's columns only in the OUTPUT clause, the MERGE statement allows you to reference the source table's columns as well. That is key to this solution, because that is how you associate the source IDs with the target IDs.

Once you have the mapping table, you can just use it in a join when inserting into T3. A plain INSERT ... SELECT will do this time:

INSERT INTO
  dbo.T3 (T2ID, Col4, Col5, Col6)
SELECT
  m.T2ID,
  t.Col4,
  t.Col5,
  t.Col6
FROM
  dbo.T1 AS t
  INNER JOIN @Mapping AS m ON t.ID = m.T1ID
;

Wrap both statements in one transaction to make your split operation atomic.

Discussion about the MERGE mapping method can be found in this thread:

Using merge..output to get mapping between source.id and target.id


Mariadb 10.1.13 - log positions similar but "seconds behind master" is behind

140625151 and 138884970 are not that close.  But 10056 seconds is a nearly 3 hours.  What query is the Slave executing?  Maybe a long-running ALTER?

There have been cases where Seconds_behind_master bounces between 0 and some large value; is that happening?


If going down the view approach which would limit disk IO, be more normalized and MAY perform better(depending on the use of the value), it would look something like this;

CREATE VIEW [newviewname]
AS
    SELECT CAST(RIGHT(Year,2) AS VARCHAR(2)) + Office + ROW_NUMBER() OVER (PARTITION_BY Year, Office ORDER BY Record))
    FROM [your table]

Also, you may want to wrap the concatenated values with ISNULL() if any of those values might actually return NULL.  A NULL value concatenated with anything else will always return NULL.

A trigger will actually be written very similarly except would need to be updated each time a value in the table is updated.  Unless there's some performance or data structure related need, I would avoid the trigger approach.
Either way for a "correct" answer we would need to know your objectives. How do you intend to use the generated value.
BTW, this answer assumes you are using MS SQL.


Oracle 12c; cannot connect to HR without running ALTER PLUGGABLE DATABASE ALL OPEN

Check out this from Oracle-Base about starting and stopping pluggable DBs.

First, they provide a trigger to auto-start your PDB:

CREATE OR REPLACE TRIGGER open_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/

And from Oracle 12.1.0.2 onward, we have this command to have Oracle restart the pluggable DB in whatever state it was in at that time (so open the PDBs then run this for each):

ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

But see the link for full details on both examples.


Use UNSIGNED to squeeze out an extra bit.  A 3-byte MEDIUMINT UNSIGNED gives you 0..16M; a 4-byte INT UNSIGNED gives you 0..4 billion.  Since we are talking about X and Y, what is the max width/height of the maze?  16M or 64K seems more realistic than 4B.

Consider using a TINYINT UNSIGNED or SET to handle the 4 "open" flags.  That would take 1 byte instead of 4.  Don't you want to go for 3 dimensions?  Why not 4?  Or string-theory with 10 or 11.  (That would take 2 bytes for all the possible openings.)

Smaller --> faster, especially since this may become I/O bound.

Do you really need FOREIGN KEY and cascading delete?

I don't think MySQL needs for you to break it into 'chunks', but if you do, use suitably sized ints, such as the 2-byte SMALLINT UNSIGNED (0..65535).

Perhaps you are optimistic to think there will be more than 4 billion "messages"?  Do you have a terabyte-sized disk?

I can't really advise on the structure, nor the indexes, without seeing the queries.  I would "guess" that you always say WHERE x=... AND y=...; that is, specifying both?

Is someone (a human, that is) going to wander through this maze?  Even at one 'step' per second, a human cannot realistically touch more than 10M rooms in a year.  And such a person would probably need a padded cell after that.


Installing SSIS package outside of SSIDB catalog

If VS doesn't have access to the remote server, you can build SSIS package, upload it to the remote server and install using SSMS.

Build SSIS project to create IPSAC file in BIN folder
Copy IPSAC file to the remote server
Go to the remote server => SSMS => Integration Services Catalog => create new folder
Created folder will have 2 sub-folders, one of them is Projects
Click right mouse button on the Projects folder and choose option Deploy
In the popup window, choose to deploy project and point it to IPSAC file


Unless I'm misunderstanding your requirements, the Person, Individual, Company, and User tables could be combined into a single record since the relationships between these tables are all one-to-one.  Unless this is some kind of trick question...

One could go so far as to say all the tables that involve one-to-one relationships could be combined into a single table since that would eliminate the duplicate foreign keys, thereby saving space.  Having said that you'd really have to be hard pressed for space to worry about saving that small an amount of space.

It looks to me like you need to revisit your design spec with an eye for simplification.


Backup all privileges related to specific user of MySQL

Capture the output from

SHOW GRANTS FOR 'u_1'@localhost;
SHOW GRANTS FOR 'u_1'@'%';

(and any other 'hosts' involved.)

Then replay GRANTs to establish hist credentials elsewhere.


SQL Server TempDB on Local SSDs with VMs

You have to specify the same local storage paths on both nodes for the TempDB in order for the nodes to fail over properly.


Is there a way to find out how many times a stored procedure was invoked since last DB start

If you can take the hit on performance, enable tracing for a "typical" session (set tracefile "/some/path/" for some_spid), and using set show_sqltext on you can see all the SQL executed. Then you will need to parse the output looking for stored procedures. Of course this approach cannot give you 100% confidence (e.g. maybe you have a batch job that runs once per year) but it's a start.


How does the sys.objects Catalog View pull from itself?

sys.objects is not a table, it is a system catalog view. The definition shown in the question even shows Create VIEW .... Also, sys.objects is not the same thing as sys.objects$:

You cannot select from sys.objects$ on a regular connection. You need to be on a DAC connection, though not necessarily in Single-User mode, to SELECT from it.
They don't have the same number of columns. Just connect to the DAC connection and run the following:

SELECT TOP (1) * FROM sys.objects;
GO
SELECT TOP (1) * FROM sys.objects$;
GO

sys.objects$ must be an internal catalog view since the underlying fields in sys.objects (no trailing $) come from these tables: sysschobjs, syssingleobjrefs, syspalnames.


Error converting data type varchar to numeric

It's because the first parameter of the REPLICATE function is a VARCHAR, not int. Please see the documentation here.
Instead of doing REPLICATE(0, 4) you'll want to do REPLICATE('0', 4) etc.
The error itself can be a little confusing when it says the word convert, because it doesn't necessarily mean the CONVERT function, rather it's trying to do implicit conversion of the INT value 0 to a VARCHAR value since that is the data type of the parameter for the REPLICATE function. This error can occur for any function that expects a certain data type for its parameter but is passed in a value that's a different data type that is unable to be implicitly converted.
An example of implicit conversion that would work, is if a function had a parameter that was of type BIGINT and you passed in an INT value like 13 instead.


Does a database backup reduce size/increase free space of the log file?

Got a SQL database and the log file is massive and keeps growing every day.

Log files growth depends on Recovery Model of a database and transaction log backups.
if database is not critical and data loss is not an issue in any case then it should be in SIMPLE recovery mode. Which allows SQL Server to reuse log space when a transaction is committed and log growth remains in limits.
But if database is critical and point in time recovery can be a requirement then database must be in FULL Recovery mode. Which is an indication for SQL Server that keep all the log in transaction log file and don't reuse, until it is backed up (transaction log backup).

  I've archived data from the database and then done a full backup...I
   was expecting the log to then be basically "empty". e.g.* Full backup or even Transaction Log backup don't left log file empty.
  Transaction log backup only mark log file space to reusable but still
  file size remains same.

If archiving means you have removed some data after fetching some data and keeping it in any other format. Then one must keep in mind that deleting data never reduce physical size of data or log files, though space could be available for reuse by database.


SQL installation fails with "Could not find the Database Engine startup handle". Tried almost all suggestions from internet, but no luck

Answering my own question.
Note: It is not a solution. It is not applicable to any professional environment.
I did a PC factory reset. Deleting all personal apps and files. Then installed it. It installed without any problem. Earlier, I reset the pc but did not remove the files. The installation of SQL had still failed.
My suspicion is that there was some problem with the log on account in my system. But I still do not know the root cause


Primary key addition for a table which contatins no data is too slow

You are running in Windows, correct?  Perhaps an older version of Windows and an older version of MySQL?  File operations are slow.  ALTER TABLE, especially when changing the PRIMARY KEY, requires creating 1-3 files, copying over some stuff (not much in your case), removing the old 1-3 files, then renaming the files.

ALTER on an empty table is not a priority.  However, some improvements were made in 5.6 on Windows.  Still, I suggest that Windows file management is the main culprit. Your code may have added up to 27 system calls for file manipulation.  15 seconds seems about right for Windows.

You could speed the process up by combining the statements:

ALTER TABLE Table1
    DROP PRIMARY KEY,
    ADD Column3 INT(10) DEFAULT '1',    -- (I fixed the syntax error here)
    ADD PRIMARY KEY Table_PK(Column1, Column2 , Column3 );

With few exceptions, you can combine multiple ALTERs for a single table into a single ALTER.  And, unless all can be run with ALGORITHM=INPLACE, it is faster to combine them.

Consider using INT UNSIGNED for Column3.  And/or some smaller type of INT, such as SMALLINT.


Having dealt this issue, the most effective and performant way is to create a CLR function that calculates the Levenshtein Distance. You will be able to mark the assembly as SAFE (if you're at all concerned about security), and it runs much quicker than SOUNDEX() or any in-built SQL Server functions.

Here's the code to set up the assembly and function in the database, as well as a basic version of the Levenshtein Distance algorithm implemented in C# from https://en.wikibooks.org/wiki/Algorithm_Implementation/Strings/Levenshtein_distance#C#

C#:

using System;
using System.Security.Cryptography;

namespace LevenshteinDistance
{
    public class LevenshteinDistance
    {
        private int LevenshteinDistance(string a, string b)
        {
            if (string.IsNullOrEmpty(a))
            {
                if (!string.IsNullOrEmpty(b))
                {
                    return b.Length;
                }
                return 0;
            }

            if (string.IsNullOrEmpty(b))
            {
                if (!string.IsNullOrEmpty(a))
                {
                    return a.Length;
                }
                return 0;
            }

            int cost;
            int[,] d = new int[a.Length + 1, b.Length + 1];
            int min1;
            int min2;
            int min3;

            for (int i = 0; i <= d.GetUpperBound(0); i += 1)
            {
                d[i, 0] = i;
            }

            for (int i = 0; i <= d.GetUpperBound(1); i += 1)
            {
                d[0, i] = i;
            }

            for (int i = 1; i <= d.GetUpperBound(0); i += 1)
            {
                for (int j = 1; j <= d.GetUpperBound(1); j += 1)
                {
                    cost = (a[i-1] != b[j-1])? 1 : 0;

                    min1 = d[i - 1, j] + 1;
                    min2 = d[i, j - 1] + 1;
                    min3 = d[i - 1, j - 1] + cost;
                    d[i, j] = Math.Min(Math.Min(min1, min2), min3);
                }
            }
            return d[d.GetUpperBound(0), d.GetUpperBound(1)];
        }
    }
}

T-SQL:

use [master];
go

exec sp_configure 'clr enabled', 1;
go
reconfigure with override;
go

use [database_name];
go

-- Drop the function...
if exists (select 1 from sys.objects so where so.[name] = 'LevenshteinDistance')
    drop function dbo.LevenshteinDistance;
go

-- ...then the assembly
if exists (select 1 from sys.assemblies sa where sa.[name] = 'LevenshteinDistance')
    drop assembly [LevenshteinDistance];
go

-- Now load the assembly from an appropriately accessible location
create assembly [LevenshteinDistance]
from
    'd:\LevenshteinDistance.dll'
with
    permission_set = safe;
go

-- Create an asymmetric key from the assembly file
use [master];
go

if not exists (select 1 from sys.asymmetric_keys ak where ak.[name] = 'LevenshteinDistanceKey')
begin
    create asymmetric key LevenshteinDistanceKey
    from executable file = 'd:\LevenshteinDistance.dll';
end
go

-- Create a user to associate with the assembly from the asymmetric key, and then
-- revoke connect access. The login is used to execute the assembly.
use [master];
go

if not exists (select 1 from sys.server_principals sp where sp.[name] = 'LevenshteinDistanceKeyUser')
begin
    create login LevenshteinDistanceKeyUser from asymmetric key LevenshteinDistanceKey;
    revoke connect sql from LevenshteinDistanceKeyUser;
end
go

grant external access assembly to LevenshteinDistanceKeyUser;
go

use [database_name];
go
alter assembly [LevenshteinDistance] with permission_set = safe;
go

-- Create the SQL function which will be called
create function [dbo].LevenshteinDistance
(
    @string1 nvarchar(2048)
    ,@string2 nvarchar(2048)
)
returns nvarchar(max)
as
    external name LevenshteinDistance.[LevenshteinDistance.LevenshteinDistance].LevenshteinDistance;
go


pg_ctl must call the wrong postgres binary.

PostgreSQL version 9.2 had a parameter unix_socket_directory, which does no longer exist (there is unix_socket_directories now). So a 9.2 server would never die with this message.

Are your PostgreSQL 9.2 binaries really installed in /usr/bin or is the pg_ctl there just a symbolic link or a script?

Try to find the directory where the postgres executable from the 9.2 installation is located and use that directory with the --old-bindir of pg_upgrade.


Great! Your database is working precisely as it should!

SYSDBA has the power to do just about anything to the database, including creating objects, dropping objects, etc, blah.

SYSOPER has a reduced set of privileges which allow such users to perform basic operational tasks but not to look at user data.

Share and enjoy.


optimizing MySQL for traffic analytics system

You can simplify your query to something along these lines. I expect MySQL will generate a simpler execution plan.

SELECT date(date) period, count(*) clicks
FROM visits
WHERE url_id = 3
  AND DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH )
GROUP BY period;

If returning results on high-traffic links in one second is a hard requirement, you might need to upgrade your hardware.

Expect to need an index on each column used in a WHERE clause. You might benefit from some multicolumn indexes; {url_id, date} is a candidate.

Test at scale, if that's at all possible. (It's usually possible, although it might take some time.) Use EXPLAIN to see what MySQL is doing with your queries.

You don't have to query browsers, countries, and everything else all at one time. When I was running web development, I rarely looked at countries--they weren't relevant to the niche I was working in. Also consider other asynchronous UI technologies.

PostgreSQL has a better optimizer. It might work better than MySQL. Test it.


Random row selection but with distinct values for a subset of columns

MariaDB 10.3 supports window functions, so something like this would work:

select * from (
  select
     t.*, -- all columns
     row_number() -- assign sequential numbers
     over ( -- within a "window"
        partition by k1, k2 -- determined by a unique combination of k1, k2
        order by rand() -- while ordering rows randomly within the partition
     ) as rn -- set the column alias
  from test t
) tt
where rn = 1 -- select only the first row from each "window" (partition)
order by k1, k2, k3

dbfiddle


Cannot log in to Mongo from command line - using MongoDB 3.0 and WiredTiger

I've had the same authentication issue on an MMS deployed db (though it has nothing to do with it).

it tries to connect to /test by default. If you want another database, just specify it, e.g. 102.216.73.164/foo.
upgrade your mongo shell to 3.0.3 (you currently use 2.6.6). That solved the problem for me.


How to set/get a field of a nested composite type variable?

There's one (awful, in my opinion) workaround... you can access only one level down at a time. Use intermediate variables.

DO LANGUAGE PLPGSQL
$$
DECLARE
    x t3 ;
    x2 t2 ;
    x1 t1 ;
BEGIN
    -- x.a.a.c := true;   -- Syntax error

    x2 := x.a ;
    x1 := x2.a ;
    x1.c := true ;
    x2.a := x1 ;
    x.a := x2 ;
END ;
$$

dbfiddle here


Does the collation of ENUM columns impact their size?

No difference.
ENUM is stored as a 1- or 2-byte number, where 0 maps to the first item in your list.  You deal with the column as a string, but the disk only sees a number.
2-bytes is used only when there are more than 255 options.  There are some who shun the use of ENUM altogether; there are some who frown on more than a few options.  I say use ENUM for a small, unlikely to change, list of options.
It may be possible to get charset or collation problems if you aren't consistent in database and connection specifications.


I can answer the distance part, but I've never used geoNear with Mongoose I'm afraid - a good way to tell if the issue lies in how you are calling things in Mongoose versus the query itself would be to try it on the mongo shell first to verify that it is returning the results you expect there.

In terms of the distance, for legacy 2d points (which you appear to be using), the $maxdistance is specified in radians - there is a page here which outlines how to use radians with spherical geometry calculations.  If you use GeoJSON instead (which I would recommend), then it is specified in meters (reference).


You can check this only if you are monitoring this.

You can also check for problems at a certain time, if you see in SQL Server Log waiting to perform IO operation.

I would start by doing all the Glenn Alan Berry scripts. https://sqlserverperformance.wordpress.com/2016/06/08/sql-server-diagnostic-information-queries-for-june-2016/

You can run a perfmon together with a sqlprofiler and look for the statements that are causing problems (this will give you hardware an query information which you will have to combine)

What I usually get my customers to use is SQL Sentry(SentryOne) this is a montoring tool which has a month free trial. This will show all information you need correlated to queries in an easy to understand GUI. This however is not the only solution, but it is the one I prefer.


PERCENT_RANK does not distribute over 100


When the ER (Entity-Relationship) model was devised in the 1970s, it was specifically intended to be implementation neutral (sometimes called "implementation agnostic").  The relational model, by contrast, was tilted towards a relational design and implementation.

In classical three stage methodology, an ER model is devised to capture the data analysis of the subject matter.  You could implement the database as relational, or OODB, or unstructured, or whatever. This is called the conceptual model.

The next model is a relational model, and captures the logical design.  This is tilted towards a class of Relational DBMS products that includes Oracle, SQL Server, and many others.  This is called the logical model.

The third model is a detailed model of the proposed database, and captures features that are DBMS specific, like indexes, and table spaces.  This is called the physical model.

Very often the logical and physical models are merged together, because the DBMS has already been chosen, and they are both design models.


Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list?

Add a column list to your code:

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON

INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
    (ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable)
SELECT ColumnWithIdentity, OtherColumnWithoutIdentity, LastColumnInThisTable
FROM ALinkedServer.PrettyCoolDatabaseAsWell.StandardSchema.TableWithIdentityColumn

SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn OFF

GO

Basically, a column list is when you explicitly state what columns you're inserting into.

Just remember that when you set IDENTITY_INSERT ON, you must specify in your insert statement the value for the column that is the identity
(in your case the column ColumnWithIdentity).
If IDENTITY_INSERT OFF, SQL Server sets the first free value for the identity column, and so you must not add the ColumnWithIdentity field in the SELECT part of your INSERT.


psql: Could not connnect to the server: No such file or directory

Alright, I found what was wrong with all of this.

First, by running /usr/bin/pg_lsclusters I could see that the cluster was indeed not online. Then I checked the /var/log/postgresql/postgresql-9.5-main.log file to look up what was the specific error that had caused the cluster to not be brought up and there I could see that my ph_hba.conf file was, somehow, impossible to read.

I don't know how or when the permissions changed but they did. I chmod 644them and now everything is awesome.


Remove/suppress dashes from "Results to Text" output in SSMS?

Those dashes are separators between the column headers and the results, since the columns in your example have no names, that isn't immediately obvious. You can see that better here:
SET NOCOUNT ON;
SELECT ServerName = @@SERVICENAME;
SELECT VersionInfo = @@version;

To suppress the column headers:

Go to the Tools menu then select Options
Navigate to Query Results >> SQL Server >> Results to Text
Uncheck the box for Include column headers in the result set.

Note that this settings page also includes other settings that affect the results both for "Results to text" and "Results to file", including maximum data length per column (else SSMS will silently truncate), and whether results should be fixed width (the default) or delimited.


Get rows with most recent date for each different item

Skinning Q#1 independently and slightly differently than @ypercube

with cte as (select row_number() over (partition by product_id,
                                       invoice_id
                                 order by dt desc) as rn,
                    product_id,
                    invoice_id,
                    amount,dt
               from product )
select product_id, invoice_id,amount,dt
  from cte
 where rn=1
 order by product_id,invoice_id;

 product_id | invoice_id | amount |     dt
------------+------------+--------+------------
 PROD1      | INV01      |      2 | 2018-01-05
 PROD1      | INV03      |      1 | 2018-01-05
 PROD2      | INV02      |      3 | 2018-01-08
 PROD2      | INV04      |      4 | 2018-01-08
(4 rows)

For Q#2, you are on the right track, but the SQL will have a cross join(gasp!)

I think a function with a loop/cursor would be more optimized (i'll try that in my next free block of time)

--the cte will give us the real values
with cte as (select product_id,
                    sum(amount) as amount,
                    dt
               from product
              group by product_id,dt)
select p.product_id,
       (select cte.amount --choose the amount
          from cte
         where cte.product_id = p.product_id
           and cte.dt <= d.gdt -- for same day or earlier
         order by cte.dt desc
         limit 1) as finamt,
       d.gdt
from (select generate_series( (select min(dt)
                                 from product), --where clause if some products
                                                --don't have an amount
                              (select max(dt)
                                 from product),
                              '1 day'
                            )::date as gdt)  d
cross join --assuming each listed product has an amount on the min date
     (select distinct product_id
        from product) p
left join --since we need to fill the gaps
     cte on ( d.gdt = cte.dt
             and p.product_id = cte.product_id)
order by d.gdt, p.product_id
;


Look into BCP or BULK INSERT, both of which can share format files.

Quick math based on pure guesses, since no hard details are given:

13 numeric columns, pretend they're 6 bytes each on average = 13*6=78 bytes.

5 VARCHAR columns, pretend they're an average of 300 bytes each based on the "largest" being VARCHAR(1000) per the original question = 5*300 = 1500 bytes

1500 + 78 = 1578 bytes per row average.

1578*10000000000/(1024*1024) = 15048981 Megabytes per day

15048981/(24*60*60) = 174MB/s EACH WAY (once from the source and once to the database) if you load it continuously, 24 hours a day, rain or shine, during index maintenance, during backups, during DBCC CHECKDB and OS level disk defragmentation.

If you have 7 VARCHAR fields at an average of 800 bytes each, that's 40 times the bandwidth requirements of 5 VARCHAR fields at an average of 20 bytes each; the latter can be done over gigabit ethernet, the former cannot given your 10 billion rows a day, every day criteria.

Figure out what your data sizes actually are, and what your time windows actually are, and then do the math on how fast you need it to go for real.

If you have to, you can try disabling or removing triggers, traces, constraints, foreign keys, indexes, etc. on whatever table you're putting this data into.

And watch out for backup space requirements as well :).


I was fighting a similar problem today with some slow SQL queries generated by WooCommerce in WordPress and I managed to resolve the issue by creating the following two indexes:

alter table wpn0_posts add index woo_index1 (post_type, post_status, id);

alter table wpn0_postmeta add index woo_index2 (post_id, meta_key, meta_value(8));

In my case, however, the query was always looking for IDs in the longtext field:

  WHERE meta_value     = '15358'....

That is why the index works in my case for my specific queries. I hope this helps you :)

Edit: You may also check the following question I posted today and I managed to optimize my query by adding indexes:

How to optimize this specific MySQL query which examines 2.84M rows and 29.49k InnoDB distinct pages


Does SQL Server upgrade to table locks at total locks or session locks?

If the lock count heuristic is per-session then it is less of potential issue.

It is per-session.

Are there any other relevant database properties to consider with respect to lock escalation?

On the database, no, but you can disable lock escalation on the whole instance by enabling trace flag 1211.

what is the “best” way to prevent table lock escalation here?

Why would you want to?

Lock escalation serves a useful purpose in SQL Server by maximizing
the efficiency of queries that are otherwise slowed down by the
overhead of acquiring and releasing several thousands of locks. Lock
escalation also helps to minimize the required memory to keep track of
locks. The memory that SQL Server can dynamically allocate for lock
structures is finite, so if you disable lock escalation and the lock
memory grows large enough, attempts to allocate additional locks for
any query may fail

https://docs.microsoft.com/en-us/troubleshoot/sql/performance/resolve-blocking-problems-caused-lock-escalation
That article also outlines an additional way to prevent lock escalation for a time on a table by running (from a seperate session) a query like:
BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN


Hierarchical data using closure table, how to ensure that a column is unique within the parent node?

I'm assuming (from the fact you considered adding a parentId column) that each Node has a single parent.
Two things to do:

Add parent column to Node, with a self-referencing foreign key to the nodeID column.  This ensures that each Node links to a single parent Node.  This should be null for the root node.
Get rid of NodeClosure table.  Seriously, you don't need it.

So your design becomes simply:
Node

nodeID (int not null, autoincrement, primary key)
parentID (int null, foreign key to Node(nodeID))
name (text not null)

Now you can indeed enforce unique names among siblings by adding a unique constraint on parent + name.
You can derive the ancestor/descendent relationship and level that was previously in NodeClosure using queries against the Node table.  Syntax for this varies depending on database.


Need Help Finding a Replacement for sys.sysprocesses

Unfortunately, Microsoft is aware of the lack of replacements. You can upvote & watch these Connect items for more details:

Deprecation of sysprocesses - DMV's doesn't fully replace all columns - by Tony Rogerson SQL

There is no real alternative to master.dbo.sysprocesses - by GV1973


It sounds like you'd be best suited by using a physical base backup + WAL archiving, with regularly updated snapshots of the base backup. I strongly recommend taking regular dumps anyway.

Using newer PostgreSQL versions (9.2 and up, IIRC) you can take fresh base backups from a replica server so you don't have to disrupt the master.

File-system or logical volume level snapshot backups work fine with PostgreSQL so long as your snapshots are atomic. Restoring one is like starting PostgreSQL back up after unexpected power loss or OS reboot, not a big deal.

See also:

The manual
A blog entry I wrote on this topic
Postgres Backup queries on AWS after reading Postgres docs
PostgreSQL: Can I do pg_start_backup() on a live, running db under load?
PostgreSQL "freeze"/"unfreeze" command equivalents
How to do incremental backup every hour in Postgres?
Running pg_dump on a hot standby server?
PostgreSQL crash recovery


If you are confident that there can only ever be at most two rows for any given customer, then you can do this:

;WITH x AS
(
 SELECT id,customer,os,ms,st,
  previous_os = LAG(os,1,NULL) OVER (PARTITION BY customer ORDER BY id),
  previous_ms = LAG(ms,1,NULL) OVER (PARTITION BY customer ORDER BY id),
  previous_st = LAG(st,1,NULL) OVER (PARTITION BY customer ORDER BY id)
 FROM #Booberry
)
UPDATE x SET
  os = COALESCE(os,previous_os),
  ms = COALESCE(ms,previous_ms),
  st = COALESCE(st,previous_st);

But I don't know how you can be so sure. This won't work very well if you have a case where there are three rows for a customer, the most recent row is all NULLs, the one before that has NULL for st, and the one before that has NULL for os. I think we need better requirements and how you want the results to look in the event of edge cases.


1/ The first I have in mind is that time is spent on checking FK constraint in your destination table while new rows are inserted. Firstly, I would check if referenced tables have PKs defined (or even clustered from performance point of view). Give it a try by performing some bulk inserts after you have disabled those FKs.

2/ Statistics might not be updated on referenced table, case when a statement like this should be executed for each referenced table:

UPDATE STATISTICS tablename;

3/ Long term, you should take into consideration partitioning that table. This is the perfect scenario for doing this.

4/ Disable indexes on destination table, perform bulk insert then re-enable the indexes. Not-recommended, but give it try to see the results and to see if this is the root cause of your poor performance for bulk inserts.

5/ Have a look at waiting threads while a bulk insert is executing.

6/ Post the result of wait statistics in order to see which types of waits are commonly arrive on your server.

Tell us the result and we'll come with more details depending on the context.


Oracle: How can I create a where clause programmatically?

PL/SQL Dynamic SQL

Below is a simple example:

set serveroutput on

declare
  l_condition varchar2(100);
  l_rc sys_refcursor;
  l_record sample%rowtype;
begin
  select where_clause into l_condition from tbl_conditions where id = 3;
  open l_rc for 'select * from sample where ' || l_condition;
  loop
    fetch l_rc into l_record;
    exit when l_rc%notfound;
    dbms_output.put_line(l_record.column1 || ', ' || l_record.column2);
  end loop;
end;
/


What would I need to do to make sure my DB policies have to pass a security audit?

I applaud your effort, however the question asked is probably too vague. Different rules apply to different industries and furthermore, sometimes you have the opportunity to set your own rules, you just need to follow them.

I would suggest checking with someone as to what audit you are expected to pass, then finding the specific requirements.


How to maintain customer addresses for Orders?

You have addresses in 3 tables.  Shrink that down to 1.  Then have a customer_address_id in the Customers table and ship_address_id in the Orders table.  Then, ship_address_id can be nullable.  Do not make all the fields in Addresses Nullable.  (Perhaps region and/or post_code can be left blank -- but make them DEFAULT '' instead of NULLable.

If a Customer is 'shipping' to himself, simply copy the customer_address_id into Orders.ship_address_id.

One more thing...  In order to not lose "old addresses", be careful about when (if ever) you change any row in Addresses.  You may end up with unused entries, but that is a small price to pay for the data integrity you require.


How to update column with ascending value column

You can use ROW_NUMBER() window function:

WITH x AS
(
  SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name) ord
  FROM
      tbl
)
UPDATE
    tbl
SET
    ord = x.ord
FROM
    x
WHERE
    x.id = tbl.id;

SELECT * FROM tbl;

id | name | ord
-: | :--- | --:
 1 | S4   |   4
 2 | S2   |   2
 3 | S3   |   3
 4 | S1   |   1

db<>fiddle here

If there isn't duplicated names you can remove 'S' from name:

UPDATE
    tbl
SET
    position = right(name, length(name) - 1)::int

db<>fiddle here


Extra fields in user_role / roles tables or a new table

I would use your current design over creating a table for each user role. Why create a new role, when your code may have to cycle through several tables to get an answer that could have come from one place.  (Opinion:  You would just be making more work for yourself.)

Of course, a Team, a TeamManager, and a TeamMember may all have some attributes that are related only to their individual roles.

If you are facing that challenge, then a supertype-subtype design can work well for this issue.  All the common data is in the supertype table, but each subtype contains data unique to that role/task/etc.  (Whatever you are trying to manage.)

A Microsoft Tutorial give a basic description at Lesson 5: Supertypes and Subtypes at https://msdn.microsoft.com/en-us/library/cc505839.aspx


What is the default size of text field in Postgres?

The default minimum size of a text column (representing a NULL value) depends on the rest of the row. In most cases it is absolutely free, if the NULL bitmap has room for another bit.

Does not using NULL in PostgreSQL still use a NULL bitmap in the header?

More about calculating the effective row size:

Configuring PostgreSQL for read performance


Delete statement not responding to Kill session command

I am at a loss as to what to do about this query to get it to rollback and also just understanding what is going on, can anyone suggest what I can look at?

DELETE FROM [Some400MRowTable]

is expensive.  Every row you delete gets logged.  And when you killed the session that huge transaction has to rollback, which is even more expensive.  So normally you just wait, and eventually it rolls back.  Your alternative is to restore from a backup.

Note that this is one of the reasons Accelerated database recovery was added in Azure SQL Database and SQL Server 2019, which provides "Instantaneous transaction rollback" where rollback cost is not a function of the number of changes made by a transaction.


Can't recycle the ERRORLOG - error 17049

From System Error Codes (1300-1699): OS error 1392 indicates file corruption.

  ERROR_FILE_CORRUPT
  1392 (0x570)
  The file or directory is corrupted and unreadable.

So it looks like you have corruption on your G: drive.

I would start with a chkdsk of that drive and all other drives.

Since corruption usually doesn't fix itself and can have the tendency to worsen, personally I would fail over to the other node ASAP before other files (I don't know what else lives on the drive) corrupt and the situation gets trickier and trickier.


Is my restore process being badly affected by this third-party non copy-only backup?

Maybe this will help you understand. Let's do a demo! We'll make a dummy database with some dummy data.

USE master;

/*Create a dummy database*/
CREATE DATABASE LogRestoreTest

/*We full now*/
ALTER DATABASE LogRestoreTest SET RECOVERY FULL

/*Context is everything*/
USE LogRestoreTest

/*If nothing changes, do we even need a log backup?*/
CREATE TABLE dbo.t1 (Id INT)

Now we'll take one full backup. Just one. Promise.

/*Take a full backup, dummy*/

BACKUP DATABASE LogRestoreTest
TO DISK = 'F:\Backup\LRT_FULL.bak'
WITH INIT, FORMAT, COMPRESSION

Now we'll make some changes and take some log backups. Just like real life. It's fun. No drinking yet.

/*Make a change*/
INSERT dbo.t1 (Id )
VALUES ( 1 )

/*Take a log backup*/
BACKUP LOG LogRestoreTest
TO DISK = 'F:\Backup\LRT_LOG_1.trn'
WITH INIT, FORMAT, COMPRESSION

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 2 )

/*Take another log backup*/
BACKUP LOG LogRestoreTest
TO DISK = 'F:\Backup\LRT_LOG_2.trn'
WITH INIT, FORMAT, COMPRESSION

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 3 )

Now we'll take an 'out of band' full backup, and another log backup.

/*A second full backup appears!*/
BACKUP DATABASE LogRestoreTest
TO DISK = 'F:\Backup\LRT_FULL_2.bak'
WITH INIT, FORMAT, COMPRESSION

/*Take another log backup*/
BACKUP LOG LogRestoreTest
TO DISK = 'F:\Backup\LRT_LOG_3.trn'
WITH INIT, FORMAT, COMPRESSION

If I want to restore data to the third log backup, I have two options.

Restore the first full backup, and all three log backups:

/*Restore the full backup*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_FULL.bak'
WITH REPLACE, NORECOVERY

/*Square one*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_1.trn'
WITH NORECOVERY

/*What about to here?*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_2.trn'
WITH NORECOVERY

/*Obligatory Your Mom*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_3.trn'
WITH NORECOVERY

Or I can restore the most recent full and then the final log file:

/*Restore the full backup*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_FULL_2.bak'
WITH REPLACE, NORECOVERY

/*What happens if I try to jump the restores?*/
RESTORE DATABASE LogRestoreTest
FROM DISK = 'F:\Backup\LRT_LOG_3.trn'
WITH NORECOVERY

You'll have to forgive me not explaining differential backups here, but there are plenty of resources out there that cover DBA 101 material.

Hope this helps!


Running a number of .sql files using SQLCMD

for /r %%i in (*.sql) do sqlcmd -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -w500  -b -r  >%%i.log  2>%%i.err

Not perfect since it creates an empty error output file when there are no errors, but a .err file with > 0 bytes will have the errors.

for %%a in (*.err) do if %%~za==0 del "%%a"

..if that's a problem...


Postgresql enabling extensions without super-user

From the docs on Extensions,

  superuser (boolean) If this parameter is true (which is the default), only superusers can create the extension or update it to a new version. If it is set to false, just the privileges required to execute the commands in the installation or update script are required.

The value isn't set in pgcrypto.control, so it's defaulting to true which requires a SuperUser.

This means you can not CREATE EXTENSION as the mere owner of the database, despite what the docs on CREATE EXTENSION lead you to believe.

I tried hard setting it to false, and no joy. C is an untrusted language and you'll get

  ERROR:  permission denied for language c

From the docs on pg_language

  Only superusers can create functions in untrusted languages.

... of course you can make c trusted with UPDATE pg_language set lanpltrusted = true where lanname = 'c'; as a superuser. Then CREATE EXTENSION pgcrypto will work fine as a non-superuser. But, that sounds like a bad idea if you have to worry about your users uploading source to your extension directory and then installing it in the database. That is to say, I wouldn't go that far. I'd find another way to skin this cat.


What would be possible reasons for a DeadLock to occur in Mysql Database?

Why DELETE+INSERT instead of UPDATE ??

Do you have BEGIN...COMMIT around the statements?

Are you hitting multiple rows in the DELETE and/or INSERT?

Are you doing DELETE ... WHERE domain_id = ... ?  If so, notice that you have no index, hence it will have to scan the whole table!

(Sorry, that does not even begin to answer the question you posed.  But it may lead to a solution to the real question.)


MySQL data warehouse calculate difference over years in percentage

Ponder a LEFT JOIN something like this

SELECT  a.year,
        a.month,
        a.total_cost,
        IFNULL(( b.total_cost - a.total_cost ) / a.total_cost ),
                'BASE') AS Diff
    FROM  fact AS b
    LEFT JOIN  fact AS a  ON  a.year  = b.year - 1
                         AND  a.month = b.month
    WHERE  b.year BETWEEN 2009+1 AND 2011+1
      AND  a.year BETWEEN 2009   AND 2011;


Oracle implemented monitoring templates a bit differently than you might expect.  When you apply a template to a database it doesn't associate the template with the database, but instead applies the template settings to the database.  This is why modifying the template after it has been applied to the database has no effect until it is re-applied to the database.

The 11.1 Oracle Enterprise Manager documentation explains how to use the "Compare Monitor Template" feature to determine how closely the template matches a particular target.  In this way you can determine if the template has been applied to the database.

  Deciding how and when to apply a template is simplified by using the
  Compare Monitoring Template feature. This feature allows you to see at
  a glance how metric and policy settings defined in a template differ
  from those defined on the destination target. Compare Monitoring
  Template is especially useful when working with aggregate targets such
  as groups and systems. For example, after you apply a Monitoring
  Template to a group, you want to verify that the group members now
  have the same monitoring settings as the template. The Compare
  Monitoring Template feature makes checking simple. You can also
  schedule this as a report, allowing you to check periodically if the
  group members still follow the template settings.


How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?

In order to make PowerShell errors cause a SQL Agent step to fail you have to make an adjustment to the $erroractionpreference setting. By default this value is set to Continue so the steps will show successful because no error is raised up for SQL Server Agent to detect an issue in the actual PowerShell command occurred.

You can put this at the top of your script that should fix that:
$erroractionpreference="stop".
This will allow the error PowerShell is returning to trickle up to SQL Server Agent process.


Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?

Sorry for the confusion, the view table is reading from the azteca.workorder table and when adding the trigger to the view table, the trigger is not working,  please find the code of  the view tables:
[dbo].[Decisions2]>>>

SELECT        REQUESTID, Communities, ObjID, [L1] AS [L1_Desecion], [L2] AS [L2_Desecion], [R1] AS [R1_Desecion], [R2] AS [R2_Desecion], [HPC1] AS [HPC1_Desecion], [HPC2] AS [SPC2_Desecion], [Drafting] AS [Drafting],
                     [Auditing] AS [Auditing_Desecion], Attachments, PROBLEMSID

FROM            (SELECT        TEXT1, REQUESTID, Communities, ObjID, Attachments, COMMENTS, PROBLEMSID
                          FROM            dbo.RequestToWorkorder) src PIVOT (max(TEXT1) FOR COMMENTS IN ([L1], [L2], [R1], [R2], [HPC1], [HPC2], [Drafting], [Auditing])) piv;

[dbo].[Con_Decisions_Groupby]>>>

SELECT        MAX(REQUESTID) AS REQUESTID, TEXT20

FROM            dbo.Con_Decisions
GROUP BY REQUESTID, TEXT20


SQL Server Identifying Connections that are utilising SQL from an application

So you want to differentiate between the app calling stored procedures vs sending T-SQL directly? Sounds like an Extended Event trace would so. Assuming the programmers call these procedures correctly from the API (and not just send a text string with EXEC procname in it).
If you capture sqlserver.sql_batch_completed and sqlserver.rpc_completed I think you will cover the submissions into SQL Server.
When an app doesn't call a proc you should either see sql_batch_completed or rpc_completed. For the later, you can check out the object_name column and if it is sp_executesql, then you have the "not calling stored procedure" case.
When it does call a proc, you will have (hopefully, if they do it right) rpc_completed with something else in the object_name column.
Depending on how sophisticated you want to be, you can play around with event_counter and the histogram targets. Or even store the trace data in a table and query that table.


I do not think that you want a computed column here. It looks like what you really need is a filtered index (introduced in SQL Server 2008), using WHERE [State] IN (0, 10) as the filter. Once this index is created, queries that have that same predicate should use this index. Please take a look at:

Create Filtered Indexes
CREATE INDEX

Just FYI: a computed column is an expression that needs to return a scalar value. State IN (0, 10) is not a scalar expression, which is why you are getting the error. You could modify it to be something like CONVERT(TINYINT, CASE [State] WHEN 0 THEN 1 WHEN 10 THEN 1 ELSE 0 END), but then indexing that will still include all rows. Using a filtered index will only include the matching rows, which will be smaller and faster :-).


Different execution plan with Rownum <NUM

By artificially limiting the number of rows returned, it's doing 90% less work (based off cardinality, bytes, etc.).

The result is that the cost-based optimizer decided to do a nested loop instead of a hash join.

The optimizer is smart enough to limit the rows returned (using table and index statistics, foreign keys, etc.) all the way up the stack, so the nested loop will be a far more efficient join for only 200 rows (hash joins are memory hungry, but more efficient at scale).


Issue with composite type in an UPSERT function

Answer
The error occurs here:
CASE tmp_code
    WHEN COALESCE(tmp_code,0)=0 THEN
Works like this:
CASE WHEN COALESCE(tmp_code,0)=0 THEN

You mixed two different syntax variants of PL/pgSQL CASE ("simple" vs. "searched") in an incompatible way.
There is another error:
update netcen.test set
test=myobj.test,
testname=myobj.testname  WHERE testkey=myobj.testkey;

Did you mean:
UPDATE test
SET    tes = myobj.tes
     , testname = myobj.testname
WHERE  testkey = myobj.testkey;

There is also no need for CREATE TYPE netcen.testobj .... You can use the table name netcen.test as type name.
You really want to "UPSERT"
In Postgres 9.5 or later use the new UPSERT (INSERT ... ON CONFLICT DO UPDATE). Like:

UPSERT with ON CONFLICT using values from source table in the UPDATE part

For older versions you can emulate an UPSERT. The simple form in plpgsql, without concurrency:
CREATE OR REPLACE FUNCTION fun_test(myobj testobj)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN FALSE;
   ELSE
      INSERT INTO test SELECT (myobj).*;
      RETURN TRUE;
   END IF;
END
$func$;

Could just be plain SQL with a data-modifying CTE:
WITH my_row(testkey, tes, testname) AS (
    SELECT 1::smallint, 'khaendra@me.net', 'khaendra'
    )
, u AS (
    UPDATE test t
    SET    tes = m.tes
         , testname = m.testname
    FROM   my_row m
    WHERE  t.testkey = m.testkey
    RETURNING t.testkey
    )
INSERT INTO test (testkey, tes, testname)
SELECT * FROM my_row
WHERE  NOT EXISTS (SELECT FROM u);

The time window for a possible race condition is extremely tiny with this form (single combined statement). If concurrency is still an issue (heavy concurrent write load), then ...
Your function reviewed
If the function returns, the row has been inserted or updated. The only other way is an EXCEPTION of a different kind. The returned value true is just noise. (Might be more interesting to return true for INSERT and false for UPDATE.) So I simplified:
CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE netcen.test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN true;
   END IF;

   BEGIN
      INSERT INTO netcen.test
      SELECT (myobj).*;  -- simpler form, parenthesis needed.

   EXCEPTION WHEN unique_violation THEN   -- cleaner
      UPDATE netcen.test
      SET    tes = myobj.tes
           , testname = myobj.testname
      WHERE  testkey = myobj.testkey;
   END;
   RETURN true;
END
$func$;

Related answers on SO:

Upsert with a transaction
Is SELECT or INSERT in a function prone to race conditions?

Blog post on UPSERT by Depesz.


Kin is correct to point you to Ola Hallengren's backup and maintenance solution.

It sounds like you're new, so also consider looking into:

SQLServerCentral's Top 6 Myths of Transaction Logs.
Paul Randall's Transaction Log blog entries
Paul Randall's Backup and Restore blog entries

Mandatory: Read A SQL Server DBA myth a day: (30/30) backup myths

Kimberly Tripp's Transaction Log blog entries

Mandatory: Read Transaction Log VLFs – too many or too few?

Aaron Bertrand is correct to point you to Why Does the Transaction Log Keep Growing or Run Out of Space?.

Usr is correct; until you test restores, assume your backups are worthless.

The commenters are also correct, you must consult with the business.

In more detail, you need:

RPO: Recovery Point Objective.  How much data loss is acceptable - i.e. restore back to within an hour before the loss?  A day?
RTO: Recovery Time Objective.  How long it takes to get the system back up again.
At least a skeleton DR plan, in particular, what types of "disasters" are your RPO and RTO good for?

corrupt database
hard drive crash
multiple drive crash resulting in RAIDset loss
accidental file deletion
server dropped during move
server stolen
building burned down
regional natural disaster (large hurricanes, earthquakes, typhoons, tsunami, major volcanic eruptions, major flooding)

Budget

After you get this, you can go back and renegotiate the previous points.

As far as a general backup plan, I would start with considering:

Search for anywhere someone's truncating the log... and stop that!  You don't want to breaking your log chain!

Likewise if you're switching in and out of SIMPLE recovery model.

Figure out where your backups need to go; backing up to the same disk spindles that hold your databases or logs is pretty pointless by itself; lose the disks, and you lose both the active database and the backups all in one fell swoop.
In all cases, set CHECKSUM on.
Full backups for everything, regularly.

Maybe this is daily, maybe it's weekly, maybe it's biweekly, maybe even monthly.
NOTE: Keeping more than one around is useful; for FULL (and BULK-LOGGED if there are no bulk-logged changes) recovery model databases, you can skip a corrupt full backup if you have a prior full backup and an unbroken log chain.

Full backups are the ONLY backup allowed for Master.

Timing on this must be at least as frequent as your RPO.

Don't forget to back up msdb too, and you might as well toss model in.

yes, model.  Sometimes it has user defined types, etc.

Differential backups on SIMPLE mode databases

Timing on this must be at least as frequent as your RPO.

OPTIONAL: Differential backups on FULL and BULK-LOGGED databases

these can allow you to have faster restores
these can also allow you to "skip over" damaged/lost/corrupt transaction log backups that are BETWEEN your full backup and a DIFFERENTIAL, after which you can continue restoring transaction logs as long as your log chain is unbroken from that point.

Log backups on all FULL and BULK-LOGGED database.

Timing on this must be at least as frequent as your RPO.
This is MANDATORY in order to keep t-log size down

Run test restores; it doesn't matter if they're on the same server or a different server, just run them.
Who has access to the backups
Encryption of the backups

Key management of the encryption

Offsite storage

And how that affects RTO

during the "larger" disasters; even so much as a blizzard or mudslide can add hours or days to this.


If you want a single result set with the first row containing a list of all users from table_a, and subsequent rows listing all ids from table_b (one per row), you could UNION the two queries. However, you would have to cast the results to a common data type (presumably something like varchar), and you would technically need to include a sort order field to guarantee that the users are in the first row.

SELECT values
FROM (select group_concat(user) as values, 1 as sort_order from table_b
      UNION ALL
      select CAST(id as varchar) as values, 2 as sort_order from table_a
     ) sq
ORDER BY sort_order
;

This will almost certainly be at least slightly slower than running the two queries separately. Not sure if that will matter, in your circumstance.

(Note:  UNION checks for and eliminates duplicate rows; UNION ALL just combines all rows without checking. Since we don't need to worry about duplicates....)


Writing to a DKNF database, but reading from a de-normalized table for performance?

Use the full normalized database, scan which queries need more performance than this model can give them and create materialized views on them. If the data is very volatile, having them fast refreshing introduces some overhead but you know the value and validity of your data, that is protected by the database. Oracle handles this very nice.

You queries remain using the normalized tables and Query Rewrite will take care of the usage of the Materialized Views, when appropriate. When the Materialized Views are not fresh, the queries fall back to the original tables.

For docu see Materialized View Concepts and Architecture