Known Good SQL Logins Failing (Error 18456) Accompanied by “Unable to Reuse a Session” (Error 18056) I am losing my patience with this problem.

I did see a couple of other questions here that are similar, but I've already looked at the fix actions that resolved their issues and they did not apply to this scenario.

I'm running SQL Server 2008 R2 with all updates. When this particular issue occurs my servers lose all connection ability to the server and it has to be restarted.

It is very intermittent. Every now and again this SQL server instance will report the error: Login failed for user 'XX'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection. Error: 18456, Severity: 14, State: 46.

I know this is a good login, it works all of the other times, and no one changes the credentials.

The above error is immediately followed by this error:

The client was unable to reuse a session with SPID 150, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

Error: 18456, Severity: 14, State: 46.

When this occurs a few minutes later this is reported also:

Message All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 0%.

The above message just keeps repeating until I restart the server.

From researching this issue I've increased the maximum worker threads (currently at 1100). The server has 72G memory and 4 vCPUs, running Windows Server 2008 R2.

I don't encounter this issue at all on my other database servers that run the same environment. Just this one. Any help would be greatly appreciated, I'm running out of options.

This is a guess.

It may be network errors. A client process will open a connection to the server, query and then typically close the connection. A few seconds/minutes later it does it again. Opening a connection is an expensive operation, so Connection Pooling was invented. With Connection Pooling, when the client process closes the connection, the connection is not actually closed: the object in the application representing the connection is invalidated and cannot be re-used, but the actual connection to the DB is preserved. When the process tries to open a new connection to the DB, the pooled connections associated with the process are searched for one with the same connection string; if found that connection is re-used avoiding the overhead of creating a new one. If one isn't found then a new one is created from scratch.

If during a query the network burps the actual underlying connection in the pool can be invalidated by the server, but the client end remains in the Pool. Re-using it will come up with an error tat mentions something about 'transport-layer level exceptions'.

The invalidation by the server would be logged by the server. I've never looked at what it logs having always come across these from the client end.

One way to recreate the error is to use SSMS to open a query against a server (could be localhost). Run a simple query and it should work. Stop and restart the SQLServer service and re-run the query. SSMS should give an error.

One way to deal with this is by clearing the Connection Pool. Another way is by stopping and restarting the client application (which effectively clears the pool because the app is now running in a different process).

Here is some code I used in an EF Azure-based app (where transient network errors should be catered for):

internal static class TransientError
{
    // Try a very light-weight SQL command to test the connection
    // Basically costs a round trip to the DB.
    // If an SqlException is returned, clear the connection pool, wait and retry.
    // If another SqlException double the wait until a total of 12 secs has been used.
    // After 12 secs (wait = 6400) return; it's not that transient so let standard error handlers
    // cope with it.
    // If any other exception occurs - don't try to do anything clever, just return and let nature
    // take its course.
    internal static void Try(DbContext context)
    {
        int wait = 800;
        bool retry = true;
        while (retry)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(context.Database.Connection.ConnectionString))
                {
                    con.Open();
                    using (SqlCommand com = new SqlCommand("declare @i int;", con))
                    {
                        com.ExecuteNonQuery();
                    }
                }
                retry = false;
            }
            catch (SqlException)
            {
                Clear(context);
                Thread.Sleep(wait);
            }
            catch (Exception)
            {
                retry = false;
            }
            // break after we reach 6.4 secs (for a total as 12 secs)
            if (wait == 6400)
            {
                retry = false;
            }
            wait = wait * 2;
        }
    }
    private static void Clear(DbContext context)
    {
        // This is a clint side operation so won't cause an exception
        // with a server side error.
        // If it does fail, probably should throw!
        using (SqlConnection con = new SqlConnection(context.Database.Connection.ConnectionString))
        {
            SqlConnection.ClearPool(con);
        }
    }
}

This Try method was called before every EF access. For example:

public Service GetService(string serviceName)
{
    TransientError.Try(this.context);
    Service s = (from svc in this.context.Services
                 where svc.System == serviceName
                 select svc).FirstOrDefault<Service>();
    return s;
}

As i said, a guess. If you try the SSMS experiment above and the error matches exceptions thrown by the client then this is likely what is happening.


SELECT id
    ,product_id
    ,date
    ,weight
FROM
(
SELECT t.id
    ,t.product_id
    ,t.date
    ,t.weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date < t.date ORDER BY date LIMIT 1) AS prev_weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date > t.date ORDER BY date LIMIT 1) AS next_weight
FROM table AS t
) AS t1
WHERE weight <> prev_weight OR weight <> next_weight


MongoLab: Does hardware effect performance of a find() query I asked a pervious question on StackOverflow about if document size affected the performance of fetching that document (the document is 3MB in size)
https://stackoverflow.com/questions/30226219/does-the-size-of-a-document-affect-performance-of-a-find-query

