Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

On SQL Server 2005, and SQL Server 2012, I did the following:

USE tempdb;
CREATE TABLE ImpTest
(
    ImpTestID INT NULL
    , ImpTestDate DATETIME NULL
);
GO
BULK INSERT ImpTest FROM 'C:\SQLServer\ImportTest.txt' WITH (FIELDTERMINATOR=',');
SELECT * FROM ImpTest;
DROP TABLE ImpTest;

With the following data:

1,
2,20130406
3,NOT_DATE
4,
5,20130409

On both servers, I received the following:

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified
codepage) for row 3, column 2 (ImpTestDate).

Perhaps, as Aaron noted, this is a bug that needs to be addressed through http://connect.microsoft.com

I'm wondering about some of the details of your system, including locale settings, collations, physical format of the import file (is it from a Unix system? etc).


How to find existing instances "instance root" (MS SQL)?

Found it by comparing several articles and looking at existing file structure and registry under:   HKLM\software\Microsoft\Microsoft SQL Server\MSSQL10.INSTANCEID\Setup
There are a few "path" and "dir" references, most of them quite long. The common denominator seems to be the SqlProgramDir key, for all the existing instances. In our case the DBA decided to put them all under "D:\SQL\Data\"


SQL Server Management Studio 2012: How to have folders in the SSMS project?

Finally, subfolders were made possible in SSMS 2016.

Cautious users: You can install SSMS 2016 and still keep using old one. See note [1] below.

Once there, start using solutions instead of projects.

Subfoldering works like this:

Adding solution subfolder:

Right-click solution item (tree root) and from menu select Add > New Solution Folder.
Step 1 now works also on any folder you added.

Adding SQL script:

Right-click any folder and from its Add menu, either add existing SQL file or new SQL file (in the list, find item type which has SQL extension).

Your Good old projects (Bad old projects)

You can still host projects in your solution if you see them beneficial for some legacy reasons, for hosting connections, etc.  Just add new or import existing projects into the solution.

[1] Regarding upgrading:

SQL Server 2008 - 2016 are officially supported by SSMS 2016. See more information at download page. For older versions, you can keep your favourite old SSMS, see point 2 below.

the only setting which might require your attention is Options > SQL Server Object Explorer > Script for server version > (set your server version)

Different SSMS versions live well together, i.e. you can have SSMS 2008 and SSMS 2016 installed together without issues – tested.


Moving MySQL databases/folder to another disk

Yes, you can here is the steps to do it.
backup:

# service mysqld stop
# cd /var/lib/mysql
# tar -cvzf /tmp/mysql-backup.tar.gz ./*
# service mysqld start

restore:

# service mysqld stop
# cd /data/mysql
# tar -xvzf /tmp/mysql-backup.tar.gz
# service mysqld start

Notes:

Cold backup will not work if you try to restore it in older versions of MySQL it will be better if you use the same version.
You need to make sure new directory is owned by MySQL user and it has the needed permission
New datadir is accurate inside my.cnf


what is the fastest way to import data into mysql 5.7

Maybe this blog can help you about how to quickly restore MySQL logical backups.Click here


Is it possible to separate the application of statements inside database transactions?

You need something like

UPDATE tab1 set value =
   CASE value
     WHEN 0 THEN 1
     WHEN 1 THEN 4
     WHEN 2 THEN 0
   END;

If you need to treat values which are not listed in CASE in a different way, say don't change it ,  you need to add ELSE to CASE as  ypercubeᵀᴹ pointed out:

UPDATE tab1 set value =
       CASE value
         WHEN 0 THEN 1
         WHEN 1 THEN 4
         WHEN 2 THEN 0
         ELSE value
       END;

Or you can create a new table, insert  all mappings there,
and then (assuming mapping is unique)

UPDATE  tab1 t SET value =
 (SELECT new_val from mapping_table t2 WHERE t2.old_val = t.value)


Two composite foreign keys have identical columns which must be equal value

I think your issue is a modeling one, as you skipped a few relations along the way. You can enforce your data consistency exclusively with the schema, no need for code enforcement with triggers or otherwise.
Also, why use surrogate keys for such a model? Try using the natural keys which in your case are short, stable, and familiar, and see hot it simplifies your queries.
Here is my take:

Courses:

Course PK <-- This is the name of the course, the one you use in the 'real world'

Semesters:

Semester PK <-- Summer, Spring, Winter, Fall or whatever you use to distinguish them

Course_Schedules:

Course REF Courses,

Semester REF Semesters,

Year ( in case not all courses are offered in the same semesters every year),

PK (Course, Semester, Year)

Students:

Student PK <-- Whatever you use to distinguish students in the real world

Course_Registrations:

Course,

Semester,

Year,

Student,

FK (Course, Semester, Year) REF Course_Schedules <-- Only 'real' scheduled courses,

PK (Course, Semester, Year, Student) <-- Student may repeat the course in different years

Course_Evaluations:

I think you are missing part of the key here, in the model you showed every course can have only one evaluation...
Who evaluates it? add to PK as needed

Course,

Semester,

Year,

-- other properties --

PK (Course, Semester, Year, {missing attribute?}),

FK (Course, Semester, Year) REF Course Schedules

Course_Student_Evaluation:

Course,

Semester,

Year,

Student,

PK (Course, Semester, Year, Student),

FK (Course, Semester, Year) REF Course_Schedules

Now you can see that since both Course_Evaluations and Course_Student_Evaluations have DRI to Course_Schedules, it enforces everything you asked for, and more..

HTH


Keeping version history of functions in PostgreSQL

Right now there isn't really a single wonderful option for this.

You need to make sure people use revision control properly and consistently.

Some after-the-fact checks can be used to query for unexpected procedure changes and alert if the pg_proc.prosrc column doesn't match what you have in revision control.

If necessary you can force all changes through a single person's approval. Alternately you can deny access to create and update procedures to all accounts except one - and have that account automatically apply changes that're committed to git via a hook. I don't recommend this though - it'll make operations very restrictive and make it hard to fix unexpected issues.


This solution also gets the intermediate nodes

;WITH RecursiveCTE
AS
(
SELECT  [SUB] ,
        [SUB] as [Top Level Part],
        CAST([SUB] as varchar(255)) as [Parts Nodes Combination],
        0 AS [LEVEL],
        [LEADTIME]  as [Days],
        CAST([LEADTIME] as varchar(255)) as [BASED ON]
FROM dbo.Levels s1
WHERE [LEVEL] = 0

UNION ALL

SELECT  s1.SUB as [Top Level Part],
        RecursiveCTE.[Top Level Part],
        CAST(RecursiveCTE.[Parts Nodes Combination]+ ' >> ' + cast(s1.[SUB] as varchar(255)) as varchar(255)),
        RecursiveCTE.[LEVEL] + 1 as [LEVEL],
        RecursiveCTE.[Days] + s1.[LEADTIME] as [Days],
        CAST(RecursiveCTE.[BASED ON] + ' + ' + CAST(s1.[LEADTIME] as varchar(255))  as  varchar(255))
FROM dbo.Levels s1
INNER JOIN RecursiveCTE
ON s1.[ROOT] = RecursiveCTE.[SUB]
WHERE s1.[LEVEL] > 0
)
SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0;

Result

SUB Top Level Part  Parts Nodes Combination LEVEL   Days    BASED ON
2       1               1 >> 2                  1       6       4 + 2
3       1               1 >> 3                  1       9       4 + 5
7       1               1 >> 7                  1       24      4 + 20
4       1               1 >> 3 >> 4             2       13      4 + 5 + 4
6       1               1 >> 3 >> 6             2       14      4 + 5 + 5
4       1               1 >> 2 >> 4             2       10      4 + 2 + 4
5       1               1 >> 2 >> 5             2       7       4 + 2 + 1

DB<>Fiddle

If you want to filter out the intermediate nodes:

;WITH RecursiveCTE
AS
(
SELECT  [SUB] ,
        [SUB] as [Top Level Part],
        CAST([SUB] as varchar(255)) as [Parts Nodes Combination],
        0 AS [LEVEL],
        [LEADTIME]  as [Days],
        CAST([LEADTIME] as varchar(255)) as [BASED ON]
FROM dbo.Levels s1
WHERE [LEVEL] = 0

UNION ALL

SELECT  s1.SUB as [Top Level Part],
        RecursiveCTE.[Top Level Part],
        CAST(RecursiveCTE.[Parts Nodes Combination]+ ' >> ' + cast(s1.[SUB] as varchar(255)) as varchar(255)),
        RecursiveCTE.[LEVEL] + 1 as [LEVEL],
        RecursiveCTE.[Days] + s1.[LEADTIME] as [Days],
        CAST(RecursiveCTE.[BASED ON] + ' + ' + CAST(s1.[LEADTIME] as varchar(255))  as  varchar(255))
FROM dbo.Levels s1
INNER JOIN RecursiveCTE
ON s1.[ROOT] = RecursiveCTE.[SUB]

WHERE s1.[LEVEL] > 0

)

SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0
AND NOT EXISTS
(
SELECT * FROM
dbo.Levels s2
WHERE RecursiveCTE.SUB = s2.[ROOT])
;

Result

SUB Top Level Part  Parts Nodes Combination LEVEL   Days    BASED ON
7           1               1 >> 7           1      24      4 + 20
4           1               1 >> 3 >> 4      2      13      4 + 5 + 4
6           1               1 >> 3 >> 6      2      14      4 + 5 + 5
4           1               1 >> 2 >> 4      2      10      4 + 2 + 4
5           1               1 >> 2 >> 5      2      7       4 + 2 + 1

DB<>Fiddle

Table & Data used

CREATE TABLE dbo.Levels ([PARTID] INT,[ROOT] int,[SUB] int,[LEVEL] int, [LEADTIME] int,[TOP LEVEL PART] int);
--PartID added
INSERT INTO dbo.Levels
 ([PARTID], [ROOT] ,[SUB] ,[LEVEL] , [LEADTIME] ,[TOP LEVEL PART])

VALUES
(1,1 , 1 ,0 ,4 ,1 ),
(1,1 , 2 ,1 ,2 ,1 ),
(1,1 , 3 ,1 ,5 ,1 ),
(1,1 , 7 ,1 ,20 ,1 ),
(1,2 , 4 ,1 ,4 ,1 ),
(1,2 , 5 ,1 ,1 ,1 ),
(1,3 , 4 ,2 ,4 ,1 ),
(1,3 , 6 ,2 ,5 ,1 );

Edit

