SQL - Convert text to a list of numeric values

While this question, for the most part, has been asked and answered what at least feels like hundreds of times on here and StackOverflow, because it concerns int values only I will say that:

Given that you are dealing with only INT values (as the values contained in the delimited string), the fastest way I have come across is to use a splitter that is a) SQLCLR, and b) specialized to only return integers. Since we know what the output type should, in the end be, there is no need to waste the extra cycles on passing back a string (slower than passing back an INT) only to convert that string into an INT. So, for this use case, I created a function that does just this: splits a string and passes back a table of INT values. My testing (so far) shows it to be quite a bit faster than the standard splitter that passes back a table of strings (for the same input values, of course). You can find a pre-done SQLCLR function of this in the SQL# library (that I created). The function is SplitInts. There is also another version of it named SplitInts4k that should be used when the input string is guaranteed to be no more than 4000 characters. The difference is that the non-4k version uses NVARCHAR(MAX) as the input parameter datatype, and that is noticeably slower than NVARCHAR(4000).

You could use it as follows, though it is unclear, if table0 really is a Table and not an input parameter, how it relates to table1 if table0 has more than 1 row:

SELECT t1.*
FROM table1 t1
WHERE [ID] IN (
         SELECT [SplitVal]
         FROM SQL#.String_SplitInts4k(table0.params, N',', 1, 0)
            );

Or perhaps:

SELECT t1.*
FROM table1 t1
INNER JOIN SQL#.String_SplitInts4k(@table0.params, N',', 1, 0) ints
        ON ints.[SplitVal] = t1.[ID];


The replica set was setup with the internal AWS addresses. So when python connected with MongoReplicaSetClient and the server sent back the addresses it couldn't find the other members of the replica set.

You can update the replica set using rs.conf() to get the config and then rs.reconfig() to set your new configuration.

Or, as I did, add the internal AWS address to the server hosts file, which I was able to do because it was not the internal AWS IP that was used.


Can sequential queries result in concurrent transactions?

To answer your question in the title: No.

Consecutive queries in the same session (same transaction or not) are never concurrent. And there can only be one transaction at a time in the same session. Concurrency issues are impossible that way, those only happen between transactions in multiple sessions.

One possible reason for your error message might be a glitch in your function, which can be simplified:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id bigint
                                            , _source_client_id bigint)
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY
   DELETE FROM message m
   USING (
      SELECT id
      FROM   message
      WHERE  queue_id = _queue_id
      AND    receiver_client_id IN (_source_client_id, -1)
      ORDER  BY insertion_time
      LIMIT  1
      ) m1
   WHERE  m.id = m1.id
   RETURNING m.content;

   IF NOT FOUND THEN
      RAISE 'No message available.';
   END IF;
 END
$func$ LANGUAGE plpgsql;

Your original check:

IF (SELECT rv IS NULL) THEN

could be simplified to:

IF rv IS NULL THEN

But that's still no good. It fires when message.content is NULL, not only when no row has been found. The table definition is missing in the question, so we don't know if that's possible.

Either way, the special variable FOUND is the better tool for the purpose. My audited version is also simpler and faster. Note that plpgsql functions do not return results until the end of the function or RETURN is reached. We can just return rows and still cancel the result later if need be. There is an example in the manual, which is a spin-off from this related question on SO:

FUNCTION syntax error


What are good ways to store decorated objects in a relational database without adding a field for every possible property?

It is not clear in your question whether you need to use these dynamic properties in your database queries. I'll assume you won't.

With this assumption, I can actually point you to an implementation I've used in the past, although I'd add a disclaimer: in these days, you really shouldn't do this unless you're really stuck to your current DB and cannot, at all, move the persistence of this collection of objects to a NoSQL DB, which is where it seems to belong.

Well then, to the answer:
You can make use of an object serialization engine, such as the one in Java (maybe you're using another language, but I'm sure it has some way of doing the same thing), then create a database table where you store the serialized objects. Then, in order to be able to query this table, you have the following options:

If you have common properties between all objects, you can create a column for each property that is needed in queries, then populate them based on the object being serialized whenever you have an insert or update.
If you need to query by using some properties that only exists in a few objects, you can create another table with a foreign key to the unique ID of the object in the original table, then add the columns needed for querying.

You can mix the options above as you wish - in my case, I ended up with  one table just for storing the serialized objects, given unique IDs, then multiple tables for querying. There was some duplication of data, but it was done based on performance measurements of the many reports that had to run on these objects. Your mileage may vary.

In the end, whichever query structure you choose will give you a relatively good mix of the advantages of relational DB queries while retaining the object structure used by your program, since the deserialization process should recreate the object (or object graph, given the use of decorators), just as it was before being serialized.

However, I feel like I should stress this again: I do not believe that this should be done in this day and age, unless you have real economic/political/whatever reasons to do so.


Does the 'ORDER BY' clause of a subquery pass through to the main results (POSTGRES)

No, the order by or the sub-query will not influence the order of the outside result.

If at all, it might be influenced by the order by of the window function.

But you can never rely on that.

If you need a specific order for the outer query that you can rely on, you have to use an order by there as well.


Sybase ASE: How to know if a device has free space to extend a database on it

sp_helpdevice does list all allocated fragments on a device when you specify the device name. This includes the size of each fragment, but indeed not the free/used metrics. Those would not be difficult to write yourself however.


Mysql subquery not recognizing the subquery join table field

Your derived table is missing the extra field it is looking for try this:

SELECT journeydata.dateTime,
alarmDetails
FROM
journeydata
LEFT JOIN
    ( SELECT
         GROUP_CONCAT( DISTINCT(
              IF(fence.fenceName IS NULL, alarmType.alarmName,
                   CONCAT(alarmType.alarmName,
                        '(',fence.fenceName,')'))
         )) AS alarmDetails,
         alarm.journeydataID as journeydataID   -- <<< ADDED
      FROM alarm
      JOIN alarmType
      ON alarm.alarmTypeID = alarmType.alarmTypeID
      LEFT JOIN fence
      ON alarm.fenceID=fence.fenceID
      WHERE alarm.associateID=6
      GROUP BY alarm.journeydataID
    ) AS alarmgroup
    ON journeydata.journeydataID=alarmgroup.journeydataID

WHERE journeydata.associateID=6

There might be more that needs fixing, but I would need a little time to look over the query and information you have provided.   Try this for now.

EDIT: Added the (untested) distinct grouping and formatting of alarmDetails as requested.


If a table is large and growing fast, clustered index might become too expensive to maintain since the DB server has to reshuffle all the data while rebalancing the tree, not only nodes with key values. It might significantly affect speed of data modification queries.
Clustered index stores table data right in the index (a tree node contains the whole record, not only the key). So when you rebalance the tree of this index, you also have to physically move records. In comparison, non-clustered indexes store trees separately from the main data and only use pointers to physical placement of corresponding records. So they are much cheaper to rebalance.

For most DBMS (TokuDB for MySQL/MariaDB/Percona) is an exception) there can be only one clustered index per table because it defines the physical data placement. So, if you need several indexes on a table, you have to use non-clustered ones.


On EC2 Why do I need individual EBS volumes for journal, log, and data?

You can combine it on 1 disk, if you wish. Not obligated to split.

The reason is to optimize the disk for the purpose. Journal is a capped collection that just writes in sequence. So less IOPS needed. And logs are logs, just adding information.  And then Data, well, reading jumps around a lot, and writing sometimes also, filling up gaps that were freed. And while journal and log are written away on other disks, the data disk doesn't lose time on that. Every little bit can help on intensive systems. The next step then is Replication and Sharding to spread the load.

On the https://university.mongodb.com, you can get more information about this if you are interested. Following M202 MongoDB Advanced Deployment and Operations now, what offers some specific information to optimize.


Is Windows 10 compatible with SQL Server 2014 Developer MDS?

I think it is fine; the documentation was just written long before Windows 10 was released and hasn't been updated (I've sent in a note that it should be corrected).

In fact it is listed as fully supported here, with no special caveats for MDS. I have never heard of any components of SQL Server 2014 having any compatibility issues with Windows 10.


How to avoid lagging when enabling log-slave-updates?

As your main concern is to have an incremental backup solution, you can change the follow variables:
Changes:
innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.
innodb_doublewrite - Disable the innodb double write
sync_binlog - Disable syn binlog
long_query_time - increase the long query time or disable it (at the moment you are logging every single query)
Why slave delays?
Other think is, it looks to be a busy master, on master you are able to handle 768 connections at the same time, it means that 768 thread can be writing data to MySQL, and the server (if not having looking issues) will execute all these queries at same time. In the other end(slave), you have a single replication thread to execute all queries received from master, and if a query take 10 seconds to execute on slave, the only thread available to execute changes from master will be busy, in this case, some delay are expected.
In your case you also use the slave as a read only copy of the master, then the replication thread may also need to wait some other thread release lock on some rows to apply the changes.


Packaging SQL Server Express 2012 install won't install due to user rights

It may be that you do not have enough rights granted for your installation.  See:

SQL Server installation fails if the Setup account does not have certain user rights

Partial comments under Cause:

  This behavior is by design. In addition to adding the user account
  that is running the Setup as a local administrator, the Setup user
  account requires the following default user rights for the Setup to be
  completed successfully

 -  Local Policy Object Display Name    User Right
 -  Backup files and directories        SeBackupPrivilege
 -  Debug Programs                      SeDebugPrivilege
 -  Manage auditing and security log    SeSecurityPrivilege

Also review the comments at:

InstallShield Limited Edition
 The Install Shield link also suggests ClickOnce Security and Deployment.
 Install SQL Server 2012 from the Installation Wizard (Setup)

If you do not have enough privileges then you probably need to either (1) pursue the extra privileges or (2) have someone with those privileges handle the install.


I would put my money on the new features of MySQL 8.0, like Atomic DDL
https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. The transaction is either committed, with applicable changes persisted to the data dictionary, storage engine, and binary log, or is rolled back, even if the server halts during the operation.
Atomic DDL is made possible by the introduction of the MySQL data dictionary in MySQL 8.0. In earlier MySQL versions, metadata was stored in metadata files, nontransactional tables, and storage engine-specific dictionaries, which necessitated intermediate commits. Centralized, transactional metadata storage provided by the MySQL data dictionary removed this barrier, making it possible to restructure DDL statement operations into atomic transactions.

Note the change to a data dictionary from metadata files.


How do you skip databases with Ola.Hallengren's scripts?

