ORDER BY and comparison of mixed strings of letters and numbers We need to do some reporting on values that are usually mixed strings of numbers and letters that need to be sorted 'naturally'. Things like, e.g. "P7B18" or "P12B3". @The strings will mostly be sequences of letters then numbers alternating. The number of these segments and the length of each could vary, though.

We'd like the numeric portions of these to be sorted in numeric order. Obviously, if I just handle those string values directly with ORDER BY, then "P12B3" is going to come before "P7B18", since "P1" is earlier than "P7", but I'd like the reverse, as "P7" naturally precedes "P12".

I'd also like to be able to do range comparisons, e.g. @bin < 'P13S6' or some such. I don't have to handle floating point or negative numbers; these will strictly be non-negative integers that we're dealing with. String lengths and number of segments could potentially be arbitrary, with no fixed upper bounds.

In our case, string casing isn't important, though if there's a way to do this in a collation-aware fashion, others might find that useful. The ugliest part of all this is I'd like to be able to do both ordering, and range filtering in the WHERE clause.

If I were doing this in C#, it would be a pretty simple task: do some parsing to separate the alpha from the numeric, implement IComparable, and you're basically done. SQL Server, of course, doesn't appear to offer any similar functionality, at least as far as I'm aware.

Anybody know any good tricks to make this work? Is there some little-publicized ability to create custom CLR types that implement IComparable and have this behave as expected? I'm also not opposed to Stupid XML Tricks (see also: list concatenation), and I've got CLR regex matching/extracting/replacement wrapper functions available on the server as well.

EDIT:
As a slightly more detailed example, I'd want the data to behave something like this.

SELECT bin FROM bins ORDER BY bin

bin
--------------------
M7R16L
P8RF6JJ
P16B5
PR7S19
PR7S19L
S2F3
S12F0

i.e. break the strings into tokens of all letters or all numbers, and sort them either alphabetically or numerically respectively, with the leftmost tokens being the most significant sorting term. Like I mentioned, piece of cake in .NET if you implement IComparable, but I don't know how (or if) you can do that sort of thing in SQL Server. It's certainly not something I've ever come across in 10 or so years of working with it.

Want a sensible, efficient means of sorting numbers in strings as actual numbers? Consider voting for my Microsoft Connect suggestion: Support "natural sorting" / DIGITSASNUMBERS as a Collation option

There is no easy, built-in means of doing this, but here is a possibility:

Normalize the strings by reformatting them into fixed-length segments:

Create a sort column of type VARCHAR(50) COLLATE Latin1_General_100_BIN2. The max length of 50 might need to be adjusted based on the max number of segments and their potential maximum lengths.
While the normalization could be done in the app layer more efficiently, handling this in the database using a T-SQL UDF would allow for placing the scalar UDF into an AFTER [or FOR] INSERT, UPDATE Trigger such that you are guaranteed of properly setting the value for all records, even those coming in via ad hoc queries, etc. Of course, that scalar UDF can also be handled via SQLCLR, but it would need to be tested to determine which one was actually more efficient. **
The UDF (regardless of being in T-SQL or SQLCLR) should:

Process an unknown number of segments by reading each character and stopping when the type switches from alpha to numeric or numeric to alpha.
Per each segment it should return a fixed-length string set to the maximum possible characters/digits of any segment (or maybe max + 1 or 2 to account for future growth).
Alpha segments should be left-justified and right-padded with spaces.
Numeric segments should be right-justified and left-padded with zeroes.
If alpha characters can come in as mixed-case but the ordering needs to be case-insensitive, apply the UPPER() function to the final result of all segments (so that it only needs to be done once and not per segment). This will allow for proper sorting given the binary collation of the sort column.

Create an AFTER INSERT, UPDATE Trigger on the table that calls the UDF to set the sort column. To improve performance, use the UPDATE() function to determine if this code column is even in the SET clause of the UPDATE statement (simply RETURN if false), and then join the INSERTED and DELETED pseudo-tables on the code column to only process rows that have changes in the code value. Be sure to specify COLLATE Latin1_General_100_BIN2 on that JOIN condition to ensure accuracy in determining if there is a change.
Create an Index on the new sort column.

Example:

P7B18   -> "P     000007B     000018"
P12B3   -> "P     000012B     000003"
P12B3C8 -> "P     000012B     000003C     000008"

In this approach, you can sort via:

ORDER BY tbl.SortColumn

And you can do range filtering via:

WHERE tbl.SortColumn BETWEEN dbo.MyUDF('P7B18') AND dbo.MyUDF('P12B3')

or:

DECLARE @RangeStart VARCHAR(50),
        @RangeEnd VARCHAR(50);
SELECT @RangeStart = dbo.MyUDF('P7B18'),
       @RangeEnd = dbo.MyUDF('P12B3');

WHERE tbl.SortColumn BETWEEN @RangeStart AND @RangeEnd

Both the ORDER BY and the WHERE filter should use the binary collation defined for SortColumn due to Collation Precedence.

Equality comparisons would still be done on the original value column.

Other thoughts:

Use a SQLCLR UDT. This might could work, though it is unclear if it presents a net-gain as compared to the approach described above.

Yes, a SQLCLR UDT can have its comparison operators overridden with custom algorithms. This handles situations where the value is being compared to either another value that is already the same custom type, or one that needs to be implicitly converted. This should handle the range filter in a WHERE condition.

With regards to sorting the UDT as a regular column type (not a computed column), this is only possible if the UDT is "byte ordered". Being "byte ordered" means that the binary representation of the UDT (which can be defined in the UDT) naturally sorts in the appropriate order. Assuming that the binary representation is handled similarly to the approach described above for the VARCHAR(50) column that has fixed-length segments that are padded, that would qualify. Or, if it was not easy to ensure that the binary representation would naturally be ordered in the proper way, you could expose a method or property of the UDT that outputs a value that would be properly ordered, and then create a PERSISTED computed column on that method or property. The method needs to be deterministic and marked as IsDeterministic = true.

Benefits of this approach are:

