Failed installation of SQL 2012 on Windows Server Core

I figured out the issue after doing some more searching and trial & error.

The process I used for finding the issue:

Started with the CLI and ran the following command:

This will provide a list of all features that are enabled and disabled

Dism /online /get-features /format:table

Next you use the following command to enable needed features

Dism /online /enable-feature /featurename:<featurename>

Example:

Dism /online /enable-feature /featurename:MicrosoftWindowsPowerShell

ALL of the following features need to be enabled for the installation to be successful:

NetFx2-ServerCore
NetFx2-ServerCore-WOW64
NetFx3-ServerCore
NetFx3-ServerCore-WOW64
MicrosoftWindowsPowerShell
MicrosoftWindowsPowerShell-WOW64

When I posted this question, I had all, but “NetFx3-ServerCore-WOW64” enabled.  Further, my problem was an assumption on my part.  I assumed that these features would be installed along with SQL setup, this wasn’t the case from the CORE installation, you need to make sure these features are installed/enabled BEFORE the SQL 2012 installation begins.  When I performed the GUI-based installations, I didn’t have worry about enabling the aforementioned features.

Moral of the story…DON’T ASSUME ANYTHING!  ALWAYS check prerequisites if you’re having installation problems for any software/hardware.


"When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained"?

A connection from the pool will have the isolation level set by the last client to use that connection. Yes, it really is that scary.

The long and the short of it is that if you change the isolation level of a connection you must explicitly set it back to READ COMMITTED before closing. Better is to explicitly declare your required isolation level at the start of any batch, to ensure your code isn't impacted by somebody else being sloppy, and return it to the default at the end.

This behaviour, while baffling, is apparently by-design.


How to save the design of an Access database -- not the content -- as text?

In This Blog Post, Adi Nuryanto gives a great process by which you can accomplish this via a "legacy operation", which I have reproduced below:

  1.Open MS Access and open your database. (For this sample the CarsDB.MDB sample database installed with ASPxperience was used)

  2.Press ALT+T+Y+D to open the Documentor. (This is a legacy menu option that still works as long as you know the shortcut)

  3.Click the Table tabs

  4.In the Documentor Table tab check the Cars and CarScheduling tables (see Figure 1)

  5.Click the Options button

  6.In the Include for Table group check Properties only

  7.In the Include for Fields group select the Names, Data Types, and Sizes radio button

  8.In the Include for Indexes group select the Nothing radio button (see Figure 2).

  9.Click OK to close the Print Table Definition dialog

  10.Click OK to generate the documentation

  11.When the documentation report is finished the Print Preview tab will be displayed. Click Text File in the Data section to export the
  report to a text file

  12.In the Export – Text File dialog (see Figure 3)

  13.Check open the destination file after the export operation is complete

  14.Click OK

  15.For the encoding choose Windows default

An alternative to the above keyboard shortcut:
    Database Tools | Analyze | Database Documenter


This schema would make sense to me:

tblCompany
(
  Company_ID  PK
  Company_Name
)

tblProduct
(
  Prod_No  PK
  Company_ID  FK_tblCompany_CompanyID
  Prod_Name
  Prod_Mark_No
  Prod_Catalogue
  Prod_Type
  Prod_Expiry_Date
  Prod_Verify_Date
  Prod_Comments
)

You could have separate tables for product type and product catalogue to get to 3NF but that's a decision for you to make depending on the amount of different values those columns could have and the way in which this data will be accessed (OLTP only or heavy reporting used here too?).


How to pass schema prefix to queries that don't contain it

Set the default schema for the user to the schema that your queries need to use.

ALTER USER [UserName] WITH DEFAULT_SCHEMA=[SchemaName]


Whether or not you are using SQLCMD mode, you can always avoid the need for a GO (which enforces batch separation) by wrapping the calls in an EXEC().  For example:

IF ($(EnableDBMail) = 1 )
BEGIN
  EXEC('sp_configure ''show advanced options'', 1;');
  EXEC('RECONFIGURE;');
  EXEC('sp_configure ''Database Mail XPs'', 1;');
  EXEC('RECONFIGURE;');
END;

This also works when creating objects that need to be the only CREATE in the batch, such as: stored procedures, functions, triggers, views, etc. The following is a single batch and can be wrapped in an IF or whatever else:

CREATE TABLE dbo.MyTable
(
  Col1 NVARCHAR(256) NOT NULL
);

EXEC('
CREATE PROCEDURE dbo.Proc1
(
   @Param1 INT
)
AS
SET NOCOUNT ON;

SELECT field
FROM   table;
');

EXEC('ALTER TABLE dbo.MyTable ADD Col2 DATETIME NULL;');


Is there a reason my CTE with a basic subquery in the second stage of the CTE is failing?

Let's pretend for a moment that CTE_RealAudit is a normal table...

You're querying it twice! Once to get the list of Minutes values, and to get all the other fields for those rows that have a Minutes value (oh, and surely it always will have, unless you have NULLs) in the list. So yes, that's going to take a lot longer than querying it once.

So that's why it's taking much longer. But I don't think you need to query it twice at all - have a good think about the logic you actually want, especially as it's not a normal table, but rather a complex subquery over a view.


Can I match two tables in postgresql, sum the data in the number column and create a new column?

As Lennart suggested, you should start with working example, which would help us better understood what the problem is, and what you need to be done. And please use english names in columns and table names.
(I wanted to response in comment, but apparently I have too less reputation to do that).
Basing on you description, though, it seems like simple join between these two tables, like:
select *
from depoyaeklenecek d1
inner join depoadetleri d2 on d2.kapino = d1.kapino

Going further, you should specify what to you understand by

collecting the data in the column "adet" - do you want to aggregate (sum?) them somehow? Or just fetch them to be used later?
refreshing my table in a way that I can write the number - do you mean that you want to update values in one table by values from the other one (per proper kapino number?) Or just want to sum these values and put them (where?)


pl/pgsql: Dynamicly get a column name from a record

Using row_to_json function:

do $$
declare
  r json;
  i int;
begin
  for r in
    select row_to_json(t.*)
    from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)
  loop
    raise info '%', r;
    for i in 11..13 loop
      if r->>('y'||i) like '%2' then -- Condition here
        raise info 'Do something for %', r->>('y'||i);
      end if;
    end loop;
  end loop;
end $$;

INFO:  {"x":1,"y11":"a1","y12":"a2","y13":"a3"}
INFO:  Do something for a2
INFO:  {"x":2,"y11":"b1","y12":"b2","y13":"b3"}
INFO:  Do something for b2

Using array constructor:

do $$
declare
  r record;
  i int;
begin
  for r in
    select x, array[y11,y12,y13] as y
    from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)
  loop
    raise info '%', r;
    for i in 1..3 loop
      if r.y[i] like '%2' then -- Condition here
        raise info 'Do something for %', r.y[i];
      end if;
    end loop;
  end loop;
end $$;

INFO:  (1,"{a1,a2,a3}")
INFO:  Do something for a2
INFO:  (2,"{b1,b2,b3}")
INFO:  Do something for b2

And using data normalization:

do $$
declare
  r record;
  i int;
begin
  for r in
    with
      test as (
        select *
        from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)),
      norm as (
        select *, unnest(array[y11,y12,y13]) as y, unnest(array[11,12,13]) as z from test)
    select * from norm
    where y like '%2'  -- Condition here
  loop
    raise info 'Do something for %', r;
  end loop;
end $$;

INFO:  Do something for (1,a1,a2,a3,a2,12)
INFO:  Do something for (2,b1,b2,b3,b2,12)

There are simplified examples, just to show how it could be done in the your more complex task.But it was tested on the PostgreSQL 9.5


How can I import my server list from pgAdmin 3 to pgAdmin 4?

For Windows in C:\Users\%user%\AppData\Roaming\pgAdmin you can find pgadmin4.db which is where pgAdmin4 saves its configuration. It is SQLite format 3 and with SQLite browser you can open it and insert servers into server table and then save and they are added to pgAdmin4. Note that passwords are saved encrypted so leave NULL to input them later.
Now you could make sql script that reads registry export from pgAdmin III and inserts into table for fully automated process.


You mentioned AWR, so you are licensed to use ASH also. You can find the session(s) executing the specific SQL in DBA_HIST_ACTIVE_SESS_HISTORY view based on the sql_id. The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID columns contain information about the PL/SQL call stack.

This however may not be complete if you have several levels of PL/SQL packages, procedures or functions built on top of each other.

If you know the sql_id and that the SQL statement will be executed in the future, you can set a trace event to dump an errorstack for this specific statement (which can cause quite big overhead and create huge trace files, but I could not come up with a more effective method).

Given the below example:

create or replace package mypackage as
  procedure myprocedure;
end;
/

create or replace package body mypackage as
  procedure myprocedure as
    x number;
  begin
    select count(*) into x from user_objects;
  end;
end;
/

execute mypackage.myprocedure;

select distinct sql_id, sql_text from v$sql where lower(sql_text) like '%user_objects%'  and lower(sql_text) not like '%v$%';

SQL_ID        SQL_TEXT
------------- ---------------------------------
4fjk3rakk6x48 SELECT COUNT(*) FROM USER_OBJECTS

You can set the below event for the above SQL:

alter system set events 'sql_trace[SQL: 4fjk3rakk6x48] errorstack(1)';

The next time this SQL statement executes, the trace and errorstack dump will be triggered:

execute mypackage.myprocedure;

The trace file created by this will contain the PL/SQL call stack like this:

----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=4fjk3rakk6x48) -----
SELECT COUNT(*) FROM USER_OBJECTS
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x870c5950         5  package body BP.MYPACKAGE
0x8706e1b8         1  anonymous block

Above shows that there was an anonymous block calling BP.MYPACKAGE at the 5th line, which is exactly the position of the SQL statement in procedure MYPROCEDURE.


Attaching a database in SSMS gives error

This doesn't look like a permissions issue at all, but rather a bug in the UI.

If you are using Management Studio Express, then you should consider two options:

Ensuring you are on the most recent service pack (yes they apply to client tools as well), and perhaps the most recent cumulative update as well.
Many of the bugs in the 2008/2008 R2 version of the tools have yet to be fixed, and likely never will be. Never mind that the Express version has limited functionality. So stop using the crippled Express version from 2008 R2; the fully functional version of Management Studio is now absolutely free as of SQL Server 2012 Service Pack 1. Yes, you can manage all of your 2005, 2008, 2008 R2 and 2012 instances with the newest version of the tool.

