nested if expressions for string in excel source file in derived column expression

Your expression will probably look something like this, in a derived column transformation.

(LEFT(Product, 3) == "ZYT" ? "ZYTOL" :
    (LEFT(Product, 2) == "AZ" ? "AZAB" :
        (LEFT(Product, 2) == "UXO" ? "UXOL" :
            (LEFT(Product, 3) == "ADX" ? "ADXIZN" : Product))))

Please note that depending upon your data type, you may need to cast some of your strings to Unicode/non-Unicode types to get everything to work smoothly.

The syntax uses the ternary operator https://msdn.microsoft.com/en-us/library/ms141680.aspx


Unable to change database owner to a user that is already mapped as user

You can easily change the schema ownership to dbo, then you should be able to drop the user, and add them back using ALTER AUTHORIZATION:

ALTER AUTHORIZATION ON SCHEMA::[schema_they_own] TO dbo;
DROP USER [user_name];
ALTER AUTHORIZATION ON DATABASE::[database_name] TO login_name;

Now, they will be the dbo user in the database (and will have automatic ownership of the schema).

You may need to change more than one schema; you can generate a script to do this using dynamic SQL.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER AUTHORIZATION ON SCHEMA::'
  + QUOTENAME(s.name) + N' TO dbo;
'
FROM sys.schemas AS s
INNER JOIN sys.database_principals AS dp
ON s.principal_id = dp.principal_id
WHERE dp.name = N'database user name';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

If you are happy with the command generated, you can copy and paste the output and run it, or you can uncomment the EXEC and run it again.


The obvious alternative to the seq scan on the child table would be the nested loop in which it has to probe the child table by index 20,718 separate times.  (Actually 21,215 times, but of course the planner doesn't know that at the time it makes its decision).  Each one of those probes is going to go to an assumedly random leaf page in the index, and then follow up to a random spot in the child table itself, triggering a bunch of random IO.  If all that of that data is already found in the cache, this will be a good plan.  But if it actually has to hit the disk for each one this is going to be ungodly slow (especially since your disk seems to be powered by gerbils--your pre-edit plan where it took 22 seconds to seq scan the child table is simply horrible).

If you want to see the alternative plan, you can set enable_hashjoin = off before executing the query.  This is generally not a setting you would want to do in production, it is more for exploratory purposes.  But as a last resort, you could make this setting change locally just for running this query, then reset it.

If you like the nested loop because most of your data actually is in memory most of the time, you can signal this to the planner by reducing the value of "random_page_cost" to something closer to 1.  In my hands, lowering from the default of 4 down to 1.4 was enough to flip the plan from the seq scan to the nested loop. If most of your data is always in cache (not just the child table and its index) then it might be reasonable to make a global change to this parameter.  If you do that, however, you should have a plan to pre-warm your database in case you reboot the machine (such as this).  Getting the data back into cache the natural way is likely to be slow and painful.


-Each Bureau has employees that only work at the bureau level (think CEO's,CIO's,etc) and employees that work at the many office level
-Each office has people who only work at the office level (maybe managers, chief,s etc) and people who work at the many divisions levels
-Each division has people who work only at the division level (division chiefs, for example) and employees who work on projects
-Also, each division has projects that are run by division staff (employees, not the high level divisions chiefs)

These rules imply that each employee has a role that requires they work either only for a bureau, an office, or a division, or requires they work for a given bureau and many offices, or an office and many divisions, or a division, and that only employees who work for a division but are not division staff run projects.  The schema shown however asserts only that employees work for a division in any given role and that any of them can run a project.  Changes are needed to bring the schema into line with rules.
The simplest way to address the business rules is to add tables to associate employees to offices and bureaus in addition to divisions.  Each employee would then be associated to each organization level in which they work.  Employees working only at a bureau, or office, or division would only be associated to that level.  Employees working at a bureau who also work at many offices would be associated to the bureau in that associative table and then also associated to the many offices by that associative table.  Note this approach only addresses where each employee actually works and not the rules as to which roles can work at which organizational levels.  To address the rules more discovery and modeling is required and is beyond the scope of this answer.
Secondly, the business rules imply that an employee has a single role - say a CIO or office manager or division chief - and not a role per associated location.  The schema can be brought into agreement with this rule, if correct, by making the role table a parent of the employee table such that each employee is assigned a single role.  Now this implication may or may not be true.  Perhaps an employee can have only a single role at a moment in time, but many over time.  In this case temporal columns are also required.  Perhaps an employee really can have many roles at the same time at different organization levels, in which case the role would not be determined by the employee.  Ultimately, more clarity is needed on the full scope of the business rules by having the subject matter expert review the model and determine if it represents accurately the rules by which they operate.  It is vital to fully understand and document the functional dependencies and natural keys in order to arrive at the correct logical design.  Right now the diagram does not show any natural keys so even if the functional dependencies were presented it would not be possible to determine if the logical design is in agreement with them.  Finally, the diagram shows that each parent entity is optional and I doubt that is really the case.  A business SME can determine under what circumstances the parent occurrence is optional, and for each of those circumstances more work is needed to determine why the parent occurrence is optional and resolve it so it is no longer optional.
I hope these points help in improving the design to meet all the informational objectives.


Very Simple SELECT Takes Too Long (300k rows, 60s)

The 6 Selects are due to live statistics. Never mind them.
Based on the fact that Client procesing time takes about 99% of the total time, issue is definitely network/client.
As proposed, try the
select count(*) from report_MyTableName

or
select * into #tmpTable from report_MyTableName

to check performance without network.
In statistics, there is the total amoun received from server. too bad the full info is missing so we can only guess about amount of data vs network speed.
Either provide full info on bytes send or info about columns (types and size) so we can estimate the data size.
EDIT:
So based on the count(*), it is obvious the total amount DB server works on it is about < 0.1s. The CPU time matches but the elapsed time is not normal.
How long did the count(*) take? Live query show ~0.25s, which seems right, elapsed time shows 42 seconds.
My guess is still that the issue is network/client, not server.
What is your ping against the server?


Database design for multiple account types management system

You are going down the path of table explosion with resulting complicated queries.
In a relational database, you want fewer tables but more columns, fewer columns but more rows.
Applying this mantra: your two types of accounts should be one accounts table with a type column. You could have two child tables to store data specific to each type of account if there would otherwise be too many null columns. But if there are only one or two such attributes I would personally add them as nullable columns and add a constraint something like:
 alter table accounts
   add check (type = 'normal' and pro_attribute is null or type = 'professional' and pro_attribute is not null);

where pro_attribute is the name of an attribute that only applies to professional accounts.
This could be simplified to:
alter table accounts
  add check ((type = 'normal') = (pro_attribute is null));

but that's not clearer.
Then for your profiles, you need to apply the same pattern: One profiles table with a service column. If individual services have different attributes you can store them in their own child tables.
As far as the relationship to services: it sounds like each account has one profile for each service that exists. In this case I would create the profiles table with columns account_id (foreign key to accounts), service (or service_id as a foreign key to a services table) and any attributes you need for that particular service for that particular account.


Tool to create a TSQL script from a SQL Server 2008 trace file

You could use ClearTrace by Bill Graziano (free) which cleans up trace queries and allows you to sort/filter the trace files and still have a readable tsql from the trace.

Courtesy reference of Brent Ozar's presentation on "Using Perfmon and Profiler" at SQL Bits 2010


Microsoft SQL Server Connection Coloring

Boiling down the question to the basics:

Q: "is there some way to set a policy or something on the Microsoft SQL Server instance itself that will cause any SSMS clients to be colorized?"

No. SSMS query color-coding is part of a client-side connection and that data is stored in the registry. You could theoretically export that registry setting and apply it to anyone in your company with SSMS, but it's not a trivial task for us database people.

I'd take that request to your sysadmins with Active Directory Group Policy administration duties and explain what you need. It's a fairly common type of request for companies that manage software deployments centrally. Unfortunately, it's not point-and-click easy, and you'll need to update the registry keys every time you add or rename a production server. These types of mass client deployments usually require change requests because modifying the Windows registry isn't something you want to be doing often.


Return previous running total when value is null in a time series

One way is to generate one row per (customer_id, the_day) before running the window function. Like:

SELECT c.customer_id, d.the_day
     , sum(t.tsla_amount) OVER w AS tsla_running_amount
     , sum(t.goog_amount) OVER w AS goog_running_amount
FROM   (
   SELECT the_day::date
   FROM   generate_series(timestamp '2020-01-01'
                        , date_trunc('day', localtimestamp)
                        , interval '1 day') the_day
   ) d
CROSS  JOIN (SELECT DISTINCT customer_id FROM transactions) c  -- !
LEFT   JOIN (
   SELECT customer_id
        , created_at::date AS the_day
        , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
        , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'
   GROUP  BY customer_id, created_at::date
   ) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

db<>fiddle here

If the table is big, and there are only relatively few distinct customers, you might want to optimize performance. See:

Optimize GROUP BY query to retrieve latest row per user

But you would typically have a separate table of customers to work with ...

If you want 0 instead of NULL in rows before the first transaction of a customer, add COALESCE. Like:

SELECT c.customer_id, d.the_day
     , COALESCE(sum(t.tsla_amount) OVER w, 0) AS tsla_running_amount
     , COALESCE(sum(t.goog_amount) OVER w, 0) AS goog_running_amount
FROM ...


Scripting a Maintenance Plan in SQL Server Express

You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you.

The most common method is to use Windows Task Scheduler, but if you have any other job scheduling application already that might be a better choice. Either way, you'll need to get comfortable with the sqlcmd utility if you aren't yet.

If I were to use Task Scheduler, I would personally chain any dependencies together in .bat files, assuming your maintenance needs are fairly simple since its an Express instance.

I've got a website post with all of the specific steps to setting up Ola on Task Scheduler if that is the route you decide to pursue. There is also a Microsoft KB with similar (though pictureless) instructions.


How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD?

It looks to me as though child_1 and child_2 are two instances of a single entity type.

If so, then you have a (second) relationship such that

Every parent must have one and only one favourite child
Every child may be the favourite of zero or more parents.

The (first) relationship being

A parent may have zero or more children (unless, of course you have defined "parent" such that a child is a pre-requisite of being a parent ;-))
A child must have exactly two parents (under basic human biology ;-))