No need for an "original value" field.
No need to call a UDF to insert the data or to compare values. Assuming that the Parse method of the UDT takes in the P7B18 value and converts it, then you should be able to simply insert the values naturally as P7B18. And with the implicit conversion method set in the UDT, the WHERE condition would also allow for using simply P7B18`.

Consequences of this approach are:

Simply selecting the field will return the binary representation, if using the byte ordered UDT as the column datatype. Or if using a PERSISTED computed column on a property or method of the UDT, then you would get the representation returned by the property or method. If you want the original P7B18 value, then you need to call a method or property of the UDT that is coded to return that representation. Since you have to override the ToString method anyway, that is a good candidate for providing this.
It is unclear (at least to me right now since I have not tested this part) how easy/difficult it would be to make any changes to the binary representation. Changing the stored, sortable representation might require dropping and re-adding the field. Also, dropping the Assembly containing the UDT would fail if used in either manner, so you would want to make sure that there was nothing else in the Assembly besides this UDT. You can ALTER ASSEMBLY to replace the definition, but there are some restrictions on that.

On the other hand, the VARCHAR() field is data that is disconnected from the algorithm so it would only require updating the column. And if there are tens of millions of rows (or more) then that can be done in a batched approach.

Implement the ICU library which actually allows for doing this alphanumeric sorting. While highly functional, the library only comes in two languages: C/C++ and Java. Which means you might need to either do some tweaks to get it to work in Visual C++, or there is the off chance that the Java code can be converted to MSIL using IKVM. There are one or two .NET side projects linked on that site that provide a COM interface that can be accessed in managed code, but I believe they have not been updated in a while and I have not tried them. The best-bet here would be to handle this in the app layer with the goal of generating sort keys. The sort keys would then be saved into a new sort column.

This might not be the most practical approach. However, it is still very cool that such an ability exists. I provided a more detailed walk-through of an example of this in the following answer:

Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11?

But the pattern being dealt with in that question is a bit simpler. For an example showing that the type of pattern being dealt with in this Question also works, please go to the following page:

ICU Collation Demo

Under "Settings", set the "numeric" option to "on" and all of the others should be set to "default". Next, to the right of the "sort" button, uncheck the option for "diff strengths" and check the option for "sort keys". Then replace the list of items in the "Input" text area with the following list:

P12B22
P7B18
P12B3
as456456hgjg6786867
P7Bb19
P7BA19
P7BB19
P007B18
P7Bb20
P7Bb19z23

Click the "sort" button. The "Output" text area should display the following:

as456456hgjg6786867
    29 4D 0F 7A EA C8 37 35 3B 35 0F 84 17 A7 0F 93 90 , 0D , , 0D .
P7B18
    47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P007B18
    47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P7BA19
    47 0F 09 2B 29 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19
    47 0F 09 2B 2B 0F 15 , 09 , FD F2 , DC C5 DC 06 .
P7BB19
    47 0F 09 2B 2B 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19z23
    47 0F 09 2B 2B 0F 15 5B 0F 19 , 0B , FD F4 , DC C5 DC 08 .
P7Bb20
    47 0F 09 2B 2B 0F 16 , 09 , FD F2 , DC C5 DC 06 .
P12B3
    47 0F 0E 2B 0F 05 , 08 , FD F1 , DC C5 DC 05 .
P12B22
    47 0F 0E 2B 0F 18 , 08 , FD F1 , DC C5 DC 05 .

Please note that the sort keys are structure in multiple fields, separated by commas. Each field needs to be sorted independently, so that presents another small problem to solve if needing to implement this in SQL Server.

** If there is any concern about performance regarding the use of User-Defined Functions, please note that the proposed approaches make minimal use of them. In fact, the main reason for storing the normalized value was to avoid calling a UDF per each row of each query. In the primary approach, the UDF is used to set the value of SortColumn, and that is only done upon INSERT and UPDATE via the Trigger. Selecting values is much more common than inserting and updating, and some value are never updated. Per each SELECT query that uses the SortColumn for a range filter in the WHERE clause, the UDF is only needed one time per each of the range_start and range_end values to get the normalized values; the UDF is not called per-row.

With regards to the UDT, the usage is actually the same as with the scalar UDF. Meaning, inserting and updating would call the normalization method once per each row to set the value. Then, the normalization method would be called once per query per each range_start and range_value in a range filter, but not per row.

A point in favor of handling the normalization entirely in a SQLCLR UDF is that given it is not doing any data access and is deterministic, if it is marked as IsDeterministic = true, then it can participate in parallel plans (which might help the INSERT and UPDATE operations) whereas a T-SQL UDF will prevent a parallel plan from being used.


Login issue in Logshipping I have configured log shipping in my primary serverA and secondary serverB.

Everything works fine until I bring the standby database online.

Users are not able to login to the secondary serverB database (which is on the secondary server, which is brought online)

Can anyone help me on this?

LogShipping does not replicate server level objects such as logins, so your user will be unable to log in to the secondary server when you switch roles.

You can migrate existing logins using the sp_help_revlogin stored procedure, keeping SIDs and passwords for SQL authenticated logins.


ORA-29913 and ORA-30653 error selecting an external table I created an external table using a .tbl file and now Im trying to select all rows from that table, using:

select * from users_load;

But Im getting this error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

Do you know why this error is happening? This table have millions of rows, I dont know if can be because of this...

You can specify the number of rows allowed to be rejected before throwing an error, or you can specify UNLIMITED. I guess you specified a number, but your .tbl file contains rows that the database can't parse appropriately based on your definition.

If you want to ignore all malformed rows, you can simply change the limit to UNLIMITED:

alter table users_load reject limit unlimited;

create table example:

CREATE TABLE foo_load (
    employee_number CHAR(5)
) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (
        ...
    )
    LOCATION ('foo.txt')
)
REJECT LIMIT UNLIMITED; --Use limit, not limited


Use mysqldump to backup table with a new table name Is there a way to back up a table using mysqldump so the table that is being backed up can have a new name in the sql script created by mysqldump.

So that If I back up the table 'test_table' like so.

mysqldump -u user -p db_name test_table > backup.sql

and I want 'test_table' to now be called 'test_table_backup' in the backup.sql that is created.

CREATE TABLE `test_table_backup` (

...

);

I know i can just dump the file then craft some way to do a search and replace on the table name but I was hoping there was a safer way using mysqldump itself.

You have to use sed to transform the table name

mysqldump -u user -p db_name test_table | sed 's/test_table/test_table_backup/g' > backup.sql

Give it a Try !!!


Separation of Duties - Can a DBA patch or Upgrade a SQL Server 2008 R2 DB without TDE Encryption keys? Are there any considerations I should have in mind when I patch or upgrade a TDE encrypted database? In the context of separation of duties, does the person executing the patch or upgrade process need access to the Keys or certificates of a TDE encrypted database or can the database be patched without the keys. The answer is important because we are a legal shop and would like an application administrator to encrypt the database and outsource the db maintenance functions to a dba to patch and upgrade the database without having access to the “application data”. Could you throw some light on this please?

TDE only encrypts the data at rest.  If they have the access to connect to the server and query the database then they can get to the data.

But I don't think the person running the patch (CU or Service Pack) needs permissions to the database proper, just permissions on the server.

The only way to separate the job functions as you seem to want would be to use the application to encrypt the data, that way it's actually encrypted in the database.  Then even if someone has select rights to the database they can't read the data without the key.  But that adds a lot of overhead and makes a lot of things harder.


Is it redundant to set your primary key as a unique key? I'm putting tables together in MySQL workbench and I'm currently ticking my primary keys to also be uniquely indexed. Is this a redundant configuration?

In MySQL, like in most other DBMS, each primary key is already indexed, so it is not necessary to ask for explicit indexing.


ORA-00990 error while trying to grant priviliges: Why is that? After creating a user named suhail I am trying to grant that user some privileges. But I am getting an error saying missing or invalid privilege . Why is that ?

C:\Windows\system32>SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 5 08:11:35 2013

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> CREATE USER suhail IDENTIFIED BY InnocentDesire02;
User created.
SQL> GRANT CREATE TABLE,ALTER TABLE,INSERT TABLE,SELECT TABLE,DELETE TABLE,DROP
VIEW,CREATE ROLE,CREATE SESSION,ALTER SESSION TO suhail;

GRANT CREATE TABLE,ALTER TABLE,INSERT TABLE,SELECT TABLE,DELETE TABLE,DROP VIEW,CREATE
ROLE,CREATE SESSION,ALTER SESSION TO suhail

               *
ERROR at line 1:
ORA-00990: missing or invalid privilege

Your list of privileges contains a number that don't exist.  There is no ALTER TABLE privilege.  Nor is there a SELECT TABLE or DELETE TABLE or DROP VIEW privilege.  You can grant a user SELECT access on individual tables that are owned by other users.


How to do automatic failover for PostgreSQL with application automatic switch? There are two ways to try.

Method 1

Use JDBC connect the PostgreSQL cluster (primary, standby 2 servers only):

jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/jiradb

But failed:

  The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.)

How to connect multiple servers from jdbc?

Method 2

Use pgbouncer and repmgr

Three servers:

pgbouncer
PostgreSQL Primary (192.168.0.1) Installed repmgr, running repmgrd
PostgreSQL Standby (192.168.0.2) Installed repmgr, running repmgrd

Set config in pgbouncer server(pgbouncer.ini):

[databases]
postgres = host=192.168.0.1 port=5432 dbname=postgres
postgres = host=192.168.0.2 port=5432 dbname=postgres

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
unix_socket_dir = /tmp

When primary down, repmgr can switch to standby server as primary. But pgbouncer didn't automatic connect the second db server. Why? The config setting for databases does not work. Is it the wrong usage? Or pgbouncer doesn't has this automatic switch feature?

Compare the JDBC documentation:

  To support simple connection fail-over it is possible to define multiple endpoints (host and port pairs) in the connection url separated by commas. The driver will try to once connect to each of them in order until the connection succeeds. If none succeed, a normal connection exception is thrown.

  The syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2/database

So your URL is fine. If JIRA complains about it, that would be a JIRA bug.


Constructing Circularly Referenced Tables I'm attempting to construct two tables in Oracle, A and B; each of these tables reference each other. In my script, I have the foreign key constraints 'in-line' of the create table statements. Of course, both the create table A and create table B are failing  because neither table has been created yet.

Is it possible to keep the foreign key constraints in-line, or must they be added only after the table(s) have already been created by means of an alter table statement?

You must add the foreign key constraints after the tables have been created, using: alter table table_name add constraint constraint_name <constraint clause>;

There is no other way.

Note that, due to your table design, you may have to create the constraints with the  initially deferred deferrable clause. This forces Oracle to defer constraint checking until commit time & may be necessary if you cannot use an INSERT ALL statement and need to insert dependant rows into the two tables with separate statements.


Expression index on a citext column ignored, why? Running on RDS with about 32M rows.

PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Also testing locally on macOS with about 8M rows.

PostgreSQL 11.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit

I've got a column named old_value that's of type citext. I asked about this already, but posted way to many of my discovery steps along the way. Here's a boiled down version that I'm hoping gets to the point.

Background

I've got a field change log table named record_changes_log_detail with 32M rows and growing that includes a citext field named old_value.

The data is very skeweed. Most values are less than a dozen characters, some are more than 5,000.

Postgres chokes on large values with an error about B-tree entries being limited to 2172 characters. So I believe that for a B-tree, I need to substring the source value.

My users primary interest is in an = search, a starts-with search, and, sometimes, a contains-this-substring search. So = string% and %string%

Goals

Create an index that supports those searches that the planner uses.

Tried and failed

A straight B-tree fails to build, in some cases, because of long values.

An expression B-tree like this builds, but is not used

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail
    USING btree (substring(old_value,1,1024));

Adding text_pattern_opts does not help.

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON  record_changes_log_detail
    USING btree (substring(old_value,1,1024) text_pattern_opts);

Tried and works partially

A hash index works, but only for equality. (Like it says on the tin.)

This is the closest I've gotten to success:

CREATE INDEX record_changes_log_detail_old_value_ix_btree
    ON record_changes_log_detail
    USING btree (old_value citext_pattern_ops);

This works for quality, but not for LIKE. The release notes for PG 11 say it should work for LIKE:

https://www.postgresql.org/docs/11/release-11.html

By "work" I mean "the index is used."

I was unable to substring succesfully with this approach.

What do people do in this situation with citext fields?

Please edit your question, rather than posting answers to it that don't answer it.

If you create an index on the expression substring(old_value,1,1024), then that index can only get used if you query involves substring(old_value,1,1024).

While it is theoretically possible to prove that old_value='foo' implies that substring(old_value,1,1024)='foo' (and thus the contrapositive to that) if you have enough insight into the internals of substring, PostgreSQL makes no attempt to prove that.  You need to write the query in a way that no such proof is needed.


Case insensitive column names in a postgresql trigger function I have a trigger function in PostgreSQL 12 which executes something like this:
CREATE OR REPLACE FUNCTION "my_latlon_function"()
RETURNS trigger AS
$$
BEGIN
    latcolumn:= (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lat.*'
    );
    loncolumn := (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lon.*'
    );
    EXECUTE 'select $1.' || loncolumn USING NEW INTO lon;
    EXECUTE 'select $1.' || latcolumn USING NEW INTO lat;

    -- do much stuff

    RETURN NEW;

END
$$
LANGUAGE 'plpgsql';

The problem is, the lat and lon columns have capital letters in their name, e.g. myLatitude and myLongitude. The trigger function is able to retrieve these names, that's no problem.
The problem lies in the two EXECUTE statements where it seems that the column names become lower-cased as stated by this error (in the underlying QUERY when the trigger is fired):
ERROR:  column "mylongitude" not found in data type gpspoints
LINE 1: select $1.myLongitude
               ^
QUERY:  select $1.myLongitude
CONTEXT:  PL/pgSQL function my_latlon_function() line 24 at EXECUTE
********** Error **********

ERROR: column "mylongitude" not found in data type gpspoints
SQL state: 42703
Context: PL/pgSQL function my_latlon_function() line 24 at EXECUTE

I know that PostgreSQL does need to enclose column names which have capital letters in their name into double quotes. Hence, I have tried to set double quotes in the two EXECUTE statements such as this:
    EXECUTE 'select $1.' || "loncolumn" USING NEW INTO lon;
    EXECUTE 'select $1.' || "latcolumn" USING NEW INTO lat;

But the error stays exactly the same.
If possible, how can I handle CamelCase column names in a PostgreSQL trigger function?
If not not, why?

Use format with the %I placeholder for identifiers:
EXECUTE format('select $1.%I', latcolumn) USING NEW INTO lat;


Variation on greatest-n-per-group where whole table is needed with indicator, not just greatest rows per group I have a query that returns some rows having a grouping on a column and dates. I would like to have that query indicate in a column which of each group has the greatest date.
There are a vast number of questions on how to do the "greatest n per group", but those questions only want the row from each group with the greatest value. I want all the rows, and just an indication of which is the greatest.
For example, here is a table:

id
rep
date

1
27058
2020-11-16

2
27058
2020-11-09

3
27058
2020-10-30

4
46029
2020-11-03

I would like to have something like the following:

id
rep
date
greatest

1
27058
2020-11-16
1

2
27058
2020-11-09
0

3
27058
2020-10-30
0

4
46029
2020-11-03
1

I have experimented with some greatest-n-per-group methods to add a "greatest" indicator. But since those attempts are focused on the only the row with the greatest value, when the part of the where clause like "table2.date IS NULL" is removed, extra rows are added with all the join combinations.
Here is an example of such an experiment that does not work. It tries to use the greatest-n-per-group but comments out the IS NULL line:
SELECT DISTINCT m1.usermessageid, m1.repid, m1.sender_role, m1.message, m1.datesent, m2.datesent
FROM usermessage m1
LEFT JOIN usermessage m2 ON m1.principalid = m2.principalid and m1.repid = m2.repid AND m1.datesent < m2.datesent
WHERE m1.principalid = 99831
AND m1.repid IN (21600,99214,27058,97360,92678,96184,55429,46029)
# AND m2.datesent IS NULL
ORDER BY m1.datesent desc;

I would like to avoid having to add an additional query just to get the greatest rows.

Use a window function:
SELECT ROW_NUMBER() OVER (PARTITION BY repid ORDER BY date DESC) = 1 AS greatest, ...

This requires MySQL 8.0.02 or MariaDB 10.2.0 to use window functions.


Is it a good idea to use COLUMN AS (left([Another_Column],(4)) instead of LEFT in the select? I came across a table like this:

CREATE TABLE TABLE1
(
 CD1    int
,CD2    varchar(16)
,CD3    varchar(21)
,CD4    decimal(14,0)
,CD5    varchar(4)
,CD6    decimal(18,2)
,CD7    AS (left([CD3],(4)))
)

This table has more than 4 BILLION rows (totally unnecessary, but this is another topic).

As you can see in the last column, they use AS (left([CD3],(4))). I think this is pretty useless, since we almost NEVER have a SELECT on this table, and here is just using space.

Isn't it better to select that field during the select when needed?

If the computed column isn't persisted or indexed, it's only a virtual column (expression), and it's not taking up any "space".


What is the most effective way to discover all running instances of SQL Server using PowerShell? I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell methods of finding these instances, but neither seem to find all the instances.

1) Use WMI

        $srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $computerName
    $instances = $srvr | ForEach-Object {$_.ServerInstances} | Select @{Name="fullName";Expression={$computerName +"\"+ $_.Name}}
    return $instances

2) Use remote registry (as with Get-SQLInstance1)

The biggest problem I'm running into is that not all of the servers that I know about are running with the SQL Server WMI provider nor are all of them allowing remote registry. Is there a third method? I can use Remote Desktop to access all the servers but I'm looking at approximately 30 machines and would like to avoid manual steps if possible. This only needs to work for SQL Server 2008 and higher and while it would be nice to know about the other SQL Server services (SSIS/SSAS/SSRS) my main focus is on SQL Server itself.

If you want something that will be useful for the future I would probably steer clear of trying to search the registry. The hives for SQL Server have changed a bit over the years and it can be troublesome to keep up with.

The method with the SqlDataSourceEnumerator is flaky at times and although I will use it, not concrete evidence that instances are on the network. I believe it depends on SQL Browser Service as well, which most of the time I find disabled.

I will utilize the WMI class win32_Service. I use this because it offers up more information about the service than Get-Service cmdlet does.

I write everything as functions generally because you can use this to actually just do daily check or verification of the service for troubleshooting.

function Get-ServiceStatus ([string[]]$server)
{
 foreach ($s in $server)
 {
   if(Test-Connection $s -Count 2 -Quiet)
   {
    Get-WmiObject win32_Service -Computer $s |
     where {$_.DisplayName -match "SQL Server"} |
     select SystemName, DisplayName, Name, State, Status, StartMode, StartName
   }
 }
}

This is a bit more than what I usually use but in case someone else comes across and wants to use it. The Test-Connection equates to ping myserver in a DOS prompt and the -Quiet flag simply just has it return true or false. This will default to 4 pings so setting -Count 2 just makes it do it twice instead.

The variable [string[]]$server is a method used to state that $server will accept an array of server names. So an example call of this function could look something like:

Get-ServiceStatus -server (Get-Content C:\temp\MyServerList.txt)

or

$servers = 'MyServer1','MyServer2','MyServer3'
Get-ServiceStatus -server $servers

EDIT

A noted comment is the above does depend on a list of servers being provided. In cases where I am not provided that list you do have a few other options.

If I am in an Active Directory environment I can use the ActiveDirectory module in PowerShell to pull a list of all the servers on the domain with Get-ADComputer cmdlet. A word of warning though make sure you use a good -Filter on large domains.
I have also simply done an IP scan (with approval) of a network that gives me the IP addresses where port 1433 was found open. I will take that IP list and utilize Get-ADComputer to find the domain computer names, then pass that into the function above

Example:

Import-Module ActiveDirectory
$sList = $ipList | Select -ExpandProperty IP
$results = foreach ($i in $sList) {
 Get-ADComputer -Filter 'IPv4Address -eq $i' -Properties * | Select Name}
Get-ServiceStatus -server $results

EDIT

The suggested edit to utilize Write-Verbose and also add in try/catch block, while that may be useful, and in most cases a code practice, I will leave that up to the person that wants to use this function to add that additional code or functionality. Just trying to provide a basic example to go on. I did add the SystemName property to the output to include the actual server name returning information, do this on other functions just generally don't use this for more than one server at a time so it slipped my mind.


how to increase speed of this mysql query? How I can increase speed of this search query?

This search bring news based on time and title and text. each item has own factor.

SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` ,
(

    (

       CASE
          WHEN time >=1432173380 THEN 21
          WHEN time >=1432162580 THEN 18
          WHEN time >=1432097780 THEN 15
          WHEN time >=1431903380 THEN 11
          WHEN time >=1431320180 THEN 7
          ELSE 0
       END
    ) * 1.75 ) AS `timescore` ,

    MATCH (`text`) AGAINST ('tax in work') * 0.65 AS `textscore`

    FROM `news`
    HAVING `titlescore` + `timescore` + `textscore` >= 35
    ORDER BY `titlescore` + `timescore` + `textscore` DESC
    LIMIT 0 , 25