Sure can.  In the job, you'll see @Databases='USER_DATABASES'.  Just switch it to @Databases='USER_DATABASES,-Db1' any that you don't need to be optimized, place a '-' in front of.

Take a look at the parameters list listed on his website:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


MySQL: Safe to kill transaction that is stuck in "Waiting for table metadata lock"

Yes, it's safe. Your alter table query hasn't even started.
However, the waiting for table metadata lock means, that there's another transaction operating on that table for a long time and you should rather kill that one to solve the problem. (read more about it here: 8.11.4 Metadata Locking)

To find this transaction, this thread is probably helpful: How do I find which transaction is causing a “Waiting for table metadata lock” state?


Where does SQL_Variant datatype come from in select statement using LOGINPROPERTY

The return value depends on the input value, but the result set returns sql_variant to accommodate all possible return types.

You'll have to make some adjustments to your code.

INSERT INTO #PasswordChange
     ( name, lastPWChangeTS, PWhash )
SELECT  name,
        CONVERT(DATETIME, ISNULL(loginproperty([name], 'PASSWORDLASTSETTIME'), 0)) AS LastChangeTime,
        CONVERT(VARBINARY(26), LOGINPROPERTY(name, 'PASSWORDHASH')) AS PWhash
FROM    sys.syslogins
WHERE   CONVERT(DATETIME, loginproperty([name], 'PASSWORDLASTSETTIME')) > DATEADD(HOUR, -24, GETDATE());


Is there any way to fix database, without restore?

No, you will have to do a restore.  Please see this reference on how to do file and filegroup restores.


How to relate Agencies at the State, County, and Local Government levels

Is there anything wrong with having one table for agencies and a column for the agency name and three columns for governments. State, county and locality. Assuming this is a national database, the government tables look OK.

This way if you wanted every county agency in Florida you would select where state is Florida and county_id is not null and locality_id is null or something like that. Or use blanks instead of NULL.


Get the Text of the currently running SQL?

There is no way that a query can output its own text, that I am aware of, but you can certainly capture it using sys.dm_exec_requests.

You can try this:

-- Query to Run
SELECT 100 / 10

-- Query Text Capture
DECLARE @Query NVARCHAR( MAX )
SET @Query = ( SELECT text /*, query_plan*/
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
        CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
    WHERE session_id = @@SPID );
SELECT @Query

Or inside SP:

CREATE PROCEDURE Test
AS
    SET NOCOUNT ON

    SELECT 100 / 10

    DECLARE @Query NVARCHAR( MAX )
    SET @Query = ( SELECT text /*, query_plan*/
        FROM sys.dm_exec_requests AS er
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
            CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
        WHERE session_id = @@SPID );

    SELECT @Query
GO
EXEC Test

I think you would be better served by SQL Trace though.

For a procedure, you can use sp_helptext and equivalents to get its text. I don't think you would need to capture SP text at the point execution as SPs don't get redefined often. Capturing SP name should be enough.


Which areas of a DBA's knowledge should a developer delve into?

The two things I deal with every day.

Disaster recovery.
Performance tuning. (Both for individual queries, and for the dbms itself.)

Your disaster recovery plan needs to be

scripted,
tested, and
practiced.

I'm using scripted in the sense of something an actor would follow, not something written in Python. It should tell everyone who needs to be involved exactly what to do. (And often, exactly what to say, too.)

Performance tuning for queries includes understanding keys, indexes, and normalization. (Often "tuning" problems are actually structural problems.)


Column name on NOT NULL constaint violation not shown

There is a security consequence to setting SQL92_SECURITY to false.
From https://www.stigviewer.com/stig/oracle_database_12c/2020-04-01/finding/V-61429

The configuration option SQL92_SECURITY specifies whether table-level
SELECT privileges are required to execute an update or delete that
references table column values. If this option is disabled (set to
FALSE), the UPDATE privilege can be used to determine values that
should require SELECT privileges. The SQL92_SECURITY setting of TRUE
prevents the exploitation of user credentials with only DELETE or
UPDATE privileges on a table from being able to derive column values
in that table by performing a series of update/delete statements using
a where clause, and rolling back the change.
In the following example, with SQL92_SECURITY set to FALSE, a user
with only delete privilege on the scott.emp table is able to derive
that there is one employee with a salary greater than 3000. With
SQL92_SECURITY set to TRUE, that user is prevented from attempting to
derive a value.

SQL92_SECURITY = FALSE
SQL> delete from scott.emp where sal > 3000;

1 row deleted

SQL> rollback;

Rollback complete

SQL92_SECURITY = TRUE
SQL> delete from scott.emp where sal > 3000;

delete from scott.emp where sal > 3000 *

ERROR at line 1: ORA-01031: insufficient privileges

If this is a concern for you, then consider using a PL/SQL API package to make changes to your table, rather than granting access directly to the user. See "Guarding Your Data Behind a Hard Shell PL/SQL API" for reference. Also see here: https://www.youtube.com/watch?reload=9&v=GZUgey3hwyI. A singleton package with definers rights could raise the error back to the app without granting excessive privileges or altering the overall security posture of your system.


MySQL Workbench: How to detach reverse-engineered EER diagram from existing database?

It looks like the table is deleted in the "model" only. The original database is not harmed. One also can filter tables via the reengineering wizard in advance.


SQL query to check a table against a "rules table"

I'm not real familiar with Access syntax, but I think the general idea you're after would be something like this... Instead of filtering for only records that don't meet your requirements, add a calculation to your select list that indicates if the record/ruleset combination resulted in a pass or fail.  Then group by your other selected columns, and and look for groups that have no passing records and at least one failing record.


I've never used MAMP or even heard of it until I read this question, but if both MySQL installations are the same base version of MySQL you can probably fix this by copying the databases from one installation's data directory to the other.

First, run the following in MySQL in each MySQL installation:

SHOW GLOBAL VARIABLES LIKE 'datadir';

That will tell you where the databases are for each installation.

Next, stop both MySQL installations. This is important because if they are running, you won't be able to make a consistent copy of the data.

Next, make a backup of the MAMP Pro MySQL installation's datadir. You should be able to do this by running the following in a shell:

cp -a <MAMP Pro datadir> <new backup directory>

Now copy the old databases to the new:

cp -a <MAMP datadir> <MAMP Pro datadir>

Finally, start the MAMP Pro instance and confirm whether the databases are present.

You could also do a backup and restore with mysqldump, but a copy is probably simpler especially since this is only for development.


ORA-1033: ORACLE initialization or shutdown in progress

For your database to open and mount, first ensure that you are logged in as the user oracle
Then start by checking whether your listener has been started, using the command
lsnrctl status

Also, please provide the details of the environment in which you are running Oracle, i.e as an active/standby in dataguard, or standalone, and also what version of Oracle you are using. Also check your alertlog /opt/oracle/admin/databasename/bdump/alert_databasename.log if there are any clues here.


By having three tables in the database (e.g. activities, user, and user_activities) and a simple LEFT JOIN SQL query as the one below can be executed:

SELECT SELECT user.name AS user_name , activities.name AS activity_name
FROM user_activities
LEFT JOIN user ON user.userid = user_activities.user_id
LEFT JOIN activities ON activities.activityid = user_activities.activity_id

As for your approach:

Activities table,

id - Unique Stream Item ID
user_id - ID of the user who created the stream item
object_id - Internal ID of the object (currently either the buckets, uploads, or documents ID)
action_name - The action taken against the object
activity_date - Timestamp that the action was created.
hidden - Boolean of if the user has chosen to hide the item.
privacy - An id to a table with Privacy Groups Options


Select Count(*) FROM with-clause-named table order by a, b not working

Unless there is just something specific to Oracle syntax, my suspicion is that you have more than columnA and columnB in your derived table t. If so, you'll need to group by those columns, apply an aggregate to them or do not ask for them (replace t.* with t.columnA, t.columnB)


Optimize query execution based on two datetime columns

Thanks to everyone who took the time to respond.
I got it fixed in a different way.
Since the values can't overlap and mysql is efficient using one single index for ranges, what i did was find the first col1 and last col1 and use a select between. This turned out really fast because mysql now analyses only the lines in the range.