Your first relationship may, of course, be different depending on the context.


The syntax of you query is not often called "correlated subquery". A correlated subquery would be like this:

SELECT
    ndo.sku AS ParentSKU,
    (
        SELECT SUM(g.sessions)
        FROM gasessiondata AS g
        WHERE g.date >= ndo.FromDate
          AND g.date <= ndo.ToDate
    ) AS s
FROM
    Temp.NumberOfDaysOnline AS ndo ;

Your query is similar to a LATERAL join (CROSS / OUTER APPLY in SQL Server). This type of correlated joins has not been implemented in many DBMS.

It's easy to rewrite though so the join is done first and then the GROUP BY. This simple query should be supported by most, if not all DBMS:

SELECT
    ndo.sku AS ParentSKU,
    SUM(g.sessions) AS s
FROM
        Temp.NumberOfDaysOnline AS ndo
    JOIN
        gasessiondata AS g
    ON  g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list

Seems that Hive supports only equi-joins and cross joins. Moving the condition to the WHERE clause might solve this:

SELECT
    ndo.sku AS ParentSKU,
    SUM(g.sessions) AS s
FROM
        Temp.NumberOfDaysOnline AS ndo
    CROSS JOIN
        gasessiondata AS g
WHERE
        g.date >= ndo.FromDate
    AND g.date <= ndo.ToDate
GROUP BY
    ndo.PK,             -- the PRIMARY KEY of the table
    ndo.sku ;           -- and any column used in the SELECT list


SQL Server INNER REMOTE JOIN returns more rows than INNER JOIN

TOP 100 with no ORDER BY means it is undeterministic which 100 rows from the remote table end up participating in the join. This is execution plan dependant and can vary.

If it is a one to many relationship it may be the case that one batch of 100 rows has more matches on the other side of the join than another different batch of 100 rows.

You should specify an ORDER BY (inside the derived table) on some unique column or combination of columns to ensure deterministic results.


Oracle 10g: Is it possible to designate a undo point?

John has pointed you at the Flashback Query options useful from a developers point of view. If I read you correctly then you are probably looking for the Flashback Database and Restore Point functionality documented here which allows you to do precisely this. Flashback Database does have some requirements over and above the flashback query functionality (for example it requires a flash recovery area to be configured and extra disk space compared to a non-flashback database ).


When does SQL Server determine to use a new index?

when does SQL Server notice the new indexes and determines whether they actually help the query?

Immediately.  Creating an index causes a recompile because of a schema change.

Here's an illustration of this.  First to setup the objects in the test database:

use TestDatabase;
go

if exists (select 1 from sys.tables where object_id = object_id('dbo.TestTable1'))
    drop table dbo.TestTable1;
create table dbo.TestTable1
(
    Id int identity(1, 1) not null
);
go

insert into dbo.TestTable1
default values;
go 1000

if exists (select 1 from sys.procedures where object_id = object_id('dbo.GetIdFromTestTable1'))
    drop proc dbo.GetIdFromTestTable1;
go
create proc dbo.GetIdFromTestTable1
    @Id int
as
    select Id
    from dbo.TestTable1
    where Id = @Id;
go

As you would expect, running this stored procedure would result in a table scan:

exec dbo.GetIdFromTestTable1 500;
go

Now let's create an index that we know this query can benefit from:

create unique index IX_TestTable1_Id
on dbo.TestTable1 (Id);
go

Re-execute the stored procedure and take a look at the post-execution plan:

exec dbo.GetIdFromTestTable1 500;
go

We can see that our table scan turned into an index seek.  This shows that a new plan was generated.

Why?

We can trace this behavior with an XEvents session:

if exists (select 1 from sys.server_event_sessions where name = N'RecompileTracing')
    drop event session RecompileTracing
    on server;
go
create event session RecompileTracing
on server
add event sqlserver.sql_statement_recompile
(
    set
        collect_statement = 1,
        collect_object_name = 1
)
add target package0.event_file
(
    set
        filename = N'RecompileTracing.xel'
);
go

alter event session RecompileTracing
on server
state = start;
go

/*
alter event session RecompileTracing
on server
state = stop;
go

drop event session RecompileTracing
on server;
go
*/

The output we get from the above illustration/repro is that we had a sql_statement_recompile on the statement select Id from dbo.TestTable1 where Id = @Id; (condensed with removed whitespaces/tabs/newlines) and the recompile_cause is Schema changed.


DISTINCT is evaluated last or almost last of all the clauses. Therefore, it can't be used on the same level as ROW_NUMBER to get the results you want. Either use a derived table with DISTINCT:

SELECT
  Dept
FROM (
  SELECT
    Dept,
    ROW_NUMBER() OVER (ORDER BY Dept) AS RowID
  FROM (
    SELECT DISTINCT
      Dept
    FROM Department
    WHERE Dept ...
  ) AS d
) AS Calls
WHERE RowID ...
;

or replace DISTINCT with GROUP BY:

SELECT
  Dept
FROM (
  SELECT
    Dept,
    ROW_NUMBER() OVER (ORDER BY Dept) AS RowID
  FROM Department
  WHERE Dept ...
  GROUP BY Dept
) AS Calls
WHERE RowID ...
;


How to make an union on 2 joined tables without affecting the sum on the column?

Ok, I solve it like this:

select CANT.ID,CANT.CANT_VAL,CANT.COD_VAL,CANT.FK_ID_CHEST,CANT.C1,CANT.C2
        from cant
    union
    select COL.FK_ID_COL_DMA,COL.CANT_VAL,COL.COD_VAL,CANT2.FK_ID_CHEST,0,0
        from col
            inner join cant2 on cant2.id=COL.FK_ID_COL_DMA

Thanks!


Looking for good, but quick references for underlying OS concepts related to SQLOS (Memory Access, Scheduling, etc...)?

This is a very broad quesiton and will be eventually closed.

Below page from BOL will be helpful to you :

Thread and Task Architecture
Platform layer for SQL Server - A new platform layer in SQL Server 2005 to exploit new hardware capabilities and their trends by Slava Oks
Architectural Overview of SQL Server 2014’s In-Memory OLTP Technology
SQL Server Threading Model - This is to Paul Randal's online course about wait stats on Pluralsight.


Postgresql: A bit confused on listing all roles in my system

If you want to find out, what exactly the "backslash commands" do, just start psql with the option -E (or --echo-hidden).

If you do that, you will see that \du uses the following query:

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

So \du simply filters out all roles where the name starts with pg_, because role names starting with pg_ are reserved for Postgres built-in roles and you can't create a role with that prefix manually.

The default roles are explained in the manual


Updating data from a report: any potential pitfalls?

Honestly, don't do it. Yes it is possible to do it by declaring a global variable to your report and then passing that to the statement that generates the result set to do some processing before returning data but it is messy and ill-advised at best. What is to say that in the future you won't want to add more variables to the report? At this point it technically becomes a form - which is what ASP.NET was designed to do - not what SSRS/ReportViewer was designed to do.

You say that you are using Report Viewer in a ASP.NET web application - so my recommendation would be to handle the print logic within the application itself if you can and leave the report to do what it does best. We have an application at our company that produces a report in a similar fashion - and we needed to track who was printing the report and when - we did this by disabling the print functionality within the report and then supplied functionality within the application to render the report to a PDF and then print it. This allowed us to be very flexible in what we track/audit and how the report was generated and where it was printed.

Also, keeping the two pieces of logic separate will be better in the long run - for instance if you need to change the report this could be done external to the application and then you just provide the rdl file and replace the old one. Putting code within the report to change data means extra testing and blurs the boundaries of responsibilities.

Additionally, as you mention above - there is the possibility that data could be compromised one way or another (SQL Injection etc..) and having the application validate the input is by far the correct thing to do - not to mention adding all the extra SQL needed to validate, update and then return data would slow your report down. This may not seem like much if you only have a few users but if your user base is large it could cause issues.

It may seem like the quickest solution right now but that is not necessarily the right solution. Keep it simple and separated and you will find you will have a lot less problems in the long run.

I hope this helps.


Microsoft sql server management studio (SSMS) shortcut for changing size of sql/result panes

SSMS has no equivalent shortcut to the CTRL+B shortcut from Query Analyzer.

In my opinion, the best alternate feature is the ability to have query & results in separate tabs. In this way, I can flip between full-screen query & results easily, and maximize screen real estate for both.

To enable this in SSMS:

Go to Tools-->Options...
In the left navigation tree, go to Query Results-->SQL Server-->Results to [Grid/Text] (The option exists for both grid & texts)
Mark the checkbox for "Display Results in a separate tab"

Optionally mark the checkbox for "Switch to results tab after the query executes"

You will need to open a new SSMS query tab for the change in setting to take effect.

You can then use F6 (or Shift+F6) to cycle your focus forward (or backwards) between Query, Results, and Messages tabs. If you have multiple result sets on the Results tab, each press of F6 will bring your query focus to the next result set.


COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

This is easily achievable with an in-line subquery :

select  m.id,
        (select count(*) from messages where parent_id= m.id ) as ChildCount
from messages m
where m.parent_id = 0

Note that no group by is needed because a sub-query is used.


External authentication using Oracle Standard

I'm afraid it is practically impossible. In Oracle's terminology external authentication means: "If users's name on client machine is identical to user's username in database, then user does not have to enter ANY password". This way of authentication is very weak and must be combined with other security features, for example you allow users to login only from one trusted, secured host.

Oracle does not support any PAM modules (like Informix for example) or whatever. The only option is how to connect Oracle to an external auth system are:

Kerberos/RADIUS - requires Enterprise edition, plus Advanced security option, which might require extra licencing
N-tier authentication - something like Unix su on Oracle level. But this requires support on application side.
Password protected role enabled by Logon trigger.


Several mongod instances that do automatic failover without duplication of data?

Your setup is plainly wrong.