Op comments:

  I've noticed the data is repeating a lot (particularly on larger
  assemblies)

  Basically I have Sub Router 1691, on top level router 1731. the Parts
  node combination is 1731 -- 1727 -- 1691 and this repeats exactly the
  same columns 8 times. Then, when it introduces another node (so 1731
  -- 1727 -- 1691 -- 1682), this one repeats 168 times. This is the case all of the way through. It appears that any 3 level nodes repeat 8
  times & any 4 level nodes 168 times.

  On a different one it repeats far fewer times. I'm wondering whether
  it has something to do with the number of unique parts in the tree.

  I think I've figured it out! A single sub can appear on several
  different top level parts. On the join to the recursiveCTE table I've
  also added 'and RecursiveCTE.[Top Level Part] = s1.TopLevelRouter' and
  I think it's done the trick.

Part of the query that needed to be changed:

 ...
FROM dbo.Levels s1
INNER JOIN RecursiveCTE
ON s1.[ROOT] = RecursiveCTE.[SUB]
and RecursiveCTE.[Top Level Part] = s1.TopLevelRouter
WHERE s1.[LEVEL] > 0
)
SELECT * FROM RecursiveCTE
WHERE [LEVEL] != 0;


Does read-only activity on synchronous secondary replica slow down primary replica in Availability Group?

usually, there is no impact on primary replica in performance point of view as the resource utilization is completely on secondary replica. i.e. (CPU, Memory, IO), and read operation would takes place on secondary copy of the database.

I believe, your assumption of performance impact considering the log processing workers, but that (AG worker connections) would anyway constantly active (which can be identified with select * from sys.dm_exec_connections where protocol_type = 'Database Mirroring' ) on primary replica regardless weather the secondary read-only operation or not. If it's really a concern you can use dedicated Ethernet for Database mirroring end-pints.

In summary, there is no performance impact but we should consider about Data Latency as the active transactions from Primary replica cannot be available in Secondary replica's select query until and unless the transaction committed by Primary replica and secondary replica has processed particular transaction.


MongoDB Collection Level Permission Issues

Not clear what you mean but this could be a solution:
var role = "<role name>";
admin = db.getSiblingDB("admin");
if (admin.getRoles({ rolesInfo: role }).length == 0)
   admin.createRole({ role: role, privileges: [], roles: [] });
admin.grantRolesToUser(<the user>, [role]);

Then you would have to run this code every time when a new database is created.
db.getSiblingDB(<your dynamic database name>).getCollectionNames().forEach(function (item) {
   db.getSiblingDB("admin").grantPrivilegesToRole(role, [{
      resource: {
         db: <your dynamic database name>, collection: item },
         actions: ["find", "insert", "remove", "update"] }])
   })


Better database design approach to store thousands of users and their validations (composed by many attributes) about thousands of elements

Just looking at two aspects of the design:

First, it looks like you need a user table (to store information for each user of the site), and a film table (to store information about each film to be "validated", whatever that means).

From that point, you have a many-to-many relationship between users and films: each user can validate many films, and each film can be validated by many users.

This is normally handled by an intermediate table (a "mapping" or "bridge" table). This might be named something like user_film, and would have at least two or three columns:

user_film_id : integer, auto-incremented; primary key for this table
user_id : same type (and ideally, same name) as the primary key for the user table
film_id : same type (and ideally, same name) as the primary key for the film table

You can choose to simply use the user_id and film_id together as the primary key for user_film. Primary keys are generally used as a "clustered index", and the data is stored in the table based on that. Using a combined key means that each new entry could wind up anywhere in the table, making page splits and fragmentation much more likely. Using a "surrogate" key like an auto-incremented integer value means that each new record will go to the "end" of the table, saving time on inserts.

If you do use a surrogate key (as suggested), I'd put a unique index on user_id and film_id on the user_film table, to make sure that each user only has one entry for any given film.

Second, as far as the validations go, it sounds like a tagging system: you would have a number values that might help categorize the film (say, "color", "black&white", "silent", "sci-fi", "horror", "romantic-comedy", "oscar", etc.)

If that's the case, then there are a couple of options, depending on your database.

One is a simplified version of what's called an "entity-attribute-value" model.

In your case, you'd have a validation table, with an ID value (again, I recommend an auto-incremented integer value) and validation name, plus any other columns (perhaps a description of what the validation is to be used for).

Then, you'd have a user_film_validation table, with the primary key from user_film and the ID value from validation, and an auto-incrementing integer (possibly a bigint or the equivalent) to be the primary key of user_film_validation (again, you could just use the first two values as the primary key here).

This makes it easy to modify the validation names: you just have to change one record to change "black&white" to "B&W", for example, instead of updating thousands of records. It also lets you add new validations without having to alter existing tables: simply adding one record to validation makes the new entry available to every user, on every movie.


Do I need a domain controller for multiple PCs accessing the same SQL server db? How do I configure it?

do I need a DC?

No you can use SQL Authentication, or Windows Auth (NTLM) with "workgroup auth".  And to use a DC you would have to join both of your boxes to the domain, so I wouldn't bother.

is this DC something that comes with the windows images?
is this DC something that comes with SQL Server?
is this an application which I'll need to install separately?

Domain Controller is a feature of Windows Server, and is not available on the client editions of Windows, like Windows 7 or Windows 10.