In fact, as it turns out (thanks @Justicator), this bug has only been fixed in the 2012 version of the tools, so looks like option (2) is your best bet (or, just not using the UI for this type of task).


Finding dependencies on a SPECIFIC COLUMN (modern way, without using sysdepends)

Here is an AdventureWorks example on viewing column dependencies.

SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.'
                          + ed.referenced_entity_name,
       ColumnName = c.name,
       ReferencedObjectType = o2.type,
       ReferencingObjecType = o1.type
FROM   AdventureWorks2012.sys.sql_expression_dependencies ed
       INNER JOIN AdventureWorks2012.sys.objects o1
               ON ed.referencing_id = o1.object_id
       INNER JOIN AdventureWorks2012.sys.objects o2
               ON ed.referenced_id = o2.object_id
       INNER JOIN AdventureWorks2012.sys.sql_dependencies d
               ON ed.referencing_id = d.object_id
                  AND d.referenced_major_id = ed.referenced_id
       INNER JOIN sys.columns c
               ON c.object_id = ed.referenced_id
                  AND d.referenced_minor_id = c.column_id
WHERE  SCHEMA_NAME(o1.schema_id) + '.' + o1.name = 'HumanResources.vEmployee' AND c.name = 'JobTitle'
ORDER  BY ReferencedObject,
          c.column_id;


Using SQL Server's 2016 Temporal Tables to Record Prior History

Currently temporal supports system time, which means the date/time values placed in those start/end time columns are populated by the system.

In a future version, temporal will also support application time, which will provide ways to have the application dictate the time and, I expect, to update existing data with real start/end times.

The only way to override this really would be to archive data from the history table into your own table (which you may end up doing over time to keep the history table from growing forever anyway), change the times there, and use that table as part of your queries.


Indexes slowing Insertions(Can't use Explain Insert to Analyse)

A file of 4 MB should not take 4 minutes to insert.I see your table have InnoDB engine.

I hope you first need to optimize basic InnoDB settings for that please have a look at Innodb Performance Optimization Basics

Now How to speed up InnoDB Inserts

There's an entire document  dedicated to bulk loading data into InnoDB. The main points:

disable autocommit to avoid an extra log flush for each insert statement: SET autocommit=0;...sql import;COMMIT;
disable foreign and unique checks (you can't disable all indexes completely):
SET unique_checks=0;
SET foreign_key_checks=0;

Setting innodb_flush_log_at_trx_commit = 0 (or 2) may also speedup inserts. Please note, ACID rules would love that value to be at 1.


Sql Server replication: Initial snapshot for publication is not yet available?

Once you add articles to a transaction replication that is synced from snapshot(@sync_type = 'automatic'), you need to run the snapshot agent to generate the snapshot for the added article.  Note, depending on the immediate_sync parameter, it will create snapshot of either the entire publication(1) or just the article that you have added(0).


MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another

Consider also replication. You can replicate only your tables so that you don't override any stored procedures or functions that the developers are working on. You can set up a publisher on production and 2 subscribers: one on TST and one on DEV.

It's almost real time and very suitable for scenarios like yours. I always try to go for replication instead of anything else because in a stable environment you set it up and forget it's there while saving network bandwidth and unnecessary data copy. (with backup and restore you basically [copy prod], [delete tst/dev], [paste prod in dev/tst] - 90% the same data everyday)

Downsides:

it's a bit of trouble until you learn to work with it
it will take some network bandwidth during the day to send your
transactions from the publisher (prod in your case) to the subscriber
(or subscribers in your case: dev and tst).
it will eat up a lot of network bandwidth, SQL Server memory and disk I/O when you initially set it up and this will repeat if and every time you need to reinitialize the replication. You can also set up replication from backup and that goes faster when you initially set it up and if you ever reinitialize.

More documentation about replication here.


Try using aliases like this:

SELECT l.*, c.*, u1.*, u2.*
FROM log l
LEFT JOIN cars c ON l.car_id=c.car_id
LEFT JOIN users u1 ON l.user_id1=u1.user_id
LEFT JOIN users u2 ON l.user_id2=u2.user_id

(Note that it's bad practice to use "SELECT *" except for ad-hoc queries. So you should instead enumerate the exact columns you want to select.)


how do I configure Oracle Net Listener on a server with two network connections

If you have static ips you can configure them all in the listener.ora file:

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=1521))
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.2)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc1521))))

If you don't know the IPs or Hostname, you can also leave out the HOST part (this is undocumented as far as I know) this will result in listening on any:

(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(PORT = 1521))
)


Is it possible to view LRU-K values in SQL Server?

There is in fact no useful way to do this as far as I can see.

The other answer mentions DBCC PAGE and leaves it up to the reader to figure out the details. From experimentation I assume they mean bUse1.

This fails to take account that DBCC PAGE is itself a use of the page and the value gets updated before it is shown to us.

A script demonstrating this is below (takes 12 seconds to run).

USE tempdb;

CREATE TABLE T(X INT);

INSERT INTO T VALUES(1);

DECLARE @DBCCPAGE NVARCHAR(100);

SELECT @DBCCPAGE = 'DBCC PAGE(0,' + CAST(file_id AS VARCHAR) + ',' + CAST(page_id AS VARCHAR) + ',0) WITH TABLERESULTS;'
FROM   T CROSS APPLY  sys.fn_PhysLocCracker (%%physloc%%)

DECLARE @DbccResults TABLE
(
      ID INT IDENTITY,
      ParentObject VARCHAR(1000)NULL,
      Object VARCHAR(4000)NULL,
      Field VARCHAR(1000)NULL,
      ObjectValue VARCHAR(MAX)NULL
)
INSERT INTO @DbccResults EXEC(@DBCCPAGE)
WAITFOR DELAY '00:00:07'
INSERT INTO @DbccResults EXEC(@DBCCPAGE)
WAITFOR DELAY '00:00:05'
INSERT INTO @DbccResults EXEC(@DBCCPAGE)

SELECT *
FROM @DbccResults
WHERE Field = 'bUse1'
ORDER BY ID

EXEC(@DBCCPAGE)

DROP TABLE T

Typical results are

+----+--------------+-------------------------+-------+-------------+
| ID | ParentObject |         Object          | Field | ObjectValue |
+----+--------------+-------------------------+-------+-------------+
|  8 | BUFFER:      | BUF @0x00000002FE1F1440 | bUse1 |       54938 |
| 49 | BUFFER:      | BUF @0x00000002FE1F1440 | bUse1 |       54945 |
| 90 | BUFFER:      | BUF @0x00000002FE1F1440 | bUse1 |       54950 |
+----+--------------+-------------------------+-------+-------------+

With the second result being

+---------+-------------------------+--------------+--------------------+
| BUFFER: | BUF @0x00000002FE1F1440 | bpage        | 0x00000002F4968000 |
| BUFFER: | BUF @0x00000002FE1F1440 | bhash        | 0x0000000000000000 |
| BUFFER: | BUF @0x00000002FE1F1440 | bpageno      | (1:120)            |
| BUFFER: | BUF @0x00000002FE1F1440 | bdbid        | 8                  |
| BUFFER: | BUF @0x00000002FE1F1440 | breferences  | 0                  |
| BUFFER: | BUF @0x00000002FE1F1440 | bcputicks    | 0                  |
| BUFFER: | BUF @0x00000002FE1F1440 | bsampleCount | 0                  |
| BUFFER: | BUF @0x00000002FE1F1440 | bUse1        | 54950              |
| BUFFER: | BUF @0x00000002FE1F1440 | bstat        | 0x9                |
| BUFFER: | BUF @0x00000002FE1F1440 | blog         | 0x1c9a             |
| BUFFER: | BUF @0x00000002FE1F1440 | bnext        | 0x0000000000000000 |
+---------+-------------------------+--------------+--------------------+

The output after the 7 second delay is incremented by 7 and after the 5 second delay by 5.

So it seems clear that these LRU values are seconds since some epoch. Restarting the SQL Server service does not alter the epoch but restarting the machine does.

The value rolls over every 65,536 seconds so I presume that it just uses something like system_up_time mod 65536

This does leave one unanswered questions in my mind (any takers?). SQL Server uses LRU-K with K=2 according to the internals book. Shouldn't there be a bUse2? If so where is that?

There is one way of observing the bUse1 value without changing it that I know of though and that is demonstrated by Bob Ward here.

Attach a debugger to the SQL Server process and display referenced memory for the memory address of the buffer structure (shown to be 0x00000002FE1F1440 above).

I did this immediately after running the script above and saw the following.