First, what sense does it make to have automatic failover when your storage system or the connection to it creates a single point of failure? And if you have a storage system which eliminates every single point of failure ( redundancy in power, network interfaces plus network infrastructure, RAID controllers, main boards and according RAM ), this would be much more expensive than setting up two simple boxes (plus a virtualized arbiter) in a replica set. And you still would have only the same advantage as the much simpler and easy solution.

Next, the backup issue. Granted, SAN snapshots are propably the easiest way to create backups and they are pretty fast. However, there will be an interruption in the service, however short it may be, when doing a snapshot on one instance of data only.

Third, a situation dreaded in all high availability scenarios: the split brain situation. How should you deal with that when both data bearing nodes would try to access the same data set? Since one would hold the lock on the data set but is demoted to secondary by an election and the elected primary can not get hold of the data set as the other instances is holding the lock, the newly elected primary would step down. Let me note that this scenario could only very theoretically take place if you started a second node by means of HALinux or something with a very strange setup. You would start to have to deal with something like STONITH, which comes with it's very own and sometimes rather delicate problems, further increasing the most expensive ressource you have: continuous administrative costs. A classical HA setup requires 24/7 monitoring and response times measured rather in seconds than in several minutes. MongoDBs failover capabilities are - when set up correctly - reliable and usually works without the need of manual intervention unless in rare edge cases. Edge cases of a failover, that is. So the edge cases of anyway rare edge cases.

Having that said: no, what you want is not possible with MongoDB out of the box. The reason for that is that there are lock files created in the data directory, preventing the second server from accessing the same data. So what you would have to do is to set some HALinux, remove that lock file before firing up the second server and then fire up the IP address. This comes with some other serious drawbacks from the MongoDB side, however. The data may be inconsistent, as the first server might not be able to flush it's data to the data files. Thinking of it twice, it might well be that the lock is only checked during startup, which might actually lead to the two servers flushing their data on the same data set, resulting in data FUBAR.

TL;DR: Use MongoDB (or any other tool, for that matter) within it's intended environment and for it's intended purpose. Don't do it as you originally planned. It's neither worth the effort nor does it give any advantage except saving - compared to the costs you create by the idea like how you want to do it - relatively cheap disk space. Don't take chances with your production data.


When should indexes be dropped and recreated?

If this is cyclical ETL, and you are in a development (i.e. NOT LIVE) data environment, then you definitely should manage your indexes as a part of your load cycle.

I do this for several data sets every month, the largest of which adds around 100 GB monthly to a 5 TB data set.

I have done extensive testing, and from my own experience the most efficient way to load with regard to indexes is:

DISABLE non-clustered indexes, leaving clustered index intact
Perform load of raw into your data table
REBUILD NC indexes

If you only add rows periodically as part of managed ETL, this is the way to go.  This also ensures all your statistics are up to date.

For stats, it's important to note that adding 20GB to a 1TB database won't reach the tipping point for a stats auto-update, so you can add a whole month of data without ever updating statistics.

Rebuilding your NC indexes is a good way around this.  You may want to also do a clustered index rebuild periodically if fragmentation gets high (depending on your table structure and your clustered key).


Login lifespan for password on SQL Server

Per the following article, This information can be found in:

Start -> All Programs -> Local Security Policy

Then navigate to Account Policies -> Password policy

You should see something similar to this:

http://www.mssqltips.com/sqlservertip/1088/sql-server-login-properties-to-enforce-password-policies-and-expiration/


Pre Caching Index on a large table in PostgrSQL

You could use the additional module pg_prewarm. Has to be installed once per database. See:

PostgreSQL: Force data into memory

It can "prewarm" tables as well as indexes. To do it for your index:

SELECT pg_prewarm('test.test_table_idx');

Unless you get index-only scans (which you do not with the index at hand), you might want to prewarm the table as well:

SELECT pg_prewarm('test.test_table');

There are more parameters to narrow down what and how to prewarm. Follow the links.

This is costly and the system might be better off using the cache for something else. If you know the exact query ahead of time and it's a SELECT without side effects, you might just run the query to "prewarm" relevant data pages of index and table.

Aside, you might be better off rearranging PK and index like this:

    ...
    primary key(string_data, date_info);
    create index test_table_idx on test.test_table(date_info);

Now, the PK index can give you index-only scans for the query at hand. Might make a substantial difference. See:

Is a composite index also good for queries on the first field?
Can Postgres use an index-only scan for this query with joined tables?


How to treat numbers within strings as numbers when sorting ("A3" sorts before "A10", not after)

Sorting strings naturally puts "15" before "2" because the first digit in the "15" is a "1", which sorts before "2". Sorting a "2" stored in a string type before the "15" can be done in a few ways. The most efficient way is to have the collation itself handle this internally. This option is not well known and not even available in most places, but any system that implements ICU (International Components for Unicode)** has the potential for allowing this type of sorting (as long as it allows for customizing the sort options), which is often referred to as "natural" sorting.
Handling natural sorting is often done programmatically by chopping the string into its pure alpha and numerical pieces and then sorting on those individually. This is a necessary evil in many cases, but fortunately, PostgreSQL (at least as of version 10), does allow for this internally. You would need to create a custom collation (this one even comes straight from their documentation):
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

and use that in the ORDER BY. Here is a working example of it:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=58763b51a8ccb2360cf387d8c2b91d51
NOTE
Due to collations not being generally well-understood, and the ability to customize them being more recent (for databases, at least) and even more esoteric, I would recommend doing the following when implementing this solution:

Prefix the name of the new collation with "custom_" to increase awareness that this is indeed a custom collation that might have non-obvious behavior and might not exist on other systems (hence might need to be added to a system or application setup process)
Place a comment just after each query that uses this custom collation, pointing out that it is a custom collation, and include a link to the official documentation:
https://www.postgresql.org/docs/12/collation.html#id-1.6.10.4.5.7.5
You could even mention that the -kn-true part enables "numeric" sorting.

BONUS
To more fully demonstrate how the "numeric" collation option works, I added some data to the previous example to show:

multiple / separate groups of numbers within a string are handled separately
different non-digit characters are handled as expected
leading 0's do not affect the result

The additional data is:
DISK 2A
DISK 2B
DISK 2B 33
DISK 2B 4
FILE 62
FILE 7
DIRECTORY 1000000
DIRECTORY 57
DIRECTORY 9999
DIRECTORY 57000
DIRECTORY 057
DIRECTORY 0057
DIRECTORY 52

Here is the updated example:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=20416b0dd731b2cc28b6fdee8ef70ec7

** To be fair, ICU / Unicode isn't "required" for doing this type of sorting, given that any collation or system can implement the same algorithm. However, it is built into ICU, and more and more systems are integrating ICU.


When does a port get assigned if SQL Server is set to TCP Dynamic Ports

Is it therefore possible that a new port will get assigned each time the instance is restarted?

Every time you start the SQLServer Instance, it uses the port that is allocated.

When a dynamic port is assigned to sql server instance, if the port is used by another program, then SQL Server chooses another port at the time of restart i.e. A dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) - but if its used by another program then SQL server will choose a new port.

I generally prefer to use static ports for my sql server installation.

Refer to my answer to a sort of similar question.


Restrict Postgres superusers to local only connections

You'll have to enumerate the superusers:

host   all  superuser1   0.0.0.0/0   reject
host   all  superuser2   0.0.0.0/0   reject
host   all         all   0.0.0.0/0      md5

There should be a very limited, fairly constant set of superusers.


Filemaker Database. How to import to one table then copy information to a different table and populate realted tables

You can import the raw data to a Temporary table.
Loop through each record.
Using Magic key, you can create both parent and related records.


How to get past the 5000 row limit in a SQL Server Reporting Services tablix report?

This turned out to have nothing to do with the Microsoft side at all. The data source we are using is a linked server to Proficy Historian and, by default, only pulls 5000 rows. You have to explicitly tell it either how many rows you want to pull, or tell it RowCount = 0 for unlimited rows. Sorry for the noise!

How To Set Rowcount in Historian OLE DB SQL Query


Get partition column for a table in Greenplum

Thanks to @a_horse_with_no_name for pointing me in the right direction,  pg_partition_columns is the table I need.


Find a username within instance wide objects

If it's in the text of a proc/function you could use the easy and free to use third party plugin of redgate:     SQL Search, there are also ways to do this with TSQL, see answer on stackexchange searching through procedures

However, very likely your proc will fetch the value from a table that stores it, so the proc doesn't have to be hard coded.
If you want to search through a bunch of columns at the same time, this link might be useful to you:
Finding string in different tables


Oracle NVARCHAR2(4000) with AL32UTF8 possible?

If your NLS_CHARACTERSET is AL32UTF8, any VARCHAR2 column will support Unicode (via the UTF-8 encoding).  In 99.9% of cases, you'd never want to create an NVARCHAR2 column.

The NVARCHAR2 column, which uses the NLS_NCHAR_CHARACTERSET which will almost always be AL16UTF16 is really only useful in a couple of cases.  The first, and by far most common, is when you have a legacy application whose NLS_CHARACTERSET doesn't support Unicode and cannot be migrated to Unicode but where you want to support Unicode data in a handful of columns.  That's not the case here.  The second would be when the database does support Unicode but you want to use the UTF-16 encoding for some reason such as the ability to store primarily Japanese and Chinese data using mostly 2 bytes per character in UTF-16 rather than 3 bytes per character in UTF-8.  But if you do that, you're still (barring 12.1 and the ability to have 32k VARCHAR2 columns) limited to 4000 bytes (2000 characters in a UTF-16 column).  Hence the error when you try to define NVARCHAR2(4000) which would need at least 8000 bytes to store 4000 characters.


how to run once a bunch of generated scripts with exception handling?