(I initially thought that if I'm installing sql server on PC1, I will have to insert the windows credentials from the PC2 and this will suffice)
could you please give me step-by-step on how to make this work?

If you create a local user on both boxes that has the same password, you can use Windows Integrated Auth for SQL Server.  This is the "workgroup auth", and it would enable accessing File Shares and other services between the computers.


How to get lsn(xid) numbers of transactions from WAL file?

Run pg_waldump on the WAL segment.
Then you will get lines like
rmgr: Heap        len (rec/tot):    115/   115, tx:        564, lsn: 0/01FECDB0, prev 0/01FECC88, desc: HOT_UPDATE off 1 xmax 564 flags 0x60 ; new off 2 xmax 0, blkref #0: rel 1663/16385/1417 blk 0

In there you have tx: 564, lsn: 0/01FECDB0, which is your desired information.


Create shared user mapping in PostgreSQL foreign data wrapper server

The only way to share a user mapping is if you define it for PUBLIC, but then it applies to all users. Groups don't work in that context, probably because it would cause ambiguity if several user mappings apply to a single user through inheritance.
If you don't want to go with a user mapping per user, you could opt to create one for PUBLIC but limit access to the foreign tables to role_name through permissions.
I know that this is not a great solution either, because it will allow all users to see the user mapping and the password in there (if there is one). But at least it is easy to GRANT SELECT ON ALL TABLES IN SCHEMA ... TO role_name.


Instead of using triggers on the CDC tables, you should use the built-in CDC functions for returning the changes recorded by CDC. If you only need the latest value for each row, use cdc.fn_cdc_get_net_changes but if you need all intermediate changes as well, use cdc.fn_cdc_get_all_changes.

These functions are created for each table (or 'capture instance') that you enable CDC on and the functions return changes on a table from the earliest LSN to the latest LSN. There is a good article here that delves into CDC and retrieving the data.

To use it in your periodic polling process, you could do something like this:

Fetch the earliest LSN from cdc.lsn_time_mapping after the last datetime that you polled.
For each table, fetch the max lsn value using sys.fn_cdc_get_max_lsn
Use cdc.fn_cdc_get_net_changes or cdc.fn_cdc_get_all_changes with the LSNs from step 1 and 2 to fetch the changes that have occurred since the last polling.
Process the changes into MongoDB
Log the min and max LSN and the start and end datetime used during this polling to a table. This can be used on the next poll to get the datetime value to determine your next min LSN.

You could even use SQL Server Integration Services which has built-in tools for working with CDC data.


How can to make a new user see table in existing schema

Log in as HR user and run following statement (first replacing the OTHER_USER with actual username):

select 'GRANT SELECT ON ' || table_name || ' TO OTHER_USER;' from user_tables;

then copy the output, paste and execute it as HR user.


Best way to model a single user with multiple usernames

You have clarified that you will not implement the team aspect in your system, but I consider that keeping the suggested approach for such scenario in this answer is still pertinent since it can be a useful example for other seekers.
Along with that, I have included some other contexts in which you could offer the option of having multiple Usernames (or Nicknames, or Handles), and a possibility that takes into account the common ASP.NET arrangement for dealing with Users and Roles data. These elements may serve to establish some analogies with the informational requirements of your own business environment.
1. User Profiles and Team Player Nicknames
1.1 Business rules
According to my understanding of your description about the social gaming website, I consider that the following conceptual-level formulations are specially relevant:

A User may be a Player of zero-one-or-many Teams
A Team is made up of one-to-many Players, each of which must be a User

1.2 IDEF1X diagrams
Then, I derived from those business rules the two IDEF1X1 diagrams that are presented in Figure 1.

1.2.1 Option A
As you can see in such a diagram, there is a many-to-many (M:N) association or relationship between the entity types Users and Teams, which takes effect in the associative entity type called TeamPlayer.
The UserProfile.UserId PRIMARY KEY (PK for brevity) property migrates2 to TeamPlayer as PlayerId, a role name that I have assigned to UserProfile.UserId in order to make it more meaningful in the context of its corresponding entity type, and these properties must be connected via a FOREIGN KEY (FK) defined in TeamPlayer.
On the other hand, TeamPlayer.TeamNumber is associated to Team.TeamNumber by virtue of another FK reference.
With this arrangement, one given UserProfile can hold

one Username when acting as an individual, and
one Nickname when he or she is carrying out the role of a Player for each of the Teams in which the UserProfile in question has joined.

1.2.2 Option B
This diagram is quite similar to the one referred in Option A, but it has an important aspect that is different, since I have moved the TeamPlayer.Nickname property to a separate entity type, which is called Nickname. This configuration is useful in case the Nickname value of a Team Player is optional, i.e., TeamPlayers may choose to provide or not provide a Nickname.
1.3 Team Player Nickname uniqueness
The Option A daigram has an ALTERNATE KEY3 (AK) defined for TeamPlayer, which is composed of two properties: TeamNumber and Nickname. In turn, the Option B diagram exhibits an AK in the Nickname entity type that is made up of TeamNumber and Nickname, as well. In this manner, both methods would provide UNIQUEness for a Nickname value within the context of a determined Team instance.
If you want to prevent the same Nickname value from being repeated in several Team occurrences, then you should set up an AK comprised of only one single property, that is, Nickname. This approach is valid for option A and Option B.
Alternatively, if you want to have the Nickname property set as NULLable (i.e., optional), you could define a “filtered” (or “conditional”, or “partial”, depending on the platform of use) UNIQUE constraint in Option A for supplying Nickname uniqueness in relation to a Team but, as I see it, proceeding this way things would get less elegant, so to speak.
2. People, User Profiles and Usernames
I created some more IDEF1X diagrams (presented in Figure 2) depicting different scenarios in which a given Person can have multiple Usernames, aiming to broaden the scope of this post. They are shown in the following section.
2.1 IDEF1X diagrams

2.1.1 Option A
This option is representing the following conceptual business rules:

A Person may log in via zero-or-one UserProfile
A UserProfile has one-to-many Usernames

Thus, it is comparable to your first diagram, but I have added a Person entity type that is optionally connected to UserProfile via the UserId FK definition.
Another important addition is the Username PK, which is comprised of

a FK reference to UserProfile.UserId and
the CreatedDateTime property that represents the instant in which a given Username was inserted.

This allows you to get rid of the Username.UsernameId (or username.id, if you prefer) property.
In this regard, this excerpt from your question is very important:

So, in this design, only one row in username per user will have active set to true. This smells a bit to me. What if no rows for a given user have active set to true?

Yes, you should establish a policy that states how many Username instances can have the IsActive property set to 'TRUE' with respect to a precise UserProfile, whether exclusively one Username that is “active” at a given point in time, perhaps two, three, etc., or none at all. As you know, each possibility demands distinct validation methods.
But, if a certain User can have multiple Usernames, I do not think that there can be a problem if such a User does not have any “active” Usernames, since the log in credentials are the Email and Password. In this case, when no Username is “active”, you could display some sort of a “default message” indicating that situation.
2.1.2 Option B
This method is very alike to Option A, but it is less flexible, since I have integrated the UserProfile credentials into the Person entity type. By proceeding this way, you would force every Person to have a LoginEmail and a Password.
Conversely, Option A permits you to retain Persons (or People) that are not related to the User context in a clean and separate manner, without employing NULLable (i.e., optional) properties.
2.1.3 Option C
The diagram displayed as Option C describes the statements that follow:

A Person may log in via zero-or-one UserProfile
A UserProfile holds zero-one-or-many entries in UsernameHistory

This method implies notions pertaining to the subject known as temporal databases. You may find my take on Scenario A and my response to Scenario B of relevance in this respect.
With this approach —once the schema depicted in this diagram is built on a certain SQL environment— you could retain the “active” Username in a, say, UserProfile table and all the “past versions” in a, say, UsernameHistory table.
As per the data manipulation factor, basically, every time that a UserProfile UPDATEs his or her Username, you save a copy of the last Username version in the UsernameHistory table. Each History Version is uniquely identified by a combination of the Date and Time datum in which such row was modified and, or course, the UserId FK that links the row in question with its respective UserProfile.
As in any relational database project, you should seriously consider using ACID TRANSACTIONS in order to guard your data integrity and consistency.
2.1.3 Option D
Let us suppose that, in your domain of discourse of interest, a Person can perform two roles: Administrator and Author. This is where Option D comes into play, and the next assertions are useful in delineating our informational diagram:

A Person may be zero-or-one Administrator
A Person may be zero-or-one Author
A Person may login via zero-or-one UserProfile

In this way, a Person holds his or her particular “active” Usernames in his or her specific UserProfile and, also, can possess a Handle in case said Person is an Administrator and another different Handle when carrying out the role of Author.
3. Possibility with the common ASP.NET Users and Roles approach
You mentioned via comments that you are using ASP.NET MVC as your application development platform and, although I am not sure if you are as well utilizing the common (or default) ASP.NET course of action for handling Users and Roles, this section can serve as a good reference assuming you have to deal with the typical conditions.
3.1 ASP.NET Users and Roles approach diagram
The approaches suggested in Figure 3 appear feasible since you would not need to modify the tables that are usually created in ASP.NET apps. As you can see, you would only have to create an additional table for retaining the Handles (or Nicknames, or Aliases, if you prefer) that a given User may (or may not) have in regard to a certain Role assigned to such User in your application program. In other words, the same User could hold multiple Handles, and the context for each Handle instance would be an exact Role performed by the User at issue.
Attaching a Handle column directly to the UsersInRoles table could be possible too, but it may have unfavorable consequences in the ASP.NET authentication and authorization processes, aspects that, as you know, are  of the greatest importance for the security of any app, so this method would require especial research and testing.

3.1.1 Option A
This method depicts a Handle table that is connected to the UsersInRoles table by virtue of a composite FK that consists of the UserId and the RoleId columns. Said Handle table has an AK set up for, precisely, the Handle column, which implies that a specific handle value* could only be INSERTed in one single row.
3.1.2 Option B
In this option I have portrayed a Handle table that also makes a reference to the UsersInRoles table by means of a FK composed of the UserId and the RoleId columns, but it shows a slightly different approach that displays an AK constituted by the RoleId and the Handle columns, which would open the chance to have the same handle value repeated in multiple rows, but only in combination with disctinct RoleId values.

Notes
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
2 IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.
3 An ALTERNATE KEY is a property (or a combination of properties) that holds values that uniquely identify an entity type occurrence but was not chosen as the PK of the pertinent entity type; each entity type can have zero, one or more ALTERNATE KEYs. In an IDEF1X diagram, they are indicated as “AK” plus its respective number, e.g., AK1, AK2, etc. They are usually declared in a logical-level SQL DDL design via (i) a single- or multi-column UNIQUE constraint along with (ii) one or more NOT NULL constraints.


As Max Vernon states, the database is trying to restore to a location that doesn't have space to contain it.  If the backup file is compressed or mostly empty, you will still need the full uncompressed space to restore to.  Additionally, by default SQL Server will try to restore those files to their original file location, so if that original location does not exist or does not have enough space when you restore, the restore will fail.

In order to see the space and location required for a backup, you can run the following command:

RESTORE FILELISTONLY
FROM DISK='<backup file location>'

The output from this will show all files contained within the backup.  Look at PhysicalName and Size for the restore location and size (in bytes) that SQL Server needs for the restore.  You can change the restore location by specifying the WITH MOVE syntax in your restore statement, but as Max states, there's not much you can do about the space required.


Syntax Error after adding FOREIGN KEY REFERENCE

Try:

...
poster int references user(userid)
...

E.g.

mysql> create table user ( uname varchar(15) primary key ) ;
Query OK, 0 rows affected (0.01 sec)

mysql> create table note (
    ->   title int primary key
    -> , poster int references user(userid)
    -> );
Query OK, 0 rows affected (0.00 sec)

Or ...

alter table note
add constraint user_fk
foreign key (poster) references user(uname);

E.g.

mysql> create table user ( uname varchar(15) primary key ) ;
Query OK, 0 rows affected (0.00 sec)

mysql> create table note (
    ->   title int primary key
    -> , poster varchar(15)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> alter table note
    -> add constraint user_fk
    -> foreign key (poster) references user(uname);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


SQLite3 doesn't have concat_ws, looking for workaround

You can use CASE:

SELECT coalesce(col1, '')
       || CASE
            WHEN coalesce(col1, '') <> ''
                 AND (coalesce(col2, '') <> ''
                       OR coalesce(col3, '') <> '')
              THEN ' '
            ELSE
              ''
          END
       || coalesce(col2, '')
       || CASE
            WHEN coalesce(col2, '') <> ''
                 AND coalesce(col3, '') <> ''
              THEN ' '
            ELSE
              ''
          END
       || coalesce(col3, '')
       FROM elbat;


How do I change Postgresql to be listening on port 5432?

ypercube and deszo were able to get this user's environment up and running (per the chat transcript).

The key seemed to be running:

sudo pg_ctlcluster 9.6 main restart

but not running it as the postgres user.

See the chat log for more details.


Deny a Sql Server user from a specific hostname

And, there several different developers which developes that CRM web
  app. They know the user name and password from the web config file
  they develope. Some of them can connect to my database with that user
  and execute some DDL and DML commands. And i want to block them by
  their client hostname. If a developer connects to db from his/her
  machine with the user credential, i want to block them.

I don't think you should try to fix it that way.

Both pc names and IPs can be changed at will, even static IPs could be changed if wanted/needed. Which means you'd have to maintain an up-to-date list all the time.
As a dev, if you really wanted to do something, remote to another VM, execute there.
I mean, there's probably plenty of security loopholes they can find if they really wanted to do something.

Finding a solution to do what you want, would probably only make it a hassle for them, but not fool proof.

I think you'd be better off trying to find a security solution for this. If one of the devs should not be able to do DML statements, don't give them a login that has access to it. Does the user in the config really need all the permissions it has now? Perhaps you can give less permissions to that user, and give the people who need more permissions an extra role, on their own login.


The second argument to DBCC SHRINKFILE is target size, in MEGABYTES. So to paraphrase your code into English, you are saying:

  "SQL Server, please take this 1 GB file, and shrink it to 1000 MB.

  P.S. 1000 MB = 1 GB. So, don't do anything."

You should try a smaller target size, e.g.:

DBCC SHRINKFILE (Database_TEST_Log, 1);

If the file doesn't seem to shrink, run the commands again (or issue a CHECKPOINT and run the shrink command again, if the recovery model is SIMPLE).

If the file still doesn't seem to shrink, it is probably because there is a long-running transaction. You can find out what this is using the following command against this database:

DBCC OPENTRAN;

Of course, when you do things like BACKUP WITH TRUNCATE_ONLY I hope you don't care about this database or want to be able to recover to a point in time prior to this operation, or at least that once you shrink this log file you will resume proper log management and backup procedures. Also, if you shrink the file to a minimum size, the next time a user causes log to be generated, they'll have to wait for file to grow. This is especially true for log files, which can't benefit from instant file initialization like data files can, and can be particularly annoying when the autogrow size is inappropriate and/or the log is not on a fast disk (like SSD).

Please have a good read of this question and its answers:

Why Does the Transaction Log Keep Growing or Run Out of Space?


Detecting resource scheduling conflicts with MySQL

Either like this:

SELECT DISTINCT
'this events and participations overlap',
e.*
FROM events e
JOIN event_participations ep ON e.id = ep.event
WHERE EXISTS (SELECT 1
              FROM events e2
              JOIN event_participations ep2 ON e2.id = ep2.event
              WHERE e.date = e2.date
                AND e2.start_minute < e.end_minute
                AND e2.end_minute > e.start_minute
                AND ep2.participant = ep.participant
             )

or like this:

SELECT DISTINCT
'this events and participations overlap',
e.*
FROM events e
JOIN event_participations ep ON e.id = ep.event
JOIN events e2 ON  e.date = e2.date
               AND e2.start_minute < e.end_minute
               AND e2.end_minute > e.start_minute
JOIN event_participations ep2 ON e2.id = ep2.event
WHERE ep2.participant = ep.participant

The nice thing about EXISTS is, that it stops to search as soon as it hits. The JOIN approach builds the whole data set.


Step 0) Shrink disclaimer

I'm not even going to say it. I'll let Brent Ozar tell you to Stop Shrinking Your Database Files. Seriously. Now.

In your comments, you mention you deleted 50% of the data in the database, so you very likely have a good use case for a one-time database shrink. (Assuming that the deleted data isn't going to come back.)

I'm not even going to touch on shrinking logs. We're just talking data files.

Step 1) Look at file size

Before actually thinking about shrinking a file, look at file size and how much free space there is. Run this in the database you want to shrink data files for:

SELECT
     LogicalName = dbf.name
    ,FileType = dbf.type_desc
    ,FilegroupName = fg.name
    ,PhysicalFileLocation = dbf.physical_name
    ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
    ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - ((dbf.size/128.0) - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0))
    ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;

