How to get minimum and maximum for grouped timestamps

Raster of 10-minute intervals
I suggest to group by a combination of "hour" and 10-minute interval:
SELECT hero
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM   tbl
GROUP  BY hero
     , date_trunc('hour', timestamp)
     , EXTRACT(MINUTE FROM timestamp)::int / 10
ORDER  BY 1, 2;  -- optional

Refer to the chapter Date/Time Functions and Operators in the manual.
EXTRACT(minute FROM timestamp) extracts the minute part of the time The expression. After the cast to integer (::int), integer division (/ 10) effectively rounds to 10-minute intervals (0 - 5).
The CASE expression only adds an end_time if more than one rows fall in the same 10-minute interval.
I advise not to use the "timestamp" as identifier. It's a reserved word in standard SQL and base data type in Postgres.
Groups defined by gaps of 10 or more minutes
If "groups" are defined by gaps of 10 minutes or more between rows of the same hero:
SELECT hero
     , count(*) AS ct  -- optional
     , min(timestamp) AS start_time
     , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM  (
   SELECT hero, timestamp, count(step OR NULL) OVER (ORDER BY hero, timestamp) AS grp
   FROM  (
      SELECT *
           , lag(timestamp) OVER (PARTITION BY hero ORDER BY timestamp)
           < timestamp - interval '10 min' AS step
      FROM   tbl
      ) sub1
   ) sub2
GROUP  BY hero, grp;

Detailed explanation:

Select longest continuous sequence

db<>fiddle here
Old sqlfiddle


You can't reference depot_resource in the outer query, it is out of scope.
You need to join the tables, and group by name to get the aggregate.
Since you didn't provide DDL and sample data, we can only guess, but maybe you need something like this?

SELECT j.name, COUNT(*) AS rescount
FROM transit as j
     INNER JOIN
     depot_resource as m
     on j.scheduled_time between m.actual_start and m.actual_end
GROUP BY j.name

Something seems odd here as there is no correlation to j.name in depot_resource, but without the table structure and the expected result we can't really tell.

HTH


I can backup my SQL Server 2008 R2 but can't restore from the very same folder

found it. turns out the permissions command I was using isn't enough for a restore (it's enough for the backup).  Granting the full access rule on the folder isn't enough - it has to be on "the folder, subfolders and files".

basically two more lines of code:

myDirectorySecurity.AddAccessRule(new FileSystemAccessRule(sqlserviceuser, FileSystemRights.FullControl, InheritanceFlags.ContainerInherit, PropagationFlags.None, AccessControlType.Allow));

myDirectorySecurity.AddAccessRule(new FileSystemAccessRule(sqlserviceuser, FileSystemRights.FullControl, InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow));


Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column

Turned out to be a minor corruption of the SQL backup file.
After re-running the entire process again, the restore worked fine with no errors.

You would think that Postgres would have in-built checking to ensure integrity of exported data!


SQL Server 2005 Log Truncate - how long for a 60Gb log; mins, hours or days?

but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen). The recovery mode is FULL (SQL Server 2005).

I guess previous users were taking file system backup which in my opinion is not best practice when you have important app running. You can never get point in time recovery. Your thinking is correct about creating a maintenance plan backup it gives you more control on backup. Plus if you don't want point in time recovery you can put database in simple recovery mode and can just have full and differential backups as per RPO and RTO. This would avoid the hassle of transaction log file backup and unnecessary growth of log file

  This is a bit of a "how long is a piece of string" question, but just need a ball park figure really of how long a truncate will take. Is it likely to be minutes, hours or days for a 60Gb log (the machine is reasonable intel 4 core 2.5Ghz 8Gb machine (not SSD drives))?

First please note log truncations and transaction log backup are not same thing.Its transaction log backup only which is responsible for log truncations in full recovery mode. For DB in simple recovery mode checkpoint causes log truncation or when log grows 70 % of its size.

Regarding how much time transaction log backup of 60 GB log file would take its hard to predict there is not such formula. If you have faster disk where log file resides and faster disk where you are dumping the backup it would take normal time. Backup reads data from disk and writes it to disk so if disk is not facing I/O contention it would be quick.

You must also read

Optimizing backup and restore Performance in SQL Server

Understanding SQL Server Backup


Forbid user to query internal SQL server information

I am afraid that there is no way to deny anyone permissions to see that kind of information. However, impersonating a database user is not enough to see the effects on server-scoped DMVs or functions such as sys.login_token: you need to impersonate a login instead.

CREATE LOGIN ZeroCool WITH PASSWORD = 'ICantSeeCoolInfo', CHECK_POLICY = OFF;
GO

EXECUTE AS LOGIN = 'ZeroCool';
GO

SELECT DB_NAME() , @@VERSION
SELECT ConnectionProperty('net_transport')
SELECT * FROM sys.login_token
GO

REVERT;

Results:

------------------------------------------------------------------------ -----------------------------------------------
master                                                                   Microsoft SQL Server 2012 - 11.0.5613.0 (X64)
    May  4 2015 19:05:02
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

(1 row(s) affected)

--------------
Shared memory

(1 row(s) affected)

principal_id sid                                  name         type          usage
------------ ------------------------------------ ------------ ------------- ---------------
301          0x34FA109FE0F7694E866DF0F9E82BB99B   ZeroCool     SQL LOGIN     GRANT OR DENY
2            0x02                                 public       SERVER ROLE   GRANT OR DENY

(2 row(s) affected)

I don't see how preventing users from seeing those info will help you tighten security: just implement security best practices and you should be ok.


Postgresql sudden storage increase at regular interval?

Since you have no filesystem access you won't get the answer but some guesses only, so here comes mine: if there are no bulk INSERTs, this might be some temporary space created by SELECT, for example big query with "Sort Method: external merge Disk", or some TEMPORARY tables, materialized view REFRESH etc. Such query would obviously take some time, so check your query log for very long duration and EXPLAIN them if found.
The usage sliding down, however, suggests it's WAL-related. SELECT * FROM pg_stat_archiver or pg_stat_replication might show something interesting, e.g. I check for streaming lag as follows:
SELECT active, pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots
What also might happen is that the space reported itself is skewed compared to datasize. For example (this is not the case, but makes reading such graphs easier) reclaiming disk space from btrfs or after removing many files on ext3/4 filesystem might take some significant time (I've seen like 10 minutes or so on btrfs).


Which Optimistic Concurrency Control method will work out best in the long run?

Depends on your DBMS platform to some extent.  On the SQL Server based development I did back in the late Jurassic period we used timestamps as these are automatically updated when the record is written.

You load a record with:

select Foo
      ,Bar
      ,TS
  from FooTab
 where FooID = 666

And save with

update FooTab
   set Foo = @Foo
      ,Bar = @Bar
 where FooID = @FooID
   and TS = @TS

select @@rowcount as RowCount

... or some such, depending on where the concurrency was being handled.

Oracle doesn't have such a timestamp, so the Oracle stuff I did around the same era worked by incrementing a version counter.

My view about checksumming and comparing field values is it is fiddlier and therefore easier to make a mistake with, so it's best only used when you don't have the option of using a timestamp or version on the record.  And, yes, without applying any google-fu to the question, I can't think of a way to do it with BLOBs off the top of my head either.  You might be able to do the comparison separately with something like:

... and hashbytes ('MD5', BlobField) = @BlobHash

So, as an opinion, use timestamping or versioning if you have the option - it's much simpler.  You can do field comparisons or hashing if you don't have the option of changing the database, but it's more fiddly.


Actually SYS.AUD$ table CAN be exported but only using old export command exp instead of expdp.

exp \"/ as sysdba\" file=aud_table.dmp tables=sys.aud$;

Since 11g or 10 (not sure) AUD$ table is prohibited to be exported.

The most convenient way is what @Balazs Papp suggested, to create table AUD$_BACKUP as select * from SYS.AUD$


How to Use a Certificate from a Different Database

In SQL Server for column encryption, you typically use the Service Master Key to encrypt your Database Master Key, which, in turn, encrypts your Certificate, which encrypts your Symmetric Key, which encrypts your data.

See https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy

For Transparent Database Encryption the hierarchy is a little different.  The Database Encryption Key in your database is encrypted by the Database Master Key in the Master Database.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

In both cases the key used to encrypt/decrypt data is protected by an encryption chain that goes through the Master database.  I think both meet your requirement in effect.


MongoDB Atlas M0 (Free Tier) BI capabilities

Your BI Connector links are from different documentation contexts (Atlas vs On-Prem). For an overview of deployment variations, see About the MongoDB Connector for BI.

  Checking the documentation again I realized BI Connector is not supported in M0: https://docs.atlas.mongodb.com/bi-connection/#bi-connection

The MongoDB Atlas documentation is referring to "BI Connector for Atlas", which is an integrated feature allowing you to add a managed instance of BI Connector to an M10+ Atlas deployment. Since BI Connector operations may be CPU and memory intensive, adding a managed BI Connector is only supported for dedicated Atlas clusters.

  This is the documentation I used originally and it does mention limitation for free tier but doesn't say it's not supported at all so I'm confused what's the root cause for this error message and if it should work or not:
  https://docs.mongodb.com/bi-connector/v2.1/tutorial/connecting-to-atlas/

This is the product documentation for the On-Prem MongoDB Connector for BI. If you install this in your own environment, you can connect to a supported MongoDB deployment (including the Atlas free tier) subject to any caveats for your deployment environment.

For example, the Atlas free tier cannot run aggregation pipelines using the allowDiskUse option. This means aggregation stages will be limited to using 100MB of RAM per stage, so queries might fail if one of the intermediate result sets is too large.

See Atlas Free Tier Limitations for other considerations.

  unable to load MongoDB information: failed to create admin session for loading server cluster information: unable to execute command: server selection error: server selection timeout

This error message indicates inability to connect to your Atlas deployment. Make sure the source IP for your BI Connector instance is whitelisted in Atlas and check the connection string details.


STEP 01

Go see if MySQL is running on the Server. Run this

service mysql status

If the status says SUCCESS! MySQL running (some number) then shutdown mysql

service mysql stop

STEP 02

Run ls -l /etc/my.cnf. If the file exists, rename it with mv /etc/my.cnf /etc/my.cnf.backup

Then, copy your my.cnf into /etc

STEP 03

See if you have a previous mysql setup

ls -l /var/lib/mysql

If you do, do this

mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql

Then, copy your /root/home/recovered/mysql into /var/lib/mysql

STEP 04

chown -R mysql:mysql /var/lib/mysql

STEP 05

service mysql start

GIVE IT A TRY !!!


Using two VIEWs in your SELECT statement will always be better because it can combine both of the views query plans into a single optimised query. Very neat solution which is also fast.

BTW. Using an in-memory temp table > 100 rows generally poor performance because the query plan optimiser assumes there is only 1 row in the temp table.


Doesn't make much sense:

the number of pairs is 6, -- not relevant to the question...
the number of unique permuted pairs is 4 - far more relevant...
There are 2 matched pairs so I make the number of unique to matched pairs at 4/2, i.e. 50%...

Notwithstanding the objection above, what you can do is something like this, using the LEAST and GREATEST (almost standard - see discussion below) SQL functions.

This example (see fiddle) is from PostgreSQL, however see discussion at the end.

CREATE TABLE test (col1 INTEGER, col2 INTEGER);

populate with your data:

INSERT INTO test VALUES (1, 6), (2,3), (3, 2), (4, 7), (5, 8), (6, 1);

First query:

SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
COUNT(*)
FROM test
GROUP BY 1, 2

Result:

mn_c    mx_c    count
   2       3        2
   4       7        1
   1       6        2
   5       8        1

Then:

SELECT COUNT(cnt1) AS matched_count,
       ROUND(COUNT(cnt1)/(SELECT COUNT(*) FROM test)::FLOAT * 100) AS percentage
FROM
(
  SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
  COUNT(*) AS cnt1
  FROM test
  GROUP BY 1, 2
  HAVING COUNT(*) > 1
) AS t

Result:

matched_count   percentage
            2           33

A version of the above code should work for most servers - see here for a discussion of the LEAST and GREATEST functions in other servers - works pretty much for all of them except MS SQL Server.

p.s. have you considered what happens if you have duplicate columns as follows?

INSERT INTO test VALUES (1, 6), (2,3), (3, 2), (4, 7), (5, 8), (6, 1), (5,5), (5,5)

Note the duplication of a duplication - (5,5) - see here for the differences which emerge between my approach and that of @McNets. My solution says that there are 3/8 matched pairs, but McNets says different. Not sure if I completely understand what his SQL is doing?

Anyway, interesting question (+1) - why do you want to do this? p.s. welcome to the forum! :-)