Now this takes (4 to 20)sec relay on search query!

Explain query result:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE         news     ALL     NULL            NULL    NULL       NULL 1656038     Using where; Using filesort

Table structure:

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf8_persian_ci NOT NULL,
  `text` varchar(400) COLLATE utf8_persian_ci DEFAULT NULL,
  `time` int(10) unsigned NOT NULL,
  `cat_id` int(10) unsigned NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `visit` int(10) unsigned NOT NULL,
  `url` text COLLATE utf8_persian_ci NOT NULL,
  `sha1` varchar(40) COLLATE utf8_persian_ci NOT NULL,
  `title_slug` text COLLATE utf8_persian_ci NOT NULL,
  `date` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
  `day_time` tinyint(4) DEFAULT NULL,
  `week_day` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sha1Uniq` (`sha1`),
  KEY `cat_id` (`cat_id`),
  KEY `source_id` (`source_id`),
  KEY `time` (`time`),
  KEY `date` (`date`),
  FULLTEXT KEY `searchIndex` (`title`,`text`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=2386224 ;

First retrieve id and the computed scores, then sort and join the result back to news:

SET @line = 0;
SET @last = 25;
SELECT B.*,titlescore,timescore,textscore FROM
(
    SELECT id,titlescore,timescore,textscore,combinedscore,(@line:=@line+1) line
    FROM
    (
        SELECT id,
            MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore`
            (
                CASE
                    WHEN time >=1432173380 THEN 21
                    WHEN time >=1432162580 THEN 18
                    WHEN time >=1432097780 THEN 15
                    WHEN time >=1431903380 THEN 11
                    WHEN time >=1431320180 THEN 7
                    ELSE 0
                END
            ) * 1.75 ) AS `timescore`,
            MATCH (`text`) AGAINST ('tax in work') * 0.65 AS `textscore`,
            MATCH (`title`) AGAINST ('tax in work') * 1.65+
            (
                CASE
                    WHEN time >=1432173380 THEN 21
                    WHEN time >=1432162580 THEN 18
                    WHEN time >=1432097780 THEN 15
                    WHEN time >=1431903380 THEN 11
                    WHEN time >=1431320180 THEN 7
                    ELSE 0
                END
            ) * 1.75 ) + MATCH (`text`) AGAINST ('tax in work') * 0.65
            AS combinedscore
        FROM news
    ) AA WHERE combinedscore >= 35
    ORDER BY combinedscore DESC
) A LEFT JOIN B news USING (id)
WHERE line <= @last;

Give it a Try !!!


SQL Login cannot connect to Database Recently I attended one interview and this was the question:

Many Users reported that they are not able to connect to server.Where as I can connect to server.

My Answer :

I will check out their permissions in security context if their access is revoked or lost (But they still have permissions).
May be their accounts are locked (No their accounts are not locked).

The Interviewer still expects more answer. What am I missing here? Can anyone help.

I thought of telling trigger but I am not sure .

I would check if the users get access via an Active Directory Security Group.


Oracle: Keeping exact db clone synced up in real time Is there a way to keep an exact clone of a database in real-time? (Or very close to real time)
The use case is for data reporting. I'm currently limited in it to make sure the application performance isn't impacted. If there was a real-time clone I could query then application performance becomes less of an issue.
I don't really know what to ask for in Oracle terminology when I approach our DBA folks about a solution, and things always go much more smoothly if I can speak their language from the outset without having to hash out precisely what I'm asking for.

