Is it good practice to only delete through a trigger to enforce 'deleted by' auditing? We've decided to design our database such that we actually delete rows when we want to delete some data, rather than just marking them as deleted, because the latter approach means that you always have to worry about filtering out the "marked as deleted" rows, which seems like an unnecessary headache.

However, we are auditing deleted rows in separate audit tables so as to keep the deleted data separately.  We have a generic audit trigger that inserts all deleted columns (and their values) to the audit table on delete.  We wish, though, to also audit the user ID of who deleted the row anytime a row is deleted. My idea is to have a deleted_by_user_id column on each table, and a trigger that blocks a delete if this column is not set. The transaction should fail and rollback if the "deleted by" user is not specified.

It would also automatically delete the row when it was set during an INSERT or UPDATE, meaning the row could only be deleted by setting this column.  This would mean that any delete would necessarily include the user ID that deleted it as part of the audit. The account deleting the row will always be the service account for the web app in the database. It's our internal user IDs that we're interested in as to "who" is deleting the row, and that's what we want to capture.

Would this be good practice?  What would be the pros and cons of using this method, and would it impact performance much compared to just using DELETE statements for row deletion?

I am not sure that using an IsDeleted column would create unnecessary headache.   Locking down the table, and allowing non-administrative action via a view which filters on that column, while not displaying it, isn't very difficult.  Add an Instead Of Delete trigger to the view, and you're all set.

Given that it's essentially boiler plate once you have the initial pattern, you could very easily write a procedure locks down the table and (re)generates an associated view, adding the filtering and trigger when it finds it has IsDeleted.  It could add user logging requirements if there was a deleted_by_user_id field as well.  I'd recommend parameter that allow rebuilding the world, or a pattern that is matched against table names (and/or schema).

In addition, versus forcing a deleted_by_user_id to be directly populated, I would look at leveraging SESSION_CONTEXT.  The trigger would check against an agreed upon session context variable (e.g. 'AppUser'), throw if not set, then use that for populating deleted_by_user_id from that.

On the application side, you should be able to create sub-class SqlConnection and override Open.  After calling the base Open, you should be able to run a EXEC sp_set_session_context N'AppUser', '{username}';, there by setting the App user for the session.

The advantage of using IsDeleted is that you don't have to write a whole row of data when doing a delete.  If you do bulk deletes, or a lot of deletes in general, this could be advantageous.  Though I would couple with Partial Index, cover just undeleted records. The advantage removing and writing to another table is that after reorganization you should have much more contiguous data, which in many cases generally makes things faster.


How to avoid/prevent Aborted Connections in mysql We are using MySQL Version 5.1.73. Database is getting huge number of connections around 600 at one time.

I observed Aborted Connections are getting increased from Global Status variables. So I enabled log_warnings=2 and when I checked MySQL log file, I  observed huge number of below warnings:

  [Warning] Aborted connection 21352666 to db: 'DBName' user: 'UserName'
  host: 'xx.xx.xx.xx' (Got an error reading communication packets)

I searched in Google and increased max_allowed_packet to 250 MB.

DB Size is 700 MB.
Innodb_log_file_size is 50 MB.

We also observed error like 'lost connection to MYSQL server at 'reading authorization packet' system error 0' in application log files.

Why are we getting these warnings and errors and what to do to avoid them?

All aborted connections tells you is the number of aborted connections.  It doesn't tell you why they didn't connect.

Do you have some kind of monitoring system or network scanning system in place?  e.g. Nagios, Spiceworks?  They will/can make a connection to the database network socket, then either not authenticate or fail to authenticate because they haven't been configured correctly.

These would cause the value of aborted connections to rise every time they attempt a connection, which could be very frequently.


DB replication and recovery of a previous version in mysql I am trying to get my head around how to have a simple recovery solution for mysql databases in case of accidental deletion.

Let's just say for argument's sake that you are an admin of a 800 gig database and one day you or someone accidentally deletes a table or a hundred rows. Here are two potential solutions I thought of, and the problems I see with them.

1) Database sync is enabled with a replication server. Meaning you can restore the data from your replication slave server by doing a dump of the table and then restoring it on the main server?

Problem: if you delete the table on the master server, this would get replicated on the slave, meaning you lose the table in both, am I right?

2) You enable the binary log and restore somehow using this.

Problem: My understanding of the recoveries with the binary log, is that first you have to restore a full backup from the previous day, and then use the binary log to rebase the changes back to the point previously before you deleted the table or rows. I think this is a huge problem if your DB is 800 gigs, because restoration could take a whole day, and that would be a whole day without the server running - not feasible.

What I want is a simple and reliable solution to restore to a previous version at a specific date and set the servers accordingly so in case something like this were to happen, recovery is simple and doesnt take an entire day or hours. Is it not possible to use the binary log to roll back without doing a full backup restore? Are there any other solutions to this problem?

First, why do u use MySQL for such a huge dtb?
Second, how about MYLVMBACKUP ?? :)

HW conf plays a big role (in regards to performance and d2d operations) -> you should never keep >1TB data size within a single MySQL node/server (it's an overkill)


Is there a way to extract a report of or script all of the primary keys in a database? I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of the schema still has primary keys, and I would like to generate a script that will add those keys to any deployed databases without them.

The best I can hope for is to just do something to script all primary keys, but I will be happy to also just extract the names of the columns that make up these keys and generate my own script.

SELECT *
FROM
 (SELECT obj.name + '.' +col.name as colName, idx.name, idx.is_unique, idx.is_primary_key, idx.is_unique_constraint
  FROM
    sys.indexes idx INNER JOIN
    sys.objects obj ON idx.object_id = obj.object_id INNER JOIN
    sys.index_columns idxcol ON idxcol.index_id = idx.index_id AND idxcol.object_id = idx.object_id INNER JOIN
    sys.columns col ON col.column_id=idxcol.column_id AND col.object_id = idxcol.object_id
  WHERE obj.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE')) a
WHERE a.is_primary_key = 1


What is the differences between to and = in set search_path? I have different schemas in my database like,

public
Hotel_Management
Hospital_Management

My default schema is Public. So I try to change from default schema to another schema.
I know how to change the schema from one to another.
set search_path to Hotel_Management;

But my doubt is, What is the real difference between to and = in set search_path?.
For example:
Set search_path to Hotel_Management;

and
Set search_path = Hotel_Management;

Thanks in Advance...

There is no difference.

If you look at the manual one is the alternative to the other. { TO | = } means either TO or =

The meaning of that is explained at the start of the manual

  and vertical lines | indicate that you must choose one alternative


Granting a user account permission to create databases in PostgreSQL How do I give a user account in PostgreSQL the ability to create and drop databases?  Is there a way to do this with GRANT?

It's done with ALTER USER username CREATEDB;

See ALTER USER in the doc.

To drop a database, either you're superuser (which can be granted with ALTER USER too) or you must own the database.


The table mysql.proc is missing, corrupt, or contains bad data In MySQL I'm getting the following error message when trying to call a stored procedure:

The table mysql.proc is missing, corrupt, or contains bad data

I've tried performing CHECK and REPAIR on the table, but it's not made any difference.

How can I fix the issue and get stored procedures back again?

I had a similar problem a while back, and mysql_upgrade etc refused to fix it.

In the end I simply took the table structure from a 'good' database and compared it to the 'broken' one. Then made the necessary adjustments. In my case I found that one column had changed slightly.


Suggest performance tradeoffs I have a an inner query that needs outer query column values as input. It looks something like this

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)
INSERT INTO  TISSET VALUES (1,1,1);
INSERT INTO  TISSET VALUES (1,2,1);
    SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTOuter.COL2 + TTOuter.COL1 ) > 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM TISSET TTInner)
    FROM TISSET TTOuter
DROP TABLE TISSET;

But this raises the following error.

"Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression."

In order to overcome this, after referring to this Stack overflow discussion, I have to modified the code as following.

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)
INSERT INTO  TISSET VALUES (1,1,1);
INSERT INTO  TISSET VALUES (1,2,1);
    SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTInner.COL2 + TTInner.COL1 ) > 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM (
            SELECT TTInner.Col3, TTOuter.COL2, TTOuter.COL1
            FROM TISSET TTInner
        ) TTInner)
    FROM TISSET TTOuter
DROP TABLE TISSET;

I understand that using functions can be very costly, so dont want to create functions.

Please suggest performance factors to consider if there are any to make this better.
I have not applied this yet on the actual table, as that table would contain upto several million rows.

Here are some suggestions on comparing different queries, since that is what you will most likely end up doing. I have made these types of remarks in the past. These recommendations are somewhat based on those comments from the community.

Create the Table TISSET

CREATE TABLE TISSET(    COL1 int ,COL2 int,COL3 int)

Create Random Data

As @peer mentionned in his comments, you should run your query on actual data. One important step on top of that, is to query against data that closely matches the actual production data you will be using. In the step below, I create random data but that is more evenly distributed. In other words, every N insertions, the values repeat themselves.

-- create random data
DECLARE @ROWNUM as bigint = 1
WHILE(1=1)
BEGIN
    set @rownum  = @ROWNUM + 1
    insert into TISSET values(1 + (CHECKSUM(NEWID())) % (30*60),
    1 + (CHECKSUM(NEWID())) % (24*60), 1 + (CHECKSUM(NEWID())) % (60*60))

    if @ROWNUM > 50000
        BREAK;
END

The amount of data and how its distributed affects the way in which the Query Optimizer chooses which execution plan to use. Having a lot of records is not sufficent, you must also consider how its distributed: number of unique values, one column having more duplicates than another.

After adding so much data, you should also consider page splits and fragmentation.
This query comes from Paul White.

 SELECT
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.index_level,
    DDIPS.fragment_count,
    DDIPS.avg_fragment_size_in_pages,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent,
    DDIPS.record_count,
    DDIPS.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'TISSET', N'U'),
        NULL,
        NULL,
        'DETAILED'
    ) AS DDIPS;

There are no indexes in TISSET, but if you did create a clustered index and/or non-clustered index, you would get back the level of fragmentation. Having heavingly fragmented data can skew results and dramatically slow things down.

Clear out the cache

Here we are cleaning out the cache and running a checkpoint. When comparing queries, it can be helpful to know the elapsed time when a query is in cache and when it is being read from disk into cache.

-- Cold cache
DBCC DROPCLEANBUFFERS
CHECKPOINT
CHECKPOINT

DBCC DROPCLEANBUFFERS cleans all buffers. Do not use this command on a production server as you will seriously slow your server down as it reads data back into memory from the disk.

CHECKPOINT - All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.

STATISTICS IO and TIME

The statistics IO statement will send back interesting information

Logical pages read, was tempdb used.

Here is the BOL page on STATISTICS IO

