Creating and querying a database to set hierarchical associations between Groups, Users, Items and Points

I think your approach - in having 3 joining tables (Point_group, Point_user, Point_item) is sound.

The considerations of this design are:

Adding "permission" is easy enough - if the user selects to add to User, then you insert into Point_user
Starting with a Point, if you want to ask "Which Groups, Users and Items have permission?" then you will need to construct a query against all 3 joining tables. You could have 3 separate queries and aggregate results in your application, or you could have a single query which is the UNION of 3 SELECTs (with a column representing the SELECT that includes a row, with values like user, etc)
If you want to start with a Group, User or Item, and ask "Which points are permitted for this Group/User/Item?" then your query gets more complicated the lower down you go. For example - if you start with an Item, you have to query Point_item for all rows having the ID of the Item. You also have to find all linked User rows - and for each of those rows, check Point_user. Likewise, you then have to find all linked Group rows and then search Point_group for all matches. In straight SQL this will be a large SELECT - but that may be totally fine - it's just something to think about.

To come at this another way - what alternatives do you have?

You could have a single table that somehow maps permissions from a Point to other objects. Its design would have point_id, of course, and then a column to specify what it is that this Point is mapping to - ie. Group, User, or Item. Then, of course, you need an id column - which would link to the relevant table.

I have seen models such as this alternative implemented before, and quite frankly, I think that's a worse design. Such a design means that when you want to join from your Point to the other entity, you need to check this mapping flag column in order to make a decision about which table should be joined. It's messy, ugly and performance intensive.

There is one further alternative, however:

Merge your Group, User and Item tables into a single table. For want of a better name, call it Entity, in this example. You now need a flag to indicate the type of entity on a given row - such as Group, User and Item. You will also want a column to specify parent - so that for an Item row you can specify its User parent.

This last suggestion would mean you only need a single mapping table between Entity and Point. However, run through all of the above scenarios again - how will you query this if you start with, say, a Point, and want to know which Groups, Users and Items have permission to view? Getting a list of all entities would be a simple query, but if you want to list all members of all entities, you would need the entity table to join itself multiple times.

This last design may have its benefits, but I think your original proposal is the easiest to maintain and understand.


Cardinality between tables in the present or over time?

I think you might be missing an entity in your model. Over time a store can be associated with multiple groups and a group can be associated with multiple stores, so unless you only want to record the current state and no history you have a N:M relationship and need a junction table. Something like:
Store      StoreGroup                        Item
=====      ==========       Group            =============
Id   <---- StoreId          ==========       Id
           GroupId    ----> Id         <---- GroupId
           StartTime        GroupProp1       ItemProperty1
           EndTime          GroupProp2       ItemProperty2

You may need to enforce the "one store has one group at a time" rule in your application layer unfortunately, or using triggers, I'm not sure SQL constraints will be sufficient.
As an alternative to directly modelling changes over time, if the database you are using supports temporal tables (sometimes called "system versioned") you could use that feature instead.
Then you can have:
Store      Group            Item
=====      ==========       =============
Id   <---- StoreId          Id
           Id         <---- GroupId
           GroupProp1       ItemProperty1
           GroupProp2       ItemProperty2

and one group has one store is enforced by only being able to have one store in the data for a group, and one group per store can be enforced by a unique constraint on Group.StoreId. But I might recommend the other approach unless you can guarantee that the one-group-one-store rule will hold for future versions (it seems unusually limiting to my mind), as the former approach would be easier to refactor to allow the greater flexibility.


How to disable instance recovery in ORACLE?

Instance Recovery is necessary to get the database back to a consistent state after shutdown abort or some other abnormal shutdown event. Oracle is never going to let you open the database in an inconsistent state, so no, there is no possibility that you can disable it. If Oracle is crashing during Instance Recovery you need to get in touch with Oracle Support or revert to a backup.
The concepts guide has essential reading about instances and more particularly what Instance Recovery is and why it is sometimes needed:

If an instance of an open database fails, either because of a SHUTDOWN ABORT statement or abnormal termination, then the following situations can result:

Data blocks committed by a transaction are not written to the data files and appear only in the online redo log. These changes must be reapplied to the database.

The data files contains changes that had not been committed when the instance failed. These changes must be rolled back to ensure transactional consistency.

Instance recovery uses only online redo log files and current online data files to synchronize the data files and ensure that they are consistent.

If the Instance Recovery crash is caused by a corrupt datafile you could mount the instance, drop the offending datafile and then open the database - but you will lose the tablespace that file is in.


Solution:

https://stackoverflow.com/questions/881132/retrieving-the-com-class-factory-for-component-failed

In DCOMCNFG, right click on the My Computer and select properties.
Choose the COM Securities tab
In Access Permissions, click "Edit Defaults" and add Network Service to it and give it "Allow local access" permission. Do the same for \Users.
In launch and Activation Permissions, click "Edit Defaults" and add Network Service to it and give it "Local launch" and "Local Activation" permission. Do the same for \Users


I see two potential issues:

Clarity on frames

I understand that a frame represents one (or more) intervals, as established by a company.

I believe you originally intended a frame to be a  block of time, and to allow multiple companies to use it. The potential problem with that is - what if one company needs to alter that frame? You basically have to create a second frame, and migrate over only the references for the one company to the old frame. This is possible, but would be a maintenance hassle (and would slow things down while you did it).

I also note that a frame can include multiple time intervals, and repeating time frames (allowing, I assume, you to set up weekly standing "appointments" where appropriate). If a frame represents a specific event (even if that "event" is a recurring weekly appointment, or a five-day conference), this shouldn't be a problem; If multiple events are re-using the same frame, then the maintenance issue mentioned above would grow even worse.

If a frame belongs to just one company, you don't really need the company_frame table; you can have the company_id in the frame table directly.