How to force higher values of open_files_limit or table_open_cache?

From the looks of your numbers, you must have hit some bug or pre-GA behavior. Why?
According to the MySQL Documentation, the default for table_open_cache is -1 for GA releases of MySQL 5.6. This tells mysqld pick a fair starting value give current OS conditions. What makes that value weird is the fact that 400 is the default for MySQL 5.6.7. The first GA release is 5.6.8. It may be a coincidence, but my guess would be that you set table_open_cache a little too high. Evidently, 5000 is a tolerable value for your DB Server.
SUGGESTION
Try experimenting with table_open_cache_instances setting it to 2 or 4. Then restart mysql and see.


Restrict access for Windows administrator in PostgreSQL

No, there is no reliable way to prevent the local administrator (or a domain admin with the ability to grant themselves local admin on a machine) from accessing the raw PostgreSQL data files, starting/stopping the server, changing the service account password, changing pg_hba.conf so they can log in to the server, etc.

As stated in the comments, if you don't trust someone don't give them local admin.

Sufficiently strict group policy could be used to make it a lot harder - but in the process would render the local admin account effectively useless. It would be more typical to create accounts that are "nearly admin", ie they have many rights granted to them but are not local admins and are also quite restricted by ACLs and group policy. This is fairly typical in a corporate SOE, but requires advanced Windows knowledge to set up and won't work on a stand-alone desktop.

If you're envisioning some scheme where you install PostgreSQL on a standalone Windows computer - say as part of some application you're distributing - and stop the person who owns the computer from accessing it then you're totally out of luck. No hope. You can slow the user down a bit if you use an embeddable database that supports whole-DB encryption like SQLcipher or SQLite see,  but even then a user with a clue and a debugger can still get in sooner or later, even if you try to add anti-debugger features to your application. PostgreSQL doesn't support in-application embedding or whole-database encryption, so if this is what you want you should look elsewhere.


SSIS database source imports as BLOB incorrectly

The table definition that it appears you let SSIS create defined a column as ntext. NTEXT, besides being deprecated, is a large object type so it's reasonable that when you extract data from that source, SSIS will simply call it BLOB because it doesn't matter to the data flow engine why it's large, it just is.

You can either fix your table to be a non LOB type and then update the loader process to account for it.

Approximately

ALTER TABLE dbo.MyTable ALTER COLUMN Foo nvarchar(4000);

Or you can perform an explicit cast when you extract data from the table to make it into a "regular" string.

Approximately

SELECT CAST(T.Foo AS nvarchar(4000)) AS Foo FROM dbo.MyTable AS T;


postgres create limited user that can select tables and columns of those tables

You can set column level privileges to very specifically (and narrowly) allow otherwise unprivileged users to access data.  In your case, it is two catalogs: pg_attribute and pg_type.

I have a user called alice who is similarly restricted as your user:

alice@test=> > SELECT * FROM pg_class LIMIT 1;
ERROR:  permission denied for relation pg_class

Issue the following GRANT statements:

GRANT SELECT (oid, typname) ON pg_type TO alice;
GRANT SELECT (attrelid, attname, atttypid, attnum) ON TABLE pg_attribute TO alice;

After these, alice can do the following, for example:

SELECT attname, typname
  FROM pg_attribute JOIN pg_type t ON t.oid = atttypid
 WHERE attrelid = 't2'::regclass AND attnum > 0;

 attname │ typname
─────────┼─────────
 key     │ jsonb
 value   │ jsonb

You can trivially extend the privileges to match your needs.


Entries with 2 variables. Do I need to make 2 more tables by entries?

Colin 't Hart is correct. Do not mix data in your table names.

Instead, create a Carrier table consisting of an identifier and the name of the carrier.

TABLE "Carrier"
ID  | Carrier
22  | Carrier1
31  | Carrier2

Reference the carrier in the weight price and shipping departure tables via the identifier as a FK. So in the case of the weight price table you may have many references to each carrier.

TABLE "WEIGHT-PRICE"
CarrierID | WEIGHT   | PRICE
22        |0-10 kg  | 10 €
22        |10-12 kg | 12 €
22        |12-16 kg | 14 €
31        |0-10 kg  | 9 €


Equivalent of MRG_MYISAM in databases other than MySQL?

From the description of the MERGE table type in the MySQL documentation at https://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html it appears this is similar to creating a view that uses a UNION statement to connect multiple tables, either from a single database or multiple databases.

In Microsoft SQL Server (which admittedly is not open-source), you could do something like this:

CREATE VIEW MergedTablesView
AS
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table1
    UNION
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table2
    UNION
    SELECT Field1, Field2, Field3, ...
    FROM Database1.dbo.Table3

    ...

The UNION statement will remove duplicate rows from the result set, which seems to be your primary requirement.

I'm certain other database engines, including PostgreSQL, also implement the UNION keyword in a very similar manner.  Here is the PostgreSQL Manual on the UNION statement.


This is the best documentation available for the query cache:

sql_cache.cc

I am not kidding, the header of that file contains the most detailed overview I have ever found. Next thing is the own code, which is on the very same link.

There is a query cache chapter on this book, and this other book, but only 4-5 paragraphs, in the context of its relation with the handlers interface.

Apart from that, you should read the general internals code guide.


You were getting close.  WIDTH_BUCKET does not allow a subset of the ranges to be specified, you must specify the start and end of the whole range and how many buckets you want that range divided into.  In the case of 1-100, 101-200, 201-300, 301-400, & 401-500 your start and end are 1 and 500 and this should be divided into five buckets.  This can be done as follows:

SELECT WIDTH_BUCKET (mycount, 1, 500, 5) Bucket FROM name_dupe;

Having the buckets we just need to count how many hits we have for each bucket using a group by.  This comes together with the above as follows:

SELECT Bucket*100 - 99 "Start", Bucket*100 "End", Count(Bucket) "Duplicates In Range"
FROM
(
   SELECT WIDTH_BUCKET (mycount, 1, 500, 5) Bucket FROM name_dupe
)
GROUP BY Bucket ORDER BY Bucket;

There are some tradeoffs for creating the name_dupe table.  To avoid them you could combine your first query with this one as follows:

SELECT Bucket*100 - 99 "Start", Bucket*100 "End", Count(Bucket) "Duplicates In Range"
FROM
(
    SELECT WIDTH_BUCKET (count(name), 1, 500, 5) Bucket FROM mynametable
    GROUP BY name
    HAVING count(name) > 1
)
GROUP BY Bucket ORDER BY Bucket;

Finally, here is another solution avoiding the use of WIDTH_BUCKET, which was apparently added in 9.2.

SELECT rownum*100 - 99 "Start", rownum*100 "End", d2 "Duplicates In Range" FROM
(
SELECT trunc((dupes+99)/100), count(trunc((dupes+99)/100)) d2 FROM
   (
      SELECT name, count(name) dupes FROM mynametable GROUP BY name
      HAVING count(name) > 1 ORDER BY 2
   )
GROUP BY trunc((dupes+99)/100) ORDER BY 1
);

Test Data:

--drop table mynametable;
create table mynametable as (
   select 'a' name from dual connect by level <= 1
   UNION ALL
   select 'b' name from dual connect by level <= 100
   UNION ALL
   select 'c' name from dual connect by level <= 101
   UNION ALL
   select 'd' name from dual connect by level <= 120
   UNION ALL
   select 'e' name from dual connect by level <= 200
   UNION ALL
   select 'f' name from dual connect by level <= 250
   UNION ALL
   select 'g' name from dual connect by level <= 300
   UNION ALL
   select 'h' name from dual connect by level <= 400
);