The statistics TIME statement will send back Execution times, both CPU and User.

Here is the BOL page on STATISTICS TIME

Output from your query

STATISTICS IO

Table 'TISSET'. Scan count 49898, logical reads 6834930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I tend to look at logical reads (Number of pages read from the data cache.) and Worktable (tempdb may have been used). A closer examination of the execution plan would be necessary. Looking at yours, the use of the Sort operator indicates that tempdb was probably used, if not enough memory was available to perform the sort.

STATISTICS TIME

SQL Server Execution Times:
   CPU time = 819333 ms,  elapsed time = 113509 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

-- compare queries
set statistics io on
set statistics time on

-- first query

   SELECT TTOuter.Col1,(
        SELECT CASE WHEN SUM(TTInner.Col3 - TTInner.COL2 + TTInner.COL1 ) > 0 THEN 'BIG' ELSE 'SMALL' END D
        FROM (
            SELECT TTInner.Col3, TTOuter.COL2, TTOuter.COL1
            FROM TISSET TTInner
        ) TTInner)
    FROM TISSET TTOuter

-- second query goes here

set statistics io on
set statistics time on

Then of course there is the Execution plan which you can review by simultaneously pressing CTRL+M or under the Query Menu in SSMS, choose Display Acutal Execution Plan

If you were analyzing multiple queries, each one would get a batch cost. This is not an exact metric, but I find it useful when the cost difference between two queries is significant.

Although this information won't answer every question, it can be used as a starting point for comparing queries and deciding which one is best.


Timestamp columns in Peer to Peer Replication According to MSDN post https://msdn.microsoft.com/en-us/library/ms151196(v=sql.105).aspx Timestamp columnns are restricted in Peer to Peer replication.
Does this mean that we can't use Timestamp columns in database that we are going to replicate ?

Tested it myself.
TimeStamp columns are not allowed. You can't replicate a table if it contains a TimeStamp column.

I got the following message when I tried:

  The table cannot be published because it contains a timestamp column. Timestamp columns are not supported in Peer-to-Peer publication.


WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT? The scenario

Two systems(not server) running PHP and PostgreSQL with the following versions

Fedora 15:

  PHP

  PHP 5.3.13 (cli) (built: May  9 2012 14:38:35)
  Copyright (c) 1997-2012 The PHP Group
  Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
  pdo_pgsql
  PostgreSQL(libpq) Version     9.0.7
  Module version    1.0.2

  PostgreSQL

  PostgreSQL 9.1.4
  With CITEXT extension enabled.

ArchLinux:

  PHP

  PHP 5.4.6 (cli) (built: Aug 16 2012 12:50:09)
  Copyright (c) 1997-2012 The PHP Group
  Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
  pdo_pgsql
  PostgreSQL(libpq) Version     9.1.4
  Module version    1.0.2

  PostgreSQL

  PostgreSQL 9.1.4
  With CITEXT extension enabled.

when a simple query such as

select column1 from schema1.table1 where column1= ?

Where column1 is of the type CITEXT, is executed through PHP PDO

On Fedora with PHP 5.3.13,libpq 9.0.7, the query performs as expected with CITEXT(a case insensitive search occurs).
On ArchLinux with PHP 5.4.6,libpq 9.1.4, the query does not perform as expected with CITEXT(a case sensitive search occurs).

I am guessing the newer version of PHP PDO libs are doing something similar to this:

select column1 from schema1.table1 where column1= 'value'::text;

during binding.

Am I right?
Is there a workaround? Otherwise, the use of CITEXT as a column data type to gain the advantage of case insensitive search is useless when using newer versions of PDO.

Update

After turning statement level logging on, on ArchLinux with PHP 5.4.6,libpq 9.1.4 :

LOG:  execute pdo_stmt_00000001: select column1 from schema1.table1 where column1 = $1
DETAIL:  parameters: $1 = 'value'
LOG:  statement: DEALLOCATE pdo_stmt_00000001

where the actual value of the column column1 is VALUE.

Still comes back with 0 elements.

When the statement

select column1 from schema1.table1 where column1 = 'value';

is executed directly on the PSQL prompt comes back with a single row.

 column1
---------
  VALUE
 (1 row)

So, the type casting does not take place! I still am not able to understand the behaviour of PDO/postgresql.

Update 2012-08-27 16:15:43.669142+00 (UMT + 0)

After trying to directly execute a query without preparing a statement.

Here is the code that was used to test :

try {
    $db = new PDO('pgsql:dbname=database1;user=user;password=pass;host=localhost');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT column1 from schema1.column1 where column1 = 'value'::citext ";
    $retval=$db->query($sql);
    foreach ($retval as $row) {
        print $row['uname'] . '
';
    }
}catch (PDOException $PDOerr) {
    echo 'An error occured :
';
    var_dump($PDOerr);
    exit;
    //some thing went wrong while performing the action on db.
    }

I get the error :

object(PDOException)#10 (8) { ["message":protected]=> string(211) "SQLSTATE[42704]: \
Undefined object: 7 ERROR: type "citext" does not exist LINE 1: ...

I do not understand why citext is not getting detected!
When the statement is executed directly on the PSQL prompt, everything works fine as mentioned above.

Locate the namespace inside which the citext type resides:

select nspname from pg_type t join pg_namespace n
  on n.oid=t.typnamespace where typname='citext';

Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to citext:

$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::public.citext";

If that solves the problem but in a way you find inelegant, you might reconsider how you're using schemas and search_path: make sure that all your custom types are accessible no matter what.


PGSQL: Which is faster, many constraints or a single trigger? I have a table that currently has 12 check constraints. I'm making some updates and I'm wondering if this is the best approach. I see three options:

Keep using the 12 constraints. They're a bit difficult to understand, but definitely possible.
Craft one massive constraint that no feeble human mind could ever understand or maintain.
Use an insert or update trigger to validate contents. This is a dream to implement and maintain.

I'm aware that a trigger will not validate existing rows. For this use case, that is acceptable.

Obviously, I like #3, but I'd some like input regarding performance of inserts and updates before actually implementing anything.

A short answer would be: generally check constraints are faster than triggers.

However, if you think a trigger will be easier to maintain than multiple check constraints, do not hesitate and try this solution. Performance deterioration will not be high. I used this solution a few times, in all cases the triggers were up to 10-15% slower than check constraints. In my opinion this is not an exorbitant price for the extra capabilities and flexibility that triggers give.

An additional important advantage of triggers is that you may reject incorrect rows instead of raising an exception, which can be particularly comfortable in bulk inserts or updates. Information on rejected rows should be stored in some way, e.g. in a form of a log, in an auxiliary (temporary) table or using notifies.


Data Warehouse: Can a Transaction Table also be a Dimension? We have a transaction table. Not only does sales team want to analyze by CustomerId or Productid sold, but also by sales amount sold or quantities.
Kimball states, a fact table here can also be a dimension. In that case, could I leave by transaction table as a large dimension table? Just want to understand Kimballs article below.

https://www.kimballgroup.com/2007/12/design-tip-97-modeling-data-as-both-a-fact-and-dimension-attribute/

It seems that amount sold and quantities sold directly relate to a transaction and I would think those items should be in a transaction fact table and not on a dimension.

If quantities are especially interesting (e.g., enough to have a hierarchy), one might create a dimension for it, in addition to the Quantity field in the fact table. Such an OrderSize dimension could have fields like QuantityBand with values like "1 to 10" and "11 to 50", and QuantityDiscount.

  Can a Transaction Table also be a Dimension?

The answer is yes, it can. Should it? Probably no, but dependent columns are not unheard of. Just know that you are wasting tons of space with repeating columns. Also, the term is Dimensions vs Fact tables. Dimension tables describe the data while Fact tables describe what they are doing.

answer based on comments left by Scott Hodgin, clifton_h, and Jon of All Trades


Get output in comma separated format in sql server When I retrieve the data I get result in congested format.

TableName       TableRows       DataSpaceMB     IndexSizeMB
Sec             2               0.02            0.02
Key             2               0.02            0.01
name            3               0.01            0.02
agenda          1               0.01            0.05
Configuration   19              0.01            0.02
Policies        2               0.01            0.01

How to get result in separated values for different columns in a single row?

E.g
    TableName,TableRows,DataSpaceMB,IndexSizeMB
    Sec,2,0.02,0.02
    Key,2,0.02,0.01
    name,3,0.01,0.02
    agenda,1,0.01,0.05
    Configuration,19,0.01,0.02
    Policies,2,0.01,0.01

You can use + or the CONCAT function to combine multiple columns into a single string. If you need the column names at the top you can do that with UNION ALL.

Here is your sample data:

CREATE TABLE #X_164768 (
TableName SYSNAME NOT NULL,
TableRows BIGINT NOT NULL,
DataSpaceMB NUMERIC(10,2) NOT NULL,
IndexSizeMB NUMERIC(10,2) NOT NULL
);

INSERT INTO #X_164768
VALUES
('Sec', 2, 0.02, 0.02),
('Key',2, 0.02, 0.01),
('name', 3, 0.01, 0.02),
('agenda', 1, 0.01, 0.05),
('Configuration', 19, 0.01, 0.02),
('Policies', 2, 0.01, 0.01);

Here is one way to get the output you want:

SELECT 'TableName,TableRows,DataSpaceMB,IndexSizeMB'

UNION ALL

SELECT
Tablename
+ ',' + CAST(TableRows AS VARCHAR(20))
+ ',' + CAST(DataSpaceMB AS VARCHAR(20))
+ ',' + CAST(IndexSizeMB AS VARCHAR(20))
FROM #X_164768;


mysql workbench failed to connect at localhost:3306 with user root ssl connection error: ssl is required but the server doesn't support it Not sure what the answers are, but I will say that I have not found the answers on how to make this work again.

The keyword support makes me think that a payment is required for this feature, but I'm not certain.

Configure MySQL Workbench to not require SSL encryption

If you get the MySQL Workbench error of "SSL connection error: SSL is required but the server doesn't support it" then you likely just need to change a setting that's defined within the MySQL Connections within MySQL Workbench.

From the MySQL Connections screen click on the little wrench icon  to the right to bring up Manage Server Connections window.

From the Manage Server Connections window click on the "MySQL Connection" that is giving you this error, then from the SSL tab you will want to ensure the Use SSL field has a value of If available so it only uses SSL if it's available or else it'll connect without SSL encryption.

Press the Test Connection option from the bottom of the Manage Server Connections window after you make the to confirm the error goes away.

Continued Trouble

If you continue to have trouble you might set the Use SSL field to a value of No and also ensure all the SSL fields below that are blank and then test the connection again.


How to add a constraint to a table using a partial index? I have a simple table:

create table item (
  lan      text not null,
  disabled date
);