As for the aggregates which were causing problems I still had to go round them anyways because I had to execute it separately or otherwise MySQL optimizer was really inefficient. The way I did it was by selecting the results into a temporary memory table and then loop those results (cursor wasn't a choice because I already had a loop) and doing the 3 aggregate functions at once. This is was the final result (just the loop part):

myLoop: LOOP

IF (SELECT COUNT(*) FROM (SELECT
    @CarrId := carrier_id,
    @FistVTime := firstValidTIME,
    @LastVTime := lastValidTIME
FROM startStopReportTable
WHERE maxSpeed = -1) AS dummyTbl) < 1 THEN
    LEAVE myLoop;
END IF;

UPDATE startStopReportTable AS t0,
    (SELECT
        SUM(l0.distance) AS distance,
        AVG(l0.speed) AS avgspeed,
        MAX(l0.speed) AS maxspeed
        FROM
            pcoarg AS l0
        WHERE
            l0.carrier_id = @CarrId
        AND
            l0.lastValidTIME BETWEEN @FistVTime AND @LastVTime
    ) AS t1
SET
    t0.distance = t1.distance,
    t0.averageSpeed = t1.avgspeed,
    t0.maxSpeed = t1.maxspeed
WHERE t0.carrier_id = @CarrId AND t0.firstValidTIME = @FistVTime AND t0.lastValidTIME = @LastVTime;
END LOOP;

LastValidTIME is col1
startStopReportTable is temporary table

Profiling results:

96  0.00058100  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...
97  0.00045100  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...
98  0.00094900  UPDATE startStopReportTable AS t0,
      (SELECT
       SUM(l0.distance) AS distance,
       AVG(l0.speed) AS avgspeed,
       MAX(l0.speed) AS maxspeed
       FROM
        pcoarg AS l0
       WHERE
        l0.carrier_id = @CarrId
       AND
        l0.lastValidTI...

It's really a shame that the optimizer can't combine sub query aggregates in one query while it can combine them in one query.


Users database to store access levels, can I move the non-essential information to another table?

Users.

id, student_number, password, first_name, last_name, address, email, mothers_name,
mothers_occupation, fathers_name, fathers_occupation, religion, civil_status

Roles
id, name

UserRoleMembership
user_id, role_id

If the problem is that you actually do want to validate some of those fields - but only for some users, I would use inheritance:

users
id, username, password

student_user
id,
user_id,
last_name required,
first_name required,
etc.

admin_user
id,
user_id,
admin_info_fields...


Indexing a date field on MongoDB - best practice

In MongoDB dates are stored as BSON Dates and this type is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This means that dates and date-time values are stored in the same format, so there is no difference in indexing on either of them.


Does PostgreSQL Explain Analyze Execution Time consider Fetching time

Fetching performance is dependant of the client performance, available memory, cpu usage, network performance... I don't see how EXPLAIN ANALYZE could take this into account. So I would say your correct.


While the syntax of your trigger is not quite correct, this is not the source of the error. The main problem...

  Msg 8197, Level 16, State 4, Procedure ComputeGrade
  The object 'dbo.StudentMarks' does not exist or is invalid for this operation.

...is exactly what the error message states. Either you have spelled the name of the table wrong, you are in the wrong database, or it is in a schema other than dbo. To locate it, make sure you are in the right database, then run:

SELECT s.name, t.name
  FROM sys.schemas AS s
  INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
  WHERE t.name LIKE N'StudentMark%';

Now, on to the trigger. The main problem is this: which row do you expect to process in the case of a multi-row insert or update? Assigning a value from inserted to a variable assigns exactly one value. Which one will it be? The first one? The last one? Random? You should re-write it using a set-based operation, for example:

CREATE TRIGGER dbo.ComputeGrade
   ON  dbo.StudentMarks
   AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE m
      SET Grade = CASE
        WHEN MarkObtained < 50 THEN 'Fail'
        WHEN MarkObtained < 65 THEN 'Fair'
        WHEN MarkObtained < 75 THEN 'Good'
        WHEN MarkObtained < 85 THEN 'Very Good'
        ELSE 'Excellent' END
   FROM dbo.StudentMarks AS m
   INNER JOIN inserted AS i
   ON m.[key column] = i.[key column];
END

But even better than a trigger would be a computed column.

ALTER TABLE dbo.StudentMarks DROP COLUMN Grade;

ALTER TABLE dbo.StudentMarks ADD Grade AS
  CONVERT(VARCHAR(10), CASE
        WHEN MarkObtained < 50 THEN 'Fail'
        WHEN MarkObtained < 65 THEN 'Fair'
        WHEN MarkObtained < 75 THEN 'Good'
        WHEN MarkObtained < 85 THEN 'Very Good'
        ELSE 'Excellent' END);

This way there is no trigger to maintain (or keep enabled) and all data is automatically synced at all times.


Oracle table is occupying too much space in disk

The culprit was Informatica bulk mode insertion and the segment configuration in Oracle.

This is what I found out, by analyzing the problem with dbms_rowid:

Informatica bulk mode insert data in blocks of 10,000 rows. My over 1 million records required 101 such blocks.
Informatica writes a block of rows in one segment and the next block in the next segment.
Segments were configured to 100 MB. 101 segments were 10 GB.
Each 100 MB segment consist of 12,800 blocks of 8 KB. Each block could accommodate between 29 and 33 records. 10,000 records needed nearly 323 blocks, give or take. So there was 12,477 unused blocks in each segment.

So, the possible solutions are:

Configure Informatica for normal mode insertion. It inserts a record letting Oracle handling the insertion. There is supposed to be slower but it is not significantly slower, even for over 1 million records.
Configure Informatica to insert larger blocks. I haven't try it and I am not sure it is possible.
Configure a smaller segment size in Oracle. For this particular table, a segment size of 3 MB or 4 MB might have been enough.


MySQL Workbench - store a query to apply to various schemas

In addition to what Rick wrote:

If you just wanna have an ad hoc solution (not something you frequently use) you could also just have your query in the editor without a schema and sequentially make each of the schemas you want to process the default schema by double clicking on it's name in the schema tree. I.e. make first one default, run the query. Make the next one default and run the query, and so on.


Executing a stored procedure on remote database

You don't need to create linked logins or anything like that. For a local database you can just add the user to DB_A from the login, then grant exec to the two procedures. First:

CREATE DATABASE DB_A;
GO
CREATE DATABASE DB_B;
GO

-- create a login:
CREATE LOGIN DBB_Owner WITH PASSWORD = 'foo',
  DEFAULT_DATABASE = DB_B,
  CHECK_POLICY = OFF;
GO

-- make DBB_Owner the DB_B owner:
-- step is not necessary, just trying to match your scenario
ALTER AUTHORIZATION ON DATABASE::DB_B TO DBB_Owner;
GO

USE DB_A;
GO

-- create a local user from the server login:
CREATE USER DBB_Owner FROM LOGIN DBB_Owner;
GO

Now create three procedures. For the first two, give explicit exec rights to DBB_Owner, and don't allow them any permissions on the third.

CREATE PROCEDURE dbo.Proc1
AS
    SELECT 'Yes! - from Proc1';
GO
CREATE PROCEDURE dbo.Proc2
AS
    SELECT 'Yes! - from Proc2';
GO
CREATE PROCEDURE dbo.Proc3
AS
    SELECT 'No! - from Proc3';
GO

GRANT EXECUTE ON dbo.Proc1 TO DBB_Owner;
GRANT EXECUTE ON dbo.Proc2 TO DBB_Owner;

Now connect to the server as DBB_Owner with the password foo, and try to execute the three procedures:

EXEC DB_A.dbo.Proc1;
EXEC DB_A.dbo.Proc2;
EXEC DB_A.dbo.Proc3;

--or

USE DB_A;
EXEC dbo.Proc1;
EXEC dbo.Proc2;
EXEC dbo.Proc3;

Results:

Yes! - from Proc1
Yes! - from Proc2
Msg 229, Level 14, State 5, Procedure Proc3, Line 1
The EXECUTE permission was denied on the object 'Proc3', database 'DB_A', schema 'dbo'.


How to scale with MySQL (when not ready to scale properly)

PARTITIONing does not save space.  Each partition has 4M-7M of "free" space.  That can add up.  Partitioning can be used for removing data from the server (or moving to another server) "old" data.  More discussion:  http://mysql.rjweb.org/doc.php/partitionmaint

Nor is any ad hoc partitioning likely to help.  That is splitting tables up, moving between databases (I'm talking about CREATE DATABASE, not server) will not change performance, nor space.  On the other hand, "easier for backups / exports" may be a valid reason.  Please provide details so we can discuss.

"Sharding" is complex, but it is the way to split data across multiple servers.  (Note: I am using "server" to mean a separate physical machine.)

A small fix is to shrink the datatypes in tables.  Are you using BIGINT (8 bytes) when MEDIUMINT (3 bytes) would suffice?  Etc, etc.

Summary tables are an excellent way to save space and speed up "reports".  But perhaps they are not as small as they could be?  Let's see SHOW CREATE TABLE.

"growing fast I was thinking into splitting it into XX tables" -- NO, NO, NO!  The only exceptions is when "sharding".

How do you populate the Summary Table(s)?  Some tips:  http://mysql.rjweb.org/doc.php/summarytables  I assume your summary tables are updated either in realtime, or nightly?  And you update them only incrementally, not by starting over.

You say 1 insert per second?  That is a very low rate.  Perhaps it is faster than that?

When the data is properly indexed and/or summarized, the size of the table does not matter (for performance).  Are you concerned about disk space or speed?


Snapshot isolation transaction failed in database 'Master'

Fortunately we have trace running in production.
Analysing the data I found an 'ALTER LOGIN' being doing in the 'MASTER' system db.

But it still strange because those commands are from a DIFFERENT APPs using a different connection, technology, etc.
Basicaly costumer got an "COA - Client Online App" and support have an "ALA - Adm Local App".
Locks like support changed the password of a user in the ALA and caused the COA to raise a error in a DB intensive operation.
It's still strange because even the users/costumers in both instances are different and don't share any data.

I was naively expecting two unrelated transactions from two unrelated connections to not crash one another.


schema shows through standard client connection but not through ODBC connection?

As I suspected this may have been, it actually appears to be an issue in Crystal Reports 2008 on the client side.

Though I was unable to see the schema enumerated in the list of schemas, after selecting "Update" to link the two, I WAS able to select the various fields necessary and generate the report from the new datasource.

So, end result -- it doesn't show up, but it's there.


As for your original question, you need an extra space before FROM in the line before your PREPARE

SET @sql = CONCAT('SELECT advertiser_id, ', @sql, ' FROM ceciliadb.tbltmp_act GROUP BY advertiser_id');

SUGGESTION

At first glance, the query you need generated looks like this

SELECT
    advertiser_id,
    IF(activity_type=1,counts,0) activity_type1,
    IF(activity_type=2,counts,0) activity_type2,
    IF(activity_type=3,counts,0) activity_type3
FROM ceciliadb.tbl.tmp_act
GROUP BY advertiser_id;

I would craft it like this:

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since you have 100 types, you nned to wedge those 100 type IDs in the subquery

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since the generated query may be too long, set the GROUP_CONCAT length to 10 MB beforehand

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

If your columns are not sequential in nature, then use the actual values as headers

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT DISTINCT activity_type FROM ceciliadb.tbl.tmp_act) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;


Always fetch 3 records in a particular order

CREATE TABLE #t(StudentID INT, BookName VARCHAR(10), RowNumber INT);

INSERT #t(StudentID,BookName,RowNumber) VALUES
(1000,'ABC1',27),    (1000,'ABC2',31),    (1000,'ABC3',28),
(1005,'ABC2',28),    (1005,'ABC3',20),    (1005,'ABC1',25),
(1006,'ABC1',12),    (1006,'ABC2',14),    (1006,'ABC3',15);

;WITH x AS
(
  SELECT StudentID, BookName, RowNumber,
   rn1 = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY BookName),
   rn2 = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY RowNumber)
  FROM #t
)
SELECT x.StudentID, x.BookName, y.RowNumber
 FROM x INNER JOIN x AS y
  ON x.StudentID = y.StudentID
  AND x.rn1 = y.rn2
ORDER BY x.StudentID, x.rn1;
GO

DROP TABLE #t;

Results:

StudentID  BookName  RowNumber
---------  --------  ---------
1000       ABC1      27
1000       ABC2      28
1000       ABC3      31
1005       ABC1      20
1005       ABC2      25
1005       ABC3      28
1006       ABC1      12
1006       ABC2      14
1006       ABC3      15


Phantom comparison data in SQL Server Reporting Services - How do I remove it?

Could you check the data type of "id" in the report dataset?

