mysql query performance... got to be a better way I have a query right now that for a lack of a better word, sucks.  I'm looking for some help with respect to other ways to approach writing this query.  Here are the details.

I've got a temp table build up with the following columns:

AssignmentSubType
AssignmentValue
OwnerUsergroupID
AssociatedObjectID

The basic gist of what I want to do is to find AssociatedObjectIDs that have the same AssignmentSubType, AssignmentValue, and OwnerUsergroupID.  This is because there is a ton of SQL down the road that really doesn't need to be run if I've got "duplicates" (same AssignmentSubType, AssignmentValue, and OwnerUsergroupID).  Once I've got my list of duplicates I run the next set of SQL stuff on one of the duplicates , when I've got back info from that I join back the rest of the duplicates for the final result set.

Example table data:

AssignmentSubType | AssignmentValue | OwnerUsergroupID | AssociatedObjectID
retailer          | Dicks           | 1                | 5
retailer          | Dicks           | 1                | 7
retailer          | Dicks           | 1                | 9

In this example I just want to do the calculations on associatedobjecid = 5 since 7 and 9 will be the exact same, and then join back in 7 and 9 at the 'end'

I've got the following query that works but is very inefficient

SELECT firstObject, f2.AssociatedObjectID
            FROM (
                SELECT firstObject, dupAss AS dups
                FROM (
                    SELECT min(AssociatedObjectID) AS firstObject, group_concat(concat('-',AssociatedObjectID,'-')) AS dupAss
                    FROM (
                        SELECT * FROM tableFromAbove
                    ) innerRes GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID
                ) outR
            ) outR2
            LEFT JOIN tableFromAbove f2
                ON outR2.dups LIKE concat('%-',f2.AssociatedObjectID,'-%') ORDER BY firstObject

this query will give me back a result set like the following

firstObject | AssociatedObjectID
5           | 7
5           | 9

like I said earlier in this post... I then use this result set to join back to the results from the rest of the queries for associatedobjectid = 5.  Any thoughts on how I could restructure this so that it is more efficient?

this is josh.  Try the following

SELECT  b.minID, a.AssociatedObjectID
FROM    tableFromAbove a
        LEFT JOIN
        (
            SELECT AssociatedObjectID, AssignmentSubType, AssignmentValue, OwnerUsergroupID, MIN(AssociatedObjectID) minID
            FROM tableFromAbove
            GROUP BY AssignmentSubType, AssignmentValue, OwnerUsergroupID
        ) b
            ON a.AssignmentSubType = b.AssignmentSubType AND a.AssignmentValue = b.AssignmentValue AND a.OwnerUsergroupID = b.OwnerUsergroupID
            WHERE minID <> a.`AssociatedObjectID`;


DB Master Key Encrypted By *Correct* Service Master Key? My client has frequently run process where we move databases from a prod environment to lower environments (refresh QA with prod database, etc).  These DBs have database master keys on them.  We have no issues re-encrypting these into the service master key on the destination server, but here's the annoying part:

sys.databases says that the DMK is already encrypted (albeit with
the source service's SMK)
There is no way we can find to tell if the DMK is encrypted by the current SMK (checked all the encryption DMVs)
The SMK signature in sys.symmetric_keys is always 0x01 (not unique across servers).

This isn't a problem, per se.  The fix is always re-encrypt the DMK to the SMK.  We just would like to be able to tell if it is already done or not.  Anyone have any tricks up their sleeve?

Thanks,
Eric

As per books online

  "The is_master_key_encrypted_by_server column of the sys.databases catalog view in master indicates whether the database master key is encrypted by the service master key."

Here is the link http://technet.microsoft.com/en-us/library/ms174382.aspx


MySQL: Error reading communication packets I get this warning in mysql,

[Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets)

I have been through few topics in google and according to some suggestion I increased the max_allowed_packet from 128 to 512 to 1024 still the same behaviour.

I am using Drupal 7, and yes there are lots of blob data types, but 1024 Mb of max_allowed_packet should be enough in my opinion.

Any other workaround how to overcome this warning ?

EDIT:

Added some settings as @Rolando's suggestions/answer, I still get the same warning.

My mysql config looks like this:

[client]
port        = 3306
socket      = /tmp/mysql.sock
default-character-set = utf8

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1024M
table_open_cache = 128
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 192K
# Query cache disabled
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 100
thread_concurrency = 10
tmp_table_size = 128M
max_heap_table_size = 128M
log_error                = /var/log/mysql/mysql-error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

log_warnings = 2

server-id   = 1
binlog-format = row
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log_bin = mysql-bin
log-slave-updates
relay-log=mysqld-relay-bin
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_data_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/mysql
innodb_buffer_pool_size = 8G
character-set-server = utf8
#innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 2047M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8

[mysql]
default-character-set = utf8

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M

[mysqlhotcopy]
interactive-timeout

[mysqld_save]
syslog

My application uses only InnoDB, but there are few database like mysql, which came with the standard mysql installations are only the ones which uses MyISAM engine type, I guess that should not be my concern though.

As you can see I have replication too, the warning is the same one in replicated server too, whose config is identical as this.

I am glad you said all your data is InnoDB so I can answer as follows: If max_allowed_packet is maxed out at 1G and you are still having issues, there is really only two places to look:

innodb_log_buffer_size : The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.
innodb_log_file_size : The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

I addressed something like about 2 years ago

Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

SUGGESTIONS

You need to increase the InnoDB transaction logs. Here are the steps to safely increase innodb_log_buffer_size and innodb_log_file_size:

Step 01 : Add these to /etc/my.cnf

[mysqld]
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M

Step 02 : Run this in mysql

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Step 03 : Shutdown mysql

service mysql stop

Step 04 : Move the old logs aside

mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

Step 05 : Start mysql

service mysql start

That's it.

The InnoDB Infrastructure should now have enough logging space for different size BLOBs.

Give it a Try !!!


InnoDB insertion faster I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think can allow more loose settings for insertion speed. At the moment, I'm using the following techniques.

disable log_bin
enable skip-innodb-doublewrite
set transaction_isolation to READ-COMMITTED or READ-UNCOMMITTED (actually READ-COMMITED)
set innodb_flush_log_at_trx_commit to 0 or 2 (actually 0)
set innodb_buffer_pool_size to 5GB (system has 6GB RAM)

Are there any more techniques for faster insertion on InnoDB?
And do I have to modify innodb_io_read_thread and innodb_io_write_thread ?
If you need more information, please tell me.

SUGGESTION #1

If your machine has multiple cores, you need to increase the following:

[mysqld]
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

What are these?

innodb_read_io_threads - The number of I/O threads for read operations in InnoDB.
innodb_write_io_threads - The number of I/O threads for write operations in InnoDB.
innodb_io_capacity - An upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer.

SUGGESTION #2

In order for separate data and indexes from the system tablespace (ibdata1), you need to do perform a complete restructuring of InnoDB. Sounds complicated, but is very straightforward. I wrote about this in the DBA StackExchange (Aug 29, 2012) and in StackOverflow (Oct 29, 2010). The basic steps are

Run SET GLOBAL innodb_fast_shutdown = 0;
mysqldump all data to a SQL dump
service mysql stop
Delete the following files

ibdata1
ib_logfile0
ib_logfile1

service mysql start

Before you run service mysql start, add this line to my.cnf

innodb_open_files=32768

That way, there will be file handles dedicated to the each individual table. The default is 300. File handles have been known to get cached. There will be a slowdown if you set this  very high and hit the ceiling quickly. This should not be the case if you are working a small number of tables.


SSIS permissions for specific databases I'm quite new to SSIS.

Is it a straightforward to grant SSIS permissions in the AD environment so that that a user can only create/manage/run ETL tasks on specific databases/Analysis Services projects on a SQL Server that contains multiple databases?

I.e. So a user can't extract/load data from/into a database/Analysis services project that they shouldn't etc.

Database version is SQL Server 2012.

Thanks!

If you intend to run the SSIS jobs from the SQL Agent, then they will execute as the user the agent runs as. That's usually the same as the account the instance runs as, which has rights to everything.


What does SQL Server use to do a count(*) on a table? Internally, how does SQL Server determine the result for a select(*)?  Is it different between a heap and a clustered index?  Is it able to do smart things with the clustered index to not have to load the whole table?

With a trivial SELECT COUNT(*) FROM dbo.YourTable; query on a disk-based table, SQL server counts rows by scanning either the heap or leaf nodes of an index. The cost-based SQL Server optimizer chooses the narrowest one available in order to minimize the number of pages scanned.

This example shows the behavior with an index scan.

CREATE TABLE dbo.YourTable(
      Column1 int NOT NULL
    );
--load 10K rows
WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.YourTable WITH (TABLOCKX) (Column1)
SELECT num
FROM t1g
WHERE num <= 10000;
GO
CREATE INDEX ncidx_YourTable_IntColumn ON dbo.YourTable(Column1);
GO
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO
--show heap and index size
SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;

Index/heap sizes:

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+

If we fragment the index, it becomes larger than the heap so the heap is scanned instead of the index:

--fragment index and update stats
UPDATE dbo.YourTable SET Column1 = 2;
UPDATE STATISTICS dbo.YourTable;
DBCC FREEPROCCACHE;
--heap scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

Heap and index size with fragmentation:

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         648 |
+---------------------------+-------------+

After the index is rebuilt, the index is scanned again because it is narrower:

--rebuild index
ALTER INDEX ncidx_YourTable_IntColumn ON dbo.YourTable REBUILD;
DBCC FREEPROCCACHE;
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

Heap and index size after rebuild:

SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;
GO

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+


How to return the value from a query with SP_EXECUTESQL in a stored procedure I have a stored procedure that generated a query string and then executes it with

EXECUTE SP_EXECUTESQL @Query

The query, e.g., is: Select Name, Age from tableA, so very simple.

But how to get the result set back from the SP_EXECUTESQL (into a variable?) and then return it as result set in my stored procedure?

You could use a table variable to hold the results of the dynamic SQL call until you are ready to exit the stored procedure.  Just before returning from the stored procedure, select the data from the table variable.

DROP PROCEDURE IF EXISTS dbo.MyProc;
GO
CREATE PROCEDURE dbo.MyProc
AS
BEGIN
    --Declare Table variable to hold results of query
    DECLARE @Results TABLE (ResultText VARCHAR(500));
    DECLARE @Query NVARCHAR(30);

    --This is the query
    SET @Query = 'select @@version'

    --Insert the results of the dynamic SQL execution into the table variable
    INSERT INTO @Results
    EXECUTE SP_EXECUTESQL @Query

    --Do more stored procedure logic

    --Finally, select the results of the table variable
    SELECT *
    FROM @Results
END
go
EXEC dbo.MyProc


Can't create Windows Login's on a case sensitive SQL Server 2005 Instance - MSG 15401 I need to create 3 logins on a SQL Server 2005 instance:

DOMAIN\aaa
DOMAIN\bbb
DOMAIN\ccc

However two of them (aaa and bbb) fail with the following error:

  Msg 15401, Level 16, State 1, Line 5 Windows NT user or group
  'DOMAIN\aaa' not found. Check the name again.

When I try to add them with DOMAIN\AAA and DOMAIN\BBB it works.

What is causing this strange case sensitive behaviour? And how can I add all accounts
lower case?

I found a KB article from Microsoft for the 15401 error, but nothing in there explains this behaviour.

You chose a case sensitive collation when installing SQL Server

You will have "CS" in this result:

 SELECT SERVERPROPERTY('Collation')

You either live with it or rebuild the master database


Understanding INSERT ... RETURNING Given:
$psql -U postgres
Password for user postgres:
psql (12.1, server 9.6.2)

postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |

I then ran:
postgres=# select count(*) from foo;
 count
-------
     0
(1 row)

postgres=# insert into foo (a) values (1) returning (select count(*) from foo);
 count
-------
     0
(1 row)

INSERT 0 1

Why did it return 0 rather than 1, i.e. since I inserted one row?

The select count(*) from foo inside the returning clause is evaluated before the insert, and then treated as a constant in the returning clause, as explain clearly shows:
> explain insert into foo (a) values (1) returning (select count(*) from foo);
┌───────────────────────────────────────────────────────────────────────────┐
│                                QUERY PLAN                                 │
├───────────────────────────────────────────────────────────────────────────┤
│ Insert on foo  (cost=41.88..41.89 rows=1 width=4)                         │
│   InitPlan 1 (returns $0)                                                 │
│     ->  Aggregate  (cost=41.88..41.88 rows=1 width=8)                     │
│           ->  Seq Scan on foo foo_1  (cost=0.00..35.50 rows=2550 width=0) │
│   ->  Result  (cost=0.00..0.01 rows=1 width=4)                            │
└───────────────────────────────────────────────────────────────────────────┘
(5 rows)

This is confirmed by the documentation where it says that the returning clause may contain column names or value expressions (using those columns) to be returned (saving a query after the insert):
> select count(*) from foo;
┌───────┐
│ count │
├───────┤
│     1 │
└───────┘
(1 row)

> insert into foo (a) values (1) returning 'a', a, (select count(*) from foo);
┌──────────┬───┬───────┐
│ ?column? │ a │ count │
├──────────┼───┼───────┤
│ a        │ 1 │     1 │
└──────────┴───┴───────┘
(1 row)

INSERT 0 1


microsoft sql server schema From blog.sqlauthority.com:

  Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such as Siebel and SAP, they are tuned for specific application access paths by using many customized indexes. Ad hoc access or alteration to the underlying base tables of these applications can severely impact performance of queries and the application itself. Using schemas, a developer can logically group objects and even create custom objects without running the risk of messing up the underlying ISV database

I have a vendor MS SQL Server 2008 R2 database that I need to add tables to in order to track timestamps. However, I am not allowed to modify the vendor's schema in any way, which is the "dbo" schema.

Would creating a schema new schema in the same database sufficiently isolate my proprietary objects from the vendor's so as not to interfere with things like vendor updates?

You will almost always be susceptible to vendor updates when it comes to schema changes and their app, especially when it comes to ERP applications such as SAP Business One or it's bigger brother.

The issue is how will their update scripts react to your table being added/modified?  Usually it's not a big deal for me but sometimes it could cause massive problems, up to silently causing issues which no one finds out until they realize their billing data is wrong or something of the sort.  If you include FKs which you probably will, note that changes to one table might also affect updates.

Problem is you need to actually make changes like this.  My suggestion would be to be open with the vendor, tell them you need this functionality so they add it to their requested features and in the meantime try to get their support on making your own tables in their app's db.  Their answer will most likely be 'we don't recommend it and we don't support it'.  Thus you should always have steps on handling this data before and after upgrades.  You could have processes based on how/when the data is accessed where you could remove the table or FKs and recreate after the update.  You'd need to track the changes diligently.

Edit:

*and hire a QA contractor 1 time or utilize a good one to have tests written before/after upgrades that cover many if not all edge cases if it's really important.


Delete data from multiple related tables I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the master table, but reference other tables also. The 200 values in the master table are spread out in other 9 tables.
I want to find out if any of the 200 values are present in all the 10 tables and delete them.
Suppose the master table has id = 1 and this record needs to be deleted. Apart from deleting the value in this table, I need to check the remaining 9 tables whether this value is present or not and delete those records also.
For example, if I need to delete the second table, the second table references 3 tables. One is the master table and other two are different tables. That is why I am in a fix.
How can I do this?
Some of the tables have been declared with on delete no action. I am not in a position to change that. Need to find another way without doing that.
Also, when I use foreign keys and delete the other tables, the other tables have been referenced by some other tables also. It is like a chain.

You can use Jailer tool, it will find all rows of child tables  that reference to master table.

Example: I have 4 tables : employee, employee_detail, phone_address, relationship. I want to delete employee which name="JOHN". With Jailer, it will find rows in employee_detail & relationship which FK to "JOHN" (by id). And because of phone_address reference to employee_detail, so Jailer will find rows in phone_address.

tb_employee ----(1,n)----tb_employee_detail----(1,n)----tb_phone_address
            ----(1,n)----tb_relationship


Teradata : Disadvantage of Collect stats I want to know any disadvantage can occur by doing collect stats on a column that is being widely used in join condition across so many procedures ? (Eg. the column is accessed 300+ times access in 60 days)

While searching in google, i came to know the only issue that can cause performance degradation is out of date Stats.
In this case, this is not going to happen as whenever data of table changes, we are going to run collect stats.

Any other disadvantage is there because of adding collect stats other than space consumption ?

The space consumption for statistics on Teradata is not significant enough to qualify as a disadvantage. For example, the statistics for a single column is retained in a 16KB VARBYTE column on DBC.TVFields

The rule of thumb has always been 10% change in the data which you have statistics collected or if they are stale. Unfortunately, stale has never really been clearly defined. Teradata 14.10 will introduce a more automated mechanism for maintaining statistics to help reduce the cost (CPU and IO) associated with the collecting stats using a homegrown maintenance schedule. This enhancement will be supported through Viewpoint.

Teradata 14 also introduced some changes with statistics that have to be taken into consideration from previous releases. Carrie Ballinger has done a good job of capturing these changes in her articles on the Teradata Developer Exchange found here and here.

Your stats maintenance schedule will be driven by the size of your environment, your ETL schedule, and manner in which your ETL maintains the target tables. We have multiple streams that maintain the same set of large target tables. As such we have moved the stats maintenance for these target tables to an external process instead of within each ETL stream.


What is the fastest way to clone/copy oracle databases on the same machine? I want to clone oracle 11g databases on the same machine and I need to change user etc. from e.g. PROD to DEV.

For that I use expdp/impdp, but this tends to be slow with increasing database size. I need to do this on linux and windows machines.

Is using expdp/impdp the fastest way to do this?

Or should I use a completly different way of doing this?
i.e. do not use the same machine,

use two machines i.e. two separate oracle servers

or install multiple oracle servers on the same machine?

I prefer to use the RMAN clone method. The overall method is to start with the PROD database, and then a DEV database that is in a NOMOUNT state with all datafiles/controlfiles/tempfiles/archivelogs removed:

oracle> rman target sys@prod CATALOG rman/x@rman_catdb

RMAN> list backup;

  1    1399    53289602077 15-DEC-11 53289629964 15-DEC-11
  1    1400    53289629964 15-DEC-11 53289657925 15-DEC-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
88      Full    20.52M     DISK        00:00:00     15-DEC-11
        BP Key: 88   Status: AVAILABLE  Compressed: NO  Tag: TAG20111215T133438
        Piece Name: /u01/backups/prod/PROD_20111215_c-4198857860-20111215-02
  SPFILE Included: Modification time: 15-DEC-11
  SPFILE db_unique_name: PROD
  Control File Included: Ckp SCN: 53295424420   Ckp time: 15-DEC-11

target database Password:
connected to target database: PROD (DBID=4198857860)

RMAN> connect auxiliary /

connected to auxiliary database: DEV (not mounted)

RMAN> run {
2> set until scn 53289602077;
3> duplicate target database to dev;
4> }

This gives me a block-for-block recreation of the production level database, and depending on how many threads you give RMAN, can be completed pretty quickly.

From there, I have a set of environment specific scripts I run that change passwords, null out email addresses, etc in the DEV database to configure it the way it needs to be.


How to pass table name and column name dynamically in Cursor I would like to delete millions of records from lots many tables. In the below code, i'm trying to select a table with some filter condition and trying to delete using its row id. In the below, for the cursor test_cusr i'm selecting from (region) table and column (code) = 'XY'. I would like to pass the table, column and the value in dynamically. I tried with for loop and ref cursor but getting some error. Can any one help me in this.

DECLARE
CURSOR test_cur
  IS
    SELECT rowid row_id FROM region WHERE code = 'XY';
TYPE test_aat
IS
  TABLE OF ROWID INDEX BY PLS_INTEGER;
  l_test test_aat;
  limit_in NUMBER := 10000;
BEGIN
  OPEN test_cur;
  LOOP
    FETCH test_cur BULK COLLECT INTO l_test LIMIT limit_in;
    IF l_test.COUNT = 0 THEN
      EXIT;
    END IF;
    FORALL indx IN l_test.FIRST .. l_test.LAST
    DELETE FROM region WHERE rowid = l_test(indx);
    COMMIT;
  END LOOP;
  IF test_cur%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE(test_cur%ROWCOUNT);
    CLOSE test_cur;
  END IF;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  IF test_cur%ISOPEN THEN
    CLOSE test_cur;
  END IF;
  ROLLBACK;
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

If I were you, I'd put the values of the tables being deleted into an array, and then loop through that to do a single delete on that table. E.g.:

DECLARE
  TYPE tab_col_rec IS RECORD (owner all_tables.owner%TYPE,
                              table_name all_tables.table_name%TYPE,
                              column_name all_tab_columns.column_name%TYPE,
                              predicate_value VARCHAR2(4000));

  TYPE tab_col_arr IS TABLE OF tab_col_rec INDEX BY PLS_INTEGER;

  v_arr tab_col_arr;
  v_sql CLOB;

  v_idx PLS_INTEGER;
BEGIN
  v_arr(1).owner := 'owner1';
  v_arr(1).table_name := 'region';
  v_arr(1).column_name := 'code';
  v_arr(1).predicate_value := 'XY';

  v_idx := v_arr.first;

  WHILE v_idx IS NOT NULL
  LOOP
    v_sql := 'DELETE FROM '|| dbms_assert.qualified_sql_name(v_arr(v_idx).owner || '.' || v_arr(v_idx).table_name) || CHR(10) ||
             'WHERE  ' || dbms_assert.simple_sql_name(v_arr(v_idx).column_name) || ' = :predicate_value';

    --dbms_output.put_line(v_sql);  -- useful for checking the sql to be run

    EXECUTE IMMEDIATE v_sql USING v_arr(v_idx).predicate_value;

    COMMIT;

    v_idx := v_arr.next(v_idx);
  END LOOP;
END;
/

I've put what I thought was relevant into the array record type, but you may need more or fewer fields as you see fit.

I've also used a single DELETE statement, since that will be faster than committing every N rows. If you don't have enough UNDO tablespace to do a single delete, I recommend you get it extended, rather than having to commit periodically.


Why is my recursive CTE so much slower on Azure SQL? I have this simple recursive CTE for a hierarchy of folders and their paths:
WITH paths AS (
    SELECT Id, ParentId, Name AS [Path] FROM Folders
    WHERE ParentId IS NULL

    UNION ALL

    SELECT f.Id, f.ParentId, [Path] + '/' + f.Name FROM Folders f
    JOIN paths on f.ParentId = paths.Id
)
SELECT Id, [Path] from paths
WHERE Id = @FolderId

On my local SQL Server express, it runs in 35 ms no problem. On my Azure SQL database, it occasionally takes around 400 ms. The Azure SQL table only has around 2000 rows, and other simple queries only take around 80ms. I have indexes on Id and ParentId. My app can make this query very frequently and it's annoying to have to wait half a second or more every time. Here are screenshots of the two separate plans, no idea why they are different:
https://www.brentozar.com/pastetheplan/?id=Hky4zASAI

https://www.brentozar.com/pastetheplan/?id=H17LfRHAU

Is Azure SQL just slower?
EDIT: this has nothing to do with Azure SQL, the table there was a lot bigger than my test.

I checked your query again and it seems you can rewrite it this way:
WITH paths AS (
    SELECT Id, ParentId, Name AS [Path] FROM Folders
    WHERE Id = @FolderId

    UNION ALL

    SELECT f.Id, f.ParentId, f.Name  + '/' + [Path] FROM Folders f
    JOIN paths on paths.ParentId = f.Id
    )
SELECT @FolderId AS Id, [Path] from paths
WHERE ParentId IS NULL


How to compare MySQL privileges granted to privileges used? I have inherited a MySQL server that has a user with far too many privileges granted but I believe not all of these are required.

Is there any way to compare the MySQL privileges granted to the MySQL privileges actually used?

This is a demanding task that may not give the exact answer. The only exact way is that you know requirement of applications that use that user privileges to access specific DB.

You must know: does that particular user administrate MySQL server? If not, remove privileges to 'mysql', 'performance_schema' and 'information_schema'.

Second step:
If you are uncertain which DBs are used, you may check logs.

There is useful log config that can be put in my.cnf: How to Log All mysql queries into log file?
Bear in mind that it will put additional load on your server, but it may give you info about usage.
Anyway, you will need to check logs for a week or more to be able to determine which queries are executed by this user and to determine which privileges are required. Bear in mind that some queries will be executed only sporadically, like some monthly tasks (usually procedures), that will not show up so easily.

The best way is to find out which applications use that user and to decide from that which DBs can be excluded from privileges given to that particular user.


Does phpMyAdmin only works with HTTPS? I have manually setup phpMyAdmin (downloaded from official site) on my site.
However, when I enter my User&Pass in login page, i get warning:

When i try same page with HTTPS, it logins. Any idea why this could happen?
tried $cfg['ForceSSL'] = false; but same happens.

I've tried same package on my localhost (where i dont have https) and it logins.

No, phpMyAdmin doesn't require using HTTPS. Some server configurations seem to be experiencing this problem, which is reported at the phpMyAdmin issue tracker at https://github.com/phpmyadmin/phpmyadmin/issues/14184.

For me, trying to log in a second time works, for others there are add-ons such as Katalon Recorder (Selenium IDE for Chrome)  or uBlock Origin preventing the file /js/vendor/js.cookie.js?v=4.8.2 (or whatever your version is) from loading.

Finally, there can be some cookies "stuck" between secure and insecure mode; you may be able to clear those directly through your browser (phpMyAdmin cookies start with "pma").


Deleting table entries based on criteria in another table We want to be able to delete entries from a MySQL table, based on deletion criteria set in another table.  Let me explain with an example.
I have two tables defined as follows:
CREATE TABLE base_tbl
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  f1 VARCHAR(8),
  f2 VARCHAR(8),

  PRIMARY KEY (id)
);