(From previous experimentation I'd found the highlighted bytes were the only ones that changed between runs so these are definitely the right ones).

One surprising aspect is that SELECT CAST(0xc896 as int) = 51350.

This is exactly 3600 (one hour) less than reported by DBCC PAGE.

I believe this to be some attempt to disfavour pages being kept in cache by calling DBCC PAGE itself. For a "normal" page select this one hour adjustment does not occur. After running

SELECT *
FROM T

SELECT ((ms_ticks) % 65536000) / 1000 AS [Roughly Expected Value]
FROM sys.dm_os_sys_info

The value shown in memory is as expected.

The DBCC command actually updates that value twice. Once at

sqlmin.dll!BPool::Touch()  + 0x3bfe bytes
sqlmin.dll!BPool::Get()  + 0x12e bytes
sqlmin.dll!LatchedBuf::ReadLatch()  + 0x14f bytes
sqlmin.dll!UtilDbccDumpPage()  + 0x364 bytes
sqlmin.dll!DbccPage()  + 0xfa bytes
sqllang.dll!DbccCommand::Execute()  + 0x153 bytes

With the higher value then again at

sqlmin.dll!LatchedBuf::FreeAndUnlatch()  + 0x71 bytes
sqlmin.dll!UtilDbccDumpPage()  + 0x545 bytes
sqlmin.dll!DbccPage()  + 0xfa bytes
sqllang.dll!DbccCommand::Execute()  + 0x153 bytes

With the lower one.

I'm not aware of any way to get buffer addresses for pages without using DBCC BUFFER/ DBCC PAGE any way though and using both of these changes the value we are trying to inspect!


PostgreSQL "upsert" tuning with unique constraint

Partitioning the table would only help if the data in the files you are trying to insert are also partitioned using the same scheme.

If you sort the data to be inserted into the same order as the unique index is defined on, that should help if the bottleneck is reading from the index.  But if the table is not clustered in the same order as the index, and the bottleneck is reading from the table, this might not help much.

For this type of task, I would usually deduplicate outside the database.  You might also look into using something like file_fdw to map the new data into the database, and then query and insert for new data.


Find efficient way of migration 2TB GBK table in SybaseIQ to UTF-8 files

I've promoted about 2X speed by named pipe.

First step:(create a named pipe)

mkfifo p1

Second step:(waiting to convert)

cat p1| iconv -f GBK -t UTF-8 > target.file

Last step:(export to p1, see the sql scripts below)

SET OPTION TEMP_EXTRACT_NAME1 = 'p1';
SELECT * FROM MY_TABLE;
SET OPTION TEMP_EXTRACT_NAME1 = '';


Error Referencing wp_users as Foreign Key

The MyISAM engine doesn't support foreign keys. Use the InnoDB engine instead.

Other observations:

The referenced table has no index on display_name. As per documentation, it is required to have an index on the referenced field.

It is better however to use the ID field from wp_users table to be referenced, and use this ID to lookup display_name, like:

FOREIGN KEY (user_id) REFERENCES wp_users(id)

In addition, change the field in the time_sheet table from username varchar(250) not null, to user_id BIGINT not null...


Architecture design for a large number of db records upgrade

You should test performing all the changes in a single transaction.  If you can make it work, this is by far the best option.

To minimize the transaction runtime, get all the data loaded into SQL Server before you start the transaction, using staging tables or temporary tables (or JSON, or XML or Table-Valued Parameters).

And consider turning on the READ COMMITTED SNAPSHOT database option to enable the application to continue to read tables affected by uncommitted transactions.

But this:

  1k independent operation that may require to validate the existing
  state of a record and updating it.

seems like something that can be performed in a second or two, at most.  If running entirely on the server-side.

If you determine that the transaction has too great an impact on the application then you have to pick an option that has a some significant downsides, such as:

1) Perform the changes during application downtime

2) Copy the affected tables and keep the copies in sync with triggers or replication.  Apply the changes to the copies, and then perform an ALTER TABLE .. SWITCH or table renames in a transaction to replace the production tables with the copies.

3) Write "undo" scripts to back-out incomplete changes on failure.


I want to group records that have the same combination of class,
  ip_address, and hostname, and keep the highest timestamp for each day
  from each group.