Maybe when the NeverAttended = 'false' is used, the data returned is having text data whereas the dataset is having Id as integer type.


You could go with a more general structure, such as this:

appliances
----------
  id (PK)
  name (such as "bedroom TV", "main freezer", etc...)

parameter_types
---------------
  id (PK)
  type (such as "STRING", "INT", ...) - You might only be to enforce this in the application code, rather than in the database.

appliance_parameter
-------------------
  id (PK)
  name (such as "Model name", "Max. operating temperature", "IP address", etc...)
  parameter_type_id (FK to parameter_types.id)

appliance_parameter_values
--------------------------
  id
  appliance_id (FK to appliances.id)
  appliance_parameter_id (FK to appliance_parameters.id)
  value

This could be populated as:

appliances
ID  | name
----+------------
1   | Marcel's TV
2   | Main fridge
3   | coffee maker

parameter_types
ID  | type
----+---------
1   | STRING
2   | NUMERIC

appliance_parameter
ID  | name        | parameter_type_id
----+-------------+------------------
1   | model number| 1
2   | colour      | 1
3   | IP addr     | 1
4   | weight (kg) | 2

appliance_parameter_values
ID  | appliance_id  | appliance_parameter_id | value
----+---------------+------------------------+-------------
1   | 1             | 1                      | TV-01A
2   | 1             | 2                      | Magenta
3   | 1             | 3                      | 192.0.0.1
4   | 1             | 4                      | 3.2
5   | 3             | 3                      | 192.0.0.2
6   | 3             | 4                      | 1.2
7   | 3             | 2                      | grey
8   | 2             | 1                      | FRDG-001

In this example, you don't need to create specific columns for appliances. You create date for an appliance parameter in a specific table, then populate values into appliance_parameter_values when you need to. This system can handle any number of appliances with any number of parameters, as long as you can add the appropriate records for them.


Sum currencies excluding duplicatied records

I agree with oNare that adding a sqlFiddle that builds a simplified version of your data model, shows what you have tried so far, and shows the desired results would be most helpful.

That said, you might want something like the following in order to avoid double-counting the sales for any customers that were given multiple offers.  I've put inline comments quoting the relevant parts of your question in this example query:

SELECT s.customerId, s.totalSellValue, o.* -- "other CRMOffer data"
FROM CRMOffer o
JOIN (
    -- "I must then group by CRMOffers customerID and sum sell values, but sum only once for each SellPK"
    -- Compute total sell value for each customer before joining to CRMOffer in order to avoid double-counting
    -- NOTE: Grouping by Sell.customerId is the same as grouping by CRMOffers.customerId since the two must match
    SELECT customerId, SUM(sellValue) AS totalSellValue
    FROM Sell
    WHERE customerId IS NOT NULL -- "If Sell table doesnt have a specific customerID, sell value will be null"
    GROUP BY customerId
) s
    ON s.customerId = o.customerId


How can I set a field when original lookup is NULL or isn't in the list?

Arguably, you would be better off with the NULL since that's the absence of a value, rather than arbitrary text indicating the absence of a valid value... but:

    -- 8< -- snip -- 8<--
    ldap_full.thomsjobfamilycode,
    ldap_full.thomsjobfamilydescr,
    ldap_full.thomsrecordtypedescr,
    sup_email.mail AS supemail,
    CASE WHEN ldap_full.thomslocationcountrydescr IS NULL
         THEN 'hey null source data here'
         WHEN reports_regionnames.regionname IS NULL AND ldap_full.thomslocationcountrydescr IS NOT NULL
         THEN 'my lookup table needs something'
         ELSE reports_regionnames.regionname END AS regionname /* << updated from comments below */
FROM
    ldap_full
    -- 8< -- snip -- 8<--

The last column selected will be the output of the CASE expression.

When ldap_full.thomslocationcountrydescr IS NULL then we return one string... otherwise when we didn't match the lookup table, yet ldap_full.thomslocationcountrydescr isn't null, we return a different string... otherwise (ELSE) we return the original value, from reports_regionnames.regionname.

The first valid match wins.

http://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html


PostgreSQL 9.6 log shipping replication stuck on startup

Thanks to Luan Huynh, I checked the logs on the Slave and saw that it was not able to start WAL streaming due to the missing replication slot on the Master (I really was certain it had been created! :-) )

Creating the replication slot with pg_create_physical_replication_slot was all that was needed.  Verifying the replication slot with SELECT * FROM pg_replication_slots showed the slot had been created and WAL streaming was active.

I was able to verify that the latest records on highly active tables on the Master are being successfully replicated on the Slave.


Postgres query very slow when using index

It seems that the statistics for that index are way off, and PostgreSQL may be faster performing a sequential scan.

Try to

ANALYZE organizations;

and see if that improves matters.

Another idea may be an index on organizations.id (if you don't already have that) with the idea to fetch rows in sort order and discard the ones that don't meet the criterion.

The perfect index would be

CREATE INDEX ON organizations (id)
   WHERE url IS NOT NULL AND domain IS NULL;

You could also include the url column in the index (using the INCLUDE clause in v11) and aim for an index only scan.


Does PostgreSQL btree_gin extension use btree or gin data structure?

The resulting index is a true blood GIN index. These operator classes are not installed by default because they are rarely needed.

A single-column GIN index on a column of scalar type would be inferior to a B-tree index in almost every way.


Query to filter out the duplicate and pick the latest

The query below will probably work; the subquery extracts the highest number after the - ('Version') and the outer query concatenates them back into the original format.

SELECT CASE Version IS NULL
           WHEN 1 THEN ID
           ELSE CONCAT(ID, '-', Version)
           END
  FROM
(SELECT
  CASE LOCATE('-', name)
           WHEN 0 THEN name
           ELSE SUBSTRING(name, 1, LOCATE('-', name) - 1)
           END AS 'ID',
  MAX(CASE LOCATE('-', name)
           WHEN 0 THEN NULL
           ELSE SUBSTRING(name, LOCATE('-', name) + 1)
           END) AS 'Version'
  FROM forms
    GROUP BY CASE LOCATE('-', name)
           WHEN 0 THEN name
           ELSE SUBSTRING(name, 1, LOCATE('-', name) - 1)
           END) AS subquery

Here is an online demo.


Recommended Auto Grow settings suitable for database whose size is 867666.25 MB

Set it to 1024MB.  If it is still happening too often, then 4096MB.  Until you measure your actual growth needs, it guesswork.

Or, manually grow it to 950GB, or an even 1TB and let Autogrow not be a factor, which is what you should be doing anyway.

Is Instant File Initialization turned on?  You didn't mention the version of SQL Server.

And change the Log file from % to MB.

And just for bonus - please get them onto separate drives.

Kevin3NF


As long as you change the service account within SQL Server Configuration Manager (SSCM) it will apply all the permissions required (registry, files, etc.).

Only thing to be aware of are those rare occasions where SSCM process does not apply all permissions as expected, generally from some WMI issue. I so far have not come across this in new versions of SQL Server (2008 R2+).

By the name of that service account I am going to assume you are on an OS of Window Server 2008 R2 or higher. Just as a side note, if you do not actually need access to Active Directory resources leaving that account as the system account is perfectly alright. Most security standards will allow it as well if you are running on a current Operating System. These are virtual accounts that offer more security than the previous local system accounts did in previous versions of the OS. They are less chances of someone impersonating those accounts. I use them on most servers I setup for clients now unless they have some reason to need full domain access.


Oracle DB Security Issue: Anyone can login using SQL*PLUS, with any username, and any wrong passphrase

The OSDBA group (typically, dba)
You must create this group the first time you install Oracle Database software on the system. This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege).

This is expected behavior as you are logging the Oracle Database from the OS user which belongs to DBA OS group.
[oracle@localhost Desktop]$ sqlplus sys/abc as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 11:23:20 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost Desktop]$ groups
oinstall dba

[oracle@localhost Desktop]$ su joe

[joe@localhost oracle]$ sqlplus sys/abc as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 11:26:56 2016

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

ERROR:
ORA-01031: insufficient privileges

Let's see which OS user group does the Joe belong.
[joe@localhost oracle]$ groups
joe

Here, the OS User Joe doesn't belong to DBA OS user group, he can not login to the database with wrong password.

But anyone who knows this problem, doesn't need to get in physically, they will be only in need of the IP address and the name of your DB to login remotely or use a script to do what they need by sqlplus.

No, this is not correct this can be controlled by remote_os_authent initialization parameter :
SQL> show parameter remote_os_authent

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent            boolean     FALSE

Setting this parameter to FALSE can prevent such situation.

How I should unlock a user which got locked after three wrong attempts?

You can use alter user username account unlock command as SYSDBA.


resuming server replication on PostgreSQL servers

It seems like archiving managed to partly write the WAL archive file before the space ran out.

Then the test in your archive_command will notice that there is already a fike of that name and will fail.

In this case the solution would be to manually remove that partially archived WAL segment so that the next attempt to archive it can succeed.

You might want to improve your archive_command by removing the file if cp fails (while still returning a non-zero return code).


Simple Search on 13 Terabyte Oracle Database to return relevant rows (.net app? Java?)

There are some useful hints in this SO post.

If you are looking for alternatives to Oracle Text, have a look at this.


Get data from previous years dynamically

You can do that with a simple join and intervals. Assuming the table with your data is named mydata and the table you mention in your question with a list of dates named mydates, you could issue the following query:

SELECT d.id, d.fname, d.date_field, dat.*
FROM mydates d
JOIN mydata dat
    ON (dat.date_field >= (d.date_field - interval 2 years)
    AND dat.date_field < d.date_field)
ORDER BY 3, 1;


You don't restart Managed Instance. The beauty of PaaS is that the cloud service provider ensures that you get a certain uptime SLA-- 99.99% in this case.

The service is abstracted away. Microsoft will ensure that the service is "always" up and running. They handle patching and maintenance on the OS and the instance (and necessary restarts that go along with those things).

If you need to stop, scale, or make other changes to the instance, you'd do that from the Azure portal (either the web GUI or PowerShell).


SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working

The error isn't coming from the trigger.  It's coming from your insert statement.

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')

It's getting parsed and returning an error before you ever get to the trigger.  Change it to this and it works.

INSERT INTO PixTest(TESTE) VALUES ('TESTE 1')


MySQL Backups - Full vs incremental - innobackupex

No, you don't need to prep every backup. You only need to create the full backup on Sunday and then create the incrementals from Monday to Saturday.

Full:

innobackupex --user=USER --password=PASSWORD /path/to/backup/dir/

Incr:

innobackupex --incremental /path/to/inc/mon --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD

innobackupex --incremental /path/to/inc/tue --incremental-basedir=/path/to/inc/mon --user=USER --password=PASSWORD

The prepare phase (--apply-log) is only needed when you restore the backup.


Incorrect unit allocation size. error comes when Tuning the Database

For the first point:

  Incorrect unit allocation size. Drive C: has a queue length average of 7.26. This drive is also responsible for writing the transaction logs sequentially and performing the read/write of SQL Server pages which is typically random*

This indicates that some of the user databases are hosted on the C: Drive which as per the best practice should not be the case. It is preferred that mdf should be on a drive with its respective RAID level for higher reads medium writes, while LDF should be on a different drive apart from MDF. and per the error message it seems you're MDF and LDF reside on C drive:

So check for those DB's whose files are on C: drive and move them to their respective data drives, if you have any, if not then get them attached.

For allocation of unit size in SQL Server read  This article it will help:

And also as recommended and mentioned above in point a , you should move the heavy DB's files to their physical drives like D and L as per you're naming convention on the servers.


I guess something like this would do:

select x.n, y.n
from generate_series(1,3) as x(n)
cross join generate_series(1,4) as y(n)
order by y.n, x.n

The idea is to take the Cartesian product between a set of 3 elements and a set with 4 elements. In this case Z_3 and Z_4

Since 3 and 4 is co-prime ( gcd(3,4) = 1 ), we can conclude that the Cartesian product between Z_3 and Z_4 is isomorphic with Z_12 (if only we had formatting options ...). See zm-cross-zn-isomorphic-to-zmn for a proof. I.e we can use Z_12 and use modulo arithmetic to achieve the desired set:

select mod(x.n,3)+1, mod(x.n,4)+1
from generate_series(1,12) as x(n)
order by mod(x.n,4)+1, mod(x.n,3)+1


truncate table for all db in sql server 2000

First run this script to see which tables would be truncated. Then do the obvious modification.

SET NOCOUNT ON

declare @name sysname
declare @sql varchar(255)
declare @sql2 varchar(255)

declare @rc int
declare db_cursor cursor for

select name from master..sysdatabases
open db_cursor

fetch next from db_cursor into @name
while @@FETCH_STATUS = 0
begin
    Set @sql = 'select * into #t1 from ' + @name + '..sysobjects where name = ''table1'' and type = ''U'''
    EXEC(@sql)
    set @rc = @@rowcount
    if @rc > 0
    begin
        set @sql2 = 'use ' + @name + '; ' + 'Truncate table table1'
        print @sql2
        --Exec(@sql2)
    end
    fetch next from db_cursor into @name
end

close db_cursor
deallocate db_cursor


In Postgres, how to select by a column of type UUID

A colleague enlightened me: it was the double quotes around the string literal. The right syntax is:

select from site where id = '9d4da323-4c20-360f-bd9b-ec54feec54f0'

I'm answering my own question, hoping that this helps someone else in the future.


Sounds like:

if action=1, then ref_transaction_id can only be 0, and
if ref_transaction_id=0, the action can only be 1

If that's correct, then the query may be simplified:

select *
from transactions
where `created` between "?" and "?" and `action`=1;

Notes:

add an index for action to help the query
use between to range check the date; suspect minor performance improvement

Unsolicited Observations

This sounds like a linked list (ref_transaction_ids) and a state machine (action). If that's true, or close, may want to consider putting that info into a separate "association" table,

....
`fk_id` bigint(20) NOT NULL,
`transaction_id` bigint(20) NOT NULL,
`fk_prior_id` bigint(20) NOT NULL COMMENT "replaces use of ref_transaction_id",
`action` smallint(6) NOT NULL,
....

Notes:

introduces fk_prior_id as a way of finding the ref_transaction_id using a field which is already indexed, id
consider adding the date fields depending on the
nature of the data
consider defining action as an enum to capture the state and improve readability; still stored as an integer internally but may be referenced using the defined "states" in queries.


How can I cancel index creation in SQL Server

One way is to find the process/session Id of the query and kill it. You can learn more here. This might be about the only way if you don't know who or what actually started the index creation.

The following query should show you the session Id of the Insert query:

    SELECT
    r.session_id as SessionID,
    r.start_time as StartTime,
    r.[status] as Status,
    r.wait_type as WaitType,
    r.blocking_session_id as BlockedBySessionID,
    sessions.login_name as BlockedByUser,
    SUBSTRING(qt.[text],r.statement_start_offset / 2,
        (CASE
            WHEN r.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
            ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) AS SQLStatement,
    DB_NAME(qt.[dbid]) AS DatabaseName,
    r.cpu_time as CPUTime,
    r.total_elapsed_time as TotalElapsedTime,
    Round(r.total_elapsed_time / 1000.0 / 60.0,1) as TotalElapsedTimeInMinutes,
    r.reads as Reads,
    r.writes as Write,
    r.logical_reads as LogicalReads
FROM sys.dm_exec_requests AS r
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS qt
    LEFT OUTER JOIN sys.dm_exec_sessions sessions ON sessions.session_id = r.blocking_session_id
WHERE r.session_id > 50 -- This eliminates system requests
ORDER BY r.start_time


mssql-server crashing: Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8

The error message is indicating internal memory pressure; could you please share the complete errorlog output when the OOM condition had occurred. We would like to validate memory consumption by each clerk.

How large is your database which is still in recovery; It looks like there some long outstanding i/o operations occurring. Are there any latencies at the storage layer?

-Pradeep


Trigram search gets much slower as search string gets longer

In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this.  With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it).

What the oldest version does is search for each trigram in the query and take the union of them, and then apply a filter. What the new version will do is pick the rarest trigram in the query and search for just that one, and then filter on the rest later.

The machinery to do this does not exist in 9.1.  In 9.4 that machinery was added, but pg_trgm wasn't adapted to make use of it at that time.

You would still have a potential DOS issue, as the malicious person can craft a query which has only common trigrams.  like '%and%', or even '%a%'

If you can't upgrade to pg_trgm 1.2, then another way to trick the planner would be:

WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')))
AND   (lower(unaccent(label||'')) like
      lower(unaccent('%someword and some more%')));

By concatenating the empty string to label, you trick the planner into thinking it can't use the index on that part of the where clause.  So it uses the index on just the %someword%, and applies a filter to just those rows.

Also, if you are always searching for entire words, you could use a function to tokenize the string into an array of words, and use a regular built-in GIN index (not pg_trgm) on that array-returning function.


Remove ORA-01100:DATABASE ALREADY MOUNTED

You seem to mixup the terminology for database and schema in an Oracle database. In Oracle up to 11g, a database is just a collection of files. Those files are logfiles, controlfiles, datafiles and tempfiles. Tablespaces are created using datafiles and you create tables and indexes inside a tablespace.

If you want to create tables, or other schema objects, you create them with a chosen user as their owner. Such an owner is a schema and there can exist multiple schemas in one database. Schemas can share tablespaces, if you want to. Often, tablespaces are created dedicated for a schema, giving a bit more oversight in who is using what resources.

Since Oracle 12c there also is the option to create a pluggable database inside a container database. Once that pluggable database is created, you can connect to it and use it in a similar way as the pre-12c database, where you again start creating users/owners/schemas and their objects.


Speeding up group by query in PostgreSQL 9.6.5

Why did the planner chose to sort first and apply the filtering afterwards, causing the costly outer part of the plan?

It didn't. You read the query from the inside out. Not from the top down.

  What indexes could be created to speed up the query? I've experimented with a lot of multiple column indexes but none yielded any significant speed improvement

Well, you have two in addition to indexing.

Your estimates are off. Pg expects 5.3 M rows, you return 2.9 M.
Your sort of 2.9 M rows is going to disk. If you can't eliminate the sort, you may want to look at adding more work_mem.

Try these in this order.

VACUUM FULL ANALYZE shares; and try again. See if you get a better estimate.
Drop all of the index you've got. Try this

CREATE INDEX zzz1 ON shares (poolid, created, miner, worker, difficulty);
ANALYZE shares;
CLUSTER shares ON zzz1;

See how that performs. You may want to go BRIN, your table is getting kind of big. Check into it anyway.

If you can normalize xml1, I would do that too. (Example 1::int and keep the xml elsewhere.) You should also check into moving to timestamp with time zone. You almost never want timestmap without time zone.


As a first note, you should never quote table names or any identifiers in PostgreSQL. This is a bad practice.

SELECT
  *,
  CASE
    WHEN "partic-acum" > 95 THEN 'C'
    WHEN "partic-acum" > 80 THEN 'B'
    ELSE 'A'
  END AS clasif
FROM (
  SELECT
    codigo,
    descripcion,
    costo,
    neto,
    round(partic) AS partic,
    round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
  FROM (
    SELECT
      codigo,
      descripcion,
      costo,
      neto,
      neto/sum(neto) OVER ()*100 AS partic
    FROM "abc-ventas"
  ) AS t
) AS t;

Outputs

 codigo | descripcion | costo | neto  | partic | partic-acum | clasif
--------+-------------+-------+-------+--------+-------------+--------
 0002   | lápiz       | 3.000 | 5.000 |     28 |          28 | A
 0005   | arroz       | 1.000 | 5.000 |     28 |          56 | A
 0001   | sandía      | 2.000 | 3.000 |     17 |          72 | A
 0004   | naranja     | 2.000 | 3.000 |     17 |          89 | B
 0003   | manzana     | 1.000 | 2.000 |     11 |         100 | C
(5 rows)

Explanation

First we generate partic with a window function. We do not round yet.

SELECT
  codigo,
  descripcion,
  costo,
  neto,
  neto/sum(neto) OVER ()*100 AS partic
FROM "abc-ventas"

This outputs

 codigo | descripcion | costo | neto  |         partic
--------+-------------+-------+-------+-------------------------
 0001   | sandía      | 2.000 | 3.000 | 16.66666666666666666700
 0002   | lápiz       | 3.000 | 5.000 | 27.77777777777777777800
 0003   | manzana     | 1.000 | 2.000 | 11.11111111111111111100
 0004   | naranja     | 2.000 | 3.000 | 16.66666666666666666700
 0005   | arroz       | 1.000 | 5.000 | 27.77777777777777777800
(5 rows)

