How do you correct a corrupted index in Oracle?

Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:

[oracle@oca ~]$ rman target=/

RMAN> list faliure;

If there are failures with status OPEN listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:

RMAN> advise failure;

Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:

RMAN> repair failure;

After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure command in RMAN, and if not you can get another advice from DRA.

EDIT:

Since the recovery you performed didn't last long (elapsed time: 00:00:00), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.

In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG mode (check with select log_mode from v$database) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.


Shared logarch across multipe db2 HADR instances

We are using a HADR environment with only one standby. Our log files are shared using a simple NFS. That works fine for us, but the two servers are located next to each other.

I assume if a NFS share works with standby 30 km away depends on the network connection (speed and reliability) to the external location.

We are going to locate our primary and standby now in two separate buildings, but they are only a few hundred meters away. For this we will have two SAN systems that are mirrored, one in each building. So the transfer of the data will be handled by the SAN system transparent to the DB2 servers. The two building are connected by fibre channel cables.

We do this for security reasons as it would make no sense to have the SAN system located in building 1 together with the primary while the standy is located in building 2 but has to access the SAN in building 1. If the data center in building 1 is going down, the standby in building 2 will be of no use without SAN access.

How you should setup your environment highly depends on your requirements and goals. Do you want to accelerate the your database access or do you want to increase the security?


Delete rows in a table that has multiple FKs

If I got you right - you need to delete referencing rows prior to deleting the referenced rows.

begin transaction
DELETE Contenttasks
FROM contentdata
 where contentdata.contentid = contenttasks.contentid
and contentdata.htmldata LIKE '%DV95%'

Delete from contentdata where htmldata like '%dv95%'
Commit


MongoDb Sharding between replica and standalone server

Inserts need to be done via the mongos for splits to happen automatically.  In fact, all work should be done via the mongos, not directly to the primary on one of the sets.  That is why you have not seen any splits happen and your chunks are all still in one place.  In terms of details, I've written up some of the details about how splits happen in a previous answer.

Note that you can split manually via the mongos if you want to, you do not have to wait for the automatic splitting to happen.


Using group by with left join and IN yields empty result

Not sure about your question but are you after this kind of result ?

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |

If yes, then you need to use a SUM(IF(ii.invitee_id IS NULL, 1,0))

SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
WHERE

  i.`event_id` IN(18569,18571,20000)
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Full answer :

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE invitees
    (`id` int, `event_id` int, `invitation_sent` int)
;

INSERT INTO invitees
    (`id`, `event_id`, `invitation_sent`)
VALUES
    (1, 18569, 1),
    (2, 18569, 1),
    (3, 18569, 1),
    (4, 18569, 1),
    (5, 18571, 1),
    (6, 18571, 1),
    (7, 10000, 0),
    (8, 10000, 0),
    (9, 20000, 1)
;

CREATE TABLE events
    (`id` int)
;

INSERT INTO events
    (`id`)
VALUES
    (18569),
    (18571),
    (10000),
    (20000)
;

CREATE TABLE invitee_information
    (`invitee_id` int)
;

INSERT INTO invitee_information
    (`invitee_id`)
VALUES
    (9),
    (4)
;

Query 1:

-- Your one by one query
SELECT i.`event_id`, COUNT(i.id) c
FROM invitees i
LEFT JOIN invitee_information ii
ON ii.invitee_id = i.id
WHERE ii.invitee_id IS NULL AND i.`event_id` = 18571 AND i.`invitation_sent` = 1

Results:

| event_id | c |
|----------|---|
|    18571 | 2 |

Query 2:

-- The IN only for count at least 1
SELECT i.`event_id`,  COUNT(i.id) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
WHERE
   ii.invitee_id IS NULL
   AND
  i.`event_id` IN(18569,18571,20000)
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |

Query 3:

-- The count for every event asked
SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
WHERE
  i.`event_id` IN(18569,18571,20000)
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |

Query 4:

-- If you want the count for every event whatever invitation_sent
SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    10000 | 2 |
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |


mysql performance tuning innodb_buffer_pool_size and warming the cache (select * vs select count(*))

Warmup

If you have a secondary index on the table, SELECT COUNT(*)... will use that.  So the first "warming" fetched that index.  The second "warming" fetched the data; note that the hump is higher, since the data is bigger?

That is, COUNT(*) works only for an InnoDB table with no secondary keys.

This might work:  SELECT COUNT(*) FROM t FORCE INDEX(PRIMARY);

You are overdue for an upgrade.  With 5.6.3, there is innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, which is better than your warmup script; it efficiently restores the buffer pool to whatever was in it.

Buffer pool size

5GB of data; 32GB of RAM --> 8GB sounds like a good buffer_pool_size:

Less than 5GB probably leads to extra I/O (depending on "working set size").
8GB leaves enough room on top of the 5GB for the "Change Buffer" and other overhead.
8-25GB -- the rest would be idle.
over 25GB -- threaten to do swapping, which would be really bad for performance.


Why wouldn't I put the single preferred option into its own table, instead of having a 'Preferred' column?

It depends.  There are cases whether one design or the other are satisfactory.

If you are doing a lot of queries only on the preferred or default entry, then the second option seems better.

If, however, you are normally doing lots of queries for all the entries, regardless of whether they are preferred/default or not, the first option is a little easier to set up and manage.

And if most of your queries want to retrieve all the entries except the preferred/default one,  you'll find the first option a lot easier.  I can't think of such a case off the top of my head.

And, in most cases, these tables are so tiny that performance considerations will be secondary.


Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

You can't guarantee contiguous or consecutive identity values in SQL Server

An insert of multiple rows won't guarantee this. See Do Inserted Records Always Receive Contiguous Identity Values.
The current identity value stays incremented on rollback (either explicit ROLLBACK or implicit due to a CONSTRAINT error).

Also:

Loading a temp table doesn't guarantee consecutive values. Saying that, this is harder to prove, and works in practice (as many folk rely on it), but be warned...

You have to adapt to gaps in your numbering, however generated.


multiple count and use subsctract function in one query

Without seeing the exact error message returned, I can't be 100% certain, but there are two glaring errors:

Is the column name Department or Dept?  This does not seem clear.
You cannot reference tot1 unless it is first derived in a CTE or sub-select (derived table).
The subquery sum(select count(status_mcu) where status_mcu='done') does not have a FROM clause.  Furthermore, I'm not sure what purpose where aggregating a high-level aggregate is necessary/desired.
As a general critique, you're hitting the same table over and over again which is not optimal from a performance standpoint.

Start with this and see if it returns the desired results (if it doesn't, you'll need to clarify your question to provide the proper logic):
SELECT
  mcu.Dept
 ,COUNT(mcu.Id) AS Tot1
 ,SUM
    (
      CASE
        WHEN mcu.status_mcu='done' THEN 1
        ELSE 0
      END
    ) AS tot2
 ,COUNT(mcu.Id) -
    SUM
      (
        CASE
          WHEN mcu.status_mcu='done' THEN 1
          ELSE 0
        END
      ) AS tot3
FROM
  mcu mcu
GROUP BY
  mcu.Dept


Postgres - How to get all the values from a field that it repeats on every json from an array?

You didn't provide any code, so I will create a sample table with one row and some queries to extract the information you need.

CREATE TABLE test (myfield jsonb);

Load data:

INSERT INTO test (myfield) VALUES ('
[
  {
    "text": "JIMIN",
    "indices": [
      16,
      22
    ]
  },
  {
    "text": "PUMA",
    "indices": [
      23,
      28
    ]
  }
]
'::jsonb);

To extract a specific item, let's say the text field of the second array item:

SELECT myfield#>>'{1,text}' AS text FROM test;

This will output:

  text
 ------
  PUMA
 (1 row)

In order to extract all the text fields inside the array, a possible solution is:

SELECT (jsonb_array_elements(myfield)::jsonb)->'text' AS text FROM test;

The output is:

  text
---------
 "JIMIN"
 "PUMA"
(2 rows)

Obviously this works with many records: each array item will be ported to one row for each array. This query is compatible with PostgreSQL 9.5. Look at the official docs to create a more efficient or versatile query ( https://www.postgresql.org/docs/9.5/static/functions-json.html ).


Drop privilege to tables only, Not to database

Do GRANT DROP ON TABLE thedatabase.* TO user1.
The MySQL Reference Manual is a great place to look for this kinds of things.


How can I insert into a Destination Table from multiple tables concurrently..?

This article is for you then.

One quote directly in regards to your question of multi-threading.

  The package property that determines how many executables can run in parallel is MaxConcurrentExecutables, as shown in Figure 2. The default is -1, which means that the control flow engine will use the number of logical processors on the server plus 2. For example, if you’re executing the package with the default setting on a quad-core server, you can have up to six executables running in parallel.

This article will talk about avoiding blocking and locking and should give you enough examples and ideas to give you your own questions to research on. Evaluating what types of locks and blocks will occur and what level of isolation you need will help you avoid blocking and locking.

Deadlocking does not sound like a problem you will encounter based on what you have typed. Here's a good explanation on how it occurs.


Migrate existing SQL Server database file to smaller files

Just adding files to the existing file group will not redistribute the existing data.

I would go the route @Max is suggesting and ensure that the new files you are creating are the same size to maximize the proportional fill feature. And before you begin take a full backup.

Once you've migrated all of the tables, indexes, etc over to the new file group you should be able to shrink the mdf down to be under the 512GB point or smaller if you are not going to use the default file group anymore.

At that point take another full backup and then restore that to the new disks with the MOVE TO option in the restore command. This will let you take the new ndf files in the new file group and put them across the SSDs.

Depending on how you are managing the database you might want to set the new file group to be the default for all of the new objects that are created.  Otherwise they will still default to the old file group and since it only has one file in it the objects/data in it won't benefit from the proportional fill feature that SQL Server has.

Moving file groups reference


Calculating the elapsed time of Oracle Database Startup operation

[oracle@o71 ~]$ time sqlplus -s / as sysdba<<EOF
> startup
> exit
> EOF
ORACLE instance started.

Total System Global Area  536867640 bytes
Fixed Size                  8659768 bytes
Variable Size             322961408 bytes
Database Buffers          197132288 bytes
Redo Buffers                8114176 bytes
Database mounted.
Database opened.

real    0m14.578s
user    0m0.014s
sys     0m0.016s
[oracle@o71 ~]$


How to store and work from a Postgresql database on an external hard drive?

I suggest you to create a new cluster in your external drive. You can use initdb
to create a new PostgreSQL database cluster.
Here is a sample command;
C:\Program Files\PostgreSQL\13\bin\initdb --pgdata="F:\MyProject\pgdata\13\main"

After that you even can register your new cluster as a new postgresql server service by using pg_ctl like the one below;
C:\Program Files\PostgreSQL\13\bin\pg_ctl register --pgdata="F:\MyProject\pgdata\13\main"


would you confirm what load you are running during this 30 mins of database uptime?  this is actually accumulative values of the total opened tables and a smaller table open cache or creating temporary tables can increase this value.

paste values of CREATED_TMP_TABLES, TABLE_OPEN_CACHE.  Also paste total ram available for this DB on your machine.

Thanks


This error occurs because the transaction log becomes full due to LOG_BACKUP. Therefore, you can’t perform any action on this database, and In this case,

  the SQL Server Database Engine will raise a 9002 error.

To solve this issue, you have to do the following:

Take a Full database backup.
Shrink the log file to reduce the physical file size.
Create a LOG_BACKUP.
Create a LOG_BACKUP Maintenance Plan to take backup logs frequently.

  Note: The Shrink operation effects on SQL Server Performance during executing shrink command. it also causes index fragmentation and can slow the performance of queries that search a range of the index.

So, it’s recommended before goes live you should prepare LOG_BACKUP Maintenance Plan to back up the log file frequently to avoid the shrink operation on Production.

  For more details check The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

Hope this helps you


What does Then 1 else 0 mean in a CASE expression used with an aggregate function?

If column1 contains the value value1 then the CASE expression will return 1, and SUM() will add 1 for that row. If it doesn't, the CASE expression will return 0, and it will add 0 for that row. This is a way to count how many rows have value1 in column1, but there are other ways to do this too, e.g. on 2012+:

SELECT COUNT(IIF(column1 = 'value1', 'truish', NULL))

On earlier versions:

SELECT COUNT(CASE WHEN column1 = 'value1' THEN 'truish' END)

(You don't need the ELSE here because, unlike SUM, COUNT ignores NULL.)

And I updated the value 1 to be a string to demonstrate that this has nothing to do with boolean.


3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows.

MySQL doesn't have Tail Call Optimization. Without TCO, each call will take more memory, and get a different stack. So implementing it in this fashion is at the very best insecure because you can't ensure through a constraint that the query won't result in SQL dying. Case and point, retrying the query in such a case will result in another dead backend. So now you gotta do something other than retry. Any answer that uses this method is pretty misguided.

So what do you do? Upgrade to MySQL 8 beta. That has Recursive CTEs. This is called a hierarchical query, or even better -- migrate to PostgreSQL which is a superior database that has had this feature for like a decade.

For more information, see the hierarchy tag.


Community wiki answer:

You can't restore without creating the log file that was there when the database was backed up, so no.

It makes sense to have the reporting system in simple recovery (and perhaps even in read only) but in order for that log file to be smaller than the production system (assuming it doesn't fit there and you can't restore the big log file and shrink it), you'll have to restore it somewhere else first, shrink the log file there, and take another backup.


Unexpected result from query based on old column value

I would try this one:

UPDATE
    table t1
SET
    upd = 1,
    b1 = GREATEST(5, a1)
WHERE
    b1 <> GREATEST(5, a1) ;

No need for "copying" the table or using the (not 100% sure) assumption that MySQL updates the columns in a specific order.

The answers in your link are about a much more difficult to do (in MySQL) operation, i.e. swapping the values of 2 columns.

If you want to update multiple columns and set the upd=1 column if at least one of the other columns is updated, you can use a simple OR in the WHERE clause:

UPDATE
    table t1
SET
    upd = 1,
    b1 = GREATEST(5, a1),
    b2 = GREATEST(7, a2),
    b3 = <whatever>
    ---
WHERE
   ( b1 <> GREATEST(5, a1)
  OR b2 <> GREATEST(7, a2)
  OR b3 <> <whatever>
  ---
   ) ;


Passing multiple value for a parameter in my sql

If I understand your intent, I might write the query something like this:

SET @City = '"London","Lyon","Kln"';
SET @sql = concat('SELECT * FROM Invoices WHERE City IN (',@City,')');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


grant select in just one DMV instead of grant VIEW SERVER STATE

I think you can wrap the select against the specific DMV in a stored procedure and then use Execute As to elevate the permissions.
whether you choose to sign the SP with a certificate, execute as SA or as Owner is something you will need to determine according to your own security needs.


It would be something like this:

DECLARE
    cur SYS_FERCURSOR;
    curid NUMBER;
    desctab DBMS_SQL.DESC_TAB;
    colcnt NUMBER; -- total number of columns
    res NUMBER;

    namevar VARCHAR2(4000);
    numvar NUMBER;
    datevar DATE;
    ... more IF NEEDED
BEGIN

    OPEN cur FOR 'SELECT column1, column2, **piece_of_query_with_dynamic_columns**, column3 FROM table1';
    curid := DBMS_SQL.TO_CURSOR_NUMBER (cur);

    DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
    FOR i IN 1..colcnt LOOP -- loop over all columns
        IF desctab(i).col_type = 1 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar);
        ELSIF desctab(i).col_type = 2 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
        ELSIF desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
        ELSIF desctab(i).col_type = ... THEN
            .......
        END IF;
    END LOOP;
    -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
           IF (desctab(i).col_type = 1) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
              UTL_FILE.PUT(v_file, namevar|| ',');
           ELSIF (desctab(i).col_type = 2) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
              UTL_FILE.PUT(v_file, numvar|| ',');
           ELSIF (desctab(i).col_type = 12) THEN
              DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
              UTL_FILE.PUT(v_file, datevar|| ',');
           ELSIF ....
              ...
           END IF;
       END LOOP;
       UTL_FILE.NEW_LINE;
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);

END;

In order to trim the last comma you may use

IF i < colcnt THEN
    UTL_FILE.PUT(v_file, namevar|| ',');
ELSE
    UTL_FILE.PUT(v_file, namevar);
END IF;


Is there any special meaning for numbers in braces like {0} or {1} in LDIF files of OpenLDAP?

It's for list ordering: Attributes or entities with the same key are sorted using the number in the braces. Especial need for access control lists, as the ordering of the rules matters, e.g.

olcAccess: {0}to attrs=userPassword,shadowLastChange
    by self write
    by dn="cn=admin,dc=mobikat,dc=net" write
    by anonymous auth
    by * none
olcAccess: {1}to *
    by self read
    by dn="cn=admin,dc=mydomain,dc=net" write
    by dn="cn=readonly,dc=mydomain,dc=net" read
    by * none


There's no real difference there, but when you start using DATETIME2 values, or functions that return DATETIME2 values, you'll get errors.

SELECT SYSDATETIME() - 1 AS [Incompatible]

  Msg 206, Level 16, State 2, Line 17 Operand type clash: datetime2 is
  incompatible with int

For these, you have to use date math functions.

SELECT DATEADD(DAY, -1, SYSDATETIME()) AS [Compatible]

Aaron Bertrand speaks about this issue briefly in his Bad Habits to Kick series.


Extracting an an integer value from an xml column

You should use the value() method of the XML data type.

If your XML structure looks like this:

<DLR>
  <siteID>12345</siteID>
</DLR>

Your query would be

select msg.value('(/DLR/siteID/text())[1]', 'int') as DOMAIN
from dbo.YourTable

But it does however not look like you have an XML column at all since msg like '<DLR%' is invalid against XML columns.

Then you need to either modify your table so the column is XML or you can cast to XML in your query.

select cast(msg as xml).value('(/DLR/siteID/text())[1]', 'int') as DOMAIN
from dbo.YourTable


Galera Replication error: log block numbers mismatch

I finally have solved this problem.

I found an article on launchpad that said to use this command to see if the problem was IO:
innobackupex --user=<username> --password=<password> --stream=tar -- ibbackup=/usr/bin/xtrabackup /tmp >/dev/null

This failed to, so I started thinking IO was not at fault.

I played around with this command and found this one to complete:
innobackupex --user=<username> --password=<password> --stream=xbstream --parallel=15 /tmp >/dev/null

This also showed that my ulimit was not high enough, so I had to set this higher for the backup to complete.

To force innobackupex to use xbstream instead of tar for the --stream option I but this in my.cnf on both of my galera servers:
[sst]
streamfmt=xbstream


Why is my query taking so long if my field is correctly indexed?

If you have a 5400 RPM hard drive, this is almost exactly how much time you would expect it to take to read 8942 blocks if there was no effective read-ahead or read re-ordering (as implemented by the kernel, with some collaboration from PostgreSQL).  You can confirm if the time goes to disk reads by turning track_io_timing on before doing the EXPLAIN (ANALYZE, BUFFERS).

Even with a single 5400 hdd, I've seen it get increased performance, up to 2x, by cranking up effective_io_concurrency to a large value, like 50 or 100.

If you repeat the query, is it faster the second time?  Is your goal to make this work from a hot-cache or a cold-cache?  How much RAM do you have?


Except in Windows-land there's no difference between a "Server" and a "PC" other than typical hardware choices and installed software.

PostgreSQL follows that, and doesn't care in the slightest what you're installing it on. Install it on Win2k8 the same way you would on any Windows desktop OS.

The only possible wrinkle is Group Policy and Active Directory domain membership, but those are also factors in domain-member PCs. So long as your group policies don't restrict the NETWORKSERVICE, and so long as you install PostgreSQL to use the NETWORKSERVICE (default in 9.2 and later) you'll have no issues, it'll be just like any other local machine install.

If you want to run PostgreSQL under an Active Directory user account, or an account with more restrictive group policies, then you might have to set up the service yourself, but you'll have a capable sysadmin to do that if you're in the kind of environment where it might be necessary.

Client configuration depends on what you're connecting with. You may want the ODBC driver psqlODBC, you may want PgJDBC, you might want the Python driver psycopg2, etc etc. The general idea is: install desired driver, use application. The only one that takes more setup is ODBC if used with a predefined DSN, and that's the same for PostgreSQL as any other ODBC app.

To configure the server to accept connections from clients you must:

Ensure the server's firewall allows inbound connections to postgres.exe and/or port 5432 (default)
Set up pg_hba.conf to allow PostgreSQL to accept remote connections (see the documentation)
Create PostgreSQL user accounts for the users


Sql server, altering column to smaller size

Changing from DATETIME to DATE changes the binary representation of the value, so the overhead will be similar to the one when increasing in size.

IIRC the only change that can be metadata only is one that doesn't change the binary representation, like changing from VARCHAR(100) to VARCHAR(50), and some may not even require scanning the data to make sure it fits, such as changing from VARCHAR(50) to VARCHAR(100).

Any change in data type that is not metadata only, will require physical update of the rows, and will cause a similar overhead as the entire row needs to be logged.


Creating a row for each value in a comma-separated string

First, create a string splitting function:

CREATE FUNCTION dbo.SplitString
(
    @List      nvarchar(max),
    @Delimiter nvarchar(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN
  (
    WITH x(x) AS
    (
      SELECT 1 UNION ALL SELECT x+1 FROM x
      WHERE x < (LEN(@List))
    )
    SELECT Item = SUBSTRING(@List, x,
      CHARINDEX(@Delimiter, @List + @Delimiter, x) - x)
    FROM x WHERE x <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, x, 1) = @Delimiter  );
GO

Then you can simply cross apply your source with the function:

DECLARE @x TABLE(PartNumber nvarchar(255), FileName nvarchar(max));

INSERT @x(PartNumber, FileName) VALUES
('PN0001-01','FOO'),
('PN0002-01','BAR'),
('PN0003-01','FOO,BAR'),
('PN0004-01','BAR,FOO,FOB');

-- INSERT dbo.ViewName(col1,col2)
SELECT x.PartNumber, f.Item
FROM @x AS x
CROSS APPLY dbo.SplitString(x.FileName, N',') AS f;

In SQL Server 2016, you can use the native STRING_SPLIT() function.


Which accented characters are considered the same?

Case or accent sensitivity behavior is defined by the collation you are using.

The full list of comparable characters in a given collation can be found in collationcharts where you find the MySQL list of collations

If you find your collation in that list you will see a chart of comparable characters.


No, is not a good idea. There is a possible security issue underneath when using sa as owner of the job.

All jobs are ran through SQL Server Agent and it sets the security context for job execution based on the role of the user owning the job. So, if you use sa then you know what it will happen. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account. The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.

So, using sa as the job owner will cause all T-SQL job steps to execute as the SQL Agent service account, which is a system administrator account. A better option is to set a non-sysadmin account as the job owner, and explicitly grant only the required database permissions to this account.

So, what I would do is to create a specific user on the DOMAIN and give that user the needed rights to execute those jobs. And that user should not be a person in the company as you could have same issue in the future, if that person goes away and the account is closed...same error again.


Automatic Translation when Converting Unicode to non-Unicode / NVARCHAR to VARCHAR

Why does SQL convert Unicode 9619 to ASCII code 166?

SQL Server is not employing any special custom logic here; it is using standard operating system services to perform the conversion.

Specifically, the SQL Server type and expression service (sqlTsEs) calls into OS routine WideCharToMultiByte in kernel32.dll. SQL Server sets the input parameters to WideCharToMultiByte such that the routine performs a 'quick translation'. This is faster than requesting a specific default character be used when no direct translation exists.

The quick translation relies on the target code page to perform a best-fit mapping for any unmatched characters, as mentioned in the link Martin Smith provided in a comment to the question:

  Best-fit strategies vary for different code pages, and they are not documented in detail.

When the input parameters are set for a quick translation, WideCharToMultiByte calls OS service GetMBNoDefault (source). Inspecting the SQL Server call stack when performing the conversion specified in the question confirms this:


Are Availability Groups feasible for 4 TB of data?

Nismo,

It's not the SIZE of the database that matters (no jokes intended), it's the rate of change coupled with the infrastructure available. For example, a relatively static database might perform poorly on 1Gb connection on an overloaded switch with 5400 RPM sata drives.

If the rate of change (aka look at your log flush bytes) is less than around 200 MB/sec and you have very fast storage (or a ton of front end cache) and extremely low latency networking then I would say you'll be fine.

Let's go over your questions now:

  1.Is this size okay for Availability Groups (we are looking to have 2-3 replicas)?

It's not about the size, but the infrastructure. So, yes that's fine.

  2.If network dies for 1hour, how fast can we get the replica in sync?

How fast does an unladened swallow fly? Seriously though, there is no way for us to tell you. You'll need to take into account the rate of log generation + latency + blocked redo + sync/async status.

The better question to ask is, "Do I have enough space on my primary for my transaction log to NOT TRUNCATE for an hour?"

  3.Do you guys have any consideration I should be following before using Availability Groups?

I would consider taking a serious look at SQL 2016 if these are your concerns. Parallel redo (coming soon), better network utilization, distributed AGs, direct seeding, etc.

If you're intent on using 2014, then the best recommendation is to make sure the server hardware and infrastructure are up to the rate of change you'll be creating. Additionally watch when you do DDL against objects as it could pose potential problems with redo.

If you're worried about the replicas catching up in time, just remove the replicas from the AG, restore the transaction logs to them and then bring them back into the AG. Remember, your primary won't be able to have the log file re-use any VLFs while an AG is "behind" as it'll only be able to mark for re-use up to that LSN.


You can use a CASE expression inside the COUNT aggregate funciton.

SELECT
    COUNT( CASE WHEN position > 1 THEN key_id ELSE NULL END ) AS GT1,
    COUNT( CASE WHEN position > 2 AND position < 5 THEN key_id ELSE NULL END ) AS GT2LT5,
FROM
     proj_reports

The COUNT aggregate does not count NULL values, so that's why it works.

Here is a SQL Fiddle that demonstrates the concept.


Optimising expensive join/subquery by filtering with conditionals

Not sure why authed is needed at all. What does:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN LATERAL (
     SELECT json
     FROM view_dataset
     WHERE id = cond.id
) dataset
    ON is_owner and is_newer;

by you? I also agree with the comment made by a_horse_with_no_name. LATERAL can be a tremendous help for pushing predicates down to the base tables in special cases, but it is just a sub-query in disguise so in most cases it makes more sense to do an ordinary join. Do also try:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN view_dataset wd
    ON wd.id = cond.id
   AND cond.is_owner
   AND cond.is_newer;

EDIT. table valued function

CREATE FUNCTION get_view_dataset(int,bool)
    RETURNS setof view_dataset AS '
        SELECT *
        FROM view_dataset wd
        WHERE wd.id = $1
          AND $2;
    ' LANGUAGE SQL;

and then use that function in your query as:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN get_view_dataset(cond.id, cond.is_owner AND cond.is_newer);

All untested.


MySQL Config for server with 8gb RAM and 4 cpus

I'd agree with comment that asks for "purpose" of the mysql instance though these days you get decent configuration in-built provided with my.cnf.

You should look to use them, my-large/huge/medium.cnf etc...
I see you have query cache and this link will help you understand best value for it.

Checkout this blog post for configuring few important parameters.
Finally I'd also like you to review this config wizard.

Again... none of this will give you exact and perfect config... Be ready to monitor and make according changes.


You mentioned system copy. Whenever you change the ipaddress or hostname, DBConsole goes haywire, and even the official solution is to recreate it as described in:

How to configure Dbconsole After the Ipaddress or hostname of the Machine has Been Changed or if the hostname ipaddress is going to be changed? (Doc ID 1333938.1)

The usual steps to do this:

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create


Rename primary key constraint in Teradata

no, renaming is not possible in Teradata but as already stated you can drop the constraint and recreate it


OUTER JOIN Works in one direction but not the other

There are two (plus 1) - seemingly independent - relationships between models and locations. One relationship is in model_locations and the other two in models.

I find both your queries to be incorrect because they don't examine the relationships independently. If there is a relationship (between a model and a location) only in one of these two tables, the query will not return any. Using LEFT joins might be possible but seems inappropriate, too.

I'd use simple OR conditions with EXISTS subqueries or a UNION:

Final all locations of a certain model (17):

SELECT
    loc.*
FROM
    locations AS loc
WHERE EXISTS
      ( SELECT 1
        FROM model_locations AS ml
        WHERE ml.model_id = 17
          AND ml.location_id = loc.id
      )
   OR EXISTS
      ( SELECT 1
        FROM models AS mod
        WHERE mod.id = 17
          AND loc.id IN (mod.location_from_id, mod.location_to_id)
      ) ;

Final all models of a certain location (11):

SELECT
    mod.*
FROM
    models AS mod
WHERE EXISTS
      ( SELECT 1
        FROM model_locations AS ml
        WHERE ml.location_id = 11
          AND ml.model_id = mod.id
      )
   OR 11 IN (mod.location_from_id, mod.location_to_id)
 ;


When shrinking log files what difference does TRUNCATEONLY make?

I see no difference when using TRUNCATEONLY when compared to regular SHRINKFILE on the log file. Here's the script I built for testing (2016 dev edition, note the DROP IF EXISTS syntax):

USE master
GO

DROP DATABASE IF EXISTS trunctest
CREATE DATABASE trunctest
 ON  PRIMARY
( NAME = N'trunctest', FILENAME = N'C:\SQLTest\DATA\trunctest.mdf' , SIZE = 8MB , FILEGROWTH = 8MB )
 LOG ON
( NAME = N'trunctest_log', FILENAME = N'C:\SQLTest\LOGS\trunctest_log.ldf' , SIZE = 8MB , FILEGROWTH = 8MB )
GO

ALTER DATABASE trunctest SET RECOVERY SIMPLE
--or use the below two lines if you want to test FULL recovery
--ALTER DATABASE trunctest SET RECOVERY FULL
--BACKUP DATABASE trunctest TO DISK = 'NUL'
GO

USE trunctest
GO

--Log files at creation
DBCC LOGINFO

--Let's make a table and a large transaction
CREATE TABLE fluff
(junk char(8000))

BEGIN TRAN
INSERT fluff
SELECT TOP 1000 REPLICATE('a',8000)
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2

--pre commit
--DBCC LOGINFO

--post commit
COMMIT
--DBCC LOGINFO

--CHECKPOINT to clear inactive VLFs
CHECKPOINT
--Or log backup if testing FULL
--BACKUP LOG trunctest TO DISK = 'NUL'

--DBCC LOGINFO

--Now let's shrink and check
DBCC SHRINKFILE(trunctest_log)
--Run this instead on round 2
--DBCC SHRINKFILE(trunctest_log, TRUNCATEONLY)
GO

SELECT size*8.0/1024 AS LogSizeInMB
FROM sys.database_files
WHERE type = 1

DBCC LOGINFO


SQL Server : how to disable connections to a linked server?

I found the solution that I wanted:

-- how to disable a linked server

--check the existent linked servers
SELECT * FROM SYS.SERVERS
WHERE NAME = 'SWLON1'

-- Disable data access
EXEC sp_serveroption @server = 'SWLON1'
      ,@optname =  'data access'
      ,@optvalue =  'FALSE'

-- ABOUT procedure sp_serveroption
--https://msdn.microsoft.com/en-us/library/ms178532.aspx

-- the idea as how to disable a linked server
--http://www.sqlservercentral.com/Forums/Topic1038823-146-1.aspx


is there a way to connect to mysql using oracle sqlplus instant client

The answer to your question is, no, you cannot connect to MySQL using the current oracle instant client.


Getting Random Sample from large database MYSQL (No Auto Inc Field)

This may sound rather evil but you might find this interesting

Suppose your table looked like this

CREATE TABLE massiveTable
(
    PK VARCHAR(50) NOT NULL,
    ... Other Fields ....
    PRIMARY KEY (PK)
) ENGINE=MyISAM;

Step 01 : Create a Empty Copy of the Table and another for Keys

CREATE TABLE randomKeys SELECT PK FROM massiveTable WHERE 1=2;
CREATE TABLE randomTable LIKE massiveTable;

Step 02 : Iteratively load every fifth key

SET @x = 0;
INSERT INTO randomKeys
SELECT PK FROM
(SELECT PK,(@x:=@x+1) num FROM massiveTable) A
WHERE MOD(num,5)=0;

Step 03 : Create the Random Temp Table

ALTER TABLE randomTable DISABLE KEYS;
INSERT INTO randomTable
SELECT B.* FROM randomKeys A LEFT JOIN randomTable B USING (PK);
ALTER TABLE randomTable ENABLE KEYS;

Step 04 : There is no Step 04. That's it !!! No stored procedure needed.

CAVEAT : I cannot make any promises on the running time or query processing.

Give it a Try !!!


No, it won't help. If Field4 were the first column, then it would have helped. Like asking if going through a telephone directory/book will help you sort peoples names by firstname. :-)

Edit: Erik posted one example where this index can help. Requires you to return all of the rows for a certain Field4 value (i.e., equal-comparison on all but Field4).


PostgreSQL covering index on large table as alternative to CLUSTER command

PostgreSQL does not have covering indexes.  There has been some work on them, but it has not yet been accepted into the code tree.

So there are limitations.  If you have unique constraint on some prefix of the indexed columns, you will have to have a separate index to support that, it can't piggy-back on your larger index.  Also, some data types do not support Btree operators which means they cannot be included into a btree index. For example, XML or many geometric types.

Also, an indexed value cannot exceed 1/3 of a page, or about 2700 bytes, so inclusion of some wide columns into the index could fail on this grounds (I don't think covering indexes, if they existed, would solve this problem)

Finally, you only avoid consulting the table for those table pages marked as "all visible".  If the table is mostly static, then is probably not a problem.  But it the table is very dynamic, it will take very aggressive vacuuming to maintain the "all visible" count.

Partitioning the table may be another option.  It is not nearly as fine-grained as clustering it, but can impose less of an ongoing burden.  Particularly if all the active updates go against 1 or 2 partitions, while the rest are effectively read-only.


Check Orders that can be delievered with reducing quantity from stock

I'm not a big fan of cursor but it seems like a good case for it.
Declare OrderCursor Cursor
Local Static Read_Only Forward_only
For select distinct OrderID from @Order order by OrderID;

declare @OrderID INT;
create table #Remaining_Inventory  (PID int, Qty int);
create table #Result (OrderID int, Stat varchar(20));

insert into #Remaining_Inventory
select * from @Inventory;

OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @OrderID;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    if not exists (
    select 1
    from @Order o
    left join #Remaining_Inventory i on o.PID = i.PID and o.Qty <= i.Qty
    where o.orderid = @OrderID
    and i.PID is null
    )
        begin
            insert into #Result select @OrderID, 'Deliverable';
            update #Remaining_Inventory set #Remaining_Inventory.Qty= #Remaining_Inventory.Qty-o.Qty
            from #Remaining_Inventory
            join @order o on #Remaining_Inventory.PID = o.PID;
        end
    else
        insert into #Result select @OrderID, 'Not Deliverable';

        FETCH NEXT FROM OrderCursor INTO @OrderID
    END

    select * from #Result;

    drop table #Remaining_Inventory;
    drop table #Result;
    CLOSE OrderCursor
    DEALLOCATE OrderCursor

will return the expected result.


Cloning a PDB from one CDB into another CDB with TDE enabled at source and target

If you are trying to avoid exportation of encryption keys, your only chance is Data Pump, because that's the whole purpose of TDE.

Yes, you have different ways to clone your PDB, such Data Guard, but you'll need to export the encryption keys too.


mysqldump still fails even with mysqldump --column-statistics=0

And what would be the use of this parameter?:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump --column-statistics=0 sakila -u root -p >d:\temp\stats.sql
Enter password: ********

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump  sakila -u root -p >d:\temp\no-stats.sql
Enter password: ********

C:\Program Files\MySQL\MySQL Server 8.0\bin>d:

D:\TEMP>dir *stats.sql
 Volume in drive D is HDD
 Volume Serial Number is D46B-804B

 Directory of D:\TEMP

17-11-2019  15:41         3.387.169 no-stats.sql
17-11-2019  15:41         3.387.169 stats.sql
               2 File(s)      6.774.338 bytes
               0 Dir(s)  62.853.836.800 bytes free

D:\TEMP>fc stats.sql no-stats.sql
Comparing files stats.sql and NO-STATS.SQL
***** stats.sql

-- Dump completed on 2019-11-17 15:41:41
***** NO-STATS.SQL

-- Dump completed on 2019-11-17 15:41:58
*****

D:\TEMP>


What is the difference between Used and Allocated space?

Think of your database like a bucket.

The ALLOCATED space is how big the bucket is, i.e. how much data it can hold.

The USED space is how much water/data is actually inside the bucket.

When the water gets to the top of the bucket, you need to make the bucket bigger by allocating more space.


Maybe you don't expect this answer, but do both. Let's see.

In order to accommodate for the life-cycle of the data ( something many database designers don't contemplate ), you should have a "historic" table with the downloads that finished. That table will not only hold files that downloaded successfully to a 100%, but also downloads that were canceled or aborted. You could have a policy of deleting records from this table after some given number of days.
The "live" downloads are in other table. Keep in mind that for the sake of simplicity, the migration of records from the "live" table to the "historic" one should be asynchronous (maybe a cron job or timer), meaning completed download will live there for a few seconds, or minutes after they finished. There's nothing wrong with it. It's even desirable so files don't disappear from view the very millisecond they finish downloading.
Add two timestamped columns to each table: TimeAdded and LastModified.
Why do I say this is both ? As I see it, it's a combination of the two approaches. Table one behaves as if you'd chosen the single table approach. Only an asynchronous process migrates completed or aborted files to a historic table after some time, it doesn't have to be immediate.


Could not allocate space for object sql server

You need to do one or more of 3 things:

Increase the max file size of the file
Increase the disk space wherever your .mdf & .ndf files are stored
Delete data from your database to free up space for the new object

Right click on your Database > Properties > Files.

Here you will see the file names, file groups, path, and importantly the Autogrowth / Maxsize. If you have it set to Limited to ... then it can't grow beyond this limit. If the file is full, you will either need to change the Maxsize or delete data from your database. If the file isn't full, then your disk could is almost certainly full and you'll need to add space there.

Also, don't shrink your database in an attempt to fix this.


MySQL on insert in table update field in another table?

Something like this should do the trick.

DELIMITER $$
CREATE TRIGGER My_Trigger -- name is arbitrary
AFTER INSERT ON TopicAnswers
FOR EACH ROW
BEGIN
  UPDATE Topics
    SET Answers = Answers + 1
    WHERE ID = NEW.TopicID;  -- The OP figured this line out for himself.
END;
$$
DELIMITER ;

I don't have your data, but it should get you started.

Take the trouble to go to the Help Centre, take the tour
and check out the "help us to help you" blog. This forum
is a great resource and you will get all the more out of
it if you take the trouble to read the guidelines (not that
there's anything wrong with your post). p.s. welcome to the
forum.


You need to read a business data modeling book such as those by Silverston, Hay, or Fowler
You are confusing a Product, which is the specification of an Asset (such as a vehicle). A product does not have a quantity. For cars, the Product has a model number (Mazda 3) and the Asset has the serial number (VIN xxx).
You should also read up on Table Inheritance (Fowler).


Distributor latency - slow sp_MSget_repl_commands performance

To answer my own question to an extent. Commands appear to be written to the command buffer as soon as sp_MSget_repl_commands makes them available. If the writer thread is unable to write the command to the subscriber then sp_MSget_repl_commands spins until the command has been written. Once written the writer notifies the reader and frees one of the two command buffers for the next batch of commands. This explains the long execution of sp_MSget_repl_commands.


MySQL group concatenation - creation of insert statement on temp table

There is no SQL that can directly perform what you have described.  Instead...

Read that data into your programming language (PHP, Java, VB, etc) and use code to construct the statement in question.  Then execute it.

Yes, a Stored Procedure could be constructed, but that might be messier.


How to deny database acess to windows autentication users

If the users are administrators, there is no way to keep them out.  This could be sysadmin on the server, dbo on the database, etc.

The easiest way to keep other (non-Administrator) logins out of your database is to never grant them rights or (as one part of your code suggests) drop any users that are not 'automacao' or some other needed service logins.

Also, rather than cycling through every securable, you can use (assuming SQL Server 2005 and forward) is to grant at the database level. E.g.

 GRANT EXECUTE ON DATABASE::YourDB TO automacao


mysql : remove row if parent id match in primary key columan

Try this:

SELECT a.organization_id,
    a.parent_organization_id,
    a.organization_name
FROM (
    SELECT om.organization_id ,
            om.parent_organization_id,
            om.organization_name
    FROM `organization_master` AS om
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id
    WHERE om.c_user_id =220 OR oau.user_id =220
    GROUP BY om.organization_id
) a
WHERE a.parent_organization_id NOT IN
(
    SELECT om.organization_id
    FROM `organization_master` AS om
    LEFT JOIN organization_assigned_user AS oau ON om.organization_id = oau.organization_id
    WHERE om.c_user_id =220 OR oau.user_id =220
    GROUP BY om.organization_id
)
OR a.parent_organization_id IS NULL

If this works for you, consider storing results of your main SELECT (used twice in my reply) in a TEMP table and joining on that for performance purposes.


sql server full text search - fuzzy searching?

You could try an implementation of one of the string distance functions.  Here's a T-SQL implementation of the Levenshtein distance taken from here.  That post does suggest there's a performance issue with the function and scalar functions generally can be bad for performance when used in queries, but worth a look.  You could always try and convert it to a table function.


Compressing partition - what about index?

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use:
ALTER TABLE table_name MOVE partition TEST_TAB_2014 COMPRESS;

And then rebuild(not recreated) all indexes manually, preferably with reasonable parallel option.
PS: you should also read something about various compression options Oracle offers. Unfortunately it is not simple from both technical and also licensing perspective.
Briefly, Oracle supports these compression options:

BASIC, offline, block level. Usually the bigger the tablespace's block size the better compression ratio. This one is used only when moving tables/partitions, or when using direct path load (/* +APPEND */ hint)

OTLP, online, row level, being part of advanced compression extra cost option.

ALTER TABLE … COMPRESS FOR OLTP  This approach will enable OLTP Table
Compression for all future DML -- however, the existing data in the
table will remain uncompressed.
ALTER TABLE … MOVE COMPRESS FOR OLTP  This approach will enable OLTP
Table Compression for future DML and also compress existing data.

COLUMNAR, works on column level, this one can also used for LOB compression. You can use it only on Exatada or when you use some SAN/NAS storage from Oracle.


DDL changes implicitly commit only if they are in the same connection. As they are in a different connection from the mysqldump they lock wait until the dump is complete and then they will complete.
As such hopefully DDL changes aren't as time critical and --single-transaction will suit your needs without any addition fiddling.
Alternate would be to create a replica server there and backup from that. An DDL in the replication stream will wait however it won't impact the user or the backup.


I can´t change innodb_buffer_pool_instances

innodb_buffer_pool_instances where removed in 10.5 because they offered no benefit due to internal restructuring of the code.
There is no replacement setting.
The variable, that serves no purpose, is there for compatibility only and will be removed in 10.6


How to prevent "ibdata files do not match the log sequence number"?

I have faced the same problem: MySQL restarts too many times and ibdata files do not match the log sequence number.

This is happening because one of the tables is corrupted.
In order to fix this issue, take a backup of the database and perform the mysqlcheck operation:

1.

mysqldump -u root -p dbname > /tmp/dbname.sql

2.

mysqlcheck -u root -p dbname

If you find the corrupted table then you can remove it, I know corrupted tables can't be deleted but we can restore the database from the corresponding production database.


Inserting 30k prices each minute, how can I skip an entry if the price did not change?

You have 30K new records for the 'current' minute; put them in a temp table; include PRIMARY KEY(ArticleId).

Note: History will need PRIMARY KEY(ArticleId, InsertTime).

You need to find the 'latest' existing price for each of the 30K articles in the existing history table.  But first, you need to find the latest InsertPrice for each article in this table:  (This is 'select#1')

SELECT ArticleId, MAX(InsertTime) AS InsertTime
    FROM History
    GROUP BY ArticleId

Now back to the latest price (select#2):

SELECT ArticleId, ArticlePrice
    FROM ( select#1 ) t1
    JOIN History  USING(ArticleId, InsertTime)

Now let's find the changed prices (select#3):

SELECT temp.ArticleId, temp.LastInsert, temp.ArticlePrice
    FROM ( select#2 ) t2
    LEFT JOIN temp  USING(ArticleId)
    WHERE temp.ArticlePrice != t2.ArticlePrice

I may have also allowed for 'new' Articles to arrive.  (But I am not sure.)

Now, let's copy the new Prices over:

INSERT INTO History (ArticleId, InsertTime, ArticlePrice)
    select#3;


Update linked server table based on local table

You cant have 5 identifiers, this should do the trick

UPDATE T2
SET    T2.cod = T1.codigo
FROM   [HQSQLHOM01\SQLHOM01].dba_teste.dbo.tabela2 AS T2
JOIN   tabela1 AS T1
       ON T1.id = T2.id

https://technet.microsoft.com/en-us/library/ms190406(v=sql.105).aspx


How to maintain database ownership restoring across domains?

The restore completes, but as soon as it's done, the domain login can no longer access the database because the database ownership is lost. (I believe because it had a different owner on the vendor's system).

Correct. While it's possible (I believe) to have the same SID (i.e. Security ID) between their system and yours (if so, it would require you creating their domain account in your domain with the SID that they provide that is the SID of the owner account on their side), most likely it's a different SID, and that is retained in the database itself.

  After the restore is complete, I have to re-create the user within the database that matches up to the domain login, and make that user the owner again.

Now this doesn't sound right. Usually, if you add an SID to a database as a User, and then later try to make a Login (having that same SID) the owner of the database, you will get an error stating that the account / SID is already present in the database:

     Msg 15110, Level 16, State 1, Line XXXXX
     The proposed new database owner is already a user or aliased in the database.

It's possible that something is misstated here, or that I am misunderstanding something, but as we will see in a moment, it doesn't really matter.

  This vendor is trusted enough that we have granted the login the "dbcreator" server role.

It's nice that you can trust them, but you don't need to do anything special for them. You can allow them to restore their database, and automatically set the owner to their domain account, and anything else that needs to happen in this process, all while removing them from the dbcreator fixed Server Role. All their login needs is the ability to connect to the instance (and they already have that), and permission to execute a single stored procedure. What stored procedure? The one I will now describe :-)

Using the wonderful awesomeness of Module Signing, you can set up a stored procedure that will a) execute the RESTORE DATABASE command, b) set their login as the owner of that database, and c) perform any other administrative steps needed for this operation, no matter what level of permissions are needed. You will never give the vendor's login any permissions except for the ability to execute this single stored procedure. The necessary permission(s) will instead be given to the stored procedure.

Assuming that you have a [Utility] (or whatever name) database for general administrative functionality, do the following:

In the [Utility] database:

Add the DOMAIN\VendorLogin to the DB using CREATE USER
Create a RestoreVendorDB stored procedure that does the following:

Executes RESTORE DATABASE [VendorDB] ...
Executes ALTER AUTHORIZATION ON DATABASE::[VendorDB] TO [DOMAIN\VendorLogin];

Allow the vendor to execute the stored proc: GRANT EXECUTE ON [dbo].[RestoreVendorDB] TO [DOMAIN\VendorLogin];
Create a certificate using CREATE CERTIFICATE...
Sign the stored procedure with that certificate using ADD SIGNATURE...
Copy certificate (only the public key) to [master] database using Dynamic SQL to execute CREATE CERTIFICATE with result of CERTENCODED(CERT_ID(...))

In the [master] DB, do the following:

Create a login from the certificate using CREATE LOGIN [Permission$CreateVendorDB] FROM CERTIFICATE [...];
Add the certificate-based login to the dbcreator role using ALTER SERVER ROLE [dbcreator] ADD MEMBER [Permission$CreateVendorDB];
If you need an additional role and/or permission in order for the stored procedure to change the database's owner, then repeat the previous step with the appropriate role and/or  GRANT the certificate-based login the appropriate permission(s).
Remove the vendor's login from the dbcreator role (they no longer need that permission) using ALTER SERVER ROLE [dbcreator] DROP MEMBER [DOMAIN\VendorLogin];

If you ever alter the code in the RestoreVendorDB stored procedure, just re-sign the stored procedure (after the ALTER) using ADD SIGNATURE....

Using this approach, you not only get exactly what you were wanting, but you also end up with a more secure system 😺 .

For more explanation and a working example (of this concept), please see:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level


How to change definition of a Postgres 12 GENERATED column

I'm not sure this is the correct answer, but reading the docs about ALTER TABLE I would say: No, you can't.

  where action is one of:

  ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE
  collation ] [ column_constraint [ ... ] ]

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

It seems that the only command that allows column_constraint is ADD COLUMN

In fact you can add a constraint name to the computed column:

val_sum int4 CONSTRAINT GA_test generated always AS (val_a + val_b) stored


What are the pros and cons of using the Sphinx Storage Engine?

Pros

If there is no Sphinx API for the language you are using SphinxSE allows you to do searches through MySQL.
If you are doing background processing and storing the search results in your database SphinxSE allows you to move the data from the searchd service directly into MySQL instead of going through an application layer.

Cons

You have to compile SphinxSE into MySQL.
Using SphinxSE requires some additional setup besides the basic searchd and indexer configuration.
(This one is more my opinion) Searching through the API is more intuitive than using the SphinxSE SQL syntax.


spt_values is not exactly a help table for your use. It is an undocumented system table used by internal stored procedures for a variety of tasks.

Since it contains neither unique nor contiguous numbers, and because it is undocumented and unsupported (and could simply vanish in a future version), I strongly recommend you don't use this table for anything.

You can always generate a sequence based on row_number from documented and supported system views, such as sys.all_objects, sys.all_columns, or a cross join of the two if you need a large number.

Or just build a Numbers table and store the numbers for yourself. This is likely to be more efficient in most cases because, if you use it enough, it will always be accessed in memory without having to calculate the sequence.


Atler Table Alter Column Set Default FALSE: Why my alter table took 10 minutes to finish?

There must have been a concurrent long running transaction that used (perhaps read from) the table. Check pg_stat_activity for sessions with a state other than idle.


SSIS: Scripts in package part not being executed when target server is 2016

Microsoft confirmed that this is indeed a bug that will be fixed in the next version of SQL Server Data Tools (SSDT) for Visual Studio 2017.

The confirmation was in the Integration Services (SSIS) Advisors Group on Yammer. The group is closed so I cannot post a direct link to it. However here is what I have been told:

  There is no easy workaround. A package containing package part has issues when persisting in previous target server version. You can try to either:

  Install and use SSDT for VS2015 release 16.5 (https://go.microsoft.com/fwlink/?LinkID=832313)
  Use your existing SSDT for VS2017, but always design your package targeting to latest SQL Server. When you want to deploy to SQL Server 2016, switch target server version to 2016, but DO NOT open the package again or launch deployment wizard. Instead, you should copy the dtsx into another empty project which does not use package part, and deploy that project.

  Sorry for the inconvenience. We will fix it ASAP.

NOTE: I have since then stopped using package parts and instead, I use parametrised child packages as an alternative.


You can't easily control the order in which SQL Server will evaluate the contents of a column that does not use the correct data type (or has mismatched precision). If you try to cast a column that is nvarchar to numeric, even if you have filters that should eliminate all non-numeric values from consideration, SQL Server can still try those first (see Erland's complaint about this on UserVoice). There are cases where you can nest CTEs until the cows come home, but SQL Server will still push or pull that evaluation to a place where you didn't expect it.

As long as aggregates and fulltext functions aren't involved (see here and here), you can force this evaluation before conversion by using a CASE expression. Here is a simplified version of your query with no CTEs:

SELECT cfv.issue,
  priority_num = CONVERT(NUMERIC(something, something),
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)
FROM
  proddb1.customfieldvalue AS cfv
  INNER JOIN proddb1.customfield AS cf
  ON cfv.CUSTOMFIELD = cf.id
  INNER JOIN proddb1.customfieldoption AS cfo
  ON cfv.CUSTOMFIELD = cfo.CUSTOMFIELD
  AND CONVERT(NUMERIC(something, something),
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END) = cfo.id
  WHERE cf.cfname = 'Issue Priority';

You will need to change something, something to the proper precision/scale (or use one of the int types if you don't need decimal places). You should never declare varying types without specifying the length - this blog post is about varchar, but it really applies to all of the types.

If you are on SQL Server 2012 (it's always useful to include information like what version of SQL Server you're using), you can simplify this:

CONVERT(NUMERIC(something, something),
    CASE WHEN ISNUMERIC(cfo.customvalue)=1 THEN cfo.customvalue END)

to this:

TRY_CONVERT(NUMERIC(something, something), cfo.customvalue)

And it will actually be more reliable (since ISNUMERIC can return 1 and still fail at conversion time for specific types). I blogged about this way back in 2002.


How does like query work with indexed table

Let me help you to understand how a "standard" index works.

Most databases indexes are just B-Trees (not to confuse with a binary tree). Simply speaking, when you query an indexed column, a binary search will be performed. A binary search performs generally in O(log(n)) and thus you can find individual rows quite fast, even if there are many of them. The database uses B-Trees instead of loading and sorting the table as the indexes don't require as much memory and less disk reads are required.

Now imagine that you try to binary search for a value, but you don't know the exact beginning or ending of the value. A binary search is basically not possible and you have to traverse nearly the whole tree to find every possibility.

Of course, there're cleverer techniques than this. MySQL is not that dumb, it uses a Boyer-Moore algorithm for this problem, but that doesn't mean you don't suffer a performance impact.

A Fulltext search index will of course help. It uses entirely different datastructures (Tries, Suffix-Trees). Reading the manual of MySQL, I also get the impression that fulltext searching is quite easy with it.

However, on most systems fulltext searches require some maintenance/housekeeping by the admin to keep up good performance. Often times fulltext indexes keep "mapping/index" tables for the tokens of the indexed texts. These tend to fragment which can impact query response time, as the index grows unecessarily larger. So from time to time, they should be defragmented and optimized. You might want to look into that.


How are these two SQL Server rollbacks different?

As indicated above, after running more tests, I arrived at an calculated conclusions. I summarized all of them into a blog post here, but I'll copy some content over to this post for posterity.

Conjecture (based on some tests)

As of now, I do not have a clear explanation as to why this is. But following are my estimations based on the artifacts gathered during the tests.

Rollback happens in both scenarios. One is explicit rollback (user hitting Cancel button), the other is implicit (Sql Server making that decision internally).

In both scenarios, the traffic going to the log file is consistent. See the images below:

Scenario 1:

Scenario 2:

One artifact that reinforced this line of thinking is capturing Sql Trace during both the scenarios.

Scenario 1 is self evident a.k.a. when we hit ‘Cancel’, it rolls back.
In Scenario 2, the error message is displayed after performing ‘rollback’ implicitly. In Sql Trace, we see the error message “The transaction log for database ‘SampleDB’ is full” a long time before the message is displayed on the screen. So, my guess is that rollbacks happens in both scenarios, but the error message is Scenario 2 is displayed after successfully and completely performing the rollback.

Scenario 2 seems to take longer as as it progresses much further along, so the rollback takes longer.

Unexplained behavior:

Why does the log file usage vary so much?

It increases to 90%, then down to 85%, then up to 99% and hovers there for a long time. I see it going up and down like this several times: 99.2 %, 99.8 %, 99.1 %, 99.7 %. Why does this happen?
One possible explanation is that, there might be a background process (something like Log Flush) that cleans up log file every few minutes. And everytime it kicks in, some entries are cleared up, resulting in more free space available.

Any ideas to help explain this behavior in a better way are welcome.


Connect to Database Engine Prompt on Script Object

I was able to reproduce the problem you're encountering.

Originally, I wasn't being prompted like you are being prompted.  There is a button just to the right of New Query that invokes the Database Engine Query.  This button would be very easy to click because it's close the the New Query button

This brings up the connection dialog box you are experiencing.  The problem is, once you click this button, you will continually be prompted for a connection until you exit out of SSMS and come back in (at least, that is how I corrected the problem).


Creating relationships between tables that where not specified in the given UML diagram

UML class diagrams do not include relations in a class' attributes list, so when creating database tables representing those classes, the database designer must identify and specify them, much like the software developer must create the pointers and lists to support the related objects.

In your diagram, Commande relates to Client but does not explicitly specify a IDClient attribute. When creating the Commande table, you must include this attribute along with a foreign key to Client.

You can find further information about this in Database Design - UML/SQL:

  The UML representation of the Order class contains only its own descriptive attributes. The UML association tells which customer placed an order. In the database, we will need a different way to identify the customer; that will be part of the relation scheme (below).


Optimize mysql query, on table with 32 million records

You need to add full text index to your table as

ALTER TABLE track_master
ADD FULLTEXT INDEX `artist_key` (`artist`)
ADD FULLTEXT INDEX `track_key` (`track`);

Also you need to tweak mysql server settings to support 2 character words. Find the mysql config file, In case of UBUNTU based server it is /etc/my.cnf and then locate the statement innodb_ft_min_token_size=3 and change it to innodb_ft_min_token_size=2. Then restart mysql server.

After that rewrite your query and use it as

SELECT MIN(NULLIF(year, 0)) as year, genre, release_date, styles
FROM track_master
WHERE MATCH(artist) AGAINST ('Lo Fidelity Allstars')
AND MATCH(track) AGAINST ('"Battle Flag"')


You will need cross-database permissions.
The most secure solution is to create a dedicated user from Certificate in the "example1" DB and grant this user read privileges. Then in the "example2" DB you will sign this stored procedure with the same certificate, so the procedure will be able to use permissions of the previously created user.
You can find detailed tutorial here. And for a longer elaboration on the topic, check out Erland's classic article.
Another option would be to enable 'Cross-database ownership chaining', but it is not recommended because of security implications.


INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp

There are a couple of connect items that might be relevant here.

An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1
Poor xml performance with table variable
Bad performance when inserting data from element-centric documents
INSERT from .nodes with element-based XML has poor performance on SP2 with x64

I have not been able to reproduce what you see with your query so I can not say for sure what is happening for you but one issue was that there was an eager spool step in the query plan that was not needed. That eager spool can be removed by using a trace flag in the first fixed versions of SQL Server. I am not sure if that is needed in later versions. The eager spool was in there "due to a general halloween protection logic". That might explain why you see the bad performance when you insert to an already existing table. If you have a look at the query plans for both your queries you should see the difference if that is the case.

There are also some workarounds suggested that you can try and I think you should try to specify the text node when you fetch text-value.

scen_val.value('(text-value/text())[1]', 'varchar(100)') AS 'scen_value' ,

If you don't do that SQL Server will aggregate all text values below text-value. For your XML you probably don't see a difference in the result but if text-value was a mixed content node with both text and sub nodes you would.

declare @XML xml = '
<root>
  <item>item text <subitem>sub item text</subitem>
  </item>
</root>'

select @XML.value('(/root/item)[1]', 'varchar(50)') as C1,
       @XML.value('(/root/item/text())[1]', 'varchar(50)') as C2

Result:

C1                           C2
---------------------------- ----------------------------
item text sub item text      item text


Modifying a result set in a Stored Procedure, then returning that modified set

Is there a better way of achieving this, or am I on the right track?

Yes there is and no, I don't think you are on the right track. There is almost no reason to use a cursor in this situation.

If ShowLocation is a value on each record in the table then this is the kind of situation where a CASE statement comes in handy.

SQL Server code would be similar to the following

CREATE PROCEDURE dbo.GetLocationData
AS
SELECT
  <all_other_fields_here>,
  CASE
     WHEN ShowLocation = 1 THEN Location
     ELSE NULL
END AS Location
FROM PERMTABLE
WHERE Location within bounds -- not entirely sure how you're doing this
ORDER BY Location;
GO


Follow the Microsoft prescribed Detecting and Ending Deadlocks article. You should never "guess" at the reason for the deadlocks, otherwise you will be guessing at solutions.  The short list of steps to take is enable trace flags 1204 and 1222.  The output will give you explicitly the resource ID of what what's actually involved.  Could be a foreign key constraint, index page.  Only knowing that can you target what to try to fix with your queries, indexes or even isolation level.


Linking foreign keys across multiple databases: direct, or using an intermediary table?

This is bordering on an opinion-based question, because of course anyone can have their own justification for either choice.
I would not choose to add the intermediary table, for a few reasons.
One is that as you know, the intermediary table cannot have a foreign key reference to a table on another MySQL instance. Foreign keys can span schemas, but not instances.
If you need to run special scripts to clean up the intermediary table anyway, that special script should be able to encode clean up tasks for all the blog tables. I assume at least the number of blog tables remains relatively stable. You aren't going to be adding so many blog tables in custom ways that this becomes a burden to maintain the cleanup script.
The intermediary table therefore seems like an extra step that just complicates things, and takes extra storage.
I've worked at several companies that eschewed the use of foreign key constraints in any case, because they make some operation tasks harder, for example schema changes. Also because they incur locking behavior that you probably don't expect. In the absence of the constraints, you can't get the ON DELETE CASCADE feature.
Like I said above, this is subject to opinion. You could easily ask, "but what if the priorities are a bit different...?" Of course, you must make this decision based on your requirements and priorities.


As you identified in your question, it is not possible to reliably discern which Windows user is logged into a SQL Server using a SQL Server login.  You could track the name of the workstation where the user connects from, but that doesn't guarantee anything - the workstation name is easily spoofed.

If you require that level of auditing, you'd need to build it into all applications accessing the database.


Applying Index happens in memory or disc?

Study the concepts of "cache" and "block".

In many situations, especially in databases, the data on disk is organized into "blocks".  When a block is needed (to satisfy some SELECT or UPDATE or whatever), it is requested.

Meanwhile, there is a "cache" in RAM that contains some of the blocks.

To satisfy the request for a block, the system first checks to see if the block is already in cache.  If it is not, then I/O is performed to bring a copy of the block from disk into RAM.  (For "persistence", the copy on disk must be considered the "source of truth", but let's not get into "ACID" in this discussion.)

Once the block is in the cache in RAM, the processing proceeds.

But, the cache is of a fixed size.  When there is not room in cache for a block, some block currently in cache must be pushed out of the way (and written back to disk if it is "dirty").

So...

Case 1:  Everything needed for the query is already in cache:  No I/O needed.

Case 2:  Some blocks are in cache:  Some I/O.

So...  Even if you have 10 billion records, and not nearly enough RAM to cache them, Case 2 says that the processing will (eventually, maybe in hours) finish with your query.

But... You mentioned indexing.  We just talked about how an index can get you to the desired row (think "block" in this view of the processing) by touching very few blocks.  With a suitable index (BTree or otherwise), the desired row can possibly be found by touching very few blocks.  And those blocks might already all be in cache.  Hence, very fast, perhaps less than a second, perhaps with no I/O, even with billions of rows.


First of all you need a proper https certificate available and installed in both instances (i suppose you installed SSRS on both instances and i suppose also that you are using SSRS native mode).
Then open Reporting Services Configuration Manager,
click Connect at the “Reporting Services Configuration Connection” Screen;
click on Web Site URL in the left-hand pane window
In the “HTTPS Certificate” drop-down box select the certificate you installed.  You will see the Port text box pre-fill and the Report Server Web Services URL pre-fill.
Click Apply


How do we determine data_length + index_length as database size in MySQL

This won't be accurate for InnoDB tables. The size on disk is actually bigger than that reported via your query, take a look at :

select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES

file_size  (which is the logical file size on disk) and
allocated_size (which is space allocated for this file and can be significantly smaller)
more details here : https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/


Delete image/blob content to reduce *.mdf file size?

Yes, deleting the unused images will free up space inside your DB files.  To actually reduce the size of those files you will have to SHRINK them after the images have been deleted.  Here's a link to the MS Technet site for this process.  Shrinking the files will most likely introduce internal fragmentation into your tables and indexes.  A thorough REBUILD would be appropriate afterwards.  On a 10GB database, once a year, this shouldn't be an undue burden.


Multiple queries in merge into statement

Using temporary table

create table test_tab (id int not null, is_active int not null) in userspace1;

with stream (id) as (values
--1, 2, 3
--2, 3, 4
3, 4, 5
)
, upd as (
select count(1) cnt
from new table(
  update test_tab t
  set is_active=0
  where is_active=1 and exists (select 1 from stream s where s.id=t.id)
)
)
select count(1) cnt
from new table(
  insert into test_tab(id, is_active)
  select id, 1
  from stream
);

select * from test_tab;

Assuming you accumulate your batch values into the streams table before each attempt to change the base table test_tab.
You can do all your operations using such a single statement as described above.
If you want to use some real table instead of streams, it's worth to collect statistics on it before every such a statement and test the performance using various number of records in the streams table.

Using batch inserts

create table test_tab2 (id int not null, is_active int not null default 1, id_int bigint not null generated always as identity) in userspace1;
create index test_tab2_idx on test_tab2(id);

create trigger test_tab2_air
after insert on test_tab2
referencing new as n
for each row
update test_tab2 t
set is_active=0
where t.is_active=1 and t.id=n.id and t.id_int<>n.id_int;

insert into test_tab2 (id) values
--1, 2, 3
--2, 3, 4
3, 4, 5
;

You can just insert new records in batches in your java program. The trigger deactivates the corresponding old records.


Schema Changes Are Not Populating to The Subscribers

Did you mean afterwards you re-created the publication that you dropped? Afterwards, did you started back the snapshot agent?


"sudo -u postgres psql postgres" does not ask for password

Postgres has a type of authentication called peer. If you look at pg_hba.conf you may see something like

local   all             postgres                       peer

or like

local   all             all                            peer

The fact you did not get asked for the password has nothing to do with using sudo and everything to do with the fact that you were connecting to a DB that matched the user name and peer authentication was enabled in pg_hba.conf.

If this is a test machine and security is not needed you can use trust instead of peer and then passwords will not be needed. Can not stress enough, trust is only a good option for development and test machines. One should never, ever, use trust on a production  machine.


SQL Agent and DTSX SSIS package: How to get it to email or ftp the output file? (SQL Server 2016)

If you're not up to modifying the SSIS package to incorporate email or SFTP, you could add an additional step to the SQL Agent job.

A Transact-SQL script step could be used to call sp_send_dbmail to email the file to the appropriate recipient.

An Operating System step could be used to run an SFTP client (e.g. WinSCP) to transfer the file (though this would probably be better done in another SSIS package--example).


Get table sizes without SELECT permission

Create an EVENT that fires every 15 minutes. Here is a tutorial.

The EVENT could be used to populate a table with the necessary information.

Then GRANT SELECT on that table and only that table to your user.


datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float

Like everything else in SQL Server, it depends.

Numeric is the same as Decimal.  These let you specify precision and are good if you will be having more than 2 decimal places or need more precision than what you indicated.

Money is used for, you guessed it, money.  If your field is a currency this is appropriate.

Float is kind of special and is NOT suited for exact numbers.  If you need to represent floating point numbers, this is a way to go.  If you need to always store exactly what you put in the field, float is a bad choice.

So, what do you want to use the field for?  What does your data actually represent?  That should be the determining factor in what datatype you use.


postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

The docs on Privileges give an explanation of how to interpret the output. For specific privileges on a table of the current database, use \z myTable.


Can you use ODBC to extract data directly from SAP HANA S/4 1610?

Your question is a bit confusing so I hope I'm deciphering it correctly.

Technically you can connect to a SAP HANA database using ODBC -- this is helpfully documented in the manual.

What you do after you connect is another question. The SAP ERP application schema is very complex and poorly (if at all) documented, and it is highly unlikely that you will be able to reverse engineer it and create a sufficiently reliable ETL process based on that.

So the answer I guess is "yes you can, but you don't want to".


There are many many ways to do this. Here is one that only slightly changes your attempt:

SELECT m1.ModuleID, m1.CWWeight, l.StaffID
FROM dbo.module as m1
INNER JOIN dbo.Lecturer as l
    ON m1.ModuleConvenor = l.StaffID
WHERE m1.CWWeight = (SELECT MIN(m2.CWWeight)
                    FROM dbo.Module as m2
                    WHERE m2.ModuleConvenor = l.StaffID) ;


byte vs char length semantics in sqlplus

Set NLS_LANG properly. Here is an example.

I use KiTTY for SSH client, with UTF-8 settings.

[oracle@o73 ~]$ file test.sql
test.sql: UTF-8 Unicode text

[oracle@o73 ~]$ cat test.sql
declare
  x varchar2(5 char);
begin
  select 'üüüü' into x from dual;
end;
/
[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 7 19:04:00 2018
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> @test
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0
[oracle@o73 ~]$ export NLS_LANG=.AL32UTF8
[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 7 19:04:13 2018
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> @test

PL/SQL procedure successfully completed.

SQL Developer handles this automatically for you.


(Borrowing from my earlier answer here...)

re: actually performing the backup

Backing up a native Access database is simply a matter of copying the entire database file (.mdb for Access_2003 and earlier, .accdb for Access_2007 and later). Any scripting language would work, even a simple Windows batch file that does something like

copy /Y d:\apps\databases\mydatabase.accdb z:\backups\databases\*.*

re: automatic scheduling of the backup

The Task Scheduler in Windows could take care of that for you. Once you've created your script to copy the database file(s) you can create a scheduled task to run it periodically. See the MSDN article Using the Task Scheduler (Windows) for more information.


OFFSET X ROWS FETCH NEXT 50 ROWS ONLY not using indexes

It's hard to say without seeing query plans, the table structure, or the index definitions on the table. You should make sure that the index is covering the query. Other than that, you can encourage SQL Server to use the index even without an explicit hint to use the index. For example, if you are confident that the query should always use the index you could try setting a small rowgoal with a hint:

DECLARE @row_goal INT = 456450

SELECT *
FROM [TableName]
ORDER BY [ColumnName] ASC
OFFSET @row_goal ROWS FETCH NEXT 50 ROWS ONLY
OPTION (OPTIMIZE FOR (@row_goal = 1));

If you want to avoid hints entirely I recommend rewriting the query. Using OFFSET with large values requires special care. It's easy to end up with a plan that scans lots of rows and does a lot of unnecessary work just to get the next 50.

If your application pages through the data one page at a time you can consider the technique described here. The basic idea is that the application keeps track of the last value that the end user has seen. When you need a new page you can pass down the last value as a filter:

SELECT *
FROM [TableName]
WHERE [ColumnName] > ?
ORDER BY [ColumnName] ASC
OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY;

If you need to be able to show an arbitrary 50 pages you can use the approach detailed here by Aaron Bertrand. With CTEs you can encourage the query optimizer to scan through a narrow index until it reaches the first row that you want. The query will get slower as you increase the OFFSET but it could perform much better than what you're doing now. The basic idea would be like this:

;WITH pg AS
(
  SELECT [key_column]
  FROM [TableName]
  ORDER BY [ColumnName] ASC
  OFFSET @PageSize * (@PageNumber - 1) ROWS
  FETCH NEXT @PageSize ROWS ONLY
)
SELECT t.*
  FROM [TableName] AS t
  INNER JOIN pg ON t.[key_column] = pg.[key_column] -- or EXISTS
  ORDER BY [ColumnName] ASC;

Finally, Paul White also writes about another way to efficient page through data here that does not use OFFSET at all.

With the right indexes I expect that at least one of the techniques described in this answer will work well for you.


Postgres Join multiple table to replace id of parent table with value

This is what you want:

select *
from dependency as d
join service as s on d.service = s.s_id
join service_type as st on s.s_type = st.id
join dependency_type as dt on d.dependency_type = dt.id;

dbfiddle here.


patching SQL Server with Availability Group and Secondary Replica

The scenario is called out and supported on the link you've provided.

Availability Group with One Remote Secondary Replica

If you have deployed an availability group only for disaster recovery, you may need to fail over the availability group to an asynchronous-commit secondary replica. Such configuration is illustrated by the following figure:

Availability Group Upgrade in DR Scenario

In this situation, you must fail over the availability group to the asynchronous-commit secondary replica during the rolling upgrade/update. To prevent data loss, change the commit mode to synchronous commit and wait for the secondary replica to be synchronized before you fail over the availability group. Therefore, the rolling upgrade/update process may look as follows:

1.Upgrade/update the remote server

2.Change the commit mode to synchronous commit

3.Wait until synchronization state is SYNCHRONIZED

4.Fail over the availability group to the remote site

5.Upgrade/update the local (primary site) server

6.Fail over the availability group to the primary site

7.Change the commit mode to asynchronous commit


MySQL: Expanding table columns and insert/update values

As far as I can understand what you want is

    CREATE TABLE IF NOT EXISTS `s_kontrol_k` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `ogr_id` int(11) NOT NULL,
      `ogr_ad` varchar(255) NOT NULL,
      `ogr_numara` varchar(255) NOT NULL,
      `yurt_id` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    );

    INSERT INTO `s_kontrol_k` (
        SELECT sk.id,sk.ogr_id, so.ad AS ogr_ad,
        so.numara AS ogr_numara, so.yurt_id FROM s_kontrol AS sk
        INNER JOIN s_ogr AS so ON sk.ogr_id = so.id
    );

First create the desired table s_kontrol_k and copy data from both s_ogr and s_kontrol  to newly created table s_kontrol_k.

I hope this helps..


How do I insert tens of thousands of rows without locking?

You cannot get rid of all locking.  However, there are ways to speed up what you are doing:

Use multi-row INSERTs.  100 rows at a time will run 10 times as fast.
As mentioned, switch to InnoDB using pt-online-schema-change.  Then put multiple INSERTs into a transaction (BEGIN ... COMMIT).
The 6 threads could write to 6 different tables (such as CREATE TEMPORARY TABLE), then INSERT INTO real_table SELECT * FROM tmp_table.  Even on MyISAM this will run significantly faster because of 6 locks instead of 6*50K.  Read about concurrent_insert to see how to avoid 50K table locks into the tmp tables.

Please show us the SHOW CREATE TABLE.  There could be significant other issues, especially with indexes.


Retrieve parent row when key appears in either one of another two tables

A good way to do that is likely to use the UNION of two queries, one for home_players, and one for away_players.  Something like:

SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN home_players h ON m.id = h.match_id
WHERE h.player = 'Dave'
UNION
SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN away_players a ON m.id = a.match_id
WHERE a.player = 'Dave'


Is there a really good reason you do want to use a shared root directory? From my experience on SQL 2000 and 2005 it sometimes wasn't the best option.

Having them separate would make it less likely that a change/patch in one might accidentally affect the other or edge cases where NTFS permissions/corruption or something aggravates another issue causing more troubleshooting or affecting both.  If there's not a strong reason to keep them shared, I typically separate due to a tiny amount of bad experiences.


SQL Server High Availability without Windows Clustering

As per my understanding and information, there isn't any SQL Server High Availability(HA) feature without Windows Server Failover Clustering mechanism (WSFC) being used.

Correct.

  SQL Server Mirroring used to be one such option where HA was possible without WSFC but with Microsoft stating that Mirroring would be removed soon from the product

Also correct.

  One of the updates in vNext was that Microsoft would be releasing an option for Cluster Free Availability groups.

Correct, but this does NOT provide HA, so you will still need to use cluster for HA even in vNext.

  But as of now, do we have any Cluster Free HA feature within the product(SQL Server 2012, 2014, 2016)?

Mirroring. If you can setup mirroring you can setup clustering and use AGs - I'm not sure why the big push to not have to use Windows Clustering.


Check if column has same value in any row

Something like this, may be
select
 article_id, user_id,
 case
   when count(1) over (partition by article_id) > 1 then 'true'
   else 'false'
 end is_connected
from something


Cross join flatten JSON - flatten only one the most recent row

Something like this (haven't tried it)
with my_mt as (
  select * from mytable
     where device_id = 20
     order by insert_time desc
     limit 1
) SELECT  my_mt.measurement_id,
          p.*
    from my_mt
    cross join jsonb_to_recordset(my_mt.data->'ports')
                as p(port_name text,
                     port_description text,
                     port_status_int int,
                     port_status text,
                     port_speed_int int,
                     ports_speed text);


What is a good way to store a large number of columns?

You could make a case either way, but if the data is going to be used for analysis and you often want to see multiple columns from that data at the same time, go with the wide table. Make sure you know your databases column quantity and row size limits. Make sure you get the datatypes right. If many of the columns are null, SQL Server allows you to optimize the table for that. You could also consider using a NOSQL (Not Only SQL) solution for analysis of this type of data.

If this data is going to be less for analysis, you might want to normalize it as stated in your question.


Please try this:

INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
SELECT a.class_ref, b.teachers_ref, c.section_ref
      FROM
      (select class.id as class_ref from class where class.name = 'pe') as a,
      (select teachers.id as teachers_ref from teachers where teachers.last_name = 'lenevo') as b,
      (select section.id as section_ref from section where section.name = 'grade 4') as c;

I assume it will be quiet slow, but should to the trick.


The difference is between CLR static methods (type_name::method()) and instance methods (instance.method())

Static methods are defined on the type itself and are generally utility methods that get everything they need to operate passed in as method parameters.

Instance methods operate on an object of a particular datatype and are able to access the private state of that object and mutate the object or return a result that uses that state.

HierarchyID::GetRoot() always returns the same result.

@sample_node.GetAncestor(1); will return a different result dependent On what @sample_node is.


Postgres text search has unexpected query conversion

You have created a new dictionary and such, but you are not using it.

SELECT to_tsquery('simple_english_with_stopwords','off <-> plan');
    to_tsquery
------------------
 'off' <-> 'plan'
(1 row)

Or:

set default_text_search_config TO 'simple_english_with_stopwords';
SELECT to_tsquery('off <-> plan');
    to_tsquery
------------------
 'off' <-> 'plan'

The first, more verbose, option is better if you want to use FTS indexes, otherwise you run into mutability problems.


Is it recommended to clear the query plan cache

I would recommend against this.  You have done optimization on probably a small subset of queries, and over time you will see the improvement and performance gain.  But if you just do a clean refresh of the query plan cache you will blow all of the execution plans out of there, affecting more than just your optimizations.

I'd be willing to say the immediate performance degradation isn't worth the smaller gain of using your optimized plans right away.  But again, this is another "it depends".


Vacuum settings for mostly append-only data

Autovacuum won't run against the table if it not changing. In your case, you are appending data to the table so autovacuum will run, but it is the ANALYZE portion that is worth mentioning. The DELETEs will kick of autovacuum runs of course.
Are you seeing performance problems when autovacuum runs? If not, I would recommend leaving it enabled. After your loading, you could issue a manual VACUUM FREEZE <your table>, but again, I would not recommend disabling autovacuum unless there is a compelling reason to do so.

Note: An unadorned VACUUM does not reclaim space, it marks it as reusable. A VACUUM FULL or CLUSTER will reclaim space however.

-- To show the current vacuum settings:

SELECT  "name",
        current_setting("name") AS current_setting,
        source
FROM pg_settings
WHERE "name" ~* 'vac'
ORDER BY "name"


MySQL Cluster MaxNoOfConcurrentOperations and RAM

This is a good summary: http://fromdual.com/mysql-cluster-memory-sizing

Most memory buffers are preallocated in memory to avoid latency issues when reserving huge chunks of memory.