Not using the column name timestamp (like you shouldn't either). It's a reserved word in SQL and a basic type name in Postgres. Using ts instead.

The query is surprisingly simple with DISTINCT ON:

SELECT DISTINCT ON (class, ip_address, hostname, ts::date) *
FROM   agent_log
WHERE  ts < now() - interval '7 days'
ORDER  BY class, ip_address, hostname, ts::date, ts DESC;

Detailed explanation:
Select first row in each GROUP BY group?


Do not want to allow Queries to take more than x minutes?

In mysql 5.7 ; you can use max_execution_time
From the documentation:

Statement Execution Time Optimizer Hints
The MAX_EXECUTION_TIME hint is permitted only for SELECT statements. It places a limit N (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it:
MAX_EXECUTION_TIME(N)

Example with a timeout of 1 second (1000 milliseconds):
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

The MAX_EXECUTION_TIME(N) hint sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_execution_time system variable applies.
The MAX_EXECUTION_TIME hint is applicable as follows:

For statements with multiple SELECT keywords, such as unions or statements with subqueries, MAX_EXECUTION_TIME applies to the entire statement and must appear after the first SELECT.

It applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

It does not apply to SELECT statements in stored programs and is ignored.


Fetch ids(Primary Key) from table where ids not available in table within range

SELECT n
FROM (
        SELECT 1000*n1+100*n2+10*n3+n4+1 n
        FROM
                (
                SELECT 0 n1 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n1,
                (
                SELECT 0 n2 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n2,
                (
                SELECT 0 n3 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n3,
                (
                SELECT 0 n4 UNION
                SELECT 1 UNION
                SELECT 2 UNION
                SELECT 3 UNION
                SELECT 4 UNION
                SELECT 5 UNION
                SELECT 6 UNION
                SELECT 7 UNION
                SELECT 8 UNION
                SELECT 9
                ) n4
        HAVING n < (
                    SELECT MAX(student_id)
                    FROM Student
                   )
     ) numbers
LEFT JOIN Student ON student_id = n
WHERE student_id IS NULL

fiddle


Login failed for user 'NT Service\SSISScaleOutMaster140'

Not sure it this is exactly what you need for replication but it is worth a quick try.

SQL Server uses the server SID as part of the password encryption/generation. Often just creating the same user name and password on 2 servers that are in an Availability Group or Mirrored or Replicated, maybe for Linked servers as well. The general term for these cases is 'Orphaned Users'. You will see it often in your career.

So just using the same login name and password will give an error. Usually when something fails over at 3AM because it wasn't tested...

There are two ways to do this. The 'old school' way is to go to this link ( https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server ) and run the included script on the server that works, meaning you can login to that server. The script will create two stored procedures in Master. You execute the sp_help_revlogin SP on the working server, it will generate SQL code that you can copy and paste into the non-working server.

From your explanation I suspect you will get an error about the login already existing, go ahead and drop the user on the non-working server. It will get created from the generated script.

The more 'modern' way is to use PowerShell, specifically the Copy-SqlLogin and Export-SqlLogin found at https://dbatools.io/

If you are comfortable using PowerShell, these should work well and are pretty well documented on the website.

Hopefully this will help or at least point to a better answer.

Norman


Full database backup using xtrabackup stream

Just ran into the same problem. As it turned out in this case, the problem was caused by dumping to a NFS volume. Writing the dump to local disk solved this issue.


Start a dedicated Mysql server with as default engine blackhole (and maybe the only one) and engine_substitution enabled.
Then you can restore your mysqldump and monitor warning(s) and error(s) generated.

Maximum execution speed and no disk space taken ...

Hope this will help you


How to create a new database in PostgreSQL 10 in Windows from batch file?

Create a bat file with the following content and execute it:

c:\path\to\postgresql\bin\psql.exe -f C:\path\to\db_create.sql  postgresql://user:password@localhost:port/postgres

This is what worked for me.


Build an Oracle query to get parent and child records

Such hierarchical queries can be written using CONNECT BY as:

SQL> set null "empty"
SQL> select id, dependency, prod, prod2 from forgerock
     connect by prior dependency = id start with prod2 = '102' order by level desc;

        ID DEPENDE PROD       PROD2
---------- ------- ---------- ----------------------
         1 empty   empty      empty
         2 1       Full       101
         4 2       Robust     102


use nextvalue(idstackacos_find_the_right_sequence_name ) instead of

OLD:

SELECT 'notyetindbisforsureunique', '2018-09-18 14:34:57', '2018-09-18 14:34:57'

NEW:

SELECT nextvalue(idstackacos_find_the_right_sequence_name) , '2018-09-18 14:34:57', '2018-09-18 14:34:57'

If it's not working please copy here the definition of the table and of the sequence.


Exporting a Database schema and triggers

To find information about database object ( I am using triggers as example here )
use TOAD
choose Triggers
Right Click on Triggers
Create Script

That will give you source for all triggers

Repeat this for all TABLES, INDEXES, SEQUENCES etc. After reverse engineering a few - you will get hang of it.

Connections between tables

I believe you mean "relations between tables". If you schema is small say a few tables, then you can browse thru Parent and Child option of each table and document them.

For large number of tables, you will need to use TOAD to create Data Model Diagram; or maybe google for "Data Modeling Tools" and find one that fits your needs and budget.

You will imperatively need to refer to data model diagram many times in your new roles - so do it patiently and correctly.


Log error restoring old database to SQL Server 2017

Make sure you've installed the minimum service pack level for 2008 on your source instance before making the backup. It appears to need to be on SP4 for it to be restorable to SQL Server 2017: Supported Version and Edition Upgrades for SQL Server 2017


Get The Latest values For Same ID/Duplicate ID

Try this:

SELECT primary_table.product_id, primary_table.on_hand, primary_table.created_at
     FROM
`table_name` primary_table,
(SELECT MAX(purchase_id) as pId FROM `table_name` Group by product_id) second_table
     where primary_table.purchase_id = second_table.pId


This might be Bug 7150873 SQL SCRIPTS FILE NAME CONTAINING SPACES RESULTS IN SP2-0556.

I assume that your script is trying to call a script containing spaces.


Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS

I have MySQL 5.6.21 on my laptop running Windows 8.1 and it is doing nothing. It still has that state. Please keep in mind that this state exists in the performance schema, in the table threads, in the column PROCESSLIST_INFO.

Please note the MySQL Documentation on PERFORMANCE_SCHEMA.THREADS.PROCESSLIST_INFO

  The statement the thread is executing, or NULL if it is not executing any statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if a CALL statement executes a stored procedure that is executing a SELECT statement, the PROCESSLIST_INFO value shows the SELECT statement.

There is still an active bug report for MySQL 5.6.20, 5.6.22, 5.7.6 on this. The performance schema is enabled by default in MySQL 5.6. Therefore, the only solution, at this time, would be to disable the performance schema. You would have look for future releases to see if this has been resolved.

Personally, I would not worry about this. It seems to behave like MySQL Replication's I/O and SQL threads. Those threads poll indefinitely. Log recovery of this nature would happen during MySQL startup and should not be polling after MySQL Startup and InnoDB Crash Recovery have completed. If the bug simply left the state unchanged and nothing is going on, you could go on using MySQL as usual.

To verify this, please do the following:

STEP 01 : Look in the error log and make sure

crash recovery had finished
MySQL: ready for connections appears near the bottom

STEP 02 : Log into MySQL, and run this

mysql> select * from information_schema.engines where engine='InnoDB'\G

It should look like this

*************************** 1. row ***************************
      ENGINE: InnoDB
     SUPPORT: DEFAULT
     COMMENT: Supports transactions, row-level locking, and foreign keys
TRANSACTIONS: YES
          XA: YES
  SAVEPOINTS: YES
1 row in set (0.00 sec)


How to automate creating credentials in a secure way

You can leverage PowerShell and the Thycotic REST API to fetch your credentials from Secret Server programmatically. Use "Windows Authentication" to avoid having to store credentials in the PowerShell scripts, or enter them during runtime.

If your deployments run under a build agent service account context, then you need to ensure that service account has access in Secret Server to the credentials you want to access.

Leveraging the API as a step in your release prior to deploying the SQL credential, you can fetch the credential and pass it to the PowerShell script that deploys the credential.


to what "scale" of applications does nosql support?

This question is really far too vague to answer effectively.  There are dozens of "NoSQL" data stores out there which have various use cases.  Here is a 10,000 foot view of what's out there.

In my mind, there are basically 3 main categories of NoSQL data stores commonly used, key/value stores, document databases, and big data (hadoop).  This is a somewhat artificial designation and many of these products can arguably cross into multiple areas.  There are some other categories, such as graph databases, which are more specialized towards a specific problem and I am not going to discuss them here as I have no expertise about them.

Most NoSQL databases are simple key/value stores which are very fast when retrieving named keys.  They are particularly inefficient if you need to scan or aggregate over sets of data.  Examples of k/v stores are memcached, Riak, Redis, CouchBase,  Voldemort, and Amazon DynamoDB.  With the HandlerSocket plugin (built into Percona Server), even MySQL can be used as a very fast k/v store.  Each of these k/v stores have different feature sets designed to solve different problems.  Very few of them are suitable as the authoritative/primary data store for an application because of how difficult and inefficient it is to perform set operations.  These are mostly used as caching layers or storing very specialized data that does not require relational operations.

Another general class of NoSQL databases are document stores.  Examples include MongoDB and Cassandra.  These types of data stores store more structured data than k/v stores and often have a more capable query language.  They have flexible "schemas" that make it possible to keep completely different sets of data from one row to the next.

Finally, you get to the true "Big Data" stores of which Hadoop and it's related query languages, pig, hive (a SQL interface to hadoop), and hbase (a real-time data store on top of hadoop/HDFS) is king.  With the exception of hbase, Hadoop-based data stores tend to be built for offline processing of truly enormous data sets across hundreds of machines.

As a side note, what drives me absolutely batty about "NoSQL" is that it has literally nothing to do with the SQL language.  NoSQL is about reinventing the data storage layer and making it more "scalable" (another vague, misunderstood term) and highly available.  The query language is irrelevant in most cases and some of these data stores have produced just horribly ugly ways of performing even the most simple operations.  SQL could be used as the access language to most these data stores had the developers made that choice - take a look at VoltDB, MySQL Cluster, or Hive for examples of distributed SQL databases that have "NoSQL" features.  When treated like a key/value store, MySQL with InnoDB is actually incredibly fast at primary key lookups (SELECT value FROM table WHERE key = ?) and it would be relatively easy to create a client library that creates a consistent hashing scheme to build a distributed MySQL cluster as one would use Riak, Redis, or memcached.

Bottom line is that you'll have to be more explicit about your needs if you want any more detail than that.  Here are just a small subset of the questions that you will need to answer in order to even limit the field:

Is your access pattern realtime (OLTP) or will it be performed in batch operations (OLAP)?
Do you need to perform aggregate or set-based calculations on the data, or is it simply accessing keys by name?
How much data do you have and how is it structured?
Have you determined that a traditional SQL database will not suit your needs?
What are your CAP priorities?
Do you require ACID features?
What kind of operations do you need to perform on the data?

I hope that this helps you a bit in your research.


SQL Server Service Account Configuration - one account per Server?

Separate service accounts for each server and for each service will restrict any issues to just one server and just one service in the following cases:

Service account getting compromised
Service account getting locked out
Service account password changed
Service account getting disabled/deleted/renamed by mistake

Imagine what would happen with a single service account if you entered the service credentials wrong a handful of times (scripts are great for this): you would lock out the account and tear down the whole SQL Server farm. Not good.


How to get columns from system tables or views like sys.database_files (SQL Server 2012)

Since you are on SQL Server 2012, best is to use the new DMF sys.dm_exec_describe_first_result_set. This dmf will take a transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.

so for example, you want to find out the columns names along with the datatypes and precison for sys.database_files, simply use

SELECT
    name,
    system_type_name,
    is_nullable
 -- add/remove columns as per your need ....
FROM
    sys.dm_exec_describe_first_result_set
    (
        N'SELECT * FROM sys.database_files;', NULL, 0
    ) AS f
ORDER BY
    column_ordinal;

Below are the results :

For versions below sql 2012:

SELECT object_NAME(c.object_id) AS OBJECT_NAME
    ,c.NAME AS COLUMN_NAME
    ,t.NAME AS DATA_TYPE
    ,c.max_length AS MAX_LENGTH
FROM sys.all_columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE object_name(c.object_id) = 'database_files'

Excellent post is written by Aaron Bertrand -- SQL Server v.Next (Denali) :Metadata enhancements which explains this in much details. Also, refer to the CAVEATS in the section down the article.

  I see many suggestions for using information_schema.columns for getting table names for user databases/tables.

Avoid using INFORMATION_SCHEMA views. Worth reading Aaron's post on The case against INFORMATION_SCHEMA views.


Is it possible to restore a database with only database roles membership?

It is not.  See https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15#permissions for detailed reference.

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

  RESTORE permissions are given to roles in which membership information
  is always readily available to the server. Because fixed database role
  membership can be checked only when the database is accessible and
  undamaged, which is not always the case when RESTORE is executed,
  members of the db_owner fixed database role do not have RESTORE
  permissions.


Single service account running all SQL Servers

Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers?

No. Service accounts do not affect performance in any way. Its all about security !

From the SQL Server 2012 security best practice whitepaper (warning: word doc) :

  When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges.

  You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server.

If you are running Windows Server 2008 R2 & SQL Server 2012 and up, then best is to use

Virtual accounts or

  Virtual accounts are managed local accounts that is automatically provisioned and managed. In SQL Server 2012, they are the default service account specified during setup. It can access the network. A virtual service account has a well-known name in the form of NT SERVICE\ and can access the network using the credentials \$.

managed service accounts

  A managed service account is a special type of domain account that can be assigned to a single computer and used to manage a service. It must be provisioned by the domain administrator prior to being used. This type of account cannot be used to log in to a computer and provides automatic SPN and password management, once provisioned.


In the domain of SQL Server backup, what is a backup set, backup device, backup media, backup media set, backup media family

The screenshots from SSMS will help you understand the terms:

backup set - is a backup from a single, successful backup operation. E.g. a full database backup created at 8 AM yesterday

You can see that there are 2 backup sets in a single backup media set

backup device - a disk file or tape drive

backup media - tapes or disks where backup are stored
backup media set - in the screenshot - it's the BAK file where backups are added

backup media family - You're creating the 8 AM full database backup and your database is huge, the backup takes more space than 4 tapes can store. Say you have 4 tape devices that are used to backup your database. When one tape is full, you insert a new one. If your database backup takes 20 tapes, that's 5 tapes per each tape device. The 5 tapes created on the tape device 1 belong to the same backup media family, as they have the same "father" - tape device 1


db2pd: Database mydb not activated on partition 0

db2pd requires Administrator authority on Windows, in the command shell you are executing it.  Setting your ID as a member of the Administrators group on the local machine is not sufficient.

IBM adds "DB2 Command Window - Administrator" in your Start Menu to give you a DB2 Command Window that has administrator privileges (which is the recommended method).  Alternatively if you are using a normal command window (cmd.exe), you would have to start it by right clicking "Command Prompt" and choosing "Run as Administrator".

It might be possible to use runas to execute db2pd, but it's probably easier to just start the command window directly with administrator privileges.


Why is Clustered Index Scan Number of Executions so high?

The JOIN after the scan gives a clue: with less rows on one side of the last join (reading right to left of course) the optimiser chooses a "nested loop" not a "hash join".

However, before looking at this I'd aim to eliminate the Key Lookup and the DISTINCT.

Key lookup: your index on FIR_Incident should be covering, probably (FI_IncidentDate, incidentid) or the other way around. Or have both and see which is used more often (they both may be)
The DISTINCT is a consequence of the LEFT JOIN ... IS NOT NULL. The optimiser has already removed it (the plans have "left semi joins" on the final JOIN) but I'd use EXISTS for clarity

Something like:

select
    F.IncidentID
from
    FIR_Incident F
where
    exists (SELECT * FROM postnfirssummary P
           WHERE P.incident_id = F.incidentid)
    AND
    F.FI_IncidentDate between '2011-07-01 00:00:00.000' and '2011-07-02 00:00:00.000'

You can also use plan guides and JOIN hints to make SQL Server use a hash join, but try to make it work normally first: a guide or a hint probably won't stand the test of time because they are only useful for the data and queries you run now, not in the future


Using secondary id as foreign key to other table

It's not complicated to do.  You need to create the Reference ID as an alternate key in the Customer table with a unique constraint.  Then you can use Reference ID as a foreign key to the Configuration table.  I generally try to avoid this however as it can get confusing and harder to maintain the data model.


Server subscriptions are for:

Republishing data
Serve as alternate synchronization partners (This has been deprecated)
Resolving conflicts according to a priority

If your subscription(s) do not require this behavior then a client subscription will work just fine.  This is covered in Subscription Type.

The Merge subscription type can be specified on the Subscription Type page of the New Subscription Wizard or by specifying a value of local (client) or global (server) for the @subscriber_type parameter in sp_addmergesubscription and sp_addmergepullsubscription.  This is covered in Specify a Merge Subscription Type and Conflict Resolution Priority.

I hope this helps.


How to get N set of data base on one column

MySQL 8+

WITH cte AS ( SELECT *,
                     DENSE_RANK() OVER (ORDER BY order_time) dr
              FROM my_table
              WHERE <conditions> )
SELECT xxx,
       yyy,
       order_time
FROM cte
WHERE dr <= 3

MySQL 5.+

SELECT my_table.*
FROM my_table
JOIN ( SELECT DISTINCT order_time
       FROM my_table
       WHERE <conditions>
       ORDER BY order_time
       LIMIT 3 ) times USING (order_time)
/* WHERE <conditions> */


NoSQL : Getting latest item from DynamoDB table

I think you're on the right track with AtomicLong, but you'd have to run a scan operation each time your app starts to determine which value to start at. You'd want to test this to ensure it doesn't degrade performance too much.

Another option is a metadata item. You could create an additional table for this, or you could add an item to your existing table using a hash attribute value that will never otherwise exist (so it won't clash with your application, and can be easily retrieved using its well-known value). Store name/value pairs that your app needs to know about (e.g. lastSeq) and use conditional writes to change this as necessary (e.g. increment lastSeq but only if the current value is 234).

If there are multiple instances of the client application, you'll want to consider contention and how this might affect things; using conditional writes should alleviate this (but you'll need to query and update the metadata item every time an item is created).

You can read more about conditional writes in the AWS DynamoDB Developer's Guide.


You are correct to conclude that "column" is a keyword. Fortunately, you can escape the name so that, keyword or not, it is accepted:

mysql> ALTER TABLE table_name DROP COLUMN `column`;

Notice the backticks around the column name. This is always the safest way to qualify MySQL object names (for example, did you realize it is possible to use a space in a column name? Not that I encourage this; but it is possible).

If you're curious to look at more craziness, take a look at my post `;`.`*`.`.` is a valid column name, which discusses the over-flexibility in object names in MySQL.


Programmatically get parameters required by DACPAC

The DACPAC is just a zip file, so you could UnZip it and parse the model.xml file contained.

small extract from one of my dacpacs:

<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="2.4" DspName="Microsoft.Data.Tools.Schema.Sql.Sql100DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
    <Header>
        <CustomData Category="SqlCmdVariables" Type="SqlCmdVariable">
            <Metadata Name="ABC" Value="" />
            <Metadata Name="XYZ" Value="" />
        </CustomData>

so I need to define the SqlCmdVariables ABC and XYZ for this dacpac


max_stack_depth not changed even stack changed in limits.conf

This solution works on Fedora Linux, but should work on Ubuntu as well.  The issue is, that in addition to replacing initd, systemd aims to take over security functions that were traditionally handled by things like /etc/security/limits.conf.  (There are other areas where it interferes as well).  When systemd starts PostgreSQL, it ignores /etc/security/limits.conf, and applies its own default limits.  To change them, do the following:

mkdir /etc/systemd/system/postgresql.service.d
cat > /etc/systemd/system/postgresql.service.d/postgresql.conf << XYZZY
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
LimitSTACK=infinity
XYZZY

systemctl daemon-reload

This method also has the added benefit that will not interfere with default changes to service unit files, should they be updated upstream, but will permanently maintain your settings override.  This helpful post lists the other limits that may be set through systemd configuration.

I should note that, at first, I tried to set the limit to 131072, but when I attempted to start PostgreSQL, I got this error:

DETAIL:  "max_stack_depth" must not exceed -384kB.

Changing it to infinity allowed PostgreSQL to start.


Should I shrink this SQL Server data file?

Presuming you are sure the database is not going to need the 200+ GB of extra space it is currently consuming, I would recommend performing a shrink without reorganizing pages, using TRUNCATEONLY:

DBCC SHRINKFILE (N'<file name>' , 0, TRUNCATEONLY);

TRUNCATEONLY simply truncates the last part of the file without moving rows inside the data file.  This is important since it will prevent logical fragmentation of tables inside the data file.  If you perform the shrink without specifying TRUNCATEONLY SQL Server will move pages around inside the file to get the smallest possible file size, thereby creating anywhere from "some" to "a lot" of fragmentation, which may adversely affect performance. Read more about this at the Books Online page for DBCC SHRINKFILE.  Thanks to @Zane for the suggestion to include these details in my answer.

Once you have performed the shrink, you may want to ensure the file growth parameters are set correctly.  For instance, ensure the file growth is not percentage based; use a number of MBs that makes sense for your expected growth rate.


Concatenation of varchar-converted DATEDIFF and placing a string afterwards - issues?

This expression is explicitly returning two different data types (nvarchar and int):
(CASE
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' ' + 'days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS int)
END) AS [Duration]

When CancelDate is not null, the value with be of type nvarchar(max).  Otherwise, it will be of type int.
A column only has one data type, which means SQL Server has to choose between those two and make every row the same type.
int has a higher data type precedence than nvarchar, so SQL Server will implicitly convert the nvarchar values from the first part of your CASE statement to ints.
It fails to convert the strings produced by the first part to int (because they contain letters and spaces), resulting in the error message in your question.
One way to resolve the problem would be to explicitly convert the number to a string, if you want to show both numbers and strings in this column:
(CASE
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS nvarchar(max))
END) AS [Duration]

As a side note:

I used nvarchar(max) for consistency with the first part of your query.  You should consider how many days are really supported, and reduce the size of the output column

You have 5 characters of fixed length (" days"), so I would expect 7 or 8 characters max would do the trick (for 2 or 3 digit durations - unless your tracking 3+ year trips)
the final data type size can affect things like query memory grants, which will have an impact on performance and concurrency

You probably only need varchar, as it doesn't seem your expression would ever produce unicode data


No is the short answer, and you don't really need this.  Full-text indexes are inverted indexes so they store the split words by the unique doc_id that you have to specify when you create the full-text index.  This must be a "unique, single-key, non-nullable column" ideally an integer.  What is essentially a foreign key does not figure and there is no easy way to partition them on that basis.

You could spoof something like this it with a table per company and full-text index per table.  You would need some kind of code logic sat in front to determine which table to insert to / fetch from. This would be a considerable headache to manage at is almost certainly not worth it.

If you had some serious volume ( eg more like 23 billion records ) then you could look at a sharding solution, eg something like an Azure VM per company with an app sat in front of them to determine which machine to connect to.  But clearly you don't need that either.

There were also a number of improvements in SQL 2008 to full-text which is now more integrated into the database engine.  One scenario, where you specify a WHERE clause against a normal column and use the full-text functions, is referred to as a 'Mixed Query' and discussed here.  This is still a great article even though the information is for SQL 2008.

If you're generally concerned about performance and plans, why not spin up some test data, introduce some skew and try it out.  I knocked up this script with ~2 million rows in a few minutes:

!!TODO introduce some skew
USE master
GO

SET NOCOUNT ON
GO

DBCC TRACEON(610)   -- Minimal logging
GO

GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo' )
BEGIN
    ALTER DATABASE fullTextDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo' )
CREATE DATABASE fullTextDemo
GO

ALTER DATABASE fullTextDemo SET RECOVERY SIMPLE
GO

USE fullTextDemo
GO

IF OBJECT_ID('dbo.yourAddresses') IS NOT NULL DROP TABLE dbo.yourAddresses
IF OBJECT_ID('dbo.companies') IS NOT NULL DROP TABLE dbo.companies
GO

CREATE TABLE dbo.companies (
    companyId       INT IDENTITY NOT NULL,
    companyName     NVARCHAR(50) NOT NULL,

    CONSTRAINT PK_companies PRIMARY KEY ( companyId )
)
GO

CREATE TABLE dbo.yourAddresses (
    rowId           INT IDENTITY,
    companyId       INT NOT NULL FOREIGN KEY REFERENCES dbo.companies ( companyId ),
    searchTerms     NVARCHAR(2048) NOT NULL

    CONSTRAINT PK_yourAddresses PRIMARY KEY ( rowId )
)
GO

-- Populate the companies
;WITH cte AS (
SELECT TOP 250 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.companies ( companyName )
SELECT NEWID()
FROM cte
GO

-- Generate 2,636,000 records
INSERT dbo.yourAddresses ( companyId, searchTerms )
SELECT c.companyId, m.[text]
FROM dbo.companies c
    CROSS JOIN ( SELECT * FROM sys.messages ) m
WHERE m.language_id = 1033
AND m.[text] Like '[a-z]%'
GO

CREATE INDEX _idx ON dbo.yourAddresses ( companyId ) INCLUDE ( searchTerms )
GO

-- !!TODO look at compression
--ALTER INDEX PK_yourAddresses ON dbo.yourAddresses REBUILD WITH ( DATA_COMPRESSION = PAGE )
--GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_yourAddresses' )
CREATE FULLTEXT CATALOG ftc_yourAddresses
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.yourAddresses ( searchTerms ) KEY INDEX PK_yourAddresses ON ftc_yourAddresses WITH CHANGE_TRACKING MANUAL  -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

ALTER FULLTEXT INDEX ON dbo.yourAddresses START FULL POPULATION;
GO

DECLARE @i INT
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses ft
WHERE companyId = 42
 AND CONTAINS ( searchTerms, 'data' )
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses a
    INNER JOIN CONTAINSTABLE ( dbo.yourAddresses, searchTerms, 'data' ) ct ON a.rowId = ct.[key]
WHERE a.companyId = 42
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses a
    INNER JOIN CONTAINSTABLE ( dbo.yourAddresses, searchTerms, 'data' ) ct ON a.rowId = ct.[key]
WHERE a.companyId = 42
OPTION ( MERGE JOIN )
GO

SELECT TOP 100 *
FROM sys.dm_fts_index_keywords (DB_ID(), OBJECT_ID('dbo.yourAddresses') )

SELECT TOP 100 *
FROM sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID('dbo.yourAddresses') )
ORDER BY document_id
GO


Correct Model for Users in a Collection with Items in that Collection

I think you are looking to use a composite foreign key from the table that models the items, to the table that models the group relationship, not to the parent users and groups, something along the lines of (pseudo code):

CREATE TABLE Users (UserIdentifier, (other attributes));
CREATE TABLE Groups (GroupIdentifier, (other attributes));
CREATE TABLE Items (ItemIdentifier, (other attributes));

CREATE TABLE Users_Groups (UserIdentifier REFERENCES Users, GroupIdentifier REFERENCES Groups);

CREATE TABLE Itmes_Users_Groups (Item REFERENCES Items, UserIdentifier, GroupIdentifier, (UserIdentifier + GroupIdentifier REFERENCES User_Groups))

HTH


If the stored procedure already ran and you're looking for the smoking gun, and you're looking for history without setting something up ahead of time, you'll need to analyze the transaction log.

If you're asking because you're ready to change the server or the database to be prepared the next time it happens, you can:

Add a line to the stored procedure to log that information to a table
Run a trace with Profiler or Extended Events, focusing just on that stored procedure
Implement auditing
If the stored procedure runs for a long time, you could log sp_WhoIsActive to a table every few minutes via an Agent job, and then later go back through those logs to see who was running it


SQL Server symmetric key & certificates games by Hacker?

The purpose of a certificate in SQL Server is to protect the symmetric keys, but it's more for protecting your offline data: .mdb files and backups.  It's not really designed for preventing access to the data once an attacker has access to the running SQL instance (with enough permission to access the data and run decryption.)

If you use a password for your symmetric key, then someone who knows or can guess the password can take a copy of your data and load and decrypt it.
If you use a certificate, then the attacker needs a copy of the data, and a backup of the certificate, and the password for the certificate.


MySQL allows a join in a DELETE statement. You can use this feature to first get a derived table, similar to the query you have that finds all maximum ids for groups that have more than 1 row, then join this derived table to the table itself.

The following assumes that (tbl_cod_rpt_id) is the primary key of the table:

DELETE
    t.*
FROM
    ( SELECT awb_no, MAX(tbl_cod_rpt_id) AS tbl_cod_rpt_id
      FROM tbl_cod_rpt_COPY
      GROUP BY awb_no
      HAVING COUNT(*) > 1
    ) AS dup
JOIN
    tbl_cod_rpt_COPY AS t
  ON
    t.tbl_cod_rpt_id = dup.tbl_cod_rpt_id ;


Do I need GRANT for INSERT to table If has EXECUTION GRANT for procedure?

Permissions on objects used by the stored procedure are not checked with an unbroken ownership chain. Consequently, there is no need to grant permissions on objects used by stored procedures when all objects involved have the same owner, which is typically the schema owner. Users need only stored procedure execute permissions in your case, which includes permissions inherited via role membership.
This assumes no dynamic SQL within stored procedures and objects reside in the same database. See the authorization and permissions documentation for details.


Improve Performance by Left Join and Group By Count - Postgresql

Your only hope is to get an execution plan that uses nested loop joins so that you don't have to read the whole layout_feature table.

CREATE INDEX ON layout_feature (session_id, layout_id);
VACUUM layout_feature;

The VACUUM is there to give you an index only scan. You have to keep the table well vacuumed for that to work.

Next, you should increase work_mem to get a hash aggregate rather than a sort + group aggregate (if that is possible here).

If one of the tables does not change often, or you don't need exact results, using materialized views can do a lot.


MySQL merge two tables of different database to single database

What you've entered is equivalent to:

CREATE TABLE TB3A AS (

SELECT a.AgreementID, a.Column2, a.Column3, a.Column4,
       a.Column5, b.AgreementID, b.Column2, b.Column3,
       b.Column4, b.Column5, b.Column6, b.Column7, b.Column8
  FROM TB1A a JOIN TB2A b
);

You'd have 13 columns, with only 8 distinct names. This is a problem.

What you probably want to do is this:

CREATE TABLE TB3A AS (

SELECT AgreementID, Column2, Column3, Column4,
       Column5, Column6, Column7, Column8
  FROM TB2A
);

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A;

TB3A will now have m + n rows, where m is the number of rows in TB1A, and n is the number of rows in TB2A.

If some rows in TB1A and TB2A are duplicates, this will not remove those. You can change the INSERT ... SELECT statement to filter out rows from TB1A that match rows already in TB3A (thus, originally from TB2A) with matching values in whatever columns you need. For instance, to not bring in rows from TB1A that have the same AgreementId as rows already in TB3A, the INSERT would be:

INSERT INTO TB3A (AgreementID, Column2, Column3, Column4, Column5)
SELECT * FROM TB1A
 WHERE NOT EXISTS (SELECT 1 FROM TB3A
                    WHERE TB1A.AgreementID = TB3A.AgreementId
                  )
;

Additional note on the original statement: JOIN is generally assumed to be an INNER JOIN - one that matches rows form the two tables based on one or more criteria. For example, TB1A JOIN TB2A ON TB1A.AgreementId = TB2A.AgreementId would return the rows from TB1A and TB2A the had the same AgreementID. Rows in TB1A with an AgreementID not found in TB2A would not be returned, and rows in TB2A with an AgreementID not found in TB1A would not be returned.

If there were three rows in TB1A with the same AgreementId, and 2 rows in TB2A with that same ID, those rows would generate 6 rows in the result set:

TB1A      TB2A
  x         m
  y         n
  z

Result set:
    x,m
    x,n
    y,m
    y,n
    z,m
    z,n

In some SQL dialects (MySQL included), if there is no JOIN criteria specified, then the INNER JOIN becomes what's called a CROSS JOIN. Here, since there's nothing to indicate which rows should not be joined together, all rows are joined together. If you have m rows in TB1A, and n rows in TB2A, you don't get m+n rows - you get m*n rows. That's what would have happened, if your original statement had run.


mysql db backup and restore withought mysqldump. only tar?

Yes.

Stop mysqld
tar that directory
Start mysqld

It will backup everything except the config file(s) such as my.cnf.

LVM snapshot is much faster, but requires setup.


Influx - Help to choose in between Tag and Field

You can take a call considering below two scenarios.

If you need to run a GROUPBY clause on a particular column/field, then you need to create it as Tag, otherwise you won't be able to use GROUPBY clause.
Indexing: Fields cannot be indexed in influxdb. Tag are automatically indexed.

Also, influx by default creates a 'time' tag, so it's better while inserting you pass the value to 'time' instead of adding an extra column called 'datetime'


Update Postgres Table with Array of JSON

If you know the desired column names (be it the same or different from the keys in the JSON structure, you can use json[b]_to_recordset():

SELECT * FROM jsonb_to_recordset('[
    { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
    { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
    { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
    { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
]'::jsonb) AS t (col1 text, col2 integer, col3 integer, col4 text);

 col1 │ col2 │ col3 │ col4
──────┼──────┼──────┼───────
 a    │    1 │    1 │ one
 b    │    2 │    2 │ two
 c    │    3 │    3 │ three
 d    │    4 │    4 │ four

As the documentation tells us,

  Note: In json_populate_record, json_populate_recordset, json_to_record and json_to_recordset, type coercion from the JSON is "best effort" and may not result in desired values for some types. JSON keys are matched to identical column names in the target row type. JSON fields that do not appear in the target row type will be omitted from the output, and target columns that do not match any JSON field will simply be NULL.

If you already have a table to work with, json_populate_recordset() is an even better solution:

CREATE TABLE inputtable (col1 text, col2 integer, col3 integer, col4 text);

SELECT * FROM jsonb_populate_recordset(NULL::yourtable, '[
    { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
    { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
    { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
    { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
]'::jsonb);

 col1 │ col2 │ col3 │ col4
──────┼──────┼──────┼───────
 a    │    1 │    1 │ one
 b    │    2 │    2 │ two
 c    │    3 │    3 │ three
 d    │    4 │    4 │ four

Now updating the table itself may be done like this:

WITH source AS (SELECT * FROM jsonb_populate_recordset [...])
UPDATE yourtable
   SET col1 = s.col1, col2 = s.col2
  FROM source AS s
 WHERE col3 = s.col3;

In case it seems slow, it might make sense to not use the CTE, but a subquery in the FROM clause instead.


First of all, you might be able to get acceptable performance with queries directly against the query store catalog views by updating stats, adding query hints with plan guides, or changing the database compatibility level / CE.  See the answers from Forrest and Marian here:

Never ending Query Store search

If you're on SP1 or greater, the simplest approach would be to use DBCC CLONEDATABASE - which includes statistics, query store data, and schema objects - but none of the actual data from the tables.

Otherwise, for exporting, one approach would be a simple SELECT...INTO from the query store views to the "sandbox" database.  These are the relevant views.

The basic approach would be like this:

SELECT * INTO Sandbox.dbo.query_store_runtime_stats FROM sys.query_store_runtime_stats;
SELECT * INTO Sandbox.dbo.query_store_runtime_stats_interval FROM sys.query_store_runtime_stats_interval;
SELECT * INTO Sandbox.dbo.query_store_plan FROM sys.query_store_plan;
SELECT * INTO Sandbox.dbo.query_store_query FROM sys.query_store_query;
SELECT * INTO Sandbox.dbo.query_store_query_text FROM sys.query_store_query_text;
SELECT * INTO Sandbox.dbo.query_store_wait_stats FROM sys.query_store_wait_stats;

The nice thing about this approach is that:

you'll only get the data you need (1000 MB)
you can add indexes to support your reporting queries, because these are actual tables
they won't have the unusual memory scanning behavior that leads to poor performance against the actual views (again because they are actual tables)

Note: the SELECT...INTO queries shouldn't drive up CPU like the built-in query store reporting queries, because they won't have the problematic joins that cause repeated access to the in-memory TVFs

you can keep different versions of the data (for different CU levels, etc) by changing the table names, or adding a column to the tables that indicates the data and / or version of SQL Server that was used for that import

The "con" of this approach is that you can't use the query store user interface.  A workaround for that would be to use profiler or extended events to capture the queries being executed by the user interface for the specific reports you need.  You could even do this capture in a non-prod environment, as the queries should be the same.

Warning: this is potentially a really bad idea.  There's a reason you can't normally write to these tables.  Special thanks to Forrest for mentioning the possibility to me.

If you really want to be able to use the user interface, you can actually load the base Query Store tables with data while connecting via the DAC.  Here's what worked for me.

Reminder: you have to be using a DAC connection to do this, otherwise you'll get errors related to the sys.plan_persist_* tables not existing

USE [master];
GO
CREATE DATABASE [Sandbox];
GO

USE [YourSourceDatabaseWithTheQueryStoreInfo];
GO

BEGIN TRANSACTION;

INSERT INTO Sandbox.sys.plan_persist_runtime_stats SELECT * FROM sys.plan_persist_runtime_stats;
INSERT INTO Sandbox.sys.plan_persist_runtime_stats_interval SELECT * FROM sys.plan_persist_runtime_stats_interval;
INSERT INTO Sandbox.sys.plan_persist_plan SELECT * FROM sys.plan_persist_plan;
INSERT INTO Sandbox.sys.plan_persist_query SELECT * FROM sys.plan_persist_query;
INSERT INTO Sandbox.sys.plan_persist_query_text SELECT * FROM sys.plan_persist_query_text;
INSERT INTO Sandbox.sys.plan_persist_wait_stats SELECT * FROM sys.plan_persist_wait_stats;
INSERT INTO Sandbox.sys.plan_persist_context_settings SELECT * FROM sys.plan_persist_context_settings

COMMIT TRANSACTION;
GO

USE [master];
GO
ALTER DATABASE [Sandbox] SET QUERY_STORE = ON (OPERATION_MODE = READ_ONLY);

Note: if you're on SQL Server 2016, you'll need to remove the line about wait stats - that catalog view wasn't added until SQL Server 2017

After doing that, I was able to use the Query Store UI in SSMS to view info on the queries from the source database.  Neat!

It's important to load the data into the Sandbox database with Query Store off, and then turn Query Store on in read only mode.  Otherwise QS ended up in an error state, and this was written to the SQL Server error log:

  Error: 12434, Severity: 20, State: 56.
  The Query Store in database Sandbox is invalid, possibly due to schema or catalog inconsistency.

I also noticed that this doesn't work if there are in-memory OLTP (Hekaton) tables in the source database.  No matter what I do, Query Store ends up in the "Error" state with this message in the error log:

  Error: 5571, Severity: 16, State: 2.
  Internal FILESTREAM error: failed to access the garbage collection table.

You may be able to work around that by adding a memory-optimized filegroup to the Sandbox database, I haven't tried that yet.


APEX 5: Horizontal Scollbar on page instead of region

I solved this issue by enabling the "Maximize Button" for the Region.


Append and add information from rows above

You can turn your AsIs table into a row set like the ShouldBe table without any temporary tables, using a single query like this:

SELECT
  PN,
  Origin,
  Sub
FROM
  AsIs

UNION ALL

SELECT
  Sub,
  Origin,
  ''
FROM
  AsIs
;

The first half of the query just lists the current contents of the AsIs table. The second half appends the same rows but this time putting the Sub column at the same position as PN in the first query and specifying an empty string in place of the original Sub.

The column names of the final resulting set are defined by the names that the first SELECT is returning. So, Sub values of the second SELECT will be returned as PN in the output and the empty strings as Sub – that is, for your example the output will look like this:

PN      Origin  Sub
------  -----   ------
11111   List1   22222
98765   List2   123456
22222   List1
123456  List2


error when starting up the mongodb cluster

Without knowing what is content of /data/mongodbtest/shard1/mongo.conf, it's look like you don't have --fork (in config file processManagement.fork:true) set up. Parameter moves the process to background.

BTW, what is reason to --maxConns parameter?


SQL Server 2016 -number of SQL agent replication jobs(transaction log repl)

Weird. Never had that many agent jobs running replication before. Do you have a dedicated distributor, or is it all running form the publisher?


Irrespective of Recovery model,shrinking wont help you if there is an active transaction or due to the  reasons mentioned here

Transaction log space will be reused in all recovery models irrespective of Size shown in file Explorer,you may still have free space..

Here is what MSDN has to say about Log space reuse..

  Log truncation frees space in the log file for reuse by the transaction log. Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files.

How the log space will  be reused is different in different Recovery Models

1.In simple recovery Model,Log Truncation will happen after checkpoints which occur due to many factors as discussed here
2.In Full Recovery Model,TLOG space will  be reused only with Backups of Tlog

So even in simple recovery model,Your log space  has grown huge which means there is some thing which is preventing logspace reuse..so changing to Full Recovery Model also won't help unless you troubleshoot what is the cause..

You can use below query to see what is Preventing TLOG space reuse..

select log_reuse_wait_desc from sys.databases

Further shrinking Log file is bad for many reasons some of them include
1.file growth operations are expensive
2.Instant file Initialisation wont help for log files

Further Readings:
https://www.red-gate.com/library/sql-server-transaction-log-management


Your tblPair (Please tell me this is a classroom assignment. I would shutter to think business operational databases had tables names prepended with "tbl"!) is almost a good intersection table which is what is normally used to connect two entities in a relationship like the one you describe.

A "pair" is made up of two Birds, one male and one female. The dates when the pairing was made and ended and the cage the pair are located are tracked.

Here are some of my impressions of your schema. Some are outside the scope of your question, but may affect the ultimate resolution of that question.

You seem to have simplified the schema to include just data that may illustrate the relationship you are asking about. That is good, but there doesn't seem to be any purpose for RingNumber. And RingNumber would appear to be unique so why is there a RingNumberID? Did you simplify away their purpose? So let's just use BirdID for the duration and stipulate that it is an integer.

Let's also simplify the SexID as a simple char(1) field containing either 'M' or 'F'. Here is the new Birds table:

create table Birds(
  ID         int       auto_generate primary key,
  SexID      char( 1 ) check( SexID in( 'F', 'M' )),
  SpecieID   int       not null references Species( ID ),
  AddDate    date      not null, -- Date bird was added to database
  constraint UQ_Birds_FKAnchor unique( ID, SexID, SpecieID )
);

The CageNumber, RemovalDate and RemovalReason are not necessary for this discussion and probably shouldn't be in this table anyway.

What you want is a way to relate a male bird with a female bird of, I assume, the same species. This should be done in such a way to make it impossible to pair two male or two female birds or to include a bird in a new pairing if already in an existing pairing.

This could be done with an intersection table.

create table Pairs(
    MaleID     int  not null
    MaleSex    char( 1 ) check( MaleSex = 'M' ),
    FemaleID   int  not null,
    FemaleSex  char( 1 ) check( FemaleSex = 'F' ),
    SpecieID   int not null,
    Status     char( 1 ),
    PairedDate date default( NOW ),
    CageNumber int  not null,
    constraint UQ_Pairs_Male unique( MaleID ),
    constraint UQ_Pairs_Female unique( FemaleID ),
    constraint FK_Pairs_MaleBird foreign key( MaleID, MaleSex, SpecieID )
      references Birds( ID, SexID, SpecieID ),
    constraint FK_Pairs_FemaleBird foreign key( FemaleID, FemaleSex, SpecieID )
      references Birds( ID, SexID, SpecieID )
);

As you can see, very close to your existing table. You don't show the constraints of your table, and those are critical.

The first field must contain the ID of a bird defined as male ('M') in the Birds table because the second field may only contain an 'M' value. (This is also why there is a unique constraint which includes the ID field even though ID is unique all by itself.) So also with the third and fourth fields defining a female bird. The unique constraints guarantee no bird may be in more than one pairing and each pairing must be with the same species.

It looks like you want to be able to end the pairing, thus making the birds available for other pairing. In that case, add the Status field to the unique constraints. This will make it possible to have the same bird in different relationships but only once for each value of Status. I assume Status is something like "Ongoing" and "SoOverEachOther". But there could be more, like "GettingToKnow", "JustFriends", "HeadOverHeels" and so forth. This works either way.

This table defines a relationship between a male and a female bird of the same species. Each attribute in this table must define the relationship itself. Thus the single species identifier which applies to each bird, the date the pairing took place and the CageNumber identifying the honeymoon suite.

To display each pair, join the Pairs table twice with the Birds table to get the details of each bird. Rather simple really.


Select query not giving result for inner query values

The problem is that:

select string_to_array(trim(mt1.id, '[]'), ',')::numeric[]

does not return an array, it returns a set of arrays (even though it's just a single row - it's still a set)

If you are sure that your sub-query always returns a single row, you can simply cast the result of the select to an array:

select mt.val
from mytable mt
where mt.id = any ((select string_to_array(trim(mt1.id, '[]'), ',')::numeric[]
                   from mytable1 mt1
                   where mt1.id1 = 1003)::numeric[])

You actually don't need the cast twice, so the above can be simplified by removing the inner cast:

select mt.val
from mytable mt
where mt.id = any ((select string_to_array(trim(mt1.id, '[]'), ',')
                   from mytable1 mt1
                   where mt1.id1 = 1003)::numeric[])


How to sum the values of a json column filtered with regex?

The immediate reason for the error message is that the data type json has no equality operator. See:

How to query a json column for empty objects?
How to remove known elements from a JSON[] array in PostgreSQL?

You have:
...
group by
    A.name,
    B.products  -- type json!?

You can do that, using jsonb instead of json, where an equality operator is defined. But do you really want to group by B.products? (Same JSON documents?) Maybe you meant to write B.products->>'status' (Same status?) Or just GROUP BY A.name?
Aside: there may also be a simpler way to extract numbers that with REGEXP_MATCH(). You would have to define possible values B.products->>'status' and disclose the exact intention of the expression.
If you are at liberty to do so, it's typically best to store numbers in a separate key or even separate table column ....
jsonpath query in Postgres 12 or later
Your added sample values suggest you might be able to use jsonpath in Postgres 12 or later. Based on jsonb (not json).
Note: this is a proof of concept. If possible, normalize the table design and store numbers in a dedicated table column. Much simpler and more efficient.
Index
jsonpath operators can also be supported with a (default) jsonb_ops GIN index. I narrow down the scope with the expression products->'times':
CREATE INDEX products_times_gin_idx ON products USING gin ((products->'times'));

Index only helps for selective queries where not most rows have to be processed anyways!
Basic query to filter qualifying rows with jsonpath
Can use above index.
SELECT *
FROM   products B
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")';

jsonpath expression explained:
$[*] ... look at each array element of outer nesting level
? ... run the following test
(@ starts with "Active: ") ... Does element value start with 'Active:'?
... unnest and return only qualifying JSON array elements
SELECT *
FROM   accounts A
JOIN   products B USING (identifier)
     , jsonb_path_query(B.products->'times', '$[*] ? (@ starts with "Active: ")') act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
;

...  get results as text
SELECT *
FROM   accounts A
JOIN   products B USING (identifier)
     , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
;

See:

How to turn JSON array into Postgres array?

... and aggregate the number part
Arriving at your final query:
SELECT A.name as product, sum(right(act::text, -8)::float)  -- -8 = length('Active: ')
FROM   accounts A
JOIN   products B USING (identifier)
     , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
GROUP  BY 1;

db<>fiddle here
Related:

Update all values for given key nested in JSON array of objects


cypher query to find Employee nodes who have all the Skills

Aside: Your Cypher query uses node labels and relationship types that do not match your illustrated data model. So, I will ignore your illustration.

This is a slightly tricky way to get the employee nodes that have Has_Skill relationships to all profession nodes:

MATCH (p:profession)
WITH COUNT(p) AS pCount
MATCH (employee:employee)-[r:Has_Skill]->()
WITH pCount, employee, COUNT(r) AS sCount
WHERE sCount = pCount
RETURN employee;

The query gets the number of profession nodes and returns employees that have that number of skills.

By the way, you will get much better support for neo4j questions at stackoverflow.com.


It is the JDBC driver option we changed in DataGrip by default. But you can configure it the way you like:


Postgresql 9.3: Add Unique constraint using an existing unique index

You don't specify the column for the unique constraint. That's not necessary because the column list is defined through the index:

ALTER TABLE package ADD CONSTRAINT
     unique_package_id UNIQUE USING INDEX package_tmp_id_idx;

There is an example for that in the manual:
http://www.postgresql.org/docs/current/static/sql-altertable.html

You need to scroll down to the end of the page


You have to use the md5 hash as a part of the string so you have to concat it

CREATE TABLE people("id" varchar(50), "data" jsonb,"firstName" text, "lastName" text)

    update people set
    data = to_jsonb(data) || ('{"firstName": "' || MD5('PostgreSQL MD5') || '", "lastName": "' || MD5('PostgreSQL MD5') || '"}')::jsonb
    where id = 'b3c09005-7afb-4ad6-922d-76078875e59e';

db<>fiddle here
still md5 is old and not very secure, so you should use a more secure version


In 'psql', you can change the database you are connected to by using the \c metacommand.  So it would look like this:

CREATE DATABASE my_database;
\c my_database
CREATE TABLE my_table (
  id UUID primary key,
  data json
);

This works from '\i' included files.  When the \i file is done, you will be left in the new database unless you do something to change back to the old one.


How to store x,y graphs: (a) Using x,y columns for each point, or (b) to store the set of points as comma separated strings?

There's a third option: store column 2 and 3 together, so like

X1,Y1,X2,Y2,X3,Y3

If you're absolutely sure you never need to sort or filter on these coordinates, and rely on application logic to create, update and process them, it's fine to store them as a 'blob' in a single column.


how to resolve "InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table" in mysql

A guess:  A FULLTEXT index on that table is corrupted.

ALTER TABLE tablename ENGINE=InnoDB;

should rebuild the FT index, thereby fixing the problem.

If that is not sufficient, please provide SHOW CREATE TABLE; there may be some other clues there.


What does the repmgr the symbol "*" mean in the repmgr cluster status output?

It's not documented, but the code is here; it looks like * running is used for the running state for primaries and witnesses, but not secondaries. I'm not sure how intended that is; it might have just been a copy/paste of the code block that was used for primary; here's the relevant commit that introduced the 2nd * running status string.


Best practices for integer key types - mixed or all the same integer type?

It's a little subjective, and there's certainly nothing wrong with reduced complexity by defaulting to INT (since it covers most use cases and there's almost no tangible difference in performance) but generally if your tables are going to be so small in record count, using SMALLINT is a good idea from a best practice standpoint. SMALLINT gets you +-32767 rows (for a total of 65,534). So it is definitely sufficient for your small tables.
When deciding on the size for a datatype, generally planning for anywhere between twice as much up to a magnitude more of the max amount values you think is possible is a decent rule of thumb, depending on how confident you are in that rough estimation.
At the end of the day, if 25% of your tables have 1,000,000 rows in them, switching one of the smaller tables to SMALLINT instead of INT will only save you about 20 MB of space overall (which is peanuts). If you did it for all your small tables, then at most you're saving about half a gigabyte of space. There's probably better optimizations to go after currently and you can let sleeping dogs lie. Moving forward if you want to take SMALLINT into better consideration on new tables, it's not a bad idea.


Good layout of 3d point data for spatial queries in Postgres?

The composite type is clean design, but it does not help performance at all.
First of all, float translates to float8 a.k.a. double precision in Postgres. You are building on a misunderstanding.
The real data type occupies 4 byte (not 8). It has to be aligned at multiples of 4 bytes.
Measure actual sizes with pg_column_size().
SQL Fiddle demonstrating actual sizes.
The composite type real3d occupies 36 bytes. That's:
23 byte tuple header
1 byte padding
4 bytes real x
4 bytes real y
4 bytes real z
---
36 bytes

If you embed that into a table, padding may have to be added. On the other hand the header of the type can be 3 byte smaller on disk. Representation on disk is typically a bit smaller than in RAM. Doesn't make a lot of difference.
More:

Configuring PostgreSQL for read performance
Calculating and saving space in PostgreSQL

Table layout
Use this equivalent design to reduce row size substantially:
    Column     |           Type           |                       Modifiers
---------------+--------------------------+---------------------------------
 id            | bigint                   | not null default nextval(...
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 user_id       | integer                  | not null
 project_id    | integer                  | not null
 location_x    | real                     | not null
 location_y    | real                     | not null
 location_z    | real                     | not null
 radius        | real                     | not null default 0
 skeleton_id   | integer                  | not null
 confidence    | smallint                 | not null default 5
 parent_id     | bigint                   |
 editor_id     | integer                  |

Test before and after to verify my claim:
SELECT pg_relation_size('treenode') As table_size;

SELECT avg(pg_column_size(t) AS avg_row_size
FROM   treenode t;

More details:

Measure the size of a PostgreSQL table row


Reclaiming data file free space after deleting a large amount of data

First, are you sure the data growth won't happen again? If there's a realistic chance it will and the empty space doesn't hurt you, leave it, do NOT shrink the database.

However, if you're positive you want to reduce the data file size, then you should be aware of the pitfalls of data file shrinking:

It causes massive index fragmentation.
Affects performance WHILE it's running and AFTER it's run (one exception - the WITH TRUNCATEONLY option, if you're just removing free space at the end of the file)

I refer to the advice of Paul Randal from his article "Why you should not shrink your data files" (read the whole article to get a clear picture what happens with index fragmentation):

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the
  new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON
  syntax, to move the tables and remove fragmentation from them at the
  same time
  Drop the old filegroup that you were going to shrink anyway
  (or shrink it way down if its the primary filegroup)

  Basically you
  need to provision some more space before you can shrink the old files,
  but it’s a much cleaner mechanism.

  If you absolutely have no choice and have to run a data file shrink
  operation, be aware that you’re going to cause index fragmentation and
  you should take steps to remove it afterwards if it’s going to cause
  performance problems. The only way to remove index fragmentation
  without causing data file growth again is to use DBCC INDEXDEFRAG or
  ALTER INDEX … REORGANIZE. These commands only require a single 8KB
  page of extra space, instead of needing to build a whole new index in
  the case of an index rebuild operation.

  Bottom line – try to avoid running data file shrink at all costs!


How should I set up SQL Server Login permissions for access to website on a different server using Windows Authentication

In order to get the application pool account to be recognized by the SQL Server you'll have to configure the application pool to run under a domain account.  Then give this account the rights that it needs within the SQL Server instance.

If you are using Windows auth then your users will need read access to the folder which is hosting the website, otherwise they won't be able to read the files for the website.  This is normal. Just give the users of the website read only rights to the folder which hosts the website.


CentOS 7 was released on 2014-07-07.
PostgreSQL 9.3 was released on 2013-09-09. You would think it would have 9.3, but nope. CentOS 7 uses PostgreSQL 9.2 which was released on 2012-09-10 and hits End-Of-Life in September 2017.

Find the official instructions for using the non-CentOS repos here. PostgreSQL maintains it's own packages too. The official packages from PostgreSQL for CentOS are available here,

https://www.postgresql.org/download/linux/redhat/

Or just use something that updates more than once every 5 years -- like Debian.


Can SQL Server stop me from running an UPDATE statement without a WHERE clause?

Create a trigger on update (another on delete is probably a good idea).

CREATE TRIGGER [Table].[uPreventUpdateOops]
ON [Table]
FOR UPDATE AS
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;

     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats
         WHERE OBJECT_ID = OBJECT_ID('Table')
         AND index_id = 1)
     BEGIN
         RAISERROR('Cannot update all rows',16,1)
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO

Note: This only prevents the most obvious mistake. Please do not use this on a production server as you're bound to find a statement that will totally Bork your table but pass this simple test. Also, this runs quite slow on large tables, performance will be affected.


You can count distinct elements by running:

select count(distinct policy_id, client_id) from policy_client;

Another option would be to group by and count that:

select count(*) from (select policy_id, client_id from policy_client group by 1,2) a;

Run both version and see which one performs better on your dataset.

A very quick way but not totally accurate if you have a key on (policy_id and client_id) you can also check the cardinality of that index but that's an approximate not exact number.


Ensuring 1 to 1 mapping through a many to many table

First: you need somewhere a link table that stores a reference to both every product (or project) and its options' values. Let's call that one ProjectOptions, and let's assume it's structured like (ProjectId, OptionId, ValueForOption).

  The trouble I'm having is thinking how to ensure that each project lists every option for the product it's tied to.

You can have an ON INSERT TRIGGER on table project that, whenever you insert a new project, creates all the corresponding entries into the ProjectOptions table.

  Adding a new option should require updating all the projects.

Actually you need to add entries to your ProjectOption table, not just update the Project table itself.

You can have another ON INSERT TRIGGER on table Options. Whenever a new Option is inserted, for a certain ProductId, look for all projects having that ProductID, and add the corresponding (ProductId, OptionID, DefaultValueForOptionID /* or NULL */) entries into your ProjectOption table.

Apart from doing this via triggers, you may consider doing the same at the application layer. If the user inputting the new options needs to perform something (like setting specific values for existing projects), this cannot be handled at the database layer.


Insert new rows when item is added to another table SQL

If you want to avoid a trigger you can use the OUTPUT clause and a table variable:
drop table #pPeriod
drop table #dimProject
drop table #factProject

create table #dimProject (project int, status varchar(10))
declare @out table (project int, status varchar(10))
create table #factProject ( project int, status varchar(10),
                            pPeriod datetime2, vValue varchar(10))
create table #pPeriod( pPeriod datetime2)

-- you will need more rows here
insert #pPeriod values ('20210501'),('20210601'),('20210701'),('20210801'),
                       ('20210901'),('20211001'),('20211101'),('20211201')

insert into #dimProject (project, status)
output
  inserted.project,
  inserted.status
into @out
(
  project
, status
)
values (1, 'A')

insert #factProject (project, status, pPeriod)
select o.project, o.status, p.pperiod
from @out o
cross join #pPeriod p
-- add a filter to join to todays date and fetch 12 months forward

select * from #dimProject

select * from #factProject


How to count all relationships in a database?

SELECT COUNT(*) FROM information_schema.tables WHERE table_type = 'BASE TABLE' --counts all tables
SELECT COUNT(*) FROM information_schema.referential_constraints --counts all FK relationships