Then we generate the cumulative partic.

  SELECT
    codigo,
    descripcion,
    costo,
    neto,
    round(partic) AS partic,
    round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
  FROM (
    SELECT
      codigo,
      descripcion,
      costo,
      neto,
      neto/sum(neto) OVER ()*100 AS partic
    FROM "abc-ventas"
  ) AS t;

Generating this. Notice this is where we round.

 codigo | descripcion | costo | neto  | partic | partic-acum
--------+-------------+-------+-------+--------+-------------
 0002   | lápiz       | 3.000 | 5.000 |     28 |          28
 0005   | arroz       | 1.000 | 5.000 |     28 |          56
 0001   | sandía      | 2.000 | 3.000 |     17 |          72
 0004   | naranja     | 2.000 | 3.000 |     17 |          89
 0003   | manzana     | 1.000 | 2.000 |     11 |         100
(5 rows)

Then we run the whole thing in a virtual table to generate the rankings.

This may look like a more complex query, but it's all done in a single table pass -- it's the fastest solution I think you can get out of this.


Oracle 12C, cannot drop NOT NULL constraint on a DEFAULT ON NULL column

Seems to be a bug in Oracle 12.1.

Solution is simply:

SQL> purge recyclebin;


What data type are the numbers in a SQL Server statistics object?

Below are the field names and datatypes of each of the result sets as reported by SQL Server (I tested on SQL Server 2012, 2014, and 2016, though I would expect these to remain consistent across SQL Server versions):

DBCC SHOW_STATISTICS() WITH STAT_HEADER

[Name]                  NVARCHAR(128)
[Updated]               NVARCHAR(20)
[Rows]                  BIGINT
[Rows Sampled]          BIGINT
[Steps]                 SMALLINT
[Density]               REAL
[Average key length]    REAL
[String Index]          NCHAR(3)
[Filter Expression]     NVARCHAR(MAX)
[Unfiltered Rows]       BIGINT

DBCC SHOW_STATISTICS() WITH DENSITY_VECTOR

[All density]           REAL
[Average Length]        REAL
[Columns]               NVARCHAR(4000)

DBCC SHOW_STATISTICS() WITH HISTOGRAM

[RANGE_HI_KEY]          INT -- Datatype varies based on datatype of first key column
[RANGE_ROWS]            REAL
[EQ_ROWS]               REAL
[DISTINCT_RANGE_ROWS]   BIGINT
[AVG_RANGE_ROWS]        REAL

To be clear, these datatypes are the result set datatypes from the DBCC commands; there is no indication of the actual datatypes used to store that info. However, it seems reasonable to assume that the datatypes of the result set are the same as those used the store the info.

Also, for anyone curious as to how I was able to determine these datatypes: I got the info from a SQLCLR stored procedure I created as part of the SQL# library. It is called DB_DescribeResultSets and is similar to sys.dm_exec_describe_first_result_set but handles multiple result sets, temp tables, dynamic SQL, etc (because it runs the query instead of just parsing it). The DB_DescribeResultSets stored procedure is not available in the Free version, though. However, you could do the same thing in any .NET app. It just requires running a query through a SqlDataReader and then using the GetSchemaTable method to get the result set schema.

I ran DB_DescribeResultSets as follows:

EXEC SQL#.DB_DescribeResultSets
  @TheQuery = N'DBCC SHOW_STATISTICS(N''msdb.dbo.sysjobs'',
                                     nc1);',
  @RowNumberToGetValuesFrom = 1,
  @ResultSetNumberToDescribe = 0, -- 0 = all result sets
  @ShowHiddenFields = 1,
  @ResultSet = ''; -- this is an XML OUTPUT param and can't have a default in SQLCLR


Understand why the query plan was changed

Statistics are one input to the query optimizer but not the only one.  As mentioned in the post, the optimizer also sniffs parameter values during compilation and uses those to generate estimates of the number of rows that will come out of various operations such as filters, joins, and group by using the statistics that exist at the time of compilation.  Furthermore, the storage engine provides row counts for each compilation and this can also change without stats changing (though if you change enough rows, the optimizer eventually forces stats to be recomputed).  Any of these things changing could be the cause of the plan changing from one compilation to the next.  The query store actually also tracks other factors that it internally uses to represent to you whether the query is the same or different semantically through a table called sys.context_settings.  So, ansi null on/off would be one such semantic change that could also cause differences in results.  The query store abstracts these from you, but if you were to, for example, create a loop in a stored procedure that changed ansi nulls on/off and then run the same statement over and over in the loop, you'd actually get that to compile over and over (each time the settings are changed from the last time the statement in that part of the sproc is compiled) and this would also give you a chance to have new parameters sniffed on each execution.  These would technically show up as different queries in the query store but to the SQL engine these are holding the same statement slot in the cached query plan for the stored procedure.

SQL 2017+ contains a feature called Automatic Tuning which can auto-force the old plan for you for large regressions.  Azure SQL DB also has this same feature.  In the future, we hope to make parameter sniffing less of a problem for customers, so I hope this gives you insight in the meantime to work around the issue for your scenario.


If you create the index before loading the table, the time taken to load the data will be significantly increased.

pre load:

create table my_table1(val integer);
create index my_index1 on my_table1(val);
insert into my_table1(val) select generate_series(1,100000) order by random();
Time: 31755.858 ms

post load:

create table my_table2(val integer);
insert into my_table2(val) select generate_series(1,100000) order by random();
Time: 15344.130 ms
create index my_index2 on my_table2(val);
Time: 4073.686 ms

If you are ok with that, with pg_restore you can:

Load just the schema using --schema-only
Create the index with --index
Load the data using --data-only

Of course "Buy more storage" may well be the best answer here...


Need advice for table design for multi-user access


Oracle Migration Upgrade 11g to 12c New Server

You can use expdp and impdp utility by Oracle for migration of database from 11g to 12c.

First of all we will use expdp utility to export the whole database into a dumpfile (.dmp) by issuing the following command:

expdp system/password@db11g full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

Here, FULL parameter indicates that the whole database will be exported
      Directory --> The directory on server in which you want to place the dumpfile
      DUMPFILE --> The name of dumpfile
      LOGFILE --> The name of logfile (that logs the status of export process)

After the export is done successfully you will get something like:
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 10:35

Now, your whole database is stored in a dumfile(.dmp) in the 'DIRECTORY' mentioned in the expdp command.

We will now start import of the database into 12c database with the help of impdp utility.

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=impdpDB12c.log

*Before starting import you might need to transfer the dumpfile to the new server (i.e. 12c)

After import has been successfully completed you will get successfull completion message.

Reference link --> https://oracle-base.com/articles/10g/oracle-data-pump-10g

Happy Migration !!


What is the proper procedure for fixing materialized view replication?

And this is exactly what happens when you create materialized views on a prebuilt table that is already stale.

Recreating the mview the same way will not solve this, why would it? You still use the same incomplete prebuilt table.

Recreating mview logs has nothing to do with it. If you recreate mview logs now, that does not include data that was modified in the past.

Yes, the solution is complete refresh, and that does not necessarily truncate the table.

REFRESH Procedures

DBMS_MVIEW.REFRESH has a parameter called atomic_refresh which defaults to true.

A complete refresh with atomic_refresh set to true refreshes all mviews given as parameter in a single transaction. It is achieved by DELETE-ing the rows from the mview.

A complete refresh with atomic_refresh set to false refreshes all mviews given as parameter in seperate transactions. It is achieved by TRUNCATE-ing the mviews (which is much quicker than DELETE).

So specify atomic_refresh => true, and the mview will not be truncated.


Database design for storing historical nmap data

I'm not sure you need audit tables. I think a simple schema like this would be sufficient:

scans
-----
  scan_id (PK)
  start (this must be a datetime)
  end (this must be a datetime)
  version
  arguments

hosts
-----
  host_id (PK)
  scan_id (FK to scans.scan_id)
  name
  ip_address
  mac_address
  status

ports
-----
  port_id (PK)
  host_id (FK to hosts.host_id)
  number
  status

Each scan has a start and end so you know when it took place. From this you can easily determine which is the most recent scan. Since hosts has a foreign key to scans, you can always tell which hosts were found with a specific scan. Similar for ports.

In this example, host_id is simply a numeric identifier generated by the database. If you want to store something like myhost.com, add a hostname column to hosts. Same thing for ports.


When and why should I reset session in a connection pool?

Always set pool_reset_session to True, unless you have a specific and strong reason not to. I cannot think of a reason not to reset the session when returning a connection to the pool.
The risk of not resetting a session is that data associated with your session will be leaked into other sessions. Say for example you are doing a banking transaction on a website that serves multiple concurrent users. Part of the code saves some data about your transaction to a temporary table. Then the code returns that MySQL connection to the connection pool, where it is handed off to some other user's banking interaction. Their session can now see some details about your banking! The same applies to other session state, like user variables, or last_insert_id.
If you need to keep using session state, like reading temp tables several times, then your code should not return that connection to the pool until it is done with that temp table.


In which cases is a full table scan preferable to an index scan with low cardinality?

Let's imagine a large table, and the index is essentially the rows numbered.  And we want every 50th row via id IN (50,100,150,200, ...).  Further, let's say that there are about 100 rows per block.

If you do a table scan, it will read all the blocks.  That scan will read a block, pick up 2 rows, move on to the next block, etc.  By the time it finishes, it will have read each block once.  If you use the index, it will bounce between the index and the data; this is slightly worse.  Still, one block will yield 2 rows before moving on to the next block.

Now, let's change the example a little.  Let's say that the rows are ordered by something else.  Now 50 and 100 are very likely to be in different blocks, etc.  Further, let's say that the table is at least twice as big as the cache.

With this case, using the index will (usually) grab one block, extract one row from it, leave the block cached.  But, before the other row in that block is requested, the block may get bumped from cache.  That is, all the blocks will be read twice.  The table scan would be twice as fast because it would need read the blocks only once, essentially as in the first case.

(Using UUIDs, GUIDs, MD5s, or SHA1s as the PRIMARY KEY is a simple way to create this problem.)


Postgresql and UTF8 to Latin1 conversion?

It looks like whatever client you are using is confused about the text encoding; it's sending utf-8 bytes as if they were latin-1, probably.

Check:

SHOW client_encoding;
SHOW server_encoding;
locale command in your terminal, if using psql

Your update is substituting the octal bytes \303\244 which are the utf-8 encoding for "ä" (U+00E4). You're not substituting latin-1 encoded data where you think you are.

Observe:

regress=> SELECT convert_from(BYTEA 'huvudv\303\244rke', 'latin-1');
 convert_from