If you look at the FreeSpaceMB column in your results, that is the amount of unused space you might potentially be able to reclaim. If it's significant, you might decide to move on and shrink the files.

Step 2) Consider the side effects

If you shrink the data file, any data located at the "end" of the file needs to be related elsewhere in the file. These operations are logged, so you're going to generate a lot of transaction log usage (Unless your database is in SIMPLE recovery). Make sure your transaction log is big enough, and make sure you are running transaction log backups frequently enough.

If your database is being mirrored, or is in an Availability Group, or is log shipping, you might find that your secondary server(s) fall behind during this operation.

Shrinking is going to create a lot of fragmentation. Depending on your workload, you might have a performance impact during the process.

Shrinking generates a bunch of IO. If you are already seeing IO issues, that might be exacerbated by shrinking, and you might have performance impact during the process.

Shrinking is a mostly online process, where data is moved page-by-page to the "front" of the file so that the end of the file can be truncated. I say mostly online because its possible for this operation to cause blocking--I've seen this particularly often in the PRIMARY filegroup. Because of the possibility of this causing blocking, I never let it run unattended, and never during peak business hours.

Be really sure that you need to shrink.

Step 3) Shrink files

I always use DBCC SHRINKFILE(BOL) when I shrink, never DBCC SHRINKDATABASE. If I am going to shrink a file, I want as much control over the process as possible.

Using the output from the above SELECT, figure out what you want to use for a target size on your database--You'll want to leave some free space in your data file, otherwise it's just going to grow bigger right away.

Now you can build the statement(s) to shrink your data file. Remember, this is an online process, but if you're worried about impacting performance, watch yoru server carefully while this runs:

USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Step 4) Review fragmentation

Congratulations, you've successfully created a ton of physical fragmentation. I know some shops run applications where fragmentation just doesn't cause any problems. If you're one of those shops, then you're done--stop reading.

If fragmentation does affect your performance, then you already have a regularly scheduled index maintenance job. After shrinking the data file, run that regular index maintenance job to get things squared away.

If you don't have a regular index maintenance job, and thing you need one: figure out what the best options are, and if you even need to consider it. Kendra Little gives a good intro to the topic here. I personally like Ola Hallengren's IndexOptimize procedure.

Remember: Even if you're running Standard Edition, REORGANIZE is an online operation, and if you kill it, you won't lose your work--it will pick up where it left off the next time.

It can be done completely online
You can use DBCC SHRINKFILE and REORGANIZE to do this completely online. If the shrink causes massive fragmentation that impacts production, you can shrink/reorganize/shrink/reorganize in smaller chunks. This will take a really, really long time. But that's a trade-off... There are faster methods, but those will likely involve varying amounts of downtime.

The easiest solution is to just leave the database half-full and accept that you're using more disk space than you need to.


PL/SQL Procedure: limit number of INSERTS

Ok, the trigger is trivial I think, for DBMS_LOCK, just to give you the idea:

create or replace procedure proc_insert(...) as
  l_count number;
  l_handle varchar2(128);
  l_return number;
begin
  dbms_lock.allocate_unique('leadership_role_insert_lock', l_handle);
  l_return := dbms_lock.request(l_handle);

  if l_return = 0 then
    -- lock successfully acquired
    select count(*) into l_count from leadership_role_table where ...;

    if l_count < 2 then
    -- inserting
      commit;
    else
      -- not inserting
      l_return := dbms_lock.release(l_handle);
      raise_application_error(-20001, 'your error goes here');
    end if;
  else
    -- could not acquire the lock
    raise_application_error(-20001, 'your error goes here');
  end if;

  -- this kind of lock needs an explicit release
  l_return := dbms_lock.release(l_handle);
  exception when others then
    rollback;
    l_return := dbms_lock.release(l_handle);
end;
/

With the above, concurrent calls of dbms_lock.request will be blocked until the previous session releases the lock.


Since you have not disclosed the RDBMS you intend to use (by the time of writing), I feel authorized to post a PostgreSQL-specific solution:

WITH rownums AS (
    SELECT *, row_number() OVER (ORDER BY col1) AS rownum
    FROM avg_test
),
averages AS (
    SELECT avg(col2) OVER (ORDER BY rownum
                           ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS average,
           rownum
    FROM rownums
)
SELECT average
FROM averages
WHERE rownum % 10 = 0
;

In the first CTE (called rownums) I simply added row numbers according to ordering by the timestamp.   This was necessary for being able to find every 10th row later. (If there were an additional unique key on this table, this yould be unnecessary.)
In the averages CTE I compute a moving average on every row.  The window is set so that the current row and nine preceding give the numbers to be averaged.
Lastly, I just simply return every 10th row.

Both row_number() and avg() are window functions here.  MySQL lacks this functionality, and, as far as I know, H2 too.  So porting the abov query to these will involve a lot more trickery, I think.

Try it at SQLFiddle.


Duplicate fails with no copy of datafile nnn found to restore

Try just backing up the tablespace for the new datafile. Then as long as you have all of the archive logs that were generated since the last backup, your clone should work.


set global sql mode but showing a different mode

Your problem is quite simple.
When you change the value of a global variable, the current session still retains the session value.
To prove that, please run this
SELECT * FROM information_schema.global_variables
WHERE variable_name='sql_mode'
UNION SELECT * FROM information_schema.session_variables
WHERE variable_name='sql_mode';

You should get this output if the global and session values are the same
+---------------+------------------------+
| VARIABLE_NAME | VARIABLE_VALUE         |
+---------------+------------------------+
| SQL_MODE      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

You should get this output if the global and session values are different
+---------------+------------------------+
| VARIABLE_NAME | VARIABLE_VALUE         |
+---------------+------------------------+
| SQL_MODE      | STRICT_ALL_TABLES      |
| SQL_MODE      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

To see that change, you must logout and login. Then, run the UNION query.
If you cannot logout of the current session, you must run
SET SESSION sql_mode = 'STRICT_ALL_TABLES';

to see the difference.
GIVE IT A TRY !!!
UPDATE 2015-09-03 18:14 EDT
You just asked me

Why doesnt it has the STRICT_ALL_TABLES in the global session though ?

Guess what ?? @@sql_mode is not global. It's session.
Let me run this query for the difference:
mysql> select @@global.sql_mode,@@session.sql_mode,@@sql_mode;
+-------------------+------------------------+------------------------+
| @@global.sql_mode | @@session.sql_mode     | @@sql_mode             |
+-------------------+------------------------+------------------------+
| STRICT_ALL_TABLES | NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+-------------------+------------------------+------------------------+
1 row in set (0.00 sec)

Therefore, if you want global, you must use @@global.. Otherwise, it's session.


MariaDB master to master replication auto increment is not sequential

All(?) INSERTs, including IODKU and REPLACE can burn ids, leading to gaps.  This happens with the statement is rolled back.  And for INSERT IGNORE when some UNIQUE triggers "ignore".
Even without multi-master, you can get AUTO_INCREMENT values out of order.  This is because the auto_inc is grabbed when needed, but not replicated until COMMITted.  Replicas will see the ids out of order.
Bottom line:  AUTO_INCREMENT guarantees uniqueness, nothing else.
(Also, what Gordan says.)


Creating a table task adding a refer id field for each referred table, in this way:

type : I prefer something as 'tree', 'pen'. Use an enum for this: ENUM('tree', 'pen');
tree_id : int (it can be NULL), that refers to tree table;
pen_id : int (it can be NULL), that refers to pen table;
name : varchar;

Some test data:

+------+---------+--------+-------+
| type | tree_id | pen_id | name  |
+------+---------+--------+-------+
| tree | 1       | NULL   | foo   |
| tree | 2       | NULL   | bar   |
| pen  | NULL    | 1      | fee   |
| pen  | NULL    | 2      | beer  |
+------+---------+--------+-------+


pgsql Return Partial Text Field Ending In A Whole Word

Something like this should work (here 15 is the equivalent of $maxlength):

SELECT
  CASE WHEN LENGTH(mycolumn) <= 15
       THEN mycolumn
  ELSE SUBSTRING(mycolumn, 0, 15 + 1 -
                 POSITION(' ' IN REVERSE(SUBSTRING(mycolumn, 0, 15 + 1))))
  END
  FROM mytable

Here is a working demo.


When and how to create users in a MongoDB sharded cluster

Tutorial Deploy Sharded Cluster with Keyfile Authentication provides a step-by-step setup for a sharded cluster with authentication, i.e. creating users.
The general procedure is this:

Deploy the entire sharded cluster completely without any user accounts.
When the cluster is complete connect to one mongos (using localhost exception) and create the user accounts. The accounts will be available in the entire cluster.
Connect locally to each shard (i.e. the PRIMARY node, using localhost exception) and create the "shard-local administrator" accounts.

Step 2 and 3 you can exchange, if you like.
What is a "shard-local administrator" account?
User accounts are (usually) defined in the admin database of the Config Servers. When a client connects to the cluster then it queries the config server to validate the credentials. Some operational tasks on the shard servers requires a direct (i.e. local) connection to the shard server, sometimes they are even disconnected from the cluster. A disconnected shard node cannot query the Config Server in order to validate any credentials.
For this reason you have to create the "shard-local administrator" accounts on each shard. With this account you can connect locally to the shard server and perform some maintenance operations even when it is not connected to the cluster. The accounts are created on the PRIMARY nodes and they are replicated to the SECONDARIES.
The "shard-local administrator" accounts are needed only for some operational task, your application does not require any of them.
The Config Server does not have such local accounts, because
local users are equal to cluster users. You can use the cluster user credential also to connect directly (i.e. locally) to a Config server.
The mongos does not have such local accounts either. The mongos does not store any data locally, thus operational tasks mentioned above does not exist and local accounts are not required nor possible.
When you add new shard replica sets then you also have to create the "shard-local administrator" accounts on them. However, it does not matter when you create it, because they are fully independent from any cluster user.


Weird order by behavior on SQL Server 2014 SP2

It is because you need the entire definition for the [TAT] field in the Group By:

CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2))