The result should be stored in a variable and should execute that  SQL.
It's called dynamic SQL.
You can read more about this here

      BEGIN TRY

       DECLARE @sql varchar(max)
        SELECT @sql = COALESCE(@sql ,'')+'exec sp_save ' + ''''
             + name + '.dbo.ref_systemSettings' + ''''+';'
        FROM sys.databases
        WHERE OBJECT_ID(name+'.dbo.ref_systemSettings') IS NOT NULL

        EXEC (@sql)

       END TRY
       BEGIN CATCH

           DECLARE @ErrorMessage NVARCHAR(4000);
           DECLARE @ErrorSeverity INT;
           DECLARE @ErrorState INT;

          SELECT
             @ErrorMessage = ERROR_MESSAGE(),
             @ErrorSeverity = ERROR_SEVERITY(),
             @ErrorState = ERROR_STATE();

          -- Use RAISERROR inside the CATCH block to return error
          -- information about the original error that caused
          -- execution to jump to the CATCH block.
          RAISERROR (@ErrorMessage, -- Message text.
                     @ErrorSeverity, -- Severity.
                     @ErrorState -- State.
                    );

       END CATCH


Rebuild system databases to a different location

I believe all of your questions can be answered here :)

The link covers:

Planned relocation of system databases
Failure recovery of system databases
Moving Master
Moving Resource
Other considerations


Which database client to use is generally personal preference, and sometimes based on which client makes certain tasks the easiest to do.

That said, pgAdmin3 doesn't connect cleanly to PG10+.  In that regard you will need to switch entirely to pgAdmin4 at some point.  If now is a convenient time then skip pgAdmin3.

There are some obvious and non-obvious UX differences between the two, some features easier in pgAdmin3, some easier in pgAdmin4, but ultimately some users are just more comfortable with pgAdmin3.  How long you want to allow your fellow users/admins to use it is always dependent on your own use case.  I personally configured pgAdmin4 to use a browser that I don't normally use and that gives me a seperate location on my task bar to find my various query windows. This, and opening new query windows in new browser tabs have made pgAdmin4 more friendly for my own personal use.

While discussing database clients, however, don't forget about psql.  The more I use it, the more I go to it first.  The -E command line option is a great learning tool for deciphering how the pg_catalog tables work and the \gexec internal command is extremely useful for as is \watch.


Is there a way to start a script on the publisher when synchronization starts?

There isn't a built-in or easy way to do this with Web Synchronization.  You're best bet is to programmatically monitor Merge Agent sessions at the Publisher/Distributor using sp_replmonitorhelpmergesession and sp_replmonitorhelpmergesessiondetail.  You can script this out and poll on a schedule.


Is it possible to have 1 slave server with multiple master?

Yes, you can run on different port and configure replication through master.
If you have different table name or db name in all master, you can for with multi source replica in that case you just need to install single instance for replica node of all masters. You can go through here and comment if have any doubt and need more clarification.


I ended up applying the hotfix for KB2845380 and the issue appears to be resolved.


PostgreSQL query very slow when subquery added

My colleague has found a way to change the query so that it needs a simple rewrite and does what it needs to do, i.e. doing the subselect in one step, and then doing the further operations on the result:

SELECT mtid FROM publication
WHERE
  mtid = ANY( (SELECT ARRAY(SELECT 9762715))::bigint[] )
  OR last_modifier=21321
LIMIT 5000;

The explain analyze now is:

 Limit  (cost=92.58..9442.38 rows=2478 width=8) (actual time=0.071..0.074 rows=1 loops=1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.01..0.02 rows=1 width=0) (actual time=0.010..0.011 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
   ->  Bitmap Heap Scan on publication  (cost=92.56..9442.36 rows=2478 width=8) (actual time=0.069..0.070 rows=1 loops=1)
         Recheck Cond: ((mtid = ANY (($1)::bigint[])) OR (last_modifier = 21321))
         Heap Blocks: exact=1
         ->  BitmapOr  (cost=92.56..92.56 rows=2478 width=0) (actual time=0.060..0.060 rows=0 loops=1)
               ->  Bitmap Index Scan on publication_pkey  (cost=0.00..44.38 rows=10 width=0) (actual time=0.046..0.046 rows=1 loops=1)
                     Index Cond: (mtid = ANY (($1)::bigint[]))
               ->  Bitmap Index Scan on publication_last_modifier_btree  (cost=0.00..46.94 rows=2468 width=0) (actual time=0.011..0.011 rows=0 loops=1)
                     Index Cond: (last_modifier = 21321)
 Planning time: 0.704 ms
 Execution time: 0.153 ms

It seems we can create a simple parser that finds and rewrites all subselects this way, and add it to a hibernate hook to manipulate the native query.


Error while creating Extended Events on 2008 SQL Server

You can use a logon trigger to record the event info.

USE yourDatabase;     -- select name from sys.databases;

--IF (OBJECT_ID('LogonInfo') IS NOT NULL)   -- select name from sys.tables where name = 'LogonInfo'
--  DROP TABLE LogonInfo;

CREATE TABLE LogonInfo
(
    LogonTime   DATETIME,
    LoginName   VARCHAR(50),
    ClientHost  VARCHAR(50),
    LoginType   VARCHAR(50)
);

--  SELECT * FROM yourDatabase..LogonInfo ORDER BY LogonTime DESC
--  SELECT DISTINCT LoginName FROM yourDatabase..LogonInfo ORDER BY LoginName

USE [master];

--IF EXISTS (SELECT * FROM sys.server_triggers WHERE [type] = 'TR' AND [name] = 'LogonInfoTrigger')
--  DROP TRIGGER LogonInfoTrigger ON ALL SERVER;

CREATE TRIGGER LogonInfoTrigger
ON ALL SERVER WITH EXECUTE AS '<serviceAccount>'   -- select name from sys.server_principals;
FOR LOGON
AS
BEGIN
    -- Check if the log table exists before trying to do the insert
    -- Otherwise the trigger will error and not allow anyone to log in
    IF (OBJECT_ID('yourDatabase..LogonInfo') IS NOT NULL)
    BEGIN
        DECLARE
            @LogonTriggerData xml,
            @EventTime datetime,
            @LoginName varchar(50),
            @HostName varchar(50),
            @LoginType varchar(50);

        SET @LogonTriggerData = eventdata();

        SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');
        SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)');
        SET @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)');
        SET @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)');

        -- use the if statment if you want to ignore certain accounts
        -- remove the if statement and just run the insert statement if you want to capture all accounts
        IF @LoginName NOT IN ('yourSafeAccount1','yourSafeAccount2')
        BEGIN
            INSERT INTO yourDatabase..LogonInfo (LogonTime, LoginName, ClientHost, LoginType)
            VALUES (@EventTime, @LoginName, @HostName, @LoginType);
        END;
    END;
END;


Store different rows type in single table

You have it the other way round.

Put EventId and EventName on Event table and reference EventId in other tables. In other words,

Add EventId column in Transition, Position and Error tables.
Remove time_stamp, transition_id, position_id, and error_id from the Event table.


Why is this MySQL SELECT performing so slowly despite indexing?

EDIT: Since the first try with a subselect lead into doom, it might be necessary to split the table into a hot range and a "cold" range. That solution only could help if you have some criteria for id_c and id_b, which shows if this id_c will be read often or rarely, for example the age of this. But even deleting from such a big table is not so easy, it would need an optimize which takes ages. It would need some clever ideas to get as little as possible reads which "overlap" between both tables, but since you want to read all rows with id_b from different id_c, there will be cases where you need two queries and adding afterwards.