My aim is to ensure uniqueness for the lan column over all rows, where disabled is null.

I created a partial index:

create unique index lan_idx on item (lan) where disabled is not null;

Now I tried to add the index to the table, but I am struggling with the syntax. (I took the code from this tutorial)

alter table item add constraint lan_idx using index lan_idx;

This throws an syntax error before "using".

I also tried the following:

alter table item add constraint lan_idx unique using index lan_idx;

But this throws the error, that a unique constraint can not be created with a partial index.

Can anybody tell me what I did wrong?

You can't define a unique (or primary key) constraint based on a partial index.

However, to enforce uniqueness of the lan column for all non-deleted rows, the partial unique index you already have is enough.

There is no need to add a constraint.


What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?

Those two columns are the result of

SELECT pg_stat_get_live_tuples(c.oid) AS n_live_tup
     , pg_stat_get_dead_tuples(c.oid) AS n_dead_tup
FROM   pg_class c;

Representing the number of live and dead rows (tuples) in the table.
Find those functions in the manual.

Dead rows are deleted rows that will later be reused for new rows from INSERTs or UPDATEs (the space, not the data). Some dead rows (or reserved free space) can be particularly useful for HOT updates (Heap-Only Tuples) that can reuse space in the same data page efficiently. More on H.O.T.:

PostgreSQL Initial Database Size
Release notes for PostgreSQL 8.3 where this feature was introduced.

Or dead rows may be removed by VACUUM FULL (or plain VACUUM if it gets lucky) or similar operations on the table, thereby shrinking the physical table accordingly.

Whenever a row is deleted or updated, the old row version becomes invisible to all other transactions starting after the transaction has been committed. The row is completely dead as soon as there are no more uncommitted older transactions. That is necessary for PostgreSQL's MVCC model to handle concurrency.

Those are just statistics. You need to enable statistics collection in postgresql.conf if you want them to be updated automatically. track_counts should be on by default, though. Bear in mind that statistics are not updated instantaneously. Read more about that in the manual.


MySQL User with Host set to a Range of IP Addresses I have a hosted MySQL DB that I need to give access to from my customers site. They have a fast broadband connection which can present them with a range of IP addresses from a fixed pool.

So I need to lock down the access to their MySQL user so that the Host setting is a range of addresses within a class. Not the whole class.

I realise I can use a wildcard such as 81.91.71.% but that is too generous. Their range is more like

81.10.20.1 --> 81.10.20.15

Is there a way to do this other than create 15 users, one for each individual IP address?
thanks
Mark

you can use the grant assign to a netmask like:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'81.10.20.1/255.255.255.240';

It will give you access from 81.10.20.1 -> 81.10.20.14, just change the subnet part to fit the range you need.

You can see more info at mysql doc account names


MySQL passwords SHA512 using AES_ENCRYPT instead of ENCRYPT? A tutorial is asking me to encrypt passwords using SHA512 in the following manner:

INSERT INTO `vir_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('pw1', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
('2', '1', ENCRYPT('pw2', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');

How would I replace this with a MySQL 5.7 non-deprecated AES_ENCRYPT instead of the old ENCRYPT way of doing things?

MySQL's documentation is severely lacking in this case at it does NOT provide any clear examples.

I found a way of doing it outside the DBMS, however I would like to keep it all contained and not dependant on external code and/or tools; that's why would like to know.

When I run this query now, I get this error message:

'ENCRYPT' is deprecated and will be removed in a future release. Please use AES_ENCRYPT instead

update:
The reason it's using this is because I need to store the password in such a way that Dovecot's default_pass_scheme (which is set to SHA512-CRYPT) can validate my passwords.

The $6$ part of the passwords seems to be important.. I don't know why, but all passwords that are generated the old way start with that. Please check the tutorial on why this is.

----- 2014-09-25 5.7.5 Milestone 15 -- Security Notes -- -----
Incompatible Change: MySQL 5.6 deprecated passwords that used the older pre-4.1 password hashing format. Support for these passwords is now removed, which involves the following changes. Applications that use any feature no longer supported must be modified.

Playing around with passwords seems like a dumb thing to have in a tutorial.  Please skip that section and move on.


MS SQL : Use Computed value to Compute other values I'm having problem with generating results which are computed from data that exists in database. This is the case

I have Coupons that can be amount or percent, if percent is 0 then amount is used

CASE
  WHEN c.CouponPercent > 0 THEN p.Price - (c.CouponPercent/100)
  ELSE c.CouponValue
END as Coupon

now I have to calculate percent for one content provider that is

(p.Price-Coupon)*(ProviderPercent/100)

How can I use calculate coupon value from above to calculate Provider percent, if I use it as in code above error is reported "Invalid column name 'Coupon'".

Please help me, i Know how to resolve this on difficult way which is calculate Coupon value each time when calculating ProviderPercent but I have 8 percents like this in one report for different providers.

So basically question is how to use computed Coupon value to use it in another calculations for same record. Coupon is changed regarding product, so i can be different for every record selected from database.

It's simple, computed value can be used by applying is value with cross apply

like this
select cpk.coupon, p.Price, p.Price-cpk.coupon as FinalPrice -- etc

from Product -- etc
CROSS APPLY (
   select coupon = CASE
  WHEN c.CouponPercent = 0 THEN p.Price - (c.CouponPercent/100)
  ELSE c.CouponValue
END as Coupon ) as cpk

where --etc


Alternatives to DBCC PAGE for working with page numbers in SQL Server Are there any management views or functions or procedures that can resolve page numbers to object id's?  Everything I'm finding says to use DBCC PAGE, but I don't have the requisite permissions.

Yes, you can use sys.dm_db_database_page_allocations if you're on SQL Server 2012 or greater.

For example, this will give you information about tempdb -- the number two passed in is database id.

SELECT *
    FROM sys.dm_db_database_page_allocations(2, NULL, NULL, NULL, 'DETAILED') AS dddpa;


Moving data from PostgreSQL 32bit to 64bit I use Synology's DS1515+ as a PostgreSQL (PG) DB server. Last week Synology upgraded their DSM (Linus based OS) to version 6.0. Doing so, they've upgraded their PG to 64bit and potentially to a newer version (9.3.6). I thus can no long start my instance of PG to access my data.

All this to say:

-I did not get a heads up that this upgrade was coming and therefore did not do a back-up to restore from.

-I can not install the 32bit version of PG to run a dump.

-I know and have access to my DB files

-I am limitted on the tools I can install and use

Is there a way for me to get to my data?

OK, first, you shouldn't need a heads-up that an upgrade is coming because you should have backups anyway. All the time. A disk failure, power surge or fire doesn't come with a few days advance warning either. (Ironically, you posted on world backup day).

32-bit PostgreSQL data files are not compatible with 64-bit binaries or vice versa. So you must, somehow, get the 32-bit binaries if you wish to read your data and dump it.

If you cannot do that on the NAS its self, you will need to:

Copy the data directory to another location ( you should do this anyway, for safekeeping )
Make another copy somewhere safe and read-only. Don't touch it again.
Install the same major version of PostgreSQL for the same operating system and architecture on a test system or VM. For example, if the NAS  runs on an x86_64 CPU but was running 32-bit i386 PostgreSQL 9.3.1 binaries, you could install Ubuntu 32-bit x86 and PostgreSQL 9.3.10 and expect it to read your data. Probably. There are some possible differences if the PostgreSQL on the NAS was compiled with special non-standard settings, but hopefully it wasn't.
Start 32-bit PostgreSQL to use your copied directory
Use pg_dumpall --globals-only to dump users, etc. Then for each database use pg_dump -Fc to dump the database.
Restore the dumps to your new database. Or, preferably, set up a database server somewhere safe that you have reasonable control over.

Now go yell at the people who made this incredibly stupid change without telling anyone.

If you find that you can't load the data files on a regular Ubuntu 32-bit VM with the same PostgreSQL major version (x.y, e.g. if you had 9.4.1 you need 9.4.x and can't use 9.3.x or 9.5.x; yes, PostgreSQL's version numbering is stupid) then you might need to find an old version of the NAS's firmware and extract the PostgreSQL binaries from it, then get them to run on a VM. Or compile your own PostgreSQL with the same non-standard options the NAS people used. Thankfully this isn't likely, as few people change the block size, wal segment size, etc... and I really wish they weren't ./configure options at all.


How to join different tables with same propriety tables? Which would be the best solution to join 4 different tables with X tables that has to be the same for the 4 tables?

Let's presume we have 2 tables MENU and PLATES each of that tables could be joined with tables like IMAGES, DESCRIPTIONS, ALLERGENS and other tables that will give some properties to MENU and PLATES

Which would be the best practise to join those property tables with the main ones?

Should i create in each property table a column for each main table as foreign key? Like this:

+----+--------------+--------+----------+
| ID | DESC         | ID_MEN | ID_PLATE |
+----+--------------+--------+----------+
| 1  | PIZZE        | 1      |          |
+----+--------------+--------+----------+
| 2  | CHICKEN SOUP |        | 4        |
+----+--------------+--------+----------+

Or should i create a intersect table for each main table that has FK for each propriety table like this:

+--------+---------+-----------+
| ID_MEN | ID_DESC | ID_IMAGES |
+--------+---------+-----------+
| 1      | 2       | 3         |
+--------+---------+-----------+

I think that a combination of the two solutions you provided is what you are looking for.

I think specifically what you are wanting to do is create a separate table for each of your attributes (IMAGE, DESCRIPTION, etc...).  Then you will create a bridge table between your objects and their attributes.  This allows you to have tables for each of your attributes.  That way you have those items normalized.  The bridge table then allows you to maintain the 1-to-many relationship between each of your objects and its attributes.  The downside with this plan is you will end up having a lot of tables as your attributes increase or your object types increase.  But the separation of data elements will allow you to keep the data integrity much better.

There is another solution utilizing a generic OBJECT-ATTRIBUTE table that allows you to maintain everything now and in the future in one place with a little bit of effort.  There is a great article provided by Red Gate which recommends against this method.  I would check out the "One table to hold all domain values" section.

Here is what I think you are looking for.  Assuming:

PLATE is a child table of MENU
ITEM is a child table of PLATE
The only 3 attributes you currently have are DESCRIPTION, ALLERGEN and IMAGE

Let me know if there are any questions or if this solution does not take care of everything you are looking for.


Oracle Upgrade - Attach External Tables We have just completed an 11g to 12c migration to a new server. There is a schema that didn't seem to come over that consists fully of external .dat files.

I can't seem to find a way to simple 'attach' these pre-existing (and moved from the old server) .dat files. Is the method to get this data back in to re-create the schema, table by table, pointing to the existing .dat files?

If so, is there a simple way to generate all the DDL statements for an entire schema of tables?

Thanks,
Wes

From researching further, and a bit from the comment on my question, I found that I needed to generate the DDL statements from the previous database. Once doing so, and setting the correct directory, everything worked perfectly.


How can wait times be higher than clock time? When I'm tracking waits with sp_BlitzFirst, I get this detail:

<?ClickToSeeDetails --
For 20 seconds over the last 5 seconds, SQL Server was waiting on this
particular bottleneck.

 -- ?>

Should that read "for 20 times over the last 5 seconds?"  Finding was CLR_SEMAPHORE.

No - wait stats can (and more often than not, will) total more than the physical time spent for the server itself.

Imagine a situation where 2 separate threads spend the same second waiting for some resource - you would have 2 seconds of wait time for 1 second of clock time.

Each thread has it's own waits - the message is telling you that 20 seconds of wait time for that particular resource occurred over the last 5 seconds of clock time.

Or to put it another way, each core can be running multiple queries at the same time, and multiple cores can add up to even more waits. Which is to say, the unit is really thread·seconds, not seconds.


Find the IDs and names of the employees whose salaries are at least 10% higher than the average salary of all employees I need to find the IDs and names of the employees whose salaries are at least 10% higher than the average salary of all employees. I can't get the code to work with the 10% higher than the average. I know how to get the average, but when I run my particular code, I get all names and know that it's not running correctly.

Here is my query:

Select employees.eid,
       CONCAT(efirstName,
       ' ', eLastName) AS Name
from employees
GROUP BY employees.salary
HAVING employees.salary > avg(salary * .10);

I have also tried:

Select employees.eid,
       CONCAT(eFirstName,
       ' ',
       eLastName AS Name
FROM employees
GROUP by eLastName
HAVING avg(salary) > avg(salary * .10);

Using a simple subquery should help with this:

SELECT e.eid, CONCAT(efirstname, ' ', e.elastname) name
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees) * 1.10


Database design import massive csv files is there a more efficient way? I need to let users import huge csv files varied between 200k - 1.5m lines. For this I use LOAD DATA INFILE which is the fastest way to import those files. Thing is however that I can't check the user input, I have solved this by using a trigger on a table. My table structure is as following: (narrowed down to make it more readable):

tbl_debtor: debtor_id (AI), debtor

tbl_debtor_tmp: debtor

tbl_debtor_err: error

Now the (before insert) trigger is on tbl_debtor_tmp where users can literally import anything. In that trigger I check their user input, if okay I'll do a insert on tbl_debtor. If the user input is not okay I write to tbl_debtor_err. After the import is done I'll do a DELETE FROM tbl_debtor_tmp

My question here is, is there a more efficient way? I have about 20 of those table structures which I think is alot.

LOAD DATA ... INTO staging ...; It has VARCHARs in place of INTs, etc.  It also has some unique id (perhaps auto_increment) for step 3.
Copy "bad" rows to debtor_err in one pass:

INSERT INTO debtor_err
     SELECT ...
         WHERE LENGTH(..) > ..
            OR NOT EXISTS( SELECT * ... )  -- FK check
            OR ... > 12
Copy "good" rows to debtor table -- Use JOIN to see which ones are good

INSERT INTO debtor
    SELECT ...   -- where nec, convert from VARCHAR
        FROM staging s
        LEFT JOIN debtor_err e
            ON s.id = e.id  -- suitable UNIQUE key
        WHERE e.id IS NULL  -- to get the non-error rows
DELETE FROM staging; -- Reset for next time.  (Using DELETE instead of TRUNCATE may avoid id not working 'right' the second time around.)

Suggestion:  Use MyISAM for staging; InnoDB for permanent tables.


Upgrade SQL Server 2014 AG to SQL Server 2017 AG Is it possible to migrate SQL Server 2014 AG (Windows 2012, SQL Server 2014) to SQL Server 2017 AG (Windows 2016, SQL Server 2017) using rolling upgrade method?

What I mean is

Build 2 SQL Server 2017 instance.
Add the above nodes to the existing cluster.
Add the new nodes to the existing AG as an additional replica (Async)
Failover to 2017 server
Remove the 2014 nodes from Ag
Remove the 2014 nodes from the cluster.

Would this work or do we have to build everything from scratch?.

As per the msdn link here, the answer is very much or possibly yes. I have not tried this out so I cannot assure that it's 100 percent will work out. It suggest that if you have a test environment go ahead and setup scenario like this and give it a shot. I is Just like how mirroring works, it's expected to be same or similar to logshipping. But make sure that the secondary is not readable as the secondary replica will be in higher SQL Server version .


Can pg_hba.conf trust processes? Is it possible to configure PostgreSQL such that it can trust a single process, instead of a specific user?

That is not possible I am afraid of.  The selection is based on the following criteria:

Type of connection (local or network)
Username requested
Database requested

Nowhere in there is a process given.  If you want to trust a given process the way to do that is to have a service account, a specific database user, and map these in, using peer authentication on *nix (peer on local connections used to be called ident but this was changed since ident is very different on network connections and has far more issues).


how to dump a single stored procedure from a database I need to create a dump file that contains a single stored procedure from a database, not all routines.

I've tried something like this:

mysqldump -u root -pssmart --skip-triggers --no-create-info --no-data \
    --no-create-db --skip-opt test_fshop SP_ITEM_SALES_REPORT -R \
    > test_fshop_routines.sql

Here test_fshop is my DB name and SP_ITEM_SALES is my SP. I want my SP as .sql file.

BAD NEWS

You cannot use mysqldump to do this.

GOOD NEWS

You can use the mysql client. First, find out if the Stored Procedure exists. If it does, then fetch the type of Procedure it is (PROCEDURE or FUNCTION). Then, issue the SHOW CREATE for the Stored Procedure. You will have to strip the top 3 and bottom 3 lines:

DBNAME=${1}
SPNAME=${2}
SPFILE=${DBNAME}_${SPNAME}.sql
SPTEMP=${DBNAME}_${SPNAME}.tmp
MYSQL_CONN="-u... -p..."
SQLSTMT="SELECT COUNT(1) FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
PROC_EXISTS=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
if [ ${PROC_EXISTS} -eq 1 ]
then
    SQLSTMT="SELECT type FROM mysql.proc WHERE db='${DBNAME}' AND name='${SPNAME}'"
    PROC_TYPE=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
    SQLSTMT="SHOW CREATE ${PROC_TYPE} ${DBNAME}.${SPNAME}\G"
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}

    #
    # Remove Top 3 Lines
    #
    LINECOUNT=`wc -l < ${SPFILE}`
    (( LINECOUNT -= 3 ))
    tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}

    #
    # Remove Bottom 3 Lines
    #
    LINECOUNT=`wc -l < ${SPTEMP}`
    (( LINECOUNT -= 3 ))
    head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
else
    echo "Stored Procedure ${DBNAME}.${SPNAME} Does Not Exist"
fi
rm -f ${SPTEMP}

Here is a sample run:

[root@***]# ./GetMyProc.sh common DMSPushers
[root@***]# cat common_DMSPushers.sql
CREATE DEFINER=`pma`@`10.%` PROCEDURE `DMSPushers`()
BEGIN

  DECLARE cntr INT DEFAULT 0;
  DECLARE dealerName VARCHAR(50);
  DECLARE dealerID INT;

  DECLARE done boolean DEFAULT false;
  DECLARE dealers CURSOR FOR SELECT bdd.dealer_id, ta.name FROM `b_dealer__dmsaccount` bdd left join t_dmsaccount ta on bdd.dmsaccount_id = ta.id where ta.dms_type = 1 order by bdd.dealer_id desc;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := true;

  OPEN dealers;

  read_loop: LOOP
        FETCH dealers INTO dealerID, dealerName;
        SET cntr = cntr + 1;

    IF done OR cntr > 200 THEN
      LEAVE read_loop;
    END IF;

        IF dealerID > 0 AND dealerID < 664 THEN
                IF dealerName IS NULL THEN
                        SET dealerName = '';
                END IF;
                SET dealerName = REPLACE(dealerName,'''','''''');

                -- Does the Database even exist???
                SET @query = CONCAT('SELECT COUNT(SCHEMA_NAME) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ''D',dealerID,''' INTO @schemaExists');
                PREPARE stmt1 FROM @query;
                EXECUTE stmt1;

                IF @schemaExists > 0 THEN
                        SET @query = CONCAT('INSERT dealers_that_push (dealerID, dealerName, pushDate) SELECT ', dealerID, ',''', dealerName ,''',date FROM D',dealerID,'.contactLog where message like ''%pushed to the DMS%''');
                        PREPARE stmt1 FROM @query;
                        EXECUTE stmt1;
                END IF;

        END IF;

  END LOOP;

  CLOSE dealers;
END
[root@***]#

GREAT NEWS

Here is a script that will dump every Stored Procedure into separate SQL files:

MYSQL_CONN="-u... -p..."
SQLSTMT="SELECT COUNT(1) FROM mysql.proc"
PROCCOUNT=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
if [ ${PROCCOUNT} -eq 0 ] ; then exit ; fi
SPLIST=""
for DBSP in `mysql ${MYSQL_CONN} -ANe"SELECT CONCAT(type,'@',db,'.',name) FROM mysql.proc"`
do
    SPLIST="${SPLIST} ${DBSP}"
done
for TYPEDBSP in `echo "${SPLIST}"`
do
    DB=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $2}'`
    SP=`echo "${TYPEDBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $3}'`
    SQLSTMT=`echo "SHOW CREATE ${TYPEDBSP}\G" | sed 's/@/ /'`
    SPFILE=${DB}_${SP}.sql
    SPTEMP=${DB}_${SP}.tmp
    echo Echoing ${SQLSTMT} into ${SPFILE}
    mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}
    #
    # Remove Top 3 Lines
    #
    LINECOUNT=`wc -l < ${SPFILE}`
    (( LINECOUNT -= 3 ))
    tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}
    #
    # Remove Bottom 3 Lines
    #
    LINECOUNT=`wc -l < ${SPTEMP}`
    (( LINECOUNT -= 3 ))
    head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
    rm -f ${SPTEMP}
done
ls -l

Give it a Try !!!


changing TEMPORARY TABLE default collation when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?

The CREATE TABLE syntax allows for specifying the character set and collation

The MySQL Documentation for CREATE TABLE shows this syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name <<------ (YOU NEED THIS OPTION)
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name <<------ (YOU NEED THIS OPTION)
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

Therefore, your syntax should be something like this:

CREATE TEMPORARY TABLE tblname
(
) ENGINE=MyISAM CHARACTER SET '...' COLLATE '...';

Give it a Try !!!

If you want to manipulate the default options for collation, you have the following:

collation_connection
collation_database
collation_server

These variables can be changed at the Global or Session Level.