CREATE TABLE del_criteria_tbl
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  f3 VARCHAR(8),
  f4 VARCHAR(8),

  PRIMARY KEY (id)
);

base_tbl has the data and del_criteria_tbl has the criteria for deleting entries from base_tbl.
I populate the tables as follows:
INSERT INTO base_tbl(f1, f2) VALUES ('ABC', '123C57'),
('ABC', '532B49'), ('DEF', '397F45'),
('DEF', '684G65'), ('GHI', '793A86'),
('GHI', '541H32');

and
INSERT INTO del_criteria_tbl(f3, f4) VALUES ('ABC', '532B49'),
('ABC', '813E89'), ('DEF', '397F45'),
('GHI', '541H32');

Obviously:
mysql>SELECT * FROM base_tbl;
+----+------+--------+
| id | f1   | f2     |
+----+------+--------+
|  1 | ABC  | 123C57 |
|  2 | ABC  | 532B49 |
|  3 | DEF  | 397F45 |
|  4 | DEF  | 684G65 |
|  5 | GHI  | 793A86 |
|  6 | GHI  | 541H32 |
+----+------+--------+

mysql>SELECT * FROM del_criteria_tbl;
+----+------+--------+
| id | f3   | f4     |
+----+------+--------+
|  1 | ABC  | 532B49 |
|  2 | ABC  | 813E89 |
|  3 | DEF  | 397F45 |
|  4 | GHI  | 541H32 |
+----+------+--------+

I would like to define a succinct and efficient SQL operation that executes the following pseudo-SQL logic:
DELETE FROM base_tbl WHERE base_tbl.f1 = del_criteria_tbl.f3 AND base_tbl.f2 = del_criteria_tbl.f4

After the operation is executed, SELECT * FROM base_tbl should yield:
+----+------+--------+
| id | f1   | f2     |
+----+------+--------+
|  1 | ABC  | 123C57 |
|  4 | DEF  | 684G65 |
|  5 | GHI  | 793A86 |
+----+------+--------+

A simple solution is using IN clause in the following way:
DELETE FROM base_tbl
WHERE (f1, f2) in (select f3, f4 from del_criteria_tbl)


MySQL equivalent of WITH in oracle Is there a MySQL equivalent of the WITH clause in Oracle?

There is not. Unless (until) one develops it (MySQL is open-source, anyone can contribute.)

The ANSI/ISO SQL WITH keyword is used to define Common Table Expressions (CTEs) and it simplifies complex queries with one or several nested references. It's available in Oracle, Postgres, SQL-Server, DB2 but not in MySQL.

The final query may have references (usually in the FROM clause but they could be in any other part) to anyone of the common table expressions, one or more times. The query can be written (without CTEs) in MySQL using derived tables but the references have to be made repeatedly.

Example of a silly query showing all persons born in the 50s and in the month of July and the number of all persons born in the same year:

WITH a AS
    ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
      FROM persons
      WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01'
    )
, b AS
    ( SELECT birthyear, COUNT(*) AS cnt
      FROM a
      GROUP BY birthyear
    )
SELECT a.name, a.birthdate, b.cnt AS number_of_births
FROM a JOIN b
  ON a.birthyear = b.birthyear
WHERE MONTH(a.birthdate) = 7 ;

In MySQL, it could be written as:

SELECT a.name, a.birthdate, b.cnt AS number_of_births
FROM
    ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
      FROM persons
      WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01'
    ) AS a
  JOIN
    ( SELECT birthyear, COUNT(*) AS cnt
      FROM
        ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
          FROM persons
          WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01'
        ) AS aa
      GROUP BY birthyear
    ) AS b
  ON a.birthyear = b.birthyear
WHERE MONTH(a.birthdate) = 7 ;

Notice the duplication of code for the derived table a. In more complex queries, code would have to be written multiple times.


Moving postresql 9.1 database - live This question has probably been asked and answered before, and if so, I would appreciate if that could be pointed out to me.