Something like this:

SELECT DISTINCT
        [TAT] = CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2))
        , [MonthName] = DATENAME(MONTH, TEST_DATE)
        , [Week] = DATEPART(WW, TEST_DATE)
        , [Year] = DATEPART(YYYY, TEST_DATE)
        , [MonthNumber] = DATEPART(MM, TEST_DATE)
        , PRODUCT_CODE
        , TEST_PLAN_NAME
        , [Count] = COUNT(PRODUCT_CODE)
FROM    CO_SAMPLES
WHERE   SAMPLE_TYPE IN ( 'CA' )
        AND PROFILE_NAME <> 'Shelf Life'
        AND RECLASS_NUMBER IS NULL
GROUP BY CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2)) ,
        DATENAME(MONTH, TEST_DATE) ,
        DATEPART(WW, TEST_DATE) ,
        DATEPART(YYYY, TEST_DATE) ,
        DATEPART(MM, TEST_DATE) ,
        TEST_PLAN_NAME
ORDER BY [Year] ,
        [MonthNumber] ,
        [Week] ,
        PRODUCT_CODE;


Creating crosstab() pivot table in PostgreSQL 9.0

Your table definition says

...
ageband character(12),
...

which means the values in there look like '18-24       ' instead of '18-24'.  This way, the items from the VALUES list do not match the values in the table, therefore you get an empty table as result.

If you change the column type to a bit more senseful text (which trims the values, too, as Erwin points it out):

ALTER TABLE activities_in_localities_asc ALTER COLUMN ageband TYPE text;

you will get your desired results.


MS SQL Server Incremental(differential) backup interrupted due to Full backup

While you can't control if the backup happens, could you ask the third party to change how the backup is taken? Using the COPY_ONLY option stops backups from interrupting other backup sequences, so if they add that to their process it will stop interfering with your's. See https://msdn.microsoft.com/en-gb/library/ms191495(v=sql.120).aspx for more info.

That is the only option that doesn't require an extra copy of the database. Other than that you could maintain a replica and have the 3rd party take their backups from there (or take your backups from there).


While AlwaysOn/Availability Groups are a welcome edition to SQL Standard Edition 2016, the GUI is not supported for group creation. Try using the T-SQL code to create the group. Per Microsoft: https://msdn.microsoft.com/en-us/library/mt614935.aspx

"To create a basic availability group, use the CREATE AVAILABILITY GROUP transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). For more information, see CREATE AVAILABILITY GROUP (Transact-SQL). At this time, there is no UI support to create basic availability groups in SQL Server Management Studio."

The GUI is spamming the Advanced group used by Enterprise Edition (to your Standard Edition) while, Standard Edition only supports Basic.


Is there any scenario where a exported (.sql) database dump could be bigger in size, than the database itself?

Lots of cases...

VARCHAR needs 1 or 2 extra bytes for the length; as text it needs two quotes, a comma, and possibly some escape characters.
The BIGINT 123456789012345 takes 8 bytes in the table, but 16 in the dump (15 digits, plus a comma).
A NULL value dumps as 5 bytes, regardless of what the datatype needs, which is probably 0.
DECIMAL(11,2) with -123456789.12:  5 bytes in the table -> 14 bytes in the dump
(and the DATETIME/TIMESTAMP example given in a Comment)

Going the other direction:

BIGINT 1:  8 bytes -> 2
CHAR(100) 'x':  100 characters -> 4 bytes.  (This example varies with charset, version and engine.)
DECIMAL(11,0) with 0:  4 bytes in the table -> 2 bytes (0,) in the dump

Etc.


Is it possible to backup and restore a materialized view with it's data?

Not really. You have to remember that pg_dump command creates simple CREATE TABLE and INSERT statements etc. So effectively when running pg_restore you're just running CREATE and INSERT statements on the server and inserting the data would require a "INSERT INTO MATERIALIZED VIEW"-command. That wouldn't make sense as getting the data by a shortcut would also danger the "integrity" of the view - you could insert invalid data on the manual restore process that the view wouldn't return otherwise. So I doubt that PostgreSQL would ever support manual restoring of materialized views data straight into database.

If you need to backup the actual data I would recommend using a table instead of materialized view and running a scheduled DELETE FROM/INSERT INTO statements to the table.


Oracle not able to add Default value in PL/SQL

Single quotes inside single quotes need to be duplicated: default '' '' not null

 CREATE OR REPLACE PROCEDURE CREATE_TABLE AS
  strTABLE_NAME varchar2(32);
BEGIN

  strTABLE_NAME := 'Inventory';
  execute immediate 'create table '|| strTABLE_NAME ||
                    ' (STORE VARCHAR2(4) DEFAULT '' '' NOT NULL)';

END CREATE_TABLE ;

See the manual for details and alternative ways of doing that:

Text Literals | docs.oracle.com


Make sure that the anchor is set properly (need to be either the roots or the final leaves). Your current anchor set (the first set from the UNION ALL) is retrieving all records from the table, not just the border ones.

Since your relationships are through parent and not by child, the anchor should contain all records without parents. Then join recursively with each child.

DECLARE @table TABLE (ID INT, name VARCHAR(1), parentID INT)

INSERT INTO @table
VALUES
    (1, 'A', NULL),
    (2, 'B', 1),
    (3, 'C', NULL),
    (4, 'D', 3),
    (5, 'E', 4)

;With CTE AS
(
    -- Anchor (all grandfathers)
    SELECT
        ID = T.ID,
        Name = T.Name,
        ParentName = CONVERT(VARCHAR(MAX), NULL) -- Set the data type for the UNION
    FROM
        @table AS T
    WHERE
        T.parentId IS NULL

    UNION ALL

    -- Traverse through each children
    SELECT
        ID = T.ID,
        Name = T.Name,
        ParentName = ISNULL(C.ParentName + ' -> ', '') + C.Name
    FROM
        CTE AS C
        INNER JOIN @table AS T ON C.ID = T.parentId
)
SELECT
    C.*
FROM
    cte AS C
ORDER BY
    C.ID

Result:

ID  Name    ParentName
1   A       NULL
2   B       A
3   C       NULL
4   D       C
5   E       C -> D


Concatenate two columns into one column in a view in MySQL

Yes, use concat or concat_ws:

CREATE VIEW myview AS
  SELECT concat_ws(' ', firstName, lastName) as "User"
  FROM mytable;


I suggest the schema is "over-normalized".  I would suggest any table that lists a 'movement' should include the direction (movementTypeId).  Perhaps it could be done with a sign instead of an extra column?  (Positive for Bought, negative for Sold.)

There are no indexes on movements_books; maybe other tables?

The GROUP BY bookId in the subquery is probably wrong.

(cleaned up a little, and fixed missing comma:)

SELECT  b.title, mbg.bookId,
        SUM(IF(m.movementTypeId=1, mbg.quantity, NULL)) AS booksBought,
        SUM(IF(m.movementTypeId=2, mbg.quantity, NULL)) AS booksSold,
        ( SUM(IF(m.movementTypeId=1, mbg.quantity, 0))-
          SUM(IF(m.movementTypeId=2, mbg.quantity, 0))
        ) AS booksAvailability,
    FROM (
        SELECT  bookId, quantity, movementId
            FROM  movements_books AS mb AS b
            GROUP BY  bookId
         ) AS  AS mbg
    JOIN  movements AS m  ON m.movementId = mbg.movementId
    JOIN  books AS b2  ON b.bookId = mbg.bookId
    GROUP BY  mbg.bookId
    ORDER BY  b2.title

movements_books needs

PRIMARY KEY(bookId, movementId),
INDEX(movementId, bookId)


How to make Event (like a Party) Sources mutually exclusive?

One possible solution to this is move the API entity up next to Admin, User, Biz User, and make a relationship User ISA (is a?) API.


Problem trying to force the use of a filtered index

Possibly the disconnect is that the filtered index won't automatically filter the results for you - you have to write a WHERE clause that matches the index's filter predicate in order to be able to use the index.

In other words, if you're only trying to get rows WHERE Col1 IS NOT NULL, you still need a WHERE clause to limit the rows. If SQL Server can't use the index you're trying to force to satisfy all rows to be returned by the query, which it can't do by definition of the index holding fewer (or the same*) rows than the table, it can't run.

  * There might be scenarios I haven't tested where this will work; for example, if the column is constrained to NOT NULL (or all rows happen to match the filter predicate), then the filtered index would, by extension, represent rows from the entire table. But that is a pretty contrived and impractical scenario (even if it were to work). And SQL Server couldn't generate a plan for the case where all rows happen to currently match the filter, because a single insert or update could break it.

If you're trying to locate data from the non-clustered index because the underlying table/CI is corrupt, you will probably have better luck with DBCC IND/PAGE or with a hex-editor-wielding Paul Randal impersonation.


How to write a procedure /job to kill all session running for more than 5mins in Oracle?

Generally, aborting database queries and DML is not a good idea, and as @Raj already pointed out, killing sessions is also bad idea – actually it's even worse.

Your sessions may be running some transactions which consume undo space (in the undo tablespace of the user), hold locks on tables and occupy space (e.g. for row sorting) in the temporary tablespace. When you kill the session, all the work done by the transaction in-flight should be rolled back, all the undo segments and locks should be released. And while "light" sessions can terminate pretty quickly, others can take time before Oracle engine cleans everything up.

To answer your question we should know why you are killing your sessions.

Is it because the user sessions consume a lot of resources (CPU) if you allow them more than 5 minutes to run?
Is it because the users establish too much of sessions and idling?
Is it because the newly established session starves for resources already used by other sessions and should have them available to run?
Is it because you developed some cool software and you want to limit users to evaluate it only a few minutes before they are disconnected?