If you want to set the collation permamnently, you could put it in /etc/my.cnf

[mysqld]
collation_connection = 'collation_connection'

and restart mysql

If you only want this for an individual BB Connection, you would run this in the session

SET collation_connection = 'collation_connection ';

See my recent post Why are there different levels of MySQL collation/charsets? to see installed character sets and collations.


How to grant SYSDBA access to users on Oracle Cloud v19c It seems that you can't grant SYSDBA access to users on oracle cloud. I am using v19c, when trying to alter roles through the web "Database Actions" interface, the only roles I see are; CDB_DBA DBA LBAC_DBA PDB_DBA. How can I make a user SYSDBA?

In short, you can't. You need to use PDBADMIN user or PDB_DBA role for such things: https://docs.oracle.com/en-us/iaas/exadata/doc/eccmanagingdatabases.html
Also there is a very useful document:
Known Issues for Oracle Database Exadata Express Cloud Service
It gives some more internal info about cloud access and privileges.


Verify if user has read access to a table I'm developing an ETL, whose extract step must read data from a table.

There are times that my user's access to that table is revoked. So, to avoid the ETL failing, before reading the table I must verify if I have permission to do it. If not, I'll just jump the extract step and run the rest of the ETL.

What's the proper way of verifying if my user has read permission over a table?

To check for a specific effective permission on a securable, use HAS_PERMS_BY_NAME*.

For example, to show if the current security context has SELECT permission on the Production.Product table in the AdventureWorks sample database:

SELECT
    HAS_PERMS_BY_NAME
    (
        N'Production.Product',
        N'OBJECT',
        N'SELECT'
    );

Related: To list effective permissions on a securable, use sys.fn_my_permissions:

SELECT
    P.[entity_name],
    P.subentity_name,
    P.[permission_name]
FROM sys.fn_my_permissions
    (
        N'Production.Product',
        N'OBJECT'
    ) AS P
ORDER BY
    P.subentity_name,
    P.[permission_name];

* The built-in function takes everything into account. SQL Server has to know how to correctly determine effective permissions. This function exposes that ability.


Calculating the optimal size for the oracle undo tablepsace To size the undo tablespace I query v$UNDOSTAT

SELECT BEGIN_TIME, END_TIME, UNDOBLKS, MAXQUERYLEN from v$UNDOSTAT;

BEGIN_TIME          END_TIME              UNDOBLKS MAXQUERYLEN
------------------- ------------------- ---------- -----------
14.10.2016 10:38:57 14.10.2016 10:48:13        911         244
14.10.2016 10:28:57 14.10.2016 10:38:57        977        1120
14.10.2016 10:18:57 14.10.2016 10:28:57       1031        2266
14.10.2016 10:08:57 14.10.2016 10:18:57       1018        2365 <--

To find out the blocksize of the undo tablespace, I query

SELECT TO_NUMBER(value) DB_BLOCK_SIZE FROM v$parameter WHERE name = 'db_block_size';

DB_BLOCK_SIZE
-------------
         8192

Now I can calculate the undo tablespace size. I have to divide the UNDOBLKS by 600 to get the rate of undo generation in blocks per second, and multiply by the blocksize to get the figure in bytes. Multiply by the largest figure for MAXQUERYLEN to find the space needed if the highest rate of undo generates coincided with the longest query and then divide by a billion to get the answer in gigabytes

1018/600 * 8192*2365=328713557,33
328713557,33/1000000000=0.3287GB

Is this a correct calculation? Should I consider other metrics to calcualte the size of the undo tablepsace?

You may use the calculation mentioned in this site:

https://taliphakanozturken.wordpress.com/tag/actual-undo-size/


Self-join on primary key Consider this query which consists of N self-joins:

select
    t1.*
from [Table] as t1
join [Table] as t2 on
    t1.Id = t2.Id
-- ...
join [Table] as tN on
    t1.Id = tN.Id

It produces an execution plan with N clustered index scans and N-1 merge joins.

Honestly, I don't see any reasons to not optimize away all joins and do just one clustered index scan, i.e. optimize original query to this:

select
    t1.*
from [Table] as t1

Questions

Why joins aren't optimized away?
Is it mathematically incorrect to say that every join doesn't change result set?

Tested on:

Source Server Version : SQL Server 2014 (12.0.4213)
Source Database Engine Edition : Microsoft SQL Server Standard Edition
Source Database Engine Type : Standalone SQL Server
Compatibility level : SQL Server 2008 (100)

The query isn't meaningful; it just came to my mind and I'm curious about it now.

Here's the fiddle with table creation and 3 queries: with inner join's, with left join's and mixed. You can also look at execution plan there, too.

It seems that left joins are eliminated in the result execution plan while inner joins are not. Still don't get why, though.

First, lets assume that (id) is the primary key of the table. In this case, yes, the joins are (can be proved) redundant and could be eliminated.

Now that's just theory - or mathematics. In order for the optimizer to do an actual elimination, the theory has to have been converted into code and added in the optimizer's suite of optimizations/rewritings/eliminations. For that to happen, the (DBMS) developers must think that it will have good benefits to efficiency and that it's a common enough case.

Personally, it doesn't sound like one (common enough). The query - as you admit - looks rather silly and a reviewer shouldn't let it pass review, unless it was improved and the redundant join removed.

That said, there are similar queries where the elimination does happen.
There is a very nice related blog post by Rob Farley: JOIN simplification in SQL Server.

In our case, all we have to do in change the joins to LEFT joins. See dbfiddle.uk. The optimizer in this case knows that the join can be safely removed without possibly changing the results. (The simplification logic is quite general and is not special-cased for self-joins.)

In the original query of course, removing the INNER joins cannot possibly change the results either. But it's not common at all to self-join on the primary key so the optimizer does not have this case implemented. It's common however to join (or left join) where joined column is the primary key of one of the tables (and there is often a foreign key constraint). Which leads to a second option to eliminate the joins: Add a (self referencing!) foreign key constraint:

ALTER TABLE "Table"
    ADD FOREIGN KEY (id) REFERENCES "Table" (id) ;

And voila, the joins are eliminated! (tested in the same fiddle): here

create table docs
(id int identity primary key,
 doc varchar(64)
) ;
GO

✓

insert
into docs (doc)
values ('Enter one batch per field, don''t use ''GO''')
     , ('Fields grow as you type')
     , ('Use the [+] buttons to add more')
     , ('See examples below for advanced usage')
  ;
GO

4 rows affected

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1
    join docs d2 on d2.id=d1.id
    join docs d3 on d3.id=d1.id
    join docs d4 on d4.id=d1.id;
set statistics xml off;
GO

id | doc
-: | :----------------------------------------
 1 | Enter one batch per field, don't use 'GO'
 2 | Fields grow as you type
 3 | Use the [+] buttons to add more
 4 | See examples below for advanced usage

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1
    left join docs d2 on d2.id=d1.id
    left join docs d3 on d3.id=d1.id
    left join docs d4 on d4.id=d1.id;
set statistics xml off;
GO

id | doc
-: | :----------------------------------------
 1 | Enter one batch per field, don't use 'GO'
 2 | Fields grow as you type
 3 | Use the [+] buttons to add more
 4 | See examples below for advanced usage

alter table docs
  add foreign key (id) references docs (id) ;
GO

✓

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select d1.* from docs d1
    join docs d2 on d2.id=d1.id
    join docs d3 on d3.id=d1.id
    join docs d4 on d4.id=d1.id;
set statistics xml off;
GO

id | doc
-: | :----------------------------------------
 1 | Enter one batch per field, don't use 'GO'
 2 | Fields grow as you type
 3 | Use the [+] buttons to add more
 4 | See examples below for advanced usage


How to capture data from a PowerShell script that runs as part of a SQL Agent Job? I have a PowerShell script called from an Agent job which opens an Excel file, converts it to .CSV and saves the .CSV file in a particular location, ready for me to import it with BULK INSERT.

What I need to do is count the number of rows in the file before loading it. Where the file is above a certain number of rows, I want to load the file to a different table. This is simple to do in PowerShell, I can get the count into a PowerShell variable no problems.

However, I have no idea how to read that variable into SQL so I can use it in my BULK INSERT process.

So my question is this: When running PowerShell from a SQL Agent Job, how do I read values from the output of that PowerShell script, either into a table, temp table or variable, I don't really mind which?

Thanks to @Tony Hinkle for giving an answer as a comment. Here it is promoted to an actual answer, so hopefully the next person with this question finds it:

I created a table in my "Automation" database named "ImportRowCount" with one column named "RowCount" and inserted a single row in there with the number 0

Next, I added this line to my Powershell script:

invoke-sqlcmd -ServerInstance SQLServerHostName -Query "UPDATE Automation.dbo.ImportRowCount SET RowCount = $NumRows"