create table name_dupe as (
   select name,count(*) mycount from mynametable
   group by name having count(*) > 1
   );


How to get the Sum of all column values in the last row of a resultset without using UNION?

It really depends on your main goal - if it should look 100% like your result in the question above then I am sorry the answer is no.
If you want to get a result with the sum etc. yes there are ways round the union but you might not see the "Total" text.

Here is one solution idea (with less data and columns as I could not extract it from your question).

create table test (year integer, month integer, week integer, cost_carrer char(1),
                    wage_cost decimal(10,2), material_cost decimal (10,2), total_cost decimal(12,2));

insert into test values (2016, 1, 2, '0', 20000.00, 30000.00, 50000.00),
                        (2016, 1, 2, '1', 30000.00, 40000.00, 70000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00);

select year, month, week, cost_carrer, sum(wage_cost), sum(material_cost), sum(total_cost)
 from test
 group by grouping sets ((year, month, week,  cost_carrer), (year, month, week))
 order by year, month, week, cost_carrer

This will return:

YEAR MONTH WEEK COST_CARRER WAGE_COST MATERIAL_COST TOTAL_COST
 ---- ----- ---- ----------- --------- ------------- ----------
 2016     1    2 0            20000.00      30000.00   50000.00
 2016     1    2 1            30000.00      40000.00   70000.00
 2016     1    2 2            75000.00     105000.00  180000.00
 2016     1    2 NULL        125000.00     175000.00  300000.00

And I think this is the "idea" behind your question and example.

Other - even more powerful group by variantions are "group by rollup" and
"group by cube" which is described here in the documention (with examples)


How to find all records in MongoDB whose primary key is a user defined character string

You can only use the $regex operator to match values that are strings. As you've noted, an ObjectID is a 12-byte binary value stored as a specific BSON type.

You should instead use the $type operator to query based on the BSON data type of a field.

Example usage of $type:

// Find all restaurants where `_id` is not an ObjectId (type 7)
db.restaurants.find({_id: { $not: { $type: 7}} })

// Find all restaurants where `_id` is a string (type 2)
db.restaurants.find({_id: { $type: 2} })

Note: the BSON type is a numeric value, but MongoDB 3.2+ also has friendlier string aliases (i.e. the string "objectId" is equivalent to the numeric $type value of 7 in MongoDB 3.2).

If you already know the _id value you are searching for is a string, you also have the option of using the $regex operator to match a more specific pattern without specifying $type; the non-string values will be ignored by $regex.

For example:

// Find all restaurants with an `_id` starting with Happy (case-sensitive)
db.restaurants.find({_id: /^Happy/})

// Find all restaurants with an `_id` only using word characters (`\w` is `[A-Za-z0-9_]`)
db.restaurants.find({_id: /^[\w]+$/})

If you are running any of these queries regularly against a large collection (or have concerns on performance impact), you should review index usage via explain results. In particular, negated queries and $regex queries have some caveats on efficient index usage.


How to calculate required ressources from a baseline and what should be measured at all?

The questions of whether/how to directly measure CPU core usage etc. are beyond my understanding, but here's what I'd consider trying:

Run a standard profiler trace with database name added, during your normally busiest period. Total up the CPU column for the SQL:BatchCompleted and RPC:Completed events by database, and you'll get a rough idea of how much CPU resources (which may be spread across multiple cores) each database is consuming. (Perhaps also total up the CPU column for the other events to see if anything major was missed. And save the trace "as trace table" for analysis.)
Exactly how to translate that to how many cores you'll need, I can't say. But if you also measure the total system CPU usage during the profiler run you might be able to estimate against the specific database's ratio of the total.

Note: If your server takes less than a few hundred batch requests a second (see SSMS activity monitor), then a standard profiler trace even across the network will almost certainly not affect performance. And if you instead script a server-side trace then more requests a second can be handled without slowing anything, but I make no promises for your environment.

For RAM, I wonder if http://www.sqlshack.com/sql-server-memory-performance-metrics-part-4-buffer-cache-hit-ratio-page-life-expectancy/ might help you determine if your instances need less/more. I don't think there's any way to do this by database though.


exception: hosts cannot switch between localhost and hostname

You need to basically reset the config, which is stored in the local database, and then start from scratch.  Assuming you still just have the one server configured as above, restart the instance without the --replSet option, then drop the local.system.replset collection.  Once that is done you may restart again with --replSet and you can re-run rs.initiate() but this time pass in the configuration document instead of letting it auto-discover, something like this:

rs.initiate({"_id" : "my-rs0", "version" : 1, "members" : [{"_id" : 0, "host" : "my-server04:27017"}]})

You can also solve this by making sure that the name resolves correctly and so is automatically populated by the default rs.initiate() command, but the above removes any doubts.


Same query, different result (depending on user's permissions)

If two users execute the same sql statement, is it possible that user1 gets different results than user2?

Yes, absolutely.

Row-Level Access Control allows the Database Administrator to put "contextual" filters on a table so that different users really do see different data.

For example, a Users table might be protected such that users in the Personnel department can see everyone but anyone in any other department can only see people in the same department.


Corrupt date fields causing query failure in Oracle

The date looks fine:

SQL> CREATE TABLE testdate (d date);
Table created
SQL> INSERT INTO testdate
  2     VALUES (to_date(to_char((120-100)*100+107-100)||'-12-31','yyyy-mm-dd'));
1 row inserted
SQL> SELECT DUMP(d), d FROM testdate;
DUMP(D)                                                                          D
-------------------------------------------------------------------------------- -----------
Typ=12 Len=7: 120,107,12,31,1,1,1                                                31/12/2007

Are you able to use the specific conversion function TO_CHAR on those lines? For instance SELECT to_char(confidential_until, 'yyyy-mm-dd') FROM mytable WHERE id = :x


SQL query to return a columns with given range

Your best bet is probably to use dynamic SQL to pivot your data. Here try this out.

IF OBJECT_ID('tempdb..#tblItems') IS NOT NULL
    DROP TABLE #tblItems;
IF OBJECT_ID('tempdb..##tblItemsTotal') IS NOT NULL
    DROP TABLE ##tblItemsTotal;

CREATE TABLE #tblItems
(
    UniqueCode CHAR(4),
    [Description] VARCHAR(100),
    [Date] Date,
    Amount Decimal(18,3)
);

INSERT INTO #tblItems
VALUES  ('ABCD','Item Description 1','01/03/2014',2068),
        ('PQRS','Item Description 2','01/05/2014',412.104),
        ('PQRS','Item Description 2','01/07/2014',635.448),
        ('UVWX','Item Description 3','01/05/2014',214.52),
        ('UVWX','Item Description 3','01/07/2014',827.2);

--Now that I've set up the problem, here's for the actual code

--Declare your date range and variables to hold column lists
DECLARE @startDate DATE = '01/03/2014',
        @endDate DATE = '01/07/2014',
        @SelectCols VARCHAR(MAX),
        @PivotCols VARCHAR(MAX);

--Caluclate column lists
SELECT  @SelectCols =  COALESCE(@SelectCols + ',','') + 'ISNULL(pvt.' + QUOTENAME(CAST([Date] AS VARCHAR(25))) + ',0) AS ' + QUOTENAME(CAST([Date] AS VARCHAR(25))),
        @PivotCols =  COALESCE(@PivotCols + ',','') + QUOTENAME(CAST([Date] AS VARCHAR(10)))
FROM    #tblItems
WHERE   [Date] BETWEEN @startDate AND @endDate
GROUP BY [Date];

--Calculate your total within data range
SELECT  UniqueCode,
        [Description],
        SUM(amount) Total INTO ##tblItemsTotal
FROM #tblItems
WHERE [Date] BETWEEN @startDate AND @endDate
GROUP BY UniqueCode,[Description];

--Use dynamic SQL to pivot your data and join to ##tblItemsTotal for the Total column
EXEC
(
    'SELECT pvt.UniqueCode,
            pvt.[Description],' +
            @selectCols +
            ',B.Total
     FROM #tblItems A
     PIVOT
     (
        SUM(Amount) FOR [Date] IN (' + @PivotCols + ')
     ) pvt
     INNER JOIN ##tblItemsTotal B
     ON pvt.UniqueCode = B.UniqueCode'
)


order by case with multiple order criteria

Case is supposed to return one value, not a tuple. Beside, you should compare 1 with something. If you want conditional ordering you can do that with two case statements:

ORDER BY CASE WHEN x = 1 THEN FirstName ELSE GivenName END
       , CASE WHEN x = 1 THEN GivenName ELSE FirstName END


Mixture of byte and character semantics in Oracle DB

Length Semantics is an option to define the SQL character types in terms of either bytes or characters. Before Oracle 9i we had only one option that is byte semantic, we used to define CHAR, VARCHAR, CLOB etc in terms of bytes. This is fine for single-byte character sets where one character is equal to one byte. But in multi-byte character sets this could be a problem because a character that you wanted to store could be larger than a byte.
For example: If you defined a column as VARCHAR2(10) and the character is multi-byte(Chinese characters for example) then it may take more than 10 bytes to store 10 characters.
After Oracle 9i we have two option we can now define SQL characters either in Byte or in Character length semantics. Byte is default length semantic.

Is there any issues if a table having mixture of byte or character semantics in a table?

The main reason to specifying the length semantics first it properly documents the expected semantics in DDL statements and second it makes the statements independent of execution environment.
If you define the column as CHAR length semantic then a single character might take a different number of bytes to store, the width of the character in bytes is variable.
If you define the column as BYTE length semantic then it takes only one byte for one character and if you try to store a character which is multi-byte then you will get an error.

Any implication in terms of performance?

No, AFAIK. Because length semantics has nothing to do with how the data is interpreted.

Caution:
Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length >semantics, resulting in runtime errors, including buffer overflows.

Reference: Length Semantics


Do MySQL limitations apply to MariaDB as well on AWS RDS

Short answer:  Probably yes.
Long answer:
I reviewed that link.  All three (MySQL, MariaDB, RDS) are derived from the same original code.  As far as I know, all three have, or at least had all of those bugs/issues/etc.  When one of the 3 fixes a bug, the others may eventually pick up the fix.
For innodb_buffer_pool_instances, MariaDB seems to be leading the change:  MariaDB-10.5.1 changelog says that innodb_buffer_pool_instances deprecated.
WHERE indexed_col1 = 'value1' AND indexed_col2 = 'value2' -- This is almost certainly fixed by using a composite index rather than depending on index merge.
The 10x for blobs was a workaround that was mentioned in 5.6(?).  It may be relaxed in some later version.
lower_case_table_names is a kludge to porting to both Windows and Unix.  It got worse with 8.0's Data Dictionary.  So, beware of switching OS's
As for the 16TB limit for an InnoDB table, I thought it was 32TB or 64TB.  It won't be long before someone finds out for sure.
Lower limits such as 2 TB, and long ago, 2 GB are probably dictated by the underlying Filesystem on the Operating System.
long_query_time allowed microseconds long ago, in 5.1.21, before MariaDB forked off.  I don't understand why AWS mentions it.
My list of limits:
http://mysql.rjweb.org/doc.php/limits


You can simply add an "is_student" bit column to the relation "UserToFOS", and create a unique key on user_id, fos_id and is_student.


remote postgresql 9.3 login using pgadmin3 and a public network server to connect to LAN-only machine

changing

host    all        all        192.168.59.1/24       trust

to

host    all        all        samenet       trust

did the trick for me. Though the difference between the two stays a secret for me.


You need to use the --base64-output=decode-rows and --verbose options with mysqlbinlog or you will not always see everything that's in the log.

You should also consider removing binlog_ignore_db from your configuration, because this not only prevents logging changes to the mysql schema, it also stops logging of events from your session that reference other schemas any time you USE mysql until you USE another database because of the way replication rules are interpreted.


MySQL is trimming my numbers and I can't figure out why

Sounds like your PHP code is converting the string to a integer and chopping off the decimal place. Possibly in the prepare statement. You could try binding the param as a string, or possibly a cast as a (float).

Example PHP:

  $number='2.90';
  echo (int)$number;

Will output 2

$number='2.90';
echo (float)$number;

Will output 2.9

I would just leave it as a %s string.


Best Data Type to Store Result of HASHBYTES('MD5', ...)

According to Books Online:

  The output conforms to the algorithm standard: 128 bits (16 bytes) for
  MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32
  bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512.

So despite the fact that the function technically returns varbinary(8000), you'll only ever get 16 bytes with MD5. If you are completely certain you only need MD5, then a binary(16) will do it. If you want to play it safe, and allow any possible algorithm (current or future) that HASHBYTES can handle, go with varbinary(8000).

I definitely wouldn't recommend a char type, since that will involve string conversion overhead, and unnecessary storage overhead.


MySQL: Using DISTINCT and GROUP BY together?

ad 1) Old mysql databases and when you disable ONLY_FULL_GROUP_BY , you can make this query and if the post_content are all equal you would notice, that mysql delivers a random not deterministic value back.