--------------
 huvudvÃ¤rke
(1 row)

regress=> SELECT convert_from(BYTEA 'huvudv\303\244rke', 'utf-8');
 convert_from
--------------
 huvudvärke
(1 row)

Not only that, but your replace could only have matched in the first place if the replace target was the utf-8 encoded byte sequence for Ã¤ interpreted as latin-1, i.e. \303\203\302\244.

It's hard to be more specific without details about the Pg version, the client being used, etc, but the root cause is clearly your client doing something totally borked with encodings on I/O.

Your original text is totally mangled, it's not valid in UTF-8 or latin-1. It looks like someone's taken some UTF-8 data, decoded it as latin-1, and then encoded it as utf-8 again.

Yep, sure enough:

regress=> SELECT convert(convert_to('huvudvärke', 'utf-8'), 'latin-1', 'utf-8');
          convert
---------------------------
 huvudv\303\203\302\244rke
(1 row)

there's your explanation.

I'd say you probably have a bunch of mis-encoded data in the DB already, and you noticed this one because it got mangled twice. You're probably doing something like routinely jamming utf-8 bytes into latin-1 encoded fields, but you usually get away with it because you decode them as utf-8 again - and this time you did something different.

If you want to get from your mangled text to the original, you simply have to reverse the incorrect encoding process. Decode the utf-8 and output latin-1, then re-interpet the latin-1 as utf-8 and decode again, e.g:

regress=> SELECT  convert_from(convert(BYTEA 'huvudv\303\203\302\244rke', 'utf-8', 'latin-1'), 'utf-8');
 convert_from
--------------
 huvudvärke
(1 row)


Postgres 9.6 reload or restart after config changes

Try this query:

SELECT name, context
FROM pg_settings
WHERE name ilike '%log%';

The pg_settings view gives the context for any setting. sighup is a reload and postmaster is a restart.


Strategies to import big data into Oracle

You didn't mention how the data comes in, but I would hazard a guess that it is through flat files. If this is the case, I worked at an order processing company that used Oracle for the back-end of the WMS we used. We regularly received 10 of millions of records a day.
We used SQL loader scripts to load the data. The caveat to that is that the data needs to be in the proper format. See here:

http://knoworacle.blogspot.com/2012/05/oracle-sql-loader-with-example.html

And of course from the makers:

http://docs.oracle.com/cd/B10501_01/text.920/a96518/aload.htm


Data exchange between 2 remote postgresql databases

If you only need one-way sync then I strongly recommend Londiste. For two-way, Londiste 3 or Bucardo can both do what you want with trigger-based replication.

Some ETL tools can do periodic table syncs, including two-way merges. Look into CloverETL, Pentaho Kettle, Talend Studio.

Don't try to roll your own. It's harder than you think.

The BDR project is working toward adding this to PostgreSQL, hopefully in 9.5, but it's currently in early development; I'm mentioning it so you're aware it exists for the future, and for later readers.


How do I select a string valued field against a number less than X?

Since the format is not specified exactly, I will just assume the usernames follow the above pattern without any special cases. Find the number:

select to_number(replace(replace('SOMENAME1234_SE', 'SOMENAME', ''), '_SE', '')) as build_number from dual;

BUILD_NUMBER
------------
        1234

Generate commands for dropping users:

select 'drop user ' || username || ' cascade;' from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < :X;

Or execute them in PL/SQL:

declare
  x number := 1234;
begin
  for cmd in (select 'drop user ' || username || ' cascade' as text from dba_users where username like 'SOMENAME%' and to_number(replace(replace(username, 'SOMENAME', ''), '_SE', ''))  < x)
  loop
    execute immediate cmd.text;
  end loop;
end;
/


How to normalize data based only on the repetitions

I've had to reverse engineer several existing complex data sets.  The most important thing to establish are the keys and dependencies in the data.  The problem is frankly NP hard, so some intuition and inspection will go a long way to getting you to a sensible answer, so don't count on a simple "turn the crank" solution unless you've got a lot of time on your hands.

What you need to do is to query the data a column at a time and by combinations of columns.  You want frequency distributions for column values (and column combination values).  Columns (or combos) with maximum frequencies of 1 are candidate keys.

You can also look at frequency distributions of combinations of columns to find potential hierarchies.  In your example each value in Col2 only ever has one value in Col1 and so forth.

When you identify candidate keys and dependencies between columns you can apply normalization.

EDIT: In response to OP's quesiton in comments:

This is a query that would tell you whether or not one column may have a functional dependency on another column:

select COL2
from MYSTERY
group by COL2
having count(distinct(COL1)) <> 1

If COL2 has a functional dependency on COL1 then this query would return 0 rows.  This is because every value of COL2 has exactly 1 corresponding value of COL1.


Index on a persisted computed column not seekable

The problem seems to be related to the fact that [TestGeocode].[ToString]() returns a max datatype (nvarchar(max)).

I also encounter the issue with this simpler version (changing the definition of c1 to varchar(8000) or using COALESCE instead of ISNULL resolves it)

DROP TABLE dbo.Test

CREATE TABLE dbo.Test
  (
     c1        VARCHAR(
                          MAX    --Fails
                        --  8000 --Works fine
                          ) NULL,
     comp1 AS CAST(ISNULL(c1, 'ABC') AS VARCHAR(100))
    CONSTRAINT UK_Test_comp1 UNIQUE NONCLUSTERED(comp1)
  )

GO

DECLARE @comp1 VARCHAR(100)

SELECT comp1
FROM   dbo.Test WITH (FORCESEEK)
WHERE  comp1 = @comp1
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Computed column references get expanded out to the underlying definition then matched back to the column later. This allows computed columns to be matched without referencing them by name at all and also allows simplification to operate on the underlying definitions.

ISNULL returns the datatype of the first parameter (VARCHAR(MAX) in my example). The return type of COALESCE will be VARCHAR(MAX) here too but it seems to be evaluated differently in a way that avoids the problem.

In the cases where the query succeeds the trace flag output includes the following

ScaOp_Convert varchar(max) collate 49160,Null,Var,Trim,ML=65535

    ScaOp_Const TI(varchar collate 49160,Var,Trim,ML=3)
                      XVAR(varchar,Owned,Value=Len,Data = (3,ABC))

Where it fails this is replaced by

ScaOp_Identifier COL: ConstExpr1003

I speculate that in the cases where it fails the (implicit) CAST('ABC' AS VARCHAR(MAX)) is just done once and this is evaluated as a runtime constant (more information). However the reference to this runtime constant label, instead of the actual string literal value itself, prevents it from matching the computed column definition.

This rewrite avoids the issue in your query

CREATE TABLE [dbo].[Test]
  (
     [test]        [VARCHAR](100) NULL,
     [TestGeocode] [geography] NULL,
     [Hashkey] AS CAST(
                        ( hashbytes
                            ('SHA',
                                ( RIGHT(SPACE(100) + isnull([test], ''), 100) )
                                + RIGHT(SPACE(100) + isnull(CAST(RIGHT([TestGeocode].[ToString](),100) AS VARCHAR(100)), ''),100)
                            )
                        ) AS BINARY(20)
                      ) PERSISTED
    CONSTRAINT [UK_Test_HashKey] UNIQUE NONCLUSTERED([Hashkey])
  )


In SQL Server, with a few assumptions like "those fields can't contain NULLs", those queries should give the almost the same plan.

But also consider the type of join you're doing. An IN clause like this is a Semi Join, not an Inner Join. An Inner Join can project onto multiple rows, thereby giving duplicates (compared to using IN or EXISTS). So you may want to consider this behaviour when choosing how you write your query.


Is it a good idea to shrink prior to a SQL Server upgrade to a new instance

I hope following information will help you to make your final decision.
faster backup times
You will not have a faster backup time.  See this answer by Aaron
Bertrand.

The backup process does not back up empty pages, only pages
with data, so typically, the backup file will be a lot smaller.
One important thing to note: when using compression, the initial size
needed on the destination drive will be the uncompressed size; the
backup will only be fully compressed at the end of the process.

less files to restore when we take a copy of production
You are not getting rid of any files so I am not sure what you are gaining here. Yes you can save your allocated space by shrinking the database.
there will be a lot of residual space after we've completed these deletes, so makes sense to reclaim it (I'm thinking ~30-35GB, so a third of the DB itself)
It all depends, can your company afford to spare 30~40 GB of space for few weeks/Months which you will need anyway.  You mentioned your auto-growth is set to 75MB and it happens 2-3 times a day. Based on this information you will use 30GB space in  in next 4.5 months.
If you are rebuilding index please do not update all statistics after that. Read this.
I will highly recommend you to increase the autogrowth size and avoid growth 2-3 times a day. Read this.


Here are a few possible solutions to your problem:

Option 1: Don't rebuild that index

That's a 30 GB index you have there.  What measurable performance problem are you trying to solve by rebuilding it?  Especially at 5% fragmentation, this seems like an incredibly expensive operation (in terms of system resources and locking) for very little gain.

You can read some very well-founded opinions on why you might want to give up on the index rebuild here:

Stop Worrying About SQL Server Fragmentation
Good Reasons to Rebuild or Reorganize Indexes
Video: Why Defragmenting Your Indexes Isn’t Helping

This is far and away your best, and EASIEST, option.  This is the home run.  Do this.

Option 2: Rebuild online

Index rebuilds require a SCH-M lock.  If you add WITH (ONLINE = ON) to your ALTER INDEX command, that lock will be deferred until the very end of the rebuild operation, which might increase the potential that whatever is preventing your maintenance task from completing has released its locks.

Option 3: Identify the blocking query

This is probably the most work of the options.  You can run sp_WhoIsActive while the ALTER INDEX command is running, and it should show you what else is running, and specifically it will show you what other session is blocking the ALTER INDEX command from acquiring its lock.  At that point, you have a bunch of options to deal with the problem:

reschedule your index maintenance so it's not running at the same time as the blocking query
re-work your blocking query so that it takes less locks / different locks / holds locks for less time (you could post another question with execution plan details if you need help with that)
reschedule the blocking query so that it is not running during the period when you do index maintenance


All data got deleted or truncated- What happened?