Short answer: Data Guard.
Note, this isn't "Active" Data Guard, which costs ready money, but good, "old-fashioned" Data Guard.
It's a Recovery technology, intended to keep standby databases up-to-date and supports three "flavours":
A Physical Standby is only for Recovery purposes, giving you a complete, separate copy of your database that you can switch/fail over to in the event of the Primary database going "Pop!" (and can even happen automatically, if you configure an Observer).  Transport and redo apply can be real-time, depending on how the Data Guard configuration is set up.
Downside is that it's only for Recovery purposes and you can't do anything "useful" with it, like query it.  (BTW, that's the extra bit that "Active" Data Guard provides - it "cons" a Physical Standby database into allowing queries).
A Logical Standby is probably what you want.
Logical standbys are meant to be readable, all the time.
Downsides are that the transport mechanism is a bit different and can be a bit slower, (transport and apply lag in the handfuls of seconds instead of real-time), some [structural/DDL] changes don't get carried through (so you have to apply them in multiple places) and some of the more "esoteric" Data Types aren't supported (although that list shrinks with every version).
If you wanted to, you could even create a "farm" of Logical Standbys, accessed by clients in a round-robin fashion (as defined in their TNS Naming files), to spread the "read" load even further.  (Possibly overkill, given how powerful Oracle Database is, but it's another option.  YMMV).
A Snapshot Standby (just for completeness, here) is a "halfway-house" between the two.
Most of the time, it behaves like a regular database, allowing queries to be run against it.  Then, periodically, you close it, "plug" it back into the Data Guard infrastructure (which silently converts it into an unreadable, Physical Standby) and it will automatically receive and apply redo logs from the Primary.  Then, when you need to use the Snapshot again, you "unplug" it and open it (which silently converts it into a [readable] Snapshot Standby) with data as of the Point-in-Time of the "unplugging".
Useful if you wanted, say, a daily snapshot of your financial transactions to support offline data analytics.
Redo Apply is fast and a whole day's worth of redo can easily be applied in a couple of hours (or less) but, of course, you have to have the extra disk space to store it until it gets applied.
Not for everybody, admittedly, but it's another option.


MySQL database design for Twitter-like timeline I am working on an application which requires to show different posts on user's timeline(something like Twitter).

A User's timeline consists of following:

Reports which user created
Reports which other user created and fall under his/her 'alert location' radius
Reports posted by other users which he/she follows (Even if that report does not fall inside 'alert location' radius)
Report Shared/Re-tweeted by user he follows (Even if that report does not fall inside 'alert location' radius)
Reports which other user created and fall under his/her 'current location' radius when a user is travelling.
Follow notification- When someone started following him/her
Message notification- When someone sent a message to him/her

Note:- Once a report appear in user's timeline then it will not disappear even if user move out of location radius or changes 'alert location'. It will only disappear when it is deleted or flagged as inappropriate.

Requirement: I need an optimal DB schema(with SELECT query) so that I can show above mentioned posts on user's timeline.

Here is my current DB structure:

Table: users
   UserID (PK)
   email
   password

Table: alert_locations
   alertLocationID (PK)
   user_id (FK)
   latitude
   longitude
   radius

Table: followers
   follower_id (FK)
   following_id (FK)(follower_id + following_id) = PK

Table: reports
   reportID (PK)
   reported_by_id (FK)
   location
   latitude
   longitude
   parent_id (FK- For retweet relations)

Now, taking into the account the above cases of timeline I am thinking of timeline table with structure shown below:

Table Name: timeline
          timelineID  (PK)
          reference_id  (Can be report_id or follower_id or message_sender_id)
          user_id   (FK)
          title
          type       (1:For Report, 2:Follow, 3:Message)

So, when a report is created, for each user a new row in timeline table is inserted who is eligible to view that report. Using this table I can query all types of posts for user's timeline.

But as I see, this approach seems to have scaling issues and it does not look wise to insert 'n' amount of rows in timeline table every time a new report is created.

Is there any other better solution to achieve this?

Details not yet spelled out for the schema:

PKs -- What datatype?  INT UNSIGNED (4 bytes) tops out at 4 billion; think about whether you need to go above that or can shrink the size.

Collations -- For inclusion of Chinese and Emoji, be sure to use utf8mb4.

Password -- You should probably not store the plain-text password, but rather the encrypted version.

Latitude & Longitude -- How much precision is needed?  What queries will be applied.  This area can lead to some terribly inefficient code.  Your choices today impact your ability to even use lat/lng without a major rewrite, especially for your item 5.  http://mysql.rjweb.org/doc.php/latlng#representation_choices  and http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

Table: followers -- Does "x follows y" imply that "y follows x"?  The answer to this has an impact all the way back to the table definition.

"reference_id  (Can be report_id or follower_id or message_sender_id)" -- This is not easy to implement.  You may have to re-think the requirements.  Ponder whether to do it as 3 tables that are UNIONed together.

If you are talking about "massive scale", you will need to allow for "sharding".  I recommend postponing that until later -- but plan for a major rewrite in the near future.

If things will be "deleted", will they be "soft" or "hard" deletes or some form of "archiving".  If the quantity is small, this question does not matter; if it is large, it impacts performance and may resort to Partitioning, which is a big schema change.

For performance, consider turning off FKs for production, but keeping them in development.  Be sure to keep the indexes that are implicitly created for FKs.

Etc.


Issue: Why Restore database wizard/GUI for SQL Server 2014 loading old backup set to restore I am using SQL server 2014 to restore backups using the GUI, however, the back sets to restore are from old backups that are no longer available along with transaction logs that span months ago.

How can I repair the wizard so that it populates the most recent full and diffs and transaction logs like the way it used to work by default?

Here is a sample screenshot of the of the restore database screen I am making reference to

The Wizard is really just a listing of backup history records in MSDB on that local server. This wizard is not necessary to do backups. In fact, you should run a process to purge backup history on a regular basis. When I do a SQL Server Health Check for a client, I consider it a finding (relatively minor but still a finding) when this history is purged.

I am wondering if you are attempting to restore to a different server which also has that database on it?

Instead, choose Device and browse to your backup file. You'll see the right backupset to restore to inside of that backup file.

The dialog here is sort of a helper if you are restoring on the same server where your SQL Agent backups are taken but if you take backups in different ways, if you clean up history or if you are restoring to a different server, that dialog is likely not so helpful.


Copying MySQL table to another table with no missing new changes I would like to recreate/copy a MySQL table (a actual new ibd file) - and most of the suggestions recommends the following :

CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;

The suggestion works well - but from my understanding (correct me if I'm wrong), it does not insert/update records in the new table during the period of copying/recreating.

Example : During the copying, if record #45 is already inserted into the new table, and there was an update on record #45 on the original table - it won't be replicated over

Is there another way to ensure that after the full copying of data over, the data on the new table will be in the most updated state? I'm not sure using TRIGGERS is the solution for this.

The way you provide is a very simple and basic level's solution to create a backup copy of the table. Once copy done and someone made a change into the main table backup won't be updated with original tables so you have to do it again.

As far I understand you want to keep backup of your whole database which should be up to date with all changes being done till now.

To make this you can create a master-slave replica of the database, which will work as a master-slave database. Changes being done in the master will automatically reflect in the slave database.

Thank you.


Getting Random Sample from large database MYSQL (No Auto Inc Field) You were so helpful with my last question, I thought id throw another one at you thats stumping me.

Basically I got a table, requirements from company was it was all supposed to be 1 table so I got myself 1000 columns, and 100million rows (... life sucks).

Anyway they want me to generate for them 500,000 row random sample of their data. Problem is my PK is a VARCHAR(50) of their ids and has no increment system to it.

My rig is not overly powerful so a ORDER BY RAND() would be very ill advised!

Currently I am working on a Stored Procedure to maybe make a temp table then export it to csv?

Its not currently done, but if I am going the wrong direction let me know!

CREATE PROCEDURE randomResults()
BEGIN
    DECLARE results CURSOR SCROLL FOR
    FOR
    SELECT * FROM massiveTable;
    DECLARE nth INTEGER;
    SET nth = 0;
    DECLARE hits INTEGER;
    SET hits = 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    CREATE TABLE tmp LIKE massiveTable;
    -- open
    OPEN results;
    -- fetch

    WHILE hits <= 500000 DO
        FETCH NEXT FROM results;
        IF (nth mod 5) THEN
            INSERT INTO
            SET hits = hits + 1;
        END IF;
        SET nth = nth + 1;
    END WHILE;
    -- end fetch
    -- close
    CLOSE results;
END$$
DELIMITER ;

I am sure you guys have a better way of doing this, was just planning to get every 5th record and put it into a tmp table then export it...

My Table:

TABLE massiveTable (

staffhashID VARCHAR(50) PRIMARY KEY,
email VARCHAR (100),
marketCat1 tinyINT(1),
...
1000 of them
) engine = MYISAM;

an example of the PK:
    433kfdgsd3gfsd232

This may sound rather evil but you might find this interesting

Suppose your table looked like this

CREATE TABLE massiveTable
(
    PK VARCHAR(50) NOT NULL,
    ... Other Fields ....
    PRIMARY KEY (PK)
) ENGINE=MyISAM;

Step 01 : Create a Empty Copy of the Table and another for Keys

CREATE TABLE randomKeys SELECT PK FROM massiveTable WHERE 1=2;
CREATE TABLE randomTable LIKE massiveTable;

Step 02 : Iteratively load every fifth key

SET @x = 0;
INSERT INTO randomKeys
SELECT PK FROM
(SELECT PK,(@x:=@x+1) num FROM massiveTable) A
WHERE MOD(num,5)=0;

Step 03 : Create the Random Temp Table

ALTER TABLE randomTable DISABLE KEYS;
INSERT INTO randomTable
SELECT B.* FROM randomKeys A LEFT JOIN randomTable B USING (PK);
ALTER TABLE randomTable ENABLE KEYS;

Step 04 : There is no Step 04. That's it !!! No stored procedure needed.

CAVEAT : I cannot make any promises on the running time or query processing.

Give it a Try !!!


How to copy one table data to another on different server? I have country-wise MySQL servers and one master server.

The master server has all country data.

In that I want to copy data of a particular country from the master MySQL server.

Example: if any data inserted on the master MySQL server is related to India, it will also be inserted into the Mysql server in India.

How can this be achieved?

Table structures in both a country-level server and the master server are the same:

--
-- Table structure for table `vxp_service_types`
--
CREATE TABLE IF NOT EXISTS `vxp_service_types` (
  `st_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `st_name` varchar(25) NOT NULL,
  PRIMARY KEY (`st_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

You could restrict on table level. This can be done by ignoring some tables, or by only specifying the databases/tables that you want.

http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-do-table

Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.

Example: --replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.


Automatic Failover in Availability Group I have 2 nodes in my SQL server fail over cluster. I use SQL Server 2012 Availability Groups for HA purpose.

The problem is that when one my server goes down the automatic fail over does not happen!

How can I enable automatic fail over?

If you want to enable automatic failover, you will need to ensure that you have an "automatic failover set", to include two replicas (in your case, that'd be your two nodes) both set with synchronous commit, as well as automatic failover.

When you setup the Availability Group, this can be accomplished through the GUI, but if you had this scripted out, it would appear in the CREATE AVAILABILITY GROUP command.  If you take a look at the syntax and possible parameters of CREATE AVAILABILITY GROUP, you would simply see the opportunity to set AVAILABILITY_MODE = SYNCHRONOUS_COMMIT as well as set FAILOVER_MODE = AUTOMATIC.  This is essentially the steps to setup automatic failover.

Likewise, if this is already setup you can modify these replica settings with ALTER AVAILABILITY GROUP ... MODIFY REPLICA ON ... and specify the same above parameters.

Before continuing on, though, with the above modifications it is definitely going to be worth verifying what you already have in place.  A quick query that will tell you what your commit mode and failover mode is for your current configuration is below:

select
    ar.replica_server_name,
    availability_group_name = ag.name,
    ar.availability_mode_desc,
    ar.failover_mode_desc
from sys.availability_replicas ar
inner join sys.availability_groups ag
on ar.group_id = ag.group_id
order by availability_group_name, replica_server_name;

Feel free to edit your question and put the output of the above query for review.

EDIT:  In order to address your comment, it is worth pointing out that to have automatic failover you must be utilizing synchronous commit.


Connection problem after a big impdp This morning i had a weird problem just opening the SQL developer i cannot connect to my database. I checked the log file of the impdp operation launched before (14G), all the data is imported without errors. But I cannot access to the database, I tried with the SQL Developer or with SQL*Plus.

I obtained the following error message:

I think the problem came with the log or archiving. I think i did a little mistake creating the oracle's instance. After having beed seen this error message i've seen that the oraarch folder is full of big sized files. I deleted them but this didn't solved the connection problem.

I think the problem is in the Ora parameters' file. Creating the instance in archivelog mode

 shutdown immediate
    startup mount
    alter database archivelog;
    alter database open;
    spool off

and archivelog folder and name files definition :

echo "log_archive_dest_1='LOCATION=$ORADATA_BASE/$SID/oraarch'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_format='${SID}_%r_%s_%t.arc'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora

Here the file creating when creating the instance:

echo "db_domain=''" > $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_name='$SID'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "instance_name='$SID'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_block_size=8192" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "aq_tm_processes=0" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_mode=CHOOSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "O7_dictionary_accessibility=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Remote_os_roles=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "timed_statistics=TRUE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Workarea_size_policy='AUTO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_management='AUTO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_tablespace='UNDO'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "undo_retention=1800" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "java_pool_size=0" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "filesystemio_options=setall" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "remote_login_passwordfile=EXCLUSIVE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "audit_file_dest='$STRUCTURE_ADMIN/$SID/audit'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "background_dump_dest='$STRUCTURE_ADMIN/$SID/bdump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "core_dump_dest='$STRUCTURE_ADMIN/$SID/cdump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "user_dump_dest='$STRUCTURE_ADMIN/$SID/udump'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "Remote_os_authent=FALSE" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_index_caching=90" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "optimizer_index_cost_adj=10" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "db_file_multiblock_read_count=16" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "control_files='$ORADATA_BASE/$SID/oractl01/${SID}_ctrl01.ctl','$ORADATA_BASE/$SID/oractl02/${SID}_ctrl02.ctl','$ORADATA_BASE/$SID/oradata01/${SID}_ctrl03.ctl'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_dest_1='LOCATION=$ORADATA_BASE/$SID/oraarch'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "log_archive_format='${SID}_%r_%s_%t.arc'" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "pga_aggregate_target=256M" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "processes=151" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "sga_target=2G" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
 echo "sessions=151" >> $STRUCTURE_ADMIN/$SID/create/init$SID.ora
echo "Connect par SQLPLUS*"

Basically your database has stalled because it has ran out of space to store archive logs, and needs to switch one.

To remedy, delete the generated logs (assuming you don't need them for backup and recovery purposes), then execute (as SYSDBA):

alter system archive log all;

alter system switch logfile;

If you don't need to run your database in archivelog mode (ie: you don't need to be able to do any disaster recovery & don't care if your data goes AWOL), you can turn archivelog mode off (as SYSDBA, database in MOUNT mode):

alter database noarchivelog;

alter database open;


Is there a way to query all error messages in Postgres? Postgres has a catalogue of possible error messages here:

http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html#ERRCODES-TABLE

However, I would like to query this information from a system view instead of looking it up on web page  (in order to check that I handle all messages correctly in my code).

Is there a way to ask Postgres via a pg_something view or table what error messages it can raise? Also, where does Postgres store the translation (if any) of these messages?

It looks like there is no easy to use SQL to query error codes inside Postgres. However, the source does contain the following file:

src/backend/utils/errcodes.txt

Which has the code listed in an easily parsable format:

01000    W    ERRCODE_WARNING                                                warning
0100C    W    ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED                   dynamic_result_sets_returned
01008    W    ERRCODE_WARNING_IMPLICIT_ZERO_BIT_PADDING                      implicit_zero_bit_padding
01003    W    ERRCODE_WARNING_NULL_VALUE_ELIMINATED_IN_SET_FUNCTION          null_value_eliminated_in_set_function
01007    W    ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED                          privilege_not_granted
01006    W    ERRCODE_WARNING_PRIVILEGE_NOT_REVOKED                          privilege_not_revoked
01004    W    ERRCODE_WARNING_STRING_DATA_RIGHT_TRUNCATION                   string_data_right_truncation
01P01    W    ERRCODE_WARNING_DEPRECATED_FEATURE                             deprecated_feature


Retrieving Alternating Values I have a column with two values in my database, I want to retrieve the two values alternately, for example I have:

name   itemcode
----------
 A       1
 B       2
 C       2
 D       2
 E       1
 F       1

I want to display them as:

name itemcode
----------
A      1
B      2
E      1
C      2
F      1
D      2

Here's a solution for SQL Server. This does work if you have itemcode 3, 4, etc. and not just 1 and 2.

CREATE TABLE [dbo].[Test]
(
    name char(1) NOT NULL,
    itemcode tinyint NOT NULL
);

INSERT INTO [dbo].[Test](name, itemcode)
    VALUES ('A', 1), ('B', 2), ('C', 2), ('D', 2), ('E', 1), ('F', 1);

SELECT
    t.name,
    t.itemcode
    FROM
    (
        SELECT
            name,
            itemcode,
            ROW_NUMBER() OVER(PARTITION BY itemcode ORDER BY name) AS RowNum
            FROM [dbo].[Test]
    ) t
    ORDER BY
        t.RowNum,
        t.itemcode;


How to install repmgr 4.2 (ubuntu) apt-get install postgresql-10-repmgr  give me repmgr 4.3.  However, my primary server is still on 4.2.

How do I install repmgr version 4.2?

Thanks in advance!

An archive of old packages (3.3.2 and later) for Debian/Ubuntu-based systems is available here:
http://atalia.postgresql.org/morgue/r/repmgr/


Recursion only if a criteria is met I'm using DB2 V7 and I have a two table PARTLIST and PARTTYPE as follow :

CREATE TABLE PARTLIST (PART VARCHAR(16),
                      SUBPART VARCHAR(16),
                      QUANTITY INT,
                      Line_number INT);

CREATE TABLE PARTTYPE (PART VARCHAR(16),
                      IS_ASSEMBLY INT);

INSERT INTO PARTLIST VALUES
('PRODUCT1','PART1'    ,1        ,1),
('PRODUCT1','PART2'    ,1        ,2),
('PRODUCT1','SUBASSEMBLY1' ,2        ,3),
('PRODUCT1','PRODUCT2' ,1        ,4),
('SUBASSEMBLY1','PART3' ,4        ,1),
('SUBASSEMBLY1','PART4'    ,4        ,2),
('PRODUCT2','PART3'    ,2        ,2),
('PRODUCT9','PART6'    ,5        ,1);

INSERT INTO PARTTYPE VALUES
('SUBASSEMBLY1',1);

I'm using this Query to browse the entire hierarchy of the PARTLIST table :

WITH RPL (PART, SUBPART, QUANTITY) AS
           (
              SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
                 FROM PARTLIST ROOT
             UNION ALL
              SELECT PARENT.PART, CHILD.SUBPART,
                     PARENT.QUANTITY*CHILD.QUANTITY
                 FROM RPL PARENT, PARTLIST CHILD
                 WHERE PARENT.SUBPART = CHILD.PART
           )
SELECT PART, SUBPART, QUANTITY AS "QTY"
   FROM RPL
   ORDER BY PART, SUBPART;

I need some help to only "explode" part that are an assembly (Based on PARTTYPE Table) and to add a column preserving the order of the line in th result.
The desired result should be something like this :

PART         SUBPART         QTY    LINE NUMBER
-----------------------------------------------
PRODUCT1     PART1           1      1
PRODUCT1     PART2           1      2
PRODUCT1     SUBASSEMBLY1    2      3
PRODUCT1     PART3           2      4
PRODUCT2     PART3           2      1
PRODUCT9     PART6           5      1
SUBASSEMBLY1 PART3           4      1
SUBASSEMBLY1 PART4           4      2

fiddle

WITH RPL (PART, SUBPART, QUANTITY) AS
           (
              SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
                 FROM PARTLIST ROOT, PARTTYPE
                 WHERE ROOT.SUBPART = PARTTYPE.PART
             UNION ALL
              SELECT PARENT.PART, CHILD.SUBPART,
                     PARENT.QUANTITY*CHILD.QUANTITY
                 FROM RPL PARENT, PARTLIST CHILD
                 WHERE PARENT.SUBPART = CHILD.PART
           )
SELECT PART, SUBPART, QUANTITY AS "QTY"
   FROM RPL
   ORDER BY PART, SUBPART
/*
UNION ALL
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
LEFT JOIN PARTTYPE ON ROOT.SUBPART = PARTTYPE.PART
WHERE PARTTYPE.PART IS NULL
*/
;


Database Design for Credit Purchase I have the following table that holds the purchase order

po_id
supplier_id
transaction_type  -- either cash or credit

and other table that holds the items

po_id
product_id
quantity
unit_price

Now, how should I handle payment transactions, as payments can either be cash, or credit. If credit, the payment can be made in multiple transactions. and if Cash, there should not be any outstanding payments for this particular purchase order. How can I design a schema that identifies outstanding amounts per purchase order and reflect that in a double entry accounting schema.

You can create the table - Transactions: po_id, client_id, transaction_type, transaction_purpose_id (you can create the table transaction purposes (products, services, taxex and so forth) amount_paid, transaction_status (processing = 1, proccessed = 2, failed = 3, unknown = -1 and so forth).

Now, a clien can pay for an order however he wants. Taxex can be paid as well.


MS SQL Server Error: Replication components are not installed on this server on Ubuntu We have a Windows 10 SQL Server 2014 Standard License I'm using on our local computer. Trying to replicate a database to an Ubuntu Server 18.x with SQL Server 2018 (installed using this easy guide).

I've completed the replication steps on my local PC (using this guide), but when I try to setup Subscription using SSMS 2018 I get the error below

Replication components are not installed on this server on Ubuntu

I've tried:

Reinstalling the SQL Server on Ubuntu as recommended by most Microsoft forums  like here and here to no avail. The instructions point to a command with .exe:

You need to run setup again and install the replication components. You can do this from the command line like this:

setup.exe /q /Features=Replication /InstanceName=MSSQLSERVER /ACTION=INSTALL /IAcceptSQLServerLicenseTerms

Edit 2: This says Replication isnt supported on Linux SQL Server 2017

Confirmed. Replication on Linux begins with SQL Server 2019 according to Microsoft

  SQL Server 2019 introduces SQL Server Replication for instances of SQL Server on Linux.


autovacuum challenges after upgrade to postgresql 9.6 We have a cluster with several dozen databases.  It was running great on 9.3 for a year (growing over time) when we did the upgrade to 9.6 last month.  Since that time, the database will freeze on updates of large tables (tables with 100k+ rows), from between a minute, to 30-60 minutes.

Specifically, one of our processes updates several columns one-at-a-time in such a table, and in the code log, I can see where it pauses and hangs, for up to 30 minutes, and then picks up later as if nothing happened, and updates many consecutive columns in the same with no such hang, within seconds.

Increasing logging in the database showed nothing at first - and analysis of memory usage on the server and in postgres showed nothing unusual for resource usage.  Eventually, I was able to see, with debug-level logging, that postgresql would go through vacuuming all databases, and the query in question would un-stick after the autovacuumer would hit that database, though not on the first loop of all databases, but after several passes.  Since then, I have been messing with the autovacuum settings.  I tried turning autovacuuming off, as it was off on 9.3 and somehow we survived - but that did not help.  So I turned it on, and have been changing various settings as I learn about them.  I got it so that the production server does not hang in this way through frequent autovacuuming, adding 16 workers, etc.

I am pasting the settings I have in general for our db server - the general memory settings were created with pgtune, the autovacuum settings I have been essentially tinkering with.

I am learning but am very much shooting in the dark - if anyone can provide guidance on how best to handle the analysis (through logs, etc) and optimizing of the autovacuuming on this instance of 9.6, that is what I am after.

Various queries at the official site and here designed to show locks don't turn up any waiting processes at all - but I do see at least one each entry for a process with an 'AccessShareLock' and 'RowExclusiveLock' in the pg admin iv dashboard, under Locks, when the query to update the column is running.

Again, what is frustrating - I don't see what is so complicated about a column update of a few hundred thousand records, one-at-a-time - it should be easy to see what is causing the hang (something about autovacuuming), and also how to fix it.  Whereas the settings below seem to be working on production, I am not clear on the why, don't know how to improve them, and don't know how to create a working set of configurations on a dev server.  Your help is appreciated.

autovacuum = on
log_autovacuum_min_duration = 200
autovacuum_max_workers = 16
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 5000

#autovacuum_analyze_threshold = 500
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_multixact_freeze_max_age =

autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 2000
default_statistics_target = 100
maintenance_work_mem = 2920MB
checkpoint_completion_target = 0.8
effective_cache_size = 22GB
work_mem = 260MB
wal_buffers = 16MB
shared_buffers = 7680MB
min_wal_size = 80MB
max_wal_size = 2GB

I'd suggest you change your strategy.

  Specifically, one of our processes updates several columns one-at-a-time in such a table, and in the code log, I can see where it pauses and hangs, for up to 30 minutes, and then picks up later as if nothing happened, and updates many consecutive columns in the same with no such hang, within seconds.

One such update can easily double the size of your table, since each UPDATE is basically equivalent to one DELETE one INSERT, which leaves each deleted row occupying useless space until the table it is VACUUMed and that space can be reused for the next round of UPDATEs.

You can do:

Change the strategy and update as many columns as you can at once. Your process will be much quicker because the data will be accessed many less times.
Issue a VACUUM from your code after each UPDATE round, so that the unused spaced is available for the next UPDATE round.
Under some circumstances, doing the UPDATEs in batches of (let's say) 1.000, and committing after each batch might ease the sitution.

Any combination of the three techniques will probably ease the updates.


Which database is best for deeply embedded database or through C DLL? I want a deeply embedded database. Deeply embedded means that the server is started by an application and closed by the application with no tcp/ip or 0 port. The main features of consideration are:

At least 10 GB Database
Database encryption and search in encryption
Full text Search
Possible updating of database through Online [This is only additional benifit]
Must have C DLL

There are many options available like MySQL, Oracle, MSSQL. An opensource database would be great.

Have a look at PostgreSQL, it has unlimited database size, what appears to be many options for encryption, support for full text search and interfaces for several languages (C as well via libpg). It is also open source. See the about-page for PostgreSQL for more info.


Using Null With Variable I have this table structure and am attempting to return the two rows where loglevel is null.  How should the query be written in order to return this?

Based off the sample DDL Below I want 2 rows for names returned:

  Pink Pig and Green Glove

  Declare @stuff Table
  (name varchar(50), vendorname varchar(100), vendorid varchar(10), loglevel varchar(10))
  Insert Into @stuff (name, vendorname, vendorid, loglevel) Values
  ('Pink Pig', 'Vendor 1', '214ac', NULL), ('Red Hat', 'Vendor 1', '214ac', '3')
  ,('Blue Sox', 'Vendor 1', '214ac', '3'), ('Green Glove', 'Vendor 1', '214ac', NULL)
  ,('Green Lantern', 'Vendor 2', 'Er23', '1'), ('Grey Goose', 'Vendor 2', 'Er23', '1')
  ,('Fox Fire', 'Vendor 2', 'Er23', NULL), ('Flyfish', 'Vendor 2', 'Er23', NULL)

  Declare @vendorid varchar(10), @loglevel varchar(10)
  Set @vendorid = '214ac';
  Set @loglevel = NULL

  Select * FROM @stuff
  WHERE vendorid = @vendorID
  AND loglevel = @loglevel

Try this.

Select * FROM @stuff
  WHERE vendorid = @vendorID
  AND loglevel Is Null


Unusable index on PK - Oracle DBMS We are executing a large number of INSERT operations (140k circa) on a table during an ETL process and this error pops out:

Caught SQLException for insert into DMALM_USER_ROLES [...]
Caused by: java.sql.SQLException: ORA-01502: index 'DM_ALM.DM_ALM_USER_ROLES_PK' or partition of such index is in unusable state

The DBMS is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Any hint?

edit: after this process, the table DMALM_USER_ROLES is empty

You can rebuild an unusable index using

alter index DM_ALM.DM_ALM_USER_ROLES_PK rebuild;


Error in in sysft_Name while restoring database I have a .bak file which gives the following error while restoring:

  System.data.sqlclient.sqlerror:The path 'E:' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive.

But I verified that the directory is not read only. Anyone know how to restore the three files being restored ( .mdf,.ldf, and sysft)?

The script i had used for restoring database has been given below.

RESTORE DATABASE [new] FROM  DISK = N'E:\a\L11.bak' WITH  FILE = 1,  MOVE N'itest' TO N'E:\a\3.mdf',  MOVE N'itest_log' TO N'E:\a\4.ldf',  MOVE N'sysft_test' TO N'E:\a',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

I think you need a "\" at the end of the path.

RESTORE DATABASE [new] FROM DISK = N'E:\a\L11.bak' WITH FILE = 1, MOVE N'itest' TO N'E:\a\3.mdf', MOVE N'itest_log' TO N'E:\a\4.ldf', MOVE N'sysft_test' TO N'E:\a\', NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO


Get ids only where one id is null and other isn't I have a table as such where id's are repeating .

    +-------+------+
      ID      NUM
    +-------+------+
      1        50
      1        51
      2        52
      2        NULL
      3        NULL
      3        NULL
      4        53
      4        54
      4        NULL
      5        NULL
      6        55
      7        NULL
    +-------+------+

I want to get table where i only have the ids which have both null and non null values in the column labelled (num)

like so

 +------+-----+
   ID     NUM
 +------+-----+
   2      52
   2      NULL
   4      53
   4      54
   4      NULL
 +------+-----+

WITH cte AS ( SELECT id,
                     num,
                     COUNT(*) OVER (PARTITION BY id) total,
                     COUNT(num) OVER (PARTITION BY id) notnull
              FROM mytable )
SELECT id, num
FROM cte
WHERE notnull * (total - notnull) > 0

OR

WITH cte AS ( SELECT id,
                     num,
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num) mini,
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num DESC) maxi
              FROM mytable )
SELECT id, num
FROM cte
WHERE mini IS NULL
  AND maxi IS NOT NULL


Oracle backup and recovery using RMAN Can some please provide a quick guide for backup and recovery of Oracle 11 DB?
I just need to test it very quickly so i don't need something too fancy, but should be scheduled.

Thanks in advance.

Performing Backup and Recovery in Oracle® Database 2 Day DBA is what you are looking for.


How to disable all password requirements in Oracle? When I try to create a new database using DBCA it stops me because my password isn't complex enough. I am working with Oracle in a VirtualBox VM on my own laptop, so I don't need the password complexity restrictions Oracle imposes out of the box. Personally I don't care if my passwords in this environment are "password" or even a space. Unfortunately I can't seem to find a straightforward answer on how to simply disable password checks. What command do I need to run / switch do I need to flip to turn it off?

I'm on a Windows laptop with Windows 2012 in the VM if that is relevant. Thanks for any tips.

In DBCA, It will mark the password weakness as an error, push through with the "NEXT" button.

You will be warned again in a pop up box that you are making a serious mistake and are you sure that you want to continue.

Answer "Yes".

It should move on from there.

It's the same pretty much everywhere.

DBCA:

Grid Infrastructure:


distinct description I have the following database:

id | remote_id | title | description
-------------------------------------
1  | 12341234  | test  | test-description
2  | 4535234   | blah  | testestetetst
3  | 12341234  | test  | test-description2
4  | 3454656   | qsffq | testqfsfqgfgsdffgfd

I'm trying to select the description (and ONLY the description) of each unique remote_id. The 'winning' description should be the highest id. like this:

id | remote_id | title | description
-------------------------------------
2  | 4535234   | blah  | testestetetst
3  | 12341234  | test  | test-description2
4  | 3454656   | qsffq | testqfsfqgfgsdffgfd

using the following code returns each unique remote_id correctly

SELECT DISTINCT(remote_id) FROM table ORDER BY id DESC

but this returns the duplicates

SELECT DISTINCT(remote_id),description FROM table ORDER BY id DESC

How do can I filter distinct values on remote_id, but only select the description with the highest id in my query?

any help would be greatly appreciated!

EDIT 1: formatting

EDIT 2: using following version: 5.7.27-0ubuntu0.18.04.1

One way is to use a Derived Table (subquery) and get all the maximum id values for every unique remote_id value. We can then JOIN this subquery  to the original table to get the rows corresponding to highest id.

SELECT
  t.*
FROM table_name AS t
JOIN (SELECT
        remote_id,
        MAX(id) AS max_id
      FROM table_name
      GROUP BY remote_id) AS dt
  ON dt.remote_id = t.remote_id
     AND dt.max_id = t.id

Another way is to use a Correlated Subquery inside the WHERE clause. We will get the Max() id value for every remote_id and match against the same.

SELECT
  t.*
FROM table_name AS t
WHERE t.id = (SELECT MAX(t2.id)
              FROM table_name AS t2
              WHERE t2.remote_id = t.remote_id)


alter column size take very long time? I have a table with 45M rows (45 GB data space and 2GB Index space). I added a new column and it finished instantly.

alter table T add C char(25)

Then I found the size is too small so I run the following query.

alter table T alter column C varchar(2500)

And it runs one hour and is still running. sp_whoisactive shows (at the moment, still running)

reads: 48,000,000
writes: 5,000,000
physical reads: 3,900,000

Shouldn't it be really fast?

Assuming all previously-created columns are fixed-width like char and datetime, adding column C above just added it to the end of the fixed-width section of the record (effectively a meta-only change). However, recasting it as a varchar required it to be moved to the variable-width section of the record, forcing an implicit rebuild of the table. Paul Randal explains the internals of records in great detail in Inside the Storage Engine: Anatomy of a Data Record.


Slow Trigger performance when big batches I have an update trigger that inserts into auditing tables.  We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when developing).
Now facing reality, I need to find a solution...

I've been doing many tests and researches to try to figure out how to solve my issue of having a trigger perform poorly...
I've come to the conclusion that to minimize the bad performance of the "Table Insert" in the execution plan, I need to insert in smaller batches.

The question is:  Since I'm not sure of where all the different updates can come from, I'm trying to figure out how I can insert the auditing records in batches within the trigger?

example, The update of the main table for 1 million records would happen and call the trigger, which would insert 100 thousand records at a time in some type of loop.

Is this possible? If so, how do you suggest?
If not, how else can I improve the table insert of the execution plan?

Addition of test scripts to reproduce:

This is a simplified version of the real thing

-- drop trigger PriceHist_trig_U
-- drop table MyPriceTable
-- drop table price_history
Create Table MyPriceTable (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100)
CONSTRAINT [PRICE_TAB_P01] PRIMARY KEY CLUSTERED
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Declare @Id int
Set @Id = 1

While @Id <= 1000000
Begin
   insert into MyPriceTable values (right('000000000000' + CAST(@Id as nvarchar(10)),13),'Grp ' + CAST(@Id%10 as nvarchar(10)), @id%3, RAND()*(25-10)+10, 1, 'there are many other columns')
   Print @Id
   Set @Id = @Id + 1
End

-- Drop table   price_history
create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE NONCLUSTERED INDEX price_history_nc1 ON price_history
(
    HistoryDate ASC,
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)

go
Create TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE
AS
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate, ChangedColumns)
            SELECT INS.SKU,INS.PriceGroup,INS.PriceLevel,INS.Price,INS.Qty,INS.ManyOtherColumns, getdate(),
CASE WHEN update(Price) and INS.Price<>DEL.Price THEN 'Price-' ELSE '' END +
CASE WHEN update(Qty) and INS.Qty<>DEL.Qty THEN 'Qty-' ELSE '' END +
CASE WHEN update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns THEN 'other-' ELSE '' END
FROM INSERTED INS
JOIN DELETED DEL ON DEL.sku=INS.sku AND DEL.PriceGroup=INS.PriceGroup AND DEL.PriceLevel=INS.PriceLevel
WHERE  (update(Price) and INS.Price<>DEL.Price)
    OR (update(Qty) and INS.Qty<>DEL.Qty)
    OR (update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns)

/* tests */
update MyPriceTable set price = price-1

When I run this with the trigger disabled, it runs in 2 seconds.
When the Trigger is enabled, it took 32 seconds to complete.
The Execution Plan shows 98% on the "Table Insert"

I've been trying to figure out how to improve the table insert, but can't find anything concrete...

I've tried with a Clustered index and the performance is worse.

Any help would be appreciated

I'm putting this here since it's a bit long, but I don't think it should qualify as an answer.  There are no answers here, just observations and advice.

Short version, there isn't anything that can be done to make the query go faster and achieve the same results.  You need to change the process that is feeding data into the main table if you want batching to solve the issue.  Otherwise, you have to change the history process.

First, it's not the table insert that's slowing you down, it's the query that pulls the INSERTED and DELETED tables together.

Why Not?

The INSERTED and DELETED tables are heaps with no indexes.  Joining them together as you are doing requires two table scans and a sort.  The larger the operation, the more expensive this gets.

Batching Inside the Trigger

This won't help you here because the source tables are heaps.  You can't walk them without creating some sort of key to work with, and adding anything will just increase (at worst) or just increase complexity (best case) without improving anything.

Batching Outside the Trigger

If you can rearrange so that you are doing smaller update statements at a shot outside the trigger then the INSERTED/DELETED tables will be smaller, making the operation faster and less blocking, although total cost will be the same.

Solution(s)?

Any solution that addresses this will require a change in some fashion.  You don't mention your version of SQL, but if you are on 2016 or better, you could look into temporal tables. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

Alternatively, for this type of history table, where you only want to catch the UPDATES, I would do a straight insert of the DELETED table contents.  No additional comparisons or joins with the INSERTED table.  Your cost should be roughly the same as the insert, so minimal increase (I mean, double the I/O, but that's as minimal as you can get).

Then for looking at it, you just grab all the history records + the live record and you can see what changed and when.  It won't have that "ChangedColumns" list that your current version has, but you could put something like that together if you want.

Good luck.


Deleting large amounts of cascading records with minimal locking I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE records on several referring tables, which makes the deletion process very slow and time consuming, during which time some of the smaller but more frequently accessed tables are, apparently, locked (these are InnoDB tables).

Since these purges happen on regular intervals on a high-load database, I'm trying to figure out the best strategy to purge this data with minimal impact on other processes, i.e. with minimal locking of parent tables.

To give an example, I have a table structure similar to this:

+------------+    +------------+    +------------+    +-------------+
| accounts   |    | users      |    | messages   |    | attachments |
+------------+    +------------+    +------------+    +-------------+
| id         |    | id         |    | id         |    | id          |
| name       |    | account_id |    | user_id    |    | message_id  |
| is_deleted |    | name       |    +------------+    +-------------+
+------------+    +------------+

users.account_id REFERENCES accounts.id ON DELETE CASCADE
messages.user_id REFERENCES users.id ON DELETE CASCADE
attachments.message_id REFERENCES messages.id ON DELETE CASCADE

Assuming there are dozens of users per account, thousands of messages per user and potentially dozens attachments and other related data (e.g. tags, metadata etc.) per message - deleting an account will result in tens or hundreds of thousands of records to delete spanning multiple tables.

What would be a good strategy to take when purging data in such a case?

I had the same issue.  Deleting in batches helps, but still tends to lock up my Percona cluster because of the delay in replicating the delete to other nodes.

In my case, most of the cascaded deletes did not exist in practice - there could in theory be records in those tables, but in practice there weren't for most of the tables.  So what I did was query the schema to find which tables the cascade could affect, then for each item to delete:

SET FOREIGN_KEY_CHECKS=0;
Query the affected tables to find if there were any affected records; if so, delete them.
Delete the main record.
SET FOREIGN_KEY_CHECKS=1;

This meant that the actual locking was very much reduced.  In my case this enabled me to delete about an order of magnitude faster.

This approach is not atomic, but won't leave the tables in a bad state if there's a crash.


How to force SQL Serve *not* to maintain a log file I have a situation that I need some advice to overcome.

I have a SQL Server 2012 database that has been set to Simple recovery model. At times, my SQL Server transaction log file grows out of control and tries to consume all available disk space. It shows as empty in SQL Server, but continues to consume disk space (I understand why). I would like to know if there is a way to prevent SQL Server from maintaining the log file at all.

The application that lives in front of this database doesn't play well with the idea of recovering using a trans log backup. Our backup strategy is one of Full and Diff backups executed on a regular enough schedule that we are adequate covered. We don't need or want point in time recovery (hence simple recovery).

To test, I had the log file capped at 10mb, and 10% auto-growth turned on. My SQL Server load was working away at about 3.8mb trans file, when I initiated a full DB re-index (to try to approximate the load that our customers may put on the database). The log file went straight to 10mb+, and all transactions halted with a '9002' error.

I believe I understand the goal of the trans log in normal situations, but in my case, it is just taking up space and getting in the way. Is there a way to prevent SQL Server from using it?

Thank you in advance for your insight!

Catt11.

There is no way to prevent SQL Server from writing to the transaction log.

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log.

This means that a large transaction can still expand the log, if the current size of the log is too small.

You need to make sure that the drive your log is on has enough adequate free space for the log to expand, or (and this is what I recommend) expand your log file manually to a size that will avoid auto-growth. Do not regularly shrink your transaction log.

The best way to get the correct size for your transaction log is to simulate production traffic on a development server. Run queries (that would be executed against your production database) against your development database for a set period of time, and monitor the log.


How should I setup a table of temporary/transactional items that I only need for a few days or weeks? We have created a table in Sql Server 2012 that we use to store temporary records of transactions with our integration partners. These records can be text, XML or BLOBs. They're not actually used for anything by our software but are for debugging, recovery and investigation purposes. After a certain age they are deleted.

As volume has increased recently, this table has grown immensely in size (50GB at the moment, individual rows are as much as 25MB). This is not a problem in itself. However, the deletion scripts that clear out old records are no longer running to completion in reasonable amounts of time.

Is there a better way to store and clean up this data? Would partitioning be useful for this? Is there some other technology that would help manage a rolling date range of large data?

First, check execution plan for DELETE - may be you'll get additional ideas.

Second, I suggest you to choose from two standard patterns for such tables.

Use DELETE TOP(N) to remove data. N should be chosen
experimentally.
Use set of tables (e.g. 10 tables) to store data by day or week and "rotate" it periodically. In this case you can TRUNCATE "old" tables which is much faster than DELETE.


Where is this mystery trace going (SQL Server 2000)? We had a sudden disk usage explosion this morning and the SQL monitor has no useful query information. I decided to check on any potential traces that could have captured something.

SELECT * FROM :: fn_trace_getinfo(default)

gives me

traceid     property    value
-------     -------     ------
1           1           1
1           2           NULL
1           3           5
1           4           NULL
1           5           1

Property #2 should be the file name to which the trace is going but, as you can tell, it's NULL.

It's running, and just to confirm, I did this which confirms that the trace is running, has no stop condition, grows max file size of 5MB (default).

Is there a way to determine the destination of this trace, or is it a known location and my Google-FU is broken?

NULL means it's using the rowset provider for trace I/O. See for reference the below article:
Server-Side Tracing and Collection (section "Querying Server-Side Trace Metadata")

This query returns the trace status, which will be 1 (started) or 0 (stopped); the server-side path to the trace file (or NULL if the trace is using the rowset provider); the maximum file size (or again, NULL in the case of the rowset provider); information about how many buffers of what size are in use for processing the I/O; the number of events captured; and the number of dropped events (in this case, NULL if your trace is using the file provider).
Note For readers migrating from SQL Server 2000, note that the sys.traces view replaces the older fn_trace_getinfo function. This older function returns only a small subset of the data returned by the sys.traces view, so it’s definitely better to use the view going forward.

For details on rowset provider see for example:
SQL Trace Architecture and Terminology

The rowset provider, on the other hand, is not designed to make any data loss guarantees. If data is not being consumed quickly enough and its internal buffers fill, it waits up to 20 seconds before it begins jettisoning events in order to free buffers to get things moving. The SQL Server Profiler client tool will send a special error message if events are getting dropped, but you can also find out if you’re headed in that direction by monitoring SQL Server’s TRACEWRITE wait type, which is incremented as threads are waiting for buffers to free up.


Poor performance in my pgpool cluster I've configured a postgres cluster with pgpool2 using the following link.
The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for example:

pgbench -c 16 -j 16 -T 600 -S bench2 -h "ONE SINGLE POSTGRES NODE" -p
5432 number of transactions actually processed: 7752147 tps =
12920.095988 (including connections establishing) tps = 12921.661448 (excluding connections establishing)
pgbench -c 16 -j 16 -T 600 -S bench2 -h "PGPOOLNODE" -p 5432 number of
transactions actually processed: 389800 tps = 648.857810 (including
connections establishing) tps = 648.886713 (excluding connections
establishing)
pgbench -c 16 -j 16 -T 600 -S bench2 -h "MASTERNODE"-p 5432 number of
transactions actually processed: 7093473 tps = 11822.379159 (including
connections establishing) tps = 11823.337051 (excluding connections
establishing)

Each node is configured default.
All nodes are identical virtaul machines and over the same network.
This is my pgpool.conf:
listen_addresses = '*'
port = 5432
socket_dir = '/var/run/postgresql'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
backend_hostname0 = '192.168.1.177'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.1.175'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/9.1/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '192.168.1.176'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/9.1/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = off
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'stderr'
print_timestamp = on
log_connections = on
log_hostname = off
log_statement = on
log_per_node_statement = on
log_standby_delay = 'none'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 0
pid_file_name = '/var/run/postgresql/pgpool.pid'
logdir = '/var/log/postgresql'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'nextval,setval'
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 0
sr_check_user = 'ZZZ'
sr_check_password = 'YYY'
delay_threshold = 0
follow_master_command = ''
parallel_mode = off
enable_query_cache = off
pgpool2_hostname = ''
system_db_hostname  = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = 'zzz'
health_check_period = 0
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = 'zzz'
failover_command = '/var/lib/postgresql/bin/failover.sh %d %M %m'
failback_command = ''
fail_over_on_backend_error = on
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
relcache_expire = 0

Any suggestion? Thanks

Relationship between max pool, num init children, and max connections

max_pool parameter configures how many connections to cache per child. So if num_init_children is configured to 100, and max_pool is configured to 3, then pgpool can potentially open 300 (=3*100) connections to the backend database.
A child process opens a new backend connection only if the requested [user,database] pair is not already in the cache. So if the application uses only one user to connect to only one database, say [pguser1,pgdb1], then each child will continue to reuse the first connection and will never open a second connection, so in effect pgpool will open no more than 100 backend connections even though max_pool is set to 3

Your config
num_init_children = 32
max_pool = 4

That means You pgpool instance is able to open up to 128 connections (4*32). But pgbench is using only one user and is executed with concurrency 16. According to second quoted paragraph above pgpool won't open a new backend connection, because there is already user,database combination in the connection cache.
Also number of tps in pgpool test is ~16 times lower (a little bit more than that) then the other two tests.
Unfortunately I'm not so familiar with pgpool and I don't know how to improve the performance.


How to output string values in single quotes? I'm trying to convert values stored in a json into its string representation.

The #>> operator works for this purpose for numbers, e.g.:

SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'{a,2}';

gives

 ?column?
------
 3
(1 row)

But for string values, the text representation (6 in the example below) does not have single quotes around it, so that one cannot tell that the stored value was a string:

SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'{b,2}';
 ?column?
----------
 6
(1 row)

How to use a function or expression to let PostgreSQL output '6' instead of 6 in the second example above (and output 3 in the first example)?

(This is with PostgreSQL 12)

-- UPDATE --

@McNets

To clarify, I am interested in getting the string representation in quotes, so that after decomposing a jsonb into its key value pairs, one can use them with inverse functions like jsonb_build_object (so to speak) to reconstruct the original jsonb.

jsonb_each_text and the #>> apparently aren't good enough because they drop the single quotes.

Rather than retrieving the value as text with #>>, use #> to retrieve it as json and cast it to text afterwards:

SELECT ('{"a":[1,2,3],"b":["4","5","6"]}'::json#>'{b,2}')::text;

 text
------
 "6"
(1 row)

There is also the json_typeof function that can tell you what JSON type the value has:

SELECT json_typeof('{"a":[1,2,3],"b":["4","5","6"]}'::json#>'{b,2}');

 json_typeof
-------------
 string
(1 row)


Package Check Failed: Oracle DB 11g on RHEL 7 Trying to install Oracle DB 11g EE on RHEL 7. Installed the necessary packages as mentioned in the below link (Section 6.1).

But I am stuck at the package check step, which fails because it couldn't find earlier versions of the packages.

Ex of one of the package error:

![Error Snippet of one particular package]

Expected Value: libaio-0.3.105 (i386)

Also, when I try to install the required package, the repo doesn't find those versions. If I ignore, I am getting the below error.

![Snippet: Stuck here]

Error in Invoking target "client_sharedlib" of makefile '<path>/product/11.2.0/db_1/rdbms/lib/ins_rdbms.mk'.

Stuck here. Please help.

List of other packages getting failed:

libaio-0.3.105
compat-libstdc++-33-3.3.3
compat-libstdc++-33-3.3.3 (twice)
libaio-devel-0.3.105
libgcc-3.4.6
libstdc++-3.4.6
unixODBC-2.2.11
unixODBC-devel-2.2.11
pdksh-5.2.14

Exact versions:

OracleDB: Oracle Database 11g Release 2 (11.2.0)
RHEL: 3.10.0-123.el7.x86_64 - Red Hat Enterprise Linux Server release 7.0 (Maipo)

From My Oracle support, Doc ID 1304727.1

  Red Hat Enterprise Linux 7 - 11.2.0.4 only

  Oracle Unbreakable Enterprise Kernel:3.8.13-35.3.1.el7uek.x86_64 or higher
  Red Hat compatible kernel: 3.10.0-123.el7.x86_64 or higher

So, unless you are installing 11.2.0.4, you will have compatibility issues on RHEL7. Either install 11.2.0.4 and up on RHEL7, or use a lower compatible version.

More information:

  Red Hat Enterprise Linux 6.x  - Database 11.2.0.3 and higher

  Red Hat kernel 2.6.32-71.el6.x86_64 or later
  Oracle Unbreakable Enterprise Kernel (UEK R1) 2.6.32-100.28.5.el6.x86_64 or later