ad 2) none what so ever

ad 3) lazy programming and it occurs an error when you enable ONLY_FULL_GROUP_BY

ad 4) No, it would display all post_content that are connected to user_id, post_id similar to addind post_content to the Group by

Like Strawberry already said this query doesn't make any sense


pg_dump -> import traject using "upsert" paradigm?

I'd simply restore the table in another schema and forge my upsert command to insert/update B data from A data.
If you're using the plain text format using pg_dump, you'll be able to change the search_path at the beginning of the file to create your new table on the new schema.
If you're using a custom format, you can either get the SQL format using pg_restore and change it or use the -l flag to get the list file and edit it accordingly.
Have a nice day


Do database engines update when the updated value is the same?

You should assume the UPDATE is always writing the new value, even when identical. Write the query to eliminate no-ops with an appropriate WHERE clause. Approach it as a large table update, ie. do it in batches and make it idempotent. Is the only safe approach that is not victimized by assumptions and implementation specific details.

Case in point if you test prior to SQL Server 2012 you may conclude that an update that generates an identical in-place row image does not update the page and generates no log, while SQL 2012 under identical conditions you may conclude that it does not update the page but does generate log.


SQL Server: Can Page Verify be set to Checksum on any database, or must I check compatibility?

Update. I can see my answer being a bit unclear updated it to make it more clear

*Short Answer: No *

You don't need to check compatibility, yes it is something that can be applied to any database. It will be compatible, even if it is an 80(SQL Server 2000) compatibility mode database, you can enable Checksum, and likely should.

This is transparent in that your tables don't look or act differently, your apps don't know about it, etc. As for the "database knowing" it depends on how you mean it. From the app connecting? No. But the SQL Server storage engine that interacts with the files knows it, so it does the checks on write, but again that is transparent to your app and users.

One more consideration:

You are correct, only pages changed after it takes effect is a good thing to keep in mind. Doing index rebuilds at some point after doing this is one good way to touch a whole lot of pages, and not a bad idea.

And watch performance/CPU overhead. There is a "cost" associated with Checksum, though that cost isn't as high as you might fear. This article, though written for 2005, talks about some of those concerns, but again it is rare to have an issue from this there.


MySQL search function join on multiple conditions

At the moment you're searching for e.g. "Barney Likert" (the whole string) in both the studies and the elements columns you've specified.

You need to either have two search strings, one for searching studies, and one for searching elements. Or if your users can't be bothered to use two separate fields, then you could parse the string before it's sent to the database, split it up if it consists of two words, and execute a particular query depending on what the search string is. E.g. you could have one query for input of one word, and another query for input of two words.

You may also want to consider changing the JOIN to LEFT OUTER JOIN, otherwise you will only get results from studies that have at least one element. If you know that all studies have at least one element, then you can keep it as is, as this type of join is faster.


This is rather tricky to do in MySQL compared to other DBMS:s. One way to try to overcome the limitations in MySQL, is to use user-defined variables. I must admit I find them almost counter-intuitive to use, so there may be errors in here:

update article a
join (
    select case when @category <> category_id
                then @rownum := 1
                else @rownum := @rownum+1
           end rownum, id
         , case when @category <> category_id
                then @category := category_id
           end
    from article b
    cross join (select @category := -1) c
    order by b.category_id, b.id
) as r
    on r.id = a.id
set a.sort_order = r.rownum;

The idea here is that we traverse category according to order by b.category_id, b.id. We initialize @category to a category that does not exist (select @category := -1) c. Every time that category changes, we reset rownum to 1, if category is unchanged we increase rownum. The result:

http://sqlfiddle.com/#!9/aa754b/1/0

I would probably look for other solutions if I were you. Ideas you may want to try is to use a cursor, I would even consider doing the update in a host language a viable option.


Efficient way to join two tables with one to many relationship

Most effective way is to use an INNER JOIN, like this:

SELECT   *
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId

And when you need aggregates on child level, you can add a GROUP BY and SUM and/or AVG or other aggregate functions:

SELECT      P.Name
            , P.Address
            , SUM(C.Amount)
FROM        Parent P
INNER JOIN  Child  C
    ON      C.ParentId = P.ParentId
GROUP BY    P.Name
            , P.Address


Will SQL charset wildcard query work on Oracle database?

Use regexp_like:

select ename
from admin_emp
where regexp_like(ename,'(^[sm])');

Example SQL Fiddle here.


Text Storage and Database Design Optimization in a SQL Server Database

If all apples are green and all bananas are yellow a Fruit table (ID int, Name varchar(50), colour varchar(50)) would be appropriate, with your data table having a foreign key to it.

If you have yellow apples and orange bananas (yea for genetic engineering!), but only certain combinations are permitted you will need FruitRainbow(ID int, FruitID, ColourID) with the latter two as FKs to your option 2 tables above and your data table having a FK to FruitRainbow.

If any fruit can occur in any colour, and you don't want to limit those combinations in advance, your option 2 is fine.

If your query is really about resource optimisation rather than relational integrity then you'll have to decide what you want to gain and what you're willing to trade to get it.  By using integer FKs instead of natural name you get a smaller disk footprint at the cost of runtime load.  There are no free lunches. Pick your problem and solve it in the knowledge of the compromises it will entail.


ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table '' contains primary key for constraint ''

Correct. You can't do a switch out if there's another table which is dependent on the switching table with a FK.

Imagine that you have PK values 1,2,3, and a foreign key into it. Now you want to switch out value 3, so it'll have to check that 3 isn't mentioned in the FK table - that's a large amount of work, and a lot more than the metadata operation that partition switching is meant to be.

...so it's not allowed. Do it without FKs.


How to properly filter columns after applying aggregate function

I think you can achieve this result without so many subqueries:

Select State
  From Customer
  Group By State
  Order By Count(*) Desc
  Limit 1;


Does dropping a partition and running update indexes stop DML execution on the whole table?

As you know, Partition solution announced to manage heavy data without blocking or high IO operation.
There are various scenarios, I'm going to explain you:

1- Clear data from non-active partitions
First you can clear data with Switch out operation, then merge your specific partition, with this, you will drop a partition without any blocking.
You don't need to refresh your Indexes with this solution.
Notice that, if your partition is Active, you have to wait to other session finished first(It's blocking). Actually you've not dropped active partitions.

2- Drop table partitioning
with this operation, you have to recreate table, then you need to recreate your indexes too. But for other situation you don't need to rebuild, reorganize your indexes.
Tip : You have to maintenance data Fragmentation of active partitions, in regular schedule.


Timezone is a startup variable, you need to restart the pgbouncer service. pgbouncer.github.io/config.html

Wiki answer originally a comment by 3manuek


SSDT Schema Compare fails when comparing my DB Project to my Azure Server

TL;DR

The query Visual Studio is executing is being blocked and the query times out.

Turns out I've managed to work this out for our situation: timing out due to blocking!

I ran a profiler trace with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events.

It would reach a certain query and then stop, when VS would claim that the comparison was complete.

Here's the batch of queries in question:

select object_id as id,
    null as id2,
    case
        when [type] != 'D ' then [type]
        when parent_object_id != 0 then 'D '
        else 'LD'
    end as [type],
    schema_name(schema_id) as name1,
    name as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.objects
where
    type != 'S'
    and is_ms_shipped != 1

union

select principal_id as id,
    null as id2,
    type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.database_principals

union

select assembly_id as id,
    null as id2,
    'CLR' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    modify_date as mod
from sys.assemblies
where
    is_user_defined =1

union

select schema_id as id,
    null as id2,
    'SCH' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    null as mod
from sys.schemas

union

select user_type_id as id,
    null as id2,
    case
        when is_assembly_type = 1 then 'UDT'
        when is_table_type = 1 then 'TT'
        else 'UDDT'
    end as [type],
    schema_name(schema_id) as name1,
    name as name2,
    null as name3,
    null as a1,
    binary_checksum(system_type_id, schema_id, max_length, precision, scale, is_nullable, is_assembly_type, default_object_id, rule_object_id) as v1,
    null as mod
from sys.types
where
    is_user_defined = 1

union

select idx.object_id as id,
    idx.index_id as id2,
    case
        when idx.type = 3 then 'XIX'
        when idx.type = 4 then 'SIX'
        when idx.type = 6 then 'CIX'
        else 'IDX'
    end as [type],
    schema_name(tab.schema_id) as name,
    tab.name as name2,
    idx.name as name3,
    null as a1,
    binary_checksum(is_unique, data_space_id, ignore_dup_key, is_primary_key, is_unique_constraint, fill_factor, is_padded, is_disabled, allow_row_locks, allow_page_locks) as v1,
    null as mod
from sys.indexes idx
    inner join sys.tables tab on idx.object_id = tab.object_id
where
    idx.is_primary_key = 0
    and idx.name is not null
    and idx.is_unique_constraint = 0
    and idx.is_hypothetical = 0

union

select stat.object_id as id,
    stat.stats_id as id2,
    'STAT' as [type],
    schema_name(tab.schema_id) as name,
    tab.name as name2,
    stat.name as name3,
    null as a1,
    no_recompute as v1,
    null as mod
from sys.stats stat
    inner join sys.tables tab on stat.object_id = tab.object_id
where
    stat.user_created = 1

union

select xml_collection_id as id,
    null as id2,
    'XSC' as type,
    schema_name(schema_id),
    name as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.xml_schema_collections
where xml_collection_id > 1

union

select
    object_id id,
    null id2,
    'DDT' as type,
    name,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.triggers
where
    parent_class = 0

union

select
    database_specification_id id,
    null as id2,
    'DAS' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.database_audit_specifications

union

select
    certificate_id id,
    null as id2,
    'CERT' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(pvt_key_encryption_type, thumbprint) as v1,
    null as mod
from sys.certificates

union

select
    asymmetric_key_id id,
    null as id2,
    'ASMK' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    null as mod
from sys.asymmetric_keys

union

select
    symmetric_key_id id,
    null as id2,
    case
        when name = N'##MS_DatabaseMasterKey##' then 'MK'
        else 'SYMK'
    end as type,
    case
        when name = N'##MS_DatabaseMasterKey##' then null
        else name
    end as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    modify_date as mod
from sys.symmetric_keys
where
    name = N'##MS_DatabaseMasterKey##'
    or name not like N'##%'

union

select
    fulltext_catalog_id id,
    null as id2,
    'FTC' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(is_default, is_accent_sensitivity_on) as v1,
    null as mod
from sys.fulltext_catalogs

union

select
    ft.object_id id,
    ft.unique_index_id as id2,
    'FTI' as type,
    schema_name(o.schema_id) as name1,
    o.name as name2,
    null as name3,
    null as a1,
    binary_checksum(is_enabled, change_tracking_state) as v1,
    null as mod
from sys.fulltext_indexes ft
inner join sys.objects o on ft.object_id = o.object_id

union

select
    stoplist_id id,
    null as id2,
    'FTSL' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    modify_date as mod
from sys.fulltext_stoplists

union

select
    data_space_id id,
    null as id2,
    [type] as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    is_default as v1,
    null as mod
from sys.data_spaces

union

select
    function_id id,
    null as id2,
    'PF' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.partition_functions

union

select
    file_id id,
    null as id2,
    'FILE' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    binary_checksum(type, data_space_id, physical_name, state, growth, is_read_only, is_sparse, is_percent_growth) as v1,
    null as mod
from sys.database_files

union

select
    message_type_id id,
    null as id2,
    'MT' as [type],
    name collate database_default as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(validation, xml_collection_id) as v1,
    null as mod
from sys.service_message_types

union

select
    service_contract_id id,
    null as id2,
    'SC' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    null as v1,
    null as mod
from sys.service_contracts

union

select
    service_id id,
    null as id2,
    'SERVICE' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(service_queue_id) as v1,
    null as mod
from sys.services

union

select
    route_id id,
    null as id2,
    'ROUTE' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(remote_service_name, broker_instance, lifetime, address, mirror_address) as v1,
    null as mod
from sys.routes

union

select
    remote_service_binding_id id,
    null as id2,
    'RSB' as type,
    name as name1,
    null as name2,
    null as name3,
    principal_id as a1,
    binary_checksum(service_contract_id, remote_principal_id, is_anonymous_on) as v1,
    null as mod
from sys.remote_service_bindings

union

select
    object_id id,
    null as id2,
    'DEN' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.event_notifications

union

select
    priority_id id,
    null as id2,
    'SBP' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    binary_checksum(service_contract_id, local_service_id, remote_service_name, priority) as v1,
    null as mod
from sys.conversation_priorities

union

select
    principal_id id,
    null as id2,
    'LOG' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_principals
where
    type != 'R'

union

select
    audit_id id,
    null as id2,
    'SAD' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_audits

union

select
    credential_id id,
    null as id2,
    'CRD' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.credentials

union

select
    msg.message_id id,
    msg.language_id as id2,
    'SEM' as type,
    cast(msg.message_id as sysname) as name1,
    lang.alias collate database_default as name2,
    null as name3,
    null as a1,
    binary_checksum(msg.severity, msg.is_event_logged, msg.text) as v1,
    null as mod
from sys.messages msg inner join sys.syslanguages lang on (msg.language_id = lang.lcid)
where
    msg.message_id > 50000

union

select
    base.endpoint_id id,
    null as id2,
    'SEP' as type,
    base.name as name1,
    null as name2,
    null as name3,
    base.principal_id as a1,
    binary_checksum(*) as v1,
    null as mod
from sys.endpoints base
    left outer join sys.http_endpoints http on (base.endpoint_id = http.endpoint_id)
    left outer join sys.service_broker_endpoints sb on (base.endpoint_id = sb.endpoint_id)
    left outer join sys.soap_endpoints soap on (base.endpoint_id = soap.endpoint_id)
    left outer join sys.tcp_endpoints tcp on (base.endpoint_id = tcp.endpoint_id)
    left outer join sys.database_mirroring_endpoints mirror on (base.endpoint_id = mirror.endpoint_id)

union

select
    server_id id,
    null as id2,
    'SLS' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.servers

union

select
    logins.server_id id,
    logins.local_principal_id as id2,
    'SLL' as type,
    srvs.name as name1,
    lp.name as name2,
    null as name3,
    null as a1,
    null as v1,
    logins.modify_date as mod
from sys.linked_logins logins
    inner join sys.servers srvs on (logins.server_id = srvs.server_id)
    left join sys.server_principals lp on (logins.local_principal_id = lp.principal_id)

union

select
    object_id id,
    null as id2,
    'SST' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_triggers
where
    is_ms_shipped = 0

union

select
    server_specification_id id,
    null as id2,
    'SAS' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    modify_date as mod
from sys.server_audit_specifications

union

select
    event_session_id id,
    null as id2,
    'SES' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    null as v1,
    null as mod
from sys.server_event_sessions

union

select
    provider_id id,
    null as id2,
    'SCP' as type,
    name as name1,
    null as name2,
    null as name3,
    null as a1,
    binary_checksum(guid, version, dll_path, is_enabled) as v1,
    null as mod
from sys.cryptographic_providers

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    binary_checksum(event_id, name, package, module) as minorid,
    name as name,
    null as id1,
    null as id2
from sys.server_event_session_actions

union

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    event_id as minorid,
    name as name,
    binary_checksum(predicate) as id1,
    null as id2
from sys.server_event_session_events

union

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    object_id as minorid,
    name as name,
    binary_checksum(value) as id1,
    null as id2
from sys.server_event_session_fields

union

select
    cast(32 as tinyint) as class,
    'SES' as type,
    event_session_id as majorid,
    event_id as minorid,
    name as name,
    null as id1,
    null as id2
from sys.server_event_session_events

union

select
    perms.class as class,
    objectpropertyex(perms.major_id, 'BaseType') as type,
    perms.major_id as majorid,
    perms.minor_id as minorid,
    perms.type + perms.state collate database_default as name,
    perms.grantee_principal_id as id1,
    perms.grantor_principal_id as id2
from sys.database_permissions perms
where
    perms.major_id >= 0

union

select
    props.class as class,
    case
        when objs.parent_object_id > 0 then objectpropertyex(objs.parent_object_id, 'BaseType')
        else objectpropertyex(props.major_id, 'BaseType')
    end as type,
    case
        when objs.parent_object_id > 0 then objs.parent_object_id
        else props.major_id
    end as majorid,
    props.minor_id as minorid,
    props.name as name,
    binary_checksum(props.value) as id1,
    null as id2
from sys.extended_properties props left outer join sys.objects objs on props.major_id = objs.object_id

union

select
    cast(4 as tinyint) as class,
    'R' as type,
    roles.role_principal_id as majorid,
    roles.member_principal_id as minorid,
    'drm' as name,
    null as id1,
    null as id2
from sys.database_role_members roles

union

select
    cast(6 as tinyint) as class,
    'TT' as type,
    tt.user_type_id as majorid,
    cols.column_id as minorid,
    cols.name as name,
    binary_checksum(cols.user_type_id, cols.max_length, cols.precision, cols.scale,
        cols.collation_name, cols.is_nullable, cols.is_ansi_padded, cols.is_rowguidcol, cols.is_identity,
        cols.is_computed, cols.is_xml_document, cols.xml_collection_id, cols.default_object_id, cols.rule_object_id) as id1,
    null as id2
from sys.table_types tt inner join sys.columns cols on (tt.type_table_object_id = cols.object_id)

select
    cols.object_id as tableid,
    cols.column_id as columnid,
    binary_checksum(cols.name) as checksum
from sys.tables tabs
    inner join sys.columns cols  on (tabs.object_id = cols.object_id)
    inner join sys.index_columns idx_cols on (idx_cols.object_id = tabs.object_id and idx_cols.column_id = cols.column_id)
where
    tabs.is_ms_shipped = 0

union

select
    object_id as tableid,
    column_id as columnid,
    binary_checksum(type_column_id, language_id) as checksum
from sys.fulltext_index_columns

Running the query in SSMS would never finish executing on the offending server but would take less than a second on every other environment.

So I started to pull it apart and found that the queries that are reading from the following 3 system views were the problem:

sys.server_event_session_actions
sys.server_event_session_events
sys.server_event_session_fields

The first of these system views only contained 28 records (found by running a Count(*)), but wouldn't return the data unless I used the (NOLOCK) hint... [b]locking problem!

Finding out the head of the blocking chain was easy enough but it was not killable as it was an Extended Events session. There's a fix for this issue here: https://support.microsoft.com/en-gb/kb/2511963

But hopefully in your case you can just kill off the head of the chain nicely and return things to normal!

EDIT: Having now had a viable maintenance window for the affected SQL Server, a restart has cleared the SPID and now compares are working once more!

Also, here's a quick script I wrote to find the head of the blocking chain(s) that might be helpful for future readers: http://gvee.co.uk/files/sql/admin.blocking_chains.sql

IF Object_ID('admin.blocking_chains', 'V') IS NULL
  BEGIN
    EXEC ('CREATE VIEW admin.blocking_chains AS SELECT NULL As buffer;');
  END
;
GO

ALTER VIEW admin.blocking_chains
  AS
WITH processes AS (
  SELECT s.spid
       , s.blocked As blocking_spid
       , DB_Name(s.dbid) As database_name
       , s.program_name
       , Cast(x.text As varchar(Max)) As definition
       , hostname As host_name
       , loginame As login_name
       , nt_domain
       , nt_username
  FROM   sys.sysprocesses (NOLOCK) As s
   CROSS
    APPLY sys.dm_exec_sql_text(s.sql_handle) As x
  WHERE s.spid > 50
)
, blocking (spid, blocking_spid, blocking_statement, sequence, level, database_name) AS (
  SELECT s.spid
       , s.blocking_spid
       , s.definition
       , Row_Number() OVER (ORDER BY s.spid) As sequence
       , 0 As level
       , s.database_name
  FROM   processes As s
   INNER
    JOIN processes As s1
      ON s1.blocking_spid = s.spid
  WHERE  s.blocking_spid = 0

    UNION ALL

      SELECT r.spid
           , r.blocking_spid
           , r.definition
           , d.sequence
           , d.level + 1
           , r.database_name
      FROM   processes As r
       INNER
        JOIN blocking As d
          ON d.spid = r.blocking_spid
      WHERE  r.blocking_spid > 0
)
, results AS (
  SELECT blocking_spid
       , spid
       , blocking_statement
       , level
       , Min(sequence) As sequence
       , Min(database_name) As database_name
  FROM   blocking
  GROUP
      BY blocking_spid
       , spid
       , blocking_statement
       , level
)
SELECT Cast(CASE WHEN results.blocking_spid = 0 THEN 1 ELSE 0 END As bit) As is_head_of_chain
     , results.sequence
     , results.level
     , results.database_name
     , results.spid
     , results.blocking_spid
     , results.blocking_statement
     , processes.host_name
     , processes.program_name
     , processes.login_name
     , processes.nt_domain
     , processes.nt_username
     , Right('0000' + Cast(results.sequence As varchar(11)), 5) + '-' + Right('0000' + Cast(results.level As varchar(11)), 5) As sort_order
FROM   results
 LEFT
  JOIN processes
    ON processes.spid = results.spid
;


Replace Character Without Using Looped REPLACE Function

Create a mapping between the old value and the new value in a table value constructor and use stuff to replace the character.

declare @T table(Value varchar(10));

insert into @T values
('Q234567888'),
('R264747848'),
('B264712481');

select stuff(T.Value, 1, 1, (
                            select V.NewValue
                            from (values('Q', 'I'),
                                        ('R', 'I'),
                                        ('B', 'U')) as V(OldValue, NewValue)
                            where V.OldValue = left(T.Value, 1)
                            ))
from @T as T;


Types can be altered, granted/revoked privileges on, so why not?

Example:

SQL> select object_type from dba_objects where object_name = 'T1';

no rows selected

SQL> create type t1 is table of varchar2(20);
  2  /

Type created.

SQL> audit all on t1;

Audit succeeded.

SQL> select * from dba_obj_audit_opts where object_name = 'T1';

OWNER                OBJECT_NAME          OBJECT_TYPE             ALT       AUD       COM       DEL       GRA       IND       INS       LOC       REN       SEL       UPD       REF EXE       CRE       REA       WRI       FBK
-------------------- -------------------- ----------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- --------- --------- --------- --------- ---------
BP                   T1                   TYPE                    A/A       A/A       -/-       -/-       A/A       -/-       -/-       -/-       -/-       -/-       -/-       -/- A/A       A/A       -/-       -/-       -/-

See DBA_OBJ_AUDIT_OPTS and AUDIT for details.


Your SELECT statement is referencing the name of the index instead of the indexed field.

Use this instead:

select *
from data
where time = '2013-10-01 17:18:06'


mysqldump select queries appearing in slow query log after upgrade

By default, MariaDB's mysqldump will log queries (--log-queries enabled by default) to the slow log when executing the reload of a dump. The MariaDB doc says:

When restoring the dump, the server will, if logging is turned on, log the queries to the general and slow query log. Defaults to on; use --skip-log-queries to disable. Added in MariaDB 10.1.1.

This was introduced back in MariaDB 10.1.1. You could try skip-log-queries as an option for mysqldump.
This may or may not affect the dump process itself.
If you are seeing SELECTs from the mysqldump in the slow log, the table being dumped might be waiting for a lock to release (in the case of a MyISAM table) or an table with a lot of transactional activity (in the case of InnoDB) when generating the INSERT INTO for thousands of rows.
SUGGESTION
You could initialize the reload session to ignore the slow query log by hiking the long_query_time for the local session:
mysql --init-command="SET long_query_time = 10000000;" < dump-file.sql


Tyring to get a better understanding of ORACLE admin accounts

Yes, there should be. Even the documentation mentions that:

Administrative User Accounts

  Create at least one additional administrative user and grant to that
  user an appropriate administrative role to use when performing daily
  administrative tasks. Do not use SYS and SYSTEM for these purposes.

But the database can not force you to do this. It is a discipline that some companies follow, while other companies ignore.


How do I track the usage of XML indexes?

I tried to reproduce what you're experiencing and couldn't make it happen, unfortunately (see the second part of this answer). If this was a bugfix between versions, it was pretty well undocumented: running this through Google produced absolutely nothing.

I suspect the problem is that the analysis query you're running is joining sys.dm_db_index_usage_stats to sys.tables. Sounds reasonable, except XML indexes don't have the same relationship to their base table as regular clustered and nonclustered indexes do.

The way XML indexes work, the indexes aren't created against the base table at all. Creating a primary XML index materializes (i.e., creates a clustered index of) the node table that gets generated from the XML data; secondary indexes are nonclustered indexes on the node table. Okay, fine, so what? The catch is that the materialized node table is an internal table, and does not appear in sys.tables (have a look in sys.internal_tables instead). So you can see why you won't get rows back for those indexes at all unless all the rows from the DMV are getting returned with an OUTER JOIN in the query (and even then, you won't directly get a name for the table in that row).

To be honest, I only recently learned how XML indexes work, so I actually have to go back and revisit my own index analysis scripts as well (I suspect they have this exact flaw). Here's a quick base script I whipped together that should correct the problem:

SELECT
    OBJECT_NAME(COALESCE(t.object_id, t2.object_id)) AS TableName,
    i.name AS IndexName,
    us.*
    FROM sys.dm_db_index_usage_stats us
    INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id
    LEFT OUTER JOIN sys.tables t ON t.object_id = us.object_id
    LEFT OUTER JOIN sys.internal_tables it ON it.object_id = us.object_id
    LEFT OUTER JOIN sys.tables t2 ON t2.object_id = it.parent_object_id

This was run against my local instance (64-bit Developer edition, 10.50.1617):

CREATE TABLE XmlIndexTest
(
    Id int IDENTITY(1, 1) NOT NULL,
    Data xml NOT NULL,

    CONSTRAINT PK_XmlIndexTest
        PRIMARY KEY CLUSTERED(Id)
)
GO

INSERT INTO XmlIndexTest(Data)
    VALUES('<Invoice><LineItem Id="1" /></Invoice>')

CREATE PRIMARY XML INDEX IX_PrimaryXml ON XmlIndexTest(Data)

SELECT *
    FROM XmlIndexTest
    WHERE Data.exist('/Invoice/LineItem[@Id = 1]') = 1

SELECT i.name, us.*
    FROM sys.dm_db_index_usage_stats us
    INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id

CREATE XML INDEX IX_SecondaryXml ON XmlIndexTest(Data)
    USING XML INDEX IX_PrimaryXml FOR PATH

SELECT *
    FROM XmlIndexTest
    WHERE Data.exist('/Invoice/LineItem[@Id = 1]') = 1

SELECT i.name, us.*
    FROM sys.dm_db_index_usage_stats us
    INNER JOIN sys.indexes i ON i.object_id = us.object_id AND i.index_id = us.index_id

and got these results:

Note how the object_id of the primary and secondary XML indexes is different than the object_id of the table's clustered index.


what is causing my windows page file to grow?

That's a good question. If you want to know what applications are causing this in a physical environment and even to a good extent a virtual environment then the following performance counters can be used:

Process()\Page File Bytes
Process()\Page File Bytes Peak

Additionally I'd capture:

Paging File\%Usage
Paging File\%Usage Peak

In a virtual environment the memory pressure is normally done within the guest os by a balloon driver which artificially inflates to grab back memory for other uses. This generally won't show as using paged memory in the file.

  The lock pages in memory setting has not been set up for any of the sql server services accounts and I think this might be related to the big size of the page file.

I get what you're saying, but it shouldn't have a bearing on the size of the page file. The system settings for page file have the bearing on the size of the page file. It may be just set to 32GB... who knows. Check out why it's set that way in the system properties.

  I am already seeing if I can add more memory to this box, but I would like to find out what is causing memory pressure (if anything inside sql server).

If your min/max memory settings are set properly then SQL Server shouldn't be the main cause of memory pressure according to Windows, especially since you said it's dedicated with nothing else running. It could be internal memory pressure, where clock hands ae sweeping caches (inside or outside hands) and that would be due to workload/amount of memory available. That should be investigated.

How do you know there is pressure? Could you update your post with what you've found?


Best Way to Migrate 100 tables To New Database

I would recommend backup and restore method.

If you are on sql server 2008R2 and up, then standard edition will allow you to compress the backup, so that you can transfer it to the new server relatively faster than using an uncompressed backup.

Once you restore the backup to the new server, just drop the tables / SPs / Views / Triggers, etc that you don't need.

After you drop the tables, just do a one time shrink (with Some Caveats) of your database to release space back to the OS. Note for when shrinking use DBCC SHRINKFILE (not DBCC SHRINKDATABASE)

Note: It would also be a good idea to "truncate" the tables (as @shanky pointed out) - if the tables are not having any FK relationship to other tables.


linked server connection error in Sql server

First, install the correct Oracle drivers.  You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment.  They are available here:
64-bit Oracle Data Access Components (ODAC) Downloads
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

One you download and unzip this into a folder run the following command from that folder:

C:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true

Then you need to add two folders to your system path:

c:\oracle\odac64 and c:\oracle\odac64\bin

Then you must reboot for the system path change to be visible by services like SQL Server.

After reboot you're ready to create and test the linked server.
First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

Then create the linked server definition.  Instead of a TNSNames alias, use an EZConnect identifier.  Here I'm specifying an IP address and a SID to connecto to an Oracle Express instance running on a VM:

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''
exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system',@rmtpassword='xxxxxx'

Now you're ready to test.  You configured the linked server for 'rpc out' so we can send a simple passthrough query to test connectivity:

exec ('select 1 a from dual') at MyOracle

That's it.


Fill percentage of total field conditional on some other field with MS Access SQL

Your last UPDATE statement should work. Access is often a bit crap though so although it should work, it probably won't. Thanks Microsoft Access dev team (probably more specifically the updates to JET).

Unfortunately, the only way I can recommend is to create a temp table based on a query which does a GROUP BY on the Date column you are attempting to aggregate. So create a query first like this:

SELECT MyTable.Date, Sum(MyTable.f) AS SumOfVal INTO temp_DateSums
FROM MyTable
GROUP BY MyTable.Date;

Then create another query which joins to this new table and performs its update based on it. This second query would look like:

UPDATE MyTable
INNER JOIN temp_DateSums ON MyTable.Date = temp_DateSums.DateDate
SET MyTable.F = [MyTable].[f]/[temp_DateSums].[SumOfVal];

This should update the column you need with the right value, albeit running 2 queries instead of one. I can't think of another way around it running a single query where you wouldn't get the dreaded "Operation must use an updatable query" option, that doesn't use VBA. Again, your DSUM should work, but DSUM doesn't seem to want to work the way it was designed anymore...


We've ended up writing our own application that manages the Table and Column renames


How can I copy specific tables into one SQL Server Table into a separate table automatically?

The instant answer that springs to mind is transactional replication. If it needs to be bi-directional, then peer-to-peer transactional replication.

If the latency here proves too high, or if the schemabinding is too different, then you could have a synchronous solution, with a linked server between the two DB servers and a trigger on the table which fires on insert/update/delete, that updates the table on the other server using the linked server. Warning here is that triggers can degrade performance a little.


different outcome based on function execution method

If you have a function that does an UPDATE and you try to call that from a SELECT statement, you should get an Oracle error saying that you cannot do DML inside a query.  So your SELECT statement should be throwing an error.  My first guess would be that you have an exception handler somewhere that, intentionally or not, is catching and discarding the error.  My next guess would be that the function was declared using an autonomous transaction pragma, which is almost certainly a bug because that would mean that the function was running in a separate transaction scope from the calling code and could not see uncommitted changes made by the caller (such as, for example, inserting the row that the function is trying to update).

In general, if you have a piece of code that is doing an update, that should be a stored procedure not a stored function.  That would mean that it cannot be called from SQL (just like a function that does DML cannot be called in a SQL statement).  But that is exactly what you want.  You don't want a function called in a SQL statement to have a side effect because you cannot control things like how many times the function is called.  Even in a SELECT from dual, the function could be called multiple times by Oracle (though that is unlikely).


Database design for smartphone product and its features

Based on your original design; you need a many-to-many relationship added to the ones you already have:

CREATE TABLE features_x_products
(
    product_id integer NOT NULL REFERENCES product (id),
    feature_id integer NOT NULL REFERENCES feature (id),
    PRIMARY KEY (product_id, feature_id),

    value varchar(200) NOT NULL,
    unit varchar(50)
) ;

This creates an EAV model (Entity - Attribute - Value). It's got many caveats. The one is mostly dislike is the fact that you have to store all values as text (or varchar), unless you have various value columns (i.e.: value_number decimal ; value_text varchar(200) ; value_timestamp timestamp ; ...), but then you'll also need some extra meta-information on your attributes to find out wich column to retrieve.

References and alternatives:

Aaron Bertrand: What is so bad about EAV, anyway?
Alternative to EAV for dynamic fields in a star schema data warehouse
Using MySql 5.7 JSON columns for EAV

Side Note: The concept of best design doesn't really exist. There are good solutions to specific problems. Some solutions are good for many different problems, others only a apply to very few ones. Depending on how you're going to query the database, whether you access the database using from one programming language or another, might make using JSON specially appealing, or completely awful. Same applies for EAV.


I think that the previously given query is closed to what you need:

select *
from daily_summary_202004 ds1
WHERE ds1.start_date >= date '2020-04-01'
  AND ds1.start_date < date '2020-05-01'
  AND ds1.active
  AND exists (select *
                  from daily_summary_202004 ds2
                  where ds1.vehicle_gid = ds2.vehicle_gid
                    and numrange(ds1.odo_start, ds1.odo_end) &&
                        numrange(ds2.odo_start, ds2.odo_end)
                    and ds1.id <> ds2.id
                    );

It selects the rows that cannot exist in the table with the given constraint. However it cannot give the one(s) that should be rejected: it gives only the candidates. You need to choose.

If I simulate with some data. I get:

select * from daily_summary_202004 ;
 id | vehicle_gid | odo_start | odo_end |     start_date      | active
----+-------------+-----------+---------+---------------------+--------
  3 |          10 |        10 |      20 | 2020-04-15 00:00:00 | t
  2 |          10 |         3 |       4 | 2020-04-15 00:00:00 | t
  1 |          10 |         2 |       6 | 2020-04-15 00:00:00 | t
(3 rows)

---
select *
from daily_summary_202004 ds1
WHERE ds1.start_date >= date '2020-04-01'
  AND ds1.start_date < date '2020-05-01'
  AND ds1.active
  AND exists (select *
                  from daily_summary_202004 ds2
                  where ds1.vehicle_gid = ds2.vehicle_gid
                    and numrange(ds1.odo_start, ds1.odo_end) && numrange(ds2.odo_start, ds2.odo_end)
                    and ds1.id <> ds2.id
                    );
 id | vehicle_gid | odo_start | odo_end |     start_date      | active
----+-------------+-----------+---------+---------------------+--------
  2 |          10 |         3 |       4 | 2020-04-15 00:00:00 | t
  1 |          10 |         2 |       6 | 2020-04-15 00:00:00 | t
(2 rows)

alter table only daily_summary_202004
add constraint odo_no_overlapping
exclude using gist
   (
   ((vehicle_gid)::text) WITH =,
     numrange(odo_start, odo_end) WITH &&)
     WHERE (start_date >= '2020-04-01'
      AND start_date < ' 2020-05-01'
    AND active
) ;
psql:tc.sql:39: ERROR:  could not create exclusion constraint "odo_no_overlapping"
DETAIL:  Key ((vehicle_gid::text), numrange(odo_start::numeric, odo_end::numeric))=(10, [3,4)) conflicts with key ((vehicle_gid::text), numrange(odo_start::numeric, odo_end::numeric))=(10, [2,6)).

Here PostgreSQL reports the involved rows but you also have to choose which one to keep and which one to reject.


MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP

According to the manual, a TIMESTAMP can be auto-initialized, auto-updated, both, or neither. The apparent caveat is this:

  With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

So, if you don't explicitly give the column a default/on update clause, it will do both. If you don't want to default or auto-update, give the default a '0':

ts TIMESTAMP DEFAULT 0


What is best way to handle transaction in sql server when inserting in 15 to 20 tables in one batch

Transaction size is unlikely to be the primary cause of import slowness. There are legitimate reasons to break up transactions, even within a single table or to import a whole set of tables in a single transaction as you do.

Also, for most requirements, large or small depends on the number of rows, not the number of tables.

Reasons for larger transactions:

Easier to keep data consistency.
Can be simpler to write and maintain.

Reasons for smaller transactions:

Usually a smaller transaction log is required.
Less blocking time. Other SQL processes can be blocked, waiting for the insert to finish.
Smaller chunks are easier for the replication log reader to handle.

But none of these have much to do with insert speed. Every row is going to have to be written to the data table and to the log, whether it is in a large transaction or small one. (with standard options)

I would first look for other causes of slowness.

Are there non-clustered indexes? They get updated with the insert and can slow things down.
Is the clustering key such that the data will get added to the end of the table? That's going to be faster than making inserts at random places. You can also create a clustered index on the temp tables to get SQL to import them in a favorable order.

There are more advanced options for performance, but they usually come with tradeoffs, So I'd check the basics first.


What encryption options are available in SQL server standard edition?

If you have SQL Server Standard edition then you do not have Encryption built into the product.  You have other options as the articles you reference mention, but they require work on your part to implement.

I think the confusion is where you are seeing that you can implement column/cell encryption using Enterprise edition OR through alternative methods.  Those alternative methods are:

Triggers and CLR functions to do the encryption/decryption for you.
Using the application layer to encrypt/decrypt the data on read and
write to/from the database.

The advantage to using TDE is that SQL understands the encryption and you can search on the encrypted data directly because it is only encrypted on disk.  But if you have encrypted the data using CLR or the application then you cannot do that and must decrypt every row.

Encryption is hard, don't roll your own.  Find out what is required by the business, not managements "everything must be encrypted" and work towards that.  You may be able to get away with seeing if the storage layer has encryption that can be implemented.


To make an efficient compare of all columns between 2 tables, you could use a UNION method with GROUP BY as described in Diff2Tables. Alternatively, if your profile table can have rows deleted from it without cascade delete, you could, drop indexes that are not for PK of the profile, Delete rows having the same key in both tables, and insert the rows from the other table into the profile then re-build the indexes on the profile table again. This may sound bad but in fact it is not that bad. This is a typical scenario in Data Warehousing.


Get total number of rows when offset is greater than max

You can adapt something like this:

select c.count, u.*
from
  (
    select count(*) as count
    from users
  ) as c
  left join
  (
    select name, id
    from users
    order by id
    limit 5 offset 50
  ) as u
  on true
order by u.id
 ;


Yes, the breaking is not good.

The reason is that you FDs (B->C and C->D) also imply B->D. So B->CD (both B->C and B->D) break 2NF and should be used for the table breaking.

So, the relations, in order to be 2NF should be: (A,B) and (B,C,D).

(and then the C->D can be used to break the second further for 3NF.)


How can I override the MySQL DELETE command to update a field rather than to remove the row?

No, this isn't possible.  You can block deletions with triggers or (where appropriate) foreign key constraints, but you can't make DELETE do anything other than delete.

It isn't more semantically correct -- you are updating a row, not deleting a row.  DELETE means remove 0 or more rows.

If you really want to use the word "delete," create a stored procedure with an appropriate name that accepts the primary key as its argument.

To prevent deletions entirely, use a simple trigger like this:

CREATE TRIGGER t1_bd
BEFORE DELETE ON t1 -- table name
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'table t1 does not support deletion';

This will immediately terminate the query and prevent the deletion.

The customary DELIMITER statements and BEGIN/END block are not required when a trigger contains a single non-complex statement in its body, like the one above.

SQLSTATE 45000 means unhandled user-defined exception.


How can you recursively query across 2 columns?

It isn't exactly clear what the group_id represents but for the traversing of the points this can be handled with a recursive query like so:
WITH RECURSIVE movement(id, person, place, end_point, level) as
(
 select id,person, start_point, end_point, 1
 from test
 union
 select p.id, c.person, c.end_point, c.end_point
  , case when p.id=c.id then p.level else p.level + 1 end
 from test c
 inner join movement p on (c.start_point=p.end_point or c.start_point=p.place)
 and c.person=p.person
), movementSum as
(
  select id,person,place,level
  from movement m
  where m.id = (select min(id) from movement t where t.person=m.person and m.place = t.place)
)

select *
from movementSum ms
order by id, place, level

Once you share more about group_id perhaps the exact sql can be produced.
Use this sql fiddle to decide what group_id should represent: http://sqlfiddle.com/#!17/cc042/2


SSRS 2012, push permission changes down the structure?

@Matthew,
Given that you do not want to revert to parent security on your sub-folders and children reports, I think your only option will be to write some custom code which accesses the SSRS Web Services API.  I've done something similar using powershell for one project and a C# script task from SSIS for a different project and should work well although it won't be accessible from the SSRS management portal.

I'm 99% certain that there is no functionality within SSRS that will directly handle your request out of the box.


How to improve the estimated cardinality of inner join lateral

If the joins can't add new rows, then why are f1 and f2 defined to be a set-returning function in the first place?  If they were not set-returning functions, this multiplication would not happen.
Alternatively, declare the functions to be expected to return only one row.
alter function f1 rows 1;

etc.,.


How to find locking schema for set of tables in database?

Use the following to determine the locking schema for each user table in an ASE database:

SELECT TableName = CONVERT(VARCHAR(30), so.name)
    , LockScheme = CONVERT(VARCHAR(30), lockscheme(so.id))
FROM sysobjects so
WHERE so.type IN ('S', 'U');

The CONVERT(VARCHAR(30), xxx) is simply to make the output easier to see in a console session.  If you are using a GUI tool to connect to the ASE instance, they are probably not necessary.  Be aware that if you have table names longer than 30 characters, they will be truncated in the output.

Run that in both instances and simply compare the output in BeyondCompare, or some other differencing tool.


You try to insert Albums.ArtistId value (7) which is absent in Artisis.ArtistId values list now. So insertion fails.

You cannot insert album of <unknown>, FK claims this is impossible, and server prevents this.

Insert artist with ArtistId=7 first, then insert his albums.


See direct (instead of indirect) info about rented book in my "actors_directors" talbe dropdown list - Access365

You can make foreignKeyBook field as a compobox with coulumn count = 2,  column widths =0"1", and row source is a query of books.index and books.title in that order

Or you could easily make this in the beginning while designing the checkout table by setting the data type of field ForeignKeyBook to lookup withered.. and following the lookup wizard window


How to assign privileges on a postgresql schema to a user?

You don't mention which user created service_schema.customers and what permissions were given, if any, to the admin user for that table.

That user is presumably not admin and it presumably gave no permission at all to admin, so that would explain the error.

After creating the schema, the script does that:

GRANT ALL ON TABLES IN SCHEMA service_schema TO admin;

(BTW I've removed the single quotes around the schema name and user name. It's an error to put single quotes around identifiers, only double quotes or no quotes at all are allowed).

Anyway that command doesn't really affect anything because there are no tables yet in a schema that has just been created.

What you probably want is to (quoting the doc here) set the privileges that will be applied to objects created in the future with

ALTER DEFAULT PRIVILEGES IN SCHEMA service_schema GRANT ALL ON TABLES TO admin;

See ALTER DEFAULT PRIVILEGES in the documentation.


Are existing entries immediately included into a newly created index?

The CREATE INDEX is unit of work that follows ACID.

So when it completes, it has completed (or failed) completely ("Atomic" in ACID). No further work is needed to populate or otherwise prepare the index ("Consistent" in ACID)

Whether it improves performance depends on it's suitability for the queries you are running, data distribution etc. That is a whole new area...


PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

It seems that I had to re-run the AWS RDS PostGIS instructions:

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

This is clearly an RDS Bug, because these instructions should not be executed more than once.

Perhaps this happened due to a database minor version upgrade.


SQL Server Database size - doubled running DBCC

CHECKDB by design will use tempdb space for temporary storage and it will fail if you run out of space. Make sure you presize your tempdb. You can check how much space it needs by issuing WITH ESTIMATEONLY command:
DBCC CHECKDB ([WideWorldImporters]) WITH ESTIMATEONLY;

You will be able to get an estimate space requirement for tempdb:
DBCC results for 'WideWorldImporters'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database WideWorldImporters = 1.
Estimated TEMPDB space (in KB) needed for CHECKDB on database WideWorldImporters = 261556.
Object ID 1179151246 (object 'Warehouse.ColdRoomTemperatures'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
Object ID 1211151360 (object 'sys.memory_optimized_history_table_1179151246'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
Object ID 1243151474 (object 'Warehouse.VehicleTemperatures'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since you have close to 800GB, what you can do is breakdown the CHECKDB into multiple DBCC commands. You can follow the recommendation here.


SQL table constraint limit number of same domain < 5

You need to normalize the database, or you'll have to use a trigger. There is a method of doing what you want, it can be done with F671, “Subqueries in CHECK constraints” however Oracle doesn't implement them. For that, you'll have to turn to everyone's favorite database, Microsoft Access.

To normalize create a table like this with a check constraint on the amount of attempts, then increase the amount of attempts. The CHECK constraint will resist incrementing past 5.

CREATE TABLE applicant (
  applicantNum     int        PRIMARY KEY,
  applicantentries smallint   CHECK ( applicantentries < 5 )
);


SQL Server Availability Group - Listener configuration problem

In order for the IP address to be brought online, three requirements must be met (among others, but these are the most relevant to this error):

At least on of the listener IP addresses must be in the same subnet as one of the cluster networks.
The cluster network that is in the same subnet as the listener IP address must be configured as a public network (will add more details in a few minutes).
The IP address must not be in use on the network.

You can view and configure the cluster networks in Failover Cluster Manager.  Expand the cluster and select the Networks node to see the networks.  Right-click on a network and select properties.  Ensure that "Allow clients to connect through this network" is enabled on all networks that are used by listener IP addresses.

If you need further help, please add more detail to the question regarding the network and IP configuration of the nodes and the listener.  The only useful information in your question at this point is the error message, so that's all I have to go on.


Mongodb high lock percentage / slow queries

To see if the hardware is not limiting:

top/htop => cpu percentage
iostat -x 1 => sysstat tool to see disk r/w limits (%util)

Concerning locking:

Mongo 2.6 : database locking
Mongo 3.0 + MMAPv1 storage engine : collection locking
Mongo 3.0 + WiredTiger storage engine : document locking

If you have 1 huge collection (server-prod), maybe Sharding is an option to distribute the load, or more cores + less locking with Mongo3.0

Improve indexes:
 - More indexes = slower write + faster read
 - Less indexes = faster write + slower read

Read from Secondaries, Only write on Primary.

> db.setProvilingLevel(1,4)  ##  save slow logs for that db slower than 4ms
> db.system.profile.find({millis:{$gt:100}}).sort({ts:-1}) ## find queries slower than 100ms, order by timestamp descending
> ....query.explain()  ## find out which indexes it uses

Information:
http://docs.mongodb.org/manual/administration/optimization/