(Replace "SQLServerHostName" with your actual server's hostname)

So now my Powershell script updates that single row with the value of the $NumRows variable each time it runs.

This is perfect, since I can read the contents of that table before I run the remaining process, and I can use it as a decision point to branch my code into various different paths depending on how many rows are in the file.


Refactoring/normalization - but almost empty table I normalized a legacy DB into this structure:

But I'm not sure if it is correctly normalized. I don't feel very comfortable with the almost empty filters table.

The requirements

A group contains several items.
A filter organizes how a item is "displayed".
Multiple terms are defining a filter.
Different groups may contain the same item, but different filter.
A filter cam have no terms.

Not really important requirements

A filter might be used several times.

Btw. the diagram was created using http://cakeapp.com.

Honestly I have several questions about the requirements.  For example: Can a group contain the same item under different filters?  Can a group contain the same item under the same filter? Can a term be used on multiple filters?

Based on what you have above here is what I would create. Starting with the easy part you have a term table and a filter table.  A filter can have multiple terms, or none.  The one-to-many relationship here handles the Multiple terms are defining a filter and A filter cam have no terms requirements.  Next the group_item_filter table joining the group, item and filter tables creating a many-to-many-to-many relationship.  You will want to put a unique index on this table depending on the questions I have above.  My guess is that a group can have an item only once so the unique index would be (group_id, item_id).  This table allows for the requirements Different groups may contain the same item, but different filter and A filter might be used several times and A group contains several items.

I wouldn't worry about the all but empty filter table.  It still represents a logical entity regardless of weather or not it has a description or any other field.  For that mater you never know, you may decide to add a description later, or any number of other fields depending on the real world application.

Also I realize the table group_item_filter sounds a bit funny and you could break it down into two tables item_filter and group__item_filter but honestly it doesn't work as well.  This way you can create unique indexes to further define the business rules without some of the restrictions that breaking it up will cause.

EDIT: 4/10/2013 9:24PM

Based on the comments below I have another option for you.  In this one I'm breaking up the 3 way cross reference table.  First there is a cross reference table between the group and the item tables (group_item) to support the requirement A group contains several items.  Next there is a second cross reference table that joins the group_item cross reference table with the filter table.  This supports the requirements Different groups may contain the same item, but different filter and A filter might be used several times.  The benefit here is that you don't have any unusual structures (the 3 way cross reference table) but still support the same requirements.  You also have an easy way to logically support a requirement where an item may only be used once per group by putting a unique key on the (group_id, item_id) combination in the group_item table.


PostgreSQL Database Migration How can I migrate data from on PostgreSQL database to another PostgreSQL database?  Is there any technique to migrate data with SQL scripting?

If you migrate to a new version of PostgreSQL use pg_dump from the latest version. Anyway, this is a simple way to dump and restore :

pg_dump -U username -h hostname source_database | psql -U username target_database


SELECT Dynamic columns with values from other table I need some help with a query. I want to select extra columns to a database query that are stored in another table. I will try to explain what I want to do. I have two tables a Task table and a TaskField table. When I query the task table then sometimes I need some extra fields selected from the TaskField table.

+-------------------+ +---------------------------+
|       Tasks       | |        TaskFields         |
+-------------------+ +---------------------------+
| TaskId PK int     | | TaskFieldId PK int        |
| TaskName string   | | TaskId FK -> Tasks.TaskId |
| TaskAuthor string | | TaskFieldName string      |
|                   | | TaskFieldValue string     |
+-------------------+ +---------------------------+

The rows in those tables will look like this. The Tasks table looks as follows

+--------+--------------+------------+
| TaskId |   TaskName   | TaskAuthor |
+--------+--------------+------------+
|      1 | Testing Task | Me         |
+--------+--------------+------------+

The taskfields table will contain this rows.

+-------------+--------+---------------+----------------+
| TaskFieldId | TaskId | TaskFieldName | TaskFieldValue |
+-------------+--------+---------------+----------------+
|           1 |      1 | Created_at    | 2017-31-05     |
|           2 |      1 | TaskLocation  | Berlin         |
+-------------+--------+---------------+----------------+

Now I want to dynamically select the data from taskfields into a row from table so I will get the following output.

+--------+--------------+------------+------------+--------------+
| TaskId |   TaskName   | TaskAuthor | Created_at | TaskLocation |
+--------+--------------+------------+------------+--------------+
|      1 | Testing Task | Me         | 2017-31-05 | Berlin       |
+--------+--------------+------------+------------+--------------+

I have no idea where to begin with this query and I don't even know if it's possible in mysql. I tried to start of with the following query.

SELECT `Tasks`.`TaskId`, `Tasks`.`TaskName`, `Tasks`.`TaskAuthor` FROM `Tasks`
JOIN `TasksFields` ON `Tasks`.`TaskId` = `TasksFields`.`TaskId`

This will return all rows joined but this is not the result I hoped for I need to dynamically append the colums to the result, but I have no idea how to get it done.

what is the logic behind above database design ?

Is TaskfieldName dynamic for particular taskid ?

In Sql Server,

    create table #t (TaskId int,TaskName varchar(50)
 ,TaskAuthor varchar(50) )
insert into #t VALUES(1,'Testing Task','Me')

create table #t1 (TaskFieldId int,TaskId int
 ,TaskFieldName varchar(50), TaskFieldValue varchar(50))
 insert into #t1 VALUES