As already stated in the comments, look into key_reads and key_reads_request with SHOW VARIABLES LIKE 'KEY%';. Check if key_buffer_size is big enough (https://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html). You could arrange two different key buffers, one only for that specific table (https://dev.mysql.com/doc/refman/5.0/en/multiple-key-caches.html), one for the rest.

The suggestion with an index id_b, id_c should help a lot, since your query can then be processed completely in index, not from the (not bigger) "real table".

Try doing an analyze table A in low-traffic-times.

It really would help to split the table into many sections, especially for OPTIMIZE TABLE and ANALYZE TABLE, but your kind of query makes that really hard.

Doomed original solution:
I think you create many N x N - block of rows, where N is the number of rows in tableA with identical id_b values, since the join has to combine the id_b - blocks. So when you have many rows for some id_b, time squares up with that number.

It might help to use subselect in that case.

EDIT: EDIT undone.

SELECT COUNT(*), OtherA.id_c
FROM A OtherA where OtherA.id_b in (
    select DISTINCT id_b from A where A.id_c = [MY_SPECIFIC_C_ID]
)
GROUP BY OtherA.id_c

So the id_b can be selected in one scan and then can be sent through the index to find the right rows.

EDIT:
The Explain inserted by the Questioner shows that this subselect is a very bad idea in that case, no wonder it takes forever:

EXPLAIN on the suggested query looks like this:

row1
id--------------1
select_type-----PRIMARY
table-----------OtherA
type------------index
possible_keys---NULL
key-------------Uniqueness
key_len---------10
ref-------------NULL
rows------------496167246
Extra-----------Using where; Using index
row2
id--------------2
select_type-----DEPENDENT SUBQUERY
table-----------A
type------------reg
possible_keys---Uniqueness, id_b
key-------------Uniqueness
key_len---------10
ref-------------const,func
rows------------1
Extra-----------Using where; Using index; Using temporary


Oracle DB : Export only table data from from all tables of given schemas

Use Datapump (expdp/impdp):
expdp username/password directory=DATAPUMP_DIR schemas=S1,S2,S3 content=DATA_ONLY dumpfile=schema_dump.dmp logfile=export.log

impdp username/password directory=DATAPUMP_DIR dumpfile=schema_dump.dmp logfile=import.log

See here for an excellent reference: https://oracle-base.com/articles/10g/oracle-data-pump-10g
Note: If you export the metadata too (lose the "content=DATA_ONLY" option) then you can have the import process truncate your target tables before reloading them, by specifying the "TABLE_EXISTS_ACTION=TRUNCATE" option.


Sounds like the file you downloaded is actually not an XML file yet. It is actually a compressed XML file, and was probably compressed using the GZip algorithm.

To see your data you will have to decompress it:

rename the file to an .xml.gz extension (if it doesn't have one already)
use any decompression program to decompress your XML data (7zip is good/free)
open the resulting XML file in your favourite text editor!


The tarballs are at the bottom of the list of downloads after you select "Linux - Generic" from the operating system drop-down... not under "Red Hat Enterprise Linux."

Assumuming you are 64-bit, the one you're looking for is called "Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive."


You're getting two rows because your GROUP BY forces two rows of output, and your case statements run on each.

By putting the case statements inside the SUMs and removing the GROUP BY, you can return a single row.

I think below should work:

SELECT
SUM(
    case
    when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 1
    Then [value]/([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
    end
) As Q1
,SUM(
    case
    when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 2
    Then [value]/([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
    end
) As Q2
FROM Test


inet_client_addr in PostgreSQL don't return IP

You could abuse the application_name setting to pass the HTTP client IP from PHP to Postgres.

In PHP, once connected:

<? pg_query("SET application_name TO '$_SERVER[REMOTE_ADDR]'"); ?>

Or set it right at connection time with pg_connect which would normally support it in the conninfo string (see Connection Strings in the manual)

In your trigger, use   current_setting('application_name') to retrieve this information,
instead of inet_client_addr() which doesn't do what you want, as already answered.

There should also be a check in the trigger that this value is what you expect before storing it, since the trigger may be fired in any session, not just the ones initiated by PHP.


Getting "unknown variable postgresqlini" when updating PostgreSQL

Same issue here while installing postgresql-12.4-1-windows-x64.exe

A data directory is registered in ***unknown variable postgresqlini*** but is neither empty or a recognized data directory

After deleting the folder: c:\Program Files\PostgreSQL\12\, the installation went through.


In Ubuntu you don't start PostgreSQL with pg_ctl. You can, but don't. Instead, use this

sudo service postgresql start

su -c will only work if you enable the root account. Don't do that. Instead, use sudo.

See my answer here for how to get up and running on Ubuntu


Subquery to return multiple columns or a JSON object

Don't make life hard for yourself, use a lateral join:

SELECT r.*,
       b.status, b.start_time, b.end_time
FROM repos AS r
   LEFT JOIN LATERAL
      (SELECT status, start_time, end_time
       FROM builds
       WHERE repo = r.id
         AND branch = 'master'
       ORDER BY id DESC
       LIMIT 1
      ) AS b ON TRUE
WHERE id = ANY($1);


How do I fix an Orphaned User in Log Shipped version of a SQL Server 2008 database

If you are running sp_change_users_login 'Report' to get that list of orphaned users it will return the SID expected.

Just take that SID and create your login with the same SID:

CREATE LOGIN [mylogin] WITH PASSWORD='', SID = <SID for orphaned user>

This should allow your login to be mapped to that user in the database and allow you to access the database.

If you want to copy a login from production to your development box you can query sys.syslogins to get the SID, or just use sp_help_revlogin procedure from KB918992.


mysql 8 ndbcluster distributed privileges

I had same issue on 8.0.21 NDB Cluster. I was looking at the error log and trying to figure out what is wrong, and I saw this
NDB: Query 'CREATE USER IF NOT EXISTS 'admin'@'localhost'' failed, error: 1819: Your password does not satisfy the current policy requirements
NDB: Query 'REVOKE ALL ON *.* FROM 'admin'@'localhost'' failed, error: 1269: Can't revoke all privileges for one or more of the requested users
NDB: Query 'ALTER USER 'admin'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0  MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0' failed, error: 1819: Your password does not satisfy the current policy requirements
NDB: Query 'ALTER USER 'admin'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$Dzd+M c5Cnthnjcc^hEvdg6M.d3j9TWVcO6ze/6DBrNnW3SB/Qh7wn19XGI7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT' failed, error: 1396: Operation ALTER USER failed for 'admin'@'localhost'

When I first create user on SQL Node 1 and grant NDB_STORED_USER to new user the second node has this log. So basically when new user created on one of the node the other nodes are trying to create it with different query. As you can see the log the second node tries to create admin@localhost user and then it tries to set password using ALTER USER. And due to validate_password policy plugin the second node cannot create user without password.
So I have disabled the validate_password plugin and created user again. After disabling the validate_password plugin the user created without any error. And the password privileges have been set on second node.


Constraint - one boolean row is true, all other rows false

Variant 1
Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it:
CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
 , standard bool DEFAULT true
 , CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint
 , CONSTRAINT standard_only_1_true UNIQUE (standard)
);

DEFAULT is an optional reminder that the first row entered should become the default. It's not enforcing anything. While you cannot set more than one row to standard = true, you can still set all rows NULL. There is no clean way to prevent this with only constraints in a single table. CHECK constraints do not consider other rows (without dirty tricks).
Related:

Restrict two specific column values from existing at the same time

Add constraint to make column unique per group of rows

To update:
BEGIN;
UPDATE taxrate SET standard = NULL WHERE standard;
UPDATE taxrate SET standard = TRUE WHERE taxrate = 2;
COMMIT;

To allow a command like (where the constraint is only satisfied at the end of the statement):
WITH kingdead AS (
   UPDATE taxrate
   SET standard = NULL
   WHERE standard
   )
UPDATE taxrate
SET standard = TRUE
WHERE taxrate = 1;

.. the UNIQUE constraint would have to be DEFERRABLE. See:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

dbfiddle here
Variant 2
Have a second table with a single row like:
Create this as superuser:
CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
);

CREATE TABLE taxrate_standard (
   taxrate int PRIMARY KEY REFERENCES taxrate
);

CREATE UNIQUE INDEX taxrate_standard_singleton ON taxrate_standard ((true));  -- singleton

REVOKE DELETE ON TABLE taxrate_standard FROM public;  -- can't delete

INSERT INTO taxrate (taxrate) VALUES (42);
INSERT INTO taxrate_standard (taxrate) VALUES (42);

Now there is always a single row pointing to the standard (in this simple case also representing the standard rate directly). Only a superuser could break it. You might disallow that, too, with a trigger BEFORE DELETE.
dbfiddle here
Related:

Constraint to enforce "at least one" or "exactly one" in a database

You might add a VIEW to see the same as in variant 1:
CREATE VIEW taxrate_combined AS
SELECT t.*, (ts.taxrate = t.taxrate) AS standard
FROM   taxrate t
LEFT   JOIN taxrate_standard ts USING (taxrate);

In queries where all you want is the standard rate, use (only) taxrate_standard.taxrate directly.

You later added:

There is a FK between products.tax_rate_id and tax_rate.id

A poor man's implementation of variant 2 would be to just add a row to products (or any similar table) pointing to the standard tax rate; a dummy product you might call "Standard tax rate" - if your setup allows it.
The FK constraints enforces referential integrity. To complete it, enforce tax_rate_id IS NOT NULL for the row (if that's not the case for the column in general). And disallow its deletion. Both could be put into triggers. No extra table, but less elegant and not as reliable.


Data scrambling for the development data

If you produce the new SSNs with a function like newSSN = makeSSN(hash(realSSN+salt)), where hash() is a known good hash function[1], salt is a long randomly generated string, and makeSSN() takes some/all of the hash function's output to make a nine-digit number output[2]. This way you get the same new value where-ever you have the same input SSN without needing to produce a mapping table to keep values consistent. To make sure that a real SSN can't be mapped to one in the resulting test data by using the same function[3] don't store the salt value between invocations.

Of course the "there are going to be other ways to identify what real person maps to which person in your munged test data-set" issue is something you should be aware of. For truly sensitive data it is far easier to be secure if you generate test data procedurally instead of by trying to depersonalise real live data.

[1] SHA2, SHA3, ...

[2] for example you could take the first or lowest 29 bits, or use CRC32 with output to decimal and strip off one character as it can produce 10

[3] though there are going to be other ways to identify what real person maps to which person in your munged test data-set


How to sort data using 2 conditions and keep the first set of data always at the top?

Use the ISNULL() function

SELECT * FROM mytable
ORDER BY ISNULL(status) DESC,date;

or

SELECT * FROM mytable
ORDER BY 1-ISNULL(status),date;

For pagination, you can reference id

SELECT B.* FROM
(
    SELECT id FROM mytable
    ORDER BY ISNULL(status) DESC,date
    LIMIT xxx OFFSET yyy
) A INNER JOIN mytable B USING (id)
ORDER BY ISNULL(B.date) DESC,B.date;


Error using sp_trace_create with parameters

Martin Smith has answered https://dba.stackexchange.com/a/24728/8783

This will work :

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @someinteger = @new_trace_id OUTPUT,
  @someinteger = 0,
  @someinteger = N'C:\trace\TestTrace.trc';

  -- get trace status
  SELECT * FROM ::fn_trace_getinfo(NULL)
  -- stop trace
  EXEC sp_trace_setstatus @traceid = 1 -- CHANGE THIS AS PER YOUR TRACE id
                    , @status = 0
  go
  -- delete trace
  EXEC sp_trace_setstatus @traceid = 1  -- CHANGE THIS AS PER YOUR TRACE id
                    , @status = 2


Joining same table vs Joining different table in Mysql?

As you suspect, a JOIN does not care whether it is the same table or another table.  (Of course, you will have to use aliases to distinguish them.)

Would you care to show us some SQL; the question seems like a rather odd choice.


Index Maintenance - Reorganize After BIG SQL Server Shrink

If you have to shrink your DB ola's script for reorganizing is probably the best approach for handling the fragmentation.

please note, this is a costly operation- plan it to run on maintenance hours if possible and make sure that if you're in a full recovery model you have enough space on your disk that holds the log file and that you're backing up transaction log files properly.


Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

Don't know if this is the best way. I first did a select to find out if a stat is double digit and assign it a 1 if it is. Summed all those up to find out total number of double digits per game. From there just sum up all the doubles and triples. Seems to work

select a.player_id,
a.team,
sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles,
sum(case when a.doubles = 3 then 1 else 0 end) as tripleDoubles
from
(select *,
(case when points > 9 then 1 else 0 end) +
(case when rebounds > 9 then 1 else 0 end) +
(case when assists > 9 then 1 else 0 end) +
(case when steals > 9 then 1 else 0 end) +
(case when blocks > 9 then 1 else 0  end) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team


Choosing values from multiple columns based on latest value of one column

EVERY Column in a SELECT has to be in the GROUP BY or use a aggregator function
SELECT
    c.userName,
    MAX(c.DateTime),
    c.bloodPressure,
    c.temperature
FROM CheckupData c
GROUP BY
    c.userName,c.bloodPressure, c.temperature

OR
SELECT
    c.userName,
    MAX(c.DateTime),
    AVG(c.bloodPressure),
    AVG(c.temperature)
FROM CheckupData c
GROUP BY
    c.userName

As we don't know nothing about your data and desired result. You have to choose what values you need.
For big tables you can use

CREATE TABLE CheckupData
    ([userName] varchar(5), [DateTime] varchar(21), [bloodPressure] int, [temperature] int, [heartBeat] int, [hatSize] int)
;

INSERT INTO CheckupData
    ([userName], [DateTime], [bloodPressure], [temperature], [heartBeat], [hatSize])
VALUES
    ('Curly', '"2021-01-19 18:24:53"', 121, 98, 60, 7.25),
    ('Larry', '"2021-01-14 18:24:53"', 125, 99, 80, 7.5),
    ('Mo', '"2021-01-13 18:24:53"', 123, 100, 70, 7.75),
    ('Curly', '"2021-01-18 18:24:53"', 120, 97, 50, 8),
    ('Larry', '"2021-01-13 18:24:53"', 119, 99, 75, 7.15),
    ('Larry', '"2021-01-15 18:24:53"', 115, 98, 85, 7.37),
    ('Mo', '"2021-01-12 18:24:53"', 110, 102, 100, 8.01),
    ('Mo', '"2021-01-20 18:24:53"', 130, 99, 110, 6.97),
    ('Larry', '"2021-01-18 18:24:53"', 127, 98, 72, 7.76),
    ('Curly', '"2021-01-17 18:24:53"', 126, 97, 82, 8.0)
;

GO

SELECT
cd.[userName], cd.[DateTime], cd.[bloodPressure], cd.[temperature], cd.[heartBeat], cd.[hatSize]
FROM CheckupData cd INNER JOIN
(SELECT MAX([DateTime]) lastdate,[userName] FROM CheckupData GROUP BY [userName]) cd1
ON cd.[userName]  = cd1.[userName] AND cd.[DateTime] = cd1.lastdate
GO

userName | DateTime              | bloodPressure | temperature | heartBeat | hatSize
:------- | :-------------------- | ------------: | ----------: | --------: | ------:
Mo       | "2021-01-20 18:24:53" |           130 |          99 |       110 |       6
Larry    | "2021-01-18 18:24:53" |           127 |          98 |        72 |       7
Curly    | "2021-01-19 18:24:53" |           121 |          98 |        60 |       7

db<>fiddle here


How do I fix a login timout in SQL Server 2005 when using a linked server and a stored procedure called from a SQL Agent Job?

SQL Agent will use Shared Memory to connect to the local server.  The Instances will attempt to use named pipes to talk to each other.  You can adjust this by creating an alias in the SQL Server Client Network Utility (cliconfg.exe) and setting the alias to use TCP.


I would do something like this:

period_start_on date
period_duration interval
position        int -- position of this row in the year,
                    -- e.g. 1 for first Monday of Oct,
                    -- 2 for 2nd Monday, etc.
values ...
primary key(period_start_on, period_duration)
check(position >= 1 and position < 40)

Then if you want December only, you use WHERE period_duration = '1d' and period_start_on >= '2012-12-01' AND period_start_on < '2013-01-01'. If you want to compare two years, you JOIN using position:

SELECT y1.*, y2.*
FROM   the_table y1
  JOIN the_table y2 USING (position)
WHERE y1.period_start_on >= '2011-01-01' AND y1.period_start_on < '2012-01-01'
  AND y2.period_start_on >= '2012-01-01' AND y2.period_start_on < '2013-01-01'


How to retrieve all the rows where primary key value found in the other column in the row in MySql

You can (easily) do one-level relations, and then UNION the results. That's what would take to get rows related to temp_no = '2017-06' (and with a query which is SQL standard):

SELECT
    *
FROM
(
    -- Direct search
    SELECT
        *
    FROM
        t AS t0
    WHERE
        t0.temp_no = '2017-06'

    UNION

    -- One level related
    SELECT
        t1.*
    FROM
        t AS t0
        JOIN t AS t1 ON t1.sno = t0.p_sno
    WHERE
        t0.temp_no = '2017-06'
) AS u
ORDER BY
    sno ;

Giving:

 sno | temp_no | first_party | second_party | p_sno
 --: | :------ | :---------- | :----------- | ----:
   5 | 2017-05 | Akhtar Ali  | Amir Khan    |  null
   6 | 2017-06 | Akhtar Ali  | Amir Khan    |     5

dbfiddle here

NOTE: I've replaced your p_sno = 0 values by nulls, which seems to me that better represents the meaning of this column.

You seem to want to find all descendants of a certain row, using a parent-child relationship between p_sno and sno. This is an indefinetly-deep multi-level hierarychy. If you use a database that supports recursive CTEs (such as MariaDB instead of MySQL) you can use the recursive CTE explained in: Check CTE to get all children (descendants) of a parent.

If your question is restricted to MySQL, you can use the tecnique described by
How to create a MySQL hierarchical recursive query, that would make you produce the following query for your temp_no = '2017-08' use case:

 SELECT
     t_sorted.*
 FROM
     (SELECT
         *
     FROM
         t
     ORDER BY
         p_sno, sno
     ) AS t_sorted,
     (SELECT
         @pv :=
         (SELECT
             sno
         FROM
             t
         WHERE
             temp_no = '2017-08'
         )
     ) AS init
 WHERE
     -- for first row
     t_sorted.sno = @pv
     -- recursive query
     OR (find_in_set(p_sno, @pv) > 0 AND @pv := concat(@pv, ',', sno)) ;

 sno | temp_no | first_party | second_party | p_sno
 --: | :------ | :---------- | :----------- | ----:
   8 | 2017-08 | Amir Khan   | Saleem       |  null
   9 | 2017-09 | Amir Khan   | Saleem       |     8
  10 | 2017-10 | Amir Khan   | Saleem       |     9

dbfiddle here


Table Joined To Itself Appears Inefficient

With two rows per table any method will be good.

Since you are OK with hard-coding the value 1 for the main RouteID I'd do it like this:

SELECT
    CASE WHEN Main.WaveValue > Other.WaveValue THEN 1 ELSE 0 END AS IsMainWaveGreater
    ,CASE WHEN Main.RollValue > Other.RollValue THEN 1 ELSE 0 END AS IsMainRollGreater
FROM
    Shipping AS Main
    CROSS APPLY
    (
        SELECT
            Other.WaveValue
            ,Other.RollValue
        FROM Shipping AS Other
        WHERE Other.RouteID <> 1
    ) AS Other
WHERE Main.RouteID = 1
;

It assumes that there are only two rows in the table, if there are more "other" routes, then specify their specific RouteID in the CROSS APPLY instead of <> 1 to get only one row that's needed.

Here is SQL Fiddle.


Which one is better performance and (approach): store as rows or as a varchar column

Option B is the worst choice: Why
Because by keeping values in CSV format in a single column, you are violating first NF http://en.wikipedia.org/wiki/First_normal_form.

Most critical drawback of violation of first NF is complex join conditions. You have to apply join using LIKE operators and worst wildcard options or using other string functions.

Secondly RDBMS will not be able to create correct data distribution statistics and ultimately query optimizer can't select efficient execution plan.


Is this table in my ERD redundant? (Image)

The purpose of the Author's_University table seems to be to allow one author on an article to be associated with more than one university. There are a couple of potential reasons why that would be helpful:

An author might claim multiple universities because they are a professor and/or are associated with more than one university when the article was published.
Your application might want to track an author's alma mater(s).

I know this isn't part of your question but I would avoid putting an apostrophe in a table name. Removing the apostrophe would change Author's_University to Authors_University or just Author_University (my preference). That will most likely make the table easier to work with because punctuation isn't your friend when naming.

Also, my personal preference is PascalCase over Snake_Case. That is merely personal preference though, and might clash with the convention in MySQL.


The "Data Model Resource Book", Volume 1, has complete and validated structures for all that. I suggest you grab a coffee - it also discusses these details in great length.

You have a problem - User and UserRole - a User may be in multiple roles and roles should be time limited so they can change over time without invalidating your audit trail. What you do if a Vendor turns into a Buyer? You fall into the typical trap of assuming a User (entity / table) contains a role - it should not. A user is an ID - much like you passport does not contain your job.

Roles are attached to users (UserRole) table, but a user does not contain a role.

Same with AddressType - what if I want to use the same address for both invoicing and shipping? Types should be tags added. Or in the Role you have a pointer to a specific address (ShippingAddressId etc.).

You code the address very detailed - that is nice, but also totally bad if you ever run into addresses that are non-standard. There is a good sense to have fields like postal code, city, country - but even state is something irrelevant (never give it on an address) for shipping but what is worse are all the address fields. Street? What about countries not using this? Not having proper house  numbers? Where your shipping address is 15 lines long? Those DO exist. Read http://online.wsj.com/news/articles/SB10001424052702304870304577489094121477570 for a reference. There is a good point to have a free text field for stuff like that - as well as additional routing details you are not aware of at the moment.

Phone numbers- same thing. Please. ContactItem is a good table - can be email, phone, etc.- Allows people to have multiple phone numbers.

PinCode? You want to store a readable pin code in there? Programming for beginners: NEVER EVER STORE A PASSWORD OR PIN CODE IN A DATABASE. Store a Hash, salted, by standard security approach. Something a hacker can not easily use to pretend to be the user. Storing a pin code was legally gross neglect 20 years ago. It still is today.

So, looks like a non-enterprise model made by someone now aware of the complexities of the real world. I can really only recommend a copy of the Data Model Resource Book for a good description of all those cases.


execute [SSISDB].[catalog].[create_execution] with 'execute as"

figured out the issue. didn't know the ssisdb procedures already were doing 'execute as' and didn't know you needed to use an AD account for impersonation to work.


Encrypting an existing tablespace became possible in 12.2.
11g documentation:
Creating an Encrypted Tablespace

You cannot encrypt an existing tablespace.

12.1 documentation:
About Creating Encrypted Tablespaces

You cannot change an existing tablespace to make it encrypted.


Error during mysqldump on performance_schema.query_cache_info

Do not dump any tables of performance_schema, information_schema or mysql.  The first two are not 'real' tables, and they cannot be reloaded anyway.

95% of Production servers are not helped by, or even hurt by, turning on the Query cache.  Please elaborate on why you are even attempting to use the QC.


pfile - client parameter file - starting an instance remotely

The procedure is described in the Remote Startup of a Database Instance part of admin guide for BS2000/OSD (I can't find this part in the Linux docs...).

Essentially what you do is:

Create an Oracle password file for your database if you haven't already done so
Add a static registration for your instance in its listener
Set the instance parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE
Restart the listener and the database.

Once you've done that and granted sysdba to a user, you can connect to the instance using sqlplus directly from another machine

c:\foobar> sqlplus /nolog
...
SQL> connect mat/mat@SOMETHING_REMOTE as sysdba
...
SQL> startup pfile='c:\foobar\init.ora'

That last command reads the pfile from the machine where sqlplus is running, not from a directory on the server.

But I'd generally say you probably shouldn't be doing this. Have your database all use spfiles on the server. Otherwise you never know how they were started.


Should we still be using QUOTENAME to protect from injection attacks?

Yes, things haven't changed much in this area, you should be using quotename for any SQL server object names that are used in dynamic SQL (especially if they are supplied externally to your code). As well as SQL injection mitigation this also means your code will work correctly for non standard identifier names.

The function is only appropriate for object names (e.g. table, column, database names) though.

You should try and parameterise everything else and use sp_executesql, passing in parameters rather than concatenating those into the query string.

The definitive article on this topic is still The Curse and Blessings of Dynamic SQL

Edit. Now you have supplied the code I see it is passing the second parameter of ' to add the outer quotes and escape any single quotes by doubling them up before injecting them into the string. This is not a good use of quotename. It will fail (return null) if the string is greater than 128 characters.

Additionally It may still leave SQL injection possibilities if the string contains U+02BC instead of the standard apostrophe and then the string is assigned to a varchar after the sanitation (where it can silently get converted to a regular apostrophe )

The correct way to do this is to leave the query parameterised. And then pass the @parm1 value in to sys.sp_executesql

DECLARE @Sql NVARCHAR(MAX);

SET @Sql = '
SELECT project      [Project],
       project_desc [Description],
       customer     [Customer],
       cpnyid       [Company]
FROM   PJPROJ (nolock)
WHERE  project LIKE @parm1
';

EXEC sys.sp_executesql
  @Sql,
  N'@parm1 varchar(100)',
  @parm1 = 'foobar%';


Temporarily Archive Databases on SQL Server 2008 R2

To answer your questions first:

  What are the potential pitfalls with temporarily renaming a database?

At this scale, it'd be bothersome and just potentially messy to be renaming this many databases multiple times in the case some are needed. It may be a pain cutting off all connections when re-naming and could confuse other users as well.

  Does MSSQL remove any logins or authentication information when a
  database is renamed and then renamed back?

Nope! Not an issue.

  Does anyone have a more standardized recommendation for this process,
  or a direction to point me to get me researching further?

In this case, I'd probably just take the databases OFFLINE temporarily to see if they are needed.

You can do this via the GUI by right-clicking a database, Task -> Take Offline, or via TSQL:

ALTER DATABASE [dbname] SET OFFLINE;

When a database is offline, all connections are closed and no new connections can be made to it. It's quick to bring it back online if required:

ALTER DATABASE [dbname] SET ONLINE;

Plus, when the database is offline it's using less resources than an online database, so it may be better for performance in the mean time as well.


Why is a temp table a more efficient solution to the Halloween Problem than an eager spool?

This is what I call Manual Halloween Protection.

You can find an example of it being used with an update statement in my article Optimizing Update Queries. One has to be a bit careful to preserve the same semantics, for example by locking the target table against all concurrent modifications while the separate queries execute, if that is relevant in your scenario.

  Why is the plan with the temp table more efficient? Isn't an eager spool mostly just an internal temp table anyway?

A spool has some of the characteristics of a temporary table, but the two are not exact equivalents. In particular, a spool is essentially a row-by-row unordered insert to a b-tree structure. It does benefit from locking and logging optimizations, but does not support bulk load optimizations.

Consequently, one can often get better performance by splitting the query in a natural way: Bulk loading the new rows into a temporary table or variable, then performing an optimized insert (without explicit Halloween Protection) from the temporary object.

Making this separation also allows you extra freedom to tune the read and write portions of the original statement separately.

As a side note, it is interesting to think about how the Halloween Problem might be addressed using row versions. Perhaps a future version of SQL Server will provide that feature in suitable circumstances.

As Michael Kutz alluded to in a comment, you could also explore the possibility of exploiting the hole-filling optimization to avoid explicit HP. One way to achieve this for the demo is to create a unique index (clustered if you like) on the ID column of A_HEAP_OF_MOSTLY_NEW_ROWS.

CREATE UNIQUE INDEX i ON dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (ID);

With that guarantee in place the optimizer can use hole-filling and rowset sharing:

MERGE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (SERIALIZABLE) AS HICETY
USING dbo.A_HEAP_OF_MOSTLY_NEW_ROWS AS AHOMNR
    ON AHOMNR.ID = HICETY.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID) VALUES (AHOMNR.ID);

While interesting, you will still be able to achieve better performance in many cases by employing carefully-implemented Manual Halloween Protection.


Make sure the file group is actually empty by looking at

SELECT *
FROM sys.allocation_units AS AU
JOIN sys.filegroups AS F
ON AU.data_space_id = F.data_space_id
ORDER BY F.name;

If it is empty:

1) Try one of the DBCC CHECKDB repair options (REPAIR_FAST or REPAIR_REBUILD). If you are sure that there is no corruption of any type in any other file it should even be safe to do a "REPAIR_ALLOW_DATA_LOSS" but make sure you have a good backup when using that last one. (It is a very good idea to have a backup anyway). Save all the output you get for later analysis. If any repair was successful empty the files as before.

2) If that did not help you should be able to just drop the files: http://technet.microsoft.com/en-us/library/ms175122.aspx
However, that might leave the database in some kind of "funky" state (see above link), so I would try 1) first.


Create a view showing total minutes per customer per month and change from previous month

Seems like you're right on the tip of the answer. Just group by the customer, the year, and then the month.

USE Masscomm_XT;

WITH C0 AS (
  SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate
  FROM dbo.reptview_LDUsageTrend
)

SELECT C0.CustomerName ,C0.PostingMonth ,C0.PostingYear, SUM (Minutes) AS MinuteTotal
FROM C0
GROUP BY C0.CustomerName, C0.PostingYear, C0.PostingMonth
;


What does this error mean in IBM Netezza Administrator? - ERROR: Permission denied on "_VT_DISK_PARTITION"

I asked someone in my department, and I guess the tool always throws this error when you do not have administrator permissions.  That Netezza view (_VT_DISK_PARTITION) is not accessible with non-admin security.  It's necessary to show some of the features in the default view/tab ("System") of the tool.

When clicking on the "Database" view/tab, and navigate to some database and table, it might take a bit to load the data.  But that view/tab should not throw any errors to make the tool usable.


Are idx_scan statistics reset automatically (default)?

Quote from the manual

  When recovery is performed at server start (e.g. after immediate shutdown, server crash, and point-in-time recovery), all statistics counters are reset

In other words, the statistics are not reset on a clean shutdown and restart.

So if your server never crashed, and you never issued a "shutdown immediate" (using `pg_ctl stop -m immediate) then the statistics show the cumulated values since the instance was created (for "global" statistics") or since the database was created (for database specific statistics).

If you want to detect if there are unused indexes, I would reset the statistics, then take snapshots of them in regular intervals (e.g. every day or every hour depending on your system) and then compare the values over time after e.g. a week of normal operation.

Note that you can't (shouldn't) remove unique indexes (e.g. a primary key index) that have never been used for reading!


More importantly, if ...

  some of the results have no entries

You need crosstab(text, text) - the variant with 2 input parameters instead of crosstab(text) you are using now to avoid incorrect results. Detailed explanation:

PostgreSQL Crosstab Query

So, assuming the respective values in the column stage are 'some_column_name_1' and 'some_column_name_2':

SELECT username
     , COALESCE(col1, 0) AS some_column_name_1  -- sets default for NULL
     , COALESCE(col2, 0) AS some_column_name_2
FROM   crosstab(
   'select username, stage, count from some_view order by 1,2'
  , $$VALUES ('some_column_name_1'), ('some_column_name_2')$$  -- !!!
   ) AS final_result (username text, col1 bigint, col2 bigint);

And COALESCE provides the default value for NULL that you asked for - like AMG commented.


Change column value status to be interrupted if no new record return after 10 mins

Depending on your response to my comment above this may be what you want. Or it may at least show you the principle of what you want.

UPDATE scadainfo_status ss
SET ss.alarm_status = 'NO UPDATE'
WHERE ss.item_id IN (WITH updates
                       AS ( SELECT so.item_id AS item_id,
                                   COUNT(*)
                                      OVER (PARTITION BY item_id
                                            ORDER BY date_time
                                            RANGE BETWEEN CURRENT ROW AND NUMTODSINTERVAL(10,'MINUTE')FOLLOWING)-1 AS later_records
                              FROM scadainfo_online so)
                       SELECT item_id
                         FROM scadainfo_online
                        WHERE later_records <1)

The 'IN' sub-query is counting records for the id that have occurred between this row and any that are in the following 10 minutes. A row (the current row) is returned if there are no further rows.
Sorry about the description of how it works; I find analytic queries tricky and explaining them even more so.


That one is just an FYI and not something that you should take action on. Some of the rows returned will be FYI-type things, some will be things that you should look into more, some will be things that you should take action on. Like I ignore the "addicted to strings" and "addicted to nulls" rows. Those are just FYIs. I take action on the missing indexes section, though not all of them, depends on the details.


How to configure SSL mongodb connection in yml file spring boot?

You can try a configuration like the one below;

server:
    ssl:
        key-store: classpath:keystore.jks
        key-store-password: secret
        key-password: another-secret
data:
     mongodb:
        authentication-database: admin
        database: <database>
        host: <host>
        password: <password>
        port: <port>
        username: <username>
        ssl-enabled: true


Change SELECT DISTINCT to UPDATE DISTINCT

To just update an arbitrary one from each distinct group you could use

WITH T
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY [Finance_Project_Number]
                                       ORDER BY (SELECT 0)) AS RN,
                [Processing_Result_Text],
                [Processing_Result]
         FROM   [InterfaceInfor].[dbo].[ProjectMaster]
         WHERE  NOT EXISTS (SELECT *
                            FROM   [IMS].[dbo].[THEOPTION]
                            WHERE  [InterfaceInfor].[dbo].[ProjectMaster].[Finance_Project_Number] = [IMS].[dbo].[THEOPTION].[NAME]))
UPDATE T
SET    [Processing_Result_Text] = 'UNIQUE',
       [Processing_Result] = 0
WHERE  RN = 1;

If you decide there is some criteria by which the row to be updated should be selected after all simply change the ORDER BY (SELECT 0) accordingly so that the desired target row is ordered first - e.g. ORDER BY DateInserted desc would update the latest one as ordered by a column called DateInserted if such a column exists.

This uses a common table expression (CTE) because it is not permitted to reference ranking functions such as ROW_NUMBER directly in the WHERE clause. It is allowed to update data via common table expressions in the same circumstances as for updatable views (basically the data being updated must be able to be mapped back straight forwardly to specific items in a single base table).

If you are not yet familiar with ranking functions you may well find SELECT-ing from the CTE first to be beneficial.

CREATE TABLE #TheOption(NAME VARCHAR(50));

CREATE TABLE #ProjectMaster
(
Finance_Project_Number VARCHAR(10) NOT NULL,
Processing_Result_Text VARCHAR(50) NULL,
Processing_Result INT NULL
);

INSERT INTO #ProjectMaster (Finance_Project_Number, Processing_Result_Text)
VALUES ('A00001', 'A'),
       ('A00001', 'B'),
       ('A00001', 'C'),
       ('B99999', 'D'),
       ('B99999', 'E'),
       ('C47474', 'F'),
       ('C47474', 'G');

INSERT INTO #TheOption (NAME) VALUES('C47474');

WITH T
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Finance_Project_Number
                                       ORDER BY (SELECT 0)) AS RN,
                Finance_Project_Number,
                Processing_Result_Text,
                Processing_Result
         FROM   #ProjectMaster pm
         WHERE  NOT EXISTS (SELECT *
                            FROM   #TheOption opt
                            WHERE  pm.Finance_Project_Number =opt.NAME))
SELECT *
FROM T
ORDER BY Finance_Project_Number, RN;

Example results are below

The C47474 rows are filtered out as they do exist in the other table so don't meet the NOT EXISTS, the remaining rows are grouped by Finance_Project_Number and assigned a sequential number within each group.

In this case the yellow rows would meet the RN = 1 condition and be updated. However there is no guarantee exactly how these numbers will be assigned within each group unless you use a ORDER BY clause on an expression guaranteed to be unique. Without this it could potentially change even between successive executions of the same statement.


In your example code, you are passing three categories of "things" into your dynamic SQL.

You pass @OrderDir, which is a keyword to signify ASC or DESC.
You pass @OrderBy, which is a column name (or potentially a set of
column names, but based on the way #1 is implemented, I assume you
expect a single column name.
You pass @PageNumber and @PageSize, which become literals in
the generated string.

Keywords

This is really straightforward--you just want to validate your input. You're spot-on that this is the right thing for this option. In this case, you are expecting either ASC or DESC, so you can either check that the user passes one of those values, or you can switch to a different parameter semantic, where you have a parameter that is a toggle switch. Declare your parameter as @SortAscending bit = 0, then within your stored procedure, translate the bit into either ASC or DESC.

Column names

Here, you should use the QUOTENAME function. Quotename will ensure that objects get properly [quoted], ensuring that if someone tries to pass in a "column" of "; TRUNCATE TABLE USERS", it will get treated as a column name, and not an arbitrary piece of injected code. This will fail, rather than truncating the USERS table:

SELECT [; TRUNCATE TABLE USERS]...
FROM...

 Literals & parameters

For @PageNumber and @PageSize, you should be using sp_executesql to pass parameters properly. Properly parameterizing your dynamic SQL allows you to not only pass values in, but also to get values back out.

In this example, @x and @y would be variables scoped to your stored procedures. They aren't available within your dynamic SQL, so you pass them into @a and @b, which are scoped to the dynamic SQL. This allows you to have properly typed values both inside and outside the dynamic SQL.

DECLARE @i int,
 @x int,
 @y int,
 @sql nvarchar(1000),
 @params nvarchar(1000);

SET @x = 10;
SET @y = 5;
SET @params = N'@i_out int OUT, @a int, @b int';
SET @sql    = N'SELECT @i_out = @a + @b';

EXEC sp_executesql @sql, @params, @i_out = @i OUT, @a = @x, @b = @y;
SELECT @i;

Even with varchar values, keeping the value as a variable prevents someone from arbitrarily passing code that gets executed. This example ensures that the user input gets SELECTed, and not arbitrarily executed:

DECLARE @UserInput varchar(100),
         @params nvarchar(1000) = N'@value varchar(100)',
         @sql nvarchar(1000)    = N'SELECT Value = @value';

SET @UserInput = '; TRUNCATE TABLE USERS;'
EXEC sp_executesql @sql, @params, @value = @UserInput;

My code

Here's my version of your stored procedure, with table definition & a few sample rows:

CREATE TABLE dbo.Persons
       (
        id INT,
        firstName VARCHAR(50),
        lastName VARCHAR(50)
       );
GO

INSERT INTO dbo.Persons(id, firstName,lastName)
VALUES (1,'George','Washington'),
       (2,'John','Adams'),
       (3,'Thomas','Jefferson'),
       (4,'James','Madison'),
       (5,'James','Monroe')

ALTER PROCEDURE dbo.GetPersons
       @pageNumber INT = 1,
       @pageSize INT = 20,
       @orderBy VARCHAR(50) = 'id',
       @orderDir VARCHAR(4) = 'desc'
AS
       SET NOCOUNT ON;

--validate inputs
IF NOT EXISTS ( SELECT   1 FROM     sys.columns
                WHERE    object_id = OBJECT_ID('dbo.Persons')
                AND name = @orderBy )
BEGIN
        RAISERROR('Order by column does not exist.', 16,1);
        RETURN;
END;

IF (@orderDir NOT IN ('ASC', 'DESC'))
BEGIN
        RAISERROR('Order direction is invalid. Must be ASC or DESC.', 16,1);
        RETURN;
END;

--Now do stuff
--sp_executesql takes in nvarchar as a datatype
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT id, firstName, lastName
FROM (
    SELECT id, firstName, LastName, ROW_NUMBER() OVER(ORDER BY '
           + QUOTENAME(@orderBy) + N' ' + @orderDir + N') AS rn
    FROM dbo.Persons
    ) t
WHERE rn > ( @pageNumber-1) * @pageSize
        AND rn <= @pageNumber * @pageSize
ORDER BY ' + QUOTENAME(@orderBy) + N' ' + @orderDir;

EXEC sys.sp_executesql @sql, N'@pageNumber int, @pageSize int',
                   @pageNumber = @pageNumber, @pageSize = @pageSize;

GO

You can see here, that the code is functional & gives you the proper ordering & pagination:

EXEC dbo.GetPersons @OrderBy = 'id', @orderDir = 'DESC';
EXEC dbo.GetPersons @OrderBy = 'id', @orderDir = 'ASC';
EXEC dbo.GetPersons @OrderBy = 'firstName';
EXEC dbo.GetPersons @OrderBy = 'lastName';
EXEC dbo.GetPersons @PageNumber = 2, @PageSize = 1, @OrderBy = 'lastName', @orderDir = 'ASC';

And also see how the input handling protects against someone trying to do strange stuff:

EXEC dbo.GetPersons @OrderBy = 'lastName', @orderDir = 'UP';
EXEC dbo.GetPersons @OrderBy = ';TRUNCATE TABLE Persons;';

 Additional Reading

sp_executesql example

Aaron Bertrand's Bad Habits to Kick : Using EXEC() instead of sp_executesql

Aaron Bertrand's kitchen sink procedure


Elijah. There's two separate questions here:
1. Is DTC supported with AlwaysOn Availability Groups?
You're using SQL Server 2012, and according to Microsoft's Documentation, that answer is no. I totally understand that you want to try it anyway, but keep in mind that you're now putting something into production that Microsoft simply will not support, AND you're using two separate niche features together (AGs and DTC). If anything whatsoever goes wrong, you're going to be in a world of hurt. This just isn't something I'd ever even think about trying in production.
Keep in mind that if your managers find out that you deployed something Microsoft specifically says in big letters, "YOU CAN'T DO THIS," and you have any kind of outage where you have to call Microsoft for support, you're going to have some ugly explaining to do.
Technically, DTC is supported starting with SQL Server 2016 SP2 and later, but it just means that you can pick which database loses data on failover, and the application has no idea data was permanently lost. That's not what a normal database administrator would call DTC support.
2. How should DTC be configured in a multi-node, multi-subnet cluster?
Read Allan Hirt's post on configuring DTC with multiple instances of SQL Server in a cluster, and make sure to read all of the links in the post as well.


I would suggest to handle your scenario using the application rather than using the DB. For example, if a member creates a message:

Check if the user in the message exists. Query the DB for this.
Based on the result from the query above: if the user exists, update
the appropriate row (DB update).
If the user does not  exist, insert the row (DB insert).

Note that in the above scenario, the application is making the logical decisions. If you want the DB to do this logical processing, then you can use an instead of insert trigger.