I have googled this point quite a bit, but I want to be very sure as to what I am doing before performing this task:

I need to move my current postresql 9.1 database to a new server. This new server will be taking over on that account and the other "turned off".

I need to do this live without, as much as possible, disturbing the users or having to shut down operation.

I also need to move without any possibility of data-loss (anything being written during the move).

Like I said above, if this is already answered/laid out anywhere, I would much appreciate being pointed to it.

Note:

I am currently running on Gentoo Linux and will be moving to a Ubuntu server.

If the PostgreSQL version and architecture is the same on the target so they're binary compatible: Set up streaming replication with WAL archive fallback, so the new host replicates from the old one. Enable hot_standby mode so you can connect to the new host read-only. Test. Then when you're ready to cut over, stop the old server and pg_ctl promote the new server to a read/write master.

If the PostgreSQL version is different on the target: Set up Londiste or Slony-I logical replication from the old DB to the new one. Let it sync up. When ready to switch over, shut down the old server, stop the replication service, switch clients to write to the new server.


MariaDB tmpdir & table copy operations I have a Linux server & MariaDB v10.0.33, where tmpdir is tmpfs (ram), for fast temporary table access; tmpfs is obviously limited in size.

MariaDB seems to use the same tmpdir location for alter table operations (table copy), that require a table copy;  these can require large amounts of space if tables are many Gb.

Old MySQL versions configure 'table copy' and 'temp table' separately;
Table copy written to tablespace & temporary tables written to tmpdir.

I am aware I can set 'innodb_tmpdir' per command.
Can MariaDB configured so that table copy & temporary tables use different locations?

I think it is a bad idea to rob space in RAM for things other than innodb_buffer_pool_size.

Furthermore, your example is flawed.  An ALTER that needs to copy works this way:

Create tmp table and populate it;
"Move" the table into where the original table is.

Step 2 is the issue:

If the tmp and the original are in the same filesystem, these are fast operations involving the directory.
If they are in different filesystems (and tmpfs is definitely different), the "move" is really a more-costly "copy".


MYSQL database failover Current status

I have a MySQL (MariaDB 10.2) server with various databases, to which a lot of people have access to. Which means that users can create MyISAM or InnoDB tables/databases.

Problem

Mentioned MySQL server doesn't have any serious fallback/backup, except daily backups. There used to be simple master-slave replication in use, but after numerious sync problems, it had to be stopped. I guess that most of problems came from mix of MyISAM and InnoDB tables, but I couldn't pinpoint the problem with 100% accuracy.

Question

What is the best way to set-up some kind of replication/fallback, that would work with minimum overwatch?

One of the problems is also that size of databases is around 100GB and downtime is undesirable. If I want to set-up a replication, is there any option to do it without serious downtime?

Cloud solutions aren't an option.

Mixing MyISAM and InnoDB in ordinary Replication is OK.
MyISAM can screw up in any form of replication.
MyISAM is not allowed in the latest HA techniques -- Galera and Group Replication.
Master-Master, but writing to only one at a time, will let you continue to have MyISAM, and give you quick (perhaps one-minute) failover.
MHA is another approach using standard Replication.

In the long run, you really need to choose between HA and MyISAM.


SQL Server 2008 r2: What events/queries hit tempdb We had a slowdown on a db server this morning. Checking the logs, the only thing that happened during the 2-minute slowdown worth noting was an object being created and deleted in tempdb over the span of about 3ms. I know that temporary tables and table variables would do this, but as far as I can tell none were used. Are there any other actions (e.g. nested selects) which might create a temporary table?

P.S. We've already figured out that it was most likely a log expansion that caused the slowdown, and the hit to tempdb was unrelated. I just wanted to ask for academic purposes and because I just cannot figure out what caused tempdb to be used.

To summarize Tempdb is used to store 3 types of objects:

user objects

local temporary table (seen by one session)
global temporary table (seen by all sessions)
Table variables

internal objects used temporarily by SQL server during Query processing : operations such as sort , join, hash and cursors all require space in tempdb. see the sys.dm_db_session_space_usage DMV.
the version store :
The following features make use of the version store:

Triggers
Snapshot isolation or Read-Commited Snapshot isolation
Online index operations
Multiple Active Result Sets (MARS)

Please note that using the sys.dm_tran_version_store can be ressource intensive.

May I suggest that you check Troubleshooting insufficient disk space in tempdb for a few examples of how to use the DMV.


problem with the restored database I have a database storing user login credentials of my application in a Table. When I backed up and restored my database to another server, the restore is successful but the login is timing out (SQL Server timeout). Even though I increased the timeout, still no luck.Can anyone suggest what can be the reason for that.

Can re-indexing the database help me in the new server?

What my colleagues are attempting to say in the comments, is that, they / we are making an assumption that the issue is due to the account / sql login not existing on your new server (the one where you restored your database). The most likely cause of what is going on is that since the login / sql account you use on your original server doesn't exist on the new server it's causing your application / connection to time out.

No biggie... easy fix with two options that come to mind to help resolve the issue.

First option - Manually create a new user with the same name and password then run the following script on the database you restored:

sp_change_users_login 'update_one', '[yourDBuser]', '[yourSQLlogin]'

This will connect the newly created SQL Server login to the user in the database you are having issues with.

Second option (I prefer this one) - Install / create the sp_help_revlogin stored procedure on your original server. You can find the script here.

Once it's installed execute the following:

sp_help_revlogin '[yourSQLlogin]'

This will generate an output like so:

-- Login: yoursqllogin
CREATE LOGIN [yoursqllogin] WITH PASSWORD = 0x0200EF889B2ECBF4A17B836A62AE80BC0F718EBF117C73697892B112C3744D04AB18490E8C666C1154F42BC3D0C49346843032AFBFBEBA28CFE018AB1FED25EF2830A5F046E9 HASHED, SID = 0x01, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

Copy and paste the output onto your new server, and you will be rockin' and rollin' in no time.


MS SQL 2008 Databse in Suspect mode and CHECKDB returns error: Object ID 3 has chain linkage mismatch We did a disk expansion for VM DB server. However, after this operation the DB became suspect mode and cannot be open.

Then we tried somethings like that;

https://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html

But the first CHECKDB step returned the following results:

  Msg 7987, Level 16, State 1, Line 1 System table pre-checks: Object ID
  3 has chain linkage mismatch. (1:13853247)->next = (1:446), but
  (1:446)->prev = (1:17209791).

  Check statement terminated due to
  unrepairable error. DBCC results for 'myDB'. CHECKDB found 0
  allocation errors and 0 consistency errors in database 'MyDB'.

Also, we bought some data recovery tools but they can transfer some of the data, not all of it.

Are there any suggestions?

The repair is unrepairable (linkage issue in system table), so you would need to restore it from a good known backup to a point in time (provided the DB is in full recovery and you were taking log backups) you did the disk expansion.

You can try repair allow data loss as last resort.


What index should I use for a read-only database? The database looks like this:

Parameters has 126000 rows, which link an ExperimentID to the starting parameters for that ExperimentID. Simulations has ~107M rows, but I really only query against Xa (a floating point value with mostly unique entries). I'm interested in the other data, but for now it's just there. The current indices I have are the Primary Key index on Simulations, which is clustered, and a nonclustered columnstore index on the following columns:

Here's an example query I might be running against this database:

USE IMTsimulations
SELECT DISTINCT p.ControlRange, p.SensorError,
    PERCENTILE_DISC(0) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_MIN,
    PERCENTILE_DISC(.25) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Quartile25,
    PERCENTILE_DISC(.5) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Median,
    PERCENTILE_DISC(.75) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Quartile75,
    PERCENTILE_DISC(1) WITHIN GROUP( ORDER BY s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_MAX,
    AVG(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_Mean,
    STDEV(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as Xa_STDEV,
    COUNT(s.Xa) OVER (PARTITION BY p.ControlRange, p.SensorError) as TotalEvents

FROM dbo.Simulations s JOIN Parameters p ON s.ExperimentID = p.ExperimentID
WHERE s.TimeElapsed <= 7200 and p.ExogDexCurve =  4
ORDER BY p.ControlRange, p.SensorError

As you can see, I'll be mostly looking at aggregates of Xa according to certain starting parameters. I feel like my primary key shouldn't be cluster, and the columnstore index should be clustered, and it should only have Xa, but I don't know for sure. The dataset is complete, so there will be no INSERTs, UPDATEs, DELETEs, or anything like that on either table.

What indexes should I be building for this thing? What's going to let me efficiently query aggregates on Xa that are partitioned according to things in the Parameters table?

I'd appreciate any depth you go into, as I'm doing this on my own, and don't have much technical background with databases.

You said, that TimeElapsed is not really filtering much, so there is no point in indexing it.

Just looking at the query structure, I'd add an index to Parameters table on
(ExogDexCurve, ControlRange, SensorError). The order of columns is important.

I would also make sure that all indexes have Fill Factor=100, because the data is static.

But, overall, the main bottleneck is reading all Xa values for the needed ControlRange and SensorError.

The server has to read all this data. Storing it in columnstore index should help.

I would also consider denormalizing the data and making just one table (persisting the join). In this case I'd create a covering index. The order of columns is important.

(ExogDexCurve, ControlRange, SensorError, Xa) INCLUDE (TimeElapsed)

Xa is part of an index rather than included, because you order by Xa.

With one joined table and this covering index the query should scan the index without extra sorts.

Also, to "hide" the persisted joined table it may be possible to create an indexed view of the join instead of explicit table, but I'm not sure.


MySql shell command not replicated to slave I have a shell script that looks something like that:

 #!/bin/bash
 mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv'
 INTO TABLE db_name.db_table FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'
(\`col1\`,\`col2\`,\`col3\`);"

the problem is that when I run this script on my master server the call doesnt get replicated to the slave server (even though /srv/some_file.csv exists on both servers).

any ideas how to make shell command replicate over servers?

Have I got a stunning revelation for you on this problem. I saw this type problem before. I have a MySQL client at my employer's web hosting company with this situation :

The client, running MySQL 5.1.37, has two DB servers in Circular Replication (call it M1 and M2)

He performed a LOAD DATA LOCAL INFILE with a 50GB CSV file on M1

Running SHOW SLAVE STATUS\G several times on M2, Log Files and Positions were not moving.

Then, I noticed something very disturbing. When I ran SHOW SLAVE STATUS\G several more times on M2, I noticed this pattern in the output:

Relay_Log_Space was growing
Seconds_Behind_Master was still 0
Still, none of the Log Files or positions were changing

I was stunned because I did not know what was happening. A few hours later, I ran SHOW PROCESSLIST;. To my surprise, there it was : LOAD DATA LOCAL INFILE in the SQL Thread, but the file name was a temp file in the /tmp folder. All of a sudden, replication was more than 6 hours behind. Then, is dawned on me what was actually happening.

I learned that day that for mysqld to replicate LOAD DATA LOCAL INFILE, this is happens under the hood:

When a Master server executes LOAD DATA LOCAL INFILE

mysqld on the Master will...

complete the LOAD DATA LOCAL INFILE command
record the LOAD DATA LOCAL INFILE command its binary logs
dump the enitre data file it just imported into its binary logs

mysqld on the Slave will do the following:

I/O Thread sees LOAD DATA LOCAL INFILE in the relay logs
I/O Thread create the import file need for LOAD DATA LOCAL INFILE
I/O Thread copies the contents of all relay logs making up the import file, storing it in /tmp
SQL Thread updates Seconds_Behind_Master based on when the Master completed LOAD DATA LOCAL INFILE command
SQL Thread executes LOAD DATA LOCAL INFILE using the mainifested temp file

In order to verfiy this is indeed happening:

On the Master

create a MyISAM table in the test database called mycsv
Run LOAD DATA LOCAL INFILE to import a 5 line file CSV file into test.mycsv

On the Slave

See if test.mycsv exists with the imported data in it
See if any file exists in /tmp that has the exact same size as the original import file on the Master

If that is what happened, test that again against a 10 GB file.

While it is replicating, goto into the OS in /var/lib/mysql and do ls -l and look for the relay logs. You should see 10 consecutive relay logs 1GB. That will show it for sure because under normal circumstances, every relay log context switch skips by 3 files not 1. Seeing multiple 1GB relay logs reveal that the data for the LOAD DATA LOCAL INFILE recently executed on the Master is currently being shipped over.

I hope this solves the mystery you have been experiencing.


MS Access migration from Oracle to SQL server? Let me start off by saying I'm relatively new to databases etc. I have been asked to undergo a project is which I will attempt to migrate about 100 or so existing MS access 2003 databases from using linked Oracle 11g linked tables to using MS SQL server linked tables.

If you can imagine these 100 MS Access tables are vital and they currently do their job perfectly however the project is intended to just move away from Oracle in general. the SQL server database and tables all exist currently. The sql server and oracle versions both have the same content the only difference if the column naming convention i.e. ‘POLICY_NUMBER’ in Oracle and ‘PolicyNumber’ in sqlserver. What I want is to this is in the easiest way possible to make the Access queries/reports/forms work by simply changing the link from oracle to SQL server.

My points of investigation currently have been looking one Access database and trying to do a proof of concept on that single table. I have been looking at the MSysQueries table, in thinking I could alter the queries in here to point it to different databases and column names. This way I would not have to change the queries individually. I have not been too successful so far and feel way out of my depth.

The bottom line of this question and the reason for posting is simply asking has anyone ever done anything similar? What would you're approach be? Am I looking in the complete wrong direction?

EDIT:
The sqlserver and oracle both do the same task, they take a copy of production data and have it available to test on test databases. The reason they both do the same thing is the reason for this project, to move away from oracle in general and licensing fees etc. The access databases are used to view and execute certain queries. They work and people are happy with them but since a move from Oracle they need to be altered. It is kind of irrelevant what the function of the Access databases is because they cannot be broken or rewritten plain and simple.

Create new linked tables to the SQL Server, drop the Oracle-linked-tables, create new queries with the names of the original Oracle-linked-tables, and presto.

Having said that, if you do this in the Access user interface, Access will attempt to modify the existing queries automatically.  You can change this behavior by deselecting the following options:

Caveat:  You'd clearly want to do this on a test-copy of the database, then test all the functionality before declaring this a win!


Compare byte value to bytea column Is there a way to compare a single byte value to a bytea field in PostgreSQL?

For example: I have a bytea field with the value \x11AA22BB. The comparison to a byte value of AA should be true while the comparison to a byte value of 1A should be false.

The only way I see is to extract each byte from the bytea column separately with something like get_byte(string, offset) and check each one.

I have a bytea field with the value \x11AA22BB. The comparison to a
  byte value of AA should be true

It seems you're looking for the position function, knowing that it will return 0 if the byte is not contained in the string:

select position('\xAA'::bytea in '\x11AA22BB') > 0 AS contained;

 contained
-----------
 t

If the byte value is stored in an integer, set_byte can be used to place it into a bytea:

select position(set_byte('\x00', 0, 170) in '\x11AA22BB') > 0;


How to troubleshoot enq: TX - row lock contention? I have the following situation.

I have RAC. On both nodes there are the locks.

On the First Node

    SID EVENT                           USERNAME    BLOCKING_SESSION    ROW_WAIT_OBJ#   OBJECT_NAME LOCKWAIT            SQL_ID          STATUS
1   102 enq: TX - row lock contention   MYUSER      155                 136972          TABLE1V     0000000810EFA958    5f4bzdg49fdxq   ACTIVE
2   111 enq: TX - row lock contention   MYUSER      155                 136972          TABLE1V     0000000810EFAC98    5f4bzdg49fdxq   ACTIVE

Blocking session info

    SID EVENT                       USERNAME    ROW_WAIT_OBJ#   OBJECT_NAME LOCKWAIT    SQL_ID          STATUS
1   155 SQL*Net message from client MYUSER      136971          MyTABLEIMAGES_IDPK      4hw85z8absbjc   INACTIVE

On the Second Node

    SID EVENT                           USERNAME    BLOCKING_SESSION    ROW_WAIT_OBJ#   OBJECT_NAME   LOCKWAIT          SQL_ID          STATUS
1   65  enq: TX - row lock contention   MYUSER      155                 137033          FactTABLE1V   0000000810EF9B58  1mznc2z75ksdx   ACTIVE
2   111 enq: TX - row lock contention   MYUSER      155                 136972          TABLE1V       0000000810EF9818  5f4bzdg49fdxq   ACTIVE

Blocking session info

    SID EVENT                       USERNAME    ROW_WAIT_OBJ#   OBJECT_NAME  SQL_ID  STATUS
1   155 SQL*Net message from client MYUSER      127176          MYTableLOG           INACTIVE

Additional Info : Blocking session SQL_TEXT

create or replace procedure ACTIONProcedureDELETE
(
p_ID NUMBER
)
 is

 cursor oldval is select r.id,r.sessionstatus
  from MyTABLEIMAGES  r where r.idparent=p_ID;

begin
       update  actionmyTableblock r  set r.status='False' where  ID=p_ID;

   for oldvalItem in oldval loop

    if oldvalItem.Sessionstatus='True' then
      update MyTABLEIMAGES r set r.sessionstatus='False' where r.id=oldvalItem.Id;
    else
      update MyTABLEIMAGES r set r.sessionstatus='True' where r.id=oldvalItem.Id;
    end if;
  end loop;

end ACTIONProcedureDELETE;

How do I troubleshoot this ?

As you can see blocking session is INACTIVE but still locking.

If I select v$sql_bind_capture there is no value for VALUE_STRING for blocking session sql_id.

From where to start?

I can guess that there is missing commit/rollback but application developer says "I have everything ok, I have written commit where it is necessary"

Please help.

Query v$transaction on each node to see uncommitted sessions:

SELECT t.start_time, s.sid, s.serial#, s.username, s.status,s.schemaname, s.osuser
   , s.process, s.machine, s.terminal, s.program, s.module
   , to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
FROM v$transaction t, v$session s
WHERE s.saddr = t.ses_addr
ORDER BY start_time;


procedure for postgres to create table if not exist i want to create table if that doesn't, i tried below code:

create or replace function create_table() returns void as
$$
begin
    if not exists(select *
                  from   pg_tables
                  where  schemaname = 'Public'
                  and    tablename = 'test')
    then

        create table test
        (
            the_id int not null,
            name text
        );

    end if;

end;
$$
language 'plpgsql';

While executing this procedure first time:

select creat_table();

table gets created but when I execute it again I get the below error:

ERROR:  relation "test" already exists
CONTEXT:  SQL statement "create table test
        (
            the_id int not null,
            name text
        )"
PL/pgSQL function create_table() line 8 at SQL statement
********** Error **********

ERROR: relation "test" already exists
SQL state: 42P07
Context: SQL statement "create table test
        (
            the_id int not null,
            name text
        )"
PL/pgSQL function create_table() line 8 at SQL statement

How to achieve this, and also I want to call this procedure from Informatica pre-sql target session property so i want to call procedure with table name as parameter.

Just use CREATE TABLE [IF NOT EXISTS]

Looks like this,

CREATE TABLE IF NOT EXISTS test (
  the_id  int   PRIMARY KEY,
  name    text
);

If you must wrap it in a function (don't though, there is no point),

CREATE FUNCTION myCreateTable() RETURNS void AS $$
  CREATE TABLE IF NOT EXISTS test (
    the_id  int   PRIMARY KEY,
    name    text
  );
$$ LANGUAGE sql
VOLATILE;

If you want that function to accept a name (still wouldn't suggest this),

CREATE OR REPLACE FUNCTION myCreateTable(myIdent text) RETURNS void AS $$
  BEGIN
    EXECUTE format(
      '
        CREATE TABLE IF NOT EXISTS %I (
          the_id  int   PRIMARY KEY,
          name    text
        );
      ',
      myIdent
    );
  END;
$$ LANGUAGE plpgsql
VOLATILE;

[ IF NOT EXISTS ] has been in PostgreSQL since 9.1


Installing PostgreSQL 9.2 and PostGIS 2.0 on Ubuntu 12 At the current moment, March 4th 2013, can PostGIS2.0 be install with PostgreSQL 9.2?

I checked their website and to my understanding it is not possible...

I hope that's not the case. Can anyone tell me and point out the instructions on how to install PostGIS 2.0 on PostgreSQL 9.2 on Ubuntu?

Just a followup: I just checked it and now there is a package called postgresql-9.2-postgis-2.0.  The problem is finally gone.


Firebird 3.0 service won't start on Windows 7 I have Firebird 2.5 classic server running on Windows 7 on port 3059. This service is set to automatically start up and it works fine.

I've installed Firebird 3.0.2 as classic server on port 3050 but service won't automatically start. I can start it manually but I really need it to start automatically.

  Error 1053: "The service did not respond in a timely fashion"

is the error I get in Event Viewer. Windows has only one, administrator user, without password. That's only error.

This could be that your system just has too much going on at initial boot or maybe the 3.0.2 service shares some resources with the 2.5 service and is running into a timeout that clears itself by the time you are able to manually start the service.  Honestly, I don't know enough about Firebird Database to know for sure, but one thing you can try is delaying the automatic start of the 3.0.2 service and see if that gets the process going automatically.
To make this change, open up services.msc, find  your Firebird 3.0.2 service and switch the startup type to Automatic (Delayed Start) and see if that gets this service working automatically.  Here's an example screenshot, of what I'm talking about (but with SQL Server instead of Firebird).

The functionality of the delayed start is best described by CoreTech's post from the SuperUser forum:

A service marked as Automatic (Delayed Start) will start shortly after all other services designated as Automatic have been started. In my experience, this means that they are started 1-2 minutes after the computer boots.
The setting is most useful in lessening the "mad rush" for resources when a machine boots.
Note that when you have 20 services all being started at the same time, each will start up slower as it competes with the others for slices of the machine's precious resources (CPU/RAM/Disk/Network). That is, each service takes longer to become available!
If you have a few services that are critical, then you may want to set those few to Automatic and set as many of the others as you can to Automatic (Delayed Start). This will ensure that the critical services get the most resources early and become available sooner, while the non-critical services start a bit later (which by definition is ok).

No guarantees this works for you, but my fingers are crossed it does.


Checking space availability before a VACUUM FULL (postgres) Is it possible to check how much re-usable space is available in a tablespace before running a VACUUM FULL on a large table?

I have a large postgres table (about 20G) that gets an occasional VACUUM FULL. The available freespace on that drive varies between 15-25 Gbytes. Before each vacuum is attempted I log the table size (using postgres queries) and the available diskspace (using OS tools).

I know that VACUUM FULL requires a full copy to be made of the table. So if the table is 20G then 20G of freespace is required.

Sometimes the table will be 20G, there will only be 15G OS space available and the vacuum will work. I guess the extra 5G required is recovered internally from the tablespace.

Other times the vacuum will fail due to lack of space, I guess on these occasions the extra 5G required wasn't found in the tablespace.

I'd like to be able to check that I've got enough space for a VACUUM FULL beforehand, how can I do this? I know how big the table is, I know how much space is available to the OS, but what I don't know is how much re-recyclable space is available in the tablespace.

Firstly, I would suggest using pgstattuple to obtain tuple-level statistics.

  pgstattuple returns a relation's physical length, percentage of “dead”
  tuples, and other info. This may help users to determine whether
  vacuum is necessary or not.

For example:

create extension pgstattuple ;
create table my_table ( id int , name text);
insert into my_table select a, md5(a::text) from generate_series(1, 1e7)a;

-- size of my_table
 Schema |   Name   | Type  |  Owner   |  Size  | Description
--------+----------+-------+----------+--------+-------------
 public | my_table | table | postgres | 651 MB |

-- dead_tuple_percent = 0, pgstattuple will not lock your table
SELECT tuple_percent, dead_tuple_count, dead_tuple_percent, free_space, free_percent FROM pgstattuple('my_table');

 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         89.35 |                0 |                  0 |     338776 |         0.05

-- let update 50% rows
update my_table set name = name || id where id < 5000000;

-- now, dead_tuple_percent = 28.63%
 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         60.43 |          4999999 |              28.63 |    1834236 |         0.17

-- size of my_table has increased

 Schema |   Name   | Type  |  Owner   |  Size   | Description
--------+----------+-------+----------+---------+-------------
 public | my_table | table | postgres | 1016 MB |

-- try to vacuum full
vacuum full my_table;

-- after that, dead_tuple_percent = 0 and size of my_table has reduced
 tuple_percent | dead_tuple_count | dead_tuple_percent | free_space | free_percent
---------------+------------------+--------------------+------------+--------------
         88.92 |                0 |                  0 |    1664780 |         0.23
 Schema |   Name   | Type  |  Owner   |    Size    | Description
--------+----------+-------+----------+------------+-------------
 public | my_table | table | postgres | 691 MB     |

Secondly, if you are in production environment, I would suggest using pg_repack to reclaim disk without locking your table.

  pg_repack is a PostgreSQL extension which lets you remove bloat from
  tables and indexes, and optionally restore the physical order of
  clustered indexes. Unlike CLUSTER and VACUUM FULL it works online,
  without holding an exclusive lock on the processed tables during
  processing. pg_repack is efficient to boot, with performance
  comparable to using CLUSTER directly.

For instance:

/usr/pgsql-11/bin/pg_repack -d postgres -U postgres -n -t my_table  &


The DELETE statement conflicted with the REFERENCE constraint I'm trying to delete all users but getting the error:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict occurred in database "workdemo.no", table "dbo.M02ArticlePersons", column 'M06PersonId'.
The statement has been terminated.

The query:

DELETE FROM [workdemo.no].[dbo].[M06Persons]
WHERE ID > '13'
GO

Seems I need to use on delete cascade; but I'm stuck.

You don't need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete a person that is still referenced by an article. It succeeded, you were just trying to do this and was blocked, kudos to the designer.

Now go and talk with that somebody that designed the schema and knows the constraints and ask him how to properly delete the records you're trying to delete, in the correct order and taking the proper precautions to keep the database consistent.


Alternative for JOIN First Trigger

ALTER TRIGGER [dbo].[Price_Modified]
    ON [dbo].[stock_recieve]
    AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Stock_out)
BEGIN
    UPDATE s
    SET value = new.Item_Price * new.Stock_out
    FROM stock_recieve AS s
    JOIN inserted AS new ON new.[Bill No] = s.[Bill No]
    JOIN deleted AS old ON old.[Bill No] = s.[Bill No]
END;

Second Trigger.

 ALTER TRIGGER [dbo].[newval]
      ON [dbo].[stock_recieve]
        AFTER UPDATE
    AS
    SET NOCOUNT ON;
    IF UPDATE (Stock_out)
    BEGIN
        UPDATE s
        SET Uservalue = new.Item_Price * new.Stock_out
        FROM stock_recieve AS s
        JOIN inserted AS new ON new.[Bill No] = s.[Bill No]
        JOIN deleted AS old ON old.[Bill No] = s.[Bill No]
    END;

Result to be obtained on first stock_out update.

| Item_Price | Stock_in | Stock_out | Value | Uservalue |
|------------|----------|-----------|-------|-----------|
| 50         | 200      | 100       | 5000  | 5000      |
| 100        | 50       | 20        | 2000  | 2000      |
| 30         | 100      | 40        | 1200  | 1200      |

Result to be obtained on second stock_out update.

| Item_Price | Stock_in | Stock_out | Value | Uservalue |
|------------|----------|-----------|-------|-----------|
| 50         | 200      | 200       | 10000 | 5000      |
| 100        | 50       | 40        | 4000  | 2000      |
| 30         | 100      | 100       | 3000  | 1800      |

The first trigger does what its suppose to do, but I want the second trigger on Uservalue to not add but instead replace the result.
Hope this is clear @Ypercube

Instead of using a trigger, you should use a Calculated Column.
This way, any change in stock_out would affect the users score.
It would be calculated automaticaly, and without duplicate rows.

An AFTER UPDATE TRIGGER could then be used to store record of previous user score, by reading data from DELETED and inserting it into an audit table.


Copy one database to another (existing) database on the same server I have a "Live" database and a "Development" database and occasionally, I want to replace the entire contents of the development database with the live data. Ideally, without taking the live database offline. Based on similar questions here and on other forums, I've pieced together this TSQL script:

USE MASTER;

BACKUP DATABASE LiveDB TO DISK = '[tempfile]' WITH COPY_ONLY

GO

ALTER DATABASE DevDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE DevDB
FROM DISK = '[tempfile]'
WITH
MOVE 'Live_db'   TO N'...\Dev.mdf',
MOVE N'Live_db_log' TO N'...\Dev_log.ldf',
REPLACE, RECOVERY, NOUNLOAD

GO

ALTER DATABASE DevDB SET MULTI_USER

The script runs, I get no error messages, but if I query any table in the development database afterwards, I see the old, unchanged development data.

I get a success message (in German), which translates to:

  RESTORE DATABASE has successfully processed 49809 pages in 16.393 seconds (23.737 MB/s)."

Immediately after the restore, I execute:

SELECT * FROM [LiveDB].dbo.SomeTable;
SELECT * FROM [DevDB].dbo.SomeTable

...and I see two completely different results. It looks as if the DevDB hasn't changed at all. I see the same rows I saw before the restore operation.

The live data is not changing; that particular table hasn't changed in the last 2 weeks at least.

What am I doing wrong? I thought WITH REPLACE would force RESTORE to overwrite the development database.

I suspected multiple backups might exist in this backup file.  If you run:

RESTORE HEADERONLY from disk = [tempfile]

this will show all the backups that are in the file.  You are not restoring the latest backup, use FILE=X to restore the appropriate backup.


View column has an 8000 character limit? In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 characters, even though the update contains a greater number of characters.

Is there some kind of limit on the number of characters that a view can receive for an update?

Ok I'm working with the application development team on this and they think they found a way around it so they discarded the code they were using as their test case.  I'll do my best to give the general idea. The update statement looked something like:

UPDATE vorders
set [description] = '<br> This is some text about the order </br>'
    + CHAR(34) + 'This is more text' + CHAR(34) + 'and on it goes'
where orderid = 12345

We would then check the length of the characters put into the field by running the following query

SELECT LEN([description])
  FROM vorders
 WHERE orderid = 12345

What the application guys found is that if they remove the + CHAR(34) + part of the code the length would go up to 8700 characters.  With the + CHAR(34) + part of the code in it was truncating the text down to 8000 characters.  This made me suspect that it was some kind of implicit conversion; however, we found that the truncation did not occur with one or two of the + CHAR(34) + in place.  The original statment had something like 30 of them.

Any idea what might be causing this cause I'm at a loss on this one.

I suspect you are simply not checking the length of the column accurately (like inspecting PRINT or SELECT output in Management Studio, which is truncated by the tool, not accurately reflecting what's actually in the database). There is no limitation on the number of characters that can be exposed or updated by a view, unless you introduce them, perhaps you are using an INSTEAD OF trigger, or there is implicit conversion going on somewhere, or your data is being truncated before the update. Hard to tell without more details, which I hope you can furnish.

In the meantime, try this:

USE tempdb;
GO

CREATE TABLE dbo.foobar(x VARCHAR(MAX));
GO

CREATE VIEW dbo.v_foobar
WITH SCHEMABINDING
AS
  SELECT x FROM dbo.foobar;
GO

INSERT dbo.v_foobar(x) VALUES('c');
GO

UPDATE dbo.v_foobar
  SET x = REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000)
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000);
GO

SELECT LEN(x), DATALENGTH(x)
  FROM dbo.v_foobar;
GO

Results:

-----    -----
16000    16000

I'm also not convinced that CHAR(34) is the culprit.

UPDATE dbo.v_foobar
  SET x = CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 800)
        + CHAR(34) + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x'
        + REPLICATE(CHAR(34), 400)
        + 'x' + CHAR(34)
        + REPLICATE(CONVERT(VARCHAR(MAX), 'c'), 8000);

GO
SELECT LEN(x), DATALENGTH(x)
  FROM dbo.v_foobar;

Results:

-----    -----
17226    17226

I suspect that something else is happening to this string as it's being generated or before the command is passed to SQL Server.


How to recover data around corrupted data in table? I have a SQL Server database with some corrupted data unrepairable via DBCC CHECKDB in a table. I can get many of the rows before the corruption out - but how do I get the rows after the corruption out?

In the end, I truncated the table as the data wasn't essential and this cleared out the corruption. I know this won't be a solution for most folks - usually you need the data.


SQL server in memory table performace I am using Microsft SQL Server 2016. I have an SQL table with 140000 rows. It is used only for selecting data.

I am trying to increase it's selection performance by making it In-Memory table (it is worth to mention that this is my first time using In-Memory tables).

The results I am getting in SQL Server profiler are rather dissatisfying. Compared data of 'CPU', 'Reads' and 'Duration' columns: SELECT from In-Memory table has 'CPU' a bit higher, 'Reads' up to 10 times lower but unfortunately 'Duration' stays pretty much the same.

I've tried generating In-Memory OLTP migration checklist - the results were successful.

Could You please tell me if this is a good use of SQL server In-Memory tables?

Maybe I am doing something wrong since duration stays the same?

Thank You in advance!

It is used only for selecting data.

  ...

  The results I am getting in SQL
  Server profiler are rather dissatisfying.

That is because you did not read what issues memory-optimized tables do address.

They were not invented to "just keep your data in memory", if your table is relatively small and frequently used, it will be in memory after the first reading from disk without any "optimizing".

Memory-optimized tables are lock free, it means in highly concurrent environment instead of beeing blocking on modifying when someone reads the data, new versions of rows are inserted, so there is no blocking.

Your table is readonly, as you said, so what did you want to achieve? There is no blocking at all on this your table, so what did you want to resolve?

If you just did something without good understanding of what you are doing, I can suppose that your bucket count was also chosen wrong, and wrong bucket number slows down your SELECT.


List All Dropped Databases I need to confirm if a particular table ever existed in our SQL Server. Is there an existing script or method one can use to list all dropped databases in an SQL Server instance?

Well, I don't know about ever, since SQL Server doesn't keep that information around forever.

Also, it's unclear if you are asking about tables or databases. Since you mentioned both, here is a query against the default trace (based on this question) that will return either, but only going back as far as the default trace goes. It shows who dropped what, when, and from where.

DECLARE @path nvarchar(260) = (SELECT
    REVERSE(SUBSTRING(REVERSE([path]),
    CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
  FROM sys.traces WHERE is_default = 1);

SELECT ObjectType,
       ObjectName,
       DatabaseName,
       LoginName,
       HostName,
       ApplicationName,
       StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 47 AND EventSubClass = 1
  ORDER BY StartTime DESC;

The enum for ObjectType is documented here, but if you want to filter, you can use ObjectType = 16964 for databases and ObjectType = 8277 for tables.

The default trace rolls off and purges the oldest rows/files as it adds new ones. If you need to go back further than that, you will need to resort to restoring older backups, if you still have them.


Conext menu behaviour change in SSMS 18.0/18.1 I'm using the new SSMS 18.0 or 18.1 and one thing extremely annoys me -

Upon pressing right click, the context menu is open.

I used to press the "up arrow"+"Return key" to pick the Refresh option.

The circularity isn't working anymore.

Is there anyway to change it?

Thanks!

While I cannot reproduce this problem (i.e. I can right-click on any item in Object Explorer and press ⇧ once to get to the bottom of the menu in SSMS 18.0 RTM), if the goal is to reduce the number of times you press a key, then I will suggest abandoning the desired approach (2 keys pressed: ⇧ and then Enter ↲) in favor of simply doing a left-click and then pressing F5 once (i.e. "Refresh"), thereby saving one key-press every time 😺.


Creating non-clustered Index on Partitioned Table that already has an existing Primary Key When I create a Primary Key as so:

ALTER TABLE dbo.TABLEA
ADD [MY_KEY] UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID()

And then proceeded to create the following index:

CREATE NONCLUSTERED INDEX [IDX1_TABLEA] ON [dbo].[TABLEA]
(
    [COL_1] ASC,
    [COL_2] ASC,
    [COL_3] ASC,
    [COL_4] ASC,
    [COL_5] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

The following exception is thrown:

  Column [Col_1] is partitioning column of the index PK_TABLE_A_1234.
  Partition columns for a unique index must be a subset of the index
  key.

I think that in order to solve this issue, all I need to do is:

A. Include column MY_KEY as part of the IDX1_TABLEA or ...

B. Include all columns from IDX1_TABLEA as part of the PK as well as including the PK (MY_KEY) as part of the IDX1_TABLEA?

Unfortunately I cannot test either option reliably since our TABLE_A in our DEV environment is not partitioned whereas in our TEST environment the same table is partitioned.

The exception actually occurred while the script to create the PK and the INDEX was being executed in our TEST environment but the same script ran perfectly fine in DEV.

FYI: I already tried option A (adding the MY_KEY column as part of the index IDX1_TABLEA) and it worked in DEV, but again, I'd like to confirm with some expert here that this will work fine when I execute the modified script in the TEST environment again.

So my modified version is this:

CREATE NONCLUSTERED INDEX [IDX1_TABLEA] ON [dbo].[TABLEA]
    (
        [MY_KEY] ASC,
        [COL_1] ASC,
        [COL_2] ASC,
        [COL_3] ASC,
        [COL_4] ASC,
        [COL_5] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

Can anyone confirm whether this is enough to get rid of that exception?

Thanks.

Update

Issue solved. See my own answer below.

In summary, I was creating a new PK in a table that was already partitioned on one of its columns, the new PK needed to include the partitioned column as part of it.

Since the TABLE_A is partitioned on COL_1, the new Primary Key being added must include COL_1 as part of it.

So the PK ends up being created as so:

-- Add the MY_KEY column
ALTER TABLE dbo.TABLEA
ADD [MY_KEY] UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWSEQUENTIALID() NOT NULL

-- Create the PK constraint
ALTER TABLE dbo.TABLEA
ADD CONSTRAINT PK_TABLEA PRIMARY KEY CLUSTERED (MY_KEY,COL_1)


Where's my text highlighting in SSMS 2014? In visual studio I find it very helpful to find where a variable is being used by highlighting it and all occurrences of that var will appear highlighted as well.

Is there a way to enable this feature in Sql Server Management studio 2014 while viewing scripts?

Text highlighting is mentioned here.

Thanks!

SQL Server Management Studio (SSMS) 2016 has this feature.  There is no real reason to user the older versions now as it's supposed to be backwards compatible, although there may be a bug or two (see below).

Just press Ctrl+ F to see your keywords highlighted like this:

Maybe a few bugs for SSMS 2016 on connect ...


How to auto-refresh/re-run a query every few seconds? I have a script running against a database with no GUI. This simple query lets me see its progress:

select
    *,
    (select count(*) from domains) as count,
    (select 456976-count(*) from domains) as remaining
from domains
order by domain desc
limit 0, 1;

Using MySQL Workbench, how can I automatically refresh/re-run this query every few seconds?

I tried Googling, but didn't come up with anything relevant.

Aside: the magic number 456,976 is 26^4; it has to do with the script.

You don't state what OS you are using, but under Linux/OSX and other Unix-a-likes you can use the mysql command line tools with the pretty standard watch utility to do this. Something like:

watch -n 10 'mysql --database=dbname --user=username --password=$(cat /path/to/file/with/mysql/password) --execute="select *, (select count(*) from domains) as count, (select 456976-count(*) from domains) as remaining from domains order by domain desc limit 0, 1;"'

Breaking that down:

watch -n 10 '<stuff>' runs <stuff> every ten seconds and echos anything that goes to stdout to your console window, the single quotes wrapped around the command(s) to run tell your shell not to run any wildcard globbing or other substitutions before giving the command to watch (we want this to happen when watch runs the command)
mysql --database=dbname --user=username --password=password runs the command line tool against a given DB with the authentication details given
Using command substitution ($(cat /path/to/file/with/mysql/password)) to read the password instead of giving it directly on the command line stops your password being presented to anyone looking at the output of top or ps (this is why we use the single quotes: without them this substitution will be done before watch is in control so the real password will end up on the process list as watch runs the resulting command)
--execute=<stuff> tells the command line tool to run <stuff> then exit immediately (otherwise it logs in and waits for you to enter commands/queries/statements).
The rest is just your SELECT statement. Each time watch runs the mysql command it will output the results of this and watch will draw them on your screen.

More detail:

mysql command line syntax
watch command man page
watch command examples with animated example output
some more examples of simple mysql command-line use (the first of many in a simple search, so you will find a lot more examples if you need/want them)


How do I find PostgreSQL's data directory? I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory.

I'm on MacOsX if that helps.

/usr/local/postgres doesn't exist on my Mac.

Using the answers provided below, I found that it was here:

/usr/local/var/postgres

If you can connect to the database with superuser access, then

SHOW data_directory;

is the shortest way.

If the server is not running and you forgot where the data directory was, then you really have to guess yourself.  An operating system specific convention or the shell history might provide hints.


Recommendation for storage of series of time series Just a few words describing the data:
In my application, there are acceleration measurements (for example at 25kHz) for the exemplary duration of one second. These measurements get repeated in not necessery äquidistant time steps for that measurement point. (Maybe each five or ten minutes). This is a kind of interrupted permanent monitoring, somehow two-periodic:

short time period of measurement is 25.000 Hz (the resolution of the measurements)
long time periodic (not in a strong sense, might differ) every 5 minutes

There are 20 or more of those points.

As dealing with time series, the first idea might be the usage of a time series db. On the other hand, for me it seems as if the main purpose of time series db is storage of scalar values. Of course, my measurements are scalar values. But I'm not sure if it would be a good idea to store every scalar value as a (time/value/measpos_id)-triple - leading to an enormous number of entries. I think single of those entries would never be evaluated.
Another idea could be the storage of the measurement vector (all values from that second) together with the starting time and the measpos_id. But howto do that? Taking all values as a blob? Not every system is capable of dealing with vectors - and maybe they differ in length. Are there concepts in timeseries-db for such problems, which I'm not aware of?
Further for evaluation (extraction) I think maybe the exctraction of the complete vector would be the most used case.
Please feel free to ask, if my description is incomplete or some more details could help in finding a good solution.
What are your recommendations? NoSQL or relational SQL? Further ideas? Every hint is welcome. Thanks in advance.

additions:

A rough idea for the volume is steady growing in size of about 1 TB a year
Giving a sample is not that easy - I'll try to describe:
Think of 1 column with 25000 float values for each measurement (each minute rougly and for each measurement position), timestamped each of these columns (at begin).
Usage for big data evaluation (means testing many kind of algorithms); windowing data, fft (spectral analysis), comparison, aggregation like energetic sum, value of max amplitude, pos (freq) of max amplitude, many more
purpose (focus) of evaluation: wear detection for condition monitoring of for example rolling devices (gears, generator sets, turbines, shafts, bearings)
evaluation would (from todays view) focus on each seperate column and maybe compare to others - but not combine (stack) columns together.
data size example: 25.000 float values in each column for 20 measured engines each 5 minutes (12 per hours) results in 6e6 floats each hour or 5.25e10 floats each year.

I can suggest Akumuli.
It's a time-series database that supports compression and high-throughput data ingestion. With 25KHz measurement frequency and 20 engines, you will need to write 500K data points per second in the worst case. Akumuli can handle an order of magnitude larger throughput (largest throughput ever recorded is around 16M data points per second).

Also, because of compression, the database needs only around 3-9 bytes per data point. Each data point is a timestamp with nanosecond precision + 64-bit floating point value. There is an automatic data retention that deletes old data only if there is not enough disk space to store the new data.

You can store data from each engine in the same time-series or you can create new time-series per burst.

The real time-series database can be a big win because you won't need to use all these fancy tricks. There is a downsides of cause. E.g. there is no clustering and backfill.

Disclaimer: I'm the author so I'm a bit biased.


SQL Server sp_BlitzCache - Many Plans for One Query I'm in need of a bit of assistance.

I ran sp_Blitz on one of my SQL Servers and one of the results it returned is "1531 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.".

I followed the link in the result.

I then ran sp_BlitzCache @ExpertMode = 1 to find queries with multiple plans but the #Plans and #Distinct Plans columns both return the value 1 for all rows.

How do I proceed to isolate the single query that has multiple plans?

Environment:
SQL 2012 SP3 CU8

sp_BlitzCache version 5.7

So close! Problem is you didn't actually read the page you linked to, which says:

  If you’d like to find more queries that sp_BlitzCache didn’t report as performance problems, this query will list all of them in the cache:

Run that query and bask in the warm knowledge that comes with reading - not just clicking on - the documentation. ;-)


Is it possible to have partition in SQL Server 2016 with both clustered and non-clustered indexes on different partitions? Suppose I have an equities pricing table with a 1000 million rows. I need to be able to maintain consistency, read and write to it at high speed with a large number of clients.

What I'd like is a partition that splits the table into two. An 'archive partition' that has a clustered index and has the older (by time) 90% of the data. I'd also like a 'recent partition' that contains the most recent 10% with a non-clustered index.

I'm not sure if this is possible.

My question is: Is it possible to have partition in SQL Server 2016 with both clustered and non-clustered indexes on different partitions?

I don't think it's possible. The notion seems to fly in the face of everything that we know about partitioning and clustered indexes, but I might be wrong.

One possibility is a partitioned view over two tables, one with a clustered index, one as a heap. I have included a demo script for this, but it does come with a number of limitations which might be critical:

inserts seem to need to explicitly insert into each column
the heap table still needs a primary key which includes the partitioning column (even if the primary key is not clustered)
moving data from 'recent' to 'archive' involves moving from one table to another

Overall your situation is probably complex enough that it needs a much more considered analysis.

Use tempdb;

Create Table TestOld (
    id int,
    partition_column char(1) Constraint chk_partition_old Check (partition_column = 'O'),
    other_column char(100),
    Constraint pTestOld Primary Key Clustered (partition_column, id));

Create Table TestNew (
    id int,
    partition_column char(1) Constraint chk_partition_new Check (partition_column = 'N'),
    other_column char(100),
    Constraint pTestNew Primary Key Nonclustered (partition_column, id));
Go

Create View Test As
Select id, partition_column, other_column
  From TestOld
Union All
Select id, partition_column, other_column
  From TestNew;
Go

Insert Into Test (id, partition_column, other_column)
Values  (1, 'O', ''),
        (2, 'N', '');

Select *
  From Test
  Where partition_column = 'N';

A second option which may have more merit in your case is a filtered index which you could add over the old part of your table and include a large number of columns so that it covers the queries that you do on the old part of the table (this would serve to speed up reads to the old part of the table but wouldn't slow writes in the recent part).


MySQL DB as Master for some tables and Slave for others I have 2 DBs - DB1 (back-office) and DB2 (web application). The data needs to be synced between the 2. I'm happy with the idea of full master-master replication and may have to implement it, but really I just want some tables replicated from DB1 (back-office) to DB2 (web application) and other tables replicated from DB2 to DB1. (Some data is too sensitive to be replicated out to DB2 hence I'd use the --replicate-wild-do-table method).
DB1 is updated by internal systems and then DB2 would receive the data to be displayed by a web application, the data considered "read-only". The web application can collect data via web forms and would put it in its local database DB, and I want that data replicated to DB1 back office server.
SO, can MySQL be a master for some tables and a slave for others? If not, could I do it with Postgres?
Many Thanks

Yes you can. MySQL 5.7 and later support filters for replication which allow you to exclude arbitrary tables:
https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html
Caveat: This is a slave-side option, which means that sensitive data will still be in the relay logs on the client side.


is Sql Server AlwaysOn AG a multi-site active-active replication technology? Scenario FCI: I understand multi-site active-active replication in SQL Server can be installed with an FCI (Failover Cluster Instance). The replication part occurs at the storage level with a connected SAN. So it's considered active-active, but only one node has the FCI ownership, but the data is replicated between sites at the storage level.
Scenario AG: But, I'm searching for a widely accepted definition of active-active replication. In Microsoft's documentation, the secondary replica in an AG (Availability Group) is active if the same can be used for read-only queries. So, an AlwaysOn AG could be used to get replication between the two sites, with the primary active read-write and the secondary active-read-only.
So the difference is:

Scenario
Main Site
DR Site

FCI
read-write
read-write on failover

AG
read-write
only-read  without failover

So my question is: I'm seen that FCI with synchronous storage level replication is considered active-active replication.  But, I'm seen too that active-active replication is considered only when you can read-write to both sites. So, my questions are:

is there some widely accepted definition of active-active replication?
Scenario FCI and scenario AF are considered active-active replication?
what's the difference between active-active replication and master-master replication?

the failover cluster instance is not a active-active solution for databases.
Here you can find the microsoft documentation:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15
The secondary node(s) is(are) not accessible (their sql server services are stopped). You can build on it an active active solution installing multiple sql server instances having active instances on different nodes but a single database can be accessible only in one node at time.
In alwayson availablility group you can have readable secondaries (only with enterprise license) to offload some tasks like backups, readonly activities etc...
Here you can find the microsoft documentation:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15
Both solutions are not considered active active sql server installation if you consider a single database.
There are other solution that can be used to reach some kind of active active solution. It is the Sql server replication. Here you can find the documentation:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver15
This last solution is not an instance or database high availability solution. It's more at application level because you have to choose to replicate tables or other specific database objects  to another instance. In this case the peer-to-peer replication is the nearest to an active-active multi master solution. But is also the worst by the point of view of maintenance and performace because your server have to sustain the io load of the whole peer-to-peer network.