Probably you could improve your business logic so as to avoid killing sessions – doing computations on the server side, for example.

Or you could develop some RESTful web application which is run in the middle tier (application server) and performs queries on behalf of users while keeping a reasonable small number of connections to the database using connection pool. For example, you could write Java web application with servlets and JSP using Apache Tomcat JDBC Connection Pool (official documentation for Tomcat 8). You could also switch from dedicated server architecture in favor of shared server architecture.

If your software doesn't support connection pooling you could employ Database Resident Connection Pooling via OCI.

But if nothing I suggested to you meet your needs, you can implement your resource plan using Database Resource Manager, define directives which abort running queries or kill sessions which run or idle too long, and everything you need to implement this is described in chapter Managing Resources with Oracle Database Resource Manager in Oracle Database Administrator's Guide. The keywords are CANCEL_SQL, KILL_SESSION, MAX_EST_EXEC_TIME, MAX_IDLE_TIME.


we still can't find backup archivelog in local controlfile

If the backups are going straight to NetBackup, try specifying DEVICE TYPE SBT in your LIST command. The default may be DEVICE TYPE DISK. For example
list backup of archivelog all device type sbt;

HTH


Crow's notation: Identifying relationships without the FK in the PK

According to this post, as pointed out by mustaccio, while the formal way of showing an identifying relationship is by including the FK in the PK in the child table, this is not always done since it might not be practical to have big PKs that include the foreign key(s).

The logical meaning is simply that the child cannot exist without the parent, and as long as it is true, the relationship is identifying.

Therefore, in the example I stated, if we assume that all the students in our database have to be enrolled in a university, the relationship between a Student and a University is classified as identifying, since a Student cannot exist without a University.


Performance usually improves from version to version of SQL Server.  SQL Server 2000's Analysis Services was nowhere near quick.

As to why - Microsoft doesn't give us the source code so it's a little tough to analyze that. ;-)

Like @ConcernedOfTunbridgeWells pointed out in his comment, though, the real reason you should get off SQL Server 2000 is that it's out of support.


At first I'd suggest to have group_id and visit_id in INT or at least BIGINT.
If that table has only 3 columns it might worth to create not-unique clustered index by group_id and unique constraint on visit_id:

CREATE TABLE [dbo].[visit](
    [visit_id] [uniqueidentifier] NOT NULL,
    [group_id] INT NOT NULL,
    [visitor_company] [nvarchar](50) NOT NULL
);
GO
CREATE CLUSTERED INDEX CLIX_Visit ON [dbo].[visit]([group_id]);
GO
ALTER TABLE [dbo].[visit] ADD CONSTRAINT UQ_Visit UNIQUE ([visit_id]);
GO


Ctrl-F1 shortcut stopped working in SSMS 2014

Here's a long shot: I think your Management Studio has for some reason changed the keyboard mappings to the 2012/2014 default setup, which is more similar to the regular Visual Studio apps. This changed as of SQL Server 2012 so, among other things, Ctrl+F1 now opens Books On-Line.

To verify if this is the case for you, you probably won't be able to execute queries using Ctrl+E or view estimated query plans using Ctrl+L.

I found a way to reset the keyboard shortcuts to the "classic" setup and wrote a blog post about it a while ago. The short version is:

Open the "Tools" menu i SSMS,
Select "Import and Export settings...",
Select the "Reset all settings" option, click "Next",
On the last page of the wizard, you have the option to back up your current settings. I would recommend you to do this.

Hope that helps.


Copy database wizard - any change in lsn number of source database

One method which you can choose will use the SMO programming tools.  The other method which results in the source database going offline is detach/attach.  Neither of these will affect backups or LSN's.


Backing up database without transaction log

It looks like your confusing various concepts here. Start with Transaction Log Management and Backup Overview topics in Books Online.

Is the root problem that when you restore the full backup to another server, the transaction log is consuming more space than you have available?

If so, the hack I described in an answer to the question Is it possible to restore sql-server bak and shrink the log at the same time? might be useful.

Is your transaction log growing, and growing, and growing because your database is in full recovery but you aren't taking log backups? See here and here.

  When I backup a database to a bak file is there a way to exclude the
  transaction log in that backup?

No. A data backup will include part of the transaction log, at least the section of the log that was generated since the backup started. It shouldn't however be a significant proportion of the log unless you have very long running transactions.

Note that when you restore a data backup, the database will be recreated with the same data and log file sizes as were present in the source database, regardless of how full the data file was or how large the active part of the log was. If this is the problem you're experiencing, see my earlier hack for working around a lack of space to restore.

  Or am I thinking about this wrong, to restore the original database is
  the content of the transaction log required?

See above, a proportion of the log will be included in a full backup.

  I cannot shrink the transaction log because the database is using
  replication and I would have to break the replication.

No reason why you can't shrink the log with replication in place (you'll only be able to shrink the inactive portion) but:

It won't make any difference to the size of your data backup.
It will probably grow back to it's current size.
The large transaction log is probably due to not taking log backups.
You shouldn't be shrinking the log.


The trick is to not actually delete rows, but to set an "IsDeleted" column to 1. That way, referring records remain unorphaned.

Updates are more interesting. Do you need to have the referring record still refer to the old record, or the new version?


Assignment of a column with dynamic column name

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE.
And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there are ways around this limitation:
Proof of concept

Assign to NEW by key in a Postgres trigger
How to set value of composite variable field using dynamic SQL

You can (ab)use the built-in JSON function json_populate_record() for a similar trick, but that's currently not documented and may be removed in future versions of Postgres.
The sure way is to use the documented #= operator of the additional hstore module. Install the module once per database with
CREATE EXTENSION IF NOT EXISTS hstore;

Then:
CREATE OR REPLACE FUNCTION prevent_column_update()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col text := quote_ident(TG_ARGV[0]);
   _old_val text;
   _new_val text;
BEGIN
   EXECUTE format('SELECT $1.%1$I, $2.%1$I', _col)
   INTO    _old_val, _new_val  -- part of plpgsql command
   USING   OLD, NEW;

   IF _old_val IS DISTINCT FROM _new_val THEN  -- only if it actually changed
      NEW := NEW #= hstore(_col, _old_val);    -- hstore operator #=
   END IF;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Note that hstore operates with text strings. Values for other data types are cast to text and back, which works for any data type I can think of. But it might cause problems for some types (like rounding errors for floating point numbers).
And this trigger definition to make the case complete:
CREATE TRIGGER tbl_upbef_nope
BEFORE UPDATE ON tbl  -- your table here
FOR EACH ROW
EXECUTE PROCEDURE prevent_column_update('date_price');

The column name is case sensitive in this example, since it is passed as string and not as identifier.
What I would do
Just write a new plain trigger function without dynamic SQL for each table. Less hassle, better performance. Byte the bullet on code duplication:
CREATE OR REPLACE FUNCTION prevent_column_update()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW.date_price:= OLD.date_price;  -- unconditionally
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Trigger:
CREATE TRIGGER tbl_upbef_nope
BEFORE UPDATE OF date_price ON tbl  -- your column and table here
FOR EACH ROW EXECUTE PROCEDURE prevent_column_update();  -- no param

I moved the check to the trigger itself, so the function is not even executed unless the column is updated. Note that this can be circumvented by additional triggers on the same table because (quoting the manual):

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

So if you cannot rule out such additional triggers, fire the trigger on UPDATE unconditionally and check for changes in the trigger function instead.


Full backup at 1am, not started by my maintenace plan

Add columns:
    msdb.dbo.backupset.software_vendor_id,
    msdb.dbo.backupset.user_name

to your query.

If your software vendor id = 4608 then its sql native, anything else is a third party software. The user name should give you some clues as to what is occurring.

Also check SQL Agent for jobs executing at around that time.


Yes in InnoDB killing the session  is causing the rollback and the the rollback can take even more time than the actual insert command issued on the table.

Refer the following:

  Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer
  to save disk I/O in inserts, but no such mechanism is used in a
  corresponding rollback. A disk-bound rollback can take 30 times as
  long to perform as the corresponding insert. Killing the database
  process does not help because the rollback starts again on server
  startup.

So you can use the InnoDb Recovery:

  You can kill the mysqld process and set innodb_force_recovery to 3 to
  bring the database up without the rollback, then DROP the table that
  is causing the runaway rollback.

Hope this solves your problem.


On not public server: Host name example.com could not be resolved: Name or service not known

When --skip-name-resolve isn't in specified, the IP address of incoming connections is resolved into a name via a DNS query...

However, there isn't just one DNS query, because the existence of a reverse DNS entry (e.g. a PTR record 203.0.113.113 → foo.example.com) is not sufficient for hostname-based authentication -- anybody could change a reverse DNS entry to claim to be any hostname they wanted.

So there's a second query that occurs.

Once the hostname is resolved, there's an additional DNS lookup in the opposite direction, to verify that the hostname discovered in the first DNS query, foo.example.com, does indeed map to an A record that resolves back to the original IP address (e.g an A record foo.example.com → 203.0.113.113).

In this case, the IP address mapped to a hostname, but the hostname did not map back to the original IP address.

http://dev.mysql.com/doc/refman/5.6/en/host-cache.html


How to merge multiple query results into one

Since the FROM, WHERE and GROUP BY clause are identical, combine them as follows:

SELECT
    prtner_name,
    COUNT(*)        shipment_count,
    SUM(inscan)     inscan_count,
    SUM(is_deliver) is_deliver_count,
    SUM(is_rto)     is_rto_count
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name;


Minimal solution
One radical solution might be to remove pin_inst completely:
  part ←────────── pin
    ↑               ↑
part_inst ←───── pin_inst

There is nothing in your question to suggest you actually need the redundant table. For pins associated to a part_inst, look at the pins of the associated part.
That would simplify the code to:
create table part (    -- using singular terms for table names
    part_id bigserial primary key
);
create table pin (
    pin_id bigserial primary key,
    part_id bigint not null references part
);
create table part_inst (
    part_inst_id bigserial primary key,
    part_id bigint not null references part
);