(1 ,1,'Created_at'   ,'2017-31-05')
,(2 ,1,'TaskLocation' ,'Berlin')
,(3 ,2,'TaskLocation' ,'Berlin')
declare @TaskID varchar(10) =1
declare @Taskfield varchar(200)
declare @Sql varchar(max)=''
select top 1
@Taskfield=stuff((select ','+'['+TaskFieldName +']'
from #t1 t11 where taskid=t1.taskid for xml path('') ),1,1,'')
from #t1 t1
where t1.TaskId=@TaskID
--select @Taskfield
set @Sql=
'SELECT *
FROM (
    SELECT t.*
        ,t1.TaskFieldName
        ,t1.TaskFieldValue
    FROM #t t
    INNER JOIN #t1 t1 ON t.TaskId = t1.TaskId
    where t.taskid='+@TaskID+'
    ) AS src
pivot(max(TaskFieldValue) FOR TaskFieldName IN (
            '+@Taskfield+'
            )) pv;'

--print @Sql
EXEC (@Sql)
drop table #t
drop table #t1


Fast migration of field in MySQL Right now I have the following tables items

name (varchar)
category (varchar)
id

...etc

I want to create a categories table and items_categories mapping table so i can start tracking categories in its own table.

The problem is with a large items table it takes awhile

Right now I do select distinct category on items table. That is going to be slow and there really isn't a way around it.

What is the fastest way to migrate the category field in items to have a category table and a categories items table? This has to be done in all Sql (no external scripts)

Caveat:  This is untested code.

CREATE TABLE Categories (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    category VARCHAR(...) NOT NULL,
    PRIMARY KEY(id),
    INDEX(category)
) ENGINE = InnoDB;

-- Create mapping table
CREATE TABLE Categories_Items (
    category_id SMALLINT UNSIGNED NOT NULL,
    item_id ...,
    PRIMARY KEY(category_id, item_id),
    INDEX(item_id, category_id)
) ENGINE = InnoDB;

-- Populate Categories table and generate ids
INSERT INTO Categories (category)
    SELECT DISTINCT category FROM items;

-- Start to convert Items.category to Items.category_id
ALTER TABLE Items
    ADD COLUMN category_id SMALLINT UNSIGNED NOT NULL;

-- Populate mapping table
INSERT INTO Categories_Items
   (category_id, item_id)
    SELECT c.id, i.id
        FROM Categories AS c
        JOIN Items AS i  ON i.category = c.category;

-- Finish conversion
ALTER TABLE Items
    DROP COLUMN category;


prepare statement not getting deallocated I am completely new to MySql and trying to use PREPARE STATEMENT, but for unknown reasons previous working query is getting cached.

SP Code :

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_filter`(
        p_membership_status varchar(10),
        p_country_iso_code varchar(2),
        p_subdivision_1_iso_code    char(5),
        p_districtname  varchar(100),

        p_mobile_req tinyint,
        p_most_ordered_items_req tinyint,
        p_emailid_req tinyint,
        p_foreign_members_req tinyint
    )
BEGIN

    /* if we dont set below variables to null, problems persists */
    set @whereclause = null;
    set @query = null;

    if(p_mobile_req = 1) then
        begin
            set @selectcolumn = 'mobile';
            if(p_country_iso_code is not null) then
            begin
                set @whereclause = concat('country_iso_code = ', "'" , p_country_iso_code , "'");
            end;
            end if;
        end;
    end if;

    if(@whereclause is not null) then
    begin
        set @query = concat('select ', @selectcolumn, ' from member where ', @whereclause);
        PREPARE stmt1 FROM @query;
        EXECUTE stmt1;
        DROP PREPARE stmt1;
    end;
    end if;
END

For example, if I call above sp like

call sp_filter(null, 'in', null, null, 1, 0, 0, 0);
call sp_filter(null, null, null, null, 1, 0, 0, 0);
// result of this 2nd call will present result of 1st call as if second parameter is now 'in' instead of null

I tried countless times, but of no use. So, I set both variables to null at the start and problem was solved. No idea why ?

What could be the reason behind this behavior ?

User-defined variables such as @whereclause and @query will retain their values between calls. I.e. the variables get a value from call #1 to the procedure and will then keep those values when you get to call #2. That is why you have to initialise the variables to make your procedure work.

However, instead of using user-defined variables, it is good practice to DECLARE variables inside the procedure:

DECLARE whereclause TEXT unsigned DEFAULT NULL;
DECLARE query TEXT unsigned DEFAULT NULL;

(For these kinds of variables you don't prefix them with the '@' character.)


Postgres - fast way to enforce AND across multiple rows I've got a 4 column table with ~25M rows:

tweetifai=> \d core_tagusage;
                                 Table "public.core_tagusage"
  Column  |         Type          |                         Modifiers
----------+-----------------------+------------------------------------------------------------
 id       | integer               | not null default nextval('core_tagusage_id_seq'::regclass)
 tag      | character varying(30) | not null
 score    | double precision      | not null
 tweet_id | character varying(16) | not null
Indexes:
    "core_tagusage_pkey" PRIMARY KEY, btree (id)
    "core_tagusage_tag_3ed0f993a18dd430_uniq" UNIQUE CONSTRAINT, btree (tag, tweet_id)
    "core_tagusage_cea90699" btree (tweet_id)
    "core_tagusage_score_772bb061400905c6_uniq" btree (score)
    "core_tagusage_tag_443351eb_uniq" btree (tag)
    "core_tagusage_tweet_id_11461293c1e23f80_like" btree (tweet_id varchar_pattern_ops)
Foreign-key constraints:
    "core_tagusage_tweet_id_11461293c1e23f80_fk_core_imagetweet_id" FOREIGN KEY (tweet_id) REFERENCES core_imagetweet(id) DEFERRABLE INITIALLY DEFERRED

There is a many-to-many relationship between tweet_id and tag.

Given a few tags (eg. "dog", "grass", "frisbee") I'd like to retrieve all tweet_ids  which match all tags and sort by score. Here's my query:

SELECT *
FROM (SELECT
        COUNT(ort.tweet_id) AS numtags,
        ort.tweet_id,
        MIN(ort.score)      AS minscore
      FROM (SELECT
              tg.tweet_id AS tweet_id,
              tg.tag      AS tag,
              tg.score    AS score
            FROM core_tagusage AS tg
            WHERE tg.tag = 'dog'
                  OR tg.tag = 'grass'
                  OR tg.tag = 'frisbee'
           ) AS ort
      GROUP BY ort.tweet_id) AS ct
WHERE numtags = 3
ORDER BY minscore DESC
LIMIT 200;

Even without the ORDER BY this is remarkably slow (~10-20 seconds). Here's what I get from EXPLAIN ANALYZE:

Limit  (cost=0.56..22720.77 rows=200 width=33) (actual time=24177.117..24177.117 rows=0 loops=1)
  ->  GroupAggregate  (cost=0.56..1327311.85 rows=11685 width=25) (actual time=24177.115..24177.115 rows=0 loops=1)
        Group Key: tg.tweet_id
        Filter: (count(tg.tweet_id) = 3)
        Rows Removed by Filter: 493764
        ->  Index Scan using core_tagusage_cea90699 on core_tagusage tg  (cost=0.56..1321691.90 rows=547389 width=25) (actual time=0.206..23322.853 rows=501654 loops=1)
              Filter: (((tag)::text = 'dog'::text) OR ((tag)::text = 'grass'::text) OR ((tag)::text = 'frisbee'::text))
              Rows Removed by Filter: 24309855
Planning time: 0.237 ms
Execution time: 24177.166 ms

Is there a different query I could write to achieve the same results?

edit:

EXPLAIN ANALYZE SELECT
              tg.tweet_id AS tweet_id,
              tg.tag      AS tag,
              tg.score    AS score
            FROM core_tagusage AS tg
            WHERE tg.tag = 'dog'
                  OR tg.tag = 'grass'
                  OR tg.tag = 'tree';

Bitmap Heap Scan on core_tagusage tg  (cost=295.51..44454.72 rows=13794 width=32) (actual time=8.253..105.051 rows=104691 loops=1)
  Recheck Cond: (((tag)::text = 'dog'::text) OR ((tag)::text = 'grass'::text) OR ((tag)::text = 'tree'::text))
  Heap Blocks: exact=1133
  ->  BitmapOr  (cost=295.51..295.51 rows=13797 width=0) (actual time=8.111..8.111 rows=0 loops=1)
        ->  Bitmap Index Scan on core_tagusage_tag_443351eb_uniq  (cost=0.00..95.06 rows=4599 width=0) (actual time=3.238..3.238 rows=38385 loops=1)
              Index Cond: ((tag)::text = 'dog'::text)
        ->  Bitmap Index Scan on core_tagusage_tag_443351eb_uniq  (cost=0.00..95.06 rows=4599 width=0) (actual time=2.217..2.217 rows=30477 loops=1)
              Index Cond: ((tag)::text = 'grass'::text)
        ->  Bitmap Index Scan on core_tagusage_tag_443351eb_uniq  (cost=0.00..95.06 rows=4599 width=0) (actual time=2.649..2.649 rows=35864 loops=1)
              Index Cond: ((tag)::text = 'tree'::text)
Planning time: 0.093 ms
Execution time: 187.032 ms

I think you have unnecessary steps in your query, consider this:

SELECT *
FROM (SELECT
        COUNT(tag) AS numtags,
        tweet_id,
        MIN(score)      AS minscore
      FROM  core_tagusage
            WHERE tag = 'dog'
                  OR tag = 'grass'
                  OR tag = 'frisbee'
      GROUP BY tweet_id) AS ct
WHERE numtags = 3
ORDER BY minscore DESC
LIMIT 200;

and this:

SELECT
    tweet_id,
    MIN(score) AS minscore
FROM  core_tagusage
WHERE tag IN ('dog', 'grass', 'frisbee')
GROUP BY tweet_id
WHERE COUNT(*) = 3
ORDER BY minscore DESC
LIMIT 200;

I also think that running analyze on your table will help the query planner.

After playing with some fabricated data locally, I was seeing ~600K rows take ~1sec, so 10 seconds doesn't seem unreasonable (but my data was so predictable that analyze made the query planner just go for a sequential scan)


How can I mask a CMD-Variable reference $(myVar)? It is kind of an accademic question, but if I write a SQL-Script for SQL-Server in CMD-Mode, how could I create an column-alias like [$(myVar)]?

SELECT 1 AS [$(myVar)] --error: myVar is not defined

Within the script, use SETVAR:

:SETVAR myVar ColumnAlias
SELECT 1 AS [$(myVar)];

Alternatively, use the -v SQLCMD command-line parameter to pass variable values to the script.  See https://msdn.microsoft.com/en-us/library/ms162773.aspx SQLCMD for syntax.

EDIT:

Note that there are two parsers involved here, SQLCMD and T-SQL.  Double quote enclosures are required for SQLCMD variable values when the value
contains a space or a double quote.  Double quotes within the SQLCMD values are  escaped with an additional double quote:

--Double quote enclosure optional
:SETVAR myVar ColumnAlias
:SETVAR myVar "ColumnAlias"
:SETVAR myVar "ThisIsA'ColumnAlias'"

--Double quote enclosure required due to spaces or double quotes
:SETVAR myVar "Column Alias"
:SETVAR myVar "This Is A ColumnAlias"
:SETVAR myVar "This Is A 'ColumnAlias'"
:SETVAR myVar "This Is A ""ColumnAlias"""

Separately, there are additional considerations depending on how the SQLCMD variable value is used within T-SQL scripts.  In your example, the value is used
as an identifier (column alias) and is already enclosed in square brackets within the T-SQL script.  Right brackets within the value need to be escaped with and additional squure bracket for T-SQL parsing:

:SETVAR myVar [ColumnAlias]]
SELECT 1 AS [$(myVar)];
:SETVAR myVar "[ColumnAlias]]"
SELECT 1 AS [$(myVar)];

In the case of a T-SQL string literal, single quotes need to be escaped not for SQLCMD, but for T-SQL parsing:

:SETVAR myVar "This is a ''string literal''"
SELECT '$(myVar)' AS [String Literal];


Is an MDF "Last Modified Date" affected by transaction log backup? This is possibly a dumb question, but it has been triggered by a conversation I'm having with a colleague in our Dev team.

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

However I've just noticed our hourly log backups are not changing the date of my .mdf files - they are stuck at the date/time of the last full backup. Shouldn't they be changing hourly for active databases?

What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date? And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

You are correct for the first part but why would full backup or log backup change "Last Modified" time of mdf file. Its actually reading from the data file its not making any changes. Normally in daily operation SQL Server reads quite often from data file but for every physical read it is not going to update the time stamp or modifiy the date that would be unnecessary.

Moreover the way SQL Server access the data and log file are not that straight it uses different mechanism so I guess its useless worrying about modified date.

  What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date?

NO it would not

  And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?

Well it can change when database is taken offline and brought online and after service restart


Is there any way to keep rows with same id sequential even if they are not stored sequentially? Edit: I think i used the wrong word here. By "sequential" i don't mean that everything aid 1 enters has to be in the order of the time at which they enter it.

What i mean is that they should be clubbed together in the table that's it.

Suppose there are tables A and B.

A is linked to table B that stores data which may be repeated for a user. Each user may have 0 or 1 or 2... or n(limit) number of data is table B.

But the data is not entered sequentially because it totally depends on if/when the user chooses to enter it, how many data they enter.

So if user with aid 1 enters "lol" today, and then user with aid 2 enters ":)" tomorrow,and then 1 enters xd day after tomorrow then the data will be stored in the same manner i.e.

| bid        | data3       |
|:-----------|------------:|
| 1          |        lol  |
| 2          |        :)   |
| 1          |        xd   |

Is there a better way to do this? Maybe the way i am storing data is wrong.

Or maybe the table can to optimized in some way like making the rows with same id to be sequential i.e. one after the other such that there is no row with a different id between 2 rows with the same id.

In rare cases the storage order of the data does matter for performance.  By "rare" I mean less than maybe 2% of tables benefit enough to bother doing anything.

Here's a use case where clustering consecutive rows 'together' (or at least 'close') could lead to significant decrease in I/O (sometimes 10-fold).

The table is too big to be cached in RAM in InnoDB's buffer_pool -- hence the potential for being slowed down by I/O.
The main queries do a range scan on whatever controls the data order.  For InnoDB, that is a range scan on the PRIMARY KEY.
Or the main queries are otherwise "close" because of a 2-column PRIMARY KEY.  Think of a messaging system and PRIMARY KEY(user_id, message_id) and the user is jumping around in his messages.

In one application where message_id is AUTO_INCREMENT, changing from

PRIMARY KEY(message_id),
INDEX(user_id, message_id),
INDEX(user_id, ...)

to

PRIMARY KEY(user_id, message_id)  -- clustered primarily on user_id
INDEX(message_id),    -- sufficient for AUTO_INCREMENT
INDEX(user_id, ...)   -- benefits, too

nearly doubled the capacity of the server.

WordPress's wp_postmeta is an example of where this can be beneficial.  But I would say that less that 2% of such instances are big enough to matter.  Still, there are various reasons to change from

  PRIMARY KEY (meta_id),
  INDEX(post_id),
  INDEX(meta_key)

to

  PRIMARY KEY(post_id, meta_key),
  INDEX(meta_key)

More discussion:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta . (This link also discusses what to do if you really need to keep meta_id.)

That brings up another topic.  Should the PRIMARY KEY always be a surrogate AUTO_INCREMENT?  In my experience, two-thirds of tables have a perfectly good "natural key" (sometimes composite) that could be used instead.  Wp_postmeta is one of many examples where the surrogate gets in the way of performance.

Semi-related:  A huge table with a UUID index is hopeless for performance.  Clustering won't help because you never do a 'range' scan and two UUIDs are rarely 'near' each other.  (A partial workaround:  http://mysql.rjweb.org/doc.php/uuid )


Running an SSIS package without the SSIS service installed On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service.

If I have an SSIS package on that server, can I still schedule to run it without SSIS service?

I tried using DTexec but it is not able to find the package.

The SSIS service doesn't actually do much and you can run packages without it or with it disabled. It's used by SSMS amd SQL Agent for example.

See the KB article "Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service"

What you need are the base SQL Server binaries which are installed with the DB engine. So you need a SQL Server Instance of some flavour to have dtexec running. Whether it's the DB Engine or the SSIS service installed but disabled.

Running packages in BIDS is a special case. Run the package on the same PC as BIDS via dtexec and it will fail unless you have installed server components (= a SQL Server Instance)


Substring without the first n characters I'm developing a SQL Server 2016 stored procedure and I want to get the last characters of a varchar(38) column.

I know there will always be at least 18 characters and I don't know the exact length of the column, because it is variable.

I think I can get the length of the column and do a subtract to use SUBSTRING, but I can't do that because I'm doing this:

set @externalCodes = (
    select Serial, AggregationLevel
      from ExternalCode where ProductionOrderId = @productionOrderId
    for json path

I'm generating a JSON and I don't know how to get the length of each Serial column inside a select.

My question is: how can I get a substring from a string without the first 18 characters without knowing its length?

One solution could be:

SUBSTRING(Serial, 18, 38)

And it always return the substring from 18 to the end of the string even if the string doesn't have a length of 38.

Your SUBSTRING solution seems good enough, I am not sure why you would need to ask for anything more. I would only like to note that if you want to skip the first 18 characters, then you should specify 19 as the second argument for SUBSTRING, because in SQL the character positions in a string start from 1. So this makes perfect sense and should work well for you:

SUBSTRING(Serial, 19, 38)

If you specify 18, then you will be skipping 17 characters.

The solution using RIGHT and LEN, suggested by George.Palacios will work as well but the results of these two solutions might not be the same depending on whether Serial can have trailing spaces. The reason for the possible difference is that the LEN function ignores trailing spaces, while RIGHT does not, which means that for a string with trailing spaces the result will most likely not be what you would expect.

Let me demonstrate that using a simple example with a shorter string. Suppose the maximum length is 10, the number of characters to skip is 4, and the specific string is 'abcde  '. (The quotation marks here are just string delimiters, for you to be able to see the trailing spaces.) If you do

SUBSTRING('abcde  ', 5, 10)

you will get 'e  ' as the result, because SUBSTRING will simply start cutting the string at the 5th character and include all the characters up to the end of the string.

In contrast, the RIGHT/LEN option

RIGHT('abcde  ', LEN('abcde  ') - 4)

will yield ' '. The LEN function will ignore the two trailing spaces and return 5. Subtracting 4 from 5 gives you 1, thus RIGHT will return just one rightmost character of the string, which is a space.

As I said, though, if Serial can never have trailing spaces, either option will do. For completeness, let me suggest one more, which uses the STUFF function:

STUFF(Serial, 1, 18, '')

The STUFF function allows you to delete a substring from and/or insert another one to a given string at a given position. So, in order to skip 18 characters using this function, you specify starting position 1, 18 characters to delete, and an empty string ('') to replace them with. Like SUBSTRING, this solution will correctly work with trailing spaces, should you have the need to take them into account.


Mongodb what is the cost of array queries? As far as I can tell, find queries in mongodb involving an array appear very expensive. Take as example the queries given in: https://docs.mongodb.com/manual/tutorial/query-array-of-documents/

db.inventory.insertMany( [
   { item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
   { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
   { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
   { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

And find query:

db.inventory.find( { "instock": { warehouse: "A" } } )

The above example can be described as simple as "Find all records connected to warehouse A". But what is the cost of the find query (big o)? Are there any indexing optimizations going on here, or is it basically an exhaustive search with a cost of O(N*K), where N is the number of inventory records, and K is the number of elements in the instock array of each record? and if so, are there any ways of optimizing this to minimize cost, by say indexing?

what is the cost of the find query (big o)?

Unindexed queries are going to involve a collection & document scan, so a search complexity of O(N*K) to iterate all documents and array field values.

Indexed queries use a B-tree search which would have a search complexity of O(log K) for a simple query and appropriate index (where K is the number of elements). MongoDB automatically flags an index as multikey if any indexed field is an array. The multikey flag is used to determine correctness of query bounds when an array field is used  in a query with multiple predicates.

  db.inventory.find( { "instock": { warehouse: "A" } } )

This query would have no results for the given example data because it is strictly matching the embedded document { warehouse: "A" } (which would not match embedded documents with additional fields like qty).

To achieve the outcome you described, the query should instead use dot notation to search the embedded warehouse values:

db.inventory.find( { "instock.warehouse": "A" } )

  Are there any indexing optimizations going on here

You can explain query results with execution stats to view the general work that goes into a given query:

db.inventory.find( { "instock.warehouse": "A" } ).explain('executionStats')

High level metrics include keysExamined (index key comparisons) and docsExamined (documents fetched). There are many other metrics that might be of interest including query stages and index bounds.

Adding an appropriate index to support your query will illustrate the difference:

db.inventory.createIndex({"instock.warehouse": 1})

The examples here are simple array queries. For details on more complex queries involving multikey indexes, see Multikey Index Bounds in the MongoDB manual. Where possible, internal optimisations attempt to minimise the range of values that need to be scanned when a multikey index is used.


How to better backup MySQL 5.6 database? Today I had an issue where I accidentally deleted a whole table (which cascaded to a few other tables) due to a funky piece of MySQL syntax, see here.

Luckily I had a backup from midnight, however all data was lost between midnight and to when I deleted all the rows in my table.

How can I better back up my database to be more up to date with the live system, or perhaps better just lagging a hour behind it?  I've seen people mention replication and binary logs but this is all kind of new to me.  From my understanding, having a replication setup, deleting my table in the master would also delete in the slave database?  Would I be better off trying to take hourly differential backups?  Or should i just do full backups every hour?  Our database is tiny, less than a gig, and we have room so it's feasible, but I would rather do things a proper way that would work for larger databases as well.

Better choice is to depend upon a backup utility with which you shall configure backup frequency, etc. be it two slaves or...

Alternatively...

If you have enabled mysql log, you shall pretty well re-create those entries since mid night backup till the current time by running all the pertaining queries.

2a. Either you shall revert the whole data to midnight backup and then run the rest of the log to re-create all and getting upto the current level

2b. Or you shall revert the particular data table to midnight backup and run the rest of the log (by screening the queries).

Depending upon your database / program flow structure you may choose any one of...


Selecting Records In SQL Server From Joined Table Where Timestamp Occurs Between Begin/End Range I have 2 tables

Table1 contains RecordID, UserID, Event, EventTimestamp

Table2 contains UserID, UserStatus, UserStatusSubgroup, StatusBeginTimestamp, StatusEndTimestamp

Table1 --SQL Server 2014 Table--

RecordID [char(18)]
UserID [nvarchar(50)]
Event [nvarchar(20)]
EventTimestamp [DateTime2(3)]

Table2 --SQL Server 2014 Table--

UserID [nvarchar(50)]
UserStatus [nvarchar(10)]
UserStatusSubgroup [nvarchar(20)]
StatusBeginTimestamp [DateTime2(3)]
StatusEndTimestamp [DateTime2(3)]

Primary Key is UserID

For each RecordID in Table1 there can be many Events performed by the UserID with the EventTimestamp recorded

I wish to join to Table2 to retreive Table2.UserStatus and Table2.UserStatusSubgroup where Table1.EventTimestamp occurs between Table2.StatusBeginTimestamp and Table2.StatusEndTimestamp

How can I do this?

--Setup demo data
Declare @Table1 table
    (RecordID int, UserID varchar(5), Event varchar(20), EventTimestamp DateTime2(3))
;

INSERT INTO @Table1
    (RecordID, UserID, Event, EventTimestamp)
VALUES
    (123456, 'Bob', 'EventA', '2018-05-30 19:01:29.683'),
    (123456, 'Bob', 'EventB', '2018-05-30 19:02:25.893'),
    (123456, 'Bob', 'EventC', '2018-05-30 19:03:15.323'),
    (123456, 'Bob', 'EventD', '2018-05-30 19:04:45.013'),
    (123456, 'Bob', 'EventE', '2018-05-30 19:05:12.993'),
    (123456, 'Null', 'EventF', '2018-05-30 19:06:00.999'),
    (123456, 'Null', 'EventG', '2018-05-30 19:07:00.002')

;

| RecordID  | UserID | Event      | EventTimestamp          |
|-----------|--------|------------|-------------------------|
| 123456    | Bob    | EventA     | 2018-05-30 19:01:29.683 |
| 123456    | Bob    | EventB     | 2018-05-30 19:02:25.893 |
| 123456    | Bob    | EventC     | 2018-05-30 19:03:15.323 |
| 123456    | Bob    | EventD     | 2018-05-30 19:04:45.013 |
| 123456    | Bob    | EventE     | 2018-05-30 19:05:12.993 |
| 123456    | Null   | EventF     | 2018-05-30 19:06:00.999 |
| 123456    | Null   | EventG     | 2018-05-30 19:07:00.002 |

--Setup demo data
Declare @Table2 table
    (UserID varchar(5), UserStatus varchar(10), UserStatusSubgroup varchar(20), StatusBeginTimestamp DateTime2(3), StatusEndTimestamp DateTime2(3)

;

INSERT INTO @Table2
    (UserID, UserStatus, UserStatusSubgroup, StatusBeginTimestamp, StatusEndTimestamp)
VALUES
    ('Bob', 'StatusA', 'SubStatus1', '2018-05-30 19:00:00.000', '2018-05-30 19:03:00.000'),
    ('Bob', 'StatusB', 'SubStatus2', '2018-05-30 19:03:00.000', '2018-05-30 19:04:00.000'),
    ('Bob', 'StatusC', 'SubStatus3', '2018-05-30 19:04:00.000', '2018-05-30 19:06:00.000')
;

| UserID | UserStatus | UserStatusSubgroup   | StatusBeginTimestamp    |  StatusEndTimestamp     |
|--------|------------|----------------------|-------------------------|-------------------------|
| Bob    | StatusA    | SubStatus1           | 2018-05-30 19:00:00.000 | 2018-05-30 19:03:00.000 |
| Bob    | StatusB    | SubStatus2           | 2018-05-30 19:03:00.000 | 2018-05-30 19:04:00.000 |
| Bob    | StatusC    | SubStatus3           | 2018-05-30 19:04:00.000 | 2018-05-30 19:06:00.000 |

You spelled out your solution:

  "I wish to join to Table2 to retreive Table2.UserStatus and
  Table2.UserStatusSubgroup where Table1.EventTimestamp occurs between
  Table2.StatusBeginTimestamp and Table2.StatusEndTimestamp"

    SELECT T1.*, T2.UserStatus, T2.UserStatusSubgroup
    FROM @Table1 AS T1
        INNER JOIN -- or LEFT OUTER JOIN to retain the rows with null userid
        @Table2 AS T2
        ON T1.UserID = T2.UserID
    WHERE /*(T1.UserID IS NULL) OR -- to retain rows with null userid  */
          T1.EventTimestamp BETWEEN T2.StatusBeginTimestamp AND T2.StatusEndTimestamp


Can I trust the effective permissions of default database roles? I've granted membership in the db_datareader and db_datawriter roles for some testing being performed by a specific user.

Looking at the names of each role, I know what the roles do. However, I want to see where the permissions were assigned just to better understand.

To my surprise I see a blank slate for the db_datareader schema for that database. I go onto read Understanding SQL Server fixed database roles and from there I know why I cannot see anything in that properties dialog:

  The catch with this role is that the permission is implicit. That means if you query sys.database_permissions, you will not see any permission granted, either to the db_datareader role or directly to the user. Therefore, if you need to audit for everyone who has SELECT access to particular tables in a database, you'll have to query the membership of this group via the use of sp_helprolemember

I was aware of querying the roles which obviously works fine. So I thought this was perhaps a limitation of the GUI. I liked the permissions script from this answer and gave that a run. However, upon execution, that returns NULL for permissiontype and permissionstate which is consistent with the above quote.

All that said, are these pre-canned roles infallible? Can I assume that no one can mess around with the implicit nature of their respective permissions i.e you can't remove select rights from db_datareader behind the scenes?

Here's what I just tried in a test database (SQL Server 2012):

GRANT EXEC ON SCHEMA::dbo TO db_datareader

...And the response I got:

Msg 4617, Level 16, State 1, Line 1
Cannot grant, deny or revoke permissions to or from special roles.

So I'm assuming you're reasonably safe. Of course, that's not to say you couldn't do something wacky like having a stored procedure testing for membership in those roles, and implementing its own security functionality based on the results.