Unfortunately, there is no easy way to determine the DML command used without having some kind of auditing setup prior to the action.
You might be able to infer whether the action was a DELETE or a TRUNCATE by looking at the transaction log to see if pages were deallocated or if row data was written to the log.  If the trnasaction log shows pages were deallocated you can be certain that the table was truncated.
If you had SQL Server trace or an Extended Events session running on the server at the time the delete occurred, and that trace/session is capturing DML statements, you'd be able to see exactly what happened.
Probably the more important consideration would be to ensure you have a Recovery Point Objective that serves the business by limiting the amount of time that can go by without a backup happening that you can use for recovery.  So, with full recovery, you could be running log backups every 5 minutes, which would mean you could recover the table to a point just before the delete took place.
This blog post by Paul Randal has an excellent tutorial for inspecting a log backup.


SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week

Should SQLRaptor's answer not work out for you, one other drastic thing you can try is using the query hint FORCESEEK. This essentially forces the optimizer to always use a plan that does an index seek instead of an index scan (if possible).

One reason it's not a first go-to is because it limits the number of query plans that the optimizer can choose to use and in certain cases will error out by saying no plan available for that query hint. Typically query hints are more of a last resort bandaid fix (except in specific edge cases) but arguably less drastic than running the DBCC FREEPROCCACHE command and probably less drastic than always recompiling the query with the OPTION RECOMPILE hint from KumarHarsh's answer too.

(This ended up being the best solution for one particular scenario I recently ran into with a table that had billions of records and the optimizer was always trying to use a clustered index scan but there was a nonclustered index that was more applicable to the query and was actually always faster as a seek.)

See the FORCESEEK section of the Microsoft doc for more information: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15


Select from a list of values from JSON encoded data

I got a solution:

Single check of value in JSON array

SELECT * FROM `nw_object_detail`
WHERE ((`details` like '%"screen_type":"%LED%"%'))

One value from multiple possibilities

SELECT * FROM `nw_object_detail`
WHERE ((`details` like '%"screen_type":[%"%LCD%"%]%')

PHP Solution

if(count($options)>1):
    $condition = '(';
    $count = 1;
    foreach( $options as $option ):
        $condition .= '(`details` like \'%"'.$key.'":[%"%'.$option.'%"%]%\')';
        if(count($options)!=$count):
            $condition .= ' OR ';
        endif;
    endforeach;
$condition .= ')';


What permissions does db_owner not have? (In SQL Server 2008)

Permissions of Fixed Database Roles: db_owner: has CONTROL permission with GRANT option in the database and VIEW ANY DATABASE permission in the server. To understand what that entails, read all of the following:

Permissions
Principals
Securables
Permission Hierarchy


Performance tuning for MySQL database (read only)

MyISAM is going to be deprecated (Bill Karwin is a big hitter on stackexchange and previously worked for Percona!) - you should change MyISAM to InnoDB NOW. You can do that by following the instructions here or here

AIUI, even the system tables in MySQL have4/are been/being moved to InnoDB, see here and here. The latter reference appears to indicate that most system tables have moved to InnoDB in the upcoming 8 release.

You can read some of the pros/cons of MyISAM/InnoDB here.

Even if you're not using the capabilities of the InnoDB engine now, you may need to use them in the future.

To answer the question, you have two performance tuning options:

you can set your InnoDB TRANSACTIONs to READ ONLY from here,

or you can

set the entire server to be READ ONLY as per here.

You may want to test these, but it is my belief that any small performance penalty is worth paying for the advantages that the InnoDB engine brings!

  It won't be updating, inserting or deleting any data.

This is good for performance - there's no need for the engine to maintain linked lists of pointers to new records and/or other transactions in this case. You should set your transactions to read only as indicated above.

  There won't be any join we need to use, at a time tool will be
     interacting with a single table exclusively.

Hmmm... a database without joins - always been a puzzling concept to me. You do realise that the optimiser can make better decisions about which tables to process in which order if there are explicit joins between the tables - those who argue that FOREIGN KEYs incur a performance penalty don't understand RDBMSs.

  Tool will use only "order by", "where", "limit (for paging)" clause
     on the the database.

And GROUP BY surely? Otherwise, why not simply use a spreadsheet? In any case, presenting a human user with thousands/millions of lines of data (or in this case apparently, records) is pretty pointless - humans will just scan and skip through pages of numbers.

  All the table will be having a column "rowno" as a primary key. There    is no FK relationship between the tables.

See above about FOREGIN KEYs. No problem with having a simple integer PRIMARY KEY as your surrogate KEY.


sp_grantdbaccess cannot be run in a stored proc owned by DBO?

Ownership chaining works only inside the database. sp_grantdbaccess lives in master and is therefore excluded from ownership chaining. Even worse, according to the sp_grantdbaccess topic in SQL Server 2000's Books Online:

  Only members of the sysadmin fixed server role, the db_accessadmin and db_owner fixed database roles can execute sp_grantdbaccess.

Erland Sommarskog wrote a great article on ownership chaining; however, it covers SQL Server 2005 and later, and there were some significant changes in the security model, so proceed with care when reading.

The only way I can think of to make this work is a hack: Create a loopback linked server and elevate the permissions that way: https://stackoverflow.com/questions/982782/sql-server-2000-execute-as

But be aware that that opens a big security hole.

So my best recommendation - I know you probably don't want to hear this now - is to upgrade to a more modern version of SQL Server.


First of all what kind of database are you using? And are you referring to table partitioning?

Anyways for Oracle, please see this link..

https://forums.oracle.com/forums/thread.jspa?threadID=879608

I'm not too sure about how this would be done in PostgreSQL, but I think partitioning is much less relevant for PostgreSQL rather than for Oracle


Estimating bandwidth required for streaming replication

Well, very roughly speaking, at steady-state (i.e. after you have a standby server initialized with a basebackup and in-sync with the primary), the amount of bandwidth needed to keep a standby in-sync will be roughly your primary's WAL volume throughput.

Now, what is your primary's WAL volume throughput? Basically, how many 16 MiB WAL files your primary server produces per unit of time. You can either poke around in your primary's pg_xlog directory and see how many new files are being churned through. Or here's a nifty shell command using psql, credit of depesz that you can use:

(echo "ibase=16" psql -qAtX -c "select pg_xlogfile_name(pg_current_xlog_location())" | cut -b 9-16,23-24) | bc

The output of that command will give you a decimal count of what number WAL file you are on. Run that command again in, say, 10 minutes. Subtract the first number from the second, and that gives you how many 16 MiB WAL segments would need to be synchronized between the primary and the standby in that period of time. That will answer your question about:

  ... the amount of bandwidth that will be required to keep the databases reasonably synchronized

But! A few caveats:

The initial step of copying over a base backup to bring a new standby online will need a LOT of bandwidth (since you will effectively need to copy over your entire DATADIR to bring up the new server), and then the standup will have work to do to catch up to the primary's current position.  Effectively, this means you'll need plenty of additional bandwidth on top of your steady-state needs to be able to reliably initialize a new standby server in a reasonable time.
The volume of WAL produced by the primary will be influenced by settings such as wal_level, which must be cranked up to hot_standby when you actually want to bring a standby server up
Useful settings to enable once you're using streaming replication such as hot_standby_feedback will consume further bandwidth.
You will likely find that your WAL throughput will vary considerably depending on how busy your database is, autovacuuming, checkpoints, DDL, etc. You'll want to be able to absorb this additional WAL volume to keep your standby in-sync.


MySQL replication still failing after new import

Take a full dump again on the master node with below command
 shell> mysqldump --single-transaction --master-data=2 --routines --triggers your_database_name > file.sql

--master-data=2 parameter will provide master's binary log position and file in file.sql
Restore the dump from master node in Slave node
login to slave and execute below
mysql> STOP SLAVE; RESET SLAVE ALL;
mysql> CHANGE MASTER TO MASTER_HOST='Master Node IP',
       MASTER_USER='replication_user',
       MASTER_PASSWORD='replication_user_password',
       MASTER_LOG_FILE='binary_log_file',
       MASTER_LOG_POS=binary_log_file_position;


You have a bunch of different questions in here.

Q: What is the "Always On" thing?

Microsoft uses that brand name (which was written without a space before 2016) to describe two different features:

Failover Clustered Instances (FCIs) - what your grandpa used to call an active/passive cluster
Availability Groups (AGs) - like database mirroring, but works with groups of databases in some cases (but not the system databases)

Use those terms to describe which specific Always On feature you're using.

Q: In a failover, will it be always on?

Neither FCIs nor AGs are really always on. During a failover, your running transactions will fail, and connection retries can fail for 5-60 seconds (or more). It's up to you to build in graceful retry logic in your applications, or build in degraded capability tools like Stack Overflow does.

Q: How do I configure Always On?

It varies dramatically based on:

Which AO feature you're using (FCIs or AGs)
The number of nodes in the cluster
How you want to handle quorum (voting)
Whether you're using automatic failover via a listener or virtual computer name

These are big decisions that involve a lot of architecture work. For more detailed specifics, include the above details, and we'll be able to tell you more about how to configure it.

Q: Isn't it just a matter of checking the box for Always On?

Nope.


Query to update one column of a table based on a column of a different table

This should solve the first requirement:
UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = CTI2.Trns_Measure_Two -- Set it to the correct value (the only non-zero value for that Customer_num)
FROM Customer_Transaction_Info AS CTI
INNER JOIN Customer_info AS CI -- Inner joining Customer_info to ensure we only update for the correct Customer and Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
INNER JOIN Customer_Transaction_Info AS CTI2 -- Need to re-join to Customer_Transaction_Info on Customer_num and Trns_Measure_Two <> 0 to get this customer's non-zero correct value
    ON CTI.Customer_num = CTI2.Customer_num
    AND CTI2.Trns_Measure_Two <> 0

This should update the incorrect branches for your second requirement:
UPDATE CTI.Trns_Measure_Two -- Updating Customer_info.Trns_Measure_Two
SET CTI.Trns_Measure_Two = 0 -- Setting incorrect values to 0
FROM Customer_Transaction_Info AS CTI
LEFT JOIN Customer_info AS CI -- Left joining to Customer_info so we can see where there's no match by Customer_num and Customer_Branch
    ON CTI.Customer_num = CI.Customer_num
    AND CTI.Branch_Code = CI.Customer_Branch
WHERE CI.Customer_num IS NULL -- This filters Customer_Transaction_Info to only the bad records (where there's no match to Customer_info)

Please see the comments for information on how these two update statements work.


Try Open query

BEGIN TRANSACTION;

select top 10  *
INTO #t
from openquery ("YOUR LINKED SERVER NAME",'SELECT   * FROM [SOURCE TABLE NAME]);

COMMIT