NOTE: One specific clinic may have many rooms, and many doctors, each of whom may have different appointments at the same time. It may be worthwhile to add another distinguishing factor to a frame - perhaps a location ("Room 2" in a doctor's office; "Conference Suite 100"; whatever's appropriate) to help differentiate. In some cases, it may be sufficient to use the doctor and the time interval (in a doctor's office, "Dr. Cameron, 15:00 - 15:30, 2018-03-23" should be distinctive); in other cases, you might need something more.

Data consistency

The next thing that comes to mind is the potential for data consistency issues. This is where the "circular" design comes into play.

In theory, with this design, nothing guarantees that a doctor associated with a frame is connected to the same company as the one that "owns" the frame. However, logically, all doctors associated with a given frame should belong to the same company as the frame.

Based on this, I think that it makes more sense that frame_doctor actually be frame_co_doctor, and that it be used to maintain a many-to-many relationship between company_doctor and frame instead of between doctor and frame (doctor would only be connected to anything else via company_doctor then).

Presumably, if a given doctor stopped working for company X, he would no longer be tied to the frames belonging to that company; he might still be connected to frames owned by other companies, though. And, company_doctor and frame would both have the company_id, making it easy to verify that the correct records are being linked - if company_doctor.company_id <> frame.company_id, then you shouldn't create the frame_co_doctor row.

Those are the biggest potential issues I see with the design as it stand now, and how I'd resolve them, based on what I know of your requirements. I may, of course, have misunderstood a requirement, or there may be some you haven't noted as yet, that would invalidate these approaches, but they're the way I'd look at it.


I expect this model to be a bit more complicated than two tables. If the vehicle tracking provides you with two IDs, then they may probably be foreign keys to some specification. Also, it is entirely possible that different vehicles will have the same hardware, and the other way around, different hardwares will have the same vehicle. In that case, you need to normalize it by placing in different tables.

My proposition is based on the following model:

EDIT

Thanks to @DamirSudarevic I've made some updates in the model.


MySQL Workbench Visual Explain not working with MariaDB 10.3.9

MySQL Workbench is a MySQL product for MySQL servers only. There are no guarantees that it works with other servers and you you may encounter non-working areas (like you described) or may not even be able to connect to such a foreign server.


PosgreSQL: setting high work_mem does not avoid disk merge

This is somewhat speculative but Depesz (Hubert Lubaczewski) has this to say on the subject:

  You might wonder, though, why PostgreSQL switched to Disk, when it
  used only 448kB? After all, work_mem is 1MB. Answer is pretty simple –
  as I understand – disk is used when work_mem is not enough, so it
  means it's already been filled. So, sort with “Disk: 448kB" would mean
  that more or less whole work_mem has been used plus 448kB of disk.

So in your case the used work_mem might be in the 6 MB range. Also, try reset work_mem first, maybe there's stuff in there from a previous query.


Database created in sqlcmd but missing in SQL Server Management Studio

Actually, I went to the wrong connection. It should be SERVER\MSQLSERVER rather than SERVER\MSSQLSERVER.


Restore a database by pointing to one or more directories containing backup files and specifying a STOPAT time

Here's what I put together last week, in lieu of finding anything else already in this niche.

https://github.com/dave-britten/RestoreDB

Usage is fairly simple (though there are a number of options to customize). For example, my test server reload script can just do something like this to restore the Dynamics GP system database:

EXEC master.dbo.RestoreDB @Source='+\\oldserver\Backup\oldserver\DYNAMICS', @Database='DYNAMICS', @AutoMove=1, @Replace=1

That will restore the database DYNAMICS to the latest available point in time, and automatically move all database files to the instance default data and log directories.

Two other useful options are @RestoreAs and @AutoRename, which, when used in combination, allow for easily restoring the database under a different name (e.g. a test copy), with the database files being automatically renamed (replace the old database name with the new database name).

Currently, RestoreDB requires at least SQL Server 2016, but I'd like to get it working with 2012 and later.


Best way to rank all the columns in a table and store those ranks in another table

You would need an index on each column in order to avoid a sort of every row and column during the ranking process.  That would of course introduce significant overhead as scores are updated continuously.  Probably not an option unless you have a high-end hardware configuration.

The ranking processes could be offloaded onto a read-only copy maintained via log shipping on a different box.  That would avoid concurrency issues with hitting the live database system of record and provide more server resources for both the intensive batch process and live database.  If you are running Enterprise Edition, you could use a database snapshot on the same box as the source if your hardware is sufficiently sized.

You mention truncate and insert.  Just want to mention that you could also use SWITCH such that you truncate and insert into a staging table.  Once the staging load is completed, truncate Table_Ranks and then SWITCH from the staging table into Table_Ranks in a single transaction.  That way, Table_Ranks would never be empty.


MySQL Difference between BEGIN and START TRANSACTION

The manual has this to say:

BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a transaction. START TRANSACTION is standard SQL syntax, is the recommended way to start an ad-hoc transaction, and permits modifiers that BEGIN does not.
The BEGIN statement differs from the use of the BEGIN keyword that starts a BEGIN ... END compound statement. The latter does not begin a transaction. See Section 13.6.1, “BEGIN ... END Compound Statement”.


Getting Error/Exception In GoldenGate Replication

There is an error in the code:

, COLMAP ( REPLICAT_NAME = @GETENV("GGENVIRONMENT", "GROUPNAME"))

There is double bracket at the end of the line.


SSMS Database Node Refresh causes Restore to Fail

SQL Server 2012 SP1 CU15 (http://support.microsoft.com/en-us/kb/3038001) resolves the issue (no deadlock occurs as the result of issuing a command during a database restore).


Pausing the secondary Replicas for a day in always on availability group

...but will there be any other problems of pausing the secondaries for a day ...

"It depends" but in general, yes. the non-complete list of items:

No transaction log truncation on the primary
Primary send queue will be large
Log caches will become large and may end up consuming more memory than you'd like...
Which could cause memory pressure, buffer pool, etc. issues
When the connection reconnects it'll need to send all the outstanding data, which it may not ever catch up

Now, this seems pretty grim, however, if the amount of traffic is normally pretty low (which you can guesstimate by looking at your transaction log backup size history) then you might be able to get away with it.

If, however, the amount is very large then you might just want to remove it from the Availability Group, do your maintenance, replay the logs when you bring it back up, and join it back in.

  Can i safely go to the Failover cluster manager and paused both the secondary nodes ?

You could but that won't affect SQL Server. You'll need to work with SQL Server through T-SQL or SMO. Pausing the node will only cause you more problems on the primary, but pausing the node on the secondary will tell the WSFC to not move any resources over (which won't happen anyway but this won't hurt anything).

Edit: Moving comments and addressing

  On average the total T log Size are 1.5 gb per day

If it'll be down for a week, that's 7 days * 1.5 GB/Day = 7 * 1.5 = 10.5 GB of outstanding data to send and the log file will roughly need to be or grow by this amount - on average. This may or may not be a "big deal" in your environment.

  Cant I just switch servers and pause both the secondaries or from the always on dashboard ssms just disable automatic failovers between the primary and the secondary?

So you could do that, again my concerns with it are listed above. "Pausing" the data movement does just that, pauses the data movement. The nodes will still, mostly likely, lose connection and the WSFC will boot some of the nodes out of the cluster - in fact, depending on the overall design and configuration of the WSFC, the entire cluster may go down (worst case).

Since this is the first time, you may want to find someone to work with to go over the design and give you some recommendations. This should be used to create your DR or runbook/playbook/whatever documentation as David suggested.


Should Fileshare be part of Cluster resource in multisubnet AAG?

I'd need to see the article where that quote is from to understand the context, but the file share will still be part of the cluster and will still be configured as the witness.

Votes: Node1=Yes, Node2=Yes, Node3=No, File share=Yes

If you ever failover to the other data center, you'll need to create a quorum there. Maybe you'd spin up another node and file share when you failover. Those would be created in the San Jose data center. Once those are in place, add the VM to the cluster and then reconfigure the cluster to use the new file share in the San Jose DC for the witness. After failover, disable the votes for the DC data center. You don't want those resources deciding if you have quorum when there's a network glitch between the two data centers.


How does a VoltDB cluster determine time sync between nodes

At startup, VoltDB is going to send a round-trip message from the first node of the cluster to all joining nodes of the cluster. This message queries the local time at each node.

For each joining node, the difference between its local time and the first node's local time is computed. This includes clock skew and network latency. In fact, it's possible for clock skew and network latency to cancel each other out, but not super likely.

Across all joining nodes, VoltDB reports the maximum difference between clocks.

In this case, VoltDB is telling you that for some pair of nodes including a joining node and the first node you started (the startup leader), the difference between the compared clocks was 120ms, which is a lot for machines on NTP that are in the same datacenter.

You can check network latency using ping between nodes, but make sure you're using the same network interface you've setup VoltDB to run on.


If you're using mysql 5.1.21 or newer, you can change the behavior of logging slow queries with the min_examined_row_length

Set it to something like 5000 or so, and keep your log_queries_not_using_indexes turned on.


Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

Here's a repro.  It's documented that you can't use FORCESEEK on an indexed view without NOEXPAND.

EG

drop table if exists table1
drop table if exists table2
go
create table Table1(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200))
create table Table2(IndexedField1 int, OtherField1 nvarchar(200), OtherField2 nvarchar(200))

go

create view IndexedView1
with schemabinding
as
SELECT
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' + OtherField2)), 32) AS IndexedField2
FROM dbo.Table1

go

create view IndexedView2
with schemabinding
as
SELECT
    IndexedField1,
    RIGHT(CONVERT(VARCHAR(34), HASHBYTES('MD5', OtherField1 + '||' + OtherField2)), 32) AS IndexedField2
FROM dbo.Table2

go

create unique clustered index pk_IndexedView1 on IndexedView1(IndexedField1,IndexedField2)
create unique clustered index pk_IndexedView2 on IndexedView2(IndexedField1,IndexedField2)

go

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (FORCESEEK)
INNER JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

    /*
Msg 8622, Level 16, State 1, Line 34
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
*/
go
drop table if exists #TEMP

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1  AS A with (noexpand)
INNER JOIN dbo.IndexedView2 AS B with (noexpand)
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2

executes with plan:

And after applying NOEXPAND, and specifying that that the plan should only involve the view indexes, you can then use the FORCESEEK hint, or specify the join style (MERGE, LOOP, or HASH) if you want.

EG

SELECT A.IndexedField1
INTO #TEMP
FROM dbo.IndexedView1 AS A WITH (NOEXPAND)
INNER MERGE JOIN dbo.IndexedView2 AS B
    ON A.IndexedField1 = B.IndexedField1
    AND A.IndexedField2 = B.IndexedField2


Postgres - caching of recently inserted data?

Caching by Postgres or the operating system isn't going to help much when Postgres doesn't know which of the millions of records in your table fulfill your query's condition(s). The only way to make sure this is going to be consistently performing as you expect is to use indexes.

An index on the combination of deviceNumber and loadedtime is what you need in your case.

In your question you note that a query can be relatively fast (like a second for recently inserted data), so I'm strongly suspecting you already have an index on at least one of these columns.


Why pt-duplicate-key-checker suggests removing a composite index?

The primary key is a part of any secondary key in InnoDB.


Don't store functionally dependent data. If you should need that for performance, you can always add a materialize view later (see below). But it's often better (and sometimes even faster) to calculate derived values in queries on the fly.

In particular, you would create many dead rows, table bloat, more work for VACUUM and nasty concurrency issues in conversation if you UPDATE that row with every new message and with every status change.

I suggest a relational model like this:

CREATE TABLE users (user_id serial PRIMARY KEY, ...)

CREATE TABLE conversation (
  conversation_ id serial PRIMARY KEY
, user_id1         int NOT NULL REFERENCES users
, user_id2         int NOT NULL REFERENCES users
-- cnt_new_msg   -- redundant
-- last_msg_id
, created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE message (
message_id      serial PRIMARY KEY
conversation_id int  NOT NULL REFERENCES conversation
sender          "char" NOT NULL CHECK (sender IN ('1', '2')
message         text NOT NULL
type            ???
unread          boolean NOT NULL DEFAULT TRUE
created_at      timestamptz NOT NULL DEFAULT now()
);

Details for data types in the manual.
You can create a VIEW or MATERIALIZED VIEW to present conversation data like you had in mind:

CREATE VIEW conversation_plus_ct AS
SELECT c.*
     , count(m.unread OR NULL) AS cnt_new_msg
     , max(m.created_at) AS last_msg_id
FROM   conversation c
JOIN   message m USING (conversation_id);

And your query could look like this (not using the VIEW, but it could ..)

SELECT *
     , count(m.unread OR NULL) AS cnt_new_msg
     , max(m.created_at) AS last_msg_id
FROM   conversation c
LEFT   JOIN message m USING (conversation_id)
WHERE  [current_user_id] IN (user_id1, user_id2);


How to turn off header only in psql (postgresql)

When executing psql from shell you can use -t (prints tuples only) option:

$ psql -t -c "SELECT version();"
 PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Within psql use \t to turn off printing header and row count.


Query for frequency of e-mail addresses in a field

I think you want a "count of counts", which can be done with 2 GROUP BY, once to find how many times an emailaddress has been used:

    SELECT emailaddress, COUNT(*) AS frequency
    FROM tablename
    GROUP BY emailaddress ;

and then enclosing the above in a derived table and doing another GROUP BY to find how many emailaddresses were used once, twice, thrice, etc...:

SELECT frequency, COUNT(*) AS emailadresses
FROM
  ( SELECT COUNT(*) AS frequency           -- we don't need email any more
    FROM tablename
    GROUP BY emailaddress
  ) AS x
GROUP BY frequency ;

Minor mysql quirk. If you want:

GROUP BY frequency
ORDER BY frequency DESC ;

it can be abbreviated (and it will be a bit more efficient) in MySQL as:

GROUP BY frequency DESC ;


SQL Server - create user for a local Windows login from another instance (AlwaysOn)

To pre-provision the local Windows database accounts, add the local Windows database users to the database before you add the database to the AG. Steps below.

create the new database on the secondary
create the secondary node local login and database user
backup the database and drop from the secondary
restore the database to the primary
create the primary node local login and database user
add the database to the AG
backup the database and transaction log
restore the database and log backups to the secondary WITH NORECOVERY


How do I know that the high number of compilation/sec is because of memory pressure not bad queries?

You can see what types of queries are in your plan cache with the following query (source: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/)

SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
AVG(usecounts) AS [Avg Use Count],
SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
    ELSE 0
    END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
SUM(CASE WHEN usecounts = 1 THEN 1
    ELSE 0
    END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC

This should show you if most of your saved plans are ad-hoc or for procedures. If most of them are only being used once, then try using the "optimize for ad-hoc workload" option mentioned like so:

sp_configure 'show advanced options',1
GO
reconfigure
GO
sp_configure 'optimize for ad hoc workloads',1
GO
reconfigure
GO


How to select first row from a join that returns multple rows on the primary key

SELECT
    A.PersonName, A.Email
FROM
        (
        Select Person.PersonName, Email.Email
            ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN
        From person
        left join Email on Person.ID=Email.PersonId
        ) A
WHERE A.RN = 1


Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

You can do most of this by using a login.sql. login.sql is executed during - surprising - login and is loaded from your SQLPATH or current directory. For the examples you gave, your really chose the worst case.

Problem is the sqlterminator. Whatever you put in there, the forward slash is maintained as a free sqlterminator. Next to that, sqlplus first scans for the sqlterminator and does this before scanning to the string terminator. A bug if you ask me. The forward slash can be used in a string as long as it is not alone on a seperate line. As soon as sqlplus finds the character specified as sqlterminator, it ignores everything else and stops reading.

The forward slash can be handled, as long as it is not alone on a line.

login.sql contains:

prompt run login.sql
show sqlterminator
show sqlblanklines
set sqlblanklines on
set sqlterminator ';'
show sqlterminator
show sqlblanklines
prompt ready login.sql
set echo on

leigh.sql contains:

INSERT INTO t1 VALUES ('fail bc semicolon
a;a
/
');

INSERT INTO t1 VALUES ('fail bc solo /

aa
/
');

INSERT INTO t1 VALUES ('ok / not solo

aa
/a
');

DELETE FROM t1 WHERE a = 'c

a/
';

run the script:

sqlplus leigh/leigh@orcl @leigh
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 9 22:36:20 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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

run login.sql
sqlterminator ";" (hex 3b)
sqlblanklines OFF
sqlterminator ";" (hex 3b)
sqlblanklines ON
ready login.sql
SQL> INSERT INTO t1 VALUES ('fail bc semicolon
  2  a;a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated

SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL>
SQL> INSERT INTO t1 VALUES ('fail bc solo /
  2
  3
  4  aa
  5  /
ERROR:
ORA-01756: quoted string not properly terminated

SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL>
SQL> INSERT INTO t1 VALUES ('ok / not solo
  2
  3
  4  aa
  5  /a
  6  ');

1 row created.

SQL>
SQL> DELETE FROM t1 WHERE a = 'c
  2
  3
  4  a/
  5  ';

0 rows deleted.

No need to fiddle with begin/end blocks. Can't handle sqlterminator inside command, no matter where it is, in a string or not, can't handle lines with forward slash alone on a line in a string.


COUNT counts values that are not NULL. You can transform the unneeded values into NULL for example with DECODE. Combining the two as below:

select
    student_id,
    count(decode(attendance_id, 'ABSENT', 1, null)) as absent_count,
    count(decode(attendance_id, 'PRESENT', 1, null)) as present_count,
from
  attendance
group by
  student_id
;

Or you could put 0 instead of null and use SUM.


There are several factors that comes to play here:

How much I/O in general can a device (your SSD in this case) handle? Say that out of 100% in total for tempdb, the log (ldf) caters for 30%. Buying a new SSD and put the ldf on there will give you more IOPS and those 30% for the ldf won't be taken from the drive where the mdf/ndf is.

The "separate the log" made a lot of sense when we had single disks and there was a huge performance difference between sequential and random I/O. A transaction has to wait for the ldf records to be written to the disks (hardened). The ldf is (almost) only sequential I/O. Isolating ldf to a separate disk means the disk is in position for the next write operations meaning sequential I/O (no moving of the disk arm). How big difference is there between sequential and random I/O for an SSD disk? I don't know, but probably minuscule. So, this aspect is probably not relevant for modern disk subsystems.

So, IMO, it boils down to capacity. Can your SSD handle the I/O load with reasonable response time? If not, then you want more capacity - more SSD drives.


Access not properly translating TOP predicate to ODBC/Oracle SQL

I think this may be less an ODBC challenge and more an Access challenge you are facing.

The driver is more or less (with differences like when drivers like to introduce server-side cursors and break work up that way, etc) doing what the client tells it to.

Access is really well known for doing some interesting things to decent databases and somewhat well designed DBs. Can you at least put your code into Oracle in stored packages/procedures and then just call those from the forms app? Are you 100% married to Access?


Querying Cassandra without a partition key

when there is no Primary Key specified in the WHERE clause, how does the Coordinator know which nodes to send the requests to?

It doesn't.  The (node chosen as the) coordinator has to scan all rows for that table on each and every node.  That's why unbound queries are considered to be an anti-pattern in Cassandra, as they incur a lot of network time.  Especially in larger clusters.  Also, the coordinator will have to do extra work as it has to assemble and return the result set.

  If multiple rows are returned, which may be distributed in different nodes, how are these rows aggregated and returned to client?

They are not really so much aggregated, as they are returned in order by the hashed token value of their partition key.

Consider an unbound query run against a table named crew, with a partition key of crewname.  When I run the CQL token() function on that key, you can see that the rows returned are indeed ordered by their token.

aploetz@cqlsh:presentation> SELECT crewname,token(crewname),firstname,lastname
FROM crew;

 crewname | token(crewname)      | firstname | lastname
----------+----------------------+-----------+-----------
    Simon | -8694467316808994943 |     Simon |       Tam
    Jayne | -3415298744707363779 |     Jayne |      Cobb
     Wash |   596395343680995623 |     Hoban | Washburne
      Mal |  4016264465811926804 |   Malcolm |  Reynolds
     Zoey |  7853923060445977899 |      Zoey | Washburne
 Sheppard |  8386579365973272775 |    Derial |      Book

(6 rows)

It works this way, because Cassandra makes certain nodes primarily responsible for certain token ranges.  It then becomes a simple task for the coordinator to return the result set in that order.  If there multiple rows with the same partition key, the results will additionally be sorted by the clustering keys within each partition key.


Changing 1.7M rows in a table requires making copies of all the rows that are changed.  (This is in case there is a crash or ROLLBACK.)  The system is not designed to handle huge changes like that.
It may be faster to do the UPDATE in chunks, COMMITting after each chunk.  This way the undo stuff is not huge.
I discuss techniques for walking through a table via the PRIMARY KEY in http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks .  That blog is worded in terms of DELETEs, but UPDATEs can use the same techniques.
Side notes.  Some of these may lead to other solutions.
Do not even think of touching the .ibd file.
You do have a PRIMARY KEY, don't you?  If not, that is bad.  And it makes the "walking" that I describe less efficient.
Please provide SHOW CREATE TABLE and the actual UPDATE if you want to discuss things further.
In general, it is "bad schema design" to need to change every row of a large table.  There are much more efficient techniques.  One that comes to mind is to have a second table that contains only one row -- with the current value of foo.  However, it may or may not work for your use case.
What is your use case?  Do you change all the rows?  Or does that UPDATE usually change only a few rows?  How many different values?  Is it a flag, a date, or what?  Etc.
You have VARCHAR(2000); is the contents typically large?  How big is the typical record?  These questions lead into discussing "off-record" storage of large columns.
How much RAM do you have?  What is the value of innodb_buffer_pool_size?  What is the table size (SHOW TABLE STATUS LIKE 'mytable';)


MariaDB master-slave replication failover and failback

I went with master-master replication. To prevent connections to the "passive" copy of the master I used a script to delete all users (except the DBA and the replication one) which makes sure to prevent writes on the "passive" copy.

As part of the failover process the users will be transferred to the "passive" and dropped from the "active".

This basically achieves what I was looking for.


Re-order columns values based on values in a table

Solution

select  ColA,ColB,ColC,ColD

from   (select  t.n,t.val

               ,row_number () over
                (
                    partition by n
                    order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end
                                ,val
                ) as rn

        from   (select      rownum as n
                           ,t.*

                from        t
                ) t unpivot (val for col in (COL1,COL2,COL3,COL4)) t

        ) t pivot (min(val) for rn in ('1' as ColA,'2' as ColB,'3' as ColC,'4' as ColD))

order by n
;

For this demo, the numeric values are assumed to be integers.

Walkthrough

Add a row number to each row:

select      rownum as n
           ,t.*

from        t
;

The row numbers will serve as identifiers to be later used when the unpivoted rows are pivoted back. The output produced is like this:

+---+------+------+------+------+
| N | COL1 | COL2 | COL3 | COL4 |
+---+------+------+------+------+
| 1 | B    | D    | C    | A    |
+---+------+------+------+------+
| 2 | C    | Y    | M    | T    |
+---+------+------+------+------+
| 3 | 3    | 5    | 2    | 4    |
+---+------+------+------+------+
| 4 | 5    | 2    | 10   | 7    |
+---+------+------+------+------+

Unpivot the rows.

unpivot (val for col in (COL1,COL2,COL3,COL4)) t

Rank the columns within each row according to their order as sorted first by their numeric value, if applicable, and then alphabetically as strings.

row_number () over
(
    partition by n
    order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end
                ,val
) as rn

For numeric items, the first sorting criterion will evaluate to corresponding numeric values and thus determine the order of the items. For string items, the first term will be null, and so the order will be determined by the second sorting criterion.

The output that you will get for your example after unpivoting and ranking will be this:

+---+-----+----+
| N | VAL | RN |
+---+-----+----+
| 1 | A   | 1  |
+---+-----+----+
| 1 | B   | 2  |
+---+-----+----+
| 1 | C   | 3  |
+---+-----+----+
| 1 | D   | 4  |
+---+-----+----+
| 2 | C   | 1  |
+---+-----+----+
| 2 | M   | 2  |
+---+-----+----+
| 2 | T   | 3  |
+---+-----+----+
| 2 | Y   | 4  |
+---+-----+----+
| 3 | 2   | 1  |
+---+-----+----+
| 3 | 3   | 2  |
+---+-----+----+
| 3 | 4   | 3  |
+---+-----+----+
| 3 | 5   | 4  |
+---+-----+----+
| 4 | 2   | 1  |
+---+-----+----+
| 4 | 5   | 2  |
+---+-----+----+
| 4 | 7   | 3  |
+---+-----+----+
| 4 | 10  | 4  |
+---+-----+----+

Pivot the rows back using the rankings as new column names (optionally aliased following any pattern you like; in the query above – as ColA, ColB etc.) and the row numbers assigned at the beginning as row identifiers.

The result will be the expected output:

+------+------+------+------+
| COLA | COLB | COLC | COLD |
+------+------+------+------+
| A    | B    | C    | D    |
+------+------+------+------+
| C    | M    | T    | Y    |
+------+------+------+------+
| 2    | 3    | 4    | 5    |
+------+------+------+------+
| 2    | 5    | 7    | 10   |
+------+------+------+------+

A live demo is available at Rextester.


Pros and cons of using many schemas in PostgreSQL as opposed to just one?

Obviously, you are dealing with the same tables in each user schema. Have you considered inheritance for this? It can give you the best of both worlds for some use cases. There are also some limitations. You can have a separate schema for each user and still search all user tables at once very conveniently.

Related:

Select rows from table where each row refers to a different schema
Find out which schema based on table values

Other than that, at least granting / revoking privileges has to be mentioned, which is much simpler with separate schemas.


Both the Plan Cache and 'optimize for adhoc workloads' are per instance.


SQL-Server: Why do we use FILE = 1 in scripts for restoring a database

You are allowed to save more than one backup in a backup file (i.e. device). The FILE clause lets you access a particular backup operation when there are multiple to choose from within the .bak file.
For more information on the various options of the RESTORE command, please see the following Microsoft documentation for RESTORE Arguments.
If you look under the Backup Set Options section (within the Syntax description), you will find:

FILE ={ backup_set_file_number | @backup_set_file_number }
Identifies the backup set to be restored. For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. You can obtain the backup_set_file_number of a backup set by using the RESTORE HEADERONLY statement.
When not specified, the default is 1, except for RESTORE HEADERONLY in which case all backup sets in the media set are processed. For more information, see "Specifying a Backup Set," later in this topic.
Important
This FILE option is unrelated to the FILE option for specifying a database file, FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }.

Yes, you should be able to restore without the FILE = 1 as 1 is the default anyway. And, if you only ever have a single backup-set in a backup file, then it shouldn't pose a problem.
An example to help illustrate when you would use the FILE option is the following, which is Example B from the RESTORE command documentation page (linked above). It shows doing two restores from a single backup file: the first RESTORE is the FULL backup, and the second RESTORE is the DIFFerential backup.
RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
   WITH FILE = 6
      NORECOVERY;
RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak'
   WITH FILE = 9
      RECOVERY;


SQL Server 2017 Linux CU1 - MODIFY FILE encountered operating system error 31

I figure out the issue.

Problem:  EXT3 files system is not supported.

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup
File System         XFS or EXT4 (other file systems, such as BTRFS, are unsupported

Solution: Create an EXT4 file system.

Comment the line in /etc/fstab that mounts the ext3 filesystem

vi /etc/fstab

-mount logical volume

"#/dev/vgsqldata/lvsqldata  /sqldata             ext3    defaults        1 1"

:wq!

Reboot the server

reboot

Check the file system is not mounted

df -kh

Check the linear volume

vgscan

vgdisplay vgsqldata

lvdisplay -v /dev/vgsqldata/lvsqldata

Format the volume as ext4

mkfs.ext4 /dev/vgsqldata/lvsqldata

Mount

mkdir /sqldata

mount -t ext4 /dev/vgsqldata/lvsqldata /sqldata

df -kh

touch /sqldata/test.txt

ls -la /sqldata

rm -rf /sqldata/test.txt

Persist the mount

vi /etc/fstab

-mount logical volume

/dev/vgsqldata/lvsqldata  /sqldata             ext4    defaults        1 1

Change Owner

chown -R mssql:mssql /sqldata

ls -la /

"drwxr-xr-x.   5 mssql mssql  4096 Nov  7 10:43 sqldata"


DROP DATABASE fails due to foreign key constraints

Try the DROP DATABASE a second time.  Since most tables are gone, that may have relieved enough constraints to allow the last two tables to go.

Or...  Do those two tables have FKs to each other?  In this case, neither can be dropped "first".  Hence, Greenforest35's answer (turn off FKs) seems to be the only solution.

Consider filing a bug report with http://bugs.mysql.com saying explaining that DROP DATABASE should be smarter -- such as ignoring the FKs automatically.  (Or at least the ones that will be removed later by later tables -- this would take some analysis.)

Worse yet...  Are there constraints between your remaining tables and table(s) in other database(s)?

(So many reasons not to bother with FKs.)


VACUUM and VACUUM FULL do not return space to OS

First you should at upgrade to the latest version of the 8.3 branch (which is 8.3.17). There were several fixes to VACUUM and autovacuum after the 8.3.3 (although I don't think any of those would fix your problem)

I think there are situations where even VACUUM FULL will no free all space possible. You could try to run CLUSTER on the table which does a better job of rebuilding it than VACUUM FULL (especially with that old version)

The drawback of CLUSTER is that it requires more intermediate space (because rebuilding isn't done "in-place"), so if your filesystem is really full, than it might not be possible.


If You work under linux, You can use Persona Backup - https://www.percona.com/software/mysql-database/percona-xtrabackup for HOT bin-log backup.


How can I import/export a MySQL database without redirection?

There's always the mysqlimport utility, however, your source would have to be delimited values... such as those obtained from a "SELECT * FROM ... INTO OUTFILE ..."

If you're moving large amounts of data, it might be faster to use this bulk loading method anyway.

Beware of the consequences it may have with replication however.


PostgreSQL UUID Literal: ERROR: syntax error at or near

As documented in the manual, string constants (or in general: anything that is not a number) need to be enclosed in single quotes:

ALTER TABLE newarts ALTER COLUMN jurisdiction_id
   SET DEFAULT 'a82857b6-e336-4c6c-8499-4242';


How to model a database for a small school portal

I think the way I would tackle a problem like this is to examine it through three lenses (although more may be appropriate in your case).

Objects (usually the nouns in your problem statement)
Relationships (how the nouns interact)
Actions (usually the verbs in your problem statement)

Before I get too much further into this - if you don't mind duplicate data (or potential data-entry errors in your database) then this could be created as a simple flat table with each of the items like programme, department, and facility being text fields associated with each staff member. This, however as I mention, is prone to data entry errors although it makes updating data far simpler.

I am going to propose having several tables which provide you with a hierarchical view of the data. In this case, I'll try to walk through each and explain how I would tackle it:

Objects

I see a few objects that you might want to incorporate in your database:

Staff
Programme
Department
Facility
Position / Role

Each of these will very likely become individual tables in your database. It is best practice to identify these objects with a primary key and a unique identifier (often times an incremental Id column in each table. This, then, allows you to take the next step ...

Relationships

Next, you identify what the relationships are between the objects you've defined. For example, a position / role may "belong to" (be associated with) one department or programme - depending upon how you define those (e.g., an Assistant Professor of Database Design might belong to the Software Department which belongs to the Management Information Sciences programme or vice-versa). A staff member would generally hold only one position / role. If this were the case you would have a tree like this: Staff -> Position -> Department -> Programme (or Staff -> Position -> Programme -> Department depending upon what each means).

If the first is the case, you would likely have a Staff table with a foreign key column called PositionId (or something similar). This PositionId would link in a one-to-many relationship from the Position table to the Staff table. The same would apply for the Position table, which would have a foreign key column called DepartmentId (or ProgrammeId) that had a similar relationship link to the Position table.

One kink in this logic is that it is sometimes possible for a staff person to hold more than one position. This would require a many-to-many relationship which requires a junction table in the middle. For example, if a staff person could hold multiple positions then you would create a new junction table called something like StaffPosition which would have a StaffId and PositionId column in it. This table, then would have one-to-many relationships between it and Staff (on StaffId) and Position (on PositionId). This would make updating the data more complicated, but walking through that is slightly out of the scope of this response.

Actions

Finally, the actions that you define would impact how you are going to interact with these tables. Going through each of your items, I'll give you my best guess on what those would look like (assuming that a Staff person can only hold one position).

Not an action
Not an action
Carry out transfer of staff - perform an UPDATE on the Staff table that changes the PositionId to the new position.
Carry out promotion of staff - perform an UPDATE on the Staff table that changes the PositionId to the new position.
Carry out removal of staff - in this case you might want to do two things: (1) Add a boolean / bit field to the Staff table that indicates whether the staff person is currently employed - you would turn that to OFF in this case and (2) set the PositionId to NULL. You could also, of course, delete the row in the Staff table, but you may need to access this data later (or they may be rehired).
Carry out addition of staff - this would be a simple INSERT into the Staff table, assigning the appropriate PositionId as applicable.
Answer simple queries - the basic schema I've proposed would allow that through some simple INNER JOINS on the proposed tables.

One thing I would mention is that in this configuration, you will need to have additional administrative functions to allow users to not only add Staff persons, but also add / delete Positions, Programmes, Departments, and Facilities (or you can use SQL to do that yourself).

Conclusion
This is a very simple overview and I could see the data model getting pretty complex in your situation. But hopefully this will give you and idea of where to start and how to do so.


Are Database duplication and Mirroring one and the same?

Duplicate Database
It is separate database contains all or subset of the data of Source Database.
Its purpose varies like:

Test backup/recovery procedure
Test upgrade of new release
Test effect of DB performance
Generate reports
Export data table and import in production environment if inadvertently dropped

Snapshot Replication
Here data is replicated at specified time by copying data changes from Source Database (Publisher) to receiving database (Subscriber)
Its purpose varies like:

If data change is infrequent
If database is of small size or data change occurs in large amount


Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column

I recreated the table specified with the next T-SQL code:

create table StackOverflowTest(POL_N nvarchar(100),ENDO_N nvarchar(100));
insert into StackOverflowTest(POL_N,ENDO_N)
values('POL001','0'),
      ('POL002','0'),
      ('POL002','ENDO001'),
      ('POL002','ENDO002'),
      ('POL003','ENDO003'),
      ('POL003','ENDO004');

T-SQL for getting the column specified. I would be wary of doing this on large datasets though, this is the unoptimized first draft:

  select POL_N,
         ENDO_N,
         case when ENDO_N = cast(0 as nvarchar(2))
         then cast(0 as nvarchar(2))
         else  POL_N +'-'+ cast(ROW_NUMBER() OVER (PARTITION BY POL_N ORDER BY ENDO_N desc) as nvarchar(100))
         end as COUNT_ENDO_N
    from StackOverflowTest
    order by POL_N, COUNT_ENDO_N;


How to benchmark query parsing & planning in Postgres?

It is not caching the actual prepared plans (unless you are using prepared statements).  But it does cache all the metadata it needs to look up in order to come up with the plan.  So the original planning time includes the time it took to read in all of that metadata.

If you wish to defeat the caching for experimental purposes, the simplest way would be to simply close psql and re-open it between every experiment.

For instrumentation, You can time each step of the parse-plan-execute process using log_statement_stats or its kin.  You can also set client_min_messages = log so that those reported statistics show up directly on the psql screen, rather than having to go fetch them from the server log file.  You will need to be a superuser to set either of those.


Just forget all that PL/SQL mess full of incorrect syntax, this is a single query:

with date_dim as
(
  select 1 as unique_id, 'Week 6, 2018' as week_name,
    '20180205' as week_begin, '20180211' as week_end from dual union all
  select    1, 'Week 5, 2018', '20180129', '20180204'from dual union all
  select 1, 'Week 4, 2018', '20180122', '20180128'from dual union all
  select 1, 'Week 3, 2018', '20180115', '20180121'from dual
),
fact as
(
  select 10001 as acct_id, '20180205' as week_name, 10  as sales from dual union all
  select 10001 as acct_id, '20180206' as week_name, 20  as sales from dual union all
  select 10001 as acct_id, '20180207' as week_name, 10  as sales from dual union all
  select 10001 as acct_id, '20180208' as week_name, 15  as sales from dual union all
  select 1002  as acct_id, '20180129' as week_name, 100 as sales from dual union all
  select 1002  as acct_id, '20180130' as week_name, 100 as sales from dual union all
  select 1002  as acct_id, '20180131' as week_name, 100 as sales from dual union all
  select 1002  as acct_id, '20180132' as week_name, 100 as sales from dual
)
select dd.week_name, sum(f.sales) as sales from date_dim dd
join fact f on (f.week_name between dd.week_begin and dd.week_end)
group by dd.week_name
;

That gives your needed output:

WEEK_NAME         SALES
------------ ----------
Week 5, 2018        400
Week 6, 2018         55


How can I check if connection to Sql Server is encrypted?

From TechNet sys.dm_exec_connections DMV: the encrypt_option will display FALSE if the connection is not encrypted.

Here is an example TSQL statement that can be run by administrators (VIEW SERVER STATE is the required privilege):

SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address
FROM sys.dm_exec_connections


How to change table with innodb/myisam engine to federated

An example from the manual:

  First, you must have a table on the remote server that you want to
  access by using a FEDERATED table. Suppose that the remote table is in
  the federated database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

  The example uses a MyISAM table, but the table could use any storage
  engine.

  Next, create a FEDERATED table on the local server for accessing the
  remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

  (Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)

  The basic structure of this table should match that of the remote
  table, except that the ENGINE table option should be FEDERATED and the
  CONNECTION table option is a connection string that indicates to the
  FEDERATED engine how to connect to the remote server.


Get 4 pieces of paper.

On the first piece, create 2 columns across the page, StudentID, StudentName and write down 1,2,3 for the StudentIDs under the StudentID column and make up some cool names like 'Dr Cheat' under the StudentName column.

On the second piece of paper, create 3 columns called StudentID, ClassID, Score and enter 1,2,3 for the StudentID and 1,1,1 for the ClassID then make up some scores - like 100%, 99%, 0%.

On the 3rd piece of paper create 2 columns - ClassID, ClassName and enter the values 1, 'How To Win by Cheating'

Now, to understand relational databases, all you need to do is on the 4th piece of paper enter the columns StudentID, StudentName, ClassID, ClassName, Score and then populate the values from the other 3 pieces of paper.

You can see you have 3 students, and some scores, and some class names for those scores - the exercise of joining them together into that 4th piece of paper is similar to writing a SQL query.

Whenever you find yourself holding 2 pieces of paper together and looking back and forward between the 2 pieces to find matching values - the column you are matching on is your JOIN column.

That's relational database design.


As always, the documentation is an excellent reference.  In here, it shows WHERE condition and condition is defined (further down the page) as

  where condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.

HAVING, as John pointed out in a comment, is for filtering on aggregates after they have been calculated, while WHERE is for filtering the rows that are gathered at the start. For example:

SELECT
  type, COUNT(type)
FROM table
GROUP BY type
HAVING COUNT(type) > 1

would return a set of rows where the COUNT(type) result ended up being more than 1..  The filtering done by HAVING takes place after the DB has already grouped the entire table by TYPE and calculated all COUNT() values. On the other hand

SELECT
  type, COUNT(type)
FROM table
WHERE type = 'EXAMPLE'
GROUP BY type

would return one row (where type is EXAMPLE) and the count of how many rows contained that type. The filtering here takes place AS THE DB COLLECTS THE ROWS up front .. Any row that doesn't have type 'EXAMPLE' is thrown out. Only one COUNT() is calculated in this instance.

One more complex example to show that the two are evaluated separately and can be combined in any query.

SELECT
  col_1, col_2, col_3, SUM(col_4), COUNT(col_4), AVG(col_4)
FROM table
WHERE col_2 IN ('A', 'B', 'C')
  AND col_3 > 3
GROUP BY col_1, col_2, col_3
HAVING COUNT(col_4) > 2
  AND AVG(col_4) > 10.5


Find max and min date over multiple fields of the same record

If I understood you correctly, I'd use CROSS APPLY with VALUES to "unpivot" your fields and then a standard MIN and MAX can be used. Something like this:

DECLARE @T TABLE (ID int IDENTITY, f1 int, f2 int, f3 int, f4 int);

INSERT INTO @T (f1, f2, f3,f4) VALUES
(1, 2, 3, 4),
(5, 6, 7, 8),
(7, 8, 0, 1),
(2, 3, 6, 5);

SELECT ID, MIN(f) AS MinF, MAX(f) AS MaxF
FROM
    @T
    CROSS APPLY
    (
        VALUES (f1), (f2), (f3), (f4)
    ) AS CA(f)
GROUP BY ID
ORDER BY ID;

Or without CROSS APPLY:

SELECT
    ID
    ,(SELECT MIN(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MinF
    ,(SELECT MAX(f) FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)) AS MaxF
FROM @T
ORDER BY ID;

One more variation of the same theme. Aggregate within CROSS APPLY.

SELECT ID, MinF, MaxF
FROM
    @T
    CROSS APPLY
    (
        SELECT MIN(f) AS MinF, MAX(f) AS MaxF
        FROM (VALUES (f1), (f2), (f3), (f4)) AS Fields(f)
    ) AS CA
ORDER BY ID;

SQL Fiddle

All variants return the same result

+----+------+------+
| ID | MinF | MaxF |
+----+------+------+
|  1 |    1 |    4 |
|  2 |    5 |    8 |
|  3 |    0 |    8 |
|  4 |    2 |    6 |
+----+------+------+

The last variant has a better-looking execution plan for this simple table.


Unable to re-install an instance using the original instance name

The cleanest solution IMHO will be to remove reporting services, too, as part of cleaning the old instance off the machine. Until you've cleaned off everything service-related, that instance name still "exists" - and it can be a really thorny and frustrating experience trying to remove all the registry entries, GUID by agonizing GUID.

I suspect that SQL Server must keep some things around e.g. for connectivity to SSRS from external sources via the instance name. This is why you can't just pave a new instance over top of it.

The other alternative would be to leave your SSRS instance intact, but install the new instance with a different name, and just update application connection strings, aliases, DNS etc. so that they will connect to the new instance name (hopefully this change will be as transparent as possible).


Multiple Similar Tables vs One Master Table

Create the Animal table which has information that all animals share. Add another column to the Animal table called type or something similar. In this, specify whether the animal is a cat, dog, mouse, or whatever.

Then, create Cat, Dog, and Mouse tables that have the Animal ID (The primary key) as a foreign key. Each of those tables has columns specific for Cats, Dogs, and Mice specifically. The Mouse table might have a column Favorite_cheese or some such, which the normal animals table would not. To get the full mouse record, you can join the two tables together.

This allows you to have a relatively lean Animal table while still supporting subclassing functionality.


Can I prevent SQL 2008 Server Management Studio's intellisense from autocompleting with the longest word?

I find it easier to just disable IntelliSense, the more I use it, the less I like it. Although before I tried it, I loved it :)

Just better to learn to hand-code things correctly. Mostly IntelliSense intefere's with that


What is the difference between text and pg_catalog.text?

They are the same thing in a default installation.

pg_catalog.text is the schema-qualified version of the type name text.

The search_path always contains pg_catalog automatically. Per documentation:

  In addition to public and user-created schemas, each database contains
  a pg_catalog schema, which contains the system tables and all the
  built-in data types, functions, and operators. pg_catalog is always
  effectively part of the search path. If it is not named explicitly in
  the path then it is implicitly searched before searching the path's
  schemas. This ensures that built-in names will always be findable.
  However, you can explicitly place pg_catalog at the end of your search
  path if you prefer to have user-defined names override built-in names.

Bold emphasis mine. If you do such a thing, Postgres will automatically start schema-qualifying names in text-representation, that would otherwise be resolved differently. That's most probably what you see. Check your current search_path with:

SHOW search_path;

Closely related:

How can I fake inet_client_addr() for unit tests in PostgreSQL?
How to create table inside specific schema by default in Postgres?


The docs on UUID_SHORT() are a little bit short on actual detail, and I suspect you are not seeing the complete story on how this function works.

As long as you do not have more than one server in the system with the same bits in the low-order byte of the @@server_id, and you don't set the system clock back in time and then restart MySQL server, and don't generate more than 16.7 million calls to UUID_SHORT() per second for every second of every minute that the server is running, then the values will be unique.

--- 64 bits of bigint unsigned comprising the initial value ----
6666555555555544444444443333333333222222222211111111110000000000
3210987654321098765432109876543210987654321098765432109876543210
|------||------------------------------||----------------------|
@@server_id     unix_timestamp of          monotonic counter
low-order      server startup time         starts at 0, increments
byte                                       up +1 with each call,
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< overflows this way
                                           when it rolls over
                                           past 24 bits

The counter is initialized as shown above with each server start, and generates a value incremented by 1 each time UUID_SHORT() is called.  It does not increment the unix_timestamp portion of the value for each tick of the wall clock -- it overflows the counter up into that portion of the value, effectively conserving this resource (of usable values) and avoiding collisions caused by a normal server restart.

Thus, if you don't generate more than 2^24 (16,777,216) calls per second every second, then the next time the server starts, it will initialize at a much higher value (relatively speaking) than where it left off.  Setting the system clock back in time would also be unlikely to be problematic unless you set it back close enough to the time of the prior server restart to cause overlapping values.


Does XP_API deprecation announcement mean that sp_executesql will not be supported in the future?

The Deprecated Database Engine Feature List for SQL Server 2017 shows XP_API as the "feature name" for a list of now-unsupported features.  The list of deprecated features consists of:

srv_alloc
srv_convert
srv_describe
srv_getbindtoken
srv_got_attention
srv_message_handler
srv_paramdata
srv_paraminfo
srv_paramlen
srv_parammaxlen
srv_paramname
srv_paramnumber
srv_paramset
srv_paramsetoutput
srv_paramstatus
srv_paramtype
srv_pfield
srv_pfieldex
srv_rpcdb
srv_rpcname
srv_rpcnumber
srv_rpcoptions
srv_rpcowner
srv_rpcparams
srv_senddone
srv_sendmsg
srv_sendrow
srv_setcoldata
srv_setcollen
srv_setutype
srv_willconvert
srv_wsendmsg

These items are all part of the Extended Stored Procedure API, which is deprecated.

The documentation for sp_executesql makes no mention of it being deprecated.  Although that might be an error of ommission, it seems very unlikely that a core piece of functionality will be deprecated.  Imagine all the code world-wide that would need to be retrofitted.

The deprecation-related Extended Events items are likely flagging some internal use by sp_executesql of one or more of the xp_api items listed above.  Since sp_executesql is an extended stored procedure implemented in the Engine, Microsoft will either re-factor the sp_executesql code to avoid calls to those deprecated events, or will not deprecate them for internal use.

This question is tagged with SQL Server 2008 R2; if you run an Extended Events session in SQL Server 2017 looking at the deprecation_announcement and deprecation_final_support events, you don't see sp_executesql firing events.  To test that, I used this session definition:

CREATE EVENT SESSION [deprecated events] ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (
        package0.event_sequence
        ,sqlserver.client_app_name
        ,sqlserver.database_name
        ,sqlserver.is_system,sqlserver.sql_text
        )
)
, ADD EVENT sqlserver.deprecation_final_support
    (
        ACTION
        (
            package0.event_sequence
            ,sqlserver.client_app_name
            ,sqlserver.database_name
            ,sqlserver.is_system
            ,sqlserver.sql_text
        )
    )
ADD TARGET package0.ring_buffer (SET max_memory=(10240))
WITH (STARTUP_STATE=OFF);
ALTER EVENT SESSION [deprecated events] ON SERVER STATE=START;
GO

Then ran this code, which uses sp_executesql:

DECLARE @cmd nvarchar(max);
SET @cmd = 'SELECT CurrentDate = GETDATE();';
EXEC sys.sp_executesql @cmd;

The ring buffer output for the [deprecated events] session shows no messages as a result of executing the code.  However, if you run this code, which contains sp_helpremotelogin, which is on the deprecated list, you do see items in the ring-buffer output:

EXEC sp_helpremotelogin @remoteserver = 'test', @remotename = NULL;


Order In WHERE Clause Matter For SARGability?

In theory, and particularly in modern versions of SQL Server, the order of the WHERE clauses makes absolutely no difference. SQL Server will generally process the filters in the most efficient order, and will pick the index that it determines will allow for the most efficient query. A difference that may occur is when the WHERE clause includes JOIN criteria or applies filters to outer joined columns; other differences can come into play when options like FORCE ORDER or specific trace flags are in use.

Re-writing as a CTE to "force" SQL Server to process a filter inside the CTE first doesn't work - SQL Server is going to re-write it anyway, and process it in the order that it deems best. This is why, for example, you can't escape invalid conversion errors by using a CTE or a subquery to first filter out the bad data - SQL Server might still present the bad data to the conversion before any filter does anything.

(Erland complained about this in Connect #537419.)

So, again generally, don't re-write your SQL code to try to out-smart the optimizer or prevent it from doing something stupid. There are occasions where it can pick the wrong index that might be resolved by changing the query itself, but deal with those in situations where you already believe that the optimizer has picked the wrong index (and before solving those, eliminate more likely causes like parameter sniffing, bad statistics, etc.).


Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE?

I tested this:
CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t` text,
  PRIMARY KEY (`id`),
  KEY `t` (`t`(768))
) ENGINE=InnoDB

I filled it with some thousands of rows of random data.
mysql> show table status like 'mytable'\G
*************************** 1. row ***************************
           Name: mytable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 95030
 Avg_row_length: 5660
    Data_length: 537903104
Max_data_length: 0
   Index_length: 190742528
      Data_free: 5242880

Dropped the index on the text column:
mysql> alter table mytable drop index t;

Refreshed information_schema statistics:
mysql> analyze table mytable;

And checked the table status:
mysql> show table status like 'mytable'\G
*************************** 1. row ***************************
           Name: mytable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 95030
 Avg_row_length: 5660
    Data_length: 537903104
Max_data_length: 0
   Index_length: 0
      Data_free: 195035136

The space that used to be taken by the index is now in data_free. The file size has not shrunk.
This is consistent with other behavior of InnoDB, that a tablespace file never shrinks, the only thing that gives you back more disk space is if you copy the data to a new tablespace file. That requires enough free disk space to hold both the old and the new tablespace, temporarily.
So if you don't have enough free disk space to do this, your only recourse is to make the whole storage volume larger using a virtual disk, or else move the whole MySQL datadir to another server that already has a larger disk.


Consolidate to multiple or single instance SQL Server on a VM with many third party vendor databases?

Obviously an opinion here, but...
Personally, I think separate instances is definitely the way to go.  We have several servers at my work like that.  You have so much more control of stuff, especially server related objects like linked servers, etc.  Being able to restart one instance and leave the others alone is a valuable benefit.  And you can upgrade each instance independently as the vendor allows


Hyperthreading & MySQL InnoDB Thread Concurrency Performance

By default, innodb_thread_concurrency is 0. That's actually the best setting. It means infinite concurrency. It allows the InnoDB storage engine to decide the best number of concurrency tickets to launch and address. Setting it to a nonzero value actually can throttle InnoDB or throttle the OS if not set properly.

I have written many posts in the DBA StackExchange about this setting:

May 26, 2011 : About single threaded versus multithreaded databases performance
Aug 16, 2011 : Why do MySQL threads often show "freeing items" status when the query cache is disabled?
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Aug 03, 2012 : Slow insert performance in mysql innodb table for solr indexing


You cannot mix and match resolvable and non-resolvable host addresses in your cluster.

When you started your config servers and another mongos, you had told them their hostnames are X.X.X.X (the IP you masked in your error message).   You are now trying to start a mongos using localhost for the host name/IP - that is not allowed.

You must use identical configdb string to every mongos every time in a sharded cluster.


postgresql on windows won't install with data and executables on different disks

The problem was not with Postgresql, but rather with the permissions on the second disk.

Enabling the relevant users to write on the disk (at the disk level) solved the situation.

Sorry for the noise, but I thought to post an answer, since it appears this kind of problem has fooled quite a bit of people on the Net. :)


DB STARTUP command active for an online database

As confirmed by you the SQL server is of enterprise version and database was not starting after restore so the recovery process can utilize fast recovery.  In enterprise edition the database would come online after second phase (this is called Fast recovery which was introduced from SS 2005 onwards) and you would see database as online but still internally third phase, the UNDO, phase is going on and would be accessible completely only after third phase completes. What I mean by completely is For fast recovery, a query may bump into one of the locks being held to allow fast recovery – in which case it will have to wait for that lock to be dropped as UNDO progresses

I suggest you also read

When is fast recovery used
3 phases of recovery


How to solve the error Msg 547, Level 16, State 0, Line 1

If you are trying to make foreign key and your child table already have some data that does not match with your parent table, you will get this error message.

For Example:

CREATE TABLE MyParent(Job_id int PRIMARY KEY,Job_Name Varchar(50));

INSERT INTO MyParent Values(1,'CEO');
INSERT INTO MyParent Values(2,'Business Manager');
INSERT INTO MyParent Values(3,'DBA');

CREATE TABLE MyChild(Emp_id int,Emp_Name Varchar(50),Job_Id int)

INSERT INTO MyChild Values(1,'Lavanya',1);
INSERT INTO MyChild Values(1,'JP Chauhan',3);
INSERT INTO MyChild Values(1,'Rakesh',5);

See the last record of MyChild table that has value in Job_Id is 5, which is not in MyParent table.

If you try to make foreign key relation like:

ALTER TABLE MyChild
ADD CONSTRAINT fk_My_foreignKey FOREIGN KEY(Job_Id)REFERENCES MyParent(Job_Id);

The error message you will get:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_My_foreignKey". The conflict occurred in database "mydatabase", table "dbo.MyParent", column 'Job_id'.


Insert in to table without duplicate records TSQL

INSERT INTO table1 (ID, Name, pin)
SELECT table2.ID, table2.Name, table2.pin
FROM table2
LEFT JOIN table1 ON t1.Name=t2.Name
WHERE t1.Name IS NULL

or
INSERT INTO table1 (ID, Name, pin)
SELECT ID, Name, pin
FROM table2
WHERE NOT EXISTS ( SELECT NULL
                   FROM table1
                   WHERE t1.Name=t2.Name )

If table1.ID value is an identity and must not be inserted but generated then replace ID in SELECT with NULL.


Display values from two tables with multiple conditions

This should work:

SELECT table2.course, MIN(table2.date)
  FROM table1 INNER JOIN table2
    ON table1.course = table2.course
  WHERE table2.flag = 1
    AND table2.date > NOW()
  GROUP BY table2.course
  ORDER BY MIN(table2.date)

For your current dataset, you don't even need to JOIN with table1.


Geography union via cursor returning null

Try inside the loop:

SET @ShapeUnion = COALESCE(@ShapeUnion.STUnion(@Shape), @Shape);

(Or initializing @ShapeUnion somehow first.)

You'll also want to filter the cursor query to not include any rows where geom is NULL (if that is possible in your data model).

You can also do this without a cursor, I think:

DECLARE @ShapeUnion GEOGRAPHY;

SELECT @ShapeUnion = COALESCE(@ShapeUnion.STUnion(geom), geom)
  FROM dbo.Boundaries_Staging
  -- WHERE geom IS NOT NULL
  ;

SELECT @ShapeUnion;


Enforcing lowest transaction isolation level across concurrent sessions?

There is no danger of locking with your scenario.

The only lock that is taken on facttable is the ACCESS SHARE lock that prevents others from dropping or altering the table while it is being read. Since ACCESS SHARE doesn't conflict with itself, that is no problem (two people can read the same poster).

The only consideration is the I/O load: if many processes perform a sequential scan on the table, that could be substantial. You should try to benefit from PostgreSQL's synchronized sequential scans by starting all processes at the same time, so that they have a chance to synchronize their sequential scans.


PostgreSQL relation size does not sum up

Does table have any variable-width columns?
If the answer is yes, you are probably missed toast tables (pg_toast_xxx). You need to find their names (not sure how to do it, need to google it) and calculate their size as well.

To reduce space (for example after deleting bunch of rows) execlute VACUUM FULL  and reindex table after it (to prevent index fragmentation).


I think just -- exporting db and taking out the json file and import in another system.

Or You can directly copy the db from one location to another----
http://docs.mongodb.org/manual/reference/method/db.copyDatabase/


How do I count rows with two properties in one index scan in SQL?

SELECT
    ItemCreationDate
  , SUM(CASE WHEN ItemUnitsCount <> 0 THEN 1 ELSE 0 END) AS CountType1
  , SUM(CASE WHEN ItemUnitsCount = 0 THEN 1 ELSE 0 END) AS CountType2
FROM
    dbo.Items
GROUP BY
    ItemCreationDate


Which version of AdventureWorks has a column called CustomerId on sales.vIndividualCustomer?

SQL Server 2005 version of Adventureworks has CustomerId and Phone.

The schema is changed from 2008 for Sales.vIndividualCustomer view :


Depends on who you ask.

Is it in terms of new features, etc.? For example, a 32-bit RDBMS system could be considered obsolete nowadays, because 64-bit is common, and most engines run safely on a 64-bit platform, with access to more memory.

Is it in terms of scaling? For example, an old design that doesn't scale well, might be considered obsolete.

A database with queries that use old-style joins, could be considered obsolete, and in this case would prevent upgrades to newer technology without changing the code.

Some wonks like to say that SQL itself is obsolete, that NoSQL technology is better suited to certain problem areas like unstructured data. I personally wouldn't say this is the right use of "obsolete", but that could be an example too.


which one is the fastest way of moving database form local to live server in sql server

You can use backup/restore. Take a backup of your dev db and restore it on the live server. Should be a bit faster than 2 hours.


Nested case statements vs multiple criteria case statements

Someone may come along and dig into the details of how these operations are actually processed on the CPUs, or what instructions get generated differently between those two queries.  But from a practical standpoint, it seems that there's not a significant difference.

This loads up a table with 10 million rows of two bit columns, with a somewhat arbitrary distribution of 1 and 0 values.

CREATE DATABASE [239583];
GO

USE [239583];
GO

SELECT TOP 10000000
    CASE WHEN v.number % 2 = 0 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS a,
    CASE WHEN v.number % 2 = 1 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS b
INTO dbo.TableName
FROM master.dbo.spt_values v
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;

I then told SSMS to discard results after execution (Query -> Query Options -> Grid -> Discard results after execution), because I don't have time to wait on SSMS to crash loading 20mm rows of 1s and 0s.

Then ran this code:

SET STATISTICS IO, TIME ON;

Select
case
    when a=1 and b=0 THEN 'True'
    when a=1 and b=1 then 'Trueish'
    when a=0 and b=0 then 'False'
    when a=0 and b=1 then 'Falseish'
    else null
end
FROM tableName;

Select
case
    when a=1 then
        case
            when b=0 then 'True'
            when b=1 then 'Trueish'
        end
    When a=0 then
        case
            when b=0 then 'False'
            when b=1 then 'Falseish'
        end
    else null
end
FROM tablename;

I typical run has CPU output like this on my machine:

Query 1: CPU time = 2141 ms,  elapsed time = 2191 ms.
Query 2: CPU time = 2359 ms,  elapsed time = 2461 ms.

Sometimes they are slightly closer to equal, or the second query is slightly faster, but I think those are just the usual variations of the CPUs on my laptop doing other stuff than just running this query.


What's a good tool for visualizing SQL Agent jobs?

Did some googling around and here are some software that may interest you:

SQL Job Manager
https://www.idera.com/productssolutions/freetools/sqljobmanager
Query Currently Running SQL Server Agent Jobs
http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/

If anyone has more links to add, go ahead!

For point 3, the link query and notes are here -

Today I went on a quest to discover a way to list all currently running SQL Server Agent jobs. As every other quest, this one also started with Google-ing. :)

Within 2 minutes I found this great post by Brent Ozar on SQLServerPedia. Why is this post so great? It is so great because Brent figured that if you query only the sysjobs and the sysjobhistory tables you will not get accurate current job status. I.e. in the sysjobhistory table you have a column run_status, however (even though according to the BOL the possible values for this column are “Status of the job execution: 0 = Failed,1 = Succeeded,2 = Retry,3 = Canceled,4 = In progress”) in reality, the value will never be 4 (In Progress). Actually, in the sysjobhistory table is kept historical data of each job step executed, which means that the status of the step is updated only after the next step is executed. In other words, the table is NOT updated in real time, nor every other second.

So, Brent figured out that there is a undocumented stored procedure sys.xp_sqlagent_enum_jobs, which is part of sp_help_job, which can give the current execution status of the Agent Job.

Even though I found a way to get the currently running jobs, I was not happy with this script because it runs only on SQL 2005 / 2008.

What should I do if I have a SQL 2000 instance and am very curious about the currently running jobs?

With a bit of Tim Chapman’s help (the master of www.SQLServerNation.com) I figured out how to do it. THANK YOU, Tim!

Here is the final script, which will run on SQL 2000, 2005 and 2008, and which will give you the currently running SQL Server agent jobs. (As you can see, the difference with Brent’s script is very little: instead of “sys.xp_sqlagent_enum_jobs” I am using “master.dbo.xp_sqlagent_enum_jobs” and I am limiting the resultset to get only the currently running jobs by using “where x.running = 1″).

Simple as it is. Enjoy.

IF EXISTS (SELECT *
FROM    tempdb.dbo.sysobjects
WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]

--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time    = right ('000000' + last_run_time, 6),
next_run_time    = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date &gt; 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration &gt; 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM          [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON     x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where x.running = 1


Can Microsoft SQL Server be installed on Ubuntu 18.04 LTS?

Supported

No, Microsoft only supports 16.04 LTS. The version that's two years old.

Installation

In order to install SQL Server on Ubuntu whether new or old, I suggest installing it as a chroot. To do that first install debchroot, and then use that utility to set up a chroot.

sudo apt install debchroot
sudo debootstrap --arch=amd64 xenial /opt/mschroot http://archive.ubuntu.com/ubuntu/

You'll have to --bind mount your host's /proc

sudo mount --bind /proc /opt/mschroot/proc

Now jump into your chroot

sudo chroot /opt/mschroot

And set out to configure it,

apt update

## Add the Universe Repository (needed https://dba.stackexchange.com/q/212868/2639)
apt install wget software-properties-common apt-transport-https
sudo add-apt-repository "deb http://archive.ubuntu.com/ubuntu $(lsb_release -sc) universe"
apt update

## Add Microsoft Repository
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"
apt update

## Configuration complete begin install
apt install -y mssql-server

Then configure it, (use option 2 for Developer)

/opt/mssql/bin/mssql-conf setup

You'll have to pick a password, note for reasons unexplained The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.. I suggest everyone use MICROSOFTsucksH4RD, as it satisfies that. Lastly, don't forget to disable the spyware.

/opt/mssql/bin/mssql-conf set telemetry.customerfeedback false


How to get Row Value & Sum/ Aggregate in Same row? (SQL Server)

Yes, there is a way. It's called "window aggregate functions":

select
    A.name, A.score,
    sum(A.score) over () as total
from myTable A ;

The OVER () means that the aggregate (sum in this case) is over the whole result set. If you wanted the total over say different names (assuming you had rows with same name, you could use OVER (PARTITION BY name).


Powershell script executed by SQL Server doesn't take effect

xp_cmdshell is not PowerShell.
If you run: xp_cmdshell 'whoami.exe'; you will discover that you are nt service\mssqlserver and not Administrator.

xp_cmdshell is impersonating that user (which is a service in our case).
So the solution of the problem should be to impersonate an Administrator.
But you cannot do that.
I have tried using Proxy Credentials:
EXEC sp_xp_cmdshell_proxy_account 'ADVWKS\Max04', 'ds35efg##65';
GO

But I couldn't impersonate Administrator.
I have also tried adding this code at the beginning of the script:
if (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) { Start-Process powershell.exe "-NoProfile -ExecutionPolicy Bypass -File `"$PSCommandPath`"" -Verb RunAs; exit }

But the code just run forever because is expecting you to click on the YES and impersonate the Administrator.
I have also tried to run it from an Agent job: no joy.
This guide says not to impersonate a domain Admin. Which is exactly what you want to do. I let you try that because I cannot do that on my PC.
For start debugging try to run this:
runas /user:domain\administrator $^

and check if you can at least be Administrator on that machine.
So the recipe is:

Try to impersonate Administrator and execute the .ps1 script from command line
In alternative add a user with Administrator privileges
Impersonate it through SSMS

Good luck.


Can I rename the values in a MySQL ENUM column in one query?

The follolwing technique I am about to show you will require guts of steel.

Given the following criteria

datadir is /var/lib/mysql
table is mydb.mytb
enum column called is called enum_col
engine is MyISAM

Here is a death-defying crack at it:

CREATE TABLE mydb.mybt LIKE mydb.mytb;
ALTER TABLE mydb.mybt MODIFY enum_col ENUM('First value','Second value');
SET wait_timeout=86400; SET interactive_timeout=86400;
FLUSH TABLES WITH READ LOCK;
In a separate OS/SSH Session, swap the .frm files

$ mv /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/myxx.frm
$ mv /var/lib/mysql/mydb/mybt.frm /var/lib/mysql/mydb/mytb.frm
$ mv /var/lib/mysql/mydb/myxx.frm /var/lib/mysql/mydb/mybt.frm

UNLOCK TABLES;
DROP TABLE mydb.mybt;

That's it !!!

CAVEAT : I CANNOT TAKE CREDIT FOR THIS !

This technique comes from "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

  The technique we are about to demonstrate is unsupported,
  undocumented, and may not work. Use it at your risk. We advise you to
  back up you data first!

Give it a try ! (Please let us know how it turned out)

UPDATE 2011-10-05 17:49 EDT

If the table is MyISAM and you have enough space in production and a straight-up downtime window, try this:

service mysql restart --skip-networking
In a separate OS/SSH Session, make a copy of the table

cp /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/mytbplay.frm
cp /var/lib/mysql/mydb/mytb.MYD /var/lib/mysql/mydb/mytbplay.MYD
cp /var/lib/mysql/mydb/mytb.MYI /var/lib/mysql/mydb/mytbplay.MYI

INFORMATION_SCHEMA.TABLES will automatically detect the presence of the new table called mydb.mytbplay.

Perform the guts-of-steel algorithm on mydb.mytbplay
You test the integrity of mydb.mytbplay

If you are satisfied

ALTER TABLE mydb.mytb RENAME mydb.mytb_backup;
ALTER TABLE mydb.mytbplay RENAME mydb.mytb;
service mysql restart

Give it a try!


There is no inherent order inside a table.

There is no pre-stablished order when you insert rows. If run for example the following query:

SELECT A.A, B.B
FROM   tblA A
       JOIN tblB B ON A.id = B.id

Without an ORDER BY, then no particular order will be used. If you want some particular sorted results, you must use an ORDER BY clause. You could use a primary key and sort according to its values for example. And doesn't matter if you add one or more JOIN to the statement, same answer, without ORDER BY you are not assure to get same order each time you run the query.

There are several questions/answers that could help you to get a better understanding of this particular point:

a really deep and detailed answer here on dba.stackexchange.com: Storage order vs Result order
You cannot guarantee the order unless you specifically provide an order by with the query
In the SQL world, order is not an inherent property of a set of data.
Without ORDER BY, You Can’t Depend On the Order of Results great blog entry by Michael J. Swart


How to pass a newly INSERTed row to a function?

A trigger requires a trigger function. And those can only be written in PL/pgSQL.

So you need something like this:

CREATE OR REPLACE FUNCTION schema1.clone_to_table2()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  INSERT INTO schema2."table2" ("uid, "did")
  VALUES(new.uid::text, new.did);
  return new;
END;
$function$
;

BEGIN does not require a ; at the end.

And then a trigger definition to go with that

CREATE TRIGGER on_insert
  AFTER INSERT ON schema1.table1
  FOR EACH ROW
  EXECUTE PROCEDURE schema1.clone_to_table2();


Why do I have two collations in mysqldump, under stored procs?

(Some of this answer is 'guesses'.)

Each database has a default charset and collation.  That is used only for providing the charset and/or collation for a CREATE TABLE when you don't explicitly provide it for the table.  (And perhaps for Stored Routines.)

Why are there two ALTERs in that dump?  I don't know.  Perhaps it would make sense if there were a CREATE PROCEDURE between them.

My point is that your question is about something that is essentially irrelevant.

The SHOW VARIABLES output does not say "The database seems all utf8".  It does say that certain defaults are utf8 and that connections will assume the client is using utf8 unless otherwise specified.


Shrinkfile on a datafile is a single-threaded operation, reusing a small memory buffer.

So the Ninja hardware hasn't got an edge with the extra memory and the 80 cores.

Your local PC however has the benefit of local I/O latency (local disk, i.e. not having to make multiple trips to the SAN).


SELECT id,name,address,telephoneno,mobileno
FROM tble_contactlist
WHERE groupid= (SELECT MAX(id) FROM tbl_Contactgroup)

Try this.. it will give you the data for all the value in contact list for the last inserted value in contactgroup


Raise error from Stored PROC in SQL Server PDW

I don't know if this will work with PDW specifically, but I've got an awful, dirty hack I use with user-defined functions, which also don't allow RAISERROR. Just attempt to cast a varchar literal containing your error message to int.

SELECT CAST('Carburetor failure detected.' AS int)

It's not the prettiest error output, but it's better than nothing.


Sensible database backup retention policy?

There are three questions that drive answers here:

How much space is the business willing to pay for to store backups?
Are there legal requirements to store backups for a certain period of time?
How often are you checking for corruption (running DBCC CHECKDB)?

For larger amounts of data, the first question usually answers the question straight away. If there are legal requirements, that can also answer things pretty quickly.

If neither of those impose a limit, then the third question is a good starting place.

Say you run DBCC CHECKDB and find corruption -- you're going to want a full backup to use in case you can't fix things in-place. If you're running it once a week, then keeping two weeks worth of backups is probably a good idea.

If you're not running CHECKDB, well, go do that instead of this.


Raid 1 or Raid 1+0 for SQL Server Architecture

RAID level 10(1+0) has been recommended by microsoft for high reads. Refer the doc: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190764%28v%3dsql.105%29

This level is also known as mirroring with striping. RAID 10 uses a striped array of disks that are then mirrored to another identical set of striped disks. For example, a striped array can be created by using five disks. The striped array of disks is then mirrored using another set of five striped disks. RAID 10 provides the performance benefits of disk striping with the disk redundancy of mirroring. RAID 10 provides the highest read-and-write performance of any one of the other RAID levels, but at the expense of using two times as many disks.

RAID levels higher than 10 (1 + 0) may offer additional fault tolerance or performance enhancements. These levels generally are proprietary systems. For more information about these types of RAID systems, contact the hardware vendor.


What is the best way to store files metadata in DB?

What is approx. size of the data? Your no. of rows being a concern here
How often does this data get updated? Consider data access and ACID transactions computation costs in your architecture.
Files metadata will have directories and subdirectories - which are dynamic in nature. Hence schematic representation(RDBMS table) may not be suitable. My initial impression says NoSQL may be a better candidate.
Choose the NoSQL database that suits best - Document DB, Key-Value pair, or Graph database if the files are interlinked (refer to each other) and you need to retrieve that relation
If you need to use RDBMS unless it is too old, many of them offer JSON capabilities inbuilt, please consider the same.


How to make a query on multiple columns without writting all possible permutations?

You don't need loops, but one CASE per searched value:

select ...
from
 (
   select ...
      case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches
   from tab
 ) dt
where matches > 0
order by matches desc


Stored Procedure error “Must declare the scalar variable”

You're not declaring either @numOfOffset or @numOfRows variables within the context of your Dynamic SQL statement.  If you copy the output of your PRINT @qaeryMain statement, you'll notice the distinct lack of any DECLARE statements, this is the reason why you're getting the error.

If you just change your code to the following, it should resolve your issue as it will specify literals within your DSQL statement instead of undefined variables.

IF (SELECT 1 FROM sys.procedures WHERE [name] = 'sp_targil1') = 1
    DROP PROCEDURE sp_targil1;
GO

CREATE PROCEDURE sp_targil1 (@tableName varchar(30), @fieldNameAggBy varchar(30), @fieldNameToAggOn varchar(30)
    ,  @numOfOffset int, @numOfRows int
    )
AS
BEGIN
    DECLARE @query1 varchar(500)
    DECLARE @qaeryMain varchar(500)
    DECLARE @maxNumberOfRows int

    SET @qaeryMain =
    '
    SELECT ' + @fieldNameAggBy + ', SUM(' + @fieldNameToAggOn + ') AS ' + @fieldNameToAggOn + '_Sum_By_' + @fieldNameAggBy +
    ' FROM ' + @tableName +
    ' GROUP BY ' + @fieldNameAggBy +
    ' ORDER BY SUM(' + @fieldNameToAggOn + ') DESC OFFSET ' + CAST(@numOfOffset AS VARHCAR(50)) + ' - 1 ROWS FETCH NEXT ' + CAST(@numOfRowsAS VARHCAR(50)) + ' ROWS ONLY'
    PRINT @qaeryMain
    EXECUTE (@qaeryMain)
END