But your comment made clear that we won't get away with that ...
Alternative if pin_inst is needed
Including part_id like you did is the simplest solution with foreign key constraints. You cannot reference a table “two tables away” with foreign key constraints.
But you can at least make do without "polluting" the primary keys. Add UNIQUE constraints.
create table part (
    part_id bigserial primary key
);
create table pin (
    pin_id bigserial primary key,
    part_id bigint not null references part,
    unique(part_id, pin_id)         -- note sequence of columns
);
create table part_inst (
    part_inst_id bigserial primary key,
    part_id bigint not null references part,
    unique(part_id, part_inst_id)
);
create table pin_inst (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,
    pin_id bigint not null,
    part_id bigint not,
    foreign key (part_id, pin_id) references pin,
    foreign key (part_id, part_inst_id) references part_inst
);
I put part_id first in the unique constraints. That is irrelevant for the referential integrity, but it matters for performance. The primary keys already implement indexes for the pk columns. It's better to have the other column first in the multicolumn indexes implementing the unique constraints. Details under these related questions:

Working of indexes in PostgreSQL
Is a composite index also good for queries on the first field?
Many to Many and Weak Entities

Related questions on SO:

Can a multi-field UNIQUE constraint include fields from more than one table?
Foreign key constraint with some column values residing in other tables

Alternative with triggers
You could resort to triggers functions, which are more flexible, but a bit more complicated and error prone and a bit less strict. The benefit: you could do without part_inst.part_id and pin.part_id ...


Export .MWB to working .SQL file using command line

Switch to mysqldump; it is aimed at scripting, whereas Workbench is aimed at UI.

Use the option --routines in addition to whatever else you need.

--no-data gives you just the schema.


% deliberately does not include localhost.  This decision was possibly made to emphasize the difference between the relatively secure local connection versus coming in from the World Wild Web.  Or perhaps because it implies a socket instead of TCP/IP.  (There is not much performance difference.)

So, either

Use the IP (or hostname) of the machine, or
GRANT ... TO admin@localhost ...;


Is my ERD valid for airports,cities and airlines database?

Looks ok.

Some countries span multiple continents, such as Russia and Turkey.

Some airports might not be in cities, like Mirabel (YMX), which is 53k from Montreal.


Speed up complex join with disjunctive condition

The below creates 300000 dummy orders with 3 random "phone" numbers and then finds the matches, should get you started.

use tempdb
GO

drop table if EXISTS Orders
GO

create table Orders (
    OrderID int primary key,
    UserName varchar(50),
    PhoneNumber1 varchar(50),
    PhoneNumber2 varchar(50),
    PhoneNumber3 varchar(50)
)

-- generate 300000 with randon "phone" numbers

;WITH TallyTable AS (
SELECT TOP 300000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2
)
insert into Orders
select n, 'user' + cast(n as varchar(10)), cast(CRYPT_GEN_RANDOM(3) as int), cast(CRYPT_GEN_RANDOM(3) as int), cast(CRYPT_GEN_RANDOM(3) as int)
FROM TallyTable;

/*

-- original query, takes a long time

select o1.OrderID,
       o1.PhoneNumber1,
       o1.PhoneNumber2,
       o1.PhoneNumber3,
       count(distinct o2.UserName)
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    or o1.PhoneNumber2 = o2.PhoneNumber2
    or o1.PhoneNumber3 = o2.PhoneNumber3
group by o1.OrderID, o1.PhoneNumber1, o1.PhoneNumber2, o1.PhoneNumber3
*/

-- which users share the same phonenumber1
select o1.UserName as username, 'phonenumber1' as phonenumber, o2.UserName as sharedwith
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    and o1.UserName <> o2.username -- make sure it's a different user
union ALL
-- which orders share the same phonenumber2
select o1.UserName, 'phonenumber2',  o2.UserName
from Orders o1
inner join Orders o2
    on o1.PhoneNumber2 = o2.PhoneNumber2
    and o1.UserName <> o2.username -- make sure it's a different user
union ALL
-- which orders share the same phonenumber3
select o1.UserName, 'phonenumber3',  o2.UserName
from Orders o1
inner join Orders o2
    on o1.PhoneNumber3 = o2.PhoneNumber3
    and o1.UserName <> o2.username -- make sure it's a different user
order by username asc

-- use the above as a cte to do some counting
; with matches as (
    select o1.UserName as username, 'phonenumber1' as phonenumber, o2.UserName as sharedwith
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber1 = o2.PhoneNumber1
        and o1.UserName <> o2.username -- make sure it's a different user
    union ALL
    -- which orders share the same phonenumber2
    select o1.UserName, 'phonenumber2',  o2.UserName
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber2 = o2.PhoneNumber2
        and o1.UserName <> o2.username -- make sure it's a different user
    union ALL
    -- which orders share the same phonenumber3
    select o1.UserName, 'phonenumber3',  o2.UserName
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber3 = o2.PhoneNumber3
        and o1.UserName <> o2.username -- make sure it's a different user
)
SELECT matches.username, COUNT(*) AS matches
from matches
group by username
order by 2 desc


Intellisense not working because of code creating columnstore index

In case it's already enabled, press Ctrl + Shift + R. That will update Intellisense with used database.

Sometimes, if the used database is not frequent, that does not work instantly in SSMS, but the above will update Intellisense.

In case of long script related issue, go to Tools>Options>. In there in on the left side, search for Text Editor>Transact-SQL>IntelliSense.