The conclusion was, yes it does. It seems that the larger the document you are trying to fetch (using the find() query) the longer it will take to get a response (because the document will take longer to download given it's larger size)

Currently I'm hosting my database on MongoLab. I'm currently using the free sandbox tier, but I do plan on upgrading to a shared or dedicated plan in a few months.

My question is, will upgrading to a shared or dedicated plan improve the performance of downloading a large document? I'm assuming it will because the paid plans have better hardware, but will the performance improvements be noticeable/significant. Just curious what other thought.

All things being equal, it is logical to assume that better hardware = better performance. However, computers are strange beasts - I know that with Oracle, if you assign very large sizes (you need good hardware) to certain caches, you can actually slow down the machine.

The only real suggestion that I have is that you test, test and test again. You cannot manage what you cannot measure.


to_char(resolved_date-opened_date, 'hh24:mi') I am using the below to return the hh:mm difference between two date ranges. How would I update this code to minus the hours by 5?
CURRENT CODE = to_char(resolved_datetime-opened_datetime, 'hh24:mi')
CURRENT RESULTS = 06:35
NEW RESULTS = 01:35
resolved_datetime timestamp
opened_datetime timestamp

You can subtract the five hours from the resulting interval, before the formatting takes place:
to_char(resolved_datetime - opened_datetime - interval '5 hour', 'hh24:mi')

Note that to_char(..., 'hh24:mi') on an interval, might not give you what you want, if the difference is greater than 24 hours, e.g. an interval of 2 days 3 hours 5 minutes formatted with that, will display as 03:05, not as 51:05


Need advice on Primary and Clustering Keys of a table I need advise on a table which is part of a development project I'm involved.
A brief summary on the use of the table:

Data from multiple databases will be consolidated in this table for the purpose of full text searching. Full text index will be on a single column.

I'm trying to decide on the best candidate for the primary (and the clustering) key.   There will be two important fields: ID and Type.    ID is an identity field (which is also primary+clustering keys) on the source tables. Using just this field will result in a conflict because several source tables have the same ID (identity) field.
Type is a look-up table.
Id and Type together is unique.

I came up with the following scenarios:

Id and Type as primary+clustering key.
Create an identity field on the target table (primary+clustering) as well as a ID (synced with source's identity field) and Type.
Create a separate Id field on the target table (clustering). Primary key will be source's ID and type.

What would be the best choice here?

First I'm assuming that both ID and Type are INT columns.  Second that this is going to be a somewhat active table with a fair number of inserts (and possibly deletes). And last but not least that you aren't going to be pulling a "range" of IDs.  For example you don't need to look at ID 10000-15000.

If so I would create a third column that is an INT identity column.  Make the Primary/Clustered key on it.  Then create a unique key on ID + Type.  By making the new column the clustered key you will avoid any fragmentation problems caused by inserting into the middle of pages and speed up your inserts.  The unique key ID+Type will be used for your queries and will be small enough (8 bytes) that you shouldn't see any issues there.

Last but not least since you are trying to put a full text index on the table you will need a single column, non-null, unique key preferably an integer.  The new column will fit that bill admirably.  (And of course your ID + Type combo wouldn't work.)


How to handle the junk characters upon conversion of the data from image datatype to varchar? We are trying to convert the column of type image to column of type varchar(max). We tried the below way to do the conversion.

--col1 is of type image
--col2 is of type varchar(max)

set col2 = convert(varchar(max), convert(varbinary(max), col1)) from table

When we do it, we are getting junk characters like ÿÿÿÿÿÿÿÿ. We also tried using nvarchar(max) instead of varchar(max) which again adds more junk data.

The above conversion works for few types of record and doesn't work for few.

col1 starting with "0xD0CF11E0A" is not working
col1 starting with "0x7B5C727" is working fine

Please help me identify the issue here.

If you are converting data from one format to another, then there is no such thing as "junk": it is all data, even if it doesn't "look" right. So, if you are saying that the conversion is not producing the expected results, then you need to be clearer about what the expect results should look like. You do mention that:

  col1 starting with "0x7B5C727" is working fine

Well, doing that conversion

SELECT CONVERT(VARCHAR(MAX), 0x7B5C72);

Returns:

{\r

Why is "{\r" considered "working"? If the data in col1 is actual binary data, then you shouldn't be converting it to a string type in the first place. If "{\r" is somehow the expected result, then the fact that

SELECT CONVERT(VARCHAR(MAX), 0xD0CF11E0);

returns:

ÐÏà

would need to also be considered "correct".

Now, there is a minor difference between the two: the "working" input of "0x7B5C727" shows characters that all fall within the standard ASCII set of values 0 - 127 (i.e. 0x00 - 0x7F). But the "not working" input of "0xD0CF11E0" shows characters that mostly fall within the Extended ASCII range of 128 - 255 (i.e. 0x80 - 0xFF) which is determined by the Code Page associated with the default Collation of the database that the query is running in. And, the one character in the standard range -- 0x11 -- is a control character (hence the box). But, while the characters returned for the "not working" set can change per each Code Page, I tried several and they tend to mostly map to the same characters.


Does sp_spaceused data contain more than the sum of each table data? I wanna monitor the size of each table in my database. After doing so, I was wondering why the sum of the data of each table does not correnspond to the data size of the whole database I get after calling sp_spaceused.

Here are my results:
ExampleDatabase: 46 589 544 KB (data)
Sum(data) of each table: 18 564 680 KB (data)

The only objects I was ignoring while saving each tables size (data) were the system-tables and some tables, whose size is not worth talking about.

Because I can't see the error in my "catch table size procedure", I'm wondering if it's the sp_spaceused.data column, which maybe contains more than the sum of each table data?

What I understand is that the sum of the tables will not be equals the database size. On msdn it says:

  database_size will always be larger than the sum of reserved +
  unallocated space because it includes the size of log files, but
  reserved and unallocated_space consider only data pages.

  Pages that are used by XML indexes and full-text indexes are included
  in index_size for both result sets. When objname is specified, the
  pages for the XML indexes and full-text indexes for the object are
  also counted in the total reserved and index_size results.

  If space usage is calculated for a database or an object that has a
  spatial index, the space-size columns, such as database_size,
  reserved, and index_size, include the size of the spatial index.

Reference here


Database migration Oracle to PostgreSQL, domain indexes I am using aws schema conversion tool for migration database oracle to PostgreSQL. I found an error which is not support in PostgreSQL. PostgreSQL doesn't support domain indexes. anyone know what is the solution of domain index in PostgreSQL?

CREATE INDEX CTX_IDX_UFA_EMAIL
  ON USR_ACCOUNT(EMAIL)
  INDEXTYPE IS CTXSYS.CTXCAT;

When I create this type of index, a table and a trigger are automatically created in the database. My question is PostgreSQL does not support this type of index. What is the alternative way to convert this type of index in PostgreSQL database that work like Oracle database?

Oracle docs: Using Extensible Indexing.

And more about the "Context" CTXCAT index type.

INDEXTYPE IS CTXSYS.CTXCAT is Oracle's way of defining a "full text index" on a column -- as opposed to an ordinary B-tree.

The good news is that Postgres also has full text indexes, and these are documented extensively at https://www.postgresql.org/docs/current/static/textsearch.html

The basic syntax for creating a similar index in Postgres is

create index CTX_IDX_UFA_EMAIL
on USR_ACCOUNT
using gin (to_tsvector('english', EMAIL));

The bad news for you is that substituting this type of index in the database will mean you also need to change the queries on this column/table. This may or may not also have implications for the user interface -- it could be that end users are currently able to enter search terms that are understood by the Oracle full text index searching implementation.


SQL Continuous Table Updating: Best Practices? I have a highly "vertical" database, and I've written a dynamic pivot script to build a new time-indexed table that I can actually query on human time scales.  Pivoting the table takes a few minutes per hour of data.

I would like to keep the new table up-to-date in an efficient way, and I was thinking of writing a script that executes every minute to pivot data, append to the new table, and delete the original entries.  I want to keep the new data forever.

Are there any SQL server 2014 features/pitfalls I should be aware of, and is there a better path than the one I'm about to head down?  I've only been scripting in SQL for about 48 hours now...

Thanks everyone.

Original Data:
10:30:00 A 1.0
10:30:01 A 1.1
10:30:02 A 1.2
10:30:00 B 2.1
10:30:01 B 2.3
10:30:02 B 2.5
10:30:00 C 3.4
10:30:01 C 3.3
10:30:02 C 3.2

New table:
           A    B    C
10:30:00  1.0  2.1  3.4
10:30:01  1.1  2.3  3.3
10:30:02  1.2  2.5  3.2

In this example, my question is how to deal with 10:30:03 data efficiently when it arrives.

This is something that I recently did with a table of mine. I used CROSS APPLY and used this page as my guide. Perhaps this can help you?


--local-infile=1 using MySQL Workbench? When I start the MySQL bash client, I have been starting it with --local-infile=1 so that I can import data from a .csv file.

Is there any way of doing this within MySQL Workbench?

According to the MySQL Documentation on LOAD DATA INFILE

  For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, “Privileges Provided by MySQL”. For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

As long as you have FILE privilege, you can do this in SQL Editor

SET GLOBAL local_infile = 1;


MySQL UPDATE Performance Issue I'm running out of ideas on a performance bottleneck I'm seeing on one of my projects.

The table in question is pretty small, with only 5396 records.  However, I'm seeing queries like this show up in our slow query log:

# Query_time: 17.482010  Lock_time: 0.000088  Rows_sent: 0  Rows_examined: 0
UPDATE `submissions` SET `updated_at` = '2011-11-15 20:12:44', `status` = 'eligible',   `published_at` = '2011-11-15 20:12:44' WHERE `submissions`.`id` = 5334

Submissions.id is the primary key.

We are relatively read heavy, so we had query_cache enabled.  I've turned that off and it helped somewhat, but the updates still seem to be way too slow.

It only seems to effect this particular update, and the database overall has fairly light load with an average of 20 queries per second.

Any ideas on places to look would be greatly appreciated!

Clarifications

Mysql version: mysql  Ver 14.12 Distrib 5.0.51, for pc-linux-gnu (x86_64) using readline 5.2

Applications talking to the database are built with Ruby on Rails.  No explicit table locks.

What does EXPLAIN show for that query?  What does mysqltuner show? What does free -m show?

If turning the query cache off helped, I'd suspect you are running out memory and your machine is starting to swap.  Swapping is a pretty effective way to kill MySQL's performance.  The solution would be to either increase the memory of the machine, or tune MySQL to require less memory.


PostgreSQL: Add or drop column under load give wrong number of columns errors I have a heavily used PostgreSQL database. Sometimes I need to add/remove columns, preferably without any service interruptions.

I follow the safe operations list from https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql but many operations cause troubles anyway when the more busy tables are updated.

Typically we have user defined functions for all operations, which are run in the following manner:

Table and function definitions:

create table a(
    id serial primary key,
    x integer
);

create or replace function select_a() returns setof a AS
$$
begin
    return query
    select a.* from a;
end;
$$ language plpgsql;

Then the actual queries are run by our application as

SELECT id FROM select_a();

However, if I add a column with ALTER TABLE users ADD COLUMN y text; while the system is under load i sometimes (more frequently and persistent the more load the system is experiencing) get errors like these

  ERROR #42804 structure of query does not match function result type: Number of returned columns (2) does not match expected column count (3).

Can this be avoided somehow, or do I need to take the system offline during these kind of changes?

To recreate this follow these steps:

create the above table and function as give above
Create one file loop_alter.sql

#!/usr/bin/env bash
for i in {0..1000}; do
echo "alter table a add column y text; alter table a drop column y;"
done;

Create one file loop_select.sql

#!/usr/bin/env bash
for i in {0..100000} do
echo "select * from select_a() limit 1;"
done;

Run the two files simultaneously with psql

In one terminal: ./loop_alter.sql | psql

In another: ./loop_select.sql | psql

If your function truly uses SELECT * then I would suggest switching to using an explicit list of fields and then separate out the amendment of the table and the function.

ADDING COLUMNS:
Alter the table first, then change the function once the table is complete.

DELETING COLUMNS:
Change the function first and then the table.  If the table is large and under heavy load I'd still expect problems.


many Servers of SQL server 2012 needs mirroring I have now simply 2 SQL Server 2012.
I want to configure database mirroring between the 2 servers.

My questions are:

How can it automatically change host of one if the other one was dropped ?
Is the performance of mirroring acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?

How can it automatically change host of one if the other one was dropped ?

From the client application side, your application connection string should use FailoverPartner pointing to secondary or mirrored server.

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
Initial Catalog=myDataBase;Integrated Security=True;

From database side, if you are running with a witness in high-safety mode (high-safety mode with automatic failover), then ONLY you will have flexibility of automatic failover, rest are manual failover.
Even though, mirroring allows you to do automatic failover in high-safety mode, make sure your keep monitoring the REDO queue as this can impact the recovery during a failover scenario.
FROM BOL :

Rolling forward the database involves applying whatever log records are currently in the redo queue on the mirror server.
The redo queue consists of the log records that have been written to disk on the mirror server but not yet rolled forward on the mirror database.
Failover time for the database depends on how fast the mirror server can roll forward the log in the redo queue, which, in turn, is determined primarily by the system hardware and the current work load. Potentially, a principal database can become so busy that the principal server ships log to the mirror server much faster than it can roll the log forward. In this situation, failover might take significant time while the mirror server rolls forward the log in the redo queue.
Is the performance of mirroring acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?

To monitor performance, you need to monitor and baseline your server instance using :

On the principal:

Log Bytes Sent/sec: Number of bytes of the log sent to the mirror per second.

Log Send Queue KB: Total kilobytes of the log that have not yet been sent to the mirror server.

Transaction Delay: Delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This counters reports the total delay for all the transactions in process at that time. To determine the average delay per transaction, divide this counter by the Transactions/sec counter. When running asynchronous mirroring this counter will always be 0.

Transactions/sec: The transaction throughput of the database. This counter is in the Databases performance object.

Log Bytes Flushed/sec: The rate at which log records are written to the disk. This is the log generation rate of the application. It plays a very important role in determining database mirroring performance. This counter is in the Databases performance object.

Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks.

On the mirror:

Redo Bytes/sec: Number of bytes of the transaction log applied on the mirror database per second.

Redo Queue KB: Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.

Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks on the mirror.

As a side note, you need to take care of migrating jobs, ssis packages, logins, etc once you failover to secondary or mirrored database.


MongoDB Partial Replication I am using MongoDB 3.0.10 with 10 databases in it,

also has implemented 3 member replica Set for it,

the question is it possible to replicate only selected databases from Primary to Secondary? If yes, then How?

You are basically looking for filtered replication, and as of writing this answer (August 2016) it is not available as a feature in MongoDB.  The relevant feature request is SERVER-1559 for tracking/voting purposes (it is currently unscheduled).

In terms of options, you can get close to what you describe with the mongo-connector but that is not an officially supported tool.


Slow query performance due to temporary file? Here is the query:

SELECT "products".*
FROM "products"
WHERE (status > 100)
AND "products"."above_revenue_average" = 't'
AND ("products"."category_id" NOT IN (5))
ORDER BY "products"."start_date" DESC

I have an index on status and start_date.

Each time I run the query from my application, I get the following in the logs:

[WHITE] temporary file:
path "pg_tblspc/16386/PG_9.3_201306121/pgsql_tmp/pgsql_tmp2544.0", size 37093376
Query: SELECT "products".* FROM "products"  WHERE (status > 100)
AND "products"."above_revenue_average" = 't'
AND ("products"."category_id" NOT IN (5))  ORDER BY "products"."start_date" DESC

I believe this temporary file creation is the cause of the slow performance.

Running an EXPLAIN ANALYZE I get the following results:

QUERY PLAN
 Sort  (cost=63395.28..63403.51 rows=16460 width=524)
       (actual time=524.134..562.635 rows=65294 loops=1)
   Sort Key: start_date
   Sort Method: external merge  Disk: 36224kB
   ->  Bitmap Heap Scan on products
        (cost=4803.40..60389.73 rows=16460 width=524)
        (actual time=27.390..397.879 rows=65294 loops=1)
         Recheck Cond: (status > 100)
         Filter: (above_revenue_average AND (category_id <> 5))
         Rows Removed by Filter: 25115
         ->  Bitmap Index Scan on index_products_on_status
            (cost=0.00..4802.58 rows=89662 width=0)
            (actual time=18.006..18.006 rows=90411 loops=1)
               Index Cond: (status > 100)
 Total runtime: 577.870 ms
(10 rows)

I have then used http://explain.depesz.com/ to make it a bit more readable:

Per node type stats

+-------------------+-------+--------------+------------+
|     node type     | count | sum of times | % of query |
+-------------------+-------+--------------+------------+
| Bitmap Heap Scan  |     1 | 379.873 ms   | 67.5 %     |
| Bitmap Index Scan |     1 | 18.006 ms    | 3.2 %      |
| Sort              |     1 | 164.756 ms   | 29.3 %     |
+-------------------+-------+--------------+------------+

Per table stats

+------------------+------------+--------------+------------+
|    Table name    | Scan count |  Total time  | % of query |
+------------------+------------+--------------+------------+
| scan type        | count      | sum of times | % of table |
| products         | 1          | 379.873 ms   | 67.5 %     |
| Bitmap Heap Scan | 1          | 379.873 ms   | 100.0 %    |
+------------------+------------+--------------+------------+

Can I increase the database performance by adding some more indexes? Maybe some composite ones? Any ideas?

work_mem
Obviously, the sort operation spills to disk:

Sort Method: external merge  Disk: 36224kB

More work_mem can help the query, like @Kassandry already suggested. Increase the setting until you see Memory instead of Disk in the EXPLAIN output. But it's probably a bad idea to increase the general setting based on one query. Proper setting depends on available RAM and your complete situation. Start by reading in the Postgres Wiki.
To just fix your query, set work_mem high enough for your transaction only with SET LOCAL in the same transaction.
BEGIN;

SET LOCAL work_mem = '45MB';

SELECT ...

COMMIT;   -- or ROLLBACK

You probably need a bit more than 40 MB. In-memory representation is a bit bigger than on-disk representation. Related:

Configuration parameter work_mem in PostgreSQL on Linux

Query
Your query (after trimming some noise):
SELECT *
FROM   products
WHERE  status > 100
AND    above_revenue_average  -- boolean can be used directly
AND    category_id <> 5
ORDER  BY start_date DESC;

Your rows are half a kilobyte wide (width=524). Do you need to return all columns? (Typically, you don't.) Only list columns in the SELECT list which you need from your query to improve overall performance, especially since you have work_mem issues already.
Can any of the involved columns be NULL? Particularly important for category_id and start_date. You might want to adapt in that case ...
Index
A multicolumn index can certainly help performance. (Like @Paul outlined). You have to weigh cost and gain. If performance for this query is important or it is very common, go for it. Don't create a special index for every query. As few as possible, as many as necessary. Indexes are more powerful when shared, that increases chances that more of them stay in cache.
A boolean column like above_revenue_average is a typical candidate for a condition in a partial index rather than for an index column.

Unexpected Seq Scan when doing query against boolean with value NULL

My wild guess based on incomplete information:
CREATE INDEX prod_special_idx ON products (start_date DESC)
WHERE  above_revenue_average
AND    status > 100
AND    category_id <> 5;

Use DESC NULLS LAST in index and query if start_date can be NULL.

PostgreSQL sort by datetime asc, null first?


PostgreSQL not starting correctly, is it repairing itself first? I never saw that problem before. I had problems and many PostgreSQL processes were stuck so I killed them with a -KILL...

When I tried to restart, it says that it cannot restart, but the daemon continues to run and uses a little processor a lot of I/O. Is it trying to repair the database?

I get no log at all. I think there is a way to increase log output... I'll look into that. At this point, the socket to connect to the server doesn't get created, but the server does not quit or emit any error/message, so I have no clue what is going on!?

If anyone has a clue, I'd be glad to hear about it.

I had problems and many postgresql processes were stuck so I killed them with a -KILL...

Don't do this. It won't cause data corruption, but as you've discovered it forces the whole database system to restart and do crash recovery.

If you hard-kill any database back-end with SIGKILL (kill -9) the postmaster has to assume that shared memory might be corrupted and it has to kill and restart all workers, doing crash recovery as if the server its self had crashed and restarted.

You should not need to SIGKILL a backend. Use regular SIGTERM to ask it to stop what it's doing and exit - or better, use pg_terminate_backend(...) from within SQL. If the back-end doesn't respond, SIGQUIT should force it to terminate.

If you do SIGKILL, crash recovery usually takes a few seconds; minutes for a really big and busy databases. However, if you have a long checkpoint_timeout and huge checkpoint_segments you can accumulate a lot of work that must be done before the DB can become available again. If you have quite slow disk I/O this will be even worse.

PostgreSQL does produce logs while it's in recovery, and they're at a log level that makes it unlikely that they'd be suppressed. So it's hard to say what could be going on. Maybe you were looking at syslog, but your PostgreSQL install is configured to log directly to files in /var/log/pgsql or in the pg_log directory in the datadir?

(For anyone else reading this, never SIGKILL the postmaster then delete the postmaster pid file and restart PostgreSQL while there are still old postgres back-ends running. That can cause data corruption, since you've disabled every safety measure PostgreSQL puts in place to stop you from starting a new postmaster while old backends might still be running.)


If all tables are myisam, should I use myisam engine instead of default innodb? I have read all of the innodb/myisam threads and they are confusing.  Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.?

The software I am using creates myisam tables so I was wondering if I should switch from the default innodb engine to myisam engine and disable innodb since I do not have a single table that is innodb?

Software that uses MyISAM tables is often a sign that you are dealing with amateur quality software.  There have been very few valid reasons to use MyISAM for several years, now... ACID and intelligent crash recovery and foreign key constraints are just a few things that come to mind that MyISAM will never dream of being able to provide.

Turning off InnoDB because you aren't using it is also an exercise in futility and short-sighted at best.  It is essentially just tinkering with your server for no good reason.  Leave that setting, and virtually all of the other settings, at their default values unless you have a specific reason to change them.  Tweaking parameters requires discipline and deliberate, well-informed action.

Oracle has officially deprecated the ability to do this, anyway.  As of now, in 5.6.23, disabling InnoDB still works, but results in a warning that it is a capability that will subsequently be removed... which should tell you something about whether it's a good idea.
http://dev.mysql.com/doc/refman/5.6/en/innodb-turning-off.html


VLDB with large quantity of data file imports I am a little out of my league with this scenario and hope this community can help get me on the right direction.

I have recently been tasked with re-designing and implementing a database application where the company I work for processes a large quantity of text files (sizes ranging from a 100MB to 3GB).  We currently process approximately 600 of these files per year.  This data needs to be accessible after our initial processing.

The current implementation has each file being imported via Application bulk load or SSIS to a "temp" database as individual tables.  I would like to keep this process in place as it gives a good entry point for when manual entry into processing is needed.

I am in the process of trying to create a new MS SQL Server installation and architecture, and am looking for advice/articles which would cover a similar scenario as to best practices (partitioning, filegroups, etc.).  I have worked in databases for a few years, but not at this level.

So if you have any experience in this type of scenario or know of a good article or book you could point me to, I would be extremely grateful.  I just need a nudge in the right direction.  Thank you in advance.

Below are general recommendations and your milage may vary depending on you workload running and your IO subsystem, server hardware configuration, etc.

At SSIS level, you can look into below things to speed up data read and data load :

Fast Parse Option along with its limitations.
Use the SQL Server Native Client 10.x OLE DB provider for an In-Memory, high performance connection
Set the Packet Size to 32767
Select the OLE DB Destination  Data Access mode “Table or View – fast load”  option

Refer to Speeding Up SSIS Bulk Inserts into SQL Server for more details.

Now coming to SQL Server - optimizing data loading :

Use Trace Flag 610 : inserts into a table with indexes will generally be a minimally logged operation. Tables with clustered indexes support multiple bulk load stream inserting concurrently, as long as these streams contain non-overlapping data. If data overlap is detected, streams will block (but not deadlock).
Look for table partitioning (Partitioned Tables and Indexes in SQL Server 2005) especially sliding window technique.
Look for -E startup parameter as well.

Look for optimizing network configuration as well :

Use fast NIC and switches.
Have the latest certified drivers for your
NIC.
Enable full duplex.
Enable support for Jumbo frames. Use TCP
Chimney Offload. Use Receive Side Scaling (RSS).

Also, you should monitor Wait Statistics on the server especially SOS_SCHEDULER_YIELD resulting in scheduler contention on Servers having multiple CPUs running concurrent Bulk load operations and competing for the same CPU Cycles.

Refer to this excellent whitepaper The Data Loading Performance Guide which have everything that I mentioned with diagrams and examples.

Also, to automate the sliding window technique - creating staging tables, loading data into it and then switching the partitions, you can fully automate it using  SQL Server Partition Management -- it has command line option as well -- available at CodePlex.


How to check when a record is replicated in SQL Server I have configured a database installed On-Premise to replicate certain tables on a Cloud SQL Server database hosted on Azure.

The replication task is scheduled to run at specific time everyday and I am looking for a way to check when a record was updated and what kind of operation was performed (Create, Update or Delete).

Is there any system table, stored procedure, or log that contains this information?

yes,you can get the statements executed by distributor agent which are applied on subscriber by sp_browsereplcmds procedure.
There is a nice article to get familiar with distributor databse system tables and other objects.

http://www.sqlservercentral.com/blogs/vinaythakur/2009/10/23/some-useful-commands-and-sp-for-transaction-replication/

all replication information stored in distribution database.Further it depends how you get.  Kindly go through the article and get the desired information.


using Aria vs InnoDB vs MyISAM I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and uses more space. Does anyone know if Aria is good alternative to MyISAM and does not corrupt the table ?

We have millions of tables deployed running ARIA in production. It is  definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables checkout TokuDB.


Is it normal for disk activity to disturb MySQL performance? I'm running MySQL 5.5 on a server with two SSDs in software RAID-1 configuration. The filesystem is ext4.

When any process (external to MySQL) produces a large amount of disk IO on the server, I find that MySQL becomes virtually paralyzed, with queries that normally take 30ms taking 10 seconds or more, depending on how long the IO lasts.

I can easily reproduce the problem by running a dd write test as follows:

dd if=/dev/zero of=tempfile bs=1M count=3024 conv=fdatasync,notrunc

This writes a 3GB test file to the same disk and partition where MySQL's data and log files are stored. Here is the output from the command:

3024+0 records in
3024+0 records out
3170893824 bytes (3.2 GB) copied, 16.0938 s, 197 MB/s

When I execute the command, I can immediately see queries showing up in the MySQL slow query log. Not surprisingly, they are all insert/update queries, which I assume are waiting to be committed. However, to my huge surprise, the query times are enormous: some are nearly as long as the execution time of the dd command itself!

It seems as if MySQL is paused or locked during any intense IO happening on the server. I thought that running MySQL on SSDs in RAID-1 would be exceptionally fast, with other IO operations unlikely to have any large effect on MySQL.

Is it normal for a single write process like this to disturb MySQL so profoundly?
Should I be looking at problems with the software RAID-1 or the ext4
filesystem?
Can MySQL be configured differently? I've read about setting
innodb_read_io_threads and innodb_write_io_threads to take
advantage of the higher IOPS available with SSDs, but I'm not sure
that will solve this problem.
Is it necessary to store all MySQL-related files on a dedicated disk to avoid this problem?

Thanks very much for any help.

From my experience, it is better to separate the MySQL data files and log files from other application files. When you don't have dedicated MySQL server, separate the data files and log files to different disks. I have faced the similar issue and we resolved by changing our data directory and log files to a each dedicated separate directories. I suggest you to go through the MOCA (MySQL optimal configuration architecture) for better understanding. I hope this will help you.

Thanks.


Connection to database causes SSPI context error I have migrated a SQL server from another server box that was decommissioned. They have the same name and also same IP address. However, when I connect to the server from an application using trusted connection, I get the "SSPI context not generated" error.
What I have done:

I have used the setspn -X to confirm there is no duplicate SPN.

I have changed the order of protocol to follow the order:
Shared Memory,
Named Pipes,
TCP/IP.

I have verified that when I restart SQL server, the service registers and deregisters. This was found in the SQL server log.

I have checked that on the SQL configuration the TCP/IP network protocol has the right IP and is active and enabled on both 32bit and 64bit.

I am running out of ideas and I am still getting the same error. I can't find any log that point to Kerberos.

Try using Microsoft Kerberos Configuration Manager for SQL Server to automatically detect and fix such SPN Issues:

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/


How to fix SQL Server Management Studio 2014 IntelliSense when intermittent Our remote SQL Server was recently upgraded to 2014. I installed SQL Server Management Studio 2014 on my Windows 10 client computer.

At first, everything seemed to be going great, until I noticed that IntelliSense is intermittent. By "intermittent" I mean that on the same computer, while working on the same script, IntelliSense might appear some times and not appear at other times. Before the upgrade to 2014, this never happened. Now it happens all the time.

Here's a specific example. Suppose I'm typing SELECT DISTINCT * from dbo. The moment I type the period, I expect IntelliSense to list the available tables, as it always does. So by intermittent I mean that sometimes I see the list of tables, and sometimes I don't.  This just a specific example of how the symptom manifests. This is not restrictive to table names. I mean that whenever I expect Intellisense to appear,  it doesn't always appear.

I checked the obvious: Troubleshooting IntelliSense (SQL Server Management Studio) reconfirmed what I already knew and didn't help. I've tried these things.

Closing the remote database connection and reconnecting
Refreshing the IntelliSense cache, both by Ctrl-R and through the menu Edit > IntelliSense > Refresh Local Cache
Making sure all Windows Updates were applied
Checking server connectivity with Ping (average 9 milliseconds)

This is not a duplicate because while other forum posts list the above steps as "answers" they are not answers for me. They do not solve the problem of intermittent IntelliSense.

Is there anything else i can do to fix this problem?

If possible, I'd suggest trying SSMS 2016 instead. It's still supported to use it against SQL Server 2014 (in fact, it's supported to use it against instances as old as SQL Server 2008). With 2016, SSMS is now free, and the installer for it is separate from the installer for the SQL Server server components (Database Engine, Integration Services, etc.). Plus, Microsoft is now releasing updates and improvements for it on a roughly monthly basis.

You can download SSMS 2016 from https://msdn.microsoft.com/en-us/library/mt238290.aspx.

I've personally found IntelliSense to work more reliably with SSMS 2016 than what I was used to with 2014, especially when working over slower or less reliable network connections.


MySQL Cluster - strange syntax error I have MySQL Cluster instance and want one column (BLOB) to be stored on disk instead of memory.
I tried to follow this tutorial: http://mikaelronstrom.blogspot.com/2006/02/how-to-define-table-that-uses-disk.html
And this is output I am getting:

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.00 sec)
mysql> use test1;
Database changed
CREATE LOGFILE GROUP lg1
    -> ADD UNDOFILE 'undofile.dat'
    -> INITIAL_SIZE 16M
    -> UNDO_BUFFER_SIZE = 1M
    -> ENGINE = NDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '16M
UNDO_BUFFER_SIZE = 1M
ENGINE = NDB' at line 3

Here is syntax for this command: http://dev.mysql.com/doc/refman/5.5/en/create-logfile-group.html
Any ideas why this is not working? I cannot find where I am violating this command syntax.

CREATE LOGFILE GROUP and CREATE TABLESPACE accept only bytes as unit.

http://bugs.mysql.com/bug.php?id=62858

With MB converted to bytes logfile group was created successfully for me.


Oracle AQ: History Table for Queue Table Is there a way to create a history table for a queue table, that will store all the events of the queues, for a defined period of time ?

It seems likely to me that you could use the retention property of the queue to do this. See http://docs.oracle.com/cd/B10500_01/appdev.920/a96587/qsample.htm#158007 and also dbms_aqadm.alter_queue for details. If you really need a separate table then how about creating a multiple subscriber queue and creating an audit subscription that populates the history table. I'd use built in functionality first.


Minimize impacts of a known Denial-of-Service in SQL Server I am currently experiencing a situation where an application is sending several identical queries at once (I found this in SQL Profiler).  This application is buggy, it is probably in loop somewhere and the responsible team is working to solve this problem.  But the same application also provides other services that are essential to the company, so I can't just disable it.  I have to live with it, until the team solves the problem.

So far so good.  But this SQL Server instance also maintains other applications, whose performance are being penalized due this occurrence, some of them timeout their operation which makes this situation a DoS, in practice.

My question is: what can I do, in the DB side, to minimize the impact to the other applications?

I don't care if the buggy app gets penalized, but I would not like to penalize the other ones.  (I can't just disable it, but I can slow down their queries results, if possible.)

If you have SQL Enterprise edition, you could use Resource governor to limit resources consumed by the problemmatic application. You will need to create a classifier function that will "detect" the application e.g. based on user login or application name and assign application sessions to separate Workload group, while keeping all other users and apps in Default group. Then define resource pool for the application and allocate resources limits for CPU, Memory and/or IOPS. Map the Workload group to the resource pool. If done correctly, the application will be restricted to the defined CPU, memory and IO limits.

Suggested reading: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

Edit: Based Diego's question I came up with possible workarounds without SQL enterprise edition. It is slightly rough and involves migration of the troublesome databases, but it's the only way I can think of:

Option 1) Install a new SQL server instance on the current server, migrate the database(s) there, edit connection string in the application etc. Limit assigned CPU cores and RAM on instance level (Processors affinity and Max Server Memory in server options). Secondary SQL instances on already licensed server do not require more licenses.

Option 2) In a virtualized environment you might create a whole new virtual machine, limit its resources on hypervisor level and then install a new SQL instance into that VM. Note that depending on your current licensing setup you might need additional Windows and/or SQL server licenses for the new VM.

Personally I'd go with 2) because it is easier to clean up when no longer needed. (And in my case I know that I have licensed entire virtualization hosts so I can create new VMs with SQL freely.)


Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed? I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many records and the time to perform these queries has progressively gotten slower, even though there are multiple indexes in places. Since there are now a couple years of data being stored, I am considering migrating records that are older than 6 months to a separate "archive" table and creating new records for each event type that includes the monthly aggregates (meaning the sum of the rows in the 31 records stored for January 2014 would be stored in 1 record). This will hopefully improve search speed, but is there a better strategy? Is this type of archiving common?

it's better to use partitioning, e.g. one table per month. You can then truncate archive tables which is an instant operation which frees disk space, or move them to a tablespace on a cheaper device and/or replace them with an aggregate row in an aggregates table. The table seen by your app will usually be a view of the union of the monthly tables. You need careful index design though.

You could also calculate a rolling (cumulative) sum in real time with a trigger.
On the other hand doing deletes and updates on the same table will not reduce the on-disk size of your db and may impact performance. But if that is an issue depends on the specifics: writes/selects/updates/sec, row size, drive hardware, indexing, memory etc. You don't mention any specifics.


Highly Concurrent Storage System Imagine your requirement is that you have 3 huge tables (structured data) with say 30 billion rows in each (total size of 4TB) and your many concurrent users (which are parallel os threads on remote LAN machines) will need to read a portion of the data through their SELELCT WHERE GROUPBY queries and highly concurrent, say 10,000 concurrent reads at the same time and also users need to insert (no update) data into these tables highly concurrent too like 2000 concurrent writers (all over the data center LAN network). The users would want to read and insert as fast as possible form this storage where each read and write will happen is ms to 1 second range.

What technologies do you recommend to satisfy such requirement? Is there any data storage or key value store that could do this? Cloud is NOT an option.

Some Clarifications:

The users do NOT have to see the data right away and eventual consistency is acceptable.
The data is accesses through whatever driver the storage can provide and users are again just threads running on remote machines of the data center. The queries are mostly like SELECT WHERE GROUPBY.

The data is in tabular format and each row is about 60 bytes.

No cloud option where I can not use DynamoDB or similar solutions. I have to be able to host it internally in the data center.

All the data of the tables can be read all the time and usage pattern is unpredictable. There is no join or super long query. No DR required but a reasonable HA is required but it does not have to be fancy. Every reader is getting a batches of rows based on its where clause and rows are not really related. We probably can have fixed length for each row but I am hoping the storage layer will worry about it.

Also, my biggest concern are all those concurrent writes that are happening with concurrent reads.

Your insights into this is highly appreciated.

And more thing, I have three of those tables with each 30 billion rows holding different object types

If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you.  Your requirement sounds a bit like an algorithmic trading platform.  This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to date data.

If you can partition your data by date and old rows don't get updated then you can build a hybrid OLAP system using a conventional OLAP server such as Microsoft Analysis services backed by an ordinary RDBMS platform.  It should be possible to make this cope with ~4TB of data and both SQL Server and SSAS will do shared-disk clusters.  Similar OLAP systems (e.g. Oracle/Hyperion Essbase) are available from other vendors.

OLAP servers work by persisting data in a native store, along with aggregates.  Most will support partitioned data.  In addition, most will also work in a ROLAP mode, where they issue queries against the underlying database.  The important thing to note is that the storage strategy can be managed on a per-partition basis, and you can switch a partition from one to the other programatically,

In this model, historical data is stored in MOLAP partitions with aggregates of the data also persisted.  If a query can be satisfied from the aggregates then the server will use them.  Aggregates can be tuned to suit the queries, and correct aggregates will dramatically reduce the amount of computation needed to resolve the query.  Very responsive aggregate queries are possible with this type of system.

Realtime data can be implemented by maintaining a small leading partition - for the current month, day or even hour if necessary.  The OLAP server will issue queries against the database; if this partition is small enough the DBMS will be able to respond quickly.  A regular process creates new leading partitions and converts closed historical periods to MOLAP.  Older partitions can be merged, allowing the historical data to be managed at any grain desired.

The clients writing to the database just write straight out the the underlying RDBMS.  If historical data remains static they will only be writing to the leading partition.  4TB is a practical volume to use SSDs for if you need extra DBMS performance.  Even mainstream vendors have SSD based offerings with faster SLC units as an option.


How to create some GIN index concurrently in Postgresql I have a large table: CREATE TABLE hh(h int8[] not null, file int8 not null), and GIN-index over h field (CREATE INDEX ON hh USING gin(h)). Index was created is about 8 minutes, so I decide to split this big table into two or more tables, and create indexes over all this tables.

I assumed that these indexes will be created in parallel and I get about double speedup (slightly less because of indexes and tables places on one disk). I assumed that for two tables I got 4-5 minutes (instead of 10 minutes for one table).

But that did not happen! Both indexes created 8-9 minutes, not 4-5 mins.

I try parallel creation of B-tree of indexes over other my big tables, and yes, PostgreSQL can create such indexes in parallel. So I assumed that works for GIN too.

I have enough RAM for indexes: 32GB RAM. Original table has 1Gb size, and original index 3Gb size. Splitted tables has 0.5Gb size and index 1.3Gb size.
maintenance_work_mem = 16Gb, work_mem = 16Gb.

I also try to put second table and index in another tablespace (located in other physical disk), but acceleration was very small (7.5 mins vs 8 mins). Why??

So, how to create GIN-indexes in parallel? Is this possible?

To follow up on my comment, PostgreSQL absolutely can build gin indexes on different tables in parallel to each other.  If you don't see a benefit, it is because you don't have enough of some resource.  Probably random disk IO capacity, as building large gin indexes can be quite intensive on this.

If using a different table space didn't speed it up, then perhaps the IO bottleneck is in the kernel or FS code, such that it fails to take full advantage of the underlying hardware.  One way to more-definitively determine the bottleneck would be to run two separate PostgreSQL servers on the same machine (listening to different ports) and then put one table in each database server, and try to create the indexes simultaneously on that set up.


How do i crack this non-ansi code blob I've just started a new job about a month ago working for a software company that is really understaffed. I've been tasked with modernizing some of this old non-ansi SQL code and I'm having a particularly tough time trying to crack open this blerb. I'm recently out of school so I'm marginally familiar with modern SQL standards and from the research I've done the deprecated piece would relate to the non-ansi joins *= . I've tried pretty much every way I can think of altering this mess and I'm at a loss.
Thanks for your assistance.

Select @SQLCmd =
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ ' AND ' + @WhereCond +
' UNION ' +
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End

Consider these two simple tables:

CREATE TABLE dbo.t1
(
    t1_ID int NOT NULL
);

CREATE TABLE dbo.t2
(
    t2_ID int NOT NULL
);

"old style" JOINs used in SQL Server 2000 and earlier, that look like this:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID *= t2.t2_ID;

Are equivalent to an ANSI-style LEFT JOIN:

SELECT *
FROM dbo.t1
    LEFT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;

This:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID =* t2.t2_ID;

becomes:

SELECT *
FROM dbo.t1
    RIGHT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;

Although, I'm not a fan of using RIGHT JOIN, and would rewrite this to its equivalent LEFT JOIN, as:

SELECT *
FROM dbo.t2
    LEFT JOIN dbo.t1 ON t2.t2_ID = t1.t1_ID;

The dynamic SQL in your question is complex enough that I'd first use PRINT to print out a sample of the generated code, then I'd use the above rules to convert the old style joins into ANSI-compliant ones.

I've mocked-up the following from your code, in an attempt to simplify it:

DECLARE @SQLCmd nvarchar(max);
DECLARE @InvType nvarchar(100) = 'InvType';
DECLARE @TableName nvarchar(128) = 'TableName';
DECLARE @RetnType nvarchar(100) = 'RetnType';
DECLARE @WhereCond nvarchar(1000) = 'WhereCond';

Select @SQLCmd =
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type
    ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ '
    AND ' + @WhereCond +
' UNION
SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND
' + @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type
' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End

PRINT @SQLCmd;

This results in:

SELECT 'InvType' AS Inv_Type, TableName_Number AS     Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, TableName.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond UNION
SELECT 'InvType' AS Inv_Type, TableName_Number AS Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, TableName.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND
TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type
AND TableName.Retention_Type = 'RetnType'

Which I'd reformat so that each column is on a separate line:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code
    AND TableName_Number *= RESV.Item_Number
    AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond
UNION
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code
    AND TableName_Number *= RESV.Item_Number
    AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'

A bit tedious, but now we can see what the query actually does.

Converting the "old-style" joins into ANSI-style joins results in:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond
UNION
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'

Folding that back into dynamic SQL results in:

DECLARE @SQLCmd nvarchar(max);
DECLARE @TableName nvarchar(128);
DECLARE @InvType nvarchar(100);
DECLARE @RetnType nvarchar(100);
DECLARE @WhereCond nvarchar(1000);

SET @SQLCmd = 'SELECT ''' + @InvType + ''' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE ' + @InvType + ' = RESV.Item_Type
    AND ' + @TableName + '.Retention_Type = ''' + @RetnType + '''
    AND ' + @WhereCond + '
UNION
SELECT ''' + @InvType + ''' AS Inv_Type
    , ' + @TableName + '_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , ' + @TableName + '.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON ' + @TableName + '_Number = RESV.Item_Number
WHERE ''' + @InvType + ''' = RESV.Item_Type
    AND TableName.Retention_Type = ''' + @RetnType + ''''


Can I separate user and system trace files in Oracle 11g? In Oracle 10g, the destinations for system and user generated trace files were controlled by the background_dump_dest and user_dump_dest Oracle parameters.

Starting in 11g these parameters are deprecated, and all trace files are written to the Diag Trace value in v$diag_info.

Is there any way to separate system and user trace files like you could in 10g?

I do not think that you can set the user and background dump destinations in 11g onward. One thing that might help is the TRACEFILE_IDENTIFIER parameter. I set this when I do a trace so that it is easy to find in the trace directory.

For example I setup a trace like this:

alter session set max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'bobbydurrett';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

My trace file has my name "bobbydurrett" in it so it is easy to search for it.

Bobby


Problem with aborted PT-online-schema change command I aborted a pt-online-schema change command to change a table definition. Now, when I run pt-online-schema change again, I get this error:

  The table <db name>.<table name> has triggers.  This tool needs to create its
  own triggers, so the table cannot already have triggers.

How do I run pt-online-schema change again?

I use MySQL.

When Percona Online Schema Change runs, it creates triggers which synchronize the new table with updates that happen to the old table (with the previous table definition).

Before you run pt-online-schema change again, you have to delete these triggers.

In MySQL you can find the triggers by writing show triggers like '<table name>'\G.

And you can delete those triggers by typing

drop trigger <trigger name>;

You should only follow this recipe if your table definition does not have other triggers than those created by pt-online-schema change. Their names will start with "pt_osc_".


SQL Server: Create default stored procedure as and when a new database is created I am using SQL Server 2008 R2. I need one simple stored procedure to be created by default, as and when a new database is created in SQL Server. How can I achieve this? Assume the stored procedure to be created is

CREATE PROCEDURE [dbo].[Default_SP]
AS
BEGIN
    SELECT GETDATE()
END

Triggers can be applied on the table level. But this is on database create event. I searched over the net and I could not find answers.

Create the procedure in the system database model.

From model Database

  The model database is used as the template for all databases created
  on an instance of SQL Server.


how to downgrade a statement for SSIS Following snippet works within SSIS 2012 --- derive colum block.

TOKEN(REPLACE(@[User::FileName],".csv",""),"\\",TOKENCOUNT(REPLACE(@[User::FileName],".csv",""),"\\"))

But it does not work within SSIS 2005. TOKENCOUNT was not recongnized according to the error dialog. Any suggestions which alternative could be used?


How to copy value from one column and update another column I made a mistake, I set a column to NULL for 3 Millions rows. The column name was "LargeImageURL".

How can I now take the value from "SmallImageURL" column and REMOVE ".SL75" from it and update LargeImageURL.

SmallImageURL

https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.SL75.jpg

LargeImageURL

https://images-na.ssl-images-amazon.com/images/I/41OAcvBFqXL.jpg

Any help please ? I know this can be done with php but can SQL do this ?

thanks


PROPOSED QUERY

SELECT
    IFNULL(B.uid,'') left_uid,
    IFNULL(C.uid,'') right_uid
FROM
    relation A
    LEFT JOIN data B ON A.left  = B.id
    LEFT JOIN data C ON A.right = C.id
;

I did it as LEFT JOIN rather than INNER JOIN in case data is missing from data table

YOUR SAMPLE DATA

drop database if exists gumkins;
create database gumkins;
use gumkins
create table data
(
    id int not null auto_increment,
    uid char(36),
    primary key (id)
);
create table relation
(
    id int not null auto_increment,
    `left` int not null,
    `right` int not null,
    primary key (id)
);
insert into data (uid) values ('466a5039-c4c1-403d-826d-07ed2399e288');
insert into data (uid) values ('188a193f-0638-4b71-9cda-03c30b4e008a');
insert into data (uid) values ('f8ed4eba-635b-4eff-a203-e72a8735fb27');
insert into data (uid) values ('7aa235f0-2441-4a00-bc5b-06194609a3d0');
insert into relation (`left`,`right`) values (1,3);
insert into relation (`left`,`right`) values (4,2);

YOUR SAMPLE DATA EXECUTED

mysql> drop database if exists gumkins;
Query OK, 2 rows affected (0.02 sec)

mysql> create database gumkins;
Query OK, 1 row affected (0.00 sec)

mysql> use gumkins
Database changed
mysql> create table data
    -> (
    ->     id int not null auto_increment,
    ->     uid char(36),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table relation
    -> (
    ->     id int not null auto_increment,
    ->     `left` int not null,
    ->     `right` int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into data (uid) values ('466a5039-c4c1-403d-826d-07ed2399e288');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (uid) values ('188a193f-0638-4b71-9cda-03c30b4e008a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (uid) values ('f8ed4eba-635b-4eff-a203-e72a8735fb27');
Query OK, 1 row affected (0.00 sec)

mysql> insert into data (uid) values ('7aa235f0-2441-4a00-bc5b-06194609a3d0');
Query OK, 1 row affected (0.01 sec)

mysql> insert into relation (`left`,`right`) values (1,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into relation (`left`,`right`) values (4,2);
Query OK, 1 row affected (0.00 sec)

YOUR SAMPLE DATA LOADED

mysql> select * from data;
+----+--------------------------------------+
| id | uid                                  |
+----+--------------------------------------+
|  1 | 466a5039-c4c1-403d-826d-07ed2399e288 |
|  2 | 188a193f-0638-4b71-9cda-03c30b4e008a |
|  3 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
|  4 | 7aa235f0-2441-4a00-bc5b-06194609a3d0 |
+----+--------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from relation;
+----+------+-------+
| id | left | right |
+----+------+-------+
|  1 |    1 |     3 |
|  2 |    4 |     2 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     IFNULL(B.uid,'') left_uid,
    ->     IFNULL(C.uid,'') right_uid
    -> FROM
    ->     relation A
    ->     LEFT JOIN data B ON A.left  = B.id
    ->     LEFT JOIN data C ON A.right = C.id
    -> ;
+--------------------------------------+--------------------------------------+
| left_uid                             | right_uid                            |
+--------------------------------------+--------------------------------------+
| 466a5039-c4c1-403d-826d-07ed2399e288 | f8ed4eba-635b-4eff-a203-e72a8735fb27 |
| 7aa235f0-2441-4a00-bc5b-06194609a3d0 | 188a193f-0638-4b71-9cda-03c30b4e008a |
+--------------------------------------+--------------------------------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Whats the term in Oracle, when I'm speaking about SCHEMAS on SQL Server Here, we have our applications developed both for oracle and SQL Server.

Devs are trying to fix their own way to be organized, using VW on views, PR on procedures and etc.

they told me that they're going to use something like DBO.REPORT_XXX to name tables, and I said "why don't you guys use Schemas"?

but, can I achieve something like sql server schemas on oracle?

I'm new with oracle and everything is different (like schema on oracle is the name of the database, that's a user)...

You can't map everything on a one to one relationship when comparing Oracle and SQL Server. There are some similarities between both products and then there are concepts that could pass as two concepts within the other product. Let's start out with the basic stuff (Columns, Tables, ...) and continue up until we reach the instance.

   Oracle Database | SQL Server
  -----------------+-----------------
   Column          | Column
   Table           | Table
*) Schema          | Schema
*) User            | Schema
*) User            | Database User
*) User            | SQL Login
   Data Dictionary | INFORMATION_SCHEMA
   Tablespace      | Filegroup
   Oracle Database | SQL Server Database
   Oracle Instance | SQL Server Instance

Reference: [Microsoft SQL Server 2008 - Practical SQL Server 2008 for Oracle Professionals][] ()

Database and Instances

An Oracle instance will normally contain one database.

The exception being when an Oracle instance has been configured to run a container database (CDB), which will contain multiple pluggable databases (PDBs).

Reference: 17 Introduction to the Multitenant Architecture (Oracle | Docs | Database Concepts)

A SQL Server instance normally contains multiple databases.

A newly created SQL Server instance will start off with the basic four system databases (master, model, msdb, tempdb).

Schemas

The database at the Oracle level is the container for multiple schemas. The same is valid for the SQL Server. However, there is a difference *) between the Oracle schema and the SQL Server schema.

... in SQL Server

In SQL Server you can create a schema like this:

USE [StackExchange]
GO

/****** Object:  Schema [DBT]    Script Date: 12.12.2018 09:42:26 ******/
CREATE SCHEMA [DBT] AUTHORIZATION TheOwner
GO

Reference: CREATE SCHEMA (Transact-SQL) (Microsoft | Docs | SQL)

Any newly created object (table, view) can be linked to a specific schema, otherwise it is linked/stored inside the default schema dbo, unless the database user creating the new object has a differente default schema.

  they told me that they're going to use something like DBO.REPORT_XXX to name tables

In this case dbo. is the default schema for any object created in SQL Server. The dbo schema doesn't have to be explicitly created as it exists as soon as you create a database. You could create your own schemas and create tables within that schema. Here an example using the previously crated DBT schema:

USE [StackExchange]
GO
----------- schema.tablename -------
CREATE TABLE [DBT].[Application](
    [ID] [int] NOT NULL,
    [APPL_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]

... in Oracle Database

In Oracle the schema is not a stand-alone concept/object. An Oracle schema is linked to an Oracle user. When you create an Oracle user a schema with the same name is created for that user. The CREATE statement for an Oracle user doesn't give a hint that the schema will be created:

CREATE USER ORACLEUSER IDENTIFIED BY YourPasswordHere1084
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 PROFILE "DEFAULT"
 QUOTA UNLIMITED ON USERS;

When you create objects (tables, views) you create them inside a specific schema. There is no default schema.

CREATE TABLE ORACLEUSER.ORACLUSERTABLE
(
  Identity  INTEGER
);

Summary

So in summary the Oracle user has a schema that has the same name as the user. The user is synonymous with the schema. Hence the *) pretty weird comparison in the table at the top.

The Oracle user (password/permissions) can be compared to the combination of SQL Server login (password) and the SQL Server database user (permissions).
The Oracle schema can be compared to the SQL Server schema.
The Oracle schema cannot exist without an Oracle user.
The SQL Server schema can exist without a corresponding user, but requires to be linked to a database user.
A SQL Server table can be created in the default schema dbo. (e.g. dbo.UserTable)
An Oracle table has to be created in a specified schema (e.g. ORACLEUSER.ORACLUESRTABLE)

Reference: Difference between database vs user vs schema (DBA Stack Exchange)

Answering Your Question

  but, can I achieve something like SQL Server schemas on Oracle?

Yes, you can. You create an Oracle user with password and a schema is automatically created. And yes, you could create a dbo user/schema in Oracle.


Aggregating on a repeated group In a current report, I'm trying to track the migration of a person from department n, to n+1, or to n+k.

My data is split into a row per term (university data).  An example:

dept |  term
------------
   a | 20120
   a | 20121
   a | 20122
   b | 20123 -- <- person moved to new department
   b | 20130
   c | 20131 -- <- person moved to another new department
   a | 20132 -- <- person went back to a department they've already been in

using the query

select
  dept,
  FIRST_VALUE(term) OVER (PARTITION BY dept ORDER BY term) as
from
  table
order by term;

gives me:

dept |  term
------------
   a | 20120
   a | 20120
   a | 20120
   b | 20123
   b | 20123
   c | 20131
   a | 20120 -- <- notice this is the same as the above set of a's

The duplicated a's in the first group and b's in the second group are due to other rows in the data (I'm okay with this, but removing that would be a bonus).  My main problem is I need that final a to be treated as a different group; so basically showing the first each time a cluster appears.

This kind of problem can be solved with the use of the LAG() function:

WITH cte AS
  ( SELECT dept,
           term,
           LAG(dept) OVER (ORDER BY term) AS prev_dept
     FROM tableX
  )
SELECT dept, term
FROM cte
WHERE prev_dept <> dept
   OR prev_dept IS NULL
ORDER BY term ;

Test at: SQL-Fiddle


IS-A relation when defining tables I already asked this question on SO, prior to realizing that there may have been a more suitable place.

I am trying to implement an IS-A relationship between tables. Say I have a singer and a band which are artists

I want every artist to have an ID which is inherited by the band or singer.

In the code I wrote it appears that a band and a singer may have the same ID. How do I prevent that? And is there a better way to implement this? Reading material is most certainly welcome.

CREATE TABLE artists
( id number not null primary key
);

CREATE TABLE singers
( id number not null primary key,
  name char(50) not null,
  last_name char(50) not null,
  foreign key(id) references artists(id)
);

CREATE TABLE bands
( id number not null primary key,
  name char(50) not null unique,
  contact char(50) not null,
  participants number not null,
  check (participants > 0),
  foreign key(id) references artists(id)
 );

insert into artists(id) values(1);
insert into singers(id, name, last_name) values(1, 'an', 'Bish');
insert into bands(id, name, contact, participants) values(1,'un','an',2);

as you can see I was able to add a band and a singer with the same ID, under the same "parent."

The best way to enforce uniqueness in this scenario then is to not split the artist "types" into separate tables. Normalizing your schema will show that there are fields in common, so at the least your artists table would contain id, artist_type ("band" or "singer"), and name. Any fields that are not shared should be split into more generalized tables.

EDIT:

After thinking about schema designs I've seen for music catalogues, the following comes to mind.

Be consistent in nomenclature. Yes, an "artist" is a "band" or a "singer." But a "singer" is also a "band" in the sense that they are a performer onstage, billed under a specific name ("Tori Amos" and not "Amos, Tori"), and usually backed by a band of musicians. A "singer" is also an "artist," but so is every member of a "band."

In other words, don't get caught up in what type of performer it is, just call it an "artist" if that is the name you've decided on. "Artists" can have 1 - n "members," which are just "people" with the normal details you would want to store about people. So your schema would consist of:

CREATE TABLE artists
( id number(10) not null,
  artist_name varchar2(50) not null,
  contact varchar2(50) not null,
  artist_type varchar2(50) not null, /* ...not sure you really need it */
  CONSTRAINT artists_pk PRIMARY KEY (id)
);

CREATE TABLE people
( id number(10) not null,
  first_name varchar2(50) not null,
  last_name varchar2(50) not null,
  contact_number varchar2(50) not null
);

CREATE TABLE artist_members
( id number(10) not null,
  artist_id number(10) not null,
  member_id number(10) not null,
  CONSTRAINT members_pk PRIMARY KEY (id),
  CONSTRAINT artists_fk FOREIGN KEY (artist_id)
    REFERENCES artists(id),
  CONSTRAINT members_fk FOREIGN KEY (member_id)
    REFERENCES people(id)
);


MySQL Records Turnover: How to do it? I have a table of villas ordered by the UID as follow :

UID, name, random_order:

4,  villaA, 12
8,  villaB, 5
9,  villaC, 9
11, villaD, 2

I want to show my villas according to the random_order column (the random number is created at the insertion of the villa) so I sort by "random_order" and I get the result:

11, villaD, 2
8,  villaB, 5
9,  villaC, 9
4,  villaA, 12

Until now it’s easy. The more difficult thing is that I want to make a turnover on the villas. Every hour the first villa becomes the last one.

After 1 hour:

8,  villaB, 5
9,  villaC, 9
4,  villaA, 12
11, villaD, 2

After 2 hours:

9,  villaC, 9
4,  villaA, 12
11, villaD, 2
8,  villaB, 5

and so on.

I don’t know how to do that. My example has 4 villas but of course I need to manage a lot more so I need something well optimized (I don’t want to update the column “random_order” every hour). Also note that a villa can be added at any time with a random value as “random_order”.

I was given the following solution (count is a variable incremented by 1 every hours and max_val is the maximum of the column “random_order”):
SELECT *, (random_order - )%MAX_VAL AS villa_order
FROM villa_table
ORDER BY villa_order ASC
But this is working only if my column “random_order” has unique values and different by 1 only and starting from 0… It’s far from my case.

Anyone has an idea?

I tried to make a select with limit from n to end union select limit from 0 to n but it doesn't accept a double limit in a single request. I could use a temporary table to put results in it but then wouldn't it be too slow?

Tks

Not tested, but the idea is using same query twice with different LIMITs (depending on %HOURS passed) in a UNION.

(
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT %HOURS, 999999999999
) UNION ALL (
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT 0, %HOURS
)

You'll need to fill in %HOURS in your script language or stored procedure. Also once %HOURS is larger than the COUNT(*) of villa_table you'll need to restart it from 0.

Note how the parentheses are necessary.

Also note that the ORDER BY fields must uniquely identify rows (i.e. append the PRIMARY KEY!) to prevent possible ambiguous sorting.


Boolean logic with BITs and Booleans in SQL Server How can I mix boolean logic with bitwise and/or operators in SQL Server, specifically SQL Azure Database and/or SQL Server 2016?

To demonstrate what I'm trying to do, consider the following script:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
--INSERT INTO @Data VALUES ((@Flag1 | @String LIKE '%qwerty%') & @Flag2)
SELECT * FROM @Data

The script works fine as-is but the moment you uncomment out that second INSERT, all hell breaks loose. I understand this is invalid syntax because I'm mixing boolean logic with bitwise operators. But what is the right way to get this logic in there? Without going crazy with CASE WHEN statements, is there some way to do this?

You may need to revise your code as follows:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | case when charindex('qwerty', @string ) > 0 then 1 else 0 end) & @Flag2)
SELECT * FROM @Data

Your original code has syntax error because LIKE is not an operator.

Another way is to use IIF as following

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | iif( @String LIKE '%qwerty%', 1, 0)) & @Flag2)
SELECT * FROM @Data


Does pure MongoDB use schemas and models like Mongoose? I'm new on pure MongoDB and I've some questions about pure MongoDB.

1. Does pure MongoDB use schemas and models like Mongoose ODM?

If you have used Mongoose ODM, you know that Mongoose ODM uses schemas and models for defining fields with their properties. Like this;

/* Dependencies */
const mongoose = require('mongoose');

/* Define the user schema */
const userSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true
  },
  lastname: {
    type: String,
    required: true
  }
});

/* Define the model */
const User = mongoose.model('User', userSchema);

Do we need to define schemas and models with their fields?

2. Does pure MongoDB have any validators?

For example, If we have a field in schema; we can define some properties to fields like;

...
  name: {
    type: String,  // Type built-in validator
    required: true // Required built-in validator
  },
  points: {
    type: Number,  // Type built-in validator
    min: 0,        // Min (minimum) built-in validator
    max: 100       // Max (maximum) built-in validator
  },
  verified: {
    type: Boolean, // Type built-in validator
    validate: {    // Custom schema validator
      validator: function(v) {
        return this.points > 50;
      },
      message: 'You can not register!'
    }
  }
...

Does pure MongoDB support for any validators or we define them while CRUD operations like this;

function createUser(userData, db, callback) {
  if(userData.name && typeof(userData.name) == 'string' && userData.username && typeof(userData.lastname) == 'string') { // Validator for object
    const collection = db.collection('users');
    collection.insertOne({
      name: userData.name,
      lastname: userData.lastname
    }, function(err, result) {
      if(err) callback(err, null);
      console.log('One record writed!');
      callback(null, result);
    });
  } else {
    callback(new Error('You must enter name and lastname to register!', null));
  }
}

All my questions are about these. Thanks...

Does pure MongoDB use schemas and models like Mongoose ODM?

No, the MongoDB server (as at 4.0) does not have a concept of models or data relationships. Data is persisted in Documents which are stored on the server in a binary JSON-like serialization format called BSON.

  Does pure MongoDB have any validators?

The MongoDB server does support per-collection validation rules for inserting & updating documents. In MongoDB 3.6 validation rules are defined using JSON Schema; earlier versions used MongoDB query operators to express validation rules.

Mongoose vs MongoDB schema validation

Typically the goal is to validate data as early as possible in order to provide timely feedback to the end user and avoid unnecessary round trips and processing. For example, validation can happen in the frontend UI, in your application layer, and at the database layer. Frontend UI often has basic validation based on data types and field masks, while custom validation, relationships, and business logic is generally better suited to your application layer. Database validation is a final check to ensure only data fitting an expected (but flexible) schema is persisted.

Using a proposed standard like JSON Schema potentially allows validation rules to be shared by multiple layers of your stack. There are JSON Schema Implementations available for most popular programming languages and browsers. Note: MongoDB's implementation of JSON Schema has an Extension which is the inclusion of a bsonType keyword to allow use of all MongoDB BSON field types.

A current limitation of MongoDB's server-side JSON Schema validation is that it does not provide a detailed report of errors: documents either succeed or fail validation. You may want to watch/upvote SERVER-20547:  Expose the reason an operation fails document validation in the MongoDB Jira issue tracker. Depending on your validation rules and options, failure will either result in an error (the insert/update is rejected) or a warning (the insert/update succeeds but results in a log message). Several of the JSON Schema implementations for application code have more detailed validation reporting suitable for end users.


Can't promote PostgreSQL warm standby server to start serving data Scenario:

Two CentOS 7 machines, with ips 10.0.2.15 and 10.0.2.4
Both running PostgreSQL 9.4.6. First one (10.0.2.15) as master, and second one (10.0.2.4) as slave, not listening for clients (just mirroring data via WAL shipping).

Problem: I can make log shipping work (so, slave receives WAL files), but I cannot promote slave to accept connections.

What I did:

In master:

Created user for replication from slave:

psql -c “CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ‘replication’;”

Allowed slave to connect as replication user, with this line in pg_hba.conf:

host    replication    rep    10.0.2.4/32    md5

postgresql.conf:

wal_level = archive
archive_mode = on
archive_command = 'rsync -av %p postgres@10.0.2.4:/var/lib/pgsql/9.4/archive/%f —exclude=postmaster.pid'
max_wal_senders = 3 # Using just 1, but anyway...

In slave:

Deleted data repository

rm -fr /var/lib/pgsql/9.4/data

Made a base backup

pg_basebackup -h 10.0.2.15 -D /var/lib/pgsql/9.4/data -U rep -v -P

(I enter the password, and the base backup is properly made: new data directory now)

Added recovery.conf to new data directory just created. With this info:

standby_mode='off'
restore_command='pg_standby /var/lib/pgsql/9.4/archive %f %p %r'

Directory /var/lib/pgsql/9.4/archive exists in the slave, and it's writable for postgres.

So, the master-slave process is working. WAL files are being sent via rsync. But if I just try this in slave:

pg_ctrl promote

I get the message "server promoting", but nothing happens. And no new messages in the log. Still can't connect to the server via psql. No incoming connections accepted.

What should I do to promote the slave to serve data as a normal server? I'm talking about a high availability scenario, with a warm standby server starting to act as a normal server, allowing clients to connect.

I know I can change parameters in the slave to make it run as normal server, but I just think there should be a quick way to put my slave server to work. I thought it was just pg_ctl promote. But probably, I'm missing something.

Any clues?

Ok, made it work by using the -R flag in pg_basebackup. This flag automatically generates the recovery.conf file with the right parameters

From the official doc:

-R
--write-recovery-conf
Write a minimal recovery.conf in the output directory (or into the base archive file when using tar format) to ease setting up a standby server.


Stored Procedure to insert data Table1 contains ServerName and a list of applications (from Application01 to Application20) installed on the server.

Now I need to import data from Table1 to Table2. The final Table2 should look as following:

How can I do this with a Stored Procedure?

IMHO you should use UNION (yes, 19 times)

create table tbl (server_name varchar(100), app1 varchar(100), app2 varchar(100), app3 varchar(100));
insert into tbl values
('server1', 'app xx', 'app xy', 'app yx'),
('server2', 'app xx', 'app xy', null),
('server3', 'app xx', 'app xy', null);
GO

3 rows affected

select server_name, app1 as application from tbl where app1 is not null
union
select server_name, app2 as application from tbl where app2 is not null
union
select server_name, app3 as application from tbl where app3 is not null
GO

server_name | application
:---------- | :----------
server1     | app xx
server1     | app xy
server1     | app yx
server2     | app xx
server2     | app xy
server3     | app xx
server3     | app xy

dbfiddle here