There is an item called Maximum Script Size. It may be 100KB`. In the drop down, change it to 5MB at least, or to Unlimited per your preference. After setting this, you need to close SSMS and start it again. Some older versions required a restart of the system. That should work.


The error message looks like it is coming from a DB2 linked server. I would check to make sure the consistency error is not being passed back from DB2.


Since the Maintenance Plan uses EXECUTE msdb.dbo.sp_notify_operator @name=N'some operator',@subject=N'some subject',@body=N'some body' there is no way to automatically include details about the Job Name, duration, status, etc.  You could add a "on failure" precedence constraint that only executes the Notify Operator Task when a step in the maintenance plan fails.  You could hard-code the Notify Operator Task with a specific message indicating the piece of the maintenance plan that failed.  This will get pretty odious very fast, and still doesn't allow automating the error message you receive via email.  This looks like:

Having said that, instead of using the built-in Maintenance Plans feature, you may want to look into Ola Hallengren's scripts that are built for this purpose, and offer a far wider range of options.  You could then use standard Agent notification for the maintenance plan the way you do for other jobs.

http://ola.hallengren.com/


Naming The SSRS Report Name based on the Parameters Passed

What you are asking is to be able to create a report name dynamically, which is not something that can be programmed, on download, say, from Report Manager. You can (although I've never tested this) possibly do this through a  subscription using @ParameterName @ReportName. citation from msdn It's about the best you can do for now, and there's a link on the citation that allows for this feature to be voted up.


During its operation, PostgreSQL records changes to transaction log files, but it doesn't immediately flush them to the actual database tables. It usually just keeps the changes in memory, and returns them from memory when they are requested, unless RAM starts getting full and it has to write them out.

This means that if it crashes, the on disk tables won't be up to date. It has to replay the transaction logs, applying the changes to the on-disk tables, before it can start back up. That can take a while for a big, busy database.

For that reason, and so that the transaction logs do not keep growing forever, PostgreSQL periodically does a checkpoint where it makes sure the DB is in a clean state. It flushes all pending changes to disk and recycles the transaction logs that were being used to keep a crash recovery record of the changes.

This flush happens in two phases:

Buffered write()s of dirty shared_buffers to the tables; and
fsync() of affected files to make sure the changes really hit disk

Both of those can increase disk I/O load. Contention caused by these writes can slow down reads, and can also slow down flushing of WAL segments that's required in order to commit transactions.

It's been a longstanding challenge, but it's getting worse as we see systems with more and more RAM so they can buffer more data and take longer to write it out. There's discussion between the Linux and PostgreSQL communities on how to deal with this at the moment, as discussed in this LWN.net article. (LWN.net won't be able to keep writing this sort of great work if people don't subscribe. I'm a subscriber and sharing this link because it's useful and informative. Please consider subscribing if you want to see more of this sort of thing.)

The main thing you can do to reduce the impact of checkpoints at the moment is to spread checkpoint activity out by increasing checkpoint_completion_target so that more of the data has been written out by the time the final checkpoint arrives. This has a cost, though - if you update a page (say) ten times, it might be written to disk multiple times before the checkpoint with a high completion target, even though it only strictly had to be written out once for crash safety. A higher completion target makes for smoother I/O patterns but more overall I/O overhead.

The other thing you can do to help is tell your operating system to immediately start writing data when it gets buffered writes. This is like the kernel side of setting checkpoint_completion_target and has a similar trade-off. See the linux vm documentation, in particular dirty_background_bytes, dirty_background_ratio, dirty_expire_centisecs.


Creating a hot standby with Postgres 9.6

2016-12-22 07:24:30 UTC [10728-3] LOG:  consistent recovery state reached at 3/4D000130
2016-12-22 07:24:30 UTC [10728-4] LOG:  redo done at 3/4D000130
2016-12-22 07:24:30 UTC [10728-5] LOG:  MultiXact member wraparound ?protections are now enabled
2016-12-22 07:24:30 UTC [10732-1] LOG:  autovacuum launcher started
2016-12-22 07:24:30 UTC [10727-4] LOG:  database system is ready to accept connections

Looks like your recovery.conf file has ignored.  Are you shure that the recovery.conf has the right permissions (basically owned by the user that run the service, usually postgres)?


EMP_DEPT database. Try to SELECT records with smallest salary, NULL column record is missing

So why isn't MCQUEEN among PORTMAN and RENO employees with zero
salary?

The issue is this predicate:
WHERE SAL = (SELECT MIN(ISNULL(SAL, 0)) FROM EMP);

At run time, the above comparison for the MCQUEEN row with NULL salary is:
WHERE NULL = 0;

The result of any comparison to NULL is UNKNOWN per the ISO SQL standard. The UNKNOWN result is neither TRUE nor FALSE so the row is eliminated from the result regardless of the value returned by the subquery.
Even though the right side subquery value will never be NULL due the the subquery ISNULL(SAL, 0) expression, the left side SAL value may be NULL so you need to similarly consider NULL values for SAL:
WHERE ISNULL(SAL, 0) = (SELECT MIN(ISNULL(SAL, 0)) FROM EMP);

Rows with NULL or zero SAL values will then be returned unless you have employees with negative salaries. Similarly, one could use COALESCE(SAL, 0), CASE WHEN SAL IS NULL THEN 0 ELSE SAL END), etc. to coerce a non-NULL for NULL values.
A better approach to include NULL may be a UNION ALL query:
SELECT * FROM EMP
WHERE SAL = (SELECT MIN(SAL) FROM EMP)
UNION ALL
SELECT * FROM EMP
WHERE SAL IS NULL;

The advantage of this technique is the queries can leverage an index on the SAL column (if one exists) to execute more efficiently.
I hope this helps you understand the technical reason why the row isn't returned. In the real world, the semantics of a NULL value need to be considered. For example, does the company really have employees with unknown salaries? If not, it's a flaw in the data model. If unknown salaries are allowed, why should a query for minimum salaries return unknown salaries since the actual employee salary could be higher?


How can I represent the entity types depicted in an ERD as relations with attribute names?

Considering the Course entity type that you describe, I have made the following assumptions:

A Course is primarily identified by its CourseNumber
A Course is alternately identified by its Name
A Course is registered at a CreatedDateTime

So I reformulated the situation as follows:

A Course is the dependent in zero-one-or-many CourseDependences
A Course is the determinant in zero-one-or-many CourseDependences

Then, I supposed that:

A CourseDependence is primarily identified by its DependentCourseNumber and DeterminantCourseNumber
A CourseDependence is registered at a CreatedDateTime

This means, yes, that there is a conceptual-level many-to-many (M:N) association (or relationship) that may involve distinct instances of the entity type called Course. Such an association implies the existence of an associative entity type that, as noted, I denominated CourseDependence.

Consequently, from the set of formulations described above, I have derived the IDEF1X1 diagram shown in Figure 1:

In this way, employing the notation that you use to express relations along with their corresponding attribute names, I would represent (a) the Course entity type and (b) the CourseDependence associative entity type as two distinct relations in the following way:

Course = (CourseNumber, Name, CreatedDateTime)
CourseDependence = (DependentCourseNumber, DeterminantCourseNumber, CreatedDateTime)

Therefore, the Option 3 exposed in your question would be very close to an accurate possibility to express the relevant aspects.

Endnote

1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by the sole originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


Should I normalize a relational database design if I know the data will never be updated?

It is not clear what the source of your data is, but your comment "these are archives pointing to real data at a point in the past, and since the past can't be changed, these rows won't either!" implies that your "new" database is a copy of subset of the "current" data.

Assuming that your are coping/moving a subset of old production data to a designated archive location, I would suggest not making any changes in the database design.

I would expect users have existing queries that they use to extract required production data.  As an end user I would expect my existing queries to work on the archive data.  If you make any design changes you put my existing solutions at risk.

TL:DR; Don't make changes to archive copies of data, existing solutions for retrieving specific data, are dependent on existing structure.

Edit

Per your most recent comment This data exists but not in database format. Like say in giant CSV files in a hard disk, that can't be queried You have a large flat file.  In this case you designing a database structure makes sense.  As Jonathan says in his comment You may get space savings by normalizing the data

You also need to consider the cost of your time; Is the potential space savings worth the cost of your time to achieve it? Are you moving the data so it can be queried, will the amount of time you spend result in notable resource saving for the end users? There are several business case questions that you would need to decide internally.

Now if this is a spare time project with no deadlines, it could be a great learning experience, go for it.

If you do decide get excentric with the design, I strongly suggest that you don't make design changes after the import AND you keep your import code.  Business user have a habit of finding just one more data set (disk) that needs go with the other that they promised was the only one that would ever exist.


Does a mirroring configuration requires the database do stop receiving requests?

After you take your transaction log backup and restore it on the mirrored pair, you might have taken more transaction log backups.  If so, try to catch up on the backups then init the mirror.  At that point all the old transactions that are not replayed with be queued up and you will endure a DB_MIRRORING event on your log file which will stop the transaction log from truncating those transactions that have not replayed yet.  Do note if your network is really slow those transactions might continue to get queued up, it's critical to have an appropiate IO subsystem and network on the 2nd pair so it can keep up.

I'm not sure if you can in your environment, but if the transaction log backups happen too fast, perhaps then you have a IO subsystem problem you need to review on the 2nd pair or a networking problem.  Worst case, you could pause transaction log backups if it isn't going to cause a bad growth issue.  Otherwise you could look at using a DIFF backup to fully catch up then do 1 transaction log restore.

Also, consider high performance mode over high safety mode if you can, but if you can't just make sure your network and diskIO are production quality.  Also set up monitoring for log delays, it will cause massive growth on the primary if you don't.


Preparing a former master to run as slave again

The only officially supported method is to make a new base backup, or:

pg_start_backup() on the new master
rsync from new master to old master
pg_stop_backup()
ensure WAL got copied over and applied
create recovery.conf
start resynced server

The 3rd party tool pg_rewind is intended for that purpose. I haven't worked with it as yet and can't vouch for it in production.


Performance comparison between using Join and Window function to get lead and lag values

The relatively low row-mode performance of LEAD and LAG window functions compared with self joins is nothing new. For example, Michael Zilberstein wrote about it on SQLblog.com back in 2012. There is quite a bit of overhead in the (repeated) Segment, Sequence Project, Window Spool, and Stream Aggregate plan operators:

In SQL Server 2016, you have a new option, which is to enable batch mode processing for the window aggregates. This requires some sort of columnstore index on the table, even if it is empty. The presence of a columnstore index is currently required for the optimizer to consider batch mode plans. In particular, it enables the much more efficient Window Aggregate batch-mode operator.

To test this in your case, create an empty nonclustered columnstore index:

 -- Empty CS index
CREATE NONCLUSTERED COLUMNSTORE INDEX dummy
ON dbo.tab1 (id, [time], [value])
WHERE id < 0 AND id > 0;

The query:

SELECT
    T1.id,
    T1.[time],
    T1.[value],
    value_lag =
        LAG(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time]),
    value_lead =
        LEAD(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time])
FROM dbo.tab1 AS T1;

Should now give an execution plan like:

...which may well execute much faster.

You may need to use an OPTION (MAXDOP 1) or other hint to get the same plan shape when storing the results in a new table. The parallel version of the plan requires a batch mode sort (or possibly two), which may well be a little slower. It rather depends on your hardware.

For more on the Batch Mode Window Aggregate operator, see the following articles by Itzik Ben-Gan:

What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 2
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 3


Why mysqldump is returning with error 1143: Couldn't execute 'show table status like

There is a bug report on this: mysqldump: Couldn't execute 'show table status': SELECT command denied to user. Surprisingly, it's not a bug at all. here is why:
customer_cohort_paid might be a view. Whatever user created the view is not listed anymore in mysql.user. What this does is make such a view incapable of being dumped.
You could do one of two(2) things
SUGGESTION #1
Force the dump with -f
mysqldump -f -ubackup -psomething --single-transaction --quick somedatabase | gzip > 4_19.gz

If you can force the dump to make views appear in the dump file, you may want to edit it (See my old post Modify DEFINER on Many Views or this other one How do I change the DEFINER of a VIEW in Mysql?)
WARNING: If any one extended INSERT command has a syntax problem or improperly escaped or encapsulated data, the hundreds of rows in that one INSERT may not be loaded.
SUGGESTION #2
Find out the original user who made the view, and recreate that user
Run this query : select * from mysql.tables_priv;
This will show all table-level and column-level grants. Look at the first three columns and figure out what GRANT command to make that user again.


calculating the total number of rows in a table - fails when it is a heap - is there any fix?

make use of DBCC UPDATEUSAGE
as mentioned in the comments by  Dan Guzman
has produced the following results:

DBCC UPDATEUSAGE(N'WMSData',N'dbo.WMS_SLALoad') WITH COUNT_ROWS

After that the results of my original select was accurate enough for my purposes.


Odd SQL Server Studio error, but not on SQL Server Express?

The remote database has compatibility level 80, which doesn't work with db_id().  More info on a related StackOverflow question.

(Just dumping this into an answer from the question comments so it disappears from the Unanswered list of questions.)


SQL Developer: choose the tablespace where a new table will be stored

Check Advanced, and under Storage, Define Storage, where you can choose the tablespace.


Difference between chained and migrated rows

Chained and Migrated Rows are explained in the Logical Storage Structures of the Concepts Guide.
A migrated row would be a row who's column data was completely transferred from one block to another due to an update. The original block would essentially only have a "forwarding address" stored for that row.
A chained row would have parts of its column data in multiple blocks. The original block would contain both actual column data and a forwarding address for the rest of it. (You can get rows chained to more than two blocks.)
Both are implemented the same way deep down, so they're really two aspects of the same thing.
Also note that for tables with more than 255 columns, all rows are technically chained - one "row piece" can only contain 255 column values. The chaining can happen in the same block, or with other blocks depending on space availability (and isn't particularly "bad" if all the data ends up in the same block).
The only way, as far as I know, to get accurate data on row chaining is to use:
ANALYZE TABLE your_table [partition (your_part)] LIST CHAINED ROWS

See Listing Chained Rows of Tables and Clusters.
This is potentially expensive, the whole table needs to be scanned. Statistics gathering doesn't fill the CHAIN_CNT column of the dba_tables view. (I think it might have at some point, but it doesn't in 11.2 at least.)
You can monitor the table fetch continued row1 statistic (v$sysstat) to see if a query is affected by chained or migrated rows, but I don't believe you can have that metric per-session so either you need a quiet system to measure, or the reading will be "noisy".
The Secrets of Oracle Row Chaining and Migration has interesting information about chained and migrated rows, how you measure them, and potential ways of fixing them.
As always, don't go about rebuilding tables or changing storage parameters "just because" you see chained or migrated rows. Do so only if you measure that it's actually causing you performance problems.
1 From Statistics Descriptions:

Number of times a chained or migrated row is encountered during a fetch
Retrieving rows that span more than one block increases the logical I/O by a factor that corresponds to the number of blocks than need to be accessed. Exporting and re-importing may eliminate this problem. Evaluate the settings for the storage parameters PCTFREE and PCTUSED. This problem cannot be fixed if rows are larger than database blocks (for example, if the LONG datatype is used and the rows are extremely large).

See also Table Fetch by Continued Row.


How to tell what invoked a trigger in SQL Server?

Couple of Options:

Option1:

Your best option is to run a server side trace with filtering options enabled.

This way you can narrow down the amount of data captured.

Option2:

Depending on the version (Enterprise), you can use SQL Audit as it allows you to track DELETE, EXECUTE and other Actions on the Object level as well. So you can filter the table that you are interested and enable the audit. Note that SQL Audit behind the covers
uses Extended Events.

Option 3: (I have not used this .. but its from msdn)

Light weight SQL Server procedure auditing without using SQL Server auditing version 2

Another option would be to use CONTEXT_INFO as described in this answer. I dont see if this will help you as you will not be modifying legacy code.